<?php 
$tenant_id = $_GET["tenant_id"];
			//GET TENANT INFO
			include ("queryTenant.php");

			$resultTenant = mysqli_fetch_assoc($runTenant);
			$tenant_id = $resultTenant["id"];
			$tenant_name = $resultTenant["tenant_name"];
			$tenant_ic = $resultTenant["ic_no"];
			$company_name = $resultTenant["company"];
			$company_ssm = $resultTenant["ssm_no"];
			$unit_no = $resultTenant["unit_no"];
			$location = $resultTenant["location_name"];
			$tenant_telNo = $resultTenant["phone"];
			$tenant_phoneNo = $resultTenant["mobile"];
			$start_rent = $resultTenant["start_date"];
			$end_rent = $resultTenant["end_date"];
			$tenant_status = $resultTenant["status"];
			$project_name = $resultTenant["project_name"];
			$mailing_address = $resultTenant["mailing_address"];
			$gender = $resultTenant["gender"];
			$marital_status = $resultTenant["marital_status"];
			$tenant_dob = $resultTenant["tenant_dob"];
			$tenant_nationality = $resultTenant["tenant_nationality"];
			$tenant_religion = $resultTenant["tenant_religion"];
			$tenant_email = $resultTenant["tenant_email"];
			$rental_rate = $resultTenant["rental_rate"];
			
			//tenant status start--
			$tenant_status_show = "";
			if($tenant_status == "enable"){
				$tenant_status_show = "Active";
			}
			else if($tenant_status == "disable"){
				$tenant_status_show = "Deactive";
			}
			
			//tenant status END
			
			//query check deposit kat table management_fee
			//penyewa lama dalam table deposits
			//penyewa baru 2024 SEPTEMBER dalam table deposit_bills + deposit_bill_other_receipts
			
			//1. TABLE DEPOSITS
			$sqlDepositsCheck = "select  two_month_rental,utilities, management_fee, total_deposit from deposits where tenant_id = $tenant_id ";
			$runDepositsCheck = mysqli_query($link, $sqlDepositsCheck);
			if($runDepositsCheck === FALSE){
				die(mysqli_error($link));
			}
			 " sqlDepositsCheck:::".$countDepositExist = mysqli_num_rows($runDepositsCheck);
			if($countDepositExist > 0){
			
				//deposit info from tenant id 
				 $sqlDeposit = "select sum(two_month_rental) as two_month_rental, sum(utilities) as utilities, sum(management_fee) as management_fee, sum(total_deposit) as total_deposit from deposits 
													where tenant_id = $tenant_id "; //RETURN NULL IN ROW - SHOULD CHECK THE PROBLEM
				$runDeposit = mysqli_query($link, $sqlDeposit);
				if($runDeposit === FALSE){
					die(mysqli_error($link));
				}
				$sumTwoMonthRental = 0;
				$sumUtilities = 0;
				$sumMgtFee = 0;
				$sumDeposit = 0;
				$totalDepositSum = 0;
				$depositStatus = "";
				$total_deposit = 0;
				//echo "   DEPOSIT ROW:::::".$deposit_row = mysqli_num_rows($runDeposit);
				$deposit_tenantID = "";
				$deposit_button = "";
				$totalDepositShow = "";
				//if($deposit_row > 0){
					while($resultDeposit = mysqli_fetch_array($runDeposit)){
						$twoMonthRental = $resultDeposit["two_month_rental"];
						$utilities = $resultDeposit["utilities"];
						$management_fee = $resultDeposit["management_fee"];
						$total_deposit = $resultDeposit["total_deposit"];
						
					}
					
						$totalDepositSum = $twoMonthRental + $utilities ;
						$totalOthersPayment = $management_fee;
						if($totalDepositSum == $total_deposit && $totalDepositSum > 0)
							$depositStatus = "Paid";
						else if($totalDepositSum > 0 && $totalDepositSum < $total_deposit )
							$depositStatus = "Partial";
						$deposit_button = "Re-print";
						$totalDepositShow = "RM" . number_format($totalDepositSum,2,".", ",");
					
					
				//}
			}
			else {
				//2. check table deposit_bills
				$sqlDepositBillsCheck = "select * from deposit_bills where tenant_id = $tenant_id";
				$runDepositBillsCheck = mysqli_query($link, $sqlDepositBillsCheck);
				if($runDepositBillsCheck === FALSE){
					die(mysqli_error($link));
				}
				$countDepositBillsExist = mysqli_num_rows($runDepositBillsCheck);
				$totalDepositPayment = 0;
				
				if($countDepositBillsExist > 0){
					//kira total deposit based on deposit_bills
					$resultDepositBills = mysqli_fetch_assoc($runDepositBillsCheck);
					$two_month_rental_amount = $resultDepositBills["two_month_rental_amount"];
					$utility_amount = $resultDepositBills["utility_amount"];
					$depositStatus = ucfirst($resultDepositBills["payment_status"]);
					 "totalDepositBills:: ". $totalDepositBills = $two_month_rental_amount + $utility_amount;
					
					
					//check payment DEPOSIT exist or not first
					$sqlDepositPaymentsCheck = "select * from deposit_payments inner join deposit_bills on deposit_payments.deposit_bill_id = deposit_bills.id where deposit_bills.tenant_id = $tenant_id";
					$runDepositPaymentsCheck = mysqli_query($link, $sqlDepositPaymentsCheck);
					if($runDepositPaymentsCheck === FALSE){
						die(mysqli_error($link));
					}
					
					 "countDepositPaymentsCheck:: ". $countDepositPaymentsCheck = mysqli_num_rows($runDepositPaymentsCheck);
					if($countDepositPaymentsCheck > 0){
						//query untuk cari payment dalam deposit dan others receipt
						//deposit_payments table
						 "   sqlDepositPayments: " .$sqlDepositPayments = "select sum(deposit_payments.two_months_amount_paid) as two_month_rental, sum(deposit_payments.utility_amount_paid) as utilities, deposit_bills.id, deposit_bills.two_month_rental_amount, deposit_bills.utility_amount
												from deposit_payments inner join deposit_bills on deposit_payments.deposit_bill_id = deposit_bills.id where deposit_bills.tenant_id = $tenant_id";
						$runDepositPayments = mysqli_query($link, $sqlDepositPayments);
						if($runDepositPayments === FALSE){
							die(mysqli_error($link));
						}
						$countDepositPayments = mysqli_num_rows($runDepositPayments);
						if($countDepositPayments > 0){
							while($resultDepositPayments = mysqli_fetch_array($runDepositPayments)){
								$twoMonthRental = $resultDepositPayments["two_month_rental"];
								$utilities = $resultDepositPayments["utilities"];
								$totalDepositPayment = $twoMonthRental + $utilities;
							}
						}
						
					}
					else if($countDepositPaymentsCheck == 0){
						$totalDepositPayment = 0;
					}
					
					if($totalDepositBills == $totalDepositPayment && $totalDepositPayment != 0){
						$depositStatus = "Paid";
						$deposit_button = "Re-print";
					}
					else if($totalDepositBills > $totalDepositPayment && $totalDepositPayment != 0 ){
						$depositStatus = "Partial";
						$deposit_button = "Pay Now"; 
						$deposit_button_payNow = ""; 
					}
					else if($totalDepositPayment == 0 && $totalDepositBills > 0){
						 "if totalDepositPayment = 0 " ;
						$deposit_button = "Update Bill 1"; 
						$deposit_button_payNow = "Pay Now"; 
					}
					 "totalDepositPayment :: " . $totalDepositPayment;
					//$deposit_button = "Re-print"; 
					$totalDepositShow = "RM" . number_format($totalDepositPayment,2,".", ","); //show deposit paid by tenant
				}
				else{
					$deposit_button = "Create Bill"; 
					$depositStatus = "";
					
				}
				
				//3. check table deposit_bill_other_receipts
				echo "deposit_bill_other_receipts: " .$sqlDepositBillsOthersReceiptCheck = "select * from deposit_bill_other_receipts where tenant_id = $tenant_id";
				$runDepositBillsOthersReceiptCheck = mysqli_query($link, $sqlDepositBillsOthersReceiptCheck);
				if($runDepositBillsOthersReceiptCheck === FALSE){
					die(mysqli_error($link));
				}
				echo "countDepositBillsOthersReceiptExist" . $countDepositBillsOthersReceiptExist = mysqli_num_rows($runDepositBillsOthersReceiptCheck);
				$totalPaymentOthers  = 0;
				$stamping_charges_amount  = 0;
				$sst_charges_amount  = 0;
				$yp_charges_amount  = 0;
				if($countDepositBillsOthersReceiptExist > 0 ){
					//kira total amount deposit_bill_other_receipts
					$resultDepositBillsOtherReceipts = mysqli_fetch_assoc($runDepositBillsOthersReceiptCheck);
					$stamping_charges_amount = $resultDepositBillsOtherReceipts["stamping_charges_amount"];
					$sst_charges_amount = $resultDepositBillsOtherReceipts["sst_charges_amount"];
					$yp_charges_amount = $resultDepositBillsOtherReceipts["yp_charges_amount"];
					$totalDepositBillOtherReceipt = $stamping_charges_amount + $sst_charges_amount + $yp_charges_amount;
					
					//check payment others receipt exist or not
					$sqlDepositPaymentOthersCheck = "select * from deposit_payment_other_receipts 
												inner join deposit_bill_other_receipts on deposit_payment_other_receipts.deposit_bill_other_id = deposit_bill_other_receipts.id where tenant_id = $tenant_id";
					$runDepositPaymentOthersCheck = mysqli_query($link, $sqlDepositPaymentOthersCheck);
					if($runDepositPaymentOthersCheck === FALSE){
						die(mysqli_error($link));
					}	
					echo "countDepositPaymentOthersCheck" . $countDepositPaymentOthersCheck = mysqli_num_rows($runDepositPaymentOthersCheck);
					if($countDepositPaymentOthersCheck > 0){
						//deposit_payment_other_receipts table
						 "   sqlDepositPaymentOthers: ".$sqlDepositPaymentOthers = "select sum(stamping_paid) as stamping_paid, sum(sst_paid) as sst_paid, sum(yp_paid) as yp_paid, sum(total_charges_paid) as total_charges_paid, 
												deposit_bill_other_receipts.stamping_charges_amount,deposit_bill_other_receipts.sst_charges_amount,deposit_bill_other_receipts.yp_charges_amount 
												from deposit_payment_other_receipts 
												inner join deposit_bill_other_receipts on deposit_payment_other_receipts.deposit_bill_other_id = deposit_bill_other_receipts.id where tenant_id = $tenant_id";
						$runDepositPaymentOthers = mysqli_query($link, $sqlDepositPaymentOthers);
						if($runDepositPaymentOthers === FALSE){
							die(mysqli_error($link));
						}
						$countDepositPaymentOthers = mysqli_num_rows($runDepositPaymentOthers);
						if($countDepositPaymentOthers > 0){
							while($resultDepositPaymentOthers = mysqli_fetch_array($runDepositPaymentOthers)){
								$stamping_paid = $resultDepositPaymentOthers["stamping_paid"];
								$sst_paid = $resultDepositPaymentOthers["sst_paid"];
								$yp_paid = $resultDepositPaymentOthers["yp_paid"];
								$total_charges_paid = $resultDepositPaymentOthers["total_charges_paid"];
								$totalPaymentOthers = $stamping_paid + $sst_paid + $yp_paid;
								
							}
						}
					}
					if($totalDepositBillOtherReceipt == $totalPaymentOthers && $totalPaymentOthers  != 0 ){
						$depositStatus = "Paid";
						$deposit_button = "Re-print";
					}
					else if($totalDepositBillOtherReceipt > $totalPaymentOthers  && $totalPaymentOthers  != 0){
						$depositStatus = "Partial";
						$deposit_button = "Pay Now"; 
						$deposit_button_payNow = ""; 
					}
					else if($totalPaymentOthers == 0 && $totalDepositBillOtherReceipt > 0){
						$deposit_button = "Pay Now";
						if($stamping_charges_amount == 0.00 || $sst_charges_amount == 0.00 || $yp_charges_amount == 0.00 ){
							$deposit_button = "Update Bill 5"; 
							$deposit_button_payNow = "Pay Now"; 
						}
						//$deposit_button = "Update Bill 2"; 
						//$deposit_button_payNow = "Pay Now"; 
					}
					
					
					//$deposit_button = "Re-print"; 
					$totalDepositShow = "RM" . number_format($totalPaymentOthers,2,".", ","); //show deposit paid by tenant
				}
				else{
					$deposit_button = "Create Bill"; 
					$depositStatus = "";
				}
				
				$totalAllPayment = $totalDepositPayment + $totalPaymentOthers ; 
				$totalAllBilling = $totalDepositBills + $totalDepositBillOtherReceipt;
				
				if($countDepositBillsExist == 0 && $countDepositBillsOthersReceiptExist == 0){
					$deposit_button = "Create Bill"; 
					$depositStatus = "";
				}
				else if($countDepositBillsExist > 0 && $countDepositBillsOthersReceiptExist == 0){
					$deposit_button = "Update Bill 3"; 
					$deposit_button_payNow = "Pay Now"; 
					//check ada buat payment atau tidak
					if($countDepositPaymentsCheck > 0)
						$depositStatus = "Partial";
				}
				else if($countDepositBillsExist == 0 && $countDepositBillsOthersReceiptExist > 0){
					$deposit_button = "Update Bill 4"; 
					$deposit_button_payNow = "Pay Now"; 
					//check ada buat payment atau tidak
					if($countDepositPaymentOthersCheck  > 0)
						$depositStatus = "Partial";
				}
				else if($countDepositBillsExist > 0 && $countDepositBillsOthersReceiptExist > 0){
					//$deposit_button = "Pay Now 7"; 
					echo "deposit_button_payNow" .$deposit_button_payNow = "YES"; 
					echo "stamping_charges_amount" .$stamping_charges_amount; 
					if($countDepositPaymentsCheck > 0 && $countDepositPaymentOthersCheck  > 0){
						if($totalAllBilling == $totalAllPayment && $totalAllPayment != 0.00){
							$depositStatus = "Paid";
							$deposit_button = ""; 
							$totalDepositShow = "RM" . number_format($totalAllPayment,2,".", ","); //show deposit paid by tenant
						}
						else if($totalAllBilling > $totalAllPayment && $totalAllPayment != 0.00)
							$depositStatus = "Partial";
					}
					ELSE IF($countDepositPaymentsCheck == 0 && $countDepositPaymentOthersCheck  > 0){
						$deposit_button_payNow = "Pay Now"; 
					}
					ELSE IF($countDepositPaymentsCheck == 0 && $countDepositPaymentOthersCheck  == 0){
						$deposit_button_payNow = "Pay Now"; 
					}
					else if($stamping_charges_amount == 0.00 || $sst_charges_amount == 0.00 || $yp_charges_amount == 0.00 ){
						echo "deposit_button: " . $deposit_button = "Update Bill 5"; 
						$deposit_button_payNow = "Pay Now"; 
					}
					
						//$depositStatus = "Partial";
				}
				
					$totalDepositShow = "RM" . number_format($totalAllPayment,2,".", ","); //show deposit paid by tenant
			}
			
			
			//current rental based on tenant id
			$totalAmountPaid = 0;
			$bill_amount = 0;
			$diff = 0;
			$bill_show = 0.00;
			$currentRentalPaymentStatus = "";
			$sqlCurrenRental = "select  sum(payments.amount_paid) as amount_paid,  bills.bill_amount, bills.amount_due, bills.payment_status
								from payments inner join bills on payments.bill_id = bills.id
								where bills.tenant_id = $tenant_id and bills.issue_date like '%".date("Y-m-01")."%' and bills.bill_type_id = 1";
			$runCurrenRental = mysqli_query($link, $sqlCurrenRental);
			if($runCurrenRental === FALSE){
				die(mysqli_error($link));
			}
			$resultCurrenRental = mysqli_fetch_assoc($runCurrenRental);
			$totalAmountPaid = $resultCurrenRental["amount_paid"];
			$bill_amount = $resultCurrenRental["bill_amount"];
			$amount_due = $resultCurrenRental["amount_due"];
			$currentRentalPaymentStatus = $resultCurrenRental["payment_status"];
			
			if($bill_amount != null && $totalAmountPaid != null){
				$bill_show = $amount_due;
				if($amount_due == 0){
					$bill_show = $bill_amount;
				}
				$bill_show = "RM" . number_format($bill_show,2,".",",");
			}
			if($bill_amount == null && $totalAmountPaid == null){
				 $sqlBillForUnpaid = "select bill_amount, payment_status from bills where tenant_id = $tenant_id and  bills.issue_date like '%".date("Y-m-01")."%' and bills.bill_type_id = 1";
				$runBillForUnpaid = mysqli_query($link, $sqlBillForUnpaid);
				if($runBillForUnpaid === FALSE){
					die(mysqli_error($link));
				}
				$resultBillForUnpaid = mysqli_fetch_assoc($runBillForUnpaid);
				$bill_amount = $resultBillForUnpaid["bill_amount"];
				$currentRentalPaymentStatus = $resultBillForUnpaid["payment_status"];
				$bill_show = "RM" . number_format($bill_amount,2,".",",");
			}
			//previous Outstanding = total bill - total payment
			// total Bill
			$totalBills = 0;
			$totalPayments = 0;
			 ' sqlTotalBills : ' . $sqlTotalBills = "select   sum(bills.bill_amount) as total_bills from bills 
								where bills.tenant_id = $tenant_id and bills.issue_date < '".date("Y-m-01")."' and bills.bill_type_id = 1";
			$runTotalBills = mysqli_query($link, $sqlTotalBills);
			if($runTotalBills === FALSE){
				die(mysqli_error($link));
			}
			if($resultTotalBills = mysqli_fetch_assoc($runTotalBills)){
				$totalBills = $resultTotalBills["total_bills"];
			}
			//total payment
			 $sqlTotalPayments = "select    sum(payments.amount_paid) as total_payments from payments 
								inner join bills on payments.bill_id = bills.id
								where bills.tenant_id = $tenant_id and bills.issue_date < '".date("Y-m-01")."' and bills.bill_type_id = 1";
			$runTotalPayments = mysqli_query($link, $sqlTotalPayments);
			if($runTotalPayments === FALSE){
				die(mysqli_error($link));
			}
			if($resultTotalPayments = mysqli_fetch_assoc($runTotalPayments)){
				$totalPayments = $resultTotalPayments["total_payments"];
			}
			
			$diff_all  = $totalBills - $totalPayments;
			$Outstanding_amount = "";
			if($diff_all == 0)
				$Outstanding_amount = "-";
			else if($diff_all != 0)
				$Outstanding_amount = "RM" . number_format($diff_all,2,".", ",");
			
			
			//Rental History
			 $sqlRentalHistory = "SELECT * FROM (( select payments.id as payment_id, payments.receipt_no, bills.issue_date,bills.bill_type_id, payments.amount_paid, payments.created_at as created_at, users.name from payments
									inner join bills on payments.bill_id = bills.id
									inner join tenants on bills.tenant_id = tenants.id
									inner join users on payments.created_by = users.id
									where tenants.id = $tenant_id and bills.bill_type_id = 1 order by payments.created_at DESC)
									union 
									(select payments.id as payment_id, payments.receipt_no, bills.issue_date,bills.bill_type_id, payments.amount_paid, payments.created_at as created_at, users.name  from payments
									inner join bills on payments.bill_id = bills.id 
									inner join tenants on bills.tenant_id = tenants.id
									inner join users on payments.created_by = users.id
									where tenants.id = $tenant_id and bills.bill_type_id = 1 order by payments.created_at DESC))
									results  ORDER BY created_at DESC
									";
			$runRentalHistory = mysqli_query($link, $sqlRentalHistory);
			if($runRentalHistory === FALSE){
				die(mysqli_error($link));
			}
			
			 $sqlWaterHistory = "SELECT * FROM (( select payments.id as payment_id, payments.receipt_no, bills.issue_date,bills.bill_type_id, payments.amount_paid, payments.created_at as created_at, users.name from payments
									inner join bills on payments.bill_id = bills.id
									inner join tenants on bills.tenant_id = tenants.id
									inner join users on payments.created_by = users.id
									where tenants.id = $tenant_id and bills.bill_type_id = 2 order by payments.created_at DESC)
									union 
									(select payments.id as payment_id, payments.receipt_no, bills.issue_date,bills.bill_type_id, payments.amount_paid, payments.created_at as created_at, users.name from payments
									inner join bills on payments.bill_id = bills.id 
									inner join tenants on bills.tenant_id = tenants.id
									inner join users on payments.created_by = users.id
									where tenants.id = $tenant_id and bills.bill_type_id = 2 order by payments.created_at DESC))
									results  ORDER BY created_at DESC
									";
			
			$runWaterHistory = mysqli_query($link, $sqlWaterHistory);
			if($runWaterHistory === FALSE){
				die(mysqli_error($link));
			}
			
			 $sqlOthersHistory = "SELECT * FROM (( select payments.id as payment_id, payments.receipt_no, bills.issue_date,bills.bill_type_id, payments.amount_paid, payments.created_at as created_at, users.name from payments
									inner join payments_paymenttype_bridge on payments.id = payments_paymenttype_bridge.payment_id
									inner join payment_type on payments_paymenttype_bridge.payment_type_id = payment_type.id
									inner join bills on payments.bill_id = bills.id
									inner join tenants on bills.tenant_id = tenants.id
									inner join users on payments.created_by = users.id
									where tenants.id = $tenant_id and bills.bill_type_id NOT IN (1,2) order by payments.created_at DESC)
									union 
									(select payments.id as payment_id, payments.receipt_no, bills.issue_date,bills.bill_type_id, payments.amount_paid, payments.created_at as created_at, users.name from payments
									inner join bills on payments.bill_id = bills.id 
									inner join tenants on bills.tenant_id = tenants.id
									inner join users on payments.created_by = users.id
									where tenants.id = $tenant_id and bills.bill_type_id NOT IN (1,2) order by payments.created_at DESC))
									results  ORDER BY created_at DESC
									";
			$runOthersHistory = mysqli_query($link, $sqlOthersHistory);
			if($runOthersHistory === FALSE){
				die(mysqli_error($link));
			}
			
			//query to check outstanding selain tunggakan PHSSB (tunggakan LPHS, MBPJ, MBI)
			$location_id = $_SESSION["location_id"];
			$bill_type_id_others = 0;
			if($location_id == 2)
				$bill_type_id_others = 4;
			else if($location_id == 3)
				$bill_type_id_others = 3;
			else if($location_id == 4)
				$bill_type_id_others = 5;
			
			$maxOutstanding = 0.00;
			$others_outstanding = "-";
			   $sqlBillOutstandingOthers = "select bills.bill_type_id, bill_types.bill_type_name, bills.amount_due as others_outstanding from bills 
											inner join bill_types on bills.bill_type_id = bill_types.id 
											where bills.tenant_id = $tenant_id 
											and bills.bill_type_id = $bill_type_id_others AND (bills.payment_status = 'unpaid' or bills.payment_status = 'partial')";
			$runBillOutstandingOthers = mysqli_query($link, $sqlBillOutstandingOthers);
			if($runBillOutstandingOthers === FALSE){
				die(mysqli_error($link));
			}
			$countRowBillOutstandingOthers = mysqli_num_rows($runBillOutstandingOthers);
			if($countRowBillOutstandingOthers > 0 ){
				$resultBillOutstandingOthers = mysqli_fetch_assoc($runBillOutstandingOthers);
				$bill_type_name = $resultBillOutstandingOthers["bill_type_name"];
				$bill_type_id = $resultBillOutstandingOthers["bill_type_id"];
				$maxOutstanding = $resultBillOutstandingOthers["others_outstanding"];
				$others_outstanding = "RM" . number_format($resultBillOutstandingOthers["others_outstanding"],2);
			}
			
			$others_outstanding_sentences = "Outstanding for ";
			$sqlBillType = "select bill_type_name from bill_types where id = $bill_type_id_others ";
			$runBillType = mysqli_query($link, $sqlBillType);
			if($runBillType === FALSE){
				die(mysqli_error($link));
			}
			$resultBillType = mysqli_fetch_assoc($runBillType);
			$bill_type_name = $resultBillType["bill_type_name"];
			
			$others_outstanding_sentences .= "" . $bill_type_name;

?>