#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# WebSoft SA
# Date In: 2006-04-10
# Updates: 2006-04-10,2007-05-20,2016-04-21
# Author:Rodolfo Machon
# File Name: aswsp.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#PERL MODULES
use CGI;
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;
use CGI::Carp qw(fatalsToBrowser);
binmode STDOUT, ":encoding(utf8)";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#HTML printing activation
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$cgiparms 	      = new CGI;
$clase            = $cgiparms->param('clase');
$id		          = $cgiparms->param('id');
$dt_vencimiento	  = $cgiparms->param('dt_vencimiento');
$dt_futuro	      = $cgiparms->param('dt_futuro');
$dt_vencimiento   =~ s/\-//g;
$dt_futuro 	      =~ s/\-//g;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
get_app_data();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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);
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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->{LongReadLen} = 512 * 1024;
$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;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#MAIN PROGRAM
$generales        = get_generales();
%abonos           = get_abonos(); 
$balance          = get_balance();
$tributos_labels  = get_tributos_labels();
$accesorios_labels= get_accesorios_labels();

$grid_header      = get_grid('head');
$grid_body        = get_grid('body');
$grid_footer      = get_grid('foot');

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 $sql	  = "SELECT $select_fields FROM $table 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";            
      }
      $generales = $i2_dias_mora."|".$d82_multa_minima."|".$i2_digitos;
}
sub get_abonos {
      my %abono		= ();
      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 $sql     = "SELECT $select_fields FROM $table WHERE $id_field='$id'";
      my $sth     = $dbh->prepare($sql);
      $sth->execute or die SQL_fail($sql,$dbh,'sub get_abonos');
      while (my @row = $sth->fetchrow_array) {
            $abono{$row[0]}{$row[1]}{$row[2]}{$row[3]} = [$row[4],$row[5],$row[6],$row[7],$row[8]];
      }
      return %abono;
}
sub get_balance {
      foreach my $p (sort keys %abonos){
	    foreach my $pp (sort keys %{$abonos{$p}}){
                  foreach my $ppp (sort keys %{$abonos{$p}{$pp}}){
                        $stream .= $p."|";
                        if($abonos{$p}{$pp}{$ppp}{'DIM'}){$stream .= int($abonos{$p}{$pp}{$ppp}{'DIM'}[0])."|";}else{$stream .= "0|";}
                        $stream .= $ppp."|";  
                        $stream .= sprintf("%.".$i2_digitos."f",$abonos{$p}{$pp}{$ppp}{'VAL'}[0])."|";
                        $stream .= sprintf("%.".$i2_digitos."f",$abonos{$p}{$pp}{$ppp}{'FIE'}[0])."|";
                        $stream .= sprintf("%.".$i2_digitos."f",$abonos{$p}{$pp}{$ppp}{'INT'}[0])."|";
                        $stream .= sprintf("%.".$i2_digitos."f",$abonos{$p}{$pp}{$ppp}{'MUL'}[0])."|";
                        $stream .= $abonos{$p}{$pp}{$ppp}{'VAL'}[1]."|";
                        $stream .= $pp."|";
                        $stream .= $abonos{$p}{$pp}{$ppp}{'VAL'}[2]."|";
                        $stream .= $abonos{$p}{$pp}{$ppp}{'VAL'}[3]."|";
                        $stream .= $abonos{$p}{$pp}{$ppp}{'VAL'}[4]."|";                        
                        $stream .= "&&";		  
                        $rtributos{substr($ppp,0,8)}= substr($ppp,0,8);
                  }
                  $stream .= "~~";
	    }
      }
      #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};
         
      foreach $key(sort keys %rtributos){
            my $sql	= "SELECT $select_fields FROM $table WHERE $id_field='$key' GROUP BY $id_field ORDER BY $id_field";
            my $sth   = $dbh->prepare($sql);
            $sth->execute or die SQL_fail($sql,$dbh,'sub get_tributos_labels');
            while (my @row = $sth->fetchrow_array) {
                  $labels .= $key.",".$row[0]."|";
            }
      }
      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};

      foreach my $key(sort keys %rtributos){    
            my $sql = "SELECT $select_fields FROM $table WHERE $id_field='$key'";
            my $sth = $dbh->prepare($sql);
            $sth->execute or die SQL_fail($sql,$dbh,'sub get_accesorios_labels');
            while (my @row = $sth->fetchrow_array) {
                  $acceso{$key}{$row[0]} = [@row];
            }
      }

      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};      
      foreach my $key(sort keys %acceso){
            foreach my $key1(sort keys %{$acceso{$key}}){	    
                  my $sql = "SELECT $select_fields1 FROM $table1 WHERE $id_field1='$key1'";
                  my $sth = $dbh->prepare($sql);
                  $sth->execute or die SQL_fail($sql,$dbh,'sub get_accesorios_labels1');
                  if (my @row = $sth->fetchrow_array) {
                        $acceso_labels{$key1} = $row[0];
                  }
            }
      }      
      
      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};
      foreach $key(sort keys %acceso_labels){
            my $sql = "SELECT $select_fields2 FROM $table2 WHERE substring($id_field2,1,8)='$key' ORDER BY dt_desde DESC LIMIT 1";
            my $sth = $dbh->prepare($sql);
            $sth->execute or die SQL_fail($sql,$dbh,'sub get_accesorios_labels2');
            if (my @row = $sth->fetchrow_array) {
                  $acceso_labels1{$row[0]} = $acceso_labels{$key};
            }
      }
      foreach my $key(sort keys %acceso_labels1){
            $labels .= $key.",".$acceso_labels1{$key}."|"    
      }
      return $labels;
}
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 construct_page {
      my $doc = $balance."^^".$tributos_labels."^^".$accesorios_labels."^^".$grid_header."^^".$grid_body."^^".$grid_footer."^^".$generales;
      print "$doc";
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#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};
    $country    = $data->{portal}->{country};
}
sub get_component_schema {
      ($cmp)=@_;
      $schema  = $cmp."_asws.xml";
      $xml     = new XML::Simple;
      $data    = $xml->XMLin("../xml/".$schema);
}
sub SQL_fail {
    ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Error: ".$dbh->Error()."\n<br>";
    print "Query: ".$query."<br>";    
    exit;
}
#-------------------------------------------------------------------------