with params as (
	select
		cast($1 as bigint) as p_lpu_id,
		cast($2 as varchar) as p_lpufilialslist,
		cast($3 as bigint) as p_lpubuilding_id,
		cast($4 as timestamp) as p_enddate
),
RepStr as(
    select 
        	t_table.row_n, t_table.group_n, t_table.row_s, t_table.text1, t_table.text2, t_table.age --1-все, 2-дети, 3-взрослые, 4-нетрудоспособные
    from(values
		(1, 1, '1', 'Гипергликемия неуточненная', 'R73.9', 1),
		(2, 1, '1.1', 'дети в возрасте 0-17 лет', '', 2),
		(3, 1, '1.2', 'взрослые в возрасте 18 лет и старше', '', 3),
		(4, 1, '1.2.1', 'лица старше трудоспособного возраста', '', 4),
		(5, 2, '2', 'Сахарный диабет', 'Е10-Е14', 1),
		(6, 2, '2.1', 'дети в возрасте 0-17 лет', '', 2),
		(7, 2, '2.2', 'взрослые в возрасте 18 лет и старше', '', 3),
		(8, 2, '2.2.1', 'лица старше трудоспособного возраста', '', 4),
		(9, 3, '3', 'Анормальная прибавка массы тела', 'R63.5', 1),
		(10, 3, '3.1', 'дети в возрасте 0-17 лет', '', 2),
		(11, 3, '3.2', 'взрослые в возрасте 18 лет и старше', '', 3),
		(12, 3, '3.2.1', 'лица старше трудоспособного возраста', '', 4),
		(13, 4, '4', 'Ожирение', 'Е66', 1),
		(14, 4, '4.1', 'дети в возрасте 0-17 лет', '', 2),
		(15, 4, '4.2', 'взрослые в возрасте 18 лет и старше', '', 3),
		(16, 4, '4.2.1', 'лица старше трудоспособного возраста', '', 4),
		(17, 5, '5', 'Язва желудка, язва двенадцатиперстной кишки', 'К25, К26', 1),
		(18, 5, '5.1', 'дети в возрасте 0-17 лет', '', 2),
		(19, 5, '5.2', 'взрослые в возрасте 18 лет и старше', '', 3),
		(20, 5, '5.2.1', 'лица старше трудоспособного возраста', '', 4),
		(21, 6, '6', 'Гастрит и дуоденит', 'K29', 1),
		(22, 6, '6.1', 'дети в возрасте 0-17 лет', '', 2),
		(23, 6, '6.2', 'взрослые в возрасте 18 лет и старше', '', 3),
		(24, 6, '6.2.1', 'лица старше трудоспособного возраста', '', 4),
		(25, 7, '7', 'Нарушения обмена липопротеидов и другие липидемии', 'E78', 1),
		(26, 7, '7.1', 'дети в возрасте 0-17 лет', '', 2),
		(27, 7, '7.2', 'взрослые в возрасте 18 лет и старше', '', 3),
		(28, 7, '7.2.1', 'лица старше трудоспособного возраста', '', 4),
		(29, 8, '8', 'Болезни печени (алкогольная болезнь печени, фиброз и цирроз печени, токсическое поражение печени и др.)', 'К70-74', 1),
		(31, 8, '8.1', 'взрослые в возрасте 18 лет и старше', '', 3),
		(32, 8, '8.1.1', 'лица старше трудоспособного возраста', '', 4),
		(33, 9, '9', 'Острый панкреатит, другие болезни поджелудочной железы', 'К85-86', 1),
		(34, 9, '9.1', 'дети в возрасте 0-17 лет', '', 2),
		(35, 9, '9.2', 'взрослые в возрасте 18 лет и старше', '', 3),
		(36, 9, '9.2.1', 'лица старше трудоспособного возраста', '', 4),
		(37, 10, '10', 'Психотические расстройства, связанные с употреблением алкоголя (алкогольные психозы)', 'F10.03, F10.07, F10.4 - F10.6, F10.73, 75, 81, 91', 1),
		(38, 10, '10.1', ' взрослые в возрасте 18 лет и старше', '', 3),
		(39, 10, '10.1.1', 'лица старше трудоспособного возраста', '', 4)
	)as t_table(row_n, group_n, row_s, text1, text2, age)
),
crazy as (  --реестр алкаголиков для 10-ых строк
	select 
		10 as group_n,
		PR.PersonRegister_id,
		case when mcb.MorbusCrazyBase_FirstDT between ('01/01/'||year(p_enddate))::date and p_enddate + interval'1 day' then 2 end as first_evn,
		case 
			when dbo.AgeYMD(ps.Person_BirthDay, PR.PersonRegister_setDate, 1) - age.p >= 60 then 4
			when dbo.AgeYMD(ps.Person_BirthDay, PR.PersonRegister_setDate, 1) >= 18 then 3
			when dbo.AgeYMD(ps.Person_BirthDay, PR.PersonRegister_setDate, 1) < 18 then 2
			else 1 end as age,
		0 as disp
	from 
		Params
		inner join v_MorbusType MorbusType on true
		inner join v_PersonRegister PR on PR.MorbusType_id = MorbusType.MorbusType_id
		inner join v_PersonState ps on ps.Person_id=PR.Person_id
		left join v_MorbusCrazy mc on mc.Morbus_id = PR.Morbus_id
		--Диагноз из специфики наркологии
		left join v_MorbusCrazyDiag mcd on mcd.MorbusCrazy_id=mc.MorbusCrazy_id
		--Способ употребления наркотиков
		left join v_MorbusCrazyBase mcb on mc.MorbusBase_id = mcb.MorbusBase_id
		left join v_CrazyDiag cd on cd.CrazyDiag_id = mcd.CrazyDiag_id
		left join lateral(
			select
				case
					when year(p_enddate) < 2020 and ps.Sex_id = 1 then 0
					when year(p_enddate) < 2022 and ps.Sex_id = 1  then 1
					when year(p_enddate) < 2024 and ps.Sex_id = 1  then 2
					when year(p_enddate) < 2026 and ps.Sex_id = 1  then 3
					when year(p_enddate) < 2028 and ps.Sex_id = 1  then 4
					when year(p_enddate) > 2027 and ps.Sex_id = 1 then 5
					when year(p_enddate) < 2020 and ps.Sex_id = 2 then -5
					when year(p_enddate) < 2022 and ps.Sex_id = 2 then -4
					when year(p_enddate) < 2024 and ps.Sex_id = 2 then -3
					when year(p_enddate) < 2026 and ps.Sex_id = 2 then -2
					when year(p_enddate) < 2028 and ps.Sex_id = 2 then -1
					when year(p_enddate) > 2027 and ps.Sex_id = 2 then 0
				end as p
		) age on true
	where 
		(1 = 1)
		and cast(PR.PersonRegister_setDate as date) between ('01/01/'||year(p_enddate))::date and p_enddate + interval'1 day'
		and (PR.Lpu_iid = p_lpu_id or p_lpu_id is null)
		and p_lpubuilding_id is null
		and coalesce(p_lpufilialslist, '') = ''
		and MorbusType.MorbusType_SysNick = 'narc'
		and (cd.CrazyDiag_Code similar to 'F10.0[37]%'
		or cd.CrazyDiag_Code similar to 'F10.[4-6]%' 
		or cd.CrazyDiag_Code similar to 'F10.7[37]%' 
		or cd.CrazyDiag_Code similar to 'F10.[89]1%')
),
RepStr_diag as (
	select 
		d.diag_id,
		d.Diag_Code,
		case when d.Diag_Code in ('R73.9') then 1
			when d.Diag_Code ilike 'E10%' then 2
			when d.Diag_Code ilike 'E11%' then 2
			when d.Diag_Code ilike 'E12%' then 2
			when d.Diag_Code ilike 'E13%' then 2
			when d.Diag_Code ilike 'E14%' then 2
			when d.Diag_Code in ('R63.5') then 3
			when d.Diag_Code ilike 'E66%' then 4
			when d.Diag_Code ilike 'K25%' then 5
			when d.Diag_Code ilike 'K26%' then 5
			when d.Diag_Code ilike 'K29%' then 6
			when d.Diag_Code ilike 'E78%' then 7
			when d.Diag_Code ilike 'K70%' then 8
			when d.Diag_Code ilike 'K71%' then 8
			when d.Diag_Code ilike 'K72%' then 8
			when d.Diag_Code ilike 'K73%' then 8
			when d.Diag_Code ilike 'K74%' then 8
			when d.Diag_Code ilike 'K85%' then 9
			when d.Diag_Code ilike 'K86%' then 9 end as group_n
	from
		dbo.diag as d
	where
		d.diaglevel_id in (4,3)
		and (d.Diag_Code in ('R73.9','R63.5')
				or d.Diag_Code ilike 'E10%'
				or d.Diag_Code ilike 'E11%'
				or d.Diag_Code ilike 'E12%'
				or d.Diag_Code ilike 'E13%'
				or d.Diag_Code ilike 'E14%'
				or d.Diag_Code ilike 'E66%'
				or d.Diag_Code ilike 'K25%'
				or d.Diag_Code ilike 'K26%'
				or d.Diag_Code ilike 'K29%'
				or d.Diag_Code ilike 'E78%'
				or d.Diag_Code ilike 'K70%'
				or d.Diag_Code ilike 'K71%'
				or d.Diag_Code ilike 'K72%'
				or d.Diag_Code ilike 'K73%'
				or d.Diag_Code ilike 'K74%'
				or d.Diag_Code ilike 'K85%'
				or d.Diag_Code ilike 'K86%')
),
LpuSection_W as (
	select 
		LS.Lpu_id
		,LS.LpuSection_id
		,LS.LpuSection_Name
		,LS.LpuSection_Code
	from 
		dbo.v_Lpu as L 
		left join params on 1=1
		inner join dbo.v_LpuSection as LS on LS.Lpu_id = L.Lpu_id
			and (LS.LpuBuilding_id = p_lpubuilding_id or p_lpubuilding_id is null)
		inner join dbo.v_LpuBuilding as LB on LB.LpuBuilding_id = LS.LpuBuilding_id
			and (',' || p_lpufilialslist || ',' like '%,' || cast(LB.LpuFilial_id as varchar(30)) || ',%' or p_lpufilialslist is null)
	where 
		1 = 1
		and (l.lpu_id = p_Lpu_id or (p_Lpu_id is null and coalesce(l.Lpu_IsTest,1)=1))
), 
EvnVizitDispDopOrp_W as -- dbo.v_EvnVizitDispDop и dbo.v_EvnVizitDispOrp
(
	select
    	CAST(CAST(EVD.Evn_setDT AS DATE) AS timestamp) as EvnVizitDispDopOrp_setDate,
    	EVD.Evn_id as EvnVizitDispDopOrp_id, 
		EVD.Evn_pid as EvnVizitDispDopOrp_pid,
		EVD.Lpu_id,
    	EVD.MedStaffFact_id,
    	EVD.MedPersonal_id,
    	EVD.LpuSection_id,
		EVD.DopDispInfoConsent_id,
		EVD.Diag_id,
		EVD.DopDispDiagType_id
  from 
  		dbo.EvnVizitDisp EVD 
		left join params on 1=1
  where 
    	EVD.EvnClass_id between 14 and 15
		and EVD.Evn_setDT between date_trunc('year', p_EndDate) and p_EndDate + interval '1 day'
		and EVD.DopDispDiagType_id between 1 and 2
		and (EVD.Evn_deleted = 1 or EVD.Evn_deleted is null)
),
EPLDisp as
(
	select
		d.group_n,
		EPLD.Person_id,
		case when EVD.DopDispDiagType_id = 2 then 2 end as first_evn,
		case 
			when dbo.AgeYMD_pg(EPLD.Person_BirthDay, p_enddate, 1) - age.p >= 60 then 4
			when dbo.AgeYMD_pg(EPLD.Person_BirthDay, p_enddate, 1) >= 18 then 3
			when dbo.AgeYMD_pg(EPLD.Person_BirthDay, p_enddate, 1) < 18 then 2
			else 1 end as age,
		case when EVD.DopDispDiagType_id = 2 then 2 end as disp
	from 
		LpuSection_W as LpuP
		left join params on 1=1
 		inner join
  		( 
    		select
		  		EPLD.EvnClass_id, 
		  		EPLD.Evn_id AS EvnPLDisp_id, 
				EPLD.Evn_disDT as EvnPLDisp_dt,
				EPLD.Lpu_id,
				EPLD.DispClass_id,
				EPLD.Person_id,
				PS.Person_BirthDay,
				PS.Sex_id,
				PS.PAddress_id,
				PS.UAddress_id
			from 
				dbo.EvnPLDisp EPLD
				left join params on 1=1
  				inner join v_PersonState as PS on PS.Person_id = EPLD.Person_id  
			where 
				EPLD.EvnClass_id IN (7,8,9,101,103,104)
	  			and EPLD.Evn_disDT between ('01/01/'||year(p_enddate))::date and p_EndDate + interval '1 day'
	  			and EPLD.EvnPLBase_IsFinish = 2
	  			and (EPLD.Evn_Deleted = 1 or EPLD.Evn_Deleted is null)  
		) EPLD on EPLD.Lpu_id = LpuP.Lpu_id 
		inner join EvnVizitDispDopOrp_W as EVD on EVD.EvnVizitDispDopOrp_pid = EPLD.EvnPLDisp_id
			and EVD.DopDispDiagType_id between 1 and 2		
		inner join LpuSection_W as LS on LS.LpuSection_id = EVD.LpuSection_id	
		inner join RepStr_diag d on d.Diag_id = EVD.Diag_id
		left join dbo.v_DopDispInfoConsent DDIC on DDIC.DopDispInfoConsent_id = EVD.DopDispInfoConsent_id
		left join dbo.v_SurveyTypeLink STL on STL.SurveyTypeLink_id = DDIC.SurveyTypeLink_id
		left join dbo.v_SurveyType ST on ST.SurveyType_id = STL.SurveyType_id
		left join lateral(
			select
				case
					when year(p_enddate) < 2020 and EPLD.Sex_id = 1 then 0
					when year(p_enddate) < 2022 and EPLD.Sex_id = 1  then 1
					when year(p_enddate) < 2024 and EPLD.Sex_id = 1  then 2
					when year(p_enddate) < 2026 and EPLD.Sex_id = 1  then 3
					when year(p_enddate) < 2028 and EPLD.Sex_id = 1  then 4
					when year(p_enddate) > 2027 and EPLD.Sex_id = 1 then 5
					when year(p_enddate) < 2020 and EPLD.Sex_id = 2 then -5
					when year(p_enddate) < 2022 and EPLD.Sex_id = 2 then -4
					when year(p_enddate) < 2024 and EPLD.Sex_id = 2 then -3
					when year(p_enddate) < 2026 and EPLD.Sex_id = 2 then -2
					when year(p_enddate) < 2028 and EPLD.Sex_id = 2 then -1
					when year(p_enddate) > 2027 and EPLD.Sex_id = 2 then 0
				end as p
		) age on true
	where
    	(lower(ST.SurveyType_Name) like '%осмотр%врач%' or EPLD.DispClass_id in (4,8,11,12)) 
),
EPL as 
(
	select
		RepStr_diag.group_n,
		case when EPL.DeseaseType_id = 3 or ve.deseasetype_id = 3
		then EPL.EvnPL_id 
		else - PS.Person_id end as Person_id, 
		case when EPL.DeseaseType_id in (1,3) 
			or ve.deseasetype_id in (1,3) then 2 end as first_evn,
		case 
			when dbo.AgeYMD(ps.Person_BirthDay, p_EndDate, 1) - age.p >= 60 then 4
			when dbo.AgeYMD(ps.Person_BirthDay, p_EndDate, 1) >= 18 then 3
			when dbo.AgeYMD(ps.Person_BirthDay, p_EndDate, 1) < 18 then 2
			else 1 end as age,
		0 as disp
	from 
		Params
		inner join LpuSection_W as LpuP on true
		inner join v_EvnPL EPL on EPL.Lpu_id = LpuP.Lpu_id
			and EPL.EvnPL_isFinish = 2
			and LpuP.LpuSection_id = EPL.LpuSection_id
			and EPL.EvnPL_disDate between ('01/01/'||year(p_enddate))::date and p_EndDate + interval '1 day'
		left join dbo.v_EvnVizitPL as EVPLLast on EVPLLast.EvnVizitPL_pid = EPL.EvnPL_id
			and EVPLLast.EvnVizitPL_Index = EVPLLast.EvnVizitPL_Count - 1
		left join dbo.v_PersonState as PS on PS.Person_id = EPL.Person_id
		left join dbo.v_evndiagplsop ve on EVPLLast.EvnVizitPL_id = ve.evndiagplsop_pid 
		inner join RepStr_diag on (RepStr_diag.Diag_id = EPL.Diag_id or RepStr_diag.Diag_id = ve.diag_id)
		left join lateral(
			select
				case
					when year(p_enddate) < 2020 and PS.Sex_id = 1 then 0
					when year(p_enddate) < 2022 and PS.Sex_id = 1 then 1
					when year(p_enddate) < 2024 and PS.Sex_id = 1 then 2
					when year(p_enddate) < 2026 and PS.Sex_id = 1 then 3
					when year(p_enddate) < 2028 and PS.Sex_id = 1 then 4
					when year(p_enddate) > 2027 and PS.Sex_id = 1 then 5
					when year(p_enddate) < 2020 and PS.Sex_id = 2 then -5
					when year(p_enddate) < 2022 and PS.Sex_id = 2 then -4
					when year(p_enddate) < 2024 and PS.Sex_id = 2 then -3
					when year(p_enddate) < 2026 and PS.Sex_id = 2 then -2
					when year(p_enddate) < 2028 and PS.Sex_id = 2 then -1
					when year(p_enddate) > 2027 and PS.Sex_id = 2 then 0
				end as p
		) age on true
)
select 
	RepStr.row_n as por_n,	
	RepStr.row_s as por_s,	
	RepStr.text1 as name_p,	
	RepStr.text2 as code_diagz,
	count(distinct crazy.PersonRegister_id) as v4,
	count(distinct case when crazy.first_evn = 2 then crazy.PersonRegister_id end) as v5,
	count(distinct case when crazy.disp = 2 then crazy.PersonRegister_id end) as v6
from
	RepStr
	left join 
	(
		select * from crazy
		union all
		select * from EPLDisp
		union all
		select * from EPL
	) as crazy on RepStr.group_n = crazy.group_n
		and (RepStr.age = 1 
			or (RepStr.age = 2 and crazy.age = 2) 
			or (RepStr.age = 3 and crazy.age in (3,4)) 
			or (RepStr.age = 4 and crazy.age = 4))
group by
	RepStr.row_n,	
	RepStr.row_s,	
	RepStr.text1,	
	RepStr.text2
order by 
	RepStr.row_n
