-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2017-02-28
-- Description:	Calculates summarized values on a child grid, where the child grid exists one level down from the summary child grid
--			  : Default ItemID, isActive, isComplete to 1. Grid won't be editable, and need to group by these values. 
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetChildSurfaceDataSummarized]
	@SurfaceID int,
	@ParentSurfaceItemId int,
	@RowLimit INT = 0,
	@GridOnly bit = 1
AS
BEGIN	

	SET NOCOUNT ON;

		DECLARE @cols AS NVARCHAR(MAX), @selectColSum AS NVARCHAR(MAX), @groupByCols AS NVARCHAR(MAX),
		@query  AS NVARCHAR(MAX),
		@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 WITH(NOLOCK)
			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,'')

		select @cols = STUFF((SELECT ',' + QUOTENAME(surfaceFieldName) 
                    from pal_SurfaceField sf where sf.surfaceFieldTypeId <> 1 AND sf.surfaceFieldTypeId <> 2 and sf.surfaceId = @SurfaceID
					and sf.surfaceFieldDisplay <> 'ParentSurfaceItemId' 
                    group by sf.surfaceFieldName, sf.recId
                    order by sf.surfaceFieldName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

		select @groupByCols = STUFF((SELECT ',' + QUOTENAME(surfaceFieldName) 
                    from pal_SurfaceField sf where sf.surfaceFieldTypeId NOT IN (1,2,4,5) and sf.surfaceId = @SurfaceID 
					and sf.surfaceFieldDisplay <> 'ParentSurfaceItemId' 
                    group by sf.surfaceFieldName, sf.recId
                    order by sf.surfaceFieldName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

		select @selectColSum = 
					CASE WHEN (LEN(@groupByCols) > 0) THEN @groupByCols + ', ' ELSE ' ' END
					+ STUFF((SELECT ',SUM(CONVERT(DECIMAL(18,0),' + surfaceFieldName + ')) AS ' + QUOTENAME(surfaceFieldName) + ' '
                    from pal_SurfaceField sf where sf.surfaceFieldTypeId IN (4, 5) and sf.surfaceId = @SurfaceID
					and sf.surfaceFieldDisplay <> 'ParentSurfaceItemId' 
                    group by sf.surfaceFieldName, sf.recId
                    order by sf.surfaceFieldName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

		IF(@RowLimit > 0)
		BEGIN 
			SET @query = N'SELECT TOP '+CAST(@RowLimit AS VARCHAR)+' '
		END
		ELSE
		BEGIN
			SET @query = N'SELECT '
		END

		IF (LEN(@selectColSum) > 0)
		BEGIN
			set @query += CONVERT(VARCHAR(50), @ParentSurfaceItemId) + ' AS ParentSurfaceItemId, 1 AS itemID, isActive, isComplete, surfaceId, linkUser, showWizardStatus, allowEdit, editAccessType, allowView, viewAccessType, allowRemove, removeAccessType, allowClone, cloneAccessType, ' + @selectColSum + ' '
		END
		ELSE
		BEGIN
			set @query += ' * '
		END
		
		set @query += N' 
					FROM  
					(    
						SELECT item.recId AS [tempID],  CONVERT(BIT,1) AS isActive, CONVERT(BIT,1) AS isComplete,
						field.surfaceId, S.linkUser,						
						ISNULL(G.showWizardStatus,0) showWizardStatus,
						G.allowEdit, ISNULL(G.editAccessType,1)editAccessType,
						G.allowView, ISNULL(G.viewAccessType,1)viewAccessType,
						ISNULL(G.allowRemove,0) allowRemove, ISNULL(G.removeAccessType,1)removeAccessType,
						ISNULL(G.allowClone, 0) allowClone, ISNULL(G.cloneAccessType,1)cloneAccessType,
						ISNULL(CASE field.surfaceFieldTypeId
								WHEN 3 Then CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))
								WHEN 4 Then CAST(data.surfaceFieldValueNum AS VARCHAR(MAX))
								WHEN 5 THEN CAST(data.surfaceFieldValueDecimal AS VARCHAR(MAX))
								WHEN 6 THEN  
										CASE WHEN LEN(CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
											THEN CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))
											ELSE (select display from pal_SurfaceLookup where pal_SurfaceLookup.recId = data.surfaceFieldLookupID)
											END
								WHEN 7 Then CASE WHEN field.surfaceDateTypeId = 2 THEN DATENAME(MONTH, data.surfaceFieldValueDate) + '' '' + CONVERT(VARCHAR,DATEPART(YYYY, data.surfaceFieldValueDate))
											ELSE CONVERT(VARCHAR, data.surfaceFieldValueDate, 111) /* yyyy/mm/dd*/ END
								WHEN 8 THEN CAST(data.surfaceFieldValueBool AS VARCHAR(MAX))									 
								WHEN 9 THEN  
										CASE WHEN LEN(CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
											THEN CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))
											ELSE (select display from pal_SurfaceLookup where pal_SurfaceLookup.recId = data.surfaceFieldLookupID)
											END
								WHEN 14 THEN
										CASE WHEN LEN(CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))) > 0
											THEN CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))
											ELSE CASE WHEN data.surfaceFieldValueDecimal > 0
													THEN CAST(data.surfaceFieldValueDecimal AS VARCHAR(MAX))
													END
												END
								WHEN 15 THEN  convert(VARCHAR,data.surfaceFieldValueTime,  114) /* hh:mm:ss:mmm(24h)*/									 
								WHEN 20 Then CAST(data.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(data.surfaceFieldValueChar, ''~|~''))
										FOR XML PATH (''''))
										, 1, 2, '''') 
								WHEN 29 THEN
									CASE field.relationalSurface 
										WHEN ''date'' THEN (CASE WHEN YEAR(item.dateUpdated) = 1900 THEN CONVERT(VARCHAR, item.dateCreated, 120) ELSE CONVERT(VARCHAR, item.dateCreated, 120) + '' (last updated on '' + CONVERT(VARCHAR, item.dateUpdated, 120) + '')'' END)
										WHEN ''user'' THEN 
											CASE WHEN item.updatedBy = 0 THEN (SELECT userDisplay FROM [Framework_EVOL-1].dbo.pal_User WHERE recId =  item.createdBy) 
											ELSE (SELECT userDisplay FROM [Framework_EVOL-1].dbo.pal_User WHERE recId =  item.createdBy) + '' (last updated by '' + (SELECT userDisplay FROM [Framework_EVOL-1].dbo.pal_User WHERE recId =  item.updatedBy) + '')'' 
											END
									END
								WHEN 30 THEN 
									STUFF((
										SELECT CAST(''; '' + display  AS VARCHAR(MAX))
										FROM pal_SurfaceLookup 
										WHERE recId IN (select splitdata from dbo.SplitString(data.surfaceFieldValueChar, '',''))
										FOR XML PATH (''''))
										, 1, 2, '''') 
							ELSE
							''''
						END, '''')  [FieldValue],  Replace(field.surfaceFieldName, '' '', '''') [FieldCode]
									
						FROM pal_SurfaceField field							
						JOIN pal_Surface S ON S.recId = field.surfaceId			
						INNER join pal_SurfaceFieldData data WITH(NOLOCK) on data.surfaceFieldID = field.recId 
						INNER join pal_SurfaceItem item on item.recId = data.surfaceItemId
						LEFT JOIN pal_SurfaceGridOptions G ON G.surfaceId = field.surfaceId
						WHERE field.isActive = 1 AND field.isGrid = ' + CAST(@GridOnly AS VARCHAR) + ' AND data.surfaceId = ' + CAST(@SurfaceID AS VARCHAR(MAX)) + ' 
						AND field.surfaceFieldTypeId IN (3,4,5,6,7,8,9,14,15,20,26,29,30)
						AND item.isDeleted = 0

						UNION 

						SELECT  item.recId AS [tempID], CONVERT(BIT,1) AS isActive, CONVERT(BIT,1) AS isComplete,
						field.surfaceId, S.linkUser,
						ISNULL(G.showWizardStatus,0) showWizardStatus,
						G.allowEdit, ISNULL(G.editAccessType,1)editAccessType,
						G.allowView, ISNULL(G.viewAccessType,1)viewAccessType,
						G.allowRemove, ISNULL(G.removeAccessType,1)removeAccessType,
						ISNULL(G.allowClone,0) allowClone, ISNULL(G.cloneAccessType,1)cloneAccessType,
						ISNULL(CAST(data.surfaceFieldValueNum AS VARCHAR), '''')  [FieldValue],  
						''ParentSurfaceItemId'' [FieldCode]
									
						FROM pal_SurfaceField field
						JOIN pal_Surface S ON S.recId = field.surfaceId	
						INNER join pal_SurfaceFieldData data WITH(NOLOCK) on data.surfaceFieldID = field.recId 
						INNER join pal_SurfaceItem item on item.recId = data.surfaceItemId
						LEFT JOIN pal_SurfaceGridOptions G ON G.surfaceId = field.surfaceId
						WHERE field.surfaceFieldDisplay = ''ParentSurfaceItemId''
						AND data.surfaceId = ' + CAST(@SurfaceID AS VARCHAR) + ' 
						AND field.surfaceFieldTypeId IN (4)
						AND item.isDeleted = 0


						) DataTable
					PIVOT
					(
						MAX(FieldValue)
						FOR FieldCode
						IN 
							(
								' + @cols + ', ParentSurfaceItemId
							)
					) PivotTable

		Where [ParentSurfaceItemId] IS NOT NULL AND ISNULL([ParentSurfaceItemId], 0) IN (' + ISNULL(@childParentItemIds, '-1') + ') '

		IF (LEN(@selectColSum) > 0)
		BEGIN
			IF (LEN(@groupByCols) > 0)
			BEGIN
				SET @query += N' GROUP BY isActive, isComplete, surfaceId, linkUser, showWizardStatus, allowEdit, editAccessType, allowView, viewAccessType, allowRemove, removeAccessType, allowClone, cloneAccessType, ' + ISNULL(@groupByCols, '') + ' '
			END 
			ELSE 
			BEGIN
				SET @query += N' GROUP BY isActive, isComplete, surfaceId, linkUser, showWizardStatus, allowEdit, editAccessType, allowView, viewAccessType, allowRemove, removeAccessType, allowClone, cloneAccessType '
			END
		END

		IF(@RowLimit > 0)
		BEGIN 
			SET @query += N' ORDER BY 1 DESC '
		END

--print @query;
		exec sp_executesql @query;
END

