def seetemperture(event): #顯示體溫
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
userid = event.source.user_id
user_id = """select * from user_id"""
gcpsql.execute(user_id)
records = gcpsql.fetchall()
timeotp = time.strftime("%Y/%m/%d", time.localtime())
pr = timeotp
for row in records:
if row[3] == str(userid):
pr += googlesheettest.temperture(int(row[1]))
try:
message = TextSendMessage(text=pr)
line_bot_api.reply_message(event.reply_token, message)
except:
line_bot_api.reply_message(event.reply_token,
TextSendMessage(text='發生錯誤！'))
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
def temperture(i):
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
output = ""
if (authorize.authtemperture(i) == "yes"):
sql_select_Query = "select * from body_temperture"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
output += " 體溫總表：\n\n"
for row in records:
output += str(row[1]) + ". " + str(row[3]) + " " + str(
row[4]) + "\n"
output += "\n輸出結束"
else:
output += " 你的體溫：\n\n"
morning = """select morning from body_temperture WHERE stu_id=%s"""
gcpsql.execute(morning, (i, ))
datas = gcpsql.fetchone()
temp = str(datas)
output += temp[1] + temp[2] + temp[3] + temp[4] + " "
evening = """select evening from body_temperture WHERE stu_id=%s"""
gcpsql.execute(evening, (i, ))
datas = gcpsql.fetchone()
temp = str(datas)
output += temp[1] + temp[2] + temp[3] + temp[4]
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
return output
def homework():
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
sql_select_Query = "select * from homework order by expiretime asc"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
output = ""
i = 0
for row in records:
i = i + 1
output += str(i) + "." + row[2] + " "
date = row[3]
output += "("
for j in range(5, 7):
output += date[j]
output += "/"
for j in range(8, 10):
output += date[j]
output += ")\n"
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
return output
def run():
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
today = date.today()
dta = timedelta(days=1)
now = today - dta
ans = now.strftime("%m%d")
name = "morning_" + ans
new_column = """ALTER TABLE body_temperture Change morning `%s` FLOAT"""
gcpsql.execute(new_column, (name, ))
lssh1.commit()
name = "evening_" + ans
new_column = """ALTER TABLE body_temperture Change evening `%s` FLOAT"""
gcpsql.execute(new_column, (name, ))
lssh1.commit()
new_column = "ALTER TABLE body_temperture ADD morning FLOAT after nickname"
gcpsql.execute(new_column)
lssh1.commit()
new_column = "ALTER TABLE body_temperture ADD evening FLOAT after morning"
gcpsql.execute(new_column)
lssh1.commit()
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
def notice():
destclass = ["<Class are going to find>"]
output = "體溫警示：\n\n"
for i in destclass:
flag = 0
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=i,
)
gcpsql = lssh1.cursor()
sql_select_Query = """select stu_id,morning,evening from body_temperture WHERE morning IS NOT NULL"""
#targetclass=destclass[0]+'.'+'body_temperture'
#target=targetclass
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
output += i[4] + i[5] + i[6] + " 班\n"
for row in records:
if (row[1] >= 37.5):
flag = 1
output += i[4] + i[5] + i[6] + str(row[0]) + " " + str(
row[1]) + "\n"
if (flag == 0): output += "本日無異常體溫\n"
output += "\n"
return output
def bulletboard():
lssh1 = mysql.connector.connect(
host = todatabase.host(),
port = "3306",
user = todatabase.username(),
password = todatabase.password(),
database=todatabase.database(),)
gcpsql= lssh1.cursor()
sql_select_Query = "select * from bulletboard"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
for row in records:
dateString = row[3]
#if(dateString==""): break
dateFormatter = "%Y/%m/%d"
a=datetime.strptime(dateString, dateFormatter)
#a=a+timedelta(days=1)
if(datetime.now()>a):
sql_Delete_query = """DELETE FROM bulletboard WHERE ID= %s"""
test=row[0]
gcpsql.execute(sql_Delete_query, (test,))
lssh1.commit()
if(lssh1.is_connected()):
gcpsql.close()
lssh1.close()
def test():
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
pr = "--------------------\n考試：\n\n"
sql_select_Query = "select * from test ORDER BY expiretime ASC"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
i = 0
for row in records:
i = i + 1
pr += str(i) + "." + row[2]
date = row[3]
datecor = date[5] + date[6] + "/" + date[8] + date[9]
pr += " (" + datecor + ")\n"
pr += "\n"
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
return pr
def homework():
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
timeotp = time.strftime("%Y/%m/%d", time.localtime())
pr = timeotp + "放學推播：\n--------------------\n作業：\n\n"
sql_select_Query = "select * from homework ORDER BY expiretime ASC"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
i = 0
for row in records:
i = i + 1
pr += str(i) + "." + row[2]
date = row[3]
datecor = date[5] + date[6] + "/" + date[8] + date[9]
pr += " (" + datecor + ")\n"
pr += "\n"
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
return pr
def callback(request):
if request.method == 'POST':
signature = request.META['HTTP_X_LINE_SIGNATURE']
body = request.body.decode('utf-8')
try:
events = parser.parse(body, signature)
except InvalidSignatureError:
return HttpResponseForbidden()
except LineBotApiError:
return HttpResponseBadRequest()
for event in events:
if isinstance(event, MessageEvent):
if isinstance(event.message, TextMessage):
mtext = event.message.text
if mtext == "使用方法":
func.manual(event)
#體溫登陸
if mtext[0] == "體" and mtext[1] == "溫":
destclass = "lssh" + mtext[3] + mtext[4] + mtext[5]
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user="******",
password="******",
database=destclass)
try:
gcpsql = lssh1.cursor()
output = ""
sql_select_Query = "select * from body_temperture"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
output += destclass[4] + destclass[5] + destclass[
6] + " 體溫總表：\n\n"
for row in records:
output += str(row[1]) + ". " + str(
row[3]) + " " + str(row[4]) + "\n"
output += "\n輸出結束"
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text=output))
except:
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text="本班級不存在"))
return HttpResponse()
else:
return HttpResponseBadRequest()
def userpick():
users.clear()
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
gcpsql = lssh1.cursor()
sql_select_Query = "select line_id,notify from user_id WHERE line_id IS NOT NULL"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
for row in records:
if (row[1] == 1):
users.append(row[0])
def exam():
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database="lssh",
)
gcpsql = lssh1.cursor()
sql_select_Query = "select * from examG11_social"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
output = ""
for row in records:
output += row[1] + "：" + row[2] + "\n"
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
return output
def bulletboard():
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
sql_select_Query = "select ID,object from bulletboard order by expiretime asc"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
output = ""
for row in records:
output += str(row[0]) + "." + row[1] + "\n\n"
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
return output
def teacher():
lssh1 = mysql.connector.connect(host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database())
gcpsql = lssh1.cursor()
sqlselect = """select ID,object from bulletboard ORDER BY expiretime ASC"""
gcpsql.execute(sqlselect)
records = gcpsql.fetchall()
output = ""
i = 0
for row in records:
i = i + 1
output += str(i) + "." + row[1] + "\n\n"
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
return output
def authaddrm(auth):
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
user_id = """select * from user_id"""
gcpsql.execute(user_id)
records = gcpsql.fetchall()
i = 0
for row in records:
if row[3] == auth:
i = row[1]
break
if (i >= 1 and i <= 25): auth = "yes"
else: auth = "no"
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
return auth
def callback(request):
if request.method == 'POST':
signature = request.META['HTTP_X_LINE_SIGNATURE']
body = request.body.decode('utf-8')
try:
events = parser.parse(body, signature)
except InvalidSignatureError:
return HttpResponseForbidden()
except LineBotApiError:
return HttpResponseBadRequest()
for event in events:
if isinstance(event, MessageEvent):
if isinstance(event.message, TextMessage):
mtext = event.message.text
if mtext == "使用方法":
func.manual(event)
#體溫登陸
if mtext[0] == "體" and mtext[1] == "溫":
destclass = "lssh" + mtext[3] + mtext[4] + mtext[5]
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user="******",
password="******",
database=destclass)
try:
gcpsql = lssh1.cursor()
output = ""
sql_select_Query = "select * from body_temperture"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
output += destclass[4] + destclass[5] + destclass[
6] + " 體溫總表：\n\n"
for row in records:
output += str(row[1]) + ". " + str(
row[3]) + " " + str(row[4]) + "\n"
output += "\n輸出結束"
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text=output))
except:
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text="本班級不存在"))
if mtext[0] == "s" and mtext[1] == "e" and mtext[
2] == "t": #設定座號
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
user_id = event.source.user_id
profile = line_bot_api.get_profile(user_id)
name = profile.display_name
expt = ""
for j in range(4, len(mtext)):
expt += mtext[j]
check = int(expt)
target = """select rw FROM user_id"""
gcpsql.execute(target)
records = gcpsql.fetchall()
if (records[check - 1][0] == 1):
target = """UPDATE user_id SET line_id=%s, nickname=%s WHERE stu_id=%s"""
record = (user_id, name, expt)
gcpsql.execute(target, (record))
lssh1.commit()
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text="新增座號成功"))
else:
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(
text="本座號已設定並由管理員確認，若需更改請填寫錯誤回報表單"))
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
if mtext == "推播通知":
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
id = event.source.user_id
sql_select_Query = "select line_id,notify from user_id"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
notify = 0
i = 1
for row in records:
if (row[0] == id):
notify = row[1]
break
i += 1
if (notify == 0): notify = 1
elif (notify == 1): notify = 0
update = """UPDATE user_id SET notify=%s WHERE stu_id=%s"""
value = (notify, i)
gcpsql.execute(update, (value))
lssh1.commit()
if (notify == 0): func.notifyoff(event)
elif (notify == 1): func.notifyon(event)
'''# user_id
if mtext!= "":
auth_json_path='linebotlssh-a0cc46d0d13a.json'
gss_scopes=['https://www.googleapis.com/auth/spreadsheets']
credentials= ServiceAccountCredentials.from_json_keyfile_name(auth_json_path,gss_scopes)
gss_client =gspread.authorize(credentials)
spreadsheets_key='1pe5azLF0piRgd00QSLZBDiiqXPAhOXmIo9kBMTtYaE0'
sheet=gss_client.open_by_key(spreadsheets_key).sheet1
user_id = event.source.user_id
profile=line_bot_api.get_profile(user_id)
a=str(user_id)
flag=0
for i in range (2,30):
if (sheet.cell(i,3).value) == a:
if (sheet.cell(i,2).value) == "":
sheet.update_cell(i,2,profile.display_name)
flag=1
else:
flag=1
if(flag==0):
timestamp=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
a=len(mtext)
output=""
for i in range(1,a): output+=mtext[i]
output=[timestamp,profile.display_name,user_id]
sheet.insert_row(output,2)'''
return HttpResponse()
else:
return HttpResponseBadRequest()
def callback(request):
if request.method == 'POST':
signature = request.META['HTTP_X_LINE_SIGNATURE']
body = request.body.decode('utf-8')
try:
events = parser.parse(body, signature)
except InvalidSignatureError:
return HttpResponseForbidden()
except LineBotApiError:
return HttpResponseBadRequest()
for event in events:
if isinstance(event, MessageEvent):
if isinstance(event.message, TextMessage):
mtext = event.message.text
if mtext == "使用方法":
func.manual(event)
#新增作業
if mtext[0] == 'a' and mtext[1] == 'd' and mtext[
2] == 'd' and mtext[3] == 'h' and mtext[4] == 'w':
auth = event.source.user_id
if (authorize.authaddrm(auth) == "yes"):
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
timestamp = time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime())
row = ""
flag = 0
exp = 0
avoid = len(mtext)
for i in range(6, avoid):
if (mtext[i] == "s" and mtext[i + 1] == "e"
and mtext[i + 2] == "t"):
flag = 1 #標記要設定截止日期
count = 0 #欲講字串與數字轉換的變數
expt = [] #輸入的截止日期 (X天後)
for j in range(i + 4, avoid): #將字串加入list中
x = 0
expt.insert(x, mtext[j])
x += 1
for j in range(
0, len(expt)): #從list取出，轉會韋數字格式
if (expt[j]) != 0:
exp += int(expt[j]) * (pow(
10, count))
count += 1
else:
exp += pow(10, count)
count += 1
row = ""
setpoint = i
for j in range(6, setpoint):
row += mtext[j]
break
if (flag == 0):
exp = 10
for j in range(6, avoid):
row += mtext[j]
now = date.today()
dta = timedelta(days=exp)
ans = now + dta
datefinal = ans.strftime("%Y/%m/%d")
target = """INSERT into homework (createtime,object,expiretime) values (%s,%s,%s)"""
record = (timestamp, row, datefinal)
gcpsql.execute(target, (record))
lssh1.commit()
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
func.addhwsuccess(event)
return mtext
else:
func.unauthorize(event)
#顯示作業
if mtext == '作業':
func.hwreply(event)
#刪除作業
if mtext[0] == 'r' and mtext[1] == 'm' and mtext[
2] == 'h' and mtext[3] == 'w':
auth = event.source.user_id
if (authorize.authaddrm(auth) == "yes"):
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
compare = ""
avoid = len(mtext) #算有幾格
for i in range(5, len(mtext)):
compare += mtext[i] #從第5格算數字
target = int(compare)
sql_select_Query = "select * from homework order by expiretime asc"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
delete = records[target - 1][0]
sql_Delete_query = """DELETE FROM homework WHERE ID= %s"""
gcpsql.execute(sql_Delete_query, (delete, ))
lssh1.commit()
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
func.rmhw(event)
return mtext
else:
func.unauthorize(event)
#新增考試
if mtext[0] == 'a' and mtext[1] == 'd' and mtext[
2] == 'd' and mtext[3] == 't' and mtext[4] == 't':
auth = event.source.user_id
if (authorize.authaddrm(auth) == "yes"):
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
timestamp = time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime())
row = ""
flag = 0
exp = 0
avoid = len(mtext)
for i in range(6, avoid):
if (mtext[i] == "s" and mtext[i + 1] == "e"
and mtext[i + 2] == "t"):
flag = 1
count = 0
expt = []
for j in range(i + 4, avoid):
x = 0
expt.insert(x, mtext[j])
x += 1
for j in range(0, len(expt)):
if (expt[j]) != 0:
exp += int(expt[j]) * (pow(
10, count))
count += 1
else:
exp += pow(10, count)
count += 1
#exp=int(mtext[i+4])
setpoint = i
row = ""
for j in range(6, setpoint):
row += mtext[j]
break
if (flag == 0):
exp = 10
for j in range(6, avoid):
row += mtext[j]
now = date.today()
dta = timedelta(days=exp)
ans = now + dta
datefinal = ans.strftime("%Y/%m/%d")
target = """INSERT into test (createtime,object,expiretime) values (%s,%s,%s)"""
record = (timestamp, row, datefinal)
gcpsql.execute(target, (record))
lssh1.commit()
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
func.addtestsuccess(event)
return mtext
else:
func.unauthorize(event)
#顯示考試
if mtext == '考試':
func.ttreply(event)
#刪除考試
if mtext[0] == 'r' and mtext[1] == 'm' and mtext[
2] == 't' and mtext[3] == 't':
auth = event.source.user_id
if (authorize.authaddrm(auth) == "yes"):
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
compare = ""
avoid = len(mtext) #算有幾格
for i in range(5, len(mtext)):
compare += mtext[i] #從第5格算數字
target = int(compare)
sql_select_Query = "select * from test order by expiretime asc"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
delete = records[target - 1][0]
sql_Delete_query = """DELETE FROM test WHERE ID= %s"""
gcpsql.execute(sql_Delete_query, (delete, ))
lssh1.commit()
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
func.rmtt(event)
else:
func.unauthorize(event)
return mtext
if mtext[0] == "#": #新增公告
auth = event.source.user_id
if (authorize.authaddrm(auth) == "yes"):
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
timestamp = time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime())
row = ""
flag = 0
exp = 0
avoid = len(mtext)
for i in range(2, avoid):
if (mtext[i] == "s" and mtext[i + 1] == "e"
and mtext[i + 2] == "t"):
flag = 1
count = 0
expt = []
for j in range(i + 4, avoid):
x = 0
expt.insert(x, mtext[j])
x += 1
for j in range(0, len(expt)):
if (expt[j]) != 0:
exp += int(expt[j]) * (pow(
10, count))
count += 1
else:
exp += pow(10, count)
count += 1
#exp=int(mtext[i+4])
setpoint = i
row = ""
for j in range(2, setpoint):
row += mtext[j]
break
if (flag == 0):
exp = 10
for j in range(2, avoid):
row += mtext[j]
now = date.today()
dta = timedelta(days=exp)
ans = now + dta
datefinal = ans.strftime("%Y/%m/%d")
target = """INSERT into bulletboard (createtime,object,expiretime) values (%s,%s,%s)"""
record = (timestamp, row, datefinal)
gcpsql.execute(target, (record))
lssh1.commit()
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
func.addbulletboard(event)
return mtext
else:
func.unauthorize(event)
#顯示公告
if mtext == '公告':
func.bulletboardreply(event)
#刪除公告
if mtext[0] == 'r' and mtext[1] == 'm' and mtext[
2] == 'b' and mtext[3] == 'b':
auth = event.source.user_id
if (authorize.authaddrm(auth) == "yes"):
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
compare = ""
avoid = len(mtext) #算有幾格
for i in range(5, len(mtext)):
compare += mtext[i] #從第5格算數字
target = int(compare)
sql_select_Query = "select * from bulletboard order by expiretime asc"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
delete = records[target - 1][0]
sql_Delete_query = """DELETE FROM bulletboard WHERE ID= %s"""
gcpsql.execute(sql_Delete_query, (delete, ))
lssh1.commit()
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
func.rmbulletboard(event)
else:
func.unauthorize(event)
return mtext
if mtext == "推播通知":
lssh1 = mysql.connector.connect(
host="127.0.0.1",
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
id = event.source.user_id
sql_select_Query = "select line_id,notify from user_id"
gcpsql.execute(sql_select_Query)
records = gcpsql.fetchall()
notify = 0
i = 1
for row in records:
if (row[0] == id):
notify = row[1]
break
i += 1
if (notify == 0): notify = 1
elif (notify == 1): notify = 0
update = """UPDATE user_id SET notify=%s WHERE stu_id=%s"""
value = (notify, i)
gcpsql.execute(update, (value))
lssh1.commit()
if (notify == 0): func.notifyoff(event)
elif (notify == 1): func.notifyon(event)
#段考
if mtext == "段考":
func.exam(event)
if mtext == "體溫查詢":
func.seetemperture(event)
#體溫
elif mtext[0] == "體" and mtext[1] == "溫":
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
userid = event.source.user_id
user_id = """select * from user_id"""
gcpsql.execute(user_id)
records = gcpsql.fetchall()
insertroll = 0
for row in records:
if row[3] == str(userid):
insertroll = int(row[1])
result = time.localtime()
temp = mtext[3] + mtext[4] + mtext[5] + mtext[6]
if (result.tm_hour <= 12):
target = """UPDATE body_temperture set morning=%s WHERE ID=%s"""
record = (temp, insertroll)
gcpsql.execute(target, (record))
lssh1.commit()
if (result.tm_hour > 12):
target = """UPDATE body_temperture set evening=%s WHERE ID=%s"""
record = (temp, insertroll)
gcpsql.execute(target, (record))
lssh1.commit()
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
func.addtemperturesuccess(event)
if mtext[0] == "s" and mtext[1] == "e" and mtext[
2] == "t": #設定座號
lssh1 = mysql.connector.connect(
host=todatabase.host(),
port="3306",
user=todatabase.username(),
password=todatabase.password(),
database=todatabase.database(),
)
gcpsql = lssh1.cursor()
user_id = event.source.user_id
profile = line_bot_api.get_profile(user_id)
name = profile.display_name
expt = ""
for j in range(4, len(mtext)):
expt += mtext[j]
check = int(expt)
target = """select rw FROM user_id"""
gcpsql.execute(target)
records = gcpsql.fetchall()
if (records[check - 1][0] == 1):
target = """UPDATE user_id SET line_id=%s, nickname=%s WHERE stu_id=%s"""
record = (user_id, name, expt)
gcpsql.execute(target, (record))
lssh1.commit()
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text="新增座號成功"))
else:
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(
text="本座號已設定並由管理員確認，若需更改請填寫錯誤回報表單"))
line_bot_api.reply_message(
event.reply_token, TextSendMessage(text="新增座號成功"))
if (lssh1.is_connected()):
gcpsql.close()
lssh1.close()
# user_id
if mtext != "":
auth_json_path = 'linebotlssh-a0cc46d0d13a.json'
gss_scopes = [
'https://www.googleapis.com/auth/spreadsheets'
]
credentials = ServiceAccountCredentials.from_json_keyfile_name(
auth_json_path, gss_scopes)
gss_client = gspread.authorize(credentials)
spreadsheets_key = '1FJaCeiPDrkkPwFMeOSDfohdOM76v-JmWV1NzljfRBNE'
sheet = gss_client.open_by_key(spreadsheets_key).sheet1
user_id = event.source.user_id
profile = line_bot_api.get_profile(user_id)
a = str(user_id)
flag = 0
for i in range(2, 30):
if (sheet.cell(i, 3).value) == a:
if (sheet.cell(i, 2).value) == "":
sheet.update_cell(i, 2,
profile.display_name)
flag = 1
else:
flag = 1
if (flag == 0):
timestamp = time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime())
a = len(mtext)
output = ""
for i in range(1, a):
output += mtext[i]
output = [timestamp, profile.display_name, user_id]
sheet.insert_row(output, 2)
return HttpResponse()
else:
return HttpResponseBadRequest()
