#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2012-11-25
# Updates: 2012-11-25
# Author:Rodolfo Machon
# File Name: ingresos_cuentas_contables_p.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use Win32::ODBC;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html\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_contables_p.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# 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,
			    (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
		    WHERE a.dt_emision >= '$dt_desde' and a.dt_emision <= '$dt_hasta' $cajero
		    ORDER BY a.c16_recibo_id+0
			";
	#print "$sql <>";
	if ( $dbh->Sql($sql) ){SQL_fail($sql,$dbh,'sub get_recibos')};
	while($dbh->FetchRow()){
	    my @row = $dbh->Data();
	    $hash{$row[0]}   = [@row];
	    $hash1{$row[0]} += $row[2];
	}
	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};
		#print "$k <br>";
		$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 $sql  = "SELECT a.c16_cuenta_recurso_id,a.v128_tarifa FROM sdm_tarifas a	";
	if ( $dbh->Sql($sql) ){SQL_fail($sql,$dbh,'sub get_recibos')};
	while($dbh->FetchRow()){
	    my @row = $dbh->Data();
	    $hash{$row[0]} = $row[1];
	}
	return %hash;
}
sub construct_table {
    my $table   = "";
	my $rowset  = "";
    my %rec_cta = ();
    my %rec_epo = ();	
	my $t_pr 	= 0;
	my $t_po 	= 0;
	my $t_ar 	= 0;
	my $t_ao 	= 0;
	my $t_fr 	= 0;
	my $t_fo 	= 0;	
    my $total   = 0;
	
    my $thead   = "<thead>
						<tr><TD colspan=3></TD><TD colspan=4 align=center>PASADO</TD><TD colspan=4 align=center>PRESENTE</TD><TD colspan=4 align=center>FUTURO</TD></tr>
						<tr><TD>CUENTA</TD><TD>RUBRO</TD><TD>SUB-TOTAL</TD><TD>RECURSO</TD><TD>MONTO</TD><TD>OBLIGACION</TD><TD>MONTO</TD><TD>RECURSO</TD><TD>MONTO</TD><TD>OBLIGACION</TD><TD>MONTO</TD><TD>RECURSO</TD><TD>MONTO</TD><TD>OBLIGACION</TD><TD>MONTO</TD></tr>
					</thead>";
    my $tfoot   = "<tfoot><tr><td>&nbsp;</td><td>TOTAL</td><td>{t_total}</td><td></td><td>{t_pr}</td><td></td><td>{t_po}</td><td></td><td>{t_ar}</td><td></td><td>{t_ao}</td><td></td><td>{t_fr}</td><td></td><td>{t_fo}</td></tr></t_futuro>";
    foreach my $k (sort keys %recibos_cuentas){
		foreach my $kk (sort keys %{$recibos_cuentas{$k}}){
			foreach my $kkk (sort keys %{$recibos_cuentas{$k}{$kk}}){
				foreach my $kkkk (sort keys %{$recibos_cuentas{$k}{$kk}{$kkk}}){
					#print "$k > $kk > $kkk > $kkk > $recibos_cuentas{$k}{$kk}{$kkk}{$kkkk}  <br>";				
					#$rec_cta{$kk}{$kkk."_".$kkkk} += $recibos_cuentas{$k}{$kk}{$kkk}{$kkkk};
					$rec_cta{$kk}{$kkkk}{$kkk} += $recibos_cuentas{$k}{$kk}{$kkk}{$kkkk};					
				}
			}
		}
	}
	foreach my $k (sort keys %rec_cta){
		foreach my $kk (sort keys %{$rec_cta{$k}}){
			my $subtotal = 0;
			foreach my $kk (sort keys %{$rec_cta{$k}}){
			my $row = "";
				if ($rec_cta{$k}{$kk}{'recurso-pasado'}){$row .= "<td>".$kk."</td>"."<td>".$rec_cta{$k}{$kk}{'recurso-pasado'}."</td>";$subtotal+=$rec_cta{$k}{$kk}{'recurso-pasado'};$t_pr+=$rec_cta{$k}{$kk}{'recurso-pasado'}}else{$row .= "<td></td><td></td>";}
				if ($rec_epo{$k}{$kk}{'obligacion-pasado'}){$row .= "<td>".$kk."</td>"."<td>".$rec_epo{$k}{$kk}{'obligacion-pasado'}."</td>";$t_po+=$rec_epo{$k}{$kk}{'obligacion-pasado'}}else{$row .= "<td></td><td></td>";}
				if ($rec_epo{$k}{$kk}{'recurso'}){$row .= "<td>".$kk."</td>"."<td>".$rec_epo{$k}{$kk}{'recurso'}."</td>";$subtotal+=$rec_epo{$k}{$kk}{'recurso'};$t_ar+=$rec_epo{$k}{$kk}{'recurso'}}else{$row .= "<td></td><td></td>";}
				if ($rec_epo{$k}{$kk}{'obligacion'}){$row .= "<td>".$kk."</td>"."<td>".$rec_epo{$k}{$kk}{'obligacion'}."</td>";$t_ao+=$rec_epo{$k}{$kk}{'obligacion'}}else{$row .= "<td></td><td></td>";}
				if ($rec_epo{$k}{$kk}{'recurso-futuro'}){$row .= "<td>".$kk."</td>"."<td>".$rec_epo{$k}{$kk}{'recurso-futuro'}."</td>";$subtotal+=$rec_epo{$k}{$kk}{'recurso-futuro'};$t_fr+=$rec_epo{$k}{$kk}{'recurso-futuro'}}else{$row .= "<td></td><td></td>";}
				if ($rec_epo{$k}{$kk}{'obligacion-futuro'}){$row .= "<td>".$kk."</td>"."<td>".$rec_epo{$k}{$kk}{'obligacion-futuro'}."</td>";$t_fo+=$rec_epo{$k}{$kk}{'obligacion-futuro'}}else{$row .= "<td></td><td></td>";}
			}
			$rowset  .= "<tr><td>".$k."</td>"."<td>".$rubros{$kk}."</td>"."<td>".sprintf("%.2f",$subtotal)."</td>".$row."</tr>";
			$total += $subtotal;
		}
	}	
    #foreach my $k (sort keys %rec_cta){
	#	foreach my $kk (sort keys %{$rec_cta{$k}}){
	#		my @tc_a  = split(/\_/,$kk);
	#		#print "$k > $tc_a[0] > $tc_a[1] > $rec_cta{$k}{$kk}  <br>";
			#$rec_epo{$k}{$tc_a[1]} = [$tc_a[0],$rec_cta{$k}{$kk}];	
	#	}
	#}	
    #foreach my $k (sort keys %rec_epo){
	#	foreach my $kk (sort keys %{$rec_epo{$k}}){
	#		my $row = "";
	#		my $subtotal = 0;
	#		if ($rec_epo{$k}{'recurso-pasado'}){$row .= "<td>".$rec_epo{$k}{'recurso-pasado'}[0]."</td>"."<td>".$rec_epo{$k}{'recurso-pasado'}[1]."</td>";$subtotal+=$rec_epo{$k}{'recurso-pasado'}[1];$t_pr+=$rec_epo{$k}{'recurso-pasado'}[1]}else{$row .= "<td></td><td></td>";}
	#		if ($rec_epo{$k}{'obligacion-pasado'}){$row .= "<td>".$kk."</td>"."<td>".$rec_epo{$k}{'obligacion-pasado'}[1]."</td>";$t_po+=$rec_epo{$k}{'obligacion-pasado'}[1]}else{$row .= "<td></td><td></td>";}
	#		if ($rec_epo{$k}{'recurso'}){$row .= "<td>".$rec_epo{$k}{'recurso'}[0]."</td>"."<td>".$rec_epo{$k}{'recurso'}[1]."</td>";$subtotal+=$rec_epo{$k}{'recurso'}[1];$t_ar+=$rec_epo{$k}{'recurso'}[1]}else{$row .= "<td></td><td></td>";}
	#		if ($rec_epo{$k}{'obligacion'}){$row .= "<td>".$rec_epo{$k}{'obligacion'}[0]."</td>"."<td>".$rec_epo{$k}{'obligacion'}[1]."</td>";$t_ao+=$rec_epo{$k}{'obligacion'}[1]}else{$row .= "<td></td><td></td>";}
	#		if ($rec_epo{$k}{'recurso-futuro'}){$row .= "<td>".$rec_epo{$k}{'recurso-futuro'}[0]."</td>"."<td>".$rec_epo{$k}{'recurso-futuro'}[1]."</td>";$subtotal+=$rec_epo{$k}{'recurso-futuro'}[1];$t_fr+=$rec_epo{$k}{'recurso-futuro'}[1]}else{$row .= "<td></td><td></td>";}
	#		if ($rec_epo{$k}{'obligacion-futuro'}){$row .= "<td>".$rec_epo{$k}{'obligacion-futuro'}[0]."</td>"."<td>".$rec_epo{$k}{'obligacion-futuro'}[1]."</td>";$t_fo+=$rec_epo{$k}{'obligacion-futuro'}[1]}else{$row .= "<td></td><td></td>";}
	#		$rowset  .= "<tr><td>".$k."</td>"."<td>".$rubros{$k}."</td>"."<td>".sprintf("%.2f",$subtotal)."</td>".$row."</tr>";
	#		$total += $subtotal;
	#	}
	#}
	$total  = sprintf("%.2f",$total);	
    $tfoot  =~ s/\{t_total}/$total/g;
    $tfoot  =~ s/\{t_pr}/$t_pr/g;
    $tfoot  =~ s/\{t_po}/$t_po/g;
    $tfoot  =~ s/\{t_ar}/$t_ar/g;
    $tfoot  =~ s/\{t_ao}/$t_ao/g;
    $tfoot  =~ s/\{t_fr}/$t_fr/g;
    $tfoot  =~ s/\{t_fo}/$t_fo/g;
			 
	$table  = "<table id=grid>".$thead.$rowset.$tfoot."</table>";
    return $table;
}
sub construct_page {
	open (DATA, "$PATH$file");
	while ($line = <DATA>) {	
		$line =~ s/\{lan}/$lan/g;
		$line =~ s/\{appid}/$appid/g;
		$line =~ s/\{table}/$table/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 a.c16_tarifa_id,
						a.v128_tarifa,			    
						(a.`d82_cantidad`*a.`d82_tarifa`) as TOTAL,
						a.txt_periodos
				FROM sdd_recibos a
				WHERE a.c16_recibo_id= '$id'
				";
    if ( $dbh->Sql($sql) ){SQL_fail($sql,$dbh,'sub get_recibos_tarifas')};
    while($dbh->FetchRow()){
		my @row = $dbh->Data();
		$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>";
		if ($dbh->Sql($sql) ){SQL_fail($sql,$dbh,'sub get_tarifa_cuentas')};
		while ($dbh->FetchRow()){
			my @row = $dbh->Data();
			$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{$tcc[6]}{'recurso'}{$tcc[0]} += $periodo_data[3];
					$debe_haber{$tcc[6]}{'obligacion'}{$tcc[1]} += $periodo_data[3];					
				}
				if($ano_periodo < $ano_actual){
					$debe_haber{$tcc[6]}{'recurso-pasado'}{$tcc[2]} += $periodo_data[3];
					$debe_haber{$tcc[6]}{'obligacion-pasado'}{$tcc[3]} += $periodo_data[3];					
				}
				if($ano_periodo > $ano_actual){
					$debe_haber{$tcc[6]}{'recurso-futuro'}{$tcc[4]} += $periodo_data[3];
					$debe_haber{$tcc[6]}{'obligacion-futuro'}{$tcc[5]} += $periodo_data[3];					
				}		
			}
		}else{
			$debe_haber{$tcc[6]}{'recurso'}{$tcc[0]} += $recibos1{$k}[2];
			$debe_haber{$tcc[6]}{'obligacion'}{$tcc[1]} += $recibos1{$k}[2];
		}
    }
	#foreach my $k (sort keys %debe_haber){
	#	foreach my $kk (sort keys %{$debe_haber{$k}}){
	#		foreach my $kkk (sort keys %{$debe_haber{$k}{$kk}}){
	#			print "$k >$kk > $kkk > $debe_haber{$k}{$kk}{$kkk} <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 $query= "SELECT DATE_FORMAT(CURRENT_DATE,'%Y%m')";
      if ($dbh->Sql($query)){SQL_fail($dbh,$query,'sub get_periodo_actual')};
      if ($dbh->FetchRow()){my @row=$dbh->Data();return ($row[0])}else{return (0)}
}
#-------------------------------------------------------------------------