<?php

include_once('connect.php');
include_once('functions.php');

//Query for sending account statement
//check if day is 1st of the month.
set_time_limit(0);
if(date("d") == "1"){
	//echo "entro";
	$query = "SELECT AccountNumber, Balance, ElapsedDays FROM ACCOUNTS_RECEIVABLE_AGING WHERE Balance > 0 AND ElapsedDays > 0";
	$result = mysqli_query($con, $query);
	while($row = mysqli_fetch_array($result)){
		$customer= new Customer($row['AccountNumber']);
		//$customer->sendStatementEmail();
		sleep(5);

	}
}else{

}
include_once("modules/payment/payment.functions.php");
refreshAging();
echo date("d");


//$query = "INSERT INTO `SALESMAN_QUOTA` (Salesman, `Month`, `Year`, `MTD`) (SELECT SalesmanID, " . date("m", $importDate-(60*60*24*1)) . ", " . date("Y", $importDate-(60*60*24*1)) . ", MTD FROM SALESMAN_QUOTA_TEMP) ON DUPLICATE KEY UPDATE `MTD` = VALUES(`MTD`);";

/*
//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();
}

//Due date emails
// 7 dias despues de 30 dias luego
$query = "SELECT AccountNumber, InvoiceID, Subtotal, INVOICE_HEAD.InvoiceDate ,TIMESTAMPDIFF(DAY, MIN(INVOICE_HEAD.DueDate), NOW()) AS OverdueDays FROM INVOICE_HEAD
LEFT JOIN PAYMENT_DETAIL USING(InvoiceID)
LEFT JOIN CUSTOMER USING(AccountNumber)
WHERE Subtotal > 0 AND Status = 1 AND Void = 0 AND `AccountNumber` LIKE '%' AND `SendAmountDueEmail` AND INVOICE_HEAD.InvoiceID NOT IN (SELECT InvoiceID FROM PAYMENT_DETAIL WHERE PartialPayment = 0) GROUP BY AccountNumber HAVING OverdueDays%30 = 7";
while($row = mysqli_fetch_array($result)){
	$customer = new Customer($row['AccountNumber']);
	//$customer->updateDueDays();
}
*/
?>
