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")