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