USP_FAFDEFAULTACKNOWLEDGEMENTS

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

declare @EMAILTEMPLATEID int
  --@ClientSitesID 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 < 7
  begin
    SELECT @confirmationType = CASE @index
        WHEN 0 THEN 1
        WHEN 1 THEN 2
        WHEN 2 THEN 3
        WHEN 3 THEN 6
        WHEN 4 THEN 97
        WHEN 5 THEN 98
        WHEN 6 THEN 99
        END

    SELECT @confirmationTypeName = CASE @confirmationType
        WHEN 1 THEN 'Registration'
        WHEN 2 THEN 'Sponsorship'
        WHEN 3 THEN 'Donation'
        WHEN 6 THEN 'ContactUs'
        WHEN 97 THEN 'Request Received'
        WHEN 98 THEN 'Request Approved'
        WHEN 99 THEN 'Request Declined'
        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 = CASE @confirmationType 
              WHEN 1 THEN 'Thank you for your registration'
              WHEN 2 THEN 'Thank you for your sponsorship'
              WHEN 3 THEN 'Thank you for your donation'
              WHEN 6 THEN 'Thank you for contacting us'
              WHEN 97 THEN 'Thank you for requesting to start a fundraising group'
              WHEN 98 THEN 'Your request is approved'
              WHEN 99 THEN 'Your request is declined'
        END
        , @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