def post(identifier, **kwargs): # pylint: disable=unused-argument; filing_type is only used for the get
"""Create a new filing."""
try:
json_data = request.get_json()
if not json_data:
return jsonify({'message': 'No input data provided'}), 400
# validate schema
is_valid, errors = validate(json_data, 'filing', validate_schema=True)
if not is_valid:
for err in errors:
print(err.message)
return jsonify(
{'message': 'Error: Invalid Filing schema'}), 400
json_data = json_data.get('filing', None)
filing_list = {'changeOfAddress': json_data.get('changeOfAddress', None),
'changeOfDirectors': json_data.get('changeOfDirectors', None),
'annualReport': json_data.get('annualReport', None)}
# ensure that the business in the AR matches the business in the URL
if identifier != json_data['business']['identifier']:
return jsonify(
{'message': 'Error: Identifier in URL does not match identifier in filing data'}), 400
filings_added = []
for filing_type in filing_list:
if filing_list[filing_type]:
filing = Filing()
filing.business = Business.find_by_identifier(identifier)
filing.header = json_data['header']
filing.filing_type = filing_type
filing.body = filing_list[filing_type]
# add the new filing
event_id = Filing.add_filing(filing)
filings_added.append({'event_id': event_id, 'filing_type': filing_type})
# return the completed filing data
completed_filing = Filing()
completed_filing.header = json_data['header']
# get business info again - could have changed since filings were applied
completed_filing.business = Business.find_by_identifier(identifier)
completed_filing.body = {}
for filing_info in filings_added:
filing = Filing.find_filing(business=completed_filing.business, event_id=filing_info['event_id'],
filing_type=filing_info['filing_type'])
if not filing:
raise FilingNotFoundException(identifier=identifier, filing_type=filing_info['filing_type'],
event_id=filing_info['event_id'])
completed_filing.body.update({filing_info['filing_type']: filing.body})
return jsonify(completed_filing.as_dict()), 201
except Exception as err: # pylint: disable=broad-except; want to catch all errors
# general catch-all exception
current_app.logger.error(err.with_traceback(None))
return jsonify(
{'message': 'Error when trying to retrieve business record from COLIN'}), 500
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_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_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 _get_inc(cls,
cursor,
identifier: str = None,
filing_event_info: dict = None):
"""Get incorporation filing."""
# business_obj
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)
filing_obj = Filing()
filing_obj.body = {
'offices': offices,
'eventId': filing_event_info['event_id']
}
filing_obj.filing_type = 'incorporationApplication'
filing_obj.paper_only = False
return filing_obj
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_con(cls, identifier: str = None, filing_event_info: dict = None):
"""Get change of name filing."""
name_obj = EntityName.get_by_event(identifier=identifier, event_id=filing_event_info['event_id'])
if not name_obj:
raise FilingNotFoundException(identifier=identifier, filing_type='change_of_name',
event_id=filing_event_info['event_id'])
filing_obj = Filing()
filing_obj.body = {
**name_obj.as_dict()
}
filing_obj.filing_type = 'changeOfName'
return filing_obj
def _get_coa(cls, identifier: str = None, filing_event_info: dict = None):
"""Get change of address filing."""
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=filing_event_info['event_id'])
filing_obj = Filing()
filing_obj.body = {
**registered_office_obj.as_dict(),
'eventId': filing_event_info['event_id']
}
filing_obj.filing_type = 'changeOfAddress'
return filing_obj
def _get_con(cls, cursor, identifier: str = None, filing_event_info: dict = None):
"""Get change of name filing."""
name_obj = EntityName.get_by_event(identifier=identifier, event_id=filing_event_info['event_id'], cursor=cursor)
if not name_obj:
raise FilingNotFoundException(identifier=identifier, filing_type='change_of_name',
event_id=filing_event_info['event_id'])
filing_obj = Filing()
filing_obj.body = {
**name_obj.as_dict()
}
filing_obj.filing_type = 'changeOfName'
filing_obj.paper_only = False
filing_obj.effective_date = filing_event_info['event_timestmp']
return filing_obj
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 _add_filing(cls, cursor, event_id, filing, 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 filing: (obj) Filing data object
:param date: (str) period_end_date
:param filing_type_code: (str) filing type code
"""
if not filing_type_code:
raise FilingNotFoundException(filing.business.business['identifier'], filing.filing_type)
try:
if filing_type_code is 'OTANN':
cursor.execute("""
INSERT INTO filing (event_id, filing_typ_cd, effective_dt, period_end_dt, agm_date)
VALUES (:event_id, :filing_type_code, sysdate, TO_DATE(:period_end_date, 'YYYY-mm-dd'),
TO_DATE(:agm_date, 'YYYY-mm-dd'))
""",
event_id=event_id,
filing_type_code=filing_type_code,
period_end_date=date,
agm_date=date
)
elif filing_type_code is 'OTADD':
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=date,
)
except Exception as err:
current_app.logger.error(err.with_traceback(None))
raise err
def _get_inc(cls, cursor, identifier: str = None, filing_event_info: dict = None):
"""Get incorporation filing."""
# business_obj
office_obj_list = Office.get_by_event(cursor, filing_event_info['event_id'])
share_structure = ShareObject.get_all(cursor, identifier, filing_event_info['event_id'])
parties = Party.get_by_event(cursor, identifier, filing_event_info['event_id'], None)
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)
filing_obj = Filing()
filing_obj.body = {
'offices': offices,
'eventId': filing_event_info['event_id'],
'shareClasses': share_structure.to_dict()['shareClasses'],
'parties': [x.as_dict() for x in parties]
}
filing_obj.filing_type = 'incorporationApplication'
filing_obj.paper_only = False
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_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 post(identifier, **kwargs):
"""Create a new filing."""
# pylint: disable=unused-argument,too-many-branches; filing_type is only used for the get
try:
json_data = request.get_json()
if not json_data:
return jsonify({'message': 'No input data provided'}), 400
# validate schema
is_valid, errors = validate(json_data, 'filing', validate_schema=True)
if not is_valid:
for err in errors:
print(err.message)
return jsonify(
{'message': 'Error: Invalid Filing schema'}), 400
json_data = json_data.get('filing', None)
filing_list = {'changeOfAddress': json_data.get('changeOfAddress', None),
'changeOfDirectors': json_data.get('changeOfDirectors', None),
'annualReport': json_data.get('annualReport', None),
'incorporationApplication': json_data.get('incorporationApplication', None)}
# Filter out null-values in the filing_list dictionary
filing_list = {k: v for k, v in filing_list.items() if filing_list[k]}
# ensure that the business in the AR matches the business in the URL
if identifier != json_data['business']['identifier']:
return jsonify(
{'message': 'Error: Identifier in URL does not match identifier in filing data'}), 400
try:
# get db connection and start a session, in case we need to roll back
con = DB.connection
con.begin()
filings_added = FilingInfo._add_filings(con, json_data, filing_list, identifier)
# return the completed filing data
completed_filing = Filing()
completed_filing.header = json_data['header']
completed_filing.header['colinIds'] = []
search_corp_num = json_data['business']['identifier']
if search_corp_num[:2] != 'CP':
search_corp_num = search_corp_num[-7:]
# get business info again - could have changed since filings were applied
completed_filing.business = Business.find_by_identifier(search_corp_num, con)
completed_filing.body = {}
for filing_info in filings_added:
filing = Filing.get_filing(con=con, business=completed_filing.business,
event_id=filing_info['event_id'], filing_type=filing_info['filing_type'])
if not filing:
raise FilingNotFoundException(identifier=identifier, filing_type=filing_info['filing_type'],
event_id=filing_info['event_id'])
completed_filing.body.update({filing_info['filing_type']: filing.body})
completed_filing.header['colinIds'].append(filing_info['event_id'])
# success! commit the db changes
con.commit()
return jsonify(completed_filing.as_dict()), 201
except Exception as db_err:
current_app.logger.error(db_err.with_traceback(None))
if con:
con.rollback()
raise db_err
except Exception as err: # pylint: disable=broad-except; want to catch all errors
# general catch-all exception
current_app.logger.error(err.with_traceback(None))
return jsonify(
{'message': 'Error when trying to retrieve business record from COLIN'}), 500
def _get_filing_event_info(
cls, # pylint: disable=too-many-arguments,too-many-branches;
identifier: str = None,
event_id: str = None,
filing_type_cd1: str = None,
filing_type_cd2: str = 'empty',
year: int = None):
# build base querystring
querystring = ("""
select event.event_id, event_timestmp, first_nme, middle_nme, last_nme, email_addr, period_end_dt,
agm_date, effective_dt, event.corp_num
from event
join filing on filing.event_id = event.event_id
left join filing_user on event.event_id = filing_user.event_id
where (filing_typ_cd=:filing_type_cd1 or filing_typ_cd=:filing_type_cd2)
""")
if identifier:
querystring += ' AND event.corp_num=:identifier'
if event_id:
querystring += ' AND event.event_id=:event_id'
if year:
querystring += ' AND extract(year from PERIOD_END_DT)=:year'
querystring += ' order by EVENT_TIMESTMP desc'
try:
cursor = DB.connection.cursor()
if event_id:
if year:
cursor.execute(querystring,
identifier=identifier,
event_id=event_id,
filing_type_cd1=filing_type_cd1,
filing_type_cd2=filing_type_cd2,
year=year)
else:
cursor.execute(querystring,
identifier=identifier,
event_id=event_id,
filing_type_cd1=filing_type_cd1,
filing_type_cd2=filing_type_cd2)
else:
if year:
cursor.execute(querystring,
identifier=identifier,
filing_type_cd1=filing_type_cd1,
filing_type_cd2=filing_type_cd2,
year=year)
else:
cursor.execute(querystring,
identifier=identifier,
filing_type_cd1=filing_type_cd1,
filing_type_cd2=filing_type_cd2)
event_info = cursor.fetchone()
if not event_info:
raise FilingNotFoundException(identifier=identifier,
filing_type=filing_type_cd1,
event_id=event_id)
event_info = dict(
zip([x[0].lower() for x in cursor.description], event_info))
# build filing user name from first, middle, last name
filing_user_name = ' '.join(
filter(None, [
event_info['first_nme'], event_info['middle_nme'],
event_info['last_nme']
]))
filing_email = event_info['email_addr']
if not filing_user_name:
filing_user_name = 'N/A'
# if email is blank, set as empty tring
if not filing_email:
filing_email = '*****@*****.**'
event_info['certifiedBy'] = filing_user_name
event_info['email'] = filing_email
return event_info
except Exception as err:
if identifier:
current_app.logger.error(
'error getting filing event info for corp {}'.format(
identifier))
else:
current_app.logger.error(
'error getting filing event info for event {}'.format(
event_id))
raise err
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 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