#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2012-11-25
# Updates: 2012-11-25,2013-03-30
# Author:Rodolfo Machon
# File Name: ingresos_cuentas_p.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# 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');
$abono	= $cgiparms->param('abono');
$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."_ingresos_recibos_abonos_p.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT

%recibos 	   = get_recibos();
%recibo_inmueble_abonos = get_recibo_inmueble_abonos();
%recibo_empresa_abonos = get_recibo_empresa_abonos();


#%abono_empresa = get_abono_empresa();
# INTEGRATION
#
# PRESENTATION
$table		   = construct_table();
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
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.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.dt_emision >= '$dt_desde' and a.dt_emision <= '$dt_hasta' $filtro 
		       ORDER BY a.c16_recibo_id+0
		       ";
	#print "$auxSql <br>";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_recibos');
    while (@row = $sth->fetchrow_array) {
	    $hash{$row[0]}   = [@row];
	    $hash1{$row[0]} += $row[6];
	}
	foreach my $k (sort keys %hash){
	    my @arr   = @{$hash{$k}};
	    my $rec_t = sprintf("%.2f",$hash1{$k});
	    pop(@arr);
	    push(@arr,$rec_t);
	    $hash{$k} =[@arr];
	}
	return %hash;
}

sub get_recibo_inmueble_abonos{
	my %hash = ();
	my $str = "";
	foreach my $k (keys %recibos){
		if ($recibos{$k}[0] ne ""){
			$str .= "'".$recibos{$k}[0]."',";
		}
	}
	$str = substr($str,0,length($str)-1);
	my $sql = "SELECT c16_recibo_id
from `sdd_inmueble_abonos`
where c16_recibo_id in ($str)
 group BY c16_recibo_id";
	my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_recibos');
    while(@row = $sth->fetchrow_array){
	    $hash{$row[0]}   = [@row];
	}
	return %hash;
}

sub get_recibo_empresa_abonos{
	my %hash = ();
	my $str = "";
	foreach my $k (keys %recibos){
		if ($recibos{$k}[0] ne ""){
			$str .= "'".$recibos{$k}[0]."',";
		}
	}
	$str = substr($str,0,length($str)-1);
	my $sql = "SELECT c16_recibo_id
from `sdd_empresa_abonos`
where c16_recibo_id in ($str)
 group BY c16_recibo_id";
	my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_recibos');
    while(@row = $sth->fetchrow_array){
	    $hash{$row[0]}   = [@row];
	}
	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>EXP.ID</TD><TD>VALOR</TD><TD>ABONOS</TD></tr></thead>";
    my $tfoot   = "<tfoot><tr><td colspan=6></td><td>TOTAL</td><td>{total}</td></tr></tfoot>";    
    if($abono eq "0"){
		foreach my $k (sort {$a cmp $b} keys %recibos){
			my @arr = @{$recibos{$k}};
			my $row = join("</td><td>",@arr);
			my $str = "NO";
			if($k eq $recibo_inmueble_abonos{$k}[0]){
				$str = "SI"
			}
			if($k eq $recibo_empresa_abonos{$k}[0]){
				$str = "SI"
			}
			
			$row    = "<tr><td><a href=javascript:OpenLink('$k','$recibos{$k}[1]')>$count</a></td><td>".$row."</td><td>".$str."</td></tr>";
			$rowset.= $row;
			$total += $arr[6];
			$count++;	    
		}
	}
	if($abono eq "1"){
		foreach my $k (sort {$a cmp $b} keys %recibos){
			my @arr = @{$recibos{$k}};
			my $str = "NO";
			if($k eq $recibo_inmueble_abonos{$k}[0]){
				$str = "SI"
			}
			if($k eq $recibo_empresa_abonos{$k}[0]){
				$str = "SI"
			}
			if($str eq "NO"){
				my $row = join("</td><td>",@arr);
				$row    = "<tr><td><a href=javascript:OpenLink('$k','$recibos{$k}[1]')>$count</a></td><td>".$row."</td><td>".$str."</td></tr>";
				$rowset.= $row;
				$total += $arr[6];
				$count++;
			}
				    
		}
	}
	if($abono eq "2"){
		foreach my $k (sort {$a cmp $b} keys %recibos){
			my @arr = @{$recibos{$k}};
			my $str = "NO";
			if($k eq $recibo_inmueble_abonos{$k}[0]){
				$str = "SI"
			}
			if($k eq $recibo_empresa_abonos{$k}[0]){
				$str = "SI"
			}
			if($str eq "SI"){
				my $row = join("</td><td>",@arr);
				$row    = "<tr><td><a href=javascript:OpenLink('$k','$recibos{$k}[1]')>$count</a></td><td>".$row."</td><td>".$str."</td></tr>";
				$rowset.= $row;
				$total += $arr[6];
				$count++;
			}
				    
		}
	}
    $total  = sprintf("%.2f",$total);
    $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 {
	
	open (DATA, "$PATH$file");
	while ($line = <DATA>) {	
		$line =~ s/\{lan}/$lan/g;
		$line =~ s/\{appid}/$appid/g;
		$line =~ s/\{table}/$table/g;
		$line =~ s/\{dt_desde}/$dt_desde/g;
		$line =~ s/\{dt_hasta}/$dt_hasta/g;
		print "$line";
	}
}

#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}
sub connect_fail {
    my ($DSN)=@_;
    print "Error connecting to $DSN<br>";
    print "Error:".Win32::ODBC::Error()."<br>";
    exit;
}
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;
}
#-------------------------------------------------------------------------