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