-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2016-08-17
-- Description:	Gets the primary contact person to use in the body of Sales transaction emails
--				Sales transaction is linked to Contact (surface) which links to Contact People (surface) where Primary Contact is indicated
--				Assumptions:
--					- Child surface name must be "ContactPeople"
--					- Child surface must contain field "ParentSurfaceItemId" (auto created)
--					- Child surface must contain field "Primary Contact" 
--					- Child surface must contain field "Contact Person" 
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSalesPrimaryContact] 
	-- 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, @contactPeoplePrimaryFieldId INT, @contactPeoplePersonFieldId 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 @contactPeoplePrimaryFieldId = recId 
	FROM pal_SurfaceField 
	WHERE surfaceId = @childSurfaceId 
	AND LOWER(surfaceFieldDisplay) IN ('primary contact')

	print @contactPeoplePrimaryFieldId

	SELECT TOP 1 @contactPeoplePersonFieldId = recId 
	FROM pal_SurfaceField 
	WHERE surfaceId = @childSurfaceId 
	AND LOWER(surfaceFieldDisplay) IN ('contact person')

	print @contactPeoplePersonFieldId

	SELECT TOP 1 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 = @contactPeoplePrimaryFieldId 
		AND surfaceFieldValueBool = 1 ) 
	AND surfaceFieldID = @contactPeoplePersonFieldId
	AND	LEN(LTRIM(RTRIM(surfaceFieldValueChar))) > 0

END
