[day23]加入购物车 & 库存检查

简单设计一个库存与订单设计，用白话一点来说就是推一台购物车，购物车上可以放上各种商品，推去结帐时这台购物车就被锁定了，使用者下次购物需要推另一台购物车

程序流程:

1. 产生购物车编号
2. 检查商品库存，库存>=0时允许绑定商品至购物车编号

实作购物车产生与库存检查

一样先做测试用的tester.yp，之後再把介面接到前端

修改doinit与doadd两个功能决定函式，加入init_add_test_items_to_shopping_cart_via_lineuid跟add_shopping_cart

# tester.py
def doinit(dbpm:DBPm, args):
    r = False
    if(args.target == 'product_category'):
        print("插入product_category测试资料")
        r = init_product_category(dbpm=dbpm, yes=args.yes)
    elif(args.target == 'products'):
        print("插入products测试资料")
        r = init_products(dbpm=dbpm, yes=args.yes)
    elif(args.target == 'cart_items' or args.target == 'shopping_cart'):
        print("插入购物车 & 插入购物车项目")
        r = init_add_test_items_to_shopping_cart_via_lineuid(dbpm=dbpm, yes=args.yes)
    if(r):print("成功")
    else:print("失败")

def doadd(dbpm:DBPm, args):
    r = False
    if(args.target == 'product_category'):
        print("手动插入product_category资料")
        r = add_product_category(dbpm=dbpm, yes=args.yes)
    elif(args.target == 'products'):
        print("手动插入products资料")
        r = add_products(dbpm=dbpm, yes=args.yes)
    elif(args.target == 'shopping_cart'):
        print("手动插入购物车shopping_cart")
        r = add_shopping_cart(dbpm=dbpm, yes=args.yes)
    if(r):print("成功")
    else:print("失败")

实作插入纪录

def add_shopping_cart(dbpm:DBPm, id=os.environ['Me'], yes=False):
    try:
        id = input(f"输入Line UID({id}):") or id
        if(not yes):yes = askyes()
        if(not yes):return False
        scid = dbpm.INS_QUY_SC(id)
        print(f"购物车ID:{scid}")
    except Exception as err:
        print(err)
        return False
    return True

def init_add_test_items_to_shopping_cart_via_lineuid(dbpm:DBPm, id=os.environ['Me'], yes=False):
    if(not yes):yes = askyes()
    if(not yes):return False

    # 购买第21、23、25号商品3、999、9个品项
    cart_item_pid = [21, 23, 25]
    cart_item_qut = [3, 999, 9]

    try:
        print(f"line id:{id}")
        scid = dbpm.INS_QUY_SC(id)
        for cp, cq in zip(cart_item_pid, cart_item_qut):
            current_product_stocks = dbpm.QUY_Prod_Quantity_by_pid(cp)
            print(f"产品{cp}, 库存:{current_product_stocks}")
            if(current_product_stocks - cq >= 0):
                print(f"INS, {cp} x {cq} to cart:{scid}")
                dbpm.INS_Prod_to_Cart(scid, cp, cq)
            else:
                print("库存不足")
    except Exception as err:
        print(err)
        return False
    return True

资料库控制:

# 返回购物车编号
def INS_QUY_SC(self, id):
    #先检查是否有存在的可用购物车

    cur = self.conn.cursor()
    query = sql.SQL("SELECT scid FROM {} WHERE uid = %s and lock = false LIMIT 1").format(sql.Identifier('shopping_cart'))
    cur.execute(query, (id,))
    scid = cur.fetchone()
    cur.close()
    print(f"scid-quy:{scid}")

    if(not scid):
        ct = datetime.now().isoformat()
        cur = self.conn.cursor()
        query = sql.SQL("INSERT INTO {}(uid, createddate) VALUES (%s, %s) RETURNING scid").format(sql.Identifier('shopping_cart'))
        cur.execute(query, (id, ct))
        scid = cur.fetchone()
        print(f"scid-ins:{scid}")
        self.conn.commit()
        cur.close()
    return scid[0]

# 返回库存
def QUY_Prod_Quantity_by_pid(self, pid):
    cur = self.conn.cursor()
    query = sql.SQL("select quantity from {} where pid = %s").format(sql.Identifier('products'))
    cur.execute(query, (pid,))
    qt = cur.fetchone()
    cur.close()
    if(qt):
        return qt[0]
    return None

# 将商品加入购物车
def INS_Prod_to_Cart(self, scid, pid, quantity):
    cur = self.conn.cursor()
    query = sql.SQL("SELECT quantity from {} where scid = %s and productid = %s").format(sql.Identifier('cart_items'))
    cur.execute(query, (scid, pid))
    qt = cur.fetchone()
    cur.close()
    if(qt):
        qt = qt[0] + quantity
        cur = self.conn.cursor()
        query = sql.SQL("UPDATE {} SET quantity=%s WHERE scid = %s and productid = %s").format(sql.Identifier('cart_items'))
        cur.execute(query, (qt, scid, pid))
        self.conn.commit()
        cur.close()
    else:
        cur = self.conn.cursor()
        query = sql.SQL("INSERT INTO {}(scid, productid, quantity) VALUES (%s, %s, %s)").format(sql.Identifier('cart_items'))
        cur.execute(query, (scid, pid, quantity))
        self.conn.commit()
        cur.close()

cursor.execute() 单参数时错误

>>> t = ('a')
>>> type(t)
<class 'str'>
>>> tt = ('a',)
>>> type(tt)
<class 'tuple'>

执行结果

heroku run python util/tester.py init shopping_cart

插入购物车 & 插入购物车项目
Confirm to Do(Y/N):Y
line id:uXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
scid-quy:(5,)
产品21, 库存:99
INS, 21 x 3 to cart:5
产品23, 库存:97
库存不足
产品25, 库存:95
INS, 25 x 9 to cart:5
成功

明天将接入订单系统，发起完整的付款流程