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 _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 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 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 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_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 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_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 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 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 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 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 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]
