def _get_cod(cls,
cursor,
identifier: str = None,
filing_event_info: dict = None):
"""Get change of directors filing."""
director_objs = Director.get_by_event(cursor, identifier,
filing_event_info['event_id'])
if len(director_objs) < 3:
current_app.logger.error(
'Less than 3 directors for {}'.format(identifier))
# check to see if this filing was made with an AR -> if it was then set the AR date as the effective date
effective_date = filing_event_info['event_timestmp']
annual_reports = cls._get_events(cursor=cursor,
identifier=identifier,
filing_type_code='OTANN')
for filing in annual_reports:
if convert_to_json_date(
filing['date']) == convert_to_json_date(effective_date):
effective_date = filing['annualReportDate']
break
filing_obj = Filing()
filing_obj.body = {
'directors': [x.as_dict() for x in director_objs],
'eventId': filing_event_info['event_id']
}
filing_obj.filing_type = 'changeOfDirectors'
filing_obj.paper_only = False
filing_obj.effective_date = effective_date
return filing_obj
def _get_ar(cls, identifier: str = None, filing_event_info: dict = None):
"""Return annual report filing."""
# get directors and registered office as of this filing
director_events = cls._get_events(
identifier=identifier,
filing_type_code=filing_event_info['filing_type_code'])
office_events = cls._get_events(
identifier=identifier,
filing_type_code=filing_event_info['filing_type_code'])
director_event_id = None
office_event_id = None
tmp_timestamp = datetime.datetime.fromtimestamp(0)
for event in director_events:
if filing_event_info['event_timestmp'] >= event[
'date'] > tmp_timestamp:
director_event_id = event['id']
tmp_timestamp = event['date']
tmp_timestamp = datetime.datetime.fromtimestamp(0)
for event in office_events:
if filing_event_info['event_timestmp'] >= event[
'date'] > tmp_timestamp:
office_event_id = event['id']
tmp_timestamp = event['date']
if director_event_id:
try:
directors = Director.get_by_event(identifier=identifier,
event_id=director_event_id)
except: # noqa B901; pylint: disable=bare-except;
# should only get here if event was before the bob date
directors = Director.get_current(identifier=identifier)
else:
directors = Director.get_current(identifier=identifier)
directors = [x.as_dict() for x in directors]
if office_event_id:
try:
reg_office = Office.get_by_event(event_id=office_event_id)
except: # noqa B901; pylint: disable=bare-except;
# should only get here if event was before the bob date
reg_office = Office.get_current(identifier=identifier)
else:
reg_office = Office.get_current(identifier=identifier)
reg_office = reg_office.as_dict()
# convert dates and date-times to correct json format
agm_date = convert_to_json_date(filing_event_info.get(
'agm_date', None))
ar_date = convert_to_json_date(filing_event_info['period_end_dt'])
filing_obj = Filing()
filing_obj.body = {
'annualGeneralMeetingDate': agm_date,
'annualReportDate': ar_date,
'directors': directors,
'eventId': filing_event_info['event_id'],
**reg_office
}
filing_obj.filing_type = 'annualReport'
return filing_obj
def _build_parties_list(cls, cursor, event_id: int = None):
parties = cursor.fetchall()
if not parties:
return None
completing_parties = {}
party_list = []
description = cursor.description
for row in parties:
party = Party()
party.title = ''
row = dict(zip([x[0].lower() for x in description], row))
if row['appointment_dt']:
party.officer = cls._get_officer(row)
party.delivery_address = Address.get_by_address_id(
cursor, row['delivery_addr_id']).as_dict()
party.mailing_address = Address.get_by_address_id(cursor, row['mailing_addr_id']).as_dict() \
if row['mailing_addr_id'] else party.delivery_address
party.appointment_date =\
convert_to_json_date(row.get('appointment_dt', None))
party.cessation_date = convert_to_json_date(
row.get('cessation_dt', None))
party.start_event_id = (row.get('start_event_id', '')) or ''
party.end_event_id = (row.get('end_event_id', '')) or ''
party.role_type = (row.get('party_typ_cd', '')) or ''
# this is in case the party was not ceased during this event
if event_id and party.end_event_id and party.end_event_id > event_id:
party.cessation_date = None
party_list.append(party)
if event_id:
completing_parties = cls.get_completing_parties(cursor, event_id)
return cls.group_parties(party_list, completing_parties)
def _build_directors_list(cls, cursor, event_id: int = None):
directors = cursor.fetchall()
if not directors:
return None
directors_list = []
description = cursor.description
for row in directors:
director = Director()
director.title = ''
row = dict(zip([x[0].lower() for x in description], row))
if row['appointment_dt']:
director.officer = {'firstName': row['first_nme'].strip() if row['first_nme'] else '',
'lastName': row['last_nme'].strip() if row['last_nme'] else '',
'middleInitial': row['middle_nme'] if row['middle_nme'] else ''}
director.delivery_address = Address.get_by_address_id(cursor, row['delivery_addr_id']).as_dict()
director.mailing_address = Address.get_by_address_id(cursor, row['mailing_addr_id']).as_dict() \
if row['mailing_addr_id'] else director.delivery_address
director.appointment_date =\
convert_to_json_date(row['appointment_dt']) if row['appointment_dt'] else None
director.cessation_date = convert_to_json_date(row['cessation_dt']) if row['cessation_dt'] else None
director.start_event_id = row['start_event_id'] if row['start_event_id'] else ''
director.end_event_id = row['end_event_id'] if row['end_event_id'] else ''
# this is in case the director was not ceased during this event
if event_id and director.end_event_id and director.end_event_id > event_id:
director.cessation_date = None
directors_list.append(director)
return directors_list
def _get_coa(cls, cursor, identifier: str = None, filing_event_info: dict = None):
"""Get change of address filing for registered and/or records office."""
office_obj_list = Office.get_by_event(cursor, filing_event_info['event_id'])
if not office_obj_list:
raise FilingNotFoundException(identifier=identifier, filing_type='change_of_address',
event_id=filing_event_info['event_id'])
offices = Office.convert_obj_list(office_obj_list)
# check to see if this filing was made with an AR -> if it was then set the AR date as the effective date
effective_date = filing_event_info['event_timestmp']
annual_reports = cls._get_events(cursor=cursor, identifier=identifier, filing_type_code='OTANN')
for filing in annual_reports:
if convert_to_json_date(filing['date']) == convert_to_json_date(effective_date):
effective_date = filing['annualReportDate']
break
filing_obj = Filing()
filing_obj.body = {
'offices': offices,
'eventId': filing_event_info['event_id']
}
filing_obj.filing_type = 'changeOfAddress'
filing_obj.paper_only = False
filing_obj.effective_date = effective_date
return filing_obj
def _get_ar(cls, cursor, identifier: str = None, filing_event_info: dict = None):
"""Return annual report filing."""
# get directors and registered office as of this filing
director_events = cls._get_events(identifier=identifier, filing_type_code='OTCDR', cursor=cursor)
office_events = cls._get_events(identifier=identifier, filing_type_code='OTADD', cursor=cursor)
director_event_id = None
office_event_id = None
tmp_timestamp = datetime.datetime.fromtimestamp(0)
for event in director_events:
if filing_event_info['event_timestmp'] >= event['date'] > tmp_timestamp:
director_event_id = event['id']
tmp_timestamp = event['date']
tmp_timestamp = datetime.datetime.fromtimestamp(0)
for event in office_events:
if filing_event_info['event_timestmp'] >= event['date'] > tmp_timestamp:
office_event_id = event['id']
tmp_timestamp = event['date']
recreated_dirs_and_office = True
if director_event_id:
try:
directors = Party.get_by_event(identifier=identifier, event_id=director_event_id, cursor=cursor)
except: # noqa B901; pylint: disable=bare-except;
# should only get here if agm was before the bob date
recreated_dirs_and_office = False
directors = Party.get_current(identifier=identifier, cursor=cursor)
else:
directors = Party.get_current(identifier=identifier, cursor=cursor)
directors = [x.as_dict() for x in directors]
if office_event_id:
try:
office_obj_list = (Office.get_by_event(event_id=office_event_id, cursor=cursor)).as_dict()
offices = Office.convert_obj_list(office_obj_list)
except: # noqa B901; pylint: disable=bare-except;
# should only get here if agm was before the bob date
recreated_dirs_and_office = False
office_obj_list = Office.get_current(identifier=identifier, cursor=cursor)
offices = Office.convert_obj_list(office_obj_list)
else:
office_obj_list = Office.get_current(identifier=identifier, cursor=cursor)
offices = Office.convert_obj_list(office_obj_list)
filing_obj = Filing()
filing_obj.body = {
'annualGeneralMeetingDate': convert_to_json_date(filing_event_info.get('agm_date', None)),
'annualReportDate': convert_to_json_date(filing_event_info['period_end_dt']),
'directors': directors,
'eventId': filing_event_info['event_id'],
'offices': offices
}
filing_obj.filing_type = 'annualReport'
filing_obj.paper_only = not recreated_dirs_and_office
filing_obj.effective_date = filing_event_info['period_end_dt']
return filing_obj
def get_historic_filings(cls, business: Business = None):
"""Get list all filings from before the bob-date=2019-03-08."""
if not business:
return None
try:
historic_filings = []
cursor = DB.connection.cursor()
cursor.execute("""
select event.event_id, event_timestmp, filing_typ_cd, effective_dt, period_end_dt, agm_date
from event join filing on event.event_id = filing.event_id
where corp_num=:identifier
order by event_timestmp
""",
identifier=business.get_corp_num()
)
filings_info_list = []
for filing_info in cursor:
filings_info_list.append(dict(zip([x[0].lower() for x in cursor.description], filing_info)))
for filing_info in filings_info_list:
if not cls.FILING_TYPES[filing_info['filing_typ_cd']]:
raise InvalidFilingTypeException(filing_type=filing_info['filing_typ_cd'])
filing_info['filing_type'] = cls.FILING_TYPES[filing_info['filing_typ_cd']]
date = convert_to_json_date(filing_info['event_timestmp'])
if date < '2019-03-08':
filing = Filing()
filing.business = business
filing.header = {
'date': date,
'name': filing_info['filing_type'],
'effectiveDate': convert_to_json_date(filing_info['effective_dt']),
'historic': True,
'availableOnPaperOnly': True,
'colinIds': [filing_info['event_id']]
}
filing.body = {
filing_info['filing_type']: {
'annualReportDate': convert_to_json_date(filing_info['period_end_dt']),
'annualGeneralMeetingDate': convert_to_json_date(filing_info['agm_date'])
}
}
historic_filings.append(filing.as_dict())
return historic_filings
except InvalidFilingTypeException as err:
current_app.logger.error('Unknown filing type found when getting historic filings for '
f'{business.get_corp_num()}.')
# 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_ar(cls,
identifier: str = None,
event_id: str = None,
year: int = None):
"""Return annual report filing."""
if event_id:
filing_event_info = cls._find_filing_event_info(
identifier=identifier,
event_id=event_id,
filing_type_cd1='OTANN',
year=year)
else:
filing_event_info = cls._find_filing_event_info(
identifier=identifier, filing_type_cd1='OTANN', year=year)
if not filing_event_info:
raise FilingNotFoundException(identifier=identifier,
filing_type='annualReport',
event_id=event_id)
# if there is no AGM date in period_end_dt, check agm_date and effective date
try:
agm_date = next(item for item in [
filing_event_info['period_end_dt'],
filing_event_info['agm_date'],
filing_event_info['effective_dt']
] if item is not None)
except StopIteration:
agm_date = None
# convert dates and date-times to correct json format
filing_event_info['event_timestmp'] = convert_to_json_date(
filing_event_info['event_timestmp'])
agm_date = convert_to_json_date(agm_date)
filing_obj = Filing()
filing_obj.header = {
'date': filing_event_info['event_timestmp'],
'name': 'annualReport'
}
filing_obj.body = {
'annualGeneralMeetingDate': agm_date,
'certifiedBy': filing_event_info['certifiedBy'],
'email': filing_event_info['email'],
'eventId': filing_event_info['event_id']
}
filing_obj.filing_type = 'annualReport'
filing_obj.event_id = filing_event_info['event_id'] # pylint: disable=attribute-defined-outside-init
return filing_obj
def _get_vd(cls, cursor, identifier: str = None, filing_event_info: dict = None):
"""Get voluntary dissolution filing."""
querystring = ("""
select filing.event_id, filing.effective_dt
from filing
where filing.event_id=:event_id
""")
try:
cursor.execute(querystring, event_id=filing_event_info['event_id'])
vd_info = cursor.fetchone()
if not vd_info:
raise FilingNotFoundException(identifier=identifier,
filing_type=cls.FILING_TYPES[filing_event_info['filing_type_code']],
event_id=filing_event_info['event_id']
)
vd_info = dict(zip([x[0].lower() for x in cursor.description], vd_info))
filing_obj = Filing()
filing_obj.body = {
'eventId': vd_info['event_id'],
'dissolutionDate': convert_to_json_date(vd_info['effective_dt'])
}
filing_obj.filing_type = cls.FILING_TYPES[filing_event_info['filing_type_code']]
filing_obj.paper_only = True
filing_obj.effective_date = filing_event_info['event_timestmp']
return filing_obj
except Exception as err:
current_app.logger.error('error voluntary dissolution filing for corp: {}'.format(identifier))
raise err
def _get_other(cls, cursor, identifier: str = None, filing_event_info: dict = None):
"""Get basic info for a filing we aren't handling yet."""
querystring = ("""
select filing.event_id, filing.effective_dt, ledger_text.notation
from filing
left join ledger_text on ledger_text.event_id = filing.event_id
where filing.event_id=:event_id
""")
try:
cursor.execute(querystring, event_id=filing_event_info['event_id'])
filing_info = cursor.fetchone()
if not filing_info:
raise FilingNotFoundException(identifier=identifier,
filing_type=cls.FILING_TYPES[filing_event_info['filing_type_code']],
event_id=filing_event_info['event_id']
)
filing_info = dict(zip([x[0].lower() for x in cursor.description], filing_info))
filing_obj = Filing()
filing_obj.body = {
'eventId': filing_info['event_id'],
'filedDate': convert_to_json_date(filing_event_info['event_timestmp']),
'ledgerText': filing_info['notation'],
}
filing_obj.filing_type = cls.FILING_TYPES[filing_event_info['filing_type_code']]
filing_obj.paper_only = True
filing_obj.effective_date = filing_info['effective_dt']
return filing_obj
except Exception as err:
current_app.logger.error('error getting {} filing for corp: {}'.format(
cls.FILING_TYPES[filing_event_info['filing_type_code']], identifier))
raise err
def _get_sr(cls, identifier: str = None, filing_event_info: dict = None):
"""Get special resolution filing."""
querystring = ("""
select filing.event_id, filing.effective_dt, ledger_text.notation
from filing
join ledger_text on ledger_text.event_id = filing.event_id
where filing.event_id=:event_id
""")
try:
cursor = DB.connection.cursor()
cursor.execute(querystring, event_id=filing_event_info['event_id'])
sr_info = cursor.fetchone()
if not sr_info:
raise FilingNotFoundException(identifier=identifier,
filing_type=cls.FILING_TYPES[filing_event_info['filing_type_code']],
event_id=filing_event_info['event_id']
)
sr_info = dict(zip([x[0].lower() for x in cursor.description], sr_info))
filing_obj = Filing()
filing_obj.body = {
'eventId': sr_info['event_id'],
'filedDate': convert_to_json_date(sr_info['effective_dt']),
'resolution': sr_info['notation'],
}
filing_obj.filing_type = 'specialResolution'
return filing_obj
except Exception as err:
current_app.logger.error('error getting special resolution filing for corp: {}'.format(identifier))
raise err
def _build_parties_list(cls,
cursor,
corp_num: str,
event_id: int = None) -> Optional[List]:
parties = cursor.fetchall()
if not parties:
return None
completing_parties = {}
party_list = []
description = cursor.description
for row in parties:
party = Party()
party.title = ''
row = dict(zip([x[0].lower() for x in description], row))
if not row['appointment_dt']:
row['appointment_dt'] = Business.get_founding_date(
cursor=cursor, corp_num=corp_num)
party.officer = cls._get_officer(row)
if (row.get('party_typ_cd', None) == cls.role_types['Director']
) and not row['delivery_addr_id']:
current_app.logger.error(
f"Bad director data for {party.officer.get('firstName')} {party.officer.get('lastName')} {corp_num}"
)
else:
if row['delivery_addr_id']:
party.delivery_address = Address.get_by_address_id(
cursor, row['delivery_addr_id']).as_dict()
party.mailing_address = Address.get_by_address_id(cursor, row['mailing_addr_id']).as_dict() \
if row['mailing_addr_id'] else party.delivery_address
party.appointment_date =\
convert_to_json_date(row.get('appointment_dt', None))
party.cessation_date = convert_to_json_date(
row.get('cessation_dt', None))
party.start_event_id = (row.get('start_event_id', '')) or ''
party.end_event_id = (row.get('end_event_id', '')) or ''
party.role_type = (row.get('party_typ_cd', '')) or ''
party.corp_party_id = row.get('corp_party_id', None)
party_list.append(party)
if event_id:
completing_parties = cls.get_completing_parties(cursor, event_id)
return cls.group_parties(party_list, completing_parties)
def get_resolutions(cls, cursor, corp_num: str) -> List:
"""Get all resolution dates for a company."""
try:
resolution_dates = []
cursor.execute("""
select resolution_dt from resolution
where corp_num = :corp_num and end_event_id is null
order by resolution_dt desc
""",
corp_num=corp_num)
resolution_oracle_dates = cursor.fetchall()
for date in resolution_oracle_dates:
resolution_dates.append(convert_to_json_date(date[0]))
return resolution_dates
except Exception as err:
current_app.logger.error(
f'Error looking up resolution dates for {corp_num}')
raise err
def find_change_of_addr(cls,
identifier: str = None,
event_id: str = None,
year: int = None): # pylint: disable=unused-argument; will use year later
"""Return change of address filing."""
if event_id:
filing_event_info = cls._find_filing_event_info(
identifier=identifier,
event_id=event_id,
filing_type_cd1='OTADD',
filing_type_cd2='OTARG')
else:
filing_event_info = cls._find_filing_event_info(
identifier=identifier,
filing_type_cd1='OTADD',
filing_type_cd2='OTARG')
registered_office_obj = Office.get_by_event(
filing_event_info['event_id'])
if not registered_office_obj:
raise FilingNotFoundException(identifier=identifier,
filing_type='change_of_address',
event_id=event_id)
filing_obj = Filing()
filing_obj.header = {
'date': convert_to_json_date(filing_event_info['event_timestmp']),
'name': 'changeOfAddress'
}
filing_obj.body = {
'certifiedBy':
filing_event_info['certifiedBy'],
'email':
filing_event_info['email'],
**registered_office_obj.as_dict(), 'eventId':
filing_event_info['event_id']
}
filing_obj.filing_type = 'changeOfAddress'
return filing_obj
def _get_cod(cls,
identifier: str = None,
event_id: str = None,
year: int = None): # pylint: disable=unused-argument; will use year later
"""Get change of directors filing."""
filing_obj = Filing()
if event_id:
filing_event_info = cls._get_filing_event_info(
identifier=identifier,
event_id=event_id,
filing_type_cd1='OTCDR',
filing_type_cd2='OTADR')
else:
filing_event_info = cls._get_filing_event_info(
identifier=identifier,
filing_type_cd1='OTCDR',
filing_type_cd2='OTADR')
director_objs = Director.get_by_event(identifier,
filing_event_info['event_id'])
if len(director_objs) < 3:
current_app.logger.error(
'Less than 3 directors for {}'.format(identifier))
filing_obj.header = {
'date': convert_to_json_date(filing_event_info['event_timestmp']),
'name': 'changeOfDirectors',
'certifiedBy': filing_event_info['certifiedBy'],
'email': filing_event_info['email']
}
filing_obj.body = {
'directors': [x.as_dict() for x in director_objs],
'eventId': filing_event_info['event_id']
}
filing_obj.filing_type = 'changeOfDirectors'
return filing_obj
def get_filing(cls,
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:
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)
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)
elif filing_type == 'changeOfAddress':
filing_obj = cls._get_coa(identifier=identifier,
filing_event_info=filing_event_info)
elif filing_type == 'changeOfDirectors':
filing_obj = cls._get_cod(identifier=identifier,
filing_event_info=filing_event_info)
elif filing_type == 'changeOfName':
filing_obj = cls._get_con(identifier=identifier,
filing_event_info=filing_event_info)
elif filing_type == 'specialResolution':
filing_obj = cls._get_sr(identifier=identifier,
filing_event_info=filing_event_info)
elif filing_type == 'voluntaryDissolution':
filing_obj = cls._get_vd(identifier=identifier,
filing_event_info=filing_event_info)
else:
raise InvalidFilingTypeException(filing_type=filing_type)
filing_obj.header = {
'date':
convert_to_json_date(filing_event_info['event_timestmp']),
'name':
filing_type,
'certifiedBy':
filing_event_info['certifiedBy'],
'email':
filing_event_info['email'],
'availableOnPaperOnly':
filing_obj.filing_type
in ['voluntaryDissolution', 'specialResolution']
}
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 _get_ar(cls, identifier: str = None, filing_event_info: dict = None):
"""Return annual report filing."""
# get directors and registered office as of this filing
director_events = cls._get_events(identifier=identifier,
filing_type_code='OTCDR')
office_events = cls._get_events(identifier=identifier,
filing_type_code='OTADD')
director_event_id = None
office_event_id = None
tmp_timestamp = datetime.datetime.fromtimestamp(0)
for event in director_events:
if filing_event_info['event_timestmp'] >= event[
'date'] > tmp_timestamp:
director_event_id = event['id']
tmp_timestamp = event['date']
tmp_timestamp = datetime.datetime.fromtimestamp(0)
for event in office_events:
if filing_event_info['event_timestmp'] >= event[
'date'] > tmp_timestamp:
office_event_id = event['id']
tmp_timestamp = event['date']
recreated_dirs_and_office = True
if director_event_id:
try:
directors = Director.get_by_event(identifier=identifier,
event_id=director_event_id)
except: # noqa B901; pylint: disable=bare-except;
# should only get here if agm was before the bob date
recreated_dirs_and_office = False
directors = Director.get_current(identifier=identifier)
else:
directors = Director.get_current(identifier=identifier)
directors = [x.as_dict() for x in directors]
if office_event_id:
try:
reg_office = Office.get_by_event(event_id=office_event_id)
except: # noqa B901; pylint: disable=bare-except;
# should only get here if agm was before the bob date
recreated_dirs_and_office = False
reg_office = Office.get_current(identifier=identifier)
else:
reg_office = Office.get_current(identifier=identifier)
reg_office = reg_office.as_dict()
# convert dates and date-times to correct json format
agm_date = convert_to_json_date(filing_event_info.get(
'agm_date', None))
ar_date = convert_to_json_date(filing_event_info['period_end_dt'])
# paper filings from colin don't enter in the agm_date and don't have no agm filings in the filings table
if filing_event_info['user_id'] != 'COOPER' and not agm_date:
agm_date = ar_date
filing_obj = Filing()
filing_obj.body = {
'annualGeneralMeetingDate': agm_date,
'annualReportDate': ar_date,
'directors': directors,
'eventId': filing_event_info['event_id'],
**reg_office
}
filing_obj.filing_type = 'annualReport'
filing_obj.paper_only = not recreated_dirs_and_office
filing_obj.effective_date = filing_event_info['period_end_dt']
return filing_obj
def _get_ar(
cls,
identifier: str = None,
event_id: str = None, # pylint: disable=too-many-locals,too-many-branches;
year: int = None):
"""Return annual report filing."""
if event_id:
filing_event_info = cls._get_filing_event_info(
identifier=identifier,
event_id=event_id,
filing_type_cd1='OTANN',
year=year)
else:
filing_event_info = cls._get_filing_event_info(
identifier=identifier, filing_type_cd1='OTANN', year=year)
if not filing_event_info:
raise FilingNotFoundException(identifier=identifier,
filing_type='annualReport',
event_id=event_id)
# get directors and registered office as of this filing
director_events = Director.get_events(identifier=identifier)
office_events = Office.get_events(identifier=identifier)
director_event_id = None
office_event_id = None
tmp_timestamp = datetime.datetime.fromtimestamp(0)
for event in director_events:
if filing_event_info['event_timestmp'] >= event[
'date'] > tmp_timestamp:
director_event_id = event['id']
tmp_timestamp = event['date']
tmp_timestamp = datetime.datetime.fromtimestamp(0)
for event in office_events:
if filing_event_info['event_timestmp'] >= event[
'date'] > tmp_timestamp:
office_event_id = event['id']
tmp_timestamp = event['date']
if director_event_id:
try:
directors = Director.get_by_event(identifier=identifier,
event_id=director_event_id)
except: # noqa B901; pylint: disable=bare-except;
# should only get here if event was before the bob date
directors = Director.get_current(identifier=identifier)
else:
directors = Director.get_current(identifier=identifier)
directors = [x.as_dict() for x in directors]
if office_event_id:
try:
reg_office = Office.get_by_event(event_id=office_event_id)
except: # noqa B901; pylint: disable=bare-except;
# should only get here if event was before the bob date
reg_office = Office.get_current(identifier=identifier)
else:
reg_office = Office.get_current(identifier=identifier)
reg_office = reg_office.as_dict()
# convert dates and date-times to correct json format
filing_event_info['event_timestmp'] = convert_to_json_date(
filing_event_info['event_timestmp'])
agm_date = convert_to_json_date(filing_event_info.get(
'agm_date', None))
ar_date = convert_to_json_date(filing_event_info['period_end_dt'])
filing_obj = Filing()
filing_obj.header = {
'date': filing_event_info['event_timestmp'],
'name': 'annualReport',
'certifiedBy': filing_event_info['certifiedBy'],
'email': filing_event_info['email']
}
filing_obj.body = {
'annualGeneralMeetingDate': agm_date,
'annualReportDate': ar_date,
'directors': directors,
'eventId': filing_event_info['event_id'],
**reg_office
}
filing_obj.filing_type = 'annualReport'
filing_obj.event_id = filing_event_info['event_id'] # pylint: disable=attribute-defined-outside-init
return filing_obj
def find_ar(cls, business: Business = None, identifier: str = None, year: int = None):
# build base querystring
querystring = (
"""
select event.EVENT_TIMESTMP, EFFECTIVE_DT, AGM_DATE, PERIOD_END_DT, NOTATION,
FIRST_NME, LAST_NME, MIDDLE_NME, EMAIL_ADDR
from EVENT
join FILING on EVENT.EVENT_ID = FILING.EVENT_ID
left join FILING_USER on EVENT.EVENT_ID = FILING_USER.EVENT_ID
left join LEDGER_TEXT on EVENT.EVENT_ID = LEDGER_TEXT.EVENT_ID
where CORP_NUM=:identifier and FILING_TYP_CD=:filing_typ_cd
"""
)
# condition by year on period end date - for coops, this is same as AGM date; for corps, this is financial
# year end date.
if year:
querystring += ' AND extract(year from PERIOD_END_DT) = {}'.format(year)
querystring += ' order by EVENT_TIMESTMP desc '
# get record
cursor = db.connection.cursor()
cursor.execute(querystring, identifier=identifier, filing_typ_cd='OTANN')
filing = cursor.fetchone()
if not filing:
raise FilingNotFoundException(identifier=identifier, filing_type='annualReport')
# add column names to resultset to build out correct json structure and make manipulation below more robust
# (better than column numbers)
filing = dict(zip([x[0].lower() for x in cursor.description], filing))
# if there is no AGM date in period_end_dt, check agm_date and effective date
try:
agm_date = next(item for item in [
filing['period_end_dt'], filing['agm_date'], filing['effective_dt']
] if item is not None)
except StopIteration:
agm_date = None
# build filing user name from first, middle, last name
filing_user_name = ' '.join(filter(None, [filing['first_nme'], filing['middle_nme'], filing['last_nme']]))
if not filing_user_name:
filing_user_name = 'Unavailable'
# if email is blank, set as empty tring
if not filing['email_addr']:
filing['email_addr'] = '*****@*****.**'
# convert dates and date-times to correct json format
filing['event_timestmp'] = convert_to_json_date(filing['event_timestmp'])
agm_date = convert_to_json_date(agm_date)
filing_obj = Filing()
filing_obj.business = business
filing_obj.header = {
'date': filing['event_timestmp'],
'name': 'annualReport'
}
filing_obj.body = {
'annualGeneralMeetingDate': agm_date,
'certifiedBy': filing_user_name,
'email': filing['email_addr']
}
filing_obj.filing_type = 'annualReport'
return filing_obj
def find_change_of_addr(cls, business: Business = None, identifier: str = None, year: int = None):
# build base querystring
# todo: check full_desc in country_type table matches with canada post api for foreign countries
querystring = (
"""
select ADDR_LINE_1, ADDR_LINE_2, ADDR_LINE_3, CITY, PROVINCE, COUNTRY_TYPE.FULL_DESC, POSTAL_CD,
DELIVERY_INSTRUCTIONS, EVENT.EVENT_TIMESTMP, FILING_USER.FIRST_NME, FILING_USER.LAST_NME,
FILING_USER.MIDDLE_NME, FILING_USER.EMAIL_ADDR
from ADDRESS
join OFFICE on ADDRESS.ADDR_ID = OFFICE.{addr_id_typ}
join COUNTRY_TYPE on ADDRESS.COUNTRY_TYP_CD = COUNTRY_TYPE.COUNTRY_TYP_CD
join EVENT on OFFICE.START_EVENT_ID = EVENT.EVENT_ID
left join FILING_USER on EVENT.EVENT_ID = FILING_USER.EVENT_ID
where OFFICE.END_EVENT_ID IS NULL and OFFICE.CORP_NUM=:corp_num and OFFICE.OFFICE_TYP_CD=:office_typ_cd
"""
)
querystring_delivery = (
querystring.format(addr_id_typ='DELIVERY_ADDR_ID')
)
querystring_mailing = (
querystring.format(addr_id_typ='MAILING_ADDR_ID')
)
# get record
cursor = db.connection.cursor()
cursor.execute(querystring_delivery, corp_num=identifier, office_typ_cd='RG')
delivery_address = cursor.fetchone()
test = cursor.fetchone()
if test:
current_app.logger.error('More than 1 delivery address returned - update oracle sql in find_reg_off_addr')
cursor.execute(querystring_mailing, corp_num=identifier, office_typ_cd='RG')
mailing_address = cursor.fetchone()
test = cursor.fetchone()
if test:
current_app.logger.error('More than 1 mailing address returned - update oracle sql in find_reg_off_addr')
if not delivery_address:
raise FilingNotFoundException(identifier=identifier, filing_type='change_of_address')
# add column names to result set to build out correct json structure and make manipulation below more robust
# (better than column numbers)
delivery_address = dict(zip([x[0].lower() for x in cursor.description], delivery_address))
mailing_address = dict(zip([x[0].lower() for x in cursor.description], mailing_address))
# build filing user name from first, middle, last name
filing_user_name = ' '.join(filter(None, [delivery_address['first_nme'], delivery_address['middle_nme'],
delivery_address['last_nme']]))
if not filing_user_name:
filing_user_name = 'N/A'
# if email is blank, set as N/A
if not delivery_address['email_addr']:
delivery_address['email_addr'] = 'N/A'
# todo: check all fields for data - may be different for data outside of coops
# expecting data-fix for all bad data in address table for coops: this will catch if anything was missed
if delivery_address['addr_line_1'] and delivery_address['addr_line_2'] and delivery_address['addr_line_3']:
current_app.logger.error('Expected 2, but got 3 delivery address lines for: {}'.format(identifier))
if not delivery_address['addr_line_1'] and not delivery_address['addr_line_2'] and not delivery_address['addr_line_3']:
current_app.logger.error('Expected at least 1 delivery addr_line, but got 0 for: {}'.format(identifier))
if not delivery_address['city'] or not delivery_address['province'] or not delivery_address['full_desc'] \
or not delivery_address['postal_cd']:
current_app.logger.error('Missing field in delivery address for: {}'.format(identifier))
if mailing_address['addr_line_1'] and mailing_address['addr_line_2'] and mailing_address['addr_line_3']:
current_app.logger.error('Expected 2, but 3 mailing address lines returned for: {}'.format(identifier))
if not mailing_address['city'] or not mailing_address['province'] or not mailing_address['full_desc'] \
or not delivery_address['postal_cd']:
current_app.logger.error('Missing field in mailing address for: {}'.format(identifier))
# for cases where delivery addresses were input out of order - shift them to lines 1 and 2
if not delivery_address['addr_line_1']:
if delivery_address['addr_line_2']:
delivery_address['addr_line_1'] = delivery_address['addr_line_2']
delivery_address['addr_line_2'] = None
if not delivery_address['addr_line_2']:
if delivery_address['addr_line_3']:
delivery_address['addr_line_2'] = delivery_address['addr_line_3']
delivery_address['addr_line_3'] = None
delivery_address['country'] = delivery_address['full_desc']
mailing_address['country'] = mailing_address['full_desc']
delivery_address['event_timestmp'] = convert_to_json_date(delivery_address['event_timestmp'])
mailing_address['event_timestmp'] = convert_to_json_date(mailing_address['event_timestmp'])
filing_obj = Filing()
filing_obj.business = business
filing_obj.header = {
'date': delivery_address['event_timestmp'],
'name': 'changeOfAddress'
}
filing_obj.body = {
"certifiedBy": filing_user_name,
"email": delivery_address['email_addr'],
"deliveryAddress": {
"streetAddress": delivery_address['addr_line_1'],
"streetAddressAdditional": delivery_address['addr_line_2'] if delivery_address['addr_line_2'] else '',
"addressCity": delivery_address['city'],
"addressRegion": delivery_address['province'],
"addressCountry": delivery_address['country'],
"postalCode": delivery_address['postal_cd'],
"deliveryInstructions": delivery_address['delivery_instructions']
if delivery_address['delivery_instructions'] else ''
},
"mailingAddress": {
"streetAddress": mailing_address['addr_line_1'],
"streetAddressAdditional": mailing_address['addr_line_2'] if mailing_address['addr_line_2'] else '',
"addressCity": mailing_address['city'],
"addressRegion": mailing_address['province'],
"addressCountry": mailing_address['country'],
"postalCode": mailing_address['postal_cd'],
"deliveryInstructions": mailing_address['delivery_instructions']
if mailing_address['delivery_instructions'] else ''
}
}
filing_obj.filing_type = 'changeOfAddress'
return filing_obj
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
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
