--han_Diag_LeaveType_s_pg.rptdesign
--select * from rpt.han_diag_leavetype_s_pg(
with param 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_lpusectionprofile_id,
		cast($7 as bigint) as p_lpusectionbedprofile_id,
		cast($8 as bigint) as p_medpersonal_id,
		cast($9 as bigint) as p_paytype_id,
		cast($10 as bigint) as p_prehosptype_id,
		cast($11 as bigint) as p_prehosparrive_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 time) as p_begtime,
		cast($18 as bigint) as p_order413,
		cast($19 as int) 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_national
),

Lpu_W as 
(
  select 
    L.Lpu_id,
    coalesce(LpuPr.Lpu_id, L.Lpu_id) as Lpu_pid,
	dbo.GetRegion() as Region_id,
	dateadd('day', (1 - coalesce(p_Order413, 2)), p_begdate) + p_begtime as p_begdate_s,
	dateadd('day', (2 - coalesce(p_Order413, 2)), p_enddate) + p_begtime as p_enddate_s,
	p.*
  from param p
  inner join dbo.v_Lpu L on true
    left join lateral
  (
    select
      LpuPr.Lpu_id
    from dbo.Lpu LpuPr
    where
      LpuPr.Lpu_id = L.Lpu_pid
      and LpuPr.Lpu_begDate < (p_enddate + interval '1 day')
    limit 1
  ) LpuPr on true
  where (L.Lpu_id = p_lpu_id 
	  or (p_lpu_id is null or p_onlypersoncard_yn = 2) and coalesce(L.Lpu_IsTest, 1) = 1
	  or Lpu_pid = p_lpu_id and p_lpu_id > 0)
    and L.Lpu_begDate <= (p_enddate + interval '1 year')
    and (L.Lpu_endDate >= 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_SysNick
  from Lpu_W as L
  inner join dbo.v_LpuSection LS on LS.Lpu_id = L.Lpu_id
    and (LS.LpuBuilding_id = p_lpubuilding_id or p_lpubuilding_id is null or p_onlypersoncard_yn = 2)
	and (LS.LpuUnit_id = p_lpuunit_id or p_lpuunit_id is null or p_onlypersoncard_yn = 2)
	and (LS.LpuSection_id = p_lpusection_id or p_lpusection_id is null or p_onlypersoncard_yn = 2)
  inner join v_LpuUnit LU on LS.LpuUnit_id = LU.LpuUnit_id
    and (LU.lpuunittype_id = p_lpuunittype_id or p_lpuunittype_id is null)
  inner join dbo.v_LpuBuilding LB on LB.LpuBuilding_id = LS.LpuBuilding_id
    and (',' || p_lpufiliallist || ',' like '%,' || cast(LB.LpuFilial_id as varchar(30)) || ',%' or p_lpufiliallist is null or p_onlypersoncard_yn = 2)
)

select 

distinct Koikodni.Diag_Code as "Diag_Code", Koikodni.Diag_Name as "Diag_Name",

count(distinct Koikodni.EvnPS_id) as "EvnDiagPS_count",

coalesce(sum(EvnSection_koikodni),0) as "EvnDiagPS_koikodni",

count (distinct case when  LeaveType_SysNick in ('leave','ksleave','dsleave') then Koikodni.EvnPS_id end) as "EvnLeave_count",--81877 добавлены коды для карелии

coalesce(sum(case when LeaveType_SysNick in ('leave','ksleave','dsleave')
		 then EvnSection_koikodni 
			  end),0) as "EvnLeave_koikodni",--81877

count (distinct case when LeaveType_SysNick in ('other','ksother','dsother') then EvnPS_id end) as "EvnOtherLpu_count",--81877
coalesce(sum(case when  LeaveType_SysNick in ('other','ksother','dsother') then EvnSection_koikodni end ),0) as "EvnOtherLpu_koikodni",--81877

count (distinct case when LeaveType_SysNick in('stac','ksstac','dsstac' ) then EvnPS_id end) as "EvnOtherStac_count",--81877
coalesce(sum(case when  LeaveType_SysNick in('stac','ksstac','dsstac' ) then EvnSection_koikodni end),0) as "EvnOtherStac_koikodni",--81877

count (distinct case when LeaveType_SysNick in('die','ksdie','ksdiepp','dsdie','dsdiepp' )then EvnPS_id end) as "EvnDie_count",--81877
coalesce(sum(case when LeaveType_SysNick in('die','ksdie','ksdiepp','dsdie','dsdiepp' ) then EvnSection_koikodni end),0) as "EvnDie_koikodni",--81877 

count (distinct case when LeaveType_SysNick not in ('leave','ksleave','dsleave','other','ksother','dsother','stac','ksstac','dsstac','die','ksdie','ksdiepp','dsdie','dsdiepp' ) then EvnPS_id end) as "EvnAnyOther_count",
coalesce(sum(case when LeaveType_SysNick not in ('leave','ksleave','dsleave','other','ksother','dsother','stac','ksstac','dsstac','die','ksdie','ksdiepp','dsdie','dsdiepp' ) then EvnSection_koikodni end),0) as "EvnAnyOther_koikodni",

coalesce(sum(Koikodni.cnt),0) as "EvnOper_Count",
count(distinct Koikodni.person_id) as "EvnOper_PCount",
coalesce(sum(case when LeaveType_SysNick in('die','ksdie','ksdiepp','dsdie','dsdiepp' ) then Koikodni.cnt end),0) as "EvnOper_DCount"

from 
(select
Diag.Diag_Code,
Diag.Diag_Name,
rpt.calc_koykoday(coalesce(coalesce(r_3.EvnPS_setDate,r_2.EvnPS_setDate,r_1.EvnPS_setDate),EvnPS.EvnPS_setDate),
						EvnSection.EvnSection_disDate,
						0,--По календарю
						case when LpuSection.LpuUnitType_SysNick = 'stac' and cast(EvnSection.EvnSection_setDate as date) != cast(EvnSection.EvnSection_disDate as date)
							then 0 else 1 end, --Тип стационара КС=0 иначе 1
						p_begtime) as EvnSection_koikodni,
EvnPS_id,
LeaveType.LeaveType_SysNick,
oper.person_id,
oper.cnt

 from Lpu_W Lpu
 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_Index=EvnSection_Count-1

 inner join lateral( 
	select *
    from v_PersonState Person 
	where Person.Person_id = EvnPS.Person_id
	  and (dbo.AgeYmd(Person.Person_BirthDay,EvnPS.EvnPS_setDate,coalesce(lpu.p_ymd_id,1)) between coalesce(lpu.p_beg_age,0) and coalesce(lpu.p_end_age,54750) or (p_beg_age is null and p_end_age is null)) --#91834
	  and (Person.Sex_id = p_sex_id or 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_MedStaffFact MSF on EvnSection.MedStaffFact_id = MSF.MedStaffFact_id
  and MSF.Lpu_id=EvnSection.Lpu_id
  and (MSF.Medpersonal_id = p_medpersonal_id or p_medpersonal_id is null)

 /*inner join v_Person_all Person on Person.Personevn_id = EvnPS.Personevn_id 
		and person.Server_id = evnps.Server_id*/
 left join v_PersonNationalityStatus PNS on Person.NationalityStatus_id = PNS.NationalityStatus_id 

 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 lateral(
   select 
	 EVS_1.EvnPS_setDate,
	 EvnPS_IsCont,
	 EVS_1.EvnPS_setTime
   from dbo.v_EvnPS as EVS_1
   where 1 = 1
     and EVS_1.Person_id = EvnPS.Person_id  
     and EVS_1.lpu_id = EvnPS.Lpu_id 
     and EvnPS.EvnPS_IsCont = 2 
     and cast(EVS_1.EvnPS_disDate as date) = cast(EvnPS.EvnPS_setDate as date)
	order by EVS_1.EvnPS_setDate
	limit 1
	) as r_1 on true
 left join lateral(
   select 
	 EVS_2.EvnPS_setDate,
	 EVS_2.EvnPS_IsCont,
	 EVS_2.EvnPS_setTime
   from dbo.v_EvnPS as EVS_2
   where 1 = 1
	 and EVS_2.Person_id = EvnPS.Person_id 
	 and EVS_2.lpu_id = EvnPS.lpu_id
	 and r_1.EvnPS_IsCont = 2
	 and cast(EVS_2.EvnPS_disDate as date) = cast(r_1.EvnPS_setDate as date)
   order by EVS_2.EvnPS_setDate
   limit 1
   ) as r_2 on true
 left join lateral(
   select 
	 EVS_3.EvnPS_setDate,
	 EVS_3.EvnPS_setTime
   from dbo.v_EvnPS as EVS_3
   where 1 = 1
	 and EVS_3.Person_id = EvnPS.Person_id 
	 and EVS_3.lpu_id = EvnPS.Lpu_id
	 and r_2.EvnPS_IsCont = 2
	 and cast(EVS_3.EvnPS_disDate as date) = cast(r_2.EvnPS_setDate as date)
   order by EVS_3.EvnPS_setDate
   limit 1
   ) as r_3 on true
  left join lateral (select EUOper.person_id,sum(EUOper.EvnUslugaOper_Kolvo) as cnt
			from dbo.v_EvnUslugaOper as EUOper 
			inner join v_EvnSection ES on EUOper.EvnUslugaOper_pid = ES.EvnSection_id
			and ES.EvnSection_pid = EvnPS.EvnPS_id
			group by EUOper.Person_id
			limit 1
  ) as oper 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 LeaveType on LeaveType.LeaveType_id = EvnSection.LeaveType_id

where 1=1
  and EvnPS_disDT >= p_begdate_s 
  and EvnPS_disDT < p_enddate_s
  and (Personcard.Lpu_id = p_lpu_did and p_lpu_did>0 and p_onlypersoncard_yn = 1
    or p_lpu_did is null and p_onlypersoncard_yn = 1
	or Personcard.Lpu_id = p_lpu_id and p_onlypersoncard_yn = 2) 
  and (EvnPS.PrehospType_id = p_prehosptype_id or p_prehosptype_id is null)
  and (EvnPS.PayType_id = p_paytype_id or p_paytype_id is null)
  and (Diag.Diag_Code >= p_begdiag_code or p_begdiag_code is null)
  and (Diag.Diag_Code <= p_enddiag_code or p_enddiag_code is null)
	and (EvnSection.LpuSectionProfile_id=p_lpusectionprofile_id or p_lpusectionprofile_id is null)
  and (p_lpusectionbedprofile_id = BedProfile.LpuSectionBedProfile_id or p_lpusectionbedprofile_id is null)
  and LeaveType.LeaveType_SysNick not in ('section','ksper','dsper' )
  --and (dbo.ageymd(Person.Person_BirthDay,EvnPS.EvnPS_setDate,coalesce(p_ymd_id,1)) between coalesce(p_beg_age,0) and coalesce(p_end_age,54750) or (p_beg_age is null and p_end_age is null)) --#91834
 -- and (Person.Sex_id = p_sex_id or p_sex_id is null)
  and (p_national is null or (coalesce(PNS.KLCountry_id,-1) = 643 and p_national = 1) or (coalesce(PNS.KLCountry_id,643) != 643 and p_national = 2)) 
  and (p_prehosparrive_id=EvnPS.PrehospArrive_id or p_prehosparrive_id is null)
 ) Koikodni 

group by "Diag_Code","Diag_Name"
order by "Diag_Code"
