def get_laboratory_patient_control_params(start_date, end_date, control_params,
card_pk):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
dp.patient_control_param_id, di.time_confirmation, value, dn.id as direction,
to_char(di.time_confirmation AT TIME ZONE %(tz)s, 'DD.MM.YYYY') AS confirm,
to_char(di.time_confirmation AT TIME ZONE %(tz)s, 'YYYY-MM') AS yearmonth_confirm
FROM directions_result
LEFT JOIN directory_fractions dp on directions_result.fraction_id = dp.id = dp.id
LEFT JOIN directions_issledovaniya di on directions_result.issledovaniye_id = di.id
LEFT JOIN directions_napravleniya dn on dn.id=di.napravleniye_id
WHERE
dn.client_id = %(card_pk)s AND
dp.patient_control_param_id in %(control_params)s AND
di.time_confirmation AT TIME ZONE %(tz)s BETWEEN %(start_date)s AND %(end_date)s
ORDER BY dp.patient_control_param_id, di.time_confirmation
""",
params={
'start_date': start_date,
'end_date': end_date,
'control_params': control_params,
'card_pk': card_pk,
'tz': TIME_ZONE
},
)
rows = namedtuplefetchall(cursor)
return rows
def last_results_researches_by_time_ago(client_id, researches, date_start, date_end):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
directions_issledovaniya.id as iss_id,
directions_napravleniya.client_id as card_id,
directions_issledovaniya.napravleniye_id as dir_id,
directions_issledovaniya.research_id as research_id,
directions_issledovaniya.time_confirmation,
to_char(directions_issledovaniya.time_confirmation AT TIME ZONE %(tz)s, 'DD.MM.YY') as confirm,
directions_result.fraction_id,
directory_fractions.title,
directions_result.value
FROM directions_issledovaniya
LEFT JOIN directions_napravleniya
ON directions_issledovaniya.napravleniye_id=directions_napravleniya.id
LEFT JOIN directions_result
ON directions_result.issledovaniye_id=directions_issledovaniya.id
LEFT JOIN directory_fractions
ON directory_fractions.id=directions_result.fraction_id
WHERE
directions_napravleniya.client_id = %(card_pk)s and
directions_issledovaniya.research_id = ANY(ARRAY[%(researches)s]) and
directions_issledovaniya.time_confirmation AT TIME ZONE %(tz)s BETWEEN %(date_start)s AND %(date_end)s
ORDER BY directions_issledovaniya.time_confirmation DESC
""",
params={'tz': TIME_ZONE, 'card_pk': client_id, 'researches': researches, 'date_start': date_start, 'date_end': date_end},
)
rows = namedtuplefetchall(cursor)
return rows
def get_charts_dataset(dashboard_pk):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
dashboards_dashboardchartdata.chart_id,
dashboards_dashboardchartdata.data_set_id,
dashboards_dashboarddataset.sql_query,
dashboards_dashboarddataset.sql_columns_settings,
dashboards_dashboarddataset.title as field_title,
dashboards_dashboarddataset.connect_id,
dashboards_databaseconnectsettings.login,
dashboards_databaseconnectsettings.ip_address,
dashboards_databaseconnectsettings.password,
dashboards_databaseconnectsettings.port,
dashboards_databaseconnectsettings.database,
dashboards_dashboardcharts.title as chart_title,
dashboards_dashboardcharts.default_type as chart_type,
dashboards_dashboardcharts.order as chart_order
FROM public.dashboards_dashboardchartdata
LEFT JOIN dashboards_dashboarddataset ON
dashboards_dashboarddataset.id = dashboards_dashboardchartdata.data_set_id
LEFT JOIN dashboards_databaseconnectsettings ON
dashboards_databaseconnectsettings.id = dashboards_dashboarddataset.connect_id
LEFT JOIN dashboards_dashboardcharts ON
dashboards_dashboardcharts.id = dashboards_dashboardchartdata.chart_id
WHERE chart_id in (SELECT id from dashboards_dashboardcharts where dashboard_id = %(dashboard_pk)s and hide=False) and
dashboards_dashboardchartdata.hide=False and dashboards_dashboardcharts.hide=False
order by dashboards_dashboardcharts.order, dashboards_dashboardchartdata.order
""",
params={'dashboard_pk': dashboard_pk},
)
rows = namedtuplefetchall(cursor)
return rows
def get_diagnoses(d_type="mkb10.4", diag_title="-1", diag_mkb="-1", limit=100):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT * FROM public.directions_diagnoses
WHERE d_type=%(d_type)s
AND CASE
WHEN %(diag_title)s != '-1' AND %(diag_mkb)s != '-1' THEN
code ~* %(diag_mkb)s and title ~* %(diag_title)s
WHEN %(diag_title)s != '-1' AND %(diag_mkb)s = '-1' THEN
title ~* %(diag_title)s
WHEN %(diag_title)s = '-1' AND %(diag_mkb)s != '-1' THEN
code ~* %(diag_mkb)s
END
AND
nsi_id IS NOT NULL
AND nsi_id != '' and hide=false
LIMIT %(limit)s
""",
params={
"d_type": d_type,
"diag_title": diag_title,
"diag_mkb": diag_mkb,
"limit": limit
},
)
rows = namedtuplefetchall(cursor)
return rows
def get_confirm_direction_patient_year_is_extract(d_s, d_e, card_pk1,
extract_research_pks):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
directions_napravleniya.id as direction,
directions_issledovaniya.time_confirmation,
to_char(directions_issledovaniya.time_confirmation AT TIME ZONE %(tz)s, 'DD.MM.YYYY') as ch_time_confirmation,
directions_issledovaniya.research_id,
directory_researches.title as research_title,
directions_issledovaniya.study_instance_uid_tag
FROM directions_napravleniya
INNER JOIN directions_issledovaniya ON (directions_napravleniya.id = directions_issledovaniya.napravleniye_id)
AND directions_issledovaniya.research_id IN %(extract_research_pks)s
LEFT JOIN directory_researches ON
directions_issledovaniya.research_id=directory_researches.id
WHERE directions_issledovaniya.time_confirmation IS NOT NULL
AND directions_issledovaniya.research_id in %(extract_research_pks)s
AND directions_issledovaniya.time_confirmation AT TIME ZONE 'ASIA/Irkutsk' BETWEEN %(d_start)s AND %(d_end)s
AND client_id=%(card_pk)s
ORDER BY directions_issledovaniya.time_confirmation DESC, directions_napravleniya.id
""",
params={
'd_start': d_s,
'd_end': d_e,
'tz': TIME_ZONE,
'card_pk': card_pk1,
'extract_research_pks': extract_research_pks,
},
)
rows = namedtuplefetchall(cursor)
return rows
def get_date_slots_for_many_resource(date_start, date_end, resource_tuple):
if not resource_tuple:
return []
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
resource_id,
id as slot_id,
datetime,
to_char(datetime AT TIME ZONE %(tz)s, 'HH:MI') AS start_slot,
to_char(datetime_end AT TIME ZONE %(tz)s, 'HH:MI') AS end_slot,
to_char(datetime AT TIME ZONE %(tz)s, 'YYYY-MM-DD') AS date_char
FROM public.doctor_schedule_slotplan
WHERE datetime AT TIME ZONE %(tz)s BETWEEN %(d_start)s AND %(d_end)s and resource_id in %(resource_tuple)s
ORDER BY resource_id, datetime
""",
params={
'd_start': date_start,
'd_end': date_end,
'tz': TIME_ZONE,
'resource_tuple': resource_tuple
},
)
rows = namedtuplefetchall(cursor)
return rows
def sort_direction_by_file_name_contract(directions, is_create_contract):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT directions_issledovaniya.research_id,
directory_researches.file_name_contract,
directions_issledovaniya.napravleniye_id,
directions_napravleniya.num_contract
FROM public.directions_issledovaniya
LEFT JOIN directory_researches on
directory_researches.id = directions_issledovaniya.research_id
LEFT JOIN directions_napravleniya on
directions_napravleniya.id = directions_issledovaniya.napravleniye_id
where directions_issledovaniya.napravleniye_id in %(directions)s AND
CASE
WHEN %(is_create_contract)s = '1' THEN directions_napravleniya.num_contract is Null
WHEN %(is_create_contract)s = '0'THEN directions_napravleniya.num_contract is not Null
END
order by directions_napravleniya.num_contract, directory_researches.file_name_contract, directions_issledovaniya.napravleniye_id
""",
params={
'directions': directions,
'is_create_contract': is_create_contract
},
)
rows = namedtuplefetchall(cursor)
return rows
def get_paraclinic_results_by_direction(pk_dir):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
directions_issledovaniya.id,
directions_issledovaniya.napravleniye_id,
directions_issledovaniya.research_id,
directions_paraclinicresult.value,
directions_paraclinicresult.field_id,
directory_ParaclinicInputField.title
FROM directions_issledovaniya
LEFT JOIN directions_paraclinicresult ON
directions_issledovaniya.id=directions_paraclinicresult.issledovaniye_id
LEFT JOIN directory_researches ON
directions_issledovaniya.research_id=directory_researches.id
LEFT JOIN directory_paraclinicinputfield ON
directions_paraclinicresult.field_id=directory_paraclinicinputfield.id
WHERE directions_issledovaniya.napravleniye_id = %(num_dir)s
""",
params={'num_dir': pk_dir},
)
rows = namedtuplefetchall(cursor)
return rows
def get_type_confirm_direction(directions_tuple):
if not directions_tuple:
return []
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
DISTINCT (directions_issledovaniya.napravleniye_id) as napravleniye_id,
directory_researches.podrazdeleniye_id,
directory_researches.is_stom,
directory_researches.is_doc_refferal,
directory_researches.is_paraclinic,
directory_researches.is_form,
directory_researches.is_microbiology,
directory_researches.is_application
FROM directions_issledovaniya
LEFT JOIN directory_researches
ON directions_issledovaniya.research_id = directory_researches.id
WHERE directions_issledovaniya.napravleniye_id in %(directions_tuple)s
ORDER BY directions_issledovaniya.napravleniye_id DESC""",
params={
'directions_tuple': directions_tuple,
},
)
rows = namedtuplefetchall(cursor)
return rows
def get_plans_hospitalization_sql(d_s, d_e, department):
"""
парам: d_s - date-start, d_e - date-end, deparment
:return:
"""
with connection.cursor() as cursor:
cursor.execute(
"""WITH
t_plans AS
(SELECT plans_planhospitalization.id as pk_plan, client_id,
to_char(exec_at AT TIME ZONE %(tz)s, 'DD.MM.YYYY') AS exec_at_char,
work_status, action, exec_at, hospital_department_id, phone, research_id, diagnos, comment,
why_cancel,
to_char(dsplan.datetime AT TIME ZONE %(tz)s, 'DD.MM') AS date_char,
to_char(dsplan.datetime AT TIME ZONE %(tz)s, 'HH.MI') AS hhmm_start,
to_char(dsplan.datetime_end AT TIME ZONE %(tz)s, 'HH.MI') AS hhmm_end
FROM plans_planhospitalization
LEFT JOIN doctor_schedule_slotfact dsfact on dsfact.id = plans_planhospitalization.slot_fact_id
Left join doctor_schedule_slotplan dsplan on dsplan.id = dsfact.plan_id
WHERE
CASE
WHEN %(department_id)s > -1 THEN
exec_at AT TIME ZONE %(tz)s BETWEEN %(d_start)s AND %(d_end)s AND hospital_department_id=%(department_id)s and action=0
ELSE exec_at AT TIME ZONE %(tz)s BETWEEN %(d_start)s AND %(d_end)s and action=0
END
ORDER BY hospital_department_id, exec_at),
t_patient AS
(SELECT clients_card.id as card_id, clients_card.individual_id, clients_individual.family as ind_family,
clients_individual.name AS ind_name, clients_individual.patronymic as ind_twoname, to_char(clients_individual.birthday, 'DD.MM.YYYY') as born,
clients_individual.birthday, clients_individual.sex
FROM clients_individual
LEFT JOIN clients_card ON clients_individual.id = clients_card.individual_id
WHERE clients_card.id in (SELECT client_id FROM t_plans))
SELECT pk_plan, client_id, exec_at_char, work_status, action, hospital_department_id,
concat_ws(' ', ind_family, ind_name, ind_twoname) as fio_patient, birthday, exec_at,
to_char(EXTRACT(YEAR from age(t_plans.exec_at, t_patient.birthday)), '999') as ind_age,
t_patient.born, podrazdeleniya_podrazdeleniya.title as depart_title, phone, research_id, directory_researches.title as research_title, diagnos,
t_patient.sex, comment, why_cancel, date_char, hhmm_start, hhmm_end
FROM t_plans
LEFT JOIN t_patient ON t_plans.client_id = t_patient.card_id
LEFT JOIN podrazdeleniya_podrazdeleniya ON podrazdeleniya_podrazdeleniya.id = hospital_department_id
LEFT JOIN directory_researches ON t_plans.research_id = directory_researches.id
ORDER BY hospital_department_id, exec_at
""",
params={
'd_start': d_s,
'd_end': d_e,
'tz': TIME_ZONE,
'department_id': department
},
)
rows = namedtuplefetchall(cursor)
return rows
def get_unique_method_instrumental_diagnostic():
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT distinct method
from external_system_instrumentalresearchrefbook
order by method DESC
""", )
rows = namedtuplefetchall(cursor)
return rows
def get_resource_by_research_hospital():
with connection.cursor() as cursor:
cursor.execute("""
SELECT scheduleresource_id, researches_id, dr.title, dr.short_title, dr.schedule_title
FROM public.doctor_schedule_scheduleresource_service
LEFT JOIN directory_researches dr on dr.id = doctor_schedule_scheduleresource_service.researches_id
where researches_id in
(SELECT id from directory_researches WHERE is_hospital=True and hide=False)
""")
rows = namedtuplefetchall(cursor)
return rows
def get_resource_researches(resource_pks):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT scheduleresource_id, researches_id FROM doctor_schedule_scheduleresource_service
WHERE scheduleresource_id in %(resource_pks)s
ORDER BY scheduleresource_id
""",
params={"resource_pks": resource_pks},
)
rows = namedtuplefetchall(cursor)
return rows
def get_plans_hospitalizationfiles(plan_pk_tuple):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT plan_id, uploaded_file FROM plans_planhospitalizationfiles
WHERE plan_id in %(plan_pk_tuple)s
ORDER BY plan_id""",
params={'plan_pk_tuple': plan_pk_tuple},
)
rows = namedtuplefetchall(cursor)
return rows
def get_confirm_direction_patient_year(d_s,
d_e,
lab_podr,
card_pk1,
is_lab=False,
is_paraclinic=False,
is_doc_refferal=False,
is_user_forms=False):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
directions_napravleniya.id as direction,
directions_issledovaniya.time_confirmation,
to_char(directions_issledovaniya.time_confirmation AT TIME ZONE %(tz)s, 'DD.MM.YYYY') as ch_time_confirmation,
directions_issledovaniya.research_id,
directory_researches.title as research_title,
directions_issledovaniya.study_instance_uid_tag
FROM directions_napravleniya
INNER JOIN directions_issledovaniya ON (directions_napravleniya.id = directions_issledovaniya.napravleniye_id)
AND directions_issledovaniya.research_id IN
(SELECT directory_researches.id FROM directory_researches WHERE CASE
WHEN %(is_lab)s = TRUE THEN directory_researches.podrazdeleniye_id = ANY(ARRAY[%(lab_podr)s])
WHEN %(is_doc_refferal)s = TRUE THEN is_doc_refferal = TRUE
WHEN %(is_paraclinic)s = TRUE THEN is_paraclinic = TRUE
WHEN %(is_user_forms)s = TRUE THEN can_created_patient = TRUE
END
)
LEFT JOIN directory_researches ON
directions_issledovaniya.research_id=directory_researches.id
WHERE directions_issledovaniya.time_confirmation IS NOT NULL
AND directions_issledovaniya.time_confirmation AT TIME ZONE 'ASIA/Irkutsk' BETWEEN %(d_start)s AND %(d_end)s
AND NOT EXISTS (SELECT directions_issledovaniya.napravleniye_id FROM directions_issledovaniya
WHERE time_confirmation IS NULL AND directions_issledovaniya.napravleniye_id = directions_napravleniya.id)
AND client_id=%(card_pk)s
ORDER BY directions_issledovaniya.time_confirmation DESC, directions_napravleniya.id
""",
params={
'd_start': d_s,
'd_end': d_e,
'tz': TIME_ZONE,
'is_lab': is_lab,
'is_paraclinic': is_paraclinic,
'is_doc_refferal': is_doc_refferal,
'is_user_forms': is_user_forms,
'lab_podr': lab_podr,
'card_pk': card_pk1,
},
)
rows = namedtuplefetchall(cursor)
return rows
def get_hospital_resource_by_research(research_pk):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT distinct(scheduleresource_id),
dss.title as resource_title
FROM public.doctor_schedule_scheduleresource_service
LEFT JOIN doctor_schedule_scheduleresource dss on dss.id = doctor_schedule_scheduleresource_service.scheduleresource_id
where researches_id = %(research_pk)s
""",
params={'research_pk': research_pk},
)
rows = namedtuplefetchall(cursor)
return rows
def get_nsi_code_fsidi(method):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
code_nsi, title, localization, area, code_nmu
from external_system_instrumentalresearchrefbook
where method=%(method)s
order by title
""",
params={'method': method},
)
rows = namedtuplefetchall(cursor)
return rows
def direction_by_card(d_s, d_e, card_id):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
directions_issledovaniya.id as iss_id,
directions_issledovaniya.napravleniye_id,
directions_issledovaniya.time_confirmation,
to_char(directions_issledovaniya.time_confirmation AT TIME ZONE %(tz)s, 'DD.MM.YYYY') date_confirm,
to_char(directions_issledovaniya.time_save AT TIME ZONE %(tz)s, 'DD.MM.YYYY-HH24:MI:SS') as ch_time_save,
directions_issledovaniya.study_instance_uid,
directory_researches.title as research_title,
directory_researches.id as research_id,
directory_researches.is_hospital,
directory_researches.is_slave_hospital,
directory_researches.is_treatment,
directory_researches.is_stom,
directory_researches.is_doc_refferal,
directory_researches.is_paraclinic,
directory_researches.is_form,
directory_researches.is_microbiology,
directory_researches.is_application,
directory_researches.is_expertise,
directory_researches.podrazdeleniye_id,
directions_napravleniya.parent_slave_hosp_id,
directions_napravleniya.client_id,
directions_napravleniya.parent_id,
directions_napravleniya.data_sozdaniya,
to_char(data_sozdaniya AT TIME ZONE %(tz)s, 'DD.MM.YY') as date_create,
directions_napravleniya.cancel
FROM directions_issledovaniya
LEFT JOIN directory_researches
ON directions_issledovaniya.research_id = directory_researches.id
LEFT JOIN directions_napravleniya
ON directions_issledovaniya.napravleniye_id = directions_napravleniya.id
WHERE directions_napravleniya.data_sozdaniya AT TIME ZONE %(tz)s BETWEEN %(d_start)s AND %(d_end)s
AND directions_napravleniya.client_id = %(card_id)s
AND NOT directory_researches.is_expertise AND NOT directory_researches.is_hospital AND NOT
directory_researches.is_slave_hospital AND NOT directory_researches.is_application
ORDER BY directions_issledovaniya.napravleniye_id DESC""",
params={
'd_start': d_s,
'd_end': d_e,
'card_id': card_id,
'tz': TIME_ZONE,
},
)
rows = namedtuplefetchall(cursor)
return rows
def get_district_limit_research(district_pk):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
limit_count,
type_period_limit,
research_id
from users_districtresearchlimitassign
WHERE district_group_id = %(district_pk)s
""",
params={'district_pk': district_pk},
)
rows = namedtuplefetchall(cursor)
return rows
def search_text_stationar(date_create_start, date_create_end, final_text):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
distinct on (history_num ) dp.napravleniye_id as history_num,
directions_issledovaniya.napravleniye_id as direction_number,
users_doctorprofile.fio as doc_fio,
directions_issledovaniya.medical_examination as date_service,
directions_napravleniya.client_id,
concat(clients_individual.family, ' ', clients_individual.name, ' ', clients_individual.patronymic) as patient_fio,
to_char(clients_individual.birthday, 'DD.MM.YYYY') as patient_birthday,
date_part('year', age(directions_issledovaniya.medical_examination, clients_individual.birthday))::int as patient_age,
clients_individual.sex as patient_sex,
directions_issledovaniya.napravleniye_id,
directions_issledovaniya.research_id,
directions_paraclinicresult.value as field_value,
directions_paraclinicresult.field_id,
directory_paraclinicinputfield.title,
directory_researches.title as research_title,
to_char(directions_issledovaniya.time_confirmation AT TIME ZONE %(tz)s, 'DD.MM.YYYY') as date_confirm
FROM directions_issledovaniya
LEFT JOIN directions_napravleniya ON directions_napravleniya.id = directions_issledovaniya.napravleniye_id
LEFT JOIN directory_researches ON directions_issledovaniya.research_id = directory_researches.id
LEFT JOIN users_doctorprofile ON directions_issledovaniya.doc_confirmation_id=users_doctorprofile.id
LEFT JOIN clients_card ON clients_card.id=directions_napravleniya.client_id
LEFT JOIN clients_individual ON clients_individual.id=clients_card.individual_id
LEFT JOIN directions_paraclinicresult on directions_paraclinicresult.issledovaniye_id=directions_issledovaniya.id
LEFT JOIN directory_paraclinicinputfield on directions_paraclinicresult.field_id=directory_paraclinicinputfield.id
LEFT JOIN directions_issledovaniya dp on directions_napravleniya.parent_id=dp.id
WHERE
directory_researches.is_slave_hospital = true
and directions_issledovaniya.time_confirmation IS NOT NULL
and (directions_napravleniya.data_sozdaniya AT TIME ZONE %(tz)s BETWEEN %(date_create_start)s AND %(date_create_end)s)
and directions_paraclinicresult.value ~* %(final_text)s
order by dp.napravleniye_id
""",
params={
'date_create_start': date_create_start,
'date_create_end': date_create_end,
'final_text': final_text,
'tz': TIME_ZONE,
},
)
rows = namedtuplefetchall(cursor)
return rows
def get_slot_fact(plan_pk_tuple):
if not plan_pk_tuple:
return []
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
plan_id
FROM public.doctor_schedule_slotfact
WHERE plan_id in %(plan_pk_tuple)s
""",
params={
'plan_pk_tuple': plan_pk_tuple,
},
)
rows = namedtuplefetchall(cursor)
return rows
def get_directions_by_user(d_s, d_e, who_create_dir):
with connection.cursor() as cursor:
cursor.execute(
"""SELECT
directions_napravleniya.id as direction_id
FROM directions_napravleniya
WHERE directions_napravleniya.data_sozdaniya AT TIME ZONE %(tz)s BETWEEN %(d_start)s AND %(d_end)s
and directions_napravleniya.doc_who_create_id in %(who_create)s
ORDER BY directions_napravleniya.id DESC""",
params={
'd_start': d_s,
'd_end': d_e,
'who_create': who_create_dir,
'tz': TIME_ZONE,
},
)
rows = namedtuplefetchall(cursor)
return rows
def get_expertis_child_iss_by_issledovaniya(parent_iss_tuple):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
directions_napravleniya.id,
directions_napravleniya.parent_id,
directions_issledovaniya.id as child_iss
FROM public.directions_napravleniya
Left JOIN directions_issledovaniya ON
directions_issledovaniya.napravleniye_id = directions_napravleniya.id
where directions_napravleniya.parent_id in %(parent_iss_tuple)s
order by directions_napravleniya.parent_id
""",
params={'parent_iss_tuple': parent_iss_tuple},
)
rows = namedtuplefetchall(cursor)
return rows
def get_patient_contract(
d_s,
d_e,
card_pk,
):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
directions_napravleniya.num_contract,
directions_personcontract.id,
directions_personcontract.cancel,
directions_personcontract.create_at,
directions_personcontract.sum_contract,
to_char(directions_personcontract.create_at AT TIME ZONE %(tz)s, 'DD.MM.YY') as date_create,
directions_issledovaniya.napravleniye_id,
directions_issledovaniya.coast,
directions_issledovaniya.discount,
directory_researches.title,
directions_personcontract.dir_list
FROM directions_issledovaniya
LEFT JOIN directory_researches ON
directory_researches.id=directions_issledovaniya.research_id
LEFT JOIN directions_napravleniya ON
directions_napravleniya.id=directions_issledovaniya.napravleniye_id
LEFT JOIN directions_personcontract ON
directions_personcontract.num_contract=directions_napravleniya.num_contract
WHERE directions_issledovaniya.napravleniye_id::varchar in (
select regexp_split_to_table(directions_personcontract.dir_list, ',') from directions_personcontract
where directions_personcontract.patient_card_id=%(card_pk)s and directions_personcontract.create_at AT TIME ZONE %(tz)s BETWEEN %(d_start)s AND %(d_end)s
)
order by directions_personcontract.create_at DESC
""",
params={
'd_start': d_s,
'd_end': d_e,
'tz': TIME_ZONE,
'card_pk': card_pk,
},
)
rows = namedtuplefetchall(cursor)
return rows
def get_count_researches_by_doc(doctor_pks, d_s, d_e):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
directions_issledovaniya.research_id,
count(directions_napravleniya.id) as count
FROM directions_napravleniya
LEFT JOIN directions_issledovaniya
ON directions_napravleniya.id=directions_issledovaniya.napravleniye_id
WHERE doc_who_create_id in %(doctor_pks)s and data_sozdaniya AT TIME ZONE %(tz)s BETWEEN %(d_start)s AND %(d_end)s
group by directions_issledovaniya.research_id
""",
params={'doctor_pks': doctor_pks, 'd_start': d_s, 'd_end': d_e, 'tz': TIME_ZONE},
)
rows = namedtuplefetchall(cursor)
return rows
def get_expertis_results_by_issledovaniya(issledovaniye_tuple):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
directions_paraclinicresult.issledovaniye_id,
directions_paraclinicresult.value,
directions_paraclinicresult.field_id,
directory_ParaclinicInputField.title
FROM directions_paraclinicresult
LEFT JOIN directory_paraclinicinputfield ON
directions_paraclinicresult.field_id=directory_paraclinicinputfield.id
WHERE directions_paraclinicresult.issledovaniye_id in %(issledovaniye_tuple)s
order by directions_paraclinicresult.issledovaniye_id
""",
params={'issledovaniye_tuple': issledovaniye_tuple},
)
rows = namedtuplefetchall(cursor)
return rows
def get_pharmacotherapy_exec_by_directions(directions_tuple):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
pharmacotherapy_procedurelist.history_id,
pharmacotherapy_procedurelisttimes.prescription_id,
pharmacotherapy_procedurelist.drug_id,
pharmacotherapy_drugs.mnn,
pharmacotherapy_drugs.trade_name,
pharmacotherapy_methodsreception.title as method_title,
pharmacotherapy_formrelease.title as form_title,
pharmacotherapy_procedurelist.dosage,
pharmacotherapy_procedurelist.units,
pharmacotherapy_procedurelist.comment,
to_char(pharmacotherapy_procedurelisttimes.times_medication at time zone %(tz)s, 'DD.MM.YYYY') as date_char,
to_char(pharmacotherapy_procedurelisttimes.times_medication at time zone %(tz)s, 'HH:MI') as time_char,
pharmacotherapy_procedurelisttimes.times_medication,
pharmacotherapy_procedurelisttimes.executor_id
FROM pharmacotherapy_procedurelisttimes
LEFT JOIN pharmacotherapy_procedurelist
ON pharmacotherapy_procedurelist.id = pharmacotherapy_procedurelisttimes.prescription_id
LEFT JOIN pharmacotherapy_drugs
ON pharmacotherapy_procedurelist.drug_id = pharmacotherapy_drugs.id
LEFT JOIN pharmacotherapy_formrelease
ON pharmacotherapy_procedurelist.form_release_id = pharmacotherapy_formrelease.id
LEFT JOIN pharmacotherapy_methodsreception
ON pharmacotherapy_procedurelist.method_id = pharmacotherapy_methodsreception.id
where pharmacotherapy_procedurelist.history_id in %(directions_tuple)s and
pharmacotherapy_procedurelisttimes.executor_id is not null
ORDER BY pharmacotherapy_procedurelist.history_id, drug_id, times_medication
""",
params={
'directions_tuple': directions_tuple,
'tz': TIME_ZONE
},
)
rows = namedtuplefetchall(cursor)
return rows
def check_limit_assign_researches(
district_group_id,
):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT users_districtresearchlimitassign.id,
users_districtresearchlimitassign.limit_count,
users_districtresearchlimitassign.type_period_limit,
users_districtresearchlimitassign.research_id,
users_districtresearchlimitassign.district_group_id
from users_districtresearchlimitassign
where
district_group_id = %(district_group_id)s
""",
params={'district_group_id': district_group_id},
)
rows = namedtuplefetchall(cursor)
return rows
def report_buh_gistology(directions):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
dn.id as direction_id,
dn.hospital_id,
dn.istochnik_f_id,
to_char(dn.visit_date AT TIME ZONE %(tz)s, 'DD.MM.YYYY HH24:MI') AS char_visit_date,
dn.visit_date,
di.title as iss_finsource_title,
cp.title as iss_price_category,
hh.title as hosp_title,
hh.id as hosp_id,
dp.value,
dp.field_id as field_id,
dpif.title as field_title
FROM directions_issledovaniya
LEFT JOIN directions_napravleniya dn on directions_issledovaniya.napravleniye_id = dn.id
LEFT JOIN hospitals_hospitals hh on dn.hospital_id = hh.id
LEFT JOIN directions_istochnikifinansirovaniya di on directions_issledovaniya.fin_source_id = di.id
LEFT JOIN contracts_pricecategory cp on directions_issledovaniya.price_category_id = cp.id
LEFT JOIN directions_paraclinicresult dp on directions_issledovaniya.id = dp.issledovaniye_id
LEFT JOIN directory_paraclinicinputfield dpif on dp.field_id = dpif.id
WHERE directions_issledovaniya.napravleniye_id in %(directions)s
ORDER BY hh.title, dn.visit_date, dn.id
""",
params={
'directions': directions,
'tz': TIME_ZONE
},
)
rows = namedtuplefetchall(cursor)
return rows
def get_messages_by_card_id(card_id):
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT
message,
created_at,
plan_id,
client_id,
to_char(created_at AT TIME ZONE %(tz)s, 'DD.MM.YYYY') AS date_create,
to_char(created_at AT TIME ZONE %(tz)s, 'HH24:MI') AS time_create
FROM plans_messages
WHERE client_id = %(card_id)s
ORDER BY created_at DESC """,
params={
'card_id': card_id,
'tz': TIME_ZONE
},
)
rows = namedtuplefetchall(cursor)
return rows
