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