def get_cart_items_by_user_id(user_id): """The function find all the items in the user's cart and return the info required by the front-end Keyword arguments: user_id -- the user id """ # Clean the input data user_id = str(user_id).strip() # Check for the existence of user if find_user(param=user_id, method='id') is None: raise ValidationError('Invalid user id.') # Establish db connection dao = DAO() cursor = dao.cursor() # Query database sql = """SELECT cart_item.cart_item_id, cart_item.product_id, product.product_name, product.price, cart_item.amount FROM cart_item, product WHERE cart_item.product_id = product.product_id AND user_id = %(user_id)s ORDER BY created_at DESC""" cursor.execute(sql, {'user_id': user_id}) result = cursor.fetchall() return result
def redeem(user_id, redeem_code): # Clean the input data user_id = str(user_id).strip() redeem_code = str(redeem_code).strip() # Find redeem card redeem_card = find_redeem_card(redeem_code) if redeem_card is None: raise ValidationError('Invalid redeen code.') # Find user user = find_user(method='id', param=user_id) if user is None: raise ValidationError('user not found.') # Establish db connection dao = DAO() cursor = dao.cursor() sql = """UPDATE user SET balance = %(new_balance)s WHERE user_id = %(user_id)s""" new_balance = user['balance'] + redeem_card['value'] cursor.execute(sql, {'new_balance': new_balance, 'user_id': user_id}) sql = """DELETE FROM redeem_card WHERE redeem_code = %(redeem_code)s""" cursor.execute(sql, {'redeem_code': redeem_code}) dao.commit()
def create_cart_item(user_id, product_id, amount=1): """The function creates a cart item if the cart item does not exist. Otherwise, the cart item will be updated in an "append" manner """ # Clean the input data user_id = str(user_id).strip() product_id = str(product_id).strip() amount = str(amount).strip() # Check is the input valid if not user_id or not product_id: raise ValidationError('Invalid identifier(s).') if not amount.isdecimal(): raise ValidationError('Invalid amount.') # Check for the existence of user and product if find_user(method='id', param=user_id) is None: raise ValidationError('Invalid user id.') if find_product(method='product_id', param=product_id) is None: raise ValidationError('Invalid product id.') # Check the user's cart. If the item already exists, perform update instead of insertion item = find_cart_item_id(user_id, product_id) if item is not None: return update_cart_item_amount(item['cart_item_id'], int(item['amount']) + int(amount)) # Establish db connection dao = DAO() cursor = dao.cursor() sql = """INSERT INTO cart_item ( user_id, product_id, amount ) VALUES ( %(user_id)s, %(product_id)s, %(amount)s )""" cursor.execute(sql, { 'user_id': user_id, 'product_id': product_id, 'amount': amount }) dao.commit()
def get_user_transaction_history(user_id): # Clean the input data user_id = str(user_id).strip() # Check for the existence of user if find_user(method='id', param=user_id) is None: raise ValidationError('Invalid user id.') # Establish db connection dao = DAO() cursor = dao.cursor() # Query database sql = """SELECT * FROM `order` WHERE user_id = %(user_id)s ORDER BY created_at DESC""" cursor.execute(sql, {'user_id': user_id}) result = cursor.fetchall() return result
def place_redeem(user_id, amount): # Clean the input data user_id = str(user_id).strip() amount = str(amount).strip() # Verify the amount if not is_money(amount): raise ValidationError('Invalid amount.') # Verify the user_id if find_user(method='id', param=user_id) is None: raise ValidationError('Invalid user id.') # Insert into order # Establish db connection dao = DAO() cursor = dao.cursor() sql = """INSERT INTO `order` ( user_id, total, actual_paid, status ) VALUES ( %(user_id)s, %(total)s, %(actual_paid)s, %(status)s )""" # A status code of 100 means it is a desposite cursor.execute(sql, { 'user_id': user_id, 'total': -amount, 'actual_paid': 0, 'status': 100 }) dao.commit()
def place_order(user_id, payment, coupon_code=''): """The function places an order of all the items in the user's cart Parameters: user_id -- the user's id payment -- the method the user chose to pay coupon_code -- the coupon code (if any) """ # Check the validity of the payment method if payment not in ['balance', 'credit_card', 'paypal']: raise ValidationError('Invalid payment method.') # Clean the input data user_id = str(user_id).strip() if coupon_code is None: coupon_code = '' coupon_code = str(coupon_code).strip() # If the user did input a coupon but the coupon is invalid coupon = None if len(coupon_code) > 0: coupon = find_coupon_and_check_validity(coupon_code) if coupon is None: raise ValidationError('Invalid coupon code.') # Check for the existence of user if find_user(method='id', param=user_id) is None: raise ValidationError('Invalid user id.') # Check the user's cart. If the item already exists, perform update instead of amount items = get_cart_items_by_user_id(user_id) if len(items) == 0: raise ValidationError('No item is found in cart.') # Calculate the total in the user's cart total = 0 for item in items: total += item['price'] * item['amount'] actual_paid = total # Calculate according to the given coupon if coupon is not None: if total >= coupon['threshold']: actual_paid -= coupon['value'] # Establish db connection dao = DAO() cursor = dao.cursor() # Proceed to the payment if payment == 'balance': user_pay(user_id, actual_paid, cursor) else: raise ValidationError( 'The payment method is not supported by the current merchant.') # Create new order sql = """INSERT INTO `order` ( actual_paid ) VALUES ( %(actual_paid)s )""" cursor.execute(sql, {'actual_paid': actual_paid}) # Retrive the newly inserted row cursor.execute('SELECT LAST_INSERT_ID()') order_id = cursor.fetchone()['LAST_INSERT_ID()'] # Insert into user_order sql = """INSERT INTO user_order ( user_id, order_id ) VALUES ( %(user_id)s, %(order_id)s )""" cursor.execute(sql, {'user_id': user_id, 'order_id': order_id}) for item in items: create_purchased_item(product_name=item['product_name'], product_price=item['price'], amount=item['amount'], order_id=order_id, cursor=cursor) delete_cart_item(cart_item_id=item['cart_item_id'], relay_cursor=cursor) # When all the procedures are successful and no exception was raised dao.commit()