#!/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');
$order		= $form->param('order');
##Variables de la tabla
$table = "<table cellspacing=0 cellpadding=0 width=100% border=1 bordercolor=#E5E5E5 style=border-width:0.5>";
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#Define los subdirectorios a utilizar en el programa
$PATH	= "../".$lan."/";
$file   = "es_empresas_sectores_rept.html";          
#-------------------------------------------------------------------
#-------------------------------------------------------------------
if ( !( $db = new Win32::ODBC($appid) ) ) {print "Error connecting to $dsn_tes <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
if ( !( $db1 = new Win32::ODBC($appid) ) ) {print "Error connecting to $dsn_tes1 <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
#-------------------------------------------------------------------
#-------------------------------------------------------------------
get_sectores();
construct_page();
$db->Close();
$db1->Close();
exit;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# AUXILIAR-SUBROUTINES
#funcion para obtener el inmueble
sub get_sectores{
	$pos = 0;
	$table.= "<tr class=tdheader2><td class=tdheader2>No.</td>
	<td class=tdheader2>SECTOR</td>
        <TD class=tdheader2>TIPO</TD>
	<td class=tdheader2>DESCRIPCION</td>
        <td class=tdheader2>TOTAL</td></tr>";
        $sql="SELECT a.c11_sector_id, a.v64_sector, b.v64_tipo_sector from sdm_sectores a inner join sdm_tipo_sectores b on a.i2_tipo_sector_id = b.i2_tipo_sector_id order by 1";
        $cnt = 1;
	if ( $db->Sql($sql) ){SQL_fail($db_tes1,$query01,'sub get_records - obtener Numero Inmuebles')};
	while($db->FetchRow()){
		undef my @row1;
		@row1	= $db->Data();
                $c11_sector_id  = $row1[0];
		$v64_sector     = $row1[1];
                $v64_tipo_sector= $row1[2];
                $total_inm      = get_count_inmueble($c11_sector_id,'');
                $table.= "<tr><td class=my_style2>$cnt</td>
		<td class=my_style2><a href=javascript:OpenDetail('$c11_sector_id')>$c11_sector_id</td>
                <td class=my_style2>$v64_tipo_sector</td>
		<td class=my_style2><a href=javascript:OpenSector('$c11_sector_id')>$v64_sector</a></td>
		<td class=my_style2>$total_inm</td>";
		 $cnt = $cnt + 1;
	}
        $table.="</table>";
        $table.= "</table>";
}
sub get_count_inmueble{
    my($c11_sector_id, $c8_tributo_id)=@_;
    $sql = "select count(*) from sdm_empresas a inner join sdd_empresa_ubicacion b
	    on a.i8_empresa_id=b.i8_empresa_id where b.c11_sector_id = '$c11_sector_id' and a.c16_estado='ACEPTADO'";
    if($c8_tributo_id ne "")
    {$sql = "select count(distinct(b.i8_inmueble_id))
        from sdm_inmuebles a inner join sdd_inmueble_servicios b on a.`i8_inmueble_id` = b.`i8_inmueble_id`
        where a.`c11_sector_id` = '$c11_sector_id' and b.`c8_tributo_id` = '$c8_tributo_id' and b.`c2_gravable` = 'SI'";}
    if ( $db1->Sql($sql) ){SQL_fail($db_tes1,$query01,'sub get_records - obtener Numero Inmuebles')};
    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;
		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;
}