with Params as( 
select
	cast($1 as bigint) as p_lpu_id,
	cast($2 as bigint) as p_lpubuilding_id,
	cast($3 as bigint) as p_lpuunit_id,
	cast($4 as bigint) as p_lpusection_id,
	cast($5 as bigint) as p_medpersonal_id,
    cast(to_timestamp($6, 'dd.mm.yyyy')as timestamp) as p_begdate,
    cast(to_timestamp($7, 'dd.mm.yyyy')as timestamp) as p_enddate
),
GetEvnPLAll as(
select 
	case when coalesce(EvnPL.DeseaseType_id,5) in (6,7,8,9,10,4)
		then EvnPl.Person_id else EvnPl.EvnPL_id end as EPL_id,
	EvnPL.EvnPL_id, 
	EvnPL.Server_id,
	EvnPL.PersonEvn_id,
	EvnPL.Person_id,
	EvnPL.Person_Age,
	EvnPL.EvnPL_setDate as EvnPL_SetDate,
	EvnPL.EvnPL_disDate as EvnPL_disDate,
	EvnPL.Diag_id,
	Diag.Diag_Code,
	EvnPL.DeseaseType_id,
	EvnPL.Lpu_id,
	EvnPL.EvnPL_IsFinish,
	1 as IsDopDisp,
	EvnVizit.MedPersonal_id,
	EvnVizit.LpuSection_id,
	p.*
from params p
	inner join v_EvnPL as EvnPL on true
	inner join Diag on Diag.Diag_id = EvnPL.Diag_id
	inner join v_EvnVizitPl as EvnVizit on EvnVizit.EvnVizitPL_pid = EvnPl.EvnPL_id
		and EvnVizit.EvnVizitPL_Index = EvnVizit.EvnVizitPl_count-1
	inner join LpuSection on LpuSection.LpuSection_id = EvnVizit.LpuSection_id 
	inner join LpuUnit on LpuSection.LpuUnit_id = LpuUnit.LpuUnit_id
where EvnPL.EvnPL_disDate between p_begdate and p_enddate
	and((EvnPl.Lpu_id = p_Lpu_id) or (p_Lpu_id is null and EvnPL.Lpu_id not in (100,101)))
	and (EvnVizit.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
	and (LpuSection.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
	and (LpuUnit.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null) 
	and (EvnVizit.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
	and EvnPL.EvnPL_IsFinish = 2
	and (left(Diag.Diag_Code,3) = 'E00' or Diag.Diag_Code in ('E01.0','E01.2','E04.0') or Diag.Diag_Code in ('E01.1','E04.1','E04.2') 
		or left(Diag.Diag_Code,3) in ('E02','E03') or left(Diag.Diag_Code,3) = 'E05' or left(Diag.Diag_Code,3) = 'E06')
	and EvnPL.Person_Age between 0 and 200	
)

select
	'Синдром врождённой йодной недостаточности' as v1,
	'01' as v2,
	'E00' as v3,
	coalesce(sum(t1.EvnPL_id),0) as v4,
	coalesce(sum(t1.v5),0) as v5, 
	coalesce(sum(t1.v6),0) as v6,
	coalesce(sum(t1.v7),0) as v7,
	coalesce(sum(t1.v8),0) as v8, 
	coalesce(sum(t1.v9),0) as v9, 
	coalesce(sum(t1.v10),0) as v10, 
	coalesce(sum(t1.v11),0) as v11,
	coalesce(sum(t1.v12),0) as v12, 
	coalesce(sum(t1.v13),0) as v13, 
	coalesce(sum(t1.v14),0) as v14, 
	coalesce(sum(t1.v15),0) as v15, 
	coalesce(sum(t1.v16),0) as v16, 
	coalesce(sum(t1.v17),0) as v17, 
	coalesce(sum(t1.v18),0) as v18 
from(
	select
		EvnPL.Diag_Code,
		count(distinct EvnPL.EPL_id) as EvnPL_id,
		count(distinct case when Person_Age between 0 and 4 then EvnPL.EPL_id end) as v5,
		count(distinct case when Person_Age between 5 and 9 then EvnPL.EPL_id end) as v6,
		count(distinct case when Person_Age between 10 and 14 then EvnPL.EPL_id end) as v7,
		count(distinct case when Person_Age between 15 and 17 then EvnPL.EPL_id end) as v8,
		count(distinct case when Person_Age between 18 and 19 then EvnPL.EPL_id end) as v9,
		count(distinct case when Person_Age between 20 and 24 then EvnPL.EPL_id end) as v10,
		count(distinct case when Person_Age between 25 and 29 then EvnPL.EPL_id end) as v11,
		count(distinct case when Person_Age between 30 and 34 then EvnPL.EPL_id end) as v12,
		count(distinct case when Person_Age between 35 and 39 then EvnPL.EPL_id end) as v13,
		count(distinct case when Person_Age between 40 and 44 then EvnPL.EPL_id end) as v14,
		count(distinct case when Person_Age between 45 and 49 then EvnPL.EPL_id end) as v15,
		count(distinct case when Person_Age between 50 and 54 then EvnPL.EPL_id end) as v16,
		count(distinct case when Person_Age between 55 and 59 then EvnPL.EPL_id end) as v17,
		count(distinct case when Person_Age >= 60 then EvnPL.EPL_id end) as v18
	from GetEvnPLAll as EvnPL
	where left(EvnPL.Diag_Code,3) = 'E00'
	group by EvnPL.Diag_Code
) as t1
	
union all select 
	'Диффузный (эндемический) зоб, связанный с йодной недостаточностью и другие формы нетоксического зоба' as v1,
	'02' as v2,
	'E01.0,E01.2,E04.0' as v3,
	coalesce(sum(t1.EvnPL_id),0) as v4,
	coalesce(sum(t1.v5),0) as v5, 
	coalesce(sum(t1.v6),0) as v6,
	coalesce(sum(t1.v7),0) as v7,
	coalesce(sum(t1.v8),0) as v8, 
	coalesce(sum(t1.v9),0) as v9, 
	coalesce(sum(t1.v10),0) as v10, 
	coalesce(sum(t1.v11),0) as v11,
	coalesce(sum(t1.v12),0) as v12, 
	coalesce(sum(t1.v13),0) as v13, 
	coalesce(sum(t1.v14),0) as v14, 
	coalesce(sum(t1.v15),0) as v15, 
	coalesce(sum(t1.v16),0) as v16, 
	coalesce(sum(t1.v17),0) as v17, 
	coalesce(sum(t1.v18),0) as v18 
from(
	select
		EvnPL.Diag_Code,
		count(distinct EvnPL.EPL_id ) as EvnPL_id,
		count(distinct case when Person_Age between 0 and 4 then EvnPL.EPL_id end) as v5,
		count(distinct case when Person_Age between 5 and 9 then EvnPL.EPL_id end) as v6,
		count(distinct case when Person_Age between 10 and 14 then EvnPL.EPL_id end) as v7,
		count(distinct case when Person_Age between 15 and 17 then EvnPL.EPL_id end) as v8,
		count(distinct case when Person_Age between 18 and 19 then EvnPL.EPL_id end) as v9,
		count(distinct case when Person_Age between 20 and 24 then EvnPL.EPL_id end) as v10,
		count(distinct case when Person_Age between 25 and 29 then EvnPL.EPL_id end) as v11,
		count(distinct case when Person_Age between 30 and 34 then EvnPL.EPL_id end) as v12,
		count(distinct case when Person_Age between 35 and 39 then EvnPL.EPL_id end) as v13,
		count(distinct case when Person_Age between 40 and 44 then EvnPL.EPL_id end) as v14,
		count(distinct case when Person_Age between 45 and 49 then EvnPL.EPL_id end) as v15,
		count(distinct case when Person_Age between 50 and 54 then EvnPL.EPL_id end) as v16,
		count(distinct case when Person_Age between 55 and 59 then EvnPL.EPL_id end) as v17,
		count(distinct case when Person_Age >= 60 then EvnPL.EPL_id end) as v18
	from GetEvnPLAll as EvnPL
	where EvnPL.Diag_Code in ('E01.0','E01.2','E04.0')
	group by EvnPL.Diag_Code
) as t1

union all select 
	'Многоузловой (эндемический) зоб, связанный с йодной недостаточностью, нетоксический одноузловой, нетоксический многоузловой зоб' as v1,
	'03' as v2,
	'E01.1,E04.1,E04.2' as v3,
	coalesce(sum(t1.EvnPL_id),0) as v4,
	coalesce(sum(t1.v5),0) as v5, 
	coalesce(sum(t1.v6),0) as v6,
	coalesce(sum(t1.v7),0) as v7,
	coalesce(sum(t1.v8),0) as v8, 
	coalesce(sum(t1.v9),0) as v9, 
	coalesce(sum(t1.v10),0) as v10, 
	coalesce(sum(t1.v11),0) as v11,
	coalesce(sum(t1.v12),0) as v12, 
	coalesce(sum(t1.v13),0) as v13, 
	coalesce(sum(t1.v14),0) as v14, 
	coalesce(sum(t1.v15),0) as v15, 
	coalesce(sum(t1.v16),0) as v16, 
	coalesce(sum(t1.v17),0) as v17, 
	coalesce(sum(t1.v18),0) as v18 
from(
	select
		EvnPL.Diag_Code,
		count(distinct EvnPL.EPL_id ) as EvnPL_id,
		count(distinct case when Person_Age between 0 and 4 then EvnPL.EPL_id end) as v5,
		count(distinct case when Person_Age between 5 and 9 then EvnPL.EPL_id end) as v6,
		count(distinct case when Person_Age between 10 and 14 then EvnPL.EPL_id end) as v7,
		count(distinct case when Person_Age between 15 and 17 then EvnPL.EPL_id end) as v8,
		count(distinct case when Person_Age between 18 and 19 then EvnPL.EPL_id end) as v9,
		count(distinct case when Person_Age between 20 and 24 then EvnPL.EPL_id end) as v10,
		count(distinct case when Person_Age between 25 and 29 then EvnPL.EPL_id end) as v11,
		count(distinct case when Person_Age between 30 and 34 then EvnPL.EPL_id end) as v12,
		count(distinct case when Person_Age between 35 and 39 then EvnPL.EPL_id end) as v13,
		count(distinct case when Person_Age between 40 and 44 then EvnPL.EPL_id end) as v14,
		count(distinct case when Person_Age between 45 and 49 then EvnPL.EPL_id end) as v15,
		count(distinct case when Person_Age between 50 and 54 then EvnPL.EPL_id end) as v16,
		count(distinct case when Person_Age between 55 and 59 then EvnPL.EPL_id end) as v17,
		count(distinct case when Person_Age >= 60 then EvnPL.EPL_id end) as v18
	from GetEvnPLAll as EvnPL
	where EvnPL.Diag_Code in ('E01.1','E04.1','E04.2')
	group by EvnPL.Diag_Code
) as t1

union all select 
	'Субклинический гипотериоз вследствии йодной недостаточности, другие формы гипотериоза' as v1,
	'04' as v2,
	'E02,E03' as v3,
	coalesce(sum(t1.EvnPL_id),0) as v4,
	coalesce(sum(t1.v5),0) as v5, 
	coalesce(sum(t1.v6),0) as v6,
	coalesce(sum(t1.v7),0) as v7,
	coalesce(sum(t1.v8),0) as v8, 
	coalesce(sum(t1.v9),0) as v9, 
	coalesce(sum(t1.v10),0) as v10, 
	coalesce(sum(t1.v11),0) as v11,
	coalesce(sum(t1.v12),0) as v12, 
	coalesce(sum(t1.v13),0) as v13, 
	coalesce(sum(t1.v14),0) as v14, 
	coalesce(sum(t1.v15),0) as v15, 
	coalesce(sum(t1.v16),0) as v16, 
	coalesce(sum(t1.v17),0) as v17, 
	coalesce(sum(t1.v18),0) as v18 
from(
	select 
		EvnPL.Diag_Code,
		count(distinct EvnPL.EPL_id ) as EvnPL_id,
		count(distinct case when Person_Age between 0 and 4 then EvnPL.EPL_id end) as v5,
		count(distinct case when Person_Age between 5 and 9 then EvnPL.EPL_id end) as v6,
		count(distinct case when Person_Age between 10 and 14 then EvnPL.EPL_id end) as v7,
		count(distinct case when Person_Age between 15 and 17 then EvnPL.EPL_id end) as v8,
		count(distinct case when Person_Age between 18 and 19 then EvnPL.EPL_id end) as v9,
		count(distinct case when Person_Age between 20 and 24 then EvnPL.EPL_id end) as v10,
		count(distinct case when Person_Age between 25 and 29 then EvnPL.EPL_id end) as v11,
		count(distinct case when Person_Age between 30 and 34 then EvnPL.EPL_id end) as v12,
		count(distinct case when Person_Age between 35 and 39 then EvnPL.EPL_id end) as v13,
		count(distinct case when Person_Age between 40 and 44 then EvnPL.EPL_id end) as v14,
		count(distinct case when Person_Age between 45 and 49 then EvnPL.EPL_id end) as v15,
		count(distinct case when Person_Age between 50 and 54 then EvnPL.EPL_id end) as v16,
		count(distinct case when Person_Age between 55 and 59 then EvnPL.EPL_id end) as v17,
		count(distinct case when Person_Age >= 60 then EvnPL.EPL_id end) as v18
	from GetEvnPLAll as EvnPL
	where left(EvnPL.Diag_Code,3) in ('E02','E03')
	group by EvnPL.Diag_Code
) as t1

union all select 'Тиретоксикоз (гипертиреоз)' as v1,
	'05' as v2,
	'E05' as v3,
	coalesce(sum(t1.EvnPL_id),0) as v4,
	coalesce(sum(t1.v5),0) as v5, 
	coalesce(sum(t1.v6),0) as v6,
	coalesce(sum(t1.v7),0) as v7,
	coalesce(sum(t1.v8),0) as v8, 
	coalesce(sum(t1.v9),0) as v9, 
	coalesce(sum(t1.v10),0) as v10, 
	coalesce(sum(t1.v11),0) as v11,
	coalesce(sum(t1.v12),0) as v12, 
	coalesce(sum(t1.v13),0) as v13, 
	coalesce(sum(t1.v14),0) as v14, 
	coalesce(sum(t1.v15),0) as v15, 
	coalesce(sum(t1.v16),0) as v16, 
	coalesce(sum(t1.v17),0) as v17, 
	coalesce(sum(t1.v18),0) as v18 
from(
	select 
		EvnPL.Diag_Code,
		count(distinct EvnPL.EPL_id ) as EvnPL_id,
		count(distinct case when Person_Age between 0 and 4 then EvnPL.EPL_id end) as v5,
		count(distinct case when Person_Age between 5 and 9 then EvnPL.EPL_id end) as v6,
		count(distinct case when Person_Age between 10 and 14 then EvnPL.EPL_id end) as v7,
		count(distinct case when Person_Age between 15 and 17 then EvnPL.EPL_id end) as v8,
		count(distinct case when Person_Age between 18 and 19 then EvnPL.EPL_id end) as v9,
		count(distinct case when Person_Age between 20 and 24 then EvnPL.EPL_id end) as v10,
		count(distinct case when Person_Age between 25 and 29 then EvnPL.EPL_id end) as v11,
		count(distinct case when Person_Age between 30 and 34 then EvnPL.EPL_id end) as v12,
		count(distinct case when Person_Age between 35 and 39 then EvnPL.EPL_id end) as v13,
		count(distinct case when Person_Age between 40 and 44 then EvnPL.EPL_id end) as v14,
		count(distinct case when Person_Age between 45 and 49 then EvnPL.EPL_id end) as v15,
		count(distinct case when Person_Age between 50 and 54 then EvnPL.EPL_id end) as v16,
		count(distinct case when Person_Age between 55 and 59 then EvnPL.EPL_id end) as v17,
		count(distinct case when Person_Age >= 60 then EvnPL.EPL_id end) as v18
	from GetEvnPLAll as EvnPL
	where left(EvnPL.Diag_Code,3) = 'E05'
	group by EvnPL.Diag_Code
) as t1

union all select 
	'Тиреоидит' as v1,
	'06' as v2,
	'E06' as v3,coalesce(sum(t1.EvnPL_id),0) as v4,
	coalesce(sum(t1.v5),0) as v5, 
	coalesce(sum(t1.v6),0) as v6,
	coalesce(sum(t1.v7),0) as v7,
	coalesce(sum(t1.v8),0) as v8, 
	coalesce(sum(t1.v9),0) as v9, 
	coalesce(sum(t1.v10),0) as v10, 
	coalesce(sum(t1.v11),0) as v11,
	coalesce(sum(t1.v12),0) as v12, 
	coalesce(sum(t1.v13),0) as v13, 
	coalesce(sum(t1.v14),0) as v14, 
	coalesce(sum(t1.v15),0) as v15, 
	coalesce(sum(t1.v16),0) as v16, 
	coalesce(sum(t1.v17),0) as v17, 
	coalesce(sum(t1.v18),0) as v18 
from(
	select 
		EvnPL.Diag_Code,
		count(distinct EvnPL.EPL_id ) as EvnPL_id,
		count(distinct case when Person_Age between 0 and 4 then EvnPL.EPL_id end) as v5,
		count(distinct case when Person_Age between 5 and 9 then EvnPL.EPL_id end) as v6,
		count(distinct case when Person_Age between 10 and 14 then EvnPL.EPL_id end) as v7,
		count(distinct case when Person_Age between 15 and 17 then EvnPL.EPL_id end) as v8,
		count(distinct case when Person_Age between 18 and 19 then EvnPL.EPL_id end) as v9,
		count(distinct case when Person_Age between 20 and 24 then EvnPL.EPL_id end) as v10,
		count(distinct case when Person_Age between 25 and 29 then EvnPL.EPL_id end) as v11,
		count(distinct case when Person_Age between 30 and 34 then EvnPL.EPL_id end) as v12,
		count(distinct case when Person_Age between 35 and 39 then EvnPL.EPL_id end) as v13,
		count(distinct case when Person_Age between 40 and 44 then EvnPL.EPL_id end) as v14,
		count(distinct case when Person_Age between 45 and 49 then EvnPL.EPL_id end) as v15,
		count(distinct case when Person_Age between 50 and 54 then EvnPL.EPL_id end) as v16,
		count(distinct case when Person_Age between 55 and 59 then EvnPL.EPL_id end) as v17,
		count(distinct case when Person_Age >= 60 then EvnPL.EPL_id end) as v18
	from GetEvnPLAll as EvnPL
	where left(EvnPL.Diag_Code,3) = 'E06'
	group by EvnPL.Diag_Code
) as t1

