#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# WebSoft SA
# Date In: 2013-02-20
# Updates: 2013-02-20, 2016-03-30
# Author:Rodolfo Machon
# File Name: mora_sector_ubicaciones_notificador_p.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use CGI::Carp qw/fatalsToBrowser warningsToBrowser/;
use DBI;
use XML::Simple;
use Data::Dumper;
binmode STDOUT, ":encoding(utf8)";
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html; charset=utf-8\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$cgiparms      = new CGI;
$clase 	       = $cgiparms->param('clase');
$c11_sector_id = $cgiparms->param('c11_sector_id');
$v64_sector    = $cgiparms->param('v64_sector');
#-------------------------------------------------------------------------
$clase="inmueble";
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($clase eq ""){print "Your are not passing CLASE parameter<br>";}
if ($c11_sector_id eq ""){print "Your are not passing SECTOR parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# OPEN DATABASE CONNECTIONS - ODBCs
get_app_data();
open_database_connections();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$file       = $lan."_inmuebles_sector_avisos_cobros_rept.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES

#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
%expedientes_imagenes	= get_expedientes_imagenes();
$dt_vencimiento			= get_vencimiento();
%calles    				= get_ubicaciones('c4_calle_id','v64_calle','calles');
%avenidas				= get_ubicaciones('c4_avenida_id','v64_avenida','avenidas');
%pasajes				= get_ubicaciones('c2_pasaje_id','v64_pasaje','pasajes');
%sendas					= get_ubicaciones('c2_senda_id','v64_senda','sendas');
%poligonos 				= get_ubicaciones('c2_poligono_id','v64_poligono','poligonos');
%blocks 				= get_ubicaciones('c2_block_id','v64_block','blocks');
#%barrios   	= get_;
# INTEGRATION
# PRESENTATION
if (keys %calles){$table_calles  = construct_table('calles',{%calles});$tables .= $table_calles."<br>"}
if (keys %avenidas){$table_avenidas = construct_table('avenidas',{%avenidas});$tables .= $table_avenidas."<br>"}
if (keys %pasajes){$table_pasajes = construct_table('pasajes',{%pasajes});$tables .= $table_pasajes."<br>"}
if (keys %sendas){$table_sendas   = construct_table('sendas',{%sendas});$tables .= $table_sendas."<br>"}
if (keys %poligonos){$table_poligonos  = construct_table('poligonos',{%poligonos});$tables .= $table_poligonos."<br>"}
if (keys %blocks){$table_blocks  = construct_table('blocks',{%blocks});$tables .= $table_blocks."<br>"}

construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_vencimiento {
    my $str  = "";
    my $sql = "SELECT a.dt_generado
				 FROM sdd_inmueble_estados_mensuales a
				 GROUP BY a.dt_generado
				 ";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_vencimiento');
	while (my @row = $sth->fetchrow_array) {
		$str = $row[0];
    }
    return $str;	
}
sub get_expedientes_imagenes {
	my %hash    	= ();
	my $files_path 	= "../fichas_cuentas_inmuebles/";
	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_ubicaciones {
	my ($id,$desc,$sec)=@_;
    my %hash  = ();
    my $tabla = "";
    if($clase eq "inmueble"){$tabla = "sdm_inmuebles"}
    if($clase eq "empresa"){$tabla = "sdd_empresa_ubicacion"}
    my $sql = "SELECT a.".$id.",
						b.".$desc.",
						count(a.i8_".$clase."_id)
				 FROM ".$tabla." a
				 LEFT JOIN sdd_sector_".$sec." b ON a.c11_sector_id=b.c11_sector_id and a.".$id."=b.".$id."
				 WHERE a.c11_sector_id='$c11_sector_id' and a.".$id."!='' and a.".$id."!='00' and a.".$id."!='0000' and a.c16_estado='ACEPTADO'
				 GROUP BY a.".$id;
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_ubicaciones');
	while (my @row = $sth->fetchrow_array) {
		my ($img,$tim) = get_ubicacion_imagenes($id,$row[0]);
		push(@row, ($img,$tim)); 
		$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_ubicacion_imagenes {
	my ($id,$id_value)=@_;
	my $count_img = 0;
	my $count_tim = 0;
    my $sql = "SELECT a.c11_sector_id,a.".$id.",a.i8_".$clase."_id
				 FROM sdm_inmuebles a
				 WHERE a.c16_estado='ACEPTADO' and a.c11_sector_id='$c11_sector_id' and a.".$id."='".$id_value."'
				";
	my $sth1 = $dbh1->prepare($sql);
	$sth1->execute or die SQL_fail($sql,$dbh1,'sub get_ubicacion_imagenes');
	while (my @row = $sth1->fetchrow_array) {
		if ($expedientes_imagenes{$row[2]}) {$count_img++;$count_tim +=$expedientes_imagenes{$row[2]}}		
    }
	return $count_img,$count_tim;
}
sub construct_table {
    my ($ubi,$h)	= @_;
    my %hash 		= %$h;
    my $count   	= 1;
    my $t_activos	= 0;
    my $t_con_img	= 0;
    my $t_imagenes	= 0;	
    my $table       = "";
    my $descripcion = uc($ubi);	
    my $rowset  	= "";
    my $thead   	= "<thead><tr><td width=5%>No.</td><td width=10%>ID</td><td width=60%>{descripcion}</td><td width=10%>ACTIVOS</td></tr></thead>";
    my $tfoot   	= "<tfoot><tr><td>&nbsp;</td><td>&nbsp;</td><td>TOTAL</td><td>{t_activos}</td></tr></tfoot>";    
    foreach my $k (sort numeros keys %hash){
		my $row   = "<td><a href=\"javascript:OpenLink('$c11_sector_id','$k','$ubi','$clase')\">$count</a></td>
						<td>".$hash{$k}[0]."</td>
						<td>".$hash{$k}[1]."</td>
						<td>".$hash{$k}[2]."</td>
					";
		$rowset    .= "<tr>".$row."</tr>";
		$t_activos += $hash{$k}[2];
		$t_con_img += $hash{$k}[3];
		$t_imagenes+= $hash{$k}[4];		
		$count++;	
    }
	if($count>0){
		$tfoot     =~ s/\{t_activos}/$t_activos/g;
		$tfoot     =~ s/\{t_con_img}/$t_con_img/g;
		$tfoot     =~ s/\{t_imagenes}/$t_imagenes/g;
		$thead     =~ s/\{descripcion}/$descripcion/g;
		$rowset    = "<tbody>".$rowset."</tbody>";
		$table     = "<table id=grid class=grilla>".$thead.$rowset.$tfoot."</table>";
		$table     = convertir_tildes($table);
	}
    return $table;
	
}
sub construct_page {
    open (DATA, "../$lan/$file");
    while ($line = <DATA>) {	
        $line =~ s/\{lan}/$lan/g;
        $line =~ s/\{appid}/$appid/g;
        $line =~ s/\{clase}/$clase/g;         
        $line =~ s/\{table}/$tables/g;
        $line =~ s/\{c11_sector_id}/$c11_sector_id/g;
        $line =~ s/\{v64_sector}/$v64_sector/g;
        $line =~ s/\{ubi}//g;
        $line =~ s/\{ubi_tipo}//g;
        $line =~ s/\{dt_vencimiento}/$dt_vencimiento/g;
        print "$line";
    }
}
#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub numeros {$a<=>$b}
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<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;
}
sub get_app_data {
    my $schema   = "app.data.xml";
    my $xml      = new XML::Simple;
    my $data 	  = $xml->XMLin("../xml/".$schema);
    $appid	  = $data->{portal}->{app};
    $lan	  = $data->{portal}->{lang};
	$city	  = $data->{portal}->{city};
	$state    = $data->{portal}->{state};
}
sub open_database_connections {
    $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->{LongTruncOk} = 1;
    $dbh1 	  = DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
    if (!$dbh1) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
    $dbh1->{LongTruncOk} = 1;	
}
#-------------------------------------------------------------------------