-- khai báo table tạm chưa data
DECLARE @tbl_kpi_temp TABLE (KPI_TYPE VARCHAR(50), REQ_TYPE VARCHAR(50), REQ_STATUS VARCHAR(50), REQ_ID VARCHAR(50), REQ_REASON NVARCHAR(MAX), CORE_NOTE NVARCHAR(250), AUTH_STATUS_KT VARCHAR(50), TRANSFER_DT DATETIME, MAKER_ID_KT VARCHAR(50), CREATE_DT_KT DATETIME, CHECKER_ID_KT VARCHAR(50),	APPROVE_DT_KT DATETIME)
-- khai báo biến cần dùng
DECLARE @KPI_TYPE VARCHAR(10), @REQ_TYPE VARCHAR(20), @REQ_STATUS VARCHAR(20), @REQ_REASON NVARCHAR(MAX), @CORE_NOTE NVARCHAR(200), @AUTH_STATUS_KT VARCHAR(5), @TRANSFER_DT DATETIME, @MAKER_ID_KT VARCHAR(20), @CREATE_DT_KT DATETIME, @CHECKER_ID_KT VARCHAR(20), @APPROVE_DT_KT DATETIME,
@REQ_ID VARCHAR(20), @PROCESS_ID VARCHAR(20), @CHECKER_ID VARCHAR(20), @APPROVE_DT NVARCHAR(MAX), @PROCESS_DESC NVARCHAR(200), @NOTES NVARCHAR(MAX), @ID INT
-- Khai báo cursor
DECLARE XmlData CURSOR LOCAL FOR
select * from PL_PROCESS
where 1=1
and (LEFT(REQ_ID, 4) = 'TADY' OR LEFT(REQ_ID, 4) = 'TRPY')-- lấy phiếu thanh toán và tạm ứng
and (	(	CONVERT(DATE, APPROVE_DT) < '2022-12-31 23:55:51.610' 
			AND CONVERT(DATE, APPROVE_DT) > '2022-12-01 00:55:51.610'
		)
	)-- cac phieu thang 10
and CHECKER_ID in	(	select TLNANME from TL_USER where DEP_ID = 'DEP000000000022') -- process thuoc phong KT
and	PROCESS_ID IN	(	'TRANSF', 'SEND', 'REJECT', 'APP', 'SUG')-- trang thai phieu
order by CONVERT(DECIMAL(18,0),RIGHT(REQ_ID, 10)), ID

-- Mở cursor
OPEN XmlData;
BEGIN TRANSACTION
	--Insert XmlData
	FETCH NEXT FROM XmlData INTO @ID, @REQ_ID, @PROCESS_ID, @CHECKER_ID, @APPROVE_DT, @PROCESS_DESC, @NOTES
	-- fetch_status=0 --> cursor đọc dữ liệu thành công
	WHILE @@fetch_status=0 
	BEGIN
		IF(LEFT(@REQ_ID, 4) = 'TADY')
		BEGIN
			SET @KPI_TYPE = 'ADV_PAY'
			SET @REQ_TYPE = 'TR_ADVANCE_PAYMENT'
			SET @REQ_REASON = (SELECT REQ_REASON FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @REQ_ID)
			SET @CORE_NOTE = (SELECT CONFIRM_NOTES FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @REQ_ID)
		END
		ELSE
		BEGIN
			SET @KPI_TYPE = 'PAY'
			SET @REQ_TYPE = 'TR_REQ_PAYMENT'
			SET @REQ_REASON = (SELECT REQ_REASON FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @REQ_ID)
			SET @CORE_NOTE = (SELECT CONFIRM_NOTE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @REQ_ID)
		END


		IF(@PROCESS_ID = 'TRANSF')
		BEGIN
			DELETE FROM @tbl_kpi_temp WHERE REQ_ID = @REQ_ID AND REQ_STATUS = 'TRANSF' AND ISNULL(MAKER_ID_KT, '') = ''
			INSERT INTO @tbl_kpi_temp(	KPI_TYPE,	REQ_TYPE,	REQ_STATUS,		REQ_ID,		REQ_REASON,			CORE_NOTE,		AUTH_STATUS_KT,	TRANSFER_DT,		MAKER_ID_KT,			CREATE_DT_KT,	CHECKER_ID_KT,		APPROVE_DT_KT) 
			VALUES (				@KPI_TYPE,	@REQ_TYPE,	'TRANSF',		@REQ_ID,	@REQ_REASON,		@CORE_NOTE,		'U',			@APPROVE_DT,		NULL,					NULL,			@CHECKER_ID,		NULL )
		END
		--GDV
		ELSE IF(@PROCESS_ID = 'SEND')
		BEGIN
			UPDATE @tbl_kpi_temp SET MAKER_ID_KT = @CHECKER_ID, CREATE_DT_KT = @APPROVE_DT, REQ_STATUS = 'SEND', AUTH_STATUS_KT = 'U' WHERE REQ_ID = @REQ_ID AND ISNULL(MAKER_ID_KT, '') = ''
		END
		ELSE IF(@PROCESS_ID = 'REJECT' AND @NOTES = N'GDV đề xuất từ chối phiếu')
		BEGIN
			UPDATE @tbl_kpi_temp SET MAKER_ID_KT = @CHECKER_ID, CREATE_DT_KT = @APPROVE_DT, REQ_STATUS = 'SUG', AUTH_STATUS_KT = 'S' WHERE REQ_ID = @REQ_ID AND ISNULL(MAKER_ID_KT, '') = ''
		END
		ELSE IF(@PROCESS_ID = 'SUG')
		BEGIN
			UPDATE @tbl_kpi_temp SET MAKER_ID_KT = @CHECKER_ID, CREATE_DT_KT = @APPROVE_DT, REQ_STATUS = 'SUG', AUTH_STATUS_KT = 'S' WHERE REQ_ID = @REQ_ID AND ISNULL(MAKER_ID_KT, '') = ''
		END
		--KSV
		ELSE IF(@PROCESS_ID = 'REJECT'  AND @NOTES <> N'GDV đề xuất từ chối phiếu')
		BEGIN
			UPDATE @tbl_kpi_temp SET CHECKER_ID_KT = @CHECKER_ID, APPROVE_DT_KT = @APPROVE_DT, REQ_STATUS = 'REJECT', AUTH_STATUS_KT = 'R' WHERE REQ_ID = @REQ_ID AND ISNULL(APPROVE_DT_KT, '') = ''
		END
		ELSE IF(@PROCESS_ID = 'APP')
		BEGIN
			UPDATE @tbl_kpi_temp SET CHECKER_ID_KT = @CHECKER_ID, APPROVE_DT_KT = @APPROVE_DT, REQ_STATUS = 'APP', AUTH_STATUS_KT = 'A' WHERE REQ_ID = @REQ_ID AND ISNULL(APPROVE_DT_KT, '') = ''
		END

		IF @@error<>0 GOTO ABORT;
		FETCH NEXT FROM XmlData INTO @ID, @REQ_ID, @PROCESS_ID, @CHECKER_ID, @APPROVE_DT, @PROCESS_DESC, @NOTES
	END;
	CLOSE XmlData;
	DEALLOCATE XmlData;

COMMIT TRANSACTION
/*
SELECT * FROM @tbl_kpi_temp
ORDER BY REQ_ID, TRANSFER_DT 
*/

SELECT H.* 
			FROM 
			(
				SELECT A.REQ_PAY_CODE AS MA_SO_PHIEU, B.MAKER_ID_KT AS GDV, B.CHECKER_ID_KT AS KSV, A.REQ_REASON AS NOI_DUNG,
				CONVERT(VARCHAR,MONTH(B.TRANSFER_DT),2) +'/'+CONVERT(VARCHAR,DAY(B.TRANSFER_DT),2) +'/'+CONVERT(VARCHAR,YEAR(B.TRANSFER_DT),2)  AS NGAY_NHAN_PHIEU,
				CONVERT(VARCHAR,MONTH(B.CREATE_DT_KT),2) +'/'+CONVERT(VARCHAR,DAY(B.CREATE_DT_KT),2) +'/'+CONVERT(VARCHAR,YEAR(B.CREATE_DT_KT),2) AS NGAY_GDV_GUI_DUYET,
				CONVERT(VARCHAR,MONTH(B.APPROVE_DT_KT),2) +'/'+CONVERT(VARCHAR,DAY(B.APPROVE_DT_KT),2) +'/'+CONVERT(VARCHAR,YEAR(B.APPROVE_DT_KT),2) AS NGAY_KSV_DUYET,
				CASE	WHEN B.AUTH_STATUS_KT = 'U' THEN N'KSV điều phối'
						WHEN B.AUTH_STATUS_KT = 'P' THEN N'GDV gửi duyệt'
						WHEN B.AUTH_STATUS_KT = 'S' THEN N'GDV đề xuất từ chối phiếu'
						WHEN B.AUTH_STATUS_KT = 'R' THEN N'KSV từ chối phiếu'
						WHEN B.AUTH_STATUS_KT = 'A' THEN N'KSV duyệt phiếu'
				END AS TRANG_THAI_PHIEU
				FROM @tbl_kpi_temp B 
				JOIN TR_REQ_PAYMENT A ON B.REQ_ID = A.REQ_PAY_ID
				WHERE 1=1

				UNION

				SELECT A.REQ_PAY_CODE AS MA_SO_PHIEU, B.MAKER_ID_KT AS GDV, B.CHECKER_ID_KT AS KSV, A.REQ_REASON AS NOI_DUNG,
				CONVERT(VARCHAR,MONTH(B.TRANSFER_DT),2) +'/'+CONVERT(VARCHAR,DAY(B.TRANSFER_DT),2) +'/'+CONVERT(VARCHAR,YEAR(B.TRANSFER_DT),2)  AS NGAY_NHAN_PHIEU,
				CONVERT(VARCHAR,MONTH(B.CREATE_DT_KT),2) +'/'+CONVERT(VARCHAR,DAY(B.CREATE_DT_KT),2) +'/'+CONVERT(VARCHAR,YEAR(B.CREATE_DT_KT),2) AS NGAY_GDV_GUI_DUYET,
				CONVERT(VARCHAR,MONTH(B.APPROVE_DT_KT),2) +'/'+CONVERT(VARCHAR,DAY(B.APPROVE_DT_KT),2) +'/'+CONVERT(VARCHAR,YEAR(B.APPROVE_DT_KT),2) AS NGAY_KSV_DUYET,
				CASE	WHEN B.AUTH_STATUS_KT = 'U' THEN N'KSV điều phối'
						WHEN B.AUTH_STATUS_KT = 'P' THEN N'GDV gửi duyệt'
						WHEN B.AUTH_STATUS_KT = 'S' THEN N'GDV đề xuất từ chối phiếu'
						WHEN B.AUTH_STATUS_KT = 'R' THEN N'KSV từ chối phiếu'
						WHEN B.AUTH_STATUS_KT = 'A' THEN N'KSV duyệt phiếu'
				END AS TRANG_THAI_PHIEU
				FROM @tbl_kpi_temp B 
				JOIN TR_REQ_ADVANCE_PAYMENT A ON B.REQ_ID = A.REQ_PAY_ID
				WHERE 1=1
			) H
			ORDER BY H.MA_SO_PHIEU

ABORT:
BEGIN
	print 1
END