USP_FAFDEFAULTNOTIFICATIONS

CREATE procedure dbo.USP_FAFDEFAULTNOTIFICATIONS
(
  @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 int

declare @fromaddress nvarchar(500)
declare @fromname nvarchar(500)

declare 
  @ISAVAILABLETOINDIVIDUAL AS bit
, @ISAVAILABLETOHOUSEHOLD AS bit
, @ISAVAILABLETOHOUSEHOLDMEMBER AS bit
, @ISAVAILABLETOTEAMMEMBER AS bit
, @ISAVAILABLETOTEAMLEADER AS bit
, @ISAVAILABLETOCOMPANYLEADER AS bit
, @ISAVAILABLETOFUNDRAISINGCOACH AS bit
, @ISAVAILABLETOSPONSOR AS bit;

declare @NCNotificationTypeID int
declare @NCNotificationID int,
        @appealid int

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

set @index = 22 

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

select @NCNotificationTypeID = ID from dbo.NotificationType where SystemName = 'FAF Notification'

begin try

---make sure to take out 33

while @index <= 35 
  begin     

        SELECT
                @EMAILTEMPLATEID = CASE WHEN  not exists(select * from EVENTEMAILTEMPLATE where EVENTID = @EVENTID AND CONFIRMATIONTYPECODE = @index) THEN 0 END
               ,@ID = CASE WHEN  not exists(select * from EVENTEMAILTEMPLATE where EVENTID = @EVENTID AND CONFIRMATIONTYPECODE = @index) THEN newid() END

              , @name = CASE @index 
                          --WHEN 20 THEN  'Weekly summary - ' + @eventname

                          --WHEN 21 THEN 'Monthly summary - ' + @eventname

                          WHEN 22 THEN 'Donation received ? individual participant - ' + @eventname
                          WHEN 23 THEN 'Donation received - fundraising group - ' + @eventname
                          WHEN 24 THEN 'Donation received - fundraising group member - ' + @eventname
                          WHEN 25 THEN 'New fundraising group member - ' + @eventname
                          WHEN 26 THEN 'New fundraising group - ' + @eventname
                          WHEN 27 THEN 'Comment received - individual' + @eventname
                          WHEN 28 THEN 'Comment received - fundraising group - ' + @eventname
                          --WHEN 29 THEN 'Incentive level achievement - ' + @eventname

                          --WHEN 30 THEN '30 day login reminder - ' + @eventname

                          WHEN 31 THEN 'Like added to comment - individual' + @eventname
                          WHEN 32 THEN 'Like added to comment - fundraising group - ' + @eventname
                          WHEN 34 THEN 'Donation transfer received ? individual participant - ' + @eventname
                          WHEN 35 THEN 'Promotion received - ' + @eventname
                  END
              , @subject = CASE @index 
                          --WHEN 20 THEN 'Weekly summary'

                          --WHEN 21 THEN 'Monthly summary'

                          WHEN 22 THEN 'You just received a donation'
                          WHEN 23 THEN 'Your fundraising group just received a donation'
                          WHEN 24 THEN 'A fundraising group member just received a donation'
                          WHEN 25 THEN 'A new fundraising group member just joined your group'
                          WHEN 26 THEN 'A new fundraising group just started'
                          WHEN 27 THEN 'Someone just made a comment on your personal fundraising page'
                          WHEN 28 THEN 'Someone just made a comment on your group fundraising page'
                          --WHEN 29 THEN 'Incentive level achievement'

                          --WHEN 30 THEN '30 day login reminder'

                          WHEN 31 THEN 'Someone liked a comment made on your personal fundraising page'
                          WHEN 32 THEN 'Someone liked a comment made on your group fundraising page'
                          WHEN 34 THEN 'A donation was just transferred to you from another participant'
                          WHEN 35 THEN 'You have been promoted'
                  END
              , @ISAVAILABLETOINDIVIDUAL          = CASE WHEN @index IN ( 20,21,22,27,30,29,31,34) THEN 1 ELSE 0 END
              , @ISAVAILABLETOHOUSEHOLD           = CASE WHEN @index IN ( 20,21,22,27,30,31,34,35) THEN 1 ELSE 0 END
              , @ISAVAILABLETOHOUSEHOLDMEMBER     = CASE WHEN @index IN ( 20,21,22,27,30,31,34) THEN 1 ELSE 0 END
              , @ISAVAILABLETOTEAMMEMBER          = CASE WHEN @index IN ( 20,21,22,27,30,31,34) THEN 1 ELSE 0 END
              , @ISAVAILABLETOTEAMLEADER          = CASE WHEN @index IN ( 20,21,22,23,24,25,27,28,30,31,32,34,35) THEN 1 ELSE 0 END
              , @ISAVAILABLETOCOMPANYLEADER       = CASE WHEN @index IN ( 20,21,22,23,24,25,26,27,28,30,31,32,34,35) THEN 1 ELSE 0 END
              , @ISAVAILABLETOFUNDRAISINGCOACH    = CASE WHEN @index IN ( 20,21,22,23,24,25,26,30) THEN 1 ELSE 0 END
              , @ISAVAILABLETOSPONSOR             = CASE WHEN @index IN ( 20,21,22,30) THEN 1 ELSE 0 END    

      if @EMAILTEMPLATEID = 0 and @index not in (29,30,33)
      begin
        exec spAddUpdate_EmailTemplate @PKID=@EMAILTEMPLATEID output, 
              @UserID = @USERID,
              @ClientsID = @CLIENTSID,
              @Name = @name,
              @Description = N'',
              @ContentHTML = N'',
              @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 = 13,
              @DataSourceID = 0,
              @ClientSitesID = @CLIENTSITESID,
              @AppealID=@appealid;

       set @NCNotificationID = 0
       exec dbo.spAddUpdate_Notification @PKID = @NCNotificationID output,  
                  @NotificationTypeID = @NCNotificationTypeID,  
                  @DisplayName = @name,  
                  @Description = @name,  
                  @EmailTemplateID = null,  
                  @Enabled = 1;  

        exec USP_EVENTEMAILTEMPLATE_ADD_EDIT 
                      @ID = @ID 
                    , @EVENTID=@EVENTID
                    , @CHANGEAGENTID=@CHANGEAGENTID
                    , @EMAILTEMPLATEID = @EMAILTEMPLATEID
                    , @CONFIRMATIONTYPECODE = @index
                    , @ISAVAILABLETOINDIVIDUAL = @ISAVAILABLETOINDIVIDUAL
                    , @ISAVAILABLETOHOUSEHOLD = @ISAVAILABLETOHOUSEHOLD
                    , @ISAVAILABLETOTEAMMEMBER = @ISAVAILABLETOTEAMMEMBER 
                    , @ISAVAILABLETOTEAMLEADER =  @ISAVAILABLETOTEAMLEADER     
                    , @ISAVAILABLETOCOMPANYLEADER =  @ISAVAILABLETOCOMPANYLEADER 
                    , @ISAVAILABLETOFUNDRAISINGCOACH = @ISAVAILABLETOFUNDRAISINGCOACH 
                    , @ISAVAILABLETOSPONSOR = @ISAVAILABLETOSPONSOR
                    , @NCNOTIFICATIONID = @NCNotificationID
                    , @ISAVAILABLETOHOUSEHOLDMEMBER = @ISAVAILABLETOHOUSEHOLDMEMBER;    

    end 

    set @index = @index + 1

  end

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

return 0