· 6 years ago · Jan 20, 2020, 09:24 PM
1DELIMITER $$
2CREATE FUNCTION `Check_Exists_FullText_Index`(
3 `TableName` varchar(200),
4 `IndexName` varchar(200)
5) RETURNS tinyint(1)
6sql security invoker
7BEGIN
8
9RETURN exists(
10 select distinct index_name from information_schema.statistics
11 where table_schema = database()
12 and TABLE_NAME = `TableName` and INDEX_TYPE = 'FULLTEXT' and INDEX_NAME like `IndexName`
13 );
14END$$
15DELIMITER ;
16DELIMITER $$
17CREATE PROCEDURE `CategoryLoadAllPaged`(
18 `ShowHidden` bool,
19 `Name` text,
20 `StoreId` int,
21 `CustomerRoleIds` text,
22 `PageIndex` int,
23 `PageSize` int,
24 OUT `TotalRecords` int
25)
26sql security invoker
27BEGIN
28 Set @lengthId = (select CHAR_LENGTH(MAX(Id)) FROM Category);
29 Set @lengthOrder = (select CHAR_LENGTH(MAX(DisplayOrder)) FROM Category);
30 drop temporary table if exists OrderedCategories;
31 create temporary table `OrderedCategories` (
32 `id` int,
33 `Order` text
34 );
35
36 insert into `OrderedCategories`
37 with recursive CategoryTree AS
38 (
39 SELECT id, cast(concat(LPAD(DisplayOrder, @lengthOrder, '0'), '-' , LPAD(Id, @lengthId, '0')) as char(500)) as `Order`
40 FROM category
41 WHERE ParentCategoryId = 0
42 UNION ALL
43 SELECT c.id, concat(sc.`Order`, '|', LPAD(c.DisplayOrder, @lengthOrder, '0'), '-' , LPAD(c.Id, @lengthId, '0')) as `Order`
44 FROM CategoryTree AS sc
45 JOIN category AS c ON sc.id = c.ParentCategoryId
46 )
47 select *
48 from CategoryTree;
49
50 select c.`Id`, c.`Name`, ct.`Order`
51 from category c
52 inner join `OrderedCategories` as ct on c.Id = ct.Id
53 #filter results
54 where not c.Deleted
55 and (ShowHidden OR c.Published)
56 and (COALESCE(`Name`, '') = '' OR c.`Name` LIKE concat('%', `Name`, '%'))
57 and (ShowHidden OR COALESCE(`CustomerRoleIds`, '') = '' OR not c.SubjectToAcl
58 OR EXISTS (
59 select 1
60 from aclRecord as acl
61 where find_in_set(acl.CustomerRoleId, CustomerRoleIds)
62 and acl.`EntityId` = c.`Id` AND acl.`EntityName` = 'Category')
63 )
64 and (not StoreId OR not c.`LimitedToStores`
65 OR EXISTS (SELECT 1 FROM storemapping sm
66 WHERE sm.`EntityId` = c.`Id` AND sm.`EntityName` = 'Category' AND sm.`StoreId` = StoreId
67 )
68 )
69 and ct.Id > `PageSize` * `PageIndex`
70 order by ct.`Order`, 1
71 LIMIT `PageSize`;
72
73 select count(*) from `OrderedCategories` into `TotalRecords`;
74
75 drop temporary table if exists OrderedCategories;
76END$$
77DELIMITER ;
78
79DELIMITER $$
80CREATE PROCEDURE `Create_FullText_Index`(
81 `TableName` varchar(200),
82 `ColumnNames` varchar(600),
83 `IndexName` varchar(200),
84 out `Result` bool
85)
86sql security invoker
87BEGIN
88 set `Result` = true;
89 select if (
90 `Check_Exists_FullText_Index`(`TableName`, `IndexName`)
91 ,'select false into @stmt_result'
92 , concat('CREATE FULLTEXT INDEX ', `IndexName`, ' ON ', `TableName`, '(', `ColumnNames`, ');')) into @a;
93
94 PREPARE stmt1 FROM @a;
95 EXECUTE stmt1;
96 DEALLOCATE PREPARE stmt1;
97
98 Set `Result` = @stmt_result;
99END$$
100DELIMITER ;
101
102DELIMITER $$
103CREATE PROCEDURE `DeleteGuests`(
104 OnlyWithoutShoppingCart bool,
105 CreatedFromUtc datetime,
106 CreatedToUtc datetime,
107 out TotalRecordsDeleted int
108)
109sql security invoker
110BEGIN
111 create temporary table tmp_guests (CustomerId int);
112
113 INSERT into tmp_guests (CustomerId)
114 SELECT c.`Id`
115 FROM `Customer` c
116 LEFT JOIN `ShoppingCartItem` sci ON sci.`CustomerId` = c.`Id`
117 INNER JOIN (
118 #guests only
119 SELECT ccrm.`Customer_Id`
120 FROM `Customer_CustomerRole_Mapping` ccrm
121 INNER JOIN `CustomerRole` cr ON cr.`Id` = ccrm.`CustomerRole_Id`
122 WHERE cr.`SystemName` = 'Guests'
123 ) g ON g.`Customer_Id` = c.`Id`
124 LEFT JOIN `Order` o ON o.`CustomerId` = c.`Id`
125 LEFT JOIN `BlogComment` bc ON bc.`CustomerId` = c.`Id`
126 LEFT JOIN `NewsComment` nc ON nc.`CustomerId` = c.`Id`
127 LEFT JOIN `ProductReview` pr ON pr.`CustomerId` = c.`Id`
128 LEFT JOIN `ProductReviewHelpfulness` prh ON prh.`CustomerId` = c.`Id`
129 LEFT JOIN `PollVotingRecord` pvr ON pvr.`CustomerId` = c.`Id`
130 LEFT JOIN `Forums_Topic` ft ON ft.`CustomerId` = c.`Id`
131 LEFT JOIN `Forums_Post` fp ON fp.`CustomerId` = c.`Id`
132 WHERE 1 = 1
133 #no orders
134 AND (o.Id is null)
135 #no blog comments
136 AND (bc.Id is null)
137 #no news comments
138 AND (nc.Id is null)
139 #no product reviews
140 AND (pr.Id is null)
141 #no product reviews helpfulness
142 AND (prh.Id is null)
143 #no poll voting
144 AND (pvr.Id is null)
145 #no forum topics
146 AND (ft.Id is null)
147 #no forum topics
148 AND (fp.Id is null)
149 #no system accounts
150 AND (c.IsSystemAccount = 0)
151 #created from
152 AND ((CreatedFromUtc is null) OR (c.`CreatedOnUtc` > @CreatedFromUtc))
153 #created to
154 AND ((CreatedToUtc is null) OR (c.`CreatedOnUtc` < @CreatedToUtc))
155 #shopping cart items
156 AND (OnlyWithoutShoppingCart OR (sci.Id is null));
157
158 #delete guests
159 DELETE from `Customer` WHERE `Id` IN (SELECT `CustomerId` FROM tmp_guests);
160
161 #delete attributes
162 DELETE FROM `GenericAttribute`
163 WHERE
164 `EntityId` IN (SELECT `CustomerId` FROM tmp_guests)
165 AND (`KeyGroup` = N'Customer');
166
167 #total records
168 SELECT COUNT(*) FROM tmp_guests into TotalRecordsDeleted;
169
170 DROP TEMPORARY TABLE tmp_guests;
171END$$
172DELIMITER ;
173
174DELIMITER $$
175CREATE PROCEDURE `Drop_FullText_Index`(
176 `TableName` varchar(200),
177 `IndexName` varchar(200),
178 out `Result` bool
179)
180sql security invoker
181BEGIN
182 set `Result` = true;
183 select if (
184 `Check_Exists_FullText_Index`(`TableName`, `IndexName`)
185 , concat('drop index ', `IndexName`, ' ON ', `TableName`, ';')
186 , 'select false into @stmt_result') into @a;
187
188 PREPARE stmt1 FROM @a;
189 EXECUTE stmt1;
190 DEALLOCATE PREPARE stmt1;
191
192 Set `Result` = @stmt_result;
193END$$
194DELIMITER ;
195
196DELIMITER $$
197CREATE PROCEDURE `FullText_Disable`()
198sql security invoker
199BEGIN
200 call `Drop_FullText_Index`('Product', 'FT_IX_Product', @drop_result);
201 call `Drop_FullText_Index`('LocalizedProperty', 'FT_IX_LocalizedProperty', @drop_result);
202 call `Drop_FullText_Index`('ProductTag', 'FT_IX_ProductTag', @drop_result);
203END$$
204DELIMITER ;
205
206DELIMITER $$
207CREATE PROCEDURE `FullText_Enable`()
208sql security invoker
209BEGIN
210 CALL `nop_mysql_db_test`.`Create_FullText_Index`('Product', 'Name, ShortDescription, FullDescription', 'FT_IX_Product', @result);
211 CALL `nop_mysql_db_test`.`Create_FullText_Index`('LocalizedProperty', 'LocaleValue', 'FT_IX_LocalizedProperty', @result);
212 CALL `nop_mysql_db_test`.`Create_FullText_Index`('ProductTag', 'Name', 'FT_IX_ProductTag', @result);
213END$$
214DELIMITER ;
215
216DELIMITER $$
217CREATE PROCEDURE `FullText_IsSupported`()
218sql security invoker
219BEGIN
220 select true;
221END$$
222DELIMITER ;
223
224DELIMITER $$
225CREATE PROCEDURE `ProductTagCountLoadAll`(
226 `StoreId` int,
227 `AllowedCustomerRoleIds` text #a list of customer role IDs (comma-separated list) for which a product should be shown (if a subject to ACL)
228)
229sql security invoker
230BEGIN
231 #filter by customer role IDs (access control list)
232 SELECT pt.Id as `ProductTagId`, COUNT(p.Id) as `ProductCount`
233 FROM ProductTag pt
234 LEFT JOIN Product_ProductTag_Mapping pptm ON pt.`Id` = pptm.`ProductTag_Id`
235 LEFT JOIN Product p ON pptm.`Product_Id` = p.`Id`
236 WHERE
237 not p.`Deleted`
238 AND p.Published
239 AND (`StoreId` = 0 or (p.LimitedToStores = 0 OR EXISTS (
240 SELECT 1 FROM `StoreMapping` sm
241 WHERE `sm`.EntityId = p.Id AND `sm`.EntityName = 'Product' and `sm`.StoreId=`StoreId`
242 )))
243 AND (length(`AllowedCustomerRoleIds`) = 0 or (not p.SubjectToAcl
244 OR EXISTS (
245 select 1
246 from aclRecord as acl
247 where find_in_set(acl.CustomerRoleId, `AllowedCustomerRoleIds`)
248 and acl.`EntityId` = p.`Id` AND acl.`EntityName` = 'Product')
249 ))
250 GROUP BY pt.Id
251 ORDER BY pt.Id;
252END$$
253DELIMITER ;
254
255DELIMITER $$
256CREATE PROCEDURE `ProductLoadAllPaged`(
257 `CategoryIds` text, #a list of category IDs (comma-separated list). e.g. 1,2,3
258 `ManufacturerId` int,
259 `StoreId` int,
260 `VendorId` int,
261 `WarehouseId` int,
262 `ProductTypeId` int, #product type identifier, null - load all products
263 `VisibleIndividuallyOnly` bool, #0 - load all products , 1 - "visible indivially" only
264 `MarkedAsNewOnly` bool, #0 - load all products , 1 - "marked as new" only
265 `ProductTagId` int,
266 `FeaturedProducts` bool, #0 featured only , 1 not featured only, null - load all products
267 `PriceMin` decimal(18, 4),
268 `PriceMax` decimal(18, 4),
269 `Keywords` text,
270 `SearchDescriptions` bool, #a value indicating whether to search by a specified "keyword" in product descriptions
271 `SearchManufacturerPartNumber` bool, # a value indicating whether to search by a specified "keyword" in manufacturer part number
272 `SearchSku` bool, #a value indicating whether to search by a specified "keyword" in product SKU
273 `SearchProductTags` bool, #a value indicating whether to search by a specified "keyword" in product tags
274 `UseFullTextSearch` bool,
275 `FullTextMode` int, #0 - using CONTAINS with <prefix_term>, 5 - using CONTAINS and OR with <prefix_term>, 10 - using CONTAINS and AND with <prefix_term>
276 `FilteredSpecs` text, #filter by specification attribute options (comma-separated list of IDs). e.g. 14,15,16
277 `LanguageId` int,
278 `OrderBy` int, #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
279 `AllowedCustomerRoleIds` text, #a list of customer role IDs (comma-separated list) for which a product should be shown (if a subject to ACL)
280 `PageIndex` int,
281 `PageSize` int,
282 `ShowHidden` bool,
283 `OverridePublished` bool, #null - process "Published" property according to "showHidden" parameter, true - load only "Published" products, false - load only "Unpublished" products
284 `LoadFilterableSpecificationAttributeOptionIds` bool, #a value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)
285 out `FilterableSpecificationAttributeOptionIds` text, #the specification attribute option identifiers applied to loaded products (all pages). returned as a comma separated list of identifiers
286 out `TotalRecords` int
287)
288 SQL SECURITY INVOKER
289BEGIN
290 DECLARE `SearchKeywords` bit default false;
291 DECLARE `sql_orderby` text;
292
293 Set @sql_command = '';
294
295 drop temporary TABLE if exists `KeywordProducts`;
296 drop temporary TABLE if exists `DisplayOrderTmp`;
297
298 /* Products that filtered by keywords */
299 CREATE temporary TABLE `KeywordProducts`
300 (
301 `ProductId` int NOT NULL
302 );
303
304 Set @Keywords = trim(COALESCE(`Keywords`, ''));
305 SET @OriginalKeywords = @Keywords;
306
307 IF @Keywords != '' then
308 SET `SearchKeywords` = true;
309 IF `UseFullTextSearch` then
310 #remove wrong chars (' ")
311 SET @Keywords = REPLACE(@Keywords, '''', '');
312 SET @Keywords = REPLACE(@Keywords, '"', '');
313 set @Keywords = concat(' ', @Keywords);
314
315 IF `FullTextMode` = 0 then
316 SET @Keywords = concat(' "', @Keywords, '*" ');
317 else
318 #5 - using CONTAINS and OR with <prefix_term>
319 #10 - using CONTAINS and AND with <prefix_term>
320
321 #clean multiple spaces
322 WHILE instr(@Keywords, ' ') > 0 do
323 SET @Keywords = REPLACE(@Keywords, ' ', ' ');
324 end while;
325
326 IF `FullTextMode` = 5 then #5 - using CONTAINS and OR with <prefix_term>
327 SET @concat_term = ' ';
328 END if;
329 IF `FullTextMode` = 10 then #10 - using CONTAINS and AND with <prefix_term>
330 SET @concat_term = ' +';
331 END if;
332
333 #now let's build search string
334 set @fulltext_keywords = '';
335 set @str_index = instr(@Keywords, ' ');
336
337 # if index = 0, then only one field was passed
338 IF(@str_index = 0) then
339 set @fulltext_keywords = concat(' "', @Keywords, '*" ');
340 ELSE
341 set @fulltext_keywords = replace(@Keywords, ' ', @concat_term);
342 end if;
343 SET @Keywords = @fulltext_keywords;
344 end if;
345 end if;
346
347 #product name
348 SET @sql_command = '
349 INSERT INTO `KeywordProducts` (ProductId)
350 SELECT p.Id
351 FROM Product p
352 WHERE ';
353
354 IF UseFullTextSearch then
355 SET @sql_command = concat(@sql_command, 'MATCH (p.`Name`) AGAINST (@Keywords IN BOOLEAN MODE) ');
356 ELSE
357 SET @sql_command = concat(@sql_command, 'instr(p.Name, @Keywords) > 0 ');
358 end if;
359
360 #localized product name
361 SET @sql_command = concat(@sql_command, '
362 UNION
363 SELECT lp.EntityId
364 FROM LocalizedProperty lp
365 WHERE
366 lp.LocaleKeyGroup = ''Product''
367 AND lp.LanguageId = ', `LanguageId`, '
368 AND lp.LocaleKey = ''Name''');
369
370 IF UseFullTextSearch = 1 then
371 SET @sql_command = concat(@sql_command, ' AND MATCH (lp.LocaleValue) AGAINST (@Keywords IN BOOLEAN MODE) ');
372 ELSE
373 SET @sql_command = concat(@sql_command, ' AND instr(lp.LocaleValue, @Keywords) > 0 ');
374 end if;
375
376 if `SearchDescriptions` then
377 #product short description
378 SET @sql_command = concat(@sql_command, '
379 UNION
380 SELECT p.Id
381 FROM Product p
382 WHERE ');
383
384 IF `UseFullTextSearch` then
385 SET @sql_command = concat(@sql_command, 'MATCH (p.ShortDescription, p.FullDescription) AGAINST (@Keywords IN BOOLEAN MODE) ');
386 ELSE
387 SET @sql_command = concat(@sql_command, 'instr(p.ShortDescription, @Keywords) > 0 or instr(p.FullDescription, @Keywords) > 0 ');
388 end if;
389
390 #localized product short description
391 SET @sql_command = concat(@sql_command, '
392 UNION
393 SELECT lp.EntityId
394 FROM LocalizedProperty lp
395 WHERE
396 lp.LocaleKeyGroup = ''Product''
397 AND lp.LanguageId = ', `LanguageId`, '
398 AND lp.LocaleKey = ''ShortDescription''');
399
400 IF `UseFullTextSearch` then
401 SET @sql_command = concat(@sql_command, ' AND MATCH (lp.LocaleValue) AGAINST (@Keywords IN BOOLEAN MODE) ');
402 ELSE
403 SET @sql_command = concat(@sql_command, ' AND instr(lp.LocaleValue, @Keywords) > 0 ');
404 end if;
405
406 #localized product full description
407 SET @sql_command = concat(@sql_command, '
408 UNION
409 SELECT lp.EntityId
410 FROM LocalizedProperty lp
411 WHERE
412 lp.LocaleKeyGroup = N''Product''
413 AND lp.LanguageId = ', `LanguageId`, '
414 AND lp.LocaleKey = N''FullDescription''');
415
416 IF `UseFullTextSearch` then
417 SET @sql_command = concat(@sql_command, ' AND MATCH (lp.LocaleValue) AGAINST (@Keywords IN BOOLEAN MODE) ');
418 ELSE
419 SET @sql_command = concat(@sql_command, ' AND instr(lp.LocaleValue, @Keywords) > 0 ');
420 end if;
421
422
423 end if;
424
425 #manufacturer part number (exact match)
426 IF `SearchManufacturerPartNumber` then
427 SET @sql_command = concat(@sql_command, '
428 UNION
429 SELECT p.Id
430 FROM Product p
431 WHERE p.ManufacturerPartNumber = @OriginalKeywords ');
432 END if;
433
434 #SKU (exact match)
435 IF `SearchSku` then
436 SET @sql_command = concat(@sql_command, '
437 UNION
438 SELECT p.Id
439 FROM Product p
440 WHERE p.Sku = @OriginalKeywords ');
441 END if;
442
443 IF `SearchProductTags` then
444 #product tags (exact match)
445 SET @sql_command = concat(@sql_command, '
446 UNION
447 SELECT pptm.Product_Id
448 FROM Product_ProductTag_Mapping pptm INNER JOIN ProductTag pt ON pt.Id = pptm.ProductTag_Id
449 WHERE pt.`Name` = @OriginalKeywords ');
450
451 #localized product tags
452 SET @sql_command = concat(@sql_command, '
453 UNION
454 SELECT pptm.Product_Id
455 FROM LocalizedProperty lp INNER JOIN Product_ProductTag_Mapping pptm ON lp.EntityId = pptm.ProductTag_Id
456 WHERE
457 lp.LocaleKeyGroup = N''ProductTag''
458 AND lp.LanguageId = ', `LanguageId`, '
459 AND lp.LocaleKey = N''Name''
460 AND lp.`LocaleValue` = @OriginalKeywords ');
461 END if;
462
463 #select @sql_command, @Keywords, @OriginalKeywords; #debug
464
465 PREPARE sql_stmts FROM @sql_command;
466 EXECUTE sql_stmts;
467 DEALLOCATE PREPARE sql_stmts;
468 end if;
469
470 create temporary table `DisplayOrderTmp`
471 (
472 Id int NOT NULL auto_increment,
473 ProductId int NOT NULL,
474 PRIMARY KEY (id)
475 );
476
477 #filter by category IDs
478 SET `CategoryIds` = COALESCE(`CategoryIds`, '');
479
480 SET @sql_command = '
481 SELECT p.Id
482 FROM
483 Product p';
484
485 IF `CategoryIds` REGEXP '^([[:digit:]](,?))+$' then
486 SET @sql_command = concat(@sql_command, '
487 INNER JOIN Product_Category_Mapping pcm
488 ON p.Id = pcm.ProductId');
489 END if;
490
491 IF `ManufacturerId` > 0 then
492 SET @sql_command = concat(@sql_command, '
493 INNER JOIN Product_Manufacturer_Mapping pmm
494 ON p.Id = pmm.ProductId');
495 END if;
496
497 IF COALESCE(`ProductTagId`, 0) != 0 then
498 SET @sql_command = concat(@sql_command, '
499 INNER JOIN Product_ProductTag_Mapping pptm
500 ON p.Id = pptm.Product_Id');
501 END if;
502
503 #searching by keywords
504 IF `SearchKeywords` then
505 SET @sql_command = concat(@sql_command, '
506 JOIN `KeywordProducts` kp
507 ON p.Id = kp.ProductId');
508 END if;
509
510 SET @sql_command = concat(@sql_command, '
511 WHERE
512 p.Deleted = 0');
513
514 #filter by category
515 IF `CategoryIds` REGEXP '^([[:digit:]](,?))+$' then
516 SET @sql_command = concat(@sql_command, '
517 AND pcm.CategoryId IN (', `CategoryIds`, ')');
518
519 IF `FeaturedProducts` IS NOT NULL then
520 SET @sql_command = concat(@sql_command, '
521 AND pcm.IsFeaturedProduct = ', `FeaturedProducts`);
522 END if;
523 END if;
524
525 #filter by manufacturer
526 IF `ManufacturerId` > 0 then
527 SET @sql_command = concat(@sql_command, '
528 AND pmm.ManufacturerId = ', `ManufacturerId`);
529
530 IF `FeaturedProducts` IS NOT NULL then
531 SET @sql_command = concat(@sql_command, '
532 AND pmm.IsFeaturedProduct = ', `FeaturedProducts`);
533 END if;
534 END if;
535
536 #filter by vendor
537 IF `VendorId` > 0 then
538 SET @sql_command = concat(@sql_command, '
539 AND p.VendorId = ', `VendorId`);
540 END if;
541
542 #filter by warehouse
543 IF `WarehouseId` > 0 then
544 #we should also ensure that 'ManageInventoryMethodId' is set to 'ManageStock' (1)
545 #but we skip it in order to prevent hard-coded values (e.g. 1) and for better performance
546 SET @sql_command = concat(@sql_command, '
547 AND
548 (
549 (p.UseMultipleWarehouses = 0 AND
550 p.WarehouseId = ', `WarehouseId`, ')
551 OR
552 (p.UseMultipleWarehouses > 0 AND
553 EXISTS (SELECT 1 FROM ProductWarehouseInventory pwi
554 WHERE pwi.WarehouseId = ', `WarehouseId`, ' AND pwi.ProductId = p.Id))
555 )');
556 END if;
557
558 #filter by product type
559 IF `ProductTypeId` is not null then
560 SET @sql_command = concat(@sql_command, '
561 AND p.ProductTypeId = ', `ProductTypeId`);
562 END if;
563
564 #filter by "visible individually"
565 IF `VisibleIndividuallyOnly` then
566 SET @sql_command = concat(@sql_command, '
567 AND p.VisibleIndividually = 1');
568 END if;
569
570 #filter by "marked as new"
571 IF `MarkedAsNewOnly` then
572 SET @sql_command = concat(@sql_command, '
573 AND p.MarkAsNew = 1
574 AND (utc_date() BETWEEN IFNULL(p.MarkAsNewStartDateTimeUtc, ''1/1/1900'') and IFNULL(p.MarkAsNewEndDateTimeUtc, ''1/1/2999''))');
575 END if;
576
577 #filter by product tag
578 IF COALESCE(`ProductTagId`, 0) != 0 then
579 SET @sql_command = concat(@sql_command, '
580 AND pptm.ProductTag_Id = ', `ProductTagId`);
581 END if;
582
583 #"Published" property
584 IF `OverridePublished` is null then
585 #process according to "showHidden"
586 IF not `ShowHidden` then
587 SET @sql_command = concat(@sql_command, '
588 AND p.Published');
589 END if;
590 ELSEIF `OverridePublished` then
591 #published only
592 SET @sql_command = concat(@sql_command, '
593 AND p.Published');
594 ELSEIF not `OverridePublished` then
595 #unpublished only
596 SET @sql_command = concat(@sql_command, '
597 AND not p.Published');
598 END if;
599
600 #show hidden
601 IF not `ShowHidden` then
602 SET @sql_command = concat(@sql_command, '
603 AND not p.Deleted
604 AND (utc_date() BETWEEN IFNULL(p.AvailableStartDateTimeUtc, ''1000-01-01'') and IFNULL(p.AvailableEndDateTimeUtc, ''9999-12-31''))');
605 END if;
606
607 #min price
608 IF `PriceMin` is not null then
609 SET @sql_command = concat(@sql_command, '
610 AND (p.Price >= ', `PriceMin`, ')');
611 END if;
612
613 #max price
614 IF `PriceMax` is not null then
615 SET @sql_command = concat(@sql_command, '
616 AND (p.Price <= ', `PriceMax`, ')');
617 END if;
618
619 #show hidden and ACL
620 IF not `ShowHidden` and `AllowedCustomerRoleIds` REGEXP '^([[:digit:]](,?))+$' then
621 SET @sql_command = concat(@sql_command, '
622 AND (not p.SubjectToAcl OR EXISTS (
623 SELECT 1
624 from aclRecord as acl
625 where acl.CustomerRoleId in (', `AllowedCustomerRoleIds` ,')
626 and acl.`EntityId` = p.`Id` AND acl.`EntityName` = ''Product''
627 )
628 )');
629 END if;
630
631 #filter by store
632 IF `StoreId` > 0 then
633 SET @sql_command = concat(@sql_command, '
634 AND (not p.LimitedToStores OR EXISTS (
635 SELECT 1 FROM StoreMapping sm
636 WHERE sm.EntityId = p.Id AND sm.EntityName = ''Product'' and sm.StoreId=', `StoreId`, '
637 ))');
638 END if;
639
640 #prepare filterable specification attribute option identifier (if requested)
641 IF `LoadFilterableSpecificationAttributeOptionIds` then
642 SET @sql_filterableSpecs = concat('
643 SELECT group_concat(DISTINCT `psam`.SpecificationAttributeOptionId separator '','')
644 FROM `Product_SpecificationAttribute_Mapping` `psam`
645 WHERE `psam`.`AllowFiltering`
646 AND `psam`.`ProductId` IN (', @sql_command, ') into @FilterableSpecs');
647
648 #select @sql_filterableSpecs; #debug
649 PREPARE sql_filterableSpecs_stmts FROM @sql_filterableSpecs;
650 EXECUTE sql_filterableSpecs_stmts;
651
652 #build comma separated list of filterable identifiers
653 if @FilterableSpecs is not null and length(@FilterableSpecs) > 0 then
654 Set `FilterableSpecificationAttributeOptionIds` = concat(IFNULL(concat(`FilterableSpecificationAttributeOptionIds`, ','), ''), @FilterableSpecs);
655 end if;
656
657 DEALLOCATE PREPARE sql_filterableSpecs_stmts;
658 end if;
659
660 #filter by specification attribution options
661 # wtf??
662
663 #sorting
664 SET @sql_orderby = '';
665
666 CASE `OrderBy`
667 WHEN 5 THEN Set @sql_orderby = ' p.`Name` ASC'; /* Name: A to Z */
668 WHEN 6 THEN Set @sql_orderby = ' p.`Name` DESC'; /* Name: Z to A */
669 WHEN 10 THEN Set @sql_orderby = ' p.`Price` ASC'; /* Price: Low to High */
670 WHEN 11 THEN Set @sql_orderby = ' p.`Price` DESC'; /* Price: High to Low */
671 WHEN 15 THEN Set @sql_orderby = ' p.`CreatedOnUtc` DESC'; /* creation date */
672 ELSE /* default sorting, 0 (position) */
673 begin
674 IF `CategoryIds` REGEXP '^([[:digit:]](,?))+$' then
675 SET @sql_orderby = ' pcm.DisplayOrder ASC';
676 end if;
677
678 #manufacturer position (display order)
679 IF `ManufacturerId` > 0 then
680 IF length(@sql_orderby) > 0 then
681 SET @sql_orderby = concat(@sql_orderby, ', ');
682 end if;
683 SET @sql_orderby = concat(@sql_orderby, ' pmm.DisplayOrder ASC');
684 END if;
685
686 #name
687 IF length(@sql_orderby) > 0 then
688 SET @sql_orderby = concat(@sql_orderby, ', ');
689 end if;
690 SET @sql_orderby = concat(@sql_orderby, ' p.`Name` ASC');
691 end;
692 end case;
693
694 SET @sql_command = concat(@sql_command, '
695 ORDER BY', @sql_orderby);
696
697 SET @sql_command = concat('
698 INSERT INTO DisplayOrderTmp (ProductId)', @sql_command);
699
700 #SELECT @sql_command; #debug
701
702 PREPARE sql_do_stmts FROM @sql_command;
703 EXECUTE sql_do_stmts;
704 DEALLOCATE PREPARE sql_do_stmts;
705
706 select count(Id) from `DisplayOrderTmp` into `TotalRecords`;
707
708 #return products
709 SELECT p.*
710 FROM `DisplayOrderTmp` dot
711 INNER JOIN Product p on p.Id = dot.ProductId
712 WHERE dot.Id > `PageSize` * `PageIndex`
713 ORDER BY dot.Id
714 limit `PageSize`;
715
716 drop temporary TABLE if exists `KeywordProducts`;
717 drop temporary TABLE if exists `DisplayOrderTmp`;
718END$$
719DELIMITER ;