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)

	DECLARE @ASSIGN_ROLES TABLE (BRANCH_ID VARCHAR(20), DEPT_ID VARCHAR(15), ROLE_ID VARCHAR(20))

	INSERT INTO @ASSIGN_ROLES SELECT * FROM [dbo].[TL_USER_GET_ASSIGNED_BRANCH](@p_USER_LOGIN)

	DECLARE @USER_ROLES TABLE (BRANCH_ID VARCHAR(20), DEPT_ID VARCHAR(15), ROLE_ID VARCHAR(20))

	INSERT INTO @USER_ROLES SELECT * FROM [dbo].[TL_USER_GET_ROLES](@p_USER_LOGIN)

	DECLARE @tmp TABLE (BRANCH_ID VARCHAR(20), DEPT_ID VARCHAR(15),BRANCH_ID_OLD VARCHAR(20), DEPT_ID_OLD VARCHAR(15))

	INSERT INTO @tmp 

	SELECT DT.BRANCH_ID, DT.DEPT_ID, DT.BRANCH_ID_OLD, DT.DEPT_ID_OLD FROM ASS_TRANSFER_MULTI_MASTER AM

	LEFT JOIN ASS_TRANSFER_MULTI_DT DT ON DT.TRANSFER_MULTI_ID IN (SELECT MIN(TRANSFER_MULTI_ID) FROM ASS_TRANSFER_MULTI_DT  TEMP WHERE TEMP.TRANS_MULTI_MASTER_ID=AM.TRANS_MULTI_MASTER_ID)

	WHERE 1= 1

	AND ((@p_TYPE_SEARCH = 'BG' AND ((EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = DT.BRANCH_ID_OLD AND BRANCH_TYPE ='HS')

		AND (DT.DEPT_ID_OLD IN (SELECT R.DEPT_ID FROM @USER_ROLES R WHERE R.ROLE_ID IN ('GDDV','TP')))

				OR DT.DEPT_ID_OLD IN (SELECT DEPT_ID FROM @ASSIGN_ROLES WHERE ROLE_ID IN ('GDDV','TP')))

		OR (NOT EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = DT.BRANCH_ID_OLD AND BRANCH_TYPE ='HS')

			AND (DT.BRANCH_ID_OLD IN (SELECT R.BRANCH_ID FROM @USER_ROLES R WHERE R.ROLE_ID IN ('GDDV','TPGD'))))

				OR DT.BRANCH_ID_OLD IN (SELECT BRANCH_ID FROM @ASSIGN_ROLES WHERE ROLE_ID IN ('GDDV','TPGD'))))

	OR (@p_TYPE_SEARCH = 'BN' AND ((EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = DT.BRANCH_ID AND BRANCH_TYPE ='HS')

			AND (DT.DEPT_ID IN (SELECT R.DEPT_ID FROM @USER_ROLES R WHERE R.ROLE_ID IN ('GDDV','TP')))

				OR DT.DEPT_ID IN (SELECT DEPT_ID FROM @ASSIGN_ROLES WHERE ROLE_ID IN ('GDDV','TP')))

		OR (NOT EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = DT.BRANCH_ID AND BRANCH_TYPE ='HS')

			AND (DT.BRANCH_ID IN (SELECT R.BRANCH_ID FROM @USER_ROLES R WHERE R.ROLE_ID IN ('GDDV','TPGD'))))

				OR DT.BRANCH_ID IN (SELECT BRANCH_ID FROM @ASSIGN_ROLES WHERE ROLE_ID IN ('GDDV','TPGD')))))

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 = '' ))

		AND ((@p_TYPE_SEARCH = 'BG' AND EXISTS(SELECT TRANS_MULTI_MASTER_ID FROM ASS_TRANSFER_MULTI_DT DT WHERE A.TRANS_MULTI_MASTER_ID = DT.TRANS_MULTI_MASTER_ID

				AND ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID_OLD) <> 'HS' AND (DT.BRANCH_ID_OLD IN (SELECT BRANCH_ID_OLD FROM @tmp))

					OR ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID_OLD) = 'HS' AND DT.DEPT_ID_OLD IN (SELECT DEPT_ID_OLD FROM @tmp)))))

			OR (@p_TYPE_SEARCH = 'BN' AND EXISTS(SELECT TRANS_MULTI_MASTER_ID FROM ASS_TRANSFER_MULTI_DT DT WHERE A.TRANS_MULTI_MASTER_ID = DT.TRANS_MULTI_MASTER_ID

				AND ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID) <> 'HS' AND (DT.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp))

					OR ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID) = 'HS' AND DT.DEPT_ID IN (SELECT DEPT_ID FROM @tmp)))))

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

	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 = '' ))

		AND ((@p_TYPE_SEARCH = 'BG' AND EXISTS(SELECT TRANS_MULTI_MASTER_ID FROM ASS_TRANSFER_MULTI_DT DT WHERE A.TRANS_MULTI_MASTER_ID = DT.TRANS_MULTI_MASTER_ID

				AND ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID_OLD) <> 'HS' AND (DT.BRANCH_ID_OLD IN (SELECT BRANCH_ID_OLD FROM @tmp))

					OR ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID_OLD) = 'HS' AND DT.DEPT_ID_OLD IN (SELECT DEPT_ID_OLD FROM @tmp)))))

			OR (@p_TYPE_SEARCH = 'BN' AND EXISTS(SELECT TRANS_MULTI_MASTER_ID FROM ASS_TRANSFER_MULTI_DT DT WHERE A.TRANS_MULTI_MASTER_ID = DT.TRANS_MULTI_MASTER_ID

				AND ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID) <> 'HS' AND (DT.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp))

					OR ((SELECT BR.BRANCH_TYPE FROM dbo.CM_BRANCH BR WHERE BR.BRANCH_ID = DT.BRANCH_ID) = 'HS' AND DT.DEPT_ID IN (SELECT DEPT_ID FROM @tmp)))))

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

	ORDER BY A.CREATE_DT DESC

	-- PAGING END

END -- PAGING