<?php 
set_time_limit(0);
error_reporting(0);
if (substr_count($_SERVER['HTTP_ACCEPT_ENCODING'], 'gzip')) ob_start("ob_gzhandler"); else ob_start();
session_start();
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Asia/Manila');
if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser'); 
require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php'; 
$objReader = PHPExcel_IOFactory::createReader('Excel5');
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
include_once('../config/minho.php');
include_once('../config/autoloadClass.php');
$showCanCtr = "";
$showPastCtr = "";
$showLackCtr = "";
$autoload		=	new autoloadClass();
$mecon			=	new condbClass();
$main			=	array();
$main2			=	array();
$main3			=	array();
$main4			=	array(); 

$monthFrom		=	isset($_GET['month_from']) ? strip_tags($_GET['month_from']) 	: "";
$monthTo		=	isset($_GET['month_to']) ? strip_tags($_GET['month_to']) 		: "";
//$filterSearch	=	isset($_GET['filter']) ? strip_tags($_GET['filter']) 			: "";

$filterSearch	=	isset($_GET['filter']) ? str_replace("'"," ",$_GET['filter']) : "";
//$filterSearch	=	isset($_GET['filter']) ? str_replace("'","''",$_GET['filter']) : str_replace("'"," ","LA BELLE'S");


$isfilterdate = isset($_GET['isfilterdate']) ? strip_tags($_GET['isfilterdate']) : "yes";
$fileName		=	"report_summary_report_of_buyers.xls";
$objPHPExcel	=	$objReader->load("templates/report_summary_report_of_buyers.xls"); 

$marr			=	array();
$carr			=	array();
$darr			=	array();
 
		//LOOP PER SHEET
		$prevAssetName = NULL;
		$objPHPExcel->removeSheetByIndex(0);
		$objPHPExcel->removeSheetByIndex(1); 
		$objPHPExcel->removeSheetByIndex(0);
 
 
$lackArr = array();
$cancelArr = array();
$pastArr = array();
$pastArr	= getPastBroker($monthFrom,$monthTo,$filterSearch,$isfilterdate);
$lackArr	= getLackBroker($monthFrom,$monthTo,$filterSearch,$isfilterdate);
$cancelArr	= getCancelBroker($monthFrom,$monthTo,$filterSearch,$isfilterdate);
$ctrPast	= count($pastArr);
$ctrLack	= count($lackArr);
$ctrCancel	= count($cancelArr);
$marr = getUniqueBrokers($lackArr,$pastArr);
 
 foreach($marr as $mIndex => $mVal){
	$mVal = str_replace("'","\\\'",$mVal);
	if ($mVal !== $prevAssetName) {
				$newsheet = $objPHPExcel->createSheet();
				$newsheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
				$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LEGAL);

				$objPHPExcel->getActiveSheet()
    ->getPageMargins()->setTop(0.25);
$objPHPExcel->getActiveSheet()
    ->getPageMargins()->setRight(0.25);
$objPHPExcel->getActiveSheet()
    ->getPageMargins()->setLeft(0.25);
$objPHPExcel->getActiveSheet()
    ->getPageMargins()->setBottom(0.25);
	              
				 $mVal2 = stripslashes(stripslashes(str_replace("\\'"," ",$mVal)));
				 $mVal3 = stripslashes(stripslashes($mVal));
				 $mVal = str_replace("\\'","\\\\''",$mVal);
				$newsheet->setTitle($mVal2);
				$prevAssetName = $mVal; 
				$modelFilter = $mVal;  
				$projectName = $_SESSION['castalia_project'];
				$newsheet->setCellValue('A1',$projectName); 
				$newsheet->setCellValue('A2',$mVal3);
				$newsheet->setCellValue('A3',"SUMMARY REPORT OF BUYER/S WITH DEFICIENCIES ");
				$newsheet->setCellValue('A4',"AS OF ");
				
				$styleArray = array('font' => array('bold' => true));
						$newsheet->getStyle('A1:A4')->applyFromArray($styleArray);
						unset($styleArray);   
				//CANCEL ACCOUNTS
				$showCancelArr = array();
				 
				$b = 0;
				
				$lackCtr = 7;
				//$lackCtr = ($showPastCtr!=0) ? $c + 5 : ( ($showCanCtr!=0) ? 8 : 5 );
				
				
				
				//$lackCtr = ($showPastCtr!=0) ? $c + 5 : ( ($showCanCtr!=0) ? 8 : 5 );
				//LACKING
				$showLackArr = array();
				if($ctrLack!=0){
					//$brok = str_replace("'","\\\'",$mVal);
					$showLackArr = showLackDetails($mVal,$monthFrom,$monthTo,$filterSearch,$isfilterdate); 
					$showLackCtr = count($showLackArr);
					if($showLackCtr!=0){
						$base = $lackCtr;
						$baseAdd = $base+1;
						$newsheet->setCellValue('A'.$base,"BUYERS WITH LACKING DOCUMENTS");
						$newsheet->setCellValue('A'.$baseAdd,"#");
						$newsheet->setCellValue('B'.$baseAdd,"RESERVATION DATE");
						$newsheet->setCellValue('C'.$baseAdd,"BLOCK");
						$newsheet->setCellValue('D'.$baseAdd,"LOT");
						$newsheet->setCellValue('E'.$baseAdd,"FULLNAME");
						//$newsheet->setCellValue('F'.$baseAdd,"BROKER");
						//$newsheet->setCellValue('F'.$baseAdd,"HOUSE CONSTRUCTION");
						$newsheet->setCellValue('F'.$baseAdd,"LACKING REQUIREMENTS"); 
						$newsheet->mergeCells('F7:I7');
						
$objPHPExcel->getActiveSheet()->getStyle('A'.$baseAdd)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);									  
$objPHPExcel->getActiveSheet()->getStyle('B'.$baseAdd)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);									  
$objPHPExcel->getActiveSheet()->getStyle('C'.$baseAdd)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);									  
$objPHPExcel->getActiveSheet()->getStyle('D'.$baseAdd)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);									  
$objPHPExcel->getActiveSheet()->getStyle('E'.$baseAdd)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);									  
//$objPHPExcel->getActiveSheet()->getStyle('F'.$baseAdd)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);									  
$objPHPExcel->getActiveSheet()->getStyle('F'.$baseAdd)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setWrapText(true);									  

						
						
						$f = $baseAdd+1;
						$newsheet->getColumnDimension('A')->setWidth(5); 
						$newsheet->getColumnDimension('B')->setWidth(18); 
						$newsheet->getColumnDimension('C')->setWidth(5); 
						$newsheet->getColumnDimension('D')->setWidth(5); 
						//$newsheet->getColumnDimension('F')->setWidth(30); 
						//$newsheet->getColumnDimension('F')->setWidth(30); 
						$newsheet->getColumnDimension('E')->setWidth(45);
						$newsheet->mergeCells('F'.$baseAdd.':I'.$baseAdd);
						$newsheet->getColumnDimension('F')->setWidth(40);
						$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),'font' => array('bold' => true));
						$newsheet->getStyle('A'.$base.':I'.$baseAdd.'')->applyFromArray($styleArray);
						unset($styleArray);   
						for($d=0; $d<count($showLackArr); $d++){ 
							$newsheet->setCellValue('A'.$f,$showLackArr[$d][0]);
							$newsheet->setCellValue('B'.$f,$showLackArr[$d][1]);
							$newsheet->setCellValue('C'.$f,$showLackArr[$d][2]);
							$newsheet->setCellValue('D'.$f,$showLackArr[$d][3]);
							$newsheet->setCellValue('E'.$f,$showLackArr[$d][4]);
							//$newsheet->setCellValue('F'.$f,strtoupper($showLackArr[$d][5]));
							//$newsheet->setCellValue('F'.$f,$showLackArr[$d][6]); 
							$newsheet->setCellValue('F'.$f,$showLackArr[$d][6]); 
						$newsheet->mergeCells('F'.$f.':I'.$f);
							$objPHPExcel->getActiveSheet()->getStyle('F1:F'.$objPHPExcel->getActiveSheet()->getHighestRow())->getAlignment()->setWrapText(true); 
							
							$objPHPExcel->getActiveSheet()->getStyle('B'.$f)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
							
							$objPHPExcel->getActiveSheet()->getStyle('B'.$f)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);	
							$objPHPExcel->getActiveSheet()->getStyle('C'.$f)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);	
							$objPHPExcel->getActiveSheet()->getStyle('D'.$f)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);	
							$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),'font' => array('bold' => false));
							$newsheet->getStyle('A'.$f.':I'.$f.'')->applyFromArray($styleArray);
							$f++;
						}
						unset($styleArray);   
					} 
				}  
				
				$showLackCtr = count($showLackArr);
				$pastCtr = $showLackCtr + 10; 
				//PAST DUES
				$showPastArr = array();
				if($ctrPast!=0){					
					$showPastArr = showPastDetails($mVal,$monthFrom,$monthTo,$filterSearch,$isfilterdate); 
					$showPastCtr = count($showPastArr);
					if($showPastCtr!=0){
						$base = $pastCtr;
						$baseAdd = $base+1;
						$newsheet->setCellValue('A'.$base,"BUYER/S WITH PAST DUE ACCOUNTS");
						$newsheet->setCellValue('A'.$baseAdd,"#");
						$newsheet->setCellValue('B'.$baseAdd,"FULLNAME");
						$newsheet->setCellValue('C'.$baseAdd,"BLOCK");
						$newsheet->setCellValue('D'.$baseAdd,"LOT");
						$newsheet->setCellValue('E'.$baseAdd,"RESERVATION DATE");
						$newsheet->setCellValue('F'.$baseAdd,"MONTH/S PAST DUE");
						$newsheet->setCellValue('G'.$baseAdd,"REASON");
						$newsheet->setCellValue('H'.$baseAdd,"AMOUNT");
						$newsheet->setCellValue('I'.$baseAdd,"RETURNED CHECK DATE");
						$newsheet->setCellValue('J'.$baseAdd,"REMARKS");
						
						$objPHPExcel->getActiveSheet()->getStyle('A'.$baseAdd)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
						$objPHPExcel->getActiveSheet()->getStyle('B'.$baseAdd)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
						$objPHPExcel->getActiveSheet()->getStyle('C'.$baseAdd)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
						$objPHPExcel->getActiveSheet()->getStyle('D'.$baseAdd)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
						$objPHPExcel->getActiveSheet()->getStyle('E'.$baseAdd)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
						$objPHPExcel->getActiveSheet()->getStyle('F'.$baseAdd)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
						$objPHPExcel->getActiveSheet()->getStyle('G'.$baseAdd)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
						$objPHPExcel->getActiveSheet()->getStyle('H'.$baseAdd)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
						$objPHPExcel->getActiveSheet()->getStyle('I'.$baseAdd)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
						$objPHPExcel->getActiveSheet()->getStyle('J'.$baseAdd)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
						$objPHPExcel->getActiveSheet()->getStyle('E'.$baseAdd)->getNumberFormat()->setFormatCode('mm/dd/yyyy');
						$c = $baseAdd+1;
						$newsheet->getColumnDimension('A')->setWidth(5); 
						$newsheet->getColumnDimension('B')->setWidth(18); 
						$newsheet->getColumnDimension('F')->setWidth(18);
						$newsheet->getColumnDimension('G')->setWidth(40);
						$newsheet->getColumnDimension('E')->setWidth(32);
						 
						$newsheet->getColumnDimension('H')->setWidth(20); 
						$newsheet->getColumnDimension('I')->setWidth(20); 
						$newsheet->getColumnDimension('J')->setWidth(30); 
						$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),'font' => array('bold' => true));
						$newsheet->getStyle('A'.$base.':J'.$baseAdd.'')->applyFromArray($styleArray);
						unset($styleArray);   
						for($d=0; $d<count($showPastArr); $d++){ 
							$newsheet->setCellValue('A'.$c,$showPastArr[$d][0]);
							$newsheet->setCellValue('B'.$c,$showPastArr[$d][1]);
							$newsheet->setCellValue('C'.$c,$showPastArr[$d][2]);
							$newsheet->setCellValue('D'.$c,$showPastArr[$d][3]);
							$newsheet->setCellValue('E'.$c,$showPastArr[$d][4]);
							$newsheet->setCellValue('F'.$c,strtoupper($showPastArr[$d][5]));
							$newsheet->setCellValue('G'.$c,$showPastArr[$d][6]);
							$newsheet->setCellValue('H'.$c,number_format($showPastArr[$d][7],2));
							$newsheet->setCellValue('I'.$c,$showPastArr[$d][8]);
							$newsheet->setCellValue('J'.$c,$showPastArr[$d][9]);
								$newsheet->getColumnDimension('H')->setWidth(15); 
								$newsheet->getColumnDimension('J')->setWidth(15); 
							$objPHPExcel->getActiveSheet()->getStyle('A'.$c)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
							$objPHPExcel->getActiveSheet()->getStyle('B'.$c)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
							$objPHPExcel->getActiveSheet()->getStyle('C'.$c)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
							$objPHPExcel->getActiveSheet()->getStyle('D'.$c)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
							$objPHPExcel->getActiveSheet()->getStyle('E'.$c)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
							$objPHPExcel->getActiveSheet()->getStyle('F'.$c)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
							$objPHPExcel->getActiveSheet()->getStyle('G'.$c)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
							$objPHPExcel->getActiveSheet()->getStyle('H'.$c)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
							$objPHPExcel->getActiveSheet()->getStyle('I'.$c)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
							$objPHPExcel->getActiveSheet()->getStyle('J'.$c)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
							
							$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),'font' => array('bold' => false));
							$newsheet->getStyle('A'.$c.':J'.$c.'')->applyFromArray($styleArray);
							$c++;
						}
					} 
				}
	}			
 } 
 $currentUser = "";
//$currentUser 	= $_SESSION['castalia_lastname'].", ".$_SESSION['castalia_firstname']; 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
$from = "A1"; // or any value
$to = "B5"; // or any value
$objPHPExcel->getActiveSheet()->getStyle("$from:$to")->getFont()->setBold( true );('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$fileName.'"');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
//header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
//header ('Pragma: public'); // HTTP/1.0

header("Cache-Control: no-cache, no-store, must-revalidate"); // HTTP 1.1.
header("Pragma: no-cache"); // HTTP 1.0.
header("Expires: 0"); // Proxies.


$objWriter->save('php://output'); 


function getLackBroker($monthFrom,$monthTo,$filterSearch,$isfilterdate){
	$autoload		=	new autoloadClass(); 
	$mecon			=	new condbClass(); 
   
 /* $sql = "SELECT r.Id as Id, r.Block as Block, r.Lot as Lot, r.Model as Model, r.Project as Project, r.ReservationDate as ReservationDate, r.Phase as Phase, r.PricelistDate as PricelistDate, CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName, r.Broker as Broker, ck.BuyerStandardReqDetails as BuyerStandardReqDetails, ck.AdditionalReqDetails as AdditionalReqDetails, r.EmployeeType as EmployeeType, r.EmployeeTypeBorrower1 as EmployeeTypeBorrower1, r.EmployeeTypeBorrower2 as EmployeeTypeBorrower2, r.EmployeeTypeBorrower3 as EmployeeTypeBorrower3, ck.CoBorrower1StandardReqDetails as CoBorrower1StandardReqDetails, ck.CoBorrower1AdditionalReqDetails as CoBorrower1AdditionalReqDetails, ck.CoBorrower2StandardReqDetails as CoBorrower2StandardReqDetails, ck.CoBorrower2AdditionalReqDetails as CoBorrower2AdditionalReqDetails, ck.CoBorrower3StandardReqDetails as CoBorrower3StandardReqDetails, ck.CoBorrower3AdditionalReqDetails as CoBorrower3AdditionalReqDetails, r.NoCoBorrower as NoCoBorrower, m.HouseConstruction as HouseConstruction FROM reservation r LEFT JOIN checklist ck ON ck.ReservationId=r.Id LEFT JOIN monitoring_folders m ON m.ReservationId=r.Id WHERE  (r.ReservationStatus!='cancelled' AND r.ReservationStatus!='transferred') AND r.Status='active'  AND (
(ck.BuyerStandardReqDetails NOT LIKE '%[\"birth_certificate\",\"proof_of_billing\",\"barangay_clearance\",\"certificate_from_house_owner\",\"photocopy_of_two\",\"photocopy_spouse_id\",\"four_photo\",\"esav\",\"msvs\",\"postdated_checks\",\"twenty_four_checks\",\"tinno\"]%' OR ck.BuyerStandardReqDetails IS NULL OR ((ck.AdditionalReqDetails NOT LIKE '%[\"notarized_certificate\",\"latest_pay_slip\"]%' OR ck.AdditionalReqDetails IS NULL)
 AND (ck.AdditionalReqDetails NOT LIKE '%[\"photocopy_of_passport\",\"contract_of_employment\",\"certificate_of_overseas\",\"duly_notarized_spa\",\"photocopy_valid_id_spa\",\"salary_certificate\"]%' OR ck.AdditionalReqDetails IS NULL)
 AND (ck.AdditionalReqDetails NOT LIKE '%[\"latest_itr\",\"business_permit\",\"dti_registration\"]%' 
  OR ck.AdditionalReqDetails IS NULL) )) 
  AND (ck.CoBorrower1StandardReqDetails NOT LIKE '%[\"birth_certificate\",\"proof_of_billing\",\"barangay_clearance\",\"certificate_from_house_owner\",\"photocopy_of_two\",\"photocopy_spouse_id\",\"four_photo\",\"esav\",\"msvs\",\"postdated_checks\",\"twenty_four_checks\",\"tinno\"]%' OR ck.CoBorrower1StandardReqDetails IS NULL OR ((ck.CoBorrower1AdditionalReqDetails NOT LIKE '%[\"notarized_certificate\",\"latest_pay_slip\"]%' OR ck.CoBorrower1AdditionalReqDetails IS NULL)
 AND (ck.CoBorrower1AdditionalReqDetails NOT LIKE  '%[\"photocopy_of_passport\",\"contract_of_employment\",\"certificate_of_overseas\",\"duly_notarized_spa\",\"photocopy_valid_id_spa\",\"salary_certificate\"]%' OR ck.CoBorrower1AdditionalReqDetails IS NULL)
 AND (ck.CoBorrower1AdditionalReqDetails NOT LIKE '%[\"latest_itr\",\"business_permit\",\"dti_registration\"]%' 
  OR ck.CoBorrower1AdditionalReqDetails IS NULL) )) AND (ck.CoBorrower2StandardReqDetails NOT LIKE '%[\"birth_certificate\",\"proof_of_billing\",\"barangay_clearance\",\"certificate_from_house_owner\",\"photocopy_of_two\",\"photocopy_spouse_id\",\"four_photo\",\"esav\",\"msvs\",\"postdated_checks\",\"twenty_four_checks\",\"tinno\"]%' OR ck.CoBorrower2StandardReqDetails IS NULL OR ((ck.CoBorrower2AdditionalReqDetails NOT LIKE '%[\"notarized_certificate\",\"latest_pay_slip\"]%' OR ck.CoBorrower2AdditionalReqDetails IS NULL)
 AND (ck.CoBorrower2AdditionalReqDetails NOT LIKE  '%[\"photocopy_of_passport\",\"contract_of_employment\",\"certificate_of_overseas\",\"duly_notarized_spa\",\"photocopy_valid_id_spa\",\"salary_certificate\"]%' OR ck.CoBorrower2AdditionalReqDetails IS NULL)
 AND (ck.CoBorrower2AdditionalReqDetails NOT LIKE '%[\"latest_itr\",\"business_permit\",\"dti_registration\"]%' 
  OR ck.CoBorrower2AdditionalReqDetails IS NULL) )) AND (ck.CoBorrower3StandardReqDetails NOT LIKE '%[\"birth_certificate\",\"proof_of_billing\",\"barangay_clearance\",\"certificate_from_house_owner\",\"photocopy_of_two\",\"photocopy_spouse_id\",\"four_photo\",\"esav\",\"msvs\",\"postdated_checks\",\"twenty_four_checks\",\"tinno\"]%' OR ck.CoBorrower3StandardReqDetails IS NULL OR ((ck.CoBorrower3AdditionalReqDetails NOT LIKE '%[\"notarized_certificate\",\"latest_pay_slip\"]%' OR ck.CoBorrower3AdditionalReqDetails IS NULL)
 AND (ck.CoBorrower3AdditionalReqDetails NOT LIKE  '%[\"photocopy_of_passport\",\"contract_of_employment\",\"certificate_of_overseas\",\"duly_notarized_spa\",\"photocopy_valid_id_spa\",\"salary_certificate\"]%' OR ck.CoBorrower3AdditionalReqDetails IS NULL)
 AND (ck.CoBorrower3AdditionalReqDetails NOT LIKE '%[\"latest_itr\",\"business_permit\",\"dti_registration\"]%' 
  OR ck.CoBorrower3AdditionalReqDetails IS NULL) )) ) ";*/



  $sql 	= "SELECT r.ReservationStatus as ReservationStatus, r.Id as Id, r.Block as Block, r.Lot as Lot, r.Model as Model, r.Project as Project, r.ReservationDate as ReservationDate, r.Phase as Phase, r.PricelistDate as PricelistDate, CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName, r.Broker as Broker, ck.BuyerStandardReqDetails as BuyerStandardReqDetails, ck.AdditionalReqDetails as AdditionalReqDetails, r.EmployeeType as EmployeeType, r.EmployeeTypeBorrower1 as EmployeeTypeBorrower1, r.EmployeeTypeBorrower2 as EmployeeTypeBorrower2, r.EmployeeTypeBorrower3 as EmployeeTypeBorrower3, ck.CoBorrower1StandardReqDetails as CoBorrower1StandardReqDetails, ck.CoBorrower1AdditionalReqDetails as CoBorrower1AdditionalReqDetails, ck.CoBorrower2StandardReqDetails as CoBorrower2StandardReqDetails, ck.CoBorrower2AdditionalReqDetails as CoBorrower2AdditionalReqDetails, ck.CoBorrower3StandardReqDetails as CoBorrower3StandardReqDetails, ck.CoBorrower3AdditionalReqDetails as CoBorrower3AdditionalReqDetails, r.NoCoBorrower as NoCoBorrower, m.HouseConstruction as HouseConstruction, bsfp.TakeOut as TakeOut FROM reservation r LEFT JOIN checklist ck ON ck.ReservationId=r.Id LEFT JOIN monitoring_folders m ON m.ReservationId=r.Id LEFT JOIN buyer_status_folder_pagibig bsfp ON bsfp.ReservationId=r.Id WHERE (bsfp.TakeOut='1970-01-01' OR bsfp.TakeOut IS NULL) 
				AND (r.ReservationStatus!='transferred' AND  r.ReservationStatus!='cancelled') 
				AND r.Status='active' 
				AND (
						(ck.BuyerStandardReqDetails NOT LIKE '%[\"birth_certificate\",\"proof_of_billing\",\"barangay_clearance\",\"photocopy_of_two\",\"photocopy_spouse_id\",\"four_photo\",\"mdf\",\"esav\",\"msvs\",\"tinno\",\"postdated_checks\",\"twenty_four_checks\",\"signed_loan_documents\",\"attended_homeowners_orientation_and_signed_deed_of_restriction\"]%' 
					  		OR ck.BuyerStandardReqDetails IS NULL 
					  		OR (
					  			(ck.AdditionalReqDetails NOT LIKE '%[\"notarized_certificate\",\"latest_pay_slip\",\"income_tax_return\"]%' OR ck.AdditionalReqDetails IS NULL)

 								AND (ck.AdditionalReqDetails NOT LIKE '%[\"employment_contract\",\"employment_contract\",\"salary_certificate\",\"certificate_of_employment\",\"certificate_of_overseas\",\"owwa_membership_certificate\",\"resident_card\",\"photocopy_passport\",\"notarized_spa\",\"photocopy_valid_id_spa\"]%' OR ck.AdditionalReqDetails IS NULL)

 								AND (ck.AdditionalReqDetails NOT LIKE '%[\"income_tax_return_audited_fs\",\"certificate_of_commission\",\"commission_vouchers\",\"photocopy_bank_statement\",\"dti_registration\",\"mayors_permit\",\"lease_contract\",\"sketch_business_location\"]%' 
  									OR ck.AdditionalReqDetails IS NULL)

  								AND (ck.AdditionalReqDetails NOT LIKE '%[\"employment_contract\",\"certificate_of_employment\",\"work_permit\",\"immigrant_visa\",\"photocopy_of_passport\",\"duly_notarized_spa\",\"photocopy_valid_id_spa\"]%' 
  									OR ck.AdditionalReqDetails IS NULL)

  								AND (ck.AdditionalReqDetails NOT LIKE '%[\"bank_statement\",\"remittances\",\"certificate_of_employment\",\"picture_of_work_station\",\"sketch_map_home_address\",\"google_map\"]%' 
  									OR ck.AdditionalReqDetails IS NULL)	   
   							)
   						) 


  						AND 
  						(ck.CoBorrower1StandardReqDetails NOT LIKE '%[\"birth_certificate\",\"proof_of_billing\",\"barangay_clearance\",\"photocopy_of_two\",\"photocopy_spouse_id\",\"four_photo\",\"mdf\",\"esav\",\"msvs\",\"tinno\"]%' 
  							OR ck.CoBorrower1StandardReqDetails IS NULL 
  							OR (
  									(ck.CoBorrower1AdditionalReqDetails NOT LIKE '%[\"notarized_certificate\",\"latest_pay_slip\",\"income_tax_return\"]%' OR ck.CoBorrower1AdditionalReqDetails IS NULL)

 									AND (ck.CoBorrower1AdditionalReqDetails NOT LIKE  '%[\"employment_contract\",\"employment_contract\",\"salary_certificate\",\"certificate_of_employment\",\"certificate_of_overseas\",\"owwa_membership_certificate\",\"resident_card\",\"photocopy_passport\",\"notarized_spa\",\"photocopy_valid_id_spa\"]%' OR ck.CoBorrower1AdditionalReqDetails IS NULL)

 									AND (ck.CoBorrower1AdditionalReqDetails NOT LIKE '%[\"income_tax_return_audited_fs\",\"certificate_of_commission\",\"commission_vouchers\",\"photocopy_bank_statement\",\"dti_registration\",\"mayors_permit\",\"lease_contract\",\"sketch_business_location\"]%' OR ck.CoBorrower1AdditionalReqDetails IS NULL) 

 									AND (ck.CoBorrower1AdditionalReqDetails NOT LIKE '%[\"employment_contract\",\"certificate_of_employment\",\"work_permit\",\"immigrant_visa\",\"photocopy_of_passport\",\"duly_notarized_spa\",\"photocopy_valid_id_spa\"]%' OR ck.CoBorrower1AdditionalReqDetails IS NULL)

  									AND (ck.CoBorrower1AdditionalReqDetails NOT LIKE '%[\"bank_statement\",\"remittances\",\"certificate_of_employment\",\"picture_of_work_station\",\"sketch_map_home_address\",\"google_map\"]%' OR ck.CoBorrower1AdditionalReqDetails IS NULL)	

 								)
 						) 


  						AND 
  						(ck.CoBorrower2StandardReqDetails NOT LIKE '%[\"birth_certificate\",\"proof_of_billing\",\"barangay_clearance\",\"photocopy_of_two\",\"photocopy_spouse_id\",\"four_photo\",\"mdf\",\"esav\",\"msvs\",\"tinno\"]%' 
  							OR ck.CoBorrower2StandardReqDetails IS NULL 
  							OR (
  									(ck.CoBorrower2AdditionalReqDetails NOT LIKE '%[\"notarized_certificate\",\"latest_pay_slip\",\"income_tax_return\"]%' OR ck.CoBorrower2AdditionalReqDetails IS NULL)

 									AND (ck.CoBorrower2AdditionalReqDetails NOT LIKE  '%[\"employment_contract\",\"employment_contract\",\"salary_certificate\",\"certificate_of_employment\",\"certificate_of_overseas\",\"owwa_membership_certificate\",\"resident_card\",\"photocopy_passport\",\"notarized_spa\",\"photocopy_valid_id_spa\"]%' OR ck.CoBorrower2AdditionalReqDetails IS NULL)

 									AND (ck.CoBorrower2AdditionalReqDetails NOT LIKE '%[\"income_tax_return_audited_fs\",\"certificate_of_commission\",\"commission_vouchers\",\"photocopy_bank_statement\",\"dti_registration\",\"mayors_permit\",\"lease_contract\",\"sketch_business_location\"]%' OR ck.CoBorrower2AdditionalReqDetails IS NULL) 

 									AND (ck.CoBorrower2AdditionalReqDetails NOT LIKE '%[\"employment_contract\",\"certificate_of_employment\",\"work_permit\",\"immigrant_visa\",\"photocopy_of_passport\",\"duly_notarized_spa\",\"photocopy_valid_id_spa\"]%' OR ck.CoBorrower2AdditionalReqDetails IS NULL)

  									AND (ck.CoBorrower2AdditionalReqDetails NOT LIKE '%[\"bank_statement\",\"remittances\",\"certificate_of_employment\",\"picture_of_work_station\",\"sketch_map_home_address\",\"google_map\"]%' OR ck.CoBorrower2AdditionalReqDetails IS NULL)	
 								)
 						) 

  						AND 
  						(ck.CoBorrower3StandardReqDetails NOT LIKE '%[\"birth_certificate\",\"proof_of_billing\",\"barangay_clearance\",\"photocopy_of_two\",\"photocopy_spouse_id\",\"four_photo\",\"mdf\",\"esav\",\"msvs\",\"tinno\"]%' 
  							OR ck.CoBorrower3StandardReqDetails IS NULL 
  							OR (
  									(ck.CoBorrower3AdditionalReqDetails NOT LIKE '%[\"notarized_certificate\",\"latest_pay_slip\",\"income_tax_return\"]%' OR ck.CoBorrower3AdditionalReqDetails IS NULL)

 									AND (ck.CoBorrower3AdditionalReqDetails NOT LIKE  '%[\"employment_contract\",\"employment_contract\",\"salary_certificate\",\"certificate_of_employment\",\"certificate_of_overseas\",\"owwa_membership_certificate\",\"resident_card\",\"photocopy_passport\",\"notarized_spa\",\"photocopy_valid_id_spa\"]%' OR ck.CoBorrower3AdditionalReqDetails IS NULL)

 									AND (ck.CoBorrower3AdditionalReqDetails NOT LIKE '%[\"income_tax_return_audited_fs\",\"certificate_of_commission\",\"commission_vouchers\",\"photocopy_bank_statement\",\"dti_registration\",\"mayors_permit\",\"lease_contract\",\"sketch_business_location\"]%' OR ck.CoBorrower3AdditionalReqDetails IS NULL) 

 									AND (ck.CoBorrower3AdditionalReqDetails NOT LIKE '%[\"employment_contract\",\"certificate_of_employment\",\"work_permit\",\"immigrant_visa\",\"photocopy_of_passport\",\"duly_notarized_spa\",\"photocopy_valid_id_spa\"]%' OR ck.CoBorrower3AdditionalReqDetails IS NULL)

  									AND (ck.CoBorrower3AdditionalReqDetails NOT LIKE '%[\"bank_statement\",\"remittances\",\"certificate_of_employment\",\"picture_of_work_station\",\"sketch_map_home_address\",\"google_map\"]%' OR ck.CoBorrower3AdditionalReqDetails IS NULL)	
 								)
 						) 

					) ";

 
	if(!empty($filterSearch)) { 
		$arr = explode(" ",$filterSearch);  
		foreach($arr as $ind){
			$ind = str_replace(',','',$ind);
			/*$sql.=" AND (r.Broker LIKE '%".$ind."%'  ";
			$sql.=" OR r.Block LIKE '%".$ind."%' ";
			$sql.=" OR r.Lot LIKE '%".$ind."%' )";*/
			
			$sql.=" AND (r.Block LIKE '%".$ind."%' ";
			$sql.=" OR r.Lot LIKE '%".$ind."%' ";
			$sql.=" OR m.HouseConstruction LIKE '%".$ind."%' ";
			$sql.=" OR r.Project LIKE '%".$ind."%' ";
			$sql.=" OR r.Model LIKE '%".$ind."%' "; 
			$sql.=" OR r.Broker LIKE '%".htmlentities($ind)."%' ";   
			$sql.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' ";
			$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";
			$sql.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%d-%b-%Y')) LIKE '%".$ind."%' ) ";   
		}
	}

	if($isfilterdate=="yes"){
			if(!empty($monthFrom) && !empty($monthTo)){
				$sql .= " AND r.ReservationDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
			}else if( !empty($monthFrom)){
				$sql .= " AND r.ReservationDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
			}else if( !empty($monthTo)){
				$sql .= " AND r.ReservationDate <= ('".date('Y-m-d', strtotime($monthTo))."') ";
			} 
	} 
	
	$sql .= ' GROUP BY (r.Broker) ';
	
	
	$mecon->query($sql);
	$result = $mecon->resultset(); 
	$lackArr	=	array(); 			
	for($a=0; $a<count($result); $a++){ 
		$lackArr[] = $result[$a]['Broker']; 
	}
	return $lackArr; 
}

function getPastBroker($monthFrom,$monthTo,$filterSearch,$isfilterdate){
	$autoload		=	new autoloadClass(); 
	$mecon			=	new condbClass();
	$sql = "SELECT r.Broker as Broker, r.Id, r.ReservationDate FROM reservation r LEFT JOIN buyer_cheque_monitoring bcm ON bcm.ReservationId=r.Id  WHERE  r.Status='active' AND bcm.ReturnRemarks IS NOT NULL AND (bcm.ChequeStatus!='Hold' OR bcm.ChequeStatus!='No Check' OR bcm.ChequeStatus!='Returned') ";

	 
	if(!empty($filterSearch)) { 
		$arr = explode(" ",$filterSearch);  
		foreach($arr as $ind){
			$ind = str_replace(',','',$ind);
			$sql.=" AND (r.Broker LIKE '%".$ind."%' ";
			$sql.=" OR r.Block LIKE '%".$ind."%' ";
			$sql.=" OR r.Lot LIKE '%".$ind."%' )";
		}
	}
	
	if($isfilterdate=="yes"){
			if(!empty($monthFrom) && !empty($monthTo)){
				$sql .= " AND r.ReservationDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
			}else if( !empty($monthFrom)){
				$sql .= " AND r.ReservationDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
			}else if( !empty($monthTo)){
				$sql .= " AND r.ReservationDate <= ('".date('Y-m-d', strtotime($monthTo))."') ";
			} 
	} 
	
	$sql .= " GROUP BY (r.Broker)";
	$mecon->query($sql);
	$result2 = $mecon->resultset(); 
	$pastArr	=	array(); 			
	for($a=0; $a<count($result2); $a++){ 
		$pastArr[] = $result2[$a]['Broker']; 
	}
	return $pastArr;
} 

function getCancelBroker($monthFrom, $monthTo, $filterSearch,$isfilterdate){
	$autoload		=	new autoloadClass(); 
	$mecon			=	new condbClass();	
	$sql = "SELECT r.Broker as Broker, r.Id as Id, r.ReservationDate, m.HouseConstruction as HouseConstruction FROM reservation r LEFT JOIN monitoring_folders m ON m.ReservationId=r.Id WHERE  r.Status='active' AND r.ReservationStatus='cancelled' ";
	  
	if(!empty($filterSearch)) { 
		$arr = explode(" ",$filterSearch);  
		foreach($arr as $ind){
			$ind = str_replace(',','',$ind);
			$sql.=" AND (r.Broker LIKE '%".$ind."%' ";
			$sql.=" OR m.HouseConstruction LIKE '%".$ind."%' ";
			$sql.=" OR r.Block LIKE '%".$ind."%' ";
			$sql.=" OR r.Lot LIKE '%".$ind."%' )";
		}
	}
	
	if($isfilterdate=="yes"){
			if(!empty($monthFrom) && !empty($monthTo)){
				$sql .= " AND r.ReservationDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
			}else if( !empty($monthFrom)){
				$sql .= " AND r.ReservationDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
			}else if( !empty($monthTo)){
				$sql .= " AND r.ReservationDate <= ('".date('Y-m-d', strtotime($monthTo))."') ";
			}
	}
 
	 $sql .= " GROUP BY (r.Broker) ";
	$mecon->query($sql);
	$result3 = $mecon->resultset(); 
	$cancelArr	=	array(); 			
	for($a=0; $a<count($result3); $a++){ 
		$cancelArr[] = $result3[$a]['Broker']; 
	} 
	return $cancelArr;
}

function showCancelDetails($broker,$monthFrom,$monthTo,$filterSearch,$isfilterdate){
	$autoload		=	new autoloadClass(); 
	$mecon			=	new condbClass();	
	$formClass			=	new formClass();	
	$sql = "SELECT r.Id as Id, r.Block as Block, r.Lot as Lot, r.Model as Model, r.Project as Project, r.ReservationDate as ReservationDate, r.PricelistDate as PricelistDate, CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName, r.Broker as Broker, r.Phase as Phase, m.HouseConstruction as HouseConstruction  FROM reservation r LEFT JOIN monitoring_folders m ON m.ReservationId=r.Id WHERE  r.Status='active' AND r.ReservationStatus='cancelled' AND r.Broker LIKE '%".$broker."%' ";
	 
 
	
	if(!empty($filterSearch)) { 
		$arr = explode(" ",$filterSearch);  
		foreach($arr as $ind){
			$ind = str_replace(',','',$ind);
			$sql.=" AND (r.Model LIKE '%".$ind."%' "; 
			$sql.=" OR m.HouseConstruction LIKE '%".$ind."%' ";
			$sql.=" OR r.Block LIKE '%".$ind."%' ";
			$sql.=" OR r.Lot LIKE '%".$ind."%' ";
			$sql.=" OR r.ReservationStatus LIKE '%".$ind."%' ";
			$sql.=" OR r.Broker LIKE '%".htmlentities($ind)."%' ) ";
		}
	}

	if($isfilterdate=="yes"){
		if(!empty($monthFrom) && !empty($monthTo)){
			$sql .= " AND r.ReservationDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
		}else if( !empty($monthFrom)){
			$sql .= " AND r.ReservationDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
		}else if( !empty($monthTo)){
			$sql .= " AND r.ReservationDate <= ('".date('Y-m-d', strtotime($monthTo))."') ";
		} 
	} 
	 
	$mecon->query($sql);
	$result3 = $mecon->resultset();  
	$dataCancel = array();
	for($a=0; $a<count($result3); $a++){ 
	$cancelArr	=	array();	
		$cancelArr[] = $a+1; 
		$cancelArr[] = $result3[$a]['ReservationDate']; 
		$cancelArr[] = $result3[$a]['Phase']; 
		$cancelArr[] = $result3[$a]['Block']; 
		$cancelArr[] = $result3[$a]['Lot']; 
		$cancelArr[] = strtoupper(html_entity_decode(stripslashes($result3[$a]['FullName']))); 
		//$cancelArr[] = $result3[$a]['PricelistStatus'];    
		
			$pricedate	= html_entity_decode(stripslashes($result3[$a]['PricelistDate'])); 
			$phase		= html_entity_decode(stripslashes($result3[$a]['Phase'])); 
			$block		= html_entity_decode(stripslashes($result3[$a]['Block'])); 
			$lot		= html_entity_decode(stripslashes($result3[$a]['Lot'])); 
			$project	= html_entity_decode(stripslashes($result3[$a]['Project'])); 
			$models		= html_entity_decode(stripslashes($result3[$a]['Model'])); 
			
			$other = $formClass->getOtherDetails($pricedate,$models,$project,$phase,$block,$lot);
			
			//$cancelArr[] = $other['pricelist_status'];
		
		//$cancelArr[] = (!empty($result3[$a]['HouseConstruction']) || $result3[$a]['HouseConstruction']=NULL) ? $result3[$a]['HouseConstruction'] : $other['pricelist_status'];
		$cancelArr[] = (!empty($result3[$a]['HouseConstruction']) || $result3[$a]['HouseConstruction']=NULL) ? $result3[$a]['HouseConstruction'] : "";
		
		$dataCancel[] = $cancelArr; 
	} 
	return $dataCancel;
}

function showPastDetails($broker,$monthFrom,$monthTo,$filterSearch,$isfilterdate){
	$autoload		=	new autoloadClass(); 
	$mecon			=	new condbClass();	
	$sql3 = "SELECT r.Id as Id, r.Block as Block, r.Lot as Lot, r.Model as Model, r.Project as Project, r.ReservationDate as ReservationDate, CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName, r.Broker as Broker, bcm.ChequeDate as ChequeDate, bcm.ChequeNo as ChequeNo, bcm.Bank as Bank, bcm.Amount as Amount, bcm.Remarks as Remarks, bcm.ChequeStatus as ChequeStatus, bcm.ReturnRemarks as ReturnRemarks, bcm.ChequeProcessDate as ChequeProcessDate FROM reservation r LEFT JOIN buyer_cheque_monitoring bcm ON bcm.ReservationId=r.Id  WHERE  r.Status='active' AND bcm.ReturnRemarks IS NOT NULL AND (bcm.ChequeStatus!='Hold' OR bcm.ChequeStatus!='No Check' OR bcm.ChequeStatus!='Returned') AND r.Broker LIKE '%".$broker."%' ";
  
	if(!empty($filterSearch)) { 
		$arr = explode(" ",$filterSearch);  
		foreach($arr as $ind){
			$ind = str_replace(',','',$ind);
			$sql3.=" AND (r.Model LIKE '%".$ind."%' "; 
			$sql3.=" OR r.Block LIKE '%".$ind."%'  ";
			$sql3.=" OR r.Lot LIKE '%".$ind."%'  ";
			$sql3.=" OR r.Broker LIKE '%".htmlentities($ind)."%' ) ";
		}
	}

	if($isfilterdate=="yes"){
			if(!empty($monthFrom) && !empty($monthTo)){
				$sql3 .= " AND r.ReservationDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
			}else if( !empty($monthFrom)){
				$sql3 .= " AND r.ReservationDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
			}else if( !empty($monthTo)){
				$sql3 .= " AND r.ReservationDate <= ('".date('Y-m-d', strtotime($monthTo))."') ";
			} 
	 }
	 
	$mecon->query($sql3);
	$result3 = $mecon->resultset(); 
	
	$dataPast = array();
	for($a=0; $a<count($result3); $a++){ 
	$pastArr	=	array(); 				
		$pastArr[] = $a+1; 
		$pastArr[] = strtoupper(html_entity_decode(stripslashes($result3[$a]['FullName'])));
		$pastArr[] = $result3[$a]['Block']; 
		$pastArr[] = $result3[$a]['Lot']; 
		$pastArr[] = $result3[$a]['ReservationDate']; 
		$pastArr[] = $result3[$a]['ChequeDate']; 
		$pastArr[] = $result3[$a]['ReturnRemarks'];    
		$pastArr[] = $result3[$a]['Amount'];    
		$pastArr[] = $result3[$a]['Remarks'];    
		$pastArr[] = $result3[$a]['ChequeProcessDate'];    
		$dataPast[] = $pastArr; 
	} 
	return $dataPast;
}

function showLackDetails($broker,$monthFrom, $monthTo, $filterSearch,$isfilterdate){
	$autoload		=	new autoloadClass(); 
	$mecon			=	new condbClass();	
	$formClass			=	new formClass();	

  /*
 $sql4 = "SELECT r.Id as Id, r.Block as Block, r.Lot as Lot, r.ReservationStatus as ReservationStatus, r.Model as Model, r.Project as Project, r.ReservationDate as ReservationDate, r.Phase as Phase, r.PricelistDate as PricelistDate, CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName, r.Broker as Broker, ck.BuyerStandardReqDetails as BuyerStandardReqDetails, ck.AdditionalReqDetails as AdditionalReqDetails, r.EmployeeType as EmployeeType, r.EmployeeTypeBorrower1 as EmployeeTypeBorrower1, r.EmployeeTypeBorrower2 as EmployeeTypeBorrower2, r.EmployeeTypeBorrower3 as EmployeeTypeBorrower3, ck.CoBorrower1StandardReqDetails as CoBorrower1StandardReqDetails, ck.CoBorrower1AdditionalReqDetails as CoBorrower1AdditionalReqDetails, ck.CoBorrower2StandardReqDetails as CoBorrower2StandardReqDetails, ck.CoBorrower2AdditionalReqDetails as CoBorrower2AdditionalReqDetails, ck.CoBorrower3StandardReqDetails as CoBorrower3StandardReqDetails, ck.CoBorrower3AdditionalReqDetails as CoBorrower3AdditionalReqDetails, r.NoCoBorrower as NoCoBorrower, m.HouseConstruction as HouseConstruction FROM reservation r LEFT JOIN checklist ck ON ck.ReservationId=r.Id LEFT JOIN monitoring_folders m ON m.ReservationId=r.Id WHERE  (r.ReservationStatus!='cancelled' AND r.ReservationStatus!='transferred' ) AND r.Status='active'  AND (
(ck.BuyerStandardReqDetails NOT LIKE '%[\"birth_certificate\",\"proof_of_billing\",\"barangay_clearance\",\"certificate_from_house_owner\",\"photocopy_of_two\",\"photocopy_spouse_id\",\"four_photo\",\"esav\",\"msvs\",\"postdated_checks\",\"twenty_four_checks\",\"tinno\"]%' OR ck.BuyerStandardReqDetails IS NULL OR ((ck.AdditionalReqDetails NOT LIKE '%[\"notarized_certificate\",\"latest_pay_slip\"]%' OR ck.AdditionalReqDetails IS NULL)
 AND (ck.AdditionalReqDetails NOT LIKE '%[\"photocopy_of_passport\",\"contract_of_employment\",\"certificate_of_overseas\",\"duly_notarized_spa\",\"photocopy_valid_id_spa\",\"salary_certificate\"]%' OR ck.AdditionalReqDetails IS NULL)
 AND (ck.AdditionalReqDetails NOT LIKE '%[\"latest_itr\",\"business_permit\",\"dti_registration\"]%' 
  OR ck.AdditionalReqDetails IS NULL) )) 
  AND (ck.CoBorrower1StandardReqDetails NOT LIKE '%[\"birth_certificate\",\"proof_of_billing\",\"barangay_clearance\",\"certificate_from_house_owner\",\"photocopy_of_two\",\"photocopy_spouse_id\",\"four_photo\",\"esav\",\"msvs\",\"postdated_checks\",\"twenty_four_checks\",\"tinno\"]%' OR ck.CoBorrower1StandardReqDetails IS NULL OR ((ck.CoBorrower1AdditionalReqDetails NOT LIKE '%[\"notarized_certificate\",\"latest_pay_slip\"]%' OR ck.CoBorrower1AdditionalReqDetails IS NULL)
 AND (ck.CoBorrower1AdditionalReqDetails NOT LIKE  '%[\"photocopy_of_passport\",\"contract_of_employment\",\"certificate_of_overseas\",\"duly_notarized_spa\",\"photocopy_valid_id_spa\",\"salary_certificate\"]%' OR ck.CoBorrower1AdditionalReqDetails IS NULL)
 AND (ck.CoBorrower1AdditionalReqDetails NOT LIKE '%[\"latest_itr\",\"business_permit\",\"dti_registration\"]%' 
  OR ck.CoBorrower1AdditionalReqDetails IS NULL) )) AND (ck.CoBorrower2StandardReqDetails NOT LIKE '%[\"birth_certificate\",\"proof_of_billing\",\"barangay_clearance\",\"certificate_from_house_owner\",\"photocopy_of_two\",\"photocopy_spouse_id\",\"four_photo\",\"esav\",\"msvs\",\"postdated_checks\",\"twenty_four_checks\",\"tinno\"]%' OR ck.CoBorrower2StandardReqDetails IS NULL OR ((ck.CoBorrower2AdditionalReqDetails NOT LIKE '%[\"notarized_certificate\",\"latest_pay_slip\"]%' OR ck.CoBorrower2AdditionalReqDetails IS NULL)
 AND (ck.CoBorrower2AdditionalReqDetails NOT LIKE  '%[\"photocopy_of_passport\",\"contract_of_employment\",\"certificate_of_overseas\",\"duly_notarized_spa\",\"photocopy_valid_id_spa\",\"salary_certificate\"]%' OR ck.CoBorrower2AdditionalReqDetails IS NULL)
 AND (ck.CoBorrower2AdditionalReqDetails NOT LIKE '%[\"latest_itr\",\"business_permit\",\"dti_registration\"]%' 
  OR ck.CoBorrower2AdditionalReqDetails IS NULL) )) AND (ck.CoBorrower3StandardReqDetails NOT LIKE '%[\"birth_certificate\",\"proof_of_billing\",\"barangay_clearance\",\"certificate_from_house_owner\",\"photocopy_of_two\",\"photocopy_spouse_id\",\"four_photo\",\"esav\",\"msvs\",\"postdated_checks\",\"twenty_four_checks\",\"tinno\"]%' OR ck.CoBorrower3StandardReqDetails IS NULL OR ((ck.CoBorrower3AdditionalReqDetails NOT LIKE '%[\"notarized_certificate\",\"latest_pay_slip\"]%' OR ck.CoBorrower3AdditionalReqDetails IS NULL)
 AND (ck.CoBorrower3AdditionalReqDetails NOT LIKE  '%[\"photocopy_of_passport\",\"contract_of_employment\",\"certificate_of_overseas\",\"duly_notarized_spa\",\"photocopy_valid_id_spa\",\"salary_certificate\"]%' OR ck.CoBorrower3AdditionalReqDetails IS NULL)
 AND (ck.CoBorrower3AdditionalReqDetails NOT LIKE '%[\"latest_itr\",\"business_permit\",\"dti_registration\"]%' 
  OR ck.CoBorrower3AdditionalReqDetails IS NULL) )) ) "; */
 


 $resultQuery 		= "SELECT r.ReservationStatus as ReservationStatus, r.Id as Id, r.Block as Block, r.Lot as Lot, r.Model as Model, r.Project as Project, r.ReservationDate as ReservationDate, r.Phase as Phase, r.PricelistDate as PricelistDate, CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) as FullName, r.Broker as Broker, ck.BuyerStandardReqDetails as BuyerStandardReqDetails, ck.AdditionalReqDetails as AdditionalReqDetails, r.EmployeeType as EmployeeType, r.EmployeeTypeBorrower1 as EmployeeTypeBorrower1, r.EmployeeTypeBorrower2 as EmployeeTypeBorrower2, r.EmployeeTypeBorrower3 as EmployeeTypeBorrower3, ck.CoBorrower1StandardReqDetails as CoBorrower1StandardReqDetails, ck.CoBorrower1AdditionalReqDetails as CoBorrower1AdditionalReqDetails, ck.CoBorrower2StandardReqDetails as CoBorrower2StandardReqDetails, ck.CoBorrower2AdditionalReqDetails as CoBorrower2AdditionalReqDetails, ck.CoBorrower3StandardReqDetails as CoBorrower3StandardReqDetails, ck.CoBorrower3AdditionalReqDetails as CoBorrower3AdditionalReqDetails, r.NoCoBorrower as NoCoBorrower, m.HouseConstruction as HouseConstruction, bsfp.TakeOut as TakeOut FROM reservation r LEFT JOIN checklist ck ON ck.ReservationId=r.Id LEFT JOIN monitoring_folders m ON m.ReservationId=r.Id LEFT JOIN buyer_status_folder_pagibig bsfp ON bsfp.ReservationId=r.Id ";  
 
$sql4	=	$resultQuery;
$sql4 	.= "  WHERE (bsfp.TakeOut='1970-01-01' OR bsfp.TakeOut IS NULL) 
				AND (r.ReservationStatus!='transferred' AND  r.ReservationStatus!='cancelled') 
				AND r.Status='active' 
				AND (
						(ck.BuyerStandardReqDetails NOT LIKE '%[\"birth_certificate\",\"proof_of_billing\",\"barangay_clearance\",\"photocopy_of_two\",\"photocopy_spouse_id\",\"four_photo\",\"mdf\",\"esav\",\"msvs\",\"tinno\",\"postdated_checks\",\"twenty_four_checks\",\"signed_loan_documents\",\"attended_homeowners_orientation_and_signed_deed_of_restriction\"]%' 
					  		OR ck.BuyerStandardReqDetails IS NULL 
					  		OR (
					  			(ck.AdditionalReqDetails NOT LIKE '%[\"notarized_certificate\",\"latest_pay_slip\",\"income_tax_return\"]%' OR ck.AdditionalReqDetails IS NULL)

 								AND (ck.AdditionalReqDetails NOT LIKE '%[\"employment_contract\",\"employment_contract\",\"salary_certificate\",\"certificate_of_employment\",\"certificate_of_overseas\",\"owwa_membership_certificate\",\"resident_card\",\"photocopy_passport\",\"notarized_spa\",\"photocopy_valid_id_spa\"]%' OR ck.AdditionalReqDetails IS NULL)

 								AND (ck.AdditionalReqDetails NOT LIKE '%[\"income_tax_return_audited_fs\",\"certificate_of_commission\",\"commission_vouchers\",\"photocopy_bank_statement\",\"dti_registration\",\"mayors_permit\",\"lease_contract\",\"sketch_business_location\"]%' 
  									OR ck.AdditionalReqDetails IS NULL)

  								AND (ck.AdditionalReqDetails NOT LIKE '%[\"employment_contract\",\"certificate_of_employment\",\"work_permit\",\"immigrant_visa\",\"photocopy_of_passport\",\"duly_notarized_spa\",\"photocopy_valid_id_spa\"]%' 
  									OR ck.AdditionalReqDetails IS NULL)

  								AND (ck.AdditionalReqDetails NOT LIKE '%[\"bank_statement\",\"remittances\",\"certificate_of_employment\",\"picture_of_work_station\",\"sketch_map_home_address\",\"google_map\"]%' 
  									OR ck.AdditionalReqDetails IS NULL)	   
   							)
   						) 


  						AND 
  						(ck.CoBorrower1StandardReqDetails NOT LIKE '%[\"birth_certificate\",\"proof_of_billing\",\"barangay_clearance\",\"photocopy_of_two\",\"photocopy_spouse_id\",\"four_photo\",\"mdf\",\"esav\",\"msvs\",\"tinno\"]%' 
  							OR ck.CoBorrower1StandardReqDetails IS NULL 
  							OR (
  									(ck.CoBorrower1AdditionalReqDetails NOT LIKE '%[\"notarized_certificate\",\"latest_pay_slip\",\"income_tax_return\"]%' OR ck.CoBorrower1AdditionalReqDetails IS NULL)

 									AND (ck.CoBorrower1AdditionalReqDetails NOT LIKE  '%[\"employment_contract\",\"employment_contract\",\"salary_certificate\",\"certificate_of_employment\",\"certificate_of_overseas\",\"owwa_membership_certificate\",\"resident_card\",\"photocopy_passport\",\"notarized_spa\",\"photocopy_valid_id_spa\"]%' OR ck.CoBorrower1AdditionalReqDetails IS NULL)

 									AND (ck.CoBorrower1AdditionalReqDetails NOT LIKE '%[\"income_tax_return_audited_fs\",\"certificate_of_commission\",\"commission_vouchers\",\"photocopy_bank_statement\",\"dti_registration\",\"mayors_permit\",\"lease_contract\",\"sketch_business_location\"]%' OR ck.CoBorrower1AdditionalReqDetails IS NULL) 

 									AND (ck.CoBorrower1AdditionalReqDetails NOT LIKE '%[\"employment_contract\",\"certificate_of_employment\",\"work_permit\",\"immigrant_visa\",\"photocopy_of_passport\",\"duly_notarized_spa\",\"photocopy_valid_id_spa\"]%' OR ck.CoBorrower1AdditionalReqDetails IS NULL)

  									AND (ck.CoBorrower1AdditionalReqDetails NOT LIKE '%[\"bank_statement\",\"remittances\",\"certificate_of_employment\",\"picture_of_work_station\",\"sketch_map_home_address\",\"google_map\"]%' OR ck.CoBorrower1AdditionalReqDetails IS NULL)	

 								)
 						) 


  						AND 
  						(ck.CoBorrower2StandardReqDetails NOT LIKE '%[\"birth_certificate\",\"proof_of_billing\",\"barangay_clearance\",\"photocopy_of_two\",\"photocopy_spouse_id\",\"four_photo\",\"mdf\",\"esav\",\"msvs\",\"tinno\"]%' 
  							OR ck.CoBorrower2StandardReqDetails IS NULL 
  							OR (
  									(ck.CoBorrower2AdditionalReqDetails NOT LIKE '%[\"notarized_certificate\",\"latest_pay_slip\",\"income_tax_return\"]%' OR ck.CoBorrower2AdditionalReqDetails IS NULL)

 									AND (ck.CoBorrower2AdditionalReqDetails NOT LIKE  '%[\"employment_contract\",\"employment_contract\",\"salary_certificate\",\"certificate_of_employment\",\"certificate_of_overseas\",\"owwa_membership_certificate\",\"resident_card\",\"photocopy_passport\",\"notarized_spa\",\"photocopy_valid_id_spa\"]%' OR ck.CoBorrower2AdditionalReqDetails IS NULL)

 									AND (ck.CoBorrower2AdditionalReqDetails NOT LIKE '%[\"income_tax_return_audited_fs\",\"certificate_of_commission\",\"commission_vouchers\",\"photocopy_bank_statement\",\"dti_registration\",\"mayors_permit\",\"lease_contract\",\"sketch_business_location\"]%' OR ck.CoBorrower2AdditionalReqDetails IS NULL) 

 									AND (ck.CoBorrower2AdditionalReqDetails NOT LIKE '%[\"employment_contract\",\"certificate_of_employment\",\"work_permit\",\"immigrant_visa\",\"photocopy_of_passport\",\"duly_notarized_spa\",\"photocopy_valid_id_spa\"]%' OR ck.CoBorrower2AdditionalReqDetails IS NULL)

  									AND (ck.CoBorrower2AdditionalReqDetails NOT LIKE '%[\"bank_statement\",\"remittances\",\"certificate_of_employment\",\"picture_of_work_station\",\"sketch_map_home_address\",\"google_map\"]%' OR ck.CoBorrower2AdditionalReqDetails IS NULL)	
 								)
 						) 

  						AND 
  						(ck.CoBorrower3StandardReqDetails NOT LIKE '%[\"birth_certificate\",\"proof_of_billing\",\"barangay_clearance\",\"photocopy_of_two\",\"photocopy_spouse_id\",\"four_photo\",\"mdf\",\"esav\",\"msvs\",\"tinno\"]%' 
  							OR ck.CoBorrower3StandardReqDetails IS NULL 
  							OR (
  									(ck.CoBorrower3AdditionalReqDetails NOT LIKE '%[\"notarized_certificate\",\"latest_pay_slip\",\"income_tax_return\"]%' OR ck.CoBorrower3AdditionalReqDetails IS NULL)

 									AND (ck.CoBorrower3AdditionalReqDetails NOT LIKE  '%[\"employment_contract\",\"employment_contract\",\"salary_certificate\",\"certificate_of_employment\",\"certificate_of_overseas\",\"owwa_membership_certificate\",\"resident_card\",\"photocopy_passport\",\"notarized_spa\",\"photocopy_valid_id_spa\"]%' OR ck.CoBorrower3AdditionalReqDetails IS NULL)

 									AND (ck.CoBorrower3AdditionalReqDetails NOT LIKE '%[\"income_tax_return_audited_fs\",\"certificate_of_commission\",\"commission_vouchers\",\"photocopy_bank_statement\",\"dti_registration\",\"mayors_permit\",\"lease_contract\",\"sketch_business_location\"]%' OR ck.CoBorrower3AdditionalReqDetails IS NULL) 

 									AND (ck.CoBorrower3AdditionalReqDetails NOT LIKE '%[\"employment_contract\",\"certificate_of_employment\",\"work_permit\",\"immigrant_visa\",\"photocopy_of_passport\",\"duly_notarized_spa\",\"photocopy_valid_id_spa\"]%' OR ck.CoBorrower3AdditionalReqDetails IS NULL)

  									AND (ck.CoBorrower3AdditionalReqDetails NOT LIKE '%[\"bank_statement\",\"remittances\",\"certificate_of_employment\",\"picture_of_work_station\",\"sketch_map_home_address\",\"google_map\"]%' OR ck.CoBorrower3AdditionalReqDetails IS NULL)	
 								)
 						) 

					) ";

	if(!empty($filterSearch)) { 
		$arr = explode(" ",$filterSearch);  
		foreach($arr as $ind){
			$ind = str_replace(',','',$ind);
			$sql4.=" AND (r.Block LIKE '%".$ind."%' ";
			$sql4.=" OR r.Lot LIKE '%".$ind."%' ";
			$sql4.=" OR m.HouseConstruction LIKE '%".$ind."%' ";
			$sql4.=" OR r.Project LIKE '%".$ind."%' ";
			$sql4.=" OR r.Model LIKE '%".$ind."%' "; 
			//$sql4.=" OR r.Broker LIKE '%".addslashes(stripslashes($ind))."%' ";   
			$sql4.=" OR r.Broker LIKE '%".$ind."%' ";   
			$sql4.=" OR CONCAT(r.LastName,', ',r.FirstName,' ',r.MiddleName) LIKE '%".htmlentities($ind)."%' ";
			$sql4.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%m/%d/%Y')) LIKE '%".$ind."%' ";
			$sql4.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%d-%b-%Y')) LIKE '%".$ind."%' ) ";   
		}
	} 
	$sql4 .= ' AND r.Broker LIKE \'%'.$broker.'%\' ';
	 

if($isfilterdate=="yes"){	
	if(!empty($monthFrom) && !empty($monthTo)){
		$sql4 .= ' AND r.ReservationDate BETWEEN (\''.date('Y-m-d', strtotime($monthFrom)).'\') AND (\''.date('Y-m-d', strtotime($monthTo)).'\')';
	}else if( !empty($monthFrom)){
		$sql4 .= " AND r.ReservationDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
	}else if( !empty($monthTo)){
		$sql4 .= " AND r.ReservationDate <= ('".date('Y-m-d', strtotime($monthTo))."') ";
	} 
}
	 
	$mecon->query($sql4);
	$result3 = $mecon->resultset(); 
	
	$dataLack = array();
	for($a=0; $a<count($result3); $a++){ 
	$lackArr	=	array(); 				
		   
			/*$buyerDetail		= $result3[$a]['BuyerStandardReqDetails'];
			$decodeBuyerDetail	= json_decode($buyerDetail);  
			$buyerDetailCtr		= count($decodeBuyerDetail);
			$additionalbuyerDetail	= str_replace('""','',$result3[$a]['AdditionalReqDetails']);
			//$additionalbuyerDetail	= $result3[$a]['AdditionalReqDetails'];
			$decodeAdditionalDetail	= json_decode($additionalbuyerDetail);  
			$additionalDetailCtr	= count($decodeAdditionalDetail);
			
				if($buyerDetailCtr==0 && $additionalDetailCtr==0){
					$lack = "";
				}else{
					if($additionalDetailCtr==0){
						$additionalbuyerDetail = '[]';
					}else if($buyerDetailCtr==0){
						$buyerDetail = '[]';
					} 
				
					$empType = $result3[$a]['EmployeeType'];
					$lack = getMainLackDoc($buyerDetail,$additionalbuyerDetail,$empType);
				}
			*/	 
		 
		 
		 //main
			$buyerDetail		= str_replace('""','',$result3[$a]['BuyerStandardReqDetails']);
			$decodeBuyerDetail	= json_decode($buyerDetail);  
			$buyerDetailCtr		= count($decodeBuyerDetail);
			
			$additionalbuyerDetail	= str_replace('""','',$result3[$a]['AdditionalReqDetails']);
			$decodeAdditionalDetail	= json_decode($additionalbuyerDetail);  
			$additionalDetailCtr	= count($decodeAdditionalDetail);
			
				if($buyerDetailCtr==0 && $additionalDetailCtr==0){
					$lack = "";
				}else{
					if($additionalDetailCtr==0){
						$additionalbuyerDetail = '[]';
					}else if($buyerDetailCtr==0){
						$buyerDetail = '[]';
					} 
					$empType = $result3[$a]['EmployeeType'];
					$lack = getMainLackDoc($buyerDetail,$additionalbuyerDetail,$empType,"primary");
					 
				}
			$noBorrower	= $result3[$a]['NoCoBorrower'];	
			
			$buyerDetailCtr1 = "";
			$additionalDetailCtr1 = "";
			
			$buyerDetailCtr2 = "";
			$additionalDetailCtr2 = "";
			
			$buyerDetailCtr3 = "";
			$additionalDetailCtr3 = "";
			
			
			if($noBorrower!=0){
				
				//borrower1
			$buyerDetail1		= str_replace('""','',$result3[$a]['CoBorrower1StandardReqDetails']);
			$decodeBuyerDetail1	= json_decode($buyerDetail1);  
			$buyerDetailCtr1		= count($decodeBuyerDetail1);
			
			$additionalbuyerDetail1	= str_replace('""','',$result3[$a]['CoBorrower1AdditionalReqDetails']);
			$decodeAdditionalDetail1	= json_decode($additionalbuyerDetail1);  
			$additionalDetailCtr1	= count($decodeAdditionalDetail1);
			
				if($buyerDetailCtr1==0 && $additionalDetailCtr1==0){
					$lack = "";
				}else{
					if($additionalDetailCtr1==0){
						$additionalbuyerDetail1 = '[]';
					}else if($buyerDetailCtr1==0){
						$buyerDetail1 = '[]';
					} 
					
					
					$empType1 = $result3[$a]['EmployeeTypeBorrower1'];
					$lack .= " [Co-Borrower1] :".getMainLackDoc($buyerDetail1,$additionalbuyerDetail1,$empType1,"coborrower");
				}
				
				//borrower2
			$buyerDetail2		= str_replace('""','',$result3[$a]['CoBorrower2StandardReqDetails']);
			$decodeBuyerDetail2	= json_decode($buyerDetail2);  
			$buyerDetailCtr2		= count($decodeBuyerDetail2);
			
			$additionalbuyerDetail2	= str_replace('""','',$result3[$a]['CoBorrower2AdditionalReqDetails']);
			$decodeAdditionalDetail2 = json_decode($additionalbuyerDetail2);
			$additionalDetailCtr2	= count($decodeAdditionalDetail2);
			
				if($buyerDetailCtr2==0 && $additionalDetailCtr2==0){
					$lack = "";
				}else{
					if($additionalDetailCtr2==0){
						$additionalbuyerDetail2 = '[]';
					}else if($buyerDetailCtr2==0){
						$buyerDetail2 = '[]';
					} 
					
					
					$empType2 = $result3[$a]['EmployeeTypeBorrower2'];
					$lack .= " [Co-Borrower2] :".getMainLackDoc($buyerDetail2,$additionalbuyerDetail2,$empType2,"coborrower");
				}
				
				//borrower3
			$buyerDetail3		= str_replace('""','',$result3[$a]['CoBorrower3StandardReqDetails']);
			$decodeBuyerDetail3	= json_decode($buyerDetail3);
			$buyerDetailCtr3	= count($decodeBuyerDetail3);
			
			$additionalbuyerDetail3		= str_replace('""','',$result3[$a]['CoBorrower3AdditionalReqDetails']);
			$decodeAdditionalDetail3	= json_decode($additionalbuyerDetail3);  
			$additionalDetailCtr3		= count($decodeAdditionalDetail3);
			
				if($buyerDetailCtr3==0 && $additionalDetailCtr3==0){
					$lack = "";
				}else{
					if($additionalDetailCtr3==0){
						$additionalbuyerDetail3 = '[]';
					}else if($buyerDetailCtr3==0){
						$buyerDetail3 = '[]';
					} 
					
					
					$empType3 = $result3[$a]['EmployeeTypeBorrower3'];
					$lack .= " [Co-Borrower3] :".getMainLackDoc($buyerDetail3,$additionalbuyerDetail3,$empType3,"coborrower");
				}
				 
		 }
		 
				$lackArr[] = $a + 1; 
				$lackArr[] = (($result3[$a]['ReservationDate']=='1970-01-01') || ($result3[$a]['ReservationDate']=='')) ? "" : date('Y-m-d', strtotime($result3[$a]['ReservationDate']));
				
				$lackArr[] = (!empty($result3[$a]['Block']) || $result3[$a]['Block']!=NULL || $result3[$a]['Block']!='') ? html_entity_decode(stripslashes($result3[$a]['Block'])) : '-';
				$lackArr[] = (!empty($result3[$a]['Lot']) || $result3[$a]['Lot']!=NULL || $result3[$a]['Lot']!='') ? html_entity_decode(stripslashes($result3[$a]['Lot'])) : '-';
				 
				$lackArr[] = strtoupper(html_entity_decode(stripslashes($result3[$a]['FullName'])));
				$lackArr[] = html_entity_decode(stripslashes($result3[$a]['Broker']));
				//$lackArr[] = html_entity_decode(stripslashes($result3[$a]['PricelistStatus'])); 
				
				$pricedate	= html_entity_decode(stripslashes($result3[$a]['PricelistDate'])); 
			$phase		= (!empty($result3[$a]['Phase']) || $result3[$a]['Phase']!='' || $result3[$a]['Phase']!=NULL) ? html_entity_decode(stripslashes($result3[$a]['Phase'])) : ''; 
			$block		= (!empty($result3[$a]['Block']) || $result3[$a]['Block']!=NULL || $result3[$a]['Block']!='') ? html_entity_decode(stripslashes($result3[$a]['Block'])) : ''; 
			$lot		= (!empty($result3[$a]['Lot']) || $result3[$a]['Lot']!='' || $result3[$a]['Lot']!=NULL) ? html_entity_decode(stripslashes($result3[$a]['Lot'])) : ''; 
			$project	= html_entity_decode(stripslashes($result3[$a]['Project'])); 
			$models		= html_entity_decode(stripslashes($result3[$a]['Model'])); 
			
			$other = $formClass->getOtherDetails($pricedate,$models,$project,$phase,$block,$lot);		
			if($phase!='' && $block!='' && $lot!=''){
				//$lackArr[] = $other['pricelist_status'];
				
				//$lackArr[] = (!empty($result3[$a]['HouseConstruction']) || $result3[$a]['HouseConstruction']=NULL) ? $result3[$a]['HouseConstruction'] : $other['pricelist_status'];
				//$lackArr[] = (!empty($result3[$a]['HouseConstruction']) || $result3[$a]['HouseConstruction']=NULL) ? $result3[$a]['HouseConstruction'] : "";
				
			}else{
				//$lackArr[] = '-';
			}
			
				//$lackArr[] = ($buyerDetailCtr==0 && $additionalDetailCtr==0) ? "New buyer - to submit all Documentary Requirements" : $lack;
				
				$lackArr[] = ($buyerDetailCtr==0 && $additionalDetailCtr==0 && $buyerDetailCtr1==0 && $additionalDetailCtr1==0 && $buyerDetailCtr2==0 && $additionalDetailCtr2==0 && $buyerDetailCtr3==0 && $additionalDetailCtr3==0) ? "New buyer - to submit all Documentary Requirements" : $lack;

			$dataLack[] = $lackArr;
	} 
	return $dataLack;
}

function getUniqueBrokers($lack,$past){
	$uniqueArr = array();
	$uniqueArr = array_merge($past,$lack);
	$uniqueArr = array_unique($uniqueArr);
	return $uniqueArr;
}  



//MAIN DETAILS
function getMainLackDoc($mainDet,$addDet,$empType,$type){ 
	$lack		= "";
	$lack2		= "";
	$lacks		= "";
	$submitArr	= json_decode($mainDet,true);    
	$submitArr2	= json_decode($addDet,true);    
	
	
//Complete MAIN
if($type=='primary'){ 
	$completeMainArr = array("birth_certificate","proof_of_billing","barangay_clearance","photocopy_of_two","photocopy_spouse_id","four_photo","mdf","esav","msvs","tinno","postdated_checks","twenty_four_checks","signed_loan_documents","attended_homeowners_orientation_and_signed_deed_of_restriction");	  
}else if($type=='coborrower'){
	$completeMainArr = array("birth_certificate","proof_of_billing","barangay_clearance","photocopy_of_two","photocopy_spouse_id","four_photo","mdf","esav","msvs","tinno");	 
}
	 
	
//COMPLETE ADDITIONAL
if($type=='primary'){ 	
	if($empType=="LOCALLY EMPLOYED"){ //LOCALLY EMPLOYED
		$completeAdditionalArr = array("notarized_certificate","latest_pay_slip","income_tax_return"); 

	}else if($empType=="OFW"){ //OFW 
		$completeAdditionalArr = array("employment_contract","salary_certificate","certificate_of_employment","certificate_of_overseas","owwa_membership_certificate","resident_card","photocopy_passport","notarized_spa","photocopy_valid_id_spa");     

	}else if($empType=="SELF-EMPLOYED / OWNED BUSINESS"){ //SELF-EMPLOYED
		$completeAdditionalArr = array("income_tax_return_audited_fs","certificate_of_commission","commission_vouchers","photocopy_bank_statement","dti_registration","mayors_permit","lease_contract","sketch_business_location");   

	}else if($empType=="ONLINE EMPLOYMENT"){ //ONLINE EMPLOYMENT 
		$completeAdditionalArr = array("bank_statement","remittances","certificate_of_employment","picture_of_work_station","sketch_map_home_address","google_map");     
  
	}else if($empType=="IMMIGRANT"){ //IMMIGRANT 
		$completeAdditionalArr = array("employment_contract","certificate_of_employment","work_permit","immigrant_visa","photocopy_of_passport","duly_notarized_spa","photocopy_valid_id_spa");  
	}else{
		$completeAdditionalArr = array("");
	} 
}else if($type=='coborrower'){
		$completeAdditionalArr = array("");
}	
	
	
	$diffArr = array_diff($completeMainArr,$submitArr);
	$diffArr2 = array_diff($completeAdditionalArr,$submitArr2); 

	
	foreach($diffArr as $key => $val){
		if($val=="birth_certificate"){
			$lack .= " Birth Certificate if Single/Marriage Contract if Married, ";
		}else if($val=="proof_of_billing"){
			$lack .= " Proof of Billing *, ";
		}else if($val=="barangay_clearance"){
			$lack .= " Barangay Clearance *, ";
		}else if($val=="photocopy_of_two"){
			$lack .= " Photocopy of Two (2) Valid ID ( passport, company ID, driver's license, PRC ID, SSS, TIN, etc ) back-to-back; signed 3 times, ";
		}else if($val=="photocopy_spouse_id"){
			$lack .= " Photocopy of valid ID of Spouse (if married); back-to-back; signed 3 times, ";
		}else if($val=="four_photo"){
			$lack .= " Four (4) pcs size 1 x 1 ID photo *, ";
		}else if($val=="mdf"){
			$lack .= " Member's Data Form, ";
		}else if($val=="esav"){
			$lack .= " Employee Statement of Accumulated Value (ESAV) from Pag-ibig, ";
		}else if($val=="msvs"){
			$lack .= " MSVS (Membership Status Verification Slip) signed by borrower *, ";
		}else if($val=="tinno"){
			$lack .= " Tax Identification Number (TIN), ";
		}else if($val=="twenty_four_checks"){
			$lack .= " Twenty four (24) Blank Checks for HDMF Amortization *";
		}else if($val=="postdated_checks"){
			$lack .= " Postdated Checks for Equity *, ";
		}else if($val=="signed_loan_documents"){
			$lack .= " Signed Loan Documents, ";
		}else if($val=="attended_homeowners_orientation_and_signed_deed_of_restriction"){
			$lack .= " Attended Homeowner's Orientation and signed Deed of Restriction, ";
		}
	}

	  
	if($empType=="LOCALLY EMPLOYED"){ //LOCALLY EMPLOYED 
		foreach($diffArr2 as $key2 => $val2){
			if($val2=="notarized_certificate"){
				$lack2 .= "  Duly Notarized Latest Certificate of Employment and Compensation *,";
			}else if($val2=="latest_pay_slip"){
				$lack2 .= "  Latest One Month Pay Slip *,";
			}else if($val2=="income_tax_return"){
				$lack2 .= "  Income Tax Return / BIR 2316,";
			} 
		} 
	}else if($empType=="OFW"){ //OFW 
		 foreach($diffArr2 as $key2 => $val2){
			if($val2=="employment_contract"){
				$lack2 .= " Employment Contract,";
			}else if($val2=="salary_certificate"){
				$lack2 .= " Payslip / Salary Certificate,";
			}else if($val2=="certificate_of_employment"){
				$lack2 .= " Certificate of Employment & Compensation,";
			}else if($val2=="certificate_of_overseas"){
				$lack2 .= " Overseas Employment Certificate from POEA,";
			}else if($val2=="owwa_membership_certificate"){
				$lack2 .= " Valid OWWA Membership Certificate,";
			}else if($val2=="resident_card"){
				$lack2 .= " Resident Card / Permit,";
			}else if($val2=="photocopy_passport"){
				$lack2 .= " Photocopy of Passport with appropriate visa, signed 3 times,";
			}else if($val2=="notarized_spa"){
				$lack2 .= " Duly Notarized SPA* / Red Ribbon SPA (if abroad)*,";
			}else if($val2=="photocopy_valid_id_spa"){
				$lack2 .= " Photocopy of Valid ID of SPA, back to back, signed 3 times,";
			} 
		}
	}else if($empType=="SELF-EMPLOYED / OWNED BUSINESS"){ //SELF-EMPLOYED
		 foreach($diffArr2 as $key2 => $val2){
			if($val2=="income_tax_return_audited_fs"){
				$lack2 .= " CTC of Income Tax Return* & Audited Financial Statements with Official Receipt of Tax Payment,";
			}else if($val2=="certificate_of_commission"){
				$lack2 .= " Certificate of Commission* / Certificate of Engagement* / CTC of Transport Franchise,";
			}else if($val2=="commission_vouchers"){
				$lack2 .= " 12 months Commission Vouchers,";
			}else if($val2=="photocopy_bank_statement"){
				$lack2 .= " 12 months Bank Statements* / Photocopy of Passbook,";
			}else if($val2=="dti_registration"){
				$lack2 .= " DTI Registration,";
			}else if($val2=="mayors_permit"){
				$lack2 .= " Mayor's Permit,";
			}else if($val2=="lease_contract"){
				$lack2 .= " Notarized Lease Contract / Proof of Ownership or Rights,";
			}else if($val2=="sketch_business_location"){
				$lack2 .= " Sketch of Business Location,";
			}  
		}
	}else if($empType=="ONLINE EMPLOYMENT"){ //ONLINE EMPLOYMENT
		 foreach($diffArr2 as $key2 => $val2){
			if($val2=="bank_statement"){
				$lack2 .= " 12 month Bank Statement*,";
			}else if($val2=="remittances"){
				$lack2 .= " Latest one (1) year remittances,";
			}else if($val2=="certificate_of_employment"){
				$lack2 .= " Certificate of Employment & Compensation,";
			}else if($val2=="picture_of_work_station"){
				$lack2 .= " Picture of Work Station,";
			}else if($val2=="sketch_map_home_address"){
				$lack2 .= " Sketch Map of Employee's Home Address*,";
			}else if($val2=="google_map"){
				$lack2 .= " Google Map of Employer's Business Location,";
			}  
		}
	}else if($empType=="IMMIGRANT"){ //IMMIGRANT
		foreach($diffArr2 as $key2 => $val2){
			if($val2=="employment_contract"){
				$lack2 .= " Employment Contract,";
			}else if($val2=="certificate_of_employment"){
				$lack2 .= " Certificate of Employment & Compensation,";
			}else if($val2=="work_permit"){
				$lack2 .= " Work Permit / Card,";
			}else if($val2=="immigrant_visa"){
				$lack2 .= " Immigrant Visa / Permanent Resident Card,";
			}else if($val2=="photocopy_of_passport"){
				$lack2 .= " Photocopy of Passport signed 3 times,";
			}else if($val2=="duly_notarized_spa"){
				$lack2 .= " Duly Notarized SPA* / Red Ribbon SPA (if abroad)*,";
			}else if($val2=="photocopy_valid_id_spa"){
				$lack2 .= " Photocopy of Valid ID of SPA, back to back, signed 3 times,";
			}   
		}
	}

	$lacks = $lack . $lack2;
	$output	= trim($lacks, ","); 
	return $output;
} 