USP_DATAFORMTEMPLATE_EDIT_MANAGEQUERYFOLDERS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MANAGEQUERYFOLDERS
(
  @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 @QUERYFOLDERHIERARCHY table
    (
      [FOLDERID] uniqueidentifier primary key,
      [FOLDERNAME] nvarchar(100),
      [PARENTFOLDERID] uniqueidentifier,
      [DISPLAYORDER] int,
      [CUSTOMPERMISSIONSSET] bit,
      [APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS] bit,
      [SECURITYLEVELCODE] tinyint,
      [QUERYDEFAULTALLOWALLUSERSTORUN] bit,
      [QUERYDEFAULTALLOWALLUSERSTOEDIT] bit,
      [SMARTQUERYDEFAULTALLOWALLUSERSTORUN] bit,
      [SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT] bit,
      [APPLYQUERYDEFAULTSTOEXISTINGQUERIES] bit,
      [FOLDERTOINHERITPERMISSIONSFROM] uniqueidentifier,
      [SYSTEMROLELIST] xml,
      [QUERYDEFAULTRUNROLES] xml,
      [QUERYDEFAULTEDITROLES] xml,
      [SMARTQUERYDEFAULTRUNROLES] xml
    )

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

    insert into @QUERYFOLDERHIERARCHY
    (
      [FOLDERID],
      [FOLDERNAME],
      [PARENTFOLDERID],
      [DISPLAYORDER],
      [CUSTOMPERMISSIONSSET],
      [APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS],
      [SECURITYLEVELCODE],
      [QUERYDEFAULTALLOWALLUSERSTORUN],
      [QUERYDEFAULTALLOWALLUSERSTOEDIT],
      [SMARTQUERYDEFAULTALLOWALLUSERSTORUN],
      [SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT],
      [APPLYQUERYDEFAULTSTOEXISTINGQUERIES],
      [FOLDERTOINHERITPERMISSIONSFROM],
      [SYSTEMROLELIST],
      [QUERYDEFAULTRUNROLES],
      [QUERYDEFAULTEDITROLES],
      [SMARTQUERYDEFAULTRUNROLES]
    )
    select 
      [FOLDERID],
      [FOLDERNAME],
      [PARENTFOLDERID],
      [DISPLAYORDER],
      [CUSTOMPERMISSIONSSET],
      [APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS],
      [SECURITYLEVELCODE],
      [QUERYDEFAULTALLOWALLUSERSTORUN],
      [QUERYDEFAULTALLOWALLUSERSTOEDIT],
      [SMARTQUERYDEFAULTALLOWALLUSERSTORUN],
      [SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT],
      [APPLYQUERYDEFAULTSTOEXISTINGQUERIES],
      [FOLDERTOINHERITPERMISSIONSFROM],
      [SYSTEMROLELIST],
      [QUERYDEFAULTRUNROLES],
      [QUERYDEFAULTEDITROLES],
      [SMARTQUERYDEFAULTRUNROLES]
    from OPENXML (@idoc, '/HIERARCHY/ITEM')
    with ([FOLDERID]  uniqueidentifier 'FOLDERID',
      [PARENTFOLDERID] uniqueidentifier 'PARENTFOLDERID',
      [FOLDERNAME] nvarchar(100) 'FOLDERNAME',
      [DISPLAYORDER] bigint 'DISPLAYORDER',
      [CUSTOMPERMISSIONSSET] bit 'CUSTOMPERMISSIONSSET',
      [APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS] bit 'APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS',
      [SECURITYLEVELCODE] tinyint 'SECURITYLEVELCODE',
      [QUERYDEFAULTALLOWALLUSERSTORUN] bit 'QUERYDEFAULTALLOWALLUSERSTORUN',
      [QUERYDEFAULTALLOWALLUSERSTOEDIT] bit 'QUERYDEFAULTALLOWALLUSERSTOEDIT',
      [SMARTQUERYDEFAULTALLOWALLUSERSTORUN] bit 'SMARTQUERYDEFAULTALLOWALLUSERSTORUN',
      [SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT] bit 'SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT',
      [APPLYQUERYDEFAULTSTOEXISTINGQUERIES] bit 'APPLYQUERYDEFAULTSTOEXISTINGQUERIES',
      [FOLDERTOINHERITPERMISSIONSFROM] uniqueidentifier 'FOLDERTOINHERITPERMISSIONSFROM',
      [SYSTEMROLELIST] xml 'SYSTEMROLELIST',
      [QUERYDEFAULTRUNROLES] xml 'QUERYDEFAULTRUNROLES',
      [QUERYDEFAULTEDITROLES] xml 'QUERYDEFAULTEDITROLES',
      [SMARTQUERYDEFAULTRUNROLES] xml 'SMARTQUERYDEFAULTRUNROLES') R

    exec sp_xml_removedocument @idoc

    --Remove all queries and favorites
    delete from @QUERYFOLDERHIERARCHY 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/query may be updated to use the display order of a deleted item.
    update dbo.[ADHOCQUERYFOLDER] 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.[ADHOCQUERYFOLDER]
    inner join @QUERYFOLDERHIERARCHY [QFH] on [ADHOCQUERYFOLDER].ID = [QFH].[FOLDERID]
    inner join 
    (
      select
        [ID],
        coalesce(
        (
          select max([DISPLAYORDER]) 
          from 
          (
            select [DISPLAYORDER], [PARENTFOLDERID] from dbo.[ADHOCQUERYFOLDER]
            union all
            select [DISPLAYORDER], [PARENTFOLDERID] from @QUERYFOLDERHIERARCHY
          ) as [DERIVEDTABLE]
          where [PARENTFOLDERID] is null
        ), 0) + row_number() over (order by NAME) as [DISPLAYORDER]
      from dbo.[ADHOCQUERYFOLDER]
    ) as [NEWDISPLAYORDER] on [ADHOCQUERYFOLDER].[ID] = [NEWDISPLAYORDER].[ID]
    where 
      [ADHOCQUERYFOLDER].[ID] not in (select [FOLDERID] from @QUERYFOLDERHIERARCHY) and
      (
        [ADHOCQUERYFOLDER].[PARENTFOLDERID] not in (select [FOLDERID] from @QUERYFOLDERHIERARCHY) or
        -- Include query folders that don't have a parent since we still need to update their display order
        [ADHOCQUERYFOLDER].[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.[ADHOCQUERYFOLDER] set
      [DISPLAYORDER] = [NEWDISPLAYORDER].[DISPLAYORDER],
      [DATECHANGED] = @CURRENTDATE,
      [CHANGEDBYID] = @CHANGEAGENTID
    from dbo.[ADHOCQUERYFOLDER]
    inner join @QUERYFOLDERHIERARCHY [UPDATEDFOLDERS] on [ADHOCQUERYFOLDER].ID = [UPDATEDFOLDERS].[FOLDERID]
    inner join 
    (
      select
        [ID],
        coalesce(
        (
          select max([DISPLAYORDER]) 
          from 
          (
            select [DISPLAYORDER], [PARENTFOLDERID] from dbo.[ADHOCQUERYFOLDER]
            union all
            select [DISPLAYORDER], [PARENTFOLDERID] from @QUERYFOLDERHIERARCHY
          ) as [DERIVEDTABLE]
          where 
            ([PARENTFOLDERID] is null and [ADHOCQUERYFOLDER].[PARENTFOLDERID] is null) or
            [PARENTFOLDERID] = [ADHOCQUERYFOLDER].[PARENTFOLDERID]
        ), 0) + row_number() over (order by [NAME]) as [DISPLAYORDER]
      from dbo.[ADHOCQUERYFOLDER]
    ) as [NEWDISPLAYORDER] on [ADHOCQUERYFOLDER].[ID] = [NEWDISPLAYORDER].[ID]

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

    declare @INSERTEDFOLDERS table
    (
      [FOLDERID] uniqueidentifier
    )

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

    -- 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.[ADHOCQUERYFOLDER] 
    (
      [ID], 
      [NAME], 
      [PARENTFOLDERID], 
      [DISPLAYORDER],
      [OWNERID],
      [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.[ADHOCQUERYFOLDER]
          where [PARENTFOLDERID] is null
        ), 0) + row_number() over (order by [FOLDERNAME]),
      @CURRENTAPPUSERID,
      @CURRENTDATE, 
      @CURRENTDATE, 
      @CHANGEAGENTID, 
      @CHANGEAGENTID
    from @QUERYFOLDERHIERARCHY [HIERARCHY]
    inner join @INSERTEDFOLDERS [INSERTEDFOLDERS] on [HIERARCHY].[FOLDERID] = [INSERTEDFOLDERS].[FOLDERID]


    -- Store the closest ancestor that defaults permissions for a folder
    -- so the folders permissions can be updated if it changes
    declare @ORIGINALFOLDERANDDEFAULTINGANCESTOR table
    (
      [FOLDERID] uniqueidentifier not null,
      [DEFAULTINGANCESTOR] uniqueidentifier not null
    )

    insert into @ORIGINALFOLDERANDDEFAULTINGANCESTOR ([FOLDERID], [DEFAULTINGANCESTOR])
    select 
      [FOLDERID],
      [DEFAULTINGANCESTOR]
    from
    (
      select
        [ADHOCQUERYFOLDER].[ID] as [FOLDERID],
        dbo.[UFN_ADHOCQUERYFOLDER_GETCLOSESTANCESTORDEFAULTINGPERMISSIONS]([ADHOCQUERYFOLDER].[ID]) as [DEFAULTINGANCESTOR]
      from dbo.[ADHOCQUERYFOLDER]
    ) as [FOLDERWITHANCESTOR]
    where
      [DEFAULTINGANCESTOR] is not null

    -- Update the existing folders
    update dbo.[ADHOCQUERYFOLDER] 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],
      -- Only update permissions values if the user specifically set the values
      [APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS] = case when [CUSTOMPERMISSIONSSET] = 1 then [UPDATEDFOLDERS].[APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS] else [ADHOCQUERYFOLDER].[APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS] end,
      [SECURITYLEVELCODE] = case when [CUSTOMPERMISSIONSSET] = 1 then [UPDATEDFOLDERS].[SECURITYLEVELCODE] else [ADHOCQUERYFOLDER].[SECURITYLEVELCODE] end,
      [QUERYDEFAULTALLOWALLUSERSTORUN] = case when [CUSTOMPERMISSIONSSET] = 1 then [UPDATEDFOLDERS].[QUERYDEFAULTALLOWALLUSERSTORUN] else [ADHOCQUERYFOLDER].[QUERYDEFAULTALLOWALLUSERSTORUN] end,
      [QUERYDEFAULTALLOWALLUSERSTOEDIT] = case when [CUSTOMPERMISSIONSSET] = 1 then [UPDATEDFOLDERS].[QUERYDEFAULTALLOWALLUSERSTOEDIT] else [ADHOCQUERYFOLDER].[QUERYDEFAULTALLOWALLUSERSTOEDIT] end,
      [DATECHANGED] = @CURRENTDATE,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [SMARTQUERYDEFAULTALLOWALLUSERSTORUN] = case when [CUSTOMPERMISSIONSSET] = 1 then [UPDATEDFOLDERS].[SMARTQUERYDEFAULTALLOWALLUSERSTORUN] else [ADHOCQUERYFOLDER].[SMARTQUERYDEFAULTALLOWALLUSERSTORUN] end,
      [SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT] = case when [CUSTOMPERMISSIONSSET] = 1 then [UPDATEDFOLDERS].[SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT] else [ADHOCQUERYFOLDER].[SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT] end
    from dbo.[ADHOCQUERYFOLDER]
    inner join @QUERYFOLDERHIERARCHY [UPDATEDFOLDERS] on [ADHOCQUERYFOLDER].[ID] = [UPDATEDFOLDERS].[FOLDERID]

    -- Save manually set permissions for folders
    delete from dbo.[SYSTEMROLEPERM_ADHOCQUERYFOLDER]
    where 
      [ADHOCQUERYFOLDERID] in
      (
        select
          [HIERARCHY].[FOLDERID]
        from @QUERYFOLDERHIERARCHY [HIERARCHY]
        cross apply [SYSTEMROLELIST].nodes('/SYSTEMROLELIST/ITEM') T(c)                            
        where
          [HIERARCHY].[CUSTOMPERMISSIONSSET] = 1
      )

    delete from dbo.[ADHOCQUERYFOLDERDEFAULTQUERYROLE]
    where 
      [ADHOCQUERYFOLDERID] in
      (
        select
          [HIERARCHY].[FOLDERID]
        from @QUERYFOLDERHIERARCHY [HIERARCHY]
        cross apply [SYSTEMROLELIST].nodes('/SYSTEMROLELIST/ITEM') T(c)                            
        where
          [HIERARCHY].[CUSTOMPERMISSIONSSET] = 1
      )

    insert into dbo.[SYSTEMROLEPERM_ADHOCQUERYFOLDER]
    (
      [SYSTEMROLEID],
      [ADHOCQUERYFOLDERID],
      [GRANTORDENY],
      [ADDEDBYID], 
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select
      T.c.value('(SYSTEMROLEID)[1]','uniqueidentifier'),
      [HIERARCHY].[FOLDERID],
      T.c.value('(GRANTORDENY)[1]','tinyint'),
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from @QUERYFOLDERHIERARCHY [HIERARCHY]
    cross apply [SYSTEMROLELIST].nodes('/SYSTEMROLELIST/ITEM') T(c)
    where
      [HIERARCHY].[CUSTOMPERMISSIONSSET] = 1 and
      [SECURITYLEVELCODE] = 1 and
      T.c.value('(GRANTORDENY)[1]','tinyint') <> 2 -- Not equal to None

    insert into dbo.[ADHOCQUERYFOLDERDEFAULTQUERYROLE]
    (
      [SYSTEMROLEID],
      [ADHOCQUERYFOLDERID],
      [GRANTORDENY],
      [PERMISSIONTYPECODE],
      [ADDEDBYID], 
      [CHANGEDBYID], 
      [DATEADDED], 
      [DATECHANGED]
    )
    select
      T.c.value('(SYSTEMROLEID)[1]','uniqueidentifier'),
      [HIERARCHY].[FOLDERID],
      T.c.value('(GRANTORDENY)[1]','tinyint'),
      0, -- Run
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE                                
    from @QUERYFOLDERHIERARCHY [HIERARCHY]
    cross apply QUERYDEFAULTRUNROLES.nodes('/QUERYDEFAULTRUNROLES/ITEM') T(c)
    where
      [HIERARCHY].[CUSTOMPERMISSIONSSET] = 1 and
      [QUERYDEFAULTALLOWALLUSERSTORUN] = 0 and
      T.c.value('(GRANTORDENY)[1]','tinyint') <> 2 -- Not equal to None
    union all
    select
      T.c.value('(SYSTEMROLEID)[1]','uniqueidentifier'),
      [HIERARCHY].[FOLDERID],
      T.c.value('(GRANTORDENY)[1]','tinyint'),
      1, -- Edit
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE                                
    from @QUERYFOLDERHIERARCHY [HIERARCHY]
    cross apply [QUERYDEFAULTEDITROLES].nodes('/QUERYDEFAULTEDITROLES/ITEM') T(c)
    where
      [HIERARCHY].[CUSTOMPERMISSIONSSET] = 1 and
      [QUERYDEFAULTALLOWALLUSERSTOEDIT] = 0 and
      T.c.value('(GRANTORDENY)[1]','tinyint') <> 2 -- Not equal to None
    union all
    select
      T.c.value('(SYSTEMROLEID)[1]','uniqueidentifier'),
      [HIERARCHY].[FOLDERID],
      T.c.value('(GRANTORDENY)[1]','tinyint'),
      2, -- RunSmartQuery
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE                                
    from @QUERYFOLDERHIERARCHY [HIERARCHY]
    cross apply [SMARTQUERYDEFAULTRUNROLES].nodes('/SMARTQUERYDEFAULTRUNROLES/ITEM') T(c)
    where
      [HIERARCHY].[CUSTOMPERMISSIONSSET] = 1 and
      [SMARTQUERYDEFAULTALLOWALLUSERSTORUN] = 0 and
      T.c.value('(GRANTORDENY)[1]','tinyint') <> 2 -- Not equal to None


    -- Update folder permissions for new folders and folders who were either moved,
    -- their ancestor was moved and it updated the ancestor they should inherit permissions from,
    -- or their ancestor had its permissions updated
    declare @FOLDERANDDEFAULTINGANCESTORTOUPDATE table
    (
      [FOLDERID] uniqueidentifier not null,
      [DEFAULTINGANCESTOR] uniqueidentifier not null
    )

    insert into @FOLDERANDDEFAULTINGANCESTORTOUPDATE ([FOLDERID], [DEFAULTINGANCESTOR])
    select
      [FOLDERWITHANCESTOR].[FOLDERID],
      [FOLDERWITHANCESTOR].[DEFAULTINGANCESTOR]
    from 
    (
      select
        [ADHOCQUERYFOLDER].[ID] as [FOLDERID],
        case 
          when [FOLDERTOINHERITPERMISSIONSFROM] is not null then [FOLDERTOINHERITPERMISSIONSFROM]
          else dbo.[UFN_ADHOCQUERYFOLDER_GETCLOSESTANCESTORDEFAULTINGPERMISSIONS]([ADHOCQUERYFOLDER].[ID])
        end as [DEFAULTINGANCESTOR],
        [UPDATEDFOLDERS].[CUSTOMPERMISSIONSSET],
        case
          when [FOLDERTOINHERITPERMISSIONSFROM] is not null then 1
          else 0
        end as [ISFOLDERTOINHERITPERMISSIONSFROMSET]
      from dbo.[ADHOCQUERYFOLDER]
      inner join @QUERYFOLDERHIERARCHY [UPDATEDFOLDERS] on [ADHOCQUERYFOLDER].[ID] = [UPDATEDFOLDERS].[FOLDERID]
    ) as [FOLDERWITHANCESTOR]
    left join @ORIGINALFOLDERANDDEFAULTINGANCESTOR [ORIGINALFOLDERANDDEFAULTINGANCESTOR] -- Left joining so that new folders or folders that weren't defaulting but are now will be included
      on [ORIGINALFOLDERANDDEFAULTINGANCESTOR].[FOLDERID] = [FOLDERWITHANCESTOR].[FOLDERID]
    where
      [FOLDERWITHANCESTOR].[CUSTOMPERMISSIONSSET] = 0 and -- Don't include folders that had their permissions set manually
      (
        [ISFOLDERTOINHERITPERMISSIONSFROMSET] = 1 or
        ([ORIGINALFOLDERANDDEFAULTINGANCESTOR].[DEFAULTINGANCESTOR] <> [FOLDERWITHANCESTOR].[DEFAULTINGANCESTOR]) or
        ([ORIGINALFOLDERANDDEFAULTINGANCESTOR].[DEFAULTINGANCESTOR] is null and [FOLDERWITHANCESTOR].[DEFAULTINGANCESTOR] is not null)                                    
      )

    delete from dbo.[SYSTEMROLEPERM_ADHOCQUERYFOLDER]
    where [ADHOCQUERYFOLDERID] in (select [FOLDERID] from @FOLDERANDDEFAULTINGANCESTORTOUPDATE)

    delete from dbo.[ADHOCQUERYFOLDERDEFAULTQUERYROLE]
    where [ADHOCQUERYFOLDERID] in (select [FOLDERID] from @FOLDERANDDEFAULTINGANCESTORTOUPDATE)

    update dbo.[ADHOCQUERYFOLDER] set
      [SECURITYLEVELCODE] = [ANCESTORPERMISSIONS].[SECURITYLEVELCODE],
      [QUERYDEFAULTALLOWALLUSERSTORUN] = [ANCESTORPERMISSIONS].[QUERYDEFAULTALLOWALLUSERSTORUN],
      [QUERYDEFAULTALLOWALLUSERSTOEDIT] = [ANCESTORPERMISSIONS].[QUERYDEFAULTALLOWALLUSERSTOEDIT],
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE,
      [SMARTQUERYDEFAULTALLOWALLUSERSTORUN] = [ANCESTORPERMISSIONS].[SMARTQUERYDEFAULTALLOWALLUSERSTORUN],
      [SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT] = [ANCESTORPERMISSIONS].[SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT]
    from dbo.[ADHOCQUERYFOLDER]
    inner join @FOLDERANDDEFAULTINGANCESTORTOUPDATE [FOLDERANDDEFAULTINGANCESTORTOUPDATE] on [ADHOCQUERYFOLDER].ID = [FOLDERANDDEFAULTINGANCESTORTOUPDATE].[FOLDERID]
    inner join dbo.[ADHOCQUERYFOLDER] [ANCESTORPERMISSIONS] on [ANCESTORPERMISSIONS].[ID] = [FOLDERANDDEFAULTINGANCESTORTOUPDATE].[DEFAULTINGANCESTOR]

    insert into dbo.[SYSTEMROLEPERM_ADHOCQUERYFOLDER]
    (
      [SYSTEMROLEID],
      [ADHOCQUERYFOLDERID],
      [GRANTORDENY],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select
      [SYSTEMROLEPERM_ADHOCQUERYFOLDER].[SYSTEMROLEID],
      [FOLDERANDDEFAULTINGANCESTORTOUPDATE].[FOLDERID],
      [SYSTEMROLEPERM_ADHOCQUERYFOLDER].[GRANTORDENY],
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from @FOLDERANDDEFAULTINGANCESTORTOUPDATE [FOLDERANDDEFAULTINGANCESTORTOUPDATE]
    inner join dbo.[SYSTEMROLEPERM_ADHOCQUERYFOLDER] on [FOLDERANDDEFAULTINGANCESTORTOUPDATE].[DEFAULTINGANCESTOR] = [SYSTEMROLEPERM_ADHOCQUERYFOLDER].[ADHOCQUERYFOLDERID]

    insert into dbo.[ADHOCQUERYFOLDERDEFAULTQUERYROLE]
    (
      [SYSTEMROLEID],
      [ADHOCQUERYFOLDERID],
      [GRANTORDENY],
      [PERMISSIONTYPECODE],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select
      [ADHOCQUERYFOLDERDEFAULTQUERYROLE].[SYSTEMROLEID],
      [FOLDERANDDEFAULTINGANCESTORTOUPDATE].[FOLDERID],
      [ADHOCQUERYFOLDERDEFAULTQUERYROLE].[GRANTORDENY],
      [ADHOCQUERYFOLDERDEFAULTQUERYROLE].[PERMISSIONTYPECODE],
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from @FOLDERANDDEFAULTINGANCESTORTOUPDATE [FOLDERANDDEFAULTINGANCESTORTOUPDATE]
    inner join dbo.[ADHOCQUERYFOLDERDEFAULTQUERYROLE] on [FOLDERANDDEFAULTINGANCESTORTOUPDATE].[DEFAULTINGANCESTOR] = [ADHOCQUERYFOLDERDEFAULTQUERYROLE].[ADHOCQUERYFOLDERID]

    -- Default permissions for folders that were added but didn't inherit permissions
    update dbo.[ADHOCQUERYFOLDER] set
      [SECURITYLEVELCODE] = 0, -- All users
      [QUERYDEFAULTALLOWALLUSERSTORUN] = 1,
      [QUERYDEFAULTALLOWALLUSERSTOEDIT] = 1,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE,
      [SMARTQUERYDEFAULTALLOWALLUSERSTORUN] = 1,
      [SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT] = 1
    from dbo.[ADHOCQUERYFOLDER]
    inner join @INSERTEDFOLDERS [INSERTEDFOLDERS] on [ADHOCQUERYFOLDER].[ID] = [INSERTEDFOLDERS].[FOLDERID]
    inner join @QUERYFOLDERHIERARCHY [HIERARCHY] on [HIERARCHY].[FOLDERID] = [INSERTEDFOLDERS].[FOLDERID]
    where
      [HIERARCHY].[CUSTOMPERMISSIONSSET] = 0 and
      [ADHOCQUERYFOLDER].[ID] not in (select [FOLDERID] from @FOLDERANDDEFAULTINGANCESTORTOUPDATE)

    -- Update permission for queries whose ancestor has APPLYQUERYDEFAULTSTOEXISTINGQUERIES set to true
    declare @QUERIESTOUPDATEPERMISSIONS table
    (
      [QUERYID] uniqueidentifier not null,
      [FOLDERID] uniqueidentifier not null,
      [ITEMTYPE] bit not null
    )

    insert into @QUERIESTOUPDATEPERMISSIONS ([QUERYID], [FOLDERID], [ITEMTYPE])
      select
        [ADHOCQUERY].[ID],
        [ANCESTORTODEFAULTFROM].[ID] as [FOLDERID],
        0 as [ITEMTYPE]
      from dbo.[ADHOCQUERY]
      cross apply
        (
          select top 1
            [ANCESTORS].[ID]
          from dbo.[UFN_ADHOCQUERY_GETANCESTORS]([ADHOCQUERY].[FOLDERID]) [ANCESTORS]
          inner join @QUERYFOLDERHIERARCHY [HIERARCHY] on [HIERARCHY].[FOLDERID] = [ANCESTORS].[ID] 
          where
            [HIERARCHY].[CUSTOMPERMISSIONSSET] = 1 and
            [HIERARCHY].[APPLYQUERYDEFAULTSTOEXISTINGQUERIES] = 1
          order by [ANCESTORS].[LEVEL]
        ) as [ANCESTORTODEFAULTFROM];

    insert into @QUERIESTOUPDATEPERMISSIONS ([QUERYID], [FOLDERID], [ITEMTYPE])
      select
        [SMARTQUERYINSTANCE].[ID],
        [ANCESTORTODEFAULTFROM].[ID] as [FOLDERID],
        1 as [ITEMTYPE]
      from dbo.[SMARTQUERYINSTANCE]
      cross apply
        (
          select top 1
            [ANCESTORS].[ID]
          from dbo.[UFN_ADHOCQUERY_GETANCESTORS]([SMARTQUERYINSTANCE].[FOLDERID]) [ANCESTORS]
          inner join @QUERYFOLDERHIERARCHY [HIERARCHY] on [HIERARCHY].[FOLDERID] = [ANCESTORS].[ID] 
          where
            [HIERARCHY].[CUSTOMPERMISSIONSSET] = 1 and
            [HIERARCHY].[APPLYQUERYDEFAULTSTOEXISTINGQUERIES] = 1
          order by [ANCESTORS].[LEVEL]
        ) as [ANCESTORTODEFAULTFROM];

    update dbo.[ADHOCQUERY] set
      [SECURITYLEVEL] = case when [ADHOCQUERYFOLDER].[QUERYDEFAULTALLOWALLUSERSTORUN] = 1 then 0 else 1 end,
      [SECURITYLEVELEDIT] = case when [ADHOCQUERYFOLDER].[QUERYDEFAULTALLOWALLUSERSTOEDIT] = 1 then 0 else 1 end,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE                            
    from dbo.[ADHOCQUERY]
    inner join @QUERIESTOUPDATEPERMISSIONS [UPDATEDQUERIES] on [UPDATEDQUERIES].[QUERYID] = [ADHOCQUERY].[ID] and [UPDATEDQUERIES].[ITEMTYPE] = 0
    inner join dbo.[ADHOCQUERYFOLDER] on [ADHOCQUERYFOLDER].[ID] = [UPDATEDQUERIES].[FOLDERID]
    where (([ADHOCQUERY].[SECURITYLEVEL] <> case when [ADHOCQUERYFOLDER].[QUERYDEFAULTALLOWALLUSERSTORUN] = 1 then 0 else 1 end)
    or ([ADHOCQUERY].[SECURITYLEVELEDIT] <> case when [ADHOCQUERYFOLDER].[QUERYDEFAULTALLOWALLUSERSTOEDIT] = 1 then 0 else 1 end));

    update dbo.[SMARTQUERYINSTANCE] set
      [SECURITYLEVEL] = case when [ADHOCQUERYFOLDER].[SMARTQUERYDEFAULTALLOWALLUSERSTORUN] = 1 then 0 else 1 end,
      [OTHERSCANMODIFY] = case when [ADHOCQUERYFOLDER].[SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT] = 1 then 1 else 0 end,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    from dbo.[SMARTQUERYINSTANCE]
    inner join @QUERIESTOUPDATEPERMISSIONS [UPDATEDQUERIES] on [UPDATEDQUERIES].[QUERYID] = [SMARTQUERYINSTANCE].[ID] and [UPDATEDQUERIES].[ITEMTYPE] = 1
    inner join dbo.[ADHOCQUERYFOLDER] on [ADHOCQUERYFOLDER].[ID] = [UPDATEDQUERIES].[FOLDERID];

 delete from dbo.[SYSTEMROLEPERM_ADHOCQUERYINSTANCE]
    where
      [ADHOCQUERYID] in (select [QUERYID] from @QUERIESTOUPDATEPERMISSIONS where [ITEMTYPE] = 0);

    insert into dbo.[SYSTEMROLEPERM_ADHOCQUERYINSTANCE]
    (
      [SYSTEMROLEID],
      [ADHOCQUERYID],
      [GRANTORDENY],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select
      [ADHOCQUERYFOLDERDEFAULTQUERYROLE].[SYSTEMROLEID],
      [UPDATEDQUERIES].[QUERYID] as [ADHOCQUERYID],
      [ADHOCQUERYFOLDERDEFAULTQUERYROLE].[GRANTORDENY],
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from @QUERIESTOUPDATEPERMISSIONS [UPDATEDQUERIES]
    inner join dbo.[ADHOCQUERYFOLDERDEFAULTQUERYROLE] on [UPDATEDQUERIES].[FOLDERID] = [ADHOCQUERYFOLDERDEFAULTQUERYROLE].[ADHOCQUERYFOLDERID]
    where
      [ADHOCQUERYFOLDERDEFAULTQUERYROLE].[PERMISSIONTYPECODE] = 0 -- Run
      and [UPDATEDQUERIES].[ITEMTYPE] = 0; -- Query

    delete from dbo.[SYSTEMROLEPERM_ADHOCQUERYINSTANCEEDIT]
    where
      [ADHOCQUERYID] in (select [QUERYID] from @QUERIESTOUPDATEPERMISSIONS where [ITEMTYPE] = 0);

    insert into dbo.[SYSTEMROLEPERM_ADHOCQUERYINSTANCEEDIT]
    (
      [SYSTEMROLEID],
      [ADHOCQUERYID],
      [GRANTORDENY],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select
      [ADHOCQUERYFOLDERDEFAULTQUERYROLE].[SYSTEMROLEID],
      [UPDATEDQUERIES].[QUERYID] as [ADHOCQUERYID],
      [ADHOCQUERYFOLDERDEFAULTQUERYROLE].[GRANTORDENY],
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from @QUERIESTOUPDATEPERMISSIONS [UPDATEDQUERIES]
    inner join dbo.[ADHOCQUERYFOLDERDEFAULTQUERYROLE] on [UPDATEDQUERIES].[FOLDERID] = [ADHOCQUERYFOLDERDEFAULTQUERYROLE].[ADHOCQUERYFOLDERID]
    where
      [ADHOCQUERYFOLDERDEFAULTQUERYROLE].[PERMISSIONTYPECODE] = 1 -- Edit
      and [UPDATEDQUERIES].[ITEMTYPE] = 0; -- Ad-hoc Query

    delete from dbo.[SYSTEMROLEPERM_SMARTQUERYINSTANCE]
    where
      [SMARTQUERYID] in (select [QUERYID] from @QUERIESTOUPDATEPERMISSIONS where [ITEMTYPE] = 1);

    insert into dbo.[SYSTEMROLEPERM_SMARTQUERYINSTANCE]
    (
      [SYSTEMROLEID],
      [SMARTQUERYID],
      [GRANTORDENY],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    select
      [ADHOCQUERYFOLDERDEFAULTQUERYROLE].[SYSTEMROLEID],
      [UPDATEDQUERIES].[QUERYID] as [SMARTQUERYID],
      [ADHOCQUERYFOLDERDEFAULTQUERYROLE].[GRANTORDENY],
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from @QUERIESTOUPDATEPERMISSIONS [UPDATEDQUERIES]
    inner join dbo.[ADHOCQUERYFOLDERDEFAULTQUERYROLE] on [UPDATEDQUERIES].[FOLDERID] = [ADHOCQUERYFOLDERDEFAULTQUERYROLE].[ADHOCQUERYFOLDERID]
    where
      [ADHOCQUERYFOLDERDEFAULTQUERYROLE].[PERMISSIONTYPECODE] = 2 -- RunSmartQuery
      and [UPDATEDQUERIES].[ITEMTYPE] = 1; -- SmartQuery

    -- Finally, delete folders
    delete from dbo.[ADHOCQUERYFOLDER]
    from dbo.[ADHOCQUERYFOLDER]
        inner join dbo.[UFN_ADHOCQUERY_GETHIERARCHYFILTER](@CURRENTAPPUSERID) as [GHF] on [ADHOCQUERYFOLDER].[ID] = [GHF].[ID]
    where [ADHOCQUERYFOLDER].[ID] not in (select [FOLDERID] from @QUERYFOLDERHIERARCHY);

    --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;