/*		

	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