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

/*		

	Lưu ý: 

	1/ 3 field ở góc phải trên của phiếu in đính kèm

	+ So: Dòng này để trống khi in ra

	+ LP: Điền user in chứng từ vào (USER_ID VD: thieuvq, daong ...)

	+ CT: REF_NO

	--Noi dung

	- CR_ACCT: TAI KHOAN GHI CO

	- CR_ACCT_NAME: TEN TAI KHOAN GHI CO

	- DR_ACCT: TAI KHOAN GHI NO

	- DR_ACCT_NAME: TEN TAI KHOAN GHI NO

	- TRN_DESC: NOI DUNG

	- AMT: SO TIEN, O TRUONG HOP NAY SO TIEN GHI NO VA SO TIEN GHI CO LA BANG NHAU NEN EM CU LAY THEO AMT

	---TEST

	TREN GIAO DIEN REPORT, PHAN DETAIL EM BAO GOM PHAN GHI NO VA GHI CO, 1 RECORD ANH TRA VE LA 1 CAP NO VA CO 

	TREN REPORT, NEU ANH TRA VE 2 RECORD THI CO NGHIA LUC DO TREN GIAO DIEN SE CO 4 DONG NO, CO, NO, CO

	---MAU TEST STORE

	[ASS_ENTRIES_POST_ByRefId] 'ASSL00000000024'

	--DOC SO TIEN BANG CHU EM DUNG THU VIEN mvvmCommon ReadMoney.ToString(decimal amount);

	select * from ASS_ENTRIES_POST

*/

ALTER PROCEDURE [dbo].[rpt_ASS_ENTRIES_POST_ByRefId_CASA_BanViet_w] @p_REF_ID VARCHAR(200) = NULL

AS

DECLARE @l_SUM_AMT DECIMAL,

        @l_REF_NO VARCHAR(20),

        @l_ASSET_ID VARCHAR(20);

DECLARE @l_TRN_TYPE VARCHAR(50) = '';

DECLARE @l_CASA_CR_ACCT VARCHAR(20) = '';

DECLARE @l_CASA_DR_ACCT VARCHAR(20) = '';

DECLARE @l_CASA_CR_ACCT_NAME NVARCHAR(500) = '';

DECLARE @l_CASA_DR_ACCT_NAME NVARCHAR(500) = '';

DECLARE @NAMES VARCHAR(20) = '';

--PHONGNT 13/8/22 CHU KY USER

DECLARE  @User TABLE

(GDV_USERNAME VARCHAR(50), 

 KSV NVARCHAR(500), 

 GDV_NAME NVARCHAR(500),

 BRANDNAME NVARCHAR(1000),

 ID VARCHAR(20)

)

DECLARE  @TRN_TYPE VARCHAR(15)

SET @TRN_TYPE =(SELECT TOP(1)TRN_TYPE FROM ASS_ENTRIES_POST A WHERE A.TRN_ID = @p_REF_ID)

IF(@TRN_TYPE='ADD_NEW')

	BEGIN

		INSERT INTO @User

		SELECT A.MAKER_ID_KT AS GDV_USERNAME,

				UM3.TLFullName AS KSV,

				UM4.TLFullName AS GDV_NAME,

				BR.BRANCH_NAME AS BRANDNAME,

				A.ADDNEW_ID

			FROM ASS_ADDNEW A

				LEFT JOIN TL_USER UM3 ON UM3.TLNANME = A.CHECKER_ID_KT

				LEFT JOIN TL_USER UM4 ON UM4.TLNANME = A.MAKER_ID_KT

				LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID = A.BRANCH_CREATE

			WHERE ADDNEW_ID = @p_REF_ID

	END

ELSE IF(@TRN_TYPE='ASS_UPDATE')

	BEGIN

		INSERT INTO @User

		SELECT A.MAKER_ID AS GDV_USERNAME,

				UM3.TLFullName AS KSV,

				UM4.TLFullName AS GDV_NAME,

				BR.BRANCH_NAME AS BRANDNAME,

				A.UPDATE_ID

			FROM dbo.ASS_UPDATE A

				LEFT JOIN TL_USER UM3 ON UM3.TLNANME = A.CHECKER_ID

				LEFT JOIN TL_USER UM4 ON UM4.TLNANME = A.MAKER_ID

				LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID = A.BRANCH_ID

			WHERE UPDATE_ID = @p_REF_ID

	END

ELSE IF(@TRN_TYPE='LIQ')

	BEGIN

		INSERT INTO @User

		SELECT A.MAKER_ID_KT AS GDV_USERNAME,

				UM3.TLFullName AS KSV,

				UM4.TLFullName AS GDV_NAME,

				BR.BRANCH_NAME AS BRANDNAME,

				A.LIQ_ID

			FROM dbo.ASS_LIQUIDATION A

				LEFT JOIN TL_USER UM3 ON UM3.TLNANME = A.CHECKER_ID_KT

				LEFT JOIN TL_USER UM4 ON UM4.TLNANME = A.MAKER_ID_KT

				LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID = A.BRANCH_ID

			WHERE LIQ_ID = @p_REF_ID

	END

SELECT A.TRN_ID,

	   RIGHT('00'+convert(varchar(10),DAY(A.TRN_DATE)),2)+N'/'+RIGHT('00'+convert(varchar(10),MONTH(A.TRN_DATE)),2) +N'/'+convert(varchar(10),YEAR(A.TRN_DATE)) AS PrintDate,

	   A.TRN_DATE PrintDate,

       A.SUM_AMT,

       A.SUM_stringMoney,

       '' CASA_CR_ACCT,

       '' CASA_CR_ACCT_NAME,

       '' CASA_DR_ACCT,

       '' CASA_DR_ACCT_NAME,

       D.TRN_DESC,

       A.REF_NO REF_NO,

       A.REF_NO REF_NO_SUM,

	   -- DuyTN 07072021 Bổ sung biến tên đơn vị và tên user GDV, KSV để phục vụ in phiếu hạch toán 1 page - 1 ref no (Do không binding trực tiếp từ giao diện được)

	   E.BRANDNAME,

	   E.GDV_USERNAME,

	   E.KSV,

	   E.GDV_NAME

FROM

(

    SELECT 

           A.TRN_ID,A.REF_NO,

		   CONVERT(DATE,A.TRN_DATE) TRN_DATE, --- LUCTV22072019: BO SUNG CONVERT SANG DATE, VI DU LIEU TRN_DATE DANG LUU THEO DATE_TIME NEN KHONG GROUP BY DC

           SUM(A.AMT) AS SUM_AMT,

           dbo.ReadMoney(SUM(A.AMT)) AS SUM_stringMoney

    FROM ASS_ENTRIES_POST A

    WHERE A.TRN_ID = @p_REF_ID

          AND LEN(CR_ACCT) <= 9

    ---GROUP BY A.TRN_ID, CONVERT(DATE,A.TRN_DATE,103) ----- LUCTV22072019: BO SUNG CONVERT SANG DATE, VI DU LIEU TRN_DATE DANG LUU THEO DATE_TIME NEN KHONG GROUP BY DC

	GROUP BY A.REF_NO,A.TRN_ID,CONVERT(DATE,A.TRN_DATE)

) AS A

    --LEFT JOIN

    --(

    --    SELECT TOP 1

    --           CR_ACCT AS CASA_CR_ACCT,

    --           --DR_ACCT AS CASA_DR_ACCT,

    --           [dbo].[FN_GET_ACC_NAME](CR_ACCT) AS CASA_CR_ACCT_NAME,

    --           [dbo].[FN_GET_ACC_NAME](DR_ACCT) AS CASA_DR_ACCT_NAME

    --    FROM ASS_ENTRIES_POST

    --    WHERE TRN_ID = @p_REF_ID

    --          AND LEN(CR_ACCT) > 9

    --) AS B

    --    ON 1 = 1

    --LEFT JOIN

    --(

    --    SELECT TOP 1

    --        --CR_ACCT AS CASA_CR_ACCT,

    --           DR_ACCT AS CASA_DR_ACCT,

    --           [dbo].[FN_GET_ACC_NAME](CR_ACCT) AS CASA_CR_ACCT_NAME,

    --           [dbo].[FN_GET_ACC_NAME](DR_ACCT) AS CASA_DR_ACCT_NAME

    --    FROM ASS_ENTRIES_POST

    --    WHERE TRN_ID = @p_REF_ID

    --          AND LEN(DR_ACCT) > 9

    --) AS C

    --    ON 1 = 1

    LEFT JOIN

    (

        SELECT TOP 1

               TRN_DESC,

               REF_NO,

               TRN_ID

        FROM ASS_ENTRIES_POST

        WHERE TRN_ID = @p_REF_ID

              AND LEN(CR_ACCT) <= 9

    ) AS D

        ON 1 = 1

    --LEFT JOIN

    --(

    --    SELECT STUFF(

    --           (

    --               SELECT ', ' + C1.REF_NO

    --               FROM ASS_ENTRIES_POST C1

    --               WHERE C2.TRN_ID = C1.TRN_ID

    --               GROUP BY C1.REF_NO,

    --                        C1.TRN_ID

    --               --AND C1.REF_NO IS NOT NULL

    --               FOR XML PATH('')

    --           ),

    --           1,

    --           1,

    --           ''

    --                ) AS REF_NO_SUM

    --    FROM

    --    (

    --        SELECT REF_NO,

    --               TRN_ID

    --        FROM ASS_ENTRIES_POST

    --        WHERE TRN_ID = @p_REF_ID

    --              AND REF_NO IS NOT NULL

    --        GROUP BY REF_NO,

    --                 TRN_ID

    --    ) C2

    --    GROUP BY C2.TRN_ID

    --) AS E

    --    ON 1 = 1;

	--LEFT JOIN (

	--	SELECT A.MAKER_ID AS USERNAME,

	--		UM3.TLFullName AS KSV,

	--		UM4.TLFullName AS GDV_NAME,

	--		BR.BRANCH_NAME AS BRANDNAME,

	--		A.ADDNEW_ID

	--	FROM ASS_ADDNEW A

	--		LEFT JOIN TL_USER UM3 ON UM3.TLNANME = A.CHECKER_ID_KT

	--		LEFT JOIN TL_USER UM4 ON UM4.TLNANME = A.MAKER_ID_KT

	--		LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID = A.BRANCH_CREATE

	--	WHERE ADDNEW_ID = @p_REF_ID

	--) E ON E.ADDNEW_ID = A.TRN_ID

	--PHONGNT 13/8/22

	LEFT JOIN @User E ON E.ID = A.TRN_ID

GO