<?php
global $con;
if(stripos($_SERVER['SCRIPT_NAME'],"/admin/")===false?false:true){
	include_once("../connect.php");
	
}else{
	include_once("connect.php");
	
}

$function = $_REQUEST['f'];
if($function == "getSalesmanQuota"){
	
	getSalesmanQuota($_REQUEST['sid']);
}elseif($function == "saveQuotaData"){
	saveQuotaData($_REQUEST['month'], $_REQUEST['year'], $_REQUEST['sid'], $_REQUEST['quota']);
}elseif($function == "fillNextYearQuota"){
	fillNextYearQuota();
}elseif($function == "bulkSaveQuota"){
	bulkSaveQuota($_REQUEST['type'], $_REQUEST['year'], $_REQUEST['axis'], $_REQUEST['percent']);
}

function getSalesmanQuota($sid){
	
	global $con;
	$query = "SELECT * FROM SALESMAN_QUOTA WHERE Salesman LIKE '$sid' AND `Month` = '" . date("m", time()-(24*60*60) ) . "'  AND `Year` = '" . date("Y", time()-(24*60*60)) . "'";
	$result = mysqli_query($con, $query); 
	$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
	$return = array();
	$return['MTD'] = $row['MTD'];
	$return['Goal'] = $row['Goal'];
	$return['Percent'] = round($row['MTD']/$row['Goal'],3);
	
	$lastDay = date("t", time()-(24*60*60));
	
	$today = date("d", time()-(24*60*60));
	$remainingDays = 0;
	for($i = $today; $i < $lastDay; $i++ ){
		if(date('w', strtotime(date('m') . '/' . $i . '/' . date('y'))) != 5 && date('w', strtotime(date('m') . '/' . $i . '/' . date('y'))) != 6){
			$remainingDays++;
			
		}
	}
	//$remainingDays = $lastDay-$today;
	$return['RemainingDays'] = $remainingDays;
	if($remainingDays == 0){
		$return['Average'] = 0;
	}else{
		$return['Average'] = round(($row['Goal']-$row['MTD'])/$remainingDays,2);
	}
	$return['Arrows'] = getSalesmanQuotaArrows($sid);
	
	$query = "SELECT ROUND(SUM(CashAmount + CheckAmount),2) AS ProcessedPayments FROM `PAYMENT_HEAD` WHERE MONTH(`ProcessDate`) = " . date("m", time()-(24*60*60) ) . " AND YEAR(`ProcessDate`) = " . date("Y", time()-(24*60*60) ) . " AND SalesmanID = '" . $sid . "' AND Void = 0 AND (CPDDate <= NOW() OR CPD = 0) ";
	//$return['PaymentsQuery'] = $query;
	$result = mysqli_query($con, $query);
	$row = mysqli_fetch_array($result);
	$return['Payments'] = $row['ProcessedPayments'];
	
	
	$conEV = mysqli_connect("localhost","grandaut_ev","R334XEmWe2&h%XJQ","grandaut_orders_ev");
	if($sid == 35 || $sid == 37 || $sid == 38){
		//if salesman is 35 ,37, or 38, substract 20 to get 15, 17 or 18 respectively (Salesmen with 2 numbers)
		$sid -= 20;
	}
	$queryEV = "SELECT ROUND(SUM(CashAmount + CheckAmount),2) AS ProcessedPayments FROM `PAYMENT_HEAD` WHERE MONTH(`ProcessDate`) = " . date("m", time()-(24*60*60) ) . " AND YEAR(`ProcessDate`) = " . date("Y", time()-(24*60*60) ) . " AND SalesmanID = '" . $sid . "' AND Void = 0 AND (CPDDate <= NOW() OR CPD = 0) ";
	$resultEV = mysqli_query($conEV, $queryEV);
	$rowEV = mysqli_fetch_array($resultEV);
	$return['Payments'] += $rowEV['ProcessedPayments'];
	
	//var_dump($return);
	echo json_encode($return);
}
function getSalesmanQuotaArrows($sid){
	global $con;
	$year = date('Y', time()-(24*60*60));
	//Array of Month # to Initial
	$months = array(
					1 => "E",
					2 => "F",
					3 => "M",
					4 => "A",
					5 => "M",
					6 => "J",
					7 => "J",
					8 => "A",
					9 => "S",
					10 => "O",
					11 => "N",
					12 => "D"
					);
	//Array of Months to Complete Name -for Popover text
	$completeMonths = array(
					1 => "Enero",
					2 => "Febrero",
					3 => "Marzo",
					4 => "Abril",
					5 => "Mayo",
					6 => "Junio",
					7 => "Julio",
					8 => "Agosto",
					9 => "Septiembre",
					10 => "Octubre",
					11 => "Noviembre",
					12 => "Diciembre"
					);
	//Open the div for the arrow button group
	$html = '<div class="btn-group btn-group-sm" role="group" aria-label="...">';
	//Query to select all quotas
	$query = "SELECT * FROM SALESMAN_QUOTA WHERE Year = " . $year . " AND Salesman = '" . $sid . "' ORDER BY Month";
	
	$result = mysqli_query($con, $query);
	$quotaYTD = 0;
	$soldYTD = 0;
	$paymentsYTD = 0;
	while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
		$remainingDays = 0; // important to calculate target goal to current month;
		$month = $row['Month'];
		$workingDays = getWorkingDays($month, $year);
		$lastDay = date("t", strtotime($month . "/2/" . $year)-(24*60*60)); // last day of the month
			
		if($year . "-" . str_pad($month, 2, '0', STR_PAD_LEFT) == date('Y-m')){
			$today = date("d", time()-(24*60*60));
			$remainingDays = 0;
			for($i = $today; $i < $lastDay; $i++ ){
				if(date('w', strtotime(date('m') . '/' . $i . '/' . date('y'))) != 5 && date('w', strtotime(date('m') . '/' . $i . '/' . date('y'))) != 6){
					$remainingDays++;
					
				}
			}
		}elseif($year . "-" . str_pad($month, 2, '0', STR_PAD_LEFT) > date('Y-m')){
			$today = 1;
			$remainingDays = 0;
			for($i = $today; $i < $lastDay; $i++ ){
				if(date('w', strtotime(date('m') . '/' . $i . '/' . date('y'))) != 5 && date('w', strtotime(date('m') . '/' . $i . '/' . date('y'))) != 6){
					$remainingDays++;
					
				}
			}
		}else{
			$remainingDays = 0;
		}
		$goal = 0;
		$arrow = '';
		$arrowColor = '';
		$arrowTag = '';
		$target = 0;
		$goal = $row['Goal'];
		$target = ($goal/$workingDays)*($workingDays-$remainingDays);
		
		$soldYTD += $row['MTD'];
		if($remainingDays>0){
			$arrow = $row['MTD']/$target>= 1?"up":"down";
			$word = $row['MTD']/$target >= 1?"Over":"Short";
			$difference = $row['MTD'] - $target;
			$quotaYTD += $target;
		}else{
			$arrow = $row['MTD']/$row['Goal'] >= 1?"up":"down";
			$word = $row['MTD']/$row['Goal'] >= 1?"Over":"Short";
			$difference = $row['MTD'] - $row['Goal'];
			$quotaYTD += $row['Goal'];
		}
		
		
		//calculate payment
		$queryPayment = "SELECT ROUND(SUM(CashAmount + CheckAmount),2) AS ProcessedPayments FROM `PAYMENT_HEAD` WHERE MONTH(`ProcessDate`) = " . $month . " AND YEAR(`ProcessDate`) = " . $year . " AND SalesmanID = '" . $sid . "' AND Void = 0 AND (CPDDate <= NOW() OR CPD = 0) ";
		$resultPayment = mysqli_query($con, $queryPayment);
		//echo $queryPayment;
		$rowPayment = mysqli_fetch_array($resultPayment);
		$totalPayments = $rowPayment['ProcessedPayments'];
		$paymentsYTD += $rowPayment['ProcessedPayments'];
		$conEV = mysqli_connect("localhost","grandaut_ev","R334XEmWe2&h%XJQ","grandaut_orders_ev");
		if($sid == 35 || $sid == 37 || $sid == 38){
			//if salesman is 35 ,37, or 38, substract 20 to get 15, 17 or 18 respectively (Salesmen with 2 numbers)
			$sid -= 20;
		}
		$queryEV = "SELECT ROUND(SUM(CashAmount + CheckAmount),2) AS ProcessedPayments FROM `PAYMENT_HEAD` WHERE MONTH(`ProcessDate`) = " . $month . " AND YEAR(`ProcessDate`) = " . $year . " AND SalesmanID = '" . $sid . "' AND Void = 0 AND (CPDDate <= NOW() OR CPD = 0) ";
		$resultEV = mysqli_query($conEV, $queryEV);
		$rowEV = mysqli_fetch_array($resultEV);
		$totalPayments += $rowEV['ProcessedPayments'];
		$paymentsYTD += $rowEV['ProcessedPayments'];
		$arrowColor = $arrow == "up"?"blue":"red";
		if($row['MTD'] == 0){
			$arrowTag = "<span class=\"glyphicon glyphicon-asterisk\" aria-hidden=\"true\"></span>";	
		}else{
			$arrowTag = "<span style=\"color: $arrowColor;\" class=\"glyphicon glyphicon-arrow-" . ($arrow) . "\" aria-hidden=\"true\"></span>";
		}
		
		if($sid == "25"){
			$html .= '<button type="button" data-toggle="popover" data-placement="bottom" title="' . $completeMonths[$month] . ' ' . $year . '" data-content="<b>MTD:</b> ' . moneyFormat($row['MTD']) . '<br /><b>Goal:</b> ' . moneyFormat($row['Goal']) . '<br/><b>' . $word . ':</b> ' . moneyFormat($difference) . '<br /><br /><b>Payments:</b> ' . moneyFormat($totalPayments) . '" data-html="true" data-trigger="focus" class="btn btn-default">' . $months[$month] . '<br />' . $arrowTag . '</button>';	
		}else{
			$html .= '<button type="button" data-toggle="popover" data-placement="bottom" title="' . $completeMonths[$month] . ' ' . $year . '" data-content="<b>' . $word . ':</b> ' . moneyFormat($difference) . '" data-html="true" data-trigger="focus" class="btn btn-default">' . $months[$month] . '<br />' . $arrowTag . '</button>';	
		}
		
	}
	$arrow = $soldYTD/$quotaYTD >= 1?"up":"down";
	$word = $soldYTD/$quotaYTD >= 1?"Over":"Short";
	$arrowColor = $soldYTD/$quotaYTD >= 1?"blue":"red";
	$difference = $soldYTD-$quotaYTD;
	$arrowTag = "<span style=\"color: $arrowColor;\" class=\"glyphicon glyphicon-arrow-" . ($arrow) . "\" aria-hidden=\"true\"></span>";
	if($sid == "25"){
		$html .= '<button type="button" data-toggle="popover" data-placement="bottom" title="Year-To-Date ' . $year . '" data-content="<b>YTD:</b> ' . moneyFormat($soldYTD) . '<br /><b>Goal:</b> ' . moneyFormat($quotaYTD) . '<br/><b>' . $word . ':</b> ' . moneyFormat($difference) . '<br /><br /><b>Payments:</b> ' . moneyFormat($paymentsYTD) . '" data-html="true" data-trigger="focus" class="btn btn-default">' . YTD . '<br />' . $arrowTag . '</button>';	
	}else{
		$html .= '<button type="button" data-toggle="popover" data-placement="bottom" title="Year-To-Date ' . $year . '" data-content="<b>' . $word . ':</b> ' . moneyFormat($difference) . '" data-html="true" data-trigger="focus" class="btn btn-default">' . YTD . '<br />' . $arrowTag . '</button>';		
	}
	$html .= '</div>';
	//$html .= '<!-- ' . $query . ' -->';
	return $html;
}

function saveQuotaData($month, $year, $salesman, $quota){
	global $con;
	$query = "UPDATE SALESMAN_QUOTA SET Goal = " . $quota . " WHERE Month = " . $month . " AND Year = " . $year	. " AND Salesman = '" . $salesman . "'";
	if($result = mysqli_query($con, $query)){
		echo "true";
	}else{
		echo mysqli_error($con);
	}
	
	
}
function fillNextYearQuota(){
	global $con;
	
	$query = "SELECT sum(`Goal`) as TotalGoal FROM `SALESMAN_QUOTA` WHERE `Month` = " . date("m", time()-(7*24*60*60)) . " and `Year` = " . date("Y", time()-(7*24*60*60)) . ";";
	$result = mysqli_query($con, $query);
	//var_dump($query);
	
	$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
	if($row['TotalGoal'] !== null){
		if(isset($_REQUEST['month']) && isset($_REQUEST['year'])){
			$month = mysqli_real_escape_string($con, $_REQUEST['month']);
			$month = intval($month);
			$year = mysqli_real_escape_string($con, $_REQUEST['year']);
			$year = intval($year);
			
			$query = "INSERT INTO SALESMAN_QUOTA (`Salesman`, `Month`, `Year`, `MTD`, `MTDPayment`, `Goal`) (SELECT `Salesman`, `Month`, (`Year` + 1) AS Year, 0 AS `MTD`, 0 AS `MTDPayment`, `MTD` as `Goal` FROM SALESMAN_QUOTA WHERE `Month` = " . $month . " and `Year` = " . $year . ")  ON DUPLICATE KEY UPDATE Goal = VALUES(Goal)";

		}else{
			$query = "INSERT INTO SALESMAN_QUOTA (`Salesman`, `Month`, `Year`, `MTD`, `MTDPayment`, `Goal`) (SELECT `Salesman`, `Month`, (`Year` + 1) AS Year, 0 AS `MTD`, 0 AS `MTDPayment`, `MTD` as `Goal` FROM SALESMAN_QUOTA WHERE `Month` = " . date("m", time()-(7*24*60*60)) . " and `Year` = " . date("Y", time()-(7*24*60*60)) . ")  ON DUPLICATE KEY UPDATE Goal = VALUES(Goal)";
	
		}
		$result = mysqli_query($con, $query);
		echo $query;
		var_dump($result);
	}
	$query = "UPDATE `SALESMAN_QUOTA` SET Goal = 0 WHERE
	(	`Salesman` = '10' OR
		`Salesman` = '15' OR
		`Salesman` = '17' OR
		`Salesman` = '18' OR
		`Salesman` = '30' OR
		`Salesman` = '70' OR
		`Salesman` = '71' OR
		`Salesman` = '99' OR
		`Salesman` = 'CT' OR
		`Salesman` = 'H1' OR
		`Salesman` = 'HT' OR
		`Salesman` = 'MA' OR
		`Salesman` = 'TR'
	)";
	mysqli_query($con, $query);
}
function bulkSaveQuota($type, $year, $axis, $percent){
	global $con;
	if($type == "MonthYear"){
		$query = "UPDATE SALESMAN_QUOTA SET Goal = Goal * (1+($percent/100)) WHERE `Year` = $year AND `Month` = $axis;";
		
		$result = mysqli_query($con, $query);
	}elseif($type == "SalesmanYear"){
		$query = "UPDATE SALESMAN_QUOTA SET Goal = Goal * (1+($percent/100)) WHERE `Year` = $year AND `Salesman` = '$axis';";
		$result = mysqli_query($con, $query);
	}elseif($type == "FullYear"){
		$query = "UPDATE SALESMAN_QUOTA SET Goal = Goal * (1+($percent/100)) WHERE `Year` = $year;";
		$result = false;
	}
	$return = array();
	$return['result'] = (bool) $result;
	$return['error'] = mysqli_error($con) . " \nQuery: " . $query;
	
	echo json_encode($return);
}
function test(){
	global $con;
	$query = "INSERT INTO `TEST` (`key`, `hash`) VALUES ('" . rand(0,900000)*rand(0,1) . "', '" . (print_r($_REQUEST,true)) . "');";
	echo $query;
	mysqli_query($con, $query);
	
}
function getWorkingDays($month, $year){
	$start = 1;
	$end = date('t', strtotime($month . '/1/' . $year));
	$workingDays = 1;
	for($i = $start; $i < $end; $i++){
		if(date('w', strtotime(date('m') . '/' . $i . '/' . date('y'))) != 5 && date('w', strtotime(date('m') . '/' . $i . '/' . date('y'))) != 6){
			$workingDays++;
		}		
	}
	return $workingDays;
 }
?>