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
),

query as
(
select distinct
			'N' as typeof,
			env.Person_id,
			EvnNotifyVener_setDate as setdate,
			--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 'A50' and 'A63.9') or (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	

			
		union all
	select distinct
			'T' as typeof,
			epl.Person_id,
			EvnPL_setDate  as setdate,
			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 'A50' and 'A63.9') or (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,
			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 'A50' and 'A63.9') or (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,
			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 'A50' and 'A63.9') or (Diag_Code between 'B35.0' and 'B35.9' or Diag_Code between 'B86' and 'B86.9') ) )
			and personregister_setDate between p_begdate and p_enddate
			and MO.MorbusVener_DiagDT between p_begdate and p_enddate
			and (PR.Lpu_iid = p_lpu_id or p_lpu_id is null)

),
taps as
(
select 
	--typeof,
	person_id,
	--setdate,
	person_fio,
	vozr,
	klregion_id,
	sum(personcategorytype_id) as personcategorytype_id,
	sum(venersocgroup_id) as venersocgroup_id,
	klcountry_id,
	klrgn_omsid,
	documenttype_id,
	klcountry_docid,
	socstatus_id,
	socstatus_sysnick,
	klareatype_id,
	diag_code,
	sex_id
from query

group by
	person_id,
	person_fio,
	vozr,
	klregion_id,

	klcountry_id,
	klrgn_omsid,
	documenttype_id,
	klcountry_docid,
	socstatus_id,
	socstatus_sysnick,
	klareatype_id,
	diag_code,
	sex_id
),

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
	from(values
		('A50','A53.9',1,'Сифилис','01','A50-A53'),
		('A54','A54.9',2,'Гонококковая инфекция','02','A54.0-A54.9'),
		('A59.0','A59.0',3,'Трихомоноз','03','A59.0,A59.8'),
		('A59.8','A59.8',3,'Трихомоноз','03','A59.0,A59.8'),
		('A56.0','A56.4',4,'Хламидийные инфекции','04','A56.0-A56.4, A56.8'),
		('A56.8','A56.8',4,'Хламидийные инфекции','04','A56.0-A56.4, A56.8'),		
		('A60.0','A60.1',5,'Аногенитальная герпетическая вирусная инфекция','05','A60.0, A60.1, A60.9'),
		('A60.9','A60.9',5,'Аногенитальная герпетическая вирусная инфекция','05','A60.0, A60.1, A60.9'),
		('A63.0','A63.0',6,'Аногенитальные (вен.) бородавки','06','A63.0'),
		('B86','B86.9',7,'Чесотка','07','B86')
	)as t_table(
		RepDiag_FromDiag,
		RepDiag_ToDiag,
		RepDiag_Row,
		RepDiag_Text1,
		RepDiag_Text2,
		RepDiag_Text3
	)
)

select
	RepDiag_Row as "RepDiag_Row",
	RepDiag_Text1 as "RepDiag_Text1",
	RepDiag_Text2 as "RepDiag_Text2",
	RepDiag_Text3 as "RepDiag_Text3",

	count(distinct
		case when 
				personcategorytype_id = 1 
				or (personcategorytype_id = 0 and KLRegion_id = dbo.GetRegion() ) 
		then taps.person_id end ) as v3, -- жители данного субъекта РФ
	count(distinct
		case when personcategorytype_id = 2 
				or (personcategorytype_id = 0 and (KLRegion_id!=dbo.GetRegion() and KLcountry_id = 643) )
				then taps.person_id end ) as v4, -- жители других регионов РФ
	count(distinct
		case when personcategorytype_id = 8
				or (personcategorytype_id = 0 and (DocumentType_id = 9 and KLcountry_id != 643
					and (KLRegion_id = dbo.GetRegion() or taps.KLRgn_omsid is null) ))
				then taps.person_id end ) as v5, -- иностранные граждане
	count(distinct
		case when personcategorytype_id = 10
					or (personcategorytype_id = 0 and ( (taps.KLcountry_Docid in (31,51,112,398,417,498,762,795,860,804)
					and taps.DocumentType_id = 9
					and (taps.KLRgn_omsid != dbo.GetRegion() or taps.KLRgn_omsid is null)) ) )
				then taps.person_id end)  as v6, -- граждане СНГ
	count(distinct
		case when personcategorytype_id = 6
				or (personcategorytype_id = 0 and Taps.SocStatus_SysNick = 'bomzh')
				then taps.person_id end ) as v7, -- контингент БОМЖ
	count(distinct
		/*case when (taps.KLRgn_id is not null
					or (taps.DocumentType_id = 9 and KLcountry_Docid != 643)
					or Taps.SocStatus_SysNick = 'bomzh')
					or personcategorytype_id in (1,2,3,6,8,10)
				then */
				taps.person_id ) as v_all_2005, -- таблица 2005, графа ИТОГО

	'|' as ter,
	-- таблица 2006
	count(distinct
		case
			when ( (venersocgroup_id = 1) or (venersocgroup_id = 0 and socstatus_sysnick = 'rab') )
				then taps.person_id end ) as v3_2006, -- работающие
	count(distinct
		case
			when ( (venersocgroup_id = 2) )--or (typeof != 'N' and venersocgroup_id is null and socstatus_sysnick = 'nrab') )
				then taps.person_id end ) as v4_2006, -- неработающие
	count(distinct
		case when venersocgroup_id = 3 --(Taps.SocStatus_SysNick like '%child%' and vozr < 7)
				then taps.person_id end) as v5_2006, -- дошкольник
	count(distinct
		case when venersocgroup_id = 4
				then taps.person_id end ) as v6_2006, -- учащийся
	count(distinct
		case when venersocgroup_id = 5
				then taps.person_id end ) as v7_2006, -- студент
	count(distinct
		case when venersocgroup_id = 6
				then taps.person_id end ) as v8_2006, -- пенсионер
	count(distinct
		case when venersocgroup_id = 7
			/*Taps.SocStatus_SysNick in(
					'mvd','gtk','dnp','ra','fapsi','fsb','fps','mcs','fire','warother')*/
				then taps.person_id end ) as v9_2006, -- военнослужащий (+ МВД, ФСБ и прочая братия)
	count(distinct
		case when venersocgroup_id is not null
				/*(Taps.SocStatus_SysNick in(
					'rab','nrab','study','pen','mvd','gtk','dnp','student',
					'ra','fapsi','fsb','fps','mcs','fire','warother') or Taps.SocStatus_SysNick like '%child%')*/
				then taps.person_id end) as v_all_2006 -- таблица 2006, графа ИТОГО
from MyRepDiag as repd
	left join query as Taps on Taps.Diag_Code between repd.RepDiag_FromDiag and repd.RepDiag_ToDiag
group by
	RepDiag_Row,
	RepDiag_Text1,
	RepDiag_Text2,
	RepDiag_Text3
