#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
use Win32::ODBC;
use CGI;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#-------------------------------------------------------------------
$form 		= new CGI;
$lan		= $form->param('lan');
$appid		= $form->param('appid');
$clase		= $form->param('clase');
$c11_sector_id	= $form->param('c11_sector_id');

#---------------------------------------------------------------
#-------------------------------------------------------------------
#Define los subdirectorios a utilizar en el programa
$PATH	= "../".$lan."/";
$file   = "es_mora_sector_detail_rept.html";          
#---------------------------------------------------------------
#---------------------------------------------------------------
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();
exit;

#-------------------------------------------------------------------------
# AUXILIAR-SUBROUTINES

#funcion para obtener el inmueble
sub get_records {
        ##avenidas
        $tr_avenidas= "<tr><td class=tdheader2 >No.</td>
        <td class=tdheader2>AVENIDA</td><td class=tdheader2>EXP_MORA</td><td class=tdheader2>MORA</td></TR>";
        $sql="select c4_avenida_id,v64_avenida from sdd_sector_avenidas where c11_sector_id='$c11_sector_id' order by 2 asc";
        $cnt = 1;
	if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records - obtener Numero Inmuebles')};
	while($db->FetchRow())
	{
		undef my @row1;
		@row1		= $db->Data();
                $c4_avenida_id  = $row1[0];
		$v64_avenida    = $row1[1];
		$expedientes    = get_count_expedientes($c11_sector_id,$c4_avenida_id,'c4_avenida_id');
		$mora    	= get_mora($c11_sector_id,$c4_avenida_id,'c4_avenida_id');
                $tr_avenidas.= "<tr><td class=my_style2>$cnt</td>
                <td class=my_style2>$v64_avenida</td>
		<td class=my_style2><a href=javascript:OpenMoraSectorDetail('$c11_sector_id',$c4_avenida_id,'c4_calle_id','1')>$expedientes&nbsp;</a></td>
		<td class=my_style2><a href=javascript:OpenMoraSectorDetail('$c11_sector_id',$c4_avenida_id,'c4_calle_id','2')>$mora&nbsp;</td></tr>";                 
                $cnt = $cnt + 1;
	}
	
        ##calles
        $tr_calles= "<tr><td class=tdheader2 >No.</td>
        <td class=tdheader2>CALLE</td><td class=tdheader2>EXP_MORA</td><td class=tdheader2>MORA</td></TR>";
        $sql="select c4_calle_id,v64_calle from sdd_sector_calles where c11_sector_id='$c11_sector_id' order by 2 asc";
        $cnt = 1;
	if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records - obtener Numero Inmuebles')};
	while($db->FetchRow())
	{
		undef my @row1;
		@row1		= $db->Data();
                $c4_calle_id   	= $row1[0];
		$v64_calle     	= $row1[1];
		$expedientes    = get_count_expedientes($c11_sector_id,$c4_calle_id,'c4_calle_id');
		$mora    	= get_mora($c11_sector_id,$c4_calle_id,'c4_calle_id');
                $tr_calles.= "<tr><td class=my_style2>$cnt</td>
                <td class=my_style2>$v64_calle</td>
		<td class=my_style2><a href=javascript:OpenMoraSectorDetail('$c11_sector_id',$c4_calle_id,'c4_calle_id','1')>$expedientes&nbsp;</a></td>
		<td class=my_style2><a href=javascript:OpenMoraSectorDetail('$c11_sector_id',$c4_calle_id,'c4_calle_id','2')>$mora&nbsp;</td></tr>";                 
                $cnt = $cnt + 1;
	}
        
	##pasajes
        $tr_pasajes= "<tr><td class=tdheader2 >No.</td>
        <td class=tdheader2>PASAJE</td><td class=tdheader2>EXP_MORA</td><td class=tdheader2>MORA</td></TR>";
        $sql="select c2_pasaje_id,v64_pasaje from sdd_sector_pasajes where c11_sector_id='$c11_sector_id' order by 2 asc";
        $cnt = 1;
	if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records - obtener Numero Inmuebles')};
	while($db->FetchRow())
	{
		undef my @row1;
		@row1		= $db->Data();
                $c2_pasaje_id   = $row1[0];
		$v64_pasaje    	= $row1[1];
		$expedientes    = get_count_expedientes($c11_sector_id,$c2_pasaje_id,'c2_pasaje_id');
		$mora    	= get_mora($c11_sector_id,$c2_pasaje_id,'c2_pasaje_id');
                $tr_pasajes.= "<tr><td class=my_style2>$cnt</td>
                <td class=my_style2>$v64_pasaje</td>
		<td class=my_style2><a href=javascript:OpenMoraSectorDetail('$c11_sector_id',$c2_pasaje_id,'c2_pasaje_id','1')>$expedientes&nbsp;</a></td>
		<td class=my_style2><a href=javascript:OpenMoraSectorDetail('$c11_sector_id',$c2_pasaje_id,'c2_pasaje_id','2')>$mora&nbsp;</td></tr>";                 
                $cnt = $cnt + 1;
	}
	
        ##block 
        $tr_blocks= "<tr><td class=tdheader2 >No.</td>
        <td class=tdheader2>BLOCK</td><td class=tdheader2>EXP_MORA</td><td class=tdheader2>MORA</td></TR>";
        $sql="select c2_block_id,v64_block from sdd_sector_blocks where c11_sector_id='$c11_sector_id' order by 2 asc";
        $cnt = 1;
	if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records - obtener Numero Inmuebles')};
	while($db->FetchRow())
	{
		undef my @row1;
		@row1		= $db->Data();
                $c2_block_id   	= $row1[0];
		$v64_block     	= $row1[1];
		$expedientes    = get_count_expedientes($c11_sector_id,$c2_block_id,'c2_block_id');
		$mora    	= get_mora($c11_sector_id,$c2_block_id,'c2_block_id');
                $tr_blocks.= "<tr><td class=my_style2>$cnt</td>
                <td class=my_style2>$v64_block</td>
		<td class=my_style2><a href=javascript:OpenMoraSectorDetail('$c11_sector_id',$c4_calle_id,'c2_block_id','1')>$expedientes&nbsp;</a></td>
		<td class=my_style2><a href=javascript:OpenMoraSectorDetail('$c11_sector_id',$c4_calle_id,'c2_block_id','2')>$mora&nbsp;</td></tr>";                 
                $cnt = $cnt + 1;
	}
	
        ##poligonos 
        $tr_poligonos= "<tr><td class=tdheader2 >No.</td>
        <td class=tdheader2>POLIGONO</td><td class=tdheader2>EXP_MORA</td><td class=tdheader2>MORA</td></TR>";
        $sql="select c2_poligono_id,v64_poligono from sdd_sector_poligonos where c11_sector_id='$c11_sector_id' order by 2 asc";
        $cnt = 1;
	if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records - obtener Numero Inmuebles')};
	while($db->FetchRow())
	{
		undef my @row1;
		@row1		= $db->Data();
                $c2_poligono_id   	= $row1[0];
		$v64_poligono     	= $row1[1];
		$expedientes    = get_count_expedientes($c11_sector_id,$c2_poligono_id,'c2_poligono_id');
		$mora    	= get_mora($c11_sector_id,$c2_poligono_id,'c2_poligono_id');
                $tr_poligonos.= "<tr><td class=my_style2>$cnt</td>
                <td class=my_style2>$v64_poligono</td>
		<td class=my_style2><a href=javascript:OpenMoraSectorDetail('$c11_sector_id',$c2_poligono_id,'c2_poligono_id','1')>$expedientes&nbsp;</a></td>
		<td class=my_style2><a href=javascript:OpenMoraSectorDetail('$c11_sector_id',$c2_poligono_id,'c2_poligono_id','2')>$mora&nbsp;</td></tr>";                 
                $cnt = $cnt + 1;
	}

        ##Sendas
	$tr_sendas= "<tr><td class=tdheader2 >No.</td>
        <td class=tdheader2>SENDA</td><td class=tdheader2>EXP_MORA</td><td class=tdheader2>MORA</td></TR>";
        $sql="select c2_senda_id,v64_senda from sdd_sector_sendas where c11_sector_id='$c11_sector_id' order by 2 asc";
        $cnt = 1;
	if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records - obtener Numero Inmuebles')};
	while($db->FetchRow())
	{
		undef my @row1;
		@row1		= $db->Data();
                $c2_senda_id   	= $row1[0];
		$v64_senda     	= $row1[1];
		$expedientes    = get_count_expedientes($c11_sector_id,$c4_calle_id,'c2_senda_id');
		$mora    	= get_mora($c11_sector_id,$c2_senda_id,'c2_senda_id');
                $tr_sendas.= "<tr><td class=my_style2>$cnt</td>
                <td class=my_style2>$v64_senda</td>
		<td class=my_style2><a href=javascript:OpenMoraSectorDetail('$c11_sector_id',$c2_senda_id,'c2_senda_id','1')>$expedientes&nbsp;</a></td>
		<td class=my_style2><a href=javascript:OpenMoraSectorDetail('$c11_sector_id',$c2_senda_id,'c2_senda_id','2')>$mora&nbsp;</td></tr>";                 
                $cnt = $cnt + 1;
	}


        $sql="select v64_sector from sdm_sectores where c11_sector_id='$c11_sector_id'";
        if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records - obtener Numero Inmuebles')};
	while($db->FetchRow())
	{
		undef my @row1;
		@row1	= $db->Data();
                $v64_sector   = $row1[0];
        }
}
sub get_count_expedientes{
    my($c11_sector_id,$field_value,$field)=@_;
    $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 $field = $field_value and a.i4_dias_mora>0";
    if ( $db1->Sql($sql) ){SQL_fail($sql,$db1,'sub get_count_expedientes')};
    while($db1->FetchRow())
    {
		undef my @row1;
		@row	= $db1->Data();	
    }
    return ($row[0]);

}
sub get_mora{
        my($c11_sector_id,$field_value,$field)=@_;
        $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 $field=$field_value 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 construct_page {
	open (DATA, "$PATH$file");
        #print "$PATH$file";
	while ($line = <DATA>) {	
		$line =~ s/\{tr_avenidas}/$tr_avenidas/g;
                $line =~ s/\{tr_calles}/$tr_calles/g;
                $line =~ s/\{tr_pasajes}/$tr_pasajes/g;
                $line =~ s/\{tr_blocks}/$tr_blocks/g;
                $line =~ s/\{tr_poligonos}/$tr_poligonos/g;
                $line =~ s/\{tr_sendas}/$tr_sendas/g;
                $line =~ s/\{appid}/$appid/g;
		$line =~ s/\{clase}/$clase/g;
                $line =~ s/\{c11_sector_id}/$c11_sector_id/g;
                $line =~ s/\{v64_sector}/$v64_sector/g;
                $line =~ s/\{lan}/$lan/g;
		print "$line";
	}
}
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Error: ".$dbh->Error()."\n<br>";
    print "Query: ".$query."<br>";    
    $dbh->Close();
    exit;
}
#funcion para crear la pagina

