def auth_check():
"""Check for authentication, and also warm up the database."""
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,
)
# Include a database health check at this point, because it greatly improves query
# perf in the next minute or so, if the database is warmed up.
try:
if current_app.config.get('IS_ORACLE'):
db.engine.execute('SELECT 1 FROM CORP_PARTY WHERE ROWNUM = 1')
else:
db.engine.execute('SELECT 1')
except exc.SQLAlchemyError:
return {
'message': 'authorized, but api is down'
}, HTTPStatus.SERVICE_UNAVAILABLE
# made it here, so all checks passed
return {'message': 'authorized, api is healthy'}, HTTPStatus.OK
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 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 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})