<?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
			
			
			?>
			
            
             <!--main content upper start-->
			<?php include "main-content-upper.php"?>
            <!--main content upper end-->
			<!--main content middle start-->
						<div class="row" >
							
									<?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));
									}

									$selectedLocation = "";
									$selectedTenantInfo = "";
									$tenantInfo = "";
									$cutOffPayment = "";
									$tenant_status = "";
									$_SESSION["aged_date"] ="";
									//$_SESSION["projectLocation"] = "";
									$_SESSION["tenantInfo"] = "";
									$_SESSION["cutOffPayment"] = "";
									$_SESSION["tenant_status"] = "";
									//$tenant_id = $_GET["tenant_id"];
									$sqlDate = "";
									$selectedDate_cut = "";
									$selectedDate_cutFrom = "";
									$selectedDate_cutTo = "";
									//$_SESSION['projectLocation']=$_GET['projectLocation'];

									$_SESSION['statement_date']="";
									$_SESSION['statement_year']="";
									$_SESSION['statement_monthyear']="";
									$_SESSION['chooseDate']="";
									$_SESSION["statement_ChooseDate"] =  "";
									$_SESSION["statementby"] = "";
									$statementby = "";
									$statement_year = "";
									$statement_monthyear = "";
									$statement_date = "";
									$statement_ByChooseDate = "";
									$selected_cutOffPayment = "";
									$displayYear = "display:none";
									$displayMonthYear = "display:none";
									$displayDateFromTo = "display:none";
									$displayChooseDate = "display:none";
									$displayCheckBox = "display:none";
									$displayStatementOption = "display:none";

									$selectedLocation = $_GET["projectLocation"];
									if($selectedLocation != "" || $selectedLocation != null){
											$projectLocation = $_GET["projectLocation"];
											include "splitLocationName_project.php";
											$project_id = $project_id;
											$location_id = $location_id;
										}
										
										
										
									   $sqlYear = "select YEAR(bills.issue_date) AS year 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 locations.id = '$location_id'
												group by year having (year <= YEAR(CURDATE()) and year <> 0 ) order by year ASC";
									$runSqlYear = mysqli_query($link, $sqlYear);
									if($runSqlYear === FALSE){
										die(mysqli_error($link));
									}

									if(isset($_GET["reset"])){
										
										$_SESSION["dateFrom"] ="";
										$_SESSION["dateTo"] ="";
										$_SESSION['statement_date']="";
										$_SESSION['statement_year']="";
										$_SESSION['statement_monthyear']="";
										$_SESSION['chooseDate']="";
										$_SESSION["cutOffPayment"] =  "";
										$_SESSION["statement_ChooseDate"] =  "";
										$_SESSION["statementby"] = "";
										$_GET["statementby"]= "";
										$_GET["statement_year"] = "";
										$_GET["statement_monthyear"] = "";
										$_GET["statement_date"] = "";
										$_GET["statement_ByChooseDate"] = "";
										$_GET["selected_cutOffPayment"] = "";
										$_GET["dateFrom"] = "";
										$_GET["dateTo"] = "";
										$_GET["chooseDate"] = "";
										$_GET["cutOffPayment"] = "";
										$_GET["statement_ChooseDate"] = "";
										$_GET["tenant_status"] = "";
										$_GET["block"] = "";
										
										
										
										$_SESSION["aged_date"] ="";
										//$_SESSION["projectLocation"] = "";
										$_SESSION["tenantInfo"] = "";
										$_SESSION["cutOffPayment"] = "";
										$_SESSION["cutOffPayment"] = "";
										$selectedDate = "";
										$selectedLocation = "";
										$selectedTenantInfo = "";
										$cutOffPayment = "";
										$tenant_status = "";
										 $displayYear = "display:none";
										$displayMonthYear = "display:none";
										$displayDateFromTo = "display:none";
										$displayChooseDate = "display:none";
										$displayCheckBox = "display:none";
										$statementby = "";
										$displayStatementOption = "display:none";
									}
									if(isset($_GET["search"])){
										$selectedLocation = $_GET["projectLocation"];
										$tenant_status = $_GET["tenant_status"];
										 "TENANT STATUS: " .$_SESSION["tenant_status"] = $tenant_status;
										$sqlTenantStatus = "";
										
										//echo "GOOD";
										$statementby = $_GET["statementby"];
										$_SESSION["statementby"] = $statementby;
										$sqlBlock = "";
										if($statementby != ""){
											
											$block = $_GET['block'];
											$sqlListBlock = "";
											$listBlock = "";
											$listBlock_plain = "";
											  if(!empty($block)) 
											  {
												 " COUNT BLOCK: ".$countBlock = count($block);
												for($i=0; $i < $countBlock; $i++)
												{
													($block[$i] . ",");
													if($listBlock == ""){
														$listBlock = "'" . $block[$i] ."'";
														$listBlock_plain = $block[$i];
													}
													else {
														$listBlock .= "," . "'" . $block[$i]."'";
														$listBlock_plain .= "," . $block[$i];
													}
												}
											  }
											IF($listBlock != ""){
												$sqlListBlock = " and properties.block in (" . $listBlock .") ";
											}
											$_SESSION["block"] = $block;
											
											if($tenant_status != ""){
												$sqlTenantStatus = " and tenants.status = '" .$tenant_status."' ";
											}
											
											$displayStatementOption = "display:block";
											
											if($statementby == "statement_year"){
												
												$statement_year = $_GET["statement_year"];
												$_SESSION["statement_year"] = $statement_year;
												$displayYear = "display:block";
												$displayMonthYear = "display:none";
												$displayChooseDate = "display:none";
												
												
												
												//cari tunggakan based on year selected - contoh selected 2023
												//jan-dec 2023
												//jan-dec 2022
												//jan-dec 2021
												//jan-dec 2020
												//jan-dec 2019
												//jan-dec 2018
												//perlu ada start date & end date untuk semak dlm issue Date
												$yearEnd = $statement_year;
												$monthStart = "01";
												$dayStart = "01";
												$monthEnd = "12";
												$dayEnd = "31";
												$yearStart = $statement_year - 5;
												$dateStart = $yearStart ."-".$monthStart."-".$dayStart;
												$dateEnd = $yearEnd ."-".$monthEnd."-".$dayEnd;
												
												//currentdate
												$currentdate = date("Y-m-d");
												$current_year = date("Y",strtotime($currentdate));
												if($current_year == $yearEnd){
													$dateEnd = date("Y-m-d");
												}
												
												$sql6YearsOutstanding_statementYear = "select 
																			tenants.id as tenant_id,
																			properties.unit_no,
																			locations.location_name,
																			projects.project_name,
																			tenants.tenant_name
																			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 = 1 AND bills.issue_date >=  '$dateStart' and bills.issue_date <=  '$dateEnd'
                                                                            and locations.id = ".$_SESSION["location_id"]." and bills.amount_due > 0.00 and 
																			(bills.payment_status = 'partial' or bills.payment_status = 'unpaid') $sqlListBlock $sqlTenantStatus
																			group by tenants.id order by properties.id, bills.issue_date ASC
																			";
												
												
												$run6YearsOutstanding_statementYear = mysqli_query($link,$sql6YearsOutstanding_statementYear);
												if($run6YearsOutstanding_statementYear === FALSE){
													die(mysqli_error($link));//TODO: better error handling
												}
												$date_statementOptionView = $statement_year;
											}
											else if($statementby == "statement_monthyear"){
												$statement_monthyear = $_GET["statement_monthyear"];
												$_SESSION["statement_monthyear"] = $statement_monthyear;
												$displayYear = "display:none";
												$displayMonthYear = "display:block";
												$displayChooseDate = "display:none";
												$date_monthYear = date("Y-m-01",strtotime($statement_monthyear));
												
												$yearEnd = date("Y",strtotime($statement_monthyear));
												$month = date("m",strtotime($statement_monthyear));
												//$dayStart = "01";
												//$monthEnd = date("m",strtotime($statement_monthyear));
												//$dayEnd = "31";
												 "::yearStart::::" .$yearStart = $yearEnd - 5;
												$columnOutstanding = 7;
												$columnCount = 1;
												$year = $yearStart;
												$prevYear = $yearStart - 1;
												$prevMonth = $month + 1;
												if($prevMonth == 13)
													$prevMonth = 1; //Jan
												$prevYearDate = $prevYear . "-" . $prevMonth ."-01";
												$prevYearDate = date("Y-m-d",strtotime($prevYearDate));
												
												$dateStart = $prevYearDate;
												$dateEnd = date("Y-m-t",strtotime($statement_monthyear));
																			
												 $sql6YearsOutstanding_statementMonthYear = "select tenants.id as tenant_id, 
																							properties.unit_no, 
																							locations.location_name,
																							projects.project_name,
																							tenants.tenant_name 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 = 1 AND 
																							bills.issue_date >=  '$dateStart' and bills.issue_date <=  '$dateEnd' and
																							locations.id = ".$_SESSION["location_id"]." and 
																							bills.amount_due > 0.00 and 
																							(bills.payment_status = 'partial' or bills.payment_status = 'unpaid')  $sqlListBlock $sqlTenantStatus
																							group by tenants.id order by properties.id";
																							
												$run6YearsOutstanding_statementMonthYear = mysqli_query($link,$sql6YearsOutstanding_statementMonthYear);
												if($run6YearsOutstanding_statementMonthYear === FALSE){
													die(mysqli_error($link));//TODO: better error handling
												}
												
												
												
												
												 "::prevYearTest::" . $prevYearTest = date("Y", strtotime($yearStart,"-1 year")); 
												$dateStart = $yearStart ."-".$month;
												$dateEnd = $yearEnd ."-".$month;
												$dateStart = date("Y-m-01", strtotime($dateStart));
												$dateEnd = date("Y-m-t", strtotime($dateEnd));
												 " DATE MONTH YEAR::".$date_monthYear;
												 " DATE dateStart::".$dateStart;
												 " DATE dateEnd::".$dateEnd;
												
												
												
												$date_statementOptionView = $date_monthYear;
												
											}
											else if($statementby == "statement_ChooseDate"){
												 " STATEMENT CHOOSE DATE: " .$statement_ChooseDate = $_GET["statement_ChooseDate"];
												$_SESSION["statement_ChooseDate"] = $statement_ChooseDate;
												
												$displayYear = "display:none";
												$displayMonthYear = "display:none";
												$displayChooseDate = "display:block";
												$statement_ChooseDate = date("Y-m-d",strtotime($statement_ChooseDate));
												
												
												$dateEnd = $statement_ChooseDate;
												
												//cari dateStart untuk 6 tahun kebelakang - mula
												$yearEnd = date("Y",strtotime($statement_ChooseDate));
												$yearStart = $yearEnd - 5;
												//semak tarikh yang dipilih tarikh hujung bulan atau tidak untuk tentukan month dan day
												$month = date("m",strtotime($statement_ChooseDate));
												$monthYearEnd = $yearEnd ."-". $month;
												 $lastMonthYearDate = date("Y-m-t", strtotime($monthYearEnd));
												$listMonthYear_td = array();
												if($dateEnd == $lastMonthYearDate){
													//echo "YES!!!";
													$dayStart = "01";
													 "monthStart: " .$monthStart = $month + 1;
													
													if($monthStart == 13){
														$monthStart = 1;
													}
													
													$yearStart = $yearStart - 1; 
													if(strlen($monthStart) == 1){
														$monthStart = "0" . $monthStart;
													}
													
													
													for($i=1;$i<7;$i++){
														$dateFrom = $yearStart ."-". $monthStart ."-". $dayStart;
														//echo " DATEFROM2:: " .$dateFrom = date("Y-m-d",strtotime($dateStart));
														
														$yearTo = $yearStart + 1;
														$monthTo = $month;
														
														$dateTo = $yearTo ."-".$monthTo;
														$dateTo = date("Y-m-t",strtotime($dateTo));
														
														$listMonthYear_td[] = $dateFrom ."," .$dateTo;
														$yearStart++;
													}
												}
												else{
													//echo "NO!!!";
													$day = date("d",strtotime($statement_ChooseDate));
													$dayStart = $day + 1;
													$monthStart = $month;
													$yearStart = $yearStart - 1; 
													if(strlen($monthStart) == 1){
														$monthStart = "0" . $monthStart;
													}
													for($i=1;$i<7;$i++){
														$dateFrom = $yearStart ."-". $monthStart ."-". $dayStart;
														//$dateFrom = date("Y-m-d",strtotime($dateStart));
														
														$yearTo = $yearStart + 1;
														$monthTo = $month;
														$dayTo = $day;
														
														$dateTo = $yearTo ."-".$monthTo ."-". $day;
														$dateTo = date("Y-m-d",strtotime($dateTo));
														
														$listMonthYear_td[] = $dateFrom ."," .$dateTo;
														$yearStart++;
													}
													
												}
												
												$dateRange_start = explode(",", $listMonthYear_td[1]);
												$dateRange_end = explode(",", $listMonthYear_td[5]);
												
												$dateStart = $dateRange_start[0];
												$dateEnd =  $dateRange_end[1];
												$columnOutstanding = 7;
												$columnCount = 1;
												$year = $yearStart;
												
												  $sql6YearsOutstanding_statementChooseDate = "select tenants.id as tenant_id, 
																							properties.unit_no, 
																							locations.location_name,
																							projects.project_name,
																							tenants.tenant_name 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 = 1 AND 
																							bills.issue_date >=  '$dateStart' and bills.issue_date <=  '$dateEnd' and
																							locations.id = ".$_SESSION["location_id"]." and 
																							bills.amount_due > 0.00 and 
																							(bills.payment_status = 'partial' or bills.payment_status = 'unpaid') $sqlListBlock $sqlTenantStatus
																							group by tenants.id order by properties.id";
																							
												$run6YearsOutstanding_statementChooseDate = mysqli_query($link,$sql6YearsOutstanding_statementChooseDate);
												if($run6YearsOutstanding_statementChooseDate === FALSE){
													die(mysqli_error($link));//TODO: better error handling
												}
												
												
												$date_statementOptionView = $statement_ChooseDate;
												//print_r($listMonthYear_td);
											}
										}
									}
									?>
							<div class="col-lg-12">
								<div align="center">
									<form action="<?php $_SERVER['PHP_SELF']; ?>" method="get">
										<input type="hidden" name="tenant_id" value="<?php echo $tenant_id?>">
										<input type="hidden" name="login" value="<?php echo $_GET["login"]?>">
										<input type="hidden" name="menu" value="<?php echo $_GET["menu"]?>">
										<input type="hidden" name="submenu" value="<?php echo $_GET["submenu"]?>">
											<div class="card shadow mb-4">
												<div class="card-header py-3">
													<h3 class="m-0 font-weight-bold text-primary">SEARCH OPTION</h3>
												</div>
												<div class="card-body">
													<div class="table-responsive">
														<table class="table table-bordered" id="dataTable" width="50%" cellspacing="0" style="font-size: 12px" class="form-control">
															<tr>
																<td width="30%"><b>LOCATION</b></td>
																<td width="70%">
																	<input type="text" list="projectLocation" name="projectLocation" class="form-control" onChange="javascript: submit();"  value="<?php echo $_SESSION['projectLocation']?>" placeholder="Select Location" readonly />
																	<datalist id="projectLocation">
																	
																	<?php
																	
																		if($_SESSION["username"] != "admin"){
																			
																			 $sqlLocations = "SELECT * FROM ( 
																						(select EL.location_id, locations.location_name, projects.project_name from extra_locations as EL 
																						inner join locations on locations.id = EL.location_id 
																						inner join projects on locations.project_id = projects.id 
																						inner join users on EL.user_id =  users.id
																						where users.id = ". $_SESSION['staff_id'] .") 
																					UNION 
																						(select locations.id, locations.location_name, projects.project_name from locations 
																						inner join users on locations.id = users.location_id 
																						inner join projects on locations.project_id = projects.id
																						where users.id = ". $_SESSION['staff_id'] .") 
																					) results ORDER BY project_name ASC";
																			
																			$runLocations = mysqli_query($link,$sqlLocations);
																			if($runLocations === FALSE){
																				die(mysqli_error($link));//TODO: better error handling
																			}
																		}
																		else if($_SESSION["username"] == "admin"){
																			$sqlLocations = "select locations.*, projects.project_name ,  projects.id as project_id  from locations 
																								inner join projects on locations.project_id = projects.id 
																								ORDER BY projects.project_name ASC";
																			$runLocations = mysqli_query($link, $sqlLocations);
																			if($runLocations === FALSE){
																				die(mysqli_error($link));
																			}
																		}

																		while($resultLocations = mysqli_fetch_array($runLocations)){
																			$location_id = $resultLocations["id"];
																			$location_name = $resultLocations["location_name"];
																			$project_name = $resultLocations["project_name"];
																				?>
																				
																				<option value="<?php echo $project_name . "-" . $location_name; ?>">
																				<?php
																		}
																	?>
																	</datalist> <?php //echo $sqlLocations?>
																</td>
															</tr>
															<tr>
																<td><b>BLOCK</b></td>
																<td>
																<?php 
																
																	$sqlBlock = "select properties.block as block from properties inner join locations on properties.location_id = locations.id where locations.id = " . $_SESSION["location_id"] . " group by properties.block";
																	$runBlock = mysqli_query($link, $sqlBlock);
																	if($runBlock === FALSE){
																		die(mysqli_error($link));
																	}
																	while($resultBlock = mysqli_fetch_array($runBlock)){
																		$blocklist = $resultBlock["block"];
																		
																		?>
																		<div class="form-check-inline form-check">
																			<label for="inline-checkbox[]" class="form-check-label">
																				<input type="checkbox" id="inline-checkbox[]" class="checkbox" name="block[]" value="<?php echo $blocklist?>" class="form-check-input" ><b><font style="font-size: 14px"><?php echo strtoupper($blocklist)?></font></b>
																			</label>
																		</div>
																		<?php
																			//}
																	}
																?>
																</td>
															</tr>
															<tr>
																<td><b>TENANT STATUS</b></td>
																<td>
																	<div class="form-check-inline form-check">
																		<div class="radio">
																			<label for="radio1" class="form-check-label ">
																				<input type="radio" id="radio1" name="tenant_status" value="enable" class="form-check-input" <?php echo $_SESSION["tenant_status"] == "enable" ? "checked" : "" ?>><b><font style="font-size: 14px">Active</font></b>
																			</label> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
																			<label for="radio2" class="form-check-label ">
																				<input type="radio" id="radio2" name="tenant_status" value="disable" class="form-check-input" <?php echo $_SESSION["tenant_status"] == "disable" ? "checked" : "" ?>><b><font style="font-size: 14px">Deactive</font></b>
																			</label>
																		</div>
																	</div>
																</td>
															</tr>
															<tr>
																<tH VALIGN="TOP" ALIGN="LEFT" >CUT OFF OPTION FOR MORE THAN 6 YEARS OUTSTANDING</tH>
																<td>
														
																	<select name="statementby" id="statementby" onchange="statementby_change()" class="form-control" >
																		<option value="">Select Statement Option</option>
																		<option value="statement_year" <?php echo ($_SESSION["statementby"] ==  'statement_year')  ? "selected" : "" ?>>STATEMENT BY YEAR</option>
																		<option value="statement_monthyear" <?php echo ($_SESSION["statementby"] ==  'statement_monthyear')  ? "selected" : "" ?>>STATEMENT BY MONTH AND YEAR</option>
																		<option value="statement_ChooseDate" <?php echo ($_SESSION["statementby"] ==  'statement_ChooseDate')  ? "selected" : "" ?> >STATEMENT BY CUT OFF DATE</option>
																	</select>
																	&nbsp;
																	<div id="statement_ByYear" style='<?php echo $displayYear ?>'>
																		<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 id="statement_ByMonthYear" style='<?php echo $displayMonthYear  ?>;font-size:12px'>
																		<input type="month"  name="statement_monthyear" id="statement_monthyear" value="<?php echo $_SESSION["statement_monthyear"]?>" class="form-control" size="20"/>
																	</div>
																	<div id="statement_ByChooseDate" style='<?php echo $displayChooseDate ?>;font-size:12px'>
																		Choose Date <?echo $_SESSION["statementby"]?><input class="form-control" id='statement_chooseDate' name='statement_ChooseDate' type='date' size='10' value="<?php echo $_SESSION["statement_ChooseDate"]?>"> 
																	</div>
																</td>
															</tr>
															
															<tr>
																<td colspan="2" align="center">
																	<br/>
																	<button class="btn btn-primary btn-sm" type="submit" name="search"><i class="fa fa-dot-circle-o"></i> SEARCH</button>
																	<button class="btn btn-danger btn-sm" type="submit" name="reset"><i class="fa fa-ban"></i> RESET</button>
																</td>
															</tr>
														</table>
												</DIV>
											</DIV>
										</DIV>
									</form>
								</div>
								<?php if(isset($_GET["search"])){ ?>
								<div class="card shadow mb-4">
									<div class="card-header py-3">
										<h3 class="m-0 font-weight-bold text-primary">MAKLUMAT TUNGGAKAN  </h3>
									</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="11">
															<div align="center">
																<button  class="btn btn-success btn-sm" onclick="print_report6YearsOutstanding('<?php echo $statementby ?>','<?php echo $date_statementOptionView?>','<?php echo $listBlock_plain?>','<?php echo $tenant_status?>')" >PRINT</button>
																<button  class="btn btn-success btn-sm" onclick="export_report6YearsOutstanding('<?php echo $statementby ?>','<?php echo $date_statementOptionView?>','<?php echo $listBlock_plain?>','<?php echo $tenant_status?>')" >EXPORT CSV</button>
															</div>
														</td>
													</tr>
													<?php if($statementby == "statement_year"){?>
													<tr bgcolor="#ebeced">
														<th  ><center>#</center></th>
														<th><center>Unit No.</center></th>
														<th><center>Name</center></th>
														<?php 
															for($i = $yearStart; $i<=$yearEnd; $i++){
														?>
														<th><center><?PHP echo $i?></center></th>
														<?php } ?>
														
														<th><center>Total (RM)</center></th>
													</tr>
												</thead>
												<tbody>
													<tr >
													<?php 
													$no=1;
													$tenantIdCheck = "";
													$year_td = $yearStart;
													$totalOutstanding = 0.00;
													$columnOutstanding = 7;
													$columnCount = 1;
													
													while($result6YearsOutstanding_statementYear = mysqli_fetch_array($run6YearsOutstanding_statementYear)){
														$unit_no = $result6YearsOutstanding_statementYear["unit_no"];
														$tenant_id = $result6YearsOutstanding_statementYear["tenant_id"];
														$tenant_name = $result6YearsOutstanding_statementYear["tenant_name"];
														$outstanding = $result6YearsOutstanding_statementYear["outstanding"];
														$yearOutstanding = $result6YearsOutstanding_statementYear["issue_date"];
														$location_name = $result6YearsOutstanding_statementYear["location_name"];
														$yearOutstanding = date("Y",strtotime($yearOutstanding));
														$totalOutstanding = 0.00;
														?>
															<td><?php echo $no ?></td>
															<td><?php echo strtoupper($unit_no .", ". $location_name)  ?></td>
															<td><?php echo strtoupper($tenant_name) ?></td>
														<?php
																												
														$listMonthYear_td = array();
														
														for($i = $yearStart; $i<=$yearEnd; $i++){
															$year = $i;
															$monthStart = "01";
															$monthEnd = "12";
															
															$dateStart = $year ."-01-01";
															$dateEnd = $year ."-12-31";
															$dateStart = date("Y-m-d", strtotime($dateStart));
															$dateEnd = date("Y-m-t", strtotime($dateEnd));
															
															//currentdate
															$currentdate = date("Y-m-d");
															$current_year = date("Y",strtotime($currentdate));
															if($current_year == $year){
																$dateEnd = date("Y-m-d");
															}
															
															    $sql6YearsOutstanding_statementYear_byTenant = "select sum(bills.amount_due) as outstanding
																			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 = 1 AND bills.issue_date >=  '$dateStart' and bills.issue_date <=  '$dateEnd'
                                                                            and locations.id = ".$_SESSION["location_id"]." and bills.amount_due > 0.00 and 
																			(bills.payment_status = 'partial' or bills.payment_status = 'unpaid') and tenants.id = $tenant_id
																			group by tenants.id order by properties.unit_no, bills.issue_date ASC
																			";
															$run6YearsOutstanding_statementYear_byTenant = mysqli_query($link, $sql6YearsOutstanding_statementYear_byTenant);
															if($run6YearsOutstanding_statementYear_byTenant === FALSE){
																die(mysqli_error($link));
															}
															$result6YearsOutstanding_statementYear_byTenant = mysqli_fetch_assoc($run6YearsOutstanding_statementYear_byTenant);
															$outstanding = $result6YearsOutstanding_statementYear_byTenant["outstanding"];
															if($outstanding == null || $outstanding == ""){
																?>
																<td align="right">-</td>
																<?php 
															}
															else{
																?>
																<td align="right"><a href="viewOutStandingByAgeing.php?tenant_id=<?php echo $tenant_id?>&dateStart=<?php echo $dateStart?>&dateEnd=<?php echo $dateEnd?>" target="_blank"><?php echo number_format($outstanding,2,".",",")?></a></td>
																<?php 
																$totalOutstanding += $outstanding;
																
																if($totalMonthYear_td_array[$i] == "")
																	$totalMonthYear_td_array[$i] = $outstanding ;
																else if($totalMonthYear_td_array[$i] != "")
																	$totalMonthYear_td_array[$i] .= "," . $outstanding;
															}
															
														}
														
														?>
														
														<td align="right"><?php echo number_format($totalOutstanding,2,".",",")?></td>
														
														<?php
														
													$no++;
													?>
													</tr>
													
													<?php }
													 
											//row Total bawah sekali
											$columnOutstanding = 6;
											$columnCount = 1;												
											$sqlTotalAmountOutstanding = "select sum(bills.amount_due) as bigTotalAmountOutstanding, YEAR(bills.issue_date) as yearBillIssue 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  YEAR(bills.issue_date) >=  '$yearStart' and YEAR(bills.issue_date) <=  '$yearEnd' and 
																			bills.bill_type_id = 1 and (bills.payment_status = 'partial' or bills.payment_status = 'unpaid')  and   bills.amount_due > 0.00 and 
																			locations.id =  ".$_SESSION["location_id"]."  and 
																			projects.id = 2 $sqlListBlock $sqlTenantStatus
																			group by YEAR(bills.issue_date)
																			order by YEAR(bills.issue_date) ASC";
											
											$runTotalAmountOutstanding = mysqli_query($link, $sqlTotalAmountOutstanding);
											if($runTotalAmountOutstanding === FALSE){
												die(mysqli_error($link));
											}
											$year_td = $yearStart;
											while($resultTotalAmountOutstanding = mysqli_fetch_array($runTotalAmountOutstanding)){
												
												$yearBillIssue = $resultTotalAmountOutstanding["yearBillIssue"];
												$bigTotalAmountOutstanding = $resultTotalAmountOutstanding["bigTotalAmountOutstanding"];
												
												if($yearBillIssue == $year_td){
													$tdBigTotalAmountOutstanding .= "<td align='right' ><b>".number_format($bigTotalAmountOutstanding,2,'.',',')."</b></td>";
												}
												else if($yearBillIssue > $year_td){
													
													$diffYear = $yearBillIssue - $year_td;
													for($i=1;$i<=$diffYear;$i++){
														$tdBigTotalAmountOutstanding .= "<td align='right' ><b> 0.00 </b></td>";
														$year_td++;
														$columnCount++;
													}
													$tdBigTotalAmountOutstanding .= "<td align='right' ><b>".number_format($bigTotalAmountOutstanding,2,'.',',')."</b></td>";
													
												}
												else if($yearBillIssue < $year_td){
													$diffYear = $yearEnd - $year_td;
												}
												$year_td++;
												$columnCount++;
											}
											
											 $sqlTotalAllAmountOutstanding = "select sum(bills.amount_due) as bigTotalAllAmountOutstanding, YEAR(bills.issue_date) as yearBillIssue 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  YEAR(bills.issue_date) >=  '$yearStart' and YEAR(bills.issue_date) <=  '$yearEnd' and 
																			bills.bill_type_id = 1 and (bills.payment_status = 'partial' or bills.payment_status = 'unpaid')  and   bills.amount_due > 0.00 and 
																			locations.id =  ".$_SESSION["location_id"]."  and 
																			projects.id = 2 $sqlListBlock $sqlTenantStatus
																			group by projects.id";
											
											$runTotalAllAmountOutstanding = mysqli_query($link, $sqlTotalAllAmountOutstanding);
											if($runTotalAllAmountOutstanding === FALSE){
												die(mysqli_error($link));
											}
											
											while($resultTotalAllAmountOutstanding = mysqli_fetch_array($runTotalAllAmountOutstanding)){
												$bigTotalAllAmountOutstanding = $resultTotalAllAmountOutstanding["bigTotalAllAmountOutstanding"];
											}
													?>
												
													<tr>
														<td colspan="3"><b>TOTAL (RM)</b></td>
														<?php echo $tdBigTotalAmountOutstanding ?>
														<td align='right'><b><?php echo number_format($bigTotalAllAmountOutstanding,2) ?></b></td>
														
													</tr>
												</tbody>	
						
										<?php 
											} 
										//}
													else if($statementby == "statement_monthyear"){
														$listMonthYear_td = array();
														
														for($i=1;$i<7;$i++){
															
															$prevYear = $year - 1;
															$prevMonth = $month + 1;
															
															if($prevMonth == 13)
																$prevMonth = 1; //Jan
															
															$prevYearDate = $prevYear . "-" . $prevMonth ."-01";
															$prevYearDate = date("Y-m-d",strtotime($prevYearDate));
															
															$monthYear = $year ."-" .$month;
															$monthYearDate = date("Y-m-t",strtotime($monthYear));
															
															$listMonthYear_td[] = $prevYearDate ."," .$monthYearDate;
															
															$year++;
															
														}
														$countListMonthYear_td = count($listMonthYear_td);
														$th_dateRange = "";
														for($i=0;$i<$countListMonthYear_td;$i++){
															
															$dateRange = explode(",", $listMonthYear_td[$i]);
															
															$th_dateRange .= "<Th><center>". date("d/m/Y",strtotime($dateRange[0])) . " - " . date("d/m/Y",strtotime($dateRange[1])) . "</center></th>";
														}
														
														
															?>
													<tr bgcolor="#ebeced">
														<th  ><center>#</center></th>
														<th><center>Unit No.</center></th>
														<th><center>Name</center></th>
														<?php 
															echo $th_dateRange ;
														?>
														
														<th><center>Total (RM)</center></th>
													</tr>
												</thead>
												<tbody>
													<tr >
													<?php 
													$no=1;
													$tenantIdCheck = "";
													$year_td = $yearStart;
													$totalOutstanding = 0.00;
													$columnOutstanding = 7;
													$columnCount = 1;
													
													//dapatkan senarai tarikh start dan end bagi setiap 6 column (6 tahun tunggakan)
													
													$year = $yearStart;
													
													
													$listTenantInfo = array();
													$listTenantOutstanding = array();
													
													$no=1;
													 while($result6YearsOutstanding_statementMonthYear = mysqli_fetch_array($run6YearsOutstanding_statementMonthYear)){
														$unit_no = $result6YearsOutstanding_statementMonthYear["unit_no"];
														$tenant_id = $result6YearsOutstanding_statementMonthYear["tenant_id"];
														$tenant_name = $result6YearsOutstanding_statementMonthYear["tenant_name"];
														$outstanding = $result6YearsOutstanding_statementMonthYear["outstanding"];
														$monthYearOutstanding = $result6YearsOutstanding_statementMonthYear["issue_date"];
														$location_name = $result6YearsOutstanding_statementMonthYear["location_name"];
														$yearOutstanding = date("Y",strtotime($yearOutstanding));
														//$dateRange = array();
														$totalOutstanding = 0.00;
														
															?>
															<td><?php echo $no ?></td>
															<td><?php echo strtoupper($unit_no .", ". $location_name)  ?></td>
															<td><?php echo strtoupper($tenant_name) ?></td>
															<?php
															$totalMonthYear_td_array = array();
															$totalMonthYear = 0.00;
															for($i=0;$i<$countListMonthYear_td;$i++){
															
																$dateRange = explode(",", $listMonthYear_td[$i]);
																$dateStart = $dateRange[0];
																$dateEnd = $dateRange[1];
																//$th_dateRange .= "<Th><center>". date("d/m/Y",strtotime($dateRange[0])) . " - " . date("d/m/Y",strtotime($dateRange[1])) . "</center></th>";
																  $sql6YearsOutstanding_statementMonthYear_bytenant = "select 
																			sum(bills.amount_due) as outstanding

																			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 = 1 AND bills.issue_date >=  '$dateStart' and bills.issue_date <=  '$dateEnd'
                                                                            and locations.id = ".$_SESSION["location_id"]." and bills.amount_due > 0.00 and 
																			(bills.payment_status = 'partial' or bills.payment_status = 'unpaid')  and tenants.id = $tenant_id
																			
																			";
																$run6YearsOutstanding_statementMonthYear_bytenant = mysqli_query($link, $sql6YearsOutstanding_statementMonthYear_bytenant);
																if($run6YearsOutstanding_statementMonthYear_bytenant === FALSE){
																	die(mysqli_error($link));
																}
																$resul6YearsOutstanding_statementMonthYear_bytenant = mysqli_fetch_assoc($run6YearsOutstanding_statementMonthYear_bytenant);
																$outstanding = $resul6YearsOutstanding_statementMonthYear_bytenant["outstanding"];
																if($outstanding == null || $outstanding == ""){
																	?>
																	<td align="right">-</td>
																	<?php 
																}
																else{
																	?>
																	<!--<td align="right"><?php echo number_format($outstanding,2,".",",")?></td>-->
																	<td align="right"><a href="viewOutStandingByAgeing.php?tenant_id=<?php echo $tenant_id?>&dateStart=<?php echo $dateStart?>&dateEnd=<?php echo $dateEnd?>" target="_blank"><?php echo number_format($outstanding,2,".",",")?></a></td>
																	<?php 
																	$totalOutstanding += $outstanding;
																	
																	if($totalMonthYear_td_array[$i] == "")
																		$totalMonthYear_td_array[$i] = $outstanding ;
																	else if($totalMonthYear_td_array[$i] != "")
																		$totalMonthYear_td_array[$i] .= "," . $outstanding;
																}
															}
															?>
															<td align="right"><?php echo number_format($totalOutstanding,2,".",",")?></td>
															<?php
														$no++;	
														?>
														</tr>
														
														<?php
													}
													?>
														<tr>
															<td colspan = "3" align="middle"><b>TOTAL (RM)</b></td>
															<?PHP 
															$totalAll = 0.00;
															for($i=0;$i<$countListMonthYear_td;$i++){
																$dateRange = explode(",", $listMonthYear_td[$i]);
																$dateStart = $dateRange[0];
																$dateEnd = $dateRange[1];
																 $sql6YearsOutstanding_statementMonthYear_total = "select 
																			sum(bills.amount_due) as total_outstanding

																			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 = 1 AND bills.issue_date >=  '$dateStart' and bills.issue_date <=  '$dateEnd'
                                                                            and locations.id = ".$_SESSION["location_id"]." and bills.amount_due > 0.00 and 
																			(bills.payment_status = 'partial' or bills.payment_status = 'unpaid')  $sqlListBlock $sqlTenantStatus
																			
																			";
																$run6YearsOutstanding_statementMonthYear_total = mysqli_query($link, $sql6YearsOutstanding_statementMonthYear_total);
																if($run6YearsOutstanding_statementMonthYear_total === FALSE){
																	die(mysqli_error($link));
																}
																$resul6YearsOutstanding_statementMonthYear_total = mysqli_fetch_assoc($run6YearsOutstanding_statementMonthYear_total);
																$total_outstanding = $resul6YearsOutstanding_statementMonthYear_total["total_outstanding"];
																if($total_outstanding == null || $total_outstanding == ""){
																	?>
																	<td align="right"><b>0.00</b></td>
																	<?php 
																}
																else{
																	?>
																	<td align="right"><b><?php echo number_format($total_outstanding,2,".",",")?></b></td>
																	<?php 
																	$totalOutstandingAll += $total_outstanding;
																	
																}
															}
															
															?>
															<td align="right"><b><?php echo number_format($totalOutstandingAll,2,".",",")?></b></td>
														</tr>
													<?php
													
												}
												else if($statementby == "statement_ChooseDate"){
													
													$countListMonthYear_td = count($listMonthYear_td);
													$th_dateRange = "";
													for($i=0;$i<$countListMonthYear_td;$i++){
														
														$dateRange = explode(",", $listMonthYear_td[$i]);
														
														$th_dateRange .= "<Th><center>". date("d/m/Y",strtotime($dateRange[0])) . " - " . date("d/m/Y",strtotime($dateRange[1])) . "</center></th>";
													}
														
														
															?>
													<tr bgcolor="#ebeced">
														<th  ><center>#</center></th>
														<th><center>Unit No.</center></th>
														<th><center>Name</center></th>
														<?php 
															echo $th_dateRange ;
														?>
														
														<th><center>Total (RM)</center></th>
													</tr>
													<tr>
													<?php
													$no=1;
													while($result6YearsOutstanding_statementChooseDate = mysqli_fetch_array($run6YearsOutstanding_statementChooseDate)){
														$unit_no = $result6YearsOutstanding_statementChooseDate["unit_no"];
														$tenant_id = $result6YearsOutstanding_statementChooseDate["tenant_id"];
														$tenant_name = $result6YearsOutstanding_statementChooseDate["tenant_name"];
														$outstanding = $result6YearsOutstanding_statementChooseDate["outstanding"];
														$monthYearOutstanding = $result6YearsOutstanding_statementChooseDate["issue_date"];
														$location_name = $result6YearsOutstanding_statementChooseDate["location_name"];
														$yearOutstanding = date("Y",strtotime($yearOutstanding));
														//$dateRange = array();
														$totalOutstanding = 0.00;
														
															?>
															<td><?php echo $no ?></td>
															<td><?php echo strtoupper($unit_no .", ". $location_name)  ?></td>
															<td><?php echo strtoupper($tenant_name) ?></td>
															<?php
															$totalMonthYear_td_array = array();
															$totalMonthYear = 0.00;
															for($i=0;$i<$countListMonthYear_td;$i++){
															
																$dateRange = explode(",", $listMonthYear_td[$i]);
																$dateStart = $dateRange[0];
																$dateEnd = $dateRange[1];
																//$th_dateRange .= "<Th><center>". date("d/m/Y",strtotime($dateRange[0])) . " - " . date("d/m/Y",strtotime($dateRange[1])) . "</center></th>";
																 $sql6YearsOutstanding_statementMonthYear_bytenant = "select 
																			sum(bills.amount_due) as outstanding

																			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 = 1 AND bills.issue_date >=  '$dateStart' and bills.issue_date <=  '$dateEnd'
                                                                            and locations.id = ".$_SESSION["location_id"]." and bills.amount_due > 0.00 and 
																			(bills.payment_status = 'partial' or bills.payment_status = 'unpaid')  and tenants.id = $tenant_id
																			
																			";
																$run6YearsOutstanding_statementMonthYear_bytenant = mysqli_query($link, $sql6YearsOutstanding_statementMonthYear_bytenant);
																if($run6YearsOutstanding_statementMonthYear_bytenant === FALSE){
																	die(mysqli_error($link));
																}
																$resul6YearsOutstanding_statementMonthYear_bytenant = mysqli_fetch_assoc($run6YearsOutstanding_statementMonthYear_bytenant);
																$outstanding = $resul6YearsOutstanding_statementMonthYear_bytenant["outstanding"];
																if($outstanding == null || $outstanding == ""){
																	?>
																	<td align="right">-</td>
																	<?php 
																}
																else{
																	?>
																	<!--<td align="right"><?php echo number_format($outstanding,2,".",",")?></td>-->
																	<td align="right"><a href="viewOutStandingByAgeing.php?tenant_id=<?php echo $tenant_id?>&dateStart=<?php echo $dateStart?>&dateEnd=<?php echo $dateEnd?>" target="_blank"><?php echo number_format($outstanding,2,".",",")?></a></td>
																	<?php 
																	$totalOutstanding += $outstanding;
																	
																	if($totalMonthYear_td_array[$i] == "")
																		$totalMonthYear_td_array[$i] = $outstanding ;
																	else if($totalMonthYear_td_array[$i] != "")
																		$totalMonthYear_td_array[$i] .= "," . $outstanding;
																}
															}
															?>
															<td align="right"><?php echo number_format($totalOutstanding,2,".",",")?></td>
															<?php
														$no++;	
													
														?>
													</tr>
														
														<?php
														}
														?>
														
														<tr>
															<td colspan = "3" align="middle"><b>TOTAL (RM)</b></td>
															<?PHP 
															$totalAll = 0.00;
															for($i=0;$i<$countListMonthYear_td;$i++){
																$dateRange = explode(",", $listMonthYear_td[$i]);
																$dateStart = $dateRange[0];
																$dateEnd = $dateRange[1];
																 $sql6YearsOutstanding_statementChooseDate_total = "select 
																			sum(bills.amount_due) as total_outstanding

																			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 = 1 AND bills.issue_date >=  '$dateStart' and bills.issue_date <=  '$dateEnd'
                                                                            and locations.id = ".$_SESSION["location_id"]." and bills.amount_due > 0.00 and 
																			(bills.payment_status = 'partial' or bills.payment_status = 'unpaid')  $sqlListBlock $sqlTenantStatus
																			
																			";
																$sql6YearsOutstanding_statementChooseDate_total = mysqli_query($link, $sql6YearsOutstanding_statementChooseDate_total);
																if($sql6YearsOutstanding_statementChooseDate_total === FALSE){
																	die(mysqli_error($link));
																}
																$resul6YearsOutstanding_statementChooseDate_total = mysqli_fetch_assoc($sql6YearsOutstanding_statementChooseDate_total);
																$total_outstanding = $resul6YearsOutstanding_statementChooseDate_total["total_outstanding"];
																if($total_outstanding == null || $total_outstanding == ""){
																	?>
																	<td align="right"><b>0.00</b></td>
																	<?php 
																}
																else{
																	?>
																	<td align="right"><b><?php echo number_format($total_outstanding,2,".",",")?></b></td>
																	<?php 
																	$totalOutstandingAll += $total_outstanding;
																}
															}
															?>
															<td align="right"><b><?php echo number_format($totalOutstandingAll,2,".",",")?></b></td>
														</tr>
														
														<?php
												}
									}
										
										?>
										</TR>
                        </div>
                    </div>
                </div>
            </div>
            <!-- END MAIN CONTENT-->
            <!-- END PAGE CONTAINER-->
        </div>
		<?PHP //include "footer.php";?>
    </div>
    <?PHP include "footerLink.php";?>
</body>

</html>
<!-- end document-->
<script>
var checkboxes = document.getElementsByClassName("checkbox"); //checkbox items
for (var i = 0; i < checkboxes.length; i++) {
    checkboxes[i].addEventListener('change', function(e){ //".checkbox" change 
        //uncheck "select all", if one of the listed checkbox item is unchecked
        if(this.checked[i] == false){
            checkboxes.checked = false;
        }
        //check "select all" if all checkbox items are checked
        if(document.querySelectorAll('.checkbox:checked').length == checkboxes.length){
            checkboxes[i].checked = true;
        }
    });
}


function statementby_change(){
	
	var statementby = document.getElementById("statementby").value;
	
	//alert(statementby);
	 if(statementby != ""){
		//document.getElementById("statement_option").style.display ='block';
		if(statementby == "statement_year"){
			document.getElementById('statement_ByYear').style.display ='block';
			document.getElementById('statement_ByMonthYear').style.display ='none';
			document.getElementById('statement_ByChooseDate').style.display ='none';
		}
		else if(statementby == "statement_monthyear"){
			document.getElementById('statement_ByYear').style.display ='none';
			document.getElementById('statement_ByMonthYear').style.display ='block';
			document.getElementById('statement_ByChooseDate').style.display ='none';
		}
		else if(statementby == "statement_ChooseDate"){
			document.getElementById('statement_ByYear').style.display ='none';
			document.getElementById('statement_ByMonthYear').style.display ='none';
			document.getElementById('statement_ByChooseDate').style.display ='block';
		} 
	} 
	
	
	
}
</script>