def ask_place(bot, update):
"""Entry point for 'subscribe' user conversation"""
subscription_allowed = db.execute_select(db.get_settings_param_value, ("allow",))[0][0]
if subscription_allowed == 'no':
bot.send_message(chat_id=update.message.chat_id,
text="Сейчас запись на занятия закрыта.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
user_id = update.effective_user.id
today = dt.date.today()
if today.weekday() == 6:
start_of_the_week = today + dt.timedelta(days=1)
else:
start_of_the_week = today - dt.timedelta(days=today.weekday())
subs = db.execute_select(db.get_user_subscriptions_sql, (user_id, start_of_the_week.isoformat()))
if user_id not in LIST_OF_ADMINS and len(subs) > 1:
bot.send_message(chat_id=update.message.chat_id,
text="У тебя уже есть две записи на эту неделю. Сначала отмени другую запись.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
keyboard = [[InlineKeyboardButton(place, callback_data=place)] for place in PLACES]
reply_markup = ReplyKeyboardWithCancel(keyboard, one_time_keyboard=True)
bot.send_message(chat_id=update.message.chat_id,
text="На какую площадку хочешь?",
reply_markup=reply_markup)
return ASK_PLACE_STATE
def ask_unsubscribe(bot, update):
"""Entry point for 'unsubscribe' user conversation
Offer only subscriptions starting from 'tomorrow' for cancel.
"""
subscription_allowed = db.execute_select(db.get_settings_param_value, ("allow",))[0][0]
if subscription_allowed == 'no':
bot.send_message(chat_id=update.message.chat_id,
text="Сейчас редактирование записи на занятия закрыто.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
user_id = update.effective_user.id
user_subs = db.execute_select(db.get_user_subscriptions_sql,
(user_id, (dt.date.today() + dt.timedelta(days=1)).isoformat()))
if user_subs:
keyboard = [[InlineKeyboardButton("{} {} {}".format(place, date, time), callback_data=(str(date), time))]
for place, date, time in user_subs]
reply_markup = ReplyKeyboardWithCancel(keyboard, one_time_keyboard=True)
bot.send_message(chat_id=update.message.chat_id,
text="Какое отменяем?",
reply_markup=reply_markup)
return RETURN_UNSUBSCRIBE_STATE
else:
bot.send_message(chat_id=update.message.chat_id,
text="Нечего отменять, у тебя нет записи на ближайшие занятия.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
def user_kbd(group_num=None):
"""
Create an inline keyboard with the people list of the given group num
:param int group_num: group number to use for kbd creation, if None the latest group num is used.
:return: Returns the InlineKeyboardMarkup object with the people list.
"""
if group_num is None:
group_num = db.execute_select(db.get_latest_group_num)[0][0]
students = db.execute_select(db.get_users_sql, (group_num, ))
rows_num = ceil(len(students) / 2)
stud_pairs = zip_longest(students[:rows_num], students[rows_num:])
keyboard = []
# First row - group num
data_ignore = create_callback_data("IGNORE", group_num, -1)
data_cancel = create_callback_data("CANCEL", group_num, -1)
row = []
row.append(
InlineKeyboardButton(f"Группа {group_num}", callback_data=data_ignore))
keyboard.append(row)
# Main rows
for pair in stud_pairs:
row = []
row.append(
InlineKeyboardButton(
text=pair[0][1] or pair[0][0], # last name or id
callback_data=create_callback_data("STUDENT", group_num,
pair[0][0])))
if pair[1]:
row.append(
InlineKeyboardButton(
text=pair[1][1] or pair[1][0], # last name or id
callback_data=create_callback_data("STUDENT", group_num,
pair[1][0])))
keyboard.append(row)
# Last row - Buttons
row = []
row.append(
InlineKeyboardButton("<",
callback_data=create_callback_data(
"PREV-GROUP", group_num, -1)))
row.append(InlineKeyboardButton("Отмена", callback_data=data_cancel))
row.append(
InlineKeyboardButton(">",
callback_data=create_callback_data(
"NEXT-GROUP", group_num, -1)))
keyboard.append(row)
return InlineKeyboardMarkup(keyboard)
def store_sign_up(bot, update, user_data):
msg = update.message.text.strip()
if msg == "Отмена":
bot.send_message(chat_id=update.message.chat_id,
text="Отменил. Попробуй заново.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
try:
match = time_regex.match(msg)
except TypeError:
bot.send_message(chat_id=update.message.chat_id,
text="Что-то пошло не так. Попробуй еще раз.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
if not match:
bot.send_message(chat_id=update.message.chat_id,
text="Плхоже, это было некорректное время. Попробуй еще раз.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
date = user_data['date']
place = user_data['place']
time = match.string
user_id = update.effective_user.id
# logic for admins to add students
if user_id in LIST_OF_ADMINS:
student_id = user_data.get('student_id')
if student_id:
user_id = student_id
del (user_data['student_id'])
class_id = db.execute_select(db.get_class_id_sql, (date, time, place))[0][0]
db.execute_insert(db.set_user_subscription_sql, (user_id, class_id))
# check if class is full (PEOPLE_PER_TIME_SLOT)
people_count = db.execute_select(db.get_people_count_per_time_slot_sql, (date, time, place))[0][0]
if people_count > PEOPLE_PER_TIME_SLOT:
bot.send_message(chat_id=update.message.chat_id,
text="Упс, на этот тайм слот уже записалось больше {} человек. "
"Попробуй еще раз на другой.".format(PEOPLE_PER_TIME_SLOT))
db.execute_insert(db.delete_user_subscription_sql, (user_id, class_id))
else:
if people_count == PEOPLE_PER_TIME_SLOT:
# set class open = False
db.execute_insert(db.set_class_state, (CLOSED, class_id))
bot.send_message(chat_id=update.message.chat_id,
text="Ok, записал на {} {} {}".format(place, date, time),
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
def get_all_books(self):
"""
books を全件取得する
"""
sql = "SELECT * FROM books"
table = db.execute_select(sql)
books = [Book(*row) for row in table] # タプルを展開してコンストラクタの引数に渡す
[print(book.__dict__) for book in books]
def remove_classes(date, time=None, place=None):
"""Remove classes from schedule
:param date: the date to remove classes from
:param time: is optional, if given only this time is removed
:return: None
"""
if not time:
# remove schedule records for classes for given date
classes_ids = db.execute_select(db.get_classes_ids_by_date_sql,
(date, place))
classes_ids = list(map(lambda x: x[0], classes_ids))
else:
# remove schedule records for classes for given date and time
classes_ids = db.execute_select(db.get_classes_ids_by_date_time_sql,
(date, time, place))
classes_ids = list(map(lambda x: x[0], classes_ids))
db.execute_insert(db.get_delete_schedules_for_classes_sql, (classes_ids, ))
db.execute_insert(db.get_delete_classes_sql, (classes_ids, ))
def unsubscribe(bot, update):
"""Handler for 'unsubscribe' command
The command allows user to unsubscribe himself from a specificclass.
Removes him from schedule. Check that the date given is not 'today'
or earlier.
"""
try:
msg = update.message.text.strip()
if msg == "Отмена":
bot.send_message(chat_id=update.message.chat_id,
text="Отменил. Попробуй заново.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
place, date, time = msg.split(" ")
try:
class_date = dt.datetime.strptime(date, DATE_FORMAT).date()
except ValueError:
bot.send_message(chat_id=update.message.chat_id,
text="Похоже, это была некорректная дата. Попробуй еще раз.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
if class_date <= dt.date.today():
bot.send_message(chat_id=update.message.chat_id,
text="Нельзя отменять запись в день занятия.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
user_id = update.effective_user.id
class_id = db.execute_select(db.get_class_id_sql, (date, time, place))[0][0]
db.execute_insert(db.delete_user_subscription_sql, (user_id, class_id))
people_count = db.execute_select(db.get_people_count_per_time_slot_sql, (date, time, place))[0][0]
if people_count < PEOPLE_PER_TIME_SLOT:
# set class open = True
db.execute_insert(db.set_class_state, (OPEN, class_id))
bot.send_message(chat_id=update.message.chat_id,
text="Ok, удалил запись на {} {} {}".format(place, date, time),
reply_markup=ReplyKeyboardRemove())
except (ValueError, DBError):
bot.send_message(chat_id=update.message.chat_id,
text="Что-то пошло не так. Попробуй еще раз.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
def get_total_balance():
book_balance = db.get_book_sum()
date = sprite.get_before_month()
sql = "SELECT * FROM collection WHERE (DATE_FORMAT(`month`, '%Y-%m') = '{0}');".\
format(date)
result = db.execute_select(sql)
checked = [row['id'] for row in result if row['check'] == 1]
coll = len(db.get_checked_member()) * 500
total_balance = book_balance + coll
results = {
"balance": total_balance
}
return jsonify(results)
def store_last_name(bot, update):
user_id = update.effective_user.id
surname = update.message.text.strip().split()[0]
if not surname:
bot.send_message(chat_id=update.message.chat_id,
text="Я немного не понял. Просто напиши свою фамилию.")
return ASK_LAST_NAME_STATE
db.execute_insert(db.update_user_last_name_sql, (surname, user_id))
user = db.execute_select(db.get_user_sql, (user_id,))[0]
bot.send_message(chat_id=update.message.chat_id,
text="Спасибо. Я тебя записал. Твоя фамилия {}, и ты из {} группы правильно? Если нет,"
" то используй команду /start чтобы изменить данные о себе."
" Если всё верно, попробуй записаться. Напиши 'Запиши меня'.".format(user[3], user[4]))
return ConversationHandler.END
def register_random_book(self, count=1):
"""
ランダムで books を登録する
"""
print(f"{count} 件ランダムで books を登録します")
publisher_list = db.execute_select("SELECT id FROM publisher")
book_list = []
for i in range(count):
publisher_id = random.choice(publisher_list)[
0] # SELECT の結果はタプルなので 1つ目の要素を参照する
book_name = generator.random_book_name()
author = generator.random_name()
book_list.append((publisher_id, book_name, author))
sql = "INSERT INTO books (publisher_id, name, author) VALUES (%s, %s, %s)"
db.bulk_insert(sql, book_list)
def ask_date(bot, update, user_data):
"""Asks date to subscribe to
Dates are offered starting from 'tomorrow'. Users are not allowed to edit their subscriptions
for 'today' and earlier.
"""
msg = update.message.text.strip()
if msg == "Отмена":
bot.send_message(chat_id=update.message.chat_id,
text="Отменил. Попробуй заново.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
try:
match = place_regex.match(msg)
place = match.group(1)
except (TypeError, IndexError):
bot.send_message(chat_id=update.message.chat_id,
text="Что-то пошло не так. Попробуй еще раз.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
user_data['place'] = place
open_dates = db.execute_select(db.get_open_classes_dates_sql,
((dt.date.today() + dt.timedelta(days=1)).isoformat(), place))
if open_dates:
keyboard = [[
InlineKeyboardButton(
"{} {} (свободно слотов {})".format(WEEKDAYS_SHORT[date.weekday()], date, count),
callback_data=str(date)
)
] for date, count in open_dates]
reply_markup = ReplyKeyboardWithCancel(keyboard, one_time_keyboard=True)
bot.send_message(chat_id=update.message.chat_id,
text="На когда?",
reply_markup=reply_markup)
return ASK_DATE_STATE
else:
bot.send_message(chat_id=update.message.chat_id,
text="Нету открытых дат для записи.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
def test_SELECT文が実行できること():
rows = db.execute_select("SELECT * FROM books")
assert len(rows) > 0
def read_iter_files():
iter_data = []
execute_query('TRUNCATE TABLE iter;')
with open('iter/iter_00_cpv2010.csv') as csv_file:
csv_reader = csv.reader(csv_file, delimiter=',')
line_count = 0
for row in csv_reader:
if line_count == 0:
line_count += 1
else:
iter_row = {
'entidad':
row[0],
'nom_ent':
'',
'mun':
row[2],
'nom_mun':
'',
'loc':
row[4],
'nom_loc':
'',
'longitud':
str((int(row[6]) * -1) /
10000 if is_integer(row[6]) else ''),
'latitud':
str((int(row[7])) / 10000 if is_integer(row[7]) else '')
}
if iter_row['mun'] == '000' or iter_row[
'loc'] == '000' or iter_row[
'longitud'] == '' or iter_row['latitud'] == '':
pass
else:
iter_data.append(iter_row)
pass
line_count += 1
processed_rows = 0
processed_rows_total = 1
new_iter_data = []
for row in iter_data:
lat_rounded = float(row['latitud'][:4])
lon_rounded = float(row['longitud'][:6])
coords_data = execute_select(
"select cp, coords from cp_coords where cp in ("
"select distinct cp from cp_coords_detail "
"where (lat >= %s and lat < %s) "
"and (lon >= %s and lon < %s))" % ((lat_rounded - .4),
(lat_rounded + .4),
(lon_rounded - .4),
(lon_rounded + .4)))
postal_code = find_postal_code(row['latitud'], row['longitud'],
coords_data)
row['cp'] = '' if postal_code is None else postal_code
print("%s - %s left %s " %
(postal_code, str(processed_rows_total),
str(len(iter_data) - processed_rows_total)))
processed_rows_total = processed_rows_total + 1
processed_rows = processed_rows + 1
new_iter_data.append(row)
if processed_rows == 5000:
execute_insert('iter', new_iter_data)
processed_rows = 0
new_iter_data = []
print(f'Processed {line_count} lines.')
def schedule(bot, update, args):
add_count = False
full_schedule = False
if len(args) > 0:
if args[0] not in ('++', 'all'):
bot.send_message(chat_id=update.message.chat_id,
text="Наверное аргумент неправильный.")
return
add_count = args[0] == '++'
full_schedule = args[0] == 'all'
if full_schedule:
schedule = db.execute_select(db.get_full_schedule_sql,
(dt.date(2019, 4, 1).isoformat(), ))
else:
schedule = db.execute_select(db.get_full_schedule_sql,
(dt.date.today().isoformat(), ))
user_ids = list(set(map(lambda x: x[5] or 'unknown', schedule)))
user_count = db.execute_select(db.get_user_visits_count,
(dt.date.today().isoformat(), user_ids))
user_count = dict(user_count)
lines = [
(
line[0],
str(line[1]),
line[2], # place, date, time
str(line[3]),
line[4], # GroupNum LastName
str(user_count.get(line[5], 0))) # visit count
for line in schedule
]
# partition by places
records_by_date_place = defaultdict(list)
for line in lines:
# group by date+place
records_by_date_place[(line[1], line[0])].append(line)
workbook = xlsxwriter.Workbook('/tmp/schedule.xlsx')
try:
merge_format = workbook.add_format({
'align': 'center',
'bold': True,
})
worksheet = workbook.add_worksheet()
row = 0
for key in sorted(records_by_date_place.keys()):
records = records_by_date_place[key]
row += 1
# merge cells and write 'day date place'
date = dt.datetime.strptime(key[0], DATE_FORMAT).date()
day = WEEKDAYS[date.weekday()]
place = key[1]
worksheet.merge_range(row, 1, row, 4, f"{day}, {date}, {place}",
merge_format)
row += 1
# write time slots
col = 1
for time in CLASSES_HOURS:
worksheet.write(row, col, time)
col += 1
row += 1
students_lists = defaultdict(list)
for line in sorted(records,
key=lambda x: x[4] or ''): # sort by last name
string = f"{line[3]} {line[4]} ({line[5]})" if add_count else f"{line[3]} {line[4]}"
students_lists[line[2]].append(string)
lines = []
for time in CLASSES_HOURS:
lines.append(students_lists[time])
for line in zip_longest(*lines, fillvalue=""):
col = 1
for val in line:
worksheet.write(row, col, val)
col += 1
row += 1
except Exception as e:
logger.error(e)
finally:
workbook.close()
bot.send_document(chat_id=update.message.chat_id,
document=open('/tmp/schedule.xlsx', 'rb'))
def getPotTotByCP(cp):
query = 'select i.cp, sum(pobtot)::text pobtot, sum(pobmas)::text pobmas, sum(pobfem)::text pobfem from ageb a ' \
' inner join iter i on i.mun = a.mun and i.loc = a.loc and i.entidad = a.entidad ' \
' where i.cp = \'%s\' group by i.cp order by 1 ' % (cp)
results = execute_select(query)
return json.dumps(results)
def getAllAviableCP():
query = 'select i.cp, a.nom_ent from ageb a ' \
' inner join iter i on i.mun = a.mun and i.loc = a.loc and i.entidad = a.entidad ' \
' where cp != \'\' group by i.cp, a.nom_ent order by 1 '
results = execute_select(query)
return json.dumps(results)
def ask_time(bot, update, user_data):
"""Asks time to subscribe to
Checks that the date given is not earlier than 'tomorrow'. Users are not allowed to edit their subscriptions
for 'today' and earlier.
"""
msg = update.message.text.strip()
if msg == "Отмена":
bot.send_message(chat_id=update.message.chat_id,
text="Отменил. Попробуй заново.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
try:
match = date_regex.match(msg)
except TypeError:
bot.send_message(chat_id=update.message.chat_id,
text="Что-то пошло не так. Попробуй еще раз.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
if not match:
# checks that the given message contains something similar to date
bot.send_message(chat_id=update.message.chat_id,
text="Похоже, это была некорректная дата. Попробуй еще раз.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
date = match.group(1)
try:
# checks the actual date correctness
date = dt.datetime.strptime(date, DATE_FORMAT).date()
except ValueError:
bot.send_message(chat_id=update.message.chat_id,
text="Похоже, это была некорректная дата. Попробуй еще раз.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
if date <= dt.date.today():
bot.send_message(chat_id=update.message.chat_id,
text="Нельзя редактировать уже зафиксированные даты (сегодня и ранее)."
"Можно записываться на 'завтра' и позже.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
date = date.isoformat()
# check for existing subscription for the date, 2 subs are not allowed per user per date
user_id = update.effective_user.id
subs = db.execute_select(db.get_user_subscriptions_for_date_sql, (user_id, date))
if user_id not in LIST_OF_ADMINS and len(subs) > 0:
bot.send_message(chat_id=update.message.chat_id,
text="У тебя уже есть запись на {}. "
"Чтобы записаться отмени ранее сделанную запись.".format(date),
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
user_data['date'] = date
place = user_data['place']
time_slots = db.execute_select(db.get_open_classes_time_sql, (date, place))
time_slots = map(lambda x: x[0], time_slots)
# TODO: show count of open positions per time
keyboard = [[InlineKeyboardButton(str(time), callback_data=str(time))] for time in time_slots]
reply_markup = ReplyKeyboardWithCancel(keyboard, one_time_keyboard=True)
bot.send_message(chat_id=update.message.chat_id,
text="Теперь выбери время",
reply_markup=reply_markup)
return ASK_TIME_STATE