USP_DATAFORMTEMPLATE_EDIT_MANAGEKPIINSTANCEFOLDERS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MANAGEKPIINSTANCEFOLDERS
(
  @HIERARCHY xml,
  @CHANGEAGENTID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as

  set nocount on;

  if @CHANGEAGENTID is null  
    exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate()

  begin try
    declare @contextCache varbinary(128);

    --cache current context information
    set @contextCache = CONTEXT_INFO();

    --set CONTEXT_INFO to @CHANGEAGENTID
    set CONTEXT_INFO @CHANGEAGENTID;

    declare @KPIFOLDERHIERARCHY table
    (
      [FOLDERID] uniqueidentifier,
      [FOLDERNAME] nvarchar(100),
      [PARENTFOLDERID] uniqueidentifier,
      [DISPLAYORDER] int
    )

    -- Use OpenXML when dealing with potentially large well formed xml documents
    -- Some Infinity users have 3000+ KPIs.
    DECLARE @idoc INT
    EXEC sp_xml_preparedocument @idoc OUTPUT, @HIERARCHY

    insert into @KPIFOLDERHIERARCHY
    (
      [FOLDERID],
      [FOLDERNAME],
      [PARENTFOLDERID],
      [DISPLAYORDER]
    )
    select 
      [FOLDERID],
      [FOLDERNAME],
      [PARENTFOLDERID],
      [DISPLAYORDER]
    from OPENXML (@idoc, '/HIERARCHY/ITEM')
    with ([FOLDERID]  uniqueidentifier 'FOLDERID',
      [PARENTFOLDERID] uniqueidentifier 'PARENTFOLDERID',
      [FOLDERNAME] nvarchar(100) 'FOLDERNAME',
      [DISPLAYORDER] bigint 'DISPLAYORDER') R

    exec sp_xml_removedocument @idoc

    --Remove all queries and favorites
    delete from @KPIFOLDERHIERARCHY where [FOLDERID] = '00000000-0000-0000-0000-000000000000'

    -- Clear the parent folder references for folder's that are about to be deleted and whose parent
    -- is about to be deleted as well to prevent a FK violation.  We don't clear the parent folder reference
    -- for folders that aren't about to be deleted but whose parent is because that should generate
    -- a foreign key violation.  This update needs to run before updating the display orders
    -- since a folder may be updated to use the display order of a deleted item.
    update dbo.[KPIINSTANCEFOLDER] set
      [PARENTFOLDERID] = null,
      -- Need to update the display order since clearing the parent folder could cause duplicate display 
      -- orders for the root level
      [DISPLAYORDER] = [NEWDISPLAYORDER].[DISPLAYORDER], 
      [DATECHANGED] = @CURRENTDATE,
      [CHANGEDBYID] = @CHANGEAGENTID
    from dbo.[KPIINSTANCEFOLDER]
    inner join 
    (
      select
        [ID],
        coalesce(
        (
          select max([DISPLAYORDER]) 
          from 
          (
            select [DISPLAYORDER], [PARENTFOLDERID] from dbo.[KPIINSTANCEFOLDER]
            union all
            select [DISPLAYORDER], [PARENTFOLDERID] from @KPIFOLDERHIERARCHY
          ) as [DERIVEDTABLE]
          where [PARENTFOLDERID] is null
        ), 0) + row_number() over (order by NAME) as [DISPLAYORDER]
      from dbo.[KPIINSTANCEFOLDER]
    ) as [NEWDISPLAYORDER] on [KPIINSTANCEFOLDER].[ID] = [NEWDISPLAYORDER].[ID]
    where 
      [KPIINSTANCEFOLDER].[ID] not in (select [FOLDERID] from @KPIFOLDERHIERARCHY) and
      (
        [KPIINSTANCEFOLDER].[PARENTFOLDERID] not in (select [FOLDERID] from @KPIFOLDERHIERARCHY) or
        -- Include folders that don't have a parent since we still need to update their display order
        [KPIINSTANCEFOLDER].[PARENTFOLDERID] is null
      )


    -- Set the display order on existing folders to a temporary value so it won't conflict with any of the new folder's display order values.
    -- The existing folder's values will be updated after inserting the new folders since an existing folder's
    -- parent may be a new folder and so that row will need to be inserted first.
    update dbo.[KPIINSTANCEFOLDER] set
      [DISPLAYORDER] = [NEWDISPLAYORDER].[DISPLAYORDER],
      [DATECHANGED] = @CURRENTDATE,
      [CHANGEDBYID] = @CHANGEAGENTID
    from dbo.[KPIINSTANCEFOLDER]
    inner join @KPIFOLDERHIERARCHY [UPDATEDFOLDERS] on [KPIINSTANCEFOLDER].ID = [UPDATEDFOLDERS].[FOLDERID]
    inner join 
    (
      select
        [ID],
        coalesce(
        (
          select max([DISPLAYORDER]) 
          from 
          (
            select [DISPLAYORDER], [PARENTFOLDERID] from dbo.[KPIINSTANCEFOLDER]
            union all
            select [DISPLAYORDER], [PARENTFOLDERID] from @KPIFOLDERHIERARCHY
          ) as [DERIVEDTABLE]
          where 
            ([PARENTFOLDERID] is null and [KPIINSTANCEFOLDER].[PARENTFOLDERID] is null) or
            [PARENTFOLDERID] = [KPIINSTANCEFOLDER].[PARENTFOLDERID]
        ), 0) + row_number() over (order by [NAME]) as [DISPLAYORDER]
      from dbo.[KPIINSTANCEFOLDER]
    ) as [NEWDISPLAYORDER] on [KPIINSTANCEFOLDER].[ID] = [NEWDISPLAYORDER].[ID]

    update @KPIFOLDERHIERARCHY set [FOLDERID] = newid() where [FOLDERID] is null

    declare @INSERTEDFOLDERS table
    (
      [FOLDERID] uniqueidentifier
    )

    insert into @INSERTEDFOLDERS ([FOLDERID])
    select
      [FOLDERID]
    from @KPIFOLDERHIERARCHY
    where
      [FOLDERID] not in (select [ID] from dbo.[KPIINSTANCEFOLDER])

    -- Insert new folders.  Insert needs to take place first in case a previously existing
    -- folder was moved to be a child of a new folder.
    insert into dbo.[KPIINSTANCEFOLDER] 
    (
      [ID], 
      [NAME], 
      [PARENTFOLDERID], 
      [DISPLAYORDER],
      [DATEADDED], 
      [DATECHANGED], 
      [ADDEDBYID], 
      [CHANGEDBYID]
    )
    select
      case when [HIERARCHY].[FOLDERID] is null then newid() else [HIERARCHY].[FOLDERID] end as [ID],
      [FOLDERNAME] as [NAME],
       -- Have to insert null in case the parent folder hasn't been inserted yet.  The update statement below 
       -- will update to the correct parent folder.
      null,
      -- Use a temporary display order value since a collision could occur if the real value was used since the parent
      -- folders aren't set yet.
      coalesce(
        (
          select max([DISPLAYORDER]) 
          from dbo.[KPIINSTANCEFOLDER]
          where [PARENTFOLDERID] is null
        ), 0) + row_number() over (order by [FOLDERNAME]),
      @CURRENTDATE, 
      @CURRENTDATE, 
      @CHANGEAGENTID, 
      @CHANGEAGENTID
    from @KPIFOLDERHIERARCHY [HIERARCHY]
    inner join @INSERTEDFOLDERS [INSERTEDFOLDERS] on [HIERARCHY].[FOLDERID] = [INSERTEDFOLDERS].[FOLDERID]

    -- Update the existing folders
    update dbo.[KPIINSTANCEFOLDER] set
      [NAME] = [UPDATEDFOLDERS].[FOLDERNAME],
      [PARENTFOLDERID] = [UPDATEDFOLDERS].[PARENTFOLDERID],
      -- This value is updated again since when the new folders are inserted, a dummy value
      -- is used for display order to avoid collisions when the correct parent folder isn't set yet.
      [DISPLAYORDER] = [UPDATEDFOLDERS].[DISPLAYORDER],
      [DATECHANGED] = @CURRENTDATE,
      [CHANGEDBYID] = @CHANGEAGENTID
    from dbo.[KPIINSTANCEFOLDER]
    inner join @KPIFOLDERHIERARCHY [UPDATEDFOLDERS] on [KPIINSTANCEFOLDER].[ID] = [UPDATEDFOLDERS].[FOLDERID]


    -- Finally, delete folders
    delete from dbo.[KPIINSTANCEFOLDER]
    from dbo.[KPIINSTANCEFOLDER]
    where [KPIINSTANCEFOLDER].[ID] not in (select [FOLDERID] from @KPIFOLDERHIERARCHY);

    --reset CONTEXT_INFO to previous value
    if not @contextCache is null
      set CONTEXT_INFO @contextCache;

  end try
  begin catch
    exec dbo.[USP_RAISE_ERROR]
    return 1
  end catch

return 0;