#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Author:Rodolfo Machon
# Company: WebSoft, SA de CV 
# Nombre del archivo: mora_general_rept.pl
# Created: 2014-02-12
# Updates: 2020-06-12
# Copyright - All Rights Reserved
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#Perl Modules
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use XML::Simple;
use DBI;
binmode STDOUT, ":encoding(utf8)";
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html; charset=utf-8\n\n";
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# GET PARAMETERS
$form					= new CGI;
$lan					= $form->param('lan');
$appid					= $form->param('appid');
$clase					= $form->param('clase');
$i2_tipo_rango_mora_id  = $form->param('i2_tipo_rango_mora_id');
$c11_sector_id          = $form->param('c11_sector_id');
$field_value            = $form->param('field_value'); ## avenida, pasaje, calle, senda, poligono, block
$field                  = $form->param('field');
$titulo_clase   		= $clase."S";
$titulo_clase   		=~ tr/a-z/A-Z/;
#------------------------------------------------------------------
#------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
$PATH		= "../".$lan."/";
$file 		= $lan."_mora_general_rept.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# OPEN DATABASE CONNECTIONS - ODBCs
open_database_connections();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
%tributos = get_tributos();
%rangos   = get_rangos();
get_expedientes_rangos();
# INTEGRATION
%mora 	= get_mora();

$table	= construct_table();
# PRESENTATION
construct_page();
exit;
#-------------------------------------------------------------------
#MAIN-SUBROUTINES
sub get_tributos{
	my %tributos = ();
	my %tarifas  = ();
	my $sql = "select a.c16_tarifa_id,b.v128_tarifa from sdd_".$clase."_estados_mensuales a, sdm_tarifas b where a.c16_tarifa_id=b.c16_tarifa_id group by a.c16_tarifa_id order by a.c16_tarifa_id";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die 'sub get_sectores';
	while (@row = $sth->fetchrow_array) {
        $tarifas{substr($row[0],0,13)} = [@row];
    }
	return %tarifas;
}
sub get_rangos{
	my %hash = ();
	my $sql    = "select i2_rango_id,i4_rango_inicial,i4_rango_final,v64_rango from sdm_rangos_dim where i2_tipo_rango_mora_id = $i2_tipo_rango_mora_id order by 1";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die 'sub get_sectores';
	while (@row = $sth->fetchrow_array) {
		$hash{$row[0]} = [@row];
    }
	return %hash;
}
sub get_expedientes_rangos{
	my $rango_inicial = "";
	my $rango_final	  = "";
	foreach $k (sort keys %rangos)        {
		$rango_inicial  = $rangos{$k}[1];
		$rango_final    = $rangos{$k}[2];
		($rangos{$k}[4],$rangos{$k}[5])  = get_expedientes($rango_inicial,$rango_final);
	}
}
sub get_mora {
	my %hash = ();
	foreach $k (sort keys %tributos)  {
		foreach $kk (sort keys %rangos) {
			my $rango_inicial     = $rangos{$kk}[1];
			my $rango_final       = $rangos{$kk}[2];
			my $str_expedientes   = $rangos{$kk}[4];

			my ($d82_val,$d82_fie,$d82_int,$d82_mul)  =  get_mora_tributo($k,$str_expedientes,$rango_inicial,$rango_final);

			$hash{$k}{$kk}[0]=$d82_val;
			$hash{$k}{$kk}[1]=$d82_fie+$d82_int+$d82_mul;
		}
	}
	#foreach $k (sort keys %hash)  {
	#	foreach $kk (sort keys %{$hash{$k}}) {
	#		print "$k > $kk > $hash{$k}{$kk}[0] <br>";
	#	}
	#}	
	return %hash;
}
sub construct_table {
	my %total = ();
	my $table = "<table id=tabla cellspacing=0 cellpadding=0 width=100% border=1 bordercolor=#E5E5E5 style=border-width:0.5>{thead}{tbody}{tfoot}</table>";
	my $trow  = "<tr><td>{tributo}</td>{rango}</tr>";
	my $rowset= "";
	my $thead = "<tr><td></td>{head}</tr><tr><td>DESCRIPCION TRIBUTO</td>{head2}</tr>";
	my $head  = "";
	my $head2 = "";
	my $tfoot = "<tr><td align=right>TOTALES</td>{foot}</tr>";
	my $foot  = "";
	
	foreach $k (sort {$a<=>$b} keys %rangos)  {
		$head  .= "<td colspan=2 align=center>$rangos{$k}[3]</td>";
		$head2 .= "<td align=center>TRIBUTO</td><td align=center>ACC</td>"
	}
	foreach $k (sort keys %mora)  {
		my $rango = "";
		my $row   = $trow;
		$row =~ s/\{tributo}/$tributos{$k}[1]/g;
		foreach $kk (sort {$a<=>$b} keys %{$mora{$k}}) {
			$rango .= "<td align=right> $mora{$k}{$kk}[0] </td><td align=right> $mora{$k}{$kk}[1] </td>";
			$total{$kk}{'tributo'} += $mora{$k}{$kk}[0];
			$total{$kk}{'accesorio'} += $mora{$k}{$kk}[1];
		}
		$row =~ s/\{rango}/$rango/g;
		$rowset .= $row;
	}
	foreach $k (sort {$a<=>$b} keys %rangos)  {
		$foot .= "<td align=center><a href=# onclick=OpenListExpedientes('$rangos{$k}[4]')>".$total{$k}{'tributo'}."</a></td><td align=center>".$total{$k}{'accesorio'}."</td>";
	}
	
	$thead =~ s/\{head}/$head/g;
	$thead =~ s/\{head2}/$head2/g;
	$tfoot =~ s/\{foot}/$foot/g;
	$table =~ s/\{thead}/$thead/g;	
	$table =~ s/\{tbody}/$rowset/g;
	$table =~ s/\{tfoot}/$tfoot/g;	
	
	return $table;
}
sub construct_page {
	open (DATA, "$PATH$file");
	if($clase eq "inmueble"){$clases=" INMUEBLES "}
	if($clase eq "empresa"){$clases=" EMPRESAS "}
	if($i2_tipo_rango_mora_id eq "1"){$tipo=" ANTIGUEDAD "}
	if($i2_tipo_rango_mora_id eq "2"){$tipo=" MONTO "}
	while ($line = <DATA>) {	
		$line =~ s/\{lan}/$lan/g;
		$line =~ s/\{appid}/$appid/g;
		$line =~ s/\{table}/$table/g;	 
		$line =~ s/\{clase}/$clase/g;
		$line =~ s/\{clases}/$clases/g;
		$line =~ s/\{tipo}/$tipo/g;
        $line =~ s/\{titulo_clase}/$titulo_clase/g;
		print "$line";
	}
}
#-------------------------------------------------------------------
# LEVEL 2
sub get_expedientes {
	my($rango_inicial,$rango_final)=@_;
	my $count = 0;
	my $str = "";

	if($i2_tipo_rango_mora_id eq "1"){$condition=" max(i4_dias_mora) "}else{$condition=" (sum(d82_val)+sum(d82_fie)+sum(d82_int)+sum(d82_mul)) "}

	my $sql ="select i8_".$clase."_id from sdd_".$clase."_estados_mensuales where i4_dias_mora>0 and d82_val>0 group by i8_".$clase."_id having $condition between $rango_inicial and  $rango_final";	

	my $sth = $dbh->prepare($sql);
	$sth->execute or die 'sub get_sectores';
	while (@row = $sth->fetchrow_array) {
		$str .= $row[0].",";
		$count++;
	}
	$str = substr($str,0,length($str)-1);
    return ($str,$count);
}
sub get_mora_tributo{
    my($c8_tributo_id,$str_expedientes,$rango_inicial,$rango_final)=@_;
	my $d82_val	= 0;
	my $d82_fie	= 0;
	my $d82_int	= 0;
	my $d82_mul	= 0;

	if ($str_expedientes eq "") {$str_expedientes="0";}
    
	my $sql		= "select sum(d82_val)
						 ,sum(d82_fie)
						 ,sum(d82_int)
						 ,sum(d82_mul)
				   from sdd_".$clase."_estados_mensuales
				   where c16_tarifa_id like '$c8_tributo_id%' and i8_".$clase."_id in ($str_expedientes) and i4_dias_mora > 0 and d82_val>0";
	#print "$sql <br>";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die 'sub get_sectores';
	while (@row = $sth->fetchrow_array) {
		$d82_val = $row[0];
		$d82_fie = $row[1];
		$d82_int = $row[2];
		$d82_mul = $row[3];
    }
   	$d82_val = sprintf("%.2f",$d82_val);
	$d82_fie = sprintf("%.2f",$d82_fie);
	$d82_int = sprintf("%.2f",$d82_int);
	$d82_mul = sprintf("%.2f",$d82_mul);
	
    return ($d82_val,$d82_fie,$d82_int,$d82_mul);    
}
#--------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub SQL_fail {
    my ($dbh,$query,$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;
}
#--------------------------------------------------------------------
