<?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 == "getCustomerActivity"){
	
	getCustomerActivity($_REQUEST['sid']);
}elseif($function == "saveDismissCustomerActivity"){
	dismissCustomer($_POST);
}elseif($function == "getSalesmanReport"){
	getSalesmanReport($_REQUEST['id']);
}


	//this function usually is called via a GET request to display Customers that don't have orders from the last 30 days
function getCustomerActivity($sid){
	
	global $con;
	/*
	$query =
		"SELECT
			CUSTOMER.AccountNumber,
			CUSTOMER.Name,
				IF(MAX(ReportDate) > MAX(OrderDate),
					MAX(ReportDate),
					MAX(OrderDate))  AS
			LastActivity,
				DATEDIFF(
					DATE_ADD(NOW(), INTERVAL -4 HOUR),
					IF(MAX(ReportDate) > MAX(OrderDate),
						MAX(ReportDate),
						MAX(OrderDate))) AS
			LastActivityDays,
				MAX(OrderDate) AS
			LastOrderDate,
				DATEDIFF(DATE_ADD(NOW(), INTERVAL -4 HOUR), MAX(OrderDate)) AS
			LastOrderDays,
			LastLogin
		FROM
			ORDER_HEAD,
			CUSTOMER
		LEFT JOIN
			CUSTOMER_ACTIVITY ON(CUSTOMER.AccountNumber = CUSTOMER_ACTIVITY.AccountNumber )
		WHERE
			CUSTOMER.Salesman LIKE '$sid' AND
			CUSTOMER.AccountNumber = ORDER_HEAD.AccountNumber
		GROUP BY
			CUSTOMER.AccountNumber
		HAVING
			LastActivity < DATE_ADD(NOW(), INTERVAL -30 DAY)
		ORDER BY
			MAX(ORDER_HEAD.OrderDate);
	
	";
	*/
	$query = "SELECT 
			AccountNumber,
			LastOrder AS
			LastOrderDate
		FROM
			CUSTOMER_ACTIVITY_ALERT
		WHERE	
			CUSTOMER_ACTIVITY_ALERT.Salesman LIKE '$sid'
		GROUP BY
			CUSTOMER_ACTIVITY_ALERT.AccountNumber, LastOrder";
	//$query = "SELECT CUSTOMER.AccountNumber, CUSTOMER.Name, MAX(OrderDate) as LastOrderDate, LastLogin FROM CUSTOMER, ORDER_HEAD WHERE CUSTOMER.Salesman = '$sid' AND CUSTOMER.AccountNumber = ORDER_HEAD.AccountNumber AND ORDER_HEAD.AccountNumber NOT IN (SELECT CUSTOMER_ACTIVITY.AccountNumber FROM CUSTOMER_ACTIVITY WHERE (ReportDate) > DATE_ADD(NOW(), INTERVAL -30 DAY)) GROUP BY CUSTOMER.AccountNumber HAVING MAX(OrderDate) < DATE_ADD(NOW(), INTERVAL -30 DAY) ORDER BY MAX(ORDER_HEAD.OrderDate)";
	$result = mysqli_query($con, $query);
	//store data in array to later convert to JSON
	$return = array();
	while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
		$query2 = "SELECT * FROM CUSTOMER_ACTIVITY, CUSTOMER WHERE CUSTOMER.AccountNumber = CUSTOMER_ACTIVITY.AccountNumber AND CUSTOMER_ACTIVITY.AccountNumber = '" . $row['AccountNumber'] . "' AND LastOrder = '" . $row['LastOrderDate'] ."' GROUP BY CUSTOMER_ACTIVITY.AccountNumber,LastOrder ORDER BY CUSTOMER_ACTIVITY.id DESC";
		$result2 = mysqli_query($con, $query2);
		//var_dump($query2);
		//$row['QueryA'] = $query;
		//$row['QueryB'] = $query2;
		if(mysqli_num_rows($result2) != 0){
			$row['ReportCount'] = mysqli_num_rows($result2);
			//CHECK REPORT DATE
			
			$row2 = mysqli_fetch_array($result2, MYSQLI_ASSOC);
			$row['LastActivity'] = $row2['ReportDate'];
			
			$reportDate = date_create($row2['ReportDate']);
			$nowDate = new DateTime();
			$diff = date_diff ($nowDate, $reportDate);
			$row['LastActivityDays'] = $diff->days;
			//if the report has less than 30 days, skip
			if($diff->days < 30){
				continue;	
			}
			//
		}else{
			//El vendedor no ha hecho reporte
			$row['ReportCount'] = 0;
			
			$reportDate = date_create($row['LastOrderDate']);
			
			$nowDate = new DateTime();
			//var_dump($reportDate);
			$row['LastActivity'] = $row['LastOrderDate'];
			
			$diff = date_diff ($nowDate, $reportDate);
			
			$row['LastActivityDays'] = $diff->days;
		}
		
		
		$customer = new Customer($row['AccountNumber']);
		$row['Name'] = $customer->Name;
		//$row['Query2'] = $query2;
		
		//change date formatting
		$row['LastOrderDate'] = date("m/d/y h:i:s a", strtotime($row['LastOrderDate']));
		$row['LastLogin'] = $customer->LastLogin?date("m/d/y", strtotime($customer->LastLogin)):"Never";
		array_push($return,$row);	
		//if($row['ReportCount'] > 1 || ($row['ReportCount'] > 0 && $row['LastActivityDays'] > 90)){
		//	continue;
		//}else{
		//	
		//}
		
	}
	echo json_encode($return);
	return $return;
}
function dismissCustomer($data){
	global $con;
	$lastOrder = date("Y-m-d H:i:s", strtotime($data['LastOrder']));
	$query = "INSERT INTO CUSTOMER_ACTIVITY (AccountNumber, ReportDate, LastOrder, Reason, Salesman) VALUES ('" . $data['AccountNumber'] . "', DATE_ADD(NOW(), INTERVAL -4 HOUR),'" . $lastOrder . "','" . mysqli_real_escape_string($con, $data['Reason'] ) . "','" . $_SESSION['SalesmanID'] . "')";
	if($result = mysqli_query($con, $query)){
		echo "true";
	}else{
		echo "false";
	}
	
	
}
function getSalesmanReport($id){
	global $con;
	$query = "SELECT * FROM CUSTOMER_ACTIVITY WHERE id = $id";
	$return = array();
	if($result = mysqli_query($con, $query)){
		if(mysqli_num_rows($result)){
			$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
			$return = $row;
		}else{
			$return[] = "error";
		}
		echo json_encode($return);
	}
	echo mysql_error($con);
	
	
	
}
?>