#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# WebSoft SA
# Date In: 2016-03-22
# Updates: 2016-03-22
# Author:Rodolfo Machon
# File Name: nuc_expedientes.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use CGI::Carp qw/fatalsToBrowser warningsToBrowser/;
use DBI;
use XML::Simple;
binmode STDOUT, ":encoding(utf8)";
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html; charset=utf-8\n\n";
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
#GET PARAMETERS
$form		= new CGI;
$clase		= $form->param('clase');
$i8_nuc_id	= $form->param('id');
$properties	= $form->param('properties');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
get_app_data();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($clase ne "") {
	my @params	= split(/\|/,$clase);
	$script		= $params[0];		
	$componente	= $params[1];
	$add_clase	= $params[2];	
}
#print "$script > $componente <br>";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH	= "../".$lan."/";
$file 	= $lan."_nuc_expedientes.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# OPEN DATABASE CONNECTIONS - ODBCs
$dbh 	  = DBI->connect("dbi:ODBC:$appid",'','',{PrintError => 1, RaiseError => 1});
if (!$dbh) {print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
%expedientes = get_expedientes();
%direcciones = get_direcciones();
# INTEGRATION
#
# PRESENTATION
$table 		   = construct_table();
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
sub get_expedientes {
    my %hash  = ();	
	my $sql	  = "SELECT  a.i8_expediente_id
						,a.c3_tipo_generador_id
						,b.v64_tipo_generador
				 FROM sdd_nuc_expedientes a
				 LEFT JOIN sdm_tipo_generadores b ON a.c3_tipo_generador_id=b.c3_tipo_generador_id
				 WHERE i8_nuc_id='$i8_nuc_id'
				 GROUP BY i8_expediente_id";
				 #print "$sql <br>";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_sectores');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_direcciones {
    my %hash  = ();
    foreach my $k (sort keys %expedientes){
		#print "$k > $expedientes{$k}[2] <br>";
		my $tabla = "";
		if(uc($expedientes{$k}[2]) eq "INMUEBLE"){$tabla = "sdm_inmuebles"}
		if(uc($expedientes{$k}[2]) eq "EMPRESA"){$tabla = "sdd_empresa_ubicacion"}
		my $sql  = "SELECT a.i8_".$expedientes{$k}[2]."_id,
						b.v64_barrio,
						c.v64_poligono,
						d.v64_block,
						e.v64_calle,
						f.v64_avenida,
						g.v64_pasaje,
						h.v64_senda,
						a.c4_casa
					 FROM ".$tabla." a
					 LEFT JOIN sdd_sector_barrios b ON a.c11_sector_id=b.c11_sector_id and a.c2_barrio_id=b.c2_barrio_id
					 LEFT JOIN sdd_sector_poligonos c ON a.c11_sector_id=c.c11_sector_id and a.c2_poligono_id=c.c2_poligono_id
					 LEFT JOIN sdd_sector_blocks d ON a.c11_sector_id=d.c11_sector_id and a.c2_block_id=d.c2_block_id
					 LEFT JOIN sdd_sector_calles e ON a.c11_sector_id=e.c11_sector_id and a.c4_calle_id=e.c4_calle_id
					 LEFT JOIN sdd_sector_avenidas f ON a.c11_sector_id=f.c11_sector_id and a.c4_avenida_id=f.c4_avenida_id
					 LEFT JOIN sdd_sector_pasajes g ON  a.c11_sector_id=g.c11_sector_id and a.c2_pasaje_id=g.c2_pasaje_id
					 LEFT JOIN sdd_sector_sendas h ON  a.c11_sector_id=h.c11_sector_id and a.c2_senda_id=h.c2_senda_id
					 WHERE a.i8_".$expedientes{$k}[2]."_id='$k'
					 ORDER BY a.i8_".$expedientes{$k}[2]."_id";
					 #print "$sql <br>";
		my $sth = $dbh->prepare($sql);
		$sth->execute or die SQL_fail($sql,$dbh,'sub get_sectores');
		while (my @row = $sth->fetchrow_array) {
			$hash{$row[0]} = [@row];
			my $dir   = "";
			if($row[1] ne ""){$dir .= " ".$row[1].","}#Barrio 	
			if($row[2] ne ""){$dir .= " ".$row[2].","}#Poligono 
			if($row[3] ne ""){$dir .= " ".$row[3].","}#Block 	
			if($row[4] ne ""){$dir .= " ".$row[4].","}#Calle 	
			if($row[5] ne ""){$dir .= " ".$row[5].","}#Avenidad 
			if($row[6] ne ""){$dir .= " ".$row[6].","}#Pasaje 	
			if($row[7] ne ""){$dir .= " ".$row[7].","}#Senda 	
			if($row[8] ne ""){$dir .= "# ".$row[8].","}	
			$dir = substr($dir,0,length($dir)-1);
			$hash{$row[0]} = $dir;
		}
	}
    return %hash;
}
sub construct_table {
    my $count   = 1;
    my $table   = "";
    my %tar_cue = ();    
    my %thead_h = ();
    my %tfoot_h = ();    
    my $thead_s = "";
    my $tfoot_s = "";    
    my $rowset  = "";
    my $thead   = "<thead><tr><th>No.</th><th>ID</th><th>GENERADOR</th><th>DIRECCION</th></tr></thead>";
    foreach my $k (sort keys %expedientes){
		my $cls		    = lc($expedientes{$k}[2]);
		my $new_clase	= $cls."_".$clase;		
		if($componente eq "PL"){
			if(uc($add_clase) eq "SI"){
				$ini_href="<a href='../pl/".$expedientes{$k}[2]."_".$script.".pl?clase=$cls&id=$k&dir=$direcciones{$k}'>";				
			}else{
				$ini_href="<a href='../pl/".$script.".pl?clase=$cls&id=$k&dir=$direcciones{$k}'>";
			}
		}else{
			if(uc($expedientes{$k}[2]) eq "INMUEBLE"){$new_clase = "inmueble_".$script}
			if(uc($expedientes{$k}[2]) eq "EMPRESA"){$new_clase = "empresa_".$script}
			my $title = $new_clase."_".$componente;
			$ini_href="<a href=javascript:parent.opener.parent.list.OpenWindowPl('$title','$componente,$new_clase,300,100','$i8_nuc_id','','$k','$properties');close()>";
		}
		my $row   = "	<td>".$ini_href.$count."</a></td>
						<td>".$k."</td>
						<td>".$expedientes{$k}[2]."</td>						
						<td>".$direcciones{$k}."</td>
					";
		$rowset  	.= "<tr>".$row."</tr>";
		$count++;	
    }
    $rowset    = "<tbody>".$rowset."</tbody>";
    $table     = "<table id=grid>".$thead.$rowset."</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 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};
    $country    = $data->{portal}->{country};
}
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1