DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

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)

	--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,

	FORMAT(A.APPROVE_DT, 'dd/MM/yyyy hh:mm:ss') 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.' + ER.POS_NAME

		ELSE 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') + '.' 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_ID 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