def insert_account_subscription(purchase_date, expire_date, account_id,
subscription_id):
sql = "INSERT INTO account_subscription(purchase_date, expire_date, account_id, subscription_id) VALUES(%s,%s,%s,%s)"
values = (str(purchase_date), str(expire_date), str(account_id),
str(subscription_id))
cursor.execute(sql, values)
connection.commit()
def account_with_highest_income_for_cinema():
sql = """select
accounts.login, SUM(IF(modifiers.type = 1, shows.price - (modifiers.value * shows.price/100), shows.price - modifiers.value )) as earnings FROM
tickets_archive, modifiers, shows, accounts
WHERE
tickets_archive.modifier_id = modifiers.id and tickets_archive.show_id = shows.id and tickets_archive.account_id = accounts.id"""
cursor.execute(sql)
return cursor.fetchall()
def branches_with_highest_income_for_cinema():
sql = """select
branches.city, branches.address, SUM(
IF(modifiers.type = 1, shows.price - (modifiers.value * shows.price/100), shows.price - modifiers.value )) as earnings FROM
tickets_archive, modifiers, shows, branches, rooms, room_show
WHERE
tickets_archive.modifier_id = modifiers.id and tickets_archive.show_id = shows.id and shows.id = room_show.show_id and room_show.room_id = rooms.id and rooms.branch_id = branches.id"""
cursor.execute(sql)
return cursor.fetchall()
def GenerateForeignKeys(self, table_name):
sql = "SELECT id FROM " + table_name
cursor.execute(sql)
records = cursor.fetchall()
id_list = []
for string_val in records:
id_list.append(int(string_val[0]))
id_list.sort()
return id_list
def taken_seats_on_show():
sql = """ SELECT seat_ticket.ticket_id AS 'ticket', tickets.show_id, seats.number AS 'taken seat', movies.title
FROM seat_ticket, seats, shows, movies, tickets
WHERE seat_ticket.seat_id = seats.id
AND seat_ticket.ticket_id=tickets.id
AND tickets.show_id = shows.id
AND shows.movie_id = movies.id
AND shows.id = 4
ORDER BY seats.id;"""
cursor.execute(sql)
return cursor.fetchall()
def top3_genres_in_year(year="2020"):
sql = f"""SELECT GenresList.Genre, COUNT(GenresList.Genre) AS CountValue
FROM ((
SELECT genres.name AS Genre, tickets.id, shows.show_date
FROM ((movies INNER JOIN (genres INNER JOIN genre_movie ON genres.id = genre_movie.genre_id) ON movies.id = genre_movie.movie_id) INNER JOIN shows ON movies.id = shows.movie_id) INNER JOIN tickets ON shows.id = tickets.show_id
GROUP BY genres.name, tickets.id, shows.show_date
HAVING (YEAR(shows.show_date)={ year })) AS GenresList
)
GROUP BY GenresList.Genre
ORDER BY CountValue DESC
LIMIT 3"""
cursor.execute(sql)
return cursor.fetchall()
def average_price_of_ticket_in_this_month():
sql = """SELECT AVG(IF(modifiers.type=1, shows.price - (modifiers.value * shows.price/100), shows.price-modifiers.value )) AS "average price this month"
FROM shows, modifiers, tickets_archive, accounts
WHERE shows.show_date IN(
SELECT shows.show_date
FROM shows
WHERE DATE(show_date) >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
AND tickets_archive.modifier_id = modifiers.id
AND tickets_archive.show_id = shows.id
AND tickets_archive.account_id = accounts.id
)"""
cursor.execute(sql)
return cursor.fetchall()
def most_often_played_movie_in_year():
sql = """SELECT shows.movie_id, movies.title, shows.show_date,
COUNT(shows.movie_id) AS times_played
FROM shows, movies
WHERE shows.movie_id = movies.id AND shows.show_date IN (
SELECT shows.show_date
FROM shows
WHERE DATE(show_date)>=DATE_SUB(NOW(),INTERVAL 1 YEAR)
)
GROUP BY movie_id
ORDER BY times_played DESC
LIMIT 1;"""
cursor.execute(sql)
return cursor.fetchall()
def most_popular_seats():
sql = """SELECT seats.number AS seat_number,rows.number AS row_number, COUNT(seat_ticket.seat_id) as popularity FROM seats INNER JOIN seat_ticket ON seat_ticket.seat_id = seats.id INNER JOIN rows ON seats.rows_id=rows.id GROUP BY seat_ticket.seat_id ORDER BY COUNT(seat_ticket.seat_id) DESC LIMIT 10"""
cursor.execute(sql)
return cursor.fetchall()
def insert_account(login, password, email):
sql = "INSERT INTO accounts(login, password,email) VALUES(%s,%s,%s)"
values = (login, password, email)
cursor.execute(sql, values)
connection.commit()
def insert_restriction(rest_name):
sql = "INSERT INTO restrictions(name) VALUE(%s)"
values = (rest_name, )
cursor.execute(sql, values)
connection.commit()
def insert_discount(name, mod_id):
sql = "INSERT INTO discounts(name, modifier_id) VALUES(%s,%s)"
values = (name, str(mod_id))
cursor.execute(sql, values)
connection.commit()
def insert_subscription(name, description, mod_id):
sql = "INSERT INTO subscriptions(name, description, modifier_id) VALUES(%s,%s,%s)"
values = (name, description, str(mod_id))
cursor.execute(sql, values)
connection.commit()
def insert_ticket(show_id, seat_id, modifier_id, account_id, email):
sql = "INSERT INTO tickets(show_id, seat_id, modifier_id, account_id, email) VALUES(%s,%s,%s,%s,%s)"
values = (str(show_id), str(seat_id), str(modifier_id), str(account_id),
email)
cursor.execute(sql, values)
connection.commit()
def insert_genre_movie(movie_id, genre_id):
sql = "INSERT INTO genre_movie(movie_id, genre_id) VALUES(%s,%s)"
values = (str(movie_id), str(genre_id))
cursor.execute(sql, values)
connection.commit()
def insert_movie(title, description, rest_id):
sql = "INSERT INTO movies(title, description, restriction_id) VALUES(%s,%s,%s)"
values = (title[:50], description, str(rest_id))
cursor.execute(sql, values)
connection.commit()
def insert_genre(genre_name):
sql = "INSERT INTO genres(name) VALUE(%s)"
values = (genre_name, )
cursor.execute(sql, values)
connection.commit()
def insert_branch(city, address):
sql = "INSERT INTO branches(city, address) VALUES(%s,%s)"
values = (city, address)
cursor.execute(sql, values)
connection.commit()
def today_movies(date="2020-11-04"):
sql = "SELECT movies.title FROM shows INNER JOIN movies ON shows.movie_id = movies.id WHERE shows.show_date = '" + date + "'"
cursor.execute(sql)
return cursor.fetchall()
def insert_room(number, branch_id):
sql = "INSERT INTO rooms(number, branch_id) VALUES(%s,%s)"
values = (str(number), str(branch_id))
cursor.execute(sql, values)
connection.commit()
def movie_with_highest_income():
sql = "select movies.title, SUM(IF(modifiers.type = 1, shows.price-(modifiers.value * shows.price/100),shows.price -modifiers.value )) as earnings FROM tickets_archive,modifiers,shows,movies WHERE tickets_archive.modifier_id = modifiers.id and tickets_archive.show_id = shows.id and shows.movie_id = movies.id"
cursor.execute(sql)
return cursor.fetchall()
def insert_row(number, room_id):
sql = "INSERT INTO rows(number, room_id) VALUES(%s,%s)"
values = (str(number)[:1], str(room_id))
cursor.execute(sql, values)
connection.commit()
def insert_seat(number, vip, row_id):
sql = "INSERT INTO seats(number, vip, rows_id) VALUES(%s,%s,%s)"
values = (str(number), str(vip), str(row_id))
cursor.execute(sql, values)
connection.commit()
def insert_show(show_date, show_time, price, movie_id):
sql = "INSERT INTO shows(show_date, show_time, price, movie_id) VALUES(%s,%s,%s,%s)"
values = (str(show_date), str(show_time), str(price), str(movie_id))
cursor.execute(sql, values)
connection.commit()
def today_played():
sql = "SELECT show_date, show_time, price, movies.title FROM shows, movies WHERE DATE(show_date)=CURDATE() AND shows.movie_id=movies.id"
cursor.execute(sql)
return cursor.fetchall()
def insert_room_show(room_id, show_id):
sql = "INSERT INTO room_show(room_id, show_id) VALUES(%s,%s)"
values = (str(room_id), str(show_id))
cursor.execute(sql, values)
connection.commit()
def find_by_id(cls, _id):
result = cursor.execute("SELECT quote FROM quotes WHERE _id = %s", (_id,))
row = cursor.fetchone()
if row:
return cls(*row)
def insert_modifier(type, name, value):
sql = "INSERT INTO modifiers(type, name, value) VALUES(%s,%s,%s)"
values = (str(type), name, str(value))
cursor.execute(sql, values)
connection.commit()
