<?php

// ESPECIALES


if($info=='vtgrge2')
{
     $sql_info='';
     
     $selec1='';
     $selec1.='select ';
     $selec1.='*,';
     for($idx=0; $idx<=24; $idx++)
     {
          $idxh=str_pad($idx,2,'0',STR_PAD_LEFT);
          $selec1.='sum(case when hora1 < "'.$idxh.':00" then 1 else 0 end) as '.$idxh.'_00,';
//           $selec1.='sum(case when hora1 < "'.$idxh.':30" then 1 else 0 end) as '.$idxh.'_30,';
     }
     $selec1.='status as st ';
     $selec1.='from '.$modu.'1 ';
     $selec1.='left join '.$modu.'2 on idnum1=idnum2 ';
     $selec1=calc_fields($modu,$selec1);
     
     
     $selec1.='where status not in ("X","O") ';
     $selec1.='and fecha>="'.$fecha1.'"';
     $selec1.='and fecha<="'.$fecha2.'"';
     if(!empty($ptov))
     {$selec1.='and ptov like "'.$ptov.'%"';}
     $selec1.='group by fecha ';

     $selec2=$selec1;
     $selec2=str_replace('hora1','hora2',$selec2);
     $selec2=str_replace('then 1','then -1',$selec2);

     $selec3='';
     $selec3.='select ';
     $selec3.='date_format(fecha,"%v") as sema,';
     $selec3.='fecha,';
     $selec3.='dia_d as dia,';
     for($idx=0; $idx<=24; $idx++)
     {
          $idxh=str_pad($idx,2,'0',STR_PAD_LEFT);
          $selec3.='convert(sum('.$idxh.'_00),char) as '.$idxh.'_00,';
//           $selec3.='convert(sum('.$idxh.'_30),char) as '.$idxh.'_30,';
     }
     $selec3.='status as st ';
     $selec3.='from(';
     $selec3.=$selec1;
     $selec3.='union ';
     $selec3.=$selec2;
     $selec3.=') as seleh ';
     $selec3.='group by fecha ';
     $selec3.='order by fecha ';
     
     $sql_info=$selec3;
}
          
if($info=='sriats')
{
     
     //vtas
     $sql_temp='';
     $sql_temp.='select * from vtas1 ';
     $sql_temp.='left join vtas2 on idnum1=idnum2 ';
     $sql_temp=calc_fields('vtas',$sql_temp);
     $sql_temp.='where ifnull(status,"")not in("X","X") ';
     $sql_temp.='and fecha>="'.$fecha1.'" ';
     $sql_temp.='and fecha<="'.$fecha2.'" ';
     if(!empty($toenti)){$sql_temp.='and toenti like "'.$toenti.'%" ';}
     $sql_temp.='group by idnum1 ';
     
     $sql_info='create temporary table ats_vtas ';
     $sql_info.='select ';
     $sql_info.='left(fecha,7) as a_mes, ';
     $sql_info.='sum(subtiva0) as vtas_iva0, ';
     $sql_info.='sum(subtiva) as vtas_iva, ';
     $sql_info.='sum(tiva) as iva_vtas, ';
     $sql_info.='convert(count(distinct case when ifnull(status,"")!="O" then enti end),char) as v_tot,';
     $sql_info.='count(distinct case when ifnull(status,"")="O" then idnum1 end) as v_anu,';
     $sql_info.='from  ('.$sql_temp.')as tempo12 ';
     $sql_info.='group by a_mes ';
     $res_info=msql_query($sql_info,$link_data);

     
     //retc
     $sql_temp='';
     $sql_temp.='select * from retc1 ';
     $sql_temp.='left join retc2 on idnum1=idnum2 ';
     $sql_temp=calc_fields('retc',$sql_temp);
     $sql_temp.='where ifnull(status,"")not in("O","X") ';
     $sql_temp.='and fecha>="'.$fecha1.'" ';
     $sql_temp.='and fecha<="'.$fecha2.'" ';
     $sql_temp.='and docu not like "RB%"';
     if(!empty($toenti)){$sql_temp.='and toenti like "'.$toenti.'%" ';}
     $sql_temp.='group by idnum1 ';
     
//      echo '==== '.$sql_temp;
     
     $sql_info='create temporary table ats_retc ';
     $sql_info.='select ';
     $sql_info.='left(fecha,7) as a_mes, ';
     $sql_info.='sum(base_rta) as bv_rta, ';
     $sql_info.='sum(rete_rta) as rv_rta, ';
     $sql_info.='count(distinct case when ifnull(status,"")!="O" and rete_rta>0 then idnum1 end) as c_rta,';
     $sql_info.='sum(base_iva) as bv_iva, ';
     $sql_info.='sum(rete_iva) as rv_iva, ';
     $sql_info.='count(distinct case when ifnull(status,"")!="O" and rete_iva>0 then idnum1 end) as c_iva,';
     $sql_info.='from  ('.$sql_temp.')as tempo12 ';
     $sql_info.='group by a_mes ';
     $res_info=msql_query($sql_info,$link_data);
     
     
     //comp
     $sql_temp='';
     $sql_temp.='select * from comp1 ';
     $sql_temp.='left join comp2 on idnum1=idnum2 ';
     $sql_temp=calc_fields('comp',$sql_temp);
     $sql_temp.='where ifnull(status,"")not in("O","X") ';
     $sql_temp.='and docu in("FP10","FP02","FP03","FP11","FP14","NP10") ';
     $sql_temp.='and fecha>="'.$fecha1.'" ';
     $sql_temp.='and fecha<="'.$fecha2.'" ';
     if(!empty($toenti)){$sql_temp.='and toenti like "'.$toenti.'%" ';}
     $sql_temp.='group by enti,idnum1 ';
     
     $sql_info='create temporary table ats_comp ';
     $sql_info.='select ';
     $sql_info.='left(fecha,7) as a_mes, ';
     $sql_info.='sum(subtiva0) as comp_iva0, ';
     $sql_info.='sum(subtiva) as comp_iva, ';
     $sql_info.='sum(tiva) as iva_comp, ';
     $sql_info.='count(distinct case when ifnull(status,"")!="O" then idnum1 end) as c_comp,';
     $sql_info.='from  ('.$sql_temp.')as tempo12 ';
     $sql_info.='group by a_mes ';
     $res_info=msql_query($sql_info,$link_data);

     
     
     //retp
     $sql_temp='';
     $sql_temp.='select * from retp1 ';
     $sql_temp.='left join retp2 on idnum1=idnum2 ';
     $sql_temp=calc_fields('retp',$sql_temp);
     $sql_temp.='where ifnull(status,"")not in("O","X") ';
//      $sql_temp.='and docu not like "RB%" ';
     $sql_temp.='and fecha>="'.$fecha1.'" ';
     $sql_temp.='and fecha<="'.$fecha2.'" ';
     if(!empty($toenti)){$sql_temp.='and toenti like "'.$toenti.'%" ';}
     $sql_temp.='group by idnum1 ';
     
     $sql_info='create temporary table ats_retp ';
     $sql_info.='select ';
     $sql_info.='left(fecha,7) as a_mes, ';
     $sql_info.='sum(base_rta) as bc_rta, ';
     $sql_info.='sum(rete_rta) as rc_rta, ';
     $sql_info.='count(distinct case when ifnull(status,"")!="O" and rete_rta>0 then idnum1 end) as c_rta,';
     $sql_info.='sum(base_iva) as bc_iva, ';
     $sql_info.='sum(rete_iva) as rc_iva, ';
     $sql_info.='count(distinct case when ifnull(status,"")!="O" and rete_iva>0 then idnum1 end) as c_iva,';
     $sql_info.='from  ('.$sql_temp.')as tempo12 ';
     $sql_info.='group by a_mes ';
     $res_info=msql_query($sql_info,$link_data);
     

     
     $sql_temp='create temporary table ames0 ';
     $sql_temp.='select ';
     $sql_temp.='left(fecha,7) as a_mes, ';
     $sql_temp.='left(fecha,7) as a_mes_hide, ';
     $sql_temp.='concat(upper(date_format(fecha,"%b"))," /",left(fecha,4)) as ats,';
     $sql_temp.='from (select fecha from vtas1 union select fecha from comp1 union select fecha from retc1 union select fecha from retp1)as fecha ';
     $sql_temp.='where fecha>="'.$fecha1.'" and fecha<="'.$fecha2.'"';
     $sql_temp.='group by a_mes ';
     $sql_temp.='order by a_mes ';
     $sql_temp=msql_query($sql_temp,$link_data);
     
     
     $sql_info='';
     $sql_info.='select  ';
     $sql_info.='*, ';
     $sql_info.='ifnull(vtas_iva0,0)+ifnull(vtas_iva,0)-ifnull(comp_iva0,0)-ifnull(comp_iva,0) as total, ';
     $sql_info.='ifnull(iva_vtas,0)-ifnull(rv_iva,0)-ifnull(iva_comp,0)+ifnull(rc_iva,0) as iva ';
     $sql_info.='from ames0 ';
     $sql_info.='left join ats_vtas on ames0.a_mes=ats_vtas.a_mes ';
     $sql_info.='left join ats_retc on ames0.a_mes=ats_retc.a_mes ';
     $sql_info.='left join ats_comp on ames0.a_mes=ats_comp.a_mes ';
     $sql_info.='left join ats_retp on ames0.a_mes=ats_retp.a_mes ';
     
     
     
//      $sql_info='create temporary table ats_ames ';
//      $sql_info.='select anio,mes,descri from ats_vtas ';
//      $sql_info.='union ';
//      $sql_info.='select anio,mes,descri from ats_comp ';
//      $sql_info.='union ';
//      $sql_info.='select anio,mes,descri from ats_retc ';
//      $sql_info.='union ';
//      $sql_info.='select anio,mes,descri from ats_retp ';
//      $sql_info.='group by anio,mes ';
//      $sql_info.='order by anio,mes ';
//      $res_info=msql_query($sql_info,$link_data);
     
     
     
//      $sql_info='select * from  ('.$sql_info.')as full ';
     
//      $sql_info='';
//      $sql_info.='select  ';
// //      $sql_info.='* ';
//      $sql_info.='* ';
//      $sql_info.='from ats_ames ';
//      $sql_info.='join ats_vtas on ats_ames.descri=ats_vtas.descri ';
//      $sql_info.='join ats_retc on ats_ames.descri=ats_retc.descri ';
//      $sql_info.='join ats_comp on ats_ames.descri=ats_comp.descri ';
//      $sql_info.='join ats_retp on ats_ames.descri=ats_retp.descri ';
     
     
     
}



if($info=='mmresu')
{
//      $sql_info='';
//      $sql_info.='select * ';
//      $sql_info.='from jwib1 ';
//      $sql_info.='left join jwib2 on idnum1=idnum2 ';
//      $sql_info.='left join docu0 on docu=docu_codigo ';
//      $sql_info.='where cong="'.$_SESSION['cong_codigo'].'" ';
//      $sql_info.='and fecha>="'.$fecha1.'" ';
//      $sql_info.='and fecha<="'.$fecha2.'" ';
//      $sql_info.='and docu like "VM3%" ';
//      $sql_info.='and ifnull(nombre1,"")!="" ';
//      if(!empty($docu))
//      {$sql_info.='and docu like "'.$docu.'" ';}
     
}



?>