<?php

class Buscar{
	private $database;
	private $fCargoInicial;
	private $fCargoFinal;
	private $ejecutivo;
	private $tipo;
	private $campo;
	private $anio;
	private $mes;
	private $user;

	public function __construct(){
		$this->database = new db();
	}

	public function setFechaCargoInicial($fCargoInicial){
		$this->fCargoInicial=$fCargoInicial;
	}
	public function setFechaCargoFinal($fCargoFinal){
		$this->fCargoFinal=$fCargoFinal;
	}
	public function setEjecutivo($ejecutivo){
		$this->ejecutivo=$ejecutivo;
	}
	public function setTipo($tipo){
		$this->tipo=$tipo;
	}
	public function setCampo($campo){
		$this->campo=$campo;
	}
	
	public function setAnio($anio){
		$this->anio=$anio;
	}
	public function setMes($mes){
		$this->mes=$mes;
	}
	public function setUser($user){
		$this->user=$user;
	}


	public function getFechaCargoInicial(){
		return $this->fCargoInicial;
	}
	public function getFechaCargoFinal(){
		return $this->fCargoFinal;
	}
	public function getEjecutivo(){
		return $this->ejecutivo;
	}
	public function getTipo(){
		return $this->tipo;
	}
	public function getCampo(){
		return $this->campo;
	}
	public function getAnio(){
		return $this->anio;
	}
	public function getMes(){
		return $this->mes;
	}
	public function getUser(){
		return $this->user;
	}
		
		
	public function insertBitacora(){
		try{
			$this->database->beginTransaction();
			$this->database->query("INSERT INTO bitacoraReporte(usuario, anio, mes, inputEjecutivo,periodo, fechaGenera) VALUES(:usuario,:anio,:mes,:inputEjecutivo,:periodo,NOW())");
			$this->database->bind(':usuario',$this->getUser());
			$this->database->bind(':anio',$this->getAnio());
			$this->database->bind(':mes',$this->getMes());
			$this->database->bind(':inputEjecutivo',$this->getEjecutivo());
			$this->database->bind(':periodo',$this->getFechaCargoInicial()."-".$this->getFechaCargoFinal());
            $this->database->execute();
			if ($this->database->endTransaction()) {
                    return array('response'=>true);
            }
		}catch(PDOException $e){
		  echo 'ERROR!';
          print_r($e);
		}
	}	
	public function obtineDatosBusqueda()
    {
        try {
          
            $this->database->beginTransaction();
			
			$cond  = "";
			if($this->getEjecutivo() == 'todos'){
				$cond = "";
			}else if($this->getEjecutivo() == 'equipo'){
				$cond = "AND r.id_ejecutivo NOT IN(182,183,295,328,337,343,364,366,370,300,309,329,338,365,371,379,381,296,330,339,344,363,380)";
			}else{
				$cond = "AND r.id_ejecutivo=".$this->getEjecutivo();
			}
			
            $this->database->query("SELECT 
			                          COUNT(DISTINCT p.id_concepto) total, 
									  CONCAT(cl.nombre,' ',cl.a_pat,' ',cl.a_mat) AS nombreCl, 
									  p.fecha AS fechaPago,
									  p.f_entrada AS fip,
									  p.f_salida AS ffc, 
									  sum(p.monto) AS monto, 
									  p.divisa AS divisa, 
									  p.tipo_moneda AS moneda, 
									  p.formapago AS formaDePago, 
									  cp.nom_propiedad AS departamento, 
									  d.no_depto AS no, 
									  c.no_anios AS anios, 
									  c.no_meses AS meses, 
									  c.no_sem AS semanas, 
									  c.no_dias AS noches, 
									  d.tipo AS tipoDep,
									  p.concepto,
									  r.id_ejecutivo,
									  r.prospeccion
					   FROM pagos AS p 
					   INNER JOIN conceptos AS c ON p.id_concepto=c.id_concepto AND c.status_renta IN('ANTICIPO','PAGADO') 
					   INNER JOIN renta AS r ON c.id_renta=r.id_renta ".$cond." 
					   INNER JOIN clientes AS cl ON r.id_cliente=cl.id_cliente 
					   INNER JOIN complejo cp ON p.propiedad=cp.id_complejo 
					   INNER JOIN departamentos AS d ON p.clave=d.id_departamento 
					   WHERE p.concepto IN('RENTA') and p.detalle NOT IN('BLANCOS','MASCOTA','PEXT','DESPERFECTO','RDLL','TC','CDD') AND p.valadmon='s' AND  c.dia_inicio BETWEEN '".$this->getFechaCargoInicial()."' AND '".$this->getFechaCargoFinal()."'  and r.prospeccion=:tipo  order by p.id_concepto
                  ");//group by id_concepto
				
			$this->database->bind(':tipo',$this->getTipo());
            $this->database->execute();
            if ($this->database->endTransaction()) {
                    return $this->database->resultset();
            }
      $this->database = null;
        } catch (PDOException $e) {
            //echo 'ERROR!';
            print_r($e);
        }
    }
	
	public function getEjecutivos(){
		try{
			$this->database->beginTransaction();
			$this->database->query("SELECT 
			                            id_empleado,
										puesto,
										gerente,
										coordinador,
										CONCAT(nombre,' ',a_pat,' ',a_mat) nombre,
										fechabaja
									FROM empleados WHERE fechabaja = '0000-00-00' and puesto = 5
                  ");
            $this->database->execute();
			if ($this->database->endTransaction()) {
                    return $this->database->resultset();
            }
		}catch(PDOException $e){
		  echo 'ERROR!';
          print_r($e);
		}
	}
	
	public function getProspeccion(){
		try{
			$this->database->beginTransaction();
			$this->database->query("SELECT 
			                           id,
									   nom
									FROM llenacombos WHERE campo = :campo and estatus = 1
                  ");
		    $this->database->bind(':campo',$this->getCampo());
            $this->database->execute();
			if ($this->database->endTransaction()) {
                    return $this->database->resultset();
            }
		}catch(PDOException $e){
		  echo 'ERROR!';
          print_r($e);
		}
	}
	
	public function getMedios(){
		try{
			$this->database->beginTransaction();
			$this->database->query("SELECT id,canal from medio where estatus=1");
            $this->database->execute();
			if ($this->database->endTransaction()) {
                    return $this->database->resultset();
            }
		}catch(PDOException $e){
		  echo 'ERROR!';
          print_r($e);
		}
	}
	
	public function getMedio($medio){
		try{
			$this->database->beginTransaction();
			$this->database->query("SELECT id,canal from medio WHERE id=:medio");
			$this->database->bind(':medio',$medio);
            $this->database->execute();
			if ($this->database->endTransaction()) {
                    return $this->database->single();
            }
		}catch(PDOException $e){
		  echo 'ERROR!';
          print_r($e);
		}
	}
	
	public function obtieneDatosMedios()
    {
        try {
          
            $this->database->beginTransaction();
			$cond  = "";
			if($this->getEjecutivo() == 'todos'){
				$cond = "";
			}else if($this->getEjecutivo() == 'equipo'){
				$cond = "AND r.id_ejecutivo NOT IN(182,183,295,328,337,343,364,366,370,300,309,329,338,365,371,379,381,296,330,339,344,363,380)";
			}else{
				$cond = "AND r.id_ejecutivo=".$this->getEjecutivo();
			}
            $this->database->query("SELECT 
			                          COUNT(DISTINCT p.id_concepto) total, 
									  CONCAT(cl.nombre,' ',cl.a_pat,' ',cl.a_mat) AS nombreCl, 
									  p.fecha AS fechaPago,
									  p.f_entrada AS fip,
									  p.f_salida AS ffc, 
									  sum(p.monto) AS monto, 
									  p.divisa AS divisa, 
									  p.tipo_moneda AS moneda, 
									  p.formapago AS formaDePago, 
									  cp.nom_propiedad AS departamento, 
									  d.no_depto AS no, 
									  c.no_anios AS anios, 
									  c.no_meses AS meses, 
									  c.no_sem AS semanas, 
									  c.no_dias AS noches, 
									  d.tipo AS tipoDep,
									  p.concepto,
									  r.id_ejecutivo,
									  r.prospeccion,
									  r.medio,
									  (CASE
										WHEN r.medio <> '' THEN r.medio
										ELSE '".$this->getTipo()."'
									  END) nombremedio
									  
					   FROM pagos AS p 
					   INNER JOIN conceptos AS c ON p.id_concepto=c.id_concepto AND c.status_renta IN('ANTICIPO','PAGADO') 
					   INNER JOIN renta AS r ON c.id_renta=r.id_renta ".$cond."
					   INNER JOIN clientes AS cl ON r.id_cliente=cl.id_cliente 
					   INNER JOIN complejo cp ON p.propiedad=cp.id_complejo 
					   INNER JOIN departamentos AS d ON p.clave=d.id_departamento 
					   WHERE p.concepto IN('RENTA') and p.detalle NOT IN('BLANCOS','MASCOTA','PEXT','DESPERFECTO','RDLL','TC','CDD') AND p.valadmon='s' AND  c.dia_inicio BETWEEN '".$this->getFechaCargoInicial()."' AND '".$this->getFechaCargoFinal()."'  and r.medio=:tipo  order by p.id_concepto
                  ");//group by id_concepto
				
			$this->database->bind(':tipo',$this->getTipo());
            $this->database->execute();
            if ($this->database->endTransaction()) {
                    return $this->database->resultset();
            }
      $this->database = null;
        } catch (PDOException $e) {
            //echo 'ERROR!';
            print_r($e);
        }
    }
	
	public function getSubMedios(){
		try{
			$this->database->beginTransaction();
			$this->database->query("SELECT idsubmedio,idcanal,subcanal from submedio where estatus=1");
            $this->database->execute();
			if ($this->database->endTransaction()) {
                    return $this->database->resultset();
            }
		}catch(PDOException $e){
		  echo 'ERROR!';
          print_r($e);
		}
	}
	
	public function getSubMedio($submedio){
		try{
			$this->database->beginTransaction();
			$this->database->query("SELECT subcanal from submedio WHERE idsubmedio=:submedio");
			$this->database->bind(':submedio',$submedio);
            $this->database->execute();
			if ($this->database->endTransaction()) {
                    return $this->database->single();
            }
		}catch(PDOException $e){
		  echo 'ERROR!';
          print_r($e);
		}
	}
	
	public function obtieneDatosSubMedios()
    {
        try {
          
            $this->database->beginTransaction();
			$cond  = "";
			if($this->getEjecutivo() == 'todos'){
				$cond = "";
			}else if($this->getEjecutivo() == 'equipo'){
				$cond = "AND r.id_ejecutivo NOT IN(182,183,295,328,337,343,364,366,370,300,309,329,338,365,371,379,381,296,330,339,344,363,380)";
			}else{
				$cond = "AND r.id_ejecutivo=".$this->getEjecutivo();
			}
            $this->database->query("SELECT 
									  CONCAT(cl.nombre,' ',cl.a_pat,' ',cl.a_mat) AS nombreCl, 
									  p.fecha AS fechaPago,
									  p.f_entrada AS fip,
									  p.f_salida AS ffc, 
									  sum(p.monto) AS monto, 
									  p.divisa AS divisa, 
									  p.tipo_moneda AS moneda, 
									  p.formapago AS formaDePago, 
									  cp.nom_propiedad AS departamento, 
									  d.no_depto AS no, 
									  c.no_anios AS anios, 
									  c.no_meses AS meses, 
									  c.no_sem AS semanas, 
									  c.no_dias AS noches, 
									  d.tipo AS tipoDep,
									  p.concepto,
									  r.id_ejecutivo,
									  r.prospeccion,
									  r.medio,
									  r.submedio,
                                      c.salida_prematura									  
					   FROM pagos AS p 
					   INNER JOIN conceptos AS c ON p.id_concepto=c.id_concepto AND c.status_renta IN('ANTICIPO','PAGADO') 
					   INNER JOIN renta AS r ON c.id_renta=r.id_renta ".$cond." 
					   INNER JOIN clientes AS cl ON r.id_cliente=cl.id_cliente 
					   INNER JOIN complejo cp ON p.propiedad=cp.id_complejo 
					   INNER JOIN departamentos AS d ON p.clave=d.id_departamento 
					   WHERE p.concepto IN('RENTA') and p.detalle NOT IN('BLANCOS','MASCOTA','PEXT','DESPERFECTO','RDLL','TC','CDD') AND p.valadmon='s' AND  c.dia_inicio BETWEEN '".$this->getFechaCargoInicial()."' AND '".$this->getFechaCargoFinal()."' group by p.id_concepto order by p.id_concepto
                  ");//group by id_concepto
				
			$this->database->bind(':tipo',$this->getTipo());
            $this->database->execute();
            if ($this->database->endTransaction()) {
                    return $this->database->resultset();
            }
      $this->database = null;
        } catch (PDOException $e) {
            //echo 'ERROR!';
            print_r($e);
        }
    }
	
	public function obtineDatosMotivo()
    {
        try {
          
            $this->database->beginTransaction();
			$cond  = "";
			if($this->getEjecutivo() == 'todos'){
				$cond = "";
			}else if($this->getEjecutivo() == 'equipo'){
				$cond = "AND r.id_ejecutivo NOT IN(182,183,295,328,337,343,364,366,370,300,309,329,338,365,371,379,381,296,330,339,344,363,380)";
			}else{
				$cond = "AND r.id_ejecutivo=".$this->getEjecutivo();
			}
            $this->database->query("SELECT 
			                          COUNT(DISTINCT p.id_concepto) total, 
									  CONCAT(cl.nombre,' ',cl.a_pat,' ',cl.a_mat) AS nombreCl, 
									  p.fecha AS fechaPago,
									  p.f_entrada AS fip,
									  p.f_salida AS ffc, 
									  sum(p.monto) AS monto, 
									  p.divisa AS divisa, 
									  p.tipo_moneda AS moneda, 
									  p.formapago AS formaDePago, 
									  cp.nom_propiedad AS departamento, 
									  d.no_depto AS no, 
									  c.no_anios AS anios, 
									  c.no_meses AS meses, 
									  c.no_sem AS semanas, 
									  c.no_dias AS noches, 
									  d.tipo AS tipoDep,
									  p.concepto,
									  r.id_ejecutivo,
									  r.prospeccion,
									  cl.motivo_viaje
					   FROM pagos AS p 
					   INNER JOIN conceptos AS c ON p.id_concepto=c.id_concepto AND c.status_renta IN('ANTICIPO','PAGADO') 
					   INNER JOIN renta AS r ON c.id_renta=r.id_renta ".$cond."
					   INNER JOIN clientes AS cl ON r.id_cliente=cl.id_cliente 
					   INNER JOIN complejo cp ON p.propiedad=cp.id_complejo 
					   INNER JOIN departamentos AS d ON p.clave=d.id_departamento 
					   WHERE p.concepto IN('RENTA') and p.detalle NOT IN('BLANCOS','MASCOTA','PEXT','DESPERFECTO','RDLL','TC','CDD') AND p.valadmon='s' AND  c.dia_inicio BETWEEN '".$this->getFechaCargoInicial()."' AND '".$this->getFechaCargoFinal()."'  and cl.motivo_viaje=:tipo  order by p.id_concepto
                  ");//group by id_concepto
				
			$this->database->bind(':tipo',$this->getTipo());
            $this->database->execute();
            if ($this->database->endTransaction()) {
                    return $this->database->resultset();
            }
      $this->database = null;
        } catch (PDOException $e) {
            //echo 'ERROR!';
            print_r($e);
        }
    }

	public function obtineMetaIngresos()
    {
        try {
          
            $this->database->beginTransaction();
			
			$cond  = "";
			if($this->getEjecutivo() == 'todos'){
				$cond = "";
			}else if($this->getEjecutivo() == 'equipo'){
				$cond = "AND ms.id_emp NOT IN(182,183,295,328,337,343,364,366,370,300,309,329,338,365,371,379,381,296,330,339,344,363,380)";
			}else{
				$cond = "AND ms.id_emp=".$this->getEjecutivo();
			}
			
            $this->database->query("SELECT ms.monto FROM metas AS ms INNER JOIN meta AS m WHERE ms.id_meta=m.id AND m.mes=:mes AND m.anio=:anio ".$cond." GROUP BY ms.id_emp
                  ");//group by ms.monto
				
			//$this->database->bind(':idEjecutivo',$this->getEjecutivo());
			$this->database->bind(':anio',$this->getAnio());
			$this->database->bind(':mes',$this->getMes());
            $this->database->execute();
            if ($this->database->endTransaction()) {
                    return $this->database->resultset();
            }
      $this->database = null;
        } catch (PDOException $e) {
            //echo 'ERROR!';
            print_r($e);
        }
    }
	
	public function obtineRealIngresos()
    {
        try {
          
            $this->database->beginTransaction();
			
			$cond  = "";
			if($this->getEjecutivo() == 'todos'){
				$cond = "";
			}else if($this->getEjecutivo() == 'equipo'){
				$cond = "AND r.id_ejecutivo NOT IN(182,183,295,328,337,343,364,366,370,300,309,329,338,365,371,379,381,296,330,339,344,363,380)";
			}else{
				$cond = "AND r.id_ejecutivo=".$this->getEjecutivo();
			}
			
            $this->database->query("SELECT CONCAT(cl.nombre,' ',cl.a_pat,' ',cl.a_mat) AS nombreCl, p.fecha AS fechaPago,CONCAT(p.f_entrada,' - ',p.f_salida) AS periodo, p.monto AS monto, p.divisa AS divisa, p.tipo_moneda AS moneda, p.formapago AS formaDePago, cp.nom_propiedad AS departamento, d.no_depto AS no,c.dia_inicio 
					  FROM pagos AS p 
					  INNER JOIN conceptos AS c ON p.id_concepto=c.id_concepto AND c.status_renta IN('ANTICIPO','PAGADO') 
					  INNER JOIN renta AS r ON c.id_renta=r.id_renta ".$cond."
					  INNER JOIN clientes AS cl ON r.id_cliente=cl.id_cliente 
					  INNER JOIN complejo cp ON p.propiedad=cp.id_complejo 
					  INNER JOIN departamentos AS d ON p.clave=d.id_departamento 
					  WHERE p.concepto NOT IN('SEGUIMIENTO','DEPOSITO','IMPUESTOS') and p.detalle NOT IN('BLANCOS','MASCOTA','PEXT','DESPERFECTO','RDLL','TC','CDD') AND p.valadmon='s' AND  c.dia_inicio BETWEEN '".$this->getFechaCargoInicial()."' AND '".$this->getFechaCargoFinal()."'
                  ");//group by id_concepto
				
			//$this->database->bind(':idEjecutivo',$this->getEjecutivo());
            $this->database->execute();
            if ($this->database->endTransaction()) {
                    return $this->database->resultset();
            }
      $this->database = null;
        } catch (PDOException $e) {
            //echo 'ERROR!';
            print_r($e);
        }
    }
	
	public function obtineRealIngresosPropiedad()
    {
        try {
          
            $this->database->beginTransaction();
			$cond  = "";
			if($this->getEjecutivo() == 'todos'){
				$cond = "";
			}else if($this->getEjecutivo() == 'equipo'){
				$cond = "AND r.id_ejecutivo NOT IN(182,183,295,328,337,343,364,366,370,300,309,329,338,365,371,379,381,296,330,339,344,363,380)";
			}else{
				$cond = "AND r.id_ejecutivo=".$this->getEjecutivo();
			}
            $this->database->query("SELECT 
										p.propiedad,
										SUM(CASE WHEN p.tipo_moneda = 'MXN' THEN  p.monto/p.divisa ELSE p.monto END) as monto,
										cp.nom_propiedad AS nompropiedad 
									FROM pagos AS p 
									  INNER JOIN conceptos AS c ON p.id_concepto=c.id_concepto AND c.status_renta IN('ANTICIPO','PAGADO') 
									  INNER JOIN renta AS r ON c.id_renta=r.id_renta ".$cond."
									  INNER JOIN clientes AS cl ON r.id_cliente=cl.id_cliente 
									  INNER JOIN complejo cp ON p.propiedad=cp.id_complejo 
									  INNER JOIN departamentos AS d ON p.clave=d.id_departamento 
									WHERE p.concepto NOT IN('SEGUIMIENTO','DEPOSITO','IMPUESTOS') and p.detalle NOT IN('BLANCOS','MASCOTA','PEXT','DESPERFECTO','RDLL','TC','CDD') AND p.valadmon='s' AND  c.dia_inicio BETWEEN '".$this->getFechaCargoInicial()."' AND '".$this->getFechaCargoFinal()."' group by p.propiedad
                  ");//group by id_concepto
				
			//$this->database->bind(':idEjecutivo',$this->getEjecutivo());
            $this->database->execute();
            if ($this->database->endTransaction()) {
                    return $this->database->resultset();
            }
      $this->database = null;
        } catch (PDOException $e) {
            //echo 'ERROR!';
            print_r($e);
        }
    }
}