def set_user_answer(chat_id, answer):
sql = "UPDATE users SET answer = {0} WHERE chat_id = {1}".format(
answer, chat_id)
c.execute(sql)
conn.commit()
log_msg = 'User {0} set ({1}) to answer'.format(chat_id, answer)
logger.info(log_msg)
def subscribe(chat_id, group_id):
sql = "UPDATE users SET is_subscribed = 1, studing_group = {0} WHERE chat_id = {1}".format(
group_id, chat_id)
c.execute(sql)
conn.commit()
log_msg = 'User {0} was subscribed'.format(chat_id)
logger.info(log_msg)
def unsubscribe(chat_id):
sql = "UPDATE users SET is_subscribed = 0 WHERE chat_id = {0}".format(
chat_id)
c.execute(sql)
conn.commit()
log_msg = 'User {0} was unsubscribed'.format(chat_id)
logger.info(log_msg)
def delete_day_records_for_group(day, group_id):
sql = """
DELETE FROM
lectors_subjects_composite
WHERE c_id in
(SELECT
class_id
FROM
timetable
WHERE
(group_id = {0})
AND
(class_id in (SELECT c_id FROM classes WHERE day = \"{1}\")));""".format(
group_id, day)
c.execute(sql)
sql = """
DELETE FROM
classes
WHERE
(c_id in (SELECT class_id FROM timetable WHERE group_id = {0}))
AND
(day = \"{1}\");""".format(group_id, day)
c.execute(sql)
conn.commit()
log_msg = 'Records deleted by (day={0}, group_id={1})'.format(
day, group_id)
logger.info(log_msg)
def add_lector(name):
sql = "INSERT INTO lectors (name) VALUES (\"{0}\")"\
.format(name)
c.execute(sql)
conn.commit()
log_msg = 'New lector ({0})'.format(name)
logger.info(log_msg)
def register_user(chat_id, username='******'):
code = abs(hash(str(chat_id)))
sql = 'INSERT OR REPLACE INTO users (chat_id, username, verification_code) VALUES ({0}, \"{1}\", {2})'\
.format(chat_id, username, code)
c.execute(sql)
conn.commit()
log_msg = 'User {0} {1} was registered'.format(chat_id, username)
logger.info(log_msg)
def add_group(name, facultet_id):
if facultet_id not in get_facultets():
raise ValueError("Facultet id doesn't exist!")
sql = "INSERT INTO groups (name, facultet) VALUES (\"{0}\", \"{1}\");".format(
name, facultet_id)
c.execute(sql)
conn.commit()
log_msg = 'New group ({0}) added'.format(name)
logger.info(log_msg)
def write_timetable(data):
for subject in data['subject_name'].unique():
if get_subject_id(subject) is None:
add_subject(subject)
lectors = set()
for l in data['lectors'].unique():
for lector in l.split(', '):
lectors.add(lector)
for lector in lectors:
if get_lector_id(lector) is None:
add_lector(lector)
for i in range(0, len(data)):
data_row = data.loc[i]
write_single_record(*data_row[:-1], data_row[-1:].lectors.split(', '))
log_msg = 'New timetable records added for group {0}'.format(
data['group'].values[0])
logger.info(log_msg)
def delete_user(chat_id):
sql = 'DELETE FROM users WHERE chat_id = {0}'.format(chat_id)
c.execute(sql)
conn.commit()
log_msg = 'User {0} was deleted'.format(chat_id)
logger.info(log_msg)
def add_subject(name):
sql = "INSERT INTO subjects (name) VALUES (\"{0}\")".format(name)
c.execute(sql)
conn.commit()
log_msg = 'New subject added ({0})'.format(name)
logger.info(log_msg)