Wordfly Pages Integration

USE [impresario]
GO
/****** Object:  StoredProcedure [dbo].[LP_WF_PAGE_RESPONSE_CUSTOM]    Script Date: 15/06/2019 9:04:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		WordFly
-- Create date: 2018
-- Custom for BDT May 2019
-- 2019-06-15 Adapted by DGH at MIAF to accomodate spec_act_no that are not always two digits
-- =============================================
ALTER PROCEDURE [dbo].[LP_WF_PAGE_RESPONSE_CUSTOM]
	@pageResponseId int
AS
BEGIN

	SET NOCOUNT ON;


	declare  @pageId int
			,@customer_no int
			,@emailAddress varchar(80)
			,@corr_id varchar(5000)
			,@spec_act_no int
			,@spec_act_desc varchar(30)
			,@spec_act_date datetime
			,@spec_act_response_date datetime
			,@spec_act_name varchar(500)
			,@spec_act_notes varchar(255)
			,@resp_status int
			,@num_attendees int
			,@attrib_no int
			,@pageType int
		

	create table #rsvp_page_responses(
			id		int identity(1,1),
			name	varchar(500),
			url		varchar(5000),
			customer_no	int,
			email		varchar(80),
			resp_dt		datetime,
			question	varchar(max),
			answer		varchar(max))

	select top 1 @pageId = PageId,@customer_no = customer_no,@emailAddress = Email from LT_WF_PAGE_RESPONSE with (nolock) where Id = @pageResponseId


	select @corr_id = a.CorrelationId, @pageType = PageTypeId from LT_WF_PAGE a with (nolock)
	where a.Id = @pageId


	--RSVP Response Special Activity Creation
	-------------------------------------------------------------------------------------------------------------------------------
	if @pageType = 1
	begin

		set @spec_act_no = 0
		set @attrib_no = 0
--		set @spec_act_date = '2000-01-01 00:00:00:0000'

		--Retrieve the Activity or Attribute no from the CorrelationId
		if substring(@corr_id, 1,8) = 'Activity'
			/* 2019-06-15 DGH MIAF edit
			This was designed for BDT, where their spec_act_no from TR_SPECIAL_ACTIVITY must be two characters.
			This is not the case in all environments... */
			-- set @spec_act_no = cast(substring(@corr_id, 10,2) as int)

			/* We do this instead, because the @corr_id is always the same format: Activity:X_20xx-xx-xx 
			So we can look at anything between the : and _ to get the spec_act_no, and it will always work */
			set @spec_act_no = cast(substring(@corr_id,CHARINDEX(':',@corr_id)+1,CHARINDEX('_',@corr_id)-CHARINDEX(':',@corr_id)-1) as int)
		if substring(@corr_id, 1,8) = 'Activity' and @corr_id like '%_%'
				set @spec_act_date = substring(@corr_id, charindex('_', @corr_id )+1, len(@corr_id))
		else if substring(@corr_id, 1,9) = 'Attribute'
			set @attrib_no = cast(substring(@corr_id, 11,500) as int)

		--Obtain special activity description
		if @spec_act_no <> 0
		begin
			select @spec_act_desc = sp.description from dbo.TR_SPECIAL_ACTIVITY sp (NOLOCK) where sp.id = @spec_act_no

			--Insert answers into temp table
			insert into #rsvp_page_responses  (name, url, customer_No, email, resp_dt, question, answer) 
			
			select a.Name, a.Url, c.customer_no, c.Email, c.PageResponseDate, b.Question, d.Answer 
			from LT_WF_PAGE a with (nolock)
				inner join LT_WF_PAGE_FIELD b with (nolock) on b.PageId = a.Id
				inner join LT_WF_PAGE_RESPONSE c with (nolock) on c.PageId = a.Id
				inner join LT_WF_PAGE_FIELD_RESPONSE d with (nolock) on d.PageFieldId = b.Id and d.PageResponseId = c.Id
			where c.Id =  @pageResponseId

			--Debug
			--select @spec_act_no as 'special code'
			--select @spec_act_desc as 'special activity'
			--select @attrib_no as 'attribute code'
			--select * from #rsvp_page_responses

			--Determine special activity status from answers 2=Declined, 4=Accepted, 1=Invited
			select @resp_status = case when answer = 'yes' then 4
									   when answer = 'no' then 2
								  else 1 end
			from #rsvp_page_responses where id = 1

			--Select number of attendees, rsvp name, and response date
			select @num_attendees = cast(answer as int),
				   @spec_act_name = name,
				   @spec_act_response_date = resp_dt
			from #rsvp_page_responses where id = 2

			--Select notes from responses
			select @spec_act_notes = substring(answer,1,255)
			from #rsvp_page_responses where id = 3

			select @spec_act_date
			select cast(@spec_act_date as datetime)
		
			--Insert or update retrieved data into Special Activity table
			if not exists (select sa_no from dbo.T_SPECIAL_ACTIVITY (nolock) where customer_no = @customer_no and sp_act = @spec_act_no and sp_act_dt = cast(@spec_act_date as datetime)) 			
				insert into dbo.T_SPECIAL_ACTIVITY (customer_no, sp_act, sp_act_dt, solicitor, perf, status, notes, num_attendees)
				values (@customer_no, @spec_act_no, cast(@spec_act_date as datetime), NULL, substring(@spec_act_name,1,20),@resp_status,@spec_act_notes,@num_attendees)
			else
				update dbo.T_SPECIAL_ACTIVITY
					set status = @resp_status,
						sp_act = @spec_act_no,
						perf = substring(@spec_act_name,1,20),
						notes = @spec_act_notes,
						num_attendees = @num_attendees
				where customer_no = @customer_no
				and sp_act = @spec_act_no
				and sp_act_dt = cast(@spec_act_date as datetime)

			end
		end
	--end

--	RETURN


RETURN


	--declare @pageId int,@customer_no int,@emailAddress varchar(80)
	--select top 1 @pageId = PageId,@customer_no = customer_no,@emailAddress = Email from LT_WF_PAGE_RESPONSE with (nolock) where Id = @pageResponseId
	
	--===  PAGE
	select * from LT_WF_PAGE a with (nolock)
	where a.Id = @pageId
	
	--==   QUESTIONS	
	select b.*
	from LT_WF_PAGE a with (nolock)
	inner join LT_WF_PAGE_FIELD b with (nolock) on b.PageId = a.Id
	where a.Id = @pageId

	--==   ANSWERS	
	select b.Question, d.Answer, COUNT(*) as AnswerCount
	from LT_WF_PAGE a with (nolock)
	inner join LT_WF_PAGE_FIELD b with (nolock) on b.PageId = a.Id
	inner join LT_WF_PAGE_RESPONSE c with (nolock) on c.PageId = a.Id
	inner join LT_WF_PAGE_FIELD_RESPONSE d with (nolock) on d.PageFieldId = b.Id and d.PageResponseId = c.Id
	where a.Id = @pageId
	group by b.Question, d.Answer
	order by b.Question,COUNT(*) desc

	--==  SPECIFIED RESPONSE
	select a.Name,a.Url,c.customer_no,c.Email,c.PageResponseDate,b.Question,d.Answer 
	from LT_WF_PAGE a with (nolock)
	inner join LT_WF_PAGE_FIELD b with (nolock) on b.PageId = a.Id
	inner join LT_WF_PAGE_RESPONSE c with (nolock) on c.PageId = a.Id
	inner join LT_WF_PAGE_FIELD_RESPONSE d with (nolock) on d.PageFieldId = b.Id and d.PageResponseId = c.Id
	where c.Id = @pageResponseId

	--==  ALL RESPONSES FOR THIS PAGE
	select a.Name,a.Url,c.customer_no,c.Email,c.PageResponseDate,b.Question,d.Answer 
	from LT_WF_PAGE a with (nolock)
	inner join LT_WF_PAGE_FIELD b with (nolock) on b.PageId = a.Id
	inner join LT_WF_PAGE_RESPONSE c with (nolock) on c.PageId = a.Id
	inner join LT_WF_PAGE_FIELD_RESPONSE d with (nolock) on d.PageFieldId = b.Id and d.PageResponseId = c.Id
	where a.Id = @pageId
	order by b.Id,c.Id desc

	--==  ALL RESPONSES TO ALL PAGES FOR THIS CUSTOMER
	select a.Name,a.Url,c.customer_no,c.Email,c.PageResponseDate,b.Question,d.Answer 
	from LT_WF_PAGE a with (nolock)
	inner join LT_WF_PAGE_FIELD b with (nolock) on b.PageId = a.Id
	inner join LT_WF_PAGE_RESPONSE c with (nolock) on c.PageId = a.Id
	inner join LT_WF_PAGE_FIELD_RESPONSE d with (nolock) on d.PageFieldId = b.Id and d.PageResponseId = c.Id
	where c.customer_no = @customer_no
	order by b.Id,c.Id desc

	--==  ALL RESPONSES TO ALL PAGES FOR THIS EMAIL
	select a.Name,a.Url,c.customer_no,c.Email,c.PageResponseDate,b.Question,d.Answer 
	from LT_WF_PAGE a with (nolock)
	inner join LT_WF_PAGE_FIELD b with (nolock) on b.PageId = a.Id
	inner join LT_WF_PAGE_RESPONSE c with (nolock) on c.PageId = a.Id
	inner join LT_WF_PAGE_FIELD_RESPONSE d with (nolock) on d.PageFieldId = b.Id and d.PageResponseId = c.Id
	where c.Email = @emailAddress
	order by b.Id,c.Id desc

END