def read_ageb_files():
all_files = [f for f in listdir('ageb/') if isfile(join('ageb/', f))]
for file_name in all_files:
print(file_name)
with open('ageb/' + file_name) as csv_file:
csv_reader = csv.reader(csv_file, delimiter=',')
line_count = 0
ageb_data = []
for row in csv_reader:
if line_count == 0:
line_count += 1
else:
ageb_row = {
'entidad': row[0],
'nom_ent': row[1],
'mun': row[2],
'nom_mun': row[3],
'loc': row[4],
'nom_loc': row[5],
'ageb': row[6],
'mza': row[7],
'pobtot': row[8].replace("*", "0"),
'pobmas': row[9].replace("*", "0"),
'pobfem': row[10].replace("*", "0")
}
if ageb_row['mun'] == '000' or ageb_row[
'loc'] == '000' or ageb_row[
'ageb'] == '000' or ageb_row['mza'] == '000':
pass
else:
ageb_data.append(ageb_row)
line_count += 1
execute_insert('ageb', ageb_data)
print(f'Processed {line_count} lines.')
def disallow(bot, update):
"""Handler for 'disallow' command.
Disallows users to subscribe for opened classes.
"""
db.execute_insert(db.set_settings_param_value, ("no", "allow"))
bot.send_message(chat_id=update.message.chat_id,
text="Запись для курсантов закрыта.")
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 read_cps_files():
all_files = [f for f in listdir('cps/') if isfile(join('cps/', f))]
for file_name in all_files:
print(file_name)
doc = xml.dom.minidom.parse('cps/' + file_name)
cp_coords_data = []
cp_coords_detail_data = []
placemark_xml_list = doc.getElementsByTagName("Placemark")
for placemark in placemark_xml_list:
cp = ""
cps_xml = placemark.getElementsByTagName("SimpleData")
for cp_xml in cps_xml:
cp = cp_xml.firstChild.data
coords_xml_list = placemark.getElementsByTagName("coordinates")
for coords_xml in coords_xml_list:
formatted_coords = (str(coords_xml.firstChild.data).replace(
'\n', ' '))
formatted_coords = re.sub('\s+', ' ', formatted_coords)
coords_list_separated = formatted_coords.split(' ')
for cls in coords_list_separated:
if len(cls) > 1:
lonlat = cls.split(',')
longitudy = float(lonlat[0])
latitudx = float(lonlat[1])
cp_coords_detail = {
'cp': cp,
'lat': str(latitudx),
'lon': str(longitudy),
'file_name': file_name
}
cp_coords_detail_data.append(cp_coords_detail)
cp_coords = {
'cp': cp,
'coords': formatted_coords,
'file_name': file_name
}
cp_coords_data.append(cp_coords)
execute_query('DELETE FROM cp_coords where file_name = \'%s\'' %
file_name)
execute_insert('cp_coords', cp_coords_data)
execute_query('DELETE FROM cp_coords_detail where file_name = \'%s\'' %
file_name)
execute_insert('cp_coords_detail', cp_coords_detail_data)
def store_group_num(bot, update):
user_id = update.effective_user.id
msg = update.message.text.strip().split()[0]
try:
group_num = int("".join(filter(str.isdigit, msg)))
except:
bot.send_message(chat_id=update.message.chat_id,
text="Что-то пошло не так. Попробуй еще раз.",
reply_markup=ReplyKeyboardRemove())
return ConversationHandler.END
if not group_num:
bot.send_message(chat_id=update.message.chat_id,
text="Я немного не понял. Просто напиши номер своей группы.")
return ASK_GROUP_NUM_STATE
db.execute_insert(db.update_user_group_sql, (int(group_num), user_id))
bot.send_message(chat_id=update.message.chat_id,
text="Теперь напиши, пожалуйста, фамилию.")
return ASK_LAST_NAME_STATE
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 add_schedule_continue(bot, update, args):
start, end = None, None
if len(args) == 2:
try:
start = dt.datetime.strptime(args[0], DATE_FORMAT).date()
end = dt.datetime.strptime(args[1], DATE_FORMAT).date()
except (ValueError, TypeError) as e:
bot.send_message(
chat_id=update.message.chat_id,
text=
"Ой, наверное дата в каком-то кривом формате. Попробуй еще раз так 2019-05-01"
)
return
error, msg = check_dates(start, end)
if error:
bot.send_message(chat_id=update.message.chat_id, text=msg)
return
else:
bot.send_message(
chat_id=update.message.chat_id,
text=
"Что-то дат не то количество... Должны быть: первый день и последний. "
"Попробуй еще раз.")
return
day = start
if start and end:
while day <= end:
for place, time in product(PLACES, CLASSES_HOURS):
try:
db.execute_insert(db.add_classes_dates_sql,
(place, day.isoformat(), time, True))
except:
bot.send_message(chat_id=update.message.chat_id,
text="Косяк! Что-то не получилось")
return
day += dt.timedelta(days=1)
bot.send_message(chat_id=update.message.chat_id,
text="Ок! Добавил даты с {} по {}. "
"Все верно?".format(start, end))
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 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.')
