with params as(
	select 
		cast($1 as int8) as p_lpu_id,
		cast($2 as int8) as p_lpubuilding_id,
		cast($3 as int8) as p_lpuunit_id,
		cast($4 as int8) as p_lpusectionprofile_id,
		cast($5 as int8) as p_lpusection_id,
		cast($6 as int8) as p_medpersonal_id,
		cast($7 as int8) as p_omssprterr, 
		cast(to_timestamp($8,'dd.mm.yyyy') as timestamp) as p_begdate,
		cast(to_timestamp($9,'dd.mm.yyyy') as timestamp) as p_enddate

),
 st as
(
  select
    'ИТОГО' as st_name,
    L.Lpu_Nick || coalesce(' (закрыта ' || to_char(L.Lpu_endDate, 'dd.mm.yyyy') || ')', '') as Lpu_Name,
    L.Lpu_Nick,
    L.Lpu_begDate,
    L.Lpu_id,
    -2 as LSProf_id,
    params.*
  from params
		left join v_Lpu as L on true
  where
    L.Lpu_id = p_Lpu_id or p_Lpu_id is null and L.Lpu_id not in (100, 101, 13002677, 13002714, 150000)
    
  union all
  
  select 'ОБЩИЙ ИТОГ' as st_name, '' as Lpu_Name, '' as Lpu_Nick, null as Lpu_begDate, -2 as Lpu_id, -2 as LSProf_id,
   params.*
  from params
	
  where p_Lpu_id is null
),

d as
(
  select
    L.Lpu_id,
   coalesce(LS.LpuSectionProfile_id, msf.LpuSectionProfile_id, -1) as LSProf_id,
    
    COUNT(distinct case when coalesce(TimetableType_id, 1) != 3 
      then TimeTableGraf_id end) as v1,
      
    COUNT(distinct case when coalesce(TimetableType_id, 1) != 3 
      and coalesce(pmUser.Lpu_id, pmUserC.Lpu_id) is not null
	  and coalesce(pmUserC.pmUser_groups, '') not like '%OperatorCallCenter%'
	  and coalesce(pmUserC.pmUser_groups, '') not like '%HallAdmin%'
	  and ((PromedReg.isReg is not null or coalesce(pmUser.pmUser_Medpersonal_id, pmUserC.MedPersonal_id) is null and ttg.pmUser_updID < 1000000)
	     or (coalesce(pmUser.pmUser_Medpersonal_id, pmUserC.MedPersonal_id) is not null or ttg.pmUser_updID > 1000000))
	  then TimeTableGraf_id end) as v2, 
      
    COUNT(distinct case when coalesce(TimetableType_id, 1) != 3 
      and coalesce(pmUser.Lpu_id, pmUserC.Lpu_id) is not null
	  and coalesce(pmUserC.pmUser_groups, '') not like '%OperatorCallCenter%'
	  and coalesce(pmUserC.pmUser_groups, '') not like '%HallAdmin%'
	  and (PromedReg.isReg is not null or coalesce(pmUser.pmUser_Medpersonal_id, pmUserC.MedPersonal_id) is null and ttg.pmUser_updID < 1000000)
	  and L.Lpu_id = coalesce(pmUser.Lpu_id, pmUserC.Lpu_id)
	  then TimeTableGraf_id end) as v3,
      
    COUNT(distinct case when coalesce(TimetableType_id, 1) != 3 
      and coalesce(pmUser.Lpu_id, pmUserC.Lpu_id) is not null
	  and coalesce(pmUserC.pmUser_groups, '') not like '%OperatorCallCenter%'
	  and coalesce(pmUserC.pmUser_groups, '') not like '%HallAdmin%'
	  and (PromedReg.isReg is not null or coalesce(pmUser.pmUser_Medpersonal_id, pmUserC.MedPersonal_id) is null and ttg.pmUser_updID < 1000000)
	  and L.Lpu_id <> coalesce(pmUser.Lpu_id, pmUserC.Lpu_id)
	  then TimeTableGraf_id end) as v4,
      
    COUNT(distinct case when coalesce(TimetableType_id, 1) != 3 
      and coalesce(pmUser.Lpu_id, pmUserC.Lpu_id) is not null
	  and coalesce(pmUserC.pmUser_groups, '') not like '%OperatorCallCenter%'
	  and coalesce(pmUserC.pmUser_groups, '') not like '%HallAdmin%'
	  and PromedReg.isReg is null 
	  and (coalesce(pmUser.pmUser_Medpersonal_id, pmUserC.MedPersonal_id) is not null or ttg.pmUser_updID > 1000000)
	  and L.Lpu_id = coalesce(pmUser.Lpu_id, pmUserC.Lpu_id)
	  then TimeTableGraf_id end) as v5,
      
    COUNT(distinct case when coalesce(TimetableType_id, 1) != 3 
      and coalesce(pmUser.Lpu_id, pmUserC.Lpu_id) is not null
	  and coalesce(pmUserC.pmUser_groups, '') not like '%OperatorCallCenter%'
	  and coalesce(pmUserC.pmUser_groups, '') not like '%HallAdmin%'
	  and PromedReg.isReg is null 
	  and (coalesce(pmUser.pmUser_Medpersonal_id, pmUserC.MedPersonal_id) is not null or ttg.pmUser_updID > 1000000)
	  and L.Lpu_id <> coalesce(pmUser.Lpu_id, pmUserC.Lpu_id)
	  then TimeTableGraf_id end) as v6,
	  
	COUNT(distinct case when coalesce(TimetableType_id, 1) != 3 
	  and (coalesce(pmUserC.pmUser_groups, '') like '%OperatorCallCenter%' 
	    or coalesce(pmUser.Lpu_id, pmUserC.Lpu_id) is null and ttg.pmUser_updID < 1000000)
	  and coalesce(ttg.pmUser_updID, -1) not in (999900, 999901)  
	  then TimeTableGraf_id end) as v7,
	  
	COUNT(distinct case when coalesce(TimetableType_id, 1) != 3 
	  and (coalesce(pmUser.Lpu_id, pmUserC.Lpu_id) is null and (ttg.pmUser_updID between 1000000 and 5000000 or ttg.pmUser_updID in (999900,999901))
	    or coalesce(pmUser.Lpu_id, pmUserC.Lpu_id) is not null and coalesce(pmUserC.pmUser_groups, '') like '%HallAdmin%')
	  then TimeTableGraf_id end) as v8,
	  
	COUNT(distinct case when coalesce(TimetableType_id, 1) != 3 
	  and coalesce(pmUser.Lpu_id, pmUserC.Lpu_id) is null
	  and ttg.pmUser_updID between 1000000 and 5000000 
	  then TimeTableGraf_id end) as v9,
	  
	COUNT(distinct case when coalesce(TimetableType_id, 1) != 3 
	  and coalesce(pmUser.Lpu_id, pmUserC.Lpu_id) is null
	  and ttg.pmUser_updID  = 999900  
	  then TimeTableGraf_id end) as v10,
	  
	COUNT(distinct case when coalesce(TimetableType_id, 1) != 3 
	  and coalesce(pmUser.Lpu_id, pmUserC.Lpu_id) is null
	  and ttg.pmUser_updID  = 999901  
	  then TimeTableGraf_id end) as v11,
	  
	COUNT(distinct case when coalesce(TimetableType_id, 1) != 3 
	  and coalesce(pmUser.Lpu_id, pmUserC.Lpu_id) is not null
	  and coalesce(pmUserC.pmUser_groups, '') like '%HallAdmin%'
	  then TimeTableGraf_id end) as v12,
	  
	COUNT(distinct case when coalesce(TimetableType_id, 1) != 3 
	  and coalesce(WOW.IsWOW, 0) = 1
	  then TimeTableGraf_id end) as v13,
	  
	COUNT(distinct case when coalesce(TimetableType_id, 1) = 3 
      then TimeTableGraf_id end) as v14   	
  from params 
  left join v_TimeTableGraf ttg  on true 
  inner join v_Medstafffact_ER msf  on msf.MedStaffFact_id = ttg.MedStaffFact_id
    and ttg.Person_id is not null
    and (msf.Lpu_id = p_Lpu_id or p_Lpu_id is null and msf.Lpu_id not in (100, 101, 13002677, 13002714, 150000))
    and (msf.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
    and (msf.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
    and cast(TimeTableGraf_begTime as date) between p_BegDate and p_EndDate
  inner join v_Lpu L  on msf.Lpu_id = L.Lpu_id
 
  left join v_LpuSection LS  on msf.LpuSection_id = LS.LpuSection_id
  left join v_LpuUnit LU  on LU.LpuUnit_id = coalesce(msf.LpuUnit_id, LS.LpuUnit_id)
  left join v_LpuBuilding LB  on LB.LpuBuilding_id = LU.LpuBuilding_id
  left join Address Addr on LB.Address_id = Addr.Address_id
  
  left join lateral
  (
    select 
      Terr.OMSSprTerr_id
    from v_OmsSprTerr Terr 
    where
      (Terr.OMSSprTerr_Code = p_OmsSprTerr or p_OmsSprTerr is null)
      and (Addr.KLCountry_id = Terr.KLCountry_id or Terr.KLCountry_id is null)
      and (Addr.KLRGN_id = Terr.KLRGN_id or Terr.KLRGN_id is null)
      and (Addr.KLSubRGN_id = Terr.KLSubRGN_id or Terr.KLSubRGN_id is null)
      and (Addr.KLCity_id = Terr.KLCity_id or Terr.KLCity_id is null)
      and (Addr.KLTown_id = Terr.KLTown_id or Terr.KLTown_id is null) limit 1
  ) Terr on true
  
  left join v_pmUser pmuser  on ttg.pmUser_updID = pmuser.PMUser_id
  left join v_pmUserCache pmUserC  on ttg.pmUser_updID = pmUserC.PMUser_id
  
  left join lateral
  (
    select
      case when coalesce(MAX(PT.PrivilegeType_Code), 0) > 0 then 1 else 0 end as IsWOW
    from PersonPrivilege PP 
    inner join PrivilegeType PT  on PP.PrivilegeType_id = PT.PrivilegeType_id
      and PT.PrivilegeType_Code in (11, 20, 40, 50, 140, 150)
      and PP.Person_id = ttg.Person_id
      and cast(PP.PersonPrivilege_begDate as date) <= cast(TimeTableGraf_begTime as date)
      and (cast(PP.PersonPrivilege_endDate as date) > cast(TimeTableGraf_begTime as date) or PP.PersonPrivilege_endDate is null)
  ) WOW on true
  
  left join lateral 
  (
    select 
	  1 as isReg
	from v_MedServiceMedPersonal msmp  
	inner join v_MedService ms  on ms.MedService_id = msmp.MedService_id
	  and msmp.MedPersonal_id = coalesce(pmUserC.MedPersonal_id, pmuser.pmUser_Medpersonal_id) 
	  and ms.Lpu_id = coalesce(pmUser.Lpu_id, pmUserC.Lpu_id)
	inner join v_MedServiceType mst  on mst.MedServiceType_id = ms.MedServiceType_id
	  and MedServiceType_SysNick = 'regpol' limit 1
  ) as PromedReg on true --для определения службы Регистратура
  
  where
    Terr.OMSSprTerr_id = p_OmsSprTerr or p_OmsSprTerr is null
    and (coalesce(msf.LpuSectionProfile_id, LS.LpuSectionProfile_id) = p_LpuSectionProfile_id or p_LpuSectionProfile_id is null)
    and (LU.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
    and (LB.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
    GROUP BY ROLLUP(L.Lpu_id,
  coalesce(LS.LpuSectionProfile_id, msf.LpuSectionProfile_id, -1))
  --group by
   -- L.Lpu_id,
  --coalesce(LS.LpuSectionProfile_id, msf.LpuSectionProfile_id, -1) 
 -- with rollup   
)

select
  ROW_NUMBER()OVER(order by case when st.Lpu_id = -2 then 1 else 0 end,
    st.Lpu_Nick, st.Lpu_begDate, st.Lpu_id,
    case when coalesce(d.LSProf_id, -2) = -2 then 2
         when coalesce(d.LSProf_id, -2) = -1 then 1
    else 0 end,
    LpuSectionProfile_Name, LpuSectionProfile_id) as RowNum1,
    
  ROW_NUMBER()OVER(partition by st.Lpu_id order by
    case when coalesce(d.LSProf_id, -2) = -2 then 2
         when coalesce(d.LSProf_id, -2) = -1 then 1
    else 0 end,
    LpuSectionProfile_Name, LpuSectionProfile_id) as RowNum2,
    
  case when coalesce(d.LSProf_id, -2) <> -2 and coalesce(st.Lpu_id, -2) <> -2 then 'row_lsprof'
       when coalesce(d.LSProf_id, -2) = -2 and coalesce(st.Lpu_id, -2) <> -2 then 'itog_lpu' 
       when coalesce(d.LSProf_id, -2) = -2 and coalesce(st.Lpu_id, -2) = -2 then 'itog_all'
  end as st_type, 
  
  st.Lpu_Name,
  
  case when coalesce(d.LSProf_id, -2) = -1 then 'без профиля'
       when coalesce(d.LSProf_id, -2) > -1 then coalesce(LSProf.LpuSectionProfile_Name, '') 
  end::varchar as LSProf_Name,
  
  case when coalesce(d.LSProf_id, -2) = -2 or st.Lpu_id = -2 
    then st_name else '' end as Itog_Name,
  
  coalesce(d.v1, 0) as v1,
  coalesce(d.v2, 0) as v2,
  coalesce(d.v3, 0) as v3,
  coalesce(d.v4, 0) as v4,
  coalesce(d.v5, 0) as v5,
  coalesce(d.v6, 0) as v6,
  coalesce(d.v7, 0) as v7,
  coalesce(d.v8, 0) as v8,
  coalesce(d.v9, 0) as v9,
  coalesce(d.v10, 0) as v10,
  coalesce(d.v11, 0) as v11,
  coalesce(d.v12, 0) as v12,
  coalesce(d.v13, 0) as v13,
  coalesce(d.v14, 0) as v14
from st
left join d on st.Lpu_id = coalesce(d.Lpu_id, -2) 
left join v_LpuSectionProfile LSProf  on LSProf.LpuSectionProfile_id = d.LSProf_id
where
  coalesce(d.v1, 0) + coalesce(d.v11, 0) > 0 or st.Lpu_id = coalesce(p_Lpu_id, -2)
