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