<?PHP 
// Initialize the session
session_start();
error_reporting(0);
// Initialize the session
//session_start();
//include('function_bgcolorSS_byrLepas7hb.php');
require_once "config.php";

function dateDifference($start_date, $end_date)
{
	// calulating the difference in timestamps 
	$diff = strtotime($start_date) - strtotime($end_date);
	 
	// 1 day = 24 hours 
	// 24 * 60 * 60 = 86400 seconds
	return ceil(abs($diff / 86400));
}


// Function to check if a year is a leap year
function isLeapYear($year)
{
  // Leap year conditions
  if (($year % 4 == 0 && $year % 100 != 0) || ($year % 400 == 0)) return true; // True, it's a leap year
  else return false; // False, it's not a leap year
  
}


$tenant_id = $_GET["tenant_id"];
$dateStart = $_GET["dateStart"];
$dateEnd = $_GET["dateEnd"];
$location_id = $_SESSION["location_id"];

$yearStart = date("Y",strtotime($dateStart));
$yearEnd = date("Y",strtotime($dateEnd));

include("queryTenant.php");

$resultTenant = mysqli_fetch_assoc($runTenant);
$tenant_name = $resultTenant["tenant_name"];
$unit_no = $resultTenant["unit_no"];
$location_name = $resultTenant["location_name"];

 $sqlAgeing_byTenant = "select sum(bills.amount_due) as total_outstanding, 
								bills.* , bills.created_at as payment_created_at, properties.unit_no, tenants.tenant_name as name , properties.id as property_id
								from bills 
								inner join tenants on bills.tenant_id = tenants.id 
								inner join properties on tenants.property_id = properties.id 
								inner join locations on properties.location_id = locations.id 
								where bills.issue_date >= '$dateStart' and bills.issue_date <= '$dateEnd'
								and locations.id = $location_id and bills.amount_due > 0.00
								and bills.bill_type_id = 1 and tenants.id = $tenant_id and 
								(bills.payment_status = 'partial' or bills.payment_status = 'unpaid')
								group by bills.issue_date,tenants.id having total_outstanding > 0 ";

$runSqlAgeing_byTenant = mysqli_query($link, $sqlAgeing_byTenant);
if($runSqlAgeing_byTenant === FALSE){
	die(mysqli_error($link));
}

$filename = 'Export_excel_' . strtoupper($_SESSION["locationName"]). "_" . $timestamp . '.csv';
header("Content-Type: application/csv");
header("Content-Disposition: attachment; filename=\"$filename\"");
$csv_statement = "";
if($tenant_id != ""){ 
	$csv_statement .= 'MAKLUMAT TUNGGAKAN';
	$csv_statement .= "\n";
	$csv_statement .= strtoupper($tenant_name) ." : ". strtoupper($unit_no) . " " .  strtoupper($location_name);
	$csv_statement .= "\n";
	$csv_statement .= date("d/m/Y",strtotime($dateStart)) . " - " . date("d/m/Y",strtotime($dateEnd));
	$csv_statement .= "\n";
	
	//check tahun lompat (leap year) - bilangan hari jadi 366 setahun
	$dateStart_year = date("Y",strtotime($dateStart));
	$dateEnd_year = date("Y",strtotime($dateEnd));
	$end_ageing = "361-365 Hari";
	$leapYear = "";
	
	// Call the function to check if the year is a leap year
	if (isLeapYear($dateStart_year)) {
		$leapYear = "yes";
		$end_ageing = "361-366 Hari";
	}
	if (isLeapYear($dateEnd_year)) {
		$leapYear = "yes";
		$end_ageing = "361-366 Hari";
	}
	
	$csv_statement .= "Bil. , Invois ID#, Tarikh , Jumlah (RM) , 0-30 Hari, 31-60 Hari, 61-90 Hari, 91-120 Hari, 121-150 Hari, 151-180 Hari , 181-210 Hari, 211-240 Hari, 241-270 Hari, 271-300 Hari ,301-330 Hari, 331-360 Hari, " . $end_ageing;
	$csv_statement .= "\n";
	$tenantLocationGroup = "";
	$bil_location = 1;
	$grandTotalOutstanding_all = 0; 
	
	$all_bill_ball30 = 0;
	$all_bill_ball3160 = 0;
	$all_bill_ball6190 = 0;
	$all_bill_ball91120 = 0;
	$all_bill_ball121150 = 0;
	$all_bill_ball151180 = 0;
	$all_bill_ball181210 = 0;
	$all_bill_ball211240 = 0;
	$all_bill_ball241270 = 0;
	$all_bill_ball271300 = 0;
	$all_bill_ball301330 = 0;
	$all_bill_ball331360 = 0;
	$all_bill_ball36365 = 0;

	$bil_tenant = 1;
	
	$grandTotalOutstanding_location = 0; 
	$bil = 1;
	$tenant_totalOutstanding = 0;
	$bill_ball30 = 0;
	$bill_ball3160 = 0;
	$bill_ball6190 = 0;
	$bill_ball91120 = 0;
	$bill_ball121150 = 0;
	$bill_ball151180 = 0;
	$bill_ball181210 = 0;
	$bill_ball211240 = 0;
	$bill_ball241270 = 0;
	$bill_ball271300 = 0;
	$bill_ball301330 = 0;
	$bill_ball331360 = 0;
	$bill_ball361365 = 0;
	$grandTotalOutstanding_tenant = 0; 
	
	while($resultSqlAgeing_byTenant = mysqli_fetch_array($runSqlAgeing_byTenant)){
															
		$bill_bal = $resultSqlAgeing_byTenant['total_outstanding'];
		$invDate = $resultSqlAgeing_byTenant['issue_date'];
		$location = $resultSqlAgeing_byTenant['location'];
		$unit = $resultSqlAgeing_byTenant['unit'];
		$tenant_name = $resultSqlAgeing_byTenant['name'];
		$invDate = date("Y-m-d",strtotime($resultSqlAgeing_byTenant['issue_date']));
		$aged_date = date("Y-m-d", strtotime($dateEnd . ' + 1 days'));
		 
		$start_date = $invDate;
		$end_date = $aged_date;
		// call dateDifference() function to find the number of days between two dates
		$diff = dateDifference($start_date, $end_date);
		
		$csv_statement .= $bil .",";
		$csv_statement .= $resultSqlAgeing_byTenant['bill_no'] .",";
		$csv_statement .= date("d/m/Y",strtotime($resultSqlAgeing_byTenant['issue_date'])) .",";
		$csv_statement .= $bill_bal .",";
		
		if($diff <= 30){
			$csv_statement .= $bill_bal .",";
			$bill_ball30 += $bill_bal;
		}else{
			$csv_statement .= ",";
		}
		if($diff >= 31 && $diff <= 60){
			$csv_statement .= $bill_bal .",";
			$bill_ball3160 += $bill_bal;
		}else{
			$csv_statement .= ",";
		}
		if($diff >= 61 && $diff <= 90){
			$csv_statement .= $bill_bal .",";
			$bill_ball6190 += $bill_bal;
		}else{
			$csv_statement .= ",";
		}
		if($diff >= 91 && $diff <= 120){
			$csv_statement .= $bill_bal .",";
			$bill_ball91120 += $bill_bal;
		}else{
			$csv_statement .= ",";
		}
		if($diff >= 121 && $diff <= 150){
			$csv_statement .= $bill_bal .",";
			$bill_ball121150 += $bill_bal;
		}else{
			$csv_statement .= ",";
		}
		if($diff >= 151 && $diff <= 180){
			$csv_statement .= $bill_bal .",";
			$bill_ball151180 += $bill_bal;
		}else{
			$csv_statement .= ",";
		}
		if($diff >= 181 && $diff <= 210){
			$csv_statement .= $bill_bal .",";
			$bill_ball181210 += $bill_bal;
		}else{
			$csv_statement .= ",";
		}
		if($diff >= 211 && $diff <= 240){
			$csv_statement .= $bill_bal .",";
			$bill_ball211240 += $bill_bal;
		}else{
			$csv_statement .= ",";
		}
		if($diff >= 241 && $diff <= 270){
			$csv_statement .= $bill_bal .",";
			$bill_ball241270 += $bill_bal;
		}else{
			$csv_statement .= ",";
		}
		if($diff >= 271 && $diff <= 300){
			$csv_statement .= $bill_bal .",";
			$bill_ball271300 += $bill_bal;
		}else{
			$csv_statement .= ",";
		}
		if($diff >= 301 && $diff <= 330){
			$csv_statement .= $bill_bal .",";
			$bill_ball301330 += $bill_bal;
		}else{
			$csv_statement .= ",";
		}
		if($diff >= 331 && $diff <= 360){
			$csv_statement .= $bill_bal .",";
			$bill_ball331360 += $bill_bal;
		}else{
			$csv_statement .= ",";
		}
		if($leapYear == "yes"){
			if($diff >= 361 && $diff <= 366){
				$csv_statement .= $bill_bal .",";
				$bill_ball361365 += $bill_bal;
			}
		}
		else{
			if($diff >= 361 && $diff <= 365){
				$csv_statement .= $bill_bal .",";
				$bill_ball361365 += $bill_bal;
			}
		}
		$csv_statement .= "\n";
		$bil++;
		$tenant_totalOutstanding  += $bill_bal;
		$grandTotalOutstanding_tenant += $bill_bal;
	}
	
	$grandTotalOutstanding_all += $tenant_totalOutstanding;
	$all_bill_ball30 += $bill_ball30;
	$all_bill_ball3160 += $bill_ball3160;
	$all_bill_ball6190 += $bill_ball6190;
	$all_bill_ball91120 += $bill_ball91120;
	$all_bill_ball121150 += $bill_ball121150;
	$all_bill_ball151180 += $bill_ball151180;
	$all_bill_ball181210 += $bill_ball181210;
	$all_bill_ball211240 += $bill_ball211240;
	$all_bill_ball241270 += $bill_ball241270;
	$all_bill_ball271300 += $bill_ball271300;
	$all_bill_ball301330 += $bill_ball301330;
	$all_bill_ball331360 += $bill_ball331360;
	$all_bill_ball361365 += $bill_ball361365;
	
	$csv_statement .= "JUMLAH KESELURUHAN TUNGGAKAN (RM): , , , ";
	$csv_statement .= $grandTotalOutstanding_all ."," ;
	$csv_statement .= $all_bill_ball30 ."," ;
	$csv_statement .= $all_bill_ball3160 ."," ;
	$csv_statement .= $all_bill_ball6190 ."," ;
	$csv_statement .= $all_bill_ball91120 ."," ;
	$csv_statement .= $all_bill_ball121150 ."," ;
	$csv_statement .= $all_bill_ball151180 ."," ;
	$csv_statement .= $all_bill_ball181210 ."," ;
	$csv_statement .= $all_bill_ball211240 ."," ;
	$csv_statement .= $all_bill_ball241270 ."," ;
	$csv_statement .= $all_bill_ball271300 ."," ;
	$csv_statement .= $all_bill_ball301330 ."," ;
	$csv_statement .= $all_bill_ball331360 ."," ;
	$csv_statement .= $all_bill_ball361365 ."," ;
	$csv_statement .= "\n";
	
	$percent_bill_ball_all = ($grandTotalOutstanding_all / $grandTotalOutstanding_all) * 100;
	$percent_bill_ball30 = ($all_bill_ball30 / $grandTotalOutstanding_all) * 100;
	$percent_bill_ball3160 = ($all_bill_ball3160 / $grandTotalOutstanding_all) * 100;
	$percent_bill_ball6190 = ($all_bill_ball6190 / $grandTotalOutstanding_all) * 100;
	$percent_bill_ball91120 = ($all_bill_ball91120 / $grandTotalOutstanding_all) * 100;
	$percent_bill_ball121150 = ($all_bill_ball121150 / $grandTotalOutstanding_all) * 100;
	$percent_bill_ball151180 = ($all_bill_ball151180 / $grandTotalOutstanding_all) * 100;
	$percent_bill_ball181210 = ($all_bill_ball181210 / $grandTotalOutstanding_all) * 100;
	$percent_bill_ball211240 = ($all_bill_ball211240 / $grandTotalOutstanding_all) * 100;
	$percent_bill_ball241270 = ($all_bill_ball241270 / $grandTotalOutstanding_all) * 100;
	$percent_bill_ball271300 = ($all_bill_ball271300 / $grandTotalOutstanding_all) * 100;
	$percent_bill_ball301330 = ($all_bill_ball301330  / $grandTotalOutstanding_all) * 100;
	$percent_bill_ball331360 = ($all_bill_ball331360  / $grandTotalOutstanding_all) * 100;
	$percent_bill_ball361365 = ($all_bill_ball361365  / $grandTotalOutstanding_all) * 100;
	
	$csv_statement .= "AGEING PERCENT: , , , ";
	$csv_statement .= $percent_bill_ball_all ."%," ;
	$csv_statement .= $percent_bill_ball30 ."%," ;
	$csv_statement .= $percent_bill_ball3160 ."%," ;
	$csv_statement .= $percent_bill_ball6190 ."%," ;
	$csv_statement .= $percent_bill_ball91120 ."%," ;
	$csv_statement .= $percent_bill_ball121150 ."%," ;
	$csv_statement .= $percent_bill_ball151180 ."%," ;
	$csv_statement .= $percent_bill_ball181210 ."%," ;
	$csv_statement .= $percent_bill_ball211240 ."%," ;
	$csv_statement .= $percent_bill_ball241270 ."%," ;
	$csv_statement .= $percent_bill_ball271300 ."%," ;
	$csv_statement .= $percent_bill_ball301330 ."%," ;
	$csv_statement .= $percent_bill_ball331360 ."%," ;
	$csv_statement .= $percent_bill_ball361365 ."%," ;
	$csv_statement .= "\n";
	
	
}

echo  $csv_statement;
exit;


?>
