Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- with m_zpmd as (
- select zpmd.id,
- max(mo.mcode::text) as mcode,
- case
- when zpmd.region = 'TULA'::zpmd.region then max(mo_tula.short_name::text)
- else max(mo.nam_mok::text) end as mo_short_name,
- case
- when zpmd.region = 'TULA'::zpmd.region then max(mo_tula.full_name::text)
- else max(mo.nam_mop::text) end as mo_full_name,
- max(mo.id) as mo_id,
- zpmd.request_name,
- zpmd.request_number,
- max(v006.name_type_usl::text) as usl_name,
- zpmd.usl_ok as usl_id,
- zpmd.date_create,
- zpmd.date_send,
- zpmd.status::text as status,
- COALESCE(concat_ws(' ', max(u.last_name), max(u.first_name)),'') as user_full_name,
- COALESCE(max(u.id::text), ''::text) as user_id,
- max(zpmd.region)::text as region,
- max(zpmd.theme_category_id::text) as theme_category_id,
- max(expertise_class_decomposed.expertise_type)::text as expertise_type,
- max(category.id::text) as category_id,
- max(theme.id::text) as theme_id,
- max(category.name::text) as category_name,
- max(theme.name::text) as theme_name,
- zpmd.related_document_id,
- zpmd.has_related_document,
- rpzpmd_parent.parent_zpmd_id,
- rpzpmd_repeated.repeated_zpmd_id,
- zpmd.podr_id,
- zpmd.is_migrated
- from zpmd.zpmd zpmd
- join directory.f003 mo on zpmd.medical_organization_code = mo.mcode
- join directory.v006 v006 on v006.code_type_usl = zpmd.usl_ok
- and current_date >= v006.begin_date and current_date <= v006.end_date
- left join directory.mo_tula mo_tula on zpmd.medical_organization_code = mo_tula.mo_code
- and zpmd.region = 'TULA'::zpmd.region
- left join zpmd.users u on u.id = zpmd.user_id
- join expert.theme_category on zpmd.theme_category_id = theme_category.id
- join expert.category on theme_category.category_id = category.id
- join expert.expertise_class_decomposed
- on expertise_class_decomposed.expertise_class = theme_category.expertise_class
- left join expert.theme on theme_category.theme_id = theme.id
- left join zpmd.repeated_parent_zpmd_map rpzpmd_repeated on rpzpmd_repeated.parent_zpmd_id = zpmd.id
- left join zpmd.repeated_parent_zpmd_map rpzpmd_parent on rpzpmd_parent.repeated_zpmd_id = zpmd.id
- group by zpmd.id, rpzpmd_parent.parent_zpmd_id, rpzpmd_repeated.repeated_zpmd_id
- order by max(zpmd.date_create) desc),
- item_info as (
- select zpmdi.zpmd_id,
- count( zpmdi.id) filter
- (where zpmdi.status not in ('CREATED'::zpmd.zpmd_item_status, 'DELETED'::zpmd.zpmd_item_status)
- and zpmdi.availability ='AVAILABLE'::zpmd.zpmd_item_availability) as received_count,
- count( zpmdi.id) filter (where zpmdi.availability = 'AVAILABLE'::zpmd.zpmd_item_availability
- and zpmdi.status <> 'DELETED'::zpmd.zpmd_item_status) as total_count,
- count( zpmdi.id) filter ( where ia.zpmd_item_id is not null ) as with_created_act_count,
- max( zpmdi.id_profil) as max_profile_id,
- min( zpmdi.id_profil) as min_profile_id,
- max( zpmdi.date_receive) as max_date_receive
- from zpmd.zpmd_item zpmdi
- left join zpmd.zpmd_item_acts ia on zpmdi.id = ia.zpmd_item_id and not ia.is_deleted
- where (( ?::text is null
- and ?::text is null
- and ?::text is null
- and ?::text is null
- and ?::date is null
- and ?::date is null)
- or exists (select id
- from zpmd.get_zpmd_items_by_pacient(?::text, ?::text, ?::text, ?::text, ?::date, ?::date)
- where id = zpmdi.id))
- group by zpmdi.zpmd_id),
- zpmd_view as (
- select m.id,
- COALESCE(podr_tula.name, podr_omsk.nam_mop) as podr_name,
- COALESCE(podr_tula.id, podr_omsk.id) as podr_id,
- COALESCE(podr_tula.podr_id, podr_omsk.lpu_1::varchar) as podr_code,
- m.mcode,
- m.mo_short_name,
- m.mo_full_name,
- m.mo_id,
- m.request_name,
- m.request_number,
- m.usl_name,
- m.usl_id,
- m.date_create,
- m.date_send,
- case when (m.status = 'RECEIVED') then
- item_info.max_date_receive - m.date_send
- else current_date - m.date_send
- end as diff,
- m.status,
- m.user_full_name,
- m.user_id,
- m.region,
- m.theme_category_id,
- m.expertise_type,
- m.theme_id,
- m.category_id,
- m.theme_name,
- m.category_name,
- COALESCE(item_info.total_count, 0) as total,
- COALESCE(item_info.received_count, 0) as received,
- case item_info.min_profile_id = item_info.max_profile_id
- when true then v002.name_profil
- when false then 'нескольким профилям'
- else '' end as profile,
- m.parent_zpmd_id,
- m.repeated_zpmd_id,
- m.related_document_id,
- m.has_related_document,
- m.is_migrated,
- item_info.with_created_act_count > 0 as has_act,
- item_info.with_created_act_count > 0 and
- item_info.with_created_act_count = item_info.total_count as all_items_has_act
- from m_zpmd m
- left join item_info on item_info.zpmd_id = m.id
- left join directory.v002 on v002.id = item_info.max_profile_id
- left join directory.podr_omsk podr_omsk on m.region = 'OMSK' and podr_omsk.id = m.podr_id
- left join directory.t_podr_tula podr_tula on m.region = 'TULA' and podr_tula.id = m.podr_id)
- select * from zpmd_view zv where 1=1 and zv.region = ? and zv.status in (?, ?, ?, ?)
- and zv.is_migrated in (?) and zv.all_items_has_act in (?)
- order by date_create DESC limit ? offset ?
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
