#!/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_notificacion= $form->param('dt_notificacion');
$str_expedientes= $form->param('str_expedientes');
$PATH		= "../".$lan."/";
$file 		= $lan."_mora_notificacion_expedientes_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;}
#print "$appid<>$clase<>$lan<>$dt_notificacion<>$str_expedientes<>$PATH<>$file<br>";
procesar_expedientes();

$db->Close();
exit;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# AUXILIAR-SUBROUTINES
sub procesar_expedientes {
    @expedientes    = split(/\|/,$str_expedientes);
    for($i=0;$i<=$#expedientes;$i++) {
        $id         = $expedientes[$i];
        $direccion  = get_direccion($id);
        imprimir_notificacion($id,$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;
}
sub imprimir_notificacion {
        my($id,$dir)=@_;
        $i8_periodo = get_ultimo_periodo($id);
	#print "$i8_periodo<br>";
        $total_mora = get_total_mora($id);
	#print "$total_mora<br>";
        $tabla      = get_records($i8_periodo,'sdd_'.$clase.'_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)');        	
	#print "$tabla<br>";
        $nombre     = get_nombre('sdd_'.$clase.'_contribuyentes a,sdm_contribuyentes b','b.v128_contribuyente');    
	#print "$nombre<br>";
        construct_page($tabla,$nombre,$dir,$id);
}
sub get_ultimo_periodo {
    my($id)=@_;    
    $sql="select max(i8_periodo) from sdd_".$clase."_estados_mensuales where i8_".$clase."_id='$id'";
    #print "$sql<br>";
    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;
}
sub get_total_mora {
    my($id) = @_;
    my $tm  = 0;
    my $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();
	$tm   = $rowx[0];
    }
    return $tm;
}
sub get_records{
	my ($i8_periodo,$tables,$fields)= @_;
	my $r	  	   = "";
	my $rs	  	   = "";
        my $suma_monto 	   = "";
        my $suma_fiestas   = "";
        my $suma_intereses = "";
        my $suma_multas    = "";
        my $contador 	   = 0;
        my $template_rows  = "<TR class=my_style5><TD width=50px>No</TD><TD width=175px>Tarifas</TD><TD align=center>Monto</TD></TR>";
	my $query 	   = "SELECT $fields FROM $tables WHERE i8_".$clase."_id='$id' and substring(a.c16_tarifa_id,1,8)=b.c8_tributo_id GROUP BY substring(c16_tarifa_id,1,8)";
        #print "$query";
	if ($db->Sql($query)){SQL_fail($db,$query,'sub get_records')};
	while($db->FetchRow()){				
		$contador++;
                undef my @row;
		@row		 = $db->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 align=center>$valor_tributo</TD></TR>"; 
	}
        if($suma_multas<2.86){$suma_multas=2.86}
        $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>";
        $suma_total	= $suma_monto+$suma_fiestas+$suma_intereses+$suma_multas;
        $template_rows	= $template_rows."<TR class=my_style5><TD>&nbsp;</TD><TD>TOTAL</TD><TD align=center>$suma_total</TD></TR>";
        $table 		= $template_rows;
        $table 		=~ s/{body}/$template_rows/g;
	return $table;
}

sub get_nombre{
    my ($tables,$fields)= @_;
    my $r     = "";
    my $rs    = "";
    my $query = "SELECT $fields FROM $tables WHERE a.i8_".$clase."_id='$id' and a.i8_contribuyente_id=b.i8_contribuyente_id  and (i2_tipo_contribuyente_id='1' or i2_tipo_contribuyente_id='3')";    
    #print "$query<br>";
    if ( $db->Sql($query) ){SQL_fail($db,$query,'sub get_records')};
    while($db->FetchRow()){
	    undef my @row;
	    @row = $db->Data();
	    $r	 = $row[0];
    }
    return $r;
}
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";
    }
    return 0;
}
sub SQL_fail {
    ($dbh,$query,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Error: ".$dbh->Error()."\n<br>";
    print "Query: ".$query."<br>";    
    $dbh->Close();
    exit;
}
