def get(self):
from_airport = request.args.get('from')
to_airport = request.args.get('to')
date_of_journey = request.args.get('date_of_journey')
ticket_class = request.args.get('ticket_class')
passengers = int(request.args.get('passengers'))
print("doj=", date_of_journey)
query = """
SELECT f.flight_id, f.airlines, DATE_FORMAT(f.departure, '%Y-%m-%d') AS departure, fs.seat_type, CAST(fs.fare AS CHAR(10)) AS fare, COUNT(DISTINCT fs.seat_no) AS seats
FROM Flight f, FlightSeat fs, Address ads, Address addd, Airport aps, Airport apd
WHERE f.source = aps.airport_id AND f.destination = apd.airport_id
AND aps.airport_id=ads.address_id AND apd.airport_id=addd.address_id
AND ads.city = '{}' AND addd.city = '{}'
AND f.departure = '{}'
AND fs.seat_type = '{}'
AND f.flight_id = fs.flight_id
AND (f.flight_id,fs.seat_no) NOT IN (SELECT fb.flight_id, fb.seat_no FROM FlightBooking fb)
GROUP BY f.flight_id, f.airlines, f.departure, fs.fare, fs.seat_type
HAVING COUNT(DISTINCT fs.seat_no) >= {}
ORDER BY f.departure, f.airlines, fs.fare
"""
res = Query(query=query)
return {
"flights": [
a
for a in res.getAll(from_airport, to_airport, date_of_journey,
ticket_class, passengers)
]
}
def get(self):
query = """
SELECT ad.city FROM Airport air
JOIN Address ad ON air.address_id = ad.address_id
"""
res = Query(query=query)
return {"airports": [a for a in res.getAll()]}
def get(self):
usr = current_user
query = """
SELECT hb.booking_id, h.hotel_name, hb.room_no,
DATE_FORMAT(hb.start_date, '%Y-%m-%d') AS start_date,
DATE_FORMAT(hb.end_date, '%Y-%m-%d') AS end_date,
DATE_FORMAT(hb.date_of_booking, '%Y-%m-%d') AS date_of_booking
FROM HotelBooking hb
JOIN Hotel h ON hb.hotel_id = h.hotel_id
WHERE hb.user_id = {};
"""
q = Query(query=query)
return {
"bookings": q.getAll(usr.user_id)
}
def get(self):
query = """
SELECT r.hotel_id, r.room_no, CAST(r.cost AS CHAR) AS cost
FROM Room r
WHERE r.hotel_id = {}
AND CONCAT_WS(r.hotel_id, r.room_no) NOT IN (
SELECT DISTINCT CONCAT_WS(hb.hotel_id, hb.room_no)
FROM HotelBooking hb
WHERE GREATEST(DATEDIFF(LEAST('{}', hb.end_date), GREATEST('{}', hb.start_date)), 0) > 0
)
"""
hotel_id = request.args.get("hotel_id")
start_date = request.args.get("start_date")
end_date = request.args.get("end_date")
q = Query(query)
return {
"rooms": q.getAll(hotel_id, end_date, start_date)
}
def get(self):
city = request.args.get('city')
start_date = request.args.get('start_date')
end_date = request.args.get('end_date')
query = """
SELECT h.hotel_id, h.hotel_name, a.*, CAST(MIN(r.cost) AS CHAR) AS min_cost
FROM Hotel h
JOIN Room r ON r.hotel_id = h.hotel_id
JOIN Address a ON a.address_id = h.address_id
WHERE a.city = '{}'
AND CONCAT_WS(h.hotel_id, r.room_no) NOT IN (
SELECT DISTINCT CONCAT_WS(hb.hotel_id, hb.room_no)
FROM HotelBooking hb
WHERE GREATEST(DATEDIFF(LEAST('{}', hb.end_date), GREATEST('{}', hb.start_date)), 0) > 0
)
GROUP BY h.hotel_id;
"""
res = Query(query=query)
return {
"hotels": [a for a in res.getAll(city, end_date, start_date)]
}
def post(self):
usr = current_user
flight_id = api.payload['flight_id']
airlines = api.payload['airlines']
departure = api.payload['departure']
seat_type = api.payload['seat_type']
seats = int(api.payload['seats'])
fare = float(api.payload['fare'])
q1 = """
SELECT fs.flight_id, fs.seat_no FROM Flight f, FlightSeat fs
WHERE f.flight_id = fs.flight_id
AND f.flight_id = {}
AND f.airlines = '{}'
AND f.departure = '{}'
AND fs.seat_type = '{}'
AND fs.fare = {}
AND (fs.flight_id, fs.seat_no) NOT IN
(SELECT flight_id, seat_no FROM FlightBooking)
LIMIT {}
"""
res = Query(query=q1)
seats = [
a for a in res.getAll(flight_id, airlines, departure, seat_type,
fare, seats)
]
q2 = []
now = datetime.now().strftime("%Y-%m-%d")
for s in seats:
q2.append(
"INSERT INTO FlightBooking VALUES ({},{},{},'{}',NULL,{:.2f},FALSE);"\
.format(usr.user_id,s['flight_id'],s['seat_no'],now,fare))
tx = Transaction(query=q2)
try:
tx.execute()
q3 = []
for s in seats:
q3.append(
"SELECT flight_id, seat_no FROM FlightBooking WHERE flight_id={} AND user_id={} AND seat_no={}"\
.format(s['flight_id'],usr.user_id,s['seat_no'])
)
results = []
for qry in q3:
ress = Query(query=qry)
results.append(ress.getOne())
print("results")
print(results)
return {"bookings": results}, 201
except Exception as e:
print(e)
return {"message": "Booking failed"}, 403
def post(self):
usr = current_user
bookings = api.payload['bookings']
q1 = """
UPDATE FlightBooking
SET is_paid=TRUE
WHERE user_id={} AND flight_id ={} AND seat_no={}
"""
try:
for booking in bookings:
Query(query=q1.format(usr.user_id, booking['flight_id'],
booking['seat_no']))
return {"message": "Payment successful"}, 200
except Exception as e:
return {"message": "Payment failed"}, 400
def post(self):
usr = current_user
hotel_id = api.payload['hotel_id']
room_no = api.payload['room_no']
q = Query("SELECT cost FROM Room WHERE hotel_id = {} AND room_no = {}")
cost = q.getOne(hotel_id, room_no)['cost']
start_date = api.payload['start_date']
end_date = api.payload['end_date']
days = datetime.strptime(end_date, "%Y-%m-%d") - datetime.strptime(start_date, "%Y-%m-%d")
if days.days <= 0:
return {
"message": "Invalid input"
}, 403
amount = cost * days.days
no_of_persons = api.payload['no_of_persons']
booking_id = uuid4()
query = [
"SELECT COUNT(*) INTO @cnt FROM HotelBooking\
WHERE hotel_id = {} AND room_no = {}\
AND NOT (start_date > '{}' OR end_date < '{}');"\
.format(hotel_id, room_no, end_date, start_date),
"INSERT INTO HotelBooking VALUES (\
IF(@cnt > 0, -1, {}), {}, {}, CURRENT_TIMESTAMP(),\
'{}', '{}', {}, 'All is well',\
{}, 0, '{}');"\
.format(usr.user_id, hotel_id, room_no, start_date,
end_date, no_of_persons, amount, booking_id)
]
tx = Transaction(query=query)
try:
tx.execute()
q = Query("SELECT booking_id FROM HotelBooking WHERE booking_id = '{}';")
res = q.getAll(booking_id)
assert len(res) == 1
return {
"message": "Booking successful"
}, 201
except Exception as e:
return {
"message": "Booking failed"
}, 403
def get_user_from_email(email):
q = Query("SELECT * FROM User WHERE email = '{}'", model=User)
usr = q.getOne(email)
assert usr is not None
return usr
def load_user(user_id):
q = Query("SELECT * FROM User WHERE user_id = {}", model=User)
usr = q.getOne(user_id)
return usr