-
Notifications
You must be signed in to change notification settings - Fork 1
/
public.py
147 lines (136 loc) · 6.25 KB
/
public.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
from . import sql
from flask import (
Blueprint, flash, g, redirect, render_template, request, session, url_for, jsonify, Response
)
bp = Blueprint('public',__name__,url_prefix='/public')
@bp.route('/search_flight',methods=['GET'])
def query_flight():
prompt = request.args.to_dict()
prompt['status']='Upcoming'
req = 'SELECT * FROM flight WHERE ' + ' AND '.join((f'{i} = {repr(j)}' for i,j in prompt.items() if j!='' and 'DATE' not in i))
for time in ["departure_time", "arrival_time"]:
if f'DATE({time})' in prompt and prompt[f'DATE({time})'] != '':
req += f'AND {time} LIKE "{prompt[f"DATE({time})"]}%"'
#print(req)
with sql.SQLConnection.Instance().conn.cursor() as cursor:
cursor.execute('SELECT * FROM airline')
airlines = [x['name'] for x in cursor.fetchall()]
cursor.execute('SELECT name FROM airport')
airports = [x['name'] for x in cursor.fetchall()]
cursor.execute(req)
result = cursor.fetchall()
login = {'profile':'https://www.gstatic.com/android/keyboard/emojikitchen/20220406/u1f349/u1f349_u1f605.png?fbx'}
if 'username' in session:
login['username'] = session['username']
login['type'] = session['type']
else:
login['username'] = 'Visitor'
login['type'] = 'visitor'
return render_template('search_for_flight.html',
result=result,
airlines=airlines,
airports=airports,
login=login)
@bp.route('/home')
def home():
'''
template data:
login
'''
#return render_template("about.html")
login = {'profile':'https://www.gstatic.com/android/keyboard/emojikitchen/20220406/u1f349/u1f349_u1f605.png?fbx'}
if 'username' in session:
login['username'] = session['username']
login['type'] = session['type']
else:
login['username'] = 'Visitor'
login['type'] = 'visitor'
return render_template("about.html",
login=login)
@bp.route('/status',methods=['GET'])
def flight_status():
'''
request params:
airline_name
flight_num
DATE(departure_time)
DATE(arrival_time)
template data:
result - a single row (dictionary) from the flight table, by default None
airlines - list of airlines
login
'''
#print(request.args)
params = request.args.to_dict()
#print(params.values())
if params != {} and '' not in params.values():
airline_name = params['airline_name']
flight_num = params['flight_num']
departure_time = params['DATE(departure_time)']
arrival_time = params['DATE(arrival_time)']
with sql.SQLConnection.Instance().conn.cursor() as cursor:
#print("SELECT * FROM flight WHERE airline_name = %s AND flight_num = % s AND departure_time LIKE '% s%%' AND arrival_time LIKE '% s%%'", (airline_name, flight_num, departure_time, arrival_time,))
#cursor.execute("SELECT * FROM flight WHERE airline_name = % s AND flight_num = % s AND departure_time LIKE % s%% AND arrival_time LIKE % s%%;", (airline_name, flight_num, departure_time, arrival_time,))
#print(f"SELECT * FROM flight WHERE airline_name = {airline_name} AND flight_num = {flight_num} AND departure_time LIKE '{departure_time}%' AND arrival_time LIKE '{arrival_time}%';")
cursor.execute(f"SELECT * FROM flight WHERE airline_name = '{airline_name}' AND flight_num = {flight_num} AND departure_time LIKE '{departure_time}%' AND arrival_time LIKE '{arrival_time}%';")
result = cursor.fetchone()
#print(result)
else:
result = None
with sql.SQLConnection.Instance().conn.cursor() as cursor:
cursor.execute('SELECT * FROM airline')
airlines = map(lambda x: [*x.values()][0],cursor.fetchall())
login = {'profile':'https://www.gstatic.com/android/keyboard/emojikitchen/20220406/u1f349/u1f349_u1f605.png?fbx'}
if 'username' in session:
login['username'] = session['username']
login['type'] = session['type']
else:
login['username'] = 'Visitor'
login['type'] = 'visitor'
return render_template('flight_status.html',
result = result,
airlines = airlines,
login=login)
#return render_template("flight_status.html",result={'airline_name':'CE','flight_num':555,'arrival_airport':'PVG','departure_airport':'JFK','status':'Delayed'})
@bp.route('/purchase_flight',methods=['POST'])
def purchase_flight():
connection = sql.SQLConnection.Instance().conn
cursor = connection.cursor()
airline_name = request.form["airline_name"]
flight_num = request.form["flight_num"]
if session['type'] == 'agent':
buy_for = request.form['buy_for']
#print(buy_for)
cursor.execute('SELECT * FROM customer WHERE email = %s', (buy_for, ))
result = cursor.fetchone()
#print(result)
if not result:
return jsonify({'message':'plane is full'}),201
id = session['username'][6:]
elif session['type'] == 'customer':
buy_for = session['email']
id = 'NULL'
#if session['type'] == 'customer':
cursor.execute('''SELECT COUNT(ticket_id) as c
FROM ticket JOIN flight USING(airline_name,flight_num)
WHERE airline_name = %s
AND flight_num = %s''', (airline_name, flight_num,))
cnt = cursor.fetchone()['c']
cursor.execute('''SELECT *
FROM flight JOIN airplane ON flight.plane=airplane.id AND flight.airline_name=airplane.airline_name
WHERE flight.airline_name = %s
AND flight.flight_num = %s''', (airline_name, flight_num,))
try:
seats = cursor.fetchone()['seats']
except:
seats = 999
if cnt < seats:
cursor.execute(
'INSERT INTO ticket (airline_name, customer_email, flight_num, booking_agent_id, ticket_id, purchased_date) VALUES (% s, %s, %s, %s, NULL, CURRENT_DATE())', (airline_name, buy_for, flight_num, id,))
connection.commit()
return jsonify({'message':'success'}),200
else:
return jsonify({'message':'plane is full'}),202
@bp.route('redirect',methods=['GET'])
def redir():
return render_template('redirecting.html',message=request.args.get('message',''),url=request.args.get('url','/'))