DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

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

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)

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_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Ố CHỨNG TỪ'+ 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) +N'THỜI GIAN '+ CHAR(10) AS NGAY_GD,

CHAR(10) +N'NỢ '+ CHAR(10) AS [NO],

CHAR(10) +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