

<?php
// 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));
}

$_SESSION["cutOffPayment"] = "";
$_SESSION['statement_year']="";
$_SESSION['statement_monthyear']="";

	$kiraan30percent_fromBF = 0;
	$totalBF = 0;
	$firstYearDate = "";
	$_SESSION["cutOffPayment"] = "";
	$tenant_id = $_GET["tenant_id"];
	//GET TENANT INFO
	include ("queryTenant.php");
	
	$resultTenant = mysqli_fetch_assoc($runTenant);
	$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"];
	//$type_reg = $resultTenant["type_reg"];
	//$ref_name = $resultTenant["ref_name"];
	//$ref_contact = $resultTenant["ref_contact"];
	//$company_director = $resultTenant["director"];
	//$company_business = $resultTenant["business"];
	//$company_tel = $resultTenant["tel_com"];
	$rental_program = $resultTenant["project_name"];
	//$RP = $resultTenant["RP"];
	//$RP = $resultTenant["rental_code"];
	
	
	
	if($start_rent != "0000-00-00 00:00:00" && $end_rent != "0000-00-00 00:00:00"){
		$start_rent = date("d/m/Y",strtotime($start_rent));
		$end_rent = date("d/m/Y",strtotime($end_rent));
	}
	else if($start_rent == "0000-00-00 00:00:00" && $end_rent == "0000-00-00 00:00:00"){
		$start_rent = "No Record";
		$end_rent = "No Record";
	}
	$pic_syarikat = "";
	$contact_no = "";
	if($tenant_telNo != "" && $tenant_phoneNo == "")
		$contact_no = $tenant_telNo;
	else if($tenant_telNo == "" && $tenant_phoneNo != "")
		$contact_no = $tenant_phoneNo;
	else if($tenant_telNo != "" && $tenant_phoneNo != "")
		$contact_no = $tenant_telNo . " / " . $tenant_phoneNo;
	else if($tenant_telNo == "" && $tenant_phoneNo == "")
		$contact_no = "-";
	
	if($ref_name != "" && $ref_contact == "")
		$pic_syarikat = $tenant_telNo;
	else if($ref_name == "" && $ref_contact != "")
		$pic_syarikat = $tenant_phoneNo;
	else if($ref_name != "" && $ref_contact != "")
		$pic_syarikat = $pic_syarikat . " (" . $ref_contact .")";
	else if($ref_name == "" && $ref_contact == "")
		$pic_syarikat = "-";
	
	$maklumatHartanah = $unit_no . ", " . $location;
	
	//GET DEPOSIT INFO
	 $sqlDeposit = "select * from deposits where tenant_id = $tenant_id";
	$runSqlDeposit = mysqli_query($link, $sqlDeposit);
	if($runSqlDeposit === FALSE){
		die(mysqli_error($link));
	}
	$cutOffDate = date("Y-m-d");
	//GET STATEMENT INFO FROM BILL AND PAYMENT TABLE 
	//echo "projectName111: ".$_SESSION["projectName"];

	$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.created_at 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.created_at 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";
	
	
	$runSqlStatementRental = mysqli_query($link, $sqlStatementRental);
	$runSqlStatementWater = mysqli_query($link, $sqlStatementWater);
	
	if($runSqlStatementRental === FALSE){
		die(mysqli_error($link));
	}
	if($runSqlStatementWater === FALSE){
		die(mysqli_error($link));
	}
	
	$sqlYear = "select YEAR(bills.issue_date) AS year from bills where tenant_id = $tenant_id group by year order by year desc";
	$runSqlYear = mysqli_query($link, $sqlYear);
	if($runSqlYear === FALSE){
		die(mysqli_error($link));
	}
	$sqlMonthYear = "select issue_date AS monthyear from bills where tenant_id = $tenant_id  order by issue_date desc";
	$runSqlMonthYear = mysqli_query($link, $sqlMonthYear);
	if($runSqlMonthYear === FALSE){
		die(mysqli_error($link));
	}
	
	if($_SESSION["projectName"] != "PPR"){
		//GET DATE FOR TENANT NOT ELIGABLE FOR 30% OF MONTLY RENTAL
		$queryDateStop30Percent = "";
		$sqlInfo30Percent = "select * from infostop30percent where tenant_id = $tenant_id and infostatus = 'enable'";
		$runInfo30Percent = mysqli_query($link, $sqlInfo30Percent);
		if($runInfo30Percent === FALSE){
			die(mysqli_error($link));
		}
		$countRowInfo30Percent = mysqli_num_rows($runInfo30Percent);
		if($countRowInfo30Percent == 1){
			$resultInfo30Percent = mysqli_fetch_assoc($runInfo30Percent);
			$dateStop30Percent = $resultInfo30Percent["month_stop30percent"];
			$queryDateStop30Percent = "and bill.issue_date < '$dateStop30Percent'";
		}
	}
	
	if(isset($_GET["searchStatement"])){
		$statement_year = $_GET["statement_year"];
		$statement_monthyear = $_GET["statement_monthyear"];
		$tenant_id = $_GET["tenant_id"];
		$selected_cutOffPayment = $_GET["cutOffPayment"];
		$_SESSION["cutOffPayment"] = $selected_cutOffPayment;
		$_SESSION['statement_year']=$_GET['statement_year'];
		$_SESSION['statement_monthyear']=$_GET['statement_monthyear'];
		
		
		$cond_selectedCutOffPayment = "";
		$cond_monthYear_BF = "";
		$cond_date_BF = "";
		if($statement_year != "" && $statement_monthyear == ""){
			$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_cutOFF'";
			}
			
			
			$cond_monthYear_BF = " and year(issue_date) < '$statement_year' ";
			$cond_date_BF = " and payments.created_at < '$firstYearDate' ";
			
			$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 and YEAR(payments.created_at) = '$statement_year' $cond_selectedCutOffPayment) 
									UNION ALL 
									(SELECT bills.id, bills.bill_no, bills.bill_amount as amount, bills.issue_date as issue_date, bills.created_at 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 = 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  and YEAR(payments.created_at) = '$statement_year' $cond_selectedCutOffPayment) 
									UNION ALL 
									(SELECT bills.id, bills.bill_no, bills.bill_amount as amount, bills.issue_date as issue_date, bills.created_at 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($statement_year == "" && $statement_monthyear != ""){
			$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' ";
			
			$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.created_at 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.created_at 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";
			
			
			$date_monthYear = date("Y-m",strtotime($statement_monthyear));
			$lastdate = $date_monthYear . "-01";
			$lastdate = date("Y-m-t", strtotime($lastdate));			
			$cutOffDate = $lastdate;
		}
		else if($statement_year == "" && $statement_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) 
								UNION ALL (SELECT bills.id, bills.bill_no, bills.bill_amount as amount, bills.issue_date as issue_date, bills.created_at 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.created_at 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";

			
			$cutOffDate = date('Y-m-d');
		}
		$runSqlStatementRental = mysqli_query($link, $sqlStatementRental);
		if($runSqlStatementRental === FALSE){
			die(mysqli_error($link));
		}
		$runSqlStatementWater = mysqli_query($link, $sqlStatementWater);
		if($runSqlStatementWater === 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 = "SELECT sum(bill_amount) as totalBillAmountBeforeSelectedYear FROM `bills` where tenant_id = $tenant_id $cond_monthYear_BF";
			$sqlTotalAmountPaidBeforeSelectedYear = "SELECT sum(amount_paid) as totalAmountPaidBeforeSelectedYear FROM `payments` inner join bills on payments.bill_id = bills.id where bills.tenant_id = '$tenant_id' $cond_date_BF";
			$runTotalBillAmountBeforeSelectedYear = mysqli_query($link, $sqlTotalBillAmountBeforeSelectedYear);
			if($runTotalBillAmountBeforeSelectedYear === FALSE){
				die(mysqli_error($link));
			}
			$runTotalAmountPaidBeforeSelectedYear = mysqli_query($link, $sqlTotalAmountPaidBeforeSelectedYear);
			if($runTotalAmountPaidBeforeSelectedYear === FALSE){
				die(mysqli_error($link));
			}
			
			$resultTotalBillAmountBeforeSelectedYear = mysqli_fetch_assoc($runTotalBillAmountBeforeSelectedYear);
			$resultTotalAmountPaidBeforeSelectedYear = mysqli_fetch_assoc($runTotalAmountPaidBeforeSelectedYear);
			
			$totalBillAmountBeforeSelectedYear = $resultTotalBillAmountBeforeSelectedYear["totalBillAmountBeforeSelectedYear"];
			$totalAmountPaidBeforeSelectedYear = $resultTotalAmountPaidBeforeSelectedYear["totalAmountPaidBeforeSelectedYear"];
			$totalBF = abs($totalBillAmountBeforeSelectedYear) - abs($totalAmountPaidBeforeSelectedYear);
			
			if($totalBF < 0){
				//$kiraan30percent_fromBF = 0.3 * abs($totalBF);
				//$kiraan30percent_fromBF = -1 * (0.3 * abs($totalBF));
			}
		}
		
		
	}
	
	//AGEING SQL PART
	IF($selected_cutOffPayment == true){
		//echo "\n CUT OFF PAYMENT: ". $selected_cutOffPayment;
		if($statement_year != "" && $statement_monthyear == ""){
				
					$sqlAgeing = " SELECT * FROM (
						(select sum(payments.amount_paid) + bills.amount_due as total_outstanding , bill.* 
							from payments 
							inner join bills on payments.bill_id = bills.id 
							where bills.issue_date <= '$cutOffDate' 
							and (lower(bills.payment_status) = 'paid' or lower(bills.payment_status) = 'partial') 
							and bills.tenant_id = $tenant_id 
							and payments.created_at > '$cutOffDate' 
							group by bills.issue_date											) 
						UNION ALL 
						(select sum(bills.amount_due) as total_outstanding, bills.* 
							from bills 
							where bills.issue_date <= '$cutOffDate' 
							and (lower(bills.payment_status) = 'unpaid' or lower(bills.payment_status) = 'partial') and tenant_id = $tenant_id group by bills.issue_date) 
					) results group by  bills.issue_date ORDER BY bills.issue_date ASC";
				
					
					
		}
		else if($statement_year == "" && $statement_monthyear != ""){
			
				$sqlAgeing = " SELECT * FROM (
						(select sum(payments.amount_paid) as total_outstanding , bills.* 
							from payments 
							inner join bills on payments.bill_id = bill.id 
							where bills.issue_date <= '$cutOffDate' 
							and (lower(bills.payment_status) = 'paid' or lower(bills.payment_status) = 'partial') 
							and bills.tenant_id = $tenant_id 
							and payments.created_at > '$cutOffDate' 
							group by bills.issue_date											) 
						UNION ALL 
						(select sum(bills.amount_due) as total_outstanding, bills.* 
							from bills 
							where bill.issue_date <= '$cutOffDate' 
							and (lower(bills.payment_status) = 'unpaid' or lower(bills.payment_status) = 'partial') and bills.tenant_id = $tenant_id group by issue_date) 
					) results group by issue_date ORDER BY issue_date ASC";
			
		}
		else if($statement_year == "" && $statement_monthyear == ""){
			
				$sqlAgeing = "select sum(bills.amount_due) as total_outstanding, bills.* from bill where issue_date <= '$cutOffDate' and (lower(paymet_status) = 'unpaid' or lower(paymet_status) = 'partial') and tenant_id = $tenant_id group by issue_date ";
			
		}
	}
	else{	
		
			$sqlAgeing = "select sum(bills.amount_due) as total_outstanding, bills.* from bills where issue_date <= '$cutOffDate' and (lower(payment_status) = 'unpaid' or lower(payment_status) = 'partial') and tenant_id = $tenant_id group by issue_date ";
		
	}
	
	//AGEING SQL PART END
	//ECHO $sqlAgeing;
	
?>
<div align="center">
	<form action="<?php $_SERVER['PHP_SELF']; ?>" method="get" class="form-horizontal">
		<input type="hidden" name="tenant_id" value="<?php echo $tenant_id?>">
		<input type="hidden" name="login" value="<?php echo $_SESSION["username"]?>">
		<div class="card">
			<div class="card-header py-3">
				<h5 class="m-0 font-weight-bold text-primary">STATEMENT OPTION</h5>
			</div>
			<div class="card-body">
				<div class="row form-group">
					<div class="col col-md-3">
						<label for="text-input" class=" form-control-label">STATEMENT BY YEAR:</label>
					</div>
					<div class="col-12 col-md-9">
						<select name="statement_year" class="form-control">
							<option value="">Please Select Year</option>
							<?php while($resultYear = mysqli_fetch_array($runSqlYear)){?>
									<option value="<?php echo $resultYear["year"]?>" <?php echo ($_SESSION["statement_year"] ==  $resultYear["year"])  ? "selected" : "" ?> ><?php echo $resultYear["year"]?></option>
							<?php } ?>
						</select>
					</div>
				</div>
				<div class="row form-group">
					<div class="col col-md-3">
						<label for="text-input" class=" form-control-label">STATEMENT BY MONTH AND YEAR:</label>
					</div>
					<div class="col-12 col-md-9">
						<select name="statement_monthyear" class="form-control">
							<option value="">Please Select Month and Year</option>
						<?php while($resultMonthYear = mysqli_fetch_array($runSqlMonthYear)){
									$monthYear = date("M Y", strtotime($resultMonthYear["monthyear"]));
							?>
								<option value="<?php echo $resultMonthYear["monthyear"]?>" <?php echo ($_SESSION["statement_monthyear"] ==  $resultMonthYear["monthyear"])  ? "selected" : "" ?>><?php echo $monthYear?></option>
						<?php } ?>
						</select>
					</div>
				</div>
			</div>
			<div class="card-footer">
				<button type="submit" name="searchStatement" class="btn btn-primary btn-sm">
					<i class="fa fa-dot-circle-o"></i> Submit
				</button>
				<button type="reset" class="btn btn-danger btn-sm">
					<i class="fa fa-ban"></i> Reset
				</button>
			</div>
		</div>
	</form>
</div>

<br/>
<div align="center">
	<div class="card">
		<div class="card-header py-3">
			<h5 class="m-0 font-weight-bold text-primary">SHOWING RESULT</h5>
			<button type="button" class="btn btn-outline-primary btn-sm" onclick="printStatement('<?php echo $tenant_id;?>')" class="btn btn-outline-warning btn-sm">
				<i class="fa fa-print"></i>&nbsp; PRINT STATEMENT
			</button>
			<button type="button" class="btn btn-outline-primary btn-sm" onclick="exportToCSV('<?php echo $tenant_id;?>','<?php echo $sqlStatement?>','<?php echo $cutOffDate?>','<?php echo $sqlAgeing?>','<?php echo $totalBF?>','<?php echo $firstYearDate?>')" class="btn btn-outline-warning btn-sm">
				<i class="fa fa-table"></i>&nbsp; EXPORT TO CSV
			</button>
		</div>
		<div class="card-body">
			<div class="card-header py-3">
				<h5 class="m-0 font-weight-bold text-primary">TENANT INFO | <a href='Up_tenant_detail.php?name = <?php echo $name?>&tenant_id=<?php echo $tenant_id?>'>EDIT</a></h5>
			</div>
			<div class="card-body">
				<div class="table-responsive">
					<table class="table table-bordered table-hover table-sm" id="dataTable" width="100%" cellspacing="0" style="font-size: 12px">
						<tr>
							<td width='30%'><strong>NAME</strong></td>
							
							<td width='50%'><?php echo strtoupper($tenant_name)?></td>
						</tr>
						<tr>
							<td width='30%'><strong>IC NO.</strong></td>
							
							<td width='50%'><?php echo $tenant_ic?></td>
						</tr>
						<tr>
							<td width='30%'><strong>CONTACT NO.</strong></td>
							
							<td width='50%'><?php echo $contact_no?></td>
						</tr>
						
						<tr>
							<td width='30%'><strong>TYPE OF PROPERTY</strong></td>
							
							<td width='50%'><?php echo strtoupper($rental_program)?></td>
						</tr>
						<tr>
							<td width='30%'><strong>PROPERTY LOCATION</strong></td>
							
							<td width='50%'><?php echo strtoupper($maklumatHartanah)?></td>
						</tr>
						<tr>
							<td width='30%'><strong>RENTAL START</strong></td>
							
							<td width='50%'><?php echo $start_rent?></td>
						</tr>
						<tr>
							<td width='30%'><strong>RENTAL END</strong></td>
							
							<td width='50%'><?php echo $end_rent?></td>
						</tr>
						
					</table>
				</div>
			</div>
			<div class="card-header py-3">
				<h5 class="m-0 font-weight-bold text-primary">DEPOSIT INFO</h5>
			</div>
			<div class="card-body">
				<div class="table-responsive">
					<table class="table table-bordered table-hover table-sm" id="dataTable" width="100%" cellspacing="0" style="font-size: 12px">
						<tr align="center">
							<td rowspan="2"><b>#</b></td>
							<td rowspan="2"><b>DATE</b></td>
							<td rowspan="2"><b>INVOICE / RECEIPT</b></td>
							<td rowspan="2"><b>DESCRIPTION</b></td>
							<td><b> (Db)</b></td>
							<td><b> (Cr)</b></td>
							<td><b>BALANCE</b></td>
						</tr>
						<tr align="center">
							<td><b>(RM)</b></td>
							<td><b>(RM)</b></td>
							<td><b>(RM)</b></td>
						</tr>
						<?php 
						$no=1;
						$totalDepo=0;
						$countRowDeposit = 0;
						$countRowDeposit = mysqli_num_rows($runSqlDeposit);
						if($countRowDeposit == 0){
							?>
						<tr>
							<td colspan="10" align="center">
								~No Record Found~
							</td>
						</tr>
							<?php
						}
						else if($countRowDeposit > 0){
							while($resultDeposit = mysqli_fetch_assoc($runSqlDeposit)){
								$created_at = $resultDeposit["created_at"];
								$created_at = date("d/m/Y",strtotime($created_at));
								$pay_id = $resultDeposit["receipt_no"];
								$tenant_id = $resultDeposit["tenant_id"];
								$depo_resit = "D" . $pay_id;
								$keterangan = "";
								//$month_amount = $resultDeposit["month_amount"];
								$two_month_rental = $resultDeposit["two_month_rental"];
								//$adv_rent = $resultDeposit["adv_rent"];
								$uti_fee = $resultDeposit["utilities"];
								$stamp_fee = $resultDeposit["stamp_fee"];
								$management_fee = $resultDeposit["management_fee"];
								$other_fee = $resultDeposit["other_fee"];
								$depo_refno = $resultDeposit["depo_refno"];
								$pay_proven = $resultDeposit["pay_proven"];
								
								//$totalDepo = $two_month_rental + $adv_rent + $uti_fee + $stamp_fee + $management_fee + $other_fee;
								$totalDepo += $two_month_rental  + $uti_fee + $stamp_fee + $management_fee + $other_fee;
								
								$rowspan = 0;
								if($two_month_rental != 0)
									$rowspan++;
								//if($adv_rent != 0)
									//$rowspan++;
								if($uti_fee != 0)
									$rowspan++;
								if($stamp_fee != 0)
									$rowspan++;
								if($management_fee != 0)
									$rowspan++;
								if($other_fee != 0)
									$rowspan++;
								$totalDepoEcho = "";
								if($depo_refno != "" || $depo_refno == ""){
									$rowspan++;
									$keterangan = "Ref: $depo_refno";
								}
								
								
							?>
							<tr>
								<td align="center" valign="top" rowspan="<?php echo $rowspan?>"><?php echo $no;?></td>
								<td align="center" valign="top" rowspan="<?php echo $rowspan?>"><?php echo created_at;?></td>
								<td valign="top" rowspan="<?php echo $rowspan?>">
									<a href="printPayment3.php?tenant_id=<?php echo $tenant_id?>&pay_id=<?php echo $pay_id?>" target='_blank'><?php echo $depo_resit;?></a>
								</td>
								<?php
									if($depo_refno != ""){
								?>
								<td valign="top" >
									<a href='Uploads/FileDepoPayment/<?php echo $pay_proven?>' target='_blank'><?php echo $keterangan;?></a>
								</td>
								<td></td>
								<td></td>
								<td></td>
									<?php 
									}
										
									?>
									
							</tr>
							<?php
								if($two_month_rental != 0){
									//$no++;
									?>
							<tr>
								
								<td><?php echo "Two Month Deposit";?></td>
								<td align="right"></td>
								<td align="right"><?php echo number_format($two_month_rental,2);?></td>
								<td align="right"></td>
							</tr><?php 
								}
								
								if($uti_fee != 0){
									?>
							<tr>
								
								<td><?php echo "Utilities Fee";?></td>
								<td align="right"></td>
								<td align="right"><?php echo number_format($uti_fee,2);?></td>
								<td align="right"></td>
							</tr><?php 
								}
								if($stamp_fee != 0){
									?>
							<tr>
								
								<td><?php echo "Stamping Fee";?></td>
								<td align="right"></td>
								<td align="right"><?php echo number_format($stamp_fee,2);?></td>
								<td align="right"></td>
							</tr><?php 
								}
								if($management_fee != 0){
									?>
							<tr>
								
								<td><?php echo "Management Fee";?></td>
								<td align="right"></td>
								<td align="right"><?php echo number_format($management_fee,2);?></td>
								<td align="right"></td>
							</tr><?php 
								}
								if($other_fee != 0){
									?>
							<tr>
								
								<td><?php echo "Others Fee";?></td>
								<td align="right"></td>
								<td align="right"><?php echo number_format($other_fee,2);?></td>
								<td align="right"></td>
							</tr><?php 
								}
							$no++;
							}?>
							<tr>
								<td colspan="4" align="right"><b>Total (RM)</b></td>
								<td align="right"></td>
								<td align="right"><b><?php echo number_format($totalDepo,2)?></b></td>
								<td align="right"></td>
							</tr>
							<?php } ?>
					</table>
				</div>
			</div>
			<div class="card-header py-3">
				<h5 class="m-0 font-weight-bold text-primary">RENTAL INFO</h5>
			</div>
			<div class="card-body">
				<div class="table-responsive">
					<table class="table table-bordered table-hover table-sm" id="dataTable" width="100%" cellspacing="0" style="font-size: 12px">
						<tr align="center">
							<td rowspan="2"><b>#</b></td>
							<td rowspan="2"><b>DATE</b></td>
							<td rowspan="2"><b>INVOICE / RECEIPT NO</b></td>
							<td rowspan="2"><b>DESCRIPTION</b></td>
							<td><b> (Db)</b></td>
							<td><b>(Cr)</b></td>
							<td><b>BALANCE</b></td>
							
						</tr>
						<tr align="center">
							<td><b>(RM)</b></td>
							<td><b>(RM)</b></td>
							<td><b>(RM)</b></td>
							
						</tr>
						<?php 
						$no=1;
						$showBFDate = "";
						if($firstYearDate != ""){
							$showBFDate = date("d/m/Y",strtotime($firstYearDate));
						}
						?>
						<tr>
							<td align="center"><?php echo $no?></td>
							<td align="center"><?php echo $showBFDate;?></td>
							<td>&nbsp;</td>
							<td>B/F</td>
							<td>&nbsp;</td>
							<td>&nbsp;</td>
							<td align="right"><?php echo number_format($totalBF,2);?></td>
							
						</tr>
						<?php 
						//$no=1;
						$totalPaid = 0;
						$totalUnpaid = 0;
						$totalPaidList = "";
						$totalUnpaidList = "";
						$totalBaki = 0;
						$bill = 0;
						$payment = 0;
						$kiraan30Percent = 0;
						$total_kiraan30Percent = 0;
						$month = "";
						$year = "";
						$slashPosition = 0;
						$RP_rentalMonth = "";
						
						$layak = "";
						$bill_id_smartsewa = "";
						while($resultStatement = mysqli_fetch_array($runSqlStatementRental)){
							$no++;
							$id = $resultStatement["payment_id"];
							$amount = $resultStatement["amount"];
							$date_created = $resultStatement["date_created"];
							$pay_info = $resultStatement["pay_info"];
							$issue_date = $resultStatement["issue_date"];
							$status_payment = $resultStatement["status_payment"];
							$info = $resultStatement["info"];
							$pay_type = $resultStatement["pay_type"];
							
							
							$date_created = date("d/m/Y", strtotime($date_created));
							
							if (strpos($issue_date, '/') !== false) {
								$issue_date = $resultStatement["issue_date"];
								$slashPosition = strpos($issue_date,"/");
								$month = substr($issue_date, 0, $slashPosition);
								$year = substr($issue_date, $slashPosition + 1 , 6);
								$issue_date = $year . "-" . $month ."-01";
								$RP_rentalMonth = $year . "-" . $month ."-07";
								$cutOff_rentalMonth = $year . "-" . $month ."-08";
								$issue_date = date("M Y", strtotime($issue_date));
								$RP_rentalMonth = date("Y-m-d", strtotime($RP_rentalMonth));
								$cutOff_rentalMonth = date("Y-m-d", strtotime($cutOff_rentalMonth));
							}
							else{
								$issue_date = date("M Y", strtotime($issue_date));
								$RP_rentalMonth = date("Y-m-07", strtotime($issue_date));
								$cutOff_rentalMonth = date("Y-m-08", strtotime($issue_date));
							}
							if (strpos($pay_info, '00:00:00') !== false) {
								$pay_info = $issue_date;
							}
							else{
								$pay_info = $pay_info . " " . $issue_date;
							}
							
							//if(strtolower($status_payment) == 'paid' || strtolower($status_payment) == 'partial'){
							$payment_id = "";
							if (strpos($info, 'payment') !== false) {
								$id = strstr($info, ' payment ', true);
								
								$bill_id_smartsewa = $bill_id;
								$totalPaidList .= $amount;
								$totalPaid += $amount;
							}
							if (strpos($info, 'bill') !== false) {
								$totalUnpaid += $amount;
								$totalUnpaidList .= $amount;
								
								$id = strstr($info, ' bill ', true);
								
								
								if(strtolower($status_payment) == 'paid' || strtolower($status_payment) == 'partial'){
									
								}
								
							}
							
							?>
						<tr>
							<td align="center"><?php echo $no?></td>
							<td align="center"><?php echo $date_created?></td>
							<td><?php echo $id?></td>
							<td><?php echo $pay_info?></td>
							<?php if (strpos($info, 'bill') !== false) { 
									$bill = $amount;
									if($totalBaki == 0 && abs($totalBF) != 0)
										$totalBaki =  $totalBF;
									
									$totalBaki = $totalBaki + $bill;
							?>
							<td align="right"><?php echo number_format($amount,2)?></td>
							<td align="right"></td>
							<td align="right"><?php echo number_format($totalBaki,2)?></td>
							
							<?php 
									
							} if (strpos($info, 'payment') !== false) { 
									$payment = $amount;
									if($totalBaki == 0 && abs($totalBF) != 0)
										$totalBaki =  $totalBF;
									
									$totalBaki = $totalBaki - $payment;
							?>
							<td align="right"></td>
							<td align="right"><?php echo number_format($amount,2)?></td>
							<td align="right"><?php echo number_format($totalBaki,2)?></td>
							
						</tr>
						<?php
						if($totalBF != 0)
							$totalBF =0; 
						}
							}
						?>
						<tr>
							<td align="right" colspan = "4"><b>TOTAL (RM)</b></td>
							<td align="right"><b><?php echo number_format($totalUnpaid,2) ?></b></td>
							<td align="right"><b><?php echo number_format($totalPaid,2) ?></b></td>
							<td align="right"><b><?php echo number_format($totalBaki,2)?></b></td>
							
						</tr>
					</table>
				</div>
			</div>
			<div class="card-header py-3">
				<h5 class="m-0 font-weight-bold text-primary">WATER BILL INFO</h5>
			</div>
			<div class="card-body">
				<div class="table-responsive">
					<table class="table table-bordered table-hover table-sm" id="dataTable" width="100%" cellspacing="0" style="font-size: 12px">
						<tr align="center">
							<td rowspan="2"><b>#</b></td>
							<td rowspan="2"><b>DATE</b></td>
							<td rowspan="2"><b>INVOICE / RECEIPT NO</b></td>
							<td rowspan="2"><b>DESCRIPTION</b></td>
							<td><b> (Db)</b></td>
							<td><b>(Cr)</b></td>
							<td><b>BALANCE</b></td>
							
						</tr>
						<tr align="center">
							<td><b>(RM)</b></td>
							<td><b>(RM)</b></td>
							<td><b>(RM)</b></td>
							
						</tr>
						<?php 
						$no=1;
						$showBFDate = "";
						if($firstYearDate != ""){
							$showBFDate = date("d/m/Y",strtotime($firstYearDate));
						}
						?>
						<tr>
							<td align="center"><?php echo $no?></td>
							<td align="center"><?php echo $showBFDate;?></td>
							<td>&nbsp;</td>
							<td>B/F</td>
							<td>&nbsp;</td>
							<td>&nbsp;</td>
							<td align="right"><?php echo number_format($totalBF,2);?></td>
							
						</tr>
						<?php 
						//$no=1;
						$totalPaid = 0;
						$totalUnpaid = 0;
						$totalPaidList = "";
						$totalUnpaidList = "";
						$totalBaki = 0;
						$bill = 0;
						$payment = 0;
						$kiraan30Percent = 0;
						$total_kiraan30Percent = 0;
						$month = "";
						$year = "";
						$slashPosition = 0;
						$RP_rentalMonth = "";
						
						$layak = "";
						$bill_id_smartsewa = "";
						while($resultStatement = mysqli_fetch_array($runSqlStatementWater)){
							$no++;
							$id = $resultStatement["payment_id"];
							$amount = $resultStatement["amount"];
							$date_created = $resultStatement["date_created"];
							$pay_info = $resultStatement["pay_info"];
							$issue_date = $resultStatement["issue_date"];
							$status_payment = $resultStatement["status_payment"];
							$info = $resultStatement["info"];
							$pay_type = $resultStatement["pay_type"];
							
							
							$date_created = date("d/m/Y", strtotime($date_created));
							
							if (strpos($issue_date, '/') !== false) {
								$issue_date = $resultStatement["issue_date"];
								$slashPosition = strpos($issue_date,"/");
								$month = substr($issue_date, 0, $slashPosition);
								$year = substr($issue_date, $slashPosition + 1 , 6);
								$issue_date = $year . "-" . $month ."-01";
								$RP_rentalMonth = $year . "-" . $month ."-07";
								$cutOff_rentalMonth = $year . "-" . $month ."-08";
								$issue_date = date("M Y", strtotime($issue_date));
								$RP_rentalMonth = date("Y-m-d", strtotime($RP_rentalMonth));
								$cutOff_rentalMonth = date("Y-m-d", strtotime($cutOff_rentalMonth));
							}
							else{
								$issue_date = date("M Y", strtotime($issue_date));
								$RP_rentalMonth = date("Y-m-07", strtotime($issue_date));
								$cutOff_rentalMonth = date("Y-m-08", strtotime($issue_date));
							}
							if (strpos($pay_info, '00:00:00') !== false) {
								$pay_info = $issue_date;
							}
							else{
								$pay_info = $pay_info . " " . $issue_date;
							}
							
							
							//if(strtolower($status_payment) == 'paid' || strtolower($status_payment) == 'partial'){
							if (strpos($info, 'payment') !== false) {
								$id = strstr($info, ' payment ', true);
								
								$bill_id_smartsewa = $bill_id;
								$totalPaidList .= $amount;
								$totalPaid += $amount;
							}
							if (strpos($info, 'bill') !== false) {
								$totalUnpaid += $amount;
								$totalUnpaidList .= $amount;
								
								$id = strstr($info, ' bill ', true);
								
								
								if(strtolower($status_payment) == 'paid' || strtolower($status_payment) == 'partial'){
									
								}
								
							}
							
							?>
						<tr>
							<td align="center"><?php echo $no?></td>
							<td align="center"><?php echo $date_created?></td>
							<td><?php echo $id?></td>
							<td><?php echo $pay_info?></td>
							<?php if (strpos($info, 'bill') !== false) { 
									$bill = $amount;
									if($totalBaki == 0 && abs($totalBF) != 0)
										$totalBaki =  $totalBF;
									
									$totalBaki = $totalBaki + $bill;
							?>
							<td align="right"><?php echo number_format($amount,2)?></td>
							<td align="right"></td>
							<td align="right"><?php echo number_format($totalBaki,2)?></td>
							
							<?php 
									
							} if (strpos($info, 'payment') !== false) { 
									$payment = $amount;
									if($totalBaki == 0 && abs($totalBF) != 0)
										$totalBaki =  $totalBF;
									
									$totalBaki = $totalBaki - $payment;
							?>
							<td align="right"></td>
							<td align="right"><?php echo number_format($amount,2)?></td>
							<td align="right"><?php echo number_format($totalBaki,2)?></td>
							
						</tr>
						<?php
							if($totalBF != 0)
								$totalBF =0; 
							}
						
					}
						?>
						<tr>
							<td align="right" colspan = "4"><b>TOTAL (RM)</b></td>
							<td align="right"><b><?php echo number_format($totalUnpaid,2) ?></b></td>
							<td align="right"><b><?php echo number_format($totalPaid,2) ?></b></td>
							<td align="right"><b><?php echo number_format($totalBaki,2)?></b></td>
							
						</tr>
					</table>
				</div>
			</div>
			<div class="card-header py-3">
				<h5 class="m-0 font-weight-bold text-primary">OUTSTANDING INFO <?PHP echo " UNTIL " . date("d/m/Y",strtotime($cutOffDate))?></h5>
			</div>
			<div class="card-body">
				<div class="table-responsive">
					<table class="table table-bordered table-hover table-sm" id="dataTable" width="100%" cellspacing="0" style="font-size: 12px">
						<tr align="center">
							<td ><b>#</b></td>
							<td><b>INVOICE</b></td>
							<td><b>DATE</b></td>
							<td><b>TOTAL</b></td>
							<td><b>0-30 DAYS</b></td>
							<td><b>31-60 DAYS</b></td>
							<td><b>61-90 DAYS</b></td>
							<td><b>90+ DAYS</b></td>
						</tr>
						<?php 
						
								$runSqlAgeing = mysqli_query($link, $sqlAgeing);
								if($runSqlAgeing === FALSE){
									die(mysqli_error($link));
								}
								
								$bil = 1;
								$bill_ball30 = 0;
								$bill_ball3160 = 0;
								$bill_ball6190 = 0;
								$bill_ball91 = 0;
								$total_billBal = 0;
								$resultSqlAgeing = array();
								while($resultSqlAgeing = mysqli_fetch_array($runSqlAgeing)){
									if($resultSqlAgeing['bill_id'] != null || $resultSqlAgeing['bill_id'] != ""){
										$bill_bal = $resultSqlAgeing['total_outstanding'];
										$invDate = $resultSqlAgeing['issue_date'];
										$invDate = date("Y-m-d",strtotime($resultSqlAgeing['issue_date']));
										$aged_date = date("Y-m-d", strtotime($ageingDate));
										$start_date = $invDate;
										$end_date = $cutOffDate;
										// call dateDifference() function to find the number of days between two dates
										$diff = dateDifference($start_date, $end_date);
										$invDate = date("d/m/Y",strtotime($resultSqlAgeing['issue_date']));
									
								?>
						<tr>
							<td align="center"><b><?php echo $bil; ?></b></td>
							<td align="center"><b><?php echo $resultSqlAgeing['bill_no']; ?></b></td>
							<td align="center"><b><?php echo $invDate; ?></b></td>
							<td align="center"><b><?php echo number_format($bill_bal,2); ?></b></td>
							<td align="center">
								<b><?php 
										if($diff <= 30){
											echo number_format($bill_bal,2);
											$bill_ball30 += $bill_bal;
										}
									?>
								</b>
							</td>
							<td align="center">
								<b><?php 
										if($diff >= 31 && $diff <= 60){
											echo number_format($bill_bal,2);
											$bill_ball3160 += $bill_bal;
										}
									?>
								</b>
							</td>
							<td align="center">
								<b><?php 
										if($diff >= 61 && $diff <= 90){
											echo number_format($bill_bal,2);
											$bill_ball6190 += $bill_bal;
										}
									?>
								</b>
							</td>
							<td align="center">
								<b><?php 
										if($diff > 90){
											echo number_format($bill_bal,2);
											$bill_ball91 += $bill_bal;
										}
									?>
								</b>
							</td>
						</tr>
							<?php
										$bil++;
										$total_billBal += $bill_bal;
									}
								}
							?>
						<tr>
							<td align="center" colspan = "3"><b>TOTAL (RM): </b></td>
							<td align="center"><b><?php echo number_format($total_billBal,2);?></b></td>
							<td align="center"><b><?php echo number_format($bill_ball30,2);?></b></td>
							<td align="center"><b><?php echo number_format($bill_ball3160,2);?></b></td>
							<td align="center"><b><?php echo number_format($bill_ball6190,2);?></b></td>
							<td align="center"><b><?php echo number_format($bill_ball91,2);?></b></td>
						</tr>
					</table>
				</div>
			</div>
		</div>
		<div class="card-footer">
			<button type="button" class="btn btn-outline-primary btn-sm" onclick="printStatement('<?php echo $tenant_id;?>')" class="btn btn-outline-warning btn-sm">
				<i class="fa fa-print"></i>&nbsp; PRINT STATEMENT
			</button>
			<button type="button" class="btn btn-outline-primary btn-sm" onclick="exportToCSV('<?php echo $tenant_id;?>')" class="btn btn-outline-warning btn-sm">
				<i class="fa fa-table"></i>&nbsp; EXPORT TO CSV
			</button>
		</div>
	</div>
</div>