<?php
date_default_timezone_set('America/Guayaquil');
require '../Conexion/class.sql.inc.php';
$db = new EyeMySQLAdap();


require_once('../PDFs/Lib/dompdf_config.inc.php');

session_start();

//$projectID = $_POST['projID'];
$UserName = $_SESSION['UserName'];
$Email	= strtolower( $_SESSION['Email']);
try {
   
	//if (true) 
	if (isset($_POST['Opcion'])) 
	{
		$varfil= $_POST['Opcion']; 

		switch ($varfil)
		{
			case 'SOL':{
					$MzID = $_POST['MzID'];
					
					$sqlR = $sql = "SELECT H.HouseID, P.ProjectID as ProjectID, P.Description as DescriptionProject, E.EtapaID as EtapaID, E.Description as DescriptionEtapa, M.MzID as MzID, M.Description as DescriptionMz, T.HouseSubTypeID as HouseSubTypeID, T.Description as DescriptionHouseSubType, H.Solar, H.SpaceMeter, H.BuildMeter, H.Price, H.Location, S.StatusHouseID as StatusHouseID, S.Description as DescriptionStatusHouse, H.Lindero1, H.Lindero2, H.Lindero3, H.Lindero4, U1.UserName as CreateUserID, H.CreateDate, U2.UserName as UpdateUserID, H.UpdateDate, H.Corner, H.GreenArea, H.WayStreet, H.AditionalCorner, H.AditionalGreenArea, H.AditionalWayStreet FROM project P, etapa E, mz M, housesubtype T, statushouse S, house H, user U1, user U2 WHERE H.ProjectID = P.ProjectID AND H.EtapaID = E.EtapaID AND H.MzID = M.MzID AND U1.UserID = H.CreateUserID AND H.HouseSubTypeID = T.HouseSubTypeID AND U2.UserID = H.UpdateUserID AND H.Status = S.StatusHouseID AND H.MzID =".$MzID;
					$resultR = $db->query($sqlR);					
					$row = mysql_num_rows($resultR);
					//Generar las lineas del plan de pagos
					
					$___STRLINES___ ="";										
					while($objR = mysql_fetch_object($resultR))
					{    				
						$HouseID = $objR->HouseID;
						$Proyecto = $objR->DescriptionProject;
						$Manzana = $objR->DescriptionMz;
						$Etapa = $objR->DescriptionEtapa;
						$Solar = $objR->Solar;
						$Estado = $objR->DescriptionStatusHouse;	
						$___STRLINES___=$___STRLINES___ . "<tr '> " . 
							"<td width=width=10%'><p class=MsoNormal style='text-align:center;line-height:normal'>" . $HouseID. "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:center;line-height:normal'>" . $Proyecto. "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $Etapa. "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $Manzana . "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $Solar. "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $Estado. "</p></td>" .
							
							"</tr>";
						
					}					
			
					$___FECHA___				= date("d/M/Y H:i:s");
					
					$strHtml= file_get_contents('../PDFs/Source/Templates/Reports/MZSolaresLibres.htm');
					$arr=array(
						"___STRLINES___"=>$___STRLINES___,
						"___FECHA___"=>$___FECHA___		
						);
					
					$count = count($arr);
					foreach($arr as $key=> $value){
						$strHtml = str_replace($key, $value, $strHtml);
					}
					/////////////////////////////////////////////////////////////
					$strFilename = '../PDFs/Source/Generated/'. 'MZSolaresLibres_'. uniqid() ;					
					$archivo=fopen($strFilename. '.html','w') ;
					fputs($archivo,  $strHtml);
					fclose($archivo);
					$oldDir = getcwd();
					chdir('../PDFs/Source/Templates/Reports/');
					$dompdf = new DOMPDF();
					$dompdf->set_paper('A4');
					$dompdf->load_html($strHtml);
					$dompdf->render();
					$strPDF = $dompdf->output();
					chdir($oldDir);
					$strFilename = $strFilename .'.pdf';
					$archivo=fopen($strFilename,'w') ;
					fputs($archivo,  $strPDF);
					fclose($archivo);
				
				}
				break;
			case'ReporteVentasDiariasConsolidado':{
					
					$FechaInicio = (isset($_POST['FechaInicio']) ? $_POST['FechaInicio'] : '');
					$FechaInicioDia = (isset($_POST['FechaInicioDia']) ? $_POST['FechaInicioDia'] : '');
					$FechaFin = (isset($_POST['FechaFin']) ? $_POST['FechaFin'] : '');
					$___FECHA___				= date("d/M/Y H:i:s");
					//Obtenemos el total por día de preventa
					$sqlDP = " select count(*) as Cant, sum(Reserve) as Ammount
								FROM sell as S
								WHERE   S.StatusSellID=3 AND 
										S.DateFiltroPreVenta>='" .  $FechaInicioDia."'" . " AND 
										S.DateFiltroPreVenta<='" . $FechaFin ."'";
					$resultDP = $db->query($sqlDP);
					$objDP = mysql_fetch_object($resultDP);
					$___CLIENTESPREVENTADIACOUNT___=$objDP->Cant;
					$___CLIENTESPREVENTADIAAMMOUNT___=number_format ($objDP->Ammount,2);
					
					//Obtenemos el total por día de venta
					$sqlDV = " select count(*) as Cant, sum(Reserve) as Ammount
								FROM sell as S
								WHERE   S.StatusSellID=4 AND 
										S.DateEmiInscription>='" .  $FechaInicioDia."'" . " AND 
										S.DateEmiInscription<='" . $FechaFin ."'";
					
					$resultDV = $db->query($sqlDV);
					$objDV = mysql_fetch_object($resultDV);
					$___CLIENTESVENTADIACOUNT___=$objDV->Cant;
					$___CLIENTESVENTADIAAMMOUNT___=number_format ($objDV->Ammount,2);
					
					
					//Obtenemos el total de preventa
					$sqlTP = " select count(*) as Cant, sum(Reserve) as Ammount
								FROM sell as S
								WHERE   S.StatusSellID=3 AND 
										S.DateFiltroPreVenta>='" .  $FechaInicio."'" . " AND 
										S.DateFiltroPreVenta<='" . $FechaFin ."'";
					$resultTP = $db->query($sqlTP);
					$objTP = mysql_fetch_object($resultTP);
					$___CLIENTESPREVENTACOUNT___=$objTP->Cant;
					$___CLIENTESPREVENTAAMMOUNT___=number_format ($objTP->Ammount,2);
					
					//Obtenemos el total de venta
					$sqlTV = " select count(*) as Cant, sum(Reserve) as Ammount
								FROM sell as S
								WHERE   S.StatusSellID=4 AND 
										S.DateEmiInscription>='" .  $FechaInicio."'" . " AND 
										S.DateEmiInscription<='" . $FechaFin ."'";					
										
					$resultTV = $db->query($sqlTV);
					$objTV = mysql_fetch_object($resultTV);
					$___CLIENTESVENTACOUNT___=$objTV->Cant;
					$___CLIENTESVENTAAMMOUNT___=number_format ($objTV->Ammount,2);
					
					$___FECHAINICIO___ = date_format( new DateTime($FechaInicio),'d-m-Y');
					$___FECHAFIN___ = date_format( new DateTime($FechaFin),'d-m-Y');
					
					$___CLIENTESVENTAPREVENTACOUNT___ = $___CLIENTESPREVENTACOUNT___ + $___CLIENTESVENTACOUNT___ ;
					$___CLIENTESVENTAPREVENTAAMMOUNT___ =number_format ( $objTP->Ammount + $objTV->Ammount,2);
					
					$strHtml= file_get_contents('../PDFs/Source/Templates/Reports/ReporteVentasConsolidado.htm');
					$arr=array(
						"___FECHA___"=>$___FECHA___,
						"___CLIENTESPREVENTADIACOUNT___"=>$___CLIENTESPREVENTADIACOUNT___,
						"___CLIENTESPREVENTADIAAMMOUNT___"=>$___CLIENTESPREVENTADIAAMMOUNT___,
						"___CLIENTESVENTADIACOUNT___"=>$___CLIENTESVENTADIACOUNT___,
						"___CLIENTESVENTADIAAMMOUNT___"=>$___CLIENTESVENTADIAAMMOUNT___,
						
						"___CLIENTESPREVENTACOUNT___"=>$___CLIENTESPREVENTACOUNT___,
						"___CLIENTESPREVENTAAMMOUNT___"=>$___CLIENTESPREVENTAAMMOUNT___,
						"___CLIENTESVENTACOUNT___"=>$___CLIENTESVENTACOUNT___,
						"___CLIENTESVENTAAMMOUNT___"=>$___CLIENTESVENTAAMMOUNT___,	
						
						"___CLIENTESVENTAPREVENTACOUNT___"=>$___CLIENTESVENTAPREVENTACOUNT___,
						"___CLIENTESVENTAPREVENTAAMMOUNT___"=>$___CLIENTESVENTAPREVENTAAMMOUNT___,
						"___FECHAINICIO___"=>$___FECHAINICIO___,
						"___FECHAFIN___"=>$___FECHAFIN___,																		
						"___USERNAME___"=>$UserName
						);
					
					$count = count($arr);
					foreach($arr as $key=> $value){
						$strHtml = str_replace($key, $value, $strHtml);
					}
					/////////////////////////////////////////////////////////////
					$strFilename = '../PDFs/Source/Generated/'. 'ReporteVentasConsolidado_'. uniqid() .'.htm' ;					
					$archivo=fopen($strFilename,'w') ;
					fputs($archivo,  $strHtml);
					fclose($archivo);					
				}
				break;
			case 'ReportePostulantes':{
					$loadCall = ($_POST['loadCall']!=''? $_POST['loadCall']:"false");
					if($loadCall=="false"){
						$IdentificacionFilsc = $_SESSION['IdentificacionFilsc'] ;
						$NombreFilsc  = $_SESSION['NombreFilsc'] ;
						$AsesorFilsc  = $_SESSION['AsesorFilsc'] ;
						$EstadoFilsc  = $_SESSION['EstadoFilsc'];
						$AhorroFilsc  = $_SESSION['AhorroFilsc'];
						$FechaInicio =  $_SESSION['FechaInicio']; 
						$FechaFin =  $_SESSION['FechaFin'];
						$Filtro =  $_SESSION['Filtro'];
					}
					else{
						$IdentificacionFilsc = (isset($_POST['IdentificacionFilsc']) ? $_POST['IdentificacionFilsc']  :  '');
						$NombreFilsc  = (isset($_POST['NombreFilsc']) ? $_POST['NombreFilsc']  : '');
						$AsesorFilsc  = (isset($_POST['AsesorFilsc']) ? $_POST['AsesorFilsc']  : '');
						$EstadoFilsc = (isset($_POST['EstadoFilsc']) ? $_POST['EstadoFilsc'] : '');
						$AhorroFilsc = (isset($_POST['AhorroFilsc']) ? $_POST['AhorroFilsc'] : '');
						
						$FechaInicio = (isset($_POST['FechaInicio']) ? $_POST['FechaInicio'] : '');
						$FechaFin = (isset($_POST['FechaFin']) ? $_POST['FechaFin'] : '');
						$Filtro = (isset($_POST['Filtro']) ? $_POST['Filtro'] : 'false');						
						$_SESSION['IdentificacionFilsc'] = $IdentificacionFilsc;
						$_SESSION['NombreFilsc'] = $NombreFilsc;
						$_SESSION['AsesorFilsc'] = $AsesorFilsc;
						$_SESSION['EstadoFilsc'] = $EstadoFilsc;
						$_SESSION['AhorroFilsc'] = $AhorroFilsc;
						$_SESSION['FechaInicio'] = $FechaInicio; 
						$_SESSION['FechaFin'] = $FechaFin;
						$_SESSION['Filtro'] = $Filtro;
					}
					
					$strID = ($IdentificacionFilsc!=''? " P.IDNumber LIKE '%" . $IdentificacionFilsc  . "%'" : "") ;
					$strName = ($NombreFilsc!="" ? " concat(P.Name,' ' ,P.FirstLastName,' ', P.SecondLastName) LIKE '%" . $NombreFilsc . "%'" : "") ;
					$strEst = ($EstadoFilsc!="" && $EstadoFilsc!="-1"? " P.StatusPostulantID=" . $EstadoFilsc : "");
					$strAho = ($AhorroFilsc!=""? " P.Saving>=" . $AhorroFilsc : "");
					$strAs= ($AsesorFilsc!="" && $AsesorFilsc!="-1"? " P.CreateUserID=" . $AsesorFilsc : "");
					if($Filtro!="false"){
						
						$strWhere = " WHERE S.HouseSubTypeID = P.HouseSubTypeID AND U.UserID = P.CreateUserID AND P.CreateDate >'".$FechaInicio."' AND P.CreateDate <'".$FechaFin."'"  .  ($strAs!=""? " AND " . $strAs: "" ) . ($strEst!=""? " AND " . $strEst : "") . ($strAho!="" ? " AND" . $strAho : "");
					}
					else{
						$strWhere =  " WHERE S.HouseSubTypeID = P.HouseSubTypeID AND U.UserID = P.CreateUserID AND " . ($strID!="" ? $strID  : "1=1" ) . ($strName!=""? " AND ". $strName:"") .  ($strAs!=""? " AND " . $strAs: " " ) . ($strEst!=""? " AND " . $strEst : "") . ($strAho!="" ? " AND" . $strAho : "");
					}
					
					$sql="select P.PostulantID, P.IDNumber, concat(P.Name,' ', P.FirstLastName,' ', P.SecondLastName) Nombre,P.HomeTelephone,P.CellularTelephone, S.Description as TipoVivienda,U.UserName as Asesor, P.CreateDate FROM postulant P, user U, housesubtype S" . $strWhere . " ORDER BY P.CreateDate ASC, PostulantID ASC"; 
					

					$resultR = $db->query($sql);					
					$row = mysql_num_rows($resultR);
					
					$___STRLINES___ ="";
					$Counter = 0;
					while($objR = mysql_fetch_object($resultR))
					{    				
						$PostulantID = $objR->PostulantID;
						$IDNumber = $objR->IDNumber;
						$Nombre = $objR->Nombre;
						$HomeTelephone = $objR->HomeTelephone;
						$CellularTelephone = $objR->CellularTelephone;
						$TipoVivienda = $objR->TipoVivienda;
						$Asesor = $objR->Asesor;
						$CreateDate = $objR->CreateDate;
						$Counter = $Counter + 1;
						$___STRLINES___=$___STRLINES___ . "<tr '> " . 
							"<td width=width=3%'><p class=MsoNormal style='text-align:center;line-height:normal'>" . $Counter. "</p></td>" .
							"<td width=width=3%'><p class=MsoNormal style='text-align:center;line-height:normal'>" . $IDNumber. "</p></td>" .
							"<td width=width=17%'><p class=MsoNormal style='text-align:center;line-height:normal'>" . $Nombre. "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $HomeTelephone. "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $CellularTelephone . "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $TipoVivienda. "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $Asesor. "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $CreateDate . "</p></td>" .
							"</tr>";						
					}
					
					$___FECHA___				= date("d/M/Y H:i:s");
					//obtenemos el usuario
					$___FILTROASESOR___='TODOS';
					if($AsesorFilsc!="-1") {
						$sqlU = 'SELECT * FROM user WHERE userid=' . $AsesorFilsc;
						$resultU = $db->query($sqlU);
						$objU = mysql_fetch_object($resultU);
						$___FILTROASESOR___=$objU->UserName;
					}
					///obtenemos el estado
					$___FILTROESTADO___='TODOS';
					if($EstadoFilsc!="-1") {
						$sqlS = 'SELECT * FROM StatusPostulant WHERE statuspostulantid=' . $EstadoFilsc;
						$resultS = $db->query($sqlS);
						$objS = mysql_fetch_object($resultS);
						$___FILTROESTADO___=$objS->Description;
					}	
					$___FECHAINICIO___ = date_format( new DateTime($FechaInicio),'d-m-Y');
					$___FECHAFIN___ = date_format( new DateTime($FechaFin),'d-m-Y');
					
					$strHtml= file_get_contents('../PDFs/Source/Templates/Reports/ReportePostulantes.htm');
					$arr=array(
						"___STRLINES___"=>$___STRLINES___,
						"___FECHA___"=>$___FECHA___,

						"___FECHAINICIO___"=>$___FECHAINICIO___,
						"___FECHAFIN___"=>$___FECHAFIN___,	
						"___FILTROASESOR___"=>$___FILTROASESOR___,
						"___FILTROESTADO___"=>$___FILTROESTADO___,
						"___USERNAME___"=>$UserName
						);
					
					$count = count($arr);
					foreach($arr as $key=> $value){
						$strHtml = str_replace($key, $value, $strHtml);
					}
					/////////////////////////////////////////////////////////////
					$strFilename = '../PDFs/Source/Generated/'. 'ReportePostulantes_'. uniqid() .'.htm' ;					
					$archivo=fopen($strFilename,'w') ;
					fputs($archivo,  $strHtml);
					fclose($archivo);
				}
				break;	
			case'ReporteVentasDiarias':{
					$AsesorFilsc  = $_POST['AsesorFilsc'] ;
					$EstadoFilsc = $_POST['EstadoFilsc'];
					$FechaInicio = (isset($_POST['FechaInicio']) ? $_POST['FechaInicio'] : '');
					$FechaFin = (isset($_POST['FechaFin']) ? $_POST['FechaFin'] : '');
					$strAs= ($AsesorFilsc!="" && $AsesorFilsc!="-1"? " S.CreateUserID=" . $AsesorFilsc : "");
					$strEs= ($EstadoFilsc!="" && $EstadoFilsc!="-1"? " S.StatusSellID=" . $EstadoFilsc : "");
					
					$sqlR =  "select S.SellID, concat(C.FirstLastName, ' ', C.SecondLastName, ' ', C.Name) as cliente, E.Description as etapa, M.Description as manzana, H.Solar as solar, T.Description as estado, S.TotalCost as Price, S.AdditionalCorner, S.AdditionalWayStreet, S.AdditionalGreenArea, S.Entrance, S.Reserve, S.EntranceSald, S.PrequalificationAmount,S.FinalSald, U.UserName as Asesor, S.CreateDate as FechaCreacion, S.DateFiltroPreVenta as FechaGenReserva, S.DateEmiInscription as FechaInscripcion
							FROM sell as S
							LEFT JOIN client as C ON S.ClientID = C.ClientID
							LEFT JOIN etapa as E ON S.EtapaID = E.EtapaID
							LEFT JOIN mz as M ON S.MzID = M.MzID
							LEFT JOIN house as H ON S.HouseID = H.HouseID
							LEFT JOIN statussell as T ON S.StatusSellID = T.StatusSellID
							LEFT JOIN user as U ON S.CreateUserID = U.UserID 
							WHERE S.StatusSellID=1 AND U.UserID=S.CreateUserID AND S.CreateDate>='" .  $FechaInicio."'" . "AND S.CreateDate<='" . $FechaFin ."'" . ($strAs!=""? " AND " . $strAs : "") . ($strEs!=""?" AND " . $strEs : "") . 
						" UNION ALL " .
						" select S.SellID, concat(C.FirstLastName, ' ', C.SecondLastName, ' ', C.Name) as cliente, E.Description as etapa, M.Description as manzana, H.Solar as solar, T.Description as estado, S.TotalCost as Price, S.AdditionalCorner, S.AdditionalWayStreet, S.AdditionalGreenArea, S.Entrance, S.Reserve, S.EntranceSald, S.PrequalificationAmount,S.FinalSald, U.UserName as Asesor, S.CreateDate as FechaCreacion, S.DateFiltroPreVenta as FechaGenReserva, S.DateEmiInscription as FechaInscripcion
							FROM sell as S
							LEFT JOIN client as C ON S.ClientID = C.ClientID
							LEFT JOIN etapa as E ON S.EtapaID = E.EtapaID
							LEFT JOIN mz as M ON S.MzID = M.MzID
							LEFT JOIN house as H ON S.HouseID = H.HouseID
							LEFT JOIN statussell as T ON S.StatusSellID = T.StatusSellID
							LEFT JOIN user as U ON S.CreateUserID = U.UserID 
							WHERE S.StatusSellID=3 AND U.UserID=S.CreateUserID AND S.DateFiltroPreVenta>='" .  $FechaInicio."'" . "AND S.DateFiltroPreVenta<='" . $FechaFin ."'" . ($strAs!=""? " AND " . $strAs : "") . ($strEs!=""?" AND " . $strEs : "") .
						" UNION ALL" .
						" select S.SellID, concat(C.FirstLastName, ' ', C.SecondLastName, ' ', C.Name) as cliente, E.Description as etapa, M.Description as manzana, H.Solar as solar, T.Description as estado, S.TotalCost as Price, S.AdditionalCorner, S.AdditionalWayStreet, S.AdditionalGreenArea, S.Entrance, S.Reserve, S.EntranceSald, S.PrequalificationAmount,S.FinalSald, U.UserName as Asesor, S.CreateDate as FechaCreacion, S.DateFiltroPreVenta as FechaGenReserva, S.DateEmiInscription as FechaInscripcion
							FROM sell as S
							LEFT JOIN client as C ON S.ClientID = C.ClientID
							LEFT JOIN etapa as E ON S.EtapaID = E.EtapaID
							LEFT JOIN mz as M ON S.MzID = M.MzID
							LEFT JOIN house as H ON S.HouseID = H.HouseID
							LEFT JOIN statussell as T ON S.StatusSellID = T.StatusSellID
							LEFT JOIN user as U ON S.CreateUserID = U.UserID 
							WHERE S.StatusSellID=4 AND U.UserID=S.CreateUserID AND S.DateEmiInscription>='" .  $FechaInicio."'" . "AND S.DateEmiInscription<='" . $FechaFin ."'" . ($strAs!=""? " AND " . $strAs : "") . ($strEs!=""?" AND " . $strEs : "") .
							" UNION ALL " . /// para el total
						"
							select 'TOTAL:' as SellID, count(*) as cliente, '' as etapa, '' as manzana, '' as solar, '' as estado, sum(S.Price) as Price, sum(S.AdditionalCorner) as AdditionalCorner, sum(S.AdditionalWayStreet) as AdditionalWayStreet, sum(S.AdditionalGreenArea) as AdditionalGreenArea, sum(S.Entrance) as Entrance, sum(S.Reserve) as Reserve, sum(S.EntranceSald) as EntranceSald, sum(PrequalificationAmount) as PrequalificationAmount, sum(S.FinalSald) as FinalSald, '' as Asesor, '' as FechaCreacion, '' as FechaGenReserva, '' as FechaInscripcion
							from 
							(select S.SellID, concat(C.FirstLastName, ' ', C.SecondLastName, ' ', C.Name) as cliente, E.Description as etapa, M.Description as manzana, H.Solar as solar, T.Description as estado, S.TotalCost as Price, S.AdditionalCorner, S.AdditionalWayStreet, S.AdditionalGreenArea, S.Entrance, S.Reserve, S.EntranceSald, S.PrequalificationAmount,S.FinalSald, U.UserName as Asesor, S.CreateDate as FechaCreacion, S.DateFiltroPreVenta as FechaGenReserva, S.DateEmiInscription as FechaInscripcion
							FROM sell as S
							LEFT JOIN client as C ON S.ClientID = C.ClientID
							LEFT JOIN etapa as E ON S.EtapaID = E.EtapaID
							LEFT JOIN mz as M ON S.MzID = M.MzID
							LEFT JOIN house as H ON S.HouseID = H.HouseID
							LEFT JOIN statussell as T ON S.StatusSellID = T.StatusSellID
							LEFT JOIN user as U ON S.CreateUserID = U.UserID 
							WHERE S.StatusSellID=1 AND U.UserID=S.CreateUserID AND S.CreateDate>='" .  $FechaInicio."'" . "AND S.CreateDate<='" . $FechaFin ."'" . ($strAs!=""? " AND " . $strAs : "") . ($strEs!=""?" AND " . $strEs : "") .
							" UNION ALL " .
						" select S.SellID, concat(C.FirstLastName, ' ', C.SecondLastName, ' ', C.Name) as cliente, E.Description as etapa, M.Description as manzana, H.Solar as solar, T.Description as estado, S.TotalCost as Price, S.AdditionalCorner, S.AdditionalWayStreet, S.AdditionalGreenArea, S.Entrance, S.Reserve, S.EntranceSald, S.PrequalificationAmount,S.FinalSald, U.UserName as Asesor, S.CreateDate as FechaCreacion, S.DateFiltroPreVenta as FechaGenReserva, S.DateEmiInscription as FechaInscripcion
							FROM sell as S
							LEFT JOIN client as C ON S.ClientID = C.ClientID
							LEFT JOIN etapa as E ON S.EtapaID = E.EtapaID
							LEFT JOIN mz as M ON S.MzID = M.MzID
							LEFT JOIN house as H ON S.HouseID = H.HouseID
							LEFT JOIN statussell as T ON S.StatusSellID = T.StatusSellID
							LEFT JOIN user as U ON S.CreateUserID = U.UserID 
							WHERE S.StatusSellID=3 AND U.UserID=S.CreateUserID AND S.DateFiltroPreVenta>='" .  $FechaInicio."'" . "AND S.DateFiltroPreVenta<='" . $FechaFin ."'" . ($strAs!=""? " AND " . $strAs : "") . ($strEs!=""?" AND " . $strEs : "") .
							" UNION ALL " .
						" select S.SellID, concat(C.FirstLastName, ' ', C.SecondLastName, ' ', C.Name) as cliente, E.Description as etapa, M.Description as manzana, H.Solar as solar, T.Description as estado, S.TotalCost as Price, S.AdditionalCorner, S.AdditionalWayStreet, S.AdditionalGreenArea, S.Entrance, S.Reserve, S.EntranceSald, S.PrequalificationAmount, S.FinalSald, U.UserName as Asesor, S.CreateDate as FechaCreacion, S.DateFiltroPreVenta as FechaGenReserva, S.DateEmiInscription as FechaInscripcion
							FROM sell as S
							LEFT JOIN client as C ON S.ClientID = C.ClientID
							LEFT JOIN etapa as E ON S.EtapaID = E.EtapaID
							LEFT JOIN mz as M ON S.MzID = M.MzID
							LEFT JOIN house as H ON S.HouseID = H.HouseID
							LEFT JOIN statussell as T ON S.StatusSellID = T.StatusSellID
							LEFT JOIN user as U ON S.CreateUserID = U.UserID 
							WHERE S.StatusSellID=4 AND U.UserID=S.CreateUserID AND S.DateEmiInscription>='" .  $FechaInicio."'" . "AND S.DateEmiInscription<='" . $FechaFin ."'" . ($strAs!=""? " AND " . $strAs : "") . ($strEs!=""?" AND " . $strEs : "") . ") as S";
					
					$resultR = $db->query($sqlR);					
					$row = mysql_num_rows($resultR);
					
					$___STRLINES___ ="";
					while($objR = mysql_fetch_object($resultR))
					{    				
						$SellID = $objR->SellID;
						$Cliente = $objR->cliente;
						$Etapa = $objR->etapa;
						$Manzana = $objR->manzana;
						$Solar = $objR->solar;
						$Estado = $objR->estado;
						$Precio = number_format ($objR->Price, 2);
						$Esquinero = number_format ($objR->AdditionalCorner, 2);
						$Avenida = number_format ($objR->AdditionalWayStreet, 2);
						$AreaVerde = number_format ($objR->AdditionalGreenArea, 2);
						$CuotaEntrada = number_format ($objR->Entrance, 2);
						$Reserva = number_format ($objR->Reserve, 2);
						$ValorFinanciar = number_format ($objR->EntranceSald, 2);
						$PrequalificationAmount= number_format ($objR->PrequalificationAmount, 2);
						$SaldoFinal = number_format ($objR->FinalSald, 2);
						$Asesor = $objR->Asesor;
						$FechaCreacion = $objR->FechaCreacion;
						$FechaGenReserva = $objR->FechaGenReserva ;
						$FechaInscription  = $objR->FechaInscripcion;
						$___LASTCOUNT___ = $Cliente;
//						$LastAmmountPorCobrar=;
						$___LASTAMMOUNT___=$Reserva;
						$___STRLINES___=$___STRLINES___ . "<tr '> " . 
							"<td width=width=3%'><p class=MsoNormal style='text-align:center;line-height:normal'>" . $SellID. "</p></td>" .
							"<td width=width=17%'><p class=MsoNormal style='text-align:center;line-height:normal'>" . $Cliente. "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $Etapa. "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $Manzana . "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $Solar. "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $Estado. "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $Precio . "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $Esquinero. "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $Avenida. "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $AreaVerde. "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $CuotaEntrada . "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $Reserva. "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $ValorFinanciar. "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $PrequalificationAmount. "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $SaldoFinal . "</p></td>" .
							"<td width=width=4%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $Asesor. "</p></td>" .
							"<td width=width=8%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $FechaCreacion. "</p></td>" .
							"<td width=width=8%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $FechaGenReserva. "</p></td>" .
							"<td width=width=8%'><p class=MsoNormal style='text-align:right;line-height:normal'>" . $FechaInscription. "</p></td>" .
							"</tr>";						
					}
					$___PREFIJO___ = "";
					if($EstadoFilsc==4){
						$strSQLVentas = " select concat(u.name,' ',u.lastname) Asesor, s.createuserid UserID, count(s.DateEmiInscription) Ventas ".
							" FROM sell s, user u " .
							" WHERE s.StatusSellID=4 AND " .
							"	u.UserID=s.CreateUserID AND " .
							"	s.DateEmiInscription>=' " . $FechaInicio . "' AND " .  
							"	s.DateEmiInscription<='" . $FechaFin .  "' " . 
							" group by s.createuserid ";
					}
					if($EstadoFilsc==3){
						$strSQLVentas = " select concat(u.name,' ',u.lastname) Asesor, s.createuserid UserID, count(s.DateFiltroPreVenta) Ventas ".
							" FROM sell s, user u " .
							" WHERE s.StatusSellID=3 AND " .
							"	u.UserID=s.CreateUserID AND " .
							"	s.DateFiltroPreVenta>=' " . $FechaInicio . "' AND " .  
							"	s.DateFiltroPreVenta<='" . $FechaFin .  "' " . 
							" group by s.createuserid ";
							$___PREFIJO___ = "PRE-";
					}
					$___STRLINESVENTAS___ ="";
					if($EstadoFilsc==3 || $EstadoFilsc==4) {
						$resultVentas = $db->query($strSQLVentas);					
						
						while($objVentas = mysql_fetch_object($resultVentas)){
							$AsesorVentas = $objVentas->Asesor;
							$CantidadVentas = $objVentas->Ventas;
							$___STRLINESVENTAS___=$___STRLINESVENTAS___. "<tr '> " . 
								"<td width=width=60%'><p class=MsoNormal style='text-align:center;font-size:smaller;line-height:normal'>" . $AsesorVentas. "</p></td>" .
								"<td width=width=40%'><p class=MsoNormal style='text-align:center;font-size:smaller;line-height:normal'>" . $CantidadVentas. "</p></td>" .
								"</tr>";	
						}						
					}
					$___FECHA___				= date("d/M/Y H:i:s");
					//obtenemos el usuario
					$___FILTROASESOR___='TODOS';
					if($AsesorFilsc!="-1") {
						$sqlU = 'SELECT * FROM user WHERE userid=' . $AsesorFilsc;
						$resultU = $db->query($sqlU);
						$objU = mysql_fetch_object($resultU);
						$___FILTROASESOR___=$objU->UserName;
					}
					///obtenemos el estado
					$___FILTROESTADO___='TODOS';
					if($EstadoFilsc!="-1") {
						$sqlS = 'SELECT * FROM statussell WHERE statussellid=' . $EstadoFilsc;
						$resultS = $db->query($sqlS);
						$objS = mysql_fetch_object($resultS);
						$___FILTROESTADO___=$objS->Description;
					}	
					$___FECHAINICIO___ = date_format( new DateTime($FechaInicio),'d-m-Y');
					$___FECHAFIN___ = date_format( new DateTime($FechaFin),'d-m-Y');
					
					$___TITULO1___='TOTAL REGISTROS';
					$___TITULO2___='TOTAL RESERVAS';
					if($EstadoFilsc=="3"){
						$___TITULO1___='TOTAL RESERVAS';
						$___TITULO2___='TOTAL POR DEPOSITAR';
					}
					if($EstadoFilsc=="4"){
						$___TITULO1___='TOTAL VENTAS';
						$___TITULO2___='TOTAL DEPOSITADO';
					}

					$strHtml= file_get_contents('../PDFs/Source/Templates/Reports/ReporteVentasDiarias.htm');
					$arr=array(
						"___STRLINES___"=>$___STRLINES___,
						"___STRLINESVENTAS___"=>$___STRLINESVENTAS___,
						"___FECHA___"=>$___FECHA___,
						"___LASTCOUNT___"=>$___LASTCOUNT___,
						"___LASTAMMOUNT___"=>$___LASTAMMOUNT___,
						"___TITULO1___"=>$___TITULO1___,
						"___TITULO2___"=>$___TITULO2___,
						"___FECHAINICIO___"=>$___FECHAINICIO___,
						"___FECHAFIN___"=>$___FECHAFIN___,	
						"___FILTROASESOR___"=>$___FILTROASESOR___,
						"___FILTROESTADO___"=>$___FILTROESTADO___,
						"___USERNAME___"=>$UserName,
						"___PREFIJO___"=> $___PREFIJO___
						);
					
					$count = count($arr);
					foreach($arr as $key=> $value){
						$strHtml = str_replace($key, $value, $strHtml);
					}
					/////////////////////////////////////////////////////////////
					$strFilename = '../PDFs/Source/Generated/'. 'ReporteVentasDiarias_'. uniqid() .'.htm' ;					
					$archivo=fopen($strFilename,'w') ;
					fputs($archivo,  $strHtml);
					fclose($archivo);
					/*$oldDir = getcwd();
					chdir('../PDFs/Source/Templates/Reports/');
					$dompdf = new DOMPDF();
					$dompdf->set_paper('A4');
					$dompdf->load_html($strHtml);
					$dompdf->render();
					$strPDF = $dompdf->output();
					chdir($oldDir);
					$strFilename = $strFilename .'.pdf';
					$archivo=fopen($strFilename,'w') ;
					fputs($archivo,  $strPDF);
					fclose($archivo);*/
				}
				break;
			case 'CARTERA':{
					$FechaInicio = (isset($_POST['FechaInicio']) ? $_POST['FechaInicio'] : '');
					$FechaFin = (isset($_POST['FechaFin']) ? $_POST['FechaFin'] : '');
					
					$sql = "SELECT s.SellID, CONCAT( c.FirstLastName,  ' ', c.SecondLastName, ' ',c.Name ) Cliente, IDNumber, CONCAT( e.Description,  ' Mz ', m.Description,  ' Solar', h.Solar ) ubicacion, s.TotalCost, s.Entrance, (

								SELECT SUM( d.Deposit ) 
								FROM deposit d
								WHERE d.SellID = s.SellID
								)Pagos
								FROM sell s, client c, house h, mz m, etapa e
								WHERE c.ClientID = s.ClientID
								AND s.StatusSellID =4
								AND h.HouseID = s.HouseID
								AND m.MzID = h.MzID
								AND e.EtapaID = m.EtapaID
								AND s.DateEmiInscription >= '" . $FechaInicio . "'
								AND s.DateEmiInscription <= '" . $FechaFin . "'
								ORDER BY Cliente";
					$result = $db->query($sql);					
					$row = mysql_num_rows($result);
					//Generar las lineas del plan de pagos
					
					$___STRLINES___ ="";										
					while($obj = mysql_fetch_object($result))
					{    				
						$SellID = $obj->SellID;
						$Cliente = $obj->Cliente;
						$Cedula = $obj->IDNumber;
						$Ubicacion = $obj->ubicacion;
						$CostoTotal = $obj->TotalCost;
						$Entrada = $obj->Entrance;
						$Pagos = $obj->Pagos;
						$___STRLINES___=$___STRLINES___ . "<tr '> " . 
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $SellID. "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $Cliente. "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $Cedula. "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $Ubicacion . "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $CostoTotal. "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $Entrada. "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $Pagos. "</p></td>" .
							"</tr>";
						
					}					
					
					$___FECHA___				= date("d/M/Y H:i:s");
					$___USERNAME___				= $UserName;
					$strHtml= file_get_contents('../PDFs/Source/Templates/Reports/Cartera.htm');
					$arr=array(
						"___STRLINES___"=>$___STRLINES___,
						"___FECHA___"=>$___FECHA___,
						"___USERNAME___"=>$___USERNAME___	
						);
					
					$count = count($arr);
					foreach($arr as $key=> $value){
						$strHtml = str_replace($key, $value, $strHtml);
					}
					/////////////////////////////////////////////////////////////
					$strFilename = '../PDFs/Source/Generated/'. 'Cartera_'. uniqid() .'.htm' ;					
					$archivo=fopen($strFilename,'w') ;
					fputs($archivo,  $strHtml);
					fclose($archivo);
					/*$oldDir = getcwd();
					chdir('../PDFs/Source/Templates/Reports/');
					$dompdf = new DOMPDF();
					$dompdf->set_paper('A4');
					$dompdf->load_html($strHtml);
					$dompdf->render();
					$strPDF = $dompdf->output();
					chdir($oldDir);
					$strFilename = $strFilename .'.pdf';
					$archivo=fopen($strFilename,'w') ;
					fputs($archivo,  $strPDF);
					fclose($archivo);*/
					
				}
				break;
			case 'PAGOSVENCIDOS':{
					$FechaFin = $_POST['FechaFin'];
					
					$sql = "SELECT CONCAT( c.Name,  ' ', c.FirstLastName,  ' ', c.SecondLastName ) Cliente, c.IDNumber Cedula, c.HomeTelephone, p.Reference, p.Capital, p.Quota, p.Sald, date_format(p.VencDate, '%d/%m/%Y')VencDate
								FROM paymentplan p, client c, sell s
								WHERE s.SellID = p.SellID
								AND c.ClientID = s.ClientID
								AND p.Status =2
								AND p.VencDate <= '" . $FechaFin . "'
								AND p.Type =2";
					$result = $db->query($sql);					
					$row = mysql_num_rows($result);
					//Generar las lineas del plan de pagos
					
					$___STRLINES___ ="";										
					while($obj = mysql_fetch_object($result))
					{    				
						//$SellID = $obj->SellID;
						$Cliente = $obj->Cliente;
						$Cedula = $obj->Cedula;
						$Telefono = $obj->HomeTelephone;
						$Reference = $obj->Reference;
						$Capital = $obj->Capital;
						$Quota = $obj->Quota;
						$Sald = $obj->Sald;
						$VencDate = $obj->VencDate;	
						$___STRLINES___=$___STRLINES___ . "<tr '> " . 
							//"<td width=width=10%'><p class=MsoNormal style='text-align:center;line-height:normal'>" . $SellID. "</p></td>" .
							"<td width=width=20%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $Cliente. "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $Cedula. "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $Telefono . "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $Reference. "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $Capital. "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $Quota. "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $Sald. "</p></td>" .
							"<td width=width=15%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $VencDate. "</p></td>" .
							
							"</tr>";
						
					}					
					
					$___FECHA___				= date("d/M/Y H:i:s");
					$___USERNAME___				= $UserName;
					
					$strHtml= file_get_contents('../PDFs/Source/Templates/Reports/PagosVencidos.htm');
					$arr=array(
						"___STRLINES___"=>$___STRLINES___,
						"___FECHA___"=>$___FECHA___,
						"___USERNAME___"=>$___USERNAME___
						);
					
					$count = count($arr);
					foreach($arr as $key=> $value){
						$strHtml = str_replace($key, $value, $strHtml);
					}
					/////////////////////////////////////////////////////////////
					$strFilename = '../PDFs/Source/Generated/'. 'PagosVencidos_'. uniqid() .'.htm';					
					$archivo=fopen($strFilename,'w') ;
					fputs($archivo,  $strHtml);
					fclose($archivo);
					/*$oldDir = getcwd();
					chdir('../PDFs/Source/Templates/Reports/');
					$dompdf = new DOMPDF();
					$dompdf->set_paper('A4');
					$dompdf->load_html($strHtml);
					$dompdf->render();
					$strPDF = $dompdf->output();
					chdir($oldDir);
					$strFilename = $strFilename .'.pdf';
					$archivo=fopen($strFilename,'w') ;
					fputs($archivo,  $strPDF);
					fclose($archivo);*/
					
				}
				break;
			case 'FIRMACONTRATO':{
					$AsesorFilsc  = $_POST['AsesorFilsc'] ;					
					$FechaInicio = (isset($_POST['FechaInicio']) ? $_POST['FechaInicio'] : '');
					$FechaFin = (isset($_POST['FechaFin']) ? $_POST['FechaFin'] : '');
					$strAs= ($AsesorFilsc!="" && $AsesorFilsc!="-1"? "S.CreateUserID=" . $AsesorFilsc : "");
					$sql = "select C.IDNumber as cedula, concat(C.FirstLastName, ' ', C.SecondLastName, ' ', C.Name) cliente, CONCAT( E.Description,  ' Mz ', M.Description,  ' Solar', H.Solar ) ubicacion, date_format(S.DocsEmisionDate,'%d/%m/%Y') FechaContrato, concat(U.Name, ' ', U.LastName) asesor
							from sell S, client C, etapa E, mz M, house H, user U
							where C.ClientID = S.ClientID
							AND E.EtapaID = S.EtapaID
							AND M.MzID = S.MzID
							AND H.HouseID = S.HouseID
							AND U.UserID = S.CreateUserID
							AND S.StatusSellID = 4
							AND S.DocsEmisionDate >= '" . $FechaInicio ."'
							AND S.DocsEmisionDate <= '" . $FechaFin . "'
							". ($strAs!=""? " AND " . $strAs : "") .
						" order by Asesor";
					$result = $db->query($sql);					
					$row = mysql_num_rows($result);
										
					//Generar las lineas del plan de pagos
					
					$___STRLINES___ ="";
					$___TOTAL___ = 0;									
					while($obj = mysql_fetch_object($result))
					{    				
						$Cedula = $obj->cedula;
						$Cliente = $obj->cliente;
						$Ubicacion = $obj->ubicacion;
						$FechaFirma = $obj->FechaContrato;
						$Asesor = $obj->asesor;
						$___TOTAL___ = $___TOTAL___ + 1;
						
						$___STRLINES___=$___STRLINES___ . "<tr '> " . 
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $Cedula. "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $Cliente. "</p></td>" .							
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $Ubicacion . "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $FechaFirma. "</p></td>" .
							"<td width=width=10%'><p class=MsoNormal style='text-align:left;line-height:normal'>" . $Asesor. "</p></td>" .
							
							"</tr>";						
					}					
					
					$___FECHA___				= date("d/M/Y H:i:s");
					$___USERNAME___				= $UserName;
					$___FECHAINICIO___ = date_format( new DateTime($FechaInicio),'d-m-Y');
					$___FECHAFIN___ = date_format( new DateTime($FechaFin),'d-m-Y');
					$strHtml= file_get_contents('../PDFs/Source/Templates/Reports/ReporteFirmaContrato.htm');
					$arr=array(
						"___STRLINES___"=>$___STRLINES___,
						"___FECHA___"=>$___FECHA___,
						"___USERNAME___"=>$___USERNAME___,
						"___TOTAL___"=>$___TOTAL___,
						"___FECHAINICIO___"=>$___FECHAINICIO___,
						"___FECHAFIN___"=>$___FECHAFIN___
						);
					
					$count = count($arr);
					foreach($arr as $key=> $value){
						$strHtml = str_replace($key, $value, $strHtml);
					}
					/////////////////////////////////////////////////////////////
					$strFilename = '../PDFs/Source/Generated/'. 'FirmaContrato_'. uniqid() .'.htm' ;					
					$archivo=fopen($strFilename,'w') ;
					fputs($archivo,  $strHtml);
					fclose($archivo);
					/*$oldDir = getcwd();
					chdir('../PDFs/Source/Templates/Reports/');
					$dompdf = new DOMPDF();
					$dompdf->set_paper('A4');
					$dompdf->load_html($strHtml);
					$dompdf->render();
					$strPDF = $dompdf->output();
					chdir($oldDir);
					$strFilename = $strFilename .'.pdf';
					$archivo=fopen($strFilename,'w') ;
					fputs($archivo,  $strPDF);
					fclose($archivo);*/
					
				}
				break;
		}
		Echo '({success:true,Datos:'. json_encode($strFilename) .',Result:' . '"OK"'. '})';
	}
	else
	{
		Echo '({success:false,Datos:'.json_encode("Error al consultar...Por Favor Verifique!!!").'})';		
	}
}
catch (Exception $e)
{
	Echo '({success:false,Datos:'.json_encode($e->getMessage()).'})';		
}

function fechadesc($fecha, $a){
	
	}
?>