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 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 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 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 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()