#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Information Software SA
# Date In: 2011-11-01
# Updates: 2011-11-01
# Author:Rodolfo Machon
# File Name: insert_delsur_abonos_puerto.pl
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#PERL MODULES
#use CGI;
use Win32::ODBC;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#HTML printing activation
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
#foreach my $arg(@ARGV) {printf "$arg";}
$lan              = $ARGV[0];
$appid            = $ARGV[1];
$clase            = $ARGV[2];
$custom_fields    = $ARGV[3];
$custom_values    = $ARGV[4];
$nombre_archivo   = $ARGV[5];
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($lan eq ""){print "Your are not passing LAN parameter<br>";}
if ($appid eq ""){print "Your are not passing APPID parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
if (!($dbh = new Win32::ODBC($appid))){print "Error connecting to $appid <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# MAIN-PROGRAM
%delsures = get_delsures();

insert_abonos();
if ($lan eq "es"){print "El Programa insertar_delsur_abonos() se ejecuto adecuadamente";}
if ($lan eq "en"){print "The program insertar_delsur_abonos() was succesfully executed";}
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
sub get_delsures {
      my %cob = ();      
      my %wfv = ();
      my @cf  = split(/\,/,$custom_fields);
      my @cv  = split(/\,/,$custom_values);
      for (my $i=0;$i<=$#cf;$i++){
            if($cf[$i] eq "c3_facturador_id" || $cf[$i] eq "i8_periodo" || $cf[$i] eq "dt_cobal"){
                  $wfv{$cf[$i]} = $cv[$i];
            }
      }      
      $sql="select i8_expediente_id,i8_periodo,c8_tributo_id,c4_lugar_id,dt_pago,i8_nic_id from sdr_delsures where c3_facturador_id=\"$wfv{'c3_facturador_id'}\" and v128_nombre_archivo=\"$nombre_archivo\" and dt_cobal=\"$wfv{'dt_cobal'}\"";
      #$sql="select i8_expediente_id,i8_periodo,c8_tributo_id,d82_pago,c4_lugar_id,c3_tipo_generador_id,dt_pago from sdr_delsures where c3_facturador_id=\"$wfv{'c3_facturador_id'}\" and dt_cobal=\"$wfv{'dt_cobal'}\"";
      #print "$sql<br>";
      if ( $dbh->Sql($sql) ) {SQL_fail($sql,$dbh,'ge delsures')}
      while($dbh->FetchRow()){
          undef my @row;
          @row = $dbh->Data();
          $cob{$row[0]."_".$row[1]."_".$row[2]}=[@row];
      }
      return %cob;
}
sub insert_abonos {
      foreach $k(sort keys %delsures){
            my $i8_inmueble_id   = $delsures{$k}[0];
            my $i8_periodo       = $delsures{$k}[1];            
            my $c8_tributo_id    = $delsures{$k}[2];
            my $sucursal         = $delsures{$k}[3];
            my $dt_abono         = $delsures{$k}[4];
            my $i8_nic_id        = $delsures{$k}[5];
            my $lugar            = "DELSUR";
            my $inicial          = substr($i8_inmueble_id,0,1);            
            #if($inicial ne "1"){$i8_inmueble_id=get_inmueble_id($i8_nic_id);}            
            my $existe_abono     = verificar_abono($i8_inmueble_id,$i8_periodo,$c8_tributo_id);
            if ($existe_abono==0){
                  my %cargos = get_cargos($i8_inmueble_id,$i8_periodo,$c8_tributo_id);
                  foreach $i (sort keys %cargos){
                        foreach $ii (sort keys %{$cargos{$i}}){                        
                              my $sql="insert into sdd_inmueble_abonos (i8_inmueble_id,c8_tributo_id,c16_tarifa_id,i8_periodo,dt_abono,d82_abono,c3_tipo_abono_id,c16_recibo_id,c2_tipo_recibo_id,v64_abono_agencia,v64_abono_sucursal,c1_balance_type,ts_in)
                                       values('$i8_inmueble_id','$i','$cargos{$i}{$ii}[2]','$i8_periodo','$dt_abono',$cargos{$i}{$ii}[3],'$ii','$i8_nic_id','DS','$lugar','$sucursal','A',now())";
                              if ( $dbh->Sql($sql) ) {SQL_fail($sql,$dbh,'insert_abonos')}
                              print "$sql \n";
                        }
                  }
            }
      }
}
sub get_inmueble_id {
      my($i8_nic_id)=@_;
      my $sql="select i8_inmueble_id from sdd_inmueble_suministro_nics where i8_nic_id='i8_nic_id'";
      #print "$inmueble_id =>$sql<br>";
      if ( $dbh->Sql($sql) ) {SQL_fail($sql,$dbh,'get_inmueble_id')}
      while($dbh->FetchRow()){
            undef my @row;
            @row      = $dbh->Data();
            $inmueble_id   = $row[0];
      }
      return $inmueble_id;
}
sub verificar_abono{
      my($inmueble_id,$periodo,$tributo)=@_;
      my $counter = 0;
      $sql="select count(*) from sdd_inmueble_abonos where i8_inmueble_id='$inmueble_id' and i8_periodo='$periodo' and c8_tributo_id='$tributo'";
      #print "<br>".$sql;
      if ( $dbh->Sql($sql) ) {SQL_fail($sql,$dbh,'verificar abono')}
      while($dbh->FetchRow()){
            my @row2 = $dbh->Data();
            $counter = $row2[0];
            #my $sql1="delete from sdd_inmueble_abonos where i8_inmueble_id='$inmueble_id' and i8_periodo='$periodo' and c8_tributo_id='$tributo'";
            #if ( $dbh1->Sql($sql1) ) {SQL_fail($sql1,$dbh1,'verificar abono')}                  
      }
      return $counter;
}
sub get_cargos {
      my ($id,$per,$tri)  = @_;
      my %carg	     = ();
      my $query = "SELECT c8_tributo_id,c3_tipo_cargo_id,c16_tarifa_id,d82_cargo FROM sdd_inmueble_cargos WHERE i8_inmueble_id='$id' and i8_periodo='$per' and c8_tributo_id='$tri'";
      if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_abonos');}
      while($dbh->FetchRow()){
	    undef my @row;
	    @row = $dbh->Data();
            $carg{$row[0]}{$row[1]} = [@row];
      }
     return %carg;
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub SQL_fail {
    my ($query,$db,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Error: ".$db->Error()."\n<br>";
    print "Query: ".$query."<br>";
    $db->Close();
    exit;
}
sub connect_fail {
    my ($DSN)=@_;
    print "Error connecting to $DSN<br>";
    print "Error:".Win32::ODBC::Error()."<br>";
    exit;
}
#-------------------------------------------------------------------------