#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2013-01-21
# Updates: 2013-01-21,2016-03-21
# Author:Rodolfo Machon
# File Name: mora_sectores_rept.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# 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');
$id			= $cgiparms->param('id');
#-------------------------------------------------------------------------

#-------------------------------------------------------------------------
# CHECK PARAMETERS
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# OPEN DATABASE CONNECTIONS - ODBCs
get_app_data();
open_conection();
get_component_schema();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH 	 = "../".$lan."/";
$file    = $lan."_expediente.tarjeta_rept.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
$dt_vencimiento			= get_fecha_actual();
$generales              = get_generales();
$contribuyente			= get_contribuyente();
%bases_imponibles       = ();
%accesorios_periodo     = ();
%tariffs_labels         = ();
%tarifa_desde_hasta     = ();      
%tributos				= get_tributos();
%tarifas 				= get_tarifas();
%accesorios				= get_accesorios();
%bases					= get_bases();
%cargos					= get_cargos();
#%abonos					= get_abonos();
%recibos				= get_abono_recibos();
%periodo_recibo			= get_periodo_recibo();

$table 		   = construct_table();
construct_page();
exit;
#-------------------------------------------------------------------------
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.857";}
		if($i2_digitos eq ""){$i2_digitos = "2";}
		if($c8_dia_cargo eq ""){$c8_dia_cargo = "1";}
    }
	$generales = $i2_dias_mora."|".$d82_multa_minima."|".$i2_digitos;
}
sub get_contribuyente {
	my $str ="";
	my $sql	= "SELECT a.v128_contribuyente FROM sdm_contribuyentes a INNER JOIN sdd_".$clase."_contribuyentes b ON b.i8_contribuyente_id=a.i8_contribuyente_id WHERE b.i8_".$clase."_id='".$id."'";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_renerales');
	if (my @row = $sth->fetchrow_array) {
		$str = $row[0];
	}
	return $str;
}
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";
	#print $sql;
	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];
	}
	return %tribu;
}
sub get_tarifas {
	my %tari		= ();
	my %tari1		= ();      
	my %tari2		= ();
	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 $code	= $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='$code' 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;
				$tariffs_labels{$row[0]}=$row[6].",".$row[7];
				$jj++;
			}
			$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;
					}
				}
			}
		}
	}
	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;
				$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'};
			}
		}
	}
	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" || $composicion_tarifa_id eq "P"){	    
				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 $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;
					}			      
				}
			}
		}
	}      
	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'";
					#print "$sql <br>";
					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'";
						#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 $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;
									}
								}
							}
						}
					}
				}
			}
		}
	}
	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'} = round($bases{$k}{$kk}{$kkk},$i2_digitos);}
							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'} = round(($bases{$k}{$kk}{$kkk}*$dias_mes),$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)<=$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"});
								}
							} 
						}
					}
				}
			}
		}
    }
    return %cargo;
}
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'";
	#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[3]} += $row[2];
		$balance_type{$row[0]}{$row[1]}{$row[3]} = $row[4];
	}
	  return %abono;
}
sub get_abono_recibos {
	my %hash	= ();
	my $sql		= "SELECT c16_recibo_id,SUM(d82_abono) as 'pago',dt_abono FROM sdd_".$clase."_abonos WHERE i8_".$clase."_id='$id' AND c3_tipo_abono_id='VAL' GROUP BY c16_recibo_id";
	#print $sql."<br>";
	my $sth 	= $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_abono_recibos');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]} = [@row];
	}
	return %hash;
}
sub get_periodo_recibo {
	my %hash	= ();
	my $recibos = "";
	foreach my $k (sort keys %recibos){$recibos .= "'".$k."',"	}
	$recibos = substr($recibos,0,length($recibos)-1);
	if ($recibos eq ""){$recibos = "'0'";}
	my $sql		= "SELECT MAX(i8_periodo),c16_recibo_id FROM sdd_".$clase."_abonos WHERE c16_recibo_id IN (".$recibos.") GROUP BY c16_recibo_id";
	#print $sql."<br>";
	my $sth 	= $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_abono_recibos');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]} = $row[1];
	}
	return %hash;
}
#-------------------------------------------------------------------------
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) = @_;
	  #print "$lest_recent_date <> $most_recent_date <br>";
      $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\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;}

      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_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 get_dia_cargo {
      my ($year,$month) = @_;
      my $dias=1;
      if($c8_dia_cargo eq "LAST" ||$c8_dia_cargo eq "ULTIMO"){$dias = Days_in_Month($year,$month)}else{$dias=$c8_dia_cargo*1}
      return $dias;
}
#SUB-ROUTINES -> LEVEL 1
sub construct_table {
    my $count   = 1;
    my $table   = "";
    my $rowset  = "";
	my $t_cargo = 0;
	my $t_abono = 0;
	my $t_saldo = 0;
	my $inicial = 0;
    my $thead   = "<thead><tr><td>PERIODO</td><td>ID</td><td>TRIBUTO</td><td>CARGO</td><td>ABONOS</td><td>SALDO</td></tr></thead>";
    my $tfoot   = "<tfoot><tr><td colspan=3>TOTAL</td><td>{t_cargo}</td><td>{t_abono}</td><td>{t_saldo}</td></tr></tfoot>";

	my %periodos= ();	
    foreach my $k (sort keys %cargos){$periodos{$k}=$k;}
	foreach my $k (sort keys %recibos){
		my $fecha = $recibos{$k}[2];
		my $hoy   = $recibos{$k}[2];
		$hoy =~ s/\-//g;
		$per = substr($hoy,0,6);
		$periodos{$per}=$fecha;
	}
	
    foreach my $k (sort {$a<=>$b} keys %periodos){
		#print "$k <br>";
		foreach $kk (sort keys %{$cargos{$k}}){
			my $tributo = get_tarifa($kk);
			my $cargo = round($cargos{$k}{$kk}{'VAL'},2);
			if($cargo>0){
				my $saldo = $inicial+$cargo;
				my $row   = "	<td>".$k."</td>
								<td>".$kk."</td>
								<td>".$tributo."</td>
								<td>".round($cargo,2)."</td>
								<td></td>
								<td>".round($saldo,2)."</td>							
							";
				$rowset  	.= "<tr>".$row."</tr>";
				$t_cargo   	+= $cargo;
				$inicial = $saldo;				
			}
		}
		foreach my $l (sort keys %recibos){
			my $fecha = $recibos{$l}[2];
			my $rec_per = $fecha;
			$rec_per =~ s/\-//g;
			$rec_per = substr($rec_per,0,6);
			if($rec_per eq $k){
				my $pago = round($recibos{$l}[1],2);
				my $desc = "Recibo No. ".$l." de Fecha ".$fecha;
				$rowset .=  "<tr>
								<td colspan>".$k."</td>
								<td>".$desc."</td>
								<td></td>
								<td> - </td>
								<td>".$pago."</td>
								<td>".round($inicial-$pago,2)." </td>							
							 </tr>
							";
				$t_abono	+= $pago;
				$inicial=round($inicial-$pago,2);
			}			
		}
    }
	$t_saldo = round(($t_cargo-$t_abono),2);
	$t_cargo = round(($t_cargo),2);
	$t_abono = round(($t_abono),2);	
    $tfoot     =~ s/\{t_cargo}/$t_cargo/g;
    $tfoot     =~ s/\{t_abono}/$t_abono/g;
    $tfoot     =~ s/\{t_saldo}/$t_saldo/g;
    $rowset    = "<tbody>".$rowset."</tbody>";
    $table     = "<table id=grid>".$thead.$rowset.$tfoot."</table>";
    return $table;
}
sub construct_page {
    open (DATA, "$PATH$file");
    while ($line = <DATA>) {	
        $line =~ s/\{lan}/$lan/g;
        $line =~ s/\{id}/$id/g;
		$line =~ s/\{contribuyente}/$contribuyente/g;
        $line =~ s/\{appid}/$appid/g;
		$line =~ s/\{city}/$city/g;
		$line =~ s/\{state}/$state/g;
        $line =~ s/\{clase}/$clase/g;         
        $line =~ s/\{table}/$table/g;
        print "$line";
    }
}
#-------------------------------------------------------------------------
#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};
	$city	  = $data->{portal}->{city};
	$state    = $data->{portal}->{state};
}
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}
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 get_component_schema {
    my  $schema     = $clase."_bsws.xml";
    my  $xml        = new XML::Simple;
      $data 	  = $xml->XMLin("../xml/".$schema);
      $path	   = $data->{base_path}."/libraries/html/";
      $file	   = "bsws.html";
      $dt_desde	  = "";
      $dt_hasta	  = "";      
}
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;
}
sub get_tarifa {
	my ($tid)=@_;
    my $sql = "SELECT v128_tarifa FROM sdm_tarifas WHERE c16_tarifa_id='".$tid."'";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_tarifa');
	if (my @row = $sth->fetchrow_array) {
		return ($row[0]);
	}
}
#-------------------------------------------------------------------------
