USE [CAPDB_CORP_SIT]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetDataAdditionalExplanationFromListTopic]    Script Date: 4/6/2021 2:45:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Puwanat>
-- Create date: <19/02/2563>
-- Description:	<คำอธิบายเพิ่มเติม>
-- =============================================
ALTER    PROCEDURE [dbo].[usp_GetDataAdditionalExplanationFromListTopic] 
	@movmntID			NVARCHAR(50)	= NULL,
	@topicCode			NVARCHAR(50)	= NULL,
	@accTypCde			NVARCHAR(50)	= NULL,
	@movAccTypId		NVARCHAR(200)	= NULL
AS  
BEGIN 
DECLARE @EmptyGuid			UNIQUEIDENTIFIER	= 0x0  
DECLARE @txtDefault			NVARCHAR(500)		= ''
DECLARE @frofitDefault		NVARCHAR(500)		= ''
DECLARE @Topic TABLE(
	rowNo		INT
	,topic		NVARCHAR(500) 
)
INSERT INTO @Topic 
VALUES (1,'PERIOD'), (2,'FEE'), (3,'PROFIT'), (4,'PAYMENT'), (5,'PF_PAY')

if OBJECT_ID('tempdb..#AdditionalFromListTopic') is not null
BEGIN
  PRINT 'Yes'
END
ELSE
BEGIN

  PRINT 'No'
CREATE TABLE #AdditionalFromListTopic
(
	topicCode		NVARCHAR(50),				
	createDate		DATETIME,				
	createDateStr	NVARCHAR(200),			
	editorId		UNIQUEIDENTIFIER,				
	custCde			NVARCHAR(50),				
	attachId		UNIQUEIDENTIFIER,				
	editorDetail	NVARCHAR(2000),			
	version			INT,				
	appState		INT,
	movmntID		NVARCHAR(50),
	accTypCde		NVARCHAR(50),
	movAccTypId		NVARCHAR(200)
)
END

DECLARE @index		INT = 1

WHILE EXISTS(SELECT 1 FROM @Topic WHERE rowNo = @index)
BEGIN
	SET @topicCode = (SELECT TOP 1 topic FROM @Topic WHERE rowNo = @index)


	IF (@topicCode = 'FEE') 
	BEGIN
		select
			@txtDefault += (CSR010.thDesc + ' ')
		from
			CBMSAPP100 as APP100
			LEFT JOIN CBTBMKT049 AS MKT049 ON MKT049.pgmCde = APP100.pgmCde ----
			LEFT JOIN CBTBCSR010 AS CSR010 ON CSR010.trnCde = MKT049.trnCde
		where
			APP100.movmntId = @movmntID
			and APP100.accTypCde = @accTypCde
			and APP100.pgmCde != '99999'
	END
	IF (@topicCode = 'PROFIT')
	BEGIN
		select
			@frofitDefault += ('อัตรากำไร'+ ' '+APP100.profitRteCde + ' ' + convert(NVARCHAR(50),APP100.discnt)+'%' )
		from CBMSAPP100 as APP100
		where
			APP100.movmntId = @movmntID
			and APP100.movAccTypId = @movAccTypId
	END
	
		INSERT INTO #AdditionalFromListTopic 
	                SELECT
	                    TOP 1 SYS60.topicCode								AS topicCode,
	                    APP80.createDate									AS createDate,
	                    FORMAT(APP80.createDate, 'dd/MM/yyyy', 'th-TH')		AS createDateStr,
	                    APP80.editorId										AS editorId,
	                    APP80.custCde										AS custCde,
	                    APP80.attachId										AS attachId,
	                    CASE
	                        WHEN @topicCode = 'FEE' THEN ISNULL(APP80.editorDetail, @txtDefault)
	                        ELSE CASE
								WHEN @topicCode = 'PROFIT' THEN ISNULL(APP80.editorDetail, @frofitDefault)
								ELSE APP80.editorDetail
							END
	                    END													AS editorDetail,
	                    APP80.version										AS version,
	                    APP80.appState										AS appState,
						@movmntID as movmntID,
						@accTypCde as accTypCde,
						@movAccTypId as movAccTypId
	                FROM
	                    CBMSAPP080 AS APP80
	                    INNER JOIN CBTBSYS060 AS SYS60 ON APP80.attachId = SYS60.attachId
	                WHERE
	                    APP80.movmntID = @movmntID
	                    AND APP80.accTypCde = @accTypCde
						AND APP80.movAccTypId = @movAccTypId
	                    AND SYS60.topicCode = @topicCode --in('PERIOD', 'FEE', 'PROFIT', 'PAYMENT', 'PF_PAY')
	                    AND APP80.editEmpNo IS NULL
	                ORDER BY
	                    APP80.createDate DESC

		SET @index	+= 1 
	END

SELECT * FROM #AdditionalFromListTopic 
where movmntID = @movmntID
and accTypCde = @accTypCde
and movAccTypId = @movAccTypId

delete #AdditionalFromListTopic 
where movmntID = @movmntID
and accTypCde = @accTypCde
and movAccTypId = @movAccTypId
END