WordFly LP update preferences - anyone customized that successfully yet?

USE [impresario]
GO
/****** Object:  StoredProcedure [dbo].[LP_UPDATE_EMAIL_PROMOTION]    Script Date: 8/6/2020 2:29:21 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO




ALTER   Procedure [dbo].[LP_UPDATE_EMAIL_PROMOTION](
	@source_no int =  null,
	@customer_no int = null,
	@response_code int = null,
	@response_dt datetime  = null,
	@eaddress varchar(80) = null,
	@url varchar(1024) = null
)

AS

SET NOCOUNT ON

/*************************************************************************************************
New Localized Procedure CWR 10/31/2003 which is called from WP_UPDATE_EMAIL_PROMOTION to optionally
take additional actions upon receiving email response notifications

Updated 4/10/17 - CMP, Removed error causing comments, commented out CSI creations

Sample code is provided below to illustrate some possible actions, especially dealing with Unsubscribe
events (response_code 5)

Updated 10/2/18 - BLS, updated to add unsubscribe for ahha Tulsa & generic 

Updated 7/24/19 - BLS, updated to add Gilcrease
*************************************************************************************************/


/* start WordFly sample code  ========================================================================*/


-- here is code that will set an attribute (hard coded values)



If @response_code = 5 -- Philbrook Unsubscribe
	Begin

	If Exists (Select * from tx_cust_keyword where customer_no = @customer_no and keyword_no = 215)
		Update	tx_cust_keyword
		Set	key_value = 'Unsubscribe'
		Where	customer_no = @customer_no 
			and keyword_no = 215
	Else
		Insert	tx_cust_keyword(customer_no, keyword_no, key_value)
		Select	@customer_no, 215, 'Unsubscribe'
	End


  If @response_code = 2 -- Philbrook Hardbounce
	  Begin
		If Exists (Select * from tx_cust_keyword where customer_no = @customer_no and keyword_no = 215)
			Update	tx_cust_keyword
			Set	key_value = 'Hard Bounce'
			Where	customer_no = @customer_no 
				and keyword_no = 215
		Else
			Insert	tx_cust_keyword(customer_no, keyword_no, key_value)
			Select	@customer_no, 215, 'Hard Bounce'
	  End


If @response_code = 10 -- ahha Tulsa Unsubscribe
	Begin
		If Exists (Select * from tx_cust_keyword where customer_no = @customer_no and keyword_no = 533)
			Update	tx_cust_keyword
			Set	key_value = 'Unsubscribe'
			Where	customer_no = @customer_no 
			and keyword_no = 533
		Else
			Insert	tx_cust_keyword(customer_no, keyword_no, key_value)
			Select	@customer_no, 533, 'Unsubscribe'
			End


  If @response_code = 7 -- ahha Tulsa Hardbounce
	  Begin
		If Exists (Select * from tx_cust_keyword where customer_no = @customer_no and keyword_no = 533)
			Update	tx_cust_keyword
			Set	key_value = 'Hard Bounce'
			Where	customer_no = @customer_no 
				and keyword_no = 533
		Else
			Insert	tx_cust_keyword(customer_no, keyword_no, key_value)
			Select	@customer_no, 533, 'Hard Bounce'
	  End



  If @response_code = 15 -- Gilcrease Unsubscribe
	Begin
		If Exists (Select * from tx_cust_keyword where customer_no = @customer_no and keyword_no = 590)
			Update	tx_cust_keyword
			Set	key_value = 'Unsubscribe'
			Where	customer_no = @customer_no 
			and keyword_no = 590
		Else
			Insert	tx_cust_keyword(customer_no, keyword_no, key_value)
			Select	@customer_no, 590, 'Unsubscribe'
			End


  If @response_code = 12 -- Gilcrease Hardbounce
	  Begin
		If Exists (Select * from tx_cust_keyword where customer_no = @customer_no and keyword_no = 590)
			Update	tx_cust_keyword
			Set	key_value = 'Hard Bounce'
			Where	customer_no = @customer_no 
				and keyword_no = 590
		Else
			Insert	tx_cust_keyword(customer_no, keyword_no, key_value)
			Select	@customer_no, 590, 'Hard Bounce'
	  End
 


  
  

	
/* == end WordFly sample code  ========================================================================*/




/*== start Tessitura sample code =======================================================================*/


--Declare	@errmsg varchar(255), @rowcount int

------ here is code that will set the t_customer emarket_ind to a value dependant on response codes
--If @response_code = 5 --  UNSUBSCRIBE: set the marketing indicator on the eaddress record for the customer
--	Begin
--		Update	t_customer
--		Set	emarket_ind = 5	-- no emarketing to philbrook
--		Where	customer_no = @customer_no
--	End

--If @response_code = 2  --  HARDBOUNCE:  set the marketing indicator on the eaddress record for the customer
--	Begin
--		Update	t_customer
--		Set	emarket_ind = 4	-- no emarketing to all organizations
--		Where	customer_no = @customer_no	
--	End

/* -- E-Marketing Codes
1 - n/a does not exist
2 - "No Emarketing (depriciated)
3 - (none) i.e. No preference
4 - "Do Not Email"
5 - "P Do Not Email" 
*/

/*  -- Commented out by Barbara S --
-- here is code that will write a customer service issue (hard coded values)
Declare @activity_no int

--Philbrook Unsub
If @response_code = 5
  Begin

	-- get an issue number
	EXEC @activity_no = AP_GET_NEXTID_Function 'AC'
	
	-- Put in the Customer Service Issue
	Insert into t_cust_activity(
		activity_no,
		category,
		activity_type,
		customer_no,
		contact_type,
		inout_ind,
		notes,
		issue_dt,
		origin)
	Select 	@activity_no,
		null,
		97,
		@customer_no,
		22,
		'I',
		'WordFly Unsubscribe',
		getdate(),
		null

  End

  -- Philbrook Hardbounce
  If @response_code = 2
  Begin

	-- get an issue number
	EXEC @activity_no = AP_GET_NEXTID_Function 'AC'
	
	-- Put in the Customer Service Issue
	Insert into t_cust_activity(
		activity_no,
		category,
		activity_type,
		customer_no,
		contact_type,
		inout_ind,
		notes,
		issue_dt,
		origin)
	Select 	@activity_no,
		null,
		98,
		@customer_no,
		22,
		'I',
		'WordFly Hard Bounce',
		getdate(),
		null

  End
*/
--RETURN

/*== end Tessitura sample code =======================================================================*/

	--  STEP 2:  FIND EADDRESS RECORD TO MODIFY
		declare @eaddress_no int, @org_id Int, @org_desc VarChar(30), @userid Char(8), @usergroupid Char(8), @control_group Int
		
		-- Get organisation I.D.
		Select		@org_id = organization_id
		From		LT_WF_PREFERENCE_HISTORY With (NoLock)
		Where		Id = @preferenceHistoryId
		
		-- Get organisation name
		Select		@org_desc = [description]
		From		TR_ORGANIZATION With (NoLock)
		Where		id = @org_id

		-- Get organisation TMS / WF user name
		Select		@userid = default_value
		From		T_DEFAULTS With (NoLock)
		Where		field_name = 'TMS User' And parent_table = @org_desc

		-- Get organisation TMS / WF user name's default user group
		Select		@usergroupid = UG_id
		From		TX_USER_GROUP With (NoLock)
		Where		userid = @userid And default_ind = 'y'

		-- Get organisation TMS / WF user name's default user group's default control group
		Select		@control_group = control_group
		From		TX_CONTROL_GROUP_USER_GROUP With (NoLock)
		Where		ug_id = @usergroupid And default_ind = 'y'

		-- Try for potential control grouped eaddress_no
		Set			@eaddress_no =
		(Select		Top 1 eaddress_no 
		From		T_EADDRESS a With (NoLock)
					Left Join TR_EADDRESS_TYPE b With (NoLock) On b.id = a.eaddress_type
		Where		a.customer_no = @customer_no And a.[address] = @messaging_address And a.inactive = 'n' And
					b.control_group = @control_group
		Order By	a.create_dt Desc, a.last_update_dt Desc)		

		-- If not found, try for default uncontrol grouped eaddress_no
		If			IsNull(@eaddress_no, 0) = 0
		Begin
			Set			@eaddress_no =
			(Select		Top 1 eaddress_no 
			From		T_EADDRESS a With (NoLock)
						Left Join TR_EADDRESS_TYPE b With (NoLock) On b.id = a.eaddress_type
			Where		a.customer_no = @customer_no And a.[address] = @messaging_address And a.inactive = 'n' And
						b.control_group = -1
			Order By	a.create_dt Desc, a.last_update_dt Desc)		
		End
		
		/*
		select @eaddress_no = eaddress_no from dbo.[t_eaddress]
		where [customer_no] = @customer_no and [address] = @messaging_address and [inactive] = 'N'
		order by create_dt desc, last_update_dt desc
		*/

		if ISNULL(@eaddress_no,0) = 0
				begin		
					raiserror('Invalid email address for this customer.', 11, 2) with seterror
					return -101		
				end