CREATE PROCEDURE [dbo].[sp_GetChildSurfaceData]
	@SurfaceID int,
	@ParentSurfaceItemId int,
	@RowLimit INT = 0,
	@GridOnly bit = 1
AS
BEGIN
	---- For Testing:
	--DECLARE
	--@SurfaceID int= 3066,
	--@ParentSurfaceItemId int=13503,
	--@RowLimit INT = 0,
	--@GridOnly bit = 1
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

		DECLARE @cols AS NVARCHAR(MAX),
		@query  AS VARCHAR(MAX),
		@query2 AS VARCHAR(MAX)

		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,'')

		--CVH 2017-03-22 If it's a linked surface, filter on pal_SurfaceItem.surfaceLinkItems, not ParentSurfaceItemId
		--JR 2017-04-02 don't join items. irrelevent to determine linked surface
		DECLARE @isLinkedSurface BIT;
		IF EXISTS(
			SELECT 1 
			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(@RowLimit > 0)
		BEGIN 
			SET @query = N'SELECT TOP '+CAST(@RowLimit AS VARCHAR)+' '
		END
		ELSE
		BEGIN
			SET @query = N'SELECT '
		END

		set @query += N' * 
					FROM
					(
						SELECT  item.recId AS [itemID], item.isActive, ISNULL(item.isWizardCompleted,0) 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 SL.display --(select display from pal_SurfaceLookup where pal_SurfaceLookup.recId = data.surfaceFieldLookupID)
											END
								WHEN 7 Then
										CASE field.surfaceDateTypeId
											WHEN 2 THEN DATENAME(MONTH, data.surfaceFieldValueDate) + '' '' + CONVERT(VARCHAR,DATEPART(YYYY, data.surfaceFieldValueDate))
											WHEN 3 THEN 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 SL.display --(select display from pal_SurfaceLookup where pal_SurfaceLookup.recId = data.surfaceFieldLookupID)
											END
								WHEN 14 THEN
										CASE WHEN field.actionType = 8
										THEN CASE field.[action]
											WHEN 12
												THEN CAST(dbo.fn_GetSurfaceFieldAggregateAverage(item.recId,field.actionValue,0) AS VARCHAR(MAX))
											WHEN 14
												THEN CAST(dbo.fn_GetSurfaceFieldAggregateDistinctionCount(item.recId,field.actionValue) AS VARCHAR(MAX))
											WHEN 15
												THEN CAST(dbo.fn_GetSurfaceFieldAggregateSum(item.recId,field.actionValue) AS VARCHAR(MAX))
											WHEN 16
												THEN CAST(dbo.fn_GetSurfaceFieldAggregateDIFF(item.recId,field.actionValue) AS VARCHAR(MAX))
											WHEN 17
												THEN CAST(dbo.fn_GetSurfaceFieldAggregateFailed(item.recId,field.actionValue) AS VARCHAR(MAX))
											WHEN 18
												THEN CAST(dbo.fn_GetSurfaceFieldAggregatePassed(item.recId,field.actionValue) AS VARCHAR(MAX))
											END
										WHEN field.actionType = 1
										THEN CASE WHEN field.[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(@SurfaceID AS VARCHAR(MAX)) + '
														JOIN pal_SurfaceFieldData D 
															ON D.surfaceFieldID = F.recId 
															AND D.surfaceId = F.surfaceId
															AND D.surfaceItemID = item.recId
												WHERE	F.recId = field.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 = item.recId
                                                WHERE F2.surfaceId = field.surfaceId 
                                                AND F2.recId IN (SELECT CASE WHEN ISNUMERIC(splitdata) = 1 THEN splitdata ELSE 0 END FROM dbo.SplitString(field.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(@SurfaceID AS VARCHAR(MAX)) + '
														JOIN pal_SurfaceFieldData D 
															ON D.surfaceFieldID = F.recId 
															AND D.surfaceId = F.surfaceId
															AND D.surfaceItemID = item.recId
												WHERE	F.recId = field.actionSource)	
											DESC)
											AS VARCHAR(MAX))	
											--CAST(dbo.fn_GetSurfaceFieldItemRanking(' + CAST(@SurfaceID AS VARCHAR(MAX)) + ',item.recId,field.recId) AS VARCHAR(MAX))	
											END		
											WHEN field.[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 = item.recId
															AND DD.surfaceFieldID = field.actionSource)				
											WHEN field.[action] = 7 -- divide
												THEN CAST(dbo.fn_GetSurfaceCalculationDivide(item.recId,field.actionSource,field.actionValue) AS VARCHAR(MAX))
											WHEN field.[action] = 20 -- percentage
												THEN CAST(dbo.fn_GetSurfaceCalculationPercentage(item.recId,field.actionSource,field.actionValue) AS VARCHAR(MAX))
											ELSE 
												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
											END
										ELSE
										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
										END
								WHEN 15 THEN  convert(VARCHAR,data.surfaceFieldValueTime,  114) /* hh:mm:ss:mmm(24h)*/									 
								WHEN 20 Then CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))
								WHEN 25 Then CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))
								WHEN 26 THEN 
									STUFF((
									SELECT CAST('', '' + display  AS VARCHAR(MAX))
									FROM pal_SurfaceLookup L
									CROSS APPLY (SELECT SUBSTRING(splitdata, 0, CHARINDEX(''~R~'', splitdata)) val FROM dbo.SplitString(data.surfaceFieldValueChar, ''~|~'')) Y
									WHERE recId = Y.val
									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 UC.userDisplay 
											ELSE UC.userDisplay + '' (last updated by '' + UU.userDisplay + '')'' 
											END
										ELSE
											CAST(data.surfaceFieldValueChar AS VARCHAR(MAX))
									END
								WHEN 30 THEN 
									STUFF((
										SELECT CAST(''; '' + display  AS VARCHAR(MAX))
										FROM pal_SurfaceLookup L
										CROSS APPLY (SELECT splitdata FROM dbo.SplitString(data.surfaceFieldValueChar, '','')) X
										WHERE recId = X.splitdata
										FOR XML PATH (''''))
										, 1, 2, '''') 
							ELSE
							''''
						END, '''')  [FieldValue],  Replace(field.surfaceFieldName, '' '', '''') [FieldCode]
						FROM	pal_Surface S
								JOIN pal_SurfaceItem item WITH(INDEX(IX_SurfaceItem_SurfaceID))
									ON item.surfaceId = S.recId	
								JOIN pal_SurfaceField field  
									ON S.recId = field.surfaceId						
								JOIN pal_SurfaceFieldData data WITH(NOLOCK)
									ON data.surfaceFieldID = field.recId 
									AND data.surfaceId = field.surfaceId
									AND data.surfaceItemId = item.recId
								JOIN pal_SurfaceGridOptions G ON G.surfaceId = S.recId
								LEFT JOIN pal_vUserShared UL 
									ON UL.recId = item.userLink
								LEFT JOIN pal_vUserShared UC 
									ON UC.recId = item.createdBy
								LEFT JOIN pal_vUserShared UU 
									ON UU.recId = item.updatedBy
								LEFT JOIN pal_SurfaceLookup SL 
									ON SL.recId = data.surfaceFieldLookupID			
						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    '

						IF (@isLinkedSurface = 1)
						BEGIN
							SET @query += N' AND ' + CAST(@ParentSurfaceItemId AS VARCHAR) + ' IN (select splitdata from dbo.SplitString(item.surfaceLinkItems, '',''))  '
						END

		SET @query2 = 
					N' UNION 

					SELECT  item.recId AS [itemID], item.isActive, ISNULL(item.isWizardCompleted,0) 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 = 4
					AND item.isDeleted = 0
					'
			--JR 2017-04-02 if linkedsurface and parent new (0), exclude ParentSurfaceItemId
			IF(@isLinkedSurface = 1 AND @ParentSurfaceItemId = 0)
			BEGIN
				SET @query2 += ' AND data.surfaceFieldValueNum <> ' + CAST(@ParentSurfaceItemId AS VARCHAR) + ''
			END
			ELSE
			BEGIN
				SET @query2 += ' AND data.surfaceFieldValueNum = ' + CAST(@ParentSurfaceItemId AS VARCHAR) + ''
			END

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

		IF (@isLinkedSurface = 0)
			SET @query2 += N' Where [ParentSurfaceItemId] IS NOT NULL AND ISNULL([ParentSurfaceItemId], 0) = ' + CAST(@ParentSurfaceItemId AS VARCHAR) + '' 

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

	print @query;
	print @query2;
	exec (@query+@query2);
END
