<?

//En linux 
$salto_linea = "\n"; //variable global para manejo del salto de linea
//En windows 
$salto_linea = "\r\n"; //variable global para manejo del salto de linea

$scarpeta = "uploads"; //carpeta donde guardar el archivo.

$sfile = $scarpeta . "/archivos_banco/PlanillaAutoliquidacionContratistas_" . date("d") . date("m") . date("Y") . ".txt"; //ruta del archivo a generar
$fp = fopen($sfile, "w");

$shtml = "";
$shtmlAuto = "";

$consul = Doctrine_Manager::getInstance()->connection();

//ARCHIVO R1DATGEN Datos Generales

$archivo = fopen("uploads/archivos_banco/r1datgen.csv", "w"); //generacion del nuevo documento txt.
//$shtml = "R101\tR102\tR103\tR104\tR105\tR106\tR107A\tR107B\tR107C\tR107\tR108\tR109\tR110\tR111\tR112\tR113\tR114\tR115\tR116\tR120A\tR117\t\n";
//$shtml .="01\t00001\tAGENCIA NACIONAL DE MINERIA\tNI\t900500018\t2\tE\t0000000000\t\tU\t\t\t14-25\t$vig_ini-$per_ini\t$vig_fin-$per_fin\t\t\t00180\t000517709000\t1\t00\t\n";

$sql = "
select 
'01' AS C1,
'00001' AS C2,
RPAD( translate(tab0.nombre_empresa,'áéíóúÁÉÍÓÚ','aeiouAEIOU') ,200,' ') AS C3,
'NI' AS C4,
rPAD(tab0.nit,16,' ') AS C5,
digito_verificacion(tab0.nit) AS C6,
'Y' AS C7,
lpad(0,10,0) AS C8,
rPAD(' ',10,' ') AS C9,
'U' AS C10,
rPAD(' ',10,' ') as c11,
rPAD(' ',40,' ') AS C12,
rpad(riesgo.codigo, 6, ' ') as c13,
'$vig_fin-' || lpad($per_fin,2,0) as c14,
'$vig_ini-' || lpad($per_ini,2,0)  AS C15,
rPAD(' ',10,' ') AS C16,
rPAD(' ',10,' ') AS C17,
--lpad(tab.cant + kardex1.cant_ka - salud1.cant_ka,5,0) as c18,
--lpad(kardex1.cant_ka + salud1.cant_ka,5,0) as c18,
lpad(tab.cant,5,0) as c18,
lpad(tab.total,12,0) as c19,
lpad(1,1,0) as c20,
lpad(0,2,0) as c21

from datos_compania tab0

left join 
(
    select 
    count(total.num_doc) as cant, 
    sum(total.total) as total 
    from (
      select 
      tab0.num_doc as num_doc, 
      sum(tab0.APORTE_PATRONO) as total 
      from cont_liquida_aporte tab0
      where tab0.id_periodo = $id_periodo
      group by tab0.num_doc 
   )total   
)tab ON 1 = 1

/*
Trae el maximo codigo de riesgo de las afiliaciones - 20130820
*/
left join 
(
    select distinct nvl(codfon.codigo,' ') as codigo 
    from no_mae_fondos maefon
    left join no_cod_fondo codfon
    on maefon.num_doc = codfon.nitocc
    where maefon.id_mae_fondos = (
        select max(id_mae_fondos) as id_mae_fondos 
        from no_afilia_fondo 
        where id_clase_fondo = 3
    )
)riesgo ON 1 = 1


left join 
(
    select 
    count(total.num_doc) as cant, 
    sum(total.aporte_ibc) as total1, 
    sum(total.IBC) as total 
    from (
        select tab0.num_doc, sum(nvl(tab0.valor_base,0)) as aporte_IBC, sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, 
        MAX(nvl(tab0.valor_base,0)) AS IBC,
        max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
        from no_liquida_aporte tab0
        where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 5
        group by tab0.num_doc
   )total   
)icbf1 ON 1 = 1


left join
(
    select 
    count(total.num_doc) as cant_ka, 
    sum(total.CANTIDAD) as total1_ka, 
    sum(total.CANTIDAD) as total_ka 
    from (
      select tab0.num_doc, count(tab0.id_novedad_kardex) as CANTIDAD
      from no_novedad_kardex tab0
      left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
      where tab1.afecta_antigu = 1 and tab0.estado = 1 and tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
      and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
      group by tab0.num_doc
    )total
)kardex1 ON 1 = 1


left join
(
    select 
    sum( round( nvl(tab6.asignacion_basica,0)/30*(30-nvl(salud1.dias-nvl(kardex4.cantidad,0),0))  ,-3) ) as valor
    from no_estado_func_v tab0    
    
    --left join no_nombramiento tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
    left join (
      SELECT
       aut.*,
       ROW_NUMBER() OVER (
        partition by aut.num_doc
        ORDER BY aut.estado DESC
       ) AS rn
      from no_nombramiento aut
    )tab5
    on tab5.id_tipo_doc = tab0.id_tipo_doc 
    and tab5.num_doc = tab0.num_doc
    and tab5.rn = 1
    
    left join no_dp_v tab6 on tab6.id_dp = tab0.id_dp

    left join
    (
    select tab0.num_doc, count(tab0.id_novedad_kardex) CANTIDAD, max(tab1.suspension) as max_marca
    from no_novedad_kardex tab0
    left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
        where tab1.afecta_antigu = 1 and tab0.estado = 1 
         /*   
        and (
          tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
          or
          tab0.fecha_fin between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
          or 
          tab0.id_periodo = $id_periodo
        )*/
        and
        (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo)
        and (
          to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_ini and tab0.fecha_fin
          or
          trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) between tab0.fecha_ini and tab0.fecha_fin
          or 
          tab0.id_periodo = $id_periodo
        )
        group by tab0.num_doc
    )kardex1 ON kardex1.num_doc = tab0.num_doc

    left join
    (
        select tab0.num_doc, sum(tab0.num_dias) CANTIDAD
        from no_novedad_kardex tab0
        left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
        where tab1.id_tip_nov_ka=4 and tab0.estado = 1 and tab0.fecha_inc between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
        and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
        group by tab0.num_doc
    )kardex4 ON kardex4.num_doc = tab0.num_doc

    left join 
    (
        select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC,
        max(tab0.porcentaje_patrono)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
        from no_liquida_aporte tab0
        where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 2
        group by tab0.num_doc
    )salud1 ON salud1.num_doc = tab0.num_doc


    WHERE tab0.periodo = $id_periodo 
    and (tab0.actual = 1 or
      ( tab0.actual = 0 
        and tab5.fecha_termina != to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
        and tab5.fecha_termina
      between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
      and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') ))
      )
    )
    and kardex1.cantidad > 0    
)kardex4 on 1 = 1


left join 
(
  select 
    count(total.num_doc) as cant_ka
    from (
      select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC,
      max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
      from no_liquida_aporte tab0
      where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 2
      group by tab0.num_doc
    )total
    where total.dias = 0
)salud1 ON 1 = 1
   
";

//                 echo $sql;



$st = $consul->execute($sql);
$detalle = $st->fetchAll();

/*
 * recorrer arreglo de datos y convertir en csv
 */
$i = 0;
$j = 0;
$sep = ""; //variable global para manejo del separador
foreach ($detalle as $rec) {
//        $shtml.='"';
    foreach ($rec as $key => $value) {
        if ($j != 0) {
//            if ($value != '/') {
            $shtml.= $sep;
//            }
        }
//        $value = str_replace('Ñ', '#', $value);
//        $value = str_replace('ñ', '#', $value);
//        $value = str_replace('[\n|\r|\n\r]', ' ', $value);
        $shtml.= $value;
        $j++;
    }
//        $shtml.='"';
//        $this->logMessage("Linea: " . $i++);
    //salto de linea para separar totales
    $shtml.=$salto_linea;
    $j = 0;
}

fwrite($archivo, $shtml);
fclose($archivo);

$shtmlAuto .= $shtml;
$shtml = "";

//ARCHIVO R2LIQDAP Detalle     
$archivo = fopen("uploads/archivos_banco/r2liqdap.csv", "w"); //generacion del nuevo documento txt.
//$shtml = "R201\tR202\tR203\tR204\tR206\tR206A\tR206B\tR206C\tR206D\tR206E\tR207\tR208\tR209\tR210\tR211\tR212\tR213\tR214\tR215\tR216\tR217\tR218\tR219\tR220\tR221\tR222\tR223\tR224\tR225\tR226\tR227\tR228\tR229\tR230\tR231\tR232\tR233\tR234\tR239A\tR235\tR241A\tR236\tR237\tR238\tR239\tR241\tR242\tR243\tR244\tR245\tR246\tR246A\tR247\tR248\tR249\tR250\tR251\tR252\tR253\tR254\tR255\tR256\tR257\tR258\tR259\tR260\tR261\tR262\tR263\tR264\tR265\tR266\tR267\t\n";
//$shtml .= "";

$sql = "
select 
tab.c1,
lpad(rownum,5,0) as c2,
tab.c3,
case when tab.c0 = 0 then tab.c4 else tab.c4a end c4,
tab.c5,
tab.c6,
tab.c7,
tab.c8,
tab.c9,
tab.c10,
case when tab.c0 = 0 then translate(tab.c11,'áéíóúÁÉÍÓÚñÑ','aeiouAEIOUnN') else translate(tab.c11a,'áéíóúÁÉÍÓÚñÑ','aeiouAEIOUnN') end c11,
translate(tab.c12,'áéíóúÁÉÍÓÚñÑ','aeiouAEIOUnN') as c12,
translate(tab.c13,'áéíóúÁÉÍÓÚñÑ','aeiouAEIOUnN') as c13,
translate(tab.c14,'áéíóúÁÉÍÓÚñÑ','aeiouAEIOUnN') as c14,
tab.c15,
tab.c16,
tab.c17,
tab.c18,
tab.c19,
tab.c20,
tab.c21,
tab.c22,
tab.c23,
tab.c24,
tab.c25,
tab.c26,
tab.c27,
tab.c28,
tab.c29,
tab.c30,
tab.c31,
tab.c32,
tab.c33,
tab.c34,
tab.c35,
tab.c36,
tab.c37,
tab.c38,
tab.c39,
tab.c40,
tab.c41,
tab.c42,
tab.c43,
tab.c44,
tab.c45,
tab.c46,
tab.c47,
tab.c48,
tab.c49,
tab.c50,
tab.c51,
tab.c52,
tab.c53,
tab.c54,
tab.c55,
tab.c56,
tab.c57,
tab.c58,
tab.c59,
tab.c60,
tab.c61,
tab.c62,
tab.c63,
tab.c64,
tab.c65,
tab.c66,
tab.c67,
tab.c68,
tab.c69,
tab.c70,
tab.c71,
tab.c72,
tab.c73,
tab.c74,
tab.c75,
tab.c76,
tab.c77,
tab.c78

from(
    
    select taborder.* from (
    
        /*
        *Registro 2 - Funcionarios con reporte de fondos
        */
        
        select 
        
        0 as c0,
        '02' AS C1,
        LPAD(rownum,5,0) AS C2,
        'CC' AS C3,
        RPAD(to_char(tab0.num_doc),16,' ') AS C4,
        rpad(to_char(tab0.num_doc),16,' ') as c4a,
        '03' AS C5,
        '00' AS C6,
        ' ' AS C7,
        ' ' AS C8,
        RPAD(nvl(tab2.coddepto,' '),2,' ') AS C9,
        RPAD(nvl(substr(tab3.codmun_text,3),' '),3,' ') AS C10,
        RPAD(nvl(tab0.primer_apellido,' '),20,' ') AS C11,
        RPAD(nvl(tab0.primer_apellido,' '),20,' ') AS C11a,
        RPAD(nvl(tab0.segundo_apellido,' '),30,' ') AS C12,
        RPAD(nvl( replace( tab0.primer_nombre,' ','') ,' '),20,' ') AS C13,
        rpad(nvl( replace( tab0.segundo_nombre,' ',''),' '),30,' ') as c14,
        case when tab5.fecha_ini
          between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
          then 'X' else ' ' end AS C15,
        case when tab5.fecha_fin
          between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
          then 'X' else ' ' end AS C16,
        
        ' ' AS C17,
        ' ' AS C18,
        ' ' AS C19,
        ' ' AS C20,
        
        case when nvl(ascenso.cantidad,0) > 0 or nvl(tab5.sueldo,0) != 0 then ' ' else ' 'end as c21,
        
        ' ' AS C22,
        ' ' AS C23,

        case when kardex1.cantidad>0 and  max_marca = 0 then 'X' when kardex1.cantidad>0 and  max_marca = 1 then 'C' else ' 'end AS C24,

        ' ' AS C25,
        ' ' AS C26,
        ' ' AS C27,
        ' ' AS C28,
        ' ' as c29,
        LPAD(to_char(nvl(0,0)),2,0) AS C30,
        rpad(nvl(null,' '),6,' ') as c31,
        rPAD(' ',6,' ') AS C32,
        rPAD(nvl(null,' '),6,' ') AS C33,
        rpad(' ',6,' ') as c34,
        rPAD(nvl(null,' '),6,' ') AS C35,
        LPAD(nvl(null,0),2,0) AS C36,
        lpad(nvl(null,0),2,0) as c37,
        LPAD(nvl(riesgo1.dias,0) ,2,0) AS C38,
        LPAD(nvl(null,0),2,0) AS C39,
        LPAD(nvl(tab5.sueldo,0),9,0) AS C40,
        ' ' AS C41,
        LPAD(nvl(null,0),9,0) AS C42,
        LPAD(nvl(null,0),9,0) AS C43,
        LPAD(nvl(riesgo1.ibc,0),9,0) AS C44,
        LPAD(nvl(null,0),9,0) AS C45,
        rpad( case when nvl(null,0) = 0 then '0.0' when nvl(null,0) < 1 then '0' else '' end || + nvl(null,0) ,7,0) as C46,
        lPAD(nvl(null,0) + nvl(null,0),9,0) AS C47,
        lPAD(0,9,0) AS C48,
        lpad(0,9,0) as C49,
        lpad(nvl(null,0) + nvl(null,0),9,0) as C50,
        lpad(nvl(null,0),9,0) as c51,
        lpad(nvl(null,0) - nvl(null,0) ,9,0) as c52,
        rpad(0,9,0) as c53,
        rpad( case when nvl(null,0) = 0 then '0.0' when nvl(null,0) < 1 then '0' else '' end || + nvl(null,0) ,7,0) as c54,
        lpad( nvl(null,0) + nvl(null,0) ,9,0) as c55,
        lpad( case when '01' = '40' then 0 else 0 end ,9,0) as c56,
        rpad(' ',15,' ') as c57,
        rpad(0,9,0) as C58,
        rpad(' ',15,' ') as C59,
        rpad(0,9,0) as c60,
        
        rpad( case when nvl(riesgo1.tarifa,0) = 0 then '0.0' when nvl(riesgo1.tarifa,0) < 1 then '0' else '' end || + nvl(riesgo1.tarifa,0) ,9,0) as c61,
        lpad(nvl(riesgo.centro_trabajo,0),9,0) as C62,
        lpad( nvl(riesgo1.aporte_patrono,0) ,9,0) as c63,
        
        rpad( case when nvl(null,0) = 0 then '0.0' when nvl(null,0) < 1 then '0' else '' end || + nvl(null,0) ,7,0) AS C64,
        lpad( nvl(null,0) ,9,0) as c65,
        rpad( case when nvl(null,0) = 0 then '0.0' when nvl(null,0) < 1 then '0' else '' end || + nvl(null,0) ,7,0) as C66,
        lpad( nvl(null,0) ,9,0) as c67,
        rpad( case when nvl(null,0) = 0 then '0.0' when nvl(null,0) < 1 then '0' else '' end || + nvl(null,0) ,7,0) as C68,
        lpad( nvl(null,0) ,9,0) as c69,
        rpad('0.0',7,'0') as c70,
        lpad(0,9,0) as c71,
        rpad('0.0',7,'0') as c72,
        lpad(0,9,0) as C73,
        '  ' AS C74,
        RPAD(to_char(' '),16,' ') AS C75,
        RPAD(to_char('N'),1,' ') AS C76,
        RPAD(riesgo_cod.codigo,6,' ') AS C77,
        RPAD(to_char('5'),1,' ') AS C78
        
        from contratista tab0
        
        --join cont_contrato tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
        left join (
          SELECT
           aut.*,
           ROW_NUMBER() OVER (
            partition by aut.num_doc
            ORDER BY aut.estado DESC
           ) AS rn
          from cont_contrato aut
        )tab5
        on tab5.id_tipo_doc = tab0.id_tipo_doc 
        and tab5.num_doc = tab0.num_doc
        and tab5.rn = 1
        
        join cont_afilia_fondo afilia 
        on afilia.id_tipo_doc = tab0.id_tipo_doc 
        and afilia.num_doc = tab0.num_doc 
        and (
        (select fecha_fin from no_periodo where id_periodo =  $id_periodo)
        between afilia.fecha_ingreso and nvl( afilia.fecha_retiro, trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) )
        or
        (select fecha_ini from no_periodo where id_periodo =  $id_periodo)
        between afilia.fecha_ingreso and nvl( afilia.fecha_retiro, trunc(( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) )
          )
        --and afilia.estado = 1
        
        left join departamento tab2 on tab2.id_depto = tab5.id_depto_contrato
        left join municipios tab3 ON tab3.codmun = tab5.ID_MUNI_CONTRATO
        
        left join
        (
          select tab0.num_doc,count(tab0.id_novedad_personal) AS CANTIDAD
          from no_novedad_personal tab0
          where tab0.id_novedad= 2 and tab0.fecha_inicial  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
          group by tab0.num_doc
        ) ascenso ON ascenso.num_doc = tab0.num_doc
        
        left join
        (
            select num_doc, sum(cantidad) AS CANTIDAD
            from(
            
                select tab0.num_doc,count(tab0.id_novedad_personal) AS CANTIDAD
                from no_novedad_personal tab0
                where tab0.id_novedad= 3 and tab0.fecha_inicial  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
                and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
                group by tab0.num_doc
                
                union 
                
                select tab0.num_doc,count(tab0.id_bonifica_servicios) AS CANTIDAD
                from no_bonifica_servicios tab0
                where tab0.estado=0 and tab0.fecha_bonifica between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
                and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
                group by tab0.num_doc
                
                union 
                
                select tab0.num_doc,count(tab0.id_hr_extra) AS CANTIDAD
                from no_hr_extra tab0
                left join no_lote_hr_extra tab1 ON tab1.id_lote_hr_extra = tab0.id_lote_hr_extra
                where tab1.estado=0 and tab1.id_periodo = $id_periodo
                group by tab0.num_doc
            
            )GROUP BY num_doc
        
        ) encargo ON encargo.num_doc = tab0.num_doc
        
        LEFT JOIN 
        (
            select distinct afilia.num_doc, null as centro_trabajo
            from cont_afilia_fondo afilia
            where afilia.id_clase_fondo = 3 and afilia.estado = 1
        )riesgo ON riesgo.num_doc = tab0.num_doc
        

        /*
        Trae el maximo codigo de riesgo de las afiliaciones - 20130820
        */
        left join 
        (
            select distinct nvl(codfon.codigo,' ') as codigo 
            from no_mae_fondos maefon
            left join no_cod_fondo codfon
            on maefon.num_doc = codfon.nitocc
            where maefon.id_mae_fondos = (
                select max(id_mae_fondos) as id_mae_fondos 
                from no_afilia_fondo 
                where id_clase_fondo = 3
            )
        )riesgo_cod ON 1 = 1

        left join 
        (
            select tab0.num_doc, sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, 
            max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC,
            max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
            from cont_liquida_aporte tab0
            where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 3
            group by tab0.num_doc
        )riesgo1 ON riesgo1.num_doc = tab0.num_doc

        left join
        (
            select tab0.num_doc, count(tab0.id_novedad_kardex) CANTIDAD, max(tab1.suspension) as max_marca
            from cont_novedad_kardex tab0
            left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
            where tab1.afecta_antigu = 1 and tab0.estado = 1 
             /*   
            and (
              tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
              and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
              or
              tab0.fecha_fin between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
              and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
              or 
              tab0.id_periodo = $id_periodo
            )*/
            and
            (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo)
            and (
              to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_ini and tab0.fecha_fin
              or
              trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) between tab0.fecha_ini and tab0.fecha_fin
              or 
              tab0.id_periodo = $id_periodo
            )
            group by tab0.num_doc
        )kardex1 ON kardex1.num_doc = tab0.num_doc
        
        where tab5.estado = 1 
        $whereFuncionarioNumDoc $whereFuncionarioTipoDoc 
        and (
          tab5.fecha_fin >= trunc(/*last_day*/( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') ))
         /*
          and tab5.fecha_fin
              between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
              and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo_actual) ,'dd/mm/yyyy') ))
         */
        )
    
    )taborder
    
    order by taborder.c11, taborder.c12, taborder.c24, taborder.c0

)tab
";

//                 echo $sql;



$st = $consul->execute($sql);
$detalle = $st->fetchAll();

//foreach ($detalle as $detalle) {
//
//    $shtml.=$detalle['C1'] . "" . "" . $detalle['C2'] . "" . $detalle['C3'] . "" . $detalle['C4'] . "" . $detalle['C5'] . "" . $detalle['C6'] . "" . $detalle['C7'] . ""
//            . $detalle['C8'] . "" . $detalle['C9'] . "" . $detalle['C10'] . "" . $detalle['C11'] . "" . $detalle['C12'] . "" . $detalle['C13'] . "" . $detalle['C14'] . ""
//            . $detalle['C15'] . "" . $detalle['C16'] . "" . $detalle['C17'] . "" . $detalle['C18'] . "" . $detalle['C19'] . "" . $detalle['C20'] . "" . $detalle['C21'] . ""
//            . $detalle['C22'] . "" . $detalle['C23'] . "" . $detalle['C24'] . "" . $detalle['C25'] . "" . $detalle['C26'] . "" . $detalle['C27'] . "" . $detalle['C28'] . ""
//            . $detalle['C29'] . "" . $detalle['C30'] . "" . $detalle['C31'] . "" . $detalle['C32'] . "" . $detalle['C33'] . "" . $detalle['C34'] . "" . $detalle['C35'] . ""
//            . $detalle['C36'] . "" . $detalle['C37'] . "" . $detalle['C38'] . "" . $detalle['C39'] . "" . $detalle['C40'] . "" . $detalle['C41'] . "" . $detalle['C42'] . ""
//            . $detalle['C43'] . "" . $detalle['C44'] . "" . $detalle['C45'] . "\n";
//}

/*
 * recorrer arreglo de datos y convertir en csv
 */
$i = 0;
$j = 0;
$sep = ""; //variable global para manejo del separador
foreach ($detalle as $rec) {
//        $shtml.='"';
    foreach ($rec as $key => $value) {
        if ($j != 0) {
//            if ($value != '/') {
            $shtml.= $sep;
//            }
        }
//        $value = str_replace('Ñ', '#', $value);
//        $value = str_replace('ñ', '#', $value);
//        $value = str_replace('[\n|\r|\n\r]', ' ', $value);
        $shtml.= $value;
        $j++;
    }
//        $shtml.='"';
//        $this->logMessage("Linea: " . $i++);
    //salto de linea para separar totales
    $shtml.=$salto_linea;
    $j = 0;
}


fwrite($archivo, $shtml);
fclose($archivo);

$shtmlAuto .= $shtml;
$shtml = "";

//ARCHIVO R3TOTAPP Total Fondos Pension        
$archivo = fopen("uploads/archivos_banco/r3totapp.csv", "w"); //generacion del nuevo documento txt.
//$shtml = "R301\tR302\tR303\tR304\tR305\tR306\tR307\tR308\tR309\tR310A\tR310\tR311\tR312\tR313\tR314A\tR314,C,6\t\n";


$sql = "
select 

'03' as c1,
lpad(rownum,5,0) as c2,
tab.c3,
tab.C4,
tab.C5,
tab.c6,
tab.c7,
tab.c8,
tab.c9,
tab.c10a,
tab.c10,
tab.c11,
tab.c12,
tab.c13,
tab.c14a,
tab.c14

from (

select 

lpad(tab0.codigo,6,0) as c3,
lpad(tab0.nitocc,16,0) as c4,
digito_verificacion(lpad(tab0.nitocc,16,0)) as c5,
lpad(sum(tab0.aporte2),10,0) as c6,--pension
lpad(0,10,0) as c7,
lpad(0,10,0) as c8,
lpad(sum(tab0.aporte3),10,0) as c9,--fsp
lpad(' ',1,' ') as c10a,
lpad(0,4,0) as c10,
lpad(0,10,0) as c11,
lpad(0,10,0) as c12,
lpad(sum(tab0.aporte2) + sum(tab0.aporte3),10,0) as c13,--total
lpad(' ',1,' ') as c14a,
LPAD( 0,6,0) AS C14,
LPAD( count (tab0.nitocc),6,0) as c141

from (

select 

tab0.num_doc,
rpad(NVL(null, ' '),6,' ') AS codigo, 
rpad(nvl(null, ' '),15,' ') as tipo, 
nvl(null,0) as nitocc,
nvl(null,0) as aporte,
nvl(null,0) as aporte_patrono,
nvl(null,0) as aporte2,
nvl(null,0) as aporte_patrono2,
nvl(null,0) as aporte3,
nvl(null,0) as aporte_patrono3

from contratista tab0
        
        --join cont_contrato tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
        left join (
          SELECT
           aut.*,
           ROW_NUMBER() OVER (
            partition by aut.num_doc
            ORDER BY aut.estado DESC
           ) AS rn
          from cont_contrato aut
        )tab5
        on tab5.id_tipo_doc = tab0.id_tipo_doc 
        and tab5.num_doc = tab0.num_doc
        and tab5.rn = 1
        
        join cont_afilia_fondo afilia 
        on afilia.id_tipo_doc = tab0.id_tipo_doc 
        and afilia.num_doc = tab0.num_doc 
        and (select fecha_fin from no_periodo where id_periodo =  $id_periodo)
        between afilia.fecha_ingreso and nvl( afilia.fecha_retiro, trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) )
        --and afilia.estado = 1
        
        left join departamento tab2 on tab2.id_depto = tab5.id_depto_contrato
        left join municipios tab3 ON tab3.codmun = tab5.ID_MUNI_CONTRATO

left join 
(
    select 
    tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc,
    sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
    max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
    from no_liquida_aporte tab0
    left join no_mae_fondos fondo on fondo.id_mae_fondos = tab0.id_mae_fondos
    left join no_cod_fondo codigo ON codigo.nitocc = fondo.num_doc
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 1
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc
)pension1 ON pension1.num_doc = tab0.num_doc

left join 
(
    select 
    tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc,
    sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
    max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
    from no_liquida_aporte tab0
    left join no_mae_fondos fondo on fondo.id_mae_fondos = tab0.id_mae_fondos
    left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 1 and tab0.tarifa = 16
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc
)pension2 ON pension2.num_doc = tab0.num_doc

left join 
(
    select 
    tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc,
    sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
    max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
    from no_liquida_aporte tab0
    left join no_mae_fondos fondo on fondo.id_mae_fondos = tab0.id_mae_fondos
    left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 1 and tab0.tarifa = 1
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc
)pension3 ON pension3.num_doc = tab0.num_doc


        where tab5.estado = 1 
        --$whereFuncionarioNumDoc $whereFuncionarioTipoDoc 
        and (
          tab5.fecha_fin >= trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') ))
         /*
          and tab5.fecha_fin
              between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
              and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo_actual) ,'dd/mm/yyyy') ))
         */
        )

)tab0

group by 
tab0.codigo, tab0.tipo, tab0.nitocc

order by
tab0.codigo

)tab
";

$st = $consul->execute($sql);
$detalle = $st->fetchAll();

/*
 * recorrer arreglo de datos y convertir en csv
 */
$i = 0;
$j = 0;
$sep = ""; //variable global para manejo del separador
foreach ($detalle as $rec) {
//        $shtml.='"';
    foreach ($rec as $key => $value) {
        if ($j != 0) {
//            if ($value != '/') {
            $shtml.= $sep;
//            }
        }
//        $value = str_replace('Ñ', '#', $value);
//        $value = str_replace('ñ', '#', $value);
//        $value = str_replace('[\n|\r|\n\r]', ' ', $value);
        $shtml.= $value;
        $j++;
    }
//        $shtml.='"';
//        $this->logMessage("Linea: " . $i++);
    //salto de linea para separar totales
    $shtml.=$salto_linea;
    $j = 0;
}

fwrite($archivo, $shtml);
fclose($archivo);

$shtmlAuto .= $shtml;
$shtml = "";

//ARCHIVO R4TOTAPS Total Fondos Salud        
$archivo = fopen("uploads/archivos_banco/r4totaps.csv", "w"); //generacion del nuevo documento txt.
//$shtml = "R401\tR402\tR403\tR404\tR405\tR406\tR407\tR408\tR409\tR410\tR411\tR412\tR413\tR414\tR415\tR416\tR417\tR418\tR419\tR420\tR421\tR422\tR423\tR424\tR425,C,6\t\n";

$sql = "
select 

'04' as c1,
lpad(rownum,5,0) as c2,
tab.c3,
tab.C4,
tab.C5,
tab.c6,
tab.c7,
tab.c8,
tab.c9,
tab.c10,
tab.c11,
tab.c12,
tab.c13,
tab.c14,
tab.c15,
tab.c16,
tab.c17,
tab.c18,
tab.c19,
tab.c20,
tab.c21,
tab.c22,
tab.c23,
tab.c24,
tab.c25

from (

select 

lpad(tab0.codigo,6,0) as c3,
lpad(tab0.nitocc,16,0) as c4,
digito_verificacion(lpad(tab0.nitocc,16,0)) as c5,
lpad(sum(tab0.aporte),10,0) as c6,
lpad(sum(tab0.upc_adicio),10,0) as c7,
LPAD(' ',1,' ') as c8,
lpad(0,10,0) as c9,
LPAD(' ',1,' ') as c10,
lpad(0,10,0) as c11,
lpad(sum(tab0.aporte) + 0 + 0 ,10,0) as c12,
lpad(0,4,0) as c13,
lpad(0,10,0) AS C14,
lpad(0,10,0) as c15,
lpad(sum(tab0.aporte),10,0) as c16,
lpad(sum(tab0.upc_adicio),10,0) as c17,
LPAD(' ',1,' ') as c18,
lpad(0,10,0) as c19,
lpad(0,10,0) as c20,
lpad(sum(tab0.aporte) + 0 + 0 ,10,0) as c21,
lpad(sum(tab0.upc_adicio),10,0) as c22,
lpad(sum(tab0.upc_adicio) + sum(tab0.aporte) + 0 + 0,10,0) as c23,
lpad( round((sum(tab0.aporte) * 1 / 100) + 0 + 0) ,10,0) as c24,
LPAD( 0,6,0) as c25,
LPAD( count (tab0.nitocc),6,0) as c251

from (

    select 

    tab0.num_doc,
    rpad(NVL(null, ' '),6,' ') AS codigo, 
    rpad(nvl(null, ' '),15,' ') as tipo, 
    nvl(null,0) as nitocc,
    nvl(null,0) as aporte,
    nvl(null,0) as aporte_patrono,
    nvl(null,0) as upc_adicio

    from contratista tab0
        
        --join cont_contrato tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
        left join (
          SELECT
           aut.*,
           ROW_NUMBER() OVER (
            partition by aut.num_doc
            ORDER BY aut.estado DESC
           ) AS rn
          from cont_contrato aut
        )tab5
        on tab5.id_tipo_doc = tab0.id_tipo_doc 
        and tab5.num_doc = tab0.num_doc
        and tab5.rn = 1
        
        join cont_afilia_fondo afilia 
        on afilia.id_tipo_doc = tab0.id_tipo_doc 
        and afilia.num_doc = tab0.num_doc 
        and (select fecha_fin from no_periodo where id_periodo =  $id_periodo)
        between afilia.fecha_ingreso and nvl( afilia.fecha_retiro, trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) )
        --and afilia.estado = 1
        
        left join departamento tab2 on tab2.id_depto = tab5.id_depto_contrato
        left join municipios tab3 ON tab3.codmun = tab5.ID_MUNI_CONTRATO

left join 
(
    select 
    tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc,
    sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
    max(tab0.tarifa)/100 as TARIFA, sum(tab0.upc_adicio) as upc_adicio
    from no_liquida_aporte tab0
    left join no_mae_fondos fondo on fondo.id_mae_fondos = tab0.id_mae_fondos
    left join no_cod_fondo codigo ON codigo.nitocc = fondo.num_doc
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 2 AND codigo.tipo = 'EPS'
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc
)salud1 ON salud1.num_doc = tab0.num_doc


        where tab5.estado = 1 
        --$whereFuncionarioNumDoc $whereFuncionarioTipoDoc 
        and (
          tab5.fecha_fin >= trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') ))
         /*
          and tab5.fecha_fin
              between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
              and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo_actual) ,'dd/mm/yyyy') ))
         */
        )

)tab0

group by 
tab0.codigo, tab0.tipo, tab0.nitocc

order by
tab0.codigo

)tab
 
";

$st = $consul->execute($sql);
$detalle = $st->fetchAll();

/*
 * recorrer arreglo de datos y convertir en csv
 */
$i = 0;
$j = 0;
$sep = ""; //variable global para manejo del separador
foreach ($detalle as $rec) {
//        $shtml.='"';
    foreach ($rec as $key => $value) {
        if ($j != 0) {
//            if ($value != '/') {
            $shtml.= $sep;
//            }
        }
//        $value = str_replace('Ñ', '#', $value);
//        $value = str_replace('ñ', '#', $value);
//        $value = str_replace('[\n|\r|\n\r]', ' ', $value);
        $shtml.= $value;
        $j++;
    }
//        $shtml.='"';
//        $this->logMessage("Linea: " . $i++);
    //salto de linea para separar totales
    $shtml.=$salto_linea;
    $j = 0;
}

fwrite($archivo, $shtml);
fclose($archivo);

//$shtmlAuto .= $shtml;
//$shtml = "";

//ARCHIVO R5TOTAPR Total Fondos Riesgos        
$archivo = fopen("uploads/archivos_banco/r5totapr.csv", "w"); //generacion del nuevo documento txt.
//$shtml = "R501\tR502\tR503\tR504\tR505\tR506\tR507\tR508\tR509\tR510\tR511\tR512\tR513\tR514,\tR515\tR516\tR517\tR518\t\n";

$sql = "
select 

'05' as c1,
lpad(rownum,5,0) as c2,
tab.c3,
tab.C4,
tab.C5,
tab.c6,
tab.c7,
tab.c8,
tab.c9,
tab.c10,
tab.c11,
tab.c12,
tab.c13,
tab.c14,
tab.c15,
tab.c16,
tab.c17,
tab.c18

from (

select 

lpad(tab0.codigo,6,' ') as c3,
lpad(tab0.nitocc,16,0) as c4,
digito_verificacion(tab0.nitocc) as c5,
lpad(sum(tab0.aporte_patrono),10,0) as c6,
lpad(nvl(tab0.num_autorizacion,' '),15,' ') as c7,
lpad(nvl(tab0.valor_total,0),10,0) as c8,
lpad(nvl(tab0.valor_aporte,0),10,0) as c9,
lpad(sum(tab0.aporte_patrono) - nvl(tab0.valor_total,0) - nvl(tab0.valor_aporte,0),10,0) as c10,
lpad(0,4,0) as c11,
lpad(0,10,0) as c12,
lpad(sum(tab0.aporte_patrono) - nvl(tab0.valor_total,0) - nvl(tab0.valor_aporte,0),10,0) as c13,
lpad(' ',10,' ') as c14,
lpad(0,10,0) as c15,
lpad(sum(tab0.aporte_patrono) - nvl(tab0.valor_total,0) - nvl(tab0.valor_aporte,0),10,0) as c16,
lpad( round((sum(tab0.aporte_patrono) * 1 / 100) ),10,0) as c17,
LPAD( count (tab0.nitocc),6,0) as c18

from (

select 
autori.num_autorizacion,
autori.valor_total,
autori.valor_aporte,
tab0.num_doc,
rpad(NVL(riesgo1.codigo, ' '),6,' ') AS codigo, 
rpad(nvl(riesgo1.tipo, ' '),15,' ') as tipo, 
nvl(riesgo1.nitocc,0) as nitocc,
nvl(riesgo1.aporte,0) as aporte,
nvl(riesgo1.aporte_patrono,0) as aporte_patrono

        from contratista tab0
        
        --join cont_contrato tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
        left join (
          SELECT
           aut.*,
           ROW_NUMBER() OVER (
            partition by aut.num_doc
            ORDER BY aut.estado DESC
           ) AS rn
          from cont_contrato aut
        )tab5
        on tab5.id_tipo_doc = tab0.id_tipo_doc 
        and tab5.num_doc = tab0.num_doc
        and tab5.rn = 1
        
        join cont_afilia_fondo afilia 
        on afilia.id_tipo_doc = tab0.id_tipo_doc 
        and afilia.num_doc = tab0.num_doc 
        and (select fecha_fin from no_periodo where id_periodo =  $id_periodo)
        between afilia.fecha_ingreso and nvl( afilia.fecha_retiro, trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) )
        --and afilia.estado = 1
        
        left join departamento tab2 on tab2.id_depto = tab5.id_depto_contrato
        left join municipios tab3 ON tab3.codmun = tab5.ID_MUNI_CONTRATO

        left join 
        (
            select 
            tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc,
            sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
            max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
            from cont_liquida_aporte tab0
            left join no_mae_fondos fondo on fondo.id_mae_fondos = tab0.id_mae_fondos
            
            --left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc  
            left join (
              SELECT
               aut.*,
               ROW_NUMBER() OVER (
                partition by aut.nitocc
                ORDER BY aut.nitocc 
               ) as rn
              from no_cod_fondo aut
            )codigo
            on codigo.nitocc = fondo.num_doc
            and codigo.rn = 1
            
            where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 3
            group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc
        )riesgo1 ON riesgo1.num_doc = tab0.num_doc



left join (
select 
tab1.valor as valor_total,
tab1.aporte_eps + tab1.aporte_afp as valor_aporte,
rownum as rn, 
tab.* 

from(

  select 
  autori.* 
  from no_autorizacion autori
  where autori.id_periodo = $id_periodo
  order by 
  autori.fecha_proceso desc, autori.id_autorizacion desc

)tab

join (
  select 
  sum(nvl(aporte_eps,0)) as aporte_eps, 
  sum(nvl(aporte_afp,0)) as aporte_afp, 
  sum(nvl(valor,0)) as valor, 
  id_periodo 
  from no_autorizacion 
  group by   id_periodo
)tab1 on tab1.id_periodo = tab.id_periodo

where
rownum = 1
)autori on autori.rn = 1 

        where tab5.estado = 1 
        --$whereFuncionarioNumDoc $whereFuncionarioTipoDoc 
        and (
          tab5.fecha_fin >= trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') ))
         /*
          and tab5.fecha_fin
              between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
              and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo_actual) ,'dd/mm/yyyy') ))
         */
        )

)tab0

group by 
tab0.codigo, tab0.tipo, tab0.nitocc, tab0.num_autorizacion, tab0.valor_total, tab0.valor_aporte

order by
tab0.codigo

)tab
 
";

$st = $consul->execute($sql);
$detalle = $st->fetchAll();

/*
 * recorrer arreglo de datos y convertir en csv
 */
$i = 0;
$j = 0;
$sep = ""; //variable global para manejo del separador
foreach ($detalle as $rec) {
//        $shtml.='"';
    foreach ($rec as $key => $value) {
        if ($j != 0) {
//            if ($value != '/') {
            $shtml.= $sep;
//            }
        }
//        $value = str_replace('Ñ', '#', $value);
//        $value = str_replace('ñ', '#', $value);
//        $value = str_replace('[\n|\r|\n\r]', ' ', $value);
        $shtml.= $value;
        $j++;
    }
//        $shtml.='"';
//        $this->logMessage("Linea: " . $i++);
    //salto de linea para separar totales
    $shtml.=$salto_linea;
    $j = 0;
}

fwrite($archivo, $shtml);
fclose($archivo);

//$shtmlAuto .= $shtml;
//$shtml = "";


//ARCHIVO R6TOTAPC Total Fondos CCF        
$archivo = fopen("uploads/archivos_banco/r6totapc.csv", "w"); //generacion del nuevo documento txt.
//$shtml = "R601\tR602\tR603\tR604\tR605\tR606\tR607\tR608\tR609\tR610\t\n";

$sql = "
select 

'06' as c1,
lpad(rownum,5,0) as c2,
tab.c3,
tab.C4,
tab.C5,
tab.c6,
tab.c7,
tab.c8,
tab.c9,
tab.c10

from (

select 

lpad(tab0.codigo,6,' ') as c3,
lpad(tab0.nitocc,16,0) as c4,
digito_verificacion(tab0.nitocc) as c5,
lpad(sum(tab0.aporte_patrono),10,0) as c6,
lpad(0,4,0) as c7,
lpad(0,10,0) as c8,
lpad(sum(tab0.aporte_patrono),10,0) as c9,
LPAD( 0,6,0) as c10,
LPAD( count (tab0.nitocc),6,0) as c101

from (

    select 

    tab0.num_doc,
    rpad(NVL(null, ' '),6,' ') AS codigo, 
    rpad(nvl(null, ' '),15,' ') as tipo, 
    nvl(null,0) as nitocc,
    nvl(null,0) as aporte,
    nvl(null,0) as aporte_patrono

    from contratista tab0
        
        --join cont_contrato tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
        left join (
          SELECT
           aut.*,
           ROW_NUMBER() OVER (
            partition by aut.num_doc
            ORDER BY aut.estado DESC
           ) AS rn
          from cont_contrato aut
        )tab5
        on tab5.id_tipo_doc = tab0.id_tipo_doc 
        and tab5.num_doc = tab0.num_doc
        and tab5.rn = 1
        
        join cont_afilia_fondo afilia 
        on afilia.id_tipo_doc = tab0.id_tipo_doc 
        and afilia.num_doc = tab0.num_doc 
        and (select fecha_fin from no_periodo where id_periodo =  $id_periodo)
        between afilia.fecha_ingreso and nvl( afilia.fecha_retiro, trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) )
        --and afilia.estado = 1
        
        left join departamento tab2 on tab2.id_depto = tab5.id_depto_contrato
        left join municipios tab3 ON tab3.codmun = tab5.ID_MUNI_CONTRATO

left join 
(
    select 
    tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc,
    sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
    max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
    from no_liquida_aporte tab0
    left join no_mae_fondos fondo on fondo.id_mae_fondos = tab0.id_mae_fondos
    
    --left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc  
    left join (
      SELECT
       aut.*,
       ROW_NUMBER() OVER (
        partition by aut.nitocc
        ORDER BY aut.nitocc 
       ) as rn
      from no_cod_fondo aut
    )codigo
    on codigo.nitocc = fondo.num_doc
    and codigo.rn = 1
    
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 4 AND codigo.tipo = 'CCF'
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc
)ccf1 ON ccf1.num_doc = tab0.num_doc



        where tab5.estado = 1 
        --$whereFuncionarioNumDoc $whereFuncionarioTipoDoc 
        and (
          tab5.fecha_fin >= trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') ))
         /*
          and tab5.fecha_fin
              between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
              and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo_actual) ,'dd/mm/yyyy') ))
         */
        )

)tab0

group by 
tab0.codigo, tab0.tipo, tab0.nitocc

order by
tab0.codigo

)tab
 
";

$st = $consul->execute($sql);
$detalle = $st->fetchAll();

/*
 * recorrer arreglo de datos y convertir en csv
 */
$i = 0;
$j = 0;
$sep = ""; //variable global para manejo del separador
foreach ($detalle as $rec) {
//        $shtml.='"';
    foreach ($rec as $key => $value) {
        if ($j != 0) {
//            if ($value != '/') {
            $shtml.= $sep;
//            }
        }
//        $value = str_replace('Ñ', '#', $value);
//        $value = str_replace('ñ', '#', $value);
//        $value = str_replace('[\n|\r|\n\r]', ' ', $value);
        $shtml.= $value;
        $j++;
    }
//        $shtml.='"';
//        $this->logMessage("Linea: " . $i++);
    //salto de linea para separar totales
    $shtml.=$salto_linea;
    $j = 0;
}

fwrite($archivo, $shtml);
fclose($archivo);

//$shtmlAuto .= $shtml;
//$shtml = "";

//ARCHIVO R7TOTAPS Total Fondos SENA        
$archivo = fopen("uploads/archivos_banco/r7totapc.csv", "w"); //generacion del nuevo documento txt.
//$shtml = "R701\tR702\tR703\tR704\tR706\tR707\tR708\tR709\tR710\t\n";

$sql = "
select 

'07' as c1,
lpad(rownum,5,0) as c2,
tab.c3,
tab.C4,
tab.C5,
tab.c6,
tab.c7,
tab.c8,
tab.c9,
tab.c10

from (

select 

lpad(tab0.codigo,6,' ') as c3,
lpad(tab0.nitocc,16,0) as c4,
digito_verificacion(tab0.nitocc) as c5,
lpad(sum(tab0.aporte_patrono),10,0) as c6,
lpad(0,4,0) as c7,
lpad(0,10,0) as c8,
lpad(sum(tab0.aporte_patrono),10,0) as c9,
LPAD( 0,6,0) as c10,
LPAD( count (tab0.nitocc),6,0) as c101

from (

    select 

    tab0.num_doc,
    rpad(NVL(null, ' '),6,' ') AS codigo, 
    rpad(nvl(null, ' '),15,' ') as tipo, 
    nvl(null,0) as nitocc,
    nvl(null,0) as aporte,
    nvl(null,0) as aporte_patrono

    from contratista tab0
        
        --join cont_contrato tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
        left join (
          SELECT
           aut.*,
           ROW_NUMBER() OVER (
            partition by aut.num_doc
            ORDER BY aut.estado DESC
           ) AS rn
          from cont_contrato aut
        )tab5
        on tab5.id_tipo_doc = tab0.id_tipo_doc 
        and tab5.num_doc = tab0.num_doc
        and tab5.rn = 1
        
        join cont_afilia_fondo afilia 
        on afilia.id_tipo_doc = tab0.id_tipo_doc 
        and afilia.num_doc = tab0.num_doc 
        and (select fecha_fin from no_periodo where id_periodo =  $id_periodo)
        between afilia.fecha_ingreso and nvl( afilia.fecha_retiro, trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) )
        --and afilia.estado = 1
        
        left join departamento tab2 on tab2.id_depto = tab5.id_depto_contrato
        left join municipios tab3 ON tab3.codmun = tab5.ID_MUNI_CONTRATO

left join 
(
    select 
    tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc,
    sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
    max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
    from no_liquida_aporte tab0
    left join no_mae_fondos fondo on fondo.id_mae_fondos = tab0.id_mae_fondos
    
    --left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc  
    left join (
      SELECT
       aut.*,
       ROW_NUMBER() OVER (
        partition by aut.nitocc
        ORDER BY aut.nitocc 
       ) as rn
      from no_cod_fondo aut
    )codigo
    on codigo.nitocc = fondo.num_doc
    and codigo.rn = 1
    
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 6
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc
)sena1 ON sena1.num_doc = tab0.num_doc



        where tab5.estado = 1 
        --$whereFuncionarioNumDoc $whereFuncionarioTipoDoc 
        and (
          tab5.fecha_fin >= trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') ))
         /*
          and tab5.fecha_fin
              between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
              and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo_actual) ,'dd/mm/yyyy') ))
         */
        )

)tab0

group by 
tab0.codigo, tab0.tipo, tab0.nitocc

order by
tab0.codigo

)tab
 
";

$st = $consul->execute($sql);
$detalle = $st->fetchAll();

/*
 * recorrer arreglo de datos y convertir en csv
 */
$i = 0;
$j = 0;
$sep = ""; //variable global para manejo del separador
foreach ($detalle as $rec) {
//        $shtml.='"';
    foreach ($rec as $key => $value) {
        if ($j != 0) {
//            if ($value != '/') {
            $shtml.= $sep;
//            }
        }
//        $value = str_replace('Ñ', '#', $value);
//        $value = str_replace('ñ', '#', $value);
//        $value = str_replace('[\n|\r|\n\r]', ' ', $value);
        $shtml.= $value;
        $j++;
    }
//        $shtml.='"';
//        $this->logMessage("Linea: " . $i++);
    //salto de linea para separar totales
    $shtml.=$salto_linea;
    $j = 0;
}

fwrite($archivo, $shtml);
fclose($archivo);

//$shtmlAuto .= $shtml;
//$shtml = "";

//ARCHIVO R8TOTAPI Total Fondos ICBF        
$archivo = fopen("uploads/archivos_banco/r8totapi.csv", "w"); //generacion del nuevo documento txt.
//$shtml = "R801\tR802\tR803\tR804\tR806\tR807\tR808\tR809\tR810\t\n";

$sql = "
select 

'08' as c1,
lpad(rownum,5,0) as c2,
tab.c3,
tab.C4,
tab.C5,
tab.c6,
tab.c7,
tab.c8,
tab.c9,
tab.c10

from (

select 

lpad(tab0.codigo,6,' ') as c3,
lpad(tab0.nitocc,16,0) as c4,
digito_verificacion(tab0.nitocc) as c5,
lpad(sum(tab0.aporte_patrono),10,0) as c6,
lpad(0,4,0) as c7,
lpad(0,10,0) as c8,
lpad(sum(tab0.aporte_patrono),10,0) as c9,
LPAD( 0,6,0) as c10,
LPAD( count (tab0.nitocc),6,0) as c101

from (

    select 

    tab0.num_doc,
    rpad(NVL(null, ' '),6,' ') AS codigo, 
    rpad(nvl(null, ' '),15,' ') as tipo, 
    nvl(null,0) as nitocc,
    nvl(null,0) as aporte,
    nvl(null,0) as aporte_patrono

    from contratista tab0
        
        --join cont_contrato tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
        left join (
          SELECT
           aut.*,
           ROW_NUMBER() OVER (
            partition by aut.num_doc
            ORDER BY aut.estado DESC
           ) AS rn
          from cont_contrato aut
        )tab5
        on tab5.id_tipo_doc = tab0.id_tipo_doc 
        and tab5.num_doc = tab0.num_doc
        and tab5.rn = 1
        
        join cont_afilia_fondo afilia 
        on afilia.id_tipo_doc = tab0.id_tipo_doc 
        and afilia.num_doc = tab0.num_doc 
        and (select fecha_fin from no_periodo where id_periodo =  $id_periodo)
        between afilia.fecha_ingreso and nvl( afilia.fecha_retiro, trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) )
        --and afilia.estado = 1
        
        left join departamento tab2 on tab2.id_depto = tab5.id_depto_contrato
        left join municipios tab3 ON tab3.codmun = tab5.ID_MUNI_CONTRATO

    left join 
    (
        select 
        tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc,
        sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
        max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
        from no_liquida_aporte tab0
        left join no_mae_fondos fondo on fondo.id_mae_fondos = tab0.id_mae_fondos

        --left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc  
        left join (
          SELECT
           aut.*,
           ROW_NUMBER() OVER (
            partition by aut.nitocc
            ORDER BY aut.nitocc 
           ) as rn
          from no_cod_fondo aut
        )codigo
        on codigo.nitocc = fondo.num_doc
        and codigo.rn = 1

        where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 5
        group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc
    )icbf1 ON icbf1.num_doc = tab0.num_doc


        where tab5.estado = 1 
        --$whereFuncionarioNumDoc $whereFuncionarioTipoDoc 
        and (
          tab5.fecha_fin >= trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') ))
         /*
          and tab5.fecha_fin
              between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
              and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo_actual) ,'dd/mm/yyyy') ))
         */
        )

)tab0

group by 
tab0.codigo, tab0.tipo, tab0.nitocc

order by
tab0.codigo

)tab
 
";

$st = $consul->execute($sql);
$detalle = $st->fetchAll();

/*
 * recorrer arreglo de datos y convertir en csv
 */
$i = 0;
$j = 0;
$sep = ""; //variable global para manejo del separador
foreach ($detalle as $rec) {
//        $shtml.='"';
    foreach ($rec as $key => $value) {
        if ($j != 0) {
//            if ($value != '/') {
            $shtml.= $sep;
//            }
        }
//        $value = str_replace('Ñ', '#', $value);
//        $value = str_replace('ñ', '#', $value);
//        $value = str_replace('[\n|\r|\n\r]', ' ', $value);
        $shtml.= $value;
        $j++;
    }
//        $shtml.='"';
//        $this->logMessage("Linea: " . $i++);
    //salto de linea para separar totales
    $shtml.=$salto_linea;
    $j = 0;
}

fwrite($archivo, $shtml);
fclose($archivo);

//$shtmlAuto .= $shtml;
//$shtml = "";

////ARCHIVO R9TOTAPE ?        
//$archivo = fopen("uploads/archivos_banco/r9totape.csv", "w"); //generacion del nuevo documento txt.
//$shtml .= "R901\tR902\tR903\tR904\tR905\tR906\tR907\tR908\t\n";
//fwrite($archivo, $shtml);
//fclose($archivo);
//
////ARCHIVO R10TOTAM ?        
//$archivo = fopen("uploads/archivos_banco/r10totam.csv", "w"); //generacion del nuevo documento txt.
//$shtml .= "R1001\tR1002\tR1003\tR1004\tR1005\tR1006\tR1007\tR1008\t\n";
//fwrite($archivo, $shtml);
//fclose($archivo);
//
////ARCHIVO R11TOTPP ?       
//$archivo = fopen("uploads/archivos_banco/r11totpp.csv", "w"); //generacion del nuevo documento txt.
//$shtml .= "R1101\tR1102\tR1103\tR1104\tR1105\tR1106\tR1107\tR1108\tR1109\tR1110\tR1111\tR1112\tR1113\tR1114\t\n";
//fwrite($archivo, $shtml);
//fclose($archivo);


/*
 * guardar y cerrar archivo
 */
$shtmlAuto.=$salto_linea;
fwrite($fp, $shtmlAuto);
fclose($fp);


/*
 * salida csv
 */
echo "<script>
    window.location='" . "/nomina/web/" . $sfile . "'</script>";
die();
?>
