IBANK - PROJECT

??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:	<3-42"@4H!@4!>

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

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 += ('-1#2
3D#'+ ' '+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