#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
use Win32::ODBC;
use CGI;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------

$form 		= new CGI;

$appid          = $form->param('appid');
$clase          = $form->param('clase');
$lan            = $form->param('lan');
$dt_impresion   = $form->param('dt_impresion');
$str_expedientes= $form->param('str_expedientes');

$PATH		= "../".$lan."/";
$file 		= $lan."_mora_notificacion_rept.html";
#print "$file";
#---------------------------------------------------------------
#---------------------------------------------------------------
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;}
procesar_expedientes();
$db->Close();
exit;

#-------------------------------------------------------------------------
# AUXILIAR-SUBROUTINES
#funcion para obtener el inmueble
sub procesar_expedientes {
    @expedientes    = split(",",$str_expedientes);
    for($i=0;$i<=$#expedientes;$i++) {
        $id         = $expedientes[$id];
        $direccion  = get_direccion($id);
        imprimir_notificacion($id,$direccion);
    }
}
##funcion para obtener la direccion
sub get_direccion
{
        $direccion = "";
	my($expediente_id)=@_;
	$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 from sdm_".$clase."s where i8_".$clase."_id='$expediente_id'";
        if($clase eq "empresa"){$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 from sdd_".$clase."_ubicacion where i8_".$clase."_id='$expediente_id'";}
	#print "<br>get_direccion: $sql<br>";
	if ( $db->Sql($sql) ) {print "SQL Fail:$sql.\n";print "Error: " . $db->Error() . "\n";$db->Close();exit;}
	if ( $db->FetchRow() )
	{
		undef @row;
		@row=$db->Data();
		$sector_id=$row[0];
		$calle_id=$row[1];
		$avenida_id=$row[2];
		$pasaje_id=$row[3];
		$senda_id=$row[4];
		$casa=$row[5];
                $block_id=$row[6];
                $poligono_id=$row[7];
		$sql="select a.i2_tipo_sector_id, a.v64_sector, b.v64_tipo_sector from sdm_sectores a left join sdm_tipo_sectores b
                on a.i2_tipo_sector_id=b.i2_tipo_sector_id where a.c11_sector_id='$sector_id'";
		if($db1->Sql($sql)){print "SQL failed2.\n";print "Error: " . $db1->Error() . "\n";$db1->Close();exit;}
		if($db1->FetchRow())
		{
			undef @row1;
			@row1       = $db1->Data();
			$sector     = $row1[1];
                        $tipo_sector= $row1[2];
			$direccion=$tipo_sector." ".$sector;
		}
		$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 failed2.\n";print "Error: " . $db1->Error() . "\n";$db1->Close();exit;}
		if($db1->FetchRow()){undef @row2;@row2=$db1->Data();$calle=$row2[0];$direccion=$direccion.", Calle ".$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 failed2.\n";print "Error: " . $db1->Error() . "\n";$db1->Close();exit;}
		if($db1->FetchRow()){undef @row2;@row2=$db1->Data();$avenida=$row2[0];$direccion=$direccion.", Avenida ".$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 failed2.\n";print "Error: " . $db1->Error() . "\n";$db1->Close();exit;}
		if($db1->FetchRow()){undef @row2;@row2=$db1->Data();$pasaje=$row2[0];$direccion=$direccion.", Pasaje ".$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 failed2.\n";print "Error: " . $db1->Error() . "\n";$db1->Close();exit;}
		if($db1->FetchRow()){undef @row2;@row2=$db1->Data();$senda=$row2[0];$direccion=$direccion.", Senda ".$senda;}
                $sql="select v64_block from sdd_sector_blocks where c2_block_id='$block_id' and c11_sector_id='$sector_id'";
		if($db1->Sql($sql)){print "SQL failed2.\n";print "Error: " . $db1->Error() . "\n";$db1->Close();exit;}
		if($db1->FetchRow()){undef @row2;@row2=$db1->Data();$block=$row2[0];$direccion=$direccion.",".$block;}
                $sql="select v64_poligono from sdd_sector_poligonos where c2_poligono_id='$poligono_id' and c11_sector_id='$sector_id'";
		if($db1->Sql($sql)){print "SQL failed2.\n";print "Error: " . $db1->Error() . "\n";$db1->Close();exit;}
		if($db1->FetchRow()){undef @row2;@row2=$db1->Data();$poligono=$row2[0];$direccion=$direccion.",".$poligono;}
                if($direccion ne ""){
                    $direccion=$direccion." ".$casa;
                }
	}
	return $direccion;
}
#funcion para generar la notificacion - solo una a la vez
sub imprimir_notificacion
{
        my($id,$dir)=@_;
        $i8_periodo=get_ultimo_periodo();
        $total_mora=get_total_mora($option,$id);
        $tabla = get_records($i8_periodo,$db_um,'sdd_inmueble_estados_mensuales a,sdm_tributos b','b.v64_tributo,MIN(a.i8_periodo),MAX(a.i8_periodo),sum(a.d82_val),sum(a.d82_fie),sum(a.d82_int),sum(a.d82_mul)');        	
        $nombre = get_nombre($db_um,'sdd_inmueble_contribuyentes a,sdm_contribuyentes b','b.v128_contribuyente');    
        construct_page($tabla,$nombre,$dir,$id);
}#funcion

#funcion para obtener el registro
sub get_records{
	my ($i8_periodo,$dbh,$tables,$fields)= @_;
	my $query = "";
	my $r	  = "";
	my $rs	  = "";
        my $suma_monto = "";
        my $suma_fiestas = "";
        my $suma_intereses = "";
        my $suma_multas = "";
        my $contador = "";
        $anio=substr($date,0,4);
        $mes=substr($date,5,2);
        
        
	#if(ts_upto eq ""){$query = "SELECT $fields FROM $tables WHERE a.c16_recibo_id=b.c16_recibo_id and b.c16_tarifa_id=c.c16_tarifa_id and substring(a.dt_emision+0,1,8)='$since' GROUP BY b.c16_tarifa_id ORDER BY 2"}
	#if(ts_upt ne ""){$query = "SELECT $fields FROM $tables WHERE a.c16_recibo_id=b.c16_recibo_id and b.c16_tarifa_id=c.c16_tarifa_id and substring(a.dt_emision+0,1,8)>='$since' and substring(a.dt_emision+0,1,8)<='$upto' GROUP BY b.c16_tarifa_id ORDER BY 2"}
	if($option eq "1"){$query = "SELECT $fields FROM $tables WHERE i8_inmueble_id='$id' and substring(a.c16_tarifa_id,1,8)=b.c8_tributo_id GROUP BY substring(c16_tarifa_id,1,8)";}
	if($option eq "2"){$query = "SELECT $fields FROM $tables WHERE i8_empresa_id='$id' and substring(a.c16_tarifa_id,1,8)=b.c8_tributo_id GROUP BY substring(c16_tarifa_id,1,8)";}
	#if(ts_upto ne ""){$query = "SELECT $fields FROM $tables WHERE a.c16_recibo_id=b.c16_recibo_id and b.c16_tarifa_id=c.c16_tarifa_id and a.dt_emision>='$since' and a.dt_emision<='$upto' GROUP BY b.c16_tarifa_id ORDER BY 2"}
	#print "$query<br>";
        
        #Aqui se le pone la primera fila de Título
        $template_rows="";
        $contador = 0;
        $template_rows=$template_rows."<TR class=my_style5><TD width=50px>No</TD><TD width=175px>Tarifas</TD><TD align=center>Monto</TD></TR>";
        #print "$query";
	if ($dbh->Sql($query)){SQL_fail($dbh,$query,'sub get_records')};
	while($dbh->FetchRow()){				
		$contador++;
                undef my @row;
		@row	= $dbh->Data();
		$row[3] = sprintf("%.2f",$row[3]);
		$row[4] = sprintf("%.2f",$row[4]);
		$row[5] = sprintf("%.2f",$row[5]);
		$row[6] = sprintf("%.2f",$row[6]);
                $valor_tributo=$row[3];
                $suma_monto     += $row[3];
                
                $suma_fiestas   += $row[4];
                
                $suma_intereses += $row[5];
                $suma_multas    += $row[6];
                
                
		
                $r	= join('|',@row);
		$rs	.= $r."~~";
                
                #$template_rows=$template_rows."<TR class=my_style5><TD>$contador</TD><TD>$row[0]</TD><TD>$row[1]</TD><TD>$row[2]</TD><TD>$row[3]</TD><TD>$row[4]</TD><TD>$row[5]</TD><TD>$row[6]</TD><TD>$suma_fila</TD></TR>";
                $template_rows=$template_rows."<TR class=my_style5><TD>$contador</TD><TD>$row[0]</TD><TD align=center>$valor_tributo</TD></TR>"; 
	}
        $contador++;
        #$suma_fiestas=sprintf("%.2f",$suma_fiestas);
        #$suma_total=sprintf("%.2f",$suma_total);
        #$template_rows=$template_rows."<TR class=my_style5><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>$suma_total</TD></TR>";
        $template_rows=$template_rows."<TR class=my_style5><TD>&nbsp;</TD><TD>Fiestas</TD><TD align=center>$suma_fiestas</TD></TR>";
        $template_rows=$template_rows."<TR class=my_style5><TD>&nbsp;</TD><TD>Intereses</TD><TD align=center>$suma_intereses</TD></TR>";
        $template_rows=$template_rows."<TR class=my_style5><TD>&nbsp;</TD><TD>Multas</TD><TD align=center>$suma_multas</TD></TR>";
        if($suma_multas<2.86){$suma_multas=2.86}
        $suma_total=$suma_monto+$suma_fiestas+$suma_intereses+$suma_multas;
        #$suma_total=sprintf("%.2f".$suma_total);
        $template_rows=$template_rows."<TR class=my_style5><TD>&nbsp;</TD><TD>TOTAL</TD><TD align=center>\$$suma_total</TD></TR>";
        #$total_mora-=$suma_total;
        #$total_mora=sprintf("%.2f",$total_mora);
        #$template_rows=$template_rows."<TR class=my_style5><TD>&nbsp;</TD><TD>Total Mora</TD><TD align=center>\$$total_mora</TD></TR>";
        #print "<br>p registros: $query<br>";
        $table = $template_table;
        $table =~ s/{body}/$template_rows/g;
        
	return $table;
}
sub get_total_mora
{
    my($option,$expediente)=@_;
    if($option eq "1"){$clase="inmueble"}
    if($option eq "2"){$clase="empresa"}
    $total_mora=0;
    $sql="select sum(d82_val)+sum(d82_fie)+sum(d82_int)+sum(d82_mul) from sdd_".$clase."_estados_mensuales where i8_".$clase."_id=".$id;
    
    if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records')};
    while($db->FetchRow())
    {
	undef my @rowx;
	@rowx	= $db->Data();
	$total_mora=$rowx[0];
        
    }
    return $total_mora;
}
sub get_ultimo_periodo
{
    if($option eq "1"){$clase="inmueble"}
    if($option eq "2"){$clase="empresa"}
    $sql="select max(i8_periodo) from sdd_".$clase."_estados_mensuales";
    $i8_periodo="";
    if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records')};
    while($db->FetchRow())
    {
	undef my @rowx;
	@rowx	= $db->Data();
	$periodo=$rowx[0];
        
    }
    return $periodo;
}
#funcion para obtener el nombre
sub get_nombre{
	my ($dbh,$tables,$fields)= @_;
	my $query = "";
	my $r	  = "";
	my $rs	  = "";
	#if(ts_upto eq ""){$query = "SELECT $fields FROM $tables WHERE a.c16_recibo_id=b.c16_recibo_id and b.c16_tarifa_id=c.c16_tarifa_id and substring(a.dt_emision+0,1,8)='$since' GROUP BY b.c16_tarifa_id ORDER BY 2"}
	#if(ts_upt ne ""){$query = "SELECT $fields FROM $tables WHERE a.c16_recibo_id=b.c16_recibo_id and b.c16_tarifa_id=c.c16_tarifa_id and substring(a.dt_emision+0,1,8)>='$since' and substring(a.dt_emision+0,1,8)<='$upto' GROUP BY b.c16_tarifa_id ORDER BY 2"}
	if($option eq "1"){$query = "SELECT $fields FROM $tables WHERE a.i8_inmueble_id='$id' and a.i8_contribuyente_id=b.i8_contribuyente_id  and (i2_tipo_contribuyente_id='1' or i2_tipo_contribuyente_id='3')";}
	if($option eq "2"){$query = "SELECT $fields FROM $tables WHERE a.i8_empresa_id='$id' and a.i8_contribuyente_id=b.i8_contribuyente_id  and (i2_tipo_contribuyente_id='1' or i2_tipo_contribuyente_id='3')";}
	#if(ts_upto ne ""){$query = "SELECT $fields FROM $tables WHERE a.c16_recibo_id=b.c16_recibo_id and b.c16_tarifa_id=c.c16_tarifa_id and a.dt_emision>='$since' and a.dt_emision<='$upto' GROUP BY b.c16_tarifa_id ORDER BY 2"}
	#print "$query<br>";

	if ( $dbh->Sql($query) ){SQL_fail($dbh,$query,'sub get_records')};
	while($dbh->FetchRow()){
		
		
		undef my @row;
		@row	= $dbh->Data();
		$r=$row[0];
		
	}
        #print "<br>p nombre: $query<br>";
	return $r;
}

#funcion para crear la pagina
sub construct_page {
	my ($tabla,$nombre,$dir,$id)= @_;
	$arc=$file;
	open (DATA, "$PATH$arc");        
    #print "<br>ultimo antes de imprimir: $rowset<br>";
	while ($line = <DATA>) {	
		$line =~ s/\{lan}/$lan/g;
		$line =~ s/\{appid}/$appid/g;
		$line =~ s/\{table}/$tabla/g;                
		$line =~ s/\{nombre}/$nombre/g;
		$line =~ s/\{dir}/$dir/g;
		$line =~ s/\{id}/$id/g;
                $line =~ s/\{i8_periodo}/$i8_periodo/g;
                $line =~ s/\{total_mensual}/$suma_total/g;
                $line =~ s/\{fecha_limite}/$fecha_limite/g;
                $line =~ s/\{anio_limite}/$anio_limite/g;
                $line =~ s/\{dia_limite}/$dia_limite/g;
                $line =~ s/\{mes_limite}/$mes_limite/g;
                $line =~ s/\{c16_codigo_catastral_id}/$c16_codigo_catastral_id/g;
                
		print "$line";
	}
}#if
