USP_FAFDEFAULTONLINECONFIRMATIONS

CREATE procedure dbo.USP_FAFDEFAULTONLINECONFIRMATIONS
(
  @EVENTID uniqueidentifier,
  @CLIENTSITESID int,
  @USERID int,
  @CLIENTSID int,
  @CHANGEAGENTID uniqueidentifier = null
)
as

declare @EMAILTEMPLATEID int
declare @ID uniqueidentifier

declare @name nvarchar(500)
declare @subject nvarchar(500)
declare @eventname nvarchar(200)
declare @index tinyint
declare @confirmationType tinyint
declare @confirmationTypeName nvarchar(100)
declare @content nvarchar(max)

declare @fromaddress nvarchar(500)
declare @fromname nvarchar(500),
        @appealid int

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

set @index = 0 

select @fromaddress = ED.EMAILADDRESS, @fromname = C.NAME, @eventname = LEFT(E.NAME,100), @appealid=BBA.ID --, @ClientSitesID = EX.CLIENTSITESID

from dbo.[EVENT] E
join dbo.[EVENTEXTENSION] EX ON E.ID = EX.EVENTID
left join CONSTITUENT C on E.EVENTLOCATIONCONTACTID = C.ID
left join EMAILADDRESS ED on E.EVENTLOCATIONCONTACTID = ED.CONSTITUENTID
left join dbo.BBNCAPPEALIDMAP BBA on BBA.APPEALID = E.APPEALID
where E.ID = @EVENTID

begin try

while @index < 4
  begin
    SELECT @confirmationType = CASE @index
        WHEN 0 THEN 101
        WHEN 1 THEN 102
        WHEN 2 THEN 103
        WHEN 3 THEN 104
        END

    SELECT @confirmationTypeName = CASE @confirmationType
        WHEN 101 THEN 'Registrant confirmation screen'
        WHEN 102 THEN 'Sponsor confirmation screen'
        WHEN 103 THEN 'Donor confirmation screen'
        WHEN 104 THEN 'Contact confirmation screen'
        END

    SELECT
        @EMAILTEMPLATEID = CASE WHEN  not exists(select * from EVENTEMAILTEMPLATE where EVENTID = @EVENTID AND CONFIRMATIONTYPECODE = @confirmationType) THEN 0 END
        ,@ID = CASE WHEN  not exists(select * from EVENTEMAILTEMPLATE where EVENTID = @EVENTID AND CONFIRMATIONTYPECODE = @confirmationType) THEN newid() END
        , @name =  @confirmationTypeName + ' - ' + @eventname
        , @subject = N''
        , @content = N''

      if @EMAILTEMPLATEID = 0
      begin
        exec spAddUpdate_EmailTemplate @PKID=@EMAILTEMPLATEID output, 
              @UserID = @USERID,
              @ClientsID = @CLIENTSID,
              @Name = @name,
              @Description = N'',
              @ContentHTML = @content,
              @RecipientListsXML = N'<root />',
              @ContentText = N'',
              @FromAddress = @fromaddress,
              @FromDisplayName = @fromname,
              @Subject = @subject,
              @Priority = 3,
              @ReplyAddress = @fromaddress,
              @ReturnReceipt = 0,
              @ReturnReceiptAddress = N'',
              @ForwardDSN = 0,
              @ForwardDSNAddress = N'',
              @GUID = @ID,
              @Type = 1,
              @DataSourceID = 0,
              @ClientSitesID = @CLIENTSITESID,
              @AppealID=@appealid;

        exec USP_EVENTEMAILTEMPLATE_ADD_EDIT 
                      @ID = @ID 
                    , @EVENTID=@EVENTID
                    , @CHANGEAGENTID=@CHANGEAGENTID
                    , @EMAILTEMPLATEID = @EMAILTEMPLATEID
                    , @CONFIRMATIONTYPECODE = @confirmationType

    end 

    set @index = @index + 1

  end

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

return 0