ALTER PROCEDURE [dbo].[rpt_PAY_LIET_KE_CHUNG_TU_BanViet] @p_DATE VARCHAR(10), @p_BRANCH_ID VARCHAR(15) ='', @p_MAKER_ID VARCHAR(15) =NULL
AS

-- LẤY MỖI SỐ REF NO RA
--DECLARE @ET TABLE (REF_NO VARCHAR(25), TK_NO VARCHAR(15), TK_CO VARCHAR(15), BRN_NO VARCHAR(15), BRANCH_CO VARCHAR(15), DEP_NO VARCHAR(15), DEP_CO VARCHAR(15))
--SELECT * FROM PAY_ENTRIES_POST WHERE TRN_ID='TRPTF0000000018' AND REF_NO ='069CCfffyutmyiiii09'
--1 LẤY DANH SÁCH HẠCH TOÁN THEO TỪNG REF NO 
--
DECLARE @REF_NO VARCHAR(45), @DR_ACCT VARCHAR(15), @CR_ACCT VARCHAR(15), @TRN_DATE VARCHAR(30), @TRN_ID VARCHAR(15), @DR_BRN VARCHAR(15), @CR_BRN VARCHAR(15), @DR_DEP VARCHAR(15), @CR_DEP VARCHAR(15),
@TRN_DESC VARCHAR(250),@MAKER_ID VARCHAR(15), @CHECKER_ID VARCHAR(15),@ET_ID VARCHAR(15), @AMT_DRCR DECIMAL(18,0), @INDEX_STT INT =0
DECLARE @TABLE_REF_NO TABLE (ID INT IDENTITY(1,1),REF_NO VARCHAR(25), TRN_ID VARCHAR(15))
INSERT INTO @TABLE_REF_NO SELECT DISTINCT A.REF_NO, TRN_ID FROM PAY_ENTRIES_POST A
WHERE (A.REF_NO IS NOT NULL AND A.REF_NO <> '' )
AND (CONVERT(DATE, A.TRN_DATE, 103) = CONVERT(DATE, @p_DATE, 103))
AND A.DO_BRN=@p_BRANCH_ID
AND(A.MAKER_ID=@p_MAKER_ID OR @p_MAKER_ID IS NULL OR @p_MAKER_ID='')
AND (A.EXP_TO_CORE='Y' OR A.EXP_TO_CORE ='1')

-- KHAI BAO TABLE CHUA THÔNG TIN HẠCH TOÁN THEO CẤU TRÚC NỢ - CÓ CHUNG 1 HÀNG
DECLARE @MW_ENTRIES_POST TABLE (ID INT IDENTITY(1,1),TRN_DATE VARCHAR(30), TRN_ID VARCHAR(15),ET_ID VARCHAR(15), DR_BRANCH_CODE VARCHAR(15),DR_BRN VARCHAR(15), CR_BRANCH_CODE VARCHAR(15),
		CR_BRN VARCHAR(15),AMT DECIMAL(18,0),TRN_DESC NVARCHAR(1000),MAKER_ID VARCHAR(15),CHECKER_ID VARCHAR(15), DO_BRN VARCHAR(15),EXP_TO_CORE VARCHAR(1),REF_NO VARCHAR(50), CRDR VARCHAR(15), STT INT)
--TABLE CHUA DANH SACH HACH TOAN CÓ
DECLARE @ET_CR_DR TABLE (ID INT IDENTITY(1,1),TK VARCHAR(15), DV VARCHAR(15), AMT DECIMAL(18,0),ET_ID VARCHAR(15))
WHILE((SELECT COUNT(*) FROM @TABLE_REF_NO) >0)
BEGIN
	SET @REF_NO =(SELECT TOP 1 REF_NO FROM @TABLE_REF_NO)
	PRINT @REF_NO
	IF(NOT EXISTS(SELECT * FROM @MW_ENTRIES_POST WHERE TRN_ID =(SELECT TOP 1 TRN_ID WHERE REF_NO =@REF_NO)))
	BEGIN
		SET @INDEX_STT = @INDEX_STT +1
	END
	SET @TRN_DATE =(SELECT TOP 1 FORMAT(TRN_DATE,'dd/MM/yyyy H:mm:ss') FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO)
	PRINT @TRN_DATE
	SET @TRN_ID =(SELECT TOP 1 TRN_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO)
	SET @TRN_DESC =(SELECT TOP 1 TRN_DESC FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO)
	SET @MAKER_ID =(SELECT TOP 1 MAKER_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO)
	SET @CHECKER_ID =(SELECT TOP 1 CHECKER_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO)
	--SET @AMT =(SELECT TOP 1 AMT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO)
	IF((SELECT COUNT(*)  FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='D' AND REF_NO IS NOT NULL AND REF_NO <> '')=1)
	BEGIN
		-- TAI KHOAN NO
		SET @DR_ACCT =(SELECT TOP 1 ACCT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='D')
		SET @DR_BRN =(SELECT TOP 1 BRN_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='D')
		--SET @DR_BRN =(SELECT TOP 1 DEPT_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='D')
		SET @ET_ID = (SELECT TOP 1 ET_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='D')
		SET @AMT_DRCR =(SELECT TOP 1 AMT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR='D')
		-- ĐỔ DỮ LIỆU VÀO BẢNG CHƯA HẠCH TOÁN CÓ
		INSERT INTO @ET_CR_DR SELECT ACCT,BRN_ID,AMT ,ET_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='C'
		--SELECT * FROM @ET_CO
		INSERT INTO @MW_ENTRIES_POST VALUES (@TRN_DATE,@TRN_ID,(SELECT TOP 1 ET_ID FROM @ET_CR_DR) ,@DR_ACCT ,@DR_BRN,'','',@AMT_DRCR,
			@TRN_DESC,UPPER(@MAKER_ID),UPPER(@CHECKER_ID),'DV0001','Y',@REF_NO,'D',@INDEX_STT)
		WHILE ((SELECT COUNT(*) FROM @ET_CR_DR)>0)
		BEGIN
			INSERT INTO @MW_ENTRIES_POST VALUES (@TRN_DATE,@TRN_ID,(SELECT TOP 1 ET_ID FROM @ET_CR_DR) ,'' ,'',(SELECT TOP 1 TK FROM @ET_CR_DR),(SELECT TOP 1 DV FROM @ET_CR_DR),(SELECT TOP 1 AMT FROM @ET_CR_DR),
			@TRN_DESC,UPPER(@MAKER_ID),UPPER(@CHECKER_ID),'DV0001','Y',@REF_NO,'C',@INDEX_STT)
			DELETE FROM @ET_CR_DR WHERE ID =(SELECT TOP 1 ID FROM @ET_CR_DR)
		END
	END
	ELSE
	BEGIN -- XỬ LÝ NẾU 1 CÓ NHIỀU NỢ
		-- TAI KHOAN NO
		SET @CR_ACCT =(SELECT TOP 1 ACCT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='C')
		SET @CR_BRN =(SELECT TOP 1 BRN_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='C')
		SET @CR_DEP =(SELECT TOP 1 DEPT_ID FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='C')
		SET @AMT_DRCR =(SELECT TOP 1 AMT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR='C')
		-- ĐỔ DỮ LIỆU VÀO BẢNG CHƯA HẠCH TOÁN CÓ
		INSERT INTO @ET_CR_DR SELECT ACCT,BRN_ID,AMT,ET_ID  FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_NO AND DRCR ='D'
		INSERT INTO @MW_ENTRIES_POST VALUES (@TRN_DATE,@TRN_ID,(SELECT TOP 1 ET_ID FROM @ET_CR_DR),'','',@CR_ACCT,@CR_BRN,@AMT_DRCR,
			@TRN_DESC,UPPER(@MAKER_ID),UPPER(@CHECKER_ID),'DV0001','Y',@REF_NO,'C',@INDEX_STT)
		WHILE ((SELECT COUNT(*) FROM @ET_CR_DR)>0)
		BEGIN
			INSERT INTO @MW_ENTRIES_POST VALUES (@TRN_DATE,@TRN_ID,(SELECT TOP 1 ET_ID FROM @ET_CR_DR) ,(SELECT TOP 1 TK FROM @ET_CR_DR) ,(SELECT TOP 1 DV FROM @ET_CR_DR),'','',(SELECT TOP 1 AMT FROM @ET_CR_DR),
			@TRN_DESC,UPPER(@MAKER_ID),UPPER(@CHECKER_ID),'DV0001','Y',@REF_NO,'D',@INDEX_STT)
			DELETE FROM @ET_CR_DR WHERE ID =(SELECT TOP 1 ID FROM @ET_CR_DR)
		END
	END
DELETE FROM @TABLE_REF_NO WHERE ID =(SELECT TOP 1 ID FROM @TABLE_REF_NO)
END -- KET THU WHILE
--LAY MA DON VI CUA HS NEU TIM THEO HS
IF @p_BRANCH_ID='HS' BEGIN
    SELECT @p_BRANCH_ID=BRANCH_ID FROM CM_BRANCH WHERE BRANCH_TYPE='HS';
END;

--SELECT CC.STT, CC.TRN_ID, CC.ET_ID, CC.DBRANCH_CODE, CC.CBRANCH_CODE, CC.CR_ACCT, CC.CR_BRN, CC.DR_ACCT, CC.DR_BRN, CC.AMT, CC.TRN_DESC,
--       CC.MAKER_ID, CC.CHECKER_ID, CC.CR_BRANCH_CODE, CC.DR_BRANCH_CODE, CC.MAPHONGBAN, CC.CCY, CC.REF_NO, CC.TRN_DATE
--FROM(
--    SELECT ROW_NUMBER() OVER (ORDER BY MIN(A.TRN_DATE)) AS STT, MIN(A.TRN_DATE) AS TRN_DATE,A.TRN_ID,MAX(A.ET_ID) AS ET_ID,
--	D.BRANCH_CODE AS DBRANCH_CODE,C.BRANCH_CODE AS CBRANCH_CODE,A.CR_ACCT, A.CR_BRN, A.DR_ACCT, A.DR_BRN, SUM(A.AMT) AS AMT, UPPER(A.TRN_DESC) AS TRN_DESC,
--    UPPER(A.MAKER_ID) AS MAKER_ID, UPPER(A.CHECKER_ID) AS CHECKER_ID,'' AS CR_BRANCH_CODE,A.DR_ACCT+' '+D.BRANCH_CODE AS DR_BRANCH_CODE,
--	'' AS MAPHONGBAN, 'VND' AS CCY, A.REF_NO as REF_NO
--    FROM @MW_ENTRIES_POST A
--	     --LEFT JOIN dbo.MW_MAST_BAL B ON A.MAST_PRICE_ID=B.PRICE_ID
--         LEFT JOIN CM_BRANCH C ON C.BRANCH_ID=A.CR_BRN
--         LEFT JOIN CM_BRANCH D ON D.BRANCH_ID=A.DR_BRN
--	WHERE 1=1
--	AND CONVERT(DATE, A.TRN_DATE, 103)= CONVERT(DATE, @p_DATE, 103) AND A.DO_BRN=@p_BRANCH_ID
--    AND(A.MAKER_ID=@p_MAKER_ID OR @p_MAKER_ID IS NULL OR @p_MAKER_ID='')
--	AND (A.EXP_TO_CORE='Y' OR A.EXP_TO_CORE ='1')
--	GROUP BY A.TRN_ID, A.CR_ACCT, A.CR_BRN, A.DR_ACCT, A.DR_BRN, A.TRN_DESC,  CONVERT(DATE, A.TRN_DATE, 103), A.MAKER_ID, A.CHECKER_ID, D.BRANCH_CODE,C.BRANCH_CODE,A.REF_NO
--    UNION ALL
--    SELECT ROW_NUMBER() OVER (ORDER BY MIN(A.TRN_DATE)) AS STT, 
--	MIN(A.TRN_DATE) AS TRN_DATE,
--	A.TRN_ID,
--	MAX(A.ET_ID) AS ET_ID,
--	D.BRANCH_CODE AS DBRANCH_CODE,C.BRANCH_CODE AS CBRANCH_CODE,
--        --A.ASSET_ID,
--        --A.TRN_TYPE,
--        A.CR_ACCT, A.CR_BRN, A.DR_ACCT, A.DR_BRN, SUM(A.AMT) AS AMT, UPPER(A.TRN_DESC) AS TRN_DESC,
--        --A.REF_NO,
--        UPPER(A.MAKER_ID) AS MAKER_ID, UPPER(A.CHECKER_ID) AS CHECKER_ID,
--        --B.ASSET_CODE,
--        --B.ASSET_NAME,
--        A.CR_ACCT+' '+C.BRANCH_CODE AS CR_BRANCH_CODE,
--        '' AS CR_BRANCH_CODE,
--        '' AS MAPHONGBAN, 'VND' AS CCY,
--		A.REF_NO as REF_NO
--       FROM @MW_ENTRIES_POST A
--        --LEFT JOIN dbo.MW_MAST_BAL B ON A.MAST_PRICE_ID=B.PRICE_ID
--         LEFT JOIN CM_BRANCH C ON C.BRANCH_ID=A.CR_BRN
--         LEFT JOIN CM_BRANCH D ON D.BRANCH_ID=A.DR_BRN
--    --LEFT JOIN CM_DEPARTMENT CD ON CD.DEP_ID=A.CR_BRN
--    --LEFT JOIN CM_DEPARTMENT DD ON DD.DEP_ID=A.DR_BRN
--	WHERE 1=1
--	AND CONVERT(DATE, A.TRN_DATE, 103)= CONVERT(DATE, @p_DATE, 103) AND A.DO_BRN=@p_BRANCH_ID
--	 AND(A.MAKER_ID=@p_MAKER_ID OR @p_MAKER_ID IS NULL OR @p_MAKER_ID='')
--	AND (A.EXP_TO_CORE='Y' OR A.EXP_TO_CORE ='1')
--	  GROUP BY A.TRN_ID, A.CR_ACCT, A.CR_BRN, A.DR_ACCT, A.DR_BRN, A.TRN_DESC,  CONVERT(DATE, A.TRN_DATE, 103), A.MAKER_ID, A.CHECKER_ID, D.BRANCH_CODE,C.BRANCH_CODE,A.REF_NO
--    ) AS CC
--ORDER BY cc.STT,CC.ET_ID, CC.CR_BRANCH_CODE, CC.TRN_DATE DESC;
SELECT CHAR(10) + A.TRN_DATE + CHAR(10) AS TRN_DATE, CHAR(10) + UPPER(A.TRN_DESC) + CHAR(10) AS TRN_DESC, CHAR(10) + A.TRN_ID + CHAR(10) AS TRN_ID, CHAR(10) + A.REF_NO + CHAR(10) AS REF_NO ,CHAR(10) + REPLACE (FORMAT ((A.AMT),'#,###'),',','.') +',00 ' + CHAR(10) AS AMT, CHAR(10) + 'VND' + CHAR(10)  AS CCY , 
CHAR(10) + REPLACE (FORMAT ((A.AMT),'#,###'),',','.') +',00' + CHAR(10) AS QUY_DOI, 
CHAR(10) + A.MAKER_ID + CHAR(10) AS MAKER_ID,CHAR(10)+ A.CHECKER_ID + CHAR(10) AS CHECKER_ID, CHAR(10)+  A.DR_BRANCH_CODE + ' ' + BR.BRANCH_CODE +  CHAR(10) AS DR_BRANCH_CODE  , CHAR(10)+ A.CR_BRANCH_CODE +' ' + CR.BRANCH_CODE + CHAR(10) AS CR_BRANCH_CODE, CHAR(10) + CONVERT(VARCHAR,A.STT,5) + CHAR(10) AS STT
FROM @MW_ENTRIES_POST A
LEFT JOIN CM_BRANCH BR ON A.DR_BRN = BR.BRANCH_ID
LEFT JOIN CM_BRANCH CR ON A.CR_BRN = CR.BRANCH_ID
AND (CONVERT(DATE, A.TRN_DATE, 103) = CONVERT(DATE, @p_DATE, 103))
AND A.DO_BRN=@p_BRANCH_ID
AND(A.MAKER_ID=@p_MAKER_ID OR @p_MAKER_ID IS NULL OR @p_MAKER_ID='')
AND (A.EXP_TO_CORE='Y' OR A.EXP_TO_CORE ='1')
ORDER BY A.REF_NO, A.CRDR DESC


SELECT N' Ngày lập phiếu: '+ FORMAT (GETDATE(),'dd/MM/yyyy H:mm:ss') AS NGAY_TAO, N'CHI NHÁNH /PGD : '+ (SELECT TOP 1 BRANCH_CODE FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_ID)+ ' ' + (SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_ID) AS DVKD,
N'Ngày: '+ @p_DATE AS NGAY_LAP_PHIEU 

SELECT CHAR(10) +'STT'+ CHAR(10) AS STT,
CHAR(10) +N'SỐ REF AMS'+ CHAR(10) AS SO_CT,
CHAR(10) +N'SỐ GIAO DỊCH / REF NO'+ CHAR(10) AS SO_GD,
CHAR(10) +N'DIỄN GIẢI TRANSACTION'+ CHAR(10) AS CORE_NOTE,
CHAR(10) +char(10) +N'THỜI GIAN '+ CHAR(10) AS NGAY_GD,
CHAR(10)+N'TÀI KHOẢN NỢ '+ CHAR(10) AS [NO],
CHAR(10) +N'TÀI KHOẢN CÓ '+ CHAR(10) AS [CO],
CHAR(10) +N'CCY '+ CHAR(10) AS CCY,
CHAR(10) +N'NGUYÊN TỆ '+ CHAR(10) AS NT,
CHAR(10) +N'QUY ĐỔI '+ CHAR(10) AS QD,
CHAR(10) +N'GDV '+ CHAR(10) AS GDV,
CHAR(10) +N'KSV '+ CHAR(10) AS KSV