#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Web Software SA
# Date In: 2016-03-18
# Updates: 2016-03-18
# Author:Rodolfo Machon
# File Name: recibos_abonados_p.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use Win32::ODBC;
use CGI::Carp qw/fatalsToBrowser warningsToBrowser/;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$cgiparms 	= new CGI;
$lan	  	= $cgiparms->param('lan');
$appid	  	= $cgiparms->param('appid');
$dt_desde 	= $cgiparms->param('dt_desde');
$dt_hasta 	= $cgiparms->param('dt_hasta');
$i2_cajero_id	= $cgiparms->param('i2_cajero_id');
$i8_cajero_id	= $cgiparms->param('i8_cajero_id');
$i8_emisor_id	= $cgiparms->param('i8_emisor_id');
$i2_tipo_departamento_id	= $cgiparms->param('i2_tipo_departamento_id');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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 ($appid eq ""){print "Your are not passing APPID parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH 	    = "../".$lan."/";
$file       = $lan."_recibos_abonados_p.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
$user			= get_remote_user();
%recibos		= get_recibos();
%recibos_multas	= get_recibos_multas();
%abonos			= get_abonos();
%abonos_multas	= get_abonos_multas();
# INTEGRATION
#
# PRESENTATION
$table		   = construct_table();
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_remote_user {
        local($username) = $ENV{"REMOTE_USER"};
        local($identify) = $ENV{"REMOTE_IDENT"};
        local($user) = $username || $identify;
        return($user);
}
sub get_recibos {
	my %hash   = ();
	my %hash1  = ();
	my $filtro = "";
	if ($i2_cajero_id ne "0") {$filtro .= " and a.i2_cajero_id='$i2_cajero_id'"}
	if ($i8_cajero_id ne "0") {$filtro .= " and a.i8_cajero_id='$i8_cajero_id'"}
	if ($i8_emisor_id ne "0") {$filtro .= " and a.i8_emisor_id='$i8_emisor_id'"}
	#if ($i2_tipo_departamento_id ne "0") {$filtro .= " and a.i2_tipo_departamento_id='$i2_tipo_departamento_id'"}	
	my $sql    = "SELECT a.c16_recibo_id
						,a.dt_emision
						,c.v64_tipo_departamento
						,a.i8_contribuyente_id
						,a.v128_contribuyente
						,a.c3_tipo_generador_id
						,a.i8_expediente_id
						,(b.d82_cantidad*b.d82_tarifa) as TOTAL
					FROM sdm_recibos a
					INNER JOIN sdd_recibos b ON a.c16_recibo_id=b.c16_recibo_id
					LEFT JOIN sdm_tipo_departamentos c ON a.i2_tipo_departamento_id=c.i2_tipo_departamento_id
					WHERE a.c3_tipo_generador_id IN ('INM','EMP') and a.dt_emision >= '$dt_desde' and a.dt_emision <= '$dt_hasta' $filtro
					ORDER BY a.c16_recibo_id+0
		       ";
	if ( $dbh->Sql($sql) ){SQL_fail($sql,$dbh,'sub get_recibos')};
	while($dbh->FetchRow()){
	    my @row = $dbh->Data();
	    $hash{$row[0]}   = [@row];
	    $hash1{$row[0]} += $row[7];
	}
	foreach my $k (sort keys %hash){
	    my @arr   = @{$hash{$k}};
	    my $rec_t = round($hash1{$k},2);
	    pop(@arr);
	    push(@arr,$rec_t);
	    $hash{$k} =[@arr];
	}
	return %hash;
}
sub get_recibos_multas {
	my %hash   = ();
	my $filtro = "";
	if ($i2_cajero_id ne "0") {$filtro .= " and a.i2_cajero_id='$i2_cajero_id'"}
	if ($i8_cajero_id ne "0") {$filtro .= " and a.i8_cajero_id='$i8_cajero_id'"}
	if ($i8_emisor_id ne "0") {$filtro .= " and a.i8_emisor_id='$i8_emisor_id'"}
	if ($i2_tipo_departamento_id ne "0") {$filtro .= " and a.i2_tipo_departamento_id='$i2_tipo_departamento_id'"}	
	my $sql    = "SELECT a.c16_recibo_id
						 ,b.d82_tarifa
					FROM sdm_recibos a
					INNER JOIN sdd_recibos b ON a.c16_recibo_id=b.c16_recibo_id
					LEFT JOIN sdm_tipo_departamentos c ON a.i2_tipo_departamento_id=c.i2_tipo_departamento_id
					WHERE a.c3_tipo_generador_id IN ('INM','EMP') and a.dt_emision >= '$dt_desde' and a.dt_emision <= '$dt_hasta' and substring(b.c16_tarifa_id,1,8)='ACINMMUL' $filtro
					ORDER BY a.c16_recibo_id+0
		       ";
	if ( $dbh->Sql($sql) ){SQL_fail($sql,$dbh,'sub get_recibos')};
	while($dbh->FetchRow()){
	    my @row = $dbh->Data();
	    $hash{$row[0]} = $row[1];
	}
	return %hash;
}
sub get_abonos {
	my %hash   = ();
	foreach my $k (sort keys %recibos){
		my $sql    = "SELECT a.i8_empresa_id,a.d82_abono FROM sdd_empresa_abonos a WHERE a.c16_recibo_id='$k' and a.c3_tipo_abono_id IN ('VAL','FIE','INT','MUL')";
		if ( $dbh->Sql($sql) ){SQL_fail($sql,$dbh,'sub get_abonos')};
		while($dbh->FetchRow()){
			my @row = $dbh->Data();
			$hash{$k}{$row[0]} += $row[1];
		}
		my $sql1    = "SELECT a.i8_inmueble_id,a.d82_abono FROM sdd_inmueble_abonos a WHERE a.c16_recibo_id='$k' and a.c3_tipo_abono_id IN ('VAL','FIE','INT','MUL')";
		if ( $dbh->Sql($sql1) ){SQL_fail($sql1,$dbh,'sub get_abonos')};
		while($dbh->FetchRow()){
			my @row = $dbh->Data();
			$hash{$k}{$row[0]} += $row[1];
		}
	}
	return %hash;
}
sub get_abonos_multas {
	my %hash   = ();
	foreach my $k (sort keys %recibos){
		my $sql    = "SELECT a.i8_empresa_id,a.d82_abono FROM sdd_empresa_abonos a WHERE a.c16_recibo_id='$k' and a.c3_tipo_abono_id IN ('MUL')";
		if ( $dbh->Sql($sql) ){SQL_fail($sql,$dbh,'sub get_abonos')};
		while($dbh->FetchRow()){
			my @row = $dbh->Data();
			$hash{$k} += $row[1];
		}
		my $sql1    = "SELECT a.i8_inmueble_id,a.d82_abono FROM sdd_inmueble_abonos a WHERE a.c16_recibo_id='$k' and a.c3_tipo_abono_id IN ('MUL')";
		if ( $dbh->Sql($sql1) ){SQL_fail($sql1,$dbh,'sub get_abonos')};
		while($dbh->FetchRow()){
			my @row = $dbh->Data();
			$hash{$k} += $row[1];
		}
	}
	return %hash;
}
sub construct_table {
    my $count   = 1;
    my $total   = 0;
    my $table   = "";
    my %thead_h = ();
    my %tfoot_h = ();    
    my $thead_s = "";
    my $tfoot_s = "";    
    my $rowset  = "";
    my $thead   = "<thead><tr><TD>No</TD><TD>RECIBO</TD><TD>FECHA</TD><TD>DEP.EMISOR</TD><TD>ID</TD><TD>CONTRIBUYENTE</TD><TD>GENERADOR</TD><TD>ID</TD><TD>CAJA</TD><TD>ABONADO</TD><TD>EXPEDIENTE</TD><TD>ACCION</TD></tr></thead>";
    my $tfoot   = "<tfoot><tr><td colspan=7><td>TOTAL</td><td>{total}</td></tr></tfoot>";    
    foreach my $k (sort numeros keys %recibos){
		my $abono 	= "";
		my $exped	= "";
		my $accion 	= "";
		my @arr 	= @{$recibos{$k}};
		my $row 	= join("</td><td>",@arr);

		if ($abonos{$k}){
			foreach my $l (sort keys %{$abonos{$k}}){
				$abono += $abonos{$k}{$l};
				$exped .= "$l,";
			}
		}
		$abono = round($abono,2);
		

		if ($recibos_multas{$k} eq "2.86" && $abono>0) {
			#print "$abono > $abonos_multas{$k} > $recibos_multas{$k} <br>";
			$abono = round(($abono-$abonos_multas{$k}+$recibos_multas{$k}),2);
		}
		if ($user eq "admin" || $user eq "imaldonado") {
			if ($abono==0) {$accion = "<input type='button' value='ABONAR' class='button' onClick=Abonar('$arr[5]','$arr[6]','$k','$arr[1]','$abono')>"}
			if (($abono-$arr[7])!=0 && $abono>0) {$accion = "<input type='button' value='VERIFICAR' class='button' onClick=Verificar('$arr[5]','$arr[6]','$k')>"}
        }
		$exped = substr($exped,0,length($exped)-1);
		if ($exped>1 && $abono>1) {$abono=$recibos{$k}[7]}
		$row    = "<tr><td><a href=javascript:OpenLink('$k','$recibos{$k}[1]')>$count</a></td><td>".$row."</td><td>$abono</td><td>$exped</td><td>$accion</td></tr>";
		$rowset.= $row;
		$total += $arr[7];
		$count++;
	}
    $total  = round($total,2);
    $thead  =~ s/\{thead_s}/$thead_s/g;
    $tfoot  =~ s/\{total}/$total/g;
    $rowset = "<tbody>".$rowset."</tbody>";
    $table  = "<table id=grid>".$thead.$rowset.$tfoot."</table>";
    $table  = convertir_tildes($table);
    return $table;
}
sub construct_page {
	my $caja = "";
	if($i2_cajero_id ne "0"){$caja = "CAJA $i2_cajero_id"}
	my $cajero = "";
	if($i8_cajero_id ne "0"){$cajero = "CAJA $i8_cajero_id"}
	my $emisor = "";
	if($i8_emisor_id ne "0"){$emisor = "CAJA $i8_emisor_id"}
	my $departamento = "";
	if($i2_tipo_departamento_id ne "0"){$departamento = "CAJA $i2_tipo_departamento_id"}

	open (DATA, "$PATH$file");
	while ($line = <DATA>) {	
		$line =~ s/\{lan}/$lan/g;
		$line =~ s/\{appid}/$appid/g;
		$line =~ s/\{table}/$table/g;
		$line =~ s/\{cajero}/$cajero/g;
		$line =~ s/\{caja}/$caja/g;
		$line =~ s/\{emisor}/$emisor/g;
		$line =~ s/\{departamento}/$departamento/g;
		$line =~ s/\{dt_desde}/$dt_desde/g;
		$line =~ s/\{dt_hasta}/$dt_hasta/g;
		print "$line";
	}
}
#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub numeros {$a<=>$b}
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;
}
sub convertir_tildes{
      my ($str) = @_;
      $str =~ s/\Ã±/ñ/g;
      $str =~ s/\Ã/Ñ/g;
      $str =~ s/\Ã‘/Ñ/g;      
      $str =~ s/\Ã¡/á/g;
      $str =~ s/\Ã©/é/g;
      $str =~ s/\Ã­/í/g;
      $str =~ s/\Ã³/ó/g;
      $str =~ s/\Ãº/ú/g;
      return $str;
}
#-------------------------------------------------------------------------