ALTER  PROCEDURE [dbo].[rpt_NhapXuatTonKhoVatLieuDuTru_BanViet]
@p_Fromdate VARCHAR(20) =NULL, @p_Todate VARCHAR(20) =NULL, @p_Branch_ID VARCHAR(15) =NULL, @p_Level VARCHAR(10) =NULL,@p_WARE_HOUSE VARCHAR(15) = NULL,
@p_BRANCH_LOGIN VARCHAR(15) =NULL
AS
DECLARE @BRANCH_TMP TABLE ( BRANCH_ID VARCHAR(15), BRANCH_NAME NVARCHAR(200))

DECLARE @tmp TABLE(BRANCH_ID VARCHAR(15));
INSERT INTO @tmp
SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_Branch_ID);

IF(@p_LEVEL='ALL')
	INSERT INTO @BRANCH_TMP
	SELECT A.BRANCH_ID,(SELECT BRANCH_NAME FROM CM_BRANCH CM WHERE CM.BRANCH_ID =A.BRANCH_ID) FROM [dbo].[CM_BRANCH_GETCHILDID](@p_Branch_ID) A; 
ELSE
	INSERT INTO @BRANCH_TMP
	SELECT BRANCH_ID,BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =@p_Branch_ID

--DECLARE @tmp TABLE(BRANCH_ID VARCHAR(15));
--INSERT INTO @tmp
--SELECT BRANCH_ID FROM CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_LOGIN;

DECLARE @BRANCH_NAME NVARCHAR(1000)
SET @BRANCH_NAME = (SELECT BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =@p_Branch_ID)


DECLARE @TABLE_REPORT TABLE(STT INT,LIQ_ACCTNO NVARCHAR(40),MATERIAL_CODE NVARCHAR(100),GROUP_NAME NVARCHAR(100),MATERIAL_NAME NVARCHAR(100),
MATERIAL_ACCTNO NVARCHAR(40),SLTonDau NUMERIC(18,0),
THANHTIENTonDau NUMERIC(18,0),SLNhap NUMERIC(18,0),THANHTIENNhap NUMERIC(18,0),SLXuatLe NUMERIC(18,0),ThanhTienXuatLe NUMERIC(18,0),SLDieuChuyen NUMERIC(18,0),ThanhTienDieuChuyen NUMERIC(18,0),
SLXuatTapTrung NUMERIC(18,0),ThanhTienXuatTapTrung NUMERIC(18,0),SLTonCuoi NUMERIC(18,0),THANHTIENTonCuoi NUMERIC(18,0),
UNIT_NAME NVARCHAR(50),GROUP_ID VARCHAR(50), GROUP_LEVEL1 VARCHAR(15), GROUP_LEVEL2 VARCHAR(15), DonGia DECIMAL(18,0), SLThuHoi NUMERIC(18,0),ThanhTienThuHoi NUMERIC(18,0), NOTES NVARCHAR(1000),DVSD NVARCHAR(1000), ORDER_IDE INT)
BEGIN
-- LEVEL 1
INSERT INTO @TABLE_REPORT SELECT AA.*,'5' FROM
(
	SELECT Row_number() over(order by BRT.BRANCH_NAME) AS STT, GR.LIQ_ACCTNO, F.PRICE_CODE MATERIAL_CODE,GR.GROUP_NAME,MI.MATERIAL_NAME, '' MATERIAL_ACCTNO, 
	ISNULL(A.SOLUONG_TON_DAU,0) SLTonDau, 
	ISNULL(A.SOTIEN_TONDAU,0) THANHTIENTonDau,
	ISNULL(A5.NHAP_TRONG_KI,0) SLNhap, 
	ISNULL(A5.SOTIEN_NHAP_TRONG_KI,0) THANHTIENNhap,
	ISNULL(A1.SOLUONG_XUAT_DONLE,0) AS SLXuatLe,
	ISNULL(A1.SOTIEN_XUAT_DONLE,0) AS ThanhTienXuatLe,
	ISNULL(TF.SOLUONG_DIEU_CHUYEN,0) AS SLDieuChuyen,
	ISNULL(TF.SOTIEN_DIEU_CHUYEN,0) AS ThanhTienDieuChuyen,
	 ISNULL(A2.SOLUONG_XUAT_TAP_TRUNG,0) AS SLXuatTapTrung,
	ISNULL(A2.SOTIEN_XUAT_TAP_TRUNG,0) AS ThanhTienXuatTapTrung, 
	(ISNULL(A4.SOLUONG_TON_CUOI,0) -ISNULL(NOT_TF.SOLUONG_DIEU_CHUYEN,0))  SLTonCuoi,(ISNULL(A4.SOTIEN_TONCUOI,0) -ISNULL(NOT_TF.SOTIEN_DIEU_CHUYEN,0)) THANHTIENTonCuoi,UN.UNIT_NAME,
	--ISNULL(DBO.FN_GET_MW_GROUP_ID(F.GROUP_ID,1),DBO.FN_GET_MW_GROUP_ID(F.GROUP_ID,1)) GROUP_ID,
	MI.GROUP_ID  GROUP_ID,
	[dbo].[FN_GET_MW_GROUP_ID](MI.GROUP_ID,1) GROUP_LEVE1, 
	[dbo].[FN_GET_MW_GROUP_ID](MI.GROUP_ID,2) GROUP_LEVEL2, F.PRICE, TH.SOLUONG_THU_HOI, TH.SOTIEN_THU_HOI,MI.NOTES,( BRT.BRANCH_NAME) AS BRANCH_NAME
	FROM @BRANCH_TMP BRT
	LEFT JOIN 
	(
	SELECT PRICE_ID, BRANCH_ID FROM dbo.MW_MAST_BAL 
	GROUP BY PRICE_ID, BRANCH_ID
	) MB ON MB.BRANCH_ID = BRT.BRANCH_ID
	LEFT JOIN MW_MAST_PRICE F ON F.PRICE_ID=MB.PRICE_ID
	LEFT JOIN MW_IN MI ON F.MATERIAL_ID = MI.IN_ID
	LEFT JOIN MW_IN_MASTER MST ON MI.IN_MASTER_ID = MST.IN_ID
	LEFT JOIN MW_MATERIAL MT ON MI.MATERIAL_ID = MT.MATERIAL_ID
	LEFT JOIN
	(
		SELECT PR.PRICE_ID,B.BRANCH_ID, SUM (CASE WHEN A.CRDR='C' THEN A.QTY ELSE -A.QTY END) AS SOLUONG_TON_DAU,
		SUM (CASE WHEN A.CRDR='C' THEN A.TOTAL_AMT ELSE -A.TOTAL_AMT END) AS SOTIEN_TONDAU
		FROM
		MW_MAST_BAL_STMT A 
		LEFT JOIN MW_MAST_BAL B ON A.MAST_BAL_ID = B.MAST_BAL_ID
		LEFT JOIN MW_MAST_PRICE PR ON B.PRICE_ID = PR.PRICE_ID
		WHERE CONVERT(DATE,A.TRN_DATE, 103) < CONVERT(DATE, @p_Fromdate,103) 		
		AND (EXISTS (SELECT [@BRANCH_TMP].BRANCH_ID FROM @BRANCH_TMP WHERE [@BRANCH_TMP].BRANCH_ID=B.BRANCH_ID))
		GROUP BY PR.PRICE_ID,B.BRANCH_ID
	) A ON F.PRICE_ID = A.PRICE_ID AND A.BRANCH_ID=BRT.BRANCH_ID

	LEFT JOIN
	(
		SELECT PR.PRICE_ID,B.BRANCH_ID, SUM (CASE WHEN A.CRDR='D' THEN A.QTY ELSE 0 END) AS SOLUONG_XUAT_DONLE,
		SUM (CASE WHEN A.CRDR='D' THEN A.TOTAL_AMT ELSE 0 END) AS SOTIEN_XUAT_DONLE
		FROM
		MW_MAST_BAL_STMT A 
		LEFT JOIN MW_MAST_BAL B ON A.MAST_BAL_ID = B.MAST_BAL_ID
		LEFT JOIN MW_MAST_PRICE PR ON B.PRICE_ID = PR.PRICE_ID
		WHERE A.TRN_TYPE IN ('O','T','C') AND CONVERT(DATE,A.TRN_DATE, 103) >= CONVERT(DATE, @p_Fromdate,103)
		AND CONVERT(DATE,A.TRN_DATE, 103) <= CONVERT(DATE, @p_Todate,103) 
		AND (EXISTS (SELECT [@BRANCH_TMP].BRANCH_ID FROM @BRANCH_TMP WHERE [@BRANCH_TMP].BRANCH_ID=B.BRANCH_ID))
		GROUP BY PR.PRICE_ID,B.BRANCH_ID
	) A1 ON F.PRICE_ID = A1.PRICE_ID  AND A1.BRANCH_ID=BRT.BRANCH_ID
	LEFT JOIN
	(
		SELECT PR.PRICE_ID,B.BRANCH_ID, SUM (CASE WHEN A.CRDR='D' THEN A.QTY ELSE 0 END) AS SOLUONG_XUAT_TAP_TRUNG,
		SUM (CASE WHEN A.CRDR='D' THEN A.TOTAL_AMT ELSE 0 END) AS SOTIEN_XUAT_TAP_TRUNG
		FROM
		MW_MAST_BAL_STMT A 
		LEFT JOIN MW_MAST_BAL B ON A.MAST_BAL_ID = B.MAST_BAL_ID
		LEFT JOIN MW_MAST_PRICE PR ON B.PRICE_ID = PR.PRICE_ID
		WHERE A.TRN_TYPE ='L' AND CONVERT(DATE,A.TRN_DATE, 103) >= CONVERT(DATE, @p_Fromdate,103)
		AND CONVERT(DATE,A.TRN_DATE, 103) <= CONVERT(DATE, @p_Todate,103)
		AND (EXISTS (SELECT [@BRANCH_TMP].BRANCH_ID FROM @BRANCH_TMP WHERE [@BRANCH_TMP].BRANCH_ID=B.BRANCH_ID))
		GROUP BY PR.PRICE_ID,B.BRANCH_ID
	) A2 ON F.PRICE_ID = A2.PRICE_ID AND A2.BRANCH_ID=BRT.BRANCH_ID
	LEFT JOIN
	(
		SELECT PR.PRICE_ID,B.BRANCH_ID, SUM (CASE WHEN A.CRDR='C' THEN A.QTY ELSE 0 END) AS SOLUONG_DIEU_CHUYEN,
		SUM (CASE WHEN A.CRDR='C' THEN A.TOTAL_AMT ELSE 0 END) AS SOTIEN_DIEU_CHUYEN
		FROM
		MW_MAST_BAL_STMT A 
		LEFT JOIN MW_MAST_BAL B ON A.MAST_BAL_ID = B.MAST_BAL_ID
		LEFT JOIN MW_MAST_PRICE PR ON B.PRICE_ID = PR.PRICE_ID
		WHERE A.TRN_TYPE IN ('T','C') AND CONVERT(DATE,A.TRN_DATE, 103) >= CONVERT(DATE, @p_Fromdate,103)
		AND CONVERT(DATE,A.TRN_DATE, 103) <= CONVERT(DATE, @p_Todate,103)
		AND  (EXISTS ( SELECT TRANSFER_ID FROM dbo.MW_TRANSFER_CONF WHERE TRANSFER_ID=A.REF_ID AND BRANCH_RECIVE=B.BRANCH_ID))
		AND (EXISTS (SELECT [@BRANCH_TMP].BRANCH_ID FROM @BRANCH_TMP WHERE [@BRANCH_TMP].BRANCH_ID=B.BRANCH_ID))
		GROUP BY PR.PRICE_ID,B.BRANCH_ID
	) TF ON F.PRICE_ID = TF.PRICE_ID AND TF.BRANCH_ID=BRT.BRANCH_ID
	LEFT JOIN
	(
		SELECT PR.PRICE_ID,B.BRANCH_ID, SUM (CASE WHEN A.CRDR='D' THEN A.QTY ELSE 0 END) AS SOLUONG_THU_HOI,
		SUM (CASE WHEN A.CRDR='D' THEN A.TOTAL_AMT ELSE 0 END) AS SOTIEN_THU_HOI
		FROM
		MW_MAST_BAL_STMT A 
		LEFT JOIN MW_MAST_BAL B ON A.MAST_BAL_ID = B.MAST_BAL_ID
		LEFT JOIN MW_MAST_PRICE PR ON B.PRICE_ID = PR.PRICE_ID
		WHERE A.TRN_TYPE ='C' AND CONVERT(DATE,A.TRN_DATE, 103) >= CONVERT(DATE, @p_Fromdate,103)
		AND CONVERT(DATE,A.TRN_DATE, 103) <= CONVERT(DATE, @p_Todate,103)
		AND (EXISTS (SELECT [@BRANCH_TMP].BRANCH_ID FROM @BRANCH_TMP WHERE [@BRANCH_TMP].BRANCH_ID=B.BRANCH_ID))
		GROUP BY PR.PRICE_ID,B.BRANCH_ID
	) TH ON F.PRICE_ID = TH.PRICE_ID AND TH.BRANCH_ID=BRT.BRANCH_ID
	LEFT JOIN
	(
		SELECT PR.PRICE_ID,B.BRANCH_ID, SUM (CASE WHEN A.CRDR='C' THEN A.QTY ELSE -A.QTY END) AS SOLUONG_TON_CUOI,
		SUM (CASE WHEN A.CRDR='C' THEN A.TOTAL_AMT ELSE -A.TOTAL_AMT END) AS SOTIEN_TONCUOI
		FROM
		MW_MAST_BAL_STMT A 
		LEFT JOIN MW_MAST_BAL B ON A.MAST_BAL_ID = B.MAST_BAL_ID
		LEFT JOIN MW_MAST_PRICE PR ON B.PRICE_ID = PR.PRICE_ID
		WHERE CONVERT(DATE,A.TRN_DATE, 103) <= CONVERT(DATE, @p_Todate,103)
		AND (EXISTS (SELECT [@BRANCH_TMP].BRANCH_ID FROM @BRANCH_TMP WHERE [@BRANCH_TMP].BRANCH_ID=B.BRANCH_ID))
		GROUP BY PR.PRICE_ID,B.BRANCH_ID
	) A4 ON F.PRICE_ID = A4.PRICE_ID AND A4.BRANCH_ID=BRT.BRANCH_ID
	LEFT JOIN
	(
		SELECT PR.PRICE_ID,B.BRANCH_ID, SUM (CASE WHEN A.CRDR='C' THEN A.QTY ELSE 0 END) AS NHAP_TRONG_KI,
		SUM (CASE WHEN A.CRDR='C' THEN A.TOTAL_AMT ELSE 0 END) AS SOTIEN_NHAP_TRONG_KI
		FROM
		MW_MAST_BAL_STMT A 
		LEFT JOIN MW_MAST_BAL B ON A.MAST_BAL_ID = B.MAST_BAL_ID
		LEFT JOIN MW_MAST_PRICE PR ON B.PRICE_ID = PR.PRICE_ID
		WHERE A.TRN_TYPE  IN ('I','O') AND CONVERT(DATE,A.TRN_DATE, 103) >= CONVERT(DATE, @p_Fromdate,103) AND CONVERT(DATE,A.TRN_DATE, 103) <= CONVERT(DATE, @p_Todate,103) 
		AND (EXISTS (SELECT [@BRANCH_TMP].BRANCH_ID FROM @BRANCH_TMP WHERE [@BRANCH_TMP].BRANCH_ID=B.BRANCH_ID))
		GROUP BY PR.PRICE_ID,B.BRANCH_ID
	) A5 ON F.PRICE_ID = A5.PRICE_ID AND A5.BRANCH_ID= BRT.BRANCH_ID
	-- TÍNH SỐ LƯỢNG ĐIỀU CHUYỂN VL NHƯNG CHƯA ĐƯỢC XÁC NHẬN
	LEFT JOIN
	(
		SELECT PR.PRICE_ID,B.BRANCH_ID, SUM (CASE WHEN A.CRDR='C' THEN A.QTY ELSE 0 END) AS SOLUONG_DIEU_CHUYEN,
		SUM (CASE WHEN A.CRDR='C' THEN A.TOTAL_AMT ELSE 0 END) AS SOTIEN_DIEU_CHUYEN
		FROM
		MW_MAST_BAL_STMT A 
		LEFT JOIN MW_MAST_BAL B ON A.MAST_BAL_ID = B.MAST_BAL_ID
		LEFT JOIN MW_MAST_PRICE PR ON B.PRICE_ID = PR.PRICE_ID
		WHERE A.TRN_TYPE IN ('T','C') AND CONVERT(DATE,A.TRN_DATE, 103) >= CONVERT(DATE, @p_Fromdate,103)
		AND CONVERT(DATE,A.TRN_DATE, 103) <= CONVERT(DATE, @p_Todate,103)
		AND  (NOT EXISTS ( SELECT TRANSFER_ID FROM dbo.MW_TRANSFER_CONF WHERE TRANSFER_ID=A.REF_ID AND BRANCH_RECIVE=B.BRANCH_ID))
		AND (EXISTS (SELECT [@BRANCH_TMP].BRANCH_ID FROM @BRANCH_TMP WHERE [@BRANCH_TMP].BRANCH_ID=B.BRANCH_ID))
		GROUP BY PR.PRICE_ID,B.BRANCH_ID
	) NOT_TF ON F.PRICE_ID = NOT_TF.PRICE_ID AND NOT_TF.BRANCH_ID=BRT.BRANCH_ID
	----------
	LEFT JOIN MW_GROUP GR ON MI.GROUP_ID = GR.GROUP_ID	
	LEFT JOIN CM_UNIT UN ON MT.UNIT_ID = UN.UNIT_ID
	WHERE MST.WARE_HOUSE =@p_WARE_HOUSE
	-- LUCTV BO SUNG DIEU KIEN CHI LAY NHUNG LO VAT LIEU DUOC NHAP KHO TRONG KHOANG THOI GIAN TU NGAY DEN NGAY
	--AND (CONVERT(DATE,MST.APPROVE_DT_KT,103) >= CONVERT (DATE,@p_Fromdate,103) OR @p_Fromdate IS NULL OR @p_Fromdate ='')
	AND (CONVERT(DATE,MST.APPROVE_DT_KT,103) <= CONVERT (DATE,@p_Todate,103) OR @p_Todate IS NULL OR @p_Todate ='')
) AA
SELECT A.* FROM @TABLE_REPORT A
ORDER BY A.DVSD
END