USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE

CREATE procedure dbo.USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE
(
    @IDSETREGISTERID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @ASOF as datetime = null,
    @NUMBERADDED int = 0 output,
    @NUMBEREDITED int = 0 output,
    @NUMBERDELETED int = 0 output,
    @RECORDTYPECODE tinyint = 0,
    @CHOSENITEMS xml = null,
    @ACTIONID int = 0,
    @PREVIOUSVALUE uniqueidentifier = null,
    @VALUE nvarchar(255) = null,
    @LISTVALUE uniqueidentifier = null,
    @ATTRIBUTECATEGORY uniqueidentifier = null,
    @CONSTITUENTID uniqueidentifier = null,
    @DATEVALUE datetime = null,
    @FUZZYDATEVALUE dbo.UDT_FUZZYDATE = '00000000',
    @TIMEVALUE dbo.UDT_HOURMINUTE = '0000',
    @CURRENCYVALUE money = null,
    @NUMBERVALUE int = 0,
    @CODETABLEVALUE uniqueidentifier = null,
    @MEMOVALUE nvarchar(max) = null,
    @CURRENCYID uniqueidentifier = null,
    @INCLUDECOMMENT bit = 0,
    @COMMENTACTION tinyint = 255,
    @COMMENTTEXT nvarchar(255)
)
with execute as owner
as
    set nocount off;

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate();
    set @NUMBERADDED = 0;
    set @NUMBEREDITED = 0;
    set @NUMBERDELETED = 0; 

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

    begin try
    if @RECORDTYPECODE in (1,2)
    begin

        if object_id('tempdb..#USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_ADD') is not null
        begin
            drop table #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_ADD;
        end;
        create table #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_ADD(ID uniqueidentifier primary key);

        if object_id('tempdb..#USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_UPDATE') is not null
        begin
            drop table #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_UPDATE;
        end;
        create table #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_UPDATE(ID uniqueidentifier primary key);

        if object_id('tempdb..#USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_DELETE') is not null
        begin
            drop table #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_DELETE;
        end;
        create table #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_DELETE(ID uniqueidentifier primary key);

        if object_id('tempdb..#USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_SELECTION') is not null
        begin
            drop table #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_SELECTION;
        end;
        create table #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_SELECTION(ID uniqueidentifier primary key);


        insert into #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_SELECTION
            select distinct ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID)
            where @IDSETREGISTERID is not null

            union all

            select ID from dbo.DESIGNATION
            where @IDSETREGISTERID is null and @RECORDTYPECODE = 1

            union all

            select ID from dbo.DESIGNATIONLEVEL
            where @IDSETREGISTERID is null and @RECORDTYPECODE = 2;



        declare @TABLECATALOGID uniqueidentifier;
        declare @TABLENAME nvarchar(41) = 'ATTRIBUTE'
        declare @VALUECOLUMNNAME nvarchar(128);
        declare @IDENTITYCOLUMN nvarchar(max);
        declare @DATATYPECODE tinyint;
        declare @ONEPERRECORD bit;
        declare @BOOLEANVALUE bit;
        declare @PREVIOUSBOOLEANVALUE nvarchar(2);

        declare @STARTDATE datetime;
        declare @ENDDATE datetime;
        declare @STARTDATEACTION tinyint = 255;
        declare @ENDDATEACTION tinyint = 255;

        select
            @STARTDATE = T.c.value('(NEWVALUE)[1]', 'datetime'),
            @STARTDATEACTION = T.c.value('(SELECTEDACTION)[1]', 'int')        
        from @CHOSENITEMS.nodes('//CHOSENITEMS/ITEM') T(c)
        where T.c.value('(ID)[1]', 'int') = 0;

        if @STARTDATEACTION not in (1,2)
            set @STARTDATE = null;

        select
            @ENDDATE = T.c.value('(NEWVALUE)[1]', 'datetime'),
            @ENDDATEACTION = T.c.value('(SELECTEDACTION)[1]', 'int')        
        from @CHOSENITEMS.nodes('//CHOSENITEMS/ITEM') T(c)
        where T.c.value('(ID)[1]', 'int') = 1;

        if @ENDDATEACTION not in (1,2)
            set @ENDDATE = null;

        if @INCLUDECOMMENT = 0
            set @COMMENTACTION = 255;
        else if @COMMENTACTION = 3
            set @COMMENTTEXT = '';

        select
            @TABLECATALOGID = ATTRIBUTECATEGORY.TABLECATALOGID,
            @DATATYPECODE = ATTRIBUTECATEGORY.DATATYPECODE,
            @TABLENAME = TABLECATALOG.TABLENAME,
            @ONEPERRECORD = ATTRIBUTECATEGORY.ONLYALLOWONEPERRECORD,
            @VALUECOLUMNNAME = ATTRIBUTECATEGORY.VALUECOLUMNNAME
        from dbo.ATTRIBUTECATEGORY
        inner join dbo.TABLECATALOG on TABLECATALOG.ID = ATTRIBUTECATEGORY.TABLECATALOGID
        where ATTRIBUTECATEGORY.ID = @ATTRIBUTECATEGORY;

        if @DATATYPECODE = 4
            if @LISTVALUE = '00000000-0000-0000-0000-000000000001'
                set @BOOLEANVALUE = 0;
            else
                set @BOOLEANVALUE = 1;


        select
            @IDENTITYCOLUMN =
            case @ONEPERRECORD
                when 1 then 'ID'
                when 0 then
                    case @RECORDTYPECODE
                        when 1 then 'DESIGNATIONID'
                        when 2 then 'DESIGNATIONLEVELID'
                    end
            end;

        declare @SQL nvarchar(max) = N'';
        declare @PARAMDEF nvarchar(max) = '';

    if @DATATYPECODE = 4
            if @PREVIOUSVALUE = '00000000-0000-0000-0000-000000000001'
                set @PREVIOUSBOOLEANVALUE = 0;
            else
                set @PREVIOUSBOOLEANVALUE = 1;

        if @ACTIONID in (1,2) -- Add value if it does not exist

        begin
            set @SQL = @SQL + N'
                insert into #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_ADD (ID)
                select
                    SELECTION.ID
                from #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_SELECTION as SELECTION
                where not exists (select 1 from dbo.' + @TABLENAME + N' where ' + @IDENTITYCOLUMN + N' = SELECTION.ID ';

            if @ONEPERRECORD = 0
                set @SQL = @SQL + N' and ' + @VALUECOLUMNNAME + N' = @NEWVALUE);
                ';
            else
                set @SQL = @SQL + N');
                ';
        end

        if @ACTIONID = 2 -- [Add value if it does not exist and] overwrite existing values

        begin
            if @ONEPERRECORD = 1
                set @SQL = @SQL + N'
                    insert into #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_UPDATE (ID)
                    select
                        ATTRIBUTETABLE.ID
                    from #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_SELECTION as SELECTION
                    inner join dbo.' + @TABLENAME + N' as ATTRIBUTETABLE on ATTRIBUTETABLE.' + @IDENTITYCOLUMN + N' = SELECTION.ID;
                ';
        end
        else if @ACTIONID = 3 -- Replace existing value

        begin
            if @ONEPERRECORD = 0
            begin
                    set @SQL = @SQL + N'
                        insert into #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_UPDATE (ID)
                        select
                            ATTRIBUTETABLE.ID
                        from #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_SELECTION as SELECTION
                        inner join dbo.' + @TABLENAME + N' as ATTRIBUTETABLE on ATTRIBUTETABLE.' + @IDENTITYCOLUMN + N' = SELECTION.ID
                        where ATTRIBUTETABLE.' + @VALUECOLUMNNAME + N' ='
                        if @DATATYPECODE=4 
                            set @SQL = @SQL + N''+@PREVIOUSBOOLEANVALUE+ '';
                        else
                            set @SQL = @SQL + N'(select ' + @VALUECOLUMNNAME + N' from dbo.' + @TABLENAME + N' where ID = @PREVIOUSVALUE)
           ';
                if @DATATYPECODE = 3
                    set @SQL = @SQL + N'
                        and ATTRIBUTETABLE.CURRENCYID = (select CURRENCYID from dbo.' + @TABLENAME + N' where ID = @PREVIOUSVALUE)
                    ';
                set @SQL = @SQL + N';';
            end
        end
        else if @ACTIONID = 4 -- Delete value if it exists

        begin
            set @SQL = @SQL + N'
                insert into #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_DELETE (ID)
                select
                    ATTRIBUTETABLE.ID
                from #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_SELECTION as SELECTION
                inner join dbo.' + @TABLENAME + N' as ATTRIBUTETABLE on ATTRIBUTETABLE.' + @IDENTITYCOLUMN + N' = SELECTION.ID
                where ATTRIBUTETABLE.' + @VALUECOLUMNNAME + N' = @NEWVALUE;
            ';
        end
        else if @ACTIONID = 5 -- Delete all values for this attribute category

        begin
            if @ONEPERRECORD = 0
                set @SQL = @SQL + N'
                    insert into #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_DELETE (ID)
                    select
                        ATTRIBUTETABLE.ID
                    from #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_SELECTION as SELECTION
                    inner join dbo.' + @TABLENAME + N' as ATTRIBUTETABLE on ATTRIBUTETABLE.' + @IDENTITYCOLUMN + N' = SELECTION.ID;
                ';
        end

        set @SQL = @SQL + N'
            insert into dbo.' + @TABLENAME + ' 
            (
                ID,
                ';
        if @ONEPERRECORD = 0 set @SQL = @SQL + @IDENTITYCOLUMN + N', ';
        set @SQL = @SQL + N'
                ' + @VALUECOLUMNNAME + N',
                ';
                if @COMMENTACTION in (1,2)        set @SQL = @SQL + N'COMMENT, ';
                if @STARTDATEACTION in (1,2)    set @SQL = @SQL + N'STARTDATE, ';
                if @ENDDATEACTION in (1,2)        set @SQL = @SQL + N'ENDDATE, ';
        set @SQL = @SQL + N'
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
                ';
        if @DATATYPECODE = 3 set @SQL = @SQL + N',CURRENCYID ,ORGANIZATIONEXCHANGERATEID, ORGANIZATIONVALUE ';
        set @SQL = @SQL + N'
            )
            select 
                ';
        if @ONEPERRECORD = 0 set @SQL = @SQL + N'newid(), ';
        set @SQL = @SQL + N'
                RECORD.ID,
                @NEWVALUE,
                ';
                if @COMMENTACTION in (1,2)        set @SQL = @SQL + N'@COMMENTTEXT, ';
                if @STARTDATEACTION in (1,2)    set @SQL = @SQL + N'@STARTDATE, ';
                if @ENDDATEACTION in (1,2)        set @SQL = @SQL + N'@ENDDATE, ';
        set @SQL = @SQL + N'
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
                ';
        if @DATATYPECODE = 3 set @SQL = @SQL + N',@CURRENCYID, CURRENCYVALUE.ORGANIZATIONEXCHANGERATEID, CURRENCYVALUE.ORGANIZATIONAMOUNT ';
        set @SQL = @SQL + N'
            from #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_ADD as RECORD';
        if @DATATYPECODE = 3 set @SQL = @SQL + N'
            cross join dbo.UFN_CURRENCY_GETCURRENCYVALUES_2(@NEWVALUE,@CURRENTDATE,@CURRENCYID,null,null,null,null,null,null,1) as CURRENCYVALUE';
        set @SQL = @SQL + N';
            ';

        --Remove records from update with date fields that would violate the table constraint, CK_#TABLENAME#_VALIDDATERANGE

        if (@STARTDATEACTION in (1,2) and @ENDDATEACTION <> 3) or (@STARTDATEACTION <> 3 and @ENDDATEACTION in (1,2))
        begin
            set @SQL = @SQL + N'
                delete from #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_UPDATE
                from #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_UPDATE as SELECTION
                inner join dbo.' + @TABLENAME + N' as ATTRIBUTETABLE on ATTRIBUTETABLE.ID = SELECTION.ID
                where (
                ';
            if @ENDDATEACTION in (1,2)
            begin
                set @SQL = @SQL + N'ATTRIBUTETABLE.STARTDATE is not null and ATTRIBUTETABLE.STARTDATE > @ENDDATE
                ';
                if @STARTDATEACTION <> 2 and @ENDDATEACTION = 1
                    set @SQL = @SQL + N'and ATTRIBUTETABLE.ENDDATE is null
                    ';
            end
            if @STARTDATEACTION in (1, 2) and @ENDDATEACTION in (1, 2)
                set @SQL = @SQL + N'
                ) or (
                ';
            if @STARTDATEACTION in (1,2)
            begin
                set @SQL = @SQL + N'ATTRIBUTETABLE.ENDDATE is not null and ATTRIBUTETABLE.ENDDATE < @STARTDATE
                ';
                if @STARTDATEACTION = 1 and @ENDDATEACTION <> 2
                    set @SQL = @SQL + N'and ATTRIBUTETABLE.STARTDATE is null
                    ';
            end
            set @SQL = @SQL + N');
                ';
        end

        set @SQL = @SQL + N'
            update
                dbo.' + @TABLENAME + N' 
            set
                ' + @VALUECOLUMNNAME + N' = @NEWVALUE, 
                ';

        if @COMMENTACTION = 1                set @SQL = @SQL + N'COMMENT = case when len(COMMENT) = 0 then @COMMENTTEXT else COMMENT end, ';
        else if @COMMENTACTION in (2,3)        set @SQL = @SQL + N'COMMENT = @COMMENTTEXT, ';

        if @STARTDATEACTION = 1                set @SQL = @SQL + N'STARTDATE = case when STARTDATE is null then @STARTDATE else STARTDATE end, ';
        else if @STARTDATEACTION in (2,3)    set @SQL = @SQL + N'STARTDATE = @STARTDATE, ';

        if @ENDDATEACTION = 1                set @SQL = @SQL + N'ENDDATE = case when ENDDATE is null then @ENDDATE else ENDDATE end, ';
        else if @ENDDATEACTION in (2,3)        set @SQL = @SQL + N'ENDDATE = @ENDDATE, ';

        set @SQL = @SQL + N'
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
                ';
        if @DATATYPECODE = 3 set @SQL = @SQL + N',CURRENCYID = @CURRENCYID, ORGANIZATIONEXCHANGERATEID = CURRENCYVALUE.ORGANIZATIONEXCHANGERATEID, ORGANIZATIONVALUE = CURRENCYVALUE.ORGANIZATIONAMOUNT ';
        set @SQL = @SQL + N'
            from dbo.' + @TABLENAME;
        if @DATATYPECODE = 3 set @SQL = @SQL + N'
            cross apply dbo.UFN_CURRENCY_GETCURRENCYVALUES_2(@NEWVALUE,DATEADDED,@CURRENCYID,null,null,null,null,null,null,1) as CURRENCYVALUE';
        set @SQL = @SQL + N'
            where
                ID in (select ID from #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_UPDATE);
        ';

        set @SQL = @SQL + N'
            delete from dbo.' + @TABLENAME + N' where ID in (select ID from #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_DELETE);
        ';

        set @PARAMDEF = '
            @COMMENTTEXT nvarchar(255), 
            @STARTDATE datetime, 
            @ENDDATE datetime, 
            @CHANGEAGENTID uniqueidentifier, 
            @CURRENTDATE datetime, 
            @CURRENCYID uniqueidentifier,
            @PREVIOUSVALUE uniqueidentifier,
            @NEWVALUE ';

        select
            @PARAMDEF = @PARAMDEF +
                case @DATATYPECODE
                    when 0 then 'nvarchar(255)'
                    when 1 then 'int'
                    when 2 then 'datetime'
                    when 3 then 'money'
                    when 4 then 'bit'
                    when 5 then 'uniqueidentifier'
                    when 6 then 'uniqueidentifier'
                    when 7 then 'dbo.UDT_FUZZYDATE'
                    when 8 then 'dbo.UDT_HOURMINUTE'
                    when 9 then 'nvarchar(max)'
                end;

        if @DATATYPECODE = 0
            exec sp_executesql @SQL, @PARAMDEF, @COMMENTTEXT, @STARTDATE, @ENDDATE, @CHANGEAGENTID, @CURRENTDATE, @CURRENCYID, @PREVIOUSVALUE, @VALUE;
        else if @DATATYPECODE = 1
            exec sp_executesql @SQL, @PARAMDEF, @COMMENTTEXT, @STARTDATE, @ENDDATE, @CHANGEAGENTID, @CURRENTDATE, @CURRENCYID, @PREVIOUSVALUE, @NUMBERVALUE;
        else if @DATATYPECODE = 2
            exec sp_executesql @SQL, @PARAMDEF, @COMMENTTEXT, @STARTDATE, @ENDDATE, @CHANGEAGENTID, @CURRENTDATE, @CURRENCYID, @PREVIOUSVALUE, @DATEVALUE;
        else if @DATATYPECODE = 3
            exec sp_executesql @SQL, @PARAMDEF, @COMMENTTEXT, @STARTDATE, @ENDDATE, @CHANGEAGENTID, @CURRENTDATE, @CURRENCYID, @PREVIOUSVALUE, @CURRENCYVALUE;
        else if @DATATYPECODE = 4
            exec sp_executesql @SQL, @PARAMDEF, @COMMENTTEXT, @STARTDATE, @ENDDATE, @CHANGEAGENTID, @CURRENTDATE, @CURRENCYID, @PREVIOUSVALUE, @BOOLEANVALUE;
        else if @DATATYPECODE = 5
            exec sp_executesql @SQL, @PARAMDEF, @COMMENTTEXT, @STARTDATE, @ENDDATE, @CHANGEAGENTID, @CURRENTDATE, @CURRENCYID, @PREVIOUSVALUE, @CODETABLEVALUE;
        else if @DATATYPECODE = 6
            exec sp_executesql @SQL, @PARAMDEF, @COMMENTTEXT, @STARTDATE, @ENDDATE, @CHANGEAGENTID, @CURRENTDATE, @CURRENCYID, @PREVIOUSVALUE, @CONSTITUENTID;
        else if @DATATYPECODE = 7
            exec sp_executesql @SQL, @PARAMDEF, @COMMENTTEXT, @STARTDATE, @ENDDATE, @CHANGEAGENTID, @CURRENTDATE, @CURRENCYID, @PREVIOUSVALUE, @FUZZYDATEVALUE;
        else if @DATATYPECODE = 8
            exec sp_executesql @SQL, @PARAMDEF, @COMMENTTEXT, @STARTDATE, @ENDDATE, @CHANGEAGENTID, @CURRENTDATE, @CURRENCYID, @PREVIOUSVALUE, @TIMEVALUE;
        else if @DATATYPECODE = 9
            exec sp_executesql @SQL, @PARAMDEF, @COMMENTTEXT, @STARTDATE, @ENDDATE, @CHANGEAGENTID, @CURRENTDATE, @CURRENCYID, @PREVIOUSVALUE, @MEMOVALUE;

        select
            @NUMBERADDED = (select count(*) from #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_ADD),
            @NUMBEREDITED = (select count(*) from #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_UPDATE),
            @NUMBERDELETED = (select count(*) from #USP_GLOBALCHANGE_MANAGEPURPOSEDESIGNATIONATTRIBUTE_DELETE)

    end
    end try

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