#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2013-01-21
# Updates: 2013-01-21
# Author:Rodolfo Machon
# File Name: moras_sectores_rept.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');
$c11_sector_id = $cgiparms->param('c11_sector_id');
$i8_periodo    = $cgiparms->param('i8_periodo');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS
if ($lan eq ""){print "Your are not passing LAN parameter<br>";}
if ($appid eq ""){print "Your are not passing APPID parameter<br>";}
if ($clase eq ""){print "Your are not passing CLASE parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH 		   = "../".$lan."/";
$file          = $lan."_moras_sector_rept.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
if (!($dbh  = new Win32::ODBC($appid))){connect_fail($appid)}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
%moras	   	 = get_moras();
if (keys %moras) {
	%expedientes  	       = get_expedientes();
	%expedientes_direccion = get_expedientes_direccion();
	# INTEGRATION
	%sector_moras		   = get_sector_moras();
	# PRESENTATION
	$table 		   = construct_table();
}
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_moras {
    my %hash  = ();
    my $query = "";
	my $table = "sdr_".$clase."s_mora_resumen_".$i8_periodo;
    if($clase eq "inmueble"){
		$query = "SELECT a.i8_inmueble_id,
						 a.d82_val,
						 a.d82_fie,
						 a.d82_int,
						 a.d82_mul
				   FROM $table a
						LEFT JOIN sdm_inmuebles b ON a.i8_inmueble_id = b.i8_inmueble_id
				   WHERE b.c11_sector_id='$c11_sector_id'
				   GROUP BY a.i8_inmueble_id
				";
    }
    if($clase eq "empresa"){
		$query = "SELECT a.i8_empresa_id,
						 a.d82_val,
						 a.d82_fie,
						 a.d82_int,
						 a.d82_mul
					FROM $table a
						LEFT JOIN sdd_empresa_ubicacion b ON a.i8_empresa_id=b.i8_empresa_id
				   WHERE b.c11_sector_id='$c11_sector_id'
					GROUP BY a.i8_empresa_id
				";
    }
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_moras')};
    while($dbh->FetchRow()){
		my @row = $dbh->Data();
		$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_expedientes {
    my $str   = ();
    my %hash  = ();
    my $query = "";
    foreach my $k (sort keys %moras){$str .= "'".$k."',"}
    $str = substr($str,0,length($str)-1);    
    if($clase eq "inmueble"){
	$query = "SELECT a.i8_".$clase."_id,c.v64_apellidos,c.v64_nombres
		  FROM sdm_".$clase."s a
		  LEFT JOIN sdd_".$clase."_contribuyentes b ON a.i8_".$clase."_id=b.i8_".$clase."_id
		  LEFT JOIN sdm_contribuyentes c ON b.i8_contribuyente_id=c.i8_contribuyente_id
		  WHERE a.c16_estado='ACEPTADO' AND  a.i8_".$clase."_id IN ($str)
		  GROUP BY a.i8_".$clase."_id";
    }
    if($clase eq "empresa"){
	$query = "SELECT  a.i8_".$clase."_id,d.v64_apellidos,d.v64_nombres
		  FROM sdm_".$clase."s a
		  LEFT JOIN sdd_".$clase."_ubicacion b ON a.i8_".$clase."_id=b.i8_".$clase."_id
		  LEFT JOIN sdd_".$clase."_contribuyentes c ON a.i8_".$clase."_id=c.i8_".$clase."_id
		  LEFT JOIN sdm_contribuyentes d ON c.i8_contribuyente_id=d.i8_contribuyente_id
		  WHERE a.c16_estado='ACEPTADO' AND  a.i8_".$clase."_id IN ($str)
		  GROUP BY a.i8_".$clase."_id";
    }
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_expedientes')};
    while($dbh->FetchRow()){
	my @row = $dbh->Data();
	$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_expedientes_direccion {
    my $str   = ();
    my %hash  = ();
    my $tabla = "";
    foreach my $k (sort keys %moras){$str .= "'".$k."',"}
    $str = substr($str,0,length($str)-1);    
    if($clase eq "inmueble"){$tabla = "sdm_inmuebles"}
    if($clase eq "empresa"){$tabla = "sdd_empresa_ubicacion"}
    my $query = "SELECT a.i8_".$clase."_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,
			i.v64_sector,
			j.v64_tipo_sector			
		 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
		 LEFT JOIN sdm_sectores i ON a.c11_sector_id=i.c11_sector_id
		 LEFT JOIN sdm_tipo_sectores j ON a.i2_tipo_sector_id=j.i2_tipo_sector_id
		 WHERE a.i8_".$clase."_id IN ($str)
		 ORDER BY a.i8_".$clase."_id";
    if ( $dbh->Sql($query) ){SQL_fail($query,$dbh,'sub get_expedientes_direccion')};
    while($dbh->FetchRow()){
	my @row   = $dbh->Data();
	my $dir   = "";
	if($row[1] ne ""){$dir .= "Barrio ".$row[1].","}
	if($row[2] ne ""){$dir .= "Poligono ".$row[2].","}
	if($row[3] ne ""){$dir .= "Block ".$row[3].","}
	if($row[4] ne ""){$dir .= "Calle ".$row[4].","}
	if($row[5] ne ""){$dir .= "Avenidad ".$row[5].","}
	if($row[6] ne ""){$dir .= "Pasaje ".$row[6].","}
	if($row[7] ne ""){$dir .= "Senda ".$row[7].","}
	if($row[8] ne ""){$dir .= "# ".$row[8].","}	
	$dir = $row[10]." ".$row[9].", ".$dir;
	$dir = substr($dir,0,length($dir)-1);
	$hash{$row[0]} = $dir;
    }
    print "";
    return %hash;
}
sub get_sector_moras {
    my %hash  = ();
    foreach my $k (sort keys %expedientes){
		$hash{$k}{'apellidos'}  = $expedientes{$k}[1];
		$hash{$k}{'nombres'}	= $expedientes{$k}[2];	
		$hash{$k}{'direccion'}	= $expedientes_direccion{$k};
		$hash{$k}{'val'} 	 	= $moras{$k}[1];
		$hash{$k}{'fie'} 	 	= $moras{$k}[2];
		$hash{$k}{'int'} 	 	= $moras{$k}[3];
		$hash{$k}{'mul'} 		= $moras{$k}[4];
		$hash{$k}{'tot'} 		= $moras{$k}[1]+$moras{$k}[2]+$moras{$k}[3]+$moras{$k}[4];
    }
    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</td><td>APELLIDOS</td><td>NOMBRES</td><td>DIRECCION</td><td>TRIBUTOS</td><td>FIESTAS</td><td>INTERESES</td><td>MULTAS</td><td>TOTAL</td></tr></thead>";
    my $tfoot   = "<tfoot><tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>{t_val}</td><td>{t_fie}</td><td>{t_int}</td><td>{t_mul}</td><td>{t_tot}</td></tr></tfoot>";
    foreach my $k (sort keys %sector_moras){
		my $row   = "	<td><a href=\"javascript:OpenLink('$k','$clase','$i8_periodo')\">$count</a></td>
						<td>".$k."</td>
						<td>".$sector_moras{$k}{'apellidos'}."</td>
						<td>".$sector_moras{$k}{'nombres'}."</td>
						<td>".$sector_moras{$k}{'direccion'}."</td>
						<td>".$sector_moras{$k}{'val'}."</td>
						<td>".$sector_moras{$k}{'fie'}."</td>
						<td>".$sector_moras{$k}{'int'}."</td>
						<td>".$sector_moras{$k}{'mul'}."</td>
						<td>".$sector_moras{$k}{'tot'}."</td>
					";
		$rowset  	.= "<tr>".$row."</tr>";
		$t_val     	+= $sector_moras{$k}{'val'};
		$t_fie 		+= $sector_moras{$k}{'fie'};
		$t_int     	+= $sector_moras{$k}{'int'};
		$t_mul     	+= $sector_moras{$k}{'mul'};
		$t_tot     	+= $sector_moras{$k}{'tot'};
	
		$count++;	
    }

    $tfoot     =~ s/\{t_val}/$t_val/g;
    $tfoot     =~ s/\{t_fie}/$t_fie/g;
    $tfoot     =~ s/\{t_int}/$t_int/g;
    $tfoot     =~ s/\{t_mul}/$t_mul/g;
    $tfoot     =~ s/\{t_tot}/$t_tot/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;
        $line =~ s/\{i8_periodo}/$i8_periodo/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;
}
#-------------------------------------------------------------------------