USP_GLOBALCHANGE_ADDORCHANGECOMMUNICATIONTEAMPLTE

create procedure dbo.USP_GLOBALCHANGE_ADDORCHANGECOMMUNICATIONTEAMPLTE
(
    @CHOOSEEDEVENT uniqueidentifier,
    @SOURCETEMPLATE xml = null,
    @SELECTEDEVENTSFORUPDATE xml = null,
    @SELECTEDTEMPLATESFORUPDATE xml = null,
    @SELECTIONOPTIONFORUPDATE smallint,
    @CHANGEAGENTID uniqueidentifier = null,
    @ASOF as datetime = null, 
    @NUMBERADDED int = 0 output,
    @NUMBEREDITED int = 0 output,
    @NUMBERDELETED int = 0 output,
    @CURRENTAPPUSERID uniqueidentifier = null
)
as            
    set nocount off;

    declare @CURRENTDATE datetime
    declare @UserID int
    declare @ISERROR int,
            @ERRORTEXT nvarchar(max)

    declare @SourceContentHtml nvarchar(max), 
            @SourceSubject nvarchar(max), 
            @SourceConfirmationTypeCode int = 0,
            @SourceDescription nvarchar(max),
      @SourceFAFTASKID uniqueidentifier = null,
      @SourceISDEFAULTFORTASK bit = 0

    declare @ISAVAILABLETOINDIVIDUAL bit 
    declare @ISAVAILABLETOHOUSEHOLD bit
    declare @ISAVAILABLETOHOUSEHOLDMEMBER bit
    declare @ISAVAILABLETOTEAMMEMBER bit
    declare @ISAVAILABLETOTEAMLEADER bit 
    declare @ISAVAILABLETOCOMPANYLEADER bit 
    declare @ISAVAILABLETOFUNDRAISINGCOACH bit 
    declare @ISAVAILABLETOSPONSOR bit 
    declare @ISACTIVE bit      
    declare @RECIPIENTDONOR bit
    declare @RECIPIENTPROSPECT bit  
    declare @RECIPIENTINDIVIDUAL bit   
    declare @RECIPIENTHOUSEHOLDMEMBER bit  
    declare @RECIPIENTHOUSEHOLDLEADER bit  
    declare @RECIPIENTTEAMMEMBER bit
    declare @RECIPIENTTEAMLEADER bit 
    declare @RECIPIENTCOMPANYLEADER bit  
    declare @RECIPIENTSTATUSPREVIOUS bit 
    declare @RECIPIENTSTATUSCURRENT bit 
    declare @NCNOTIFICATIONID int 
    declare @AVAILABLEASLETTER bit  

    declare @Count int, @Index int
    declare @EventID uniqueidentifier, 
            @AppealID int,
            @ClientSiteID int,
            @ClientsID int,
            @EmailTemplateID int = 0,
            @EmailTemplateGuID uniqueidentifier,
            @EventEmailTemplateID uniqueidentifier,
            @EmailTemplateName nvarchar(200),
      @EventName nvarchar(max),
      @SourceTemplateID int

    declare @EventsThatAlreadyHaveOptionsWithThisName table(ID int identity(1,1), EventID uniqueidentifier, EventName nvarchar(max))
    declare @EventInfo table(
                      ID int identity(1,1), 
                      EventID uniqueidentifier,
                      EventName nvarchar(max),
                      AppealID int,
                      ClientSiteID int,
                      ClientsID int
          )

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

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

    select @UserID = [ClientUsers].ID from BBNCUSERMAP 
      join dbo.[ClientUsers] on [ClientUsers].[UserName] = BBNCUSERMAP.BBNCUSERNAME
      where BBNCUSERMAP.ID = @CURRENTAPPUSERID

  if @UserID is null
    RAISERROR('The owner of this global change spec is not mapped to a Netcommunity user, or is mapped to a NetCommunity user that has subsequently been deleted.', 11, 1)

  SET @SourceTemplateID = (SELECT T.c.value('(TEMPLATEID)[1]','int') FROM @SOURCETEMPLATE.nodes('/SOURCETEMPLATE/ITEM') T(c))

    if @SourceTemplateID is not null
      begin
             SELECT 
                @SourceContentHtml = CONTENTHTML, 
                @SourceConfirmationTypeCode = EET.CONFIRMATIONTYPECODE, 
                @SourceSubject=ET.SUBJECT,
                @SourceDescription = ET.Description,
                @ISAVAILABLETOINDIVIDUAL = EET.ISAVAILABLETOINDIVIDUAL,
                @ISAVAILABLETOHOUSEHOLD = EET.ISAVAILABLETOHOUSEHOLD,
                @ISAVAILABLETOHOUSEHOLDMEMBER = EET.ISAVAILABLETOHOUSEHOLDMEMBER,
                @ISAVAILABLETOTEAMMEMBER = EET.ISAVAILABLETOTEAMMEMBER,
                @ISAVAILABLETOTEAMLEADER = EET.ISAVAILABLETOTEAMLEADER,  
                @ISAVAILABLETOCOMPANYLEADER = EET.ISAVAILABLETOCOMPANYLEADER,
                @ISAVAILABLETOFUNDRAISINGCOACH = EET.ISAVAILABLETOFUNDRAISINGCOACH, 
                @ISAVAILABLETOSPONSOR = EET.ISAVAILABLETOSPONSOR,
                @ISACTIVE = EET.ISACTIVE,
                @RECIPIENTDONOR = EET.RECIPIENTDONOR,
                @RECIPIENTPROSPECT  = EET.RECIPIENTPROSPECT,
                @RECIPIENTINDIVIDUAL = EET.RECIPIENTINDIVIDUAL,
                @RECIPIENTHOUSEHOLDMEMBER  = EET.RECIPIENTHOUSEHOLDMEMBER,
                @RECIPIENTHOUSEHOLDLEADER  = EET.RECIPIENTHOUSEHOLDLEADER,
                @RECIPIENTTEAMMEMBER = EET.RECIPIENTTEAMMEMBER,
                @RECIPIENTTEAMLEADER = EET.RECIPIENTTEAMLEADER,
                @RECIPIENTCOMPANYLEADER  = EET.RECIPIENTCOMPANYLEADER,
                @RECIPIENTSTATUSPREVIOUS = EET.RECIPIENTSTATUSPREVIOUS,
                @RECIPIENTSTATUSCURRENT = EET.RECIPIENTSTATUSCURRENT,
                @NCNOTIFICATIONID = EET.NCNOTIFICATIONID,
                @AVAILABLEASLETTER  = EET.AVAILABLEASLETTER,
        @SourceFAFTASKID = EET.FAFTASKID,
        @SourceISDEFAULTFORTASK = EET.ISDEFAULTFORTASK

            FROM dbo.EmailTemplate ET
            INNER JOIN dbo.EVENTEMAILTEMPLATE EET ON ET.ID = EET.EMAILTEMPLATEID
            WHERE ET.ID = @SourceTemplateID
      end
    else
    return;

    begin try
    --select "Add template to specific events"
    if @SELECTIONOPTIONFORUPDATE = 1
        begin
          insert into @EventInfo(EventID)
          SELECT EVENTID FROM (SELECT T.c.value('(EVENTID)[1]','uniqueidentifier') As 'EVENTID'
          FROM @SELECTEDEVENTSFORUPDATE.nodes('/SELECTEDEVENTSFORUPDATE/ITEM') T(c)) AS 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)) )
          )      

      --if source template is the default for its task, clear the default settings for that task in the target event's templates
      if @SourceISDEFAULTFORTASK=1
        UPDATE EVENTEMAILTEMPLATE
        SET ISDEFAULTFORTASK=0 WHERE FAFTASKID=@SourceFAFTASKID AND EVENTID in (select EventID from @EventInfo)

          update @EventInfo 
          set AppealID = BBA.ID,
              ClientSiteID = EX.CLIENTSITESID,
              ClientsID = CS.ClientsID,
        EventName = E.Name
          from @EventInfo EI
            join Event E on e.ID = EI.EventID 
            join dbo.EVENTEXTENSION EX on E.ID = EX.EVENTID
            left join dbo.BBNCAPPEALIDMAP BBA on E.APPEALID = BBA.APPEALID
            left join dbo.ClientSites CS on cs.ID = EX.CLIENTSITESID  

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

         while @Index< = @Count
             begin
                select @EventID = EventID, 
                @AppealID = AppealID,
                @ClientSiteID  = ClientSiteID,
                @ClientsID = ClientsID ,
                @EmailTemplateGuID = newid(),
                @EmailTemplateID = 0,
        @EventName = EventName
                from @EventInfo  where ID = @Index 

        Set @EmailTemplateName = @EventName + ': participant communication template - ' + CAST(newid() AS nvarchar(max))

                exec spAddUpdate_EmailTemplate @PKID=@EmailTemplateID output, 
                                              @UserID    = @USERID,
                                              @ClientsID = @ClientsID,
                                              @Name = @EmailTemplateName,
                                              @Description = @SourceDescription,
                                              @ContentHTML = @SourceContentHtml,
                                              @RecipientListsXML = N'<root />',
                                              @ContentText = N'',
                                              @FromAddress = N'',
                                              @FromDisplayName = N'',
                                              @Subject = @SourceSubject,
                                              @Priority = 3,
                                              @ReplyAddress =  N'' ,
                                              @ReturnReceipt = 0,
                                              @ReturnReceiptAddress = N'',
                                              @ForwardDSN = 0,
                                              @ForwardDSNAddress = N'',
                                              @GUID = @EmailTemplateGuID,
                                              @Type = 0,
                                              @DataSourceID = 0,
                                              @ClientSitesID = @CLIENTSITEID,
                                              @AppealID=@AppealID;

                if @EmailTemplateID is not null 
                    begin
                        exec USP_EVENTEMAILTEMPLATE_ADD_EDIT @CHANGEAGENTID=@CHANGEAGENTID, @EVENTID=@EVENTID, @EMAILTEMPLATEID = @EmailTemplateID, @CONFIRMATIONTYPECODE = @SourceConfirmationTypeCode, 
                        @ISAVAILABLETOINDIVIDUAL = @ISAVAILABLETOINDIVIDUAL, @ISAVAILABLETOHOUSEHOLD = @ISAVAILABLETOHOUSEHOLD, @ISAVAILABLETOTEAMMEMBER = @ISAVAILABLETOTEAMMEMBER,
                        @ISAVAILABLETOTEAMLEADER =  @ISAVAILABLETOTEAMLEADER, @ISAVAILABLETOCOMPANYLEADER = @ISAVAILABLETOCOMPANYLEADER, 
                        @ISAVAILABLETOFUNDRAISINGCOACH = @ISAVAILABLETOFUNDRAISINGCOACH, @ISAVAILABLETOSPONSOR = @ISAVAILABLETOSPONSOR,
                        @RECIPIENTDONOR =  @RECIPIENTDONOR, @RECIPIENTPROSPECT = @RECIPIENTPROSPECT, @RECIPIENTINDIVIDUAL = @RECIPIENTINDIVIDUAL,
                        @RECIPIENTHOUSEHOLDMEMBER = @RECIPIENTHOUSEHOLDMEMBER, @RECIPIENTHOUSEHOLDLEADER = @RECIPIENTHOUSEHOLDLEADER, @RECIPIENTTEAMMEMBER = @RECIPIENTTEAMMEMBER, 
                        @RECIPIENTTEAMLEADER = @RECIPIENTTEAMLEADER, @RECIPIENTCOMPANYLEADER = @RECIPIENTCOMPANYLEADER, @RECIPIENTSTATUSPREVIOUS = @RECIPIENTSTATUSPREVIOUS,    
                        @RECIPIENTSTATUSCURRENT = @RECIPIENTSTATUSCURRENT, @AVAILABLEASLETTER = @AVAILABLEASLETTER, @ISAVAILABLETOHOUSEHOLDMEMBER = @ISAVAILABLETOHOUSEHOLDMEMBER,
            @FAFTASKID = @SourceFAFTASKID, @ISDEFAULTFORTASK = @SourceISDEFAULTFORTASK

                        set @NUMBERADDED = @NUMBERADDED + 1
                    end
              set @Index = @Index + 1
             end
    end

    --select "Replace existing templates"    
    if @SELECTIONOPTIONFORUPDATE = 2
        begin
              DECLARE @UpdatedTable table (ID int identity(1,1), EmailTemplateGUID uniqueidentifier, EventEmailTemplateID uniqueidentifier, EventID uniqueidentifier)

              insert into @UpdatedTable(EmailTemplateGUID)
              SELECT EmailTemplateGUID FROM (SELECT T.c.value('(RECORDID)[1]','uniqueidentifier') AS 'EMAILTEMPLATEGUID'
                FROM @SELECTEDTEMPLATESFORUPDATE.nodes('/SELECTEDTEMPLATESFORUPDATE/ITEM') T(c)) AS XMLTABLE
        INNER JOIN EMAILTEMPLATE ET ON XMLTABLE.EMAILTEMPLATEGUID = ET.GUID
        INNER JOIN EVENTEMAILTEMPLATE ETT on ET.ID = ETT.EMAILTEMPLATEID
        WHERE exists(
            select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(ETT.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 E.ID, E.Name 
        from dbo.EVENTEMAILTEMPLATE EET
        join dbo.Event E on E.ID = EET.EVENTID 
        join dbo.EmailTemplate ET on ET.ID = EET.EMAILTEMPLATEID 
        where ET.Guid in (select EmailTemplateGUID from @UpdatedTable)

        select @ISERROR = COUNT(EventID) from @EventInfo Group By EventID HAVING (Count(EventID) > 1 )
        if @ISERROR > 0
          begin
                    SELECT @ERRORTEXT = COALESCE(@ERRORTEXT+', ' ,'') + EventName from @EventInfo Group By EventName HAVING (Count(EventName) > 1 )
                    RAISERROR ('Your updates could not be processed because participant communication template names must be unique for each event. You selected to update more than one participant communication template for the following events: %s. To continue, please change your selections to ensure that only one participant communication template per event is selected.', 11, 1, @ERRORTEXT);
          end

              update @UpdatedTable
                set EventEmailTemplateID = EET.ID,
                    EventID = EET.EVENTID
              from @UpdatedTable T
              inner join EmailTemplate ET on ET.Guid = T.EmailTemplateGUID and ET.ID <> @SourceTemplateID--It is not itself.
              inner join EVENTEMAILTEMPLATE EET on EET.EMAILTEMPLATEID = ET.ID and EET.CONFIRMATIONTYPECODE = @SourceConfirmationTypeCode

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

              while @Index< = @Count
                begin
                    select @EventEmailTemplateID = EventEmailTemplateID,@EventID = EventID 
                    from @UpdatedTable  
                    where ID = @Index     

                    if @EventEmailTemplateID is not null 
                        begin
                            update EMAILTEMPLATE
                               SET SUBJECT=@SourceSubject, 
                                CONTENTHTML=@SourceContentHtml
                            from EMAILTEMPLATE ET
                            inner join @UpdatedTable T on T.EmailTemplateGUID = ET.GUID and T.ID = @Index

                            exec USP_EVENTEMAILTEMPLATE_ADD_EDIT @ID = @EventEmailTemplateID, @CHANGEAGENTID=@CHANGEAGENTID, @CONFIRMATIONTYPECODE = @SourceConfirmationTypeCode, 
                                    @ISAVAILABLETOINDIVIDUAL = @ISAVAILABLETOINDIVIDUAL, @ISAVAILABLETOHOUSEHOLD = @ISAVAILABLETOHOUSEHOLD, @ISAVAILABLETOTEAMMEMBER = @ISAVAILABLETOTEAMMEMBER,
                                    @ISAVAILABLETOTEAMLEADER =  @ISAVAILABLETOTEAMLEADER, @ISAVAILABLETOCOMPANYLEADER = @ISAVAILABLETOCOMPANYLEADER, 
                                    @ISAVAILABLETOFUNDRAISINGCOACH = @ISAVAILABLETOFUNDRAISINGCOACH, @ISAVAILABLETOSPONSOR = @ISAVAILABLETOSPONSOR,
                                    @RECIPIENTDONOR =  @RECIPIENTDONOR, @RECIPIENTPROSPECT = @RECIPIENTPROSPECT, @RECIPIENTINDIVIDUAL = @RECIPIENTINDIVIDUAL,
                                    @RECIPIENTHOUSEHOLDMEMBER = @RECIPIENTHOUSEHOLDMEMBER, @RECIPIENTHOUSEHOLDLEADER = @RECIPIENTHOUSEHOLDLEADER, @RECIPIENTTEAMMEMBER = @RECIPIENTTEAMMEMBER, 
                                    @RECIPIENTTEAMLEADER = @RECIPIENTTEAMLEADER, @RECIPIENTCOMPANYLEADER = @RECIPIENTCOMPANYLEADER, @RECIPIENTSTATUSPREVIOUS = @RECIPIENTSTATUSPREVIOUS,    
                                    @RECIPIENTSTATUSCURRENT = @RECIPIENTSTATUSCURRENT, @AVAILABLEASLETTER = @AVAILABLEASLETTER, @ISAVAILABLETOHOUSEHOLDMEMBER = @ISAVAILABLETOHOUSEHOLDMEMBER,
                  @FAFTASKID = @SourceFAFTASKID, @ISDEFAULTFORTASK = @SourceISDEFAULTFORTASK

                            set @NUMBEREDITED = @NUMBEREDITED + 1
                    end
                    set @Index = @Index + 1
                end
        end    
    end try
    begin catch
      exec dbo.USP_RAISE_ERROR;
      return 1; 
      end catch