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

ALTER PROCEDURE dbo.ASS_TRANSFER_CONFIRM_MASTER_Search

@p_TRANS_MULTI_MASTER_ID	varchar(15)  = NULL,

@p_BRANCH_ID	varchar(15)  = NULL,

@p_TRANSFER_DT	VARCHAR(20) = NULL,

@p_USER_TRANSFER	nvarchar(200)  = NULL,

@p_USER_TRANSFER_NAME	nvarchar(200)  = NULL,

@p_NOTES	nvarchar(1000)  = NULL,

@p_RECORD_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_LEVEL	VARCHAR(50) = 'UNIT',

@p_TOP	INT = 10,

@p_FROMDATE VARCHAR(20) = NULL,

@p_TODATE VARCHAR(20) = NULL,

@p_AUTH_STATUS	varchar(1)  = NULL, -- TRẠNG THÁI XÁC NHẬN CỦA BÊN GIAO (CHO)

@p_IS_UPDATE VARCHAR(1) = NULL, -- TRẠNG THÁI XÁC NHẬN CỦA NHẬN

@p_USER_LOGIN   VARCHAR(15) = NULL,

@p_TYPE_SEARCH VARCHAR(10)= NULL -- BN  --- BG

AS

BEGIN -- PAGING

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SET @p_RECORD_STATUS = '1'

	DECLARE @BRANCH_LOGIN VARCHAR(15),@BRANCH_TYPE VARCHAR(10), @DEP_LOGIN VARCHAR(15)

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

		SET @DEP_LOGIN = (SELECT TOP 1 SECUR_CODE FROM TL_USER WHERE TLNANME = @p_USER_LOGIN)

		SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_LOGIN)	

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

	-- PAGING BEGIN

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

		ATKT.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_KT,T1.TLFullName AS MAKER_NAME,T2.TLFullName AS MAKER_NAME_KT,T3.TLFullName APPROVE_NAME, T4.TLFullName APPROVE_NAME_KT,

		@p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH,

		ACM.CONFIRM_STATUS_BG, ACM.CONFIRM_STATUS_BN, ACM.TRANS_MULTI_MASTER_CONFIRM_ID

	-- SELECT END

	FROM ASS_TRANSFER_MULTI_MASTER A

	INNER JOIN dbo.ASS_TRANSFER_CONFIRM_MASTER ACM ON A.TRANS_MULTI_MASTER_ID = ACM.TRANS_MULTI_MASTER_ID

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

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

	LEFT JOIN CM_AUTH_STATUS ATKT ON ATKT.AUTH_STATUS = A.AUTH_STATUS_KT

	LEFT JOIN TL_USER T1 ON A.MAKER_ID = T1.TLNANME 

	LEFT JOIN TL_USER T2 ON A.MAKER_ID_KT = T2.TLNANME

	LEFT JOIN TL_USER T3 ON A.CHECKER_ID = T3.TLNANME 

	LEFT JOIN TL_USER T4 ON A.CHECKER_ID_KT = T4.TLNANME

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

	 WHERE 1 = 1

		AND (A.TRANS_MULTI_MASTER_ID LIKE '%' + @p_TRANS_MULTI_MASTER_ID + '%' OR  @p_TRANS_MULTI_MASTER_ID IS NULL OR @p_TRANS_MULTI_MASTER_ID = '')	

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

		AND (CONVERT(DATE,ISNULL(A.CREATE_DT_KT, A.TRANSFER_DT),103) <= CONVERT(DATE,@p_TODATE, 103) OR @p_TODATE IS NULL OR @p_TODATE ='')

		AND A.AUTH_STATUS = 'A' AND [dbo].[FN_CHECK_PRIVATE_TRANSFER](A.TRANS_MULTI_MASTER_ID) = 0

		--TRẠNG THÁI XÁC NHẬN CỦA BÊN GIAO (CHO)

		AND ((@p_AUTH_STATUS = 'Y' AND ACM.CONFIRM_STATUS_BG = 'Y') 

			OR (@p_AUTH_STATUS = 'N' AND ACM.CONFIRM_STATUS_BG IS NULL)

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

		--  TRẠNG THÁI XÁC NHẬN CỦA NHẬN

		AND ((@p_IS_UPDATE = 'Y' AND ACM.CONFIRM_STATUS_BN = 'Y') 

				OR (@p_IS_UPDATE = 'N' AND ACM.CONFIRM_STATUS_BN IS NULL)

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

  -- TIM KIEM THEO TRUONG DON VI GIAO TAI SAN

  AND ((@p_TYPE_SEARCH = 'BG' AND EXISTS(SELECT * FROM dbo.FN_GET_USER_BY_ROLE('GDDV',

    (SELECT TOP 1 DT.BRANCH_ID_OLD FROM ASS_TRANSFER_MULTI_DT DT WHERE DT.TRANS_MULTI_MASTER_ID = A.TRANS_MULTI_MASTER_ID ORDER BY DT.TRANSFER_MULTI_ID ASC),

    (SELECT TOP 1 DT.DEPT_ID_OLD FROM ASS_TRANSFER_MULTI_DT DT WHERE DT.TRANS_MULTI_MASTER_ID = A.TRANS_MULTI_MASTER_ID ORDER BY DT.TRANSFER_MULTI_ID ASC)) US

    WHERE US.TLNANME = @p_USER_LOGIN))

  -- TIM KIEM THEO TRUONG DON VI NHAN TAI SAN

  OR (@p_TYPE_SEARCH = 'BN' AND EXISTS(SELECT * FROM dbo.FN_GET_USER_BY_ROLE('GDDV',

    (SELECT TOP 1 DT.BRANCH_ID FROM ASS_TRANSFER_MULTI_DT DT WHERE DT.TRANS_MULTI_MASTER_ID = A.TRANS_MULTI_MASTER_ID ORDER BY DT.TRANSFER_MULTI_ID ASC),

    (SELECT TOP 1 DT.DEPT_ID FROM ASS_TRANSFER_MULTI_DT DT WHERE DT.TRANS_MULTI_MASTER_ID = A.TRANS_MULTI_MASTER_ID ORDER BY DT.TRANSFER_MULTI_ID ASC)) US

    WHERE US.TLNANME = @p_USER_LOGIN))

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

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

	ORDER BY A.CREATE_DT DESC

	-- PAGING END

ELSE

	-- PAGING BEGIN

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

		ATKT.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_KT,T1.TLFullName AS MAKER_NAME,T2.TLFullName AS MAKER_NAME_KT,T3.TLFullName APPROVE_NAME, T4.TLFullName APPROVE_NAME_KT,

		@p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH,

		ACM.CONFIRM_STATUS_BG, ACM.CONFIRM_STATUS_BN, ACM.TRANS_MULTI_MASTER_CONFIRM_ID	

		-- SELECT END

		FROM ASS_TRANSFER_MULTI_MASTER A

		INNER JOIN dbo.ASS_TRANSFER_CONFIRM_MASTER ACM ON A.TRANS_MULTI_MASTER_ID = ACM.TRANS_MULTI_MASTER_ID

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

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

		LEFT JOIN CM_AUTH_STATUS ATKT ON ATKT.AUTH_STATUS = A.AUTH_STATUS_KT

		LEFT JOIN TL_USER T1 ON A.MAKER_ID = T1.TLNANME 

		LEFT JOIN TL_USER T2 ON A.MAKER_ID_KT = T2.TLNANME

		LEFT JOIN TL_USER T3 ON A.CHECKER_ID = T3.TLNANME 

		LEFT JOIN TL_USER T4 ON A.CHECKER_ID_KT = T4.TLNANME

	 WHERE 1 = 1

		AND (A.TRANS_MULTI_MASTER_ID LIKE '%' + @p_TRANS_MULTI_MASTER_ID + '%' OR  @p_TRANS_MULTI_MASTER_ID IS NULL OR @p_TRANS_MULTI_MASTER_ID = '')	

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

		AND (CONVERT(DATE,ISNULL(A.CREATE_DT_KT, A.TRANSFER_DT),103) <= CONVERT(DATE,@p_TODATE, 103) OR @p_TODATE IS NULL OR @p_TODATE ='')

		AND A.AUTH_STATUS = 'A' AND [dbo].[FN_CHECK_PRIVATE_TRANSFER](A.TRANS_MULTI_MASTER_ID) = 0

		--TRẠNG THÁI XÁC NHẬN CỦA BÊN GIAO (CHO)

		AND ((@p_AUTH_STATUS = 'Y' AND ACM.CONFIRM_STATUS_BG = 'Y') 

			OR (@p_AUTH_STATUS = 'N' AND ACM.CONFIRM_STATUS_BG IS NULL)

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

		--  TRẠNG THÁI XÁC NHẬN CỦA NHẬN

		AND ((@p_IS_UPDATE = 'Y' AND ACM.CONFIRM_STATUS_BN = 'Y') 

				OR (@p_IS_UPDATE = 'N' AND ACM.CONFIRM_STATUS_BN IS NULL)

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

    -- TIM KIEM THEO TRUONG DON VI GIAO TAI SAN

    AND ((@p_TYPE_SEARCH = 'BG' AND EXISTS(SELECT * FROM dbo.FN_GET_USER_BY_ROLE('GDDV',

      (SELECT TOP 1 DT.BRANCH_ID_OLD FROM ASS_TRANSFER_MULTI_DT DT WHERE DT.TRANS_MULTI_MASTER_ID = A.TRANS_MULTI_MASTER_ID ORDER BY DT.TRANSFER_MULTI_ID ASC),

      (SELECT TOP 1 DT.DEPT_ID_OLD FROM ASS_TRANSFER_MULTI_DT DT WHERE DT.TRANS_MULTI_MASTER_ID = A.TRANS_MULTI_MASTER_ID ORDER BY DT.TRANSFER_MULTI_ID ASC)) US

      WHERE US.TLNANME = @p_USER_LOGIN))

    -- TIM KIEM THEO TRUONG DON VI NHAN TAI SAN

    OR (@p_TYPE_SEARCH = 'BN' AND EXISTS(SELECT * FROM dbo.FN_GET_USER_BY_ROLE('GDDV',

      (SELECT TOP 1 DT.BRANCH_ID FROM ASS_TRANSFER_MULTI_DT DT WHERE DT.TRANS_MULTI_MASTER_ID = A.TRANS_MULTI_MASTER_ID ORDER BY DT.TRANSFER_MULTI_ID ASC),

      (SELECT TOP 1 DT.DEPT_ID FROM ASS_TRANSFER_MULTI_DT DT WHERE DT.TRANS_MULTI_MASTER_ID = A.TRANS_MULTI_MASTER_ID ORDER BY DT.TRANSFER_MULTI_ID ASC)) US

      WHERE US.TLNANME = @p_USER_LOGIN))

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

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

	ORDER BY A.CREATE_DT DESC

	-- PAGING END

END -- PAGING