def find_by_identifier(cls, identifier: str = None, con=None): # pylint: disable=too-many-statements;
"""Return a Business by identifier."""
business = None
if not identifier:
return None
try:
# get record
if not con:
con = DB.connection
con.begin()
cursor = con.cursor()
cursor.execute("""
select corp.CORP_NUM as identifier, CORP_FROZEN_TYP_CD, corp_typ_cd type,
filing.period_end_dt as last_ar_date, LAST_AR_FILED_DT as last_ar_filed_date, LAST_AGM_DATE,
corp_op_state.full_desc as state, corp_state.state_typ_cd as corp_state,
t_name.corp_nme as legal_name,
t_assumed_name.CORP_NME as assumed_name, RECOGNITION_DTS as founding_date,
BN_15 as business_number, CAN_JUR_TYP_CD, OTHR_JURIS_DESC
from CORPORATION corp
left join CORP_NAME t_name on t_name.corp_num = corp.corp_num and t_name.CORP_NAME_TYP_CD='CO'
AND t_name.END_EVENT_ID is null
left join CORP_NAME t_assumed_name on t_assumed_name.corp_num = corp.corp_num
and t_assumed_name.CORP_NAME_TYP_CD='AS' AND t_assumed_name.END_EVENT_ID is null
join CORP_STATE on CORP_STATE.corp_num = corp.corp_num and CORP_STATE.end_event_id is null
join CORP_OP_STATE on CORP_OP_STATE.state_typ_cd = CORP_STATE.state_typ_cd
left join JURISDICTION on JURISDICTION.corp_num = corp.corp_num
join event on corp.corp_num = event.corp_num
left join filing on event.event_id = filing.event_id and filing.filing_typ_cd = 'OTANN'
where corp_typ_cd in ('CP', 'BC')
and corp.CORP_NUM=:corp_num
order by last_ar_date desc nulls last""",
corp_num=identifier)
business = cursor.fetchone()
if not business:
raise BusinessNotFoundException(identifier=identifier)
# add column names to resultset to build out correct json structure and make manipulation below more robust
# (better than column numbers)
business = dict(
zip([x[0].lower() for x in cursor.description], business))
# get last ledger date from EVENT table and add to business record
# note - FILE event type is correct for new filings; CONVOTHER is for events/filings pulled over from COBRS
# during initial data import for Coops.
cursor.execute("""
select max(EVENT_TIMESTMP) as last_ledger_timestamp from EVENT
where EVENT_TYP_CD in('FILE', 'CONVOTHER') and CORP_NUM = '{}'""".
format(identifier))
last_ledger_timestamp = cursor.fetchone()[0]
business['last_ledger_timestamp'] = last_ledger_timestamp
# if this is an XPRO, get correct jurisdiction; otherwise, it's BC
if business['type'] == 'XCP':
business['jurisdiction'] = business['can_jur_typ_cd']
if business['can_jur_typ_cd'] == 'OT':
business['jurisdiction'] = business['othr_juris_desc']
else:
business['jurisdiction'] = 'BC'
# set name
if business['assumed_name']:
business['legal_name'] = business['assumed_name']
# set status - In Good Standing if certain criteria met, otherwise use original value
if business['state'] == 'Active' and \
business['last_ar_filed_date'] is not None and \
isinstance(business['last_ar_filed_date'], datetime) and \
business['last_agm_date'] is not None and isinstance(business['last_agm_date'], datetime):
business['status'] = business['state']
if business['last_ar_filed_date'] > business['last_agm_date']:
business['status'] = 'In Good Standing'
else:
business['status'] = business['state']
# convert dates and date-times to correct json format and convert to camel case for schema names
business['foundingDate'] = convert_to_json_datetime(
business['founding_date'])
business['lastAgmDate'] = convert_to_json_date(
business['last_agm_date'])
business['lastArDate'] = convert_to_json_date(business['last_ar_date']) if business['last_ar_date'] \
else business['lastAgmDate']
business['lastLedgerTimestamp'] = convert_to_json_datetime(
business['last_ledger_timestamp'])
business['businessNumber'] = business['business_number']
business['corpState'] = business['corp_state']
business['legalName'] = business['legal_name']
business['legalType'] = business['type']
# remove unnecessary fields (
del business['can_jur_typ_cd']
del business['othr_juris_desc']
del business['assumed_name']
del business['state']
del business['business_number']
del business['corp_frozen_typ_cd']
del business['corp_state']
del business['founding_date']
del business['last_agm_date']
del business['last_ar_filed_date']
del business['last_ledger_timestamp']
del business['legal_name']
del business['type']
del business['last_ar_date']
# add cache_id todo: set to real value
business['cacheId'] = 0
# convert to Business object
business_obj = Business()
business_obj.business = business
return business_obj
except Exception as err:
# general catch-all exception
current_app.logger.error(err.with_traceback(None))
# pass through exception to caller
raise err
def get_filing(cls, con=None, # pylint: disable=too-many-arguments, too-many-branches;
business: Business = None, event_id: str = None, filing_type: str = None, year: int = None):
"""Get a Filing."""
if not business or not filing_type:
return None
try:
if not con:
con = DB.connection
con.begin()
cursor = con.cursor()
identifier = business.get_corp_num()
# get the filing types corresponding filing code
code = [key for key in cls.FILING_TYPES if cls.FILING_TYPES[key] == filing_type]
if len(code) < 1:
raise InvalidFilingTypeException(filing_type=filing_type)
# get the filing event info
filing_event_info = cls._get_filing_event_info(identifier=identifier, event_id=event_id,
filing_type_cd=code[0], year=year, cursor=cursor)
if not filing_event_info:
raise FilingNotFoundException(identifier=identifier, filing_type=filing_type, event_id=event_id)
if filing_type == 'annualReport':
filing_obj = cls._get_ar(identifier=identifier, filing_event_info=filing_event_info, cursor=cursor)
elif filing_type == 'changeOfAddress':
filing_obj = cls._get_coa(identifier=identifier, filing_event_info=filing_event_info, cursor=cursor)
elif filing_type == 'changeOfDirectors':
filing_obj = cls._get_cod(identifier=identifier, filing_event_info=filing_event_info, cursor=cursor)
elif filing_type == 'changeOfName':
filing_obj = cls._get_con(identifier=identifier, filing_event_info=filing_event_info, cursor=cursor)
elif filing_type == 'specialResolution':
filing_obj = cls._get_sr(identifier=identifier, filing_event_info=filing_event_info, cursor=cursor)
elif filing_type == 'voluntaryDissolution':
filing_obj = cls._get_vd(identifier=identifier, filing_event_info=filing_event_info, cursor=cursor)
elif filing_type == 'incorporationApplication':
filing_obj = cls._get_inc(identifier=identifier, filing_event_info=filing_event_info, cursor=cursor)
else:
# uncomment to bring in other filings as available on paper only
# filing_obj = cls._get_other(identifier=identifier, filing_event_info=filing_event_info, cursor=cursor)
raise InvalidFilingTypeException(filing_type=filing_type)
filing_obj.header = {
'availableOnPaperOnly': filing_obj.paper_only,
'certifiedBy': filing_event_info['certifiedBy'],
'colinIds': [filing_obj.body['eventId']],
'date': convert_to_json_date(filing_event_info['event_timestmp']),
'effectiveDate': convert_to_json_datetime(filing_obj.effective_date),
'email': filing_event_info['email'],
'name': filing_type
}
filing_obj.business = business
return filing_obj
except FilingNotFoundException as err:
# pass through exception to caller
raise err
except Exception as err:
# general catch-all exception
current_app.logger.error(err.with_traceback(None))
# pass through exception to caller
raise err
def find_by_identifier(cls,
identifier: str,
corp_types: List,
con=None) -> Business:
"""Return a Business by identifier."""
business = None
try:
# get record
if not con:
con = DB.connection
con.begin()
cursor = con.cursor()
cursor.execute(f"""
select corp.corp_num, corp_typ_cd, recognition_dts, bn_15, can_jur_typ_cd, othr_juris_desc,
filing.period_end_dt, last_agm_date, corp_op_state.full_desc as state,
corp_state.state_typ_cd as corp_state
from CORPORATION corp
join CORP_STATE on CORP_STATE.corp_num = corp.corp_num and CORP_STATE.end_event_id is null
join CORP_OP_STATE on CORP_OP_STATE.state_typ_cd = CORP_STATE.state_typ_cd
left join JURISDICTION on JURISDICTION.corp_num = corp.corp_num
join event on corp.corp_num = event.corp_num
left join filing on event.event_id = filing.event_id and filing.filing_typ_cd in ('OTANN', 'ANNBC')
where corp_typ_cd in ({stringify_list(corp_types)}) and corp.CORP_NUM=:corp_num
order by filing.period_end_dt desc nulls last
""",
corp_num=identifier)
business = cursor.fetchone()
if not business:
raise BusinessNotFoundException(identifier=identifier)
# add column names to resultset to build out correct json structure and make manipulation below more robust
# (better than column numbers)
business = dict(
zip([x[0].lower() for x in cursor.description], business))
# get all assumed, numbered/corporation, translation names
corp_names = CorpName.get_current(cursor=cursor,
corp_num=identifier)
assumed_name = None
corp_name = None
for name_obj in corp_names:
if name_obj.type_code == CorpName.TypeCodes.ASSUMED.value:
assumed_name = name_obj.corp_name
break
elif name_obj.type_code in [
CorpName.TypeCodes.CORP.value,
CorpName.TypeCodes.NUMBERED_CORP.value
]:
corp_name = name_obj.corp_name
# get last ledger date from EVENT table and add to business record
# note - FILE event type is correct for new filings; CONVOTHER is for events/filings pulled over from COBRS
cursor.execute("""
select max(EVENT_TIMESTMP) from EVENT
where EVENT_TYP_CD in ('FILE', 'CONVOTHER') and CORP_NUM=:corp_num
""",
corp_num=identifier)
last_ledger_timestamp = cursor.fetchone()[0]
business['last_ledger_timestamp'] = last_ledger_timestamp
# if this is an XPRO, get correct jurisdiction; otherwise, it's BC
if business['corp_typ_cd'] == 'XCP':
business['jurisdiction'] = business['can_jur_typ_cd']
if business['can_jur_typ_cd'] == 'OT':
business['jurisdiction'] = business['othr_juris_desc']
else:
business['jurisdiction'] = 'BC'
# convert to Business object
business_obj = Business()
business_obj.business_number = business['bn_15']
business_obj.corp_name = assumed_name if assumed_name else corp_name
business_obj.corp_num = business['corp_num']
business_obj.corp_state = business['corp_state']
business_obj.corp_type = business['corp_typ_cd']
business_obj.founding_date = convert_to_json_datetime(
business['recognition_dts'])
business_obj.jurisdiction = business['jurisdiction']
business_obj.last_agm_date = convert_to_json_date(
business['last_agm_date'])
business_obj.last_ar_date = convert_to_json_date(business['period_end_dt']) if business['period_end_dt'] \
else convert_to_json_date(business['last_agm_date'])
business_obj.last_ledger_timestamp = convert_to_json_datetime(
business['last_ledger_timestamp'])
business_obj.status = business['state']
return business_obj
except Exception as err:
# general catch-all exception
current_app.logger.error(err.with_traceback(None))
# pass through exception to caller
raise err
