with params as(
	select
cast($1 as bigint) as p_lpu_id,
cast($2 as bigint) as p_lpubuilding_id,
cast($3 as bigint) as p_lpuunit_id, 
cast($4 as bigint) as p_lpusection_id, 
cast($5 as bigint) as p_medpersonal_id, 
cast(to_timestamp($6,'dd.mm.yyyy') as timestamp) as p_begdate, 
cast(to_timestamp($7,'dd.mm.yyyy') as timestamp) as p_enddate, 
cast($8 as bigint) as p_begdiag, 
cast($9 as bigint) as p_enddiag
)

SELECT DISTINCT 
--Блок для последующих тестов при наличии расхождений с данными интерфейса
/*
ps.Person_surname,ps.Person_firname,ps.Person_secname, ein.Lpu_id,
case when eps.EvnPS_numcard is not null then 'КВС' Else case when epl.EvnPL_numcard is not null then 'ТАП' end end as v2,
coalesce(eps.EvnPS_numcard,epl.EvnPL_numcard) as Numcard,ein.*
*/

cast(to_char(EIN.EvnInfectNotify_FirstSESDT,'dd.MM.yyyy hh:mm:ss') as timestamp) as FirstSESDT_v2,
coalesce(mp.Person_Fio,'') as mp_fio_v2,
coalesce(EIN.EvnInfectNotify_ReceiverMessage,'') as ReceiverMessage_v2,
coalesce(lpu.Lpu_Name,'') as Lpu_Name_v3,--просьба в графу 3 учитывать МО - из ТАП, в пределах которго создано извещение.
(coalesce(Person.Person_SurName,'')||' '||coalesce(Person.Person_FirName,'')||' '||coalesce(Person.Person_SecName,'')) as fio_v4,
to_char(Person.Person_BirthDay,'dd.MM.yyyy') as Birthdate_v5,
dbo.age(Person.Person_BirthDay,EIN.EvnInfectNotify_insDT) as  Age_v5,
coalesce(KLRgn.KLRgn_FullName,'')||', '||coalesce(KLSubRgn.KLSubRgn_Name,'')||', '||coalesce(KLCity.KLCity_FullName,KLTown.KLTown_FullName)||', '
||coalesce(KLStreet.KLStreet_FullName,'')||', '||coalesce(Address.Address_House,'')||', '||coalesce(Address.Address_Flat,'') as Person_address_v6,
coalesce(rtrim(OrgJob.Org_Name),'') as Org_Name_v7,
to_char(EvnInfectNotify_NextVizitDate,'dd.MM.yyyy') NextVizitDatev7, --Дата последующего посещения детского учреждения, школы
to_char(EIN.EvnInfectNotify_DiseaseDate,'dd.MM.yyyy') as DiseaseDate_v8, --дата заболевания
to_char(EIN.EvnInfectNotify_SetDiagDate,'dd.MM.yyyy') as SetDiagDate_v9,--дата устан диагноза
coalesce(DiagEPL.diag_FullName,DiagEPS.diag_FullName) as Diag_Name_v9,

case when eps.EvnPS_id is not null --проверяем наличие связанного с извещением КВС
	then  to_char(EPS.EvnPS_setDate,'dd.MM.yyyy')  
	Else case when epl.EvnPL_id is not null 
		then case when EvnPS_after.ESdiag_id=evpl.Diag_id --при совпадении диагноза из КВС, оформленного после извещения, с диагнозом визита ТАП, связанного  с извещением, берем дату из оформленного после извещения КВС
				then to_char(EvnPS_after.EvnPS_setDate,'dd.MM.yyyy')
				end 
		end 
	end as EvnPS_setDate_v10, 
case when eps.EvnPS_id is not null 
	then  lpuEps.Lpu_Name 
	Else case when epl.EvnPL_id is not null 
			then case when EvnPS_after.ESdiag_id=evpl.Diag_id 
					then EvnPS_after.Lpu_Name 
					end 
			end
	end as Lpu_Name_gos_v10,

to_char(EIN.EvnInfectNotify_FirstTreatDate,'dd.MM.yyyy') as FirstTreatDate_v11,

case when eps.EvnPS_id is not null --проверяем наличие связанного с извещением КВС
	then  coalesce(to_char(EPS.EvnPS_disDate,'dd.MM.yyyy'),to_char(EIN.EvnInfectNotify_SetDiagDate,'dd.MM.yyyy'))--при извещении из КВС берем данные из исходного КВС
	Else case when epl.EvnPL_id is not null 
			then coalesce(to_char(EvnPS_after.EvnPS_disDate,'dd.MM.yyyy'),to_char(EIN.EvnInfectNotify_SetDiagDate,'dd.MM.yyyy')) end end  as EvnPS_disDate_v12, --при извещении из ТАП берем данные из последующего КВС

coalesce(EvnPS_after.Diag_fullname,coalesce(DiagEPL.diag_FullName,DiagEPS.diag_FullName)) as diag_name_v12,-- при наличии последующего КВС берем заключение из него, при отсутствии- из исходящего КВС/ТАП

case when EIN.EvnInfectNotify_IsLabDiag=1 Then 'Нет' Else 'Да' end as IsLabDiag_v15
--ucc.UslugaComplex_Name as UslugaComplex_Name_v15,
--ucc.UslugaTest_setDT as UslugaTest_setDT_v15,
--ucc.UslugaTest_ResultValue as UslugaTest_ResultValue_v15

from v_EvnInfectNotify EIN 
left join params on 1=1
inner join v_Lpu as lputap  on ein.Lpu_id = lputap.Lpu_id
inner join v_PersonState as ps  on ps.Person_id = ein.Person_id
left join v_EvnPL as epl  on epl.EvnPL_id=ein.EvnInfectNotify_rid-----------------------ТАП до извещения
left join v_LpuSection lsEpl on epl.LpuSection_id=lsEpl.LpuSection_id
left join v_LpuUnit luEpl on lsEpl.lpuUnit_id=luEpl.LpuUnit_id
left join v_LpuBuilding lbEpl on lsEpl.lpubuilding_id=lbEpl.lpubuilding_id
left join v_EvnVizitPL EVPL  on epl.EvnPL_id=evpl.EvnVizitPL_pid------------------- Визит до извещения

left join v_EvnPS as eps  on eps.EvnPS_id = ein.EvnInfectNotify_rid-----------------------КВС до извещения
left join v_LpuSection lsEps on eps.LpuSection_id=lsEps.LpuSection_id
left join v_LpuUnit luEps on lsEps.lpuUnit_id=luEps.LpuUnit_id
left join v_LpuBuilding lbEps on lsEps.lpubuilding_id=lbEps.lpubuilding_id
left join v_Lpu lpuEps on eps.Lpu_id=lpuEps.Lpu_id
left join v_EvnSection as es  on eps.EvnPS_id=es.EvnSection_pid-----------------------Движение до извещения

left join v_Diag DiagEPL  on DiagEPL.Diag_id = EPL.Diag_id
left join v_Diag DiagEPS  on DiagEPS.Diag_id = EPS.Diag_id

inner join v_lpu lpu    on lpu.lpu_id=coalesce(epl.lpu_id,eps.lpu_id)
inner join v_PersonState  Person  on EIN.Person_id=Person.Person_id
left join Address  on coalesce(Person.UAddress_id,Person.PAddress_id)=Address.Address_id
left join  v_KLRgn  KLRgn   on Address.KLRgn_id=KLRgn.KLRgn_id
left join  v_KLSubRgn  KLSubRgn   on Address.KLSubRgn_id=KLSubRgn.KLSubRgn_id
left join  v_KLCity  KLCity   on Address.KLCity_id=KLCity.KLCity_id
left join  v_KLTown  KLTown   on Address.KLTown_id=KLTown.KLTown_id
left join  v_KLStreet KLStreet   on Address.KLStreet_id=KLStreet.KLStreet_id
left join v_KLArea KLArea   on KLArea.KLArea_id=KLstreet.KLArea_id
left join v_Job Job  on Job.Job_id=Person.Job_id
left join v_Org OrgJob  on OrgJob.Org_id=Job.Org_id
left join v_medpersonal mp  on MP.medpersonal_id=EIN.MedPersonal_id

left join lateral
	(select EvnPS_id,coalesce(lpu_a.Lpu_Name,'') as Lpu_Name, EvnPS.EvnPS_setDate as EvnPS_setDate, EvnPS.EvnPS_disDate as EvnPS_disDate, dg12.Diag_fullname, es.diag_id as ESdiag_id
	from v_EvnPS EvnPS 
	inner join v_EvnSection es  on EvnPS.EvnPS_id=es.EvnSection_pid
	inner join v_lpu lpu_a   on lpu_a.lpu_id=EvnPS.Lpu_id
	inner join v_Diag dg12  on EvnPS.diag_id=dg12.Diag_id
	where EvnPS.Person_id=EIN.Person_id 
	and cast(EvnPS.EvnPS_setDate as date) in (cast(EIN.EvnInfectNotify_FirstTreatDate as date), cast((EIN.EvnInfectNotify_FirstTreatDate+ INTERVAL '1 day') as date))
	limit 1)EvnPS_after on true                  -------------------------------------------------------------------------------------------------------------------------------------------------------------КВС после извещения

left join lateral (select uc.UslugaComplex_id,uc.UslugaComplex_Name,ut.UslugaTest_setDT,ut.UslugaTest_ResultValue,ucat.UslugaComplexAttributeType_sysnick
             from v_UslugaTest ut 
			 inner join v_UslugaComplex uc  on ut.UslugaComplex_id=uc.UslugaComplex_id
			 inner join v_UslugaComplexAttribute uca  on uc.UslugaComplex_id=uca.UslugaComplex_id
             inner join v_UslugaComplexAttributeType ucat  on uca.UslugaComplexAttributeType_id=ucat.UslugaComplexAttributeType_id
			-- left join v_UslugaTest UT  on uc.UslugaComplex_id=UT.UslugaComplex_id
			 where 
			 (cast(ut.UslugaTest_setDT as date) between cast(EIN.EvnInfectNotify_FirstTreatDate as date) and cast(EIN.EvnInfectNotify_SetDiagDate as date))
			 and ucat.UslugaComplexAttributeType_sysnick='lab'
             limit 1) UCC on true
-----------------------------------------------------------------------------------------------------------------------------------------------------15

WHERE 
		(ein.Lpu_id = p_lpu_id or p_lpu_id is null)
		and (p_begdiag is null and p_enddiag is null
					or (DiagEPL.Diag_Code between (select diag_code from v_diag  where diag_id=p_begdiag) and (select diag_code from v_diag  where diag_id=p_enddiag)
					and DiagEPS.Diag_Code between (select diag_code from v_diag  where diag_id=p_begdiag) and (select diag_code from v_diag  where diag_id=p_enddiag))
			)
		AND (mp.MedPersonal_id = p_medpersonal_id or p_medpersonal_id is null)
		and (cast(EIN.EvnInfectNotify_insDT as date) between p_begdate and p_enddate)
		and ((lsEpl.LpuSection_id=p_lpusection_id or p_lpusection_id is null) and (luEpl.lpuUnit_id=p_lpuunit_id or p_lpuunit_id is null) and (lbEpl.lpubuilding_id=p_lpubuilding_id or p_lpubuilding_id is null)
				or
				(lsEps.LpuSection_id=p_lpusection_id or p_lpusection_id is null) and (luEps.lpuUnit_id=p_lpuunit_id or p_lpuunit_id is null) and (lbEps.lpubuilding_id=p_lpubuilding_id or p_lpubuilding_id is null))
