USP_SQLVIEWCATALOG_UPDATESUMMARYTILEINFO

CREATE procedure dbo.USP_SQLVIEWCATALOG_UPDATESUMMARYTILEINFO
(
    @DATAFORMINSTANCECATALOGID uniqueidentifier,
    @RECORDTYPE nvarchar(100),
    @SUMMARYTILE xml = null,
    @CHANGEAGENTID uniqueidentifier = null
)
as

set nocount on;

begin try

    declare @CONTEXTCACHE varbinary(128);
    declare @ERRORMSG nvarchar(max);
    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

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


    if coalesce(cast(@SUMMARYTILE as nvarchar(max)), '') <> ''
    begin

        -- Get the ID of the record type from the string passed in.


        declare @RECORDTYPEID uniqueidentifier = null;
        select top 1 @RECORDTYPEID = ID from dbo.RECORDTYPE where upper(NAME) = upper(@RECORDTYPE);
        if @RECORDTYPEID is null
        begin
            set @ERRORMSG = 'Could not find the record type "' + @RECORDTYPE + '" in the RECORDTYPE table.';
            raiserror(@ERRORMSG,13,1);
        end

        -- Populate a table with the categories from the spec's SummaryTile element.


        declare @CATEGORYTABLE table (
            EXISTINGROWID uniqueidentifier,
            NAME nvarchar(100),
            TILENAME nvarchar(100),
            DEFAULTSEQUENCE int,
            DEFAULTVISIBLE bit,
            SUMMARYTILECONTEXTID uniqueidentifier
        );

        with xmlnamespaces (default 'bb_appfx_viewdataformtemplate')
        insert into @CATEGORYTABLE (NAME, TILENAME, DEFAULTSEQUENCE, DEFAULTVISIBLE)
        select
            T.c.value('(@Name)[1]','nvarchar(100)') as 'NAME',
            T.c.value('(@TileName)[1]','nvarchar(100)') as 'TILENAME',
            coalesce(T.c.value('(@DefaultSequence)[1]','int'), 0) as 'DEFAULTSEQUENCE',
            coalesce(T.c.value('(@DefaultVisible)[1]','bit'), 0) as 'DEFAULTVISIBLE'
        from
            @SUMMARYTILE.nodes('/SummaryTile/Categories/Category') T(c);

        -- Find the row in SUMMARYTILECONTEXT corresponding to this record type and category.

        update CATEGORYTABLE
        set SUMMARYTILECONTEXTID = SUMMARYTILECONTEXT.ID
        from
            @CATEGORYTABLE as CATEGORYTABLE
            inner join dbo.SUMMARYTILECONTEXT
                on SUMMARYTILECONTEXT.CATEGORYKEY = CATEGORYTABLE.NAME
        where
            SUMMARYTILECONTEXT.RECORDTYPEID = @RECORDTYPEID;

        -- If we weren't able to find a SUMMARYTILECONTEXT row for a category in the

        -- collection, throw an error.  We want LoadSpec to stop on this rather than

        -- fail silently.

        declare @MISSINGCATEGORYNAME nvarchar(100) = null;
        select top 1
            @MISSINGCATEGORYNAME = coalesce(NAME, '')
        from @CATEGORYTABLE
        where SUMMARYTILECONTEXTID is null;

        if @MISSINGCATEGORYNAME is not null
        begin
            set @ERRORMSG = 'Could not find the category "' + @MISSINGCATEGORYNAME;
            set @ERRORMSG = @ERRORMSG + '" for the record type "' + @RECORDTYPE;
            set @ERRORMSG = @ERRORMSG + '".  Please ensure a row for this combination exists in the SUMMARYTILECONTEXT table.';
            raiserror(@ERRORMSG,13,1);
        end


        -- Find existing rows in the database table that match those in the spec.


        update CATEGORYTABLE
        set
            EXISTINGROWID = AVAILABLESUMMARYTILE.ID
        from @CATEGORYTABLE as CATEGORYTABLE
        inner join dbo.AVAILABLESUMMARYTILE
            on AVAILABLESUMMARYTILE.SUMMARYTILECONTEXTID = CATEGORYTABLE.SUMMARYTILECONTEXTID
        where
            AVAILABLESUMMARYTILE.DATAFORMINSTANCECATALOGID = @DATAFORMINSTANCECATALOGID


        -- Make the summary tile information in the database match what came in from the spec.

        -- 1. Insert rows from the spec that do not have a match in the database.

        -- 2. Update rows in the database that matched rows in the spec.

        -- 3. Delete rows in the database that are not present in the database.


        -- Step 1. Insert rows from the spec that do not have a match in the database.

        insert into AVAILABLESUMMARYTILE
        (
            ID,
            DATAFORMINSTANCECATALOGID,
            SUMMARYTILECONTEXTID,
            TILENAME,
            SEQUENCE,
            ISVISIBLEBYDEFAULT,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            newid(),
            @DATAFORMINSTANCECATALOGID,
            CATEGORYTABLE.SUMMARYTILECONTEXTID,
            CATEGORYTABLE.TILENAME,
            CATEGORYTABLE.DEFAULTSEQUENCE,
            CATEGORYTABLE.DEFAULTVISIBLE,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from
            @CATEGORYTABLE as CATEGORYTABLE
        where
            CATEGORYTABLE.EXISTINGROWID is null;


        -- Step 2. Update rows in the database that matched rows in the spec.

        update dbo.AVAILABLESUMMARYTILE
        set
            TILENAME = CATEGORYTABLE.TILENAME,
            SEQUENCE = CATEGORYTABLE.DEFAULTSEQUENCE,
            ISVISIBLEBYDEFAULT = CATEGORYTABLE.DEFAULTVISIBLE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from
            dbo.AVAILABLESUMMARYTILE
            inner join @CATEGORYTABLE as CATEGORYTABLE
                on CATEGORYTABLE.EXISTINGROWID = AVAILABLESUMMARYTILE.ID

        -- Step 3. Delete rows in the database that are not present in the database.

        --         Make sure we use CONTEXT_INFO appropriately for the audit info.


        set @CONTEXTCACHE = CONTEXT_INFO();
        set CONTEXT_INFO @CHANGEAGENTID;

        delete from dbo.AVAILABLESUMMARYTILE
        where
            DATAFORMINSTANCECATALOGID = @DATAFORMINSTANCECATALOGID
            and SUMMARYTILECONTEXTID not in (
                select SUMMARYTILECONTEXTID from @CATEGORYTABLE
            );

        if not @CONTEXTCACHE is null
            set CONTEXT_INFO @CONTEXTCACHE;

    end -- @SUMMARYTILE is not empty

    else
    begin
        -- @SUMMARYTILE is null or empty

        -- Remove the summary tile information in the database, if it's there.

        -- Make sure we use CONTEXT_INFO appropriately for the audit info.


        set @CONTEXTCACHE = CONTEXT_INFO();
        set CONTEXT_INFO @CHANGEAGENTID;

        delete from dbo.AVAILABLESUMMARYTILE
        where DATAFORMINSTANCECATALOGID = @DATAFORMINSTANCECATALOGID;

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

end try
begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
end catch

return 0;