#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Information Software SA
# Date In: 2006-04-10
# Updates: 2006-04-10, 2009-07-11
# Author:Rodolfo Machon
# File Name: bswsp.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#PERL MODULES
use CGI;
use Win32::ODBC;
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;
#use Math::BigFloat;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#HTML printing activation
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$cgiparms 	        = new CGI;
$lan	 	        = $cgiparms->param('lan');
$appid	 	        = $cgiparms->param('appid');
$clase      	    = $cgiparms->param('clase');
$id		            = $cgiparms->param('id');
#$tp		        = $cgiparms->param('tp');
$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');
$parametros         = $cgiparms->param('parametros');
$dt_vencimiento     =~ s/\-//g;
$dt_futuro 	        =~ s/\-//g;
$dt_corte_hasta     =~ s/\-//g;
$dt_corte_desde     =~ s/\-//g;
#-------------------------------------------------------------------------
#$dt_corte_hasta	  = "20100630";
#$dt_corte_desde	  = "20100401";
#-------------------------------------------------------------------------
# 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>";}
if ($id eq ""){print "Your are not passing ID parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
get_component_schema($clase);
#if (!($dbh_pc = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#MAIN PROGRAM
$parameters_passed      = "no";
%bases_imponibles       = ();
%accesorios_periodo     = ();
%tariffs_labels         = ();
%rtributos 		= ();
%tarifa_desde_hasta     = ();      
$generales              = get_generales();
%tributos		= get_tributos();
%tarifas 		= get_tarifas();
%accesorios		= get_accesorios();
%bases			= get_bases();
%cargos			= get_cargos();
$cargos_a_cuenta        = get_cargos_a_cuenta();
%abonos			= get_abonos();
$balance		= get_balance();
$tributos_labels	= get_tributos_labels();
$accesorios_labels	= get_accesorios_labels();
$tributos_accesorios    = get_tributos_accesorios();
$bases_imponibles       = get_bases_imponibles();
$tariffs                = get_tarifas_labels();
if($parametros eq ""){$parametros = get_parametros();}else{$parameters_passed="yes"}
$grid_header		= get_grid('head');
$grid_body		= get_grid('body');
$grid_footer		= get_grid('foot');
$billing		= get_billing();

construct_page();

exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_generales {
      my $database	= $data->{clase}->{database};
      my $table		= "sdm_parametros";
      my $select_fields	= "i4_parametro_id,i2_dias_mora,d82_multa_minima,i2_digitos,dt_parametro,c8_dia_cargo";
      my $query	= "SELECT $select_fields FROM $table ORDER BY 5 DESC";
      #print "$query<br>\n";
      if (!($dbh = new Win32::ODBC($database))){connect_fail($database)}      
      if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_generales');}
      if($dbh->FetchRow()){
	    undef my @row;
	    @row = $dbh->Data();
            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";}
      }
      $dbh->Close();
      #print "DIA_CARGO=$c8_dia_cargo";
      $generales = $i2_dias_mora."|".$d82_multa_minima."|".$i2_digitos;
}
sub get_tributos {
      my $pair 		= "";
      my $WHERE		= "";
      my %tribu		= ();
      my $database	= $data->{clase}->{charges}->{process}->{tributos}->{database};
      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 $query = "SELECT $select_fields FROM $table $WHERE ORDER BY $order_by";
      #print "$query<br>\n";
      if (!($dbh = new Win32::ODBC($database))){connect_fail($database)}
      if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_tributos');}
      while($dbh->FetchRow()){
	    undef my @row;
	    @row = $dbh->Data();
	    $tribu{$row[0]}{$row[1]} = [@row];
      }
      $dbh->Close();
#      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);
      if (!($dbh = new Win32::ODBC($database))){connect_fail($database)}
      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 $query	= "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";
		  if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_tarifas');}
		  while($dbh->FetchRow()){
			#undef my @row;
			@row = $dbh->Data();
                        $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;
	    }
      }
      $dbh->Close();
      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 $database	= $data->{clase}->{charges}->{process}->{accesorios}->{database};
      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};

      if (!($dbh = new Win32::ODBC($database))){connect_fail($database)}
      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 $query = "SELECT $select_fields FROM $table WHERE $id_field='$tributo'";
		  if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_accesorios 1');}
		  while($dbh->FetchRow()){
			undef my @row;
			@row = $dbh->Data();
			$acceso{$tributo}{$row[0]}=[@row];
                        $cod_acceso = substr($row[0],5,3);
                        $accesorios_periodo{$tributo}{$cod_acceso}=[@row];
		  }
	    }
      }
      $dbh->Close();

      my %acceso_tarifas= ();
      my $database1	= $data->{clase}->{charges}->{process}->{tarifas}->{database};
      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};      
      if (!($dbh1 = new Win32::ODBC($database1))){connect_fail($database1)}
      foreach $key(sort keys %acceso){
	    foreach $key1(sort keys %{$acceso{$key}}){
		  $query = "SELECT $select_fields1 FROM $table1 WHERE $id_field1='$key1' ORDER BY $order_by1 ASC LIMIT 1";
		  if ( $dbh1->Sql($query) ){SQL_fail($query,$dbh1,'sub get_accesorios 2');}
		  if ($dbh1->FetchRow()){
			undef my @row;
			@row = $dbh1->Data();
			$acceso_tarifas{$key}{$key1} = [@row];
		  }
	    }
      }      
      $dbh1->Close();

      my %atc		= ();      
      my $database2	= $data->{clase}->{charges}->{process}->{tarifas_compuestas}->{database};
      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 (!($dbh2 = new Win32::ODBC($database2))){connect_fail($database2)}
      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	= "";
			$query = "SELECT $select_fields2 FROM $table2 WHERE $id_field2='$tarifa_id'";
			if ( $dbh2->Sql($query) ){SQL_fail($query,$dbh2,'sub get_accesorios 3');}
			while ($dbh2->FetchRow()){
			      undef my @row2;
			      @row2 			 = $dbh2->Data();
			      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;
			      }			      
			}
		  }
	    }
      }      
      $dbh2->Close();
#      print "<br>ACCESORIOS\n<br>";
#      foreach $k (sort keys %atc){
#	    foreach $kk (sort keys %{$atc{$k}}){
#			print "$k=>$kk=>$atc{$k}{$kk}\n<br>";
#	    }
#      }      
      return %atc;
}
sub get_bases {
      my %base		= ();
      my %base1		= ();
      my $database	= $data->{clase}->{charges}->{process}->{tarifas_compuestas}->{database};
      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 $database1	= $data->{clase}->{charges}->{process}->{bases}->{database};
      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};

      if (!($dbh = new Win32::ODBC($database))){connect_fail($database)}
      if (!($dbh1 = new Win32::ODBC($database1))){connect_fail($database1)}

      foreach $k (sort keys %tarifas){
	    foreach $kk (sort keys %{$tarifas{$k}}){
                  foreach $kkk (sort keys %{$tarifas{$k}{$kk}}){
                        #my $tarifa_id			= $kkk;
                        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];
                        #-------------------------------------------------------
                        #Esta linea fue agregada para la modificacion de bases.
                        if($composicion_tarifa_id eq "F"){
                              $base{$k}{$kk}{$kkk} += $valor_tarifa;
                              #print "$key=>$key1=>$per=>$base{$key}{$key1}{$per}<br>\n";
                        }
                        #-------------------------------------------------------
                        if($composicion_tarifa_id eq "C"){
                              my $query = "SELECT $select_fields FROM $table WHERE $id_field='$kkk'";
                              #print "$k=>$kk=>$kkk=>$query\n<br>";		  
                              if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_bases_imponibles');}
                              while($dbh->FetchRow()){
                                    undef my @row;
                                    @row = $dbh->Data();
                                    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 $query1 = "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 "$tipo_tarifa_proceso_id=>$valor=>$query1\n";
                                    if ( $dbh1->Sql($query1) ){SQL_fail($query1,$dbh1,'sub get_basefas');}
                                    while($dbh1->FetchRow()){
                                          undef my @row1;
                                          @row1 		  = $dbh1->Data();
                                          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;#Verificar si hay que sumar las bases imponibles por grupo y periodo
                                          $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} += $valor+((($imponible-$rango_maximo)/$divisor)*$excendente);                                                      
                                                      }
                                                }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   = sprintf("%.2f", ($resultado - int($resultado)));
                                                            if ($decimal>.00){$entero++}
                                                            #$base{$k}{$kk}{$kkk} += $valor+((($imponible-$rango_minimo)/$divisor)*$excendente);
                                                            $base{$k}{$kk}{$kkk} += $valor+($entero*$excendente);
                                                      }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} += $valor+($imponible*$excendente);
                                                            }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++}
                                                                  #$base{$k}{$kk}{$kkk} += $valor+((($imponible-$rango_minimo)/$divisor)*$excendente);
                                                                  $base{$k}{$kk}{$kkk} += $valor+($entero*$excendente);                                                                  
                                                            }
                                                      }else{
                                                            $base{$k}{$kk}{$kkk} += $valor;
                                                      }
                                                }
                                          }
                                    }
                                    
                              }
                        }
                  }
	    }
      }
      $dbh->Close();
      $dbh1->Close();
#      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'}<=>$cargo{$k}{$kk}{'FIE'}";
#	    }
#	    print "\n<br>";
#      }      
      return %cargo;
}
sub get_cargos_a_cuenta {
      my $cac                 = "";
      my $dt_actual	      = "";
      if(!$data->{clase}->{charges}->{to_account}){return}
      my $database	      = $data->{clase}->{charges}->{to_account}->{database};
      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_database	      = $data->{clase}->{charges}->{process}->{accesorios}->{database};
      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 $database1	      = $data->{clase}->{charges}->{process}->{tarifas}->{database};
      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 $database2	      = $data->{clase}->{charges}->{process}->{tarifas_compuestas}->{database};
      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()}
      
      if (!($dbh  = new Win32::ODBC($database))){connect_fail($database)}
      if (!($dbh1 = new Win32::ODBC($a_database))){connect_fail($a_database)}
      if (!($dbh2 = new Win32::ODBC($database1))){connect_fail($database1)}
      if (!($dbh3 = new Win32::ODBC($database2))){connect_fail($database2)}      
      
      my $where = "$id_field='$id' and $filter_field='$filter_value_field'";
      my $query	= "SELECT $period_field,$value_field,$tributo_field,$tarifa_field FROM $table WHERE $where";
      #print "1. $query</n><br>";
      if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_cargos_a_cuenta');}
      while($dbh->FetchRow()){
	    undef my @row;
	    @row = $dbh->Data();
            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);
            #print "$dt_cobro_desde<=$row[0]>=$dt_cobro_hasta<>$dtd<>$dth<br>";      
            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);
                  #print "$row[0]<=>$row[1]<=>$row[2]<=>$row[3]<br>";
                  my $a_query = "SELECT $a_select_fields FROM $a_table WHERE $a_id_field='$row[2]'";
                  #print "2. $a_query<br>";
                  if ( $dbh1->Sql($a_query) ){SQL_fail($a_query,$dbh1,'sub get_accesorios 1');}
                  while($dbh1->FetchRow()){
                        undef my @row1;
                        @row1    = $dbh1->Data();
                        $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 $query1 = "SELECT $select_fields1 FROM $table1 WHERE $id_field1='$row1[0]' ORDER BY $order_by1 ASC LIMIT 1";
                              #print "3. $query1<br>";
                              if ( $dbh2->Sql($query1) ){SQL_fail($query1,$dbh2,'sub get_accesorios 2');}
                              if ($dbh2->FetchRow()){
                                    undef my @row2;
                                    @row2 = $dbh2->Data();
                                    if($row2[3] eq "C"){	    
                                          my $m89	= "";
                                          my $m90	= "";
                                          my $query2  = "SELECT $select_fields2 FROM $table2 WHERE $id_field2='$row2[0]'";
                                          #print "4. $query2<br>";
                                          if ( $dbh3->Sql($query2) ){SQL_fail($query2,$dbh3,'sub get_accesorios 3');}
                                          while ($dbh3->FetchRow()){
                                                undef my @row3;
                                                @row3 = $dbh3->Data();
                                                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{
                                                      #print "value:$row3[4]";
                                                      $cargos{$row[0]}{$row[3]}{$row3[0]} = $cargos{$row[0]}{$row[3]}{'DIM'}*$cargos{$row[0]}{$row[3]}{'VAL'}*($row3[4]/365);
                                                }
                                          }
                                    }
                              }
                        }
                  }
                  $cac .= $row[2]."|";
            }
      }
      $dbh->Close();
      $dbh1->Close();
      $dbh2->Close();
      $dbh3->Close();
      return $cac;
#      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 $database	= $data->{clase}->{installments}->{database};
      my $table		= $data->{clase}->{installments}->{table};
      my $id_field	= $data->{clase}->{installments}->{id_field};
      my $select_fields	= $data->{clase}->{installments}->{select_fields};
      
      #my $query = "SELECT $select_fields FROM $table WHERE $id_field='$id' and dt_abono>='$dt_desde'";
      my $query = "SELECT $select_fields FROM $table WHERE $id_field='$id'";
      #print "$query\n";
      if (!($dbh = new Win32::ODBC($database))){connect_fail($database)}
      if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_abonos');}
      while($dbh->FetchRow()){
	    undef my @row;
	    @row = $dbh->Data();
	    #my $codigo = substr($row[1],5,3);
	    #print "$row[0],$row[1],$codigo,$row[2]";
	    #if($codigo eq "FIE" || $codigo eq "INT" || $codigo eq "MUL"){}else{$codigo = "VAL"}
	    $abono{$row[0]}{$row[1]}{$row[3]} += $row[2];
	    $balance_type{$row[0]}{$row[1]}{$row[3]} = $row[4];
      }
      $dbh->Close();
#      print "ABONOS\n<br>";
#      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<br>";	    
#      }
      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){
                                    #print "$k=>$kk=>$kkk=>CARGO=>$cargos{$k}{$kk}{$kkk}<>ABONO=>$abonos{$k}{$kk}{$kkk}<>TIPO=>$balance_tipo<>TYPE=>$balance_type{$k}{$kk}{$kkk}\n";                                    
				    if($balance_tipo eq "ABONO"){
					  if(!$abonos{$k}{$kk}{$kkk}){
						$bal{$k}{$kk}{$kkk} = $cargos{$k}{$kk}{$kkk};
                                                #$bal{$k}{$kk}{'FIE'} = get_tarifa_accesorio($k,'FIE',$accesorios{substr($kk,0,8)}{'FIE'},$bal{$k}{$kk}{$kkk},$abonos{$k}{$kk}{$kkk});
                                                #$bal{$k}{$kk}{'INT'} = get_tarifa_accesorio($k,'INT',$accesorios{substr($kk,0,8)}{'INT'},$bal{$k}{$kk}{$kkk},$abonos{$k}{$kk}{$kkk});
                                                #$bal{$k}{$kk}{'MUL'} = get_tarifa_accesorio($k,'MUL',$accesorios{substr($kk,0,8)}{'MUL'},$bal{$k}{$kk}{$kkk},$abonos{$k}{$kk}{$kkk});
						$suma_tarifa += $bal{$k}{$kk}{$kkk};
					  }
				    }
				    if($balance_tipo eq "SALDO"){
					  if($abonos{$k}{$kk}{$kkk}){
                                                my $cargo = sprintf("%.".$i2_digitos."f",$cargos{$k}{$kk}{$kkk});
                                                 if (($cargo-$abonos{$k}{$kk}{$kkk})<-0.0001 || ($cargo-$abonos{$k}{$kk}{$kkk})>=0.0001) {
                                                      $bal{$k}{$kk}{$kkk} = $cargo-$abonos{$k}{$kk}{$kkk};
                                                      #$bal{$k}{$kk}{'FIE'} = get_tarifa_accesorio($k,'FIE',$accesorios{substr($kk,0,8)}{'FIE'},$bal{$k}{$kk}{$kkk},$abonos{$k}{$kk}{$kkk});
                                                      #$bal{$k}{$kk}{'INT'} = get_tarifa_accesorio($k,'INT',$accesorios{substr($kk,0,8)}{'INT'},$bal{$k}{$kk}{$kkk},$abonos{$k}{$kk}{$kkk});
                                                      #$bal{$k}{$kk}{'MUL'} = get_tarifa_accesorio($k,'MUL',$accesorios{substr($kk,0,8)}{'MUL'},$bal{$k}{$kk}{$kkk},$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};
                                                #$bal{$k}{$kk}{'FIE'} = get_tarifa_accesorio($k,'FIE',$accesorios{substr($kk,0,8)}{'FIE'},$bal{$k}{$kk}{$kkk},$abonos{$k}{$kk}{$kkk});
                                                #$bal{$k}{$kk}{'INT'} = get_tarifa_accesorio($k,'INT',$accesorios{substr($kk,0,8)}{'INT'},$bal{$k}{$kk}{$kkk},$abonos{$k}{$kk}{$kkk});
                                                #$bal{$k}{$kk}{'MUL'} = get_tarifa_accesorio($k,'MUL',$accesorios{substr($kk,0,8)}{'MUL'},$bal{$k}{$kk}{$kkk},$abonos{$k}{$kk}{$kkk});
                                                $suma_tarifa += $bal{$k}{$kk}{$kkk};
                                          }else{
                                                if($balance_type{$k}{$kk}{$kkk} eq "S"){
                                                      my $cargo = sprintf("%.".$i2_digitos."f",$cargos{$k}{$kk}{$kkk});
                                                      if (($cargo-$abonos{$k}{$kk}{$kkk})<-0.0001 || ($cargo-$abonos{$k}{$kk}{$kkk})>=0.0001) {
                                                            $bal{$k}{$kk}{$kkk} = $cargo-$abonos{$k}{$kk}{$kkk};
                                                            
                                                            #$bal{$k}{$kk}{'FIE'} = get_tarifa_accesorio($k,'FIE',$accesorios{substr($kk,0,8)}{'FIE'},$bal{$k}{$kk}{$kkk},$abonos{$k}{$kk}{$kkk});
                                                            #$bal{$k}{$kk}{'INT'} = get_tarifa_accesorio($k,'INT',$accesorios{substr($kk,0,8)}{'INT'},$bal{$k}{$kk}{$kkk},$abonos{$k}{$kk}{$kkk});
                                                            #$bal{$k}{$kk}{'MUL'} = get_tarifa_accesorio($k,'MUL',$accesorios{substr($kk,0,8)}{'MUL'},$bal{$k}{$kk}{$kkk},$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'};			
		  }
	    }
      }
#      print "BALANCE\n";      
#      foreach $k (sort keys %bal){
#	    print "$k=>";
#	    foreach $kk (sort keys %{$bal{$k}}){
#		  print "$kk=>";
#		  foreach $kkk (sort keys %{$bal{$k}{$kk}}){
#			print "$kkk=>$bal{$k}{$kk}{$kkk}\n";
#		  }
#	    }
#	    print "\n";	    
#      }      
      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 .= sprintf("%.".$i2_digitos."f",$bal{$p}{$pp}{'VAL'})."|";
                              $stream .= sprintf("%.".$i2_digitos."f",$bal{$p}{$pp}{'FIE'})."|";
                              $stream .= sprintf("%.".$i2_digitos."f",$bal{$p}{$pp}{'INT'})."|";
                              $stream .= sprintf("%.".$i2_digitos."f",$bal{$p}{$pp}{'MUL'})."|";
                              $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){
                                    #if($bal{$p}{$pp}{'VAL'}){
                                    $stream .= $p."|";
                                    $stream .= $bal{$p}{$pp}{'DIM'}."|";
                                    $stream .= $pp."|";		  
                                    $stream .= sprintf("%.".$i2_digitos."f",$bal{$p}{$pp}{'VAL'})."|";
                                    $stream .= sprintf("%.".$i2_digitos."f",$bal{$p}{$pp}{'FIE'})."|";
                                    $stream .= sprintf("%.".$i2_digitos."f",$bal{$p}{$pp}{'INT'})."|";
                                    $stream .= sprintf("%.".$i2_digitos."f",$bal{$p}{$pp}{'MUL'})."|";
                                    $stream .= "&&";		  
                                    $rtributos{substr($pp,0,8)}= substr($pp,0,8);
                                    $no_tarif .= $stream;
                                    #}
                              }
                        }
                  }                        
	    }
	    if($no_tarif ne ""){$stream .= "~~";}
      }
#      foreach $k (sort keys %rtributos){
#	    print "$k=>$rtributos{$k}\n";
#      }      
      #print "BALANCE\n";
      #print "$stream";
      return $stream;     
}
sub get_tributos_labels {
      my $labels	= "";
      my %tribu		= ();
      my $database	= $data->{clase}->{charges}->{process}->{tributos}->{labels}->{database};
      my $table		= $data->{clase}->{charges}->{process}->{tributos}->{labels}->{table};
      my $id_field	= $data->{clase}->{charges}->{process}->{tributos}->{labels}->{id_field};
      my $select_fields	= $data->{clase}->{charges}->{process}->{tributos}->{labels}->{select_fields};
     
      if (!($dbh = new Win32::ODBC($database))){connect_fail($database)}
      foreach $k (sort keys %tributos){
	    foreach $kk (sort keys %{$tributos{$k}}){
		  if($rtributos{$kk} eq $kk){
			$tribu{$kk} = [$tributos{$k}{$kk}];
		  }
	    }
      }      
      if($cargos_a_cuenta ne ""){
            my @o_tribu	= split(/\|/,$cargos_a_cuenta);
            for(my $i=0;$i<=$#o_tribu;$i++){
                $tribu{$o_tribu[$i]} = $o_tribu[$i];  
            }
      }
      foreach $key(sort keys %tribu){
	    my $query	= "SELECT $select_fields FROM $table WHERE $id_field='$key' GROUP BY $id_field ORDER BY $id_field";
	    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_tarifas');}
	    while($dbh->FetchRow()){
		  undef my @row;
		  @row	   = $dbh->Data();
		  $labels .= $key.",".$row[0]."|";
	    }
      }
      #print "$labels";
      $dbh->Close();
      return $labels;
}
sub get_accesorios_labels {
       my $labels	= "";
      my %acceso	= ();
      my %acceso_labels	= ();
      my %acceso_labels1= ();
      my %tribu		= ();
      my $database	= $data->{clase}->{charges}->{process}->{accesorios}->{database};
      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};

      if (!($dbh = new Win32::ODBC($database))){connect_fail($database)}
      foreach $k (sort keys %tributos){
	    foreach $kk (sort keys %{$tributos{$k}}){
		  $tribu{$kk}=[$tributos{$k}{$kk}];
	    }
      }
      foreach my $key(sort keys %tribu){
	    #my $tributo	= $tributos{$key}[0];
	    #my $nivel	= $tributos{$key}[1];	    
	    #my $desde	= $tributos{$key}[2];
	    #my $hasta	= $tributos{$key}[3];	    
	    my $query = "SELECT $select_fields FROM $table WHERE $id_field='$key'";
	    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_accesorios labels');}
	    while($dbh->FetchRow()){
		  undef my @row;
		  @row = $dbh->Data();
		  $acceso{$key}{$row[0]} = [@row];
	    }
      }
      $dbh->Close();

      my $database1	= $data->{clase}->{charges}->{process}->{accesorios}->{labels}->{database};
      my $table1	= $data->{clase}->{charges}->{process}->{accesorios}->{labels}->{table};
      my $id_field1	= $data->{clase}->{charges}->{process}->{accesorios}->{labels}->{id_field};
      my $select_fields1= $data->{clase}->{charges}->{process}->{accesorios}->{labels}->{select_fields};      
      if (!($dbh1 = new Win32::ODBC($database1))){connect_fail($database1)}
      foreach my $key(sort keys %acceso){
	    foreach my $key1(sort keys %{$acceso{$key}}){	    
		  $query = "SELECT $select_fields1 FROM $table1 WHERE $id_field1='$key1'";
		  #print $query;
		  if ( $dbh1->Sql($query) ){SQL_fail($query,$dbh1,'sub get_accesorios labels');}
		  if ($dbh1->FetchRow()){
			undef my @row;
			@row = $dbh1->Data();
			$acceso_labels{$key1} = $row[0];
		  }
	    }
      }      
      $dbh1->Close();
      
      my $database2	 = $data->{clase}->{charges}->{process}->{tarifas}->{database};
      my $table2	 = $data->{clase}->{charges}->{process}->{tarifas}->{table};
      my $id_field2	 = $data->{clase}->{charges}->{process}->{tarifas}->{id_field};
      my $select_fields2 = $data->{clase}->{charges}->{process}->{tarifas}->{select_fields};
      if (!($dbh2 = new Win32::ODBC($database2))){connect_fail($database2)}
      foreach $key(sort keys %acceso_labels){
	    my $query	= "SELECT $select_fields2 FROM $table2 WHERE substring($id_field2,1,8)='$key' ORDER BY dt_desde DESC LIMIT 1";
	    #print $query;
	    if ( $dbh2->Sql($query) ){SQL_fail($query,$dbh2,'sub get_accesorios labels');}
	    if ($dbh2->FetchRow()){
		  undef my @row;
		  @row = $dbh2->Data();
		  $acceso_labels1{$row[0]} = $acceso_labels{$key};
	    }
      }
      $dbh2->Close();
      
      foreach my $key(sort keys %acceso_labels1){
	    $labels .= $key.",".$acceso_labels1{$key}."|"    
      }
      #$labels .= "~~";
      return $labels;
}
sub get_tributos_accesorios {
      my $trib_acc ="";
      foreach $k (sort keys %accesorios_periodo){
	    foreach $kk (sort keys %{$accesorios_periodo{$k}}){
                  my $acc_desde = $accesorios_periodo{$k}{$kk}[1];
                  my $acc_hasta = $accesorios_periodo{$k}{$kk}[2];
                  $acc_desde =~ s/\-//g;
                  $acc_hasta =~ s/\-//g;
                  $trib_acc .= $k.",".$kk.",".$acc_desde.",".$acc_hasta."|";
	    }
      }      
      return $trib_acc;
}
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_tarifas_labels {
      my $tar_lab ="";
      foreach $k (sort keys %tariffs_labels){
            $tar_lab .= $k.",".$tariffs_labels{$k}."|";
      }
      return $tar_lab;
}
sub get_parametros {#recordar arreglar parametros
      my $params = "";
      foreach $k (sort keys %accesorios){
	    foreach $kk (sort keys %{$accesorios{$k}}){
		  $params .= $kk."|".$accesorios{$k}{$kk}."~~";
	    }
      }
      $params .= "DG|60~~MM|2.857~~";
      return $params;
}
sub get_grid {
      my ($row_type)=@_;
      my $gr	= $data->{grid}->{row}->{$row_type};
      my $xml	= new XML::Simple (NoAttr=>1, RootName=>$row_type);
      my $data	= $xml->XMLout($gr);      
      $data 	=~ s/<content>//g;
      $data 	=~ s/<\/content>//g;      
      $data 	=~ s/&gt;//g;
      $data 	=~ s/\n//g;
      $data 	=~ s/ //g;
      return $data;
}
sub get_billing {
      my $gr	= $data->{clase}->{billing};
      my $b	= "billing";
      my $xml	= new XML::Simple (NoAttr=>1, RootName=>$b);
      my $data	= $xml->XMLout($gr);      
      $data 	=~ s/<content>//g;
      $data 	=~ s/<\/content>//g;      
      $data 	=~ s/&gt;//g;
      $data 	=~ s/\n//g;
      $data 	=~ s/ //g;
      return $data;
}
sub construct_page {
      my $doc = $balance."^^".$tributos_labels."^^".$accesorios_labels."^^".$grid_header."^^".$grid_body."^^".$grid_footer."^^".$billing."^^".$parametros."^^".$generales."^^".$tariffs."^^".$tributos_accesorios."^^".$bases_imponibles;
      print "$doc";
}
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 2
sub get_fecha_actual {
      my $database = $data->{clase}->{database};
      if (!($dbh = new Win32::ODBC($database))){connect_fail($database)}
      my $query= "SELECT DATE_FORMAT(CURRENT_DATE,'%Y%m%d')";
      if ($dbh->Sql($query)){SQL_fail($dbh,$query,'sub get_fecha_actual')};
      if ($dbh->FetchRow()){undef my @row; @row=$dbh->Data();return ($row[0])}else{return (0)}
}
sub get_periodo_cobro {
      my ($tributo_id,$dt_td,$dt_th)	= @_;
      #my ($tributo_id,$dt_actual)	= @_;      
      #$dt_actual	=~ s/\-//g;
      my $desde		= $dt_td;
      my $hasta		= $dt_th;
      #my $desde		= $dt_actual;
      #my $hasta		= $dt_actual;
      my $database	= $data->{clase}->{charges}->{process}->{inicio_cobros}->{database};
      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};      

      if (!($dbh_pc = new Win32::ODBC($database))){connect_fail($database)}
      my $query   = "SELECT $select_fields FROM $table WHERE $id_field='$id' AND c8_tributo_id='$tributo_id'";
      #print "$query\n";
      if ( $dbh_pc->Sql($query) ){SQL_fail($query,$dbh_pc,'sub get_periodo_cobro');}
      if($dbh_pc->FetchRow()){
	    undef my @row;
	    @row = $dbh_pc->Data();
	    $row[0] =~ s/\-//g;
	    $row[1] =~ s/\-//g;
	    $desde=$row[0];
	    $hasta=$row[1];	    
            #print "YO=>$desde<>$hasta\n";
      }
      $dbh_pc->Close();      
      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\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_valor {
      my ($periodo,$tari) = @_;
      my $val	= 0;
      my $ltari	= "";
      foreach my $a (sort keys %{$bases{$tari}}){if($a<=$periodo){$val = $bases{$tari}{$a};$ltari=$tari}}
      return ($ltari,$val)
}
sub get_tarifa_accesorio {
      my ($periodo,$acce_id,$acce_params,$tarifa_valor,$dias_mora) = @_;
      my $acce_valor = 0;
      #$tarifa_valor+=10;
      #$tarifa_valor=sprintf("%.2f",$tarifa_valor);
      #$tarifa_valor-=10;
      #if($acce_id eq "FIE"){$acce_valor = $tarifa_valor*$acce_params;}
      if($acce_id eq "FIE"){$acce_valor = $tarifa_valor*$acce_params;$acce_valor = sprintf("%.2f",$acce_valor)}      
      if($acce_id eq "INT"){$acce_valor = $dias_mora*$tarifa_valor*($acce_params/365);}
      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+=10;
      #$acce_valor = sprintf("%.2f",$acce_valor);
      #$acce_valor-=10;
      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-ROUTINES -> LEVEL 3
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}
      #print "DIA_CARGO=$c8_dia_cargo<>DAYS=$dias\n";
      return $dias;
}
#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub get_component_schema {
      ($cmp)=@_;
      $schema     = $cmp."_bsws.xml";
      $xml        = new XML::Simple;
      $data 	  = $xml->XMLin("../xml/".$schema);
      $path	  = $data->{base_path}."/libraries/html/";
      $file	  = "bsws.html";
      $dt_desde	  = "";
      $dt_hasta	  = "";      
}
sub connect_fail {
    my ($DSN)=@_;
    print "Error connecting to $DSN<br>";
    print "Error:".Win32::ODBC::Error()."<br>";
    exit;
}
sub SQL_fail {
    ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Error: ".$dbh->Error()."\n<br>";
    print "Query: ".$query."<br>";    
    $dbh->Close();
    exit;
}
#-------------------------------------------------------------------------
#sub hashValueAscendingNum{
#      $grades{$a}<=>$grades{$b}
#}
#The Mythical Man-Month: Essays on Software Engineering, 20th Anniversary Edition (Paperback) 
#by Frederick P. Brooks