AMS - VB

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