USE [CAPDB_CORP_SIT]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetDataAdditionalExplanationFromListTopic]    Script Date: 4/6/2021 2:39:27 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')

DECLARE @Response	TABLE(
	topicCode		NVARCHAR(50),				
	createDate		DATETIME,				
	createDateStr	NVARCHAR(200),			
	editorId		UNIQUEIDENTIFIER,				
	custCde			NVARCHAR(50),				
	attachId		UNIQUEIDENTIFIER,				
	editorDetail	NVARCHAR(2000),			
	version			INT,				
	appState		INT		
)

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 @Response 
		select top 1 * from
	    (
	        select * from
	            (
	                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
	                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
	            ) as x
	        UNION
	        SELECT
	            @topicCode									AS topicCode,
	            getdate()									AS createDate,
	            FORMAT(getdate(), 'dd/MM/yyyy', 'th-TH')	AS createDateStr,
	            @EmptyGuid									AS editorId,
	            ''											AS custCde,
	            @EmptyGuid									AS attachId,
	            @txtDefault									AS editorDetail,
	            0											AS version,
	            0											AS appState
	    ) as DataComment

		SET @index	+= 1 
	END

	SELECT * FROM @Response
END