def test_dev_corp_sql():
specific_corps = [
#'0574972',
'0657791'
]
with BCRegistries(True) as bc_registries:
for corp in specific_corps:
#print('=========================')
print('===== ', corp)
bc_registries.cache_bcreg_corp_tables([corp], True)
sqls = bc_registries.generated_sqls
fake_corp_num = bc_registries.add_generated_corp_num(corp)
print('=============>>> ', fake_corp_num)
#print('=========================')
#print('sqls:')
#for sql in sqls:
# print('"""' + sql.replace(' values ', '\nvalues\n') + '""",')
#print('=========================')
with BCRegistries(True) as cached_bc_reg:
cached_bc_reg.cache_bcreg_code_tables()
cached_bc_reg.insert_cache_sqls(sqls)
# try running with dummy event id zero
corp_info = cached_bc_reg.get_bc_reg_corp_info(
fake_corp_num, 0)
#print('-------------------------')
#print('corp_info:')
#print(corp_info)
#print('-------------------------')
with EventProcessor() as event_processor:
corp_creds = event_processor.generate_credentials(
system_type, 0, 0, fake_corp_num, corp_info)
def test_generate_corp_sql():
specific_corps = [
'A0059733','A0040189','A0059933','A0060938','A0060045',
]
with BCRegistries(True) as bc_registries:
for corp in specific_corps:
#print('=========================')
print('===== ', corp)
bc_registries.cache_bcreg_corp_tables([corp], True)
sqls = bc_registries.generated_sqls
fake_corp_num = bc_registries.add_generated_corp_num(corp)
print('=============>>> ', fake_corp_num)
#print('=========================')
#print('sqls:')
#for sql in sqls:
# print('"""' + sql.replace(' values ', '\nvalues\n') + '""",')
#print('=========================')
with BCRegistries(True) as cached_bc_reg:
cached_bc_reg.cache_bcreg_code_tables()
cached_bc_reg.insert_cache_sqls(sqls)
# try running with dummy event id zero
corp_info = cached_bc_reg.get_bc_reg_corp_info(fake_corp_num)
#print('-------------------------')
#print('corp_info:')
#print(corp_info)
#print('-------------------------')
start_event = {'event_id':0, 'event_date':MIN_START_DATE}
end_event = {'event_id':9999999999, 'event_date':MAX_END_DATE}
with EventProcessor() as event_processor:
corp_creds = event_processor.generate_credentials(system_type, start_event, end_event, fake_corp_num, corp_info)
def _test_dev_corp_sql2():
specific_corps = [
'A0073185',
]
# try caching everything
#with BCRegistries(True) as bc_registries:
# bc_registries.cache_bcreg_corps(specific_corps)
#def test_dev_corp_sql():
# specific_corps = [
# 'C1033288','C1043095'
# ]
# go one-by-one
with BCRegistries(True) as bc_registries:
for corp in specific_corps:
#print('=========================')
print('===== ', corp)
bc_registries.cache_bcreg_corp_tables([corp], True)
sqls = bc_registries.generated_sqls
fake_corp_num = bc_registries.add_generated_corp_num(corp)
print('=============>>> ', fake_corp_num)
#print('=========================')
#print('sqls:')
#for sql in sqls:
# print('"""' + sql.replace(' values ', '\nvalues\n') + '""",')
#print('=========================')
start_event = {'event_id': 0, 'event_date': MIN_START_DATE}
end_event = {'event_id': 9999999999, 'event_date': MAX_END_DATE}
with BCRegistries(True) as cached_bc_reg:
cached_bc_reg.cache_bcreg_code_tables()
cached_bc_reg.insert_cache_sqls(sqls)
# try running with dummy event id zero
corp_info = cached_bc_reg.get_bc_reg_corp_info(fake_corp_num)
print('-------------------------')
print('corp_info:')
print(corp_info)
print('-------------------------')
with EventProcessor() as event_processor:
corp_creds = event_processor.generate_credentials(
system_type, start_event, end_event, fake_corp_num,
corp_info)
print('-------------------------')
print('corp_creds:')
print(corp_creds)
print('-------------------------')
def test_compare_corp_events():
specific_corps = [
'0641655',
'0700450',
'0803224',
'LLC0000192',
'C0277609',
'A0072972',
'A0051862',
'C0874156',
'0874244',
'A0068919',
'A0064760',
'LLC0000234',
'A0077118',
'A0062459',
'0708325',
'0679026',
'0707774',
'C0874057',
'A0028374',
'A0053381',
'A0051632',
'A0032100',
'0874088',
'0803207',
]
with BCRegistries(False) as bc_registries:
prev_event_date = MIN_START_DATE
prev_event_id = 0
max_event_date = bc_registries.get_max_event_date()
max_event_id = bc_registries.get_max_event(max_event_date)
baseline_corps = bc_registries.get_specific_corps(specific_corps)
baseline_corps = bc_registries.get_unprocessed_corp_events(
prev_event_id, prev_event_date, max_event_id, max_event_date,
baseline_corps)
with BCRegistries(True) as bc_registries:
prev_event_date = MIN_START_DATE
prev_event_id = 0
# run these queries, but reuse the above "max" event so the calculations will all match
_foo_max_event_date = bc_registries.get_max_event_date()
_foo_max_event_id = bc_registries.get_max_event(max_event_date)
corps = bc_registries.get_specific_corps(specific_corps)
corps = bc_registries.get_unprocessed_corp_events(
prev_event_id, prev_event_date, max_event_id, max_event_date,
corps)
assert len(baseline_corps) == len(corps)
assert baseline_corps == corps
def test_dev_corp_sql1():
specific_corps = [
'FM0128548',
]
# try caching everything
#with BCRegistries(True) as bc_registries:
# bc_registries.cache_bcreg_corps(specific_corps)
#def test_dev_corp_sql():
# specific_corps = [
# 'C1033288','C1043095'
# ]
# go one-by-one
with BCRegistries() as bc_registries:
for corp in specific_corps:
start_event = {'event_id': 0, 'event_date': MIN_START_DATE}
end_event = {'event_id': 9999999999, 'event_date': MAX_END_DATE}
# try running with dummy event id zero
corp_info = bc_registries.get_bc_reg_corp_info(corp)
print('-------------------------')
print('corp_info:')
print(corp_info)
print('-------------------------')
with EventProcessor() as event_processor:
corp_creds = event_processor.generate_credentials(
system_type, start_event, end_event, corp, corp_info)
print('-------------------------')
print('corp_creds:')
print(corp_creds)
print('-------------------------')
def _generate_corp_nums():
print(
'corp_num,corp_type,corp_state,party_ct,party_addr_ct,name_ct,name_assumed_ct,name_trans_ct,tilma_ct,jurisdiction_ct'
)
with BCRegistries(False) as bc_registries:
corp_rows = bc_registries.get_bcreg_sql('event', select_corp_sql)
for corp in corp_rows:
party_ct = bc_registries.get_bcreg_sql(
'corp_party', run_corp_sql(corp['corp_num'],
select_party_recs))
party_addr_ct = bc_registries.get_bcreg_sql(
'office', run_corp_sql(corp['corp_num'],
select_party_addr_recs))
name_ct = bc_registries.get_bcreg_sql(
'name', run_corp_sql(corp['corp_num'], select_name_recs))
name_assumed_ct = bc_registries.get_bcreg_sql(
'name', run_corp_sql(corp['corp_num'],
select_name_assumed_recs))
name_trans_ct = bc_registries.get_bcreg_sql(
'name', run_corp_sql(corp['corp_num'], select_name_trans_recs))
tilma_ct = bc_registries.get_bcreg_sql(
'tilma_involved',
run_corp_sql(corp['corp_num'], select_tilma_recs))
juisdiction_ct = bc_registries.get_bcreg_sql(
'jurisdiction',
run_corp_sql(corp['corp_num'], select_jurisdiction_recs))
print(corp['corp_num'] + ', ' + corp['corp_typ_cd'] + ', ' +
corp['state_typ_cd'] + ', ' + str(party_ct[0]['count']) +
', ' + str(party_addr_ct[0]['count']) + ', ' +
str(name_ct[0]['count']) + ', ' +
str(name_assumed_ct[0]['count']) + ', ' +
str(name_trans_ct[0]['count']) + ', ' +
str(tilma_ct[0]['count']) + ', ' +
str(juisdiction_ct[0]['count']) + ', ')
def add_missing_corps_to_queue(specific_corps):
system_type = 'BC_REG'
with BCRegistries() as bc_registries:
with EventProcessor() as event_processor:
print("Get last processed event")
prev_event_id = 0
print("Get last max event")
max_event_date = event_processor.get_last_processed_event_date(
system_type)
max_event_id = event_processor.get_last_processed_event(
max_event_date, system_type)
# get specific test corps (there are about 6)
print("Get specific corps")
corps = bc_registries.get_specific_corps(specific_corps)
print("Find unprocessed events for each corp")
last_event_dt = bc_registries.get_event_effective_date(
prev_event_id)
max_event_dt = bc_registries.get_event_effective_date(max_event_id)
corps = bc_registries.get_unprocessed_corp_events(
prev_event_id, last_event_dt, max_event_id, max_event_dt,
corps)
print("Update our queue")
event_processor.update_corp_event_queue(system_type, corps,
max_event_id,
max_event_date)
def test_preset_corp_scenario_all():
for test_corp in sample_test_corps.keys():
corp_num = sample_test_corps[test_corp]['corp_num']
corp_sqls = sample_test_corps[test_corp]['sqls']
with BCRegistries(True) as cached_bc_reg:
cached_bc_reg.cache_bcreg_code_tables()
cached_bc_reg.insert_cache_sqls(corp_sqls)
corp_info = cached_bc_reg.get_bc_reg_corp_info(corp_num)
start_event = {'event_id':0, 'event_date':MIN_START_DATE}
end_event = {'event_id':9999999999, 'event_date':MAX_END_DATE}
with EventProcessor() as event_processor:
corp_creds = event_processor.generate_credentials(system_type, start_event, end_event, corp_num, corp_info)
def test_compare_corp_events():
specific_corps = [
'0641655',
#'0820416',
'0700450',
'0803224',
'LLC0000192',
'C0277609',
'A0072972',
'A0051862',
'C0874156',
'0874244',
'0593707',
'A0068919',
'A0064760',
'LLC0000234',
'A0077118',
'A0062459',
'0708325',
'0679026',
'0707774',
'C0874057',
'A0028374',
'A0053381',
'A0051632',
'0578221',
'A0032100',
'0874088',
'0803207',
#'0873646',
]
with BCRegistriesBaseline() as bc_registries:
prev_event_id = 0
max_event_id = bc_registries.get_max_event()
baseline_corps = bc_registries.get_specific_corps(specific_corps)
baseline_corps = bc_registries.get_unprocessed_corp_events(
prev_event_id, max_event_id, baseline_corps)
with BCRegistries() as bc_registries:
prev_event_id = 0
max_event_id = bc_registries.get_max_event()
corps = bc_registries.get_specific_corps(specific_corps)
corps = bc_registries.get_unprocessed_corp_events(
prev_event_id, max_event_id, corps)
assert len(baseline_corps) == len(corps)
assert baseline_corps == corps
def generate_creds_for_corp(corp_dict):
corp_num = corp_dict['corp_num']
corp_sqls = corp_dict['sqls']
with BCRegistries(True) as cached_bc_reg:
cached_bc_reg.cache_bcreg_code_tables()
cached_bc_reg.insert_cache_sqls(corp_sqls)
corp_info = cached_bc_reg.get_bc_reg_corp_info(corp_num)
start_event = {'event_id':0, 'event_date':MIN_START_DATE}
end_event = {'event_id':9999999999, 'event_date':MAX_END_DATE}
with EventProcessor() as event_processor:
corp_creds = event_processor.generate_credentials(system_type, start_event, end_event, corp_num, corp_info)
#print("Corp: " + corp_num + " generated " + str(len(corp_creds)) + " credentials")
#print(json.dumps(corp_creds, cls=DateTimeEncoder, indent=4, sort_keys=True))
return corp_creds
def generate_info_for_corp(corp_dict):
corp_num = corp_dict['corp_num']
corp_sqls = corp_dict['sqls']
with BCRegistries(True) as cached_bc_reg:
cached_bc_reg.cache_bcreg_code_tables()
cached_bc_reg.insert_cache_sqls(corp_sqls)
corp_info = cached_bc_reg.get_bc_reg_corp_info(corp_num)
#print("Corp: " + corp_num + " loaded.")
#print(json.dumps(corp_info, cls=CustomJsonEncoder, default=str, indent=4, sort_keys=True))
#for corp_state in corp_info['corp_state']:
# print(corp_state['corp_num'], corp_state['start_event']['event_id'],
# corp_state['end_event']['event_id'] if 'end_event' in corp_state else 0,
# corp_state['effective_start_date'], corp_state['effective_end_date'],
# corp_state['state_typ_cd'], corp_state['op_state_typ_cd'], corp_state['corp_state_effective_event']['event_typ_cd'],
# corp_state['start_event']['event_typ_cd'], corp_state['start_event']['effective_date'],
# corp_state['end_event']['effective_date'] if 'end_event' in corp_state else '')
return corp_info
def test_preset_corp_scenario_3dbas():
# use corp corp_A5589691
corp_num = sample_test_corps['corp_A5589691']['corp_num']
corp_sqls = sample_test_corps['corp_A5589691']['sqls']
with BCRegistries(True) as cached_bc_reg:
cached_bc_reg.cache_bcreg_code_tables()
cached_bc_reg.insert_cache_sqls(corp_sqls)
corp_info = cached_bc_reg.get_bc_reg_corp_info(corp_num)
start_event = {'event_id':0, 'event_date':MIN_START_DATE}
end_event = {'event_id':9999999999, 'event_date':MAX_END_DATE}
with EventProcessor() as event_processor:
corp_creds = event_processor.generate_credentials(system_type, start_event, end_event, corp_num, corp_info)
#print(corp_creds)
assert len(corp_creds) == 6
assert corp_creds[1]['cred_type'] == 'REG'
assert corp_creds[2]['cred_type'] == 'ADDR'
assert corp_creds[3]['cred_type'] == 'REL'
assert corp_creds[4]['cred_type'] == 'REL'
assert corp_creds[5]['cred_type'] == 'REL'
def test_compare_corp_infos():
specific_corps = [
'0641655',
#'0820416',
'0700450',
'0803224',
'LLC0000192',
'C0277609',
'A0072972',
'A0051862',
'C0874156',
'0874244',
'0593707',
'A0068919',
'A0064760',
'LLC0000234',
'A0077118',
'A0062459',
'0708325',
'0679026',
'0707774',
'C0874057',
'A0028374',
'A0053381',
'A0051632',
'0578221',
'A0032100',
'0874088',
'0803207',
#'0873646',
]
with BCRegistries(True) as bc_registries:
prev_event_date = MIN_START_DATE
prev_event_id = 0
max_event_date = bc_registries.get_max_event_date()
max_event_id = bc_registries.get_max_event(max_event_date)
corps = bc_registries.get_specific_corps(specific_corps)
corps = bc_registries.get_unprocessed_corp_events(
prev_event_id, prev_event_date, max_event_id, max_event_date,
corps)
with BCRegistries(False) as bc_registries:
prev_event_id = 0
# run these queries, but reuse the above "max" event so the calculations will all match
_foo_max_event_date = bc_registries.get_max_event_date()
_foo_max_event_id = bc_registries.get_max_event(max_event_date)
baseline_corps = bc_registries.get_specific_corps(specific_corps)
baseline_corps = bc_registries.get_unprocessed_corp_events(
prev_event_id, prev_event_date, max_event_id, max_event_date,
corps)
assert len(baseline_corps) == len(corps)
assert baseline_corps == corps
bcreg_loading_time = 0
bcreg_loading_time_baseline = 0
corp_info = {}
with BCRegistries(True) as bc_registries:
print('Load cached corp info')
start_time = time.perf_counter()
bc_registries.cache_bcreg_corps(specific_corps)
for corp in corps:
print(corp)
corp_info[corp['CORP_NUM']] = bc_registries.get_bc_reg_corp_info(
corp['CORP_NUM'])
bcreg_loading_time = bcreg_loading_time + (time.perf_counter() -
start_time)
print('new load:', bcreg_loading_time)
corp_info_baseline = {}
with BCRegistries(False) as bc_registries:
print('Run baseline')
start_time = time.perf_counter()
for corp in corps:
#print(corp)
corp_info_baseline[
corp['CORP_NUM']] = bc_registries.get_bc_reg_corp_info(
corp['CORP_NUM'])
bcreg_loading_time_baseline = bcreg_loading_time_baseline + (
time.perf_counter() - start_time)
print('baseline:', bcreg_loading_time_baseline, 'new load:',
bcreg_loading_time)
corp_creds = {}
with EventProcessor() as event_processor:
for corp in corps:
corp_creds[
corp['CORP_NUM']] = event_processor.generate_credentials(
system_type, corp['PREV_EVENT'], corp['LAST_EVENT'],
corp['CORP_NUM'], corp_info[corp['CORP_NUM']])
corp_creds_baseline = {}
with EventProcessor() as event_processor:
for corp in corps:
corp_creds_baseline[
corp['CORP_NUM']] = event_processor.generate_credentials(
system_type, corp['PREV_EVENT'], corp['LAST_EVENT'],
corp['CORP_NUM'], corp_info_baseline[corp['CORP_NUM']])
diffs = []
for corp in corps:
if corp_creds[corp['CORP_NUM']] != corp_creds_baseline[
corp['CORP_NUM']]:
diffs.append(corp['CORP_NUM'])
#print("==================================")
#print("==================================")
#print("CORP INFO:")
#print("==================================")
#print(json.dumps(corp_info[corp['CORP_NUM']], cls=CustomJsonEncoder, sort_keys=True))
#print("----------------------------------")
#print(json.dumps(corp_info_baseline[corp['CORP_NUM']], cls=CustomJsonEncoder, sort_keys=True))
#print("==================================")
#print("CORP CREDS:")
print("==================================")
print(
json.dumps(corp_creds[corp['CORP_NUM']],
cls=CustomJsonEncoder,
sort_keys=True))
print("----------------------------------")
print(
json.dumps(corp_creds_baseline[corp['CORP_NUM']],
cls=CustomJsonEncoder,
sort_keys=True))
print("==================================")
#print("==================================")
if 0 < len(diffs):
print(diffs)
assert 0 == len(diffs)
def test_connect_bcreg():
with BCRegistries(True) as bc_registries:
assert True
def test_connect_bcreg_baseline():
with BCRegistries(False) as bc_registries:
assert True
def test_compare_corp_infos():
specific_corps = [
'0641655',
#'0820416',
'0700450',
'0803224',
'LLC0000192',
'C0277609',
'A0072972',
'A0051862',
'C0874156',
'0874244',
'0593707',
'A0068919',
'A0064760',
'LLC0000234',
'A0077118',
'A0062459',
'0708325',
'0679026',
'0707774',
'C0874057',
'A0028374',
'A0053381',
'A0051632',
'0578221',
'A0032100',
'0874088',
'0803207',
#'0873646',
]
with BCRegistries() as bc_registries:
prev_event_id = 0
max_event_id = bc_registries.get_max_event()
corps = bc_registries.get_specific_corps(specific_corps)
corps = bc_registries.get_unprocessed_corp_events(
prev_event_id, max_event_id, corps)
with BCRegistriesBaseline() as bc_registries:
prev_event_id = 0
max_event_id = bc_registries.get_max_event()
baseline_corps = bc_registries.get_specific_corps(specific_corps)
baseline_corps = bc_registries.get_unprocessed_corp_events(
prev_event_id, max_event_id, baseline_corps)
assert len(baseline_corps) == len(corps)
assert baseline_corps == corps
bcreg_loading_time = 0
bcreg_loading_time_baseline = 0
corp_info = {}
with BCRegistries(True) as bc_registries:
print('Load cached corp info')
start_time = time.perf_counter()
bc_registries.cache_bcreg_corps(specific_corps)
for corp in corps:
print(corp)
corp_info[corp['CORP_NUM']] = bc_registries.get_bc_reg_corp_info(
corp['CORP_NUM'], corp['LAST_EVENT_ID'])
bcreg_loading_time = bcreg_loading_time + (time.perf_counter() -
start_time)
print('new load:', bcreg_loading_time)
corp_info_baseline = {}
with BCRegistriesBaseline() as bc_registries:
print('Run baseline')
start_time = time.perf_counter()
for corp in corps:
print(corp)
corp_info_baseline[
corp['CORP_NUM']] = bc_registries.get_bc_reg_corp_info(
corp['CORP_NUM'], corp['LAST_EVENT_ID'])
bcreg_loading_time_baseline = bcreg_loading_time_baseline + (
time.perf_counter() - start_time)
print('baseline:', bcreg_loading_time_baseline, 'new load:',
bcreg_loading_time)
#for corp in corps:
# assert corp_info[corp['CORP_NUM']] == corp_info_baseline[corp['CORP_NUM']]
corp_creds = {}
with EventProcessor() as event_processor:
for corp in corps:
corp_creds[
corp['CORP_NUM']] = event_processor.generate_credentials(
system_type, corp['PREV_EVENT_ID'], corp['LAST_EVENT_ID'],
corp['CORP_NUM'], corp_info[corp['CORP_NUM']])
corp_creds_baseline = {}
with EventProcessorBaseline() as event_processor:
for corp in corps:
corp_creds_baseline[
corp['CORP_NUM']] = event_processor.generate_credentials(
system_type, corp['PREV_EVENT_ID'], corp['LAST_EVENT_ID'],
corp['CORP_NUM'], corp_info_baseline[corp['CORP_NUM']])
diffs = []
for corp in corps:
if corp_creds[corp['CORP_NUM']] != corp_creds_baseline[
corp['CORP_NUM']]:
diffs.append(corp['CORP_NUM'])
print(diffs)
assert 0 == len(diffs)
def process_corp_event_queue_internal(self,
load_regs=True,
generate_creds=False,
use_cache=False):
sql1 = """SELECT RECORD_ID, SYSTEM_TYPE_CD, PREV_EVENT_ID, LAST_EVENT_ID, CORP_NUM, ENTRY_DATE
FROM EVENT_BY_CORP_FILING
WHERE PROCESS_DATE is null
LIMIT """ + str(CORP_BATCH_SIZE)
sql1a = """SELECT RECORD_ID, SYSTEM_TYPE_CD, PREV_EVENT_ID, LAST_EVENT_ID, CORP_NUM, CORP_JSON, ENTRY_DATE
FROM CORP_HISTORY_LOG
WHERE PROCESS_DATE is null
LIMIT """ + str(CORP_BATCH_SIZE)
sql2 = """INSERT INTO CORP_HISTORY_LOG (SYSTEM_TYPE_CD, PREV_EVENT_ID, LAST_EVENT_ID, CORP_NUM, CORP_STATE, CORP_JSON, ENTRY_DATE)
VALUES(%s, %s, %s, %s, %s, %s, %s) RETURNING RECORD_ID;"""
sql2a = """INSERT INTO CORP_HISTORY_LOG (SYSTEM_TYPE_CD, PREV_EVENT_ID, LAST_EVENT_ID, CORP_NUM, CORP_STATE, CORP_JSON, ENTRY_DATE, PROCESS_DATE)
VALUES(%s, %s, %s, %s, %s, %s, %s, %s) RETURNING RECORD_ID;"""
sql3 = """UPDATE EVENT_BY_CORP_FILING
SET PROCESS_DATE = %s
WHERE RECORD_ID = %s"""
sql3a = """UPDATE CORP_HISTORY_LOG
SET PROCESS_DATE = %s
WHERE RECORD_ID = %s"""
cur = None
try:
continue_loop = True
while continue_loop:
corps = []
specific_corps = []
# load data from BC Registries for the corporations we need to process (max of 3000 per chunk)
# this data may be pulled directly, or pulled from a "cache" in the event processor database
if load_regs:
# we are loading data from BC Registries based on the corp event queue
cur = self.conn.cursor()
cur.execute(sql1)
row = cur.fetchone()
while row is not None:
corps.append({
'RECORD_ID': row[0],
'SYSTEM_TYPE_CD': row[1],
'PREV_EVENT_ID': row[2],
'LAST_EVENT_ID': row[3],
'CORP_NUM': row[4],
'ENTRY_DATE': row[5]
})
specific_corps.append(row[4])
row = cur.fetchone()
cur.close()
cur = None
else:
# not loading from BC Reg, just processing data already loaded in corp_history
cur = self.conn.cursor()
cur.execute(sql1a)
row = cur.fetchone()
while row is not None:
# print(row)
corps.append({
'RECORD_ID': row[0],
'SYSTEM_TYPE_CD': row[1],
'PREV_EVENT_ID': row[2],
'LAST_EVENT_ID': row[3],
'CORP_NUM': row[4],
'CORP_JSON': row[5],
'ENTRY_DATE': row[6]
})
specific_corps.append(row[4])
row = cur.fetchone()
cur.close()
cur = None
if len(specific_corps) == 0:
continue_loop = False
else:
# now generate credentials from the corporate data
with BCRegistries(use_cache) as bc_registries:
if use_cache:
bc_registries.cache_bcreg_corps(specific_corps)
for i, corp in enumerate(corps):
print(
'>>> Processing {} of {} corporations.'.format(
i + 1, len(corps)))
if load_regs:
# fetch corp info from bc_registries
corp_info = bc_registries.get_bc_reg_corp_info(
corp['CORP_NUM'], corp['LAST_EVENT_ID'])
else:
# json blob is cached in event processor database
corp_info = corp['CORP_JSON']
if generate_creds:
# generate and store credentials
cur = self.conn.cursor()
corp_creds = self.generate_credentials(
corp['SYSTEM_TYPE_CD'],
corp['PREV_EVENT_ID'],
corp['LAST_EVENT_ID'], corp['CORP_NUM'],
corp_info)
self.store_credentials(
cur, corp['SYSTEM_TYPE_CD'],
corp['PREV_EVENT_ID'],
corp['LAST_EVENT_ID'], corp['CORP_NUM'],
corp_info['corp_state']['op_state_typ_cd'],
corp_info, corp_creds)
cur.close()
cur = None
# store corporate info
if load_regs:
cur = self.conn.cursor()
cur.execute(sql2a, (
corp['SYSTEM_TYPE_CD'],
corp['PREV_EVENT_ID'],
corp['LAST_EVENT_ID'],
corp['CORP_NUM'],
corp_info['corp_state']
['op_state_typ_cd'],
bc_registries.to_json(corp_info),
datetime.datetime.now(),
datetime.datetime.now(),
))
cur.close()
cur = None
else:
# update process date
cur = self.conn.cursor()
cur.execute(sql3a, (
datetime.datetime.now(),
corp['RECORD_ID'],
))
cur.close()
cur = None
elif load_regs:
# store corporate info for future generation of credentials
cur = self.conn.cursor()
cur.execute(sql2, (
corp['SYSTEM_TYPE_CD'],
corp['PREV_EVENT_ID'],
corp['LAST_EVENT_ID'],
corp['CORP_NUM'],
corp_info['corp_state']['op_state_typ_cd'],
bc_registries.to_json(corp_info),
datetime.datetime.now(),
))
cur.close()
cur = None
# update process date
cur = self.conn.cursor()
cur.execute(sql3, (
datetime.datetime.now(),
corp['RECORD_ID'],
))
self.conn.commit()
cur.close()
cur = None
except (Exception, psycopg2.DatabaseError) as error:
print(error)
raise
finally:
if cur is not None:
cur.close()
print('Cursor closed.')
def test_generate_corp_nums():
selected_corps = [
'0043687',
'0384698',
'0446323',
'0613083',
'0614205',
'0713939',
'0765995',
'0790280',
'0827071',
'0830501',
'0858892',
'0874259',
'0874260',
'A0031179',
'A0039015',
'A0053427',
'A0053864',
'A0057212',
'A0078116',
'C0499505',
'C0610814',
'C0874156',
'LLC0000221',
]
selected_corp_list = ''
i = 0
for corp in selected_corps:
selected_corp_list = selected_corp_list + "'" + corp + "'"
i = i + 1
if i < len(selected_corps):
selected_corp_list = selected_corp_list + ', '
with BCRegistries(False) as bc_registries:
sql = select_corp_scenario_sql.replace('%s', selected_corp_list)
corp_rows = bc_registries.get_bcreg_sql('event', sql)
for corp in corp_rows:
party_ct = bc_registries.get_bcreg_sql(
'corp_party', run_corp_sql(corp['corp_num'],
select_party_recs))
party_addr_ct = bc_registries.get_bcreg_sql(
'office', run_corp_sql(corp['corp_num'],
select_party_addr_recs))
name_ct = bc_registries.get_bcreg_sql(
'name', run_corp_sql(corp['corp_num'], select_name_recs))
name_assumed_ct = bc_registries.get_bcreg_sql(
'name', run_corp_sql(corp['corp_num'],
select_name_assumed_recs))
name_trans_ct = bc_registries.get_bcreg_sql(
'name', run_corp_sql(corp['corp_num'], select_name_trans_recs))
tilma_ct = bc_registries.get_bcreg_sql(
'tilma_involved',
run_corp_sql(corp['corp_num'], select_tilma_recs))
juisdiction_ct = bc_registries.get_bcreg_sql(
'jurisdiction',
run_corp_sql(corp['corp_num'], select_jurisdiction_recs))
with BCRegistries(True) as bc_cache_reg:
bc_cache_reg.cache_bcreg_corp_tables([corp['corp_num']], True)
sqls = bc_cache_reg.generated_sqls
fake_corp_num = bc_cache_reg.add_generated_corp_num(
corp['corp_num'])
header.append(key)
header_line = header_line + key
return (header, header_line)
def csv_line(header, corp):
line = ""
for key in header:
if len(line) > 0:
line = line + ','
line = line + corp[key]
return line
# load BC Registries with local cache enabled
with BCRegistries(True) as bc_registries:
print("Events", datetime.datetime.now())
parties = bc_registries.get_bcreg_sql("event",
"select * from bc_registries.event",
cache=True)
print("States", datetime.datetime.now())
parties = bc_registries.get_bcreg_sql(
"corp_state", "select * from bc_registries.corp_state", cache=True)
print("Fetch all incorrect events")
sql2 = """
SELECT *
from event, corp_state
where event.event_id = corp_state.start_event_id
and event.corp_num != corp_state.corp_num;
def test_generate_corp_sql():
specific_corps = [
'0641655',
#'0820416',
#'0700450',
#'0803224',
#'LLC0000192',
#'C0277609',
#'A0072972',
#'A0051862',
#'C0874156',
#'0874244',
#'0593707',
#'A0068919',
#'A0064760',
#'LLC0000234',
#'A0077118',
#'A0062459',
#'0708325',
#'0679026',
#'0707774',
#'C0874057',
#'A0028374',
#'A0053381',
#'A0051632',
#'0578221',
#'A0032100',
#'0874088',
#'0803207',
#'0873646',
]
with BCRegistries(True) as bc_registries:
for corp in specific_corps:
#print('=========================')
print('===== ', corp)
bc_registries.cache_bcreg_corp_tables([corp], True)
sqls = bc_registries.generated_sqls
fake_corp_num = bc_registries.add_generated_corp_num(corp)
print('=============>>> ', fake_corp_num)
#print('=========================')
#print('sqls:')
#for sql in sqls:
# print('"""' + sql.replace(' values ', '\nvalues\n') + '""",')
#print('=========================')
with BCRegistries(True) as cached_bc_reg:
cached_bc_reg.cache_bcreg_code_tables()
cached_bc_reg.insert_cache_sqls(sqls)
# try running with dummy event id zero
corp_info = cached_bc_reg.get_bc_reg_corp_info(
fake_corp_num, 0)
#print('-------------------------')
#print('corp_info:')
#print(corp_info)
#print('-------------------------')
with EventProcessor() as event_processor:
corp_creds = event_processor.generate_credentials(
system_type, 0, 0, fake_corp_num, corp_info)
def test_specific_corp_scenario():
corp_num = '6096127'
corp_sqls = [
"""create table if not exists corp_party (corp_party_id numeric, mailing_addr_id numeric, delivery_addr_id numeric, corp_num text, party_typ_cd text, start_event_id numeric, end_event_id numeric, prev_party_id numeric, corr_typ_cd text, last_report_dt timestamp, appointment_dt timestamp, cessation_dt timestamp, last_nme text, middle_nme text, first_nme text, business_nme text, bus_company_num text, email_address text, corp_party_seq_num numeric, office_notification_dt timestamp, phone text, reason_typ_cd text)""",
"""insert into corp_party (corp_party_id, mailing_addr_id, delivery_addr_id, corp_num, party_typ_cd, start_event_id, end_event_id, prev_party_id, corr_typ_cd, last_report_dt, appointment_dt, cessation_dt, last_nme, middle_nme, first_nme, business_nme, bus_company_num, email_address, corp_party_seq_num, office_notification_dt, phone, reason_typ_cd)
values
(105820465, null, null, 'FM8694883', 'FBO', 105039871, null, null, null, null, '1981-07-03 00:00:00', null, null, null, null, '0641655Y QDSSDFWICJOJESKXZ ', '6096127', null, null, null, null, null)""",
"""create table if not exists event (event_id numeric, corp_num text, event_typ_cd text, event_timestmp timestamp, trigger_dts timestamp)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(5511129, '6096127', 'CONVICORP', '2004-03-26 20:36:00', null)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(5511130, '6096127', 'FILE', '2004-03-10 00:00:00', null)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(5511131, '6096127', 'FILE', '2004-03-10 00:00:00', null)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(5511132, '6096127', 'FILE', '2002-02-06 00:00:00', null)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(6245683, '6096127', 'ADCORP', '2005-04-20 16:02:27', null)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(6245701, '6096127', 'FILE', '2005-04-20 16:06:43', null)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(7055682, '6096127', 'FILE', '2006-04-12 14:37:52', null)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(7549434, '6096127', 'FILE', '2007-03-05 09:00:12', null)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(7591037, '6096127', 'FILE', '2007-03-28 15:16:53', null)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(9141401, '6096127', 'FILE', '2009-12-15 16:51:57', null)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(6245023, '6096127', 'FILE', '2005-04-20 15:48:29', null)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(8257866, '6096127', 'FILE', '2008-05-08 12:16:30', null)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(8855073, '6096127', 'FILE', '2009-06-04 10:32:15', null)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(8855876, '6096127', 'FILE', '2009-06-04 15:02:00', null)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(8855913, '6096127', 'FILE', '2009-06-04 15:11:53', null)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(9235934, '6096127', 'FILE', '2012-02-03 11:53:59', null)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(9235935, '6096127', 'FILE', '2012-02-03 11:54:30', null)""",
"""insert into event (event_id, corp_num, event_typ_cd, event_timestmp, trigger_dts)
values
(105039871, 'FM8694883', 'CONVFMREGI', '1981-07-03 00:00:00', null)""",
"""create table if not exists filing (event_id numeric, filing_typ_cd text, effective_dt timestamp, change_dt timestamp, registration_dt timestamp, period_end_dt timestamp, accession_num text, arrangement_ind text, auth_sign_dt timestamp, withdrawn_event_id numeric, ods_typ_cd text, dd_event_id numeric, access_cd text, nr_num text, court_appr_ind text, court_order_num text, agm_date timestamp, new_corp_num text)""",
"""insert into filing (event_id, filing_typ_cd, effective_dt, change_dt, registration_dt, period_end_dt, accession_num, arrangement_ind, auth_sign_dt, withdrawn_event_id, ods_typ_cd, dd_event_id, access_cd, nr_num, court_appr_ind, court_order_num, agm_date, new_corp_num)
values
(5511130, 'CONVL', '2004-03-10 00:00:00', null, null, null, null, null, null, null, 'P ', null, null, null, null, null, null, null)""",
"""insert into filing (event_id, filing_typ_cd, effective_dt, change_dt, registration_dt, period_end_dt, accession_num, arrangement_ind, auth_sign_dt, withdrawn_event_id, ods_typ_cd, dd_event_id, access_cd, nr_num, court_appr_ind, court_order_num, agm_date, new_corp_num)
values
(5511131, 'CONVL', '2004-03-10 00:00:00', null, null, null, null, null, null, null, 'P ', null, null, null, null, null, null, null)""",
"""insert into filing (event_id, filing_typ_cd, effective_dt, change_dt, registration_dt, period_end_dt, accession_num, arrangement_ind, auth_sign_dt, withdrawn_event_id, ods_typ_cd, dd_event_id, access_cd, nr_num, court_appr_ind, court_order_num, agm_date, new_corp_num)
values
(5511132, 'CONVL', '2002-02-06 00:00:00', null, null, null, null, null, null, null, 'P ', null, null, null, null, null, null, null)""",
"""insert into filing (event_id, filing_typ_cd, effective_dt, change_dt, registration_dt, period_end_dt, accession_num, arrangement_ind, auth_sign_dt, withdrawn_event_id, ods_typ_cd, dd_event_id, access_cd, nr_num, court_appr_ind, court_order_num, agm_date, new_corp_num)
values
(6245023, 'ANNBC', '2005-04-20 15:48:29', null, null, '2005-02-06 00:00:00', null, 'N', null, null, 'F ', null, '102532462', null, null, null, null, null)""",
"""insert into filing (event_id, filing_typ_cd, effective_dt, change_dt, registration_dt, period_end_dt, accession_num, arrangement_ind, auth_sign_dt, withdrawn_event_id, ods_typ_cd, dd_event_id, access_cd, nr_num, court_appr_ind, court_order_num, agm_date, new_corp_num)
values
(6245701, 'TRANS', '2005-04-20 16:06:43', null, null, null, null, 'N', null, null, 'F ', 6245701, null, null, null, null, null, null)""",
"""insert into filing (event_id, filing_typ_cd, effective_dt, change_dt, registration_dt, period_end_dt, accession_num, arrangement_ind, auth_sign_dt, withdrawn_event_id, ods_typ_cd, dd_event_id, access_cd, nr_num, court_appr_ind, court_order_num, agm_date, new_corp_num)
values
(7055682, 'ANNBC', '2006-04-12 14:37:52', null, null, '2006-02-06 00:00:00', null, 'N', null, null, 'F ', null, '105437040', null, null, null, null, null)""",
"""insert into filing (event_id, filing_typ_cd, effective_dt, change_dt, registration_dt, period_end_dt, accession_num, arrangement_ind, auth_sign_dt, withdrawn_event_id, ods_typ_cd, dd_event_id, access_cd, nr_num, court_appr_ind, court_order_num, agm_date, new_corp_num)
values
(7549434, 'ANNBC', '2007-03-05 09:00:12', null, null, '2007-02-06 00:00:00', null, 'N', null, null, 'F ', null, '108353590', null, null, null, null, null)""",
"""insert into filing (event_id, filing_typ_cd, effective_dt, change_dt, registration_dt, period_end_dt, accession_num, arrangement_ind, auth_sign_dt, withdrawn_event_id, ods_typ_cd, dd_event_id, access_cd, nr_num, court_appr_ind, court_order_num, agm_date, new_corp_num)
values
(7591037, 'NOALA', '2007-03-28 15:16:53', null, null, null, null, 'N', null, null, 'F ', 7591037, null, 'NR6422424', null, null, null, null)""",
"""insert into filing (event_id, filing_typ_cd, effective_dt, change_dt, registration_dt, period_end_dt, accession_num, arrangement_ind, auth_sign_dt, withdrawn_event_id, ods_typ_cd, dd_event_id, access_cd, nr_num, court_appr_ind, court_order_num, agm_date, new_corp_num)
values
(8257866, 'ANNBC', '2008-05-08 12:16:30', null, null, '2008-02-06 00:00:00', null, 'N', null, null, 'F ', null, '111282232', null, null, null, null, null)""",
"""insert into filing (event_id, filing_typ_cd, effective_dt, change_dt, registration_dt, period_end_dt, accession_num, arrangement_ind, auth_sign_dt, withdrawn_event_id, ods_typ_cd, dd_event_id, access_cd, nr_num, court_appr_ind, court_order_num, agm_date, new_corp_num)
values
(8855073, 'ANNBC', '2009-06-04 10:32:15', null, null, '2009-02-06 00:00:00', null, 'N', null, null, 'F ', null, '114301161', null, null, null, null, null)""",
"""insert into filing (event_id, filing_typ_cd, effective_dt, change_dt, registration_dt, period_end_dt, accession_num, arrangement_ind, auth_sign_dt, withdrawn_event_id, ods_typ_cd, dd_event_id, access_cd, nr_num, court_appr_ind, court_order_num, agm_date, new_corp_num)
values
(8855876, 'NOALU', '2009-06-04 15:02:00', null, null, null, null, 'N', null, null, 'F ', 8855876, null, 'NR8796381', null, null, null, null)""",
"""insert into filing (event_id, filing_typ_cd, effective_dt, change_dt, registration_dt, period_end_dt, accession_num, arrangement_ind, auth_sign_dt, withdrawn_event_id, ods_typ_cd, dd_event_id, access_cd, nr_num, court_appr_ind, court_order_num, agm_date, new_corp_num)
values
(8855913, 'NOCDR', '2009-06-04 15:11:53', null, null, null, null, 'N', null, null, 'F ', null, null, null, null, null, null, null)""",
"""insert into filing (event_id, filing_typ_cd, effective_dt, change_dt, registration_dt, period_end_dt, accession_num, arrangement_ind, auth_sign_dt, withdrawn_event_id, ods_typ_cd, dd_event_id, access_cd, nr_num, court_appr_ind, court_order_num, agm_date, new_corp_num)
values
(9141401, 'NOCAD', '2009-12-16 00:01:00', null, null, null, null, 'N', null, null, 'F ', null, null, null, null, null, null, null)""",
"""insert into filing (event_id, filing_typ_cd, effective_dt, change_dt, registration_dt, period_end_dt, accession_num, arrangement_ind, auth_sign_dt, withdrawn_event_id, ods_typ_cd, dd_event_id, access_cd, nr_num, court_appr_ind, court_order_num, agm_date, new_corp_num)
values
(9235934, 'ANNBC', '2012-02-03 11:53:59', null, null, '2010-02-06 00:00:00', null, 'N', null, null, 'F ', null, '117411967', null, null, null, null, null)""",
"""insert into filing (event_id, filing_typ_cd, effective_dt, change_dt, registration_dt, period_end_dt, accession_num, arrangement_ind, auth_sign_dt, withdrawn_event_id, ods_typ_cd, dd_event_id, access_cd, nr_num, court_appr_ind, court_order_num, agm_date, new_corp_num)
values
(9235935, 'ANNBC', '2012-02-03 11:54:30', null, null, '2011-02-06 00:00:00', null, 'N', null, null, 'F ', null, null, null, null, null, null, null)""",
"""insert into filing (event_id, filing_typ_cd, effective_dt, change_dt, registration_dt, period_end_dt, accession_num, arrangement_ind, auth_sign_dt, withdrawn_event_id, ods_typ_cd, dd_event_id, access_cd, nr_num, court_appr_ind, court_order_num, agm_date, new_corp_num)
values
(105039871, 'FRREG', '1981-07-03 00:00:00', null, null, null, null, null, null, null, 'P ', null, null, null, null, null, null, null)""",
"""create table if not exists corporation (corp_num text, corp_frozen_typ_cd text, corp_typ_cd text, recognition_dts timestamp, last_ar_filed_dt timestamp, transition_dt timestamp, bn_9 text, bn_15 text, accession_num text, corp_password text, prompt_question text, admin_email text, send_ar_ind text, tilma_involved_ind text, tilma_cessation_dt timestamp, firm_last_image_date timestamp, os_session integer, last_agm_date timestamp, firm_lp_xp_termination_date timestamp, last_ledger_dt timestamp, ar_reminder_option text, ar_reminder_date text, temp_password text, temp_password_expiry_date timestamp)""",
"""insert into corporation (corp_num, corp_frozen_typ_cd, corp_typ_cd, recognition_dts, last_ar_filed_dt, transition_dt, bn_9, bn_15, accession_num, corp_password, prompt_question, admin_email, send_ar_ind, tilma_involved_ind, tilma_cessation_dt, firm_last_image_date, os_session, last_agm_date, firm_lp_xp_termination_date, last_ledger_dt, ar_reminder_option, ar_reminder_date, temp_password, temp_password_expiry_date)
values
('6096127', null, 'ULC', '2002-02-06 00:00:00', '2011-02-06 00:00:00', '2005-04-20 16:06:43', null, null, null, 'DXACRIXX', 'IKMQPBMP', null, 'N', 'N', null, null, null, null, null, null, null, null, null, null)""",
"""insert into corporation (corp_num, corp_frozen_typ_cd, corp_typ_cd, recognition_dts, last_ar_filed_dt, transition_dt, bn_9, bn_15, accession_num, corp_password, prompt_question, admin_email, send_ar_ind, tilma_involved_ind, tilma_cessation_dt, firm_last_image_date, os_session, last_agm_date, firm_lp_xp_termination_date, last_ledger_dt, ar_reminder_option, ar_reminder_date, temp_password, temp_password_expiry_date)
values
('FM8694883', null, 'SP', '1981-07-03 00:00:00', null, null, null, null, null, null, null, null, null, 'N', null, null, null, null, null, null, null, null, null, null)""",
"""create table if not exists conv_event (event_id numeric, effective_dt timestamp, report_corp_ind text, prev_bc_ind text, activity_user_id text, activity_dt timestamp, activity_tm timestamp, annual_file_dt timestamp, corp_cre_typ_cd text, accession_num text, dd_event_id numeric, remarks text)""",
"""create table if not exists corp_state (corp_num text, start_event_id numeric, end_event_id numeric, state_typ_cd text, dd_corp_num text)""",
"""insert into corp_state (corp_num, start_event_id, end_event_id, state_typ_cd, dd_corp_num)
values
('6096127', 5511129, null, 'ACT', null)""",
"""insert into corp_state (corp_num, start_event_id, end_event_id, state_typ_cd, dd_corp_num)
values
('FM8694883', 105039871, null, 'ACT', null)""",
"""create table if not exists tilma_involved (tilma_involved_id text, corp_num text, start_event_id text, end_event_id text, tilma_jurisdiction text, nuans_number text, nuans_expiry_date timestamp, nr_number text, jurisdiction_num text, jurisdiction_reg_date timestamp, can_number text, jurisdiction_assumed_name text, assumed_nuans_number text, assumed_nuans_name text, assumed_nuans_expiration_date timestamp, involved_ind text, cessation_date timestamp)""",
"""create table if not exists jurisdiction (corp_num text, start_event_id numeric, end_event_id numeric, dd_corp_num text, can_jur_typ_cd text, xpro_typ_cd text, home_recogn_dt timestamp, othr_juris_desc text, home_juris_num text, bc_xpro_num text, home_company_nme text, other_juris_party_id text)""",
"""create table if not exists corp_name (corp_num text, corp_name_typ_cd text, start_event_id numeric, corp_name_seq_num numeric, end_event_id numeric, srch_nme text, corp_nme text, dd_corp_num text)""",
"""insert into corp_name (corp_num, corp_name_typ_cd, start_event_id, corp_name_seq_num, end_event_id, srch_nme, corp_nme, dd_corp_num)
values
('6096127', 'CO', 8855876, 1, null, 'ZRNSHTSBJWOFBYGLINWP', 'QZITQDNKIDIZKUWBCR RHFJMH', null)""",
"""insert into corp_name (corp_num, corp_name_typ_cd, start_event_id, corp_name_seq_num, end_event_id, srch_nme, corp_nme, dd_corp_num)
values
('6096127', 'CO', 5511129, 0, 7591037, 'PYWAJCFUEPLUIRTCNQQB', 'OSZUOXJZVZSYKYTAADRNOPHPR', null)""",
"""insert into corp_name (corp_num, corp_name_typ_cd, start_event_id, corp_name_seq_num, end_event_id, srch_nme, corp_nme, dd_corp_num)
values
('6096127', 'CO', 7591037, 1, 8855876, 'SXDEUETULEXXDMCYWTYK', 'BTNLFMWTESSLUJCWYI GBLQAY', null)""",
"""insert into corp_name (corp_num, corp_name_typ_cd, start_event_id, corp_name_seq_num, end_event_id, srch_nme, corp_nme, dd_corp_num)
values
('FM8694883', 'CO', 105039871, 0, null, 'TPTPYBIICTYEVFMKKPCF', 'FLOHTOGCRGKGIPWBZHDUZLFZC', null)""",
"""create table if not exists office (corp_num text, office_typ_cd text, start_event_id numeric, end_event_id numeric, mailing_addr_id numeric, delivery_addr_id numeric, dd_corp_num text, email_address text)""",
"""insert into office (corp_num, office_typ_cd, start_event_id, end_event_id, mailing_addr_id, delivery_addr_id, dd_corp_num, email_address)
values
('6096127', 'RC', 6245701, 9141401, 2836997, 2836996, null, null)""",
"""insert into office (corp_num, office_typ_cd, start_event_id, end_event_id, mailing_addr_id, delivery_addr_id, dd_corp_num, email_address)
values
('6096127', 'RG', 6245701, 9141401, 2836999, 2836998, null, null)""",
"""insert into office (corp_num, office_typ_cd, start_event_id, end_event_id, mailing_addr_id, delivery_addr_id, dd_corp_num, email_address)
values
('6096127', 'RG', 5511129, 6245701, 1606746, 1606746, null, null)""",
"""insert into office (corp_num, office_typ_cd, start_event_id, end_event_id, mailing_addr_id, delivery_addr_id, dd_corp_num, email_address)
values
('6096127', 'RC', 5511129, 6245701, 1606746, 1606746, null, null)""",
"""insert into office (corp_num, office_typ_cd, start_event_id, end_event_id, mailing_addr_id, delivery_addr_id, dd_corp_num, email_address)
values
('6096127', 'RG', 9141401, null, 7404645, 7404644, null, null)""",
"""insert into office (corp_num, office_typ_cd, start_event_id, end_event_id, mailing_addr_id, delivery_addr_id, dd_corp_num, email_address)
values
('6096127', 'RC', 9141401, null, 7404643, 7404642, null, null)""",
"""create table if not exists address (addr_id numeric, province text, country_typ_cd text, postal_cd text, addr_line_1 text, addr_line_2 text, addr_line_3 text, city text, address_format_type text, address_desc text, address_desc_short text, delivery_instructions text, unit_no text, unit_type text, civic_no text, civic_no_suffix text, street_name text, street_type text, street_direction text, lock_box_no text, installation_type text, installation_name text, installation_qualifier text, route_service_type text, route_service_no text, province_state_name text)""",
"""insert into address (addr_id, province, country_typ_cd, postal_cd, addr_line_1, addr_line_2, addr_line_3, city, address_format_type, address_desc, address_desc_short, delivery_instructions, unit_no, unit_type, civic_no, civic_no_suffix, street_name, street_type, street_direction, lock_box_no, installation_type, installation_name, installation_qualifier, route_service_type, route_service_no, province_state_name)
values
(1606746, 'BC', 'CA', 'MHIFWS', 'JYFSNUJMIOZINBMIQYCUDUSAR', 'BAMEPDCRMR YDZWOJPGVFWLTJ', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)""",
"""insert into address (addr_id, province, country_typ_cd, postal_cd, addr_line_1, addr_line_2, addr_line_3, city, address_format_type, address_desc, address_desc_short, delivery_instructions, unit_no, unit_type, civic_no, civic_no_suffix, street_name, street_type, street_direction, lock_box_no, installation_type, installation_name, installation_qualifier, route_service_type, route_service_no, province_state_name)
values
(2836996, 'BC', 'CA', '5JFPB2', 'VPDBKQIF XSDDIFT NLPEIOMV', null, null, 'VANCOUVER', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)""",
"""insert into address (addr_id, province, country_typ_cd, postal_cd, addr_line_1, addr_line_2, addr_line_3, city, address_format_type, address_desc, address_desc_short, delivery_instructions, unit_no, unit_type, civic_no, civic_no_suffix, street_name, street_type, street_direction, lock_box_no, installation_type, installation_name, installation_qualifier, route_service_type, route_service_no, province_state_name)
values
(2836997, 'BC', 'CA', 'A645UW', 'PNIVFCTTGZSTCNCLSUFCHG ZK', null, null, 'VANCOUVER', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)""",
"""insert into address (addr_id, province, country_typ_cd, postal_cd, addr_line_1, addr_line_2, addr_line_3, city, address_format_type, address_desc, address_desc_short, delivery_instructions, unit_no, unit_type, civic_no, civic_no_suffix, street_name, street_type, street_direction, lock_box_no, installation_type, installation_name, installation_qualifier, route_service_type, route_service_no, province_state_name)
values
(2836998, 'BC', 'CA', 'TNOGDH', ' WCYXRKOAAPNTOKSB WFYGGEV', null, null, 'VANCOUVER', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)""",
"""insert into address (addr_id, province, country_typ_cd, postal_cd, addr_line_1, addr_line_2, addr_line_3, city, address_format_type, address_desc, address_desc_short, delivery_instructions, unit_no, unit_type, civic_no, civic_no_suffix, street_name, street_type, street_direction, lock_box_no, installation_type, installation_name, installation_qualifier, route_service_type, route_service_no, province_state_name)
values
(2836999, 'BC', 'CA', '4OK4EI', ' HELE CXXVVWCNATIDDBXAEEU', null, null, 'VANCOUVER', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)""",
"""insert into address (addr_id, province, country_typ_cd, postal_cd, addr_line_1, addr_line_2, addr_line_3, city, address_format_type, address_desc, address_desc_short, delivery_instructions, unit_no, unit_type, civic_no, civic_no_suffix, street_name, street_type, street_direction, lock_box_no, installation_type, installation_name, installation_qualifier, route_service_type, route_service_no, province_state_name)
values
(7404642, 'BC', 'CA', 'VSX7G8', 'AADWCOLUFJSASZQBFCWMOHJHN', 'WTVJIGYKXAHGZTYEEBRGLIDLO', null, 'Vancouver', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)""",
"""insert into address (addr_id, province, country_typ_cd, postal_cd, addr_line_1, addr_line_2, addr_line_3, city, address_format_type, address_desc, address_desc_short, delivery_instructions, unit_no, unit_type, civic_no, civic_no_suffix, street_name, street_type, street_direction, lock_box_no, installation_type, installation_name, installation_qualifier, route_service_type, route_service_no, province_state_name)
values
(7404643, 'BC', 'CA', 'C8886D', 'ZXXIBPHZQJBRXTTQRO HCSRAI', 'BYSCABJETMIFAX MIHLBTBAAE', null, 'Vancouver', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)""",
"""insert into address (addr_id, province, country_typ_cd, postal_cd, addr_line_1, addr_line_2, addr_line_3, city, address_format_type, address_desc, address_desc_short, delivery_instructions, unit_no, unit_type, civic_no, civic_no_suffix, street_name, street_type, street_direction, lock_box_no, installation_type, installation_name, installation_qualifier, route_service_type, route_service_no, province_state_name)
values
(7404644, 'BC', 'CA', 'VYXN94', 'RIILJCAEYEHMTZFGLJMLEOPHE', 'RNVOLRLYGKSWKKOUUEE FYOUO', null, 'Vancouver', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)""",
"""insert into address (addr_id, province, country_typ_cd, postal_cd, addr_line_1, addr_line_2, addr_line_3, city, address_format_type, address_desc, address_desc_short, delivery_instructions, unit_no, unit_type, civic_no, civic_no_suffix, street_name, street_type, street_direction, lock_box_no, installation_type, installation_name, installation_qualifier, route_service_type, route_service_no, province_state_name)
values
(7404645, 'BC', 'CA', '2IFBB1', 'QUAETPPSJOXNSKSYYVY NWPHK', 'IWLXVNQKIAYDNUUGKDZRVSBYM', null, 'Vancouver', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)""",
]
with BCRegistries(True) as cached_bc_reg:
cached_bc_reg.cache_bcreg_code_tables()
cached_bc_reg.insert_cache_sqls(corp_sqls)
# try running with dummy event id zero
corp_info = cached_bc_reg.get_bc_reg_corp_info(corp_num)
#print('-------------------------')
#print('corp_info:')
#print(corp_info)
#print('-------------------------')
start_event = {'event_id':0, 'event_date':MIN_START_DATE}
end_event = {'event_id':9999999999, 'event_date':MAX_END_DATE}
with EventProcessor() as event_processor:
corp_creds = event_processor.generate_credentials(system_type, start_event, end_event, corp_num, corp_info)
def process_corp_event_queue_internal(self,
load_regs=True,
generate_creds=False):
sql1 = """SELECT RECORD_ID, SYSTEM_TYPE_CD, PREV_EVENT_ID, LAST_EVENT_ID, CORP_NUM, ENTRY_DATE
FROM EVENT_BY_CORP_FILING
WHERE PROCESS_DATE is null"""
sql1a = """SELECT RECORD_ID, SYSTEM_TYPE_CD, PREV_EVENT_ID, LAST_EVENT_ID, CORP_NUM, CORP_JSON, ENTRY_DATE
FROM CORP_HISTORY_LOG
WHERE PROCESS_DATE is null"""
sql2 = """INSERT INTO CORP_HISTORY_LOG (SYSTEM_TYPE_CD, PREV_EVENT_ID, LAST_EVENT_ID, CORP_NUM, CORP_JSON, ENTRY_DATE)
VALUES(%s, %s, %s, %s, %s, %s) RETURNING RECORD_ID;"""
sql2a = """INSERT INTO CORP_HISTORY_LOG (SYSTEM_TYPE_CD, PREV_EVENT_ID, LAST_EVENT_ID, CORP_NUM, CORP_JSON, ENTRY_DATE, PROCESS_DATE)
VALUES(%s, %s, %s, %s, %s, %s, %s) RETURNING RECORD_ID;"""
sql3 = """UPDATE EVENT_BY_CORP_FILING
SET PROCESS_DATE = %s
WHERE RECORD_ID = %s"""
sql3a = """UPDATE CORP_HISTORY_LOG
SET PROCESS_DATE = %s
WHERE RECORD_ID = %s"""
cur = None
try:
corps = []
if load_regs:
# we are loading data from BC Registries based on the corp event queue
cur = self.conn.cursor()
cur.execute(sql1)
row = cur.fetchone()
while row is not None:
corps.append({
'RECORD_ID': row[0],
'SYSTEM_TYPE_CD': row[1],
'PREV_EVENT_ID': row[2],
'LAST_EVENT_ID': row[3],
'CORP_NUM': row[4],
'ENTRY_DATE': row[5]
})
row = cur.fetchone()
cur.close()
cur = None
else:
# not loading from BC Reg, just processing data already loaded in corp_history
cur = self.conn.cursor()
cur.execute(sql1a)
row = cur.fetchone()
while row is not None:
# print(row)
corps.append({
'RECORD_ID': row[0],
'SYSTEM_TYPE_CD': row[1],
'PREV_EVENT_ID': row[2],
'LAST_EVENT_ID': row[3],
'CORP_NUM': row[4],
'CORP_JSON': row[5],
'ENTRY_DATE': row[6]
})
row = cur.fetchone()
cur.close()
cur = None
with BCRegistries() as bc_registries:
for i, corp in enumerate(corps):
print('>>> Processing {} of {} corporations.'.format(
i + 1, len(corps)))
if load_regs:
# fetch corp info from bc_registries
corp_info = bc_registries.get_bc_reg_corp_info(
corp['CORP_NUM'], corp['LAST_EVENT_ID'])
else:
corp_info = corp['CORP_JSON']
if generate_creds:
# generate and store credentials
cur = self.conn.cursor()
corp_creds = self.generate_credentials(
corp['SYSTEM_TYPE_CD'], corp['PREV_EVENT_ID'],
corp['LAST_EVENT_ID'], corp['CORP_NUM'], corp_info)
self.store_credentials(cur, corp['SYSTEM_TYPE_CD'],
corp['PREV_EVENT_ID'],
corp['LAST_EVENT_ID'],
corp['CORP_NUM'], corp_info,
corp_creds)
cur.close()
cur = None
# store corporate info
if load_regs:
cur = self.conn.cursor()
cur.execute(sql2a, (
corp['SYSTEM_TYPE_CD'],
corp['PREV_EVENT_ID'],
corp['LAST_EVENT_ID'],
corp['CORP_NUM'],
bc_registries.to_json(corp_info),
datetime.datetime.now(),
datetime.datetime.now(),
))
cur.close()
cur = None
else:
# update process date
cur = self.conn.cursor()
cur.execute(sql3a, (
datetime.datetime.now(),
corp['RECORD_ID'],
))
cur.close()
cur = None
elif load_regs:
# store corporate info for future generation of credentials
cur = self.conn.cursor()
cur.execute(sql2, (
corp['SYSTEM_TYPE_CD'],
corp['PREV_EVENT_ID'],
corp['LAST_EVENT_ID'],
corp['CORP_NUM'],
bc_registries.to_json(corp_info),
datetime.datetime.now(),
))
cur.close()
cur = None
# update process date
cur = self.conn.cursor()
cur.execute(sql3, (
datetime.datetime.now(),
corp['RECORD_ID'],
))
self.conn.commit()
cur.close()
cur = None
# commit the changes to the database
self.conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
raise
finally:
if cur is not None:
cur.close()
print('Cursor closed.')
corp_name + "%' and end_event_id is null")
if 1 == len(corp_name_info):
# exact match
return corp_name_info
if 10 > len(corp_name):
# name too short, return
return corp_name_info
# try searching with abbreviated corp name
return bcreg_corp_name_search(bc_registries,
corp_name[:len(corp_name) - 5])
export_file = "bcreg/tests/test_data.csv"
stats_file = "bcreg/tests/test_data_stats.csv"
with BCRegistries() as bc_registries:
with open(export_file) as csvfile, open(stats_file, 'w') as outfile:
csv_reader = csv.reader(csvfile)
csv_writer = csv.writer(outfile)
header = None
rows = []
row_count = 0
corp_num_count = 0
dba_num_count = 0
name_count = 0
close_name_count = 0
dba_name_count = 0
close_dba_name_count = 0
def test_generate_corp_nums():
selected_corps = [
# '0078162',
# '0754041',
# 'XS1000180',
# 'LP1000140',
# 'A0059911',
# 'S1000080',
# '0637981',
# 'A0051632',
# '0578221',
# '0497648',
# 'A0038634',
# '0136093',
# '0869404',
# '0641396',
# 'C0283576',
# '0860306',
# '0673578',
# '0763302',
# '0860695',
# 'A0039853',
# '0403029',
'A0073185',
]
selected_corp_list = ''
i = 0
for corp in selected_corps:
selected_corp_list = selected_corp_list + "'" + corp + "'"
i = i + 1
if i < len(selected_corps):
selected_corp_list = selected_corp_list + ', '
with BCRegistries(False) as bc_registries:
sql = select_corp_scenario_sql.replace('%s', selected_corp_list)
corp_rows = bc_registries.get_bcreg_sql('event', sql)
print('sample_test_corps = {')
for corp in corp_rows:
party_ct = bc_registries.get_bcreg_sql(
'corp_party', run_corp_sql(corp['corp_num'],
select_party_recs))
party_addr_ct = bc_registries.get_bcreg_sql(
'office', run_corp_sql(corp['corp_num'],
select_party_addr_recs))
name_ct = bc_registries.get_bcreg_sql(
'name', run_corp_sql(corp['corp_num'], select_name_recs))
name_assumed_ct = bc_registries.get_bcreg_sql(
'name', run_corp_sql(corp['corp_num'],
select_name_assumed_recs))
name_trans_ct = bc_registries.get_bcreg_sql(
'name', run_corp_sql(corp['corp_num'], select_name_trans_recs))
tilma_ct = [
{
'count': 0
}
] # bc_registries.get_bcreg_sql('tilma_involved', run_corp_sql(corp['corp_num'], select_tilma_recs))
juisdiction_ct = bc_registries.get_bcreg_sql(
'jurisdiction',
run_corp_sql(corp['corp_num'], select_jurisdiction_recs))
with BCRegistries(True) as bc_cache_reg:
bc_cache_reg.cache_bcreg_corp_tables([corp['corp_num']], True)
sqls = bc_cache_reg.generated_sqls
fake_corp_num = bc_cache_reg.add_generated_corp_num(
corp['corp_num'])
print(' "corp_' + fake_corp_num + '": {')
print(' "corp_num":' + "'" + fake_corp_num + "'" +
', ' + '"corp_typ_cd":' + "'" + corp['corp_typ_cd'] + "'" +
', ' + '"state_typ_cd":' + "'" + corp['state_typ_cd'] + "'" +
', ' + '"party_ct":' + str(party_ct[0]['count']) + ', ' +
'"party_addr_ct":' + str(party_addr_ct[0]['count']) + ', ' +
'"name_ct":' + str(name_ct[0]['count']) + ', ' +
'"name_assumed_ct":' + str(name_assumed_ct[0]['count']) +
', ' + '"name_trans_ct":' + str(name_trans_ct[0]['count']) +
', ' + '"tilma_ct":' + str(tilma_ct[0]['count']) + ', ' +
'"juisdiction_ct":' + str(juisdiction_ct[0]['count']) + ', ')
print(' "sqls": [')
for sql in sqls:
print(' """' + sql.replace(
' values ',
'\n values\n ') +
'""",')
print(' ]')
print(' },')
print('}')
