with params as (
	select 
		cast($1 as bigint) as p_lpu_id, 
		cast($2 as varchar) as p_lpufiliallist, 
		cast($3 as bigint) as p_lpubuilding_id, 
		cast($4 as bigint) as p_lpuunit_id, 
		cast($5 as bigint) as p_lpusection_id, 
		cast($6 as bigint) as p_lpusectionbedprofile_id, 
		cast($7 as bigint) as p_medpersonal_id, 
		cast($8 as bigint) as p_paytype_id, 
		cast($9 as bigint) as p_evnps_iscont, 
		cast($10 as bigint) as p_prehosparrive_id, 
		cast($11 as bigint) as p_prehosptype_id, 
		cast($12 as bigint) as p_lpuunittype_id, 
		cast($13 as bigint) as p_lpu_did, 
		cast($14 as bigint) as p_onlypersoncard_yn, 
		CAST(TO_TIMESTAMP($15,'DD.MM.YYYY') AS TIMESTAMP) as p_begdate, 
		CAST(TO_TIMESTAMP($16,'DD.MM.YYYY') AS TIMESTAMP) as p_enddate,
		cast($17 as bigint) as p_order413, 
		cast($18 as time) as p_begtime,
		cast($19 as bigint) as p_ymd_id, 
		cast($20 as bigint) as p_beg_age, 
		cast($21 as bigint) as p_end_age, 
		cast($22 as bigint) as p_sex_id, 
		dbo.GetDiagCodeByID(cast($23 as bigint)) as p_begdiag_code,
		dbo.GetDiagCodeByID(cast($24 as bigint)) as p_enddiag_code,
		cast($25 as bigint) as p_nationality
),
	
Lpu_W as (
  select 
    L.Lpu_id,
	L.Lpu_Nick,
    coalesce(LpuPr.Lpu_id, L.Lpu_id) as Lpu_pid,
	dbo.GetRegion() as Region_id
  from dbo.v_Lpu L
  inner join params p on 1 = 1 
    LEFT join LATERAL
  (
    select
      LpuPr.Lpu_id
    from dbo.Lpu LpuPr
    where
      LpuPr.Lpu_id = L.Lpu_pid
      and LpuPr.Lpu_begDate < (p.p_endDate + interval '1 day')
    limit 1
  ) LpuPr on TRUE
  where (L.Lpu_id = p_lpu_id 
	  or (p.p_lpu_id is null or p.p_onlypersoncard_yn = 2) and coalesce(L.Lpu_IsTest, 1) = 1
	  or Lpu_pid = p.p_lpu_id and p.p_lpu_id > 0)
    and L.Lpu_begDate <= (p.p_endDate + interval '1 year')
    and (L.Lpu_endDate >= p.p_begdate or L.Lpu_endDate is null) 	           
),

LpuSection_W as 
(
  select 
    LS.Lpu_id,
    LS.LpuSection_id,
    LS.LpuSection_Name,
    LS.LpuSection_Code,
	LU.LpuUnitType_id
  from Lpu_W as L
  inner join params p on 1 = 1 
  inner join dbo.v_LpuSection LS on LS.Lpu_id = L.Lpu_id
    and (LS.LpuBuilding_id = p.p_lpubuilding_id or p.p_lpubuilding_id is null or p.p_onlypersoncard_yn = 2)
	and (LS.LpuUnit_id = p.p_lpuunit_id or p.p_lpuUnit_id is null or p.p_onlypersoncard_yn = 2)
	and (LS.LpuSection_id = p.p_lpusection_id or p.p_lpusection_id is null or p.p_onlypersoncard_yn = 2)
  inner join v_LpuUnit LU on LS.LpuUnit_id = LU.LpuUnit_id
    and (LU.LpuUnitType_id = p.p_lpuunittype_id or p.p_lpuunittype_id is null)
  inner join dbo.v_LpuBuilding LB on LB.LpuBuilding_id = LS.LpuBuilding_id
    and (',' || p.p_lpufiliallist || ',' like '%,' || cast(LB.LpuFilial_id as varchar(30)) || ',%' or p.p_lpufiliallist is null or p.p_onlypersoncard_yn = 2)
)


select 
	Koikodni.Lpu_Nick as "Lpu_Nick",
	Koikodni.LpuSection_Code as "LpuSection_Code",
	Koikodni.LpuSection_Name as "LpuSection_Name",
	Koikodni.LpuSectionBedProfile_Name as "LpuSectionBedProfile_Name",
	LUType.LpuUnitType_id as "LpuUnitType_id",
	LUType.LpuUnitType_Name as "LpuUnitType_Name",
	Koikodni.Diag_Code as "Diag_Code", 
	Koikodni.Diag_Name as "Diag_Name",
	
	count(Koikodni.EvnSection_id) as "EvnSection_count",
	sum(EvnSection_koikodni) as "EvnSection_koikodni",
	sum(case when Koikodni.LpuUnitType_id in (6,9) then EvnSection_koikodni - hday_count end)::bigint as "EvnSection_dnstac_koikodni",
	
	sum(EvnSection_koikodni) / count(Koikodni.EvnSection_id) as "sred_dlit",
	
	count (case when  LeaveType_SysNick in ('leave','ksleave','dsleave') then Koikodni.EvnSection_id end) as "EvnLeave_count",
	sum(case when LeaveType_SysNick in ('leave','ksleave','dsleave') then EvnSection_koikodni end) as "EvnLeave_koikodni",
	
	count (case when LeaveType.LeaveType_SysNick in ('section','ksper','dsper') then Koikodni.EvnSection_id end) as "EvnSectionNext_count",
	sum (case when LeaveType.LeaveType_SysNick in ('section','ksper','dsper') then EvnSection_koikodni end) as "EvnSectionNext_koikodni",
	
	count (case when LeaveType_SysNick in ('other','ksother','dsother') then Koikodni.EvnSection_id end) as "EvnOtherLpu_count",
	sum(case when  LeaveType_SysNick in ('other','ksother','dsother') then EvnSection_koikodni end ) as "EvnOtherLpu_koikodni",
	
	count (case when LeaveType_SysNick in('stac','ksstac','dsstac' ) then Koikodni.EvnSection_id end) as "EvnOtherStac_count",
	sum(case when  LeaveType_SysNick in('stac','ksstac','dsstac' ) then EvnSection_koikodni end) as "EvnOtherStac_koikodni",
	
	count (case when LeaveType_SysNick in('die','ksdie','ksdiepp','dsdie','dsdiepp' )then Koikodni.EvnSection_id end) as "EvnDie_count",
	sum(case when LeaveType_SysNick in('die','ksdie','ksdiepp','dsdie','dsdiepp' ) then EvnSection_koikodni end) as "EvnDie_koikodni",
	
	count (case when LeaveType_SysNick not in ('leave','ksleave','dsleave','section','ksper','dsper','other','ksother','dsother','stac','ksstac','dsstac','die','ksdie','ksdiepp','dsdie','dsdiepp' ) 
		then Koikodni.EvnSection_id end) as "EvnAnyOther_count",
	sum(case when LeaveType_SysNick not in ('leave','ksleave','dsleave','section','ksper','dsper','other','ksother','dsother','stac','ksstac','dsstac','die','ksdie','ksdiepp','dsdie','dsdiepp' ) 
		then EvnSection_koikodni end) as "EvnAnyOther_koikodni"

from 
(select
Lpu.Lpu_Nick,
LpuSection.LpuSection_Code,
LpuSection.LpuSection_Name,
BedProfile.LpuSectionBedProfile_Name,
LpuSection.LpuUnitType_id,
Diag.Diag_Code,
Diag.Diag_Name,
EvnSection.EvnSection_id,
EvnSection.LeaveType_id,
rpt.k_day_count_pg(LpuSection.LpuUnitType_id,EvnSection.EvnSection_setDate, EvnSection.EvnSection_disDate) as EvnSection_koikodni,
holidays.hday_count

 from Lpu_W Lpu
 inner join params p on 1 = 1 
 inner join v_EvnPS EvnPS on EvnPS.Lpu_id = Lpu.Lpu_id
 inner join v_EvnSection EvnSection on (EvnSection.EvnSection_pid = EvnPS.EvnPS_id)
	and EvnSection_IsPriem is null
	and EvnSection.EvnSection_disDT between dateadd('day', (1 - coalesce(p_Order413, 2)), p.p_begdate) + p.p_begtime
										and dateadd('day', (2 - coalesce(p_Order413, 2)), p.p_enddate) + p.p_begtime --#PROMEDWEB-76502
inner join lateral (select *
    from v_PersonState Person 
    inner join params p on 1 = 1 
	where Person.Person_id = EvnPS.Person_id
	and (dbo.ageymd(Person.Person_BirthDay::timestamp,EvnPS.EvnPS_setDate::timestamp,coalesce(p.p_ymd_id,1)::int) between coalesce(p.p_beg_age,0) and coalesce(p.p_end_age,54750)) --#91834
	and (Person.Sex_id = p.p_sex_id or p.p_sex_id is null)
	limit 1
   ) Person on true
inner join LpuSection_W LpuSection on (LpuSection.LpuSection_id = EvnSection.LpuSection_id)
inner join v_MedPersonal MedPersonal on MedPersonal.MedPersonal_id=EvnSection.MedPersonal_id
and MedPersonal.Lpu_id=EvnSection.Lpu_id and MedPersonal.Lpu_id = EvnSection.Lpu_id
 and (EvnSection.Medpersonal_id=p.p_medpersonal_id or p.p_medpersonal_id is null)
left join lateral
 (
  select PersonCard_id, PersonCard_code,Lpu_id from v_PersonCard_all as PersonCard
  where PersonCard.Person_id=EvnPS.Person_id
    and ((PersonCard.personcard_begDate<=EvnPS.evnps_setdate) and (PersonCard.personcard_endDate >=EvnPS.evnps_setdate 
		or PersonCard.personcard_endDate is null))
        and (LpuAttachType_id = 1)
        order by PersonCard.personcard_begDate
   limit 1
 ) Personcard on true

 left join v_Diag Diag on Diag.Diag_id =coalesce(EvnPS.Diag_aid,EvnSection.Diag_id)
 left join fed.LpuSectionBedProfileLink  LSBPL on LSBPL.LpuSectionBedProfileLink_id=EvnSection.LpuSectionBedProfileLink_fedid 
 left join v_LpuSectionBedProfile BedProfile on  BedProfile.LpuSectionBedProfile_id=coalesce(EvnSection.LpuSectionBedProfile_id, LSBPL.LpuSectionBedProfile_id)
 left join lateral 
  (
			select LS.LpuSection_FullName, ES.EvnSection_id, LeaveCause.LeaveCause_SysNick
			From  v_EvnSection ES 
			inner join v_LpuSection LS on LS.LpuSection_id=ES.LpuSection_id
			left join v_EvnLeaveBase EvnLeaveBase on EvnLeaveBase.EvnLeaveBase_pid = ES.EvnSection_id
				and (EvnLeaveBase.EvnLeaveBase_Count-1 = EvnLeaveBase.EvnLeaveBase_index)
			left join v_LeaveCause LeaveCause on EvnLeaveBase.LeaveCause_id = LeaveCause.LeaveCause_id
			where es.EvnSection_pid = EvnPS.EvnPS_id
				and ES.EvnSection_setDt < EvnSection.EvnSection_setDt
				and coalesce(ES.EvnSection_IsPriem,1) ! =2
			order by ES.EvnSection_setDt desc
			limit 1
			)ES_pred on true
 left join lateral 
  (
			select EvnPS.EvnPS_id,EvnPS.EvnPS_NumCard,LeaveCause.LeaveCause_SysNick
			From  v_EvnPS EPS 
			inner join v_LeaveType LT on EPS.LeaveType_id=LT.LeaveType_id
			inner join fed.LeaveType flt on flt.LeaveType_id=LT.LeaveType_fedid
			inner join v_EvnSection es_last on es_last.EvnSection_pid=EPS.EvnPS_id
				and es_last.EvnSection_Index=es_last.EvnSection_Count - 1
			left join v_EvnLeaveBase EvnLeaveBase on EvnLeaveBase.EvnLeaveBase_pid = es_last.EvnSection_id
				and (EvnLeaveBase.EvnLeaveBase_Count-1=EvnLeaveBase.EvnLeaveBase_index)
			left join v_LeaveCause LeaveCause on EvnLeaveBase.LeaveCause_id = LeaveCause.LeaveCause_id
			where EvnPS.Person_id = EPS.Person_id
			and EPS.EvnPS_disDate = EvnPS.EvnPS_setDate
			and EvnPS.EvnPS_IsCont = 2
			and flt.LeaveType_Code in ('204','104')
			limit 1
			)EvnPS_pred on true
 left join dbo.Nationalitystatus as NS on NS.Nationalitystatus_id = person.Nationalitystatus_id
 left join dbo.v_klcountry as country on country.KLCountry_id = NS.KLCountry_id 
 left join lateral
	(
	select
		COUNT(*) as hday_count 
	from Calendar
	where Calendar_Date between EvnSection.EvnSection_setDate and EvnSection.EvnSection_disDate
		and Calendar_IsHoliday=2
	) holidays on true

where 1=1 
  and (Personcard.Lpu_id = p.p_lpu_did and p.p_lpu_did>0 and p.p_onlypersoncard_yn = 1
    or p.p_lpu_did is null and p.p_onlypersoncard_yn = 1
	or Personcard.Lpu_id = p.p_lpu_id and p.p_onlypersoncard_yn = 2)
  --and (EvnPS.PrehospType_id=p_prehosptype_id or p_prehosptype_id is null)
  and (EvnPS.EvnPS_IsCont=1 and ES_pred.EvnSection_id  is null and EvnPS.PrehospType_id=p.p_prehosptype_id
	or EvnPS.EvnPS_IsCont=2 and ES_pred.EvnSection_id is null and case EvnPS_pred.LeaveCause_SysNick when 'plan' then 2 when 'extr' then 1 else 2 end = p.p_prehosptype_id
	or ES_pred.EvnSection_id is not null and case ES_pred.LeaveCause_SysNick when 'plan' then 2 when 'extr' then 1 else 2 end = p.p_prehosptype_id
	or p.p_prehosptype_id is null)
  and (EvnPS.PayType_id=p.p_paytype_id or p.p_paytype_id is null)
  and (Diag.Diag_Code >= p.p_begdiag_code or p.p_begdiag_code is null)
  and (Diag.Diag_Code <= p.p_enddiag_code or p.p_enddiag_code is null)
  and (p.p_lpusectionbedprofile_id=BedProfile.LpuSectionBedProfile_id or p.p_lpusectionbedprofile_id is null)
  and (p.p_evnps_iscont is null  or 
	p.p_evnps_iscont = 1 and EvnPS.EvnPS_IsCont=1 and ES_pred.EvnSection_id  is null or 
	p.p_evnps_iscont = 2 and not (EvnPS.EvnPS_IsCont=1 and ES_pred.EvnSection_id is null)
  and (EvnPS.PrehospArrive_id = p.p_prehosparrive_id and EvnPS.EvnPS_IsCont=1 and ES_pred.EvnSection_id is null
    or p.p_prehosparrive_id is null)
  and (((country.KLCountry_id = 643 or country.KLCountry_id is null or NS.NationalityStatus_IsTwoNation = 2) and p.p_nationality = 1) 
		or (coalesce(country.KLCountry_id, -1) not in (643) and p.p_nationality = 2 and NS.NationalityStatus_IsTwoNation = 1) 
		or p.p_nationality is null) --гражданство
	)
) Koikodni 
left join LeaveType on LeaveType.LeaveType_id=Koikodni.LeaveType_id
left join v_LpuUnitType LUType on Koikodni.LpuUnitType_id = LUType.LpuUnitType_id

group by
	Koikodni.Lpu_Nick,
	Koikodni.LpuSection_Code,
	Koikodni.LpuSection_Name,
	Koikodni.LpuSectionBedProfile_Name,
	LUType.LpuUnitType_id,
	LUType.LpuUnitType_Name,
	Koikodni.Diag_Code, 
	Koikodni.Diag_Name

order by 
	Koikodni.Diag_Code
