def get_attendance():
query = (
"select idShowing, idCustomer, FirstName, LastName, MovieName, ShowingDateTime, TicketPrice, Rating "
"from Attend left join Customer on Customer_idCustomer=idCustomer "
"left join Showing on Showing_idShowing=idShowing "
"left join Movie on Movie_idMovie=idMovie order by Rating")
return db_connector.query_db(query)
def sql_injection():
data = ''
if request.method == 'POST':
call = 'SELECT * FROM Customer WHERE idCustomer=\'{0}\''.format(request.form.get('sql'))
data = db_connector.query_db(call)
return render_template("sql_injection.html", data=data)
def search_showing(genre=None, date_b=None, date_e=None, not_full=None, title=None):
##NEEDS A SECURE IMPLEMENTATION - VULNERABLE TO SQL INJECTION##
call = "SELECT showing.idShowing, MovieName, ShowingDateTime, TheatreRoom_RoomNumber, TicketPrice, seats_left " \
"FROM Showing left join Movie on Movie_idMovie=idMovie left join " \
"(select idShowing, (Capacity-ifnull(attendance,0)) as seats_left, attendance, Capacity from Showing left join " \
"(select count(Showing_idShowing) as attendance, Showing_idShowing from attend group by Showing_idShowing) as countShow on countShow.Showing_idShowing=idShowing " \
"left join TheatreRoom on TheatreRoom_RoomNumber=RoomNumber) as seatsQuery on seatsQuery.idShowing=showing.idShowing "
if genre or date_b or date_e or title or not_full:
call += "WHERE "
if genre:
call += "Movie_idMovie IN (SELECT idMovie FROM Movie LEFT JOIN Genre on idMovie = Movie_idMovie WHERE Genre=\"{0}\") ".format(genre)
if date_b and date_e:
if genre:
call += "AND "
call += "ShowingDateTime BETWEEN \"{0}\" AND \"{1}\" ".format(date_b, date_e)
if title:
if genre or (date_b and date_e):
call += "AND "
call += "Movie_idMovie IN (SELECT idMovie FROM Movie WHERE MovieName=\"{0}\") ".format(title)
if not_full:
if genre or date_b or date_e or title:
call += "AND "
call += "seats_left > 0"
return db_connector.query_db(call)
def showings():
genres = db_connector.query_db("SELECT DISTINCT Genre FROM Genre")
if request.method == 'GET':
showings = customer_models.search_showing()
elif request.method == 'POST':
showings = customer_models.search_showing(request.form['genre'],
request.form['b_date'],
request.form['a_date'],
request.form.get('not_full'),
request.form['title'])
return render_template('showings.html', showings=showings, genres=genres)
def get_rooms():
query = ("SELECT * FROM TheatreRoom")
return db_connector.query_db(query)
def get_genres():
query = ("SELECT Genre, MovieName, idMovie FROM Genre LEFT JOIN Movie "
"ON Movie_idMovie=idMovie ORDER BY Genre")
return db_connector.query_db(query)
def get_movies():
query = ("SELECT * FROM Movie ORDER BY MovieName")
return db_connector.query_db(query)
def get_customers():
query = ("SELECT * FROM Customer ORDER BY LastName")
return db_connector.query_db(query)
def get_showings():
query = (
"select idShowing, ShowingDateTime, MovieName, TheatreRoom_RoomNumber, TicketPrice "
"from Showing left join Movie on Movie_idMovie=idMovie ORDER BY ShowingDateTime"
)
return db_connector.query_db(query)