#!/perl/bin/perl.exe
$|=1;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# Author:Rodolfo Machon
# Company:WebSoft, SA de CV 
# Nombre del archivo: nuc.empresa.get.pl
# Created: 2017-09-21
# Updates: 2017-09-21
# 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;
$clase		= $form->param('clase');
$id			= $form->param('id');
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# GET VARIABLES
get_app_data();
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# 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
($eid,$codid,$pid,$nis,$nic) = get_empresa();
if($eid eq ""){print "0|EL ID QUE INGRESO NO FUE ENCONTRADO";exit;}

($cid,$contribuyente) 		 = get_contribuyente_empresa();
if($cid eq ""){print "0|EL EXPEDIENTE NO PRESENTA CONTRIBUYENTE TITULAR ASIGNADO";exit;}
$nuc						 = get_nuc_expediente();
# INTEGRATION
if($nuc eq "" || $nuc <=0){
	$cid_nuc = get_contribuyente_nuc();
	if($cid_nuc  ne ""){
		insert_nuc_expediente();		
	}else{
		$nuc = insert_nuc();
		insert_nuc_expediente();
	}
}
# PRESENTATION
print "$nuc";	
exit;
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#SUB-ROUTINES -> LEVEL 1
sub get_empresa {
	my @arr = ();   
	my $sql = "SELECT a.i8_empresa_id,a.c8_base_empresa,b.c16_codigo_catastral_id
			   FROM sdm_empresas a
			   LEFT JOIN sdd_empresa_ubicacion b ON a.i8_empresa_id=b.i8_empresa_id
			   WHERE a.i8_empresa_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],0,0);
}
sub get_contribuyente_empresa {
	my @arr = ();   
	my $sql = "SELECT a.i8_contribuyente_id,b.v128_contribuyente
			   FROM sdd_empresa_contribuyentes a
			   LEFT JOIN sdm_contribuyentes b ON a.i8_contribuyente_id=b.i8_contribuyente_id
			   WHERE a.i8_empresa_id='$eid' 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_expediente {
	my $str	= "";
	my $sql = "SELECT a.i8_nuc_id FROM sdd_nuc_expedientes a
			   WHERE a.i8_expediente_id='$eid' and a.c3_tipo_generador_id='EMP'
			  ";
	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 get_contribuyente_nuc {
	my $str	= "";
	my $sql = "SELECT a.i8_nuc_id FROM sdd_nuc_expedientes a
			   WHERE a.i8_expediente_id='$eid' and a.c3_tipo_generador_id='EMP'
			  ";
	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','$eid','EMP','$nis','$nic','$codid','$pid',now(),now())";
	$dbh->do($sql);
}
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------
#AUXILIAR-SUBROUTINES
sub get_app_data {
    my $schema   = "app.data.xml";
    my $xml      = new XML::Simple;
    $data 	  = $xml->XMLin("../xml/".$schema);
    $appid	  = $data->{portal}->{app};
}
sub SQL_fail {
    my ($query,$dbh,,$section)= @_;
    print "SQL failed in Section $section<br>";
    print "Query: ".$query."<br>";    
    exit;
}
#------------------------------------------------------------------------- 
#-------------------------------------------------------------------------