with params as(
	select
		cast($1 as bigint) as p_Lpu_id,
		cast($2 as bigint) as p_onlypersoncard_yn,
		cast($3 as varchar) as p_lpufilialslist,
		cast($4 as bigint) as p_lpubuilding_id,
		cast($5 as bigint) as p_lpuunit_id,
		cast($6 as bigint) as p_lpusection_id,
		cast($7 as bigint) as p_lpuregiontype_id,
		cast($8 as bigint) as p_lpuregion_id,
		cast($9 as bigint) as p_medpersonal_id,
		dbo.GetDiagCodeByID(cast($10 as bigint)) as p_begdiag_code,
		dbo.GetDiagCodeByID(cast($11 as bigint)) as p_enddiag_code,
		cast($12 as bigint) as p_lpu_did,
		cast($13 as bigint) as p_begage,
		cast($14 as bigint) as p_endage,
		cast($15 as bigint) as p_sex_id,
		cast($16 as bigint) as p_socstatus_id,
		cast($17 as bigint) as p_sticktype_id,
		cast($18 as bigint) as p_vizittype_id,
		cast($19 as bigint) as p_org_id,
		cast($20 as bigint) as p_deseasetype_id,
		cast($21 as bigint) as p_isfinish,
		cast($22 as timestamp) as p_begdate,
		cast($23 as timestamp) as p_enddate,
		cast($24 as bigint) as p_nationality,
		cast($25 as bigint) as p_isguzonly
),

LpuP as 
 (
  select 
    L.Lpu_id,
    coalesce(LpuPr.Lpu_id, L.Lpu_id) as Lpu_pid,
	dbo.GetRegion() as Region_id
  from dbo.v_Lpu L 
	left join params on 1=1
  inner join dbo.Org O  on L.Org_id = O.Org_id
    and (Lpu_id = p_Lpu_id 
	  or (p_Lpu_id is null or p_OnlyPersonCard_YN = 2) and coalesce(L.Lpu_IsTest, 1) = 1
	  or Lpu_pid = p_Lpu_id and p_Lpu_id > 0)
    and (case when dbo.GetRegion() = 59 and L.Lpu_id in (13002556,10011362) or O.Okfs_id in (2,4,5) -- 11. Государственная собственность, 13. Собственность субъектов Российской Федерации, 14. Муниципальная собственность
           then 2 else 1 end >= p_IsGUZOnly or p_OnlyPersonCard_YN = 2)
    and L.Lpu_begDate <= p_EndDate::date + interval '1 year'
    and (L.Lpu_endDate >= p_BegDate::date or L.Lpu_endDate is null) 	
  left join lateral
  (
    select 
      LpuPr.Lpu_id
    from dbo.Lpu LpuPr 
    where
      LpuPr.Lpu_id = L.Lpu_pid
      and LpuPr.Lpu_begDate < p_EndDate::date + interval '1 day' limit 1
  ) LpuPr on true            
),

LpuPC as 
(
   select
    L.Lpu_Nick,
	L.Lpu_id
  from dbo.v_Lpu L 
	left join params on 1=1
  where
	(L.Lpu_id = p_Lpu_id 
	  or p_Lpu_id is null and coalesce(L.Lpu_IsTest, 1) = 1
      or L.Lpu_pid = p_Lpu_id and p_Lpu_id > 0
	  or p_OnlyPersonCard_YN = 1)
    and L.Lpu_begDate <= p_EndDate::date + interval '1 year'
	and coalesce(L.Lpu_endDate, p_BegDate) >= p_BegDate
), 

LpuSection_W as 
(
  select 
    
    LS.Lpu_id,
    LS.LpuSection_id,
    LS.LpuSection_Name,
    LS.LpuSection_Code,
	LB.LpuBuilding_id,
	LS.LpuUnit_id,
	LB.LpuFilial_id
  from LpuP as L 
	left join params on 1=1
  inner join dbo.v_LpuSection LS  on LS.Lpu_id = L.Lpu_id
    and (LS.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null or p_OnlyPersonCard_YN = 2)    
    and (LS.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null or p_OnlyPersonCard_YN = 2)
    and (LS.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null or p_OnlyPersonCard_YN = 2)
  inner join v_LpuBuilding LB on LB.LpuBuilding_id = LS.LpuBuilding_id
     and (',' || coalesce(p_LpuFilialsList, '') || ',' like '%,' || coalesce(cast(lb.LpuFilial_id as varchar), '0') || ',%' 
      or coalesce(rtrim(p_LpuFilialsList), '') = '' or p_OnlyPersonCard_YN = 2)
),

LpuRegion_W as 
(
  select 
    L.Lpu_id,
    LR.LpuRegion_id,
	LR.LpuRegion_Name,
	LB.LpuBuilding_id,
	LS.LpuSection_id,
	LS.LpuUnit_id,
	LB.LpuFilial_id, 
	LR.LpuRegionType_id
  from dbo.v_Lpu L 
	left join params on 1=1
  inner join dbo.Org O  on L.Org_id = O.Org_id
    and p_OnlyPersonCard_YN = 2
    and (Lpu_id = p_Lpu_id 
	  or p_Lpu_id is null and coalesce(L.Lpu_IsTest, 1) = 1
	  or Lpu_pid = p_Lpu_id and p_Lpu_id > 0)
    and (case when dbo.GetRegion() = 59 and L.Lpu_id in (13002556,10011362) or O.Okfs_id in (2,4,5) -- 11. Государственная собственность, 13. Собственность субъектов Российской Федерации, 14. Муниципальная собственность
           then 2 else 1 end >= p_IsGUZOnly)
    and L.Lpu_begDate <= p_EndDate::date + interval '1 year'
    and (L.Lpu_endDate >= p_BegDate::date or L.Lpu_endDate is null) 
  inner join dbo.v_LpuRegion LR  on LR.Lpu_id = L.Lpu_id
    and (LR.LpuRegionType_id = p_LpuRegionType_id or p_LpuRegionType_id is null)
    and (LR.LpuRegion_id = p_LpuRegion_id or p_LpuRegion_id is null)
  left join dbo.v_LpuSection LS  on LS.LpuSection_id = LR.LpuSection_id
  left join dbo.v_LpuBuilding LB  on LB.LpuBuilding_id = LS.LpuBuilding_id
  where 
    (LS.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
    and (LS.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
    and (LS.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
     and (',' || coalesce(p_LpuFilialsList, '') || ',' like '%,' || coalesce(cast(lb.LpuFilial_id as varchar), '0') || ',%' 
      or coalesce(rtrim(p_LpuFilialsList), '') = '')
),

tab_diags as (
select 
	EPL.Person_id, 	
	EPL.Evn_id as EvnVizitPL_id,
	PS.Lpu_id OnlyLpu_id,
	PS.LpuRegionType_id OnlyLpuRegionType_id,
	PS.LpuRegion_id as OnlyPCLpuRegion_id,
	PS.LpuFilial_id as OnlyPCLpuFilial_id,
	PS.LpuSection_id as OnlyPCLpuSection_id,
	PS.LpuUnit_id as OnlyPCLpuUnit_id,
	PS.LpuBuilding_id as OnlyPCLpuBuilding_id,	
	LS.Lpu_id as SluchLpu_id,
	LS.LpuFilial_id as SluchLpuFilial_id,
	LS.LpuBuilding_id as SluchLpuBuilding_id,
	LS.LpuUnit_id as SluchLpuUnit_id,
	LS.LpuSection_id as SluchLpuSection_id,
	diags.typeD_id,	
	EvnPL_NumCard,
	coalesce(dosn.OsnDiagFull, evpldiag.Diag_Code||' '|| evpldiag.Diag_Name) OsnDiagFull,
	coalesce(dtosn.OsnDeseaseTypefull,	cast(dtevpl.DeseaseType_Code as varchar(10)) || '.' || dtevpl.DeseaseType_Name) OsnDeseaseTypefull,	
	PS.LpuRegion_Name, --Участок 
	PS.Lpu_Nick as Lpu_Nick_Prik, --Mо прикрепления
	PS.PersonCard_Code, --код амб. карты
	PS.Person_FIO, -- фио
	to_char(PS.Person_Birthday,'dd.mm.yyyy')  as Person_Birthday,
	Addr.Address_Address as PersonAddress,
	WorkOrg.Org_Name,
	concat_ws(' ',MSF.Person_SurName,MSF.Person_FirName,MSF.Person_SecName) as MedPerson_Fio,  
	to_char(EPL.Evn_setDT, 'dd.mm.yyyy') as EvnPL_setDate,
	to_char(EPL.Evn_disDT, 'dd.mm.yyyy') as EvnPL_disDate,
	diags.Diag_Code, --для фильтрации
	diags.diag_sop,
	diags.DeseaseType_sop,
	PDisp.PDisp_Diag,
	PDisp.dateDisp,
	PDisp.PersonDisp_begDate,
	PDisp.PersonDisp_endDate,
	string_agg(case when diags.typeD_id =2 then diags.diag_sop else '' end,';'|| chr(10)) as SopDiag_Name,
	string_agg(case when diags.typeD_id =2 then diags.DeseaseType_sop else '' end ,';'|| chr(10)) as SopDiag_DeseaseType_Name,
	string_agg(PDisp.PDisp_Diag , ';'|| chr(10)) as PDisp_Diag_sd,
	string_agg(PDisp.dateDisp, ';'|| chr(10)) as dateDisp_sd

from EvnPL EPL
left join params on 1=1
inner join EvnVizitPL as EVPL on EVPL.Evn_pid = EPL.Evn_id
	and EVPL.Evn_setDT between (select p_begDate from params) and (select p_EndDate from params) + interval '1 day' - interval '1 minute'
	and EVPL.evn_deleted = 1
inner join EvnPLBase eplb on eplb.Evn_id = EPL.Evn_id
left join LpuSection_W as LS  on EPL.LpuSection_id = LS.LpuSection_id
left join diag evpldiag  on evpldiag.Diag_id=EVPL.Diag_id
left join deseasetype dtevpl  on dtevpl.DeseaseType_id=epl.DeseaseType_id

left join lateral (
			select d.Diag_Code||' '|| d.Diag_Name as OsnDiagFull -- основной диагноз из случая
			from diag d 
			where epl.Diag_id=d.Diag_id
		)dosn on true
left join lateral (
			select cast(DT.DeseaseType_Code as varchar(10)) || '.' || DT.DeseaseType_Name as OsnDeseaseTypefull
			from v_DeseaseType dt 
			where dt.DeseaseType_id=epl.DeseaseType_id
		)DtOsn on true
	
left join lateral (
		
	select Diag_id, DeseaseType_id, typeD_id, dsop.Diag_Code,  
			dsop.diag_sop, sopDeseaseType.DeseaseType_sop, diagsall.Person_id
	from
	(
--		select EPl.Person_id, EPl.Diag_id, EPL.DeseaseType_id, 1 typeD_id

--		union all

		select EvnDiagPLSop.Person_id, EvnDiagPLSop.Diag_id, EvnDiagPLSop.DeseaseType_id, 2 typeD_id
		from EvnDiagPLSop EvnDiagPLSop where EvnDiagPLSop.Evn_rid = EPL.Evn_id
	) diagsall
	
	inner join lateral (
			select Diag_Code, Diag_Code||' '||Diag_Name diag_sop
			from diag 
			where diagsall.Diag_id=diag.Diag_id and diagsall.Diag_id is not null
		)dsop on true
	left join lateral (
			select cast(DT.DeseaseType_Code as varchar(10)) || '.' || DT.DeseaseType_Name DeseaseType_sop
			from DeseaseType dt 
			where dt.DeseaseType_id=diagsall.DeseaseType_id
		)sopDeseaseType on true
	where Diag_id is not null
) diags on true

left join dbo.v_MedStaffFact MSF  on EVPL.MedStaffFact_id = MSF.MedStaffFact_id
left join lateral	(
		select distinct

		coalesce(DiagPDisp.Diag_Code, '')||' '||coalesce(DiagPDisp.Diag_Name, '') as PDisp_Diag,
		'Дата постановки на дисп. наблюдение: '|| to_char(PD.PersonDisp_begDate, 'dd.mm.yyyy')||'; '|| 
		'Дата снятия с дисп. наблюдения: '|| coalesce(to_char(PD.PersonDisp_endDate, 'dd.mm.yyyy'), ' ') dateDisp,
		PersonDisp_begDate,
		PersonDisp_endDate
		from dbo.PersonDisp pd
		inner join dbo.Diag DiagPDisp  on DiagPDisp.Diag_id = PD.Diag_id
		and PD.Person_id = diags.Person_id
		and DiagPDisp.Diag_id = Diags.Diag_id	
		and PersonDisp_begDate <= p_EndDate
		and (PersonDisp_endDate >=  p_EndDate or PersonDisp_endDate is null)
		and PD.Lpu_id = epl.Lpu_id
	) PDisp on true
	
left join lateral
	(
		select 
		ES.Evn_id as EvnStick_id,
		ES.StickType_id
		from dbo.EvnStick ES 
		where
		ES.Evn_rid = EPL.Evn_id
		and ES.evn_deleted = 1
		and ES.StickType_id = p_StickType_id
		limit 1
	) EStick on true
inner join LpuP on LpuP.Lpu_id = EPL.Lpu_id

--PersonState_W
	inner join lateral
	(
 select 
    PS.Person_id,
    cast(cast(PS.Person_BirthDay as date) as timestamp) as Person_BirthDay,
    PS.Sex_id,	
	concat_ws(' ',PS.Person_SurName,PS.Person_FirName,PS.Person_SecName)  as Person_FIO, 
	PS.Job_id,
	coalesce(PS.PAddress_id, PS.UAddress_id) as Address_id,
  cast(coalesce(PS.Person_deadDT, PS.Person_closeDT) as date) as Person_DeadDate,
	PC.PersonCard_Code,
	LpuPC.Lpu_Nick as Lpu_Nick,
	PC.Lpu_id,
	LReg.LpuRegion_Name,
	PC.LpuRegion_id,
	LR.LpuFilial_id,
	LR.LpuSection_id,
	LR.LpuUnit_id,
	LR.LpuBuilding_id,
	PC.LpuRegionType_id
from v_PersonState PS
left join params on 1=1
left join dbo.v_NationalityStatus NS  on NS.NationalityStatus_id = PS.NationalityStatus_id
	left join lateral
	(
		select * from PersonCard PC
		where PC.PersonCard_begDate < p_EndDate::date + interval '1 day'
		  and (coalesce(PC.PersonCard_endDate, p_EndDate::date + interval '1 day') > p_EndDate or
		      (PC.PersonCard_begDate < p_EndDate and PC.PersonCard_endDate is null))
			and PC.Person_id=ps.Person_id 
			and PC.LpuAttachType_id = 1			
			order by PC.personcard_begDate desc
			limit 1
	)PC on true
  inner join  LpuPC on LpuPC.Lpu_id = PC.Lpu_id
	left join lateral
	(
		select * from LpuRegion_W as LR  where LR.LpuRegion_id=PC.LpuRegion_id
		limit 1
	)LR on true
  left join v_LpuRegion as LReg  on LReg.LpuRegion_id=PC.LpuRegion_id
  where
		(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)
	  and (p_Nationality = 1 and NS.KLCountry_id = 643
        or p_Nationality = 2 and NS.KLCountry_id = 112 
	    or p_Nationality = 3 and NS.KLCountry_id <> 643
	    or p_Nationality is null)
    and (PC.LpuRegion_id >= -1 or p_OnlyPersonCard_YN = 1)
		and PS.Person_id=EPL.Person_id
		limit 1
	)PS on true

left join dbo.Address Addr  on Addr.Address_id = PS.Address_id
left join dbo.v_Job Job  on Job.Job_id = PS.Job_id
left join dbo.Org WorkOrg on WorkOrg.Org_id = Job.Org_id
where EPL.Evn_disDT between (select p_BegDate from params) and (select p_EndDate from params) + interval '1 day' - interval '1 minute'
and EPL.evn_deleted = 1
and (EPL.Lpu_id = (select p_Lpu_id from params) or (select p_Lpu_id from params) is null)
    and (LS.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null or p_OnlyPersonCard_YN = 2)    
    and (LS.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null or p_OnlyPersonCard_YN = 2)
    and (LS.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null or p_OnlyPersonCard_YN = 2)
		and (eplb.EvnPLBase_IsFinish = p_isfinish or p_isfinish is null)
		and (epl.DeseaseType_id = p_deseasetype_id or p_deseasetype_id is null)
		and (evpl.VizitType_id = p_VizitType_id or p_VizitType_id is null)
		and (WorkOrg.Org_id = p_Org_id or p_Org_id is null)
		and (epl.Lpu_did = p_Lpu_did or p_Lpu_did is null)
		and (evpl.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
group by
	EPL.Person_id, 	
	EPL.Evn_id,
	PS.Lpu_id,
	PS.LpuRegionType_id,
	PS.LpuRegion_id,
	PS.LpuFilial_id,
	PS.LpuSection_id,
	PS.LpuUnit_id,
	PS.LpuBuilding_id,
	LS.Lpu_id,
	LS.LpuFilial_id,
	LS.LpuBuilding_id,
	LS.LpuUnit_id,
	LS.LpuSection_id,
	diags.typeD_id,	
	EvnPL_NumCard,
	coalesce(dosn.OsnDiagFull, evpldiag.Diag_Code||' '|| evpldiag.Diag_Name),
	coalesce(dtosn.OsnDeseaseTypefull,	cast(dtevpl.DeseaseType_Code as varchar(10)) || '.' || dtevpl.DeseaseType_Name),
	PS.LpuRegion_Name, --Участок 
	PS.Lpu_Nick, --Mо прикрепления
	PS.PersonCard_Code, --код амб. карты
	PS.Person_FIO, -- фио
	to_char(PS.Person_Birthday,'dd.mm.yyyy'),
	Addr.Address_Address,
	WorkOrg.Org_Name,
	concat_ws(' ',MSF.Person_SurName,MSF.Person_FirName,MSF.Person_SecName),
	to_char(EPL.Evn_setDT, 'dd.mm.yyyy'),
	to_char(EPL.Evn_disDT, 'dd.mm.yyyy'),
	diags.Diag_Code, --для фильтрации
	diags.diag_sop,
	diags.DeseaseType_sop,
	PDisp.PDisp_Diag,
	PDisp.dateDisp,
	PDisp.PersonDisp_begDate,
	PDisp.PersonDisp_endDate
)

select distinct	
	td.EvnPL_NumCard::varchar as "EvnPL_NumCard",
	td.Person_id as "Person_id",
	td.EvnVizitPL_id as "EvnVizitPL_id",
	td.OsnDiagFull::varchar as "OsnDiagFull",
	td.OsnDeseaseTypefull::varchar as "OsnDeseaseTypefull",
	td.SopDiag_Name::varchar as "SopDiag_Name",
	td.SopDiag_DeseaseType_Name::varchar as "SopDiag_DeseaseType_Name",
	td.Lpu_Nick_Prik::varchar as "Lpu_Nick_Prik", --Mо прикрепления,
	td.LpuRegion_Name::varchar as "LpuRegion_Name", --Участок 	
	td.PersonCard_Code::varchar as "PersonCard_Code", --код амб. карты
	td.Person_FIO::varchar as "Person_FIO", -- фио
	td.Person_Birthday::varchar as "Person_Birthday",-- дата рождения
	td.PersonAddress::varchar as "PersonAddress",
	td.Org_Name::varchar as "Org_Name",
	td.MedPerson_Fio::varchar as "MedPerson_Fio",
	td.EvnPL_setDate::varchar as "EvnPL_setDate",
	td.EvnPL_disDate::varchar as "EvnPL_disDate",
	td.PDisp_Diag_sd::varchar as "PDisp_Diag",
	replace(td.dateDisp_sd, ' ;', '')::varchar as "dateDisp"
from tab_diags td
left join params on 1=1

where (Diag_Code between p_BegDiag_Code and p_EndDiag_Code or (p_BegDiag_Code is null or p_EndDiag_Code is null))
