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($6 as bigint) as p_medpersonal_sid,
		cast($7 as bigint) as p_paytype_id,
		cast($8 as bigint) as p_usluga_id,
		cast($9 as bigint) as p_lpudir_id,
		cast($10 as timestamp) as p_begdate,
		cast($11 as timestamp) as p_enddate,
		cast($12 as bigint) as p_categstrah,
		cast($13 as bigint) as p_polis_id
)

select distinct
	eup1.EvnUslugaPar_id as "EvnUslugaPar_id",
	case when up.UslugaPlace_SysNick='otherlpu' then Lpu_uid.Lpu_Nick
	     when up.UslugaPlace_SysNick='lpusection' then Lpu.Lpu_Nick
		 when up.UslugaPlace_SysNick='otherorg' then Org_uid.Org_Nick end as "Lpu",
	eup1.LpuSection_Name as "LpuSection_Name",
	NULLIF (coalesce(PS.Person_SurName, '') || coalesce(' ' || PS.Person_FirName, '') || coalesce(' ' || PS.Person_SecName, ''), '') as "Person_FIO",
	rtrim(case when polis.PolisType_id = 4
        then PS.Person_EdNum 
        else coalesce(polis.Polis_Num,'') end) as "Polis_Num",
    coalesce(Document.Document_Ser, '    ') || ' ' || coalesce(Document.Document_Num, '    ') || ' ' || coalesce(to_char(Document.Document_begDate, 'DD.MM.YYYY'),'') as "Document",
	rtrim(OrgSMO.OrgSMO_Name) as "OrgSMO_Name",
	to_char(PS.Person_BirthDay,'DD.MM.YYYY') as "Person_BirthDay",
	coalesce(Addr1.Address_Address,Addr2.Address_Address) as "Address_Address",
	PS.Person_Snils as "Person_Snils",
	PS.PersonPhone_Phone as "PersonPhone_Phone",
	eup1.UslugaComplex_Code as "Usluga_Code",
	eup1.UslugaComplex_Name as "Usluga_Name",
	case when Uslugatest_ResultApproved=2 then ut1.UslugaComplex_Code end as "Usluga_Code_T",
	case when Uslugatest_ResultApproved=2 then ut1.UslugaComplex_Name end as "Usluga_Name_T",
	to_char(coalesce(ut1.UslugaTest_setDT,eup1.EvnUslugaPar_setDT),'DD.MM.YYYY') as "EvnUsluga_setDate",
	to_char(coalesce(ut1.UslugaTest_setDT,eup1.EvnUslugaPar_setDT),'HH24:MI') as "EvnUsluga_setTime",
	case when Uslugatest_ResultApproved=2 then coalesce(ut1.UslugaTest_ResultValue,'') end as "Rez",
	case when Uslugatest_ResultApproved=2 then coalesce(ut1.UslugaTest_ResultUnit,'') end as "Unit",
	coalesce(lpuDir1.Lpu_Nick,lpuDir2.Lpu_Nick,Org.Org_Nick) as "Lpu_Nick",
	coalesce(ED.EvnDirection_Num,eup1.EvnDirection_Num) as "EvnDirection_Num",
	to_char(coalesce(ED.EvnDirection_setDate,eup1.evndirection_setdt),'DD.MM.YYYY') as "EvnDirection_setDate",
	case when coalesce(NewslatterAccept_IsSMS,1) = 2 then 'Да' end as "IsSMS",
	orgwrk.Org_Nick as "OrgWrk",
	coalesce(D.Diag_Code || ' ' || D.Diag_Name, D1.Diag_Code || ' ' || D1.Diag_Name) as "Diag",
	eup1.UslugaComplexTariff_Tariff as "UslugaComplexTariff_Tariff"
	
from (select
		eup.EvnUslugaPar_id,
		eup.UslugaComplex_id,
		eup.Person_id,
		eup.PersonEvn_id,
		eup.Server_id,
		eup.MedPersonal_id,
		eup.Lpu_id,
		eup.EvnDirection_id,
		eup.LpuSection_uid,
		eup.EvnLabSample_id,
		eup.EvnUslugaPar_setDT,
		eup.Lpu_did,
		eup.evndirection_setdt,
		eup.EvnDirection_Num,
		eup.Org_did,
		eup.lpu_uid,
		eup.UslugaPlace_id,
		eup.Org_uid,
		LpuSection.LpuSection_Name,
		uc1.UslugaComplex_Code,
		uc1.UslugaComplex_Name,
		uc1.UslugaComplexTariff_Tariff,
		eup.Diag_id
	from v_EvnUslugaPar eup
	left join params on 1=1
	left join LpuSection   on lpuSection.lpuSection_id=eup.LpuSection_uid
	left join lateral
	(
		select * from LpuUnit   where LpuUnit.LpuUnit_id = LpuSection.LpuUnit_id limit 1
	)LpuUnit on true
	left join lateral
	(
		select uc1.*, uct.UslugaComplexTariff_Tariff
		from v_UslugaComplex as uc1
		left join v_UslugaComplexTariff uct on uct.UslugaComplex_id = uc1.UslugaComplex_id
			and uct.UslugaComplexTariff_begDate <= p_enddate and (uct.UslugaComplexTariff_endDate >= p_enddate or uct.UslugaComplexTariff_endDate is null)
		where uc1.UslugaComplex_id = eup.UslugaComplex_id limit 1
	)uc1 on true
	where  (eup.EvnUslugaPar_setdate >= p_begDate)
		and (eup.EvnUslugaPar_setdate <= p_endDate)
		AND (eup.Lpu_id = p_Lpu_id or p_Lpu_id is null)
		and (eup.PayType_id = p_PayType_id or p_PayType_id is null)
		and (eup.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null) 
		and (LpuSection.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
		and (LpuSection.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
		and (LpuUnit.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
		and (eup.UslugaComplex_id = p_Usluga_id or p_Usluga_id is null)
		and (eup.MedPersonal_sid=p_MedPersonal_sid or p_MedPersonal_sid is null)
	) eup1
	inner join v_person_all ps  on PS.PersonEvn_id = eup1.PersonEvn_id and PS.server_id = eup1.server_id
	left join v_Job j on j.job_id = ps.Job_id
	left join Org orgwrk on orgwrk.Org_id = j.Org_id
--	left join lateral
--	(
--		select * from EvnLabSample els1  where els1.Evn_id = eup1.EvnLabSample_id
--	)els1 on true
	left join lateral
	(
		select ut1.*, uc21.UslugaComplex_Code, uc21.UslugaComplex_Name from v_UslugaTest as ut1 
		left join v_UslugaComplex as uc21  on uc21.UslugaComplex_id = ut1.UslugaComplex_id
		where --ut1.EvnLabSample_id = els1.Evn_id and 
		eup1.EvnUslugaPar_id=ut1.uslugatest_rid and ut1.Uslugatest_ResultApproved = 2
	)ut1 on true
	left join v_EvnDirection_all ED  on ED.EvnDirection_id = eup1.EvnDirection_id
	left join v_Diag d on d.Diag_id = eup1.Diag_id
	left join v_Diag d1 on d1.Diag_id = ed.Diag_id
	left join v_Lpu lpuDir1  on lpuDir1.Lpu_id = ED.Lpu_sid
	left join v_Lpu lpuDir2  on lpuDir2.Lpu_id = eup1.Lpu_did
	left join v_Polis Polis  on Polis.Polis_id = PS.Polis_id
	left join v_Address as Addr1  on Addr1.Address_id = PS.UAddress_id
	left join v_Address as Addr2  on Addr2.Address_id = PS.PAddress_id
	left join v_Org Org  on Org.Org_id = eup1.Org_did
	left join v_OrgSMO OrgSMO  on OrgSMO.OrgSMO_id = Polis.OrgSMO_id 
	left join v_OmsSprTerr OST  on OST.OMSSprTerr_id = Polis.OMSSprTerr_id
	left join v_NewslatterAccept NA  on NA.Person_id = PS.Person_id
	left join v_Lpu lpu on lpu.lpu_id=eup1.lpu_id --свое МО
	left join v_Lpu lpu_uid on lpu_uid.lpu_id=eup1.lpu_uid --чужое МО
	left join v_UslugaPlace up on up.UslugaPlace_id=eup1.UslugaPlace_id
	left join v_Org org_uid on org_uid.Org_id=eup1.Org_uid
	left join v_Document Document on Document.Document_id = PS.Document_id
	left join params on 1=1
where (1=1)
and eup1.EvnUslugaPar_setDT between p_begdate and p_enddate + interval '1 day' - interval '1 minute'
and (coalesce(lpuDir1.Lpu_id,lpuDir2.Lpu_id) = p_LpuDir_id or p_LpuDir_id is null)
and ((p_CategStrah=1 and Polis.Polis_id is not null and OST.KLRgn_id=dbo.GetRegion() and (Polis.Polis_endDate is null or Polis.Polis_endDate > coalesce(ut1.UslugaTest_setDT,eup1.EvnUslugaPar_setDT)) --застрахованные на терр региона
	or p_CategStrah=2 and (Polis.Polis_id is null or (Polis.Polis_endDate is not null and Polis.Polis_endDate <= coalesce(ut1.UslugaTest_setDT,eup1.EvnUslugaPar_setDT)))) --не застрахованные
	or (p_CategStrah=3 and Polis.Polis_id is not null and coalesce(OST.KLRgn_id,0)!= dbo.GetRegion() and (Polis.Polis_endDate is null or Polis.Polis_endDate > coalesce(ut1.UslugaTest_setDT,eup1.EvnUslugaPar_setDT)) --застрахованные на терр др. субъектов РФ
	or p_CategStrah is null))
and (p_Polis_id=1 and Polis.Polis_id is not null
	or p_Polis_id=2 and Polis.Polis_id is null
	or p_Polis_id is null)
limit 300000
