a.m. wrote:
4. "Grouped" product is a product which could have several "simple" products associated and displayed on one single product details page (previously it was "a product with several variants"). First a store owner will have to create several products with "simple" product type, then he'll create a "grouped" product and will assign these "simple" products to this "grouped" product. In this case we use properties such as price, inventory, etc only for "simple" associated products. For "grouped" products these properties are ignored
This is a nice one !
and i changed nop's database to Mysql,the StoredProcedures is a longtime work.
Can you guys write a Mysql StoredProcedures when add a new one?
this is the Mysql StoredProcedures for Nopcommerce 3.40
CREATE PROCEDURE `nop_splitstring_to_table`
(
IN string LONGTEXT,
IN delim CHAR(1)
)
BEGIN
DROP TEMPORARY TABLE IF EXISTS nop_splitstring_to_table_TempTable;
CREATE TEMPORARY TABLE nop_splitstring_to_table_TempTable
(
`data` LONGTEXT
);
set @start = 1;
set @theend = LOCATE(delim, string);
WHILE @start < LENGTH(string) + 1 DO
IF @theend = 0 then
SET @theend = LENGTH(string) + 1;
END IF;
INSERT INTO nop_splitstring_to_table_TempTable (data)
VALUES(SUBSTRING(string, @start, @theend - @start));
SET @start = @theend + 1;
SET @theend = LOCATE(delim, string, @start);
END WHILE;
END;
-- GO
CREATE PROCEDURE `nop_getnotnullnotempty`
(
IN p1 LONGTEXT,
IN p2 LONGTEXT,
OUT res LONGTEXT
)
BEGIN
set res = p1;
IF res IS NULL OR res = '' then
set res = p2;
END IF;
END;
-- GO
CREATE PROCEDURE `nop_getprimarykey_indexname`
(
IN table_name varchar(1000),
OUT index_name varchar(1000)
)
BEGIN
select CONSTRAINT_NAME into index_name from information_schema.table_constraints t
where t.TABLE_NAME = table_name AND CONSTRAINT_TYPE = 'PRIMARY KEY';
END;
-- GO
CREATE PROCEDURE `ProductLoadAllPaged`(
IN CategoryIds LONGTEXT, -- a list of category IDs (comma-separated list). e.g. 1,2,3
IN ManufacturerId int,
in StoreId int,
in VendorId int,
in WarehouseId int,
in ParentGroupedProductId int,
in ProductTypeId int, -- product type identifier, null - load all products
in VisibleIndividuallyOnly bool, -- 0 - load all products , 1 - "visible indivially" only
IN ProductTagId int,
IN FeaturedProducts bool, -- 0 featured only , 1 not featured only, null - load all products
IN PriceMin decimal(18, 4),
IN PriceMax decimal(18, 4),
IN Keywords nvarchar(4000),
IN SearchDescriptions bool,
in SearchSku bool, -- a value indicating whether to search by a specified "keyword" in product SKU
in SearchProductTags bool, -- a value indicating whether to search by a specified "keyword" in product tags
IN UseFullTextSearch bool,
IN FullTextMode int, -- 0 using CONTAINS with <prefix_term>, 5 - using CONTAINS and OR with <prefix_term>, 10 - using CONTAINS and AND with <prefix_term>
IN FilteredSpecs longtext, -- filter by attributes (comma-separated list). e.g. 14,15,16
IN LanguageId int,
IN 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
in AllowedCustomerRoleIds longtext, -- a list of customer role IDs (comma-separated list) for which a product should be shown (if a subjet to ACL)
IN PageIndex int,
IN PageSize int,
IN ShowHidden bool,
IN LoadFilterableSpecificationAttributeOptionIds bool, -- a value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)
OUT FilterableSpecificationAttributeOptionIds longtext, -- the specification attribute option identifiers applied to loaded products (all pages). returned as a comma separated list of identifiers
OUT TotalRecords int
)
BEGIN
if ManufacturerId is null then
set ManufacturerId = 0;
END IF;
if StoreId is null then
set StoreId = 0;
END IF;
if VendorId is null then
set VendorId = 0;
END IF;
if WarehouseId is null then
set WarehouseId = 0;
END IF;
if ParentGroupedProductId is null then
set ParentGroupedProductId = 0;
END IF;
if VisibleIndividuallyOnly is null then
set VisibleIndividuallyOnly = 0;
END IF;
if ProductTagId is null then
set ProductTagId = 0;
END IF;
if SearchDescriptions is null then
set SearchDescriptions = 0;
END IF;
if SearchSku is null then
set SearchSku = 0;
END IF;
if SearchProductTags is null then
set SearchProductTags = 0;
END IF;
if UseFullTextSearch is null then
set UseFullTextSearch = 0;
END IF;
if FullTextMode is null then
set FullTextMode = 0;
END IF;
if LanguageId is null then
set LanguageId = 0;
END IF;
if OrderBy is null then
set OrderBy = 0;
END IF;
if PageIndex is null then
set PageIndex = 0;
END IF;
if PageSize is null then
set PageSize = 2147483644;
END IF;
if ShowHidden is null then
set ShowHidden = 0;
END IF;
if LoadFilterableSpecificationAttributeOptionIds is null then
set LoadFilterableSpecificationAttributeOptionIds = 0;
END IF;
/* Products that filtered by keywords */
drop temporary table if exists KeywordProducts_TempTable;
CREATE temporary TABLE KeywordProducts_TempTable
(
ProductId int NOT NULL
) ENGINE = MEMORY;
-- filter by keywords
SET Keywords = coalesce(Keywords, '');
SET Keywords = rtrim(ltrim(Keywords));
IF Keywords != '' then
SET @SearchKeywords = 1;
IF UseFullTextSearch = 1 then
-- full-text search
IF FullTextMode = 0 then
-- 0 - using CONTAINS with <prefix_term>
SET Keywords = concat(' ', Keywords, '* ');
ELSE
-- 5 - using CONTAINS and OR with <prefix_term>
-- 10 - using CONTAINS and AND with <prefix_term>
-- remove wrong chars (' ")
SET Keywords = REPLACE(Keywords, '''', '');
SET Keywords = REPLACE(Keywords, '"', '');
-- clean multiple spaces
WHILE LOCATE(' ', Keywords) > 0 DO
SET Keywords = REPLACE(Keywords, ' ', ' ');
END WHILE;
IF FullTextMode = 5 then -- 5 - using CONTAINS and OR with <prefix_term>
SET @concat_term = '';
ELSEIF FullTextMode = 10 then -- 10 - using CONTAINS and AND with <prefix_term>
SET @concat_term = '+';
END IF;
-- now let's build search string
set @fulltext_keywords = N'';
set @index = LOCATE(' ', Keywords);
-- if index = 0, then only one field was passed
IF @index = 0 then
set @fulltext_keywords = CONCAT(' ', Keywords, '* ');
ELSE
-- SET @first = 1;
WHILE @index > 0 DO
-- IF @first = 1 then
-- SET @fulltext_keywords = @concat_term; -- CONCAT(@fulltext_keywords, ' ', @concat_term, ' ');
-- -- ELSE
-- SET @first = 0;
-- END IF;
SET @fulltext_keywords = CONCAT(@fulltext_keywords, ' ', @concat_term, SUBSTRING(Keywords, 1, @index - 1), '*');
SET Keywords = SUBSTRING(Keywords, @index + 1, LENGTH(Keywords) - @index);
SET @index = LOCATE(' ', Keywords);
end while;
-- add the last field
IF LENGTH(@fulltext_keywords) > 0 THEN
SET @fulltext_keywords = CONCAT(@fulltext_keywords, ' ', @concat_term, SUBSTRING(Keywords, 1, LENGTH(Keywords)), '*');
END IF;
END IF;
SET Keywords = ltrim(rtrim(@fulltext_keywords));
END IF;
ELSE
-- usual search by PATINDEX
SET Keywords = concat('%', Keywords, '%');
END IF;
-- PRINT Keywords
-- product name
SET @sql = '
INSERT INTO KeywordProducts_TempTable (`ProductId`)
SELECT p.Id
FROM Product p
WHERE ';
IF UseFullTextSearch = 1 THEN
SET @sql = CONCAT(@sql, 'MATCH (p.`Name`) AGAINST (@TempKeywords IN BOOLEAN MODE) ');
ELSE
SET @sql = CONCAT(@sql, 'p.`Name` LIKE @TempKeywords ');
END IF;
-- product variant name
-- SET @sql = CONCAT(@sql, '
-- UNION
-- SELECT pv.ProductId
-- FROM ProductVariant pv
-- WHERE ');
-- IF UseFullTextSearch = 1 THEN
-- SET @sql = CONCAT(@sql, 'MATCH (pv.`Name`) AGAINST (@TempKeywords IN BOOLEAN MODE) ');
-- ELSE
-- SET @sql = CONCAT(@sql, 'pv.`Name` LIKE @TempKeywords ');
-- END IF;
-- localized product name
SET @sql = CONCAT(@sql, '
UNION
SELECT lp.EntityId
FROM LocalizedProperty lp
WHERE
lp.LocaleKeyGroup = N''Product''
AND lp.LanguageId = ', COALESCE(LanguageId, 0), '
AND lp.LocaleKey = N''Name''');
IF UseFullTextSearch = 1 THEN
SET @sql = CONCAT(@sql, ' AND MATCH (lp.`LocaleValue`) AGAINST (@TempKeywords IN BOOLEAN MODE) ');
ELSE
SET @sql = CONCAT(@sql, ' AND lp.`LocaleValue` LIKE @TempKeywords ');
END IF;
-- product short description
IF SearchDescriptions = 1 THEN
SET @sql = CONCAT(@sql, '
UNION
SELECT p.Id
FROM Product p
WHERE ');
IF UseFullTextSearch = 1 THEN
SET @sql = CONCAT(@sql, 'MATCH (p.`ShortDescription`) AGAINST (@TempKeywords IN BOOLEAN MODE) ');
ELSE
SET @sql = CONCAT(@sql, 'p.`ShortDescription` LIKE @TempKeywords ');
END IF;
-- product full description
SET @sql = CONCAT(@sql, '
UNION
SELECT p.Id
FROM Product p
WHERE ');
IF UseFullTextSearch = 1 THEN
SET @sql = CONCAT(@sql, 'MATCH (p.`FullDescription`) AGAINST (@TempKeywords IN BOOLEAN MODE) ');
ELSE
SET @sql = CONCAT(@sql, 'p.`FullDescription` LIKE @TempKeywords ');
END IF;
-- product variant description
-- SET @sql = CONCAT(@sql, '
-- UNION
-- SELECT pv.ProductId
-- FROM ProductVariant pv
-- WHERE ');
-- IF UseFullTextSearch = 1 THEN
-- SET @sql = CONCAT(@sql, 'MATCH (pv.`Description`) AGAINST (@TempKeywords IN BOOLEAN MODE) ');
-- ELSE
-- SET @sql = CONCAT(@sql, 'pv.`Description` LIKE @TempKeywords ');
-- END IF;
-- localized product short description
SET @sql = CONCAT(@sql, '
UNION
SELECT lp.EntityId
FROM LocalizedProperty lp
WHERE
lp.LocaleKeyGroup = N''Product''
AND lp.LanguageId = ', COALESCE(LanguageId, 0), '
AND lp.LocaleKey = N''ShortDescription''');
IF UseFullTextSearch = 1 THEN
SET @sql = CONCAT(@sql, ' AND MATCH (lp.`LocaleValue`) AGAINST (@TempKeywords IN BOOLEAN MODE) ');
ELSE
SET @sql = CONCAT(@sql, ' AND lp.`LocaleValue` LIKE @TempKeywords ');
END IF;
-- localized product full description
SET @sql = CONCAT(@sql, '
UNION
SELECT lp.EntityId
FROM LocalizedProperty lp
WHERE
lp.LocaleKeyGroup = N''Product''
AND lp.LanguageId = ', COALESCE(LanguageId, 0), '
AND lp.LocaleKey = N''FullDescription''');
IF UseFullTextSearch = 1 THEN
SET @sql = CONCAT(@sql, ' AND MATCH (lp.`LocaleValue`) AGAINST (@TempKeywords IN BOOLEAN MODE) ');
ELSE
SET @sql = CONCAT(@sql, ' AND lp.`LocaleValue` LIKE @TempKeywords ');
END IF;
END IF;
-- SKU
if SearchSku = 1 then
SET @sql = CONCAT(@sql, '
UNION
SELECT p.ProductId
FROM Product p
WHERE ');
IF UseFullTextSearch = 1 THEN
SET @sql = CONCAT(@sql, 'MATCH (p.`Sku`) AGAINST (@TempKeywords IN BOOLEAN MODE) ');
ELSE
SET @sql = CONCAT(@sql, 'p.`Sku` LIKE @TempKeywords ');
END IF;
end if;
if SearchProductTags = 1 then
-- product tag
SET @sql = CONCAT(@sql, 'UNION
SELECT pptm.Product_Id
FROM Product_ProductTag_Mapping pptm INNER JOIN ProductTag pt ON pt.Id = pptm.ProductTag_Id
WHERE ');
if @UseFullTextSearch = 1 then
SET @sql = CONCAT(@sql, 'MATCH (pt.`Name`) AGAINST (@TempKeywords IN BOOLEAN MODE) ');
ELSE
SET @sql = CONCAT(@sql, 'pt.`Name` LIKE @TempKeywords ');
end if;
-- localized product tag
SET @sql = CONCAT(@sql, '
UNION
SELECT pptm.Product_Id
FROM LocalizedProperty lp INNER JOIN Product_ProductTag_Mapping pptm ON lp.EntityId = pptm.ProductTag_Id
WHERE
lp.LocaleKeyGroup = N''ProductTag''
AND lp.LanguageId = ', COALESCE(LanguageId, 0), '
AND lp.LocaleKey = N''Name''');
if @UseFullTextSearch = 1 then
SET @sql = CONCAT(@sql, 'MATCH (lp.`LocaleValue`) AGAINST (@TempKeywords IN BOOLEAN MODE) ');
ELSE
SET @sql = CONCAT(@sql, 'lp.`LocaleValue` LIKE @TempKeywords ');
end if;
end if;
-- select @sql;
-- select Keywords;
set @TempKeywords = Keywords;
-- SET @sql = '
-- Select @TempKeywords;';
-- PRINT (@sql)
PREPARE stmt1 FROM @sql;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
EXECUTE stmt1; -- USING @TempKeywords;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DEALLOCATE PREPARE stmt1;
-- select * from KeywordProducts_TempTable;
-- EXEC sp_executesql @sql, N'Keywords varchar(4000)', Keywords
ELSE
SET @SearchKeywords = 0;
END IF;
-- filter by category IDs
SET CategoryIds = COALESCE(CategoryIds, '');
Drop temporary table if exists FilteredCategoryIds_TempTable;
CREATE temporary TABLE FilteredCategoryIds_TempTable
(
CategoryId int not null
) ENGINE = MEMORY;
call nop_splitstring_to_table(CategoryIds, ',');
INSERT INTO FilteredCategoryIds_TempTable (CategoryId)
SELECT (data + 0) FROM nop_splitstring_to_table_TempTable;
SELECT COUNT(1) FROM FilteredCategoryIds_TempTable into @CategoryIdsCount;
-- filter by attributes
SET FilteredSpecs = COALESCE(FilteredSpecs, '');
Drop temporary table if exists FilteredSpecs_TempTable;
CREATE temporary TABLE FilteredSpecs_TempTable
(
SpecificationAttributeOptionId int not null
) ENGINE = MEMORY;
call nop_splitstring_to_table(FilteredSpecs, ',');
INSERT INTO FilteredSpecs_TempTable (SpecificationAttributeOptionId)
SELECT (data + 0) FROM nop_splitstring_to_table_TempTable;
SELECT COUNT(1) FROM FilteredSpecs_TempTable into @SpecAttributesCount;
-- filter by customer role IDs (access control list)
SET AllowedCustomerRoleIds = COALESCE(AllowedCustomerRoleIds, '');
Drop temporary table if exists FilteredCustomerRoleIds_TempTable;
CREATE temporary TABLE FilteredCustomerRoleIds_TempTable
(
CustomerRoleId int not null
) ENGINE = MEMORY;
call nop_splitstring_to_table(AllowedCustomerRoleIds, ',');
INSERT INTO FilteredCustomerRoleIds_TempTable (CustomerRoleId)
SELECT (data + 0) FROM nop_splitstring_to_table_TempTable;
-- paging
SET @RowsToReturn = PageSize * (PageIndex + 1);
SET @PageLowerBound = PageSize * PageIndex;
SET @PageUpperBound = @PageLowerBound + PageSize + 1;
Drop temporary table if exists DisplayOrder_TempTable;
CREATE temporary TABLE DisplayOrder_TempTable
(
Id int NOT NULL AUTO_INCREMENT,
ProductId int NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE = MEMORY;
SET @sql = '
INSERT INTO DisplayOrder_TempTable (`ProductId`)
SELECT p.Id
FROM
Product p';
IF @CategoryIdsCount > 0 THEN
SET @sql = CONCAT(@sql, '
LEFT JOIN Product_Category_Mapping pcm
ON p.Id = pcm.ProductId');
END IF;
IF ManufacturerId > 0 THEN
SET @sql = CONCAT(@sql, '
LEFT JOIN Product_Manufacturer_Mapping pmm
ON p.Id = pmm.ProductId');
END IF;
IF COALESCE(ProductTagId, 0) != 0 THEN
SET @sql = CONCAT(@sql, '
LEFT JOIN Product_ProductTag_Mapping pptm
ON p.Id = pptm.Product_Id');
END IF;
-- IF ShowHidden = 0
-- OR PriceMin > 0
-- OR PriceMax > 0
-- OR OrderBy = 10 /* Price: Low to High */
-- OR OrderBy = 11 /* Price: High to Low */ THEN
-- SET @sql = CONCAT(@sql, '
-- LEFT JOIN ProductVariant pv
-- ON p.Id = pv.ProductId');
-- END IF;
-- searching by keywords
IF @SearchKeywords = 1 THEN
SET @sql = CONCAT(@sql, '
JOIN KeywordProducts_TempTable kp
ON p.Id = kp.ProductId');
END IF;
SET @sql = CONCAT(@sql, '
WHERE
p.Deleted = 0');
-- filter by category
IF @CategoryIdsCount > 0 THEN
SET @sql = CONCAT(@sql, '
AND pcm.CategoryId IN (SELECT CategoryId FROM FilteredCategoryIds_TempTable)');
IF FeaturedProducts IS NOT NULL THEN
SET @sql = CONCAT(@sql, '
AND pcm.IsFeaturedProduct = ', FeaturedProducts);
END IF;
END IF;
-- filter by manufacturer
IF ManufacturerId > 0 THEN
SET @sql = CONCAT(@sql, '
AND pmm.ManufacturerId = ', ManufacturerId);
IF FeaturedProducts IS NOT NULL THEN
SET @sql = CONCAT(@sql, '
AND pmm.IsFeaturedProduct = ', FeaturedProducts);
END IF;
END IF;
-- filter by vendor
if VendorId > 0 then
SET @sql = CONCAT(@sql, '
AND p.VendorId = ', VendorId);
end if;
-- filter by warehouse
if WarehouseId > 0 then
SET @sql = CONCAT(@sql, '
AND p.WarehouseId = ', WarehouseId);
end if;
-- filter by parent grouped product identifer
if ParentGroupedProductId > 0 then
SET @sql = CONCAT(@sql, '
AND p.ParentGroupedProductId = ', ParentGroupedProductId);
end if;
-- filter by product type
if ProductTypeId is not null then
SET @sql = CONCAT(@sql, '
AND p.ProductTypeId = ', ProductTypeId);
end if;
-- filter by parent product identifer
if VisibleIndividuallyOnly =1 then
SET @sql = CONCAT(@sql, '
AND p.VisibleIndividually = 1');
end if;
-- filter by product tag
IF COALESCE(ProductTagId, 0) != 0 THEN
SET @sql = CONCAT(@sql, '
AND pptm.ProductTag_Id = ', ProductTagId);
END IF;
-- show hidden
IF ShowHidden = 0 THEN
SET @sql = CONCAT(@sql, '
AND p.Published = 1
AND p.Deleted = 0
AND (utc_timestamp() BETWEEN COALESCE(p.AvailableStartDateTimeUtc, STR_TO_DATE(''(1-1-1900)'', ''(%e-%c-%Y)'')) and COALESCE(p.AvailableEndDateTimeUtc, STR_TO_DATE(''(1-1-2999)'', ''(%e-%c-%Y)'')))');
END IF;
-- min price
IF PriceMin > 0 THEN
SET @sql = CONCAT(@sql, '
AND (
(
-- special price (specified price and valid date range)
(p.SpecialPrice IS NOT NULL AND (utc_timestamp() BETWEEN COALESCE(p.SpecialPriceStartDateTimeUtc, STR_TO_DATE(''(1-1-1900)'', ''(%e-%c-%Y)'')) AND COALESCE(p.SpecialPriceEndDateTimeUtc, STR_TO_DATE(''(1-1-2999)'', ''(%e-%c-%Y)''))))
AND
(p.SpecialPrice >= ', PriceMin, ')
)
OR
(
-- regular price (price isnt specified or date range isnt valid)
(p.SpecialPrice IS NULL OR (utc_timestamp() NOT BETWEEN COALESCE(p.SpecialPriceStartDateTimeUtc, STR_TO_DATE(''(1-1-1900)'', ''(%e-%c-%Y)'')) AND COALESCE(p.SpecialPriceEndDateTimeUtc, STR_TO_DATE(''(1-1-2999)'', ''(%e-%c-%Y)''))))
AND
(p.Price >= ', PriceMin, ')
)
)');
END IF;
-- max price
IF PriceMax > 0 THEN
SET @sql = CONCAT(@sql, '
AND (
(
-- special price (specified price and valid date range)
(p.SpecialPrice IS NOT NULL AND (utc_timestamp() BETWEEN COALESCE(p.SpecialPriceStartDateTimeUtc, STR_TO_DATE(''(1-1-1900)'', ''(%e-%c-%Y)'')) AND COALESCE(p.SpecialPriceEndDateTimeUtc, STR_TO_DATE(''(1-1-2999)'', ''(%e-%c-%Y)''))))
AND
(p.SpecialPrice <= ', PriceMax, ')
)
OR
(
-- regular price (price isnt specified or date range isnt valid)
(p.SpecialPrice IS NULL OR (utc_timestamp() NOT BETWEEN COALESCE(p.SpecialPriceStartDateTimeUtc, STR_TO_DATE(''(1-1-1900)'', ''(%e-%c-%Y)'')) AND COALESCE(p.SpecialPriceEndDateTimeUtc, STR_TO_DATE(''(1-1-2999)'', ''(%e-%c-%Y)''))))
AND
(p.Price <= ', PriceMax, ')
)
)');
END IF;
-- show hidden and ACL
if ShowHidden = 0 then
SET @sql = CONCAT(@sql, '
AND (p.SubjectToAcl = 0 OR EXISTS (
SELECT 1 FROM FilteredCustomerRoleIds_TempTable `fcr`
WHERE
`fcr`.CustomerRoleId IN (
SELECT `acl`.CustomerRoleId
FROM `AclRecord` acl
WHERE `acl`.EntityId = p.Id AND `acl`.EntityName = ''Product''
)
))');
end if;
-- show hidden and filter by store
if StoreId > 0 then
SET @sql = CONCAT(@sql, '
AND (p.LimitedToStores = 0 OR EXISTS (
SELECT 1 FROM `StoreMapping` sm
WHERE `sm`.EntityId = p.Id AND `sm`.EntityName = ''Product'' and `sm`.StoreId=',StoreId, '
))');
end if;
-- filter by specs
IF @SpecAttributesCount > 0 THEN
SET @sql = CONCAT(@sql, '
AND NOT EXISTS (
SELECT 1
FROM
FilteredSpecs_TempTable `fs`
WHERE
`fs`.SpecificationAttributeOptionId NOT IN (
SELECT psam.SpecificationAttributeOptionId
FROM Product_SpecificationAttribute_Mapping psam
WHERE psam.AllowFiltering = 1 AND psam.ProductId = p.Id
)
)');
END IF;
-- sorting
SET @sql_orderby = '';
IF OrderBy = 5 THEN /* Name: A to Z */
SET @sql_orderby = ' p.`Name` ASC';
ELSEIF OrderBy = 6 THEN /* Name: Z to A */
SET @sql_orderby = ' p.`Name` DESC';
ELSEIF OrderBy = 10 THEN /* Price: Low to High */
SET @sql_orderby = ' p.`Price` ASC';
ELSEIF OrderBy = 11 THEN /* Price: High to Low */
SET @sql_orderby = ' p.`Price` DESC';
ELSEIF OrderBy = 15 THEN /* creation date */
SET @sql_orderby = ' p.`CreatedOnUtc` DESC';
ELSE /* default sorting, 0 (position) */
-- category position (display order)
IF @CategoryIdsCount > 0 THEN
SET @sql_orderby = ' pcm.DisplayOrder ASC';
END IF;
-- manufacturer position (display order)
IF ManufacturerId > 0 THEN
IF LENGTH(@sql_orderby) > 0 THEN
SET @sql_orderby = CONCAT(@sql_orderby, ', ');
END IF;
SET @sql_orderby = CONCAT(@sql_orderby, ' pmm.DisplayOrder ASC');
END IF;
-- parent grouped product specified (sort associated products)
if ParentGroupedProductId > 0 then
if length(@sql_orderby) > 0 then
SET @sql_orderby = CONCAT(@sql_orderby, ', ');
end if;
SET @sql_orderby = CONCAT(@sql_orderby, ' p.`DisplayOrder` ASC');
end if;
-- name
IF LENGTH(@sql_orderby) > 0 THEN
SET @sql_orderby = CONCAT(@sql_orderby, ', ');
END IF;
SET @sql_orderby = CONCAT(@sql_orderby, ' p.`Name` ASC');
END IF;
SET @sql = CONCAT(@sql, '
ORDER BY', @sql_orderby);
-- select @sql;
-- PRINT (@sql)
PREPARE stmt2 FROM @sql;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
EXECUTE stmt2;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DEALLOCATE PREPARE stmt2;
DROP Temporary TABLE FilteredCategoryIds_TempTable;
DROP Temporary TABLE FilteredSpecs_TempTable;
DROP Temporary TABLE FilteredCustomerRoleIds_TempTable;
DROP Temporary TABLE KeywordProducts_TempTable;
DROP Temporary TABLE if exists PageIndex_TempTable;
CREATE Temporary TABLE PageIndex_TempTable
(
IndexId int NOT NULL AUTO_INCREMENT,
ProductId int NOT NULL,
PRIMARY KEY (`IndexId`)
) ENGINE = MEMORY;
INSERT INTO PageIndex_TempTable (`ProductId`)
SELECT ProductId
FROM DisplayOrder_TempTable
GROUP BY ProductId
ORDER BY min(`Id`);
-- total records
SET TotalRecords = ROW_COUNT();
DROP temporary TABLE DisplayOrder_TempTable;
-- prepare filterable specification attribute option identifier (if requested)
IF LoadFilterableSpecificationAttributeOptionIds = 1 THEN
DROP Temporary TABLE if exists FilterableSpecs_TempTable;
CREATE Temporary TABLE FilterableSpecs_TempTable
(
SpecificationAttributeOptionId int NOT NULL
) ENGINE = MEMORY;
INSERT INTO FilterableSpecs_TempTable (`SpecificationAttributeOptionId`)
SELECT DISTINCT `psam`.SpecificationAttributeOptionId
FROM `Product_SpecificationAttribute_Mapping` `psam`
WHERE `psam`.`AllowFiltering` = 1
AND `psam`.`ProductId` IN (SELECT `pi`.ProductId FROM PageIndex_TempTable `pi`);
-- build comma separated list of filterable identifiers
SELECT GROUP_CONCAT(COALESCE(concat(FilterableSpecificationAttributeOptionIds, ',') , ''), SpecificationAttributeOptionId) into FilterableSpecificationAttributeOptionIds
FROM FilterableSpecs_TempTable;
DROP Temporary TABLE FilterableSpecs_TempTable;
END IF;
set @sql = concat('
SELECT
p.*
FROM
PageIndex_TempTable pi
INNER JOIN Product p on p.Id = pi.ProductId
WHERE
pi.IndexId > ', @PageLowerBound, ' AND
pi.IndexId < ', @PageUpperBound, '
ORDER BY
pi.IndexId
limit ', @RowsToReturn, ';');
PREPARE stmt3 FROM @sql;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
DROP Temporary TABLE PageIndex_TempTable;
END;
-- GO
CREATE PROCEDURE `ProductTagCountLoadAll`(
in StoreId int
)
begin
select pt.
