--select * from v_lpu where lpu_nick ilike '%ТАЗОВСКАЯ%'
with params as(
	select
		cast(13101850 as bigint) as p_lpu_id,
		cast(null as bigint) as p_LpuBuilding_id,
		cast(null as bigint) as p_LpuUnit_id,
		cast(null as bigint) as p_LpuSection_id,
		cast(null as bigint) as p_LpuRegionType_id,
		cast(null as bigint) as p_LpuRegion_id,
		cast(null as bigint) as p_lpu_did,
		cast(null as bigint) as p_MedPersonal_id,
		cast(null as bigint) as p_PayType_id,
		cast(null as bigint) as p_SocStatus_id,
		cast(null as bigint) as p_PrivilegeType_id,
		cast(null as bigint) as p_OrgSmo_id,
		cast(null as bigint) as p_OmsSprTerr_id,
		cast(null as bigint) as p_Org_id,
		cast('2022-01-01' as timestamp) as p_begdate,
		cast('2022-01-10' as timestamp) as p_enddate,
		cast(null as bigint) as p_begAge,
		cast(null as bigint) as p_endAge,
		cast(null as bigint) as p_prehosptrauma_id,
		cast(null as bigint) as p_IsUnlaw,
		cast(null as bigint) as p_KLArea_id,
		cast(null as bigint) as p_IsFinish_id,
		cast(null as bigint) as p_KatNasel_id,
		cast(null as bigint) as p_ReestrNum,
		cast(null as bigint) as p_YesNo,
		cast(1 as bigint) as p_OnlyPersonCard_YN
)

select 
Person.Person_id as "Person_id",
Person.polis_id,
ROW_NUMBER() OVER(ORDER BY 
						coalesce(rtrim(Person.person_surname),'') || ' ' || 
						coalesce(rtrim(Person.person_firname),'') || ' ' || 
						coalesce(rtrim(Person.person_secname),''),
						LpuSection.LpuSection_Name,  
						smo.Org_Nick,
						OrgJob.Org_Nick,  
						Diag.Diag_FullName,  
						EvnPL_setDate,  
						EvnPL_disDate	
					) as "RowNum", --1. номер строки
	coalesce(rtrim(Person.person_surname),'') || ' ' || 
						coalesce(rtrim(Person.person_firname),'') || ' ' || 
						coalesce(rtrim(Person.person_secname),'') as "Person_FIO", --2. ФИО пациента
	to_char(cast(Person.Person_birthday as date), 'DD.MM.YYYY') as "Person_birthday", --3. дата рождения
	EvnPL.Person_Age as "Person_Age", --4. возраст
	Sex.Sex_Name as "Sex_Name", --5. пол
	smo.Org_Nick as "OrgSMO_Nick", --6. СМО
	rtrim(coalesce(Polis.Polis_Ser,'')) || ' ' || rtrim(coalesce(case when Polis.PolisType_id = 4 then Person.person_ednum else Polis.Polis_Num end,'')) as "PersonPolis", --7. Полис
	coalesce(a1.Address_Address,a2.Address_Address) as "Address_Address", --8. адрес
	OrgJob.Org_Nick as "Org_Nick", --9. место работы
	personcard.Lpu_Nick as "Lpu_Nick", --10. МО прикрепления
	EvnPL.EvnPL_NumCard as "EvnPL_NumCard", --11. ТАП
	DiagN.Diag_FullName as "DiagN", --12. диагноз направившего МО
	LpuSection.LpuSection_Name as "LpuSection_Name", --13. отделение
	MedPersonal.Person_Fio as "medpers_FIO", --14. врач
	Diag.Diag_FullName as "Diag_FullName", --15. диагноз
	to_char(min(EvnVizitPL_setDate), 'DD.MM.YYYY') as "EvnPL_setDate", --16. дата начала лечения
	to_char(max(EvnVizitPL_setDate), 'DD.MM.YYYY') as "EvnPL_disDate", --17. дата конца
	ResultClass.ResultClass_Name as "ResultClass_Name", --18. результат лечения
	count (distinct EvnVizit.evnvizitpl_id) as "EvnVizit_kolvo", --19. количество посещений
	coalesce(EvnVizit.EvnVizitPL_Uet,0) as "Uet", --20. УЕТ
	coalesce(EvnPL.EvnPL_UKL, 1) as "UKL", --21. УКЛ
	rd1.RegistryData_Tariff as "Stoim", --22. Стоимость
	coalesce(rd1.RegistryData_ItogSum, rd1.RegistryData_Tariff) as "ItogSum", --23. сумма к оплате
	r.Registry_Num as "Registry_Num", --24. номер счета
	to_char(r.Registry_accDate,'DD.MM.YYYY') as "Registry_accDate", --25. дата счета
	Person.Person_Snils,
	ltrim(coalesce(dt.DocumentType_Name, '') || ' ') || ltrim(coalesce(d.Document_Ser, '') || ' ') || coalesce(d.Document_Num, '') as doc,
	vt.VizitType_Code || ' ' || vt.VizitType_Name as vizitType

from v_evnvizitpl evnvizit
left join params on 1=1
left join v_VizitType vt on vt.VizitType_id = evnvizit.VizitType_id
inner join v_EvnPl EvnPL  on EvnPL.EvnPL_id=EvnVizit.EvnVizitPL_pid
							and EvnPL.Lpu_id=p_Lpu_id
left join /*reg_schema*/r89/*/reg_schema*/.RegistryData RD1  on evnPL.evnPL_id=RD1.Evn_rid
left join /*reg_schema*/r89/*/reg_schema*/.Registry R on R.Registry_id=RD1.Registry_id
inner join lateral
(
	select * from v_Person_reg as Person  where Person.PersonEvn_id=EvnVizit.PersonEvn_id 
							and Person.Server_id=EvnVizit.Server_id
							limit 1
)Person on true
left join v_Document d on d.Document_id = Person.Document_id
left join v_DocumentType dt on dt.DocumentType_id = d.DocumentType_id
inner join Sex  on coalesce(Person.Sex_id,1) = Sex.Sex_id --#93179
inner join v_LpuSection LpuSection  on LpuSection.LpuSection_id = evnvizit.LpuSection_id
inner join LpuUnit   on LpuSection.LpuUnit_id = LpuUnit.LpuUnit_id
inner join LpuSectionProfile  on LpuSectionProfile.LpuSectionProfile_id=LpuSection.LpuSectionProfile_id
--left join v_EvnVizit as EvnVizitPLStom  on  EvnVizitPLStom.EvnVizit_id=EvnVizit.EvnVizitPL_id
left join v_MedPersonal as MedPersonal  on MedPersonal.MedPersonal_id = EvnVizit.MedPersonal_id
	and MedPersonal.Lpu_id = EvnVizit.Lpu_id
left join v_Diag as Diag  on Diag.Diag_id = coalesce(Evnvizit.Diag_id,EvnPL.Diag_id)
left join v_Diag as DiagN  on DiagN.Diag_id = EvnPL.Diag_did
left join Address as a1  on a1.Address_id = Person.PAddress_id
left join Address as a2  on a2.Address_id = Person.UAddress_id
left join v_KLAreaStat as1 on as1.KLAreaStat_id = a1.KLAreaStat_id
left join v_KLAreaStat as2 on as2.KLAreaStat_id = a2.KLAreaStat_id
left join lateral (select OMSSprTerr_id, OMSSprTerr_Code from  OMSSprTerr as OMSSprTerr   where  
								 ((coalesce(a1.KLCountry_id,a2.KLCountry_id) = OMSSprTerr.KLCountry_id) or (OMSSprTerr.KLCountry_id is null)) and
								 ((coalesce(a1.KLRGN_id,a2.KLRGN_id) = OMSSprTerr.KLRGN_id) or (OMSSprTerr.KLRGN_id is null)) and
								 ((coalesce(a1.KLSubRGN_id,a2.KLSubRGN_id) = OMSSprTerr.KLSubRGN_id) or (OMSSprTerr.KLSubRGN_id is null)) and
								 ((coalesce(a1.KLCity_id,a2.KLCity_id) = OMSSprTerr.KLCity_id) or (OMSSprTerr.KLCity_id is null)) and
								 ((coalesce(a1.KLTown_id,a2.KLTown_id) = OMSSprTerr.KLTown_id) or (OMSSprTerr.KLTown_id is null))
order by OMSSprTerr_id 
limit 1
)OMSSprTerr on true ---#97918
left join ResultClass   on EvnPL.ResultClass_id = ResultClass.ResultClass_id
left join Polis  on Polis.Polis_id=Person.Polis_id
	left join lateral
	(
	SELECT 
	Org.Org_Nick,
	Org_pre.Org_Nick as Org_pre_Nick,
	OrgSMO_pre.OrgSmo_id as OrgSMO_pre_id,
	OrgSMO.OrgSmo_id
	FROM OrgSMO 
	left join Org  on OrgSMO.Org_id=Org.Org_id
	left join Org Org_pre  on Org.Org_nid=Org_pre.Org_id
	left join OrgSMO OrgSMO_pre  on OrgSMO_pre.Org_id=Org_pre.Org_id
	where OrgSMO.OrgSMO_id=Polis.OrgSmo_id
	) smo on true
left join OMSSprTerr as OMSSprTerrP  on (OMSSprTerrP.OMSSprTerr_id = Polis.OMSSprTerr_id)
left join Job  on Person.Job_id=Job.Job_id
left join Org OrgJob  on Job.Org_id=OrgJob.Org_id
left join v_prehosptrauma prehosptrauma  on prehosptrauma.prehosptrauma_id=EvnPL.prehosptrauma_id

left join lateral
		(
		select  
			PersonPrivilege_id, 
			PrivilegeType_id 
		from PersonPrivilege  
		where 1=1
			and Person.Person_id=PersonPrivilege.Person_id
			and (EvnPL.EvnPL_SetDate>=PersonPrivilege.PersonPrivilege_begDate)
			and (EvnPL.EvnPL_SetDate<=coalesce(PersonPrivilege.PersonPrivilege_endDate,EvnPL.EvnPL_SetDate))
		order by PersonPrivilege.PersonPrivilege_begDate
		limit 1
		)PersonPrivilege on true
 
left join lateral
		(
		select
			PersonCard_id, 
			LpuRegion_id,
			lpu_nick, 
			PersonCard.lpu_id
		from v_PersonCard_all as PersonCard  
		inner join v_lpu Lpu  on lpu.lpu_id=PersonCard.lpu_id
		where PersonCard.Person_id=Person.Person_id
			and ((PersonCard.personcard_begDate<=evnpl.evnpl_setdate) 
			and (PersonCard.personcard_endDate >=evnpl.evnpl_setdate or PersonCard.personcard_endDate is null))
			and (LpuAttachType_id = 1)
		order by PersonCard.personcard_begDate
		limit 1
		)personcard on true
left join lateral
(	
	select max (RD./*reg_paid*/Paid_id/*/reg_paid*/) as RegistryData_isPaid 
	from /*reg_schema*/r89/*/reg_schema*/.RegistryData RD 
	where evnPL.evnPL_id=RD.Evn_rid
	and (RD.Registry_id=p_ReestrNum or p_ReestrNum is null)
)rd on true
where 1=1
	and EvnVizit.Lpu_id=p_Lpu_id
	and ((p_ReestrNum is null and EVNvizit.EvnvizitPL_setDate between p_begdate AND p_enddate) or
	(p_ReestrNum is not null and EvnPL.EvnPL_disDate between p_begdate and p_enddate))
	and (EvnVizit.LpuSection_id=p_LpuSection_id or p_LpuSection_id is null)
	and (LpuSection.LpuUnit_id=p_LpuUnit_id or p_LpuUnit_id is null)
	and (LpuUnit.LpuBuilding_id =p_LpuBuilding_id or p_LpuBuilding_id is null)  
	and (EvnVizit.Medpersonal_id=p_MedPersonal_id or p_MedPersonal_id is null)
	and (PersonCard.LpuRegion_id=p_LpuRegion_id or p_LpuRegion_id is null)
	and (EvnVizit.PayType_id=p_PayType_id or  p_PayType_id is null)
	and (Person.SocStatus_id=p_SocStatus_id or  p_SocStatus_id is null)
	and (smo.OrgSmo_id=p_OrgSmo_id or smo.OrgSMO_pre_id=p_OrgSmo_id or p_OrgSmo_id IS NULL)
	and (OMSSprTerrP.OmsSprTerr_Code=p_OmsSprTerr_id or p_OmsSprTerr_id is null)
	and (OrgJob.Org_id=p_Org_id or p_Org_id is null)
	and (EvnPL.Person_Age >=p_BeGAge or p_BegAge is null)
	and (EvnPL.Person_Age <=p_EndAge or p_EndAge is null)
	and (coalesce(p_IsUnlaw,1)=coalesce(EvnPL_IsUnlaw,1) or p_IsUnlaw is null)
	and ((prehosptrauma.prehosptrauma_id = p_prehosptrauma_id)or(p_prehosptrauma_id is null))
	and (PersonPrivilege.PrivilegeType_id = p_PrivilegeType_id or p_PrivilegeType_id is null)
	and (case when EvnPL.EvnPL_IsFinish = 2 then 2 else 1 end =p_IsFinish_id or p_IsFinish_id is null)
	and (( RD.RegistryData_isPaid = 2 and p_YesNo= 2) or (coalesce(RD.RegistryData_isPaid,1) = 1 and p_YesNo= 1) or p_YesNo is null)
	and (EvnPL.Lpu_did=p_lpu_did or p_lpu_did is null)
	and  (((PersonCard.Lpu_id!=p_Lpu_id or PersonCard.Lpu_id is null) and p_OnlyPersonCard_YN=1)
	or (PersonCard.Lpu_id=p_Lpu_id and p_OnlyPersonCard_YN=2) or p_OnlyPersonCard_YN is null)
		and (p_KatNasel_id = 1 and (OMSSprTerrP.KLRgn_id = 59) or
	(p_KatNasel_id = 2 and OMSSprTerrP.KLRgn_id != 59) or  p_KatNasel_id is null)
	and (coalesce(as1.KLAreaStat_Code, as2.KLAreaStat_Code)=p_KLArea_id or p_KLArea_id is null)
	and (RD1.Registry_id=p_ReestrNum or p_ReestrNum is null)
	and evnvizit.EvnVizitPL_Index = evnvizit.EvnVizitPL_Count - 1
group by 
EvnPL.EvnPL_id,
Person.Person_id,
Person.polis_id,
LpuSectionProfile.LpuSectionProfile_Code, 
LpuSection.LpuSection_Name, 
coalesce(rtrim(Person.person_surname),'') || ' ' || coalesce(rtrim(Person.person_firname),'') || ' ' || coalesce(rtrim(Person.person_secname),''),
Person.Person_birthday,EvnPL.Person_Age,Sex.Sex_Name,
smo.Org_Nick,
rtrim(coalesce(Polis.Polis_Ser,'')) || ' ' || rtrim(coalesce(case when Polis.PolisType_id = 4 then Person.person_ednum else Polis.Polis_Num end,'')),
coalesce(a1.Address_Address,a2.Address_Address),
OrgJob.Org_Nick, 
EvnPL.EvnPL_NumCard, 
Diag.Diag_FullName,
EvnPL.EvnPL_setDate, 
EvnPL.EvnPL_disDate, 
ResultClass.ResultClass_Name,
coalesce(EvnPL.EvnPL_UKL, 1),  
DiagN.Diag_FullName,
MedPersonal.Person_Fio,
personcard.Lpu_Nick,
EvnVizitPL_Uet,
rd1.RegistryData_ItogSum,
rd1.RegistryData_Tariff,
r.Registry_Num,
r.Registry_accDate,
Person.Person_Snils,
ltrim(coalesce(dt.DocumentType_Name, '') || ' ') || ltrim(coalesce(d.Document_Ser, '') || ' ') || coalesce(d.Document_Num, ''),
vt.VizitType_Code || ' ' || vt.VizitType_Name
