#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Information Software SA
# Date In: 2015-01-07
# Updates: 2015-01-07
# Author:Rodolfo Machon
# File Name: get_cuotas_pendientes.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#PERL MODULES
use CGI;
use Win32::ODBC;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#HTML printing activation
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$form       = new CGI;
$lan        = $form->param('lan');
$appid      = $form->param('appid');
$dt_desde   = $form->param('dt_desde');
$dt_hasta   = $form->param('dt_hasta');
$valor      = $form->param('valor');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#CHECK PARAMETERS
if ($lan eq ""){print "Your are not passing LAN parameter<br>";}
if ($appid eq ""){print "Your are not passing APPID parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#MAIN VARIABLES
$dt_desde =~ s/-//g;
$dt_hasta =~ s/-//g;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#OPEN DATABASE CONECTOR
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
if (!($dbh1 = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#MAIN PROGRAM
#PROCUREMENT
%otros        = get_otros();
%nics         = get_cobales_nics();
%cuotas       = get_cuotas_pendientes();
%cuotas_otros = get_cuotas_pendientes_otros();
#INTEGRATION
%tributos = put_cuotas_otros();
#PRESENTATION
$table  = construct_table();
print $table;
exit;
#-------------------------------------------------------------------------
#SUB-ROUTINES LEVEL 1
sub get_otros {
    my %hash    = ();
    my $query ="SELECT i8_expediente_id,i8_periodo,(d82_pago/100) 
                FROM sdr_cobales
                WHERE c8_tributo_id='TSINMOTR'
                      AND dt_cobal BETWEEN '$dt_desde' and '$dt_hasta'
                ";
    #print "$query <br>";
    if ( $dbh->Sql($query) ) {SQL_Fail($query,$dbh,'get_otros')}
    while($dbh->FetchRow()){
        my @row          = $dbh->Data();
        my $bar          = get_barrido($row[0],$row[1]);
        my $pago         = round(($row[2]/1.05),2);
        $bar             = round(($bar/1.05),2);
        $pago            = $pago-$bar;
        #$hash{'fiestas'}+= round(($row[2]-$pago),2);        
        if($bar>0){
            $hash{'Barrido'} += $bar;
        }
        if($pago>0){
            $hash{'Pavimentacion'}   += $pago;
        }
        #$hash{'total'}   += round($row[2],2);
    }      
    return %hash;
}
sub get_cobales_nics {
    my %hash    = ();
    my $query ="SELECT i8_periodo,i8_nic_id,i8_expediente_id 
                FROM sdr_cobales
                WHERE c8_tributo_id='TSINMCUP'
                      AND dt_cobal BETWEEN '$dt_desde' and '$dt_hasta'
                ";
    if ( $dbh->Sql($query) ) {SQL_Fail($query,$dbh,'get_cobales_nics')}
    while($dbh->FetchRow()){
          my @row = $dbh->Data();
          $hash{$row[0]}{$row[1]} = $row[2];
    }
    return %hash;
}
sub get_cuotas_pendientes {
    my $total    = 0;
    my $total_sf = 0;
    my %hash     = ();
    foreach my $k (sort keys %nics){
        my $ns      = "";
        my $ano		= substr($k,0,4);
        my $mes		= substr($k,4,2)*1;
        my $nmes	= $mes-2;
        if($nmes<=0){$ano = $ano-1; $nmes = 12+$nmes}
        if($nmes<10){$nmes = "0".$nmes}
        my $cuota_per = $ano.$nmes;
        foreach my $kk (sort keys %{$nics{$k}}){
            $ns .= $kk.",";
        }
        $ns = substr($ns,0,length($ns)-1);
        my $query = "SELECT aseo,alumbrado,desecho,calle,barrido
                     FROM cargas_caess
                     WHERE  nic IN ($ns) AND periodo = '$cuota_per'
                    ";
        #print "$query <br>";
        if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_cuotas_pendientes');}                  
        while($dbh->FetchRow()){
            my @row = $dbh->Data();
            $hash{'Aseo'}      += round((($row[0]*1)/100)/1.05,2);
            $hash{'Alumbrado'} += round((($row[1]*1)/100)/1.05,2);
            $hash{'Desecho'}   += round((($row[2]*1)/100)/1.05,2);
            $hash{'Pavimentacion'}     += round((($row[3]*1)/100)/1.05,2);
            $hash{'Barrido'}   += round((($row[4]*1)/100)/1.05,2);
            $total += (($row[0]*1)/100)+(($row[1]*1)/100)+(($row[2]*1)/100)+(($row[3]*1)/100)+(($row[4]*1)/100);
        }            
    }
    $total_sf = $hash{'aseo'}+$hash{'alumbrado'}+$hash{'desecho'}+$hash{'calle'}+$hash{'barrido'};
    #$hash{'fiestas'}  = round($total - $total_sf,2);   
    return %hash;
}
sub get_cuotas_pendientes_otros {
    my $total    = 0;
    my $total_sf = 0;
    my %hash     = ();
    foreach my $k (sort keys %nics){
        my $ns      = "";
        my $ano		= substr($k,0,4);
        my $mes		= substr($k,4,2)*1;
        my $nmes	= $mes-2;
        if($nmes<=0){$ano = $ano-1; $nmes = 12+$nmes}
        if($nmes<10){$nmes = "0".$nmes}
        my $cuota_per = $ano.$nmes;
        foreach my $kk (sort keys %{$nics{$k}}){
            my $query = "SELECT (otro/100)
                         FROM cargas_caess
                         WHERE  nic='$kk' AND periodo = '$cuota_per'
                        ";
            #print "$query <br>";
            if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_cuotas_pendientes');}                  
            while($dbh->FetchRow()){
                my @row = $dbh->Data();
                if($row[0]*1>0){
                    my $bar          = get_barrido($cuota_per,$nics{$k}{$kk});
                    my $pago         = round(($row[0]/1.05),2);
                    $bar             = round(($bar/1.05),2);
                    $pago            = $pago-$bar;
                    #$hash{'fiestas'}+= round(($row[0]-$pago),2);        
                    if($bar>0){
                        $hash{'Barrido'} += $bar;
                    }
                    if($pago>0){
                        $hash{'Pavimentacion'}   += $pago;
                    }
                }
            }
        }
    }
    return %hash;
}
sub put_cuotas_otros {
    my %hash  = ();
    my $total = 0;
    my $difer = 0;
    foreach my $k (sort keys %otros) {
        $hash{$k} += $otros{$k}; 
    }    
    #foreach my $k (sort keys %otros){print "$k > ".$otros{$k}."<br>";}
    foreach my $k (sort keys %cuotas) {
        $hash{$k} += $cuotas{$k}; 
    }
    #foreach my $k (sort keys %cuotas){print "$k > ".$cuotas{$k}."<br>";}    
    foreach my $k (sort keys %cuotas_otros) {
        $hash{$k} += $cuotas_otros{$k}; 
    }
    #foreach my $k (sort keys %cuotas_otros){print "$k > ".$cuotas_otros{$k}."<br>";}
    foreach my $k (sort keys %hash) {
        $total += $hash{$k}
    }
    $difer = round(($valor - $total),2);
    
    if($hash{'Aseo'}>0 && $difer>0){$hash{'Aseo'}=round(($hash{'Aseo'}+$difer),2);$difer=0}
    if($hash{'Alumbrado'}>0 && $difer>0){$hash{'Alumbrado'}=round(($hash{'Alumbrado'}+$difer),2);$difer=0}
    if($hash{'Desecho'}>0 && $difer>0){$hash{'Desecho'}=round(($hash{'Desecho'}+$difer),2);$difer=0}
    if($hash{'Pavimentacion'}>0 && $difer>0){$hash{'Pavimentacion'}=round(($hash{'Pavimentacion'}+$difer),2);$difer=0}
    return %hash;
}
sub construct_table {
	$table .= "<TABLE id=cuota>";	
	$table .= "<TR>
                    <TD colspan=2>CUOTA PENDIENTE Y/U OTROS</TD>
                </TR>
              ";
    foreach my $k (sort keys %tributos) {
        if($tributos{$k}>0){
            $table.="<TR>
                        <TD>$k</TD>
                        <TD align=right>".round($tributos{$k},2)."</TD></TR>";
        }
    }
	$table.="</table>";
    return $table;
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
sub get_barrido {
    my($i8_inmueble_id,$i8_periodo)=@_;
    my $cargo_barrido = 0;
    my $sql = "select sum(d82_cargo) from sdd_inmueble_cargos where i8_inmueble_id='$i8_inmueble_id' and c8_tributo_id='TSINMBAR' and i8_periodo='$i8_periodo'";
    if ( $dbh1->Sql($sql) ) {SQL_fail($sql,$dbh1,'verificar abono')}
    while($dbh1->FetchRow()){
          my @row2=$dbh1->Data();
          if($row2[0] ne ""){$cargo_barrido = $row2[0]}
    }
    return $cargo_barrido;
}
#-------------------------------------------------------------------------
# AUXILIAR-SUBROUTINES
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Error: ".$dbh->Error()."\n<br>";
    print "Query: ".$query."<br>";    
    $dbh->Close();
    exit;
}
sub connect_fail {
    my ($DSN)=@_;
    print "Error connecting to $DSN<br>";
    print "Error:".Win32::ODBC::Error()."<br>";
    exit;
}
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;
}