-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2016-08-16
-- Description:	Gets a list of email addresses to use as Email CC on Sales transaction emails
--				Sales transaction is linked to Contact (surface) which links to Contact People (surface) where Email is saved
--				Assumptions:
--					- Child surface name must be "ContactPeople"
--					- Child surface must contain field "ParentSurfaceItemId" (auto created)
--					- Child surface must contain field "Include in Emails" **
--					- Child surface must contain field "Email" **
--					** Spelling Email or E-mail will be accepted 
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSalesCcEmailAddresses] 
	-- Add the parameters for the stored procedure here
	@parentSurfaceItemId INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    DECLARE @childSurfaceId INT, @parentSurfaceItemIdFieldId INT, @contactPeopleIncludeFieldId INT, @contactPeopleEmailFieldId INT;

	SELECT @childSurfaceId = recId 
	FROM pal_Surface WHERE name = 'ContactPeople'

	print @childSurfaceId 

	SELECT @parentSurfaceItemIdFieldId = recId  
	FROM pal_SurfaceField 
	WHERE surfaceId = @childSurfaceId 
	AND surfaceFieldName = 'ParentSurfaceItemId'

	print @parentSurfaceItemIdFieldId

	SELECT TOP 1 @contactPeopleIncludeFieldId = recId 
	FROM pal_SurfaceField 
	WHERE surfaceId = @childSurfaceId 
	AND LOWER(surfaceFieldDisplay) IN ('include in emails', 'include in e-mails')

	print @contactPeopleIncludeFieldId

	SELECT TOP 1 @contactPeopleEmailFieldId = recId 
	FROM pal_SurfaceField 
	WHERE surfaceId = @childSurfaceId 
	AND surfaceFieldDisplay IN ('Email', 'E-mail')

	print @contactPeopleEmailFieldId

	SELECT surfaceFieldValueChar AS CcEmail 
	FROM pal_SurfaceFieldData 
	WHERE surfaceItemId IN (
		SELECT surfaceItemId 
		FROM pal_SurfaceFieldData 
		WHERE surfaceFieldID = @parentSurfaceItemIdFieldId 
		AND surfaceFieldValueNum = @parentSurfaceItemId ) 
	AND surfaceItemId IN (
		SELECT surfaceItemId 
		FROM pal_SurfaceFieldData 
		WHERE surfaceFieldID = @contactPeopleIncludeFieldId 
		AND surfaceFieldValueBool = 1 ) 
	AND surfaceFieldID = @contactPeopleEmailFieldId
	AND	LEN(LTRIM(RTRIM(surfaceFieldValueChar))) > 0
END
