def search_table(self):
departure_date_value = self.request.args.get("departure_date", None)
if departure_date_value:
departure_date = parse_date(departure_date_value)
self.q = self.q.filter(Train.departure_date >= departure_date)
self.search_form.departure_date.default = departure_date
arrival_date_value = self.request.args.get("arrival_date", None)
if arrival_date_value:
arrival_date = parse_date(arrival_date_value)
self.q = self.q.filter(Train.arrival_date <= arrival_date)
self.search_form.arrival_date.default = arrival_date
from_country_id = self.request.args.get("from_country_id", -1)
if from_country_id:
try:
from_country_id = int(from_country_id)
except ValueError:
return render_template('errors/error-500.html'), 500
if from_country_id != -1:
self.q = self.q.filter(
Train.from_country_id == from_country_id)
self.search_form.from_country_id.default = from_country_id
to_country_id = self.request.args.get("to_country_id", -1)
if to_country_id:
try:
to_country_id = int(to_country_id)
except ValueError:
return render_template('errors/error-500.html'), 500
if to_country_id != -1:
self.q = self.q.filter(Train.to_country_id == to_country_id)
self.search_form.to_country_id.default = to_country_id
from_city = self.request.args.get("from_city", None)
if from_city:
self.q = self.q.filter(
func.lower(Train.from_city).contains(from_city.lower()))
self.search_form.from_city.default = from_city
to_city = self.request.args.get("to_city", None)
if to_city:
self.q = self.q.filter(
func.lower(Train.to_city).contains(to_city.lower()))
self.search_form.to_city.default = to_city
def search_table(self):
code_value = self.request.args.get("code", None)
if code_value:
self.q = self.q.filter(FlightCode.code.contains(code_value))
self.search_form.code.default = code_value
date_value = self.request.args.get("date", None)
if date_value:
date = parse_date(date_value)
self.q = self.q.filter(FlightCode.date == date)
self.search_form.date.default = date
from_country_id = self.request.args.get("from_country_id", -1)
if from_country_id:
try:
from_country_id = int(from_country_id)
except ValueError:
return render_template('errors/error-500.html'), 500
if from_country_id != -1:
self.q = self.q.filter(
FlightCode.from_country_id == from_country_id)
self.search_form.from_country_id.default = from_country_id
to_country_id = self.request.args.get("to_country_id", -1)
if to_country_id:
try:
to_country_id = int(to_country_id)
except ValueError:
return render_template('errors/error-500.html'), 500
if to_country_id != -1:
self.q = self.q.filter(
FlightCode.to_country_id == to_country_id)
self.search_form.to_country_id.default = to_country_id
from_city = self.request.args.get("from_city", None)
if from_city:
self.q = self.q.filter(
func.lower(FlightCode.from_city).contains(from_city.lower()))
self.search_form.from_city.default = from_city
to_city = self.request.args.get("to_city", None)
if to_city:
self.q = self.q.filter(
func.lower(FlightCode.to_city).contains(to_city.lower()))
self.search_form.to_city.default = to_city
def get_flights_by_date():
if not current_user.is_authenticated:
return redirect(url_for('login_blueprint.login'))
if not current_user.user_role.can_add_air:
return render_template('errors/error-400.html'), 400
date = request.form.get("date")
flights_options = ""
if date != "-1":
flights = FlightCode.query.filter_by(date=parse_date(date))
flights_options = "".join([
"<option value='{}'>{}</option>".format(
f.id, "{}, {} - {}".format(f.code, f.from_city, f.to_city))
for f in flights
])
return json.dumps(flights_options, ensure_ascii=False)
def get_gen_stat(request, q, type_="all"):
infected_state_id = State.query.filter_by(
value=c.state_infec[0]).first().id
hospitalized_state_id = State.query.filter_by(
value=c.state_hosp[0]).first().id
data = []
date = request.form.get("date", datetime.today())
if not date:
date = datetime.today()
else:
date = parse_date(date)
date = date.date()
prev_date = date - timedelta(days=1)
if type_ == "wo_symptoms":
q = q.filter(
cast(PatientState.attrs["state_infec_illness_symptoms"], db.String)
== "'{}'".format(c.without_symptoms))
elif type_ == "w_symptoms":
q = q.filter(
cast(PatientState.attrs["state_infec_illness_symptoms"], db.String)
== "'{}'".format(c.with_symptoms))
for region in Region.query.all():
if region.name != "Вне РК":
region_q = q.filter(Patient.region_id == region.id)
region_q_date = region_q.filter(
PatientState.detection_date == date)
region_q_prev_date = region_q.filter(
PatientState.detection_date == prev_date)
def count_increase(a, b):
increase = 0
if b != 0:
increase = round(((a - b) / b) * 100, 2)
return increase
infected_count = region_q_date.count()
prev_day_infected_count = region_q_prev_date.count()
infec_increase_percent = count_increase(infected_count,
prev_day_infected_count)
def symptoms_count(params):
params_q = []
for p in params:
params_q.append(
text(
"CAST(\"PatientState\".attrs ->> '{}' AS VARCHAR) = '{}'"
.format(p[0], p[1])))
symptoms_q = region_q_date.filter(or_(*params_q))
symptoms = symptoms_q.count()
symptoms_prev_day = region_q_prev_date.filter(
or_(*params_q)).count()
symptoms_increase = count_increase(symptoms, symptoms_prev_day)
return symptoms, symptoms_increase
wo_symptoms, wo_symptoms_increase = symptoms_count([
("state_infec_illness_symptoms", c.without_symptoms[0])
])
w_symptoms, w_symptoms_increase = symptoms_count([
("state_infec_illness_symptoms", c.with_symptoms[0])
])
prof_screen, prof_screen_increase = symptoms_count([
("state_infec_type", c.prof_tzel[0]),
("state_infec_type", c.contacted_prof_tzel[0])
])
self_request, self_request_increase = symptoms_count([
("state_infec_type", c.self_request[0]),
("state_infec_type", c.contacted_self_request[0])
])
zavoznoi, zavoznoi_increase = symptoms_count([
("state_infec_type", c.zavoznoi[0]),
("state_infec_type", c.contacted_zavoznoi[0])
])
contacted, contacted_increase = symptoms_count([
("state_infec_type", c.contacted_self_request[0]),
("state_infec_type", c.contacted_prof_tzel[0]),
("state_infec_type", c.contacted_zavoznoi[0])
])
unknown, unknown_increase = symptoms_count([("state_infec_type",
-1)])
overall_count = Patient.query.join(
PatientState, PatientState.patient_id == Patient.id)
overall_count = overall_count.filter(
Patient.region_id == region.id)
overall_count = overall_count.filter(
PatientState.state_id == infected_state_id).group_by(
Patient.id).count()
contact_q = ContactedPersons.query.join(
Patient, ContactedPersons.contacted_patient_id == Patient.id)
contact_q = contact_q.filter(Patient.region_id == region.id)
date_contact = contact_q.filter(Patient.created_date == date)
prev_date_contact = contact_q.filter(
Patient.created_date == prev_date)
close_date_contact = date_contact.filter(
ContactedPersons.is_potential_contact == False).count()
close_prev_date_contact = date_contact.filter(
ContactedPersons.is_potential_contact == False).count()
close_contact_increase = count_increase(close_date_contact,
close_prev_date_contact)
potential_date_contact = date_contact.filter(
ContactedPersons.is_potential_contact == True).count()
potential_prev_date_contact = date_contact.filter(
ContactedPersons.is_potential_contact == True).count()
potential_contact_increase = count_increase(
close_date_contact, close_prev_date_contact)
hospitalized_count = Patient.query.join(
PatientState, PatientState.patient_id == Patient.id)
hospitalized_count = hospitalized_count.filter(
Patient.region_id == region.id)
hospitalized_count = hospitalized_count.filter(
PatientState.detection_date == date)
hospitalized_count = hospitalized_count.filter(
PatientState.state_id == hospitalized_state_id).group_by(
Patient.id).count()
entry = [
region.name, infected_count, infec_increase_percent,
close_date_contact, potential_date_contact
]
if type_ == "all":
entry += [
wo_symptoms, wo_symptoms_increase, w_symptoms,
w_symptoms_increase
]
entry += [
prof_screen, prof_screen_increase, self_request,
self_request_increase, zavoznoi, zavoznoi_increase, contacted,
contacted_increase, unknown, unknown_increase, overall_count,
close_contact_increase, potential_contact_increase,
hospitalized_count
]
data.append(entry)
infec_day_header = "{} {}".format(_("инфицировано за \n"),
date.strftime("%d-%m-%Y"))
columns = [
_("Регион"), infec_day_header,
_("прирост (%)"),
_("установлено БК"),
_("установлено ПК")
]
if type_ == "all":
columns += [
_("без \n симптомов"),
_("%"),
_("с \n симптомами"),
_("%")
]
columns += [
_("профскрининг"),
_("%"),
_("самообращение"),
_("%"),
_("завозной"),
_("%"),
_("контактных"),
_("%"),
_("статус \n не известен"),
_("%"),
_("всего зарегистрировано \n инфицированных с 13 марта"),
_("БК в нарастании"),
_("ПК в нарастании"),
_("изолировано в карантинный \n стационар")
]
data = pd.DataFrame(data, columns=columns)
return data
def export_various_data_xls():
if not current_user.is_authenticated:
return redirect(url_for('login_blueprint.login'))
if not current_user.user_role.can_access_various_exports:
return render_template('errors/error-500.html'), 500
q = Patient.query
infected_state_id = State.query.filter_by(
value=c.state_infec[0]).first().id
hospitalized_state_id = State.query.filter_by(
value=c.state_hosp[0]).first().id
q = q.join(PatientState, PatientState.patient_id == Patient.id)
q = q.filter(PatientState.state_id == infected_state_id)
q = q.group_by(Patient.id)
data = []
value = request.form.get("value", None)
if value == "region_age_sex_infected":
# task = export_region_age_sex_infected.delay(filename = "sss.xls")
# start_task(current_user, _("Инфицированные - регион, возраст, пол"), task.id)
# def export_region_age_sex_infected(self, filename):
def get_age_filter(age_start, age_end):
date_start = datetime.strftime(
datetime.today() - timedelta(days=age_start * 365), "%Y-%m-%d")
date_end = datetime.strftime(
datetime.today() - timedelta(days=age_end * 365), "%Y-%m-%d")
return Patient.dob.between(date_end, date_start)
q = Patient.query
infected_state_id = State.query.filter_by(
value=c.state_infec[0]).first().id
q = q.join(PatientState, PatientState.patient_id == Patient.id)
q = q.filter(PatientState.state_id == infected_state_id)
q = q.group_by(Patient.id)
total = Region.query.count()
i = 0
data = []
for region in Region.query.all():
if region.name != "Вне РК":
infected_count = q.filter(
Patient.region_id == region.id).count()
entry = [region.name, infected_count]
for age_range in [(0, 9), (10, 19), (20, 29), (30, 39),
(40, 49), (50, 59), (60, 69), (70, 79),
(80, 89), (90, 99)]:
age_query = q.filter(
get_age_filter(age_range[0], age_range[1])).filter(
Patient.region_id == region.id)
entry.append(
age_query.filter(Patient.gender == False).count())
entry.append(
age_query.filter(Patient.gender == True).count())
data.append(entry)
# self.update_state(state='PROGRESS',
# meta={'current': i, 'total': total,
# 'status': 'PROGRESS'})
i += 1
age_ranges = [
_("0-9"),
_("10-19"),
_("20-29"),
_("30-39"),
_("40-49"),
_("50-59"),
_("60-69"),
_("70-79"),
_("80-89"),
_("90-99")
]
gender_age_ranges = [["М {}".format(age_r), "Ж {}".format(age_r)]
for age_r in age_ranges]
gender_age_ranges = [x for l in gender_age_ranges for x in l]
data = pd.DataFrame(
data, columns=[_("Регион"),
_("Все"), *gender_age_ranges])
elif value == "region_geo_age":
def calculate_age(born):
today = date.today()
return today.year - born.year - ((today.month, today.day) <
(born.month, born.day))
count = 0
start_count = request.form.get("start_count", "")
end_count = request.form.get("end_count", "")
for patient in q.all():
entry = []
hospital_state_id = State.query.filter_by(
value=c.state_hosp[0]).first().id
if patient.region and patient.region.name != "Вне РК" and patient.home_address:
count += 1
if start_count != "" and end_count != "":
if count < int(start_count):
continue
if count > int(end_count):
break
were_hospitalized = PatientState.query.filter_by(
patient_id=patient.id).filter_by(
state_id=hospital_state_id).count()
gender = _("Неизвестно")
if patient.gender == False:
gender = _("Мужчина")
elif patient.gender == True:
gender = _("Женщина")
contacted_count = ContactedPersons.query.filter_by(
infected_patient_id=patient.id).count()
is_contacted = ContactedPersons.query.filter_by(
contacted_patient_id=patient.id).count()
entry = [
patient.region, patient.home_address.lat,
patient.home_address.lng, patient.home_address.city,
patient.home_address.street, patient.dob, gender,
patient.travel_type,
yes_no(were_hospitalized), contacted_count,
yes_no(is_contacted),
yes_no(patient.is_dead), patient.job, patient.job_address,
patient.job_address.lat, patient.job_address.lng
]
if start_count != "" and end_count != "":
if count >= int(start_count) and count <= int(end_count):
data.append(entry)
else:
data.append(entry)
data = pd.DataFrame(data,
columns=[
_("Регион"),
_("Latitude"),
_("Longitude"),
_("Город"),
_("Улица"),
_("Дата Рождения"),
_("Пол"),
_("Тип Въезда"),
_("Был ли Госпитализирован"),
_("Число Контактов"),
_("Контактный?"),
_("Умер"),
_("Место Работы"),
_("Адрес Работы"),
_("Работа Lat"),
_("Работа Lng")
])
elif value == "hospitals_list_by_regions":
hospitals_q = Hospital.query
hospital_type = request.form.get("hospital_type", "-1")
if hospital_type != "-1":
hospitals_q = hospitals_q.filter_by(hospital_type_id=hospital_type)
for hospital in hospitals_q.all():
entry = [
str(hospital.region), hospital.hospital_type,
str(hospital)
]
data.append(entry)
data = pd.DataFrame(data,
columns=[
_("Регион"),
_("Тип Стационара"),
_("Название Стационара")
])
elif value == "infected_with_params":
start_date = request.form.get("start_date", None)
if start_date:
start_date = parse_date(start_date)
q = q.filter(PatientState.detection_date >= start_date)
end_date = request.form.get("end_date", None)
if end_date:
end_date = parse_date(end_date)
q = q.filter(PatientState.detection_date <= end_date)
for patient in q.all():
infected_state = PatientState.query.filter_by(
patient_id=patient.id).filter_by(
state_id=infected_state_id).first()
if infected_state:
state_infec_attr = c.unknown[1]
illness_symptoms = c.unknown[1]
illness_severity = c.unknown[1]
if infected_state.attrs:
state_infec_attr = infected_state.attrs.get(
'state_infec_type', None)
if state_infec_attr:
state_infec_attr = dict(c.state_infec_types).get(
state_infec_attr, c.unknown[1])
illness_symptoms = infected_state.attrs.get(
'state_infec_illness_symptoms', None)
if illness_symptoms != c.unknown[1] and illness_symptoms:
illness_symptoms = dict(c.illness_symptoms).get(
illness_symptoms, c.unknown[1])
illness_severity = infected_state.attrs.get(
'state_infec_illness_severity', None)
if illness_severity != c.unknown[1] and illness_severity:
illness_severity = dict(c.illness_severity).get(
illness_severity, c.unknown[1])
entry = [
str(patient), patient.dob,
str(patient.home_address), patient.job,
patient.job_position, patient.job_category,
state_infec_attr, illness_symptoms, illness_severity
]
data.append(entry)
data = pd.DataFrame(data,
columns=[
_("ФИО"),
_("Год рождения"),
_("Адрес"),
_("Место работы"),
_("Профессия (должность)"),
_("Категория Работы"),
_("Как выявлен"),
_("Клиника"),
_("Степень Симптомов")
])
elif value == "gen_stat_1_day":
data = get_gen_stat(request, q)
elif value == "gen_stat_1_day_wo_symptoms":
data = get_gen_stat(request, q, "wo_symptoms")
elif value == "gen_stat_1_day_w_symptoms":
data = get_gen_stat(request, q, "w_symptoms")
output = io.BytesIO()
writer = pd.ExcelWriter(output, engine='xlsxwriter')
data.to_excel(writer, index=False)
def get_col_widths(df):
widths = []
for col in df.columns:
col_data_width = len(col)
if col_data_width < 5:
col_data_width = 5
if not isinstance(df[col], pd.DataFrame):
col_data_width = max(df[col].map(str).map(len).max(), len(col))
if col_data_width < 40:
col_data_width *= 1.2
widths.append(col_data_width)
return widths
for i, width in enumerate(get_col_widths(data)):
writer.sheets['Sheet1'].set_column(i, i, width)
writer.sheets['Sheet1'].set_row(0, 30)
writer.save()
xlsx_data = output.getvalue()
filename_xls = "выгрузка.xls"
response = Response(xlsx_data, mimetype="application/vnd.ms-excel")
response.headers["Content-Disposition"] = \
"attachment;" \
"filename*=UTF-8''{}".format(urllib.parse.quote(filename_xls.encode('utf-8')))
return response
def search_table(self):
region_id = self.request.args.get("region_id", -1)
if region_id:
region_id = int(region_id)
if region_id != -1:
self.q = self.q.filter(Patient.region_id == region_id)
self.search_form.region_id.default = region_id
self.search_params.append(
(_("Регион"),
Region.query.filter_by(id=region_id).first().name))
filt = dict()
job_category_id = self.request.args.get("job_category_id", "-1")
if job_category_id != "-1":
job_category_disp_name = _("Неизвестно")
if job_category_id == "None":
job_category_id = None
else:
job_category_id = int(job_category_id)
job_category_disp_name = JobCategory.query.filter_by(
id=job_category_id).first().name
self.search_params.append(
(_("Категория Работы"), job_category_disp_name))
self.q = self.q.filter(Patient.job_category_id == job_category_id)
self.search_form.job_category_id.default = job_category_id
is_found = self.request.args.get("is_found", "-1")
if is_found != "-1":
filt["is_found"] = is_found == "1"
self.search_form.is_found.default = is_found
self.search_params.append(
(_("Найден"), _("Да") if is_found == "1" else _("Нет")))
is_currently_infected = self.request.args.get("is_currently_infected",
"-1")
if is_currently_infected != "-1":
filt["is_infected"] = is_currently_infected == "1"
self.search_form.is_currently_infected.default = is_currently_infected
self.search_params.append(
(_("Инфицирован"),
_("Да") if filt["is_infected"] else _("Нет")))
def name_search(param, param_str, q, param_disp_name):
if param_str in request.args:
req_str = request.args[param_str]
if req_str:
q = q.filter(func.lower(param).contains(req_str.lower()))
param = getattr(self.search_form, param_str, None)
if param:
setattr(param, 'default', req_str)
self.search_params.append((param_disp_name, req_str))
return q
self.q = name_search(Patient.first_name, "first_name", self.q,
_("Имя"))
self.q = name_search(Patient.second_name, "second_name", self.q,
_("Фамилия"))
self.q = name_search(Patient.patronymic_name, "patronymic_name",
self.q, _("Отчество"))
iin = request.args.get("iin", "")
if iin:
self.q = self.q.filter(Patient.iin.contains(iin))
self.search_form.iin.default = iin
self.search_params.append((_("ИИН"), iin))
pass_num = request.args.get("pass_num", "")
if pass_num:
self.q = self.q.filter(Patient.pass_num.contains(pass_num))
self.search_form.pass_num.default = pass_num
self.search_params.append((_("Номер Паспорта"), pass_num))
travel_type = request.args.get("travel_type", c.all_travel_types[0])
if travel_type and travel_type != c.all_travel_types[0]:
try:
travel_type_query = TravelType.query.filter_by(
value=travel_type).first()
travel_type_id = travel_type_query.id
except (exc.SQLAlchemyError, AttributeError):
raise exc.SQLAlchemyError(
"Travel Type Error - {}".format(travel_type))
if travel_type_id:
filt["travel_type_id"] = travel_type_id
self.search_form.travel_type.default = travel_type
self.search_params.append(
(_("Тип Въезда"), travel_type_query.name))
# Created_date range
date_range_start = request.args.get("date_range_start", None)
if date_range_start:
date_range_start = parse_date(date_range_start)
self.q = self.q.filter(Patient.created_date >= date_range_start)
self.search_form.date_range_start.default = date_range_start
self.search_params.append((_("Дата Создания (Начало)"),
date_range_start.strftime('%Y-%m-%d')))
date_range_end = request.args.get("date_range_end", None)
if date_range_end:
date_range_end = parse_date(date_range_end)
self.q = self.q.filter(Patient.created_date <= date_range_end)
self.search_form.date_range_end.default = date_range_end
self.search_params.append((_("Дата Создания (Конец)"),
date_range_end.strftime('%Y-%m-%d')))
self.q = self.q.filter_by(**filt)
# State search
patient_state = self.request.args.get("patient_state", "-1")
if patient_state != "-1":
# State Filter
patient_state_val = State.query.filter_by(
value=patient_state).first()
patient_state_id = patient_state_val.id
patient_state_disp_name = patient_state_val.name
self.q = self.q.join(PatientState,
PatientState.patient_id == Patient.id)
self.q = self.q.filter(PatientState.state_id == patient_state_id)
self.search_form.patient_state.default = patient_state
# State Date
# State Date Start
state_date_range_start = request.args.get("state_date_range_start",
None)
if state_date_range_start:
state_date_range_start = parse_date(state_date_range_start)
self.q = self.q.filter(
PatientState.detection_date >= state_date_range_start)
self.search_form.state_date_range_start.default = state_date_range_start
patient_state_disp_name = "{} {}".format(
patient_state_disp_name,
state_date_range_start.strftime('%Y-%m-%d'))
# State Date End
state_date_range_end = request.args.get("state_date_range_end",
None)
if state_date_range_end:
state_date_range_end = parse_date(state_date_range_end)
self.q = self.q.filter(
PatientState.detection_date <= state_date_range_end)
self.search_form.state_date_range_end.default = state_date_range_end
patient_state_disp_name = "{}:{}".format(
patient_state_disp_name,
state_date_range_end.strftime('%Y-%m-%d'))
self.search_params.append(
(_("Статус Пациента"), patient_state_disp_name))
# State Count
# State Count Min
state_count_min = request.args.get("state_count_min", None)
if state_count_min:
self.q = self.q.having(
func.count(PatientState.id) >= state_count_min)
self.search_form.state_count_min.default = state_count_min
self.search_params.append(
(_("Минимум Статусов"), state_count_min))
# State Count Max
state_count_max = request.args.get("state_count_max", None)
if state_count_max:
self.q = self.q.having(
func.count(PatientState.id) <= state_count_max)
self.search_form.state_count_max.default = state_count_max
self.search_params.append(
(_("Максимум Статусов"), state_count_max))
# State Infec
if patient_state_val.value == c.state_infec[0]:
state_infec_type = request.args.get("state_infec_type", "None")
if state_infec_type != "None":
param = "state_infec_type"
self.q = self.q.filter(
text(
"CAST(\"PatientState\".attrs ->> '{}' AS VARCHAR) = '{}'"
.format(param, state_infec_type)))
self.search_form.state_infec_type.default = state_infec_type
self.search_params.append(
(_("Инфицирован - Тип"),
dict(c.state_infec_types)[state_infec_type]))
state_infec_illness_symptoms = request.args.get(
"state_infec_illness_symptoms", "None")
if state_infec_illness_symptoms != "None":
param = "state_infec_illness_symptoms"
self.q = self.q.filter(
text(
"CAST(\"PatientState\".attrs ->> '{}' AS VARCHAR) = '{}'"
.format(param, state_infec_illness_symptoms)))
self.search_form.state_infec_illness_symptoms.default = state_infec_illness_symptoms
self.search_params.append(
(_("Инфицирован - Симптомы"), dict(
c.illness_symptoms)[state_infec_illness_symptoms]))
state_infec_illness_severity = request.args.get(
"state_infec_illness_severity", "None")
if state_infec_illness_severity != "None":
param = "state_infec_illness_severity"
self.q = self.q.filter(
text(
"CAST(\"PatientState\".attrs ->> '{}' AS VARCHAR) = '{}'"
.format(param, state_infec_illness_severity)))
self.search_form.state_infec_illness_severity.default = state_infec_illness_severity
self.search_params.append((
_("Инфицирован - Тяжесть Болезни"),
dict(
c.illness_severity)[state_infec_illness_severity]))
# State Dead
if patient_state_val.value == c.state_dead[0]:
state_dead_reason = request.args.get("state_dead_reason",
"None")
if state_dead_reason != "None":
param = "state_dead_reason"
self.q = self.q.filter(
text(
"CAST(\"PatientState\".attrs ->> '{}' AS VARCHAR) = '{}'"
.format(param, state_dead_reason)))
self.search_form.state_dead_reason.default = state_dead_reason
self.search_params.append(
(_("Умер - Причина Смерти"),
dict(c.death_reasons)[state_dead_reason]))
self.q = self.q.group_by(Patient.id)
# Is contacted
contacted = self.request.args.get("contacted", "-1")
if contacted != "-1":
contacted_disp_name = None
if contacted == "contacted":
self.q = self.q.join(
ContactedPersons,
ContactedPersons.contacted_patient_id == Patient.id)
self.q = self.q.group_by(Patient.id)
contacted_disp_name = _("Контактный")
elif contacted == "with_contacts":
self.q = self.q.join(
ContactedPersons,
ContactedPersons.infected_patient_id == Patient.id)
self.q = self.q.group_by(Patient.id)
contacted_disp_name = _("С Контактами")
elif contacted == "contacted_close":
self.q = self.q.join(
ContactedPersons,
ContactedPersons.contacted_patient_id == Patient.id)
self.q = self.q.filter(
ContactedPersons.is_potential_contact == False)
self.q = self.q.group_by(Patient.id)
contacted_disp_name = _("Контактный (БК)")
elif contacted == "contacted_potential":
self.q = self.q.join(
ContactedPersons,
ContactedPersons.contacted_patient_id == Patient.id)
self.q = self.q.filter(
ContactedPersons.is_potential_contact == True)
self.q = self.q.group_by(Patient.id)
contacted_disp_name = _("Контактный (ПК)")
self.search_form.contacted.default = contacted
self.search_params.append((_("Контакты"), contacted_disp_name))
is_iin_fail = request.args.get("is_iin_fail", None)
if is_iin_fail:
if is_iin_fail == "is_iin_empty":
self.q = self.q.filter_by(iin='')
self.search_form.is_iin_fail.default = "is_iin_empty"
elif is_iin_fail == "is_iin_invalid":
self.q = self.q.filter(Patient.iin != '')
self.q = self.q.filter(func.length(Patient.iin) != 12)
self.search_form.is_iin_fail.default = "is_iin_invalid"
elif is_iin_fail == "is_iin_valid":
self.q = self.q.filter(func.length(Patient.iin) == 12)
self.search_form.is_iin_fail.default = "is_iin_valid"
address = self.request.args.get("address", None)
if address:
self.q = self.q.join(Address,
Patient.home_address_id == Address.id)
self.q = self.q.join(Country, Country.id == Address.country_id)
self.q = self.q.group_by(Patient.id)
self.q = self.q.filter(
func.lower(
func.concat(Country.name, ' ', Address.city, ' ',
Address.street, ' ', Address.house, ' ',
Address.flat)).contains(address.lower()))
self.search_form.address.default = address
self.search_params.append((_("Адрес"), address))
current_country = Country.query.filter_by(
code=c.current_country).first()
home_address_location_type_id = self.request.args.get(
"home_address_location_type_id", "-1")
if home_address_location_type_id != "-1":
location_type = AddressLocationType.query.filter_by(
id=home_address_location_type_id).first()
self.q = self.q.join(Address,
Patient.home_address_id == Address.id)
self.q = self.q.group_by(Patient.id)
self.q = self.q.filter(
Address.location_type_id == home_address_location_type_id)
self.search_form.home_address_location_type_id.default = home_address_location_type_id
self.search_params.append((_("Село/Город"), location_type))
if travel_type and travel_type != c.all_travel_types[0]:
# FlightTravel
if travel_type_query.value == c.flight_type[0]:
self.q = self.q.join(FlightTravel)
flight_code_id = request.args.get("flight_code_id", None)
if flight_code_id != None:
self.q = self.q.filter(
FlightTravel.flight_code_id == flight_code_id)
self.search_params.append((_("Номер Рейса"),
FlightCode.query.filter_by(
id=flight_code_id).first()))
travel_in_out = request.args.get("travel_departure_outer",
"all_travel")
if travel_in_out != "all_travel":
self.q = self.q.join(
FlightCode,
FlightTravel.flight_code_id == FlightCode.id)
if travel_in_out == "outer_travel":
self.q = self.q.filter(
FlightCode.from_country != current_country)
elif travel_in_out == "domestic_travel":
self.q = self.q.filter(
FlightCode.from_country == current_country)
self.search_form.travel_departure_outer.default = travel_in_out
# TrainTravel
elif travel_type_query.value == c.train_type[0]:
self.q = self.q.join(TrainTravel)
train_id = request.args.get("train_id", None)
if train_id != None:
self.q = self.q.filter(TrainTravel.train_id == train_id)
travel_in_out = request.args.get("travel_departure_outer",
"all_travel")
if travel_in_out != "all_travel":
self.q = self.q.join(Train,
TrainTravel.train_id == Train.id)
if travel_in_out == "outer_travel":
self.q = self.q.filter(
Train.from_country != current_country)
elif travel_in_out == "domestic_travel":
self.q = self.q.filter(
Train.from_country == current_country)
self.search_form.travel_departure_outer.default = travel_in_out
self.search_params.append(
(_("Поезд"),
Train.query.filter_by(id=train_id).first()))
# Blockpost
elif travel_type_query.value == c.blockpost_type[0]:
self.q = self.q.join(BlockpostTravel)
arrival_date = request.args.get("arrival_date", None)
if arrival_date:
self.q = self.q.filter(
BlockpostTravel.date == arrival_date)
self.search_form.arrival_date.default = parse_date(
arrival_date)
blockpost_region_id = request.args.get("blockpost_region_id",
"-1")
if blockpost_region_id != "-1":
self.q = self.q.filter(
BlockpostTravel.region_id == blockpost_region_id)
self.search_form.blockpost_region_id.default = blockpost_region_id
self.search_params.append(
(_("Блокпост"),
Region.query.filter_by(
id=blockpost_region_id).first().name))
# Auto
elif (travel_type_query.value,
travel_type_query.name) in c.various_travel_types:
self.q = self.q.join(VariousTravel)
arrival_date = request.args.get("arrival_date", None)
if arrival_date:
self.q = self.q.filter(VariousTravel.date == arrival_date)
self.search_form.arrival_date.default = parse_date(
arrival_date)
border_id = request.args.get("auto_border_id", "-1")
if border_id != "-1":
self.search_form.auto_border_id.default = border_id
border_list = [
("auto_border_id", self.search_form.auto_border_id),
("foot_border_id", self.search_form.foot_border_id),
("sea_border_id", self.search_form.sea_border_id)
]
for border_type in border_list:
if border_type[0] in request.args:
if request.args[border_type[0]] != "-1":
border_id = request.args[border_type[0]]
border_type[1].default = border_id
self.q = self.q.filter(
VariousTravel.border_control_id == border_id)
self.search_params.append(
(_("Граница"),
BorderControl.query.filter_by(
id=border_id).first().name))
break
self.search_form.process()
def export_users_activity_xls():
if not current_user.is_authenticated:
return redirect(url_for('login_blueprint.login'))
if not current_user.user_role.can_export_users:
return render_template('errors/error-500.html'), 500
q_patient = db.session.query(Patient.created_by_id,
func.count('*').label('patient_count'))
start_date = request.form.get("start_date", None)
if start_date:
try:
start_date = parse_date(start_date)
except ValueError:
return render_template('errors/error-500.html'), 500
q_patient = q_patient.filter(Patient.created_date >= start_date)
end_date = request.form.get("end_date", None)
if end_date:
try:
end_date = parse_date(end_date)
except ValueError:
return render_template('errors/error-500.html'), 500
# Add day so that we get data from throughout the whole day
q_patient = q_patient.filter(
Patient.created_date <= end_date + timedelta(days=1))
q_patient = q_patient.group_by(Patient.created_by_id).subquery()
q = db.session.query(User, q_patient.c.patient_count).outerjoin(
q_patient, User.id == q_patient.c.created_by_id)
region_id = request.form.get("region_id", None)
if region_id:
try:
region_id = int(region_id)
except ValueError:
return render_template('errors/error-500.html'), 500
if region_id != -1:
q = q.filter(User.region_id == region_id)
data = [[
row[0].full_name, row[0].organization, row[0].region,
row[1] if row[1] else 0
] for row in q.all()]
data = pd.DataFrame(data,
columns=[
_("ФИО"),
_("Организация"),
_("Регион"),
_("Кол-во добавленных пациентов")
])
output = io.BytesIO()
writer = pd.ExcelWriter(output, engine='xlsxwriter')
data.to_excel(writer)
writer.save()
xlsx_data = output.getvalue()
region_name = Region.query.filter_by(
id=region_id).first().name if region_id != -1 else c.all_regions
filename_xls = "{}_{}".format(_("пользователи"), region_name)
if start_date:
filename_xls = "{}_{}".format(filename_xls, start_date)
if end_date:
filename_xls = "{}_{}".format(filename_xls, end_date)
filename_xls = "{}.xls".format(filename_xls)
response = Response(xlsx_data, mimetype="application/vnd.ms-excel")
response.headers["Content-Disposition"] = \
"attachment;" \
"filename*=UTF-8''{}".format(urllib.parse.quote(filename_xls.encode('utf-8')))
return response