<?php
declare(strict_types=0);


// select ultimos 5 de cada DOCU
// set @cont:=1;
// set @ante:=null;
// select * from(
// select *,
// if(docu=@ante,@cont:=@cont+1,@cont:=1) as cont,
// @ante:=docu
// from inve1 
// where ifnull(status,"")not in("O","X")
// order by docu, fecha desc 
// ) as inve
// where cont<=5


// ultimos IB10 de un item
// set @cont:=1;
// set @ante:=null;
// select * from(
// 	select *,
// 	if(item=@ante,@cont:=@cont+1,@cont:=1) as cont,
// 	@ante:=item
// 	from  (
// 		select * from inve1 left join inve2 on idnum1=idnum2
// 		where ifnull(status,"")not in("O","X")
// 		and item is not null
// 		and docu in ("IB10","IB15")
// 		order by item, fecha desc 
//     ) as inve12
// ) as inve123
// where cont<=2
     
     
function trim_colu($tabla,$colum)
{
     if(empty($tabla)){return;}
     if(empty($colum)){return;}
     
     if(empty($link_data)){$link_data=$_SESSION['link_data'];}
     
     $sql_updt='update '.$tabla.' set '.$colum.'=trim('.$colum.')  ';
     $res_updt=msql_query($sql_updt,$link_data);
     
     $sql_updt='update '.$tabla.' set '.$field.'=replace('.$field.'," ,",",") ';
     $res_updt=msql_query($sql_updt,$link_data);
     $sql_updt='update '.$tabla.' set '.$field.'=replace('.$field.'," ;",";") ';
     $res_updt=msql_query($sql_updt,$link_data);
     $sql_updt='update '.$tabla.' set '.$field.'=replace('.$field.',"/ ","/") ';
     $res_updt=msql_query($sql_updt,$link_data);

     $sql_updt='update '.$tabla.' set '.$field.'=replace('.$field.',"    "," ") ';
     $res_updt=msql_query($sql_updt,$link_data);
     $sql_updt='update '.$tabla.' set '.$field.'=replace('.$field.',"   "," ") ';
     $res_updt=msql_query($sql_updt,$link_data);
     $sql_updt='update '.$tabla.' set '.$field.'=replace('.$field.',"  "," ") ';
     $res_updt=msql_query($sql_updt,$link_data);

     $sql_updt='update '.$tabla.' set '.$field.'=replace('.$field.',",",", ") ';
     $res_updt=msql_query($sql_updt,$link_data);
     $sql_updt='update '.$tabla.' set '.$field.'=replace('.$field.',";","; ") ';
     $res_updt=msql_query($sql_updt,$link_data);
     $sql_updt='update '.$tabla.' set '.$field.'=replace('.$field.',"/"," /") ';
     $res_updt=msql_query($sql_updt,$link_data);
     
     for($idx=0;$idx<=9;$idx++)
     {
     $sql_updt='update '.$tabla.' set '.$field.'=replace('.$field.',"'.$idx.' /","'.$idx.'/") ';
     $res_updt=msql_query($sql_updt,$link_data);
     }
     
     
     return;
// 
//      $sql_fields.='replace(replace(replace(concepto,",",", "),"/"," /"),"  "," ") as concepto_t, ';
     
}

function trim_maes($maes,$codigo)
{
     if(empty($link_data)){$link_data=$_SESSION['link_data'];}
     $maes0=$maes.'0';
     $sql_updt='update '.$maes0.' set ';
     $sql_updt.=$maes.'_descri set ';
     $sql_updt.='where '.$maes.'_status='.$maes.'_status ';
     if(!empty($codigo))
     {$sql_updt.='where '.$maes.'_codigo like "'.trim($codigo).'%"';}
}

function view_rgsn()
{
     
     if(empty($link_data)){$link_data=$_SESSION['link_data'];}

//      {
          
//      $sql_updt='update rgsn0 set rgsn_farmaco=rgsn_priact';
//      $res_updt=msql_query($sql_updt,$link_data);
     
//      $sql_updt='update rgsn0 ';
//      $sql_updt.='set rgsn_farmaco=substring(rgsn_farmaco,locate("tiene",rgsn_farmaco)+5) ';
//      $sql_updt.='where rgsn_farmaco like "%tiene%" ';
//      $res_updt=msql_query($sql_updt,$link_data);
     
//      $charx=".....";
//      $sql_updt='update rgsn0 ';
//      $sql_updt.='set rgsn_farmaco=substring(rgsn_farmaco,1,locate("'.$charx.'",rgsn_farmaco)-1) ';
//      $sql_updt.='where rgsn_farmaco like "%'.$charx.'%" ';
//      $res_updt=msql_query($sql_updt,$link_data);
     
//      $charx="…";
//      $sql_updt='update rgsn0 ';
//      $sql_updt.='set rgsn_farmaco=substring(rgsn_farmaco,1,locate("'.$charx.'",rgsn_farmaco)-1) ';
//      $sql_updt.='where rgsn_farmaco like "%'.$charx.'%" ';
//      $res_updt=msql_query($sql_updt,$link_data);
     
//      $charx="(";
//      $sql_updt='update rgsn0 ';
//      $sql_updt.='set rgsn_farmaco=substring(rgsn_farmaco,1,locate("'.$charx.'",rgsn_farmaco)-1) ';
//      $sql_updt.='where rgsn_farmaco like "%'.$charx.'%" ';
//      $res_updt=msql_query($sql_updt,$link_data);
     
//      $charx="+";
//      $sql_updt='update rgsn0 ';
//      $sql_updt.='set rgsn_farmaco=substring(rgsn_farmaco,1,locate("'.$charx.'",rgsn_farmaco)-1) ';
//      $sql_updt.='where rgsn_farmaco like "%'.$charx.'%" ';
//      $res_updt=msql_query($sql_updt,$link_data);

//      $charx="---";
//      $sql_updt='update rgsn0 ';
//      $sql_updt.='set rgsn_farmaco=substring(rgsn_farmaco,1,locate("'.$charx.'",rgsn_farmaco)-1) ';
//      $sql_updt.='where rgsn_farmaco like "%'.$charx.'%" ';
//      $res_updt=msql_query($sql_updt,$link_data);

//      $charx="polvo";
//      $sql_updt='update rgsn0 ';
//      $sql_updt.='set rgsn_farmaco=substring(rgsn_farmaco,1,locate("'.$charx.'",rgsn_farmaco)-1) ';
//      $sql_updt.='where rgsn_farmaco like "%'.$charx.'%" ';
//      $res_updt=msql_query($sql_updt,$link_data);

//      $charx="durante";
//      $sql_updt='update rgsn0 ';
//      $sql_updt.='set rgsn_farmaco=substring(rgsn_farmaco,1,locate("'.$charx.'",rgsn_farmaco)-1) ';
//      $sql_updt.='where rgsn_farmaco like "%'.$charx.'%" ';
//      $res_updt=msql_query($sql_updt,$link_data);

//      $charx="equiv";
//      $sql_updt='update rgsn0 ';
//      $sql_updt.='set rgsn_farmaco=substring(rgsn_farmaco,1,locate("'.$charx.'",rgsn_farmaco)-1) ';
//      $sql_updt.='where rgsn_farmaco like "%'.$charx.'%" ';
//      $res_updt=msql_query($sql_updt,$link_data);

     
     
//      $sql_updt='update rgsn0 ';
//      $sql_updt.='set rgsn_farmaco=substring(rgsn_farmaco,2) ';
//      $sql_updt.='where rgsn_farmaco like ":%" ';
//      $res_updt=msql_query($sql_updt,$link_data);
     
//      $sql_updt='update rgsn0 ';
//      $sql_updt.='set rgsn_farmaco=substring(rgsn_farmaco,2) ';
//      $sql_updt.='where rgsn_farmaco like "*%" ';
//      $res_updt=msql_query($sql_updt,$link_data);
     
//      $sql_updt='update rgsn0 ';
//      $sql_updt.='set rgsn_farmaco=trim(rgsn_farmaco) ';
//      $res_updt=msql_query($sql_updt,$link_data);
//      }
     
     
     $view='pcom';
     $view0=$view.'0';
     @msql_query('drop view '.$view0,$link_data);
     
     $sql_view='';
     $sql_view.='';
     $sql_view.='create view '.$view0.' as select distinct ';
     $sql_view.='rgsn_codpre as '.$view.'_codigo,';
     $sql_view.='rgsn_precom as '.$view.'_descri,';
     $sql_view.='substring(rgsn_precom,1,locate(" ",rgsn_precom)) as '.$view.'_tipo,';
     $sql_view.='convert("-" using latin1)collate latin1_spanish_ci as '.$view.'_status,';
     $sql_view.='convert("-" using latin1)collate latin1_spanish_ci as '.$view.'_iuser,';
     $sql_view.='now() as '.$view.'_idate ';
     $sql_view.='from rgsn0 ';
//      $sql_view.='collate latin1_spanish_ci ';
     $sql_view.='order by rgsn_codpre ';
     msql_query($sql_view,$link_data);
     
//      echo '*/*/*/*'.$sql_view;
     
     
     $view='ffar';
     $view0=$view.'0';
     @msql_query('drop view '.$view0,$link_data);
     
     $sql_view='';
     $sql_view.='';
     $sql_view.='create view '.$view0.' as select distinct ';
     $sql_view.='trim(rgsn_formaf) as '.$view.'_codigo,';
     $sql_view.='trim(rgsn_formaf) as '.$view.'_descri,';
//      $sql_view.='(substring(rgsn_precom,1,locate(" ",rgsn_precom))) as '.$view.'_tipo,';
     $sql_view.='convert("-" using latin1)collate latin1_spanish_ci as '.$view.'_tipo,';
     $sql_view.='convert("-" using latin1)collate latin1_spanish_ci as '.$view.'_status,';
     $sql_view.='convert("-" using latin1)collate latin1_spanish_ci as '.$view.'_iuser,';
     $sql_view.='now() as '.$view.'_idate ';
     $sql_view.='from rgsn0 ';
     $sql_view.='where ifnull(rgsn_formaf,"")!="" ';
//      $sql_view.='collate latin1_spanish_ci ';
     $sql_view.='order by trim(rgsn_formaf) ';
     msql_query($sql_view,$link_data);
     

     
//      echo '*/*/*/*/*/*/*/*/';

     $view='mdcn';
     $view0=$view.'0';
     @msql_query('drop view '.$view0,$link_data);
     
     $sql_view='';
     $sql_view.='create view '.$view0.' as select distinct ';
     $sql_view.='concat(  substring_index( substring_index( substring_index( substring_index( trim(rgsn_nomcom) ,"(",1) ,":",1) ,"llamado",1) ,"antes",1) ," || ",trim(rgsn_formaf)) as '.$view.'_codigo,';
     $sql_view.='concat(  substring_index( substring_index( substring_index( substring_index( trim(rgsn_nomcom) ,"(",1) ,":",1) ,"llamado",1) ,"antes",1) ," || ",trim(rgsn_formaf)) as '.$view.'_descri,';

     $sql_view.='trim(rgsn_nomcom) as '.$view.'_nomcom,';
     $sql_view.='trim(rgsn_formaf) as '.$view.'_formaf,';
//      $sql_view.='trim(rgsn_priact) as '.$view.'_priact,';
//      $sql_view.='trim(rgsn_concen) as '.$view.'_concen,';
     $sql_view.='case when locate("tiene",rgsn_priact)=0 then rgsn_priact else trim(substring(rgsn_priact,locate("tiene",rgsn_priact)+6)) end as '.$view.'_priact,';
     $sql_view.='case when locate("tiene",rgsn_concen)=0 then rgsn_concen else trim(substring(rgsn_concen,locate("tiene",rgsn_concen)+6)) end as '.$view.'_concen,';
     $sql_view.='convert("-" using latin1)collate latin1_spanish_ci as '.$view.'_tipo,';
     $sql_view.='convert("-" using latin1)collate latin1_spanish_ci as '.$view.'_status,';
     $sql_view.='convert("-" using latin1)collate latin1_spanish_ci as '.$view.'_iuser,';
     $sql_view.='now() as '.$view.'_idate ';
     $sql_view.='from rgsn0 ';
     $sql_view.='where ifnull(rgsn_nomcom,"")!="" ';
     msql_query($sql_view,$link_data);

     
     
     
//      $view='prac';
//      $view0=$view.'0';
//      @msql_query('drop view '.$view0,$link_data);
     
//      $sql_view='';
//      $sql_view.='create view '.$view0.' as select distinct ';
//      $sql_view.='case when locate("tiene",rgsn_priact)=0 then rgsn_priact ';
//      $sql_view.='else trim(substring(rgsn_priact,locate("tiene",rgsn_priact)+6)) end ';
//      $sql_view.='as '.$view.'_codigo,';
     
//      $sql_view.='case when locate("tiene",rgsn_priact)=0 then rgsn_priact ';
//      $sql_view.='else trim(substring(rgsn_priact,locate("tiene",rgsn_priact)+6)) end ';
//      $sql_view.='as '.$view.'_descri,';
     
//      $sql_view.='convert("-" using latin1)collate latin1_spanish_ci as '.$view.'_tipo,';
//      $sql_view.='convert("-" using latin1)collate latin1_spanish_ci as '.$view.'_status,';
//      $sql_view.='convert("-" using latin1)collate latin1_spanish_ci as '.$view.'_iuser,';
//      $sql_view.='now() as '.$view.'_idate ';
//      $sql_view.='from rgsn0 ';
//      msql_query($sql_view,$link_data);
     
     
//      echo '==== '.$sql_view;     
     
     
}     

function view_caba()
{
//      return;
     
     if(empty($link_data)){$link_data=$_SESSION['link_data'];}
     $view='caba';
     $view0=$view.'0';
//      @msql_query('drop table '.$view0,$link_data);
     @msql_query('drop view '.$view0,$link_data);
     
     $sql_view='';
     $sql_view.='';
     $sql_view.='create view '.$view0.' as ';
     $sql_view.='select ';
     $sql_view.='caja_codigo as '.$view.'_codigo,';
     $sql_view.='caja_descri as '.$view.'_descri,';
     $sql_view.='caja_tipo as '.$view.'_tipo,';
     $sql_view.='caja_status as '.$view.'_status,';
     $sql_view.='caja_ctacont as '.$view.'_ctacont,';
     $sql_view.='caja_color as '.$view.'_color, ';
     $sql_view.='caja_idate as '.$view.'_idate ';
     $sql_view.='from caja0 ';
     $sql_view.='union ';
     $sql_view.='select ';
     $sql_view.='ctab_codigo as '.$view.'_codigo,';
     $sql_view.='ctab_descri as '.$view.'_descri,';
     $sql_view.='ctab_tipo as '.$view.'_tipo,';
     $sql_view.='ctab_status as '.$view.'_status,';
     $sql_view.='ctab_ctacont as '.$view.'_ctacont,';
     $sql_view.='ctab_color as '.$view.'_color, ';
     $sql_view.='ctab_idate as '.$view.'_idate ';
     $sql_view.='from ctab0 ';
//      $sql_view.='collate latin1_spanish_ci ';
//      $sql_view.='order by caja_codigo ';
     
     
     msql_query($sql_view,$link_data);
     
     
     
}     

function view_maes($maes,$view,$where='')
{
     if(empty($link_data)){$link_data=$_SESSION['link_data'];}
     $maes0=$maes.'0';
     $view0=$view.'0';
     @msql_query('drop view '.$view0,$link_data);
     if($where=='X'){return;}
     
     $res_desc=msql_query('describe '.$maes0,$link_data);
     
     $sql_view='create view '.$view0.' as select ';
     while($aso_desc=msql_fetch_assoc($res_desc))
     {$sql_view.=$aso_desc['Field'].' as '.str_replace($maes.'_',$view.'_',$aso_desc['Field']).',';}
     $sql_view=substr($sql_view,0,strlen($sql_view)-1).' ';    
     $sql_view.='from '.$maes0.' ';
     if(!empty($where))
     {$sql_view.='where '.$where;}
     msql_query($sql_view,$link_data);
     
     
     return;
}


function maes_view($modu='')
{
     if(empty($link_data)){$link_data=$_SESSION['link_data'];}
     $empr0=data_maes('empr',$_SESSION['empr_codigo']);
     
if($modu=='empl' or empty($modu))
{view_maes('enti','empl',
           '(
           (left(enti_codigo,10) in(select left(enti,10) from susa1 where status!="X") 
           or left(enti_codigo,10) in(select left(user_cedula,10) from user0 where user_tipo in ("sist","supe","admi") and user_status not in("I","X")))
           and length(trim(enti_codigo))=10 )'
          ); }
// if($modu=='pcnt' or empty($modu)){view_maes('enti','pcnt','enti_codigo like "99%" or enti_codigo in(select distinct pcnt from vtas1 where status!="X")  or enti_codigo in(select distinct pcnt from clie1 where status!="X")');}
// if($modu=='enti' or empty($modu)){view_maes('enti','sex1','enti_sexo=1 ');}
// if($modu=='enti' or empty($modu)){view_maes('enti','sex2','enti_sexo=2 ');}
     
if($modu=='vndd' or empty($modu)){view_maes('enti','vndd','enti_codigo in(select distinct vndd from vtas1 where status!="X" and ifnull(vndd,"")!="")  or enti_codigo in(select distinct vndd from clie1 where status!="X" and ifnull(vndd,"")!="")');}
if($modu=='rcdd' or empty($modu)){view_maes('enti','rcdd','enti_codigo in(select distinct rcdd from vtas1 where status!="X")  or enti_codigo in(select distinct rcdd from clie1 where status!="X")'); }
if($modu=='mcnc' or empty($modu)){view_maes('enti','mcnc','enti_codigo in(select distinct mcnc from otbj1 left join otbj2 on idnum1=idnum2 where status!="X") '); }
if($modu=='codt' or empty($modu)){view_maes('enti','codt','enti_codigo in(select distinct cedula1 from guia1 where status!="X")');}
if($modu=='rfrx' or empty($modu)){view_maes('user','rfrx','ifnull(user_referx,"")!=""');}

if($empr0['empr_clinica']==1  or $empr0['empr_medico']==1 or $empr0['empr_veterinaria']==1)
{
     $xwherev='';
     if($empr0['empr_veterinaria']==1)
     {
          $xwherev.='length(trim(enti_codigo))=10 ';
          $xwherev.='and (enti_codigo like "99%" ';
//           $xwherev.=')and( ';
          $xwherev.='or  ';
          $xwherev.='     enti_codigo in(select distinct pcnt from vtas1 where status!="X") ';
          $xwherev.='    or enti_codigo in(select distinct pcnt from clie1 where status!="X") ';
          $xwherev.='    or enti_codigo in(select distinct pcnt from hcli1 where status!="X") ';
          $xwherev.='    or enti_codigo in(select distinct enti from hcli1 where status!="X") ';
          $xwherev.='    or enti_codigo in(select distinct enti from rmed1 where status!="X")';
          $xwherev.=')';
     }     
     if($modu=='pcnt' or empty($modu)){view_maes('enti','pcnt',$xwherev);}
}
     
     
// {if($modu=='pcnt' or empty($modu)){view_maes('enti','pcnt','enti_codigo like "99%" or enti_codigo in(select distinct pcnt from vtas1 where status!="X")  or enti_codigo in(select distinct pcnt from clie1 where status!="X")');}}
     
if($empr0['empr_clinica']==1 or $empr0['empr_medico']==1)     
{
     if($modu=='mdco' or empty($modu)){view_maes('enti','mdco');}
     if($modu=='ciex' or empty($modu)){view_maes('ciex','cie2');}
     if($modu=='ciex' or empty($modu)){view_maes('ciex','cie3');}
}
          
if($modu=='rpis' or empty($modu)){view_maes('enti','segu','enti_actividad like "%SEGURO%"');}
if($modu=='rpis' or empty($modu)){if(!is_dir('rpis')){mkdir('rpis');}}

if($modu=='ctas' or empty($modu)){view_maes('ctas','cta1','left(ctas_codigo,1)="1"');}
if($modu=='ctas' or empty($modu)){view_maes('ctas','cta2','left(ctas_codigo,1)="2"');}
if($modu=='ctas' or empty($modu)){view_maes('ctas','cta3','left(ctas_codigo,1)="3"');}
if($modu=='ctas' or empty($modu)){view_maes('ctas','cta4','left(ctas_codigo,1)="4"');}
if($modu=='ctas' or empty($modu)){view_maes('ctas','cta5','left(ctas_codigo,1)="5"');}
if($modu=='ctas' or empty($modu)){view_maes('ctas','cta6','left(ctas_codigo,1)="6"');}
if($modu=='ctas' or empty($modu)){view_maes('ctas','cta7','left(ctas_codigo,1)="7"');}
if($modu=='ctas' or empty($modu)){view_maes('ctas','cta8','left(ctas_codigo,1)="8"');}
if($modu=='ctas' or empty($modu)){view_maes('ctas','cta9','left(ctas_codigo,1)="9"');}

     
if($modu=='iinv' or empty($modu)){view_maes('item','iinv','(left(item_codigo,10) in(select left(item,10) from inve1 left join inve2 on idnum1=idnum2 and docu in("IB00","IB10","IB15")))');}
if($modu=='ibod' or empty($modu)){view_maes('item','ibod','(left(item_codigo,10) in(select left(item,10) from inve1 left join inve2 on idnum1=idnum2 and docu in("IB00","IB10","IB15")))');}
if($modu=='ivta' or empty($modu)){view_maes('item','ivta','(left(item_codigo,10) in(select left(item,10) from vtas2) or left(item_codigo,10) in(select left(item,10) from clie2))');}
if($modu=='igas' or empty($modu)){view_maes('item','igas','(item_codigo like "99%")');}
// if($modu=='prod' or empty($modu)){view_maes('item','prod','(left(item_codigo,10) in(select left(item,10) from inve2) or left(item_codigo,10) in(select left(item,10) from prod2))');}
if($modu=='serv' or empty($modu)){view_maes('item','serv','(left(item_codigo,10) not in(select left(item,10) from inve2) and left(item_codigo,10) not in(select left(item,10) from pduc2))');}
if($modu=='ipro' or empty($modu)){view_maes('item','ipro','(left(item_codigo,10) in(select left(item,10) from pduc2))');}
     
// if($modu=='team' or empty($modu)){view_maes('team','sqad');}
     
if($modu=='asub' or empty($modu)){view_maes('asub','atvd');}
     
if($modu=='ctas' or empty($modu)){set_ctasgd();}
if($modu=='ctas' or empty($modu)){view_maes('ctas','ctag','(ctas_tipogd="G")');}
if($modu=='ctas' or empty($modu)){view_maes('ctas','ctad','(ctas_tipogd="D")');}
     
return;     
     
     
}


function set_colu($tabla,$colu,$tipo,$after="",$defa="")
{
     $msc = microtime(true);
     if(empty($link_data)){$link_data=$_SESSION['link_data'];}
     
     $colu1=$colu;
     if(substr($tabla,-1,1)=='0')
     {
          if(substr($colu,4,1)!='_'){$colu=substr($tabla,0,4).'_'.$colu;}
          if(substr($after,4,1)!='_' and !empty($after)){$after=substr($tabla,0,4).'_'.$after; }
     }

     if($tipo==='X')
     {
          echo '<br> COL ==> '.$tabla.'_'.$colu.' => X';
          @msql_query('alter table '.$tabla.' drop column '.$colu,$link_data);
          return $after;
     }
     
//      echo '<br>*/*/* '.date('H:i:s'); 
     $sql_sche ='select ordinal_position,column_name,column_type ';
     $sql_sche.='from information_schema.columns ';
     $sql_sche.='where table_schema="'.$_SESSION['dataname'].'" ';
     $sql_sche.='and table_name="'.$tabla.'" ';
     if(empty($after))
     {$sql_sche.='and column_name="'.$colu.'" ';}
     else
     {
          $sql_sche.='and (column_name="'.$colu.'" ';
          $sql_sche.='or column_name="'.$after.'") ';
          $sql_sche.='order by ordinal_position desc';
     }     
     $res_sche=msql_query($sql_sche,$link_data);
     
     
     $colu_name=msql_result($res_sche,0,'column_name');
     $colu_posi=msql_result($res_sche,0,'ordinal_position');
     $colu_type=msql_result($res_sche,0,'column_type','type');
     
     $afte_name=msql_result($res_sche,1,'column_name');
     $afte_posi=msql_result($res_sche,1,'ordinal_position');


     if(empty($colu_name))
     {
          $colu_name=msql_result($res_sche,0,'COLUMN_NAME');
          $colu_posi=msql_result($res_sche,0,'ORDINAL POSITION');
          $colu_type=msql_result($res_sche,0,'COLUMN_TYPE','TYPE');

          $afte_name=msql_result($res_sche,1,'COLUMN_NAME');
          $afte_posi=msql_result($res_sche,1,'ORDINAL_POSITION');
     }
     
     $colu_posi=(int)$colu_posi;
     $afte_posi=(int)$afte_posi;
     
     $colu_msge=$cfg_empr.' ==> '.$tabla.'.'.$colu;
//      if(!mb_stristr2($colu_name.$afte_name,$colu))
     if($colu!=$colu_name and $colu!=$afte_name)
     {
          $sql_colu='alter table '.$tabla.' add column '.$colu.' '.$tipo.' null ';
          $colu_msge.= ' => '.$tipo;
     }
	else
     {
          $sql_colu='alter table '.$tabla.' change column '.$colu.' '.$colu.' '.$tipo.' ';
          if(!mb_stristr2($colu,'_codigo')){$sql_colu.='null ';}
          
          if($tipo!=$colu_type)
          {$colu_msge.=' => '.$colu_type;}
          $colu_msge.=' => '.$tipo;
     }

     if($after!="")
     {
          if(mb_stristr2($colu_name.$afte_name,$after) and ($afte_posi+1)!=$colu_posi)
          {
               $sql_colu.=' after '.$after;
               $colu_msge.=' ('.$after.')';
          }     
     }
     msql_query($sql_colu,$link_data);
       

     if(mb_stristr2($tipo,'int') or mb_stristr2($tipo,'decimal'))
     {if(empty($defa)){$defa='0';}}
     
     if($defa!="")
     {
          $sql_colu='alter table '.$tabla.' alter column '.$colu.' drop default ';
          msql_query($sql_colu,$link_data);

          $sql_colu='alter table '.$tabla.' alter column '.$colu.' set default "'.$defa.'"';
          msql_query($sql_colu,$link_data);

          if($defa=="NULL" )
          {$sql_colu='update '.$tabla.' set '.$colu.'=null where '.$colu.' =""';}
          else
          {$sql_colu='update '.$tabla.' set '.$colu.'="'.$defa.'" where ifnull('.$colu.',"")=""';}
          msql_query($sql_colu,$link_data);
     }

     if(mb_stristr2('.docu.pto.numero.fecha.docum.ptoem.numer.enti.item.ctas.cntn',$colu))
     {
          msql_query('alter table '.$tabla.' drop key '.$colu,$link_data);
          msql_query('create index '.$colu.' on '.$tabla.'('.$colu.')',$link_data);
     }
     
     $msc = microtime(true)-$msc;
     $msc=round($msc,2);
     $colu_msge.=' :: '.$msc;
     echo '<br>'.$colu_msge;
     
     return $colu;
}

function fecha0($tabla='',$codi='')
{
//      return "2000-01-01";
     if(empty($empr)){$empr=$_SESSION['empr_codigo'];}     
     if(empty($link_data)){$link_data=$_SESSION['link_data'];}
     if(empty($tabla)){$tabla='cont';}
     
     
     
     if($empr=='imcp')
     {
          if(date('Y-m-d')<="2024-06-30")
          {
               if($tabla=='caja'){return "2023-01-01";}
               if($tabla=='bcos'){return "2023-01-01";}
          }
     }
     
     
     $fec0="2000-01-01";
     if($empr=='demo'){return $fec0;}     
     
     if($tabla=='cxco'){$tabla='dxco';}
     if($tabla=='cxpa'){$tabla='dxpa';}
     if($tabla=='sri1'){$tabla=''; return $fec0;}
     
     $sql_fec0 ='select * from '.$tabla.'1 ';
     $sql_fec0.='where ifnull(fecha,"")!="" ';
     $sql_fec0.='and fecha>="2000-01-01" ';
     $sql_fec0.='and ifnull(status,"") not in ("O","X")';
     if($tabla=='cont'){$sql_fec0.='and docu like "DD00" ';}
     if($tabla=='inve'){$sql_fec0.='and docu like "IB00" ';}
     if($tabla=='caja'){$sql_fec0.='and docu like "IE00" ';}
     if($tabla=='bcos'){$sql_fec0.='and docu like "DB00" ';}
     if($tabla=='dxco'){$sql_fec0.='and docu like "%00" ';}
     if($tabla=='dxpa'){$sql_fec0.='and docu like "%00" ';}

     if($tabla=='caja' and !empty($codi)){$sql_fec0.='and caja like "'.$codi.'" ';}
     if($tabla=='bcos' and !empty($codi)){$sql_fec0.='and ctab like "'.$codi.'" ';}
     if($tabla=='inve' and !empty($codi)){$sql_fec0.='and bode like "'.$codi.'" ';}
               
     $sql_fec0.='order by fecha ';
     $sql_fec0.='limit 1 ';
//      $sql_fec0.='order by fecha desc limit 1';
     
     if($tabla=='clac')
     {
          $sql_fec0='select '.$tabla.'_fecha as fecha ';
          $sql_fec0.='from '.$tabla.'0 ';
          $sql_fec0.='where ifnull('.$tabla.'_status,"")not in("I","X") ';
          $sql_fec0.='order by '.$tabla.'_fecha limit 1';
     }

     if(!empty($sql_fec0))
     {
          $res_fec0=msql_query($sql_fec0,$link_data);
          if(msql_num_rows($res_fec0)!=0)
          {$fec0= msql_result($res_fec0,0,'fecha');}
     }
     
//      echo '==='.$fec0;
//      echo '==='.$sql_fec0;
     
//      if(empty($fec0)){$fec0="2000-01-01";}
     if($tabla=='clac'){$fec0="2010-01-01";}
     
     if($tabla=='retc'){$fec0=date('Y-m-01',strtotime($fec0));}
     if($tabla=='retp'){$fec0=date('Y-m-01',strtotime($fec0));}
     if($tabla=='vtas'){$fec0=date('Y-m-01',strtotime($fec0));}
     if($tabla=='comp'){$fec0=date('Y-m-01',strtotime($fec0));}
     
//  echo '*/*/*'.$fec0;
     
     return $fec0;
}

function fecha9($tabla='')
{
//      return "2000-01-01";
     $fec0=date('Y-m-d');
     if(empty($link_data)){$link_data=$_SESSION['link_data'];}
     if(empty($tabla)){$tabla='cont';}
     
     if($tabla=='cxco'){$tabla='dxco';}
     if($tabla=='cxpa'){$tabla='dxpa';}
     if($tabla=='sri1'){$tabla=''; return $fec0;}
     
     $sql_fec0 ='select * from '.$tabla.'1 ';
     $sql_fec0.='where ifnull(status,"") not in ("X") and fecha is not null ';
     if($tabla=='inve'){$sql_fec0.='and docu not like "IB13" ';}
     $sql_fec0.='order by fecha desc limit 1';

     if(!empty($sql_fec0))
     {
          $res_fec0=msql_query($sql_fec0,$link_data);
          if(msql_num_rows($res_fec0)!=0)
          {$fec0= msql_result($res_fec0,0,'fecha');}
     }
     
     if(empty($fec0)){$fec0=date('Y-m-d');}
     
     return $fec0;
}



function fechax($fechax=7, $tabla='',$docu='',$ptoe='',$enti='',$user='')
{
//      return "2000-01-01";
     if(empty($link_data)){$link_data=$_SESSION['link_data'];}
     if(empty($tabla)){$tabla='cont';}

     $sql_fecx ='select distinct fecha ';
     $sql_fecx.='from '.$tabla.'1 ';
     $sql_fecx.='where status not in ("O","X") ';
     if(!empty($docu)){$sql_fecx.='and docu like "'.$docu.'%" ';}
     if(!empty($ptoe)){$sql_fecx.='and ptoe like "'.$ptoe.'%" ';}
     if(!empty($enti)){$sql_fecx.='and enti like "'.substr($enti,0,10).'%" ';}
     if(!empty($user)){$sql_fecx.='and iuser = "'.$user.'" ';}
     $sql_fecx.='order by fecha desc ';
     $sql_fecx.='limit '.$fechax;

//      echo '*/*/*'.$sql_fecx;
//      if(!empty($sql_fecx))
     {
          $res_fecx=msql_query($sql_fecx,$link_data);
          if(msql_num_rows($res_fecx)!=0)
          {$fecx= msql_result($res_fecx,msql_num_rows($res_fecx)-1,'fecha');}
     }
     
     if(empty($fecx)){$fec0="2000-01-01";}
     
     return $fecx;
}


function get_image($maes,$codigo)
{
     $codigo=trim($codigo);
     if(empty($maes)){return;}
     if(empty($codigo)){return;}
     if(empty($link_data)){$link_data=$_SESSION['link_data'];}
     
     $tabla='img'.substr($maes,0,1);
     $sql_image ='select * from '.$tabla.'0 ';
     $sql_image.='where '.$tabla.'_'.$maes.' like "'.$codigo.'%" ';
     $sql_image.='order by '.$tabla.'_defa desc,'.$tabla.'_codigo ';
     $sql_image.='limit 1';
     $res_image=msql_query($sql_image,$link_data);
     $image=msql_result($res_image,0,$tabla.'_file');
     
}

function get_iddet($docu,$idnum)
{
     if(empty($docu)){return;}
     if(empty($idnum)){return;}
     if(empty($link_data)){$link_data=$_SESSION['link_data'];}
     
     $sql_docu='select docu_tabla from docu0 where docu_codigo like "'.$docu.'%" order by docu_codigo limit 1';
     $res_docu=msql_query($sql_docu,$link_data);
     $tabla=msql_result($res_docu,0,'docu_tabla');
     
     $sql_docu='select iddet from '.$tabla.'2 where idnum2="'.$idnum.'" order by iddet desc limit 1';
     $res_docu=msql_query($sql_docu,$link_data);
     $iddet=msql_result($res_docu,0,'iddet');

     if(empty($iddet)){$iddet=0;}
     $iddet=$iddet+1;
     return $iddet;
}

function ctas_deta($pctas)
{
     $pctas=trim($pctas);
     $lctas=strlen($pctas);
     if($lctas<5){return false;}

     if(empty($link_data)){$link_data=$_SESSION['link_data'];}
     
     $sql_ctas='select * from ctas0 where ctas_codigo like "'.$pctas.'%" and  ctas_status!="X" order by ctas_codigo';
     $res_ctas=msql_query($sql_ctas,$link_data);
     if(msql_num_rows($res_ctas)==1)
     {
          $sql_ctas='select * from ctas0 where ctas_codigo<"'.$pctas.'" and length(trim(ctas_codigo))<'.$lctas.' and  ctas_status!="X" order by ctas_codigo desc';
          $res_ctas=msql_query($sql_ctas,$link_data);
          $cta_sup='';
          $cta_sup.=trim(msql_result($res_ctas,0,'ctas_codigo'));
          $cta_sup.='->';
          $cta_sup.=trim(msql_result($res_ctas,0,'ctas_descri'));
          return $cta_sup;
     }
     return false;
}

function reso2file($reso,$name='')
{
     if(gettype($reso)!='resource' and gettype($reso)!='object'){return;}

     $blockrows=5;
     $blockbott='dotted 1px;';

     $type='xml';
     if(mb_stristr2($name,'xml')){$type='xml';}
     if(mb_stristr2($name,'html')){$type='html';}
     if(mb_stristr2($name,'json')){$type='json';}
     if($name==$type or $name=='.'.$type){$name='';}
     
     if(empty($name)){$name=$type.'_'.date('Yms_His');}
	$file_name=$name.'.'.$type;
     
	$dom = new DOMDocument();
		$dom->encoding = 'utf-8';
		$dom->xmlVersion = '1.0';
		$dom->formatOutput = true;

          $xcont=0;
          $thead=array();
          $tfoot=array();
          $field_name=array();
          $field_type=array();
          $field_leng=array();
          $table='grid';
          $trow='row';
          $tcol='col';
          if($type=='html')
          {
               $table='table';
               $trow='tr';
               $tcol='td';
          }
     
          $root=$dom->appendChild($dom->createElement($name));
               $root->setAttribute('date',date('Y-m-d'));
               $root->setAttribute('time',date('H:i:s'));
     
          $style='';
          $style.='tr[name]:hover{background-color:lightblue !important;}; ';
          $root->appendChild($dom->createElement('style',$style));
     
          $grid=$root->appendChild($dom->createElement($table));
               $grid->setAttribute('name',$name);
               $grid->setAttribute('blockrows',$blockrows);
               $grid->setAttribute('blockbott',$blockbott);
               $grid->setAttribute('heightrow',$heightrow);
     
          if($type=='html')
          {
               $grid->setAttribute('id',$name);
               $grid->setAttribute('width','99%');
               $grid->setAttribute('cellspacing','0');
               $grid->setAttribute('cellpadding','3');
               $style='';
               $style.='border-collapse:collapse;';
               $style.='background-color:snow;';
//                $style.='font-family:arial;';
               $grid->setAttribute('style',$style);
          }
     
          if($type=='xml')
          {
               $rowg=$grid->appendChild($dom->createElement('tbody'));
               $footg=$grid->appendChild($dom->createElement('tfoot'));
          }

          if($type=='html')
          {
               $caption='';
               $grid->appendChild($dom->createElement('caption',$caption));
               $colg=$grid->appendChild($dom->createElement('colgroup'));
               $headg=$grid->appendChild($dom->createElement('thead'));
               $rowg=$grid->appendChild($dom->createElement('tbody'));
               $footg=$grid->appendChild($dom->createElement('tfoot'));
                    $style='';
                    $style.='background-color:silver;';
                    $style.='border-top:2px solid;border-bottom:2px solid;';
                    $headg->setAttribute('style',$style);
                    $footg->setAttribute('style',$style);
                    $rowg->setAttribute('valign','top');
                    $rowg->setAttribute('style','overflow-y:auto;');
          }

     
          // validar totales
          for($xcol=0; $xcol<msql_num_fields($reso); $xcol++)
          {
               $field_name[$xcol]=msql_field_name($reso,$xcol);
               $field_type[$xcol]=msql_field_type($reso,$xcol);
               if(mb_stristr2($field_name[$xcol],'__')){$field_name[$xcol]=str_replace('__','-',$field_name[$xcol]);}
               for($xrow=0; $xrow<msql_num_rows($reso); $xrow++)
               {
                    $value=msql_result($reso,$xrow,$xcol);
                    $value_leng=strlen($value);
                    if($value_leng>$field_leng[$xcol]){$field_leng[$xcol]=$value_leng;}
                    if(mb_stristr2('.int.real',$field_type[$xcol])){$value=$value*1;}
                    if(mb_stristr2('.int.real',$field_type[$xcol]) and $type=='xml'){$value=1;}
                    if(mb_stristr2('.saldo.total',$field_name[$xcol])){$value=1;}
                    if(mb_stristr2($field_name[$xcol],'_hide')){$value=0;}
                    if(!empty($value)){$thead[$xcol]='1';break;}
               }
          }

     
          // llena detalle
          msql_data_seek($reso,0);
          for($xrow=0; $xrow<msql_num_rows($reso); $xrow++)
          {
               $asoc=msql_fetch_assoc($reso);
               $item_duni=$asoc['item_duni'];
               $item_dval=$asoc['item_dval'];
               $item_dpre=$asoc['item_dpre'];
               $item_dcos=$asoc['item_dcos'];
               $status=$asoc['status'];
               $stat=$asoc['stat'];
               $st=$asoc['st'];
               if($item_dval<2){$item_dval=2;}
               if($item_dpre<2){$item_dpre=2;}
               if($item_dcos<2){$item_dcos=2;}

               $style=''; 
               $class='';
               if(($xrow+1)%$blockrows==0){$style.='border-bottom:'.$blockbott; } 
               if($xrow+1==msql_num_rows($reso)){$style.='border-bottom:solid 2px;'; } 
               
               $row=$rowg->appendChild($dom->createElement($trow));
               if($type=='html')
               {
                    if(!empty($heightrow))
                    {$row->setAttribute('height',$heightrow);}
                    $row->setAttribute('name',"");
                    if($status=='O' or $stat=='O' or $st=='O'){$row->setAttribute('linethr',"");}
                    if(!empty($style)){$row->setAttribute('style',$style);}
                    if(!empty($class)){$row->setAttribute('class',$class);}
                    
                    // COLUMNA #linea
                    $col=$row->appendChild($dom->createElement($tcol,str_pad($xrow+1,strlen(msql_num_rows($reso)),'0',STR_PAD_LEFT)));
                    $style='';
                    $class='';
                    
                    $col->setAttribute('align-c',"");
                    $col->setAttribute('size80',"");
                    $col->setAttribute('border-l',"");
                    $col->setAttribute('border-r',"");
                    if(!empty($class)){$col->setAttribute('class',$class);}
                    if(!empty($style)){$col->setAttribute('style',$style);}
               }
               
               for($xcol=0; $xcol<msql_num_fields($reso); $xcol++)
               {
                    $value=msql_result($reso,$xrow,$xcol);
                    if($type=='xml'){$tcol=$field_name[$xcol];}
                    if($field_type[$xcol]=='int'){$tfoot[$xcol]++;}
                    if($field_type[$xcol]=='real'){$tfoot[$xcol]+=$value;}

                    if($type=='xml' and !empty($thead[$xcol]))
                    {$col=$row->appendChild($dom->createElement($tcol,htmlspecialchars($value)));}

                    if($type=='html' and !empty($thead[$xcol]))
                    {
                         if(mb_stristr2('.puni.p_unit.p-unit.unit.punit.pvp',$field_name[$xcol]))
                         {$value=number_format(trunc_real($value,$item_dval),$item_dpre);}
                         
                         if(mb_stristr2('.cost',$field_name[$xcol]) and mb_stristr2('.int.real',$field_type[$xcol]))
                         {$value=number_format(trunc_real($value,$item_dval),$item_dcos);}
                         
                         if(mb_stristr2('.stock.saldou.saldound',$field_name[$xcol]) and mb_stristr2('.int.real',$field_type[$xcol]))
                         {$value=number_format((float)$value,$item_duni);}
                         
                         if(mb_stristr2('.cant.ingreso.egreso.saldound',$field_name[$xcol]))
                         {$value=number_format($value,$item_duni);}
                         
                         if(mb_stristr2('.tdcto.subt.subtiva0.subtiva.subtice.sub-total.sub-tot.saldo',$field_name[$xcol]))
                         {$value=number_format($value,2);}
                         
                         if(mb_stristr2('.debito.credito',$field_name[$xcol]))
                         {$value=number_format($value,2);}
                         
                         if(mb_stristr2('.img',$field_name[$xcol]))
                         {
//                               if(file_exists($value))
//                               {$value_img=$value;$value="";}
                              if(empty($value)){$value="SIN FOTO";}
                              else{$value_img=$value;$value="";}
                         }

                         $crea_value=true;
                         if(mb_stristr2('.numero',$field_name[$xcol]))
                         {
                              $ptoe=$asoc['ptoe'];
                              $col=$row->appendChild($dom->createElement($tcol));       
                              $div=$col->appendChild($dom->createElement('div',htmlspecialchars($ptoe.$value)));                           
                                   $div->setAttribute('hide',"");
                              
                              $href='';
                              $href='?prnt=1&docu='.$asoc['docu'].'&idnum='.$asoc['idnum1'].'&formato=';
                              $ref=$col->appendChild($dom->createElement('a',htmlspecialchars($value)));                           
                                   $ref->setAttribute('href',$href);
                                   $ref->setAttribute('target','_blank');
                              
                              $crea_value=false;
                         }

                         if($crea_value)
                         {$col=$row->appendChild($dom->createElement($tcol,htmlspecialchars($value)));}
                         $class='';
                         $style='';
                         if(empty($value_img))
                         {}
                         else
                         {
                              $col->setAttribute("align-c","");
                              $img=$col->appendChild($dom->createElement("img",""));
                              $img->setAttribute("src",$value_img);
                              $img->setAttribute("width","50");
                              $img->setAttribute("heigth","50");
                              $value_img="";
                         }
                      
                         if(strlen($value)>40){$col->setAttribute('size60',"");}
                         elseif(strlen($value)>20){$col->setAttribute('size80',"");}
                         
                         if(mb_stristr2('.idnum1.idnum2.idn.iddet.idd',$field_name[$xcol])){$col->setAttribute('size80',"");}
                         if(mb_stristr2('.tdcto.tiva.tice.tirbp',$field_name[$xcol])){$col->setAttribute('size80',"");}
                         if(mb_stristr2('.tiva.tice.tirbp',$field_name[$xcol])){$col->setAttribute('italic',"");}
                         if(mb_stristr2('.ptoe.ptov.bode.caja.ctab',$field_name[$xcol])){$col->setAttribute('size80',"");}
                         if(mb_stristr2('.tdcto',$field_name[$xcol])){$col->setAttribute('underline',"");}
                         if(mb_stristr2('.ptoe.numero.status.st',$field_name[$xcol])){$col->setAttribute('align-c',"");}
                         if(mb_stristr2('.color.talla',$field_name[$xcol])){$col->setAttribute('size80',"");}
                         if(mb_stristr2('.color.talla',$field_name[$xcol])){$col->setAttribute('align-c',"");}
                         if(mb_stristr2('.item',$field_name[$xcol]))
                         {
                              $onclick ="";
                              $onclick.="{window.location.href=\"?scri=shop_item2&buscar=".$value."\"}";
                              $col->setAttribute('onclick',$onclick);
                         }
                      
                         if($field_type[$xcol]=='string' and mb_stristr2($value,'-')){$col->setAttribute('nowrap',"");}
                         if($field_type[$xcol]=='string' and mb_stristr2($value,'-')){$col->setAttribute('size80',"");}
                         if($field_type[$xcol]=='string' and $field_leng[$xcol]<=9){$col->setAttribute('nowrap',"");}
                         if($field_type[$xcol]=='string' and $field_leng[$xcol]<=4){$col->setAttribute('align-c',"");}
                         if($field_type[$xcol]=='int' and $field_leng[$xcol]<=2){$col->setAttribute('align-c',"");}
                         if($field_type[$xcol]=='int' and $field_leng[$xcol]>2){$col->setAttribute('align-r',"");}
                         if($field_type[$xcol]=='real'){$col->setAttribute('align-r',"");}
                         if($field_type[$xcol]=='date'){$col->setAttribute('td_date',"");}
                         if($field_type[$xcol]=='datetime'){$col->setAttribute('td_date',"");}
                         if(mb_stristr2('.total.saldo',$field_name[$xcol])){$col->setAttribute('td_total',"");}
                         if(!empty($class)){$col->setAttribute('class',$class);}
                         if(!empty($style)){$col->setAttribute('style',$style);}
                    }
               }
          }

          if($type=='xml')
          {
               $rowf=$footg->appendChild($dom->createElement($trow));
               for($xcol=0; $xcol<msql_num_fields($reso); $xcol++)
               {
                    if(!empty($thead[$xcol]))
                    {$col=$rowf->appendChild($dom->createElement($field_name[$xcol],$tfoot[$xcol]));}
               }
          }
     
          // colgroup head foot
          $xsort=0;
          if($type=='html')
          {
               $rowh=$headg->appendChild($dom->createElement($trow));
               $rowf=$footg->appendChild($dom->createElement($trow));
               
               $ccolg=$colg->appendChild($dom->createElement('col',""));
                    $ccolg->setAttribute('colu','L');
               $crowh=$rowh->appendChild($dom->createElement('th','L'));
               $crowf=$rowf->appendChild($dom->createElement('th',$xrow));

               $onclick='sortTable(this,'.$xsort++.'';
               if($field_type[$xcol]=='real'){$onclick.=',"N"';}
               $onclick.=');';
               $crowh->setAttribute('onclick',$onclick);
               $crowh->setAttribute('size80',"");
                              
               for($xcol=0; $xcol<msql_num_fields($reso); $xcol++)
               {
                    if(!empty($thead[$xcol]))
                    {
                         // group col
                         $style='';
                         $class='';
                         if(mb_stristr2('.enti.item.ctas',$field_name[$xcol])){$style.='background-color:gainsboro;';}

                         $col=$colg->appendChild($dom->createElement('col',""));
                              $col->setAttribute('colu',$field_name[$xcol]);
                              $col->setAttribute('type',$field_type[$xcol]);
                              if(empty($thead[$xcol])){$col->setAttribute('hide',"");}                         
                              if($field_type[$xcol]=='date'){$col->setAttribute('td_date',"");}
                              if($field_type[$xcol]=='real'){$col->setAttribute('align-r',"");}
                              if(mb_stristr2('.total.saldo',$field_name[$xcol])){$col->setAttribute('td_total',"");}
                              if(mb_stristr2('.puni.p-unit.unit.punit.pvp.cost',$field_name[$xcol])){$col->setAttribute('border-l',"");}
                              if(mb_stristr2('.puni.p-unit.unit.punit.pvp.cost',$field_name[$xcol])){$col->setAttribute('border-r',"");}
                              if(!empty($class)){$col->setAttribute('class',$class);}
                              if(!empty($style)){$col->setAttribute('style',$style);}

                         $style='';
                         $class='';
                         $onclick='';
                         
                              if(mb_stristr2('.idnum1.idnum2.idn.iddet.idd',$field_name[$xcol])){$class.='size80 ';}
                              if(mb_stristr2('.tdcto.tiva.tice.tirbp',$field_name[$xcol])){$class.='italic size80 ';}
                              if(mb_stristr2('.tdcto',$field_name[$xcol])){$class.='underline ';}
                              if($field_type[$xcol]=='date'){$class.='date';}
                                   $onclick.='sortTable(this,'.$xsort++.'';
                                   if($field_type[$xcol]=='real'){$onclick.=',"N"';}
                                   $onclick.=');';
                         
                         // group head
                         $val_fieldname=$field_name[$xcol];
                         $val_fieldname=strtoupper($val_fieldname);
                         $val_fieldname=str_replace('_','-',$val_fieldname);
                         
                         $col=$rowh->appendChild($dom->createElement('th',$val_fieldname));
                              $col->setAttribute('name',$field_name[$xcol]);
                              if(!empty($onclick)){$col->setAttribute('onclick',$onclick);}
                              if(!empty($class)){$col->setAttribute('class',$class);}
                              if(!empty($style)){$col->setAttribute('style',$style);}
                              if($field_leng[$xcol]<=5){$col->setAttribute('size60',"");}
                              elseif($field_leng[$xcol]<=9){$col->setAttribute('size80',"");}
                         
                         $tfoot_val=$tfoot[$xcol];
                         if($field_type[$xcol]=='real'){$tfoot_val=number_format($tfoot_val,2);}
                         $col=$rowf->appendChild($dom->createElement('th',$tfoot_val));
                              $col->setAttribute('name',$field_name[$xcol]);
                              if(!empty($class)){$col->setAttribute('class',$class);}
                              if(!empty($style)){$col->setAttribute('style',$style);}
                    }
               }
          }
     
     if(!empty($file_name)){$dom->save($file_name);}
     return $dom->saveXML();
     
}

function calc_maes($tabla,$sql_query='')
{
//      return;
     if(empty($empr)){$empr=$_SESSION['empr_codigo'];}     
     if(empty($link_data)){$link_data=$_SESSION['link_data'];}
     
//      $empr0=data_maes('empr',$empr);

     
     $trim_colu='trim_colu';
     $trim_colu='ifnull('.$trim_colu.',"")';
     $trim_colu='trim('.$trim_colu.')';
     $trim_colu='replace('.$trim_colu.',",",", ")';
     $trim_colu='replace('.$trim_colu.',";","; ")';
//      $trim_colu='replace('.$trim_colu.',"/"," /")';
     $trim_colu='replace('.$trim_colu.',"  "," ")';
     $trim_colu='replace('.$trim_colu.',"+"," +")';
     
     
     $sql_fields='';
//      echo '====='.$tabla;
     if(mb_stristr2('.enti.clie.prov.pcnt.vndd.rcdd.codt',$tabla))
     {
          
// TIMESTAMPDIFF(YEAR, fecha_nacimiento, CURDATE()) AS edad_anios,
// TIMESTAMPDIFF(MONTH, fecha_nacimiento, CURDATE()) % 12 AS edad_meses
          
//           $sql_fields.='concat((datediff(curdate(),'.$tabla.'_fechanac) div 365),":",(datediff(curdate(),'.$tabla.'_fechanac) mod 365)div 30) as '.$tabla.'_edad,';
//           $sql_fields.='concat(timestampdiff(year,'.$tabla.'_fechanac,curdate()),":",timestampdiff(month,'.$tabla.'_fechanac,curdate()) % 12) as '.$tabla.'_edad,';
          $sql_fields.='concat(lpad(timestampdiff(year,'.$tabla.'_fechanac,curdate()),2,"0"),":",lpad(timestampdiff(month,'.$tabla.'_fechanac,curdate()) % 12,2,"0"),"") as '.$tabla.'_edad,';
          $sql_fields.='timestampdiff(year,'.$tabla.'_fechanac,curdate()) as '.$tabla.'_edad1,';
          $sql_fields.='timestampdiff(year,'.$tabla.'_fechanac,curdate())% 12 as '.$tabla.'_edad2,';
          $sql_fields.='timestampdiff(year,'.$tabla.'_fechaini,curdate()) as '.$tabla.'_anti1,';
          $sql_fields.='timestampdiff(year,'.$tabla.'_fechaini,curdate())% 12 as '.$tabla.'_anti2,';
//           $sql_fields.='(datediff(curdate(),'.$tabla.'_fechanac) div 365) as '.$tabla.'_edad1,';
//           $sql_fields.='(datediff(curdate(),'.$tabla.'_fechanac) mod 365)div 30 as '.$tabla.'_edad2,';
//           $sql_fields.='(datediff(curdate(),'.$tabla.'_fechaini) div 365) as '.$tabla.'_anti1,';
//           $sql_fields.='(datediff(curdate(),'.$tabla.'_fechaini) mod 365)div 30 as '.$tabla.'_anti2,';
          
          
//      $sql_fields.='timestampdiff(day,concat(fecha," ",hora),now()) as dias,';
//      $sql_fields.='timestampdiff(hour,concat(fecha," ",hora),now())%24 as horas,';
//      $sql_fields.='timestampdiff(minute,concat(fecha," ",hora),now())%60 as mins,';
          
          
//           🚺♀
          $sql_fields.='case ';
               $sql_fields.='when '.$tabla.'_sexo=1 then "Masculino" ';
               $sql_fields.='when '.$tabla.'_sexo=2 then "Femenino" ';  //&#127800;
               $sql_fields.='else "-" end as '.$tabla.'_sexod,';
          
          
          $sql_fields.='case ';
               $sql_fields.='when '.$tabla.'_sexo=1 then "&#'.mb_ord('♂️').';" ';
//                $sql_fields.='when '.$tabla.'_sexo=2 then "&#127872;" ';
               $sql_fields.='when '.$tabla.'_sexo=2 then "&#'.mb_ord('♀️').';" ';
               $sql_fields.='else "-" end as '.$tabla.'_sexoi,';
          
          if($tabla=='pcnt')
          {
               if($_SESSION['empr_clinica']==1 or $_SESSION['empr_veterinaria']==1)
               {
               $sql_fields.='case ';
                    $sql_fields.='when '.$tabla.'_esteril=1 then "Si" ';
                    $sql_fields.='when '.$tabla.'_esteril=2 then "No" ';
                    $sql_fields.='else "-" end as '.$tabla.'_esterild,';
               }          

               if($_SESSION['empr_veterinaria']==1)
               {
               $sql_fields.='case ';
                    $sql_fields.='when '.$tabla.'_veneno=1 then "Si" ';
                    $sql_fields.='when '.$tabla.'_veneno=2 then "No" ';
                    $sql_fields.='else "-" end as '.$tabla.'_venenod,';
               }          
          }
          
          $sql_fields.='case ';
               $sql_fields.='when left('.$tabla.'_codigo,2)>="01" and left('.$tabla.'_codigo,2)<="30" and length(trim('.$tabla.'_codigo))=10 then "C" ';
               $sql_fields.='when left('.$tabla.'_codigo,2)>="01" and left('.$tabla.'_codigo,2)<="30" and length(trim('.$tabla.'_codigo))=13 then "R" ';
               $sql_fields.='when length(trim('.$tabla.'_codigo))!=10 and length(trim('.$tabla.'_codigo))!=13 and length(trim('.$tabla.'_pasaporte))!=0 then "P" ';
               $sql_fields.='when length(trim('.$tabla.'_codigo))!=10 and length(trim('.$tabla.'_codigo))!=13 and length(trim('.$tabla.'_idexterior))!=0 then "E" ';
               $sql_fields.='when left('.$tabla.'_codigo,2)="99" and length(trim('.$tabla.'_pasaporte))>0 then "P" ';
               $sql_fields.='when left('.$tabla.'_codigo,2)="99" and length(trim('.$tabla.'_idexterior))>0 then "E" ';
               $sql_fields.='when left('.$tabla.'_codigo,2)="99" and length(trim('.$tabla.'_pasaporte))=0 and length(trim('.$tabla.'_idexterior))=0 then "F" ';
               $sql_fields.='when left('.$tabla.'_codigo,1)="1"  and length(trim('.$tabla.'_codigo))=6 then "P" ';
               $sql_fields.='else "-" end as '.$tabla.'_tipoid,';

          $sql_fields.='case ';
               $sql_fields.='when left('.$tabla.'_codigo,2)>="01" and left('.$tabla.'_codigo,2)<="30" and substr('.$tabla.'_codigo,3,1)<="5" then "N" ';
               $sql_fields.='when left('.$tabla.'_codigo,2)>="01" and left('.$tabla.'_codigo,2)<="30" and substr('.$tabla.'_codigo,3,1)="6" then "P" ';
               $sql_fields.='when left('.$tabla.'_codigo,2)>="01" and left('.$tabla.'_codigo,2)<="30" and substr('.$tabla.'_codigo,3,1)="9" then "J" ';
               $sql_fields.='when left('.$tabla.'_codigo,2)="99" and (length(trim('.$tabla.'_codigo))=10 or length(trim('.$tabla.'_codigo))=13) then "F" ';
               $sql_fields.='else "-" end as '.$tabla.'_tiporuc,';

          $sql_fields.='case ';
          $sql_fields.='when '.$tabla.'_codigo like "99%" then "CF" ';
          $sql_fields.='when '.$tabla.'_coesp=1 and length(trim('.$tabla.'_codigo))=13 then "CE" ';
          $sql_fields.='when '.$tabla.'_rimpe=1 and length(trim('.$tabla.'_codigo))=13 then "RE" ';
          $sql_fields.='when '.$tabla.'_rimpe=2 and length(trim('.$tabla.'_codigo))=13 then "NP" ';
          $sql_fields.='else "RG" end as '.$tabla.'_tipoC,';

          $sql_fields.='case ';
          $sql_fields.='when '.$tabla.'_codigo like "99%" then "CONSUMIDOR FINAL" ';
//           $sql_fields.='when '.$tabla.'_pais like "99%" then "CONSUMIDOR FINAL" ';
          $sql_fields.='when '.$tabla.'_coesp=1 and length(trim('.$tabla.'_codigo))=13 then "CONTRIBUYENTE ESPECIAL" ';
          $sql_fields.='when '.$tabla.'_rimpe=1 and length(trim('.$tabla.'_codigo))=13 then "RIMPE EMPRENDEDOR" ';
          $sql_fields.='when '.$tabla.'_rimpe=2 and length(trim('.$tabla.'_codigo))=13 then "RIMPE NEGOCIO POPULAR" ';
          $sql_fields.='else "REGIMEN GENERAL" end as '.$tabla.'_tipoCont,';

          
          // CELEC
          $sql_fields.='case ';
               $sql_fields.='when left('.$tabla.'_codigo,2)>="01" and left('.$tabla.'_codigo,2)<="30" and length(trim('.$tabla.'_codigo))=10 then "05" ';
               $sql_fields.='when left('.$tabla.'_codigo,2)>="01" and left('.$tabla.'_codigo,2)<="30" and length(trim('.$tabla.'_codigo))=13 then "04" ';
               $sql_fields.='when left('.$tabla.'_codigo,2)="99" and length(trim('.$tabla.'_pasaporte))>0 then "06" ';
               $sql_fields.='when left('.$tabla.'_codigo,2)="99" and length(trim('.$tabla.'_idexterior))>0 then "08" ';
               $sql_fields.='when left('.$tabla.'_codigo,2)="99" and length(trim('.$tabla.'_pasaporte))=0 and length(trim('.$tabla.'_idexterior))=0 then "07" ';
               $sql_fields.='else "04" end as '.$tabla.'_tipoIdComp,';
          
          $sql_fields.='case ';
               $sql_fields.='when left('.$tabla.'_codigo,4)="9999" then '.$tabla.'_codigo ';
               $sql_fields.='when left('.$tabla.'_codigo,2)="99" and length(trim('.$tabla.'_pasaporte))>0 then '.$tabla.'_pasaporte ';
               $sql_fields.='when left('.$tabla.'_codigo,2)="99" and length(trim('.$tabla.'_idexterior))>0 then '.$tabla.'_idexterior ';
               $sql_fields.='else '.$tabla.'_codigo end as '.$tabla.'_IdComp,';
          
          $sql_fields.='case ';
               $sql_fields.='when '.$tabla.'_obcon=1 then "SI" ';
               $sql_fields.='when '.$tabla.'_obcon="S" then "SI" ';
               $sql_fields.='else "NO" end as '.$tabla.'_obCont,';
          
          
          
          
          
     }
     
//     if(mb_stristr2($tabla,'item'))
     if(mb_stristr2('.item.prod.serv',$tabla))
     {
// 		 $sql_fields.='case when ifnull('.$tabla.'_codigov,"")!="" then '.$tabla.'_codigov else '.$tabla.'_codigo end as '.$tabla.'_codigoAuxiliar ';
          
          
		 $sql_fields.='concat(';
		 $sql_fields.='char(substr('.$tabla.'_ean,1,1)+35),';
		 $sql_fields.='"!",';
		 $sql_fields.='char(substr('.$tabla.'_ean,2,1)+48),';
		 $sql_fields.='case ';
			 $sql_fields.='when locate(left('.$tabla.'_ean,1),"0123")!=0 ';
			 $sql_fields.='then char(substr('.$tabla.'_ean,3,1)+48) ';
			 $sql_fields.='else char(substr('.$tabla.'_ean,3,1)+65) ';
			 $sql_fields.='end, ';
		 $sql_fields.='case ';
			 $sql_fields.='when locate(left('.$tabla.'_ean,1),"0478")!=0 ';
			 $sql_fields.='then char(substr('.$tabla.'_ean,4,1)+48) ';
			 $sql_fields.='else char(substr('.$tabla.'_ean,4,1)+65) ';
			 $sql_fields.='end, ';
		 $sql_fields.='case ';
			 $sql_fields.='when locate(left('.$tabla.'_ean,1),"0145")!=0 ';
			 $sql_fields.='then char(substr('.$tabla.'_ean,5,1)+48) ';
			 $sql_fields.='else char(substr('.$tabla.'_ean,5,1)+65) ';
			 $sql_fields.='end, ';
		 $sql_fields.='case ';
			 $sql_fields.='when locate(left('.$tabla.'_ean,1),"02567")!=0 ';
			 $sql_fields.='then char(substr('.$tabla.'_ean,6,1)+48) ';
			 $sql_fields.='else char(substr('.$tabla.'_ean,6,1)+65) ';
			 $sql_fields.='end, ';
		 $sql_fields.='case ';
			 $sql_fields.='when locate(left('.$tabla.'_ean,1),"03689")!=0 ';
			 $sql_fields.='then char(substr('.$tabla.'_ean,7,1)+48) ';
			 $sql_fields.='else char(substr('.$tabla.'_ean,7,1)+65) ';
			 $sql_fields.='end, ';
		 $sql_fields.='"-",';
		 $sql_fields.='char(substr('.$tabla.'_ean,8,1)+97,substr('.$tabla.'_ean,9,1)+97,substr('.$tabla.'_ean,10,1)+97,substr('.$tabla.'_ean,11,1)+97,substr('.$tabla.'_ean,12,1)+97,substr('.$tabla.'_ean,13,1)+97),"!")';
		 $sql_fields.=' as item_ean13bc, ';
          
		 $sql_fields.='concat(';
		 $sql_fields.='"!",';
		 $sql_fields.='char(substr('.$tabla.'_ean,1,1)+35),';
		 $sql_fields.='char(substr('.$tabla.'_ean,2,1)+48),';
		 $sql_fields.='case ';
			 $sql_fields.='when locate(left('.$tabla.'_ean,1),"0123")!=0 ';
			 $sql_fields.='then char(substr('.$tabla.'_ean,3,1)+48) ';
			 $sql_fields.='else char(substr('.$tabla.'_ean,3,1)+65) ';
			 $sql_fields.='end, ';
		 $sql_fields.='case ';
			 $sql_fields.='when locate(left('.$tabla.'_ean,1),"0478")!=0 ';
			 $sql_fields.='then char(substr('.$tabla.'_ean,4,1)+48) ';
			 $sql_fields.='else char(substr('.$tabla.'_ean,4,1)+65) ';
			 $sql_fields.='end, ';
		 $sql_fields.='"-",';
		 $sql_fields.='case ';
			 $sql_fields.='when locate(left('.$tabla.'_ean,1),"0145")!=0 ';
			 $sql_fields.='then char(substr('.$tabla.'_ean,5,1)+48) ';
			 $sql_fields.='else char(substr('.$tabla.'_ean,5,1)+65) ';
			 $sql_fields.='end, ';
		 $sql_fields.='case ';
			 $sql_fields.='when locate(left('.$tabla.'_ean,1),"02567")!=0 ';
			 $sql_fields.='then char(substr('.$tabla.'_ean,6,1)+48) ';
			 $sql_fields.='else char(substr('.$tabla.'_ean,6,1)+65) ';
			 $sql_fields.='end, ';
		 $sql_fields.='case ';
			 $sql_fields.='when locate(left('.$tabla.'_ean,1),"03689")!=0 ';
			 $sql_fields.='then char(substr('.$tabla.'_ean,7,1)+48) ';
			 $sql_fields.='else char(substr('.$tabla.'_ean,7,1)+65) ';
			 $sql_fields.='end, ';
		 $sql_fields.='char(substr('.$tabla.'_ean,8,1)+97),"!")';
		 $sql_fields.=' as item_ean8bc, ';
          
          
     }
     
     if(mb_stristr2($tabla,'ctas'))
     {
//           $empr0['empr_ctasuper']=1;
//           if($empr0['empr_ctasuper']==1)
//           {
//                $sql_fields.='case when left(ctas,1) in(1,2,3) then left(ctas,1) else left(ctas,2) end as ctan1,';
//                $sql_fields.='case when left(ctas,1) in(1,2,3) then left(ctas,3) else left(ctas,4) end as ctan2,';
//                $sql_fields.='case when left(ctas,1) in(1,2,3) then left(ctas,5) else left(ctas,6) end as ctan3,';
//                $sql_fields.='case when left(ctas,1) in(1,2,3) then left(ctas,7) else left(ctas,8) end as ctan4,';
//                $sql_fields.='ctas as ctan5,';
//                $sql_fields.='case when left(ctas,1) in(1,2,3) then ctan1.ctas_descri else ctan1b.ctas_descri end as descri1,';
//                $sql_fields.='case when left(ctas,1) in(1,2,3) then ctan2.ctas_descri else ctan2b.ctas_descri end as descri2,';
//                $sql_fields.='case when left(ctas,1) in(1,2,3) then ctan3.ctas_descri else ctan3b.ctas_descri end as descri3,';
//                $sql_fields.='case when left(ctas,1) in(1,2,3) then ctan4.ctas_descri else ctan4b.ctas_descri end as descri4,';
//                $sql_fields.='ctas00.ctas_descri as descri5,';
//           }
//           else
//           {
//                $sql_fields.='left(ctas,1) as ctan1,';
//                $sql_fields.='left(ctas,3) as ctan2,';
//                $sql_fields.='left(ctas,5) as ctan3,';
//                $sql_fields.='left(ctas,7) as ctan4,';
//                $sql_fields.='ctas as ctan5,';
//                $sql_fields.='ctan1.ctas_descri as descri1,';
//                $sql_fields.='ctan2.ctas_descri as descri2,';
//                $sql_fields.='ctan3.ctas_descri as descri3,';
//                $sql_fields.='ctan4.ctas_descri as descri4,';
//                $sql_fields.='ctas00.ctas_descri as descri5,';
//           }
     
     }
     
     
     
     
     if(mb_stristr2('.item.mdvh',$tabla))
     {
          $sql_fields.='substring_index(substring_index(lower('.$tabla.'_medidas),"x",1),"x",-1)*';
          $sql_fields.='substring_index(substring_index(lower('.$tabla.'_medidas),"x",1),"x",-1)*';
          $sql_fields.='substring_index(substring_index(lower('.$tabla.'_medidas),"x",1),"x",-1)/';
          $sql_fields.='(1000*1000) as '.$tabla.'_volcm3, ';
          
          $sql_fields.='substring_index(substring_index(lower('.$tabla.'_medidas),"x",1),"x",-1)*';
          $sql_fields.='substring_index(substring_index(lower('.$tabla.'_medidas),"x",1),"x",-1)*';
          $sql_fields.='substring_index(substring_index(lower('.$tabla.'_medidas),"x",1),"x",-1)/';
          $sql_fields.='(1000*1000*1000) as '.$tabla.'_volm3, ';
          
     }
     
     if(mb_stristr2($tabla,'vinn'))
     {
          $slq_fields.='left('.$tabla.'_codigo,3) as '.$tabla.'_vwmi,';
     }
     
     
     if(mb_stristr2('vhcl.plvh',$tabla))
     {
          $sql_fields.='case ';
          $sql_fields.='when '.$tabla.'_clase like "moto%" then "&#'.mb_ord('🏍️').';" ';
          $sql_fields.='when '.$tabla.'_clase like "cami%" then "&#'.mb_ord('🛻').';" ';
          $sql_fields.='when '.$tabla.'_clase like "auto%" then "&#'.mb_ord('🚘').';" ';
          $sql_fields.='when '.$tabla.'_clase like "veh%" then "&#'.mb_ord('🚙').';" ';
          
          $sql_fields.='else "" end as '.$tabla.'_icon,';
          $slq_fields.='substring_index('.$tabla.'_modelo," ",2) as '.$tabla.'_model1,';
          
     }
     
     
     
     if(mb_stristr2($tabla,'cntn'))
     {
//           $sql_fields.='substring(cntn_codigo,1,3) as cntn_propt, ';
//           $sql_fields.='substring(cntn_codigo,5,6) as cntn_serie, ';
//           $sql_fields.='case ';
//           $sql_fields.='when substring('.$tabla.'_codigo,3,1)  "U" then "CONTENEDOR" ';
//           $sql_fields.='else "" end as '.$tabla.'_tipo_c,';
     }
     
     
     if(mb_stristr2($tabla,'pcnt'))
     {
//           mb_ord('🏚️)
          $sql_fields.='case ';
          $sql_fields.='when '.$tabla.'_especie like "can%" then "&#'.mb_ord('🐶').';" '; //128021
          $sql_fields.='when '.$tabla.'_especie like "fel%" then "&#'.mb_ord('😺').';" '; //128008
          $sql_fields.='else "" end as '.$tabla.'_icon,';
          
     }
     
     
     if(!empty($sql_fields)){$sql_fields.='0 as '.$tabla.'_end ';}
     

     if(!empty($sql_query))
     {$sql_fields=str_replace(' from ',','.$sql_fields.' from ',$sql_query);}
     
     $sql_fields=str_replace(', from',' from',$sql_fields);
     
     return $sql_fields;
}


function calc_fields($tabla, $sql_query='', $info='****')
{
//      return ;
     if(empty($empr)){$empr=$_SESSION['empr_codigo'];}     
     if(empty($link_data)){$link_data=$_SESSION['link_data'];}
     
     $empr0=data_maes('empr',$empr);

     $trim_colu='trim_colu';
     $trim_colu='ifnull('.$trim_colu.',"")';
     $trim_colu='trim('.$trim_colu.')';
     $trim_colu='replace('.$trim_colu.',",",", ")';
     $trim_colu='replace('.$trim_colu.',";","; ")';
     $trim_colu='replace('.$trim_colu.',"+"," +")';
     $trim_colu='replace('.$trim_colu.',":",": ")';
     $trim_colu='replace('.$trim_colu.',"  "," ")';
     
$xrnd=6;     
$sql_fields='';     

     
if(mb_stristr2($sql_query,'idnum1=idnum2'))
{
$sql_fields.='sum(case when status in("O","X") then 0 else cant end) as sum_cant,';
$sql_fields.='sum(case when status in("O","X") then 0 else cant*puni end) as subt,';
}

if(mb_stristr2('.vtas.clie.carr.comp.prov.lpro',$tabla))
{
     if(mb_stristr2('.vtas',$tabla)){$ldocu1='"FC","NV"';}
     if(mb_stristr2('.clie',$tabla)){$ldocu1='"PC","PF"';}
     if(mb_stristr2('.carr',$tabla)){$ldocu1='"CA","LC"';}
     
     if(mb_stristr2('.comp',$tabla)){$ldocu1='"FP"';}
     if(mb_stristr2('.prov',$tabla)){$ldocu1='"PP","PS","DA","LP","PT"';}
     if(mb_stristr2('.lpro',$tabla)){$ldocu1='"LP"';}

     $sql_fields.='(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then 1 ';
     $sql_fields.='else -1 end)';
     $sql_fields.='as signo, ';
          
     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') then abs(cant) ';
     $sql_fields.='else abs(cant)*-1 end) ';
     $sql_fields.='as tcant, ';

     if($tabla=='prov')
     {
          $sql_fields.='sum(case ';
          $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
          $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
          $sql_fields.='then abs(peso) ';
          $sql_fields.='else abs(peso)*-1 end) ';
          $sql_fields.='as tpeso, ';
          
//           $sql_fields.='sum(case ';
//           $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
//           $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
//           $sql_fields.='then abs(volu) ';
//           $sql_fields.='else abs(volu)*-1 end) ';
//           $sql_fields.='as tvolu, ';
          
          $sql_fields.='sum(case ';
          $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
          $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
          $sql_fields.='then abs(adva) ';
          $sql_fields.='else abs(adva)*-1 end) ';
          $sql_fields.='as tadva, ';
          
          $sql_fields.='sum(case ';
          $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
          $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
          $sql_fields.='then abs(espe) ';
          $sql_fields.='else abs(espe)*-1 end) ';
          $sql_fields.='as tespe, ';
          
          $sql_fields.='sum(case ';
          $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
          $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
          $sql_fields.='then abs(dump) ';
          $sql_fields.='else abs(dump)*-1 end) ';
          $sql_fields.='as tdump, ';
          
          $sql_fields.='sum(case ';
          $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
          $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
          $sql_fields.='then abs(fodi) ';
          $sql_fields.='else abs(fodi)*-1 end) ';
          $sql_fields.='as tfodi, ';
          
          
     }

     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant*puni) ';
     $sql_fields.='else abs(cant*puni)*-1 end)';
     $sql_fields.='as tsubt, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant*puni*ifnull(dcto,0)/100) ';
     $sql_fields.='else abs(cant*puni*ifnull(dcto,0)/100)*-1 end)';
     $sql_fields.='as tdcto, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant*puni*(1-ifnull(dcto,0)/100)*prop/100) ';
     $sql_fields.='else abs(cant*puni*(1-ifnull(dcto,0)/100)*prop/100)*-1 end)';
     $sql_fields.='as tprop, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.')';
     $sql_fields.='then abs(cant*ssid) ';
     $sql_fields.='else abs(cant*ssid)*-1 end)';
     $sql_fields.='as tssid, ';
     
//      $sql_fields.='sum(case ';
//      $sql_fields.='when status in("O","X") then 0 ';
//      $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
//      $sql_fields.='then ((abs(cant*puni)*(1-ifnull(dcto,0)/100))+(abs(cant*puni)*(ifnull(ice,0)/100)))*if(ifnull(iva,0)=0,1,0) ';
//      $sql_fields.='else ((abs(cant*puni)*(1-ifnull(dcto,0)/100))+(abs(cant*puni)*(ifnull(ice,0)/100)))*if(ifnull(iva,0)=0,1,0)*-1 end)';
//      $sql_fields.='as subtiva0, ';
     
// 		$sumprop1.='when left(docu,2) in ("FC","FP","PF","PC","PP","CA","LC") then abs(cant*puni*(1-dcto/100)*prop/100) ';
//      $sumtotal1.='when left(docu,2) in ("FC","FP","PF","PC","PP","CA","LC") then (abs(cant*puni)*(1-dcto/100) +(abs(cant*puni)*(ice/100))) *(1+ifnull(iva,0)/100) +(cant*irbp) +(abs(cant*puni*(1-dcto/100))*prop/100) ';
//                                                               $sumtotal1.='else ((abs(cant*puni)*(1-dcto/100) +(abs(cant*puni)*(ice/100))) *(1+ifnull(iva,0)/100) +(cant*irbp) +(abs(cant*puni*(1-dcto/100))*prop/100))*-1 end)';
     
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then ((abs(cant*puni)*(1-ifnull(dcto,0)/100))+(abs(cant*puni)*(ifnull(ice,0)/100)))*if(ifnull(iva,0)=0,1,0) +abs(cant*puni*(1-ifnull(dcto,0)/100)*prop/100) ';
     $sql_fields.='else(((abs(cant*puni)*(1-ifnull(dcto,0)/100))+(abs(cant*puni)*(ifnull(ice,0)/100)))*if(ifnull(iva,0)=0,1,0) +abs(cant*puni*(1-ifnull(dcto,0)/100)*prop/100)) * -1 end)';
     $sql_fields.='as subtiva0, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then ((abs(cant*puni)*(1-ifnull(dcto,0)/100))+(abs(cant*puni)*(ifnull(ice,0)/100)))*if(ifnull(iva,0)!=0,1,0) ';
     $sql_fields.='else ((abs(cant*puni)*(1-ifnull(dcto,0)/100))+(abs(cant*puni)*(ifnull(ice,0)/100)))*if(ifnull(iva,0)!=0,1,0)*-1 end)';
     $sql_fields.='as subtiva, ';
     
//      $sql_fields.='sum(case ';
//      $sql_fields.='when status in("O","X") then 0 ';
//      $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
//      $sql_fields.='then ((abs(cant*puni)*(1-ifnull(dcto,0)/100))+(abs(cant*puni)*(ifnull(ice,0)/100)))*if(ifnull(iva,0)!=0,1,0) ';
//      $sql_fields.='else ((abs(cant*puni)*(1-ifnull(dcto,0)/100))+(abs(cant*puni)*(ifnull(ice,0)/100)))*if(ifnull(iva,0)!=0,1,0)*-1 end)';
//      $sql_fields.='as codPorcIva, ';
     

     $sql_fields.='(sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then ((abs(cant*puni)*(1-ifnull(dcto,0)/100))) ';
     $sql_fields.='else ((abs(cant*puni)*(1-ifnull(dcto,0)/100)))*-1 end)';
     $sql_fields.=') as subtot, ';
     if(mb_stristr2('.vtas.clie.',$tabla))
     {
          
//      if($_SESSION['user_codigo']==2)
     {
     $sql_fields.='(sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant*cuni) ';
     $sql_fields.='else abs(cant*cuni)*-1 end)';
     $sql_fields.=') as costo, ';

     $sql_fields.='(sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then ((abs(cant*puni)*(1-ifnull(dcto,0)/100))-abs(cant*cuni)) ';
     $sql_fields.='else ((abs(cant*puni)*(1-ifnull(dcto,0)/100))-abs(cant*cuni))*-1 end)';
     $sql_fields.=') as renta, ';
          
//      $sql_fields.='(sum(case ';
//      $sql_fields.='when status in("O","X") then 0 ';
//      $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
//      $sql_fields.='then ((abs(cant*puni)*(1-ifnull(dcto,0)/100))-abs(cant*cuni)) ';
//      $sql_fields.='else ((abs(cant*puni)*(1-ifnull(dcto,0)/100))-abs(cant*cuni))*-1 end)';
//      $sql_fields.=') as p_renta, ';
          
     }
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then ((abs(cant*puni)*(1-ifnull(dcto,0)/100) * (ifnull(comi,0)/100) )) ';
     $sql_fields.='else ((abs(cant*puni)*(1-ifnull(dcto,0)/100) * (ifnull(comi,0)/100) ))*-1 end)';
     $sql_fields.='as tcomi, ';
     }

     $sql_fields.='max(iva) as piva, ';
     $sql_fields.='max(ice) as pice, ';
     $sql_fields.='max(prop) as pprop, ';
     
     
     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then (abs(cant*puni)*(1-ifnull(dcto,0)/100) +(abs(cant*puni)*(ifnull(ice,0)/100))) *(ifnull(iva,0)/100) ';
     $sql_fields.='else ((abs(cant*puni)*(1-ifnull(dcto,0)/100) +(abs(cant*puni)*(ifnull(ice,0)/100))) *(ifnull(iva,0)/100))*-1 end)';
     $sql_fields.='as tiva, ';

     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant*puni)*if(ice>0,1,0) ';
     $sql_fields.='else abs(cant*puni)*if(ice>0,1,0)*-1 end)';
     $sql_fields.='as subtice, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant*puni)*(ifnull(ice,0)/100) ';
     $sql_fields.='else abs(cant*puni)*(ifnull(ice,0)/100)*-1 end)';
     $sql_fields.='as tice, ';

     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant)*if(irbp>0,1,0) ';
     $sql_fields.='else abs(cant)*if(irbp>0,1,0)*-1 end)';
     $sql_fields.='as subtirbp, ';

     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant*ifnull(irbp,0)) ';
     $sql_fields.='else abs(cant*ifnull(irbp,0))*-1 end)';
     $sql_fields.='as tirbp, ';

//            $porcIva=msql_result($res_cel1,0,'iva');
//            {
//              if($porcIva==0){$codPorcIva='0';}
//              if($porcIva==12){$codPorcIva='2';}
//              if($porcIva==14){$codPorcIva='3';}
//            }
//            $porcIce=msql_result($res_cel1,0,'ice');
//            if($porcIce==0){$codPorcIce='0';}

     if(mb_stristr2('.vtas.clie.comp.prov.',$tabla))
     {
          $sql_fields.='case ';
          $sql_fields.='when ifnull(plazo,0)<=1 then 1 ';
          $sql_fields.='when ifnull(plazo,0)>=2 then 2 ';
          $sql_fields.='else 0 end as t_pago, ';

          $sql_fields.='adddate(fecha,ifnull(plazo,0)) as vence, ';
          $sql_fields.='upper(date_format(adddate(fecha,ifnull(plazo,0)),"%W - %d/%b/%Y")) as vence_ddmy, ';
          
     }
     

     if(mb_stristr2('.prov.impo.',$tabla))
     {
//           $sql_fields.='case when ifnull(costoe1d,0)=1 then "Uni" when ifnull(costoe1d,0)=2 then "Val" when ifnull(costoe1d,0)=3 then "Kgs" else "" end as d_ce, ';
//           $sql_fields.='case when ifnull(seguro1d,0)=1 then "Uni" when ifnull(seguro1d,0)=2 then "Val" when ifnull(seguro1d,0)=3 then "Kgs" else "" end as d_sg, ';
//           $sql_fields.='case when ifnull(flete1d,0)=1 then "Uni" when ifnull(flete1d,0)=2 then "Val" when ifnull(flete1d,0)=3 then "Kgs" else "" end as d_fl, ';
//           $sql_fields.='case when ifnull(costol1d,0)=1 then "Uni" when ifnull(costol1d,0)=2 then "Val" when ifnull(costol1d,0)=3 then "Kgs" else "" end as d_cl, ';
          
          $sql_fields.='case when ifnull(costoe1d,0)=1 then "Uni" when ifnull(costoe1d,0)=2 then "Val" when ifnull(costoe1d,0)=3 then "Kgs"  when ifnull(costoe1d,0)=4 then "Vol" else "" end as d_ce, ';
          $sql_fields.='case when ifnull(seguro1d,0)=1 then "Uni" when ifnull(seguro1d,0)=2 then "Val" when ifnull(seguro1d,0)=3 then "Kgs"  when ifnull(seguro1d,0)=4 then "Vol" else "" end as d_sg, ';
          $sql_fields.='case when ifnull(flete1d,0)=1 then "Uni" when ifnull(flete1d,0)=2 then "Val" when ifnull(flete1d,0)=3 then "Kgs"  when ifnull(flete1d,0)=4 then "Vol" else "" end as d_fl, ';
          $sql_fields.='case when ifnull(costol1d,0)=1 then "Uni" when ifnull(costol1d,0)=2 then "Val" when ifnull(costol1d,0)=3 then "Kgs"  when ifnull(costol1d,0)=4 then "Vol" else "" end as d_cl, ';
          
     }
     
     
//      echo '*/*/*'.$tabla;
     if(mb_stristr2('.prov.lpro.',$tabla))
     {
          $sql_fields.='adddate(fecha,ifnull(dias,0)) as limite, ';
          $sql_fields.='upper(date_format(adddate(fecha,ifnull(dias,0)),"%W - %d/%b/%Y")) as limite_ddmy, ';
     }
     
     
     
     $sql_fields.='case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when ifnull(iva,0)=0 then 0 ';
     $sql_fields.='when ifnull(iva,0)=12 then 2 ';
     $sql_fields.='when ifnull(iva,0)=14 then 3 ';
     $sql_fields.='when ifnull(iva,0)=15 then 4 ';
     $sql_fields.='when ifnull(iva,0)=5 then 5 ';
     $sql_fields.='when ifnull(iva,0)=13 then 10 ';
     $sql_fields.='when ifnull(iva,0)>=8 and  ifnull(iva,0)<=12 then 8 '; // iva diferenciado
     $sql_fields.='else 0 end as codPorcIva, ';
     
     $sql_fields.='case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when ifnull(ice,0)=0 then 0 ';
     $sql_fields.='else 0 end as codPorcIce, ';
     
     
     
     if(mb_stristr2('.vtas.clie',$tabla))
     {
          
          $sql_fields.='sum(case ';
          $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
          $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
          $sql_fields.='then puni*(1+ifnull(ice,0)/100)*(1+ifnull(iva,0)/100)+irbp ';
          $sql_fields.='else 0 end)';
          $sql_fields.='as pvp, ';

          $sql_fields.='sum(case ';
          $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
          $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
          $sql_fields.='then abs(cant*pcp1) ';
          $sql_fields.='else abs(cant*pcp1)*-1 end)';
          $sql_fields.='as tpcp1, ';
     }

     
     if(mb_stristr2('.comp.prov',$tabla))
     {
          $sql_fields.='sum(case ';
          $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
          $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
          $sql_fields.='then puni*(1-ifnull(dcto,0)/100) ';
          $sql_fields.='else 0 end)';
          $sql_fields.='as cuni1, ';

     }

     if(mb_stristr2('.vtas.clie',$tabla))
     {
          $sql_fields.='sum(cant) as cant_conv, ';
//           echo '==='.$sql_fields;
     }

     if(mb_stristr2('.comp.prov.lpro',$tabla))
     {
          $sql_fields.='sum(cant*(ifnull(uxpre,1)*ifnull(facon,1)) ) as cant_conv, ';
     
          $sql_fields.='(sum(case ';
          $sql_fields.='when status in("O","X") then 0 ';
          $sql_fields.='else (cant*puni*(1-ifnull(dcto,0)/100)) / (cant*(ifnull(uxpre,1)*ifnull(facon,1))) end)';
          $sql_fields.=') as cuni1_conv, ';
          
//           echo '*/*/*/';
          
          $sql_fields.='((case ';
          $sql_fields.='when status in("O","X") then 0 ';
          $sql_fields.='when ifnull(uxpre,0)*ifnull(facon,0)=0 then (cant*puni*(1-ifnull(dcto,0)/100)) ';
          $sql_fields.='else (cant*puni*(1-ifnull(dcto,0)/100)) / (cant*uxpre*facon) end)';
          $sql_fields.=') as puni1_conv, ';
          
     }     
     
     
     
     
//      if(mb_stristr2('.demo.hg01.anml',$cfg_empr))
     if(@mb_stristr2('.demo.hg01.anml',$_SESSION['empr_codigo']))
     if(mb_stristr2('.vtas',$tabla))
     {
          $sql_fields.='case when status="X" or status="O" then 0 else max(efectivo) end as tefect,';
          $sql_fields.='case when status="X" or status="O" then 0 else max(tarjeta) end as ttarje,';
//           $sql_fields.='case when status="X" or status="O" then 0 else max(credito) end as tcredi,';
          $sql_fields.='case when status="X" or status="O" then 0 else max(transf) end as ttransf,';
          
     }

     if(mb_stristr2('.vtas.inve.infi',$tabla))
     {
          $sql_fields.='case when status="X" or status="O" then "" else max(lote) end as mlote,';
          $sql_fields.='case when status="X" or status="O" then "" else max(numserie) end as mserie,';
     }

     if(mb_stristr2('.vtas.clie',$tabla))
     {
          if($_SESSION['empr_clinica']==1 or $_SESSION['empr_veterinaria']==1)
          {$sql_fields.='max(trim(pcnt)) as pcnt_m,';}
//           if($_SESSION['empr_automotriz']==1)
//           {$sql_fields.='max(trim(vhcl)) as vhcl_m,';}
          if($_SESSION['empr_automotriz']==1)
          {$sql_fields.='max(trim(plvh)) as plvh_m,';}
          if($_SESSION['empr_garage']==1)
          {$sql_fields.='substring(timediff(hora2,hora1),1,5) as hora12,';}
          
          if($empr0['empr_codigo']=='pcs1')
          {$sql_fields.='max(trim(cntn)) as cntn_m,';}
     }

     
     
     if(mb_stristr2('.comp.prov',$tabla))
     {
          $sql_fields.='max(trim(cair)) as cair_m,';
          $sql_fields.='max(trim(ctas)) as ctas_m,';
     }
     
     if(mb_stristr2('.comp.prov',$tabla))
     {
          $sql_fields.='case ';
          $sql_fields.='when length(trim(toenti))=10 then "C" ';
          $sql_fields.='when length(trim(toenti))=13 then "R" ';
          $sql_fields.='else "-" end as c_r,';
          
     }
     
     
     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then  (abs(cant*puni)*(1-ifnull(dcto,0)/100) +(abs(cant*puni)*(ifnull(ice,0)/100))) *(1+ifnull(iva,0)/100) +(cant*irbp) +(abs(cant*puni*(1-ifnull(dcto,0)/100))*prop/100) ';
     $sql_fields.='else ((abs(cant*puni)*(1-ifnull(dcto,0)/100) +(abs(cant*puni)*(ifnull(ice,0)/100))) *(1+ifnull(iva,0)/100) +(cant*irbp) +(abs(cant*puni*(1-ifnull(dcto,0)/100))*prop/100))*-1 end)';
     $sql_fields.='as saldo, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='else ((abs(cant*puni)*(1-ifnull(dcto,0)/100) +(abs(cant*puni)*(ifnull(ice,0)/100))) *(1+ifnull(iva,0)/100) +(cant*irbp) +(abs(cant*puni*(1-ifnull(dcto,0)/100))*prop/100)) end)';
     $sql_fields.='as total, ';

     
     if($empr0['empr_codigo']=='mhec')
     if(mb_stristr2('.comp.prov.impo',$tabla))
     {
          $sql_fields.='sum(case ';
          $sql_fields.='when status in("O","X") then 0 ';
          $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
          $sql_fields.='then abs(cant*(puni2-puni)) ';
          $sql_fields.='else abs(cant*(puni2-puni)*-1) end)';
          $sql_fields.='as l_comp, ';
          
          $sql_fields.='sum(case ';
          $sql_fields.='when status in("O","X") then 0 ';
          $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
          $sql_fields.='then abs(cant*puni2) ';
          $sql_fields.='else abs(cant*puni2)*-1 end)';
          $sql_fields.='as tsubt2, ';
     }
     
     
}



     
     
     
if(mb_stristr2('.guia',$tabla))
{
     if(mb_stristr2('.guia',$tabla)){$ldocu1='"GR"';}
          
     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant) ';
     $sql_fields.='else abs(cant)*-1 end) ';
     $sql_fields.='as tcant, ';

     
}
     
if(mb_stristr2('.opro',$tabla))
{
     if(mb_stristr2('.opro',$tabla)){$ldocu1='"OP"';}
          
     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant) ';
     $sql_fields.='else abs(cant)*-1 end) ';
     $sql_fields.='as tcant, ';
     
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='else abs(cant*puni) end)';
     $sql_fields.='as total, ';
     

     if($_SESSION['empr_codigo']=='hfre')
     {
          $sql_fields.='sum(case ';
          $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
          $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
          $sql_fields.='then abs(saco) ';
          $sql_fields.='else abs(saco)*-1 end) ';
          $sql_fields.='as tsaco, ';
     }
     
}

if(mb_stristr2('.susa',$tabla))
{
     if(mb_stristr2('.susa',$tabla)){$ldocu1='"SS"';}
          
     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='else ifnull(sueldo,0)+ifnull(alimentacion,0)+ifnull(movilizacion,0)+ifnull(comision,0) end) ';
     $sql_fields.='as total, ';
}
     
if(mb_stristr2('.nomi',$tabla))
{
//      if(mb_stristr2('.susa',$tabla)){$ldocu1='"SS"';}
          
     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='else ifnull(cant,0)*ifnull(puni,0) end)';
     $sql_fields.='as total, ';
     
     
}
     
     
if(mb_stristr2('.cont',$tabla))
{
     $sql_fields='';    
     
     if($empr0['empr_codigo']=='posa')
     {
          $sql_fields.='if(ctas_1!=ctas_2,"S","-") as modif,';
     }
     
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cant>0 ';
     $sql_fields.='then (cant) ';
     $sql_fields.='else 0 end)';
     $sql_fields.='as debito, ';
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cant<0 ';
     $sql_fields.='then abs(cant) ';
     $sql_fields.='else 0 end)';
     $sql_fields.='as credito, ';
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cant>0 ';
     $sql_fields.='then abs(cant) ';
     $sql_fields.='else abs(cant)*-1 end)';
     $sql_fields.='as saldo, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='else abs(cant) end)';
     $sql_fields.='as total, ';

     if($empr0['empr_ctasuper']==1)
     {
          $sql_fields.='case when left(ctas,1) in(1,2,3) then left(ctas,1) else left(ctas,2) end as ctas_codi1,';
          $sql_fields.='case when left(ctas,1) in(1,2,3) then left(ctas,3) else left(ctas,4) end as ctas_codi2,';
          $sql_fields.='case when left(ctas,1) in(1,2,3) then left(ctas,5) else left(ctas,6) end as ctas_codi3,';
          $sql_fields.='case when left(ctas,1) in(1,2,3) then left(ctas,7) else left(ctas,8) end as ctas_codi4,';
          $sql_fields.='trim(ctas) as ctas_codi5,';
     }
     
     if($empr0['empr_ctasuper']!=1)
     {
          $sql_fields.='case when left(ctas,1) in(1,2,3) then left(ctas,1) else left(ctas,1) end as ctas_codi1,';
          $sql_fields.='case when left(ctas,1) in(1,2,3) then left(ctas,3) else left(ctas,3) end as ctas_codi2,';
          $sql_fields.='case when left(ctas,1) in(1,2,3) then left(ctas,5) else left(ctas,5) end as ctas_codi3,';
          $sql_fields.='case when left(ctas,1) in(1,2,3) then left(ctas,7) else left(ctas,7) end as ctas_codi4,';
          $sql_fields.='trim(ctas) as ctas_codi5,';
     }
     
     
     
//      if(mb_stristr2($sql_query,'join ctas0'))
//      {
//           $sql_fields.='case when left(ctas,1) in(1,2,3) then left(ctas,1) else left(ctas,2) end as ctas_desc1,';
//           $sql_fields.='case when left(ctas,1) in(1,2,3) then left(ctas,3) else left(ctas,4) end as ctas_desc2,';
//           $sql_fields.='case when left(ctas,1) in(1,2,3) then left(ctas,5) else left(ctas,6) end as ctas_desc3,';
//           $sql_fields.='case when left(ctas,1) in(1,2,3) then left(ctas,7) else left(ctas,8) end as ctas_desc4,';
//           $sql_fields.='trim(ctas) as ctas_codi5,';
//      }
     
}    
     
if(mb_stristr2('.plan',$tabla))
{
     $sql_fields='';    
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cant>0 ';
     $sql_fields.='then abs(cant) ';
     $sql_fields.='else 0 end)';
     $sql_fields.='as horas, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='else abs(cant*puni) end)';
     $sql_fields.='as total, ';

//      $sql_fields.='concat(left(asub,4),"-0000") as asub0000, ';
     
}    

if(mb_stristr2('.atvd',$tabla))
{
     $sql_fields='';    
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cant>0 ';
     $sql_fields.='then abs(cant) ';
     $sql_fields.='else 0 end)';
     $sql_fields.='as horas, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='else abs(cant*puni) end)';
     $sql_fields.='as total, ';

//      $sql_fields.='concat(left(asub,4),"-0000") as asub0000, ';
     
}    

if(mb_stristr2('.hcli',$tabla))
{
     $sql_fields='';    
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='else abs(cant*puni) end)';
     $sql_fields.='as total, ';
     
     $sql_fields.='max(tempcor) as mtempcor, ';
     $sql_fields.='max(freqcar) as mfreqcar, ';
     $sql_fields.='max(freqres) as mfreqres, ';
     $sql_fields.='max(presart) as mpresart, ';
     $sql_fields.='max(pesokg) as mpesokg, ';
     $sql_fields.='max(tallacm) as mtallacm, ';
     $sql_fields.='max(pesokg/ power(tallacm/100,2)) as imc_cli, ';
     $sql_fields.='max(pesokg/ (hshcm*otcm)) as imc_vet, ';

      $sql_fields.='case ';
      $sql_fields.='when fase="vitales" then concat(" /TC : ",ifnull(round(tempcor,1),"_")," /FR : ",ifnull(freqres,"_")," /FC : ",ifnull(freqcar,"_")," /PA : ",ifnull(presart,"_")) ';

     $sql_fields.='when fase="medible" then concat(';
     $sql_fields.='"/Kg : ",ifnull(pesokg,"_"),';
     
     if($_SESSION['empr_clinica']==1)
     {
     $sql_fields.='"/Cm : ",ifnull(tallacm,"_"),';
     }

     if($_SESSION['empr_veterinaria']==1)
     {
     $sql_fields.='"/HSh : ",ifnull(hshcm,"_"),';
     $sql_fields.='"/OT : ",ifnull(otcm,"_"),';
     $sql_fields.='"/IMC : ", round(ifnull(pesokg,0)/(ifnull(hshcm,1)*ifnull(otcm,1)),2),';
     }
     
     $sql_fields.='"") ';
     
      $sql_fields.='when fase="medica" then concat(" :: Dosis : ",round(cant,2)) ';
      $sql_fields.='when fase="explora" then concat(';
     if($_SESSION['empr_veterinaria']==1)
     {
      $sql_fields.='"/MC : ",if(mucosa=1,"Normales",if(mucosa=2,"Anemicas",if(mucosa=3,"Ictericas",if(mucosa=4,"Congestivas",if(mucosa=5,"Cianoticas","_"))))),';
      $sql_fields.='"/DH : ",if(dhidra=1,"Muy Leve",if(dhidra=2,"Leve",if(dhidra=3,"Moderada",if(dhidra=4,"Severa",if(dhidra=5,"Muy Severa","_"))))),';
      $sql_fields.='"/PQ : ",if(ptquia=1,"Muy Leve",if(ptquia=2,"Leve",if(ptquia=3,"Moderada",if(ptquia=4,"Severa",if(ptquia=5,"Muy Severa","_"))))),';
      $sql_fields.='"/EP : ",if(ectopr=1,"Muy Pocos",if(ectopr=2,"Pocos",if(ectopr=3,"Muchos",if(ectopr=4,"Abundantes",if(ectopr=5,"Muy Abundantes","_"))))),';
     }
      $sql_fields.='"") ';
      $sql_fields.='else ifnull(detalle,"") ';
      $sql_fields.='end as notas, ';
     
//      echo '*/*/*/*======='.$sql_fields;
     
     
     
     $sql_fields.='max(case ';
     $sql_fields.='when dhidra=1 then "Muy Leve" ';
     $sql_fields.='when dhidra=2 then "Leve" ';
     $sql_fields.='when dhidra=3 then "Moderada" ';
     $sql_fields.='when dhidra=4 then "Severa" ';
     $sql_fields.='when dhidra=5 then "Muy Severa" ';
     $sql_fields.='else "_" end) as dhidrad,';

     $sql_fields.='max(case ';
     $sql_fields.='when ptquia=1 then "SI" ';
     $sql_fields.='when ptquia=2 then "NO" ';
//      $sql_fields.='when ptquia=3 then "Moderada" ';
//      $sql_fields.='when ptquia=4 then "Severa" ';
//      $sql_fields.='when ptquia=5 then "Muy Severa" ';
     $sql_fields.='else "_" end) as ptquiad,';

     
     $sql_fields.='max(case ';
//      $sql_fields.='when ectopr=1 then "Muy Leve" ';
     $sql_fields.='when ectopr=2 then "Poco" ';
     $sql_fields.='when ectopr=3 then "Mucho" ';
     $sql_fields.='when ectopr=4 then "Abundante" ';
//      $sql_fields.='when ectopr=5 then "Muy Severa" ';
     $sql_fields.='else "-" end) as ectoprd,';

     
}    

     
     
if(mb_stristr2('.otbj',$tabla))
{
     $sql_fields='';    

     
//      if(mb_stristr2('.vtas',$tabla)){$ldocu1='"FC","NV"';}
     
     $ldocu1='"OT"';

     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') then abs(cant) ';
     $sql_fields.='else abs(cant)*-1 end) ';
     $sql_fields.='as tcant, ';

     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant*puni) ';
     $sql_fields.='else abs(cant*puni)*-1 end)';
     $sql_fields.='as tsubt, ';
     
//      $sql_fields.='sum(case ';
//      $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
//      $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
//      $sql_fields.='then abs(cant*puni*ifnull(dcto,0)/100) ';
//      $sql_fields.='else abs(cant*puni*ifnull(dcto,0)/100)*-1 end)';
//      $sql_fields.='as tdcto, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then ((abs(cant*puni)*(1-ifnull(dcto,0)/100)))*if(ifnull(iva,0)=0,1,0)  ';
     $sql_fields.='else(((abs(cant*puni)*(1-ifnull(dcto,0)/100)))*if(ifnull(iva,0)=0,1,0) ) * -1 end)';
     $sql_fields.='as subtiva0, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then ((abs(cant*puni)*(1-ifnull(dcto,0)/100)))*if(ifnull(iva,0)!=0,1,0) ';
     $sql_fields.='else ((abs(cant*puni)*(1-ifnull(dcto,0)/100)))*if(ifnull(iva,0)!=0,1,0)*-1 end)';
     $sql_fields.='as subtiva, ';
     

     $sql_fields.='(sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then ((abs(cant*puni)*(1-ifnull(dcto,0)/100))) ';
     $sql_fields.='else ((abs(cant*puni)*(1-ifnull(dcto,0)/100)))*-1 end)';
     $sql_fields.=') as subtot, ';
//      if(mb_stristr2('.vtas.clie.',$tabla))
     {
   
     if(1==2)
     {
     $sql_fields.='(sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant*cuni) ';
     $sql_fields.='else abs(cant*cuni)*-1 end)';
     $sql_fields.=') as costo, ';

     $sql_fields.='(sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then ((abs(cant*puni)*(1-ifnull(dcto,0)/100))-abs(cant*cuni)) ';
     $sql_fields.='else ((abs(cant*puni)*(1-ifnull(dcto,0)/100))-abs(cant*cuni))*-1 end)';
     $sql_fields.=') as renta, ';
          
     }
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then ((abs(cant*puni)*(1-ifnull(dcto,0)/100) * (ifnull(comi,0)/100) )) ';
     $sql_fields.='else ((abs(cant*puni)*(1-ifnull(dcto,0)/100) * (ifnull(comi,0)/100) ))*-1 end)';
     $sql_fields.='as tcomi, ';
     }

     $sql_fields.='max(iva) as piva, ';
//      $sql_fields.='max(ice) as pice, ';
//      $sql_fields.='max(prop) as pprop, ';
     
     
     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then (abs(cant*puni)*(1-ifnull(dcto,0)/100) ) *(ifnull(iva,0)/100) ';
     $sql_fields.='else ((abs(cant*puni)*(1-ifnull(dcto,0)/100) ) *(ifnull(iva,0)/100))*-1 end)';
     $sql_fields.='as tiva, ';

     
     
     
//      if(mb_stristr2('.vtas.clie',$tabla))
     {
          
          $sql_fields.='sum(case ';
          $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
          $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
          $sql_fields.='then puni*(1+ifnull(iva,0)/100) ';
          $sql_fields.='else 0 end)';
          $sql_fields.='as pvp, ';

//           $sql_fields.='sum(case ';
//           $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
//           $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
//           $sql_fields.='then abs(cant*pcp1) ';
//           $sql_fields.='else abs(cant*pcp1)*-1 end)';
//           $sql_fields.='as tpcp1, ';
     }

     if(mb_stristr2('.vtas',$tabla))
     {
          $sql_fields.='case when status="X" or status="O" then 0 else max(efectivo) end as tefect,';
          $sql_fields.='case when status="X" or status="O" then 0 else max(tarjeta) end as ttarje,';
//           $sql_fields.='case when status="X" or status="O" then 0 else max(credito) end as tcredi,';
          $sql_fields.='case when status="X" or status="O" then 0 else max(transf) end as ttransf,';
          
     }

     if(mb_stristr2('.vtas.inve.infi',$tabla))
     {
          $sql_fields.='case when status="X" or status="O" then "" else max(lote) end as mlote,';
          $sql_fields.='case when status="X" or status="O" then "" else max(numserie) end as mserie,';
     }

//      if(mb_stristr2('.vtas.clie',$tabla))
     {
          if($_SESSION['empr_automotriz']==1)
          {$sql_fields.='max(trim(plvh)) as plvh_m,';}
          if($_SESSION['empr_garage']==1)
          {$sql_fields.='substring(timediff(hora2,hora1),1,5) as hora12,';}
          
     }

     
     
//      $sql_fields.='sum(case ';
//      $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
//      $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
//      $sql_fields.='then  (abs(cant*puni)*(1-ifnull(dcto,0)/100) +(abs(cant*puni)*(ifnull(ice,0)/100))) *(1+ifnull(iva,0)/100) +(cant*irbp) +(abs(cant*puni*(1-ifnull(dcto,0)/100))*prop/100) ';
//      $sql_fields.='else ((abs(cant*puni)*(1-ifnull(dcto,0)/100) +(abs(cant*puni)*(ifnull(ice,0)/100))) *(1+ifnull(iva,0)/100) +(cant*irbp) +(abs(cant*puni*(1-ifnull(dcto,0)/100))*prop/100))*-1 end)';
//      $sql_fields.='as saldo, ';

     
     
     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='else ((abs(cant*puni)*(1-ifnull(dcto,0)/100)) *(1+ifnull(iva,0)/100) ) end)';
     $sql_fields.='as total, ';

     
     
//      $sql_fields.='item, ';
     $sql_fields.='max(odometro) as modometro, ';
     $sql_fields.='max(horometro) as mhorometro, ';
     $sql_fields.='max(reservorio) as mreservorio, ';
     
      $sql_fields.='case ';
      $sql_fields.='when fase like "recep%" then concat(" /KM : ",ifnull(odometro,0)," /HR : ",ifnull(horometro,0)," ") ';
      $sql_fields.='when fase like "repue%" then item_descri ';
      $sql_fields.='when fase like "traba%" then item_descri ';
      $sql_fields.='when fase like "contr%" then item_descri ';
//       $sql_fields.='else ifnull(detalle,"") ';
      $sql_fields.='else "" ';
      $sql_fields.='end as detalles, ';
     

     
}    

     
     
if(mb_stristr2('.btcr.avso',$tabla))
{
     $sql_fields.='case when docu ="BTC1" then fecha end as fecha1, ';
     $sql_fields.='case when docu ="BTC1" then hora end as hora1, ';
     $sql_fields.='case when docu ="BTC2" then fecha end as fecha2, ';
     $sql_fields.='case when docu ="BTC2" then hora end as hora2, ';
     
     $sql_fields.='timestampdiff(day,concat(fecha," ",hora),now()) as dias,';
     $sql_fields.='timestampdiff(hour,concat(fecha," ",hora),now())%24 as horas,';
     $sql_fields.='timestampdiff(minute,concat(fecha," ",hora),now())%60 as mins,';
     
}
     
     
if(mb_stristr2('.infi',$tabla))
{
     if(mb_stristr2('.infi',$tabla)){$ldocu1='"IF"';}
     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant) ';
     $sql_fields.='else abs(cant)*1 end) ';
     $sql_fields.='as tcant, ';

     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant*puni) ';
     $sql_fields.='else abs(cant*puni)*1 end)';
     $sql_fields.='as tsubt, ';
     
}
     
if(mb_stristr2('.inve.prXod',$tabla))
{
     if(mb_stristr2('.inve',$tabla)){$ldocu1='"IB"';}
     if(mb_stristr2('.prXod',$tabla)){$ldocu1='"IP"';}
     
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then 1 ';
     $sql_fields.='else -1 end)';
     $sql_fields.='as signo, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant) ';
     $sql_fields.='else abs(cant)*1 end) ';
     $sql_fields.='as tcant, ';

     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant*puni) ';
     $sql_fields.='else abs(cant*puni)*1 end)';
     $sql_fields.='as tsubt, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant) ';
     $sql_fields.='else 0 end)';
     $sql_fields.='as ingreso, ';
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)not in('.$ldocu1.') ';
     $sql_fields.='then abs(cant) ';
     $sql_fields.='else 0 end)';
     $sql_fields.='as egreso, ';
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant) ';
     $sql_fields.='else abs(cant)*-1 end)';
     $sql_fields.='as stock, ';

     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then 0 ';
     $sql_fields.='else ifnull(nega,0) end) ';
     $sql_fields.='as tnega, ';
     
     $sql_fields.='max(case ';
     $sql_fields.='when status="X" or status="O" then "" ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') then "" ';
//      $sql_fields.='when ifnull(nega,0)>0 then bode ';
     $sql_fields.='when nega>0 then bode ';
     $sql_fields.='else "" end) ';
     $sql_fields.='as bnega, ';
     
     //cpu consumo promedio unidades
     $prdo_cpu=date('Y-m-01',strtotime($fecha2.' -365 days'));
     $docu_cpu='"EB10","EB19"';
     $docu_ibc='"IB10","IB15"';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when docu in('.$docu_cpu.') ';
     $sql_fields.='then abs(cant) ';
     $sql_fields.='else 0 end)';
     $sql_fields.='as eb_cpu, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when docu in('.$docu_ibc.') ';
     $sql_fields.='then abs(cant) ';
     $sql_fields.='else 0 end)';
     $sql_fields.='as ib_cpu, ';
     
     $sql_fields.='(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when docu in('.$docu_ibc.') ';
     $sql_fields.='then ifnull(dias,1) ';
     $sql_fields.='else 0 end)';
     $sql_fields.='as ib_dep, ';
     
     
     
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant*puni) ';
     $sql_fields.='else 0 end)';
     $sql_fields.='as debito, ';
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)not in('.$ldocu1.') ';
     $sql_fields.='then abs(cant*puni) ';
     $sql_fields.='else 0 end)';
     $sql_fields.='as credito, ';
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant*puni) ';
     $sql_fields.='else abs(cant*puni)*-1 end)';
     $sql_fields.='as saldo, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='else abs(cant*puni) end)';
     $sql_fields.='as total, ';
     
}

     
if(mb_stristr2('.pduc',$tabla))
{
     $sql_fields='';    
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cant>0 ';
     $sql_fields.='then 1 ';
     $sql_fields.='else -1 end)';
     $sql_fields.='as signo, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when ifnull(status,"")in("O","X") then 0 ';
     $sql_fields.='when cant>0 ';
     $sql_fields.='then abs(cant) ';
     $sql_fields.='else abs(cant)*1 end) ';
     $sql_fields.='as tcant, ';

     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cant>0 ';
     $sql_fields.='then abs(cant*puni) ';
     $sql_fields.='else abs(cant*puni)*1 end)';
     $sql_fields.='as tsubt, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cant>0 ';
     $sql_fields.='then abs(cant) ';
     $sql_fields.='else 0 end)';
     $sql_fields.='as salida, ';
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cant<0 ';
     $sql_fields.='then abs(cant) ';
     $sql_fields.='else 0 end)';
     $sql_fields.='as entrada, ';
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cant<0 ';
     $sql_fields.='then abs(cant) ';
     $sql_fields.='else abs(cant)*-1 end)';
     $sql_fields.='as stock, ';

     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cant<0 ';
     $sql_fields.='then abs(cant*puni) ';
     $sql_fields.='else 0 end)';
     $sql_fields.='as debito, ';
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cant>0 ';
     $sql_fields.='then abs(cant*puni) ';
     $sql_fields.='else 0 end)';
     $sql_fields.='as credito, ';
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cant<0 ';
     $sql_fields.='then abs(cant*puni) ';
     $sql_fields.='else abs(cant*puni)*-1 end)';
     $sql_fields.='as saldo, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='else abs(cant*puni) end)';
     $sql_fields.='as total, ';
     
     
}    
     
     
     
     
// if(mb_stristr2('.caja.bcos.dxco.dxpa.retc.retp',$tabla))
if(mb_stristr2('.caja.bcos.dxco.dxpa',$tabla))
{
     $ldocu1='';
     if($tabla=='bcos'){$ldocu1='"DB","DC"';}
     if($tabla=='caja'){$ldocu1='"IE","IC"';}
     if($tabla=='dxco'){$ldocu1='"AC","XC"';}
     if($tabla=='dxpa'){$ldocu1='"AP","XP"';}
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then 1 ';
     $sql_fields.='else -1 end)';
     $sql_fields.='as signo, ';
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant*puni) ';
     $sql_fields.='else 0 end)';
     $sql_fields.='as debito, ';
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)not in('.$ldocu1.') ';
     $sql_fields.='then abs(cant*puni) ';
//      if(mb_stristr2('.retc.retp',$tabla)){$sql_fields.='/100 ';}
     $sql_fields.='else 0 end)';
     $sql_fields.='as credito, ';
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when left(docu,2)in('.$ldocu1.') ';
     $sql_fields.='then abs(cant*puni) ';
     $sql_fields.='else abs(cant*puni)*(-1) ';
//      if(mb_stristr2('.retc.retp',$tabla)){$sql_fields.='/100 ';}
     $sql_fields.='end)';
     $sql_fields.='as saldo, ';

     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='else abs(cant*puni) ';
//      if(mb_stristr2('.retc.retp',$tabla)){$sql_fields.='/100 ';}
     $sql_fields.='end)';
     $sql_fields.='as total, ';
     
}

     
if(mb_stristr2('.retc.retp.',$tabla))
{

     $ldocu1='';

     $sql_fields.='max(case when cair*1>=300 then cair else 0 end) as cod_cair,';
     $sql_fields.='max(case when cair*1<300 then cair else 0 end) as cod_iva,';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cair*1>=300 ';
     $sql_fields.='then cant ';
     $sql_fields.='else 0 ';
     $sql_fields.='end)';
     $sql_fields.='as base_rta, ';
     
     $sql_fields.='max(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cair*1>=300 then puni ';
     $sql_fields.='else 0 ';
     $sql_fields.='end)';
     $sql_fields.='as porc_rta, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cair*1>=300 ';
     $sql_fields.='then cant*puni/100 ';
     $sql_fields.='else 0 ';
     $sql_fields.='end)';
     $sql_fields.='as rete_rta, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cair*1<300 ';
     $sql_fields.='then cant ';
     $sql_fields.='else 0 ';
     $sql_fields.='end)';
     $sql_fields.='as base_iva, ';
     
     $sql_fields.='max(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cair*1<300 then puni ';
     $sql_fields.='else 0 ';
     $sql_fields.='end)';
     $sql_fields.='as porc_iva, ';
     
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='when cair*1<300 ';
     $sql_fields.='then cant*puni/100 ';
     $sql_fields.='else 0 ';
     $sql_fields.='end)';
     $sql_fields.='as rete_iva, ';
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='else abs(cant*puni)/100 ';
     $sql_fields.='end)';
     $sql_fields.='as saldo, ';
     
          
     $sql_fields.='sum(case ';
     $sql_fields.='when status in("O","X") then 0 ';
     $sql_fields.='else abs(cant*puni)/100 ';
     $sql_fields.='end)';
     $sql_fields.='as total, ';
     
     $poriva='30.70.100';
     $poriva=explode('.',$poriva);
     for($idx=0; $idx<count($poriva); $idx++)
     {
          $sql_fields.='sum(case ';
          $sql_fields.='when status in("O","X") then 0 ';
          $sql_fields.='when puni='.$poriva[$idx].' then abs(cant*puni)/100 ';
          $sql_fields.='else 0 ';
          $sql_fields.='end)';
          $sql_fields.='as total'.$poriva[$idx].', ';
     }
     
}
     

if(mb_stristr2('.jwib',$tabla))
{
     $sql_fields='';
     $sql_fields.='date_format(fecha,"%x-%v") as fsema,';
     $sql_fields.='upper(date_format(adddate(fecha,ifnull(plazo,0)),"%W - %d/%b/%Y")) as vence_ddmy, ';
}
     
if(mb_stristr2('.jwrv',$tabla))
{
     $fecha_nt=date('Y-m-d',strtotime('-12 month'));
     $sql_fields='';
     
     $sql_fields.='max(ndpto)as mndpto,';
     
     $sql_fields.='case when mod(ndpto,1)=0 then lpad(floor(ndpto),3,"0") else CONCAT(LPAD(FLOOR(ndpto*1), 3, "0"),".",RIGHT(FORMAT(ndpto,1),1)) end as ndptoA,';
     
     $sql_fields.='case when hora=1 then "Mañana" when hora=2 then "Tarde" when hora=3 then "Noche" else "N/A" end as horario, ';
     $sql_fields.='case when ifnull(visita,"") not in("NC","NT") then 1 end as vi, ';
     $sql_fields.='case when visita="NC" then 1 end as nc, ';
     $sql_fields.='case when visita="NT" and fecha>="'.$fecha_nt.'" then 1 end as nt, ';
     
//      ❌⛔🛑🫥😠🚪🏚️😀😤😬😡😶🤬🤐🫥🕸️&#65039; &#128566;&#129296;🆗❎✖️
     
     $sql_fields.='case ';
     $sql_fields.='when visita="NT" then "&#'.mb_ord('😡').';" ';
//      $sql_fields.='when visita="OK" then "&#'.mb_ord('😀').';" ';
     $sql_fields.='when visita="OK" then "&#'.mb_ord('🆗').';" ';
//      🏚️🕸️
     $sql_fields.='when visita="DH" then "&#'.mb_ord('🕸️').';" ';
     $sql_fields.='when visita="NC" then "&#'.mb_ord('✖️').';" ';
     $sql_fields.='when visita="OC" then "&#'.mb_ord('🤐').';" ';
     $sql_fields.='end as visita_icon, ';
     
//      $sql_fields.='case ';
//      $sql_fields.='when hora like "M%" then "&#'.mb_ord('🌅').';" ';
//      $sql_fields.='when hora like "T%" then "&#'.mb_ord('🌇').';" ';
//      $sql_fields.='when hora like "N%" then "&#'.mb_ord('🌃').';" ';
//      $sql_fields.='end as hora_icon, ';
     
//      ☀️
     $sql_fields.='case ';
     $sql_fields.='when hora like "M%" then "&#'.mb_ord('🌞').';" ';
     $sql_fields.='when hora like "T%" then "&#'.mb_ord('🌇').';" ';
     $sql_fields.='when hora like "N%" then "&#'.mb_ord('🌙').';" ';
     $sql_fields.='when hora like "1%" then "&#'.mb_ord('🌞').';" ';
     $sql_fields.='when hora like "2%" then "&#'.mb_ord('🌇').';" ';
     $sql_fields.='when hora like "3%" then "&#'.mb_ord('🌙').';" ';
     $sql_fields.='end as hora_icon, ';
     
     $sql_fields.='case ';
     $sql_fields.='when etario like "I%" then "&#'.mb_ord('😋').';" ';
     $sql_fields.='when etario like "J%" then "&#'.mb_ord('😎').';" ';
     $sql_fields.='when etario like "A%" then "&#'.mb_ord('😐').';" ';
     $sql_fields.='when etario like "M%" then "&#'.mb_ord('🤓').';" ';
     $sql_fields.='end as etario_icon, ';
     
     
//      echo '==='.$sql_fields;
     
     
//      🚫
}
     
if(mb_stristr2('.jwrt',$tabla))
{
     $fecha_nt=date('Y-m-d',strtotime('-4 month'));
     $sql_fields='';
     $sql_fields.='adddate(fecha,ifnull(plazo,0)-1) as vence, ';
     $sql_fields.='upper(date_format(adddate(fecha,ifnull(plazo,0)),"%W - %d/%b/%Y")) as vence_ddmy, ';
}

     
if(mb_stristr2('.jwib.jwmc',$tabla))
{
     $sql_fields='';
     
     $sql_fields.='case ';
     $sql_fields.='when ndia=1 then "LUNES" ';
     $sql_fields.='when ndia=2 then "MARTES" ';
     $sql_fields.='when ndia=3 then "MIERCOLES" ';
     $sql_fields.='when ndia=4 then "JUEVES" ';
     $sql_fields.='when ndia=5 then "VIERNES" ';
     $sql_fields.='when ndia=6 then "SABADO" ';
     $sql_fields.='when ndia=7 then "DOMINGO" ';
     $sql_fields.='else "-" end as ndia_descri, ';
     
     
     if($tabla=='jwib')
     {
          $sql_fields.='case when docu="VM30" then 1 end as lb, ';
          for($idx=1; $idx<=5; $idx++)
          {$sql_fields.='case when docu="VM3'.$idx.'" then 1 end as m'.$idx.', ';}

          $sql_fields.='case when docu="VM30" then substring(fecha,1,7) end as f_lb, ';
          for($idx=1; $idx<=5; $idx++)
          {$sql_fields.='case when docu="VM3'.$idx.'" then substring(fecha,1,7) end as f_m'.$idx.', ';}

          for($idx=10; $idx<=20; $idx++)
          {$sql_fields.='case when docu="AS'.$idx.'" then substring(fecha,1,7) end as f_s'.$idx.', ';}

          for($idx=10; $idx<=50; $idx++)
          {$sql_fields.='case when docu="VM'.$idx.'" then substring(fecha,1,7) end as f_v'.$idx.', ';}
     }
     
     if($tabla=='jwmc')
     {
          $sql_fields.='case ';
          $sql_fields.='when docu="MDC1" then "&#'.mb_ord('🏠').';" ';
          $sql_fields.='when docu="MDC2" then "&#'.mb_ord('📨').';" ';
          $sql_fields.='when docu="MDC3" then "&#'.mb_ord('🚶').';" ';
          $sql_fields.='when docu="MDC4" then "&#'.mb_ord('📞').';" ';
          $sql_fields.='when docu="MDC5" then "&#'.mb_ord('🪧').';" ';
          $sql_fields.='else "-" end as docu_icon, ';
     }
// $zoptions='1,🏠Casa/Casa ;2,📞Telefónica ;3,📨Carta ';     
}     
     
     
// extras
{
     if(!mb_stristr2('.ttssff.ttrrii.ttssnn.ttsfcc.ttricc.ttsscc',$info))
     {
          $sql_fields.='count(distinct idnum1) as idnum_c,'; 
          $sql_fields.='max(numero*1) as numero_m,';
          
          $sql_fields.='max(concepto) as concepto_m,';
          $sql_fields.='max(length(trim(concepto))) as concepto_l, ';
          $sql_fields.= str_replace('trim_colu','concepto',$trim_colu).' as concepto_t,';
     //      $sql_fields.='max(detalle) as detalle_d,';
          if(mb_stristr2($sql_query,'idnum1=idnum2'))
          {
          $sql_fields.='count(idnum2) as iddet_c,';
          $sql_fields.='max(detalle) as detalle_m,';
          $sql_fields.='max(length(trim(detalle))) as detalle_l, ';
          $sql_fields.= str_replace('trim_colu','detalle',$trim_colu).' as detalle_t,';
          }

          if(mb_stristr2('.vtas.inve',$tabla))
          {
               $sql_fields.='max(trim(lote)) as lote_m,';
          }

          if(mb_stristr2('.inve',$tabla))
          {
               $sql_fields.='max(trim(cambio)) as cambio_m,';
          }

          if(mb_stristr2('.comp.prov.lpro',$tabla))
          {
               $sql_fields.='max(trim(umdd)) as umdd_m,';
          }

          if(mb_stristr2('.cont.comp.prov.lpro.nomi',$tabla))
          {
               $sql_fields.='if(substring(docu,1,2)in("PT"),"",max(trim(csub))) as csub_m,';
          }

          if(mb_stristr2('.comp.prov.inve',$tabla))
          {
               $sql_fields.='max(trim(impor)) as impor_m,';
          }
          
          if(mb_stristr2('.cont',$tabla))
          {
               $sql_fields.='max(trim(item)) as item_m,';
               $sql_fields.='max(trim(lote)) as lote_m,';
//                $sql_fields.='max(trim(impor)) as impor_m,';
          }

     }
     
     $sql_fields.='date_format(fecha,"%Y") as anio,';
     $sql_fields.='date_format(fecha,"%m") as mes,';
     $sql_fields.='date_format(fecha,"%v") as sema, ';
     $sql_fields.='date_format(fecha,"%d") as dia,';
     $sql_fields.='date_format(fecha,"%Y-%m") as ames,';
     $sql_fields.='date_format(fecha,"%x-%v") as asema, ';
     $sql_fields.='upper(date_format(fecha,"%b")) as mes_d,';
     $sql_fields.='upper(date_format(fecha,"%M")) as mes_des,';
     $sql_fields.='upper(date_format(fecha,"%a")) as dia_d,';
     $sql_fields.='upper(date_format(fecha,"%W")) as dia_des,';
     $sql_fields.='ceil(date_format(fecha,"%v")/4) as prdo, ';
     $sql_fields.='date_format(fecha,"%w") as dia_n,';
     $sql_fields.='date_format(fecha,"%j") as doy, ';
     $sql_fields.='date_format(idate,"%T") as ihora, ';
//      $sql_fields.='date_format(fecha,"%d-%b-%Y") as dmy1,';
     $sql_fields.='date_format(fecha,"%d/%m/%Y") as dmy0,';
     $sql_fields.='date_format(fecha,"%d/%b/%y") as dmy1,';
     $sql_fields.='date_format(fecha,"%d/%b/%Y") as dmy2,';
     $sql_fields.='date_format(fecha,"%d/%M/%Y") as dmy3,';
     $sql_fields.='"'.$tabla.'" as tabla1,';
}
   
     
     
$dias_edit=1;
$dias_anul=90;
$dias_elim=90;    
     
if(mb_stristr2('.cont',$tabla))
{
     $dias_edit=date('z');
     $dias_anul=date('z');
     $dias_elim=date('z');     
     
//      if(date('m')==1)
     if(date('m')<=3)
     {
          $dias_edit=$dias_edit+90;
          $dias_anul=$dias_anul+90;
          $dias_elim=$dias_elim+90;
     }
     
}
     
     
     

if(mb_stristr2('.retp',$tabla))
{
     $dias_edit=1;
     $dias_anul=date('d')+date('t',strtotime('-29 days'));
     $dias_elim=date('d');          
}
     
if(mb_stristr2('.comp',$tabla) and date('Ymd')<='20221231' and $_SESSION['empr_codigo']=='sawo')
{
     $dias_edit=360;
     $dias_anul=360;
     $dias_elim=360;     
}

if(mb_stristr2('.vtas',$tabla) and date('Ymd')<='20240930' and $_SESSION['empr_codigo']=='icvs')
{
     $dias_edit=1;
     $dias_anul=360;
     $dias_elim=360;     
}

if(date('Ymd')<='20250315' and $_SESSION['empr_codigo']=='pcs1')
{
     $dias_edit=1;
     $dias_anul=360;
     $dias_elim=360;     
}


if(date('Y-m-d')<='2025-05-31' and $_SESSION['empr_codigo']=='imcp')
{
     $dias_edit=360+150;
     $dias_anul=360+150;
     $dias_elim=360+150;     
}



// $dias_edit=360;     
if(mb_stristr2('.clie.',$tabla))
{
$sql_fields.='case when status not in("F","O","X")  ';
$sql_fields.='and datediff(adddate(fecha,ifnull(plazo,0)),curdate())>=0 then 1 else 0 end as b_f, ';
}
$sql_fields.='case when ifnull(status,"") not in("F","O","X","A","R")  ';

if(mb_stristr2('.vtas.retp.guia.comp.retc',$tabla))
{$sql_fields.='and ifnull(autornum,"")="" ';}
     
$sql_fields.='and datediff(curdate(),idate)<='.$dias_edit.' ';

if(mb_stristr2('.susa',$tabla))
{$sql_fields.='or datediff(curdate(),idate2)<='.$dias_edit.' ';}
     
if(mb_stristr2('.comp',$tabla))
{$sql_fields.='or (docu in("FP15") and datediff(curdate(),idate2)<=90) ';}
     
$sql_fields.='then 1 else 0 end as b_e, ';

// echo '<br>==='.$sql_fields;     
     
// 2 anular // 3 eliminar  // 4 nota credito
if($tabla=='vtas')
{
// $sql_fields.='case ';
// $sql_fields.='when status not in("O","X") ';
// // if(mb_stristr2('.vtas',$tabla))
// // {$sql_fields.='and ifnull(autornum,"")!="" ';}     
// $sql_fields.='then 1 ';
// $sql_fields.='else 0 end as b_n, ';
}
     
     
$sql_fields.='case ';
$sql_fields.='when status not in("F","O","X") ';
$sql_fields.='and datediff(curdate(),fecha)<='.$dias_anul.' ';
if(mb_stristr2('.susa',$tabla))
{$sql_fields.='or datediff(curdate(),idate2)<='.$dias_anul.' ';}
$sql_fields.='then 2 ';


// echo '==='.$dias_elim;     
$sql_fields.='when status="O" ';
if(mb_stristr2('.vtas.retp.guia.comp.retc',$tabla))
{$sql_fields.='and ifnull(autornum,"")="" ';}     
// $sql_fields.='and datediff(curdate(),fecha)<='.$dias_elim.' ';
$sql_fields.='and datediff(curdate(),idate)<='.$dias_elim.' ';
$sql_fields.='then 3  ';
$sql_fields.='else 0 end as b_o, ';
     
$sql_fields.='0 as b_x, ';

//      echo '<br>*/*/*/*'.$sql_fields;
//      echo '<br>*/*/*/*'.$dias_edit;
     
     
     
if(mb_stristr2('.vtas.clie.retp.retc.guia.comp.prov.opro.caja.bcos.rmed',$tabla))
{
     
// 8-fecha ddmmaaaa
// 2-tipo de comprobante 01-fact 04-n/c 07-reten
// 13- ruc emisor
// 1- ambiente 1-pruebas 2-produccion
// 6- num serie (sucursal y ptoemision)
// 9- num secuencial
// 8- aleatorios
// 1- tipo emision 1-normal
// 1- verificador modulo 11
     
     $sql_fields.='case when length(trim(claveacc))=49 then substring(claveacc,24,1) end as ambice, ';
     
     $sql_fields.='case when length(trim(claveacc))=49 then ';
     $sql_fields.='concat(';
     $sql_fields.='substring(claveacc,1,24)," ",';
     $sql_fields.='substring(claveacc,25),"",';
     $sql_fields.='"")end as claveacc1, ';
     
     $sql_fields.='case when length(trim(claveacc))=49 then ';
     $sql_fields.='concat(';
     $sql_fields.='substring(claveacc,1,8),".",';
     $sql_fields.='substring(claveacc,9,2),".",';
     $sql_fields.='substring(claveacc,11,13),".",';
     $sql_fields.='substring(claveacc,24,1)," ",';
     $sql_fields.='substring(claveacc,25,6),".",';
     $sql_fields.='substring(claveacc,31,9),".",';
     $sql_fields.='substring(claveacc,40,8),".",';
     $sql_fields.='substring(claveacc,48,1),"",';
     $sql_fields.='substring(claveacc,49,1),"",';
     $sql_fields.='"")end as claveacc2, ';
     
//      if(mb_stristr2('.vtas.retp.guia.comp.prov.guia.',$tabla))
//      if(mb_stristr2('.vtas.retp.guia.comp.guia.',$tabla))
     if(mb_stristr2('.vtas.retp.guia.comp.guia.retc',$tabla))
     {
     $sql_fields.='concat(';
     $sql_fields.='substring(claveacc,24,1),';
          $sql_fields.='case ';
          $sql_fields.='when autorfec>=fecha then "5" ';
          $sql_fields.='when recepmsj like "%ERROR%" then "4" ';
          $sql_fields.='when autormsj like "%ERROR%" then "4" ';
          $sql_fields.='when recepfec>=fecha then "3" ';
          $sql_fields.='when firmafec>=fecha then "2" ';
          $sql_fields.='when claveacc*1>0 then "1" ';
          $sql_fields.='else "0" end,';
     $sql_fields.='":",';
          $sql_fields.='case ';
          $sql_fields.='when autorfec>=fecha then "AUT" ';
          $sql_fields.='when recepmsj like "%ERROR%" then "ERR" ';
          $sql_fields.='when autormsj like "%ERROR%" then "ERR" ';
          $sql_fields.='when recepfec>=fecha then "SRI" ';
          $sql_fields.='when firmafec>=fecha then "FIR" ';
          $sql_fields.='when claveacc*1>0 then "EMI" ';
          $sql_fields.='else "-" end,';
          $sql_fields.='case ';
          $sql_fields.='when emailfec>=fecha then "@" ';
          $sql_fields.='else "" end';
    $sql_fields.=') as st_ce,';
     }
     
//      echo '<br>*/*/*/*'.$sql_fields;
}
     
if(mb_stristr2('.clie',$tabla))
{
     $sql_fields.='concat(';
     $sql_fields.='substring(claveacc,24,1),';
          $sql_fields.='case ';
//           $sql_fields.='when autorfec>=fecha then "5" ';
//           $sql_fields.='when recepmsj is not null then "4" ';
//           $sql_fields.='when recepmsj like "%ERROR%" then "4" ';
//           $sql_fields.='when recepfec>=fecha then "3" ';
//           $sql_fields.='when firmafec>=fecha then "2" ';
          $sql_fields.='when claveacc*1>0 then "1" ';
          $sql_fields.='else "0" end,';
     $sql_fields.='":",';
          $sql_fields.='case ';
//           $sql_fields.='when autorfec>=fecha then "AUT" ';
//           $sql_fields.='when recepmsj like "%ERROR%" then "ERR" ';
//           $sql_fields.='when recepfec>=fecha then "SRI" ';
//           $sql_fields.='when firmafec>=fecha then "FIR" ';
          $sql_fields.='when claveacc*1>0 then "EMI" ';
          $sql_fields.='else "-" end,';
          $sql_fields.='case ';
          $sql_fields.='when emailfec>=fecha then "@" ';
          $sql_fields.='else "" end';
    $sql_fields.=') as st_ce,';
}
     
     
     
// $sql_fields.='"'.$tabla.'" as tabla,';
$sql_fields.='status as stdocu ';
     
if(!empty($sql_query))
{$sql_fields=str_replace(' from ',','.$sql_fields.' from ',$sql_query);}

     
// quita redondeos     
if($tabla!='cont')
{
// $sql_fields=str_replace(','.$xrnd.')',' ',$sql_fields);
// $sql_fields=str_replace('round(',' ',$sql_fields);
}
     
     
//      echo '<br>*** '.$sql_fields;
//      echo '<br>';
     
return $sql_fields;     
}

function sql_view($tabla, $sql_query='', $nivel=5)
{
     
     $trim_colu='trim_colu';
     $trim_colu='ifnull('.$trim_colu.',"")';
     $trim_colu='trim('.$trim_colu.')';
     $trim_colu='replace('.$trim_colu.',",",", ")';
     $trim_colu='replace('.$trim_colu.',";","; ")';
//      $trim_colu='replace('.$trim_colu.',"/"," /")';
     $trim_colu='replace('.$trim_colu.',"  "," ")';
     $trim_colu='replace('.$trim_colu.',"+"," +")';
     
     if(mb_stristr2('.cont',$tabla))
     {
          $docu_fields='docu_codigo,docu_descri,docu_status,';
          $enti_fields='enti_codigo,enti_descri,enti_razsoc,enti_nomcom,enti_zsub,enti_tpro,enti_status,';
          $item_fields='item_codigo,item_descri,item_status,';

          $sql_contab='';
          for($idx=$nivel; $idx>=1; $idx--)
          {
               $sql_contab='';
               $sql_contab.='select ';
               $sql_contab.='*, ';
               if($empr0['empr_ctasuper']==1)
               {$sql_contab.='case when left(ctas,1) in(1,2,3) then left(ctas,1) else left(ctas,2) end as ctas_cod1,';}
               else
               {$sql_contab.='case when left(ctas,1) in(1,2,3) then left(ctas,1) else left(ctas,1) end as ctas_cod1,';}
               
               $sql_contab.='from cont1 ';
               $sql_contab.='left join cont2 on idnum1=idnum2 ';
               $sql_contab.='left join docu0 on docu=docu_codigo ';
               $sql_contab.='left join ctas0 on ctas=ctas_codigo ';
               $sql_contab.='left join enti0 on enti=enti_codigo ';
               $sql_contab.='left join item0 on item=item_codigo ';
//                $sql_contab=calc_fields('cont',$sql_conta);
               
          }
          
          $sql_contab='';
          $sql_contab.='select ';
          $sql_contab.='cont1.*, cont2.*, ';
          $sql_contab.=$docu_fields;
          $sql_contab.=$enti_fields;
          $sql_contab.=$item_fields;
          if($empr0['empr_ctasuper']==1)
          {
               $sql_contab.='case when left(ctas,1) in(1,2,3) then left(ctas,1) else left(ctas,2) end as ctas_cod1,';
               $sql_contab.='case when left(ctas,1) in(1,2,3) then left(ctas,3) else left(ctas,4) end as ctas_cod2,';
               $sql_contab.='case when left(ctas,1) in(1,2,3) then left(ctas,5) else left(ctas,6) end as ctas_cod3,';
               $sql_contab.='case when left(ctas,1) in(1,2,3) then left(ctas,7) else left(ctas,8) end as ctas_cod4,';
               $sql_contab.='ctas as ctas_cod5,';
          }
          else
          {
               $sql_contab.='left(ctas,1) as ctas_cod1,';
               $sql_contab.='left(ctas,3) as ctas_cod2,';
               $sql_contab.='left(ctas,5) as ctas_cod3,';
               $sql_contab.='left(ctas,7) as ctas_cod4,';
               $sql_contab.='ctas as ctas_cod5,';
          }
          $sql_contab.='status as st ';
          $sql_contab.='from cont1 ';
          $sql_contab.='left join cont2 on idnum1=idnum2 ';
          $sql_contab.='left join docu0 on docu=docu_codigo ';
          $sql_contab.='left join ctas0 on ctas=ctas_codigo ';
          $sql_contab.='left join enti0 on enti=enti_codigo ';
          $sql_contab.='left join item0 on item=item_codigo ';
          
          $sql_contab.='where ifnull(ctas_codigo,"")!="X"';
          
          if($empr0['empr_ctasuper']==1 or 1==1)
          {
//                $sql_contab.='left join ctas0 ctan1 on left(ctas,1)=ctan1.ctas_codigo ';
//                $sql_contab.='left join ctas0 ctan2 on left(ctas,3)=ctan2.ctas_codigo ';
//                $sql_contab.='left join ctas0 ctan3 on left(ctas,5)=ctan3.ctas_codigo ';
//                $sql_contab.='left join ctas0 ctan4 on left(ctas,7)=ctan4.ctas_codigo ';
//                $sql_contab.='left join ctas0 ctan1b on left(ctas,2)=ctan1b.ctas_codigo ';
//                $sql_contab.='left join ctas0 ctan2b on left(ctas,4)=ctan2b.ctas_codigo ';
//                $sql_contab.='left join ctas0 ctan3b on left(ctas,6)=ctan3b.ctas_codigo ';
//                $sql_contab.='left join ctas0 ctan4b on left(ctas,8)=ctan4b.ctas_codigo ';
//                $sql_contab.='left join ctas00 on (ctas=ctas00.ctas_codigo) ';
          }
          else
          {
//                $sql_contab.='left join ctas0 ctan1 on left(ctas,1)=ctan1.ctas_codigo ';
//                $sql_contab.='left join ctas0 ctan2 on left(ctas,3)=ctan2.ctas_codigo ';
//                $sql_contab.='left join ctas0 ctan3 on left(ctas,5)=ctan3.ctas_codigo ';
//                $sql_contab.='left join ctas0 ctan4 on left(ctas,7)=ctan4.ctas_codigo ';
//                $sql_contab.='left join ctas00 on (ctas=ctas00.ctas_codigo) ';
          }
          
          
//           $sql_contab=str_replace('where status not in ("X")','where status not in ("X") and enti not like "999999%" ',$sql_contab);

//           if(!empty($sql_query))
//           {$sql_fields=str_replace(' from ',','.$sql_fields.' from ',$sql_query);}

          $sql_fields=$sql_contab;
          if(!empty($sql_query))
          {
               $sql_fields ='';
               $sql_fields.= substr($sql_query,0,strpos($sql_query,'from')+5);
               $sql_fields.='('.$sql_contab.') as contab ';
               $sql_fields.=substr($sql_query,strpos($sql_query,'where'));
          }
          
     }

     if(mb_stristr2('.cxco.cxpa',$tabla))
     {
          if(mb_stristr2('.cxco',$tabla))
          {
//                $docu_head='"FC","AC","LC","PR","XC"';
               $docu_head='"FC","AC","LC","PR"';
               $caja_copa='"IE","AC","IV","IC"';
               $caja_reve='"EE","XC"';
               $bcos_copa='"DxB","DC"';
               $bcos_reve='"CH","DP","PR"';
               $dxcp_copa='"CC"';
               $rete_docu='"RC"';
          }
          
          if(mb_stristr2('.cxpa',$tabla))
          {
               $docu_head='"FP","AP","LP"';
               
//                $caja_copa='"EE","AP"';
               $caja_copa='"EE","AP","EC"';
               $caja_reve='"IE","XP"';
               $bcos_copa='"CH","DP","EV"';
               $bcos_reve='"DxB","DC"';
               $dxcp_copa='"CP"';
               $rete_docu='"RP"';
          }

          $enti_fields ='enti_codigo,enti_descri,enti_razsoc,enti_nomcom,enti_zsub,enti_tpro,enti_status,';
          $enti_fields.='concat((enti_wsapp),"; ",(enti_telefono),"; ",(enti_celular)) as enti_telefonos,';
          $enti_fields.='concat(enti_email,"; ",enti_emailce) as enti_emails,';

          $sql_cxcopa='';

          // vtas comp
//           if(1==2)
          {
          $sql_cxcopa.='select ';
          $sql_cxcopa.='idnum1,iddet, ';
          $sql_cxcopa.='case when left(docu,2) in ('.$docu_head.') then 1 else 2 end as headet, ';
          $sql_cxcopa.='docu,ptoe,numero,fecha, ';
          $sql_cxcopa.='fecha as fcobro, ';
          $sql_cxcopa.='adddate(fecha,ifnull(plazo,0)) as vence, ';
          $sql_cxcopa.='datediff(curdate(),adddate(fecha,ifnull(plazo,0))) as diasv, ';
               
          if(mb_stristr2('.cxco',$tabla))
          {$sql_cxcopa.='dixco, ';}
          else
          {$sql_cxcopa.='0 as dixco, ';}     
               
                              
          $sql_cxcopa.='sum(case ';
          $sql_cxcopa.='when status in("O","X") then 0 ';
          $sql_cxcopa.='when left(docu,2) in ('.$docu_head.') ';
          $sql_cxcopa.='then (abs(cant*puni)*(1-ifnull(dcto,0)/100) +(abs(cant*puni)*(ifnull(ice,0)/100))) *(1+ifnull(iva,0)/100) +(cant*irbp) +(abs(cant*puni*(1-ifnull(dcto,0)/100))*prop/100) ';
          $sql_cxcopa.='else((abs(cant*puni)*(1-ifnull(dcto,0)/100) +(abs(cant*puni)*(ifnull(ice,0)/100))) *(1+ifnull(iva,0)/100) +(cant*irbp) +(abs(cant*puni*(1-ifnull(dcto,0)/100))*prop/100))*-1 ';
          $sql_cxcopa.='end) as saldo, ';
          $sql_cxcopa.='sum(case ';
          $sql_cxcopa.='when status in("O","X") then 0 ';
          $sql_cxcopa.='when left(docu,2) in ('.$docu_head.') ';
          $sql_cxcopa.='then (abs(cant*puni)*(1-ifnull(dcto,0)/100) +(abs(cant*puni)*(ifnull(ice,0)/100))) *(1+ifnull(iva,0)/100) +(cant*irbp) +(abs(cant*puni*(1-ifnull(dcto,0)/100))*prop/100) ';
          $sql_cxcopa.='else 0 ';
          $sql_cxcopa.='end) as total, ';
          $sql_cxcopa.='case when left(docu,2) in ('.$docu_head.') then docu else todocu end as docum, ';
          $sql_cxcopa.='case when left(docu,2) in ('.$docu_head.') then ptoe else toptoe end as ptoem, ';
          $sql_cxcopa.='case when left(docu,2) in ('.$docu_head.') then numero else tonume end as numer, ';
          $sql_cxcopa.='max(iva) as iva, ';
          $sql_cxcopa.='0 as anticipo, ';
          $sql_cxcopa.='0 as retencion, ';
          $sql_cxcopa.='0 as cair, ';
          $sql_cxcopa.='0 as base, ';
          $sql_cxcopa.='0 as porc_rta, ';
          $sql_cxcopa.='0 as porc_iva, ';
          $sql_cxcopa.='0 as efectivo, ';
          $sql_cxcopa.='0 as cheque, ';
          $sql_cxcopa.='0 as protesto, ';
          $sql_cxcopa.='left(enti,10) as enti, ';
          $sql_cxcopa.=$enti_fields;
          $sql_cxcopa.='docu_descri,zsub_descri,tpro_descri, ';
          $sql_cxcopa.='concepto,detalle, ';
               
          $sql_cxcopa.= str_replace('trim_colu','concepto',$trim_colu).' as concepto_t,';
          $sql_cxcopa.= str_replace('trim_colu','detalle',$trim_colu).' as detalle_t,';
               
          if(mb_stristr2('.cxco',$tabla))
          {
               $sql_cxcopa.='vndd as vndd_hide, ';
               $sql_cxcopa.='enti_vndd as enti_vndd_hide, ';
               $sql_cxcopa.='vndd_descri, ';
               
               $sql_cxcopa.='rcdd as rcdd_hide, ';
               $sql_cxcopa.='enti_rcdd as enti_rcdd_hide, ';
               $sql_cxcopa.='rcdd_descri, ';
               
          }
          else
          {
               $sql_cxcopa.='"" as vndd_hide, "" as enti_vndd_hide, "" as vndd_descri, ';
          }

               
               
               
          $sql_cxcopa.='"" as caja, ';
          $sql_cxcopa.='"" as ctab, ';
          $sql_cxcopa.='status, ';
          $sql_cxcopa.='idate, ';
          $sql_cxcopa.='idnum1 as idfrom, ';
          $sql_cxcopa.='"vtas" as mdfrom, ';
          $sql_cxcopa.='"vtas" as tabla1 ';
          $sql_cxcopa.='from vtas1  ';
          $sql_cxcopa.='left join vtas2 on idnum1=idnum2 ';
          $sql_cxcopa.='left join enti0 on enti=enti_codigo ';
          $sql_cxcopa.='left join docu0 on docu=docu_codigo ';
          $sql_cxcopa.='left join zsub0 on zsub=zsub_codigo ';
          $sql_cxcopa.='left join tpro0 on enti_tpro=tpro_codigo ';
               
          if(mb_stristr2('.cxco',$tabla))
          {$sql_cxcopa.='left join vndd0 on vndd=vndd_codigo ';}
               
          if(mb_stristr2('.cxco',$tabla))
          {$sql_cxcopa.='left join rcdd0 on enti_rcdd=rcdd_codigo ';}
               
          $sql_cxcopa.='where status not in ("X") ';
               // aqui filtrar facturas por cobrar
//           $sql_cxcopa.='and ifnull(credito,0)>0 ';
          $sql_cxcopa.='group by idnum1 ';

          if($tabla=='cxpa')
          {$sql_cxcopa=str_replace('vtas','comp',$sql_cxcopa);}

          }

          // caja
//           if(1==2)
          {
          $sql_cxcopa.='union ';
          $sql_cxcopa.='select ';
          $sql_cxcopa.='idnum1,iddet, ';
          $sql_cxcopa.='2 as headet, ';
          $sql_cxcopa.='docu,ptoe,numero,fecha, ';
          $sql_cxcopa.='fcobro, ';
               
          $sql_cxcopa.='0 as vence, ';
          $sql_cxcopa.='0 as diasv, ';
          $sql_cxcopa.='0 as dixco, ';

          $sql_cxcopa.='sum(case ';
          $sql_cxcopa.='when status in("O","X") then 0 ';
          $sql_cxcopa.='when left(docu,2)in('.$caja_copa.') and ifnull(fcobro,fecha)>fecha and ifnull(fcobro,fecha)>now() then 0 ';
          $sql_cxcopa.='when left(docu,2)in('.$caja_copa.') then abs(cant)*-1 ';
          $sql_cxcopa.='else abs(cant) ';
          $sql_cxcopa.='end) as saldo, ';
          $sql_cxcopa.='0 as total, ';
          $sql_cxcopa.='docum,ptoem,numer, ';
//           $sql_cxcopa.='docum,ptoem,numer*1 as numer, ';

          $sql_cxcopa.='0 as iva, ';
          $sql_cxcopa.='sum(case ';
          $sql_cxcopa.='when status in("O","X") then 0 ';
          $sql_cxcopa.='when docu in("AC11","AP11") then abs(cant)*-1 ';
          $sql_cxcopa.='else 0 ';
          $sql_cxcopa.='end) as anticipo, ';
          $sql_cxcopa.='0 as retencion, ';
          $sql_cxcopa.='0 as cair, ';
          $sql_cxcopa.='0 as base, ';
          $sql_cxcopa.='0 as porc_rta, ';
          $sql_cxcopa.='0 as porc_iva, ';
               
//           $sql_cxcopa.='0 as numret, ';
//           $sql_cxcopa.='0 as idret, ';
               
          $sql_cxcopa.='sum(case ';
          $sql_cxcopa.='when status in("O","X") then 0 ';
          $sql_cxcopa.='when left(docu,2) in("IE") and fcobro>"'.date('Y-m-d').'" ';
          $sql_cxcopa.='then abs(cant) ';
          $sql_cxcopa.='else 0 ';
          $sql_cxcopa.='end) as efectivo, ';
               
          $sql_cxcopa.='sum(case ';
          $sql_cxcopa.='when status in("O","X") then 0 ';
          $sql_cxcopa.='when left(docu,3) in("ICH") and fcobro>"'.date('Y-m-d').'" ';
          $sql_cxcopa.='then abs(cant) ';
          $sql_cxcopa.='else 0 ';
          $sql_cxcopa.='end) as cheque, ';
               
          $sql_cxcopa.='sum(case ';
          $sql_cxcopa.='when status in("O","X") then 0 ';
          $sql_cxcopa.='when left(docum,2) in("PR") ';
          $sql_cxcopa.='then abs(cant)*-1 ';
          $sql_cxcopa.='else 0 ';
          $sql_cxcopa.='end) as protesto, ';
               
          $sql_cxcopa.='left(enti,10) as enti, ';
          $sql_cxcopa.=$enti_fields;
          $sql_cxcopa.='docu_descri,zsub_descri,tpro_descri, ';
          $sql_cxcopa.='concepto,detalle, ';
          $sql_cxcopa.= str_replace('trim_colu','concepto',$trim_colu).' as concepto_t,';
          $sql_cxcopa.= str_replace('trim_colu','detalle',$trim_colu).' as detalle_t,';
               
          $sql_cxcopa.='"" as vndd_hide, "" as enti_vndd_hide, "" as vndd_descri,';
               
          if(mb_stristr2('.cxco',$tabla))
          {
               $sql_cxcopa.='"" as rcdd_hide, ';
               $sql_cxcopa.='enti_rcdd as enti_rcdd_hide, ';
               $sql_cxcopa.='rcdd_descri, ';
          }
               
          $sql_cxcopa.='caja as caja, ';
          $sql_cxcopa.='"" as ctab, ';
          $sql_cxcopa.='status, ';
          $sql_cxcopa.='idate, ';
          $sql_cxcopa.='idnum1 as idfrom, ';
          $sql_cxcopa.='"caja" as mdfrom, ';
          $sql_cxcopa.='"caja" as tabla1 ';
          $sql_cxcopa.='from caja1  ';
          $sql_cxcopa.='left join caja2 on idnum1=idnum2 ';
          $sql_cxcopa.='left join enti0 on enti=enti_codigo ';
          $sql_cxcopa.='left join docu0 on docu=docu_codigo ';
          $sql_cxcopa.='left join zsub0 on enti_zsub=zsub_codigo ';
          $sql_cxcopa.='left join tpro0 on enti_tpro=tpro_codigo ';
          if(mb_stristr2('.cxco',$tabla))
          {$sql_cxcopa.='left join rcdd0 on enti_rcdd=rcdd_codigo ';}
          $sql_cxcopa.='where status not in ("X") ';
          $sql_cxcopa.='and (left(docu,2)in('.$caja_copa.') or left(docu,2)in('.$caja_reve.'))';
          $sql_cxcopa.='and left(docum,2)in('.$docu_head.') ';
     //      $sql_cxcopa.='and left(docum,2)in("FC","AC","PR") '; 
     //      $sql_cxcopa.='and (left(docum,2)in('.$docu_head.') or docu like "PRX%")';
          $sql_cxcopa.='group by idnum1,iddet ';
          }

          // bcos
//           if(1==2)
          {
          $sql_cxcopa.='union ';
          $sql_cxcopa.='select ';
          $sql_cxcopa.='idnum1,iddet, ';
//           $sql_cxcopa.='2 as headet, ';
          $sql_cxcopa.='case when left(docu,2) in ("PR") then 1 else 2 end as headet, ';
          $sql_cxcopa.='docu,ptoe,numero, ';
          $sql_cxcopa.='fecha, ';
          $sql_cxcopa.='fcobro, ';
               
          $sql_cxcopa.='case when left(docu,2) in ("PR") then fecha else "0000-00-00" end as vence, ';
          $sql_cxcopa.='case when left(docu,2) in ("PR") then datediff(curdate(),fecha) else 0 end as diasv, ';
          $sql_cxcopa.='0 as dixco, ';
               
          $sql_cxcopa.='sum(case ';
          $sql_cxcopa.='when status in("O","X") then 0 ';
          $sql_cxcopa.='when left(docu,2)in('.$bcos_copa.') ';
          $sql_cxcopa.='then abs(cant)*-1 ';
          $sql_cxcopa.='else abs(cant) ';
          $sql_cxcopa.='end) as saldo, ';
               
          $sql_cxcopa.='sum(case ';
          $sql_cxcopa.='when status in("O","X") then 0 ';
          $sql_cxcopa.='when left(docu,2)in("PR") ';
          $sql_cxcopa.='then abs(cant) ';
          $sql_cxcopa.='else 0 ';
          $sql_cxcopa.='end) as total, ';
               
          $sql_cxcopa.='case when left(docu,2) in ("PR") then docu else docum end as docum, ';
          $sql_cxcopa.='case when left(docu,2) in ("PR") then ptoe else ptoem end as ptoem, ';
          $sql_cxcopa.='case when left(docu,2) in ("PR") then numero else numer end as numer, ';
               
          $sql_cxcopa.='0 as iva, ';
          $sql_cxcopa.='0 as anticipo, ';
          $sql_cxcopa.='0 as retencion, ';
          $sql_cxcopa.='0 as cair, ';
          $sql_cxcopa.='0 as base, ';
          $sql_cxcopa.='0 as porc_rta, ';
          $sql_cxcopa.='0 as porc_iva, ';
               
//           $sql_cxcopa.='0 as numret, ';
//           $sql_cxcopa.='0 as idret, ';
          $sql_cxcopa.='0 as efectivo, ';
          $sql_cxcopa.='0 as cheque, ';
          $sql_cxcopa.='sum(case ';
          $sql_cxcopa.='when status in("O","X") then 0 ';
          $sql_cxcopa.='when left(docu,2)in("PR") then abs(cant) ';
          $sql_cxcopa.='when left(docum,2)in("PR") then abs(cant)*-1 ';
          $sql_cxcopa.='else 0 ';
          $sql_cxcopa.='end) as protesto, ';
          $sql_cxcopa.='left(enti,10) as enti, ';
          $sql_cxcopa.=$enti_fields;
          $sql_cxcopa.='docu_descri,zsub_descri,tpro_descri, ';
          $sql_cxcopa.='concepto,detalle, ';
          $sql_cxcopa.= str_replace('trim_colu','concepto',$trim_colu).' as concepto_t,';
          $sql_cxcopa.= str_replace('trim_colu','detalle',$trim_colu).' as detalle_t,';
               
          $sql_cxcopa.='"" as vndd_hide, "" as enti_vndd_hide, "" as vndd_descri,';
          if(mb_stristr2('.cxco',$tabla))
          {
               $sql_cxcopa.='"" as rcdd_hide, ';
               $sql_cxcopa.='enti_rcdd as enti_rcdd_hide, ';
               $sql_cxcopa.='rcdd_descri, ';
          }
               
          $sql_cxcopa.='"" as caja, ';
          $sql_cxcopa.='ctab as ctab, ';
          $sql_cxcopa.='status, ';
          $sql_cxcopa.='idate, ';
          $sql_cxcopa.='idnum1 as idfrom, ';
          $sql_cxcopa.='"bcos" as mdfrom, ';
          $sql_cxcopa.='"bcos" as tabla1 ';
          $sql_cxcopa.='from bcos1  ';
          $sql_cxcopa.='left join bcos2 on idnum1=idnum2 ';
          $sql_cxcopa.='left join enti0 on enti=enti_codigo ';
          $sql_cxcopa.='left join docu0 on docu=docu_codigo ';
          $sql_cxcopa.='left join zsub0 on enti_zsub=zsub_codigo ';
          $sql_cxcopa.='left join tpro0 on enti_tpro=tpro_codigo ';
          if(mb_stristr2('.cxco',$tabla))
          {$sql_cxcopa.='left join rcdd0 on enti_rcdd=rcdd_codigo ';}
          $sql_cxcopa.='where status not in ("X") ';
     //      if($tabla=='cxpa')
          {
          $sql_cxcopa.='and (left(docu,2)in('.$bcos_copa.') or left(docu,2)in('.$bcos_reve.'))';
          }
     //      $sql_cxcopa.='and left(docum,2)in('.$docu_head.') ';
          $sql_cxcopa.='and (left(docum,2)in('.$docu_head.') or docu like "PR%")';
          $sql_cxcopa.='group by idnum1,iddet ';
          }

          // retc retp
//           if(1==2)
          {
          $sql_cxcopa.='union ';
          $sql_cxcopa.='select ';
          $sql_cxcopa.='idnum1,iddet, ';
          $sql_cxcopa.='2 as headet, ';
          $sql_cxcopa.='docu,ptoe,numero,fecha, ';
          $sql_cxcopa.='fecha as fcobro, ';
          $sql_cxcopa.='0 as vence, ';
          $sql_cxcopa.='0 as diasv, ';
          $sql_cxcopa.='0 as dixco, ';
               
//           $sql_cxcopa.='sum(case ';
          $sql_cxcopa.='(case ';
          $sql_cxcopa.='when status in("O","X") then 0 ';
          $sql_cxcopa.='when left(docu,2)in('.$rete_docu.') ';
          $sql_cxcopa.='then abs(cant*puni)/100*(-1) ';
          $sql_cxcopa.='else abs(cant*puni)/100 ';
          $sql_cxcopa.='end) as saldo, ';
               
          $sql_cxcopa.='0 as total, ';
          $sql_cxcopa.='docum,ptoem,numer, ';
          $sql_cxcopa.='0 as iva, ';
          $sql_cxcopa.='0 as anticipo, ';
               
//           $sql_cxcopa.='sum(case ';
          $sql_cxcopa.='(case ';
          $sql_cxcopa.='when status in("O","X") then 0 ';
          $sql_cxcopa.='when left(docu,2) in("RC","RP") ';
          $sql_cxcopa.='then abs(cant*puni)/100 ';
          $sql_cxcopa.='else 0 ';
          $sql_cxcopa.='end) as retencion, ';
               
          $sql_cxcopa.='(cair) as cair, ';
          $sql_cxcopa.='0 as base, ';
//           $sql_cxcopa.='0 as porc, ';

          $sql_cxcopa.='(case ';
          $sql_cxcopa.='when status in("O","X") then 0 ';
          $sql_cxcopa.='when cair*1>=300 then puni ';
          $sql_cxcopa.='else 0 ';
          $sql_cxcopa.='end)';
          $sql_cxcopa.='as porc_rta, ';
               
          $sql_cxcopa.='(case ';
          $sql_cxcopa.='when status in("O","X") then 0 ';
          $sql_cxcopa.='when cair*1<300 then puni ';
          $sql_cxcopa.='else 0 ';
          $sql_cxcopa.='end)';
          $sql_cxcopa.='as porc_iva, ';
               
               
          $sql_cxcopa.='0 as efectivo, ';
          $sql_cxcopa.='0 as cheque, ';
          $sql_cxcopa.='0 as protesto, ';
          $sql_cxcopa.='left(enti,10) as enti, ';
          $sql_cxcopa.=$enti_fields;
          $sql_cxcopa.='docu_descri,zsub_descri,tpro_descri, ';
          $sql_cxcopa.='concepto,detalle, ';
          $sql_cxcopa.= str_replace('trim_colu','concepto',$trim_colu).' as concepto_t,';
          $sql_cxcopa.= str_replace('trim_colu','detalle',$trim_colu).' as detalle_t,';

          $sql_cxcopa.='"" as vndd_hide, "" as enti_vndd_hide, "" as vndd_descri,';
          if(mb_stristr2('.cxco',$tabla))
          {
               $sql_cxcopa.='"" as rcdd_hide, ';
               $sql_cxcopa.='enti_rcdd as enti_rcdd_hide, ';
               $sql_cxcopa.='rcdd_descri, ';
          }
               
          $sql_cxcopa.='"" as caja, ';
          $sql_cxcopa.='"" as ctab, ';
          $sql_cxcopa.='status, ';
          $sql_cxcopa.='idate, ';
          $sql_cxcopa.='idnum1 as idfrom, ';
          $sql_cxcopa.='"retc" as mdfrom, ';
          $sql_cxcopa.='"retc" as tabla1 ';
          $sql_cxcopa.='from retc1  ';
          $sql_cxcopa.='left join retc2 on idnum1=idnum2 ';
          $sql_cxcopa.='left join enti0 on enti=enti_codigo ';
          $sql_cxcopa.='left join docu0 on docu=docu_codigo ';
          $sql_cxcopa.='left join zsub0 on enti_zsub=zsub_codigo ';
          $sql_cxcopa.='left join tpro0 on enti_tpro=tpro_codigo ';
          if(mb_stristr2('.cxco',$tabla))
          {$sql_cxcopa.='left join rcdd0 on enti_rcdd=rcdd_codigo ';}
          $sql_cxcopa.='where status not in ("X") ';
          $sql_cxcopa.='and numer*1>0 ';
          $sql_cxcopa.='and left(docu,2)in('.$rete_docu.') ';
//           $sql_cxcopa.='and cair not like "344%" '; ??????
          $sql_cxcopa.='group by idnum1,iddet ';

          if($tabla=='cxpa')
          {$sql_cxcopa=str_replace('retc','retp',$sql_cxcopa);}
          }

          // dxco dxpa
//           if(1==2)
          {
          $sql_cxcopa.='union ';
          $sql_cxcopa.='select ';
          $sql_cxcopa.='idnum1,iddet, ';
          $sql_cxcopa.='case when left(docu,2)in('.$docu_head.') or docu like "%00" then 1 else 2 end as headet, ';
          $sql_cxcopa.='docu,ptoe,numero, ';
          $sql_cxcopa.='fecha, ';
          $sql_cxcopa.='fecha as fcobro, ';
               
          $sql_cxcopa.='0 as vence, ';
          $sql_cxcopa.='0 as diasv, ';
          $sql_cxcopa.='0 as dixco, ';
               
          $sql_cxcopa.='sum(case ';
          $sql_cxcopa.='when status in("O","X") then 0 ';
          $sql_cxcopa.='when left(docu,2)in('.$dxcp_copa.') ';
          $sql_cxcopa.='then abs(cant*puni)*-1 ';
          $sql_cxcopa.='else abs(cant*puni) ';
          $sql_cxcopa.='end) as saldo, ';
               
          $sql_cxcopa.='sum(case ';
          $sql_cxcopa.='when status in("O","X") then 0 ';
//           $sql_cxcopa.='when left(docu,2)in('.$docu_head.') ';
          $sql_cxcopa.='when left(docu,2)in('.$docu_head.') or docu like "%00" ';
          $sql_cxcopa.='then abs(cant*puni) ';
          $sql_cxcopa.='else 0 ';
          $sql_cxcopa.='end) as total, ';
               
          $sql_cxcopa.='case when left(docu,2)in('.$docu_head.') then docu else docum end as docum, ';
          $sql_cxcopa.='case when left(docu,2)in('.$docu_head.') then ptoe else ptoem end as ptoem, ';
          $sql_cxcopa.='case when left(docu,2)in('.$docu_head.') then numero else numer end as numer, ';
//           $sql_cxcopa.='case when left(docu,2)in('.$docu_head.') then numero*1 else numer*1 end as numer, ';
               
          $sql_cxcopa.='0 as iva, ';
          $sql_cxcopa.='sum(case ';
          $sql_cxcopa.='when status in("O","X") then 0 ';
          $sql_cxcopa.='when docu in("AC10","AP10") ';
          $sql_cxcopa.='then abs(cant*puni) ';
          $sql_cxcopa.='else 0 ';
          $sql_cxcopa.='end) as anticipo, ';
               
          $sql_cxcopa.='0 as retencion, ';
          $sql_cxcopa.='0 as cair, ';
          $sql_cxcopa.='0 as base, ';
          $sql_cxcopa.='0 as porc_rta, ';
          $sql_cxcopa.='0 as porc_iva, ';
               
//           $sql_cxcopa.='0 as numret, ';
//           $sql_cxcopa.='0 as idret, ';
          $sql_cxcopa.='0 as efectivo, ';
          $sql_cxcopa.='0 as cheque, ';
          $sql_cxcopa.='0 as protesto, ';
               
          $sql_cxcopa.='left(enti,10) as enti, ';
          $sql_cxcopa.=$enti_fields;
          $sql_cxcopa.='docu_descri,zsub_descri,tpro_descri, ';
          $sql_cxcopa.='concepto,detalle, ';
          $sql_cxcopa.= str_replace('trim_colu','concepto',$trim_colu).' as concepto_t,';
          $sql_cxcopa.= str_replace('trim_colu','detalle',$trim_colu).' as detalle_t,';
               
          $sql_cxcopa.='"" as vndd_hide, "" as enti_vndd_hide, "" as vndd_descri,';
          if(mb_stristr2('.cxco',$tabla))
          {
               $sql_cxcopa.='"" as rcdd_hide, ';
               $sql_cxcopa.='enti_rcdd as enti_rcdd_hide, ';
               $sql_cxcopa.='rcdd_descri, ';
          }
               
               
          $sql_cxcopa.='"" as caja, ';
          $sql_cxcopa.='"" as ctab, ';
          $sql_cxcopa.='status, ';
          $sql_cxcopa.='idate, ';
          $sql_cxcopa.='idnum1 as idfrom, ';
          $sql_cxcopa.='"dxco" as mdfrom, ';
          $sql_cxcopa.='"dxco" as tabla1 ';
          $sql_cxcopa.='from dxco1  ';
          $sql_cxcopa.='left join dxco2 on idnum1=idnum2 ';
          $sql_cxcopa.='left join enti0 on enti=enti_codigo ';
          $sql_cxcopa.='left join docu0 on docu=docu_codigo ';
          $sql_cxcopa.='left join zsub0 on enti_zsub=zsub_codigo ';
          $sql_cxcopa.='left join tpro0 on enti_tpro=tpro_codigo ';
          if(mb_stristr2('.cxco',$tabla))
          {$sql_cxcopa.='left join rcdd0 on enti_rcdd=rcdd_codigo ';}
          $sql_cxcopa.='where status not in ("X") ';
          $sql_cxcopa.='group by idnum1,iddet ';
//           $sql_cxcopa.='order by fecha desc ';

          if($tabla=='cxpa'){$sql_cxcopa=str_replace('dxco','dxpa',$sql_cxcopa);}

          }     

          
          // no consumidor final
//           $sql_cxcopa=str_replace('where status not in ("X")','where status not in ("X") and enti not like "999999%" ',$sql_cxcopa);

//           if(!empty($sql_query))
//           {$sql_fields=str_replace(' from ',','.$sql_fields.' from ',$sql_query);}

          $sql_fields=$sql_cxcopa;
          if(!empty($sql_query))
          {
               $sql_fields ='';
               $sql_fields.= substr($sql_query,0,strpos($sql_query,'from')+5);
               $sql_fields.='('.$sql_cxcopa.') as cxcopa ';
               $sql_fields.=substr($sql_query,strpos($sql_query,'where'));
          }
     }     
 
//    echo $sql_fields;  
return $sql_fields;     
}



function data_maes($maes,$codigo,$link_data='',$exact=0)
{


     // retorna array asociaivo
   if(empty($link_data)){$link_data=$_SESSION['link_data'];}
   
   $maes0=$maes.'0';
   $codigo=trim($codigo);
   $data_maes=array();

//      $exaxt
//      0 : cualquie parte
//      1 : igual
//      2 : empezar igual
//      3 : terminar igual
          
     
   if($maes=='ctas' and mb_stristr2('123456789',substr($codigo,0,1))) {$exact=2;}  
     
   if(empty($codigo) and $maes!='empr')
   {
//         if(msql_num_rows($res_data)==0)
        {
             $sql_describe='describe '.$maes.'0';
             $res_describe=msql_query($sql_describe,$link_data);
             
             if(msql_errno($link_data)==1146){maes_view($maes);}

             while($aso_describe=msql_fetch_assoc($res_describe))
             {$data_maes[$aso_describe['Field']]=''; }
        }
        
        return $data_maes;
   }
     
   $sql_data ='select * ';
     
   if($maes=='ctas')
   {
     $empr0=data_maes('empr',$empr);     
     $sql_data.=',';
     if($empr0['empr_ctasuper']==0)
     {
          $sql_data.='case when left(ctas_codigo,1) in(1,2,3) then left(ctas_codigo,1) else left(ctas_codigo,1) end as ctas_codi1,';
          $sql_data.='case when left(ctas_codigo,1) in(1,2,3) then left(ctas_codigo,3) else left(ctas_codigo,3) end as ctas_codi2,';
          $sql_data.='case when left(ctas_codigo,1) in(1,2,3) then left(ctas_codigo,5) else left(ctas_codigo,5) end as ctas_codi3,';
          $sql_data.='case when left(ctas_codigo,1) in(1,2,3) then left(ctas_codigo,7) else left(ctas_codigo,7) end as ctas_codi4,';
          $sql_data.='trim(ctas_codigo) as ctas_codi5,';
     }
        
     if($empr0['empr_ctasuper']==1)
     {
          $sql_data.='case when left(ctas_codigo,1) in(1,2,3) then left(ctas_codigo,1) else left(ctas_codigo,2) end as ctas_codi1,';
          $sql_data.='case when left(ctas_codigo,1) in(1,2,3) then left(ctas_codigo,3) else left(ctas_codigo,4) end as ctas_codi2,';
          $sql_data.='case when left(ctas_codigo,1) in(1,2,3) then left(ctas_codigo,5) else left(ctas_codigo,6) end as ctas_codi3,';
          $sql_data.='case when left(ctas_codigo,1) in(1,2,3) then left(ctas_codigo,7) else left(ctas_codigo,8) end as ctas_codi4,';
          $sql_data.='trim(ctas_codigo) as ctas_codi5,';
     }
        
   }
     
   $sql_data.='from '.$maes0.' ';
   if($maes=='empr')
   {
      $join='enti';
      $sql_data.=' left join '.$join.'0 on '.$maes.'_ruc='.$join.'_codigo ';
   }
     
   if($maes=='user')
   {
//       $join='enti';
//       $sql_data.=' left join '.$join.'0 on '.$maes.'_'.$join.'='.$join.'_codigo ';
   }
     
   if($maes=='docu')
   {
//       $join='tice';
      $join='tcel';
      $sql_data.=' left join '.$join.'0 on '.$maes.'_'.$join.'='.$join.'_codigo ';
   }
     
   if($maes=='enti')
   {
      $join='zsub';
      $sql_data.=' left join '.$join.'0 on '.$maes.'_'.$join.'='.$join.'_codigo ';
      $join='tpro';
      $sql_data.=' left join '.$join.'0 on '.$maes.'_'.$join.'='.$join.'_codigo ';
   }

   if($maes=='item')
   {
      $join='lsub';
      $sql_data.=' left join '.$join.'0 on '.$maes.'_'.$join.'='.$join.'_codigo ';
      $join='mrca';
      $sql_data.=' left join '.$join.'0 on '.$maes.'_'.$join.'='.$join.'_codigo ';
      $join='clor';
      $sql_data.=' left join '.$join.'0 on '.$maes.'_'.$join.'='.$join.'_codigo ';
      $join='tlla';
      $sql_data.=' left join '.$join.'0 on '.$maes.'_'.$join.'='.$join.'_codigo ';
      $join='cair';
      $sql_data.=' left join '.$join.'0 on '.$maes.'_'.$join.'='.$join.'_codigo ';
   }

   if(!empty($codigo) and $maes!='empr')
   {$sql_data.='where '.$maes.'_codigo like "'.$codigo.'" ';}
     
   $sql_data.='order by '.$maes.'_codigo ';
   $sql_data.='limit 1 ';

   $sql_data=calc_maes($maes,$sql_data);
   $res_data=msql_query($sql_data,$link_data);

     if($exact!=1)
     {
          if(empty($exact) or $exact==3)
          if(msql_num_rows($res_data)==0)
          {
               $sql_data=substr($sql_data,0,strpos($sql_data,' where '));
               $sql_data.=' ';
               $sql_data.='where '.$maes.'_codigo like "%'.$codigo.'" ';
               $sql_data.='order by '.$maes.'_codigo desc ';
               $sql_data.='limit 1 ';
               $res_data=msql_query($sql_data,$link_data);
          }

          if(empty($exact) or $exact==2)
          if(msql_num_rows($res_data)==0)
          {
               $sql_data=substr($sql_data,0,strpos($sql_data,' where '));
               $sql_data.=' ';
               $sql_data.='where '.$maes.'_codigo like "'.$codigo.'%" ';
               if(mb_stristr2('.enti.item.ctXas',$maes))               
               {$sql_data.='and length(trim('.$maes.'_codigo))>=8 ';}
               $sql_data.='order by '.$maes.'_codigo  ';
               if(mb_stristr2('.enti',$maes)){$sql_data.='desc ';}
               $sql_data.='limit 1 ';
               $res_data=msql_query($sql_data,$link_data);
               
          }

          if(msql_num_rows($res_data)==0)
          {
               $sql_data=substr($sql_data,0,strpos($sql_data,' where '));
               $sql_data.=' ';
               $sql_data.='where '.$maes.'_codigo like "'.substr($codigo,0,10).'%" ';
               if(mb_stristr2('.enti.item.ctXas',$maes))               
               {$sql_data.='and length(trim('.$maes.'_codigo))>=8 ';}
               $sql_data.='order by '.$maes.'_codigo  ';
               if(mb_stristr2('.enti',$maes)){$sql_data.='desc ';}
               $sql_data.='limit 1 ';
               $res_data=msql_query($sql_data,$link_data);
          }

          if(mb_stristr2('.enti.clie.prov.vndd.rcdd.codt',$maes))               
          if(msql_num_rows($res_data)==0)
          {
               $sql_data=substr($sql_data,0,strpos($sql_data,' where '));
               $sql_data.=' ';
               $sql_data.='where '.$maes.'_pasaporte like "%'.$codigo.'%" ';
               {$sql_data.='and length(trim('.$maes.'_codigo))>=8 ';}
               $sql_data.='order by '.$maes.'_codigo  ';
               {$sql_data.='desc ';}
               $sql_data.='limit 1 ';
               $res_data=msql_query($sql_data,$link_data);
          }

          if(mb_stristr2('.enti.clie.prov.vndd.rcdd.codt',$maes))               
          if(msql_num_rows($res_data)==0)
          {
               $sql_data=substr($sql_data,0,strpos($sql_data,' where '));
               $sql_data.=' ';
               $sql_data.='where '.$maes.'_idexterior like "%'.$codigo.'%" ';
               {$sql_data.='and length(trim('.$maes.'_codigo))>=8 ';}
               $sql_data.='order by '.$maes.'_codigo  ';
               {$sql_data.='desc ';}
               $sql_data.='limit 1 ';
               $res_data=msql_query($sql_data,$link_data);
          }

          if(mb_stristr2('.item',$maes))               
          if(msql_num_rows($res_data)==0)
          {
               $sql_data=substr($sql_data,0,strpos($sql_data,' where '));
               $sql_data.=' ';
               $sql_data.='where '.$maes.'_ean like "%'.$codigo.'%" ';
               {$sql_data.='and length(trim('.$maes.'_codigo))>=8 ';}
               $sql_data.='order by '.$maes.'_codigo  ';
               {$sql_data.='desc ';}
               $sql_data.='limit 1 ';
               $res_data=msql_query($sql_data,$link_data);
          }

          if(mb_stristr2('.item',$maes))               
          if(msql_num_rows($res_data)==0)
          {
               $sql_data=substr($sql_data,0,strpos($sql_data,' where '));
               $sql_data.=' ';
               $sql_data.='where '.$maes.'_numpart like "%'.$codigo.'%" ';
               {$sql_data.='and length(trim('.$maes.'_codigo))>=8 ';}
               $sql_data.='order by '.$maes.'_codigo  ';
               {$sql_data.='desc ';}
               $sql_data.='limit 1 ';
               $res_data=msql_query($sql_data,$link_data);
          }

          if(mb_stristr2('.item',$maes))               
          if(msql_num_rows($res_data)==0)
          {
               $sql_data=substr($sql_data,0,strpos($sql_data,' where '));
               $sql_data.=' ';
               $sql_data.='where '.$maes.'_codigov like "%'.$codigo.'%" ';
               {$sql_data.='and length(trim('.$maes.'_codigo))>=8 ';}
               $sql_data.='order by '.$maes.'_codigo  ';
               {$sql_data.='desc ';}
               $sql_data.='limit 1 ';
               $res_data=msql_query($sql_data,$link_data);
          }

          
          
//           if($exact!=1)
          {
               
//                if(empty($exact) or $exact==2)
               if(msql_num_rows($res_data)==0)
               {
                    $sql_data=substr($sql_data,0,strpos($sql_data,' where '));
                    $sql_data.=' ';
                    $sql_data.='where '.$maes.'_descri like "'.$codigo.'%" ';
                    $sql_data.='order by '.$maes.'_descri,'.$maes.'_codigo ';
                    $sql_data.='limit 1 ';
                    $res_data=msql_query($sql_data,$link_data);
               }

//                if(empty($exact) or $exact==2)
               if(msql_num_rows($res_data)==0)
               {
                    $sql_data=substr($sql_data,0,strpos($sql_data,' where '));
                    $sql_data.=' ';
                    $sql_data.='where '.$maes.'_descri like "%'.$codigo.'%" ';
                    $sql_data.='order by '.$maes.'_descri,'.$maes.'_codigo ';
                    $sql_data.='limit 1 ';
                    $res_data=msql_query($sql_data,$link_data);
               }

               if(empty($exact))
               if(msql_num_rows($res_data)==0)
               {
                    $sql_data=substr($sql_data,0,strpos($sql_data,' where '));
                    $sql_data.=' ';
                    $sql_data.='where '.$maes.'_codigo like "%'.$codigo.'%" ';
                    $sql_data.='order by '.$maes.'_descri,'.$maes.'_codigo ';
                    $sql_data.='limit 1 ';
                    $res_data=msql_query($sql_data,$link_data);
               }
               
          }          

     }

     
     if($maes=='enti')
     {
          
//      echo '<br>*/*/*'.$maes;
//      echo '///*/*/*/* '.$sql_data;
//      echo '///*/*/*/* '.msql_num_rows($res_data);
//      echo '<br>*/*/*'.msql_num_rows($res_data);
     }
     
   if(msql_num_rows($res_data)==0)
   {
        $sql_describe='describe '.$maes.'0';
        $res_describe=msql_query($sql_describe,$link_data);
        while($aso_describe=msql_fetch_assoc($res_describe))
        {$data_maes[$aso_describe['Field']]=''; }
   }
     
     
   if(msql_num_rows($res_data)!=0)
   {
      $cfield=msql_num_fields($res_data);
      $data_maes=msql_fetch_assoc($res_data);
      if($maes=='empr')
      {$data_maes[$maes.'_codigo']=strtolower($data_maes[$maes.'_codigo']);}
        
      if($maes=='user')
      {$data_maes[$maes.'_tipo']=strtolower($data_maes[$maes.'_tipo']);}
       
      if(isset($data_maes[$maes.'_tabla'])) 
      if(strtolower($data_maes[$maes.'_codigo'])==strtolower($data_maes[$maes.'_tabla'])) 
      {
           $sql_used='update '.$maes0.' set '.$maes.'_lused=now() where '.$maes.'_codigo = "'.$codigo.'"';
           $res_used=msql_query($sql_used,$link_data);
      }
      $data_maes[$maes.'_codigo']=trim($data_maes[$maes.'_codigo']);  
   }
     
     if($data_maes[$maes.'_status']=='I'){$data_maes[$maes.'_descri'].=' * INACTIVO *';}
     
   return $data_maes;
}

function data_modu($modu,$docu,$idnum,$iddet='',$link_data='')
{     
//    if(empty($link_data)){$link_data=$_SESSION['link_data'];}
//    $sql_data ='select * ';
//    $sql_data.='from '.$modu;
//    $sql_data.='from '.$modu;
}

function data_clac($clave_acceso)
{
     if(empty($link_data)){$link_data=$_SESSION['link_data'];}
     $codDoc=substr($clave_acceso,8,2); 	
     //01 04 05
     $tabla='vtas';
     // 	if($codDoc=='05'){$tabla='vtas';}
     if($codDoc=='03'){$tabla='comp';}
     if($codDoc=='06'){$tabla='guia';}
     if($codDoc=='07'){$tabla='retp';}

     if($codDoc=='81'){$tabla='caja';}
     if($codDoc=='82'){$tabla='bcos';}

     if($codDoc=='91'){$tabla='clie';}
     if($codDoc=='92'){$tabla='clie';}
     if($codDoc=='95'){$tabla='prov';}
     if($codDoc=='99'){$tabla='role';}
     
//      if(empty())
//      $sql_docu='select * from docu0 where ifnull(docu_status,"") not in ("X") and docu_tice="'.$codDoc.'"';
//      $res_docu=msql_query($sql_docu,$link_data);
//      $tabla=msql_result($res_docu,0,'docu_tabla');

     $sql_docu='select * from '.$tabla.'1 ';
     $sql_docu.='where ifnull(status,"")not in ("O","X") ';
     $sql_docu.='and claveacc="'.$clave_acceso.'" ';
     $sql_docu.='order by idnum1 desc ';
     $res_docu=msql_query($sql_docu,$link_data);
//      if(msql_num_rows($res_docu)==0)
     {
//           $tabla='comp';
//           if($codDoc=='07'){$tabla='retc';}
//           $sql_docu='select * from '.$tabla.'1 ';
//           $sql_docu.='where ifnull(status,"")not in ("O","X") ';
//           $sql_docu.='and claveacc="'.$clave_acceso.'" ';
//           $sql_docu.='order by idnum1 desc ';
//           $res_docu=msql_query($sql_docu,$link_data);
     }
     
//      echo '<br>======================*******'.$sql_docu;
     
//      echo '<br>======================*******'.msql_num_rows($res_docu);
     
     if(msql_num_rows($res_docu)==0){return;}
     
//      echo '///////////////////////////========================*/*/*/*'.$docu.$idnum;
     
     $docu=msql_result($res_docu,0,'docu');
     $idnum=msql_result($res_docu,0,'idnum1');
     $data_clac=data_docu($docu,$idnum);
     return $data_clac;

}

function data_docu($docu,$idnum,$iddet='',$link_data='')
{
   if(empty($link_data)){$link_data=$_SESSION['link_data'];}
   
   if(empty($docu)){return;}
   if(empty($idnum)){return;}
     
   $docu0=data_maes('docu',$docu);
   $tabla=$docu0['docu_tabla'];
   $tabla1=$tabla.'1';
   $tabla2=$tabla.'2';
   
   $sql_data ='select * ';
   $sql_data.=' from '.$tabla1;
   
     //2025-03-12
   $sql_data.=' left join '.$tabla2.' on idnum1=idnum2 ';
   
   if($maes=='enti')
   {
      $join='zsub';
      $sql_data.=' left join '.$join.'0 on '.$maes.'_'.$join.'='.$join.'_codigo ';
   }
   
   if($maes=='item')
   {
      $join='lsub';
      $sql_data.=' left join '.$join.'0 on '.$maes.'_'.$join.'='.$join.'_codigo ';
      $join='mrca';
      $sql_data.=' left join '.$join.'0 on '.$maes.'_'.$join.'='.$join.'_codigo ';
      $join='clor';
      $sql_data.=' left join '.$join.'0 on '.$maes.'_'.$join.'='.$join.'_codigo ';
      $join='tlla';
      $sql_data.=' left join '.$join.'0 on '.$maes.'_'.$join.'='.$join.'_codigo ';
      $join='cair';
      $sql_data.=' left join '.$join.'0 on '.$maes.'_'.$join.'='.$join.'_codigo ';
   }
     
   $sql_data.=' where idnum1="'.$idnum.'" ';
   if(!empty($iddet)){$sql_data.='and iddet="'.$iddet.'" ';}
   $res_data=msql_query($sql_data,$link_data);
   
   if(msql_num_rows($res_data)!=0)
   {
      $cfield=msql_num_fields($res_data);
      $data_docu=msql_fetch_assoc($res_data);
   }
   
   return $data_docu;
}


function calc_anticipo($docu,$enti='',$fecha='')
{
     if(empty($link_data)){$link_data=$_SESSION['link_data'];}
     
     $anticipo=array();

     $docu=substr(strtoupper($docu),0,2);
     if($docu=='AC'){$modu='cxco'; $tabla='dxco';}  
     if($docu=='AP'){$modu='cxpa'; $tabla='dxpa';}  

     $tabla1=$tabla.'1';  
     $tabla2=$tabla.'2';  

     $sql_anti0='select *, sum(cant*puni) as total ';
     $sql_anti0.='from '.$tabla1.' ';
     $sql_anti0.='left join '.$tabla2.' on idnum1=idnum2 ';
     $sql_anti0.='where ifnull(status,"")not in ("O","X") ';
     $sql_anti0.='and docu like "'.$docu.'%"';
     if(!empty($enti))
     {$sql_anti0.='and enti like "'.substr($enti,0,10).'%"';}     
     if(!empty($fecha))
     {$sql_anti0.='and fecha <= "'.$fecha.'"';}     

     $res_anti0=msql_query($sql_anti0,$link_data);
     $anti0=msql_result($res_anti0,0,'total');
     
     
     $tabla1='caja1';
     $tabla2='caja2';
     $sql_anti1='select *, sum(cant*puni) as total ';
     $sql_anti1.='from '.$tabla1.' ';
     $sql_anti1.='left join '.$tabla2.' on idnum1=idnum2 ';
     $sql_anti1.='where ifnull(status,"")not in ("O","X") ';
     $sql_anti1.='and docu like "'.$docu.'%"';
     if(!empty($enti))
     {$sql_anti1.='and enti like "'.substr($enti,0,10).'%"';}     
     if(!empty($fecha))
     {$sql_anti1.='and fecha <= "'.$fecha.'"';} 
     

     $res_anti1=msql_query($sql_anti1,$link_data);
     $anti1=msql_result($res_anti1,0,'total');
     
     
     $anticipo=$anti0-$anti1;
     return $anticipo;
     
}

?>