def registration(date, reg_no): if "class_of_charge" in request.args: class_of_charge = request.args["class_of_charge"] else: class_of_charge = None cursor = connect(cursor_factory=psycopg2.extras.DictCursor) try: logging.audit(format_message("Retrieve entry details for %s, %s"), reg_no, date) details = get_registration_details(cursor, reg_no, date, class_of_charge) if details is not None: addl_info = get_additional_info(cursor, details) if addl_info is not None: details['additional_information'] = addl_info finally: complete(cursor) if details is None: logging.warning( format_message("Returning 404 for /registrations/{}/{}".format( date, reg_no))) return Response(status=404) else: return Response(json.dumps(details), status=200, mimetype='application/json')
def test_ltd_co_name_key(self): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) assert create_registration_key(cursor, {'type': 'Limited Company', 'company': 'Shakespeares Books Limited'})['key'] \ == 'SHAKESPEARESBOOKSLD' assert create_registration_key(cursor, {'type': 'Limited Company', 'company': 'Brown Bros & Company (Associated) Ltd'})['key'] \ == 'BROWNBROCOASSLD' assert create_registration_key(cursor, {'type': 'Limited Company', 'company': 'Jewel Builders Ltd'})['key'] \ == 'JEWELBUILDERLD' assert create_registration_key(cursor, {'type': 'Limited Company', 'company': 'A Green & Co Ltd'})['key'] \ == 'AGREENCOLD' assert create_registration_key(cursor, {'type': 'Limited Company', 'company': 'Messrs Jones & Green Cyf'})['key'] \ == 'JONESGREENLD' assert create_registration_key(cursor, {'type': 'Limited Company', 'company': 'James and Edgar Public Ltd Cos'})['key'] \ == 'JAMESEDGARLD' assert create_registration_key(cursor, {'type': 'Limited Company', 'company': 'ABC Builders PLC'})['key'] \ == 'ABCBUILDERLD' # TODO: example says ARCBUILDERS, but the trailing S rule contradicts assert create_registration_key(cursor, {'type': 'Limited Company', 'company': 'D Evans Cwmni Cyf Cyhoeddus'})['key'] \ == 'DEVANSLD' complete(cursor)
def load_counties(): # pragma: no cover if not app.config['ALLOW_DEV_ROUTES']: # and is_dev_VM()): return Response(status=403) if request.headers['Content-Type'] != "application/json": logging.error(format_message('Content-Type is not JSON')) return Response(status=415) json_data = request.get_json(force=True) cursor = connect() try: for item in json_data: if 'cym' not in item: item['cym'] = None cursor.execute( 'INSERT INTO COUNTY (name, welsh_name) VALUES (%(e)s, %(c)s)', { 'e': item['eng'], 'c': item['cym'] }) complete(cursor) except: rollback(cursor) raise return Response(status=200)
def get_translated_county(county_name): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) try: counties = list() counties.append(county_name) cursor.execute( "SELECT name FROM COUNTY where UPPER(welsh_name) = %(n)s", {'n': county_name.upper()}) rows = cursor.fetchall() for row in rows: if row['name']: counties.append(row['name']) else: cursor.execute( "SELECT welsh_name FROM COUNTY where UPPER(name) = %(n)s", {'n': county_name.upper()}) rows = cursor.fetchall() for row in rows: if row['welsh_name']: counties.append(row['welsh_name']) finally: complete(cursor) return Response(json.dumps(counties), status=200, mimetype='application/json')
def clear_area_variants(): if not app.config['ALLOW_DEV_ROUTES']: # and is_dev_VM()): return Response(status=403) cursor = connect(cursor_factory=psycopg2.extras.DictCursor) cursor.execute('DELETE FROM county_search_keys') complete(cursor) return Response(status=200)
def get_applicant(request_id): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) try: applicant = get_applicant_detl(cursor, request_id) finally: complete(cursor) return Response(json.dumps(applicant), status=200, mimetype='application/json')
def test_varnam_a(self): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) key = create_registration_key(cursor, {'type': 'Other', 'other': 'Beauty Without Cruelty'}) assert key['indicator'] == 'A' assert key['key'] == 'BEAUTYWITHOUTCRUELTY' key = create_registration_key(cursor, {'type': 'Other', 'other': 'Carol Fayre'}) assert key['indicator'] == 'A' assert key['key'] == 'CAROLFAYRE' complete(cursor)
def update_request_fee(request_id, transaction_fee): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) cursor.execute( 'UPDATE request SET transaction_fee = %(fee)s ' 'WHERE id = %(request_id)s', { 'request_id': request_id, 'fee': transaction_fee }) logging.audit(format_message("Set transaction fee to %s for request %s"), transaction_fee, request_id) complete(cursor) return Response(status=200)
def test_county_council(self): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) key = create_registration_key( cursor, { 'type': 'County Council', 'local': { 'name': 'Nottinghamshire County Council', 'area': 'Nottinghamshire' } }) assert key['key'] == 'NOTTINGHA' complete(cursor)
def test_local_authority(self): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) key = create_registration_key( cursor, { 'type': 'Parish Council', 'local': { 'name': 'Over Parish Council', 'area': 'Over' } }) assert key['key'] == 'NULL KEY' complete(cursor)
def all_registrations(): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) try: logging.audit(format_message("Retrieve all registrations")) details = get_all_registrations(cursor) finally: complete(cursor) if details is None: logging.warning(format_message("Returning 404 for /registrations")) return Response(status=404) else: return Response(json.dumps(details), status=200, mimetype='application/json')
def multi_reg_check(registration_date, registration_no): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) try: details = get_multi_registrations(cursor, registration_date, registration_no) finally: complete(cursor) if details is None: # this is not a multi_reg application return Response(None, status=200, mimetype='application/json') else: return Response(json.dumps(details), status=200, mimetype='application/json')
def registrations_by_date(date): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) try: logging.audit(format_message("Retrieve registrations dated %s"), date) details = get_registrations_by_date(cursor, date) finally: complete(cursor) if details is None: logging.warning( format_message("Returning 404 for date {}".format(date))) return Response(status=404) else: return Response(json.dumps(details), status=200, mimetype='application/json')
def registration_history(date, reg_no): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) try: logging.audit(format_message("Retrieve entry history for %s, %s"), reg_no, date) details = get_registration_history(cursor, reg_no, date) finally: complete(cursor) if details is None: logging.warning("Returning 404") return Response(status=404) else: return Response(json.dumps(details), status=200, mimetype='application/json')
def test_varnam_a(self): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) key = create_registration_key(cursor, { 'type': 'Other', 'other': 'Beauty Without Cruelty' }) assert key['indicator'] == 'A' assert key['key'] == 'BEAUTYWITHOUTCRUELTY' key = create_registration_key(cursor, { 'type': 'Other', 'other': 'Carol Fayre' }) assert key['indicator'] == 'A' assert key['key'] == 'CAROLFAYRE' complete(cursor)
def get_searches_by_date(date): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) try: data = get_search_ids_by_date(cursor, date) finally: logging.audit(format_message("Retrieve searches for date: %s"), date) complete(cursor) if data is None: logging.warning( format_message("Returning 404 for date {}".format(date))) return Response(status=404) else: return Response(json.dumps(data), status=200, mimetype='application/json')
def create_search(): if request.headers['Content-Type'] != "application/json": logging.error(format_message('Content-Type is not JSON')) return Response(status=415) data = request.get_json(force=True) logging.debug('this is search data: %s', json.dumps(data)) errors = validate(data, SEARCH_SCHEMA) if errors is not None: return Response(json.dumps(errors), status=400) logging.debug(data['parameters']['search_type']) if data['parameters']['search_type'] not in ['full', 'banks']: message = "Invalid search type supplied: {}".format( data['parameters']['search_type']) logging.error(format_message(message)) return Response(message, status=400) cursor = connect(cursor_factory=psycopg2.extras.DictCursor) try: # Store the search request if 'X-LC-Username' in request.headers: data['user_id'] = request.headers['X-LC-Username'] search_request_id, search_details_id, search_data = store_search_request( cursor, data) # Run the queries results = perform_search(cursor, search_data['parameters'], search_data['search_date']) for item in results: store_search_result(cursor, search_request_id, search_details_id, item['name_id'], item['name_result']) logging.audit(format_message("Submit search request: %d, ID: %d"), search_request_id, search_details_id) complete(cursor) logging.info(format_message("Search request and result committed")) except: rollback(cursor) raise results = [search_request_id] if len(results) == 0: return Response(status=404) else: return Response(json.dumps(results, ensure_ascii=False), status=200, mimetype='application/json')
def set_area_variants(): if not app.config['ALLOW_DEV_ROUTES']: # and is_dev_VM()): return Response(status=403) data = json.loads(request.data.decode('utf-8')) cursor = connect(cursor_factory=psycopg2.extras.DictCursor) for item in data: cursor.execute( 'INSERT INTO county_search_keys (name, key, variant_of, county_council) ' 'VALUES( %(name)s, %(key)s, %(variant)s, %(county)s )', { 'name': item['name'], 'key': item['key'], 'variant': item['variant_of'], 'county': item['county_council'] }) complete(cursor) return Response(status=200)
def retrieve_office_copy(): class_of_charge = request.args['class'] reg_no = request.args['reg_no'] date = request.args['date'] cursor = connect(cursor_factory=psycopg2.extras.DictCursor) try: data = get_ins_office_copy(cursor, class_of_charge, reg_no, date) finally: logging.audit(format_message("Retrieve office copy for %s, %s (%s)"), reg_no, date, class_of_charge) complete(cursor) if data is None: return Response(data, status=404, mimetype='application/json') else: return Response(data, status=200, mimetype='application/json')
def delete_all_regs(): # pragma: no cover if not app.config['ALLOW_DEV_ROUTES']: # and is_dev_VM()): logging.warning("Non-Dev attempt to delete all data") return Response(status=403) cursor = connect(cursor_factory=psycopg2.extras.DictCursor) try: cursor.execute( "TRUNCATE party_address, address, address_detail, party_trading, party_name_rel, " "party, migration_status, register, detl_county_rel, register_details, audit_log, " "search_results, search_name, search_details, request, ins_bankruptcy_request, " "party_name, county") complete(cursor) except: rollback(cursor) raise return Response(status=200)
def get_search_type(request_id): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) # get all rows for this request id, if none contain results then search type is 'search_nr' try: sql = "Select result from search_results where request_id = %(request_id)s" cursor.execute(sql, {"request_id": request_id}) rows = cursor.fetchall() finally: logging.audit( format_message("Retrieve search results for request: %s"), request_id) complete(cursor) search_type = {'search_type': 'search nr'} for row in rows: if row['result']: search_type = {'search_type': 'search'} return Response(json.dumps(search_type), status=200, mimetype='application/json')
def validate_county_council(county_name): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) try: cursor.execute( "SELECT county_council FROM county_search_keys WHERE name=%(name)s", {'name': county_name.upper()}) rows = cursor.fetchall() if len(rows) != 1: return Response(status=404) print(rows[0]) if rows[0]['county_council'] is not True: return Response(status=404) return Response(status=200) finally: complete(cursor)
def get_request_details(request_id): request_type = get_request_type(request_id) cursor = connect(cursor_factory=psycopg2.extras.DictCursor) try: if request_type.lower() == 'search': data = get_search_request_details(request_id) else: # not a search - reg register details data = get_register_request_details(request_id) for index, row in enumerate( data): # Each AKA registration needs populating revealable = get_most_recent_revealable( cursor, row["registration_no"], row["registration_date"]) if revealable: if index < len(revealable['registrations']): details = get_registration_details( cursor, revealable['registrations'][index]['number'], revealable['registrations'][index]['date']) else: # if nothing came back from revealable details = get_registration_details( cursor, row["registration_no"], row["registration_date"]) if details is not None: if 'particulars' in details: if 'counties' in details['particulars']: if len(details['particulars']['counties']) > 1: county = get_county(cursor, row['registration_no'], row['registration_date']) details['particulars']['counties'] = county addl_info = get_additional_info(cursor, details) if addl_info is not None: details['additional_information'] = addl_info row['details'] = details finally: logging.audit(format_message("Retrieve request details for ID: %s"), request_id) complete(cursor) return Response(json.dumps(data), status=200, mimetype='application/json')
def get_searches(): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) try: if 'name' in request.args: name = request.args['name'] result = read_searches(cursor, name) elif 'id' in request.args: result = get_search_by_request_id(cursor, request.args['id']) else: result = [] data = [] for results in result: for ids in results['result']: reg_data = get_registration(cursor, ids, None) data.append({ 'reg_id': ids, 'reg_no': reg_data['registration_no'], 'reg_date': reg_data['registration_date'], 'class': reg_data['class_of_charge'] }) finally: if 'name' in request.args: logging.audit( format_message("Retrieve search results by name: %s"), request.args['name']) elif 'id' in request.args: logging.audit(format_message("Retrieve search results by ID: %s"), request.args['id']) else: logging.audit(format_message("Retrieve search results")) complete(cursor) return Response(json.dumps(data), status=200, mimetype='application/json')
def last_search(): if not app.config['ALLOW_DEV_ROUTES']: # and is_dev_VM()): return Response(status=403) cursor = connect(cursor_factory=psycopg2.extras.DictCursor) # get all rows for this request id, if none contain results then search type is 'search_nr' data = {} try: sql = "Select id as search_details_id, request_id, search_timestamp " \ "from search_details where id = (select max(id) from search_details) " cursor.execute(sql) rows = cursor.fetchall() finally: complete(cursor) for row in rows: data = { 'search_details_id': row['search_details_id'], 'request_id': row['request_id'], 'timestamp': str(row['search_timestamp']) } return Response(json.dumps(data), status=200, mimetype='application/json')
def test_varnam_b(self): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) key = create_registration_key(cursor, {'type': 'Other', 'other': 'John Brown and Bros Associated'}) assert key['indicator'] == 'B' assert key['key'] == 'JOHNBROWNBROASS' key = create_registration_key(cursor, {'type': 'Other', 'other': 'John Brown and Brothers Associated'}) assert key['indicator'] == 'B' assert key['key'] == 'JOHNBROWNBROASS' key = create_registration_key(cursor, {'type': 'Other', 'other': 'Bob Ross and Bros Associated'}) assert key['indicator'] == 'B' assert key['key'] == 'BOBROSSBROASS' key = create_registration_key(cursor, {'type': 'Other', 'other': 'Lollipops and Roses'}) assert key['indicator'] == 'B' assert key['key'] == 'LOLLIPOPSROSES' key = create_registration_key(cursor, {'type': 'Other', 'other': 'The Board of Governors Inc'}) assert key['indicator'] == 'B' assert key['key'] == 'NULL KEY' complete(cursor)
def registration_by_id(reg_id): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) try: logging.audit(format_message("Retrieve entry details for %s"), reg_id) details = get_registration_details_by_register_id(cursor, reg_id) if details is not None: addl_info = get_additional_info(cursor, details) if addl_info is not None: details['additional_information'] = addl_info finally: complete(cursor) if details is None: logging.warning( format_message( "Returning 404 for /registrations/{}".format(reg_id))) return Response(status=404) else: return Response(json.dumps(details), status=200, mimetype='application/json')
def court_ref_existence_check(ref): logging.debug("Court existence checking") cursor = connect(cursor_factory=psycopg2.extras.DictCursor) try: logging.audit(format_message("Retrieve details by court: %s"), ref) cursor.execute( "SELECT registration_no, date FROM register r, register_details rd " + "WHERE UPPER(rd.legal_body_ref)=%(body_ref)s " + "AND rd.id=r.details_id " "AND (r.expired_on is NULL OR r.expired_on > current_date) " "AND rd.cancelled_by is NULL " + "AND (UPPER(rd.amendment_type)!='CANCELLATION' or rd.amendment_type is NULL) ", {"body_ref": ref.upper()}) rows = cursor.fetchall() results = [] if len(rows) > 0: status_code = 200 for row in rows: details = get_registration_details(cursor, row['registration_no'], row['date']) debtor_name = details['parties'][0]['names'][0]['private'] name_string = " ".join( debtor_name['forenames']) + " " + debtor_name['surname'] results.append({ 'reg_no': details['registration']['number'], 'date': details['registration']['date'], 'class_of_charge': details['class_of_charge'], 'name': name_string }) else: status_code = 404 finally: complete(cursor) return Response(json.dumps(results), status=status_code, mimetype='application/json')
def test_varnam_b(self): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) key = create_registration_key(cursor, { 'type': 'Other', 'other': 'John Brown and Bros Associated' }) assert key['indicator'] == 'B' assert key['key'] == 'JOHNBROWNBROASS' key = create_registration_key( cursor, { 'type': 'Other', 'other': 'John Brown and Brothers Associated' }) assert key['indicator'] == 'B' assert key['key'] == 'JOHNBROWNBROASS' key = create_registration_key(cursor, { 'type': 'Other', 'other': 'Bob Ross and Bros Associated' }) assert key['indicator'] == 'B' assert key['key'] == 'BOBROSSBROASS' key = create_registration_key(cursor, { 'type': 'Other', 'other': 'Lollipops and Roses' }) assert key['indicator'] == 'B' assert key['key'] == 'LOLLIPOPSROSES' key = create_registration_key(cursor, { 'type': 'Other', 'other': 'The Board of Governors Inc' }) assert key['indicator'] == 'B' assert key['key'] == 'NULL KEY' complete(cursor)
def get_request_type(request_id): if not request_id: return None cursor = connect(cursor_factory=psycopg2.extras.DictCursor) # get all rows for this request id, if none contain results then search type is 'search_nr' try: cursor.execute( "Select application_type " "from request where id = %(request_id)s ", {"request_id": request_id}) rows = cursor.fetchall() finally: logging.audit(format_message("Retrieve request type for request: %s"), request_id) complete(cursor) data = "" if rows: for row in rows: data = row['application_type'] else: logging.error("could not find request " + request_id) return None return data
def renew_registration(): print("renewing...") if request.headers['Content-Type'] != "application/json": logging.error('Content-Type is not JSON') return Response(status=415) json_data = json.loads(request.data.decode('utf-8')) cursor = connect(cursor_factory=psycopg2.extras.DictCursor) try: row_count, reg_nos, request_id, originals = insert_renewal( json_data, get_username()) data = { "new_registrations": reg_nos, "amended_registrations": originals, "request_id": request_id } complete(cursor) logging.info(format_message("Renewal committed")) except: rollback(cursor) raise return Response(json.dumps(data), status=200)
def get_counties_list(): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) welsh_req = "" try: if 'welsh' in request.args: welsh_req = request.args['welsh'] if welsh_req == "yes": cursor.execute("SELECT name, welsh_name FROM COUNTY") else: cursor.execute("SELECT name FROM COUNTY") rows = cursor.fetchall() counties = list() for row in rows: counties.append(row['name']) if welsh_req == "yes" and row['welsh_name'] and (row['welsh_name'] != row['name']): counties.append(row['welsh_name']) counties.sort() finally: complete(cursor) return Response(json.dumps(counties), status=200, mimetype='application/json')
def test_county_council(self): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) key = create_registration_key(cursor, {'type': 'County Council', 'local': {'name': 'Nottinghamshire County Council', 'area': 'Nottinghamshire'}}) assert key['key'] == 'NOTTINGHA' complete(cursor)
def test_local_authority(self): cursor = connect(cursor_factory=psycopg2.extras.DictCursor) key = create_registration_key(cursor, {'type': 'Parish Council', 'local': {'name': 'Over Parish Council', 'area': 'Over'}}) assert key['key'] == 'NULL KEY' complete(cursor)