· 8 years ago · May 11, 2018, 04:06 AM
1USE [HSEStore_New]
2GO
3
4/****** Object: StoredProcedure [Orders].[Order_Search] Script Date: 11/05/2018 11:14:43 AM ******/
5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
11CREATE PROCEDURE [Orders].[Order_Search]
12 @orderId int = null,
13 @deliveryType varchar(50) = null,
14 @orderStatus varchar(50) = null,
15 @paymentStatus varchar(50) = null,
16 @fraudStatus varchar(50) = null,
17 @orderPlacedByFirstName varchar(32) = null,
18 @orderPlacedByLastName varchar(32) = null,
19 @createdDateFrom datetime = null,
20 @createdDateTo datetime = null,
21 @latestModifiedDateFrom datetime = null,
22 @latestModifiedDateTo datetime = null,
23 @recipientsFirstName varchar(32) = null,
24 @recipientsLastName varchar(32) = null,
25 @email varchar(64) = null,
26 @deliveryPhone varchar(16) = null,
27 @paymentType varchar(25) = null,
28 @customerFirstName varchar(32) = null,
29 @customerLastName varchar(32) = null,
30 @consignmentNumber varchar(255) = null,
31 @orderLineStatus varchar(50) = null,
32 @stockCode varchar(128) = null,
33 @addressAmas varchar(255) = null,
34 @typeOfAddress varchar(12) = null,
35
36 @cardNumber varchar(20) = null, --NOT FOUND IN TABLE
37
38 @customAttributes dbo.CustomAttributes READONLY, --INCLUDED IN QUERY BUILDER - EBAY ORDER NUMBER ebay order number
39 @customAttributeEntity varchar(32) = null,
40
41 @isPresale BIT = null,
42 @isReturn BIT = null,
43 @isReissue BIT = null,
44
45 @createdBy int = null,
46 @modifiedBy int = null,
47
48 @pageNumber INT = 1,
49 @pageSize INT = 10,
50 @orderBy NVARCHAR(256) = null,
51
52 @includePageHeaders BIT = 0
53AS
54 DECLARE @orderByMultiplier INT
55 EXEC [dbo].[Tools_ParseOrderBy] @orderBy, @orderBy OUT, @orderByMultiplier OUT
56
57 --CLEAN UP PHONE NUMBER
58 SELECT @deliveryPhone = REPLACE(@deliveryPhone, ' ', '')
59
60 --CREATE A TABLE TO STORE THE RESULTS
61 CREATE TABLE #MatchingOrders (
62 [OrderId] INT PRIMARY KEY
63 )
64
65 DECLARE @Sql NVARCHAR(MAX) = ''
66 DECLARE @Sep NVARCHAR(5) = ''
67 DECLARE @NextUnion NVARCHAR(11) = ''
68
69 --BUILD UP THE ORDER TABLE QUERY
70 DECLARE @OrderWhereClause NVARCHAR(MAX) = ''
71 SELECT @Sep = ''
72 IF @orderId IS NOT NULL AND EXISTS (SELECT 1 FROM ORDERS.[Order] WHERE ID=@orderId)
73 SELECT @OrderWhereClause = @OrderWhereClause + @Sep + 'ID LIKE @orderId', @Sep = ' AND '
74 IF @paymentStatus IS NOT NULL
75 SELECT @OrderWhereClause = @OrderWhereClause + @Sep + 'PaymentSettlementStatus LIKE @paymentStatus', @Sep = ' AND '
76 IF @fraudStatus IS NOT NULL
77 SELECT @OrderWhereClause = @OrderWhereClause + @Sep + 'FraudStatus LIKE @fraudStatus', @Sep = ' AND '
78 IF @createdDateFrom IS NOT NULL
79 SELECT @OrderWhereClause = @OrderWhereClause + @Sep + 'cast(Date as Date) >= @createdDateFrom', @Sep = ' AND '
80 IF @createdDateTo IS NOT NULL
81 SELECT @OrderWhereClause = @OrderWhereClause + @Sep + 'cast(Date as Date) <= @createdDateTo', @Sep = ' AND '
82 IF @latestModifiedDateFrom IS NOT NULL
83 SELECT @OrderWhereClause = @OrderWhereClause + @Sep + 'cast(LastModified as Date) >= @latestModifiedDateFrom', @Sep = ' AND '
84 IF @latestModifiedDateTo IS NOT NULL
85 SELECT @OrderWhereClause = @OrderWhereClause + @Sep + 'cast(LastModified as Date) <= @latestModifiedDateTo', @Sep = ' AND '
86 IF @orderPlacedByFirstName IS NOT NULL
87 SELECT @OrderWhereClause = @OrderWhereClause + @Sep + 'FirstName LIKE @orderPlacedByFirstName', @Sep = ' AND '
88 IF @orderPlacedByLastName IS NOT NULL
89 SELECT @OrderWhereClause = @OrderWhereClause + @Sep + 'LastName LIKE @orderPlacedByLastName', @Sep = ' AND '
90 IF @recipientsFirstName IS NOT NULL
91 SELECT @OrderWhereClause = @OrderWhereClause + @Sep + 'RecipientFirstName LIKE @recipientsFirstName', @Sep = ' AND '
92 IF @recipientsLastName IS NOT NULL
93 SELECT @OrderWhereClause = @OrderWhereClause + @Sep + 'RecipientLastName LIKE @recipientsLastName', @Sep = ' AND '
94 IF @deliveryPhone IS NOT NULL
95 SELECT @OrderWhereClause = @OrderWhereClause + @Sep + 'DeliveryPhone LIKE @deliveryPhone', @Sep = ' AND '
96 IF @email IS NOT NULL
97 SELECT @OrderWhereClause = @OrderWhereClause + @Sep + 'Email LIKE @email', @Sep = ' AND '
98 IF @typeOfAddress IS NOT NULL AND @addressAmas IS NOT NULL
99 BEGIN
100 IF @typeOfAddress LIKE '%billing%'
101 SELECT @OrderWhereClause = @OrderWhereClause + @Sep + 'BillStreet1 LIKE @addressAmas', @Sep = ' AND '
102 ELSE IF @typeOfAddress LIKE '%delivery%'
103 SELECT @OrderWhereClause = @OrderWhereClause + @Sep + 'DeliveryStreet1 LIKE @addressAmas', @Sep = ' AND '
104 END
105 IF (LEN(@OrderWhereClause) > 0)
106 SELECT @Sql = @Sql + @NextUnion + 'SELECT [ID] FROM [Orders].[Order] WHERE ' + @OrderWhereClause, @NextUnion = ' INTERSECT '
107
108 --Shopper search
109 DECLARE @ShopperWhereClause NVARCHAR(MAX) = ''
110 SELECT @Sep = ''
111 IF @customerFirstName IS NOT NULL
112 SELECT @ShopperWhereClause = @ShopperWhereClause + @Sep + 'first_name LIKE @customerFirstName', @Sep = ' AND '
113 IF @customerLastName IS NOT NULL
114 SELECT @ShopperWhereClause = @ShopperWhereClause + @Sep + 'last_name LIKE @customerLastName', @Sep = ' AND '
115 IF (LEN(@ShopperWhereClause) > 0)
116 SELECT @Sql = @Sql + @NextUnion + 'SELECT o.[ID] FROM [Orders].[Order] o INNER JOIN [shopper] s ON s.[shopper_id] = o.[ShopperID] WHERE ' + @ShopperWhereClause, @NextUnion = ' INTERSECT '
117
118 IF @orderStatus IS NOT NULL
119 SELECT @Sql = @Sql + @NextUnion + 'SELECT o.ID FROM [Orders].[Order] as o Left Join [OrderManagement].CompletedOrder as co on o.ID = co.OrderId WHERE ISNULL(co.Status,o.status) like @orderStatus', @Sep = ' And ', @NextUnion = ' INTERSECT '
120 --Completed order search
121 DECLARE @CompletedOrderWhereClause NVARCHAR(MAX) = ''
122 SELECT @Sep = ''
123 IF @orderId IS NOT NULL AND EXISTS (SELECT 1 FROM [OrderManagement].[CompletedOrder] WHERE OrderId=@orderId)
124 SELECT @CompletedOrderWhereClause = @CompletedOrderWhereClause + @Sep + 'OrderId = @orderId', @Sep = ' AND '
125 IF(LEN(@CompletedOrderWhereClause) > 0)
126 SELECT @Sql = @Sql + @NextUnion + 'SELECT [OrderId] FROM [OrderManagement].[CompletedOrder] WHERE ' + @CompletedOrderWhereClause, @NextUnion = ' INTERSECT '
127 --CompletedOrderDeliveryInfo search
128 DECLARE @CompletedOrderDeliveryInfoWhereClause NVARCHAR(MAX) = ''
129 SELECT @Sep = ''
130 IF @consignmentNumber IS NOT NULL
131 SELECT @CompletedOrderDeliveryInfoWhereClause = @CompletedOrderDeliveryInfoWhereClause + @Sep + 'ConsignmentNo LIKE @consignmentNumber', @Sep = ' AND '
132 IF (LEN(@CompletedOrderDeliveryInfoWhereClause) > 0)
133 SELECT @Sql = @Sql + @NextUnion + 'SELECT [OrderId] FROM [OrderManagement].[CompletedOrderDeliveryInfo] WHERE ' + @CompletedOrderDeliveryInfoWhereClause, @NextUnion = ' INTERSECT '
134
135 --CompletedOrderLine search
136 DECLARE @CompletedOrderLineWhereClause NVARCHAR(MAX) = ''
137 SELECT @Sep = ''
138 IF @orderLineStatus IS NOT NULL
139 SELECT @CompletedOrderLineWhereClause = @CompletedOrderLineWhereClause + @Sep + '[Status] LIKE @orderLineStatus', @Sep = ' AND '
140 IF (LEN(@CompletedOrderLineWhereClause) > 0)
141 SELECT @Sql = @Sql + @NextUnion + 'SELECT [OrderId] FROM [OrderManagement].[CompletedOrderLine] WHERE ' + @CompletedOrderLineWhereClause, @NextUnion = ' INTERSECT '
142
143 --OrderProduct search
144 DECLARE @OrderProductWhereClause NVARCHAR(MAX) = ''
145 SELECT @Sep = ''
146 IF @stockCode IS NOT NULL
147 SELECT @OrderProductWhereClause = @OrderProductWhereClause + @Sep + 'Stockcode LIKE @stockCode', @Sep = ' AND '
148 IF (LEN(@OrderProductWhereClause) > 0)
149 SELECT @Sql = @Sql + @NextUnion + 'SELECT [OrderId] FROM [Orders].[OrderProduct] WHERE ' + @OrderProductWhereClause, @NextUnion = ' INTERSECT '
150 --OrderPayment search
151 DECLARE @OrderPaymentWhereClause NVARCHAR(MAX) = ''
152 SELECT @Sep = ''
153 IF @paymentType IS NOT NULL
154 SELECT @OrderPaymentWhereClause = @OrderPaymentWhereClause + @Sep + '[Type] LIKE @paymentType', @Sep = ' AND '
155 IF (LEN(@OrderPaymentWhereClause) > 0)
156 SELECT @Sql = @Sql + @NextUnion + 'SELECT [OrderId] FROM [Orders].[OrderPayment] WHERE ' + @OrderPaymentWhereClause, @NextUnion = ' INTERSECT '
157 DECLARE @OrderInfoWhereClause NVARCHAR(MAX) = ''
158 SELECT @Sep = ''
159 IF @deliveryType IS NOT NULL
160 SELECT @OrderInfoWhereClause = @OrderInfoWhereClause + @Sep + '[FulfilmentDeliveryMethod] LIKE @deliveryType', @Sep = ' AND '
161 IF (LEN(@OrderInfoWhereClause) > 0)
162 SELECT @Sql = @Sql + @NextUnion + 'SELECT [OrderId] FROM [Orders].[OrderInfo] WHERE ' + @OrderInfoWhereClause, @NextUnion = ' INTERSECT '
163
164 --OrderType search
165 DECLARE @SqlOrderType NVARCHAR(MAX) = ''
166 SELECT @Sep = ''
167 SELECT @NextUnion = ''
168
169 If @isPreSale IS NOT NULL AND @isPreSale = 1
170 SELECT @SqlOrderType = @SqlOrderType + @NextUnion + 'SELECT [OrderId] FROM [Orders].[OrderInfo] WHERE IsPresale = 1 ' , @NextUnion = ' INTERSECT '
171 --return order search
172 If @isReturn IS NOT NULL AND @isReturn = 1
173 SELECT @SqlOrderType = @SqlOrderType + @NextUnion + 'SELECT [OrderId] FROM [OrderManagement].[ReturnOrder] ', @NextUnion = ' INTERSECT '
174 --reissue order search
175 If @isReissue IS NOT NULL AND @isReissue = 1
176 SELECT @SqlOrderType = @SqlOrderType + @NextUnion + 'SELECT [OrderId] FROM [dbo].[ReIssue] ', @NextUnion = ' INTERSECT '
177
178 IF (LEN(@Sql) > 0 AND LEN(@SqlOrderType) = 0)
179 SELECT @Sql = 'INSERT INTO #MatchingOrders ' + @Sql + ' EXCEPT SELECT Null'
180
181 IF (LEN(@Sql) > 0 AND LEN(@SqlOrderType) > 0)
182 SELECT @Sql = 'INSERT INTO #MatchingOrders ' + @Sql + ' intersect (' + @SqlOrderType + ') EXCEPT SELECT Null'
183 IF (LEN(@Sql) = 0 AND LEN(@SqlOrderType) > 0)
184 SELECT @Sql = 'INSERT INTO #MatchingOrders ' + @SqlOrderType + ' EXCEPT SELECT Null'
185
186 EXEC sp_executeSQL @Sql,
187 N'
188 @orderId int,
189 @deliveryType varchar(50),
190 @orderStatus varchar(50),
191 @paymentStatus varchar(50),
192 @fraudStatus varchar(50),
193 @orderPlacedByFirstName varchar(32),
194 @orderPlacedByLastName varchar(32),
195 @createdDateFrom datetime,
196 @createdDateTo datetime,
197 @latestModifiedDateFrom datetime,
198 @latestModifiedDateTo datetime,
199 @recipientsFirstName varchar(32),
200 @recipientsLastName varchar(32),
201 @email varchar(64),
202 @deliveryPhone varchar(16),
203 @customerFirstName varchar(32),
204 @customerLastName varchar(32),
205 @consignmentNumber varchar(255),
206 @orderLineStatus varchar(50),
207 @stockCode varchar(128),
208 @addressAmas varchar(255),
209 @typeOfAddress varchar(12),
210 @isPresale bit,
211 @isReturn bit,
212 @isReissue bit,
213 @paymentType varchar(50)',
214 @orderId = @orderId,
215 @deliveryType = @deliveryType,
216 @orderStatus = @orderStatus,
217 @paymentStatus = @paymentStatus,
218 @fraudStatus = @fraudStatus,
219 @orderPlacedByFirstName = @orderPlacedByFirstName,
220 @orderPlacedByLastName = @orderPlacedByLastName,
221 @createdDateFrom = @createdDateFrom,
222 @createdDateTo = @createdDateTo,
223 @latestModifiedDateFrom = @latestModifiedDateFrom,
224 @latestModifiedDateTo = @latestModifiedDateTo,
225 @recipientsFirstName = @recipientsFirstName,
226 @recipientsLastName = @recipientsLastName,
227 @email = @email,
228 @deliveryPhone = @deliveryPhone,
229 @customerFirstName = @customerFirstName,
230 @customerLastName = @customerLastName,
231 @consignmentNumber = @consignmentNumber,
232 @orderLineStatus = @orderLineStatus,
233 @stockCode = @stockCode,
234 @addressAmas = @addressAmas,
235 @typeOfAddress = @typeOfAddress,
236 @isPresale = @isPresale,
237 @isReturn = @isReturn,
238 @isReissue = @isReissue,
239 @paymentType = @paymentType
240 CREATE TABLE #MatchingAttributeOrders (
241 [OrderID] INT
242 )
243 CREATE TABLE #FinalOrders (
244 [OrderID] INT PRIMARY KEY
245 )
246 --QUERY ENTITY ATTRIBUTE DICTIONARY AND CUSTOM ATTRIBUTE
247 INSERT INTO #MatchingAttributeOrders
248 SELECT EntityId as OrderID
249 FROM [dbo].[OEA_V_EntityAttributeDictionary] as EAD
250 INNER JOIN @customAttributes AS CA
251 ON EAD.AttributeName = CA.AttributeName
252 AND EAD.AttributeValue like Value
253 WHERE EntityType = @customAttributeEntity
254 declare @matchingOrdersCount int;
255 declare @matchingAttributeOrdersCount int;
256 select @matchingOrdersCount = count(*) from #MatchingOrders
257 select @matchingAttributeOrdersCount = count(*) from #MatchingAttributeOrders
258 -- Check condition 1 here
259 IF @matchingOrdersCount > 0 and @matchingAttributeOrdersCount > 0
260 INSERT INTO #FinalOrders select OrderID from #MatchingOrders INTERSECT Select OrderID from #MatchingAttributeOrders
261 ELSE IF @matchingOrdersCount > 0
262 INSERT INTO #FinalOrders select * from #MatchingOrders
263 ELSE IF @matchingAttributeOrdersCount > 0
264 INSERT INTO #FinalOrders select DISTINCT * from #MatchingAttributeOrders
265
266 DECLARE @totalRecordCount int,
267 @offSet int = 0
268 SELECT @totalRecordCount = COUNT([OrderId])
269 FROM #FinalOrders
270 IF @orderByMultiplier < 0
271 SELECT @offSet = @totalRecordCount + 1
272 SELECT [ShopperId], [FirstName], [LastName], [IsGuest], [OrderId], [RecipientOrderId], [RecipientId], [DeliveryAddress], [FulfillmentType], [OrderDate], [OrderStatus], [Channel]
273 FROM
274 (
275 (
276 SELECT
277 @offSet + @orderByMultiplier * ROW_NUMBER() OVER (
278 ORDER BY
279 CASE @orderBy --(INT)
280 WHEN 'OrderId' THEN o.[ID]
281 WHEN 'ShopperId' THEN s.[shopper_id]
282 END,
283 CASE @orderBy --(VARCHAR)
284 WHEN 'FirstName' THEN s.[first_name]
285 WHEN 'LastName' THEN s.[last_name]
286 WHEN 'DeliveryAddress' THEN o.[DeliveryStreet1]
287 WHEN 'DeliveryType' THEN oi.[FulfilmentDeliveryMethod]
288 END,
289 CASE @orderBy --(BIT)
290 WHEN 'IsGuest' THEN s.[IsGuest]
291 END
292 ) [ROW],
293 o.[ID] [OrderId],
294 '' [RecipientOrderId],
295 '' [RecipientId],
296 s.[shopper_id] [ShopperId],
297 s.[first_name] [FirstName],
298 s.[last_name] [LastName],
299 s.[IsGuest] [IsGuest],
300 o.[DeliveryStreet1] + ' ' + o.[DeliveryStreet2] [DeliveryAddress],
301 oi.[FulfilmentDeliveryMethod] [FulfillmentType],
302 o.[Date] [OrderDate],
303 ISNULL(co.[Status], o.[Status]) [OrderStatus],
304 ea.AttributeValue as 'Channel'
305 FROM #FinalOrders fo
306 INNER JOIN [Orders].[Order] o ON o.[ID] = fo.[OrderId]
307 INNER JOIN [Orders].[OrderInfo] oi ON oi.[OrderId] = o.[ID]
308 INNER JOIN [dbo].[Shopper] s ON o.[ShopperId] = s.[shopper_id]
309 LEFT JOIN [OrderManagement].[CompletedOrder] co ON o.ID = co.OrderID
310 Inner Join OEA_V_EntityAttributeDictionary ea on ea.EntityId = CAST(o.Id as varchar(20))
311 where ea.AttributeName = 'OrderSource'
312 )
313 UNION
314 (
315 SELECT
316 @offSet + @orderByMultiplier * ROW_NUMBER() OVER (
317 ORDER BY
318 CASE @orderBy --(INT)
319 WHEN 'OrderId' THEN co.[OrderId]
320 WHEN 'ShopperId' THEN s.[shopper_id]
321 END,
322 CASE @orderBy --(VARCHAR)
323 WHEN 'FirstName' THEN s.[first_name]
324 WHEN 'LastName' THEN s.[last_name]
325
326 END,
327 CASE @orderBy --(BIT)
328 WHEN 'IsGuest' THEN s.[IsGuest]
329 END
330 ) [ROW],
331 orr.[OrderId] [OrderId],
332 co.[OrderId] [RecipientOrderId],
333 co.[RecipientId] [RecipientId],
334 s.[shopper_id] [ShopperId],
335 s.[first_name] [FirstName],
336 s.[last_name] [LastName],
337 s.[IsGuest] [IsGuest],
338 orr.[Street1] + ' ' + orr.[Street1] [DeliveryAddress],
339 oi.[FulfilmentDeliveryMethod] [FulfillmentType],
340 o.[Date] [OrderDate],
341 ISNULL(co.[Status], o.[Status]) [OrderStatus],
342 ea.AttributeValue as 'Channel'
343 FROM #FinalOrders fo
344 INNER JOIN [OrderManagement].[completedOrder] co ON co.[OrderId] = fo.[OrderId]
345 INNER JOIN [Orders].[OrderRecipient] orr ON orr.[Id] = co.[RecipientId]
346 INNER JOIN [Orders].[Order] o ON o.[Id] = orr.[OrderId]
347 INNER JOIN [dbo].[Shopper] s ON co.[ShopperId] = s.[shopper_id]
348 INNER JOIN [Orders].[OrderInfo] oi ON oi.[OrderId] = o.[ID]
349 Inner Join OEA_V_EntityAttributeDictionary ea on ea.EntityId = CAST(o.Id as varchar(20))
350 where ea.AttributeName = 'OrderSource'
351 )
352 ) s
353 WHERE
354 (([Row] - 1) / @pageSize) + 1 = @pageNumber
355 ORDER BY [Row]
356 SELECT @totalRecordCount [TotalRecordCount]
357GRANT EXECUTE ON [Orders].[Order_Search] TO [HSWebStore]
358
359GO