DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

/*

rpt_TSCD_BC017  'DV0002','1/12/2013','31/12/2013','DV0002','ALL','TSCD',

'0','0','','','',''

*/

ALTER PROCEDURE [dbo].[rpt_TSCD_BC017_Excel]

	-- Add the parameters for the stored procedure here

	@p_Branch_id varchar(15) = NULL,

	@p_fromDate varchar(15) = 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,

	[dbo].[FN_GET_GROUPNAME](M.GROUP_ID,'4') AS NHOM_LV4,

	--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

	--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_PRICE - LD.REMAIN_VALUE) LAI_LO,

	LD.LIQ_PRICE AS GT_TL,

	L.BUY_COMPANY_ID --  DON VI MUA

	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 

	-- SELECT * FROM ASS_LIQUIDATION

	WHERE 

	--(M.BRANCH_ID =  @p_Branch_id OR @p_Branch_id IS NULL OR @p_Branch_id  = '')

	 (L.APPROVE_DT >= CONVERT(DATETIME,  @p_fromDate, 103) OR @p_fromDate IS NULL OR @p_fromDate  = '')

	AND (L.APPROVE_DT <= 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 (@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 (@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 <> '')

	AND (L.AUTH_STATUS_KT ='A')

	--@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

END