def test_corporation_search(session): # pylint: disable=unused-argument
"""Assert that Corporations can be found by name or number."""
results = Corporation.query_corporations('Pembina Pipeline', 'corpNme', None, 'corp_num')
assert results.count() == 1
results = Corporation.query_corporations('Pembina', 'corpNme', None, 'corp_num')
assert results.count() == 1
results = Corporation.query_corporations('Pipeline', 'corpNme', None, 'corp_num')
assert results.count() == 1
def test_corporation(session):
"""Assert that an Entity can be stored in the service."""
corporation = Corporation(corp_num='BC1234567', recognition_dts=DEFAULT_DATE, corp_typ_cd='C')
session.add(corporation)
session.commit()
assert corporation.corp_num is not None
assert corporation.corp_typ_cd == 'C'
def corporations():
"""Search corporations"""
return (Corporation.search_corporations(
ImmutableMultiDict([
('query', 'countable'),
('page', '1'),
('sort_type', 'dsc'),
('sort_value', 'corpNme'),
])).filter(literal_column('rownum') <= 165))
def corporation(corp_id):
"""Get a single Corporation by corpNum."""
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
corp = Corporation.get_corporation_by_id(corp_id)
if not corp:
return jsonify({
'message':
'Corporation with id {} could not be found.'.format(corp_id)
}), 404
offices = Office.get_offices_by_corp_id(corp_id)
names = CorpName.get_corp_name_by_corp_id(corp_id)
output = {}
output['corpNum'] = corp.corp_num
output['transitionDt'] = corp.transition_dt
output['offices'] = []
for office in offices:
output['offices'].append({
# The company address isn't allowed to be displayed to Director Search users currently.
# 'deliveryAddr': Address.normalize_addr(office.delivery_addr_id),
# 'mailingAddr': Address.normalize_addr(office.mailing_addr_id),
'deliveryAddr':
'',
'mailingAddr':
'',
'officeTypCd':
_format_office_typ_cd(office.office_typ_cd),
'emailAddress':
office.email_address,
})
output['adminEmail'] = corp.admin_email
output['names'] = []
for row in names:
output['names'].append({'name': row.corp_nme})
return jsonify(output)
def corporation_search():
"""Search for Corporations by keyword or corpNum.
This function takes the following query arguments:
- query={search keyword}
- page={page number}
"""
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,
)
# args <- ImmutableMultiDict([('query', 'countable'), ('page', '1'), ('sort_type', 'dsc'),
# ('sort_value', 'corpNme')])
args = request.args
if not args.get('query'):
return 'No search query was received', 400
# Due to performance issues, exclude address.
results = Corporation.search_corporations(args, include_addr=False)
# Pagination
per_page = 50
page = int(args.get('page')) if 'page' in args else 1
# We've switched to using ROWNUM rather than pagination, for performance reasons.
# This means queries with more than 500 results are invalid.
# results = results.limit(50).offset((page - 1) * 50).all()
if current_app.config.get('IS_ORACLE'):
results = results.filter(literal_column('rownum') <= 500).yield_per(50)
else:
results = results.limit(500)
result_fields = [
'corpNum',
'corpNme',
'recognitionDts',
'corpTypCd',
'stateTypCd',
# Due to performance issues, exclude address.
# 'postalCd'
]
corporations = []
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
}
# Due to performance issues, exclude address.
result_dict['addr'] = '' # _merge_addr_fields(row)
corporations.append(result_dict)
index += 1
return jsonify({'results': corporations, 'num_results': index})
def corporation_search_export():
"""Export a set of Corporation search results to Excel (.xlsx).
Uses the same parameters as corporation_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 = Corporation.search_corporations(args, include_addr=False)
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
# Sheet headers (first row)
_ = sheet.cell(column=1, row=1, value='Inc/Reg #')
_ = sheet.cell(column=2, row=1, value='Entity Type')
_ = sheet.cell(column=3, row=1, value='Company Name')
_ = sheet.cell(column=4, row=1, value='Incorporated')
_ = sheet.cell(column=5, row=1, value='Company Status')
# _ = sheet.cell(column=6, row=1, value='Company Address')
# _ = sheet.cell(column=7, row=1, value='Postal Code')
index = 2
for row in results:
# Corporation.corp_num
_ = sheet.cell(column=1, row=index, value=row.corp_num)
# Corporation.corp_typ_cd
_ = sheet.cell(column=2, row=index, value=row.corp_typ_cd)
# CorpName.corp_nme
_ = sheet.cell(column=3, row=index, value=row.corp_nme)
# Corporation.recognition_dts
_ = sheet.cell(column=4, row=index, value=row.recognition_dts)
# CorpOpState.state_typ_cd
_ = sheet.cell(column=5, row=index, value=row.state_typ_cd)
# The company address isn't allowed to be displayed to Director Search users currently.
# Address.addr_line_1, Address.addr_line_2, Address.addr_line_3
# _ = sheet.cell(column=6, row=index, value=_merge_addr_fields(row))
# Address.postal_cd
# _ = sheet.cell(column=7, row=index, value=row.postal_cd)
index += 1
current_date = datetime.datetime.strftime(datetime.datetime.now(),
'%Y-%m-%d %H:%M:%S')
filename = 'Corporation Search Results {date}.xlsx'.format(
date=current_date)
full_filename_path = '{dir}/{filename}'.format(dir=export_dir,
filename=filename)
workbook.save(filename=full_filename_path)
return send_from_directory(export_dir, filename, as_attachment=True)
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()