#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
use CGI;
use DBI;
use utf8;
use Encode;
use Encode qw/is_utf8 decode/;
use CGI::Carp qw(fatalsToBrowser);
#-------------------------------------------------------------------
#-------------------------------------------------------------------
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------
#-------------------------------------------------------------------
$form 		= new CGI;
$lan		= $form->param('lan');
$appid		= $form->param('appid');
$clase		= $form->param('clase');
$table 		= "<table id='grid'>";
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#Define los subdirectorios a utilizar en el programa
$PATH	= "../".$lan."/";
$file   = "es_generar_mora_sector.html";          
#-------------------------------------------------------------------
#-------------------------------------------------------------------
$dbh 	  = DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
if (!$dbh) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
$dbh1 	  = DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
if (!$dbh1) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
#-------------------------------------------------------------------
#-------------------------------------------------------------------
get_records();
construct_page();
exit;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
sub get_records {
	$table.= "<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>";
        $sql = "SELECT a.c11_sector_id, b.v64_tipo_sector, a.v64_sector
		FROM sdm_sectores a
		INNER JOIN sdm_tipo_sectores b
		ON a.i2_tipo_sector_id = b.i2_tipo_sector_id
		ORDER BY a.c11_sector_id";
        $cnt = 1;
	my $sth  = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_records');
	while (my @row = $sth->fetchrow_array) {
        $c11_sector_id  = $row[0];
        $v64_tipo_sector= $row[1];
		$v64_sector     = $row[2];
        $total_inm      = get_count_expedientes($c11_sector_id,'');
		if ($total_inm>0) {
			$total_inm_mora = get_count_expedientes($c11_sector_id,'mora');
			$dt_generado    = get_generado($c11_sector_id,'');		
			$table	       .= "<tr><td>$cnt</td>
								<td>$c11_sector_id</td>
								<td>$v64_tipo_sector</td>
								<td>$v64_sector</td>
								<td>$total_inm</td>
								<td>$total_inm_mora</td>
								<td>$dt_generado</td>							
								<td><input type=button id=generar name=dt_vencimiento value='Generar' onClick=GenerarMoraSector('$c11_sector_id')></td>
							 </tr>";
			$cnt++;
		}
	}
    $table.= "</table>";
}
sub get_count_expedientes {
	my($c11_sector_id, $type)=@_;
	my $rows = 0; 
	my $sql = "select count(*) from sdm_".$clase."s where c11_sector_id = '$c11_sector_id' and c16_estado='ACEPTADO'";
	if($type eq "mora"){
		$sql = "SELECT count(distinct(a.i8_".$clase."_id))
			FROM sdd_".$clase."_estados_mensuales a
			INNER JOIN sdm_".$clase."s  b
			ON a.i8_".$clase."_id=b.i8_".$clase."_id
			WHERE b.c11_sector_id='$c11_sector_id' and a.i4_dias_mora>0";
	}
	my $sth1  = $dbh1->prepare($sql);
	$sth1->execute or die SQL_fail($sql,$dbh1,'get_count_expedientes');
	while (my @row = $sth1->fetchrow_array) {
			$rows =  $row[0];
	}
	return $rows;
}
sub get_mora_sector {
	my($c11_sector_id)=@_;
	my $rows = 0; 
	my $sql="select (sum(a.d82_val)+sum(a.d82_fie)+sum(a.d82_int)+sum(a.d82_mul)) from sdd_".$clase."_estados_mensuales a
	inner join sdm_".$clase."s b on a.i8_".$clase."_id = b.i8_".$clase."_id where b.c11_sector_id='$c11_sector_id' and a.i4_dias_mora>0";
 	my $sth1  = $dbh1->prepare($sql);
	$sth1->execute or die SQL_fail($sql,$dbh1,'get_count_expedientes');
	while (my @row = $sth1->fetchrow_array) {
			$rows =  $row[0];
	}
	return $rows;
}
sub get_generado {
	my($c11_sector_id)=@_;
	my $rows = 0; 
	my $sql="select a.dt_generado from sdd_".$clase."_estados_mensuales a
	inner join sdm_".$clase."s b on a.i8_".$clase."_id = b.i8_".$clase."_id where b.c11_sector_id='$c11_sector_id' and a.i4_dias_mora>0
	limit 1 
	";
 	my $sth1  = $dbh1->prepare($sql);
	$sth1->execute or die SQL_fail($sql,$dbh1,'get_count_expedientes');
	while (my @row = $sth1->fetchrow_array) {
			$rows =  $row[0];
	}
	return $rows;
}
sub construct_page {
	open (DATA, "$PATH$file");
        #print "$PATH$file";
	while ($line = <DATA>)
        {	
		$line =~ s/\{table}/$table/g;
                $line =~ s/\{appid}/$appid/g;
                $line =~ s/\{lan}/$lan/g;
                $line =~ s/\{clase}/$clase/g;
		print "$line";
	}
}
sub SQL_fail {
    my ($dbh,$query,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}