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

ALTER PROCEDURE [dbo].[rpt_ASS_PRINT_TEMP_Excel]

	@p_ASSET_ID			varchar(MAX) = NULL,

	@p_ASSET_CODE		nvarchar(100) = NULL,

	@p_ASSET_NAME		nvarchar(1000) = NULL,

	@p_BRANCH_ID		varchar(15) = NULL,

	@p_BRANCH_LOGIN		varchar(15) = NULL,

	@p_ASSET_TYPE VARCHAR(15) = NULL,--Them truong hop khong chon nhom tai san

	@p_ASSET_GROUP VARCHAR(15) = NULL,

	@p_FROM_NUM VARCHAR(10) = NULL,

	@p_TO_NUM VARCHAR(10) = NULL,

	@p_Fromdate varchar(20) = null,

	@p_Todate VARCHAR(20) = NULL,

	@p_DEPT_ID varchar(15) = NULL,--thieuvq	Search theo phong ban

	@p_SerialNo VARCHAR(MAX) = NULL,--- luctv search theo ngay nhap

	@p_AddNewID VARCHAR(20) = NULL,--- luctv search ma phieu nhap 16092019

	@p_Use_MasterID VARCHAR(20) = NULL--- luctv search theo ma phieu xuat 16092019

AS

BEGIN

	--IF @p_TOP = '' SET @p_TOP = 1000000

	declare @tmp table(BRANCH_ID varchar(15))

	insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)

--- khai bao table chua ds ma tai san

		DECLARE @l_LSTASSETCODE TABLE (

		[ID] [int] IDENTITY(1,1) NOT NULL,

		[VALUE] [NVARCHAR](MAX) NULL)

		INSERT INTO @l_LSTASSETCODE SELECT VALUE FROM WSISPLIT(@p_ASSET_ID,',')

	-- khai bao table chua ds seria

		DECLARE @l_LSTSERI TABLE (

		[ID] [int] IDENTITY(1,1) NOT NULL,

		[VALUE] [NVARCHAR](MAX) NULL)

		INSERT INTO @l_LSTSERI SELECT VALUE FROM WSISPLIT(@p_SerialNo,',')	

		-- MA CODE TAI SAN

			--- khai bao table chua ds ma code tai san

		DECLARE @l_LSTASSETCODE_V1 TABLE (

		[ID] [int] IDENTITY(1,1) NOT NULL,

		[VALUE] [VARCHAR](MAX) NULL)

		INSERT INTO @l_LSTASSETCODE_V1 SELECT VALUE FROM WSISPLIT(@p_ASSET_CODE,',')

		SELECT

				ROW_NUMBER() OVER(ORDER BY A.ASSET_ID) AS STT,

				C.[TYPE_NAME] LOAI_TS,

				B.GROUP_NAME NHOM_TS,

				 A.ASSET_CODE MA_TS,

				 A.ASSET_NAME, 

				 A.ASSET_SERIAL_NO,

				 A.NOTES,

				ISNULL(DP.DEP_NAME,BR.BRANCH_NAME) DVSD,

				A.CREATE_DT NGAYNHAP_TS,

				A.USE_DATE NGAY_SD		

		FROM ASS_MASTER A

		LEFT JOIN ASS_GROUP B ON A.GROUP_ID = B.GROUP_ID

		LEFT JOIN ASS_TYPE C ON A.[TYPE_ID] = C.[TYPE_ID]

		LEFT JOIN ASS_AMORT_STATUS D ON A.AMORT_STATUS = D.STATUS_CODE

		LEFT JOIN ASS_STATUS E ON A.ASS_STATUS = E.STATUS_ID

		LEFT JOIN CM_AUTH_STATUS ZZ ON ZZ.AUTH_STATUS = A.AUTH_STATUS

		LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID=A.BRANCH_ID

		LEFT JOIN TL_USER EM ON EM.TLNANME=A.EMP_ID

		LEFT JOIN CM_DEPARTMENT DP ON DP.DEP_ID = A.DEPT_ID

		LEFT JOIN ASS_TRANSACTIONS AT ON AT.ASSET_ID = A.ASSET_ID AND AT.TRN_TYPE ='ADD_NEW'

		LEFT JOIN ASS_TRANSACTIONS AT2 ON AT2.ASSET_ID = A.ASSET_ID AND AT2.TRN_TYPE ='ADD_USE' AND @p_Use_MasterID <>'' AND @p_Use_MasterID IS NOT NULL

		WHERE 1 = 1

			AND AMORT_STATUS <> 'VNM'  AND  AMORT_STATUS <> 'DTL'

			--AND (A.ASSET_ID = @p_ASSET_ID OR @p_ASSET_ID IS NULL OR @p_ASSET_ID = '')

			AND (A.ASSET_ID IN (SELECT VALUE FROM @l_LSTASSETCODE) OR @p_ASSET_ID IS NULL OR @p_ASSET_ID ='')

			AND (A.ASSET_CODE IN (SELECT VALUE FROM @l_LSTASSETCODE_V1) OR @p_ASSET_CODE IS NULL OR @p_ASSET_CODE ='')

			AND (A.ASSET_NAME like N'%' + @p_ASSET_NAME + '' OR @p_ASSET_NAME IS NULL OR @p_ASSET_NAME = '')

			--AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@P_BRANCH_ID)) OR @P_BRANCH_ID IS NULL OR @P_BRANCH_ID = '')

			--AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp) OR @p_BRANCH_LOGIN IS NULL OR @p_BRANCH_LOGIN = '')

			AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')

			AND (B.GROUP_ID = @p_ASSET_GROUP OR @p_ASSET_GROUP IS NULL OR @p_ASSET_GROUP = '')

			AND (A.TYPE_ID = @p_ASSET_TYPE OR @p_ASSET_TYPE IS NULL OR @p_ASSET_TYPE = '')

			--AND ((CONVERT(INT,SUBSTRING(A.ASSET_CODE,2 + LEN(B.GROUP_CODE),LEN(A.ASSET_CODE) - 1 - 

			--LEN(B.GROUP_CODE))) >= CONVERT(INT,@p_FROM_NUM)) OR @p_FROM_NUM = '' OR @p_FROM_NUM IS NULL)

			--AND ((CONVERT(INT,SUBSTRING(A.ASSET_CODE,2 + LEN(B.GROUP_CODE),LEN(A.ASSET_CODE) - 1 - 

			--LEN(B.GROUP_CODE))) <= CONVERT(INT,@p_TO_NUM)) OR @p_TO_NUM = '' OR @p_TO_NUM IS NULL)

			AND ((CONVERT(DECIMAL,RIGHT(A.ASSET_CODE,4))>= CONVERT(INT,@p_FROM_NUM)) OR @p_FROM_NUM = '' OR @p_FROM_NUM IS NULL)

			AND ((CONVERT(DECIMAL,RIGHT(A.ASSET_CODE,4)) <= CONVERT(INT,@p_TO_NUM)) OR @p_TO_NUM = '' OR @p_TO_NUM IS NULL)

			AND(A.BUY_DATE_KT <= CONVERT(DATETIME,@p_Todate,103) OR @p_Todate= '' OR @p_Todate IS NULL)

			AND	(A.BUY_DATE_KT >= CONVERT(datetime, @p_Fromdate, 103) OR @p_Fromdate IS NULL OR @p_Fromdate = '')

			AND A.RECORD_STATUS = '1'

			AND (A.DEPT_ID = @p_DEPT_ID OR @p_DEPT_ID IS NULL OR @p_DEPT_ID = '')-- THIEUVQ THEM 19_03_2014

			--AND (A.ASSET_SERIAL_NO = @p_SerialNo OR @p_SerialNo IS NULL OR @p_SerialNo = '')

			AND (A.ASSET_SERIAL_NO IN (SELECT VALUE FROM @l_LSTSERI) OR @p_SerialNo IS NULL OR @p_SerialNo ='')

			AND (AT.TRN_ID = @p_AddNewID OR @p_AddNewID ='' OR @p_AddNewID IS NULL)

			AND (AT2.TRN_ID = @p_Use_MasterID OR @p_Use_MasterID ='' OR @p_Use_MasterID IS NULL)

END