/* CVH 2017-09-27 ALTER to calculate failed based on Number of Subjects taken and Subjects Passed if these fields exist, otherwise it counts all 0's, even subjects not taken */
CREATE FUNCTION [dbo].[fn_GetSurfaceFieldAggregateFailed]
( 
	@surfaceItemId INT
	,@actionValue VARCHAR(255)
)
RETURNS INT
AS
BEGIN
DECLARE @returnVal INT

IF EXISTS (
SELECT f1.recId
FROM pal_SurfaceItem i1 
LEFT OUTER JOIN pal_SurfaceField f1 ON f1.surfaceId = i1.surfaceId 
WHERE i1.recId = @surfaceItemId 
AND (f1.surfaceFieldDisplay IN ('Number of Courses Taken', 'Number of Subjects Taken')
OR f1.surfaceFieldDisplay IN ('Number of Courses Passed','Number of Subjects Passed')))
BEGIN 
	SELECT @returnVal = CASE WHEN ISNULL(surfaceFieldValueNum,0) > 0 
			THEN (surfaceFieldValueNum - dbo.fn_GetSurfaceFieldAggregatePassed(@surfaceItemId, fNoPassed.actionValue)) 
			ELSE 0
			END 
	FROM	pal_Surface S
	JOIN pal_SurfaceItem I
		ON I.surfaceId = S.recId
	LEFT JOIN pal_SurfaceField fNoCourses
		ON fNoCourses.surfaceFieldDisplay IN ('Number of Courses Taken', 'Number of Subjects Taken')
		AND fNoCourses.surfaceId = S.recId
	LEFT JOIN pal_SurfaceFieldData dNoCourses
		ON dNoCourses.surfaceFieldID = fNoCourses.recId
		AND dNoCourses.surfaceId = S.recId
		AND dNoCourses.surfaceItemId = I.recId
	LEFT JOIN pal_SurfaceField fNoPassed
		ON fNoPassed.surfaceFieldDisplay IN ('Number of Courses Passed','Number of Subjects Passed')
		AND fNoPassed.surfaceId = S.recId
	WHERE I.recId = @surfaceItemId
END
ELSE 
BEGIN
	SELECT	@returnVal = COUNT(1)
	FROM	pal_SurfaceField F
			JOIN pal_SurfaceFieldData D
				ON D.surfaceFieldID = F.recId
	WHERE	D.surfaceItemId = @surfaceItemId
			AND F.recId IN (SELECT splitdata FROM SplitString(@actionValue,'|'))
			AND 50 > CASE F.surfaceFieldTypeId 
						WHEN 4
						THEN D.surfaceFieldValueNum
						WHEN 5
						THEN D.surfaceFieldValueDecimal
						END
END

RETURN	@returnVal

END

