#!/perl/bin/perl.exe
$|=1;
#--------------------------------------------------------------------------------
#--------------------------------------------------------------------------------
# Information Software SA
# Date In: 2013-05-21
# Updates: 2013-05-21,2022-03-26
# Author:Rodolfo Machon
# File Name: inmuebles_sectores_revision_detail_rept.pl
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use DBI;
use XML::Simple;
use Data::Dumper;
use CGI::Carp qw(fatalsToBrowser);
binmode STDOUT, ":encoding(utf8)";
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$cgiparms   	= new CGI;
$c11_sector_id 	= $cgiparms->param('c11_sector_id');
$v64_sector    	= $cgiparms->param('v64_sector');
$zid    		= $cgiparms->param('zid');
$ubi_id     	= $cgiparms->param('ubi_id');
$ubi_tipo   	= $cgiparms->param('ubi_tipo');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# CHECK PARAMETERS

#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
get_app_data();
db_connect();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLBAL VARIABLES
$PATH 		= "../".$lan."/";
$file       = $lan."_inmuebles_sectores_revision_detail_rept.html";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
$hoy                    = get_fecha();
($uid,$uf,$ul,$rev,$sup)= get_user();
$ubi_field				= get_ubi_field($ubi_tipo);
%tarifas				= get_tarifas();
%tributos				= get_tributos();
%expedientes			= get_expedientes();
%expedientes_nis		= get_nis();
%expedientes_direccion	= get_expedientes_direccion();
%expedientes_servicios	= get_expedientes_servicios();
%expedientes_bases		= get_expedientes_bases();
%expedientes_imagenes	= get_expedientes_imagenes();
# INTEGRATION
%expedientes_tributos   = get_expedientes_tributos();
%expedientes_direccion_servicios = get_expedientes_direccion_servicios();
# PRESENTATION
$table 		   	= construct_table();
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_user {
	my $uid	= "";
	my $uf	= "";
	my $ul	= "";
	my $re	= "";
	my $su	= "";
    my $user  = $ENV{"REMOTE_USER"};
	my $sql = "SELECT a.i8_user_id,a.v32_fname,a.v32_lname,a.c2_revisador,a.c2_supervisor FROM sdm_users a where a.v16_login='$user'";
    my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_user');
	while (my @row = $sth->fetchrow_array) {
		$uid = $row[0];
		$uf  = $row[1];
		$ul  = $row[2];
		$re  = $row[3];
		$su  = $row[4];		
	}
	return $uid,$uf,$ul,$re,$su;
}
sub get_ubi_field {
	my ($ubit)=@_;
	$ubif{'calles'}   = "c4_calle_id";
	$ubif{'avenidas'} = "c4_avenida_id";
	$ubif{'pasajes'}  = "c2_pasaje_id";
	$ubif{'sendas'}   = "c2_senda_id";
	$ubif{'poligonos'}= "c2_poligono_id";
	$ubif{'blocks'}   = "c2_block_id";	
	return $ubif{$ubit};
}
sub get_tarifas {
    my %hash   = ();
	my $sql = "SELECT a.c8_tributo_id,
						a.c1_tipo_tarifa_id,
						a.c2_codigo_id,
					    b. c2_tipo_base_imponible_id
				 FROM sdm_tarifas a
				 INNER JOIN sdd_tarifas b ON a.c16_tarifa_id=b.c16_tarifa_id
				 ORDER BY a.c16_tarifa_id
				 ";
    my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_tarifas');
	while (my @row = $sth->fetchrow_array) {
		$hash{$row[0]}{$row[1]}{$row[2]} = $row[3];
    }
    return %hash;
}
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];
    }
    return %hash;
}
sub get_expedientes {
    my %hash   = ();
	if($ubi_id ne "" && $ubi_tipo ne ""){$filtro = "AND a.$ubi_field='$ubi_id'"}else{$filtro = ""}	
	my $sql = "SELECT a.i8_inmueble_id,c.v64_apellidos,c.v64_nombres,a.c8_cuenta_rct_id,a.c2_revisado,a.c2_supervisado,a.c2_observado,a.c2_actualizado
				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' AND a.c2_zona_id='$zid' $filtro
				GROUP BY a.i8_inmueble_id";
	#print "$sql <br>";
    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";
	#print "$sql <br>";
    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		
		 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";
	#print "$sql <br>";
    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);	
		$hash{$row[0]} = $dir;
    }
    return %hash;
}
sub get_expedientes_servicios {
     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.c8_tributo_id,
						 b.c1_tipo_tarifa_id,
						 b.c2_codigo_id,
						 b.c2_gravable,
						 b.i2_grupo_id	 
				  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 "$sql <br>";				 
    my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_expediente_servicios');
	while (my @row = $sth->fetchrow_array) {
        $hash{$row[0]}{$row[1]}{$row[5]}= [@row];
    }
 	return %hash;
}
sub get_expedientes_bases {
     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.c2_tipo_base_imponible_id,
						 (b.f4_total-b.f4_deducible),
						 b.i2_grupo_id						 
				  FROM sdm_inmuebles a
				  INNER JOIN sdd_inmueble_bases_imponibles b ON a.i8_inmueble_id=b.i8_inmueble_id
				  WHERE a.i8_inmueble_id in ($exps)
				  ";
	#print "$sql";
    my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_expediente_bases');
	while (my @row = $sth->fetchrow_array) {
        $hash{$row[0]}{$row[1]}{$row[3]}= [@row];
    }
 	return %hash;
}
sub get_expedientes_imagenes {
	my %hash    	= ();
	my $files_path 	= "../fichas_cuentas_inmuebles/";
	opendir (DIR,$files_path) or die "$!";
	@files = readdir DIR;	
	close DIR;
	foreach $fi (@files){
        my @fin  = split(/\_/,$fi);
		my $fina = $fin[0];
		$hash{$fina} += 1;
	}
	return %hash;
}
sub get_expedientes_tributos {
    my %hash  = ();
    foreach my $k (sort keys %expedientes_servicios){
		foreach my $kk (sort keys %{$expedientes_servicios{$k}}){
			foreach my $kkk (sort keys %{$expedientes_servicios{$k}{$kk}}){		
				my $bit 				  = $tarifas{$kk}{$expedientes_servicios{$k}{$kk}{$kkk}[2]}{$expedientes_servicios{$k}{$kk}{$kkk}[3]};
				$hash{$k}{$kk}{'tributo'} = $kk;
				$hash{$k}{$kk}{'tip_tar'} = $expedientes_servicios{$k}{$kk}{$kkk}[2];
				$hash{$k}{$kk}{'codigo'}  = $expedientes_servicios{$k}{$kk}{$kkk}[3];
				$hash{$k}{$kk}{'gravable'}= $expedientes_servicios{$k}{$kk}{$kkk}[4];			
				$hash{$k}{$kk}{'tip_bas'} = $bit;
				$hash{$k}{$kk}{'medida'}  = $expedientes_bases{$k}{$bit}{$kkk}[2];
			}
		}	
    }
    return %hash;
}
sub get_expedientes_direccion_servicios {
    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}{'ficha'}		 = $expedientes{$k}[3];
		$hash{$k}{'revisado'}	 = $expedientes{$k}[4];
		$hash{$k}{'supervisado'} = $expedientes{$k}[5];
		$hash{$k}{'observado'}   = $expedientes{$k}[6];
		$hash{$k}{'actualizado'} = $expedientes{$k}[7];
    }
    return %hash;
}
sub construct_table {
    my $count   	= 1;
	my %hash    	= ();
	my %servicios	= ();
    my $table   	= "";
    my %tar_cue 	= ();    
    my %thead_h 	= ();
    my $thead_s 	= "";
    my $rowset  	= "";
	my $desactivado_r  	= "disabled='true'";
	my $desactivado_s  	= "disabled='true'";	
	my $str_notificacion = "Agregar";
    my $thead   = "<thead><tr><td>No.</td><td>REV</td><td>OBS</td><td>SUP</td><td>ACT</td><td>ID</td><td><a href=\"javascript:OpenContribuyentes()\">NOMBRE CONTRIBUYENTE</a></td><td>DIRECCION</td><td>FICHA No.</td><td colspan=2>FICHA</td><td colspan=2>NIC - NIS</td><td colspan=2>TRIBUTOS</td>{thead_s}</tr></thead>";
	if ($rev eq "1") {$desactivado_r = "";}
	if ($sup eq "1") {$desactivado_s = "";}
	foreach my $k (sort keys %expedientes_servicios){foreach my $kk (sort keys %{$expedientes_servicios{$k}}){$hash{$kk}=$kk;}}
    foreach my $k (sort keys %hash){$thead_s .= "<td colspan=5>".$tributos{$k}[1]."</td>";}
    foreach my $k (sort keys %expedientes_direccion_servicios){
		my $doc_name	= "";		
		my $estado_r  	= "";
		my $estado_s  	= "";	
		my $observado   = $expedientes_direccion_servicios{$k}{'observado'};
		my $actualizado = $expedientes_direccion_servicios{$k}{'actualizado'};
		my $cod			= $expedientes{$k}[3];
		if ($rev eq "1") {$observado = "<a href=\"javascript:OpenComparacionR('$k')\">".$expedientes_direccion_servicios{$k}{'observado'}."</a>"}
		if ($sup eq "1") {$actualizado   = "<a href=\"javascript:OpenComparacionS('$k')\">".$expedientes_direccion_servicios{$k}{'actualizado'}."</a>"}
		my $nic = $expedientes_nis{$k}[1];
		my $act = $expedientes_nis{$k}[2];		
		if($nic eq ""){$nic = " - "; $act = " - "}
		if($expedientes_imagenes{$k} eq ""){$expedientes_imagenes{$k} = " - ";}		
		if(!$expedientes_direccion_servicios{$k}{'direccion'}){$expedientes_direccion_servicios{$k}{'direccion'}= "-"}
		if ($expedientes_direccion_servicios{$k}{'revisado'} eq "1") {$estado_r="checked"}
		if ($expedientes_direccion_servicios{$k}{'supervisado'} eq "1") {$estado_s="checked"}
		if ($expedientes_imagenes{$cod}>0){$doc_name="<a href=\"javascript:OpenFicha('$cod')\">FICHAS</a>"}
		my $row   = "<td><a href=\"javascript:OpenExpediente('$k')\">".$count."</a></td>
					 <td><input type=checkbox id='chk_r_$k' onclick=Revisado('$k') $desactivado_r $estado_r ></td>
					 <td>$observado</td>
					 <td><input type=checkbox id='chk_s_$k' onclick=Supervisado('$k') $desactivado_s $estado_s></td>
					 <td>$actualizado</td>					 
					 <td><a href=\"javascript:OpenBalance('$k')\">".$k."</a></td>
					 <td><a href=\"javascript:OpenPropietarios('$k')\">".$expedientes_direccion_servicios{$k}{'apellidos'}.", ".$expedientes_direccion_servicios{$k}{'nombres'}."</a></td>
					 <td><a href=\"javascript:OpenIdentificacion('$k')\">".$expedientes_direccion_servicios{$k}{'direccion'}."</a></td>
					 <td>".$expedientes_direccion_servicios{$k}{'ficha'}."</td>
					 <td>$doc_name</td>
					 <td><a href=\"javascript:SubirFicha('$k')\">".$expedientes_imagenes{$cod}."</a></td>
					 <td><a href=\"javascript:OpenNic('$k')\">".$nic."</a></td>
					 <td><a href=\"javascript:OpenAceptacion('$k')\">".$act."</a></td>
					 <td><a href=\"javascript:OpenServicios('$k')\">"."SER"."</a></td>
					 <td><a href=\"javascript:OpenBases('$k')\">"."BAS"."</a></td>					 
				    ";
		foreach my $kk (sort keys %hash){
			$row .= "<td>".$expedientes_tributos{$k}{$kk}{'gravable'}."</td>
					 <td>".$expedientes_tributos{$k}{$kk}{'tip_tar'}."</td>
					 <td>".$expedientes_tributos{$k}{$kk}{'codigo'}."</td>
					 <td>".$expedientes_tributos{$k}{$kk}{'tip_bas'}."</td>
					 <td>".$expedientes_tributos{$k}{$kk}{'medida'}."</td>
				    "; 
		}				 
		$rowset .= "<tr>".$row."</tr>";
		$count++;		
    }
	$thead  =~ s/\{thead_s}/$thead_s/g;
    $rowset = "<tbody>".$rowset."</tbody>";
    $table  = "<table id=grid>".$thead.$rowset."</table>";
    return $table;
}
sub construct_page {
    open (DATA, "$PATH$file");
	#print "$PATH$file <br>";
    while ($line = <DATA>) {	
        $line =~ s/\{lan}/$lan/g;
        $line =~ s/\{appid}/$appid/g;
        $line =~ s/\{table}/$table/g;
        $line =~ s/\{c11_sector_id}/$c11_sector_id/g;
        $line =~ s/\{v64_sector}/$v64_sector/g;
        $line =~ s/\{hoy}/$hoy/g;
        $line =~ s/\{city}/$city/g;
        $line =~ s/\{state}/$state/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 db_connect {
	$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 get_fecha_iso {
	my $fec  = "";
	my $sql = "SELECT DATE_FORMAT(CURDATE(),'%Y-%m-%d')";
    my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_fecha');
	while (my @row = $sth->fetchrow_array) {
        $fec = $row[0];                   
    }
	return $fec;	
}
sub get_fecha {
	my ($db) = @_;
	my $fec  = "";
	my $cloc = "";

	my $sql  = "SELECT \@\@lc_time_names";
	my $sth 	   = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_fecha');
	if (my @row = $sth->fetchrow_array) {
		$cloc = $row[0];
	}
	
	$sql = "SET lc_time_names ='".$lan."_".$country."'";
    $dbh->do($sql); 
	
	$sql = "SELECT UPPER(DATE_FORMAT(CURDATE(),'%W %e %M %Y'))";
	$sth  = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_fecha');
	if (my @row = $sth->fetchrow_array) {
		$fec = $row[0];
	}	

	$sql = "SET lc_time_names ='$cloc'";
    $dbh->do($sql); 

	return $fec;	
}
#-------------------------------------------------------------------------