· 8 years ago · Feb 27, 2018, 10:42 AM
1
2--GET ORDERS
3IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Order_fetchGridList') )
4BEGIN
5 DROP PROCEDURE Order_fetchGridList
6END
7
8GO
9
10--Fetching the first grid
11create PROCEDURE [dbo].[Order_fetchGridList](
12 @freetext as nvarchar(500),
13 @organizationId as int,
14 @includeDeactivated as tinyint,
15 @fromDate as datetime,
16 @toDate as datetime,
17 @customerId as int,
18 @loggedUserDepartmentId int,
19 @central tinyint
20 )
21AS
22BEGIN
23
24 DECLARE @departmentHandling as nvarchar(1500);
25 DECLARE @ordersFields as nvarchar(1500);
26 DECLARE @sharedWhere as nvarchar(1500);
27 DECLARE @joinsSql as nvarchar(1500);
28 /*
29 Shared department handling
30 Original version was written without optimization logic around the central parameter
31 Original version duplicated code.
32 */
33
34 SET @ordersFields = '
35 BOT_ORDER.BOT_ORDER_ID
36 ,BOT_ORDER.BOT_PARENT_ID
37 ,BOT_ORDER.BOT_ORDER_ACTIVE
38 ,BOT_ORDER.BOT_CUSTOMER_ID
39 ,GOT_STATUS.GOT_STATUS_PROGID
40 ,BOT_ORDER.BOT_ORDER_DATE
41 ,BOT_ORDER.BOT_ORDER_TAG
42 ,BOT_ORDER.BOT_ORDER_EXTERNAL_ID
43 ,GOT_TYPE.GOT_TYPE_PROGID
44 ,BOT_ORDER.BOT_ADDRESS_ID
45 ,BOT_ORDER.BOT_ORDER_NOTE
46 ,BOT_ORDER.BOT_ORDER_START_DATE
47 ,BOT_ORDER.BOT_ORDER_END_DATE
48 ,BOT_ORDER.BOT_ORDER_CREATED_DATE
49 ,BOT_ORDER.BOT_ORDER_LASTUPDATED_DATE
50 ,LASTUPDATED_BY_USER.GOT_USER_FIRSTNAME AS LASTUPDATEBY_USER_FIRSTNAME
51 ,LASTUPDATED_BY_USER.GOT_USER_LASTNAME AS LASTUPDATEBY_USER_LASTNAME
52 ,GOT_DEPARTMENT.GOT_DEPARTMENT_ID
53 ,GOT_DEPARTMENT.GOT_DEPARTMENT_NAME
54 ,BOT_ORDER.BOT_ORDER_PROPERTYID
55 ,BOT_ORDER.BOT_ORDER_DESCRIPTION
56 ,BOT_ORDER.BOT_ORDER_CUSTOMER_REF
57 ,BOT_ORDER.BOT_ORDER_OUR_REF
58 ,BOT_ORDER.BOT_ORDER_COMMENT
59 ';
60
61 SET @sharedWhere = '';
62 SET @departmentHandling = '';
63
64 IF ( ISNULL(@central,0) <> 1 )
65 BEGIN
66 set @departmentHandling = @departmentHandling + '
67 AND
68 ((
69 (
70 GOT_DEPARTMENT.GOT_DEPARTMENT_IS_PUBLIC = 0
71 OR LOGGED_USER_DEPARTMENT.GOT_DEPARTMENT_IS_PUBLIC = 0
72 )
73 AND GOT_DEPARTMENT.GOT_DEPARTMENT_ID = ' + convert(nvarchar(50), ISNULL(@loggedUserDepartmentId,0)) + '
74
75 )
76 OR (
77 GOT_DEPARTMENT.GOT_DEPARTMENT_IS_PUBLIC = 1
78 AND LOGGED_USER_DEPARTMENT.GOT_DEPARTMENT_IS_PUBLIC = 1
79
80 )
81 )'
82 END
83
84 IF ISNULL(@organizationId, 0) > 0
85 BEGIN
86 SET @sharedWhere += ' AND BOT_ORDER.GOT_ORGANIZATION_ID = ' + cast(@organizationId as nvarchar(max))
87 END
88
89 IF @includeDeactivated = 0
90 BEGIN
91 SET @sharedWhere += ' AND BOT_ORDER.BOT_ORDER_ACTIVE = 1'
92 END
93
94 IF len( cast(@fromDate as nvarchar(max))) > 0
95 BEGIN
96 SET @sharedWhere += ' AND BOT_ORDER.BOT_ORDER_DATE BETWEEN ''' + cast(@fromDate as nvarchar(max)) + ''' and ''' + cast(ISNULL(@toDate, GETDATE()) + 0.9 as nvarchar(max)) + ''''
97 END
98
99 IF ISNULL(@customerId, 0) > 0
100 BEGIN
101 SET @sharedWhere += ' AND BOT_ORDER.BOT_CUSTOMER_ID = ' + cast(@customerId as nvarchar(max))
102 END
103
104 SET @joinsSql = '
105 LEFT JOIN BOT_CUSTOMER ON BOT_ORDER.BOT_CUSTOMER_ID = BOT_CUSTOMER.BOT_CUSTOMER_ID
106 INNER JOIN GOT_STATUS ON BOT_ORDER.GOT_STATUS_ID = GOT_STATUS.GOT_STATUS_ID
107 INNER JOIN GOT_TYPE ON BOT_ORDER.GOT_TYPE_ID = GOT_TYPE.GOT_TYPE_ID
108 LEFT JOIN BOT_ADDRESS ON BOT_ORDER.BOT_ADDRESS_ID = BOT_ADDRESS.BOT_ADDRESS_ID
109 INNER JOIN GOT_USER AS LASTUPDATED_BY_USER ON LASTUPDATED_BY_USER.GOT_USER_ID = BOT_ORDER.BOT_ORDER_LASTUPDATED_BY_GOT_USER_ID
110 INNER JOIN GOT_USER AS CREATED_BY_USER ON CREATED_BY_USER.GOT_USER_ID = BOT_ORDER.BOT_ORDER_CREATED_BY_GOT_USER_ID
111 INNER JOIN GOT_DEPARTMENT ON GOT_DEPARTMENT.GOT_DEPARTMENT_ID = CREATED_BY_USER.GOT_DEPARTMENT_ID
112 INNER JOIN GOT_DEPARTMENT AS LOGGED_USER_DEPARTMENT ON LOGGED_USER_DEPARTMENT.GOT_DEPARTMENT_ID = ' + CAST(ISNULL(@loggedUserDepartmentId, 0) as nvarchar(50)) + '
113 ';
114
115 IF @freeText = ''
116 BEGIN
117 SET @freeText = null;
118 END
119
120 declare @sql as nvarchar(max);
121
122 IF @freetext is null
123 BEGIN
124 SET @sql = 'SELECT TOP 1000
125 ' + @ordersFields + '
126 ';
127 SET @sql += 'FROM BOT_ORDER
128 '+ @joinsSql + '
129 WHERE BOT_ORDER.BOT_PARENT_ID IS NULL
130 AND BOT_ORDER.BOT_ORDER_LOADING_COMPLETE = 1
131 ';
132 SET @sql = @sql + @departmentHandling + '
133 ';
134 SET @sql = @sql + @sharedWhere + '
135 ';
136 SET @sql += ' ORDER BY BOT_ORDER.BOT_ORDER_ACTIVE, BOT_ORDER.BOT_ORDER_DATE DESC;';
137 END
138
139 ELSE -- @freeText is not null
140 BEGIN
141
142 DECLARE @filteredFields as nvarchar(1500);
143 SET @filteredFields = '
144 BOT_ORDER_ID
145 ,#FilteredOrders.BOT_PARENT_ID
146 ,#FilteredOrders.BOT_ORDER_ACTIVE
147 ,#FilteredOrders.BOT_CUSTOMER_ID
148 ,#FilteredOrders.GOT_STATUS_PROGID
149 ,#FilteredOrders.BOT_ORDER_DATE
150 ,#FilteredOrders.BOT_ORDER_TAG
151 ,#FilteredOrders.BOT_ORDER_EXTERNAL_ID
152 ,#FilteredOrders.GOT_TYPE_PROGID
153 ,#FilteredOrders.BOT_ADDRESS_ID
154 ,#FilteredOrders.BOT_ORDER_NOTE
155 ,#FilteredOrders.BOT_ORDER_START_DATE
156 ,#FilteredOrders.BOT_ORDER_END_DATE
157 ,#FilteredOrders.BOT_ORDER_CREATED_DATE
158 ,#FilteredOrders.BOT_ORDER_LASTUPDATED_DATE
159 ,#FilteredOrders.LASTUPDATEBY_USER_FIRSTNAME
160 ,#FilteredOrders.LASTUPDATEBY_USER_LASTNAME
161 ,#FilteredOrders.GOT_DEPARTMENT_ID
162 ,#FilteredOrders.GOT_DEPARTMENT_NAME
163 ,#FilteredOrders.BOT_ORDER_PROPERTYID
164 ,#FilteredOrders.BOT_ORDER_DESCRIPTION
165 ,#FilteredOrders.BOT_ORDER_CUSTOMER_REF
166 ,#FilteredOrders.BOT_ORDER_OUR_REF
167 ,#FilteredOrders.BOT_ORDER_COMMENT
168 ';
169 DECLARE @allOrdersFields as nvarchar(1500);
170 SET @allOrdersFields = '
171 BOT_ORDER_ID
172 ,BOT_PARENT_ID
173 ,BOT_ORDER_ACTIVE
174 ,BOT_CUSTOMER_FIRSTNAME
175 ,BOT_CUSTOMER_SURNAME
176 ,GOT_STATUS_PROGID
177 ,BOT_ORDER_DATE
178 ,BOT_ORDER_TAG
179 ,BOT_ORDER_NOTE
180 ,BOT_ORDER_EXTERNAL_ID
181 ,BOT_ORDER_START_DATE
182 ,BOT_ORDER_END_DATE
183 ,BOT_ORDER_CREATED_DATE
184 ,BOT_ORDER_LASTUPDATED_DATE
185 ,LASTUPDATEBY_USER_FIRSTNAME
186 ,LASTUPDATEBY_USER_LASTNAME
187 ,GOT_TYPE_PROGID
188 ,BOT_ADDRESS_STREET
189 ,BOT_ORDER_NOTE
190 ,GOT_DEPARTMENT_ID
191 ,GOT_DEPARTMENT_NAME
192 ,BOT_ORDER_PROPERTYID
193 ,BOT_ORDER_DESCRIPTION
194 ,BOT_ORDER_CUSTOMER_REF
195 ,BOT_ORDER_OUR_REF
196 ,BOT_ORDER_COMMENT
197 ';
198
199 Create table #FilteredOrders
200 (
201 BOT_ORDER_ID int
202 ,BOT_PARENT_ID int
203 ,BOT_ORDER_ACTIVE tinyint
204 ,BOT_CUSTOMER_ID int
205 ,GOT_STATUS_PROGID nvarchar(50)
206 ,BOT_ORDER_DATE datetime
207 ,BOT_ORDER_TAG nvarchar(32)
208 ,BOT_ORDER_EXTERNAL_ID nvarchar(100)
209 ,GOT_TYPE_PROGID nvarchar(50)
210 ,BOT_ADDRESS_ID int
211 ,BOT_ORDER_NOTE nvarchar(250)
212 ,BOT_ORDER_START_DATE datetime
213 ,BOT_ORDER_END_DATE datetime
214 ,BOT_ORDER_CREATED_DATE datetime
215 ,BOT_ORDER_LASTUPDATED_DATE datetime
216 ,LASTUPDATEBY_USER_FIRSTNAME nvarchar(255)
217 ,LASTUPDATEBY_USER_LASTNAME nvarchar(255)
218 ,GOT_DEPARTMENT_ID int
219 ,GOT_DEPARTMENT_NAME nvarchar(250)
220 ,BOT_ORDER_PROPERTYID int
221 ,BOT_ORDER_DESCRIPTION nvarchar(MAX)
222 ,BOT_ORDER_CUSTOMER_REF nvarchar(200)
223 ,BOT_ORDER_OUR_REF nvarchar(200)
224 ,BOT_ORDER_COMMENT nvarchar(max)
225 )
226 SET @sql = ' SELECT
227 ' + @ordersFields + '
228 ';
229 SET @sql += ' FROM BOT_ORDER
230 ' + @joinsSql + '
231 WHERE BOT_ORDER.BOT_ORDER_LOADING_COMPLETE = 1
232 ';
233 SET @sql += @departmentHandling + '
234 ';
235 SET @sql += @sharedWhere + '
236 ';
237
238 SET @sql += ' AND (BOT_ORDER.BOT_ORDER_ID like ''' + @freeText + '%''
239 OR BOT_CUSTOMER.BOT_CUSTOMER_FIRSTNAME like ''' + @freeText + '%''
240 OR BOT_CUSTOMER.BOT_CUSTOMER_SURNAME like ''' + @freeText + '%''
241 OR (BOT_CUSTOMER.BOT_CUSTOMER_FIRSTNAME + '' '' + BOT_CUSTOMER.BOT_CUSTOMER_SURNAME) like ''' + @freeText + '%''
242 OR (BOT_CUSTOMER.BOT_CUSTOMER_SURNAME + '' '' + BOT_CUSTOMER.BOT_CUSTOMER_FIRSTNAME) like ''' + @freeText + '%''
243 OR GOT_STATUS.GOT_STATUS_NAME like ''' + @freeText + '%''
244 OR BOT_ORDER.BOT_ORDER_NOTE like ''' + @freeText + '%''
245 OR BOT_ORDER.BOT_ORDER_TAG like ''' + @freeText + '%''
246 OR BOT_ADDRESS.BOT_ADDRESS_STREET like ''' + @freeText + '%''
247 OR GOT_DEPARTMENT.GOT_DEPARTMENT_NAME like ''' + @freeText + '%''
248 OR BOT_ORDER.BOT_ORDER_EXTERNAL_ID like ''' + @freeText + '%''' +
249 ');';
250
251 INSERT INTO #FilteredOrders Execute (@sql);
252
253 -- reset the sql;
254 SET @sql = 'SELECT *
255 INTO #AllMainOrders
256 FROM #FilteredOrders
257 WHERE BOT_PARENT_ID IS NULL
258 ';
259 SET @sql += '
260 ;WITH OrderTreeFromFilteredSubOrders
261 AS
262 (
263 SELECT '
264 + @filteredFields + '
265 FROM #FilteredOrders
266 WHERE BOT_PARENT_ID IS NOT NULL
267 ';
268 SET @sql += '
269 UNION ALL
270 ';
271 SET @sql += ' SELECT
272 ' +@ordersFields + '
273 ';
274 SET @sql += ' FROM BOT_ORDER
275 INNER JOIN GOT_STATUS ON BOT_ORDER.GOT_STATUS_ID = GOT_STATUS.GOT_STATUS_ID
276 INNER JOIN GOT_TYPE ON BOT_ORDER.GOT_TYPE_ID = GOT_TYPE.GOT_TYPE_ID
277 INNER JOIN OrderTreeFromFilteredSubOrders on BOT_ORDER.BOT_ORDER_ID = OrderTreeFromFilteredSubOrders.BOT_PARENT_ID
278 INNER JOIN GOT_USER AS LASTUPDATED_BY_USER ON LASTUPDATED_BY_USER.GOT_USER_ID = BOT_ORDER.BOT_ORDER_LASTUPDATED_BY_GOT_USER_ID
279 INNER JOIN GOT_USER AS CREATED_BY_USER ON CREATED_BY_USER.GOT_USER_ID = BOT_ORDER.BOT_ORDER_CREATED_BY_GOT_USER_ID
280 INNER JOIN GOT_ORGANIZATION ON GOT_ORGANIZATION.GOT_ORGANIZATION_ID = BOT_ORDER.GOT_ORGANIZATION_ID
281 INNER JOIN GOT_DEPARTMENT ON GOT_DEPARTMENT.GOT_DEPARTMENT_ID = CREATED_BY_USER.GOT_DEPARTMENT_ID
282 INNER JOIN GOT_DEPARTMENT AS LOGGED_USER_DEPARTMENT ON LOGGED_USER_DEPARTMENT.GOT_DEPARTMENT_ID = ' + CAST(ISNULL(@loggedUserDepartmentId, 0) as nvarchar(50)) + '
283 WHERE BOT_ORDER.BOT_ORDER_LOADING_COMPLETE = 1 ';
284
285 IF (@departmentHandling <> '')
286 BEGIN
287 SET @sql = @sql + @departmentHandling --Handle department logic
288 END
289 SET @sql += ')
290 ';
291
292 SET @sql += 'INSERT INTO #AllMainOrders
293 SELECT *
294 FROM OrderTreeFromFilteredSubOrders
295 WHERE OrderTreeFromFilteredSubOrders.BOT_PARENT_ID IS NULL;
296 ';
297 SET @sql += 'SELECT TOP 1000
298 ' + @allOrdersFields + '
299 ';
300 SET @sql += ' FROM #AllMainOrders
301 LEFT JOIN BOT_CUSTOMER ON #AllMainOrders.BOT_CUSTOMER_ID = BOT_CUSTOMER.BOT_CUSTOMER_ID
302 LEFT JOIN BOT_ADDRESS ON #AllMainOrders.BOT_ADDRESS_ID = BOT_ADDRESS.BOT_ADDRESS_ID';
303 SET @sql += ' GROUP BY
304 ' + @allOrdersFields + '
305 ';
306 SET @sql += ' ORDER BY #AllMainOrders.BOT_ORDER_ACTIVE, #AllMainOrders.BOT_ORDER_DATE DESC;'
307 SET @sql += 'DROP TABLE #AllMainOrders;
308 DROP TABLE #FilteredOrders;'
309 END
310
311 exec sp_ExecuteSQl @sql
312END
313
314GO