#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
use Win32::ODBC;
use CGI;
use Date::Calc qw(Days_in_Month);
use Date::Calc qw(Today);
#-------------------------------------------------------------------
#-------------------------------------------------------------------
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------
#-------------------------------------------------------------------
$form 	= new CGI;
$appid  = $form->param('appid');
$clase  = $form->param('clase');
$lan    = $form->param('lan');
$id     = $form->param('id');
$nivel  = $form->param('tipo');
$gid	= $form->param('gid');
#-------------------------------------------------------------------
#-------------------------------------------------------------------
$PATH		= "../".$lan."/";
$file 		= $lan."_mora_notificacion_{nivel}_{clase}_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;}
#-------------------------------------------------------------------
#-------------------------------------------------------------------
#MAIN PROGRAMM
($year,$month,$day) = Today();
if(length($month)<2){$month = "0".$month}
$i8_periodo_actual = $year.$month;
$nivel		   = ($nivel*1)+1;
$nombre		   = get_nombre('sdd_'.$clase.'_contribuyentes a,sdm_contribuyentes b','b.v128_contribuyente',$id);
$direccion	   = get_direccion($id);

($total_mor,$desde_mor,$hasta_mor) = get_mora($id);
($total_cor,$desde_cor,$hasta_cor) = get_corriente($id);

($recibio,$dui,$relacion,$telefono,$emitido,$entregado) = get_notificacion($id); 

$total = $total_mor+$total_cor;
$table = construct_table();

construct_page();

$db->Close();
$db1->Close();
exit;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# AUXILIAR-SUBROUTINES
sub get_nombre{
    my ($tables,$fields,$id)= @_;
    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 get_direccion {
	my($expediente_id)=@_;
	$direccion = "";
	$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 get_mora {
    my($id) = @_;
    my $des = 0;    
    my $has = 0;
    my $tm  = 0;
    my $sql = "select sum(d82_val)+ sum(d82_fie)+ sum(d82_int)+ sum(d82_mul),MIN(i8_periodo),MAX(i8_periodo) from sdd_".$clase."_estados_mensuales where i8_".$clase."_id='$id' and i4_dias_mora>0 and d82_val>0";
   #print "$sql";
    if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records')};
    while($db->FetchRow()){
	undef my @rowx;
	@rowx = $db->Data();
	$tm   = $rowx[0];
	$des  = $rowx[1];
	$has  = $rowx[2];	
    }
    my $mes = substr($des,4,2);
    my $ano = substr($des,0,4);
    $des    = "01/".$mes."/".$ano;

    my $ano1 = substr($has,0,4);
    my $mes1 = substr($has,4,2);
    my $dia  = Days_in_Month($ano1,$mes1);
    $has     = $dia."/".$mes1."/".$ano1;

    return $tm,$des,$has;
}
sub get_corriente {
    my($id)    = @_;
    my $des    = 0;    
    my $has    = 0;
    my $tm     = 0;
    my $sql    = "select sum(d82_val)+ sum(d82_fie)+ sum(d82_int)+ sum(d82_mul),MIN(i8_periodo),MAX(i8_periodo) from sdd_".$clase."_estados_mensuales where i8_".$clase."_id='$id' and i4_dias_mora=0 and d82_val>0 and i8_periodo<='$i8_periodo_actual'";
   #print "$sql";
    if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records')};
    while($db->FetchRow()){
	undef my @rowx;
	@rowx = $db->Data();
	$tm   = $rowx[0];
	$des  = $rowx[1];
	$has  = $rowx[2];	
    }
    my $mes   = substr($des,4,2);
    my $ano   = substr($des,0,4);
    $des      = "01/".$mes."/".$ano;

    my $ano1  = substr($has,0,4);
    my $mes1  = substr($has,4,2);
    my $dia   = Days_in_Month($ano1,$mes1);
    $has      = $dia."/".$mes1."/".$ano1;
    return $tm,$des,$has;
}
sub get_notificacion {
    my($id) = @_;
    my @row  = ();
    my $sql = "select a.v64_persona,a.c11_dui_id,b.v64_tipo_contribuyente,a.c16_telefono,a.dt_emision,a.dt_entrega,a.i2_tipo_contribuyente_id from sdd_".$clase."_administrativo a left join sdm_tipo_contribuyentes b on a.i2_tipo_contribuyente_id=b.i2_tipo_contribuyente_id where a.i8_".$clase."_id='$id' and a.i4_gestion_id='$gid' and a.i2_tipo_notificacion_id='$nivel'";
    #print "$sql<br>";
    if ( $db->Sql($sql) ){SQL_fail($db,$sql,'sub get_records')};
    while($db->FetchRow()){
	undef @row;
	@row = $db->Data();
    }
    if($row[6] eq "10"){$row[2]="PADRE"}
    if($row[6] eq "11"){$row[2]="MADRE"}
    if($row[6] eq "12"){$row[2]="ESPOSA"}
    if($row[6] eq "14"){$row[2]="HERMANO"}
    if($row[6] eq "15"){$row[2]="TIO(A)"}
    if($row[6] eq "16"){$row[2]="PRIMO(A)"}
    if($row[6] eq "17"){$row[2]="VECINO(A)"}
    if($row[6] eq "13"){$row[2]="NO APLICA"}
    return $row[0],$row[1],$row[2],$row[3],$row[4],$row[5];
}
sub construct_table {
    my $mora	  = big_money($total_mor);
    my $corriente = big_money($total_cor);
    my $deuda	  = big_money($total);
    my $tab = "<table class=Grid>
		    <tr><td>MORA DESDE $desde_mor HASTA $hasta_mor</td><td align=right>$mora</td></tr>
		    <tr><td>MESES CORRIENTES DESDE $desde_cor HASTA $hasta_cor</td><td align=right>$corriente</td></tr>
		    <tr><td><b>TOTAL</b></td><td align=right><b>$deuda</b></td></tr>
		</table>";
    return $tab;
}
sub construct_page {
    my $arc	= $file;
    my $pagina	= "";
    my $todate	= "<script language='javascript'>document.write(dateString)</script>";
    my $deuda	  = big_money($total);    
    $arc	=~ s/\{nivel}/$nivel/g;
    $arc	=~ s/\{clase}/$clase/g;
    if($emitido ne ""){$todate = $emitido}
    open (DATA, "$PATH$arc");        
    #print "<br>$PATH$arc<br>";
    while ($line = <DATA>) {	
	$line =~ s/\{lan}/$lan/g;
	$line =~ s/\{appid}/$appid/g;
	$line =~ s/\{id}/$id/g;
	$line =~ s/\{nombre}/$nombre/g;
	$line =~ s/\{direccion}/$direccion/g;
	$line =~ s/\{generador}/$clase/g;
	$line =~ s/\{table}/$table/g;
	$line =~ s/\{deuda}/$deuda/g;
	$line =~ s/\{recibio}/$recibio/g;
	$line =~ s/\{dui}/$dui/g;
	$line =~ s/\{telefono}/$telefono/g;
	$line =~ s/\{relacion}/$relacion/g;
	$line =~ s/\{todate}/$todate/g;
	$line =~ s/\{entregado}/$entregado/g;	
	$pagina .= $line;
    }
    print "$pagina";
    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;
}
sub big_money {
  my $number = sprintf "%.2f", shift @_;
  # Add one comma each time through the do-nothing loop
  1 while $number =~ s/^(-?\d+)(\d\d\d)/$1,$2/;
  # Put the dollar sign in the right place
  $number =~ s/^(-?)/$1\$/;
  $number;
}
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 $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";
	$vv="\$";
	$template_rows = "";
	$template_rows  = $template_rows."<TR class=my_style3><TD width=175px>TARIFAS</TD><TD align=center>MONTO</TD></TR>";
	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_style3><TD>$row[0]</TD><TD align=center>$vv$valor_tributo</TD></TR>"; 
	}
        if($suma_multas<2.86){$suma_multas=2.86}
        $template_rows	= $template_rows."<TR class=my_style3><TD>Fiestas</TD><TD align=center>$vv$suma_fiestas</TD></TR>";
        $template_rows	= $template_rows."<TR class=my_style3><TD>Intereses</TD><TD align=center>$vv$suma_intereses</TD></TR>";
        $template_rows	= $template_rows."<TR class=my_style3><TD>Multas</TD><TD align=center>$vv$suma_multas</TD></TR>";
        $suma_total	= $suma_monto+$suma_fiestas+$suma_intereses+$suma_multas;
        $template_rows	= $template_rows."<TR class=my_style3><TD>TOTAL</TD><TD align=center>$vv$suma_total</TD></TR>";
        $table 		= $template_table;
        $table 		=~ s/{body}/$template_rows/g;
	if(substr($id,0,1) eq "1"){$clase="inmueble"}
	if(substr($id,0,1) eq "2"){$clase="empresa"}
	return $table;	
}
sub get_min_periodo {
    my ($id)=@_;
    my $dt  = "";
    my $sql = "select min(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();
	$dt    = $rowx[0];
    }
    my $mes = substr($dt,4,2);
    my $ano = substr($dt,0,4);

    return "01/".$mes."/".$ano; 
}
sub get_ultimo_periodo {
    my($id)=@_;
    my $dt  = "";
    my $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();
	$dt   = $rowx[0];
    }
    my $ano = substr($dt,0,4);
    my $mes = substr($dt,4,2);
    my $dia = Days_in_Month($ano,$mes);

    return $dia."/".$mes."/".$ano;
}
