USP_GLOBALCHANGE_ADDCAMPAIGNTOEVENTPAYMENT

CREATE procedure dbo.USP_GLOBALCHANGE_ADDCAMPAIGNTOEVENTPAYMENT
(
    @CHANGEAGENTID uniqueidentifier = null,
    @ASOF as datetime = null,
    @NUMBERADDED int = 0 output,
    @NUMBEREDITED int = 0 output,
    @NUMBERDELETED int = 0 output,
    @EVENTID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as            
    set nocount off;

    declare @CURRENTDATETIME datetime
    declare @CURRENTDATE date
    declare @TEMP_TABLE_CNT int = 0
    set @CURRENTDATETIME = getdate();
    set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATETIME);
    set @NUMBERADDED = 0;
    set @NUMBEREDITED = 0;
    set @NUMBERDELETED = 0; 

    declare @ISSYSADMIN bit;
    declare @BPID uniqueidentifier;

    set @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
    set @BPID = '3269A1D1-31CB-4D28-945C-B7623A3EFCCA';  

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

    begin try
        -- do the global change operation here, and set @NUMBERADDED, @NUMBEREDITED, and/or @NUMBERDELETED as appropriate

        declare @CAMPAIGNID uniqueidentifier;
        declare @CAMPAIGNSUBPRIORITYID uniqueidentifier;
        declare @REVENUESPLITID uniqueidentifier;
        declare @REVENUESPLITCAMPAIGNID uniqueidentifier;

        declare @TempTbl table( 
                                 EVENTID uniqueidentifier,
                               CAMPAIGNID uniqueidentifier,
                               CAMPAIGNSUBPRIORITYID uniqueidentifier);

        insert into @TempTbl(EVENTID,CAMPAIGNID,CAMPAIGNSUBPRIORITYID) 
        select EVENTID,CAMPAIGNID,CAMPAIGNSUBPRIORITYID
        from dbo.EVENTCAMPAIGN
        where EVENTID = @EVENTID
        -- if campaigns are associated with this event

        set @TEMP_TABLE_CNT = (select count(*) FROM @TempTbl);

        if @TEMP_TABLE_CNT > 0
        begin
            -- Find existing payments that are lacking a record in the REVENUESPLITCAMPAIGN table.

            declare REGISTRANT_CURSOR cursor local fast_forward for
            select  ERP.PAYMENTID,
                            T.CAMPAIGNID,
                            T.CAMPAIGNSUBPRIORITYID 
                from  @TempTbl T
                            inner join dbo.REGISTRANT R on R.EVENTID = T.EVENTID
                            inner join EVENTREGISTRANTPAYMENT ERP on ERP.REGISTRANTID = R.ID
             where  not exists (select 'X' 
                                                from REVENUESPLITCAMPAIGN 
                                                where REVENUESPLITID = ERP.PAYMENTID and CAMPAIGNID = T.CAMPAIGNID
                                                and ISNULL(CAMPAIGNSUBPRIORITYID,0X00) = ISNULL(T.CAMPAIGNSUBPRIORITYID,0X00))
                                and 
                                (
                                    @ISSYSADMIN = 1
                                    or exists
                                    (
                                        select 1 
                                        from dbo.UFN_SITEID_MAPFROM_EVENTID(R.EVENTID) REVSITES
                                        where dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERID, @BPID, REVSITES.SITEID) = 1
                                    )
                                )

            open REGISTRANT_CURSOR;
            fetch next from REGISTRANT_CURSOR into @REVENUESPLITID,@CAMPAIGNID,@CAMPAIGNSUBPRIORITYID;

            while (@@FETCH_STATUS = 0)
            begin
                -- Insert new REVENUESPLITCAMPAIGN records.

                insert into REVENUESPLITCAMPAIGN (ID,REVENUESPLITID,CAMPAIGNID,CAMPAIGNSUBPRIORITYID,
                                                                                    ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                values( newid(),@REVENUESPLITID,@CAMPAIGNID,@CAMPAIGNSUBPRIORITYID,
                                 @CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE)
                set @NUMBERADDED = @NUMBERADDED + 1

                fetch next from REGISTRANT_CURSOR into @REVENUESPLITID,@CAMPAIGNID,@CAMPAIGNSUBPRIORITYID
            end
            close REGISTRANT_CURSOR;
            deallocate REGISTRANT_CURSOR;
        end -- if TempTbl not empty

    -- Find records in the RevenueSplitCampaign table for this event that are lacking a record in

        -- the EVENTCAMPAIGN table


        begin
            -- Find existing payments for this event, that have a record in REVENUESPLITCAMPAIGN 

            -- but the campaign is no longer there.

            declare DELETE_REGISTRANT_CURSOR cursor local fast_forward for
            select  RSC.ID
                from  dbo.EVENTREGISTRANTPAYMENT ERP 
                inner join dbo.REGISTRANT R on R.ID = ERP.REGISTRANTID
                inner join dbo.REVENUESPLITCAMPAIGN RSC on RSC.REVENUESPLITID = ERP.PAYMENTID
where R.EVENTID = @EVENTID
              and (@TEMP_TABLE_CNT = 0 or not exists (    select 'X' 
                                                                                                from @TempTbl 
                                                                                                where CAMPAIGNID = RSC.CAMPAIGNID 
                                                                                                and isnull(RSC.CAMPAIGNSUBPRIORITYID,0x00) = isnull(CAMPAIGNSUBPRIORITYID,0x00)))
              and 
              (
                  @ISSYSADMIN = 1
                  or exists
                  (
                      select 1 
                      from dbo.UFN_SITEID_MAPFROM_EVENTID(R.EVENTID) REVSITES
                      where dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERID, @BPID, REVSITES.SITEID) = 1
                  )
              )                                                


            open DELETE_REGISTRANT_CURSOR;
            fetch next from DELETE_REGISTRANT_CURSOR into @REVENUESPLITCAMPAIGNID;

            while (@@FETCH_STATUS = 0)
            begin
                -- Remove REVENUESPLITCAMPAIGN records because they are no longer associated with a campaign

        delete from REVENUESPLITCAMPAIGN where ID = @REVENUESPLITCAMPAIGNID
                set @NUMBERDELETED = @NUMBERDELETED + 1
                fetch next from DELETE_REGISTRANT_CURSOR into @REVENUESPLITCAMPAIGNID;
            end
            close DELETE_REGISTRANT_CURSOR;
            deallocate DELETE_REGISTRANT_CURSOR;        
        end -- delete REVENUESPLITCAMPAIGN records.

    end try

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