
/****** Object:  StoredProcedure [dbo].[sp_INDE-6_GetSurfaceModulePicklist]    Script Date: 2020/06/01 14:59:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_INDE-6_GetSurfaceModulePicklist] 
	@surfaceFieldId INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @IsPublished AS BIT

	SET @IsPublished = (SELECT isPublished FROM dbo.pal_Surface WHERE recId = (SELECT surfaceId FROM dbo.pal_SurfaceField WHERE recId = @surfaceFieldId))

	IF @IsPublished = 1
		BEGIN
			DECLARE @surf AS VARCHAR(200)
			DECLARE @fieldName AS VARCHAR(500)
			SET @surf = (SELECT 'publ_' + name FROM dbo.pal_Surface WHERE recId = (SELECT surfaceId FROM dbo.pal_SurfaceField WHERE recId = @surfaceFieldId))
			SET @fieldName = (SELECT surfaceFieldName FROM dbo.pal_SurfaceField WHERE recId = @surfaceFieldId)
		
			Declare @Query as Varchar(Max)
			SET @Query = 'SELECT ' + @fieldName + ' as DataText, itemID as DataValue FROM ' + @surf + ' WHERE  ' + @fieldName + ' <> '''' AND isActive = 1		
				ORDER BY
				CASE WHEN LEN(' + @fieldName + ') = 3 AND CHARINDEX(''.'', ' + @fieldName + ') = 2 THEN SUBSTRING(' + @fieldName + ',1,2) + ''0'' + SUBSTRING(' + @fieldName + ',3,1) 
					WHEN CHARINDEX(''.'', ' + @fieldName + ') = 3 AND LEN(' + @fieldName + ') = 4 THEN SUBSTRING(' + @fieldName + ',1,3) + ''0'' + SUBSTRING(' + @fieldName + ',4,1) 
					ELSE
					CASE WHEN ISNUMERIC(Replace(' + @fieldName + ', ''.'', '''')) = 0 
						THEN SUBSTRING(' + @fieldName + ',1,2) + ''0'' + SUBSTRING(' + @fieldName + ',3,2) 
						ELSE 
						' + @fieldName + ' 
					END 
				END '
			EXECUTE(@Query)

		END
	ELSE 
	BEGIN
		SELECT * FROM
		(
		SELECT DISTINCT(d.surfaceFieldValueChar) as DataText, i.recId as DataValue 
		FROM pal_SurfaceItem i
		INNER JOIN pal_SurfaceFieldData d ON d.surfaceItemId = i.recId
		WHERE d.surfaceFieldID = @surfaceFieldId 
		AND i.isActive = 1 AND i.isDeleted = 0) AS TBL
		ORDER BY
		CASE WHEN LEN(DataText) = 3 AND CHARINDEX('.', DataText) = 2 THEN SUBSTRING(DataText,1,2) + '0' + SUBSTRING(DataText,3,1) 
			WHEN CHARINDEX('.', DataText) = 3 AND LEN(DataText) = 4 THEN SUBSTRING(DataText,1,3) + '0' + SUBSTRING(DataText,4,1) 
			ELSE
			CASE WHEN ISNUMERIC(Replace(DataText, '.', '')) = 0 
				THEN SUBSTRING(DataText,1,2) + '0' + SUBSTRING(DataText,3,2) 
				ELSE 
				DataText 
			END 
		END 
	END
	
END
GO


