<?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 = "";
		
		$year_check = "";
		
		
		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'));
			
			 "YEAR CHECK::" .$year_check = $statement_year;
			
			IF($selected_cutOffPayment == true){
				
				$cond_selectedCutOffPayment = " and YEAR(payments.created_at) = '$statement_year'";
				$cond_selectedCutOffPayment_water = " and YEAR(payments_water.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_old ="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,concat(payments.created_at,' tarikh_keyin ',payments.updated_at) as period
								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 and payments.created_at < '2025-01-01' 
								 ORDER BY date_created ASC";
			
			
			$sqlStatementWater_new  ="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,concat(payments.created_at,' tarikh_keyin ',payments.updated_at) as period
								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 and payments.created_at > '2024-12-31')
								UNION ALL 
								(SELECT payments_water.id as payment_id,payments_water.receipt_no, payments_water.amount_paid as amount, bills_water.issue_date as issue_date, payments_water.created_at as date_created,  bills_water.payment_status as status_payment, 
									concat(payments_water.receipt_no,' payment ',payments_water.amount_paid) as info,concat(bills_water.date_reading_current,'/',bills_water.date_reading_before,'/',bills_water.reading_current,'/',bills_water.reading_before) as period 
									FROM payments_water
									inner join bills_water on payments_water.water_bill_id = bills_water.id
									where bills_water.tenant_id = $tenant_id $cond_selectedCutOffPayment_water)
									
								UNION ALL 
									(SELECT bills_water.id, bills_water.bill_invoice, bills_water.bill_amount as amount, bills_water.issue_date as issue_date, bills_water.issue_date as date_created, bills_water.payment_status as status_payment, 
									concat(bills_water.bill_invoice,' bill ',bill_amount) as info, concat(bills_water.date_reading_current,'/',bills_water.date_reading_before,'/',bills_water.reading_current,'/',bills_water.reading_before) as period
									FROM bills_water 
									where bills_water.tenant_id = $tenant_id and YEAR(issue_date) = '$statement_year') 
								) 
								results ORDER BY date_created ASC";
								
								
			$sqlStatementOthers ="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 not in (1,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.bill_type_id not in (1,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));
			
			$year_check = date("Y", 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%' ";
			 "cond_selectedCutOffPayment_water::" . $cond_selectedCutOffPayment_water = " and bills_water.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 ";
						
		 	" monthyear sqlStatementRental :: " .  $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";
		
			
			
		 	" monthyear sqlStatementWater_old :: " . 	$sqlStatementWater_old ="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 ,concat(payments.created_at,' tarikh_keyin ',payments.updated_at) as period
							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 and payments.created_at < '2025-01-01' 
							 ORDER BY date_created ASC";
							
		 	" monthyear sqlStatementWater_new :: " . 	 $sqlStatementWater_new ="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 ,concat(payments.created_at,' tarikh_keyin ',payments.updated_at) as period
							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 and payments.created_at > '2024-12-31')
							UNION ALL 
							(SELECT payments_water.id as payment_id,payments_water.receipt_no, payments_water.amount_paid as amount, bills_water.issue_date as issue_date, payments_water.created_at as date_created,  bills_water.payment_status as status_payment, 
									concat(payments_water.receipt_no,' payment ',payments_water.amount_paid) as info,concat(bills_water.date_reading_current,'/',bills_water.date_reading_before,'/',bills_water.reading_current,'/',bills_water.reading_before) as period 
									FROM payments_water
									inner join bills_water on payments_water.water_bill_id = bills_water.id
									where bills_water.tenant_id = $tenant_id $cond_selectedCutOffPayment_water)
									
							UNION ALL 
									(SELECT bills_water.id, bills_water.bill_invoice, bills_water.bill_amount as amount, bills_water.issue_date as issue_date, bills_water.issue_date as date_created, bills_water.payment_status as status_payment, 
									concat(bills_water.bill_invoice,' bill ',bill_amount) as info, concat(bills_water.date_reading_current,'/',bills_water.date_reading_before,'/',bills_water.reading_current,'/',bills_water.reading_before) as period
									FROM bills_water 
									where bills_water.tenant_id = $tenant_id and bills_water.issue_date like '%$firstDay%') 
							) 
							results ORDER BY date_created ASC";
							
		 	" monthyear sqlStatementOthers :: " . 	$sqlStatementOthers ="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 not in (1,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 not in (1,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;
				
				$year_check = date("Y", strtotime($firstDay));
				
				$cond_selectedCutOffPayment =  " and (bills.issue_date  >= '$dateFrom'  and bills.issue_date < '$date_cutOFF') and (payments.created_at >= '$dateFrom' and payments.created_at < '$date_cutOFF') ";
				$cond_selectedCutOffPayment_water =  " and (bills_water.issue_date  >= '$dateFrom'  and bills_water.issue_date < '$date_cutOFF') and (payments_water.created_at >= '$dateFrom' and payments_water.created_at < '$date_cutOFF') ";
				$sqlDate = " (bills.issue_date  >= '$dateFrom'  and bills.issue_date < '$date_cutOFF') ";
				$sqlDate_water = " (bills_water.issue_date  >= '$dateFrom'  and bills_water.issue_date < '$date_cutOFF') ";
				$infoTarikhTunggakan = " DARI ".date("d/m/Y",strtotime($dateFrom)) . " SEHINGGA " . date("d/m/Y",strtotime($dateTo));
				
		 " date sqlStatementRental ::  "	. 	 $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";
			 
							
							
		 " date sqlStatementWater_old ::  "	.		$sqlStatementWater_old ="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,concat(payments.created_at,' tarikh_keyin ',payments.updated_at) as period
							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 and payments.created_at < '2025-01-01'
							 ORDER BY date_created ASC";
							
		 " date sqlStatementWater_new ::  "	.		$sqlStatementWater_new ="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,concat(payments.created_at,' tarikh_keyin ',payments.updated_at) as period
							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 and payments.created_at > '2024-12-31') 
							UNION ALL 
							(SELECT payments_water.id as payment_id,payments_water.receipt_no, payments_water.amount_paid as amount, bills_water.issue_date as issue_date, payments_water.created_at as date_created,  bills_water.payment_status as status_payment, 
									concat(payments_water.receipt_no,' payment ',payments_water.amount_paid) as info,concat(bills_water.date_reading_current,'/',bills_water.date_reading_before,'/',bills_water.reading_current,'/',bills_water.reading_before) as period 
									FROM payments_water
									inner join bills_water on payments_water.water_bill_id = bills_water.id
									where bills_water.tenant_id = $tenant_id $cond_selectedCutOffPayment_water)
							UNION ALL 
									(SELECT bills_water.id, bills_water.bill_invoice, bills_water.bill_amount as amount, bills_water.issue_date as issue_date, bills_water.issue_date as date_created, bills_water.payment_status as status_payment, 
									concat(bills_water.bill_invoice,' bill ',bill_amount) as info, concat(bills_water.date_reading_current,'/',bills_water.date_reading_before,'/',bills_water.reading_current,'/',bills_water.reading_before) as period
									FROM bills_water 
									where bills_water.tenant_id = $tenant_id and $sqlDate_water ) 
							) 
							results ORDER BY date_created ASC";
							
		 " date sqlStatementOthers ::  "	.		$sqlStatementOthers ="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 not in (1,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 not in (1,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"]));
				$year_check = date("Y", strtotime($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'";
				$cond_selectedCutOffPayment_water = " and payments_water.created_at < '$date_cutOFF'";
				$sqlDate = " bills.issue_date < '$date_cutOFF' ";
				$sqlDate_water = " bills_water.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_old ="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 ,concat(payments.created_at,' tarikh_keyin ',payments.updated_at) as period
							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 and payments.created_at < '2025-01-01'
							 ORDER BY date_created ASC";
				
				$sqlStatementWater_new ="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 ,concat(payments.created_at,' tarikh_keyin ',payments.updated_at) as period
							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 and payments.created_at > '2024-12-31') 
							UNION ALL 
							(SELECT payments_water.id as payment_id,payments_water.receipt_no, payments_water.amount_paid as amount, bills_water.issue_date as issue_date, payments_water.created_at as date_created,  bills_water.payment_status as status_payment, 
									concat(payments_water.receipt_no,' payment ',payments_water.amount_paid) as info,concat(bills_water.date_reading_current,'/',bills_water.date_reading_before,'/',bills_water.reading_current,'/',bills_water.reading_before) as period 
									FROM payments_water
									inner join bills_water on payments_water.water_bill_id = bills_water.id
									where bills_water.tenant_id = $tenant_id $cond_selectedCutOffPayment_water)
							UNION ALL 
									(SELECT bills_water.id, bills_water.bill_invoice, bills_water.bill_amount as amount, bills_water.issue_date as issue_date, bills_water.issue_date as date_created, bills_water.payment_status as status_payment, 
									concat(bills_water.bill_invoice,' bill ',bill_amount) as info, concat(bills_water.date_reading_current,'/',bills_water.date_reading_before,'/',bills_water.reading_current,'/',bills_water.reading_before) as period
									FROM bills_water 
									where bills_water.tenant_id = $tenant_id and $sqlDate_water ) 
							) 
							results ORDER BY date_created ASC";
				
			  " sqlStatementOthers 3::" .	$sqlStatementOthers ="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 not in (1,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 not in (1,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 and bills.issue_date <= '$issueDate_cutOff') ) 
							results ORDER BY date_created ASC";
	
						
						
			$sqlStatementWater_old ="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,concat(payments.created_at,' tarikh_keyin ',payments.updated_at) as period
							FROM payments
							inner join bills on payments.bill_id = bills.id
							where bills.tenant_id = $tenant_id and bills.bill_type_id = 2  and payments.created_at < '2025-01-01' ORDER BY date_created ASC";
			
			$sqlStatementWater_new ="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,concat(payments.created_at,' tarikh_keyin ',payments.updated_at) as period
							FROM payments
							inner join bills on payments.bill_id = bills.id
							where bills.tenant_id = $tenant_id and bills.bill_type_id = 2  and payments.created_at > '2024-12-31') 
						UNION ALL 	
							(SELECT payments_water.id as payment_id,payments_water.receipt_no, payments_water.amount_paid as amount, bills_water.issue_date as issue_date, payments_water.created_at as date_created,  bills_water.payment_status as status_payment, 
							concat(payments_water.receipt_no,' payment ',payments_water.amount_paid) as info,concat(bills_water.date_reading_current,'/',bills_water.date_reading_before,'/',bills_water.reading_current,'/',bills_water.reading_before) as period 
							FROM payments_water
							inner join bills_water on payments_water.water_bill_id = bills_water.id
							where bills_water.tenant_id = $tenant_id )
							
						UNION ALL 
							(SELECT bills_water.id, bills_water.bill_invoice, bills_water.bill_amount as amount, bills_water.issue_date as issue_date, bills_water.issue_date as date_created, bills_water.payment_status as status_payment, 
							concat(bills_water.bill_invoice,' bill ',bill_amount) as info, concat(bills_water.date_reading_current,'/',bills_water.date_reading_before,'/',bills_water.reading_current,'/',bills_water.reading_before) as period
							FROM bills_water 
							where bills_water.tenant_id = $tenant_id ) 
						)	
						results ORDER BY date_created ASC";
			
		 	$sqlStatementOthers ="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 not in (1,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 not in (1,2)) ) 
						results ORDER BY date_created ASC";
			

		}
		
		$runSqlStatementRental = mysqli_query($link, $sqlStatementRental);
		$runSqlStatementWater_old = mysqli_query($link, $sqlStatementWater_old);
		$runSqlStatementWater_new = mysqli_query($link, $sqlStatementWater_new);
		$runSqlStatementOthers = mysqli_query($link, $sqlStatementOthers);
		
		if($runSqlStatementRental === FALSE){
			die(mysqli_error($link));
		}
		if($runSqlStatementWater_old === FALSE){
			die(mysqli_error($link));
		}
		if($runSqlStatementWater_new === FALSE){
			die(mysqli_error($link));
		}
		if($runSqlStatementOthers === FALSE){
			die(mysqli_error($link));
		}
		$countRowOthers = mysqli_num_rows($runSqlStatementOthers);
		$countRowWater_old = mysqli_num_rows($runSqlStatementWater_old);
		$countRowWater_new = mysqli_num_rows($runSqlStatementWater_new);
		$countRowRental = mysqli_num_rows($runSqlStatementRental);
		
		if($countRowRental == 0){
			 $sqlStatementRental ="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 and bills.issue_date <= '$issueDate_cutOff'
										 ORDER BY date_created ASC";
			$runSqlStatementRental = mysqli_query($link, $sqlStatementRental);							
			if($runSqlStatementRental === FALSE){
				die(mysqli_error($link));
			}
		}
		if($countRowOthers == 0){
		 	$sqlStatementOthers ="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 not in (1,2)
										 ORDER BY date_created ASC";
			$runSqlStatementOthers = mysqli_query($link, $sqlStatementOthers);							
			if($runSqlStatementRental === FALSE){
				die(mysqli_error($link));
			}
		}
		if($countRowWater_new == 0){
			$runSqlStatementWater_new ="SELECT bills_water.id, bills_water.bill_invoice, bills_water.bill_amount as amount, bills_water.issue_date as issue_date, bills_water.issue_date as date_created, bills_water.payment_status as status_payment, 
											concat(bills_water.bill_invoice,' bill ',bill_amount) as info, concat(bills_water.date_reading_current,'/',bills_water.date_reading_before,'/',bills_water.reading_current,'/',bills_water.reading_before) as period
											FROM bills_water 
											where bills_water.tenant_id = $tenant_id
										 ORDER BY date_created ASC";
			$runSqlStatementWater_new = mysqli_query($link, $sqlStatementWater_new);							
			if($runSqlStatementRental === FALSE){
				die(mysqli_error($link));
			}
		}
		
		//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;
		}
	
	
		//water - start
		
		$bakiTerdahulu_new = 0.00;
		$payment_new = 0.00;
		
		//IF($year_check >= "2025"){
			//1. dapatkan baki b/f end 31/12/2024
			$sqlBfEnd2024 = "SELECT * FROM `bill_water_bf_end2024` where tenant_id = $tenant_id";
			$runBfEnd2024= mysqli_query($link, $sqlBfEnd2024);
			if($runBfEnd2024=== FALSE){
				die(mysqli_error($link));
			}
			$resultBfEnd2024 = mysqli_fetch_assoc($runBfEnd2024);
			 "bf_end2024:". $bf_end2024 = $resultBfEnd2024["bf_end2024"];
			
			//if($year_check >= "2025"){
				//2. semak total bills_water 
				$date_start = "2025-01-01";
				$date_end = date("Y-m-d");
				 "year_check: ". $year_check;
				if($statementby == 'statement_year'){
					$year_end = $year_check - 1;
					$date_end = $year_end . "-12-31";
				}
				else if($statementby == 'statement_monthyear'){
					$date_monthYear = date("Y-m-01",strtotime($statement_monthyear));
					$date_end = date("Y-m-d",strtotime($date_monthYear." -1 day"));
				}
				else if($statementby == 'statement_date'){
					$dateFrom = date("Y-m-d",strtotime($_GET["dateFrom"]));
					$date_end = date("Y-m-d",strtotime($dateFrom." -1 day"));
				}
				else if($statementby == 'statement_ChooseDate'){
					$chooseDate = date("Y-m-d",strtotime($_GET["statement_ChooseDate"]));
					$date_end = date("Y-m-d",strtotime($chooseDate." -1 day"));
				}
					
				
				 " sqlBakiTerdahulu_new:: ".	$sqlBakiTerdahulu_new = "select sum(bills_water.bill_amount) as total_bill_amount from bills_water where tenant_id = $tenant_id and date_reading_current <= '$date_end' ";
					$runBakiTerdahulu_new= mysqli_query($link, $sqlBakiTerdahulu_new);
					if($runBakiTerdahulu_new === FALSE){
						die(mysqli_error($link));
					}
					$countResultBakiTerdahulu_new = mysqli_num_rows($runBakiTerdahulu_new);
					if($countResultBakiTerdahulu_new > 0){
						$resultBakiTerdahulu_new = mysqli_fetch_assoc($runBakiTerdahulu_new);
						 "bakiTerdahulu_new:".  $bakiTerdahulu_new = $resultBakiTerdahulu_new["total_bill_amount"];
					}
					
					//3. semak total payments_water
					//table baru
					 $sqlWaterPayment_new = "select sum(amount_paid) as payment_new from payments_water inner join bills_water on payments_water.water_bill_id = bills_water.id 
											where bills_water.tenant_id = $tenant_id and payments_water.created_at <= '$date_end'";
					$runWaterPayment_new= mysqli_query($link, $sqlWaterPayment_new);
					if($runWaterPayment_new=== FALSE){
						die(mysqli_error($link));
					}
					$countResultWaterPayment_new = mysqli_num_rows($runWaterPayment_new);
					if($countResultWaterPayment_new > 0){
						$resultWaterPayment_new = mysqli_fetch_assoc($runWaterPayment_new);
						 "payment_new:".  $payment_new = $resultWaterPayment_new["payment_new"];
					}
					//table lama
					if($date_start < $date_end){
						  "sqlWaterPayment_old: ". $sqlWaterPayment_old = "select sum(amount_paid) as payment_old from payments 
																	inner join bills on payments.bill_id = bills.id 
																	where bills.bill_type_id = 2 and 
																	bills.tenant_id = $tenant_id and 
																	payments.created_at between '$date_start' and '$date_end'";
						$runWaterPayment_old= mysqli_query($link, $sqlWaterPayment_old);
						if($runWaterPayment_old=== FALSE){
							die(mysqli_error($link));
						}
						$countResultWaterPayment_old = mysqli_num_rows($runWaterPayment_old);
						if($countResultWaterPayment_old > 0){
							$resultWaterPayment_old = mysqli_fetch_assoc($runWaterPayment_old);
							 "payment_old:". $payment_old = $resultWaterPayment_old["payment_old"];
						}
					}
				
			 "Y-m:" . date("Y-m",strtotime($date_end));
			
			 "totalBF_water_new: ". $totalBF_water_new = $bf_end2024;
		
		//water - end
	
	
	//AGEING SQL PART
	include("sql_ageingPart.php");
	//AGEING SQL PART
	
	?>