<?php

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

    /**
     * Obtener records para los saldos
     *
     * @param sfWebRequest $request
     */
    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");



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

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


        //solo imprime log la primera vez de consulta
        if ($rowStart == 0) {

            /*
             * variables por defecto para el registro de log
             */
            $id_tabla = 'null';
            $id_vigencia = 'null';
            $id_cen_op = 'null';

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

            //obtener id maestro tabla
            $id_mae_bd = CN_TABLE::consultarIdTablaBD('CA_SALDOS_DEB_VIGPER ');

            //1 - indica operacion de consulta
            $id_ope_bd = 1;

            //id del maestro tercero
            $id_mae_ter = 1;
            if (isset($request['id_username'])) {
                $id_mae_ter = $request['id_username'];
            }
            $usuario = 'admin';
            if (isset($request['username'])) {
                if (!is_null($request['username']) && $request['username'] != '') {
                    $usuario = $request['username'];
                }
            }
            $comando = 'sin comando';
            if (isset($request['comando'])) {
                $comando = $request['comando'];
            }
            //construir observacion
            $observacion = "'Se consulto estado cuenta resumido desde la funcionalidad: " . $comando . "'";

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

            /*
             * registro de consulta en tabla de log
             */
            $sql = CN_TABLE::registroTableLog($id_tabla, $id_vigencia, $id_cen_op, $id_cia, $id_mae_bd, $id_ope_bd, $id_mae_ter, $usuario, $comando, $observacion, $id_punto_venta_log);

            //ejecuta el sql
//            CN_TABLE::executeSQL($sql);
        }

        //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);


        /*
         * Manejo de errores
         */
        try {


            /*
             * Variables obligatorias
             */
            //tab0.vig_per between '201201' and '201204'
            //Periodo-vigencia
            $vig_ini = $request['vig_ini'];
            $per_ini = $request['per_ini'];
            $vig_fin = $request['vig_fin'];
            $per_fin = $request['per_fin'];
            //gwt o unir php
            $vig_perIni = $vig_ini . substr('00' . $per_ini, -2, 2);
            $vig_perFin = $vig_fin . substr('00' . $per_fin, -2, 2);


            /*
             * Variables NO obligatorias
             */
            //--and debref.id_referencia2 = '1'
            if (isset($request['id_referencia2'])) {
                $id_mae_ter = $request['id_referencia2'];
                $whereTitulo = "and debref.id_referencia2 = '" . $request['id_referencia2'] . "'";
            } else {
                $whereTitulo = " ";
            }

            //--and ter.id_mae_ter = 443
            if (isset($request['id_mae_ter'])) {
                $id_mae_ter = $request['id_mae_ter'];
                $whereTercero = "and ter.id_mae_ter = " . $request['id_mae_ter'];
            } else {
                $whereTercero = " ";
            }
            /*
             * Se elimina 2176 y 28143 Incidencia #47  - Drummnod de la consulta
             */
            $quitarTercero = " and ter.id_mae_ter != 2176 and ter.id_mae_ter != 28143 ";


            /*
             * sql que trae los resultados
             * por los filtros, para totalizar las filas
             */
            $sql = <<<EOD

select count(*) as count from(

select

debref.id_referencia2,

deb.id_mae_ter,
ter.num_doc,
ter.razon_soc,

null as id_debito,
null as sec_debito,

NVL( sum(

case
   when tab0.id_meses = $per_ini and tab0.id_anhos = $vig_ini
    then tab0.saldo_anterior
   else 0
end

) ,0)  as saldo_anterior,
NVL( sum(tab0.debitos_mes) ,0) as debitos_mes,
NVL( sum(tab0.creditos_mes) ,0) as creditos_mes,
NVL( sum(

case
   when tab0.id_meses = $per_fin and tab0.id_anhos = $vig_fin
    then tab0.saldo_actual
   else 0
end

) ,0)  as saldo_actual,
NVL( sum(

case
   when tab0.id_meses = $per_ini and tab0.id_anhos = $vig_ini
    then tab0.saldo_anterior
   else 0
end

) + sum(tab0.debitos_mes) - sum(tab0.creditos_mes) ,0) as saldo_actual2

from ca_saldos_deb_vigper tab0

join ca_debito deb
on deb.id_debito = tab0.id_debito

left join ca_debito_ref debref
on debref.id_debito = deb.id_debito

join interceros_v ter
on ter.id_mae_ter = deb.id_mae_ter

WHERE
tab0.vig_per between '$vig_perIni' and '$vig_perFin'
--and deb.estado = 1 
and (case when deb.fecha_anu<=last_day(to_date($per_fin||'-'||$vig_fin,'MM-YYYY')) then 1 else 0 END  = 0)    
$whereTitulo
$whereTercero
$quitarTercero
    
group by
debref.id_referencia2,
deb.id_mae_ter,
ter.num_doc,
ter.razon_soc

order by
debref.id_referencia2,
ter.num_doc

)

EOD;

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

            //validar si hay registros
            if ($total_record_count > 0) {


                //sentencia sql del informe
                $sql = <<<EOD

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

select

debref.id_referencia2,

deb.id_mae_ter,
ter.num_doc,
ter.razon_soc,

null as id_debito,
null as sec_debito,

NVL( sum(

case
   when tab0.id_meses = $per_ini and tab0.id_anhos = $vig_ini
    then tab0.saldo_anterior
   else 0
end

) ,0)  as saldo_anterior,
NVL( sum(tab0.debitos_mes) ,0) as debitos_mes,
NVL( sum(tab0.creditos_mes) ,0) as creditos_mes,
NVL( sum(

case
   when tab0.id_meses = $per_fin and tab0.id_anhos = $vig_fin
    then tab0.saldo_actual
   else 0
end

) ,0)  as saldo_actual,
NVL( sum(

case
   when tab0.id_meses = $per_ini and tab0.id_anhos = $vig_ini
    then tab0.saldo_anterior
   else 0
end

) + sum(tab0.debitos_mes) - sum(tab0.creditos_mes) ,0) as saldo_actual2

from ca_saldos_deb_vigper tab0

join ca_debito deb
on deb.id_debito = tab0.id_debito

left join ca_debito_ref debref
on debref.id_debito = deb.id_debito

join interceros_v ter
on ter.id_mae_ter = deb.id_mae_ter

WHERE
tab0.vig_per between '$vig_perIni' and '$vig_perFin'
--and deb.estado = 1 
and (case when deb.fecha_anu<=last_day(to_date($per_fin||'-'||$vig_fin,'MM-YYYY')) then 1 else 0 END  = 0) 
$whereTitulo
$whereTercero
$quitarTercero

group by
debref.id_referencia2,
deb.id_mae_ter,
ter.num_doc,
ter.razon_soc

order by
debref.id_referencia2,
ter.num_doc

) 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("[ESTADO CUENTA RESUMIDO] Consulta exitosa.");
            } else {

                $mensaje = "[ESTADO CUENTA RESUMIDO] No hay registros de consulta.";
                $this->status = 1;
                $this->mensajeError = $mensaje;
                $this->list_data = array();
                $this->logMessage($mensaje);
            }
        } catch (Exception $e) {

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

    /**
     * Metodo para sumar informe cartera edades
     *
     * @param sfWebRequest $request
     */
    public function executeSum(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 {


            /*
             * Variables obligatorias
             */
            //tab0.vig_per between '201201' and '201204'
            //Periodo-vigencia
            $vig_ini = $request['vig_ini'];
            $per_ini = $request['per_ini'];
            $vig_fin = $request['vig_fin'];
            $per_fin = $request['per_fin'];
            //gwt o unir php
            $vig_perIni = $vig_ini . substr('00' . $per_ini, -2, 2);
            $vig_perFin = $vig_fin . substr('00' . $per_fin, -2, 2);


            /*
             * Variables NO obligatorias
             */
            //--and debref.id_referencia2 = '1'
            if (isset($request['id_referencia2'])) {
                $id_mae_ter = $request['id_referencia2'];
                $whereTitulo = "and debref.id_referencia2 = '" . $request['id_referencia2'] . "'";
            } else {
                $whereTitulo = " ";
            }

            //--and ter.id_mae_ter = 443
            if (isset($request['id_mae_ter'])) {
                $id_mae_ter = $request['id_mae_ter'];
                $whereTercero = "and ter.id_mae_ter = " . $request['id_mae_ter'];
            } else {
                $whereTercero = " ";
            }
            /*
             * Se elimina 2176 y 28143 Incidencia #47  - Drummnod de la consulta
             */
            $quitarTercero = " and ter.id_mae_ter != 2176 and ter.id_mae_ter != 28143 ";


            /*
             * sql que trae los resultados
             * por los filtros, para totalizar las filas
             */
            $sql = <<<EOD

select

'TOTALES - Deudores: '  || count(*)  as id_referencia2,
sum(saldo_anterior) as saldo_anterior,
sum(debitos_mes) as debitos_mes,
sum(creditos_mes) as creditos_mes,
sum(saldo_actual) as saldo_actual

from(

select

debref.id_referencia2,

deb.id_mae_ter,
ter.num_doc,
ter.razon_soc,

null as id_debito,
null as sec_debito,

NVL( sum(

case
   when tab0.id_meses = $per_ini and tab0.id_anhos = $vig_ini
    then tab0.saldo_anterior
   else 0
end

) ,0)  as saldo_anterior,
NVL( sum(tab0.debitos_mes) ,0) as debitos_mes,
NVL( sum(tab0.creditos_mes) ,0) as creditos_mes,
NVL( sum(

case
   when tab0.id_meses = $per_fin and tab0.id_anhos = $vig_fin
    then tab0.saldo_actual
   else 0
end

) ,0)  as saldo_actual,
NVL( sum(

case
   when tab0.id_meses = $per_ini and tab0.id_anhos = $vig_ini
    then tab0.saldo_anterior
   else 0
end

) + sum(tab0.debitos_mes) - sum(tab0.creditos_mes) ,0) as saldo_actual2

from ca_saldos_deb_vigper tab0

join ca_debito deb
on deb.id_debito = tab0.id_debito

left join ca_debito_ref debref
on debref.id_debito = deb.id_debito

join interceros_v ter
on ter.id_mae_ter = deb.id_mae_ter

WHERE
tab0.vig_per between '$vig_perIni' and '$vig_perFin'
--and deb.estado = 1 
and (case when deb.fecha_anu<=last_day(to_date($per_fin||'-'||$vig_fin,'MM-YYYY')) then 1 else 0 END  = 0) 
$whereTitulo
$whereTercero
$quitarTercero

group by
debref.id_referencia2,
deb.id_mae_ter,
ter.num_doc,
ter.razon_soc

order by
debref.id_referencia2,
ter.num_doc


)tab1

EOD;

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



            $this->totalRows = count($records);
            $this->startRow = 0;
            $this->endRow = count($records);
            $this->list_data = $records;
            $this->logMessage("[ESTADO CUENTA RESUMIDO] Consulta exitosa.");
        } catch (Exception $e) {

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

}
