<?php

/**
 * inf_costos_cgn_consol actions.
 *
 * @package    nomina
 * @subpackage inf_costos_cgn_consol
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 23810 2009-11-12 11:07:44Z Kris.Wallsmith $
 */
class inf_costos_cgn_consolActions 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.codigo_concepto,
tab0.denominacion_comp,
tab0.grado_comp desc,
tab0.codigo_tipo_vinc ';
        }



        /*
         * 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
'D' as sod,                    
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,

sum(tab0.val_concep_basica) as val_concep_basica,
sum(tab0.val_concep_gasto_repre) as val_concep_gasto_repre,
max(tab0.factor_concep_gasto_repre) as factor_concep_gasto_repre,
sum(tab0.val_concep_primtec_sal) as val_concep_primtec_sal,
max(tab0.factor_concep_primtec_sal) as factor_concep_primtec_sal,
sum(tab0.val_concep_primgest) as val_concep_primgest,
max(tab0.factor_concep_primgest) as factor_concep_primgest,
sum(tab0.val_concep_primloc) as val_concep_primloc,
max(tab0.factor_concep_primloc) as factor_concep_primloc,
sum(tab0.val_concep_primcoor) as val_concep_primcoor,
max(tab0.factor_concep_primcoor) as factor_concep_primcoor,
sum(tab0.val_concep_primries) as val_concep_primries,
max(tab0.factor_concep_primries) as factor_concep_primries,
sum(tab0.val_concep_primaextra) as val_concep_primaextra,
max(tab0.factor_concep_primaextra) as factor_concep_primaextra,
sum(tab0.val_concep_primalt) as val_concep_primalt,
max(tab0.factor_concep_primalt) as factor_concep_primalt,
sum(tab0.val_concep_subalim) as val_concep_subalim,
max(tab0.factor_concep_subalim) as factor_concep_subalim,
sum(tab0.val_concep_auxtrans) as val_concep_auxtrans,
max(tab0.factor_concep_auxtrans) as factor_concep_auxtrans,
sum(tab0.val_concep_primant) as val_concep_primant,
max(tab0.factor_concep_primant) as factor_concep_primant,
sum(tab0.val_concep_primserv) as val_concep_primserv,
max(tab0.factor_concep_primserv) as factor_concep_primserv,
sum(tab0.val_concep_primnav) as val_concep_primnav,
max(tab0.factor_concep_primnav) as factor_concep_primnav,
sum(tab0.val_concep_bonif) as val_concep_bonif,
max(tab0.factor_concep_bonif) as factor_concep_bonif,
sum(tab0.val_concep_bonfrec) as val_concep_bonfrec,
max(tab0.factor_concep_bonfrec) as factor_concep_bonfrec,
sum(tab0.val_concep_primvac) as val_concep_primvac,
max(tab0.factor_concep_primvac) as factor_concep_primvac,
sum(tab0.val_concep_primact) as val_concep_primact,
max(tab0.factor_concep_primact) as factor_concep_primact,
sum(tab0.val_concep_otraprim_sal) as val_concep_otraprim_sal,
sum(tab0.val_concep_otraprim_no_sal) as val_concep_otraprim_no_sal,
sum(tab0.val_concep_cesanu) as 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 tab0.valor 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,
    --0 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,
        
        tab1.num_doc as num_doc,
        tab1.nombre_apellido,
        case 
            when ajus.id_concep_nomina is not null then ajus.id_concep_nomina 
            when rein.id_concep_nomina is not null then rein.id_concep_nomina 
            else tab1.id_concep_nomina end as id_concep_nomina,
        tab1.valor_liquidado as valor,
        
        tab3.factor_salarial,
        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,
        (select
                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_cesanu
                
        from no_dp_v tab0
        
        left join(
        
            select
            liq.id_liquida_nomina as tag_id_union,
            liq.id_concep_nomina,
            case when agrup.operador = 1 then (1) else (-1) end * liq.valor_liquidado as valor_liquidado,
            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_liquida_nomina liq
            
            join no_estado_func_v tab1
            on  liq.num_doc = tab1.num_doc
            and liq.id_tipo_doc = tab1.id_tipo_doc
            and liq.id_periodo = tab1.periodo
        
            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
            and tab3.id_tipo_doc = tab1.id_tipo_doc
            
            join (
            
                select
                1 as operador,
                agrup.id_concep_nomina
                from no_agrup_deta agrup
                
                where
                agrup.id_agrup_concepto = 111
                
                union
                
                select
                1 as operador,
                con.id_concep_ajuste
                from no_agrup_deta agrup
                join no_concep_nomina con
                on con.id_concep_nomina = agrup.id_concep_nomina
                
                where
                agrup.id_agrup_concepto = 111
                
                union
                
                select                
                0 as operador,
                con.id_concep_rein
                from no_agrup_deta agrup
                join no_concep_nomina con
                on con.id_concep_nomina = agrup.id_concep_nomina
                
                where
                agrup.id_agrup_concepto = 111
                    
            )agrup
            on agrup.id_concep_nomina = liq.id_concep_nomina
        
            where
            tab1.id_anhos = $id_anhos
            
            union
            
            select
            ces.id_cesantia as tag_id_union,
            (
                select
                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,
            ces.valor_consolidado as valor_liquidado,
            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_cesantia ces
                        
            join no_estado_func_v tab1
            on  ces.num_doc = tab1.num_doc
            and ces.id_tipo_doc = tab1.id_tipo_doc
            and ces.id_periodo = tab1.periodo
                    
            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
            and tab3.id_tipo_doc = tab1.id_tipo_doc
            
            where
            tab1.id_anhos = $id_anhos
            and ces.valor_consolidado != 0
        
        )tab1
        on tab0.id_dp = tab1.id_dp
        
        left join no_concep_nomina tab3 
        on tab3.id_concep_nomina = tab1.id_concep_nomina
        
        left join no_concep_nomina ajus 
        on ajus.id_concep_ajuste = tab1.id_concep_nomina
        
        left join no_concep_nomina rein 
        on rein.id_concep_rein = tab1.id_concep_nomina
        
        left join no_param_concep tab4 
        on tab4.id_param_concep = 1
        
        --where     tab1.num_doc = 10011338
    
    )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

where
1 = 1
$wherePlanta
$whereComponenteEscala

group by 
tab0.codigo_concepto,
tab0.denominacion_comp,
tab0.grado_comp,
tab0.codigo_tipo_vinc 
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

$sortBy 
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());
        }
    }

}
