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})