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

ALTER PROCEDURE [dbo].[ASS_COLLECT_CONFIRM_MASTER_Search]

@p_COL_MULTI_MASTER_ID	varchar(15)  = NULL,

@p_BRANCH_ID	varchar(15)  = NULL,

@p_COLLECT_DT	VARCHAR(20) = NULL,

@p_USER_COLLECT	nvarchar(200)  = NULL,

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

@p_TOP	INT = NULL,

@p_FROMDATE VARCHAR(20) = NULL,

@p_TODATE VARCHAR(20) = NULL,

@p_IS_UPDATE VARCHAR(1) = NULL,

@p_USER_LOGIN   VARCHAR(15),

@p_TYPE_SEARCH VARCHAR(10)= NULL

AS

BEGIN -- PAGING

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

----

set @p_RECORD_STATUS = '1'

----

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

	INSERT INTO @tmp 

	SELECT DT.BRANCH_ID, DT.DEPT_ID_USE FROM dbo.ASS_COLLECT_MULTI_MASTER AM

	LEFT JOIN ASS_COLLECT_MULTI_DT DT ON DT.COLLECT_MULTI_ID IN (SELECT MIN(COLLECT_MULTI_ID) FROM ASS_COLLECT_MULTI_DT  TEMP WHERE TEMP.COL_MULTI_MASTER_ID=AM.COL_MULTI_MASTER_ID)

	WHERE 1= 1

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

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

				OR DT.DEPT_ID_USE 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')))

-- GiaNT

DECLARE @BRANCH_LOGIN VARCHAR(15), @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 DEP_ID  FROM TL_USER WHERE TLNANME = @p_USER_LOGIN)

-- GIANT 11/10/2021

DECLARE @BRANCH_TYPE VARCHAR(5)

		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)

begin

-- PAGING BEGIN

	SELECT A.*,C.BRANCH_CODE, C.BRANCH_NAME, F.AUTH_STATUS_NAME, T1.TLFullName AS MAKER_NAME,T2.TLFullName AS MAKER_NAME_KT,T3.TLFullName APPROVE_NAME, T4.TLFullName APPROVE_NAME_KT,

	ATKT.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_KT, C.BRANCH_CODE +' - '+ C.BRANCH_NAME AS FULL_BRANCH_NAME,

	@p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH,

	CASE WHEN EXISTS (SELECT * FROM ASS_COLLECT_CONFIRM_MASTER M WHERE M.COL_MULTI_MASTER_ID = A.COL_MULTI_MASTER_ID) 

			THEN 'Y'  

		ELSE 'N' END AS CONFIRM_STATUS

	-- SELECT END

	FROM ASS_COLLECT_MULTI_MASTER A

	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,COL_MULTI_MASTER_ID,COUNT(*) AS SL FROM ASS_COLLECT_MULTI_DT WHERE DEPT_ID_USE = @BRANCH_LOGIN GROUP BY BRANCH_ID,COL_MULTI_MASTER_ID) DTT ON DTT.COL_MULTI_MASTER_ID = A.COL_MULTI_MASTER_ID   

	WHERE 1 = 1

	AND (A.COL_MULTI_MASTER_ID LIKE '%' + @p_COL_MULTI_MASTER_ID + '%' OR  @p_COL_MULTI_MASTER_ID IS NULL OR @p_COL_MULTI_MASTER_ID = '')

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

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

	AND (A.USER_COLLECT LIKE '%' + @p_USER_COLLECT + '%' OR  @p_USER_COLLECT IS NULL OR @p_USER_COLLECT = '')

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

	AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')

	AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR  @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')

	AND (A.CREATE_DT =CONVERT(DATETIME, @p_CREATE_DT, 103) OR  @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')

	AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR  @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')

	AND (A.APPROVE_DT =CONVERT(DATETIME, @p_APPROVE_DT, 103) OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')

	AND (A.AUTH_STATUS_KT LIKE '%' + @p_AUTH_STATUS_KT + '%' OR  @p_AUTH_STATUS_KT IS NULL OR @p_AUTH_STATUS_KT = '')

	AND (A.CREATE_DT_KT =CONVERT(DATETIME, @p_CREATE_DT_KT, 103) OR  @p_CREATE_DT_KT IS NULL OR @p_CREATE_DT_KT = '')

	AND (A.APPROVE_DT_KT =CONVERT(DATETIME, @p_APPROVE_DT_KT, 103) OR  @p_APPROVE_DT_KT IS NULL OR @p_APPROVE_DT_KT = '')

	AND (A.MAKER_ID_KT LIKE '%' + @p_MAKER_ID_KT + '%' OR  @p_MAKER_ID_KT IS NULL OR @p_MAKER_ID_KT = '')

	AND (A.CHECKER_ID_KT LIKE '%' + @p_CHECKER_ID_KT + '%' OR  @p_CHECKER_ID_KT IS NULL OR @p_CHECKER_ID_KT = '')

	AND (A.REPORT_STATUS LIKE '%' + @p_REPORT_STATUS + '%' OR  @p_REPORT_STATUS IS NULL OR @p_REPORT_STATUS = '')

	AND A.RECORD_STATUS='1'

	AND (A.REPORT_STATUS LIKE '%' + @p_REPORT_STATUS + '%' OR  @p_REPORT_STATUS IS NULL OR @p_REPORT_STATUS = '')

	-- GiaNT 28/09/2021

	AND ( (@p_AUTH_STATUS = 'Y' AND A.COL_MULTI_MASTER_ID IN (SELECT G.COL_MULTI_MASTER_ID FROM ASS_COLLECT_CONFIRM_MASTER G)) 

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

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

	AND A.AUTH_STATUS = 'A'

	-- GiaNT 11/10/2021

	--AND ( 

	--	(@BRANCH_TYPE = 'HS' AND  EXISTS (SELECT * FROM ASS_COLLECT_MULTI_DT WHERE A.COL_MULTI_MASTER_ID = ASS_COLLECT_MULTI_DT.COL_MULTI_MASTER_ID AND ASS_COLLECT_MULTI_DT.DEPT_ID_USE = @DEP_LOGIN))

	--	OR (@BRANCH_TYPE <> 'HS' AND  EXISTS (SELECT * FROM ASS_COLLECT_MULTI_DT WHERE A.COL_MULTI_MASTER_ID = ASS_COLLECT_MULTI_DT.COL_MULTI_MASTER_ID AND ASS_COLLECT_MULTI_DT.BRANCH_ID = @BRANCH_LOGIN))

	--)

	AND (EXISTS (SELECT DT.COLLECT_MULTI_ID FROM dbo.ASS_COLLECT_MULTI_DT DT WHERE A.COL_MULTI_MASTER_ID = DT.COL_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_USE IN (SELECT DEPT_ID FROM @tmp)))))

	ORDER BY A.CREATE_DT DESC

-- PAGING END

end

ELSE

BEGIN

-- PAGING BEGIN

	SELECT TOP(CONVERT(INT,@P_TOP))A.*,C.BRANCH_CODE, C.BRANCH_NAME, F.AUTH_STATUS_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,

	C.BRANCH_CODE +' - '+ C.BRANCH_NAME AS FULL_BRANCH_NAME,

	@p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH,

	CASE WHEN EXISTS (SELECT * FROM ASS_COLLECT_CONFIRM_MASTER M WHERE M.COL_MULTI_MASTER_ID = A.COL_MULTI_MASTER_ID) 

			THEN 'Y'  

		ELSE 'N' END AS CONFIRM_STATUS

	 -- SELECT END

	FROM ASS_COLLECT_MULTI_MASTER A

	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,COL_MULTI_MASTER_ID,COUNT(*) AS SL FROM ASS_COLLECT_MULTI_DT WHERE BRANCH_ID = @BRANCH_LOGIN GROUP BY BRANCH_ID,COL_MULTI_MASTER_ID) DTT ON DTT.COL_MULTI_MASTER_ID = A.COL_MULTI_MASTER_ID   

	 WHERE 1 = 1

	 AND (A.COL_MULTI_MASTER_ID LIKE '%' + @p_COL_MULTI_MASTER_ID + '%' OR  @p_COL_MULTI_MASTER_ID IS NULL OR @p_COL_MULTI_MASTER_ID = '')

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

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

	 AND (A.USER_COLLECT LIKE '%' + @p_USER_COLLECT + '%' OR  @p_USER_COLLECT IS NULL OR @p_USER_COLLECT = '')

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

	 AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')

	 AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR  @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')

	 AND (A.CREATE_DT =CONVERT(DATETIME, @p_CREATE_DT, 103) OR  @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')

	 AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR  @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')

	 AND (A.APPROVE_DT =CONVERT(DATETIME, @p_APPROVE_DT, 103) OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')

	 AND (A.AUTH_STATUS_KT LIKE '%' + @p_AUTH_STATUS_KT + '%' OR  @p_AUTH_STATUS_KT IS NULL OR @p_AUTH_STATUS_KT = '')

	 AND (A.CREATE_DT_KT =CONVERT(DATETIME, @p_CREATE_DT_KT, 103) OR  @p_CREATE_DT_KT IS NULL OR @p_CREATE_DT_KT = '')

	 AND (A.APPROVE_DT_KT =CONVERT(DATETIME, @p_APPROVE_DT_KT, 103) OR  @p_APPROVE_DT_KT IS NULL OR @p_APPROVE_DT_KT = '')

	 AND (A.MAKER_ID_KT LIKE '%' + @p_MAKER_ID_KT + '%' OR  @p_MAKER_ID_KT IS NULL OR @p_MAKER_ID_KT = '')

	 AND (A.CHECKER_ID_KT LIKE '%' + @p_CHECKER_ID_KT + '%' OR  @p_CHECKER_ID_KT IS NULL OR @p_CHECKER_ID_KT = '')

	 AND (A.REPORT_STATUS LIKE '%' + @p_REPORT_STATUS + '%' OR  @p_REPORT_STATUS IS NULL OR @p_REPORT_STATUS = '')

	 AND A.RECORD_STATUS='1'

	 AND (A.REPORT_STATUS LIKE '%' + @p_REPORT_STATUS + '%' OR  @p_REPORT_STATUS IS NULL OR @p_REPORT_STATUS = '')

	-- GiaNT 28/09/2021

	AND ( (@p_AUTH_STATUS = 'Y' AND A.COL_MULTI_MASTER_ID IN (SELECT G.COL_MULTI_MASTER_ID FROM ASS_COLLECT_CONFIRM_MASTER G)) 

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

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

	AND A.AUTH_STATUS = 'A'

	-- GiaNT 11/10/2021

	--AND ( 

	--	(@BRANCH_TYPE = 'HS' AND  EXISTS (SELECT * FROM ASS_COLLECT_MULTI_DT WHERE A.COL_MULTI_MASTER_ID = ASS_COLLECT_MULTI_DT.COL_MULTI_MASTER_ID AND ASS_COLLECT_MULTI_DT.DEPT_ID_USE = @DEP_LOGIN))

	--	OR (@BRANCH_TYPE <> 'HS' AND  EXISTS (SELECT * FROM ASS_COLLECT_MULTI_DT WHERE A.COL_MULTI_MASTER_ID = ASS_COLLECT_MULTI_DT.COL_MULTI_MASTER_ID AND ASS_COLLECT_MULTI_DT.BRANCH_ID = @BRANCH_LOGIN))

	--)

	AND (EXISTS (SELECT DT.COLLECT_MULTI_ID FROM dbo.ASS_COLLECT_MULTI_DT DT WHERE A.COL_MULTI_MASTER_ID = DT.COL_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_USE IN (SELECT DEPT_ID FROM @tmp)))))

	ORDER BY A.CREATE_DT DESC

-- PAGING END

END

END -- PAGING