USP_GLOBALCHANGE_UPDATEGIFTAIDSTATUS

CREATE procedure dbo.USP_GLOBALCHANGE_UPDATEGIFTAIDSTATUS
(
    @IDSETREGISTERID uniqueidentifier = null, 
    @CHANGEAGENTID uniqueidentifier = null,
    @ASOF as datetime = null,
    @NUMBERADDED int = 0 output,
    @NUMBEREDITED int = 0 output,
    @NUMBERDELETED int = 0 output,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null
)
with execute as owner as     
    set nocount off;

    set @NUMBERADDED = 0;
    set @NUMBEREDITED = 0;
    set @NUMBERDELETED = 0; 

    declare @CURRENTDATE datetime
    declare @BPID uniqueidentifier = '3269A1D1-31CB-4D28-945C-B7623A3EFCCA';

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

    set @CURRENTDATE = GetDate()      

    begin try

        set nocount on;

        -- Using temp table instead of table variable since it needs to be accessed by the dynamic sql

        create table #GIFTAIDRECORDS  
        (
            ID uniqueidentifier,
            RULES_STATUS bit,
            ATTRIBUTES_STATUS bit
        );

        declare @SQLTOEXEC nvarchar(max);
        set @SQLTOEXEC = '    insert into #GIFTAIDRECORDS (ID, RULES_STATUS, ATTRIBUTES_STATUS) 
                            select
                                REVENUESPLITGIFTAID.ID,
                                REVENUESPLITGIFTAID.RULES_STATUS,
                                REVENUESPLITGIFTAID.ATTRIBUTES_STATUS
                            from dbo.REVENUESPLITGIFTAID '

        if @IDSETREGISTERID is not null
            set @SQLTOEXEC = @SQLTOEXEC + ' inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID) as SELECTION on REVENUESPLITGIFTAID.ID = SELECTION.ID '

        if @STARTDATE is not null or @ENDDATE is not null
            set @SQLTOEXEC = @SQLTOEXEC + ' inner join dbo.REVENUESPLIT on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID 
                                            inner join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID '

        set @SQLTOEXEC = @SQLTOEXEC + '    where 
                                        (
                                            dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
                                            or exists
                                            (
                                                select 1 
                                                from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLITGIFTAID.ID) REVSITES
                                                where dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERID, @BPID, REVSITES.SITEID) = 1
                                            )
                                        ) '

        if @STARTDATE is not null
            set @SQLTOEXEC = @SQLTOEXEC + ' and REVENUE.[DATE] >= @STARTDATE '

        if @ENDDATE is not null
            set @SQLTOEXEC = @SQLTOEXEC + ' and REVENUE.[DATE] <= @ENDDATE '

        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
        set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

        exec sp_executesql @SQLTOEXEC, N'@IDSETREGISTERID uniqueidentifier,@STARTDATE datetime,@ENDDATE datetime,@CURRENTAPPUSERID uniqueidentifier,@BPID uniqueidentifier',
            @IDSETREGISTERID=@IDSETREGISTERID,@STARTDATE=@STARTDATE,@ENDDATE=@ENDDATE,@CURRENTAPPUSERID=@CURRENTAPPUSERID,@BPID=@BPID;

        -- Update all disqualified by rules and mark all as qualified based on Attributes...

        update rsga set
            RULES_STATUS = dbo.UFN_GIFTAIDSTATUS_GETBASEDONRULES(rsga.ID),
            ATTRIBUTES_STATUS = 1,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE                    
        from 
            dbo.REVENUESPLITGIFTAID rsga
            inner join #GIFTAIDRECORDS GIFTAID on rsga.ID = GIFTAID.ID
        where 
            rsga.TAXCLAIMNUMBER = '' and
            rsga.PENDINGR68STATUSID is null;

        -- Iterate through Attributes which disqualify revenue and mark those affected as disqualified

        declare GIFTAIDATTRIB_CURSOR cursor LOCAL FAST_FORWARD READ_ONLY for 
            select 
                GIFTAIDDISQUALIFIEDBYATTRIBUTE.ATTRIBUTECATEGORYID,
                ATTRIBUTECATEGORY.ONLYALLOWONEPERRECORD,         
                ATTRIBUTECATEGORY.VALUECOLUMNNAME,
                case GIFTAIDDISQUALIFIEDBYATTRIBUTE.DATATYPE 
                      when 0 then TEXTVALUE when 1 then convert(nvarchar(100),GIFTAIDDISQUALIFIEDBYATTRIBUTE.NUMBERVALUE) 
                      when 2 then convert(nvarchar(100),GIFTAIDDISQUALIFIEDBYATTRIBUTE.DATEVALUE, 121) 
                      when 3 then Convert(nvarchar(100),GIFTAIDDISQUALIFIEDBYATTRIBUTE.CURRENCYVALUE) 
                      when 4 then convert(nvarchar(100),GIFTAIDDISQUALIFIEDBYATTRIBUTE.BOOLEANVALUE) 
                      when 5 then convert(nvarchar(100),GIFTAIDDISQUALIFIEDBYATTRIBUTE.CODETABLEVALUE) 
                      when 6 then Convert(nvarchar(36),GIFTAIDDISQUALIFIEDBYATTRIBUTE.CONSTITUENTID) 
                      when 7 then GIFTAIDDISQUALIFIEDBYATTRIBUTE.FUZZYDATEVALUE 
                      when 8 then GIFTAIDDISQUALIFIEDBYATTRIBUTE.HOURMINUTEVALUE 
                      when 9 then GIFTAIDDISQUALIFIEDBYATTRIBUTE.MEMOVALUE 
                end as GIFTAIDVALUE 
            from dbo.GIFTAIDDISQUALIFIEDBYATTRIBUTE 
            inner join dbo.ATTRIBUTECATEGORY
                on GIFTAIDDISQUALIFIEDBYATTRIBUTE.ATTRIBUTECATEGORYID = ATTRIBUTECATEGORY.ID

        declare @ATTRIBUTECATEGORYID uniqueidentifier;
        declare @ONLYALLOWONEPERRECORD bit;
        declare @DISQVALUE nvarchar(max);
        declare @COLUMNNAME nvarchar(128);

        declare @sql nvarchar(max);

        open GIFTAIDATTRIB_CURSOR;
        fetch next from GIFTAIDATTRIB_CURSOR into @ATTRIBUTECATEGORYID, @ONLYALLOWONEPERRECORD, @COLUMNNAME, @DISQVALUE;

        while @@FETCH_STATUS = 0
            begin 
                declare @ATTRIBUTETABLENAME nvarchar(128) = 'ATTRIBUTE' + REPLACE(cast(@ATTRIBUTECATEGORYID as nvarchar(36)), '-', '');

                set @sql = 
                    'update rsga set rsga.ATTRIBUTES_STATUS = 0, rsga.CHANGEDBYID = ''' + cast(@CHANGEAGENTID as nvarchar(36)) + ''', rsga.DATECHANGED = ''' + cast(@CURRENTDATE as nvarchar(100)) + ''' '  +
                    'from dbo.REVENUESPLITGIFTAID rsga '

                if @IDSETREGISTERID is not null
                    set @sql = @sql + 'inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(''' + cast(@IDSETREGISTERID as nvarchar(36)) + ''') as SELECTION on rsga.ID = SELECTION.ID ';

                set @sql = @sql + 'inner join dbo.REVENUESPLIT on rsga.ID = REVENUESPLIT.ID '

                set @sql = @sql + 'inner join dbo.' + @ATTRIBUTETABLENAME + ' '

                if @ONLYALLOWONEPERRECORD = 0
                    set @sql = @sql + 'on ' + @ATTRIBUTETABLENAME + '.REVENUEID = REVENUESPLIT.REVENUEID ';     
                else
                    set @sql = @sql + 'on ' + @ATTRIBUTETABLENAME + '.ID = REVENUESPLIT.REVENUEID ';

                -- Disqualify non-claimed applications that have not been disqualified by another attribute value

                set @sql = @sql + 'where rsga.TAXCLAIMNUMBER = '''' and rsga.PENDINGR68STATUSID is null and rsga.ATTRIBUTES_STATUS = 1 and ' + @ATTRIBUTETABLENAME + '.' + @COLUMNNAME + ' = ''' + @DISQVALUE + '''';     

                set @sql = @sql + ' and (
                            dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERIDPARAMETER) = 1
                            or exists
                            (
                                select 1 
                                from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(rsga.ID) REVSITES
                                where dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERIDPARAMETER, @BPIDPARAMETER, REVSITES.SITEID) = 1
                            )
                        )';

                declare @IDSETPARAMETERDEFINITION nvarchar(100) = '@CURRENTAPPUSERIDPARAMETER uniqueidentifier, @BPIDPARAMETER uniqueidentifier';
                exec sp_executesql @sql, @IDSETPARAMETERDEFINITION, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID;

                fetch next from GIFTAIDATTRIB_CURSOR into @ATTRIBUTECATEGORYID, @ONLYALLOWONEPERRECORD, @COLUMNNAME, @DISQVALUE;
            end 

        close GIFTAIDATTRIB_CURSOR;
        deallocate GIFTAIDATTRIB_CURSOR;

        -- Set tax claim amount to zero, for splits that were qualified and are no longer qualified

        update rsga 
        set rsga.BASETAXCLAIMAMOUNT = convert(money, 0),
            rsga.TRANSITIONALTAXCLAIMAMOUNT = convert(money, 0),
            rsga.TRANSACTIONBASETAXCLAIMAMOUNT = convert(money, 0),
            rsga.TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT = convert(money, 0),
            rsga.ORGANIZATIONBASETAXCLAIMAMOUNT = convert(money, 0),
            rsga.ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT = convert(money, 0),
            rsga.CHANGEDBYID = @CHANGEAGENTID,
            rsga.DATECHANGED = @CURRENTDATE
        from dbo.REVENUESPLITGIFTAID rsga 
            inner join #GIFTAIDRECORDS GIFTAID on rsga.ID = GIFTAID.ID 
        where (GIFTAID.RULES_STATUS = 1 and rsga.RULES_STATUS = 0) or 
            (GIFTAID.ATTRIBUTES_STATUS = 1 and rsga.ATTRIBUTES_STATUS = 0);

        -- Calculate the tax claim amount, for splits that were not qualified and are now qualified

        update rsga 
        set rsga.BASETAXCLAIMAMOUNT = NEWTAXCLAIMAMOUNTS.BASETAXCLAIMAMOUNT, 
            rsga.TRANSITIONALTAXCLAIMAMOUNT = NEWTAXCLAIMAMOUNTS.TRANSITIONALTAXCLAIMAMOUNT, 
            rsga.TRANSACTIONBASETAXCLAIMAMOUNT = NEWTAXCLAIMAMOUNTS.TRANSACTIONBASETAXCLAIMAMOUNT, 
            rsga.TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT = NEWTAXCLAIMAMOUNTS.TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT,
            rsga.ORGANIZATIONBASETAXCLAIMAMOUNT = NEWTAXCLAIMAMOUNTS.ORGANIZATIONBASETAXCLAIMAMOUNT, 
            rsga.ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT = NEWTAXCLAIMAMOUNTS.ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT,
            rsga.CHANGEDBYID = @CHANGEAGENTID, 
            rsga.DATECHANGED = @CURRENTDATE
        from dbo.REVENUESPLITGIFTAID rsga 
            inner join #GIFTAIDRECORDS GIFTAID on rsga.ID = GIFTAID.ID 
            cross apply dbo.UFN_GIFTAID_CALCULATETAXCLAIMAMOUNT(rsga.ID) NEWTAXCLAIMAMOUNTS 
        where (GIFTAID.RULES_STATUS = 0 and rsga.RULES_STATUS = 1) or 
            (GIFTAID.ATTRIBUTES_STATUS = 0 and rsga.ATTRIBUTES_STATUS = 1) and
            rsga.DECLINESGIFTAID = 0;

        -- Count the number of Revenue Application records updated

        select 
            @NUMBEREDITED = count(*)
        from dbo.REVENUESPLITGIFTAID rsga
        inner join #GIFTAIDRECORDS GIFTAID 
            on rsga.ID = GIFTAID.ID 
        where rsga.RULES_STATUS <> GIFTAID.RULES_STATUS or
            rsga.ATTRIBUTES_STATUS <> GIFTAID.ATTRIBUTES_STATUS;

        drop table #GIFTAIDRECORDS
    end try

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