<?php
// Initialize the session
 $username = $_SESSION['username'];
 $query = "SELECT id FROM users WHERE username LIKE '%$name%' ";
$result = mysqli_query($link, $query);
$rowid = mysqli_fetch_array($result);
$assignid = $rowid['id'];
/* $staff_IDfromDashboard = $_GET["staff_ID"];
$cond_staffID = "";
if($staff_IDfromDashboard != "" || $staff_IDfromDashboard != null)
	$cond_staffID = " and tenants.created_by = '$staff_IDfromDashboard'";
 */
if($_SESSION["projectLocation"] != "")
	$projectLocation = $_SESSION["projectLocation"];
else{
	$_SESSION["projectLocation"] = $projectLocation;
}

//$projectLocation = $_GET["projectLocation"];
if($projectLocation == ""){
	echo "<center><font size='6' color='red'>Please Select Location Above.</font></center>";
}
else{	
include "splitLocationName_project.php";


$cond_TenantInfo = "";
$cond_Location = "";
$cond_and = "";
$cond_combine = "";
$cond_propertiesUnit = "";
$cond_tenantName = "";
if($searchTenant == "" && $locationName != ""){
	$cond_Location = " and  locations.location_name = '$locationName'  ";
}
if($searchTenant != "" && $locationName == ""){
	$cond_TenantInfo = " and (properties.unit_no = '".$searchTenant."'" ." or 
				tenants.tenant_name LIKE '%".$searchTenant."%'" ." or 
				tenants.ic_no LIKE '%".$searchTenant."%'" ." ) ";
}
if($searchTenant != "" && $locationName != ""){
	$cond_Location = " and  locations.location_name = '$locationName'  ";
	$cond_TenantInfo = " and  (properties.unit_no = '".$searchTenant."'" ." or 
				tenants.tenant_name LIKE '%".$searchTenant."%'" ." or 
				tenants.ic_no LIKE '%".$searchTenant."%'" ." ) ";
	
}
if($searchTenant == "" && $locationName == ""){
	$cond_Location = " ";
	$cond_TenantInfo = " ";
	$cond_and = " ";
}
if($_SESSION["properties_unitno"] != ""){
	$cond_propertiesUnit = " and properties.unit_no = '".$_SESSION["properties_unitno"]."' ";
}
if($_SESSION["tenant_name_listing"] != ""){
	$cond_tenantName = " and tenants.tenant_name = '".$_SESSION["tenant_name_listing"]."' ";
}


$cond_combine = $cond_TenantInfo . $cond_and . $cond_Location . $cond_propertiesUnit . $cond_tenantName;

if($_SESSION["username"] != "admin"){
			
	 $sqlActiveTenant = "select tenants.*,billtype_info.bill_code,
						properties.unit_no,
						locations.location_name, 
						projects.project_name from tenants 
						inner join tenant_rental_rate on tenants.id = tenant_rental_rate.tenant_id
						inner join billtype_info on tenant_rental_rate.tenant_bill_type_id = billtype_info.id
						inner join properties on tenants.property_id = properties.id 
						inner join locations on properties.location_id = locations.id 
						inner join projects on locations.project_id = projects.id
						where locations.id = $location_id and  (lower(tenants.status) = 'enable')
						$cond_combine ";
}
else if($_SESSION["username"] == "admin"){
		
	    $sqlActiveTenant = "select tenants.*,billtype_info.bill_code,
						properties.unit_no,
						locations.location_name, 
						projects.project_name from tenants  
						inner join tenant_rental_rate on tenants.id = tenant_rental_rate.tenant_id
						inner join billtype_info on tenant_rental_rate.tenant_bill_type_id = billtype_info.id
						inner join properties on tenants.property_id = properties.id 
						inner join locations on properties.location_id = locations.id 
						inner join projects on locations.project_id = projects.id
						where locations.id = $location_id and  (lower(tenants.status) = 'enable' )
						$cond_combine ";
}

$grabactive = mysqli_query($link, $sqlActiveTenant);//or die ("Cannot execute program");

if($grabactive === FALSE){
	die(mysqli_error($link));
}

if($_SESSION["username"] != "admin"){
	
		$sqlDeactiveTenant = "select tenants.*, billtype_info.bill_code,
						properties.unit_no,
						locations.location_name, 
						projects.project_name from tenants  
						inner join tenant_rental_rate on tenants.id = tenant_rental_rate.tenant_id
						inner join billtype_info on tenant_rental_rate.tenant_bill_type_id = billtype_info.id
						inner join properties on tenants.property_id = properties.id
						inner join locations on properties.location_id = locations.id 
						inner join projects on locations.project_id = projects.id
						where locations.id = $location_id and  (lower(tenants.status)= 'disable' )
						$cond_combine ";
}
else if($_SESSION["username"] == "admin"){
			
	$sqlDeactiveTenant = "select tenants.*,billtype_info.bill_code,
						properties.unit_no,
						locations.location_name, 
						projects.project_name from tenants  
						inner join tenant_rental_rate on tenants.id = tenant_rental_rate.tenant_id
						inner join billtype_info on tenant_rental_rate.tenant_bill_type_id = billtype_info.id
						inner join properties on tenants.property_id = properties.id
						inner join locations on properties.location_id = locations.id 
						inner join projects on locations.project_id = projects.id
						where locations.id = $location_id and  (lower(tenants.status)= 'disable' )
						$cond_combine ";
}
$grabdeactive = mysqli_query($link, $sqlDeactiveTenant);//or die ("Cannot execute program");

if($grabdeactive === FALSE){
	die(mysqli_error($link));
}

}
if(isset($_POST["reset"])){
	$_SESSION["properties_unitno"] = "";
	$_SESSION["tenant_name_listing"] = "";
}
?>
	
					
                        
                            <div class="table-responsive">
								<table class="table table-bordered table-hover table-sm" id="dataTable" width="100%" cellspacing="0" style="font-size: 12px">
                                    <thead>
										<tr>
											<td colspan='12' align='center' bgcolor="#7cc8fc"><b>ACTIVE TENANTS</b></td>
										</tr>
                                        <tr bgcolor="#ebeced" align="center" valign="middle">
                                            <td><b>No.</b></td>
											<td><b>Name</b></td>
											<td><b>Unit</b></td>
											<td><b>Location</b></td>
											<td><b>Kemasukan</b></td>
											<td><b>Tarikh Masuk</b></td>
											<td><b>Tarikh Keluar</b></td>
											<td><b>Rental (RM)</b></td>
											<td><b>Current Rental Due (RM)</b></td>
											<td><b>Amount Due (RM)</b></td>
											<td><b>Status</b></td>
											<td><b>Action</b></td>
                                        </tr>
                                    </thead>
                                   
									<?php
										$no = 1;
										$totalAmountDue=0;
										$totalCurrentRental=0;
										$totalRentalAMount_active = 0;
										$totalRentalAMount_deactive = 0;
										$totalAmountDue_deactive = 0;
										$tenant_name = "";
										while ($row = mysqli_fetch_array($grabactive))
										{	
											$current_month = date("m");
											$current_year = date("Y");
											$tenant_id = $row["id"];
											$name = $row["tenant_name"];
											///$company = $row["company"];
											$unit = $row["unit_no"];
											$location = $row["location_name"];
											$tenant_status = $row["status"];
											$type_reg = $row["type"];
											$bill_code = $row["bill_code"];

											$firstDay = date("Y-m-d");
											
											$due = "SELECT SUM(bills.amount_due) AS dueamount FROM `payments` inner join bills on payments.bill_id = bills.id WHERE bills.tenant_id = '$tenant_id' and issue_date < '$firstDay' and bill_type_id = 1";
											 $sqlOutstanding = "SELECT total_outstanding FROM ( 
																(select bills.bill_amount- sum( payments.amount_paid ) as total_outstanding , bills.*,payments.created_at as payment_date, properties.unit_no, tenants.tenant_name from payments 
																inner join bills on payments.bill_id = bills.id 
																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.tenant_id =$tenant_id and bills.issue_date <= '$firstDay' and 
																bills.bill_type_id = 1 group by bills.issue_date having total_outstanding <> 0 ) 
																UNION 
																(select sum(bills.bill_amount) as total_outstanding, bills.* , bills.created_at as payment_date, properties.unit_no, 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
																where bills.issue_date < '$firstDay' and 
																bills.id not in (select payments.bill_id from payments inner join bills on payments.bill_id = bills.id WHERE bills.tenant_id = $tenant_id ) and 
																tenant_id = $tenant_id and bills.bill_type_id = 1 group by issue_date having total_outstanding <> 0 and bills.issue_date < CURRENT_DATE) ) results group by issue_date ORDER BY issue_date ASC";
											
											
											
											$fine = 0.00;
											$duetotal = mysqli_query($link, $sqlOutstanding);
											
											if($duetotal === FALSE){
                                            	die(mysqli_error($link));
                                            }
											
											
											while($totalOutstanding = mysqli_fetch_array($duetotal)){
												$fine += $totalOutstanding["total_outstanding"];
											}
												
											//$finetotal = $fine['dueamount'];
											//$fine = number_format($fine,2)	;
											$rent = "SELECT * FROM bills  WHERE tenant_id = '$tenant_id'";
											$rentmonth = mysqli_query($link, $rent);
											
											if($rentmonth === FALSE){
                                            	die(mysqli_error($link));
                                            }
											
											
											$fix = mysqli_fetch_assoc($rentmonth);
												
											$amount = $fix['bill_amount'];	
											$rental_month =  $fix['issue_date'];	
											
											$sqlCurrentRental = "SELECT * FROM `bills` WHERE MONTH(`issue_date`) = ".$current_month." AND YEAR(`issue_date`) = ".$current_year." and tenant_id = '$tenant_id'";
											$runCurrentRental = mysqli_query($link, $sqlCurrentRental);
											if($runCurrentRental === FALSE){
                                            	die(mysqli_error($link));
                                            }
											
											
											$resultCurrentRental = mysqli_fetch_assoc($runCurrentRental);
											$bill_bal = $resultCurrentRental['amount_due'];
											
											//check rental rate for tenant based on deposit table
											 $sqlDepositTable = "select rental_rate from tenants where id = $tenant_id LIMIT 1";
											$runDepositTable = mysqli_query($link, $sqlDepositTable);
											if($runDepositTable === FALSE){
                                            	die(mysqli_error($link));
                                            }
											$resultDepositTable = mysqli_fetch_assoc($runDepositTable);
											$rental_rate = $resultDepositTable['rental_rate'];
											
											if(strtoupper($type_reg) == 'INDIVIDU'){
												$tenant_name = $name;
											}
											else if(strtoupper($type_reg) == 'SYARIKAT'){
												$tenant_name = $company;
											}
											
											$sqlRentalRate = "SELECT * FROM `tenant_rental_rate` WHERE `tenant_id` = $tenant_id";
											$runRentalRate = mysqli_query($link, $sqlRentalRate);
											if($runRentalRate === FALSE){
                                            	die(mysqli_error($link));
                                            }
											$resultRentalRate = mysqli_fetch_assoc($runRentalRate);
											$start_date = $resultRentalRate["start_date"];
											$end_date = $resultRentalRate["end_date"];
											
											$tenant_status_show = "Active";
											if($tenant_status == 'disable' || $tenant_status == ""){
												$tenant_status_show = "Deactive";
											}
											
											if($start_date == "0000-00-00 00:00:00" || $end_date == "0000-00-00 00:00:00" || $start_date == null || $end_date == null){
												$start_date = "-";
												$end_date = "-";
											}
											else{
												$start_date = date("d/m/Y",strtotime($start_date));
												$end_date =  date("d/m/Y",strtotime($end_date));
											}
										?>
                                    <tbody>
                                       <tr>
											<td width='3%'><?php echo $no?></td>
											<td width='20%'><a href="viewTenantDashboard.php?tenant_id=<?php echo $tenant_id?>&menu=Tenant&submenu=Dashboard"><?php echo strtoupper($name); ?></a></td>
											<td width='7%'><?php echo $unit;?></td>	
											<td><?php echo $location;?></td>	
											<td><?php echo $bill_code;?></td>	
											<td><?php echo $start_date;?></td>	
											<td><?php echo $end_date;?></td>	
											<td width='7%' align="right"><?php echo number_format($rental_rate,2,".",",");?></td>
											<td width='7%' align="right"><?php echo number_format($bill_bal,2,".",",");?></td>
											<td width='7%' align="right"><font color="red"><b><?php echo number_format($fine,2,".",",");?></b></font></td>
											<td align="center"><?php echo $tenant_status_show?></td>
											<td >
												<button type="button"  onclick="editTenant('<?php echo $tenant_id?>')" class="btn btn-outline-warning btn-sm">Edit Tenant</button>
												<button type="button"  onclick="viewStatement('<?php echo $tenant_id?>')" class="btn btn-outline-success btn-sm">View Statement</button>
												<?php 
												if(strtolower($tenant_status) == "enable"){
												?>
												
												<button type="button" style="float:right" class="btn btn-outline-danger btn-sm" onclick="Javascript:newPopup('deactivateTenant.php?tenant_id=<?php echo $tenant_id;?>&tenant_name=<?php echo $name?>&unit_no=<?php echo $unit?>')">
													Deactivate Tenant
												</button> 
												
												<!--
												<button type="button"  onclick="confirmFunction('<?php echo $name; ?>','<?php echo $unit?>','<?php echo $tenant_id?>','<?php echo $_SESSION["projectLocation"]?>');" class="btn btn-outline-danger btn-sm">Deactivate Tenant</button>-->
												<?php
												}
												?>
											</td>
										</tr>
										<?php 
										$no ++; 
										$totalAmountDue+=$fine;
										$totalCurrentRental+=$bill_bal;
										$totalRentalAMount_active+=$amount;
									}
									?>
										<tr bgcolor="#ebeced">
											<td colspan="7" align="right"><b>TOTAL (RM)</b></td>
											<td align="right"><font color="red"><b><?php echo number_format($totalRentalAMount_active,2,".",",");?></b></font></td>
											<td align="right"><font color="red"><b><?php echo number_format($totalCurrentRental,2,".",",");?></b></font></td>
											<td align="right"><font color="red"><b><?php echo number_format($totalAmountDue,2,".",",");?></b></font></td>
											<td colspan="2"></td>
										</tr>
                                    </tbody>
                                </table>
								<table class="table table-bordered" id="dataTable" width="100%" cellspacing="0" style="font-size: 12px">
                                    <thead>
										<tr>
											<th colspan='12' align='center' bgcolor="#7cc8fc">DEACTIVE TENANTS <?php //echo $sqlActiveTenant?></b></td>
										</tr>
                                        <tr bgcolor="#ebeced">
                                            <td width='3%' ><strong>No.</strong></td>
											<td width='20%'><strong>Name</strong></td>
											<td width='7%'><strong>Unit</strong></td>
											<td width='15%'><strong>Location</strong></td>
											<td><b>Kemasukan</b></td>
											<td><b>Tarikh Masuk</b></td>
											<td><b>Tarikh Keluar</b></td>
											<td width='7%'><strong>Rental (RM)</strong></td>
											<td width='7%'><strong>Amount Due (RM)</strong></td>
											<td ><strong>Status</strong></td>
											<td  ><strong>Action</strong></td>
                                        </tr>
                                    </thead>
                                   
									<?php
										$no = 1;
										$tenant_name = "";
											while ($row = mysqli_fetch_array($grabdeactive))
											{
											
											$tenant_id = $row["id"];
											$name = $row["tenant_name"];
											$company = $row["company"];
											$unit = $row["unit_no"];
											$location = $row["location_name"];
											$tenant_status = $row["status"];
											$type_reg = $row["type"];
											$bill_code = $row["bill_code"];
											
											$due = "SELECT SUM(bills.amount_due) AS dueamount FROM bills
														inner join tenants on bills.tenant_id = tenants.id
														WHERE bills.tenant_id = '$tenant_id' and lower(tenants.status) = 'disable'";
											$duetotal = mysqli_query($link, $due);
											if($duetotal === FALSE){
                                            	die(mysqli_error($link));
                                            }
											$fine = mysqli_fetch_assoc($duetotal);
												
											$finetotal = $fine['dueamount'];
												
											$rent = "SELECT * FROM bills  WHERE tenant_id = '$tenant_id' ";
											$rentmonth = mysqli_query($link, $rent);
											if($rentmonth === FALSE){
                                            	die(mysqli_error($link));
                                            }
											$fix = mysqli_fetch_assoc($rentmonth);
												
											$amount = $fix['bill_amount'];
											
											//check rental rate for tenant based on deposit table
												$sqlDepositTable = "select rental_rate from tenants where id = $tenant_id LIMIT 1";
												$runDepositTable = mysqli_query($link, $sqlDepositTable);
												if($runDepositTable === FALSE){
													die(mysqli_error($link));
												}
												$resultDepositTable = mysqli_fetch_assoc($runDepositTable);
												$rental_rate = $resultDepositTable['rental_rate'];
											
											if(strtoupper($type_reg) == 'INDIVIDU'){
												$tenant_name = $name;
											}
											else if(strtoupper($type_reg) == 'SYARIKAT'){
												$tenant_name = $company;
											}
											$sqlRentalRate = "SELECT * FROM `tenant_rental_rate` WHERE `tenant_id` = $tenant_id";
											$runRentalRate = mysqli_query($link, $sqlRentalRate);
											if($runRentalRate === FALSE){
                                            	die(mysqli_error($link));
                                            }
											$resultRentalRate = mysqli_fetch_assoc($runRentalRate);
											$start_date = $resultRentalRate["start_date"];
											$end_date = $resultRentalRate["end_date"];
											
											
											$tenant_status_show = "Active";
											if($tenant_status == 'disable' || $tenant_status == ""){
												$tenant_status_show = "Deactive";
											}
											
											
											if($start_date == "0000-00-00 00:00:00" ||  $start_date == null ){
												$start_date = "-";
											}
											else if($start_date != "0000-00-00 00:00:00" ||  $start_date != null){
												$start_date = date("d/m/Y",strtotime($start_date));
											}
											
											if($end_date == "0000-00-00 00:00:00" ||  $end_date == null){
												$end_date = "-";
											}
											else if($end_date != "0000-00-00 00:00:00" ||  $end_date != null){
												$end_date =  date("d/m/Y",strtotime($end_date));
											}
										?>
                                    <tbody>
                                       <tr>
											<td width='3%'><?php echo $no?></td>
											<td width='20%'><a href="viewTenantDashboard.php?tenant_id=<?php echo $tenant_id?>&menu=Tenant&submenu=Dashboard"><?php echo strtoupper($name); ?></a></td>
											<!--<td width='30%'><?php echo strtoupper($name)?></td>-->
											<td width='7%'><?php echo $unit;?></td>	
											<td><?php echo $location;?></td>
											<td><?php echo $bill_code;?></td>
											<td><?php echo $start_date;?></td>	
											<td><?php echo $end_date;?></td>	
																						
											<td width='7%' align="right"><?php echo number_format($rental_rate,2,".",",");?></td>
											<td width='7%' align="right"><font color="red"><b><?php echo number_format($finetotal,2,".",",");?></b></font></td>
											<td align="center"><?php echo $tenant_status_show?></td>
											<td >
												<button type="button"  onclick="editTenant('<?php echo $tenant_id?>')" class="btn btn-outline-warning btn-sm">Edit Tenant</button>
												<button type="button"  onclick="viewStatement('<?php echo $tenant_id?>')" class="btn btn-outline-success btn-sm">View Statement</button>
												<button type="button"  onclick="listPayment('<?php echo $tenant_id?>')" class="btn btn-outline-success btn-sm">View Details Payment</button>	
											</td>
										</tr>
										<?php 
											$no ++; 
											$totalAmountDue_deactive+=$finetotal;
											$totalRentalAMount_deactive+=$amount;
											
										}
										?>
										<tr bgcolor="#ebeced">
											<td colspan="7" align="right"><b>TOTAL (RM)</b></td>
											<td align="right"><font color="red"><b><?php echo number_format($totalRentalAMount_deactive,2,".",",");?></b></font></td>
											<td align="right"><font color="red"><b><?php echo number_format($totalAmountDue_deactive,2,".",",");?></b></font></td>
											<td colspan="2"></td>
										</tr>
                                    </tbody>
                                </table>
                            </div>




















