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

ALTER   PROCEDURE [dbo].[rpt_MW_ENTRIES_POST_ByRefId_BanViet_w] @p_REF_ID VARCHAR(200) = NULL, @p_BRANCH_ID VARCHAR(50) = NULL, @p_RefNo VARCHAR(500) = NULL

AS

DECLARE @ENT_TEMP TABLE (ID INT,REF_ID VARCHAR(15), CR_ACCT VARCHAR(50), CR_BRN VARCHAR(15), DR_ACCT VARCHAR(50), DR_BRN VARCHAR(15), AMT DECIMAL(18,0), TRN_DESC NVARCHAR(500), ASSET_ID VARCHAR(15))

DECLARE @ENT_TEMP_ROOT TABLE (ID INT,REF_ID VARCHAR(15), CR_ACCT VARCHAR(50), CR_BRN VARCHAR(15), DR_ACCT VARCHAR(50), DR_BRN VARCHAR(15), AMT DECIMAL(18,0), TRN_DESC NVARCHAR(500), ASSET_ID VARCHAR(15),DR_NAME NVARCHAR(500), CR_NAME NVARCHAR(500))

DECLARE @ENT_TEMP_ROOT_NOT_VAT TABLE (ID INT,REF_ID VARCHAR(15), CR_ACCT VARCHAR(50), CR_BRN VARCHAR(15), DR_ACCT VARCHAR(50), DR_BRN VARCHAR(15), AMT DECIMAL(18,0), TRN_DESC NVARCHAR(500), ASSET_ID VARCHAR(15),DR_NAME NVARCHAR(500), CR_NAME NVARCHAR(500))

DECLARE @ENT_TEMP_ROOT_VAT TABLE (ID INT,REF_ID VARCHAR(15), CR_ACCT VARCHAR(50), CR_BRN VARCHAR(15), DR_ACCT VARCHAR(50), DR_BRN VARCHAR(15), AMT DECIMAL(18,0), TRN_DESC NVARCHAR(500), ASSET_ID VARCHAR(15),DR_NAME NVARCHAR(500), CR_NAME NVARCHAR(500))

DECLARE @TRN_DESC NVARCHAR(500) = '', @TRN_TYPE VARCHAR(50)

--------------------------LAY DANH SACH BUT TOAN -- LU VAO BANG DU LIEU GOC--------------------------

INSERT INTO @ENT_TEMP_ROOT SELECT Row_number() over(order by A.CR_ACCT),'',A.CR_ACCT, B.BRANCH_CODE CR_BRN, A.DR_ACCT, C.BRANCH_CODE DR_BRN, SUM(A.AMT) AS AMT,@TRN_DESC, A.MAST_PRICE_ID AS ASSET_ID,NULL, NULL

FROM MW_ENTRIES_POST A

LEFT JOIN CM_BRANCH B ON A.CR_BRN = B.BRANCH_ID

LEFT JOIN CM_BRANCH C ON A.DR_BRN = C.BRANCH_ID

WHERE TRN_ID = @p_REF_ID AND (A.DO_BRN = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')  AND (REF_NO = @p_RefNo OR @p_RefNo IS NULL OR @p_RefNo = '')

GROUP BY A.CR_ACCT, B.BRANCH_CODE, A.DR_ACCT, C.BRANCH_CODE,A.MAST_PRICE_ID

INSERT INTO @ENT_TEMP_ROOT_NOT_VAT SELECT Row_number() over(order by A.CR_ACCT),'',A.CR_ACCT, B.BRANCH_CODE CR_BRN, A.DR_ACCT, C.BRANCH_CODE DR_BRN, SUM(A.AMT) AS AMT,@TRN_DESC, A.MAST_PRICE_ID AS ASSET_ID,NULL, NULL

FROM MW_ENTRIES_POST A

LEFT JOIN CM_BRANCH B ON A.CR_BRN = B.BRANCH_ID

LEFT JOIN CM_BRANCH C ON A.DR_BRN = C.BRANCH_ID

WHERE TRN_ID = @p_REF_ID AND (A.DO_BRN = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')  AND (REF_NO = @p_RefNo OR @p_RefNo IS NULL OR @p_RefNo = '') AND TRN_TYPE <> 'MW_OUT_VAT'

GROUP BY A.CR_ACCT, B.BRANCH_CODE, A.DR_ACCT, C.BRANCH_CODE,A.MAST_PRICE_ID

INSERT INTO @ENT_TEMP_ROOT_VAT SELECT Row_number() over(order by A.CR_ACCT),'',A.CR_ACCT, B.BRANCH_CODE CR_BRN, A.DR_ACCT, C.BRANCH_CODE DR_BRN, SUM(A.AMT) AS AMT,@TRN_DESC, A.MAST_PRICE_ID AS ASSET_ID,NULL, NULL

FROM MW_ENTRIES_POST A

LEFT JOIN CM_BRANCH B ON A.CR_BRN = B.BRANCH_ID

LEFT JOIN CM_BRANCH C ON A.DR_BRN = C.BRANCH_ID

WHERE TRN_ID = @p_REF_ID AND (A.DO_BRN = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')  AND (REF_NO = @p_RefNo OR @p_RefNo IS NULL OR @p_RefNo = '') AND TRN_TYPE = 'MW_OUT_VAT'

GROUP BY A.CR_ACCT, B.BRANCH_CODE, A.DR_ACCT, C.BRANCH_CODE,A.MAST_PRICE_ID

--ORDER BY LEN(CR_ACCT)

--select * from @ENT_TEMP_ROOT

--DECLARE @COUNT INT = (SELECT COUNT(*) - COUNT(DISTINCT ASSET_ID) FROM ASS_ENTRIES_POST WHERE TRN_ID = @p_REF_ID)

--IF (SELECT COUNT(DISTINCT TRN_TYPE) FROM MW_ENTRIES_POST WHERE TRN_ID = @p_REF_ID) > 1 

--SET @TRN_TYPE = 'ADD_NEW'

--ELSE

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

----------------------------TACH 1 DONG DU LIEU THANH 2 DONG NAM LIEN KE NHAU DE BINDING WORD--------------------------

-------LAY TAI KHOAN GL TUONG UNG CASA TRONG CM_BRANCH

UPDATE @ENT_TEMP_ROOT SET CR_ACCT = B.TEL, CR_NAME = B.PROVICE

FROM CM_BRANCH B

INNER JOIN @ENT_TEMP_ROOT C ON C.CR_ACCT = B.DAO_CODE AND LEN(C.CR_ACCT)>9

UPDATE @ENT_TEMP_ROOT SET DR_ACCT = B.TEL, DR_NAME = B.PROVICE

FROM CM_BRANCH B

INNER JOIN @ENT_TEMP_ROOT C ON C.DR_ACCT = B.DAO_CODE AND LEN(C.DR_ACCT)>9

--IF @TRN_TYPE = 'ADD_NEW'  OR @TRN_TYPE = 'ASS_USE' OR @TRN_TYPE='ASS_TRANSFER' 

--BEGIN

	/*

	SELECT SUM(AG.AMT) AMT, AG.DR_ACCT, AG.DR_ACCT_NAME, AG.DR_BRN, AG.ACCT,SUM(REF_ID) REF_ID, AG.DR_D DR_D, AG.CR_C

	FROM

	(

		SELECT A.AMT, A.DR_ACCT AS DR_ACCT, ISNULL(A.DR_NAME,[dbo].[FN_GET_ACC_NAME](A.DR_ACCT)) AS DR_ACCT_NAME, '' AS DR_BRN, N'Nợ/Debit' AS ACCT,ID AS REF_ID, A.DR_BRN AS DR_D, A.CR_ACCT AS CR_C

		FROM @ENT_TEMP_ROOT A

		--WHERE LEN(A.DR_ACCT) <= 9

		UNION 

		SELECT A.AMT, A.CR_ACCT AS DR_ACCT, ISNULL(A.CR_NAME,[dbo].[FN_GET_ACC_NAME](A.CR_ACCT)) AS DR_ACCT_NAME, '' AS DR_BRN, N'Có/Credit' AS ACCT,ID AS REF_ID, A.CR_BRN AS DR_D, A.CR_ACCT AS CR_C

		FROM @ENT_TEMP_ROOT A

		--WHERE LEN(A.CR_ACCT) <= 9

		--ORDER BY ID, ACCT DESC

	) AG

	-- LUCTV 03062020 CHINH SUA THEO HĐ BAO HANH BAO TRI:  BO SUNG CHI LAY NHUNG  DONG HACH TOAN CO SO TIEN LON HON KHONG

	WHERE AG.AMT >0

	---

	GROUP BY AG.ACCT, AG.DR_ACCT, AG.DR_ACCT_NAME,AG.DR_BRN,AG.DR_ACCT, AG.DR_D, AG.CR_C

	ORDER BY AG.ACCT DESC, SUM(REF_ID), AG.CR_C, AG.DR_ACCT DESC

	*/

	SELECT SUM(AG.AMT) AMT, AG.DR_ACCT, AG.DR_ACCT_NAME, AG.DR_BRN, AG.ACCT,SUM(REF_ID) REF_ID, AG.DR_D DR_D, AG.CR_C

	FROM

	(

		SELECT A.AMT, A.DR_ACCT AS DR_ACCT, ISNULL(A.DR_NAME,[dbo].[FN_GET_ACC_NAME](A.DR_ACCT)) AS DR_ACCT_NAME, '' AS DR_BRN, N'Nợ/Debit' AS ACCT,ID AS REF_ID, A.DR_BRN AS DR_D, A.CR_ACCT AS CR_C

		FROM @ENT_TEMP_ROOT_NOT_VAT A

		UNION 

		SELECT A.AMT, A.CR_ACCT AS DR_ACCT, ISNULL(A.CR_NAME,[dbo].[FN_GET_ACC_NAME](A.CR_ACCT)) AS DR_ACCT_NAME, '' AS DR_BRN, N'Có/Credit' AS ACCT,ID AS REF_ID, A.CR_BRN AS DR_D, A.CR_ACCT AS CR_C

		FROM @ENT_TEMP_ROOT_NOT_VAT A

	) AG

	WHERE AG.AMT >0

	GROUP BY AG.ACCT, AG.DR_ACCT, AG.DR_ACCT_NAME,AG.DR_BRN,AG.DR_ACCT, AG.DR_D, AG.CR_C

	UNION

	SELECT SUM(AG.AMT) AMT, AG.DR_ACCT, AG.DR_ACCT_NAME, AG.DR_BRN, AG.ACCT,SUM(REF_ID) REF_ID, AG.DR_D DR_D, AG.CR_C

	FROM

	(

		SELECT A.AMT, A.DR_ACCT AS DR_ACCT, ISNULL(A.DR_NAME,[dbo].[FN_GET_ACC_NAME](A.DR_ACCT)) AS DR_ACCT_NAME, '' AS DR_BRN, N'Nợ/Debit' AS ACCT,ID AS REF_ID, A.DR_BRN AS DR_D, A.CR_ACCT AS CR_C

		FROM @ENT_TEMP_ROOT_VAT A

		UNION 

		SELECT A.AMT, A.CR_ACCT AS DR_ACCT, ISNULL(A.CR_NAME,[dbo].[FN_GET_ACC_NAME](A.CR_ACCT)) AS DR_ACCT_NAME, '' AS DR_BRN, N'Có/Credit' AS ACCT,ID AS REF_ID, A.CR_BRN AS DR_D, A.CR_ACCT AS CR_C

		FROM @ENT_TEMP_ROOT_VAT A

	) AG

	WHERE AG.AMT >0

	GROUP BY AG.ACCT, AG.DR_ACCT, AG.DR_ACCT_NAME,AG.DR_BRN,AG.DR_ACCT, AG.DR_D, AG.CR_C

--END

--ELSE

--BEGIN

--	--IF LEFT(@p_REF_ID,4) = 'ASSL'--THANH LY TAI SAN

--	SELECT SUM(AG.AMT) AMT, AG.DR_ACCT, AG.DR_ACCT_NAME, AG.DR_BRN, AG.ACCT,SUM(REF_ID) REF_ID, AG.DR_D DR_D

--	FROM

--	(

--		SELECT A.AMT, A.DR_ACCT AS DR_ACCT, [dbo].[FN_GET_ACC_NAME](A.DR_ACCT) AS DR_ACCT_NAME, A.ASSET_ID AS DR_BRN, N'Nợ/Debit' AS ACCT,ID AS REF_ID, A.DR_BRN AS DR_D

--		FROM @ENT_TEMP_ROOT A

--		UNION 

--		SELECT A.AMT, A.CR_ACCT AS DR_ACCT, [dbo].[FN_GET_ACC_NAME](A.CR_ACCT) AS DR_ACCT_NAME, A.ASSET_ID AS DR_BRN, N'Có/Credit' AS ACCT,ID AS REF_ID, A.CR_BRN AS DR_D

--		FROM @ENT_TEMP_ROOT A

--	--ORDER BY ID, ACCT DESC

--	) AG

--	GROUP BY AG.DR_ACCT, AG.DR_ACCT_NAME,AG.DR_BRN,AG.DR_ACCT,AG.ACCT, AG.DR_D

--	ORDER BY AG.DR_BRN, AG.ACCT DESC

--END

DELETE @ENT_TEMP

DELETE @ENT_TEMP_ROOT

--------------------------DONE--------------------------