#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Information Software SA, WebSoft,SA de CV
# Date In: 2014-07-30
# Updates: 2014-07-30,2022-08-18
# Author:Rodolfo Machon
# File Name: compensar_pagos_s.pl
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#PERL MODULES
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
binmode STDOUT, ":encoding(utf8)";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#HTML printing activation
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$form		= new CGI;
$clase		= $form->param("clase");
$id			= $form->param("i8_expediente_id");
$Compensar	= $form->param("Compensar");
$ACompensar	= $form->param("ACompensar");
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($d82_cuota eq ""){$d82_cuota=0}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET VARIABLES
get_app_data();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# LOCAL VARIABLES
$PATH 		= "../$lan/";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
$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;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
$dt_actual   	= get_fecha_hoy();
$ultimo		 	= get_ultimo_abono();
%tarifas     	= get_tarifas();
%tarifas_d   	= get_tarifas_detalles();      
%accesorios  	= get_accesorios();
%servicios   	= get_servicios();
%bases       	= get_bases();
# INTEGRATION
%cargos	  		= put_cargos();
%compensados	= put_compensados();
%acompensar		= put_acompensar();
# PRESENTATION
insert_compensados();
insert_acompensar();
print "Las Compensaciones Fueron Procesadas Exitosamente!!";
exit;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_ultimo_abono {
	my $str   = "";
	my $sql	= "SELECT i8_periodo FROM sdd_".$clase."_abonos WHERE i8_".$clase."_id='$id' ORDER BY i8_periodo DESC LIMIT 1";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_ultimo_abono');
    while (my @row = $sth->fetchrow_array) {
		$str = $row[0];
	}
    return $str;
}
sub get_tarifas {
	my %tari  = ();
	my $str   = "";
	my $sql= "SELECT MAX(c16_tarifa_id) FROM sdm_tarifas group by c8_tributo_id,c1_tipo_tarifa_id,c2_codigo_id";      
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_tarifas');
	if (my @row = $sth->fetchrow_array) {
		  $str .= "'".$row[0]."',";
	}
    $str = substr($str,0,length($str)-1);
	my $sql1 = "SELECT c16_tarifa_id,c8_tributo_id,c1_tipo_tarifa_id,c2_codigo_id,c1_frecuencia_tarifa_id,c1_composicion_tarifa_id,d82_tarifa,dt_desde FROM sdm_tarifas WHERE c16_tarifa_id in ($str)";
	my $sth = $dbh->prepare($sql1);
	$sth->execute or die SQL_fail($sql1,$dbh,'sub get_tarifas');
	while (my @row = $sth->fetchrow_array) {
		$tari{$row[1]}{$row[2]}{$row[3]}{$row[4]}{$row[5]} = [@row];
	}
#      print "TARIFAS\n<br>";      
#      foreach $k (sort keys %tari){
#	    foreach $kk (sort keys %{$tari{$k}}){
#            	    foreach $kkk (sort keys %{$tari{$k}{$kk}}){
#                        foreach $kkkk (sort keys %{$tari{$k}{$kk}{$kkk}}){
#                              foreach $kkkkk (sort keys %{$tari{$k}{$kk}{$kkk}{$kkkk}}){
#                                    print "$k=>$kk=>$kkk=>$kkkk=>$kkkkk<>$tari{$k}{$kk}{$kkk}{$kkkk}{$kkkkk}[6]<br>";
#                              }
#                        }
#            	    }
#            }
#	    print "\n<br>";
#      }
      return %tari;
}
sub get_tarifas_detalles {
	my %tarid  = ();
	my $sql	 = "SELECT c16_tarifa_id,c16_tipo_tarifa_proceso_id,c2_tipo_base_imponible_id,i2_rango,d82_valor,d82_excedente,d82_divisor,d82_rango_minimo,d82_rango_maximo FROM sdd_tarifas ORDER BY c16_tarifa_id";
    my $sth = $dbh->prepare($sql);	
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_tarifas_detalles');
	while (my @row = $sth->fetchrow_array) {
		$tarid{$row[0]}{$row[3]} = [@row];
	}
#      print "TARIFAS DETALLES\n<br>";      
#      foreach $k (sort keys %tarid){
#	    foreach $kk (sort keys %{$tarid{$k}}){
#                  print "$k=>$kk=>$tarid{$k}{$kk}[4]<br>";
#            }
#	    print "\n<br>";
#      }      
      return %tarid;
}
sub get_accesorios {
	my %acce	= ();
	my $sql = "SELECT c8_tributo_id,c8_accesorio_id,dt_desde,dt_hasta FROM sdd_tributo_accesorios";
	my $sth = $dbh->prepare($sql);	
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_accesorios');
	while (my @row = $sth->fetchrow_array) {
		$acce{$row[0]}{$row[1]}=[@row];
	}
#      print "ACCE\n<br>";      
#      foreach $k (sort keys %acce){
#	    foreach $kk (sort keys %{$acce{$k}}){
#                   print "$k=>$kk=><>$acce{$k}{$kk}[2]<br>";
#            }
#	    print "\n<br>";
#      } 
      return %acce;
}
sub get_servicios {
	my %serv = ();
	my $sql = "SELECT i2_grupo_id,c8_tributo_id,c1_tipo_tarifa_id,c2_codigo_id,dt_desde,dt_hasta  FROM sdd_".$clase."_servicios WHERE i8_".$clase."_id='$id' and c2_gravable='SI' and dt_desde<='$dt_actual' and dt_hasta>='$dt_actual' ORDER BY 1,2,3";
    my $sth = $dbh->prepare($sql);	
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_servicios');
	while (my @row = $sth->fetchrow_array) {
		$serv{$row[0]}{$row[1]} = [@row];
	}
	return %serv;
}
sub get_bases {
	my %base	 = ();
	my $sql = "SELECT i2_grupo_id,c2_tipo_base_imponible_id,f4_total,f4_deducible,i2_tipo_nivel_id,i2_divisor,dt_desde
			   FROM sdd_".$clase."_bases_imponibles
			   WHERE i8_".$clase."_id='$id'
			   ORDER BY dt_desde ASC";
    my $sth = $dbh->prepare($sql);	
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_bases');
	while (my @row = $sth->fetchrow_array) {
		$base{$row[0]}{$row[1]} = [@row];
	}
	#      print "BASES\n<br>";      
	#      foreach my $k (sort keys %base){
	#	    foreach my $kk (sort keys %{$base{$k}}){
	#            	    foreach my $kkk (sort keys %{$base{$k}{$kk}}){
	#                           print "$k=>$kk=>$kkk=><>$base{$k}{$kk}{$kkk}[3]<br>";
	#            	    }
	#            }
	#	    print "\n<br>";
	#      } 
	return %base;
}
sub put_cargos {
	my %hash = ();
	foreach my $k (sort keys %servicios){
		foreach my $kk (sort keys %{$servicios{$k}}){
			my $tip                     = $servicios{$k}{$kk}[2];
			my $cod                     = $servicios{$k}{$kk}[3];
			($tar_cod,$base_imponible)  = get_base_imponible($k,$kk,$tip,$cod);
			$hash{$tar_cod}{'VAL'} 	= sprintf("%.2f",$base_imponible); 
			$hash{$tar_cod}{'FIE'} 	= get_fiesta($tar_cod,$base_imponible);
		}
	}
	#print "CARGOS<br>\n";      
	#foreach $k (sort keys %hash){
	#	foreach $kk (sort keys %{$hash{$k}}){
	#			print "$k=>$kk=>$hash{$k}{$kk}<br>\n";
	#	}
	#	print "\n";	    
	#}      
    return %hash;
}
sub put_compensados {
	my %hash = ();
	my @pers  = split(/\~~/,$Compensar);
	for(my $i=0;$i<=$#pers;$i++){
        my @tribs	= split(/\|/,$pers[$i]);
		my $periodo	= $tribs[0];
		my $dias	= $tribs[1];
		my $etiqueta= $tribs[2];
		my $tarifa	= $tribs[3];
		my $valor	= $tribs[4];
		my $fiesta	= $tribs[5];
		my $interes	= $tribs[6];
		my $multa	= $tribs[7];
		my $tributo = substr($tarifa,0,8);
		$hash{$periodo}{$tributo}{'dias'}	= $dias;
		$hash{$periodo}{$tributo}{'etiqueta'}= $etiqueta;		
		$hash{$periodo}{$tributo}{'tarifa'}	= $tarifa;
		$hash{$periodo}{$tributo}{'valor'}	= $valor;		
		$hash{$periodo}{$tributo}{'fiesta'}	= $fiesta;
		$hash{$periodo}{$tributo}{'interes'}= $interes;
		$hash{$periodo}{$tributo}{'multa'} 	= $multa;		
	}
	return %hash;	
}
sub put_acompensar {
	my %ACom	= ();
	my %hash	= ();
	my %hash1	= ();
	my %hash2	= ();
	my %hash3	= ();
	my $credit  = 0;
	my $cargo   = 0;
	my $meses	= 0;
	my $last_per= "";
	my $per 	= "";	
	my @pers	= split(/\~~/,$ACompensar);
	for(my $i=0;$i<=$#pers;$i++){
        my @tribs	= split(/\|/,$pers[$i]);
		my $periodo	= $tribs[0];
		my $dias	= $tribs[1];
		my $etiqueta= $tribs[2];
		my $tarifa	= $tribs[3];
		my $valor	= $tribs[4];
		my $fiesta	= $tribs[5];
		my $interes	= $tribs[6];
		my $multa	= $tribs[7];
		my $tributo = substr($tarifa,0,8);
		$ACom{$periodo}{$tributo}{'dias'}	= $dias;
		$ACom{$periodo}{$tributo}{'etiqueta'}= $etiqueta;		
		$ACom{$periodo}{$tributo}{'tarifa'}	= $tarifa;
		$ACom{$periodo}{$tributo}{'valor'}	= $valor;		
		$ACom{$periodo}{$tributo}{'fiesta'}	= $fiesta;
		$ACom{$periodo}{$tributo}{'interes'}= $interes;
		$ACom{$periodo}{$tributo}{'multa'} 	= $multa;		
	}
	foreach my $k (sort {$a<=>$b} keys %ACom){
		foreach my $kk (sort keys %{$ACom{$k}}){
			my $val = $ACom{$k}{$kk}{'valor'};
			$hash{$k}{$val}{$kk} = $ACom{$k}{$kk}{'tarifa'};
		}
		$last_per = $k;
	}
	foreach my $k (sort {$a<=>$b} keys %compensados){
		foreach my $kk (sort keys %{$compensados{$k}}){
			my $val = $compensados{$k}{$kk}{'valor'};
			$hash1{$k}{$val}{$kk} = $compensados{$k}{$kk}{'tarifa'};
		}
	}
	foreach my $k (sort {$a<=>$b} keys %hash1){
		my $count	= 0;
		foreach my $kk (sort {$b<=>$a} keys %{$hash1{$k}}){
			foreach my $kkk (sort keys %{$hash1{$k}{$kk}}){
				my @array = ($k,$kkk,$hash1{$k}{$kk}{$kkk},$kk);
				$hash2{$k}{$count} = [@array];
				$count++;
			}
		}
	}
	foreach my $k (sort {$a<=>$b} keys %hash2){
		foreach my $kk (sort keys %{$hash2{$k}}){
			$credit += ($hash2{$k}{$kk}[3])*-1;
		}
	}
	foreach my $k (sort keys %cargos){
		$cargo += $cargos{$k}{'VAL'};
	}
	$meses = sprintf("%.0f",($credit/$cargo));
	$per   = $last_per;
	for(my $i=0;$i<=$meses;$i++){
		my $year 	= substr($per,0,4)*1;
		my $month 	= substr($per,4,2)*1;
		$mes = $month+1;
		if($mes>12){$mes=0;$year++}
		if($mes<10){$month="0".$mes}else{$month=$mes}
		$per = $year.$month;
		foreach my $k (sort keys %cargos){
			$hash{$per}{$cargos{$k}{'VAL'}}{substr($k,0,8)} = $k;
		}	
	}
	foreach my $k (sort {$a<=>$b} keys %hash){
		foreach my $kk (sort {$b<=>$a} keys %{$hash{$k}}){
			foreach my $kkk (sort keys %{$hash{$k}{$kk}}){
				my $val   = $kk;
				my $tarif = $hash{$k}{$kk}{$kkk};
				foreach my $l (sort {$a<=>$b} keys %hash2){
					foreach my $ll (sort {$b<=>$a} keys %{$hash2{$l}}){
						my @array = ();
						my $sus	  = ($hash2{$l}{$ll}[3]*-1);
						if ($val==$sus) {
							@array = ($hash2{$l}{$ll}[0],$hash2{$l}{$ll}[1],$hash2{$l}{$ll}[2],$val,$tarif);
							$hash3{$k}{$kkk}{$hash2{$l}{$ll}[0]}{$hash2{$l}{$ll}[1]} = [@array];
							delete $hash2{$l}{$ll};
							$val = 0;
						}
						if ($val>$sus) {
							@array = ($hash2{$l}{$ll}[0],$hash2{$l}{$ll}[1],$hash2{$l}{$ll}[2],$sus,$tarif);
							$hash3{$k}{$kkk}{$hash2{$l}{$ll}[0]}{$hash2{$l}{$ll}[1]} = [@array];
							delete $hash2{$l}{$ll};
							$val = sprintf("%.2f",($val - $sus));
						}elsif ($val<$sus) {
							@array = ($hash2{$l}{$ll}[0],$hash2{$l}{$ll}[1],$hash2{$l}{$ll}[2],$val,$tarif);
							$hash3{$k}{$kkk}{$hash2{$l}{$ll}[0]}{$hash2{$l}{$ll}[1]} = [@array];
							$hash2{$l}{$ll}[3] = sprintf("%.2f",($sus-$val)*-1);
							$val = 0;
						}
						if ($val == 0){last}
					}
					if ($val == 0){last}
				}
			}
		}
	}	
	#print "$credit / $cargo = $meses<br>";
	#foreach my $k (sort {$a<=>$b} keys %hash){
	#	foreach my $kk (sort keys %{$hash{$k}}){
	#		foreach my $kkk (sort keys %{$hash{$k}{$kk}}){
	#			print "$k > $kk > $kkk > $hash{$k}{$kk}{$kkk} <br>";
	#		}
	#	}
	#}	
	#foreach my $k (sort {$a<=>$b} keys %hash2){
	#	foreach my $kk (sort keys %{$hash2{$k}}){
	#		print "$k > $kk > $hash2{$k}{$kk}[0] , $hash2{$k}{$kk}[1] , $hash2{$k}{$kk}[2], $hash2{$k}{$kk}[3] <br>";
	#	}
	#}
	#foreach my $k (sort {$a<=>$b} keys %hash3){
	#	foreach my $kk (sort keys %{$hash3{$k}}){
	#		foreach my $kkk (sort {$a<=>$b} keys %{$hash3{$k}{$kk}}){
	#			foreach my $kkkk (sort keys %{$hash3{$k}{$kk}{$kkk}}){
	#				print "$k > $kk > $kkk > $kkkk > $hash3{$k}{$kk}{$kkk}{$kkkk}[0] , $hash3{$k}{$kk}{$kkk}{$kkkk}[1] , $hash3{$k}{$kk}{$kkk}{$kkkk}[2],  $hash3{$k}{$kk}{$kkk}{$kkkk}[3] <br>";
	#			}
	#		}
	#	}
	#}
	return %hash3;	
}
sub insert_compensados {
	foreach my $k (sort {$a<=>$b} keys %compensados){
		foreach my $kk (sort keys %{$compensados{$k}}){
			my $recibo = $k.$kk;
			my $existe_abono     = verificar_abono($id,$k,$kk);
			if ($existe_abono==0){			
				my $sql="insert into sdd_".$clase."_abonos (i8_".$clase."_id,c8_tributo_id,c16_tarifa_id,i8_periodo,dt_abono,d82_abono,c3_tipo_abono_id,c16_recibo_id,c2_tipo_recibo_id,v64_abono_agencia,v64_abono_sucursal,c1_balance_type,ts_in)
						 values('$id','$kk','$compensados{$k}{$kk}{'tarifa'}','$k','$dt_actual',$compensados{$k}{$kk}{'valor'},'VAL','$recibo','CM','AMLH','CENTRAL','S',now())";
				#print "$sql <br>";				
				$dbh->do($sql);
			}
		}
		#print "<br>\n";	    
	} 	
}
sub insert_acompensar {
	foreach my $k (sort {$a<=>$b} keys %acompensar){
		foreach my $kk (sort keys %{$acompensar{$k}}){
			foreach my $kkk (sort {$a<=>$b} keys %{$acompensar{$k}{$kk}}){
				foreach my $kkkk (sort keys %{$acompensar{$k}{$kk}{$kkk}}){
					#print "$k > $kk > $kkk > $kkkk > $acompensar{$k}{$kk}{$kkk}{$kkkk}[0] , $acompensar{$k}{$kk}{$kkk}{$kkkk}[1] , $acompensar{$k}{$kk}{$kkk}{$kkkk}[2],  $acompensar{$k}{$kk}{$kkk}{$kkkk}[3] <br>";
					if ($acompensar{$k}{$kk}{$kkk}{$kkkk}[3]>0){
						my $periodo = $acompensar{$k}{$kk}{$kkk}{$kkkk}[0];
						my $tributo = $acompensar{$k}{$kk}{$kkk}{$kkkk}[1];
						my $tarifa  = $acompensar{$k}{$kk}{$kkk}{$kkkk}[2];
						my $valor	= $acompensar{$k}{$kk}{$kkk}{$kkkk}[3];
						my $tarif	= $acompensar{$k}{$kk}{$kkk}{$kkkk}[4];						
						my $recibo  = $periodo.$tributo;
						my $sql = "insert into sdd_".$clase."_abonos (i8_".$clase."_id,c8_tributo_id,c16_tarifa_id,i8_periodo,dt_abono,d82_abono,c3_tipo_abono_id,c16_recibo_id,c2_tipo_recibo_id,v64_abono_agencia,v64_abono_sucursal,c1_balance_type,ts_in)
								   values('$id','$kk','$tarif','$k','$dt_actual',$valor,'VAL','$recibo','AC','AMLH','CENTRAL','S',now())";
						#print "$sql <br>";				
						$dbh->do($sql);
					}
				}
			}
		}
		#print "<br>\n";		
	}
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 2
sub get_base_imponible {
	my ($gr,$trb,$tip,$cod)= @_;
	my $imponible  = 0;
	my $tariff_cod = "";
	foreach $l (sort keys %{$tarifas{$trb}{$tip}{$cod}{'M'}}){
		$tariff_cod = $tarifas{$trb}{$tip}{$cod}{'M'}{$l}[0];
		if ($l eq "F"){
			#print "$trb=>$tip=>$cod=>M=>$l<>$tariff_cod=>";
			#print "$tarifas{$trb}{$tip}{$cod}{'M'}{$l}[6]<br>";
			$imponible  = $tarifas{$trb}{$tip}{$cod}{'M'}{$l}[6];
		}
		if ($l eq "C"){
			$imponible = get_tarifa_detalle_valor($tariff_cod,$gr); 
		}
	}
	#print "$tariff_cod<>$imponible<br>";
	return ($tariff_cod,$imponible);
}
sub get_fiesta {
    my ($tarifa,$base_imponible)= @_;
    my $fiesta= 0;
    my $trib  = substr($tarifa,0,8);
    my $desde = substr($accesorios{$trib}{'ACINMFIE'}[2],0,7);
    my $hasta = substr($accesorios{$trib}{'ACINMFIE'}[3],0,7);
    $desde    =~ s/\-//g;
    $hasta    =~ s/\-//g;
    #~print "$i8_periodo<>$desde<>$hasta<br>";
    if($i8_periodo>=$desde && $i8_periodo<=$hasta){
         $fiesta = sprintf("%.2f",$base_imponible*0.07);
    }
    return $fiesta;
}
sub verificar_abono{
	my($exp_id,$periodo,$tributo)=@_;
	my $counter = 0;
	my $sql="select count(*) from sdd_".$clase."_abonos where i8_".$clase."_id='$exp_id' and i8_periodo='$periodo' and c8_tributo_id='$tributo' and c2_tipo_recibo_id='CM'";
    my $sth = $dbh->prepare($sql);	
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_verifar_abono');
	while (my @row = $sth->fetchrow_array) {
		$counter = $row[0];
	}
	return $counter;
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 3
sub get_tarifa_detalle_valor {
      my ($tarifa,$gr)  = @_;
      my $val_trib  = 0;
      my $imponible = 0;
      foreach $k (sort keys %{$tarifas_d{$tarifa}}){
            my $tbp          = $tarifas_d{$tarifa}{$k}[2];
            my $valor        = $tarifas_d{$tarifa}{$k}[4];
            my $excedente    = $tarifas_d{$tarifa}{$k}[5];
            my $divisor      = $tarifas_d{$tarifa}{$k}[6];      
            my $rango_minimo = $tarifas_d{$tarifa}{$k}[7];
            my $rango_maximo = $tarifas_d{$tarifa}{$k}[8];
            my $tipo_rango   = $tarifas_d{$tarifa}{$k}[9];
            $imponible = $bases{$gr}{$tbp}[2]-$bases{$gr}{$tbp}[3];            
            if($tarifas_d{$tarifa}{$k}[1] eq "BXV"){
                  $val_trib += $imponible*$valor;
                  if($tarifas_d{$tarifa}{$k}[7]>0){if($val_trib<$tarifas_d{$tarifa}{$k}[7]){$val_trib=$tarifas_d{$tarifa}{$k}[7]}}
                  if($tarifas_d{$tarifa}{$k}[7]>0){if($val_trib>$tarifas_d{$tarifa}{$k}[8]){$val_trib=$tarifas_d{$tarifa}{$k}[8]}}
            }
            if($tarifas_d{$tarifa}{$k}[1] eq "BVT"){
                  my $calculo = $imponible*$valor;
                  if($calculo > $tarifas_d{$tarifa}{$k}[8]){
                        $calculo = $tarifas_d{$tarifa}{$k}[8];
                  }
                  $val_trib += $calculo;
            }
            if($tarifas_d{$tarifa}{$k}[1] eq "BME"){
                  if ($imponible>$tarifas_d{$tarifa}{$k}[8]){
                        if($excedente ne ""){
                              if($divisor==0 || $divisor eq ""){$divisor=1}
                              $val_trib += $valor+((($imponible-$tarifas_d{$tarifa}{$k}[8])/$divisor)*$excedente);                                                      
                        }
                  }else{
                        $val_trib += $valor;
                  }
            }
            if($tarifas_d{$tarifa}{$k}[1] eq "CR"){
                  if ($imponible>=$tarifas_d{$tarifa}{$k}[7] && $imponible<=$tarifas_d{$tarifa}{$k}[8]){
                        if($excedente ne ""){
                              if($divisor==0){$divisor=1}
                              my $resultado = (($imponible-$tarifas_d{$tarifa}{$k}[7])/$divisor);
                              my $entero    = int($resultado);
                              my $decimal   = sprintf("%.2f", ($resultado - int($resultado)));
                              if ($decimal>.00){$entero++}
                              $val_trib += $valor+($entero*$excedente);
                        }else{
                              $val_trib += $valor;
                        }
                  }
            }
            if($tarifas_d{$tarifa}{$k}[1] eq "CRPE"){
                  if ($imponible>=$rango_minimo && $imponible<=$rango_maximo){
                        #print "$imponible>$rango_minimo>$rango_maximo<br>";
                        if($excedente ne ""){
                              if($tipo_rango eq "RE"){
                                    $val_trib = $valor+($imponible*$excedente);
                              }else{
                                    if($divisor==0){$divisor=1}
                                    my $resultado = (($imponible-$rango_minimo)/$divisor);
                                    my $entero    = int($resultado);
                                    my $decimal   = sprintf("%.2f", ($resultado - int($resultado)));
                                    if ($decimal>.00){$entero++}
                                    $val_trib += $valor+($entero*$excedente);
                              }
                        }else{
                              $val_trib += $valor;
                        }
                  }
            }
      }
      return $val_trib;
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub SQLfail {
    my ($sql,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$sql."<br>";    
    exit;
}
sub get_fecha_hoy {
	my $fec  	= "";
	my $sql 	= "SELECT DATE_FORMAT(CURDATE(),'%Y-%m-%d')";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_fecha_hoy');
    if (my @row = $sth->fetchrow_array) {
		$fec 	  = $row[0];
	}	
	return $fec;	
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
