|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[RET_MASTER_Search]
|
|
3
|
@p_RET_ID varchar(15) = NULL,
|
|
4
|
@p_ASSET_ID VARCHAR(15) = NULL,
|
|
5
|
@P_ASSET_CODE VARCHAR(15) = NULL,
|
|
6
|
@P_DIV_ID VARCHAR(15) = NULL,
|
|
7
|
@P_DIV_CODE VARCHAR(15) = NULL,
|
|
8
|
@P_OWNER VARCHAR(50) = NULL,
|
|
9
|
@p_LENGTH DECIMAL(18,2) = NULL,
|
|
10
|
@p_WIDTH DECIMAL(18,2) = NULL,
|
|
11
|
@p_CURRENT_STATE nvarchar(100) = NULL,
|
|
12
|
@p_RET_TYPE varchar(15) = NULL,
|
|
13
|
@p_STATUS varchar(15) = NULL,
|
|
14
|
@p_LAND_SQUARE DECIMAL(18,2) = NULL,
|
|
15
|
@p_CONSTRUCT_SQUARE DECIMAL(18,2) = NULL,
|
|
16
|
@p_TOTAL_SQUARE DECIMAL(18,2) = NULL,
|
|
17
|
@p_BOUNDARY nvarchar(100) = NULL,
|
|
18
|
@p_HOUSEDES nvarchar(100) = NULL,
|
|
19
|
@p_PURPOSE_IN_USE nvarchar(100) = NULL,
|
|
20
|
@P_W_USE_CON NVARCHAR(100) = NULL,
|
|
21
|
@p_USE_STATUS varchar(15) = NULL,
|
|
22
|
@p_CONST_STATUS varchar(15) = NULL,
|
|
23
|
@p_OWNER_TYPE varchar(15) = NULL,
|
|
24
|
@p_USE_PERIOD int = NULL,
|
|
25
|
@p_PERSON_HOLDER nvarchar(100) = NULL,
|
|
26
|
@p_NOTES nvarchar(100) = NULL,
|
|
27
|
@p_RECORD_STATUS varchar(1) = NULL,
|
|
28
|
@p_AUTH_STATUS varchar(1) = NULL,
|
|
29
|
@p_MAKER_ID varchar(15) = NULL,
|
|
30
|
@p_CREATE_DT VARCHAR(20) = NULL,
|
|
31
|
@p_CHECKER_ID varchar(15) = NULL,
|
|
32
|
@p_APPROVE_DT VARCHAR(20) = NULL,
|
|
33
|
@p_TOP INT = 10,
|
|
34
|
------------------BAODNQ 16/2/2022: Thêm tham số------------
|
|
35
|
@p_USER_LOGIN VARCHAR(15)
|
|
36
|
|
|
37
|
AS
|
|
38
|
--Validation is here
|
|
39
|
/*
|
|
40
|
DECLARE @ERRORSYS NVARCHAR(15) = ''
|
|
41
|
IF ( [NOT] EXISTS ( SELECT * FROM [TABLE] WHERE [CONDITION] ))
|
|
42
|
SET @ERRORSYS = ''
|
|
43
|
IF @ERRORSYS <> ''
|
|
44
|
BEGIN
|
|
45
|
SELECT ErrorCode Result, '' CAR_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
|
|
46
|
RETURN '0'
|
|
47
|
END */
|
|
48
|
|
|
49
|
BEGIN -- PAGING
|
|
50
|
|
|
51
|
DECLARE @p_USER_LOGIN_ROLE VARCHAR(50) = (SELECT RoleName FROM TL_USER WHERE TLNANME = @p_USER_LOGIN)
|
|
52
|
|
|
53
|
IF(@p_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
|
|
54
|
-- PAGING BEGIN
|
|
55
|
SELECT A.*,(CASE WHEN USE_PERIOD = 0 THEN N'Lâu dài' ELSE convert(varchar(100),USE_PERIOD_DT, 103) END) AS USE_PERIOD_NAME, B.AUTH_STATUS_NAME, C.CONTENT, D.ASSET_NAME, D.BRANCH_ID,
|
|
56
|
CASE WHEN BR.BRANCH_TYPE = 'HS' THEN DP.DEP_NAME ELSE BR.BRANCH_NAME END AS BRANCH_NAME,
|
|
57
|
G.ADDR as ASS_ADDR,
|
|
58
|
D.BUY_PRICE,G.DIV_ID,G.DIV_CODE,G.DIV_NAME,E.CONTENT AS RET_STATUS, AST.TYPE_NAME AS ASSET_TYPE, ASG.GROUP_NAME AS ASS_GROUP,
|
|
59
|
-----------BAODNQ 15/3/2022 Lấy thông tin xuất excel
|
|
60
|
C.CONTENT AS RET_TYPE_NAME, --loại BDS
|
|
61
|
UF.CONTENT AS USE_FORM_NAME, -- hình thức sử dụng
|
|
62
|
US.CONTENT AS USE_STATUS_NAME, --tình trạng sử dụng đất
|
|
63
|
CS.CONTENT AS CONST_STATUS_NAME, --tình trạng xây dựng
|
|
64
|
E.CONTENT AS RET_STATUS_NAME, --hiện trạng pháp lý gắn liền vs đất
|
|
65
|
OT.CONTENT AS OWNER_TYPE_NAME -- loại sỡ hữu
|
|
66
|
-- SELECT END
|
|
67
|
|
|
68
|
FROM RET_MASTER A
|
|
69
|
INNER JOIN CM_AUTH_STATUS B ON B.AUTH_STATUS = A.AUTH_STATUS
|
|
70
|
INNER JOIN CM_ALLCODE C ON C.CDVAL = A.RET_TYPE AND C.CDNAME = 'RET_TYPE'AND C.CDTYPE = 'RET'
|
|
71
|
INNER JOIN CM_ALLCODE E ON E.CDVAL = A.[STATUS] AND E.CDNAME = 'RET_STATUS' AND E.CDTYPE = 'RET'
|
|
72
|
INNER JOIN ASS_MASTER D ON D.ASSET_ID = A.ASSET_ID
|
|
73
|
LEFT JOIN CM_DIVISION G ON G.DIV_ID=D.DIVISION_ID
|
|
74
|
--LEFT JOIN CM_BRANCH BR ON D.BRANCH_CREATE = BR.BRANCH_ID
|
|
75
|
LEFT JOIN CM_BRANCH BR ON D.BRANCH_ID = BR.BRANCH_ID
|
|
76
|
LEFT JOIN CM_DEPARTMENT DP ON DP.DEP_ID = D.DEPT_ID
|
|
77
|
LEFT JOIN ASS_MASTER AMS ON A.ASSET_ID = AMS.ASSET_ID
|
|
78
|
LEFT JOIN ASS_TYPE AST ON AMS.TYPE_ID = AST.TYPE_ID
|
|
79
|
LEFT JOIN ASS_GROUP ASG ON AMS.GROUP_ID = ASG.GROUP_ID
|
|
80
|
----------------------BAODNQ 15/3/2022: Lấy thông tin xuất excel-----
|
|
81
|
LEFT JOIN CM_ALLCODE UF ON A.USE_FORM = UF.CDVAL AND UF.CDNAME = 'USE_FORM' AND UF.CDTYPE = 'RET'
|
|
82
|
LEFT JOIN CM_ALLCODE US ON A.USE_STATUS = US.CDVAL AND US.CDNAME = 'USE_STATUS' AND US.CDTYPE = 'RET'
|
|
83
|
LEFT JOIN CM_ALLCODE CS ON A.CONST_STATUS = CS.CDVAL AND CS.CDNAME = 'CONST_STATUS' AND CS.CDTYPE = 'RET'
|
|
84
|
LEFT JOIN CM_ALLCODE OT ON A.OWNER_TYPE = OT.CDVAL AND OT.CDNAME = 'OWNER_TYPE' AND OT.CDTYPE = 'RET'
|
|
85
|
|
|
86
|
|
|
87
|
WHERE 1 = 1
|
|
88
|
AND (A.RET_ID LIKE '%' + @p_RET_ID + '%' OR @p_RET_ID IS NULL OR @p_RET_ID = '')
|
|
89
|
AND (A.ASSET_ID LIKE '%' + @p_ASSET_ID + '%' OR @p_ASSET_ID IS NULL OR @p_ASSET_ID = '')
|
|
90
|
AND (A.ASSET_CODE LIKE '%' + @P_ASSET_CODE + '%' OR @P_ASSET_CODE IS NULL OR @P_ASSET_CODE = '')
|
|
91
|
AND (A.OWNER LIKE '%' + @P_OWNER + '%' OR @P_OWNER IS NULL OR @P_OWNER = '')
|
|
92
|
AND (A.LENGTH = @p_LENGTH OR @p_LENGTH IS NULL)
|
|
93
|
AND (A.WIDTH = @p_WIDTH OR @p_WIDTH IS NULL)
|
|
94
|
AND (A.CURRENT_STATE LIKE '%' + @p_CURRENT_STATE + '%' OR @p_CURRENT_STATE IS NULL OR @p_CURRENT_STATE = '')
|
|
95
|
AND (A.RET_TYPE LIKE '%' + @p_RET_TYPE + '%' OR @p_RET_TYPE IS NULL OR @p_RET_TYPE = '')
|
|
96
|
AND (A.STATUS LIKE '%' + @p_STATUS + '%' OR @p_STATUS IS NULL OR @p_STATUS = '')
|
|
97
|
AND (A.LAND_SQUARE = @p_LAND_SQUARE OR @p_LAND_SQUARE IS NULL)
|
|
98
|
AND (A.CONSTRUCT_SQUARE = @p_CONSTRUCT_SQUARE OR @p_CONSTRUCT_SQUARE IS NULL)
|
|
99
|
AND (A.TOTAL_SQUARE = @p_TOTAL_SQUARE OR @p_TOTAL_SQUARE IS NULL)
|
|
100
|
AND (A.BOUNDARY LIKE '%' + @p_BOUNDARY + '%' OR @p_BOUNDARY IS NULL OR @p_BOUNDARY = '')
|
|
101
|
AND (A.HOUSEDES LIKE '%' + @p_HOUSEDES + '%' OR @p_HOUSEDES IS NULL OR @p_HOUSEDES = '')
|
|
102
|
AND (A.PURPOSE_IN_USE LIKE '%' + @p_PURPOSE_IN_USE + '%' OR @p_PURPOSE_IN_USE IS NULL OR @p_PURPOSE_IN_USE = '')
|
|
103
|
AND (A.W_USE_CON LIKE '%' + @P_W_USE_CON + '%' OR @P_W_USE_CON IS NULL OR @P_W_USE_CON = '')
|
|
104
|
AND (A.USE_STATUS LIKE '%' + @p_USE_STATUS + '%' OR @p_USE_STATUS IS NULL OR @p_USE_STATUS = '')
|
|
105
|
AND (A.CONST_STATUS LIKE '%' + @p_CONST_STATUS + '%' OR @p_CONST_STATUS IS NULL OR @p_CONST_STATUS = '')
|
|
106
|
AND (A.OWNER_TYPE LIKE '%' + @p_OWNER_TYPE + '%' OR @p_OWNER_TYPE IS NULL OR @p_OWNER_TYPE = '')
|
|
107
|
AND (A.USE_PERIOD = @p_USE_PERIOD OR @p_USE_PERIOD IS NULL)
|
|
108
|
AND (A.PERSON_HOLDER LIKE '%' + @p_PERSON_HOLDER + '%' OR @p_PERSON_HOLDER IS NULL OR @p_PERSON_HOLDER = '')
|
|
109
|
AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR @p_NOTES IS NULL OR @p_NOTES = '')
|
|
110
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
111
|
AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
112
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
113
|
AND (DATEDIFF(DAY,A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
114
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
115
|
AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
116
|
AND (A.RECORD_STATUS = '1')
|
|
117
|
AND (G.DIV_ID LIKE '%' + @P_DIV_ID + '%' OR @P_DIV_ID = '' OR @P_DIV_ID IS NULL)
|
|
118
|
AND (G.DIV_CODE LIKE '%' + @P_DIV_CODE + '%' OR @P_DIV_CODE = '' OR @P_DIV_CODE IS NULL)
|
|
119
|
-------BAODNQ 16/2/2022: Thêm điều kiện search-------
|
|
120
|
AND (A.MAKER_ID = @p_USER_LOGIN ---- user login là ng tạo
|
|
121
|
OR (A.SIGN_USER = @p_USER_LOGIN AND A.IS_SEND_APPR = 'Y') --- user login là cấp duyệt trung gian và đã gửi YC phê duyệt
|
|
122
|
OR ((@p_USER_LOGIN_ROLE IN('GDDV','PGD', 'TP', 'PP','TPGD','PPGD', 'TBP','PP')) --- user login là trưởng ĐV và đã gửi YC phê duyệt
|
|
123
|
AND A.IS_SEND_APPR = 'Y'
|
|
124
|
AND (
|
|
125
|
ISNULL(A.SIGN_USER,'') = '' OR (ISNULL(A.SIGN_USER,'') <> '' AND A.SIGN_DT IS NOT NULL)
|
|
126
|
)
|
|
127
|
|
|
128
|
)
|
|
129
|
)
|
|
130
|
|
|
131
|
-- PAGING END
|
|
132
|
ELSE
|
|
133
|
-- PAGING BEGIN
|
|
134
|
SELECT TOP(CONVERT(INT,@P_TOP)) A.*, B.AUTH_STATUS_NAME, C.CONTENT, D.ASSET_NAME, D.BRANCH_ID,
|
|
135
|
CASE WHEN BR.BRANCH_TYPE = 'HS' THEN DP.DEP_NAME ELSE BR.BRANCH_NAME END AS BRANCH_NAME,
|
|
136
|
D.BUY_PRICE,G.DIV_ID,G.DIV_CODE,G.DIV_NAME, E.CONTENT AS RET_STATUS,
|
|
137
|
-----------BAODNQ 15/3/2022 Lấy thông tin xuất excel
|
|
138
|
C.CONTENT AS RET_TYPE_NAME, --loại BDS
|
|
139
|
UF.CONTENT AS USE_FORM_NAME, -- hình thức sử dụng
|
|
140
|
US.CONTENT AS USE_STATUS_NAME, --tình trạng sử dụng đất
|
|
141
|
CS.CONTENT AS CONST_STATUS_NAME, --tình trạng xây dựng
|
|
142
|
E.CONTENT AS RET_STATUS_NAME, --hiện trạng pháp lý gắn liền vs đất
|
|
143
|
OT.CONTENT AS OWNER_TYPE_NAME -- loại sỡ hữu
|
|
144
|
-- SELECT END
|
|
145
|
FROM RET_MASTER A
|
|
146
|
INNER JOIN CM_AUTH_STATUS B ON B.AUTH_STATUS = A.AUTH_STATUS
|
|
147
|
INNER JOIN CM_ALLCODE C ON C.CDVAL = A.RET_TYPE AND C.CDNAME = 'RET_TYPE'AND C.CDTYPE = 'RET'
|
|
148
|
INNER JOIN CM_ALLCODE E ON E.CDVAL = A.[STATUS] AND E.CDNAME = 'RET_STATUS' AND E.CDTYPE = 'RET'
|
|
149
|
INNER JOIN ASS_MASTER D ON D.ASSET_ID = A.ASSET_ID
|
|
150
|
LEFT JOIN CM_DIVISION G ON G.DIV_ID=D.DIVISION_ID
|
|
151
|
--LEFT JOIN CM_BRANCH BR ON D.BRANCH_CREATE = BR.BRANCH_ID
|
|
152
|
LEFT JOIN CM_BRANCH BR ON D.BRANCH_ID = BR.BRANCH_ID
|
|
153
|
LEFT JOIN CM_DEPARTMENT DP ON DP.DEP_ID = D.DEPT_ID
|
|
154
|
----------------------BAODNQ 15/3/2022: Lấy thông tin xuất excel-----
|
|
155
|
LEFT JOIN CM_ALLCODE UF ON A.USE_FORM = UF.CDVAL AND UF.CDNAME = 'USE_FORM' AND UF.CDTYPE = 'RET'
|
|
156
|
LEFT JOIN CM_ALLCODE US ON A.USE_STATUS = US.CDVAL AND US.CDNAME = 'USE_STATUS' AND US.CDTYPE = 'RET'
|
|
157
|
LEFT JOIN CM_ALLCODE CS ON A.CONST_STATUS = CS.CDVAL AND CS.CDNAME = 'CONST_STATUS' AND CS.CDTYPE = 'RET'
|
|
158
|
LEFT JOIN CM_ALLCODE OT ON A.OWNER_TYPE = OT.CDVAL AND OT.CDNAME = 'OWNER_TYPE' AND OT.CDTYPE = 'RET'
|
|
159
|
|
|
160
|
WHERE 1 = 1
|
|
161
|
AND (A.RET_ID LIKE '%' + @p_RET_ID + '%' OR @p_RET_ID IS NULL OR @p_RET_ID = '')
|
|
162
|
AND (A.ASSET_ID LIKE '%' + @p_ASSET_ID + '%' OR @p_ASSET_ID IS NULL OR @p_ASSET_ID = '')
|
|
163
|
AND (A.ASSET_CODE LIKE '%' + @P_ASSET_CODE + '%' OR @P_ASSET_CODE IS NULL OR @P_ASSET_CODE = '')
|
|
164
|
AND (A.OWNER LIKE '%' + @P_OWNER + '%' OR @P_OWNER IS NULL OR @P_OWNER = '')
|
|
165
|
AND (A.LENGTH = @p_LENGTH OR @p_LENGTH IS NULL)
|
|
166
|
AND (A.WIDTH = @p_WIDTH OR @p_WIDTH IS NULL)
|
|
167
|
AND (A.CURRENT_STATE LIKE '%' + @p_CURRENT_STATE + '%' OR @p_CURRENT_STATE IS NULL OR @p_CURRENT_STATE = '')
|
|
168
|
AND (A.RET_TYPE LIKE '%' + @p_RET_TYPE + '%' OR @p_RET_TYPE IS NULL OR @p_RET_TYPE = '')
|
|
169
|
AND (A.STATUS LIKE '%' + @p_STATUS + '%' OR @p_STATUS IS NULL OR @p_STATUS = '')
|
|
170
|
AND (A.LAND_SQUARE = @p_LAND_SQUARE OR @p_LAND_SQUARE IS NULL)
|
|
171
|
AND (A.CONSTRUCT_SQUARE = @p_CONSTRUCT_SQUARE OR @p_CONSTRUCT_SQUARE IS NULL)
|
|
172
|
AND (A.TOTAL_SQUARE = @p_TOTAL_SQUARE OR @p_TOTAL_SQUARE IS NULL)
|
|
173
|
AND (A.BOUNDARY LIKE '%' + @p_BOUNDARY + '%' OR @p_BOUNDARY IS NULL OR @p_BOUNDARY = '')
|
|
174
|
AND (A.HOUSEDES LIKE '%' + @p_HOUSEDES + '%' OR @p_HOUSEDES IS NULL OR @p_HOUSEDES = '')
|
|
175
|
AND (A.PURPOSE_IN_USE LIKE '%' + @p_PURPOSE_IN_USE + '%' OR @p_PURPOSE_IN_USE IS NULL OR @p_PURPOSE_IN_USE = '')
|
|
176
|
AND (A.W_USE_CON LIKE '%' + @P_W_USE_CON + '%' OR @P_W_USE_CON IS NULL OR @P_W_USE_CON = '')
|
|
177
|
AND (A.USE_STATUS LIKE '%' + @p_USE_STATUS + '%' OR @p_USE_STATUS IS NULL OR @p_USE_STATUS = '')
|
|
178
|
AND (A.CONST_STATUS LIKE '%' + @p_CONST_STATUS + '%' OR @p_CONST_STATUS IS NULL OR @p_CONST_STATUS = '')
|
|
179
|
AND (A.OWNER_TYPE LIKE '%' + @p_OWNER_TYPE + '%' OR @p_OWNER_TYPE IS NULL OR @p_OWNER_TYPE = '')
|
|
180
|
AND (A.USE_PERIOD = @p_USE_PERIOD OR @p_USE_PERIOD IS NULL)
|
|
181
|
AND (A.PERSON_HOLDER LIKE '%' + @p_PERSON_HOLDER + '%' OR @p_PERSON_HOLDER IS NULL OR @p_PERSON_HOLDER = '')
|
|
182
|
AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR @p_NOTES IS NULL OR @p_NOTES = '')
|
|
183
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
184
|
AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
185
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
186
|
AND (DATEDIFF(DAY,A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
187
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
188
|
AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
189
|
AND (A.RECORD_STATUS = '1')
|
|
190
|
AND (G.DIV_ID LIKE '%' + @P_DIV_ID + '%' OR @P_DIV_ID = '' OR @P_DIV_ID IS NULL)
|
|
191
|
AND (G.DIV_CODE LIKE '%' + @P_DIV_CODE + '%' OR @P_DIV_CODE = '' OR @P_DIV_CODE IS NULL)
|
|
192
|
-------BAODNQ 16/2/2022: Thêm điều kiện search-------
|
|
193
|
AND (A.MAKER_ID = @p_USER_LOGIN ---- user login là ng tạo
|
|
194
|
OR (A.SIGN_USER = @p_USER_LOGIN AND A.IS_SEND_APPR = 'Y') --- user login là cấp duyệt trung gian và đã gửi YC phê duyệt
|
|
195
|
OR ((@p_USER_LOGIN_ROLE IN('GDDV','PGD', 'TP', 'PP','TPGD','PPGD', 'TBP','PP')) --- user login là trưởng ĐV và đã gửi YC phê duyệt
|
|
196
|
AND A.IS_SEND_APPR = 'Y'
|
|
197
|
AND (
|
|
198
|
ISNULL(A.SIGN_USER,'') = '' OR (ISNULL(A.SIGN_USER,'') <> '' AND A.SIGN_DT IS NOT NULL)
|
|
199
|
)
|
|
200
|
|
|
201
|
)
|
|
202
|
)
|
|
203
|
-- PAGING END
|
|
204
|
END -- PAGING
|
|
205
|
|