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