
-- =============================================
-- Author:		Graham Rook
-- Create date: 21-10-2016
-- Description:	Fetch Surface App Contacts based on surface and mapped calendar fields 
-- Search based on Item IDs
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSurfaceCalendarUsersByIDs]
	@SurfaceID int,
	@NameField int,
	@SurnameField int,
	@EmailField int,
	@MobileField int,
	@ItemIDs as varchar(1000)
AS
BEGIN
	Declare @NameFieldCol As varchar(500)
		Declare @SurnameFieldCol As varchar(500)
		Declare @EmailFieldCol As varchar(500)
		Declare @MobileFieldCol As varchar(500)

		SET @NameFieldCol = (select surfaceFieldName from pal_SurfaceField WHERE recId = @NameField)
		SET @SurnameFieldCol = (select surfaceFieldName from pal_SurfaceField WHERE recId = @SurnameField)
		SET @EmailFieldCol = (select surfaceFieldName from pal_SurfaceField WHERE recId = @EmailField)
		SET @MobileFieldCol = (select surfaceFieldName from pal_SurfaceField WHERE recId = @MobileField)

		DECLARE @cols AS NVARCHAR(MAX),
		@query  AS NVARCHAR(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.recId IN (@NameField, @SurnameField,@EmailField, @MobileField)
                    group by sf.surfaceFieldName, sf.recId
					order by (
					case sf.recId  when @NameField then 1 when @SurnameField then 2 when @EmailField then 3 when @MobileField then 4 end)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

		set @query = N'SELECT
					*
					FROM
					(
						SELECT  item.recId AS [itemID], item.isActive, 
						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 (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 25 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, '''') 
							ELSE
							''''
						END, '''')  [FieldValue],  Replace(field.surfaceFieldName, '' '', '''') [FieldCode]
									
						FROM pal_SurfaceField field							
									
						INNER join pal_SurfaceFieldData data on data.surfaceFieldID = field.recId 
						INNER join pal_SurfaceItem item on item.recId = data.surfaceItemId
						WHERE field.isActive = 1 AND field.isGrid = 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)
						AND item.isDeleted = 0 
						ANd field.recId IN (' + CAST(@NameField AS VARCHAR(MAX)) + ',' + CAST(@SurnameField AS VARCHAR(MAX)) + ',' + CAST(@EmailField AS VARCHAR(MAX)) + ',' + CAST(@MobileField AS VARCHAR(MAX)) + ')						
						
						) DataTable
					PIVOT
					(
						MAX(FieldValue)
						FOR FieldCode
						IN 
							(
								' + @cols + '
							)
					) PivotTable
					WHERE itemID IN (' + @ItemIDs + ')
					'
					
		--print @query;
		exec sp_executesql @query;
END

GO


