DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

ALTER PROCEDURE [dbo].[ASS_USE_CONFIRM_MASTER_Search]

@p_USER_MASTER_ID	varchar(15)  = NULL,

@p_BRANCH_ID	varchar(15)  = NULL,

@p_USE_EXPORT_DT	VARCHAR(20) = NULL,

@p_USER_EXPORT	nvarchar(200)  = NULL,

@p_USER_EXPORT_NAME	Nvarchar(200)  = NULL,

@p_NOTES	nvarchar(1000)  = NULL,

@p_RECORD_STATUS	varchar(1)  = NULL,

@p_AUTH_STATUS	varchar(1)  = NULL,

@p_MAKER_ID	varchar(15)  = NULL,

@p_CREATE_DT	VARCHAR(20) = NULL,

@p_CHECKER_ID	varchar(15)  = NULL,

@p_APPROVE_DT	VARCHAR(20) = NULL,

@p_AUTH_STATUS_KT	varchar(15)  = NULL,

@p_CREATE_DT_KT	VARCHAR(20) = NULL,

@p_APPROVE_DT_KT	VARCHAR(20) = NULL,

@p_MAKER_ID_KT	varchar(50)  = NULL,

@p_CHECKER_ID_KT	varchar(50)  = NULL,

@p_REPORT_STATUS	varchar(15)  = NULL,

@p_KT_IS_DO			VARCHAR(1) = NULL,

@p_LEVEL	VARCHAR(50) = 'UNIT',

@p_TOP	INT = 10,

@p_FROMDATE VARCHAR(20) = NULL,

@p_TODATE VARCHAR(20) = NULL,

@p_USER_LOGIN   VARCHAR(15),-- 26022020 LUCTV TRUYEN XUONG USER LOGIN DE DO TIM MA PHONG BAN

@p_TYPE_SEARCH VARCHAR(10)= NULL -- 26022020 LUCTV TRUYEN XUONG DAU HIEU DE PHAN BIET TIM KIEM O MAN HINH KE TOAN HAY MAN HINH HCQT

AS

BEGIN -- PAGING

	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

	------

	SET @p_RECORD_STATUS = '1'

	-------

	DECLARE @BRANCH_LOGIN VARCHAR(15)

		SET @BRANCH_LOGIN = (SELECT TOP 1 TLSUBBRID FROM TL_USER WHERE TLNANME = @p_USER_LOGIN)

IF(@p_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)

-- PAGING BEGIN

	SELECT A.*,C.BRANCH_CODE, C.BRANCH_NAME, F.CONTENT AS AUTH_STATUS_NAME,C.BRANCH_CODE+' - '+ C.BRANCH_NAME AS FULL_BRANCH_NAME,

	ATKT.CONTENT AS AUTH_STATUS_NAME_KT,

	@p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH, 

	B.CONFIRM_STATUS, dbo.FN_ASS_USE_MULTI_MASTER_GET_TOTAL_VALUE(A.USER_MASTER_ID) AS TOTAL_USE_VALUE,D.TLFullName CONFIRM_USER_NAME,B.CONFIRM_DT,

	CASE WHEN B.CONFIRM_STATUS = 'Y'

			THEN N'Đã xác nhận'  

			ELSE N'Chưa xác nhận' END AS CONFIRM_STATUS_NAME

-- SELECT END

	FROM ASS_USE_MULTI_MASTER A

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

	LEFT JOIN dbo.CM_ALLCODE F ON F.CDVAL = A.AUTH_STATUS AND F.CDNAME = 'ASS_ADD_AUTH' AND F.CDTYPE = 'ASS_ADDNEW'

	LEFT JOIN dbo.CM_ALLCODE ATKT ON ATKT.CDVAL = A.AUTH_STATUS_KT AND ATKT.CDNAME = 'ASS_ADD_AUTH' AND ATKT.CDTYPE = 'ASS_ADDNEW'

	LEFT JOIN  (SELECT TOP 1 USER_MASTER_ID,DT.BRANCH_ID,DEPT_ID FROM ASS_USE_MULTI_DT DT ORDER BY USE_MULTI_ID ASC) tmp ON A.USER_MASTER_ID = tmp.USER_MASTER_ID

	--INNER JOIN (SELECT BRANCH_ID,USER_MASTER_ID,COUNT(*) AS SL FROM ASS_USE_MULTI_DT WHERE BRANCH_ID = @BRANCH_LOGIN GROUP BY BRANCH_ID,USER_MASTER_ID) DTT ON DTT.USER_MASTER_ID = A.USER_MASTER_ID   

	 LEFT JOIN ASS_USE_CONFIRM_MASTER B ON A.USER_MASTER_ID = B.USER_MASTER_ID

	 LEFT JOIN TL_USER D ON B.MAKER_ID = D.TLNANME

	WHERE 1 = 1

	AND (A.USER_MASTER_ID LIKE '%' + @p_USER_MASTER_ID + '%' OR  @p_USER_MASTER_ID IS NULL OR @p_USER_MASTER_ID = '')

	AND (CONVERT(DATE,ISNULL(A.APPROVE_DT,A.USE_EXPORT_DT),103) >=CONVERT(DATE, @p_FROMDATE, 103) OR  @p_FROMDATE IS NULL OR @p_FROMDATE = '')

	AND (CONVERT(DATE,ISNULL(A.APPROVE_DT, A.USE_EXPORT_DT),103) <=CONVERT(DATETIME, @p_TODATE, 103) OR  @p_TODATE IS NULL OR @p_TODATE = '')

	AND (A.USER_EXPORT LIKE '%' + @p_USER_EXPORT + '%' OR  @p_USER_EXPORT IS NULL OR @p_USER_EXPORT = '')

	AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')	 

	AND A.AUTH_STATUS = 'A' 

  AND A.AUTH_STATUS_KT <>'A'

  AND A.APPROVE_DT>=CONVERT(DATETIME,'2022-17-10',103)

	  --AND ( (@p_AUTH_STATUS = 'Y' AND A.USER_MASTER_ID = B.USER_MASTER_ID) 

			--OR (@p_AUTH_STATUS = 'N' AND A.USER_MASTER_ID <> B.USER_MASTER_ID

			--OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))

	 AND ( (@p_AUTH_STATUS = 'Y' AND A.USER_MASTER_ID IN (SELECT G.USER_MASTER_ID FROM ASS_USE_CONFIRM_MASTER G)) 

			OR (@p_AUTH_STATUS = 'N' AND A.USER_MASTER_ID NOT IN (SELECT G.USER_MASTER_ID FROM ASS_USE_CONFIRM_MASTER G)

			OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))

   --TIM KIEM THEO TRUONG DON VI NHAN TAI SAN

   --AND (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',

   -- tmp.BRANCH_ID,

   -- tmp.DEPT_ID) US

   -- WHERE US.TLNANME = @p_USER_LOGIN))

  AND (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',

    (SELECT TOP 1 DT.BRANCH_ID FROM ASS_USE_MULTI_DT DT WHERE DT.USER_MASTER_ID = A.USER_MASTER_ID ORDER BY USE_MULTI_ID ASC),

    (SELECT TOP 1 DT.DEPT_ID FROM ASS_USE_MULTI_DT DT WHERE DT.USER_MASTER_ID = A.USER_MASTER_ID ORDER BY USE_MULTI_ID ASC)) US

    WHERE US.TLNANME = @p_USER_LOGIN))

	ORDER BY A.CREATE_DT DESC

-- PAGING END

	ELSE

-- PAGING BEGIN

	SELECT TOP(CONVERT(INT,@P_TOP))A.*,C.BRANCH_CODE, C.BRANCH_NAME, F.CONTENT AS AUTH_STATUS_NAME, C.BRANCH_CODE+' - '+ C.BRANCH_NAME AS FULL_BRANCH_NAME,

	ATKT.CONTENT AS AUTH_STATUS_NAME_KT,

	@p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH,

	B.CONFIRM_STATUS, dbo.FN_ASS_USE_MULTI_MASTER_GET_TOTAL_VALUE(A.USER_MASTER_ID) AS TOTAL_USE_VALUE,D.TLFullName CONFIRM_USER_NAME,B.CONFIRM_DT,

	CASE WHEN B.CONFIRM_STATUS = 'Y'

			THEN N'Đã xác nhận'  

			ELSE N'Chưa xác nhận' END AS CONFIRM_STATUS_NAME

-- SELECT END

		FROM ASS_USE_MULTI_MASTER A

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

		LEFT JOIN dbo.CM_ALLCODE F ON F.CDVAL = A.AUTH_STATUS AND F.CDNAME = 'ASS_ADD_AUTH' AND F.CDTYPE = 'ASS_ADDNEW'

		LEFT JOIN dbo.CM_ALLCODE ATKT ON ATKT.CDVAL = A.AUTH_STATUS_KT AND ATKT.CDNAME = 'ASS_ADD_AUTH' AND ATKT.CDTYPE = 'ASS_ADDNEW'

		LEFT JOIN  (SELECT TOP 1 USER_MASTER_ID,DT.BRANCH_ID,DEPT_ID FROM ASS_USE_MULTI_DT DT ORDER BY USE_MULTI_ID ASC) tmp ON A.USER_MASTER_ID = tmp.USER_MASTER_ID

		--INNER JOIN (SELECT BRANCH_ID,USER_MASTER_ID,COUNT(*) AS SL FROM ASS_USE_MULTI_DT WHERE BRANCH_ID = @BRANCH_LOGIN GROUP BY BRANCH_ID,USER_MASTER_ID) DTT ON DTT.USER_MASTER_ID = A.USER_MASTER_ID   

		LEFT JOIN ASS_USE_CONFIRM_MASTER B ON A.USER_MASTER_ID = B.USER_MASTER_ID

		LEFT JOIN TL_USER D ON B.MAKER_ID = D.TLNANME

		WHERE 1 = 1

		AND (A.USER_MASTER_ID LIKE '%' + @p_USER_MASTER_ID + '%' OR  @p_USER_MASTER_ID IS NULL OR @p_USER_MASTER_ID = '')

		AND (CONVERT(DATE,ISNULL(A.APPROVE_DT,A.USE_EXPORT_DT),103) >=CONVERT(DATE, @p_FROMDATE, 103) OR  @p_FROMDATE IS NULL OR @p_FROMDATE = '')

		AND (CONVERT(DATE,ISNULL(A.APPROVE_DT,A.USE_EXPORT_DT),103) <=CONVERT(DATETIME, @p_TODATE, 103) OR  @p_TODATE IS NULL OR @p_TODATE = '')

		AND (A.USER_EXPORT LIKE '%' + @p_USER_EXPORT + '%' OR  @p_USER_EXPORT IS NULL OR @p_USER_EXPORT = '')

		AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')

		AND A.AUTH_STATUS = 'A' 

    AND A.AUTH_STATUS_KT <>'A'

    AND A.APPROVE_DT>=CONVERT(DATETIME,'2022-17-10',103)

		AND ( (@p_AUTH_STATUS = 'Y' AND A.USER_MASTER_ID IN (SELECT G.USER_MASTER_ID FROM ASS_USE_CONFIRM_MASTER G)) 

			OR (@p_AUTH_STATUS = 'N' AND A.USER_MASTER_ID NOT IN (SELECT G.USER_MASTER_ID FROM ASS_USE_CONFIRM_MASTER G)

			OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))

		 --AND ( (@p_AUTH_STATUS = 'Y' AND A.USER_MASTER_ID = B.USER_MASTER_ID) 

			--OR (@p_AUTH_STATUS = 'N' AND A.USER_MASTER_ID <> B.USER_MASTER_ID

			--OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '' )))

    --AND (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',

    --tmp.BRANCH_ID,

    --tmp.DEPT_ID) US

    --WHERE US.TLNANME = @p_USER_LOGIN))

	AND (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',

    (SELECT TOP 1 DT.BRANCH_ID FROM ASS_USE_MULTI_DT DT WHERE DT.USER_MASTER_ID = A.USER_MASTER_ID ORDER BY USE_MULTI_ID ASC),

    (SELECT TOP 1 DT.DEPT_ID FROM ASS_USE_MULTI_DT DT WHERE DT.USER_MASTER_ID = A.USER_MASTER_ID ORDER BY USE_MULTI_ID ASC)) US

    WHERE US.TLNANME = @p_USER_LOGIN))

		ORDER BY A.CREATE_DT DESC

-- PAGING END

END -- PAGING