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