<?php

/**
 * ca_reg_cir_cart_tit actions.
 *
 * @package    cartera
 * @subpackage ca_reg_cir_cart_tit
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 23810 2009-11-12 11:07:44Z Kris.Wallsmith $
 */
class ca_reg_cir_cart_titActions extends sfActions {

    public function executeList(sfWebRequest $request) {

        //Revisar que sea post
        //$this->forward404Unless($request->isMethod('post'));
        //Guardar los parametros del request en el log
        $this->logMessage("---BEGIN Logging request");
        foreach ($request->getPostParameters() as $key => $value) {
            $this->logMessage("request[$key] = $value");
        }
        $this->logMessage("--------------------------------------------------*  Ip: " . $_SERVER['REMOTE_ADDR']);
        foreach ($request->getGetParameters() as $key => $value) {
            $this->logMessage("request[$key] = $value");
        }
        $this->logMessage("---END Logging request");


        /*
         * Manejo de errores
         */
        try {


            //sentencia sql
            $sql = <<<EOD
ALTER SESSION SET NLS_DATE_FORMAT = "YYYY-MM-DD"
EOD;
            //ejecuta y obtiene el resultado del sql
            CN_TABLE::executeSQL($sql);


            /*
             * Variables obligatorias
             */

            if (isset($request['id_username'])) {
                $id_username = $request['id_username'];
            }

            /*
             * Paginacion
             */
            if (isset($request['_startRow'])) {
                $rowStart = $request['_startRow'];
            } else {
                $rowStart = 0;
            }

            if (isset($request['_endRow'])) {
                $rowEnd = $request['_endRow'];
            } else {
                $rowEnd = 75;
            }
            $rowEnd = 5000; //Tope Maximo

            /*
             * Ordenar consulta
             */
            if (isset($request['_sortBy'])) {
                $sortBy = " Order by " . $request['_sortBy'];
            } else {
                $sortBy = 'order by debref.id_referencia2 ';
            }

            /*
             * Variables de filtro
             */
            //Periodo
            $id_anhos_filtro = $request['id_anhos_filtro'];
            $id_meses_filtro = $request['id_meses_filtro'];

            $username = $request['username'];


            $id_referencia2 = null;
            $whereTitulo = "";
            if (isset($request['id_referencia2'])) {
                $id_referencia2 = $request['id_referencia2'];
                $whereTitulo = " and debref.id_referencia2 = '" . $id_referencia2 . "'";
            }

            $id_punto_venta = null;
            $wherePuntoVenta = "";
            if (isset($request['id_punto_venta'])) {
                $id_punto_venta = $request['id_punto_venta'];
                $wherePuntoVenta = " AND tab0.id_punto_venta = " . $request['id_punto_venta'];
            }

            $id_mae_ter = null;
            $whereTercero = "";
            if (isset($request['id_mae_ter'])) {
                $id_mae_ter = $request['id_mae_ter'];
                $whereTercero = " AND tab0.id_mae_ter = " . $request['id_mae_ter'];
            }

            $tope = null;
            $whereTope = "";
            if (isset($request['tope'])) {
                $tope = $request['tope'];
                $whereTope = " AND tab0.valor_total < " . $request['tope'];
            }

            $observaciones = null;
            if (isset($request['observaciones'])) {
                $observaciones = $request['observaciones'];
            }


            /**
             * Encontrar registros de debitos por titulo/referencia2
             * Registros sin titular de tercero canon
             */
            //Sql
            $sql = <<<EOD
select count(*) as count from (

select 
/*
tab0.*,
debref.id_referencia1,
*/
debref.id_referencia2,
to_char(wm_concat(tab0.id_debito)) as id_debitos,
nvl(max(tabcant.cant),0) as cant

from ca_debito_saldos tab0

join ca_debito_ref debref
on tab0.id_debito = debref.id_debito

left join(
    select 
    tabcant.cod_titulo,
    count(tabcant.num_doc) as cant
    
    from(
    
        select  
        titper.id_titulo,
        titper.id_titular,
        titper.id_rol_titulo,
        titper.actual,
        tit.cod_titulo,
        ter.num_doc,
        ter.raz_soc,
        case 
        when inter.mail is not null then inter.mail
        when ter.correo is null then ter.correo_alt else ter.correo end as correo
        
        from rmn_titulo_persona@can.anm.local titper
        
        join rmn_titulo@can.anm.local tit
        on tit.id_titulo = titper.id_titulo
        
        join rmn_persona_v@can.anm.local ter
        on ter.id_persona = titper.id_titular
        
        left join (
          SELECT
           aut.*,
           ROW_NUMBER() OVER (
            partition by aut.num_doc
            ORDER BY aut.num_doc
           ) AS rn
          from interceros_du_dir_v aut
        )inter
        on inter.num_doc = ter.num_doc
        and inter.rn = 1
        
        where
        titper.id_rol_titulo = 26 
        --and titper.actual = 1 
        --and tit.cod_titulo = 'ABF-071'
    
    )tabcant
    
    group by
    tabcant.cod_titulo
)tabcant
on tabcant.cod_titulo = debref.id_referencia2

where 
tab0.estado_ven = 1
and tab0.id_meses_sal = $id_meses_filtro and tab0.id_anhos_sal = $id_anhos_filtro
and tab0.saldo_actual > 0
                    
$whereTitulo
$wherePuntoVenta 
$whereTercero
$whereTope     

having 
to_char(wm_concat(tab0.id_debito)) is not null   
and nvl(max(tabcant.cant),0) <= 0

group by
debref.id_referencia2                  
                    
)
EOD;

            $total_record_count = CN_TABLE::executeSQL($sql);
            $total_record_count = $total_record_count[0]["count"];


            /*
             * Obtiene los registros de debito/vencimiento con su saldo respectivo
             * Cambio por listagg, ya que la otra oficialmente no es soportada, y presento error en la consulta - 20170809
             */
            $sql = <<<EOD

SELECT * FROM ( SELECT row_.*, ROWNUM rownum_ FROM (

select 
/*
tab0.*,
debref.id_referencia1,
*/
debref.id_referencia2,
--to_char(wm_concat(tab0.id_debito)) as id_debitos,
listagg( tab0.id_debito, ',' ) within group ( order by tab0.id_debito ) as id_debitos,
nvl(max(tabcant.cant),0) as cant

from ca_debito_saldos tab0

join ca_debito_ref debref
on tab0.id_debito = debref.id_debito

left join(
    select 
    tabcant.cod_titulo,
    count(tabcant.num_doc) as cant
    
    from(
    
        select  
        titper.id_titulo,
        titper.id_titular,
        titper.id_rol_titulo,
        titper.actual,
        tit.cod_titulo,
        ter.num_doc,
        ter.raz_soc,
        case 
        when inter.mail is not null then inter.mail
        when ter.correo is null then ter.correo_alt else ter.correo end as correo
        
        from rmn_titulo_persona@can.anm.local titper
        
        join rmn_titulo@can.anm.local tit
        on tit.id_titulo = titper.id_titulo
        
        join rmn_persona_v@can.anm.local ter
        on ter.id_persona = titper.id_titular
        
        left join (
          SELECT
           aut.*,
           ROW_NUMBER() OVER (
            partition by aut.num_doc
            ORDER BY aut.num_doc
           ) AS rn
          from interceros_du_dir_v aut
        )inter
        on inter.num_doc = ter.num_doc
        and inter.rn = 1
        
        where
        titper.id_rol_titulo = 26 
        --and titper.actual = 1 
        --and tit.cod_titulo = 'ABF-071'
    
    )tabcant
    
    group by
    tabcant.cod_titulo
)tabcant
on tabcant.cod_titulo = debref.id_referencia2

where 
tab0.estado_ven = 1
and tab0.id_meses_sal = $id_meses_filtro and tab0.id_anhos_sal = $id_anhos_filtro
and tab0.saldo_actual > 0
                    
$whereTitulo
$wherePuntoVenta 
$whereTercero
$whereTope     

having 
--to_char(wm_concat(tab0.id_debito)) is not null
listagg( tab0.id_debito, ',' ) within group ( order by tab0.id_debito ) is not null
and nvl(max(tabcant.cant),0) <= 0 

group by
debref.id_referencia2    
                        
$sortBy                        

) row_ WHERE ROWNUM <=  $rowEnd   ) where rownum_ >  $rowStart
EOD;

            //ejecuta y obtiene el resultado del sql
            $records = CN_TABLE::executeSQL($sql);


            $this->totalRows = $total_record_count;
            $this->startRow = $rowStart;
            $this->endRow = $rowStart + count($records);
            $this->list_data = $records;
            $this->logMessage("[ca_reg_cir_cart_tit] Consulta exitosa.");
        } catch (Exception $e) {

            $this->status = 1;
            $this->mensajeError = "ERROR!!!";
            $this->list_data = array();
            $this->logMessage("ERROR!!!");
            $this->logMessage($e->getMessage());
        }
    }

}
