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