#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
use Win32::ODBC;
use CGI;
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');
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#Define los subdirectorios a utilizar en el programa
$PATH	= "../".$lan."/";
$file   = "es_generar_mora_sector_empresa.html";          
$table 	= "<table id='grid'>";
#-------------------------------------------------------------------
#-------------------------------------------------------------------
if ( !( $db = new Win32::ODBC($appid) ) ) {print "Error connecting to $db <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
if ( !( $db1 = new Win32::ODBC($appid) ) ) {print "Error connecting to $db1 <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
#-------------------------------------------------------------------
get_records();
construct_page();
$db->Close();
$db1->Close();
exit;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
sub get_records {
	$table.= "<tr>
				<td>No.</td>
				<TD>SECTOR ID</TD>	
				<TD>TIPO SECTOR</TD>
				<td>DESCRIPCION</td>
				<td>TOTAL&nbsp;</td>
				<td>EN&nbsp;MORA</td>
				<td>GENERADO</td>				
				<td>GENERAR</td>
			</tr>";
		$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";
        $cnt = 1;
	if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records - Sectores')};
	while($db->FetchRow()) 	{
		undef my @row1;
		@row1		= $db->Data();
        $c11_sector_id  = $row1[0];
        $v64_tipo_sector= $row1[1];
		$v64_sector     = $row1[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 name=dt_vencimiento value='Generar' onClick=GenerarMoraSector('$c11_sector_id')></td>
							</tr>";
            $cnt = $cnt + 1;
		}
	}
        $table.= "</table>";
}
sub get_count_expedientes {
	my($c11_sector_id, $type)=@_;
	$sql = "SELECT  count(a.i8_empresa_id)
		FROM sdm_empresas a
		INNER JOIN  sdd_empresa_ubicacion b
		ON a.i8_empresa_id = b.i8_empresa_id
		WHERE a.c16_estado='ACEPTADO' and b.c11_sector_id='$c11_sector_id'";
	if($type eq "mora"){
		$sql = "SELECT count(distinct(a.i8_empresa_id))
			FROM sdd_empresa_estados_mensuales a
			INNER JOIN sdm_empresas  b
			ON a.i8_empresa_id=b.i8_empresa_id
			INNER JOIN sdd_empresa_ubicacion c
			ON b.`i8_empresa_id`=c.`i8_empresa_id`
			WHERE c.c11_sector_id='$c11_sector_id' and a.i4_dias_mora>0";
	}
	if ( $db1->Sql($sql) ){SQL_fail($db1,$sql,'sub get_records - obtener Numero expedientes')};
	while($db1->FetchRow()) {
		undef my @row1;
		@row	= $db1->Data();	
	}
	return ($row[0]);
}
sub get_mora_sector {
        my($c11_sector_id)=@_;
        $sql="select (sum(a.d82_val)+sum(a.d82_fie)+sum(a.d82_int)+sum(a.d82_mul))
		from sdd_empresa_estados_mensuales a
		inner join sdm_empresas b on a.i8_empresa_id = b.i8_empresa_id
		inner join `sdd_empresa_ubicacion` c
		on b.i8_empresa_id=c.`i8_empresa_id`
		where c.c11_sector_id='$c11_sector_id' and a.i4_dias_mora>0";
        if ( $db1->Sql($sql) ){SQL_fail($sql,$db1,'Get Mora sector $c11_Sector_id')};
        while($db1->FetchRow()){
                    undef my @row1;
                    @row	= $db1->Data();
        }
        return ($row[0]);
}
sub get_generado {
        my($c11_sector_id)=@_;
        my $sql = "select a.dt_generado
					from sdd_".$clase."_estados_mensuales a
					inner join sdd_".$clase."_ubicacion 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 
				   ";
        if ( $db1->Sql($sql) ){SQL_fail($sql,$db1,'Get Mora sector $c11_Sector_id')};
        while($db1->FetchRow()){
                    undef my @row1;
                    @row	= $db1->Data();	
        }
        return ($row[0]);
}
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 "Error: ".$dbh->Error()."\n<br>";
    print "Query: ".$query."<br>";    
    $dbh->Close();
    exit;
}