#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Information Software SA
# Date In: 2005-02-02
# Updates: 2005-02-03,2020-06-15
# Author:Rodolfo Machon
# File Name: generar_mora_sector_empresa.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#PERL MODULES
use CGI;
use DBI;
use utf8;
use Encode;
use Encode qw/is_utf8 decode/;
use CGI::Carp qw(fatalsToBrowser);
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#HTML printing activation
print "Content-type: text/html; charset=utf-8\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$form 		= new CGI;
$lan		= $form->param('lan');
$appid		= $form->param('appid');
$clase		= $form->param('clase');
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#Define los subdirectorios a utilizar en el programa
$PATH	= "../".$lan."/";
$file   = "es_generar_mora_sector_empresa.html";          
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# OPEN DATABASE CONNECTIONS - ODBCs
$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->{LongTruncOk} = 1;
$dbh1 	  = DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
if (!$dbh1) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
$dbh1->{LongTruncOk} = 1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
%sectores 				= get_sectores();
%sector_expedientes 	= get_sector_expedientes();
%sector_expediente_moras= get_sector_expediente_moras();
# INTEGRATION

# PRESENTATION
$table = construct_table();
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------

#SUB-ROUTINES -> LEVEL 1
sub get_sectores{
    my %hash  = ();
	my $sql =  "SELECT a.c11_sector_id,d.v64_tipo_sector,b.v64_sector
				FROM sdd_empresa_ubicacion a
				LEFT JOIN sdm_tipo_sectores d ON a.i2_tipo_sector_id=d.i2_tipo_sector_id
				LEFT JOIN sdm_sectores b ON a.c11_sector_id=b.c11_sector_id
				LEFT JOIN sdm_empresas c ON a.i8_empresa_id=c.i8_empresa_id
				WHERE c.c16_estado='ACEPTADO'
				GROUP 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_sector_expedientes {
	my %hash = ();
	my $sql = "SELECT b.c11_sector_id,count(a.i8_".$clase."_id)
				FROM sdm_empresas a
				INNER JOIN  sdd_empresa_ubicacion b ON a.i8_".$clase."_id = b.i8_".$clase."_id
				WHERE a.c16_estado='ACEPTADO'
				GROUP BY b.c11_sector_id";
	my $sth1  = $dbh1->prepare($sql);
	$sth1->execute or die SQL_fail($sql,$dbh1,'get_count_expedientes');
	while (my @row = $sth1->fetchrow_array) {
		$hash{$row[0]} = $row[1];
	}
	return %hash;
}
sub get_sector_expediente_moras {
	my %hash = ();
	my $sql = "SELECT c.c11_sector_id,count(distinct(a.i8_".$clase."_id)),a.dt_generado
				FROM sdd_".$clase."_estados_mensuales a
				INNER JOIN sdm_".$clase."s  b ON a.i8_".$clase."_id=b.i8_".$clase."_id
				INNER JOIN sdd_empresa_ubicacion c ON b.`i8_".$clase."_id`=c.`i8_".$clase."_id`				
				WHERE a.i4_dias_mora>0
				GROUP BY c.c11_sector_id";	
	my $sth1  = $dbh1->prepare($sql);
	$sth1->execute or die SQL_fail($sql,$dbh1,'get_count_expediente_moras');
	while (my @row = $sth1->fetchrow_array) {
		$hash{$row[0]} = [@row];
	}
	return %hash;
}
sub construct_table {
	my $count   = 1;
    my $table   = "";
    my $rowset  = "";
	my $content = " readonly";
    my $thead   = get_thead();
    my $tfoot   = "";
	foreach my $k (sort keys %sectores){
		my $row = "<td>$count</td>
					<td>$sectores{$k}[0]</td>
					<td>$sectores{$k}[1]</td>
					<td>$sectores{$k}[2]</td>
					<td>$sector_expedientes{$k}</td>
					<td>$sector_expediente_moras{$k}[1]</td>
					<td>$sector_expediente_moras{$k}[2]</td>							
					<td><input type=button id=generar name=dt_vencimiento value='Generar' onClick=GenerarMoraSector('$k')></td>
				   ";
		$rowset  .= "<tr>".$row."</tr>";
		$count++;
	}
    $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/\{table}/$table/g;
	
        print "$line";
    }
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 2
sub get_thead {
    my $thead = "<thead>
					<tr>
						<th>No.</th>
						<th>SECTOR ID</th>	
						<th>TIPO SECTOR</th>
						<th>DESCRIPCION</th>
						<th>TOTAL&nbsp;</th>
						<th>EN&nbsp;MORA</th>
						<th>GENERADO</th>	
						<th>GENERAR</th>
					  </tr>
				</thead>";
	return $thead;
}
#--------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}