#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2014-12-07
# Updates: 2014-12-07
# Author:Rodolfo Machon
# File Name: cobal_borrar.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use Win32::ODBC;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$cgiparms	= new CGI;
$appid		= $cgiparms->param('appid');
$dt_cobal	= $cgiparms->param('dt_cobal');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($dt_cobal eq ""){print "Your are not passing FECHA parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH	="../libraries/html/";
$FILE 	= $lan."_cobales_s.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROCUREMENT
%cobales = get_cobales();
# INTEGRATION
delete_cobal();
delete_abonos();
# PRESENTATION
print "COBAL BORRADO";
#construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_cobales {
	my %hash = ();
    my $sql = "SELECT i8_expediente_id,i8_periodo,c8_tributo_id,d82_pago,c4_lugar_id,c3_tipo_generador_id,dt_pago
			   FROM sdr_cobales
			   WHERE c3_facturador_id='CAE' and dt_cobal='$dt_cobal'";
    #print "$sql<br>";
    if ( $dbh->Sql($sql) ) {SQL_fail($sql,$dbh,'ge cobales')}
    while($dbh->FetchRow()){
        my @row = $dbh->Data();
        $hash{$row[0]."_".$row[1]."_".$row[2]}=[@row];
    }
	return %hash;
}
sub delete_cobal {
	my $query = "DELETE FROM sdr_cobales WHERE dt_cobal='$dt_cobal'";
	#print "$query <br>\n";
	if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub delete_cobal')}
}
sub delete_abonos {
	foreach my $k (sort keys %cobales){
		if($cobales{$k}[2] eq "TSINMCUP"){
			%tributos = get_tributos($cobales{$k}[0],$cobales{$k}[1]);
			foreach my $k (sort keys %tributos){
				my $cuenta		= $tributos{$k}[3];
				my $aseo		= round((($tributos{$k}[4]*1)/100),2);
				my $alumbrado	= round((($tributos{$k}[5]*1)/100),2);
				my $desecho		= round((($tributos{$k}[6]*1)/100),2);
				my $otro		= round((($tributos{$k}[7]*1)/100),2);
				my $calle		= round((($tributos{$k}[9]*1)/100),2);
				my $barrido		= round((($tributos{$k}[10]*1)/100),2);
				my $cuota		= round((($tributos{$k}[11]*1)/100),2);
				my $periodo		= $tributos{$k}[12];
				if($aseo>0){
					my $query = "DELETE FROM sdd_inmueble_abonos WHERE i8_inmueble_id='$cuenta' and i8_periodo='$periodo' and c8_tributo_id='TSINMASE' and c2_tipo_recibo_id='CS'";
					if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub delete_cobal')}
				}
				if($alumbrado>0){
					my $query = "DELETE FROM sdd_inmueble_abonos WHERE i8_inmueble_id='$cuenta' and i8_periodo='$periodo' and c8_tributo_id='TSINMALU' and c2_tipo_recibo_id='CS'";
					if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub delete_cobal')}
				}
				if($desecho>0){
					my $query = "DELETE FROM sdd_inmueble_abonos WHERE i8_inmueble_id='$cuenta' and i8_periodo='$periodo' and c8_tributo_id='TSINMDES' and c2_tipo_recibo_id='CS'";
					if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub delete_cobal')}
				}
				if($calle>0){
					my $query = "DELETE FROM sdd_inmueble_abonos WHERE i8_inmueble_id='$cuenta' and i8_periodo='$periodo' and c8_tributo_id='TSINMCAL' and c2_tipo_recibo_id='CS'";
					if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub delete_cobal')}
				}
				if($barrido>0){
					my $query = "DELETE FROM sdd_inmueble_abonos WHERE i8_inmueble_id='$cuenta' and i8_periodo='$periodo' and c8_tributo_id='TSINMBAR' and c2_tipo_recibo_id='CS'";
					if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub delete_cobal')}
				}
				if($otro>0){
					my $query = "DELETE FROM sdd_inmueble_abonos WHERE i8_inmueble_id='$cuenta' and i8_periodo='$periodo' and c8_tributo_id='TSINMBAR' and c2_tipo_recibo_id='CS'";
					if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub delete_cobal')}
					my $query1 = "DELETE FROM sdd_inmueble_abonos WHERE i8_inmueble_id='$cuenta' and i8_periodo='$periodo' and c8_tributo_id='TSINMCAL' and c2_tipo_recibo_id='CS'";
					if ( $dbh->Sql($query1) ){SQL_fail($query1,$dbh,'sub delete_cobal')}					
				}				
			}
		}else{
			my $query = "DELETE FROM sdd_inmueble_abonos
						 WHERE i8_inmueble_id='$cobales{$k}[0]' and i8_periodo='$cobales{$k}[1]' and c8_tributo_id='$cobales{$k}[2]' and c2_tipo_recibo_id='CS'
						 ";
			if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub delete_cobal')}
		}
	}
}
sub construct_page{
	open (DATA, "$PATH$FILE");
	while ($line = <DATA>){
	  $line =~ s/{lan}/$lan/g;
	  $line =~ s/{appid}/$appid/g;
	  print "$line";
	}
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
sub get_tributos {
    my($id,$per)=@_;
    my $count	= 0; 
    my %hash	= ();
    my $ano		= substr($per,0,4);
    my $mes		= substr($per,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;
    my $sql = "SELECT nic,unicom,medidor,cuenta,aseo,alumbrado,desecho,otro,actualizacion,calle,barrido,cuota_pendiente,periodo
			   FROM cargas_caess
			   WHERE cuenta='$id' and periodo='$cuota_per'
			  ";
	#print "$sql <br>";
    if ( $dbh->Sql($sql) ){SQL_fail($dbh,$sql,'sub get_tributos')};
    while($dbh->FetchRow()){
		my @row	 = $dbh->Data();	
		$hash{$count} = [@row];
		$count++;
    }
    return %hash;
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#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;
}
#-------------------------------------------------------------------------