def deleteCoupon(coupon_id):
db = get_db()
db.execute(
'DELETE FROM DISCOUNT WHERE DISCOUNT.DiscountID = ?', (coupon_id,)
)
db.commit()
return redirect(url_for('coupon.couponList'))
def edit(coupon_id):
coupon = get_coupon(coupon_id)
if request.method == 'POST':
db = get_db()
error = None
discountType = request.form['discountType']
discountName = request.form['discountName']
discountString = request.form['discountString']
discountPercentage = request.form['discountPercentage']
if db.execute(
'SELECT discountString FROM DISCOUNT WHERE DISCOUNT.DiscountString = ?',
(discountString,)
).fetchone() is not None and (discountString != coupon['DiscountString']):
error = '折扣碼重複!'
if error is None:
db.execute(
'UPDATE DISCOUNT SET DiscountName = ?, DiscountString = ?, DiscountTypeID = ?, DiscountPercentage = ?'
' WHERE DiscountID = ?',
(discountName, discountString, discountType, discountPercentage, coupon['DiscountID'])
)
db.commit()
return redirect(url_for('coupon.couponList'))
flash(error)
return render_template('coupon/editCouponInfo.html', coupon=coupon)
def register():
if g.user is not None:
return redirect('goods.index')
if request.method == 'POST':
username = request.form['username']
account = request.form['account']
password = request.form['password']
id = request.form['identification']
gender = request.form['gender']
cellphone = request.form['cellphone']
email = request.form['email']
db = get_db()
error = None
if not account:
error = 'Account is required.'
elif not password:
error = 'Password is required.'
elif db.execute('SELECT AccountID FROM ACCOUNT WHERE Account = ?',
(account, )).fetchone() is not None:
error = 'Account {} is already registered.'.format(account)
# 待修改
if error is None:
db.execute(
'INSERT INTO ACCOUNT (Account, Password, PermissionID, UserName, IdentificationNumber, Gender, CellphoneNumber, Email) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
(account, generate_password_hash(password), 3, username, id,
gender, cellphone, email))
db.commit()
return redirect(url_for('user.login'))
flash(error)
return render_template('user/register.html')
def login():
if g.user is not None:
return redirect('goods.index')
if request.method == 'POST':
account = request.form['account']
password = request.form['password']
db = get_db()
error = None
user = db.execute('SELECT * FROM ACCOUNT WHERE Account = ?',
(account, )).fetchone()
if user is None:
error = 'Incorrect username.'
elif not check_password_hash(user['password'], password):
error = 'Incorrect password.'
if error is None:
session.clear()
session['user_id'] = user['AccountID']
return redirect(url_for('goods.index'))
flash(error)
return render_template('user/login.html')
def addNewCoupon():
if request.method == 'POST':
db = get_db()
error = None
discountType = request.form['discountType']
discountName = request.form['discountName']
discountString = request.form['discountString']
discountPercentage = request.form['discountPercentage']
if db.execute(
'SELECT * FROM DISCOUNT WHERE DISCOUNT.DiscountString = ?',
(discountString,)
).fetchone() is not None:
error = '折扣碼不可重複'
if error is None:
db.execute(
'INSERT INTO DISCOUNT (DiscountName, DiscountString, DiscountTypeID, DiscountPercentage) VALUES (?, ?, ?, ?)',
(discountName, discountString, discountType, discountPercentage,)
)
db.commit()
return redirect(url_for('coupon.couponList'))
return render_template('coupon/addCoupon.html')
def update_goods_stock_quantity(goods_id, new_amount):
db = get_db()
db.execute(
'UPDATE GOODS SET StockQuantity = ? WHERE GOODSID = ? ',
(new_amount, goods_id)
)
db.commit()
def delete_all_goods_from_shopping_cart(account_id):
db = get_db()
db.execute(
'DELETE FROM SHOPPINGCART WHERE SHOPPINGCART.AccountID = ?',
(account_id,),
)
db.commit()
def update_order_status(order_id, status_id):
db = get_db()
db.execute(
'UPDATE ORDERS SET StatusID = ? WHERE OrderID = ? ',
(status_id, order_id)
)
db.commit()
def update_goods(name, goods_type, price, stockQuantity, introduction, imageName, countryOfOrigin, id):
db = get_db()
db.execute(
'UPDATE GOODS SET GoodsName = ?, GoodsType = ?, Price = ?, StockQuantity = ?, Introduction = ?, ImageName = ?, CountryOfOrigin = ?'
' WHERE GoodsID = ?',
(name, goods_type, price, stockQuantity, introduction, imageName, countryOfOrigin, id)
)
db.commit()
def get_user_information(account_id):
db = get_db()
userInformation = db.execute(
'SELECT Account, UserName, CellphoneNumber, Gender, Email, PermissionName FROM ACCOUNT, PERMISSION'
' WHERE ACCOUNT.AccountID = ? AND PERMISSION.PermissionID = ACCOUNT.PermissionID',
(account_id,)
).fetchone()
return userInformation
def get_goods(id):
goods = get_db().execute(
'SELECT GoodsID, GoodsName, GoodsType, Price, StockQuantity, Introduction, ImageName, CountryOfOrigin'
' FROM GOODS'
' WHERE GoodsID = ?',
(id,)
).fetchone()
return goods
def add_new_sales_on(order_id, goods_id, amount):
db = get_db()
db.execute(
'INSERT INTO SALES_ON (OrderID, GoodsID, Amount) '
'VALUES (?, ?, ?)',
(order_id, goods_id, amount,)
)
db.commit()
def delete_goods_from_shopping_cart(account_id, goods_id):
db = get_db()
db.execute(
'DELETE FROM SHOPPINGCART'
'WHERE SHOPPINGCART.GoodsID = ? AND SHOPPINGCART.AccountID = ?',
(goods_id, account_id),
)
db.commit()
def get_all_goods():
db = get_db()
goods = db.execute(
'SELECT GoodsID, GoodsName, GoodsType, Price, StockQuantity, Introduction, ImageName, CountryOfOrigin'
' FROM GOODS'
' ORDER BY GoodsID DESC'
).fetchall()
return goods
def check_discount(discount_str):
db = get_db()
goodsDiscount = db.execute(
'SELECT DiscountID, DiscountName, DiscountString, DiscountPercentage, DiscountTypeID FROM DISCOUNT WHERE DiscountString = ?',
(discount_str,)
).fetchone()
db.commit()
return goodsDiscount
def getShoppingCart(AccountID, check_author=True):
user = g.user
myShoppingCart = get_db().execute(
'Select B.Account, C.GoodsID, C.GoodsName, C.ImageName, C.CountryOfOrigin, C.StockQuantity, C.Price, A.Amount, C.Price*A.Amount AS totalPrice '
'FROM SHOPPINGCART AS A, ACCOUNT AS B, GOODS AS C '
'WHERE (A.AccountID=B.AccountID) and (A.GoodsID = C.GoodsID) and '
'A.AccountID = ?', (user['AccountID'], )).fetchall()
return myShoppingCart
def load_logged_in_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
g.user = get_db().execute(
'SELECT A.AccountID, A.Account, A.Password, A.PermissionID, B.PermissionName, A.UserName, A.IdentificationNumber, A.Gender, A.CellphoneNumber, A.Email FROM ACCOUNT AS A, PERMISSION AS B WHERE A.PermissionID = B.PermissionID and AccountID = ?',
(user_id, )).fetchone()
def couponList():
db = get_db()
coupon = db.execute(
'SELECT DiscountID, DiscountName, DiscountString, DiscountPercentage, DiscountTypeName'
' FROM DISCOUNT, DISCOUNTTYPE'
' WHERE DISCOUNT.DiscountTypeID = DISCOUNTTYPE.DiscountTypeID'
)
return render_template('coupon/couponList.html', coupons=coupon)
def add_new_goods(name, goods_type, price, stockQuantity, introduction, imageName, countryOfOrigin):
db = get_db()
db.execute(
'INSERT INTO GOODS (GoodsName, GoodsType, Price, StockQuantity, Introduction, ImageName, CountryOfOrigin)'
' VALUES (?, ?, ?, ?, ?, ?, ?)',
(name, goods_type, price, stockQuantity, introduction, imageName, countryOfOrigin)
)
db.commit()
def add_new_order(account_id, address, shipping_method_id, payment_id, goods_discount, total_price):
db = get_db()
db.execute(
'INSERT INTO ORDERS (AccountID, Address, ShippingMethodID, StatusID, PaymentID, DiscountID, TotalPrice) '
'VALUES (?, ?, ?, ?, ?, ?, ?)',
(account_id, address, shipping_method_id, '1',payment_id, goods_discount, total_price,)
)
db.commit()
def get_user(uid):
db = get_db()
user = db.execute('SELECT * FROM ACCOUNT AS A'
' WHERE A.AccountID = ?', (uid, )).fetchone()
if user is None:
abort(404, "User id {0} doesn't exist.".format(id))
return user
def get_coupon(coupon_id):
db = get_db()
coupon = db.execute(
'SELECT DiscountID, DiscountName, DiscountString, DiscountPercentage, DiscountTypeName'
' FROM DISCOUNT, DISCOUNTTYPE'
' WHERE DISCOUNT.DiscountTypeID = DISCOUNTTYPE.DiscountTypeID AND DISCOUNT.DiscountID = ?',
(coupon_id,)
).fetchone()
return coupon
def get_all_goods_statistics_list():
db = get_db()
goods_statistics_list = db.execute(
'SELECT D.UserName, C.GoodsName, B.Amount, B.Amount * C.Price AS Earn '
'FROM ORDERS AS A, SALES_ON AS B, GOODS AS C, ACCOUNT AS D '
'WHERE A.OrderID = B.OrderID and '
'B.GoodsID = C.GoodsID and '
'A.AccountID = D.AccountID',
).fetchall()
return goods_statistics_list
def search():
db = get_db()
name = request.form['searchName']
goods = db.execute(
'SELECT * FROM GOODS'
' WHERE GoodsName LIKE ?',
('%' + name + '%',)
).fetchall()
return render_template('search/searchResult.html', posts=goods)
def get_all_shopping_cart_goods(account_id):
db = get_db()
my_shopping_cart = db.execute(
'SELECT B.Account, B.UserNAme, C.GoodsID, C.GoodsName, C.StockQuantity, C.Price, A.Amount, C.Price * A.Amount AS total '
'FROM SHOPPINGCART AS A, ACCOUNT AS B, GOODS AS C '
'WHERE A.AccountID = B.AccountID and '
'A.GoodsID = C.GoodsID and '
'A.AccountID = ?',
(account_id,)
).fetchall()
return my_shopping_cart
def searchUser():
if request.method == 'POST':
db = get_db()
name = request.form['searchName']
user = db.execute('SELECT * FROM ACCOUNT'
' WHERE UserName LIKE ?',
('%' + name + '%', )).fetchall()
return render_template('user/userList.html', user=user)
return render_template('user/search.html')
def get_discount(discount_str):
db = get_db()
discount = db.execute(
'SELECT A.DiscountID, A.DiscountName, B.DiscountTypeName, A.DiscountString, DiscountPercentage '
'FROM DISCOUNT AS A, DISCOUNTTYPE AS B '
'WHERE A.DiscountTypeID = B.DiscountTypeID and '
'A.DiscountString = ?',
(discount_str,)
).fetchone()
return discount
def get_all_goods_statistics():
db = get_db()
goods_statistics = db.execute(
'SELECT C.GoodsID, C.GoodsName, C.Price, SUM(B.Amount) AS Amount, SUM(B.Amount * C.Price) AS TotalPrice '
'FROM ORDERS AS A, SALES_ON AS B, GOODS AS C, ACCOUNT AS D '
'WHERE A.OrderID = B.OrderID and '
'B.GoodsID = C.GoodsID and '
'A.AccountID = D.AccountID '
'GROUP BY C.GoodsName '
'ORDER BY C.GoodsID ASC',
).fetchall()
return goods_statistics
def get_goods(GoodsID, check_author=True):
post = get_db().execute(
'SELECT GoodsID, GoodsName, GoodsType, Price, StockQuantity, Introduction, ImageName, CountryOfOrigin'
' FROM GOODS'
' WHERE GoodsID = ?',
(GoodsID,)
).fetchone()
if post is None:
abort(404, "Post id {0} doesn't exist.".format(id))
return post
def get_all_orders():
db = get_db()
orders = db.execute(
'SELECT A.OrderID, B.Account, B.UserName, A.Address, C.ShippingMethodName, D.StatusName, E.PaymentName, F.DiscountName, F.DiscountPercentage, A.TotalPrice '
'FROM ORDERS AS A, ACCOUNT AS B, SHIPPINGMETHOD AS C, STATUS AS D, PAYMENT AS E, DISCOUNT AS F '
'WHERE A.AccountID = B.AccountID and '
'A.ShippingMethodID = C.ShippingMethodID and '
'A.StatusID = D.StatusID and '
'A.PaymentID = E.PaymentID and '
'A.DiscountID = F.DiscountID',
).fetchall()
return orders
