#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2012-11-24
# Updates: 2012-11-24
# Author:Rodolfo Machon
# File Name: ingreso_anual_cuentas_ingresos_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');
$i4_anio    = $cgiparms->param('i4_anio');
$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."_ingreso_anual_cuentas_ingresos_p.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# MAIN-PROGRAM
# PROCURMENT
%recibos 	 = get_recibos();
%tarifas_cuentas = get_tarifas_cuentas();
# INTEGRATION
%cuentas 	 = get_cuentas();
# PRESENTATION
$table	 	 = construct_table();
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_recibos {
     my %hash  = ();
    if ($i2_cajero_id ne "0") {$filtro .= " and i2_cajero_id='$i2_cajero_id'"}    
    my $query = "SELECT b.c16_tarifa_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 substring(dt_emision,1,4)='$i4_anio' $filtro
		 HAVING TOTAL>0
		 ORDER BY a.c16_recibo_id
		";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_recibos');}
    while($dbh->FetchRow()){
        undef my @row;
        @row 		 	  = $dbh->Data();
	my @fecha 		  = split('-',$row[1]);
	$hash{$row[0]}{$fecha[0]."-".$fecha[1]} += $row[2];
    }
    return %hash;
}
sub get_tarifas_cuentas {
    my %hash   = ();
    my %tars_h = ();    
    my $tars_s = "";
    my $tars   = "'0',";
    foreach my $k (sort numeros keys %recibos){
	foreach my $kk (sort keys %{$recibos{$k}}){	
	    $tars_h{$k} = $k;
	}
    }
    foreach my $k (sort numeros keys %tars_h){$tars .= "'".$k."',";}
    $tars = substr($tars,0,length($tars)-1);
    my $query = "SELECT a.c16_tarifa_id,
			b.v128_cuenta_contable,
			b.c16_cuenta_ingreso_id
		 FROM sdm_tarifas a
		 INNER JOIN sdm_cuentas_contables b ON a.c16_cuenta_obligacion_id=b.c16_cuenta_contable_id
		 WHERE a.c16_tarifa_id in ($tars)
		";
    #print "$query";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub tarifas_cuentas');}
    while($dbh->FetchRow()){
        undef my @row;
        @row 	       = $dbh->Data();
	$hash{$row[0]} = [@row];	
    }
    return %hash;
}
sub get_cuentas {
    my %hash  = ();
    foreach my $k (sort keys %recibos){
	foreach my $kk (sort keys %{$recibos{$k}}){
	    $hash{$tarifas_cuentas{$k}[2]}{$kk} += $recibos{$k}{$kk};
	}
    }
    return %hash;
}
sub construct_table {
    my $count   = 1;
    my $total   = 0;
    my $table   = "";
    my %tar_cue = ();    
    my %thead_h = ();
    my %tfoot_h = ();    
    my $thead_s = "";
    my $tfoot_s = "";    
    my $rowset  = "";
    my $thead   = "<thead><tr><td>No.</td><td>DESCRIPCION</td><td>CUENTA</td><td>TOTAL</td>{thead_s}</tr></thead>";
    my $tfoot   = "<tfoot><tr><td>&nbsp;</td><td>&nbsp;</td><td>TOTALES</td><td>{total}</td>{tfoot_s}</tr></tfoot>";    
    foreach my $k (sort keys %recibos){
	foreach my $kk (sort keys %{$recibos{$k}}){
	    $thead_h{$kk} = $kk;
	}
    }
    foreach my $k (sort keys %tarifas_cuentas){
	$tar_cue{$tarifas_cuentas{$k}[2]} = $tarifas_cuentas{$k}[1];
    }    
    foreach my $k (sort numeros keys %cuentas){
	foreach my $kk (sort keys %{$cuentas{$k}}){
	    $total_cuentas{$k} += $cuentas{$k}{$kk};
	}
    }    
    foreach my $k (sort keys %thead_h){$thead_s .= "<td>".$k."</td>";}    
    foreach my $k (sort numeros keys %cuentas){
	my $row = "<td>$count</td><td>".$tar_cue{$k}."</td><td>".$k."</td>";
	my $tot = sprintf("%.2f",$total_cuentas{$k});
	$row   .= "<td>".$tot."</td>";
	foreach my $kk (sort keys %thead_h){
	    my $ing = "";
	    if($cuentas{$k}{$kk}){$ing = $cuentas{$k}{$kk};$ing = sprintf("%.2f",$ing);}else{$ing ="&nbsp;"}
	    $row .= "<td>".$ing."</td>";
	    $tfoot_h{$kk} += $cuentas{$k}{$kk};
	}
	$rowset .= "<tr>".$row."</tr>";
	$total += $total_cuentas{$k};	
	$count++;	
    }
    foreach my $k (sort keys %tfoot_h){$tot = sprintf("%.2f",$tfoot_h{$k});$tfoot_s .= "<td>".$tot."</td>";}
    $total  = sprintf("%.2f",$total);
    $thead  =~ s/\{thead_s}/$thead_s/g;
    $tfoot  =~ s/\{total}/$total/g;
    $tfoot  =~ s/\{tfoot_s}/$tfoot_s/g;
    $rowset = "<tbody>".$rowset."</tbody>";
    $table  = "<table id=grid>".$thead.$rowset.$tfoot."</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;    
        print "$line";
    }
}
#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub numeros {$a<=>$b}
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;
      return $str;
}
#-------------------------------------------------------------------------