-- =============================================
-- Author:				Jas Rademeyer 
-- Plagiarized from :	Charlene van Heerden's sp_GetSurfaceAggrSum
-- Create date:			2017-04-18
-- Description:			Calculates the surface action Aggregation Sum. Calculates the sum value of the given surface and field, and parentSurfaceItemId if applicable. 
--						Only sums a field of type Number of Decimal.
--						Will sum the value for all surface instances on the given item, as well as 1 level lower. 
--						Ignores summary surfaces, no data saved for summary surfaces. For SBF, it will calculate the 1 level down sum, but ignore the 2x summary surface directly linked to item.
-- =============================================
CREATE FUNCTION [dbo].[fn_GetSurfaceAggrSum]
( 
	@surfaceId INT,
	@surfaceFieldId INT,
	@parentSurfaceItemId INT
)
RETURNS INT
AS
BEGIN
DECLARE @returnVal DECIMAL(18,2)

IF (ISNULL((SELECT COUNT(f3.recId) FROM pal_SurfaceField f3 WHERE f3.surfaceId = @surfaceId AND f3.surfaceFieldDisplay = 'ParentSurfaceItemId'), 0) > 0)
	BEGIN
		
		--CVH 2017-03-22 If it's a linked surface, filter on pal_SurfaceItem.surfaceLinkItems, not ParentSurfaceItemId
		DECLARE @isLinkedSurface BIT;
		IF EXISTS(
			SELECT * 
			FROM pal_SurfaceLink l
			INNER JOIN pal_SurfaceItem p ON p.surfaceId = l.linkedSurfaceId 
			WHERE l.surfaceId = @surfaceId 
			AND p.recId = @ParentSurfaceItemId)
		SET 
			@isLinkedSurface = 1
		ELSE 
		SET 
			@isLinkedSurface = 0

		IF (@isLinkedSurface = 1)
		BEGIN
			SELECT @returnVal = ISNULL(SUM(CASE 
				WHEN f2.surfaceFieldTypeId = 4 THEN d2.surfaceFieldValueNum
				WHEN f2.surfaceFieldTypeId = 5 THEN d2.surfaceFieldValueDecimal
				ELSE 0 END),0) --AS TotalSum 
			FROM pal_SurfaceField f2 
			LEFT OUTER JOIN pal_SurfaceFieldData d2 ON f2.recId = d2.surfaceFieldID 
			LEFT OUTER JOIN pal_SurfaceItem i2 ON i2.recId = d2.surfaceItemId 
			WHERE d2.surfaceId = @surfaceId 
			AND d2.surfaceFieldID = @surfaceFieldId 
			AND CAST(@ParentSurfaceItemId AS VARCHAR) IN (select splitdata from dbo.SplitString(i2.surfaceLinkItems, ',')) 
		END
		ELSE
		BEGIN

			DECLARE @childParentItemIds AS NVARCHAR(MAX);
			--get all items on lower level surface linked to parent item Id 
			SELECT @childParentItemIds =  STUFF((SELECT ',' + CONVERT(VARCHAR(50), d1.surfaceItemId)
				FROM pal_SurfaceFieldData d1 
				LEFT OUTER JOIN pal_SurfaceField f1 ON f1.recId = d1.surfaceFieldID 
				LEFT OUTER JOIN pal_SurfaceItem i1 ON i1.recId = d1.surfaceItemId 
				WHERE f1.surfaceFieldDisplay = 'ParentSurfaceItemId' 
				AND d1.surfaceFieldValueNum = @parentSurfaceItemId
				AND i1.isActive = 1 AND i1.isDeleted = 0
				AND d1.surfaceId IN (
					--get all surface Id's where target surface is a child surface on one level lower than target field
					SELECT f3.surfaceId 
					FROM pal_SurfaceField f3 
					LEFT OUTER JOIN pal_Surface s3 ON s3.name = f3.relationalSurface  
					WHERE f3.surfaceFieldTypeId = 11 --grid 
					AND s3.recId = @surfaceId 
					AND f3.surfaceId NOT IN (SELECT i3.surfaceId 
					FROM pal_SurfaceItem i3 
					WHERE i3.recId = @parentSurfaceItemId))
			FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)') 
			,1,1,'')


			--if the surface is linked to a lower level, add to query, otherwise only sum current level
			IF (LEN(@childParentItemIds) > 0)
			BEGIN
				SELECT @returnVal = ISNULL(SUM(CASE 
					WHEN f2.surfaceFieldTypeId = 4 THEN d2.surfaceFieldValueNum
					WHEN f2.surfaceFieldTypeId = 5 THEN d2.surfaceFieldValueDecimal
					ELSE 0 END),0) --AS TotalSum 
				FROM pal_SurfaceFieldData d2 
				LEFT OUTER JOIN pal_SurfaceField f2 ON f2.recId = d2.surfaceFieldID 
				WHERE --d2.surfaceId = @surfaceId 
				--AND 
				d2.surfaceFieldID = @surfaceFieldId 
				AND	d2.surfaceItemId IN (SELECT d1.surfaceItemId
					FROM pal_SurfaceFieldData d1 
					LEFT OUTER JOIN pal_SurfaceField f1 ON f1.recId = d1.surfaceFieldID 
					WHERE d1.surfaceId = @surfaceId 
					AND f1.surfaceFieldDisplay = 'ParentSurfaceItemId' 
					AND (d1.surfaceFieldValueNum = @parentSurfaceItemId OR d1.surfaceFieldValueNum IN (select splitdata from dbo.SplitString(@childParentItemIds, ',')))) 
			END
			ELSE
			BEGIN
				SELECT @returnVal = ISNULL(SUM(CASE 
					WHEN f2.surfaceFieldTypeId = 4 THEN d2.surfaceFieldValueNum
					WHEN f2.surfaceFieldTypeId = 5 THEN d2.surfaceFieldValueDecimal
					ELSE 0 END),0) --AS TotalSum 
				FROM pal_SurfaceFieldData d2 
				LEFT OUTER JOIN pal_SurfaceField f2 ON f2.recId = d2.surfaceFieldID 
				WHERE d2.surfaceId = @surfaceId 
				AND d2.surfaceFieldID = @surfaceFieldId 
				AND d2.surfaceItemId IN (SELECT d1.surfaceItemId
					FROM pal_SurfaceFieldData d1 
					LEFT OUTER JOIN pal_SurfaceField f1 ON f1.recId = d1.surfaceFieldID 
					WHERE d1.surfaceId = @surfaceId 
					AND f1.surfaceFieldDisplay = 'ParentSurfaceItemId' 
					AND d1.surfaceFieldValueNum = @parentSurfaceItemId) 
			END
		END
	END
	ELSE
	BEGIN
		SELECT @returnVal = ISNULL(SUM(CASE 
			WHEN f2.surfaceFieldTypeId = 4 THEN d2.surfaceFieldValueNum
			WHEN f2.surfaceFieldTypeId = 5 THEN d2.surfaceFieldValueDecimal
			ELSE 0 END), 0) --AS TotalSum 
		FROM pal_SurfaceFieldData d2 
		LEFT OUTER JOIN pal_SurfaceField f2 ON f2.recId = d2.surfaceFieldID 
		WHERE d2.surfaceId = @surfaceId 
		AND d2.surfaceFieldID = @surfaceFieldId 
	END

RETURN	@returnVal

END
