ALTER PROCEDURE [dbo].[TR_REQUEST_DOC_Search]
@p_REQ_ID	varchar(15)  = NULL,
@p_REQ_CODE	nvarchar(100)  = NULL,
@p_REQ_NAME	nvarchar(200)  = NULL,
@p_REQ_DT	VARCHAR(20) = NULL,
@p_REQ_TYPE	varchar(20) = NULL,
@p_REQ_CONTENT NVARCHAR(1000)=NULL,
@p_CONTRACT_ID	varchar(15)  = NULL,
@p_SUP_ID	varchar(15)  = NULL,
@p_SUP_NAME	nvarchar(200)  = NULL,
@p_SUP_ADDR	nvarchar(200)  = NULL,
@p_TOTAL_AMT	decimal = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID VARCHAR(100)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID VARCHAR(100)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_CONTRACT_CODE VARCHAR(15) = NULL,
@p_RECEIVE_BRANCH VARCHAR(15) = NULL,
@p_USERNAME VARCHAR(100) = NULL,
@p_BRANCH_ID VARCHAR(15)=NULL,
@p_DEP_ID VARCHAR(15)=NULL,
@p_STATUS  VARCHAR(15)=NULL,
@p_TOP	INT = 10
AS
BEGIN -- PAGING

	DECLARE @DEP_ID VARCHAR(20),@BRANCH_ID VARCHAR(20)

	SET @DEP_ID= (SELECT DEP_ID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME)
	SET @BRANCH_ID= (SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME)
	DECLARE @lstCOST TABLE
	(
		COST_ID VARCHAR(20)
	)
	INSERT INTO @lstCOST
	SELECT COST_ID FROM dbo.CM_COSTCENTER_DT WHERE BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID

	IF(@P_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
	-- PAGING BEGIN
		SELECT A.REQ_ID,
               A.REQ_CODE,
               A.REQ_NAME,
               ISNULL(PRP.APPROVE_DT,A.REQ_DT) AS REQ_DT,
               A.REQ_TYPE,
               A.REQ_CONTENT,
               A.CONTRACT_ID,
               A.SUP_ID,
               A.SUP_NAME,
               A.SUP_ADDR,
               A.TOTAL_AMT,
               A.NOTES,
               A.RECORD_STATUS,
               A.MAKER_ID,
               A.CREATE_DT,
               A.AUTH_STATUS,
               A.CHECKER_ID,
               A.APPROVE_DT,
               A.BRANCH_ID,
               A.STATUS,
               A.DEP_ID,
               A.HO_NOTES,C.CONTRACT_CODE, D.AUTH_STATUS_NAME,  E.SUP_CODE AS S_SUP_CODE, E.SUP_NAME AS S_SUP_NAME, E.ADDR AS S_ADDR, K.CONTENT AS REQ_TYPE_NAME, I.CONTENT AS REQ_STATUS_NAME,
				F.CONTRACT_CODE AS C_CONTRACT_CODE, F.[CONTRACT_NAME] AS C_CONTRACT_NAME,  F.TOTAL_AMT AS C_TOTAL_AMT ,
				ISNULL((SELECT SUM(TR_REQ.TOTAL_AMT) FROM TR_REQUEST_DOC TR_REQ WHERE TR_REQ.CONTRACT_ID = A.CONTRACT_ID),0) AS C_DONE_AMT, @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,G.BRANCH_NAME, PRP.DVKD_USER_APP
		-- SELECT END
		FROM TR_REQUEST_DOC A
		
		LEFT JOIN TR_CONTRACT C ON A.CONTRACT_ID = C.CONTRACT_ID  
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_SUPPLIER E ON E.SUP_ID= A.SUP_ID
		LEFT JOIN TR_CONTRACT F ON F.CONTRACT_ID = A.CONTRACT_ID --AND A.CONTRACT_ID IS NOT NULL
		LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
		LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQTYPE' AND K.CDVAL = A.REQ_TYPE
		LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS]
		LEFT JOIN dbo.PL_REQUEST_PROCESS PRP ON PRP.REQ_ID=A.REQ_ID AND   PRP.PROCESS_ID= (SELECT T.PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS T WHERE T.REQ_ID=A.REQ_ID AND T.PROCESS_ID=@p_STATUS) 

		WHERE 1 = 1
		
	   AND (A.MAKER_ID=@p_USERNAME  OR  (EXISTS(SELECT r.DisplayName FROM TL_USER u 
						LEFT JOIN AbpUserRoles ur ON ur.UserId = u.ID
						LEFT JOIN AbpRoles r ON ur.RoleId = r.Id
						where u.TLNANME=@p_USERNAME AND r.DisplayName='CVMS') ) OR 
		 
	   EXISTS (SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID  
						AND (((PL_REQUEST_PROCESS.STATUS='C' OR PL_REQUEST_PROCESS.STATUS='P') AND (@p_AUTH_STATUS='' OR @p_AUTH_STATUS IS NULL)) OR 
								(@p_AUTH_STATUS='U' AND PL_REQUEST_PROCESS.STATUS='C') OR (@p_AUTH_STATUS='A' AND PL_REQUEST_PROCESS.STATUS='P'))
						AND PROCESS_ID <> 'DVKD'
						AND PL_REQUEST_PROCESS.ROLE_USER in (SELECT r.DisplayName FROM TL_USER u 
						LEFT JOIN AbpUserRoles ur ON ur.UserId = u.ID
						LEFT JOIN AbpRoles r ON ur.RoleId = r.Id
						where u.TLNANME=@p_USERNAME)
						 AND BRANCH_ID=@BRANCH_ID
						 AND (DEP_ID IS NULL OR DEP_ID='' OR DEP_ID IN (SELECT DEP_ID FROM dbo.CM_KHOI_DT WHERE KHOI_ID IN  (SELECT KHOI_ID FROM dbo.CM_KHOI_DT WHERE DEP_ID=@DEP_ID)) )
						 AND (COST_ID IN (SELECT COST_ID FROM @lstCOST) OR COST_ID ='' OR COST_ID IS NULL)
						 ) OR
	   EXISTS (SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID  
						AND PROCESS_ID = 'DVKD' AND (DVKD_USER_APP IS NULL OR DVKD_USER_APP = '' OR DVKD_USER_APP = @p_USERNAME)
						 ))
	    AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
		AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
		AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
		AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
		AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
		AND (A.CONTRACT_ID LIKE '%' + @p_CONTRACT_ID + '%' OR @p_CONTRACT_ID IS NULL OR @p_CONTRACT_ID = '')
		AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
		AND (A.SUP_ADDR LIKE '%' + @p_SUP_ADDR + '%' OR @p_SUP_ADDR IS NULL OR @p_SUP_ADDR = '')		
		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 (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		--AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')		
		AND (C.CONTRACT_CODE LIKE '%' + @P_CONTRACT_CODE + '%' OR @P_CONTRACT_CODE = '' OR @P_CONTRACT_CODE IS NULL)
		AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1)
		AND (A.STATUS LIKE '%' + @p_STATUS + '%' OR @p_STATUS IS NULL OR @p_STATUS = '')
		--AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
		--AND (@p_RECEIVE_BRANCH IS NULL OR A.PO_ID IN (SELECT PO_ID FROM TR_PO_DETAIL WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH))
		--AND (@p_RECEIVE_BRANCH IS NULL OR @p_RECEIVE_BRANCH = '' OR EXISTS(SELECT REQ_DOC_ID FROM TR_REQUEST_DOC_DT WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH AND REQ_DOC_ID = A.REQ_ID))
		AND A.RECORD_STATUS = '1'
		AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID)
		AND (@p_DEP_ID IS NULL OR @p_DEP_ID = '' OR A.DEP_ID = @p_DEP_ID)
		ORDER BY A.CREATE_DT DESC
	-- PAGING END
   ELSE
   -- PAGING BEGIN
		SELECT TOP(CONVERT(INT,@P_TOP)) A.REQ_ID,
                                        A.REQ_CODE,
                                        A.REQ_NAME,
                                         ISNULL(PRP.APPROVE_DT,A.REQ_DT) AS REQ_DT,
                                        A.REQ_TYPE,
                                        A.REQ_CONTENT,
                                        A.CONTRACT_ID,
                                        A.SUP_ID,
                                        A.SUP_NAME,
                                        A.SUP_ADDR,
                                        A.TOTAL_AMT,
                                        A.NOTES,
                                        A.RECORD_STATUS,
                                        A.MAKER_ID,
                                        A.CREATE_DT,
                                        A.AUTH_STATUS,
                                        A.CHECKER_ID,
                                        A.APPROVE_DT,
                                        A.BRANCH_ID,
                                        A.STATUS,
                                        A.DEP_ID,
                                        A.HO_NOTES,C.CONTRACT_CODE, D.AUTH_STATUS_NAME,  E.SUP_CODE AS S_SUP_CODE, E.SUP_NAME AS S_SUP_NAME, E.ADDR AS S_ADDR, K.CONTENT AS REQ_TYPE_NAME, I.CONTENT AS REQ_STATUS_NAME,
				F.CONTRACT_CODE AS C_CONTRACT_CODE, F.[CONTRACT_NAME] AS C_CONTRACT_NAME,  F.TOTAL_AMT AS C_TOTAL_AMT ,
				ISNULL((SELECT SUM(TR_REQ.TOTAL_AMT) FROM TR_REQUEST_DOC TR_REQ WHERE TR_REQ.CONTRACT_ID = A.CONTRACT_ID),0) AS C_DONE_AMT, @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,G.BRANCH_NAME, PRP.DVKD_USER_APP
		-- SELECT END
		FROM TR_REQUEST_DOC A
		
		LEFT JOIN TR_CONTRACT C ON A.CONTRACT_ID = C.CONTRACT_ID  
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_SUPPLIER E ON E.SUP_ID= A.SUP_ID
		LEFT JOIN TR_CONTRACT F ON F.CONTRACT_ID = A.CONTRACT_ID --AND A.CONTRACT_ID IS NOT NULL
		LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
		LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQTYPE' AND K.CDVAL = A.REQ_TYPE
		LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS]
		LEFT JOIN dbo.PL_REQUEST_PROCESS PRP ON PRP.REQ_ID=A.REQ_ID AND   PRP.PROCESS_ID= (SELECT T.PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS T WHERE T.REQ_ID=A.REQ_ID AND T.PROCESS_ID=@p_STATUS) 

		WHERE 1 = 1
		 AND (A.MAKER_ID=@p_USERNAME  OR  (EXISTS(SELECT r.DisplayName FROM TL_USER u 
						LEFT JOIN AbpUserRoles ur ON ur.UserId = u.ID
						LEFT JOIN AbpRoles r ON ur.RoleId = r.Id
						where u.TLNANME=@p_USERNAME AND r.DisplayName='CVMS') ) OR
		 
	   EXISTS (SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID  
						AND (((PL_REQUEST_PROCESS.STATUS='C' OR PL_REQUEST_PROCESS.STATUS='P') AND (@p_AUTH_STATUS='' OR @p_AUTH_STATUS IS NULL)) OR 
								(@p_AUTH_STATUS='U' AND PL_REQUEST_PROCESS.STATUS='C') OR (@p_AUTH_STATUS='A' AND PL_REQUEST_PROCESS.STATUS='P'))
						AND PROCESS_ID <> 'DVKD'
						AND PL_REQUEST_PROCESS.ROLE_USER in (SELECT r.DisplayName FROM TL_USER u 
						LEFT JOIN AbpUserRoles ur ON ur.UserId = u.ID
						LEFT JOIN AbpRoles r ON ur.RoleId = r.Id
						where u.TLNANME=@p_USERNAME)
						 AND BRANCH_ID=@BRANCH_ID
						 AND (DEP_ID IS NULL OR DEP_ID='' OR DEP_ID IN (SELECT DEP_ID FROM dbo.CM_KHOI_DT WHERE KHOI_ID IN  (SELECT KHOI_ID FROM dbo.CM_KHOI_DT WHERE DEP_ID=@DEP_ID)) )
						 AND (COST_ID IN (SELECT COST_ID FROM @lstCOST) OR COST_ID ='' OR COST_ID IS NULL)
						 ) OR
	   EXISTS (SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID  
						AND PROCESS_ID = 'DVKD' AND (DVKD_USER_APP IS NULL OR DVKD_USER_APP = '' OR DVKD_USER_APP = @p_USERNAME)
						 ))
			
	    AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
		AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
		AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
		AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
		AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
		AND (A.CONTRACT_ID LIKE '%' + @p_CONTRACT_ID + '%' OR @p_CONTRACT_ID IS NULL OR @p_CONTRACT_ID = '')
		AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
		AND (A.SUP_ADDR LIKE '%' + @p_SUP_ADDR + '%' OR @p_SUP_ADDR IS NULL OR @p_SUP_ADDR = '')				
		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 (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		--AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')		
		AND (C.CONTRACT_CODE LIKE '%' + @P_CONTRACT_CODE + '%' OR @P_CONTRACT_CODE = '' OR @P_CONTRACT_CODE IS NULL)
		AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1)
			AND (A.STATUS LIKE '%' + @p_STATUS + '%' OR @p_STATUS IS NULL OR @p_STATUS = '')
		--AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
		--AND (@p_RECEIVE_BRANCH IS NULL OR A.PO_ID IN (SELECT PO_ID FROM TR_PO_DETAIL WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH))
		--AND (@p_RECEIVE_BRANCH IS NULL OR @p_RECEIVE_BRANCH = '' OR EXISTS(SELECT REQ_DOC_ID FROM TR_REQUEST_DOC_DT WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH AND REQ_DOC_ID = A.REQ_ID))
		AND A.RECORD_STATUS = '1'
		AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID)
		AND (@p_DEP_ID IS NULL OR @p_DEP_ID = '' OR A.DEP_ID = @p_DEP_ID)
		ORDER BY A.CREATE_DT DESC
	-- PAGING END
   END -- PAGING