<?php

include_once('connect.php');
include_once('functions.php');
//Query to check for customers without orders and add it to the alert list;
$query = "INSERT IGNORE INTO CUSTOMER_ACTIVITY_ALERT (SELECT 0 AS id, CUSTOMER.AccountNumber, MAX(OrderDate) as LastOrder, DATE_ADD(NOW(), INTERVAL -4 HOUR) AS AlertDate, CUSTOMER.Salesman AS Salesman FROM CUSTOMER, ORDER_HEAD WHERE CUSTOMER.Salesman != '30' AND CUSTOMER.AccountNumber = ORDER_HEAD.AccountNumber AND ORDER_HEAD.AccountNumber GROUP BY CUSTOMER.AccountNumber HAVING MAX(OrderDate) < DATE_ADD(NOW(), INTERVAL -30 DAY) ORDER BY MAX(ORDER_HEAD.OrderDate))";
$finalResult = '';
if($result = mysqli_query($con, $query)){
	$finalResult = "Completed";
}else{
	$finalResult = "ERROR: " . mysqli_error($con);
}
event_log('AUTOMATIC_SYSTEM', 'CUSTOMERS WITHOUT ORDERS-ADD TO ALERT LIST', $finalResult);
//END

//CHECK FOR ALERTS THAT HAS BEEN THERE FOR AT LEAST 23 HOURS
if(false){
	$query = "SELECT *, TIMEDIFF(DATE_ADD(NOW(), INTERVAL -4 HOUR), AlertDate) AS AlertLength FROM CUSTOMER_ACTIVITY_ALERT WHERE TIMEDIFF(DATE_ADD(NOW(), INTERVAL -4 HOUR), AlertDate) > \"23:45\"";
	$result = mysqli_query($con, $query);
	while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
		$query2 = "INSERT INTO CUSTOMER_ACTIVITY (AccountNumber, ReportDate, LastOrder, Reason, Salesman) VALUES ('" . $row['AccountNumber'] . "', DATE_ADD(NOW(), INTERVAL -4 HOUR),'" . $row['LastOrder'] . "','VENDEDOR NO CONTESTO','" . $row['Salesman'] . "')";
		$result2 = mysqli_query($con, $query2);
		$finalResult = "Customer: " . $row['AccountNumber'] . "\n";
		$finalResult .= "Salesman: " . $row['Salesman'] . "\n";
		$finalResult .= "LastOrder: " . $row['LastOrder'] . "\n";
		$finalResult .= "AlertDate: " . $row['AlertDate'] . "\n";
		event_log('AUTOMATIC_SYSTEM', 'CUSTOMERS WITHOUT ORDERS-SALESMAN THAT DID NOT ANSWER ALERT', $finalResult);
	}
}

//DELETE ALL CUSTOMERS THAT ARE ON THE ALERT LIST AND MADE AN ORDER
$query = "SELECT AccountNumber, LastOrder, MAX(ORDER_HEAD.OrderDate) NewLastOrder FROM `CUSTOMER_ACTIVITY_ALERT` join ORDER_HEAD USING(AccountNumber) GROUP BY AccountNumber HAVING MAX(ORDER_HEAD.OrderDate) > LastOrder";
$result = mysqli_query($con, $query);

while($row = mysqli_fetch_array($result)){
	$finalResult = '';
	$query2 = "DELETE FROM CUSTOMER_ACTIVITY_ALERT WHERE AccountNumber = '" . $row['AccountNumber'] . "' AND LastOrder = '" . $row['LastOrder'] . "';";
	$finalResult .= "DELETING " . $row['AccountNumber'] . " LastOrder: " . $row['LastOrder'] . " -> New LastOrder: " . $row['NewLastOrder'] . "...";
	if($result2 = mysqli_query($con, $query2)){
		$finalResult .= "Completed\n";
	}else{
		$finalResult .= "ERROR: " . mysqli_error($con) . "\n";
	}
	event_log('AUTOMATIC_SYSTEM', 'CUSTOMERS WITHOUT ORDERS-DELETE ALERTS CUST WITH NEW ORDERS', $finalResult);
}

event_log('AUTOMATIC_SYSTEM', 'CUSTOMERS WITHOUT ORDERS-DELETE ALERTS CUST WITH NEW ORDERS', "Completed");




//UPDATE DUE DAYS OF ALL CUSTOMERS

$query = "SELECT AccountNumber FROM CUSTOMER ORDER BY AccountNumber";
$result = mysqli_query($con, $query);
while($row = mysqli_fetch_array($result)){
	$customer = new Customer($row['AccountNumber']);
	$customer->updateDueDays();
}
?>