with params as(
	select
		cast($1 as bigint) as p_lpu_id,
		cast($2 as timestamp) as p_begdate,
		cast($3 as timestamp) as p_enddate
),

MyRepDiag as (
	select
		t_table.RepDiag_FromDiag,
		t_table.RepDiag_ToDiag,
		t_table.RepDiag_Row,
		t_table.RepDiag_Text1,
		t_table.RepDiag_Text2,
		t_table.RepDiag_Text3,
		t_table.RepDiag_Text4,
		t_table.RepDiag_Text5,
		t_table.RepDiag_Text6,
		t_table.RepDiag_Text7,
		t_table.RepDiag_Text8
	from(values
		('B35','B35.9',1,'Дерматофитии (всего)','в том числе: мужчины                       
женщины','м','ж','01','02','03','В35.0-В35.9'),
		('0','0',2,'это строчки быть не должно','из них:микроспории','м','ж','999','04','05','В35.0, 4'),
		('0','0',3,'это строчки быть не должно','трихофитии','м','ж','999','06','07','В35.0, 4'),
		('B35.1','B35.3',4,'микозы стоп и кистей, всего','в том числе: мужчины                       
женщины','м','ж','08','09','10','В35.1-3'),
		('B35.1','B35.1',5,'это строчки быть не должно','из них онихомикозы','м','ж','999','11','12','В35.1'),
		('B86','B86.9',6,'Чесотка','в том числе: мужчины                       
женщины','м','ж','13','14','15','В86')
	)as t_table(
		RepDiag_FromDiag,
		RepDiag_ToDiag,
		RepDiag_Row,
		RepDiag_Text1,
		RepDiag_Text2,
		RepDiag_Text3,
		RepDiag_Text4,
		RepDiag_Text5,
		RepDiag_Text6,
		RepDiag_Text7,
		RepDiag_Text8
	)
)

select
	RepDiag_Row as "RepDiag_Row",
	RepDiag_Text1 as "RepDiag_Text1",
	RepDiag_Text2 as "RepDiag_Text2",
	RepDiag_Text3 as "RepDiag_Text3",
	RepDiag_Text4 as "RepDiag_Text4",
	RepDiag_Text5 as "RepDiag_Text5",
	RepDiag_Text6 as "RepDiag_Text6",
	RepDiag_Text7 as "RepDiag_Text7",
	RepDiag_Text8 as "RepDiag_Text8",
	
	count(distinct taps.person_id) as "v5All",
	count(distinct
		case
			when taps.vozr between 0 and 14 
				then taps.person_id end) as "v6All",
	count(distinct
		case
			when taps.vozr between 15 and 17 
				then taps.person_id end) as "v7All",
	count(distinct
		case
			when taps.vozr between 18 and 29 
				then taps.person_id end) as "v8All",
	count(distinct
		case
			when taps.vozr between 30 and 39 
				then taps.person_id end) as "v9All",
	count(distinct
		case
			when taps.vozr >=40 
				then taps.person_id end) as "v10All",
	count(distinct
		case
			when Taps.KLAreaType_id = 2 
				then taps.person_id end) as "v11All",
	count(distinct
		case
			when taps.vozr between 0 and 14 and Taps.KLAreaType_id = 2 
				then taps.person_id end) as "v12All",
	count(distinct
		case
			when taps.vozr between 15 and 17 and Taps.KLAreaType_id = 2 
				then taps.person_id end) as "v13All",
	count(distinct
		case
			when taps.Sex_id = 1 
				then taps.person_id end) as v5m,
	count(distinct
		case
			when taps.Sex_id = 1 and taps.vozr between 0 and 14 
				then taps.person_id end) as v6m,
	count(distinct
		case
			when taps.Sex_id = 1 and taps.vozr between 15 and 17 
				then taps.person_id end) as v7m,
	count(distinct
		case
			when taps.Sex_id = 1 and taps.vozr between 18 and 29 
				then taps.person_id end) as v8m,
	count(distinct
		case
			when taps.Sex_id = 1 and taps.vozr between 30 and 39 
				then taps.person_id end) as v9m,
	count(distinct
		case
			when taps.Sex_id = 1 and taps.vozr >= 40 
				then taps.person_id end) as v10m,
	count(distinct
		case
			when taps.Sex_id = 1 and Taps.KLAreaType_id = 2 
				then taps.person_id end) as v11m,
	count(distinct
		case
			when taps.Sex_id = 1 and taps.vozr between 0 and 14 and Taps.KLAreaType_id = 2 
				then taps.person_id end) as v12m,
	count(distinct
		case
			when taps.Sex_id = 1 and taps.vozr between 15 and 17 and Taps.KLAreaType_id = 2 
				then taps.person_id end) as v13m,
	count(distinct
		case
			when taps.Sex_id = 2 
				then taps.person_id end) as v5f,
	count(distinct
		case
			when taps.Sex_id = 2 and taps.vozr between 0 and 14 
				then taps.person_id end) as v6f,
	count(distinct
		case
			when taps.Sex_id = 2 and taps.vozr between 15 and 17 
				then taps.person_id end) as v7f,
	count(distinct
		case
			when taps.Sex_id = 2 and taps.vozr between 18 and 29 
				then taps.person_id end) as v8f,
	count(distinct
		case
			when taps.Sex_id = 2 and taps.vozr between 30 and 39 
				then taps.person_id end) as v9f,
	count(distinct
		case
			when taps.Sex_id = 2 and taps.vozr >= 40 
				then taps.person_id end) as v10f,
	count(distinct
		case
			when taps.Sex_id = 2 and Taps.KLAreaType_id = 2 
				then taps.person_id end) as v11f,
	count(distinct
		case
			when taps.Sex_id = 2 and taps.vozr between 0 and 14 and Taps.KLAreaType_id = 2 
				then taps.person_id end) as v12f,
	count(distinct
		case
			when taps.Sex_id = 2 and taps.vozr between 15 and 17 and Taps.KLAreaType_id = 2 
				then taps.person_id end) as v13f
from MyRepDiag as repd
	left join(
select distinct
			'N' as typeof,
			env.Person_id,
			EvnNotifyVener_setDate as setdate,
			case when (EvnNotifyVener_Pathogen like '%icrosporum%' or bm.bactmicro_pid = 9316) then '2'
						when (EvnNotifyVener_Pathogen like '%richophyton%' or bm.bactmicro_pid = 9349) then '3'
						else EvnNotifyVener_Pathogen end as Pathogen,
			--null::timestamp as setdt,
			ps.person_surname || ' ' || coalesce(ps.person_firname, '') || ' ' || coalesce(ps.person_secname, '') as person_fio,
			dbo.AgeYMD(ps.Person_Birthday,env.evnnotifyvener_setdate,1) as vozr,
			coalesce(adr.KLRgn_id, adr2.KLRgn_id,0) as KLRegion_id,
			coalesce(personcategorytype_id,0) as personcategorytype_id,
			coalesce(venersocgroup_id,0) as venersocgroup_id,
			coalesce(adr.KLCountry_id,0) as KLCountry_id,
			coalesce(oms.KLRgn_id,0) as KLRgn_omsid, -- место оформления полиса
			coalesce(doc.DocumentType_id,0) as DocumentType_id,
			coalesce(NatSt.KLCountry_id,0) as KLCountry_Docid,
			sc.SocStatus_id,
			sc.SocStatus_SysNick,
			coalesce(adr.KLAreaType_id, adr2.KLAreaType_id,0) as KLAreaType_id,
			diag.diag_code,
			ps.Sex_id
		
		from params
			inner join v_EvnNotifyVener env on true
					and (env.Lpu_id = p_lpu_id or p_lpu_id is null)
					and (EvnNotifyVener_setDate between p_begdate and p_enddate)
			inner join v_Diag as diag on env.Diag_id = diag.Diag_id
				and  (Diag_Code between 'B35.0' and 'B35.9' or Diag_Code between 'B86' and 'B86.9')
				--and ( (diag_code between 'A50.0' and 'A63.9') or diag_code = 'B86')
			inner join v_PersonState as ps on env.Person_id = ps.Person_id
			left join v_Address as adr on ps.PAddress_id = adr.Address_id
			left join v_Address as adr2 on ps.UAddress_id = adr2.Address_id
			left join v_Document as doc on ps.Document_id = doc.Document_id
			left join NationalityStatus as NatSt on ps.NationalityStatus_id = NatSt.NationalityStatus_id
			left join v_SocStatus as sc on ps.SocStatus_id = sc.SocStatus_id
			left join v_Polis as pls on pls.Polis_id = ps.Polis_id
			left join v_OmsSprTerr as oms on oms.OmsSprTerr_id = pls.OmsSprTerr_id	
			
			left join v_bactmicro bm on bm.bactmicro_id = env.bactmicro_id

			
		union all
	select distinct
			'T' as typeof,
			epl.Person_id,
			EvnPL_setDate  as setdate,
			null as EvnNotifyVener_Pathogen,
			ps.person_surname || ' ' || coalesce(ps.person_firname, '') || ' ' || coalesce(ps.person_secname, '') as person_fio,
			dbo.AgeYMD(ps.Person_Birthday,epl.evnpl_setdate,1) as vozr,
			coalesce(adr.KLRgn_id, adr2.KLRgn_id,0) as KLRegion_id,
			0 as personcategorytype_id,
			0 as venersocgroup_id,
			coalesce(adr.KLCountry_id,0) as KLCountry_id,
			coalesce(oms.KLRgn_id,0) as KLRgn_omsid, -- место оформления полиса
			coalesce(doc.DocumentType_id,0) as DocumentType_id,
			coalesce(NatSt.KLCountry_id,0) as KLCountry_Docid,
			sc.SocStatus_id,
			sc.SocStatus_SysNick,
			coalesce(adr.KLAreaType_id, adr2.KLAreaType_id,0) as KLAreaType_id,
			diag.diag_code,
			ps.Sex_id
		from v_EvnPL as epl
			inner join params on (epl.Lpu_id = p_lpu_id or p_lpu_id is null)
			inner join v_Diag as diag on epl.Diag_id = diag.Diag_id
			
			inner join v_PersonState as ps on epl.Person_id = ps.Person_id
			left join v_Address as adr on ps.PAddress_id = adr.Address_id
			left join v_Address as adr2 on ps.UAddress_id = adr2.Address_id
			left join v_Document as doc on ps.Document_id = doc.Document_id
			left join NationalityStatus as NatSt on ps.NationalityStatus_id = NatSt.NationalityStatus_id
			left join v_SocStatus as sc on ps.SocStatus_id = sc.SocStatus_id
			left join v_Polis as pls on pls.Polis_id = ps.Polis_id
			left join v_OmsSprTerr as oms on oms.OmsSprTerr_id = pls.OmsSprTerr_id
			
			left join v_DeseaseType as dt on dt.DeseaseType_id = epl.DeseaseType_id
		where  (Diag_Code between 'B35.0' and 'B35.9' or Diag_Code between 'B86' and 'B86.9')
			and EvnPL_setDate between p_begdate and p_enddate
			and EvnPL_IsFinish = 2
			--and dt.DeseaseType_SysNick in ('sharp','new','hrnew')
			and dt.DeseaseType_SysNick in ('new','hrnew')
		union all
		select 
			'D' as typeof,
			PD.Person_id,
			PD.PersonDisp_begDate as setdate,
			null as EvnNotifyVener_Pathogen,
			ps.person_surname || ' ' || coalesce(ps.person_firname, '') || ' ' || coalesce(ps.person_secname, '') as person_fio,
			dbo.AgeYMD(ps.Person_Birthday,PD.PersonDisp_begDate,1) as vozr,
			coalesce(adr.KLRgn_id, adr2.KLRgn_id,0) as KLRegion_id,
			0 as personcategorytype_id,
			0 as venersocgroup_id,
			coalesce(adr.KLCountry_id,0) as KLCountry_id,
			coalesce(oms.KLRgn_id,0) as KLRgn_omsid, -- место оформления полиса
			coalesce(doc.DocumentType_id,0) as DocumentType_id,
			coalesce(NatSt.KLCountry_id,0) as KLCountry_Docid,
			sc.SocStatus_id,
			sc.SocStatus_SysNick,
			coalesce(adr.KLAreaType_id, adr2.KLAreaType_id,0) as KLAreaType_id,
			diag.diag_code,
			ps.Sex_id
		from v_PersonDisp PD
			inner join v_PersonState as ps on pd.Person_id = ps.Person_id
			left join v_Address as adr on ps.PAddress_id = adr.Address_id
			left join v_Address as adr2 on ps.UAddress_id = adr2.Address_id
			left join v_Document as doc on ps.Document_id = doc.Document_id
			left join NationalityStatus as NatSt on ps.NationalityStatus_id = NatSt.NationalityStatus_id
			left join v_SocStatus as sc on ps.SocStatus_id = sc.SocStatus_id
			left join v_Polis as pls on pls.Polis_id = ps.Polis_id
			left join v_OmsSprTerr as oms on oms.OmsSprTerr_id = pls.OmsSprTerr_id
			
			inner join params as params on (params.p_lpu_id = PD.Lpu_id)
			inner join v_Diag diag on PD.Diag_id = diag.Diag_id
		where  (Diag_Code between 'B35.0' and 'B35.9' or Diag_Code between 'B86' and 'B86.9')
			and PD.PersonDisp_begDate between p_begdate and p_enddate
			
		union all
		select
			'R' as type_of,
			--PR.Lpu_iid as Lpu_id,
			PS.Person_id,
			coalesce(MO.MorbusVener_DiagDT, personregister_setDate) as setdate,
			null as EvnNotifyVener_Pathogen,
			ps.person_surname || ' ' || coalesce(ps.person_firname, '') || ' ' || coalesce(ps.person_secname, '') as person_fio,
			dbo.AgeYMD(ps.Person_Birthday,PR.PersonRegister_setDate,1) as vozr,
			coalesce(adr.KLRgn_id, adr2.KLRgn_id,0) as KLRegion_id,
			0 as personcategorytype_id,
			0 as venersocgroup_id,
			coalesce(adr.KLCountry_id,0) as KLCountry_id,
			coalesce(oms.KLRgn_id,0) as KLRgn_omsid, -- место оформления полиса
			coalesce(doc.DocumentType_id,0) as DocumentType_id,
			coalesce(NatSt.KLCountry_id,0) as KLCountry_Docid,
			sc.SocStatus_id,
			sc.SocStatus_SysNick,
			coalesce(adr.KLAreaType_id, adr2.KLAreaType_id,0) as KLAreaType_id,
			diag.diag_code,
			ps.sex_id
		
		from params
			inner join v_PersonState as ps on true --epl.Person_id = ps.Person_id
			left join v_Address as adr on ps.PAddress_id = adr.Address_id
			left join v_Address as adr2 on ps.UAddress_id = adr2.Address_id
			left join v_Document as doc on ps.Document_id = doc.Document_id
			left join NationalityStatus as NatSt on ps.NationalityStatus_id = NatSt.NationalityStatus_id
			left join v_SocStatus as sc on ps.SocStatus_id = sc.SocStatus_id
			left join v_Polis as pls on pls.Polis_id = ps.Polis_id
			left join v_OmsSprTerr as oms on oms.OmsSprTerr_id = pls.OmsSprTerr_id
			
			inner join v_MorbusType MT on MT.MorbusType_SysNick = 'vener'
			inner join v_PersonRegister PR on PR.Person_id = PS.Person_id 
				and PR.MorbusType_id = MT.MorbusType_id
			left join v_Diag Diag on Diag.Diag_id = PR.Diag_id 
			--left join v_EvnNotifyVener EN on EN.EvnNotifyVener_id = PR.EvnNotifyBase_id
			left join v_MorbusVener MO on MO.Morbus_id = PR.Morbus_id -- select * from v_MorbusVener limit 10
		where  (Diag_Code between 'B35.0' and 'B35.9' or Diag_Code between 'B86' and 'B86.9')
			and coalesce(MO.MorbusVener_DiagDT, personregister_setDate) between p_begdate and p_enddate -- (сначала дата установления диагноза, а потом дата включения в реестр)
			and (PR.Lpu_iid = p_lpu_id or p_lpu_id is null)
	)Taps on (Taps.Diag_Code between repd.RepDiag_FromDiag and repd.RepDiag_ToDiag) or taps.Pathogen = repd.RepDiag_Row::varchar

group by
	RepDiag_Row,
	RepDiag_Text1,
	RepDiag_Text2,
	RepDiag_Text3,
	RepDiag_Text4,
	RepDiag_Text5,
	RepDiag_Text6,
	RepDiag_Text7,
	RepDiag_Text8

