|
1
|
ALTER PROCEDURE [dbo].[CM_ATTACH_FILE_Search]
|
|
2
|
@p_ATTACH_ID varchar(15) = NULL,
|
|
3
|
@p_TYPE varchar(50) = NULL,
|
|
4
|
@p_REF_ID varchar(15) = NULL,
|
|
5
|
@p_FILE_NAME_OLD nvarchar(200) = NULL,
|
|
6
|
@p_PATH_OLD nvarchar(1000) = NULL,
|
|
7
|
@p_FILE_NAME_NEW nvarchar(200) = NULL,
|
|
8
|
@p_PATH_NEW nvarchar(1000) = NULL,
|
|
9
|
@p_FILE_SIZE decimal = NULL,
|
|
10
|
@p_FILE_TYPE varchar(50) = NULL,
|
|
11
|
@p_ATTACH_DT VARCHAR(20) = NULL,
|
|
12
|
@p_EMP_ID varchar(15) = NULL,
|
|
13
|
@P_EMP_NAME VARCHAR(1000) = NULL,
|
|
14
|
@p_TOP INT = 10
|
|
15
|
AS
|
|
16
|
BEGIN TRANSACTION
|
|
17
|
IF(@p_TOP = NULL OR @P_TOP = '' OR @P_TOP = 0)
|
|
18
|
SELECT A.*,B.TLFullName
|
|
19
|
FROM CM_ATTACH_FILE A LEFT JOIN TL_USER B ON A.EMP_ID = B.TLNANME
|
|
20
|
WHERE 1=1
|
|
21
|
AND (A.ATTACH_ID LIKE '%' + @p_ATTACH_ID + '%' OR @p_ATTACH_ID IS NULL OR @p_ATTACH_ID = '')
|
|
22
|
AND (A.TYPE LIKE '%' + @p_TYPE + '%' OR @p_TYPE IS NULL OR @p_TYPE = '')
|
|
23
|
AND (A.REF_ID LIKE '%' + @p_REF_ID + '%' )
|
|
24
|
AND (A.FILE_NAME_OLD LIKE '%' + @p_FILE_NAME_OLD + '%' OR @p_FILE_NAME_OLD IS NULL OR @p_FILE_NAME_OLD = '')
|
|
25
|
AND (A.PATH_OLD LIKE '%' + @p_PATH_OLD + '%' OR @p_PATH_OLD IS NULL OR @p_PATH_OLD = '')
|
|
26
|
AND (A.FILE_NAME_NEW LIKE '%' + @p_FILE_NAME_NEW + '%' OR @p_FILE_NAME_NEW IS NULL OR @p_FILE_NAME_NEW = '')
|
|
27
|
AND (A.PATH_NEW LIKE '%' + @p_PATH_NEW + '%' OR @p_PATH_NEW IS NULL OR @p_PATH_NEW = '')
|
|
28
|
AND (A.FILE_SIZE = @p_FILE_SIZE OR @p_FILE_SIZE IS NULL)
|
|
29
|
AND (A.FILE_TYPE LIKE '%' + @p_FILE_TYPE + '%' OR @p_FILE_TYPE IS NULL OR @p_FILE_TYPE = '')
|
|
30
|
AND (DATEDIFF(DAY,A.ATTACH_DT ,CONVERT(DATETIME, @p_ATTACH_DT, 103)) = 0 OR @p_ATTACH_DT IS NULL OR @p_ATTACH_DT = '')
|
|
31
|
AND (A.EMP_ID LIKE '%' + @p_EMP_ID + '%' OR @p_EMP_ID IS NULL OR @p_EMP_ID = '')
|
|
32
|
AND (B.TLFullName LIKE '%' + @p_EMP_NAME + '%' OR @p_EMP_NAME IS NULL OR @p_EMP_NAME = '')
|
|
33
|
AND(A.STATUS ='1' OR A.STATUS IS NULL)
|
|
34
|
AND ((A.TYPE IN ('TR_CONTRACT','TR_REQUEST_DOC_FILE','PL_REQUEST_DOC_FILE') AND
|
|
35
|
(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)))
|
|
36
|
OR (A.TYPE NOT IN ('TR_CONTRACT','TR_REQUEST_DOC_FILE','PL_REQUEST_DOC_FILE')))
|
|
37
|
|
|
38
|
ELSE
|
|
39
|
SELECT TOP(CONVERT(INT,@P_TOP))A.*,B.TLFullName
|
|
40
|
FROM CM_ATTACH_FILE A LEFT JOIN TL_USER B ON A.EMP_ID = B.TLNANME
|
|
41
|
WHERE 1=1
|
|
42
|
AND (A.ATTACH_ID LIKE '%' + @p_ATTACH_ID + '%' OR @p_ATTACH_ID IS NULL OR @p_ATTACH_ID = '')
|
|
43
|
AND (A.TYPE LIKE '%' + @p_TYPE + '%' OR @p_TYPE IS NULL OR @p_TYPE = '')
|
|
44
|
AND (A.REF_ID LIKE '%' + @p_REF_ID + '%' )
|
|
45
|
AND (A.FILE_NAME_OLD LIKE '%' + @p_FILE_NAME_OLD + '%' OR @p_FILE_NAME_OLD IS NULL OR @p_FILE_NAME_OLD = '')
|
|
46
|
AND (A.PATH_OLD LIKE '%' + @p_PATH_OLD + '%' OR @p_PATH_OLD IS NULL OR @p_PATH_OLD = '')
|
|
47
|
AND (A.FILE_NAME_NEW LIKE '%' + @p_FILE_NAME_NEW + '%' OR @p_FILE_NAME_NEW IS NULL OR @p_FILE_NAME_NEW = '')
|
|
48
|
AND (A.PATH_NEW LIKE '%' + @p_PATH_NEW + '%' OR @p_PATH_NEW IS NULL OR @p_PATH_NEW = '')
|
|
49
|
AND (A.FILE_SIZE = @p_FILE_SIZE OR @p_FILE_SIZE IS NULL)
|
|
50
|
AND (A.FILE_TYPE LIKE '%' + @p_FILE_TYPE + '%' OR @p_FILE_TYPE IS NULL OR @p_FILE_TYPE = '')
|
|
51
|
AND (DATEDIFF(DAY,A.ATTACH_DT ,CONVERT(DATETIME, @p_ATTACH_DT, 103)) = 0 OR @p_ATTACH_DT IS NULL OR @p_ATTACH_DT = '')
|
|
52
|
AND (A.EMP_ID LIKE '%' + @p_EMP_ID + '%' OR @p_EMP_ID IS NULL OR @p_EMP_ID = '')
|
|
53
|
AND (B.TLFullName LIKE '%' + @p_EMP_NAME + '%' OR @p_EMP_NAME IS NULL OR @p_EMP_NAME = '')
|
|
54
|
AND(A.STATUS ='1' OR A.STATUS IS NULL)
|
|
55
|
AND ((A.TYPE IN ('TR_CONTRACT','TR_REQUEST_DOC_FILE','PL_REQUEST_DOC_FILE') AND
|
|
56
|
(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)))
|
|
57
|
OR (A.TYPE NOT IN ('TR_CONTRACT','TR_REQUEST_DOC_FILE','PL_REQUEST_DOC_FILE')))
|
|
58
|
COMMIT TRANSACTION
|