def update_reservation(cid, rid):
validate_sin(cid)
data = request.json
if 'status' not in data:
raise BadRequestError(
message='Missing required body field \'status\'')
status = request.json['status']
query = '''SELECT status_id, check_in_day, hotel_id FROM hotel.room_booking
WHERE customer_sin = '{}' AND booking_ID = '{}\''''.format(cid, rid)
result = get_results(query, conn, single=True)
if len(result) == 0:
raise ResourceNotFoundError(
message=
'Reservation does not exist or does not belong to this customer'
.format(cid))
result = ast.literal_eval(result)
current_status = result['status_id']
d = result['check_in_day']
if not (current_status == 1 and status == 'Cancelled') and not (current_status == 2 and status == 'Archived') \
and not (current_status == 1 and status == 'Renting' and d <= datetime.today().strftime('%Y-%m-%d')):
raise BadRequestError(
message=
'Invalid status transition. Booked rooms can be cancelled/rented, '
'and rented rooms can be archived.')
if 'employee_sin' in data:
validate_sin(data['employee_sin'])
query = 'SELECT employee_SIN, hotel_id FROM hotel.employee WHERE status_id = 1 AND employee_SIN = \'{}\'' \
.format(data['employee_sin'])
e_result = get_results(query, conn, jsonify=False)
if len(e_result) == 0:
raise ResourceNotFoundError(message='Employee SIN not found')
if e_result[0].get('hotel_id') != result['hotel_id']:
raise BadRequestError(
message=
"Given employee cannot update this reservation - works at different hotel"
)
query = '''UPDATE hotel.room_booking SET status_ID =
(SELECT s.status_ID FROM hotel.booking_status s WHERE s.value = '{}') , employee_sin = '{}'
WHERE booking_ID = {}'''.format(
status, e_result[0].get('employee_sin'), rid)
else:
query = '''UPDATE hotel.room_booking SET status_ID =
(SELECT s.status_ID FROM hotel.booking_status s WHERE s.value = '{}')
WHERE booking_ID = {}'''.format(status, rid)
execute(query, conn)
return Response(status=204, mimetype='application/json')
def verify_manager(manager_sin, hotel_id, conn):
query = 'SELECT employee_sin FROM hotel.employee WHERE job_title = \'Manager\' AND hotel_id = {}'.format(hotel_id)
result = get_results(query, conn, jsonify=False)
if len(result) == 0:
raise BadRequestError(message='Invalid manager SIN')
for employee in result:
sin = employee.get('employee_sin')
if sin is not None:
if sin == manager_sin:
return
raise BadRequestError(message='Invalid manager SIN')
def delete_room(hid, rid):
data = request.json
if 'manager_sin' not in data:
raise BadRequestError(
message="Missing required body field 'manager_sin'")
manager_sin = data['manager_sin']
verify_manager(manager_sin, hid, conn)
query = '''SELECT COUNT(*) FROM hotel.hotel WHERE hotel_id = {}'''.format(
hid)
response = get_results(query, conn, jsonify=False)
if response[0].get('count') == 0:
raise ResourceNotFoundError(
message='Hotel ID={} not found'.format(hid))
query = '''SELECT COUNT(*) FROM hotel.hotel_room_type WHERE hotel_id = {} AND type_id = {}
AND deleted = false'''.format(hid, rid)
response = get_results(query, conn, jsonify=False)
if response[0].get('count') == 0:
raise ResourceNotFoundError(
message='Room ID={} not found at hotel'.format(rid))
query = '''UPDATE hotel.hotel_room_type SET deleted = true WHERE type_id = {}'''.format(
rid)
execute(query, conn)
return Response(status=204, mimetype='application/json')
def get_room_availability_by_hotel(hid):
people = request.args.get('people')
check_in_day = request.args.get('check-in')
check_out_day = request.args.get('check-out')
if people is None:
raise BadRequestError(
message="Missing required query param 'people'")
if check_in_day is None:
raise BadRequestError(
message="Missing required query param 'check-in'")
if check_out_day is None:
raise BadRequestError(
message="Missing required query param 'check-out'")
try:
people = int(people)
if people < 1:
raise BadRequestError(
message="'people' must be a positive integer")
except ValueError:
raise BadRequestError(
message="'people' must be a positive integer")
try:
datetime.strptime(check_in_day, '%Y-%m-%d')
except ValueError:
raise BadRequestError(
message="Invalid date format for check-in. Must be YYYY-MM-DD")
try:
datetime.strptime(check_out_day, '%Y-%m-%d')
except ValueError:
raise BadRequestError(
message="Invalid date format for check-in. Must be YYYY-MM-DD")
if check_out_day <= check_in_day:
raise BadRequestError(
message='check-out must be later than check-in')
query = '''SELECT t.type_id FROM hotel.hotel_room_type t
WHERE t.hotel_ID = {} AND t.room_capacity >= {}'''.format(
hid, people)
response = get_results(query, conn, jsonify=False)
for room in response:
query = '''SELECT hotel.max_occupancy(DATE '{}', DATE '{}', {})'''.format(
check_in_day, check_out_day, room.get('type_id'))
occupancy = get_results(query, conn, jsonify=False)
room['occupancy'] = occupancy[0].get('max_occupancy')
return Response(json.dumps(response, default=str),
status=200,
mimetype='application/json')
def get_reservations(hid):
action = request.args.get('action')
if action is None:
raise BadRequestError(
message='Missing required query param action')
if action != 'check-in' and action != 'check-out':
raise BadRequestError(
message='Invalid action param, must be check-in or check-out')
try:
hid = int(hid)
except ValueError:
raise BadRequestError(
message="Invalid hotel_ID: must be an integer")
execute('SELECT hotel.correct_status_hotel({})'.format(hid), conn)
if action == 'check-in':
query = '''SELECT b.booking_id, b.date_of_registration, b.check_in_day, b.check_out_day,
t.title, t.is_extendable, t.amenities, v.view, t.price, c.customer_sin, c.customer_name,
e.employee_name, e.job_title
FROM hotel.room_booking b
JOIN hotel.booking_status s ON s.status_ID = b.status_ID
JOIN hotel.hotel_room_type t ON t.type_ID = b.type_ID
JOIN hotel.view_type v ON v.view_ID = t.view_ID
JOIN hotel.customer c ON b.customer_sin = c.customer_sin
LEFT JOIN hotel.employee e ON b.employee_sin = e.employee_sin
WHERE s.value = 'Booked' AND b.hotel_id = {} AND CURRENT_DATE >= b.check_in_day
AND CURRENT_DATE < b.check_out_day
'''.format(hid)
else:
query = '''SELECT b.booking_id, b.date_of_registration, b.check_in_day, b.check_out_day,
t.title, t.is_extendable, t.amenities, v.view, t.price, c.customer_sin, c.customer_name,
e.employee_name, e.job_title
FROM hotel.room_booking b
JOIN hotel.booking_status s ON s.status_ID = b.status_ID
JOIN hotel.hotel_room_type t ON t.type_ID = b.type_ID
JOIN hotel.view_type v ON v.view_ID = t.view_ID
JOIN hotel.customer c ON b.customer_sin = c.customer_sin
LEFT JOIN hotel.employee e ON b.employee_sin = e.employee_sin
WHERE b.hotel_id = {} AND s.value = 'Renting\''''.format(
hid)
response = get_results(query, conn)
return Response(response, status=200, mimetype='application/json')
def validate_email(email):
try:
email_error = ' ' in email or email.index('@') < 1
if not email_error:
index = email.index('.', email.index('@'))
if index < 3 or index == len(email) - 1:
email_error = True
except ValueError:
email_error = True
if email_error:
raise BadRequestError(message='Invalid email address')
def get_all_customers():
email = request.args.get('email')
if email is None or len(email) == 0:
extra_query = 'LIMIT 20'
else:
if '\'' in email:
raise BadRequestError(
message='Invalid email: remove \' character')
extra_query = 'WHERE customer_email = \'{}\''.format(email)
query = 'SELECT * FROM hotel.customer ' + extra_query
results = get_results(query, conn)
return Response(results, status=200, mimetype='application/json')
def update_customer(cid):
data = request.json
validate_sin(cid)
get_customer_by_id(cid, conn)
name_exists = False
address_exists = False
email_exists = False
phone_exists = False
query = 'UPDATE hotel.customer SET '
if 'customer_name' in data and len(data['customer_name']) > 0:
query += 'customer_name = \'' + data['customer_name'] + '\' '
name_exists = True
if 'customer_address' in data and len(data['customer_address']) > 0:
if name_exists:
query += ', '
query += 'customer_address = \'' + data['customer_address'] + '\' '
address_exists = True
if 'customer_email' in data:
if name_exists or address_exists:
query += ', '
validate_email(data['customer_email'])
query += 'customer_email = \'' + data['customer_email'] + '\' '
email_exists = True
if 'customer_phone' in data:
if name_exists or address_exists or email_exists:
query += ', '
validate_phone(data['customer_phone'])
query += 'customer_phone = \'' + data['customer_phone'] + '\' '
phone_exists = True
if not name_exists and not address_exists and not email_exists and not phone_exists:
raise BadRequestError(
message=
'Must provide at least one of: customer_name, customer_address, '
'customer_email, customer_phone')
query += 'WHERE customer_sin = \'' + cid + "'"
try:
execute(query, conn)
except psycopg2.DatabaseError:
raise ResourceConflictError(message='Email address already exists')
return Response(status=204, mimetype='application/json')
def get_all_employees():
email = request.args.get('email')
if email is None or len(email) == 0:
extra_query = ' LIMIT 10'
else:
if '\'' in email:
raise BadRequestError(message='Invalid email: remove \' character')
extra_query = ' WHERE employee_email = \'{}\''.format(email)
query = '''SELECT e.employee_sin, e.employee_email, s.status, e.employee_name, e.employee_address, e.salary, e.job_title,
b.name AS brand_name, h.brand_id, h.hotel_id, h.physical_address AS hotel_address
FROM hotel.employee e
JOIN hotel.hotel h ON h.hotel_id = e.hotel_id
JOIN hotel.hotel_brand b ON b.brand_id = h.brand_id
JOIN hotel.employee_status s ON s.status_id = e.status_id''' + extra_query
results = get_results(query, conn)
return Response(results, status=200, mimetype='application/json')
def create_employee(hid):
data = request.json
if 'employee_sin' not in data:
raise BadRequestError(
message="Missing required body field 'employee_sin'")
if 'manager_sin' not in data:
raise BadRequestError(
message="Missing required body field 'manager_sin'")
if 'email' not in data:
raise BadRequestError(
message="Missing required body field 'email'")
if 'name' not in data:
raise BadRequestError(message="Missing required body field 'name'")
if 'address' not in data:
raise BadRequestError(
message="Missing required body field 'address'")
if 'salary' not in data:
raise BadRequestError(
message="Missing required body field 'salary'")
if 'job_title' not in data:
raise BadRequestError(
message="Missing required body field 'job_title'")
employee_sin = data['employee_sin']
manager_sin = data['manager_sin']
name = data['name']
email = data['email']
address = data['address']
salary = data['salary']
job_title = data['job_title']
verify_manager(manager_sin, hid, conn)
query = """INSERT INTO hotel.employee(employee_sin, employee_name, employee_email, employee_address, salary, job_title,
hotel_ID) VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}')""" \
.format(employee_sin, name, email, address, salary, job_title, hid)
try:
execute(query, conn)
except psycopg2.DatabaseError as e:
if 'duplicate key value violates unique constraint "employee_employee_email_key"' in str(
e):
raise ResourceConflictError(
message='Unable to add employee - email already in use')
raise ResourceConflictError(message='Employee already exists')
return Response(status=201, mimetype='application/json')
def create_customer():
data = request.json
if 'customer_sin' not in data:
raise BadRequestError(
message="Missing required body field 'customer_sin'")
if 'customer_name' not in data:
raise BadRequestError(
message="Missing required body field 'customer_name'")
if 'customer_address' not in data:
raise BadRequestError(
message="Missing required body field 'customer_address'")
if 'customer_email' not in data:
raise BadRequestError(
message="Missing required body field 'customer_email'")
if 'customer_phone' not in data:
raise BadRequestError(
message="Missing required body field 'customer_phone'")
customer_sin = data['customer_sin']
customer_name = data['customer_name']
customer_address = data['customer_address']
customer_email = data['customer_email']
customer_phone = data['customer_phone']
validate_sin(customer_sin)
validate_email(customer_email)
validate_phone(customer_phone)
if len(customer_address) == 0 or len(customer_address) > 255:
raise BadRequestError(
message=
'Invalid customer address - must be between 1 and 255 characters'
)
query = """INSERT INTO hotel.customer(customer_SIN, customer_name, customer_address, customer_email,
customer_phone) VALUES ('{}', '{}', '{}', '{}', '{}')""" \
.format(customer_sin, customer_name, customer_address, customer_email, customer_phone)
try:
execute(query, conn)
except psycopg2.DatabaseError:
raise ResourceConflictError(message='Customer already exists')
return Response(status=201, mimetype='application/json')
def create_reservation(cid):
validate_sin(cid)
data = request.json
if 'check_in' not in data:
raise BadRequestError(
message="Missing required body field 'check_in'")
if 'check_out' not in data:
raise BadRequestError(
message="Missing required body field 'check_out'")
if 'type_id' not in data:
raise BadRequestError(
message="Missing required body field 'type_id'")
check_in = data['check_in']
check_out = data['check_out']
type_id = data['type_id']
try:
check_in_date = datetime.strptime(check_in, '%Y-%m-%d').date()
today = date.today()
if check_in_date < today:
raise BadRequestError(
message="Check in date cannot be in the past")
except ValueError:
raise BadRequestError(
message="Invalid date format for check_in. Must be YYYY-MM-DD")
try:
check_out_date = datetime.strptime(check_out, '%Y-%m-%d').date()
if check_out_date <= check_in_date:
raise BadRequestError(
message="Check out date must be later than check in date")
except ValueError:
raise BadRequestError(
message="Invalid date format for check_out. Must be YYYY-MM-DD"
)
try:
check_out_date = datetime.strptime(check_out, '%Y-%m-%d').date()
if check_out_date <= check_in_date:
raise BadRequestError(
message="Check out date must be later than check in date")
except ValueError:
raise BadRequestError(
message="Invalid date format for check_out. Must be YYYY-MM-DD"
)
query = 'SELECT hotel_ID FROM hotel.hotel_room_type WHERE type_ID = {}'.format(
type_id)
result = get_results(query, conn, jsonify=False)
if len(result) == 0:
raise ResourceNotFoundError(
message="Hotel not found with type id=" + str(type_id))
if 'employee_sin' in data:
query = 'SELECT employee_SIN FROM hotel.employee WHERE status_id = 1 AND employee_SIN = \'{}\' ' \
'AND hotel_ID = {}'.format(data['employee_sin'], int(result[0].get('hotel_id')))
e_result = get_results(query, conn, jsonify=False)
if len(e_result) == 0:
raise ResourceNotFoundError(message="Employee SIN not found")
query = '''INSERT INTO hotel.room_booking(type_ID, hotel_ID, customer_SIN, check_in_day, check_out_day,
employee_SIN) VALUES ({}, {}, '{}', DATE '{}', DATE '{}', '{}')''' \
.format(type_id, result[0].get('hotel_id'), cid, check_in_date, check_out_date,
e_result[0].get('employee_sin'))
else:
query = 'INSERT INTO hotel.room_booking(type_ID, hotel_ID, customer_SIN, check_in_day, check_out_day) ' \
"VALUES ({}, {}, '{}', DATE '{}', DATE '{}')".format(type_id, result[0].get('hotel_id'), cid,
check_in_date, check_out_date)
try:
execute(query, conn)
except psycopg2.DatabaseError as e:
e = str(e)
if 'Key (customer_sin)=' in e:
raise BadRequestError(message='Customer id not found: ' + cid)
if 'This room is already booked up' in e:
raise ResourceConflictError(
message='Room is already booked up')
raise BadRequestError
return Response(status=201, mimetype='application/json')
def create_room(hid):
query = '''SELECT * FROM hotel.hotel WHERE hotel_id = {}'''.format(hid)
response = get_results(query, conn)
if response == '[]':
raise ResourceNotFoundError(
message='Hotel ID={} not found'.format(hid))
data = request.json
if 'manager_sin' not in data:
raise BadRequestError(
message="Missing required body field 'manager_sin'")
if 'title' not in data:
raise BadRequestError(
message="Missing required body field 'title'")
if 'price' not in data:
raise BadRequestError(
message="Missing required body field 'price'")
if 'amenities' not in data:
raise BadRequestError(
message="Missing required body field 'amenities'")
if 'room_capacity' not in data:
raise BadRequestError(
message="Missing required body field 'price'")
if 'view' not in data:
raise BadRequestError(message="Missing required body field 'view'")
if 'is_extendable' not in data:
raise BadRequestError(
message="Missing required body field 'is_extendable'")
if 'rooms' not in data:
raise BadRequestError(
message="Missing required body field 'rooms'")
manager_sin = data['manager_sin']
verify_manager(manager_sin, hid, conn)
title = data['title']
price = data['price']
amenities = data['amenities']
room_capacity = data['room_capacity']
view = data['view']
is_extendable = data['is_extendable']
rooms = data['rooms']
if len(title) == 0:
raise BadRequestError(
message='Length of \'title\' must be greater than 0')
try:
price = round(float(price), 2)
except ValueError:
raise BadRequestError(message="Invalid value of 'price'")
if price < 0:
raise BadRequestError(message="Invalid value of 'price'")
if type(amenities) != list:
raise BadRequestError(
message=
"Invalid format of 'amenities': must be an array of strings")
if type(room_capacity) != int or room_capacity <= 0:
raise BadRequestError(message="Invalid value of 'room_capacity'")
if type(view) != str or len(view) == 0:
raise BadRequestError(message="Invalid value of 'view'")
view_id = '''SELECT view_id FROM hotel.view_type WHERE view = '{}\''''.format(
view)
view_id = get_results(view_id, conn, jsonify=False)
if len(view_id) == 0:
raise BadRequestError(message="View='{}' not found".format(view))
view_id = view_id[0].get('view_id')
if type(is_extendable) != bool:
raise BadRequestError(message="Invalid value of 'is_extendable'")
if type(rooms) != int or rooms < 0:
raise BadRequestError(message="Invalid value of 'rooms'")
amenities = to_pg_array(amenities)
query = '''INSERT INTO hotel.hotel_room_type(hotel_id, title, price, amenities, room_capacity, view_id, is_extendable,
total_number_rooms, rooms_available) VALUES ({}, '{}', '{}', '{}', {}, {}, {}, {}, {})''' \
.format(hid, title, price, amenities, room_capacity, view_id, is_extendable, rooms, rooms)
execute(query, conn)
query = '''SELECT MAX(type_id) FROM hotel.hotel_room_type WHERE hotel_id = {}'''.format(
hid)
response = get_results(query, conn, jsonify=False)
response = {'type_id': response[0].get('max')}
return Response(json.dumps(response, default=str),
status=201,
mimetype='application/json')
def update_employee(hid, eid):
data = request.json
if 'status' in data or 'hotel_id' in data or 'salary' in data or 'job_title' in data:
if 'manager_sin' not in data:
raise BadRequestError(
message=
"Body field 'manager_sin' is required to update status and hotel_id"
)
manager_sin = data['manager_sin']
verify_manager(manager_sin, hid, conn)
if manager_sin == eid:
raise BadRequestError(
message=
'Manager cannot change themself - please contact the db administrator'
)
query = ''
if 'status' in data:
query += '''UPDATE hotel.employee e SET status_id = (SELECT status_id FROM hotel.employee_status s
WHERE s.status = '{}') WHERE employee_sin = \'{}\';'''.format(
data['status'], eid)
if 'hotel_id' in data:
query += '''UPDATE hotel.employee e SET hotel_id = {} WHERE employee_sin = \'{}\';'''.format(
hid, eid)
if 'employee_address' in data and len(data['employee_address']) > 0:
query += '''UPDATE hotel.employee e SET employee_address = '{}' WHERE employee_sin = \'{}\';''' \
.format(data['employee_address'], eid)
if 'employee_name' in data and len(data['employee_name']) > 0:
query += '''UPDATE hotel.employee e SET employee_name = '{}' WHERE employee_sin = \'{}\';''' \
.format(data['employee_name'], eid)
if 'employee_email' in data and len(data['employee_email']) > 0:
print('employee email UPDATING')
query += '''UPDATE hotel.employee e SET employee_email = '{}' WHERE employee_sin = \'{}\';''' \
.format(data['employee_email'], eid)
if 'salary' in data:
try:
float(data['salary'])
except ValueError:
raise BadRequestError(
message='Invalid salary - Must be a decimal number')
query += '''UPDATE hotel.employee e SET salary = '{}' WHERE employee_sin = \'{}\';''' \
.format(data['salary'], eid)
if 'job_title' in data and len(data['job_title']) > 0:
query += '''UPDATE hotel.employee e SET job_title = '{}' WHERE employee_sin = \'{}\';''' \
.format(data['job_title'], eid)
try:
if query != '':
execute(query, conn)
except psycopg2.DatabaseError as e:
if 'null value in column "status_id" of relation "employee" violates not-null constraint' in str(
e):
raise BadRequestError(
message='Unable to delete employee - invalid status')
if 'duplicate key value violates unique constraint "employee_employee_email_key"' in str(
e):
raise ResourceConflictError(
message='Unable to update employee - email already in use')
raise ResourceConflictError(message='Unable to delete employee')
return Response(status=204, mimetype='application/json')
def validate_sin(sin):
if len(re.findall(sin_regex, sin)) == 0:
raise BadRequestError(message='Invalid social insurance number')
def validate_phone(phone):
if len(re.findall(phone_regex, phone)) == 0:
raise BadRequestError(message='Invalid phone number')