#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Information Software SA - Web Software SA
# Date In: 2018-06-30
# Updates: 2018-06-30
# Author:Rodolfo Machon
# File Name: expedientes_financiamientos_rept.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#PERL MODULES
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
binmode STDOUT, ":encoding(utf8)";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#HTML printing activation
print "Content-type: text/html; charset=utf-8\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$cgiparms = new CGI;
$lan	  = $cgiparms->param('lan');
$appid	  = $cgiparms->param('appid');
$clase	  = $cgiparms->param('clase');
$estado	  = $cgiparms->param('estado');
$desde	  = $cgiparms->param('dt_desde');
$hasta	  = $cgiparms->param('dt_hasta');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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 ($clase eq ""){print "Your are not passing CLASE parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH 	= "../".$lan."/";
$file   = "es_expedientes_financiamientos_rept.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";}
$dbh->{LongReadLen} = 512 * 1024;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT

%financiamientos= get_financiamientos();
%contribuyentes	= get_contribuyentes();
# INTEGRATION

# PRESENTATION
if(keys %financiamientos){$table = construct_table($id)}
construct_page();
exit;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# MAIN-SUBROUTINES
sub get_financiamientos {
	my $cnt		= 0;	
	my %hash	= ();
	my $WHERE	= "";
	if ($estado ne "") {
		$WHERE = " WHERE a.c8_estado='$estado'";
	}
	if ($dt_desde ne "") {
		$WHERE .= " AND i6_desde>='dt_desde' ";
		if ($dt_hasta ne "") {
			$WHERE .= "AND i6_hasta<='dt_hasta'";			
		}
	}
	
	my $sql = "SELECT
			a.i8_".$clase."_id,
			a.i8_financiamiento_id,
			a.c8_estado,
			a.i6_desde,
			a.i6_hasta,
			a.i2_cuotas,
			a.i4_periodos_mora,
			a.d82_mora,
			a.d82_cargo_corriente,
			a.c2_fiesta,
			a.c2_interes,
			a.c2_multa,
			a.c2_icc
		   FROM sdd_".$clase."_financiamientos a
		   $WHERE
		   ORDER BY a.i8_financiamiento_id";
	#print "$sql <br>";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_financiamientos');
	while (my @row = $sth->fetchrow_array) {
		$hash{$cnt} = [@row];
		$cnt++;
	}
	return %hash;
}
sub get_contribuyentes {
	my %hash = ();
	foreach my $k (sort keys %financiamientos){
		my	$sql = "SELECT a.i8_".$clase."_id,CONCAT(b.v64_nombres,' ',b.v64_apellidos)
					FROM sdd_".$clase."_contribuyentes a
					LEFT JOIN sdm_contribuyentes b ON a.i8_contribuyente_id=b.i8_contribuyente_id
					WHERE a.i2_tipo_contribuyente_id and a.i8_".$clase."_id='$financiamientos{$k}[0]'; 
				   ";
		#print "$sql <br>";
		my $sth = $dbh->prepare($sql);
		$sth->execute or die SQL_fail($sql,$dbh,'sub get_contribuyentes');
		while (my @row = $sth->fetchrow_array) {
			$hash{$financiamientos{$k}[1]}= [@row];
		}		
	}
    return %hash;
}
sub construct_table {
    my $count   = 1;
    my $table   = "";
    my %tar_cue = ();    
    my %thead_h = ();
    my %tfoot_h = ();    
    my $thead_s = "";
    my $tfoot_s = "";    
    my $rowset  = "";
    my $thead   = "<thead>
			<tr>
				<td>No.</td>
				<td>ID</td>
				<td>INM ID</td>
				<td>CONTRIBUYENTE</td>
				<td>ESTADO</td>
				<td>DESDE</td>
				<td>HASTA</td>
				<td>CUOTAS</td>
				<td>PERIODOS</td>
				<td>MORA</td>
				<td>CORRIENTE</td>
				<td>FIESTAS</td>
				<td>INTERESES</td>
				<td>MULTAS</td>
				<td>CORRIENTE</td>				
			</tr>
		   </thead>";
    foreach my $k (sort {$a<=>$b} keys %financiamientos){
		my $row  = "<td><a href=# onclick=openFinanciamiento('$financiamientos{$k}[1]','$clase')>".$count."</a></td>
					<td>".$financiamientos{$k}[1]."</td>
					<td>".$financiamientos{$k}[0]."</td>
					<td>".$contribuyentes{$financiamientos{$k}[1]}[1]."</td>
					<td>".$financiamientos{$k}[2]."</td>
					<td>".$financiamientos{$k}[3]."</td>
					<td>".$financiamientos{$k}[4]."</td>
					<td>".$financiamientos{$k}[5]."</td>
					<td>".$financiamientos{$k}[6]."</td>
					<td>".$financiamientos{$k}[7]."</td>
					<td>".$financiamientos{$k}[8]."</td>
					<td>".$financiamientos{$k}[9]."</td>
					<td>".$financiamientos{$k}[10]."</td>
					<td>".$financiamientos{$k}[11]."</td>
					<td>".$financiamientos{$k}[12]."</td>
		     ";
		$rowset .= "<tr>".$row."</tr>";
		$count++;	
    }
    $rowset    = "<tbody>".$rowset."</tbody>";
    $table     = "<table id=grid>".$thead.$rowset."</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/\{clase}/$clase/g;
                $line =~ s/\{id}/$id/g;
                $line =~ s/\{v64_nombres}/$v64_nombres/g;
                $line =~ s/\{v64_apellidos}/$v64_apellidos/g;
		$line =~ s/\{table}/$table/g;
		print "$line";
	}
}
#AUXILIAR-SUBROUTINES
sub SQL_fail {
    my ($query,$dbhh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}
#-------------------------------------------------------------------------
