#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# WebSoft SA
# Date In: 2012-11-27
# Updates: 2012-11-27, 2016-03-30
# Author: Rodolfo Machon
# File Name: inmuebles_sector_nis_p.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#PERL MODULES
use CGI;
use CGI::Carp qw/fatalsToBrowser warningsToBrowser/;
use DBI;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#HTML printing activation
print "Content-type: text/html; charset=utf-8\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');
$v64_sector    	= $cgiparms->param('v64_sector');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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."_inmuebles_sector_bases_rept.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
$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;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
%tributos			        = get_tributos();
%expedientes			= get_expedientes();
%expedientes_nis		= get_nis();
%expedientes_direccion	= get_expedientes_direccion();
%expedientes_servicios  = get_expedientes_servicios();
# INTEGRATION
$table 		   			= construct_table();
# PRESENTATION

construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_tributos {
    my %hash= ();
	my $sql = "SELECT c8_tributo_id,v64_tributo FROM sdm_tributos";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_tributos');
    while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]} = $row[1];
    }
    return %hash;
}
sub get_expedientes {
    my %hash = ();
	my $sql  = "SELECT a.i8_inmueble_id,c.v128_contribuyente
				FROM sdm_inmuebles a
				LEFT JOIN sdd_inmueble_contribuyentes b ON a.i8_inmueble_id=b.i8_inmueble_id and b.i2_tipo_contribuyente_id='1'
				LEFT JOIN sdm_contribuyentes c ON b.i8_contribuyente_id=c.i8_contribuyente_id
				WHERE a.c16_estado='ACEPTADO' AND a.c11_sector_id='$c11_sector_id'
				GROUP BY a.i8_inmueble_id";
     my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_expedientes');
    while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_nis {
    my %hash   = ();
    my $exps   = "'0',";
    foreach my $k (sort {$a<=>$b} keys %expedientes){$exps .= "'".$k."',";}
    $exps   = substr($exps,0,length($exps)-1);	
	my $sql = "SELECT a.i8_inmueble_id,b.i8_nic_id,b.c8_estado
				FROM sdm_inmuebles a
				LEFT JOIN sdd_inmueble_suministro_nics b ON a.i8_inmueble_id=b.i8_inmueble_id
				WHERE a.i8_inmueble_id in ($exps)
				GROUP BY a.i8_inmueble_id";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_nis');
    while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]} = [@row];
    }
    return %hash;
}
sub get_expedientes_direccion {
    my %hash   = ();
    my $tabla  = "";
    my $exps   = "'0',";
    foreach my $k (sort {$a<=>$b} keys %expedientes){$exps .= "'".$k."',";}
    $exps = substr($exps,0,length($exps)-1);
    my $sql = "SELECT a.i8_inmueble_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,
            a.txt_referencia
		 FROM sdm_inmuebles 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_inmueble_id in ($exps)
		 ORDER BY a.i8_inmueble_id";
	my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_expedientes_direccion');
    while (my @row = $sth->fetchrow_array) {
		my $dir   = "";
		if($row[1] ne ""){$dir .= " ".$row[1].","}
		if($row[2] ne ""){$dir .= " ".$row[2].","}
		if($row[3] ne ""){$dir .= " ".$row[3].","}
		if($row[4] ne ""){$dir .= " ".$row[4].","}
		if($row[5] ne ""){$dir .= " ".$row[5].","}
		if($row[6] ne ""){$dir .= " ".$row[6].","}
		if($row[7] ne ""){$dir .= " ".$row[7].","}		
		if($row[8] ne ""){$dir .= "# ".$row[8].","}	
		$dir = substr($dir,0,length($dir)-1);	
		$dirc = $dir;
        $dirc =~ s/\,//g;
		if($dirc eq ""){$hash{$row[0]} =$row[9];}else{$hash{$row[0]} = $dir;}
    }
    return %hash;
}
sub get_expedientes_servicios {
    my %hash= ();
    my $exps= "'0',";
    foreach my $k (sort keys %expedientes){$exps .= "'".$k."',";}
    $exps   = substr($exps,0,length($exps)-1);
    my $sql = "SELECT a.i8_inmueble_id,
						 b.c8_tributo_id,
						 b.v64_codigo		 
				  FROM sdm_inmuebles a
				  INNER JOIN sdd_inmueble_servicios b ON a.i8_inmueble_id=b.i8_inmueble_id
				  WHERE a.i8_inmueble_id in ($exps)
				  ";
	#print "$query";
	my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_expedientes_servicios');
    while (my @row = $sth->fetchrow_array) {
        $hash{$row[0]}{$row[1]}= $row[2];
    }
 	return %hash;
}
sub construct_table {
    my %hash    = ();
    my $count   = 1;
    my $table   = "";
    my $rowset  = "";
    my $ehead   = "";
    my $thead   = "<thead><tr><td>No.</td><td>ID</td><td>NOMBRE CONTRIBUYENTE</td><td>DIRECCION</td><td>NIC - NIS</td>{ehead}</tr></thead>";
    my $tfoot   = "<tfoot><tr><td colspan=5>TOTAL</td><td>{total}<td></tr></thead>";
    
    foreach my $k (sort keys %expedientes_servicios){
        foreach my $kk (sort keys %{$expedientes_servicios{$k}}){
            if($tributos{$kk}){
                $hash{$kk}=$tributos{$kk};               
            }
        }
    }
    foreach my $k (sort keys %hash){$ehead .= "<td>".$hash{$k}."</td>";}
    foreach my $k (sort keys %expedientes){
		my $row   = "<td><a href=\"javascript:OpenExpediente('$k')\">".$count."</a></td>
					 <td>$k</td>
					 <td>".$expedientes{$k}[1].", ".$expedientes{$k}[2]."</td>
					 <td>".$expedientes_direccion{$k}."</td>
					 <td>".$expedientes_nis{$k}[1]."</td>
				    ";
        foreach my $kk (sort keys %hash){
            if($expedientes_servicios{$k}{$kk}){
                $row .=  "<td>".$expedientes_servicios{$k}{$kk}."</td>";
            }else{
                $row .=  "<td></td>";
            }
        }
		$rowset .= "<tr>".$row."</tr>";
		$count++;		
    }
    $thead  =~ s/\{ehead}/$ehead/g;
    $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;
        $line =~ s/\{c11_sector_id}/$c11_sector_id/g;
        $line =~ s/\{v64_sector}/$v64_sector/g;
        $line =~ s/\{ubi_id}/$ubi_id/g;
        $line =~ s/\{ubi_tipo}/$ubi_tipo/g;
        $line =~ s/\{dt_vencimiento}/$dt_vencimiento/g;			   			   
        print "$line";
    }
}
#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub round {
    my ($n, $places) = @_;
    my $abs = abs $n;
    my $val = substr($abs + ('0.' . '0' x $places . '5'),0,length(int($abs)) +(($places > 0) ? $places + 1 : 0));
    ($n < 0) ? "-" . $val : $val;
}
sub SQL_fail {
    my ($query,$dbh,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}#-------------------------------------------------------------------------