#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Information Software SA
# Date In: 2006-12-10
# Updates: 2006-12-12,2007-06-27;2010-11-21;2022-11-27
# Author:Rodolfo Machon
# File Name: gmswsp.pl
# Copyright - All Rights Reserved
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#PERL MODULES
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use XML::Simple;
use Data::Dumper;
use Date::Calc qw(Add_Delta_YMD);
use Date::Calc qw(Delta_Days);
use Date::Calc qw(Delta_YMD);
use Date::Calc qw(Days_in_Month);
use Time::Local;
binmode STDOUT, ":encoding(utf8)";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#HTML printing activation
print "Content-type: text/html; charset=utf-8\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$cgiparms 	      = new CGI;
$clase      	= $cgiparms->param('clase');
$dr         	= $cgiparms->param('dr');
$id         	= $cgiparms->param('id');
$dt_vencimiento	= $cgiparms->param('dt_vencimiento');
$dt_futuro	      = $cgiparms->param('dt_futuro');
$dt_corte_hasta	= $cgiparms->param('dt_corte');
$dt_corte_desde	= $cgiparms->param('dt_corte1');
$i2_dias_mora     = $cgiparms->param('i2_dias_mora');
$d82_multa_minima = $cgiparms->param('d82_multa_minima');
$i2_digitos       = $cgiparms->param('i2_digitos');
$c8_dia_cargo     = $cgiparms->param('c8_dia_cargo');
$c11_sector_id    = $cgiparms->param('sector');
$dt_vencimiento   =~ s/\-//g;
$dt_futuro 	      =~ s/\-//g;
$dt_corte_hasta   =~ s/\-//g;
$dt_corte_desde   =~ s/\-//g;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($clase eq ""){print "Your are not passing CLASE parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
get_app_data();
get_component_schema($clase);
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# OPEN DATABASE CONNECTIONS - ODBCs
 open_conection();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#MAIN PROGRAM
$generales = get_generales();
print "$generales <br>";
process_fork();

construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_generales {
      my $table		= "sdm_parametros";
      my $select_fields	= "i4_parametro_id,i2_dias_mora,d82_multa_minima,i2_digitos,dt_parametro,c8_dia_cargo";
	my $sql				= "SELECT $select_fields FROM $table ORDER BY 5 DESC";
	#print "$sql <br>";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_renerales');
	if (my @row = $sth->fetchrow_array) {
            if($i2_dias_mora eq ""){$i2_dias_mora = $row[1];}
            if($d82_multa_minima eq ""){$d82_multa_minima = $row[2];}
            if($i2_digitos eq ""){$i2_digitos = $row[3];}
            if($c8_dia_cargo eq ""){$c8_dia_cargo = "\U$row[5]";}
      }else{
            if($i2_dias_mora eq ""){$i2_dias_mora = "60";}
            if($d82_multa_minima eq ""){$d82_multa_minima = "2.86";}
            if($i2_digitos eq ""){$i2_digitos = "2";}
            if($c8_dia_cargo eq ""){$c8_dia_cargo = "1";}
      }
      #$dbh->Close();
      #print "DIA_CARGO=$c8_dia_cargo";
      $i2_digitos = 2;
      $generales = $i2_dias_mora."|".$d82_multa_minima."|".$i2_digitos;
}
sub process_fork {
      my $b = 0;
      my $c = 0;
      if($id ne ""){
            @expedientes = split(/\,/,$id);
      }else{
            @expedientes = get_expedientes($clase);            
            if($dr eq "YES"){delete_expedientes()}
      }
      #@expedientes = ("1003577");
      #print "$#expedientes\n";
      #my $file     = $path.$dt_vencimiento.".txt";
      #open("file_carga",">".$file);
      #print "$#expedientes<br>\n";$#expedientes
      for (my $a=0;$a<=$#expedientes;$a++){
            $id         = $expedientes[$a];
            #print "$id<br>\n";            
            %tarifa_desde_hasta = (); 
            %tributos	= get_tributos();
            #print "Tributos<br>\n";
            %tarifas 	= get_tarifas();
            #print "Tarifas<br>\n";
            %accesorios	= get_accesorios();
            #print "Accesorios<br>\n";
            %bases	= get_bases();
            #print "Bases<br>\n";
            %cargos	= get_cargos();
            #print "Cargos<br>\n";
            get_cargos_a_cuenta();
            #print "Cargos a Cuenta<br>\n";            
            %abonos	= get_abonos();
            #print "Abonos<br>\n";            
            $balance	= get_balance();
            #print "Balance=>$balance<br>\n";            
            if ($balance ne ""){
                  if($c11_sector_id ne ""){delete_balance_from_database($clase,$balance,$id);}
                  insert_balance_into_database($clase,$balance,$id);
            }
            if($c==5){print ".";$c=0}
            if($b==1000){print "Procesados=$a,ID Hasta=$id<br>";$b=0}
            $b++;
            $c++;
            #$balance_stream    .= $balance."^^";
            #$balance    =~ s/\&&/\n/g;
            #$balance    =~ s/\~~//g;
            #print file_carga $balance;
      }
      #close(file_carga);      
      return;
}
sub get_expedientes {
      my $stream        = "";
      my $table		= "sdm_".$clase."s";
      my $select_fields	= "i8_".$clase."_id";
      my $order_by	= "1";
      my $query         = "";
      if($c11_sector_id ne ""){
            if($clase eq "empresa"){
                  $sql ="SELECT a.i8_empresa_id FROM $table a,sdd_empresa_ubicacion b WHERE a.i8_empresa_id=b.i8_empresa_id and a.c16_estado='ACEPTADO' AND b.c11_sector_id='$c11_sector_id' ORDER BY $order_by";
            }else{
                  $sql ="SELECT $select_fields FROM $table WHERE c16_estado='ACEPTADO' AND c11_sector_id='$c11_sector_id' ORDER BY $order_by";                  
            }
      }else {
            $sql ="SELECT $select_fields FROM $table WHERE c16_estado='ACEPTADO' ORDER BY $order_by";            
      }
      #print "$query\n";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_expedientes');
	while (my @row = $sth->fetchrow_array) {
	    $stream .= $row[0].",";
      }
      $stream = substr($stream,0,length($stream)-1);
      my @expe = split(/\,/,$stream);
      return @expe;
}
sub get_tributos {
      my $pair 		= "";
      my $WHERE		= "";
      my %tribu		= ();
      my $table		= $data->{clase}->{charges}->{process}->{tributos}->{table};
      my $id_field	= $data->{clase}->{charges}->{process}->{tributos}->{id_field};
      my $select_fields	= $data->{clase}->{charges}->{process}->{tributos}->{select_fields};
      my $order_by	= $data->{clase}->{charges}->{process}->{tributos}->{order_by};      
      my @fields	= split(/\,/,$data->{clase}->{charges}->{process}->{tributos}->{filters}->{fields});
      
      for (my $i=0;$i<=$#fields;$i++){
	  my $type  = $data->{clase}->{charges}->{process}->{tributos}->{filters}->{field}->{$fields[$i]}->{type};
	  my $valor = $data->{clase}->{charges}->{process}->{tributos}->{filters}->{field}->{$fields[$i]}->{value};
	  if($type eq "post"){$pair .= "$fields[$i]='$valor' and ";}
      }
      if($pair ne ""){$pair = substr($pair,0,length($pair)-5);$WHERE = "WHERE $id_field='$id' and ".$pair}else{$WHERE = "WHERE $id_field='$id'"}
     
      my $sql = "SELECT $select_fields FROM $table $WHERE ORDER BY $order_by";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_tributos');
	while (my @row = $sth->fetchrow_array) {
	    $tribu{$row[0]}{$row[1]} = [@row];
      }
#      print "TRIBUTOS\n<br>";
#      foreach $k (sort keys %tribu){
#	    foreach $kk (sort keys %{$tribu{$k}}){
#		  print "$k=>$kk=>$tribu{$k}{$kk}[0],$tribu{$k}{$kk}[1],$tribu{$k}{$kk}[2],$tribu{$k}{$kk}[3],$tribu{$k}{$kk}[4],$tribu{$k}{$kk}[5]\n";
#	    }
#	    print "\n<br>";
#      }
      return %tribu;
}
sub get_tarifas {
    my %tari		= ();
    my %tari1		= ();      
    my %tari2		= ();
    my $database	= $data->{clase}->{charges}->{process}->{tarifas}->{database};
    my $table		= $data->{clase}->{charges}->{process}->{tarifas}->{table};
    my $id_field	= $data->{clase}->{charges}->{process}->{tarifas}->{id_field};
    my $select_fields	= $data->{clase}->{charges}->{process}->{tarifas}->{select_fields};
    my $order_by	= $data->{clase}->{charges}->{process}->{tarifas}->{order_by};      
    my @fields	= split(/\,/,$select_fields);
    my %group_count = ();
    foreach $k(sort keys %tributos){
	    foreach $kk(sort keys %{$tributos{$k}}){
            my $jj=0;
            my @row       = ();
            my $grupo	= $tributos{$k}{$kk}[0];
            my $tributo	= $tributos{$k}{$kk}[1];	    
            my $tipo	= $tributos{$k}{$kk}[2];
            my $clase	= $tributos{$k}{$kk}[3];	    
            my $sql	= "SELECT $select_fields FROM $table WHERE c8_tributo_id='$tributo' and c1_tipo_tarifa_id='$tipo' and c2_codigo_id='$clase' ORDER BY $order_by DESC";
			my $sth 	= $dbh->prepare($sql);
			$sth->execute or die SQL_fail($sql,$dbh,'sub get_tarifas');
			while (my @row = $sth->fetchrow_array) {
                        $row[6] =~ s/\-//g;
                        $tari1{$k}{$kk}{$jj}=[$row[0],$row[6]];
                        $tari{$k}{$kk}{$row[0]}{'DATA'}=[@row];
                        $tari{$k}{$kk}{$row[0]}{'COUNT'}=$jj;
                        $jj++;
                        #print "<>$row[0],$row[6]<>";
            }
            $group_count{$k}{$kk}= $jj;
	    }
    }
    foreach $k (sort keys %tari1){
	    foreach $kk (sort keys %{$tari1{$k}}){
                  foreach $kkk (sort keys %{$tari1{$k}{$kk}}){
                        $tarifa_desde_hasta{$k}{$kk}{$tari1{$k}{$kk}{$kkk}[0]}{'DESDE'}= $tari1{$k}{$kk}{$kkk}[1];
                        if ($tari1{$k}{$kk}{$kkk-1}[1] ne ""){
                              $tarifa_desde_hasta{$k}{$kk}{$tari1{$k}{$kk}{$kkk}[0]}{'HASTA'} = get_mes_anterior($tari1{$k}{$kk}{$kkk-1}[1]);
                        }
                        if ($tari1{$k}{$kk}{$kkk-1}[1] eq ""){                        
                              if($dt_futuro ne ""){
                                    $tarifa_desde_hasta{$k}{$kk}{$tari1{$k}{$kk}{$kkk}[0]}{'HASTA'}=$dt_futuro;
                              }else{
                                    $tarifa_desde_hasta{$k}{$kk}{$tari1{$k}{$kk}{$kkk}[0]}{'HASTA'}=$dt_vencimiento;
                              }
                        }
                        #print "$k<>$kk<>$tari1{$k}{$kk}{$kkk}[0]<>$group_count{$k}{$kk}<>$tarifa_desde_hasta{$k}{$kk}{$tari1{$k}{$kk}{$kkk}[0]}{'DESDE'}<>$tarifa_desde_hasta{$k}{$kk}{$tari1{$k}{$kk}{$kkk}[0]}{'HASTA'}<br>";
                  }
	    }
    }
    foreach $k (sort keys %tari){
	    foreach $kk (sort keys %{$tari{$k}}){
                  my $count = 0;
                  my $count1= 0;
                  foreach $kkk (sort keys %{$tari{$k}{$kk}}){
                        my $dt_tributo_desde	= $tributos{$k}{$kk}[4];
                        my $dt_tributo_hasta	= $tributos{$k}{$kk}[5];
                        my $dt_tarifa_desde	= $tarifa_desde_hasta{$k}{$kk}{$kkk}{'DESDE'};
                        my $dt_tarifa_hasta	= $tarifa_desde_hasta{$k}{$kk}{$kkk}{'HASTA'};
                        $dt_tributo_desde =~ s/\-//g;
                        $dt_tributo_hasta =~ s/\-//g;
                        #print "0.$k=>$kk=>$kkk=>GC:$group_count{$k}{$kk}=>TRI-DESDE:$dt_tributo_desde=>TRI-HASTA:$dt_tributo_hasta=>TAR-DESDE:$dt_tarifa_desde=>TAR-HASTA:$dt_tarifa_hasta<br>";

                        $tari2{$k}{$kk}{$kkk}{'DESDE'} = $tarifa_desde_hasta{$k}{$kk}{$kkk}{'DESDE'};
                        $tari2{$k}{$kk}{$kkk}{'HASTA'} = $tarifa_desde_hasta{$k}{$kk}{$kkk}{'HASTA'};
                        $tari2{$k}{$kk}{$kkk}{'DATA'}  = $tari{$k}{$kk}{$kkk}{'DATA'};
                        #print "$k=>$kk=>$kkk=>GC:$group_count{$k}{$kk}=>TRI-DESDE:$dt_tributo_desde=>TRI-HASTA:$dt_tributo_hasta=>TAR-DESDE:$tari2{$k}{$kk}{$kkk}{'DESDE'}=>TAR-HASTA:$tari2{$k}{$kk}{$kkk}{'HASTA'}<br>";
                  }
                  #print "<br>";
	    }
      }
#      print "TARIFAS\n<br>";      
#      foreach $k (sort keys %tari2){
#	    foreach $kk (sort keys %{$tari2{$k}}){
#                  foreach $kkk (sort keys %{$tari2{$k}{$kk}}){
#                        print "$k<>$kk<>$kkk<>$tari2{$k}{$kk}{$kkk}{'DESDE'}<>$tari2{$k}{$kk}{$kkk}{'HASTA'}<br>";
#                  }
#                  print "<br>";
#	    }
#      }
    return %tari2;
}
sub get_accesorios {#traer dias para multa desde la tabla de configuracion general
    my %acceso	= ();
    my $table		= $data->{clase}->{charges}->{process}->{accesorios}->{table};
    my $id_field	= $data->{clase}->{charges}->{process}->{accesorios}->{id_field};
    my $select_fields	= $data->{clase}->{charges}->{process}->{accesorios}->{select_fields};
    foreach $key(sort keys %tributos){
	    foreach $key1(sort keys %{$tributos{$key}}){	    
            my $grupo	= $tributos{$key}{$key1}[0];
            my $tributo	= $tributos{$key}{$key1}[1];
            my $tipo	= $tributos{$key}{$key1}[2];
            my $clase	= $tributos{$key}{$key1}[3];
            my $desde	= $tributos{$key}{$key1}[4];
            my $hasta	= $tributos{$key}{$key1}[5];
            my $sql 	= "SELECT $select_fields FROM $table WHERE $id_field='$tributo'";
            my $sth 	= $dbh->prepare($sql);
            $sth->execute or die SQL_fail($sql,$dbh,'sub get_accesorios');
            while (my @row = $sth->fetchrow_array) {
                $acceso{$tributo}{$row[0]}=[@row];
                $cod_acceso = substr($row[0],5,3);                        
                $accesorios_periodo{$tributo}{$cod_acceso}=[@row];
            }
	    }
    }
    my %acceso_tarifas= ();
    my $table1	        = $data->{clase}->{charges}->{process}->{tarifas}->{table};
    my $id_field1	    = $data->{clase}->{charges}->{process}->{tarifas}->{id_field};
    my $select_fields1  = $data->{clase}->{charges}->{process}->{tarifas}->{select_fields};
    my $order_by1	    = $data->{clase}->{charges}->{process}->{tarifas}->{order_by};      
    foreach $key(sort keys %acceso){
        foreach $key1(sort keys %{$acceso{$key}}){
			my $sql	= "SELECT $select_fields1 FROM $table1 WHERE $id_field1='$key1' ORDER BY $order_by1 ASC LIMIT 1";
			my $sth = $dbh->prepare($sql);
			$sth->execute or die SQL_fail($sql,$dbh,'sub get_accesorios 2');
			if (my @row = $sth->fetchrow_array) {
                $acceso_tarifas{$key}{$key1} = [@row];
            }
	    }
    }      

    my %atc		    = ();      
    my $table2	    = $data->{clase}->{charges}->{process}->{tarifas_compuestas}->{table};
    my $id_field2	= $data->{clase}->{charges}->{process}->{tarifas_compuestas}->{id_field};
    my $select_fields2= $data->{clase}->{charges}->{process}->{tarifas_compuestas}->{select_fields};      
    foreach $key(sort keys %acceso_tarifas){
	    foreach $key1(sort keys %{$acceso_tarifas{$key}}){	    
            my $tarifa_id			    = $acceso_tarifas{$key}{$key1}[0];
            my $tipo_tarifa_id		    = $acceso_tarifas{$key}{$key1}[1];
            my $codigo_id			    = $acceso_tarifas{$key}{$key1}[2];
            my $composicion_tarifa_id	= $acceso_tarifas{$key}{$key1}[3];
            my $frecuencia_tarifa_id	= $acceso_tarifas{$key}{$key1}[4];	    
            my $valor_tarifa		    = $acceso_tarifas{$key}{$key1}[5];
            my $desde			        = $acceso_tarifas{$key}{$key1}[6];
            if($composicion_tarifa_id eq "C"){	    
                my $m89	= "";
                my $m90	= "";
				my $sql		= "SELECT $select_fields2 FROM $table2 WHERE $id_field2='$tarifa_id'";
				my $sth 	= $dbh->prepare($sql);
				$sth->execute or die SQL_fail($sql,$dbh,'sub get_accesorios 3');
				while (my @row2 = $sth->fetchrow_array) {
                    my $tipo_tarifa_proceso_id  = $row2[0];
                    my $tipo_base_imponible_id  = $row2[1];
                    my $tipo_rango_id	   	    = $row2[2];
                    my $rango		   	        = $row2[3];
                    my $valor		   	        = $row2[4];
                    my $excendente		        = $row2[5];
                    my $divisor		            = $row2[6];
                    my $rango_minimo	   	    = int($row2[7]);
                    my $rango_maximo	   	    = int($row2[8]);
                    if($tipo_tarifa_proceso_id eq "INT"){$atc{$key}{"INT"} = $valor}
                    if($tipo_tarifa_proceso_id eq "FIE"){$atc{$key}{"FIE"} = $valor}			      
                    if($tipo_tarifa_proceso_id eq "MUL"){
                        if($rango_minimo>=1 && $rango_maximo<90){$m89="89=".$valor}
                        if($rango_minimo>=90){$m90="90=".$valor}
                        my $multa   = $m89.'|'.$m90;
                        $atc{$key}{"MUL"}  = $multa;
                    }			      
                }
            }
        }
    }      
#      print "ACCESORIOS\n";
#      foreach $k (sort keys %atc){
#	    foreach $kk (sort keys %{$atc{$k}}){
#			print "$k=>$kk=>$atc{$k}{$kk}\n";
#	    }
#      }      
    return %atc;
}
sub get_bases {
    my %base		= ();
    my %base1		= ();
    my $table		= $data->{clase}->{charges}->{process}->{tarifas_compuestas}->{table};
    my $id_field	= $data->{clase}->{charges}->{process}->{tarifas_compuestas}->{id_field};
    my $select_fields	= $data->{clase}->{charges}->{process}->{tarifas_compuestas}->{select_fields};      

    my $table1	= $data->{clase}->{charges}->{process}->{bases}->{table};
    my @id_field1	= split(/\,/,$data->{clase}->{charges}->{process}->{bases}->{id_field});
    my $select_fields1= $data->{clase}->{charges}->{process}->{bases}->{select_fields};

    foreach $k (sort keys %tarifas){
      foreach $kk (sort keys %{$tarifas{$k}}){
            foreach $kkk (sort keys %{$tarifas{$k}{$kk}}){
				my $tipo_tarifa_id			= $tarifas{$k}{$kk}{$kkk}{'DATA'}[1];
				my $codigo_id				= $tarifas{$k}{$kk}{$kkk}{'DATA'}[2];
				my $composicion_tarifa_id	= $tarifas{$k}{$kk}{$kkk}{'DATA'}[3];
				my $frecuencia_tarifa_id	= $tarifas{$k}{$kk}{$kkk}{'DATA'}[4];
				my $valor_tarifa			= $tarifas{$k}{$kk}{$kkk}{'DATA'}[5];
				my $desde					= $tarifas{$k}{$kk}{$kkk}{'DATA'}[6];
				if($composicion_tarifa_id eq "F"){ $base{$k}{$kk}{$kkk} += $valor_tarifa;				}
				if($composicion_tarifa_id eq "C"){
					my $sql = "SELECT $select_fields FROM $table WHERE $id_field='$kkk'";
					my $sth = $dbh->prepare($sql);
					$sth->execute or die SQL_fail($sql,$dbh,'sub get_bases');
					while (my @row = $sth->fetchrow_array) {
						my $tipo_tarifa_proceso_id  = $row[0];
						my $tipo_base_imponible_id  = $row[1];
						my $tipo_rango_id	   	 	= $row[2];
						my $rango		   	 		= $row[3];
						my $valor		   	 		= $row[4];
						my $excendente		 		= $row[5];
						my $divisor					= $row[6]*1;
						my $rango_minimo			= $row[7];
						my $rango_maximo			= $row[8];
						my $sql1	= "SELECT $select_fields1 FROM $table1 WHERE $id_field1[0]='$id' AND c2_tipo_base_imponible_id='$tipo_base_imponible_id' and i2_grupo_id='$k'";
						if($tipo_tarifa_proceso_id eq "CRBXV"){
							$sql1	= "SELECT $select_fields1 FROM $table1 WHERE $id_field1[0]='$id' AND c2_tipo_base_imponible_id='$tipo_base_imponible_id'";
						}
						#print "$sql1 <br>";
						my $sth1 	= $dbh1->prepare($sql1);
						$sth1->execute or die SQL_fail($sql1,$dbh1,'sub get_bases 1');
						while (my @row1 = $sth1->fetchrow_array) {
							my $f4_total		= $row1[0];
							my $f4_deducible	= $row1[1];
							my $i2_tipo_nivel_id= $row1[2];
							my $i2_divisor	  	= $row1[3]*1;
							my $dt_base_desde   = $row1[4];
							my $i2_grupo_id		= $row1[5];							
							my $imponible	    = $f4_total-$f4_deducible;
							$dt_base_desde      =~ s/\-//g;
							$bases_imponibles{$k}{$kk}{'BASE'} = $imponible;
							$bases_imponibles{$k}{$kk}{'DESDE'}= $dt_base_desde;
							if($tipo_tarifa_proceso_id eq "BXV"){
								$base{$k}{$kk}{$kkk} += $imponible*$valor;
								if($rango_minimo>0){if($base{$k}{$kk}{$kkk}<$rango_minimo){$base{$k}{$kk}{$kkk}=$rango_minimo}}
								if($rango_minimo>0){if($base{$k}{$kk}{$kkk}>$rango_maximo){$base{$k}{$kk}{$kkk}=$rango_maximo}}
							}
							if($tipo_tarifa_proceso_id eq "BVT"){
								my $calculo = $imponible*$valor;
								if($calculo > $rango_maximo){
									$calculo = $rango_maximo;
								}
								$base{$k}{$kk}{$kkk} += $calculo;
							}
							if($tipo_tarifa_proceso_id eq "BME"){
								if ($imponible>$rango_maximo){
									if($excendente ne ""){
										if($divisor==0 || $divisor eq ""){$divisor=1}
										$base{$k}{$kk}{$kkk} += round($valor+((($imponible-$rango_maximo)/$divisor)*$excendente),$i2_digitos);
									}
								}else{
									$base{$k}{$kk}{$kkk} += $valor;
								}
							}
							if($tipo_tarifa_proceso_id eq "CR"){
								if ($imponible>=$rango_minimo && $imponible<=$rango_maximo){
									if($excendente ne ""){
										if($divisor==0){$divisor=1}
										my $resultado = (($imponible-$rango_minimo)/$divisor);
										my $entero    = int($resultado);
										my $decimal   = round(($resultado - int($resultado)),$i2_digitos);
										if ($decimal>.00){$entero++}
										$base{$k}{$kk}{$kkk} += round($valor+($entero*$excendente),$i2_digitos);
									}else{
										$base{$k}{$kk}{$kkk} += $valor;
									}
								}
							}
							if($tipo_tarifa_proceso_id eq "CRB"){
								if ($imponible>=$rango_minimo && $imponible<=$rango_maximo){
									if($excendente ne ""){
										if($divisor==0){$divisor=1}
										my $resultado = round((($imponible*$valor)+$excendente),$i2_digitos);
										$base{$k}{$kk}{$kkk} += $resultado;
									}else{
										$base{$k}{$kk}{$kkk} += $excendente;
									}
								}
							}
							if($tipo_tarifa_proceso_id eq "CRFE"){
								if ($imponible>=$rango_minimo && $imponible<=$rango_maximo){
									if($excendente ne ""){
										if($divisor==0){$divisor=1}
										my $resultado = round((($imponible*$excendente)+$valor),$i2_digitos);
										$base{$k}{$kk}{$kkk} += $resultado;
									}else{
										$base{$k}{$kk}{$kkk} += $valor;
									}
								}
							}
							if($tipo_tarifa_proceso_id eq "CRPE"){
								if ($imponible>=$rango_minimo && $imponible<=$rango_maximo){
									if($excendente ne ""){
										  if($tipo_rango_id eq "RE"){
												$base{$k}{$kk}{$kkk} += round($valor+($imponible*$excendente),$i2_digitos);
										  }else{
												if($divisor==0){$divisor=1}
												my $resultado = (($imponible-$rango_minimo)/$divisor);
												my $entero    = int($resultado);
												my $decimal   = round(($resultado - int($resultado)),$i2_digitos);
												if ($decimal>.00){$entero++}
												$base{$k}{$kk}{$kkk} += round($valor+($entero*$excendente),$i2_digitos);                                                                  
										  }
									}else{
										  $base{$k}{$kk}{$kkk} += $valor;
									}
								}
							}
							if($tipo_tarifa_proceso_id eq "CRBXV"){
								if ($imponible>=$rango_minimo && $imponible<=$rango_maximo){
									$base{$i2_grupo_id}{$kk}{$kkk} = $imponible*$valor;
									#{202201}{$kk}{$kkk}
								}
							}
						}
					}
				}
			}
        }
    }
#      print "BASES IMPONIBLES\n<br>";
#      foreach $k (sort keys %base){
#	    foreach $kk (sort keys %{$base{$k}}){
#                  foreach $kkk (sort keys %{$base{$k}{$kk}}){
#                        print "$k=>$kk=>$kkk=>$base{$k}{$kk}{$kkk}\n<br>";
#                  }
#	    }
#      }
#      print "END - BASES IMPONIBLES\n<br>";      
      return %base;
}
sub get_cargos {
    my $dt_actual	        = "";
    my %cargo		  	= ();
    my %base			= ();
    my %tribu			= ();
    if($dt_vencimiento ne ""){$dt_actual= $dt_vencimiento;}else{$dt_actual= get_fecha_actual()}
    foreach $k (sort keys %tarifas){
	    foreach $kk (sort keys %{$tarifas{$k}}){
                  foreach $kkk (sort keys %{$tarifas{$k}{$kk}}){
                        my $dt_tributo_desde	= $tributos{$k}{$kk}[4];
                        my $dt_tributo_hasta	= $tributos{$k}{$kk}[5];
                        my $frecuencia_tarifa = $tarifas{$k}{$kk}{$kkk}{'DATA'}[4];
                        my $dt_tarifa_desde   = $tarifas{$k}{$kk}{$kkk}{'DESDE'};
                        my $dt_tarifa_hasta   = $tarifas{$k}{$kk}{$kkk}{'HASTA'};
                        $dt_tributo_desde =~ s/\-//g;
                        $dt_tributo_hasta =~ s/\-//g; 
                        my ($dt_cobro_desde,$dt_cobro_hasta) = get_periodo_cobro($kk,$dt_tarifa_desde,$dt_tarifa_hasta);
                        my @periodos	 = get_periodos($dt_tarifa_desde,$dt_tarifa_hasta);
                        for(my $p=0;$p<=$#periodos;$p++){
                              if(substr($dt_cobro_desde,0,6)<=$periodos[$p] && substr($dt_cobro_hasta,0,6)>=$periodos[$p]){
                                    if(substr($dt_tributo_desde,0,6)<=$periodos[$p] && substr($dt_tributo_hasta,0,6)>=$periodos[$p]){
                                          $cargo{$periodos[$p]}{$kkk}{'PER'} = $periodos[$p];
                                          $cargo{$periodos[$p]}{$kkk}{'DIM'} = get_dias_mora($periodos[$p],$dt_actual);
                                          if($frecuencia_tarifa eq "M"){$cargo{$periodos[$p]}{$kkk}{'VAL'} = $bases{$k}{$kk}{$kkk};}
                                          if($frecuencia_tarifa eq "D"){
                                                my $y = substr($periodos[$p],0,4)*1;
                                                my $m = substr($periodos[$p],4,2)*1;
                                                my $dias_mes = Days_in_Month($y,$m);
                                                $cargo{$periodos[$p]}{$kkk}{'VAL'} = $bases{$k}{$kk}{$kkk}*$dias_mes;
                                          }
                                          foreach my $k1 (sort keys %{$accesorios{$kk}}){
                                                my $acc_desde = $accesorios_periodo{$kk}{$k1}[1];
                                                my $acc_hasta = $accesorios_periodo{$kk}{$k1}[2];
                                                $acc_desde =~ s/\-//g;
                                                $acc_hasta =~ s/\-//g;                                                
                                                if(substr($acc_desde,0,6)<=$periodos[$p] && substr($acc_hasta,0,6)>=$periodos[$p]){
													  $cargo{$periodos[$p]}{$kkk}{$k1} = get_tarifa_accesorio($periodos[$p],$k1,$accesorios{$kk}{$k1},$cargo{$periodos[$p]}{$kkk}{"VAL"},$cargo{$periodos[$p]}{$kkk}{"DIM"});
                                                }
                                          }
                                    }
                              }
                        }
                  }
            }
      }  
#      print "CARGOS\n<br>";      
#      foreach $k (sort keys %cargo){
#	    foreach $kk (sort keys %{$cargo{$k}}){
#		  print "$k=>$kk=>$cargo{$k}{$kk}{'VAL'}";
#	    }
#	    print "\n<br>";
#      } 
    return %cargo;      
}
sub get_cargos_agua {
	my $dt_actual	= get_fecha_actual();
	if($dt_vencimiento ne ""){$dt_actual= $dt_vencimiento;}
	foreach $k (sort keys %bases){
		foreach $kk (sort keys %{$bases{$k}}){
			if($kk eq "TSINMAGU"){
				foreach $kkk (sort keys %{$bases{$k}{$kk}}){
					$cargos{$k}{$kkk}{'PER'} = $k;
					$cargos{$k}{$kkk}{'DIM'} = get_dias_mora($k,$dt_actual);
					$cargos{$k}{$kkk}{'VAL'} = round($bases{$k}{$kk}{$kkk},$i2_digitos);
					foreach my $k1 (sort keys %{$accesorios{$kk}}){
						my $acc_desde = $accesorios_periodo{$kk}{$k1}[1];
						my $acc_hasta = $accesorios_periodo{$kk}{$k1}[2];
						$acc_desde =~ s/\-//g;
						$acc_hasta =~ s/\-//g;
						if(substr($acc_desde,0,6)<=$k && substr($acc_hasta,0,6)>=$k){
							$cargos{$k}{$kkk}{$k1} = get_tarifa_accesorio($k,$k1,$accesorios{$kk}{$k1},$cargos{$k}{$kkk}{"VAL"},$cargos{$k}{$kkk}{"DIM"});
						}
					}
				}
			}
		}
	}
	
}
sub get_cargos_a_cuenta {
    my $cac                 = "";
    my $dt_actual	        = "";
    if(!$data->{clase}->{charges}->{to_account}){return}
    my $table		        = $data->{clase}->{charges}->{to_account}->{table};
    my $id_field	        = $data->{clase}->{charges}->{to_account}->{id_field};
    my $period_field	    = $data->{clase}->{charges}->{to_account}->{period_field};
    my $value_field	        = $data->{clase}->{charges}->{to_account}->{value_field};
    my $tributo_field	    = $data->{clase}->{charges}->{to_account}->{tributo_field};
    my $tarifa_field	    = $data->{clase}->{charges}->{to_account}->{tarifa_field};
    my $date_field	        = $data->{clase}->{charges}->{to_account}->{date_field};
    my $filter_field	    = $data->{clase}->{charges}->{to_account}->{filter_field};
    my $filter_value_field  = $data->{clase}->{charges}->{to_account}->{filter_value_field};

    my $a_table	            = $data->{clase}->{charges}->{process}->{accesorios}->{table};
    my $a_id_field	        = $data->{clase}->{charges}->{process}->{accesorios}->{id_field};
    my $a_select_fields     = $data->{clase}->{charges}->{process}->{accesorios}->{select_fields};

    my $table1	            = $data->{clase}->{charges}->{process}->{tarifas}->{table};
    my $id_field1	        = $data->{clase}->{charges}->{process}->{tarifas}->{id_field};
    my $select_fields1      = $data->{clase}->{charges}->{process}->{tarifas}->{select_fields};
    my $order_by1	        = $data->{clase}->{charges}->{process}->{tarifas}->{order_by};
    
    my $table2	            = $data->{clase}->{charges}->{process}->{tarifas_compuestas}->{table};
    my $id_field2	        = $data->{clase}->{charges}->{process}->{tarifas_compuestas}->{id_field};
    my $select_fields2      = $data->{clase}->{charges}->{process}->{tarifas_compuestas}->{select_fields};
    
    if($dt_vencimiento ne ""){$dt_actual= $dt_vencimiento;}else{$dt_actual= get_fecha_actual()}
    
	my $where 	= "$id_field='$id' and $filter_field='$filter_value_field'";
	my $sql		= "SELECT $period_field,$value_field,$tributo_field,$tarifa_field FROM $table WHERE $where";
	my $sth 	= $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_cargo_a_cuenta');
	while (my @row = $sth->fetchrow_array) {
		my $dias_mes = Days_in_Month((substr($row[0],0,4)*1),(substr($row[0],4,2)*1));
		my $dth    = $row[0].$dias_mes;
		my $dtd    = $row[0]."01";
		my ($dt_cobro_desde,$dt_cobro_hasta) = get_periodo_cobro($row[2],$dtd,$dth);
		if(substr($dt_cobro_desde,0,6)<=$row[0] && substr($dt_cobro_hasta,0,6)>=$row[0]){
			$cargos{$row[0]}{$row[3]}{'PER'} = $row[0];
			$cargos{$row[0]}{$row[3]}{'VAL'} = $row[1];
			$cargos{$row[0]}{$row[3]}{'DIM'} = get_dias_mora($row[0],$dt_actual);
			my $sql1 = "SELECT $a_select_fields FROM $a_table WHERE $a_id_field='$row[2]'";
			my $sth1 = $dbh1->prepare($sql1);
			$sth1->execute or die SQL_fail($sql1,$dbh1,'sub get_cargo_a_cuenta 1');
			while (my @row1 = $sth1->fetchrow_array) {
				$row1[1] =~ s/\-//g;
				$row1[2] =~ s/\-//g;
				my $cc_desde =substr($row1[1],0,6);
				my $cc_hasta =substr($row1[2],0,6);                  
				if($cc_desde<=$row[0] && $cc_hasta>=$row[0]){
					my $sql2	= "SELECT $select_fields1 FROM $table1 WHERE $id_field1='$row1[0]' ORDER BY $order_by1 ASC LIMIT 1";
					my $sth2 	= $dbh2->prepare($sql2);
					$sth2->execute or die SQL_fail($sql2,$dbh2,'sub get_cargos_a_cuenta 2');
					while (my @row2 = $sth2->fetchrow_array) {
						if($row2[3] eq "C"){	    
							my $m89		= "";
							my $m90		= "";
							my $sql3	= "SELECT $select_fields2 FROM $table2 WHERE $id_field2='$row2[0]'";
							my $sth3 	= $dbh3->prepare($sql3);
							$sth3->execute or die SQL_fail($sql3,$dbh3,'sub get_cargo_a_cuenta 3');
							while (my @row3 = $sth3->fetchrow_array) {
								my $rango_minimo = int($row2[7]);
								my $rango_maximo = int($row2[8]);
								#print "$row3[0]<br>";
								if($row3[0] eq "MUL"){
									if($rango_minimo>= $cargos{$row[0]}{$row[3]}{'DIM'} && $rango_maximo<= $cargos{$row[0]}{$row[3]}{'DIM'}){
										$cargos{$row[0]}{$row[3]}{$row3[0]} = $cargos{$row[0]}{$row[3]}{'DIM'}*$cargos{$row[0]}{$row[3]}{'VAL'}*($row3[4]/365);
										$cargos{$row[0]}{$row[3]}{$row3[0]} = ($cargos{$row[0]}{$row[3]}{$row3[0]});
									}
								}else{
									$cargos{$row[0]}{$row[3]}{$row3[0]} = $cargos{$row[0]}{$row[3]}{'DIM'}*$cargos{$row[0]}{$row[3]}{'VAL'}*($row3[4]/365);
								}
							}
						}
					}
				}
            }
		}
    }       
#      print "CARGOS A CUENTA\n<br>";      
#      foreach $k (sort keys %cargos){
#	    foreach $kk (sort keys %{$cargos{$k}}){
#		  print "$k=>$kk=>$cargos{$k}{$kk}{'PER'}=>PER<br>";
#		  print "$k=>$kk=>$cargos{$k}{$kk}{'DIM'}=>DIM<br>";                  
#		  print "$k=>$kk=>$cargos{$k}{$kk}{'VAL'}=>VAL<br>";
#		  print "$k=>$kk=>$cargos{$k}{$kk}{'FIE'}=>FIE<br>";
#		  print "$k=>$kk=>$cargos{$k}{$kk}{'INT'}=>INT<br>";
#		  print "$k=>$kk=>$cargos{$k}{$kk}{'MUL'}=>MUL<br>";                  
#	    }
#	    print "\n<br>";
#      }        
}
sub get_abonos {
    my %abono		= ();
    %balance_type     = ();
    my $table		= $data->{clase}->{installments}->{table};
    my $id_field	= $data->{clase}->{installments}->{id_field};
    my $select_fields	= $data->{clase}->{installments}->{select_fields};
    
    my $sql		= "SELECT $select_fields FROM $table WHERE $id_field='$id'";
    my $sth 	= $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_abonos');
    while (my @row = $sth->fetchrow_array) {
        $abono{$row[0]}{$row[1]}{$row[3]} += $row[2];
        $balance_type{$row[0]}{$row[1]}{$row[3]} = $row[4];
    }
#      print "ABONOS\n";      
#      foreach $k (sort keys %abono){
#	    foreach $kk (sort keys %{$abono{$k}}){
#		  foreach $kkk (sort keys %{$abono{$k}{$kk}}){  
#			print "$k=>$kk=>$kkk=>$abono{$k}{$kk}{$kkk}\n";
#		  }
#	    }
#	    print "\n";	    
#      }
    return %abono;
}
sub get_balance {
	my $stream = "";
	my %bal	 = ();
	my $balance_tipo	= "\U$data->{clase}->{balance_type}";
	$generales .= "|".$balance_tipo;      
	if($balance_tipo eq ""){$balance_tipo = "ABONO";}
	foreach my $k (sort keys %cargos){
	    foreach my $kk (sort keys %{$cargos{$k}}){
			my $suma_tarifa = 0.0000;
			my $dim	  = "";		  
			my $per	  = "";
			foreach my $kkk (sort keys %{$cargos{$k}{$kk}}){
				if($kkk eq "VAL" || $kkk eq "FIE" || $kkk eq "INT" || $kkk eq "MUL"){
					if(($cargos{$k}{$kk}{$kkk}*1)!=0){
						if($balance_tipo eq "ABONO"){
							if(!$abonos{$k}{$kk}{$kkk}){
								$bal{$k}{$kk}{$kkk} = $cargos{$k}{$kk}{$kkk};
								$suma_tarifa += $bal{$k}{$kk}{$kkk};
							}
						}
						if($balance_tipo eq "SALDO"){
							if($abonos{$k}{$kk}{$kkk}){
								my $cargo = round($cargos{$k}{$kk}{$kkk},$i2_digitos);
								if (($cargo-$abonos{$k}{$kk}{$kkk})<-0.0001 || ($cargo-$abonos{$k}{$kk}{$kkk})>=0.0001) {
									$bal{$k}{$kk}{$kkk} = $cargo-$abonos{$k}{$kk}{$kkk};
								}
							}else{
								$bal{$k}{$kk}{$kkk} = $cargos{$k}{$kk}{$kkk};				    
							}
												$suma_tarifa += $bal{$k}{$kk}{$kkk};
						}
						if($balance_tipo eq "BANDERA"){
							if(!$abonos{$k}{$kk}{$kkk}){
								$bal{$k}{$kk}{$kkk} = $cargos{$k}{$kk}{$kkk};
								$suma_tarifa += $bal{$k}{$kk}{$kkk};
							}else{
								if($balance_type{$k}{$kk}{$kkk} eq "S"){
									my $cargo = round($cargos{$k}{$kk}{$kkk},$i2_digitos);
									if (($cargo-$abonos{$k}{$kk}{$kkk})<-0.0001 || ($cargo-$abonos{$k}{$kk}{$kkk})>=0.0001) {
										$bal{$k}{$kk}{$kkk} = $cargo-$abonos{$k}{$kk}{$kkk};
										$suma_tarifa += $bal{$k}{$kk}{$kkk};
									}
								}
							}
						}                                    
					}
				}
			}
			if($suma_tarifa!=0){
				$bal{$k}{$kk}{'PER'} = $cargos{$k}{$kk}{'PER'};
				$bal{$k}{$kk}{'DIM'} = $cargos{$k}{$kk}{'DIM'};			
			}
		}
	}
	foreach my $p (sort keys %bal){
		my $no_tarif = "";
		foreach my $pp (sort keys %{$bal{$p}}){
			if($bal{$p}{$pp}{'VAL'}){
				if($bal{$p}{$pp}{'VAL'}<0){
					  $bal{$p}{$pp}{'FIE'}= 0;
					  $bal{$p}{$pp}{'INT'}= 0;
					  $bal{$p}{$pp}{'MUL'}= 0;                              
				}
				if($dt_corte_desde eq "" || $dt_corte_hasta eq ""){
					  $stream .= $p."|";
					  $stream .= $bal{$p}{$pp}{'DIM'}."|";
					  $stream .= $pp."|";		  
					  $stream .= round($bal{$p}{$pp}{'VAL'},$i2_digitos)."|";
					  $stream .= round($bal{$p}{$pp}{'FIE'},$i2_digitos)."|";
					  $stream .= round($bal{$p}{$pp}{'INT'},$i2_digitos)."|";
					  $stream .= round($bal{$p}{$pp}{'MUL'},$i2_digitos)."|";
					  $stream .= "&&";		  
					  $rtributos{substr($pp,0,8)}= substr($pp,0,8);
					  $no_tarif .= $stream;
				}else{
					if(substr($dt_corte_desde,0,6)<=$p && substr($dt_corte_hasta,0,6)>=$p){
						$stream .= $p."|";
						$stream .= $bal{$p}{$pp}{'DIM'}."|";
						$stream .= $pp."|";		  
						$stream .= round($bal{$p}{$pp}{'VAL'},$i2_digitos)."|";
						$stream .= round($bal{$p}{$pp}{'FIE'},$i2_digitos)."|";
						$stream .= round($bal{$p}{$pp}{'INT'},$i2_digitos)."|";
						$stream .= round($bal{$p}{$pp}{'MUL'},$i2_digitos)."|";
						$stream .= "&&";		  
						$rtributos{substr($pp,0,8)}= substr($pp,0,8);
						$no_tarif .= $stream;
					}
				}	
			}                        
		}
	    if($no_tarif ne ""){$stream .= "~~";}
	}
	return $stream;     
}
sub insert_balance_into_file {
      my ($balance_stream)=@_;      
      my $file     = $path.$dt_vencimiento.".txt";
      my $registro = $balance_stream;
      $registro    =~ s/\&&/\n/g;
      $registro    =~ s/\~~//g;
      $registro    =~ s/\^^//g;      
      open("file_carga",">".$file);
      print file_carga $registro;
      close(file_carga);      
}
sub delete_balance_from_database {
    my ($clase,$balance,$id)=@_;
    my $database	= $appid;
    my $table		= "sdd_".$clase."_estados_mensuales";
    my $query   = "DELETE FROM $table WHERE i8_".$clase."_id='$id'";
    #print "$query\n";
    $dbh->do($query); 
}
sub insert_balance_into_database {
    my ($clase,$balance,$id)=@_;
    my $database	= $appid;
    my $table		= "sdd_".$clase."_estados_mensuales";
    my $insert_fields	= "i8_".$clase."_id,i8_periodo,i4_dias_mora,c16_tarifa_id,d82_val,d82_fie,d82_int,d82_mul,dt_generado";      
    my @per_records	= split(/~~/,$balance);
    for (my $a=0;$a<=$#per_records;$a++){
        my @tar_records	= split(/&&/,$per_records[$a]);
        for (my $b=0;$b<=$#tar_records;$b++){            
            my @values = split(/\|/,$tar_records[$b]);
            my $per    = $values[0];
            my $dim    = $values[1];
            my $tar    = $values[2];
            my $val    = $values[3];
            my $fie    = $values[4];
            my $int    = $values[5];
            my $mul    = $values[6];
            if ($val>0) {
                $query = "INSERT INTO $table ($insert_fields) VALUES ('$id','$per','$dim','$tar',$val,$fie,$int,$mul,'$dt_vencimiento')";
                #print "$query<br>\n";
                #print "TRUE|";
                $dbh->do($query);
            }
        }
    }
}
sub delete_records {
      my ($clase,$del)=@_;
      my $database = $appid;
      my $table	   = "sdd_".$clase."_estados_mensuales";
      if($del==1){
            for (my $a=0;$a<=$#expedientes;$a++){
                  my $iden = $expedientes[$a];
                  $query = "DELETE FROM $table WHERE i8_inmueble_id='$iden'";
                  $dbh->do($query);
            }
      }
      if($del==2){
            $query = "DELETE FROM $table";
            $dbh->do($query);
      }
}
sub construct_page {
      #my $doc = $balance."^^".$tributos_labels."^^".$accesorios_labels."^^".$grid_header."^^".$grid_body."^^".$grid_footer."^^".$billing."^^".$parametros;
      #my $doc = $balance;
      print "Estados Mensuales Generados<br>";
      #print "true";
}
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 2
sub delete_expedientes {
    my $database = $appid;
    my $table	   = "sdd_".$clase."_estados_mensuales";
    my $query    = "DELETE FROM $table";
    #print "$query<br>";
    $dbh->do($query);
}
sub get_fecha_actual {
    my $sql = "SELECT DATE_FORMAT(CURRENT_DATE,'%Y%m%d')";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_fecha_actual');
	if (my @row = $sth->fetchrow_array) {
		return ($row[0]);
	}else{
		return (0)
	}
}
sub get_periodo_cobro {
	my ($tributo_id,$dt_td,$dt_th)	= @_;
	
	my $desde		= $dt_td;
	my $hasta		= $dt_th;
	my $table		= $data->{clase}->{charges}->{process}->{inicio_cobros}->{table};
	my $id_field	= $data->{clase}->{charges}->{process}->{inicio_cobros}->{id_field};
	my $select_fields	= $data->{clase}->{charges}->{process}->{inicio_cobros}->{select_fields};      

    my $sql   = "SELECT $select_fields FROM $table WHERE $id_field='$id' AND c8_tributo_id='$tributo_id'";
	my $sth 	= $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_accesorios');
	if (my @row = $sth->fetchrow_array) {
	    $row[0] =~ s/\-//g;
	    $row[1] =~ s/\-//g;
	    $desde=$row[0];
	    $hasta=$row[1];	    
    }
    return($desde,$hasta);
}
sub get_periodos {
      my ($lest_recent_ts,$most_recent_ts)=@_;
      my @periods = ();
      my $y1 = substr($lest_recent_ts,0,4);
      my $m1 = substr($lest_recent_ts,4,2);
      #my $d1 = substr($lest_recent_ts,6,2);
      my $d1 = "01"; 
      my $y2 = substr($most_recent_ts,0,4);
      my $m2 = substr($most_recent_ts,4,2);
      #my $d2 = substr($most_recent_ts,6,2);
      my $d2 = "28";
      my ($years,$months,$days) = Delta_YMD($y1,$m1,$d1,$y2,$m2,$d2);
      my $total_months = $years*12+$months;
      $year=($y1*1);$mes=($m1*1);
      for(my $i=0;$i<=$total_months;$i++){
	  if($mes<10){$month="0".$mes}else{$month=$mes}
	  $periods[$i]= $year.$month;
	  if($mes>=12){$mes=0;$year++}
	  $mes++;
      }
      return @periods;
}
sub get_dias_mora {
      my ($lest_recent_date,$most_recent_date) = @_;

      $y1  = substr($most_recent_date,0,4);
      $mo1 = substr($most_recent_date,4,2);
      $d1  = substr($most_recent_date,6,2);
      $d1  = $d1*1;
      #if ($mo1<12){$mo1++;}else{$mo1=1;$y1++}      
      #if ($mo1<12){}else{$mo1=1;$y1++}
      $y2  = substr($lest_recent_date,0,4)*1;
      $mo2 = substr($lest_recent_date,4,2)*1;
      #$d2  = 1;
      $d2  = get_dia_cargo($y2,$mo2);     
      #if ($mo2<12){$mo2++;}else{$mo2=1;$y2++}
      #if ($mo2<12){}else{$mo2=1;$y2++}
      #print "$y2,$mo2,$d2<>$y1,$mo1,$d1<br>\n";
      $dm  = Delta_Days($y2,$mo2,$d2,$y1,$mo1,$d1);
      #print "$dm\n";
      #my $periodo_gracia = get_periodo_gracia();
      my $periodo_gracia = $i2_dias_mora;      
      $dm  = $dm-$periodo_gracia+1;
      if ($dm<0){$dm=0;}
      if ($dm eq ""){$dm=0;}      

      return $dm;
}
sub get_tarifa_accesorio {
	my ($periodo,$acce_id,$acce_params,$tarifa_valor,$dias_mora) = @_;
	my $acce_valor = 0;
	if($acce_id eq "FIE"){$acce_valor = $tarifa_valor*$acce_params;}      
	if($acce_id eq "INT"){
		$acce_valor = $dias_mora*$tarifa_valor*($acce_params/365);
		#print "$periodo > $dias_mora > $tarifa_valor > $acce_params <br>";
	}
	if($acce_id eq "MUL"){
		my @params	= split(/\|/,$acce_params);
		my @mul89	= split(/\=/,$params[0]);
		my @mul90	= split(/\=/,$params[1]);
		if($dias_mora>=1 && $dias_mora<90){$acce_valor = $tarifa_valor*$mul89[1]}
		if($dias_mora>=90){$acce_valor = $acce_valor = $tarifa_valor*$mul90[1]}
		if($dias_mora<1){$acce_valor = 0}	    
	}
    $acce_valor = round($acce_valor,$i2_digitos);
	return $acce_valor;
}
sub get_bases_imponibles {
    my $bas_imp ="";
    foreach $k (sort keys %bases_imponibles){
        foreach $kk (sort keys %{$bases_imponibles{$k}}){
            $bas_imp .= $k.",".$kk.",".$bases_imponibles{$k}{$kk}{BASE}.",".$bases_imponibles{$k}{$kk}{DESDE}."|";
        }
    }      
    return $bas_imp;
}
sub get_mes_anterior {
     my ($mes_actual) = @_;
     my $year  = substr($mes_actual,0,4);
     my $month = substr($mes_actual,4,2);
     my $day   = substr($mes_actual,6,2);     
     my ($y,$m,$d) = Add_Delta_YMD($year,$month,$day,0,-1,0);
     if(length($m)<2){$m="0".$m}
     if(length($d)<2){$d="0".$d}     
     return ($y.$m.$d)
}

sub get_hasta {
     my ($mes_tar) = @_;
     my $year  = substr($mes_tar,0,4);
     my $month = substr($mes_tar,4,2);
     my $day   = substr($mes_tar,6,2);
     #print "<>$year,$month,$day<>";
     my ($y,$m,$d) = Add_Delta_YMD($year,$month,$day,0,0,-1);
     if(length($m)<2){$m="0".$m}
     if(length($d)<2){$d="0".$d}     
     #print "<=>$y.$m.$d<=>";
     return ($y.$m.$d)     
}
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 3
sub get_periodo_gracia {#recordar arreglar periodo gracia
      return 60;
}
sub get_dia_cargo {
      my ($year,$month) = @_;
      my $days=1;
      if($c8_dia_cargo eq "LAST" ||$c8_dia_cargo eq "ULTIMO"){
            $days = Days_in_Month($year,$month)
      }else{
            $days=$c8_dia_cargo*1;
            $days = Days_in_Month($year,$month)            
      }
      #print "DIA_CARGO=$c8_dia_cargo<>DAYS=$days\n";
      return $days;
}
#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub get_app_data {
    my $schema   = "app.data.xml";
    my $xml      = new XML::Simple;
    my $data 	  = $xml->XMLin("../xml/".$schema);
    $appid	  = $data->{portal}->{app};
    $lan	  = $data->{portal}->{lang};
}
sub get_component_schema {
      my ($cmp)=@_;
      my $schema  = $cmp."_bsws.xml";
      $xml        = new XML::Simple;
      $data 	  = $xml->XMLin("../xml/".$schema);
      $path       = "../archivos_carga/";      
}
sub open_conection {
	$dbh 	= DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
	if (!$dbh) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
	$dbh->{LongReadLen} = 512 * 1024;
	$dbh->{LongTruncOk} = 1;	
	$dbh1 	= DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
	if (!$dbh1) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
	$dbh1->{LongReadLen} = 512 * 1024;
	$dbh1->{LongTruncOk} = 1;
	$dbh2 	= DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
	if (!$dbh2) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
	$dbh2->{LongReadLen} = 512 * 1024;
	$dbh2->{LongTruncOk} = 1;	
	$dbh3 	= DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
	if (!$dbh3) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
	$dbh3->{LongReadLen} = 512 * 1024;
	$dbh3->{LongTruncOk} = 1;
}
sub SQL_fail {
    ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}
sub round {
    my ($n, $places) = @_;
    my $abs = abs $n;
    my $val = substr($abs + ('0.' . '0' x $places . '5'),0,length(int($abs)) +(($places > 0) ? $places + 1 : 0));
    ($n < 0) ? "-" . $val : $val;
}
#------------------------------------------------------------------------