USP_GLOBALCHANGE_ADDORCHANGEINCENTIVELEVEL

CREATE procedure dbo.USP_GLOBALCHANGE_ADDORCHANGEINCENTIVELEVEL
(
    @SOURCEEVENT uniqueidentifier,
    @SOURCEINCENTIVELEVEL uniqueidentifier,
    @SELECTIONOPTIONFORUPDATE smallint,
    @SELECTEDEVENTSFORUPDATE xml= null,
    @SELECTEDINCENTIVELEVELSFORUPDATE xml= null,    
    @CURRENTAPPUSERID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @ASOF as datetime = null,
    @NUMBERADDED int = 0 output,
    @NUMBEREDITED int = 0 output,
    @NUMBERDELETED int = 0 output
)
as            
    set nocount off;

    declare @CURRENTDATE datetime,
            @ErrorCount int,
            @ErrorText nvarchar(max),
            @Count int,
            @Index int,
            @EventID uniqueidentifier,
            @FAFIncentiveLevelID uniqueidentifier

    declare    @NAME nvarchar(100),    
            @DESCRIPTION nvarchar(1000),
            @TYPECODE tinyint,
            @MAXIMUMAMOUNT money,     
            @MAXIMUMRETENTION decimal(6,3),
            @MAXIMUMNUMBER int,
            @BENEFITS xml,
            @SOURCEINCENTIVETYPE nvarchar(200),
            @SOURCEMINVALUE nvarchar(100)

    declare @EventInfo table(ID int identity(1,1), EventID uniqueidentifier, EventName nvarchar(max))
    declare @EventHaveLevelWithSameTypeAndValue table(ID int identity(1,1), EventID uniqueidentifier, EventName nvarchar(max))
       declare @ReplaceTable table (ID int identity(1,1), IncentiveID uniqueidentifier)


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

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


    -- get data of source incentive level

    if @SOURCEINCENTIVELEVEL is not null
        begin
            exec dbo.USP_DATAFORMTEMPLATE_EDITLOAD_INCENTIVELEVEL 
                @ID = @SOURCEINCENTIVELEVEL,
                @NAME = @NAME output,
                @DESCRIPTION = @DESCRIPTION output,
                @TYPECODE = @TYPECODE output,
                @MAXIMUMAMOUNT = @MAXIMUMAMOUNT output,
                @MAXIMUMRETENTION = @MAXIMUMRETENTION output,
                @MAXIMUMNUMBER = @MAXIMUMNUMBER output,
                @BENEFITS = @BENEFITS output
        end
    --reget @benefits    

    set @BENEFITS= (SELECT [BENEFITID], [DETAILS], '00000000-0000-0000-0000-000000000000', [QUANTITY], [TOTALVALUE], [UNITVALUE], DEDUCTIBLEUNITVALUE, DEDUCTIBLEVALUE  
                 FROM dbo.UFN_INCENTIVELEVEL_GETBENEFITS(@SOURCEINCENTIVELEVEL)
                 for xml raw('ITEM'),type,elements,root('BENEFITS'),BINARY BASE64)
    --get the type name             

    set @SOURCEINCENTIVETYPE = case @TYPECODE 
                                    when 1 then 'Fundraising' 
                                    when 2 then 'Participant recruitment' 
                                    when 3 then 'Donation retention' 
                                    when 4 then 'Communications' 
                                    else '' 
                               end
    --get the minimum value

    set @SOURCEMINVALUE =  case @TYPECODE 
                                when 1 then '$' + CAST(@MAXIMUMAMOUNT AS nvarchar(100)) 
                                when 2 then CAST(@MAXIMUMNUMBER AS nvarchar(100))
                                when 3 then CAST(cast(@MAXIMUMRETENTION as decimal(18,2)) AS nvarchar(100)) +'%'
                                when 4 then CAST(@MAXIMUMNUMBER AS nvarchar(100))
                                else CAST(@MAXIMUMAMOUNT AS nvarchar(100)) 
                            end 

begin tran                
    begin try
        if @SELECTIONOPTIONFORUPDATE = 1 --add to selected event

            begin
                insert into @EventInfo(EventID)
                SELECT EVENTID FROM (SELECT T.c.value('(EVENTID)[1]','uniqueidentifier') AS 'EVENTID'
                FROM @SELECTEDEVENTSFORUPDATE.nodes('/SELECTEDEVENTSFORUPDATE/ITEM') T(c)) XMLTABLE
                WHERE exists(
                    select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(XMLTABLE.EVENTID) EVENTSITE
                    where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
                      or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,'800093A6-B727-490B-8CC4-C0C0CF2148F0',20) 
                      where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null)) )
                    ) 

                insert into @EventHaveLevelWithSameTypeAndValue(EventID,EventName)
                select E.ID, E.NAME 
                from dbo.FAFINCENTIVELEVEL FIL
                inner join dbo.EVENT E on E.ID = fil.EVENTID 
                where FIL.TYPECODE = @TYPECODE
                    and FIL.MAXIMUMAMOUNT = @MAXIMUMAMOUNT
                    and FIL.MAXIMUMNUMBER = @MAXIMUMNUMBER
                    and FIL.MAXIMUMRETENTION * 100 = @MAXIMUMRETENTION
                    and E.ID in (select EVENTID from @EventInfo)     

                select @ErrorCount = Count(*) from @EventHaveLevelWithSameTypeAndValue

                 if @ErrorCount > 0
                    begin
                      SELECT @ERRORTEXT = COALESCE(@ERRORTEXT+', ' ,'') + EventName from @EventHaveLevelWithSameTypeAndValue
                      RAISERROR ('Your updates could not be processed because the minimum value to qualify for the incentive must be unique for the incentive type. A %s incentive named "%s" with a minimum value of "%s" already exists for the following event(s): %s. To continue, please remove the incentive level from the event(s) or remove the event(s) from the update.', 11, 1,@SOURCEINCENTIVETYPE, @NAME, @SOURCEMINVALUE, @ERRORTEXT);
                    end

                select @Index = 1, @Count = COUNT(*) from @EventInfo 

                while @Index< = @Count
                    begin
                          select @EventID =EventID from @EventInfo where ID=@Index
                          exec dbo.USP_DATAFORMTEMPLATE_ADD_INCENTIVE_LEVEL 
                                    @EVENTID = @EVENTID,
                                    @CHANGEAGENTID = @CHANGEAGENTID,
                                    @NAME = @NAME,
                                    @DESCRIPTION = @DESCRIPTION,
                                    @TYPECODE = @TYPECODE,
                                    @MAXIMUMAMOUNT = @MAXIMUMAMOUNT,
                                    @MAXIMUMNUMBER = @MAXIMUMNUMBER,
                                    @MAXIMUMRETENTION = @MAXIMUMRETENTION,
                                    @BENEFITS = @BENEFITS
                         select @Index = @Index + 1, @NUMBERADDED = @NUMBERADDED +1
                    end

            end

        if @SELECTIONOPTIONFORUPDATE = 2 --replace selected incentive level

            begin              
                insert into @ReplaceTable(IncentiveID)
                SELECT INCENTIVEID FROM (SELECT T.c.value('(RECORDID)[1]','uniqueidentifier') AS 'INCENTIVEID'
                FROM @SELECTEDINCENTIVELEVELSFORUPDATE.nodes('/SELECTEDINCENTIVELEVELSFORUPDATE/ITEM') T(c)) XMLTABLE
                INNER JOIN FAFINCENTIVELEVEL FIL ON XMLTABLE.INCENTIVEID = FIL.ID 
                WHERE exists(
                    select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(FIL.EVENTID) EVENTSITE
                    where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
                    or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,'800093A6-B727-490B-8CC4-C0C0CF2148F0',20) 
                    where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null)) )
                )      

                insert into @EventInfo(EventID,EventName)
                Select FIL.EventID, E.Name 
                from dbo.FAFINCENTIVELEVEL FIL
                inner join dbo.Event E on FIL.EVENTID = E.ID
                where FIL.ID in (select IncentiveID from @ReplaceTable)


                select @ErrorCount = COUNT(EVENTID) from @EventInfo group by EventID HAVING (Count(EventID) > 1) 
                if @ErrorCount > 0
                    begin
                        SELECT @ERRORTEXT = COALESCE(@ERRORTEXT+', ' ,'') + EventName from @EventInfo Group By EventName HAVING (Count(EventName) > 1 )
                        RAISERROR ('Your updates could not be processed because the minimum value to qualify for the incentive must be unique for the incentive type in each event. You selected to update more than one %s incentive level for the following event(s): %s. To continue, please select only one incentive level for each event.', 11, 1, @SOURCEINCENTIVETYPE, @ERRORTEXT);
                    end


                insert into @EventHaveLevelWithSameTypeAndValue(EventID,EventName)
                select E.ID, E.NAME 
                from dbo.FAFINCENTIVELEVEL FIL
                inner join dbo.EVENT E on E.ID = FIL.EVENTID 
                where FIL.TYPECODE = @TYPECODE
                        and FIL.MAXIMUMAMOUNT = @MAXIMUMAMOUNT
                        and FIL.MAXIMUMNUMBER = @MAXIMUMNUMBER
                        and FIL.MAXIMUMRETENTION * 100 = @MAXIMUMRETENTION
                        and E.ID in (select EVENTID from @EventInfo)   
                        and FIL.ID not in (select IncentiveID from @ReplaceTable)    

                select @ErrorCount = Count(*) from @EventHaveLevelWithSameTypeAndValue                

                if @ErrorCount > 0
                    begin
                        SELECT @ERRORTEXT = COALESCE(@ERRORTEXT+', ' ,'') + EventName from @EventHaveLevelWithSameTypeAndValue
                        RAISERROR ('Your updates could not be processed because the minimum value to qualify for the incentive must be unique for the incentive type. A %s incentive named "%s" with minimum value to qualify of "%s" already exists for the following event(s): %s. To continue, please remove the incentive level in these event(s) or remove the event(s) from the update.', 11, 1, @SOURCEINCENTIVETYPE, @NAME, @SOURCEMINVALUE, @ERRORTEXT);
                    end

                select @Index = 1, @Count = COUNT(*) from @EventInfo 

                while @Index< = @Count
                    begin
                        select @FAFIncentiveLevelID =IncentiveID from @ReplaceTable where ID = @Index 

                        exec dbo.USP_DATAFORMTEMPLATE_EDIT_INCENTIVELEVEL 
                                @ID = @FAFIncentiveLevelID,
                                @CHANGEAGENTID = @CHANGEAGENTID,
                                @NAME = @NAME,
                                @DESCRIPTION = @DESCRIPTION,
                                @TYPECODE = @TYPECODE,
                                @MAXIMUMAMOUNT = @MAXIMUMAMOUNT,
                                @MAXIMUMNUMBER = @MAXIMUMNUMBER ,
                                @MAXIMUMRETENTION = @MAXIMUMRETENTION,
                                @BENEFITS = @BENEFITS
                         select @Index = @Index + 1, @NUMBEREDITED = @NUMBEREDITED +1
                    end
            end

    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        rollback tran
        return 1;
    end catch
commit tran