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

ALTER PROCEDURE [dbo].[CM_REQUEST_TEMPLATE_Search] 

			@p_REQUEST_TEMPLATE_CODE varchar(15)=NULL,

			@p_REQUEST_TEMPLATE_NAME nvarchar(200)=NULL,

			@p_NOTES nvarchar(1000)=NULL,

			@p_RECORD_STATUS varchar(1)=NULL,

			@p_MAKER_ID varchar(12)=NULL,

			@p_CREATE_DT VARCHAR(20)=NULL,

			@p_AUTH_STATUS varchar(50)=NULL,

			@p_CHECKER_ID varchar(12)=NULL,

			@p_APPROVE_DT VARCHAR(20)=NULL,

			@p_MaxResultCount int = NULL,

			@p_SkipCount int = NULL,

			@p_Sorting varchar(100) = NULL,

			@p_TotalCount int = NULL,

			@p_REPORT_NO NVARCHAR(50)=NULL,

			@p_REPORT_DT NVARCHAR(50)=NULL,

			@p_TYPE_TEMPLATE NVARCHAR(50)=NULL,

			@p_FROM_DT NVARCHAR(20)=NULL,

			@p_TO_DT NVARCHAR(20)=NULL,

			@p_TYPE_TEMPLATE_ID varchar(20)=null,

			@p_MAKER_FULLNAME Nvarchar(50)=null,

			@p_USER_LOGIN VARCHAR(20)=NULL,

			@p_AUTH_STATUS_APP VARCHAR(20)=NULL,

			@p_TITLE NVARCHAR(500)=NULL,

			@p_USER_APPROVES NVARCHAR(500)=NULL,

			@p_FROM_DATE VARCHAR(20)=NULL,

			@p_TO_DATE VARCHAR(20)=NULL

AS

BEGIN -- PAGING

-- PAGING BEGIN

	SELECT A.*,B.CONTENT AS AUTH_STATUS_NAME,C.CONTENT AS RECORD_STATUS_NAME

	,TT.TYPE_TEMPLATE_NAME AS TYPE_TEMPLATE_NAME

	,CASE 

		WHEN BR.[BRANCH_ID] IS NOT NULL THEN BR.[BRANCH_NAME]

		ELSE (SELECT TOP 1 D.[BRANCH_NAME] + ' - ' + C.[DEP_NAME]

				FROM CM_DEPARTMENT C 

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

				WHERE C.DEP_ID = A.BRANCH_ID)

	 END AS	BRANCH_NAME

	,U.TLFullName AS MAKER_FULLNAME

	,AUTH.CONTENT AS AUTH_STATUS_APP_NAME

	,GR.USER_APPROVES

	-- SELECT END

	FROM CM_REQUEST_TEMPLATE A

	--LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS

	LEFT JOIN CM_ALLCODE C ON C.CDNAME = 'RECORD_STATUS' AND C.CDVAL = A.RECORD_STATUS 

	LEFT JOIN CM_TYPE_TEMPLATE TT ON TT.TYPE_TEMPLATE_ID=A.TYPE_TEMPLATE_ID

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

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

	--WORKFLOW

	LEFT JOIN 

	(

		SELECT 

		G.REQ_ID

		,CASE 

		WHEN DONE=0 THEN 'U'

		WHEN DONE=1 THEN 'A'

		END AS AUT_STATUS 

		FROM CM_APPROVE_GROUP G WHERE (DONE=1 OR PROCESS_STATUS=1) AND (@p_USER_LOGIN=APPROVE_USERNAME OR @p_USER_LOGIN=AUTHORITY_NAME)

	)	AG ON AG.REQ_ID=A.REQUEST_TEMPLATE_ID

	LEFT JOIN dbo.CM_ALLCODE AUTH ON AUTH.CDVAL = AG.AUT_STATUS AND AUTH.CDNAME = 'AUTH_STATUS_APP'

	LEFT JOIN dbo.CM_ALLCODE B ON B.CDVAL = A.AUTH_STATUS AND B.CDNAME = 'AUTH_STATUS_ONL'

	LEFT JOIN (

		SELECT A.REQ_ID,

		STUFF( (select ';' + U.TLFullName from CM_APPROVE_GROUP G JOIN TL_USER U ON G.APPROVE_USERNAME=U.TLNANME WHERE REQ_ID=A.REQ_ID AND PROCESS_STATUS=1 AND DONE=0 AND [TYPE]='APP' FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') AS USER_APPROVES

		FROM CM_APPROVE_GROUP A WHERE PROCESS_STATUS=1 AND DONE=0

		GROUP BY REQ_ID

	) GR ON GR.REQ_ID=A.REQUEST_TEMPLATE_ID

	--END WORKFLOW

	WHERE 1=1	AND

	(@p_REQUEST_TEMPLATE_CODE IS NULL OR @p_REQUEST_TEMPLATE_CODE='' OR REQUEST_TEMPLATE_CODE LIKE '%' + @p_REQUEST_TEMPLATE_CODE + '%') AND

	(@p_REQUEST_TEMPLATE_NAME IS NULL OR @p_REQUEST_TEMPLATE_NAME='' OR REQUEST_TEMPLATE_NAME LIKE '%' + @p_REQUEST_TEMPLATE_NAME + '%') AND

	(@p_TYPE_TEMPLATE_ID IS NULL OR @p_TYPE_TEMPLATE_ID='' OR A.TYPE_TEMPLATE_ID LIKE '%' + @p_TYPE_TEMPLATE_ID + '%') AND

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

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

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

	--(@p_MAKER_ID IS NULL OR @p_MAKER_ID='' OR MAKER_ID = @p_MAKER_ID) AND

	(@p_CREATE_DT IS NULL OR DATEDIFF(DAYOFYEAR,A.CREATE_DT,CONVERT(datetime, @p_CREATE_DT,103)) = 0) AND

	(@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS='' OR A.AUTH_STATUS = @p_AUTH_STATUS) AND

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

	(@p_APPROVE_DT IS NULL OR DATEDIFF(DAYOFYEAR,A.APPROVE_DT,CONVERT(datetime, @p_APPROVE_DT,103)) = 0)

	AND (A.REPORT_DT >= CONVERT(DATE,@p_FROM_DATE,103) OR @p_FROM_DATE IS NULL OR @p_FROM_DATE = '') 

	AND (A.REPORT_DT <= CONVERT(DATE,@p_TO_DATE,103) OR @p_TO_DATE IS NULL OR @p_TO_DATE = '')

	AND(REPORT_DT=CONVERT(DATETIME,@p_REPORT_DT,103) OR @p_REPORT_DT IS NULL)

	AND(@p_MAKER_FULLNAME IS NULL OR @p_MAKER_FULLNAME='' OR U.TLFullName LIKE '%'+@p_MAKER_FULLNAME+'%' OR	U.TLNANME LIKE '%'+@p_MAKER_FULLNAME+'%')

	AND(@p_REPORT_NO IS NULL OR @p_REPORT_NO='' OR A.REPORT_NO LIKE '%'+@p_REPORT_NO+'%')

	AND(@p_AUTH_STATUS_APP IS NULL OR @p_AUTH_STATUS_APP='' OR AG.AUT_STATUS = @p_AUTH_STATUS_APP)

	AND(@p_TITLE IS NULL OR @p_TITLE='' OR A.TITLE LIKE '%'+@p_TITLE+'%')

	--WORKFLOW

	AND (@p_USER_APPROVES IS NULL OR @p_USER_APPROVES='' OR GR.USER_APPROVES LIKE '%'+@p_USER_APPROVES+'%')

	AND (

		(A.MAKER_ID=@p_USER_LOGIN) 

		OR (EXISTS(SELECT * FROM CM_APPROVE_GROUP WHERE REQ_ID=A.REQUEST_TEMPLATE_ID AND (APPROVE_USERNAME=@p_USER_LOGIN OR AUTHORITY_NAME=@p_USER_LOGIN) AND( DONE=1 OR PROCESS_STATUS=1 OR [TYPE]='AUT')	) )

		OR (@p_USER_LOGIN IN (SELECT SHARE_USERNAME FROM CM_TEMPLATE_SHARE  WHERE REQ_ID=A.REQUEST_TEMPLATE_ID)	)

		OR (dbo.FN_CHECK_ROLE_USER(@p_USER_LOGIN,'ADMIN_TO_TRINH')=1)

		OR (EXISTS(SELECT * FROM [dbo].[TL_USER_GET_ROLES] (@p_USER_LOGIN) USERROLE WHERE USERROLE.ROLE_ID = 'GDDV' 

																							AND (

																									EXISTS(SELECT * FROM TL_USER US WHERE US.TLNANME = A.MAKER_ID AND (USERROLE.BRANCH_ID = US.TLSUBBRID OR USERROLE.DEPT_ID = US.DEP_ID) AND US.TLSUBBRID <> 'DV0001')

																									OR EXISTS(SELECT * FROM TL_USER US WHERE US.TLNANME = A.MAKER_ID AND USERROLE.DEPT_ID = US.DEP_ID)

																								)

				  )

			)

	)

	AND ((A.RECORD_STATUS = '0' AND (A.MAKER_ID = @p_USER_LOGIN)) OR A.RECORD_STATUS = '1')

	--END WORKFLOW

	ORDER BY A.CREATE_DT DESC

-- PAGING END

END -- PAGING