def get_nr_header(session, nr_num):
current_app.logger.debug(nr_num)
# get the NR Header
#############################
sql_nr = ('select request_id,'
'nr_num,'
'previous_request_id,'
'submit_count,'
'priority_cd,'
'request_type_cd,'
'expiration_date,'
'additional_info,'
'nature_business_info,'
'xpro_jurisdiction,'
'home_juris_num'
' from request_vw'
' where nr_num = :nr')
sql_lu = (
'select SYS_EXTRACT_UTC (cast(last_update as timestamp)) as last_update'
' from req_instance_max_event'
' where request_id = :id')
sql_state = ('select rs.state_type_cd'
' from request_state_vw rs'
' where rs.request_id = :req_id'
' and rs.end_event_id IS NULL')
result = session.execute(sql_nr, nr=nr_num)
row = result.fetchone()
col_names = [row[0] for row in session.description]
#get main row
if row:
nr = ora_row_to_dict(col_names, row)
# get last_updated
result = session.execute(sql_lu, id=nr['request_id'])
row = result.fetchone()
if row:
col_names = [row[0] for row in session.description]
nr = {**nr, **(ora_row_to_dict(col_names, row))}
# get state
result = session.execute(sql_state, req_id=nr['request_id'])
row = result.fetchone()
if row:
col_names = [row[0] for row in session.description]
nr = {**nr, **(ora_row_to_dict(col_names, row))}
current_app.logger.debug(nr)
current_app.logger.debug('BEFORE NRO EXPIRATION DATE SET')
current_app.logger.debug(nr['expiration_date'])
nr['expiration_date'] = correct_expiration_date(
nr['expiration_date'])
current_app.logger.debug(
'AFTER CORRECT_EXPIRATION EXPIRATION DATE SET')
current_app.logger.debug(nr['expiration_date'])
return nr
return None
def get_nr_header(session, nr_num):
# get the NR Header
#############################
sql_nr = (
'select request_id,'
'nr_num,'
'previous_request_id,'
'submit_count,'
'priority_cd,'
'request_type_cd,'
'expiration_date,'
'additional_info,'
'nature_business_info,'
'xpro_jurisdiction'
' from request_vw'
' where nr_num = :nr'
)
sql_lu = (
'select last_update'
' from req_instance_max_event'
' where request_id = :id'
)
sql_state = (
'select rs.state_type_cd'
' from request_state_vw rs'
' where rs.request_id = :req_id'
' and rs.end_event_id IS NULL'
)
result = session.execute(sql_nr, nr=nr_num)
row = result.fetchone()
col_names = [row[0] for row in session.description]
#get main row
if row:
nr = ora_row_to_dict(col_names, row)
# get last_updated
result = session.execute(sql_lu, id=nr['request_id'])
row = result.fetchone()
if row:
col_names = [row[0] for row in session.description]
nr = {**nr, **(ora_row_to_dict(col_names, row))}
# get state
result = session.execute(sql_state, req_id=nr['request_id'])
row = result.fetchone()
if row:
col_names = [row[0] for row in session.description]
nr = {**nr, **(ora_row_to_dict(col_names, row))}
current_app.logger.debug(nr)
return nr
return None
def get_nr_requester(session, request_id):
# get the NR Requester
#############################
sql = (
'select request_id,'
' last_name,'
' first_name,'
' middle_name,'
' phone_number,'
' fax_number,'
' email_address,'
' contact,'
' client_first_name,'
' client_last_name,'
' decline_notification_ind,'
' addr_line_1,'
' addr_line_2,'
' addr_line_3,'
' city,'
' postal_cd,'
' state_province_cd,'
' country_type_cd'
' from request_party_vw'
' where request_id = :req_id'
)
result = session.execute(sql, req_id=request_id)
row = result.fetchone()
if row:
col_names = [row[0] for row in session.description]
return ora_row_to_dict(col_names, row)
return None
def get_names(session, request_id):
"""
Get the NRO Names.
To support reset functionality - keep decision data in Namex but clear it in NRO - this
function will not overwrite name decision data if the reset flag is true.
"""
sql = (
'select choice_number,'
' name,'
' designation,'
' name_state_type_cd,'
' consumption_date,'
' corp_num'
' from names_vw'
' where request_id = :req_id'
)
result = session.execute(sql, req_id=request_id)
col_names = [row[0] for row in session.description]
names = []
for row in result:
names.append(ora_row_to_dict(col_names, row))
if len(names) < 1:
return None
return names
def job(app, db, nro_connection, max_rows=100):
row_count = 0
try:
ora_con = nro_connection
result, col_names = job_result_set(ora_con, max_rows)
for r in result:
row_count += 1
if row_count > max_rows:
return row_count
row = ora_row_to_dict(col_names, r)
corp_num = row['id']
corp_name = row['name']
corp_name = corp_name.replace('\'', "''")
start_date = row['start_date']
name_count = find_corp_count_in_namex(corp_num, corp_name)
if name_count == 0:
#these are the ones that need to be datafixed in namesp to geta valid consumed NR into Namex
insert_missing_corps_list(corp_num, corp_name)
else:
name_results = find_corp_in_namex(corp_num, corp_name)
for name in name_results:
if name.consumption_date:
test_date = name.consumption_date
test_date = test_date.date()
else:
test_date = None
test_start_date = start_date.date()
if test_date != test_start_date:
update_consumption_date(name.id, start_date)
#these are the active corps in namex and will be used to find the cnsumed names that shoudl be set to historical
#any names that dont match to this list shoudl be set to historical at the request level.
insert_active_corps_list(corp_num, corp_name, name.nr_id,
name.id)
db.session.commit()
return row_count
except Exception as err:
current_app.logger.error('Update Failed:', err.with_traceback(None))
return -1
def job(app, db, nro_connection, max_rows=100):
row_count = 0
try:
results = pg_job_results(max_rows)
ora_con = nro_connection
for r in results:
row_count += 1
if row_count > max_rows:
return row_count
corp_num = r.corp_num
corp_name = r.corp_name.strip()
corp_name = corp_name.replace('\'', "''")
active_corp, col_names = get_active_corp_info(ora_con, corp_num)
for a in active_corp:
row = ora_row_to_dict(col_names, a)
start_date = row['start_date']
name_count = find_corp_name_count_in_namex(corp_name)
if name_count == 0:
#update skip='D' for Datafix
skip_value = 'D'
update_missing_corps_list(corp_num, skip_value)
else:
name_results = find_corp_name_in_namex(corp_name)
for name in name_results:
update_consumption_info(name.id, corp_num, start_date)
insert_active_corps_list(corp_num, corp_name, name.nr_id,
name.id)
if name_count == 1:
skip_value = 'A' #added to active_corp_list
else:
skip_value = 'M' #multiple names because of bad datq in the colin nr filing table worng corp_num and name
update_missing_corps_list(corp_num, skip_value)
db.session.commit()
return row_count
except Exception as err:
current_app.logger.error('Update Failed:', err.with_traceback(None))
return -1
def get_nr_submitter(session, request_id):
# get the NR Submitter
#############################
sql = (
'select SYS_EXTRACT_UTC (cast(SUBMITTED_DATE as timestamp)) as SUBMITTED_DATE,'
' submitter'
' from submitter_vw'
' where request_id = :req_id')
result = session.execute(sql, req_id=request_id)
row = result.fetchone()
if row:
col_names = [row[0] for row in session.description]
return ora_row_to_dict(col_names, row)
return None
def get_nr_submitter(session, request_id):
# get the NR Submitter
#############################
sql = (
'select submitted_date,'
' submitter'
' from submitter_vw'
' where request_id = :req_id'
)
result = session.execute(sql, req_id=request_id)
row = result.fetchone()
if row:
col_names = [row[0] for row in session.description]
return ora_row_to_dict(col_names, row)
return None
def get_exam_comments(session, request_id):
# get the NR Requester
#############################
sql = (
'select examiner_IDIR,'
' examiner_comment,'
' state_comment,'
' SYS_EXTRACT_UTC (cast(event_timestamp as timestamp)) as event_timestamp'
' from examiner_comments_vw'
' where request_id= :req_id')
result = session.execute(sql, req_id=request_id)
col_names = [row[0] for row in session.description]
ex_comments = []
for row in result:
if row[1] or row[2]:
ex_comments.append(ora_row_to_dict(col_names, row))
if len(ex_comments) < 1:
return None
return ex_comments
def get_nwpta(session, request_id):
# get the NR NWPTA Partner information
#############################
sql = 'select partner_name_type_cd,' \
' partner_name_number,' \
' partner_jurisdiction_type_cd,' \
' partner_name_date,' \
' partner_name,' \
' last_update_id' \
' from partner_name_system_vw pns' \
' where end_event_id IS NULL' \
' and pns.request_id= :req_id'
result = session.execute(sql, req_id=request_id)
col_names = [row[0] for row in session.description]
nwpta = []
for row in result:
nwpta.append(ora_row_to_dict(col_names, row))
if len(nwpta) < 1:
return None
return nwpta
def job(app, namex_db, nro_connection, user, max_rows=100):
"""Process the NRs that have been updated in the NamesDB.
Most updates will go away as NRO (the legacy UI for the NamesDB) is decommissioned.
The following states allow the following changes:
- all changes allowed: DRAFT, PENDING_PAYMENT
- no changes allowed: INPROGRESS, REFUND_REQUESTED, REJECTED, EXPIRED, HISTORICAL, COMPLETED
- set cancelled state: CANCELLED
- all changes, except for state: HOLD
- consumed info only: RESERVED, COND_RESERVE, APPROVED, CONDITIONAL
"""
row_count = 0
try:
ora_con = nro_connection
# get the NRs from Oracle NamesDB of interest
result, col_names = job_result_set(ora_con, max_rows)
for r in result:
row_count += 1
row = ora_row_to_dict(col_names, r)
nr_num = row['nr_num']
nr = Request.find_by_nr(nr_num)
action = row['action']
current_app.logger.debug(
'processing: {}, NameX state: {}, action: {}'.format(
nr_num, None if (not nr) else nr.stateCd, action))
# NO CHANGES ALLOWED
if nr and (nr.stateCd in [
State.INPROGRESS, State.REFUND_REQUESTED, State.REJECTED,
State.EXPIRED, State.HISTORICAL, State.COMPLETED
]):
success = update_feeder_row(
ora_con,
row_id=row['id'],
status='C',
send_count=1 + 0 if
(row['send_count'] is None) else row['send_count'],
error_message='Ignored - Request: not processed')
ora_con.commit()
# continue to next row
current_app.logger.info(
'skipping: {}, NameX state: {}, action: {}'.format(
nr_num, None if (not nr) else nr.stateCd, action))
continue
# ignore existing NRs not in a completed state or draft, or in a completed state and not furnished
if nr and (
nr.stateCd not in State.COMPLETED_STATE + [State.DRAFT] or
(nr.stateCd in State.COMPLETED_STATE and nr.furnished == 'N')):
success = update_feeder_row(
ora_con,
row_id=row['id'],
status='C',
send_count=1 + 0 if
(row['send_count'] is None) else row['send_count'],
error_message='Ignored - Request: not processed')
ora_con.commit()
continue
# for any NRs in a completed state or new NRs not existing in NameX
else: # pylint: disable=R1724: Unnecessary "else"
try:
# get submitter
ora_cursor = ora_con.cursor()
nr_header = get_nr_header(ora_cursor, nr_num)
nr_submitter = get_nr_submitter(ora_cursor,
nr_header['request_id'])
# get pending payments
pending_payments = []
if nr:
pending_payments = [
x for x in nr.payments.all()
if x.payment_status_code ==
PaymentStatusCode.CREATED.value
]
# ignore if:
# - NR does not exist and NR originated in namex (handles racetime condition for when it is still in the process of saving)
# - NR has a pending update from namex (pending payment)
if (not nr and nr_submitter
and nr_submitter.get('submitter', '')
== 'namex') or (nr and len(pending_payments) > 0):
success = update_feeder_row(
ora_con,
row_id=row['id'],
status='C',
send_count=1 + 0 if
(row['send_count'] is None) else row['send_count'],
error_message='Ignored - Request: not processed')
ora_con.commit()
else:
nr = nro.fetch_nro_request_and_copy_to_namex_request(
user, nr_number=nr_num, name_request=nr)
namex_db.session.add(nr)
EventRecorder.record(user,
Event.UPDATE_FROM_NRO,
nr,
nr.json(),
save_to_session=True)
current_app.logger.debug(
'EventRecorder should have been saved to by now, although not committed'
)
success = update_feeder_row(
ora_con,
row_id=row['id'],
status='C',
send_count=1 + 0 if
(row['send_count'] is None) else row['send_count'],
error_message=None)
if success:
ora_con.commit()
current_app.logger.debug('Oracle commit done')
namex_db.session.commit()
current_app.logger.debug('Postgresql commit done')
else:
raise Exception()
except Exception as err:
current_app.logger.error(err.with_traceback(None))
success = update_feeder_row(
ora_con,
row_id=row['id'],
status=row['status'],
send_count=1 + 0 if
(row['send_count'] is None) else row['send_count'],
error_message=err.with_traceback(None))
namex_db.session.rollback()
ora_con.commit()
return row_count
except Exception as err:
current_app.logger.error('Update Failed:', err.with_traceback(None))
return -1
def job(app, namex_db, nro_connection, user, max_rows=100):
row_count = 0
try:
ora_con = nro_connection
result, col_names = job_result_set(ora_con, max_rows)
for r in result:
row_count += 1
row = ora_row_to_dict(col_names, r)
nr_num = row['nr_num']
nr = Request.find_by_nr(nr_num)
action = row['action']
current_app.logger.debug(
'processing: {}, NameX state: {}, action: {}'.format(
nr_num, None if (not nr) else nr.stateCd, action))
if nr and (nr.stateCd != State.DRAFT):
if action != 'X':
success = update_feeder_row(
ora_con,
id=row['id'],
status='C',
send_count=1 + 0 if
(row['send_count'] is None) else row['send_count'],
error_message='Ignored - Request: not processed')
ora_con.commit()
continue
try:
nr = nro.fetch_nro_request_and_copy_to_namex_request(
user, nr_number=nr_num, name_request=nr)
namex_db.session.add(nr)
EventRecorder.record(user,
Event.UPDATE_FROM_NRO,
nr, {},
save_to_session=True)
success = update_feeder_row(
ora_con,
id=row['id'],
status='C',
send_count=1 + 0 if
(row['send_count'] is None) else row['send_count'],
error_message=None)
if success:
ora_con.commit()
namex_db.session.commit()
else:
raise Exception()
except Exception as err:
current_app.logger.error(err.with_traceback(None))
success = update_feeder_row(
ora_con,
id=row['id'],
status=row['status'],
send_count=1 + 0 if
(row['send_count'] is None) else row['send_count'],
error_message=err.with_traceback(None))
namex_db.session.rollback()
ora_con.commit()
return row_count
except Exception as err:
current_app.logger.error('Update Failed:', err.with_traceback(None))
return -1
def job(app, namex_db, nro_connection, user, max_rows=100):
row_count = 0
datafix_status = None
try:
ora_con = nro_connection
result, col_names = job_result_set(ora_con, max_rows)
for r in result:
row_count += 1
row = ora_row_to_dict(col_names, r)
#stuff from the datafix table (from namesp, CPRD)
nr_num = row['nr_num']
corp_num = row['corp_num']
corp_name = row['name']
#check to see if there are any
ora_con1 = nro_connection
name_count_results, col_ni_count = get_name_count(
ora_con1, corp_num, corp_name)
ni_count_row = ora_row_to_dict(col_ni_count, name_count_results)
#no mathcing name instnace rows
test = ni_count_row['occurence']
if test[0] == 0:
#current_app.logger.error(err.with_traceback(None))
success = update_datafix_row(ora_con,
id=row['id'],
nr_num=nr_num,
status='BAD')
else:
skipped = 0
#check for name_instance corp rows
ora_con2 = nro_connection
name_results, col_ni = get_name_instance_rows(
ora_con2, corp_num, corp_name)
for ni in name_results:
skipped = skipped + 1
ni_row = ora_row_to_dict(col_ni, ni)
if ni_row['nr_num'] != nr_num:
nr_num = ni_row['nr_num']
nr = Request.find_by_nr(nr_num)
if (nr is None and skipped < test[0]):
continue
current_app.logger.debug(
'processing: {}, NameX state: {}'.format(
nr_num, None if (not nr) else nr.stateCd[0:9]))
try:
nr = nro.fetch_nro_request_and_copy_to_namex_request(
user, nr_number=nr_num, name_request=nr)
nr._source = 'NRO'
nr.furnished = 'Y'
#for ones that are mistakenely set as HISTORICAL, set to APPROVED as this is an active corp
namex_db.session.add(nr)
EventRecorder.record(user,
Event.UPDATE_FROM_NRO,
nr, {},
save_to_session=True)
current_app.logger.debug(
'EventRecorder should have been saved to by now, although not committed'
)
datafix_status = None if (not nr) else nr.stateCd[0:9]
success = update_datafix_row(ora_con,
id=row['id'],
nr_num=nr_num,
status=datafix_status)
if success:
ora_con.commit()
current_app.logger.debug('Oracle commit done')
namex_db.session.commit()
current_app.logger.debug('Postgresql commit done')
else:
raise Exception()
except Exception as err:
current_app.logger.error(err.with_traceback(None))
success = update_datafix_row(ora_con,
id=row['id'],
nr_num=nr_num,
status='ERROR')
namex_db.session.rollback()
ora_con.commit()
return row_count
except Exception as err:
current_app.logger.error('Update Failed:', err.with_traceback(None))
return -1
def job(app, namex_db, nro_connection, user, max_rows=100):
row_count = 0
try:
ora_con = nro_connection
result, col_names = job_result_set(ora_con, max_rows)
for r in result:
row_count += 1
row = ora_row_to_dict(col_names, r)
nr_num = row['nr_num']
nr = Request.find_by_nr(nr_num)
action = row['action']
current_app.logger.debug('processing: {}, NameX state: {}, action: {}'
.format(
nr_num,
None if (not nr) else nr.stateCd,
action
))
if nr and (nr.stateCd != State.DRAFT):
# do NOT ignore updates of completed NRs, since those are CONSUME transactions -
# the only kind that gets into the namex_feeder table for completed NRs
if nr.stateCd in State.COMPLETED_STATE and action == 'U':
pass
elif action != 'X':
success = update_feeder_row(ora_con
,id=row['id']
,status='C'
,send_count=1 + 0 if (row['send_count'] is None) else row['send_count']
, error_message='Ignored - Request: not processed')
ora_con.commit()
continue
try:
nr = nro.fetch_nro_request_and_copy_to_namex_request(user, nr_number=nr_num, name_request=nr)
namex_db.session.add(nr)
EventRecorder.record(user, Event.UPDATE_FROM_NRO, nr, {}, save_to_session=True)
current_app.logger.debug('EventRecorder should have been saved to by now, although not committed')
success = update_feeder_row(ora_con
, id=row['id']
, status='C'
, send_count=1 + 0 if (row['send_count'] is None) else row['send_count']
, error_message=None)
if success:
ora_con.commit()
current_app.logger.debug('Oracle commit done')
namex_db.session.commit()
current_app.logger.debug('Postgresql commit done')
else:
raise Exception()
except Exception as err:
current_app.logger.error(err.with_traceback(None))
success = update_feeder_row(ora_con
, id=row['id']
, status=row['status']
, send_count=1 + 0 if (row['send_count'] is None) else row['send_count']
, error_message=err.with_traceback(None))
namex_db.session.rollback()
ora_con.commit()
return row_count
except Exception as err:
current_app.logger.error('Update Failed:', err.with_traceback(None))
return -1
