USP_FAFNOTIFICATION_SUBSCRIPTION

subscribe to FAF notification based on registration roles
Parameters

Parameter	Parameter Type	Mode
@UserID	int	IN
@EventID	uniqueidentifier	IN
@FAFRoles	nvarchar(250)	IN
Definition

Copy


CREATE procedure dbo.USP_FAFNOTIFICATION_SUBSCRIPTION
    @UserID int, 
    @EventID uniqueidentifier,
    @FAFRoles nvarchar(250)
as
begin
  set @FAFRoles= LOWER(@FAFRoles)

    -- everyone gets 22,27,31,33, 34

    -- team/household/company leader gets 23, 24, 25, 35

    -- team/company leader gets 26, 28, 32


    DECLARE @notificationID int

    SELECT @notificationID = NCNOTIFICATIONID from dbo.EVENTEMAILTEMPLATE (NOLOCK) where EVENTID = @EventID and CONFIRMATIONTYPECODE= 22
    IF @notificationID IS NOT NULL
        exec dbo.spAddUpdate_NotificationSubscription @pkid=0, @userID=@UserID, @NotificationID= @notificationID, @contextID= null, @status = 1,  @BackOfficeSystemPeopleID = null,  @BackOfficeSystemsID = null

  SET @notificationID = null
    SELECT @notificationID = NCNOTIFICATIONID from dbo.EVENTEMAILTEMPLATE (NOLOCK) where EVENTID = @EventID and CONFIRMATIONTYPECODE= 27
    IF @notificationID IS NOT NULL
        exec dbo.spAddUpdate_NotificationSubscription @pkid=0, @userID=@UserID, @NotificationID= @notificationID, @contextID= null, @status = 1,  @BackOfficeSystemPeopleID = null,  @BackOfficeSystemsID = null

  SET @notificationID = null
    SELECT @notificationID = NCNOTIFICATIONID from dbo.EVENTEMAILTEMPLATE (NOLOCK) where EVENTID = @EventID and CONFIRMATIONTYPECODE= 31
    IF @notificationID IS NOT NULL
        exec dbo.spAddUpdate_NotificationSubscription @pkid=0, @userID=@UserID, @NotificationID= @notificationID, @contextID= null, @status = 1,  @BackOfficeSystemPeopleID = null,  @BackOfficeSystemsID = null

  SET @notificationID = null
    SELECT @notificationID = NCNOTIFICATIONID from dbo.EVENTEMAILTEMPLATE (NOLOCK) where EVENTID = @EventID and CONFIRMATIONTYPECODE= 33
    IF @notificationID IS NOT NULL
        exec dbo.spAddUpdate_NotificationSubscription @pkid=0, @userID=@UserID, @NotificationID= @notificationID, @contextID= null, @status = 1,  @BackOfficeSystemPeopleID = null,  @BackOfficeSystemsID = null

---transfer gift notification

  SET @notificationID = null
      SELECT @notificationID = NCNOTIFICATIONID from dbo.EVENTEMAILTEMPLATE (NOLOCK) where EVENTID = @EventID and CONFIRMATIONTYPECODE= 34
      IF @notificationID IS NOT NULL
          exec dbo.spAddUpdate_NotificationSubscription @pkid=0, @userID=@UserID, @NotificationID= @notificationID, @contextID= null, @status = 1,  @BackOfficeSystemPeopleID = null,  @BackOfficeSystemsID = null


    IF CHARINDEX('leader', @FAFRoles)>0
    BEGIN

    SET @notificationID = null
        SELECT @notificationID = NCNOTIFICATIONID from dbo.EVENTEMAILTEMPLATE (NOLOCK) where EVENTID = @EventID and CONFIRMATIONTYPECODE= 23
        IF @notificationID IS NOT NULL
            exec dbo.spAddUpdate_NotificationSubscription @pkid=0, @userID=@UserID, @NotificationID= @notificationID, @contextID= null, @status = 1,  @BackOfficeSystemPeopleID = null,  @BackOfficeSystemsID = null

    SET @notificationID = null
        SELECT @notificationID = NCNOTIFICATIONID from dbo.EVENTEMAILTEMPLATE (NOLOCK) where EVENTID = @EventID and CONFIRMATIONTYPECODE= 24
        IF @notificationID IS NOT NULL
            exec dbo.spAddUpdate_NotificationSubscription @pkid=0, @userID=@UserID, @NotificationID= @notificationID, @contextID= null, @status = 1,  @BackOfficeSystemPeopleID = null,  @BackOfficeSystemsID = null

    SET @notificationID = null
        SELECT @notificationID = NCNOTIFICATIONID from dbo.EVENTEMAILTEMPLATE (NOLOCK) where EVENTID = @EventID and CONFIRMATIONTYPECODE= 25
        IF @notificationID IS NOT NULL
            exec dbo.spAddUpdate_NotificationSubscription @pkid=0, @userID=@UserID, @NotificationID= @notificationID, @contextID= null, @status = 1,  @BackOfficeSystemPeopleID = null,  @BackOfficeSystemsID = null

    SET @notificationID = null
        SELECT @notificationID = NCNOTIFICATIONID from dbo.EVENTEMAILTEMPLATE (NOLOCK) where EVENTID = @EventID and CONFIRMATIONTYPECODE= 35
        IF @notificationID IS NOT NULL
            exec dbo.spAddUpdate_NotificationSubscription @pkid=0, @userID=@UserID, @NotificationID= @notificationID, @contextID= null, @status = 1,  @BackOfficeSystemPeopleID = null,  @BackOfficeSystemsID = null

        IF CHARINDEX('company leader', @FAFRoles)>0 or CHARINDEX('team leader', @FAFRoles)>0
        BEGIN

      SET @notificationID = null
            SELECT @notificationID = NCNOTIFICATIONID from dbo.EVENTEMAILTEMPLATE (NOLOCK) where EVENTID = @EventID and CONFIRMATIONTYPECODE= 26
            IF @notificationID IS NOT NULL
                exec dbo.spAddUpdate_NotificationSubscription @pkid=0, @userID=@UserID, @NotificationID= @notificationID, @contextID= null, @status = 1,  @BackOfficeSystemPeopleID = null,  @BackOfficeSystemsID = null

      SET @notificationID = null
            SELECT @notificationID = NCNOTIFICATIONID from dbo.EVENTEMAILTEMPLATE (NOLOCK) where EVENTID = @EventID and CONFIRMATIONTYPECODE= 28
            IF @notificationID IS NOT NULL
                exec dbo.spAddUpdate_NotificationSubscription @pkid=0, @userID=@UserID, @NotificationID= @notificationID, @contextID= null, @status = 1,  @BackOfficeSystemPeopleID = null,  @BackOfficeSystemsID = null

      SET @notificationID = null
            SELECT @notificationID = NCNOTIFICATIONID from dbo.EVENTEMAILTEMPLATE (NOLOCK) where EVENTID = @EventID and CONFIRMATIONTYPECODE= 32
            IF @notificationID IS NOT NULL
                exec dbo.spAddUpdate_NotificationSubscription @pkid=0, @userID=@UserID, @NotificationID= @notificationID, @contextID= null, @status = 1,  @BackOfficeSystemPeopleID = null,  @BackOfficeSystemsID = null

        END
    END
end