#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2013-01-21
# Updates: 2013-01-21,2016-03-21
# Author:Rodolfo Machon
# File Name: mora_sectores_rept.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use CGI::Carp qw/fatalsToBrowser warningsToBrowser/;
use DBI;
use XML::Simple;
use Data::Dumper;
binmode STDOUT, ":encoding(utf8)";
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html; charset=utf-8\n\n";
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
#GET PARAMETERS
$cgiparms   = new CGI;
$clase 	    = $cgiparms->param('clase');
$i8_periodo = $cgiparms->param('i8_periodo');
$id			= $cgiparms->param('id');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# OPEN DATABASE CONNECTIONS - ODBCs
get_app_data();
open_database_connections();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH 	 = "../".$lan."/";
$file    = $lan."_moras_sectores_rept.html";
if ($id ne "") {
	$i8_periodo = substr($id,3,6);
	if(substr($id,0,3) eq "INM"){$clase = "inmueble"}
	if(substr($id,0,3) eq "EMP"){$clase = "empresa"}	
}
$table_d = "sdr_".$clase."s_mora_detalle_".$i8_periodo;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
$exists = table_exists();
if($exists eq "NO_EXISTE"){
	$table = "FAVOR GENERE LA MORA CORRESPONDIENTE A ESTE MES ANTES, LUEGO REGRESE Y VUELVA A GENERAR EL REPORTE..";
	construct_page();
	exit;
}
%sectores  = get_sectores();
%moras	   = get_moras();
# INTEGRATION
%sectores_moras = get_sectores_moras();
# PRESENTATION
$table 		   = construct_table();
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub table_exists {
	my $tab = "NO_EXISTE";
	my $sql = "SHOW TABLES LIKE '$table_d'";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub table_exists');
	if (my @row = $sth->fetchrow_array) {
		$tab    = $row[0];
	}
	return $tab;
}
sub get_sectores {
    my %hash  = ();
	my $table = "";
    if($clase eq "inmueble"){$table = "sdm_inmuebles"}
    if($clase eq "empresa"){$table = "sdd_empresa_ubicacion"}
    my $sql   = "SELECT a.c11_sector_id,
						b.v64_sector,
						c.v64_tipo_sector
				 FROM $table a
				 LEFT JOIN sdm_sectores b ON a.c11_sector_id=b.c11_sector_id
				 LEFT JOIN sdm_tipo_sectores c	ON b.i2_tipo_sector_id = c.i2_tipo_sector_id
				 ORDER BY a.c11_sector_id";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_sectores');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_moras {
    my %hash  = ();
    my $sql   = "";
    if($clase eq "inmueble"){
		$sql  = "SELECT b.c11_sector_id,
						 SUM(a.d82_val),
						 SUM(a.d82_fie),
						 SUM(a.d82_int),
						 SUM(a.d82_mul)
					FROM $table_d a
					LEFT JOIN sdm_inmuebles b ON a.i8_inmueble_id=b.i8_inmueble_id
					GROUP BY b.c11_sector_id
				";
    }
    if($clase eq "empresa"){
		$sql = "SELECT b.c11_sector_id,
						 SUM(a.d82_val),
						 SUM(a.d82_fie),
						 SUM(a.d82_int),
						 SUM(a.d82_mul)
					FROM $table_d a
					LEFT JOIN sdd_empresa_ubicacion b ON a.i8_empresa_id=b.i8_empresa_id
					GROUP BY b.c11_sector_id
				";
    }
	#print "$sql <br>";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_expedientes');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_sectores_moras {
    my %hash  = ();
    foreach my $k (sort keys %sectores){
		$hash{$k}{'val'} 	= $moras{$k}[1];
		$hash{$k}{'fie'} 	= $moras{$k}[2];
		$hash{$k}{'int'} 	= $moras{$k}[3];
		$hash{$k}{'mul'} 	= $moras{$k}[4];
		$hash{$k}{'tot'} 	= $moras{$k}[1]+$moras{$k}[2]+$moras{$k}[3]+$moras{$k}[4];
    }
    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 $t_val = 0;
	my $t_fie = 0;	
	my $t_int = 0;	
	my $t_mul = 0;	
	my $t_tot = 0;		
    my $thead   = "<thead><tr><td>No.</td><td>ID SECTOR</td><td>TIPO SECTOR</td><td>DESCRIPCION SECTOR</td><td>TRIBUTOS</td><td>FIESTAS</td><td>INTERESES</td><td>MULTAS</td><td>TOTAL</td></tr></thead>";
    my $tfoot   = "<tfoot><tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>{t_val}</td><td>{t_fie}</td><td>{t_int}</td><td>{t_mul}</td><td>{t_tot}</td></tr></tfoot>";
    foreach my $k (sort keys %moras){
		my $tot = $moras{$k}[1]+$moras{$k}[2]+$moras{$k}[3]+$moras{$k}[4];
		my $row   = "	<td><a href=\"javascript:OpenLink('$k','$clase','$i8_periodo')\">$count</a></td>
						<td>".$k."</td>
						<td>".$sectores{$k}[2]."</td>
						<td>".$sectores{$k}[1]."</td>
						<td>".$moras{$k}[1]."</td>
						<td>".$moras{$k}[2]."</td>
						<td>".$moras{$k}[3]."</td>
						<td>".$moras{$k}[4]."</td>
						<td>".$tot."</td>						
					";
		$rowset  	.= "<tr>".$row."</tr>";
		$t_val     	+= $moras{$k}[1];
		$t_fie 		+= $moras{$k}[2];
		$t_int     	+= $moras{$k}[3];
		$t_mul     	+= $moras{$k}[4];
		$t_tot     	+= $tot;
	
		$count++;	
    }

    $tfoot     =~ s/\{t_val}/$t_val/g;
    $tfoot     =~ s/\{t_fie}/$t_fie/g;
    $tfoot     =~ s/\{t_int}/$t_int/g;
    $tfoot     =~ s/\{t_mul}/$t_mul/g;
    $tfoot     =~ s/\{t_tot}/$t_tot/g;				
    $rowset    = "<tbody>".$rowset."</tbody>";
    $table     = "<table id=grid>".$thead.$rowset.$tfoot."</table>";
    return $table;
}
sub construct_table1 {
    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 SECTOR</td><td>TIPO SECTOR</td><td>DESCRIPCION SECTOR</td><td>TRIBUTOS</td><td>FIESTAS</td><td>INTERESES</td><td>MULTAS</td><td>TOTAL</td></tr></thead>";
    my $tfoot   = "<tfoot><tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>{t_val}</td><td>{t_fie}</td><td>{t_int}</td><td>{t_mul}</td><td>{t_tot}</td></tr></tfoot>";
    foreach my $k (sort keys %sectores_moras){
		my $row   = "	<td><a href=\"javascript:OpenLink('$k','$clase','$i8_periodo')\">$count</a></td>
						<td>".$k."</td>
						<td>".$sectores{$k}[2]."</td>
						<td>".$sectores{$k}[1]."</td>
						<td>".$sectores_moras{$k}{'val'}."</td>
						<td>".$sectores_moras{$k}{'fie'}."</td>
						<td>".$sectores_moras{$k}{'int'}."</td>
						<td>".$sectores_moras{$k}{'mul'}."</td>
						<td>".$sectores_moras{$k}{'tot'}."</td>						
					";
		$rowset  	.= "<tr>".$row."</tr>";
		$t_val     	+= $sectores_moras{$k}{'val'};
		$t_fie 		+= $sectores_moras{$k}{'fie'};
		$t_int     	+= $sectores_moras{$k}{'int'};
		$t_mul     	+= $sectores_moras{$k}{'mul'};
		$t_tot     	+= $sectores_moras{$k}{'tot'};
	
		$count++;	
    }

    $tfoot     =~ s/\{t_val}/$t_val/g;
    $tfoot     =~ s/\{t_fie}/$t_fie/g;
    $tfoot     =~ s/\{t_int}/$t_int/g;
    $tfoot     =~ s/\{t_mul}/$t_mul/g;
    $tfoot     =~ s/\{t_tot}/$t_tot/g;				
    $rowset    = "<tbody>".$rowset."</tbody>";
    $table     = "<table id=grid>".$thead.$rowset.$tfoot."</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/\{city}/$city/g;
		$line =~ s/\{state}/$state/g;
        $line =~ s/\{clase}/$clase/g;         
        $line =~ s/\{table}/$table/g;
        $line =~ s/\{i8_periodo}/$i8_periodo/g;
        print "$line";
    }
}
#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub get_app_data {
    my $schema   = "app.data.xml";
    my $xml      = new XML::Simple;
    my $data 	  = $xml->XMLin("../xml/".$schema);
    $appid	  = $data->{portal}->{app};
    $lan	  = $data->{portal}->{lang};
	$city	  = $data->{portal}->{city};
	$state    = $data->{portal}->{state};
}
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}
sub open_database_connections {
	$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;	
}
#-------------------------------------------------------------------------