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 _create_filing(
cls,
cursor,
event_id,
corp_num,
ar_date, # pylint: disable=too-many-arguments;
agm_date,
filing_type_code='FILE'):
"""Add record to FILING.
Note: Period End Date and AGM Date are both the AGM Date value for Co-ops.
:param cursor: oracle cursor
:param event_id: (int) event_id for all events for this transaction
:param date: (str) period_end_date
:param filing_type_code: (str) filing type code
"""
try:
if filing_type_code == 'OTANN':
cursor.execute(
"""
INSERT INTO filing (event_id, filing_typ_cd, effective_dt, period_end_dt, agm_date, arrangement_ind,
ods_typ_cd)
VALUES (:event_id, :filing_type_code, sysdate, TO_DATE(:period_end_date, 'YYYY-mm-dd'),
TO_DATE(:agm_date, 'YYYY-mm-dd'), 'N', 'P')
""",
event_id=event_id,
filing_type_code=filing_type_code,
period_end_date=ar_date if not agm_date else agm_date,
agm_date=agm_date)
elif filing_type_code == 'OTADD' or 'OTCDR':
cursor.execute(
"""
INSERT INTO filing (event_id, filing_typ_cd, effective_dt, period_end_dt)
VALUES (:event_id, :filing_type_code, sysdate, TO_DATE(:period_end_date, 'YYYY-mm-dd'))
""",
event_id=event_id,
filing_type_code=filing_type_code,
period_end_date=ar_date,
)
else:
current_app.logger.error(
f'error in filing: Did not recognize filing type code: {filing_type_code}'
)
raise InvalidFilingTypeException(filing_type=filing_type_code)
except Exception as err:
current_app.logger.error(
f'error in filing: could not create filing {filing_type_code} for {corp_num}'
)
raise err
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()
if filing_type == 'annualReport':
filing_obj = cls._get_ar(identifier=identifier,
event_id=event_id,
year=year)
elif filing_type == 'changeOfAddress':
filing_obj = cls._get_coa(identifier=identifier,
event_id=event_id,
year=year)
elif filing_type == 'changeOfDirectors':
filing_obj = cls._get_cod(identifier=identifier,
event_id=event_id,
year=year)
else:
raise InvalidFilingTypeException(filing_type=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 add_filing(cls, filing): # pylint: disable=too-many-locals,too-many-statements,too-many-branches;
"""Add new filing to COLIN tables.
:param filing: Filing dict.
:returns (int): the filing ID of the new filing.
"""
try:
corp_num = filing.get_corp_num()
# get db connection and start a session, in case we need to roll back
con = DB.connection
con.begin()
cursor = con.cursor()
# create new event record, return event ID
event_id = cls._get_event_id(cursor, corp_num, 'FILE')
# create new filing user
cls._create_filing_user(cursor, event_id, filing)
if filing.filing_type == 'annualReport':
ar_date = filing.body['annualReportDate']
agm_date = filing.body['annualGeneralMeetingDate']
filing_type_cd = 'OTANN'
# create new filing
cls._create_filing(cursor, event_id, corp_num, ar_date,
agm_date, filing_type_cd)
# update corporation record
Business.update_corporation(cursor, corp_num, agm_date)
# update corp_state TO ACT (active) if it is in good standing. From CRUD:
# - the current corp_state != 'ACT' and,
# - they just filed the last outstanding ARs
if filing.business.business['corpState'] != 'ACT':
agm_year = int(ar_date[:4])
last_year = datetime.datetime.now().year - 1
if agm_year >= last_year:
Business.update_corp_state(cursor,
event_id,
corp_num,
state='ACT')
elif filing.filing_type == 'changeOfAddress':
# set date to last ar date + 1
last_ar_date = filing.business.business['lastArDate']
day = int(last_ar_date[-2:]) + 1
try:
date = str(
datetime.datetime.strptime(
last_ar_date[:-2] + ('0' + str(day))[1:],
'%Y-%m-%d'))[:10]
except ValueError:
try:
day = '-01'
month = int(last_ar_date[5:7]) + 1
date = str(
datetime.datetime.strptime(
last_ar_date[:5] + ('0' + str(month))[1:] +
day, '%Y-%m-%d'))[:10]
except ValueError:
mm_dd = '-01-01'
yyyy = int(last_ar_date[:4]) + 1
date = str(
datetime.datetime.strptime(
str(yyyy) + mm_dd, '%Y-%m-%d'))[:10]
# create new filing
filing_type_cd = 'OTADD'
cls._create_filing(cursor, event_id, corp_num, date, None,
filing_type_cd)
# create new addresses for delivery + mailing, return address ids
delivery_addr_id = Address.create_new_address(
cursor, filing.body['deliveryAddress'])
mailing_addr_id = Address.create_new_address(
cursor, filing.body['mailingAddress'])
# update office table to include new addresses
Office.update_office(cursor, event_id, corp_num,
delivery_addr_id, mailing_addr_id, 'RG')
# create new ledger text for address change
cls._add_ledger_text(
cursor, event_id,
f'Change to the Registered Office, effective on {date}')
# update corporation record
Business.update_corporation(cursor, corp_num)
elif filing.filing_type == 'changeOfDirectors':
# create new filing
date = filing.business.business['lastArDate']
filing_type_cd = 'OTCDR'
cls._create_filing(cursor, event_id, corp_num, date, None,
filing_type_cd)
# create, cease, change directors
changed_dirs = []
for director in filing.body['directors']:
if 'appointed' in director['actions']:
Director.create_new_director(
cursor=cursor,
event_id=event_id,
director=director,
business=filing.business.as_dict())
if 'ceased' in director['actions'] and not any(
elem in ['nameChanged', 'addressChanged']
for elem in director['actions']):
Director.end_by_name(cursor=cursor,
director=director,
event_id=event_id,
corp_num=corp_num)
elif 'nameChanged' in director[
'actions'] or 'addressChanged' in director[
'actions']:
if 'appointed' in director['actions']:
current_app.logger.error(
f'Director appointed with name/address change: {director}'
)
changed_dirs.append(director)
# end tmp copy of director with no cessation date (will be recreated with changes and cessation
# date - otherwise end up with two copies of ended director)
tmp = director.copy()
tmp['cessationDate'] = ''
Director.end_by_name(cursor=cursor,
director=tmp,
event_id=event_id,
corp_num=corp_num)
# add back changed directors as new row - if ceased director with changes this will add them with
# cessation date + end event id filled
for director in changed_dirs:
Director.create_new_director(
cursor=cursor,
event_id=event_id,
director=director,
business=filing.business.as_dict())
# create new ledger text for address change
cls._add_ledger_text(cursor=cursor,
event_id=event_id,
text=f'Director change.')
# update corporation record
Business.update_corporation(cursor=cursor, corp_num=corp_num)
else:
raise InvalidFilingTypeException(
filing_type=filing.filing_type)
# success! commit the db changes
con.commit()
return event_id
except Exception as err:
# something went wrong, roll it all back
current_app.logger.error(err.with_traceback(None))
if con:
con.rollback()
raise err
def add_filing(cls, con, filing): # pylint: disable=too-many-locals,too-many-statements,too-many-branches;
"""Add new filing to COLIN tables.
:param con: DB connection
:param filing: Filing dict.
:returns (int): the filing ID of the new filing.
"""
try:
corp_num = filing.get_corp_num()
legal_type = corp_num[:2]
user_id = Filing.USERS[legal_type] if legal_type in ('CP', 'BC') else None
cursor = con.cursor()
# create new event record, return event ID
event_id = cls._get_event_id(cursor, corp_num, 'FILE')
# create new filing user
cls._create_filing_user(cursor, event_id, filing, user_id)
if filing.filing_type == 'annualReport':
ar_date = filing.body['annualReportDate']
agm_date = filing.body['annualGeneralMeetingDate']
filing_type_cd = 'OTANN'
# create new filing
cls._create_filing(cursor, event_id, corp_num, ar_date, agm_date, filing_type_cd)
# update corporation record
Business.update_corporation(cursor, corp_num, agm_date, True)
# update corp_state TO ACT (active) if it is in good standing. From CRUD:
# - the current corp_state != 'ACT' and,
# - they just filed the last outstanding ARs
agm_year = int(ar_date[:4])
if filing.business.business['corpState'] != 'ACT':
last_year = datetime.datetime.now().year - 1
if agm_year >= last_year:
Business.update_corp_state(cursor, event_id, corp_num, state='ACT')
# create new ledger text for annual report
text = agm_date if agm_date else f'NO AGM HELD IN {agm_year}'
cls._add_ledger_text(
cursor=cursor, event_id=event_id, text=f'ANNUAL REPORT - {text}', user_id=user_id)
elif filing.filing_type == 'changeOfAddress':
# set date to last ar date + 1 -- Bob wants this to be set to null
# last_ar_date = filing.business.business['lastArDate']
# day = int(last_ar_date[-2:]) + 1
# try:
# date = str(datetime.datetime.strptime(last_ar_date[:-2] + ('0' + str(day))[1:], '%Y-%m-%d'))[:10]
# except ValueError:
# try:
# day = '-01'
# month = int(last_ar_date[5:7]) + 1
# date = str(datetime.datetime.strptime(last_ar_date[:5] + ('0' + str(month))[1:] + day,
# '%Y-%m-%d')
# )[:10]
# except ValueError:
# mm_dd = '-01-01'
# yyyy = int(last_ar_date[:4]) + 1
# date = str(datetime.datetime.strptime(str(yyyy) + mm_dd, '%Y-%m-%d'))[:10]
date = None
# create new filing
filing_type_cd = 'OTADD'
cls._create_filing(cursor, event_id, corp_num, date, None, filing_type_cd)
# create new addresses for delivery + mailing, return address ids
cls._add_office_from_filing(cursor, event_id, corp_num, user_id, filing)
# update corporation record
Business.update_corporation(cursor, corp_num)
elif filing.filing_type == 'changeOfDirectors':
# create new filing
# bob wants this to be null
# date = filing.business.business['lastArDate']
date = None
filing_type_cd = 'OTCDR'
cls._create_filing(cursor, event_id, corp_num, date, None, filing_type_cd)
# create, cease, change directors
changed_dirs = []
for director in filing.body['directors']:
if 'appointed' in director['actions']:
Party.create_new_corp_party(cursor=cursor, event_id=event_id, party=director,
business=filing.business.as_dict())
if 'ceased' in director['actions'] and not any(elem in ['nameChanged', 'addressChanged']
for elem in director['actions']):
Party.end_director_by_name(cursor=cursor, director=director, event_id=event_id,
corp_num=corp_num)
elif 'nameChanged' in director['actions'] or 'addressChanged' in director['actions']:
if 'appointed' in director['actions']:
current_app.logger.error(f'Director appointed with name/address change: {director}')
changed_dirs.append(director)
# end tmp copy of director with no cessation date (will be recreated with changes and cessation
# date - otherwise end up with two copies of ended director)
tmp = director.copy()
tmp['cessationDate'] = ''
Party.end_director_by_name(cursor=cursor, director=tmp, event_id=event_id, corp_num=corp_num)
# add back changed directors as new row - if ceased director with changes this will add them with
# cessation date + end event id filled
for director in changed_dirs:
Party.create_new_corp_party(cursor=cursor, event_id=event_id, party=director,
business=filing.business.as_dict())
# create new ledger text for address change
cls._add_ledger_text(cursor=cursor, event_id=event_id, text=f'Director change.', user_id=user_id)
# update corporation record
Business.update_corporation(cursor=cursor, corp_num=corp_num)
elif filing.filing_type == 'incorporationApplication':
# Add business, update business info
# Add offices
date = None
filing_type_cd = 'OTINC'
cls._create_filing(cursor, event_id, corp_num, date, None, filing_type_cd)
# Do incorporation here
Business.create_corp_name(cursor, corp_num, 'test name', event_id)
Business.create_corp_state(cursor, corp_num, event_id)
cls._add_office_from_filing(cursor, event_id, corp_num, user_id, filing)
cls._add_parties_from_filing(cursor, event_id, filing)
cls._add_shares_from_filing(cursor, event_id, corp_num, filing)
else:
raise InvalidFilingTypeException(filing_type=filing.filing_type)
return event_id
except Exception as err:
# something went wrong, roll it all back
current_app.logger.error(err.with_traceback(None))
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 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 add_filing(cls, filing):
"""Add new filing to COLIN tables.
:param filing: Filing dict.
:returns (int): the filing ID of the new filing.
"""
try:
corp_num = filing.get_corp_num()
# get db connection and start a session, in case we need to roll back
con = db.connection
con.begin()
cursor = con.cursor()
# create new event record, return event ID
event_id = cls._get_event_id(cursor, corp_num, 'FILE')
# create new filing user
cls._add_filing_user(cursor, event_id, filing)
if filing.filing_type == 'annualReport':
date = filing.body['annualGeneralMeetingDate']
filing_type_cd = 'OTANN'
# create new filing
cls._add_filing(cursor, event_id, filing, date, filing_type_cd)
# update corporation record
cls._update_corporation(cursor, corp_num, date)
# update corp_state TO ACT (active) if it is in good standing. From CRUD:
# - the current corp_state != 'ACT' and,
# - they just filed the last outstanding ARs
if filing.business.business['corpState'] != 'ACT':
agm_year = int(date[:4])
last_year = datetime.datetime.now().year - 1
if agm_year >= last_year:
cls._update_corp_state(cursor, event_id, corp_num, state='ACT')
elif filing.filing_type == 'changeOfAddress':
# set date to last agm date + 1
last_agm_date = filing.business.business['lastAgmDate']
dd = int(last_agm_date[-2:]) + 1
try:
date = str(datetime.datetime.strptime(last_agm_date[:-2] + ('0' + str(dd))[1:], "%Y-%m-%d"))[:10]
except ValueError as err:
try:
dd = '-01'
mm = int(last_agm_date[5:7]) + 1
date = str(datetime.datetime.strptime(last_agm_date[:5] + ('0' + str(mm))[1:] + dd, "%Y-%m-%d"))[:10]
except ValueError as err:
mm_dd = '-01-01'
yyyy = int(last_agm_date[:4]) + 1
date = str(datetime.datetime.strptime(str(yyyy) + mm_dd, "%Y-%m-%d"))[:10]
filing_type_cd = 'OTADD'
# create new filing
cls._add_filing(cursor, event_id, filing, date, filing_type_cd)
# create new addresses for delivery + mailing, return address ids
delivery_addr_id = cls._get_address_id(cursor, filing.body['deliveryAddress'])
mailing_addr_id = cls._get_address_id(cursor, filing.body['mailingAddress'])
# update office table to include new addresses
cls._update_office(cursor, event_id, corp_num, delivery_addr_id, mailing_addr_id, 'RG')
# update corporation record
cls._update_corporation(cursor, corp_num, None)
# create new ledger text for address change
cls._add_ledger_text(cursor, event_id, 'Change to the Registered Office, effective on {} as filed with'
' {} Annual Report'.format(date, date[:4]))
else:
raise InvalidFilingTypeException(identifier=filing.business.business['identifier'],
filing_type=filing.filing_type)
# success! commit the db changes
con.commit()
except Exception as err:
# something went wrong, roll it all back
current_app.logger.error(err.with_traceback(None))
if con:
con.rollback()
raise err
