def _add_office_from_filing(
cls,
cursor, # pylint: disable=too-many-arguments
event_id,
corp_num,
user_id,
filing):
office_desc = ''
text = 'Change to the %s.'
if filing.filing_type == 'incorporationApplication':
text = 'Incorporation for %s.'
for office_type in filing.body['offices']:
office_arr = filing.body['offices'][office_type]
delivery_addr_id = Address.create_new_address(
cursor, office_arr['deliveryAddress'])
mailing_addr_id = Address.create_new_address(
cursor, office_arr['mailingAddress'])
office_desc = (office_type.replace('O', ' O')).title()
office_code = Office.OFFICE_TYPES_CODES[office_type]
# update office table to include new addresses
Office.update_office(cursor, event_id, corp_num, delivery_addr_id,
mailing_addr_id, office_code)
# create new ledger text for address change
cls._add_ledger_text(cursor, event_id, text % (office_desc), user_id)
def create_new_director(cls, cursor, event_id: int = None, director: dict = None, business: dict = None):
"""Insert new director into the corp_party table."""
if not event_id:
current_app.logger.error('Error in director: No event id given to create director.')
if not director:
current_app.logger.error('Error in director: No director data given to create director.')
# create new address
delivery_addr_id = Address.create_new_address(cursor=cursor, address_info=director['deliveryAddress'])
mailing_addr_id = delivery_addr_id
if 'mailingAddress' in director:
mailing_addr_id = Address.create_new_address(cursor=cursor, address_info=director['mailingAddress'])
# create new corp party entry
try:
cursor.execute("""select noncorp_party_seq.NEXTVAL from dual""")
row = cursor.fetchone()
corp_party_id = int(row[0])
except Exception as err:
current_app.logger.error('Error in director: Failed to get next corp_party_id.')
raise err
try:
cursor.execute("""
insert into corp_party (corp_party_id, mailing_addr_id, delivery_addr_id, corp_num, party_typ_cd,
start_event_id, end_event_id, appointment_dt, cessation_dt, last_nme, middle_nme, first_nme,
bus_company_num)
values (:corp_party_id, :mailing_addr_id, :delivery_addr_id, :corp_num, :party_typ_cd, :start_event_id,
:end_event_id, TO_DATE(:appointment_dt, 'YYYY-mm-dd'), TO_DATE(:cessation_dt, 'YYYY-mm-dd'), :last_nme,
:middle_nme, :first_nme, :bus_company_num)
""",
corp_party_id=corp_party_id,
mailing_addr_id=mailing_addr_id,
delivery_addr_id=delivery_addr_id,
corp_num=business['business']['identifier'],
party_typ_cd='DIR',
start_event_id=event_id,
end_event_id=event_id if director['cessationDate'] else None,
appointment_dt=str(datetime.datetime.strptime(director['appointmentDate'], '%Y-%m-%d'))[:10],
cessation_dt=str(datetime.datetime.strptime(director['cessationDate'], '%Y-%m-%d'))[:10]
if director['cessationDate'] else None,
last_nme=director['officer']['lastName'],
middle_nme=director['officer'].get('middleInitial', ''),
first_nme=director['officer']['firstName'],
bus_company_num=business['business']['businessNumber']
)
except Exception as err:
current_app.logger.error(f'Error in director: Failed create new director for event {event_id}')
raise err
return corp_party_id
def create_new_office(cls, cursor, addresses: Dict, event_id: str,
corp_num: str, office_type: str):
"""Create row for new office and end old one.
addresses.keys() = ['deliveryAddress', 'mailingAddress']
"""
delivery_addr_id = Address.create_new_address(
cursor, addresses['deliveryAddress'], corp_num=corp_num)
mailing_addr_id = Address.create_new_address(
cursor, addresses['mailingAddress'], corp_num=corp_num)
office_code = Office.OFFICE_TYPES_CODES[office_type]
# update office table to include new addresses and end old office
update_dict = {
'new_event_id': event_id,
'corp_num': corp_num,
'new_delivery_addr_id': delivery_addr_id,
'new_mailing_addr_id': mailing_addr_id,
'office_code': office_code
}
Office.update_office(cursor=cursor, office_info=update_dict)
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 create_new_corp_party(cls,
cursor,
event_id: int = None,
party: dict = None,
business: dict = None):
"""Insert new party into the corp_party table."""
query = """
insert into corp_party (corp_party_id, mailing_addr_id, delivery_addr_id, corp_num, party_typ_cd,
start_event_id, end_event_id, appointment_dt, cessation_dt, last_nme, middle_nme, first_nme,
bus_company_num, business_nme)
values (:corp_party_id, :mailing_addr_id, :delivery_addr_id, :corp_num, :party_typ_cd, :start_event_id,
:end_event_id, TO_DATE(:appointment_dt, 'YYYY-mm-dd'), TO_DATE(:cessation_dt, 'YYYY-mm-dd'), :last_nme,
:middle_nme, :first_nme, :bus_company_num, :business_name)
"""
completing_party_query = """
insert into completing_party (event_id, mailing_addr_id, last_nme,
middle_nme, first_nme, email_req_address)
values (:event_id, :mailing_addr_id, :last_nme,
:middle_nme, :first_nme, :email)
"""
if not event_id:
current_app.logger.error(
'Error in corp_party: No event id given to create party.')
if not party:
current_app.logger.error(
'Error in corp_party: No party data given to create party.')
# create new corp party entry
corp_num = business['business']['identifier']
try:
if corp_num == 'CP':
cursor.execute(
"""select noncorp_party_seq.NEXTVAL from dual""")
row = cursor.fetchone()
corp_party_id = int(row[0])
else:
cursor.execute("""
SELECT id_num
FROM system_id
WHERE id_typ_cd = 'CP'
FOR UPDATE
""")
corp_party_id = int(cursor.fetchone()[0])
if corp_party_id:
cursor.execute("""
UPDATE system_id
SET id_num = :new_num
WHERE id_typ_cd = 'CP'
""",
new_num=corp_party_id + 1)
except Exception as err:
current_app.logger.error(
'Error in corp_party: Failed to get next corp_party_id.')
raise err
try:
role_type = party.get('role_type', 'DIR')
delivery_info = party[
'deliveryAddress'] if 'deliveryAddress' in party else party[
'mailingAddress']
# create new address
delivery_addr_id = Address.create_new_address(
cursor=cursor, address_info=delivery_info, corp_num=corp_num)
mailing_addr_id = delivery_addr_id
if 'mailingAddress' in party:
mailing_addr_id = Address.create_new_address(
cursor=cursor,
address_info=party['mailingAddress'],
corp_num=corp_num)
if role_type == 'CPRTY':
cursor.execute(completing_party_query,
event_id=event_id,
mailing_addr_id=mailing_addr_id,
last_nme=party['officer']['lastName'],
middle_nme=party['officer'].get(
'middleInitial', ''),
first_nme=party['officer']['firstName'],
email=party['officer']['email'])
else:
cursor.execute(
query,
corp_party_id=corp_party_id,
mailing_addr_id=mailing_addr_id,
delivery_addr_id=delivery_addr_id,
corp_num=corp_num,
party_typ_cd=role_type,
start_event_id=event_id,
end_event_id=event_id
if party.get('cessationDate', '') else None,
appointment_dt=str(
datetime.datetime.strptime(party['appointmentDate'],
'%Y-%m-%d'))[:10],
cessation_dt=str(
datetime.datetime.strptime(party['cessationDate'],
'%Y-%m-%d'))[:10]
if party.get('cessationDate', None) else None,
last_nme=party['officer']['lastName'],
middle_nme=party['officer'].get('middleInitial', ''),
first_nme=party['officer']['firstName'],
bus_company_num=business['business'].get(
'businessNumber', None),
business_name=party['officer'].get('orgName', ''),
)
except Exception as err:
current_app.logger.error(
f'Error in corp_party: Failed create new party for event {event_id}'
)
raise err
return corp_party_id
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()
user_id = 'COOPER' if corp_num[:2] == 'CP' 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
for office_type in filing.body['offices']:
office_arr = filing.body['offices'][office_type]
delivery_addr_id = Address.create_new_address(
cursor, office_arr['deliveryAddress'])
mailing_addr_id = Address.create_new_address(
cursor, office_arr['mailingAddress'])
office_desc = (office_type.replace('O', ' O')).title()
office_code = Office.OFFICE_TYPES_CODES[office_type]
# update office table to include new addresses
Office.update_office(cursor, event_id, corp_num,
delivery_addr_id, mailing_addr_id,
office_code)
# create new ledger text for address change
cls._add_ledger_text(cursor, event_id,
f'Change to the {office_desc}.',
user_id)
# 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']:
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.',
user_id=user_id)
# update corporation record
Business.update_corporation(cursor=cursor, corp_num=corp_num)
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
