· 6 years ago · Apr 08, 2019, 06:18 PM
1/* ==Параметры Ñценариев==
2
3 ВерÑÐ¸Ñ Ð¸Ñходного Ñервера : SQL Server 2017 (14.0.2002)
4 ВыпуÑк иÑходного Ñдра СУБД : ВыпуÑк Microsoft SQL Server Enterprise Edition
5 Тип иÑходного Ñдра СУБД : Изолированный SQL Server
6
7 ВерÑÐ¸Ñ Ñ†ÐµÐ»ÐµÐ²Ð¾Ð³Ð¾ Ñервера : SQL Server 2017
8 ВыпуÑк целевого Ñдра СУБД : ВыпуÑк Microsoft SQL Server Enterprise Edition
9 Тип целевого Ñдра СУБД : Изолированный SQL Server
10*/
11
12USE [confettidb]
13GO
14
15/****** Object: StoredProcedure [dbo].[ProductLoadAllPaged] Script Date: 08.04.2019 21:14:21 ******/
16SET ANSI_NULLS ON
17GO
18
19SET QUOTED_IDENTIFIER ON
20GO
21
22
23-- =============================================
24-- Author: <Author,,Name>
25-- Create date: <Create Date,,>
26-- Description: <Description,,>
27-- =============================================
28CREATE PROCEDURE [dbo].[ProductLoadAllPaged]
29 @CategoryIds nvarchar(MAX) = null, --a list of category IDs (comma-separated list). e.g. 1,2,3
30 @WarehouseId int = 0,
31 @MarkedAsNewOnly bit = 0, --0 - load all products , 1 - "marked as new" only
32 @FeaturedProducts bit = null, --0 featured only , 1 not featured only, null - load all products
33 @PriceMin decimal(18, 4) = null,
34 @PriceMax decimal(18, 4) = null,
35 @Keywords nvarchar(4000) = null,
36 @SearchDescriptions bit = 0, --a value indicating whether to search by a specified "keyword" in product descriptions
37 @SearchSku bit = 0, --a value indicating whether to search by a specified "keyword" in product SKU
38 @FilteredSpecs nvarchar(MAX) = null, --filter by specification attribute options (comma-separated list of IDs). e.g. 14,15,16
39 @OrderBy int = 0, --0 - position, 5 - Name: A to Z, 6 - Name: Z to A, 10 - Price: Low to High, 11 - Price: High to Low, 15 - creation date
40 @PageIndex int = 0,
41 @PageSize int = 2147483644,
42 @ShowHidden bit = 0,
43 @OverridePublished bit = null, --null - process "Published" property according to "showHidden" parameter, true - load only "Published" products, false - load only "Unpublished" products
44 @LoadFilterableSpecificationAttributeOptionIds bit = 0, --a value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)
45 @FilterableSpecificationAttributeOptionIds nvarchar(MAX) = null OUTPUT, --the specification attribute option identifiers applied to loaded products (all pages). returned as a comma separated list of identifiers
46 @LoadMinMaxPrices bit = 0, --a value indicating whether we should load min and max prices (all pages)
47 @MinPrice decimal(18, 4) = null OUTPUT, --min price (all pages)
48 @MaxPrice decimal(18, 4) = null OUTPUT, --max price (all pages)
49 @TotalRecords int = null OUTPUT
50AS
51BEGIN
52 -- SET NOCOUNT ON added to prevent extra result sets from
53 -- interfering with SELECT statements.
54 SET NOCOUNT ON;
55
56 /* Products that filtered by keywords */
57 CREATE TABLE #KeywordProducts
58 (
59 [ProductId] int NOT NULL
60 )
61
62 DECLARE
63 @SearchKeywords bit,
64 @OriginalKeywords nvarchar(4000),
65 @sql nvarchar(max),
66 @sql_orderby nvarchar(max)
67
68 --filter by keywords
69 SET @Keywords = isnull(@Keywords, '')
70 SET @Keywords = rtrim(ltrim(@Keywords))
71 SET @OriginalKeywords = @Keywords
72
73 IF ISNULL(@Keywords, '') != ''
74 BEGIN
75
76 SET @SearchKeywords = 1
77
78 --usual search by PATINDEX
79 SET @Keywords = '%' + @Keywords + '%'
80
81 --product name
82 SET @sql = '
83 INSERT INTO #KeywordProducts (
84 [ProductId]
85 )
86 SELECT
87 p.Id
88 FROM
89 Product p with (NOLOCK)
90 WHERE '
91 SET @sql = @sql + 'PATINDEX(@Keywords, p.[Name]) > 0 '
92
93 IF @SearchDescriptions = 1
94 BEGIN
95
96 --product short description
97 SET @sql = @sql + '
98 UNION
99 SELECT
100 p.Id
101 FROM
102 Product p with (NOLOCK)
103 WHERE '
104 SET @sql = @sql + 'PATINDEX(@Keywords, p.[ShortDescription]) > 0 '
105
106 --product full description
107 SET @sql = @sql + '
108 UNION
109 SELECT
110 p.Id
111 FROM
112 Product p with (NOLOCK)
113 WHERE '
114 SET @sql = @sql + 'PATINDEX(@Keywords, p.[FullDescription]) > 0 '
115
116 END
117
118 --SKU (exact match)
119 IF @SearchSku = 1
120 BEGIN
121
122 SET @sql = @sql + '
123 UNION
124 SELECT
125 p.Id
126 FROM
127 Product p with (NOLOCK)
128 WHERE
129 p.[Sku] = @OriginalKeywords '
130
131 END
132
133 --PRINT (@sql)
134 EXEC sp_executesql @sql, N'@Keywords nvarchar(4000), @OriginalKeywords nvarchar(4000)', @Keywords, @OriginalKeywords
135
136 END
137 ELSE
138 BEGIN
139 SET @SearchKeywords = 0
140 END
141
142 --filter by category IDs
143 SET @CategoryIds = isnull(@CategoryIds, '')
144
145 CREATE TABLE #FilteredCategoryIds
146 (
147 CategoryId int not null
148 )
149
150 INSERT INTO #FilteredCategoryIds (CategoryId)
151 SELECT CAST(data as int) FROM [confetti_splitstring_to_table](@CategoryIds, ',')
152
153 DECLARE @CategoryIdsCount int
154 SET @CategoryIdsCount = (SELECT COUNT(1) FROM #FilteredCategoryIds)
155
156 --paging
157 DECLARE @PageLowerBound int
158 DECLARE @PageUpperBound int
159 DECLARE @RowsToReturn int
160 SET @RowsToReturn = @PageSize * (@PageIndex + 1)
161 SET @PageLowerBound = @PageSize * @PageIndex
162 SET @PageUpperBound = @PageLowerBound + @PageSize + 1
163
164 CREATE TABLE #PositionTmp
165 (
166 [Id] int IDENTITY (1, 1) NOT NULL,
167 [ProductId] int NOT NULL
168 )
169
170 SET @sql = '
171 SELECT
172 p.Id
173 FROM
174 Product p with (NOLOCK)'
175
176 --searching by category IDs
177 IF @CategoryIdsCount > 0
178 BEGIN
179 SET @sql = @sql + '
180 INNER JOIN
181 Product_Category_Mapping pcm with (NOLOCK)
182 ON
183 p.Id = pcm.ProductId'
184 END
185
186 --searching by keywords
187 IF @SearchKeywords = 1
188 BEGIN
189 SET @sql = @sql + '
190 JOIN
191 #KeywordProducts kp
192 ON
193 p.Id = kp.ProductId'
194 END
195
196 SET @sql = @sql + '
197 WHERE
198 p.Deleted = 0'
199
200 --filter by category
201 IF @CategoryIdsCount > 0
202 BEGIN
203
204 SET @sql = @sql + '
205 AND pcm.CategoryId IN ('
206
207 SET @sql = @sql + + CAST(@CategoryIds AS nvarchar(max))
208
209 SET @sql = @sql + ')'
210
211 IF @FeaturedProducts IS NOT NULL
212 BEGIN
213
214 SET @sql = @sql + '
215 AND pcm.IsFeaturedProduct = ' + CAST(@FeaturedProducts AS nvarchar(max))
216
217 END
218 END
219
220 --filter by warehouse
221 IF @WarehouseId > 0
222 BEGIN
223
224 --we should also ensure that 'ManageInventoryMethodId' is set to 'ManageStock' (1)
225 --but we skip it in order to prevent hard-coded values (e.g. 1) and for better performance
226 SET @sql = @sql + '
227 AND
228 (
229 (p.UseMultipleWarehouses = 0
230 AND
231 p.WarehouseId = ' + CAST(@WarehouseId AS nvarchar(max)) + ')
232 OR
233 (p.UseMultipleWarehouses > 0
234 AND
235 EXISTS (
236 SELECT
237 1
238 FROM
239 ProductWarehouseInventory [pwi]
240 WHERE
241 [pwi].WarehouseId = ' + CAST(@WarehouseId AS nvarchar(max)) + ' AND [pwi].ProductId = p.Id
242 )
243 )
244 )'
245
246 END
247
248 --filter by "marked as new"
249 IF @MarkedAsNewOnly = 1
250 BEGIN
251
252 SET @sql = @sql + '
253 AND
254 p.MarkAsNew = 1
255 AND
256 (getutcdate()
257 BETWEEN
258 ISNULL(p.MarkAsNewStartDateTimeUtc, ''1/1/1900'')
259 and
260 ISNULL(p.MarkAsNewEndDateTimeUtc, ''1/1/2999'')
261 )'
262
263 END
264
265 --"Published" property
266 IF (@OverridePublished is null)
267 BEGIN
268
269 --process according to "showHidden"
270 IF @ShowHidden = 0
271 BEGIN
272
273 SET @sql = @sql + '
274 AND p.Published = 1'
275
276 END
277 END
278 ELSE IF (@OverridePublished = 1)
279 BEGIN
280
281 --published only
282 SET @sql = @sql + '
283 AND p.Published = 1'
284
285 END
286 ELSE IF (@OverridePublished = 0)
287 BEGIN
288
289 --unpublished only
290 SET @sql = @sql + '
291 AND p.Published = 0'
292
293 END
294
295 --show hidden (AvailableDateTimeUtc Start/End)
296 IF @ShowHidden = 0
297 BEGIN
298
299 SET @sql = @sql + '
300 AND (
301 getutcdate()
302 BETWEEN
303 ISNULL(p.AvailableStartDateTimeUtc, ''1/1/1900'')
304 and
305 ISNULL(p.AvailableEndDateTimeUtc, ''1/1/2999'')
306 )'
307
308 END
309
310 --min price
311 IF @PriceMin is not null
312 BEGIN
313
314 SET @sql = @sql + '
315 AND (p.Price >= ' + CAST(@PriceMin AS nvarchar(max)) + ')'
316
317 END
318
319 --max price
320 IF @PriceMax is not null
321 BEGIN
322
323 SET @sql = @sql + '
324 AND (p.Price <= ' + CAST(@PriceMax AS nvarchar(max)) + ')'
325
326 END
327
328 --get specs for filtering
329 CREATE TABLE #FilteredSpecs
330 (
331 SpecificationAttributeOptionId int not null
332 )
333
334 INSERT INTO
335 #FilteredSpecs (
336 SpecificationAttributeOptionId
337 )
338 SELECT
339 CAST(data as int)
340 FROM
341 [confetti_splitstring_to_table](@FilteredSpecs, ',')
342
343 CREATE TABLE #FilteredSpecsWithAttributes
344 (
345 SpecificationAttributeId int not null,
346 SpecificationAttributeOptionId int not null
347 )
348
349 INSERT INTO
350 #FilteredSpecsWithAttributes (
351 SpecificationAttributeId,
352 SpecificationAttributeOptionId
353 )
354 SELECT
355 sao.SpecificationAttributeId,
356 fs.SpecificationAttributeOptionId
357 FROM
358 #FilteredSpecs fs
359 INNER JOIN
360 SpecificationAttributeOption sao
361 ON
362 sao.Id = fs.SpecificationAttributeOptionId
363 ORDER BY
364 sao.SpecificationAttributeId
365
366 DECLARE @SpecAttributesCount int
367 SET @SpecAttributesCount = (SELECT COUNT(1) FROM #FilteredSpecsWithAttributes)
368
369 --prepare filterable specification attribute option identifiers (if requested)
370 IF @LoadFilterableSpecificationAttributeOptionIds = 1
371 BEGIN
372
373 CREATE TABLE #FilterableSpecs
374 (
375 [SpecificationAttributeOptionId] int NOT NULL,
376 [CountOfProducts] int NOT NULL
377 )
378
379 DECLARE @sql_filterableSpecs nvarchar(max)
380 SET @sql_filterableSpecs = '
381 INSERT INTO
382 #FilterableSpecs (
383 [SpecificationAttributeOptionId],
384 [CountOfProducts]
385 )
386 SELECT
387 [psam].SpecificationAttributeOptionId,
388 COUNT([psam].ProductId)
389 FROM
390 [Product_SpecificationAttribute_Mapping] [psam] WITH (NOLOCK)
391 WHERE
392 [psam].[AllowFiltering] = 1
393 AND
394 [psam].[ProductId] IN (' + @sql + ')'
395
396 --load available specs by filtered specs
397 IF @SpecAttributesCount > 0
398 BEGIN
399
400 --do it for each specified specification option
401 DECLARE @SpecificationAttributeOptionId int
402 DECLARE @SpecificationAttributeId int
403 DECLARE @LastSpecificationAttributeId int
404 SET @LastSpecificationAttributeId = 0
405
406 DECLARE
407 cur_SpecificationAttributeOption CURSOR FOR
408 SELECT
409 SpecificationAttributeId,
410 SpecificationAttributeOptionId
411 FROM
412 #FilteredSpecsWithAttributes
413
414 OPEN cur_SpecificationAttributeOption
415 FOREACH:
416
417 FETCH NEXT
418 FROM
419 cur_SpecificationAttributeOption
420 INTO
421 @SpecificationAttributeId,
422 @SpecificationAttributeOptionId
423
424 IF (@LastSpecificationAttributeId <> 0
425 AND
426 @SpecificationAttributeId <> @LastSpecificationAttributeId
427 OR
428 @@FETCH_STATUS <> 0)
429
430 SET @sql_filterableSpecs = @sql_filterableSpecs + '
431 AND
432 [psam].ProductId IN (
433 SELECT
434 [psam_in].ProductId
435 FROM
436 Product_SpecificationAttribute_Mapping AS [psam_in] WITH (NOLOCK)
437 WHERE
438 [psam_in].AllowFiltering = 1
439 AND
440 [psam_in].SpecificationAttributeOptionId IN (
441 SELECT
442 SpecificationAttributeOptionId
443 FROM
444 #FilteredSpecsWithAttributes
445 WHERE
446 SpecificationAttributeId = ' + CAST(@LastSpecificationAttributeId AS nvarchar(max)) +
447 ')
448 )
449 AND
450 [psam].SpecificationAttributeOptionId NOT IN (
451 SELECT
452 SpecificationAttributeOptionId
453 FROM
454 #FilteredSpecsWithAttributes
455 WHERE
456 SpecificationAttributeId = ' + CAST(@LastSpecificationAttributeId AS nvarchar(max)) +
457 ')'
458
459 SET @LastSpecificationAttributeId = @SpecificationAttributeId
460
461 IF @@FETCH_STATUS = 0 GOTO FOREACH
462
463 CLOSE cur_SpecificationAttributeOption
464 DEALLOCATE cur_SpecificationAttributeOption
465
466 END
467
468 --Group by SpecificationAttributeOptionId (eliminate duplicate)
469 SET @sql_filterableSpecs = @sql_filterableSpecs + '
470 GROUP BY [psam].SpecificationAttributeOptionId'
471
472 EXEC sp_executesql @sql_filterableSpecs
473
474 --build comma separated list of filterable identifiers
475 SELECT
476 @FilterableSpecificationAttributeOptionIds =
477 COALESCE(@FilterableSpecificationAttributeOptionIds + ',' , '')
478 +
479 CAST(SpecificationAttributeOptionId as nvarchar(4000))
480 +
481 '-'
482 +
483 CAST(CountOfProducts as nvarchar(4000))
484 FROM
485 #FilterableSpecs
486
487 DROP TABLE #FilterableSpecs
488
489 END
490
491 --prepare min/max prices (if requested)
492 IF @LoadMinMaxPrices = 1
493 BEGIN
494
495 CREATE TABLE #PriceRange
496 (
497 [MinPrice] decimal(18, 4) NOT NULL,
498 [MaxPrice] decimal(18, 4) NOT NULL
499 )
500
501 DECLARE @sql_minMaxPrices nvarchar(max)
502 SET @sql_minMaxPrices = '
503 INSERT INTO
504 #PriceRange (
505 [MinPrice],
506 [MaxPrice]
507 )
508 SELECT
509 min(p.Price),
510 max(p.Price)
511 FROM
512 Product p
513 WHERE
514 p.Id IN (' + @sql + ')'
515
516 --adjust prices by filtered specs
517 IF @SpecAttributesCount > 0
518 BEGIN
519
520 --do it for each specified specification option
521 SET @SpecificationAttributeOptionId = 0
522 SET @SpecificationAttributeId = 0
523 SET @LastSpecificationAttributeId = 0
524
525 DECLARE cur_SpecificationAttributeOption CURSOR FOR
526 SELECT
527 SpecificationAttributeId,
528 SpecificationAttributeOptionId
529 FROM
530 #FilteredSpecsWithAttributes
531
532 OPEN cur_SpecificationAttributeOption
533 FOREACH_1:
534
535 FETCH NEXT
536 FROM
537 cur_SpecificationAttributeOption
538 INTO
539 @SpecificationAttributeId,
540 @SpecificationAttributeOptionId
541
542 IF (@LastSpecificationAttributeId <> 0
543 AND
544 @SpecificationAttributeId <> @LastSpecificationAttributeId
545 OR
546 @@FETCH_STATUS <> 0)
547
548 SET @sql_minMaxPrices = @sql_minMaxPrices + '
549 AND
550 p.Id IN (
551 SELECT
552 psam.ProductId
553 FROM
554 [Product_SpecificationAttribute_Mapping] psam with (NOLOCK)
555 WHERE
556 psam.AllowFiltering = 1
557 AND
558 psam.SpecificationAttributeOptionId IN (
559 SELECT
560 SpecificationAttributeOptionId
561 FROM
562 #FilteredSpecsWithAttributes
563 WHERE
564 SpecificationAttributeId = ' + CAST(@LastSpecificationAttributeId AS nvarchar(max)) +
565 '))'
566
567 SET @LastSpecificationAttributeId = @SpecificationAttributeId
568
569 IF @@FETCH_STATUS = 0 GOTO FOREACH_1
570
571 CLOSE cur_SpecificationAttributeOption
572 DEALLOCATE cur_SpecificationAttributeOption
573
574 END
575
576 EXEC sp_executesql @sql_minMaxPrices
577
578 SELECT
579 @MinPrice = #PriceRange.MinPrice,
580 @MaxPrice = #PriceRange.MaxPrice
581 FROM
582 #PriceRange
583
584 DROP TABLE #PriceRange
585
586 END
587
588
589 --filter by specification attribution options
590 IF @SpecAttributesCount > 0
591 BEGIN
592
593 --do it for each specified specification option
594 SET @SpecificationAttributeOptionId = 0
595 SET @SpecificationAttributeId = 0
596 SET @LastSpecificationAttributeId = 0
597
598 DECLARE cur_SpecificationAttributeOption CURSOR FOR
599 SELECT
600 SpecificationAttributeId,
601 SpecificationAttributeOptionId
602 FROM
603 #FilteredSpecsWithAttributes
604
605 OPEN cur_SpecificationAttributeOption
606 FOREACH_2:
607 FETCH NEXT FROM cur_SpecificationAttributeOption INTO @SpecificationAttributeId, @SpecificationAttributeOptionId
608 IF (@LastSpecificationAttributeId <> 0 AND @SpecificationAttributeId <> @LastSpecificationAttributeId OR @@FETCH_STATUS <> 0)
609 SET @sql = @sql + '
610 AND
611 p.Id IN (
612 SELECT
613 psam.ProductId
614 FROM
615 [Product_SpecificationAttribute_Mapping] psam with (NOLOCK)
616 WHERE
617 psam.AllowFiltering = 1
618 AND
619 psam.SpecificationAttributeOptionId IN (
620 SELECT
621 SpecificationAttributeOptionId
622 FROM
623 #FilteredSpecsWithAttributes
624 WHERE
625 SpecificationAttributeId = ' + CAST(@LastSpecificationAttributeId AS nvarchar(max)) +
626 '))'
627
628 SET @LastSpecificationAttributeId = @SpecificationAttributeId
629
630 IF @@FETCH_STATUS = 0 GOTO FOREACH_2
631
632 CLOSE cur_SpecificationAttributeOption
633 DEALLOCATE cur_SpecificationAttributeOption
634
635 END
636
637 --sorting
638 SET @sql_orderby = ''
639 IF @OrderBy = 5 /* Name: A to Z */
640 SET @sql_orderby = ' p.[Name] ASC'
641 ELSE IF @OrderBy = 6 /* Name: Z to A */
642 SET @sql_orderby = ' p.[Name] DESC'
643 ELSE IF @OrderBy = 10 /* Price: Low to High */
644 SET @sql_orderby = ' p.[Price] ASC'
645 ELSE IF @OrderBy = 11 /* Price: High to Low */
646 SET @sql_orderby = ' p.[Price] DESC'
647 ELSE IF @OrderBy = 15 /* creation date */
648 SET @sql_orderby = ' p.[CreatedOnUtc] DESC'
649 ELSE /* default sorting, 0 (position) */
650 BEGIN
651 --category position
652 IF @CategoryIdsCount > 0 SET @sql_orderby = ' pcm.Position ASC'
653
654 --name
655 IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
656 SET @sql_orderby = @sql_orderby + ' p.[Name] ASC'
657 END
658
659 SET @sql = @sql + '
660 ORDER BY' + @sql_orderby
661
662 SET @sql = '
663 INSERT INTO #PositionTmp ([ProductId])' + @sql
664
665 --PRINT (@sql)
666 EXEC sp_executesql @sql
667
668 DROP TABLE #FilteredCategoryIds
669 DROP TABLE #FilteredSpecs
670 DROP TABLE #FilteredSpecsWithAttributes
671 DROP TABLE #KeywordProducts
672
673 CREATE TABLE #PageIndex
674 (
675 [IndexId] int IDENTITY (1, 1) NOT NULL,
676 [ProductId] int NOT NULL
677 )
678 INSERT INTO #PageIndex (
679 [ProductId]
680 )
681 SELECT
682 ProductId
683 FROM
684 #PositionTmp
685 GROUP BY
686 ProductId
687 ORDER BY
688 min([Id])
689
690 --total records
691 SET @TotalRecords = @@rowcount
692
693 DROP TABLE #PositionTmp
694
695 --return products
696 SELECT TOP (@RowsToReturn)
697 p.*
698 FROM
699 #PageIndex [pi]
700 INNER JOIN Product p with (NOLOCK) on p.Id = [pi].[ProductId]
701 WHERE
702 [pi].IndexId > @PageLowerBound AND
703 [pi].IndexId < @PageUpperBound
704 ORDER BY
705 [pi].IndexId
706
707 DROP TABLE #PageIndex
708
709END
710GO