/****** Object:  UserDefinedFunction [dbo].[FNCUST_GET_PROPERTY_VALUES]    Script Date: 17.10.2021 19:11:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- T-SQL Table function to get WinLine property values
-- Requires min. SQL Version 2017!

-- See examples at the bottom!

-- Stand: MK // 19.10.2021
-- 19.10.2021 Description expanded
-- 17.10.2021 @mesocomp, @mesoyear and @ResultType implemented
-- 06.10.2021 Initial

alter FUNCTION [dbo].[FNCUST_GET_PROPERTY_VALUES] (	
	@PropertyID			BIGINT,			--	= eg 1011 or 1012, see (select [c000], [c010] from [CWLSYSTEM].[dbo].[T069CMP] where [C003] = 0 and [c006] = 0 and [c000] > 1000)
	@ObjectID			VARCHAR(200),	--	= eg 10000002 = PersonalAccount; 4711 = ProductNr; 10i001 = PotentialBuyer
	@PropertyTypeID		BIGINT,			--  = eg 3 = PersonalAccount; 1 = ProductNr; 5 = PotentialBuyer
	@mesocomp			VARCHAR(4),		--  = OPTIONAL - Only valid in combination with @mesoyear
	@mesoyear			SMALLINT,		--  = OPTIONAL - Only valid in combination with @mesocomp 
	@ResultType			VARCHAR(20)		--  = OPTIONAL - Determ if Result should be compressed in one line, see examples
)
RETURNS @vTB_RESULT  TABLE	([MESOKEY]		  [BIGINT]  NOT NULL,
							 [PROPERTY_COUNT] [BIGINT]  NOT NULL,
							 [PROPERTY_VALUE] [VARCHAR](max) NULL)
AS
BEGIN

	DECLARE @PropertyCounter	BIGINT;
 
	----------------------------------------------------------------------------------------------------------------------------------

	-- IF optional params are null
	IF (@mesocomp is NULL) and (@mesoyear is NULL)

		BEGIN

			-- Count how many property values were found
			SELECT @PropertyCounter = count(CountFunction.C010) FROM (
	
				SELECT [CWLSYSTEM].[dbo].[t069cmp].[c010]

				FROM   [t070] (NOLOCK)
				JOIN   [CWLSYSTEM].[dbo].[t069cmp] (NOLOCK) ON [t070].[c002] = [t069cmp].[C001]

				WHERE  [CWLSYSTEM].[dbo].[t069cmp].[c000] = @PropertyID
				  and  [CWLSYSTEM].[dbo].[t069cmp].[C003] = 0	
				  and  [t070].[C000] = @ObjectID
				  and  [t070].[C003] = @PropertyTypeID

				GROUP BY [CWLSYSTEM].[dbo].[t069cmp].[c010]

			) as CountFunction

		END

	-- If optional params were set
	ELSE

		BEGIN

			-- Count how many property values were found and respect mesoyear and mesocomp
			SELECT @PropertyCounter = count(CountFunction.C010) FROM (
	
				SELECT [CWLSYSTEM].[dbo].[t069cmp].[c010]

				FROM   [t070] (NOLOCK)
				JOIN   [CWLSYSTEM].[dbo].[t069cmp] (NOLOCK) ON [t070].[c002] = [t069cmp].[C001]

				WHERE  [CWLSYSTEM].[dbo].[t069cmp].[c000] = @PropertyID
				  and  [CWLSYSTEM].[dbo].[t069cmp].[C003] = 0	
				  and  [t070].[C000] = @ObjectID
				  and  [t070].[C003] = @PropertyTypeID
				  and  [t070].[mesocomp] = @mesocomp
				  and  [t070].[mesoyear] = @mesoyear

				GROUP BY [CWLSYSTEM].[dbo].[t069cmp].[c010]

			) as CountFunction

		END

	----------------------------------------------------------------------------------------------------------------------------------

	IF (@PropertyCounter >= 1)

		BEGIN

			IF (@mesocomp is NULL) and (@mesoyear is NULL)

				BEGIN

					INSERT INTO @vTB_RESULT ([MESOKEY],[PROPERTY_COUNT],[PROPERTY_VALUE])

					SELECT [CWLSYSTEM].[dbo].[t069cmp].[MESOKEY],
						   @PropertyCounter,
						   [CWLSYSTEM].[dbo].[t069cmp].[c010]

					FROM   [t070] (NOLOCK)
					JOIN   [CWLSYSTEM].[dbo].[t069cmp] (NOLOCK) ON [t070].[c002] = [t069cmp].[C001]

					WHERE  [CWLSYSTEM].[dbo].[t069cmp].[c000] = @PropertyID
					  and  [CWLSYSTEM].[dbo].[t069cmp].[C003] = 0	
					  and  [t070].[C000] = @ObjectID
					  and  [t070].[C003] = @PropertyTypeID

					GROUP BY [CWLSYSTEM].[dbo].[t069cmp].[mesokey],
							 [CWLSYSTEM].[dbo].[t069cmp].[c010]

				END

			ELSE

				BEGIN

					INSERT INTO @vTB_RESULT ([MESOKEY],[PROPERTY_COUNT],[PROPERTY_VALUE])

					SELECT [CWLSYSTEM].[dbo].[t069cmp].[MESOKEY],
						   @PropertyCounter,
						   [CWLSYSTEM].[dbo].[t069cmp].[c010]

					FROM   [t070] (NOLOCK)
					JOIN   [CWLSYSTEM].[dbo].[t069cmp] (NOLOCK) ON [t070].[c002] = [t069cmp].[C001]

					WHERE  [CWLSYSTEM].[dbo].[t069cmp].[c000] = @PropertyID
					  and  [CWLSYSTEM].[dbo].[t069cmp].[C003] = 0	
					  and  [t070].[C000] = @ObjectID
					  and  [t070].[C003] = @PropertyTypeID
					  and  [t070].[mesocomp] = @mesocomp
					  and  [t070].[mesoyear] = @mesoyear

					GROUP BY [CWLSYSTEM].[dbo].[t069cmp].[mesokey],
							 [CWLSYSTEM].[dbo].[t069cmp].[c010]

				END

		END

	ELSE

		BEGIN

			INSERT INTO @vTB_RESULT ([MESOKEY],[PROPERTY_COUNT],[PROPERTY_VALUE])

			SELECT 0, @PropertyCounter, NULL

		END
	
	----------------------------------------------------------------------------------------------------------------------------------

	-- IF Result should be in one Line, delete temp table and use STRING_AGG function
	IF (@ResultType is not NULL) and (@PropertyCounter > 1)

		BEGIN

			DECLARE		@vTB_RESULT_TEMP TABLE	([MESOKEY]		  [BIGINT]  NOT NULL,
												 [PROPERTY_COUNT] [BIGINT]  NOT NULL,
												 [PROPERTY_VALUE] [VARCHAR](max) NULL)
												 
			INSERT	 INTO @vTB_RESULT_TEMP ([MESOKEY],[PROPERTY_COUNT],[PROPERTY_VALUE])
			SELECT * FROM @vTB_RESULT

			DELETE   FROM @vTB_RESULT

			INSERT	 INTO @vTB_RESULT ([MESOKEY],[PROPERTY_COUNT],[PROPERTY_VALUE])
			SELECT	 9999999999, @PropertyCounter, (STRING_AGG(CONVERT(NVARCHAR(max), ISNULL(PROPERTY_VALUE,'')), ';')) FROM @vTB_RESULT_TEMP;

			RETURN;

		END

	RETURN;

END


-- ================ EXAMPLES ================

-- Get (minimum) 1 or n lines back
-- select * from [FNCUST_GET_PROPERTY_VALUES](1012,'10000002',3,null,null,null)

-- Get (always) 1 line back
-- select * from [FNCUST_GET_PROPERTY_VALUES](1012,'10000002',3,null,null,'not null')

-- ==========================================