#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2013-02-18
# Updates: 2013-02-18
# Author:Rodolfo Machon
# File Name: mora_zonas_notificaciones_p.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use Win32::ODBC;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$cgiparms   = new CGI;
$lan	    = $cgiparms->param('lan');
$appid	    = $cgiparms->param('appid');
$clase 	    = $cgiparms->param('clase');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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."_mora_zonas_notificaciones_p.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
%zonas  	    = get_zonas();
%zona_expedientes   = get_zona_expedientes();
%expedientes_mora   = get_expedientes_mora();
# INTEGRATION
%zona_expendientes_mora = get_zona_expedientes_mora();
# PRESENTATION
$table 		   = construct_table();
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_zonas {
    my $table = "";
    my %hash  = ();
    if($clase eq "inmueble"){$table = "sdm_".$clase."s";}
    if($clase eq "empresa"){$table = "sdd_".$clase."_ubicacion";}
    my $query = "SELECT
			a.c2_zona_id,
			c.v64_tipo_zona
		 FROM  $table a
		 LEFT JOIN sdm_tipo_zonas c on a.c2_zona_id=c.c2_tipo_zona_id
		 GROUP BY a.c2_zona_id
		 ORDER BY a.c2_zona_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_zona_expedientes {
    my %hash  = ();
    my $query = "";
    if($clase eq "inmueble"){
	$query = "SELECT a.c2_zona_id,count(a.i8_".$clase."_id)
		 FROM sdm_".$clase."s a
		 WHERE a.c16_estado='ACEPTADO'
		 GROUP BY a.c2_zona_id
		 ";
    }
    if($clase eq "empresa"){
	$query = "SELECT  b.c2_zona_id,count(a.i8_".$clase."_id)
		  FROM sdm_".$clase."s a
		  INNER JOIN sdd_".$clase."_ubicacion b ON a.i8_".$clase."_id=b.i8_".$clase."_id
		  WHERE a.c16_estado='ACEPTADO'
		  GROUP BY a.c2_zona_id";
    }
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_sector_expedientes')};
    while($dbh->FetchRow()){
	my @row = $dbh->Data();
	$hash{$row[0]} = $row[1];
    }
    return %hash;
}
sub get_expedientes_mora {
    my %hash  = ();
    if($clase eq "inmueble"){
	$query = "SELECT b.c2_zona_id,count(distinct(a.i8_".$clase."_id))
		  FROM sdd_".$clase."_estados_mensuales a
		  INNER JOIN sdm_".$clase."s  b	on a.i8_".$clase."_id=b.i8_".$clase."_id
		  WHERE a.i4_dias_mora>0
		  GROUP BY b.c2_zona_id";
    }
    if($clase eq "empresa"){
	$query = "SELECT c.c2_zona_id,count(distinct(a.i8_".$clase."_id))
		  FROM sdd_".$clase."_estados_mensuales a
		  INNER JOIN sdm_".$clase."s  b	ON a.i8_".$clase."_id=b.i8_".$clase."_id
		  INNER JOIN sdd_".$clase."_ubicacion c ON a.i8_".$clase."_id=c.i8_".$clase."_id
		  WHERE a.i4_dias_mora>0
		  GROUP BY b.c2_zona_id";
    }
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_expedientes_mora')};
    while($dbh->FetchRow()){
	my @row = $dbh->Data();
	$hash{$row[0]} = $row[1];
    }
    return %hash;
}
sub get_zona_expedientes_mora {
    my %hash  = ();
    foreach my $k (sort keys %zona_expedientes){
	$hash{$k}{'zona_id'} = $zonas{$k}[0];
	$hash{$k}{'nombre'}  = $zonas{$k}[1];
	$hash{$k}{'activos'} = $zona_expedientes{$k};
	$hash{$k}{'morosos'} = $expedientes_mora{$k};
    }
    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>ID ZONA</td><td>ZONA</td><td>ACTIVOS</td><td>MOROSOS</tr></thead>";
    my $tfoot   = "<tfoot><td>&nbsp;</td><td>&nbsp;</td><td>TOTAL</td><td>{t_activos}</td><td>{t_morosos}</td></tr></tfoot>";    
    foreach my $k (sort keys %zona_expendientes_mora){
	my $sec   = $zona_expendientes_mora{$k}{'nombre'};
	my $row   = "<td><a href=\"javascript:OpenLink('$k','$clase','$sec')\">$count</a></td><td>".$zona_expendientes_mora{$k}{'zona_id'}."</td><td>".$zona_expendientes_mora{$k}{'nombre'}."</td><td>".$zona_expendientes_mora{$k}{'activos'}."</td><td>".$zona_expendientes_mora{$k}{'morosos'}."</td>";
	$rowset  .= "<tr>".$row."</tr>";
	$t_activos += $zona_expendientes_mora{$k}{'activos'};
	$t_morosos += $zona_expendientes_mora{$k}{'morosos'};
	$count++;	
    }
    $tfoot     =~ s/\{t_activos}/$t_activos/g;
    $tfoot     =~ s/\{t_morosos}/$t_morosos/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;
}
#-------------------------------------------------------------------------