#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2012-11-20
# Updates: 2012-11-20
# Author:Rodolfo Machon
# File Name: ingreso_mensual_empresas_p.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use Win32::ODBC;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$cgiparms   = new CGI;
$appid	    = $cgiparms->param('appid');
$lan	    = $cgiparms->param('lan');
$clase 	    = $cgiparms->param('clase');
$i8_periodo = $cgiparms->param('i8_periodo');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($lan eq ""){print "Your are not passing LAN parameter<br>";}
if ($appid eq ""){print "Your are not passing APPID parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH 		= "../".$lan."/";
$file       	= $lan."_ingreso_mensual_empresas_p.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
%empresas  	  = get_empresas();
#%ubicaciones 	  = get_ubicacion();
%actividades 	  = get_actividades();
%clasificaciones  = get_clasificaciones();
%mensualidades    = get_mensualidades();
# INTEGRATION
#
# PRESENTATION
$table 		  = construct_table();
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_empresas {
    my %hash  = ();
    my $query = "SELECT a.i8_empresa_id,
			a.v128_empresa,
			a.v128_denominacion_comercial,
			b.c11_sector_id,
			b.c16_codigo_catastral_id
		 FROM sdm_empresas a
		      LEFT JOIN sdd_empresa_ubicacion b ON a.i8_empresa_id =  b.i8_empresa_id
		 WHERE a.c16_estado = 'ACEPTADO'
		 ORDER BY a.i8_empresa_id";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_empresas')};
    while($dbh->FetchRow()){
	my @row = $dbh->Data();
	$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_actividades {
    my %hash  = ();
    my $query = "SELECT a.i8_empresa_id,
			a.i2_actividad_general_id,
			b.v64_name,			
			a.i4_actividad_especifica_id,
			c.v64_especifica_name,
			a.i6_actividad_subespecifica_id,
			d.v64_subespecifica_name
		 FROM sdd_empresa_actividades a
		      LEFT JOIN sdm_actividad_generales b ON a.i2_actividad_general_id =  b.i2_actividad_general_id
		      LEFT JOIN sdm_actividad_especificas c ON a.i4_actividad_especifica_id =  c.i4_actividad_especifica_id
		      LEFT JOIN sdm_actividad_subespecificas d ON a.i6_actividad_subespecifica_id =  d.i6_actividad_subespecifica_id
		 ORDER BY a.i8_empresa_id";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_actividades')};
    while($dbh->FetchRow()){
	my @row = $dbh->Data();
	$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_clasificaciones {
    my %hash  = ();
    my $query = "SELECT a.i8_empresa_id,
			a.c2_clasificacion_id,
			b.v64_tipo_empresa
		 FROM sdd_empresa_clasificaciones a
		      LEFT JOIN sdm_tipo_empresas b ON a.c2_clasificacion_id=b.c2_tipo_empresa_id
		 ORDER BY a.i8_empresa_id";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_clasificaciones')};
    while($dbh->FetchRow()){
	my @row = $dbh->Data();
	$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_mensualidades {
    my %hash  = ();
    my $query = "SELECT a.i8_empresa_id,
			a.c16_tarifa_id,
			b.v128_tarifa,
			a.d82_med,
			a.d82_val,
			a.d82_fie,
			(a.d82_val+a.d82_fie) as 'TOTAL'
		 FROM sdd_empresa_mensualidades a
		 LEFT JOIN sdm_tarifas b ON a.c16_tarifa_id=b.c16_tarifa_id
		 WHERE a.i8_periodo='$i8_periodo'
		 ORDER BY a.i8_empresa_id";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_mensualidades')};
    while($dbh->FetchRow()){
	my @row = $dbh->Data();
	$hash{$row[0]} = [@row];
    }
    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>ID</td><td>NOMBRE</td><td>DENOMINACION COMERCIAL</td><td>SECTOR</td><td>CATASTRAL</td><td>GENERAL</td><td>ESPECIFICA</td><td>SUB-ESPECIFICA</td><td>TIPO</td><td>TARIFA</td><td>ACTIVO</td><td>CARGO</td><td>FIESTA</td><td>TOTAL</td></tr></thead>";
    my $tfoot   = "<tfoot><tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>TOTALES</td><td>{total_a}</td><td>{total_v}</td><td>{total_f}</td><td>{total_t}</td></tr></tfoot>";    
    foreach my $k (sort numeros keys %empresas){
	my $row   = "<td>".$count."</td><td>".$k."</td><td>".$empresas{$k}[1]."</td><td>".$empresas{$k}[2]."</td><td>".$empresas{$k}[3]."</td><td>".$empresas{$k}[4]."</td>";
	$row     .= "<td>".$actividades{$k}[2]."</td><td>".$actividades{$k}[4]."</td><td>".$actividades{$k}[6]."</td>";
	$row     .= "<td>".$clasificaciones{$k}[2]."</td>";
	$row     .= "<td>".$mensualidades{$k}[2]."</td><td>".$mensualidades{$k}[3]."</td><td>".$mensualidades{$k}[4]."</td><td>".$mensualidades{$k}[5]."</td><td>".$mensualidades{$k}[6]."</td>";
	$rowset  .= "<tr>".$row."</tr>";
	$total_a += $mensualidades{$k}[3];
	$total_v += $mensualidades{$k}[4];
	$total_f += $mensualidades{$k}[5];
	$total_t += $mensualidades{$k}[6];
	$count++;	
    }
    $total_a = sprintf("%.2f",$total_a);
    $total_v = sprintf("%.2f",$total_v);
    $total_f = sprintf("%.2f",$total_f);
    $total_t = sprintf("%.2f",$total_t);    
    $tfoot   =~ s/\{total_a}/$total_a/g;
    $tfoot   =~ s/\{total_v}/$total_v/g;
    $tfoot   =~ s/\{total_f}/$total_f/g;
    $tfoot   =~ s/\{total_t}/$total_t/g;
    $rowset  = "<tbody>".$rowset."</tbody>";
    $table   = "<table id=grid>".$thead.$rowset.$tfoot."</table>";
    $table   = convertir_tildes($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/\{clase}/$clase/g;         
        $line =~ s/\{table}/$table/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;
}
#-------------------------------------------------------------------------