|
1
|
CREATE OR ALTER PROCEDURE [dbo].[BUD_CONTRACT_RENT_Search]
|
|
2
|
@p_BUDC_RENT_CODE nvarchar(50) = NULL,
|
|
3
|
@p_BUDC_RENT_ID varchar(15) = NULL,
|
|
4
|
@p_BUDC_RENT_TYPE varchar(1) = NULL,
|
|
5
|
@p_BUDC_RENT_NAME NVARCHAR(200) = NULL,
|
|
6
|
@p_SIGN_DT VARCHAR(20) = NULL,
|
|
7
|
@p_CREATE_DT VARCHAR(50) = NULL,
|
|
8
|
@p_AUTH_STATUS VARCHAR(1) = NULL,
|
|
9
|
@p_AUTH_STATUS_DC VARCHAR(1) = NULL,
|
|
10
|
@p_START_DT VARCHAR(20) = NULL,
|
|
11
|
@p_END_DT VARCHAR(20) = NULL,
|
|
12
|
@p_PYC_ID varchar(15)=NULL,
|
|
13
|
@p_TOP INT =10,
|
|
14
|
@p_PYC_CODE VARCHAR(125) = NULL,
|
|
15
|
@p_PYC_NAME NVARCHAR(250) = NULL,
|
|
16
|
@p_CONT_TYPE VARCHAR(150) = NULL,
|
|
17
|
@p_BRANCH_ID VARCHAR(15)= NULL,
|
|
18
|
@p_BRANCH_LOGIN VARCHAR(15) = NULL,
|
|
19
|
@p_LEVEL VARCHAR(15) = 'ALL',
|
|
20
|
@p_MAKER_ID VARCHAR(100) = NULL,
|
|
21
|
@p_BUDC_RENT_STATUS VARCHAR(1) = NULL, -- Ở màn hình tìm kiếm dùng biến này tìm kiếm hiệu lực
|
|
22
|
@p_REQ_DOC_ID VARCHAR(15) = NULL,
|
|
23
|
@p_USER_LOGIN VARCHAR(15) = NULL,
|
|
24
|
@p_DEP_LOGIN VARCHAR(15) = NULL
|
|
25
|
---- Thêm location tìm kiếm theo điều kiện chức năng ------
|
|
26
|
, @p_LOCATION VARCHAR(100) = NULL
|
|
27
|
,@p_END_CONTRACT_DT VARCHAR(20) = NULL
|
|
28
|
AS
|
|
29
|
BEGIN -- PAGING
|
|
30
|
IF(@p_LEVEL IS NULL OR @p_LEVEL = '')
|
|
31
|
BEGIN
|
|
32
|
SET @p_LEVEL = 'ALL'
|
|
33
|
END
|
|
34
|
|
|
35
|
-- IF(@p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
36
|
-- BEGIN
|
|
37
|
-- SET @p_MAKER_ID = @p_USER_LOGIN
|
|
38
|
-- END
|
|
39
|
|
|
40
|
SET @p_TOP =2000
|
|
41
|
declare @tmp table(BRANCH_ID varchar(15))
|
|
42
|
insert into @tmp SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
|
|
43
|
|
|
44
|
|
|
45
|
IF(@p_TOP IS NULL OR @p_TOP = '' OR @p_TOP = 0)
|
|
46
|
-- PAGING BEGIN
|
|
47
|
SELECT A.*,
|
|
48
|
D.AUTH_STATUS_NAME,
|
|
49
|
'' AS SO_PYC, '' AS TEN_PYC,
|
|
50
|
CASE WHEN BR.BRANCH_TYPE = 'HS' THEN BR.BRANCH_NAME + ' - ' + CD.DEP_NAME ELSE BR.BRANCH_NAME END AS BRANCH_NAME,
|
|
51
|
BR.BRANCH_CODE, '' AS TO_TRINH_ID,
|
|
52
|
0.0 AS TOTAL_ADV_AMT, ---Số tiền đã tạm ứng
|
|
53
|
'' AS IS_FLAG_END, TU.TLFullName AS MAKER_NAME, TA.TLFullName AS CHECKER_NAME,
|
|
54
|
CB.BRANCH_CODE AS BRANCH_RENT_CODE,
|
|
55
|
CB.BRANCH_NAME AS BRANCH_RENT_NAME,
|
|
56
|
CD1.DEP_CODE AS DEP_RENT_CODE,
|
|
57
|
CD1.DEP_NAME AS DEP_RENT_NAME,
|
|
58
|
CASE
|
|
59
|
WHEN DATEDIFF(MONTH, GETDATE(),A.END_DT) <= 0 THEN N'Đã hết hạn'
|
|
60
|
ELSE CAST(DATEDIFF(MONTH, GETDATE(),A.END_DT) AS VARCHAR(50)) + N' Tháng'
|
|
61
|
END AS END_CONTRACT_DT
|
|
62
|
|
|
63
|
-- SELECT END
|
|
64
|
FROM BUD_CONTRACT_RENT A
|
|
65
|
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
|
|
66
|
LEFT JOIN CM_BRANCH BR ON A.BRANCH_ID= BR.BRANCH_ID
|
|
67
|
LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID= CD.DEP_ID
|
|
68
|
LEFT JOIN TL_USER TU ON A.MAKER_ID = TU.TLNANME
|
|
69
|
LEFT JOIN TL_USER TA ON A.CHECKER_ID = TA.TLNANME
|
|
70
|
LEFT JOIN CM_BRANCH CB ON A.BRANCH_RENT_ID = CB.BRANCH_ID --ĐƠN VỊ THUÊ
|
|
71
|
LEFT JOIN CM_DEPARTMENT CD1 ON A.DEP_RENT_ID = CD1.DEP_ID --PHÒNG BAN THUÊ
|
|
72
|
|
|
73
|
WHERE 1=1
|
|
74
|
AND (A.MAKER_ID like '%'+@p_MAKER_ID+'%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID like '')
|
|
75
|
AND (A.BUDC_RENT_CODE like '%'+@p_BUDC_RENT_CODE+'%' OR @p_BUDC_RENT_CODE IS NULL OR @p_BUDC_RENT_CODE like '')
|
|
76
|
AND (A.BUDC_RENT_ID like '%'+@p_BUDC_RENT_ID+'%' OR @p_BUDC_RENT_ID IS NULL OR @p_BUDC_RENT_ID like '')
|
|
77
|
AND (A.BUDC_RENT_NAME like '%'+ @p_BUDC_RENT_NAME +'%' OR @p_BUDC_RENT_NAME IS NULL OR @p_BUDC_RENT_NAME like '')
|
|
78
|
AND (A.AUTH_STATUS like '%'+@p_AUTH_STATUS +'%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS like '')
|
|
79
|
AND (A.AUTH_STATUS_DC like '%'+@p_AUTH_STATUS_DC +'%' OR @p_AUTH_STATUS_DC IS NULL OR @p_AUTH_STATUS_DC like '')
|
|
80
|
AND (DATEDIFF(DAY, CONVERT(DATETIME, @p_CREATE_DT,103),A.CREATE_DT) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
81
|
AND (A.BUDC_RENT_TYPE like '%'+@p_BUDC_RENT_TYPE +'%' OR @p_BUDC_RENT_TYPE IS NULL OR @p_BUDC_RENT_TYPE like '')
|
|
82
|
AND A.RECORD_STATUS = '1'
|
|
83
|
AND ((@p_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp)
|
|
84
|
OR (@p_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_LOGIN)) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL
|
|
85
|
AND (A.BRANCH_ID =@p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='')
|
|
86
|
)
|
|
87
|
|
|
88
|
AND (@p_LOCATION IS NULL OR @p_LOCATION = '' OR @p_LOCATION = 'BUD_MASTER' OR @p_LOCATION = 'BUD_CONTRACT'
|
|
89
|
OR (@p_LOCATION = 'UPDATE_RENT' AND A.AUTH_STATUS = 'A')
|
|
90
|
)
|
|
91
|
AND (
|
|
92
|
@p_END_CONTRACT_DT = '3T' AND DATEDIFF(MONTH, GETDATE(),A.END_DT)<=3
|
|
93
|
OR (@p_END_CONTRACT_DT = '6T' AND DATEDIFF(MONTH, GETDATE(),A.END_DT)<=6)
|
|
94
|
OR (@p_END_CONTRACT_DT = '9T' AND DATEDIFF(MONTH, GETDATE(),A.END_DT)<=9)
|
|
95
|
OR @p_END_CONTRACT_DT IS NULL OR @p_END_CONTRACT_DT = ''
|
|
96
|
)
|
|
97
|
AND ((@p_BUDC_RENT_STATUS = 'C' AND DATEDIFF(MONTH, GETDATE(),A.END_DT) > 0)
|
|
98
|
OR (@p_BUDC_RENT_STATUS = 'H' AND DATEDIFF(MONTH, GETDATE(),A.END_DT) <= 0)
|
|
99
|
OR @p_BUDC_RENT_STATUS IS NULL OR @p_BUDC_RENT_STATUS = ''
|
|
100
|
)
|
|
101
|
OR A.MAKER_ID = @p_USER_LOGIN
|
|
102
|
ORDER BY A.CREATE_DT DESC
|
|
103
|
-- PAGING END
|
|
104
|
ELSE
|
|
105
|
-- PAGING BEGIN
|
|
106
|
SELECT TOP(CONVERT(INT,@p_TOP)) A.*,
|
|
107
|
D.AUTH_STATUS_NAME,
|
|
108
|
'' AS SO_PYC, '' AS TEN_PYC,
|
|
109
|
CASE WHEN BR.BRANCH_TYPE = 'HS' THEN BR.BRANCH_NAME + ' - ' + CD.DEP_NAME ELSE BR.BRANCH_NAME END AS BRANCH_NAME, BR.BRANCH_CODE, '' AS TO_TRINH_ID,
|
|
110
|
0.0 AS TOTAL_ADV_AMT, ---Số tiền đã tạm ứng
|
|
111
|
'' AS IS_FLAG_END, TU.TLFullName AS MAKER_NAME, TA.TLFullName AS CHECKER_NAME,
|
|
112
|
CB.BRANCH_CODE AS BRANCH_RENT_CODE,
|
|
113
|
CB.BRANCH_NAME AS BRANCH_RENT_NAME,
|
|
114
|
CD1.DEP_CODE AS DEP_RENT_CODE,
|
|
115
|
CD1.DEP_NAME AS DEP_RENT_NAME,
|
|
116
|
CASE
|
|
117
|
WHEN DATEDIFF(MONTH, GETDATE(),A.END_DT) <= 0 THEN N'Đã hết hạn'
|
|
118
|
ELSE CAST(DATEDIFF(MONTH, GETDATE(),A.END_DT) AS VARCHAR(50)) + N' Tháng'
|
|
119
|
END AS END_CONTRACT_DT
|
|
120
|
|
|
121
|
-- SELECT END
|
|
122
|
FROM BUD_CONTRACT_RENT A
|
|
123
|
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
|
|
124
|
LEFT JOIN CM_BRANCH BR ON A.BRANCH_ID= BR.BRANCH_ID
|
|
125
|
LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID= CD.DEP_ID
|
|
126
|
LEFT JOIN TL_USER TU ON A.MAKER_ID = TU.TLNANME
|
|
127
|
LEFT JOIN TL_USER TA ON A.CHECKER_ID = TA.TLNANME
|
|
128
|
LEFT JOIN CM_BRANCH CB ON A.BRANCH_RENT_ID = CB.BRANCH_ID --ĐƠN VỊ THUÊ
|
|
129
|
LEFT JOIN CM_DEPARTMENT CD1 ON A.DEP_RENT_ID = CD1.DEP_ID --PHÒNG BAN THUÊ
|
|
130
|
|
|
131
|
WHERE 1=1
|
|
132
|
AND (A.MAKER_ID like '%'+@p_MAKER_ID+'%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID like '')
|
|
133
|
AND (A.BUDC_RENT_CODE like '%'+@p_BUDC_RENT_CODE+'%' OR @p_BUDC_RENT_CODE IS NULL OR @p_BUDC_RENT_CODE like '')
|
|
134
|
AND (A.BUDC_RENT_ID like '%'+@p_BUDC_RENT_ID+'%' OR @p_BUDC_RENT_ID IS NULL OR @p_BUDC_RENT_ID like '')
|
|
135
|
AND (A.BUDC_RENT_NAME like '%'+ @p_BUDC_RENT_NAME +'%' OR @p_BUDC_RENT_NAME IS NULL OR @p_BUDC_RENT_NAME like '')
|
|
136
|
AND (A.AUTH_STATUS like '%'+@p_AUTH_STATUS +'%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS like '')
|
|
137
|
AND (A.AUTH_STATUS_DC like '%'+@p_AUTH_STATUS_DC +'%' OR @p_AUTH_STATUS_DC IS NULL OR @p_AUTH_STATUS_DC like '')
|
|
138
|
AND (DATEDIFF(DAY, CONVERT(DATETIME, @p_CREATE_DT,103),A.CREATE_DT) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
139
|
AND (A.BUDC_RENT_TYPE like '%'+@p_BUDC_RENT_TYPE +'%' OR @p_BUDC_RENT_TYPE IS NULL OR @p_BUDC_RENT_TYPE like '')
|
|
140
|
AND A.RECORD_STATUS = '1'
|
|
141
|
AND ((@p_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp)
|
|
142
|
OR (@p_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_LOGIN)) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL
|
|
143
|
AND (A.BRANCH_ID =@p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='')
|
|
144
|
)
|
|
145
|
AND (@p_LOCATION IS NULL OR @p_LOCATION = '' OR @p_LOCATION = 'BUD_MASTER' OR @p_LOCATION = 'BUD_CONTRACT'
|
|
146
|
OR (@p_LOCATION = 'UPDATE_RENT' AND A.AUTH_STATUS = 'A')
|
|
147
|
)
|
|
148
|
AND (
|
|
149
|
@p_END_CONTRACT_DT = '3T' AND DATEDIFF(MONTH, GETDATE(),A.END_DT)<=3
|
|
150
|
OR (@p_END_CONTRACT_DT = '6T' AND DATEDIFF(MONTH, GETDATE(),A.END_DT)<=6)
|
|
151
|
OR (@p_END_CONTRACT_DT = '9T' AND DATEDIFF(MONTH, GETDATE(),A.END_DT)<=9)
|
|
152
|
OR @p_END_CONTRACT_DT IS NULL OR @p_END_CONTRACT_DT = ''
|
|
153
|
)
|
|
154
|
AND ((@p_BUDC_RENT_STATUS = 'C' AND DATEDIFF(MONTH, GETDATE(),A.END_DT) > 0)
|
|
155
|
OR (@p_BUDC_RENT_STATUS = 'H' AND DATEDIFF(MONTH, GETDATE(),A.END_DT) <= 0)
|
|
156
|
OR @p_BUDC_RENT_STATUS IS NULL OR @p_BUDC_RENT_STATUS = ''
|
|
157
|
)
|
|
158
|
OR A.MAKER_ID = @p_USER_LOGIN
|
|
159
|
ORDER BY A.CREATE_DT DESC
|
|
160
|
-- PAGING END
|
|
161
|
END -- PAGING
|
|
162
|
|
|
163
|
|