<?php

		$statementby = $_GET["statementby"];
		$statement_year = $_GET["statement_year"];
		$statement_monthyear = $_GET["statement_monthyear"];
		$tenant_id = $_GET["tenant_id"];
		$selected_cutOffPayment = $_GET["cutOffPayment"];
		$_SESSION["cutOffPayment"] = $selected_cutOffPayment;
		
		
		
		$cond_selectedCutOffPayment = "";
		$cond_monthYear_BF = "";
		$cond_date_BF = "";
		
		
		if($statementby == 'statement_year'){
			 $displayYear = "display:block";
			$displayMonthYear = "display:none";
			$displayDateFromTo = "display:none";
			$displayChooseDate = "display:none";
			$displayCheckBox = "display:block";
			
			$statement_year = $_GET["statement_year"];
			$_SESSION['statement_year']=$statement_year;
			$selected_cutOffPayment = $_GET["cutOffPayment"];
			$_SESSION["cutOffPayment"] = $selected_cutOffPayment;
			$cutOffDate = date("Y-m-d",strtotime($statement_year . '-12-31')); //tarikh hujung tahun
			$firstYearDate =  date("Y-m-d",strtotime($statement_year . '-01-01')); //tarikh awal tahun
			$statement_year_cutOFF = date('Y-m-d', strtotime($cutOffDate. ' + 1 days'));
			
			
			IF($selected_cutOffPayment == true){
				
				$cond_selectedCutOffPayment = " and YEAR(payments.created_at) = '$statement_year'";
			}
			
			$year_selected = date("Y",strtotime($firstYearDate));
			$cond_monthYear_BF = " and year(bills.issue_date) < '$firstYearDate' and bills.issue_date < CURRENT_DATE ";
			$cond_date_BF = " and payments.created_at < '$firstYearDate' ";
			$sqlDate = "  YEAR(bills.issue_date)  = $year_selected and bills.issue_date < CURRENT_DATE";
			$infoTarikhTunggakan = " TAHUN $statement_year";
			
			
			 $sqlStatementRental ="SELECT * FROM ( 
								(SELECT payments.id as payment_id,payments.receipt_no, payments.amount_paid as amount, bills.issue_date as issue_date, payments.created_at as date_created,  bills.payment_status as status_payment, concat(payments.receipt_no,' payment ',payments.amount_paid) as info FROM payments
								inner join bills on payments.bill_id = bills.id
								where bills.tenant_id = $tenant_id  and bills.bill_type_id = 1  $cond_selectedCutOffPayment) 
								UNION ALL 
								(SELECT bills.id, bills.bill_no, bills.bill_amount as amount, bills.issue_date as issue_date, bills.issue_date as date_created, bills.payment_status as status_payment, concat(bills.bill_no,' bill ',bill_amount) as info  FROM bills 
								where bills.tenant_id = $tenant_id and $sqlDate  and bills.bill_type_id = 1) ) 
								results ORDER BY date_created ASC";
			$sqlStatementWater ="SELECT * FROM ( 
								(SELECT payments.id as payment_id,payments.receipt_no, payments.amount_paid as amount, bills.issue_date as issue_date, payments.created_at as date_created,  bills.payment_status as status_payment, concat(payments.receipt_no,' payment ',payments.amount_paid) as info FROM payments
								inner join bills on payments.bill_id = bills.id
								where bills.tenant_id = $tenant_id  and bills.bill_type_id = 2  $cond_selectedCutOffPayment) 
								UNION ALL 
								(SELECT bills.id, bills.bill_no, bills.bill_amount as amount, bills.issue_date as issue_date, bills.issue_date as date_created, bills.payment_status as status_payment, concat(bills.bill_no,' bill ',bill_amount) as info  FROM bills 
								where bills.tenant_id = $tenant_id and YEAR(issue_date) = '$statement_year' and bills.bill_type_id = 2) ) 
								results ORDER BY date_created ASC";
				
				
		}
		else if($statementby == 'statement_monthyear'){
			$displayYear = "display:none";
			$displayMonthYear = "display:block";
			$displayDateFromTo = "display:none";
			$displayChooseDate = "display:none";
			$displayCheckBox = "display:block";
			$statement_monthyear = $_GET["statement_monthyear"];
			$_SESSION['statement_monthyear']=$statement_monthyear;
			$selected_cutOffPayment = $_GET["cutOffPayment"];
			$_SESSION["cutOffPayment"] = $selected_cutOffPayment;
			$payment_monthYear = date("m/Y", strtotime($statement_monthyear));
			
			$firstDay = "";
			$lastDay = "";
			
			$firstDay = date("Y-m-01", strtotime($statement_monthyear));
			$lastDay = date("Y-m-t", strtotime($statement_monthyear));
			
			$cond_monthYear_BF = " and issue_date < '$firstDay' ";
			$cond_date_BF = " and payments.created_at < '$firstDay' ";
			
			//$cond_selectedCutOffPayment = " and payments.created_at between '$firstDay' and '$lastDay' ";
			$cond_selectedCutOffPayment = " and bills.issue_date like  '%$firstDay%' ";
		
			$date_monthYear = date("Y-m-01",strtotime($statement_monthyear));
			$lastdate = $date_monthYear . "-01";
			$lastdate = date("Y-m-t", strtotime($lastdate));			
			$cutOffDate = $lastdate;
			
			$sqlDate = "  bills.issue_date  = '$date_monthYear' ";
			$infoTarikhTunggakan = " BULAN $payment_monthYear ";
						
			  $sqlStatementRental ="SELECT * FROM ( 
							(SELECT payments.id  as payment_id,payments.receipt_no, payments.amount_paid as amount, bills.issue_date as issue_date, payments.created_at as date_created,  bills.payment_status as status_payment, concat(payments.receipt_no,' payment ',payments.amount_paid) as info FROM payments
							inner join bills on payments.bill_id = bills.id
							where bills.tenant_id = $tenant_id and bills.bill_type_id = 1 $cond_selectedCutOffPayment) 
							UNION ALL (SELECT bills.id, bills.bill_no, bills.bill_amount as amount, bills.issue_date as issue_date, bills.issue_date as date_created, bills.payment_status as status_payment, concat(bills.bill_no,' bill ',bill_amount) as info  FROM bills 
							where bills.tenant_id = $tenant_id and bills.issue_date like '%$firstDay%' and bills.bill_type_id = 1) ) 
							results ORDER BY date_created ASC";
			 $sqlStatementWater ="SELECT * FROM ( 
							(SELECT payments.id  as payment_id,payments.receipt_no, payments.amount_paid as amount, bills.issue_date as issue_date, payments.created_at as date_created,  bills.payment_status as status_payment, concat(payments.receipt_no,' payment ',payments.amount_paid) as info FROM payments
							inner join bills on payments.bill_id = bills.id
							where bills.tenant_id = $tenant_id  and bills.bill_type_id = 2 $cond_selectedCutOffPayment ) 
							UNION ALL (SELECT bills.id, bills.bill_no, bills.bill_amount as amount, bills.issue_date as issue_date, bills.issue_date as date_created, bills.payment_status as status_payment, concat(bills.bill_no,' bill ',bill_amount) as info  FROM bills 
							where bills.tenant_id = $tenant_id and bills.issue_date like '%$firstDay%'  and bills.bill_type_id = 2) ) 
							results ORDER BY date_created ASC";
			
		}
		else if($statementby == 'statement_date'){
				
				$displayYear = "display:none";
				$displayMonthYear = "display:none";
				$displayDateFromTo = "display:block";
				$displayChooseDate = "display:none";
				$displayCheckBox = "display:block";
				$statement_date = $_GET["statement_date"];
				$dateFrom = date("Y-m-d",strtotime($_GET["dateFrom"]));
				$dateTo =  date("Y-m-d",strtotime($_GET["dateTo"]));
				$_SESSION['statement_date']=$statement_date;
				$_SESSION['dateFrom']=$dateFrom;
				$_SESSION['dateTo']=$dateTo;
				$date_cutOFF = date('Y-m-d', strtotime($dateTo. ' + 1 days'));	
				$cutOffDate = $date_cutOFF;
				//$cond_date_BF = $date_cutOFF;
				$cond_date_BF = " and payments.created_at < '$dateFrom' ";
				$cond_monthYear_BF = " and bills.issue_date < '$dateFrom' ";
				$firstDay = $dateFrom;
				$lastDay = $date_cutOFF;
				
				$cond_selectedCutOffPayment =  " and (bills.issue_date  >= '$dateFrom'  and bills.issue_date < '$date_cutOFF') and (payments.created_at >= '$dateFrom' and payments.created_at < '$date_cutOFF') ";
				$sqlDate = " (bills.issue_date  >= '$dateFrom'  and bills.issue_date < '$date_cutOFF') ";
				$infoTarikhTunggakan = " DARI ".date("d/m/Y",strtotime($dateFrom)) . " SEHINGGA " . date("d/m/Y",strtotime($dateTo));
				
			 	 $sqlStatementRental ="SELECT * FROM ( 
							(SELECT payments.id  as payment_id,payments.receipt_no, payments.amount_paid as amount, bills.issue_date as issue_date, payments.created_at as date_created,  bills.payment_status as status_payment, concat(payments.receipt_no,' payment ',payments.amount_paid) as info FROM payments
							inner join bills on payments.bill_id = bills.id
							where bills.tenant_id = $tenant_id and bills.bill_type_id = 1 $cond_selectedCutOffPayment) 
							UNION ALL (SELECT bills.id, bills.bill_no, bills.bill_amount as amount, bills.issue_date as issue_date, bills.issue_date as date_created, bills.payment_status as status_payment, concat(bills.bill_no,' bill ',bill_amount) as info  FROM bills 
							where bills.tenant_id = $tenant_id and  $sqlDate and bills.bill_type_id = 1) ) 
							results ORDER BY date_created ASC";
			 	$sqlStatementWater ="SELECT * FROM ( 
							(SELECT payments.id  as payment_id,payments.receipt_no, payments.amount_paid as amount, bills.issue_date as issue_date, payments.created_at as date_created,  bills.payment_status as status_payment, concat(payments.receipt_no,' payment ',payments.amount_paid) as info FROM payments
							inner join bills on payments.bill_id = bills.id
							where bills.tenant_id = $tenant_id  and bills.bill_type_id = 2 $cond_selectedCutOffPayment ) 
							UNION ALL (SELECT bills.id, bills.bill_no, bills.bill_amount as amount, bills.issue_date as issue_date, bills.issue_date as date_created, bills.payment_status as status_payment, concat(bills.bill_no,' bill ',bill_amount) as info  FROM bills 
							where bills.tenant_id = $tenant_id and  $sqlDate  and bills.bill_type_id = 2) ) 
							results ORDER BY date_created ASC";
				
				
		}
		else if($statementby == 'statement_ChooseDate'){
				$displayYear = "display:none";
				$displayMonthYear = "display:none";
				$displayDateFromTo = "display:none";
				$displayChooseDate = "display:block";
				$displayCheckBox = "display:block";
				$chooseDate = date("Y-m-d",strtotime($_GET["statement_ChooseDate"]));
				$_SESSION['chooseDate']=$chooseDate;
				$selected_cutOffPayment = $_GET["cutOffPayment"];
				$_SESSION["cutOffPayment"] = $selected_cutOffPayment;
				$date_cutOFF = date('Y-m-d', strtotime($chooseDate. ' + 1 days'));	
				$cond_date_BF = "";
				$cond_monthYear_BF = "";
				$cond_selectedCutOffPayment = " and payments.created_at < '$date_cutOFF'";
				$sqlDate = " bills.issue_date < '$date_cutOFF' ";
				$cutOffDate = $chooseDate;
				$infoTarikhTunggakan = " SEHINGGA ". date("d/m/Y",strtotime($chooseDate));
				
			 	  $sqlStatementRental ="SELECT * FROM ( 
							(SELECT payments.id  as payment_id,payments.receipt_no, payments.amount_paid as amount, bills.issue_date as issue_date, payments.created_at as date_created,  bills.payment_status as status_payment, concat(payments.receipt_no,' payment ',payments.amount_paid) as info FROM payments
							inner join bills on payments.bill_id = bills.id
							where bills.tenant_id = $tenant_id and bills.bill_type_id = 1 $cond_selectedCutOffPayment) 
							UNION ALL (SELECT bills.id, bills.bill_no, bills.bill_amount as amount, bills.issue_date as issue_date, bills.issue_date as date_created, bills.payment_status as status_payment, concat(bills.bill_no,' bill ',bill_amount) as info  FROM bills 
							where bills.tenant_id = $tenant_id and  $sqlDate and bills.bill_type_id = 1) ) 
							results ORDER BY date_created ASC";
				$sqlStatementWater ="SELECT * FROM ( 
							(SELECT payments.id  as payment_id,payments.receipt_no, payments.amount_paid as amount, bills.issue_date as issue_date, payments.created_at as date_created,  bills.payment_status as status_payment, concat(payments.receipt_no,' payment ',payments.amount_paid) as info FROM payments
							inner join bills on payments.bill_id = bills.id
							where bills.tenant_id = $tenant_id  and bills.bill_type_id = 2 $cond_selectedCutOffPayment ) 
							UNION ALL (SELECT bills.id, bills.bill_no, bills.bill_amount as amount, bills.issue_date as issue_date, bills.issue_date as date_created, bills.payment_status as status_payment, concat(bills.bill_no,' bill ',bill_amount) as info  FROM bills 
							where bills.tenant_id = $tenant_id and $sqlDate  and bills.bill_type_id = 2) ) 
							results ORDER BY date_created ASC";
				
				
		}
		else if($statementby == ""){
			$chooseDate = date('Y-m-d');
			$cutOffDate = date('Y-m-d', strtotime($chooseDate. ' + 1 days'));
			$cond_selectedCutOffPayment = " and payments.created_at < '$cutOffDate'";
			$sqlDate = " bills.issue_date < '$cutOffDate' ";
			$infoTarikhTunggakan = " SEHINGGA " . DATE("d/m/Y");
			$displayMonthYear = "display:none";
			$displayDateFromTo = "display:none";
			$displayChooseDate = "display:none";
			$displayCheckBox = "display:none";
			$displayStatementOption = "display:none";
			
			 $sqlStatementRental ="SELECT * FROM ( 
							(SELECT payments.id as payment_id,payments.receipt_no, payments.amount_paid as amount, bills.issue_date as issue_date, payments.created_at as date_created,  bills.payment_status as status_payment, concat(payments.receipt_no,' payment ',payments.amount_paid) as info FROM payments
							inner join bills on payments.bill_id = bills.id
							where bills.tenant_id = $tenant_id and bills.bill_type_id = 1) 
							UNION ALL (SELECT bills.id, bills.bill_no, bills.bill_amount as amount, bills.issue_date as issue_date, bills.issue_date as date_created, bills.payment_status as status_payment, concat(bills.bill_no,' bill ',bill_amount) as info  FROM bills 
							where bills.tenant_id = $tenant_id  and bills.bill_type_id = 1) ) 
							results ORDER BY date_created ASC";
	
			$sqlStatementWater ="SELECT * FROM ( 
						(SELECT payments.id as payment_id,payments.receipt_no, payments.amount_paid as amount, bills.issue_date as issue_date, payments.created_at as date_created,  bills.payment_status as status_payment, concat(payments.receipt_no,' payment ',payments.amount_paid) as info FROM payments
						inner join bills on payments.bill_id = bills.id
						where bills.tenant_id = $tenant_id and bills.bill_type_id = 2) 
						UNION ALL (SELECT bills.id, bills.bill_no, bills.bill_amount as amount, bills.issue_date as issue_date, bills.issue_date as date_created, bills.payment_status as status_payment, concat(bills.bill_no,' bill ',bill_amount) as info  FROM bills 
						where bills.tenant_id = $tenant_id  and bills.bill_type_id = 2) ) 
						results ORDER BY date_created ASC";

		}
		
		
		
		//kiraan B/F (bahagian atas statement)
		//kira berapa jumlah B/F - cut off sehingga hujung tahun sebelumnya dari tarikh yg dipilih. (cnth: pilihan tahun 2021, tahun sebelumnya 2020)
		if($cond_monthYear_BF != "" || $cond_date_BF != ""){
			  $sqlTotalBillAmountBeforeSelectedYear_rental = "SELECT sum(bill_amount) as totalBillAmountBeforeSelectedYear FROM `bills` where tenant_id = $tenant_id and bills.bill_type_id = 1 $cond_monthYear_BF";
			   $sqlTotalAmountPaidBeforeSelectedYear_rental = "SELECT sum(amount_paid) as totalAmountPaidBeforeSelectedYear FROM `payments` inner join bills on payments.bill_id = bills.id 
																where bills.tenant_id = '$tenant_id' and bills.bill_type_id = 1 $cond_date_BF ";
			$runTotalBillAmountBeforeSelectedYear_rental = mysqli_query($link, $sqlTotalBillAmountBeforeSelectedYear_rental);
			if($runTotalBillAmountBeforeSelectedYear_rental === FALSE){
				die(mysqli_error($link));
			}
			$runTotalAmountPaidBeforeSelectedYear_rental = mysqli_query($link, $sqlTotalAmountPaidBeforeSelectedYear_rental);
			if($runTotalAmountPaidBeforeSelectedYear_rental === FALSE){
				die(mysqli_error($link));
			}
			
			$resultTotalBillAmountBeforeSelectedYear_rental = mysqli_fetch_assoc($runTotalBillAmountBeforeSelectedYear_rental);
			$resultTotalAmountPaidBeforeSelectedYear_rental = mysqli_fetch_assoc($runTotalAmountPaidBeforeSelectedYear_rental);
			
			$totalBillAmountBeforeSelectedYear_rental = $resultTotalBillAmountBeforeSelectedYear_rental["totalBillAmountBeforeSelectedYear"];
			$totalAmountPaidBeforeSelectedYear_rental = $resultTotalAmountPaidBeforeSelectedYear_rental["totalAmountPaidBeforeSelectedYear"];
			$totalBF_rental = abs($totalBillAmountBeforeSelectedYear_rental) - abs($totalAmountPaidBeforeSelectedYear_rental);
			//$totalBF_rental = $totalBillAmountBeforeSelectedYear_rental - $totalAmountPaidBeforeSelectedYear_rental;
			
			if($totalBF_rental < 0){
				//$kiraan30percent_fromBF = 0.3 * abs($totalBF);
				//$kiraan30percent_fromBF = -1 * (0.3 * abs($totalBF));
			}
			
			$sqlTotalBillAmountBeforeSelectedYear_water = "SELECT sum(bill_amount) as totalBillAmountBeforeSelectedYear FROM `bills` where tenant_id = $tenant_id and bills.bill_type_id = 2 $cond_monthYear_BF";
			 $sqlTotalAmountPaidBeforeSelectedYear_water = "SELECT sum(amount_paid) as totalAmountPaidBeforeSelectedYear FROM `payments` inner join bills on payments.bill_id = bills.id where bills.tenant_id = '$tenant_id' and bills.bill_type_id = 2 $cond_date_BF ";
			$runTotalBillAmountBeforeSelectedYear_water = mysqli_query($link, $sqlTotalBillAmountBeforeSelectedYear_water);
			if($runTotalBillAmountBeforeSelectedYear_water === FALSE){
				die(mysqli_error($link));
			}
			$runTotalAmountPaidBeforeSelectedYear_water = mysqli_query($link, $sqlTotalAmountPaidBeforeSelectedYear_water);
			if($runTotalAmountPaidBeforeSelectedYear_water === FALSE){
				die(mysqli_error($link));
			}
			
			$resultTotalBillAmountBeforeSelectedYear_water = mysqli_fetch_assoc($runTotalBillAmountBeforeSelectedYear_water);
			$resultTotalAmountPaidBeforeSelectedYear_water = mysqli_fetch_assoc($runTotalAmountPaidBeforeSelectedYear_water);
			
			$totalBillAmountBeforeSelectedYear_water = $resultTotalBillAmountBeforeSelectedYear_water["totalBillAmountBeforeSelectedYear"];
			$totalAmountPaidBeforeSelectedYear_water = $resultTotalAmountPaidBeforeSelectedYear_water["totalAmountPaidBeforeSelectedYear"];
			$totalBF = abs($totalBillAmountBeforeSelectedYear) - abs($totalAmountPaidBeforeSelectedYear);
			//$totalBF_water = $totalBillAmountBeforeSelectedYear_water - $totalAmountPaidBeforeSelectedYear_water;
			
			if($totalBF_water < 0){
				//$kiraan30percent_fromBF = 0.3 * abs($totalBF);
				//$kiraan30percent_fromBF = -1 * (0.3 * abs($totalBF));
			}
		}
		else if($cond_monthYear_BF == "" && $cond_date_BF == ""){
			$totalBF_rental = 0;
			$totalBF_water = 0;
		}
	
	
	//AGEING SQL PART
	//AGEING SQL PART
	include("sql_ageingPart.php");
	//AGEING SQL PART
	
	?>