DGAWNvkM

1import sqlite3
2import telegram
3import datetime
4from telegram.ext import Updater, CommandHandler, MessageHandler, Filters, CallbackQueryHandler
5from telegram import ReplyKeyboardMarkup, InlineKeyboardButton, InlineKeyboardMarkup
6
7TOKEN = '629744308:AAE_dJDCdpla_vgd77UftCWZ7CiTh-MHKds'
8YES = 'Yes'
9NO = 'No'
10myDB = '/tmp/quarrelsDB'
11LAST_MONTH = 'Last Month'
12THIS_MONTH = 'This Month'
13OVERALL = 'Overall'
14
15bot = telegram.Bot(token=TOKEN)
16updater = Updater(token=TOKEN)
17jobq = updater.job_queue
18dispatcher = updater.dispatcher
19
20reply_keyboard = [[YES, NO]]
21markup = ReplyKeyboardMarkup(reply_keyboard, one_time_keyboard=True, resize_keyboard=True)
22
23
24# function sends welcome message and executes JobQueue thread
25def start(bot, update):
26    bot.send_message(chat_id=update.message.chat_id, text='Hello, I will collect information about your quarrels.\n'
27                                                          'I will write to you every day at 20-00 MSK and ask some questions\n'
28                                                          ': )  cya!\n'
29                                                          'write /help to see all commands\n')
30    setup_database()
31    q = datetime.time(hour=22, minute=00)
32    jobq.run_daily(callback=daily_question_func, time=q, context=(update.message.chat_id,))
33
34
35# function asks about fight and show a keyboard with answers
36def daily_question_func(bot, job):
37    bot.send_message(chat_id=job.context[0], text='Did you fight today?',
38                     reply_markup=markup)
39
40
41# message handler manages incoming messages, check existence of data and adds information in DB
42def fight_handler(bot, update):
43    updtext = update.message.text
44    updchatid = update.message.chat_id
45    x = datetime.datetime.now()
46    x = datetime.datetime.strftime(x, '%y-%m-%d')
47    tmplogin = update.message.chat.username
48    if not check_existence(updchatid, x):
49        if updtext == YES:
50            bot.send_message(chat_id=updchatid, text='What happened?')
51        elif updtext == NO:
52            insert_into_bd(date=x, quarrel=0, id=updchatid, reason='NULL', login=tmplogin)
53            bot.send_message(chat_id=updchatid, text='Glad to hear it! Keep It up!',
54                             reply_markup=telegram.ReplyKeyboardRemove())
55        else:
56            bot.send_message(chat_id=updchatid, text="Damn. So sad.\nI hope It won't happen again",
57                             reply_markup=telegram.ReplyKeyboardRemove())
58            insert_into_bd(date=x, quarrel=1, id=updchatid, reason=updtext, login=tmplogin)
59    else:
60        bot.send_message(chat_id=updchatid, text="You've sent a report already or just w8 when I'll ask you : - )")
61
62
63# function inserts data in DB
64def insert_into_bd(date, quarrel, id, reason, login):
65    with sqlite3.connect(myDB) as conn:
66        c = conn.cursor()
67        c.execute('INSERT INTO quarrels (date, quarrel, id, reason, login) VALUES (?, ?, ?, ?, ?)',
68                  (date, quarrel, id, reason, login))
69        conn.commit()
70
71
72# fucntion checks existence of some data in my DB
73def check_existence(id, date):
74    with sqlite3.connect(myDB) as conn:
75        c = conn.cursor()
76        c.execute("SELECT COUNT(*) FROM quarrels WHERE id=? AND date=?", (id, date))
77        ret = c.fetchone()[0] > 0
78        return ret
79
80
81# help command handler
82def help(bot, update):
83    bot.send_message(chat_id=update.message.chat_id, text='/statistic displays information about the quarrels')
84
85
86# fucntion gets statistic for specific date range
87def get_statistic(id, datefrom, dateto):
88    count = 0
89    with sqlite3.connect(myDB) as conn:
90        c = conn.cursor()
91        c.execute("SELECT date, reason FROM quarrels WHERE id=? AND quarrel=1 AND date>=? AND date<=?",
92                  (id, datefrom, dateto))
93        bot.send_message(chat_id=id, text="Here's your statistic between "
94                                          + datefrom + " and " + dateto + ":\n_________________________")
95        for row in c:
96            count += 1
97            bot.send_message(chat_id=id, text='You had an argue on ' + row[0] + ' with reason: ' + row[1])
98        bot.send_message(chat_id=id, text='_________________________\nOverall between ' + datefrom
99                                          + ' and ' + dateto + ' you had ' + str(count) + ' quarrels',
100                         reply_markup=telegram.ReplyKeyboardRemove())
101
102
103# statistic command handler shows inline keyboard to user to get date range
104def statistic(bot, update):
105    keyboard = [[InlineKeyboardButton("Last month", callback_data=LAST_MONTH),
106                 InlineKeyboardButton("This month", callback_data=THIS_MONTH)],
107                [InlineKeyboardButton("Overall", callback_data=OVERALL)]]
108
109    reply_markup = InlineKeyboardMarkup(keyboard)
110
111    update.message.reply_text('Select data range:', reply_markup=reply_markup)
112
113
114# command quqery handler manages inline keyboard output data and executes get_statistic function with correct dates
115def callbackquery(bot, update):
116    query = update.callback_query
117    id = query.message.chat.id
118    dnow = datetime.datetime.strftime(datetime.datetime.today(), '%y-%m-%d')
119    dfirstdayofmnth = datetime.datetime.now().replace(day=1)
120    dfirstdayofthemonth = datetime.datetime.strftime(dfirstdayofmnth, '%y-%m-%d')
121    dlastmnthlastday = dfirstdayofmnth - datetime.timedelta(days=1)
122    dlastmnthfirstday = dlastmnthlastday.replace(day=1)
123    firstdaylm = datetime.datetime.strftime(dlastmnthfirstday, '%y-%m-%d')
124    lastdaylm = datetime.datetime.strftime(dlastmnthlastday, '%y-%m-%d')
125    query_res = update.callback_query
126    if query_res.data == OVERALL:
127        get_statistic(id, '19-01-01', dnow)
128    elif query_res.data == THIS_MONTH:
129        get_statistic(id, dfirstdayofthemonth, dnow)
130    elif query_res.data == LAST_MONTH:
131        get_statistic(id, firstdaylm, lastdaylm)
132
133
134def setup_database():
135    with sqlite3.connect(myDB) as conn:
136        query = "CREATE TABLE IF NOT EXISTS quarrels " \
137                "('date' text, 'quarrel' blob,'id' integer,'reason' text,'login' text)"
138        c = conn.cursor()
139        c.execute(query)
140        conn.commit()
141
142
143stat_handler = CommandHandler('statistic', statistic)
144dispatcher.add_handler(MessageHandler(Filters.text, fight_handler))
145help_handler = CommandHandler('help', help)
146start_handler = CommandHandler('start', start)
147dispatcher.add_handler(CallbackQueryHandler(callback=callbackquery))
148dispatcher.add_handler(stat_handler)
149dispatcher.add_handler(help_handler)
150dispatcher.add_handler(start_handler)
151updater.start_polling()