<?php
				
 " report_type: " .$report_type = $_GET["report_type"];
 " statementBy: " .$statementby = $_GET["statementby"];
  " date_statementOptionView: " .$date_statementOptionView = $_GET["date_statementOptionView"];
  " date_statementOptionView_from: " .$date_statementOptionView_from = $_GET["date_statementOptionView_from"];
  " date_statementOptionView_to: " .$date_statementOptionView_to = $_GET["date_statementOptionView_to"];
  " block: " .$block = $_GET["block"];
 " tenant_status: " .$tenant_status = $_GET["tenant_status"];
	$reportPrintExport == $_GET["print"];
	/* if($date_statementOptionView == "" || ($date_statementOptionView_from == "" && $date_statementOptionView_to == "")){
		
	} */
 if($date_statementOptionView != "" || ($date_statementOptionView_from != "" && $date_statementOptionView_to != "")){
		 $dateTo;
		if($date_statementOptionView_from != "" && $date_statementOptionView_to != ""){
			$dateFrom = $date_statementOptionView_from;
			$dateTo = $date_statementOptionView_to;
			$_SESSION["dateFrom"] = $dateFrom;
			 "DATE TO: " .$_SESSION["dateTo"] = $dateTo;
		}
	}
	if($report_type == 'Rental'){
		$keterangan = " BAYARAN SEWA ";
			
		}
	else if($report_type == 'Water'){
		$keterangan = " BAYARAN BILL AIR ";
			
	}


include("bill_types.php");

				
				 " reportPaymentPrintExport :: ".$reportPaymentPrintExport = $_GET["print"];
				//$block = $_GET["block"];
				$sqlListBlock = "";
				$listBlock = "";
				$listBlock_plain = "";
				if($reportPaymentPrintExport == "no"){
					$statement_year = $_GET["statement_year"];
					$statement_monthyear = $_GET["statement_monthyear"];
					$dateFrom = $_GET["dateFrom"];
					$dateTo = $_GET["dateTo"];
					$statement_ChooseDate = $_GET["statement_ChooseDate"];
					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];
							}
						}
					
					}
				}
				else if($reportPaymentPrintExport == "yes"){
					 "STATEMENT YEAR :: ".$statement_year = $date_statementOptionView;
					$statement_monthyear = $date_statementOptionView;
					$dateFrom = $date_statementOptionView_from;
					$dateTo = $date_statementOptionView_to;
					//block - start
					$blockListing = "";
					if(strpos($block,",")){
					   "BLOCK EXPLODE: " .$blockListing = explode(",",$block);
					   "count block explode: " .$countBlock = count($blockListing);
					}
					else {
						$blockListing = $block;
						 "COUNT BLOCK: " .$countBlock = strlen($blockListing);
					}
					$sqlListBlock = "";
					$listBlock = "";
					  if(!empty($blockListing)) 
					  {
						for($i=0; $i < $countBlock; $i++)
						{
							($block[$i] . ",");
							if($listBlock == "")
								$listBlock = "'" . $blockListing[$i] . "'";
							else {
								$listBlock .= ",'" . $blockListing[$i]. "'";
							}
						}
					  }
				}
				
				 "LIST BLOCK: ".$listBlock;
				IF($listBlock != ""){
					$sqlListBlock = " and properties.block in (" . $listBlock .") ";
				}
				$_SESSION["block"] = $block;
				
				//block - end






				
				$sqlTenantStatus = "";
				if($tenant_status != ""){
					$sqlTenantStatus = " and tenants.status = '" .$tenant_status."' ";
				}
				
				if($statementby == 'statement_year'){
						//all payments start from 1st Jan - 31 Dec
						$displayYear = "display:block";
						$displayMonthYear = "display:none";
						$displayDateFromTo = "display:none";
						$displayChooseDate = "display:none";
						$displayCheckBox = "display:block";
						
						//$statement_year = $_GET["statement_year"];
						$_SESSION['statement_year']=$statement_year;
						$selected_cutOffPayment = $_GET["cutOffPayment"];
						$_SESSION["cutOffPayment"] = $selected_cutOffPayment;
						$cutOffDate = date("Y-m-d",strtotime($statement_year . '-12-31')); //tarikh hujung tahun
						$firstYearDate =  date("Y-m-d",strtotime($statement_year . '-01-01')); //tarikh awal tahun
						$statement_year_cutOFF = date('Y-m-d', strtotime($cutOffDate. ' + 1 days'));
						IF($selected_cutOffPayment == true){
							
							$cond_selectedCutOffPayment = " and YEAR(payments.created_at) < '$statement_year_cutOFF'";
						}
						
						$year_selected = date("Y",strtotime($firstYearDate));
						$cond_monthYear_BF = " and year(bills.issue_date) < '$statement_year' ";
						$cond_date_BF = " and payments.created_at < '$firstYearDate' ";
						$sqlDate = "  YEAR(payments.created_at)  = '$year_selected' ";
						$sqlDateDeposit = "  YEAR(deposits.created_at)  = '$year_selected' ";
						$infoTarikhTunggakan = " TAHUN $statement_year";
						$sqlDateADV = " and bills.issue_date > '$cutOffDate' ";
						$sqlDatePayments = " and payments.created_at >= '$firstYearDate' and payments.created_at < '$statement_year_cutOFF' ";
						$sqlDateDepositPayments = "  deposit_payments.created_at >= '$firstYearDate' and deposit_payments.created_at < '$statement_year_cutOFF' ";
						$sqlDateDepositPaymentsOthers = "  deposit_payment_other_receipts.created_at >= '$firstYearDate' and deposit_payment_other_receipts.created_at < '$statement_year_cutOFF' ";
						
						
						$date_statementOptionView = $statement_year;
				}
				else if($statementby == 'statement_monthyear'){
						//statement by bills issue_date = monthyear selected
						$displayYear = "display:none";
						$displayMonthYear = "display:block";
						$displayDateFromTo = "display:none";
						$displayChooseDate = "display:none";
						$displayCheckBox = "display:block";
						//$statement_monthyear = $_GET["statement_monthyear"];
						$_SESSION['statement_monthyear']=$statement_monthyear;
						$selected_cutOffPayment = $_GET["cutOffPayment"];
						$_SESSION["cutOffPayment"] = $selected_cutOffPayment;
						$payment_monthYear = date("m/Y", strtotime($statement_monthyear));
						
						$firstDay = "";
						$lastDay = "";
						
						$firstDay = date("Y-m-01", strtotime($statement_monthyear));
						$lastDay = date("Y-m-t", strtotime($statement_monthyear));
						
						$cond_monthYear_BF = " and issue_date < '$firstDay ' ";
						$cond_date_BF = " and payments.created_at < '$firstDay' ";
						
						$cond_selectedCutOffPayment = " and payments.created_at between '$firstDay' and '$lastDay' ";
					
						$date_monthYear = date("Y-m-01",strtotime($statement_monthyear));
						$lastdate = $date_monthYear . "-01";
						$lastdate = date("Y-m-t", strtotime($lastdate));			
						$cutOffDate = $lastdate;
						
						$date_monthYearDeposit = date("M Y",strtotime($lastdate));	
						
						$sqlDate = "  bills.issue_date  = '$date_monthYear' ";
						$sqlDateDeposit = " deposits.created_at >= '$firstDay' and deposits.created_at < '$lastdate'";
						$infoTarikhTunggakan = " BULAN $payment_monthYear ";
						
						$sqlDateDepositPayments = "  deposit_payments.created_at between '$firstDay' and '$lastDay' ";
						$sqlDateDepositPaymentsOthers = "   deposit_payment_other_receipts.created_at between '$firstDay' and '$lastDay' ";
						
						
						$date_statementOptionView = $statement_monthyear;
				}
				else if($statementby == 'statement_date' ){
						//statement by payments on selected date (dateFrom - dateTo)
						$displayYear = "display:none";
						$displayMonthYear = "display:none";
						$displayDateFromTo = "display:block";
						$displayChooseDate = "display:none";
						$displayCheckBox = "display:block";
						//$statement_date = $_GET["statement_date"];
						//$dateFrom = date("Y-m-d",strtotime($_GET["dateFrom"]));
						//$dateTo =  date("Y-m-d",strtotime($_GET["dateTo"]));
						$_SESSION['statement_date']=$statement_date;
						$_SESSION['dateFrom']=$dateFrom;
						$_SESSION['dateTo']=$dateTo;
						$date_cutOFF = date('Y-m-d', strtotime($dateTo. ' + 1 days'));
						$dateOpening = date('Y-m-d', strtotime($dateFrom. ' - 1 days'));
						
						//]compare month year
						$monthYearFrom = date('m-Y',strtotime($dateFrom));
						$monthYearTo = date('m-Y',strtotime($dateTo));
						if($monthFrom == $monthTo){
							$bulanTerimaan = date('M Y', strtotime($dateFrom));
						}
						else{
							$bulanTerimaan = date("d/m/Y",strtotime($dateFrom)) . " SEHINGGA " . date("d/m/Y",strtotime($dateTo));
						}
						
						//compare year
						$tahunTerimaan = date('Y', strtotime($dateFrom));
						
						$firstDay = $dateFrom;
						$lastDay = $date_cutOFF;
						
						$cond_selectedCutOffPayment = " and payments.created_at between '$firstDay' and '$lastDay' ";
						$sqlDate = " (payments.created_at  >= '$dateFrom'  and payments.created_at < '$date_cutOFF') ";
						$sqlDateDeposit = " (deposits.created_at  >= '$dateFrom'  and deposits.created_at < '$date_cutOFF') ";
						$infoTarikhTunggakan = " DARI ".date("d/m/Y",strtotime($dateFrom)) . " SEHINGGA " . date("d/m/Y",strtotime($dateTo));
						
						$sqlDateDepositPayments = "  deposit_payments.created_at >= '$dateFrom' and deposit_payments.created_at < '$date_cutOFF' ";
						$sqlDateDepositPaymentsOthers = "   deposit_payment_other_receipts.created_at >= '$dateFrom' and deposit_payment_other_receipts.created_at < '$date_cutOFF'  ";
						
						$date_statementOptionView_from = $dateFrom;
						$date_statementOptionView_to = $dateTo;
						
						
					
						
						
				}
				else if($statementby == 'statement_ChooseDate'){
						//statement by payments on selected date
						$displayYear = "display:none";
						$displayMonthYear = "display:none";
						$displayDateFromTo = "display:none";
						$displayChooseDate = "display:block";
						$displayCheckBox = "display:block";
						//$chooseDate = "";
						if($_GET["statement_ChooseDate"] != "" || $_GET["statement_ChooseDate"] != null){
							$chooseDate = date("Y-m-d",strtotime($_GET["statement_ChooseDate"]));
						}else{
							$chooseDate = $date_statementOptionView;
						}
						$_SESSION['chooseDate']=$chooseDate;
						$selected_cutOffPayment = $_GET["cutOffPayment"];
						$_SESSION["cutOffPayment"] = $selected_cutOffPayment;
						$date_cutOFF = date('Y-m-d', strtotime($chooseDate));	
						
						$cond_selectedCutOffPayment = " and payments.created_at = '$date_cutOFF'";
						$sqlDate = " payments.created_at like '%$chooseDate%' ";
						$sqlDateDeposit = " deposits.created_at like '%$chooseDate%' ";
						$cutOffDate = $chooseDate;
						$infoTarikhTunggakan = " SEHINGGA ". date("d/m/Y",strtotime($chooseDate));
						
						$sqlDateDepositPayments = "  deposit_payments.created_at like '%$chooseDate%' ";
						$sqlDateDepositPaymentsOthers = "   deposit_payment_other_receipts.created_at like '%$chooseDate%' ";
						
						$date_statementOptionView = $chooseDate;
				}
				else if($statementby == ""){
					$cutOffDate = date('Y-m-d', strtotime($chooseDate. ' + 1 days'));
					$cond_selectedCutOffPayment = " and payments.created_at < '$cutOffDate'";
					//$sqlDate = " payments.created_at = '$cutOffDate' ";
					$infoTarikhTunggakan = " TARIKH:  " . DATE("d/m/Y");
				}
				
				
				if($report_type == "Deposit"){
					//check table deposits (penyewa lama)
					   $sqlDeposit_oldRecord = "select deposits.*,tenants.id as tenant_id, tenants.tenant_name, properties.unit_no  from deposits inner join 
														tenants on deposits.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 locations.id =". $_SESSION['location_id'] ."
														 $sqlTenantStatus  $sqlListBlock 
														and ($sqlDateDeposit) order by deposits.created_at ASC
														";
					$runDeposit_oldRecord = mysqli_query($link, $sqlDeposit_oldRecord);
						if($runDeposit_oldRecord === FALSE){
						die(mysqli_error("SQL DIE! Error for SQL: ".$sqlDeposit_oldRecord));
					}
					$countRowOldRecord = mysqli_num_rows($runDeposit_oldRecord);
					
					//check table deposit_bills + deposit_payments (penyewa baru)
					  $sqlDeposit_newRecord = "SELECT deposit_payments.id as deposit_id,
																deposit_payments.receipt_no as receipt_no, 
																deposit_payments.two_months_amount_paid as two_months_amount_paid,
																deposit_bills.two_month_rental_amount as two_month_rental_amount_bill, 
																deposit_payments.utility_amount_paid as utility_amount_paid, 
																deposit_bills.utility_amount as utility_amount_bill, 
																deposit_payments.created_at as created_at, 
																deposit_bills.id as deposit_bill_id, 
																deposit_bills.deposit_bill_no as deposit_bill_no, 
																deposit_bills.payment_status, tenants.tenant_name, properties.unit_no, tenants.id as tenant_id
																FROM deposit_payments
																inner join deposit_bills on deposit_payments.deposit_bill_id = deposit_bills.id
                                                                inner join tenants on deposit_bills.tenant_id = tenants.id
                                                                inner join properties on tenants.property_id = properties.id
                                                                inner join locations on properties.location_id = locations.id
																where  ( $sqlDateDepositPayments ) and locations.id = ". $_SESSION['location_id'] ."  $sqlTenantStatus  $sqlListBlock order by deposit_payments.created_at ASC";
					$runDeposit_newRecord = mysqli_query($link, $sqlDeposit_newRecord);
						if($runDeposit_newRecord === FALSE){
						die(mysqli_error("SQL DIE! Error for SQL: ".$sqlDeposit_newRecord));
					}
					$countDeposit_newRecord = mysqli_num_rows($runDeposit_newRecord);
																
					  $sqlDepositPaymentOthers ="SELECT deposit_payment_other_receipts.id as deposit_id,
																deposit_payment_other_receipts.deposit_other_payment_no as receipt_no, 
																deposit_payment_other_receipts.yp_paid as yp_paid,
																deposit_bill_other_receipts.yp_charges_amount as yp_charges_amount, 
																deposit_payment_other_receipts.sst_paid as sst_paid, 
																deposit_bill_other_receipts.sst_charges_amount as sst_charges_amount, 
																deposit_payment_other_receipts.stamping_paid as stamping_paid, 
																deposit_bill_other_receipts.stamping_charges_amount as stamping_charges_amount, 
																deposit_payment_other_receipts.created_at as created_at, 
																deposit_bill_other_receipts.id as deposit_bill_id, 
																deposit_bill_other_receipts.deposit_other_bill_no as deposit_other_bill_no, 
																deposit_bill_other_receipts.payment_status, tenants.tenant_name, properties.unit_no, tenants.id as tenant_id
																FROM deposit_payment_other_receipts
																inner join deposit_bill_other_receipts on deposit_payment_other_receipts.deposit_bill_other_id = deposit_bill_other_receipts.id
																inner join tenants on deposit_bill_other_receipts.tenant_id = tenants.id
                                                                inner join properties on tenants.property_id = properties.id
                                                                inner join locations on properties.location_id = locations.id
																where ( $sqlDateDepositPaymentsOthers ) and locations.id = ". $_SESSION['location_id'] ."  $sqlTenantStatus  $sqlListBlock order by deposit_payment_other_receipts.created_at ASC";
																
					$runDepositPaymentOthers = mysqli_query($link, $sqlDepositPaymentOthers);
						if($runDepositPaymentOthers === FALSE){
						die(mysqli_error("SQL DIE! Error for SQL: ".$sqlDepositPaymentOthers));
					}
					$countRowPaymentOthers = mysqli_num_rows($runDepositPaymentOthers);
				}
				else if($report_type == "Rental" || $report_type == "Water" ){
					 
					if($statementby == "statement_year"){
						 
						 $sqlReport = "select count(tenants.id) as countTenant,  properties.unit_no, 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
										inner join projects on locations.project_id = projects.id
										where bills.bill_type_id in ($bill_types_id,$bill_type_Outstanding) and locations.id = ". $_SESSION['location_id'] ."  and  YEAR(bills.issue_date) = $statement_year $sqlTenantStatus  $sqlListBlock
										  group by properties.id order by properties.id ASC";
					}
					else if($statementby == "statement_ChooseDate"){
						  $sqlReport = "select tenants.id as tenant_id
									from payments 
									inner join bills on payments.bill_id = bills.id
									inner join bill_types on bills.bill_type_id = bill_types.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
									inner join projects on locations.project_id = projects.id
									inner join users on payments.created_by =  users.id
									where bills.bill_type_id in ($bill_types_id,$bill_type_Outstanding)  and locations.id = ". $_SESSION['location_id'] ."  $sqlTenantStatus  $sqlListBlock
									 and ($sqlDate) GROUP BY tenants.id
									
									";
					}
					else if($statementby == "statement_monthyear"){
						 $sqlReport = "select sum(payments.amount_paid) as total_amount_paid, count(payments.id) as count_payment,  tenants.id as tenant_id, tenants.tenant_name, properties.unit_no, bills.bill_amount, bills.issue_date, bills.id as bill_id
									from payments 
									inner join bills on payments.bill_id = bills.id
									inner join bill_types on bills.bill_type_id = bill_types.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
									inner join projects on locations.project_id = projects.id
									inner join users on payments.created_by =  users.id
									where bills.bill_type_id in ($bill_types_id,$bill_type_Outstanding)  and locations.id = ". $_SESSION['location_id'] ."   $sqlTenantStatus  $sqlListBlock
									  and ($sqlDate) group by tenants.id order by properties.id ASC";
					}
					else if($statementby = "statement_date"){
						
						  	$sqlReport = "select count(tenants.id) as countPayment,tenants.id as tenant_id, tenants.tenant_name,  bills.bill_amount, sum(payments.amount_paid) as totalPaidByTenant,  properties.unit_no, properties.id as property_id 
										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
										inner join projects on locations.project_id = projects.id 
										where bills.bill_type_id in ($bill_types_id,$bill_type_Outstanding)  and locations.id = ". $_SESSION['location_id'] ."   $sqlTenantStatus  $sqlListBlock
										  and ($sqlDate) group by tenants.id order by properties.id ASC";
					}
					else{
					     $sqlReport = "select payments.*, tenants.id as tenant_id, tenants.tenant_name, properties.unit_no, tenants.rental_rate, bills.issue_date,  users.name, locations.location_name,projects.project_name, bill_types.bill_type_name, bills.bill_type_id
									from payments 
									inner join bills on payments.bill_id = bills.id
									inner join bill_types on bills.bill_type_id = bill_types.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
									inner join projects on locations.project_id = projects.id
									inner join users on payments.created_by =  users.id 
									where bills.bill_type_id in ($bill_types_id,$bill_type_Outstanding)  and locations.id = ". $_SESSION['location_id'] ."  $sqlTenantStatus  $sqlListBlock
									 and ($sqlDate) order by payments.created_at ASC
									
									";
					}				
					$runReport= mysqli_query($link, $sqlReport);
					if($runReport=== FALSE){
						die(mysqli_error($link));
					}
					
				}
				
				?>