<?php
//require '../Conexion/class.sql.inc.php';
require '../../Conexion/class.oracle.php';
include_once '../../General/Logger.php';
//$db = new EyeMySQLAdap();

session_start();

$connection = new PDOConnection();

try {

	//if (true)
	if (isset($_POST['Filsc']))
	{
		$varfil= $_POST['Filsc'];

		//$varfil= 'Ciudad';
		$arr = array();

		switch ($varfil)
		{
			case "Mensajes":{
				$UserID = $_POST['UserID'];
				$sql =  "SELECT MessageID, Description,date_format(CreateDate,'%d/%m/%Y') CreateDate,Type ,Criticity, Status FROM message WHERE Status =1 AND UserID=" . $UserID;
				break;
				}
			case 'ComboCustomerB':{
                if(isset($_POST['IncludeAll'])){
					if($_POST['IncludeAll']=='true')
						$arr[]=array("ProductoID"=>"-1","Description"=>"Todos");
                }
					$sql = "select CUSTOMERID , CUSTOMERFIDUCIA||'-'||CUSTOMERNAME DESCRIPTION FROM CUSTOMER";
				}
				break;
			case 'ComboAgencyB':{ /// TODO: Validar para cuando solo debe ver su ciudad

				if(isset($_POST['IncludeAll'])){
					if($_POST['IncludeAll']=='true')
						$arr[]=array("AgenciaID"=>"-1","Description"=>"Todos");
                }
				$customerID = "-1";
			if(isset($_POST['CustomerID'])){
                $customerID=$_POST['CustomerID'];
            }
				$sql = " SELECT CI.CITYID || '-'|| CU.CUSTOMERID AS CUSTOMERCITYID, CI.DESCRIPTION AS DESCRIPTION" .
                    " FROM CUSTOMERCITY CU, CITY CI " .
                    " WHERE CU.CUSTOMERID = " . $customerID . " " .
                    " AND CU.STATEID = 1 " .
                    " AND CU.CITYID = CI.CITYID " .
                    " AND CI.STATUS = 1 ";
				}
				break;
            case 'ComboFormatoB':{ /// TODO: Validar para cuando solo debe ver su ciudad

                    if(isset($_POST['IncludeAll'])){
                        if($_POST['IncludeAll']=='true')
                            $arr[]=array("TRUSTYPEID"=>"-1","DESCRIPTION"=>"Todos");
                    }
                    $sql = " SELECT TRUSTTYPEID, DESCRIPTION FROM TRUSTTYPEFORMAT" ;
				}
				break;
			case 'GridCcontratoB':{
					$customerID = $_POST['CustomerID'];
					$cityIDtxt = $_POST['AgencyID'];
					$cityID = explode('-',$cityIDtxt)[0];
					$trustFormatID	= $_POST['TrustFormatID'];
					$txtSearch	= $_POST['txtSearch'];
					$SearchBy = $_POST['SearchBy'];
					$Filsc		= $_POST['Filsc'];
					$sql = "select tmp.contractid AS CONTRATOID, tmp.name AS NOMBRE,  to_char(tmp.daycontract, 'DD/MM/YYYY') AS FECHA, tmp.vehicleid, tmp.vehicle AS VEHICULO, tmp.cedula AS CEDULARUC, tmp.vehicleenginenumber AS MOTOR, tmp.vehiclechassisnumber, tmp.customername, tmp.description AS ESTADO, tmp.urltype, tmp.statusid, tmp.pasaporte " .
					" from ( ".
					" SELECT  co.contractid as contractid, ve.vehicleid as vehicleid, a.identitynumber as cedula, a.lastname||' '||a.lastnamemother||' '||a.firstname||' '||a.secondname as name , p.description||' '||ve.vehicletype||' '||ve.vehicleclass as vehicle, ve.enginenumber as vehicleenginenumber, ve.chassisnumber as vehiclechassisnumber, co.daycontract as daycontract, cu.customername as customername, ct.description as description, tr.urltype as urltype, co.statusid as statusid, a.passport as pasaporte " .
					" FROM contractadhesion co, applicant a, customer cu, vehicle ve, contractstatus ct, trusttypeformat tr, parameter p " .
					" WHERE co.customerid =   " . $customerID .  ( $SearchBy ==1  && $txtSearch != '' ?  " and a.identitynumber like '%" . $txtSearch  . "%'" : " ")  .
					 ($SearchBy ==3  && $txtSearch != '' ?  " and a.lastname||' '||a.lastnamemother||' '||a.firstname||' '||a.secondname like REPLACE('%" . $txtSearch . "%', ' ', '')" : " " ) .  " and co.cityid = " .  $cityID . " and co.trusttypeid =  " . $trustFormatID .
					//(profileId == -1 ?  :  AND co.usersystemid =   profileId)
					($SearchBy ==2 && $txtSearch !='' ? " AND trim(VE.CHASSISNUMBER) like '%" . $txtSearch . "%'" : " ") . " AND co.customerid = cu.customerid and co.contractid = a.contractid and co.contractid = ve.contractid and co.statusid = ct.statusid and co.trusttypeid = tr.trusttypeid and co.statusid = ct.statusid and p.domainid = cu.domainformarca and p.subdomainid = ve.trademark and a.applicantid = (select min(e.applicantid) from applicant e where e.contractid = co.contractid and e.applicanttype=1) " .
					" group by co.contractid, a.identitynumber, ve.vehicleid, p.description || ' ' || ve.vehicletype || ' ' || ve.vehicleclass, ve.enginenumber, ve.chassisnumber, " .
					" co.daycontract, cu.customername, ct.description, tr.urltype, co.statusid, a.firstname, a.secondname, a.lastname, a.lastnamemother, a.passport " .
					" ) tmp order by tmp.contractid desc " ;

				}
				break;
            case 'HistoricoEstados':{
                $contractID = $_POST['ContractID'];

                $sql = "select st.when AS FECHA, us.username AS USUARIO, cs.description AS ESTADO, st.comentario as COMENTARIO
						  from statushistory st, contractstatus cs, usersystem us
						 where cs.statusid = st.status_end
						   AND us.usersystemid = st.usersystemid
						   AND st.contract_type = 1
						   and st.contractid =" . $contractID . "  order by 1 desc"  ;

				}
				break;
		}

		Logger::info("ON FORMAT");
		Logger::debug($_POST['Filsc'] . ' -> [' . $sql  . ']');
		Logger::debug($sql);
		$result=$connection->select($sql);
		$rows = count($result);
		Logger::debug($rows);
		Echo '({success:true,"totalcount":"'.$rows.'","Data":'.json_encode($result).'})';
	}
	else
	{
		Echo '({success:false,Datos:'.json_encode("Error al consultar...Por Favor Verifique!!!").'})';
	}
}
catch (Exception $e)
{
	Echo '({success:false,Datos:'.json_encode($e->getMessage()).'})';
}

?>

