<?php

$connect = new mysqli("localhost", "sooggoo", "4Compassion*", "evergreenint"); 

		
// Get DataTables parameters
$start = isset($_POST['start']) ? intval($_POST['start']) : 0;
$length = isset($_POST['length']) ? intval($_POST['length']) : 10;
$search = isset($_POST['search']['value']) ? mysqli_real_escape_string($_POST['search']['value']) : "";



// Total records query
$totalQuery = "SELECT COUNT(DISTINCT account_number) AS total FROM debtoraccount";
$totalResult = mysqli_query($connect,$totalQuery);
$totalRow = mysqli_fetch_array($totalResult);
$totalRecords = $totalRow['total'];


$sql="SELECT account_number, debtorno, departmentid
					FROM debtoraccount
					
					";


if (!empty($search)) {
    $sql .= " WHERE account_number LIKE '%$search%'";
}

$sql .= " GROUP BY account_number LIMIT $start, $length";

$resultfromdebtoraccount = mysqli_query($connect,$sql);

if (!$resultfromdebtoraccount) {
    die("Query failed: ");
}






// Fetch data
$data = [];
		
while ($myrowfromdebtoraccount = mysqli_fetch_array($resultfromdebtoraccount)){
				
				
				
				
			$SQL9 = "SELECT 
			                departmentname
			
														
		                            FROM department
									
			WHERE departmentid = '".$myrowfromdebtoraccount['departmentid']."'";
			$result9=mysqli_query($connect,$SQL9);
			$myrow9=mysqli_fetch_array($result9);
				$departmentname = $myrow9['departmentname'];
					$balance = $myrowfromdebtoraccount['balance'];
			
		
				
			$accountnumber = 	$myrowfromdebtoraccount['account_number'];
				
				$SQL = "SELECT 
				debtorsmaster.name,
				debtorsmaster.lastpaid,
				debtorsmaster.lastpaiddate,
				debtortrans.account_number,
				
				paymentterms.terms,
			
				SUM(
					debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc - debtortrans.ptax - debtortrans.retention
				) AS balance,
		
            SUM(CASE WHEN DATEDIFF(NOW(), debtortrans.trandate) BETWEEN 61 AND 90 THEN amount ELSE 0 END) AS overdue3,
            SUM(CASE WHEN DATEDIFF(NOW(), debtortrans.trandate) BETWEEN 91 AND 120 THEN amount ELSE 0 END) AS overdue4,
            SUM(CASE WHEN DATEDIFF(NOW(), debtortrans.trandate) BETWEEN 121 AND 150 THEN amount ELSE 0 END) AS overdue5,
            SUM(CASE WHEN DATEDIFF(NOW(), debtortrans.trandate) BETWEEN 151 AND 180 THEN amount ELSE 0 END) AS overdue6
				
				FROM debtorsmaster,
					paymentterms,
					
					debtortrans
				WHERE debtorsmaster.paymentterms = paymentterms.termsindicator
				
			AND debtortrans.account_number = '" . $accountnumber."'
					
					AND debtorsmaster.debtorno =  '" . $myrowfromdebtoraccount['debtorno']."'
						AND debtortrans.settled=0
				AND debtortrans.cancelled = '0'
				AND debtortrans.trandate > '2020-12-31 00:00:00'
				
					 ";

		
			
	$ErrMsg = _('This customer could not be added because');
			$result4 = mysqli_query($connect,$SQL,$ErrMsg);
	
				
				
			
		$AgedAnalysis =mysqli_fetch_array($result4);
			
			
              
		
		
		$TotBal = $AgedAnalysis['balance'];
		
			
			$TotOD3 = $AgedAnalysis['overdue3']-$AgedAnalysis['overdue4'];
				$TotOD4 = $AgedAnalysis['overdue3']-$AgedAnalysis['overdue4'];
				$TotOD5 = $AgedAnalysis['overdue4']-$AgedAnalysis['overdue5'];
				$TotOD6 = $AgedAnalysis['overdue5']-$AgedAnalysis['overdue6'];
	
			
			
			
			
			
			$finalbalance = $TotBal ;
		
				
			//	$retentionBalance = locale_number_format($AgedAnalysis['retentionbalance'],2);
if ($TotOD3 > 0){
$data[] = [
        "debtorno" => $myrowfromdebtoraccount['debtorno'],
        "customername" => $AgedAnalysis['name'],
        "accountnumber" => $accountnumber,
        "departmentname" => $departmentname,
	    "finalbalance" => $finalbalance,
        "aging_90" => $TotOD3,
        "aging_120" => $TotOD4,
        "aging_150" => $TotOD5,
        "aging_180" => $TotOD6
    ];
}
}
// Prepare response
$response = [
    "draw" => isset($_POST['draw']) ? intval($_POST['draw']) : 0,
    "recordsTotal" => $totalRecords,
    "recordsFiltered" => $totalRecords,
    "data" => $data
];

// Return JSON response
header('Content-Type: application/json');
echo json_encode($response);
			  

// Close connection
mysql_close($connect);



?>
