#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Author:Rodolfo Machon
# Company:WebSoft, SA de CV 
# Nombre del archivo: expedienteId.get.pl
# Created: 2022-04-07
# Updates: 2022-04-07
# Copyright - All Rights Reserved
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PERL MODULES
use CGI;
use CGI::Carp qw(fatalsToBrowser);
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; charset=utf-8\n\n";
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#GET PARAMETERS
$form		= new CGI;
$appid		= $form->param('appid');
$id			= $form->param('id');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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;
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
# PROGRAM DESCRIPTION
# PROCUREMENT
($iid,$codid,$pid,$nis,$nic) = get_inmueble();
($cid,$contribuyente) 		 = get_contribuyente();
$nuc						 = get_nuc();
# INTEGRATION
if($nuc eq ""){
	$nuc = insert_nuc();
	insert_nuc_expediente();
}
# PRESENTATION
print "$nuc";
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_inmueble {
	my @arr = ();   
	my $sql = "SELECT a.i8_inmueble_id,a.c8_cuenta_rct_id,a.c16_codigo_catastral_id,b.i8_nis_id,b.i8_nic_id
			   FROM sdm_inmuebles a
			   LEFT JOIN sdd_inmueble_suministro_nics b ON a.i8_inmueble_id=b.i8_inmueble_id and b.c8_estado='ACT'
			   WHERE a.i8_inmueble_id='$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) {
		@arr	= @row;
	}
	return ($arr[0],$arr[1],$arr[2],$arr[4],$arr[4],$arr[5]);
}
sub get_contribuyente {
	my @arr = ();   
	my $sql = "SELECT a.i8_contribuyente_id,b.v128_contribuyente
			   FROM sdd_inmueble_contribuyentes a
			   LEFT JOIN sdm_contribuyentes b ON a.i8_contribuyente_id=b.i8_contribuyente_id
			   WHERE a.i8_inmueble_id='$iid' and a.i2_tipo_contribuyente_id='1'
			  ";
	#print "$sql <br>";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_nuc');
	if (my @row = $sth->fetchrow_array) {
		@arr	= @row;
	}
	return ($arr[0],$arr[1]);
}
sub get_nuc{
	my $str	= "";
	my $sql = "SELECT i8_nuc_id FROM sdd_nuc_expedientes
			   WHERE i8_expediente_id='$iid' and c3_tipo_generador_id='INM'
			  ";
	my $sth = $dbh->prepare($sql);
	$sth->execute or die SQL_fail($sql,$dbh,'sub get_nuc');
	if (my @row = $sth->fetchrow_array) {
		$str	= $row[0];
	}
	return $str;
}
sub insert_nuc {
	my $str	= "";
	my $sql = "INSERT INTO sdm_nucs (v128_nuc,ts_in,ts_update)
			   VALUES ('$contribuyente',now(),now())";
	$dbh->do($sql);
	my $sql1 = "SELECT LAST_INSERT_ID()";
	my $sth = $dbh->prepare($sql1);
	$sth->execute or die SQL_fail($sql1,$dbh,'sub insert_get_nuc');
	if (my @row = $sth->fetchrow_array) {
		$str = $row[0]
	}
	return $str;
}
sub insert_nuc_expediente {
	if($nis eq ""){$nis=0}
	if($nic eq ""){$nic=0}	
	my $sql = "INSERT INTO sdd_nuc_expedientes (i8_nuc_id,i8_expediente_id,c3_tipo_generador_id,i8_nis_id,i8_nic_id,c8_cuenta_rct_id,c16_codigo_catastral_id,ts_update,ts_in)
			   VALUES ('$nuc','$iid','INM','$nis','$nic','$codid','$pid',now(),now())";
	#print "$sql <br>";
	$dbh->do($sql);
}
