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