ALTER PROCEDURE dbo.CM_WORKFLOW_APPROVE_DATA 
	@p_REQ_ID varchar(20)=null,
	@p_DONE BIT = NULL,
	@p_IsNote BIT = NULL,
	@p_IsFile BIT = NULL
AS
BEGIN 
	DECLARE @temp_NONE TABLE ([NONE] CHAR)
	DECLARE @Is_Back_Day bit=0
	DECLARE @Back_Day date
	IF(EXISTS(SELECT 1 FROM CM_REQUEST_TEMPLATE WHERE REQUEST_TEMPLATE_ID=@p_REQ_ID AND CAST(CREATE_DT AS DATE) > CAST(REPORT_DT AS DATE)))
	BEGIN
		SET @Is_Back_Day=1
		SET @Back_Day=(SELECT REPORT_DT FROM CM_REQUEST_TEMPLATE WHERE REQUEST_TEMPLATE_ID=@p_REQ_ID AND CREATE_DT>REPORT_DT)
	END
	--Table 1
	SELECT 
	A.APPROVE_GROUP_ID,
    A.APPROVE_USERNAME,
    A.AUTHORITY_NAME,
    A.STEP_LEVEL,
    A.PROCESS_STATUS,
    A.DONE,
    A.NOTES,
    A.RECORD_STATUS,
    A.AUTH_STATUS,
    A.MAKER_ID,
    A.CREATE_DT,
    A.CHECKER_ID,
	CASE WHEN @Is_Back_Day=1 THEN FORMAT(@Back_Day, 'dd/MM/yyyy HH:mm:ss')
	ELSE FORMAT(A.APPROVE_DT, 'dd/MM/yyyy HH:mm:ss') END AS 'APPROVE_DT',
    A.REQ_ID,
    A.IS_REJECT,
    A.NEED_SEND_EMAIL,
    A.[TYPE],
    A.APPROVE_ROOT_ID,
    A.ORDER_STEP,
	CASE
		WHEN GR.APPROVE_USERNAME IS NOT NULL THEN 'TUQ.' + UPPER(ER.POS_NAME)
		ELSE UPPER( E.POS_NAME )
	END AS 'POS_NAME',
	A.APPROVE_ROOT_ID,
	UA.TLFullName AS 'APPROVE_FULL_NAME'
	FROM CM_APPROVE_GROUP A
	LEFT JOIN TL_USER UA ON UA.TLNANME = A.APPROVE_USERNAME
	LEFT JOIN CM_EMPLOYEE_LOG E ON E.USER_DOMAIN = UA.TLNANME
	LEFT JOIN dbo.CM_APPROVE_GROUP GR ON A.APPROVE_ROOT_ID = GR.APPROVE_GROUP_ID
	LEFT JOIN CM_EMPLOYEE_LOG ER ON GR.APPROVE_USERNAME = ER.USER_DOMAIN
	WHERE 1=1
	AND A.REQ_ID = @p_REQ_ID
	AND A.DONE = 1
	ORDER BY STEP_LEVEL ASC, ORDER_STEP ASC, CREATE_DT ASC

	--Table 2
	IF(@p_IsNote = 1)
	BEGIN
		SELECT 
			N'- Yêu cầu đã được ' +
			CASE 
				WHEN A.[ACTION] = 'AUT' THEN N'uỷ quyền '
				WHEN A.[ACTION] = 'ADD' THEN N'tạo '
				WHEN A.[ACTION] = 'UPD' THEN N'cập nhật '
				WHEN A.[ACTION] = 'APP' THEN N'duyệt '
				WHEN A.[ACTION] = 'REJ' THEN N'từ chối '
				WHEN A.[ACTION] = 'HAN' THEN N'bàn giao '
				WHEN A.[ACTION] = 'SEND' THEN N'gửi đi '
				WHEN A.[ACTION] = 'SEND_R' THEN N'gửi lại '
				WHEN A.[ACTION] = 'SHARE' THEN N'chia sẻ '
				WHEN A.[ACTION] = 'APP_ADMIN' THEN N'duyệt thay '
				ELSE ''
			END + 
			N'bởi ' + UA.TLFullName + 
			CASE 
				WHEN A.[ACTION] = 'AUT' THEN N' cho ' + UG2.TLFullName
				WHEN A.[ACTION] = 'HAN' THEN N' cho ' + UG2.TLFullName
				WHEN A.[ACTION] = 'APP_ADMIN' THEN N' (Admin) cho ' + UG2.TLFullName
				WHEN A.[ACTION] = 'SHARE' THEN + ISNULL(N' cho ' + 
														STUFF((SELECT ', ' + ISNULL(TU.TLFullName, '')
																FROM CM_TEMPLATE_LOG_SHARE LG	
																LEFT JOIN dbo.TL_USER TU ON TU.TLNANME = LG.SHARER_ID
																WHERE LG.TEMPLATE_PROCESS_ID = A.[TEMPLATE_PROCESS_ID]
																AND LG.IS_ADD = 1
																FOR XML PATH(''), TYPE).value('.[1]', 'NVARCHAR(MAX)'), 1, 2, '') + ' và ', '')
												+ ISNULL(N' đã hủy chia sẻ '+
														STUFF((SELECT ', ' + ISNULL(TU.TLFullName, '')
																FROM CM_TEMPLATE_LOG_SHARE LG	
																LEFT JOIN dbo.TL_USER TU ON TU.TLNANME = LG.SHARER_ID
																WHERE LG.TEMPLATE_PROCESS_ID = A.[TEMPLATE_PROCESS_ID]
																AND LG.IS_ADD = 0
																FOR XML PATH(''), TYPE).value('.[1]', 'NVARCHAR(MAX)'), 1, 2, ''), '')	
				ELSE ''
			END + 
			N' vào lúc: ' + FORMAT(A.APPROVE_DT, 'dd/MM/yyyy HH:mm:ss') + '.' +
			CASE 
				WHEN A.[ACTION] IN ('APP', 'REJ', 'APP_ADMIN') THEN N' Lý do: ' + a.PROCESS_DESC + '.'
				ELSE ''
			END AS 'LOG'
		FROM CM_TEMPLATE_LOG A
		LEFT JOIN TL_USER UA ON UA.TLNANME = A.CHECKER_ID
		--LEFT JOIN CM_APPROVE_GROUP G ON G.APPROVE_GROUP_ID = A.APPROVE_GROUP_ID
		--LEFT JOIN TL_USER UA2 ON UA2.TLNANME = G.AUTHORITY_NAME
		LEFT JOIN TL_USER UG2 ON UG2.TLNANME = A.HANDOVER_MAKER_ID
		WHERE 1=1
		AND A.REQ_ID = @p_REQ_ID
		ORDER BY A.APPROVE_DT DESC
	END
	ELSE
	BEGIN
		SELECT '' AS 'LOG'
		FROM @temp_NONE
	END

	--Table 3
	SELECT 
		N'- ' + UA.TLFullName + FORMAT(A.CREATE_DT, ' (dd/MM/yyyy HH:mm:ss): ') + A.CONTENT + '.' AS 'NOTE'
	FROM CM_TEMPLATE_NOTE A
	LEFT JOIN TL_USER UA ON A.TEMPLATE_NOTE_USERNAME = UA.TLNANME 
	WHERE 1=1
	AND A.REQ_ID = @p_REQ_ID
	ORDER BY A.CREATE_DT DESC

	--Table 4
	IF(@p_IsFile = 1)
	BEGIN
		DECLARE @FILE_NAME_OLD NVARCHAR(MAX) = NULL,
				@FILE_NAME_NEW NVARCHAR(MAX) = NULL,
				@PATH_NEW NVARCHAR(MAX) = NULL,
				@FILE_TYPE NVARCHAR(MAX) = NULL

		DECLARE @temp_OLD TABLE (
			[FILE_NAME] NVARCHAR(MAX),
			[INDEX] INT
		)
		DECLARE @temp_NEW TABLE (
			[FILE_NAME] NVARCHAR(MAX),
			[INDEX] INT
		)	
		DECLARE @temp_PATH TABLE (
			[PATH_NAME] NVARCHAR(MAX),
			[INDEX] INT
		)	
		DECLARE @temp_TYPE TABLE (
			[TYPE_NAME] NVARCHAR(MAX),
			[INDEX] INT
		)

		SELECT TOP 1
			@FILE_NAME_OLD = FILE_NAME_OLD, 
			@FILE_NAME_NEW = FILE_NAME_NEW, 
			@PATH_NEW = PATH_NEW,
			@FILE_TYPE = FILE_TYPE
		FROM dbo.CM_ATTACH_FILE
		WHERE REF_ID = @p_REQ_ID
		ORDER BY ATTACH_DT DESC

		INSERT INTO @temp_OLD ([FILE_NAME], [INDEX])
		SELECT RTRIM(LTRIM(value)) AS 'FILE_NAME', ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS 'INDEX' 
		FROM WSISPLIT(@FILE_NAME_OLD, '|')

		INSERT INTO @temp_NEW ([FILE_NAME], [INDEX])
		SELECT RTRIM(LTRIM(value)) AS 'FILE_NAME', ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS 'INDEX' 
		FROM WSISPLIT(@FILE_NAME_NEW, '|')

		INSERT INTO @temp_PATH ([PATH_NAME], [INDEX])
		SELECT RTRIM(LTRIM(value)) AS 'PATH_NAME', ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS 'INDEX' 
		FROM WSISPLIT(@PATH_NEW, '|')

		INSERT INTO @temp_TYPE ([TYPE_NAME], [INDEX])
		SELECT RTRIM(LTRIM(value)) AS 'TYPE_NAME', ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS 'INDEX' 
		FROM WSISPLIT(@FILE_TYPE, '|')

		SELECT 			
			CASE
				WHEN D.[TYPE_NAME] IN ('.xlsx', '.xls') THEN 'excel.png'
				WHEN D.[TYPE_NAME] IN ('.doc', '.docx') THEN 'word.png'
				WHEN D.[TYPE_NAME] IN ('.pdf') THEN 'pdf.png'
				WHEN D.[TYPE_NAME] IN ('.rar') THEN 'rar.png'
				WHEN D.[TYPE_NAME] IN ('.jpeg', '.png') THEN 'image.png'
				ELSE 'file.png'
			END AS 'ICON',
			A.[FILE_NAME] AS 'FILE_NAME' , 
			CONCAT('api/', C.[PATH_NAME], '/', B.[FILE_NAME]) AS 'FILE_PATH'
		FROM @temp_OLD A
		INNER JOIN @temp_NEW B ON B.[INDEX] = A.[INDEX]
		INNER JOIN @temp_PATH C ON C.[INDEX] = A.[INDEX]
		INNER JOIN @temp_TYPE D ON D.[INDEX] = A.[INDEX]
	END
	ELSE
	BEGIN
		SELECT NULL AS 'ICON', NULL AS 'FILE_NAME' , NULL AS 'FILE_PATH'
		FROM @temp_NONE
	END
END
GO