<?php error_reporting(0);
set_time_limit(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');

$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		=	"sales_report_per_agent.xls";
$objPHPExcel	=	$objReader->load("templates/sales_report_per_agent.xls"); 

$marr			=	array();
$carr			=	array();
$darr			=	array();
$marr			=	getModel($monthFrom,$monthTo,$filterSearch,$isfilterdate);  
		//LOOP PER SHEET
		$prevAssetName = NULL;
		$objPHPExcel->removeSheetByIndex(0);
		$objPHPExcel->removeSheetByIndex(1); 
		$objPHPExcel->removeSheetByIndex(0);
			$activeJanTotal = ""; $activeFebTotal = ""; $activeMarTotal = ""; $activeAprTotal = ""; $activeMayTotal = ""; $activeJunTotal = ""; $activeJulTotal = ""; $activeAugTotal = ""; $activeSepTotal = ""; $activeOctTotal = ""; $activeNovTotal = ""; $activeDecTotal = "";  $activeTotalSales = "";  $activeNetTotal = ""; $activeTransferTotal = "";
				
				$cancelJanTotal = ""; $cancelFebTotal = ""; $cancelMarTotal = ""; $cancelAprTotal = ""; $cancelMayTotal = ""; $cancelJunTotal = ""; $cancelJulTotal = ""; $cancelAugTotal = ""; $cancelSepTotal = ""; $cancelOctTotal = ""; $cancelNovTotal = ""; $cancelDecTotal = "";  $cancelTotalSales = ""; 
				
		for($b=0; $b<count($marr); $b++){ 
			if ($marr[$b][1] !== $prevAssetName) {
				$newsheet = $objPHPExcel->createSheet();
				$newsheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
				$newsheet->setTitle($marr[$b][1]);
				$prevAssetName = $marr[$b][1];
				
				$modelFilter = $marr[$b][1]; 
				$projectName 		= $_SESSION['danvillee_project'];
				$projectNameVenue 	= $_SESSION['danvillee_project_venue'];
				$newsheet->setCellValue('A1', $projectName); 
				$newsheet->setCellValue('A2', $projectNameVenue);
				$newsheet->setCellValue('A4',strtoupper($modelFilter)." MODEL");
				
				$newsheet->getColumnDimension('A')->setWidth(30);
				$newsheet->getColumnDimension('B')->setWidth(30);
				$newsheet->getColumnDimension('P')->setWidth(15);
				$newsheet->getColumnDimension('R')->setWidth(15);
				$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),'font' => array('bold' => true));
				//DETAILS PER SHEET
				$darr = getModelDetails($monthFrom,$monthTo,$filterSearch,$modelFilter,$isfilterdate);
				if(count($darr)){
				$newsheet->getStyle('A5:S5')->applyFromArray($styleArray);
				unset($styleArray); 
				$newsheet->setCellValue('A5',"AGENT")->setCellValue('B5',"BROKERS")->setCellValue('C5',"YEAR")->setCellValue('D5',"JAN")->setCellValue('E5',"FEB")->setCellValue('F5',"MAR")->setCellValue('G5',"APR")->setCellValue('H5',"MAY")->setCellValue('I5',"JUN")->setCellValue('J5',"JUL")->setCellValue('K5',"AUG")->setCellValue('L5',"SEP")->setCellValue('M5',"OCT")->setCellValue('N5',"NOV")->setCellValue('O5',"DEC")->setCellValue('P5',"TOTAL SALES");
				//->setCellValue('Q5',"NET")->setCellValue('R5',"INTENT TO BUY")->setCellValue('S5',"TRANSFER");
			
					$dRow = 6;
					
					for($c=0; $c<count($darr); $c++){
						$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
						$rowCount = $dRow + $c;
						$newsheet->getStyle('A'.$rowCount.':S'.$rowCount)->applyFromArray($styleArray);
						unset($styleArray);  
						$newsheet->setCellValue('A'.$rowCount,$darr[$c][19])->setCellValue('B'.$rowCount,$darr[$c][2])->setCellValue('C'.$rowCount,$darr[$c][16])->setCellValue('D'.$rowCount,$darr[$c][3])->setCellValue('E'.$rowCount,$darr[$c][4])->setCellValue('F'.$rowCount,$darr[$c][5])->setCellValue('G'.$rowCount,$darr[$c][6])->setCellValue('H'.$rowCount,$darr[$c][7])->setCellValue('I'.$rowCount,$darr[$c][8])->setCellValue('J'.$rowCount,$darr[$c][9])->setCellValue('K'.$rowCount,$darr[$c][10])->setCellValue('L'.$rowCount,$darr[$c][11])->setCellValue('M'.$rowCount,$darr[$c][12])->setCellValue('N'.$rowCount,$darr[$c][13])->setCellValue('O'.$rowCount,$darr[$c][14])->setCellValue('P'.$rowCount,$darr[$c][15]);
						$net		= $darr[$c][15] - $darr[$c][18];
						$totalnet 	= $darr[$c][32] - $darr[$c][34];
						//$newsheet->setCellValue('Q'.$rowCount,$net)->setCellValue('R'.$rowCount,0)->setCellValue('S'.$rowCount,$darr[$c][17]);
						
						$activeJanTotal = $darr[$c][20];
						$activeFebTotal = $darr[$c][21];
						$activeMarTotal = $darr[$c][22];
						$activeAprTotal = $darr[$c][23];
						$activeMayTotal = $darr[$c][24];
						$activeJunTotal = $darr[$c][25];
						$activeJulTotal = $darr[$c][26];
						$activeAugTotal = $darr[$c][27];
						$activeSepTotal = $darr[$c][28];
						$activeOctTotal = $darr[$c][29];
						$activeNovTotal = $darr[$c][30];
						$activeDecTotal = $darr[$c][31]; 
						$activeTotalSales = $darr[$c][32]; 
						$activeNetTotal = $totalnet; 
						$activeTransferTotal = $darr[$c][33];
					}
					$totRow = $rowCount + 1;
					$newsheet->mergeCells('A'.$totRow.':C'.$totRow);
					
					$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),'font' => array('bold' => true));
					$newsheet->getStyle('A'.$totRow.':S'.$totRow)->applyFromArray($styleArray);
						unset($styleArray);   

					$newsheet->setCellValue('A'.$totRow,"TOTAL")->setCellValue('D'.$totRow,$activeJanTotal)->setCellValue('E'.$totRow,$activeFebTotal)->setCellValue('F'.$totRow,$activeMarTotal)->setCellValue('G'.$totRow,$activeAprTotal)->setCellValue('H'.$totRow,$activeMayTotal)->setCellValue('I'.$totRow,$activeJunTotal)->setCellValue('J'.$totRow,$activeJulTotal)->setCellValue('K'.$totRow,$activeAugTotal)->setCellValue('L'.$totRow,$activeSepTotal)->setCellValue('M'.$totRow,$activeOctTotal)->setCellValue('N'.$totRow,$activeNovTotal)->setCellValue('O'.$totRow,$activeDecTotal)->setCellValue('P'.$totRow,$activeTotalSales);
					//->setCellValue('Q'.$totRow,$activeNetTotal)->setCellValue('R'.$totRow,0)->setCellValue('S'.$totRow,$activeTransferTotal); 
				}
				$totRows = $rowCount;
				//CANCEL DETAILS
				$carr = getModelDetailsCancel($monthFrom,$monthTo,$filterSearch,$modelFilter,$isfilterdate);
				if(count($carr)){
				$cRow = $rowCount + 5;
				$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),'font' => array('bold' => true));
					$newsheet->getStyle('A'.$cRow.':P'.$cRow)->applyFromArray($styleArray);
						unset($styleArray);   
				$newsheet->setCellValue('A'.$cRow,"CANCELLED")->setCellValue('B'.$cRow,"BROKERS")->setCellValue('C'.$cRow,"YEAR")->setCellValue('D'.$cRow,"JAN")->setCellValue('E'.$cRow,"FEB")->setCellValue('F'.$cRow,"MAR")->setCellValue('G'.$cRow,"APR")->setCellValue('H'.$cRow,"MAY")->setCellValue('I'.$cRow,"JUN")->setCellValue('J'.$cRow,"JUL")->setCellValue('K'.$cRow,"AUG")->setCellValue('L'.$cRow,"SEP")->setCellValue('M'.$cRow,"OCT")->setCellValue('N'.$cRow,"NOV")->setCellValue('O'.$cRow,"DEC")->setCellValue('P'.$cRow,"TOTAL SALES");
				
					$cRows = $cRow + 1;
					for($d=0; $d<count($carr); $d++){ 
						$rowCounts = $cRows + $d;
						$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
						$newsheet->getStyle('A'.$rowCounts.':P'.$rowCounts)->applyFromArray($styleArray);
						unset($styleArray);   
						$newsheet->setCellValue('A'.$rowCounts,$carr[$d][19])->setCellValue('B'.$rowCounts,$carr[$d][2])->setCellValue('C'.$rowCounts,$carr[$d][16])->setCellValue('D'.$rowCounts,$carr[$d][3])->setCellValue('E'.$rowCounts,$carr[$d][4])->setCellValue('F'.$rowCounts,$carr[$d][5])->setCellValue('G'.$rowCounts,$carr[$d][6])->setCellValue('H'.$rowCounts,$carr[$d][7])->setCellValue('I'.$rowCounts,$carr[$d][8])->setCellValue('J'.$rowCounts,$carr[$d][9])->setCellValue('K'.$rowCounts,$carr[$d][10])->setCellValue('L'.$rowCounts,$carr[$d][11])->setCellValue('M'.$rowCounts,$carr[$d][12])->setCellValue('N'.$rowCounts,$carr[$d][13])->setCellValue('O'.$rowCounts,$carr[$d][14])->setCellValue('P'.$rowCounts,$carr[$d][15]);
  						
						$cancelJanTotal = $carr[$d][20];
						$cancelFebTotal = $carr[$d][21];
						$cancelMarTotal = $carr[$d][22];
						$cancelAprTotal = $carr[$d][23];
						$cancelMayTotal = $carr[$d][24];
						$cancelJunTotal = $carr[$d][25];
						$cancelJulTotal = $carr[$d][26];
						$cancelAugTotal = $carr[$d][27];
						$cancelSepTotal = $carr[$d][28];
						$cancelOctTotal = $carr[$d][29];
						$cancelNovTotal = $carr[$d][30];
						$cancelDecTotal = $carr[$d][31]; 
						$cancelTotalSales = $carr[$d][32];
					}
					$totRows = $cRows + 1;
					$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),'font' => array('bold' => true));
					$newsheet->getStyle('A'.$totRows.':P'.$totRows)->applyFromArray($styleArray);
						unset($styleArray);   
					$newsheet->mergeCells('A'.$totRows.':C'.$totRows);
					$newsheet->setCellValue('A'.$totRows,"TOTAL")->setCellValue('D'.$totRows,$cancelJanTotal)->setCellValue('E'.$totRows,$cancelFebTotal)->setCellValue('F'.$totRows,$cancelMarTotal)->setCellValue('G'.$totRows,$cancelAprTotal)->setCellValue('H'.$totRows,$cancelMayTotal)->setCellValue('I'.$totRows,$cancelJunTotal)->setCellValue('J'.$totRows,$cancelJulTotal)->setCellValue('K'.$totRows,$cancelAugTotal)->setCellValue('L'.$totRows,$cancelSepTotal)->setCellValue('M'.$totRows,$cancelOctTotal)->setCellValue('N'.$totRows,$cancelNovTotal)->setCellValue('O'.$totRows,$cancelDecTotal)->setCellValue('P'.$totRows,$cancelTotalSales); 
				}
				
				$totRowsFinal = $totRows + 3;
				$activeCancelJanTotal	= $darr[0][35];
				$activeCancelFebTotal	= $darr[0][36]; 
				$activeCancelMarTotal	= $darr[0][37]; 
				$activeCancelAprTotal	= $darr[0][38]; 
				$activeCancelMayTotal	= $darr[0][39]; 
				$activeCancelJunTotal	= $darr[0][40]; 
				$activeCancelJulTotal	= $darr[0][41]; 
				$activeCancelAugTotal	= $darr[0][42]; 
				$activeCancelSepTotal	= $darr[0][43]; 
				$activeCancelOctTotal	= $darr[0][44]; 
				$activeCancelNovTotal	= $darr[0][45]; 
				$activeCancelDecTotal	= $darr[0][46]; 
				$activeCancelTotalSales = $darr[0][47];
				$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),'font' => array('bold' => true));
					$newsheet->getStyle('A'.$totRowsFinal.':P'.$totRowsFinal)->applyFromArray($styleArray);
						unset($styleArray);   
				$newsheet->mergeCells('A'.$totRowsFinal.':C'.$totRowsFinal);
				$newsheet->setCellValue('A'.$totRowsFinal,"NET RESERVATION")->setCellValue('D'.$totRowsFinal,$activeCancelJanTotal)->setCellValue('E'.$totRowsFinal,$activeCancelFebTotal)->setCellValue('F'.$totRowsFinal,$activeCancelMarTotal)->setCellValue('G'.$totRowsFinal,$activeCancelAprTotal)->setCellValue('H'.$totRowsFinal,$activeCancelMayTotal)->setCellValue('I'.$totRowsFinal,$activeCancelJunTotal)->setCellValue('J'.$totRowsFinal,$activeCancelJulTotal)->setCellValue('K'.$totRowsFinal,$activeCancelAugTotal)->setCellValue('L'.$totRowsFinal,$activeCancelSepTotal)->setCellValue('M'.$totRowsFinal,$activeCancelOctTotal)->setCellValue('N'.$totRowsFinal,$activeCancelNovTotal)->setCellValue('O'.$totRowsFinal,$activeCancelDecTotal)->setCellValue('P'.$totRowsFinal,$activeCancelTotalSales)->setCellValue('Q'.$totRowsFinal,"");
			} 
			
			//add signature line
			$addLine	= $totRowsFinal + 5; 
			$addLine1	= $totRowsFinal + 6;  
			$styleArray = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),'font' => array('bold' => true)); 
			$newsheet->mergeCells('A'.$addLine.':B'.$addLine);
			$newsheet->getStyle('A'.$addLine.':B'.$addLine)->applyFromArray($styleArray);
			$newsheet->mergeCells('F'.$addLine.':J'.$addLine);
			$newsheet->getStyle('F'.$addLine.':J'.$addLine)->applyFromArray($styleArray);
			$newsheet->mergeCells('N'.$addLine.':Q'.$addLine);
			$newsheet->getStyle('N'.$addLine.':Q'.$addLine)->applyFromArray($styleArray); 
			$newsheet->setCellValue('N'.$addLine,"Ana Maria P. Lim");
			$newsheet->setCellValue('N'.$addLine1,"Managing Director");
		} 
  
 
$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 getModel($monthFrom,$monthTo,$filterSearch,$isfilterdate){ 
		$mecon	=	new condbClass(); 
		$sql	=	"SELECT DISTINCT(r.Model) as Model, r.Agent as Agent, Year(ReservationDate) as ryear, r.Broker as Broker, r.Id as Id, Count(case when month(ReservationDate)=1 then Id end) As January, Count(case when month(ReservationDate)=2 then Id end) As February, Count(case when month(ReservationDate)=3 then Id end) As March, Count(case when month(ReservationDate)=4 then Id end) As April, Count(case when month(ReservationDate)=5 then Id end) As May, Count(case when month(ReservationDate)=6 then Id end) As June, Count(case when month(ReservationDate)=7 then Id end) As July, Count(case when month(ReservationDate)=8 then Id end) As August, Count(case when month(ReservationDate)=9 then Id end) As September, Count(case when month(ReservationDate)=10 then Id end) As October, Count(case when month(ReservationDate)=11 then Id end) As November, Count(case when month(ReservationDate)=12 then Id end) As December, COUNT(*) AS YTD, r.Status, r.ReservationStatus as ReservationStatus  FROM reservation r WHERE r.Status='active' "; 

	if(!empty($filterSearch)) { 
		$arr = explode(" ",$filterSearch);  
		foreach($arr as $ind){
			$ind = str_replace(',','',$ind);
			$sql.=" AND ( YEAR(ReservationDate) LIKE '%".$ind."%' ";
			$sql.=" OR r.Model LIKE '%".$ind."%' ";
			$sql.=" OR (SELECT COUNT(*) FROM reservation) LIKE '%".$ind."%' ";
			$sql.=" OR r.Agent LIKE '%".$ind."%' ";
			$sql.=" OR r.ReservationStatus LIKE '%".$ind."%' ";
			$sql.=" OR r.Broker 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 Model ORDER BY Model ";  
	$mecon->query($sql);
	$data		= array();
	$main2		= $mecon->resultset();
	$counter 	= count($main2); 
	$arr1 		= array();
	$arr2		= array();  
	$arr3 		= array();
	for($a=0; $a<$counter; $a++){   
		$arr1[0]	= $a+1;
		$arr1[1]	= trim(html_entity_decode(stripslashes($main2[$a]['Model']))); 
		$arr2[] 	= $arr1;
	}
	$arr3['datam'] = $arr2; 

	return $arr3['datam'];
}


function getModelDetails($monthFrom,$monthTo,$filterSearch,$modelFilter,$isfilterdate){
		$filterQuery = "";
		$filterQuery2 = "";
		//FILTER FOR INNER QUERY
		if(!empty($filterSearch)) { 
			$arr = explode(" ",$filterSearch);  
			foreach($arr as $ind){
				$ind = str_replace(',','',$ind);
				$filterQuery.=" AND ( YEAR(ReservationDate) LIKE '%".$ind."%' ";
				$filterQuery.=" OR Model LIKE '%".$ind."%' ";
				$filterQuery.=" OR (SELECT COUNT(*) FROM reservation) LIKE '%".$ind."%' ";
				$filterQuery.=" OR Agent LIKE '%".$ind."%' ";
				$filterQuery.=" OR ReservationStatus LIKE '%".$ind."%' ";
				$filterQuery.=" OR Broker LIKE '%".$ind."%' ) ";
			}
		}

		if($isfilterdate=="yes"){
			if(!empty($monthFrom) && !empty($monthTo)){
				$filterQuery .= " AND ReservationDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
			}else if( !empty($monthFrom)){
				$filterQuery .= " AND ReservationDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
			}else if( !empty($monthTo)){
				$filterQuery .= " AND ReservationDate <= ('".date('Y-m-d', strtotime($monthTo))."') ";
			}
		}
		
		//FILTER FOR MAIN QUERY
		if(!empty($filterSearch)) { 
			$arr = explode(" ",$filterSearch);  
			foreach($arr as $ind){
				$ind = str_replace(',','',$ind);
				$filterQuery2.=" AND ( YEAR(ReservationDate) LIKE '%".$ind."%' ";
				$filterQuery2.=" OR r.Model LIKE '%".$ind."%' ";
				$filterQuery2.=" OR (SELECT COUNT(*) FROM reservation) LIKE '%".$ind."%' ";
				$filterQuery2.=" OR r.Agent LIKE '%".$ind."%' ";
				$filterQuery2.=" OR r.ReservationStatus LIKE '%".$ind."%' ";
				$filterQuery2.=" OR r.Broker LIKE '%".htmlentities($ind)."%' ) ";
			}
		}

		if($isfilterdate=="yes"){
			if(!empty($monthFrom) && !empty($monthTo)){
				$filterQuery2 .= " AND r.ReservationDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
			}else if( !empty($monthFrom)){
				$filterQuery2 .= " AND r.ReservationDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
			}else if( !empty($monthTo)){
				$filterQuery2 .= " AND r.ReservationDate <= ('".date('Y-m-d', strtotime($monthTo))."') ";
			}
		}
		
		$mecon	=	new condbClass(); 
		$sql	=	"SELECT DISTINCT(r.Model) as Model, Year(ReservationDate) as ryear, r.Agent as Agent, r.Broker as Broker, r.Id as Id, Count(case when month(ReservationDate)=1 then Id end) As January, Count(case when month(ReservationDate)=2 then Id end) As February, Count(case when month(ReservationDate)=3 then Id end) As March, Count(case when month(ReservationDate)=4 then Id end) As April, Count(case when month(ReservationDate)=5 then Id end) As May, Count(case when month(ReservationDate)=6 then Id end) As June, Count(case when month(ReservationDate)=7 then Id end) As July, Count(case when month(ReservationDate)=8 then Id end) As August, Count(case when month(ReservationDate)=9 then Id end) As September, Count(case when month(ReservationDate)=10 then Id end) As October, Count(case when month(ReservationDate)=11 then Id end) As November, Count(case when month(ReservationDate)=12 then Id end) As December, 
		(SELECT COUNT(*) FROM reservation WHERE Broker=r.Broker AND Agent=r.Agent AND Year(ReservationDate)=Year(r.ReservationDate) AND Model='".$modelFilter."' ".$filterQuery.") AS YTD, (SELECT COUNT(*) FROM reservation WHERE Broker=r.Broker AND Agent=r.Agent AND Year(ReservationDate)=Year(r.ReservationDate) AND  Model='".$modelFilter."' AND ReservationStatus='transferred' ".$filterQuery.") as transfer,
		(SELECT COUNT(*) FROM reservation WHERE Broker=r.Broker AND Agent=r.Agent AND Year(ReservationDate)=Year(r.ReservationDate) AND Model='".$modelFilter."' AND ReservationStatus='cancelled' ".$filterQuery.") as cancel, 
		r.Status, r.ReservationStatus as ReservationStatus, 
		(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=1 AND Model='".$modelFilter."' ".$filterQuery.") as total_jan,
		(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=2 AND Model='".$modelFilter."' ".$filterQuery.") as total_feb,

(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=3 AND Model='".$modelFilter."' ".$filterQuery.") as total_mar,

(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=4 AND Model='".$modelFilter."' ".$filterQuery.") as total_apr,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=5 AND Model='".$modelFilter."' ".$filterQuery.") as total_may,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=6 AND Model='".$modelFilter."' ".$filterQuery.") as total_jun,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=7 AND Model='".$modelFilter."' ".$filterQuery.") as total_jul,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=8 AND Model='".$modelFilter."' ".$filterQuery.") as total_aug,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=9 AND Model='".$modelFilter."' ".$filterQuery.") as total_sep,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=10 AND Model='".$modelFilter."' ".$filterQuery.") as total_oct,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=11 AND Model='".$modelFilter."' ".$filterQuery.") as total_nov,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=12 AND Model='".$modelFilter."' ".$filterQuery.") as total_dec,
(SELECT COUNT(*) FROM reservation WHERE Model='".$modelFilter."' ".$filterQuery.") AS total_ytd,
(SELECT COUNT(*) FROM reservation WHERE Model='".$modelFilter."' AND ReservationStatus='transferred' ".$filterQuery.") as total_transfer,
(SELECT COUNT(*) FROM reservation WHERE Model='".$modelFilter."' AND ReservationStatus='cancelled' ".$filterQuery.") as total_cancel,
((SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=1  AND Model='".$modelFilter."' ".$filterQuery.") - (SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=1  AND ReservationStatus='cancelled'  AND Model='".$modelFilter."' ".$filterQuery.")) as net_total_jan, 
((SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=2  AND Model='".$modelFilter."' ".$filterQuery.") - (SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=2  AND ReservationStatus='cancelled'  AND Model='".$modelFilter."' ".$filterQuery.")) as net_total_feb,
((SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=3  AND Model='".$modelFilter."' ".$filterQuery.") - (SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=3 AND ReservationStatus='cancelled'  AND Model='".$modelFilter."' ".$filterQuery.")) as net_total_mar,
((SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=4  AND Model='".$modelFilter."' ".$filterQuery.") - (SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=4 AND ReservationStatus='cancelled'  AND Model='".$modelFilter."' ".$filterQuery.")) as net_total_apr,
((SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=5  AND Model='".$modelFilter."' ".$filterQuery.") - (SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=5 AND ReservationStatus='cancelled'  AND Model='".$modelFilter."' ".$filterQuery.")) as net_total_may,
((SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=6  AND Model='".$modelFilter."' ".$filterQuery.") - (SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=6 AND ReservationStatus='cancelled'  AND Model='".$modelFilter."' ".$filterQuery.")) as net_total_jun,
((SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=7  AND Model='".$modelFilter."' ".$filterQuery.") - (SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=7 AND ReservationStatus='cancelled'  AND Model='".$modelFilter."' ".$filterQuery.")) as net_total_jul,
((SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=8  AND Model='".$modelFilter."' ".$filterQuery.") - (SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=8 AND ReservationStatus='cancelled'  AND Model='".$modelFilter."' ".$filterQuery.")) as net_total_aug,
((SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=9  AND Model='".$modelFilter."' ".$filterQuery.") - (SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=9 AND ReservationStatus='cancelled'  AND Model='".$modelFilter."' ".$filterQuery.")) as net_total_sep,
((SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=10  AND Model='".$modelFilter."' ".$filterQuery.") - (SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=10 AND ReservationStatus='cancelled'  AND Model='".$modelFilter."' ".$filterQuery.")) as net_total_oct,
((SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=11  AND Model='".$modelFilter."' ".$filterQuery.") - (SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=11 AND ReservationStatus='cancelled'  AND Model='".$modelFilter."' ".$filterQuery.")) as net_total_nov,
((SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=12  AND Model='".$modelFilter."' ".$filterQuery.") - (SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=12 AND ReservationStatus='cancelled'  AND Model='".$modelFilter."' ".$filterQuery.")) as net_total_dec,
((SELECT COUNT(*) FROM reservation WHERE Model='".$modelFilter."' ".$filterQuery.") - (SELECT COUNT(*) FROM reservation WHERE Model='".$modelFilter."' AND ReservationStatus='cancelled' ".$filterQuery.")) as net_total_sales 
FROM reservation r WHERE r.Status='active' AND r.Model='".$modelFilter."' ".$filterQuery2.""; 
 
	$sql .= " GROUP BY Year(r.ReservationDate), r. Model, r.Agent, r.Broker,r.ReservationStatus ORDER BY Broker, YEAR(r.ReservationDate)";  
	$mecon->query($sql);
	$data		= array();
	$main		= $mecon->resultset();
	$counter 	= count($main); 
	$arr1 		= array();
	$arr2		= array();  
	$arr3 		= array();
	for($a=0; $a<$counter; $a++){   
		$arr1[0]	= $a+1;
		$arr1[1]	= trim(html_entity_decode(stripslashes($main[$a]['Model'])));  
		$arr1[2]	= trim(html_entity_decode(stripslashes($main[$a]['Broker'])));
		$arr1[3]	= trim(html_entity_decode(stripslashes($main[$a]['January']))); 
		$arr1[4]	= trim(html_entity_decode(stripslashes($main[$a]['February']))); 
		$arr1[5]	= trim(html_entity_decode(stripslashes($main[$a]['March']))); 
		$arr1[6]	= trim(html_entity_decode(stripslashes($main[$a]['April']))); 
		$arr1[7]	= trim(html_entity_decode(stripslashes($main[$a]['May']))); 
		$arr1[8]	= trim(html_entity_decode(stripslashes($main[$a]['June']))); 
		$arr1[9]	= trim(html_entity_decode(stripslashes($main[$a]['July']))); 
		$arr1[10]	= trim(html_entity_decode(stripslashes($main[$a]['August']))); 
		$arr1[11]	= trim(html_entity_decode(stripslashes($main[$a]['September']))); 
		$arr1[12]	= trim(html_entity_decode(stripslashes($main[$a]['October']))); 
		$arr1[13]	= trim(html_entity_decode(stripslashes($main[$a]['November']))); 
		$arr1[14]	= trim(html_entity_decode(stripslashes($main[$a]['December'])));
		$arr1[15]	= trim(html_entity_decode(stripslashes($main[$a]['YTD'])));
		$arr1[16]	= trim(html_entity_decode(stripslashes($main[$a]['ryear'])));
		$arr1[17]	= trim(html_entity_decode(stripslashes($main[$a]['transfer'])));
		$arr1[18]	= trim(html_entity_decode(stripslashes($main[$a]['cancel'])));
		$arr1[19]	= trim(html_entity_decode(stripslashes($main[$a]['Agent'])));
		$arr1[20]	= trim(html_entity_decode(stripslashes($main[$a]['total_jan'])));
		$arr1[21]	= trim(html_entity_decode(stripslashes($main[$a]['total_feb'])));
		$arr1[22]	= trim(html_entity_decode(stripslashes($main[$a]['total_mar'])));
		$arr1[23]	= trim(html_entity_decode(stripslashes($main[$a]['total_apr'])));
		$arr1[24]	= trim(html_entity_decode(stripslashes($main[$a]['total_may'])));
		$arr1[25]	= trim(html_entity_decode(stripslashes($main[$a]['total_jun'])));
		$arr1[26]	= trim(html_entity_decode(stripslashes($main[$a]['total_jul'])));
		$arr1[27]	= trim(html_entity_decode(stripslashes($main[$a]['total_aug'])));
		$arr1[28]	= trim(html_entity_decode(stripslashes($main[$a]['total_sep'])));
		$arr1[29]	= trim(html_entity_decode(stripslashes($main[$a]['total_oct'])));
		$arr1[30]	= trim(html_entity_decode(stripslashes($main[$a]['total_nov'])));
		$arr1[31]	= trim(html_entity_decode(stripslashes($main[$a]['total_dec'])));
		$arr1[32]	= trim(html_entity_decode(stripslashes($main[$a]['total_ytd'])));
		$arr1[33]	= trim(html_entity_decode(stripslashes($main[$a]['total_transfer'])));
		$arr1[34]	= trim(html_entity_decode(stripslashes($main[$a]['total_cancel'])));
		$arr1[35]	= trim(html_entity_decode(stripslashes($main[$a]['net_total_jan'])));
		$arr1[36]	= trim(html_entity_decode(stripslashes($main[$a]['net_total_feb'])));
		$arr1[37]	= trim(html_entity_decode(stripslashes($main[$a]['net_total_mar'])));
		$arr1[38]	= trim(html_entity_decode(stripslashes($main[$a]['net_total_apr'])));
		$arr1[39]	= trim(html_entity_decode(stripslashes($main[$a]['net_total_may'])));
		$arr1[40]	= trim(html_entity_decode(stripslashes($main[$a]['net_total_jun'])));
		$arr1[41]	= trim(html_entity_decode(stripslashes($main[$a]['net_total_jul'])));
		$arr1[42]	= trim(html_entity_decode(stripslashes($main[$a]['net_total_aug'])));
		$arr1[43]	= trim(html_entity_decode(stripslashes($main[$a]['net_total_sep'])));
		$arr1[44]	= trim(html_entity_decode(stripslashes($main[$a]['net_total_oct'])));
		$arr1[45]	= trim(html_entity_decode(stripslashes($main[$a]['net_total_nov'])));
		$arr1[46]	= trim(html_entity_decode(stripslashes($main[$a]['net_total_dec'])));
		$arr1[47]	= trim(html_entity_decode(stripslashes($main[$a]['net_total_sales'])));
		$arr2[] 	= $arr1;
	}
	$arr3['datam'] = $arr2; 

	return $arr3['datam'];
}

function getModelDetailsCancel($monthFrom,$monthTo,$filterSearch,$modelFilter,$isfilterdate){
		
		$filterQuery = "";
		$filterQuery2 = "";
		//FILTER FOR INNER QUERY
		if(!empty($filterSearch)) { 
			$arr = explode(" ",$filterSearch);  
			foreach($arr as $ind){
				$ind = str_replace(',','',$ind);
				$filterQuery.=" AND (YEAR(ReservationDate) LIKE '%".$ind."%' ";
				$filterQuery.=" OR Model LIKE '%".$ind."%' ";
				$filterQuery.=" OR (SELECT COUNT(*) FROM reservation) LIKE '%".$ind."%' ";
				$filterQuery.=" OR Agent LIKE '%".$ind."%' ";
				$filterQuery.=" OR ReservationStatus LIKE '%".$ind."%' ";
				$filterQuery.=" OR Broker LIKE '%".$ind."%' ) ";
			}
		}

		if($isfilterdate=="yes"){
				if(!empty($monthFrom) && !empty($monthTo)){
					$filterQuery .= " AND ReservationDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
				}else if( !empty($monthFrom)){
					$filterQuery .= " AND ReservationDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
				}else if( !empty($monthTo)){
					$filterQuery .= " AND ReservationDate <= ('".date('Y-m-d', strtotime($monthTo))."') ";
				}
		}
		
		//FILTER FOR MAIN QUERY
		if(!empty($filterSearch)) { 
			$arr = explode(" ",$filterSearch);  
			foreach($arr as $ind){
				$ind = str_replace(',','',$ind);
				$filterQuery2.=" AND ( YEAR(ReservationDate) LIKE '%".$ind."%' ";
				$filterQuery2.=" OR r.Model LIKE '%".$ind."%' ";
				$filterQuery2.=" OR (SELECT COUNT(*) FROM reservation) LIKE '%".$ind."%' ";
				$filterQuery2.=" OR r.Agent LIKE '%".$ind."%' ";
				$filterQuery2.=" OR r.ReservationStatus LIKE '%".$ind."%' ";
				$filterQuery2.=" OR r.Broker LIKE '%".$ind."%' ) ";
			}
		}

		if($isfilterdate=="yes"){
				if(!empty($monthFrom) && !empty($monthTo)){
					$filterQuery2 .= " AND r.ReservationDate BETWEEN ('".date('Y-m-d', strtotime($monthFrom))."') AND ('".date('Y-m-d', strtotime($monthTo))."')";
				}else if( !empty($monthFrom)){
					$filterQuery2 .= " AND r.ReservationDate >= ('".date('Y-m-d', strtotime($monthFrom))."') ";
				}else if( !empty($monthTo)){
					$filterQuery2 .= " AND r.ReservationDate <= ('".date('Y-m-d', strtotime($monthTo))."') ";
				}
		}
		
		$mecon	=	new condbClass(); 
		$sql	=	"SELECT DISTINCT(r.Model) as Model, Year(ReservationDate) as ryear, r.Agent as Agent, r.Broker as Broker, r.Id as Id, Count(case when month(ReservationDate)=1 then Id end) As January, Count(case when month(ReservationDate)=2 then Id end) As February, Count(case when month(ReservationDate)=3 then Id end) As March, Count(case when month(ReservationDate)=4 then Id end) As April, Count(case when month(ReservationDate)=5 then Id end) As May, Count(case when month(ReservationDate)=6 then Id end) As June, Count(case when month(ReservationDate)=7 then Id end) As July, Count(case when month(ReservationDate)=8 then Id end) As August, Count(case when month(ReservationDate)=9 then Id end) As September, Count(case when month(ReservationDate)=10 then Id end) As October, Count(case when month(ReservationDate)=11 then Id end) As November, Count(case when month(ReservationDate)=12 then Id end) As December, 
		(SELECT COUNT(*) FROM reservation WHERE Broker=r.Broker AND Agent=r.Agent AND Year(ReservationDate)=Year(r.ReservationDate) AND Model='".$modelFilter."' ".$filterQuery.") AS YTD, 
		(SELECT COUNT(*) FROM reservation WHERE Broker=r.Broker AND Agent=r.Agent AND Year(ReservationDate)=Year(r.ReservationDate) AND Model='".$modelFilter."' AND ReservationStatus='transferred'  ".$filterQuery.") as transfer,
		(SELECT COUNT(*) FROM reservation WHERE Broker=r.Broker AND Agent=r.Agent AND Year(ReservationDate)=Year(r.ReservationDate) AND Model='".$modelFilter."' AND ReservationStatus='cancelled'  ".$filterQuery.") as cancel, 
		r.Status, r.ReservationStatus as ReservationStatus, 
		(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=1  AND r.ReservationStatus='cancelled'  AND Model='".$modelFilter."'  ".$filterQuery.") as total_jan,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=2  AND ReservationStatus='cancelled'  AND Model='".$modelFilter."'  ".$filterQuery.") as total_feb,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=3  AND ReservationStatus='cancelled'  AND Model='".$modelFilter."'  ".$filterQuery.") as total_mar,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=4  AND ReservationStatus='cancelled'  AND Model='".$modelFilter."'  ".$filterQuery.") as total_apr,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=5  AND ReservationStatus='cancelled'  AND Model='".$modelFilter."'  ".$filterQuery.") as total_may,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=6  AND ReservationStatus='cancelled'  AND Model='".$modelFilter."'  ".$filterQuery.") as total_jun,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=7  AND ReservationStatus='cancelled'  AND Model='".$modelFilter."'  ".$filterQuery.") as total_jul,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=8  AND ReservationStatus='cancelled'  AND Model='".$modelFilter."'  ".$filterQuery.") as total_aug,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=9  AND ReservationStatus='cancelled'  AND Model='".$modelFilter."'  ".$filterQuery.") as total_sep,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=10  AND ReservationStatus='cancelled'  AND Model='".$modelFilter."'  ".$filterQuery.") as total_oct,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=11  AND ReservationStatus='cancelled'  AND Model='".$modelFilter."'  ".$filterQuery.") as total_nov,
(SELECT COUNT(*) FROM reservation WHERE month(ReservationDate)=12  AND ReservationStatus='cancelled' AND Model='".$modelFilter."'  ".$filterQuery.") as total_dec,
(SELECT COUNT(*) FROM reservation WHERE Model='".$modelFilter."'  AND ReservationStatus='cancelled'  ".$filterQuery.") AS total_ytd,
(SELECT COUNT(*) FROM reservation WHERE Model='".$modelFilter."' AND ReservationStatus='transferred'  ".$filterQuery.") as total_transfer,
(SELECT COUNT(*) FROM reservation WHERE Model='".$modelFilter."' AND ReservationStatus='cancelled'  ".$filterQuery.") as total_cancel  FROM reservation r WHERE r.Status='active' AND r.ReservationStatus='cancelled' AND r.Model='".$modelFilter."'  ".$filterQuery2." "; 
 
	$sql .= " GROUP BY Year(r.ReservationDate), r. Model, r.Agent, r.Broker,r.ReservationStatus ORDER BY Broker, YEAR(r.ReservationDate)";  
	$mecon->query($sql);
	$data		= array();
	$main		= $mecon->resultset();
	$counter 	= count($main); 
	$arr1 		= array();
	$arr2		= array();  
	$arr3 		= array();
	for($a=0; $a<$counter; $a++){   
		$arr1[0]	= $a+1;
		$arr1[1]	= trim(html_entity_decode(stripslashes($main[$a]['Model'])));  
		$arr1[2]	= trim(html_entity_decode(stripslashes($main[$a]['Broker'])));
		$arr1[3]	= trim(html_entity_decode(stripslashes($main[$a]['January']))); 
		$arr1[4]	= trim(html_entity_decode(stripslashes($main[$a]['February']))); 
		$arr1[5]	= trim(html_entity_decode(stripslashes($main[$a]['March']))); 
		$arr1[6]	= trim(html_entity_decode(stripslashes($main[$a]['April']))); 
		$arr1[7]	= trim(html_entity_decode(stripslashes($main[$a]['May']))); 
		$arr1[8]	= trim(html_entity_decode(stripslashes($main[$a]['June']))); 
		$arr1[9]	= trim(html_entity_decode(stripslashes($main[$a]['July']))); 
		$arr1[10]	= trim(html_entity_decode(stripslashes($main[$a]['August']))); 
		$arr1[11]	= trim(html_entity_decode(stripslashes($main[$a]['September']))); 
		$arr1[12]	= trim(html_entity_decode(stripslashes($main[$a]['October']))); 
		$arr1[13]	= trim(html_entity_decode(stripslashes($main[$a]['November']))); 
		$arr1[14]	= trim(html_entity_decode(stripslashes($main[$a]['December'])));
		$arr1[15]	= trim(html_entity_decode(stripslashes($main[$a]['YTD'])));
		$arr1[16]	= trim(html_entity_decode(stripslashes($main[$a]['ryear'])));
		$arr1[17]	= trim(html_entity_decode(stripslashes($main[$a]['transfer'])));
		$arr1[18]	= trim(html_entity_decode(stripslashes($main[$a]['cancel'])));
		$arr1[19]	= trim(html_entity_decode(stripslashes($main[$a]['Agent'])));
		$arr1[20]	= trim(html_entity_decode(stripslashes($main[$a]['total_jan'])));
		$arr1[21]	= trim(html_entity_decode(stripslashes($main[$a]['total_feb'])));
		$arr1[22]	= trim(html_entity_decode(stripslashes($main[$a]['total_mar'])));
		$arr1[23]	= trim(html_entity_decode(stripslashes($main[$a]['total_apr'])));
		$arr1[24]	= trim(html_entity_decode(stripslashes($main[$a]['total_may'])));
		$arr1[25]	= trim(html_entity_decode(stripslashes($main[$a]['total_jun'])));
		$arr1[26]	= trim(html_entity_decode(stripslashes($main[$a]['total_jul'])));
		$arr1[27]	= trim(html_entity_decode(stripslashes($main[$a]['total_aug'])));
		$arr1[28]	= trim(html_entity_decode(stripslashes($main[$a]['total_sep'])));
		$arr1[29]	= trim(html_entity_decode(stripslashes($main[$a]['total_oct'])));
		$arr1[30]	= trim(html_entity_decode(stripslashes($main[$a]['total_nov'])));
		$arr1[31]	= trim(html_entity_decode(stripslashes($main[$a]['total_dec'])));
		$arr1[32]	= trim(html_entity_decode(stripslashes($main[$a]['total_ytd'])));
		$arr1[33]	= trim(html_entity_decode(stripslashes($main[$a]['total_transfer'])));
		$arr1[34]	= trim(html_entity_decode(stripslashes($main[$a]['total_cancel'])));
		$arr2[] 	= $arr1;
	}
	$arr3['datam'] = $arr2; 

	return $arr3['datam'];
}