with param as (
select  cast($1 as bigint) p_lpu, 
cast($2 as bigint)  as p_lpubuilding,
cast($3 as bigint)  as p_lpuunit,
cast($4 as bigint)  as p_lpusection,
cast($5 as varchar) as p_lpufiliallist,
cast($6 as bigint)  as p_sex,
cast($7 as bigint)  as p_begpersonage,
cast($8 as bigint)  as p_endpersonage,
CAST(TO_TIMESTAMP($9,'DD.MM.YYYY') AS TIMESTAMP) as p_begdate, 
CAST(TO_TIMESTAMP($10,'DD.MM.YYYY') AS TIMESTAMP) as p_enddate
) ,

Lpu_W as(
select 
L.lpu_id,
L.lpu_Nick,
param.*
from v_Lpu L
inner join param on true
where (L.Lpu_id = p_lpu or p_lpu is null and COALESCE(L.Lpu_IsTest, 1) = 1)
),
        
EvnPS_W as (
 select
 lp.lpu_id,
 ps.person_id,
 eps.evnps_id,
 evns.evnsection_id,
 COALESCE(ps.person_surname, '') || ' ' || COALESCE(ps.Person_firname, '') || ' ' || COALESCE(ps.person_secname, '') as person_fio,
 to_char(ps.person_birthday,'DD.MM.YYYY') as person_birthday,
 dbo.ageYMD( ps.person_birthday, eps.EvnPS_setDate, 1) as age,
 case when ps.sex_id=1 then 'мужской' else 'женский' end as sex_name,
 LpuPC.Lpu_Nick as personcard_lpu,
 adr.address_address,
 coun.klcountry_name,
 lp.lpu_nick,
 ls.lpusection_fullname,

 case when ls.LpuSectionDopType_id=1 then 'ПСО' 
 when ls.LpuSectionDopType_id=2 then 'РССЦ' end doppriznak,
 
 COALESCE(lbed.LpuSectionBedProfile_Code||'. ','')||COALESCE(lbed.LpuSectionBedProfile_Name,'') as profilkoek,
 

 
 mf.person_fio as medstafname,
 eps.evnps_numcard,
 evns.diag_code,
 evns.diag_name, 
 dop_diag.diag_code_dop,
 dop_diag.diag_name_dop,
 to_char(eps.EvnPS_setDate,'DD.MM.YYYY') as evnps_setdate,
 to_char(eps.EvnPS_setDT , 'hh:mm') as evnps_setdate_time,
 cast (eps.EvnPS_disDate as date) as evnps_disdate,
 cast(eps.evnps_timedesease as varchar)||' '||ok.Okei_Name as evnps_timedesease,
 pa.prehosparrive_name,
 pa.prehosparrive_code,
 dp.diagsetphase_name,
 RSin.rankinscale_name,
 case when getregion()=1 then null else 
 evns.evnsection_insultscale end evnsection_insultscale,
 to_char(evnslast.EvnSection_disDate, 'DD.MM.YYYY') as evnps_disdate_last,
 
 
 case when evnslast.EvnSection_disDate is not null
					then
						case
							when date_part('day', evnslast.EvnSection_disDate - evnslast.EvnSection_setDate) + 1 > 1
							then date_part('day', evnslast.EvnSection_disDate - evnslast.EvnSection_setDate)
							else date_part('day', evnslast.EvnSection_disDate - evnslast.EvnSection_setDate) + 1
						end - coalesce(evnslast.EvnSection_Absence, 0)
					else null
				 end as koikodni,
 

 lt.leavetype_name,
 UslugaOMNK.uslugatds,
 UslugaOMNK.uslugattime,
 UslugaOMNK.uslugakds,
 UslugaOMNK.uslugaktime,
 UslugaOMNK.uslugamds,
 UslugaOMNK.uslugamtime,
 dipat.Diag_Code as diag_code_pat,
 dipat.Diag_Name as diag_name_pat
 
 from Lpu_W lp
 inner join v_evnps eps on eps.lpu_id=lp.lpu_id 
 and eps.EvnPS_disDate between p_begdate and p_enddate
 and eps.PrehospWaifRefuseCause_id is null
 
 
 --Первое основное движение
 inner join lateral (select 
 e.evnsection_id,
 e.lpusection_id,
 e.MedStaffFact_id,
 e.LpuSectionProfile_id,
 e.LpuSectionBedProfile_id,
 e.diag_id,
 e.RankinScale_id,
 e.EvnSection_InsultScale,
 diag.Diag_Code,
 diag.Diag_Name

 from v_evnsection e
 inner join v_LpuSection LS1 on LS1.LpuSection_id = e.LpuSection_id
 inner join v_LpuUnit LU1 on LU1.LpuUnit_id = LS1.LpuUnit_id
 inner join v_LpuUnitType LUT1 on LUT1.LpuUnitType_id = LU1.LpuUnitType_id
 and LUT1.lpuUnitType_Code=2--круглосуточный стационар
 inner join v_Diag Diag on Diag.Diag_id = e.Diag_id
 and (diag.Diag_Code like 'G45%' 
 or diag.Diag_Code like 'G46%' or diag.Diag_Code like 'I60%' 
 or diag.Diag_Code like 'I61%' or diag.Diag_Code like 'I62%' 
 or diag.Diag_Code like 'I63%' or diag.Diag_Code like 'I64%')

 where e.evnsection_pid=eps.evnps_id 
 and COALESCE(e.EvnSection_IsPriem,1)=1
 order by e.EvnSection_setDate limit 1
 )evns on true

  --Последнее основное движение
 inner join lateral (select 
 e.evnsection_id,
 e.EvnSection_setDate,
 e.EvnSection_disDate,
 e.EvnSection_Absence,
 e.LeaveType_id,
 e.LeaveType_fedid
 from v_evnsection e 
 inner join v_LpuSection LS1 on LS1.LpuSection_id = e.LpuSection_id
 inner join v_LpuUnit LU1 on LU1.LpuUnit_id = LS1.LpuUnit_id
 inner join v_LpuUnitType LUT1 on LUT1.LpuUnitType_id = LU1.LpuUnitType_id
 and LUT1.lpuUnitType_Code=2--круглосуточный стационар
 where e.evnsection_pid=eps.evnps_id 
 and COALESCE(e.EvnSection_IsPriem,1)=1
 order by e.EvnSection_setDate desc limit 1
 )evnslast on true

 left join v_LpuSection LS on LS.lpusection_id = evns.lpusection_id
 left join v_LpuBuilding lb on lb.LpuBuilding_id = ls.LpuBuilding_id --#161295
 left join v_LpuFilial lf on lf.LpuFilial_id = lb.LpuFilial_id
 --left join v_lpusectionprofile lpr on lpr.lpusectionprofile_id=evns.lpusectionprofile_id
 left join v_MedStaffFact mf on mf.MedStaffFact_id=evns.MedStaffFact_id
 inner join v_PersonState ps on ps.person_id = eps.Person_id 
 and (ps.sex_id = p_sex or p_sex is null)


 left join v_NationalityStatus ns on ns.NationalityStatus_id=ps.NationalityStatus_id
 left join v_klcountry coun on coun.klcountry_id = ns.klcountry_id
 left join V_PrehospArrive pa on pa.PrehospArrive_id=eps.PrehospArrive_id
 left join v_LpuSectionBedProfile lbed on lbed.LpuSectionBedProfile_id=evns.LpuSectionBedProfile_id
 left join v_okei ok on ok.okei_id=eps.Okei_id

 /*left join v_LpuSectionBedState LSBS on LSBS.lpusection_id=ls.lpusection_id
 left join fed.LpuSectionBedProfileLink LSBSlink on LSBSlink.LpuSectionBedProfileLink_id=LSBS.LpuSectionBedProfileLink_fedid
 left join fed.LpuSectionBedProfile lbedfed on lbedfed.LpuSectionBedProfile_id=LSBSlink.LpuSectionBedProfile_fedid*/
 
 left join v_address adr on adr.Address_id = COALESCE(ps.paddress_id,ps.uaddress_id)
 left join v_DiagSetPhase  dp on dp.DiagSetPhase_id=eps.DiagSetPhase_pid
 left join v_RankinScale RSin  on RSin.RankinScale_id = evns.RankinScale_id
 left join v_LeaveType lt0  on lt0.LeaveType_id = evnslast.LeaveType_id
 left join fed.LeaveType LT on LT.LeaveType_id = COALESCE(evnslast.LeaveType_fedid, lt0.LeaveType_fedid)

 left join lateral (select 
 d.Diag_Code,
 d.Diag_Name
 from v_EvnDie ED 
 inner join v_diag d on d.Diag_id=ed.Diag_aid
 where ED.EvnDie_pid = evnslast.EvnSection_id
 and LT.LeaveType_code in (105,106) and COALESCE(ED.EvnDie_IsAnatom,1)=2
 order by ed.EvnDie_setDate desc limit 1
 ) dipat on true
 
 left join lateral (
 select
 
 string_agg(d.Diag_code ,';'||chr(10)) 
 filter(where edps.DiagSetClass_id = 2) as Diag_code_dop,
 
 string_agg(d.Diag_name ,';'||chr(10)) 
 filter(where edps.DiagSetClass_id = 3) as Diag_name_dop
				
 from v_EvnDiagPS as edps
 inner join v_Diag as d on edps.Diag_id = d.Diag_id
 where edps.EvnDiagPS_rid = eps.EvnPS_id
 and edps.DiagSetClass_id in (2,3)
 and edps.DiagSetType_id = 3

 )dop_diag on true

   left join lateral (
   select 
 --  Тромболитическая терапия

   string_agg(to_char(eu.EvnUsluga_setDate,'DD.MM.YYYY'),';'||chr(10)) 
   filter(where UC.UslugaComplex_code in ('A11.12.003.002','A16.23.034.011','A16.23.034.012','A25.30.036.002','A25.30.036.003')) as uslugatds,	--Тромболитическая терапия Дата,
 
   string_agg(to_char(eu.EvnUsluga_setDate,'hh:mm'),';'||chr(10)) 
   filter(where UC.UslugaComplex_code in ('A11.12.003.002','A16.23.034.011','A16.23.034.012','A25.30.036.002','A25.30.036.003'))  as uslugattime,	--Тромболитическая терапия Время
 
   --КТ
   
   string_agg(to_char(eu.EvnUsluga_setDate,'DD.MM.YYYY'),';'||chr(10)) 
   filter(where UC.UslugaComplex_code in ('A06.23.004','A06.23.004.001','A06.23.004.002','A06.23.004.006','A06.23.004.007','A06.23.004.008'))  as uslugakds,	--КТ Дата,
 
   string_agg(to_char(eu.EvnUsluga_setDate,'hh:mm'),';'||chr(10)) 
   filter(where UC.UslugaComplex_code in ('A06.23.004','A06.23.004.001','A06.23.004.002','A06.23.004.006','A06.23.004.007','A06.23.004.008'))  as uslugaktime,	--КТ Время
  
   --МРТ
   
   string_agg(to_char(eu.EvnUsluga_setDate,'DD.MM.YYYY'),';'||chr(10)) 
   filter(where UC.UslugaComplex_code in ('A05.23.005','A05.23.005.001','A05.23.009','A05.23.009.001','A05.23.009.002','A05.23.009.003','A05.23.009.004','A05.23.009.005','A05.23.009.006'))  as uslugamds,	--МРТ Дата,
 
   string_agg(to_char(eu.EvnUsluga_setDate,'hh:mm'),';'||chr(10)) 
   filter(where UC.UslugaComplex_code in ('A05.23.005','A05.23.005.001','A05.23.009','A05.23.009.001','A05.23.009.002','A05.23.009.003','A05.23.009.004','A05.23.009.005','A05.23.009.006'))  as uslugamtime --МРТ Время
 
   
   from v_EvnUsluga EU  
   inner join v_UslugaComplex UC  on UC.UslugaComplex_id = EU.UslugaComplex_id
   where EU.EvnUsluga_rid = eps.EvnPS_id   
   ) UslugaOMNK on true

 
 left join lateral (
 select 
 pcd.PersonCard_id,
 pcd.LpuRegion_id,
 pcd.Lpu_id
 from v_PersonCard_all pcd
 where pcd.Person_id = ps.Person_id 
 and pcd.personcard_begDate < eps.evnps_setdate
 and (pcd.personcard_endDate >= eps.evnps_setdate or pcd.personcard_endDate is null) 
 and pcd.LpuAttachType_id = 1 limit 1
 ) personcard on true
 
 left join v_Lpu LpuPC on LpuPC.Lpu_id = PersonCard.Lpu_id

 where  (LS.LpuUnit_id = p_lpuunit or p_lpuunit is null)
 and (LS.LpuBuilding_id = p_lpubuilding or p_lpubuilding is null)
 and (LS.LpuSection_id = p_lpusection or p_lpusection is null)
 and (p_lpufiliallist is null or lf.LpuFilial_id in (select * from rpt.getids_fromstrlist_pg(p_lpufiliallist))) 
 and dbo.ageYMD(ps.person_birthday, eps.EvnPS_setDate, 1) between COALESCE(p_begpersonage, 0) and COALESCE(p_endpersonage, 200)

),

CMP_W as (
 select
 CmpCallCard.Lpu_id as lpu_id_cmp,
 CmpCallCard.person_id as person_id_cmp,
 cast(CmpCallCard.CmpCallCard_prmDT as DATE) date_cmp,

 to_char(COALESCE(CmpCloseCard.AcceptTime,CmpCallCard.CmpCallCard_prmDT),'DD.MM.YYYY') as datevizov_cmp, --Дата вызова
 to_char(COALESCE(CmpCloseCard.AcceptTime,CmpCallCard.CmpCallCard_prmDT),'hh:mm') as datevizov_time_cmp, --время вызова
 to_char(COALESCE(CmpCloseCard.ArriveTime,CmpCallCard.CmpCallCard_Przd),'hh:mm') as datepribit_time_cmp, --время прибытия на адрес
 to_char(COALESCE(CmpCloseCard.ToHospitalTime,CmpCallCard.CmpCallCard_Tsta),'hh:mm') as  datestac_time_cmp,-- время прибытия в стационар
 
 di.Diag_Code as diag_code_cmp,
 di.Diag_Name as diag_name_cmp,


CmpCallCardUsluga.uslugacomplex_code as uslugacomplex_code_cmp,
CmpCallCardUsluga.uslugaComplex_name as uslugacomplex_name_cmp
 
from EvnPS_W eps
inner join v_CmpCallCard as CmpCallCard on CmpCallCard.Lpu_id = eps.Lpu_id
and CmpCallCard.person_id=eps.person_id
left join v_CmpCloseCard as CmpCloseCard on CmpCallCard.CmpCallCard_id = CmpCloseCard.CmpCallCard_id
left join v_Diag di on di.Diag_id=COALESCE(CmpCloseCard.Diag_id,CmpCallCard.Diag_uid)

left join lateral 
(select 

 string_agg(UslugaComplex.UslugaComplex_Code ,';'||chr(10)) as UslugaComplex_Code,
 string_agg(COALESCE(UslugaComplex.UslugaComplex_Name) ,';'||chr(10)) as UslugaComplex_Name
 
 from v_CmpCallCardUsluga CmpCallCardUsluga 
 inner join v_UslugaComplex UslugaComplex on CmpCallCardUsluga.UslugaComplex_id = UslugaComplex.UslugaComplex_id
 and CmpCallCard.CmpCallCard_id = CmpCallCardUsluga.CmpCallCard_id
 and coalesce(UslugaComplex.UslugaComplex_Name,'') not like '%транспорт%' 
 and coalesce(UslugaComplex.UslugaComplex_Name,'') not like '%осмотр%'
 
 ) CmpCallCardUsluga on true
	
)


select
eps.*,
cmpp.*
from EvnPS_w eps

--последняя предшествующая карта СМП
left join lateral (select 
cmp.*
from CMP_W cmp 
where cmp.lpu_id_cmp=eps.lpu_id
and cmp.person_id_cmp=eps.person_id
and cmp.date_cmp<=eps.evnps_disdate
and eps.PrehospArrive_Code in (2,4) --скорая помощь, санавиация
order by cmp.date_cmp desc limit 1

)cmpp on true
order by eps.person_fio
