def ranking(self, sc, data):
msgs = []
query = "select userid, count(*)-(select count(*) from emojiuse B where A.name=B.name and B.del=1) n from emojiuse A where del=0 group by userid order by n desc"
cur = db.execute(query)
result = cur.fetchall()
rank = 0
for row in result:
rank += 1
query = "select count(*)-(select count(*) from emojiuse B where A.name=B.name and B.del=1) n, name from emojiuse A where A.del=0 and A.userid=? group by name order by n desc"
cur = db.execute(query, [row[0]])
use = cur.fetchone()
n = use[0]
if n < 1:
continue
user = sc.getUserByID(row[0])
msg = str(rank) + "位:" + user['name'] + " " + str(
row[1]) + "回 よく使う絵文字:" + use[1] + ":"
while True:
use = cur.fetchone()
if use is None or use[0] != n:
break
msg += ":" + use[1] + ":"
msg += str(n) + "回"
msgs.append(msg)
sc.rtm_send_message(data['channel'], "\n".join(msgs))
return False
def process(self, sc, d):
print("reaction")
t = d['type']
name = d['reaction']
user = d['user']
if slack.getClient().myID == user:
#print("bot reaction")
return False
delete = 0
if t == 'reaction_removed':
delete = 1
cur = db.execute(
"SELECT COUNT(*) FROM " + self.tablename +
" WHERE name=? AND userid=? GROUP BY del ORDER BY del",
[name, user])
result = cur.fetchall()
count = 0
if len(result) >= 2:
count = result[0][0] - result[1][0]
elif len(result) > 0:
count = result[0][0]
if count <= 0 and delete == 1:
return False
cur = db.execute(
"INSERT INTO " + self.tablename +
" VALUES(?,CURRENT_TIMESTAMP,?,?)", [name, user, delete])
return False
def process(self, sc, data):
text = data['text']
channel = data['channel']
ts = data['ts']
user = data['user']
if text == self.wakeword:
cur = db.execute(
"SELECT text FROM A_WORD_TODAY ORDER BY RANDOM() LIMIT 1")
result = cur.fetchone()
if result is None:
msg = "登録されていません"
else:
msg = result[0]
sc.rtm_send_message(channel, msg)
return False
if self.addPat.match(text):
text = self.addPat.sub("", text)
param = [user, text]
db.execute(
"INSERT INTO A_WORD_TODAY(insuser, instime, text) VALUES(?, CURRENT_DATE, ?)",
param)
sc.rtm_send_message(channel, '追加しました')
return False
return True
def __init__(self):
cur = db.execute(
"SELECT COUNT(*) FROM sqlite_master WHERE type='table' and name=?",
[self.tablename])
count = cur.fetchone()[0]
print(count)
if count == 0:
print("newtable created")
db.execute("CREATE TABLE " + self.tablename +
"(name, datetime, userid, del)")
def __init__(self):
cur = db.execute(
"SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?",
[self.tableName])
count = cur.fetchone()[0]
if count == 0:
print("table created: " + self.tableName)
db.execute("CREATE TABLE " + self.tableName +
"(insuser, instime, text)")
self.addPat = re.compile(r"^" + self.wakeword + "追加 ")
def process(self, sc, data):
text = data['text']
match = re.match(r"^query (.+)", text)
if match is None:
return True
query = match.group(1)
if re.match(r"^select ", query.lower()) is None:
sc.rtm_send_message(data['channel'], "SELECTのみ許可されています")
return False
try:
cur = db.execute(query)
except sqlite3.OperationalError as e:
sc.rtm_send_message(data['channel'], str(e))
return False
result = cur.fetchall()
msgs = []
msg = []
for des in cur.description:
msg.append(des[0])
msgs.append("\t".join(msg))
msg = ''
for row in result:
row = [str(e) for e in row]
msg = "\t".join(row)
msgs.append(str(msg))
msg = "\n".join(msgs)
if len(result) == 0:
msg = "選択されませんでした"
sc.rtm_send_message(data['channel'], msg)
return False
def process(self, sc, data):
text = data['text']
if re.match(r"emojistatus\s+ranking$", text):
return self.ranking(sc, data)
mat = self.cmdreg.match(text)
if mat is None:
return True
#print(mat.groupdict())
wherelist = []
paramlist = []
order = "desc"
d = mat.groupdict()
if d['emoji'] is not None:
#絵文字のリストを取得する
pass
if d['user'] is not None:
w = "userid=?"
wherelist.append(w)
paramlist.append(d['user'])
if d['fromd'] is not None or d['tod'] is not None:
f = d['fromd']
t = d['tod']
if f is not None and t is not None:
w = "date(datetime, 'localtime') between ? and ?"
wherelist.append(w)
paramlist.append(f.replace("/", "-"))
paramlist.append(t.replace("/", "-"))
elif f is None:
w = "date(datetime, 'localtime') <= ?"
wherelist.append(w)
paramlist.append(t.replace("/", "-"))
else:
w = "date(datetime, 'localtime') >= ?"
wherelist.append(w)
paramlist.append(f.replace("/", "-"))
if d['date'] is not None:
w = "date(datetime, 'localtime') = ?"
wherelist.append(w)
paramlist.append(d['date'].replace("/", "-"))
if d['order'] is not None:
order = d['order']
if len(wherelist) != 0:
where = " and " + " and ".join(wherelist)
else:
where = ''
paramlist = []
query = "select date(min(datetime), 'localtime'), date(max(datetime), 'localtime') from emojiuse where del=0" + where
cur = db.execute(query, paramlist)
period = cur.fetchone()
query = "select name, count(*)-(select count(*) from emojiuse B where A.name=B.name and B.del=1) n from emojiuse A where del=0" + where + " group by name having n<>0 order by n " + order + " limit " + str(
self.listMaxCount)
#print(query)
#print(paramlist)
cur = db.execute(query, paramlist)
msgs = []
if period is not None:
msgs.append("集計期間:" + period[0] + "~" + period[1])
mx = 0
result = cur.fetchall()
for row in result:
if row[1] > mx:
mx = row[1]
mx = len(str(mx))
n = 0
for row in result:
if row[1] < 1:
continue
if row[1] == n:
msgs[-1] += ":" + row[0] + ":"
continue
n = row[1]
msg = str(row[1]).zfill(mx) + "回::" + row[0] + ":"
msgs.append(msg)
msg = "\n".join(msgs)
if msg == '':
msg = "該当するものはありませんでした"
sc.rtm_send_message(data['channel'], msg)
return False