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 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 get_rooms_by_hotel(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))
query = '''SELECT h.type_id, h.title, h.price, h.amenities, h.room_capacity, v.view, h.is_extendable,
h.total_number_rooms, h.rooms_available FROM hotel.hotel_room_type h JOIN hotel.view_type v
ON v.view_ID = h.view_ID WHERE h.hotel_id = {} AND h.deleted = false'''.format(
hid)
response = get_results(query, conn)
return Response(response, status=200, mimetype='application/json')
def setup_if_missing(conn, data_mode):
results = get_results(
"SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'hotel'",
conn,
jsonify=False)
if len(results) == 0:
print("Schema is empty! Generating...")
setup(conn, data_mode)
def get_employees_by_hotel(hid):
query = '''SELECT e.employee_sin, e.employee_email, e.employee_name, e.employee_address, e.salary, e.job_title
FROM hotel.employee e WHERE e.status_id = 1 AND e.hotel_id = {}'''.format(
hid)
response = get_results(query, conn)
if response == '[]':
raise ResourceNotFoundError(
message='Hotel ID={} not found'.format(hid))
return Response(response, status=200, mimetype='application/json')
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 get_reservations_by_customer(cid):
validate_sin(cid)
execute('SELECT hotel.correct_status_customer(\'{}\')'.format(cid),
conn)
query = '''SELECT b.booking_id, b.date_of_registration, b.check_in_day, b.check_out_day, s.value AS status,
t.title, t.is_extendable, t.amenities, v.view, h.physical_address, t.price, 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.hotel h ON h.hotel_ID = b.hotel_ID
LEFT JOIN hotel.employee e ON b.employee_sin = e.employee_sin
WHERE b.customer_sin = '{}';'''.format(cid)
response = get_results(query, conn)
return Response(response, 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 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')