USP_FAF_DEFAULT_MARKETING_MESSAGES_COPY

      CREATE PROCEDURE dbo.USP_FAF_DEFAULT_MARKETING_MESSAGES_COPY
          (
            @EVENTID uniqueidentifier,
            @OLDEVENTID uniqueidentifier,
            @APPUSERID uniqueidentifier = null,
            @CHANGEAGENTID uniqueidentifier = null,
            @USERID int = null,
            @CLIENTSID int = null
          )
          as

          SET NOCOUNT ON;

          DECLARE @EVENTNAME varchar(200)
          DECLARE @TEMPLATENAME varchar(255)
        --  DECLARE @UserID int

        --  DECLARE @ClientsID int

          DECLARE @DATEADDED datetime
          DECLARE @EMAILTEMPLATEID int
          DECLARE @FromDisplayName nvarchar(255)
          DECLARE @FromAddress nvarchar(255)
          DECLARE @ClientSitesID int

          DECLARE @EMAILID int
          DECLARE @EMAILNAME nvarchar(255)
          DECLARE @EMAILGUID uniqueidentifier
          DECLARE @EVENTSTARTDATE datetime
          DECLARE @EVENTENDDATE datetime
          DECLARE @RecipientListsDoc nvarchar(4000)
          DECLARE @EMAILLISTID int
          DECLARE @EMAILLISTNAME varchar(255)
          DECLARE @NAMETAG varchar(55)
          DECLARE @SCHEDULEDATE datetime
          DECLARE @EmailDescription nvarchar(255)
          DECLARE @EmailSubject nvarchar(255)

          DECLARE @PrivacyPageID int
          DECLARE @EmailPreferencePageID int

          DECLARE @PrivacyPage nvarchar(200)
          DECLARE @EmailPreferencePage nvarchar(200)
          DECLARE @EmailLink nvarchar(1000)
          DECLARE @EmailPreferencePageName nvarchar(150)

          DECLARE @OLDSCHEDULEDATE datetime
          DECLARE @OLDEVENTSTARTDATE datetime
          DECLARE @OLDEMAILID int
          DECLARE @DAYS int
          DECLARE @OLDEMAILLISTID int
          DECLARE @OLDEVENTNAME varchar(200)
          DECLARE @OLDClientSitesID int
          DECLARE @OLDEMAILLISTNAME varchar(255)
          DECLARE @TYPE tinyint

          DECLARE @PREVIOUSEVENTID uniqueidentifier
          DECLARE @PREVIOUSEVENTNAME varchar(200)

          DECLARE @OLDPREVIOUSEVENTID uniqueidentifier
          DECLARE @OLDPREVIOUSEVENTNAME varchar(200)

          DECLARE @appealid int
          DECLARE @TOTAL int
          DECLARE @i int
          DECLARE @j int

          DECLARE @ContentHTML nvarchar(max) = ''

          SET @DATEADDED = GETDATE()

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

          DECLARE @EMAILMESSAGES table (ID int identity(1,1), EmailID int, EmailLIstID int, [TYPE] tinyint)

          INSERT INTO @EMAILMESSAGES (EmailID, EmailLIstID,[TYPE])
          SELECT emailid, Emaillistid,[TYPE] from dbo.FAFEVENTEMAIL WHERE EVENTID = @OLDEVENTID

          SELECT @TOTAL = COUNT(*) from @EMAILMESSAGES

          SELECT @EMAILTEMPLATEID = ET.ID, @FromDisplayName = ET.FromDisplayName, @FromAddress = ET.FromAddress
          FROM dbo.EmailTemplate ET (NOLOCK)
          INNER JOIN dbo.EVENTEMAILTEMPLATE EET (NOLOCK)
          ON ET.ID = EET.EMAILTEMPLATEID
          AND EET.CONFIRMATIONTYPECODE = 33
          WHERE EET.EVENTID = @EVENTID

          SELECT @EVENTNAME = E.NAME, @ClientSitesID = CLIENTSITESID, @EVENTSTARTDATE = E.STARTDATE, @EVENTENDDATE = E.ENDDATE, @appealid=BBA.ID
          FROM dbo.EVENT E (NOLOCK) 
          INNER JOIN dbo.EVENTEXTENSION EE
          ON E.ID = EE.EVENTID
          LEFT JOIN dbo.BBNCAPPEALIDMAP BBA ON BBA.APPEALID = E.APPEALID
          WHERE E.ID = @EVENTID;

          SELECT @OLDEVENTNAME = E.NAME, @OLDClientSitesID = CLIENTSITESID, @OLDEVENTSTARTDATE = E.STARTDATE
          FROM dbo.EVENT E (NOLOCK) 
          INNER JOIN dbo.EVENTEXTENSION EE
          ON E.ID = EE.EVENTID
          WHERE E.ID = @OLDEVENTID;

          SELECT @OLDPREVIOUSEVENTNAME = PE.NAME, @OLDPREVIOUSEVENTID = PE.ID
          FROM dbo.EVENTEXTENSION EE (NOLOCK)
          INNER JOIN dbo.EVENT PE (NOLOCK)
          ON EE.PRIORYEAREVENTID = PE.ID
          WHERE EE.EVENTID = @OLDEVENTID;

          SELECT @PREVIOUSEVENTNAME = PE.NAME, @PREVIOUSEVENTID = PE.ID
          FROM dbo.EVENTEXTENSION EE (NOLOCK)
          INNER JOIN dbo.EVENT PE (NOLOCK)
          ON EE.PRIORYEAREVENTID = PE.ID
          WHERE EE.EVENTID = @EVENTID;

          -- new


            SELECT top 1 @EmailPreferencePageID = sp.ID, @EmailPreferencePageName = Sp.pagename
                from SitePages sp
                inner join PageContent pc on pc.SitePagesID = sp.ID
                inner join SiteContent sc on pc.SiteContentID = sc.ID and sc.ContentTypesID = 121
                inner join APEXFORMCONTENT afc on afc.ID = sc.Guid
                inner join APEXFORM af on af.ID = afc.FORMID and af.FORMTYPEID = '89D3C4CC-4D01-4E8B-B82E-04AD966EEE09'
                WHERE SP.ClientSitesID = @OLDClientSitesID;

        -- old


            IF @EmailPreferencePageID IS NULL
            SELECT top 1 @EmailPreferencePageID = VPC.PageID, @EmailPreferencePageName = VPC.pagename
                from vwPageContent VPC
                inner join dbo.SitePages SP on SP.ID = VPC.PageID
                WHERE SP.ClientSitesID = @OLDClientSitesID
                and VPC.IsTemplatePage = 0
                and VPC.ContentTypesID = 50;

          select @PrivacyPageID = id
                      from sitepages where id=(select CAST(VALUE as int) from CMSSITESETTING where CLIENTSITESID=@OLDClientSitesID AND ENUMID=5)

            SET @PrivacyPage = '<P style="margin-bottom:16px;"><a href="target=&amp;pid='+CAST(@PrivacyPageID as nvarchar(10))+'&amp;did=0&amp;tab=0">Privacy Policy Page (Privacy Page)</a></p>'

          SET @EmailPreferencePage = '<P style="margin-bottom:16px;"><a href="target=&amp;pid='+CAST(@EmailPreferencePageID as nvarchar(10))+'&amp;did=0&amp;tab=0">'+@EmailPreferencePageName+' (Email Preferences Page)</a></p>'

          SET @i = 1

          BEGIN TRAN

          begin try    

          WHILE (@i <= @TOTAL)
          BEGIN

              SET @EMAILID = 0;
              SET @EMAILLISTID = null;
              SET @OLDEMAILLISTID = null;
              SET @OLDEMAILID = null;
              SET @ContentHTML = ''

              SELECT @OLDEMAILID = EmailID, @OLDEMAILLISTID = EmailLIstID, @TYPE = [Type]
              from @EMAILMESSAGES WHERE ID = @i

              SELECT @EMAILNAME = Name, @ContentHTML = ContentHTML, @FromAddress = FromAddress, @FromDisplayName = FromDisplayName, @EmailSubject = [Subject], 
                      @OLDSCHEDULEDATE = SendAfterDate, @EmailDescription = [Description]
              FROM dbo.Email (NOLOCK) 
              WHERE ID = @OLDEMAILID

              SELECT @OLDEMAILLISTNAME = Name
              from dbo.EmailList WHERE ID = @OLDEMAILLISTID 


              SET @DAYS = datediff(day,@OLDEVENTSTARTDATE,@OLDSCHEDULEDATE);
              SET @SCHEDULEDATE = dateadd(day,@DAYS,@EVENTSTARTDATE);

          IF ISNULL(@PrivacyPage,'') > ''
              SET @ContentHTML = REPLACE(@ContentHTML,@PrivacyPage,'');

          IF ISNULL(@EmailPreferencePage,'') > ''
              SET @ContentHTML = REPLACE(@ContentHTML,@EmailPreferencePage,'');

              SET @EmailDescription = REPLACE(@EmailDescription,@OLDEVENTNAME,@EVENTNAME);

              SET @OLDEMAILLISTNAME = REPLACE(@OLDEMAILLISTNAME,@OLDEVENTNAME,@EVENTNAME);

              SELECT @EMAILLISTID = ID
              FROM dbo.EmailList
              WHERE Name = @OLDEMAILLISTNAME and ClientsID = @ClientsID 

              IF @EMAILLISTID IS NULL
              SET @RecipientListsDoc = '';

              IF ISNULL(@EMAILLISTID,-1) > 1
              BEGIN
              SET @RecipientListsDoc = '<root><RecipientLists EmailListID="'+CAST(@EMAILLISTID as nvarchar(25))+'" /></root>'

              IF @EMAILLISTID = @OLDEMAILLISTID
              SET @RecipientListsDoc = '';
              END

          IF (@PREVIOUSEVENTID IS NULL) AND (@TYPE IN (16,17,18,19,20,21))  -- if there is no previous event then we don't want to create previous event messages

          SET @ContentHTML = ''              

              IF @SCHEDULEDATE > GETDATE() AND ISNULL(@ContentHTML,'') > ''
              BEGIN

                  EXEC dbo.spAddUpdate_Email @PKID=@EMAILID output,@EmailTemplateID=@EMAILTEMPLATEID,@Name=@EMAILNAME,@Description=@EmailDescription,@UserID=@UserID,@Guid=@EMAILGUID ,@type=0,@status=0,
                      @ContentHTML=@ContentHTML,@ContentText=N'',@FromAddress=@FromAddress,@FromDisplayName=@FromDisplayName,@Subject=@EmailSubject,@Priority=3,@ReplyAddress=N'',
                      @ReturnReceipt=0,@ReturnReceiptAddress=N'',@ForwardDSN=0,@ForwardDSNAddress=N'',@SendAfterDate=@SCHEDULEDATE,
                      @RecipientListsDoc=@RecipientListsDoc,@RecipientsXML=N'<?xml version="1.0" encoding="utf-16"?><ArrayOfEmailRecipient xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />',
                      @ProjectAppealListEmailID=-1,@CLIENTSITESID=@ClientSitesID,@AppealID=@appealid;


                  INSERT INTO dbo.FAFEVENTEMAIL(ID,EVENTID,EMAILID,EMAILLISTID,TYPE,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    VALUES(NEWID(),@EVENTID,@EMAILID,@EMAILLISTID,@TYPE,@CHANGEAGENTID, @CHANGEAGENTID, @DATEADDED, @DATEADDED)

              END

          SET @i = @i+1
          END

          IF @OLDPREVIOUSEVENTID IS NULL AND @PREVIOUSEVENTID IS NOT NULL
              BEGIN

                  EXEC dbo.USP_FAF_DEFAULT_MARKETING_MESSAGES_ADD @EVENTID = @EVENTID, @APPUSERID = @APPUSERID, @CHANGEAGENTID = @CHANGEAGENTID, @TYPE = 16, @USERID = @USERID, @CLIENTSID = @CLIENTSID

            EXEC dbo.USP_FAF_DEFAULT_MARKETING_MESSAGES_ADD @EVENTID = @EVENTID, @APPUSERID = @APPUSERID, @CHANGEAGENTID = @CHANGEAGENTID, @TYPE = 17, @USERID = @USERID, @CLIENTSID = @CLIENTSID

            EXEC dbo.USP_FAF_DEFAULT_MARKETING_MESSAGES_ADD @EVENTID = @EVENTID, @APPUSERID = @APPUSERID, @CHANGEAGENTID = @CHANGEAGENTID, @TYPE = 18, @USERID = @USERID, @CLIENTSID = @CLIENTSID

            EXEC dbo.USP_FAF_DEFAULT_MARKETING_MESSAGES_ADD @EVENTID = @EVENTID, @APPUSERID = @APPUSERID, @CHANGEAGENTID = @CHANGEAGENTID, @TYPE = 19, @USERID = @USERID, @CLIENTSID = @CLIENTSID

            EXEC dbo.USP_FAF_DEFAULT_MARKETING_MESSAGES_ADD @EVENTID = @EVENTID, @APPUSERID = @APPUSERID, @CHANGEAGENTID = @CHANGEAGENTID, @TYPE = 20, @USERID = @USERID, @CLIENTSID = @CLIENTSID

            EXEC dbo.USP_FAF_DEFAULT_MARKETING_MESSAGES_ADD @EVENTID = @EVENTID, @APPUSERID = @APPUSERID, @CHANGEAGENTID = @CHANGEAGENTID, @TYPE = 21, @USERID = @USERID, @CLIENTSID = @CLIENTSID

              END

          SET @j = 1;
          WHILE (@j <= 21)
          BEGIN

            IF NOT EXISTS(SELECT 1 FROM dbo.FAFEVENTEMAIL (NOLOCK) WHERE EVENTID = @EVENTID AND [TYPE] = @j)
            BEGIN
              IF @j <=15
              EXEC dbo.USP_FAF_DEFAULT_MARKETING_MESSAGES_ADD @EVENTID = @EVENTID, @APPUSERID = @APPUSERID, @CHANGEAGENTID = @CHANGEAGENTID, @TYPE = @j, @USERID = @USERID, @CLIENTSID = @CLIENTSID

              IF (@j IN (16,17,18,19,20,21)) AND (@PREVIOUSEVENTID IS NOT NULL) 
              EXEC dbo.USP_FAF_DEFAULT_MARKETING_MESSAGES_ADD @EVENTID = @EVENTID, @APPUSERID = @APPUSERID, @CHANGEAGENTID = @CHANGEAGENTID, @TYPE = @j, @USERID = @USERID, @CLIENTSID = @CLIENTSID
            END

            SET @j = @j + 1

          END

          end try
          begin catch
            exec dbo.USP_RAISE_ERROR;
            ROLLBACK TRAN
            return 1;
          end catch

          COMMIT TRAN
          return 0;