USP_DATAFORMTEMPLATE_EDIT_ADHOCQUERY_PERMISSIONS_2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_ADHOCQUERY_PERMISSIONS_2
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,    
  @SECURITYLEVEL tinyint,
  @SYSTEMROLELIST xml,    
  @SECURITYLEVELEDIT tinyint,
  @SYSTEMROLELISTEDIT xml

) AS

set nocount on;

if @CHANGEAGENTID is null  
  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

declare @CURRENTDATE datetime;
set @CURRENTDATE=getdate();

BEGIN TRY

  declare @SYSTEMROLEID varchar(max)
  declare @SYSTEMROLEEDITLIST varchar(max)
  declare @SYSTEMROLEEDITLISTFORRUNTAB varchar(max)
  declare @SYSTEMROLEEDITLISTMODIFIED xml
  declare @SYSTEMROLEEDITLISTFORRUNTABMODIFIED xml
  declare @GRANTORDENY varchar(max)
  declare @XMLTOBESAVED as xml

  if @SECURITYLEVEL = 1
    begin    
      -- build a temporary table containing the values from the XML, excluding ones where permission is None

      -- Not using UFN_ADHOCQUERYINSTANCE_GETSYSTEMROLEPERMISSIONSLIST_FROMITEMLISTXML since we need 

      -- GRANTORDENY to be deserialized as a tinyint since it could be set to 2 - which indicates None.

      declare @TempTbl table (ID uniqueidentifier, SYSTEMROLEID uniqueidentifier, GRANTORDENY bit)
      insert into @TempTbl 
      select
        T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
        T.c.value('(SYSTEMROLEID)[1]','uniqueidentifier') AS 'SYSTEMROLEID',
        T.c.value('(GRANTORDENY)[1]','bit') AS 'GRANTORDENY'
      from @SYSTEMROLELIST.nodes('/SYSTEMROLELIST/ITEM') T(c)
      where 
        T.c.value('(GRANTORDENY)[1]','tinyint') in (0, 1)

      -- rebuild the system role list

      set @SYSTEMROLELIST = 
        (select ID, SYSTEMROLEID, GRANTORDENY
         from @TempTbl
         for xml raw('ITEM'), type, elements, root('SYSTEMROLELIST'), BINARY BASE64)

      if @SYSTEMROLELIST is null
        delete from dbo.SYSTEMROLEPERM_ADHOCQUERYINSTANCE where ADHOCQUERYID = @ID
      else
        exec dbo.USP_ADHOCQUERYINSTANCE_GETSYSTEMROLEPERMISSIONSLIST_UPDATEFROMXML @ID, @SYSTEMROLELIST, @CHANGEAGENTID;

      declare SYSTEMROLESFORRUNTAB CURSOR FOR
        select SYSTEMROLEID,
        case when GRANTORDENY = 0 then 'false'
        else 'true' end as GRANTORDENY from dbo.[SYSTEMROLEPERM_ADHOCQUERYINSTANCE] where ADHOCQUERYID = @ID;

      open SYSTEMROLESFORRUNTAB

      fetch next from SYSTEMROLESFORRUNTAB into @SYSTEMROLEID,@GRANTORDENY;

      while ( @@FETCH_STATUS = 0)
      begin
        set  @SYSTEMROLEEDITLISTFORRUNTAB = '<r SystemRoleID='''+ISNULL(@SYSTEMROLEID,'')+''' GrantOrDeny='''+@GRANTORDENY+''' />'+ISNULL(@SYSTEMROLEEDITLISTFORRUNTAB,'');
        fetch next from SYSTEMROLESFORRUNTAB into @SYSTEMROLEID,@GRANTORDENY;
      end

      close SYSTEMROLESFORRUNTAB
      deallocate SYSTEMROLESFORRUNTAB

        set @XMLTOBESAVED = CONVERT(XML, @SYSTEMROLEEDITLISTFORRUNTAB); 

        select @SYSTEMROLEEDITLISTFORRUNTABMODIFIED = QUERYDEFINITIONXML from dbo.ADHOCQUERY where ID = @ID; 

        set @SYSTEMROLEEDITLISTFORRUNTABMODIFIED = CAST(REPLACE(CAST(@SYSTEMROLEEDITLISTFORRUNTABMODIFIED AS NVARCHAR(MAX)),' xmlns=""','') AS XML);

        update dbo.ADHOCQUERY set QUERYDEFINITIONXML = @SYSTEMROLEEDITLISTFORRUNTABMODIFIED where ID = @ID;

        ;WITH XMLNAMESPACES ('Blackbaud.AppFx.WebService.API.1' as t)
          update dbo.ADHOCQUERY set QUERYDEFINITIONXML.modify('  
          delete (/t:AdHocQuery/t:PermissionedRunRoles/t:r)
        ') where ID = @ID;

        ;WITH XMLNAMESPACES ('Blackbaud.AppFx.WebService.API.1' as t)
          update dbo.ADHOCQUERY set QUERYDEFINITIONXML.modify('
          insert sql:variable("@XMLTOBESAVED")
          into (/t:AdHocQuery/t:PermissionedRunRoles)[1]
         ') where ID = @ID;

         select @SYSTEMROLEEDITLISTFORRUNTABMODIFIED = QUERYDEFINITIONXML from dbo.ADHOCQUERY where ID = @ID; 

   set @SYSTEMROLEEDITLISTFORRUNTABMODIFIED = CAST(REPLACE(CAST(@SYSTEMROLEEDITLISTFORRUNTABMODIFIED AS NVARCHAR(MAX)),' xmlns=""','') AS XML);

         update dbo.ADHOCQUERY set QUERYDEFINITIONXML = @SYSTEMROLEEDITLISTFORRUNTABMODIFIED where ID = @ID;
    end
  else
    -- remove rows from dbo.SYSTEMROLEPERM_ADHOCQUERYINSTANCE

    delete from dbo.SYSTEMROLEPERM_ADHOCQUERYINSTANCE where ADHOCQUERYID = @ID

  if @SECURITYLEVELEDIT = 1
    begin    
      -- build a temporary table containing the values from the XML, excluding ones where permission is None

      -- Not using UFN_ADHOCQUERYINSTANCE_GETSYSTEMROLEPERMISSIONSLIST_FROMITEMLISTXML since we need 

      -- GRANTORDENY to be deserialized as a tinyint when verifying it's 0 or 1 since it could be set to 2 (which indicates None)

      -- which then couldn't be converted.

      declare @TempTblEdit table (ID uniqueidentifier, SYSTEMROLEID uniqueidentifier, GRANTORDENY bit)
      insert into @TempTblEdit
      select
        T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
        T.c.value('(SYSTEMROLEID)[1]','uniqueidentifier') AS 'SYSTEMROLEID',
        T.c.value('(GRANTORDENY)[1]','bit') AS 'GRANTORDENY'
      from @SYSTEMROLELISTEDIT.nodes('/SYSTEMROLELISTEDIT/ITEM') T(c)
      where 
        T.c.value('(GRANTORDENY)[1]','tinyint') in (0, 1)

      -- rebuild the system role list

      -- need to change the element to 'SYSTEMROLELIST' since that's what USP_ADHOCQUERYINSTANCE_GETSYSTEMROLEEDITPERMISSIONSLIST_UPDATEFROMXML

      -- expects

      set @SYSTEMROLELISTEDIT = 
        (select ID, SYSTEMROLEID, GRANTORDENY
         from @TempTblEdit
         for xml raw('ITEM'), type, elements, root('SYSTEMROLELIST'), BINARY BASE64)

     if @SYSTEMROLELISTEDIT is null
       delete from dbo.SYSTEMROLEPERM_ADHOCQUERYINSTANCEEDIT where ADHOCQUERYID = @ID
     else
        exec dbo.USP_ADHOCQUERYINSTANCE_GETSYSTEMROLEEDITPERMISSIONSLIST_UPDATEFROMXML @ID, @SYSTEMROLELISTEDIT, @CHANGEAGENTID;

       declare SYSTEMROLES CURSOR FOR
       select SYSTEMROLEID,
       case when GRANTORDENY = 0 then 'false'
       else 'true' end as GRANTORDENY from dbo.[SYSTEMROLEPERM_ADHOCQUERYINSTANCEEDIT] where ADHOCQUERYID = @ID;

      open SYSTEMROLES

       fetch next from SYSTEMROLES into @SYSTEMROLEID,@GRANTORDENY;

      while ( @@FETCH_STATUS = 0)
      begin

       set  @SYSTEMROLEEDITLIST = '<r SystemRoleID='''+ISNULL(@SYSTEMROLEID,'')+''' GrantOrDeny='''+@GRANTORDENY+''' />'+ISNULL(@SYSTEMROLEEDITLIST,'');
          fetch next from SYSTEMROLES into @SYSTEMROLEID,@GRANTORDENY;
      end

      close SYSTEMROLES
      deallocate SYSTEMROLES

       set @XMLTOBESAVED = CONVERT(XML, @SYSTEMROLEEDITLIST); 

       select @SYSTEMROLEEDITLISTMODIFIED = QUERYDEFINITIONXML from dbo.ADHOCQUERY where ID = @ID; 

       set @SYSTEMROLEEDITLISTMODIFIED = CAST(REPLACE(CAST(@SYSTEMROLEEDITLISTMODIFIED AS NVARCHAR(MAX)),' xmlns=""','') AS XML);

       update dbo.ADHOCQUERY set QUERYDEFINITIONXML = @SYSTEMROLEEDITLISTMODIFIED where ID = @ID;

       ;WITH XMLNAMESPACES ('Blackbaud.AppFx.WebService.API.1' as t)
         update dbo.ADHOCQUERY set QUERYDEFINITIONXML.modify('  
         delete (/t:AdHocQuery/t:PermissionedEditRoles/t:r)
       ') where ID = @ID;

       ;WITH XMLNAMESPACES ('Blackbaud.AppFx.WebService.API.1' as t)
        update dbo.ADHOCQUERY set QUERYDEFINITIONXML.modify('
         insert sql:variable("@XMLTOBESAVED")
         into (/t:AdHocQuery/t:PermissionedEditRoles)[1]
        ') where ID = @ID;

        select @SYSTEMROLEEDITLISTMODIFIED = QUERYDEFINITIONXML from dbo.ADHOCQUERY where ID = @ID; 

        set @SYSTEMROLEEDITLISTMODIFIED = CAST(REPLACE(CAST(@SYSTEMROLEEDITLISTMODIFIED AS NVARCHAR(MAX)),' xmlns=""','') AS XML);

        update dbo.ADHOCQUERY set QUERYDEFINITIONXML = @SYSTEMROLEEDITLISTMODIFIED where ID = @ID;

         end
  else
    delete from dbo.SYSTEMROLEPERM_ADHOCQUERYINSTANCEEDIT where ADHOCQUERYID = @ID

  -- now update the flag on the ad-hoc query table

  update dbo.ADHOCQUERY set
    SECURITYLEVEL = @SECURITYLEVEL,
    SECURITYLEVELEDIT = @SECURITYLEVELEDIT,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
  where 
    (ID = @ID) and 
    (
      SECURITYLEVEL <> @SECURITYLEVEL or
      SECURITYLEVELEDIT <> @SECURITYLEVELEDIT
    )

  return 0;

END TRY
BEGIN CATCH
  exec dbo.USP_RAISE_ERROR;
  return 1;
END CATCH