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_uslugacomplex_id,
	cast($7 as timestamp) as p_begdate,
	cast($8 as timestamp) as p_enddate,
	cast($9 as bigint) as p_othermo_id,
	cast($10 as bigint) as p_covid
),
covid_usl as(
    select
		/*evndirection_covid19_uslugarezult_pg.rptdesign*/
		t_table.usl_Text1
    from(values
		('A11.08.010.001.1'),
		('A26.05.066.002.'),
		('A26.05.076'),
		('A26.06.091.001.'),
		('A26.06.091.002.'),
		('A26.06.092.001'),
		('A26.06.092.001.'),
		('A26.06.114.02'),
		('A26.08.000.000.023'),
		('A26.08.000.000.025'),
		('A26.08.000.001'),
		('A26.08.000.002'),
		('A26.08.0004'),
		('A26.08.0005'),
		('A26.08.027'),
		('A26.08.027.001'),
		('A26.08.027.001.1'),
		('A26.08.027.001.2'),
		('A26.08.027.001.3'),
		('A26.08.027.001.4'),
		('A26.08.027.001.5'),
		('A26.08.027.001.6'),
		('A26.08.027.001.7'),
		('A26.08.027.001.8'),
		('A26.08.027.001.9'),
		('A26.08.027.001/2'),
		('A26.08.027.001/2.1'),
		('A26.08.027.001/2.2'),
		('A26.08.027.001/3'),
		('A26.08.027.001/3.1'),
		('A26.08.027.001/3.2'),
		('A26.08.027.002'),
		('A26.08.027.003'),
		('A26.08.027.004'),
		('A26.08.027.004.001'),
		('A26.08.027.004.002'),
		('A26.08.027.004.003'),
		('A26.08.027.005'),
		('A26.08.027.006'),
		('A26.08.027.007'),
		('A26.08.046.001/2'),
		('A26.08.046.001/2.1'),
		('A26.08.046.001/2.2'),
		('A26.08.046.001/3'),
		('A26.08.046.001/3.1'),
		('A26.08.046.001/3.2'),
		('A26.08.046.002'),
		('A26.08.046.099'),
		('A26.09.044.002'),
		('A26.09.060.002'),
		('A26.19.092.101'),
		('A26.31.011'),
		('A26.31.012'),
		('A26.05.066.002'),
		('A26.06.091.002'),
		('A26.06.091.001'),
		--Курган
		('A26.06.091.001'),
		('A26.06.091.002'),
		('A26.06.092.001'),
		('A26.08.008'),
		('A26.08.008.001'),
		('A26.08.008.002'),
		('A26.08.027.001'),
		('A26.05.066.002.1'),
		('A26.08.046.002.1'),
		('A26.09.044.002.1'),
		('A26.09.060.002.1'),
		('A26.05.066.002.2'),
		('A26.08.046.002.2'),
		('A26.09.044.002.2'),
		('A26.09.060.002.2')
	)as t_table(usl_Text1)
)
select
	row_number() over(order by ed.EvnDirection_setDT asc) as row_n,
	ed.EvnDirection_id as "EvnDirection_id",
	ls.LpuSection_Name as "LpuSection_Name",
	ed.evndirection_num,
	ed.EvnDirection_setDate as "EvnDirection_setDate",	
	dl.Lpu_Nick as "Lpu_Nick",
	(
		select Person_Fio 
		from v_MedPersonal
		where MedPersonal_id =  ed.MedPersonal_id
		limit 1
		) as "Person_Fio",
	concat_ws(' ',
		ps.Person_Surname,
		ps.Person_Firname,
		ps.Person_Secname) as fiopac,	
	ps.Person_Birthday as "Person_Birthday",
	doc.pasport,
	ps.person_snils,
	ps.Polis_Num as "Polis_Num",
	ps.Person_Phone as "Person_Phone",
	uc.UslugaComplex_Code as "UslugaComplex_Code",
	uc.UslugaComplex_Name as "UslugaComplex_Name",
	case 
		when uc.evnpl_numcard is not null then coalesce('Да/№' || uc.evnpl_numcard, '') 
		when epl.evnpl_numcard is not null then coalesce('Нет/№' || epl.evnpl_numcard, '') 
		else 'Нет/Направление сформировано автоматически' end as epl_num,
	uc_test.UslugaComplex_Code as test_code,
	uc_test.UslugaComplex_Name as test_name,
	right(els.EvnLabSample_Num, 4) as prob_num,
	coalesce(utest.UslugaTest_ResultValue,'') || ' '
		|| coalesce(utest.UslugaTest_ResultUnit,'') as rezult,
	(select lpu_nick from v_lpu where lpu_id = utest.Lpu_id) as lpu_dir,
	utest.UslugaTest_setDT as rezult_date,
	(
		select Person_Fio
		from v_MedPersonal 
		where MedPersonal_id = els.MedPersonal_aid
		limit 1
	) as medpersonal_dir,
	(select ad.Address_Address from v_address ad where ad.address_id = coalesce(ps.PAddress_id, ps.UAddress_id) limit 1) as adres,
	mat.matname,
	reactiv.rname
from params
	left join v_EvnDirection_all as ed on ed.Lpu_sid = p_lpu_id or p_lpu_id is null
	left join v_LpuSection as ls on ls.LpuSection_id = ed.LpuSection_id
	left join v_Lpu as dl on dl.Lpu_id = ed.Lpu_did
	left join v_PersonState as ps on ed.Person_id = ps.Person_id
	left join lateral
	(
		select
			uc.UslugaComplex_Code,
			uc.UslugaComplex_Name,
			epl.evnpl_numcard
		from 
			dbo.v_UslugaComplex as uc 
			left join v_EvnUslugaPar eup on uc.UslugaComplex_id = eup.UslugaComplex_id
			left join v_evnpl epl on epl.evnpl_id = eup.evnuslugapar_rid
		where 
			eup.EvnDirection_id = ed.EvnDirection_id or uc.uslugacomplex_id = ed.uslugacomplex_did 
		limit 1
	) uc on true
	left join lateral
	(
		select 
			epl.evnpl_numcard
		from 
			v_evnpl epl
		where
			epl.evnpl_id = ed.evndirection_rid 
		limit 1
	) epl on true
	left join v_EvnLabRequest as elr on elr.EvnDirection_id = ed.EvnDirection_id
	left join dbo.v_EvnLabSample as els on elr.EvnLabRequest_id = els.EvnLabRequest_id
		and els.LabSampleStatus_id = 4
	left join dbo.v_UslugaTest as utest on els.evnlabsample_id = utest.EvnLabSample_id
	left join dbo.v_UslugaComplex as uc_test on utest.UslugaComplex_id = uc_test.UslugaComplex_id
	left join lateral
	(
		select 
			QCM.QcControlMaterial_Name as matname 
		FROM lis.QcControlMaterialValue QCMV
			LEFT JOIN lis.v_QcControlMaterial QCM on QCM.QcControlMaterial_id = QCMV.QcControlMaterial_id
			LEFT JOIN dbo.v_UslugaComplex UC on UC.UslugaComplex_id = QCMV.UslugaComplex_id
		WHERE 
			QCM.Lpu_id = ed.Lpu_did and QCMV.UslugaComplex_id=utest.UslugaComplex_id
		limit 1
	) mat on true
	left join lateral
	(
		select
			coalesce(DN.DrugNomen_Code,'') || ' ' || coalesce(DN.DrugNomen_Name, '') as rname
		from
			v_NormCostItem NCI
			left join rls.v_DrugNomen DN on DN.DrugNomen_id = NCI.DrugNomen_id
		where 
			NCI.UslugaComplex_id = utest.UslugaComplex_id
		limit 1
	) reactiv on true
	left join lateral
	(
		select
			coalesce(dct.DocumentType_Name,'') || ' ' ||
				coalesce(dc.Document_Ser,'') || ' ' || 
				coalesce(dc.Document_Num,'') as pasport
		from
			v_Document dc
			left join v_DocumentType dct on dc.DocumentType_id = dct.DocumentType_id
		where
			ps.Document_id = dc.Document_id
		limit 1
	) as doc on true
where true
	and ed.EvnDirection_setDT between p_begdate and p_enddate
	and ed.dirtype_id in (10,7,29,30)
	and ed.EvnDirection_failDT is null
	and (ls.LpuBuilding_id = p_lpubuilding_id or p_lpubuilding_id is null)
	and (ls.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 (ed.MedPersonal_id = p_medpersonal_id or p_medpersonal_id is null)
	and (uc_test.UslugaComplex_id = p_uslugacomplex_id or p_uslugacomplex_id is null)
	and ((p_othermo_id = 1 and ed.lpu_id = ed.lpu_did) or p_othermo_id = 2)
	and (p_covid = 2 and uc_test.UslugaComplex_Code in (select usl_Text1 from covid_usl)
			or p_covid = 1)
