def getUser(data):
sql = '''
select nme, count(*) as posts, sum(len)
, sum(case type when 't' then 1 else 0 end) as t
, sum(case type when 'a' then 1 else 0 end) as a
, sum(case type when 's' then 1 else 0 end) as s
, sum(case type when 'p' then 1 else 0 end) as p
, sum(case type when 'v' then 1 else 0 end) as v
, sum(case type when 'i' then 1 else 0 end) as i
, sum(case type when 'p' then 1 else 0 end) as o
, sum(case type when 'd' then 1 else 0 end) as d
, sum(case type when 'c' then 1 else 0 end) as c
, sum(case type when 'N' then 1 else 0 end) as n
from msglog l
left join user_name u on u.user_id=l.user_id
where l.chat_id=%(chat_id)s and l.date between %(start)s and %(end)s
group by nme, u.user_id order by posts desc;'''
users = []
for r in tele_util.readSQL(sql, data=data):
tele_util.typ3s
types = {}
for idx,(_,name) in enumerate(tele_util.typ3s.items()):
if r[idx+3]!=0:
types[name] = int(r[idx+3])
user = {'name': r[0], 'posts': r[1],'textlen': r[2],'type': types,}
users.append(user)
return users
def getDailyPost(tags):
sql = "select id, file_id, type from daylie_post where tags like '%s' and date_day='%s' limit 1" \
% (tags, time.strftime('%Y-%m-%d'))
rows = tele_util.readSQL(sql)
if rows:
return rows[0][2], rows[0][1]
else:
sql = """ select id, file_id, type from daylie_post where tags like '%s' and date_day=''
order by rand() limit 1""" % (tags)
rows = tele_util.readSQL(sql)
if len(rows) == 0:
return 'der tag ' + tags + ' ist aufgebraucht', 'm'
sql = "update daylie_post set date_day = '%s' where id=%s" % (
time.strftime('%Y-%m-%d'), rows[0][0])
tele_util.executeSQL(sql)
return rows[0][2], rows[0][1]
def quiz(msg):
sql = "select text, autor from ztt where chat_id=%s and text <> '' order by rand() limit 1"
text, autor = tele_util.readSQL(sql, data=[str(msg.getChatId())])[0]
sql = "select autor from ztt where chat_id=%s and autor!=%s and text<>'' group by autor order by rand() limit 5"
r = tele_util.readSQL(sql, data=[str(msg.getChatId()), autor])
a = [e[0] for e in r]
a.append(autor)
random.shuffle(a)
url = 'https://api.telegram.org/bot%s/sendpoll' % config.swagbot['api_key']
myobj = {
'chat_id': msg.getChatId(),
'question': text,
'options': a,
'correct_option_id': a.index(autor),
'type': 'quiz',
'is_anonymous': False
}
requests.post(url, json=myobj)
def getChart3(data):
sql = '''
select nme, sum(len)
from msglog l left join user_name u on u.user_id=l.user_id
where l.chat_id=%(chat_id)s and l.date between %(start)s and %(end)s
group by chat_id,nme;'''
chart3=[]
for r in tele_util.readSQL(sql, data=data):
chart3.append([r[0], r[1]])
return chart3
def getLinedata(data, users):
userlinedata={}
for u in users:
d={}
for i in range(0,24):
d['%02d' % i]=0
userlinedata[u['name']]=d
sql = '''
select u.nme, SUBSTRING(time from 1 for 2) as h, count(*) as c
from msglog l left join user_name u on u.user_id=l.user_id
where l.chat_id=%(chat_id)s and l.date between %(start)s and %(end)s
group by l.chat_id, l.user_id, SUBSTRING(time from 1 for 2) order by l.user_id;
'''
for row in tele_util.readSQL(sql, data=data):
nme, h, cnt = row
userlinedata.get(nme, {})[h] = cnt
linedata = []
for _,t in userlinedata.items():
line = []
for _, val in t.items():
line.append(val)
linedata.append(line)
return linedata
def getChart2(data):
sql = '''
select sum(case type when 't' then 1 else 0 end) as t
, sum(case type when 'a' then 1 else 0 end) as a
, sum(case type when 's' then 1 else 0 end) as s
, sum(case type when 'p' then 1 else 0 end) as p
, sum(case type when 'v' then 1 else 0 end) as v
, sum(case type when 'i' then 1 else 0 end) as i
, sum(case type when 'p' then 1 else 0 end) as o
, sum(case type when 'd' then 1 else 0 end) as d
, sum(case type when 'c' then 1 else 0 end) as c
, sum(case type when 'N' then 1 else 0 end) as n
from msglog
where chat_id=%(chat_id)s and date between %(start)s and %(end)s
group by chat_id;'''
chart2=[]
rows = tele_util.readSQL(sql, data=data)
if len(rows) == 0:
return []
r = rows[0]
for idx,(_,name) in enumerate(tele_util.typ3s.items()):
if r[idx] > 0:
chart2.append([name, r[idx]])
return chart2
#!/usr/bin/python3.6
from datetime import date, datetime
import holidays
import config
import tele_util
import lst
swagbot = tele_util.startBot(config.swagbot)
if date.today() in holidays.DE(years=date.today().year):
sql = "select chat_id from props where name='holidays'"
rows = tele_util.readSQLL(sql)
for r in rows:
swagbot.sendMessage(r[0],
'Heute haben wir frei => *' + u'\U0001F389' +
holidays.DE(years=2020)[date.today()] + '*' +
u'\U0001F389',
parse_mode='Markdown')
sql = "select chat_id, value from props where name='backlog/reminder'"
rows = tele_util.readSQL(sql)
doy = datetime.now().timetuple().tm_yday
for r in rows:
if doy % int(r[1]) == 0:
l = lst.getList(r[0], 'backlog')
if len(l) > 0:
swagbot.sendMessage(r[0], ('Es steht folgendes aus: %s' % l))
def getAllLists(chat_id):
sql = "select name from ls_entry where chat_id=%s group by name"
rows = tele_util.readSQL(sql, data=[chat_id])
return [r[0] for r in rows]
def getList(chat_id, name):
sql = 'select entry from ls_entry where chat_id=%(chat_id)s and name=%(name)s'
data = {'chat_id': chat_id, 'name': name}
rows = tele_util.readSQL(sql, data)
return [r[0] for r in rows]