with params as(
	select
		cast($1 as bigint) as p_lpu_id,
		cast($2 as varchar) as p_lpufiliallist,
		cast($3 as bigint) as p_lpubuilding_id,
		cast($4 as bigint) as p_lpuunit_id,
		cast($5 as bigint) as p_lpusection_id,
		cast($6 as timestamp) as p_begdate,
		cast($7 as timestamp) as p_enddate
),
stroki
	as(
	select tab.nstr,tab.num,tab.namestr
	from
	  (
    values
	 (1,'20','Численность беспризорных и безнадзорных несовершеннолетних,доставленных в медицинскую организацию, всего (сумма строк 21 - 23)')
	,(2,'21','в том числе: '||E'\n' ||'сотрудниками органов внутренних дел')
	,(3,'22','гражданами')
	,(4,'23','самостоятельно обратились')		
	,(5,'24','Из общего числа доставленных (обратившихся) (из строки 20):осмотрено врачами:) '||E'\n' ||'педиатром')
	,(6,'25','психиатром')
	,(7,'26','дерматологом')
	,(8,'27','психиатром-наркологом')																																																																
	,(9,'28','гинекологом')
	,(10,'29','госпитализировано')																																																																
	,(11,'30','отказано в госпитализации')																																																												
	,(12,' ','Из числа госпитализированных (сумма строк 31 - 38) выбыло:')
	,(13,'31','передано родителям или законным представителям')
	,(14,'32','в учреждения социальной защиты населения')																																																						
	,(15,'33','в учреждения системы образования')																																																										
	,(16,'34','в учреждения системы здравоохранения (дома ребенка)	')																																															
	,(17,'35','в учреждения временного содержания несовершеннолетних мвд россии')																																										
	,(18,'36','самовольно покинули учреждение')																																																	
	,(19,'37','умерло')																																																																							
	,(20,'38','прочее')
	) as tab (nstr,num,namestr)
),

kvs as
(
select 
	count(distinct case when PrehospWaifArrive_id in (1,2,3) then evnps.person_id end) v20,
	count(distinct case when PrehospWaifArrive_id=1 then evnps.person_id end ) v21, --сотр. ОВД
	count(distinct case when PrehospWaifArrive_id=2 then evnps.person_id end ) v22, --гражданами
	count(distinct case when PrehospWaifArrive_id=3 then evnps.person_id end ) v23, --самостоятельно
	count(distinct case when PrehospWaifArrive_id in (1,2,3) and trim(s.name) ilike 'Педиатрия' then evnps.person_id end ) v24,
	count(distinct case when PrehospWaifArrive_id in (1,2,3) and trim(s.name) ilike 'Психиатрия' then evnps.person_id end ) v25,
	count(distinct case when PrehospWaifArrive_id in (1,2,3) and (trim(s.name) ilike 'Дерматология' or trim(s.name) ilike 'Дерматовенерология') then evnps.person_id end ) v26,
	count(distinct case when PrehospWaifArrive_id in (1,2,3) and trim(s.name) ilike 'Психиатрия-наркология' then evnps.person_id end ) v27,
	count(distinct case when PrehospWaifArrive_id in (1,2,3) and trim(s.name) ilike 'Акушерство и гинекология' then evnps.person_id end ) v28,
	count(distinct case when PrehospWaifArrive_id in (1,2,3) and evnps.LpuSection_eid is not null then evnps.person_id end ) v29, --госпитализировано
	count(distinct case when PrehospWaifArrive_id in (1,2,3) and PrehospWaifRefuseCause_id is not null then evnps.person_id end ) v30, --отказано в госпитализации
	count (distinct case when el.PrehospWaifRetired_id is not null or (evnps.LpuSection_eid is not null and LeaveType_SysNick in ('die','ksdie','ksdiepp')) then evnps.Person_id end) as v30a,
	count (distinct case when el.PrehospWaifRetired_id = 1 then  evnps.person_id end ) v31, --" Передан родителям или законным представителям"
	count (distinct case when el.PrehospWaifRetired_id = 2 then  evnps.person_id end ) v32, --" в учреждения социальной защиты населения"
	count (distinct case when el.PrehospWaifRetired_id = 3 then  evnps.person_id end ) v33, --"в учреждения системы образования"  
	count (distinct case when el.PrehospWaifRetired_id = 4 then  evnps.person_id end ) v34, --"в учреждения системы здравоохранения (дома ребенка)"  
	count (distinct case when el.PrehospWaifRetired_id = 5 then  evnps.person_id end ) v35, --"в учреждения временного содержания несовершеннолетних МВД России"  
	count (distinct case when el.PrehospWaifRetired_id = 7 then  evnps.person_id end ) v36, --"Самовольно покинул учреждение"  
	count (distinct case when evnps.LpuSection_eid is not null and LeaveType_SysNick in ('die','ksdie','ksdiepp') then evnps.person_id end ) v37,
	count (distinct case when el.PrehospWaifRetired_id = 6 then  evnps.person_id end ) v38 --"прочее"  
from v_evnps evnps
left join params on 1=1
inner join v_lpu lp on lp.lpu_id=evnps.lpu_id
and (lp.lpu_id=p_lpu_id or p_lpu_id is null)
inner join v_lpusection ls on ls.lpusection_id=evnps.lpusection_id
and (ls.lpusection_id=p_lpusection_id or p_lpusection_id is null)
inner join v_lpubuilding lb on ls.lpubuilding_id=lb.lpubuilding_id
and (lb.lpubuilding_id=p_lpubuilding_id or p_lpubuilding_id is null)
inner join v_lpuunit lu on ls.lpuunit_id=lu.lpuunit_id
and (lu.lpuunit_id=p_lpuunit_id or @p_lpuunit_id is null)
inner join 
  (
    select id
    from rpt.getids_fromstrlist(null)
  
    union all
  
    select -2 as id
    where coalesce(rtrim(null), '') = ''
  ) lpufil on (coalesce(lb.lpufilial_id, -2) = lpufil.id or coalesce(rtrim(null), '') = '')

inner join v_personstate_all ps on ps.person_id=evnps.person_id
inner join v_evnsection es on evnps.evnps_id = es.evnsection_pid
inner join v_medstafffact ms on ms.medstafffact_id=es.medstafffact_id
inner join persis.post p on p.id=ms.Post_id
inner join persis.Speciality s on s.id=p.Speciality_id
left join v_evnleave el on evnps.evnps_id=el.evnleave_rid
left join v_leavetype lt on lt.leavetype_id=evnps.leavetype_id
left join v_LeaveCause lc on el.LeaveCause_id=lc.LeaveCause_id
where (1=1)
and evnps.evnps_disdate between p_begdate and p_enddate
and dbo.ageymd(ps.person_birthday,evnps.evnps_setdate,1) between 0 and 17 
and evnps_iswaif=2
),

sumstr as (
select 
	v20,v21,v22,v23,v24,v25,v26,v27,v28,v29,v30,
	coalesce(v31,0)+coalesce(v32,0)+coalesce(v33,0)+coalesce(v34,0)+coalesce(v35,0)+coalesce(v36,0)+coalesce(v37,0)+coalesce(v38,0) as v30a,
	v31,v32,v33,v34,v35,v36,v37,v38
from kvs 
)

select  stroki.nstr, stroki.num, stroki.namestr, unpvt.result
from stroki 
	left join (
		select 
			row_number() over(order by unpvt.val asc) rn, unpvt.val, unpvt.result
		from (
			select 
				 unnest(array['v20','v21','v22','v23','v24','v25','v26','v27','v28','v29','v30','v30a','v31','v32','v33','v34','v35','v36','v37','v38']) as val,
				 unnest(array[v20,v21,v22,v23,v24,v25,v26,v27,v28,v29,v30,v30a,v31,v32,v33,v34,v35,v36,v37,v38]) as "result"
			from sumstr
		) unpvt
) unpvt on unpvt.rn=stroki.nstr
order by nstr
