<?php

/**
 * inf_costo_cgn actions.
 *
 * @package    nomina
 * @subpackage inf_costo_cgn
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 23810 2009-11-12 11:07:44Z Kris.Wallsmith $
 */
class inf_costo_cgnActions 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 concepto, id_tipo_doc, num_doc ';
        }



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

            /*
             * Variables opcionales
             */
            //clase planta
            if (isset($request['id_planta'])) {
                $wherePlanta = " AND tab0.id_planta_p = " . $request['id_planta'];
            } else {
                $wherePlanta = ' ';
            }
            if (isset($request['id_com_esc'])) {
                $whereComponenteEscala = " AND tab0.id_comp_es = " . $request['id_com_esc'];
            } else {
                $whereComponenteEscala = ' ';
            }
            

            //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);
            
            
            $sql_consulta = <<<EOD
select
tab0.id_comp_es,
tab0.id_planta_p,
tab0.codigo_concepto,
tab0.denominacion_comp,
tab0.grado_comp,
sum(tab0.cargo_aprobado) as cargo_aprobado,
sum(tab0.cargo_provisto) as cargo_provisto,
tab0.codigo_tipo_vinc,


tab0.val_concep_basica,
tab0.val_concep_gasto_repre,
tab0.factor_concep_gasto_repre,
tab0.val_concep_primtec_sal,
tab0.factor_concep_primtec_sal,
tab0.val_concep_primgest,
tab0.factor_concep_primgest,
tab0.val_concep_primloc,
tab0.factor_concep_primloc,
tab0.val_concep_primcoor,
tab0.factor_concep_primcoor,
tab0.val_concep_primries,
tab0.factor_concep_primries,
tab0.val_concep_primaextra,
tab0.factor_concep_primaextra,
tab0.val_concep_primalt,
tab0.factor_concep_primalt,
tab0.val_concep_subalim,
tab0.factor_concep_subalim,
tab0.val_concep_auxtrans,
tab0.factor_concep_auxtrans,
tab0.val_concep_primant,
tab0.factor_concep_primant,
tab0.val_concep_primserv,
tab0.factor_concep_primserv,
tab0.val_concep_primnav,
tab0.factor_concep_primnav,
tab0.val_concep_bonif,
tab0.factor_concep_bonif,
tab0.val_concep_bonfrec,
tab0.factor_concep_bonfrec,
tab0.val_concep_primvac,
tab0.factor_concep_primvac,
tab0.val_concep_primact,
tab0.factor_concep_primact,
tab0.val_concep_otraprim_sal,
tab0.val_concep_otraprim_no_sal,
tab0.val_concep_cesanu,
0 as val_int_cesanu
from(
select
tab0.id_dp,
tab0.codigo,
tab0.id_comp_es,
tab0.id_planta_p,
tab0.codigo_concepto,
tab0.denominacion_comp,
tab0.grado_comp,
tab0.cargo_aprobado,
tab0.cargo_provisto,
tab0.codigo_tipo_vinc,
tab0.num_doc,
tab0.nombre_apellido,

tab0.val_concep_basica,
tab0.val_concep_gasto_repre,
tab0.factor_concep_gasto_repre,
tab0.val_concep_primtec_sal,
tab0.factor_concep_primtec_sal,
tab0.val_concep_primgest,
tab0.factor_concep_primgest,
tab0.val_concep_primloc,
tab0.factor_concep_primloc,
tab0.val_concep_primcoor,
tab0.factor_concep_primcoor,
tab0.val_concep_primries,
tab0.factor_concep_primries,
tab0.val_concep_primaextra,
tab0.factor_concep_primaextra,
tab0.val_concep_primalt,
tab0.factor_concep_primalt,
tab0.val_concep_subalim,
tab0.factor_concep_subalim,
tab0.val_concep_auxtrans,
tab0.factor_concep_auxtrans,
tab0.val_concep_primant,
tab0.factor_concep_primant,
tab0.val_concep_primserv,
tab0.factor_concep_primserv,
tab0.val_concep_primnav,
tab0.factor_concep_primnav,
tab0.val_concep_bonif,
tab0.factor_concep_bonif,
tab0.val_concep_bonfrec,
tab0.factor_concep_bonfrec,
tab0.val_concep_primvac,
tab0.factor_concep_primvac,
tab0.val_concep_primact,
tab0.factor_concep_primact,
tab0.val_concep_otraprim_sal,
tab0.val_concep_otraprim_no_sal,
tab0.val_concep_cesanu,
0 as val_int_cesanu

from(
    select
    tab0.id_dp,
    tab0.codigo,
    tab0.id_comp_es,
    tab0.id_planta_p,
    tab0.codigo_concepto,
    tab0.denominacion_comp,
    tab0.grado_comp,
    1 as cargo_aprobado,
    nvl2(tab0.num_doc,1,0) as cargo_provisto,
    tab0.codigo_tipo_vinc,
    tab0.num_doc,
    tab0.nombre_apellido,

    sum(case when tab0.id_concep_basica = tab0.id_concep_nomina then case when tab0.valor>0 then tab0.valor else tab0.asignacion_basica end else 0 end) as val_concep_basica,
    sum(case when tab0.id_concep_gasto_repre = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_gasto_repre,
    max(case when tab0.id_concep_gasto_repre = tab0.id_concep_nomina and tab0.valor>0 then tab0.factor_salarial else 0 end) as factor_concep_gasto_repre,
    sum(case when tab0.id_concep_primtec_sal = tab0.id_concep_nomina or tab0.id_concep_primtec_nosal = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_primtec_sal,
    max(case when tab0.id_concep_primtec_sal = tab0.id_concep_nomina and tab0.valor>0  then tab0.factor_salarial
             when tab0.id_concep_primtec_nosal = tab0.id_concep_nomina and tab0.valor>0 then tab0.factor_salarial else 0 end) as factor_concep_primtec_sal,
    sum(case when tab0.id_concep_primgest = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_primgest,
    max(case when tab0.id_concep_primgest = tab0.id_concep_nomina then tab0.factor_salarial else 0 end) as factor_concep_primgest,
    sum(case when tab0.id_concep_primloc = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_primloc,
    max(case when tab0.id_concep_primloc = tab0.id_concep_nomina then tab0.factor_salarial else 0 end) as factor_concep_primloc,
    sum(case when tab0.id_concep_primcoor = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_primcoor,
    max(case when tab0.id_concep_primcoor = tab0.id_concep_nomina then tab0.factor_salarial else 0 end) as factor_concep_primcoor,
    sum(case when tab0.id_concep_primries = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_primries,
    max(case when tab0.id_concep_primries = tab0.id_concep_nomina then tab0.factor_salarial else 0 end) as factor_concep_primries,
    sum(case when tab0.id_concep_primaextra = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_primaextra,
    max(case when tab0.id_concep_primaextra = tab0.id_concep_nomina then tab0.factor_salarial else 0 end) as factor_concep_primaextra,
    sum(case when tab0.id_concep_primalt = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_primalt,
    max(case when tab0.id_concep_primalt = tab0.id_concep_nomina then tab0.factor_salarial else 0 end) as factor_concep_primalt,
    sum(case when tab0.id_concep_subalim = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_subalim,
    max(case when tab0.id_concep_subalim = tab0.id_concep_nomina then tab0.factor_salarial else 0 end) as factor_concep_subalim,
    sum(case when tab0.id_concep_auxtrans = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_auxtrans,
    max(case when tab0.id_concep_auxtrans = tab0.id_concep_nomina then tab0.factor_salarial else 0 end) as factor_concep_auxtrans,
    sum(case when tab0.id_concep_primant = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_primant,
    max(case when tab0.id_concep_primant = tab0.id_concep_nomina then tab0.factor_salarial else 0 end) as factor_concep_primant,
    sum(case when tab0.id_concep_primserv = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_primserv,
    max(case when tab0.id_concep_primserv = tab0.id_concep_nomina then tab0.factor_salarial else 0 end) as factor_concep_primserv,
    sum(case when tab0.id_concep_primnav = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_primnav,
    max(case when tab0.id_concep_primnav = tab0.id_concep_nomina then tab0.factor_salarial else 0 end) as factor_concep_primnav,
    sum(case when tab0.id_concep_bonif = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_bonif,
    max(case when tab0.id_concep_bonif = tab0.id_concep_nomina then tab0.factor_salarial else 0 end) as factor_concep_bonif,
    sum(case when tab0.id_concep_bonfrec = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_bonfrec,
    max(case when tab0.id_concep_bonfrec = tab0.id_concep_nomina then tab0.factor_salarial else 0 end) as factor_concep_bonfrec,
    sum(case when tab0.id_concep_primvac = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_primvac,
    max(case when tab0.id_concep_primvac = tab0.id_concep_nomina then tab0.factor_salarial else 0 end) as factor_concep_primvac,
    sum(case when tab0.id_concep_primact = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_primact,
    max(case when tab0.id_concep_primact = tab0.id_concep_nomina then tab0.factor_salarial else 0 end) as factor_concep_primact,
    sum(case when tab0.id_concep_cesanu = tab0.id_concep_nomina then tab0.valor else 0 end) as val_concep_cesanu,
    sum(case when tab0.id_concep_nomina not in (tab0.id_concep_basica,tab0.id_concep_gasto_repre,tab0.id_concep_primtec_sal,tab0.id_concep_primtec_nosal,tab0.id_concep_primgest,
                                      tab0.id_concep_primloc,tab0.id_concep_primcoor,tab0.id_concep_primries,tab0.id_concep_primaextra,tab0.id_concep_primalt,tab0.id_concep_subalim,tab0.id_concep_auxtrans,
                                      tab0.id_concep_primant,tab0.id_concep_primserv,tab0.id_concep_primnav,tab0.id_concep_bonfrec,tab0.id_concep_primvac,tab0.id_concep_primact,tab0.id_concep_bonif
                                     ,tab0.id_concep_cesanu) and tab0.factor_salarial=1 then tab0.valor else 0 end) as val_concep_otraprim_sal,
    sum(case when tab0.id_concep_nomina not in (tab0.id_concep_basica,tab0.id_concep_gasto_repre,tab0.id_concep_primtec_sal,tab0.id_concep_primtec_nosal,tab0.id_concep_primgest,
                                      tab0.id_concep_primloc,tab0.id_concep_primcoor,tab0.id_concep_primries,tab0.id_concep_primaextra,tab0.id_concep_primalt,tab0.id_concep_subalim,tab0.id_concep_auxtrans,
                                      tab0.id_concep_primant,tab0.id_concep_primserv,tab0.id_concep_primnav,tab0.id_concep_bonfrec,tab0.id_concep_primvac,tab0.id_concep_primact,tab0.id_concep_bonif
                                     ,tab0.id_concep_cesanu) and tab0.factor_salarial=0 then tab0.valor else 0 end) as val_concep_otraprim_no_sal

    from(
        SELECT
        tab0.id_dp,
        tab0.codigo,
        tab0.id_comp_es,
        tab0.id_planta_p,
        tab0.codigo_concepto,
        tab0.denominacion_comp,
        tab0.grado_comp,
        tab0.codigo_tipo_vinc,
        nvl(phas.salario,tab0.asignacion_basica) as asignacion_basica,
        tab1.num_doc,
        tab1.nombre_apellido,

        tab2.id_concep_nomina,
        tab2.tipo_valor,
        tab3.factor_salarial,
        case when tab1.num_doc is not null then trunc(nvl(no_consulta_sql_f(tab1.anho_estado_func,1,tab1.anho_estado_func,12,tab1.id_tipo_doc,tab1.num_doc,tab2.id_concep_nomina,tab2.tipo_valor,tab1.id_periodo),0)) else 0 end as valor,

        nvl(tab4.id_concep_basica,0) as id_concep_basica,
        nvl(tab4.id_concep_gasto_repre,0) as id_concep_gasto_repre,
        nvl(tab4.id_concep_primtec_sal,0) as id_concep_primtec_sal,
        nvl(tab4.id_concep_primtec_nosal,0) as id_concep_primtec_nosal,
        nvl(tab4.id_concep_primgest,0) as id_concep_primgest,
        nvl(tab4.id_concep_primloc,0) as id_concep_primloc,
        nvl(tab4.id_concep_primcoor,0) as id_concep_primcoor,
        nvl(tab4.id_concep_primries,0) as id_concep_primries,
        nvl(tab4.id_concep_primaextra,0) as id_concep_primaextra,
        nvl(tab4.id_concep_primalt,0) as id_concep_primalt,
        nvl(tab4.id_concep_subalim,0) as id_concep_subalim,
        nvl(tab4.id_concep_auxtrans,0) as id_concep_auxtrans,
        nvl(tab4.id_concep_primant,0) as id_concep_primant,
        nvl(tab4.id_concep_primserv,0) as id_concep_primserv,
        nvl(tab4.id_concep_primnav,0) as id_concep_primnav,
        nvl(tab4.id_concep_bonfrec,0) as id_concep_bonfrec,
        nvl(tab4.id_concep_primvac,0) as id_concep_primvac,
        nvl(tab4.id_concep_primact,0) as id_concep_primact,
        nvl(tab4.id_concep_bonif,0) as id_concep_bonif,
        nvl(case when tab2.tipo_valor = 'X' then tab2.id_concep_nomina else 0 end,0) as id_concep_cesanu

        FROM no_dp_v tab0

        left join (
            SELECT
             aut.*,
             ROW_NUMBER() OVER (
              partition by aut.id_com_es
              ORDER BY aut.id_com_es ASC, aut.fecha DESC
             ) AS rn
            from rh_pn_as aut
            where
            extract(year from aut.fecha) = $id_anhos
        )phas
        on phas.id_com_es = tab0.id_comp_es
        and phas.rn = 1

        LEFT JOIN(
            SELECT
            tab1.id_dp,
            tab1.id_tipo_doc,
            tab1.num_doc,
            tab3.nombre_apellido,
            tab1.id_anhos as anho_estado_func,
            tab1.id_meses as meses_estado_func,
            tab1.periodo as id_periodo

            FROM no_estado_func_v tab1

            JOIN no_periodo tab2
            ON tab2.id_periodo = tab1.periodo
            AND tab2.id_anhos = tab1.id_anhos

            LEFT JOIN no_datos_funcionario_v tab3 ON tab3.num_doc=tab1.num_doc

            LEFT JOIN no_nombramiento_v nom ON nom.num_doc = tab1.num_doc

            WHERE
            tab1.id_meses = 12
            AND tab2.id_tipo_periodo = 1
            AND tab1.id_anhos = $id_anhos
            AND tab1.actual = 1
            and (nom.fecha_retiro is null or nom.fecha_retiro >= to_date( $id_anhos || '12-31','yyyy-mm-dd'))

        )tab1
        ON tab0.id_dp = tab1.id_dp

        left join(

          select
          agrup.id_concep_nomina,
          agrup.id_agrup_concepto,
          agrup.tipo_valor
          from no_agrup_deta agrup

          where
          agrup.id_agrup_concepto = 111

          union

          select
          agrup.id_concep_nomina,
          agrup.id_agrup_concepto,
          'X' as tipo_valor
          from no_agrup_deta agrup

          where
          agrup.id_agrup_concepto = (select id_agrup_cesanu from no_param_concep where id_param_concep = 1)

        )tab2 on tab2.id_agrup_concepto != 0

        left join no_concep_nomina tab3 on tab3.id_concep_nomina=tab2.id_concep_nomina

        LEFT JOIN no_param_concep tab4 ON tab4.id_param_concep = 1
        
        join (
        
            select distinct 
            liq.num_doc,
            liq.id_concep_nomina
            from no_liquida_nomina liq
            left join no_periodo per
            on per.id_periodo = liq.id_periodo
            where
            per.id_anhos = $id_anhos
            
            union
            
            select distinct 
            liq.num_doc,
                (select
                max(agrup.id_concep_nomina)
                from no_agrup_deta agrup
                where
                agrup.id_agrup_concepto = (select id_agrup_cesanu from no_param_concep where id_param_concep = 1) ) as id_concep_nomina
            from no_liquida_nomina liq
            left join no_periodo per
            on per.id_periodo = liq.id_periodo
            where
            per.id_anhos = $id_anhos
        
        )liq
        on liq.num_doc = tab1.num_doc 
        and liq.id_concep_nomina = tab2.id_concep_nomina
                    
        where
        1 = 1
        $wherePlanta

    )tab0

    group by
    tab0.id_dp,
    tab0.codigo,
    tab0.id_comp_es,
    tab0.id_planta_p,
    tab0.codigo_concepto,
    tab0.denominacion_comp,
    tab0.grado_comp,
    tab0.codigo_tipo_vinc,
    tab0.num_doc,
    tab0.nombre_apellido

)tab0
)tab0
                    
where
1 = 1
$wherePlanta
$whereComponenteEscala 
                    
group by tab0.id_comp_es,
tab0.id_planta_p,tab0.codigo_concepto,tab0.denominacion_comp,
tab0.grado_comp,
tab0.codigo_tipo_vinc,tab0.val_concep_basica,
tab0.val_concep_gasto_repre,
tab0.factor_concep_gasto_repre,
tab0.val_concep_primtec_sal,
tab0.factor_concep_primtec_sal,
tab0.val_concep_primgest,
tab0.factor_concep_primgest,
tab0.val_concep_primloc,
tab0.factor_concep_primloc,
tab0.val_concep_primcoor,
tab0.factor_concep_primcoor,
tab0.val_concep_primries,
tab0.factor_concep_primries,
tab0.val_concep_primaextra,
tab0.factor_concep_primaextra,
tab0.val_concep_primalt,
tab0.factor_concep_primalt,
tab0.val_concep_subalim,
tab0.factor_concep_subalim,
tab0.val_concep_auxtrans,
tab0.factor_concep_auxtrans,
tab0.val_concep_primant,
tab0.factor_concep_primant,
tab0.val_concep_primserv,
tab0.factor_concep_primserv,
tab0.val_concep_primnav,
tab0.factor_concep_primnav,
tab0.val_concep_bonif,
tab0.factor_concep_bonif,
tab0.val_concep_bonfrec,
tab0.factor_concep_bonfrec,
tab0.val_concep_primvac,
tab0.factor_concep_primvac,
tab0.val_concep_primact,
tab0.factor_concep_primact,
tab0.val_concep_otraprim_sal,
tab0.val_concep_otraprim_no_sal,
tab0.val_concep_cesanu
EOD;

            /*
             * sql que trae los resultados
             * por los filtros, para totalizar las filas
             */
//            $sql = <<<EOD
//select count(*) as count from (
//
//$sql_consulta
//                    
//)
//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 (
//    
//$sql_consulta
//
//ORDER BY 
//tab0.denominacion_comp,
//tab0.grado_comp
//          
//) row_ WHERE ROWNUM <=  $rowEnd   ) where rownum_ >  $rowStart
//EOD;
//            //ejecuta y obtiene el resultado del sql
//            $resultado = CN_TABLE::executeSQL($sql);


            //obtener el listado de recaudos
            $sql = <<<EOD
    
$sql_consulta

ORDER BY 
tab0.denominacion_comp,
tab0.grado_comp
EOD;
            //ejecuta y obtiene el resultado del sql
            $resultado = CN_TABLE::executeSQL($sql);


            $this->totalRows = count($resultado);
            $this->startRow = 0;
            $this->endRow = 0 + count($resultado);
            $this->list_data = $resultado;
            $this->logMessage("[inf_Costos_CGN] 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());
        }
    }

}
