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