#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# WebSoft SA
# Date In: 2013-05-25
# Updates: 2013-05-25, 2016-03-30
# Author:Rodolfo Machon
# File Name: empresas_sectores_avisos_cobros_rept.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use Win32::ODBC;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$appid= 'RTM_v30';
$cgiparms   = new CGI;
$lan	    = $cgiparms->param('lan');
#$appid	    = $cgiparms->param('appid');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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."_empresas_sectores_avisos_cobros_rept.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
$dt_vencimiento			= get_vencimiento();
%sectores				= get_sectores();
%sector_expedientes		= get_sector_expedientes();
%expedientes_imagenes	= get_expedientes_imagenes();
# INTEGRATION
%sectores_expedientes_imagenes   = get_sectores_expedientes_imagenes();
# PRESENTATION
$table 		            = construct_table();
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_vencimiento {
    my $str  = "";
    my $query = "SELECT a.dt_generado
				 FROM sdd_empresa_estados_mensuales a
				 GROUP BY a.dt_generado
				 ";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_vencimiento')};
		while($dbh->FetchRow()){
		my @row = $dbh->Data();
		$str = $row[0];
    }
    return $str;	
}
sub get_sectores {
    my %hash  = ();
    my $query = "SELECT a.c11_sector_id,
			a.v64_sector,
			b.v64_tipo_sector
		 FROM sdm_sectores a
		 LEFT JOIN sdm_tipo_sectores b	ON a.i2_tipo_sector_id = b.i2_tipo_sector_id
		 ORDER BY a.c11_sector_id";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_sectores')};
		while($dbh->FetchRow()){
		my @row = $dbh->Data();
		$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_sector_expedientes {
    my %hash  = ();
    my $query = "SELECT b.c2_zona_id,b.c11_sector_id,a.i8_empresa_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'
				";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_sector_expedientes')};
    while($dbh->FetchRow()){
	my @row = $dbh->Data();
		$hash{$row[0]}{$row[1]}{$row[2]} = $row[2];
    }
    return %hash;
}
sub get_expedientes_imagenes {
	my %hash    	= ();
	my $files_path 	= "../fichas_cuentas_empresas/";
	opendir (DIR,$files_path) or die "$!";
	@files = readdir DIR;	
	close DIR;
	foreach $fi (@files){
		my $fina =substr($fi,0,7);		
		$hash{$fina} += 1;
	}
	return %hash;
}
sub get_sectores_expedientes_imagenes {
    my %hash  = ();
    foreach my $k (sort keys %sector_expedientes){
		foreach my $kk (sort keys %{$sector_expedientes{$k}}){
			my $count_exp = 0;
			my $count_img = 0;
			my $count_tim = 0;
			foreach my $kkk (sort keys %{$sector_expedientes{$k}{$kk}}){
				if ($expedientes_imagenes{$kkk}) {$count_img++;$count_tim +=$expedientes_imagenes{$kkk}}
				$count_exp++;
			}
			$hash{$k}{$kk}{'exp'} = $count_exp;
			$hash{$k}{$kk}{'img'} = $count_img;
			$hash{$k}{$kk}{'tim'} = $count_tim;
		}
    }
    return %hash;	
}
sub construct_table {
    my $count   = 1;
	my $expe	= ();
    my $table   = "";
    my $rowset  = "";
    my $thead   = "<thead><tr><td>No.</td><td>SECTOR ID</td><td>ZON</td><td>TIPO SECTOR</td><td>SECTOR</td><td>EXPEDIENTES</td></tr></thead>";
    my $tfoot   = "<tfoot><tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>TOTAL</td><td>{t_exp}</td></tr></tfoot>";
	foreach my $k (sort keys %sectores_expedientes_imagenes){
		foreach my $kk (sort keys %{$sectores_expedientes_imagenes{$k}}){
			my $row   = "<td><a href=\"javascript:OpenLink('$kk','$sectores{$kk}[2] $sectores{$kk}[1]')\">$count</a></td>
						 <td>".$kk."</td>
						 <td>".$k."</td>
						 <td>".$sectores{$kk}[2]."</td>
						 <td><a ('$kk','$sectores{$kk}[2] $sectores{$kk}[1]')\">".$sectores{$kk}[1]."</a></td>
						 <td>".$sectores_expedientes_imagenes{$k}{$kk}{'exp'}."</td>
						 ";					 
			$rowset  .= "<tr>".$row."</tr>";
			$t_exp += $sectores_expedientes_imagenes{$k}{$kk}{'exp'};
			$t_img += $sectores_expedientes_imagenes{$k}{$kk}{'img'};
			$t_tim += $sectores_expedientes_imagenes{$k}{$kk}{'tim'};		
			$count++;
		}	
	}
    $tfoot     =~ s/\{t_exp}/$t_exp/g;
    $tfoot     =~ s/\{t_img}/$t_img/g;
    $tfoot     =~ s/\{t_tim}/$t_tim/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/\{table}/$table/g;
        $line =~ s/\{dt_vencimiento}/$dt_vencimiento/g;			   
        print "$line";
    }
}
#-------------------------------------------------------------------------
#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;
      return $str;
}
#-------------------------------------------------------------------------