-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2017-03-07
-- Description:	Returns the datatable required for the Document Checklist report in project STUD-1 (Students for a better future) (see task Stu-Pro-18 for report requirements)
-- =============================================
CREATE PROCEDURE [dbo].[sp_STUD1_GetReportDataDocumentChecklist] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    DECLARE @cols AS NVARCHAR(MAX), @colsPivot AS NVARCHAR(MAX),
		@query1  AS NVARCHAR(MAX),
		@query2  AS NVARCHAR(MAX),
		@query3  AS NVARCHAR(MAX),
		@query4  AS NVARCHAR(MAX),
		@primarySurfaceId INT

	SET @primarySurfaceId = ISNULL((SELECT TOP 1 f.surfaceId 
		FROM pal_ReportField f 
		LEFT OUTER JOIN pal_Report r ON r.recId = f.reportId 
		WHERE r.name = 'Document Checklist' 
		AND f.reportFieldTypeId = 1 
		AND f.surfaceId <> 0
		ORDER BY f.[sequence] ASC ),0)

	SELECT @cols = STUFF((SELECT ', ISNULL(' + QUOTENAME(cf.reportFieldDisplay) + ', '''') AS ''' + cf.reportFieldDisplay + ''' ' 
				FROM pal_ReportField cf 
				INNER JOIN pal_Report cr ON cr.recId = cf.reportId 
				INNER JOIN pal_SurfaceField csf ON csf.recId = cf.surfaceFieldId
				WHERE cr.name = 'Document Checklist' AND cf.isActive = 1 
				AND cf.reportFieldTypeId = 1 
				ORDER BY cf.sequence 
		FOR XML PATH(''), TYPE
		).value('.', 'NVARCHAR(MAX)') 
	,1,1,'')


	SELECT @colsPivot = STUFF((SELECT ',' + QUOTENAME(cf.reportFieldDisplay) 
				FROM pal_ReportField cf 
				INNER JOIN pal_Report cr ON cr.recId = cf.reportId 
				INNER JOIN pal_SurfaceField csf ON csf.recId = cf.surfaceFieldId
				WHERE cr.name = 'Document Checklist' AND cf.isActive = 1 
				AND cf.reportFieldTypeId = 1
				ORDER BY cf.sequence 
		FOR XML PATH(''), TYPE
		).value('.', 'NVARCHAR(MAX)') 
	,1,1,'')


	set @query1 = N' 
	SELECT '''' AS [Status], ' + @cols +  '  FROM (
		SELECT sd.surfaceItemId as itemId, 
		ISNULL(CASE sf.surfaceFieldTypeId
			WHEN 3 Then CAST(sd.surfaceFieldValueChar AS VARCHAR(MAX))
			WHEN 4 Then CAST(sd.surfaceFieldValueNum AS VARCHAR(MAX))
			WHEN 5 THEN CAST(sd.surfaceFieldValueDecimal AS VARCHAR(MAX))
			WHEN 6 THEN  
					CASE WHEN LEN(CAST(sd.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
						THEN CAST(sd.surfaceFieldValueChar AS VARCHAR(MAX))
						ELSE (select display from pal_SurfaceLookup where pal_SurfaceLookup.recId = sd.surfaceFieldLookupID)
						END
			WHEN 7 Then CASE WHEN sf.surfaceDateTypeId = 2 THEN DATENAME(MONTH, sd.surfaceFieldValueDate) + '' '' + CONVERT(VARCHAR,DATEPART(YYYY, sd.surfaceFieldValueDate))
						ELSE CONVERT(VARCHAR, sd.surfaceFieldValueDate, 111) /* yyyy/mm/dd*/ END
			WHEN 8 THEN CAST(sd.surfaceFieldValueBool AS VARCHAR(MAX))									 
			WHEN 9 THEN  
					CASE WHEN LEN(CAST(sd.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
						THEN CAST(sd.surfaceFieldValueChar AS VARCHAR(MAX))
						ELSE (select display from pal_SurfaceLookup where pal_SurfaceLookup.recId = sd.surfaceFieldLookupID)
						END  '
	SET @query2 = N'
			WHEN 14 THEN
				CASE WHEN sf.actionType = 8
				THEN CASE sf.[action]
					WHEN 12
						THEN CAST(dbo.fn_GetSurfaceFieldAggregateAverage(si.recId,sf.actionValue,0) AS VARCHAR(MAX))
					WHEN 14
						THEN CASE WHEN sf.controlledValue = ''75'' THEN CAST(dbo.fn_GetSurfaceFieldAggregateDistinctionCount(si.recId,sf.actionValue, 0) AS VARCHAR(MAX)) 
												ELSE CAST(dbo.fn_GetSurfaceFieldAggregateDistinctionCount(si.recId,sf.actionValue, DEFAULT) AS VARCHAR(MAX)) END
					WHEN 15
						THEN CAST(dbo.fn_GetSurfaceFieldAggregateSum(si.recId,sf.actionValue) AS VARCHAR(MAX))
					WHEN 16
						THEN CAST(dbo.fn_GetSurfaceFieldAggregateDIFF(si.recId,sf.actionValue) AS VARCHAR(MAX))
					WHEN 17
						THEN CAST(dbo.fn_GetSurfaceFieldAggregateFailed(si.recId,sf.actionValue) AS VARCHAR(MAX))
					WHEN 18
						THEN CAST(dbo.fn_GetSurfaceFieldAggregatePassed(si.recId,sf.actionValue) AS VARCHAR(MAX))
					END
				WHEN sf.actionType = 1
				THEN CASE WHEN sf.[action] = 13
					THEN

					CASE WHEN ( CAST((SELECT	CASE F.surfaceFieldTypeId
									WHEN 4 THEN D.surfaceFieldValueNum 
									WHEN 5 THEN D.surfaceFieldValueDecimal  
									WHEN 14 THEN CASE WHEN F.actionType = 8
												THEN CASE WHEN F.[action] = 12
													THEN dbo.fn_GetSurfaceFieldAggregateAverage(D.surfaceItemId,F.actionValue,0) 
													END 
												END
											END
							FROM	pal_Surface S
									JOIN pal_SurfaceField F
										ON F.surfaceId = S.recId
										AND S.recId = ' + CAST(@primarySurfaceId AS VARCHAR(MAX)) + '
									JOIN pal_SurfaceFieldData D 
										ON D.surfaceFieldID = F.recId 
										AND D.surfaceId = F.surfaceId
										AND D.surfaceItemID = si.recId
							WHERE	F.recId = sf.actionSource) AS VARCHAR(100)) IS NULL)
						THEN '''' 
						ELSE
						CAST(DENSE_RANK() OVER
						(PARTITION BY
							(
							SELECT STUFF((SELECT '','' + D2.surfaceFieldValueChar
                            FROM pal_SurfaceField F2  
                            JOIN pal_SurfaceFieldData D2 ON D2.surfaceFieldID = F2.recId AND D2.surfaceItemId = si.recId
                            WHERE F2.surfaceId = sf.surfaceId 
                            AND F2.recId IN (SELECT CASE WHEN ISNUMERIC(splitdata) = 1 THEN splitdata ELSE 0 END FROM dbo.SplitString(sf.actionValue, ''|''))
                            FOR XML PATH(''''), TYPE
                                    ).value(''.'', ''VARCHAR(MAX)'') 
                                    ,1,1,'''')
							)	
						ORDER BY 
							(SELECT	CASE F.surfaceFieldTypeId
									WHEN 4 THEN D.surfaceFieldValueNum 
									WHEN 5 THEN D.surfaceFieldValueDecimal  
									WHEN 14 THEN CASE WHEN F.actionType = 8
												THEN CASE WHEN F.[action] = 12
													THEN dbo.fn_GetSurfaceFieldAggregateAverage(D.surfaceItemId,F.actionValue,0) 
													END 
												END
											END
							FROM	pal_Surface S
									JOIN pal_SurfaceField F
										ON F.surfaceId = S.recId
										AND S.recId = ' + CAST(@primarySurfaceId AS VARCHAR(MAX)) + '
									JOIN pal_SurfaceFieldData D 
										ON D.surfaceFieldID = F.recId 
										AND D.surfaceId = F.surfaceId
										AND D.surfaceItemID = si.recId
							WHERE	F.recId = sf.actionSource)	
						DESC)
						AS VARCHAR(MAX))	
						--CAST(dbo.fn_GetSurfaceFieldItemRanking(' + CAST(@primarySurfaceId AS VARCHAR(MAX)) + ',si.recId,sf.recId) AS VARCHAR(MAX))	
						END			
					WHEN sf.[action] = 2 --age
						THEN (SELECT CAST((CONVERT(int,CONVERT(char(8),GETDATE(),112))-CONVERT(char(8),DD.surfaceFieldValueDate,112))/10000  AS VARCHAR(10))
							FROM	pal_SurfaceFieldData DD
							WHERE	DD.surfaceItemId = si.recId
									AND DD.surfaceFieldID = sf.actionSource)	 '

	SET @query3 = N'
					WHEN sf.[action] = 7 -- divide
						THEN CAST(dbo.fn_GetSurfaceCalculationDivide(si.recId,sf.actionSource,sf.actionValue) AS VARCHAR(MAX))
					WHEN sf.[action] = 20 -- percentage
						THEN CAST(dbo.fn_GetSurfaceCalculationPercentage(si.recId,sf.actionSource,sf.actionValue) AS VARCHAR(MAX))
					ELSE 
						CASE WHEN LEN(CAST(sd.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
							THEN CAST(sd.surfaceFieldValueChar AS VARCHAR(MAX))
							ELSE CASE WHEN sd.surfaceFieldValueDecimal > 0
									THEN CAST(sd.surfaceFieldValueDecimal AS VARCHAR(MAX))
								END
						END
					END
				ELSE
				CASE WHEN LEN(CAST(sd.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
					THEN CAST(sd.surfaceFieldValueChar AS VARCHAR(MAX))
					ELSE CASE WHEN sd.surfaceFieldValueDecimal > 0
							THEN CAST(sd.surfaceFieldValueDecimal AS VARCHAR(MAX))
							END
						END
				END
			WHEN 15 THEN  convert(VARCHAR,sd.surfaceFieldValueTime,  114) /* hh:mm:ss:mmm(24h)*/									 
			WHEN 20 Then CAST(sd.surfaceFieldValueChar AS VARCHAR(MAX))
			WHEN 25 Then CAST(sd.surfaceFieldValueChar AS VARCHAR(MAX))
			WHEN 26 THEN 
				STUFF((
					SELECT CAST('', '' + display  AS VARCHAR(MAX))
					FROM pal_SurfaceLookup 
					WHERE recId IN (select SUBSTRING(splitdata, 0, CHARINDEX(''~R~'', splitdata)) from dbo.SplitString(sd.surfaceFieldValueChar, ''~|~''))
					FOR XML PATH (''''))
					, 1, 2, '''') 
			WHEN 29 THEN
				CASE sf.relationalSurface 
					WHEN ''date'' THEN (CASE WHEN YEAR(si.dateUpdated) = 1900 THEN CONVERT(VARCHAR, si.dateCreated, 120) ELSE CONVERT(VARCHAR, si.dateCreated, 120) + '' (last updated on '' + CONVERT(VARCHAR, si.dateUpdated, 120) + '')'' END)
					WHEN ''user'' THEN 
						CASE WHEN si.updatedBy = 0 THEN (SELECT userDisplay FROM [Framework_EVOL-1].dbo.pal_User WHERE recId =  si.createdBy) 
						ELSE (SELECT userDisplay FROM [Framework_EVOL-1].dbo.pal_User WHERE recId =  si.createdBy) + '' (last updated by '' + (SELECT userDisplay FROM [Framework_EVOL-1].dbo.pal_User WHERE recId =  si.updatedBy) + '')'' 
						END
				END
			WHEN 30 THEN 
				STUFF((
					SELECT CAST(''; '' + display  AS VARCHAR(MAX))
					FROM pal_SurfaceLookup 
					WHERE recId IN (select splitdata from dbo.SplitString(sd.surfaceFieldValueChar, '',''))
					FOR XML PATH (''''))
					, 1, 2, '''') 
		ELSE
		''''
		END, '''') [FieldValue],  
		f.reportFieldDisplay AS [FieldCode]
		FROM pal_ReportField f 
		INNER JOIN pal_Report r ON r.recId = f.reportId 
		INNER JOIN pal_SurfaceFieldData sd ON sd.surfaceFieldID = f.surfaceFieldId 
		INNER JOIN pal_SurfaceField sf ON sf.recId = f.surfaceFieldId 
		INNER join pal_SurfaceItem si on si.recId = sd.surfaceItemId
		WHERE r.name = ''Document Checklist'' 
		AND si.isActive = 1 AND si.isDeleted = 0
		AND f.reportFieldTypeId = 1
		AND f.surfaceId = ' + CAST(@primarySurfaceId AS VARCHAR(50))  + '

		UNION 

		SELECT pd.surfaceFieldValueNum as itemId, 
		ISNULL((
			SELECT CASE 
			WHEN SUM(NoResult) > 0 THEN ''No'' 
			WHEN SUM(FlagResult) > 0 THEN ''Flag''
			WHEN SUM(YesResult) > 0 THEN ''Yes'' 
			WHEN SUM(NAResult) > 0 THEN ''N/A'' 
			ELSE '''' END AS Result '

	SET @query4 = N'
			FROM (
				SELECT 
				CASE WHEN ChildValue = ''No'' THEN 1 ELSE 0 END AS NoResult, 
				CASE WHEN ChildValue = ''Flag'' THEN 1 ELSE 0 END AS FlagResult,
				CASE WHEN ChildValue = ''Yes'' THEN 1 ELSE 0 END AS YesResult, 
				CASE WHEN ChildValue = ''N/A'' THEN 1 ELSE 0 END AS NAResult
				FROM (
				SELECT ISNULL(CASE cf1.surfaceFieldTypeId
						WHEN 3 --Text
							Then CAST(cd1.surfaceFieldValueChar AS VARCHAR(MAX))
						WHEN 6 --Picklist
							THEN  (select display from pal_SurfaceLookup where pal_SurfaceLookup.recId = cd1.surfaceFieldLookupID)
						WHEN 9 THEN  --RadioButtonList
								(select display from pal_SurfaceLookup where pal_SurfaceLookup.recId = cd1.surfaceFieldLookupID)
						WHEN 26 THEN 
							STUFF((
								SELECT CAST('', '' + display  AS VARCHAR(MAX))
								FROM pal_SurfaceLookup 
								WHERE recId IN (select SUBSTRING(splitdata, 0, CHARINDEX(''~R~'', splitdata)) from dbo.SplitString(cd1.surfaceFieldValueChar, ''~|~''))
								FOR XML PATH (''''))
								, 1, 2, '''') 
					ELSE
					''''
					END, ''N/A'') AS ChildValue
				FROM pal_SurfaceFieldData cd1 
				LEFT OUTER JOIN pal_SurfaceField cf1 ON cf1.recId = cd1.surfaceFieldID 
				WHERE cf1.recId = f.surfaceFieldId 
				AND cd1.surfaceItemId IN (
					SELECT cd.surfaceItemId 
					FROM pal_SurfaceFieldData cd 
					LEFT OUTER JOIN pal_SurfaceField cf ON cf.recId = cd.surfaceFieldID 
					WHERE cf.surfaceFieldDisplay = ''ParentSurfaceItemId'' 
					AND cd.surfaceFieldValueNum = pd.surfaceFieldValueNum   
					AND cd.surfaceId = f.surfaceId) 
				) ChildResults
			) ChildSummary
		), '''') AS [FieldValue], 
		f.reportFieldDisplay AS [FieldCode]
		FROM pal_ReportField f  
		INNER JOIN pal_Report r ON r.recId = f.reportId 
		INNER JOIN pal_SurfaceField pf ON pf.surfaceId = f.surfaceId 
		INNER JOIN pal_SurfaceFieldData pd ON pd.surfaceId = pf.surfaceId AND pd.surfaceFieldID = pf.recId 
		INNER JOIN pal_SurfaceItem si ON si.recId = pd.surfaceFieldValueNum AND si.surfaceId = ' + CAST(@primarySurfaceId AS VARCHAR(50))  + '
		WHERE r.name = ''Document Checklist''
		AND f.reportFieldTypeId = 1 
		AND si.isActive = 1 AND si.isDeleted = 0 
		AND f.surfaceId <> ' + CAST(@primarySurfaceId AS VARCHAR(50))  + '
		AND pf.surfaceFieldDisplay = ''ParentSurfaceItemId'' 
	) DataTable
	PIVOT
	(
		MAX(FieldValue)
		FOR FieldCode
		IN 
			(
				' + @colsPivot + '
			)
	) PivotTable    '


	exec (@query1 + @query2 + @query3 + @query4) 
	--print @query1 
	--PRINT @query2 
	--PRINT @query3 
	--PRINT @query4 
	--print @cols
	--print @colsPivot




END
