def payment_method_update(id):
form = PaymentMethodForm()
with db.connection.cursor() as cursor:
# heroku reconnect
db.reconnect()
cursor.execute(
'SELECT payment_name FROM payment_method WHERE payment_method_id = %s',
(id))
method = cursor.fetchone()
form.name.data = method['payment_name']
if form.validate_on_submit():
# Update from
cursor.execute(
'''UPDATE payment_method SET payment_name = %s WHERE payment_method_id = %s''',
(request.form['name'], id))
# commit update
db.connection.commit()
flash('Payment method updated')
return redirect(url_for('admin.payment_method_list'))
return render_template('admin/payment_method_update.html', form=form)
def country_update(id):
'''Update country'''
form = CountryForm()
with db.connection.cursor() as cursor:
# reconnect to heroku
db.reconnect()
# get country
cursor.execute("SELECT name FROM country WHERE country_id=%s", (id))
country = cursor.fetchone()
# populate field
form.name.data = country['name']
if form.validate_on_submit():
# update country
cursor.execute("UPDATE country SET name = %s WHERE country_id=%s",
(request.form['name'], id))
db.connection.commit()
flash('Update successfull')
return redirect(url_for('admin.country_list'))
return render_template('admin/country_update.html', form=form)
def product_create(id):
form = ProductCreateForm()
with db.connection.cursor() as cursor:
# reconnect by default because heroku server connection is unstable
db.reconnect()
# get this store_id
#cursor.execute('SELECT * FROM user WHERE store_id=(%s)', (id))
#user = cursor.fetchone()
# select all categories
cursor.execute('SELECT * FROM category')
category_list = cursor.fetchall()
form.category.choices = [(category['category_id'],
category['category_name'])
for category in category_list]
if form.validate_on_submit():
cursor.execute(
'''INSERT INTO product (name, price, available, category_id, description, store_id) VALUES (%s, %s, %s, %s, %s, %s)''',
(form.name.data, form.price.data, True, form.category.data,
form.description.data, g.user['store_id']))
# Commit changes to db
db.connection.commit()
flash('Product added')
return redirect(
url_for('store.store_manager', id=g.user['store_id']))
return render_template('product_create.html', form=form)
def profile_update(id):
'''Controller to update the user's email, name, lastname'''
profile_form = ProfileUpdateForm()
address_form = AddressUpdateForm()
password_form = UserPasswordUpdateForm()
with db.connection.cursor() as cursor:
# reconnect to heroku
db.reconnect()
if profile_form.validate_on_submit():
cursor.execute(
'UPDATE customer_view SET firstname=%s, lastname=%s, email=%s WHERE user_id=%s',
(request.form['firstname'], request.form['lastname'],
request.form['email'], id))
db.connection.commit()
flash('Successfully updated')
return redirect(url_for('users.profile', id=g.user['user_id']))
return render_template('profile.html',
profile_form=profile_form,
address_form=address_form,
password_form=password_form)
def product_create():
'''Create new product'''
form = ProductCreateForm()
with db.connection.cursor() as cursor:
# reconnect with heroku
db.reconnect()
# get categories
cursor.execute("SELECT * FROM category")
category_list = cursor.fetchall()
# get stores
cursor.execute("SELECT * FROM store")
store_list = cursor.fetchall()
# populate select field
form.category.choices = [(category['category_id'],
category['category_name'])
for category in category_list]
form.store.choices = [(store['store_id'], store['store_name'])
for store in store_list]
if form.validate_on_submit():
cursor.execute(
"INSERT INTO product (name, price, description, category_id, store_id) VALUES (%s, %s, %s, %s, %s)",
(form.name.data, form.price.data, form.description.data,
form.category.data, form.store.data))
# commit changes
db.connection.commit()
flash('Product added')
return redirect(url_for('admin.product_list'))
return render_template('admin/product_create.html', form=form)
def city_create():
form = CityForm()
with db.connection.cursor() as cursor:
# reconnect to heroku
db.reconnect()
# get countries
cursor.execute('SELECT * FROM country')
# fetch results
country_list = cursor.fetchall()
# select field for countries
form.country.choices = [(country['country_id'], country['name'])
for country in country_list]
if form.validate_on_submit():
cursor.execute(
"INSERT INTO city (name, country_id) VALUES (%s, %s)",
(form.name.data, form.country.data))
# commit to db
db.connection.commit()
flash('City has been added')
return redirect(url_for('admin.city_list'))
return render_template('admin/city_create.html', form=form)
def purchase_history(id):
with db.connection.cursor() as cursor:
db.reconnect()
cursor.execute(
'CREATE VIEW purchase_history_store '
'AS SELECT p.name, oi.quantity, oi.total_price, pm.payment_name, u.firstname, u.lastname '
'FROM product p, order_item oi, payment_method pm, user u, transaction t '
'WHERE t.payment_method_id=pm.payment_method_id '
'AND t.order_item_id=oi.order_item_id '
'AND oi.product_id=p.product_id '
'AND t.user_id= u.user_id '
'AND t.store_id=(%s)', (id))
db.connection.commit()
cursor.execute('SELECT * FROM purchase_history_store')
phs = cursor.fetchall()
print(phs)
cursor.execute('DROP VIEW purchase_history_store')
db.connection.commit()
return render_template('purchase_history_store.html', phs=phs)
def store_detail(id):
'''Display the store\'s name & owner'''
with db.connection.cursor() as cursor:
db.reconnect()
# Inner join to get the the necessary data to display
# the
cursor.execute(
'''SELECT u.firstname, u.lastname, s.store_name
FROM user u
INNER JOIN store s
ON u.store_id=s.store_id
WHERE s.store_id = (%s)
''', (id))
# fetch the data
owner = cursor.fetchone()
# select this store's products
cursor.execute(
'''SELECT p.product_id, p.name, p.description, p.price
FROM product p
INNER JOIN store s ON p.store_id=s.store_id
WHERE p.store_id=%s''', (id))
product_list = cursor.fetchall()
return render_template("store_detail.html",
owner=owner,
product_list=product_list)
def register():
'''Controller for registering new users'''
form = UserRegistrationForm()
if form.validate_on_submit():
with db.connection.cursor() as cursor:
db.reconnect()
# insert new user object into user
cursor.execute(
'INSERT INTO user (firstname, lastname, email, password, is_active) VALUES (%s, %s, %s, %s, %s)',
(form.firstname.data, form.lastname.data, form.email.data,
generate_password_hash(form.password1.data), True))
# commit to db
db.connection.commit()
# select new registered user
cursor.execute('SELECT user_id FROM user WHERE email LIKE (%s)',
(form.email.data))
user = cursor.fetchone()
session.pop('user', None)
session['is_authenticated'] = True
session['user'] = user
flash('Awesome! You just created an account and can now login')
# redirect user to address form
return redirect(url_for('users.register_address', id=user['user_id']))
else:
print(form.errors)
print('Failed to insert new user to db')
return render_template('register.html', form=form)
def admin_update(id):
'''Update selected administrator'''
form = AdminUpdateForm()
with db.connection.cursor() as cursor:
# reconnect to heroku
db.reconnect()
# retrieve selected admin data
cursor.execute('SELECT * FROM admin WHERE admin_id=%s', int(id))
admin = cursor.fetchone()
# populate fields
form.username.data = admin['username']
form.email.data = admin['email']
if form.validate_on_submit():
# UPDATE admin
cursor.execute(
"UPDATE admin SET username = %s, email = %s, password1 = %s, password2 = %s WHERE admin_id = %s",
(request.form['username'], request.form['email'],
form.password1.data, form.passord2.data, id))
db.connection.commit()
return redirect(url_for('admin.admin_list'))
return render_template('admin/admin_update.html', form=form, admin=admin)
def order_confirm():
form = PaymentMethodForm()
with db.connection.cursor() as cursor:
# reconnect to heroku
db.reconnect()
# get the current user data
cursor.execute('SELECT * FROM customer_view')
customer = cursor.fetchone()
# get the payment methods
cursor.execute('SELECT * FROM payment_method')
payment_methods = cursor.fetchall()
form.payment_method.choices = [(p['payment_method_id'],
p['payment_name'])
for p in payment_methods]
if form.validate_on_submit():
# insert into transaction and related tables
cursor.execute(
"INSERT INTO transaction (payment_method, user_id) VALUES (%s, %s)",
(form.payment_method.data, session['user'].user_id))
db.connection.commit()
# select the transaction that belongs to this user
cursor.execute('SELECT * FROM transaction WHERE user_id=%s',
(session['user'].user_id))
transaction = cursor.fetch()
return redirect(url_for('order_confirmed.html'))
return render_template('order_confirm.html', form=form, customer=customer)
def store_delete(id):
'''Delete store'''
with db.connection.cursor() as cursor:
# reconnect to heroku
db.reconnect()
#
return redirect(url_for('users.account', id=g.user['user_id']))
def customer_create():
'''Create new user'''
form = CustomerCreateForm()
with db.connection.cursor() as cursor:
# reconnect to heroku server
db.reconnect()
# get all the cities
cursor.execute('SELECT * FROM city')
city_list = cursor.fetchall()
form.city.choices = [(city['city_id'], city['name'])
for city in city_list]
if form.validate_on_submit():
# insert into user first
cursor.execute(
'INSERT INTO user (firstname, lastname, email, password, is_active) VALUES (%s, %s, %s, %s, %s)',
(form.firstname.data, form.lastname.data, form.email.data,
generate_password_hash(form.password1.data), True))
db.connection.commit()
# select this user
#cursor.execute('SELECT user_id FROM user WHERE email LIKE (%s)', (
# form.email.data))
#user = cursor.fetchone()
cursor.execute(
'INSERT INTO address (line1, line2, line3, postal_code, city_id) VALUES (%s, %s, %s, %s, %s)',
(form.address_line1.data, form.address_line2.data,
form.address_line3.data, form.postal_code.data,
form.city.data))
db.connection.commit()
cursor.execute('SELECT address_id FROM address WHERE line1=%s',
(form.address_line1.data))
address = cursor.fetchone()
cursor.execute('UPDATE user SET address_id=%s',
(address['address_id']))
## insert address into customer_view
#cursor.execute('UPDATE customer_view SET address_line1=%s, address_line2=%s, address_line3=%s, postal_code=%s, city_id=%s WHERE user_id=%s', (
# form.address_line1.data,
# form.address_line2.data,
# form.address_line3.data,
# form.postal_code.data,
# form.city.data,
# user['user_id']))
db.connection.commit()
flash(f'{form.firstname.data} {form.lastname.data} created')
return redirect(url_for('admin.customer_list'))
return render_template('admin/customer_create.html', form=form)
def payment_method_delete(id):
'''Delete selected payment method'''
with db.connection.cursor() as cursor:
db.reconnect()
# delete from
cursor.execute(
'DELETE FROM payment_method WHERE payment_method_id = %s', (id))
# Commit changes
return redirect(url_for('admin.payment_method_list'))
def admin_list():
'''List of all the administrators'''
with db.connection.cursor() as cursor:
# reconnect to heroku server
db.reconnect()
# Select all administrators
cursor.execute('SELECT * FROM admin')
# fetch the result
admin_list = cursor.fetchall()
return render_template('admin/admin_list.html', admin_list=admin_list)
def category_list():
'''List of all the categories'''
with db.connection.cursor() as cursor:
# reconnect to heroku
db.reconnect()
# retrieve all categories
cursor.execute('SELECT * FROM category')
# fetch results
category_list = cursor.fetchall()
return render_template('admin/category_list.html',
category_list=category_list)
def product_update(id):
'''Update selected product'''
form = ProductUpdateForm()
with db.connection.cursor() as cursor:
# reconnect with heroku
db.reconnect()
# get select product to update
cursor.execute(
"SELECT name, price, description, category_id, store_id FROM product WHERE product_id = %s",
(id))
product = cursor.fetchone()
# get categories
cursor.execute("SELECT * FROM category")
category_list = cursor.fetchall()
# get stores
cursor.execute("SELECT * FROM store")
store_list = cursor.fetchall()
# populate select field
form.name.data = product['name']
form.price.data = product['price']
form.description.data = product['description']
form.category.data = product['category_id']
form.store.data = product['store_id']
# select fields
form.category.choices = [(category['category_id'],
category['category_name'])
for category in category_list]
form.store.choices = [(store['store_id'], store['store_name'])
for store in store_list]
if form.validate_on_submit():
# update the selected product
cursor.execute(
"UPDATE product SET name = %s, price = %s, description = %s, category_id = %s, store_id = %s WHERE product_id = %s",
(request.form['name'], request.form['price'],
request.form['description'], request.form['category'],
request.form['store'], id))
# commit changes
db.connection.commit()
flash('Product added')
return redirect(url_for('admin.product_list'))
return render_template('admin/product_update.html', form=form)
def customer_delete(id):
'''Delete selected customer from db'''
with db.connection.cursor() as cursor:
# reconnect to heroku
db.reconnect()
# delete
cursor.execute('DELETE FROM user WHERE user_id=%s', (id))
# commmit changes
db.connection.commit()
flash('Customer deleted')
return redirect(url_for('admin.customer_list'))
def customer_list():
'''List of all the customers'''
with db.connection.cursor() as cursor:
# reconnect to heroku server
db.reconnect()
# Select all customers
cursor.execute(
'SELECT u.user_id, u.firstname, u.lastname, u.email, u.joined_on, u.is_active, s.store_name FROM user u LEFT JOIN store s ON u.store_id=s.store_id ORDER BY u.joined_on DESC'
)
customer_list = cursor.fetchall()
return render_template('admin/customer_list.html',
customer_list=customer_list)
def payment_method_list():
'''List out the payment methods'''
with db.connection.cursor() as cursor:
# reconnect to heroku
db.reconnect()
# Retrieve all payment methods
cursor.execute(
'SELECT payment_method_id AS id, payment_name AS name FROM payment_method'
)
payment_method_list = cursor.fetchall()
return render_template('admin/payment_method_list.html',
payment_method_list=payment_method_list)
def product_delete(id):
with db.connection.cursor() as cursor:
# reconnect to heroku clear_db
db.reconnect()
# delete product from database
cursor.execute('DELETE FROM product WHERE product.product_id=%s',
(int(id)))
db.connection.commit()
# success message
flash('Product deleted')
return redirect(url_for('store.store_manager', id=g.user['store_id']))
def account(id):
'''Controller for the user account'''
with db.connection.cursor() as cursor:
# reconnect to heroku
db.reconnect()
cursor.execute(
'SELECT firstname, lastname, email, address_line1, address_line2, address_line3, postal_code, city_id, city_name FROM customer_view WHERE user_id=%s',
(id))
user = cursor.fetchone()
image = url_for('static', filename="profile_img/default_avatar.png")
return render_template('user_dashboard.html', image=image, user=user)
def store_manager(id):
'''Display the store owner's products'''
with db.connection.cursor() as cursor:
db.reconnect()
cursor.execute(
'''SELECT p.product_id, p.name, p.price, p.category_id, p.added_on, c.category_name FROM product p
INNER JOIN category c
ON p.category_id=c.category_id
WHERE p.store_id = (%s)''', (id))
product_list = cursor.fetchall()
print(session)
return render_template('store_manager.html', product_list=product_list)
def admin_delete(id):
'''Delete selected administrator'''
with db.connection.cursor() as cursor:
# reconnect to heroku
db.reconnect()
# delete from admin
cursor.execute('DELETE FROM admin WHERE admin.admin_id=(%s)',
(int(id)))
# commit changes
db.connection.commit()
flash('Administrator has been deleted')
return redirect(url_for('admin.admin_list'))
def address_list():
'''List of all address'''
with db.connection.cursor() as cursor:
# reconnect to heroku server
db.reconnect()
# select all addresses
cursor.execute('SELECT * FROM address_view')
# fetch all
address_list = cursor.fetchall()
return render_template('admin/address_list.html',
address_list=address_list)
def store_list():
'''List of all the stores'''
with db.connection.cursor() as cursor:
# reconnect to heroku
db.reconnect()
# retrieve all stores
cursor.execute(
'''SELECT s.store_id, s.store_name, s.joined_on, concat(u.firstname, ' ', u.lastname) AS fullname FROM store s
INNER JOIN user u ON s.store_id=u.store_id
''')
# fetch results
store_list = cursor.fetchall()
return render_template('admin/store_list.html', store_list=store_list)
def filtered_by_category(name):
with db.connection.cursor() as cursor:
# reconnect to heroku
db.reconnect()
cursor.execute('SELECT DISTINCT * FROM category')
category_list = cursor.fetchall()
cursor.execute(
'SELECT * FROM product_view WHERE category_name LIKE (%s)', (name))
product_list = cursor.fetchall()
return render_template('product_filtered_by_category.html',
product_list=product_list,
category_list=category_list)
def address_update(id):
'''Add a new address'''
form = AddressCreateForm()
with db.connection.cursor() as cursor:
# recconect to heroku
db.reconnect()
# get selected address date
cursor.execute(
'SELECT line1, line2, line3, postal_code, city_id FROM address WHERE address_id = %s',
(id))
# get address
address = cursor.fetchone()
# get all cities for select form
cursor.execute('''SELECT city_id, name FROM city''')
city_list = cursor.fetchall()
form.city.choices = [(city['city_id'], city['name'])
for city in city_list]
# populate fields
form.line1.data = address['line1']
form.line2.data = address['line2']
form.line3.data = address['line3']
form.postal_code.data = address['postal_code']
#form.city.data = address['city_id']
#form.country.data = address['country_id']
print(form.city.data)
print(type(address['city_id']))
if form.validate_on_submit():
# Update selected address
cursor.execute(
"UPDATE address SET line1 = %s, line2 = %s, line3 = %s, postal_code = %s, city_id = %s WHERE address_id = %s",
(request.form['line1'], request.form['line2'],
request.form['line3'], request.form['postal_code'],
form.city.data, id))
#commit to db
db.connection.commit
flash('Address updated')
return redirect(url_for('admin.address_list'))
return render_template('admin/address_update.html', form=form)
def product_list():
'''List of all the products sorted by store owner'''
with db.connection.cursor() as cursor:
# reconnect to heroku
db.reconnect()
# retrieve all products and order by the latest added on timestamp
cursor.execute(
'SELECT p.product_id, p.name, p.price, p.added_on, s.store_name, c.category_name FROM product p '
'INNER JOIN category c ON c.category_id=p.category_id '
'INNER JOIN store s ON p.store_id=s.store_id ORDER BY p.added_on DESC'
)
# fetch results
product_list = cursor.fetchall()
return render_template('admin/product_list.html',
product_list=product_list)
def purchase_history(id):
with db.connection.cursor() as cursor:
db.reconnect()
# SELECT ALL transaction from this user
cursor.execute(
'SELECT t.transaction_id, t.ordered_on FROM transaction t WHERE t.user_id=%s',
(id))
transaction_list = cursor.fetchall()
cursor.execute(
'SELECT firstname, lastname, email, address_line1, address_line2, address_line3, postal_code, city_id, city_name FROM customer_view WHERE user_id=%s',
(id))
user = cursor.fetchone()
return render_template('purchase_history_user.html',
transaction_list=transaction_list,
user=user)