#!/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');
$str_expedientes= $form->param('str_expedientes');
##Variables de la tabla
$table = "<table id=Grid 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_mora_expedientes_empresa_rept.html";          
#-------------------------------------------------------------------
#-------------------------------------------------------------------
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;}
#-------------------------------------------------------------------
#-------------------------------------------------------------------
generar_lista_expedientes();
construct_page();
$db->Close();
exit;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# AUXILIAR-SUBROUTINES
#funcion para obtener el inmueble
sub generar_lista_expedientes {
        $table.="<tr><td class=tdheader1><input type=checkbox name=chk onClick=MarkAll() class=checkbox>No.</td><td class=tdheader1>EXPEDIENTE</td><td class=tdheader1>PARCELA</td><td class=tdheader1>NOMBRE&nbsp;DEL&nbsp;TITULAR&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td><td class=tdheader1>DIRECCION</td><td class=tdheader1>VALOR_TRIBUTOS</td><td class=tdheader1>ACC_FIESTA</td><td class=tdheader1>ACC_INTERES</td><td class=tdheader1>ACC_MULTA</td><td class=tdheader1>SUBTOTAL</td><td class=tdheader1>COBRO_ADMIN&nbsp;</td></tr>";
        @expedientes=split(/|/,$str_expedientes);
        $cnt=$#expedientes;
        $contador=1;
        for($i=0;$i<=$cnt;$i++)	{
                $i8_expediente_id=$expedientes[$i];
                #print "<br>$i8_inmueble_id,$d82_val,$d82_fie,$d82_int,$d82_mul,$d82_total";
                my($sector,$barrio,$calle,$avenida,$pasaje,$senda,$poligono,$block,$casa)=get_direccion($i8_expediente_id,$clase);
                $str_direccion=$sector;
                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"}
                
                #print "<br>Direccion:$sector";
                my($contribuyente)=get_nombre($i8_expediente_id,$clase);
                #print "<br>Contribuyente:$contribuyente";
                
                $str_notificacion="Agregar";
                $sql="select b.v64_tipo_notificacion,a.dt_administrativo from sdd_".$clase."_administrativo a 
                inner join sdm_tipo_notificaciones b on a.i2_tipo_notificacion_id=b.i2_tipo_notificacion_id
                where a.i8_".$clase."_id=$i8_expediente_id
                order by b.i2_tipo_notificacion_id desc limit 1";
                if ( $db->Sql($sql) ){print "Error $sql"};
                while($db->FetchRow()) {
                	undef my @row11;
                	@row11	= $db->Data();
                	$str_notificacion=$row11[0];
                        $str_notificacion.=" *";
                        $str_notificacion.=$row11[1];
                }
                ($d82_val,$d82_fie,$d82_int,$d82_mul)=get_mora($i8_expediente_id,$clase);
                $d82_subtotal=$d82_val+$d82_fie+$d82_int+$d82_mul;
                $d82_total+=$d82_subtotal;
                $d82_val_t+=$d82_val;
                $d82_fie_t+=$d82_fie;
                $d82_int_t+=$d82_int;
                $d82_mul_t+=$d82_mul;
                $d82_subtotal_t+=$d82_subtotal;
                $d82_subtotal=sprintf("%.2f",$d82_subtotal);
                $table.="<tr><td class=tdheader1><input type=checkbox name=chk_$contador class=checkbox>$contador</td><td class=tdheader1><a href=javascript:OpenBalance($i8_expediente_id)>$i8_expediente_id</a></td>
                <td class=tdheader1>$c16_codigo_catastral_id&nbsp;</td><td class=tdheader1>$contribuyente&nbsp;</td>
                <td class=tdheader1>$str_direccion&nbsp;</td><td class=tdheader1>$d82_val&nbsp;</td><td class=tdheader1>$d82_fie&nbsp;</td>
                <td class=tdheader1>$d82_int&nbsp;</td><td class=tdheader1>$d82_mul&nbsp;</td>
                <td class=tdheader1><a href=javascript:OpenNotificacion($i8_expediente_id)>$d82_subtotal&nbsp;</a></td>
                <td class=tdheader1><a href=javascript:OpenAdministrativo($i8_expediente_id,'$clase')>$str_notificacion</a>
                </tr>";
		$contador++;
	}
        $d82_val_t=sprintf("%.2f",$d82_val_t);
        $d82_fie_t=sprintf("%.2f",$d82_fie_t);
        $d82_int_t=sprintf("%.2f",$d82_int_t);
        $d82_mul_t=sprintf("%.2f",$d82_mul_t);
        $d82_subtotal_t=sprintf("%.2f",$d82_subtotal_t);
        $table.="<tr><td class=tdheader1 colspan=5>TOTALES</td><td class=tdheader1>\$$d82_val_t&nbsp;</td><td class=tdheader1>\$$d82_fie_t&nbsp;</td><td class=tdheader1>\$$d82_int_t&nbsp;</td><td class=tdheader1>\$$d82_mul_t&nbsp;</td><td class=tdheader1><a href='javascript:OpenNotificacion($i8_expediente_id,1)'>\$$d82_subtotal_t&nbsp;</a></td><td class=tdheader1><a href='javascript:OpenWindowNotificacion($i8_expediente_id,1)'>$str_notificacion</a></tr>";
        $table.="</table>";
        #print $table;
}
sub get_mora {
    my($id,$clase)=@_;
    $sql = "select sum(d82_val) as valor,sum(d82_fie) as fiestas,sum(d82_int) as intereses, sum(d82_mul) as multas from sdd_".$clase."_estados_mensuales where i8_".$clase."_id='$id' and i4_dias_mora>0;";
    #print "$sql<br>";
    if ( $db1->Sql($sql) ){SQL_fail($db1,$sql,'sub get_records - obtener Numero Inmuebles')};
    while($db1->FetchRow()){
		undef my @row1;
		@row1	= $db1->Data();
		$valor_val=$row1[0];
		$valor_fie=$row1[1];
		$valor_int=$row1[2];
		$valor_mul=$row1[3];
    }
    if($valor_mul>0 && $valor_mul<2.86){
	$valor_mul=2.86;
    }
    
    return ($valor_val,$valor_fie,$valor_int,$valor_mul);

}
sub get_direccion {
	my($expediente_id,$clase)=@_;
	#$generador=lc($generador);
	if($clase eq "inmueble"){$tabl="sdm_inmuebles";}
	if($clase eq "empresa"){$tabl="sdd_empresa_ubicacion";}
	$sql="select c11_sector_id,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'";
	
        #print "$sql";
        $sector="";
	$calle="";
	$avenida="";
	$pasaje="";
	$senda="";
	$casa="";
        $poligono="";
        $block="";
        $barrio="";
	
	if ( $db->Sql($sql) ) {print "Error en $sql";}
	while( $db->FetchRow() ){
		undef @row;
		@row=$db->Data();
		#$direccion=$row[0];
		$sector_id=$row[0];
		$calle_id=$row[1];
		$avenida_id=$row[2];
		$pasaje_id=$row[3];
		$senda_id=$row[4];
		$casa=$row[5];
                $c2_poligono_id=$row[6];
                $c2_block_id=$row[7];
                $c2_barrio_id=$row[8];
		$sql="select i2_tipo_sector_id, v64_sector from sdm_sectores where c11_sector_id='$sector_id' and c11_sector_id='$sector_id'";
                #print "$sql<br>";
                if($db1->Sql($sql)){print "SQL failed4.\n";print "Error: " . $db1->Error() . "\n";$db1->Close();exit;}
		if($db1->FetchRow()){
			undef @row1;
			@row1=$db1->Data();
			$tipo_sector_id=$row1[0];
			$sector=$row1[1];
			$sql="select v64_tipo_sector from sdm_tipo_sectores where i2_tipo_sector_id='$tipo_sector_id'";
			if($db2->Sql($sql)){print "SQL failed5.\n";print "Error: " . $db2->Error() . "\n";$db2->Close();exit;}
			if($db2->FetchRow()){undef @row2;@row2=$db2->Data();$tipo_sector=$row2[0];}
			if($tipo_sector eq "OTROS"){$direccion=$sector;}
			else{
				$direccion=$tipo_sector." ".$sector;
				}
		}
                $sector=$direccion;
                
                $sql="select v64_barrio from sdd_sector_barrios where c2_barrio_id='$c2_barrio_id' and c11_sector_id='$sector_id'";
                if($db1->Sql($sql)){print "SQL failed6.\n";print "Error: " . $db1->Error() . "\n";$db1->Close();exit;}
		if($db1->FetchRow()){undef @row2;@row2=$db1->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='$sector_id'";
                if($db1->Sql($sql)){print "SQL failed6.\n";print "Error: " . $db1->Error() . "\n";$db1->Close();exit;}
		if($db1->FetchRow()){undef @row2;@row2=$db1->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='$sector_id'";
		if($db1->Sql($sql)){print "SQL failed7.\n";print "Error: " . $db1->Error() . "\n";$db1->Close();exit;}
		if($db1->FetchRow()){undef @row2;@row2=$db1->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='$sector_id'";
		if($db1->Sql($sql)){print "SQL failed8.\n";print "Error: " . $db1->Error() . "\n";$db1->Close();exit;}
		if($db1->FetchRow()){undef @row2;@row2=$db1->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='$sector_id'";
		if($db1->Sql($sql)){print "SQL failed9.\n";print "Error: " . $db1->Error() . "\n";$db1->Close();exit;}
		if($db1->FetchRow()){undef @row2;@row2=$db1->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='$sector_id'";
		if($db1->Sql($sql)){print "SQL failed9.\n";print "Error: " . $db1->Error() . "\n";$db1->Close();exit;}
		if($db1->FetchRow()){undef @row2;@row2=$db1->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='$sector_id'";
		if($db1->Sql($sql)){print "SQL failed9.\n";print "Error: " . $db1->Error() . "\n";$db1->Close();exit;}
		if($db1->FetchRow()){undef @row2;@row2=$db1->Data();$block=$row2[0];$direccion=$direccion.", ".$block;}
                
		$direccion=$direccion."".$casa;
		#print $direccion;
	}
	#print $direccion;
	return ($sector,$barrio,$calle,$avenida,$pasaje,$senda,$poligono,$block,$casa);
}
#funcion para obtener el nombre
sub get_nombre{
	my ($id,$clase)= @_;
	$r="";
        $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 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;
		print "$line";
	}
}
#funcion para crear la pagina

