def _get_offices_held_by_corp_party(corp_party_id):
results = CorpParty.get_offices_held_by_corp_party_id(corp_party_id)
if len(results) == 0:
return {'offices': [], 'sameAddr': [], 'sameNameAndCompany': []}
offices = []
for row in results:
result_dict = {}
result_dict['corpPartyId'] = int(row.corp_party_id)
result_dict['officerTypCd'] = row.officer_typ_cd
result_dict['shortDesc'] = row.short_desc
result_dict['appointmentDt'] = row.appointment_dt
result_dict[
'year'] = row.event_timestmp.year if row.event_timestmp else None
offices.append(result_dict)
same_addr = CorpParty.get_corp_party_at_same_addr(corp_party_id)
same_name_and_company = CorpParty.get_corp_party_same_name_at_same_addr(
corp_party_id)
results = {
'offices':
offices,
'sameAddr': [{
**s[0].as_dict(),
**{
'year': int(s[1].year)
}
} for s in same_addr if s[0].corp_party_id != int(corp_party_id)],
'sameNameAndCompany': [{
**s[0].as_dict(),
**{
'year': int(s[1].year)
}
} for s in same_name_and_company
if s[0].corp_party_id != int(corp_party_id)],
}
return results
def corpparty_search_export():
"""Export a list of CorpParty search results. Uses the same arguments as corpparty_search()."""
account_id = request.headers.get('X-Account-Id', None)
if not authorized(jwt, account_id):
return (
jsonify({
'message':
'User is not authorized to access Director Search'
}),
HTTPStatus.UNAUTHORIZED,
)
# Query string arguments
args = request.args
# Fetching results
results = CorpParty.search_corp_parties(args)
if current_app.config.get('IS_ORACLE'):
results = results.filter(literal_column('rownum') <= 500).yield_per(50)
else:
results = results.limit(500)
# Exporting to Excel
workbook = Workbook()
export_dir = '/tmp'
with NamedTemporaryFile(mode='w+b', dir=export_dir, delete=True):
sheet = workbook.active
for index, column_header in enumerate(
_get_corp_party_export_column_headers(args), start=1):
_ = sheet.cell(column=index, row=1, value=column_header)
for row_index, row in enumerate(results, start=2):
for column_index, column_value in enumerate(
_get_corp_party_export_column_values(row, args), start=1):
_ = sheet.cell(column=column_index,
row=row_index,
value=column_value)
current_date = datetime.datetime.strftime(datetime.datetime.now(),
'%Y-%m-%d %H:%M:%S')
filename = 'Director Search Results {date}.xlsx'.format(
date=current_date)
workbook.save(filename='{dir}/{filename}'.format(dir=export_dir,
filename=filename))
return send_from_directory(export_dir, filename, as_attachment=True)
def corp_party_2param_search():
"""Corp party 2 params"""
args = ImmutableMultiDict([
('field', 'firstNme'),
('field', 'lastNme'),
('operator', 'startswith'),
('operator', 'startswith'),
('value', 'clark'),
('value', 'van'),
('mode', 'ALL'),
('additional_cols', 'none'),
('page', '1'),
('sort_type', 'dsc'),
('sort_value', 'lastNme'),
])
return (CorpParty.search_corp_parties(args).filter(
literal_column('rownum') <= 165))
def test_corp_party_search(session): # pylint: disable=unused-argument
"""Assert that CorpParty entities can be found by name."""
args = ImmutableMultiDict(
[
('field', 'lastNme'),
('operator', 'exact'),
('value', 'PATTERSON'),
('mode', 'ALL'),
('page', '1'),
('sort_type', 'dsc'),
('sort_value', 'lastNme'),
('additional_cols', 'none'),
]
)
results = CorpParty.search_corp_parties(args).limit(50)
assert results.count() == 2
assert results[0].last_nme == 'PATTERSON'
assert results[1].last_nme == 'Patterson'
def corp_party_postal_cd_search():
"""Search Corp Party by Postal Code"""
# Notes -
# Ideally we'd like an index of UPPER(TRIM(POSTAL_CD)) for address.
# Instead, the implementation is to try the 2 common versions. Uppercase with a space and
# uppercase without.
args = ImmutableMultiDict([
('field', 'postalCd'),
('operator', 'exact'),
('value', 'V0H2B0'),
('mode', 'ALL'),
('page', '1'),
('sort_type', 'dsc'),
('sort_value', 'lastNme'),
('additional_cols', 'none'),
])
return (CorpParty.search_corp_parties(args).filter(
literal_column('rownum') <= 165))
def corp_party_addr_search():
"""Search Corp Party by Address"""
args = ImmutableMultiDict([
# ('field', 'firstNme'),
# ('operator', 'exact'),
# ('value', 'donna'),
('field', 'addrLine1'),
('operator', 'contains'),
('value', '123 main st'),
('mode', 'ALL'),
('page', '1'),
('sort_type', 'dsc'),
('sort_value', 'lastNme'),
('additional_cols', 'none'),
])
return (CorpParty.search_corp_parties(args).filter(
literal_column('rownum') <= literal_column('165')))
def corp_party_nickname_search():
"""Search Corp Party by nickname"""
args = ImmutableMultiDict([
('field', 'firstNme'),
('field', 'lastNme'),
('operator', 'nicknames'),
('operator', 'exact'),
('value', 'john'),
('value', 'smith'),
('mode', 'ALL'),
('additional_cols', 'none'),
('page', '1'),
('sort_type', 'dsc'),
('sort_value', 'lastNme'),
])
return (CorpParty.search_corp_parties(args).filter(
literal_column('rownum') <= 165))
def populate_corps(): # pylint: disable=too-many-locals
"""
Generate some mock corporation filings
"""
office_types = ['BC']
officer_types = ['SEC', 'DIR', 'INC']
corp_op_states = ['ACT', 'HIS']
index = 0
while index < len(CORP_NUMS):
print(index)
default_date = datetime.datetime.now() + datetime.timedelta(weeks=-(1 + index))
# ADDRESS
address_array = ADDRESSES[index].split(',')
address = Address(
addr_id=index,
province=address_array[2].strip(),
postal_cd=address_array[3].strip(),
addr_line_1=address_array[0].strip(),
city=address_array[1].strip(),
)
db.session.add(address)
# CORPORATION
corporation = Corporation(
corp_num=CORP_NUMS[index],
recognition_dts=default_date,
corp_typ_cd=CORP_TYP_CDS[index % 11],
)
db.session.add(corporation)
# CORPNAME
corp_name2 = CorpName(
corp_num=CORP_NUMS[index],
corp_name_seq_num=2,
corp_nme=CORP_NAMES[index],
corp_name_typ_cd='CO',
)
db.session.add(corp_name2)
# CORPSTATE
corp_state = CorpState(
corp_num=CORP_NUMS[index],
state_typ_cd=corp_op_states[index % 2],
start_event_id=1,
)
db.session.add(corp_state)
# OFFICE
office = Office(
corp_num=CORP_NUMS[index],
office_typ_cd=office_types[0],
start_event_id=index,
mailing_addr_id=index,
delivery_addr_id=index,
)
db.session.add(office)
# CORPPARTY
corp_party_name = CORP_PARTY_NAMES[index].split(' ')
appointment_date = default_date
cessation_date = default_date
party_types = ['FIO', 'DIR']
corp_party = CorpParty(
corp_party_id=(index),
party_typ_cd=(party_types[index % 2]),
corp_num=CORP_NUMS[index],
first_nme=corp_party_name[0],
middle_nme=corp_party_name[1] if len(corp_party_name) > 1 else None,
last_nme=corp_party_name[2]
if len(corp_party_name) > 2
else corp_party_name[1],
appointment_dt=appointment_date,
cessation_dt=cessation_date,
mailing_addr_id=index,
delivery_addr_id=index,
start_event_id=index,
)
db.session.add(corp_party)
# EVENT
event = Event(
event_id=index,
corp_num=None,
event_type_cd=None,
event_timestmp=None,
trigger_dts=None,
)
db.session.add(event)
# FILING
filing = Filing(
event_id=index,
filing_typ_cd=FILING_TYPES[index % 4]['filing_typ_cd'],
effective_dt=None,
change_dt=None,
registration_dt=None,
period_end_dt=None,
accession_num=None,
arrangement_ind=None,
auth_sign_dt=None,
withdrawn_event_id=None,
ods_typ_cd=None,
dd_event_id=None,
access_cd=None,
nr_num=None,
court_appr_ind=None,
court_order_num=None,
agm_date=None,
new_corp_num=None,
)
db.session.add(filing)
# OFFICESHELD
officer_type_base1 = officer_types[0]
officer_type_base2 = officer_types[0]
if index % 3 == 0:
officer_type_base1 = officer_types[0]
officer_type_base2 = officer_types[1]
elif index % 3 == 1:
officer_type_base1 = officer_types[0]
officer_type_base2 = officer_types[2]
elif index % 3 == 2:
officer_type_base1 = officer_types[1]
officer_type_base2 = officer_types[2]
offices_held1 = OfficesHeld(
corp_party_id=index, officer_typ_cd=officer_type_base1,
)
db.session.add(offices_held1)
offices_held2 = OfficesHeld(
corp_party_id=index, officer_typ_cd=officer_type_base2,
)
db.session.add(offices_held2)
index = index + 1
db.session.commit()
def corpparty_search():
"""Search for CorpParty entities.
This function takes any number of field triples in the following format:
- field={field name}
- operator={'exact', 'contains', 'startswith', 'nicknames', 'similar' or 'endswith'}
- value={search keyword}
To include Address or CorpOpState info in the search results, set the additional_cols
field to 'addr' or 'active', respectively.
- additional_cols={'none', 'addr', or 'active'}
In addition, the following arguments are provided:
- page={page number}
- sort_type={'asc' or 'dsc'}
- sort_value={field name to sort results by}
"""
current_app.logger.info('Starting director search')
account_id = request.headers.get('X-Account-Id', None)
if not authorized(jwt, account_id):
return (
jsonify({
'message':
'User is not authorized to access Director Search'
}),
HTTPStatus.UNAUTHORIZED,
)
current_app.logger.info(
'Authorization check finished; starting query {query}'.format(
query=request.url))
args = request.args
fields = args.getlist('field')
additional_cols = args.get('additional_cols')
try:
results = CorpParty.search_corp_parties(args)
except BadSearchValue as e:
return {'results': [], 'error': 'Invalid search: {}'.format(str(e))}
current_app.logger.info('Before query')
# Pagination
page = int(args.get('page')) if 'page' in args else 1
per_page = 50
# Manually paginate results, because flask-sqlalchemy's paginate() method counts the total,
# which is slow for large tables. This has been addressed in flask-sqlalchemy but is unreleased.
# Ref: https://github.com/pallets/flask-sqlalchemy/pull/613
# results = results.limit(per_page).offset((page - 1) * per_page).all()
# update:
# We've switched to using ROWNUM rather than pagination, for performance reasons.
# for benchmarking, dump the query here and copy to benchmark.py
# This means queries with more than 500 results are invalid.
# from sqlalchemy.dialects import oracle
# results = results.limit(per_page).offset((page - 1) * per_page).all()
# oracle_dialect = oracle.dialect(max_identifier_length=30)
if current_app.config.get(
'IS_ORACLE'
): # raise Exception(results.statement.compile(dialect=oracle_dialect))
results = results.filter(
literal_column('rownum') <= 500).yield_per(per_page)
else:
results = results.limit(500)
current_app.logger.info('After query')
result_fields = [
'corpPartyId',
'firstNme',
'middleNme',
'lastNme',
'appointmentDt',
'cessationDt',
'corpNum',
'corpNme',
'partyTypCd',
]
corp_parties = []
index = 0
for row in results:
if (page - 1) * per_page <= index < page * per_page:
result_dict = {
key: getattr(row, convert_to_snake_case(key))
for key in result_fields
}
result_dict['corpPartyId'] = int(result_dict['corpPartyId'])
additional_result_columns = CorpParty.add_additional_cols_to_search_results(
additional_cols, fields, row)
result_dict = {**result_dict, **additional_result_columns}
corp_parties.append(result_dict)
index += 1
current_app.logger.info('Returning JSON results')
return jsonify({'results': corp_parties, 'num_results': index})
def get_corp_party_by_id(corp_party_id):
"""Get a CorpParty by id."""
account_id = request.headers.get('X-Account-Id', None)
if not authorized(jwt, account_id):
return (
jsonify({
'message':
'User is not authorized to access Director Search'
}),
HTTPStatus.UNAUTHORIZED,
)
result = CorpParty.get_corporation_info_by_corp_party_id(corp_party_id)
if not result:
return jsonify({
'message':
'Director with id {} could not be found.'.format(corp_party_id)
}), 404
person = result[0]
result_dict = {}
filing_description = CorpParty.get_filing_description_by_corp_party_id(
corp_party_id)
name = CorpName.get_corp_name_by_corp_id(person.corp_num)[0]
offices = Office.get_offices_by_corp_id(person.corp_num)
delivery_addr = Address.normalize_addr(person.delivery_addr_id)
mailing_addr = Address.normalize_addr(person.mailing_addr_id)
states = CorpState.get_corp_states_by_corp_id(person.corp_num)
corp_delivery_addr = ';'.join([
Address.normalize_addr(office.delivery_addr_id) for office in offices
])
corp_mailing_addr = ';'.join(
[Address.normalize_addr(office.mailing_addr_id) for office in offices])
result_dict['corpPartyId'] = int(person.corp_party_id)
result_dict['firstNme'] = person.first_nme
result_dict['middleNme'] = person.middle_nme
result_dict['lastNme'] = person.last_nme
result_dict['appointmentDt'] = person.appointment_dt
result_dict['cessationDt'] = person.cessation_dt
result_dict['corpNum'] = person.corp_num
result_dict['corpNme'] = name.corp_nme
result_dict['partyTypCd'] = person.party_typ_cd
result_dict['partyTypeDesc'] = PartyType.query.filter(
PartyType.party_typ_cd == person.party_typ_cd).one().short_desc
result_dict['corpPartyEmail'] = person.email_address
result_dict['deliveryAddr'] = delivery_addr
result_dict['mailingAddr'] = mailing_addr
result_dict['corpDeliveryAddr'] = corp_delivery_addr
result_dict['corpMailingAddr'] = corp_mailing_addr
result_dict['corpTypCd'] = result.corp_typ_cd
result_dict['corpAdminEmail'] = result.admin_email
result_dict['fullDesc'] = filing_description[
0].full_desc if filing_description else None
result_dict['states'] = [s.as_dict() for s in states]
offices_held = _get_offices_held_by_corp_party(corp_party_id)
incorporator_name_types = [
'APP',
'ATT',
'FBO',
'FCP',
'FIO',
'INC',
'LIQ',
'PAS',
'PSA',
'RAD',
'RAF',
'RAO',
'RCC',
'RCM',
'TAA',
'TAP',
]
if person.party_typ_cd in incorporator_name_types:
result_dict['businessNme'] = person.business_nme
return jsonify({**result_dict, **offices_held})
def test_corp_party_offices(session): # pylint: disable=unused-argument
"""Assert that offices held by CorpParty can be found."""
offices = CorpParty.get_offices_held_by_corp_party_id(1)
assert len(offices) == 2
def test_corp_party_same_addr(session): # pylint: disable=unused-argument
"""Assert that CorpParty entities at same address can be found."""
results = CorpParty.get_corp_party_at_same_addr(1)
assert results.count() == 1