<?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']) : "";
$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){
	 
	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);
	
				$newsheet->setTitle(html_entity_decode(strip_tags($mVal)));
				$prevAssetName = $mVal; 
				$modelFilter = $mVal; 
				$projectName = $_SESSION['danvillee_project'];
				$newsheet->setCellValue('A1',$projectName);
				$newsheet->setCellValue('A2',$mVal);
				$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){					
					$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('G'.$baseAdd,"LACKING REQUIREMENTS"); 
						$newsheet->mergeCells('G7: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('G'.$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('G'.$baseAdd.':I'.$baseAdd);
						$newsheet->getColumnDimension('G')->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('G'.$f,$showLackArr[$d][7]); 
						$newsheet->mergeCells('G'.$f.':I'.$f);
							$objPHPExcel->getActiveSheet()->getStyle('G1:G'.$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 	= $_SESSION['danvillee_lastname'].", ".$_SESSION['danvillee_firstname']; 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
header('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
$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,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!='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) )) ) ";
 
	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(bsfp.TakeOut,'%m/%d/%Y')) LIKE '%".$ind."%' ";
			$sql.=" OR UPPER(DATE_FORMAT(bsfp.TakeOut,'%d-%b-%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 '%".htmlentities($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 '%".htmlentities($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,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!='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) )) ) ";
 
	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 '%".htmlentities($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(bsfp.TakeOut,'%m/%d/%Y')) LIKE '%".$ind."%' ";
			$sql4.=" OR UPPER(DATE_FORMAT(bsfp.TakeOut,'%d-%b-%Y')) LIKE '%".$ind."%'  ";   
			$sql4.=" OR UPPER(DATE_FORMAT(r.ReservationDate,'%d-%b-%Y')) LIKE '%".$ind."%' ) ";   
		}
	} 
	$sql4 .= ' AND r.Broker LIKE \'%'.htmlentities($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);
					 
				}
			$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);
				}
				
				//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);
				}
				
				//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);
				}
				 
		 }
		 
				$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){ 
	$lack		= "";
	$lack2		= "";
	$lacks		= "";
	$submitArr	= json_decode($mainDet,true);    
	$submitArr2	= json_decode($addDet,true);    
	
	//Complete MAIN
	$completeMainArr = array("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");	
	
	//COMPLETE ADDITIONAL
	//$completeAdditionalArr = array("notarized_certificate","latest_pay_slip"); 
	if($empType=="PRIVATE / GOVERNMENT EMPLOYEE"){ //PRIVATE
		$completeAdditionalArr = array("notarized_certificate","latest_pay_slip");
	}else if($empType=="OVERSEAS CONTRACT WORKER"){ //OVERSEAS 
		$completeAdditionalArr = array("photocopy_of_passport","contract_of_employment","certificate_of_overseas","duly_notarized_spa","photocopy_valid_id_spa","salary_certificate");
	}else if($empType=="SELF-EMPLOYED / OWNED BUSINESS"){ //SELF-EMPLOYED
		$completeAdditionalArr = array("latest_itr","business_permit","dti_registration");
	}else{
		$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=="certificate_from_house_owner"){
			$lack .= " Certificate from House Owner / Lessor with photocopy of his valid ID, back-to-back; signed 3 times *, ";
		}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=="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=="twenty_four_checks"){
			$lack .= " Twenty four (24) Blank Checks for HDMF Amortization *, ";
		}else if($val=="postdated_checks"){
			$lack .= " Postdated Checks for Miscellaneous Fee *, ";
		}else if($val=="tinno"){
			$lack .= " Tax Identification Number (TIN), ";
		}
	}

	  
	if($empType=="PRIVATE / GOVERNMENT EMPLOYEE"){ //PRIVATE 
		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($empType=="OVERSEAS CONTRACT WORKER"){ //OVERSEAS 
		 foreach($diffArr2 as $key2 => $val2){
			if($val2=="photocopy_of_passport"){
				$lack2 .= " Photocopy of Passport signed 3 times,";
			}else if($val2=="contract_of_employment"){
				$lack2 .= " Contract of Employment,";
			}else if($val2=="certificate_of_overseas"){
				$lack2 .= " Certificate of Overseas Filipino Worker Information from POEA *,";
			}else if($val2=="duly_notarized_spa"){
				$lack2 .= " Duly Notarized SPA*/ Red Ribbon Special Power of Attorney (if already abroad)* 5 original copies,";
			}else if($val2=="photocopy_valid_id_spa"){
				$lack2 .= " Photocopy of Valid ID of SPA; back-to-back; signed 3 times,";
			}else if($val2=="salary_certificate"){
				$lack2 .= " Payslip / Salary Certificate,";
			}
		}
	}else if($empType=="SELF-EMPLOYED / OWNED BUSINESS"){ //SELF-EMPLOYED
		 foreach($diffArr2 as $key2 => $val2){
			if($val2=="latest_itr"){
				$lack2 .= " Latest ITR with Audited Financial Statement,";
			}else if($val2=="business_permit"){
				$lack2 .= " Business Permit / Mayor's Permit,";
			}else if($val2=="dti_registration"){
				$lack2 .= " DTI Registration,";
			}
		}
	}

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