cust_ohc_text_autoreply_MEET

This is a sample from Trey. This is the word handler for "MEET". When this sproc runs it looks for anybody who has that phone number. Of the list found it sorts by family status and gender to best guess at the head of household (this could be customized to your needs). If a person is found from that search it then will add that person to a specific tag and initiate a response message. If a person is not found a different response message is sent.

CREATE PROCEDURE [dbo].[cust_ohc_text_autoreply_MEET] 
( 
    @FromNumber VARCHAR(20) 
    , @PossiblyFrom VARCHAR(200) 
    , @ToNumber VARCHAR(20) 
    , @ToNumberID INT
    , @Message VARCHAR(2000) 
    , @OutStatus INT = 0 OUTPUT
    , @OutMessage VARCHAR(2000) = '' OUTPUT
) 
AS
  
SET @FromNumber = dbo.fn_StripPhone(@FromNumber) 
DECLARE @LogicTest INT
SELECT @LogicTest =  
( 
SELECT TOP 1 ph.person_id 
FROM
    core_person_phone ph 
    JOIN core_person p ON ph.person_id = p.person_id 
    JOIN core_family_member f ON p.person_id = f.person_id 
WHERE
    ph.phone_number_stripped = RIGHT(@FromNumber,10) 
    OR ph.phone_number_stripped + phone_ext = RIGHT(@FromNumber,10) 
ORDER BY
    CASE WHEN ph.phone_luid = 282 THEN -1 ELSE ph.phone_luid END
    , f.role_luid 
    , p.gender 
) 
  
DECLARE @TagId INT = 2916 -- Tag to put person in. 
DECLARE @TagSource INT = 274 -- Website 
DECLARE @TagStatus INT = 255 -- Connected 
DECLARE @TagActivity INT = 269 -- Status Change 
DECLARE @Now DATETIME = GETDATE() 
DECLARE @Note VARCHAR(2000) = 'Text received from: ' + RIGHT(@FromNumber,10) 
  
IF @LogicTest <> '' OR @LogicTest IS NOT NULL
BEGIN
    EXEC dbo.core_sp_save_profile_member @TagId, @LogicTest, 'MEET Text', @TagSource, @TagStatus, @Note, '9999-12-31 23:59:59.997', '9999-12-31 23:59:59.997', @Now, '9999-12-31 23:59:59.997'
    EXEC dbo.core_sp_save_profile_member_activity @TagId, @LogicTest, 'MEET Text', @TagActivity,'Member added to ''MEET'' as ''Connected''.'
      
    --Mark this number as SMS Enabled 
        UPDATE
            core_person_phone 
        SET
            sms_enabled = 1 
        WHERE
            phone_number_stripped = RIGHT(@FromNumber,10) 
            OR phone_number_stripped + phone_ext = RIGHT(@FromNumber,10) 
END
  
SELECT @OutMessage = CASE
WHEN @LogicTest IS NULL THEN 'We''re excited to meet you! Click the link to provide a little more information and your Community Minister will be in touch soon: http://bit.ly/mMeetMyMinister'
ELSE 'We''re excited to meet you! Your Community Minister will be in touch soon.'
END
  
SET @OutStatus = 1