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



$tenant_id = $_GET["tenant_id"];
$bills_water_id = $_GET["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"];
$address_line1 = $resultTenant["line1"];
$address_line2 = $resultTenant["line2"];
$address_city = $resultTenant["city"];
$address_state = $resultTenant["state"];
$address_country = $resultTenant["country"];
$address_postcode = $resultTenant["postcode"];

//GET RENTAL RATE FROM TENANT ID
$sqlTenantRentalRate = "select * from tenant_rental_rate where tenant_id = $tenant_id";
$runTenantRentalRate = mysqli_query($link, $sqlTenantRentalRate);
if($runTenantRentalRate === FALSE){
	die(mysqli_error($link));
}

//check water bill
$sqlWaterBills = "select bills_water.*,bill_water_rate.rate_amount as waterbill_rateamount,bill_water_rebet.usage_m3 as rebet_usage_m3,bill_water_rebet.amount as rebet_amount from bills_water 
					inner join bill_water_rate on bills_water.bill_water_rate_id = bill_water_rate.id
					inner join bill_water_rebet on bills_water.rebet_id = bill_water_rebet.id
					where bills_water.id = $bills_water_id and bill_water_rate.status ='enable' and bill_water_rebet.status='enable' ";
$runWaterBills = mysqli_query($link, $sqlWaterBills);
if($runWaterBills === FALSE){
	die(mysqli_error($link));
}
$resultWaterBills = mysqli_fetch_assoc($runWaterBills);
$bakiBelumDijelaskan = 0.00;
//$tenant_id = $resultWaterBills["tenant_id"];
//$tenant_name = $resultWaterBills["tenant_name"];
//$unit_no = $resultWaterBills["unit_no"];
//$tenant_status = $resultWaterBills["status"];
$bill_account = $resultWaterBills["bill_account"];
$bill_invoice = $resultWaterBills["bill_invoice"];
$date_reading_current = $resultWaterBills["date_reading_current"];
$reading_current = $resultWaterBills["reading_current"];
$date_reading_before = $resultWaterBills["date_reading_before"];
$reading_before = $resultWaterBills["reading_before"];
$total_usage_m3 = $resultWaterBills["total_usage_m3"];
$amount_due = $resultWaterBills["amount_due"];
$bill_amount = $resultWaterBills["bill_amount"];
$issue_date = $resultWaterBills["issue_date"];

$waterrate_id = $resultWaterBills["waterrate_id"];
$rate_amount = $resultWaterBills["rate_amount"];
$waterrebet_id = $resultWaterBills["waterrebet_id"];
$rebetAmount = $resultWaterBills["rebetamount"];

$payment_status = $resultWaterBills["payment_status"];
$water_bill_id = $resultWaterBills["id"];

//set payment type = Outstanding / Normal / Advance
$payment_type = "";
$issue_date = date("m/Y", strtotime($resultWaterBills["issue_date"]));
$created_at = date("d/m/Y", strtotime($resultWaterBills["created_at"]));


//if bill_type_id not in (1,2), payment type = Outstanding (what ever reason), 1 = rental, 2 = water


//tenant_status start

if($tenant_status == "enable"){
	$tenant_status = "active";
}
else if($tenant_status == "disable"){
	$tenant_status = "deactive";
}
//tenant_status end

$string_date = date("d/m/Y", strtotime($resultWaterBills["date_reading_before"])) . " - " . date("d/m/Y", strtotime($resultWaterBills["date_reading_current"]));

$date_reading_current_diff = strtotime($date_reading_current);
$date_reading_before_diff = strtotime($date_reading_before);

$timeDiff = abs($date_reading_current_diff - $date_reading_before_diff);

$numberDays = $timeDiff/86400;  // 86400 seconds in one day

// and you might want to convert to integer
$numberDays = intval($numberDays);

//cari total invoice lama bagi tenants
$sqlTotalWaterInv_old = "select sum(bill_amount) as total_inv_old from bills where bill_type_id = 2 and tenant_id = $tenant_id";
$runTotalWaterInv_old= mysqli_query($link, $sqlTotalWaterInv_old);
if($runTotalWaterInv_old=== FALSE){
	die(mysqli_error($link));
}
//cari total payment lama bagi tenants
$sqlTotalWaterPayment_old = "select sum(amount_paid) as total_payment_old, payment_status from payments inner join bills on payments.bill_id = bills.id where bill_type_id = 2 and tenant_id = $tenant_id";
$runTotalWaterPayment_old= mysqli_query($link, $sqlTotalWaterPayment_old);
if($runTotalWaterPayment_old=== FALSE){
	die(mysqli_error($link));
}
$resultTotalWaterInv_old = mysqli_fetch_assoc($runTotalWaterInv_old);
$total_inv_old = $resultTotalWaterInv_old["total_inv_old"];
$resultTotalWaterPayment_old = mysqli_fetch_assoc($runTotalWaterPayment_old);
$total_payment_old = $resultTotalWaterPayment_old["total_payment_old"];
$payment_status_old = $resultTotalWaterPayment_old["payment_status"];
$outstanding = $total_inv_old - $total_payment_old; //baki terdahulu


//baki terdahulu = Brought forward (b/f) - start
$bakiTerdahulu = 0.00;
$sqlBakiTerdahulu = "select * from bill_water_bf_end2024 where tenant_id = $tenant_id";
$runBakiTerdahulu= mysqli_query($link, $sqlBakiTerdahulu);
if($runBakiTerdahulu=== FALSE){
	die(mysqli_error($link));
}
$resultBakiTerdahulu = mysqli_fetch_assoc($runBakiTerdahulu);
$bakiTerdahulu_bfEnd2024 = $resultBakiTerdahulu["bf_end2024"];
if($bakiTerdahulu_bfEnd2024 > 0 ){
	$bakiBelumDijelaskan = $bakiTerdahulu_bfEnd2024 - $totalPayment_terkini ;
}
else if($bakiTerdahulu_bfEnd2024 < 0){
	$bakiBelumDijelaskan = $bakiTerdahulu_bfEnd2024 + $totalPayment_terkini ;
}

//baki terdahulu = Brought forward (b/f) - end


//contoh 30 - 30 = 0
//contoh 30 - 20 = 10 (tunggakan)
//contoh 30 - 40 = -10 (lebihan bayaran)		
//bayaran sehingga kini - lebihan bayaran (kalau ada) dan jumlah bayaran dari tarikh bacaan lepas hingga bacaan terkini
//table payments
$payment_old = 0.00;
$payment_new = 0.00;
$totalPayment_terkini = 0.00;
$jumlahPerluDijelaskan = 0.00;
 "date_reading_before: " . $date_reading_before = date("Y-m-d",strtotime($date_reading_before));
 "date_reading_current: " .  $date_reading_current = date("Y-m-d",strtotime($date_reading_current));
//if($date_reading_before > '2024-12-31' || $date_reading_current > '2024-12-31'){
if($date_reading_current > '2024-12-31'){
	$date_start = "2025-01-01";
	//ECHO "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_reading_before' and '$date_reading_current'";
	 "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_reading_current'";
	$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 = $resultWaterPayment_old["payment_old"];
	}

	//table payments_water
	 "sqlWaterPayment_new: ".  $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 between '$date_start' and '$date_reading_current'";
	$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 = $resultWaterPayment_new["payment_new"];
	}
	$totalPayment_terkini = $payment_old + $payment_new;
	
	//baki TERDAHULU start
	$bakiTerdahulu_new = 0.00;
	 "sqlBakiTerdahulu_new $tenant_id: " . $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_reading_current' ";
	$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 = $resultBakiTerdahulu_new["total_bill_amount"];
	}
	
	"bakiTerdahulu $tenant_id : " . $bakiTerdahulu = $bakiTerdahulu_bfEnd2024 + $bakiTerdahulu_new;
	//baki TERDAHULU end
	
	
}
else{
	$bakiTerdahulu = $bakiTerdahulu_bfEnd2024;
}

//$bakiBelumDijelaskan = $bill_amount + $bakiTerdahulu - $totalPayment_terkini ;
$bakiBelumDijelaskan =  $bakiTerdahulu - $totalPayment_terkini ;

$jumlahPerluDijelaskan = $bill_amount + $bakiBelumDijelaskan;

$fullAddress = $address_line1 . ", ". $address_line2 ." <br/> " . $address_postcode . " " . $address_city . " <br/> " .$address_state;

?>

<body  height="100%" style="font-family:verdana;font-size:12px" onLoad="window.print();" >
<div style="vertical-align: top;">	
	<div class="page-header" style="text-align: center">
		<table>
			
			<tr>
				<table width="100%" height="100%" >
					<thead>
						<tr >
							<td>
							<!--place holder for the fixed-position header-->
							<div class="page-header-space"></div>
							</td>
						</tr>
					</thead>
					<tbody>
						<tr style="border: 1px solid black;">
							<td style="font-size: 100%;" bgcolor="yellow" colspan="3"><b>PERUMAHAN DAN HARTANAH SELANGOR SDN BHD</b></td>
							<td style="font-size: 20px; " align="right" width="40%" >
								<i>INVOIS</i>
							</td>
						</tr>
						<tr style="border: 1px solid black;">
							<td colspan="3"><img src="images/BARCODE DESIGN.png"  height="25px" ></td>
							<td style="font-size: 30px; " align="right" width="40%" >
							BIL AIR
							</td>
						</tr>
						<tr>
							<td colspan="4">
								<table valign="top" width="100%" >
									<tr>
										<td colspan="3"  rowspan="4" width="50%">
											<div style="font-size: 14px;" >
												<b><?php  echo strtoupper($tenant_name)?></b><br/>
												<?php echo $unit_no . ", " .strtoupper($location) . ", "?><br/>
												<?php echo $fullAddress ?><br/>
											</div>
										</td>
										<td  valign="top" width="50%" STYLE="float:right;background-color:#cccccc">
											<table style="font-size: 12px;" >
												<tr>
													<td  valign="top">No. Akaun</td>
													<td>:</td>
													<td valign="top"><?php echo $bill_account;?></td>
												</tr>
												<tr>
													<td  valign="top">No. Invois</td>
													<td>:</td>
													<td valign="top"><?php echo $bill_invoice;?></td>
												</tr>
												<tr>
													<td  valign="top">Date </td>
													<td>:</td>
													<td valign="top"><?php echo date("d/m/Y");?></td>
												</tr>
											</table>
										</td>
									</tr>
								</table>
								<br/>
								<div style="border-collapse: collapse;border-bottom: 1px solid black;"  style="line-height: 3;">
									<table width="100%" style="font-size: 12px;" border='0' >
										<tr>
											<td bgcolor="#000000" colspan = "2" width="45%"><font color="white" style="font-size: 12px;"><b>RINGKASAN BIL</b></font> </td>
										</tr>
										<tr>
											<td colspan = "2" >
												<table style="font-size: 14px;" border='0'  width="100%">
													<tr>
														<td width='30%'><strong>BAKI TERDAHULU</strong></td>
														
														<td width='50%' align="right"><b>RM <?php echo number_format($bakiTerdahulu,2,".",",");?></b></td>
													</tr>
													<tr>
														<td width='30%'><strong>BAYARAN SEHINGGA KINI</strong></td>
														
														<td width='50%' align="right"><b>RM <?php echo number_format($totalPayment_terkini,2,".",","); ?></b></td>
													</tr>
													<tr>
														<td width='30%'><strong>BAKI BELUM DIJELASKAN</strong></td>
														
														<td width='50%' align="right"><b>RM <?php echo number_format($bakiBelumDijelaskan,2,".",",");?></b></td>
													</tr>
													
													<tr>
														<td width='30%'><strong>BIL SEMASA (BAYAR SEBELUM <?PHP ECHO date("d/m/Y")?>)</strong></td>
														
														<td width='50%' align="right"><b>RM <?php echo number_format($bill_amount,2,".",",");?></b></td>
													</tr>
													<tr style="background-color:yellow">
														<td width='30%'><strong>JUMLAH PERLU DIBAYAR</strong></td>
														
														<td width='50%' align="right" ><b>RM <?php echo number_format($jumlahPerluDijelaskan,2,".",",");?></b></td>
													</tr>
												</table>
											</td>
										</tr>
										<tr>
											<td colspan = "2" width="45%">&nbsp;</td>
										</tr>
										<tr>
											<td bgcolor="#000000" colspan = "2" width="45%"><font color="white" style="font-size: 12px;"><b>BUTIRAN BIL</b></font> </td>
										</tr>
										<tr>
											<td>
												<table border='0' style="font-size: 12px;" border='0'  width="100%">
													<tr>
														<th align="middle"><b>Tarikh</b></th>
														<th align="middle"><b>Tempoh Bacaan (Hari)</b></th>
														<th align="middle"><b>Bacaan</b></th>
														<th align="middle"><b>Penggunaan (m3)</b></th>
													</tr>
													<?php $penggunaan = $reading_current - $reading_before; ?>
													<tr>	
														<td align="middle"><?php echo $date_reading_current?></td>
														<td align="middle"><?php echo $numberDays ?></td>
														<td align="middle"><?php echo $reading_current?></td>
														<td align="middle"><?php echo $penggunaan?></td>
													</tr>
													<tr>	
														<td align="middle"><?php echo $date_reading_before?></td>
														<td></td>
														<td align="middle"><?php echo $reading_before?></td>
														<td></td>
													</tr>
													<tr style="background-color:yellow">
														<td colspan="3" align="middle" ><b>Jumlah Penggunaan yang dibilkan</b></td>
														<td align="middle"><b><?php echo $penggunaan?></b></td>
													</tr>
												</table>
											</td>
										</tr>
										
									</table>
								</div>
							
							</td>
						</tr>
					</tbody>
					<tfoot>
						<tr>
							<td>
							  <!--place holder for the fixed-position footer-->
							  <div class="page-footer-space"></div>
							</td>
						</tr>
					</tfoot>
				</table>
			</tr>
		</table>
	</div>
	<div class="page-footer" align="center">
		www.phssb.com
	</div>
	<div class="watermark" align="center">
	</div>
		

	
</div>
</body>