[day28] 更新购物车内品项

昨天有改了Products表格，先换一下

-- Table: public.products

-- DROP TABLE IF EXISTS public.products;

CREATE TABLE IF NOT EXISTS public.products
(
    pid integer NOT NULL DEFAULT nextval('products_pid_seq'::regclass),
    product_name text COLLATE pg_catalog."default" NOT NULL,
    quantity integer NOT NULL,
    product_decp text COLLATE pg_catalog."default",
    createddate timestamp with time zone,
    expireddate timestamp with time zone,
    price integer NOT NULL DEFAULT 0,
    categoryid integer NOT NULL,
    CONSTRAINT products_pkey PRIMARY KEY (pid),
    CONSTRAINT categoryid FOREIGN KEY (categoryid)
        REFERENCES public.product_category (pcid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
    CONSTRAINT "price must than zero" CHECK (price >= 0)
)

TABLESPACE pg_default;

购物车品项修改/字串处理

在这边系统会侦测Cart单字，进入对应的字串处理，购物车的控制指令格式 Cart {产品ID} +/-数量 || 直接设定数量，例如:

Cart 25 +1 --> 编号25的产品多一件
Cart 25 -1 --> 编号25的产品少一件
Cart 32 2 --> 编号32的产品两件

实作购物车品项维护

资料库处理dbPm.py


    # 新增/设定商品数量至购物车
    def INS_UPD_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):
            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, (quantity, 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()

    # 删除购物车中数量=0的品项
    def DEL_Shopping_Cart_items(self, scid):
        cur = self.conn.cursor()
        query = sql.SQL("DELETE FROM {} WHERE scid = %s and quantity = 0").format(sql.Identifier('cart_items'))
        cur.execute(query, (scid,))
        self.conn.commit()
        cur.close()

    # 查询购物车中产品的数量
    def QUY_Shopping_Cart_item_Quantity(self, productid, scid):
        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, productid))
        qt = cur.fetchone()
        if(not qt):return 0
        else:return qt[0]

Server.py，取出字串开头是'cart '的文字讯息

@handler.add(MessageEvent, message=TextMessage)
def handle_message(event:MessageEvent):
    prof = line_bot_api.get_profile(event.source.user_id)
    dt = datetime.fromtimestamp(event.timestamp / 1000.0).astimezone(TWT)
    format_time = dt.strftime("%Y/%m/%d %H:%M:%S")
    app.logger.debug(f"message:{event.message.type}-{event.message.id} = {event.message.text}, from {event.source.type}:{prof.display_name}({event.source.user_id}) at {format_time}")
    dbpm.INS_msg_log(event.message.id, event.message.type, event.message.text, dt.isoformat(), event.source.type, event.source.user_id)
    user_type_text = str(event.message.text).lower()
    if(user_type_text.startswith('cart ')):
        msg = Handler.Control_Shopping_Cart_ViaMessageText(event.source.user_id, event.message.text)
        line_bot_api.reply_message(
            event.reply_token,
            TextSendMessage(text=msg))
    else:
        line_bot_api.reply_message(
            event.reply_token,
            TextSendMessage(text="找不到对应的指令，请参考主选单-帮助"))

OrderHandler.py，进行字串处理，参数处理

def Control_Shopping_Cart_ViaMessageText(uid, user_type_text):
    split_text = user_type_text.split(' ')
    if(len(split_text) > 3):return "错误的购物车指令\ncart \{要加入或变更的产品ID\} \{该产品的数量\}\n输入数字不需要大括弧\{\}"
    if(not split_text[1].isnumeric()):return "请输入罗马数字的产品ID"

    scid = dbpm.INS_QUY_SC(uid)

    if((split_text[2][0] == '+' or split_text[2][0] == '-') and split_text[2][1:].isnumeric()):
        num = int(split_text[2])
        new_qt = dbpm.QUY_Shopping_Cart_item_Quantity(split_text[1], scid) + num
    elif(split_text[2].isnumeric()):
        new_qt = int(split_text[2])
    if(new_qt < 0):
        new_qt = 0
    app.logger.debug(f"{split_text[1]}, {new_qt}")
    dbpm.INS_UPD_Prod_to_Cart(scid, split_text[1], new_qt)
    p_name, p_price = dbpm.QUY_Prod_Name_and_Price_by_pid(split_text[1])
    dbpm.DEL_Shopping_Cart_items(scid)
    if(new_qt == 0):
        return f"已将{p_name}自购物车中删除"
    else:
        return f"已将{p_name}(单价:{p_price})的购买数量设定为{new_qt}"

结果: