ALTER PROCEDURE dbo.rpt_CCLD_BC8_2
	@sp_Fromdate VARCHAR(20) = NULL,
	@sp_Todate VARCHAR(20) = NULL,
	@sp_DVSD VARCHAR(15) = NULL,
	@sp_CCLD_Type varchar(15) = NULL,
	@sp_Supplier varchar(15) = NULL,
	@sp_BRANCH_ID VARCHAR(15) = NULL,
	@sp_BRANCH_LOGIN VARCHAR(15) = NULL,
	@sp_LEVEL VARCHAR(10) = 'ALL',
	@sp_PriceFrom varchar(15) = Null,
	@sp_PriceTo varchar(15) = null,
	@sp_Dep_ID VARCHAR(15)= NULL

AS
BEGIN
--thieuvq 24/8/2017 - DOC DU LIEU BO QUA COMMIT TRANSACTION 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

declare @tmp table(BRANCH_ID varchar(15))
insert into @tmp  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@sp_BRANCH_ID)
declare @tmp_login table(BRANCH_ID varchar(15))
insert into @tmp_login  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@sp_BRANCH_LOGIN)


SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT,
--F.MA_CCLD,
--F.TEN_CCLD,
SUM(F.SL_GIATRI_TONDAUKY) AS SL_GIATRI_TONDAUKY,
SUM(F.SL_GIATRI_NHAPTRONGKY) AS SL_GIATRI_NHAPTRONGKY,
SUM(F.SL_GIATRI_XUATTRONGKY) AS SL_GIATRI_XUATTRONGKY,
SUM(F.SL_GIATRI_TONCUOIKY) AS SL_GIATRI_TONCUOIKY,
SUM(F.GIATRI_TONDAUKY) AS GIATRI_TONDAUKY, 
SUM(F.GIATRI_NHAPTRONGKY) AS GIATRI_NHAPTRONGKY,
SUM(F.GIATRI_XUATTRONGKY) AS GIATRI_XUATTRONGKY,
SUM(F.GIATRI_TONCUOIKY) AS GIATRI_TONCUOIKY,
F.MA_CHI_NHANH_KHO AS MA_CHI_NHANH,
--F.TEN_CHINHANH_KHO,
'' GHI_CHU,
F.MA_NHOM_CCLD_1,
F.MA_NHOM_CCLD_2,
F.MA_NHOM_CCLD_3,
F.NHOM_CCLD_1,
F.NHOM_CCLD_2,
F.NHOM_CCLD_3,
F.NHOM_CCLD_4,
F.UNIT_NAME_CCLD_3
FROM
(	--NHAP KHO
	SELECT 
		STT,
		MA_CCLD,
		TEN_CCLD,
		0 GIATRI_TONDAUKY, 
		GIATRI AS GIATRI_NHAPTRONGKY,
		0 GIATRI_XUATTRONGKY,
		GIATRI AS GIATRI_TONCUOIKY,

    0 AS SL_GIATRI_TONDAUKY,
    1 AS SL_GIATRI_NHAPTRONGKY,
    0 AS SL_GIATRI_XUATTRONGKY,
    1 AS SL_GIATRI_TONCUOIKY,

		MA_CHI_NHANH,
		TEN_CHINHANH,
		GHI_CHU,
		NHOM_CCLD_1,
		NHOM_CCLD_2,
		NHOM_CCLD_3,
		NHOM_CCLD_4,
		MA_NHOM_CCLD_1,
		MA_NHOM_CCLD_2,    
    MA_NHOM_CCLD_3,
		MA_CHI_NHANH_KHO,
		TEN_CHI_NHANH_KHO,
    UNIT_NAME_CCLD_3
	FROM [dbo].[fn_NHAP_KHO](@sp_Fromdate,@sp_Todate,@sp_DVSD,
	@sp_CCLD_Type,@sp_Supplier,@sp_BRANCH_ID,@sp_BRANCH_LOGIN,@sp_LEVEL,@sp_PriceFrom,@sp_PriceTo,@sp_Dep_ID)
	UNION ALL
	--XUAT KHO
	SELECT 
		STT,
		MA_CCLD,
		TEN_CCLD,
		CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN GIATRI
		ELSE 0
		END AS GIATRI_TONDAUKY,
		0 GIATRI_NHAPTRONGKY,

		GIATRI AS GIATRI_XUATTRONGKY,
		 
		CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN 0
		ELSE -GIATRI
		END AS GIATRI_TONCUOIKY,

    CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN 1	ELSE 0 END AS SL_GIATRI_TONDAUKY,
    0 SL_GIATRI_NHAPTRONGKY,
    1 AS SL_GIATRI_XUATTRONGKY,
    CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN 0 ELSE -1 END AS SL_GIATRI_TONCUOIKY,

		DVSD AS MA_CHI_NHANH,
		TEN_CHINHANH,
		GHI_CHU,
		NHOM_CCLD_1,
		NHOM_CCLD_2,
		NHOM_CCLD_3,
		NHOM_CCLD_4,
		MA_NHOM_CCLD_1,
		MA_NHOM_CCLD_2,
    MA_NHOM_CCLD_3,
		MA_CHI_NHANH_KHO,
		TEN_CHI_NHANH_KHO,
    UNIT_NAME_CCLD_3
	FROM [dbo].[fn_XUAT_KHO](@sp_Fromdate,@sp_Todate,@sp_DVSD,'',@sp_CCLD_Type,@sp_Supplier,
	@sp_BRANCH_ID,@sp_BRANCH_LOGIN,@sp_LEVEL,@sp_PriceFrom,@sp_PriceTo,@sp_Dep_ID)
	UNION ALL 
	--TON KHO
	SELECT 
		STT,
		MA_CCLD,
		TEN_CCLD,
		CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN GIATRI
		ELSE 0
		END AS GIATRI_TONDAUKY,

		0 GIATRI_NHAPTRONGKY,

		0 AS GIATRI_XUATTRONGKY,

		CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN GIATRI
		ELSE 0
		END AS GIATRI_TONCUOIKY,



    CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN 1 ELSE 0 END AS SL_GIATRI_TONDAUKY,
		0 AS SL_GIATRI_NHAPTRONGKY,
		0 AS SL_GIATRI_XUATTRONGKY,
		CASE WHEN (BUY_DATE_KT < CONVERT(datetime, @sp_Fromdate, 103)) THEN 1 ELSE 0 END AS SL_GIATRI_TONCUOIKY,

		MA_CHINHANH,
		TEN_CHINHANH,
		GHI_CHU,
		NHOM_CCLD_1,
		NHOM_CCLD_2,
		NHOM_CCLD_3,
		NHOM_CCLD_4,
		MA_NHOM_CCLD_1,
		MA_NHOM_CCLD_2,
    MA_NHOM_CCLD_3,
		MA_CHI_NHANH_KHO,
		TEN_CHI_NHANH_KHO,
    UNIT_NAME_CCLD_3
	FROM [dbo].[fn_TON_KHO](@sp_Todate,@sp_DVSD,@sp_CCLD_Type,@sp_Supplier,
	@sp_BRANCH_ID,@sp_BRANCH_LOGIN,@sp_LEVEL,@sp_PriceFrom,@sp_PriceTo,@sp_Dep_ID)
)F
--ORDER BY F.MA_CCLD
GROUP BY --F.MA_CCLD,F.TEN_CCLD,
F.MA_NHOM_CCLD_1,F.MA_NHOM_CCLD_2,F.MA_NHOM_CCLD_3,F.MA_CHI_NHANH_KHO,F.NHOM_CCLD_1,F.NHOM_CCLD_2,NHOM_CCLD_3,NHOM_CCLD_4,F.UNIT_NAME_CCLD_3
END