USP_SMARTQUERYINSTANCE_CREATEORUPDATE

CREATE procedure dbo.USP_SMARTQUERYINSTANCE_CREATEORUPDATE
(
    @ID uniqueidentifier,
    @SMARTQUERYCATALOGID uniqueidentifier,
    @NAME nvarchar(255),
    @DESCRIPTION nvarchar(1024),
    @QUERYCATEGORYCODEID uniqueidentifier,
    @DATAFORMINSTANCEID uniqueidentifier,
    @DATAFORMITEMFILTERXML xml,
    @OTHERSCANMODIFY bit,
    @APPUSERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @SITEID uniqueidentifier = null,
    @MOBILIZE bit = null,
    @FOLDERID uniqueidentifier = null,
    @ISFAVORITE bit = null,
    @SECURITYLEVEL tinyint = null,
    @PERMISSIONEDROLES xml = null
)
as

declare @EXISTINGID uniqueidentifier;
declare @EXISTINGOWNERID uniqueidentifier;
declare @EXISTINGOWNER nvarchar(128);
declare @EXISTINGOTHERSCANMODIFY bit;

select @EXISTINGID = SMARTQUERYINSTANCE.ID,
    @EXISTINGOTHERSCANMODIFY = SMARTQUERYINSTANCE.OTHERSCANMODIFY, 
    @EXISTINGOWNERID = SMARTQUERYINSTANCE.OWNERID, 
    @EXISTINGOWNER = APPUSER.USERNAME
from dbo.SMARTQUERYINSTANCE 
inner join dbo.APPUSER on SMARTQUERYINSTANCE.OWNERID = APPUSER.ID
where SMARTQUERYINSTANCE.ID = @ID;

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

if @FOLDERID = cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)
    set @FOLDERID = null;

set @SECURITYLEVEL = coalesce(@SECURITYLEVEL, 0);

if @EXISTINGID is null
    insert into dbo.SMARTQUERYINSTANCE (ID, SMARTQUERYCATALOGID, NAME, DESCRIPTION, QUERYCATEGORYCODEID, DATAFORMINSTANCEID, DATAFORMITEMFILTERXML, OTHERSCANMODIFY, OWNERID, SITEID, MOBILIZE, FOLDERID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SECURITYLEVEL)
    values (@ID, @SMARTQUERYCATALOGID, @NAME, @DESCRIPTION, @QUERYCATEGORYCODEID, @DATAFORMINSTANCEID, @DATAFORMITEMFILTERXML, @OTHERSCANMODIFY, @APPUSERID, @SITEID, coalesce(@MOBILIZE, 0), @FOLDERID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @SECURITYLEVEL)
else
begin
    declare @ISSYSADMIN bit;
    select @ISSYSADMIN = ISSYSADMIN from dbo.APPUSER where ID = @APPUSERID;

    if @EXISTINGOWNERID <> @APPUSERID and @ISSYSADMIN = 0
    begin
        if @EXISTINGOTHERSCANMODIFY = 0
        begin
            raiserror(N'This smart query instance can only be modified by the user %s or a system administrator.', 16, 1, @EXISTINGOWNER);
            return 1;
        end;

        if @EXISTINGOTHERSCANMODIFY <> @OTHERSCANMODIFY
        begin
            raiserror(N'The ''others can modify'' property can only be changed by the user %s or a system administrator.', 16, 1, @EXISTINGOWNER);
            return 2;
        end;
    end;

    update dbo.SMARTQUERYINSTANCE set SMARTQUERYCATALOGID = @SMARTQUERYCATALOGID, NAME = @NAME, DESCRIPTION = @DESCRIPTION, QUERYCATEGORYCODEID = @QUERYCATEGORYCODEID, DATAFORMINSTANCEID = @DATAFORMINSTANCEID, DATAFORMITEMFILTERXML = @DATAFORMITEMFILTERXML, OTHERSCANMODIFY = @OTHERSCANMODIFY, SITEID = @SITEID, MOBILIZE = coalesce(@MOBILIZE, MOBILIZE), FOLDERID = @FOLDERID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE, SECURITYLEVEL = @SECURITYLEVEL
    where ID = @ID

    if @SECURITYLEVEL = 0
    begin
        declare @contextCache varbinary(128);
        set @contextCache = CONTEXT_INFO();
        set CONTEXT_INFO @CHANGEAGENTID;                    

        -- Clear specific roles if all roles are allowed now

        if @SECURITYLEVEL = 0
            delete from dbo.SYSTEMROLEPERM_SMARTQUERYINSTANCE where SMARTQUERYID = @ID

        if not @contextCache is null
            set CONTEXT_INFO @contextCache;
    end
end

if @ISFAVORITE = 1
begin
    exec dbo.USP_APPUSERSMARTQUERYINSTANCEFAVORITE_ADD @ID, @APPUSERID, @CHANGEAGENTID
end
else
begin
    exec dbo.USP_APPUSERSMARTQUERYINSTANCEFAVORITE_DELETE @ID, @APPUSERID, @CHANGEAGENTID
end

if @SECURITYLEVEL = 1
begin
    exec dbo.USP_SMARTQUERYINSTANCE_GETSYSTEMROLEPERMISSIONSLIST_UPDATEFROMXML @ID, @PERMISSIONEDROLES, @CHANGEAGENTID;
end