Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT pat.id_lpu_fk,
- lpu.st_bol_name,
- otd.kodotd,
- COALESCE(pm.otd_num,0) AS otd_num,
- otd.naimotd || COALESCE(' № ' || CAST(pm.otd_num AS VARCHAR(2)), '')
- AS NAIMOTD,
- pat.numcard,
- RIGHT(EXTRACT(YEAR FROM pat.dt_receipt),2) || RIGHT('000' ||
- COALESCE(pm.sprotd_id_fk,''),3) || RIGHT('00000' ||
- COALESCE(pat.numcard,''),5) AS NUM_CARD,
- peo.lname || ' ' || peo.fname || COALESCE(' ' || peo.mname, '')
- AS FIO,
- pm.diagnos AS DIAGNOS,
- CASE -- BDC 20.02.2019
- WHEN n18.id_reas = 0 THEN 'Перв' -- BDC 20.02.2019
- WHEN n18.id_reas = 1 THEN 'Рецид' -- BDC 20.02.2019
- WHEN n18.id_reas = 2 THEN 'Прогр' -- BDC 20.02.2019
- WHEN n18.id_reas = 3 THEN 'Динам' -- BDC 20.02.2019
- WHEN n18.id_reas = 4 THEN 'Дисп' -- BDC 20.02.2019
- WHEN n18.id_reas = 5 THEN 'Диагн' -- BDC 20.02.2019
- WHEN n18.id_reas = 6 THEN 'Симпт' -- BDC 20.02.2019
- ELSE n18.reas_name
- END AS DS1_T,
- n03.kod_t AS T,
- n04.kod_n AS N,
- n05.kod_m AS M,
- n02.kod_st AS KOD_ST,
- CASE
- WHEN pm.sprpaiment_id_fk = 1 THEN 'ОМС'
- WHEN pm.sprpaiment_id_fk = 9 THEN 'ВМП ОМС'
- ELSE '???'
- END AS OPL,
- pm.ksg_select AS KSG,
- -- "первая" услуга
- (SELECT FIRST 1 srv_id
- FROM
- (SELECT
- su.srv_id_fk AS srv_id,
- su.cl_oper AS cl
- FROM wt_surgical_operation su
- JOIN wt_patient_move pm1 ON pm1.wt_patient_move_id = su.wt_pm_id_fk
- JOIN wt_patient pat1 ON pat1.wt_patient_id = pm1.wt_patient_id_fk
- WHERE su.wt_pm_id_fk = pm.wt_patient_move_id
- AND pat1.payment IN (1,9) -- только ОМС или ВМП по ОМС
- AND pm1.cl_otd > 0 -- только оплачиваемый случай
- AND ((su.srv_id_fk IS NOT NULL) AND (su.srv_id_fk <> '')) -- только с мед услугой для операции
- AND ((pat1.payment = 9) OR (su.cl_oper > 0)) -- только оплачиваемая операция (ОМС)
- UNION ALL
- SELECT
- sr.srv_id_fk AS srv_id,
- sr.cl_srv AS cl
- FROM wt_srv sr
- JOIN wt_patient_move pm2 ON pm2.wt_patient_move_id = sr.wt_pm_id_fk
- JOIN wt_patient pat2 ON pat2.wt_patient_id = pm2.wt_patient_id_fk
- WHERE sr.wt_pm_id_fk = pm.wt_patient_move_id
- AND pat2.payment IN (1,9) -- только ОМС или ВМП по ОМС
- AND pm2.cl_otd > 0 -- только оплачиваемый случай
- AND ((pat2.payment = 9) OR (sr.cl_srv > 0)) -- только оплачиваемая операция (ОМС)
- ORDER BY 2
- )
- ) AS SRV_ID,
- -- доп критерии
- --COALESCE( -- BDC 20.02.2019
- --(SELECT FIRST 1 SUBSTRING(dt.data_value_kod FROM 1 FOR 2) || -- BDC 20.02.2019
- -- '=' || SUBSTRING(dt.data_value_kod FROM 3 FOR 99) || ';' -- BDC 20.02.2019
- -- FROM dt_spr dt -- BDC 20.02.2019
- -- WHERE dt.def_set_data_id_fk IN (87,88,89) -- BDC 20.02.2019
- -- AND dt.data_value_kod IS NOT NULL -- BDC 20.02.2019
- -- AND dt.wt_patient_id_fk = pat.wt_patient_id -- BDC 20.02.2019
- -- AND dt.wt_patient_move_id_fk = pm.wt_patient_move_id), '') || -- BDC 20.02.2019
- (SELECT ADD_CR FROM list_cr(pm.wt_patient_move_id)) || -- BDC 20.02.2019
- COALESCE(
- (SELECT FIRST 1 'EKO=' || CAST(srv.eko AS CHAR(1)) || ';'
- FROM wt_srv srv
- WHERE srv.wt_pm_id_fk = pm.wt_patient_move_id
- AND srv.srv_id_fk = 'A11.20.017'), '') -- ЭКО
- AS ADD_CR,
- COALESCE(sch.mnn,'') || -- BDC 20.02.2010
- COALESCE( -- BDC 20.02.2010
- (SELECT FIRST 1 -- BDC 20.02.2010
- mnn.mnn -- BDC 20.02.2010
- FROM wt_srv sr -- BDC 20.02.2010
- JOIN wt_patient_move pm2 ON pm2.wt_patient_move_id = sr.wt_pm_id_fk -- BDC 20.02.2010
- JOIN wt_patient pat2 ON pat2.wt_patient_id = pm2.wt_patient_id_fk -- BDC 20.02.2010
- LEFT JOIN st_sprmnn mnn ON mnn.kod = sr.mnn -- BDC 20.02.2010
- WHERE sr.wt_pm_id_fk = pm.wt_patient_move_id -- BDC 20.02.2010
- AND pat2.payment IN (1,9) -- BDC 20.02.2010
- AND pm2.cl_otd > 0 -- BDC 20.02.2010
- ORDER BY sr.cl_srv DESC),'') AS mnn, -- BDC 20.02.2010
- ------------------
- doc.naimdoc AS DOC, -- BDC 20.02.2019
- pm.cl_otd AS CL_OTD,
- pm.kd AS KD, -- BDC 15.08.2019
- pm.koef_07 AS KOEF_07 -- BDC 15.08.2019
- FROM wt_patient_move pm
- JOIN wt_patient pat ON pat.wt_patient_id = pm.wt_patient_id_fk
- JOIN peoples_loc peo ON peo.people_id = pat.people_id_fk
- JOIN st_bol lpu ON lpu.kodbol = pat.id_lpu_fk
- JOIN st_sprotd otd ON otd.kodotd = pm.sprotd_id_fk
- LEFT JOIN wt_patient_move_can can ON can.wt_patient_move_id_fk = pm.wt_patient_move_id
- LEFT JOIN n003$onkt n03 ON n03.id_t = can.onk_t
- AND pat.dtextract BETWEEN n03.datebeg AND COALESCE(n03.dateend,'31.12.2099') -- BDC 20.02.2019
- LEFT JOIN n004$onkn n04 ON n04.id_n = can.onk_n
- AND pat.dtextract BETWEEN n04.datebeg AND COALESCE(n04.dateend,'31.12.2099') -- BDC 20.02.2019
- LEFT JOIN n005$metastasis n05 ON n05.id_m = can.onk_m
- AND pat.dtextract BETWEEN n05.datebeg AND COALESCE(n05.dateend,'31.12.2099') -- BDC 20.02.2019
- LEFT JOIN n002$onkstad n02 ON n02.id_st = can.stad
- AND pat.dtextract BETWEEN n02.datebeg AND COALESCE(n02.dateend,'31.12.2099') -- BDC 20.02.2019
- LEFT JOIN n018$onkreas n18 ON n18.id_reas = can.ds1_t -- BDC 20.02.2019
- AND pat.dtextract BETWEEN n18.datebeg AND COALESCE(n18.dateend,'31.12.2099') -- BDC 20.02.2019
- LEFT JOIN st_sprdoc doc ON doc.koddoc = pm.sprdoc_id_fk -- BDC 20.02.2019
- AND doc.id_lpu_fk = pat.id_lpu_fk -- BDC 20.02.2019
- LEFT JOIN st_sprscheme sch ON sch.kod = -- BDC 20.02.2019
- (SELECT FIRST 1 dt.data_value_kod -- BDC 20.02.2019
- FROM dt_spr dt -- BDC 20.02.2019
- WHERE dt.def_set_data_id_fk = 87 -- схема -- BDC 20.02.2019
- AND dt.data_value_kod IS NOT NULL -- BDC 20.02.2019
- AND dt.wt_patient_move_id_fk = pm.wt_patient_move_id) -- BDC 20.02.2019
- AND pat.dtextract BETWEEN sch.datebeg AND COALESCE(sch.dateend,'31.12.2099') -- BDC 20.02.2019
- WHERE lpu.key3 = :LPU_ID -- включая и подчиненные
- AND ((pat.dtextract IS NOT NULL) AND (pat.dtextract BETWEEN :DATE_BEG AND :DATE_END))
- AND ((:KOD = 0) OR (kodotd = :KOD))
- AND ((:NUM = 0) OR (otd_num = :NUM))
- AND pm.sprpaiment_id_fk IN (1,9) -- ОМС или ВМП по ОМС
- AND pm.cl_otd IS NOT NULL AND pm.cl_otd > 0
- ORDER BY id_lpu_fk, kodotd, otd_num, NUM_CARD
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
