#!/perl/bin/perl.exe
$|=1;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# Information Software SA
# Date In: 2013-01-19
# Updates: 2013-01-19,2016-03-14
# Author:Rodolfo Machon
# File Name: moras_generar_p.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use XML::Simple;
use Data::Dumper;
use DBI;
use CGI::Carp qw(fatalsToBrowser);
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);
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html; charset=utf-8\n\n";
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
#GET PARAMETERS
$cgiparms 	= new CGI;
$lan	  		= $cgiparms->param('lan');
$appid	  		= $cgiparms->param('appid');
$clase	  		= $cgiparms->param('clase');
$table	  		= $cgiparms->param('table');
$dt_vencimiento	= $cgiparms->param('dt_vencimiento');
$dt_vencimiento	=~ s/\-//g;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($lan eq ""){print "Your are not passing LAN parameter<br>";}
if ($appid eq ""){print "Your are not passing APPID parameter<br>";}
if ($clase eq ""){print "Your are not passing CLASE parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH 	= "../".$lan."/";
$file   = "es_moras_generar.html";          
$count  = 0;
$a		= 0;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
get_component_schema($clase);
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# OPEN DATABASE CONNECTIONS - ODBCs
$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->{LongTruncOk} = 1;
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
table_exists();
print "0. Check Table.....<br>\n";
delete_cargo_from_database();
print "1. Delete ........<br>\n";
get_generales();
print "2. Generales ......<br>\n";
%expedientes= get_expedientes();
print "3. Expedientes.....<br>";
%servicios	= get_tributos();
print "4. Servicios ......<br>\n";
%bases		= get_bases();
print "5. Bases ..........<br>";
%inicios	= get_inicios_cobros();
print "6. Inicios ........<br>\n";
%accesorios	= get_accesorios();
print "7. Accesorios......<br>\n";
%tarifas    = get_tarifas();
print "8. Tarifas ........<br>\n";
%tarifas_d  = get_tarifas_detalles();
print "9. Tarifas Detalles<br>\n";
%cargos_a   = get_cargos_a_cuenta();
print "10. Cargos a Cuenta<br>\n";
# INTEGRATION
print "100000.";
foreach my $id (sort keys %expedientes){
	my %cargos 	= get_cargos($id);
	my %abonos 	= get_abonos($id);	
	my %balance = get_balance($id,\%cargos,\%abonos);
	insert_cargo_into_database(%balance);
	
	if($count<=1000){print ".";}else{print "<br>Registros: $a >> ID Procesado: $id";$count=0}
	$count++;
	$a++;
}
# PRESENTATION
print "<br>FIN<br>";
#construct_page();
exit;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub table_exists {
	my $tab   = "NO_EXISTE";
	my $sql   = "SHOW TABLES LIKE '$table'";
	my $sth   = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub table_exists');
	if (my @row = $sth->fetchrow_array) {
		$tab    = $row[0];
	}
	if($tab eq "NO_EXISTE"){
		my $sql = "CREATE TABLE $table (
									i8_".$clase."_id int(8) DEFAULT NULL,
									i8_periodo int(8) DEFAULT NULL,
									i4_dias_mora int(4) DEFAULT NULL,
									c16_tarifa_id varchar(16) DEFAULT NULL,
									d82_val decimal(12,2) DEFAULT NULL,
									d82_fie decimal(12,2) DEFAULT NULL,
									d82_int decimal(12,2) DEFAULT NULL,
									d82_mul decimal(12,2) DEFAULT NULL,
									KEY i8_".$clase."_id (i8_".$clase."_id),
									KEY i8_periodo (i8_periodo),
									KEY c16_tarifa_id (c16_tarifa_id),
									KEY i4_dias_mora (i4_dias_mora),
									KEY d82_val (d82_val)
								) ENGINE=MyISAM DEFAULT CHARSET=utf8;		
					";
			$dbh->do($sql);
	}
}
sub delete_cargo_from_database {
	my $sql = "DELETE FROM $table";
	$dbh->do($sql);
}
sub get_generales {
    my $sql	= "SELECT i4_parametro_id,i2_dias_mora,d82_multa_minima,i2_digitos,dt_parametro,c8_dia_cargo FROM sdm_parametros ORDER BY 5 DESC";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_generales');
	if (my @row = $sth->fetchrow_array) {
		$i2_dias_mora		= $row[1];
		$d82_multa_minima	= $row[2];
		$i2_digitos 		= $row[3];
		$c8_dia_cargo 		= "\U$row[5]";
	}else{
		$i2_dias_mora 		= "60";
		$d82_multa_minima 	= "2.86";
		$i2_digitos 		= "2";
		$c8_dia_cargo 		= "1";
    }
}
sub get_expedientes {
	my %hash= ();
	my $sql	= "SELECT i8_".$clase."_id FROM sdm_".$clase."s WHERE c16_estado='ACEPTADO' ORDER BY 1";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_expedientes');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]}	= $row[0];
	}
	return %hash;
}
sub get_tributos {
    my %tribu		= ();
    my $sql = "SELECT i8_".$clase."_id,i2_grupo_id,c8_tributo_id,c1_tipo_tarifa_id,c2_codigo_id,dt_desde,dt_hasta FROM sdd_".$clase."_servicios WHERE c2_gravable='SI'";
	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[2]} = [@row];
    }
    return %tribu;
}
sub get_bases {
    my %base	 = ();
    my $sql = "SELECT i8_".$clase."_id,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";
	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[2]} = [@row];
    }
    return %base;
}
sub get_inicios_cobros {
    my %inicio = ();
    my $sql  = "SELECT i8_".$clase."_id,c8_tributo_id,dt_cargo_desde,dt_cargo_hasta FROM sdd_".$clase."_cargo_inicios";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_inicios_cobros');
	while (my @row = $sth->fetchrow_array) {
		$inicio{$row[0]}{$row[1]}{'DESDE'} = $row[2];
		$inicio{$row[0]}{$row[1]}{'HASTA'} = $row[3];
    }
    return %inicio;
}
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 1');
	while (my @row = $sth->fetchrow_array) {
		$acce{$row[0]}{$row[1]}=[@row];
    }
    return %acce;
}
sub get_tarifas {
    my $j    = 0;
	my $str  = "";
	my %tri  = ();
    my %tar  = ();
    my %tari = ();
    foreach my $k (sort keys %servicios){
		foreach my $kk (sort keys %{$servicios{$k}}){
			foreach my $kkk (sort keys %{$servicios{$k}{$kk}}){
				$tri{$kkk} = $kkk;
			}
		}
	}
	foreach my $k (sort keys %tri){$str .= "'".$k."',"}
    $str = substr($str,0,length($str)-1);
    my $sql	= "SELECT c8_tributo_id,c16_tarifa_id,c1_tipo_tarifa_id,c2_codigo_id,c1_frecuencia_tarifa_id,c1_composicion_tarifa_id,d82_tarifa,dt_desde
				   FROM sdm_tarifas
				   WHERE c8_tributo_id IN ($str)
				   ORDER BY c16_tarifa_id,dt_desde";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_tarifas');
	while (my @row = $sth->fetchrow_array) {
		$tari{$row[0]}{$row[2]}{$row[3]}{$j} = [@row];
		$j++;
    }
    foreach my $k (sort keys %tari){
		foreach my $kk (sort keys %{$tari{$k}}){
			foreach my $kkk (sort keys %{$tari{$k}{$kk}}){
				foreach my $kkkk (sort {$a<=>$b} keys %{$tari{$k}{$kk}{$kkk}}){
					$tar{$k}{$tari{$k}{$kk}{$kkk}{$kkkk}[1]}{'DATA'}  = $tari{$k}{$kk}{$kkk}{$kkkk};
					$tar{$k}{$tari{$k}{$kk}{$kkk}{$kkkk}[1]}{'DESDE'} = $tari{$k}{$kk}{$kkk}{$kkkk}[7];
					my $next_tar = $kkkk+1;
					if($tari{$k}{$kk}{$kkk}{$next_tar}[7]){
						$tar{$k}{$tari{$k}{$kk}{$kkk}{$kkkk}[1]}{'HASTA'} = $tari{$k}{$kk}{$kkk}{$next_tar}[7];
					}else{
						$tar{$k}{$tari{$k}{$kk}{$kkk}{$kkkk}[1]}{'HASTA'} = $dt_vencimiento
					}
				}
			}
		}
    }    
    return %tar;
}
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,c2_tipo_rango_id 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];
    }
    return %tarid;
}
sub get_cargos_a_cuenta {
	my %hash = ();
    my $sql	 = "SELECT i8_".$clase."_id,i8_periodo,c16_tarifa_id,d82_cargo FROM  sdd_".$clase."_cargos WHERE c2_tipo_mandamiento_id='>DE'";
	my $sth  = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_cargos_a_cuenta');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]}{$row[1]}{$row[2]}{'PER'} = $row[1];
		$hash{$row[0]}{$row[1]}{$row[2]}{'VAL'} = $row[3];
		$hash{$row[0]}{$row[1]}{$row[2]}{'DIM'} = get_dias_mora($row[1],$dt_vencimiento);
    }
	return %hash;
}
sub get_cargos {
	my ($id)  = @_;
	my %cargo = ();
	foreach my $k (sort keys %{$servicios{$id}}){
		foreach my $kk (sort keys %{$servicios{$id}{$k}}){
			my $desde_cobro	= "";
			my $hasta_cobro	= "";
			my $tip_tri    	= $servicios{$id}{$k}{$kk}[3];
			my $cod_tri    	= $servicios{$id}{$k}{$kk}[4];
			if($inicios{$id}{$kk}{'DESDE'}){$desde_cobro=$inicios{$id}{$kk}{'DESDE'}}else{$desde_cobro=$servicios{$id}{$k}{$kk}[5]}
			if($inicios{$id}{$kk}{'HASTA'}){$hasta_cobro=$inicios{$id}{$kk}{'HASTA'}}else{$hasta_cobro=$servicios{$id}{$k}{$kk}[6]}
			$desde_cobro 	=~ s/\-//g;
			$hasta_cobro 	=~ s/\-//g;
			foreach my $kkk (sort keys %{$tarifas{$kk}}){
				my $tip_tar    		  = $tarifas{$kk}{$kkk}{'DATA'}[2];
				my $cod_tar    		  = $tarifas{$kk}{$kkk}{'DATA'}[3];					
				my $frecuencia_tarifa = $tarifas{$kk}{$kkk}{'DATA'}[4];
				my $composicion_tarifa= $tarifas{$kk}{$kkk}{'DATA'}[5];				
				my $desde_tarifa	  = $tarifas{$kk}{$kkk}{'DESDE'};
				my $hasta_tarifa	  = $tarifas{$kk}{$kkk}{'HASTA'};
				if($tip_tri eq $tip_tar && $cod_tri eq $cod_tar){
					$desde_tarifa 		  =~ s/\-//g;
					$hasta_tarifa 		  =~ s/\-//g;					
					my @periodos	 	  = get_periodos($desde_tarifa,$hasta_tarifa);
					for(my $p=0;$p<=$#periodos;$p++){
						if(substr($desde_cobro,0,6)<=$periodos[$p] && substr($hasta_cobro,0,6)>=$periodos[$p]){
							my $base_imponible = 0;
							if($composicion_tarifa eq "F"){
								$base_imponible	= $tarifas{$kk}{$kkk}{'DATA'}[6]
							}else{
								$base_imponible	= get_tarifa_detalle_valor($kkk,$id,$k);
							}
							if($frecuencia_tarifa eq "M"){
								$cargo{$id}{$periodos[$p]}{$kkk}{'VAL'} = round($base_imponible,2);
							}
							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{$id}{$periodos[$p]}{$kkk}{'VAL'} = round($base_imponible*$dias_mes,2);
							}
							$cargo{$id}{$periodos[$p]}{$kkk}{'PER'}	= $periodos[$p];
							$cargo{$id}{$periodos[$p]}{$kkk}{'DIM'}	= get_dias_mora($periodos[$p],$dt_vencimiento);
							$cargo{$id}{$periodos[$p]}{$kkk}{'FIE'} = get_tarifa_accesorio($periodos[$p],'FIE','0.05',$cargo{$id}{$periodos[$p]}{$kkk}{'VAL'},$cargo{$id}{$periodos[$p]}{$kkk}{'DIM'});
							$cargo{$id}{$periodos[$p]}{$kkk}{'INT'} = get_tarifa_accesorio($periodos[$p],'INT','0.08',$cargo{$id}{$periodos[$p]}{$kkk}{'VAL'},$cargo{$id}{$periodos[$p]}{$kkk}{'DIM'});
							$cargo{$id}{$periodos[$p]}{$kkk}{'MUL'} = get_tarifa_accesorio($periodos[$p],'MUL','MUL89=0.05|MUL90=0.10',$cargo{$id}{$periodos[$p]}{$kkk}{'VAL'},$cargo{$id}{$periodos[$p]}{$kkk}{'DIM'});
							#print $cargo{$id}{$periodos[$p]}{$kkk}{'VAL'}."<br>";
						}
					}
				}
			}	    
		}
	}
    return %cargo;
}
sub get_abonos {
    my ($id)  = @_;
	my %abono = ();
	my $sql = "SELECT i8_".$clase."_id,i8_periodo,c16_tarifa_id,c3_tipo_abono_id,c1_balance_type,d82_abono FROM sdd_".$clase."_abonos WHERE i8_".$clase."_id='$id'";
	#print "$sql <br>";
	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[2]}{$row[3]}{'VAL'} += $row[5];
		$abono{$row[0]}{$row[1]}{$row[2]}{$row[3]}{'TYP'}  = $row[4];
	}
	return %abono;
}
sub get_balance {
	my ($id,$cargos_h,$abonos_h) = @_;
	my %cargos	= %$cargos_h;
	my %abonos	= %$abonos_h;
	my %bal	 	= ();
    my $balance_tipo	= "\U$data->{clase}->{balance_type}";
    if($balance_tipo eq ""){$balance_tipo = "ABONO";}	
	foreach my $k (sort keys %{$cargos{$id}}){
		foreach my $kk (sort keys %{$cargos{$id}{$k}}){
			foreach my $kkk (sort keys %{$cargos{$id}{$k}{$kk}}){
				#print "$k > $kk > $kkk >".$cargos{$id}{$k}{$kk}{$kkk}." <br>";
				if($balance_tipo eq "ABONO"){
					if(!$abonos{$id}{$k}{$kk}{$kkk}){
						$bal{$id}{$k}{$kk}{$kkk} = $cargos{$id}{$k}{$kk}{$kkk}
					}					
				}
				if($balance_tipo eq "SALDO"){
					if(!$abonos{$id}{$k}{$kk}{$kkk}){
						$bal{$id}{$k}{$kk}{$kkk} = $cargos{$id}{$k}{$kk}{$kkk}
					}else{
						$bal{$id}{$k}{$kk}{$kkk} = $cargos{$id}{$k}{$kk}{$kkk}-$abonos{$id}{$k}{$kk}{$kkk}{'VAL'}
					}										
				}
				if($balance_tipo eq "BANDERA"){
					if(!$abonos{$id}{$k}{$kk}{$kkk}){
						$bal{$id}{$k}{$kk}{$kkk} = $cargos{$id}{$k}{$kk}{$kkk}
					}else{
						if($abonos{$id}{$k}{$kk}{$kkk}{'TYP'} eq "BANDERA"){
							$bal{$id}{$k}{$kk}{$kkk} = $cargos{$id}{$k}{$kk}{$kkk}
						}else{
							$bal{$id}{$k}{$kk}{$kkk} = $cargos{$id}{$k}{$kk}{$kkk}-$abonos{$id}{$k}{$kk}{$kkk}{'VAL'}
						}
					}
				}
			}
		}
	}
	foreach my $k (sort keys %{$cargos_a{$id}}){
		foreach my $kk (sort keys %{$cargos_a{$id}{$k}}){
			foreach my $kkk (sort keys %{$cargos_a{$id}{$k}{$kk}}){
				if($balance_tipo eq "ABONO"){
					if(!$abonos{$id}{$k}{$kk}{$kkk}){
						$bal{$id}{$k}{$kk}{$kkk} = $cargos_a{$id}{$k}{$kk}{$kkk}
					}					
				}
				if($balance_tipo eq "SALDO"){
					if(!$abonos{$id}{$k}{$kk}{$kkk}){
						$bal{$id}{$k}{$kk}{$kkk} = $cargos_a{$id}{$k}{$kk}{$kkk}
					}else{
						$bal{$id}{$k}{$kk}{$kkk} = $cargos_a{$id}{$k}{$kk}{$kkk}-$abonos{$id}{$k}{$kk}{$kkk}{'VAL'}
					}										
				}
				if($balance_tipo eq "BANDERA"){
					if(!$abonos{$id}{$k}{$kk}{$kkk}){
						$bal{$id}{$k}{$kk}{$kkk} = $cargos_a{$id}{$k}{$kk}{$kkk}
					}else{
						if($abonos{$id}{$k}{$kk}{$kkk}{'TYP'} eq "BANDERA"){
							$bal{$id}{$k}{$kk}{$kkk} = $cargos_a{$id}{$k}{$kk}{$kkk}
						}else{
							$bal{$id}{$k}{$kk}{$kkk} = $cargos_a{$id}{$k}{$kk}{$kkk}-$abonos{$id}{$k}{$kk}{$kkk}{'VAL'}
						}
					}
				}
			}
		}
	}
	#foreach my $k (sort keys %bal){
	#	foreach my $kk (sort keys %{$bal{$k}}){
	#		foreach my $kkk (sort keys %{$bal{$k}{$kk}}){
	#			foreach my $kkkk (sort keys %{$bal{$k}{$kk}{$kkk}}){
	#				print $bal{$k}{$kk}{$kkk}{$kkkk}." <br>";
	#			}
	#		}
	#	}
	#}	
	return %bal;
}
sub insert_cargo_into_database {
	my (%balance) = @_;
	my $insert_fields	= "i8_".$clase."_id,i8_periodo,c16_tarifa_id,i4_dias_mora,d82_val,d82_fie,d82_int,d82_mul";
	foreach my $k (sort keys %balance){
		$a++;
		foreach my $kk (sort keys %{$balance{$k}}){
			foreach my $kkk (sort keys %{$balance{$k}{$kk}}){
				my $dim = $balance{$k}{$kk}{$kkk}{'DIM'};
				my $val = $balance{$k}{$kk}{$kkk}{'VAL'};
				my $fie = $balance{$k}{$kk}{$kkk}{'FIE'};				
				my $int = $balance{$k}{$kk}{$kkk}{'INT'};
				my $mul = $balance{$k}{$kk}{$kkk}{'MUL'};
				if($dim eq ""){$dim = 0}				
				if($val eq ""){$val = 0}
				if($fie eq ""){$fie = 0}
				if($int eq ""){$int = 0}
				if($mul eq ""){$mul = 0}
				if($val>0){
					my $query = "INSERT INTO $table ($insert_fields) VALUES ('$k','$kk','$kkk',$dim,$val,$fie,$int,$mul)";
					#print "$query <br>";
					$dbh->do($query);
				}
			}
		}
	}
}
sub construct_page {
	open (DATA, "$PATH$file");
	while ($line = <DATA>) {
		$line =~ s/\{lan}/$lan/g;
		$line =~ s/\{appid}/$appid/g;
		$line =~ s/\{clase}/$clase/g;
		print "$line";
	}
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 2
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;
      $y2  = substr($lest_recent_date,0,4)*1;
      $mo2 = substr($lest_recent_date,4,2)*1;
      $d2  = get_dia_cargo($y2,$mo2);
      $dm  = Delta_Days($y2,$mo2,$d2,$y1,$mo1,$d1);
      my $periodo_gracia = $i2_dias_mora;      
      $dm  = $dm-$periodo_gracia+1;
      if ($dm<0){$dm=0;}
      return $dm;
}
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 = "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_tarifa_detalle_valor {
	my ($tarifa,$id,$gr)  = @_;
	my $val_trib  = 0;
	my $imponible = 0;
	foreach $k (sort keys %{$tarifas_d{$tarifa}}){
		my $proceso		 = $tarifas_d{$tarifa}{$k}[1];
		my $tbp          = $tarifas_d{$tarifa}{$k}[2];
		my $rango        = $tarifas_d{$tarifa}{$k}[3];			
		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{$id}{$gr}{$tbp}[3]-$bases{$id}{$gr}{$tbp}[4];            
		if($proceso 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($proceso eq "BVT"){
			my $calculo = $imponible*$valor;
			if($calculo > $tarifas_d{$tarifa}{$k}[8]){
				$calculo = $tarifas_d{$tarifa}{$k}[8];
			}
			$val_trib += $calculo;
		}
		if($proceso 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($proceso 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   = round(($resultado - int($resultado)),2);
					if ($decimal>.00){$entero++}
					$val_trib += $valor+($entero*$excedente);
				}else{
					$val_trib += $valor;
				}
			}
		}
		if($proceso eq "CRPE"){
			if ($imponible>=$rango_minimo && $imponible<=$rango_maximo){
				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   = round(($resultado - int($resultado),2));
						if ($decimal>.00){$entero++}
						$val_trib += $valor+($entero*$excedente);
					}
				}else{
					$val_trib += $valor;
				}
			}
		}
	}
	return $val_trib;
}
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;
    if($i8_periodo>=$desde && $i8_periodo<=$hasta){
            $fiesta = round(($base_imponible*0.05),2);
    }
    return $fiesta;
}
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;$acce_valor = round($acce_valor,2)}
	if($acce_id eq "INT"){$acce_valor = $dias_mora*$tarifa_valor*($acce_params/365);$acce_valor = round($acce_valor,2)}
	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,2);
	}
	return $acce_valor;
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 3
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}
      #print "DIA_CARGO=$c8_dia_cargo<>DAYS=$days\n";
      return $days;
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub get_component_schema {
	my ($cmp)=@_;
	my $schema  = $cmp."_balance_bsws.xml";
	$xml        = new XML::Simple;
	$data 	  	= $xml->XMLin("../xml/".$schema);
}
sub SQL_fail {
    my ($query,$dbhh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Error: ".$dbhh->Error()."\n<br>";
    print "Query: ".$query."<br>";    
    $dbhh->Close();
    exit;
}
sub connect_fail {
    my ($DSN)=@_;
    print "Error connecting to $DSN<br>";
    print "Error:".Win32::ODBC::Error()."<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;
}
#-------------------------------------------------------------------------
