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_EMDMedStaffFact_id,
		cast($4 as bigint) as p_person_id,
		cast($5 as bigint) as p_EMDDocumentTypeLocal_id,
		cast($6 as bigint) as p_EMDVersionIsReady_id,
		cast($7 as bigint) as p_ActualVersions_id,
		cast($8 as timestamp) as p_setbegdate,
		cast($9 as timestamp) as p_setenddate,
		cast($10 as timestamp) as p_begdate,
		cast($11 as timestamp) as p_enddate
)

select
dt."EMDDocumentType_Name" as "EMDDocumentTypeLocal_Name",
v."EMDVersion_insDT" as "EMDVersion_insDT",
vs."EMDVersionStatus_Name",
r."Person_id",
coalesce(r."Person_SurName",'') || ' ' || coalesce(r."Person_FirName",'') || ' ' || coalesce(r."Person_SecName",'') as person_fio,
to_char(r."Person_Birthday",'DD.MM.YYYY') as person_birthday,
s."MedStaffFact_id",
r."Lpu_id",
r."EMDRegistry_Num",
to_char(v."EMDVersion_RegistrationDate", 'DD.MM.YYYY') as "EMDVersion_RegistrationDate",
v."EMDVersion_VersionNum",
ff."EMDFileFormat_Name",
v."EMDVersion_EmdrId",
case when vs."EMDVersionStatus_id" in (2,5,6) then coalesce(qe."EMDQueryError_Code", 'Не указано') end as err_code,
case when vs."EMDVersionStatus_id" in (2,5,6) then coalesce(qe."EMDQueryError_Message", 'Не указано') end as err_msg
from "EMD"."EMDRegistry" r
left join params on 1=1
inner join lateral
(
	select max(v."EMDVersion_VersionNum") as "EMDVersion_VersionNum", v."EMDVersion_id", v."EMDVersion_IsReady", v."EMDFileFormat_id", v."EMDVersion_insDT",
	v."EMDVersion_RegistrationDate", v."EMDVersion_EmdrId"
	from "EMD"."EMDVersion" v
	where r."EMDRegistry_id"=v."EMDRegistry_id"
	and v."EMDVersion_IsReady" is not null
	and cast(v."EMDVersion_insDT" as date) between p_begdate and p_enddate
	and ((p_setbegdate is not null and p_setenddate is not null and 
	cast(v."EMDVersion_RegistrationDate" as date) between p_setbegdate and p_setenddate)
	or (p_setbegdate is null and p_setenddate is null and 
	(cast(v."EMDVersion_RegistrationDate" as date) between cast(to_timestamp('01.01.1970', 'DD.MM.YYYY') as timestamp) and current_date or v."EMDVersion_RegistrationDate" is null)))
	group by v."EMDVersion_id"
	order by v."EMDVersion_VersionNum" desc
	limit case when p_ActualVersions_id=2 then 1 else 100000 end
)v on true
left join "EMD"."EMDDocumentTypeLocal" dtl on dtl."EMDDocumentTypeLocal_id"=r."EMDDocumentTypeLocal_id"
inner join "EMD"."EMDDocumentType" dt on dtl."EMDDocumentType_id"=dt."EMDDocumentType_id" and dtl."EMDDocumentTypeLocal_IsREMD"=2

left join "EMD"."EMDVersionStatus" vs on vs."EMDVersionStatus_id"=v."EMDVersion_IsReady"
left join "EMD"."EMDSignatures" s on s."EMDVersion_id"=v."EMDVersion_id" and s."EMDSignatures_Stage"=1
left join "EMD"."EMDFileFormat" ff on ff."EMDFileFormat_id"=v."EMDFileFormat_id"
left join lateral
(
	select jq."EMDQueryType_id", jq."EMDJournalQuery_id", jq."EMDQueryStatus_id", jq."EMDJournalQuery_updDT"
	from "EMD"."EMDJournalQuery" jq
	where r."EMDRegistry_id"=jq."EMDRegistry_id"
	order by jq."EMDJournalQuery_updDT" desc, jq."EMDJournalQuery_id" desc
	limit 1
)jq on true
left join lateral
(
	select * from "EMD"."EMDQueryError" qe
	where qe."EMDJournalQuery_id"=jq."EMDJournalQuery_id"
	order by qe."EMDQueryError_updDT" desc, qe."EMDQueryError_id" desc
	limit 1
)qe on true
where 1=1
and (vs."EMDVersionStatus_id" = p_EMDVersionIsReady_id or p_EMDVersionIsReady_id is null)
and (dt."EMDDocumentType_Code" = p_EMDDocumentTypeLocal_id or p_EMDDocumentTypeLocal_id is null)
and (r."Person_id" = p_person_id or p_person_id is null)
--and (s."MedStaffFact_id" = p_EMDMedStaffFact_id or p_EMDMedStaffFact_id is null)
and (r."LpuBuilding_id" = p_lpubuilding_id or p_lpubuilding_id is null)
and (r."Lpu_id" = cast(p_lpu_id as bigint) or cast(p_lpu_id as bigint) is null)
and coalesce(r."EMDRegistry_deleted",1)=1
order by v."EMDVersion_insDT"

