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