USP_DATAFORMTEMPLATE_EDIT_FAFPROGRAM_2

create procedure dbo.USP_DATAFORMTEMPLATE_EDIT_FAFPROGRAM_2 (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier,
    @NAME nvarchar(100),  
    @DESCRIPTION nvarchar(255),
    @SITES xml,
    @PREVIOUSCAMPAIGNID uniqueidentifier,
    @SELECTEDEVENTS xml
)
as

    set nocount on;

    declare @CURRENTDATE datetime
    declare @defaultSiteId uniqueidentifier
    declare @dSite nvarchar(200)
    declare @REMOVEDEVENTLIST table(EVENTID uniqueidentifier)
    declare @ADDEDEVENTLIST table(EVENTID uniqueidentifier)

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

    set @CURRENTDATE = getdate()

    insert into @REMOVEDEVENTLIST(EVENTID)
    select EVENTID 
    from dbo.EVENTEXTENSION 
    where FAFPROGRAMID  = @ID
    and EVENTID not in (select EVENTID from dbo.UFN_GLOBALCHANGE_GETEVENTLIST_FROMITEMLISTXML(@SELECTEDEVENTS))

    insert into @ADDEDEVENTLIST(EVENTID)
    select EVENTID 
    from dbo.UFN_GLOBALCHANGE_GETEVENTLIST_FROMITEMLISTXML(@SELECTEDEVENTS)
    where EVENTID not in (select EVENTID from dbo.EVENTEXTENSION where FAFPROGRAMID = @ID)

    begin try
    -- handle updating the data
    if dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID) = 1 
        begin 
             if @SITES is null
                raiserror('Site is required.',13,1) 
             else
                 begin        
                        select @defaultSiteId = SITEID from dbo.UFN_SITE_MYSITE_GET(@CURRENTAPPUSERID) 
                        set @dSite = '<SITEID>'+ cast(@defaultSiteId as nvarchar(40)) +'</SITEID>';
                        if CAST(@SITES AS NVARCHAR(MAX)) NOT LIKE '%'+@dSite+'%'
                          raiserror('You did not assign your default site to the event campaign. To continue, edit sites and add your default site.', 13,1)
                 end
         end  

    exec dbo.USP_FAFPROGRAM_GETSITES_UPDATEFROMXML @ID, @SITES, @CHANGEAGENTID, @CURRENTDATE
    set @SITES = dbo.UFN_FAFPROGRAM_GETSITES_TOITEMLISTXML(@ID)

    update dbo.FAFPROGRAM set
            NAME = @NAME,
            DESCRIPTION = @DESCRIPTION,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE,
            PREVIOUSCAMPAIGNID = @PREVIOUSCAMPAIGNID
    where ID = @ID

    update dbo.EVENTEXTENSION 
    set FAFPROGRAMID = NULL, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
        where EVENTID in (select EVENTID from @REMOVEDEVENTLIST)

    update dbo.EVENTEXTENSION 
    set FAFPROGRAMID = @ID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
        where EVENTID in (select EVENTID from @ADDEDEVENTLIST)

    --remove local group from this campaign
    update dbo.TEAMEXTENSION 
    set NFGCAMPAIGNLEVELID = null,CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
    where EVENTID in (select EVENTID from @REMOVEDEVENTLIST)

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

return 0;