def _get_user_info(self, user_id, username, password, is_manager): self.username = username self.password = password if is_manager: # connect to database conn, cur = get_db() cur.execute(sql_translator('select * from manager where userID=?'), (user_id, )) item = cur.fetchone() self.id = item[1] if item[2]: self.type = 'senior_manager' else: self.type = 'manager' self.name = item[3] self.email = item[4] self.salary = item[5] else: # connect to database conn, cur = get_db() cur.execute( sql_translator('select * from customer where userID=?'), (user_id, )) item = cur.fetchone() self.id = item[1] self.name = item[2] self.type = 'customer' self.email = item[3] self.phone_number = item[4] return self
def record_transaction(response_dict: dict): store_id = int(session['store_id']) purchase_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") shipping_address = "\n".join([ response_dict['address_name'], response_dict['address_street'], response_dict['address_city'], ", ".join([ response_dict['address_state'], response_dict['address_zip'], response_dict['address_country_code'] ]) ]) paypal_id = response_dict['txn_id'] total_payment = response_dict['mc_gross'] conn, cur = get_db() cur.execute( sql_translator('select customerID from customer where userID=?'), (current_user.id, )) customer_id = cur.fetchone()[0] # get items in the cart cur.execute( sql_translator(''' select SC.amount, SC.movieID, salePrice from shopping_cart SC join stock S on S.movieID=SC.movieID and S.storeID=SC.storeID where customerID=? and SC.storeID=? '''), (customer_id, store_id)) records = cur.fetchall() # remove records from database.shopping_cart cur.execute( sql_translator( 'delete from shopping_cart where customerID=? and storeID=?'), (customer_id, store_id)) # insert into database.transaction_info cur.execute( sql_translator('insert into transaction_info values (?,?,?,?,?,?,?)'), (paypal_id, purchase_time, customer_id, store_id, total_payment, shipping_address, 0)) for amount, movie_id, price in records: # update database.stock cur.execute( sql_translator( 'select amount from stock where movieID=? and storeID=?'), (movie_id, store_id)) amount_all = cur.fetchone()[0] cur.execute( sql_translator( 'update stock set amount=? where movieID=? and storeID=?'), (amount_all - amount, movie_id, store_id)) # insert into database.transaction_detail cur.execute( sql_translator('insert into transaction_detail values (?,?,?,?)'), (paypal_id, movie_id, amount, price)) conn.commit() return jsonify({"operation": "record transaction"})
def get_movies_with_params(movie_columns): """ Get list of movie data from database given the parameters. :param movie_columns: which columns are needed (a string) :return: """ # initialize form form = SearchBarForm() # connect to database conn, cur = get_db() # store info store_id = int(session['store_id']) cur.execute(sql_translator('select storeID, region from store')) store_data = cur.fetchall() stores = [] for store in store_data: stores.append({'id': str(store[0]), 'name': store[1]}) # retrieve movie info range_sql, form, is_default = get_range_sql(form, store_id) cur.execute("select {}".format(movie_columns) + range_sql) data = cur.fetchall() # init filter options form.init_options(range_sql) content = copy(context_base) content['search_bar'] = True content['default'] = is_default content['stores'] = stores content['form'] = form return data, content
def movie_details(movie_id): content = copy(context_base) conn, cur = get_db() # get movie info cur.execute(sql_translator('select * from movie where movieID=?'), (movie_id,)) movie_info = cur.fetchone() if movie_info is None: flash("Movie Not Found! Return to Shopping Page.") return redirect(url_for('shopping')) # get genres cur.execute(sql_translator('select genre from genres where movieID=?'), (movie_id,)) genres = cur.fetchall() content['movie_info'] = { 'movie_id': str(movie_info[0]), 'title': movie_info[1], 'summary': movie_info[2], 'year': movie_info[3], 'certificate': movie_info[4], 'rating': movie_info[5], 'imdb_link': imdb_id_to_imdb_link(movie_info[6]), 'genres': ", ".join(map(lambda x: x[0], genres)) } content['title'] = movie_info[1] return render_template('movie_detail.html', **content)
def remove_items_in_cart(): store_id = int(session['store_id']) conn, cur = get_db() cur.execute( sql_translator('select customerID from customer where userID=?'), (current_user.id, )) customer_id = cur.fetchone()[0] # get items in the cart cur.execute( sql_translator( 'select amount, movieID from shopping_cart where customerID=? and storeID=?' ), (customer_id, store_id)) records = cur.fetchall() # remove records from database.shopping_cart cur.execute( sql_translator( 'delete from shopping_cart where customerID=? and storeID=?'), (customer_id, store_id)) # update database.stock for amount, movie_id in records: cur.execute( sql_translator( 'select amountTemp from stock where movieID=? and storeID=?'), (movie_id, store_id)) temp_amount_all = cur.fetchone()[0] cur.execute( sql_translator( 'update stock set amountTemp=? where movieID=? and storeID=?'), (temp_amount_all + amount, movie_id, store_id)) conn.commit() return jsonify({"operation": "remove all items"})
def query_by_username(self, username): # connect to database conn, cur = get_db() cur.execute( sql_translator( 'select userID, password, is_manager from users where username=?' ), (username, )) result = cur.fetchone() if result is None: return None else: return self._get_user_info(result[0], username, result[1], result[2])
def manage_all_customer(): content = copy(context_base) conn, cur = get_db() cur.execute(sql_translator('select name, emailAddress, phoneNumber from customer')) customers = cur.fetchall() conn.close() data = [] for customer in customers: data.append({ 'name': customer[0], 'email': customer[1], 'tele_number': customer[2] }) content['customers'] = data content['current_page'] = '/manage/customers' return render_template('manage_customers.html', **content)
def update_item_in_cart(movie_id): response = request.get_json() amount = response['number'] store_id = int(session['store_id']) conn, cur = get_db() cur.execute( sql_translator('select customerID from customer where userID=?'), (current_user.id, )) customer_id = cur.fetchone()[0] cur.execute( sql_translator( 'select amount from shopping_cart where customerID=? and movieID=? and storeID=?' ), (customer_id, movie_id, store_id)) current_amount = cur.fetchone()[0] # remove or update record into database.shopping_cart if amount <= 0: amount = 0 cur.execute( sql_translator( 'delete from shopping_cart where customerID=? and movieID=? and storeID=?' ), (customer_id, movie_id, store_id)) else: cur.execute( sql_translator( 'update shopping_cart set amount=? where customerID=? and movieID=? and storeID=?' ), (amount, customer_id, movie_id, store_id)) # update amountTemp in database.stock cur.execute( sql_translator( 'select amountTemp from stock where movieID=? and storeID=?'), (movie_id, store_id)) temp_amount_all = cur.fetchone()[0] cur.execute( sql_translator( 'update stock set amountTemp=? where movieID=? and storeID=?'), (temp_amount_all - amount + current_amount, movie_id, store_id)) conn.commit() return jsonify({ "operation": "update item", "movieID": movie_id, "amount": amount })
def count_items_in_cart(): store_id = int(session['store_id']) conn, cur = get_db() cur.execute( sql_translator('select customerID from customer where userID=?'), (current_user.id, )) customer_id = cur.fetchone()[0] cur.execute( sql_translator(''' select COUNT(*) from shopping_cart Shop where customerID=? and Shop.storeID=? '''), (customer_id, store_id)) count = cur.fetchone() return jsonify(count)
def show_history(): content = copy(context_base) conn, cur = get_db() cur.execute(sql_translator('select customerID from customer where userID=?'), (current_user.id,)) customer_id = cur.fetchone()[0] # get order history in database.transaction_info cur.execute(sql_translator(''' select paypalID, purchaseDate, region, totalPrice, shippingAddress, status from transaction_info join store on transaction_info.storeID = store.storeID where customerID=? order by purchaseDate desc '''), (customer_id,)) records = cur.fetchall() history = [] for record in records: order = { 'paypal_id': record[0], 'date': record[1], 'store': record[2], 'total_price': record[3], 'shipping': record[4], 'status': record[5], 'item_list': []} cur.execute(sql_translator(''' select T.movieID, M.title, T.amount, T.unitPrice from transaction_detail T join movie M on T.movieID = M.movieID where paypalID=? '''), (record[0],)) item_list = cur.fetchall() for item in item_list: order['item_list'].append({ 'movieID': str(item[0]), 'title': item[1], 'amount': item[2], 'price': item[3]}) history.append(order) content['history'] = history content['current_page'] = '/show_history' return render_template('show_history.html', **content)
def add_item_to_cart(movie_id): amount = 1 store_id = int(session['store_id']) conn, cur = get_db() cur.execute( sql_translator('select customerID from customer where userID=?'), (current_user.id, )) customer_id = cur.fetchone()[0] cur.execute( sql_translator( 'select amount from shopping_cart where customerID=? and movieID=? and storeID=?' ), (customer_id, movie_id, store_id)) current_amount = cur.fetchone() # insert or update record into database.shopping_cart if current_amount is None: cur.execute( sql_translator('insert into shopping_cart values (?,?,?,?)'), (amount, customer_id, movie_id, store_id)) else: cur.execute( sql_translator( 'update shopping_cart set amount=? where customerID=? and movieID=? and storeID=?' ), (current_amount[0] + amount, customer_id, movie_id, store_id)) # update amountTemp in database.stock cur.execute( sql_translator( 'select amountTemp from stock where movieID=? and storeID=?'), (movie_id, store_id)) temp_amount_all = cur.fetchone()[0] cur.execute( sql_translator( 'update stock set amountTemp=? where movieID=? and storeID=?'), (temp_amount_all - amount, movie_id, store_id)) conn.commit() return jsonify({ "operation": "add item", "movieID": movie_id, "amount": amount })
def get_items(): """Inner function to get items in cart.""" store_id = int(session['store_id']) conn, cur = get_db() cur.execute( sql_translator('select customerID from customer where userID=?'), (current_user.id, )) customer_id = cur.fetchone()[0] # get items in the cart with their price cur.execute( sql_translator(''' select Shop.amount, Shop.movieID, M.title, S.salePrice from shopping_cart Shop join stock S on Shop.movieID=S.movieID and Shop.storeID=S.storeID join movie M on S.movieID = M.movieID where customerID=? and Shop.storeID=? '''), (customer_id, store_id)) records = cur.fetchall() return records
def manage_movie_detail(movie_id): form = MovieDetailForm() content = copy(context_base) conn, cur = get_db() # get movie info cur.execute(sql_translator('select * from movie where movieID=?'), (movie_id,)) movie_info = cur.fetchone() if movie_info is None: flash("Movie Not Found! Return to Shopping Page.") return redirect(url_for('shopping')) # get genres cur.execute(sql_translator('select genre from genres where movieID=?'), (movie_id,)) genres = cur.fetchall() content['movie_info'] = { 'movie_id': str(movie_info[0]), 'title': movie_info[1], 'summary': movie_info[2], 'year': movie_info[3], 'certificate': movie_info[4], 'rating': movie_info[5], 'imdb_link': imdb_id_to_imdb_link(movie_info[6]), 'genres': ", ".join(map(lambda x: x[0], genres)) } if form.is_submitted(): form.summary.data = request.form.get('summary') cur.execute(sql_translator('update movie set title=?, summary=?, year=?, contentRating=?, rating=? where movieID=?'), ( form.title.data, form.summary.data, int(form.year.data), form.content_rating.data, float(form.rating.data), movie_id )) conn.commit() flash("Changes Saved.") content['form'] = form content['title'] = movie_info[1] return render_template('manage_movie_detail.html', **content)
def new_user(self, username, name, password, type, email=None, phone_numer=None, salary=None): self.username = username self.name = name self.password = generate_password_hash(password) self.type = type self.email = email self.phone_number = phone_numer self.salary = salary # connect to database conn, cur = get_db() cur.execute(sql_translator('select max(userID) from users')) current_max_id = cur.fetchone()[0] if current_max_id is not None: self.id = str(current_max_id + 1) else: self.id = str(1) if self.type == 'customer': cur.execute(sql_translator('insert into users values (?,?,?,?)'), (self.id, self.username, self.password, False)) cur.execute( sql_translator('insert into customer values (?,?,?,?,?)'), (None, self.id, self.name, self.email, self.phone_number)) else: cur.execute(sql_translator('insert into users values (?,?,?,?)'), (self.id, self.username, self.password, True)) cur.execute( sql_translator('insert into manager values (?,?,?,?,?,?)'), (None, self.id, False, self.name, self.email, self.salary)) conn.commit()
def init_options(self, range_sql): """Dynamically load filter options""" from movie.database import get_db conn, cur = get_db() # remove "order by ..." if exist order_filter_pattern = re.compile(r'order by .*') range_sql = order_filter_pattern.sub('', range_sql) cur.execute( sql_translator('select distinct year {} order by year desc'). format(range_sql)) years = cur.fetchall() self.year.choices.extend([(year[0], year[0]) for year in years]) # join genres if not if 'join genres G' not in range_sql: idx = range_sql.index("where") range_sql = range_sql[: idx] + 'join genres G on M.movieID = G.movieID\n' + range_sql[ idx:] cur.execute( sql_translator('select distinct genre {} order by genre').format( range_sql)) genres = cur.fetchall() self.genres.choices.extend([(genre[0], genre[0]) for genre in genres]) cur.execute( sql_translator( 'select distinct contentRating {} order by contentRating'). format(range_sql)) content_ratings = cur.fetchall() self.content_rating.choices.extend([ (content_rating[0], content_rating[0]) for content_rating in content_ratings ])
def manage_add_movie(): form = MovieDetailForm() content = copy(context_base) conn, cur = get_db() new_id = str(get_max_movie_id() + 1) store_id = int(session['store_id']) # check upload file if 'img' in request.files: img = request.files['img'] img.save(os.path.join("movie/static/posters", "{}.{}".format(new_id, img.filename.split('.')[-1]))) # search on IMDB if request.form.get('search') == '': if form.imdb_id.data == '': flash("Please enter IMDB ID") else: response = imdb_retrieve_movie_by_id(form.imdb_id.data) form.title.data, form.summary.data, form.year.data, form.content_rating.data, form.rating.data, form.imdb_id.data, form.genres.data, poster_url = response # download poster img = requests.get(poster_url, allow_redirects=True) img_filename = 'movie/static/posters/{}.{}'.format(new_id, poster_url.split(".")[-1]) open(img_filename, 'wb').write(img.content) # resize img im = Image.open(img_filename) im = im.resize((600, 900), Image.ANTIALIAS) im.save(img_filename, "JPEG") if request.form.get('add') == '' and not form.validate(): flash("Please fill in required fields.") elif form.validate_on_submit(): # check if movie existed in movie table cur.execute(sql_translator('select movieID from movie where imdbID=? or title=?'), (form.imdb_id.data, form.title.data)) exist_movie_id = cur.fetchall() if exist_movie_id != []: # Below is the exmaple for error message, if the added movie is in the stock, showing the error like this. return jsonify(message="Added movie already in the stock, current movieID is {}".format(";".join(map(lambda x: str(x[0]), exist_movie_id)))), 500 else: form.summary.data = request.form.get('summary') try: cur.execute(sql_translator( 'insert into movie values (?,?,?,?,?,?,?)'), ( new_id, form.title.data, form.summary.data, int(form.year.data), form.content_rating.data, float(form.rating.data), form.imdb_id.data)) cur.execute(sql_translator( 'insert into stock values (?,?,?,?,?,?)'), ( store_id, new_id, int(form.stock.data), int(form.stock.data), float(form.price.data), float(form.cost.data))) for genre in form.genres.data.split(";"): cur.execute(sql_translator('insert into genres values (?,?)'), (new_id, genre)) conn.commit() flash("New Movie Added.") return redirect(url_for('manage_movies')) except sqlite3.IntegrityError: print('IntegrityError') except connector.errors.IntegrityError: print('IntegrityError') except connector.errors.DataError: print('DataError') content['form'] = form content['max_id'] = new_id content['title'] = 'Add New Movie' return render_template('manage_add_movie.html', **content)
def get_max_movie_id(): # connect to database conn, cur = get_db() cur.execute(sql_translator('select MAX(movieID) from movie')) return cur.fetchone()[0]