def query3():
if request.method == 'POST':
sala = request.form.get('sale')
film = request.form.get('film')
if sala != 'Seleziona...' and film != 'Seleziona...':
settimana = date.today() - timedelta(days=7)
duesettimane = date.today() - timedelta(days=14)
mese = date.today() - timedelta(days=30)
conn = choiceEngine()
#numeri di posti prenotati per sala per film
unasettimana = select([func.count(booking.c.id).label('count')]).\
select_from(booking.join(movieSchedule, booking.c.idmovieSchedule == movieSchedule.c.id).\
join(movies,movieSchedule.c.idMovie == movies.c.id)).\
where(
and_(movieSchedule.c.idMovie == bindparam('film'),#controlla che funzioni bene la clausola where , datetime.now()???
movieSchedule.c.theater == bindparam('sala'),\
movieSchedule.c.dateTime.between(bindparam('tempo'),\
datetime.datetime.now())))
titolo = select([movies]).where(movies.c.id == film)
ristitolo = conn.execute(titolo).fetchone()
ris1 = conn.execute(unasettimana, {
'sala': sala,
'film': film,
'tempo': settimana
}).fetchone()
ris2 = conn.execute(unasettimana, {
'sala': sala,
'film': film,
'tempo': duesettimane
}).fetchone()
ris3 = conn.execute(unasettimana, {
'sala': sala,
'film': film,
'tempo': mese
}).fetchone()
conn.close()
return render_template("/manager/statistiche/resultOccupazioneSala.html", sala = sala,\
film = ristitolo['title'], settimana = ris1['count'],\
duesettimane= ris2['count'], mese = ris3['count'])
#mi servono per visualizzare le possibili scelte tra film e sale
s3 = select([theaters]) #trovo tutte le sale
s41 = movieSchedule.join(movies, movieSchedule.c.idMovie == movies.c.id)
#trovo solo i film con prenotazioni, mi serve il distinct perche non voglio doppioni
s4 = select([func.distinct(movies.c.id).label('id'),
movies.c.title]).select_from(s41).order_by(movies.c.title)
conn = choiceEngine()
sale = conn.execute(s3)
film = conn.execute(s4)
resp = make_response(
render_template("/manager/statistiche/occupazioneSala.html",
theaters=sale,
movies=film))
conn.close()
return resp
def register():
if request.method == 'POST':
name = request.form.get("name")
surname = request.form.get("surname")
email = request.form.get("email")
password = request.form.get("password")
birthdate = request.form.get("birthdate")
if not name or not email or not password or not birthdate or not surname:
flash("Devi inserire tutti i dati")
return redirect("/register")
#eta' minima di iscrizione 13 anni
min = date.today() - timedelta(days=4745)
if datetime.strptime(birthdate, "%Y-%m-%d").date(
) > min: #mi serve per convertire una stringa in un tipo datetime
flash("Inserisci una data di compleanno valida", "error")
return redirect("/register")
conn = choiceEngine()
#mi serve per controllare la mail inserita non sia gia stata utilizzata
u = select([users]).where(users.c.email == bindparam('email'))
y = conn.execute(u, {'email': email}).fetchone()
conn.close()
if y is not None:
flash('Email gia usata, riprova con un altra!', 'error')
return redirect('/register')
conn = choiceEngine()
ins = users.insert(None).values(name=bindparam('name'),
surname=bindparam('surname'),
email=bindparam('email'),
password=bindparam('password'))
conn.execute(ins, {
'name': name,
'surname': surname,
'email': email,
'password': password
})
conn.close()
conn = choiceEngine()
query = select([users]).where(users.c.email == bindparam('email'))
ris = conn.execute(query, {'email': email}).fetchone()
insclients = clients.insert(None).values(id=ris.id,
birthDate=birthdate,
credit=0.)
conn.execute(insclients)
conn.close()
flash('Ti sei registrato correttamente!', 'info')
return redirect("/")
return render_template("/user/noLogged/register.html")
def financialReport():
sel = select([managers]).\
where( managers.c.admin == True)
conn = choiceEngine()
res = conn.execute(sel).fetchone()
conn.close()
return render_template("/manager/admin/financialReport.html", result=res)
def pay(id, amount):
conn = choiceEngine()
trans = conn.begin()
try:
#Se il credito non è sufficiente il db manda un errore per il check
#rimuovo il credito dall'utente
u_cl = clients.update().\
where(clients.c.id == bindparam('id_cl')).\
values( credit = clients.c.credit - float(amount) )
conn.execute(u_cl, {'id_cl': id})
#selezione dell'id del manager
s_mn = select([managers]).\
where(managers.c.admin == True)
result = conn.execute(s_mn).fetchone()
#decremento il bilancio dell'amministratore
u_mn = managers.update().\
where(managers.c.id == result['id']).\
values( financialReport = result['financialReport'] + float(amount) )
conn.execute(u_mn)
trans.commit()
resp = True
except:
trans.rollback()
resp = False
finally:
conn.close()
trans.close()
return resp
def queryAndTemplate(s, htmlTemplate, otherPar=""):
conn = choiceEngine()
result = conn.execute(s)
resp = make_response(
render_template(htmlTemplate, result=result, par=otherPar))
conn.close()
return resp
def removeGenre():
if request.method == 'POST':
id = request.form.get('genre')
if id:
#cancella solo se non ci sono film collegati
conn = choiceEngine()
#lo faccio dentro un try perchè se ci sono film collegati va in errore perchè condizione sulla chiave esterna
try:
rem = genres.delete().\
where(genres.c.id == bindparam('id'))
result = conn.execute(rem,{'id' : id})
flash('Genere rimosso con successo!', 'info')
resp = redirect(url_for( 'listGenres'))
except:
flash('Il genere ha dei film collegati, sei sicuro di non volerlo modificare?', 'error')
resp = redirect(url_for('removeGenre'))
finally:
conn.close()
return resp
flash('Inserire i dati richiesti !', 'error')
sel = select([genres])
return queryAndTemplate(sel, '/tables/genre/removeGenre.html')
def removeTheater():
if request.method == 'POST':
id = request.form.get("id")
if id:
conn = choiceEngine()
conn = conn.execution_options(isolation_level="SERIALIZABLE")
trans = conn.begin()
try:
#verifico se ci sono spettacoli collegati
sel = select([movieSchedule]).\
where( movieSchedule.c.theater == bindparam('id'))
if queryHasResultWithConnection(sel, conn, {'id': id}):
#verifico se gli spettacoli collegati sono futuri
sel = select([movieSchedule]).\
where(
and_(
movieSchedule.c.theater == bindparam('id'),
movieSchedule.c.dateTime >= datetime.today()
)
)
if queryHasResultWithConnection(sel, conn, {'id': id}):
#non posso cancellare
flash(
"""Non si può rimuovere la sala {} perchè ci sono proiezioni non ancora andate in onda.\n
Riassegna le proiezioni ad un altra sala. """
.format(id), 'error')
raise
else:
#devo mettere non disponibile
up = theaters.update().\
where(theaters.c.id == bindparam('t_id')).\
values(available = False)
flash("Sala DISATTIVATA!", 'info')
conn.execute(up, {'t_id': id})
trans.commit()
ret = redirect(url_for('listTheaters'))
else:
#posso cancellarlo
rm = theaters.delete().\
where(theaters.c.id == bindparam('id'))
flash("Sala rimossa!", 'info')
conn.execute(rm, {'id': id})
trans.commit()
ret = redirect(url_for('listTheaters'))
except:
trans.rollback()
ret = redirect(url_for('removeTheater'))
finally:
conn.close()
trans.close()
return ret
else:
flash('You have to insert the value to remove', 'error')
return queryAndTemplate(selectTheaters,
"/tables/theater/removeTheater.html")
def queryHasResult(q, args=None):
conn = choiceEngine()
if args:
result = conn.execute(q, args).fetchone()
else:
result = conn.execute(q).fetchone()
conn.close()
return True if result else False
def occupazioneSala():
if request.method == 'POST':
conn = choiceEngine()
query = select([movies.c.id, movies.c.title]).\
where(exists(select([movieSchedule.join(theaters, movieSchedule.c.theater == theaters.c.id)]).\
where(and_(movies.c.id == movieSchedule.c.idMovie, (theaters.c.seatsCapacity / 100) * 75 < (select(count(booking.c.id).\
where(booking.c.idmovieSchedule == movieSchedule.c.id)))))))
user = conn.execute(query).fetchone()
conn.close()
def queryAndFun(s, nameFun, args=None):
conn = choiceEngine()
if args:
result = conn.execute(s, args)
else:
result = conn.execute(s)
conn.close()
return redirect(url_for(nameFun))
def removeMovie():
if request.method == 'POST':
id = request.form.get('id')
if id:
conn = choiceEngine()
conn = conn.execution_options( isolation_level="SERIALIZABLE" )
trans = conn.begin()
try:
#verifico se ci sono spettacoli collegati
sel = select([movieSchedule]).\
where( movieSchedule.c.idMovie == bindparam('id'))
if queryHasResultWithConnection(sel, conn, {'id' : id}):
#verifico se gli spettacoli collegati sono futuri
sel = select([movieSchedule]).\
where(
and_(
movieSchedule.c.idMovie == bindparam('id'),
movieSchedule.c.dateTime >= datetime.today()
)
)
if queryHasResultWithConnection(sel, conn, {'id' : id}):
#non posso cancellare
flash( """Non si può rimuovere il film perchè ci sono proiezioni non ancora andate in onda.\n
Riassegna le proiezioni ad un altro film. """, 'error')
raise
else:
#devo mettere non disponibile
up = movies.update().\
where(movies.c.id == bindparam('t_id')).\
values(available = False)
flash("Film DISABILITATO!", 'info')
conn.execute(up, {'t_id' : id} )
trans.commit()
ret = redirect(url_for('listMovies'))
else:
#posso cancellarlo
rm = movies.delete().\
where(movies.c.id == bindparam('id'))
flash("Film rimossa!", 'info')
conn.execute(rm, {'id' : id} )
trans.commit()
ret = redirect(url_for('listMovies'))
except:
trans.rollback()
ret = redirect(url_for('removeMovie'))
finally:
conn.close()
trans.close()
return ret
else:
flash('Inserisci tutti i dati richiesti', 'error')
s = select([movies]).where(movies.c.available == True )
return queryAndTemplate(s, '/tables/movie/removeMovie.html')
def registerManager():
if request.method == 'POST':
name = request.form.get("name")
surname = request.form.get("surname")
email = request.form.get("email")
password = request.form.get("password")
if not name or not email or not password or not surname: #controllo non ci siano valori mancanti
flash("Devi inserire tutti i dati") #in caso mando un errore
return redirect("/registerManager")
conn = choiceEngine()
#mi serve per controllare che la mail inserita non sia gia stata utilizzata
u = select([users]).where(users.c.email == bindparam('email'))
y = conn.execute(u, {'email': email}).fetchone()
conn.close()
if y is not None:
flash('Email gia usata, riprova con un altra!', 'error')
return redirect('/registerManager')
conn = choiceEngine()
#insert sulla tabella users
ins = users.insert(None).values(name=bindparam('name'),
surname=bindparam('surname'),
email=bindparam('email'),
password=bindparam('password'))
conn.execute(ins, {
'name': name,
'surname': surname,
'email': email,
'password': password
})
conn.close()
conn = choiceEngine()
query = select([users]).where(users.c.email == bindparam('email'))
#mi serve per ritrovarmi l'ID corretto ed effettuare il giusto inserimento nella tabella user
ris = conn.execute(query, {'email': email}).fetchone()
insmanager = managers.insert(None).values(id=ris.id,
admin=False,
financialReport=None)
conn.execute(insmanager)
conn.close()
return redirect("/")
return render_template("/manager/admin/registerManager.html")
def account_info():
conn = choiceEngine()
join = users.join(clients, users.c.id == clients.c.id)
query = select(
[users,
clients]).select_from(join).where(users.c.id == current_user.get_id())
u = conn.execute(query) #ritorna none se non contiene nessuna riga
resp = make_response(
render_template("/user/logged/accountInfo.html", infoPersonali=u))
conn.close()
return resp
def query2():
conn = choiceEngine()
#incasso per film
querynumeroPrenotazioni = select([movies.c.title,func.sum(movieSchedule.c.price).label('sum')]).\
select_from(movies.join(movieSchedule, movies.c.id == movieSchedule.c.idMovie,isouter = True).\
join(booking, movieSchedule.c.id == booking.c.idmovieSchedule, isouter = True)).\
group_by(movies.c.title,movies.c.id).\
order_by(func.sum(movieSchedule.c.price).desc())
ris = conn.execute(querynumeroPrenotazioni).fetchall()
conn.close()
return render_template("/manager/statistiche/saldoFilm.html", saldo=ris)
def selectGenreToUpdate():
if request.method == "POST":
id = request.form.get('choosed')
if id:
sel = select([genres]).\
where(genres.c.id == bindparam('id'))
conn = choiceEngine()
result = conn.execute(sel, {'id' : id}).fetchone()
conn.close()
return render_template('/tables/genre/modifyGenre.html',result = result)
else:
flash('Inserire i dati richiesti !', 'error')
s = select([genres])
return queryAndTemplate(s, "/tables/genre/updateGenre.html")
def soldispesi():
conn = choiceEngine()
ris = select([func.count(booking.c.id).label('count'),genres.c.description]).\
select_from(
booking.join(movieSchedule, booking.c.idmovieSchedule == movieSchedule.c.id).\
join(movies, movieSchedule.c.idMovie == movies.c.id).\
join(genres, movies.c.idGenre == genres.c.id)).\
where(current_user.get_id()== booking.c.clientUsername).\
group_by(movies.c.idGenre,genres.c.description).\
order_by(func.count(booking.c.id).desc())
y = conn.execute(ris).fetchall()
conn.close()
return render_template("/user/logged/visualizzazioni.html", fav=y)
def query1():
queryCount =select([
genres.c.description,
func.count(booking.c.id).label('numero'),
func.avg(booking.c.viewerAge).label('avgAge')]
).\
select_from(
genres.join(movies,genres.c.id == movies.c.idGenre,isouter =True).join(movieSchedule, movies.c.id == movieSchedule.c.idMovie, isouter = True).\
join(booking, movieSchedule.c.id == booking.c.idmovieSchedule, isouter = True)).\
group_by(genres.c.id).order_by(func.count(booking.c.id).desc())
conn = choiceEngine()
ris1 = conn.execute(queryCount).fetchall()
conn.close()
return render_template("/manager/statistiche/statGenere.html",
numeroPrenotazioni=ris1)
def selectMovieToUpdate():
if request.method == 'POST':
id = request.form.get('choosed')
if id:
sel = select([movies]).\
where(movies.c.id == bindparam('id'))
conn = choiceEngine()
r1 = conn.execute(sel, {'id' : id}).fetchone()
sel = select([genres])
r2 = conn.execute(sel)
conn.close()
return render_template("/tables/movie/modifyMovie.html", genres = r2, movie = r1)
else:
flash('Inserire i dati richiesti !', 'error')
s = select([movies.c.id, movies.c.title, movies.c.duration, movies.c.minimumAge, genres.c.description]).\
where( and_( movies.c.idGenre == genres.c.id, movies.c.available ==True))
return queryAndTemplate(s, "/tables/movie/updateMovie.html")
def change1():
if request.method == 'POST':
money = request.form.get("import")
conn = choiceEngine()
base = select([clients]).where(clients.c.id == current_user.get_id())
ris = conn.execute(base).fetchone()
if float(money) < 0:
#non si accettano ricariche negative
flash("Non puoi inserire valori negativi!", 'error')
return redirect("/updateCredit")
query = clients.update().values(
credit=float(money) +
float(ris.credit)).where(clients.c.id == current_user.get_id())
flash("Ricarica avvenuta con successo!", 'info')
conn.execute(query)
conn.close()
return redirect("/updateCredit")
else:
return render_template("/user/logged/updateCredit.html")
def verifymovie():
if request.method == 'POST':
choice = request.form.get("choice") #idmovieSchedule
if choice:
return redirect(url_for("verifyBook", idmovieSchedule=choice))
else:
flash('Effettuare una scelta', 'error')
#mi ritorna le info riguardo alle prenotazioni ordinate per data in senso non crescente
query = select([movieSchedule.c.id, movieSchedule.c.dateTime, movies.c.title, genres.c.description, movies.c.duration, movies.c.minimumAge, movieSchedule.c.theater, movieSchedule.c.price, theaters.c.seatsCapacity, func.count(booking.c.id).label("count")]).\
select_from(movieSchedule.join(movies, movieSchedule.c.idMovie == movies.c.id).\
join(genres, movies.c.idGenre == genres.c.id).join(theaters, theaters.c.id == movieSchedule.c.theater).\
join(booking, booking.c.idmovieSchedule == movieSchedule.c.id, isouter = True)).\
order_by(desc(movieSchedule.c.dateTime)).\
group_by(movieSchedule.c.id, movieSchedule.c.dateTime, movies.c.title, genres.c.description, movies.c.duration, movies.c.minimumAge, movieSchedule.c.theater, movieSchedule.c.price, theaters.c.seatsCapacity).\
where(movieSchedule.c.dateTime > datetime.datetime.now())
conn = choiceEngine()
result = conn.execute(query)
resp = make_response(
render_template("/manager/shared/verifyMovie.html", result=result))
conn.close()
return resp
def selectTheaterToUpdate():
if request.method == "POST":
id = request.form.get('choosed')
if id:
#per poter stampare i dati nella pagina
sel = select([theaters]).\
where(
and_(
theaters.c.id == bindparam('id'),
theaters.c.available == True
)
)
conn = choiceEngine()
result = conn.execute(sel, {'id': id}).fetchone()
conn.close()
return render_template('/tables/theater/modifyTheater.html',
theater=result)
else:
flash('Inserire i dati richiesti !', 'error')
return queryAndTemplate(selectTheaters,
"/tables/theater/updateTheater.html")
def verifyBook(idmovieSchedule):
conn = choiceEngine()
queryTheater = select([theaters.c.id, theaters.c.seatsCapacity]).\
select_from(theaters.join(movieSchedule, theaters.c.id == movieSchedule.c.theater)).\
where(movieSchedule.c.id == bindparam('idmovieSchedule'))
#mi ritorna il numero della sala e la capienza
infoTheater = conn.execute(queryTheater, {
'idmovieSchedule': idmovieSchedule
}).fetchone()
#mi ritorna i posti già prenotati
queryBooking = select([booking.c.seatNumber]).\
select_from(booking.join(movieSchedule, booking.c.idmovieSchedule == movieSchedule.c.id)).\
where(movieSchedule.c.id == bindparam('idmovieSchedule'))
#mi torna una lista di interi contenenti i posti occupati
seatsOccuped = createIntegerListFromQuery(
conn.execute(queryBooking, {'idmovieSchedule': idmovieSchedule}))
resp = make_response(
render_template("/manager/shared/verifyBook.html",
infoTheater=infoTheater,
seatsOccuped=seatsOccuped,
idmovieSchedule=idmovieSchedule))
conn.close()
return resp
def insertTheater():
if request.method == 'POST':
capacity = request.form.get("capacity")
id = request.form.get('id')
if capacity and id: #verifico che mi abbiano passato i parametri e che non siano già registrate sale con lo stesso id
conn = choiceEngine()
#lo faccio dentro un try perchè se ci sono già sale con la stessa PK va in errore
try:
ins = theaters.insert().\
values(id = bindparam('id'), seatsCapacity = bindparam('capacity'))
result = conn.execute(ins, {'id': id, 'capacity': capacity})
flash('Genere sala inserita con successo!', 'info')
resp = redirect(url_for('listTheaters'))
except:
flash('La sala numero {} è già salvata!'.format(id), 'error')
resp = redirect(url_for('insertTheater'))
finally:
conn.close()
return resp
else:
flash("Dati mancanti", 'error')
return render_template("/tables/theater/insertTheater.html")
def insertShowTime():
if request.method == 'POST':
date = request.form.get('date')
price = request.form.get('price')
movie = request.form.get('movie')
theater = request.form.get('theater')
if date and price and movie and theater:
#controllo che l'inserimento non porti a sovrapposizione di spettacoli
conn = choiceEngine()
conn = conn.execution_options(isolation_level="SERIALIZABLE")
trans = conn.begin()
try:
#Trovo la durata del film che voglio andare a mettere in proiezione
sel = select([movies
]).where(movies.c.id == bindparam('id_movie'))
result = conn.execute(sel, {'id_movie': movie}).fetchone()
runningTime = result['duration']
date = datetime.strptime(date, '%Y-%m-%dT%H:%M')
end = date + timedelta(minutes=runningTime)
#verifico che il film che volgio inserire:
# - inizi dopo il film attualmente in proiezione
# o
# - finisca prima del film attualmente in proiezione
sel = select([movieSchedule, movies]).\
where(
and_(
movieSchedule.c.idMovie == movies.c.id,
movieSchedule.c.theater == bindparam('theater'),
movieSchedule.c.dateTime + text("interval '60 seconds'") * movies.c.duration >= bindparam('date'),
movieSchedule.c.dateTime <= end,
)
)
#se c'è un risultato allora la sala è occupata
if (queryHasResultWithConnection(sel, conn, {
'theater': theater,
'date': date
})):
flash("Sala occupata! Cambia sala o orario", 'error')
raise
else:
#inserisco il film nel database
ins = movieSchedule.insert().\
values(dateTime = bindparam('date'), price = bindparam('price'), idMovie = bindparam('movie'), theater = bindparam('theater'))
flash("Spettacolo inserito con successo", 'info')
conn.execute(
ins, {
'date': date,
'price': price,
'movie': movie,
'theater': theater
})
trans.commit()
ret = redirect(url_for('listShowTime'))
except:
trans.rollback()
ret = redirect(url_for('insertShowTime'))
finally:
conn.close()
trans.close()
return ret
else:
flash('Dati mancanti', 'error')
s1 = selectTheaters #trovo tutte le sale
s2 = select([movies
]).where(movies.c.available == True) #trovo tutti i film
conn = choiceEngine()
mv = conn.execute(s2)
th = conn.execute(s1)
resp = make_response(
render_template("/tables/movieSchedule/insertShowTime.html",
theaters=th,
movies=mv))
conn.close()
return resp
def selectTheaters():
s = select([theaters]).where(theaters.c.available == True)
conn = choiceEngine()
result = conn.execute(s)
conn.close()
return result
