<?php

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

    /**
     * Executes list action
     *
     * @param sfRequest $request A request object
     */
    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;
        }

        //ordenar consulta
        if (isset($request['_sortBy'])) {
            $sortBy = " Order by " . $request['_sortBy'];
        } else {
            $sortBy = ' order by tab0.id_anhos, tab0.num_contrato, tab0.num_doc';
        }


        /*
         * Manejo de errores
         */
        try {
            /*
             * Variables obligatorias
             */
            $id_anhos = $request['id_anhos'];
            //$fecha_ini = $request['fecha_ini'];
            //$fecha_fin = $request['fecha_fin'];

            /*
             * Variables opcionales
             */
            //id_contratista
            if (isset($request['id_contratista'])) {
                $whereIdContratista = " AND tab0.id_contratista = " . $request['id_contratista'];
            } else {
                $whereIdContratista = ' ';
            }
            //id_contrato
            if (isset($request['id_contrato'])) {
                $whereIdContrato = " AND tab0.id_contrato = " . $request['id_contrato'];
            } else {
                $whereIdContrato = ' ';
            }

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

            /**
             *
             */
            $consulta_sql = <<<EOD
select
tab0.*
from(

    SELECT

    cont.id_acta_inicio,
    cont.adjunto,
    cont.fecha_log,
    cont.usuario_log,
                    
    cont.id_contrato,
    cont.id_contratista,
    cont.num_contrato,
    cont.id_anhos,
    cont.fecha_contrato,
    cont.objeto,
    cont.obligaciones,
    cont.estado,
    cont.rp_anm,
    cont.rp_sgr,
    cont.fecha_rp,
    cont.fecha_poliza,
    cont.valor_anm,
    cont.cdp_anm,
    cont.fecha_cdp_anm,
    cont.valor_sgr,
    cont.cdp_sgr,
    cont.fecha_cdp_sgr,
    cont.dependencia,
    cont.meses_plazo,
    cont.dias_plazo,
    cont.fecha_plazo,
    cont.id_supervisor,
    cont.id_apoyo_supervisor,
    cont.fecha_ini,
    cont.fecha_fin,
    cont.year,
    cont.id_mae_ter_sup,
    cont.num_doc_sup,
    cont.razon_soc_sup,
    cont.cargo_sup,
    cont.correo_sup,
    cont.estado_sup,
    cont.id_mae_ter_apo,
    cont.num_doc_apo,
    cont.razon_soc_apo,
    cont.cargo_apo,
    cont.correo_apo,
    cont.estado_apo,
    cont.rp_anm || cont.rp_sgr as rp,

    contra.tipo_recurso,
    contra.num_doc,
    contra.razon_social,
    contra.correo,

    nvl(forpag.valor_mensual,0) as valor_mensual,
    nvl(forpag.valor_comisiones,0) as valor_comisiones,
    nvl(forpag.valor_pagar,0) as valor_pagar,
    nvl(forpag.valor_a_pagar,0) as valor_a_pagar,
    nvl(forpag.valor_total_reten,0) as valor_total_reten,
    nvl(forpag.aporte_salud,0) as aporte_salud,
    nvl(forpag.aporte_pension,0) as aporte_pension,
    nvl(forpag.aporte_fsp,0) as aporte_fsp,
    nvl(forpag.aporte_arl,0) as aporte_arl,
    nvl(forpag.aporte_volun_afc,0) as aporte_volun_afc,
    nvl(forpag.aporte_volun_pen,0) as aporte_volun_pen,
    nvl(forpag.valor_intereses_vivienda,0) as valor_intereses_vivienda,
    nvl(forpag.valor_dependientes,0) as valor_dependientes,
    nvl(forpag.valor_medicina,0) as valor_medicina,
    nvl(forpag.valor_embargo,0) as valor_embargo,
    
    (cont.valor_anm + cont.valor_sgr) as valor_contrato,
    nvl(forpag.valor_a_pagar,0) as valor_ejecutado,
    (cont.valor_anm + cont.valor_sgr) - nvl(forpag.valor_a_pagar,0) as saldo_contrato,
    round(nvl(forpag.valor_a_pagar,0) * 40 / 100) as valor_ibc

    FROM rvc_acta_inicio_v cont

    left join rvc_contratista_v contra
    on contra.id_contratista = cont.id_contratista

    left join(
        select
        forpag.id_anhos,
        forpag.id_contrato,
        forpag.id_contratista,

        sum( nvl(forpag.valor_mensual,0) ) as valor_mensual,
        sum( nvl(forpag.valor_comisiones,0) ) as valor_comisiones,
        sum( nvl(forpag.valor_pagar,0) ) as valor_pagar,
        sum( nvl(forpag.valor_a_pagar,0) ) as valor_a_pagar,
        sum( nvl(forpag.valor_total_reten,0) ) as valor_total_reten,
        sum( nvl(forpag.aporte_salud,0) ) as aporte_salud,
        sum( nvl(forpag.aporte_pension,0) ) as aporte_pension,
        sum( nvl(forpag.aporte_fsp,0) ) as aporte_fsp,
        sum( nvl(forpag.aporte_arl,0) ) as aporte_arl,
        sum( nvl(forpag.aporte_volun_afc,0) ) as aporte_volun_afc,
        sum( nvl(forpag.aporte_volun_pen,0) ) as aporte_volun_pen,
        sum( nvl(forpag.valor_intereses_vivienda,0) ) as valor_intereses_vivienda,
        sum( nvl(forpag.valor_dependientes,0) ) as valor_dependientes,
        sum( nvl(forpag.valor_medicina,0) ) as valor_medicina,
        sum( nvl(forpag.valor_embargo,0) ) as valor_embargo

        from rvc_formato_pago_v forpag

        where
        forpag.id_anhos <= $id_anhos
        and forpag.estado = 1

        group by
        forpag.id_anhos,
        forpag.id_contrato,
        forpag.id_contratista
    )forpag
    on cont.id_contrato = forpag.id_contrato

    where 
    cont.id_anhos <= $id_anhos 

)tab0
                    
where
1 = 1
$whereIdContratista
$whereIdContrato      
EOD;

            /*
             * Sql que trae los resultados
             * por los filtros, para totalizar las filas
             */
            $sql = <<<EOD
select count(*) as count from (
                    
    $consulta_sql    
                    
)
EOD;


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


            //obtener el listado de recaudos
            $sql = <<<EOD
SELECT * FROM ( SELECT row_.*, ROWNUM rownum_ FROM (

    $consulta_sql

    $sortBy
          
) row_ WHERE ROWNUM <=  $rowEnd   ) where rownum_ >  $rowStart
EOD;
            //ejecuta y obtiene el resultado del sql
            $resultado = CN_TABLE::executeSQL($sql);


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

            $this->status = 1;
            $this->totalRows = 0;
            $this->startRow = 0;
            $this->endRow = 0;
            $this->list_data = array();
            $this->logMessage("ERROR!!!");
            $this->logMessage($e->getMessage());
        }
    }

}
