def gathering(event):
DATABASE_URL = os.environ['DATABASE_URL']
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
postgres_select_query = f"""SELECT * FROM group_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
i = data.index(None)
print("i =", i)
column_all = [
'record_no', 'activity_type', 'activity_name', 'activity_date',
'activity_time', 'location_tittle', 'lat', 'long', 'people', 'cost',
'due_date', 'description', 'photo', 'your_name', 'your_phone',
'your_mail', 'attendent', 'condition', 'user_id'
]
#處理activity date and time
if event.postback.data == "Activity_time":
record = event.postback.params['datetime']
record = record.split("T")
postgres_update_query = f"""UPDATE group_data SET ({column_all[i]},{column_all[i+1]}) = ('{record[0]}','{record[1]}') WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
cursor.execute(postgres_select_query)
data = cursor.fetchone()
print("227 i = ", i)
if None in data:
msg = flexmsg.flex(i, data)
line_bot_api.reply_message(event.reply_token, msg)
elif None not in data:
postgres_select_query = f"""SELECT * FROM group_data ORDER BY record_no DESC;"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
msg = flexmsg.summary(data)
line_bot_api.reply_message(event.reply_token, msg)
cursor.close()
conn.close()
#處理due date
elif event.postback.data == "Due_time":
record = event.postback.params['date']
postgres_update_query = f"""UPDATE group_data SET {column_all[i]} = '{record}' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
cursor.execute(postgres_select_query)
data = cursor.fetchone()
if None in data:
msg = flexmsg.flex(i, data)
line_bot_api.reply_message(event.reply_token, msg)
elif None not in data:
postgres_select_query = f"""SELECT * FROM group_data ORDER BY record_no DESC;"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
msg = flexmsg.summary(data)
line_bot_api.reply_message(event.reply_token, msg)
cursor.close()
conn.close()
def gathering(event):
progress_list_fullgroupdata = [7, 1, 2, 3, 4, 5, 6, 7]
progress_list_halfgroupdata = [5, 1, 2, 3, 4, 5]
DATABASE_URL = os.environ['DATABASE_URL']
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
postgres_select_query = f"""SELECT * FROM group_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
i = data.index(None)
print("i =", i)
column_all = [
'acrivity_no', 'activity_type', 'activity_name', 'activity_date',
'activity_time', 'location_tittle', 'lat', 'long', 'people', 'cost',
'due_date', 'description', 'photo', 'name', 'phone', 'mail',
'attendee', 'condition', 'user_id'
]
tittle = event.message.title
if event.message.title:
tittle = event.message.title.replace("'", "‘")
record = [tittle, event.message.latitude, event.message.longitude]
if record[0] == None:
record[0] = event.message.address[:50]
postgres_update_query = f"""UPDATE group_data SET ({column_all[i]}, {column_all[i+1]}, {column_all[i+2]}) = ('{record[0]}', '{record[1]}', '{record[2]}') WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
cursor.execute(postgres_select_query)
data = cursor.fetchone()
try:
if len(data[14]) > 0:
progress_target = progress_list_halfgroupdata
else:
progress_target = progress_list_fullgroupdata
except:
progress_target = progress_list_fullgroupdata
if None in data:
msg = flexmsg.flex(i, data, progress_target)
line_bot_api.reply_message(event.reply_token, msg)
elif None not in data:
postgres_select_query = f"""SELECT * FROM group_data WHERE user_id = '{event.source.user_id}' ORDER BY activity_no DESC;"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
msg = flexmsg.summary(data)
line_bot_api.reply_message(event.reply_token, msg)
cursor.close()
conn.close()
def gathering(event):
DATABASE_URL = os.environ['DATABASE_URL']
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
postgres_select_query = f"""SELECT * FROM group_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
i = data.index(None)
print("i =",i)
column_all = ['acrivity_no', 'activity_type', 'activity_name',
'activity_date', 'activity_time', 'location_tittle', 'lat', 'long', 'people', 'cost',
'due_date', 'description', 'photo', 'name',
'phone', 'mail', 'attendee', 'condition', 'user_id']
record =[ event.message.title, event.message.latitude, event.message.longitude]
postgres_update_query = f"""UPDATE group_data SET ({column_all[i]}, {column_all[i+1]}, {column_all[i+2]}) = ('{record[0]}', '{record[1]}', '{record[2]}') WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
cursor.execute(postgres_select_query)
data = cursor.fetchone()
if None in data:
msg=flexmsg.flex(i,data)
line_bot_api.reply_message(
event.reply_token,
msg)
elif None not in data:
postgres_select_query = f"""SELECT * FROM group_data WHERE user_id = '{event.source.user_id}' ORDER BY record_no DESC;"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
msg=flexmsg.summary(data)
line_bot_api.reply_message(
event.reply_token,
msg
)
cursor.close()
conn.close()
def echo(event):
if event.source.user_id != "Udeadbeefdeadbeefdeadbeefdeadbeef":
#連結到heroku資料庫
DATABASE_URL = os.environ['DATABASE_URL']
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
if event.message.text == "~open":
line_bot_api.reply_message(event.reply_token,
flexmsg.activity_type)
print("prepare to open the group")
#把只創建卻沒有寫入資料的列刪除
postgres_delete_query = f"""DELETE FROM group_data WHERE (condition) = ('initial');"""
cursor.execute(postgres_delete_query)
conn.commit()
#創建一列(condition = initial)
postgres_insert_query = f"""INSERT INTO group_data (condition, user_id, attendent) VALUES ('initial', '{event.source.user_id}', '1');"""
cursor.execute(postgres_insert_query)
conn.commit()
cursor.close()
conn.close()
#中途想結束輸入~delete, 把initial那列刪除
elif event.message.text == "取消":
line_bot_api.reply_message(event.reply_token,
TextSendMessage(text='取消成功'))
postgres_delete_query = f"""DELETE FROM group_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
#如果有創建了一列, 則接下來的資料繼續寫入
else:
postgres_select_query = f"""SELECT * FROM group_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
print('data = ', data)
column_all = [
'record_no', 'activity_type', 'activity_name', 'activity_date',
'activity_time', 'location_tittle', 'lat', 'long', 'people',
'cost', 'due_date', 'description', 'photo', 'your_name',
'your_phone', 'your_mail', 'attendent', 'condition', 'user_id'
]
if data:
if None in data:
i = data.index(None)
print("i= ", i)
record = event.message.text
#如果使用者輸入的資料不符合資料庫的資料型態, 則輸入N/A
if event.message.type == 'text':
postgres_update_query = f"""UPDATE group_data SET {column_all[i]} = '{record}' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
else:
postgres_update_query = f"""UPDATE group_data SET {column_all[i]} = 'N/A' WHERE condition = 'initial'AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
#如果還沒輸入到最後一格, 則繼續詢問下一題
postgres_select_query = f"""SELECT * FROM group_data WHERE condition = 'initial'AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
if None in data:
msg = flexmsg.flex(i, data)
line_bot_api.reply_message(event.reply_token, msg)
#如果已經到最後一格, condition改為finish, 回覆summary，
elif None not in data:
postgres_select_query = f"""SELECT * FROM group_data ORDER BY record_no DESC;"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
msg = flexmsg.summary(data)
line_bot_api.reply_message(event.reply_token, msg)
else:
if event.message.text == '確認開團':
postgres_update_query = f"""UPDATE group_data SET condition = 'pending' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text="finish!!"))
cursor.close()
conn.close()
else:
column = event.message.text
# 處理location 因為location 跟資料庫的名字不一樣
if column == "location":
postgres_update_query = f"""UPDATE group_data SET location_tittle = Null WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
msg = flexmsg.flex(column, data)
line_bot_api.reply_message(event.reply_token, msg)
elif column in column_all:
postgres_update_query = f"""UPDATE group_data SET {column} = Null WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
msg = flexmsg.flex(column, data)
line_bot_api.reply_message(event.reply_token, msg)
else:
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(
text=
'please enter the column you want to edit')
)
else:
if event.message.text.encode == "double":
line_bot_api.reply_message(event.reply_token,
flexmsg.summary(data))
elif event.message.text == "早安":
img = 'https://pic.pimg.tw/ellenlee0409/1566550498-3560465550.jpg'
line_bot_api.reply_message(
event.reply_token,
ImageSendMessage(original_content_url=img,
preview_image_url=img))
def echo(event):
# [0] 總題數 [1:]目前題數
progress_list_drug_refill = [5, 1, 2, 3, 4, 5]
progress_list_drug_documentation = [7, 1, 2, 3, 4, 5, 6, 7]
event.message.text = event.message.text.replace("'", "‘")
# progress_target
if event.source.user_id != "Udeadbeefdeadbeefdeadbeefdeadbeef":
# 連結到heroku資料庫
DATABASE_URL = os.environ['DATABASE_URL']
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
if event.message.text == "領藥時間查詢":
line_bot_api.reply_message(event.reply_token,
flexmsg.drug_refill_calculation)
print("prepare to calculate refill date")
# 把只創建卻沒有寫入資料的列刪除
postgres_delete_query = f"""DELETE FROM refill_cal_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
# 創建一列(condition = initial)
postgres_insert_query = f"""INSERT INTO refill_cal_data (condition, user_id, today_date) VALUES ('initial', '{event.source.user_id}', 'str({event.source.dt}.datetime.today())');"""
cursor.execute(postgres_insert_query)
conn.commit()
cursor.close()
conn.close()
elif event.message.text == "新增藥品記錄":
line_bot_api.reply_message(event.reply_token,
flexmsg.drug_category)
print("prepare to document the drugs")
# 把只創建卻沒有寫入資料的列刪除
postgres_delete_query = f"""DELETE FROM drug_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
# 創建一列(condition = initial)
postgres_insert_query = f"""INSERT INTO drug_data (condition, user_id, generic_name, description, photo) VALUES ('initial', '{event.source.user_id}', '無', '無', '無');"""
cursor.execute(postgres_insert_query)
conn.commit()
cursor.close()
conn.close()
# 中途想結束輸入~delete, 把initial那列刪除
elif event.message.text == "取消":
postgres_select_query = f'''SELECT * FROM refill_cal_data WHERE user_id = '{event.source.user_id}' AND condition = 'initial';'''
cursor.execute(postgres_select_query)
data = cursor.fetchone()
postgres_delete_query = f"""DELETE FROM refill_cal_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
postgres_select_query = f'''SELECT * FROM drug_data WHERE user_id = '{event.source.user_id}' AND condition = 'initial';'''
cursor.execute(postgres_select_query)
data_2 = cursor.fetchone()
postgres_delete_query = f"""DELETE FROM drug_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
if data or data_2:
line_bot_api.reply_message(event.reply_token,
TextSendMessage(text='取消成功'))
else:
line_bot_api.reply_message(event.reply_token,
TextSendMessage(text='無可取消的紀錄'))
# 如果有創建了一列, 則接下來的資料繼續寫入
else:
postgres_select_query = f"""SELECT * FROM drug_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
# 準備寫入藥品記錄資料的那一列
data_2 = cursor.fetchone()
print("data_2 :", data_2)
column_all_drug = [
'document_no', 'drug_category', 'drug_name', 'generic_name',
'drug_prescription_date', 'duration', 'drug_amount',
'frequency', 'due_date'
'description', 'photo', 'user_id'
]
# drug_category = ['處方藥', '非處方藥品', '中草藥', '保健食品']
postgres_select_query = f"""SELECT * FROM refill_cal_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
print('data = ', data)
column_all_refill = [
'record_no', 'today_date', 'visit_date', 'prescription_days',
'total_refill_number', 'last_refill_number',
'last_refill_date', 'second_refill_start_dt',
'second_refill_end_dt', 'third_refill_start_dt',
'third_refill_end_dt', 'valid_dt', 'user_id'
]
if data:
try:
progress_target = progress_list_drug_refill
except:
progress_target = progress_list_drug_refill
while None in data: # 如果還沒輸入到最後一格, 則繼續詢問下一題
i = data.index(None)
print("i = ", i)
record = event.message.text
try:
postgres_select_query = f"""SELECT * FROM refill_cal_data WHERE condition = 'initial'AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
postgres_update_query = f"""UPDATE refill_cal_data SET {column_all_refill[i]} = '{record}' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
except: # 如果使用者輸入的資料不符合資料庫的資料型態, 則輸入N/A
line_bot_api.reply_message(
event.reply_token, TextSendMessage(text="請重新輸入"))
if None in data:
msg = flexmsg.flex_prescription(
i, data, progress_target)
line_bot_api.reply_message(event.reply_token, msg)
elif None not in data:
postgres_select_query = f"""SELECT * FROM refill_cal_data WHERE user_id = '{event.source.user_id}' ORDER BY activity_no DESC;"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
msg = flexmsg.summary(data)
line_bot_api.reply_message(event.reply_token, msg)
# 如果已經到最後一格, condition改為finish, 回覆summary
# elif None not in data:
postgres_select_query = f"""SELECT * FROM refill_cal_data WHERE user_id = '{event.source.user_id}' ORDER BY activity_no DESC;"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
msg = flexmsg.summary(data)
line_bot_api.reply_message(event.reply_token, msg)
if event.message.text == '確認填寫無誤':
postgres_update_query = f"""UPDATE refill_cal_data SET condition = 'pending' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
line_bot_api.reply_message(event.reply_token,
TextSendMessage(text="請等待計算結果"))
cursor.close()
conn.close()
else:
column = event.message.text
if column in column_all_refill:
postgres_update_query = f"""UPDATE refill_cal_data SET {column} = Null WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
progress_target = [5, 4, 4, 4, 4, 4]
msg = flexmsg.flex_presciption(i, progress)
line_bot_api.reply_message(event.reply_token, msg)
else:
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text='請輸入您想修改的欄位'))
elif data_2:
try:
if len(data_2[6]) > 0:
drug_progress_target = progress_list_drug_documentation
except:
drug_progress_target = progress_list_drug_documentation
if None in data_2:
j = data_2.index(None)
print("j = ", j)
if j == 8:
postgres_update_query = f"""UPDATE drug_data SET {column_all_drug[j]} = '無' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
postgres_select_query = f"""SELECT * FROM drug_data WHERE user_id = '{event.source.user_id}' ORDER BY docu_no DESC;"""
cursor.execute(postgres_select_query)
data_2 = cursor.fetchone()
drug_info = flexmsg.drug_info_summary(data_2)
line_bot_api.reply_message(
event.reply_token,
[TextSendMessage(text="上傳失敗。"), drug_info])
else:
record = event.message.text
# 如果使用者輸入的資料不符合資料庫的資料型態, 則輸入N/A
try:
postgres_update_query = f"""UPDATE drug_data SET {column_all_drug[j]} = '{record}' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
except:
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text="請重新輸入"))
# 如果還沒輸入到最後一格, 則繼續詢問下一題
postgres_select_query = f"""SELECT * FROM drug_data WHERE condition = 'initial'AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data_2 = cursor.fetchone()
if None in data_2:
drug_info = flexmsg.flex_drug(
j, data_2, drug_progress_target)
line_bot_api.reply_message(event.reply_token,
drug_info)
# 如果已經到最後一格, condition改為finish, 回覆summary，
elif None not in data_2:
postgres_select_query = f"""SELECT * FROM group_data WHERE user_id = '{event.source.user_id}' ORDER BY activity_no DESC;"""
cursor.execute(postgres_select_query)
data_2 = cursor.fetchone()
drug_info = flexmsg.drug_info_summary(data_2)
line_bot_api.reply_message(event.reply_token,
drug_info)
else:
if event.message.text == '確認紀錄':
postgres_update_query = f"""UPDATE drug_data SET condition = 'pending' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
line_bot_api.reply_message(
event.reply_token, TextSendMessage(text="完成紀錄"))
cursor.close()
conn.close()
else:
column_all_drug = event.message.text
if column in column_all_drug:
postgres_update_query = f"""UPDATE drug_data SET {column} = Null WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
drug_progress_target = [7, 6, 6, 6, 6, 6, 6, 6]
drug_info = flexmsg.flex_drug(
column, data_2, drug_progress_target)
line_bot_api.reply_message(event.reply_token,
drug_info)
else:
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text='請輸入您想修改的欄位'))
def pic(event):
DATABASE_URL = os.environ['DATABASE_URL']
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
postgres_select_query = f"""SELECT * FROM drug_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data_2 = cursor.fetchone()
if data_2:
j = data_2.index(None)
print("i =", j)
if j == 12:
column_all_drug = [
'document_no', 'drug_category', 'drug_name', 'generic_name',
'drug_prescription_date', 'duration', 'drug_amount',
'frequency', 'due_date', 'description', 'photo', 'user_id'
]
# 把圖片存下來並傳上去
ext = 'jpg'
message_content = line_bot_api.get_message_content(
event.message.id)
with tempfile.NamedTemporaryFile(dir=static_tmp_path,
prefix=ext + '-',
delete=False) as tf:
for chunk in message_content.iter_content():
tf.write(chunk)
tempfile_path = tf.name
dist_path = tempfile_path + '.' + ext
dist_name = os.path.basename(dist_path)
os.rename(tempfile_path, dist_path)
try:
config = configparser.ConfigParser()
config.read('config.ini')
client = ImgurClient(config.get('imgur', 'client_id'),
config.get('imgur', 'client_secret'),
config.get('imgur', 'access_token'),
config.get('imgur', 'refresh_token'))
con = {
'album': config.get('imgur', 'album_id'),
'name': f'{event.source.user_id}_{data_2[2]}',
'title': f'{event.source.user_id}_{data_2[2]}',
'description': f'{event.source.user_id}_{data_2[2]}'
}
path = os.path.join('static', 'tmp', dist_name)
image = client.upload_from_path(path, config=con, anon=False)
print("path = ", path)
os.remove(path)
print("image = ", image)
# 把圖片網址存進資料庫
postgres_update_query = f"""UPDATE group_data SET {column_all_drug[j]} = '{image['link']}' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
msg = [TextSendMessage(text='上傳成功')]
postgres_select_query = f"""SELECT * FROM group_data WHERE user_id = '{event.source.user_id}' ORDER BY activity_no DESC;"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
if None not in data:
msg.append(flexmsg.summary(data))
line_bot_api.reply_message(event.reply_token, msg)
except:
line_bot_api.reply_message(event.reply_token,
TextSendMessage(text='上傳失敗'))
else:
line_bot_api.reply_message(event.reply_token,
TextSendMessage(text="現在不用傳圖片給我"))
return 0
def documentation(event):
progress_list_drug = [5, 1, 2, 3, 4, 5]
# DATABASE_URL = os.environ['DATABASE_URL']
# conn = psycopg2.connect(DATABASE_URL, sslmode='require')
# cursor = conn.cursor()
DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a drugtaking-linebot').read()[:-1]
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
postgres_select_query = f"""SELECT * FROM drug_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data_2 = cursor.fetchone()
postback_data_drug = event.postback.data_2
progress_list_drug = [5, 1, 2, 3, 4, 5]
postgres_select_query = f"""SELECT * FROM refill_cal_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
postback_data_refill = event.postback.data
# 查看藥品紀錄 (藥品名稱、地點、時間、費用、已報名人數)
if "藥品紀錄" in postback_data_drug:
# 把只創建卻沒有寫入資料完成的列刪除
postgres_delete_query = f"""DELETE FROM drug_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
postgres_select_query = f"""SELECT * FROM drug_data WHERE user_id = '{event.source.user_id}' AND due_date >= '{dt.date.today()}' ORDER BY prescription_date ASC;"""
cursor.execute(postgres_select_query)
drug_data = cursor.fetchall()
print("drug_data = ", drug_data)
drug_info = flexmsg.DrugList(drug_data)
line_bot_api.reply_message(
event.reply_token,
drug_info
)
elif "慢箋查詢紀錄" in postback_data_refill:
# 把只創建卻沒有寫入資料完成的列刪除
postgres_delete_query = f"""DELETE FROM refill_cal_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
postgres_select_query = f"""SELECT * FROM refill_cal_data WHERE user_id = '{event.source.user_id}' AND due_date >= '{dt.date.today()}' ORDER BY prescription_date ASC;"""
cursor.execute(postgres_select_query)
refill_cal_data = cursor.fetchall()
print("refill_cal_data = ", refill_cal_data)
msg = flexmsg.DrugList(refill_cal_data)
line_bot_api.reply_message(
event.reply_token,
msg
)
# elif "詳細資訊" in postback_data:
# record = postback_data.split("_")
# DATABASE_URL = os.environ['DATABASE_URL']
# conn = psycopg2.connect(DATABASE_URL, sslmode='require')
# cursor = conn.cursor()
# postgres_select_query = f"""SELECT * FROM group_data WHERE activity_no = '{record[0]}' ;"""
# cursor.execute(postgres_select_query)
# data_tmp = cursor.fetchone()
# msg = flexmsg.MoreInfoSummary(data_tmp)
#
# line_bot_api.reply_message(
# event.reply_token,
# msg
# )
#
# # ~~點了carousel的"了解更多"，跳出該團的summary
# elif "forward" in postback_data_drug or "backward" in postback_data_drug:
#
# record = postback_data_drug.split("_") # record[0] = forward, record[1] = command
#
# if record[1] == "activity":
#
# # record[2] = activity_type, record[3] = i
# j = int(record[3])
#
# postgres_select_query = f"""SELECT * FROM group_data WHERE activity_date >= '{dt.date.today()}' AND due_date >= '{dt.date.today()}' AND activity_type = '{record[2]}' and people > attendee and condition = 'pending' ORDER BY activity_date ASC;"""
# cursor.execute(postgres_select_query)
# data = cursor.fetchall()
#
# drug_info = flexmsg.flex_drug(data_2, j)
# line_bot_api.reply_message(
# event.reply_token,
# drug_info
# )
#
# elif record[1] == "group":
#
# # record[2] = j
# j = int(record[2])
# # 用user_id尋找該主揪所有的開團資料
# postgres_select_query = f"""SELECT * FROM drug_data WHERE user_id = '{event.source.user_id}' AND activity_date >= '{dt.date.today()}' ORDER BY activity_date ASC;"""
# cursor.execute(postgres_select_query)
# drug_data = cursor.fetchall()
#
# print("group_data = ", drug_data)
#
# # 回傳開團列表
# drug_info = flexmsg.DrugList(drug_data, j)
# line_bot_api.reply_message(
# event.reply_token,
# drug_info
# )
#
# elif record[1] == "registration":
#
# # record[2] = i
# i = int(record[2])
#
# # 用user_id從database找出有報的團
# postgres_select_query = f"""SELECT * FROM registration_data WHERE user_id = '{event.source.user_id}' AND activity_date >= '{dt.date.today()}' ORDER BY activity_date ASC;"""
# cursor.execute(postgres_select_query)
#
# # 避免look_up_data_registration裡的activity_name重複
# look_up_data_registration = []
# act_no = []
# alldata = cursor.fetchall()
# if alldata:
# for act in alldata:
# if act[1] not in act_no: # act[1]為activity_no, act[2]為activity_name
# act_no.append(act[1])
# look_up_data_registration.append(act)
#
# msg = flexmsg.registration_list(look_up_data_registration, i)
# line_bot_api.reply_message(
# event.reply_token,
# msg
# )
# 上一頁下一頁要寫在這個上面
else:
if data:
i = data.index(None)
print("i = ", i)
progress_list_refill = [5, 1, 2, 3, 4, 5]
column_all_refill = ['record_no', 'visit_date', 'prescription_days', 'total_refill_number',
'last_refill_number', 'last_refill_date', 'today_date', 'second_refill_start_dt',
'second_refill_end_dt', 'third_refill_start_dt',
'third_refill_end_dt', 'valid_dt', 'user_id']
# 處理 visit_date, last refill date
if event.postback.data == "visit_date":
record = event.postback.params['date']
postgres_update_query = f"""UPDATE refill_cal_data SET {column_all_refill[i]} = '{record}' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
elif event.postback.data == "last_refill_date":
record = event.postback.params['date']
postgres_update_query = f"""UPDATE refill_cal_data SET {column_all_refill[i]} = '{record}' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
cursor.execute(postgres_select_query)
data = cursor.fetchone()
if None in data:
msg = flexmsg.flex_prescription(i, progress_list_refill)
line_bot_api.reply_message(
event.reply_token,
msg)
elif None not in data:
postgres_select_query = f"""SELECT * FROM refill_cal_data WHERE user_id = '{event.source.user_id}' ORDER BY documentation_no DESC;"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
msg = flexmsg.summary(data)
line_bot_api.reply_message(
event.reply_token,
msg
)
cursor.close()
conn.close()
elif data_2:
j = data_2.index(None)
print("j = ", j)
column_all_drug = ['document_no', 'drug_category', 'drug_name', 'drug_amount', 'drug_frequency',
'prescription_date', 'duration', 'due_date', 'generic_name', 'description', 'photo', 'condition',
'user_id']
# 處理 prescription date
if event.postback.data_2 == "prescription_date":
record = event.postback.params['date']
postgres_update_query = f"""UPDATE drug_data SET {column_all[j]} = '{record}' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
cursor.execute(postgres_select_query)
data_2 = cursor.fetchone()
if None in data_2:
drug_info = flexmsg.flex_drug(j, progress_list_drug)
line_bot_api.reply_message(
event.reply_token,
drug_info)
elif None not in data_2:
postgres_select_query = f"""SELECT * FROM drug_data WHERE user_id = '{event.source.user_id}' ORDER BY documentation_no DESC;"""
cursor.execute(postgres_select_query)
data_2 = cursor.fetchone()
drug_info = flexmsg.drug_info_summary(data_2)
line_bot_api.reply_message(
event.reply_token,
drug_info
)
cursor.close()
conn.close()
def drug_refill_event(event_2):
if event_2.source.user_id != "Udeadbeefdeadbeefdeadbeefdeadbeef":
DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a drugtaking-linebot').read()[:-1]
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
if event_2.message.text == "領藥時間查詢":
line_bot_api.reply_message(
event_2.reply_token,
flexmsg.drug_refill_calculation)
print("prepare to calculate refill date")
# 把只創建卻沒有寫入資料的列刪除
postgres_delete_query = f"""DELETE FROM refill_cal_data WHERE (condition, user_id) = ('initial', '{event_2.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
# 創建一列(condition = initial)
postgres_insert_query = f"""INSERT INTO refill_cal_data (condition, user_id, second_refill_start_dt, second_refill_end_dt, third_refill_start_dt, third_refill_end_dt) VALUES ('initial', '{event_2.source.user_id}', '無', '無', '無', '無');"""
cursor.execute(postgres_insert_query)
conn.commit()
cursor.close()
conn.close()
# 中途想結束輸入~delete, 把initial那列刪除
elif event_2.message.text == "取消":
postgres_select_query = f'''SELECT * FROM refill_cal_data WHERE user_id = '{event_2.source.user_id}' AND condition = 'initial';'''
cursor.execute(postgres_select_query)
data = cursor.fetchone()
postgres_delete_query = f"""DELETE FROM refill_cal_data WHERE (condition, user_id) = ('initial', '{event_2.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
if data:
line_bot_api.reply_message(
event_2.reply_token,
TextSendMessage(text='取消成功')
)
else:
line_bot_api.reply_message(
event_2.reply_token,
TextSendMessage(text='無可取消的紀錄')
)
else:
DATABASE_URL = os.popen('heroku config:get DATABASE_URL -a drugtaking-linebot').read()[:-1]
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
#領藥時間查詢
postgres_select_query = f"""SELECT * FROM refill_cal_data WHERE condition = 'initial' AND user_id = '{event_2.source.user_id}';"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
print("data :", data)
column_all_refill = ['document_no', 'prescription_category','visit_date', 'prescription_days', 'total_refill_number',
'last_refill_number', 'last_refill_date', 'today_date', 'second_refill_start_dt',
'second_refill_end_dt', 'third_refill_start_dt',
'third_refill_end_dt', 'valid_dt', 'user_id']
if data:
# [0] 總題數 [1:]目前題數
progress_list_refill = [5, 1, 2, 3, 4, 5]
if None in data:
i = data.index(None)
print("i = ", i)
if event_2.message.text == '一般處方箋':
line_bot_api.reply_message(
event_2.reply_token,
TextSendMessage(text="請在看診3日內領取")
)
postgres_delete_query = f"""DELETE FROM refill_cal_data WHERE (condition, user_id) = ('initial', '{event_2.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
else:
record = event_2.message.text
try:
postgres_update_query = f"""UPDATE refill_cal_data SET {column_all_refill[i]} = '{record}' WHERE condition = 'initial' AND user_id = '{event_2.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
print("data :", data)
except:
line_bot_api.reply_message(
event_2.reply_token,
TextSendMessage(text="請重新輸入")
)
# 如果還沒輸入到最後一格, 則繼續詢問下一題
postgres_select_query = f"""SELECT * FROM refill_cal_data WHERE condition = 'initial'AND user_id = '{event_2.source.user_id}';"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
if None in data:
progress_list_refill = [5, 1, 2, 3, 4, 5]
msg = flexmsg.flex_prescription(i, progress_list_refill)
line_bot_api.reply_message(
event_2.reply_token,
msg)
print("data :", data)
# 如果已經到最後一格, condition改為finish, 回覆summary，
elif None not in data:
postgres_select_query = f"""SELECT * FROM refill_cal_data WHERE user_id = '{event_2.source.user_id}' ;"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
msg = flexmsg.summary(data)
line_bot_api.reply_message(
event_2.reply_token,
msg
)
else:
if event_2.message.text == '確認填寫無誤':
postgres_select_query = f"""SELECT * FROM refill_cal_data WHERE user_id = '{event_2.source.user_id}' ;"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
# valid_date = drug_refill_cal(data)
valid_date = {data[2]}+ datetime.timedelta(days= ({data[3]} * {data[4]}))
postgres_update_query = f"""UPDATE refill_cal_data SET valid_dt = {valid_date} , condition = 'calculating' WHERE condition = 'initial' AND user_id = '{event_2.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
line_bot_api.reply_message(
event_2.reply_token,
TextSendMessage(text=drug_refill_cal(data))
)
cursor.close()
conn.close()
else:
column = event_2.message.text
if column in column_all_refill:
postgres_update_query = f"""UPDATE refill_cal_data SET {column} = Null WHERE condition = 'initial' AND user_id = '{event_2.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
progress_list_refill_all = [5, 4, 4, 4, 4, 4]
msg = flexmsg.flex_prescription(column, progress_list_refill_all)
line_bot_api.reply_message(
event_2.reply_token,
msg
)
else:
line_bot_api.reply_message(
event_2.reply_token,
TextSendMessage(text='請輸入您想修改的欄位')
)
def gathering(event):
DATABASE_URL = os.environ['DATABASE_URL']
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
column_all = ['acrivity_no', 'activity_type', 'activity_name',
'activity_date', 'activity_time', 'location_tittle', 'lat', 'long', 'people', 'cost',
'due_date', 'description', 'photo', 'name',
'phone', 'mail', 'attendee', 'condition', 'user_id']
#主揪查看自己開的團的資訊 (活動名稱、地點、時間、費用、已報名人數)
if "開團資訊" in postback_data:
activity_no = postback_data.replace("開團資訊", "")
postgres_select_query = f"""SELECT * FROM group_data WHERE activity_no = '{activity_no}';"""
cursor.execute(postgres_select_query)
group_data = cursor.fetchone()
print("group_data = ", group_data)
msg = flexmsg.MyGroupInfo(group_data)
line_bot_api.reply_message(
event.reply_token,
msg
)
#主揪查看報名者資訊(報名者暱稱、電話)
elif "報名者資訊" in postback_data:
activity_no = postback_data.replace("報名者資訊", "")
postgres_select_query = f"""SELECT activity_name FROM registration_data WHERE activity_no = '{activity_no}';"""
cursor.execute(postgres_select_query)
activity_name = "".join(cursor.fetchone())
print("activity_name = ", activity_name)
postgres_select_query = f"""SELECT attendee_name, phone FROM registration_data WHERE activity_no = '{activity_no}';"""
cursor.execute(postgres_select_query)
attendee_data = cursor.fetchall()
print("attendee_data = ", attendee_data)
attendee_lst = []
for row in attendee_data:
attendee_lst.append(" ".join(row))
msg = f"{activity_name}"+"\n報名者資訊："
for attendee in attendee_lst:
msg += f"\n{attendee}"
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text = msg)
)
#主揪提早關團
elif "結束報名" in postback_data:
activity_no = postback_data.replace("結束報名", "")
postgres_update_query = f"""UPDATE group_data SET condition = 'closed' WHERE activity_no = '{activity_no}';"""
cursor.execute(postgres_update_query)
conn.commit()
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text = "成功結束報名！")
)
else:
postgres_select_query = f"""SELECT * FROM group_data WHERE condition = 'initial' AND user_id = '{user}';"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
i = data.index(None)
print("i = ",i)
#處理activity date and time
if event.postback.data == "Activity_time" :
record = event.postback.params['datetime']
record = record.split("T")
due = dt.date.fromisoformat(record[0]) - dt.timedelta(days=1)
postgres_update_query = f"""UPDATE group_data SET ({column_all[i]}, {column_all[i+1]}, {column_all[i+7]}) = ('{record[0]}','{record[1]}', '{due}') WHERE condition = 'initial' AND user_id = '{user}';"""
cursor.execute(postgres_update_query)
conn.commit()
#處理due date
elif event.postback.data == "Due_time":
record = event.postback.params['date']
postgres_update_query = f"""UPDATE group_data SET {column_all[i]} = '{record}' WHERE condition = 'initial' AND user_id = '{user}';"""
cursor.execute(postgres_update_query)
conn.commit()
cursor.execute(postgres_select_query)
data = cursor.fetchone()
if None in data:
msg = flexmsg.flex(i, data)
line_bot_api.reply_message(
event.reply_token,
msg
)
elif None not in data:
postgres_select_query = f"""SELECT * FROM group_data WHERE user_id = '{user}' ORDER BY activity_no DESC;"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
msg = flexmsg.summary(data)
line_bot_api.reply_message(
event.reply_token,
msg
)
cursor.close()
conn.close()
def echo(event):
if event.source.user_id != "Udeadbeefdeadbeefdeadbeefdeadbeef":
#連結到heroku資料庫
DATABASE_URL = os.environ['DATABASE_URL']
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
if event.message.text == "~open":
line_bot_api.reply_message(
event.reply_token,
flexmsg.activity_type)
print("prepare to open the group")
#把只創建卻沒有寫入資料的列刪除
postgres_delete_query = f"""DELETE FROM group_data WHERE (condition) = ('initial');"""
cursor.execute(postgres_delete_query)
conn.commit()
#創建一列(condition = initial)
postgres_insert_query = f"""INSERT INTO group_data (condition, user_id, attendee, photo, description) VALUES ('initial', '{event.source.user_id}', '1', '無', '無');"""
cursor.execute(postgres_insert_query)
conn.commit()
#撈主揪的資料
postgres_select_query=f'''SELECT name,phone FROM group_data WHERE user_id = '{event.source.user_id}' AND condition!= 'initial' ORDER BY activity_no DESC;'''
cursor.execute(postgres_select_query)
data_for_basicinfo = cursor.fetchone()
if data_for_basicinfo:
postgres_insert_query = f"""UPDATE group_data SET name='{data_for_basicinfo[0]}' , phone='{data_for_basicinfo[1]}' WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_insert_query)
conn.commit()
cursor.close()
conn.close()
#中途想結束輸入~delete, 把initial那列刪除
elif event.message.text == "取消" :
postgres_select_query=f'''SELECT * FROM group_data WHERE user_id = '{event.source.user_id}' AND condition= 'initial';'''
cursor.execute(postgres_select_query)
data = cursor.fetchone()
postgres_select_query=f'''SELECT * FROM group_data WHERE user_id = '{event.source.user_id}' AND condition= 'initial';'''
cursor.execute(postgres_select_query)
data_2 = cursor.fetchone()
postgres_delete_query = f"""DELETE FROM group_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
postgres_delete_query = f"""DELETE FROM registration_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
if data or data_2:
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text='取消成功')
)
else:
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text='無可取消的開團/報名資料')
)
elif event.message.text == "~join":
msg=flexmsg.activity_type
line_bot_api.reply_message(
event.reply_token,
msg
)
elif event.message.text == "~查詢我的報名":
#用user_id從database找出有報的團
postgres_select_query = f"""SELECT * FROM registration_data WHERE user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
#避免look_up_data_registration裡的actinity_name重複
look_up_data_registration = []
act_no = []
alldata = cursor.fetchall()
if alldata:
for act in alldata:
if act[1] not in act_no: #act[1]為activity_no, act[2]為activity_name
act_no.append(act[1])
look_up_data_registration.append(act)
msg = flexmsg.registration_list(look_up_data_registration)
line_bot_api.reply_message(
event.reply_token,
msg
)
#如果有創建了一列, 則接下來的資料繼續寫入
else:
postgres_select_query = f"""SELECT * FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
#準備寫入報名資料的那一列
data_2 = cursor.fetchone()
column_all_registration = ['record_no', 'activity_no',
'activity_name', 'attendee_name', 'phone',
'mail', 'condition', 'user_id']
activity_type = ['登山踏青', '桌遊麻將', '吃吃喝喝', '唱歌跳舞']
postgres_select_query = f"""SELECT * FROM group_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
print('data = ', data)
column_all = ['acrivity_no', 'activity_type', 'activity_name',
'activity_date', 'activity_time', 'location_tittle', 'lat', 'long', 'people', 'cost',
'due_date', 'description', 'photo', 'name',
'phone', 'mail', 'attendee', 'condition', 'user_id']
if data:
if None in data:
i = data.index(None)
print("i= ",i)
record = event.message.text
#如果使用者輸入的資料不符合資料庫的資料型態, 則輸入N/A
if event.message.type == 'text':
postgres_update_query = f"""UPDATE group_data SET {column_all[i]} = '{record}' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
else:
postgres_update_query = f"""UPDATE group_data SET {column_all[i]} = 'N/A' WHERE condition = 'initial'AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
#如果還沒輸入到最後一格, 則繼續詢問下一題
postgres_select_query = f"""SELECT * FROM group_data WHERE condition = 'initial'AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
if None in data:
msg=flexmsg.flex(i,data)
line_bot_api.reply_message(
event.reply_token,
msg)
#如果已經到最後一格, condition改為finish, 回覆summary，
elif None not in data:
postgres_select_query = f"""SELECT * FROM group_data WHERE user_id = '{event.source.user_id}' ORDER BY activity_no DESC;"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
msg=flexmsg.summary(data)
line_bot_api.reply_message(
event.reply_token,
msg
)
else:
if event.message.text == '確認開團':
postgres_update_query = f"""UPDATE group_data SET condition = 'pending' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text="finish!!")
)
cursor.close()
conn.close()
else:
column = event.message.text
# 處理location 因為location 跟資料庫的名字不一樣
if column == "location":
postgres_update_query = f"""UPDATE group_data SET location_tittle = Null WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
msg=flexmsg.flex(column,data)
line_bot_api.reply_message(
event.reply_token,
msg
)
elif column in column_all:
postgres_update_query = f"""UPDATE group_data SET {column} = Null WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
msg=flexmsg.flex(column,data)
line_bot_api.reply_message(
event.reply_token,
msg
)
else :
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text= 'please enter the column you want to edit')
)
elif event.message.text in activity_type: #這裡的event.message.text會是上面quick reply回傳的訊息(四種type其中一種)
DATABASE_URL = os.environ['DATABASE_URL']
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
postgres_select_query = f"""SELECT * FROM group_data WHERE activity_date >= '{dt.date.today()}' AND activity_type='{event.message.text}' and people > attendee and condition = 'pending' ORDER BY activity_date ASC ;"""
cursor.execute(postgres_select_query)
data_2 = cursor.fetchall()
msg=flexmsg.carousel(data_2)
line_bot_api.reply_message(
event.reply_token,
msg
)
elif "詳細資訊" in event.message.text :
record=event.message.text.split("_")
DATABASE_URL = os.environ['DATABASE_URL']
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
postgres_select_query = f"""SELECT * FROM group_data WHERE activity_no = '{record[0]}' ;"""
cursor.execute(postgres_select_query)
data_tmp = cursor.fetchone()
msg=flexmsg.MoreInfoSummary(data_tmp)
line_bot_api.reply_message(
event.reply_token,
msg
)
#~~點了carousel的"了解更多"，跳出該團的summary
elif '立即報名' in event.message.text: #點了"立即報名後即回傳activity_no和activity_name"
record=event.message.text.split("_")
#刪掉報名失敗的列
postgres_delete_query = f"""DELETE FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_delete_query)
conn.commit()
#創建一列
postgres_insert_query = f"""INSERT INTO registration_data (condition, user_id, activity_no, activity_name ) VALUES ('initial', '{event.source.user_id}','{record[1]}', '{record[2]}');"""
cursor.execute(postgres_insert_query)
conn.commit()
elif '立即報名' in event.message.text: #點了"立即報名後即回傳activity_no和activity_name"
record=event.message.text.split("_")
#record[0]:立即報名 record[1]：活動代號 record[2]:活動名稱
#刪掉報名失敗的列
postgres_delete_query = f"""DELETE FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_delete_query)
conn.commit()
#創建一列
postgres_insert_query = f"""INSERT INTO registration_data (condition, user_id, activity_no, activity_name ) VALUES ('initial', '{event.source.user_id}','{record[1]}', '{record[2]}');"""
cursor.execute(postgres_insert_query)
conn.commit()
#撈報團者的資料
postgres_select_query=f'''SELECT attendee_name, phone FROM registration_data WHERE user_id = '{event.source.user_id}' AND condition!= 'initial' ORDER BY record_no DESC;'''
cursor.execute(postgres_select_query)
data_for_basicinfo = cursor.fetchone()
phone= data_for_basicinfo[1]
#審核電話
postgres_select_query = f"""SELECT phone FROM registration_data WHERE activity_no = '{record[1]}' ;"""
cursor.execute(postgres_select_query)
phone_registration = cursor.fetchall()
#如果使用者輸入的電話重複則報名失敗，刪掉原本創建的列
if data_for_basicinfo:
if (f'{phone}',) in phone_registration:
postgres_select_query = f"""SELECT * FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data_2 = cursor.fetchone()
i_2 = data_2.index(None)
msg_2 = flexmsg.extend(i_2,data_2) #flexmsg需要新增報名情境
line_bot_api.reply_message(
event.reply_token,
msg_2
)
else:
postgres_update_query = f"""UPDATE registration_data SET attendee_name='{data_for_basicinfo[0]}' , phone='{data_for_basicinfo[1]}' WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_update_query)
conn.commit()
postgres_select_query = f"""SELECT * FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data_2 = cursor.fetchone()
msg_2 = flexmsg.summary_for_attend(data_2)
line_bot_api.reply_message(
event.reply_token,
msg_2
)
elif data_2:
if None in data_2:
i_2 = data_2.index(None)
record = event.message.text
#當進行到輸入電話時(i_2==4)，開始檢驗是否重複
if i_2 == 4:
postgres_select_query = f"""SELECT activity_no FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
activity_no = cursor.fetchone()[0]
postgres_select_query = f"""SELECT phone FROM registration_data WHERE activity_no = '{activity_no}';"""
cursor.execute(postgres_select_query)
phone_registration = cursor.fetchall()
#如果使用者輸入的電話重複則報名失敗，刪掉原本創建的列
for phone in phone_registration:
if record in phone_registration:
postgres_delete_query = f"""DELETE FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_delete_query)
conn.commit()
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text="不可重複報名")
)
#~~~這邊感覺可以設計一個flex_msg，出現[返回]按鈕，重新回到報名第一步(按鈕回傳~join)
else:
postgres_update_query = f"""UPDATE registration_data SET {column_all_registration[i_2]} = '{record}' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
else:
#如果使用者輸入的資料不符合資料庫的資料型態, 則輸入N/A
if event.message.type == 'text':
postgres_update_query = f"""UPDATE registration_data SET {column_all_registration[i_2]} = '{record}' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
else:
postgres_update_query = f"""UPDATE registration_data SET {column_all_registration[i_2]} = 'N/A' WHERE condition = 'initial'AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
postgres_select_query = f"""SELECT * FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data_2 = cursor.fetchone() #準備寫入報名資料的那一列
print("i_2 = ",i_2)
print("data_2 = ",data_2)
if None in data_2:
msg_2 = flexmsg.extend(i_2+1,data_2) #flexmsg需要新增報名情境
line_bot_api.reply_message(
event.reply_token,
msg_2
)
#出現summary
elif None not in data_2:
msg_2 = flexmsg.summary_for_attend(data_2)
line_bot_api.reply_message(
event.reply_token,
msg_2
)
else:
if event.message.text == '確認報名':
#找到他報的團的編號activity_no
postgres_select_query = f"""SELECT activity_no FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
activity_no = cursor.fetchone()[0]
#找報該團現在的報名人數attendee並更新(+1)
postgres_select_query = f"""SELECT attendee FROM group_data WHERE activity_no = {activity_no};"""
cursor.execute(postgres_select_query)
attendee = cursor.fetchone()[0]
attendee += 1
#將更新的報名人數attendee記錄到報名表單group_data裡
postgres_update_query = f"""UPDATE group_data SET attendee = {attendee} WHERE activity_no = {activity_no};"""
cursor.execute(postgres_update_query)
conn.commit()
#檢查報名人數attendee是否達上限people
postgres_select_query = f"""SELECT people FROM group_data WHERE activity_no = {activity_no};"""
cursor.execute(postgres_select_query)
people = cursor.fetchone()[0]
if attendee == people:
postgres_update_query = f"""UPDATE group_data SET condition = 'closed' WHERE activity_no = {activity_no};"""
cursor.execute(postgres_update_query)
conn.commit()
#將報名表單的condition改成closed
postgres_update_query = f"""UPDATE registration_data SET condition = 'closed' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text="finish!!")
)
cursor.close()
conn.close()
elif event.message.text in column_all_registration:
postgres_update_query = f"""UPDATE registration_data SET {event.message.text} = Null WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
msg_2 = flexmsg.extend(event.message.text,data_2) #flexmsg需要新增報名情境
line_bot_api.reply_message(
event.reply_token,
msg_2
)
else:
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text= 'please enter the column you want to edit')
)
elif "查報名" in event.message.text: #點選列表裡的活動將回傳(activity_no_查報名)
activity_no = event.message.text.split('_')[0]
#根據回傳的activity_no，從group_data裡找到活動資訊
postgres_select_query = f"""SELECT * FROM group_data WHERE activity_no = {activity_no};"""
cursor.execute(postgres_select_query)
group_info = cursor.fetchone()
#根據回傳的activity_no和user_id找到報名資訊(可能不只一列)
postgres_select_query = f"""SELECT * FROM registration_data WHERE activity_no = {activity_no} AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
registration_info = cursor.fetchall()
msg = flexmsg.carousel_registration(group_info, registration_info)
line_bot_api.reply_message(
event.reply_token,
msg
)
# bubble的input為"group_info"和"registration_info"
# 報名資訊要有"取消報名"按鈕
elif "取消報名" in event.message.text: #按下取消報名按鈕將回傳(record_activity_取消報名)
record_no = event.message.text.split('_')[0]
activity_no = event.message.text.split('_')[1]
postgres_delete_query = f"""DELETE FROM registration_data WHERE record_no = {record_no} AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_delete_query)
conn.commit()
#找報該團現在的報名人數attendee並更新(-1)
postgres_select_query = f"""SELECT attendee FROM group_data WHERE activity_no = {activity_no};"""
cursor.execute(postgres_select_query)
attendee = cursor.fetchone()[0]
attendee -= 1
#將更新的報名人數attendent記錄到報名表單group_data裡
postgres_update_query = f"""UPDATE group_data SET attendee = {attendee} WHERE activity_no = {activity_no};"""
cursor.execute(postgres_update_query)
conn.commit()
#更新該活動的condition(=pending)
postgres_update_query = f"""UPDATE group_data SET condition = 'pending' WHERE activity_no = {activity_no};"""
cursor.execute(postgres_update_query)
conn.commit()
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text="取消成功!")
)
else:
if event.message.text.encode == "double":
line_bot_api.reply_message(
event.reply_token,
flexmsg.summary(data)
)
elif event.message.text == "早安":
img = 'https://pic.pimg.tw/ellenlee0409/1566550498-3560465550.jpg'
line_bot_api.reply_message(
event.reply_token,
ImageSendMessage(original_content_url=img ,preview_image_url=img)
)
def echo(event):
# [0] 總題數 [1:]目前題數
progress_list_fullgroupdata = [7, 1, 2, 3, 4, 5, 6, 7]
progress_list_halfgroupdata = [5, 1, 2, 3, 4, 5]
progress_list_fullregistrationdata = [2, 0, 0, 0, 0, 0, 1, 2]
event.message.text = event.message.text.replace("'", "‘")
#progress_target
if event.source.user_id != "Udeadbeefdeadbeefdeadbeefdeadbeef":
#連結到heroku資料庫
DATABASE_URL = os.environ['DATABASE_URL']
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
if event.message.text == "我要開團":
line_bot_api.reply_message(event.reply_token,
flexmsg.activity_type)
print("prepare to open the group")
#把只創建卻沒有寫入資料的列刪除
postgres_delete_query = f"""DELETE FROM group_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
postgres_delete_query = f"""DELETE FROM registration_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
#創建一列(condition = initial)
postgres_insert_query = f"""INSERT INTO group_data (condition, user_id, attendee, photo, description) VALUES ('initial', '{event.source.user_id}', '1', '無', '無');"""
cursor.execute(postgres_insert_query)
conn.commit()
#撈主揪的資料
postgres_select_query = f'''SELECT name,phone FROM group_data WHERE user_id = '{event.source.user_id}' AND condition != 'initial' ORDER BY activity_no DESC;'''
cursor.execute(postgres_select_query)
data_for_basicinfo = cursor.fetchone()
if data_for_basicinfo:
postgres_update_query = f"""UPDATE group_data SET name = '{data_for_basicinfo[0]}' , phone = '{data_for_basicinfo[1]}' WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_update_query)
conn.commit()
cursor.close()
conn.close()
#中途想結束輸入~delete, 把initial那列刪除
elif event.message.text == "取消":
postgres_select_query = f'''SELECT * FROM group_data WHERE user_id = '{event.source.user_id}' AND condition = 'initial';'''
cursor.execute(postgres_select_query)
data = cursor.fetchone()
postgres_select_query = f'''SELECT * FROM registration_data WHERE user_id = '{event.source.user_id}' AND condition = 'initial';'''
cursor.execute(postgres_select_query)
data_2 = cursor.fetchone()
postgres_delete_query = f"""DELETE FROM group_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
postgres_delete_query = f"""DELETE FROM registration_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
if data or data_2:
line_bot_api.reply_message(event.reply_token,
TextSendMessage(text='取消成功'))
else:
line_bot_api.reply_message(
event.reply_token, TextSendMessage(text='無可取消的開團/報名資料'))
elif event.message.text == "我要報名":
#把只創建卻沒有寫入資料的列刪除
postgres_delete_query = f"""DELETE FROM group_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
postgres_delete_query = f"""DELETE FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_delete_query)
conn.commit()
msg = flexmsg.activity_type
line_bot_api.reply_message(event.reply_token, msg)
#如果有創建了一列, 則接下來的資料繼續寫入
else:
postgres_select_query = f"""SELECT * FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
#準備寫入報名資料的那一列
data_2 = cursor.fetchone()
print("data_2 :", data_2)
column_all_registration = [
'record_no', 'activity_no', 'activity_name', 'attendee_name',
'phone', 'mail', 'condition', 'user_id'
]
activity_type = ['登山踏青', '桌遊麻將', '吃吃喝喝', '唱歌跳舞']
postgres_select_query = f"""SELECT * FROM group_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
print('data = ', data)
column_all = [
'acrivity_no', 'activity_type', 'activity_name',
'activity_date', 'activity_time', 'location_tittle', 'lat',
'long', 'people', 'cost', 'due_date', 'description', 'photo',
'name', 'phone', 'mail', 'attendee', 'condition', 'user_id'
]
if data:
try:
if len(data[14]) > 0:
progress_target = progress_list_halfgroupdata
else:
progress_target = progress_list_fullgroupdata
except:
progress_target = progress_list_fullgroupdata
if None in data:
i = data.index(None)
print("i = ", i)
if i == 5:
line_bot_api.reply_message(event.reply_token, [
TextSendMessage(text="請點選按鈕選擇活動地點，謝謝。"),
flexmsg.location(progress_target)
])
elif i == 12:
postgres_update_query = f"""UPDATE group_data SET {column_all[i]} = '無' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
postgres_select_query = f"""SELECT * FROM group_data WHERE user_id = '{event.source.user_id}' ORDER BY activity_no DESC;"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
msg = flexmsg.summary(data)
line_bot_api.reply_message(
event.reply_token,
[TextSendMessage(text="上傳失敗。"), msg])
else:
record = event.message.text
#如果使用者輸入的資料不符合資料庫的資料型態, 則輸入N/A
try:
postgres_update_query = f"""UPDATE group_data SET {column_all[i]} = '{record}' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
except:
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text="請重新輸入"))
#如果還沒輸入到最後一格, 則繼續詢問下一題
postgres_select_query = f"""SELECT * FROM group_data WHERE condition = 'initial'AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
if None in data:
msg = flexmsg.flex(i, data, progress_target)
line_bot_api.reply_message(event.reply_token, msg)
#如果已經到最後一格, condition改為finish, 回覆summary，
elif None not in data:
postgres_select_query = f"""SELECT * FROM group_data WHERE user_id = '{event.source.user_id}' ORDER BY activity_no DESC;"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
msg = flexmsg.summary(data)
line_bot_api.reply_message(event.reply_token, msg)
else:
if event.message.text == '確認開團':
postgres_update_query = f"""UPDATE group_data SET condition = 'pending' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
line_bot_api.reply_message(
event.reply_token, TextSendMessage(text="開團成功"))
cursor.close()
conn.close()
else:
column = event.message.text
# 處理location 因為location 跟資料庫的名字不一樣
if column == "location":
postgres_update_query = f"""UPDATE group_data SET location_tittle = Null WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
progress_target = [7, 6, 6, 6, 6, 6, 6, 6]
msg = flexmsg.flex(column, data, progress_target)
line_bot_api.reply_message(event.reply_token, msg)
elif column in column_all:
postgres_update_query = f"""UPDATE group_data SET {column} = Null WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
progress_target = [7, 6, 6, 6, 6, 6, 6, 6]
msg = flexmsg.flex(column, data, progress_target)
line_bot_api.reply_message(event.reply_token, msg)
else:
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text='請輸入您想修改的欄位'))
elif event.message.text in activity_type: #這裡的event.message.text會是上面quick reply回傳的訊息(四種type其中一種)
DATABASE_URL = os.environ['DATABASE_URL']
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
postgres_select_query = f"""SELECT * FROM group_data WHERE activity_date >= '{dt.date.today()}' AND due_date >= '{dt.date.today()}' AND activity_type='{event.message.text}' and people > attendee and condition = 'pending' ORDER BY activity_date ASC ;"""
cursor.execute(postgres_select_query)
data_2 = cursor.fetchall()
msg = flexmsg.carousel(data_2)
line_bot_api.reply_message(event.reply_token, msg)
elif data_2:
if None in data_2:
i_2 = data_2.index(None)
record = event.message.text
#當進行到輸入電話時(i_2 == 4)，開始檢驗是否重複
if i_2 == 4:
postgres_select_query = f"""SELECT activity_no FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
activity_no = cursor.fetchone()[0]
postgres_select_query = f"""SELECT phone FROM registration_data WHERE activity_no = '{activity_no}';"""
cursor.execute(postgres_select_query)
phone_registration = cursor.fetchall()
#如果使用者輸入的電話重複則報名失敗，刪掉原本創建的列
if (f'{record}', ) in phone_registration:
postgres_delete_query = f"""DELETE FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_delete_query)
conn.commit()
line_bot_api.reply_message(event.reply_token, [
TextSendMessage(text="不可重複報名"),
flexmsg.activity_type
])
#~~~這邊感覺可以設計一個flex_msg，出現[返回]按鈕，重新回到報名第一步(按鈕回傳~join)
else:
postgres_update_query = f"""UPDATE registration_data SET {column_all_registration[i_2]} = '{record}' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
else:
#如果使用者輸入的資料不符合資料庫的資料型態, 則輸入N/A
try:
postgres_update_query = f"""UPDATE registration_data SET {column_all_registration[i_2]} = '{record}' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
except:
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text="請重新輸入"))
postgres_select_query = f"""SELECT * FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data_2 = cursor.fetchone() #準備寫入報名資料的那一列
print("i_2 = ", i_2)
print("data_2 = ", data_2)
if None in data_2:
msg_2 = flexmsg.extend(
i_2 + 1, data_2, progress_list_fullregistrationdata
) #flexmsg需要新增報名情境
line_bot_api.reply_message(event.reply_token, msg_2)
#出現summary
elif None not in data_2:
msg_2 = flexmsg.summary_for_attend(data_2)
line_bot_api.reply_message(event.reply_token, msg_2)
else:
if event.message.text == '確認報名':
#找到他報的團的編號activity_no
postgres_select_query = f"""SELECT activity_no FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
activity_no = cursor.fetchone()[0]
#找報該團現在的報名人數attendee並更新(+1)
postgres_select_query = f"""SELECT attendee, condition FROM group_data WHERE activity_no = {activity_no};"""
cursor.execute(postgres_select_query)
temp = cursor.fetchone()
attendee = temp[0]
condition = temp[1]
if condition == "closed":
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text="報名失敗"))
else:
attendee += 1
#將更新的報名人數attendee記錄到報名表單group_data裡
postgres_update_query = f"""UPDATE group_data SET attendee = {attendee} WHERE activity_no = {activity_no};"""
cursor.execute(postgres_update_query)
conn.commit()
#檢查報名人數attendee是否達上限people
postgres_select_query = f"""SELECT people FROM group_data WHERE activity_no = {activity_no};"""
cursor.execute(postgres_select_query)
people = cursor.fetchone()[0]
if attendee == people:
postgres_update_query = f"""UPDATE group_data SET condition = 'closed' WHERE activity_no = {activity_no};"""
cursor.execute(postgres_update_query)
conn.commit()
#將報名表單的condition改成closed
postgres_update_query = f"""UPDATE registration_data SET condition = 'closed' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
line_bot_api.reply_message(
event.reply_token, TextSendMessage(text="報名成功"))
cursor.close()
conn.close()
elif event.message.text in column_all_registration:
postgres_update_query = f"""UPDATE registration_data SET {event.message.text} = Null WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
msg_2 = flexmsg.extend(
event.message.text, data_2,
progress_list_fullregistrationdata
) #flexmsg需要新增報名情境
line_bot_api.reply_message(event.reply_token, msg_2)
else:
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text='請輸入您想修改的欄位'))
else:
if event.message.text.encode == "double":
line_bot_api.reply_message(event.reply_token,
flexmsg.summary(data))
elif event.message.text == "早安":
img = 'https://pic.pimg.tw/ellenlee0409/1566550498-3560465550.jpg'
line_bot_api.reply_message(
event.reply_token,
ImageSendMessage(original_content_url=img,
preview_image_url=img))
def gathering(event):
progress_list_fullgroupdata = [7, 1, 2, 3, 4, 5, 6, 7]
progress_list_halfgroupdata = [5, 1, 2, 3, 4, 5]
progress_list_fullregistrationdata = [2, 0, 0, 0, 0, 0, 1, 2]
DATABASE_URL = os.environ['DATABASE_URL']
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
postgres_select_query = f"""SELECT * FROM group_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
postback_data = event.postback.data
try:
if len(data[14]) > 0:
progress_target = progress_list_halfgroupdata
else:
progress_target = progress_list_fullgroupdata
except:
progress_target = progress_list_fullgroupdata
#主揪查看自己開的團的資訊 (活動名稱、地點、時間、費用、已報名人數)
if "我的開團" in postback_data:
#把只創建卻沒有寫入資料完成的列刪除
postgres_delete_query = f"""DELETE FROM group_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
postgres_delete_query = f"""DELETE FROM registration_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
postgres_select_query = f"""SELECT * FROM group_data WHERE user_id = '{event.source.user_id}' AND activity_date >= '{dt.date.today()}' ORDER BY activity_date ASC;"""
cursor.execute(postgres_select_query)
group_data = cursor.fetchall()
print("group_data = ", group_data)
msg = flexmsg.GroupLst(group_data)
line_bot_api.reply_message(event.reply_token, msg)
elif "我的報名" in postback_data:
#把只創建卻沒有寫入資料完成的列刪除
postgres_delete_query = f"""DELETE FROM group_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
postgres_delete_query = f"""DELETE FROM registration_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
#用user_id從database找出有報的團
postgres_select_query = f"""SELECT * FROM registration_data WHERE user_id = '{event.source.user_id}'AND activity_date >= '{dt.date.today()}' ORDER BY activity_date ASC;"""
cursor.execute(postgres_select_query)
#避免look_up_data_registration裡的actinity_name重複
look_up_data_registration = []
act_no = []
alldata = cursor.fetchall()
if alldata:
for act in alldata:
if act[1] not in act_no: #act[1]為activity_no, act[2]為activity_name
act_no.append(act[1])
look_up_data_registration.append(act)
print(act)
msg = flexmsg.registration_list(look_up_data_registration)
line_bot_api.reply_message(event.reply_token, msg)
elif "開團資訊" in postback_data:
activity_no = postback_data.replace("開團資訊", "")
postgres_select_query = f"""SELECT * FROM group_data WHERE activity_no = '{activity_no}';"""
cursor.execute(postgres_select_query)
group_data = cursor.fetchone()
print("group_data = ", group_data)
msg = flexmsg.MyGroupInfo(group_data)
line_bot_api.reply_message(event.reply_token, msg)
#主揪查看報名者資訊(報名者暱稱、電話)
elif "報名者資訊" in postback_data:
activity_no = postback_data.replace("報名者資訊", "")
postgres_select_query = f"""SELECT activity_name FROM registration_data WHERE activity_no = '{activity_no}';"""
cursor.execute(postgres_select_query)
# try:
temp = cursor.fetchone()
if temp:
activity_name = "".join(temp)
print("activity_name = ", activity_name)
postgres_select_query = f"""SELECT attendee_name, phone FROM registration_data WHERE activity_no = '{activity_no}' ;"""
cursor.execute(postgres_select_query)
attendee_data = cursor.fetchall()
print("attendee_data = ", attendee_data)
attendee_lst = []
for row in attendee_data:
attendee_lst.append(" ".join(row))
msg = f"{activity_name}" + "\n報名者資訊："
for attendee in attendee_lst:
msg += f"\n{attendee}"
# except:
# msg = "本活動目前無人報名"
line_bot_api.reply_message(event.reply_token,
TextSendMessage(text=msg))
else:
line_bot_api.reply_message(event.reply_token,
TextSendMessage(text='目前無人報名'))
#主揪提早關團
elif "結束報名" in postback_data:
activity_no = postback_data.replace("結束報名", "")
postgres_update_query = f"""UPDATE group_data SET condition = 'closed' WHERE activity_no = '{activity_no}';"""
cursor.execute(postgres_update_query)
conn.commit()
line_bot_api.reply_message(event.reply_token,
TextSendMessage(text="成功結束報名！"))
elif "查報名" in postback_data: #點選列表裡的活動將回傳(activity_no_查報名)
activity_no = postback_data.split('_')[0]
#根據回傳的activity_no，從group_data裡找到活動資訊
postgres_select_query = f"""SELECT * FROM group_data WHERE activity_no = {activity_no};"""
cursor.execute(postgres_select_query)
group_info = cursor.fetchone()
#根據回傳的activity_no和user_id找到報名資訊(可能不只一列)
postgres_select_query = f"""SELECT * FROM registration_data WHERE activity_no = {activity_no} AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
registration_info = cursor.fetchall()
msg = flexmsg.carousel_registration(group_info, registration_info)
line_bot_api.reply_message(event.reply_token, msg)
# bubble的input為"group_info"和"registration_info"
# 報名資訊要有"取消報名"按鈕
elif "取消報名" in postback_data: #按下取消報名按鈕將回傳(record_activity_取消報名)
record_no = postback_data.split('_')[0]
activity_no = postback_data.split('_')[1]
postgres_delete_query = f"""DELETE FROM registration_data WHERE record_no = {record_no} AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_delete_query)
conn.commit()
#找報該團現在的報名人數attendee並更新(-1)
postgres_select_query = f"""SELECT attendee FROM group_data WHERE activity_no = {activity_no};"""
cursor.execute(postgres_select_query)
attendee = cursor.fetchone()[0]
attendee -= 1
#將更新的報名人數attendent記錄到報名表單group_data裡
postgres_update_query = f"""UPDATE group_data SET attendee = {attendee} WHERE activity_no = {activity_no};"""
cursor.execute(postgres_update_query)
conn.commit()
#更新該活動的condition(= pending)
postgres_update_query = f"""UPDATE group_data SET condition = 'pending' WHERE activity_no = {activity_no};"""
cursor.execute(postgres_update_query)
conn.commit()
line_bot_api.reply_message(event.reply_token,
TextSendMessage(text="取消成功!"))
elif "詳細資訊" in postback_data:
record = postback_data.split("_")
DATABASE_URL = os.environ['DATABASE_URL']
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cursor = conn.cursor()
postgres_select_query = f"""SELECT * FROM group_data WHERE activity_no = '{record[0]}' ;"""
cursor.execute(postgres_select_query)
data_tmp = cursor.fetchone()
msg = flexmsg.MoreInfoSummary(data_tmp)
line_bot_api.reply_message(event.reply_token, msg)
#~~點了carousel的"了解更多"，跳出該團的summary
elif '立即報名' in postback_data: #點了"立即報名後即回傳activity_no和activity_name"
record = postback_data.split("_")
#record[0]:立即報名 record[1]：活動代號 record[2]:活動名稱 record[3]: 活動日期
#把只創建卻沒有寫入資料的列刪除
postgres_delete_query = f"""DELETE FROM group_data WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_delete_query)
conn.commit()
postgres_delete_query = f"""DELETE FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_delete_query)
conn.commit()
#創建一列
postgres_insert_query = f"""INSERT INTO registration_data (condition, user_id, activity_no, activity_name, activity_date ) VALUES ('initial', '{event.source.user_id}','{record[1]}', '{record[2]}', '{record[3]}');"""
cursor.execute(postgres_insert_query)
conn.commit()
#撈報團者的資料
postgres_select_query = f'''SELECT attendee_name, phone FROM registration_data WHERE user_id = '{event.source.user_id}' AND condition != 'initial' ORDER BY record_no DESC;'''
cursor.execute(postgres_select_query)
data_for_basicinfo = cursor.fetchone()
print(" 651 data_for_basicinfo = ", data_for_basicinfo)
#審核電話
postgres_select_query = f"""SELECT phone FROM registration_data WHERE activity_no = '{record[1]}' ;"""
cursor.execute(postgres_select_query)
phone_registration = cursor.fetchall()
#如果使用者輸入的電話重複則報名失敗，刪掉原本創建的列
if data_for_basicinfo:
phone = data_for_basicinfo[1]
if (f'{phone}', ) in phone_registration:
postgres_select_query = f"""SELECT * FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data_2 = cursor.fetchone()
i_2 = data_2.index(None)
print("617 count none in data_2 = ", data_2.count(None))
print("618 i_2", i_2)
msg_2 = flexmsg.extend(
i_2, data_2,
progress_list_fullregistrationdata) #flexmsg需要新增報名情境
line_bot_api.reply_message(event.reply_token, msg_2)
else:
postgres_update_query = f"""UPDATE registration_data SET attendee_name = '{data_for_basicinfo[0]}' , phone = '{data_for_basicinfo[1]}' WHERE (condition, user_id) = ('initial', '{event.source.user_id}');"""
cursor.execute(postgres_update_query)
conn.commit()
postgres_select_query = f"""SELECT * FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data_2 = cursor.fetchone()
msg_2 = flexmsg.summary_for_attend(data_2)
line_bot_api.reply_message(event.reply_token, msg_2)
else:
postgres_select_query = f"""SELECT * FROM registration_data WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_select_query)
data_2 = cursor.fetchone()
i_2 = data_2.index(None)
print("617 count none in data_2 = ", data_2.count(None))
print("618 i_2", i_2)
msg_2 = flexmsg.extend(
i_2, data_2,
progress_list_fullregistrationdata) #flexmsg需要新增報名情境
line_bot_api.reply_message(event.reply_token, msg_2)
elif "forward" in postback_data or "backward" in postback_data:
record = postback_data.split(
"_") #record[0] = forward, reocord[1] = command
if record[1] == "activity":
#record[2] = activity_type, record[3] = i
i = int(record[3])
postgres_select_query = f"""SELECT * FROM group_data WHERE activity_date >= '{dt.date.today()}' AND due_date >= '{dt.date.today()}' AND activity_type = '{record[2]}' and people > attendee and condition = 'pending' ORDER BY activity_date ASC;"""
cursor.execute(postgres_select_query)
data = cursor.fetchall()
msg = flexmsg.carousel(data, i)
line_bot_api.reply_message(event.reply_token, msg)
elif record[1] == "group":
#record[2] = i
i = int(record[2])
#用user_id尋找該主揪所有的開團資料
postgres_select_query = f"""SELECT * FROM group_data WHERE user_id = '{event.source.user_id}' AND activity_date >= '{dt.date.today()}' ORDER BY activity_date ASC;"""
cursor.execute(postgres_select_query)
group_data = cursor.fetchall()
print("group_data = ", group_data)
#回傳開團列表
msg = flexmsg.GroupLst(group_data, i)
line_bot_api.reply_message(event.reply_token, msg)
elif record[1] == "registration":
#record[2] = i
i = int(record[2])
#用user_id從database找出有報的團
postgres_select_query = f"""SELECT * FROM registration_data WHERE user_id = '{event.source.user_id}' AND activity_date >= '{dt.date.today()}' ORDER BY activity_date ASC;"""
cursor.execute(postgres_select_query)
#避免look_up_data_registration裡的actinity_name重複
look_up_data_registration = []
act_no = []
alldata = cursor.fetchall()
if alldata:
for act in alldata:
if act[1] not in act_no: #act[1]為activity_no, act[2]為activity_name
act_no.append(act[1])
look_up_data_registration.append(act)
msg = flexmsg.registration_list(look_up_data_registration, i)
line_bot_api.reply_message(event.reply_token, msg)
#上一頁下一頁要寫在這個else上面
else:
i = data.index(None)
print("i = ", i)
column_all = [
'acrivity_no', 'activity_type', 'activity_name', 'activity_date',
'activity_time', 'location_tittle', 'lat', 'long', 'people',
'cost', 'due_date', 'description', 'photo', 'name', 'phone',
'mail', 'attendee', 'condition', 'user_id'
]
#處理activity date and time
if event.postback.data == "Activity_time":
record = event.postback.params['datetime']
record = record.split("T")
temp = dt.date.fromisoformat(record[0]) - dt.timedelta(days=1)
postgres_update_query = f"""UPDATE group_data SET ({column_all[i]}, {column_all[i+1]}, {column_all[i+7]}) = ('{record[0]}', '{record[1]}', '{temp}') WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
#處理due date
elif event.postback.data == "Due_time":
record = event.postback.params['date']
postgres_update_query = f"""UPDATE group_data SET {column_all[i]} = '{record}' WHERE condition = 'initial' AND user_id = '{event.source.user_id}';"""
cursor.execute(postgres_update_query)
conn.commit()
cursor.execute(postgres_select_query)
data = cursor.fetchone()
if None in data:
msg = flexmsg.flex(i, data, progress_target)
line_bot_api.reply_message(event.reply_token, msg)
elif None not in data:
postgres_select_query = f"""SELECT * FROM group_data WHERE user_id = '{event.source.user_id}' ORDER BY activity_no DESC;"""
cursor.execute(postgres_select_query)
data = cursor.fetchone()
msg = flexmsg.summary(data)
line_bot_api.reply_message(event.reply_token, msg)
cursor.close()
conn.close()