#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Information Software SA
# Date In: 2020-11-05
# Updates: 2020-11-05
# Author:Rodolfo Machon
# File Name: insertar_cobal_to_abonos.pl
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#PERL MODULES
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use File::Basename;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#HTML printing activation
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$cgiparms         = new CGI;
$appid	          = $cgiparms->param('appid');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($appid eq ""){print "Your are not passing APPID parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
$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;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# MAIN-PROGRAM
%cobales = get_cobales();
insert_abonos();
print "DONE";
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
sub get_cobales {
      my %hash  = ();
       my $query = "
                  SELECT
                        i8_expediente_id,
                        i8_periodo,
                        c8_tributo_id,
                        d82_pago,
                        c4_lugar_id,
                        c3_tipo_generador_id,
                        dt_pago,
                        i8_nic_id
                  FROM sdr_cobales
                  ";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_tributos');
    while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]."_".$row[1]."_".$row[2]}=[@row];
	}
	return %hash;
}
sub insert_abonos {
    foreach $k(sort keys %cobales){
        my $i8_inmueble_id   = $cobales{$k}[0];
        my $i8_periodo       = $cobales{$k}[1];            
        my $c8_tributo_id    = $cobales{$k}[2];
        my $sucursal         = $cobales{$k}[4];
        my $dt_abono         = $cobales{$k}[6];
        my $i8_nic_id        = $cobales{$k}[7];            
        my $lugar            = "CAESS";
        my $existe_abono     = verificar_abono($i8_inmueble_id,$i8_periodo,$c8_tributo_id);
        print "$i8_inmueble_id > $i8_periodo > $c8_tributo_id > $existe_abono<br>";
        if ($existe_abono==0 && $c8_tributo_id ne "TSINMCUP"){
            my %cargos = get_cargos($i8_inmueble_id,$i8_periodo);
            foreach my $i (sort keys %cargos){
                foreach my $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,ts_in,ts_update,c1_balance_type)
                             values('$i8_inmueble_id','$i','$cargos{$i}{$ii}[2]','$i8_periodo','$dt_abono',$cargos{$i}{$ii}[3],'$ii','$i8_nic_id','CS','$lugar','$sucursal',now(),now(),'A')";
                     print "$sql <br>";        
                    if ( $dbh->Sql($sql) ) {SQL_fail($sql,$dbh,'insert_abonos')}
                }
            }
        }
    }
}
#-------------------------------------------------------------------------
sub get_servicio {
    my($inmueble_id,$periodo,$tributo)=@_;
	my $tipo = "";
	my $codigo = "";
	my $sql = "select c1_tipo_tarifa_id,c2_codigo_id from sdd_inmnueble_servicios where c2_gravable='SI' and i8_inmueble_id='$inmueble_id' and c8_tributo_id='$tributo' and dt_desde<='$periodo'.'15' and dt_hasta>='$periodo'.'15'";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_servicio');
    while (my @row = $sth->fetchrow_array) {
		$tipo = $row[0];
		$codigo = $row[1];
	}
	return $tipo,$codigo;
}
sub get_tarifa {
    my($tributo,$tipo_tarifa_id,$codigo_id,$periodo)=@_;
	my $tarifa = "";
	my $sql = "select c16_tarifa_id from sdm_tarifas where c8_tributo_id='$tributo' and c1_tipo_tarifa_id='$tipo_tarifa_id' and c2_codigo_id='$codigo_id' and dt_desde<='$periodo'.'15' order by dt_desde desc limit 1";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_servicio');
    while (my @row = $sth->fetchrow_array) {
		$tarifa = $row[0];
	}
	return $tarifa;
}
sub verificar_abono{
    my($inmueble_id,$periodo,$tributo)=@_;
    my $counter=0;
    my $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];
    }
    return $counter;
}
#-------------------------------------------------------------------------
#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;
}
#-------------------------------------------------------------------------