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