USE [DD_ECM]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- TSQL Table function for "Kontierungstabelle"

-- MK // 24.02.2025
-- 24.02.2025 Initial

CREATE OR ALTER FUNCTION [dbo].[FNCUST_GET_INVOICE_POS] (	
		@pEMAIL_MESSAGE_ID				NVARCHAR(900),
		@pDATA_SEPERATOR				NVARCHAR(1)		= '~',
		@pDATA_SEPERATOR_TAX			NVARCHAR(3)		= ' - '
)
RETURNS @vTB_INVOICE_POS TABLE  ([GUID]						INT				IDENTITY(1,1)	NOT NULL,
								 [CONTENT_CONCAT]			NVARCHAR(max)					NOT NULL,
								 [ORDER_POSITION_ARTICLE]	NVARCHAR(50)					NULL,
								 [INVOICE_POSITION_ARTICLE]	NVARCHAR(50)					NULL)
AS
BEGIN

	-- declare new vars because of parameter sniffing
	DECLARE @MandatorShortName			NVARCHAR(5)			= 'SWE',
			@EMAIL_MESSAGE_ID			NVARCHAR(900)		= UPPER(LTRIM(RTRIM(@pEMAIL_MESSAGE_ID))),
			@DATA_SEPERATOR				NVARCHAR(1)			= UPPER(LTRIM(RTRIM(@pDATA_SEPERATOR))),
			@DATA_SEPERATOR_TAX			NVARCHAR(3)			= ISNULL(@pDATA_SEPERATOR_TAX,' - ');

	DECLARE @INVOICE_POS_COUNTER		INT,
			@REFERENCE_GUID				NVARCHAR(900),
			@ITEM_DESCRIPTION			NVARCHAR(900),
			@ITEM_VALUE					NVARCHAR(900),
			@GROUP_COUNTER				INT,
			@COUNTER					INT = 0,
			@ACCOUNTING					VARCHAR(900),
			@ARTICLE_NR_EXT				NVARCHAR(50),
			@POS_AMOUNT					DECIMAL(20,2),
			@POS_SUM_NETTO				DECIMAL(20,2),
			@POS_TAX_CODE				NVARCHAR(25),
			@POS_TAX_RATE				DECIMAL(20,2),
			@POS_SUM_BRUTTO				DECIMAL(20,2);

	DECLARE	@ARTICLE_NR					NVARCHAR(50),
			@ORDER_NR					NVARCHAR(50),
			@ORDER_NR_FALLBACK			NVARCHAR(50)		= 'EB9999999',			-- Dummy nr für Rechnungen ohne Bestellung
			@WORK_ORDER_NR				NVARCHAR(25),
			@WORK_ORDER_NR_FALLBACK		NVARCHAR(25)		= 'A-99999999',			-- Dummy nr für Lagerbestellungen
			@POS_TAX_CODE_NAV_VAT19		NVARCHAR(25)		= '19 - INL - 19.00%';	-- Standardwert aus NAV für 19% MwSt Inland 

	SET @INVOICE_POS_COUNTER = (SELECT (MAX([GROUP_COUNTER])-1) as 'INVOICE_POS_COUNTER' FROM [TBEDMI_ITEM_VALUE] (NOLOCK) WHERE [REFERENCE_GUID] = LTRIM(RTRIM(@EMAIL_MESSAGE_ID)))
	IF (@INVOICE_POS_COUNTER > 0) BEGIN
	
		SELECT @ORDER_NR = ITEM_VALUE
		FROM dbo.TBEDMI_ITEM_VALUE (NOLOCK)
		WHERE [SPEC_NAME]      = 'INVOICE_REFERENCE'
		AND   [REFERENCE_GUID] = @EMAIL_MESSAGE_ID; 

		DECLARE CURSOR_INVOICE_POS CURSOR
		LOCAL FAST_FORWARD FOR 
			SELECT DISTINCT [REFERENCE_GUID], [GROUP_COUNTER] 
			FROM   [TBEDMI_ITEM_VALUE] (NOLOCK)
			WHERE  [SPEC_NAME]      IN ('INVOICE_POSITION_ARTICLE','INVOICE_POSITION_AMOUNT','INVOICE_POSITION_TAX_AMOUNT','INVOICE_TAXPOS_TAX_RATE')
			AND    [GROUP_COUNTER]  > 1
			AND    [REFERENCE_GUID] = @EMAIL_MESSAGE_ID; 
			 -- OR [REFERENCE_GUID] = 'ERw202512091413384620257svcdigitaldata' -- TEST

		OPEN CURSOR_INVOICE_POS;
		FETCH NEXT FROM CURSOR_INVOICE_POS INTO @REFERENCE_GUID, @GROUP_COUNTER;
		WHILE @@FETCH_STATUS = 0
			BEGIN

				-----------------------------------------------------------------------------------------------------------------------------
				SET @COUNTER	 	 += 1
				SET @ACCOUNTING		  = NULL;
				SET @POS_AMOUNT		  = NULL;
				SET @POS_SUM_NETTO	  = NULL;
				SET @POS_TAX_CODE	  = NULL;
				SET @POS_SUM_BRUTTO   = NULL;

				SET @ARTICLE_NR		  = NULL;
				SET @WORK_ORDER_NR    = NULL;
				-----------------------------------------------------------------------------------------------------------------------------

				--==================================================-- PositionsArtikel --==================================================--
				SET @ARTICLE_NR_EXT		= ISNULL((SELECT CASE WHEN LEN(LTRIM(RTRIM(convert(nvarchar(200),[ITEM_VALUE])))) = 0
																THEN ''
																ELSE CAST([ITEM_VALUE] AS NVARCHAR(50))
															END as 'ITEM_VALUE'
										FROM	[TBEDMI_ITEM_VALUE] (NOLOCK) 
										WHERE	[REFERENCE_GUID]	= @REFERENCE_GUID 
										AND		[GROUP_COUNTER]		= @GROUP_COUNTER
										AND		[SPEC_NAME]			= 'INVOICE_POSITION_ARTICLE'),'');
				-----------------------------------------------------------------------------------------------------------------------------

				--===================================================-- PositionsMenge --===================================================--
				SET @POS_AMOUNT			= ISNULL((SELECT CASE WHEN LEN(LTRIM(RTRIM(convert(nvarchar(200),[ITEM_VALUE])))) = 0
																THEN 0
																ELSE CAST(REPLACE([ITEM_VALUE],',','.') AS DECIMAL(20,2))
															END as 'ITEM_VALUE'
										FROM	[TBEDMI_ITEM_VALUE] (NOLOCK) 
										WHERE	[REFERENCE_GUID]	= @REFERENCE_GUID 
										AND		[GROUP_COUNTER]		= @GROUP_COUNTER
										AND		[SPEC_NAME]			= 'INVOICE_POSITION_AMOUNT'),0);
				-----------------------------------------------------------------------------------------------------------------------------

				--===================================================-- PositionsSumme --===================================================--
				SET @POS_SUM_NETTO		= ISNULL((SELECT CASE WHEN LEN(LTRIM(RTRIM(convert(nvarchar(200),[ITEM_VALUE])))) = 0
																THEN 0
																ELSE CAST(REPLACE([ITEM_VALUE],',','.') AS DECIMAL(20,2))
															END as 'ITEM_VALUE'
										FROM	[TBEDMI_ITEM_VALUE] (NOLOCK) 
										WHERE	[REFERENCE_GUID]	= @REFERENCE_GUID 
										AND		[GROUP_COUNTER]		= @GROUP_COUNTER
										AND		[SPEC_NAME]			= 'INVOICE_POSITION_TAX_AMOUNT'),0);
				-----------------------------------------------------------------------------------------------------------------------------

				--=================================================-- PositionsSteuersatz --================================================--
				SET @POS_TAX_RATE		= ISNULL((SELECT CASE WHEN LEN(LTRIM(RTRIM(convert(NVARCHAR(200),[ITEM_VALUE])))) = 0
																THEN 0
																ELSE CAST(REPLACE([ITEM_VALUE],',','.') AS DECIMAL(20,2))
															END as 'ITEM_VALUE'
										FROM	[TBEDMI_ITEM_VALUE] (NOLOCK) 
										WHERE	[REFERENCE_GUID]	= @REFERENCE_GUID 
										AND		[GROUP_COUNTER]		= @GROUP_COUNTER
										AND		[SPEC_NAME]			= 'INVOICE_TAXPOS_TAX_RATE'),0);

				SET @POS_TAX_CODE = CASE WHEN @POS_TAX_RATE = 19.00
										 THEN @POS_TAX_CODE_NAV_VAT19

										 ELSE '0'

									END;
				-----------------------------------------------------------------------------------------------------------------------------

				--======================================================-- ERP Daten --=====================================================--
				SELECT TOP 1	@ARTICLE_NR		= [No_],
								@WORK_ORDER_NR	= [Work Order No_]
				FROM   [dbo].[FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS](@MandatorShortName,@ORDER_NR,@DATA_SEPERATOR)
				WHERE  [GUID] = @COUNTER
				AND   ([Description] IS NOT NULL OR [Description 2] IS NOT NULL)
				OR     (UPPER([Description])   =          @ARTICLE_NR_EXT 
				OR      UPPER([Description])   like '%' + @ARTICLE_NR_EXT + '%'
				OR      UPPER([Description 2]) =          @ARTICLE_NR_EXT 
				OR      UPPER([Description 2]) like '%' + @ARTICLE_NR_EXT + '%');
				--TEST: select * from [FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS]('SWE','EB00017529','~')


				SELECT TOP 1	@POS_TAX_RATE	= [VAT _] 
				FROM   [dbo].[FNCUST_GET_MS-DYN365BC_VAT_LIST](@MandatorShortName,@ORDER_NR,@DATA_SEPERATOR_TAX)
				WHERE  [CONTENT_CONCAT] = @POS_TAX_CODE;
				SET @POS_TAX_RATE = ISNULL(@POS_TAX_RATE,0);  -- Failsafe
				-----------------------------------------------------------------------------------------------------------------------------
				
				-----------------------------------------------------------------------------------------------------------------
				SET @POS_SUM_BRUTTO = @POS_SUM_NETTO * (100.00 + @POS_TAX_RATE) / 100;
				SET @ACCOUNTING		= concat(@COUNTER,											@DATA_SEPERATOR,
				                             ISNULL(@WORK_ORDER_NR,@WORK_ORDER_NR_FALLBACK),	@DATA_SEPERATOR,
											 NULL,												@DATA_SEPERATOR,
											 REPLACE(@POS_AMOUNT,',','.'),						@DATA_SEPERATOR,
											 REPLACE(@POS_SUM_NETTO,',','.'),					@DATA_SEPERATOR,
											 @POS_TAX_CODE,										@DATA_SEPERATOR,
											 REPLACE(@POS_SUM_BRUTTO,',','.'),					@DATA_SEPERATOR,
											 'false',											@DATA_SEPERATOR,
											 @ARTICLE_NR);
	
				IF ((@POS_AMOUNT > 0) AND (@POS_SUM_NETTO > 0)) BEGIN

					INSERT INTO @vTB_INVOICE_POS([CONTENT_CONCAT],[ORDER_POSITION_ARTICLE],[INVOICE_POSITION_ARTICLE]) 
					VALUES                      (@ACCOUNTING,     @ARTICLE_NR,             @ARTICLE_NR_EXT)

				END;
				-----------------------------------------------------------------------------------------------------------------

				FETCH NEXT FROM CURSOR_INVOICE_POS INTO @REFERENCE_GUID, @GROUP_COUNTER;
			END;
		CLOSE CURSOR_INVOICE_POS;
		DEALLOCATE CURSOR_INVOICE_POS;

	END;

	RETURN;

END;

/***
-- Test

	SELECT * FROM [FNCUST_GET_INVOICE_POS]('ERw202603031419486748168lfelsburg','~',' - ')  --WHERE 
	SELECT * FROM [FNCUST_GET_INVOICE_POS]('ERw202603121044356088460lfelsburg','~',' - ')  --WHERE 

***/
GO