#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Author:Rodolfo Machon
# Company:WebSoft, SA de CV 
# Nombre del archivo: inmueble.balance.rept.pl
# Created: 2016-08-01
# Updates: 2016-08-01,2022-08-18
# Copyright - All Rights Reserved
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use DBI;
use XML::Simple;
use Data::Dumper;
use CGI::Carp qw(fatalsToBrowser);
binmode STDOUT, ":encoding(utf8)";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html; charset=utf-8\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$form	        	= new CGI;
$i8_expediente_id 	= $form->param("i8_expediente_id");
$generador          = $form->param("generador");
$abonos				= $form->param("Abonos");
$dt_emision			= $form->param("dt_emision");
$dt_vencimiento		= $form->param("dt_vencimiento");		
$i8_contribuyente_id= $form->param("i8_contribuyente_id");	
$v128_contribuyente	= $form->param("v128_contribuyente");	
$i8_expediente_id	= $form->param("i8_expediente_id");		
$c11_dui_id 		= $form->param("c11_dui_id"); 			
$c14_nit_id 		= $form->param("c14_nit_id"); 			
$c14_iva_id 		= $form->param("c14_iva_id"); 			
$v16_fax  			= $form->param("v16_fax");  				
$v16_telefono		= $form->param("v16_telefono");			
$txt_direccion		= $form->param("txt_direccion");			
$txt_email		  	= $form->param("txt_email");		  		
$propietarios		= $form->param("propietarios");			
$v64_tipo_sector	= $form->param("v64_tipo_sector");		
$v64_sector			= $form->param("v64_sector");			
$v64_poligono		= $form->param("v64_poligono");			
$v64_block			= $form->param("v64_block");				
$v64_calle			= $form->param("v64_calle");				
$v64_avenida		= $form->param("v64_avenida");			
$v64_senda			= $form->param("v64_senda");				
$v64_pasaje			= $form->param("v64_pasaje");			
$c4_casa			= $form->param("c4_casa");
$v64_barrio			= $form->param("v64_barrio");
$i8_nic_id			= $form->param("i8_nic_id");
$i8_nis_id			= $form->param("i8_nis_id");
$c16_codigo_catastral_id = $form->param("c16_codigo_catastral_id");
$c8_cuenta_rct_id	= $form->param("c8_cuenta_rct_id");
$txt_referencia		= $form->param("txt_referencia");
$fecha_emision		= $form->param("fecha_emision");
$fecha_vencimiento	= $form->param("fecha_vencimiento");
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET VARIABLES
get_app_data();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET VARIABLES
$PATH 			= "../es/";
$file_html      = $lan."_empresas.balance.rept.html";
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# OPEN DATABASE CONNECTIONS - ODBCs
$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;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#MAIN PROGRAM
%tarifas_val 	= put_tarifas_val();
%tarifas_vals 	= put_tarifas_vals();
%tarifas_desc   = get_tarifas_desc();
%tributos_desc	= get_tributos_desc();

$table = construct_table();

construct_page();
	
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# SUB-ROUTINES - LEVEL 1
sub put_tarifas_val {
	my %hash = ();
	my @per_tar = split(/\~~/,$abonos);
	for(my $i=0;$i<=$#per_tar;$i++){
		my @tar_d = split(/\|/,$per_tar[$i]);
		$hash{$tar_d[3]} = $tar_d[4];
	}
	return %hash;
}
sub put_tarifas_vals {
	my %hash = ();
	my @per_tar = split(/\~~/,$abonos);
	for(my $i=0;$i<=$#per_tar;$i++){
		my @tar_d = split(/\|/,$per_tar[$i]);
		$hash{$tar_d[3]}{'val'} += $tar_d[4];
		$hash{$tar_d[3]}{'fie'} += $tar_d[5];
		$hash{$tar_d[3]}{'int'} += $tar_d[6];
		$hash{$tar_d[3]}{'mul'} += $tar_d[7];
		$hash{$tar_d[3]}{'per'} += 1;		
	}
	return %hash;
}
sub get_tarifas_desc {
	my $str		= "";
	my %hash	= ();
	my %hash1	= ();
	foreach my $k (sort keys %tarifas_vals){$hash1{$k}=$k}	
	foreach my $k (sort keys %hash1){$str .= "'".$k."',"}
	$str	= substr($str,0,length($str)-1);
	my $sql = "SELECT a.c16_tarifa_id,a.dt_desde FROM sdm_tarifas a WHERE a.c16_tarifa_id IN ($str)";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_tarifas_vals');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]} = $row[1];
	}
	return %hash;
}
sub get_tributos_desc {
	my $str		= "";
	my %hash	= ();
	foreach my $k (sort keys %tarifas_desc){$str .= "'".substr($k,0,8)."',"}
	$str	= substr($str,0,length($str)-1);
	my $sql = "SELECT a.c8_tributo_id,a.v64_tributo FROM sdm_tributos a WHERE a.c8_tributo_id IN ($str)";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_tributos_desc');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]} = $row[1];
	}
	return %hash;
}
sub construct_table {
	my %hash	= ();
	my %tribs	= ();
	my %foots	= ();	
	my $t_val	= 0;
	my $t_fie	= 0;
	my $t_int	= 0;
	my $t_mul	= 0;
	my $tot		= 0;
	my $t_tab 	= "";
	my $rowhead	= "";
	my $rowhead1= "";	
	my $rowset	= "";
	my $rowfoot	= "";	
	my $thead	= "";
	my $tbody	= "<tbody>{rowset}</tbody>";
	my $tfoot	= "<tfoot><td>TOTAL</td><tr>{tfoot}<tr></tfoot>";
	foreach my $k (sort keys %tarifas_vals){
		$hash{$tarifas_desc{$k}}{substr($k,0,8)}{'val'} += $tarifas_vals{$k}{'val'};
		$hash{$tarifas_desc{$k}}{substr($k,0,8)}{'fie'} += $tarifas_vals{$k}{'fie'};
		$hash{$tarifas_desc{$k}}{substr($k,0,8)}{'int'} += $tarifas_vals{$k}{'int'};
		$hash{$tarifas_desc{$k}}{substr($k,0,8)}{'mul'} += $tarifas_vals{$k}{'mul'};
		$hash{$tarifas_desc{$k}}{substr($k,0,8)}{'per'} += $tarifas_vals{$k}{'per'};
		$hash{$tarifas_desc{$k}}{substr($k,0,8)}{'tar'}  = $tarifas_val{$k};			
		$tribs{substr($k,0,8)} 	= $tributos_desc{substr($k,0,8)};
	}
	foreach my $kk (sort keys %tribs){
		$rowhead .= "<th colspan='3'>".$tribs{$kk}."</th>";
		$rowhead1.= "<th>MESES</th><th>VAL</th><th>TOT</th>"; 
	}
	foreach my $k (sort keys %hash){
		my $per		= 0; 
		my $val		= 0; 
		my $fie		= 0; 
		my $int		= 0; 
		my $mul		= 0;
		my $tot_ord = 0;
		my $row = "<td class='center'>".substr($k,0,7)."</td>";
		foreach my $kk (sort keys %tribs){
			if ($hash{$k}{$kk}) {
				$row .= "<td>".$hash{$k}{$kk}{'per'}."</td>";
				$row .= "<td>".round($hash{$k}{$kk}{'tar'},2)."</td>";				
				$row .= "<td>".round($hash{$k}{$kk}{'val'},2)."</td>";
				
				$foots{$kk}{'per'} += $hash{$k}{$kk}{'per'};
				$foots{$kk}{'val'} += round($hash{$k}{$kk}{'val'},2);				

				$val += round($hash{$k}{$kk}{'val'},2);				
				$fie += round($hash{$k}{$kk}{'fie'},2);
				$int += round($hash{$k}{$kk}{'int'},2);
				$mul += round($hash{$k}{$kk}{'mul'},2);
				
				$tot_ord = round($val,2)+round($fie,2)+round($int,2)+round($mul,2);
			}else{
				$row .= "<td></td><td></td><td></td>";
			}
		}
		
		$rowset .= "<tr>".$row."<td>".round($val,2)."</td><td>".round($fie,2)."</td><td>".round($int,2)."</td><td>".round($mul,2)."</td><td>".round($tot_ord,2)."</td></tr>";
		$t_val	+= round($val,2);
		$t_fie	+= round($fie,2);
		$t_int	+= round($int,2);
		$t_mul	+= round($mul,2);
		$tot 	+= round($tot_ord,2);
	}
	
	if($t_mul<=2.87){$t_mul = 2.87; $tot=round($t_val+$t_fie+$t_int+$t_mul,2);}
	foreach my $kk (sort keys %foots){
		$rowfoot.= "<td>".$foots{$kk}{'per'}."</td><td></td><td>".round($foots{$kk}{'val'},2)."</td>"; 
	}
	$thead  = "<thead><tr><th></th>".$rowhead."<th colspan='4'>ACCESORIO</th></tr><tr><th>ORDENANZAS</th>".$rowhead1."<th>VAL</th><th>FIE</th><th>INT</th><th>MUL</th><th>TOTAL</th></tr></thead>";
	$tfoot  = "<tfoot><tr><td>TOTAL</td>".$rowfoot."<td>".round($t_val,2)."</td><td>".round($t_fie,2)."</td><td>".round($t_int,2)."</td><td>".round($t_mul,2)."</td><td>".round($tot,2)."</td></tr></tfoot>";
	$tbody  =~ s/{rowset}/$rowset/g;
	$tab 	= "<table id='grid'>".$thead.$tbody.$tfoot."</table>";
	$tab	=~ s/>0.00<//g;
	return $tab;
}
sub construct_page{
	open (DATA, "$PATH$file_html");
	while ($line = <DATA>) {
		$line =~ s/{lan}/$lan/g;
		$line =~ s/{appid}/$appid/g;
		$line =~ s/{i8_expediente_id}/$i8_expediente_id/g;		
		$line =~ s/{generador}/$generador/g;
		$line =~ s/{table}/$table/g;
		$line =~ s/{dt_emision}/$dt_emision/g;
		$line =~ s/{dt_vencimiento}/$dt_vencimiento/g;
		$line =~ s/{i8_contribuyente_id}/$i8_contribuyente_id/g;
		$line =~ s/{v128_contribuyente}/$v128_contribuyente/g;
		$line =~ s/{i8_expediente_id}/$i8_expediente_id/g;
		$line =~ s/{c11_dui_id}/$c11_dui_id/g;
		$line =~ s/{c14_nit_id}/$c14_nit_id/g;
		$line =~ s/{c14_iva_id}/$c14_iva_id/g;
		$line =~ s/{v16_fax}/$v16_fax/g;
		$line =~ s/{v16_telefono}/$v16_telefono/g;
		$line =~ s/{txt_direccion}/$txt_direccion/g;
		$line =~ s/{txt_email}/$txt_email/g;  		
		$line =~ s/{propietarios}/$propietarios/g;	
		$line =~ s/{v64_tipo_sector}/$v64_tipo_sector/g;
		$line =~ s/{v64_sector}/$v64_sector/g;	
		$line =~ s/{v64_poligono}/$v64_poligono/g;
		$line =~ s/{v64_block}/$v64_block/g;
		$line =~ s/{v64_calle}/$v64_calle/g;
		$line =~ s/{v64_avenida}/$v64_avenida/g;
		$line =~ s/{v64_senda}/$v64_senda/g;
		$line =~ s/{v64_pasaje}/$v64_pasaje/g;
		$line =~ s/{c4_casa}/$c4_casa/g;
		$line =~ s/{i8_nic_id}/$i8_nic_id/g;
		$line =~ s/{i8_nis_id}/$i8_nis_id/g;
		$line =~ s/{c16_codigo_catastral_id}/$c16_codigo_catastral_id/g;
		$line =~ s/{c8_cuenta_rct_id}/$c8_cuenta_rct_id/g;
		$line =~ s/{txt_referencia}/$txt_referencia/g;
		$line =~ s/{fecha_emision}/$fecha_emision/g;
		$line =~ s/{fecha_vencimiento}/$fecha_vencimiento/g;		
		print $line;
	}		
	close (DATA);
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
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};
}
sub round {
    my ($n, $places) = @_;
    my $abs = abs $n;
    my $val = substr($abs + ('0.' . '0' x $places . '5'),0,length(int($abs)) +(($places > 0) ? $places + 1 : 0));
    ($n < 0) ? "-" . $val : $val;
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------