
with param as (select
    cast($1 as bigint) as p_lpu_id, 
    cast($2 as text) 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_lpuunittype_id, 
    cast($7 as bigint) as p_paytype_id, 
    cast(to_timestamp($8,'dd.mm.yyyy') as timestamp) as p_begdate, 
    cast(to_timestamp($9,'dd.mm.yyyy') as timestamp) as p_enddate, 
    cast($10 as time) as p_stactime, 
    cast($11 as bigint) as p_order413, 
    cast($12 as bigint) as p_printstring,
    cast($13 as bigint) as p_PrintProf,
    cast($14 as bigint) as p_NeedReanim,
    cast($15 as bigint) as p_national
  
  ),
    --Выбираем все МО с их право приемственностью
  LpuP as 
(
  select     
    L.Lpu_id,
    cast(L.Lpu_begDate as date) as Lpu_begDate,
    cast(L.Lpu_endDate as date) as Lpu_endDate,
	(case when coalesce(p_Order413, 2) = 2 then - interval '1 day' else interval '0 day' end + p_begDate) as LS_begDate,
  (case when coalesce(p_Order413, 2) = 2 then - interval '1 day' else interval '0 day' end + p_endDate) as LS_endDate,

    (case when coalesce(p_Order413, 2) = 2 then - interval '1 day' else interval '0 day' end + p_begDate) + 
	  coalesce(p_StacTime, cast('09:00' as time)) as Stac_begDT,

    (case when coalesce(p_Order413, 2) = 2 then interval '0 day' else interval '1 day' end + p_endDate) + 
	  coalesce(p_StacTime, cast('09:00' as time)) as Stac_endDT,

    coalesce(p_StacTime, cast('09:00' as time)) as StacTime
  from param 
      inner join rpt.GetLpu_succession_pg (p_Lpu_id, (case when coalesce(p_Order413, 2) = 2 then - interval '1 day' else interval '0 day' end + p_begDate), (case when coalesce(p_Order413, 2) = 2 then interval '0 day' else interval '1 day' end + p_endDate)) as L on true
),

LpSection as
(
  select 
    LpuP.Lpu_id,
    LS.LpuSection_id,
	LS.LpuSection_Code,
	LS.LpuSection_Name,

    LU.LpuUnitType_SysNick,
    LpuP.Lpu_begDate,
    LpuP.Lpu_endDate,
    LpuP.LS_begDate,
    LpuP.LS_endDate,
	LpuP.Stac_begDT,
	LpuP.Stac_endDT,
	LpuP.StacTime,
    dbo.MaxDate(cast(coalesce(LS.LpuSection_setDate, LU.LpuUnit_begDate) as date), LpuP.LS_begDate) as LpuSection_setDate,
    
    dbo.MinDate(cast(coalesce(LS.LpuSection_disDate, LU.LpuUnit_endDate, LpuP.Lpu_endDate, LpuP.LS_endDate) as date),
      LpuP.LS_endDate) as LpuSection_disDate, 
      
    LpuSectionAge_id,
	LS.LpuSectionBedProfile_id
  from param 
      inner join LpuP on true
  inner join v_LpuSection LS  on LS.Lpu_id = LpuP.Lpu_id
    and LS.LpuSectionProfile_SysNick = 'profil'
    and (LS.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
    and (LS.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
    and (LS.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
  inner join v_LpuUnit LU  on LU.LpuUnit_id = LS.LpuUnit_id
    and LU.LpuUnitType_SysNick in ('stac','dstac','pstac','hstac')  
	and (LU.LpuUnitType_id = p_LpuUnitType_id or p_LpuUnitType_id is null)
  inner join v_LpuBuilding LB  on LB.LpuBuilding_id = LU.LpuBuilding_id
    and (',' || coalesce(p_LpuFilialList, '') || ',' like '%,' || coalesce(cast(LB.LpuFilial_id as varchar), '0') || ',%' 
      or coalesce(rtrim(p_LpuFilialList), '') = '')
),
Medservice_w as (
select 
  ms.MedService_id,
  ms.MedService_Code,
  ms.MedService_Name,
  LpuP.Lpu_id,
  LpuP.Stac_begDT,
  LpuP.Stac_endDT,
  LpuP.StacTime,
  ms.MedService_begDT,
  ms.MedService_endDT
from LpuP  
   inner join v_MedService ms  on ms.Lpu_id = LpuP.Lpu_id
where 1=1
  and ms.MedServiceType_id = 67 --Службы реанимации
),
EvnPS_W as
(
  select
    EvnPS.EvnPS_id, 
    EvnPS.EvnPS_setDate,
	EvnPS.EvnPS_disDate,
	EvnPS.EvnPS_setDT,
	EvnPS.EvnPS_disDT,
	EvnPS.Lpu_id,
	EvnPS.Person_id,
  EvnPS.Server_id,
	EvnPS.PersonEvn_id,
	EvnPS.Person_Age,
	EvnPS.LpuSection_id,
	EvnPS.LpuSection_pid,
	EvnPS.MedPersonal_pid,
	EvnPS.MedStaffFact_pid,
	EvnPS.PayType_id,
	EvnPS.Diag_id,
	EvnPS.Diag_pid,
	EvnPS.LeaveType_id,
    EvnPS.EvnPS_IsCont,
    EvnPS.PreHospType_id,
	EvnPS.PrehospDirect_id,
	EvnPS.LpuSection_did,
  EvnPS.EvnPS_NumCard,
    EvnPS.PrehospArrive_id
  from v_EvnPS EvnPS 
	where EvnPS.PrehospWaifRefuseCause_id is null
),
EvnSection_w as (
select
	ES.EvnSection_id, 
  coalesce(end_Reanim.EvnReanimatPeriod_disDT,ES.EvnSection_setDT) as EvnSection_setDT,
	coalesce(beg_Reanim.EvnReanimatPeriod_setDT,ES.EvnSection_disDT) as EvnSection_disDT,
	cast(cast(coalesce(end_Reanim.EvnReanimatPeriod_disDT,ES.EvnSection_setDT) as date) as timestamp) as EvnSection_setDate,
	cast(cast(coalesce(beg_Reanim.EvnReanimatPeriod_setDT,ES.EvnSection_disDT) as date) as timestamp) as EvnSection_disDate,
  ES.EvnSection_pid,
	ES.Lpu_id,
	ES.Person_id,
	ES.Server_id,
	ES.EvnSection_Index,
	ES.EvnSection_Count,
	case when beg_Reanim.EvnReanimatPeriod_setDT is not null then 1 else 0 end IsToRean,
	case when end_Reanim.EvnReanimatPeriod_disDT != ES.EvnSection_setDT then 1 else 0 end IsFromRean,
	ES.LpuSection_id as LpuSection_id,
	ES.PayType_id as PayType_id,
	ES.LeaveType_id as LeaveType_id,
	ES.EvnSection_IsPriem,
	ES.LpuSectionBedProfileLink_fedid,
	ES.EvnSection_IsAdultEscort,
	LSBPL.LpuSectionBedProfile_id

  from param inner join v_EvnSection ES on true
  left join fed.LpuSectionBedProfileLink LSBPL  on LSBPL.LpuSectionBedProfileLink_id = ES.LpuSectionBedProfileLink_fedid
   left join lateral
  (select ES.EvnSection_setDT as EvnReanimatPeriod_disDT
  union all
   select 
     erp.EvnReanimatPeriod_disDT
   from v_EvnReanimatPeriod erp  
   where 1=1
     and ES.EvnSection_id = erp.EvnReanimatPeriod_pid
     and erp.EvnReanimatPeriod_disDT is not null
  	 and erp.ReanimResultType_id = 1
     and param.p_NeedReanim is null  
  ) end_Reanim on true
  left join lateral
  (select 
     rp.EvnReanimatPeriod_setDT
   from v_EvnReanimatPeriod rp 
   where 1=1
     and ES.EvnSection_id = rp.EvnReanimatPeriod_pid
     and rp.EvnReanimatPeriod_setDT>=end_Reanim.EvnReanimatPeriod_disDT
     and p_NeedReanim is null 
   order by rp.EvnReanimatPeriod_setDT limit 1
  ) beg_Reanim on true

),
EvnSectionRean_w as (
select
	erp.Evn_id as EvnSection_id, 
  erp.Evn_setDT as EvnSection_setDT,
	erp.Evn_disDT as EvnSection_disDT,
	cast(cast(erp.Evn_setDT as date) as timestamp) as EvnSection_setDate,
	cast(cast(erp.Evn_disDT as date) as timestamp) as EvnSection_disDate,
    erp.Evn_rid as EvnSection_pid,
	erp.Lpu_id as Lpu_id,
	erp.Server_id as Server_id,
	erp.Person_id,
	erp.Evn_Index as EvnSection_Index,
	erp.Evn_Count as EvnSection_Count,
	erp.ReanimResultType_id,
	erp.LpuSectionBedProfile_id,
	erp.MedService_id,
	erp.LpuSection_id
from param inner join EvnReanimatPeriod erp on true
where 1 = 1
  and erp.Evn_deleted = 1
  and coalesce(p_NeedReanim,2) = 2 
),
getEvnSection as (
select ES.EvnSection_id,
       ES.EvnSection_pid,
       ES.Person_id,
       ES.LpuSectionBedProfile_id,
       --Поступило больных* (без переведенных внутри больницы)
       case
         when ES.EvnSection_setDT >= LS.Stac_begDT 
           and ES.EvnSection_setDT < LS.Stac_endDT 
           and ESPriem.EvnSection_id is null 
           and ES.IsFromRean = 0 --Не переведен из реанимации
           and (COALESCE(EPS.EvnPS_IsCont, 1) = 1 or EPS.EvnPS_IsCont = 2 and COALESCE(EPS.PrehospDirect_id, - 1) <> 1) -- кроме "Кем направлен" = "Отделение МО"
       then 1
       end as val6,
       --переведено больных внутри больницы из других отделений
       case
         when ES.EvnSection_setDT >= LS.Stac_begDT 
           and ES.EvnSection_setDT < LS.Stac_endDT 
           and (ESPriem.EvnSection_id is not null or ES.IsFromRean = 1 --Переведен из реанимации
             or EPS.EvnPS_IsCont = 2 and EPS.PrehospDirect_id = 1 and ESPriem.EvnSection_id is null) then 1
       end as val11,
       --переведено больных внутри больницы в другие отделения
       case
         when ES.EvnSection_disDT >= LS.Stac_begDT 
           and ES.EvnSection_disDT < LS.Stac_endDT 
           and (ES.EvnSection_Index = ES.EvnSection_Count - 1 
           and LT.LeaveType_SysNick in ('section', 'ksper', 'dsper', 'psreab') 
             or ES.IsToRean = 1 --Переведен в реанимацию
             or ES.EvnSection_Index < ES.EvnSection_Count - 1) then 1
       end as val12,
       --Выписано* больных
       case
         when ES.EvnSection_disDT >= LS.Stac_begDT 
           and ES.EvnSection_disDT < LS.Stac_endDT 
           and ES.EvnSection_Index = ES.EvnSection_Count - 1 
           and ES.IsToRean = 0 --Не переведен в реанимацию
           and LT.LeaveType_Code is not null 
           and coalesce(flt.LeaveType_Code, 0) not in (105,106,205,206)
           and COALESCE(LT.LeaveType_SysNick, '') not in ('section', 'ksper', 'dsper', 'psreab') 
           and COALESCE(RD.ResultDesease_SysNick, '') != 'umer' then 1
       end as val13,
       --Выписано* больных переведенных в другие стационары
       case
         when ES.EvnSection_disDT >= LS.Stac_begDT 
           and ES.EvnSection_disDT < LS.Stac_endDT 
           and ES.EvnSection_Index = ES.EvnSection_Count - 1 
           and ES.IsToRean = 0 --Не переведен в реанимацию
           and COALESCE(LT.LeaveType_SysNick, '') in ('other', 'ksother', 'dsother', 'ksperitar') then 1
       end as val14,
       --умерло
       case
         when ES.EvnSection_disDT >= LS.Stac_begDT 
          and ES.EvnSection_disDT < LS.Stac_endDT 
          and ES.EvnSection_Index = ES.EvnSection_Count - 1 
          and ES.IsToRean = 0 --Не переведен в реанимацию
          and (coalesce(flt.LeaveType_Code, 0) in (105,106,205,206) or RD.ResultDesease_SysNick = 'umer') then 1
       end as val18
from param inner join LpSection LS on true
     inner join EvnSection_w ES on ES.LpuSection_id = LS.LpuSection_id and ES.Lpu_id = LS.Lpu_id
     -- #Статистические сутки с 9:00 по 8:59 следующего дня.
     and (ES.EvnSection_disDT >= LS.Stac_begDT or ES.EvnSection_disDT is null) 
     and EvnSection_setDT < LS.Stac_endDT 
     and COALESCE(ES.EvnSection_IsPriem, 1) = 1 
     and (ES.PayType_id = p_PayType_id or p_PayType_id is null)
     inner join EvnPS_W EPS on EPS.EvnPS_id = ES.EvnSection_pid
     left join v_LeaveType LT on LT.LeaveType_id = ES.LeaveType_id
     left join fed.LeaveType flt on lt.LeaveType_fedid = flt.LeaveType_id
     left join v_LpuSection LSDir on LSDir.LpuSection_id = EPS.LpuSection_did
     left join v_LpuUnit LUDir on LUDir.LpuUnit_id = LSDir.LpuUnit_id
     left join v_EvnOtherStac EOSt on EOSt.EvnOtherStac_pid = ES.EvnSection_id --EvnOtherStac_rid = EPS.EvnPS_id
     left join v_LpuUnitType LUTOSt on LUTOSt.LpuUnitType_id = EOSt.LpuUnitType_oid
     left join v_EvnLeaveBase as ELB on ELB.EvnLeaveBase_pid = ES.EvnSection_id
     left join v_ResultDesease rd on ELB.ResultDesease_id = rd.ResultDesease_id
     LEFT JOIN LATERAL
     (
       select ESPriem.EvnSection_id
       from v_EvnSection ESPriem
       where ESPriem.EvnSection_pid = ES.EvnSection_pid and
             ESPriem.EvnSection_Index = ES.EvnSection_Index - 1 and
             COALESCE(ESPriem.EvnSection_IsPriem, 1) = 1
       limit 1
     ) ESPriem on true
where (p_PrintString
      = 2) and
      COALESCE(p_NeedReanim, 1) = 1),
getEvnSectionRean as (
select ES.EvnSection_id,
       ES.EvnSection_pid,
       ES.Person_id,
       ES.LpuSectionBedProfile_id,
       --переведено больных внутри больницы из других отделений
       case
         when ES.EvnSection_setDT >= LS.Stac_begDT and ES.EvnSection_setDT < LS.Stac_endDT then 1
       end as val11,
       --переведено больных внутри больницы в другие отделения
       case
         when ES.EvnSection_disDT >= LS.Stac_begDT and ES.EvnSection_disDT < LS.Stac_endDT and ES.ReanimResultType_id in (1,4) then 1
       end as val12,
       --умерло
       case
         when ES.EvnSection_disDT >= LS.Stac_begDT and ES.EvnSection_disDT < LS.Stac_endDT and ES.ReanimResultType_id in (2, 3) then 1
       end as val18
from param inner join LpSection LS on true
     inner join EvnSectionRean_w ES on ES.LpuSection_id = LS.LpuSection_id and LS.Lpu_id = es.Lpu_id and (ES.EvnSection_disDT >= LS.Stac_begDT or ES.EvnSection_disDT is null) and EvnSection_setDT < LS.Stac_endDT
     inner join EvnPS_W EPS on EPS.EvnPS_id = ES.EvnSection_pid and EPS.EvnPS_setDT < LS.Stac_endDT and COALESCE(EPS.EvnPS_disDT, LS.Stac_begDT) >= LS.Stac_begDT
where (p_PrintString = 2) and
      COALESCE(p_NeedReanim, 2) = 2)
select max(entered) as ent,
       max(from_stac_entered) as from_stac,
       max(released) as rels,
       max(trans_to_section) as to_sect,
       max(trans_to_other) as to_other,
       max(died) as die
from (
  -- поступившие (1-6)
  select row_number() over(
    order by 0, PS.Person_SurName, PS.Person_id) as recn, COALESCE(PS.Person_SurName || ' ', '') || COALESCE(PS.Person_FirName || ' ', '') || COALESCE(PS.Person_SecName, '') || '/' || EPS.EvnPS_NumCard || case when p_PrintProf = 2 then '/'||COALESCE(LSBProf.LpuSectionBedProfile_Name,'профиль коек не указан') else '' end
         as entered, null from_stac_entered, null released, null trans_to_section, null trans_to_other, null died
  from param inner join getEvnSection GES on true
     inner join EvnPS_W EPS on EPS.EvnPS_id = GES.EvnSection_pid and GES.val6 = 1
     inner join v_Person_all PS  on PS.PersonEvn_id = EPS.PersonEvn_id 
				and EPS.Server_id = PS.Server_id
     left join v_PersonNationalityStatus PNS on PS.PersonEvn_id = PNS.PersonNationalityStatus_id and PS.Server_id = PNS.Server_id
     left join v_LpuSectionBedProfile LSBProf on LSBProf.LpuSectionBedProfile_id = GES.LpuSectionBedProfile_id
  where (param.p_national is null or (COALESCE(PNS.KLCountry_id,643) = 643 and param.p_national = 1) or (COALESCE(PNS.KLCountry_id,643) != 643 and param.p_national = 2))
  union
      --поступившие из стационара (2-11)
  select row_number() over(
    order by ES.TypeES, PS.Person_SurName, PS.Person_id) as recn, null, COALESCE(PS.Person_SurName || ' ', '') || COALESCE(PS.Person_FirName || ' ', '') || COALESCE(PS.Person_SecName, '') || '/' || EPS.EvnPS_NumCard || case when p_PrintProf = 2 then '/'||COALESCE(LSBProf.LpuSectionBedProfile_Name,'профиль коек не указан') else '' end
         as from_stac_entered, null, null, null, null
  from param inner join (
       select 0 as TypeES,
              GES.Person_id,
              GES.EvnSection_pid,
              GES.LpuSectionBedProfile_id
       from getEvnSection GES
       where GES.val11 = 1
       union all
       select 1 as TypeES,
              GES.Person_id,
              GES.EvnSection_pid,
              GES.LpuSectionBedProfile_id
       from getEvnSectionRean GES
       where GES.val11 = 1
     ) ES on true
     inner join EvnPS_W EPS on EPS.EvnPS_id = ES.EvnSection_pid
     inner join v_Person_all PS  on PS.PersonEvn_id = EPS.PersonEvn_id 
				and EPS.Server_id = PS.Server_id
     left join v_PersonNationalityStatus PNS on PS.PersonEvn_id = PNS.PersonNationalityStatus_id and PS.Server_id = PNS.Server_id
     left join v_LpuSectionBedProfile LSBProf on LSBProf.LpuSectionBedProfile_id = ES.LpuSectionBedProfile_id
  where (param.p_national is null or (COALESCE(PNS.KLCountry_id,643) = 643 and param.p_national = 1) or (COALESCE(PNS.KLCountry_id,643) != 643 and param.p_national = 2))
  union
      --выписанные (3-13)
  select row_number() over(
    order by 0, PS.Person_SurName, PS.Person_id) as recn, null, null, COALESCE(PS.Person_SurName || ' ', '') || COALESCE(PS.Person_FirName || ' ', '') || COALESCE(PS.Person_SecName, '') || '/' || EPS.EvnPS_NumCard || case when p_PrintProf = 2 then '/'||COALESCE(LSBProf.LpuSectionBedProfile_Name,'профиль коек не указан') else '' end
         as released, null, null, null
  from param inner join getEvnSection GES on true
     inner join EvnPS_W EPS on EPS.EvnPS_id = GES.EvnSection_pid and GES.val13 = 1
     inner join v_Person_all PS  on PS.PersonEvn_id = EPS.PersonEvn_id 
				and EPS.Server_id = PS.Server_id
     left join v_PersonNationalityStatus PNS on PS.PersonEvn_id = PNS.PersonNationalityStatus_id and PS.Server_id = PNS.Server_id
     left join v_LpuSectionBedProfile LSBProf on LSBProf.LpuSectionBedProfile_id = GES.LpuSectionBedProfile_id
  where (param.p_national is null or (COALESCE(PNS.KLCountry_id,643) = 643 and param.p_national = 1) or (COALESCE(PNS.KLCountry_id,643) != 643 and param.p_national = 2))
  union
      --переведенные в др отделение (4-12)
  select row_number() over(
    order by ES.TypeES, PS.Person_SurName, PS.Person_id) as recn, null, null, null, COALESCE(PS.Person_SurName || ' ', '') || COALESCE(PS.Person_FirName || ' ', '') || COALESCE(PS.Person_SecName, '') || '/' || EPS.EvnPS_NumCard || case when p_PrintProf = 2 then '/'||COALESCE(LSBProf.LpuSectionBedProfile_Name,'профиль коек не указан') else '' end
         as trans_to_section, null, null
  from param inner join  (
       select 0 as TypeES,
              GES.Person_id,
              GES.EvnSection_pid,
              GES.LpuSectionBedProfile_id
       from getEvnSection GES
       where GES.val12 = 1
       union all
       select 1 as TypeES,
              GES.Person_id,
              GES.EvnSection_pid,
              GES.LpuSectionBedProfile_id
       from getEvnSectionRean GES
       where GES.val12 = 1
     ) ES on true
     inner join EvnPS_W EPS on EPS.EvnPS_id = ES.EvnSection_pid
     inner join v_Person_all PS  on PS.PersonEvn_id = EPS.PersonEvn_id 
				and EPS.Server_id = PS.Server_id
     left join v_PersonNationalityStatus PNS on PS.PersonEvn_id = PNS.PersonNationalityStatus_id and PS.Server_id = PNS.Server_id
     left join v_LpuSectionBedProfile LSBProf on LSBProf.LpuSectionBedProfile_id = ES.LpuSectionBedProfile_id
  where (param.p_national is null or (COALESCE(PNS.KLCountry_id,643) = 643 and param.p_national = 1) or (COALESCE(PNS.KLCountry_id,643) != 643 and param.p_national = 2))
  union
      --переведенные в др стационар (5-14)
  select row_number() over(
    order by 0, PS.Person_SurName, PS.Person_id) as recn, null, null, null, null, COALESCE(PS.Person_SurName || ' ', '') || COALESCE(PS.Person_FirName || ' ', '') || COALESCE(PS.Person_SecName, '') || '/' || EPS.EvnPS_NumCard || case when p_PrintProf = 2 then '/'||COALESCE(LSBProf.LpuSectionBedProfile_Name,'профиль коек не указан') else '' end
         as trans_to_other, null
  from param inner join getEvnSection GES on true
     inner join EvnPS_W EPS on EPS.EvnPS_id = GES.EvnSection_pid and GES.val14 = 1
     inner join v_Person_all PS  on PS.PersonEvn_id = EPS.PersonEvn_id 
				and EPS.Server_id = PS.Server_id
     left join v_PersonNationalityStatus PNS on PS.PersonEvn_id = PNS.PersonNationalityStatus_id and PS.Server_id = PNS.Server_id
     left join v_LpuSectionBedProfile LSBProf on LSBProf.LpuSectionBedProfile_id = GES.LpuSectionBedProfile_id
  where (param.p_national is null or (COALESCE(PNS.KLCountry_id,643) = 643 and param.p_national = 1) or (COALESCE(PNS.KLCountry_id,643) != 643 and param.p_national = 2))
  union
      --умерли (6-18)
  select row_number() over(
    order by ES.TypeES, PS.Person_SurName, PS.Person_id) as recn, null, null, null, null, null, COALESCE(PS.Person_SurName || ' ', '') || COALESCE(PS.Person_FirName || ' ', '') || COALESCE(PS.Person_SecName, '') || '/' || EPS.EvnPS_NumCard || case when p_PrintProf = 2 then '/'||COALESCE(LSBProf.LpuSectionBedProfile_Name,'профиль коек не указан') else '' end
         as died
   from param inner join (
       select 0 as TypeES,
              GES.Person_id,
              GES.EvnSection_pid,
              GES.LpuSectionBedProfile_id
       from getEvnSection GES
       where GES.val18 = 1
       union all
       select 1 as TypeES,
              GES.Person_id,
              GES.EvnSection_pid,
              GES.LpuSectionBedProfile_id
       from getEvnSectionRean GES
       where GES.val18 = 1
     ) ES on true
     inner join EvnPS_W EPS on EPS.EvnPS_id = ES.EvnSection_pid
     inner join v_Person_all PS  on PS.PersonEvn_id = EPS.PersonEvn_id 
				and EPS.Server_id = PS.Server_id
     left join v_PersonNationalityStatus PNS on PS.PersonEvn_id = PNS.PersonNationalityStatus_id and PS.Server_id = PNS.Server_id
     left join v_LpuSectionBedProfile LSBProf on LSBProf.LpuSectionBedProfile_id = ES.LpuSectionBedProfile_id
  where (param.p_national is null or (COALESCE(PNS.KLCountry_id,643) = 643 and param.p_national = 1) or (COALESCE(PNS.KLCountry_id,643) != 643 and param.p_national = 2))
  ) AGGR
group by AGGR.recn
--  order by 1,2,3,4,5,6

