CREATE OR ALTER PROCEDURE [dbo].[BUD_CONTRACT_RENT_Search]
	@p_BUDC_RENT_CODE	nvarchar(50)  = NULL,
	@p_BUDC_RENT_ID	varchar(15)  = NULL,
	@p_BUDC_RENT_TYPE	varchar(1)  = NULL,
	@p_BUDC_RENT_NAME	NVARCHAR(200)  = NULL,
	@p_SIGN_DT  VARCHAR(20)  = NULL,
	@p_CREATE_DT VARCHAR(50) = NULL,	
	@p_AUTH_STATUS VARCHAR(1) = NULL,
	@p_AUTH_STATUS_DC VARCHAR(1) = NULL,
	@p_START_DT VARCHAR(20) = NULL,
	@p_END_DT VARCHAR(20) = NULL,
	@p_PYC_ID varchar(15)=NULL,
	@p_TOP INT =10,
	@p_PYC_CODE VARCHAR(125) = NULL,
	@p_PYC_NAME NVARCHAR(250) = NULL,
	@p_CONT_TYPE VARCHAR(150) = NULL,
	@p_BRANCH_ID VARCHAR(15)= NULL,
	@p_BRANCH_LOGIN VARCHAR(15) = NULL,
	@p_LEVEL VARCHAR(15) = 'ALL',
	@p_MAKER_ID VARCHAR(100) = NULL,
	@p_BUDC_RENT_STATUS VARCHAR(1) = NULL, -- Ở màn hình tìm kiếm dùng biến này tìm kiếm hiệu lực
	@p_REQ_DOC_ID VARCHAR(15) = NULL,
	@p_USER_LOGIN VARCHAR(15) = NULL,
	@p_DEP_LOGIN VARCHAR(15) = NULL
    ---- Thêm location tìm kiếm theo điều kiện chức năng ------
    , @p_LOCATION VARCHAR(100) = NULL
    ,@p_END_CONTRACT_DT VARCHAR(20) = NULL
AS
BEGIN -- PAGING
	IF(@p_LEVEL IS NULL OR @p_LEVEL = '')
	BEGIN
		SET @p_LEVEL = 'ALL'
	END
    
--    IF(@p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
--	BEGIN
--		SET @p_MAKER_ID = @p_USER_LOGIN
--	END

	SET @p_TOP =2000
	declare @tmp table(BRANCH_ID varchar(15))
	insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)


IF(@p_TOP IS NULL OR @p_TOP = '' OR @p_TOP = 0)
	-- PAGING BEGIN
		SELECT A.*,
		 D.AUTH_STATUS_NAME,
		'' AS SO_PYC, '' AS TEN_PYC,
		CASE WHEN BR.BRANCH_TYPE = 'HS' THEN BR.BRANCH_NAME + ' - ' + CD.DEP_NAME ELSE BR.BRANCH_NAME END AS BRANCH_NAME, 
        BR.BRANCH_CODE, '' AS TO_TRINH_ID,
		0.0 AS TOTAL_ADV_AMT, ---Số tiền đã tạm ứng
		'' AS IS_FLAG_END, TU.TLFullName AS MAKER_NAME, TA.TLFullName AS CHECKER_NAME,
        CB.BRANCH_CODE AS BRANCH_RENT_CODE,
        CB.BRANCH_NAME AS BRANCH_RENT_NAME,
        CD1.DEP_CODE AS DEP_RENT_CODE,
        CD1.DEP_NAME AS DEP_RENT_NAME,
        CASE
        	WHEN DATEDIFF(MONTH, GETDATE(),A.END_DT) <= 0 THEN N'Đã hết hạn'
        	ELSE CAST(DATEDIFF(MONTH, GETDATE(),A.END_DT) AS VARCHAR(50)) + N' Tháng'
        END AS END_CONTRACT_DT
        
        -- SELECT END
		FROM BUD_CONTRACT_RENT A
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH BR ON A.BRANCH_ID= BR.BRANCH_ID
		LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID= CD.DEP_ID
		LEFT JOIN TL_USER TU ON A.MAKER_ID = TU.TLNANME
		LEFT JOIN TL_USER TA ON A.CHECKER_ID = TA.TLNANME
    LEFT JOIN CM_BRANCH CB ON A.BRANCH_RENT_ID = CB.BRANCH_ID --ĐƠN VỊ THUÊ
    LEFT JOIN CM_DEPARTMENT CD1 ON A.DEP_RENT_ID = CD1.DEP_ID --PHÒNG BAN THUÊ

		WHERE 1=1
		AND (A.MAKER_ID like '%'+@p_MAKER_ID+'%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID like '')
		AND (A.BUDC_RENT_CODE like '%'+@p_BUDC_RENT_CODE+'%' OR @p_BUDC_RENT_CODE IS NULL OR @p_BUDC_RENT_CODE like '')
		AND (A.BUDC_RENT_ID like '%'+@p_BUDC_RENT_ID+'%' OR @p_BUDC_RENT_ID IS NULL OR @p_BUDC_RENT_ID like '')
		AND (A.BUDC_RENT_NAME like '%'+ @p_BUDC_RENT_NAME +'%' OR @p_BUDC_RENT_NAME IS NULL OR @p_BUDC_RENT_NAME like '')
		AND (A.AUTH_STATUS like '%'+@p_AUTH_STATUS +'%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS like '')
		AND (A.AUTH_STATUS_DC like '%'+@p_AUTH_STATUS_DC +'%' OR @p_AUTH_STATUS_DC IS NULL OR @p_AUTH_STATUS_DC like '')
        AND (DATEDIFF(DAY, CONVERT(DATETIME, @p_CREATE_DT,103),A.CREATE_DT) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		AND (A.BUDC_RENT_TYPE like '%'+@p_BUDC_RENT_TYPE +'%' OR @p_BUDC_RENT_TYPE IS NULL OR @p_BUDC_RENT_TYPE like '')
		AND A.RECORD_STATUS = '1'
		AND ((@p_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp)
    		OR (@p_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_LOGIN)) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL
    		AND (A.BRANCH_ID =@p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='')
	    )
        
        AND (@p_LOCATION IS NULL OR @p_LOCATION = '' OR @p_LOCATION = 'BUD_MASTER' OR @p_LOCATION = 'BUD_CONTRACT'
                OR (@p_LOCATION = 'UPDATE_RENT' AND A.AUTH_STATUS = 'A')
            )
        AND (
            @p_END_CONTRACT_DT = '3T' AND DATEDIFF(MONTH, GETDATE(),A.END_DT)<=3
            OR (@p_END_CONTRACT_DT = '6T' AND DATEDIFF(MONTH, GETDATE(),A.END_DT)<=6)
            OR (@p_END_CONTRACT_DT = '9T' AND DATEDIFF(MONTH, GETDATE(),A.END_DT)<=9)
            OR @p_END_CONTRACT_DT IS NULL OR @p_END_CONTRACT_DT = ''
        )
        AND ((@p_BUDC_RENT_STATUS = 'C' AND DATEDIFF(MONTH, GETDATE(),A.END_DT) > 0)
            OR (@p_BUDC_RENT_STATUS = 'H' AND DATEDIFF(MONTH, GETDATE(),A.END_DT) <= 0)
            OR @p_BUDC_RENT_STATUS IS NULL OR @p_BUDC_RENT_STATUS = ''
        )
        OR A.MAKER_ID = @p_USER_LOGIN
	ORDER BY A.CREATE_DT DESC
	-- PAGING END
ELSE
    -- PAGING BEGIN
		SELECT TOP(CONVERT(INT,@p_TOP)) A.*,
		 D.AUTH_STATUS_NAME,
		'' AS SO_PYC, '' AS TEN_PYC,
		CASE WHEN BR.BRANCH_TYPE = 'HS' THEN BR.BRANCH_NAME + ' - ' + CD.DEP_NAME ELSE BR.BRANCH_NAME END AS BRANCH_NAME, BR.BRANCH_CODE, '' AS TO_TRINH_ID,
		0.0 AS TOTAL_ADV_AMT, ---Số tiền đã tạm ứng
		'' AS IS_FLAG_END, TU.TLFullName AS MAKER_NAME, TA.TLFullName AS CHECKER_NAME,
        CB.BRANCH_CODE AS BRANCH_RENT_CODE,
        CB.BRANCH_NAME AS BRANCH_RENT_NAME,
        CD1.DEP_CODE AS DEP_RENT_CODE,
        CD1.DEP_NAME AS DEP_RENT_NAME,
        CASE
        	WHEN DATEDIFF(MONTH, GETDATE(),A.END_DT) <= 0 THEN N'Đã hết hạn'
        	ELSE CAST(DATEDIFF(MONTH, GETDATE(),A.END_DT) AS VARCHAR(50)) + N' Tháng'
        END AS END_CONTRACT_DT

    -- SELECT END
		FROM BUD_CONTRACT_RENT A
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH BR ON A.BRANCH_ID= BR.BRANCH_ID
		LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID= CD.DEP_ID
		LEFT JOIN TL_USER TU ON A.MAKER_ID = TU.TLNANME
		LEFT JOIN TL_USER TA ON A.CHECKER_ID = TA.TLNANME
    LEFT JOIN CM_BRANCH CB ON A.BRANCH_RENT_ID = CB.BRANCH_ID --ĐƠN VỊ THUÊ
    LEFT JOIN CM_DEPARTMENT CD1 ON A.DEP_RENT_ID = CD1.DEP_ID --PHÒNG BAN THUÊ

		WHERE 1=1
		AND (A.MAKER_ID like '%'+@p_MAKER_ID+'%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID like '')
		AND (A.BUDC_RENT_CODE like '%'+@p_BUDC_RENT_CODE+'%' OR @p_BUDC_RENT_CODE IS NULL OR @p_BUDC_RENT_CODE like '')
		AND (A.BUDC_RENT_ID like '%'+@p_BUDC_RENT_ID+'%' OR @p_BUDC_RENT_ID IS NULL OR @p_BUDC_RENT_ID like '')
		AND (A.BUDC_RENT_NAME like '%'+ @p_BUDC_RENT_NAME +'%' OR @p_BUDC_RENT_NAME IS NULL OR @p_BUDC_RENT_NAME like '')
		AND (A.AUTH_STATUS like '%'+@p_AUTH_STATUS +'%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS like '')
		AND (A.AUTH_STATUS_DC like '%'+@p_AUTH_STATUS_DC +'%' OR @p_AUTH_STATUS_DC IS NULL OR @p_AUTH_STATUS_DC like '')
        AND (DATEDIFF(DAY, CONVERT(DATETIME, @p_CREATE_DT,103),A.CREATE_DT) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
        AND (A.BUDC_RENT_TYPE like '%'+@p_BUDC_RENT_TYPE +'%' OR @p_BUDC_RENT_TYPE IS NULL OR @p_BUDC_RENT_TYPE like '')
		AND A.RECORD_STATUS = '1'
		AND ((@p_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp)
    		OR (@p_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_LOGIN)) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL
    		AND (A.BRANCH_ID =@p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='')
	    )
        AND (@p_LOCATION IS NULL OR @p_LOCATION = '' OR @p_LOCATION = 'BUD_MASTER' OR @p_LOCATION = 'BUD_CONTRACT'
             OR (@p_LOCATION = 'UPDATE_RENT' AND A.AUTH_STATUS = 'A')
            )
        AND (
            @p_END_CONTRACT_DT = '3T' AND DATEDIFF(MONTH, GETDATE(),A.END_DT)<=3
            OR (@p_END_CONTRACT_DT = '6T' AND DATEDIFF(MONTH, GETDATE(),A.END_DT)<=6)
            OR (@p_END_CONTRACT_DT = '9T' AND DATEDIFF(MONTH, GETDATE(),A.END_DT)<=9)
            OR @p_END_CONTRACT_DT IS NULL OR @p_END_CONTRACT_DT = ''
        )
        AND ((@p_BUDC_RENT_STATUS = 'C' AND DATEDIFF(MONTH, GETDATE(),A.END_DT) > 0)
            OR (@p_BUDC_RENT_STATUS = 'H' AND DATEDIFF(MONTH, GETDATE(),A.END_DT) <= 0)
            OR @p_BUDC_RENT_STATUS IS NULL OR @p_BUDC_RENT_STATUS = ''
        )
        OR A.MAKER_ID = @p_USER_LOGIN
		ORDER BY A.CREATE_DT DESC
		-- PAGING END
END -- PAGING