USP_DATALIST_INFORMATIONLIBRARY_KPIHIERARCHY

CREATE procedure dbo.USP_DATALIST_INFORMATIONLIBRARY_KPIHIERARCHY
(
    @GROUPBY tinyint = 0,
    @CURRENTAPPUSERID uniqueidentifier 
)
as
    set nocount on;


  declare @ISSYSADMIN bit;
  select @ISSYSADMIN = [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @CURRENTAPPUSERID;

    declare @SITEPRODUCTINSTALLED bit;
  set @SITEPRODUCTINSTALLED = dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('133f9bca-00f1-4007-9792-586b931340c6');

  --------


  /* folders user has permission to see */
    declare @PERMISSIONEDFOLDERS table
    (
        [FOLDERID] uniqueidentifier,
        [PARENTFOLDERID] uniqueidentifier,
        [FOLDERNAME] nvarchar(max),
        [DISPLAYORDER] int
    );

    insert into @PERMISSIONEDFOLDERS ([FOLDERID], [PARENTFOLDERID], [FOLDERNAME], [DISPLAYORDER])
        select 
          [ID],
          isnull([PARENTFOLDERID], '00000000-0000-0000-0000-000000000000'),
          [NAME],
          [DISPLAYORDER]
        from dbo.[KPIINSTANCEFOLDER]
        union all
        select '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000003', 'root', 1
        union all
        select '00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000000', '<none>', 0;

  --------


    /* KPIs user has permission to see */
  declare @KPISTOINCLUDE table
  (
      [KPIINSTANCEID] uniqueidentifier,
      [FOLDERID] uniqueidentifier,
      [KPICATALOGID] uniqueidentifier,
      [RECORDTYPEID] uniqueidentifier,
      [ISFAVORITE] bit
  );    

  with 
  [USERKPIS] as 
  (
    select distinct [KPIINSTANCEID]
    from
  (select 
    distinct [SYSTEMROLEKPIINSTANCE].[KPIINSTANCEID]
  from dbo.[SYSTEMROLEKPIINSTANCE]
  inner join dbo.[SYSTEMROLEAPPUSER] on [SYSTEMROLEKPIINSTANCE].[SYSTEMROLEID] = [SYSTEMROLEAPPUSER].[SYSTEMROLEID]
  where [SYSTEMROLEAPPUSER].[APPUSERID] = @CURRENTAPPUSERID

  union all

  select
    [KPIINSTANCE].[ID]
  from dbo.[V_SECURITY_SYSTEMROLEASSIGNMENT_USER_KPI] [SECURITYVIEW]
  inner join dbo.[KPIINSTANCE] on [SECURITYVIEW].[KPICATALOGID] = [KPIINSTANCE].[KPICATALOGID]
  where [SECURITYVIEW].[APPUSERID] = @CURRENTAPPUSERID
  group by [KPIINSTANCE].[ID]
  having  min(cast([SECURITYVIEW].[GRANTORDENY] as tinyint)) = 1) as [KPI]
  )

  insert into @KPISTOINCLUDE
  (
      [KPIINSTANCEID],
      [FOLDERID],
      [KPICATALOGID],
      [RECORDTYPEID],
      [ISFAVORITE]     
  )  
    select 
        [KPIINSTANCE].[ID] [KPIINSTANCEID],
        isnull([KPIINSTANCE].[FOLDERID], '00000000-0000-0000-0000-000000000001') [FOLDERID],
        [KPICATALOG].[ID] [KPICATALOGID],
        isnull([RECORDTYPE].[ID], '00000000-0000-0000-0000-000000000001') [RECORDTYPEID],
    case 
        when [FAVORITE].[ID] is not null then convert(bit, 1) 
        else convert(bit, 0)
        end as [ISFAVORITE]
    from dbo.[KPIINSTANCE]
    inner join dbo.[KPICATALOG] on [KPIINSTANCE].[KPICATALOGID] = [KPICATALOG].[ID]
    inner join @PERMISSIONEDFOLDERS [FOLDER] on isnull([KPIINSTANCE].[FOLDERID], '00000000-0000-0000-0000-000000000000') = [FOLDER].[FOLDERID]
    left join dbo.[RECORDTYPE] on [KPICATALOG].[CONTEXTRECORDTYPEID] = [RECORDTYPE].[ID]    
    left join dbo.[APPUSERKPIINSTANCEFAVORITE] [FAVORITE] on [FAVORITE].[KPIINSTANCEID] = [KPIINSTANCE].[ID] and [FAVORITE].[APPUSERID] = @CURRENTAPPUSERID
    where 
    (@ISSYSADMIN = 1
     or exists
     (select [KPIINSTANCEID]
     from [USERKPIS]
     where [USERKPIS].[KPIINSTANCEID] = [KPIINSTANCE].[ID])
     )  --Security check

        and 
        (
            [KPICATALOG].[HASSITEFILTER] = 0
            or [KPIINSTANCE].[SITEFILTERENABLEDFORINSTANCE] = 0
            or @SITEPRODUCTINSTALLED = 0
            or @ISSYSADMIN = 1
            or exists
            (
                select top 1 1
                from dbo.[SYSTEMROLEAPPUSER]
                where [SYSTEMROLEAPPUSER].[APPUSERID] = @CURRENTAPPUSERID
                -- make sure the kpi instance is permissioned for this role 

                and exists 
                (
                    select top 1 1 
                    from dbo.[SYSTEMROLEKPIINSTANCE]
                    where [SYSTEMROLEKPIINSTANCE].[SYSTEMROLEID] = [SYSTEMROLEAPPUSER].[SYSTEMROLEID]
                    and [SYSTEMROLEKPIINSTANCE].[KPIINSTANCEID] = [KPIINSTANCE].[ID]
                )
                and
                (
                    [SYSTEMROLEAPPUSER].[SECURITYMODECODE] = 0 -- all sites

                    or
                    (
                        [SYSTEMROLEAPPUSER].[SECURITYMODECODE] = 1 -- no site

                        and not exists (select top 1 1 from dbo.[KPIINSTANCESITE] where [KPIINSTANCEID] = [KPIINSTANCE].[ID])
                    ) 
                    or
                    (
                        [SYSTEMROLEAPPUSER].[SECURITYMODECODE] in (2, 3) -- records with one of these sites assigned or within a branch

                        and exists
                        (
                            select top 1 1
                            from dbo.[SITEPERMISSION]
                            inner join dbo.[KPIINSTANCESITE] on [SITEPERMISSION].[SITEID] = [KPIINSTANCESITE].[SITEID]
                            where [SITEPERMISSION].[SYSTEMROLEID] = [SYSTEMROLEAPPUSER].[SYSTEMROLEID]
                            and [SITEPERMISSION].[APPUSERID] = [SYSTEMROLEAPPUSER].[APPUSERID]
                            and [KPIINSTANCESITE].[KPIINSTANCEID] = [KPIINSTANCE].[ID]
                        )
                    )
                )       
            )
        )    

    ---------


  /* nodes to show the user */    
  declare @NODES table
  (
      [NODEID] nvarchar(255),
      [PARENTNODEID] nvarchar(255),
      [NODENAME] nvarchar(max),
      [DISPLAYORDER] int,
      [COUNT] int default 0
  );     

  --Rollup by folders

  if @GROUPBY = 0
  begin   
      insert into @NODES([NODEID], [PARENTNODEID], [NODENAME], [DISPLAYORDER], [COUNT])
        select  
          f.[FOLDERID],
          f.[PARENTFOLDERID],
          f.[FOLDERNAME],
          f.[DISPLAYORDER],
          count(distinct k.[KPIINSTANCEID])
        from @PERMISSIONEDFOLDERS f
        left join @KPISTOINCLUDE k on k.[FOLDERID] = f.[FOLDERID]
        group by 
          f.[FOLDERID],
          f.[PARENTFOLDERID],
          f.[FOLDERNAME],
          f.[DISPLAYORDER];
  end
  --Rollup by type

  else if @GROUPBY = 1
  begin    
    declare @TYPES table
    (
      [PATH] nvarchar(255),
      [FOLDERNAME] nvarchar(255),
      [PARENTPATH] nvarchar(255),
      [DISPLAYORDER] [int]
    );

    --Parse out UI folders into a folder hierarchy

    with [UIFOLDERS] as
    (
      select distinct
        case [UIFOLDER]
          when '' then 'Other\' + kc.[NAME]
          else [UIFOLDER] + '\' + kc.[NAME]
        end [UIFOLDER],
        case [UIFOLDER]
          when '' then len('Other\' + kc.[NAME]) + 1
          else len([UIFOLDER] + '\' + kc.[NAME]) + 1
        end [LEN]
      from dbo.[KPICATALOG] kc
      inner join @KPISTOINCLUDE ki on kc.[ID] = ki.[KPICATALOGID]
    ), [HIERARCHY] as
    (
      select
                f.[UIFOLDER],
        cast(substring(f.[UIFOLDER], 1, coalesce(nullif(charindex('\', f.[UIFOLDER], 1), 0), f.[LEN]) - 1) as nvarchar(255)) [PATH],
        f.[LEN],
        cast(1 as int) [START],    
        coalesce(nullif(charindex('\', f.[UIFOLDER], 1),0), f.[LEN]) [END],
        cast('00000000-0000-0000-0000-000000000000' as nvarchar(255)) [PARENT],
        substring(f.[UIFOLDER], 1, coalesce(nullif(charindex('\', f.[UIFOLDER], 1), 0), f.[LEN]) - 1) [FOLDER]    
      from [UIFOLDERS] f
      union all
      select
                h.[UIFOLDER],
        cast(h.[PATH] + '\' + substring(h.[UIFOLDER], h.[END] + 1, coalesce(nullif(charindex('\', h.[UIFOLDER], h.[END] + 1), 0), h.[LEN]) - h.[END] - 1) as nvarchar(255)) [PATH],
        h.[LEN],
        h.[END] + 1 [START],
        coalesce(nullif(charindex('\', h.[UIFOLDER], h.[END] + 1), 0), h.[LEN]) [END],
        h.[PATH] [PARENT],
        substring(h.[UIFOLDER], h.[END] + 1, coalesce(nullif(charindex('\', h.[UIFOLDER], h.[END] + 1), 0), h.[LEN]) - h.[END] - 1) [FOLDER]  
      from [HIERARCHY] h
      where h.[END] < h.[LEN]
    ), [DISTINCT] as
    (
      select distinct
        [PATH], 
        [FOLDER], 
        [PARENT]
      from [HIERARCHY]
    )
    insert into @TYPES ([PATH], [FOLDERNAME], [PARENTPATH], [DISPLAYORDER])        
            select
                [PATH], 
                [FOLDER], 
                [PARENT],
                row_number() over(order by [PATH])
            from [DISTINCT]
            union all
            select '00000000-0000-0000-0000-000000000000', 'root', '00000000-0000-0000-0000-000000000003', 1;            

      insert into @NODES([NODEID], [PARENTNODEID], [NODENAME], [DISPLAYORDER], [COUNT])
          select  
            t.[PATH],
            t.[PARENTPATH],
            t.[FOLDERNAME],
            t.[DISPLAYORDER],
            count(distinct k.[KPIINSTANCEID])
          from @TYPES t
          left join dbo.[KPICATALOG] c on coalesce(nullif(c.[UIFOLDER], ''), 'Other') + '\' + c.[NAME] = t.[PATH]
          left join @KPISTOINCLUDE k on k.[KPICATALOGID] = c.[ID]
          group by  
            t.[PATH],
            t.[PARENTPATH],
            t.[FOLDERNAME],
            t.[DISPLAYORDER]; 
  end
  --Rollup by categories

  else if @GROUPBY = 2
  begin
      declare @CATEGORIES table
      (
          [ID] uniqueidentifier,
          [PARENTID] uniqueidentifier,
          [DESCRIPTION] nvarchar(max),
          [DISPLAYORDER] int
      );

        insert into @CATEGORIES ([ID], [PARENTID], [DESCRIPTION], [DISPLAYORDER])
            select 
                r.[ID],
                '00000000-0000-0000-0000-000000000000' [PARENTID],
                r.[NAME],
                row_number() over(order by r.[NAME])
            from dbo.[RECORDTYPE] r
          inner join (
            select distinct [RECORDTYPEID]
            from @KPISTOINCLUDE
          ) k on r.[ID] = k.[RECORDTYPEID]
            union all
            select '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000003', 'root', 1
            union all
            select '00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000000', '<none>', 0;

      insert into @NODES([NODEID], [PARENTNODEID], [NODENAME], [DISPLAYORDER], [COUNT])
          select  
            c.[ID],
            c.[PARENTID],
            c.[DESCRIPTION],
            c.[DISPLAYORDER],
            count(distinct k.[KPIINSTANCEID])
          from @CATEGORIES c
          left join @KPISTOINCLUDE k on k.[RECORDTYPEID] = c.[ID]
          group by  
            c.[ID],
            c.[PARENTID],
            c.[DESCRIPTION],
            c.[DISPLAYORDER]; 
  end

    /* Favorites */
    insert into @NODES([NODEID], [PARENTNODEID], [NODENAME], [DISPLAYORDER], [COUNT])
    select
      '00000000-0000-0000-0000-000000000002',
      '00000000-0000-0000-0000-000000000003',
      'Favorite',
      0,
      count(distinct k.[KPIINSTANCEID])
    from @KPISTOINCLUDE k
    where k.[ISFAVORITE] = 1;

    ---------

    /* rollup node counts */
    with [ROLLUP] as
    (  
      select 
        f.[NODEID] [PARENTNODEID],
        f.[NODEID],
        f.[COUNT]
      from @NODES f

      union all 

      select 
        r.[PARENTNODEID],
        f.[NODEID],
        f.[COUNT]
      from @NODES f
      inner join [ROLLUP] r on f.[PARENTNODEID] = r.[NODEID]
    )  
    select
      f.[NODEID],
      f.[PARENTNODEID],
      f.[NODENAME],
      f.[DISPLAYORDER],
      sum(r.[COUNT]) [COUNT]
    from [ROLLUP] r
    inner join @NODES f on r.[PARENTNODEID] = f.[NODEID]
    group by 
      f.[NODEID],
      f.[PARENTNODEID],
      f.[NODENAME],
      f.[DISPLAYORDER];