<STYLE type="text/css">

/* Styles go here */

.page-header, .page-header-space {
  height: 50px;
}

.page-footer, .page-footer-space {
  height: 20px;

}

.page-footer {
  position: fixed;
  bottom: 0;
  width: 100%;
  border-top: 1px solid black; /* for demo */
  background: yellow; /* for demo */
}

.page-header {
  position: fixed;
  top: 0mm;
  width: 100%;
  /*border-bottom: 1px solid black;*/ /* for demo */
 /*background: yellow;*/ /* for demo */
}

.page {
  page-break-before: always;
}

.watermark{
	background-repeat: repeat;
	background-image: url("img/phssbwatermark.png");
}

@page {
  margin: 10mm
  
}

@media print {
   thead {display: table-header-group;} 
   tfoot {display: table-footer-group;}
   
   button {display: none;}
   
   body {
	margin: 0;
	/*background-repeat: repeat;
	background-image: url("img/phssbwatermark.png");*/
   }
   
}

hr.line {
  border-top: 1px dashed red;
}
}
table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
</STYLE>


<?php 

// Initialize the session
session_start();
error_reporting(0);
// Initialize the session
//session_start();
//include('function_bgcolorSS_byrLepas7hb.php');
require_once "config.php";
function dateDifference($start_date, $end_date)
{
    // calulating the difference in timestamps 
    $diff = strtotime($start_date) - strtotime($end_date);
     
    // 1 day = 24 hours 
    // 24 * 60 * 60 = 86400 seconds
    return ceil(abs($diff / 86400));
}

$report_type = $_GET["report_type"];
$statementby = $_GET["statementby"];

$tenant_status = $_GET["tenant_status"];

include("bill_types.php");
if($statementby == "statement_year" || $statementby == "statement_monthyear" ||  $statementby == "statement_ChooseDate"){
	$date_statementOptionView = $_GET["date_statementOptionView"];
}
else if($statementby == "statement_date"){
	$date_statementOptionView_from = $_GET["date_statementOptionView_from"];
	$date_statementOptionView_to = $_GET["date_statementOptionView_to"];
}
$condRentalWater_statementby = "";
$condDeposit_statementby = "";
$condDepositOther_statementby = "";

$sqlTenantStatus = "";
if($tenant_status != ""){
	$sqlTenantStatus = " and tenants.status = '" .$tenant_status."' ";
}


if($statementby == 'statement_year'){
	$sqlIssueDate = "";
	$statement_year = $date_statementOptionView;
	$_SESSION['statement_year']=$statement_year;
	
	
	$dateEnd = date("Y-m-d",strtotime($statement_year . '-12-31')); //tarikh hujung tahun
	$dateStart =  date("Y-m-d",strtotime($statement_year . '-01-01')); //tarikh awal tahun
	
	//currentdate
	$currentdate = date("Y-m-d");
	$current_year = date("Y",strtotime($currentdate));
	 "dateEnd_year:" . $dateEnd_year = date("Y",strtotime($dateEnd));
	if($current_year == $dateEnd_year){
		 "DATE END:::: " .$dateEnd = date("Y-m-d");
	}
	$condDeposit_statementby = "  deposit_bills.created_at >= '$dateStart' and   deposit_bills.created_at <= '$dateEnd' ";
	$condDepositOther_statementby = "  deposit_bill_other_receipts.created_at  >= '$dateStart' and   deposit_bill_other_receipts.created_at <= '$dateEnd'";
	//$condRentalWater_statementby = "  year(bills.issue_date) < '$date_statementOptionView' ";
	$sqlIssueDate = " AND  bills.issue_date  >= '$dateStart' and  bills.issue_date <= '$dateEnd'  ";
}
else if($statementby == 'statement_monthyear'){
	
	$date_monthYear = date("Y-m-01",strtotime($date_statementOptionView));
	$lastdate = $date_monthYear . "-01";
	$lastdate = date("Y-m-t", strtotime($lastdate));			
	$cutOffDate = $lastdate;
	
	$month = date("m",strtotime($date_monthYear));
	$year = date("Y",strtotime($date_monthYear));
	
	$date_monthYearDeposit = date("M Y",strtotime($lastdate));	
	
	$sqlDate = "  bills.issue_date  = '$date_monthYear' ";
	$condDeposit_statementby = " MONTH(deposit_bills.created_at)  = '$month' and YEAR(deposit_bills.created_at)  = '$year'";
	$condDepositOther_statementby = " MONTH(deposit_bill_other_receipts.created_at)  = '$month' and YEAR(deposit_bill_other_receipts.created_at)  = '$year'";
	
}
else if($statementby == 'statement_ChooseDate'){
	if($date_statementOptionView != "" || $date_statementOptionView != null){
		$chooseDate = date("Y-m-d",strtotime($date_statementOptionView));
	}
	$sqlDate = " bills.issue_date like '%$chooseDate%' ";
	$condDeposit_statementby = " deposit_bills.created_at like '%$chooseDate%' ";
	$condDepositOther_statementby = " deposit_bill_other_receipts.created_at like '%$chooseDate%' ";
}
else if($statementby == 'statement_date' ){
	
	$dateFrom = date("Y-m-d",strtotime($_GET["date_statementOptionView_from"]));
	$dateTo =  date("Y-m-d",strtotime($_GET["date_statementOptionView_to"]));
	$date_cutOFF = date('Y-m-d', strtotime($dateTo. ' + 1 days'));
	
	$sqlDate = " (bills.issue_date  >= '$dateFrom'  and bills.issue_date < '$date_cutOFF') ";
	$condDeposit_statementby = " (deposit_bills.created_at  >= '$dateFrom'  and deposit_bills.created_at < '$date_cutOFF') ";
	$condDepositOther_statementby = " (deposit_bill_other_receipts.created_at  >= '$dateFrom'  and deposit_bill_other_receipts.created_at < '$date_cutOFF') ";
	
}

if($report_type == "Deposit"){
	//check table deposit_bills (penyewa baru)
	 $sqlStatementDeposit_newRecord ="SELECT tenants.id as tenant_id, tenants.tenant_name, properties.unit_no, deposit_bills.id as deposit_id, 
											deposit_bills.deposit_bill_no as invoice_no, 
											deposit_bills.two_month_rental_due as two_month_rental_due, 
											deposit_bills.two_month_rental_amount as two_month_rental_amount_bill, 
											deposit_bills.utility_due as utility_due, 
											deposit_bills.utility_amount as utility_amount_bill, 
											deposit_bills.created_at as created_at, 
											deposit_bills.id as deposit_bill_id, 
											deposit_bills.deposit_bill_no as deposit_bill_no, 
											deposit_bills.payment_status, 
											concat(deposit_bills.deposit_bill_no,' deposit_bill ',deposit_bills.two_month_rental_amount) as info_two_months_amount  ,
											concat(deposit_bills.deposit_bill_no,' deposit_bill ',deposit_bills.utility_amount) as info_utility_amount  
											FROM deposit_bills 
											inner JOIN tenants on deposit_bills.tenant_id =  tenants.id
											inner join properties on tenants.property_id = properties.id
											inner join locations on properties.location_id = locations.id
											inner join projects on locations.project_id = projects.id 
											where locations.id = " . $_SESSION["location_id"] ." and (deposit_bills.payment_status = 'unpaid' or deposit_bills.payment_status = 'partial') $condDeposit_statementby $sqlTenantStatus";
											
											
	
	
	$runStatementDeposit_newRecord = mysqli_query($link, $sqlStatementDeposit_newRecord);
	if($runStatementDeposit_newRecord === FALSE){
		die(mysqli_error($link));
	}
	$countStatementDeposit_newRecord = mysqli_num_rows($runStatementDeposit_newRecord);
	
	
	
	$colspanShow_header = 7;
	$colspanShow_bottom = 4;

	//deposit_payment_other_receipts table
	
	 $sqlDepositPaymentOthers ="SELECT tenants.id as tenant_id, tenants.tenant_name, properties.unit_no, deposit_bill_other_receipts.id as depositOthers_id, 
											deposit_bill_other_receipts.deposit_other_bill_no as invoice_no, 
											deposit_bill_other_receipts.yp_charges_due as yp_due, 
											deposit_bill_other_receipts.yp_charges_amount as yp_charges_amount, 
											deposit_bill_other_receipts.sst_charges_due as sst_due, 
											deposit_bill_other_receipts.sst_charges_amount as sst_charges_amount, 
											deposit_bill_other_receipts.stamping_charges_due as stamping_due, 
											deposit_bill_other_receipts.stamping_charges_amount as stamping_charges_amount, 
											deposit_bill_other_receipts.created_at as created_at, 
											deposit_bill_other_receipts.id as deposit_bill_other_id, 
											deposit_bill_other_receipts.deposit_other_bill_no as deposit_other_bill_no, 
											deposit_bill_other_receipts.payment_status, 
											concat(deposit_bill_other_receipts.deposit_other_bill_no,' deposit_other_bill ',deposit_bill_other_receipts.yp_charges_amount) as info_yp_paid  ,
											concat(deposit_bill_other_receipts.deposit_other_bill_no,' deposit_other_bill ',deposit_bill_other_receipts.sst_charges_amount) as info_sst_paid,  
											concat(deposit_bill_other_receipts.deposit_other_bill_no,' deposit_other_bill ',deposit_bill_other_receipts.stamping_charges_amount) as info_stamping_paid  
											FROM deposit_bill_other_receipts 
											inner JOIN tenants on deposit_bill_other_receipts.tenant_id =  tenants.id
											inner join properties on tenants.property_id = properties.id
											inner join locations on properties.location_id = locations.id
											inner join projects on locations.project_id = projects.id 
											where locations.id = " . $_SESSION["location_id"] ." and (deposit_bill_other_receipts.payment_status = 'unpaid' or deposit_bill_other_receipts.payment_status = 'partial')  $condDepositOther_statementby $sqlTenantStatus";
	
	
	
	$runDepositPaymentOthers = mysqli_query($link, $sqlDepositPaymentOthers);
	if($runDepositPaymentOthers === FALSE){
		die(mysqli_error($link));
	}
	$countRowPaymentOthers = mysqli_num_rows($runDepositPaymentOthers);
	
}
else if($report_type == "Rental" || $report_type == "Water" ){
	if($statementby == "statement_year"){
		  "statement year $report_type";
			 
						
		   $sqlNotPaidList = "select tenants.id as tenant_id, tenants.tenant_name, properties.unit_no, bills.id as bill_id,
											bills.amount_due, bills.bill_amount, bills.payment_status, bills.issue_date, bills.bill_no, bills.created_at from bills
											inner join tenants on bills.tenant_id = tenants.id
											inner join properties on tenants.property_id = properties.id
											inner join locations on properties.location_id = locations.id
											inner join projects on locations.project_id = projects.id
											where bills.bill_type_id in ($bill_types_id) and locations.id = ". $_SESSION['location_id'] ."   $sqlIssueDate $sqlTenantStatus
											and projects.id =  ". $_SESSION['project_id'] ."  and (bills.payment_status = 'unpaid' or bills.payment_status = 'partial')  order by properties.id ASC
											";
											
											
											
			
	}
	else if($statementby == "statement_ChooseDate"){
		
		 $sqlNotPaidList = " select tenants.id as tenant_id, tenants.tenant_name, properties.unit_no, bills.id as bill_id,bills.bill_no,
							bills.amount_due, bills.bill_amount, bills.payment_status, bills.issue_date, bills.created_at from bills
							inner join tenants on bills.tenant_id = tenants.id
							inner join properties on tenants.property_id = properties.id
							inner join locations on properties.location_id = locations.id
							inner join projects on locations.project_id = projects.id 
							where bills.bill_type_id in ($bill_types_id)  and locations.id = ". $_SESSION['location_id'] ." 
							and projects.id = ". $_SESSION['project_id'] ." and (bills.payment_status = 'unpaid' or bills.payment_status = 'partial') and ($sqlDate)  $sqlTenantStatus
					
					";
	}
	else if($statementby == "statement_monthyear"){
		 
		 $sqlNotPaidList = " select tenants.id as tenant_id, tenants.tenant_name, properties.unit_no, bills.id as bill_id,bills.bill_no,
							bills.amount_due, bills.bill_amount, bills.payment_status, bills.issue_date, bills.created_at from bills
							inner join tenants on bills.tenant_id = tenants.id
							inner join properties on tenants.property_id = properties.id
							inner join locations on properties.location_id = locations.id
							inner join projects on locations.project_id = projects.id 
							where bills.bill_type_id in ($bill_types_id)  and locations.id = ". $_SESSION['location_id'] ."  
							and projects.id =  ". $_SESSION['project_id'] ." and (bills.payment_status = 'unpaid' or bills.payment_status = 'partial') and ($sqlDate) $sqlTenantStatus order by properties.id ASC";
	}
	else if($statementby = "statement_date"){
		
			
		$sqlNotPaidList = "select tenants.id as tenant_id, tenants.tenant_name, properties.unit_no, bills.id as bill_id,bills.bill_no,
							bills.amount_due, bills.bill_amount, bills.payment_status, bills.issue_date, bills.created_at from bills
							inner join tenants on bills.tenant_id = tenants.id
							inner join properties on tenants.property_id = properties.id
							inner join locations on properties.location_id = locations.id
							inner join projects on locations.project_id = projects.id 
							where bills.bill_type_id in ($bill_types_id)  and locations.id = ". $_SESSION['location_id'] ."  
							and projects.id =  ". $_SESSION['project_id'] ." and (bills.payment_status = 'unpaid' or bills.payment_status = 'partial') and ($sqlDate)$sqlTenantStatus order by properties.id ASC";
	}
	else{
		 
		$sqlNotPaidList = " select tenants.id as tenant_id, tenants.tenant_name, properties.unit_no, bills.id as bill_id,bills.bill_no,
							bills.amount_due, bills.bill_amount, bills.payment_status, bills.issue_date, bills.created_at from bills
							inner join tenants on bills.tenant_id = tenants.id
							inner join properties on tenants.property_id = properties.id
							inner join locations on properties.location_id = locations.id
							inner join projects on locations.project_id = projects.id 
							where bills.bill_type_id in ($bill_types_id)  and locations.id = ". $_SESSION['location_id'] ." 
							and projects.id = ". $_SESSION['project_id'] ." and (bills.payment_status = 'unpaid' or bills.payment_status = 'partial') and ($sqlDate) $sqlTenantStatus order by properties.id ASC
							";
	}				
	$runNotPaidList= mysqli_query($link, $sqlNotPaidList);
	if($runNotPaidList=== FALSE){
		die(mysqli_error($link));
	}
}
?>

<style>
table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
</style>
<body  height="100%" style="font-family:verdana;font-size:12px" onLoad="window.print();" >
<table width="100%" cellspacing="0" style="font-size: 12px;">
				<tr>
					<td align="center" ><b>#</b></td>
					<td align="center" ><b>NAMA PENYEWA</b></td>
					<td align="center" ><b>UNIT</b></td>
					<td align="center" ><b>INVOICE NO</b></td>
					<td align="center" ><b>MONTH/YEAR</b></td>
					<td align="center" ><b>AMOUNT DUE (RM)</b></td>
					<td align="center" ><b>PAYMENT STATUS</b></td>
					<td align="center" ><b>DATE CREATED</b></td>
				</tr>
				<?php 
				$no=1;
				$totalAmountDue = 0.00;
				while($resultNotPaidList = mysqli_fetch_array($runNotPaidList)){
					$tenant_id = $resultNotPaidList["tenant_id"];
					$tenant_name = $resultNotPaidList["tenant_name"];
					$unit_no = $resultNotPaidList["unit_no"];
					$bill_no = $resultNotPaidList["bill_no"];
					$amount_due = $resultNotPaidList["amount_due"];
					$issue_date = $resultNotPaidList["issue_date"];
					$payment_status = $resultNotPaidList["payment_status"];
					$created_at = $resultNotPaidList["created_at"];
					$bill_id = $resultNotPaidList["bill_id"];
					
					$totalAmountDue += $amount_due;
					
				?>
				<tr>
					<td align="center"><?php echo $no; ?></td>
					<td align="left"><?php echo strtoupper($tenant_name); ?></td>
					<td align="center"><?php echo $unit_no; ?></td>
					<td align="center"><?php echo $bill_no; ?></td>
					<td align="center"><?php echo date("M Y",strtotime($issue_date)); ?></td>
					<td align="right"><?php echo number_format($amount_due,2,".",","); ?></td>
					<td align="center"><?php echo $payment_status; ?></td>
					<td align="center"><?php echo date("d/m/Y",strtotime($created_at)); ?></td>
				</tr>
				
				<?php
				$no++;
				}
				
				?>
				<tr>
					<td COLSPAN=5><B>TOTAL (RM)</B></td>
					<td><B><?php echo number_format($totalAmountDue,2,".",","); ?></B></td>
				</tr>
			</table>
</body>