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 bigint)  as p_lpusectionprofile,
cast($6 as bigint)  as p_medstafffactstac,
cast($7 as bigint)  as p_sex,
cast($8 as bigint)  as p_begpersonage,
cast($9 as bigint)  as p_endpersonage,
cast($10 as bigint)  as p_klcountry,
cast($11 as bigint)  as p_paytype,
cast($12 as bigint) as p_socstatus,
dbo.GetDiagCodeByID(cast($13 as int8)) as p_begdiag, 
dbo.GetDiagCodeByID(cast($14 as int8)) as p_enddiag, 
cast($15 as bigint)  as p_lpunittype,
cast($16 as bigint) as p_prehosptype,
cast($17 as bigint) as p_paramincludedopdisp,
cast($18 as int) as p_yesno,
cast(to_timestamp($19,'dd.mm.yyyy') as timestamp) as p_begdate, 
cast(to_timestamp($20,'dd.mm.yyyy') as timestamp) as p_enddate,
cast($21 as int) as p_onlypersoncard
) ,
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
 DENSE_RANK() OVER(
 ORDER BY rtrim(COALESCE(pdz.person_surname, '') || ' ' || COALESCE(pdz.Person_firname, '') || ' ' || COALESCE(pdz.person_secname, ''))
 , evns.evnsection_setdate, evns.evnsection_disdate, evns.evnsection_id) as "Record_Num",
 row_number() OVER(PARTITION BY evns.evnsection_id  ORDER BY uslug.UslugaComplex_Code) as "Record",
 evns.evnsection_id as EvnSection_id, 
 eps.evnps_numcard, 
 to_char(evns.evnsection_setdate, 'DD.MM.YYYY') as "EvnPS_setDate",
 to_char(evns.evnsection_disdate, 'DD.MM.YYYY') as "EvnPS_disDate",
 adrp.address_address as "PAddress",
 adru.address_address as "UAddress",
 pol.Polis_SerNum as "Polis_SerNum",
 Terr.omssprterr_name as "Polis_Terr",
 case when pdz.sex_id=1 then 'M' else 'Ж' end as sex_name,
 dbo.ageYMD( pdz.person_birthday, evns.evnsection_disdate, 1) as age,
 LpuPC.Lpu_Nick as "Personcard_LPU",
 coun.KLCountry_name as "KLCountry_name",
 dt.documenttype_name as "DocumentType_Name",
 soc.socstatus_name as "SocStatus_Name",
 lp.lpu_nick as "Lpu_Nick",
 ls.lpusection_fullname as "LpuSection_FullName",
 coalesce(lpr.lpusectionprofile_code,'')||' '||coalesce(lpr.lpusectionprofile_name,'') as "LpuSectionProfile_name",
 case when eps.PrehospArrive_id = 2 then '+' end as "PrehospArrive_quick",
 case when eps.PrehospArrive_id = 1 then '+' end as "PrehospArrive_sam",
 diag.Diag_FullName as "Diag_Name",              
 pht.PrehospType_Name as "PrehospType_Name",
 lt.LeaveType_Name as "ResultDesease_Name",
 Refuse.PrehospWaifRefuseCause_Name as "RefuseCause_Name",
 COALESCE(pdz.person_surname, '') || ' ' || COALESCE(pdz.Person_firname, '') || ' ' || COALESCE(pdz.person_secname, '') as "Person_Fio",
 to_char(pdz.person_birthday, 'DD.MM.YYYY') as "Person_Birthday",
  case when evns.EvnSection_disDate is not null
					then
						case
							when LUT1.LpuUnitType_Code = 2 and date_part('day', evns.EvnSection_disDate - evns.EvnSection_setDate) + 1 > 1
							then date_part('day', evns.EvnSection_disDate - evns.EvnSection_setDate)
							else date_part('day', evns.EvnSection_disDate - evns.EvnSection_setDate) + 1
						end - coalesce(evns.EvnSection_Absence, 0)
					else null
				 end as "PayKoikoDni",
 uslug.UslugaComplex_Code as "UslugaComplex_Code",
 uslug.UslugaComplex_Name as "UslugaComplex_Name",
  case when eu.EvnUsluga_Kolvo=0 then 1 else eu.EvnUsluga_Kolvo end as "EvnUsluga_Kolvo",
 pt.paytype_name as "PayType_Name"
 from Lpu_W lp
 inner join v_evnps eps on eps.lpu_id=lp.lpu_id 
 and eps.EvnPS_setDate <= p_enddate and COALESCE(case when eps.PrehospWaifRefuseCause_id >0 then eps.EvnPS_OutcomeDT else eps.evnps_disdate end, p_begdate) >= p_begdate 
 and eps.person_age between COALESCE(p_begpersonage, 0) and COALESCE(p_endpersonage, 200) 
 and (eps.prehosptype_id = p_prehosptype or p_prehosptype is null)
 and COALESCE(eps.EvnPS_IsArchive,1)=1
 inner join v_evnsection evns on evns.evnsection_pid=eps.evnps_id 
 and evns.evnsection_disdate between p_begdate and p_enddate 
 and (COALESCE(evns.EvnSection_IsPriem, 1) = 1 or eps.PrehospWaifRefuseCause_id > 0) 
 and (evns.MedPersonal_id = p_medstafffactstac or p_medstafffactstac is null) 
 and (evns.LpuSection_id = p_lpusection or p_lpusection is null)
 and (evns.lpusectionprofile_id=p_lpusectionprofile or p_lpusectionprofile is null)
 and (evns.PayType_id = p_paytype or p_paytype is null)
 left join v_LpuSection LS on LS.lpusection_id = evns.lpusection_id
 left join v_lpusectionprofile lpr on lpr.lpusectionprofile_id=evns.lpusectionprofile_id
 inner join v_PersonState pdz on pdz.Person_id = eps.person_id
 and (pdz.sex_id = p_sex or p_sex is null)
 and (pdz.socstatus_id = p_socstatus or p_socstatus is null)
 left join lateral (select * from v_PersonStateAll pa 
 where pa.Person_id = eps.Person_id
 and cast (pa.PersonEvn_begDT as date) <=evns.EvnSection_disDate
 order by pa.PersonEvn_begDT desc limit 1
 ) pa on true
 left join v_NationalityStatus ns on ns.NationalityStatus_id=pa.NationalityStatus_id
 left join v_klcountry coun on coun.klcountry_id = COALESCE(ns.klcountry_id,643)
 --к/дн
 left join v_LpuSection LS1 on LS1.LpuSection_id = evns.LpuSection_id
 left join v_LpuUnit LU1 on LU1.LpuUnit_id = LS1.LpuUnit_id
 left join v_LpuUnitType LUT1 on LUT1.LpuUnitType_id = LU1.LpuUnitType_id
 left join v_address adru on adru.Address_id = pdz.uaddress_id
 left join v_address adrp on adrp.address_id = pdz.paddress_id
 left join v_Diag Diag on Diag.Diag_id = evns.Diag_id
 left join document doc on doc.Document_id = pdz.Document_id
 left join DocumentType as dt on doc.DocumentType_id = dt.DocumentType_id
 LEFT JOIN LATERAL (
 select 
 pcd.PersonCard_id,
 pcd.LpuRegion_id,
 pcd.Lpu_id
 from v_PersonCard_all pcd
 where pcd.Person_id = pdz.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 lateral (
 select Pol.Polis_id,Pol.Polis_begDate,
 rtrim(COALESCE(Pol.Polis_Ser, '')) || rtrim(' ' || COALESCE(Pol.Polis_Num, '')) as Polis_SerNum,
 Pol.OmsSprTerr_id
 from v_PersonPolis Pol
 where Pol.Person_id = eps.Person_id 
 and (cast (Pol.Polis_begDate as date) <= case when dbo.ageYMD(pdz.person_birthday, evns.evnsection_disdate, 2)<1 and dbo.ageYMD(Pol.Polis_begDate, evns.evnsection_disdate, 2)<2
 and coun.klcountry_id=643 then cast (dateadd('month', 2, pdz.Person_BirthDay) as date) 
 else evns.EvnSection_disDate end
 and (cast (Pol.Polis_endDate as date) >= case when dbo.ageYMD(pdz.person_birthday, evns.evnsection_disdate, 2)<1 and dbo.ageYMD(Pol.Polis_begDate, evns.evnsection_disdate, 2)<2
 and coun.klcountry_id=643 then cast (dateadd('month', 2, pdz.Person_BirthDay) as date) 
 else evns.EvnSection_disDate end or Pol.Polis_endDate is null))                                    
 order by Pol.Polis_begDate desc limit 1
 ) Pol on true
 left join lateral (
  select 
  euc.UslugaComplex_id,
  SUM(euc.EvnUsluga_Kolvo) as EvnUsluga_Kolvo
  from v_EvnUsluga euc
  where euc.EvnUsluga_pid in (evns.EvnSection_id)
  and euc.EvnUsluga_Kolvo is not null and euc.EvnUsluga_setDate IS NOT null 
  group by euc.UslugaComplex_id
  ) EU on true
  left join v_UslugaComplex uslug on uslug.UslugaComplex_id=EU.UslugaComplex_id
 left join v_Lpu LpuPC on LpuPC.Lpu_id = PersonCard.Lpu_id
 left join v_socstatus soc on soc.socstatus_id=pdz.socstatus_id
 left join v_PrehospType pht on pht.PrehospType_id = eps.PrehospType_id
 left join LeaveType lt0  on lt0.LeaveType_id = evns.LeaveType_id
 left join fed.LeaveType LT on LT.LeaveType_id = COALESCE(evns.LeaveType_fedid, lt0.LeaveType_fedid)
 left join v_PrehospWaifRefuseCause Refuse on Refuse.PrehospWaifRefuseCause_id = eps.PrehospWaifRefuseCause_id
 left join v_OMSSprTerr Terr on Terr.OMSSprTerr_id = Pol.OmsSprTerr_id
 left join v_PayType pt on pt.PayType_id = evns.PayType_id
 where  (ns.KLCountry_id = p_klcountry or p_klcountry is null)
 and case when (coun.klcountry_id=643 or (coun.klcountry_id!=643 and p_yesno=2)) then 1 else 2 end = case when (coun.klcountry_id=643 or (coun.klcountry_id!=643 and p_yesno=2)) and Pol.Polis_id is null 
																								 then 1 else 2 end
 and  1 = case when p_paramincludedopdisp=2 and coun.klcountry_id=643 then 1 
          when p_paramincludedopdisp=1 and coun.klcountry_id!=643  then 1 
		  when p_paramincludedopdisp is null then 1 end
 and (Diag.Diag_Code >= p_begdiag or p_begdiag is null) 
 and (Diag.Diag_Code <= p_enddiag or p_enddiag is null)
 and (case when PersonCard.PersonCard_id is not null then 2  else 1 end  >= p_onlypersoncard or p_onlypersoncard is null)
 and (LS.LpuUnit_id = p_lpuunit or p_lpuunit is null)
 and (LS.LpuBuilding_id = p_lpubuilding or p_lpubuilding is null)
 and (LU1.LpuUnitType_id = cast(p_lpunittype as bigint) or cast( p_lpunittype as bigint) is null)
 ) ,
 --Отказы без движений
 EvnPS_W_noevnsection as (
 select 
  DENSE_RANK() OVER(
 ORDER BY rtrim(COALESCE(pdz.person_surname, '') || ' ' || COALESCE(pdz.Person_firname, '') || ' ' || COALESCE(pdz.person_secname, ''))
 ,eps.evnps_setdate, eps.evnps_disdate, eps.evnps_id) as "Record_Num",
 1 as "Record",
 eps.evnps_id as EvnSection_id, 
 eps.evnps_numcard,
 to_char(eps.evnps_setdate, 'DD.MM.YYYY') as "EvnPS_setDate",
 to_char(eps.evnps_disdate, 'DD.MM.YYYY') as "EvnPS_disDate",
 adrp.address_address as "PAddress",
 adru.address_address as "UAddress",
 pol.Polis_SerNum as "Polis_SerNum",
 Terr.omssprterr_name as "Polis_Terr",
 case when pdz.sex_id=1 then 'M' else 'Ж' end as sex_name,
 dbo.ageYMD( pdz.person_birthday, eps.evnps_disdate, 1) as age,
 LpuPC.Lpu_Nick as "Personcard_LPU",
 coun.KLCountry_name as "KLCountry_name",
 dt.documenttype_name as "DocumentType_Name",
 soc.socstatus_name as "SocStatus_Name",
 lp.lpu_nick as "Lpu_Nick",
 ls.lpusection_fullname as "LpuSection_FullName",
 coalesce(lpr.lpusectionprofile_code,'')||' '||coalesce(lpr.lpusectionprofile_name,'') as "LpuSectionProfile_name",
 case when eps.PrehospArrive_id = 2 then '+' end as "PrehospArrive_quick",
 case when eps.PrehospArrive_id = 1 then '+' end as "PrehospArrive_sam",
 diag.Diag_FullName as "Diag_Name",               
 pht.PrehospType_Name as "PrehospType_Name",
 lt.LeaveType_Name as "ResultDesease_Name",
 Refuse.PrehospWaifRefuseCause_Name as "RefuseCause_Name",
 COALESCE(pdz.person_surname, '') || ' ' || COALESCE(pdz.Person_firname, '') || ' ' || COALESCE(pdz.person_secname, '') as "Person_Fio",
 to_char(pdz.person_birthday, 'DD.MM.YYYY') as "Person_Birthday",
 cast(null as bigint)  as "PayKoikoDni",
 null as "UslugaComplex_Code",
 null as "UslugaComplex_Name",
 cast(null as bigint)  as "EvnUsluga_Kolvo",
 pt.paytype_name as "PayType_Name"
 from Lpu_W lp
 inner join v_evnps eps on eps.lpu_id=lp.lpu_id 
 and eps.EvnPS_setDate <= p_enddate and COALESCE(case when eps.PrehospWaifRefuseCause_id >0 then eps.EvnPS_OutcomeDT else eps.evnps_disdate end, p_begdate) >= p_begdate 
 and eps.person_age between COALESCE(p_begpersonage, 0) and COALESCE(p_endpersonage, 200) 
 and (eps.prehosptype_id = p_prehosptype or p_prehosptype is null)
 and (eps.medpersonal_pid= p_medstafffactstac or p_medstafffactstac is null) 
 and (eps.LpuSection_id = p_lpusection or p_lpusection is null)
 and (eps.PayType_id = p_paytype or p_paytype is null)
 left join v_LpuSection LS on LS.lpusection_id = eps.lpusection_id
 left join v_lpusectionprofile lpr on lpr.lpusectionprofile_id=ls.lpusectionprofile_id
 left join v_LpuUnit LU1 on LU1.LpuUnit_id = LS.LpuUnit_id
 left join v_LpuUnitType LUT1 on LUT1.LpuUnitType_id = LU1.LpuUnitType_id
 inner join v_PersonState pdz on pdz.Person_id = eps.person_id
 and (pdz.sex_id = p_sex or p_sex is null)
 and (pdz.socstatus_id = p_socstatus or p_socstatus is null)
 left join lateral (select * from v_PersonStateAll pa 
 where pa.Person_id = eps.Person_id
 and cast (pa.PersonEvn_begDT as date) <=eps.Evnps_disDate
 order by pa.PersonEvn_begDT desc limit 1
 ) pa on true
 left join v_NationalityStatus ns on ns.NationalityStatus_id=pa.NationalityStatus_id
 left join v_klcountry coun on coun.klcountry_id = COALESCE(ns.klcountry_id,643)
 left join v_address adru on adru.Address_id = pdz.uaddress_id
 left join v_address adrp on adrp.address_id = pdz.paddress_id
 left join v_Diag Diag on Diag.Diag_id = eps.Diag_id
 left join document doc on doc.Document_id = pdz.Document_id
 left join DocumentType as dt on doc.DocumentType_id = dt.DocumentType_id
  LEFT JOIN LATERAL (
 select 
 pcd.PersonCard_id,
 pcd.LpuRegion_id,
 pcd.Lpu_id
 from v_PersonCard_all pcd
 where pcd.Person_id = pdz.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 lateral (
 select Pol.Polis_id,Pol.Polis_begDate,
 rtrim(COALESCE(Pol.Polis_Ser, '')) || rtrim(' ' || COALESCE(Pol.Polis_Num, '')) as Polis_SerNum,
 Pol.OmsSprTerr_id
 from v_PersonPolis Pol
 where Pol.Person_id = eps.Person_id 
 and (cast (Pol.Polis_begDate as date) <= case when dbo.ageYMD(pdz.person_birthday, eps.Evnps_disDate, 2)<1 and dbo.ageYMD(Pol.Polis_begDate, eps.Evnps_disDate, 2)<2
 and coun.klcountry_id=643 then cast (dateadd('month', 2, pdz.Person_BirthDay) as date) 
 else eps.Evnps_disDate end
 and (cast (Pol.Polis_endDate as date) >= case when dbo.ageYMD(pdz.person_birthday, eps.Evnps_disDate, 2)<1 and dbo.ageYMD(Pol.Polis_begDate, eps.Evnps_disDate, 2)<2
 and coun.klcountry_id=643 then cast (dateadd('month', 2, pdz.Person_BirthDay) as date) 
 else eps.Evnps_disDate end or Pol.Polis_endDate is null))                                    
 order by Pol.Polis_begDate desc limit 1
 ) Pol on true
 left join v_Lpu LpuPC on LpuPC.Lpu_id = PersonCard.Lpu_id
 left join v_socstatus soc on soc.socstatus_id=pdz.socstatus_id
 left join v_PrehospType pht on pht.PrehospType_id = eps.PrehospType_id
 left join LeaveType lt0  on lt0.LeaveType_id = eps.LeaveType_id
 left join fed.LeaveType LT on LT.LeaveType_id = COALESCE(eps.LeaveType_id, lt0.LeaveType_fedid)
 left join v_PrehospWaifRefuseCause Refuse on Refuse.PrehospWaifRefuseCause_id = eps.PrehospWaifRefuseCause_id
 left join v_OMSSprTerr Terr on Terr.OMSSprTerr_id = Pol.OmsSprTerr_id
 left join v_PayType pt on pt.PayType_id = eps.PayType_id
 where  (ns.KLCountry_id = p_klcountry or p_klcountry is null)
 and case when (coun.klcountry_id=643 or (coun.klcountry_id!=643 and p_yesno=2)) then 1 else 2 end = case when (coun.klcountry_id=643 or (coun.klcountry_id!=643 and p_yesno=2)) and Pol.Polis_id is null 
																								 then 1 else 2 end
 and  1 = case when p_paramincludedopdisp=2 and coun.klcountry_id=643 then 1 
          when p_paramincludedopdisp=1 and coun.klcountry_id!=643  then 1 
		  when p_paramincludedopdisp is null then 1 end
 and (Diag.Diag_Code >= p_begdiag or p_begdiag is null) 
 and (Diag.Diag_Code <= p_enddiag or p_enddiag is null)
 and (case when PersonCard.PersonCard_id is not null then 2  else 1 end  >= p_onlypersoncard or p_onlypersoncard is null)
 and (LS.LpuUnit_id = p_lpuunit or p_lpuunit is null)
 and (LS.LpuBuilding_id = p_lpubuilding or p_lpubuilding is null)
 and (LS.LpuSectionProfile_id = p_lpusectionprofile or p_lpusectionprofile is null)
 and (LU1.LpuUnitType_id = cast(p_lpunittype as bigint) or cast(p_lpunittype as bigint) is null)
 and eps.PrehospWaifRefuseCause_id >0
 and not exists (select es.evnsection_id from v_evnsection es
 where es.evnsection_pid=eps.evnps_id) 
 ),
 evnps_all as (
 select * from  EvnPS_W_noevnsection
 union all 
 select * from  EvnPS_W
 )
 select  e.*,
 reestr.RegistryData_ItogSum
 from evnps_all e
 left join lateral (select
 sum(rd.registrydataevnps_itogsum) as RegistryData_ItogSum
 from r50.RegistryDataEvnPS rd
 inner join r50.registry r on r.registry_id=rd.Registry_id
 and COALESCE(r.Registry_deleted,1)=1
 where rd.Evn_id=e.EvnSection_id
  and COALESCE(rd.RegistryDataEvnps_deleted,1)=1
 )reestr on true
 order by 1
