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