<script type="text/javascript">

$(document).ready( function () {
    $('#dataTable').DataTable();
} );

$(document).ready(function () {
    $('#example').DataTable();
});
</script>


<div class="card shadow mb-4">
	<div class="card-header py-3">
		<h6 class="m-0 font-weight-bold text-primary">Rental</h6>
	</div>
	<div class="card-body">
		<div class="table-responsive">
			<!--<table class="table table-bordered" id="dataTable" widtd="70%" cellspacing="0" style="font-size: 12px">-->
			<table class="table table-bordered table-hover table-sm" id="dataTable" width="50%" cellspacing="0" style="font-size: 12px">
					<tr>
						<td  align="center" ><b>BIL</b></td>
						<td  align="center" ><b>NAMA</b></td>
						<td  align="center" ><b>NO UNIT</b></td>
						<td  align="center" ><b>STATUS PENYEWAAN</b></td>
						<td align = "center" ><b>ACC NO</b></td>
						<td align = "center" ><b>INVOICE NO</b></td>
						<td align = "center" ><b>MONTH/YEAR</b></td>
						<td align = "center" ><b>TARIKH</b></td>
						<td align = "center" style="width:5%;word-wrap: break-word;"><b>TEMPOH BACAAN</b></td>
						<td align = "center" style="width:5%;word-wrap: break-word;"><b>PENGGUNAAN (m3)</b></td>
						<td align = "center" style="width:5%;word-wrap: break-word;"><b>KADAR (RM/m3)</b></td>
						<td align = "center" style="width:5%;word-wrap: break-word;"><b>POTONGAN REBET (RM)</b></td>
						<td align = "center" style="width:5%;word-wrap: break-word;"><b>CAJ PENGGUNAAN (BIL SEMASA)(RM)</b></td>
						<td align = "center" style="width:5%;word-wrap: break-word;"><b>BAKI TERDAHULU (RM)</b></td>
						<td align = "center" style="width:5%;word-wrap: break-word;"><b>BAYARAN SEHINGGA KINI (RM)</b></td>
						<td align = "center" style="width:5%;word-wrap: break-word;" ><b>BAKI BELUM DIJELASKAN (RM)</b></td>
						<td align = "center" style="width:5%;word-wrap: break-word;"><b>PAYMENT STATUS</b></td>
					</tr>
					
				<?PHP 
				$no = 1;
				$outstanding = 0.00;
				$sumBillAmount = 0.00;
				$sumAmountPaid = 0.00;
				$sumOutstanding = 0.00;
				$outstandingBill = 0.00;
				$sumAdvance = 0.00;
				$sumALL = 0.00;
				while($resultWaterBills = mysqli_fetch_array($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"];
					$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
					/* if($bill_type_id != 1 && $bill_type_id != 2)
						$payment_type = "Outstanding";
					else{
						//1. Outstanding
						if($created_at > $issue_date){
							 $payment_type = "Outstanding";
						}
						//2. Normal
						else if($created_at == $issue_date){
							 $payment_type = "Normal";
						}
						//3. Advance
						else if($created_at < $issue_date){
							 $payment_type = "Advance";
						}
					}
					$outstanding = $bill_amount - $amount_paid; */
					
					//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;
					 "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 ;
					
					
					
				?>
				 <tbody>
				<tr>
					<td><?php echo $no; ?></td>
					<td><a href="viewTenantDashboard.php?tenant_id=<?php echo $tenant_id?>"><?php echo strtoupper($tenant_name); ?></a></td>
					<td><?php echo $unit_no; ?></td>
					<td><?php echo ucfirst($tenant_status); ?></td>
					<td><?php echo $bill_account; ?></td>
					<td><a href="waterbill_invoice.php?tenant_id=<?php echo $tenant_id?>&id=<?php echo $water_bill_id?>" target="_blank"><?php echo $bill_invoice; ?></a></td>
					<td><?php echo date("m/Y",strtotime($resultWaterBills["issue_date"])); ?></td>
					<td><?php echo $string_date; ?></td>
					<td><?php echo $numberDays; ?></td>
					<td><?php echo $total_usage_m3; ?></td>
					<td><?php echo $rate_amount; ?></td>
					<td><?php echo $rebetAmount; ?></td>
					<td><?php echo number_format($bill_amount,2,".",","); ?></td>
					<td><?php echo number_format($bakiTerdahulu,2,".",","); ?></td>
					<td><?php echo number_format($totalPayment_terkini,2,".",","); ?></td>
					<td><?php echo number_format($bakiBelumDijelaskan,2,".",","); ?></td>
					<?php if($payment_status == "paid" || $payment_status == "partial"){?>
					<td><a href="printPaymenFromBillsWater.php?water_bill_id=<?php echo $water_bill_id?>" target="_blank" ><?php echo strtoupper($payment_status); ?></a></td>
					<?php } else{?>
					<td><?php echo strtoupper($payment_status); ?></td>
					<?php }
						$sumBillAmount += $bill_amount;
						$sumOutstanding += $outstanding;
						$sumtotalPayment_terkini += $totalPayment_terkini;
						$sumbakiBelumDijelaskan += $bakiBelumDijelaskan;
						$sumTotalUsagem3 += $total_usage_m3;
					?>
					
				</tr>
				
				<?php 
				
				$no++;
				} 
				?>
				<tfoot>
				<tr>
					<td colspan="9"><b>TOTAL (RM)</b></td>
					<td><b><?php echo number_format($sumTotalUsagem3,2,".",",");?></b></td>
					<td></td>
					<td></td>
					<td><b><?php echo number_format($sumBillAmount,2,".",",");?></b></td>
					<td><b><?php echo number_format($sumOutstanding,2,".",",");?></b></td>
					<td><b><?php echo number_format($sumtotalPayment_terkini,2,".",",");?></b></td>
					<td><b><?php echo number_format($bakiBelumDijelaskan,2,".",",");?></b></td>
					<td></td>
				</tr>
				<?PHP 
				$outstandingBill  = $sumBillAmount - $sumAmountPaid;
				?>
				<!--<tr>
					<td colspan="5"><b>TOTAL OUTSTANDING(RM)</b></td>
					<td colspan="5" align="center"><b><?php echo number_format($outstandingBill,2,".",",");?></b></td>
				</tr>-->
				</tfoot>
				 </tbody>
			</table>
		</div>
	</div>
</div>