ALTER PROCEDURE dbo.rpt_TSCD_BC017_Excel_BanViet
	-- Add the parameters for the stored procedure here
	@p_BRANCH_ID varchar(30) = NULL,
	@p_FromDate varchar(30) = NULL,
	@p_ToDate varchar(15) = NULL,
	@p_BRANCH_LOGIN varchar(15) = NULL,
	@p_LEVEL varchar(10) = 'ALL',
	@p_ASSET_TYPE varchar(15) = '', -- loai tai san TSCD/CCLD
	@p_PriceFrom varchar(18) = null, -- nguyen gia tu
	@p_PriceTo varchar(18) = null, -- nguyen gia den
	@p_SupplierId varchar(15) = null, -- nha cung cap
	@p_ASSET_GROUP_ID varchar(15) = null, -- Nhom tai san
	@p_Branch_ID_Use varchar(15) = null ,-- don vi su dung
	@p_User_ID varchar(15) = null -- nguoi su dung	
AS
BEGIN
-- Do du lieu ao

declare @tmp table(BRANCH_ID varchar(15))
insert into @tmp  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)
declare @tmp_login table(BRANCH_ID varchar(15))
insert into @tmp_login  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
if @p_PriceFrom = '0' and @p_PriceTo = '0'
	begin
		set	@p_PriceFrom = null
		set	@p_PriceTo = null
	end
	SELECT 
	Row_number() over(order by L.LIQ_ID) AS STT,
	[dbo].[FN_GET_GROUPNAME](M.GROUP_ID,'1') AS NHOMTS,
--		[dbo].[FN_GET_GROUPNAME](A.GROUP_ID,'1') AS NHOMTSLV1, --EM GOM NHOM THEO 3 CAI THUOC TINH NAY NHA
	[dbo].[FN_GET_GROUPNAME](M.GROUP_ID,'2') AS NHOM_LV2,
	[dbo].[FN_GET_GROUPNAME](M.GROUP_ID,'3') AS NHOM_LV3,
	--G.GROUP_NAME NHOMTS, -- NHOM TAI SAN
	M.ASSET_CODE MATS,
	M.ASSET_NAME TENTS,
	M.BUY_DATE NGAYNHAP, -- NGAY NHAP
	M.BUY_PRICE NGUYENGIA,
	--L.APPROVE_DT NGAY_TL, -- NGAY THANH LY
	L.APPROVE_DT_KT NGAY_TL, --
	--M.AMORTIZED_MONTH AS KHLK_NGAY_TL, -- KHAU HAO LUY KE DEN NGAY THANH LY
	(M.AMORT_AMT - LD.REMAIN_VALUE) KHLK_NGAY_TL,
	--M.BUY_PRICE - M.AMORTIZED_MONTH AS GTCL_NGAY_TL, -- GIA TRI CON LAI NGAY THANH LY
	LD.REMAIN_VALUE GTCL_NGAY_TL,
	LD.LIQ_PRICE GTTH, -- GIA TRI THU HOI
	--(M.BUY_PRICE - M.AMORTIZED_MONTH - LD.LIQ_PRICE) AS LAI_LO, -- LAI~ HAY LO~
	(LD.LIQ_AMT_BE_VAT - LD.REMAIN_VALUE) LAI_LO,
	LD.LIQ_PRICE AS GT_TL,
	L.BUY_COMPANY_ID, --  DON VI MUA
	CASE WHEN M.BRANCH_ID <>'' THEN BR.BRANCH_NAME ELSE BR_CR.BRANCH_NAME END DVSD, -- DON VI SU DUNG
	CASE WHEN M.BRANCH_ID <>'' THEN BR.BRANCH_CODE ELSE BR_CR.BRANCH_CODE END BRANCH_CODE, -- DON VI SU DUNG
	DP.DEP_NAME PHONGBAN_SD,DP.DEP_CODE, -- PHONG BAN SU DUNG
	M.AMORT_START_DATE NGAYBD_KHPB, -- NGAY BAT DAU KHAU HAO PHAN BO
	M.AMORT_MONTH THOIGIAN_KH,-- THOI GIAN KHAU HAO PHAN BO
	M.AMORT_MONTH  - (M.AMORTIZED_MONTH - ISNULL(AMT.ST_KH_PD_TAM,0)) SOTHANG_KH_CL ,-- SO THANG HAU HAO CON LAI,
	M.BRANCH_CREATE, M.BRANCH_ID
	from ASS_LIQUIDATION L
	LEFT JOIN ASS_LIQUIDATION_DT LD ON L.LIQ_ID = LD.LIQ_ID
	LEFT JOIN ASS_MASTER M ON LD.ASSET_ID = M.ASSET_ID
	LEFT JOIN ASS_GROUP G ON  M.GROUP_ID =G.GROUP_ID 
	LEFT JOIN CM_BRANCH BR ON M.BRANCH_ID = BR.BRANCH_ID
	LEFT JOIN CM_BRANCH BR_CR ON M.BRANCH_CREATE = BR_CR.BRANCH_ID
	LEFT JOIN CM_DEPARTMENT DP ON M.DEPT_ID = DP.DEP_ID
	-- TINH SO THANG DA KHAU HAO = AMORTED_MONTH - SỐ THÁNG ĐÃ KHẤU HAO : AMORT_DT > TO_DATE
		LEFT JOIN 
		(
			--SELECT AM_DT.ASSET_ID, ISNULL(SUM(ISNULL(AM_DT.AMORT_AMT,0)),0) AS ST_DA_PB FROM ASS_AMORT_DT AM_DT 
			SELECT AM_DT.ASSET_ID, ISNULL(COUNT (*),0) AS ST_KH_PD_TAM FROM ASS_AMORT_DT AM_DT 
			WHERE CONVERT(DATE,AM_DT.AMORT_DT) > CONVERT(DATE,@p_ToDate,103)
			AND AM_DT.CRDR='C'
			GROUP BY AM_DT.ASSET_ID
		) AMT ON AMT.ASSET_ID = M.ASSET_ID 
	-- END
	-- SELECT * FROM ASS_LIQUIDATION
	WHERE 
	--(M.BRANCH_ID =  @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID  = '')
	--LUCTV: 26-12-2018: CHUYEN TU APPROVE HANH CHINH SANG APPROVE KE TOAN
	--LUCTV : 03-12-2018: CHI LAY NHUNG THANH LY DA DUOC DUYET
	(L.AUTH_STATUS_KT='A')
	AND(CONVERT(datetime,L.APPROVE_DT_KT,103)>= CONVERT(datetime,  @p_FromDate, 103) OR @p_FromDate IS NULL OR @p_FromDate  = '')
	AND (CONVERT(datetime,L.APPROVE_DT_KT,103)<= CONVERT(datetime,  @p_ToDate, 103) OR @p_ToDate IS NULL OR @p_ToDate  = '')
	AND (M.TYPE_ID = @p_ASSET_TYPE OR @p_ASSET_TYPE IS NULL OR @p_ASSET_TYPE  = '')
	AND (M.BUY_PRICE >= CONVERT(decimal(18),@p_PriceFrom) OR @p_PriceFrom IS NULL OR @p_PriceFrom  = '')
	AND (M.BUY_PRICE <= CONVERT(decimal(18),@p_PriceTo) OR @p_PriceTo IS NULL OR @p_PriceTo  = '')
	AND (M.SUP_ID = @p_SupplierId OR @p_SupplierId IS NULL OR @p_SupplierId  = '')
	AND (M.GROUP_ID = @p_ASSET_GROUP_ID OR @p_ASSET_GROUP_ID IS NULL OR @p_ASSET_GROUP_ID  = '')
	AND (M.EMP_ID = @p_User_ID OR @p_User_ID IS NULL OR @p_User_ID  = '')
	-- PHAN QUYEN
	--AND ((@p_LEVEL = 'ALL' AND M.BRANCH_ID IN (select BRANCH_ID from @tmp) or M.BRANCH_ID='' or M.BRANCH_ID is null )
	--OR (@p_LEVEL = 'UNIT' AND M.BRANCH_ID = @p_BRANCH_ID) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)
	--AND ((@p_LEVEL = 'ALL' AND M.BRANCH_ID IN (select BRANCH_ID from @tmp_login)or M.BRANCH_ID='' or M.BRANCH_ID is null)
	--OR (@p_LEVEL = 'UNIT' AND M.BRANCH_ID = @p_BRANCH_LOGIN) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL 
	--OR @p_BRANCH_ID IS NOT NULL OR @p_BRANCH_ID <> '')
	--LUCTV: 04-12-2019 : BỔ SUNG THÊM NẾU CHƯA XUẤT SỬ DỤNG THÌ LỌC TÀI SẢN THEO ĐƠN VỊ TẠO
	--AND ((@p_LEVEL = 'ALL' AND M.BRANCH_ID IN (select BRANCH_ID from @tmp) OR (M.BRANCH_ID IS NULL OR M.BRANCH_ID ='' AND M.BRANCH_CREATE IN(SELECT BRANCH_ID FROM @tmp)))
	--OR (@p_LEVEL = 'UNIT' AND M.BRANCH_ID = @p_BRANCH_ID OR (M.BRANCH_ID IS NULL OR M.BRANCH_ID ='' AND M.BRANCH_CREATE = @p_BRANCH_ID)))
	--AND ((@p_LEVEL = 'ALL' AND M.BRANCH_ID IN (select BRANCH_ID from @tmp_login) OR (M.BRANCH_ID IS NULL OR M.BRANCH_ID ='' AND M.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp_login)))
	--OR (@p_LEVEL = 'UNIT' AND M.BRANCH_ID = @p_BRANCH_LOGIN  OR (M.BRANCH_ID IS NULL OR M.BRANCH_ID ='' AND M.BRANCH_CREATE =@p_BRANCH_LOGIN)))
	AND ((@p_LEVEL = 'ALL' AND (M.BRANCH_ID IN (select BRANCH_ID from @tmp) OR (M.BRANCH_ID ='' AND M.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp))))
		OR (@p_LEVEL = 'UNIT' AND((M.BRANCH_ID ='' AND M.BRANCH_CREATE = @p_BRANCH_ID) OR M.BRANCH_ID = @p_BRANCH_ID)))
	--AND ((@p_LEVEL = 'ALL' AND (M.BRANCH_ID IN (select BRANCH_ID from @tmp_login) OR (M.BRANCH_ID ='' AND M.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp_login))))
		--luctv 01/01/19 bo dieu kien Unit di vi dieu kien nay bi sai.
		--OR (@p_LEVEL = 'UNIT' AND((M.BRANCH_ID ='' AND M.BRANCH_CREATE = @p_BRANCH_LOGIN)OR M.BRANCH_ID =@p_BRANCH_LOGIN))
		--)
	AND ((M.BRANCH_ID IN (select BRANCH_ID from @tmp_login) OR (M.BRANCH_ID ='' AND M.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp_login))))
	--@p_BRANCH_LOGIN varchar(15) = NULL,
	--@p_LEVEL varchar(10) = 'ALL',
	
	--@p_Branch_ID_Use varchar(15) = null ,-- don vi su dung
	--@p_User_ID varchar(15) = null -- nguoi su dung
	--SELECT TOP 2 * FROM ASS_MASTER
	--SELECT * FROM ASS_LIQUIDATION
	--SELECT * FROM ASS_LIQUIDATION_DT
	--SELECT * FROM ASS_GROUP

    IF(
    SELECT COUNT(M.BRANCH_ID)
    from ASS_LIQUIDATION L
	LEFT JOIN ASS_LIQUIDATION_DT LD ON L.LIQ_ID = LD.LIQ_ID
	LEFT JOIN ASS_MASTER M ON LD.ASSET_ID = M.ASSET_ID
	LEFT JOIN ASS_GROUP G ON  M.GROUP_ID =G.GROUP_ID 
	LEFT JOIN CM_BRANCH BR ON M.BRANCH_ID = BR.BRANCH_ID
	LEFT JOIN CM_BRANCH BR_CR ON M.BRANCH_CREATE = BR_CR.BRANCH_ID
	LEFT JOIN CM_DEPARTMENT DP ON M.DEPT_ID = DP.DEP_ID
		LEFT JOIN 
		(
			SELECT AM_DT.ASSET_ID, ISNULL(COUNT (*),0) AS ST_KH_PD_TAM FROM ASS_AMORT_DT AM_DT 
			WHERE CONVERT(DATE,AM_DT.AMORT_DT) > CONVERT(DATE,@p_ToDate,103)
			AND AM_DT.CRDR='C'
			GROUP BY AM_DT.ASSET_ID
		) AMT ON AMT.ASSET_ID = M.ASSET_ID 
	WHERE 
	(L.AUTH_STATUS_KT='A')
	AND(CONVERT(datetime,L.APPROVE_DT_KT,103)>= CONVERT(datetime,  @p_FromDate, 103) OR @p_FromDate IS NULL OR @p_FromDate  = '')
	AND (CONVERT(datetime,L.APPROVE_DT_KT,103)<= CONVERT(datetime,  @p_ToDate, 103) OR @p_ToDate IS NULL OR @p_ToDate  = '')
	AND (M.TYPE_ID = @p_ASSET_TYPE OR @p_ASSET_TYPE IS NULL OR @p_ASSET_TYPE  = '')
	AND (M.BUY_PRICE >= CONVERT(decimal(18),@p_PriceFrom) OR @p_PriceFrom IS NULL OR @p_PriceFrom  = '')
	AND (M.BUY_PRICE <= CONVERT(decimal(18),@p_PriceTo) OR @p_PriceTo IS NULL OR @p_PriceTo  = '')
	AND (M.SUP_ID = @p_SupplierId OR @p_SupplierId IS NULL OR @p_SupplierId  = '')
	AND (M.GROUP_ID = @p_ASSET_GROUP_ID OR @p_ASSET_GROUP_ID IS NULL OR @p_ASSET_GROUP_ID  = '')
	AND (M.EMP_ID = @p_User_ID OR @p_User_ID IS NULL OR @p_User_ID  = '')
	AND ((@p_LEVEL = 'ALL' AND (M.BRANCH_ID IN (select BRANCH_ID from @tmp) OR (M.BRANCH_ID ='' AND M.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp))))
		OR (@p_LEVEL = 'UNIT' AND((M.BRANCH_ID ='' AND M.BRANCH_CREATE = @p_BRANCH_ID) OR M.BRANCH_ID = @p_BRANCH_ID)))
	AND ((M.BRANCH_ID IN (select BRANCH_ID from @tmp_login) OR (M.BRANCH_ID ='' AND M.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp_login))))
    GROUP BY M.BRANCH_ID) = 1
    BEGIN
    	SELECT TOP 1 L.MAKER_ID_KT AS GDV, L.CHECKER_ID_KT AS KSV
        from ASS_LIQUIDATION L
	LEFT JOIN ASS_LIQUIDATION_DT LD ON L.LIQ_ID = LD.LIQ_ID
	LEFT JOIN ASS_MASTER M ON LD.ASSET_ID = M.ASSET_ID
	LEFT JOIN ASS_GROUP G ON  M.GROUP_ID =G.GROUP_ID 
	LEFT JOIN CM_BRANCH BR ON M.BRANCH_ID = BR.BRANCH_ID
	LEFT JOIN CM_BRANCH BR_CR ON M.BRANCH_CREATE = BR_CR.BRANCH_ID
	LEFT JOIN CM_DEPARTMENT DP ON M.DEPT_ID = DP.DEP_ID
		LEFT JOIN 
		(
			SELECT AM_DT.ASSET_ID, ISNULL(COUNT (*),0) AS ST_KH_PD_TAM FROM ASS_AMORT_DT AM_DT 
			WHERE CONVERT(DATE,AM_DT.AMORT_DT) > CONVERT(DATE,@p_ToDate,103)
			AND AM_DT.CRDR='C'
			GROUP BY AM_DT.ASSET_ID
		) AMT ON AMT.ASSET_ID = M.ASSET_ID 
	WHERE 
	(L.AUTH_STATUS_KT='A')
	AND(CONVERT(datetime,L.APPROVE_DT_KT,103)>= CONVERT(datetime,  @p_FromDate, 103) OR @p_FromDate IS NULL OR @p_FromDate  = '')
	AND (CONVERT(datetime,L.APPROVE_DT_KT,103)<= CONVERT(datetime,  @p_ToDate, 103) OR @p_ToDate IS NULL OR @p_ToDate  = '')
	AND (M.TYPE_ID = @p_ASSET_TYPE OR @p_ASSET_TYPE IS NULL OR @p_ASSET_TYPE  = '')
	AND (M.BUY_PRICE >= CONVERT(decimal(18),@p_PriceFrom) OR @p_PriceFrom IS NULL OR @p_PriceFrom  = '')
	AND (M.BUY_PRICE <= CONVERT(decimal(18),@p_PriceTo) OR @p_PriceTo IS NULL OR @p_PriceTo  = '')
	AND (M.SUP_ID = @p_SupplierId OR @p_SupplierId IS NULL OR @p_SupplierId  = '')
	AND (M.GROUP_ID = @p_ASSET_GROUP_ID OR @p_ASSET_GROUP_ID IS NULL OR @p_ASSET_GROUP_ID  = '')
	AND (M.EMP_ID = @p_User_ID OR @p_User_ID IS NULL OR @p_User_ID  = '')
	AND ((@p_LEVEL = 'ALL' AND (M.BRANCH_ID IN (select BRANCH_ID from @tmp) OR (M.BRANCH_ID ='' AND M.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp))))
		OR (@p_LEVEL = 'UNIT' AND((M.BRANCH_ID ='' AND M.BRANCH_CREATE = @p_BRANCH_ID) OR M.BRANCH_ID = @p_BRANCH_ID)))
	AND ((M.BRANCH_ID IN (select BRANCH_ID from @tmp_login) OR (M.BRANCH_ID ='' AND M.BRANCH_CREATE IN (SELECT BRANCH_ID FROM @tmp_login))))
    END
END