-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2017-03-09
-- Description:	Custom for SBF: Calculates the Total "Portion of Total Monthly Income Contributed to Household Expenses"
--								Gets all child surfaces with a field called 'Portion...' 
--								If Portion = All, add to the total Portion the full value in the field 'Total Monthly Income'
--								If Portion = None, add nothing
--								If Portion = Limited, add the value specified in the wants reason textbox on the Portion field
--								SUM for all child items
-- =============================================
CREATE PROCEDURE [dbo].[sp_STUD1_CalculatePortionOfTotalMonthlyIncome] 
	@surfaceId INT, 
	@surfaceItemId INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT	SUM(Portion) AS TotalPortion 
	FROM	(
			SELECT	CASE l2.display
					WHEN 'None' THEN 0
					WHEN 'All' THEN ISNULL((
									CASE WHEN ISNULL((SELECT COUNT(f4.recId)
										FROM pal_SurfaceField f4 
										WHERE f4.surfaceId = d2.surfaceId AND f4.surfaceFieldDisplay = 'Total Monthly Income' AND f4.actionType = 6 AND f4.action = 10 AND LEN(f4.actionValue) > 0), 0) > 0 
									THEN 
										ISNULL((SELECT 
												ISNULL(SUM(CASE 
													WHEN f6.surfaceFieldTypeId = 4 THEN d6.surfaceFieldValueNum
													WHEN f6.surfaceFieldTypeId = 5 THEN d6.surfaceFieldValueDecimal
													ELSE 0 END),0) AS TotalSum 
												FROM pal_SurfaceField f6
												INNER JOIN pal_SurfaceFieldData d6 WITH(NOLOCK) ON f6.recId = d6.surfaceFieldID 
												INNER JOIN pal_SurfaceField f5 ON f5.surfaceId = d2.surfaceId AND f5.surfaceFieldDisplay = 'Total Monthly Income' AND f5.actionType = 6 AND f5.action = 10 AND LEN(f5.actionValue) > 0
												WHERE d6.surfaceId = f5.actionValue 
												AND d6.surfaceFieldID = f5.actionSource 
												AND d6.surfaceItemId IN (SELECT d7.surfaceItemId
													FROM pal_SurfaceField f7 
													INNER JOIN pal_SurfaceFieldData d7 WITH(NOLOCK) ON f7.recId = d7.surfaceFieldID 
													WHERE d7.surfaceId = f5.actionValue 
													AND f7.surfaceFieldDisplay = 'ParentSurfaceItemId' 
													AND d7.surfaceFieldValueNum = d2.surfaceItemId) ),0)
									ELSE 
										ISNULL((
											SELECT CASE f8.surfaceFieldTypeId WHEN 4 THEN d8.surfaceFieldValueNum WHEN 5 THEN d8.surfaceFieldValueDecimal ELSE 0 END
											FROM pal_SurfaceFieldData d8 WITH(NOLOCK)
											INNER JOIN pal_SurfaceField f8 ON f8.recId = d8.surfaceFieldID 
											WHERE f8.surfaceId = d2.surfaceId
											AND f8.surfaceFieldDisplay = 'Total Monthly Income' 
											AND d8.surfaceItemId = d2.surfaceItemId 
											),0) 
									END
									),0) 
					ELSE ISNULL(TRY_PARSE(d2.surfaceFieldValueChar AS INT), 0)
					END AS Portion
			FROM	pal_Surface s2				
					JOIN pal_SurfaceField f2 
						ON s2.recId = f2.surfaceId 
					JOIN pal_SurfaceFieldData d2 WITH(NOLOCK)
						ON f2.recId = d2.surfaceFieldID 
					JOIN pal_SurfaceLookup l2 
						ON l2.recId = d2.surfaceFieldLookupID 
			WHERE	s2.name IN (
						SELECT	f1.relationalSurface  
						FROM	pal_SurfaceField f1
						WHERE	f1.surfaceId = @surfaceId 
								AND f1.surfaceFieldTypeId = 11 
								AND f1.isControlled = 0 
						)
					AND f2.surfaceFieldDisplay like 'Portion of Total Monthly Income%' 
					AND d2.surfaceItemId IN (
						SELECT	d3.surfaceItemId 
						FROM	pal_SurfaceField f3 
								JOIN pal_SurfaceFieldData d3 WITH(NOLOCK) 
									ON f3.recId = d3.surfaceFieldID 
						WHERE	f3.surfaceFieldDisplay = 'ParentSurfaceItemId' 
								AND d3.surfaceFieldValueNum = @surfaceItemId   
								AND d3.surfaceId = d2.surfaceId
						)
			) Result
END