API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Oct 27th, 2017
83
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 37.33 KB | None | 0 0
raw download clone embed print report
  1. # Jia Sen Wu - jw3675
  2. # Ray Mohabir - rm3887
  3. # Title: Please Do Not Mind The Spaghetti
  4. # Final Project
  5.  
  6. import ujson as json
  7. import hashlib
  8. from flask import Flask, request, render_template, session, redirect
  9. from flask_bootstrap import Bootstrap
  10. from flaskext.mysql import MySQL
  11. import os
  12. import datetime
  13. import binascii
  14.  
  15. mysql = MySQL()
  16. app = Flask(__name__)
  17. app.config['MYSQL_DATABASE_USER'] = 'dbman'
  18. app.config['MYSQL_DATABASE_PASSWORD'] = 'kappa'
  19. app.config['MYSQL_DATABASE_DB'] = 'fly'
  20. app.config['MYSQL_DATABASE_HOST'] = 'localhost'
  21. mysql.init_app(app)
  22.  
  23. inject = "';"
  24.  
  25. def dbsearch(num = None, dcity = None, acity = None, ddate = None, adate = None, email = None, status = False):
  26. ret = ""
  27. query = "SELECT DISTINCT flight.flight_num, flight.airline_name, departure_airport, departure_time, arrival_airport, arrival_time, status FROM flight"
  28. if email:
  29. if not session.get('type') == "staff":
  30. query += ", ticket, purchases"
  31. if session.get('type') == "agent":
  32. query += ", booking_agent"
  33. else:
  34. query += ", airline, airline_staff"
  35. if num or dcity or acity or ddate or adate or email or status:
  36. query += " WHERE flight.airline_name IS NOT NULL"
  37. if num:
  38. if inject in num:
  39. print "Warning! SQL Injection!"
  40. else:
  41. query += " AND flight.flight_num='" + num + "'"
  42. if dcity:
  43. if inject in dcity:
  44. print "Warning! SQL Injection!"
  45. else:
  46. query += " AND departure_airport='" + dcity + "'"
  47. if acity:
  48. if inject in acity:
  49. print "Warning! SQL Injection!"
  50. else:
  51. query += " AND arrival_airport='" + acity + "'"
  52. if ddate:
  53. if inject in ddate:
  54. print "Warning! SQL Injection!"
  55. else:
  56. query += " AND DATE(departure_time)='" + ddate + "'"
  57. if adate:
  58. if inject in adate:
  59. print "Warning! SQL Injection!"
  60. else:
  61. query += " AND DATE(arrival_time)='" + adate + "'"
  62. if status:
  63. query += " AND (status='Upcoming' OR status='Delayed')"
  64. if email:
  65. if session.get('type') == "agent":
  66. query += " AND booking_agent.email = \"" + email + "\" AND booking_agent.booking_agent_id = purchases.booking_agent_id AND purchases.ticket_id = ticket.ticket_id AND flight.flight_num = ticket.flight_num"
  67. elif session.get('type') == "staff":
  68. query += " AND airline_staff.username = \"" + email + "\" AND airline_staff.airline_name = flight.airline_name"
  69. else:
  70. query += " AND \"" + email + "\" = purchases.customer_email AND purchases.ticket_id = ticket.ticket_id AND flight.flight_num = ticket.flight_num"
  71. print query
  72. cursor = mysql.connect().cursor()
  73. cursor.execute(query)
  74. data = cursor.fetchall()
  75.  
  76. #print data
  77. cursor.close()
  78. return data
  79.  
  80. def airport(place):
  81. ret = "<option value=\"\"></option>"
  82. query = "SELECT DISTINCT " + place + "_airport FROM flight"
  83. cursor = mysql.connect().cursor()
  84. cursor.execute(query)
  85. data = cursor.fetchall()
  86.  
  87. #print data
  88. for x in data:
  89. ret += "<option value=\"" + str(x[0]) + "\">" + str(x[0]) + "</option>"
  90.  
  91. cursor.close()
  92. return ret
  93.  
  94. @app.route('/viewflight')
  95. def viewflight():
  96. if not session.get('logged_in'):
  97. return redirect('/')
  98.  
  99. cursor = mysql.connect().cursor()
  100. error = None
  101. ret1 = ""
  102. ret2 = ""
  103. customer = False
  104. fly = ""
  105.  
  106. if session.get('type') == "staff":
  107. initial = (datetime.datetime.now() + datetime.timedelta(-30)).strftime("%Y-%m-%d")
  108. final = datetime.datetime.now().strftime("%Y-%m-%d")
  109.  
  110. if request.args.get('date'):
  111. if inject in request.args.get('date'):
  112. print "Warning! SQL Injection!"
  113. else:
  114. initial = request.args.get('date')
  115. if request.args.get('date2'):
  116. if inject in request.args.get('date2'):
  117. print "Warning! SQL Injection!"
  118. else:
  119. final = request.args.get('date2')
  120.  
  121. query = '''SELECT flight_num, airline_name, departure_airport, departure_time, arrival_airport, arrival_time, status
  122. FROM flight WHERE DATE(departure_time) BETWEEN "''' + initial + '''" AND "''' + final + '''" AND airline_name =
  123. (SELECT airline_name FROM airline_staff WHERE username = "''' + session.get('email') + '''")'''
  124.  
  125. if request.args.get('dcity'):
  126. if inject in request.args.get('dcity'):
  127. print "Warning! SQL Injection!"
  128. else:
  129. query += " AND departure_airport='" + dcity + "'"
  130. if request.args.get('acity'):
  131. if inject in request.args.get('acity'):
  132. print "Warning! SQL Injection!"
  133. else:
  134. query += " AND arrival_airport='" + acity + "'"
  135. cursor.execute(query)
  136. data = cursor.fetchall()
  137. print data
  138.  
  139. for x in data:
  140. if x[6] == "Upcoming" or x[6] == "Delayed":
  141. ret1 += "<tr><td><a href='?num=" + str(x[0]) + "'>" + str(x[0]) + "</a></td><td>" + str(x[1]) + "</td><td>" + str(x[2]) + "</td><td>" + str(x[3]) + \
  142. "</td><td>" + str(x[4]) + "</td><td>" + str(x[5]) + "</td><td>" + str(x[6]) + "</td></tr>"
  143. else:
  144. ret2 += "<tr><td><a href='?num=" + str(x[0]) + "'>" + str(x[0]) + "</a></td><td>" + str(x[1]) + "</td><td>" + str(x[2]) + "</td><td>" + str(x[3]) + \
  145. "</td><td>" + str(x[4]) + "</td><td>" + str(x[5]) + "</td><td>" + str(x[6]) + "</td></tr>"
  146.  
  147. if request.args.get('num'):
  148. for x in xrange(0,len(data)):
  149. if not [item for item in data if int(request.args.get('num')) in item]:
  150. error = "Flight number is not in the airline."
  151. break
  152. if not error:
  153. query = '''SELECT customer_email FROM purchases NATURAL JOIN ticket WHERE purchases.ticket_id = ticket.ticket_id AND
  154. ticket.airline_name = (SELECT airline_name FROM airline_staff WHERE username = "''' + session.get('email') +'''")
  155. AND ticket.flight_num = "''' + request.args.get('num') + '''"'''
  156. cursor.execute(query)
  157. data = cursor.fetchall()
  158. customer = True
  159. for x in data:
  160. fly += "<tr><td>" + str(x[0]) + "</td></tr>"
  161. print fly
  162. else:
  163. data = dbsearch(None, None, None, None, None, session.get('email'))
  164.  
  165. for x in data:
  166. if x[6] == "Upcoming" or x[6] == "Delayed":
  167. ret1 += "<tr>"
  168. for y in x:
  169. ret1 += "<td>" + str(y) + "</td>"
  170. ret1 += "</tr>"
  171. else:
  172. ret2 += "<tr>"
  173. for y in x:
  174. ret2 += "<td>" + str(y) + "</td>"
  175. ret2 += "</tr>"
  176.  
  177. cursor.close()
  178. return render_template('viewflight.html', error = error, search=ret1, search2=ret2, dcity=airport("departure"), acity=airport("arrival"), date=datetime.datetime.now().strftime("%Y-%m-%d"), num=request.args.get('num'), customer=fly)
  179.  
  180. @app.route('/purchase', methods=['GET', 'POST'])
  181. def purchase():
  182. if not session.get('logged_in') or session.get('type') == "staff":
  183. return redirect('/')
  184.  
  185. conn = mysql.connect()
  186. cursor = conn.cursor()
  187. error = None
  188. ret1 = ""
  189. ret2 = ""
  190.  
  191. if request.method == 'POST':
  192. if (request.form['flight'] and inject in request.form['flight']):
  193. print "Warning! SQL Injection!"
  194. else:
  195. if session.get('type') == "agent":
  196. query = '''SELECT customer_email FROM ticket, flight, purchases WHERE ticket.flight_num = flight.flight_num AND
  197. purchases.ticket_id = ticket.ticket_id AND customer_email = "''' + request.form['email'] + '''" AND flight.flight_num = ''' + \
  198. request.form['flight'].split()[1]
  199. cursor.execute(query)
  200. data = cursor.fetchall()
  201. if data:
  202. error = "Customer has already purchased this flight"
  203.  
  204. if not error:
  205. query = '''SELECT MAX(ticket_id) FROM ticket'''
  206. cursor.execute(query)
  207. max_ticket = int(cursor.fetchall()[0][0])+1
  208. query = '''INSERT INTO ticket VALUES (''' + str(max_ticket) + ''', "''' + request.form['flight'].split()[0] + '''", ''' + \
  209. request.form['flight'].split()[1] + ''')'''
  210. print request.form['flight'].split()[1]
  211. cursor.execute(query)
  212. conn.commit()
  213.  
  214. if session.get('type') == "customer":
  215. query = '''INSERT INTO purchases VALUES (''' + str(max_ticket) + ''', "''' + session.get('email') + '''", NULL, "''' + \
  216. datetime.datetime.now().strftime("%Y-%m-%d") + '''")'''
  217. #print query
  218. cursor.execute(query)
  219. conn.commit()
  220. else:
  221. if (request.form['email'] and inject in request.form['email']):
  222. print "Warning! SQL Injection!"
  223. else:
  224. if not error:
  225. query = '''INSERT INTO purchases VALUES (''' + str(max_ticket) + ''', "''' + request.form['email'] + \
  226. '''", (SELECT booking_agent_id FROM booking_agent WHERE email ="''' + session.get('email') + '''"), "''' + \
  227. datetime.datetime.now().strftime("%Y-%m-%d") + '''")'''
  228. #print query
  229. try:
  230. cursor.execute(query)
  231. except Exception as e:
  232. print e
  233. error = "Customer does not exist."
  234. conn.commit()
  235.  
  236. if session.get('type') == "customer":
  237. query = '''SELECT flight_num, airline_name, departure_airport, departure_time, arrival_airport, arrival_time, status
  238. FROM flight WHERE flight_num NOT IN (SELECT flight_num FROM purchases, ticket WHERE purchases.ticket_id = ticket.ticket_id
  239. AND customer_email = "''' + session.get('email') + '''")
  240. AND flight_num NOT IN (SELECT flight_num FROM ticket NATURAL JOIN flight NATURAL JOIN airplane GROUP BY flight_num HAVING COUNT(ticket_id) >= SUM(seats))
  241. AND (status='Upcoming' OR status='Delayed')'''
  242. cursor.execute(query)
  243. data1 = cursor.fetchall()
  244.  
  245.  
  246. query = '''SELECT airline_name, flight_num, price FROM flight
  247. WHERE flight_num NOT IN (SELECT flight_num FROM purchases, ticket WHERE purchases.ticket_id = ticket.ticket_id
  248. AND customer_email = "''' + session.get('email') + '''")
  249. AND flight_num NOT IN (SELECT flight_num FROM ticket NATURAL JOIN flight NATURAL JOIN airplane GROUP BY flight_num HAVING COUNT(ticket_id) >= SUM(seats))
  250. AND (status='Upcoming' OR status='Delayed')'''
  251. cursor.execute(query)
  252. data2 = cursor.fetchall()
  253.  
  254.  
  255. for x in data1:
  256. ret1 += "<tr>"
  257. for y in x:
  258. ret1 += "<td>" + str(y) + "</td>"
  259. ret1 += "</tr>"
  260.  
  261. for x in data2:
  262. ret2 += "<option value=\"" + str(x[0]) + " " + str(x[1]) + "\">" + str(x[0]) + " Flight " + str(x[1]) + ": $" + str(x[2]) + "</option>"
  263. else:
  264. data1 = dbsearch(None, None, None, None, None, None, True)
  265.  
  266. query = "SELECT airline_name, flight_num, price FROM flight WHERE (status = \"Upcoming\" OR status = \"Delayed\")"
  267. cursor = mysql.connect().cursor()
  268. cursor.execute(query)
  269. data2 = cursor.fetchall()
  270.  
  271.  
  272. for x in data1:
  273. ret1 += "<tr>"
  274. for y in x:
  275. ret1 += "<td>" + str(y) + "</td>"
  276. ret1 += "</tr>"
  277.  
  278. for x in data2:
  279. ret2 += "<option value=\"" + str(x[0]) + " " + str(x[1]) + "\">" + str(x[0]) + " Flight " + str(x[1]) + ": $" + str(x[2]) + "</option>"
  280.  
  281. cursor.close()
  282. return render_template('purchase.html', error=error, show=ret1, flight=ret2)
  283.  
  284. @app.route('/commission')
  285. def commission():
  286. if not session.get('logged_in') or session.get('type') != "agent":
  287. return redirect('/')
  288.  
  289. initial = (datetime.datetime.now() + datetime.timedelta(-30)).strftime("%Y-%m-%d")
  290. final = datetime.datetime.now().strftime("%Y-%m-%d")
  291.  
  292. if request.args.get('date'):
  293. if inject in request.args.get('date'):
  294. print "Warning! SQL Injection!"
  295. else:
  296. initial = request.args.get('date')
  297. if request.args.get('date2'):
  298. if inject in request.args.get('date2'):
  299. print "Warning! SQL Injection!"
  300. else:
  301. final = request.args.get('date2')
  302.  
  303. query = '''SELECT SUM(price), AVG(price), COUNT(price) FROM flight, ticket, purchases, booking_agent WHERE email = "''' + session.get('email') + \
  304. '''" AND booking_agent.booking_agent_id = purchases.booking_agent_id AND purchases.ticket_id = ticket.ticket_id
  305. AND ticket.flight_num = flight.flight_num AND (purchase_date BETWEEN "''' + initial + '''" AND "''' + final + '''")'''
  306. cursor = mysql.connect().cursor()
  307. cursor.execute(query)
  308. data = cursor.fetchall()
  309. #print data
  310.  
  311. cursor.close()
  312. return render_template('commission.html', tcomm=format(data[0][0],'.2f'), acomm=format(data[0][1],'.2f'), ttix=data[0][2], date=initial, date2=final)
  313.  
  314. @app.route('/flight', methods=['GET', 'POST'])
  315. def flight():
  316. if not session.get('logged_in') or session.get('type') != "staff":
  317. return redirect('/')
  318.  
  319. conn = mysql.connect()
  320. cursor = conn.cursor()
  321. ret = ""
  322. search = ""
  323. if request.args.get('type'):
  324. redir = "flight?type=" + request.args.get('type')
  325.  
  326. error = None
  327. if request.method == 'POST':
  328. if request.args.get('type') == "create":
  329. depart = datetime.datetime.strptime(request.form['ddate'] + " " + request.form['dtime'], '%Y-%m-%d %H:%M')
  330. arrive = datetime.datetime.strptime(request.form['adate'] + " " + request.form['atime'], '%Y-%m-%d %H:%M')
  331.  
  332. if request.form['dairport'] == request.form['aairport']:
  333. error = "Departure airport and arrival airport cannot be the same."
  334. elif (request.form['dairport'] and inject in request.form['dairport']) or (request.form['airplane'] and inject in request.form['airplane']) or \
  335. (request.form['ddate'] and inject in request.form['ddate']) or (request.form['dtime'] and inject in request.form['dtime']) or \
  336. (request.form['aairport'] and inject in request.form['aairport']) or (request.form['adate'] and inject in request.form['adate']) or \
  337. (request.form['price'] and inject in request.form['price']) or (request.form['atime'] and inject in request.form['atime']):
  338. print "Warning! SQL Injection!"
  339. elif depart > arrive:
  340. error = "Departure time cannot be later than arrival time."
  341. elif request.form['dairport'] and request.form['ddate'] and request.form['dtime'] and request.form['aairport'] and request.form['adate'] and \
  342. request.form['atime'] and request.form['price'] and request.form['airplane']:
  343. query = '''SELECT status FROM flight WHERE airplane_id = ''' + request.form['airplane'] + ''' AND "''' + str(depart) + \
  344. '''" <= arrival_time AND "''' + str(arrive) + '''" >= departure_time'''
  345. #print query
  346. cursor.execute(query)
  347. data = cursor.fetchall()
  348. #print data
  349.  
  350. if data:
  351. error = "There is already a flight for Airplane " + request.form['airplane'] + " between " + str(depart) + " and " + str(arrive) + "."
  352. else:
  353. query = '''INSERT INTO flight VALUES ((SELECT airline_name FROM airline_staff WHERE username = "''' + session.get('email') + \
  354. '''"), (SELECT MAX(flight_num) FROM flight as f1, airline_staff WHERE username = "''' + session.get('email') + \
  355. '''" AND airline_staff.airline_name = f1.airline_name)+1, "''' + request.form['dairport'] + '''", "''' + request.form['ddate'] + \
  356. ''' ''' + request.form['dtime'] + ''':00", "''' + request.form['aairport'] + '''", "''' + request.form['adate'] + \
  357. ''' ''' + request.form['atime'] + ''':00", ''' + request.form['price'] + ''', "Upcoming", ''' + request.form['airplane'] + ''')'''
  358. print query
  359. cursor.execute(query)
  360. data = cursor.fetchall()
  361. conn.commit()
  362. error = "New flight for Airplane " + request.form['airplane'] + " between " + str(depart) + " and " + str(arrive) + " has been successfully added."
  363. elif request.args.get('type') == "change":
  364. if (request.form['status'] and inject in request.form['status']) or (request.form['airplane'] and inject in request.form['airplane']):
  365. print "Warning! SQL Injection!"
  366. else:
  367. query = '''UPDATE flight SET status = "''' + request.form['status'] + '''" WHERE flight_num = ''' + request.form['airplane']
  368. cursor.execute(query)
  369. data = cursor.fetchall()
  370. conn.commit()
  371.  
  372. if request.args.get('type') == "create":
  373. ret = ""
  374. query = '''SELECT airplane_id, seats FROM airplane, airline_staff WHERE airline_staff.username = "''' + session.get('email') + \
  375. '''" AND airline_staff.airline_name = airplane.airline_name'''
  376. cursor.execute(query)
  377. data = cursor.fetchall()
  378.  
  379. query = '''SELECT flight_num, departure_airport, departure_time, arrival_airport, arrival_time, status
  380. FROM flight WHERE DATE(departure_time) > "''' + (datetime.datetime.now() + datetime.timedelta(-30)).strftime("%Y-%m-%d") + '''"
  381. AND status != "Completed"'''
  382. cursor.execute(query)
  383. search_data = cursor.fetchall()
  384.  
  385. for x in search_data:
  386. search += "<tr>"
  387. for y in x:
  388. search += "<td>" + str(y) + "</td>"
  389. search += "</tr>"
  390.  
  391. for x in data:
  392. ret += "<option value=\"" + str(x[0]) + "\"> Airplane " + str(x[0]) + ": " + str(x[1]) + " Seats</option>"
  393.  
  394. cursor.close()
  395. return render_template('flight.html', search=search, airplane=ret, dairport=airport("departure"), aairport=airport("arrival"), today=datetime.datetime.now(), error=error)
  396. elif request.args.get('type') == "change":
  397. query = '''SELECT flight_num, status FROM flight, airline_staff WHERE airline_staff.username = "''' + session.get('email') + \
  398. '''" AND airline_staff.airline_name = flight.airline_name AND (status='Upcoming' OR status='Delayed' OR status='In-Progress')'''
  399. cursor.execute(query)
  400. data = cursor.fetchall()
  401.  
  402. for x in data:
  403. ret += "<option value=\"" + str(x[0]) + "\"> Flight " + str(x[0]) + ": " + str(x[1]) + "</option>"
  404.  
  405. cursor.close()
  406. return render_template('flight.html', airplane=ret)
  407. else:
  408. cursor.close()
  409. return render_template('flight.html')
  410.  
  411. @app.route('/add', methods=['GET', 'POST'])
  412. def add():
  413. if not session.get('logged_in') or session.get('type') != "staff":
  414. return redirect('/')
  415.  
  416. if request.args.get('type'):
  417. redir = "add?type=" + request.args.get('type')
  418.  
  419. error = None
  420. ret = ""
  421. conn = mysql.connect()
  422. cursor = conn.cursor()
  423.  
  424. if request.method == 'POST':
  425. if request.args.get('type') == "airplane":
  426. if request.form['seats']:
  427. if inject in request.form['seats']:
  428. print "Warning! SQL Injection!"
  429. else:
  430. query = '''INSERT INTO airplane VALUES ((SELECT airline_name FROM airline_staff WHERE username = "''' + session.get('email') + \
  431. '''"), (SELECT MAX(airplane_id) from airplane as a1)+1, ''' + request.form['seats'] + ''')'''
  432. cursor.execute(query)
  433. data = cursor.fetchall()
  434. conn.commit()
  435. elif request.args.get('type') == "airport":
  436. if request.form['name'] and request.form['city']:
  437. if inject in request.form['name'] or inject in request.form['city']:
  438. print "Warning! SQL Injection!"
  439. else:
  440. query = '''INSERT INTO airport VALUES ("''' + request.form['name'] + '''", "''' + request.form['city'] + '''")'''
  441. cursor.execute(query)
  442. data = cursor.fetchall()
  443. conn.commit()
  444.  
  445. if request.args.get('type'):
  446. if request.args.get('type') == "airplane":
  447. query = '''SELECT airplane_id, seats FROM airplane, airline_staff WHERE airline_staff.username = "''' + session.get('email') + \
  448. '''" AND airline_staff.airline_name = airplane.airline_name'''
  449. cursor.execute(query)
  450. data = cursor.fetchall()
  451. elif request.args.get('type') == "airport":
  452. query = '''SELECT * FROM airport'''
  453. cursor.execute(query)
  454. data = cursor.fetchall()
  455.  
  456. for x in data:
  457. ret += "<tr>"
  458. for y in x:
  459. ret += "<td>" + str(y) + "</td>"
  460. ret += "</tr>"
  461.  
  462. cursor.close()
  463. return render_template('add.html', error=error, data=ret)
  464. else:
  465. cursor.close()
  466. return render_template('add.html')
  467.  
  468. @app.route('/viewagent')
  469. def viewagent():
  470. if not session.get('logged_in') or session.get('type') != "staff":
  471. return redirect('/')
  472.  
  473. ret1 = ""
  474. ret2 = ""
  475. cursor = mysql.connect().cursor()
  476.  
  477. query = '''SELECT booking_agent_id, count(ticket_id) FROM purchases WHERE booking_agent_id IS NOT NULL AND purchase_date >= "''' + \
  478. (datetime.datetime.now() + datetime.timedelta(-30)).strftime("%Y-%m-%d") + '''" GROUP BY booking_agent_id ORDER BY count(ticket_id) DESC LIMIT 5'''
  479. cursor.execute(query)
  480. month_sale = cursor.fetchall()
  481.  
  482. query = '''SELECT booking_agent_id, count(ticket_id) FROM purchases WHERE booking_agent_id IS NOT NULL AND purchase_date >= "''' + \
  483. (datetime.datetime.now() + datetime.timedelta(-365)).strftime("%Y-%m-%d") + '''" GROUP BY booking_agent_id ORDER BY count(ticket_id) DESC LIMIT 5'''
  484. cursor.execute(query)
  485. year_sale = cursor.fetchall()
  486.  
  487. query = '''SELECT booking_agent_id, SUM(price) FROM purchases, flight, ticket WHERE booking_agent_id IS NOT NULL AND purchase_date >= "''' + \
  488. (datetime.datetime.now() + datetime.timedelta(-365)).strftime("%Y-%m-%d") + '''" AND purchases.ticket_id = ticket.ticket_id
  489. AND flight.flight_num = ticket.flight_num GROUP BY booking_agent_id ORDER BY SUM(price) DESC LIMIT 5'''
  490. cursor.execute(query)
  491. year_commission = cursor.fetchall()
  492.  
  493. query = '''SELECT booking_agent_id, count(ticket_id) FROM purchases WHERE booking_agent_id IS NOT NULL AND purchase_date >= "''' + \
  494. (datetime.datetime.now() + datetime.timedelta(-30)).strftime("%Y-%m-%d") + '''" GROUP BY booking_agent_id ORDER BY booking_agent_id DESC LIMIT 5'''
  495. cursor.execute(query)
  496. all1 = cursor.fetchall()
  497.  
  498. query = '''SELECT booking_agent_id, count(ticket_id) FROM purchases WHERE booking_agent_id IS NOT NULL AND purchase_date >= "''' + \
  499. (datetime.datetime.now() + datetime.timedelta(-365)).strftime("%Y-%m-%d") + '''" GROUP BY booking_agent_id ORDER BY booking_agent_id DESC LIMIT 5'''
  500. cursor.execute(query)
  501. all2 = cursor.fetchall()
  502.  
  503. query = '''SELECT booking_agent_id, SUM(price) FROM purchases, flight, ticket WHERE booking_agent_id IS NOT NULL AND purchase_date >= "''' + \
  504. (datetime.datetime.now() + datetime.timedelta(-365)).strftime("%Y-%m-%d") + '''" AND purchases.ticket_id = ticket.ticket_id
  505. AND flight.flight_num = ticket.flight_num GROUP BY booking_agent_id ORDER BY booking_agent_id DESC LIMIT 5'''
  506. cursor.execute(query)
  507. all3 = cursor.fetchall()
  508.  
  509. for x in xrange(0,len(month_sale)):
  510. ret1 += "<tr><td>" + str(month_sale[x][0]) + "</td><td>" + str(month_sale[x][1]) + \
  511. "</td><td>" + str(year_sale[x][0]) + "</td><td>" + str(year_sale[x][1]) + \
  512. "</td><td>" + str(year_sale[x][0]) + "</td><td>" + str(year_commission[x][1]) + "</td></tr>"
  513.  
  514. for x in xrange(0,len(all1)):
  515. ret2 += "<tr><td>" + str(all1[x][0]) + "</td><td>" + str(all1[x][1]) + \
  516. "</td><td>" + str(all2[x][1]) + "</td><td>" + str(all3[x][1]) + "</td></tr>"
  517.  
  518. cursor.close()
  519. return render_template('viewagent.html', top=ret1, all_agent=ret2)
  520.  
  521. @app.route('/freqcustomer')
  522. def freqcustomers():
  523. if not session.get('logged_in') or session.get('type') != "staff":
  524. return redirect('/')
  525.  
  526. error = None
  527. ret1 = ""
  528. ret2 = ""
  529. customer = False
  530. cursor = mysql.connect().cursor()
  531.  
  532. query = '''SELECT customer_email, COUNT(purchases.ticket_id) FROM purchases, ticket WHERE purchases.ticket_id = ticket.ticket_id AND ticket.airline_name =
  533. (SELECT airline_name FROM airline_staff WHERE username = "''' + session.get('email') +'''") GROUP BY customer_email ORDER BY COUNT(ticket_id) DESC LIMIT 10'''
  534. cursor.execute(query)
  535. top1 = cursor.fetchall()
  536.  
  537. query = '''SELECT customer_email, COUNT(purchases.ticket_id) FROM purchases, ticket WHERE purchases.ticket_id = ticket.ticket_id AND ticket.airline_name =
  538. (SELECT airline_name FROM airline_staff WHERE username = "''' + session.get('email') +'''") AND purchase_date >= "''' + \
  539. (datetime.datetime.now() + datetime.timedelta(-365)).strftime("%Y-%m-%d") + '''" GROUP BY customer_email ORDER BY COUNT(ticket_id) DESC LIMIT 10'''
  540. cursor.execute(query)
  541. top2 = cursor.fetchall()
  542.  
  543. for x in xrange(0,len(top1)):
  544. ret1 += "<tr><td><a href='?email=" + str(top1[x][0]) + "'>" + str(top1[x][0]) + "</a></td><td>" + str(top1[x][1]) + \
  545. "</td><td><a href='?email=" + str(top2[x][0]) + "'>" + str(top2[x][0]) + "</a></td><td>" + str(top2[x][1]) + "</td></tr>"
  546.  
  547.  
  548. #print request.args.get('email')
  549. if request.args.get('email'):
  550. for x in xrange(0,len(top1)):
  551. if not ([item for item in top1 if request.args.get('email') in item] or [item for item in top2 if request.args.get('email') in item]):
  552. error = "Email is not in frequent customers."
  553. break
  554. if not error:
  555. query = '''SELECT flight_num FROM purchases NATURAL JOIN ticket WHERE purchases.ticket_id = ticket.ticket_id AND
  556. ticket.airline_name = (SELECT airline_name FROM airline_staff WHERE username = "''' + session.get('email') +'''")
  557. AND customer_email = "''' + request.args.get('email') + '''"'''
  558. cursor.execute(query)
  559. data = cursor.fetchall()
  560. customer = True
  561. for x in data:
  562. ret2 += "<tr><td>" + str(x[0]) + "</td></tr>"
  563.  
  564. #print ret2
  565. cursor.close()
  566. return render_template('freqcustomer.html', top=ret1, customer=ret2, error=error, email=request.args.get('email'))
  567.  
  568. @app.route('/report')
  569. def report():
  570. if not session.get('logged_in') or session.get('type') != "staff":
  571. return redirect('/')
  572.  
  573. cursor = mysql.connect().cursor()
  574.  
  575. initial = datetime.datetime.now() + datetime.timedelta(-30)
  576. final = datetime.datetime.now()
  577.  
  578. if request.args.get('date'):
  579. initial = datetime.datetime.strptime(request.args.get('date'), '%Y-%m-%d')
  580. if request.args.get('date'):
  581. final = datetime.datetime.strptime(request.args.get('date2'), '%Y-%m-%d')
  582.  
  583. query = '''SELECT COUNT(purchases.ticket_id) FROM purchases, ticket WHERE purchases.ticket_id = ticket.ticket_id AND ticket.airline_name =
  584. (SELECT airline_name FROM airline_staff WHERE username = "''' + session.get('email') +'''") AND DATE(purchase_date) BETWEEN "''' + \
  585. initial.strftime("%Y-%m-%d") + '''" AND "''' + final.strftime("%Y-%m-%d") + '''"'''
  586. cursor.execute(query)
  587. ticket_num = cursor.fetchall()
  588.  
  589. loop1 = initial
  590. loop2 = final
  591. chart_data = ""
  592. while loop1 < loop2:
  593. query = '''SELECT COUNT(purchases.ticket_id) FROM purchases, ticket WHERE purchases.ticket_id = ticket.ticket_id AND ticket.airline_name =
  594. (SELECT airline_name FROM airline_staff WHERE username = "''' + session.get('email') +'''") AND DATE(purchase_date) BETWEEN "''' + \
  595. loop1.strftime("%Y-%m-%d") + '''" AND "''' + loop2.strftime("%Y-%m-%d") + '''"'''
  596. cursor.execute(query)
  597. chart_data += "['" + loop2.strftime("%Y-%m-%d") + "', " + str(cursor.fetchall()[0][0]) + ", '#" + str(binascii.b2a_hex(os.urandom(3))) + "'],"
  598. loop2 += datetime.timedelta(-30)
  599. chart_data = chart_data[:len(chart_data)-1]
  600.  
  601. cursor.close()
  602. return render_template('report.html', date=initial, date2=final, tickets=ticket_num[0][0], chart=chart_data)
  603.  
  604. @app.route('/register', methods=['GET', 'POST'])
  605. def register():
  606. if session.get('logged_in'):
  607. return redirect('/')
  608.  
  609. if request.args.get('user'):
  610. redir = "register?user=" + request.args.get('user')
  611.  
  612. conn = mysql.connect()
  613. cursor = conn.cursor()
  614. error = None
  615. logged = False
  616. if request.method == 'POST':
  617. if request.form['password'] != request.form['password2']:
  618. error = "Passwords do not match."
  619. else:
  620. if request.args.get('user') == "agent":
  621. if (request.form['email'] and inject in request.form['email']) or (request.form['id'] and inject in request.form['id']):
  622. print "Warning! SQL Injection!"
  623. else:
  624. query = '''SELECT booking_agent_id FROM booking_agent WHERE booking_agent_id = ''' + request.form['id']
  625. cursor.execute(query)
  626. data = cursor.fetchall()
  627. if data:
  628. error = "Someone has already registered the ID."
  629. else:
  630. query = "INSERT INTO booking_agent VALUES ('" + request.form['email'] \
  631. + "', '" + hashlib.md5(request.form['password']).hexdigest() + "', '" + request.form['id'] + "')"
  632. logged = True
  633. cursor.execute(query)
  634. conn.commit()
  635. data = cursor.fetchall()
  636. print data
  637. print query
  638. elif request.args.get('user') == "staff":
  639. if (request.form['username'] and inject in request.form['username']) or (request.form['fname'] and inject in request.form['fname']) or \
  640. (request.form['lname'] and inject in request.form['lname']) or (request.form['dob'] and inject in request.form['dob']):
  641. print "Warning! SQL Injection!"
  642. else:
  643. query = "INSERT INTO airline_staff VALUES ('" + request.form['username'] \
  644. + "', '" + hashlib.md5(request.form['password']).hexdigest() + "', '" + request.form['fname'] \
  645. + "', '" + request.form['lname'] + "', '" + request.form['dob'] + "')"
  646. logged = True
  647. cursor.execute(query)
  648. conn.commit()
  649. data = cursor.fetchall()
  650. print data
  651. print query
  652. else:
  653. if (request.form['dob'] and inject in request.form['dob']) or (request.form['email'] and inject in request.form['email']) or \
  654. (request.form['building'] and inject in request.form['building']) or (request.form['street'] and inject in request.form['street']) or \
  655. (request.form['city'] and inject in request.form['city']) or (request.form['state'] and inject in request.form['state']) or \
  656. (request.form['phone'] and inject in request.form['phone']) or (request.form['pnum'] and inject in request.form['pnum']) or \
  657. (request.form['pdate'] and inject in request.form['pdate']) or (request.form['pcountry'] and inject in request.form['pcountry']) or \
  658. (request.form['name'] and inject in request.form['name']):
  659. print "Warning! SQL Injection!"
  660. else:
  661. query = "INSERT INTO customer VALUES ('" + request.form['email'] + "', '" + request.form['name'] \
  662. + "', '" + hashlib.md5(request.form['password']).hexdigest() + "', '" + request.form['building'] + "', '" + request.form['street'] \
  663. + "', '" + request.form['city'] + "', '" + request.form['state'] + "', '" + request.form['phone'] \
  664. + "', '" + request.form['pnum'] + "', '" + request.form['pdate'] + "', '" + request.form['pcountry'] \
  665. + "', '" + request.form['dob'] + "')"
  666. logged = True
  667. cursor.execute(query)
  668. conn.commit()
  669. data = cursor.fetchall()
  670. print data
  671. print query
  672.  
  673. if not data and logged:
  674. session['logged_in'] = True
  675. session['email'] = request.form['email']
  676. if request.args.get('user') == "agent":
  677. session['type'] = "agent"
  678. elif request.args.get('user') == "staff":
  679. session['type'] = "staff"
  680. else:
  681. session['type'] = "customer"
  682. return redirect('/')
  683. elif not error:
  684. error = "Username or email already exists, or not all forms were filled out."
  685.  
  686. cursor.close()
  687.  
  688. if request.args.get('user') == "agent":
  689. return render_template('register.html', error=error, agent=True)
  690. if request.args.get('user') == "staff":
  691. return render_template('register.html', error=error, staff=True)
  692. else:
  693. return render_template('register.html', error=error)
  694.  
  695. @app.route('/login', methods=['GET', 'POST'])
  696. def login():
  697. if session.get('logged_in'):
  698. return redirect('/')
  699.  
  700. if request.args.get('user'):
  701. redir = "login?user=" + request.args.get('user')
  702.  
  703. error = None
  704. if request.method == 'POST':
  705. if request.form['email'] and inject in request.form['email']:
  706. query = ""
  707. print "Warning! SQL Injection!"
  708. else:
  709. if request.args.get('user') == "agent":
  710. query = "SELECT email FROM booking_agent WHERE email=\""
  711. elif request.args.get('user') == "staff":
  712. query = "SELECT username FROM airline_staff WHERE username=\""
  713. else:
  714. query = "SELECT email FROM customer WHERE email=\""
  715. query += request.form['email'] + "\" AND password=\"" + hashlib.md5(request.form['password']).hexdigest() + "\""
  716. cursor = mysql.connect().cursor()
  717. cursor.execute(query)
  718. data = cursor.fetchall()
  719. cursor.close()
  720.  
  721. if data:
  722. session['logged_in'] = True
  723. session['email'] = request.form['email']
  724. if request.args.get('user') == "agent":
  725. session['type'] = "agent"
  726. elif request.args.get('user') == "staff":
  727. session['type'] = "staff"
  728. else:
  729. session['type'] = "customer"
  730. return redirect('/')
  731. else:
  732. error = "Invalid username/email or password"
  733.  
  734.  
  735. if request.args.get('user') == "agent":
  736. return render_template('login.html', error=error, ltype="Email", register="agent", ttype="Booking Agent")
  737. if request.args.get('user') == "staff":
  738. return render_template('login.html', error=error, ltype="Username", register="staff", ttype="Airline Staff")
  739. else:
  740. return render_template('login.html', error=error, ltype="Email", ttype="Customer")
  741.  
  742. @app.route('/logout')
  743. def logout():
  744. if not session.get('logged_in'):
  745. return redirect('/')
  746.  
  747. args = session['type']
  748. session['logged_in'] = False
  749. session['type'] = None
  750. return redirect('/login?user=' + args)
  751.  
  752. @app.route('/')
  753. def home():
  754. ret1 = ""
  755. ret2 = ""
  756.  
  757. data1 = dbsearch(None, request.args.get('dcity'), request.args.get('acity'), request.args.get('ddate'))
  758. data2 = dbsearch(request.args.get('num2'), None, None, request.args.get('ddate2'), request.args.get('adate2'))
  759.  
  760. for x in data1:
  761. ret1 += "<tr>"
  762. for y in x:
  763. ret1 += "<td>" + str(y) + "</td>"
  764. ret1 += "</tr>"
  765.  
  766. for x in data2:
  767. ret2 += "<tr>"
  768. for y in x:
  769. ret2 += "<td>" + str(y) + "</td>"
  770. ret2 += "</tr>"
  771.  
  772. return render_template("home.html", dcity = airport("departure"), acity = airport("arrival"), search = ret1, search2 = ret2, \
  773. ddate=request.args.get('ddate'), ddate2=request.args.get('ddate2'), adate2=request.args.get('adate2'))
  774.  
  775. if __name__ == '__main__':
  776. app.secret_key = os.urandom(12)
  777. app.run(host='0.0.0.0', port=80)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!