CREATE PROCEDURE [dbo].[sp_GetSurfaceItemData]
	@SurfaceID int
	,@SurfaceItemID int
AS
BEGIN
	--testing
	--DECLARE @SurfaceID int = 3038
	--		,@SurfaceItemID int= 12052
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

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

		IF @SurfaceID = 0
		  BEGIN
		  SET @SurfaceID = (Select SurfaceId from pal_SurfaceItem Where recId = @SurfaceItemID)
		  END

		select @cols = STUFF((SELECT ',' + QUOTENAME(surfaceFieldName) 
                    from pal_SurfaceField sf 
					where sf.surfaceFieldTypeId IN (3,4,5,6,7,8,9,14,15,20,25,26,29,30)
						AND sf.surfaceId = @SurfaceID
                    group by sf.surfaceFieldName, sf.recId
                    order by sf.surfaceFieldName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

		
		set @query = N'SELECT
					*
					FROM
					(
						SELECT  item.recId AS [itemID],
						field.surfaceId,
						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 ISNULL((SELECT RankValue FROM (
												SELECT dr.surfaceItemId as RankItemId,                                                          
												CASE WHEN ( CAST((SELECT   CASE fr1.surfaceFieldTypeId
																		WHEN 4 THEN dr1.surfaceFieldValueNum 
																		WHEN 5 THEN dr1.surfaceFieldValueDecimal  
																		WHEN 14 THEN CASE WHEN fr1.actionType = 8
																							THEN CASE WHEN fr1.[action] = 12
																									THEN dbo.fn_GetSurfaceFieldAggregateAverage(dr1.surfaceItemId,fr1.actionValue,0) 
																									END 
																							END
																					END
														FROM   pal_Surface sr1
																		JOIN pal_SurfaceField fr1
																			ON fr1.surfaceId = sr1.recId
																			AND sr1.recId = fr.surfaceId
																		JOIN pal_SurfaceFieldData dr1 
																			ON dr1.surfaceFieldID = fr1.recId 
																			AND dr1.surfaceId = fr1.surfaceId
																			AND dr1.surfaceItemID = dr.surfaceItemId
														WHERE  fr1.recId = fr.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 = dr.surfaceItemId
														WHERE F2.surfaceId = fr.surfaceId 
														AND F2.recId IN (SELECT CASE WHEN ISNUMERIC(splitdata) = 1 THEN splitdata ELSE 0 END FROM dbo.SplitString(fr.actionValue, ''|''))
														FOR XML PATH(''''), TYPE
																).value(''.'', ''VARCHAR(MAX)'') 
																,1,1,'''')
														)      
													ORDER BY 
														(SELECT       CASE fr2.surfaceFieldTypeId
																		WHEN 4 THEN dr2.surfaceFieldValueNum 
																		WHEN 5 THEN dr2.surfaceFieldValueDecimal  
																		WHEN 14 THEN CASE WHEN fr2.actionType = 8
																							THEN CASE WHEN fr2.[action] = 12
																									THEN dbo.fn_GetSurfaceFieldAggregateAverage(dr2.surfaceItemId,fr2.actionValue,0) 
																									END 
																							END
																					END
														FROM   pal_Surface sr2
																		JOIN pal_SurfaceField fr2
																			ON fr2.surfaceId = sr2.recId
																			AND sr2.recId = fr.surfaceId
																		JOIN pal_SurfaceFieldData dr2 
																			ON dr2.surfaceFieldID = fr2.recId 
																			AND dr2.surfaceId = fr2.surfaceId
																			AND dr2.surfaceItemID = dr.surfaceItemId
														WHERE  fr2.recId = fr.actionSource)  
													DESC)
													AS VARCHAR(MAX))     
													--CAST(dbo.fn_GetSurfaceFieldItemRanking(3046,item.recId,field.recId) AS VARCHAR(MAX))                                                                                 
													END AS RankValue    
												FROM   pal_SurfaceField fr
												LEFT JOIN pal_SurfaceFieldData dr WITH(NOLOCK)
													ON dr.surfaceFieldID = fr.recId AND dr.surfaceId = fr.surfaceId
												WHERE fr.recId = field.recId   
											) RankResult 
											WHERE RankItemId = item.recId
										), '''')
										
										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 = SUBSTRING(Y.val, 0, CHARINDEX(''~R~'', 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 data.surfaceId = ' + CAST(@SurfaceID AS VARCHAR(MAX)) + ' 
						AND field.surfaceFieldTypeId IN (3,4,5,6,7,8,9,14,15,20,25,26,29,30)
						AND item.recId = '+CAST(@SurfaceItemID AS VARCHAR(MAX))+' 
						) DataTable
					PIVOT
					(
						MAX(FieldValue)
						FOR FieldCode
						IN 
							(
								' + @cols + '
							)
					) PivotTable'

		exec sp_executesql @query;

END

