#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Author:Rodolfo Machon
# Company:WebSoft, SA de CV 
# Nombre del archivo: ingresos_diarios_rubros_cuentas_ingresos_p.pl
# Created: 2016-07-25
# Updates: 2016-07-25
# Copyright - All Rights Reserved
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#PERL MODULES
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use Encode;
use Encode qw/is_utf8 decode/;
#--------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html; charset=utf-8\n\n";
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# GET PARAMETERS
$cgiparms 	= new CGI;
$lan	  	= $cgiparms->param('lan');
$appid	  	= $cgiparms->param('appid');
$dt_desde 	= $cgiparms->param('dt_desde');
$dt_hasta 	= $cgiparms->param('dt_hasta');
$i2_cajero_id = $cgiparms->param('i2_cajero_id');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($lan eq ""){print "Your are not passing LAN parameter<br>";}
if ($appid eq ""){print "Your are not passing APPID parameter<br>";}
if ($appid eq ""){print "Your are not passing APPID parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH 	    = "../".$lan."/";
$file       = $lan."_ingresos_diarios_rubros_cuentas_ingresos_p.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;
$dbh->{LongTruncOk} = 1;
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# MAIN-PROGRAM
# PROCUREMENT
$periodo_actual	 	= get_periodo_actual();
%recibos 	   	 	= get_recibos();
$recibos_cuentas_ref= get_recibos_cuentas();
%recibos_cuentas 	= %$recibos_cuentas_ref;
%rubros				= get_rubros();
# INTEGRATION
#
# PRESENTATION
$table		   = construct_table();
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_recibos {
	my %hash  = ();
	my %hash1 = ();
	my $cajero= "";
	if($i2_cajero_id==0){$cajero = ""}else {$cajero = "and a.i2_cajero_id=$i2_cajero_id"}	
	my $sql  = "SELECT  a.c16_recibo_id,
			    a.dt_emision,
			    c.v64_tipo_departamento,
			    a.i8_contribuyente_id,
			    a.v128_contribuyente,
			    (b.`d82_cantidad`*b.`d82_tarifa`) as TOTAL
		    FROM sdm_recibos a
		    INNER JOIN sdd_recibos b ON a.c16_recibo_id=b.c16_recibo_id
		    LEFT JOIN sdm_tipo_departamentos c ON a.i2_tipo_departamento_id=c.i2_tipo_departamento_id
		    WHERE a.dt_emision >= '$dt_desde' and a.dt_emision <= '$dt_hasta' $cajero
		    ORDER BY a.c16_recibo_id+0
			";
	#print "$sql <>";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_recibos');
    while (@row = $sth->fetchrow_array) {
	    $hash{$row[0]}   = [@row];
	    $hash1{$row[0]} += $row[5];
	}
	foreach my $k (sort keys %hash){
	    my @arr   = @{$hash{$k}};
	    my $rec_t = sprintf("%.2f",$hash1{$k});
	    pop(@arr);
	    push(@arr,$rec_t);
	    $hash{$k} =[@arr];
	}
	return %hash;
}
sub get_recibos_cuentas{
	my %partida = ();
	my %tarifas = ();
	my %cuenta  = ();	
	foreach my $k (sort keys %recibos){
		$tarifas_ref = get_recibo_tarifas($k);
		$tarifas{$k} = {%$tarifas_ref};
		$cuentas_ref = get_tarifas_cuentas(%{$tarifas{$k}});
		$cuentas{$k} = {%$cuentas_ref};
		$partida_ref = get_debe_haber(\%{$tarifas{$k}},\%{$cuentas{$k}});
		$partida{$k} = {%$partida_ref};
	}
	return \%partida;
}
sub get_rubros {
	my %hash = ();
	my $sql  = "SELECT a.c16_cuenta_ingreso_id,a.v128_tarifa FROM sdm_tarifas a	";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_user_name');
    while (@row = $sth->fetchrow_array) {
	    $hash{$row[0]} = $row[1];
	}
	return %hash;
}
sub construct_table {
    my $count   = 1;
    my $total   = 0;
    my $table   = "";
    my %thead_h = ();
    my %tfoot_h = ();    
	my %tcuen_h = ();
    my $thead_s = "";
    my $thead_s1= "";	
    my $tfoot_s = "";    
    my $rowset  = "";
    my $thead   = "<thead><tr><TD>CUENTA</TD><TD>RUBRO</TD><TD>PASADO</TD><TD>PRESENTE</TD><TD>FUTURO</TD></tr></thead>";
    my $tfoot   = "<tfoot><tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>TOTAL</td><td>{total}</td>{tfoot_s}</tr></tfoot>";    

    foreach my $k (sort keys %recibos_cuentas){
		foreach my $kk (sort keys %{$recibos_cuentas{$k}}){
			foreach my $kkk (sort keys %{$recibos_cuentas{$k}{$kk}}){			
				#print "$k > $kk > $kkk > $recibos_cuentas{$k}{$kk}{$kkk}<br>";
				$thead_h{$kkk}=$kkk;
			}
		}
	}
    foreach my $k (sort {$a<=>$b} keys %thead_h){
		$thead_s .= "<td colspan=3>".$k."</td>";
		$thead_s1.= "<td>PAS</td><td>ACT</td><td>FUT</td>";		
	}	
    foreach my $k (sort keys %recibos){
		my @arr = @{$recibos{$k}};
		my $row = join("</td><td>",@arr);
		$row     = "<tr><td><a href=javascript:OpenLink('$k','$recibos{$k}[1]')>$count</a></td><td>".$row."</td>";
		foreach my $kk (sort {$a<=>$b} keys %thead_h){
			my $ing_p = $recibos_cuentas{$k}{'recurso_pasado'}{$kk};
			my $ing_a = $recibos_cuentas{$k}{'recurso'}{$kk};
			my $ing_f = $recibos_cuentas{$k}{'recurso_futuro'}{$kk};			

			if($ing_p ne ""){$ing_p = sprintf("%.2f",$ing_p);}else{$ing_p ="&nbsp;"}
			if($ing_a ne ""){$ing_a = sprintf("%.2f",$ing_a);}else{$ing_a ="&nbsp;"}
			if($ing_f ne ""){$ing_f = sprintf("%.2f",$ing_f);}else{$ing_f ="&nbsp;"}			
			$row .= "<td>$ing_p</td>";
			$row .= "<td>$ing_a</td>";
			$row .= "<td>$ing_f</td>";			
			$tfoot_h{$kk}{'pasado'} += sprintf("%.2f",$ing_p);
			$tfoot_h{$kk}{'actual'} += sprintf("%.2f",$ing_a);
			$tfoot_h{$kk}{'futuro'} += sprintf("%.2f",$ing_f);			
		}		
		$rowset .= $row."</tr>";
		$total  += $arr[5];
		$count++;
	}
    foreach my $k (sort {$a<=>$b} keys %tfoot_h){
		$tfoot_s .= "<td>".sprintf("%.2f",$tfoot_h{$k}{'pasado'})."</td><td>".sprintf("%.2f",$tfoot_h{$k}{'actual'})."</td><td>".sprintf("%.2f",$tfoot_h{$k}{'futuro'})."</td>";		
	}
	my $t_pasado = 0;
	my $t_actual = 0;
	my $t_futuro = 0;
	my $t_total  = 0;
    my $rowset1  = "";
    my $thead1   = "<thead><tr><TD>CUENTA</TD><TD>RUBRO</TD><TD>SUB-TOTAL</TD><TD>PASADO</TD><TD>PRESENTE</TD><TD>FUTURO</TD></tr></thead>";
    my $tfoot1   = "<tfoot><tr><td>&nbsp;</td><td>TOTAL</td><td>{t_total}</td><td>{t_pasado}</td><td>{t_actual}</td><td>{t_futuro}</td></tr></t_futuro>"; 	

    foreach my $k (sort {$a<=>$b} keys %tfoot_h){
		my $sub   = sprintf("%.2f",$tfoot_h{$k}{'pasado'}+$tfoot_h{$k}{'actual'}+$tfoot_h{$k}{'futuro'});
		if ($sub>0) {
			$rowset1 .= "<tr><td>$k</td><td>$rubros{$k}</td><td>$sub</td><td>".sprintf("%.2f",$tfoot_h{$k}{'pasado'})."</td><td>".sprintf("%.2f",$tfoot_h{$k}{'actual'})."</td><td>".sprintf("%.2f",$tfoot_h{$k}{'futuro'})."</td><tr>";
			$t_pasado += $tfoot_h{$k}{'pasado'};
			$t_actual += $tfoot_h{$k}{'actual'};
			$t_futuro += $tfoot_h{$k}{'futuro'};
			$t_total  += $sub;
		}
	}	
	
    $total  = sprintf("%.2f",$total);
    $thead  =~ s/\{thead_s}/$thead_s/g;
    $thead  =~ s/\{thead_s1}/$thead_s1/g;
    $tfoot  =~ s/\{total}/$total/g;
    $tfoot  =~ s/\{tfoot_s}/$tfoot_s/g;
    $thead  =~ s/\{tfoot_s1}/$tfoot_s/g;
    $thead  =~ s/\{total}/$total/g;
    $tfoot  =~ s/\<td>0.00/<td>/g;
    $thead  =~ s/\<td>0.00/<td>/g;
    $rowset = "<tbody>".$rowset."</tbody>";
	#$table  = "<table id=grid>".$thead.$rowset.$tfoot."</table>";
    $tfoot1  =~ s/\{t_pasado}/$t_pasado/g;
    $tfoot1  =~ s/\{t_actual}/$t_actual/g;
    $tfoot1  =~ s/\{t_futuro}/$t_futuro/g;
    $tfoot1  =~ s/\{t_total}/$t_total/g;
	$table  = "<table id=grid>".$thead1.$rowset1.$tfoot1."</table>";
	
    $table  = convertir_tildes($table);
    return $table;
}
sub construct_page {
	my $cajero = "";
	if($i2_cajero_id ne "0"){$cajero = "CAJA $i2_cajero_id"}
	open (DATA, "$PATH$file");
	while ($line = <DATA>) {	
		$line =~ s/\{lan}/$lan/g;
		$line =~ s/\{appid}/$appid/g;
		$line =~ s/\{table}/$table/g;
		$line =~ s/\{cajero}/$cajero/g;
		$line =~ s/\{dt_desde}/$dt_desde/g;
		$line =~ s/\{dt_hasta}/$dt_hasta/g;
		print "$line";
	}
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 2
sub get_recibo_tarifas {
    my ($id)= @_; 
    my $count = 1;
    my %hash  = ();
    my $sql   = "SELECT b.c16_tarifa_id,
						b.v128_tarifa,			    
						(b.`d82_cantidad`*b.`d82_tarifa`) as TOTAL,
						b.txt_periodos
		 FROM sdm_recibos a
		 INNER JOIN sdd_recibos b ON a.c16_recibo_id=b.c16_recibo_id
		 WHERE a.c16_recibo_id= '$id'
		 ORDER BY a.c16_recibo_id+0";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_recibo_tarifas');
    while (@row = $sth->fetchrow_array) {
		$hash{$row[0]."_".$count}   = [@row];
		$count++;
    }
	#foreach my $k (sort keys %hash){print "$k <br>";}
    return \%hash;
}
sub get_tarifas_cuentas {
    my (%recs)= @_;
	#my %recs = %$tarifas_ref;
    my %hash  = ();
    foreach my $k(sort keys %recs){
		my @tid = split(/\_/,$k);
		my $sql = "SELECT c16_cuenta_recurso_id,
						  c16_cuenta_obligacion_id,
						  c16_cuenta_recurso_pasado_id,
						  c16_cuenta_obligacion_pasado_id,
						  c16_cuenta_recurso_futuro_id,
						  c16_cuenta_obligacion_futuro_id,
						  c16_cuenta_ingreso_id
			   FROM sdm_tarifas
			   WHERE c16_tarifa_id='$tid[0]'";
		#print "$sql <br>";
		my $sth = $dbh->prepare($sql);
		$sth->execute or die SQL_fail($sql,$dbh,'sub get_tarifas_cuentas');
		while (@row = $sth->fetchrow_array) {
			$hash{$tid[0]}= join('|',@row);;
		}		
    }
	#foreach my $k (sort keys %hash){print "$k  > $hash{$k} <br>";}	
    return \%hash;
}
sub get_debe_haber{
    my ($t_ref,$c_ref)= @_;
	my %recibos1   = %$t_ref;
	my %cuentas    = %$c_ref;	
    my %debe_haber = ();
    foreach my $k(sort keys %recibos1){
		my @tid = split(/\_/,$k);			
		my @tcc = split(/\|/,$cuentas{$tid[0]});
		#print "$k $tid[0] $tcc[6] <br>";		
		$recibos1{$k}[3] =~ s/\ //g;
		if($recibos1{$k}[3] ne ""){
			my @periodos = split(/\~/,$recibos1{$k}[3]);
			for (my $i=0;$i<=$#periodos;$i++){
				my @periodo_data  = split(/\,/,$periodos[$i]);
				my $ano_periodo   = substr($periodo_data[0],0,4); 
				my $ano_actual    = substr($periodo_actual,0,4);
				if($ano_periodo == $ano_actual){
					$debe_haber{'recurso'}{$tcc[6]} += $periodo_data[3];
				}
				if($ano_periodo < $ano_actual){
					$debe_haber{'recurso_pasado'}{$tcc[6]} += $periodo_data[3];
				}
				if($ano_periodo > $ano_actual){
					$debe_haber{'recurso_futuro'}{$tcc[6]} += $periodo_data[3];
				}		
			}
		}else{
			$debe_haber{'recurso'}{$tcc[6]} += $recibos1{$k}[2];
		}
    }
	#foreach my $k (sort keys %debe_haber){
	#	foreach my $kk (sort keys %{$debe_haber{$k}}){
	#		print "$k >$kk > $debe_haber{$k}{$kk} <br>";
	#	}
	#}		
    return \%debe_haber;
}
#AUXILIAR-SUBROUTINES
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Error: ".$dbh->Error()."\n<br>";
    print "Query: ".$query."<br>";    
    $dbh->Close();
    exit;
}
sub connect_fail {
    my ($DSN)=@_;
    print "Error connecting to $DSN<br>";
    print "Error:".Win32::ODBC::Error()."<br>";
    exit;
}
sub convertir_tildes{
	my ($str) = @_;
	$str =~ s/\Ã±/ñ/g;
	$str =~ s/\Ã/Ñ/g;
	$str =~ s/\Ã‘/Ñ/g;      
	$str =~ s/\Ã¡/á/g;
	$str =~ s/\Ã©/é/g;
	$str =~ s/\Ã­/í/g;
	$str =~ s/\Ã³/ó/g;
	$str =~ s/\Ãº/ú/g;
	return $str;
}
sub get_periodo_actual {
	my $sql= "SELECT DATE_FORMAT(CURRENT_DATE,'%Y%m')";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_periodo_actual');
    if (@row = $sth->fetchrow_array) {
		return ($row[0])
	}else{
		return (0)
	}
}
#-------------------------------------------------------------------------