-- =============================================
-- Author:		Jas Rademeyer
-- Create date: 2017-05-04
-- Description:	If a child grid is moved from it's parent to it's grandparent surface, the data needs to be updated
--              This proc is run after the child grid has been moved to it's new location
-- =============================================
CREATE PROC sp_MoveChildSurfaceFromParentToGrandParent
(
	@surfaceIdToUpdate INT-- = 1023
	,@currentParentSurfaceId INT-- = 1016
	,@safetyCheck VARCHAR(10) = ''
)
AS

IF(@safetyCheck <> 'PLEASE')
BEGIN
	SELECT	'Are you sure? Say the magic word.'
END
ELSE
BEGIN
	DECLARE @currentParentFieldId INT,
			@newParentFieldId INT,
			@newRelationalSurface VARCHAR(50)
		

	SELECT	@currentParentFieldId = F.recId
	FROM	pal_SurfaceField F
			JOIN pal_Surface S
				ON S.recId = F.surfaceId
	WHERE	F.surfaceFieldName = 'parentSurfaceItemId'
			AND F.surfaceId = @surfaceIdtoUpdate

	SELECT	@newParentFieldId = F.recId
	FROM	pal_SurfaceField F
			JOIN pal_Surface S
				ON S.recId = F.surfaceId
	WHERE	F.surfaceFieldName = 'parentSurfaceItemId'
			AND F.surfaceId = @currentParentSurfaceId

	UPDATE	D
		SET	surfaceFieldValueNum = DP.surfaceFieldValueNum
			,@newRelationalSurface = FP.relationalSurface
	--SELECT	I.*,D.surfaceFieldValueNum--,DP.surfaceFieldValueNum
	FROM	pal_SurfaceItem I
			JOIN pal_SurfaceField F
				ON F.surfaceId = I.surfaceId
				AND F.recId = @currentParentFieldId
			JOIN pal_SurfaceFieldData D
				ON D.surfaceFieldID = F.recId
				AND D.surfaceItemId = I.recId
			JOIN pal_SurfaceItem IP
				ON IP.recId = D.surfaceFieldValueNum
			JOIN pal_SurfaceField FP
				ON FP.surfaceId = IP.surfaceId
				AND FP.recId = @newParentFieldId
			JOIN pal_SurfaceFieldData DP
				ON DP.surfaceFieldID = FP.recId
				AND DP.surfaceItemId = IP.recId

	UPDATE	F
		SET	relationalSurface = @newRelationalSurface
	--SELECT	*
	FROM	pal_SurfaceField F
			JOIN pal_Surface S
				ON S.recId = F.surfaceId
	WHERE	F.surfaceFieldName = 'parentSurfaceItemId'
			AND F.surfaceId = @surfaceIdtoUpdate

	EXEC sp_CreateSurfaceQueryTable @surfaceIdtoUpdate,0,219
END
