<?php include "headerLink.php"?>
<!DOCTYPE html>
<html lang="en">


<body class="animsition">
    <div class="page-wrapper">
        <!-- HEADER MOBILE-->
        <?php include "menuSideBar_mobile.php" ;?>
        <!-- END HEADER MOBILE-->
		
        <!-- MENU SIDEBAR-->
        <?php include "menuSideBar.php" ;?>
        <!-- END MENU SIDEBAR-->

        <!-- PAGE CONTAINER-->
        <div class="page-container">
            <!-- HEADER DESKTOP-->
			<?php include "headerPage.php"?>
            <!-- END HEADER DESKTOP-->
			
			
			
			<!-- MAIN CONTENT-->
			<?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));
			}

			
			// Function to check if a year is a leap year
			function isLeapYear($year)
			{
			  // Leap year conditions
			  if (($year % 4 == 0 && $year % 100 != 0) || ($year % 400 == 0)) return true; // True, it's a leap year
			  else return false; // False, it's not a leap year
			  
			}

			// Driver program
			// Replace this value with the year you want to check
			//$year = 2024;

			
			
			
			
			
			$tenant_id = $_GET["tenant_id"];
			$dateStart = $_GET["dateStart"];
			$dateEnd = $_GET["dateEnd"];
			$location_id = $_SESSION["location_id"];
			
			$yearStart = date("Y",strtotime($dateStart));
			$yearEnd = date("Y",strtotime($dateEnd));
			
			include("queryTenant.php");
			
			$resultTenant = mysqli_fetch_assoc($runTenant);
			$tenant_name = $resultTenant["tenant_name"];
			$unit_no = $resultTenant["unit_no"];
			$location_name = $resultTenant["location_name"];
			
			echo $sqlAgeing_byTenant = "select sum(bills.amount_due) as total_outstanding, 
											bills.* , bills.created_at as payment_created_at, properties.unit_no, tenants.tenant_name as name , properties.id as property_id
											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 
											where bills.issue_date >= '$dateStart' and bills.issue_date <= '$dateEnd'
											and locations.id = $location_id and bills.amount_due > 0.00
											and bills.bill_type_id = 1 and tenants.id = $tenant_id and 
											(bills.payment_status = 'partial' or bills.payment_status = 'unpaid')
											group by bills.issue_date,tenants.id having total_outstanding > 0 ";

			$runSqlAgeing_byTenant = mysqli_query($link, $sqlAgeing_byTenant);
			if($runSqlAgeing_byTenant === FALSE){
				die(mysqli_error($link));
			}
			
			?>
			
            
            <div class="main-content"  >
                <div class="section__content section__content--p30" >
                    <div class="container-fluid" >
                        <!-- menu show on page after selected - start -->
                        <?php include "selectedMenuSubMenu.php"?>
						<!-- menu show on page after selected - end -->
						<div class="row" >
							
							<div class="col-lg-12">
									<div align="center">
										
									</div>
									<?php if($tenant_id != ""){ ?>
									<div class="card shadow mb-4">
										<div class="card-header py-3">
											<h3 class="m-0 font-weight-bold text-primary">MAKLUMAT TUNGGAKAN  </h3>
											<h4><?PHP echo strtoupper($tenant_name) . " : " . strtoupper($unit_no) . "," . strtoupper($location_name)?></h4>
											<h4><?PHP echo date("d/m/Y",strtotime($dateStart)) . " - " . date("d/m/Y",strtotime($dateEnd))?></h4>
										</div>
										<div class="card-body">
											<div class="table-responsive">
												<table class="table table-bordered" id="dataTable" width="100%" cellspacing="0" style="font-size: 12px">
													<thead>
													
														<tr >
															<td colspan="17">
																<div align="center">
																	<button  class="btn btn-primary btn-sm" onclick="print_viewOutStandingAgeingByTenant('<?php echo $tenant_id ?>','<?php echo $dateStart?>','<?php echo $dateEnd?>')" >PRINT</button>
																	<button  class="btn btn-primary btn-sm" onclick="export_viewOutStandingAgeingByTenant('<?php echo $tenant_id ?>','<?php echo $dateStart?>','<?php echo $dateEnd?>')" >EXPORT CSV</button>
																</div>
															</td>
														</tr>
														<?php 
														//check tahun lompat (leap year) - bilangan hari jadi 366 setahun
															$dateStart_year = date("Y",strtotime($dateStart));
															$dateEnd_year = date("Y",strtotime($dateEnd));
															$end_ageing = "361-365 Hari";
															$leapYear = "";
															
															// Call the function to check if the year is a leap year
															if (isLeapYear($dateStart_year)) {
																$leapYear = "yes";
																$end_ageing = "361-366 Hari";
															}
															if (isLeapYear($dateEnd_year)) {
																$leapYear = "yes";
																$end_ageing = "361-366 Hari";
															}
															
															
														?>
														<tr bgcolor="#ebeced">
															<td align="center"><b>Bil.</b></td>
															<td align="center"><b>Invois ID#</b></td>
															<td align="center"><b>Tarikh</b></td>
															<td align="center"><b>Jumlah (RM)</b></td>
															<td align="center"><b>0-30 Hari</b></td>
															<td align="center"><b>31-60 Hari</b></td>
															<td align="center"><b>61-90 Hari</b></td>
															<td align="center"><b>91-120 Hari</b></td>
															<td align="center"><b>121-150 Hari</b></td>
															<td align="center"><b>151-180 Hari</b></td>
															<td align="center"><b>181-210 Hari</b></td>
															<td align="center"><b>211-240 Hari</b></td>
															<td align="center"><b>241-270 Hari</b></td>
															<td align="center"><b>271-300 Hari</b></td>
															<td align="center"><b>301-330 Hari</b></td>
															<td align="center"><b>331-360 Hari</b></td>
															<td align="center"><b><?php echo $end_ageing?></b></td>
														</tr>
													</thead>
														<?php
															$tenantLocationGroup = "";
															$bil_location = 1;
															$grandTotalOutstanding_all = 0; 
															
															$all_bill_ball30 = 0;
															$all_bill_ball3160 = 0;
															$all_bill_ball6190 = 0;
															$all_bill_ball91120 = 0;
															$all_bill_ball121150 = 0;
															$all_bill_ball151180 = 0;
															$all_bill_ball181210 = 0;
															$all_bill_ball211240 = 0;
															$all_bill_ball241270 = 0;
															$all_bill_ball271300 = 0;
															$all_bill_ball301330 = 0;
															$all_bill_ball331360 = 0;
															$all_bill_ball36365 = 0;
														
															$bil_tenant = 1;
															
															$grandTotalOutstanding_location = 0; 
															$bil = 1;
															$tenant_totalOutstanding = 0;
															$bill_ball30 = 0;
															$bill_ball3160 = 0;
															$bill_ball6190 = 0;
															$bill_ball91120 = 0;
															$bill_ball121150 = 0;
															$bill_ball151180 = 0;
															$bill_ball181210 = 0;
															$bill_ball211240 = 0;
															$bill_ball241270 = 0;
															$bill_ball271300 = 0;
															$bill_ball301330 = 0;
															$bill_ball331360 = 0;
															$bill_ball361365 = 0;
															$grandTotalOutstanding_tenant = 0; 
															
															
															
															while($resultSqlAgeing_byTenant = mysqli_fetch_array($runSqlAgeing_byTenant)){
															
																$bill_bal = $resultSqlAgeing_byTenant['total_outstanding'];
																$invDate = $resultSqlAgeing_byTenant['issue_date'];
																$location = $resultSqlAgeing_byTenant['location'];
																$unit = $resultSqlAgeing_byTenant['unit'];
																$tenant_name = $resultSqlAgeing_byTenant['name'];
																$invDate = date("Y-m-d",strtotime($resultSqlAgeing_byTenant['issue_date']));
																$aged_date = date("Y-m-d", strtotime($dateEnd . ' + 1 days'));
																 
																$start_date = $invDate;
																$end_date = $aged_date;
																// call dateDifference() function to find the number of days between two dates
																$diff = dateDifference($start_date, $end_date);
																
														?>
														<tr bgcolor="#F8FAFE">
															<td align="center"><b><?php echo $bil; ?></b></td>
															<td align="center"><b><?php echo $resultSqlAgeing_byTenant['bill_no']; ?></b></td>
															<td align="center"><b><?php echo date("d/m/Y",strtotime($resultSqlAgeing_byTenant['issue_date'])); ?></b></td>
															<td align="center"><b><?php echo number_format($bill_bal,2); ?></b></td>
															<td align="center">
																<b>
																<?php 
																//echo $diff . " >> $start_date >> $end_date";
																	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 >= 91 && $diff <= 120){
																			echo number_format($bill_bal,2);
																			$bill_ball91120 += $bill_bal;
																			
																		}
																	?>
																</b>
															</td>
															<td align="center">
																<b><?php 
																		if($diff >= 121 && $diff <= 150){
																			echo number_format($bill_bal,2);
																			$bill_ball121150 += $bill_bal;
																			
																		}
																	?>
																</b>
															</td>
															<td align="center">
																<b><?php 
																		if($diff >= 151 && $diff <= 180){
																			echo number_format($bill_bal,2);
																			$bill_ball151180 += $bill_bal;
																			
																		}
																	?>
																</b>
															</td>
															<td align="center">
																<b><?php 
																		if($diff >= 181 && $diff <= 210){
																			echo number_format($bill_bal,2);
																			$bill_ball181210 += $bill_bal;
																			
																		}
																	?>
																</b>
															</td>
															<td align="center">
																<b><?php 
																		if($diff >= 211 && $diff <= 240){
																			echo number_format($bill_bal,2);
																			$bill_ball211240 += $bill_bal;
																			
																		}
																	?>
																</b>
															</td>
															<td align="center">
																<b><?php 
																		if($diff >= 241 && $diff <= 270){
																			echo number_format($bill_bal,2);
																			$bill_ball241270 += $bill_bal;
																			
																		}
																	?>
																</b>
															</td>
															<td align="center">
																<b><?php 
																		if($diff >= 271 && $diff <= 300){
																			echo number_format($bill_bal,2);
																			$bill_ball271300 += $bill_bal;
																			
																		}
																	?>
																</b>
															</td>
															<td align="center">
																<b><?php 
																		if($diff >= 301 && $diff <= 330){
																			echo number_format($bill_bal,2);
																			$bill_ball301330 += $bill_bal;
																			
																		}
																	?>
																</b>
															</td>
															<td align="center">
																<b><?php 
																		if($diff >= 331 && $diff <= 360){
																			echo number_format($bill_bal,2);
																			$bill_ball331360 += $bill_bal;
																			
																		}
																	?>
																</b>
															</td>
															<td align="center">
																<b><?php 
																if($leapYear == "yes"){
																		if($diff >= 361 && $diff <= 366){
																			echo number_format($bill_bal,2);
																			$bill_ball361365 += $bill_bal;
																			
																		}
																}
																else{
																		if($diff >= 361 && $diff <= 365){
																			echo number_format($bill_bal,2);
																			$bill_ball361365 += $bill_bal;
																			
																		}
																}
																	?>
																</b>
															</td>
														</tr>
														<?php
																$bil++;
																$tenant_totalOutstanding  += $bill_bal;
																$grandTotalOutstanding_tenant += $bill_bal;
															}//close grandTotalOutstanding_tenant
																//$bil_tenant++;
															
															?>
														
															<?php
															
															$grandTotalOutstanding_all += $tenant_totalOutstanding;
															$all_bill_ball30 += $bill_ball30;
															$all_bill_ball3160 += $bill_ball3160;
															$all_bill_ball6190 += $bill_ball6190;
															$all_bill_ball91120 += $bill_ball91120;
															$all_bill_ball121150 += $bill_ball121150;
															$all_bill_ball151180 += $bill_ball151180;
															$all_bill_ball181210 += $bill_ball181210;
															$all_bill_ball211240 += $bill_ball211240;
															$all_bill_ball241270 += $bill_ball241270;
															$all_bill_ball271300 += $bill_ball271300;
															$all_bill_ball301330 += $bill_ball301330;
															$all_bill_ball331360 += $bill_ball331360;
															$all_bill_ball361365 += $bill_ball361365;
															}//close grandTotalOutstanding_location
														?>
														
														
														<tr bgcolor="red">
															<td align="RIGHT" colspan = "3" ><font style="color:white"><b>JUMLAH KESELURUHAN TUNGGAKAN (RM): </b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($grandTotalOutstanding_all,2);?></b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($all_bill_ball30,2);?></b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($all_bill_ball3160,2);?></b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($all_bill_ball6190,2);?></b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($all_bill_ball91120,2);?></b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($all_bill_ball121150,2);?></b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($all_bill_ball151180,2);?></b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($all_bill_ball181210,2);?></b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($all_bill_ball211240,2);?></b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($all_bill_ball241270,2);?></b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($all_bill_ball271300,2);?></b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($all_bill_ball301330,2);?></b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($all_bill_ball331360,2);?></b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($all_bill_ball361365,2);?></b></font></td>
														</tr>
														<?PHP 
														//KIRAAN AGEING PERCENT - MULA
														//100% = grandTotalOutstanding_all
														$percent_bill_ball_all = ($grandTotalOutstanding_all / $grandTotalOutstanding_all) * 100;
														$percent_bill_ball30 = ($all_bill_ball30 / $grandTotalOutstanding_all) * 100;
														$percent_bill_ball3160 = ($all_bill_ball3160 / $grandTotalOutstanding_all) * 100;
														$percent_bill_ball6190 = ($all_bill_ball6190 / $grandTotalOutstanding_all) * 100;
														$percent_bill_ball91120 = ($all_bill_ball91120 / $grandTotalOutstanding_all) * 100;
														$percent_bill_ball121150 = ($all_bill_ball121150 / $grandTotalOutstanding_all) * 100;
														$percent_bill_ball151180 = ($all_bill_ball151180 / $grandTotalOutstanding_all) * 100;
														$percent_bill_ball181210 = ($all_bill_ball181210 / $grandTotalOutstanding_all) * 100;
														$percent_bill_ball211240 = ($all_bill_ball211240 / $grandTotalOutstanding_all) * 100;
														$percent_bill_ball241270 = ($all_bill_ball241270 / $grandTotalOutstanding_all) * 100;
														$percent_bill_ball271300 = ($all_bill_ball271300 / $grandTotalOutstanding_all) * 100;
														$percent_bill_ball301330 = ($all_bill_ball301330  / $grandTotalOutstanding_all) * 100;
														$percent_bill_ball331360 = ($all_bill_ball331360  / $grandTotalOutstanding_all) * 100;
														$percent_bill_ball361365 = ($all_bill_ball361365  / $grandTotalOutstanding_all) * 100;
														//KIRAAN AGEING PERCENT - TAMAT
														?>
														<tr bgcolor="red">
															<td align="RIGHT" colspan = "3" ><font style="color:white"><b>AGEING PERCENT: </b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($percent_bill_ball_all,2);?>%</b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($percent_bill_ball30,2);?>%</b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($percent_bill_ball3160,2);?>%</b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($percent_bill_ball6190,2);?>%</b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($percent_bill_ball91120,2);?>%</b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($percent_bill_ball121150,2);?>%</b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($percent_bill_ball151180,2);?>%</b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($percent_bill_ball181210,2);?>%</b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($percent_bill_ball211240,2);?>%</b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($percent_bill_ball241270,2);?>%</b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($percent_bill_ball271300,2);?>%</b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($percent_bill_ball301330,2);?>%</b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($percent_bill_ball331360,2);?>%</b></font></td>
															<td align="center"><font style="color:white"><b><?php echo number_format($percent_bill_ball361365,2);?>%</b></font></td>
														</tr>
												</table>
											</div>
										</div>
									</div>
									<?php  ?>
							
							
							
							
                        </div>
                        
                        <?PHP include "footer.php";?>
                        
                    </div>
                </div>
            </div>
            <!-- END MAIN CONTENT-->
            <!-- END PAGE CONTAINER-->
        </div>
		
    </div>
    <?PHP include "footerLink.php";?>
</body>

</html>
<!-- end document-->
