--pan_Desease_List_Disp_pg.rptdesign
with /*params*/p as (
	select 
		cast($1 as bigint) as Lpu_id,
		cast($2 as varchar) as LpuFilialList,
		cast($3 as bigint) as LpuBuilding_id,
		cast($4 as bigint) as LpuUnit_id,
		cast($5 as bigint) as LpuSection_id,
		cast($6 as bigint) as LpuRegionType_id,
		cast($7 as bigint) as LpuRegion_id,
		cast($8 as bigint) as MedPersonal_id,
		cast($9 as bigint) as DispType_id,
		dbo.GetDiagCodeByID(cast($10 as bigint)) as BegDiag_Code,
		dbo.GetDiagCodeByID(cast($11 as bigint)) as EndDiag_Code, 
		cast($12 as bigint) as BegPersonAge,
		cast($13 as bigint) as EndPersonAge,
		cast($14 as bigint) as Sex_id,
		cast($15 as bigint) as SocStatus_id,
		cast($16 as bigint) as Org_id,
		cast($17 as bigint) as DopDispDiagType_id,
		CAST(TO_TIMESTAMP($18,'DD.MM.YYYY') AS TIMESTAMP) as BegDate,
		CAST(TO_TIMESTAMP($19,'DD.MM.YYYY') AS TIMESTAMP) as EndDate
),

LpuP as (
	select L.Lpu_id
	from rpt.GetLpuList_pg (
		(select p.Lpu_id from p limit 1),
		(select p.begdate from p limit 1), 
		(select p.endDate from p limit 1), 
		1, 2) L
	where cast(L.Lpu_begDate as date) <= (select p.endDate from p limit 1)
		and cast(coalesce(L.Lpu_endDate::timestamp, (L.LpuPr_begDate - interval '1 day')::timestamp, (select p.endDate from p limit 1)) as date) 
			>= cast((select p.endDate from p limit 1) as date) or L.Lpu_endDate is null
),

w_DispCard as (
	select 
		DispOrp.Lpu_id,
		DispOrp.EvnPLDispOrp_id as DispCard_id,
		DispOrp.Person_id,
		DispOrp.EvnPLDispOrp_setDate as DispCard_setDate,
		DispOrp.EvnPLDispOrp_disDate as DispCard_disDate,
		DispOrp.DispClass_id,
		DispOrp.EvnPLDispOrp_consDT as EvnPLDisp_consDT,
		DispOrp.EvnPLDispOrp_IsFinish as EvnPLBase_IsFinish,
		DispOrp.evnclass_id
	from v_EvnPLDispOrp DispOrp
	union all 
	select 
		DispDop.Lpu_id,
		DispDop.EvnPLDispDop13_id,
		DispDop.Person_id,
		DispDop.EvnPLDispDop13_setDate,
		DispDop.EvnPLDispDop13_disDT,
		DispDop.DispClass_id,
		DispDop.EvnPLDispDop13_consDT,
		DispDop.EvnPLDispDop13_IsFinish as EvnPLBase_IsFinish,
		DispDop.evnclass_id
	from v_EvnPLDispDop13 DispDop 
	union all 
	select
		DispProf.Lpu_id,
		DispProf.EvnPLDispProf_id,
		DispProf.Person_id,
		DispProf.EvnPLDispProf_setDate,
		DispProf.EvnPLDispProf_disDT,
		DispProf.DispClass_id,
		DispProf.EvnPLDispProf_consDT,
		DispProf.EvnPLDispProf_IsFinish as EvnPLBase_IsFinish,
		DispProf.evnclass_id
	from v_EvnPLDispProf DispProf
	union all 
	select
		DispTeen.Lpu_id,
		DispTeen.EvnPLDispTeenInspection_id,
		DispTeen.Person_id,
		DispTeen.EvnPLDispTeenInspection_setDate,
		DispTeen.EvnPLDispTeenInspection_disDT,
		DispTeen.DispClass_id,
		DispTeen.EvnPLDispTeenInspection_consDT,
		DispTeen.EvnPLDispTeenInspection_IsFinish as EvnPLBase_IsFinish,
		DispTeen.evnclass_id
	from v_EvnPLDispTeenInspection DispTeen
),

Disp as (
	select 
		VizitDisp.VizitDisp_id,
		Diag.Diag_Code,
		Diag.Diag_FullName,
		DiagType.DopDispDiagType_Name,
		PC.PersonCard_Code,
		Person.Person_FIO,
		Person.Person_Birthday,
		Addr.Address_Address,
		PC.LpuPC_Nick,
		PC.LpuRegion_Name,
		Org.Org_Name,
		case when DispCard.Evnclass_id = 9 then ODS.OrpDispSpec_Name else ST.SurveyType_Name end as Survey_Name,
		MP.MedPersonal_FIO,
		DispClass.DispClass_Name,
		DispCard.EvnPLDisp_consDT,
		DispCard.DispCard_disDate,
		PersonDisp.PersonDisp_begDate,
		PersonDisp.PersonDisp_endDate
		
	from LpuP L
	inner join p on 1 = 1
	inner join lateral (
		select 
			DC.Lpu_id,
			DC.EvnClass_id,
			DC.DispCard_id,
			DC.Person_id,
			DC.DispCard_setDate,
			DC.DispCard_disDate,
			DC.DispClass_id,
			DC.EvnPLDisp_consDT
		from w_DispCard DC
		where DC.DispCard_disDate between p.BegDate and p.EndDate
			and DC.Lpu_id = L.Lpu_id
			and DC.EvnPLBase_IsFinish = 2 
			and (p.DispType_id = case when DC.EvnClass_id = 101 then 1 
								  when DC.EvnClass_id = 9   then 2 
								  when DC.EvnClass_id = 103 then 3
								  when DC.EvnClass_id = 104 then 4 
				end or p.DispType_id is null)

	) DispCard on true
	
	inner join lateral (
		select 
			VizitDispDop.EvnVizitDispDop_id as VizitDisp_id,
			VizitDispDop.evnvizitdispdop_setdate as VizitDisp_Date,
			VizitDispDop.MedPersonal_id,
			VizitDispDop.LpuSection_id,
			VizitDispDop.Diag_id,
			VizitDispDop.DopDispDiagType_id,
			VizitDispDop.DopdispInfoConsent_id,
			null as OrpDispSpec_id
		from v_EvnVizitDispDop VizitDispDop 
		where VizitDispDop.EvnVizitDispDop_pid = DispCard.DispCard_id
		union all 
		select 
			VizitDispOrp.EvnVizitDispOrp_id as VizitDisp_id,
			VizitDispOrp.evnVizitDispOrp_setdate as VizitDisp_Date,
			VizitDispOrp.MedPersonal_id,
			VizitDispOrp.LpuSection_id,
			VizitDispOrp.Diag_id,
			VizitDispOrp.DopDispDiagType_id,
			VizitDispOrp.DopdispInfoConsent_id,
			VizitDispOrp.OrpDispSpec_id
		from v_EvnVizitDispOrp VizitDispOrp
		where VizitDispOrp.EvnVizitDispOrp_pid = DispCard.DispCard_id
	) VizitDisp on true 
	
	--маршрутная карта
	inner join v_DopdispInfoConsent DDIC on DDIC.DopdispInfoConsent_id = VizitDisp.DopdispInfoConsent_id
	inner join v_SurveyTypeLink STL on STL.SurveyTypeLink_id = DDIC.SurveyTypeLink_id
	inner join v_SurveyType ST on ST.SurveyType_id = STL.SurveyType_id
		and ST.SurveyType_Name ~* 'осмотр'
		and ST.SurveyType_Name ~* 'врач'
	left join v_OrpDispSpec ODS on ODS.OrpDispSpec_id = VizitDisp.OrpDispSpec_id --для ДДС
		and DispCard.EvnClass_id = 9
		
	inner join v_Diag Diag on Diag.Diag_id = VizitDisp.Diag_id
		and (Diag.Diag_Code >= p.BegDiag_Code or p.BegDiag_Code is null)
		and (Diag.Diag_Code <= p.EndDiag_Code or p.EndDiag_Code is null)
		and left(Diag.Diag_Code,1) <> 'Z'
	inner join lateral (
		select
			MPers.MedPersonal_id,
			MPers.Person_Fio as MedPersonal_FIO
		from v_MedPersonal MPers 
		where MPers.MedPersonal_id = VizitDisp.MedPersonal_id
		limit 1
	) MP on true
	inner join v_DispClass DispClass on DispClass.DispClass_id = DispCard.DispClass_id

	inner join lateral (
		select 
			PS.Person_id,
			coalesce(PS.Person_Surname || ' ', '') || coalesce(PS.Person_Firname || ' ', '') || coalesce(PS.Person_SecName, '') as Person_FIO,
			PS.Person_Firname,
			PS.Person_Secname,
			PS.Job_id,
			PS.Person_Birthday,
			coalesce(PS.PAddress_id, PS.UAddress_id) as Address_id
		from v_PersonState PS 
		where PS.Person_id = DispCard.Person_id
			and dbo.AgeYMD(PS.Person_Birthday, VizitDisp.VizitDisp_Date, 1) >= coalesce(p.BegPersonAge, 0)
			and dbo.AgeYMD(PS.Person_Birthday, VizitDisp.VizitDisp_Date, 1) <= coalesce(p.EndPersonAge, 200)
			and (PS.Sex_id = p.Sex_id or p.Sex_id is null)
			and (PS.SocStatus_id = p.SocStatus_id or p.SocStatus_id is null)
	) Person on true 

	left join v_Address Addr on Addr.Address_id = Person.Address_id
	left join v_Job Job on Job.Job_id = Person.Job_id
	left join v_Org Org on Org.Org_id = Job.Org_id

	left join v_DopDispDiagType DiagType on DiagType.DopDispDiagType_id = VizitDisp.DopDispDiagType_id

	left join LpuSection LS on LS.LpuSection_id = VizitDisp.LpuSection_id 
	left join LpuUnit LU on LU.LpuUnit_id = LS.LpuUnit_id 
	left join LpuBuilding LB on LB.LpuBuilding_id = LU.LpuBuilding_id

	left join lateral (
		select
			PC.PersonCard_id,
			PC.PersonCard_Code,
			PC.LpuRegionType_id,
			PC.LpuRegion_id,
			PC.LpuRegion_Name,
			LpuPC.Lpu_Nick as LpuPC_Nick
		from v_PersonCard_all PC 
		inner join v_Lpu LpuPC on LpuPC.Lpu_id = PC.Lpu_id
		where PC.Person_id = DispCard.Person_id 
			and PC.LpuAttachType_id = 1
			and PC.PersonCard_begDate <= VizitDisp.VizitDisp_Date
			and (PC.PersonCard_endDate >= VizitDisp.VizitDisp_Date or PC.PersonCard_endDate is null)
		order by 
			PC.PersonCard_begDate desc
		limit 1
	) PC on true

	left join lateral ( --дисп. наблюдение
		select
			PD.PersonDisp_id,
			PD.PersonDisp_begDate,
			PD.PersonDisp_endDate
		from v_PersonDisp PD 
		where PD.Person_id = DispCard.Person_id 
			and PD.PersonDisp_begDate >= DispCard_setDate 
			and PD.PersonDisp_begDate <= p.EndDate
			and PD.Diag_id = VizitDisp.Diag_id
			and (PD.PersonDisp_endDate between p.BegDate and p.EndDate or PD.PersonDisp_endDate is null)
		order by 
			pd.PersonDisp_begDate desc
	) PersonDisp on true

	where 
		(',' || p.LpuFilialList || ',' like '%,' || cast(LB.LpuFilial_id as varchar(30)) || ',%' or p.LpuFilialList is null)
		and (LB.LpuBuilding_id = p.LpuBuilding_id or p.LpuBuilding_id is null)
		and (LU.LpuUnit_id = p.LpuUnit_id or p.LpuUnit_id is null)
		and (LS.LpuSection_id = p.LpuSection_id or p.LpuSection_id is null)
		and (PC.LpuRegionType_id = p.LpuRegionType_id or p.LpuRegionType_id is null)
		and (PC.LpuRegion_id = p.LpuRegion_id or p.LpuRegion_id is null)
		and (MP.MedPersonal_id = p.MedPersonal_id or p.MedPersonal_id is null)
		and (Org.Org_id = p.Org_id or p.Org_id is null)
		and (DiagType.DopDispDiagType_id = p.DopDispDiagType_id or p.DopDispDiagType_id is null)
)

select 
	ROW_NUMBER() over(order by D.Diag_Code, D.Person_FIO) as v1_row,
	D.Diag_FullName as v2_diag,
	D.DopDispDiagType_Name as v3_diagtype,
	D.PersonCard_Code as v4_amb_card,
	D.Person_FIO as v5_person_fio,
	to_char(D.Person_Birthday, 'dd.mm.yyyy') as v6_birthday,
	D.Address_Address as v7_address,
	D.LpuPC_Nick as v8_lpupc,
	D.LpuRegion_Name as v9_lpuregion,
	D.Org_Name as v10_job,
	D.Survey_Name as v11_survey,
	D.MedPersonal_FIO as v12_med_fio,
	D.DispClass_Name as v13_vid_surv,
	to_char(D.EvnPLDisp_consDT, 'dd.mm.yyyy') as v14_card_consdate,
	to_char(D.DispCard_disDate, 'dd.mm.yyyy') as v15_card_disdate,
	to_char(D.PersonDisp_begDate, 'dd.mm.yyyy') as v16_obs_begdate,
	to_char(D.PersonDisp_endDate, 'dd.mm.yyyy') as v17_obs_enddate

from (
	select distinct Disp.* 
	from Disp
) D
