DAY 21 新增查询与删除团购讯息

管理讯息的功能有

• 新增团购讯息
• 删除团购讯息
• 查询团购讯息
• 手动新增团购者
• 手动删除团购者

新增团购讯息

if(event.message.text[:3:]=="add"):
    lastLength=event.message.text.find(" ",4)
    if(not event.message.text[4:lastLength:] in keywords):
        handled_text=handle_emoji_text(event.message.text[lastLength+1::],event.message.emojis,lastLength+1)
        handled_emoji=handle_emoji(event.message.emojis,lastLength+1)
        cursor = conn.cursor()
        cursor.execute(f"INSERT INTO group_buying_message (message_text, keyword) VALUES ('{handled_text}', '{event.message.text[4:lastLength:]}');")
        for emoji in handled_emoji:      
            cursor.execute(f"INSERT INTO message_emoji (mid, index, product_id, emoji_id) VALUES ((SELECT mid FROM group_buying_message WHERE message_text='{handled_text}'), '{emoji.index}', '{emoji.product_id}', '{emoji.emoji_id}');")
        conn.commit()
        cursor.close()
        print("successfully insert emoji message")
        message=TextSendMessage(handled_text,handled_emoji)
    else:
        message=TextSendMessage(event.message.text[4:lastLength:]+"已存在，如要新增请先删除原有讯息")
    line_bot_api.reply_message(event.reply_token,message)

删除团购讯息

if(event.message.text[:3:]=="删除 "):
    key=event.message.text[3::]
    cursor = conn.cursor()
    try:
        #Delete row in message_emoji  
        cursor.execute(f"DELETE FROM message_emoji WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}');")
        #Delete row in group_buying_user
        cursor.execute(f"DELETE FROM group_buying_user WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}');")
        #Delete row in group_buying_message
        cursor.execute(f"DELETE FROM group_buying_message WHERE keyword='{key}';")
        conn.commit()
        cursor.close()
        message=TextSendMessage("删除"+key+"成功")
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
        message=TextSendMessage("删除"+key+"失败")       
    line_bot_api.reply_message(event.reply_token,message)

查询团购讯息

if(event.message.text[:3:]=="团购"):
    cursor = conn.cursor()
    #get keyword and group buying user data
    cursor.execute(f"SELECT keyword,name,quantity FROM group_buying_user NATURAL JOIN group_buying_message order by keyword;")
    users = cursor.fetchall()
    cursor.close()
    keyword=""
    message_text=""
    for user in users:
        if("".join(user[0])==keyword):
            message_text=message_text+"  "+"".join(user[1])+" "+"".join(user[2])+"\n"
        else:
            message_text=message_text+"".join(user[0])+":\n"+"  "+"".join(user[1])+" "+"".join(user[2])+"\n"
            keyword= "".join(user[0])           
    message=TextSendMessage(message_text)
    line_bot_api.reply_message(event.reply_token,message)

手动新增团购者

if(event.message.text[:event.message.text.find(" "):] in keywords and len(event.message.text)>event.message.text.find(" ")):
    text=event.message.text
    lastLength=text.find(" ")
    if(text[lastLength+1:lastLength+2:]=='@'):            
        key=text[:lastLength:]
        name=text[lastLength+2:text.find(" ",lastLength+1):]
        uid=""
        for user in event.message.mention.mentionees:
            uid=user.user_id
        quantity=text[text.find(" ",lastLength+1)+1:len(text):]
        cursor=conn.cursor()
        cursor.execute(f"DO $do$ BEGIN IF EXISTS(SELECT uid FROM group_buying_user WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}') AND uid='{uid}' ) THEN UPDATE group_buying_user SET name='{name}',quantity='{quantity}' WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}') AND uid='{uid}'; ELSE INSERT INTO group_buying_user (mid ,uid ,name, quantity) VALUES ((SELECT mid FROM group_buying_message WHERE keyword='{key}'),'{uid}','{name}','{quantity}'); END IF; END $do$")
        conn.commit()
        message=text
        message=TextSendMessage(message)
    else:
        key=text[:lastLength:]
        profile = line_bot_api.get_profile(event.source.user_id)
        cursor=conn.cursor()
        cursor.execute(f"DO $do$ BEGIN IF EXISTS(SELECT uid FROM group_buying_user WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}') AND uid='{profile.user_id}' ) THEN UPDATE group_buying_user SET name='{profile.display_name}',quantity='{text[lastLength+1::]}' WHERE mid=(SELECT mid FROM group_buying_message WHERE keyword='{key}' AND uid='{profile.user_id}'); ELSE INSERT INTO group_buying_user (mid, uid, name, quantity) VALUES ((SELECT mid FROM group_buying_message WHERE keyword='{key}'),'{profile.user_id}','{profile.display_name}','{text[lastLength+1::]}'); END IF; END $do$")
        conn.commit()
        message=key+" "+profile.display_name+" "+text[lastLength+1::]
        message=TextSendMessage(message)
    line_bot_api.reply_message(event.reply_token,message)

下篇会做手动删除团购者，跟权限管理
这几样功能需要有权限才能使用
添加团购讯息、删除团购讯息、手动新增团购者、手动删除团购者、查询团购讯息