def save(self): plan_array = [*self.plan_array] plan_array = str(list(map(str, plan_array))) plan_array = plan_array.replace('[', '{').replace(']', '}').replace('\'', '\"') status_array = [*self.status_array] status_array = str(list(map(str, status_array))) status_array = status_array.replace('[', '{').replace(']', '}').replace( '\'', '\"') if Plan.get(self.id): execute_database_command( 'UPDATE plans SET user_id = %s, plan_array = %s, status_array = %s, date = %s WHERE id = %s', (self.user_id, plan_array, status_array, self.date, self.id)) else: plan_id = execute_database_command( 'INSERT INTO plans (user_id, plan_array, status_array, date) VALUES (%s, %s, %s, %s) RETURNING id;', (self.user_id, plan_array, status_array, self.date))[0][0] self.id = plan_id return self
def handle_completed_habits(bot, update): habit_ids = execute_database_command( f'SELECT id FROM habits WHERE user_id = {update.user_id}') completed_habits_report = {} for habit_id in habit_ids: habit_id = habit_id[0] habit = Habit.get(habit_id) if not habit.get_remaining_checks(): check_statuses = execute_database_command( f'SELECT status FROM checks WHERE habit_id = {habit_id} ORDER BY datetime_utc;' ) for check_status in check_statuses: check_status = check_status[0] if habit_id in completed_habits_report: completed_habits_report[habit_id]['checks'].append( status_icons[check_status]) else: completed_habits_report[habit_id] = \ {'label': habit.label, 'checks': [status_icons[check_status]]} if completed_habits_report: report = '' for habit in completed_habits_report.values(): report += f'*{habit["label"]}*\n{" ".join(habit["checks"])}\n\n' else: user = User.get(update.user_id) ru_report = 'Нет ни одной завершённой привычки' en_report = 'There are no completed habits' report = ru_report if user.language_code == 'ru' else en_report bot.send_message(update.user_id, report)
def handle_active_habits(bot, update): user = User.get(update.user_id) habit_ids = execute_database_command( f'SELECT id FROM habits WHERE user_id = {user.id}') active_habits_ids = [] for habit_id in habit_ids: habit_id = habit_id[0] habit = Habit.get(habit_id) if habit.get_remaining_checks(): active_habits_ids.append(habit_id) check_statuses = execute_database_command( f'SELECT status FROM checks WHERE habit_id = {habit_id} ORDER BY datetime_utc;' ) check_report = '' for check_status in check_statuses: check_report += status_icons[check_status[0]] # TODO убрать дублирование кода с Intro ru_days = ['Пн', 'Вт', 'Ср', 'Чт', 'Пт', 'Сб', 'Вс'] en_days = ['Mo', 'Tu', 'We', 'Th', 'Fr', 'Sa', 'Su'] days = ru_days if user.language_code == 'ru' else en_days days_of_week = list( map(lambda x: int(x), habit.days_of_week[1:-1].split(','))) time_array = list( map(lambda x: x.strip(), habit.time_array[1:-1].split(','))) check_days = re.sub( r'\s+', ' ', ' '.join([ day if day_of_week in days_of_week else '' for day_of_week, day in enumerate(days) ])).strip() check_time = ' '.join(time_array) ru_text = f'Привычка: *{habit.label}*\n' \ f'Дни недели: *{check_days}*\n' \ f'Время проверки: *{check_time}*\n' \ f'Длительность: *3 недели*\n\n' \ f'Прогресс\n' en_text = f'Habit: *{habit.label}*\n' \ f'Days of week: *{check_days}*\n' \ f'Checks time: *{check_time}*\n' \ f'Duration: *3 weeks*\n\n' \ f'Progress\n' text = ru_text if user.language_code == 'ru' else en_text text += check_report bot.send_message(update.user_id, text, keyboard=keyboards.get_delete_habit_keyboard( user, habit_id)) if not active_habits_ids: ru_text = 'Нет ни одной активной привычки' en_text = 'There are no active habits' text = ru_text if user.language_code == 'ru' else en_text bot.send_message(update.user_id, text)
def get_fines(self, judge=None): # TODO попробовать переписать красиво if judge: res = execute_database_command( 'SELECT h.label, c.datetime_native, h.fine FROM checks c JOIN habits h ON c.habit_id = h.id WHERE c.status = %s AND h.user_id = %s AND h.judge=%s;', (CheckStatus.FAIL.name, self.id, judge))[0] else: res = execute_database_command( 'SELECT h.label, c.datetime_native, h.fine FROM checks c JOIN habits h ON c.habit_id = h.id WHERE c.status = %s AND h.user_id = %s AND h.judge IS NULL;', (CheckStatus.FAIL.name, self.id))[0] return res
def satisfy_fines(self, satisfaction_type, judge=None): # TODO попробовать переписать красиво if judge: execute_database_command( 'UPDATE checks SET status = %s FROM habits WHERE habits.id = checks.habit_id AND habits.user_id = %s AND checks.status = %s AND habits.judge = %s;', (satisfaction_type, self.id, CheckStatus.FAIL.name, judge)) else: execute_database_command( 'UPDATE checks SET status = %s FROM habits WHERE habits.id = checks.habit_id AND habits.user_id = %s AND checks.status = %s AND habits.judge IS NULL;', (satisfaction_type, self.id, CheckStatus.FAIL.name))
def handle_delete_reminder(bot, update): user = User.get(update.user_id) reminder = Reminder.get(update.cmd_args['reminder_id']) if reminder: execute_database_command( f'DELETE FROM reminders WHERE id = {reminder.id};') bot.delete_message(update.message.body.mid) else: ru_text = f'Такого напоминания не существует' en_text = f'This reminder does not exists' text = ru_text if user.language_code == 'ru' else en_text bot.send_message(update.user_id, text)
def save(self): if User.get(self.id): execute_database_command( 'UPDATE users SET username = %s, first_name = %s, last_name = %s, timezone = %s, language_code = %s, is_active = %s, referrer=%s, score=%s WHERE id = %s', (self.username, self.first_name, self.last_name, self.timezone, self.language_code, self.is_active, self.referrer, self.score, self.id)) else: execute_database_command( 'INSERT INTO users (id, username, first_name, last_name, timezone, language_code, is_active, referrer, score) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)', (self.id, self.username, self.first_name, self.last_name, self.timezone, self.language_code, self.is_active, self.referrer, self.score))
def handle_delete_habit(bot, update): user = User.get(update.user_id) habit = Habit.get(update.cmd_args['habit_id']) if habit: execute_database_command(f'DELETE FROM habits WHERE id = {habit.id};') ru_text = f'Привычка *{habit.label}* удалена' en_text = f'The habit *{habit.label}* has been deleted' text = ru_text if user.language_code == 'ru' else en_text else: ru_text = f'Такой привычки не существует' en_text = f'This habit does not exists' text = ru_text if user.language_code == 'ru' else en_text bot.send_message(update.user_id, text)
def save(self): if Habit.get(self.id): execute_database_command( 'UPDATE habits SET user_id = %s, label = %s, question = %s, days_of_week = %s, time_array = %s, fine = %s, judge=%s WHERE id = %s', (self.user_id, self.label, self.question, self.days_of_week, self.time_array, self.fine, self.judge, self.id)) else: habit_id = execute_database_command( 'INSERT INTO habits (user_id, label, question, days_of_week, time_array, fine, judge) VALUES (%s, %s, %s, %s, %s, %s, %s) RETURNING id;', (self.user_id, self.label, self.question, self.days_of_week, self.time_array, self.fine, self.judge))[0][0][0] self.id = habit_id return self
def save(self): if Check.get(self.id): execute_database_command( f'UPDATE checks SET ' f'habit_id = %s,' f'''datetime_native = '{self.datetime_native}',''' f'''datetime_utc = '{self.datetime_utc}',''' f'status = %s WHERE id = %s', (self.habit_id, self.status, self.id)) else: check_id = execute_database_command( 'INSERT INTO checks (habit_id, datetime_native, datetime_utc, status) ' f'''VALUES (%s, '{self.datetime_native}', '{self.datetime_utc}', %s) RETURNING id;''', (self.habit_id, self.status))[0][0] return Check(self.habit_id, self.datetime_native, self.datetime_utc, self.status, check_id)
def get(check_id): try: id, habit_id, datetime_native, datetime_utc, status = \ execute_database_command('SELECT * FROM checks WHERE id=%s', (check_id,))[0] return Check(habit_id, datetime_native, datetime_utc, status, id) except IndexError: return None
def handle_my_reminders(bot, update): user = User.get(update.user_id) now_utc = datetime.strptime(datetime.utcnow().strftime("%Y-%m-%d %H:%M"), "%Y-%m-%d %H:%M") # TODO Нужно исправить reminders = execute_database_command( f'SELECT id, mid, datetime_native FROM reminders WHERE ' f'datetime_utc > %s AND user_id = %s', (now_utc, user.id)) for reminder in reminders: reminder_id = reminder[0] mid = reminder[1] date = reminder[2].strftime("%d.%m.%Y") time = reminder[2].strftime("%H:%M") print(reminder_id) text = f'{date} в {time}' if user.language_code != 'ru': text = 'On ' + text.replace('в', 'at') bot.reply_message(user.id, mid, text, keyboard=keyboards.get_delete_reminder_keyboard( user, reminder_id)) if not reminders: ru_text = 'Нет ни одного напоминания' en_text = 'There are no any reminders' text = ru_text if user.language_code == 'ru' else en_text bot.send_message(update.user_id, text)
def get(reminder_id): try: id, user_id, mid, datetime_native, datetime_utc = execute_database_command( 'SELECT * FROM reminders WHERE id=%s', (reminder_id, ))[0] return Reminder(user_id, mid, datetime_native, datetime_utc, id) except IndexError: return None
def get(habit_id): try: id, user_id, label, question, days_of_week, time_array, fine, judge = execute_database_command( 'SELECT * FROM habits WHERE id=%s', (habit_id, ))[0][0] return Habit(user_id, label, days_of_week, time_array, fine, id, question, judge) except IndexError: return None
def save(self): if Reminder.get(self.id): execute_database_command( f'UPDATE reminders SET ' f'user_id = %s,' f'mid = %s,' f'''datetime_native = '{self.datetime_native}',''' f'''datetime_utc = '{self.datetime_utc}',''' f'WHERE id = %s', (self.user_id, self.mid, self.id)) else: reminder_id = execute_database_command( 'INSERT INTO reminders (user_id, mid, datetime_native, datetime_utc) ' f'''VALUES (%s, %s, '{self.datetime_native}', '{self.datetime_utc}') RETURNING id;''', (self.user_id, self.mid))[0][0] self.id = reminder_id return self
def score_users(): success_checks = execute_database_command( '''SELECT h.user_id, h.fine FROM checks c JOIN habits h ON c.habit_id = h.id WHERE c.status=%s; ''', (CheckStatus.SUCCESS.name, ))[0] for success_check in success_checks: u = User.get(success_check[0]) u.score += success_check[1] u.save() fail_checks = execute_database_command( '''SELECT h.user_id, h.fine FROM checks c JOIN habits h ON c.habit_id = h.id WHERE c.status=%s; ''', (CheckStatus.FAIL.name, ))[0] for fail_check in fail_checks: u = User.get(fail_check[0]) u.score -= fail_check[1] u.save() # def send_info_to_users(): # users = execute_database_command('SELECT id, first_name, language_code FROM users;')[0] # # for user in users: # ru_text = f'Привет{", " + user[1] if user[1] else ""}!\n\n' \ # f'Я решил больше не церемониться. Теперь разговариваю дерзко и на "ты". ' \ # f'Может, хотя бы это поможет тебе побороть свою лень. ' \ # f'А ещё я теперь отправляю стикеры.\n\n' \ # f'Короче, я теперь не унылый стандартный бот.' # en_text = f'Hello{", " + user[1] if user[1] else ""}!\n\n' \ # f'I decided to no longer stand on ceremony. Now I speak impertinently. ' \ # f'Maybe at least it will help you overcome your laziness. ' \ # f'And now I send stickers.\n\n' \ # f'In short, I am no longer a dull standard bot.' # text = ru_text if user[2] == 'ru' else en_text # # try: # bot.send_message(user[0], text, parse_mode='Markdown') # bot.send_sticker(user[0], 'CAADAgADcgADE-ZSAdekz3zzPdFUAg') # except Exception as e: # print(e)
def get(plan_id): try: id, user_id, plan_array, status_array, date = execute_database_command( 'SELECT * FROM plans WHERE id=%s', (plan_id, ))[0] plan_array = ast.literal_eval( plan_array.replace('{', '[').replace('}', ']')) status_array = ast.literal_eval( status_array.replace('{', '[').replace('}', ']')) return Plan(user_id, plan_array, status_array, date, id) except IndexError: return None
def score_users(): success_checks = execute_database_command( '''SELECT h.user_id, h.fine FROM checks c JOIN habits h ON c.habit_id = h.id WHERE c.status=%s; ''', (CheckStatus.SUCCESS.name, ))[0] for success_check in success_checks: u = User.get(success_check[0]) u.score += success_check[1] u.save() fail_checks = execute_database_command( '''SELECT h.user_id, h.fine FROM checks c JOIN habits h ON c.habit_id = h.id WHERE c.status=%s; ''', (CheckStatus.FAIL.name, ))[0] for fail_check in fail_checks: u = User.get(fail_check[0]) u.score -= fail_check[1] u.save()
def today_plan(self): today = datetime.datetime.now(tz=pytz.timezone(self.timezone)).date() try: id, user_id, plan_array, status_array, date = \ execute_database_command('SELECT * FROM plans WHERE user_id=%s AND date=%s', (self.id, today))[0] plan_array = ast.literal_eval( plan_array.replace('{', '[').replace('}', ']')) status_array = ast.literal_eval( status_array.replace('{', '[').replace('}', ']')) return Plan(user_id, plan_array, status_array, date, id) except: return None
def fill_database_with_test_data(): execute_database_command(f'''INSERT INTO users (id, username, first_name, last_name, timezone, language_code, is_active) VALUES ( {settings.ADMIN_ID}, 'Ivan', '', '', 'Europe/Moscow', 'ru', TRUE )''') execute_database_command(f'''INSERT INTO habits (user_id, label, question, days_of_week, time_array, fine) VALUES ( {settings.ADMIN_ID}, 'Не залипать', '', '[0, 1]', '["7:30", "19:30"]', 5 );''') for i in range(30): for j in range(1): datetime_native = pytz.timezone('Europe/Moscow').localize(datetime.now() + timedelta(minutes=i)) datetime_utc = datetime_native.astimezone(pytz.UTC).strftime("%Y-%m-%d %H:%M") datetime_native = datetime_native.strftime("%Y-%m-%d %H:%M") execute_database_command(f'''INSERT INTO checks (habit_id, datetime_native, datetime_utc, status) VALUES ( 1, '{datetime_native}', '{datetime_utc}', '{CheckStatus.PENDING.name}' );''')
def rate_users(): rating = execute_database_command( 'SELECT id, score FROM users ORDER BY score DESC;', (CheckStatus.SUCCESS.name, )) total = len(rating) for place, record in enumerate(rating, 1): u = User.get(record[0]) ru_text = f'Твоё место в рейтинге: *{place}/{total}*\n\n' \ f'Количество очков: *{record[1]}*' en_text = f'Your place in rating *{place}/{total}*\n\n' \ f'Score: *{record[1]}*' text = ru_text if u.language_code == 'ru' else en_text bot.send_message(u.id, text)
def motivate_users_with_Jason_Statham(): users = execute_database_command('SELECT id, language_code FROM users;')[0] for user in users: user_id = user[0] language_code = user[1] quote = random.choice(Jason_Statham_quotes_ru) + '\n\n*Джейсон Стэтхэм*' if language_code == 'ru' else \ random.choice(Jason_Statham_quotes_en) + '\n\n*Jason Statham*' sticker = random.choice(Jason_Statham_sticker_pack) try: bot.send_message(user_id, quote, parse_mode='Markdown') bot.send_sticker(user_id, sticker) except Exception: pass
def check_users(last_check_utc): now_utc = datetime.strptime(datetime.utcnow().strftime("%Y-%m-%d %H:%M"), "%Y-%m-%d %H:%M") # Нужно исправить checks = execute_database_command( '''SELECT c.id, c.habit_id, c.datetime_native, c.datetime_utc, h.label, h.user_id, h.judge FROM checks c JOIN habits h ON c.habit_id = h.id JOIN users u ON u.id = h.user_id WHERE c.datetime_utc <= %s AND c.datetime_utc > %s AND c.status=%s; ''', (now_utc, last_check_utc, CheckStatus.PENDING.name))[0] for check in checks: check_id, habit_id, datetime_native, datetime_utc, label, user_id, judge_id = check c = Check(habit_id, datetime_native, datetime_utc, CheckStatus.CHECKING.name, check_id) c.save() user = User.get(user_id) ru_text = f'Ты обещал "*{label}*". Ты держишь своё слово?\n\n' \ f'Учти, что за ответ "❌ Нет" нужно будет заплатить штраф' en_text = f'You promised "*{label}*". Are you keeping your promise?\n\n' \ f'Note that you will have to pay a fine for the answer "❌ No"' text = ru_text if user.language_code == 'ru' else en_text try: bot.send_message(user_id, text, reply_markup=markups.get_check_inline_markup( user_id, check_id), parse_mode='Markdown') except Exception: pass if judge_id: judge = User.get(judge_id) ru_text_judge = f'{get_user_naming(user, "Твой друг")} обещал "*{label}*"' en_text_judge = f'{get_user_naming(user, "Your friend")} promised "*{label}*"' text_judge = ru_text_judge if judge.language_code == 'ru' else en_text_judge try: bot.send_message(judge_id, text_judge, reply_markup=markups.get_kick_lazy_ass_markup( judge_id, habit_id), parse_mode='Markdown') except Exception as e: print(e) return now_utc
def remind_users(last_remind_utc): now_utc = datetime.strptime(datetime.utcnow().strftime("%Y-%m-%d %H:%M"), "%Y-%m-%d %H:%M") # TODO Нужно исправить reminders = execute_database_command( '''SELECT user_id, mid FROM reminders WHERE datetime_utc <= %s AND datetime_utc > %s; ''', (now_utc, last_remind_utc)) for reminder in reminders: user_id, mid = reminder user = User.get(user_id) bot.send_message( user_id, 'Ты просил напомнить' if user.language_code == 'ru' else 'You asked to remind you') bot.forward_message(user_id, mid) return now_utc
def take_points_from_debtors(): debtors = execute_database_command( '''SELECT u.id, SUM(h.fine) FROM users u JOIN habits h ON u.id = h.user_id JOIN checks c ON c.habit_id = h.id WHERE c.status = 'FAIL' GROUP BY u.id; ''', (CheckStatus.SUCCESS.name, ))[0] for debtor in debtors: user = User.get(debtor[0]) user.score -= debtor[1] user.save() ru_text = f'Должникам по долгам их!\n\nТвой долг по штрафам составляет *${debtor[1]}*\n\n*-{debtor[1]} очков*' en_text = f'Debtors must be punished!\n\nYour debt on fines *${debtor[1]}*\n\n*-{debtor[1]} points*' text = ru_text if user.language_code == 'ru' else en_text try: bot.send_message(user.id, text, parse_mode='Markdown') except Exception: pass # TODO убрать дублирование с user_violations violations = user.get_fines() if violations: ru_report = 'Ты обвиняешься в следующих нарушениях:\n\n' en_report = 'You are charged with the following violations:\n\n' report = ru_report if user.language_code == 'ru' else en_report for violation in violations: label = violation[0] datetime_native = violation[1] fine = violation[2] report += f'_{datetime_native}_ {label} *${fine}*\n\n' report += 'Выбери наказание' if user.language_code == 'ru' else 'Choose punishment' try: bot.send_message(user.id, text=report, parse_mode='Markdown', reply_markup=get_punishment_markup(user.id)) except: pass
def send_info_to_users(): users = execute_database_command( 'SELECT id, first_name, language_code FROM users;')[0] for user in users: ru_text = f'Привет{", " + user[1] if user[1] else ""}!\n\n' \ f'Я решил больше не церемониться. Теперь разговариваю дерзко и на "ты". ' \ f'Может, хотя бы это поможет тебе побороть свою лень. ' \ f'А ещё я теперь отправляю стикеры.\n\n' \ f'Короче, я теперь не унылый стандартный бот.' en_text = f'Hello{", " + user[1] if user[1] else ""}!\n\n' \ f'I decided to no longer stand on ceremony. Now I speak impertinently. ' \ f'Maybe at least it will help you overcome your laziness. ' \ f'And now I send stickers.\n\n' \ f'In short, I am no longer a dull standard bot.' text = ru_text if user[2] == 'ru' else en_text try: bot.send_message(user[0], text, parse_mode='Markdown') bot.send_sticker(user[0], 'CAADAgADcgADE-ZSAdekz3zzPdFUAg') except Exception as e: print(e)
def check_users(last_check_utc): now_utc = datetime.strptime(datetime.utcnow().strftime("%Y-%m-%d %H:%M"), "%Y-%m-%d %H:%M") # TODO Нужно исправить checks = execute_database_command( '''SELECT c.id, c.habit_id, c.datetime_native, c.datetime_utc, h.label, h.user_id FROM checks c JOIN habits h ON c.habit_id = h.id JOIN users u ON u.id = h.user_id WHERE c.datetime_utc <= %s AND c.datetime_utc > %s AND c.status=%s; ''', (now_utc, last_check_utc, CheckStatus.PENDING.name)) for check in checks: check_id, habit_id, datetime_native, datetime_utc, label, user_id = check c = Check(habit_id, datetime_native, datetime_utc, CheckStatus.CHECKING.name, check_id) c.save() user = User.get(user_id) ru_text = f'Ты обещал *{label}*. Ты держишь своё слово?' en_text = f'You promised *{label}*. Are you keeping your promise?' text = ru_text if user.language_code == 'ru' else en_text bot.send_message(user_id, text, keyboard=keyboards.get_check_keyboard(user, check_id)) return now_utc
def get_remaining_checks(self): return execute_database_command( 'SELECT id FROM checks WHERE habit_id = %s AND (status = %s OR status = %s)', (self.id, CheckStatus.PENDING.value, CheckStatus.CHECKING.value))[0]
def get(telegram_id): try: id, username, first_name, last_name, timezone, language_code, is_active, referrer, score = execute_database_command( 'SELECT * FROM users WHERE id=%s', (telegram_id, ))[0][0] return User(id, username, first_name, last_name, timezone, language_code, is_active, referrer, score) except IndexError: return None