-
Notifications
You must be signed in to change notification settings - Fork 7
/
purchase.py
180 lines (170 loc) · 7.63 KB
/
purchase.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
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
from flask import Flask, render_template, request, session, url_for, redirect
import pymysql.cursors
import string, sys, random
from appdef import app, conn
@app.route('/purchasePageCustomer')
def purchasePage():
return render_template('purchaseCustomer.html')
@app.route('/purchasePageAgent')
def purchasePageAgent():
return render_template('purchaseAgent.html')
@app.route('/searchPurchaseCustomer', methods=['POST'])
def searchPurchaseCustomer():
cursor = conn.cursor()
fromcity = request.form['fromcity']
fromairport = request.form['fromairport']
fromdate = request.form['fromdate']
tocity = request.form['tocity']
toairport = request.form['toairport']
todate = request.form['todate']
query = 'SELECT distinct f.airline_name, f.flight_num, departure_airport, departure_time, arrival_airport, arrival_time, price, airplane_id \
FROM flight as f, airport \
WHERE airport.airport_name=f.departure_airport \
AND airport.airport_city = %s \
AND airport.airport_name = %s \
AND %s BETWEEN DATE_SUB(f.departure_time, INTERVAL 2 DAY) AND DATE_ADD(f.departure_time, INTERVAL 2 DAY)\
AND %s BETWEEN DATE_SUB(f.arrival_time, INTERVAL 2 DAY) AND DATE_ADD(f.arrival_time, INTERVAL 2 DAY)\
AND (f.airline_name, f.flight_num) in \
(SELECT flight.airline_name, flight.flight_num FROM flight, airport \
WHERE airport.airport_name=flight.arrival_airport \
AND airport.airport_city = %s \
AND airport.airport_name = %s) \
AND (SELECT DISTINCT seats \
FROM flight, airplane \
WHERE flight.airplane_id = airplane.airplane_id AND flight.airline_name = airplane.airline_name \
AND flight.airline_name = f.airline_name AND flight.flight_num = f.flight_num) \
>= (SELECT COUNT(*) \
FROM ticket \
WHERE ticket.airline_name = f.airline_name AND ticket.flight_num = f.flight_num)'
cursor.execute(query, (fromcity, fromairport, fromdate, todate, tocity, toairport))
# print cursor._executed
data = cursor.fetchall()
cursor.close()
error = None
if(data):
return render_template('purchaseCustomer.html', results=data)
else:
#returns an error message to the html page
error = 'No results found'
return render_template('purchaseCustomer.html', searchError=error)
# Thought it works, not really...
# def _genTix(ticketCount, airline_name, flight_num):
# pre = [str(flight_num), str(ticketCount+1)]
# di = dict(zip(string.letters,[ord(c)%32 for c in string.letters])) # taken from http://stackoverflow.com/a/4535403
# for c in airline_name:
# pre.append(str(di[c]))
# return ''.join(pre)
def _genTix():
cursor = conn.cursor()
cand = random.randint(1, 2147483647)
query = 'SELECT ticket_id FROM ticket'
cursor.execute(query)
allTix = cursor.fetchall()
cursor.close()
while cand in allTix:
cand = random.randint(1, 2147483647)
return cand
@app.route('/purchaseCustomer', methods=['POST'])
def purchaseCustomer():
username = session['username']
cursor = conn.cursor()
airline_name = request.form['airline_name']
flight_num = request.form['flight_num']
# Find the number of tickets to generate the next ticket_id
queryCount = 'SELECT COUNT(*) as count FROM ticket \
WHERE ticket.airline_name = %s AND ticket.flight_num = %s'
cursor.execute(queryCount, (airline_name, flight_num))
ticketCount = cursor.fetchone()
ticketCountVal = 0
if ticketCount != None:
ticketCountVal = ticketCount['count']
# ticket_id = _genTix(ticketCountVal, airline_name.strip().replace(' ', ''), flight_num)
ticket_id = _genTix()
# print("WHAT FUCKING NUMBER: ", ticket_id)
# Create the new ticket
queryNewTicket = 'INSERT INTO ticket VALUES(%s, %s, %s)'
cursor.execute(queryNewTicket, (ticket_id, airline_name, flight_num))
# Finalize the purchase
queryPurchase = 'INSERT INTO purchases VALUES(%s, %s, %s, CURDATE())'
cursor.execute(queryPurchase, (ticket_id, username, None))
data = cursor.fetchone()
conn.commit()
cursor.close()
return render_template('purchaseCustomer.html')
@app.route('/searchPurchaseAgent', methods=['POST'])
def searchPurchaseAgent():
cursor = conn.cursor()
fromcity = request.form['fromcity']
fromairport = request.form['fromairport']
fromdate = request.form['fromdate']
tocity = request.form['tocity']
toairport = request.form['toairport']
todate = request.form['todate']
query = 'SELECT distinct f.airline_name, f.flight_num, departure_airport, departure_time, arrival_airport, arrival_time, price, airplane_id \
FROM flight as f, airport \
WHERE airport.airport_name=f.departure_airport \
AND airport.airport_city = %s \
AND airport.airport_name = %s \
AND %s BETWEEN DATE_SUB(f.departure_time, INTERVAL 2 DAY) AND DATE_ADD(f.departure_time, INTERVAL 2 DAY)\
AND %s BETWEEN DATE_SUB(f.arrival_time, INTERVAL 2 DAY) AND DATE_ADD(f.arrival_time, INTERVAL 2 DAY)\
AND (f.airline_name, f.flight_num) in \
(SELECT flight.airline_name, flight.flight_num FROM flight, airport \
WHERE airport.airport_name=flight.arrival_airport \
AND airport.airport_city = %s \
AND airport.airport_name = %s) \
AND (SELECT DISTINCT seats \
FROM flight, airplane \
WHERE flight.airplane_id = airplane.airplane_id AND flight.airline_name = airplane.airline_name \
AND flight.airline_name = f.airline_name AND flight.flight_num = f.flight_num) \
>= (SELECT COUNT(*) \
FROM ticket \
WHERE ticket.airline_name = f.airline_name AND ticket.flight_num = f.flight_num)'
cursor.execute(query, (fromcity, fromairport, fromdate, todate, tocity, toairport))
# print cursor._executed
data = cursor.fetchall()
cursor.close()
error = None
if(data):
print(data)
return render_template('purchaseAgent.html', results=data)
else:
#returns an error message to the html page
error = 'No results found'
return render_template('purchaseAgent.html', searchError=error)
@app.route('/purchaseAgent', methods=['POST'])
def purchaseAgent():
username = session['username']
customer_email = request.form['customer_email']
cursor = conn.cursor()
airline_name = request.form['airline_name']
flight_num = request.form['flight_num']
# Find the number of tickets to generate the next ticket_id
queryCount = 'SELECT COUNT(*) as count FROM ticket \
WHERE ticket.airline_name = %s AND ticket.flight_num = %s'
cursor.execute(queryCount, (airline_name, flight_num))
ticketCount = cursor.fetchone()
ticketCountVal = 0
if ticketCount != None:
ticketCountVal = ticketCount['count']
# ticket_id = _genTix(ticketCountVal, airline_name.strip().replace(' ', ''), flight_num)
ticket_id = _genTix()
# Create the new ticket
queryNewTicket = 'INSERT INTO ticket VALUES(%s, %s, %s)'
cursor.execute(queryNewTicket, (ticket_id, airline_name, flight_num))
# Get booking_agent_id
queryGetID = 'SELECT booking_agent_id FROM booking_agent WHERE email=%s'
cursor.execute(queryGetID, username)
agentID = cursor.fetchone() # returns a dict
# Finalize the purchase
queryPurchase = 'INSERT INTO purchases VALUES(%s, %s, %s, CURDATE())'
cursor.execute(queryPurchase, (ticket_id, customer_email, agentID['booking_agent_id']))
data = cursor.fetchone()
conn.commit()
cursor.close()
error = None
if(data):
return render_template('agent.html', results=data)
else:
#returns an error message to the html page
error = 'Cannot complete purchase'
return render_template('purchaseAgent.html', error=error)