def _get_cod(cls, cursor, identifier: str = None, filing_event_info: dict = None): """Get change of directors filing.""" director_objs = Director.get_by_event(cursor, identifier, filing_event_info['event_id']) if len(director_objs) < 3: current_app.logger.error( 'Less than 3 directors for {}'.format(identifier)) # 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 = { 'directors': [x.as_dict() for x in director_objs], 'eventId': filing_event_info['event_id'] } filing_obj.filing_type = 'changeOfDirectors' filing_obj.paper_only = False filing_obj.effective_date = effective_date return filing_obj
def _get_ar(cls, identifier: str = None, filing_event_info: dict = None): """Return annual report filing.""" # get directors and registered office as of this filing director_events = cls._get_events( identifier=identifier, filing_type_code=filing_event_info['filing_type_code']) office_events = cls._get_events( identifier=identifier, filing_type_code=filing_event_info['filing_type_code']) 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 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.body = { 'annualGeneralMeetingDate': agm_date, 'annualReportDate': ar_date, 'directors': directors, 'eventId': filing_event_info['event_id'], **reg_office } filing_obj.filing_type = 'annualReport' return filing_obj
def _build_parties_list(cls, cursor, event_id: int = None): parties = cursor.fetchall() if not parties: return None completing_parties = {} party_list = [] description = cursor.description for row in parties: party = Party() party.title = '' row = dict(zip([x[0].lower() for x in description], row)) if row['appointment_dt']: party.officer = cls._get_officer(row) party.delivery_address = Address.get_by_address_id( cursor, row['delivery_addr_id']).as_dict() party.mailing_address = Address.get_by_address_id(cursor, row['mailing_addr_id']).as_dict() \ if row['mailing_addr_id'] else party.delivery_address party.appointment_date =\ convert_to_json_date(row.get('appointment_dt', None)) party.cessation_date = convert_to_json_date( row.get('cessation_dt', None)) party.start_event_id = (row.get('start_event_id', '')) or '' party.end_event_id = (row.get('end_event_id', '')) or '' party.role_type = (row.get('party_typ_cd', '')) or '' # this is in case the party was not ceased during this event if event_id and party.end_event_id and party.end_event_id > event_id: party.cessation_date = None party_list.append(party) if event_id: completing_parties = cls.get_completing_parties(cursor, event_id) return cls.group_parties(party_list, completing_parties)
def _build_directors_list(cls, cursor, event_id: int = None): directors = cursor.fetchall() if not directors: return None directors_list = [] description = cursor.description for row in directors: director = Director() director.title = '' row = dict(zip([x[0].lower() for x in description], row)) if row['appointment_dt']: director.officer = {'firstName': row['first_nme'].strip() if row['first_nme'] else '', 'lastName': row['last_nme'].strip() if row['last_nme'] else '', 'middleInitial': row['middle_nme'] if row['middle_nme'] else ''} director.delivery_address = Address.get_by_address_id(cursor, row['delivery_addr_id']).as_dict() director.mailing_address = Address.get_by_address_id(cursor, row['mailing_addr_id']).as_dict() \ if row['mailing_addr_id'] else director.delivery_address director.appointment_date =\ convert_to_json_date(row['appointment_dt']) if row['appointment_dt'] else None director.cessation_date = convert_to_json_date(row['cessation_dt']) if row['cessation_dt'] else None director.start_event_id = row['start_event_id'] if row['start_event_id'] else '' director.end_event_id = row['end_event_id'] if row['end_event_id'] else '' # this is in case the director was not ceased during this event if event_id and director.end_event_id and director.end_event_id > event_id: director.cessation_date = None directors_list.append(director) return directors_list
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_ar(cls, cursor, identifier: str = None, filing_event_info: dict = None): """Return annual report filing.""" # get directors and registered office as of this filing director_events = cls._get_events(identifier=identifier, filing_type_code='OTCDR', cursor=cursor) office_events = cls._get_events(identifier=identifier, filing_type_code='OTADD', cursor=cursor) 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'] recreated_dirs_and_office = True if director_event_id: try: directors = Party.get_by_event(identifier=identifier, event_id=director_event_id, cursor=cursor) except: # noqa B901; pylint: disable=bare-except; # should only get here if agm was before the bob date recreated_dirs_and_office = False directors = Party.get_current(identifier=identifier, cursor=cursor) else: directors = Party.get_current(identifier=identifier, cursor=cursor) directors = [x.as_dict() for x in directors] if office_event_id: try: office_obj_list = (Office.get_by_event(event_id=office_event_id, cursor=cursor)).as_dict() offices = Office.convert_obj_list(office_obj_list) except: # noqa B901; pylint: disable=bare-except; # should only get here if agm was before the bob date recreated_dirs_and_office = False office_obj_list = Office.get_current(identifier=identifier, cursor=cursor) offices = Office.convert_obj_list(office_obj_list) else: office_obj_list = Office.get_current(identifier=identifier, cursor=cursor) offices = Office.convert_obj_list(office_obj_list) filing_obj = Filing() filing_obj.body = { 'annualGeneralMeetingDate': convert_to_json_date(filing_event_info.get('agm_date', None)), 'annualReportDate': convert_to_json_date(filing_event_info['period_end_dt']), 'directors': directors, 'eventId': filing_event_info['event_id'], 'offices': offices } filing_obj.filing_type = 'annualReport' filing_obj.paper_only = not recreated_dirs_and_office filing_obj.effective_date = filing_event_info['period_end_dt'] return filing_obj
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 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_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_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 _build_parties_list(cls, cursor, corp_num: str, event_id: int = None) -> Optional[List]: parties = cursor.fetchall() if not parties: return None completing_parties = {} party_list = [] description = cursor.description for row in parties: party = Party() party.title = '' row = dict(zip([x[0].lower() for x in description], row)) if not row['appointment_dt']: row['appointment_dt'] = Business.get_founding_date( cursor=cursor, corp_num=corp_num) party.officer = cls._get_officer(row) if (row.get('party_typ_cd', None) == cls.role_types['Director'] ) and not row['delivery_addr_id']: current_app.logger.error( f"Bad director data for {party.officer.get('firstName')} {party.officer.get('lastName')} {corp_num}" ) else: if row['delivery_addr_id']: party.delivery_address = Address.get_by_address_id( cursor, row['delivery_addr_id']).as_dict() party.mailing_address = Address.get_by_address_id(cursor, row['mailing_addr_id']).as_dict() \ if row['mailing_addr_id'] else party.delivery_address party.appointment_date =\ convert_to_json_date(row.get('appointment_dt', None)) party.cessation_date = convert_to_json_date( row.get('cessation_dt', None)) party.start_event_id = (row.get('start_event_id', '')) or '' party.end_event_id = (row.get('end_event_id', '')) or '' party.role_type = (row.get('party_typ_cd', '')) or '' party.corp_party_id = row.get('corp_party_id', None) party_list.append(party) if event_id: completing_parties = cls.get_completing_parties(cursor, event_id) return cls.group_parties(party_list, completing_parties)
def get_resolutions(cls, cursor, corp_num: str) -> List: """Get all resolution dates for a company.""" try: resolution_dates = [] cursor.execute(""" select resolution_dt from resolution where corp_num = :corp_num and end_event_id is null order by resolution_dt desc """, corp_num=corp_num) resolution_oracle_dates = cursor.fetchall() for date in resolution_oracle_dates: resolution_dates.append(convert_to_json_date(date[0])) return resolution_dates except Exception as err: current_app.logger.error( f'Error looking up resolution dates for {corp_num}') raise err
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 _get_cod(cls, identifier: str = None, event_id: str = None, year: int = None): # pylint: disable=unused-argument; will use year later """Get change of directors filing.""" filing_obj = Filing() if event_id: filing_event_info = cls._get_filing_event_info( identifier=identifier, event_id=event_id, filing_type_cd1='OTCDR', filing_type_cd2='OTADR') else: filing_event_info = cls._get_filing_event_info( identifier=identifier, filing_type_cd1='OTCDR', filing_type_cd2='OTADR') director_objs = Director.get_by_event(identifier, filing_event_info['event_id']) if len(director_objs) < 3: current_app.logger.error( 'Less than 3 directors for {}'.format(identifier)) filing_obj.header = { 'date': convert_to_json_date(filing_event_info['event_timestmp']), 'name': 'changeOfDirectors', 'certifiedBy': filing_event_info['certifiedBy'], 'email': filing_event_info['email'] } filing_obj.body = { 'directors': [x.as_dict() for x in director_objs], 'eventId': filing_event_info['event_id'] } filing_obj.filing_type = 'changeOfDirectors' 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
def _get_ar(cls, identifier: str = None, filing_event_info: dict = None): """Return annual report filing.""" # get directors and registered office as of this filing director_events = cls._get_events(identifier=identifier, filing_type_code='OTCDR') office_events = cls._get_events(identifier=identifier, filing_type_code='OTADD') 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'] recreated_dirs_and_office = True 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 agm was before the bob date recreated_dirs_and_office = False 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 agm was before the bob date recreated_dirs_and_office = False 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 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']) # paper filings from colin don't enter in the agm_date and don't have no agm filings in the filings table if filing_event_info['user_id'] != 'COOPER' and not agm_date: agm_date = ar_date filing_obj = Filing() 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.paper_only = not recreated_dirs_and_office filing_obj.effective_date = filing_event_info['period_end_dt'] return filing_obj
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 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 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_by_identifier(cls, identifier: str, corp_types: List, con=None) -> Business: """Return a Business by identifier.""" business = None try: # get record if not con: con = DB.connection con.begin() cursor = con.cursor() cursor.execute(f""" select corp.corp_num, corp_typ_cd, recognition_dts, bn_15, can_jur_typ_cd, othr_juris_desc, filing.period_end_dt, last_agm_date, corp_op_state.full_desc as state, corp_state.state_typ_cd as corp_state from CORPORATION corp join CORP_STATE on CORP_STATE.corp_num = corp.corp_num and CORP_STATE.end_event_id is null join CORP_OP_STATE on CORP_OP_STATE.state_typ_cd = CORP_STATE.state_typ_cd left join JURISDICTION on JURISDICTION.corp_num = corp.corp_num join event on corp.corp_num = event.corp_num left join filing on event.event_id = filing.event_id and filing.filing_typ_cd in ('OTANN', 'ANNBC') where corp_typ_cd in ({stringify_list(corp_types)}) and corp.CORP_NUM=:corp_num order by filing.period_end_dt desc nulls last """, corp_num=identifier) business = cursor.fetchone() if not business: raise BusinessNotFoundException(identifier=identifier) # add column names to resultset to build out correct json structure and make manipulation below more robust # (better than column numbers) business = dict( zip([x[0].lower() for x in cursor.description], business)) # get all assumed, numbered/corporation, translation names corp_names = CorpName.get_current(cursor=cursor, corp_num=identifier) assumed_name = None corp_name = None for name_obj in corp_names: if name_obj.type_code == CorpName.TypeCodes.ASSUMED.value: assumed_name = name_obj.corp_name break elif name_obj.type_code in [ CorpName.TypeCodes.CORP.value, CorpName.TypeCodes.NUMBERED_CORP.value ]: corp_name = name_obj.corp_name # get last ledger date from EVENT table and add to business record # note - FILE event type is correct for new filings; CONVOTHER is for events/filings pulled over from COBRS cursor.execute(""" select max(EVENT_TIMESTMP) from EVENT where EVENT_TYP_CD in ('FILE', 'CONVOTHER') and CORP_NUM=:corp_num """, corp_num=identifier) last_ledger_timestamp = cursor.fetchone()[0] business['last_ledger_timestamp'] = last_ledger_timestamp # if this is an XPRO, get correct jurisdiction; otherwise, it's BC if business['corp_typ_cd'] == 'XCP': business['jurisdiction'] = business['can_jur_typ_cd'] if business['can_jur_typ_cd'] == 'OT': business['jurisdiction'] = business['othr_juris_desc'] else: business['jurisdiction'] = 'BC' # convert to Business object business_obj = Business() business_obj.business_number = business['bn_15'] business_obj.corp_name = assumed_name if assumed_name else corp_name business_obj.corp_num = business['corp_num'] business_obj.corp_state = business['corp_state'] business_obj.corp_type = business['corp_typ_cd'] business_obj.founding_date = convert_to_json_datetime( business['recognition_dts']) business_obj.jurisdiction = business['jurisdiction'] business_obj.last_agm_date = convert_to_json_date( business['last_agm_date']) business_obj.last_ar_date = convert_to_json_date(business['period_end_dt']) if business['period_end_dt'] \ else convert_to_json_date(business['last_agm_date']) business_obj.last_ledger_timestamp = convert_to_json_datetime( business['last_ledger_timestamp']) business_obj.status = business['state'] return business_obj 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_by_identifier(cls, identifier: str = None, con=None): # pylint: disable=too-many-statements; """Return a Business by identifier.""" business = None if not identifier: return None try: # get record if not con: con = DB.connection con.begin() cursor = con.cursor() cursor.execute(""" select corp.CORP_NUM as identifier, CORP_FROZEN_TYP_CD, corp_typ_cd type, filing.period_end_dt as last_ar_date, LAST_AR_FILED_DT as last_ar_filed_date, LAST_AGM_DATE, corp_op_state.full_desc as state, corp_state.state_typ_cd as corp_state, t_name.corp_nme as legal_name, t_assumed_name.CORP_NME as assumed_name, RECOGNITION_DTS as founding_date, BN_15 as business_number, CAN_JUR_TYP_CD, OTHR_JURIS_DESC from CORPORATION corp left join CORP_NAME t_name on t_name.corp_num = corp.corp_num and t_name.CORP_NAME_TYP_CD='CO' AND t_name.END_EVENT_ID is null left join CORP_NAME t_assumed_name on t_assumed_name.corp_num = corp.corp_num and t_assumed_name.CORP_NAME_TYP_CD='AS' AND t_assumed_name.END_EVENT_ID is null join CORP_STATE on CORP_STATE.corp_num = corp.corp_num and CORP_STATE.end_event_id is null join CORP_OP_STATE on CORP_OP_STATE.state_typ_cd = CORP_STATE.state_typ_cd left join JURISDICTION on JURISDICTION.corp_num = corp.corp_num join event on corp.corp_num = event.corp_num left join filing on event.event_id = filing.event_id and filing.filing_typ_cd = 'OTANN' where corp_typ_cd in ('CP', 'BC') and corp.CORP_NUM=:corp_num order by last_ar_date desc nulls last""", corp_num=identifier) business = cursor.fetchone() if not business: raise BusinessNotFoundException(identifier=identifier) # add column names to resultset to build out correct json structure and make manipulation below more robust # (better than column numbers) business = dict( zip([x[0].lower() for x in cursor.description], business)) # get last ledger date from EVENT table and add to business record # note - FILE event type is correct for new filings; CONVOTHER is for events/filings pulled over from COBRS # during initial data import for Coops. cursor.execute(""" select max(EVENT_TIMESTMP) as last_ledger_timestamp from EVENT where EVENT_TYP_CD in('FILE', 'CONVOTHER') and CORP_NUM = '{}'""". format(identifier)) last_ledger_timestamp = cursor.fetchone()[0] business['last_ledger_timestamp'] = last_ledger_timestamp # if this is an XPRO, get correct jurisdiction; otherwise, it's BC if business['type'] == 'XCP': business['jurisdiction'] = business['can_jur_typ_cd'] if business['can_jur_typ_cd'] == 'OT': business['jurisdiction'] = business['othr_juris_desc'] else: business['jurisdiction'] = 'BC' # set name if business['assumed_name']: business['legal_name'] = business['assumed_name'] # set status - In Good Standing if certain criteria met, otherwise use original value if business['state'] == 'Active' and \ business['last_ar_filed_date'] is not None and \ isinstance(business['last_ar_filed_date'], datetime) and \ business['last_agm_date'] is not None and isinstance(business['last_agm_date'], datetime): business['status'] = business['state'] if business['last_ar_filed_date'] > business['last_agm_date']: business['status'] = 'In Good Standing' else: business['status'] = business['state'] # convert dates and date-times to correct json format and convert to camel case for schema names business['foundingDate'] = convert_to_json_datetime( business['founding_date']) business['lastAgmDate'] = convert_to_json_date( business['last_agm_date']) business['lastArDate'] = convert_to_json_date(business['last_ar_date']) if business['last_ar_date'] \ else business['lastAgmDate'] business['lastLedgerTimestamp'] = convert_to_json_datetime( business['last_ledger_timestamp']) business['businessNumber'] = business['business_number'] business['corpState'] = business['corp_state'] business['legalName'] = business['legal_name'] business['legalType'] = business['type'] # remove unnecessary fields ( del business['can_jur_typ_cd'] del business['othr_juris_desc'] del business['assumed_name'] del business['state'] del business['business_number'] del business['corp_frozen_typ_cd'] del business['corp_state'] del business['founding_date'] del business['last_agm_date'] del business['last_ar_filed_date'] del business['last_ledger_timestamp'] del business['legal_name'] del business['type'] del business['last_ar_date'] # add cache_id todo: set to real value business['cacheId'] = 0 # convert to Business object business_obj = Business() business_obj.business = business return business_obj except Exception as err: # general catch-all exception current_app.logger.error(err.with_traceback(None)) # pass through exception to caller raise err