Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- select zsl.id,
- zsl.period,
- zsl.manual_upload,
- zsl.family,
- zsl.firstname,
- zsl.patronymic,
- zsl.born_date,
- zsl.policy_number,
- zsl.usl_ok, -- net
- zsl.usl_ok_name,
- zsl.lpu_code,
- zsl.lpu_name,
- zsl.begin_date,
- zsl.end_date,
- zsl.kd_z,
- zsl.rslt,
- zsl.rslt_name,
- zsl.ishod,
- zsl.ishod_name,
- zsl.disp_code,
- zsl.disp_type,
- zsl.vidpom,
- zsl.vidpom_name,
- zsl.sumv,
- zsl.otd_names,
- zsl.category_ids,
- max(sluch.id) filter (where sluch.is_final_record = true) as final_medical_case_id,
- sluch.idzsl,
- coalesce(jsonb_agg(distinct v002.name_profil) filter (where v002.name_profil is not null),'[]' :: jsonb) as mp_profiles,
- coalesce(jsonb_agg(distinct v020.kp_prname) filter (where v020.kp_prname is not null), '[]' :: jsonb) as bed_profiles,
- jsonb_agg(distinct sluch.nhistory) as med_history_numbers,
- coalesce(jsonb_agg(distinct v018.name_vid_pom) filter (where v018.name_vid_pom is not null), '[]' :: jsonb) as vid_hmp_names,
- coalesce(jsonb_agg(distinct v019.name_vid_hpom) filter (where v019.name_vid_hpom is not null),'[]' :: jsonb) as method_hmp_names,
- coalesce(jsonb_agg(distinct jsonb_build_object('pPer', sluch.other ->> 'P_PER', 'pPerName', p_per_dict.pname)) filter (where sluch.other ->> 'P_PER' is not null), '[]' :: jsonb) as p_pers,
- max(sluch.diag ->> 'DS1') as ds1,
- max(mkb_d.mkb_name) as ds1_name,
- coalesce(
- jsonb_agg(distinct spec.name) filter (where spec.name <> '' :: text),
- jsonb_agg(distinct v021.name_spec) filter (where v021.name_spec <> '' :: text),
- '[]' :: jsonb
- ) as special_names,
- coalesce(jsonb_agg(distinct jsonb_build_object('ksg', sluch.ksg_kpg ->> 'N_KSG', 'ksgName', case when :regionCode = 74 then ksg.name else v023.name_ksg end)) filter (where(sluch.ksg_kpg ->> 'N_KSG') is not null), '[]' :: jsonb) as ksgs,
- coalesce(jsonb_agg(distinct sluch_onk.onk ->> 'DS1_T') filter (where sluch_onk.onk ->> 'DS1_T' is not null), '[]' :: jsonb) as ds_onks,
- count(distinct sluch.id) as med_case_count,
- coalesce(array_agg(distinct v002.code_profil) filter (where v002.code_profil is not null), '{}'::smallint[]) as mp_profile_codes,
- coalesce((array_agg(distinct sluch.other -> 'PROFIL_K') filter (where sluch.other ->> 'PROFIL_K' is not null))::smallint[], '{}'::smallint[]) as bed_profile_codes,
- coalesce(jsonb_agg(distinct sluch.hmp ->> 'VID_HMP') filter (where sluch.hmp ->> 'VID_HMP' is not null), '[]'::jsonb) as vid_hmp_codes,
- coalesce((array_agg(distinct sluch.hmp ->> 'METOD_HMP') filter (where sluch.hmp ->> 'METOD_HMP' is not null))::smallint[], '{}'::smallint[]) as method_hmp_codes,
- coalesce(jsonb_agg(distinct sluch.diag ->> 'DS2') filter (where sluch.diag ->> 'DS2' is not null), '[]'::jsonb) as ds2,
- coalesce(jsonb_agg(distinct sluch.diag ->> 'DS3') filter (where sluch.diag ->> 'DS3' is not null), '[]'::jsonb) as ds3,
- coalesce(array_agg(distinct sluch.ksg_kpg ->> 'N_KSG') filter (where (sluch.ksg_kpg ->> 'N_KSG') is not null), '{}'::text[]) as ksg_codes,
- coalesce(array_agg(distinct sluch.ksg_kpg ->> 'N_KPG') filter (where (sluch.ksg_kpg ->> 'N_KPG') is not null), '{}'::text[]) as kpg_codes,
- coalesce((array_agg(distinct sluch.med ->> 'PRVS') filter (where sluch.med ->> 'PRVS' is not null))::integer[], '{}'::integer[]) as special_codes
- from ( (
- select zsl.id as id,
- schet_period.period_date as period,
- zsl.manual_upload,
- person.fam as family,
- person.im as firstname,
- person.ot as patronymic,
- person.dr as born_date,
- person.npolis as policy_number,
- v006.code_type_usl as usl_ok,
- v006.name_type_usl as usl_ok_name,
- f003.mcode as lpu_code,
- f003.nam_mok as lpu_name,
- zsl.date_begin as begin_date,
- zsl.date_end as end_date,
- (zsl.other ->> 'KD_Z')::smallint as kd_z,
- CASE WHEN (schet.iddisp is null) THEN v009.code_resl ELSE v017.code_disp_resl END as rslt,
- CASE WHEN (schet.iddisp is null) THEN v009.name_resl::text ELSE v017.name_disp_resl::text END as rslt_name,
- v012.code_ishod::smallint as ishod,
- v012.name_ishod as ishod_name,
- v016.code_disp as disp_code,
- v016.name_disp as disp_type,
- (zsl.med_pom ->> 'VIDPOM')::smallint as vidpom,
- v008.vmpname as vidpom_name,
- (zsl.sum ->> 'SUMV')::numeric as sumv,
- '[]'::jsonb as otd_names,
- (select coalesce(jsonb_agg(distinct selected_zsl.category_id), '[]'::jsonb) from bills.selected_zsl where selected_zsl.zsl_id = zsl.id ) as category_ids
- from bills.schet
- join bills.schet_period on schet_period.id = schet.idperiod
- join bills.zsl on zsl.idschet = schet.id
- join pacients.person on person.id = zsl.idperson
- join directory.f003 on f003.id = zsl.idmedorg
- left join directory.v006 v006 on v006.id = zsl.idtypeusl
- left join directory.v008 v008 on v008.idvmp::text = zsl.med_pom ->> 'VIDPOM'
- left join directory.v009 on v009.id = zsl.idresult
- left join directory.v017 on v017.id = zsl.idresult
- left join directory.v012 on v012.id = zsl.idishod
- left join directory.v016 on v016.id = schet.iddisp
- left join bills.not_visible_zsl nvz on zsl.id=nvz.zsl_id
- where schet.idfilial = directory.getfilialidbycode(:regionCode)
- and nvz.id is null and schet_period.period_date between :periodBeginDate and :periodEndDate and exists(
- select 1 from bills.sluch
- left join directory.v002 on v002.id = sluch.idprofil
- where sluch.idzsl = zsl.id ) and (bills.zsl.sum->>'OPLATA')::smallint is distinct from 2 AND EXISTS (SELECT 1 FROM bills.selected_zsl WHERE zsl_id = zsl.id AND category_id = :categoryId)
- order by lower(person.fam), lower(person.im), lower(person.ot), zsl.date_begin, zsl.date_end, zsl.id
- limit :limit offset :offset)) zsl
- join bills.sluch on sluch.idzsl = zsl.id
- left join bills.usl on usl.idsluch = sluch.id
- left join directory.v002 on v002.id = sluch.idprofil
- left join directory.v020 v020 on v020.idk_pr::text = sluch.other ->> 'PROFIL_K'
- and sluch.date_end between v020.date_begin and v020.date_end
- left join directory.p_per p_per_dict on p_per_dict.p_per_id::text = sluch.other ->> 'P_PER'
- left join directory.v018 v018 on v018.code_vid_pom = sluch.hmp ->> 'VID_HMP'
- and sluch.date_end between v018.begin_date and v018.end_date
- left join directory.v019 v019 on v019.code_vid_hpom::text = sluch.hmp ->> 'METOD_HMP'
- and sluch.date_end between v019.begin_date and v019.end_date
- left join directory.mkb mkb_d on mkb_d.mkb_code = sluch.diag ->> 'DS1'
- left join directory.special spec on (sluch.other -> 'COD_SPEC')::int = spec.npp and :regionCode = 74
- left join directory.v021 v021 on v021.code_spec :: text = sluch.med ->> 'PRVS' and :regionCode != 74
- and sluch.date_end >= v021.begin_date
- and sluch.date_begin <= v021.end_date
- left join directory.v023 v023 on v023.idump = zsl.usl_ok
- and v023.code_ksg = (sluch.ksg_kpg ->> 'N_KSG') and sluch.date_end >= v023.begin_date and (v023.end_date is null or sluch.date_end <= v023.end_date)
- left join directory.ksg on ksg.usl_ok = zsl.usl_ok
- and ksg.ksg_num2 = (sluch.ksg_kpg ->> 'N_KSG') and sluch.date_end >= date(to_timestamp(ksg.date_beg2 / 1000) at time zone 'Europe/Moscow')
- and (ksg.date_end2 is null or sluch.date_end <= date(to_timestamp(ksg.date_end2 / 1000) at time zone 'Europe/Moscow'))
- left join bills.sluch_onk on sluch_onk.idsluch = sluch.id
- group by sluch.idzsl,
- zsl.id,
- zsl.period,
- zsl.family,
- zsl.firstname,
- zsl.patronymic,
- zsl.born_date,
- zsl.policy_number,
- zsl.usl_ok,
- zsl.usl_ok_name,
- zsl.lpu_code,
- zsl.lpu_name,
- zsl.begin_date,
- zsl.end_date,
- zsl.kd_z,
- zsl.rslt,
- zsl.rslt_name,
- zsl.ishod,
- zsl.ishod_name,
- zsl.disp_code,
- zsl.disp_type,
- zsl.vidpom,
- zsl.vidpom_name,
- zsl.sumv,
- zsl.otd_names,
- zsl.category_ids,
- zsl.manual_upload
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement