#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Author:Rodolfo Machon
# Company:WebSoft, SA de CV 
# Nombre del archivo: inmueble.vinculacion.r.pl
# Created: 2022-08-04
# Updates: 2022-08-04
# Copyright - All Rights Reserved
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use JSON;
use XML::Simple;
use Data::Dumper;
binmode STDOUT, ":encoding(utf8)";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# HTML PRINTING ACTIVATION
print "Content-type: text/html; charset=utf-8\n\n";
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#GET PARAMETERS
$form	= new CGI;
$id				= $form->param('id');
$expediente_id	= $form->param('expediente_id');
$nis			= $form->param('nis');
$nic			= $form->param('nic');
$medidor		= $form->param('medidor');
$sector			= $form->param('sector');
$parcela		= $form->param('parcela');
$area			= $form->param('area');
$perimetro		= $form->param('perimetro');
$long			= $form->param('long x');
$lat			= $form->param('lat y');
$x				= $form->param('x');
$y				= $form->param('y');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET CLASE SCHEMA AND VARIABLES
get_app_data();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GLOBAL VARIABLES
$PATH	= "../".$lan."/";
$FILE	= $lan.".inmueble.vinculacion.r.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->{LongReadLen} = 512 * 1024;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# MAIN-PROGRAM
%expediente  = get_expediente();
if($expediente{'nis'}){
	$nis = $expediente{'nis'};
	$nic = $expediente{'nic'};
	$medidor = $expediente{'medidor'};
	%abonado = get_abonado();		
}
#%propietario = get_propietario();
if($long eq ""){$long=$x};
if($lat eq ""){$lat=$y};
construct_page();
exit;
#-------------------------------------------------------------------
#MAIN-SUBROUTINES
sub get_expediente{
	my %hash= "";
	my $sql = "SELECT a.i8_inmueble_id
					 ,b.i8_contribuyente_id
					 ,c.v128_contribuyente
					 ,d.i8_nis_id
					 ,d.i8_nic_id
					 ,d.c16_medidor_id
					 ,a.c8_cuenta_rct_id
					 ,a.txt_referencia
					 ,d.c8_estado
			   FROM sdm_inmuebles a
			   LEFT JOIN sdd_inmueble_contribuyentes b ON b.i8_inmueble_id=a.i8_inmueble_id
			   LEFT JOIN sdm_contribuyentes c ON c.i8_contribuyente_id=b.i8_contribuyente_id
			   LEFT JOIN sdd_inmueble_suministro_nics d ON d.i8_inmueble_id=a.i8_inmueble_id AND d.c8_estado='ACT'
			   WHERE a.i8_inmueble_id='$expediente_id'
			  ";
	#print "$sql <br>";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_expediente');
	if (my @row = $sth->fetchrow_array) {
		$hash{'expediente_id'} = $row[0];
		$hash{'contribuyente_id'} = $row[1];
		$hash{'contribuyente'} = $row[2];
		$hash{'nis'} = $row[3];
		$hash{'nic'} = $row[4];
		$hash{'medidor'} = $row[5];
		$hash{'sector_parcela'} = $row[6];
		$hash{'referencia'} = $row[7];
		$hash{'estado'} = $row[8];
	}
	return %hash;
}
sub get_abonado{
	my %hash= ();
	my $sql = "SELECT nombre,direccion FROM  aes WHERE nis='$nis'";
	#print "$sql <br>";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_abonados');
	if (my @row = $sth->fetchrow_array) {
		$hash{'abonado'} = $row[0];
		$hash{'direccion'} = $row[1];		
	}
	return %hash;
}
sub get_propietario{
	
}
sub construct_page{
	open (DATA, "$PATH$FILE");
	#print "$PATH$FILE";
	while ($line = <DATA>){
        $line =~ s/{id}/$id/g;
        $line =~ s/{expediente_id}/$expediente{expediente_id}/g;
        $line =~ s/{nis}/$nis/g;
        $line =~ s/{nic}/$nic/g;
        $line =~ s/{medidor}/$medidor/g;
        $line =~ s/{contribuyente_id}/$expediente{contribuyente_id}/g;
        $line =~ s/{contribuyente}/$expediente{contribuyente}/g;
        $line =~ s/{referencia}/$expediente{referencia}/g;
        $line =~ s/{estado}/$expediente{estado}/g;
        $line =~ s/{abonado}/$abonado{abonado}/g;
        $line =~ s/{direccion}/$abonado{direccion}/g;
        $line =~ s/{propietario}/$propietario/g;
        $line =~ s/{ubicacion}/$ubicacion/g;		
        $line =~ s/{sector}/$sector/g;
        $line =~ s/{parcela}/$parcela/g;
        $line =~ s/{area}/$area/g;
        $line =~ s/{perimetro}/$perimetro/g;
        $line =~ s/{long}/$long/g;
        $line =~ s/{lat}/$lat/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};
}