def confirm_choice(message): from bot.handlers.check_first import start_handler if message.text == "Все верно": sql_con = functions.get_connection() cursor = sql_con.cursor() cursor.execute( """SELECT student_group_id FROM user_choice WHERE user_id = %s""", (message.chat.id, )) group_id = cursor.fetchone()[0] user_id = message.chat.id cursor.close() sql_con.close() functions.add_new_user(user_id, group_id) answer = "Главное меню\n\n" \ "{0} - информация о боте\n" \ "{1} - оценить бота\n" \ "{2} - настройки\n" \ "{3} - электрички\n" \ "{4} - <b>редактор расписания</b>\n" \ "@Spbu4u_news - новости бота".format( emoji["info"], emoji["star"], emoji["settings"], emoji["suburban"], emoji["editor"] ) bot.send_message(message.chat.id, answer, reply_markup=main_keyboard, parse_mode="HTML") elif message.text == "Другая группа": sql_con = functions.get_connection() cursor = sql_con.cursor() cursor.execute( """SELECT admission_year_name FROM user_choice WHERE user_id = %s""", (message.chat.id, )) data = cursor.fetchone() cursor.close() sql_con.close() message.text = data[0] select_admission_year(message) return elif message.text == "Другой год": select_student_group(message) return elif message.text == "Другая программа": select_admission_year(message) return elif message.text == "Другая ступень": select_study_program_combination(message) return elif message.text == "Другое направление": start_handler(message) return
def select_study_level(message): from bot.handlers.check_first import start_handler answer = "" sql_con = functions.get_connection() cursor = sql_con.cursor() cursor.execute( """SELECT study_programs_json FROM user_choice WHERE user_id = %s""", (message.chat.id, )) data = cursor.fetchone()[0] cursor.close() sql_con.close() study_programs = loads(data) study_level_names = [] for study_program in study_programs: study_level_names.append(study_program["StudyLevelName"].strip()) if message.text in study_level_names: answer += "Укажи программу:" study_program_combinations_keyboard = ReplyKeyboardMarkup( resize_keyboard=True, one_time_keyboard=False) index = study_level_names.index(message.text) study_program_combinations = study_programs[index][ "StudyProgramCombinations"] for study_program_combination in study_program_combinations: study_program_combinations_keyboard.row( study_program_combination["Name"]) study_program_combinations_keyboard.row("Другая ступень") sql_con = functions.get_connection() cursor = sql_con.cursor() cursor.execute( """UPDATE user_choice SET study_level_name = %s WHERE user_id = %s""", (message.text, message.chat.id)) sql_con.commit() cursor.close() sql_con.close() bot.send_message(message.chat.id, answer, reply_markup=study_program_combinations_keyboard) set_next_step(message.chat.id, "select_study_program_combination") elif message.text == "Другое направление": start_handler(message) return else: answer += "Пожалуйста, укажи ступень:" bot.send_message(message.chat.id, answer) set_next_step(message.chat.id, "select_study_level")
def select_division(message): answer = "" sql_con = functions.get_connection() cursor = sql_con.cursor() cursor.execute( """SELECT divisions_json FROM user_choice WHERE user_id = %s""", (message.chat.id, )) data = cursor.fetchone() cursor.close() sql_con.close() divisions = loads(data[0]) division_names = [division["Name"].strip() for division in divisions] aliases = [division["Alias"].strip() for division in divisions] if message.text in division_names: answer += "Выбери ступень:" study_programs_keyboard = ReplyKeyboardMarkup(resize_keyboard=True, one_time_keyboard=False) index = division_names.index(message.text) alias = aliases[index] study_programs = get_program_levels(alias) for study_program in study_programs: study_programs_keyboard.row(study_program["StudyLevelName"]) study_programs_keyboard.row("Другое направление") data = dumps(study_programs) sql_con = functions.get_connection() cursor = sql_con.cursor() cursor.execute( """UPDATE user_choice SET alias = %s, division_name = %s, study_programs_json = %s WHERE user_id = %s""", (alias, message.text, data, message.chat.id)) sql_con.commit() cursor.close() sql_con.close() bot.send_message(message.chat.id, answer, reply_markup=study_programs_keyboard) set_next_step(message.chat.id, "select_study_level") else: answer += "Пожалуйста, укажи направление:" bot.send_message(message.chat.id, answer) set_next_step(message.chat.id, "select_division")
def set_next_step(user_id, next_step): sql_con = functions.get_connection() cursor = sql_con.cursor() cursor.execute( """UPDATE user_choice SET step = %s WHERE user_id = %s""", (next_step, user_id)) sql_con.commit() cursor.close() sql_con.close()
def get_step(user_id): sql_con = functions.get_connection() cursor = sql_con.cursor() cursor.execute( """SELECT step FROM user_choice WHERE user_id = %s""", (user_id, )) step = cursor.fetchone() cursor.close() sql_con.close() if step is None: return None else: return step[0]
def return_lesson_handler(call_back): lesson_id = call_back.data.split(" - ")[0] events = call_back.message.text.split("\n\n")[1:-1] lesson_title = "" for event in events: if event.split("\n")[0].split(": ")[1] == lesson_id: lesson_title = event.split("\n")[1].split(": ")[1] break sql_con = func.get_connection() cursor = sql_con.cursor() cursor.execute("""DELETE FROM skips WHERE user_id = %s AND lesson_id = %s""", (call_back.message.chat.id, lesson_id)) sql_con.commit() cursor.close() sql_con.close() answer = "<b>Занятие возвращено:</b>\n{0}".format(lesson_title) bot.edit_message_text(text=answer, chat_id=call_back.message.chat.id, message_id=call_back.message.message_id, parse_mode="HTML")
def change_template_group_handler(call_back): answer = "Группа успешно изменена на <b>{0}</b>" chosen_group_id = int(call_back.data) sql_con = func.get_connection() cursor = sql_con.cursor() cursor.execute( """SELECT title FROM groups_data WHERE id = %s""", (chosen_group_id, )) group_title = cursor.fetchone()[0] cursor.execute( """UPDATE user_data SET group_id = %s WHERE id = %s""", (chosen_group_id, call_back.message.chat.id)) sql_con.commit() cursor.close() sql_con.close() bot.edit_message_text(text=answer.format(group_title), chat_id=call_back.message.chat.id, message_id=call_back.message.message_id, parse_mode="HTML")
def schedule_sender(): sql_con = get_connection() cursor = sql_con.cursor() cursor.execute("""SELECT id FROM user_data WHERE sending = 1""") data = cursor.fetchall() cursor.close() sql_con.close() send_cnt = 0 err_cnt = 0 tomorrow_moscow_datetime = \ datetime.today() + timedelta(days=1) + server_timedelta tomorrow_moscow_date = tomorrow_moscow_datetime.date() for user_data in data: user_id = user_data[0] json_day = get_json_day_data(user_id, tomorrow_moscow_date) full_place = is_full_place(user_id) answer = create_schedule_answer(json_day, full_place, user_id) if "Выходной" in answer: continue try: answer = "Расписание на завтра:\n\n" + answer send_long_message(bot, answer, user_id) send_cnt += 1 except Exception as err: print(err) print(user_id) err_cnt += 1 continue return send_cnt, err_cnt
def start_handler(message): answer = "" if bot_name != "Spbu4UBot" and message.chat.id not in ids.values(): answer = "Это тестовый бот. Используйте @Spbu4UBot" bot.send_message(message.chat.id, answer) return if message.text == "/start": answer = "Приветствую!\n" elif message.text.split()[1].isdecimal(): answer = "Приветствую!\nДобавляю тебя в группу..." bot_msg = bot.send_message(message.chat.id, answer) try: group_id = int(message.text.split()[1]) except ValueError: answer = "Ошибка в id группы." bot.edit_message_text(answer, message.chat.id, bot_msg.message_id) message.text = "/start" start_handler(message) return try: res = spbu.get_group_events(group_id) except spbu.ApiException: answer = "Ошибка в id группы." bot.edit_message_text(answer, message.chat.id, bot_msg.message_id) message.text = "/start" start_handler(message) return group_title = res["StudentGroupDisplayName"][7:] func.add_new_user(message.chat.id, group_id, group_title) answer = "Готово!\nГруппа <b>{0}</b>".format(group_title) bot.edit_message_text(answer, message.chat.id, bot_msg.message_id, parse_mode="HTML") answer = "Главное меню\n\n" \ "{0} - информация о боте\n" \ "{1} - оценить бота\n" \ "{2} - настройки\n" \ "{3} - электрички\n" \ "{4} - <b>редактор расписания</b>\n" \ "@Spbu4u_news - новости бота".format(emoji["info"], emoji["star"], emoji["settings"], emoji["suburban"], emoji["editor"]) bot.send_message(chat_id=message.chat.id, text=answer, reply_markup=main_keyboard, parse_mode="HTML") return answer += "Загружаю список направлений..." bot_msg = bot.send_message(message.chat.id, answer) bot.send_chat_action(message.chat.id, "typing") answer = "Укажи свое направление:" divisions = spbu.get_study_divisions() division_names = [division["Name"] for division in divisions] divisions_keyboard = ReplyKeyboardMarkup(True, False) for division_name in division_names: divisions_keyboard.row(division_name) divisions_keyboard.row("Поддержка", "Завершить") data = dumps(divisions) sql_con = func.get_connection() cursor = sql_con.cursor() cursor.execute("""DELETE FROM user_choice WHERE user_id = %s""", (message.chat.id, )) sql_con.commit() cursor.execute( """INSERT INTO user_choice (user_id, divisions_json) VALUES (%s, %s)""", (message.chat.id, data)) sql_con.commit() cursor.close() sql_con.close() bot.edit_message_text(text="Готово!", chat_id=message.chat.id, message_id=bot_msg.message_id) bot.send_message(message.chat.id, answer, reply_markup=divisions_keyboard) reg_func.set_next_step(message.chat.id, "select_division")
def copy_from_db(): # FROM DB sql_con = sqlite3.connect("Bot.db") cursor = sql_con.cursor() try: # user choice cursor.execute("""SELECT * FROM user_choice""") user_choices = cursor.fetchall() # group data cursor.execute("""SELECT id, title FROM groups_data""") groups_data = cursor.fetchall() # user data cursor.execute("""SELECT id, group_id, is_teacher, full_place, sending, rate, home_station_code, univer_station_code FROM user_data""") users_data = cursor.fetchall() except sqlite3.OperationalError: return finally: cursor.close() sql_con.close() # TO DB sql_con = get_connection() cursor = sql_con.cursor() # user choice try: cursor.executemany( """INSERT INTO user_choice VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", user_choices) sql_con.commit() except pymysql.IntegrityError: sql_con.rollback() # group data try: cursor.executemany( """INSERT INTO groups_data (id, title) VALUES (%s, %s)""", groups_data) sql_con.commit() except pymysql.IntegrityError: sql_con.rollback() # user data try: cursor.executemany( """INSERT INTO user_data (id, group_id, is_teacher, full_place, sending, rate, home_station_code, univer_station_code) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)""", users_data) sql_con.commit() except pymysql.IntegrityError: sql_con.rollback() cursor.close() sql_con.close()
def create_sql(): sql_con = get_connection() cursor = sql_con.cursor() # user choice cursor.execute("""CREATE TABLE IF NOT EXISTS user_choice ( user_id INT PRIMARY KEY, divisions_json TEXT, alias TEXT, division_name TEXT, study_programs_json TEXT, study_level_name TEXT, study_program_combination_name TEXT, admission_year_name TEXT, student_groups_json TEXT, student_group_name TEXT, student_group_id INT, step VARCHAR(40) DEFAULT 'handle_start' NOT NULL )""") sql_con.commit() # group data cursor.execute("""CREATE TABLE IF NOT EXISTS groups_data ( id INT PRIMARY KEY NOT NULL, title TEXT NOT NULL )""") sql_con.commit() # user data cursor.execute("""CREATE TABLE IF NOT EXISTS user_data ( id INT PRIMARY KEY NOT NULL, group_id INT NOT NULL, is_teacher INT DEFAULT 0 NOT NULL, full_place INT DEFAULT 1 NOT NULL, sending INT DEFAULT 0 NOT NULL, rate INT DEFAULT 0 NOT NULL, home_station_code VARCHAR(8) DEFAULT 'c2' NOT NULL, univer_station_code VARCHAR(8) DEFAULT 's9603770' NOT NULL, CONSTRAINT user_data_groups_data_id_fk FOREIGN KEY (group_id) REFERENCES groups_data (id) )""") sql_con.commit() # lessons cursor.execute("""CREATE TABLE IF NOT EXISTS lessons ( id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(500) NOT NULL, types VARCHAR(250) NOT NULL, day VARCHAR(11) NOT NULL DEFAULT 'all', time VARCHAR(11) NOT NULL DEFAULT 'all', educators VARCHAR(200) NOT NULL DEFAULT 'all', UNIQUE (name, types, day, time, educators) )""") sql_con.commit() # skips cursor.execute("""CREATE TABLE IF NOT EXISTS skips ( lesson_id INT NOT NULL, user_id INT NOT NULL, CONSTRAINT skips_lesson_id_user_id_pk PRIMARY KEY (lesson_id, user_id), CONSTRAINT skips_user_data_id_fk FOREIGN KEY (user_id) REFERENCES user_data (id), CONSTRAINT skips_lessons_id_fk FOREIGN KEY (lesson_id) REFERENCES lessons (id) )""") sql_con.commit() # user groups cursor.execute("""CREATE TABLE IF NOT EXISTS user_groups ( group_id INT NOT NULL, user_id INT NOT NULL, CONSTRAINT user_groups_group_id_user_id_pk PRIMARY KEY (group_id, user_id), CONSTRAINT user_groups_user_data_id_fk FOREIGN KEY (user_id) REFERENCES user_data (id), CONSTRAINT user_groups_groups_data_id_fk FOREIGN KEY (group_id) REFERENCES groups_data (id) )""") sql_con.commit() # user educators cursor.execute("""CREATE TABLE IF NOT EXISTS user_educators ( user_id INT NOT NULL, lesson_id INT NOT NULL, CONSTRAINT user_educators_user_id_lesson_id_pk PRIMARY KEY (user_id, lesson_id), CONSTRAINT user_educators_user_data_id_fk FOREIGN KEY (user_id) REFERENCES user_data (id), CONSTRAINT user_educators_lessons_id_fk FOREIGN KEY (lesson_id) REFERENCES lessons (id) )""") sql_con.commit() # user_lessons cursor.execute("""CREATE TABLE IF NOT EXISTS user_lessons ( user_id INT NOT NULL, lesson_id INT NOT NULL, CONSTRAINT user_lessons_user_id_lesson_id_pk PRIMARY KEY (user_id, lesson_id), CONSTRAINT user_lessons_user_data_id_fk FOREIGN KEY (user_id) REFERENCES user_data (id), CONSTRAINT user_lessons_lessons_id_fk FOREIGN KEY (lesson_id) REFERENCES lessons (id) )""") sql_con.commit() cursor.close() sql_con.close()
def select_student_group(message): answer = "" sql_con = functions.get_connection() cursor = sql_con.cursor() cursor.execute( """SELECT student_groups_json FROM user_choice WHERE user_id = %s""", (message.chat.id, )) data = cursor.fetchone()[0] cursor.close() sql_con.close() student_groups = loads(data) student_group_names = [] for student_group in student_groups["Groups"]: student_group_names.append(student_group["StudentGroupName"].strip()) if message.text in student_group_names: index = student_group_names.index(message.text) student_group_id = student_groups["Groups"][index]["StudentGroupId"] sql_con = functions.get_connection() cursor = sql_con.cursor() cursor.execute( """UPDATE user_choice SET student_group_name = %s, student_group_id = %s WHERE user_id = %s""", (message.text, student_group_id, message.chat.id)) sql_con.commit() cursor.execute( """SELECT division_name, study_level_name, study_program_combination_name, admission_year_name, student_group_name FROM user_choice WHERE user_id = %s""", (message.chat.id, )) data = cursor.fetchone() cursor.close() sql_con.close() text = ">> " + "\n>> ".join(data) answer += "Подтверди выбор:\n" + "<b>" + text + "</b>" choice_keyboard = ReplyKeyboardMarkup(resize_keyboard=True, one_time_keyboard=False) buttons = [ "Все верно", "Другая группа", "Другой год", "Другая программа", "Другая ступень", "Другое направление" ] for button in buttons: choice_keyboard.row(button) bot.send_message(message.chat.id, answer, parse_mode="HTML", reply_markup=choice_keyboard) set_next_step(message.chat.id, "confirm_choice") elif message.text == "Другой год": sql_con = functions.get_connection() cursor = sql_con.cursor() cursor.execute( """SELECT study_program_combination_name FROM user_choice WHERE user_id = %s""", (message.chat.id, )) data = cursor.fetchone() cursor.close() sql_con.close() message.text = data[0] select_study_program_combination(message) return else: answer += "Пожалуйста, укажи группу:" bot.send_message(message.chat.id, answer) set_next_step(message.chat.id, "select_student_group")
def select_admission_year(message): answer = "" sql_con = functions.get_connection() cursor = sql_con.cursor() cursor.execute( """SELECT study_programs_json, study_level_name, study_program_combination_name FROM user_choice WHERE user_id = %s""", (message.chat.id, )) data = cursor.fetchone() cursor.close() sql_con.close() study_programs = loads(data[0]) study_level_name = data[1] study_program_combination_name = data[2] study_level_names = [] for study_program in study_programs: study_level_names.append(study_program["StudyLevelName"]) index = study_level_names.index(study_level_name) study_program_combinations = study_programs[index][ "StudyProgramCombinations"] study_program_combination_names = [] for study_program_combination in study_program_combinations: study_program_combination_names.append( study_program_combination["Name"]) index = study_program_combination_names.index( study_program_combination_name) admission_years = study_program_combinations[index]["AdmissionYears"] admission_year_names = [] for admission_year in admission_years: admission_year_names.append(admission_year["YearName"].strip()) if message.text in admission_year_names: answer += "Укажи группу:" index = admission_year_names.index(message.text) study_program_id = admission_years[index]["StudyProgramId"] student_groups = get_groups(study_program_id) student_group_names = [] for student_group in student_groups["Groups"]: student_group_names.append(student_group["StudentGroupName"]) student_groups_keyboard = ReplyKeyboardMarkup(resize_keyboard=True, one_time_keyboard=False) for student_group_name in student_group_names: student_groups_keyboard.row(student_group_name) student_groups_keyboard.row("Другой год") data = dumps(student_groups) sql_con = functions.get_connection() cursor = sql_con.cursor() cursor.execute( """UPDATE user_choice SET admission_year_name = %s, student_groups_json = %s WHERE user_id = %s""", (message.text, data, message.chat.id)) sql_con.commit() cursor.close() sql_con.close() bot.send_message(message.chat.id, answer, reply_markup=student_groups_keyboard) set_next_step(message.chat.id, "select_student_group") elif message.text == "Другая программа": sql_con = functions.get_connection() cursor = sql_con.cursor() cursor.execute( """SELECT study_level_name FROM user_choice WHERE user_id = %s""", (message.chat.id, )) data = cursor.fetchone() cursor.close() sql_con.close() message.text = data[0] select_study_level(message) return else: answer += "Пожалуйста, укажи год:" bot.send_message(message.chat.id, answer) set_next_step(message.chat.id, "select_admission_year")
def select_study_program_combination(message): answer = "" sql_con = functions.get_connection() cursor = sql_con.cursor() cursor.execute( """SELECT study_level_name, study_programs_json FROM user_choice WHERE user_id = %s""", (message.chat.id, )) data = cursor.fetchone() cursor.close() sql_con.close() study_level_name, study_programs = data[0], loads(data[1]) study_level_names = [] for study_program in study_programs: study_level_names.append(study_program["StudyLevelName"]) index = study_level_names.index(study_level_name) study_program_combinations = study_programs[index][ "StudyProgramCombinations"] study_program_combination_names = [] for study_program_combination in study_program_combinations: study_program_combination_names.append( study_program_combination["Name"].strip()) if message.text in study_program_combination_names: answer += "Укажи год поступления:" admission_years_keyboard = ReplyKeyboardMarkup(resize_keyboard=True, one_time_keyboard=False) index = study_program_combination_names.index(message.text) admission_years = study_program_combinations[index]["AdmissionYears"] for admission_year in admission_years: admission_years_keyboard.row(admission_year["YearName"]) admission_years_keyboard.row("Другая программа") sql_con = functions.get_connection() cursor = sql_con.cursor() cursor.execute( """UPDATE user_choice SET study_program_combination_name = %s WHERE user_id = %s""", (message.text, message.chat.id)) sql_con.commit() cursor.close() sql_con.close() bot.send_message(message.chat.id, answer, reply_markup=admission_years_keyboard) set_next_step(message.chat.id, "select_admission_year") elif message.text == "Другая ступень": sql_con = functions.get_connection() cursor = sql_con.cursor() cursor.execute( """SELECT division_name FROM user_choice WHERE user_id = %s""", (message.chat.id, )) data = cursor.fetchone() cursor.close() sql_con.close() message.text = data[0] select_division(message) return else: answer += "Пожалуйста, укажи программу:" bot.send_message(message.chat.id, answer) set_next_step(message.chat.id, "select_study_program_combination")