#!/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('sector');
$estado		= $form->param('estado');
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#Define los subdirectorios a utilizar en el programa
$PATH	= "../".$lan."/";
$file   = "es_mora_noficaciones_sector_rept.html";          
$table = "<table id=Grid cellspacing=0 cellpadding=0 width=100% border=1 bordercolor=#E5E5E5 style=border-width:0.5>";
#-------------------------------------------------------------------
#-------------------------------------------------------------------
if ( !( $db = new Win32::ODBC($appid) ) ) {print "Error connecting to $appid <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
if ( !( $db1 = new Win32::ODBC($appid) ) ) {print "Error connecting to $appid <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
if ( !( $db2 = new Win32::ODBC($appid) ) ) {print "Error connecting to $appid <br>";print "Error: " . Win32::ODBC::Error() . "<br>";exit;}
#-------------------------------------------------------------------
#-------------------------------------------------------------------
$sector = get_sector();
generar_expedientes();
construct_page();
$db->Close();
exit;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# AUXILIAR-SUBROUTINES
sub get_sector {
	my $sec = "";
	my $sql = "select v64_sector from sdm_sectores where c11_sector_id='$c11_sector_id'";
	if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_sector')};
	if($db->FetchRow()) {
		undef my @row;
		@row = $db->Data();
                $sec = $row[0];
	}
	return $sec;
}
sub generar_expedientes {
	my $contador = 1;
 	$table.= "<tr class=tdheader2>
			<td class=tdheader2>No.</td>
			<TD class=tdheader2>EXPEDIENTE</TD>
			<td class=tdheader2>CONTRIBUYENTE</td>
			<td class=tdheader2>DIRECCION</td>			
			<td class=tdheader2>PRIMERA</td>
			<td class=tdheader2>SEGUNDA</td>
			<td class=tdheader2>TERCERA</td>
			<td class=tdheader2>LLAMADA</td>
			</tr>";
	my $sql = "select b.i8_".$clase."_id from sdd_".$clase."_administrativo a,sdm_".$clase."s b where a.i8_".$clase."_id=b.i8_".$clase."_id and b.c11_sector_id = '$c11_sector_id' and a.c8_gestion_estado='ACTIVO' limit 1";
	if($clase eq "empresa"){
		$sql = "select a.i8_empresa_id from `sdd_empresa_ubicacion` a
			left join `sdm_empresas` b on a.`i8_empresa_id`=b.`i8_empresa_id`
			left join `sdd_empresa_administrativo` c on b.`i8_empresa_id`=c.`i8_empresa_id`
			where a.`c11_sector_id`='$c11_sector_id' and b.`c16_estado`='aceptado' and c.c8_gestion_estado='ACTIVO'"
	}
	if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_lista_expedientes')};
	while($db->FetchRow()) {
		undef my @row;
		@row = $db->Data();
                my $i8_expediente_id = $row[0];
                my($barrio,$calle,$avenida,$pasaje,$senda,$poligono,$block,$casa)= get_direccion($i8_expediente_id,$clase);
                $str_direccion= "";
                if($avenida ne ""){$str_direccion.="$avenida, "}
                if($calle ne ""){$str_direccion.="$calle, "}
                if($pasaje ne ""){$str_direccion.="$pasaje, "}
                if($senda ne ""){$str_direccion.="$senda, "}
                if($poligono ne ""){$str_direccion.="$poligono, "}
                if($block ne ""){$str_direccion.="$block, "}
                if($casa ne ""){$str_direccion.=" # $casa"}
                my($contribuyente)= get_nombre($i8_expediente_id,$clase);
		
                my ($noti1,$gid1)  = get_notificaciones($i8_expediente_id,'ACTIVO','1');
                my ($noti2,$gid2)  = get_notificaciones($i8_expediente_id,'ACTIVO','2');
                my ($noti3,$gid3)  = get_notificaciones($i8_expediente_id,'ACTIVO','3');
                my ($noti6,$gid6)  = get_notificaciones($i8_expediente_id,'ACTIVO','6');

		$table .= "<td class=tdheader1>$contador</td>";
		$table .= "<td class=tdheader1><a href=javascript:OpenBalance($i8_expediente_id)>$i8_expediente_id</a></td>";
		$table .= "<td class=tdheader1>$contribuyente&nbsp;</td>";
		$table .= "<td class=tdheader1>$str_direccion&nbsp;</td>";
                $table .= "<td align=right><a href=javascript:OpenNotificacion('$i8_expediente_id','0','$gid1')>$noti1</a></td>";
                $table .= "<td align=right><a href=javascript:OpenNotificacion('$i8_expediente_id','1','$gid2')>$noti2</a></td>";
                $table .= "<td align=right><a href=javascript:OpenNotificacion('$i8_expediente_id','2','$gid3')>$noti3</a></td>";
                $table .= "<td align=right><a href=javascript:OpenNotificacion('$i8_expediente_id','6','$gid6')>$noti6</a></td>";
		$table .= "</tr>";
		$contador++;
	}
        $table.="</table>";
        #print $table;
}
sub get_direccion {
	my($expediente_id,$clase)=@_;

	if($clase eq "inmueble"){$tabl="sdm_inmuebles";}
	if($clase eq "empresa"){$tabl="sdd_empresa_ubicacion";}
	my $sql="select c4_calle_id,c4_avenida_id,c2_pasaje_id,c2_senda_id,c4_casa,c2_block_id,c2_poligono_id,c2_barrio_id
		  from $tabl where i8_".$clase."_id='$expediente_id'";

	my $calle	= "";
	my $avenida	= "";
	my $pasaje	= "";
	my $senda	= "";
	my $casa	= "";
        my $poligono	= "";
        my $block	= "";
        my $barrio	= "";
	
	if ( $db1->Sql($sql) ) {print "Error en $sql";}
	while( $db1->FetchRow() ){
		undef my @row;
		@row		= $db1->Data();
		$calle_id	= $row[0];
		$avenida_id	= $row[1];
		$pasaje_id	= $row[2];
		$senda_id	= $row[3];
		$casa		= $row[4];
                $c2_poligono_id = $row[5];
                $c2_block_id	= $row[6];
                $c2_barrio_id	= $row[7];
                
                $sql="select v64_barrio from sdd_sector_barrios where c2_barrio_id='$c2_barrio_id' and c11_sector_id='$c11_sector_id'";
                if($db2->Sql($sql)){print "SQL failed6.\n";print "Error: " . $db2->Error() . "\n";$db2->Close();exit;}
		if($db2->FetchRow()){undef @row2;@row2=$db2->Data();$barrio=$row2[0];$direccion=$direccion.", ".$barrio;}
                
		$sql="select v64_calle from sdd_sector_calles where c4_calle_id='$calle_id' and c11_sector_id='$c11_sector_id'";
                if($db2->Sql($sql)){print "SQL failed6.\n";print "Error: " . $db2->Error() . "\n";$db2->Close();exit;}
		if($db2->FetchRow()){undef @row2;@row2=$db2->Data();$calle=$row2[0];$direccion=$direccion.", ".$calle;}

		$sql="select v64_avenida from sdd_sector_avenidas where c4_avenida_id='$avenida_id' and c11_sector_id='$c11_sector_id'";
		if($db2->Sql($sql)){print "SQL failed7.\n";print "Error: " . $db2->Error() . "\n";$db2->Close();exit;}
		if($db2->FetchRow()){undef @row2;@row2=$db2->Data();$avenida=$row2[0];$direccion=$direccion.", ".$avenida;}

		$sql="select v64_pasaje from sdd_sector_pasajes where c2_pasaje_id='$pasaje_id' and c11_sector_id='$c11_sector_id'";
		if($db2->Sql($sql)){print "SQL failed8.\n";print "Error: " . $db2->Error() . "\n";$db2->Close();exit;}
		if($db2->FetchRow()){undef @row2;@row2=$db2->Data();$pasaje=$row2[0];$direccion=$direccion.", ".$pasaje;}

		$sql="select v64_senda from sdd_sector_sendas where c2_senda_id='$senda_id' and c11_sector_id='$c11_sector_id'";
		if($db2->Sql($sql)){print "SQL failed9.\n";print "Error: " . $db2->Error() . "\n";$db2->Close();exit;}
		if($db2->FetchRow()){undef @row2;@row2=$db2->Data();$senda=$row2[0];$direccion=$direccion.", ".$senda;}
                
                $sql="select v64_poligono from sdd_sector_poligonos where c2_poligono_id='$c2_poligono_id' and c11_sector_id='$c11_sector_id'";
		if($db2->Sql($sql)){print "SQL failed9.\n";print "Error: " . $db2->Error() . "\n";$db2->Close();exit;}
		if($db2->FetchRow()){undef @row2;@row2=$db2->Data();$poligono=$row2[0];$direccion=$direccion.", ".$poligono;}
                
                $sql="select v64_block from sdd_sector_blocks where c2_block_id='$c2_block_id' and c11_sector_id='$c11_sector_id'";
		if($db2->Sql($sql)){print "SQL failed9.\n";print "Error: " . $db2->Error() . "\n";$db2->Close();exit;}
		if($db2->FetchRow()){undef @row2;@row2=$db2->Data();$block=$row2[0];$direccion=$direccion.", ".$block;}
                
		$direccion=$direccion."".$casa;
	}
	return ($barrio,$calle,$avenida,$pasaje,$senda,$poligono,$block,$casa);
}
sub get_nombre{
	my ($id,$clase)= @_;
	my $r   = "";
        my $sql = "select concat(a.v64_nombres,' ',a.v64_apellidos)
		   from sdm_contribuyentes a
		   inner join sdd_".$clase."_contribuyentes b on a.i8_contribuyente_id = b.i8_contribuyente_id
		   where b.i2_tipo_contribuyente_id=1 and b.i8_".$clase."_id='$id'";
	if ( $db1->Sql($sql) ){print "Error $sql"};
	while($db1->FetchRow()){
		undef my @row;
		@row	= $db1->Data();
		$r=$row[0];
	}
	return $r;
}
sub get_notificaciones {
	my($id,$estado,$tipo)=@_;
	my $dt = "";
	my $gd = "";	
	$sql = "select a.dt_entrega,a.i4_gestion_id
		from sdd_".$clase."_administrativo a
		where a.i8_".$clase."_id='$id' and a.c8_gestion_estado='$estado' and a.i2_tipo_notificacion_id='$tipo'";
	if($clase eq "empresa"){
		$sql = "select count(*) from `sdd_empresa_administrativo` a
			where a.i8_".$clase."_id='$id' and a.c8_gestion_estado='$estado' and a.i2_tipo_notificacion_id='$tipo'";
	}
	if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_notificaciones')};
	while($db->FetchRow()) {
		    undef my @row;
		    @row = $db->Data();
		    $dt  = $row[0];
		    $gd  = $row[1];
	}
	return $dt,$gd;
}
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/\{clase}/$clase/g;
                $line =~ s/\{sector}/$sector/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;
}