#!/perl/bin/perl.exe
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Author:Rodolfo Machon
# Company:WebSoft, SA de CV 
# Nombre del archivo: contribuyente.expedientes.get.pl
# Created: 2017-10-01
# Updates: 2017-10-01
# Copyright - All Rights Reserved
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#PERL MODULES
use CGI;
use DBI;
use XML::Simple;
use Data::Dumper;
use Date::Calc qw(Add_Delta_YMD);
use CGI::Carp qw(fatalsToBrowser);
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');
$getback	= $cgiparms->param('getback');
$opener_form= $cgiparms->param('opener_form');
$id_field	= $cgiparms->param('id_field');
$field_focus= $cgiparms->param('field_focus');
$id			= $cgiparms->param('id');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
get_app_data();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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 ($id eq ""){print "Your are not passing ID parameter<br>";}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLOBAL VARIABLES
$PATH 	    = "../".$lan."/";
$file       = $lan.".contribuyente.expedientes.get.html";
#--------------------------------------------------------------------------
#--------------------------------------------------------------------------
# OPEN DATABASE CONNECTIONS - ODBCs
$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;
$dbh->{LongTruncOk} = 1;
#--------------------------------------------------------------------------
# MAIN-PROGRAM
# PROCUREMENT
%inmuebles		= get_expedientes('inmueble');
%empresas 		= get_expedientes('empresa');
%agua 		    = get_expedientes('agua');
%puestos 		    = get_expedientes('puesto');
%nis			= get_nis(%inmuebles);
%generales_inm	= get_generales('inmueble',%inmuebles);
%generales_emp	= get_generales('empresa',%empresas);
# INTEGRATION
#
# PRESENTATION
$inmuebles_tab	= construct_table('sdm_inmuebles','inmueble',%inmuebles);
$empresas_tab	= construct_table('sdd_empresa_ubicacion','empresa',%empresas);
$agua_tab	    = construct_table('sdm_aguas','agua',%agua);
$puestos_tab	= construct_table('sdm_puestos','puesto',%puestos);
construct_page();
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_expedientes {
    my ($name)= @_;
	my %hash = ();
	my $sql  = "SELECT  a.i8_".$name."_id,b.c16_estado
				FROM sdd_".$name."_contribuyentes a
				INNER JOIN sdm_".$name."s b ON a.i8_".$name."_id=b.i8_".$name."_id
				WHERE a.i8_contribuyente_id='$id'
				ORDER BY a.i8_".$name."_id
				";
	#print "$sql <br>";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_expedientes');
    while (@row = $sth->fetchrow_array) {
	    $hash{$row[0]} = $row[1];
	}
	return %hash;
}
sub get_nis {
    my (%hash1)	= @_;
	my %hash	= ();
	my $str		= "0,";
	foreach my $k (sort keys %hash1){$str .= $k.",";}
	$str = substr($str,0,length($str)-1);
	my $sql  = "SELECT  a.i8_inmueble_id,a.i8_nis_id,a.i8_nic_id
				FROM sdd_inmueble_suministro_nics a
				WHERE a.i8_inmueble_id IN ($str)
				ORDER BY a.i8_inmueble_id
				";
	#print "$sql <>";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_nis');
	while (@row = $sth->fetchrow_array) {
		$hash{$row[0]} = [@row];
	}
	return %hash;
}
sub get_generales {
    my ($name,%hash1)	= @_;	
	my %hash	= ();
	my $str		= "0,";
	foreach my $k (sort keys %hash1){$str .= $k.",";}
	$str = substr($str,0,length($str)-1);
	my $sql  = "SELECT  a.i8_".$name."_id,a.c8_cuenta_rct_id
				FROM sdm_".$name."s a
				WHERE a.i8_".$name."_id IN ($str)
				ORDER BY a.i8_".$name."_id
				";
	#print "$sql";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die SQL_fail($sql,$dbh,'sub get_generales');
    while (@row = $sth->fetchrow_array) {
	    $hash{$row[0]} = $row[1];
	}
	return %hash;
}
sub construct_table {
    my ($tab,$nam,%hash1) = @_;
    my $count   = 1;
	my $table	= "";
    foreach my $k (sort keys %hash1){
		my $t	= get_table();
		my %hash= get_ubicacion($tab,$nam,$k);
        
        
		$t =~ s/\{i8_expediente_id}/$k/g;
		$t =~ s/\{generador}/$nam/g;
		$t =~ s/\{c16_estado}/$hash1{$k}/g;
		$t =~ s/\{clave}/$generales_inm{$k}/g;
		$t =~ s/\{i8_nis_id}/$nis{$k}[1]/g;
		$t =~ s/\{i8_nic_id}/$nis{$k}[2]/g;
      
        foreach my $kk (sort keys %hash){$t =~ s/{$kk}/$hash{$kk}/g;}
		
        $table .= $t;
		$count++;
	}
    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/\{getback}/$getback/g;
		$line =~ s/\{opener_form}/$opener_form/g;
		$line =~ s/\{id_field}/$id_field/g;
		$line =~ s/\{field_focus}/$field_focus/g;
		$line =~ s/\{id}/$id/g;
		$line =~ s/\{inmuebles_tab}/$inmuebles_tab/g;
		$line =~ s/\{empresas_tab}/$empresas_tab/g;
		$line =~ s/\{agua_tab}/$agua_tab/g;
		$line =~ s/\{puestos_tab}/$puestos_tab/g;
		print "$line";
	}
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 2
sub get_ubicacion {
    my ($tab,$nam,$eid)= @_;
    my %ubi		= ();
	my $campos	= "a.i8_".$nam."_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,a.c16_codigo_catastral_id,a.txt_referencia";
	my @fields	= split(/\,/,$campos);
    my $sql 	= " SELECT $campos
					FROM ".$tab." 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_".$nam."_id='$eid'
					ORDER BY a.i8_".$nam."_id
				";
	#print "$sql <br>";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_ubicacion');
	while (my @row = $sth->fetchrow_array) {
		for (my $i=0;$i<=$#fields;$i++) {
			my @arr	= split(/\./,$fields[$i]);
			$ubi{$arr[1]} = $row[$i];
		}
    }
    return %ubi;
}
sub get_table {
	my $str = "
				<TABLE>
					<TR>
						<TD><a href=# onclick=OpenBalance('{i8_expediente_id}','{generador}')>Expedientes ID</a></TD>
                        <TD><a href=javascript:passBackId('{i8_expediente_id}','{generador}')>{i8_expediente_id}</a></TD><TD>Estado</TD><TD>{c16_estado}</TD>            
					</TR>
					<TR>
						<TD>Clave</TD><TD>{clave}</TD><TD>CodigoCatastral</TD><TD>{c16_codigo_catastral_id}</TD>
					</TR>
					<TR> 
						<TD>Tipo Sector</TD><TD>{v64_tipo_sector}</TD><TD>Barrio</TD><TD>{v64_barrio}</TD>
					</TR>
					<TR> 
						<TD>Sector</TD><TD>{v64_sector}</TD><TD>Casa #</TD><TD>{c4_casa}</TD>
					</TR>
					<TR> 
						<TD>Calle</TD><TD>{v64_calle}</TD><TD>Poligono</TD><TD>{v64_poligono}</TD>
					</TR>
					<TR> 
						<TD>Avenida</TD><TD>{v64_avenida}</TD><TD>Senda</TD><TD>{v64_senda}</TD>
					</TR>
					<TR>
						<TD>Pasaje</TD><TD>{v64_pasaje}</TD><TD>Block</TD><TD>{v64_block}</TD>
					</TR>
					<TR> 
						<TD>NIS</TD><TD>{i8_nis_id}</TD><TD>NIC</TD><TD>{i8_nic_id}</TD>                            
					</TR>
						<TR><TD colspan=4>Referencia</TD>
					</TR>
					<TR>
						<TD colspan=4 valign=top>{txt_referencia}</TD>
					</TR>
				</TABLE>	
			";
	return $str;
}
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#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 "Error: ".$dbh->Error()."\n<br>";
    print "Query: ".$query."<br>";    
    exit;
}
#-------------------------------------------------------------------------