<?php
/**
 * Created by PhpStorm.
 * User: Sinerconsult
 * Date: 08-04-2016
 * Time: 11:42 AM
 */

foreach ([
			 "USER" =>"postgres",
			 "PASS" =>"123",
			 "SCHEMA" =>"gestiondelcorredor",
			 "DSN" =>"pgsql:port=5432;host=localhost;user=postgres;dbname=sitiotes_gdc",
		 ] as $key => $value
) { define(strtoupper($key),$value); }

require("Daniia.php");

use Daniia\Daniia;

function met_registrosJoin_Asg_Cli_Gen_Pol_Com_Rec_Gen($cedrif, $cirif, $fecha, $nombrecliente, $rif, $discriminante, $tipovencimiento, $idpoliza=null, $mylim=null, $myoffset=null) {

	$daniia = new Daniia();
	$daniia->from(function(Daniia $daniia){
		$daniia->table('ASEGURA');
		$daniia->select(
			'ASEGURA.cedrif'
			,'NULL AS rif'
			,'CLIENTENATURAL.cirif AS cirif'
			,'CLIENTENATURAL.nombre AS nombre'
			,'CLIENTENATURAL.apellido AS apellido'
			,'ASEGURA.idpoliza AS idpoliza'
			,'POLIZA.discriminante AS discriminante'
			,'COMPANIA.razon AS razon'
			,'GENERA.idrecibo AS idrecibo'
			,"to_char(RECIBO.fvigini, 'DD/MM/YYYY') AS fvigini"
			,"to_char(RECIBO.fvigfin, 'DD/MM/YYYY') AS fvigfin"
			,"to_char(RECIBO.fvigcobro, 'DD/MM/YYYY') AS fvigcobro"
			,'RECIBO.prima AS prima');
		$daniia->join('CLIENTENATURAL','ASEGURA.cirif','CLIENTENATURAL.cirif');
		$daniia->join('GENERA','ASEGURA.idpoliza','GENERA.idpoliza');
		$daniia->join('COMPANIA','GENERA.rif','COMPANIA.rif');
		$daniia->join('POLIZA','GENERA.idpoliza','POLIZA.idpoliza');
		$daniia->join('RECIBO','GENERA.idrecibo','RECIBO.idrecibo');
		$daniia->andWhere('RECIBO.nuevo');

		$daniia->union(function(Daniia $daniia) {
			$daniia->table('ASEGURA');
			$daniia->select(
				'ASEGURA.cedrif'
				,'COMPANIA.rif'
				,'CLIENTENONATURAL.cirif AS cirif'
				,'CLIENTENONATURAL.razonsocial AS nombre'
				,'NULL AS apellido'
				,'ASEGURA.idpoliza AS idpoliza'
				,'POLIZA.discriminante AS discriminante'
				,'COMPANIA.razon AS razon'
				,'GENERA.idrecibo AS idrecibo'
				,"to_char(RECIBO.fvigini, 'DD/MM/YYYY') AS fvigini"
				,"to_char(RECIBO.fvigfin, 'DD/MM/YYYY') AS fvigfin"
				,"to_char(RECIBO.fvigcobro, 'DD/MM/YYYY') AS fvigcobro"
				,'RECIBO.prima AS prima');
			$daniia->join('CLIENTENONATURAL','ASEGURA.cirif','CLIENTENONATURAL.cirif');
			$daniia->join('GENERA','ASEGURA.idpoliza','GENERA.idpoliza');
			$daniia->join('COMPANIA','GENERA.rif','COMPANIA.rif');
			$daniia->join('POLIZA','GENERA.idpoliza','POLIZA.idpoliza');
			$daniia->join('RECIBO','GENERA.idrecibo','RECIBO.idrecibo');
			$daniia->andWhere('RECIBO.nuevo');
		});
	});

	$daniia->where('cedrif',$cedrif);
	if ($cirif) $daniia->andWhere('cirif','ILIKE','%'.$cirif.'%');
	if ($nombrecliente) $daniia->andWhere(function(Daniia $daniia)use($nombrecliente) {
		$daniia->where('nombre','ILIKE',"%{$nombrecliente}%");
		$daniia->orWhere('apellido','ILIKE',"%{$nombrecliente}%");
	});
	if ($rif) $daniia->andWhere('rif',$rif);
	if ($discriminante) $daniia->andWhere('discriminante',$discriminante);
	if ($tipovencimiento == 1 && !$fecha) $daniia->andWhere('TRUE');
	if ($tipovencimiento == 2 && !$fecha) $daniia->andWhere('fvigfin < CURRENT_DATE');
	if ($tipovencimiento == 3 && !$fecha) $daniia->andWhere('fvigcobro IS NULL');
	if ($tipovencimiento == 1 &&  $fecha) $daniia->andWhere(function(Daniia $daniia)use($fecha){
		$daniia->where('Extract(month from fvigfin)',"Extract(month from to_date(".$daniia->quote($fecha).",'YYYY-MM-DD'))",false);
		$daniia->andWhere('Extract(year from fvigfin)',"Extract(year from to_date(".$daniia->quote($fecha).",'YYYY-MM-DD'))",false);
	});
	if ($tipovencimiento == 2 &&  $fecha) $daniia->andWhere(function(Daniia $daniia)use($fecha){
		$daniia->where('fvigfin < CURRENT_DATE');
		$daniia->andWhere('Extract(month from fvigfin)',"Extract(month from to_date(".$daniia->quote($fecha).",'YYYY-MM-DD'))",false);
		$daniia->andWhere('Extract(year from fvigfin)',"Extract(year from to_date(".$daniia->quote($fecha).",'YYYY-MM-DD'))",false);
	});
	if ($tipovencimiento == 3 &&  $fecha) $daniia->andWhere('fvigcobro IS NULL');
	if ($idpoliza) $daniia->andWhere('idpoliza',$idpoliza);
	if ($mylim!==null) $daniia->limit($mylim,$myoffset);

	$data  = $daniia->get();
	echo $daniia->last_sql.'<br><br>';
	//$error = $daniia->error();
	//echo utf8_decode($error[2]).'<br><br>';

	return $data;
}


$r = met_registrosJoin_Asg_Cli_Gen_Pol_Com_Rec_Gen(
	'v-11223344'
	,$cirif=null
	,$fecha=null
	,$nombrecliente=null
	,$rif=null
	,$discriminante=null
	,$tipovencimiento=null
	,$idpoliza=null
	,$mylim=null
	,$myoffset=null
);

var_dump($r);