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,
		cast($4 as bigint) as p_isreg),

LpuP as( -- правопреемник
	select
		/*f7_2019_2010_pg.rptdesign*/
		Lpu.Lpu_id,
		Lpu.Lpu_Nick,
		Lpu.Lpu_endDate,
		Lpu.Lpu_pid,
		params.*
	from params
		left join lateral(
			select *
			from rpt.GetLpuList(p_Lpu_id, p_begdate, p_enddate, 1, 2) as Lpu
		)Lpu on true
),

epl as( --ТАП
	select 
		Evn.Evn_id as EvnPL_id, 
		evn.person_id,
		diag.diag_code,
		evnpl.DeseaseType_id,
		cast(Evn.Evn_disDT as date) as evnpl_disdate,
		p_isreg,
		p_begdate,
		p_enddate
	from LpuP 
		inner join Evn on LpuP.lpu_id = evn.lpu_id
			and Evn.EvnClass_id in (3, 6, 35, 60)
			and cast(Evn.Evn_disDT as date) between p_begdate and p_enddate
		inner join EvnPLBase on Evn.Evn_id = EvnPLBase.Evn_id
		inner join EvnPL on EvnPLBase.Evn_id = EvnPL.Evn_id
		inner join diag on diag.diag_id = evnpl.diag_id
			and left(Diag.Diag_Code,1) in ('C', 'D')
	where coalesce(Evn.Evn_deleted, 1) = 1
		and (coalesce(EvnPLBase.EvnPLBase_IsFinish, 1) = 2)
),

eps as( --КВС
	select 
		eps.evnps_id,
		eps.person_id,
		diag.diag_code,
		coalesce(es.deseasetype_id, eps.DeseaseType_id) as DeseaseType_id,
		evnps_disdate,
		p_isreg,
		p_begdate,
		p_enddate
	from LpuP 
		inner join v_evnps as eps on LpuP.lpu_id = eps.lpu_id 
		inner join v_evnsection as es on es.evnsection_pid = eps.evnps_id
			and eps.evnps_disdate between p_begdate and p_enddate
		inner join diag on diag.diag_id = coalesce(es.diag_id, eps.diag_id)
			and left(Diag.Diag_Code,1) in ('C', 'D')
),

dvn as( --ДВН
	select 
		disp.EvnPLDispDop13_id,
		disp.person_id,
		diag.diag_code,
		1 as DeseaseType_id,
		disp.evnpldispdop13_disdate,
		p_isreg,
		p_begdate,
		p_enddate
	from LpuP 
		inner join v_EvnPLDispDop13 as disp on LpuP.lpu_id = disp.lpu_id 
			and disp.evnpldispdop13_disdate between p_begdate and p_enddate
		inner join v_EvnVizitDispDop as vizit
			on vizit.EvnVizitDispDop_pid = disp.EvnPLDispDop13_id
		left join diag on diag.diag_id = coalesce(vizit.diag_id, disp.diag_id)
	where coalesce(disp.EvnPLDispDop13_IsFinish, 1) = 2
		and vizit.DopDispDiagType_id = 2 --Выявленное во время дополнительной диспансеризации (профосмотра)
		and left(Diag.Diag_Code,1) in ('C', 'D')
),

povn as( --ПОВН
	select 
		prof.EvnPLDispProf_id,
		prof.person_id,
		diag.diag_code,
		1 as DeseaseType_id,
		prof.EvnPLDispProf_disDate,
		p_isreg,
		p_begdate,
		p_enddate
	from LpuP 
		inner join v_EvnPLDispProf as prof on LpuP.lpu_id = prof.lpu_id 
			and prof.EvnPLDispProf_disDate between p_begdate and p_enddate
		left join v_EvnVizitDispDop as vizit on vizit.EvnVizitDispDop_pid = prof.EvnPLDispProf_id
		left join diag on diag.diag_id = coalesce(vizit.diag_id, prof.diag_id)
	where coalesce(prof.EvnPLDispProf_IsFinish, 1) = 2
		and vizit.DopDispDiagType_id = 2 --Выявленное во время дополнительной диспансеризации (профосмотра)
		and left(Diag.Diag_Code,1) in ('C', 'D')
),

dds as(--ДДС
	select
		orp.EvnPLDispOrp_id,
		orp.person_id,
		diag.diag_code,
		1 as DeseaseType_id,
		orp.EvnPLDispOrp_disDate,
		p_isreg,
		p_begdate,
		p_enddate
	from lpup 
		inner join v_EvnPLDispOrp as orp on lpup.lpu_id = orp.lpu_id 
			and orp.EvnPLDispOrp_disDate between p_begdate and p_enddate 
		left join v_EvnVizitDispOrp as vizit on vizit.EvnVizitDispOrp_pid = orp.EvnPLDispOrp_id
		left join diag on diag.diag_id = vizit.diag_id
	where coalesce(orp.EvnPLDispOrp_IsFinish, 1) = 2
		and vizit.DopDispDiagType_id = 2
		and left(Diag.Diag_Code,1) in ('C', 'D')
),

mon as(--МОН
	select 
		teen.EvnPLDispTeenInspection_id,
		teen.person_id,
		diag.diag_code,
		1 as DeseaseType_id,
		teen.EvnPLDispTeenInspection_disDate,
		p_isreg,
		p_begdate,
		p_enddate
	from lpup 
		inner join v_EvnPLDispTeenInspection as teen on lpup.lpu_id = teen.lpu_id 
			and teen.EvnPLDispTeenInspection_disDate between p_begdate and p_enddate
		left join v_EvnVizitDispDop vizit
			on vizit.EvnVizitDispDop_pid = teen.EvnPLDispTeenInspection_id
		left join diag on diag.diag_id = vizit.diag_id
	where coalesce(teen.EvnPLDispTeenInspection_IsFinish, 1) = 2
		and vizit.DopDispDiagType_id = 2
		and left(Diag.Diag_Code,1) in ('C', 'D')
),

doc as(
	select 
		person.sex_id,
		all_doc.id,
		all_doc.Person_id,
		all_doc.DeseaseType_id,
		all_doc.Diag_Code,
		Address.Klareatype_id,
		MOB.TumorPrimaryMultipleType_id,
		PD.DPerson_id,
		p_isreg,
		p_begdate,
		p_enddate
	from(
		select 
			EvnPL_id as id,
			person_id,
			diag_code,
			DeseaseType_id,
			evnpl_disdate as evn_disdate,
			p_isreg,
			p_begdate,
			p_enddate
		from epl 
		union all select * from eps
		union all select * from dvn
		union all select * from povn 
		union all select * from dds
		union all select * from mon
	)all_doc
		inner join v_personstate as person on person.person_id = all_doc.person_id
		left join Address on Address.Address_id = coalesce(Person.PAddress_id, Person.UAddress_id)
		left join v_MorbusBase as MB on MB.Person_id = Person.Person_id
		left join v_MorbusOnkoBase as MOB on MB.MorbusBase_id = MOB.MorbusBase_id
		left join lateral(
			select PD.Person_id as DPerson_id 
			from v_PersonDisp as PD 
			inner join Diag as D on D.Diag_id = PD.Diag_id
				and D.Diag_Code like'C%'
			where PD.Person_id = Person.Person_id
			limit 1
		)PD on true

		left join lateral( --#165561 пациент в регистре
			select register.personregister_id
			from dbo.personregister register 
				left join diag as reg_diag on reg_diag.diag_id = register.diag_id
			where register.person_id = all_doc.person_id 
				and register.personregistertype_id = 3
				and register.PersonRegister_setDate <= p_enddate 
				and coalesce(PersonRegister_Deleted, 1) = 1
				and left(reg_diag.diag_code, 3) = left(all_doc.diag_code, 3) --совпадение до трёх знаков
			limit 1
		)register on true

where (p_isReg = 1 and register.PersonRegister_id is null) 
	or (p_isReg = 2 and register.PersonRegister_id is not null) 
	or p_isReg is null --#165561
)

select
	count(distinct 
		case 
			when Sex_id = 1 and DeseaseType_id in (1, 3) 
					and left(Diag_Code,1) = 'C'
					and KLAreaType_id = 2
				then Person_id
			end) as val5, 
	count(distinct
		case 
			when Sex_id = 2 and DeseaseType_id in (1, 3)
					and left(Diag_Code,1) = 'C'
					and KLAreaType_id = 2
				then Person_id
			end) as val24,
	count(distinct
		case 
			when TumorPrimaryMultipleType_id in (2,3,4) and left(Diag_Code,1) = 'C'
				then DPerson_id
			end) as val25,
	count(distinct
		case 
			when TumorPrimaryMultipleType_id in (2,3,4) and DeseaseType_id = 1
					and left(Diag_Code,1) = 'C'
				then DPerson_id
			end) as val26,
	count(distinct
		case 
			when DeseaseType_id in (1, 3) and left(Diag_Code,1) = 'D'
				then Person_id
			end) as val27,
	count(distinct
		case
			when DeseaseType_id in (1, 3) and Diag_Code like 'D05%'
				then Person_id
			end) as val28,
	count(distinct
		case 
			when DeseaseType_id in (1, 3) and Diag_Code like 'D06%'
				then Person_id
			end) as val29
from doc
	right join rpt.RepDiag on doc.Diag_Code between rtrim(repDiag_fromDiag) and rtrim(repDiag_ToDiag)
where RepDiag_SysNIck = 'frm7_2015' and RepDiag_Part = 2
