with recursive params as(
	select
		cast($1 as bigint) as p_Lpu_id,
		$2 as p_LpuBuildingList,
		cast($3 as bigint) as p_LpuUnit_id,
		cast($4 as bigint) as p_LpuSection_id,
		--cast(null as bigint) as p_SubSection_id,
		cast($5 as bigint) as p_MedPersonal_id,
		--cast(? as bigint) as p_StickType_id,
		cast($6 as bigint) as p_org_id,
		cast($7 as timestamp) as p_begdate,
		cast($8 as timestamp) as p_enddate
),
--f16vn_2014_pg.rptdesign
rd_base as(
	select
		t_table.row_num,
		t_table.m_num,
		t_table.w_num,
		t_table.row_text,
		t_table.diag_text,
		t_table.diag_from,
		t_table.diag_to,
		causes.cause_arr
	from(values
		(0, 1, 2, 'Некоторые инфекционные и паразитарные болезни', 'A00-B99', 'A00.', 'B99.9'),
		(1, 3, 4, 'из них: туберкулёз', 'A15-A19', 'A15.', 'A19.9'),
		(2, 5, 6, 'Новообразования', 'C00-D48', 'C00.', 'D48.9'),
		(3, 7, 8, 'из них: злокачественные новообразования', 'C00-C97', 'C00.', 'C97.9'),
		(4, 9, 10, 'Болезни крови, кроветворных органов и отдельные нарушения, вовлекающие иммунный механизм', 'D50-D89', 'D50.', 'D89.9'),
		(5, 11, 12, 'Болезни эндокринной системы, расстройства питания и нарушения обмена веществ', 'E00-E89,  E90', 'E00.', 'E90.9'),
		(6, 13, 14, 'их них: сахарный диабет', 'E10-E14', 'E10.', 'E14.9'),
		(7, 15, 16, 'Психические расстройства и расстройства поведения', 'F00-F99', 'F00.', 'F99.9'),
		(8, 17, 18, 'Болезни нервной системы', 'G00-G98, G99', 'G00.', 'G99.9'),
		(9, 19, 20, 'Болезни глаза и его придаточного аппарата', 'H00-H59', 'H00.', 'H59.9'),
		(10, 21, 22, 'Болезни уха и сосцевидного отростка', 'H60-H95', 'H60.', 'H95.9'),
		(11, 23, 24, 'Болезни системы кровообращения', 'I00-I99', 'I00.', 'I99.9'),
		(12, 25, 26, 'из них: ишемические болезни сердца', 'I20-I25', 'I20.', 'I25.9'),
		(13, 27, 28, 'цереброваскулярные болезни', 'I60-I69', 'I60.', 'I69.9'),
		(14, 29, 30, 'Болезни органов дыхания', 'J00-J98, J99', 'J00.', 'J99.9'),
		(15, 31, 32, 'из них: острые респираторные инфекции верхних дыхательных путей', 'J00,J01,J04,'||chr(10)||'J05,J06', 'J00.', 'J01.9'),
		(15, 31, 32, 'из них: острые респираторные инфекции верхних дыхательных путей', 'J00,J01,J04,'||chr(10)||'J05,J06', 'J04.', 'J06.9'),
		(16, 33, 34, 'грипп', 'J09,J11', 'J09.', 'J11.9'),
		(17, 35, 36, 'пневмония', 'J12-J18', 'J12.', 'J18.9'),
		(18, 37, 38, 'Болезни органов пищеварения', 'K00-K92, K93', 'K00.', 'K93.9'),
		(19, 39, 40, 'Болезни кожи и подкожной клетчатки', 'L00-L99', 'L00.', 'L99.9'),
		(20, 41, 42, 'Болезни костно-мышечной и соединительной ткани', 'M00-M99', 'M00.', 'M99.9'),
		(21, 43, 44, 'Болезни мочеполовой системы', 'N00-N99', 'N00.', 'N99.9'),
		(22, 999, 45, 'Беременность, роды и послеродовой период', 'O00-O99', 'O00.', 'O99.9'),
		(23, 46, 47, 'Врождённые аномалии (пороки развития), деформация и хромосомные нарушения', 'Q00-Q99', 'Q00.', 'Q99.9'),
		(24, 48, 49, 'Травмы, отравления и некоторые другие последствия воздействия внешних причин', 'S00-T98', 'S00.', 'T98.9'),
		(26, 999, 52, 'из них: аборты (из стр.45)', 'O03-O08', 'O03.', 'O08.9'),
		(27, 53, 54, 'уход за больным', '', null, null),
		(28, 55, 56, 'отпуск в связи с санаторно-курортным лечением (без туберкулеза и долечивания инфаркта миокарда)', '', null, null),
		(29, 57, 58, 'освобождение от работы в связи с карантином и бактерионосительством', '', null, null),
		(31, 999, 61, 'Отпуск по беременности и родам', '', null, null),
		(32, 62, 63, 'COVID-19', 'U07.1, U07.2', 'U07.1','U07.2'),
		(33, 64, 65, 'карантин по поводу COVID-19 (из стр.57, 58)', '','U07.1','U07.2')
		
	) as t_table(row_num, m_num, w_num, row_text, diag_text, diag_from, diag_to)
		left join lateral(
			select
				case
					when (t_table.row_num between 0 and 26 or t_table.row_num = 32)
						then '{desease,abort,trauma,prof,accident,protstac,inoe,soczab,lechklin,protez,zabtub,zabrebmin,deseaseP1,deseasetot,deseaseprof,traumotrnrpr}'
					when t_table.row_num = 27
						then '{postvaccinal,uhodrebsan,uhod,rebinv,vich,uhodnoreb,uhodreb}'
					when t_table.row_num = 28
						then '{kurort,dolsan}'
					when t_table.row_num in (29,33)
						then '{karantin}'
					when t_table.row_num = 31
						then '{pregn,dopotp}'
					end::text[] as cause_arr
		)causes on true
),

rd as(
	select
		rd_base.row_num,
		rd_base.m_num,
		rd_base.w_num,
		rd_base.row_text,
		rd_base.diag_text,
		rd_base.diag_from,
		rd_base.diag_to,
		rd_base.cause_arr
	from rd_base
	
	union all select
		25 as row_num,
		50 as m_num,
		51 as w_num,
		'Всего по заболеваниям' as row_text,
		'' as diag_text,
		rd_base.diag_from,
		rd_base.diag_to,
		rd_base.cause_arr
	from rd_base
	where rd_base.row_num not in(
		1,3,6,12,13,15,16,17,26, --подстроки
		27,28,29,31,33 --выбор по причинам
	)
	
	union all select
		30 as row_num,
		59 as m_num,
		60 as w_num,
		'Итого по всем причинам' as row_text,
		'' as diag_text,
		rd_base.diag_from,
		rd_base.diag_to,
		rd_base.cause_arr
	from rd_base
	where rd_base.row_num not in(
		1,3,6,12,13,15,16,17,26,33, --подстроки
		31 --беременность не включается в Итого
	)
),

LpuP as(
	select
		L.Lpu_id,
		params.*
	from params
		left join lateral(
			select L.Lpu_id
			from rpt.GetLpu_succession_pg(p_Lpu_id, p_Begdate, p_Enddate)L
		)L on true
),

sticks as(
	select 
		es.EvnStick_id,
		es.EvnStick_rid,
		es.EvnStick_mid,
		coalesce(fss.StickCause_id,es.StickCause_id) as StickCause_id,
		es.EvnStick_prid,
		es.MedStaffFact_id,
		es.PersonEvn_id,
		es.Server_id,
		es.EvnStick_disDT,
		es.Org_id,
		es.Lpu_outid,
		coalesce(eswr.EvnStick_DayCount,0)+coalesce(fss.EvnStick_DayCount,0) as EvnStick_DayCount
	from LpuP
	inner join dbo.v_EvnStick as es on es.Lpu_outid = LpuP.Lpu_id
	
	--and es.evnstick_num='900000639737'
	
	inner join dbo.v_EvnStickBase as esb on esb.EvnStickBase_id = es.EvnStick_id
	left join lateral(
		select 
			min(eswr.EvnStickWorkRelease_begDT) as EvnStickWorkRelease_begdt, 
			max(eswr.EvnStickWorkRelease_enddt) as EvnStickWorkRelease_enddt,
			sum(date_part('day', (eswr.EvnStickWorkRelease_endDT - eswr.EvnStickWorkRelease_begDT))::int	+ 1) as EvnStick_DayCount
		from dbo.v_EvnStickWorkRelease as eswr
		where eswr.EvnStickBase_id = es.EvnStick_id
	)eswr on true
	
  left join lateral(--случай для ЛВН из ФСС
		select 
      sc.StickCause_id,
  		sf.StickFSSDataGet_FirstBegDate evnStickWorkRelease_begDT, 
			coalesce(sf.StickFSSDataGet_ThirdEndDate, sf.StickFSSDataGet_SecondEndDate, sf.StickFSSDataGet_FirstEndDate) EvnStickWorkRelease_endDT,
			date_part('day', (coalesce(sf.StickFSSDataGet_ThirdEndDate, sf.StickFSSDataGet_SecondEndDate, sf.StickFSSDataGet_FirstEndDate) - sf.StickFSSDataGet_FirstBegDate))::int	+ 1 as EvnStick_DayCount
		from v_StickFSSDataGet sf
    inner join v_StickCause sc on sc.StickCause_Code = sf.StickFSSDataGet_StickReason
    where sf.EvnStickBase_id = es.EvnStick_id
    	and es.evnstick_isfss = 2
    order by sf.StickFSSDataGet_updDT desc
		limit 1
  )fss on true

	where true
		and dbo.maxdate(coalesce(eswr.evnstickworkrelease_enddt,fss.evnstickworkrelease_enddt),coalesce(fss.evnstickworkrelease_enddt,eswr.evnstickworkrelease_enddt))
							between p_BegDate and p_EndDate + interval '1day' - interval '1 minute'
	--		and es.EvnStick_disDT between p_BegDate and p_EndDate + interval'1day'
		and esb.StickLeaveType_rid in (1,3,4,5,6,7,8,9)
		--and (es.StickType_id = p_StickType_id or p_StickType_id is null)
		and coalesce(es.StickWorkType_id,1) in (1,3) --без совместительства
		and es.StickType_id=1 --больничный
		and coalesce(es.EvnStick_IsOriginal,1) = 1 --1 Оригинал/ 2 Дубликат
		and coalesce(esb.EvnStickBase_IsDelQueue,1) = 1 --В очереди на удаление
	
	union all select 
		sticks.EvnStick_id,
		sticks.EvnStick_rid,
		sticks.EvnStick_mid,
		sticks.StickCause_id,
		es.EvnStick_prid,
		sticks.MedStaffFact_id,
		sticks.PersonEvn_id,
		sticks.Server_id,
		sticks.EvnStick_disDT,
		sticks.Org_id,
		sticks.Lpu_outid,
		coalesce(eswr.EvnStick_DayCount,0)+coalesce(fss.EvnStick_DayCount,0) as EvnStick_DayCount
	from dbo.v_EvnStick as es
		inner join sticks on es.EvnStick_id = sticks.EvnStick_prid
		inner join dbo.v_EvnStickBase as esb on esb.EvnStickBase_id = es.EvnStick_id
		left join lateral(
			select sum(date_part('day', (eswr.EvnStickWorkRelease_endDT - eswr.EvnStickWorkRelease_begDT))::int	+ 1) as EvnStick_DayCount
	  	from dbo.v_EvnStickWorkRelease as eswr
			where eswr.EvnStickBase_id = es.EvnStick_id
		)eswr on true
  left join lateral(--случай для ЛВН из ФСС
		select 
      sc.StickCause_id,
			date_part('day', (coalesce(sf.StickFSSDataGet_ThirdEndDate, sf.StickFSSDataGet_SecondEndDate, sf.StickFSSDataGet_FirstEndDate) - sf.StickFSSDataGet_FirstBegDate))::int	+ 1 as EvnStick_DayCount
		from v_StickFSSDataGet sf
    inner join v_StickCause sc on sc.StickCause_Code = sf.StickFSSDataGet_StickReason
    where sf.EvnStickBase_id = es.EvnStick_id
    	and es.evnstick_isfss = 2
    order by sf.StickFSSDataGet_updDT desc
		limit 1
  )fss on true
	where true
		and coalesce(es.StickWorkType_id,1) in (1,3) --без совместительства
		and es.StickType_id=1 --больничный
		and coalesce(es.EvnStick_IsOriginal,1) = 1 --1 Оригинал/ 2 Дубликат
		and coalesce(esb.EvnStickBase_IsDelQueue,1) = 1 --В очереди на удаление
),

sticks_agg as(
	select 
		sticks.EvnStick_id,
		sticks.EvnStick_rid,
		sticks.EvnStick_mid,
		sticks.StickCause_id,
		sticks.MedStaffFact_id,
		sticks.PersonEvn_id,
		sticks.Server_id,
		sticks.EvnStick_disDT,
		sticks.Org_id,
		sticks.Lpu_outid,
		sum(sticks.EvnStick_DayCount) as EvnStick_DayCount
	from sticks
	group by 1,2,3,4,5,6,7,8,9,10
),

subq as(
	select 
		rd.row_num,
		sum(coalesce(EvnStick_Daycount, 0)) filter(where Sex_id = 1) as val5,
		sum(coalesce(EvnStick_Daycount, 0)) filter(where Sex_id = 2) as val17,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 1) as val6,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 2) as val18,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 1 and age between 15 and 19) as val7,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 2 and age between 15 and 19) as val19,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 1 and age between 20 and 24) as val8,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 2 and age between 20 and 24) as val20,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 1 and age between 25 and 29) as val9,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 2 and age between 25 and 29) as val21,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 1 and age between 30 and 34) as val10,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 2 and age between 30 and 34) as val22,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 1 and age between 35 and 39) as val11,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 2 and age between 35 and 39) as val23,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 1 and age between 40 and 44) as val12,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 2 and age between 40 and 44) as val24,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 1 and age between 45 and 49) as val13,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 2 and age between 45 and 49) as val25,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 1 and age between 50 and 54) as val14,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 2 and age between 50 and 54) as val26,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 1 and age between 55 and 59) as val15,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 2 and age between 55 and 59) as val27,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 1 and age >= 60) as val16,
		count(distinct stck.EvnStick_id) filter(where Sex_id = 2 and age >= 60) as val28
	from sticks_agg as stck
		inner join dbo.v_StickCause as sc on sc.StickCause_id = stck.StickCause_id
		left join params on true
		inner join lateral(
			select 
				ps.Sex_id,
				date_part('year', age(stck.EvnStick_disDT, ps.Person_BirthDay)) as age
			from dbo.v_Person_all as ps
			left join dbo.Job on Job.Job_id = ps.Job_id
			where ps.PersonEvn_id = stck.PersonEvn_id
				and ps.Server_id = stck.Server_id
				and (coalesce(stck.Org_id, Job.Org_id) = p_Org_id or p_Org_id is null)
		)ps on true

		inner join lateral(
			select
				evns.LpuSection_id,
				evns.Diag_lid
			from(
				(select
					evpl.EvnVizitPL_setDT as Evn_disDT,
					evpl.LpuSection_id,
					coalesce(evpl.Diag_id, epl.Diag_id) as Diag_lid
				from dbo.v_EvnPL as epl
					inner join dbo.v_EvnVizitPL as evpl on evpl.EvnVizitPL_pid = epl.EvnPL_id
				where (stck.EvnStick_rid = epl.EvnPL_id or stck.EvnStick_mid = epl.EvnPL_id)
					and epl.Lpu_id = stck.Lpu_outid
					and (epl.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
				order by evpl.EvnVizitPL_setDT desc
				limit 1)

				union all (select
					eps.Evnps_disDT as Evn_disDT,
					ESec.LpuSection_id,
					eps.Diag_id as Diag_lid
				from dbo.v_Evnps as eps 
					inner join dbo.v_EvnSection as ESec on ESec.EvnSection_pid = eps.Evnps_id
				where stck.EvnStick_rid = eps.Evnps_id
					and (eps.PrehospWaifRefuseCause_id is null
						and coalesce(ESec.EvnSection_IsPriem, 1) = 1
						or eps.PrehospWaifRefuseCause_id is not null)
					and eps.Lpu_id = stck.Lpu_outid
					and (Esec.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
				order by ESec.EvnSection_setDT desc
				limit 1)
			)evns
			order by evns.Evn_disDT desc
			limit 1
		)evns on true

		inner join dbo.Diag as d on d.Diag_id = evns.Diag_lid
		left join dbo.v_MedStaffFact as msf on msf.MedStaffFact_id = stck.MedStaffFact_id
		inner join dbo.v_LpuSection as ls on ls.LpuSection_id = evns.LpuSection_id
		--	case when p_MedPersonal_id is null then evns.LpuSection_id else msf.LpuSection_id end
		inner join rd on (d.Diag_Code between rd.diag_from and rd.diag_to or rd.diag_from is null)
			and sc.StickCause_SysNick = any(cause_arr)
	where true
		and (msf.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
		and (ls.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null) 
		and (ls.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
		and (ls.LpuBuilding_id = any(('{'||p_LpuBuildingList||'}')::int8[])
			or p_LpuBuildingList is null)
	group by rd.row_num
)

select
	rd.row_num,
	rd.m_num as "M_Num",
	rd.w_num as "W_Num",
	rd.row_text as "RepDiag_Text1",
	rd.diag_text as "RepDiag_Text2",
	coalesce(subq.val5,0) as val5,
	coalesce(subq.val17,0) as val17,
	coalesce(subq.val6,0) as val6,
	coalesce(subq.val18,0) as val18,
	coalesce(subq.val7,0) as val7,
	coalesce(subq.val19,0) as val19,
	coalesce(subq.val8,0) as val8,
	coalesce(subq.val20,0) as val20,
	coalesce(subq.val9,0) as val9,
	coalesce(subq.val21,0) as val21,
	coalesce(subq.val10,0) as val10,
	coalesce(subq.val22,0) as val22,
	coalesce(subq.val11,0) as val11,
	coalesce(subq.val23,0) as val23,
	coalesce(subq.val12,0) as val12,
	coalesce(subq.val24,0) as val24,
	coalesce(subq.val13,0) as val13,
	coalesce(subq.val25,0) as val25,
	coalesce(subq.val14,0) as val14,
	coalesce(subq.val26,0) as val26,
	coalesce(subq.val15,0) as val15,
	coalesce(subq.val27,0) as val27,
	coalesce(subq.val16,0) as val16,
	coalesce(subq.val28,0) as val28 
from(
	select distinct
		rd.row_num,
		rd.m_num,
		rd.w_num,
		rd.row_text,
		rd.diag_text
	from rd
)rd
	left join subq on subq.row_num = rd.row_num
order by 1
