#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2013-01-10
# Updates: 2013-01-10
# Author:Rodolfo Machon
# File Name: judiciales_sectores_p.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
$cgiparms      = new CGI;
$clase 	       = $cgiparms->param('clase');
$boton        = $cgiparms->param('boton');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($clase eq ""){print "Your are not passing CLASE parameter<br>";}
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# OPEN DATABASE CONNECTIONS - ODBCs
get_app_data();
open_database_connections();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH 	= "../".$lan."/";
$file   = $lan."_judiciales_sectores_p.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
($amis,$admi,$judi)	    = get_tipo_notificaciones();
%expedientes_notificaciones = get_expedientes_notificaciones();
if (keys %expedientes_notificaciones) {
	%sectores  	          = get_sectores();
	%sectores_expedientes     = get_sectores_expedientes();
	# INTEGRATION
	%sectores_notificaciones = get_sectores_notificaciones();
	# PRESENTATION
	$table 		   = construct_table();
}
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_tipo_notificaciones {
    my $ami   = "";
    my $adm   = "";
    my $jud   = "";
    my %hash  = ();
    my $sql = "SELECT a.i2_tipo_notificacion_id,
			a.v64_tipo_notificacion
		 FROM sdm_tipo_notificaciones a";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_sector');
	while (my @row = $sth->fetchrow_array) {
		if($row[0]>0 && $row[0]<20){$ami .= "<li>".$row[0].". ".$row[1]."<li>";}
		if($row[0]>=20 && $row[0]<30){$adm .= "<li>".$row[0].". ".$row[1]."<li>";}
		if($row[0]>=30 && $row[0]<40){$jud .= "<li>".$row[0].". ".$row[1]."<li>";}
    }
    $ami = "<ul>".$ami."</ul>";
    $adm = "<ul>".$adm."</ul>";
    $jud = "<ul>".$jud."</ul>";
    return $ami,$adm,$jud;
}
sub get_expedientes_notificaciones {
    my %hash  = ();
    my $table = "";
    if($clase eq "inmueble"){$table = "sdm_".$clase."s"}
    if($clase eq "empresa"){$table = "sdd_".$clase."_ubicacion"}
    my $sql = "SELECT c.c11_sector_id,
			SUM(IF(a.i2_tipo_notificacion_id='1',1,0)),
			SUM(IF(a.i2_tipo_notificacion_id='2',1,0)),
			SUM(IF(a.i2_tipo_notificacion_id='3',1,0)),
			SUM(IF(a.i2_tipo_notificacion_id='10',1,0)),
			SUM(IF(a.i2_tipo_notificacion_id='11',1,0)),
			SUM(IF(a.i2_tipo_notificacion_id='12',1,0)),
			SUM(IF(a.i2_tipo_notificacion_id='20',1,0)),
			SUM(IF(a.i2_tipo_notificacion_id='21',1,0)),
			SUM(IF(a.i2_tipo_notificacion_id='22',1,0)),
			SUM(IF(a.i2_tipo_notificacion_id='30',1,0)),
			SUM(IF(a.i2_tipo_notificacion_id='31',1,0)),
			SUM(IF(a.i2_tipo_notificacion_id='32',1,0))			
		 FROM sdd_".$clase."_administrativo a
		 INNER JOIN sdm_".$clase."s b ON a.i8_".$clase."_id=b.i8_".$clase."_id
		 LEFT JOIN $table c ON a.i8_".$clase."_id=c.i8_".$clase."_id
		 WHERE b.c16_estado='ACEPTADO' 
		 GROUP BY c.c11_sector_id
		 ORDER BY c.c11_sector_id";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_sector');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_sectores {
    my %hash  = ();
    foreach my $k (sort keys %expedientes_notificaciones){$str .= "'".$k."',"}
    $str = substr($str,0,length($str)-1);    
    my $sql = "SELECT a.c11_sector_id,
			a.v64_sector,
			b.v64_tipo_sector
		 FROM sdm_sectores a
		 INNER JOIN sdm_tipo_sectores b	ON a.i2_tipo_sector_id = b.i2_tipo_sector_id
		 WHERE a.c11_sector_id IN ($str)
		 ORDER BY a.c11_sector_id";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_sector');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_sectores_expedientes {
    my %hash  = ();
    my $sql = "";
    my $str   = "";
    foreach my $k (sort keys %sectores){$str .= "'".$k."',"}
    $str = substr($str,0,length($str)-1);
    if($clase eq "inmueble"){
	$sql = "SELECT a.c11_sector_id,count(a.i8_".$clase."_id)
		  FROM sdm_".$clase."s a
		  WHERE a.c16_estado='ACEPTADO' and a.c11_sector_id IN ($str)
		  GROUP BY a.c11_sector_id";
    }
    if($clase eq "empresa"){
	$sql = "SELECT  b.c11_sector_id,count(a.i8_".$clase."_id)
		  FROM sdm_".$clase."s a
		  LEFT JOIN sdd_".$clase."_ubicacion b ON a.i8_".$clase."_id=b.i8_".$clase."_id
		  WHERE a.c16_estado='ACEPTADO' and b.c11_sector_id IN ($str)
		  GROUP BY a.c11_sector_id";
    }
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_sector');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]} = $row[1];
    }
    return %hash;
}
sub get_sectores_notificaciones {
    my %hash  = ();
    foreach my $k (sort keys %sectores){
		$hash{$k}{'nombre'}  	= $sectores{$k}[1];
		$hash{$k}{'tipo'}    	= $sectores{$k}[2];
		$hash{$k}{'expedientes'}= $sectores_expedientes{$k};
		$hash{$k}{'1'} 	 	= $expedientes_notificaciones{$k}[1];
		$hash{$k}{'2'} 	 	= $expedientes_notificaciones{$k}[2];
		$hash{$k}{'3'} 	 	= $expedientes_notificaciones{$k}[3];
		$hash{$k}{'10'} 	= $expedientes_notificaciones{$k}[10];
		$hash{$k}{'11'} 	= $expedientes_notificaciones{$k}[11];
		$hash{$k}{'12'} 	= $expedientes_notificaciones{$k}[12];
		$hash{$k}{'20'} 	= $expedientes_notificaciones{$k}[20];
		$hash{$k}{'21'} 	= $expedientes_notificaciones{$k}[21];
		$hash{$k}{'22'} 	= $expedientes_notificaciones{$k}[22];
		$hash{$k}{'30'} 	= $expedientes_notificaciones{$k}[30];
		$hash{$k}{'31'} 	= $expedientes_notificaciones{$k}[31];
		$hash{$k}{'32'} 	= $expedientes_notificaciones{$k}[32];	
    }
    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><td>No.</td><td>TIPO SECTOR</td><td>SECTOR ID</td><td>SECTOR</td><td>EXPEDIENTES</td><td>1</td><td>2</tr><td>3</td><td>10</td><td>11</td><td>12</td><td>20</td><td>21</td><td>22</td><td>30</td><td>31</td><td>32</td></tr></thead>";
    my $tfoot   = "<tfoot><tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>TOTAL</td><td>{t_expedientes}</td><td>{t_1}</td><td>{t_2}</td><td>{t_3}</td><td>{t_10}</td><td>{t_11}</td><td>{t_12}</td><td>{t_20}</td><td>{t_21}</td><td>{t_22}</td><td>{t_30}</td><td>{t_31}</td><td>{t_32}</td></tr></tfoot>";
    foreach my $k (sort keys %sectores_notificaciones){
	my $sec   = $sectores_notificaciones{$k}{'tipo'}." ".$sectores_notificaciones{$k}{'nombre'};
	my $row   = "<td><a href=\"javascript:OpenLink('$k','$clase','$sec')\">$count</a></td>
		     <td>".$k."</td><td>".$sectores_notificaciones{$k}{'tipo'}."</td>
		     <td>".$sectores_notificaciones{$k}{'nombre'}."</td>
		     <td>".$sectores_notificaciones{$k}{'expedientes'}."</td>
		     <td>".$sectores_notificaciones{$k}{'1'}."</td>
		     <td>".$sectores_notificaciones{$k}{'2'}."</td>
		     <td>".$sectores_notificaciones{$k}{'3'}."</td>
		     <td>".$sectores_notificaciones{$k}{'10'}."</td>
		     <td>".$sectores_notificaciones{$k}{'11'}."</td>
		     <td>".$sectores_notificaciones{$k}{'12'}."</td>
		     <td>".$sectores_notificaciones{$k}{'20'}."</td>
		     <td>".$sectores_notificaciones{$k}{'21'}."</td>
		     <td>".$sectores_notificaciones{$k}{'22'}."</td>
		     <td>".$sectores_notificaciones{$k}{'30'}."</td>
		     <td>".$sectores_notificaciones{$k}{'31'}."</td>
		     <td>".$sectores_notificaciones{$k}{'32'}."</td>
		     ";
	$rowset  	.= "<tr>".$row."</tr>";
	$t_expedientes  += $sectores_notificaciones{$k}{'expedientes'};
	$t_1     	+= $sectores_notificaciones{$k}{'1'};
	$t_2   		+= $sectores_notificaciones{$k}{'2'};
	$t_3     	+= $sectores_notificaciones{$k}{'3'};
	$t_10   	+= $sectores_notificaciones{$k}{'10'};
	$t_11     	+= $sectores_notificaciones{$k}{'11'};
	$t_12   	+= $sectores_notificaciones{$k}{'12'};
	$t_20     	+= $sectores_notificaciones{$k}{'20'};
	$t_21   	+= $sectores_notificaciones{$k}{'21'};
	$t_22   	+= $sectores_notificaciones{$k}{'22'};
	$t_30     	+= $sectores_notificaciones{$k}{'30'};
	$t_31   	+= $sectores_notificaciones{$k}{'31'};
	$t_32   	+= $sectores_notificaciones{$k}{'32'};
	$count++;	
    }
    $tfoot     =~ s/\{t_expedientes}/$t_expedientes/g;    
    $tfoot     =~ s/\{t_1}/$t_1/g;
    $tfoot     =~ s/\{t_2}/$t_2/g;
    $tfoot     =~ s/\{t_3}/$t_3/g;
    $tfoot     =~ s/\{t_10}/$t_10/g;
    $tfoot     =~ s/\{t_11}/$t_11/g;
    $tfoot     =~ s/\{t_12}/$t_12/g;
    $tfoot     =~ s/\{t_20}/$t_20/g;
    $tfoot     =~ s/\{t_21}/$t_21/g;
    $tfoot     =~ s/\{t_22}/$t_22/g;
    $tfoot     =~ s/\{t_30}/$t_30/g;
    $tfoot     =~ s/\{t_31}/$t_31/g;
    $tfoot     =~ s/\{t_32}/$t_32/g;
    $rowset    = "<tbody>".$rowset."</tbody>";
    $table     = "<table id=grid>".$thead.$rowset.$tfoot."</table>";
    return $table;
}
sub construct_page {
    open (DATA, "$PATH$file");
    while ($line = <DATA>) {	
        $line =~ s/\{clase}/$clase/g;
        $line =~ s/\{city}/$city/g;
        $line =~ s/\{state}/$state/g;  
        $line =~ s/\{clase}/$clase/g;         
        $line =~ s/\{table}/$table/g;
        $line =~ s/\{amistoso}/$amis/g;
        $line =~ s/\{administrativo}/$admi/g;
        $line =~ s/\{judicial}/$judi/g;
        $line =~ s/\{boton}/$boton/g;
        print "$line";
    }
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub numeros {$a<=>$b}
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->{LongReadLen} = 512 * 1024;	
}
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}
#-------------------------------------------------------------------------