#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Author:Rodolfo Machon
# Company:WebSoft, SA de CV 
# Nombre del archivo: mora_notificacion_expedietes_rept.pl
# Created: 2022-11-27
# Updates: 2022-11-27
# Copyright - All Rights Reserved
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#PERL MODULES
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use Date::Calc qw(Add_Delta_Days);
use Date::Calc qw(Days_in_Month);
use XML::Simple;
use DBI;
binmode STDOUT, ":encoding(utf8)";
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
print "Content-type: text/html; charset=utf-8\n\n";
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# GET PARAMETERS
$form 		= new CGI;
$clase		= $form->param('clase');
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# OPEN DATABASE CONNECTIONS - ODBCs
get_app_data();
open_database_connections();
#-------------------------------------------------------------------
#-------------------------------------------------------------------
$str_expedientes= $form->param('str_expedientes');

$template_table = "<TABLE cellspacing=0 cellpadding=0 border=1>{body}</TABLE>";
$PATH	= "../".$lan."/";
$file 	= $lan."_mora_notificacion_expedientes_rept.html";

procesar_expedientes();

exit;
#-------------------------------------------------------------------
#-------------------------------------------------------------------
# LEVEL I
sub procesar_expedientes {
    @expedientes    = split(/\|/,$str_expedientes);
    for($i=0;$i<=$#expedientes;$i++) {
        my $id         = $expedientes[$i];
        my $nombre     = get_nombre('sdd_'.$clase.'_contribuyentes a,sdm_contribuyentes b','b.v128_contribuyente',$id);
        my $direccion  = get_direccion($id);
        my $desde      = get_min_periodo($id);
        my $hasta      = get_ultimo_periodo($id);
        my $total_mora = get_total_mora($id);
        construct_page($id,$nombre,$direccion,$desde,$hasta,$total_mora);
    }
}
sub get_nombre{
    my ($tables,$fields,$id)= @_;
    my $r     = "";
    my $rs    = "";
    my $sql = "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 "$sql<br>";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die 'sub get_';
	while (@row = $sth->fetchrow_array) {
	    $r	 = $row[0];
    }
    return $r;
}
sub get_direccion {
	my($expediente_id)=@_;
	my $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>";
	$sth = $dbh->prepare($sql);
	$sth->execute or die 'sub get_';
	while (@row = $sth->fetchrow_array) {
		$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'";
		$sth1 = $dbh->prepare($sql);
		$sth1->execute or die 'sub get_';
		if (@row1 = $sth1->fetchrow_array) {
			$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'";
		$sth1 = $dbh->prepare($sql);
		$sth1->execute or die 'sub get_';
		if (@row2 = $sth1->fetchrow_array) {
			 $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'";
		$sth1 = $dbh->prepare($sql);
		$sth1->execute or die 'sub get_';
		if (@row2 = $sth1->fetchrow_array) {
			$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'";
		$sth1 = $dbh->prepare($sql);
		$sth1->execute or die 'sub get_';
		if (@row2 = $sth1->fetchrow_array) {
			$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'";
		$sth1 = $dbh->prepare($sql);
		$sth1->execute or die 'sub get_';
		if (@row2 = $sth1->fetchrow_array) {
			$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'";
		$sth1 = $dbh->prepare($sql);
		$sth1->execute or die 'sub get_';
		if (@row2 = $sth1->fetchrow_array) {
			$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'";
		$sth1 = $dbh->prepare($sql);
		$sth1->execute or die 'sub get_';
		if (@row2 = $sth1->fetchrow_array) {
			$poligono=$row2[0];$direccion=$direccion.",".$poligono;
		}
        if($direccion ne ""){
			$direccion=$direccion." ".$casa;
        }
	}
	return $direccion;
}
sub get_min_periodo {
    my ($id)=@_;
    my $dt  = "";
    my $sql = "select min(i8_periodo) from sdd_".$clase."_estados_mensuales where i8_".$clase."_id='$id'";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die 'sub get_';
	while (@row = $sth->fetchrow_array) {
        $dt    = $row[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'";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die 'sub get_';
	while (@row = $sth->fetchrow_array) {
        $dt   = $row[0];
    }
    my $ano = substr($dt,0,4);
    my $mes = substr($dt,4,2);
    my $dia = Days_in_Month($ano,$mes);

    return $dia."/".$mes."/".$ano;
}
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' and d82_val>0";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die 'sub get_';
	while (@row = $sth->fetchrow_array) {
        $tm   = $row[0];
    }
    $tm = big_money($tm);	    
    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 $sql 	   = "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 "$sql";
	$vv="\$";
	$template_rows = "";
	$template_rows  = $template_rows."<TR class=my_style3><TD width=175px>TARIFAS</TD><TD align=center>MONTO</TD></TR>";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die 'sub get_';
	while (@row = $sth->fetchrow_array) {
		$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 construct_page {
  my ($id,$nombre,$direccion,$desde,$hasta,$total_mora)= @_;
  $arc=$file;
  $pagina = "";
  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/\{city}/$city/g;
    $line =~ s/\{nombre}/$nombre/g;
    $line =~ s/\{direccion}/$direccion/g;
    $line =~ s/\{generador}/$clase/g;
    $line =~ s/\{desde}/$desde/g;
    $line =~ s/\{hasta}/$hasta/g;
    $line =~ s/\{total_mora}/$total_mora/g;
    $pagina .= $line;
  }
  print "$pagina";
  return 0;
}
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 imprimir_notificacion {
  my($i,$id,$dir,$periodo,$periodo1)=@_;
 construct_page($tabla,$nombre,$dir,$id,$periodo,$periodo1);
}
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# AUXILIAR
sub get_app_data {
    my $schema  = "app.data.xml";
    my $xml     = new XML::Simple;
    my $data    = $xml->XMLin("../xml/".$schema);
    $appid      = $data->{portal}->{app};
    $lan        = $data->{portal}->{lang};
    $city       = $data->{portal}->{city};	
}
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}
sub open_database_connections {
	$dbh 	= DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
	if (!$dbh) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
	$dbh->{LongReadLen} = 512 * 1024;
	$dbh->{LongTruncOk} = 1;
	$dbh1 	= DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
	if (!$dbh1) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
	$dbh1->{LongReadLen} = 512 * 1024;
	$dbh1->{LongTruncOk} = 1;
	$dbh2 	= DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
	if (!$dbh2) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
	$dbh2->{LongReadLen} = 512 * 1024;
	$dbh2->{LongTruncOk} = 1;
}