ALTER   PROCEDURE [dbo].[CM_ATTACH_FILE_Search]
@p_ATTACH_ID	varchar(15)  = NULL,
@p_TYPE	varchar(50)  = NULL,
@p_REF_ID	varchar(15)  = NULL,
@p_FILE_NAME_OLD	nvarchar(200)  = NULL,
@p_PATH_OLD	nvarchar(1000)  = NULL,
@p_FILE_NAME_NEW	nvarchar(200)  = NULL,
@p_PATH_NEW	nvarchar(1000)  = NULL,
@p_FILE_SIZE	decimal = NULL,
@p_FILE_TYPE	varchar(50)  = NULL,
@p_ATTACH_DT	VARCHAR(20) = NULL,
@p_EMP_ID	varchar(15)  = NULL,
@P_EMP_NAME VARCHAR(1000) = NULL,
@p_TOP	INT = 10
AS
BEGIN TRANSACTION
IF(@p_TOP = NULL OR @P_TOP = '' OR @P_TOP = 0)
SELECT A.*,B.TLFullName
FROM CM_ATTACH_FILE A LEFT JOIN TL_USER B ON A.EMP_ID = B.TLNANME
 WHERE 1=1
	AND (A.ATTACH_ID LIKE '%' + @p_ATTACH_ID + '%' OR  @p_ATTACH_ID IS NULL OR @p_ATTACH_ID = '')
	AND (A.TYPE LIKE '%' + @p_TYPE + '%' OR  @p_TYPE IS NULL OR @p_TYPE = '')
	AND (A.REF_ID LIKE '%' + @p_REF_ID + '%' )
	AND (A.FILE_NAME_OLD LIKE '%' + @p_FILE_NAME_OLD + '%' OR  @p_FILE_NAME_OLD IS NULL OR @p_FILE_NAME_OLD = '')
	AND (A.PATH_OLD LIKE '%' + @p_PATH_OLD + '%' OR  @p_PATH_OLD IS NULL OR @p_PATH_OLD = '')
	AND (A.FILE_NAME_NEW LIKE '%' + @p_FILE_NAME_NEW + '%' OR  @p_FILE_NAME_NEW IS NULL OR @p_FILE_NAME_NEW = '')
	AND (A.PATH_NEW LIKE '%' + @p_PATH_NEW + '%' OR  @p_PATH_NEW IS NULL OR @p_PATH_NEW = '')
	AND (A.FILE_SIZE = @p_FILE_SIZE OR  @p_FILE_SIZE IS NULL)
	AND (A.FILE_TYPE LIKE '%' + @p_FILE_TYPE + '%' OR  @p_FILE_TYPE IS NULL OR @p_FILE_TYPE = '')
	AND (DATEDIFF(DAY,A.ATTACH_DT ,CONVERT(DATETIME, @p_ATTACH_DT, 103)) = 0 OR  @p_ATTACH_DT IS NULL OR @p_ATTACH_DT = '')
	AND (A.EMP_ID LIKE '%' + @p_EMP_ID + '%' OR  @p_EMP_ID IS NULL OR @p_EMP_ID = '')
	AND (B.TLFullName LIKE '%' + @p_EMP_NAME + '%' OR  @p_EMP_NAME IS NULL OR @p_EMP_NAME = '')
	AND(A.STATUS ='1' OR A.STATUS IS NULL)
	AND ((A.TYPE IN ('TR_CONTRACT','TR_REQUEST_DOC_FILE','PL_REQUEST_DOC_FILE') AND
			(A.REF_ID IN (SELECT TR_REQUEST_DOC_FILE_ID FROM TR_REQUEST_DOC_FILE) OR A.REF_ID IN (SELECT PL_REQUEST_DOC_FILE_ID FROM PL_REQUEST_DOC_FILE)))
			OR (A.TYPE NOT IN ('TR_CONTRACT','TR_REQUEST_DOC_FILE','PL_REQUEST_DOC_FILE')))
	
ELSE
SELECT TOP(CONVERT(INT,@P_TOP))A.*,B.TLFullName
FROM CM_ATTACH_FILE A LEFT JOIN TL_USER B ON A.EMP_ID = B.TLNANME
 WHERE 1=1
	AND (A.ATTACH_ID LIKE '%' + @p_ATTACH_ID + '%' OR  @p_ATTACH_ID IS NULL OR @p_ATTACH_ID = '')
	AND (A.TYPE LIKE '%' + @p_TYPE + '%' OR  @p_TYPE IS NULL OR @p_TYPE = '')
	AND (A.REF_ID LIKE '%' + @p_REF_ID + '%' )
	AND (A.FILE_NAME_OLD LIKE '%' + @p_FILE_NAME_OLD + '%' OR  @p_FILE_NAME_OLD IS NULL OR @p_FILE_NAME_OLD = '')
	AND (A.PATH_OLD LIKE '%' + @p_PATH_OLD + '%' OR  @p_PATH_OLD IS NULL OR @p_PATH_OLD = '')
	AND (A.FILE_NAME_NEW LIKE '%' + @p_FILE_NAME_NEW + '%' OR  @p_FILE_NAME_NEW IS NULL OR @p_FILE_NAME_NEW = '')
	AND (A.PATH_NEW LIKE '%' + @p_PATH_NEW + '%' OR  @p_PATH_NEW IS NULL OR @p_PATH_NEW = '')
	AND (A.FILE_SIZE = @p_FILE_SIZE OR  @p_FILE_SIZE IS NULL)
	AND (A.FILE_TYPE LIKE '%' + @p_FILE_TYPE + '%' OR  @p_FILE_TYPE IS NULL OR @p_FILE_TYPE = '')
	AND (DATEDIFF(DAY,A.ATTACH_DT ,CONVERT(DATETIME, @p_ATTACH_DT, 103)) = 0 OR  @p_ATTACH_DT IS NULL OR @p_ATTACH_DT = '')
	AND (A.EMP_ID LIKE '%' + @p_EMP_ID + '%' OR  @p_EMP_ID IS NULL OR @p_EMP_ID = '')
	AND (B.TLFullName LIKE '%' + @p_EMP_NAME + '%' OR  @p_EMP_NAME IS NULL OR @p_EMP_NAME = '')
	AND(A.STATUS ='1' OR A.STATUS IS NULL)
	AND ((A.TYPE IN ('TR_CONTRACT','TR_REQUEST_DOC_FILE','PL_REQUEST_DOC_FILE') AND
			(A.REF_ID IN (SELECT TR_REQUEST_DOC_FILE_ID FROM TR_REQUEST_DOC_FILE) OR A.REF_ID IN (SELECT PL_REQUEST_DOC_FILE_ID FROM PL_REQUEST_DOC_FILE)))
			OR (A.TYPE NOT IN ('TR_CONTRACT','TR_REQUEST_DOC_FILE','PL_REQUEST_DOC_FILE')))
COMMIT TRANSACTION