def patient_add():
form = AddPatientForm()
if request.method == 'POST' and form.validate_on_submit():
if form.num.data:
query = session_mis.query(HltMkabTable)
query = query.filter(
HltMkabTable.num.ilike('%' + form.num.data + '%'))
else:
query = session_mis.query(HltMkabTable)
query = query.filter(
HltMkabTable.family.ilike('%' + form.fam.data + '%'))
query = query.filter(
HltMkabTable.name.ilike('%' + form.im.data + '%'))
query = query.filter(
HltMkabTable.ot.ilike('%' + form.ot.data + '%'))
if form.birthday.data:
query = query.filter(
HltMkabTable.date_bd == form.birthday.data)
query = query.order_by(HltMkabTable.family, HltMkabTable.name,
HltMkabTable.ot)
return render_template('patientadd.html',
form=form,
patients=query.limit(100).all())
return render_template('patientadd.html', form=form)
def get_close_tap_list_by_close_doctor(prm_date_start, prm_date_end):
query_tap = session_mis.query(HltTapTable.tapid)
query_tap = query_tap.group_by(HltTapTable.tapid)
query_tap = query_tap.filter(HltTapTable.dateclose >= prm_date_start)
query_tap = query_tap.filter(HltTapTable.dateclose <= prm_date_end)
query_tap = query_tap.filter(HltTapTable.isclosed == 1)
query_smtap = session_mis.query(HltSmTapTable.rf_tapid, func.max(HltSmTapTable.date_p).label('max_date_p'), func.coalesce(1, 1).label('case_count')).group_by(HltSmTapTable.rf_tapid)
query_smtap = query_smtap.having(func.count(HltSmTapTable.rf_tapid) > 1)
query_smtap = query_smtap.filter(HltSmTapTable.rf_tapid.in_(query_tap)).subquery('subq1')
query_smtap2 = session_mis.query(query_smtap, HltSmTapTable.rf_lpudoctorid, HltSmTapTable.rf_docprvdid).distinct().join(HltSmTapTable, and_(HltSmTapTable.rf_tapid == query_smtap.c.rf_tapid, HltSmTapTable.date_p == query_smtap.c.max_date_p))
query_smtap2 = query_smtap2.order_by(query_smtap.c.rf_tapid).subquery('subq2')
return query_smtap2
def rep_case_get_list(prm_date_start=None, prm_date_end=None):
# if prm_date_start is None:
# date_start = '2020-03-23'
# else:
# date_start = prm_date_start
# if prm_date_end is None:
# date_end = '2020-03-23'
# else:
# date_end = prm_date_end
# query_smtap = session_mis.query(HltSmTapTable.rf_tapid).group_by(HltSmTapTable.rf_tapid)
# query_smtap = query_smtap.having(func.count(HltSmTapTable.rf_tapid) > 1)
query_smtap = get_close_tap_list_by_close_doctor(prm_date_start=prm_date_start, prm_date_end=prm_date_end)
query = session_mis.query((HltLpuDoctorTable.fam_v+" "+HltLpuDoctorTable.im_v+" "+HltLpuDoctorTable.ot_v).label('fio_v'), HltMkabTable.num, HltMkabTable.family,
HltMkabTable.name, HltMkabTable.ot, HltMkabTable.date_bd, HltTapTable.dateclose, OmsMkbTable.ds)
query = query.join(query_smtap, query_smtap.c.rf_lpudoctorid == HltLpuDoctorTable.lpudoctorid)
query = query.join(HltTapTable, query_smtap.c.rf_tapid == HltTapTable.tapid)
query = query.join(HltMkabTable, HltMkabTable.mkabid == HltTapTable.rf_mkabid)
query = query.join(OmsMkbTable, OmsMkbTable.mkbid == HltTapTable.rf_mkbid)
# query = query.filter(HltTapTable.dateclose >= date_start)
# query = query.filter(HltTapTable.dateclose <= date_end)
# query = query.filter(HltTapTable.tapid.in_(query_smtap))
query = query.order_by(HltLpuDoctorTable.fam_v, HltLpuDoctorTable.im_v, HltLpuDoctorTable.ot_v, HltMkabTable.num)
return query
def patient_save_from_mis(mkabid=0):
if mkabid:
query = session_mis.query(HltMkabTable)
query = query.filter(HltMkabTable.mkabid == mkabid)
query = query.limit(1).all()
if query:
query_patients = Patients.query.filter(
Patients.mis_id == query[0].mkabid).all()
if not query_patients:
patient_new_rec = Patients()
patient_new_rec.fam = query[0].family
patient_new_rec.im = query[0].name
patient_new_rec.ot = query[0].ot
patient_new_rec.birthday = query[0].date_bd
patient_new_rec.num = query[0].num
patient_new_rec.mis_id = query[0].mkabid
patient_new_rec.is_deleted = 0
db.session.add(patient_new_rec)
db.session.commit()
return redirect(
url_for('patient_open',
patient_id=patient_new_rec.patient_id))
return redirect(
url_for('patient_open',
patient_id=query_patients[0].patient_id))
def rep_case_get_count_by_close_doctor(prm_date_start=None, prm_date_end=None):
query_smtap2 = get_close_tap_list_by_close_doctor(prm_date_start=prm_date_start, prm_date_end=prm_date_end)
query = session_mis.query((HltLpuDoctorTable.fam_v+" "+HltLpuDoctorTable.im_v+" "+HltLpuDoctorTable.ot_v).label('fio_v'),
HltDocPrvdTable.name, func.sum(query_smtap2.c.case_count)).join(query_smtap2, HltLpuDoctorTable.lpudoctorid == query_smtap2.c.rf_lpudoctorid)
query = query.join(HltDocPrvdTable, query_smtap2.c.rf_docprvdid == HltDocPrvdTable.docprvdid)
query = query.group_by(HltLpuDoctorTable.fam_v, HltLpuDoctorTable.im_v, HltLpuDoctorTable.ot_v, HltDocPrvdTable.name)
query = query.order_by(HltLpuDoctorTable.fam_v, HltLpuDoctorTable.im_v, HltLpuDoctorTable.ot_v, HltDocPrvdTable.name)
return query
def rep_case_get_count(prm_date_start=None, prm_date_end=None):
if prm_date_start is None:
date_start = '2020-03-23'
else:
date_start = prm_date_start
if prm_date_end is None:
date_end = '2020-03-23'
else:
date_end = prm_date_end
query_smtap = session_mis.query(HltSmTapTable.rf_tapid).group_by(HltSmTapTable.rf_tapid)
query_smtap = query_smtap.having(func.count(HltSmTapTable.rf_tapid) > 1)
query = session_mis.query((HltLpuDoctorTable.fam_v+" "+HltLpuDoctorTable.im_v+" "+HltLpuDoctorTable.ot_v).label('fio_v'),
HltDocPrvdTable.name, func.sum(cast(HltTapTable.isclosed, NUMERIC(10, 0))).label('case'))
query = query.join(HltTapTable, HltTapTable.rf_lpudoctorid == HltLpuDoctorTable.lpudoctorid)
query = query.join(HltDocPrvdTable, HltTapTable.rf_docprvdid == HltDocPrvdTable.docprvdid)
query = query.filter(HltTapTable.dateclose >= date_start)
query = query.filter(HltTapTable.dateclose <= date_end)
query = query.filter(HltTapTable.tapid.in_(query_smtap))
query = query.group_by(HltLpuDoctorTable.fam_v, HltLpuDoctorTable.im_v, HltLpuDoctorTable.ot_v, HltDocPrvdTable.name)
query = query.order_by(HltLpuDoctorTable.fam_v, HltLpuDoctorTable.im_v, HltLpuDoctorTable.ot_v, HltDocPrvdTable.name)
return query.all()
def report_mse_referral():
query = MseReferral.query.filter_by(is_deleted=0).all()
file_name = str(uuid.uuid4()) + '.xlsx'
file_full_name = os.path.join(os.getcwd(), 'site_app', 'files', file_name)
tr = TemplateRender(25,
file_name=file_full_name,
copyfile=False,
open_in_excel=False,
sheet_title='Список')
tr.add_titles_row([['№ карты', 10], ['Фамилия', 20], ['Имя', 20],
['Отчество', 20], ['ДР', 20], ['Возраст', 20],
['Пол', 10], ['Бюро МСЭ', 20],
['Установлена впервые', 20], ['Не устанволена', 20],
['Бессрочно', 20], ['Дата явки', 20],
['% потери трудоспособности', 20], ['Группа', 20],
['Диагноз', 20], ['Врач', 20], ['Дата экспертизы', 20],
['Примечание', 70]])
for referral in query:
tr.add_data_row([[
referral.patient.num, referral.patient.fam, referral.patient.im,
referral.patient.ot, referral.patient.birthday,
referral.patient.get_age(),
session_mis.query(HltMkabTable).get(referral.patient.mis_id).w,
str(referral.bureau), referral.is_first_direction,
referral.is_disability_no_set, referral.is_set_indefinitely,
referral.next_date, referral.degree_disability,
str(referral.disability_group) if referral.disability_group
is not None else '', referral.mse_disease,
str(referral.doctor), referral.expert_date, referral.mse_comment
]])
tr.close_template_file()
return send_file(os.path.join('files', file_name),
as_attachment=True,
mimetype='application/vnd.ms-excel',
attachment_filename="направления на мсэ.xlsx")
def set_patient_num_to_excel(path_name,
file_name,
start_row_num,
type_mapping_birthday,
fam_col_index,
dr_col_index,
result_col_index,
worksheet_num=1,
field_name='num'):
# start_row = 2
start_row = start_row_num - 1 # нумерация с 0 !!!!!!!!!!!!!!!!!!
# type_mapping_birthday = MAPPING_BY_DATE
if isinstance(fam_col_index, list):
fam_col = column_index_from_string(fam_col_index[0])
im_col = column_index_from_string(fam_col_index[1])
ot_col = column_index_from_string(fam_col_index[2])
else:
fam_col = column_index_from_string(fam_col_index)
dr_col = column_index_from_string(dr_col_index)
result_col_history = column_index_from_string(result_col_index)
find_records_count = 0
file_full_name = os.path.join(path_name, file_name)
wb = openpyxl.load_workbook(file_full_name)
# ws = wb.active
ws = wb.worksheets[worksheet_num - 1]
if ws.max_column <= result_col_history:
ws.cell(1, result_col_history).value = ''
for row_num, row in enumerate(ws.iter_rows()):
if row_num < start_row:
continue
row_num += 1
year_bird = row[dr_col - 1].value
fam = None
im = None
ot = None
if isinstance(fam_col_index, list):
fam = row[fam_col - 1].value
im = row[im_col - 1].value
ot = row[ot_col - 1].value
if fam is None:
continue
if im is None:
continue
else:
fam_value = row[fam_col - 1].value
if fam_value is None:
continue
logging.warning(fam_value)
fio_list = fam_value.strip().split(' ', 1)
if len(fio_list) > 1:
fam = fio_list[0]
fam_value = fio_list[1]
fio_list = fam_value.strip().split(' ', 1)
im = fio_list[0]
if len(fio_list) > 1:
ot = fio_list[1]
else:
continue
logging.warning([fam, im, ot])
query = session_mis.query(HltMkabTable.num.label('num'),
HltMkabTable.ss.label('ss'))
query = query.filter(
func.rtrim(func.ltrim(HltMkabTable.family)) == fam.strip())
query = query.filter(
func.rtrim(func.ltrim(HltMkabTable.name)) == im.strip())
if ot is not None:
query = query.filter(
func.rtrim(func.ltrim(HltMkabTable.ot)) == ot.strip())
if type_mapping_birthday == MAPPING_BY_YEAR:
query = query.filter(HltMkabTable.date_bd >= datetime.date(
year=year_bird, month=1, day=1))
query = query.filter(HltMkabTable.date_bd <= datetime.date(
year=year_bird, month=12, day=31))
elif type_mapping_birthday == MAPPING_BY_DATE:
if isinstance(year_bird, str):
year_bird = datetime.datetime.strptime(year_bird, '%d.%m.%Y')
if isinstance(year_bird, datetime.datetime):
query = query.filter(HltMkabTable.date_bd == year_bird)
else:
logging.error('Ошибка в дате рождения, поиск не выполнен')
continue
recs = query.all()
logging.warning(recs)
if recs:
find_records_count += 1
ws.cell(row_num, result_col_history).value = recs[0][
recs[0]._fields.index(field_name)]
logging.warning(find_records_count)
wb.save(file_full_name)