<?php
     $filename=$xlsf.'_zeus.xls';
     
     $file_content='';
     $file_content.='<?php '."\n";
     $file_content.='header("Content-type: application/vnd.ms-excel");'."\n";
//      $file_content.='header("Content-type: application/vnd.ms-excel"); charset=iso-8859-1'."\n";
     $file_content.='header("Content-Disposition: attachment; filename='.$filename.'");'."\n";
     $file_content.='header("Pragma: no-cache");'."\n";
     $file_content.='header("Expires: 0");'."\n";
     
     $file_content.="\n".'?> ';
     $file_content.="\n";

     $file_content.="<style>"."\n";
     /*force text*/     
     $file_content.='.text{mso-number-format:"\@";}'."\n";
     $file_content.='.num {mso-number-format:General;}'."\n";
     $file_content.="</style>"."\n";

// hshe
$cta_gene      ='1355';
$cta_xcob      ='13050502';
$cta_ret1      ='13551501';
$cta_ret2      ='13551502';
$cta_ret275    ='13551503';
$cta_ret10     ='13551703';
$cta_ret20     ='13551702';
$cta_ret30     ='13551704';
$cta_ret70     ='13551701';
$cta_ret100    ='13551705';


$cta_gene      ='1355';
$cta_xcob      ='13050504';
$cta_ret1      ='13551501';
$cta_ret175    ='13551503';
$cta_ret2      ='13551502';
$cta_ret8      ='13551508';
$cta_ret275    ='13551504';
$cta_ret10     ='13551703';
$cta_ret20     ='13551703';
$cta_ret30     ='13551704';
$cta_ret70     ='13551701';
$cta_ret100    ='13551702';



if($empr0['empr_codigo']=='hshe')
{
     $cta_gene      ='1355';
     $cta_xcob      ='13050502';
     $cta_ret1      ='13551501';
     $cta_ret2      ='13551502';
     $cta_ret275    ='13551503';
     $cta_ret10     ='13551703';
     $cta_ret20     ='13551702';
     $cta_ret30     ='13551704';
     $cta_ret70     ='13551701';
     $cta_ret100    ='13551705';
}
     



$sql_retc='';
$sql_retc.='select ';
$sql_retc.='date_format(fecha,"%Y%m") as Año_y_Mes,';
$sql_retc.='"22" as Fuente,';
$sql_retc.='"15NUEVO" as Documento,';
$sql_retc.='fecha as Fecha,';
$sql_retc.='case ';
$sql_retc.='when puni=1       then "'.$cta_ret1.'" ';
$sql_retc.='when puni=2       then "'.$cta_ret2.'" ';
$sql_retc.='when puni=1.75    then "'.$cta_ret175.'" ';
$sql_retc.='when puni=2.75    then "'.$cta_ret275.'" ';
$sql_retc.='when puni=8       then "'.$cta_ret8.'" ';
$sql_retc.='when puni=10      then "'.$cta_ret10.'" ';
$sql_retc.='when puni=20      then "'.$cta_ret20.'" ';
$sql_retc.='when puni=30      then "'.$cta_ret30.'" ';
$sql_retc.='when puni=70      then "'.$cta_ret70.'" ';
$sql_retc.='when puni=100     then "'.$cta_ret100.'" ';
$sql_retc.='else "'.$cta_gene.'" end as Cuenta,';
$sql_retc.='concat("\'",enti) as Nit,';
$sql_retc.='"" as Auxiliar,';
$sql_retc.='"" as Centro_de_Costo,';
$sql_retc.='"" as Item,';
$sql_retc.='concat("FACTURA : ",numer) as Descripción,';
$sql_retc.='round(abs(cant*puni/100),2) as Valor,';
$sql_retc.='"1" as Ind_Tipo_de_Cuenta,';
$sql_retc.='"" as Conciliado,';
$sql_retc.='"" as Banco,';
$sql_retc.='"" as Vendedor,';
$sql_retc.='"" as Plaza,';
$sql_retc.='"CR" as Tipo_de_Factura,';
// $sql_retc.='numero as No_Factura,';
$sql_retc.='concat("\'",numero) as No_Factura,';
$sql_retc.='"" as Vencimiento,';
$sql_retc.='"" as Referencia,';
$sql_retc.='"Excel" as Usuario,';
$sql_retc.='fecha as Fecha_de_Grabación,';
$sql_retc.='"\'02" as Zona,';
$sql_retc.='"" as Cliente_Provedor,';
$sql_retc.='round(puni,2) as P_de_Retención,';
$sql_retc.='round(cant,2) as Base_de_Retención,';
$sql_retc.='"" as Código_de_Presupuesto,';
$sql_retc.='"" as Número_de_la_Reserva_Presupuestal,';
$sql_retc.='"0.00" as Valor_en_Otra_Moneda,';
$sql_retc.='"" as Estado,';
$sql_retc.='"0.00" as Consecutivo_de_Reversión,';
$sql_retc.='"" as Aplición,';
$sql_retc.='"" as Unidad_1,';
$sql_retc.='"" as Unidad_2,';
$sql_retc.='"" as Unidad_3,';
$sql_retc.='"0.00" as Valor_Unid_1,';
$sql_retc.='"0.00" as Valor_Unid_2,';
$sql_retc.='"0.00" as Valor_Unid_3,';
$sql_retc.='ptoe as Serie_del_Proveedor,';
$sql_retc.='concat("\'",autornum) as Autorizacion_del_Proveedor,';
$sql_retc.='"0.00" as Tasa_de_Cambio,';
$sql_retc.='"Local" as Unidad_de_negocio ';
$sql_retc.='from retc1 left join retc2 on idnum1=idnum2 ';
$sql_retc.='where ifnull(status,"") not in("O","X") ';
$sql_retc.='and cant*puni!=0 ';
$sql_retc.='and docu="RCLI" ';
$sql_retc.='and ifnull(zeus,0)="1" ';
$sql_retc.='and fecha>="'.$fecha1.'" ';
$sql_retc.='and fecha<="'.$fecha2.'" ';

$sql_retc.='union ';

$sql_retc.='select ';
$sql_retc.='date_format(fecha,"%Y%m") as Año_y_Mes,';
$sql_retc.='"22" as Fuente,';
$sql_retc.='"15NUEVO" as Documento,';
$sql_retc.='fecha as Fecha,';
//
$sql_retc.='"'.$cta_xcob.'" as Cuenta, ';
$sql_retc.='concat("\'",enti) as Nit,';
$sql_retc.='"" as Auxiliar,';
$sql_retc.='"" as Centro_de_Costo,';
$sql_retc.='"" as Item,';
$sql_retc.='concat("FACTURA : ",numer) as Descripción,';
//
$sql_retc.='sum(round(abs(cant*puni/100)*(-1),2)) as Valor,';
//
$sql_retc.='"2" as Ind_Tipo_de_Cuenta,';
$sql_retc.='"" as Conciliado,';
$sql_retc.='"" as Banco,';
$sql_retc.='"" as Vendedor,';
$sql_retc.='"" as Plaza,';
//
$sql_retc.='"\'01" as Tipo_de_Factura,';
//
// $sql_retc.='numer as No_Factura,';
$sql_retc.='concat("\'",numer) as No_Factura,';
$sql_retc.='"" as Vencimiento,';
$sql_retc.='"" as Referencia,';
$sql_retc.='"Excel" as Usuario,';
$sql_retc.='fecha as Fecha_de_Grabación,';
$sql_retc.='"\'02" as Zona,';
$sql_retc.='concat("\'",enti) as Cliente_Provedor,';
//
$sql_retc.='"0.00" as P_de_Retención,';
$sql_retc.='"0.00" as Base_de_Retención,';
$sql_retc.='"" as Código_de_Presupuesto,';
$sql_retc.='"" as Número_de_la_Reserva_Presupuestal,';
$sql_retc.='"0.00" as Valor_en_Otra_Moneda,';
$sql_retc.='"" as Estado,';
$sql_retc.='"0.00" as Consecutivo_de_Reversión,';
$sql_retc.='"" as Aplición,';
$sql_retc.='"" as Unidad_1,';
$sql_retc.='"" as Unidad_2,';
$sql_retc.='"" as Unidad_3,';
$sql_retc.='"0.00" as Valor_Unid_1,';
$sql_retc.='"0.00" as Valor_Unid_2,';
$sql_retc.='"0.00" as Valor_Unid_3,';
$sql_retc.='ptoem as Serie_del_Proveedor,';
$sql_retc.='concat("\'",autornum) as Autorizacion_del_Proveedor,';
$sql_retc.='"0.00" as Tasa_de_Cambio,';
$sql_retc.='"Local" as Unidad_de_negocio ';
$sql_retc.='from retc1 left join retc2 on idnum1=idnum2 ';
$sql_retc.='where ifnull(status,"") not in("O","X") ';
$sql_retc.='and docu="RCLI" ';
$sql_retc.='and ifnull(zeus,0)="1" ';
$sql_retc.='and fecha>="'.$fecha1.'" ';
$sql_retc.='and fecha<="'.$fecha2.'" ';
$sql_retc.='group by idnum1 ';


$sql_retc='select * from ('.$sql_retc.') as retc1 ';
$sql_retc.='order by Fecha,Descripción,Valor desc ';

$res_retc=msql_query($sql_retc,$link_data);

     
$domg = new DOMDocument();
     $domg->encoding = 'utf-8';
     $domg->xmlVersion = '1.0';
     $domg->formatOutput = true;

     
     $style='';
     $style.='.text{mso-number-format:"\@";}';
     $style.='.num {mso-number-format:General;}';

     $element=$domg->appendChild($domg->createElement("style",htmlspecialchars($style)));     


     $table=$domg->appendChild($domg->createElement("table",""));  
          $xstyle='';
          $xstyle.='font-family:arial;';
          $xstyle.='font-size:12px;';
          $xstyle.='white-space:nowrap;';
          $xstyle.='border-collapse:collapse;';
          $table->setAttribute("style",$xstyle) ;  
          $table->setAttribute("width","300%") ;  

     $trow=$table->appendChild($domg->createElement("tr",""));
          $trow->setAttribute("style",'background:#d3d3d3;') ;  
     for($xcol=0; $xcol<msql_num_fields($res_retc); $xcol++)
     {
          $field_name=msql_field_name($res_retc,$xcol);
          $field_name=str_replace('_',' ',$field_name);
          $tcol=$trow->appendChild($domg->createElement('th',$field_name));

     }          
     while($aso_retc=msql_fetch_assoc($res_retc))
     {
          $trow=$table->appendChild($domg->createElement("tr",""));
               if($aso_retc['Valor']<0)
               {$trow->setAttribute("style",'background:#d3d3d3;');}

          for($xcol=0; $xcol<msql_num_fields($res_retc); $xcol++)
          {
               $value=$aso_retc[msql_field_name($res_retc,$xcol)];
               $tcol=$trow->appendChild($domg->createElement('td',$value));
          }          
          
     }
     
     
     
     $html2=$domg->saveHTML();
     $file_content.=$html2;
     file_put_contents($file_php_xls_zeus,$file_content); 

?>