USP_DATAFORMTEMPLATE_EDIT_ADHOCQUERYHIERARCHY

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_ADHOCQUERYHIERARCHY
(
  @CHANGEAGENTID uniqueidentifier = null,
  @ADHOCQUERYHIERARCHY xml,
  @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 @HIERARCHY table
    (
      ID uniqueidentifier,
      PARENTFOLDERID uniqueidentifier,
      NAME nvarchar(100),
      DISPLAYORDER int,
      ITEMTYPE tinyint,
      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,
      ISFAVORITE bit
    )

    -- 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, @ADHOCQUERYHIERARCHY

    insert into @HIERARCHY
    (
      ID,
      PARENTFOLDERID,
      NAME,
      DISPLAYORDER,
      ITEMTYPE,
      CUSTOMPERMISSIONSSET,
      APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS,
      SECURITYLEVELCODE,
      QUERYDEFAULTALLOWALLUSERSTORUN,
      QUERYDEFAULTALLOWALLUSERSTOEDIT,
      SMARTQUERYDEFAULTALLOWALLUSERSTORUN,
      SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT,
      APPLYQUERYDEFAULTSTOEXISTINGQUERIES,
      FOLDERTOINHERITPERMISSIONSFROM,
      SYSTEMROLELIST,
      QUERYDEFAULTRUNROLES,
      QUERYDEFAULTEDITROLES,
      SMARTQUERYDEFAULTRUNROLES,
      ISFAVORITE
    )
    select 
      ID,
      case when PARENTFOLDERID = '00000000-0000-0000-0000-000000000000'
        then null
        else PARENTFOLDERID
      end,
      NAME,
      DISPLAYORDER,
      coalesce(ITEMTYPE, 1), -- Default to folder,
      coalesce(CUSTOMPERMISSIONSSET, 0),
      coalesce(APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS, 0),
      coalesce(SECURITYLEVEL, 0),
      coalesce(QUERYDEFAULTALLOWALLUSERSTORUN, 1),
      coalesce(QUERYDEFAULTALLOWALLUSERSTOEDIT, 1),
      coalesce(SMARTQUERYDEFAULTALLOWALLUSERSTORUN, 1),
      coalesce(SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT, 1),
      coalesce(APPLYQUERYDEFAULTSTOEXISTINGQUERIES, 0),
      case when FOLDERTOINHERITPERMISSIONSFROM = '00000000-0000-0000-0000-000000000000'
        then null
        else FOLDERTOINHERITPERMISSIONSFROM
      end,
      SYSTEMROLELIST,
      QUERYDEFAULTRUNROLES,
      QUERYDEFAULTEDITROLES,
      SMARTQUERYDEFAULTRUNROLES,
      ISFAVORITE
    from OPENXML (@idoc, '/ADHOCQUERYHIERARCHY/ITEM')
    WITH (ID  uniqueidentifier 'ID',
      PARENTFOLDERID uniqueidentifier 'PARENTFOLDERID',
      NAME nvarchar(100) 'NAME',
      DISPLAYORDER bigint 'DISPLAYORDER',
      ITEMTYPE int 'ITEMTYPE',
      CUSTOMPERMISSIONSSET bit 'CUSTOMPERMISSIONSSET',
      APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS bit 'APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS',
      SECURITYLEVEL tinyint 'SECURITYLEVEL',
      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',
      ISFAVORITE bit 'ISFAVORITE') R

    EXEC sp_xml_removedocument @idoc

    -- Need to clear the DISPLAYORDER values temporarily on ADHOCQUERY so that
    -- a collision won't occur if a folder is now using that DISPLAYORDER.
    update dbo.ADHOCQUERY set
      DISPLAYORDER = 0,
      DATECHANGED = @CURRENTDATE,
      CHANGEDBYID = @CHANGEAGENTID
    from dbo.ADHOCQUERY
    inner join @HIERARCHY UPDATEDQUERIES on ADHOCQUERY.ID = UPDATEDQUERIES.ID
    where UPDATEDQUERIES.ITEMTYPE = 0 -- Ad-hoc Query
      and ADHOCQUERY.DISPLAYORDER <> UPDATEDQUERIES.DISPLAYORDER

    -- Need to clear the DISPLAYORDER values temporarily on SMARTQUERYINSTANCE so that
    -- a collision won't occur if a folder is now using that DISPLAYORDER.
    update dbo.SMARTQUERYINSTANCE set
      DISPLAYORDER = 0,
      DATECHANGED = @CURRENTDATE,
      CHANGEDBYID = @CHANGEAGENTID
    from dbo.SMARTQUERYINSTANCE
    inner join @HIERARCHY UPDATEDQUERIES on SMARTQUERYINSTANCE.ID = UPDATEDQUERIES.ID
    where UPDATEDQUERIES.ITEMTYPE = 2 -- Template Query
      and SMARTQUERYINSTANCE.DISPLAYORDER <> UPDATEDQUERIES.DISPLAYORDER

    -- 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 
    (
      select
        ID,
        coalesce(
        (
          select max(DISPLAYORDER) 
          from 
          (
            select DISPLAYORDER, PARENTFOLDERID from dbo.ADHOCQUERYFOLDER
            union all
            select DISPLAYORDER, FOLDERID as PARENTFOLDERID from dbo.ADHOCQUERY
                        union all
            select DISPLAYORDER, FOLDERID as PARENTFOLDERID from dbo.SMARTQUERYINSTANCE
            union all
            select DISPLAYORDER, PARENTFOLDERID from @HIERARCHY
          ) 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 ID from @HIERARCHY) and
      (
        ADHOCQUERYFOLDER.PARENTFOLDERID not in (select ID from @HIERARCHY) 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 @HIERARCHY UPDATEDFOLDERS on ADHOCQUERYFOLDER.ID = UPDATEDFOLDERS.ID
    inner join 
    (
      select
        ID,
        coalesce(
        (
          select max(DISPLAYORDER) 
          from 
          (
            select DISPLAYORDER, PARENTFOLDERID from dbo.ADHOCQUERYFOLDER
            union all
            select DISPLAYORDER, FOLDERID as PARENTFOLDERID from dbo.ADHOCQUERY
            union all
            select DISPLAYORDER, FOLDERID as PARENTFOLDERID from dbo.SMARTQUERYINSTANCE
            union all
            select DISPLAYORDER, PARENTFOLDERID from @HIERARCHY
          ) 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
    where UPDATEDFOLDERS.ITEMTYPE = 1 -- Folder

    update @HIERARCHY set ID = newid() where ID is null

    declare @INSERTEDFOLDERS table
    (
      FOLDERID uniqueidentifier
    )

    insert into @INSERTEDFOLDERS (FOLDERID)
    select
      ID
    from @HIERARCHY
    where
      ID not in (select ID from dbo.ADHOCQUERYFOLDER) and
      ITEMTYPE = 1 -- Folder


    -- 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 ID is null then newid() else ID end,
      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 
          (
            select DISPLAYORDER, PARENTFOLDERID from dbo.ADHOCQUERYFOLDER
            union all
            select DISPLAYORDER, FOLDERID from dbo.ADHOCQUERY
            union all
            select DISPLAYORDER, FOLDERID from dbo.SMARTQUERYINSTANCE
          ) as DERIVEDTABLE
          where PARENTFOLDERID is null
        ), 0) + row_number() over (order by NAME),
      @CURRENTAPPUSERID,
      @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID
    from @HIERARCHY HIERARCHY
    inner join @INSERTEDFOLDERS INSERTEDFOLDERS on HIERARCHY.ID = 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.NAME,
      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 @HIERARCHY UPDATEDFOLDERS on ADHOCQUERYFOLDER.ID = UPDATEDFOLDERS.ID
    where UPDATEDFOLDERS.ITEMTYPE = 1 -- Folder


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

    delete from dbo.ADHOCQUERYFOLDERDEFAULTQUERYROLE
    where 
      ADHOCQUERYFOLDERID in
      (
        select
          HIERARCHY.ID
        from @HIERARCHY 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.ID,
      T.c.value('(GRANTORDENY)[1]','tinyint'),
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from @HIERARCHY 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.ID,
      T.c.value('(GRANTORDENY)[1]','tinyint'),
      0, -- Run
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE                                
    from @HIERARCHY 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.ID,
      T.c.value('(GRANTORDENY)[1]','tinyint'),
      1, -- Edit
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE                                
    from @HIERARCHY 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.ID,
      T.c.value('(GRANTORDENY)[1]','tinyint'),
      2, -- RunSmartQuery
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE                                
    from @HIERARCHY 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 @HIERARCHY UPDATEDFOLDERS on ADHOCQUERYFOLDER.ID = UPDATEDFOLDERS.ID
    ) 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 @HIERARCHY HIERARCHY on HIERARCHY.ID = INSERTEDFOLDERS.FOLDERID
    where
      HIERARCHY.CUSTOMPERMISSIONSSET = 0 and
      ADHOCQUERYFOLDER.ID not in (select FOLDERID from @FOLDERANDDEFAULTINGANCESTORTOUPDATE)


    -- Update permissions for queries that were moved to a new folder or that reside
    -- in a folder that had APPLYQUERYDEFAULTSTOEXISTINGQUERIES set to true
    declare @QUERIESTOUPDATEPERMISSIONS table
    (
      ADHOCQUERYID uniqueidentifier not null,
      NEWFOLDERID uniqueidentifier not null,
      ITEMTYPE integer not null
    )

    insert into @QUERIESTOUPDATEPERMISSIONS (ADHOCQUERYID, NEWFOLDERID, ITEMTYPE)
    -- Find queries that were moved to a new folder
    select
      ADHOCQUERY.ID,
      UPDATEDQUERIES.PARENTFOLDERID,
      UPDATEDQUERIES.ITEMTYPE
    from dbo.ADHOCQUERY
    inner join @HIERARCHY UPDATEDQUERIES on ADHOCQUERY.ID = UPDATEDQUERIES.ID
    where
      UPDATEDQUERIES.ITEMTYPE = 0 and
      UPDATEDQUERIES.PARENTFOLDERID is not null and
      (
        ADHOCQUERY.FOLDERID <> UPDATEDQUERIES.PARENTFOLDERID or
        (ADHOCQUERY.FOLDERID is null and UPDATEDQUERIES.PARENTFOLDERID is not null)
      )
    union all
    select
      SMARTQUERYINSTANCE.ID,
      UPDATEDQUERIES.PARENTFOLDERID,
      UPDATEDQUERIES.ITEMTYPE
    from dbo.SMARTQUERYINSTANCE
    inner join @HIERARCHY UPDATEDQUERIES on SMARTQUERYINSTANCE.ID = UPDATEDQUERIES.ID
    where
      UPDATEDQUERIES.ITEMTYPE = 2 and
      UPDATEDQUERIES.PARENTFOLDERID is not null and
      (
        SMARTQUERYINSTANCE.FOLDERID <> UPDATEDQUERIES.PARENTFOLDERID or
        (SMARTQUERYINSTANCE.FOLDERID is null and UPDATEDQUERIES.PARENTFOLDERID is not null)
      )
    union all
    -- Find queries whose ancestor has APPLYQUERYDEFAULTSTOEXISTINGQUERIES set to true
    select
      UPDATEDQUERIES.ID,
      ANCESTORTODEFAULTFROM.ID,
      UPDATEDQUERIES.ITEMTYPE
    from @HIERARCHY UPDATEDQUERIES
    cross apply
    (
      select top 1
        ANCESTORS.ID
      from dbo.UFN_ADHOCQUERY_GETANCESTORS(UPDATEDQUERIES.PARENTFOLDERID) ANCESTORS
      inner join @HIERARCHY UPDATEDFOLDERS on ANCESTORS.ID = UPDATEDFOLDERS.ID
      where
        UPDATEDFOLDERS.ITEMTYPE = 1 and -- Folder
        UPDATEDFOLDERS.CUSTOMPERMISSIONSSET = 1 and
        UPDATEDFOLDERS.APPLYQUERYDEFAULTSTOEXISTINGQUERIES = 1
      order by ANCESTORS.LEVEL
    ) as ANCESTORTODEFAULTFROM
    where
      UPDATEDQUERIES.ITEMTYPE = 0 or -- Query
      UPDATEDQUERIES.ITEMTYPE = 2 -- SmartQuery

    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 QUERIESTOUPDATEPERMISSIONS on ADHOCQUERY.ID = QUERIESTOUPDATEPERMISSIONS.ADHOCQUERYID
        inner join dbo.ADHOCQUERYFOLDER on ADHOCQUERYFOLDER.ID = QUERIESTOUPDATEPERMISSIONS.NEWFOLDERID
        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))
      and QUERIESTOUPDATEPERMISSIONS.ITEMTYPE = 0 -- Query

    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 QUERIESTOUPDATEPERMISSIONS on SMARTQUERYINSTANCE.ID = QUERIESTOUPDATEPERMISSIONS.ADHOCQUERYID
        inner join dbo.ADHOCQUERYFOLDER on ADHOCQUERYFOLDER.ID = QUERIESTOUPDATEPERMISSIONS.NEWFOLDERID
        where QUERIESTOUPDATEPERMISSIONS.ITEMTYPE = 2 -- SmartQuery

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

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

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

    insert into dbo.SYSTEMROLEPERM_ADHOCQUERYINSTANCEEDIT
    (
      SYSTEMROLEID,
      ADHOCQUERYID,
      GRANTORDENY,
      ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
    )
    select
      ADHOCQUERYFOLDERDEFAULTQUERYROLE.SYSTEMROLEID,
      QUERIESTOUPDATEPERMISSIONS.ADHOCQUERYID,
      ADHOCQUERYFOLDERDEFAULTQUERYROLE.GRANTORDENY,
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from @QUERIESTOUPDATEPERMISSIONS QUERIESTOUPDATEPERMISSIONS
    inner join dbo.ADHOCQUERYFOLDERDEFAULTQUERYROLE on QUERIESTOUPDATEPERMISSIONS.NEWFOLDERID = ADHOCQUERYFOLDERDEFAULTQUERYROLE.ADHOCQUERYFOLDERID
    where
      ADHOCQUERYFOLDERDEFAULTQUERYROLE.PERMISSIONTYPECODE = 1 -- Edit
      and QUERIESTOUPDATEPERMISSIONS.ITEMTYPE = 0 -- Query

    delete from dbo.SYSTEMROLEPERM_SMARTQUERYINSTANCE
    where
      SMARTQUERYID in (select ADHOCQUERYID from @QUERIESTOUPDATEPERMISSIONS where ITEMTYPE = 2)

    insert into dbo.SYSTEMROLEPERM_SMARTQUERYINSTANCE
    (
      SYSTEMROLEID,
      SMARTQUERYID,
      GRANTORDENY,
      ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
    )
    select
      ADHOCQUERYFOLDERDEFAULTQUERYROLE.SYSTEMROLEID,
      QUERIESTOUPDATEPERMISSIONS.ADHOCQUERYID,
      ADHOCQUERYFOLDERDEFAULTQUERYROLE.GRANTORDENY,
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from @QUERIESTOUPDATEPERMISSIONS QUERIESTOUPDATEPERMISSIONS
    inner join dbo.ADHOCQUERYFOLDERDEFAULTQUERYROLE on QUERIESTOUPDATEPERMISSIONS.NEWFOLDERID = ADHOCQUERYFOLDERDEFAULTQUERYROLE.ADHOCQUERYFOLDERID
    where
      ADHOCQUERYFOLDERDEFAULTQUERYROLE.PERMISSIONTYPECODE = 2 -- RunSmartQuery
      and QUERIESTOUPDATEPERMISSIONS.ITEMTYPE = 2 -- SmartQuery

    -- Update the queries parent folder and display order.  Those are the only query properties
    -- that can be updated.
    update dbo.ADHOCQUERY set
      FOLDERID = UPDATEDQUERIES.PARENTFOLDERID,
      DISPLAYORDER = UPDATEDQUERIES.DISPLAYORDER,
      DATECHANGED = @CURRENTDATE,
      CHANGEDBYID = @CHANGEAGENTID
    from dbo.ADHOCQUERY
        inner join @HIERARCHY UPDATEDQUERIES on ADHOCQUERY.ID = UPDATEDQUERIES.ID
    where UPDATEDQUERIES.ITEMTYPE = 0 -- Query
             and ((ADHOCQUERY.DISPLAYORDER <> UPDATEDQUERIES.DISPLAYORDER) or
                  ((ADHOCQUERY.FOLDERID <> UPDATEDQUERIES.PARENTFOLDERID) or 
                   ((ADHOCQUERY.FOLDERID is null) and (not UPDATEDQUERIES.PARENTFOLDERID is null)) or
                   ((not ADHOCQUERY.FOLDERID is null) and (UPDATEDQUERIES.PARENTFOLDERID is null))))

    update dbo.SMARTQUERYINSTANCE set
      FOLDERID = UPDATEDQUERIES.PARENTFOLDERID,
      DISPLAYORDER = UPDATEDQUERIES.DISPLAYORDER,
      DATECHANGED = @CURRENTDATE,
      CHANGEDBYID = @CHANGEAGENTID
    from dbo.SMARTQUERYINSTANCE
        inner join @HIERARCHY UPDATEDQUERIES on SMARTQUERYINSTANCE.ID = UPDATEDQUERIES.ID
    where UPDATEDQUERIES.ITEMTYPE = 2 -- template Query
             and ((SMARTQUERYINSTANCE.DISPLAYORDER <> UPDATEDQUERIES.DISPLAYORDER) or
                  ((SMARTQUERYINSTANCE.FOLDERID <> UPDATEDQUERIES.PARENTFOLDERID) or 
                   ((SMARTQUERYINSTANCE.FOLDERID is null) and (not UPDATEDQUERIES.PARENTFOLDERID is null)) or
                   ((not SMARTQUERYINSTANCE.FOLDERID is null) and (UPDATEDQUERIES.PARENTFOLDERID is null))))

    MERGE dbo.APPUSERADHOCQUERYFAVORITE AS target
    USING (SELECT ID, ISFAVORITE from @HIERARCHY where ITEMTYPE = 0) AS source (ID, ISFAVORITE)
    ON (target.APPUSERID = @CURRENTAPPUSERID and target.ADHOCQUERYID = source.ID)
    WHEN MATCHED AND ISFAVORITE = 0 THEN 
        DELETE
    WHEN NOT MATCHED AND ISFAVORITE = 1 THEN    
        INSERT ([ID],[APPUSERID],[ADHOCQUERYID],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
        VALUES (newId(), @CURRENTAPPUSERID, source.ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

    --update smart favorites
    MERGE dbo.APPUSERSMARTQUERYINSTANCEFAVORITE AS target
    USING (SELECT ID, ISFAVORITE from @HIERARCHY where ITEMTYPE = 2) AS source (ID, ISFAVORITE)
    ON (target.APPUSERID = @CURRENTAPPUSERID and target.SMARTQUERYINSTANCEID = source.ID)
    WHEN MATCHED AND ISFAVORITE = 0 THEN 
        DELETE
    WHEN NOT MATCHED AND ISFAVORITE = 1 THEN    
        INSERT ([ID],[APPUSERID],[SMARTQUERYINSTANCEID],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
        VALUES (newId(), @CURRENTAPPUSERID, source.ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

    -- Finally, delete folders
    delete from dbo.ADHOCQUERYFOLDER 
    where ID not in (select ID from @HIERARCHY) and
      ID in (select ID from dbo.UFN_ADHOCQUERY_GETHIERARCHYFILTER(@CURRENTAPPUSERID))

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