/// <summary>
/// Search products
/// </summary>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="categoryIds">Category identifiers</param>
/// <param name="manufacturerId">Manufacturer identifier; 0 to load all records</param>
/// <param name="storeId">Store identifier; 0 to load all records</param>
/// <param name="vendorId">Vendor identifier; 0 to load all records</param>
/// <param name="warehouseId">Warehouse identifier; 0 to load all records</param>
/// <param name="productType">Product type; 0 to load all records</param>
/// <param name="visibleIndividuallyOnly">A values indicating whether to load only products marked as "visible individually"; "false" to load all records; "true" to load "visible individually" only</param>
/// <param name="markedAsNewOnly">A values indicating whether to load only products marked as "new"; "false" to load all records; "true" to load "marked as new" only</param>
/// <param name="featuredProducts">A value indicating whether loaded products are marked as featured (relates only to categories and manufacturers). 0 to load featured products only, 1 to load not featured products only, null to load all products</param>
/// <param name="priceMin">Minimum price; null to load all records</param>
/// <param name="priceMax">Maximum price; null to load all records</param>
/// <param name="productTagId">Product tag identifier; 0 to load all records</param>
/// <param name="keywords">Keywords</param>
/// <param name="searchDescriptions">A value indicating whether to search by a specified "keyword" in product descriptions</param>
/// <param name="searchManufacturerPartNumber">A value indicating whether to search by a specified "keyword" in manufacturer part number</param>
/// <param name="searchSku">A value indicating whether to search by a specified "keyword" in product SKU</param>
/// <param name="searchProductTags">A value indicating whether to search by a specified "keyword" in product tags</param>
/// <param name="languageId">Language identifier (search for text searching)</param>
/// <param name="filteredSpecs">Filtered product specification identifiers</param>
/// <param name="orderBy">Order by</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <param name="overridePublished">
/// null - process "Published" property according to "showHidden" parameter
/// true - load only "Published" products
/// false - load only "Unpublished" products
/// </param>
/// <returns>Products</returns>
public virtual IPagedList <Product> SearchProducts(
int pageIndex = 0,
int pageSize = int.MaxValue,
IList <Guid> categoryIds = null,
Guid manufacturerId = default,
Guid storeId = default,
Guid vendorId = default,
Guid warehouseId = default,
ProductType?productType = null,
bool visibleIndividuallyOnly = false,
bool markedAsNewOnly = false,
bool?featuredProducts = null,
decimal?priceMin = null,
decimal?priceMax = null,
int productTagId = 0,
string keywords = null,
bool searchDescriptions = false,
bool searchManufacturerPartNumber = true,
bool searchSku = true,
bool searchProductTags = false,
Guid languageId = default,
IList <int> filteredSpecs = null,
ProductSortingEnum orderBy = ProductSortingEnum.Position,
bool showHidden = false,
bool?overridePublished = null)
{
return(SearchProducts(out var _, false,
pageIndex, pageSize, categoryIds, manufacturerId,
storeId, vendorId, warehouseId,
productType, visibleIndividuallyOnly, markedAsNewOnly, featuredProducts,
priceMin, priceMax, productTagId, keywords, searchDescriptions, searchManufacturerPartNumber, searchSku,
searchProductTags, languageId, filteredSpecs,
orderBy, showHidden, overridePublished));
}
///// <summary>
///// Search celebrities
///// </summary>
///// <param name="pageIndex">Page index</param>
///// <param name="pageSize">Page size</param>
///// <param name="celebrityTagId">Celebrity tag identifier; 0 to load all records</param>
///// <param name="keywords">Keywords</param>
///// <param name="searchCelebrityTags">A value indicating whether to search by a specified "keyword" in celebrity tags</param>
///// <param name="languageId">Language identifier (search for text searching)</param>
///// </param>
///// <returns>Celebrities</returns>
//public virtual IPagedList<Product> SearchCelebrities(
// int pageIndex = 0,
// int pageSize = int.MaxValue,
// int celebrityTagId = 0,
// string keywords = null,
// bool searchCelebrityTags = false,
// int languageId = 0,
// ProductSortingEnum orderBy = ProductSortingEnum.Position,
// bool showHidden = false
// )
//{
// return SearchCelebrities(pageIndex, pageSize, celebrityTagId, keywords, searchCelebrityTags, languageId, orderBy, showHidden);
//}
/// <summary>
/// Search celebrities
/// </summary>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="storeId">Store identifier; 0 to load all records</param>
/// <param name="celebrityTagId">Celebrity tag identifier; 0 to load all records</param>
/// <param name="searchCelebrityTags">A value indicating whether to search by a specified "keyword" in celebrity tags</param>
/// <param name="languageId">Language identifier (search for text searching)</param>
/// <param name="orderBy">Order by</param>
/// </param>
/// <returns>Celebrities</returns>
public virtual IPagedList <Celebrity> SearchCelebrities(
int pageIndex = 0,
int pageSize = int.MaxValue,
int storeId = 0,
int celebrityTagId = 0,
bool searchCelebrityTags = false,
int languageId = 0,
ProductSortingEnum orderBy = ProductSortingEnum.Position
)
{
//search by keyword
var searchLocalizedValue = false;
if (languageId > 0)
{
//ensure that we have at least two published languages
var totalPublishedLanguages = _languageService.GetAllLanguages().Count;
searchLocalizedValue = totalPublishedLanguages >= 2;
}
//some databases don't support int.MaxValue
if (pageSize == int.MaxValue)
{
pageSize = int.MaxValue - 1;
}
//prepare input parameters
var pStoreId = SqlParameterHelper.GetInt32Parameter("StoreId", !_catalogSettings.IgnoreStoreLimitations ? storeId : 0);
var pCelebrityTagId = SqlParameterHelper.GetInt32Parameter("CelebrityTagId", celebrityTagId);
var pSearchCelebrityTags = SqlParameterHelper.GetBooleanParameter("SearchCelebrityTags", searchCelebrityTags);
var pUseFullTextSearch = SqlParameterHelper.GetBooleanParameter("UseFullTextSearch", _commonSettings.UseFullTextSearch);
var pFullTextMode = SqlParameterHelper.GetInt32Parameter("FullTextMode", (int)_commonSettings.FullTextMode);
var pLanguageId = SqlParameterHelper.GetInt32Parameter("LanguageId", searchLocalizedValue ? languageId : 0);
var pOrderBy = SqlParameterHelper.GetInt32Parameter("OrderBy", (int)orderBy);
var pPageIndex = SqlParameterHelper.GetInt32Parameter("PageIndex", pageIndex);
var pPageSize = SqlParameterHelper.GetInt32Parameter("PageSize", pageSize);
var pTotalRecords = SqlParameterHelper.GetOutputInt32Parameter("TotalRecords");
//invoke stored procedure
var celebrities = _celebrityRepository.EntityFromSql("CelebrityLoadAllPaged",
pStoreId,
pCelebrityTagId,
pSearchCelebrityTags,
pUseFullTextSearch,
pFullTextMode,
pLanguageId,
pOrderBy,
pPageIndex,
pPageSize,
pTotalRecords).ToList();
//return celebrities
var totalRecords = pTotalRecords.Value != DBNull.Value ? Convert.ToInt32(pTotalRecords.Value) : 0;
return(new PagedList <Celebrity>(celebrities, pageIndex, pageSize, totalRecords));
}
public IPagedList<Advertisement> SearchAdvertisements(
int pageIndex = 0,
int pageSize = int.MaxValue,
int storeId = 0,
int vendorId = 0,
string keywords = null,
ProductSortingEnum orderBy = ProductSortingEnum.Position)
{
#region Search products
//products
var query = _advertisementRepository.Table;
query = query.Where(p => !p.Deleted);
if (storeId > 0)
{
query = query.Where(ad => ad.StoreId == storeId);
}
if (vendorId > 0)
{
query = query.Where(ad => ad.VendorId == vendorId);
}
if (orderBy == ProductSortingEnum.Position)
{
//otherwise sort by name
query = query.OrderBy(p => p.Name);
}
else if (orderBy == ProductSortingEnum.NameAsc)
{
//Name: A to Z
query = query.OrderBy(p => p.Name);
}
else if (orderBy == ProductSortingEnum.NameDesc)
{
//Name: Z to A
query = query.OrderByDescending(p => p.Name);
}
else if (orderBy == ProductSortingEnum.CreatedOn)
{
//creation date
query = query.OrderByDescending(p => p.CreatedOnUtc);
}
else
{
//actually this code is not reachable
query = query.OrderBy(p => p.Name);
}
#endregion
var products = new PagedList<Advertisement>(query, pageIndex, pageSize);
return products;
}
public IPagedList <Advertisement> SearchAdvertisements(
int pageIndex = 0,
int pageSize = int.MaxValue,
int storeId = 0,
int vendorId = 0,
string keywords = null,
ProductSortingEnum orderBy = ProductSortingEnum.Position)
{
#region Search products
//products
var query = _advertisementRepository.Table;
query = query.Where(p => !p.Deleted);
if (storeId > 0)
{
query = query.Where(ad => ad.StoreId == storeId);
}
if (vendorId > 0)
{
query = query.Where(ad => ad.VendorId == vendorId);
}
if (orderBy == ProductSortingEnum.Position)
{
//otherwise sort by name
query = query.OrderBy(p => p.Name);
}
else if (orderBy == ProductSortingEnum.NameAsc)
{
//Name: A to Z
query = query.OrderBy(p => p.Name);
}
else if (orderBy == ProductSortingEnum.NameDesc)
{
//Name: Z to A
query = query.OrderByDescending(p => p.Name);
}
else if (orderBy == ProductSortingEnum.CreatedOn)
{
//creation date
query = query.OrderByDescending(p => p.CreatedOnUtc);
}
else
{
//actually this code is not reachable
query = query.OrderBy(p => p.Name);
}
#endregion
var products = new PagedList <Advertisement>(query, pageIndex, pageSize);
return(products);
}
private void OrderBy_Click(object sender, EventArgs e)
{
StackPanel s = new StackPanel();
var Picker = new ListPicker();
Picker.SetValue(ListPicker.ItemCountThresholdProperty, 6);
var PickerItems = new string[] { ProductSortingEnum.CreatedOn.ToString(), ProductSortingEnum.NameAsc.ToString(),
ProductSortingEnum.NameDesc.ToString(), ProductSortingEnum.Position.ToString(), ProductSortingEnum.PriceAsc.ToString(), ProductSortingEnum.PriceDesc.ToString() };
Picker.ItemsSource = PickerItems;
s.Children.Add(Picker);
CustomMessageBox messageBox = new CustomMessageBox()
{
Caption = "Order By",
Message = "Select the order you want the products to be displayed",
LeftButtonContent = "Submit",
Content = s,
RightButtonContent = "Cancel"
};
messageBox.Show();
messageBox.Dismissed += async(s1, e1) => {
ProductSortingEnum Result = ProductSortingEnum.Position;
if (e1.Result.Equals(CustomMessageBoxResult.LeftButton))
{
foreach (ProductSortingEnum pse in Enum.GetValues(typeof(ProductSortingEnum)))
{
if (pse.ToString().Equals(Picker.SelectedItem.ToString()))
{
Result = pse;
break;
}
}
var Count = 0;
foreach (PivotItem pivot in CategoryPivot.Items)
{
var Listbox = Helper.FindFirstElementInVisualTree <ListBox>(pivot);
Listbox.Items.Clear();
if (Listbox.ItemTemplate.Equals(Application.Current.Resources["ProductCategoryTemplate"] as DataTemplate))
{
var Prods = await api.CategoryProductsSortedFiltered(Categories[Count].Id, true, false, Result, 0, 0);
foreach (ProductDTO p in Prods)
{
Listbox.Items.Add(new MainPage.ProductData {
Id = p.Id, Description = p.Description, Image = Helper.ConvertToBitmapImage(p.Image.First()), ProductName = p.Name, Value = p.Price.ToString("0.0#") + " " + Currency
});
}
}
Count++;
}
}
};
}
public static int GetCountBySearch(
int siteId = -1,
string zoneIds = null,
int publishStatus = -1,
int languageId = -1,
int manufactureId = -1,
int productType = -1,
decimal?priceMin = null,
decimal?priceMax = null,
int position = -1,
int showOption = -1,
string propertyCondition = null,
string productIds = null,
string keyword = null,
bool searchCode = false,
string stateIds = null,
ProductSortingEnum orderBy = ProductSortingEnum.Position)
{
return(DBProduct.GetCountBySearch(siteId, zoneIds, publishStatus, languageId, manufactureId, productType, priceMin, priceMax, position, showOption, propertyCondition, productIds, keyword, searchCode, stateIds, (int)orderBy));
}
public CatalogSearchQuery SortBy(ProductSortingEnum sort)
{
switch (sort)
{
case ProductSortingEnum.CreatedOnAsc:
case ProductSortingEnum.CreatedOn:
return(SortBy(SearchSort.ByDateTimeField("createdon", sort == ProductSortingEnum.CreatedOn)));
case ProductSortingEnum.NameAsc:
case ProductSortingEnum.NameDesc:
return(SortBy(SearchSort.ByStringField("name", sort == ProductSortingEnum.NameDesc)));
case ProductSortingEnum.PriceAsc:
case ProductSortingEnum.PriceDesc:
return(SortBy(SearchSort.ByDoubleField("price", sort == ProductSortingEnum.PriceDesc)));
case ProductSortingEnum.Relevance:
return(SortBy(SearchSort.ByRelevance()));
default:
return(this);
}
}
/// <summary>
/// Search products
/// </summary>
/// <param name="categoryId">Category identifier; 0 to load all records</param>
/// <param name="manufacturerId">Manufacturer identifier; 0 to load all records</param>
/// <param name="vendorId">Vendor identifier; 0 to load all records</param>//add by hz
/// <param name="featuredProducts">A value indicating whether loaded products are marked as featured (relates only to categories and manufacturers). 0 to load featured products only, 1 to load not featured products only, null to load all products</param>
/// <param name="priceMin">Minimum price; null to load all records</param>
/// <param name="priceMax">Maximum price; null to load all records</param>
/// <param name="productTagId">Product tag identifier; 0 to load all records</param>
/// <param name="keywords">Keywords</param>
/// <param name="searchDescriptions">A value indicating whether to search by a specified "keyword" in product descriptions</param>
/// <param name="searchProductTags">A value indicating whether to search by a specified "keyword" in product tags</param>
/// <param name="languageId">Language identifier</param>
/// <param name="filteredSpecs">Filtered product specification identifiers</param>
/// <param name="orderBy">Order by</param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="loadFilterableSpecificationAttributeOptionIds">A value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)</param>
/// <param name="filterableSpecificationAttributeOptionIds">The specification attribute option identifiers applied to loaded products (all pages)</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <returns>Product collection</returns>
public virtual IPagedList<Product> SearchProducts(int categoryId, int manufacturerId,
int vendorId, //add by hz
bool? featuredProducts,
decimal? priceMin, decimal? priceMax, int productTagId,
string keywords, bool searchDescriptions, bool searchProductTags, int languageId,
IList<int> filteredSpecs, ProductSortingEnum orderBy,
int pageIndex, int pageSize,
bool loadFilterableSpecificationAttributeOptionIds, out IList<int> filterableSpecificationAttributeOptionIds,
bool showHidden = false)
{
var categoryIds = new List<int>();
if (categoryId > 0)
categoryIds.Add(categoryId);
return SearchProducts(categoryIds, manufacturerId,
vendorId, //add by hz
featuredProducts,
priceMin, priceMax, productTagId,
keywords, searchDescriptions,searchProductTags, languageId,
filteredSpecs, orderBy, pageIndex, pageSize,
loadFilterableSpecificationAttributeOptionIds, out filterableSpecificationAttributeOptionIds,
showHidden);
}
/// <summary>
/// Gets all products
/// </summary>
/// <param name="categoryId">Category identifier</param>
/// <param name="manufacturerId">Manufacturer identifier</param>
/// <param name="productTagId">Product tag identifier</param>
/// <param name="featuredProducts">A value indicating whether loaded products are marked as featured (relates only to categories and manufacturers). 0 to load featured products only, 1 to load not featured products only, null to load all products</param>
/// <param name="priceMin">Minimum price</param>
/// <param name="priceMax">Maximum price</param>
/// <param name="keywords">Keywords</param>
/// <param name="searchDescriptions">A value indicating whether to search in descriptions</param>
/// <param name="pageSize">Page size</param>
/// <param name="pageIndex">Page index</param>
/// <param name="filteredSpecs">Filtered product specification identifiers</param>
/// <param name="languageId">Language identifier</param>
/// <param name="orderBy">Order by</param>
/// <param name="totalRecords">Total records</param>
/// <returns>Product collection</returns>
public List<Product> GetAllProducts(int categoryId,
int manufacturerId, int productTagId, bool? featuredProducts,
decimal? priceMin, decimal? priceMax,
string keywords, bool searchDescriptions, int pageSize,
int pageIndex, List<int> filteredSpecs, int languageId,
ProductSortingEnum orderBy, out int totalRecords)
{
return GetAllProducts(categoryId,
manufacturerId, productTagId, featuredProducts,
priceMin, priceMax, 0,
keywords, searchDescriptions, pageSize,
pageIndex, filteredSpecs, languageId,
orderBy, out totalRecords);
}
protected void BindData()
{
var category = CategoryManager.GetCategoryById(this.CategoryId);
//breadcrumb
rptrCategoryBreadcrumb.DataSource = CategoryManager.GetBreadCrumb(this.CategoryId);
rptrCategoryBreadcrumb.DataBind();
lDescription.Text = category.Description;
//subcategories
var subCategoryCollection = CategoryManager.GetAllCategories(this.CategoryId);
if (subCategoryCollection.Count > 0)
{
rptrSubCategories.DataSource = subCategoryCollection;
rptrSubCategories.DataBind();
}
else
{
rptrSubCategories.Visible = false;
}
//featured products
var featuredProducts = category.FeaturedProducts;
if (featuredProducts.Count > 0)
{
dlFeaturedProducts.DataSource = featuredProducts;
dlFeaturedProducts.DataBind();
}
else
{
pnlFeaturedProducts.Visible = false;
}
//price ranges
this.ctrlPriceRangeFilter.PriceRanges = category.PriceRanges;
//page size
int totalRecords = 0;
int pageSize = 10;
if (category.PageSize > 0)
{
pageSize = category.PageSize;
}
//price ranges
decimal?minPrice = null;
decimal?maxPrice = null;
decimal?minPriceConverted = null;
decimal?maxPriceConverted = null;
if (ctrlPriceRangeFilter.SelectedPriceRange != null)
{
minPrice = ctrlPriceRangeFilter.SelectedPriceRange.From;
if (minPrice.HasValue)
{
minPriceConverted = CurrencyManager.ConvertCurrency(minPrice.Value, NopContext.Current.WorkingCurrency, CurrencyManager.PrimaryStoreCurrency);
}
maxPrice = ctrlPriceRangeFilter.SelectedPriceRange.To;
if (maxPrice.HasValue)
{
maxPriceConverted = CurrencyManager.ConvertCurrency(maxPrice.Value, NopContext.Current.WorkingCurrency, CurrencyManager.PrimaryStoreCurrency);
}
}
//specification filter
var psoFilterOption = ctrlProductSpecificationFilter.GetAlreadyFilteredSpecOptionIds();
//sorting
ProductSortingEnum orderBy = ProductSortingEnum.Position;
if (SettingManager.GetSettingValueBoolean("Common.AllowProductSorting"))
{
CommonHelper.SelectListItem(this.ddlSorting, CommonHelper.QueryStringInt("orderby"));
orderBy = (ProductSortingEnum)Enum.ToObject(typeof(ProductSortingEnum), int.Parse(ddlSorting.SelectedItem.Value));
}
var productCollection = ProductManager.GetAllProducts(this.CategoryId,
0, 0, false, minPriceConverted, maxPriceConverted,
string.Empty, false, pageSize, this.CurrentPageIndex,
psoFilterOption, orderBy, out totalRecords);
if (productCollection.Count > 0)
{
this.catalogPager.PageSize = pageSize;
this.catalogPager.TotalRecords = totalRecords;
this.catalogPager.PageIndex = this.CurrentPageIndex;
this.lvCatalog.DataSource = productCollection;
this.lvCatalog.DataBind();
}
else
{
this.lvCatalog.Visible = false;
this.pnlSorting.Visible = false;
}
}
/// <summary>
/// Sorts the elements of a sequence in order according to a product sorting rule
/// </summary>
/// <param name="productsQuery">A sequence of products to order</param>
/// <param name="currentLanguage">Current language</param>
/// <param name="orderBy">Product sorting rule</param>
/// <param name="localizedPropertyRepository">Localized property repository</param>
/// <returns>An System.Linq.IOrderedQueryable`1 whose elements are sorted according to a rule.</returns>
/// <remarks>
/// If <paramref name="orderBy"/> is set to <c>Position</c> and passed <paramref name="productsQuery"/> is
/// ordered sorting rule will be skipped
/// </remarks>
public static IQueryable <Product> OrderBy(this IQueryable <Product> productsQuery, IRepository <LocalizedProperty> localizedPropertyRepository, Language currentLanguage, ProductSortingEnum orderBy)
{
if (orderBy == ProductSortingEnum.NameAsc || orderBy == ProductSortingEnum.NameDesc)
{
var currentLanguageId = currentLanguage.Id;
var query =
from product in productsQuery
join localizedProperty in localizedPropertyRepository.Table on new
{
product.Id,
languageId = currentLanguageId,
keyGroup = nameof(Product),
key = nameof(Product.Name)
}
equals new
{
Id = localizedProperty.EntityId,
languageId = localizedProperty.LanguageId,
keyGroup = localizedProperty.LocaleKeyGroup,
key = localizedProperty.LocaleKey
} into localizedProperties
from localizedProperty in localizedProperties.DefaultIfEmpty(new LocalizedProperty {
LocaleValue = product.Name
})
select new { localizedProperty, product };
if (orderBy == ProductSortingEnum.NameAsc)
{
productsQuery = from item in query
orderby item.localizedProperty.LocaleValue, item.product.Name
select item.product;
}
else
{
productsQuery = from item in query
orderby item.localizedProperty.LocaleValue descending, item.product.Name descending
select item.product;
}
return(productsQuery);
}
return(orderBy switch
{
ProductSortingEnum.PriceAsc => productsQuery.OrderBy(p => p.Price),
ProductSortingEnum.PriceDesc => productsQuery.OrderByDescending(p => p.Price),
ProductSortingEnum.CreatedOn => productsQuery.OrderByDescending(p => p.CreatedOnUtc),
ProductSortingEnum.Position when productsQuery is IOrderedQueryable => productsQuery,
_ => productsQuery.OrderBy(p => p.DisplayOrder).ThenBy(p => p.Id)
});
/// <summary>
/// Search products
/// </summary>
/// <param name="filterableSpecificationAttributeOptionIds">The specification attribute option identifiers applied to loaded products (all pages)</param>
/// <param name="loadFilterableSpecificationAttributeOptionIds">A value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)</param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="categoryIds">Category identifiers</param>
/// <param name="manufacturerId">Manufacturer identifier; 0 to load all records</param>
/// <param name="storeId">Store identifier; 0 to load all records</param>
/// <param name="vendorId">Vendor identifier; 0 to load all records</param>
/// <param name="warehouseId">Warehouse identifier; 0 to load all records</param>
/// <param name="productType">Product type; 0 to load all records</param>
/// <param name="visibleIndividuallyOnly">A values indicating whether to load only products marked as "visible individually"; "false" to load all records; "true" to load "visible individually" only</param>
/// <param name="markedAsNewOnly">A values indicating whether to load only products marked as "new"; "false" to load all records; "true" to load "marked as new" only</param>
/// <param name="featuredProducts">A value indicating whether loaded products are marked as featured (relates only to categories and manufacturers). 0 to load featured products only, 1 to load not featured products only, null to load all products</param>
/// <param name="priceMin">Minimum price; null to load all records</param>
/// <param name="priceMax">Maximum price; null to load all records</param>
/// <param name="productTagId">Product tag identifier; 0 to load all records</param>
/// <param name="keywords">Keywords</param>
/// <param name="searchDescriptions">A value indicating whether to search by a specified "keyword" in product descriptions</param>
/// <param name="searchManufacturerPartNumber">A value indicating whether to search by a specified "keyword" in manufacturer part number</param>
/// <param name="searchSku">A value indicating whether to search by a specified "keyword" in product SKU</param>
/// <param name="searchProductTags">A value indicating whether to search by a specified "keyword" in product tags</param>
/// <param name="languageId">Language identifier (search for text searching)</param>
/// <param name="filteredSpecs">Filtered product specification identifiers</param>
/// <param name="orderBy">Order by</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <param name="overridePublished">
/// null - process "Published" property according to "showHidden" parameter
/// true - load only "Published" products
/// false - load only "Unpublished" products
/// </param>
/// <returns>Products</returns>
public override IPagedList <Product> SearchProducts(
out IList <int> filterableSpecificationAttributeOptionIds,
bool loadFilterableSpecificationAttributeOptionIds = false,
int pageIndex = 0,
int pageSize = int.MaxValue,
IList <int> categoryIds = null,
int manufacturerId = 0,
int storeId = 0,
int vendorId = 0,
int warehouseId = 0,
ProductType?productType = null,
bool visibleIndividuallyOnly = false,
bool markedAsNewOnly = false,
bool?featuredProducts = null,
decimal?priceMin = null,
decimal?priceMax = null,
int productTagId = 0,
string keywords = null,
bool searchDescriptions = false,
bool searchManufacturerPartNumber = true,
bool searchSku = true,
bool searchProductTags = false,
int languageId = 0,
IList <int> filteredSpecs = null,
ProductSortingEnum orderBy = ProductSortingEnum.Position,
bool showHidden = false,
bool?overridePublished = null)
{
filterableSpecificationAttributeOptionIds = new List <int>();
//validate "categoryIds" parameter
if (categoryIds != null && categoryIds.Contains(0))
{
categoryIds.Remove(0);
}
//Access control list. Allowed customer roles
var allowedCustomerRolesIds = _workContext.CurrentCustomer.GetCustomerRoleIds();
//pass category identifiers as comma-delimited string
var commaSeparatedCategoryIds = categoryIds == null ? string.Empty : string.Join(",", categoryIds);
//pass customer role identifiers as comma-delimited string
var commaSeparatedAllowedCustomerRoleIds = string.Join(",", allowedCustomerRolesIds);
//some databases don't support int.MaxValue
if (pageSize == int.MaxValue)
{
pageSize = int.MaxValue - 1;
}
var query = new StringBuilder();
query.AppendLine(@"
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT p.*
FROM Product p");
var filter = new StringBuilder();
filter.AppendLine(" WHERE p.Deleted = 0");
if (!string.IsNullOrEmpty(keywords))
{
filter.AppendLine($"AND (p.Name LIKE '%{keywords}%'");
if (searchSku)
{
filter.Append($" OR p.Sku = '{keywords}'");
}
filter.Append(")");
}
if (categoryIds != null && categoryIds.Any())
{
query.AppendLine("INNER JOIN Product_Category_Mapping pcm ON p.Id = pcm.ProductId");
filter.AppendLine($"AND pcm.CategoryId IN ({commaSeparatedCategoryIds})");
if (featuredProducts.HasValue)
{
filter.AppendLine($"AND pcm.IsFeaturedProduct = {(featuredProducts.Value ? 1 : 0)}");
}
}
if (manufacturerId > 0)
{
query.AppendLine("INNER JOIN Product_Manufacturer_Mapping pmm ON p.Id = pmm.ProductId");
filter.AppendLine($"AND pmm.ManufacturerId = {manufacturerId}");
if (featuredProducts.HasValue)
{
filter.AppendLine($"AND pmm.IsFeaturedProduct = {(featuredProducts.Value ? 1 : 0)}");
}
}
if (vendorId > 0)
{
filter.AppendLine($"AND p.VendorId = {vendorId}");
}
if (warehouseId > 0)
{
filter.AppendLine($@"AND
(
(p.UseMultipleWarehouses = 0 AND p.WarehouseId = {warehouseId})
OR
(p.UseMultipleWarehouses > 0 AND EXISTS (SELECT 1 FROM ProductWarehouseInventory pwi
WHERE pwi.WarehouseId = {warehouseId} AND pwi.ProductId = p.Id))
)" );
}
if (productType != null)
{
filter.AppendLine($"AND p.ProductTypeId = {(int)productType}");
}
if (visibleIndividuallyOnly)
{
filter.AppendLine("AND p.VisibleIndividually = 1");
}
if (markedAsNewOnly)
{
filter.AppendLine(@"AND p.MarkAsNew = 1
AND (UTC_TIMESTAMP() BETWEEN IFNULL(p.MarkAsNewStartDateTimeUtc, '1900-1-1') and IFNULL(p.MarkAsNewEndDateTimeUtc, '2999-1-1'))" );
}
if (productTagId != 0)
{
query.AppendLine("INNER JOIN Product_ProductTag_Mapping pptm ON p.Id = pptm.Product_Id");
filter.AppendLine($"AND pptm.ProductTag_Id = {productTagId}");
}
if (overridePublished == null && !showHidden)
{
filter.AppendLine("AND p.Published = 1");
}
else if (overridePublished.HasValue && overridePublished.Value)
{
filter.AppendLine("AND p.Published = 1");
}
else if (overridePublished.HasValue && !overridePublished.Value)
{
filter.AppendLine("AND p.Published = 0");
}
if (!showHidden)
{
filter.AppendLine(@"AND p.Deleted = 0
AND(UTC_TIMESTAMP() BETWEEN IFNULL(p.AvailableStartDateTimeUtc, '1900-1-1') and IFNULL(p.AvailableEndDateTimeUtc, '2999-1-1'))");
}
if (priceMin != null)
{
filter.AppendLine($"AND p.Price >= {priceMin}");
}
if (priceMax != null)
{
filter.AppendLine($"AND p.Price <= {priceMax}");
}
if (!_catalogSettings.IgnoreAcl && !showHidden && allowedCustomerRolesIds != null && allowedCustomerRolesIds.Any())
{
filter.AppendLine($@"AND (p.SubjectToAcl = 0 OR
EXISTS (
SELECT 1
FROM AclRecord acl
WHERE acl.EntityId = p.Id AND acl.EntityName = 'Product'
AND acl.CustomerRoleId IN ({commaSeparatedAllowedCustomerRoleIds})
))" );
}
if (!_catalogSettings.IgnoreStoreLimitations && storeId > 0)
{
filter.AppendLine($@"AND (p.LimitedToStores = 0 OR EXISTS (
SELECT 1 FROM StoreMapping sm
WHERE sm.EntityId = p.Id AND sm.EntityName = 'Product' and sm.StoreId={storeId}
))" );
}
var orderBySql = string.Empty;
switch (orderBy)
{
case ProductSortingEnum.NameAsc:
orderBySql = " p.Name ASC";
break;
case ProductSortingEnum.NameDesc:
orderBySql = " p.Name DESC";
break;
case ProductSortingEnum.PriceAsc:
orderBySql = " p.Price ASC";
break;
case ProductSortingEnum.PriceDesc:
orderBySql = " p.Price ASC";
break;
case ProductSortingEnum.CreatedOn:
orderBySql = " p.CreatedOnUtc DESC";
break;
default:
if (categoryIds?.Count > 0)
{
orderBySql = " pcm.DisplayOrder ASC";
}
if (manufacturerId > 0)
{
if (!string.IsNullOrEmpty(orderBySql))
{
orderBySql = orderBySql + ", ";
}
orderBySql = orderBySql + " pmm.DisplayOrder ASC";
}
if (!string.IsNullOrEmpty(orderBySql))
{
orderBySql = orderBySql + ", ";
}
orderBySql = orderBySql + " p.Name ASC";
break;
}
filter.AppendLine($"ORDER BY {orderBySql}");
query.Append(filter);
query.Append(";");
query.AppendLine("COMMIT ;");
var queryable = _dbContext.EntityFromSql <Product>(query.ToString()).AsNoTracking();
var totalRecords = queryable.Count();
var products = queryable.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
//return products
return(new PagedList <Product>(products, pageIndex, pageSize, totalRecords));
}
/// <summary>
/// Search products
/// </summary>
/// <param name="filterableSpecificationAttributeOptionIds">The specification attribute option identifiers applied to loaded products (all pages)</param>
/// <param name="loadFilterableSpecificationAttributeOptionIds">A value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)</param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="categoryIds">Category identifiers</param>
/// <param name="manufacturerId">Manufacturer identifier; 0 to load all records</param>
/// <param name="storeId">Store identifier; 0 to load all records</param>
/// <param name="vendorId">Vendor identifier; 0 to load all records</param>
/// <param name="warehouseId">Warehouse identifier; 0 to load all records</param>
/// <param name="productType">Product type; 0 to load all records</param>
/// <param name="visibleIndividuallyOnly">A values indicating whether to load only products marked as "visible individually"; "false" to load all records; "true" to load "visible individually" only</param>
/// <param name="markedAsNewOnly">A values indicating whether to load only products marked as "new"; "false" to load all records; "true" to load "marked as new" only</param>
/// <param name="featuredProducts">A value indicating whether loaded products are marked as featured (relates only to categories and manufacturers). 0 to load featured products only, 1 to load not featured products only, null to load all products</param>
/// <param name="priceMin">Minimum price; null to load all records</param>
/// <param name="priceMax">Maximum price; null to load all records</param>
/// <param name="productTagId">Product tag identifier; 0 to load all records</param>
/// <param name="keywords">Keywords</param>
/// <param name="searchDescriptions">A value indicating whether to search by a specified "keyword" in product descriptions</param>
/// <param name="searchManufacturerPartNumber">A value indicating whether to search by a specified "keyword" in manufacturer part number</param>
/// <param name="searchSku">A value indicating whether to search by a specified "keyword" in product SKU</param>
/// <param name="searchProductTags">A value indicating whether to search by a specified "keyword" in product tags</param>
/// <param name="languageId">Language identifier (search for text searching)</param>
/// <param name="filteredSpecs">Filtered product specification identifiers</param>
/// <param name="orderBy">Order by</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <param name="overridePublished">
/// null - process "Published" property according to "showHidden" parameter
/// true - load only "Published" products
/// false - load only "Unpublished" products
/// </param>
/// <returns>Products</returns>
public virtual IPagedList <Product> SearchProducts(
out IList <int> filterableSpecificationAttributeOptionIds,
bool loadFilterableSpecificationAttributeOptionIds = false,
int pageIndex = 0,
int pageSize = int.MaxValue,
IList <Guid> categoryIds = null,
Guid manufacturerId = default,
Guid storeId = default,
Guid vendorId = default,
Guid warehouseId = default,
ProductType?productType = null,
bool visibleIndividuallyOnly = false,
bool markedAsNewOnly = false,
bool?featuredProducts = null,
decimal?priceMin = null,
decimal?priceMax = null,
int productTagId = 0,
string keywords = null,
bool searchDescriptions = false,
bool searchManufacturerPartNumber = true,
bool searchSku = true,
bool searchProductTags = false,
Guid languageId = default,
IList <int> filteredSpecs = null,
ProductSortingEnum orderBy = ProductSortingEnum.Position,
bool showHidden = false,
bool?overridePublished = null)
{
filterableSpecificationAttributeOptionIds = new List <int>();
//validate "categoryIds" parameter
if (categoryIds != null && categoryIds.Contains(Guid.Empty))
{
categoryIds.Remove(Guid.Empty);
}
//pass category identifiers as comma-delimited string
var commaSeparatedCategoryIds = categoryIds == null ? string.Empty : string.Join(",", categoryIds);
//pass specification identifiers as comma-delimited string
var commaSeparatedSpecIds = string.Empty;
if (filteredSpecs != null)
{
((List <int>)filteredSpecs).Sort();
commaSeparatedSpecIds = string.Join(",", filteredSpecs);
}
//some databases don't support int.MaxValue
if (pageSize == int.MaxValue)
{
pageSize = int.MaxValue - 1;
}
//prepare input parameters
var pCategoryIds = SqlParameterHelper.GetStringParameter("CategoryIds", commaSeparatedCategoryIds);
var pManufacturerId = SqlParameterHelper.GetGuidParameter("ManufacturerId", manufacturerId);
var pStoreId = SqlParameterHelper.GetGuidParameter("StoreId", storeId);
var pVendorId = SqlParameterHelper.GetGuidParameter("VendorId", vendorId);
var pWarehouseId = SqlParameterHelper.GetGuidParameter("WarehouseId", warehouseId);
var pProductTypeId = SqlParameterHelper.GetInt32Parameter("ProductTypeId", (int?)productType);
var pVisibleIndividuallyOnly = SqlParameterHelper.GetBooleanParameter("VisibleIndividuallyOnly", visibleIndividuallyOnly);
var pMarkedAsNewOnly = SqlParameterHelper.GetBooleanParameter("MarkedAsNewOnly", markedAsNewOnly);
var pProductTagId = SqlParameterHelper.GetInt32Parameter("ProductTagId", productTagId);
var pFeaturedProducts = SqlParameterHelper.GetBooleanParameter("FeaturedProducts", featuredProducts);
var pPriceMin = SqlParameterHelper.GetDecimalParameter("PriceMin", priceMin);
var pPriceMax = SqlParameterHelper.GetDecimalParameter("PriceMax", priceMax);
var pKeywords = SqlParameterHelper.GetStringParameter("Keywords", keywords);
var pSearchDescriptions = SqlParameterHelper.GetBooleanParameter("SearchDescriptions", searchDescriptions);
var pSearchManufacturerPartNumber = SqlParameterHelper.GetBooleanParameter("SearchManufacturerPartNumber", searchManufacturerPartNumber);
var pSearchSku = SqlParameterHelper.GetBooleanParameter("SearchSku", searchSku);
var pSearchProductTags = SqlParameterHelper.GetBooleanParameter("SearchProductTags", searchProductTags);
var pUseFullTextSearch = SqlParameterHelper.GetBooleanParameter("UseFullTextSearch", _commonSettings.UseFullTextSearch);
var pFullTextMode = SqlParameterHelper.GetInt32Parameter("FullTextMode", (int)_commonSettings.FullTextMode);
var pFilteredSpecs = SqlParameterHelper.GetStringParameter("FilteredSpecs", commaSeparatedSpecIds);
var pLanguageId = SqlParameterHelper.GetGuidParameter("LanguageId", languageId);
var pOrderBy = SqlParameterHelper.GetInt32Parameter("OrderBy", (int)orderBy);
var pAllowedCustomerRoleIds = SqlParameterHelper.GetStringParameter("AllowedCustomerRoleIds", string.Empty);
var pPageIndex = SqlParameterHelper.GetInt32Parameter("PageIndex", pageIndex);
var pPageSize = SqlParameterHelper.GetInt32Parameter("PageSize", pageSize);
var pShowHidden = SqlParameterHelper.GetBooleanParameter("ShowHidden", showHidden);
var pOverridePublished = SqlParameterHelper.GetBooleanParameter("OverridePublished", overridePublished);
var pLoadFilterableSpecificationAttributeOptionIds = SqlParameterHelper.GetBooleanParameter("LoadFilterableSpecificationAttributeOptionIds", loadFilterableSpecificationAttributeOptionIds);
//prepare output parameters
var pFilterableSpecificationAttributeOptionIds = SqlParameterHelper.GetOutputStringParameter("FilterableSpecificationAttributeOptionIds");
pFilterableSpecificationAttributeOptionIds.Size = int.MaxValue - 1;
var pTotalRecords = SqlParameterHelper.GetOutputInt32Parameter("TotalRecords");
//invoke stored procedure
var products = _productRepository.EntityFromSql("ProductLoadAllPaged",
pCategoryIds,
pManufacturerId,
pStoreId,
pVendorId,
pWarehouseId,
pProductTypeId,
pVisibleIndividuallyOnly,
pMarkedAsNewOnly,
pProductTagId,
pFeaturedProducts,
pPriceMin,
pPriceMax,
pKeywords,
pSearchDescriptions,
pSearchManufacturerPartNumber,
pSearchSku,
pSearchProductTags,
pUseFullTextSearch,
pFullTextMode,
pFilteredSpecs,
pLanguageId,
pOrderBy,
pAllowedCustomerRoleIds,
pPageIndex,
pPageSize,
pShowHidden,
pOverridePublished,
pLoadFilterableSpecificationAttributeOptionIds,
pFilterableSpecificationAttributeOptionIds,
pTotalRecords).ToList();
//get filterable specification attribute option identifier
var filterableSpecificationAttributeOptionIdsStr =
pFilterableSpecificationAttributeOptionIds.Value != DBNull.Value
? (string)pFilterableSpecificationAttributeOptionIds.Value
: string.Empty;
if (loadFilterableSpecificationAttributeOptionIds &&
!string.IsNullOrWhiteSpace(filterableSpecificationAttributeOptionIdsStr))
{
filterableSpecificationAttributeOptionIds = filterableSpecificationAttributeOptionIdsStr
.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
.Select(x => Convert.ToInt32(x.Trim()))
.ToList();
}
//return products
var totalRecords = pTotalRecords.Value != DBNull.Value ? Convert.ToInt32(pTotalRecords.Value) : 0;
return(new PagedList <Product>(products, pageIndex, pageSize, totalRecords));
}
/// <summary>
/// Search products
/// </summary>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="categoryIds">Category identifiers</param>
/// <param name="manufacturerId">Manufacturer identifier; 0 to load all records</param>
/// <param name="storeId">Store identifier; 0 to load all records</param>
/// <param name="vendorId">Vendor identifier; 0 to load all records</param>
/// <param name="featuredProducts">A value indicating whether loaded products are marked as featured (relates only to categories and manufacturers). 0 to load featured products only, 1 to load not featured products only, null to load all products</param>
/// <param name="priceMin">Minimum price; null to load all records</param>
/// <param name="priceMax">Maximum price; null to load all records</param>
/// <param name="productTagId">Product tag identifier; 0 to load all records</param>
/// <param name="keywords">Keywords</param>
/// <param name="searchDescriptions">A value indicating whether to search by a specified "keyword" in product descriptions</param>
/// <param name="searchProductTags">A value indicating whether to search by a specified "keyword" in product tags</param>
/// <param name="languageId">Language identifier (search for text searching)</param>
/// <param name="filteredSpecs">Filtered product specification identifiers</param>
/// <param name="orderBy">Order by</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <returns>Products</returns>
public virtual IPagedList<Product> SearchProducts(
int pageIndex = 0,
int pageSize = 2147483647, //Int32.MaxValue
IList<int> categoryIds = null,
int manufacturerId = 0,
int storeId = 0,
int vendorId = 0,
bool? featuredProducts = null,
decimal? priceMin = null,
decimal? priceMax = null,
int productTagId = 0,
string keywords = null,
bool searchDescriptions = false,
bool searchProductTags = false,
int languageId = 0,
IList<int> filteredSpecs = null,
ProductSortingEnum orderBy = ProductSortingEnum.Position,
bool showHidden = false)
{
IList<int> filterableSpecificationAttributeOptionIds = null;
return SearchProducts(out filterableSpecificationAttributeOptionIds, false,
pageIndex, pageSize, categoryIds, manufacturerId, storeId, vendorId, featuredProducts,
priceMin, priceMax, productTagId, keywords, searchDescriptions,
searchProductTags, languageId, filteredSpecs, orderBy, showHidden);
}
/// <summary>
/// Sorts the elements of a sequence in order according to a product sorting rule
/// </summary>
/// <param name="productsQuery">A sequence of products to order</param>
/// <param name="orderBy">Product sorting rule</param>
/// <returns>An System.Linq.IOrderedQueryable`1 whose elements are sorted according to a rule.</returns>
/// <remarks>
/// If <paramref name="orderBy"/> is set to <c>Position</c> and passed <paramref name="productsQuery"/> is
/// ordered sorting rule will be skipped
/// </remarks>
public static IQueryable <Product> OrderBy(this IQueryable <Product> productsQuery, ProductSortingEnum orderBy)
{
return(orderBy switch
{
ProductSortingEnum.NameAsc => productsQuery.OrderBy(p => p.Name),
ProductSortingEnum.NameDesc => productsQuery.OrderByDescending(p => p.Name),
ProductSortingEnum.PriceAsc => productsQuery.OrderBy(p => p.Price),
ProductSortingEnum.PriceDesc => productsQuery.OrderByDescending(p => p.Price),
ProductSortingEnum.CreatedOn => productsQuery.OrderByDescending(p => p.CreatedOnUtc),
ProductSortingEnum.Position when productsQuery is IOrderedQueryable => productsQuery,
_ => productsQuery.OrderBy(p => p.DisplayOrder).ThenBy(p => p.Id)
});
/// <summary>
/// Search products
/// </summary>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="categoryIds">Category identifiers</param>
/// <param name="manufacturerId">Manufacturer identifier; 0 to load all records</param>
/// <param name="storeId">Store identifier; 0 to load all records</param>
/// <param name="vendorId">Vendor identifier; 0 to load all records</param>
/// <param name="warehouseId">Warehouse identifier; 0 to load all records</param>
/// <param name="productType">Product type; 0 to load all records</param>
/// <param name="visibleIndividuallyOnly">A values indicating whether to load only products marked as "visible individually"; "false" to load all records; "true" to load "visible individually" only</param>
/// <param name="markedAsNewOnly">A values indicating whether to load only products marked as "new"; "false" to load all records; "true" to load "marked as new" only</param>
/// <param name="featuredProducts">A value indicating whether loaded products are marked as featured (relates only to categories and manufacturers). 0 to load featured products only, 1 to load not featured products only, null to load all products</param>
/// <param name="priceMin">Minimum price; null to load all records</param>
/// <param name="priceMax">Maximum price; null to load all records</param>
/// <param name="productTagId">Product tag identifier; 0 to load all records</param>
/// <param name="keywords">Keywords</param>
/// <param name="searchDescriptions">A value indicating whether to search by a specified "keyword" in product descriptions</param>
/// <param name="searchManufacturerPartNumber">A value indicating whether to search by a specified "keyword" in manufacturer part number</param>
/// <param name="searchSku">A value indicating whether to search by a specified "keyword" in product SKU</param>
/// <param name="searchProductTags">A value indicating whether to search by a specified "keyword" in product tags</param>
/// <param name="languageId">Language identifier (search for text searching)</param>
/// <param name="filteredSpecs">Filtered product specification identifiers</param>
/// <param name="orderBy">Order by</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <param name="overridePublished">
/// null - process "Published" property according to "showHidden" parameter
/// true - load only "Published" products
/// false - load only "Unpublished" products
/// </param>
/// <returns>Products</returns>
public virtual IPagedList<Product> SearchProducts(
int pageIndex = 0,
int pageSize = int.MaxValue,
IList<int> categoryIds = null,
int manufacturerId = 0,
int storeId = 0,
int vendorId = 0,
int warehouseId = 0,
ProductType? productType = null,
bool visibleIndividuallyOnly = false,
bool markedAsNewOnly = false,
bool? featuredProducts = null,
decimal? priceMin = null,
decimal? priceMax = null,
int productTagId = 0,
string keywords = null,
bool searchDescriptions = false,
bool searchManufacturerPartNumber = true,
bool searchSku = true,
bool searchProductTags = false,
int languageId = 0,
IList<int> filteredSpecs = null,
ProductSortingEnum orderBy = ProductSortingEnum.Position,
bool showHidden = false,
bool? overridePublished = null)
{
IList<int> filterableSpecificationAttributeOptionIds;
return SearchProducts(out filterableSpecificationAttributeOptionIds, false,
pageIndex, pageSize, categoryIds, manufacturerId,
storeId, vendorId, warehouseId,
productType, visibleIndividuallyOnly, markedAsNewOnly, featuredProducts,
priceMin, priceMax, productTagId, keywords, searchDescriptions, searchManufacturerPartNumber, searchSku,
searchProductTags, languageId, filteredSpecs,
orderBy, showHidden, overridePublished);
}
/// <summary>
/// Search products
/// </summary>
/// <param name="filterableSpecificationAttributeOptionIds">The specification attribute option identifiers applied to loaded products (all pages)</param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="categoryIds">Category identifiers</param>
/// <param name="manufacturerId">Manufacturer identifier; 0 to load all records</param>
/// <param name="RoleType">会员类型</param>
/// <param name="isAgreeActive">是否同意参与活动</param>
/// <param name="featuredProducts">A value indicating whether loaded products are marked as featured (relates only to categories and manufacturers). 0 to load featured products only, 1 to load not featured products only, null to load all products</param>
/// <param name="priceMin">Minimum price; null to load all records</param>
/// <param name="priceMax">Maximum price; null to load all records</param>
/// <param name="keywords">Keywords</param>
/// <param name="filteredSpecs">Filtered product specification identifiers</param>
/// <param name="orderBy">Order by</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <returns>Products</returns>
public virtual IPagedList <Product> SearchProducts(
out IList <int> filterableSpecificationAttributeOptionIds,
bool loadFilterableSpecificationAttributeOptionIds,
int pageIndex = 0,
int pageSize = int.MaxValue,
IList <int> categoryIds = null,
IList <int> manufacturerIds = null,
int carId = 0,
int RoleId = 0,
bool album = false,
bool isAgreeActive = false,
bool?featuredProducts = null,
decimal?priceMin = null,
decimal?priceMax = null,
string keywords = null,
IList <int> filteredSpecs = null,
ProductSortingEnum orderBy = ProductSortingEnum.Position,
DbGeography location = null,
bool showHidden = false)
{
filterableSpecificationAttributeOptionIds = new List <int>();
//validate "categoryIds" parameter
if (categoryIds != null && categoryIds.Contains(0))
{
categoryIds.Remove(0);
}
//stored procedures aren't supported. Use LINQ
#region Search products
//products
var query = _ProductRepository.Table;
query = query.Where(p => !p.Deleted);
//只有审核通过的才可以显示
if (!showHidden)
{
query = query.Where(p => p.Published);
}
if (isAgreeActive)
{
query = query.Where(p => p.isAgreeActive);
}
if (album)
{
query = query.Where(p => p.IsVipAlbum);
}
//The function 'CurrentUtcDateTime' is not supported by SQL Server Compact.
if (priceMin.HasValue)
{
bool searchVipPrice = RoleId >= (int)RoleType.Member;
//min price
query = query.Where(p =>
//特价之后要实现的
//special price (specified price and valid date range)
//((p.SpecialPrice.HasValue &&
// ((!p.SpecialPriceStartDateTimeUtc.HasValue ||
// p.SpecialPriceStartDateTimeUtc.Value < nowUtc) &&
// (!p.SpecialPriceEndDateTimeUtc.HasValue ||
// p.SpecialPriceEndDateTimeUtc.Value > nowUtc))) &&
// (p.SpecialPrice >= priceMin.Value))
//||
//regular price (price isn't specified or date range isn't valid)
(p.Price >= priceMin.Value) || (searchVipPrice && p.VipPrice >= priceMin.Value));
}
if (priceMax.HasValue)
{
bool searchVipPrice = RoleId >= (int)RoleType.Member;
//max price
query = query.Where(p =>
//special price (specified price and valid date range)
//regular price (price isn't specified or date range isn't valid)
(p.Price <= priceMax.Value) || (searchVipPrice && p.VipPrice <= priceMax));
}
//searching by keyword
if (!String.IsNullOrWhiteSpace(keywords))
{
query = query.Where(p => p.Name.Contains(keywords));
}
//category filtering
if (categoryIds != null && categoryIds.Any())
{
query = query.Where(p => categoryIds.Contains(p.CategoryId));
}
if (isAgreeActive)
{
query = query.Where(p => p.isAgreeActive);
}
if (manufacturerIds != null && manufacturerIds.Any())
{
query = query.Where(p => manufacturerIds.Contains(p.ManufacturerId));
}
if (carId > 0)
{
query = from p in query
from pm in p.ProductCarCate.Where(pc => pc.CarCateId == carId || p.IsMatchAllCar)
select p;
}
if (loadFilterableSpecificationAttributeOptionIds)
{
var querySpecs = from p in query
join psa in _ProductSpecificationAttributeRepository.Table on p.Id equals psa.ProductId
select psa.SpecificationAttributeOptionId;
//only distinct attributes
filterableSpecificationAttributeOptionIds = querySpecs.Distinct().ToList();
}
//search by specs
if (filteredSpecs != null && filteredSpecs.Any())
{
var filteredAttributes = _SpecificationAttributeOptionRepository.Table
.Where(sao => filteredSpecs.Contains(sao.Id)).Select(sao => sao.SpecificationAttributeId).Distinct();
query = query.Where(p => !filteredAttributes.Except
(
_SpecificationAttributeOptionRepository.Table.Where(
sao => p.ProductSpecificationAttributes.Where(
psa => filteredSpecs.Contains(psa.SpecificationAttributeOptionId))
.Select(psa => psa.SpecificationAttributeOptionId).Contains(sao.Id))
.Select(sao => sao.SpecificationAttributeId).Distinct()
).Any());
}
//only distinct products (group by ID)
//if we use standard Distinct() method, then all fields will be compared (low performance)
//it'll not work in SQL Server Compact when searching products by a keyword)
query = from p in query
group p by p.Id
into pGroup
orderby pGroup.Key
select pGroup.FirstOrDefault();
if (location != null)
{
var tempQuery = from p in query
join d in (from t in
(from s in _StorageRepository.Table
join psq in _ProductStorageQuantityRepository.Table on s.Id equals psq.StorageId
select new { s, psq })
group t by t.psq.ProductId into g
select new
{
ProductId = g.Key,
distance = g.Min(z => z.s.Location.Distance(location))
}) on p.Id equals d.ProductId
select new
{
p,
d.distance
};
var count = tempQuery.Count();
var source = tempQuery.OrderBy(x => x.distance)
.Skip(pageIndex * pageSize).Take(pageSize).ToList();
var products = source.Select(x => x.p).ToList();;
foreach (var sr in source)
{
var product = products.First(x => x.Id == sr.p.Id);
product.Distance = sr.distance;
}
var result = new PagedList <Product>(products, pageIndex, pageSize, count);
return(result);
}
else
{
//sort products
if (orderBy == ProductSortingEnum.Position && categoryIds != null && categoryIds.Any())
{
//category position
var firstCategoryId = categoryIds[0];
query = query.OrderBy(p => p.Category.DisplayOrder);
}
else if (orderBy == ProductSortingEnum.Position && manufacturerIds != null && manufacturerIds.Any())
{
//manufacturer position
var firstCategoryId = manufacturerIds[0];
query = query.OrderBy(p => p.Manufacturer.DisplayOrder);
}
else if (orderBy == ProductSortingEnum.Position)
{
//otherwise sort by name
query = query.OrderBy(p => p.Name);
}
else if (orderBy == ProductSortingEnum.NameAsc)
{
//Name: A to Z
query = query.OrderBy(p => p.Name);
}
else if (orderBy == ProductSortingEnum.NameDesc)
{
//Name: Z to A
query = query.OrderByDescending(p => p.Name);
}
else if (orderBy == ProductSortingEnum.PriceAsc)
{
//Price: Low to High
query = query.OrderBy(p => p.Price);
}
else if (orderBy == ProductSortingEnum.PriceDesc)
{
//Price: High to Low
query = query.OrderByDescending(p => p.Price);
}
else if (orderBy == ProductSortingEnum.CreatedOn)
{
//creation date
query = query.OrderByDescending(p => p.CreateTime);
}
else
{
//actually this code is not reachable
query = query.OrderBy(p => p.Name);
}
var products = new PagedList <Product>(query, pageIndex, pageSize);
//return products
return(products);
}
#endregion
}
/// <summary>
/// Search products
/// </summary>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="categoryIds">Category identifiers</param>
/// <param name="storeId">Store identifier; 0 to load all records</param>
/// <param name="vendorId">Vendor identifier; 0 to load all records</param>
/// <param name="productType">Product type; 0 to load all records</param>
/// <param name="visibleIndividuallyOnly">A values indicating whether to load only products marked as "visible individually"; "false" to load all records; "true" to load "visible individually" only</param>
/// <param name="markedAsNewOnly">A values indicating whether to load only products marked as "new"; "false" to load all records; "true" to load "marked as new" only</param>
/// <param name="featuredProducts">A value indicating whether loaded products are marked as featured (relates only to categories). 0 to load featured products only, 1 to load not featured products only, null to load all products</param>
/// <param name="productTagId">Product tag identifier; 0 to load all records</param>
/// <param name="keywords">Keywords</param>
/// <param name="searchDescriptions">A value indicating whether to search by a specified "keyword" in product descriptions</param>
/// <param name="searchProductTags">A value indicating whether to search by a specified "keyword" in product tags</param>
/// <param name="languageId">Language identifier (search for text searching)</param>
/// <param name="filteredSpecs">Filtered product specification identifiers</param>
/// <param name="orderBy">Order by</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <param name="overridePublished">
/// null - process "Published" property according to "showHidden" parameter
/// true - load only "Published" products
/// false - load only "Unpublished" products
/// </param>
/// <returns>Products</returns>
public virtual IPagedList <Product> SearchProducts(
int pageIndex = 0,
int pageSize = int.MaxValue,
IList <int> categoryIds = null,
int storeId = 0,
int vendorId = 0,
ProductType?productType = null,
bool visibleIndividuallyOnly = false,
bool markedAsNewOnly = false,
bool?featuredProducts = null,
int productTagId = 0,
string keywords = null,
bool searchDescriptions = false,
bool searchProductTags = false,
int languageId = 0,
ProductSortingEnum orderBy = ProductSortingEnum.Position,
bool showHidden = false,
bool?overridePublished = null)
{
//search by keyword
var searchLocalizedValue = false;
if (languageId > 0)
{
if (showHidden)
{
searchLocalizedValue = true;
}
else
{
//ensure that we have at least two published languages
var totalPublishedLanguages = _languageService.GetAllLanguages().Count;
searchLocalizedValue = totalPublishedLanguages >= 2;
}
}
//validate "categoryIds" parameter
if (categoryIds != null && categoryIds.Contains(0))
{
categoryIds.Remove(0);
}
//Access control list. Allowed customer roles
var allowedCustomerRolesIds = _workContext.CurrentCustomer.GetCustomerRoleIds();
//pass category identifiers as comma-delimited string
var commaSeparatedCategoryIds = categoryIds == null ? string.Empty : string.Join(",", categoryIds);
//pass customer role identifiers as comma-delimited string
var commaSeparatedAllowedCustomerRoleIds = string.Join(",", allowedCustomerRolesIds);
//some databases don't support int.MaxValue
if (pageSize == int.MaxValue)
{
pageSize = int.MaxValue - 1;
}
//prepare input parameters
var pCategoryIds = _dataProvider.GetStringParameter("CategoryIds", commaSeparatedCategoryIds);
var pStoreId = _dataProvider.GetInt32Parameter("StoreId", !_catalogSettings.IgnoreStoreLimitations ? storeId : 0);
var pVendorId = _dataProvider.GetInt32Parameter("VendorId", vendorId);
var pProductTypeId = _dataProvider.GetInt32Parameter("ProductTypeId", (int?)productType);
var pVisibleIndividuallyOnly = _dataProvider.GetBooleanParameter("VisibleIndividuallyOnly", visibleIndividuallyOnly);
var pMarkedAsNewOnly = _dataProvider.GetBooleanParameter("MarkedAsNewOnly", markedAsNewOnly);
var pProductTagId = _dataProvider.GetInt32Parameter("ProductTagId", productTagId);
var pFeaturedProducts = _dataProvider.GetBooleanParameter("FeaturedProducts", featuredProducts);
var pKeywords = _dataProvider.GetStringParameter("Keywords", keywords);
var pSearchDescriptions = _dataProvider.GetBooleanParameter("SearchDescriptions", searchDescriptions);
var pSearchProductTags = _dataProvider.GetBooleanParameter("SearchProductTags", searchProductTags);
var pUseFullTextSearch = _dataProvider.GetBooleanParameter("UseFullTextSearch", _commonSettings.UseFullTextSearch);
var pFullTextMode = _dataProvider.GetInt32Parameter("FullTextMode", (int)_commonSettings.FullTextMode);
var pLanguageId = _dataProvider.GetInt32Parameter("LanguageId", searchLocalizedValue ? languageId : 0);
var pOrderBy = _dataProvider.GetInt32Parameter("OrderBy", (int)orderBy);
var pAllowedCustomerRoleIds = _dataProvider.GetStringParameter("AllowedCustomerRoleIds", !_catalogSettings.IgnoreAcl ? commaSeparatedAllowedCustomerRoleIds : string.Empty);
var pPageIndex = _dataProvider.GetInt32Parameter("PageIndex", pageIndex);
var pPageSize = _dataProvider.GetInt32Parameter("PageSize", pageSize);
var pShowHidden = _dataProvider.GetBooleanParameter("ShowHidden", showHidden);
var pOverridePublished = _dataProvider.GetBooleanParameter("OverridePublished", overridePublished);
//prepare output parameters
var pTotalRecords = _dataProvider.GetOutputInt32Parameter("TotalRecords");
//invoke stored procedure
var products = _dbContext.EntityFromSql <Product>("ProductLoadAllPaged",
pCategoryIds,
pStoreId,
pVendorId,
pProductTypeId,
pVisibleIndividuallyOnly,
pMarkedAsNewOnly,
pProductTagId,
pFeaturedProducts,
pKeywords,
pSearchDescriptions,
pSearchProductTags,
pUseFullTextSearch,
pFullTextMode,
pLanguageId,
pOrderBy,
pAllowedCustomerRoleIds,
pPageIndex,
pPageSize,
pShowHidden,
pOverridePublished,
pTotalRecords).ToList();
//return products
var totalRecords = pTotalRecords.Value != DBNull.Value ? Convert.ToInt32(pTotalRecords.Value) : 0;
return(new PagedList <Product>(products, pageIndex, pageSize, totalRecords));
}
/// <summary>
/// Search products
/// </summary>
/// <param name="categoryId">Category identifier; 0 to load all recordss</param>
/// <param name="manufacturerId">Manufacturer identifier; 0 to load all records</param>
/// <param name="featuredProducts">A value indicating whether loaded products are marked as featured (relates only to categories and manufacturers). 0 to load featured products only, 1 to load not featured products only, null to load all products</param>
/// <param name="priceMin">Minimum price; null to load all records</param>
/// <param name="priceMax">Maximum price; null to load all records</param>
/// <param name="productTagId">Product tag identifier; 0 to load all records</param>
/// <param name="keywords">Keywords</param>
/// <param name="searchDescriptions">A value indicating whether to search in descriptions</param>
/// <param name="languageId">Language identifier</param>
/// <param name="filteredSpecs">Filtered product specification identifiers</param>
/// <param name="orderBy">Order by</param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <returns>Product collection</returns>
public virtual IPagedList<Product> SearchProducts(int categoryId, int manufacturerId, bool? featuredProducts,
decimal? priceMin, decimal? priceMax, int productTagId,
string keywords, bool searchDescriptions, int languageId,
IList<int> filteredSpecs, ProductSortingEnum orderBy,
int pageIndex, int pageSize, bool showHidden = false)
{
bool searchLocalizedValue = false;
if (languageId > 0)
{
if (showHidden)
{
searchLocalizedValue = true;
}
else
{
//ensure that we have at least two published languages
var totalPublishedLanguages = _languageService.GetAllLanguages(false).Count;
searchLocalizedValue = totalPublishedLanguages >= 2;
}
}
if (_commonSettings.UseStoredProceduresIfSupported && _dataProvider.StoredProceduredSupported)
{
//stored procedures are enabled and supported by the database.
//It's much faster than the LINQ implementation below
//TODO We should not reference SqlParameter here. DAL should do it.
var pTotalRecords = new SqlParameter { ParameterName = "TotalRecords", Direction = ParameterDirection.Output, SqlDbType = SqlDbType.Int };
string commaSeparatedSpecIds = "";
if (filteredSpecs != null)
{
((List<int>)filteredSpecs).Sort();
for (int i = 0; i < filteredSpecs.Count; i++)
{
commaSeparatedSpecIds += filteredSpecs[i].ToString();
if (i != filteredSpecs.Count - 1)
{
commaSeparatedSpecIds += ",";
}
}
}
//some databases don't support int.MaxValue
if (pageSize == int.MaxValue)
pageSize = int.MaxValue - 1;
var products = _dbContext.ExecuteStoredProcedureList<Product>(
//"EXEC [ProductLoadAllPaged] @CategoryId, @ManufacturerId, @ProductTagId, @FeaturedProducts, @PriceMin, @PriceMax, @Keywords, @SearchDescriptions, @FilteredSpecs, @LanguageId, @OrderBy, @PageIndex, @PageSize, @ShowHidden, @TotalRecords",
"ProductLoadAllPaged",
new SqlParameter { ParameterName = "CategoryId", Value = categoryId, SqlDbType = SqlDbType.Int },
new SqlParameter { ParameterName = "ManufacturerId", Value = manufacturerId, SqlDbType = SqlDbType.Int },
new SqlParameter { ParameterName = "ProductTagId", Value = productTagId, SqlDbType = SqlDbType.Int },
new SqlParameter { ParameterName = "FeaturedProducts", Value = featuredProducts.HasValue ? (object)featuredProducts.Value : DBNull.Value, SqlDbType = SqlDbType.Bit },
new SqlParameter { ParameterName = "PriceMin", Value = priceMin.HasValue ? (object)priceMin.Value : DBNull.Value, SqlDbType = SqlDbType.Decimal },
new SqlParameter { ParameterName = "PriceMax", Value = priceMax.HasValue ? (object)priceMax.Value : DBNull.Value, SqlDbType = SqlDbType.Decimal },
new SqlParameter { ParameterName = "Keywords", Value = keywords != null ? (object)keywords : DBNull.Value, SqlDbType = SqlDbType.NVarChar },
new SqlParameter { ParameterName = "SearchDescriptions", Value = searchDescriptions, SqlDbType = SqlDbType.Bit },
new SqlParameter { ParameterName = "FilteredSpecs", Value = commaSeparatedSpecIds != null ? (object)commaSeparatedSpecIds : DBNull.Value, SqlDbType = SqlDbType.NVarChar },
new SqlParameter { ParameterName = "LanguageId", Value = searchLocalizedValue ? languageId : 0, SqlDbType = SqlDbType.Int },
new SqlParameter { ParameterName = "OrderBy", Value = (int)orderBy, SqlDbType = SqlDbType.Int },
new SqlParameter { ParameterName = "PageIndex", Value = pageIndex, SqlDbType = SqlDbType.Int },
new SqlParameter { ParameterName = "PageSize", Value = pageSize, SqlDbType = SqlDbType.Int },
new SqlParameter { ParameterName = "ShowHidden", Value = showHidden, SqlDbType = SqlDbType.Bit },
pTotalRecords);
int totalRecords = (pTotalRecords.Value != DBNull.Value) ? Convert.ToInt32(pTotalRecords.Value) : 0;
return new PagedList<Product>(products, pageIndex, pageSize, totalRecords);
}
else
{
//stored procedures aren't supported. Use LINQ
#region Search products
//products
var query = _productRepository.Table;
query = query.Where(p => !p.Deleted);
if (!showHidden)
{
query = query.Where(p => p.Published);
}
//searching by keyword
if (!String.IsNullOrWhiteSpace(keywords))
{
query = from p in query
join lp in _localizedPropertyRepository.Table on p.Id equals lp.EntityId into p_lp
from lp in p_lp.DefaultIfEmpty()
from pv in p.ProductVariants.DefaultIfEmpty()
where (p.Name.Contains(keywords)) ||
(searchDescriptions && p.ShortDescription.Contains(keywords)) ||
(searchDescriptions && p.FullDescription.Contains(keywords)) ||
(pv.Name.Contains(keywords)) ||
(searchDescriptions && pv.Description.Contains(keywords)) ||
//localized values
(searchLocalizedValue && lp.LanguageId == languageId && lp.LocaleKeyGroup == "Product" && lp.LocaleKey == "Name" && lp.LocaleValue.Contains(keywords)) ||
(searchDescriptions && searchLocalizedValue && lp.LanguageId == languageId && lp.LocaleKeyGroup == "Product" && lp.LocaleKey == "ShortDescription" && lp.LocaleValue.Contains(keywords)) ||
(searchDescriptions && searchLocalizedValue && lp.LanguageId == languageId && lp.LocaleKeyGroup == "Product" && lp.LocaleKey == "FullDescription" && lp.LocaleValue.Contains(keywords))
select p;
}
//product variants
//The function 'CurrentUtcDateTime' is not supported by SQL Server Compact.
//That's why we pass the date value
var nowUtc = DateTime.UtcNow;
query = from p in query
from pv in p.ProductVariants.DefaultIfEmpty()
where
//deleted
(showHidden || !pv.Deleted) &&
//published
(showHidden || pv.Published) &&
//price min
(
!priceMin.HasValue
||
//special price (specified price and valid date range)
((pv.SpecialPrice.HasValue && ((!pv.SpecialPriceStartDateTimeUtc.HasValue || pv.SpecialPriceStartDateTimeUtc.Value < nowUtc) && (!pv.SpecialPriceEndDateTimeUtc.HasValue || pv.SpecialPriceEndDateTimeUtc.Value > nowUtc))) && (pv.SpecialPrice >= priceMin.Value))
||
//regular price (price isn't specified or date range isn't valid)
((!pv.SpecialPrice.HasValue || ((pv.SpecialPriceStartDateTimeUtc.HasValue && pv.SpecialPriceStartDateTimeUtc.Value > nowUtc) || (pv.SpecialPriceEndDateTimeUtc.HasValue && pv.SpecialPriceEndDateTimeUtc.Value < nowUtc))) && (pv.Price >= priceMin.Value))
) &&
//price max
(
!priceMax.HasValue
||
//special price (specified price and valid date range)
((pv.SpecialPrice.HasValue && ((!pv.SpecialPriceStartDateTimeUtc.HasValue || pv.SpecialPriceStartDateTimeUtc.Value < nowUtc) && (!pv.SpecialPriceEndDateTimeUtc.HasValue || pv.SpecialPriceEndDateTimeUtc.Value > nowUtc))) && (pv.SpecialPrice <= priceMax.Value))
||
//regular price (price isn't specified or date range isn't valid)
((!pv.SpecialPrice.HasValue || ((pv.SpecialPriceStartDateTimeUtc.HasValue && pv.SpecialPriceStartDateTimeUtc.Value > nowUtc) || (pv.SpecialPriceEndDateTimeUtc.HasValue && pv.SpecialPriceEndDateTimeUtc.Value < nowUtc))) && (pv.Price <= priceMax.Value))
) &&
//available dates
(showHidden || (!pv.AvailableStartDateTimeUtc.HasValue || pv.AvailableStartDateTimeUtc.Value < nowUtc)) &&
(showHidden || (!pv.AvailableEndDateTimeUtc.HasValue || pv.AvailableEndDateTimeUtc.Value > nowUtc))
select p;
//search by specs
if (filteredSpecs != null && filteredSpecs.Count > 0)
{
query = from p in query
where !filteredSpecs
.Except(
p.ProductSpecificationAttributes.Where(psa => psa.AllowFiltering).Select(
psa => psa.SpecificationAttributeOptionId))
.Any()
select p;
}
//category filtering
if (categoryId > 0)
{
query = from p in query
from pc in p.ProductCategories.Where(pc => pc.CategoryId == categoryId)
where (!featuredProducts.HasValue || featuredProducts.Value == pc.IsFeaturedProduct)
select p;
}
//manufacturer filtering
if (manufacturerId > 0)
{
query = from p in query
from pm in p.ProductManufacturers.Where(pm => pm.ManufacturerId == manufacturerId)
where (!featuredProducts.HasValue || featuredProducts.Value == pm.IsFeaturedProduct)
select p;
}
//related products filtering
//if (relatedToProductId > 0)
//{
// query = from p in query
// join rp in _relatedProductRepository.Table on p.Id equals rp.ProductId2
// where (relatedToProductId == rp.ProductId1)
// select p;
//}
//tag filtering
if (productTagId > 0)
{
query = from p in query
from pt in p.ProductTags.Where(pt => pt.Id == productTagId)
select p;
}
//only distinct products (group by ID)
//if we use standard Distinct() method, then all fields will be compared (low performance)
//it'll not work in SQL Server Compact when searching products by a keyword)
query = from p in query
group p by p.Id
into pGroup
orderby pGroup.Key
select pGroup.FirstOrDefault();
//sort products
if (orderBy == ProductSortingEnum.Position && categoryId > 0)
{
//category position
query = query.OrderBy(p => p.ProductCategories.Where(pc => pc.CategoryId == categoryId).FirstOrDefault().DisplayOrder);
}
else if (orderBy == ProductSortingEnum.Position && manufacturerId > 0)
{
//manufacturer position
query =
query.OrderBy(p => p.ProductManufacturers.Where(pm => pm.ManufacturerId == manufacturerId).FirstOrDefault().DisplayOrder);
}
//else if (orderBy == ProductSortingEnum.Position && relatedToProductId > 0)
//{
// //sort by related product display order
// query = from p in query
// join rp in _relatedProductRepository.Table on p.Id equals rp.ProductId2
// where (relatedToProductId == rp.ProductId1)
// orderby rp.DisplayOrder
// select p;
//}
else if (orderBy == ProductSortingEnum.Position)
{
query = query.OrderBy(p => p.Name);
}
else if (orderBy == ProductSortingEnum.Name)
{
query = query.OrderBy(p => p.Name);
}
else if (orderBy == ProductSortingEnum.Price)
{
query = query.OrderBy(p => p.ProductVariants.FirstOrDefault().Price);
}
else if (orderBy == ProductSortingEnum.CreatedOn)
query = query.OrderByDescending(p => p.CreatedOnUtc);
else
query = query.OrderBy(p => p.Name);
var products = new PagedList<Product>(query, pageIndex, pageSize);
return products;
#endregion
}
}
/// <summary>
/// Search products
/// </summary>
/// <param name="filterableSpecificationAttributeOptionIds">The specification attribute option identifiers applied to loaded products (all pages)</param>
/// <param name="loadFilterableSpecificationAttributeOptionIds">A value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)</param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="categoryIds">Category identifiers</param>
/// <param name="manufacturerId">Manufacturer identifier; 0 to load all records</param>
/// <param name="storeId">Store identifier; 0 to load all records</param>
/// <param name="vendorId">Vendor identifier; 0 to load all records</param>
/// <param name="warehouseId">Warehouse identifier; 0 to load all records</param>
/// <param name="productType">Product type; 0 to load all records</param>
/// <param name="visibleIndividuallyOnly">A values indicating whether to load only products marked as "visible individually"; "false" to load all records; "true" to load "visible individually" only</param>
/// <param name="featuredProducts">A value indicating whether loaded products are marked as featured (relates only to categories and manufacturers). 0 to load featured products only, 1 to load not featured products only, null to load all products</param>
/// <param name="priceMin">Minimum price; null to load all records</param>
/// <param name="priceMax">Maximum price; null to load all records</param>
/// <param name="productTagId">Product tag identifier; 0 to load all records</param>
/// <param name="keywords">Keywords</param>
/// <param name="searchDescriptions">A value indicating whether to search by a specified "keyword" in product descriptions</param>
/// <param name="searchSku">A value indicating whether to search by a specified "keyword" in product SKU</param>
/// <param name="searchProductTags">A value indicating whether to search by a specified "keyword" in product tags</param>
/// <param name="languageId">Language identifier (search for text searching)</param>
/// <param name="filteredSpecs">Filtered product specification identifiers</param>
/// <param name="orderBy">Order by</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <param name="overridePublished">
/// null - process "Published" property according to "showHidden" parameter
/// true - load only "Published" products
/// false - load only "Unpublished" products
/// </param>
/// <returns>Products</returns>
public virtual IPagedList<Product> SearchProducts(
out IList<string> filterableSpecificationAttributeOptionIds,
bool loadFilterableSpecificationAttributeOptionIds = false,
int pageIndex = 0,
int pageSize = 2147483647, //Int32.MaxValue
IList<int> categoryIds = null,
int manufacturerId = 0,
int storeId = 0,
int vendorId = 0,
int warehouseId = 0,
ProductType? productType = null,
bool visibleIndividuallyOnly = false,
bool markedAsNewOnly = false,
bool? featuredProducts = null,
decimal? priceMin = null,
decimal? priceMax = null,
int productTagId = 0,
string keywords = null,
bool searchDescriptions = false,
bool searchSku = true,
bool searchProductTags = false,
int languageId = 0,
IList<string> filteredSpecs = null,
ProductSortingEnum orderBy = ProductSortingEnum.Position,
bool showHidden = false,
bool? overridePublished = null)
{
filterableSpecificationAttributeOptionIds = new List<string>();
featuredProducts = featuredProducts.HasValue ? featuredProducts : false;
//search by keyword
bool searchLocalizedValue = false;
if (languageId > 0)
{
if (showHidden)
{
searchLocalizedValue = true;
}
else
{
//ensure that we have at least two published languages
var totalPublishedLanguages = _languageService.GetAllLanguages().Count;
searchLocalizedValue = totalPublishedLanguages >= 2;
}
}
//validate "categoryIds" parameter
if (categoryIds !=null && categoryIds.Contains(0))
categoryIds.Remove(0);
//Access control list. Allowed customer roles
var allowedCustomerRolesIds = _workContext.CurrentCustomer.GetCustomerRoleIds();
//stored procedures aren't supported. Use LINQ
#region Search products
//products
var builder = Builders<Product>.Filter;
var filter = FilterDefinition<Product>.Empty;
//category filtering
if (categoryIds != null && categoryIds.Count > 0)
{
if (featuredProducts.Value)
{
int categoryId = categoryIds[0];
filter = filter & builder.Where(x => x.ProductCategories.Any(y => y.CategoryId == categoryId && y.IsFeaturedProduct));
}
else
{
filter = filter & builder.Where(x => x.ProductCategories.Any(y => categoryIds.Contains(y.CategoryId)));
}
}
//manufacturer filtering
if (manufacturerId > 0)
{
if (featuredProducts.Value)
{
filter = filter & builder.Where(x => x.ProductManufacturers.Any(y => y.ManufacturerId == manufacturerId && y.IsFeaturedProduct));
}
else
{
filter = filter & builder.Where(x => x.ProductManufacturers.Any(y => y.ManufacturerId == manufacturerId));
}
}
if (!overridePublished.HasValue)
{
//process according to "showHidden"
if (!showHidden)
{
filter = filter & builder.Where(p => p.Published);
}
}
else if (overridePublished.Value)
{
//published only
filter = filter & builder.Where(p => p.Published);
}
else if (!overridePublished.Value)
{
//unpublished only
filter = filter & builder.Where(p => !p.Published);
}
if (visibleIndividuallyOnly)
{
filter = filter & builder.Where(p => p.VisibleIndividually);
}
if (productType.HasValue)
{
var productTypeId = (int)productType.Value;
filter = filter & builder.Where(p => p.ProductTypeId == productTypeId);
}
//The function 'CurrentUtcDateTime' is not supported by SQL Server Compact.
//That's why we pass the date value
var nowUtc = DateTime.UtcNow;
if (priceMin.HasValue)
{
filter = filter & builder.Where(p =>
//special price (specified price and valid date range)
((p.SpecialPrice != null &&
((p.SpecialPriceStartDateTimeUtc == null ||
p.SpecialPriceStartDateTimeUtc < nowUtc) &&
(p.SpecialPriceEndDateTimeUtc == null ||
p.SpecialPriceEndDateTimeUtc > nowUtc))) &&
(p.SpecialPrice >= priceMin.Value))
||
//regular price (price isn't specified or date range isn't valid)
((p.SpecialPrice == null ||
((p.SpecialPriceStartDateTimeUtc != null &&
p.SpecialPriceStartDateTimeUtc > nowUtc) ||
(p.SpecialPriceEndDateTimeUtc != null &&
p.SpecialPriceEndDateTimeUtc < nowUtc))) &&
(p.Price >= priceMin.Value)));
}
if (priceMax.HasValue)
{
//max price
filter = filter & builder.Where(p =>
//special price (specified price and valid date range)
((p.SpecialPrice != null &&
((p.SpecialPriceStartDateTimeUtc == null ||
p.SpecialPriceStartDateTimeUtc < nowUtc) &&
(p.SpecialPriceEndDateTimeUtc == null ||
p.SpecialPriceEndDateTimeUtc > nowUtc))) &&
(p.SpecialPrice <= priceMax.Value))
||
//regular price (price isn't specified or date range isn't valid)
((p.SpecialPrice == null ||
((p.SpecialPriceStartDateTimeUtc != null &&
p.SpecialPriceStartDateTimeUtc > nowUtc) ||
(p.SpecialPriceEndDateTimeUtc != null &&
p.SpecialPriceEndDateTimeUtc < nowUtc))) &&
(p.Price <= priceMax.Value)));
}
if (!showHidden && !_catalogSettings.IgnoreFilterableAvailableStartEndDateTime)
{
filter = filter & builder.Where(p =>
(p.AvailableStartDateTimeUtc == null || p.AvailableStartDateTimeUtc < nowUtc) &&
(p.AvailableEndDateTimeUtc == null || p.AvailableEndDateTimeUtc > nowUtc));
}
if (markedAsNewOnly)
{
filter = filter & builder.Where(p => p.MarkAsNew);
filter = filter & builder.Where(p =>
(!p.MarkAsNewStartDateTimeUtc.HasValue || p.MarkAsNewStartDateTimeUtc.Value < nowUtc) &&
(!p.MarkAsNewEndDateTimeUtc.HasValue || p.MarkAsNewEndDateTimeUtc.Value > nowUtc));
}
//searching by keyword
if (!String.IsNullOrWhiteSpace(keywords))
{
if(_commonSettings.UseFullTextSearch)
{
keywords = "\"" + keywords + "\"";
keywords = keywords.Replace("+", "\" \"");
keywords = keywords.Replace(" ", "\" \"");
filter = filter & builder.Text(keywords);
}
else
{
if (!searchDescriptions)
filter = filter & builder.Where(p =>
p.Name.ToLower().Contains(keywords.ToLower())
||
p.Locales.Any(x => x.LocaleKey == "Name" && x.LocaleValue != null && x.LocaleValue.ToLower().Contains(keywords.ToLower()))
);
else
{
filter = filter & builder.Where(p =>
(p.Name != null && p.Name.ToLower().Contains(keywords.ToLower()))
||
(p.ShortDescription != null && p.ShortDescription.ToLower().Contains(keywords.ToLower()))
||
(p.FullDescription != null && p.FullDescription.ToLower().Contains(keywords.ToLower()))
||
(p.Locales.Any(x => x.LocaleValue != null && x.LocaleValue.ToLower().Contains(keywords.ToLower()))));
}
}
}
if (!showHidden && !_catalogSettings.IgnoreAcl)
{
filter = filter & (builder.AnyIn(x => x.CustomerRoles, allowedCustomerRolesIds) | builder.Where(x => !x.SubjectToAcl));
}
if (storeId > 0 && !_catalogSettings.IgnoreStoreLimitations)
{
filter = filter & builder.Where(x => x.Stores.Contains(storeId) || !x.LimitedToStores);
}
//search by specs
if (filteredSpecs != null && filteredSpecs.Count > 0)
{
foreach(var spec in filteredSpecs)
{
int _specId = Convert.ToInt32(spec.Split(':').FirstOrDefault().ToString());
int _specOptionId = Convert.ToInt32(spec.Split(':').Last().ToString());
filter = filter & builder.Where(x => x.ProductSpecificationAttributes.Any(y=>y.Id == _specId && y.SpecificationAttributeOptionId == _specOptionId));
}
}
//vendor filtering
if (vendorId > 0)
{
//query = query.Where(p => p.VendorId == vendorId);
filter = filter & builder.Where(x => x.VendorId == vendorId);
}
//warehouse filtering
if (warehouseId > 0)
{
var manageStockInventoryMethodId = (int)ManageInventoryMethod.ManageStock;
filter = filter & (builder.Where(x => x.ManageInventoryMethodId == manageStockInventoryMethodId && x.UseMultipleWarehouses && x.ProductWarehouseInventory.Any(y=>y.WarehouseId == warehouseId)) |
builder.Where(x => x.ManageInventoryMethodId != manageStockInventoryMethodId && !x.UseMultipleWarehouses && x.WarehouseId == warehouseId));
}
//tag filtering
if (productTagId > 0)
{
filter = filter & builder.Where(x => x.ProductTags.Any(y => y.Id == productTagId));
}
var builderSort = Builders<Product>.Sort.Descending(x=>x.CreatedOnUtc);
if (orderBy == ProductSortingEnum.Position && categoryIds != null && categoryIds.Count > 0)
{
//category position
builderSort = Builders<Product>.Sort.Ascending(x => x.DisplayOrderCategory);
}
else if (orderBy == ProductSortingEnum.Position && manufacturerId > 0)
{
//manufacturer position
builderSort = Builders<Product>.Sort.Ascending(x => x.DisplayOrderManufacturer);
}
else if (orderBy == ProductSortingEnum.Position)
{
//otherwise sort by name
builderSort = Builders<Product>.Sort.Ascending(x => x.Name);
}
else if (orderBy == ProductSortingEnum.NameAsc)
{
//Name: A to Z
builderSort = Builders<Product>.Sort.Ascending(x => x.Name);
}
else if (orderBy == ProductSortingEnum.NameDesc)
{
//Name: Z to A
builderSort = Builders<Product>.Sort.Descending(x => x.Name);
}
else if (orderBy == ProductSortingEnum.PriceAsc)
{
//Price: Low to High
builderSort = Builders<Product>.Sort.Ascending(x => x.Price);
}
else if (orderBy == ProductSortingEnum.PriceDesc)
{
//Price: High to Low
builderSort = Builders<Product>.Sort.Descending(x => x.Price);
}
else if (orderBy == ProductSortingEnum.CreatedOn)
{
//creation date
builderSort = Builders<Product>.Sort.Ascending(x => x.CreatedOnUtc);
}
else
{
//actually this code is not reachable
builderSort = Builders<Product>.Sort.Ascending(x => x.Id);
}
var products = new PagedList<Product>();
Task taskProduct = Task.Run(() =>
{
products = new PagedList<Product>(_productRepository.Collection, filter, builderSort, pageIndex, pageSize);
});
if (loadFilterableSpecificationAttributeOptionIds && !_catalogSettings.IgnoreFilterableSpecAttributeOption)
{
IList<string> specyfication = new List<string>();
Task t = Task.Run(() =>
{
var filterSpec = filter &
builder.Where(x => x.ProductSpecificationAttributes.Count > 0);
//builder.Where(x => x.ProductSpecificationAttributes.Any(y => y.AllowFiltering));
var taskCount = _productRepository.Collection.CountAsync(filterSpec);
taskCount.Wait();
if ((int)taskCount.Result > 0)
{
var qspec = _productRepository.Collection
.Aggregate()
.Match(filterSpec)
.Unwind(x => x.ProductSpecificationAttributes)
.Project(new BsonDocument
{
{"AllowFiltering", "$ProductSpecificationAttributes.AllowFiltering"},
{"SpecificationAttributeId", "$ProductSpecificationAttributes.SpecificationAttributeId"},
{"SpecificationAttributeOptionId", "$ProductSpecificationAttributes.SpecificationAttributeOptionId"}
})
.Match(new BsonDocument("AllowFiltering", true))
.Group(new BsonDocument
{
{"_id",
new BsonDocument {
{ "SpecificationAttributeId", "$SpecificationAttributeId" },
{ "SpecificationAttributeOptionId", "$SpecificationAttributeOptionId" },
}
},
{"count", new BsonDocument
{
{ "$sum" , 1}
}
}
})
.ToListAsync().Result;
foreach (var item in qspec)
{
var so = item["_id"]["SpecificationAttributeId"].ToString() + ":" +
item["_id"]["SpecificationAttributeOptionId"].ToString();
specyfication.Add(so);
}
}
});
t.Wait();
filterableSpecificationAttributeOptionIds = specyfication;
}
taskProduct.Wait();
//return products
return products;
#endregion
}
public Task <ProductDTO[]> CategoryProductsSortedFiltered(int CatId, bool Sorted, bool Filtered, ProductSortingEnum Sorting, decimal Max, decimal Min)
{
var tcs = new TaskCompletionSource <ProductDTO[]>();
client.CategoryProductsSortedFilteredCompleted += (sender, result) => tcs.TrySetResult(result.Result.ToArray());
client.CategoryProductsSortedFilteredAsync(CatId, Sorted, Filtered, Sorting, Max, Min);
return(tcs.Task);
}
public virtual IPagedList<Product> SearchProducts(
int pageIndex = 0,
int pageSize = int.MaxValue,
IList<int> categoryIds = null,
bool? featuredProducts = null,
decimal? priceMin = null,
decimal? priceMax = null,
string keywords = null,
bool searchDescriptions = false,
ProductSortingEnum orderBy = ProductSortingEnum.Position)
{
//products
var query = _productRepository.Table;
query = query.Where(p => !p.Deleted);
// price term
var nowUtc = DateTime.UtcNow;
if (priceMin.HasValue)
{
//min price
query = query.Where(p => p.Price >= priceMin.Value );
}
if (priceMax.HasValue)
{
//max price
query = query.Where(p => p.Price <= priceMax.Value);
}
//searching by keyword
if (!String.IsNullOrWhiteSpace(keywords))
{
query = from p in query
where (p.Name.Contains(keywords)) ||
(searchDescriptions && p.ShortDescription.Contains(keywords)) ||
(searchDescriptions && p.Description.Contains(keywords))
select p;
}
//category filtering
if (categoryIds != null && categoryIds.Count > 0)
{
query = from p in query
from pc in p.ProductCategories.Where(pc => categoryIds.Contains(pc.CategoryId))
where (!featuredProducts.HasValue || featuredProducts.Value == pc.IsFeaturedProduct)
select p;
}
//sort products
if (orderBy == ProductSortingEnum.Position && categoryIds != null && categoryIds.Count > 0)
{
//category position
var firstCategoryId = categoryIds[0];
query = query.OrderBy(p => p.ProductCategories.FirstOrDefault(pc => pc.CategoryId == firstCategoryId).ShowOrder);
}
else if (orderBy == ProductSortingEnum.Position)
{
//otherwise sort by name
query = query.OrderBy(p => p.Name);
}
else if (orderBy == ProductSortingEnum.NameAsc)
{
//Name: A to Z
query = query.OrderBy(p => p.Name);
}
else if (orderBy == ProductSortingEnum.NameDesc)
{
//Name: Z to A
query = query.OrderByDescending(p => p.Name);
}
else if (orderBy == ProductSortingEnum.PriceAsc)
{
//Price: Low to High
query = query.OrderBy(p => p.Price);
}
else if (orderBy == ProductSortingEnum.PriceDesc)
{
//Price: High to Low
query = query.OrderByDescending(p => p.Price);
}
else if (orderBy == ProductSortingEnum.CreatedOn)
{
//creation date
query = query.OrderByDescending(p => p.CreatedOnUtc);
}
else
{
//actually this code is not reachable
query = query.OrderBy(p => p.Name);
}
var products = new PagedList<Product>(query, pageIndex, pageSize);
return products;
}
public List <Product> GetAllProducts(int categoryId, int BrandId, int productTagId, bool?featuredProducts, decimal?priceMin, decimal?priceMax, int relatedToProductId, string keywords, bool searchDescriptions, int pageSize, int pageIndex, List <int> filteredSpecs, int languageId, ProductSortingEnum orderBy, out int totalRecords)
{
throw new NotImplementedException();
}
/// <summary>
/// Search products
/// </summary>
/// <param name="categoryIds">Category identifiers</param>
/// <param name="manufacturerId">Manufacturer identifier; 0 to load all records</param>
/// <param name="featuredProducts">A value indicating whether loaded products are marked as featured (relates only to categories and manufacturers). 0 to load featured products only, 1 to load not featured products only, null to load all products</param>
/// <param name="priceMin">Minimum price; null to load all records</param>
/// <param name="priceMax">Maximum price; null to load all records</param>
/// <param name="productTagId">Product tag identifier; 0 to load all records</param>
/// <param name="keywords">Keywords</param>
/// <param name="searchDescriptions">A value indicating whether to search in descriptions</param>
/// <param name="languageId">Language identifier</param>
/// <param name="filteredSpecs">Filtered product specification identifiers</param>
/// <param name="orderBy">Order by</param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="loadFilterableSpecificationAttributeOptionIds">A value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)</param>
/// <param name="filterableSpecificationAttributeOptionIds">The specification attribute option identifiers applied to loaded products (all pages)</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <returns>Product collection</returns>
public virtual IPagedList<Product> SearchProducts(IList<int> categoryIds,
int manufacturerId, bool? featuredProducts,
decimal? priceMin, decimal? priceMax, int productTagId,
string keywords, bool searchDescriptions, int languageId,
IList<int> filteredSpecs, ProductSortingEnum orderBy,
int pageIndex, int pageSize,
bool loadFilterableSpecificationAttributeOptionIds, out IList<int> filterableSpecificationAttributeOptionIds,
bool showHidden = false)
{
filterableSpecificationAttributeOptionIds = new List<int>();
bool searchLocalizedValue = false;
if (languageId > 0)
{
if (showHidden)
{
searchLocalizedValue = true;
}
else
{
//ensure that we have at least two published languages
var totalPublishedLanguages = _languageService.GetAllLanguages(false).Count;
searchLocalizedValue = totalPublishedLanguages >= 2;
}
}
if (_commonSettings.UseStoredProceduresIfSupported && _dataProvider.StoredProceduredSupported)
{
//stored procedures are enabled and supported by the database.
//It's much faster than the LINQ implementation below
#region Use stored procedure
//pass categry identifiers as comma-delimited string
string commaSeparatedCategoryIds = "";
if (categoryIds != null)
{
for (int i = 0; i < categoryIds.Count; i++)
{
commaSeparatedCategoryIds += categoryIds[i].ToString();
if (i != categoryIds.Count - 1)
{
commaSeparatedCategoryIds += ",";
}
}
}
//pass specification identifiers as comma-delimited string
string commaSeparatedSpecIds = "";
if (filteredSpecs != null)
{
((List<int>)filteredSpecs).Sort();
for (int i = 0; i < filteredSpecs.Count; i++)
{
commaSeparatedSpecIds += filteredSpecs[i].ToString();
if (i != filteredSpecs.Count - 1)
{
commaSeparatedSpecIds += ",";
}
}
}
//some databases don't support int.MaxValue
if (pageSize == int.MaxValue)
pageSize = int.MaxValue - 1;
//prepare parameters
var pCategoryIds = _dataProvider.GetParameter();
pCategoryIds.ParameterName = "CategoryIds";
pCategoryIds.Value = commaSeparatedCategoryIds != null ? (object)commaSeparatedCategoryIds : DBNull.Value;
pCategoryIds.DbType = DbType.String;
var pManufacturerId = _dataProvider.GetParameter();
pManufacturerId.ParameterName = "ManufacturerId";
pManufacturerId.Value = manufacturerId;
pManufacturerId.DbType = DbType.Int32;
var pProductTagId = _dataProvider.GetParameter();
pProductTagId.ParameterName = "ProductTagId";
pProductTagId.Value = productTagId;
pProductTagId.DbType = DbType.Int32;
var pFeaturedProducts = _dataProvider.GetParameter();
pFeaturedProducts.ParameterName = "FeaturedProducts";
pFeaturedProducts.Value = featuredProducts.HasValue ? (object)featuredProducts.Value : DBNull.Value;
pFeaturedProducts.DbType = DbType.Boolean;
var pPriceMin = _dataProvider.GetParameter();
pPriceMin.ParameterName = "PriceMin";
pPriceMin.Value = priceMin.HasValue ? (object)priceMin.Value : DBNull.Value;
pPriceMin.DbType = DbType.Decimal;
var pPriceMax = _dataProvider.GetParameter();
pPriceMax.ParameterName = "PriceMax";
pPriceMax.Value = priceMax.HasValue ? (object)priceMax.Value : DBNull.Value;
pPriceMax.DbType = DbType.Decimal;
var pKeywords = _dataProvider.GetParameter();
pKeywords.ParameterName = "Keywords";
pKeywords.Value = keywords != null ? (object)keywords : DBNull.Value;
pKeywords.DbType = DbType.String;
var pSearchDescriptions = _dataProvider.GetParameter();
pSearchDescriptions.ParameterName = "SearchDescriptions";
pSearchDescriptions.Value = searchDescriptions;
pSearchDescriptions.DbType = DbType.Boolean;
var pFilteredSpecs = _dataProvider.GetParameter();
pFilteredSpecs.ParameterName = "FilteredSpecs";
pFilteredSpecs.Value = commaSeparatedSpecIds != null ? (object)commaSeparatedSpecIds : DBNull.Value;
pFilteredSpecs.DbType = DbType.String;
var pLanguageId = _dataProvider.GetParameter();
pLanguageId.ParameterName = "LanguageId";
pLanguageId.Value = searchLocalizedValue ? languageId : 0;
pLanguageId.DbType = DbType.Int32;
var pOrderBy = _dataProvider.GetParameter();
pOrderBy.ParameterName = "OrderBy";
pOrderBy.Value = (int)orderBy;
pOrderBy.DbType = DbType.Int32;
var pPageIndex = _dataProvider.GetParameter();
pPageIndex.ParameterName = "PageIndex";
pPageIndex.Value = pageIndex;
pPageIndex.DbType = DbType.Int32;
var pPageSize = _dataProvider.GetParameter();
pPageSize.ParameterName = "PageSize";
pPageSize.Value = pageSize;
pPageSize.DbType = DbType.Int32;
var pShowHidden = _dataProvider.GetParameter();
pShowHidden.ParameterName = "ShowHidden";
pShowHidden.Value = showHidden;
pShowHidden.DbType = DbType.Boolean;
var pLoadFilterableSpecificationAttributeOptionIds = _dataProvider.GetParameter();
pLoadFilterableSpecificationAttributeOptionIds.ParameterName = "LoadFilterableSpecificationAttributeOptionIds";
pLoadFilterableSpecificationAttributeOptionIds.Value = loadFilterableSpecificationAttributeOptionIds;
pLoadFilterableSpecificationAttributeOptionIds.DbType = DbType.Boolean;
var pFilterableSpecificationAttributeOptionIds = _dataProvider.GetParameter();
pFilterableSpecificationAttributeOptionIds.ParameterName = "FilterableSpecificationAttributeOptionIds";
pFilterableSpecificationAttributeOptionIds.Direction = ParameterDirection.Output;
pFilterableSpecificationAttributeOptionIds.Size = 100;
pFilterableSpecificationAttributeOptionIds.DbType = DbType.String;
var pTotalRecords = _dataProvider.GetParameter();
pTotalRecords.ParameterName = "TotalRecords";
pTotalRecords.Direction = ParameterDirection.Output;
pTotalRecords.DbType = DbType.Int32;
//invoke stored procedure
var products = _dbContext.ExecuteStoredProcedureList<Product>(
//"EXEC [ProductLoadAllPaged] @CategoryId, @ManufacturerId, @ProductTagId, @FeaturedProducts, @PriceMin, @PriceMax, @Keywords, @SearchDescriptions, @FilteredSpecs, @LanguageId, @OrderBy, @PageIndex, @PageSize, @ShowHidden, @TotalRecords",
"ProductLoadAllPaged",
pCategoryIds,
pManufacturerId,
pProductTagId,
pFeaturedProducts,
pPriceMin,
pPriceMax,
pKeywords,
pSearchDescriptions,
pFilteredSpecs,
pLanguageId,
pOrderBy,
pPageIndex,
pPageSize,
pShowHidden,
pLoadFilterableSpecificationAttributeOptionIds,
pFilterableSpecificationAttributeOptionIds,
pTotalRecords);
//get filterable specification attribute option identifier
string filterableSpecificationAttributeOptionIdsStr = (pFilterableSpecificationAttributeOptionIds.Value != DBNull.Value) ? (string)pFilterableSpecificationAttributeOptionIds.Value : "";
if (loadFilterableSpecificationAttributeOptionIds &&
!string.IsNullOrWhiteSpace(filterableSpecificationAttributeOptionIdsStr))
{
filterableSpecificationAttributeOptionIds = filterableSpecificationAttributeOptionIdsStr
.Split(new[] {','}, StringSplitOptions.RemoveEmptyEntries)
.Select(x => Convert.ToInt32(x.Trim()))
.ToList();
}
//return products
int totalRecords = (pTotalRecords.Value != DBNull.Value) ? Convert.ToInt32(pTotalRecords.Value) : 0;
return new PagedList<Product>(products, pageIndex, pageSize, totalRecords);
#endregion
}
else
{
//stored procedures aren't supported. Use LINQ
#region Search products
//products
var query = _productRepository.Table;
query = query.Where(p => !p.Deleted);
if (!showHidden)
{
query = query.Where(p => p.Published);
}
//searching by keyword
if (!String.IsNullOrWhiteSpace(keywords))
{
query = from p in query
join lp in _localizedPropertyRepository.Table on p.Id equals lp.EntityId into p_lp
from lp in p_lp.DefaultIfEmpty()
from pv in p.ProductVariants.DefaultIfEmpty()
where (p.Name.Contains(keywords)) ||
(searchDescriptions && p.ShortDescription.Contains(keywords)) ||
(searchDescriptions && p.FullDescription.Contains(keywords)) ||
(pv.Name.Contains(keywords)) ||
(searchDescriptions && pv.Description.Contains(keywords)) ||
//localized values
(searchLocalizedValue && lp.LanguageId == languageId && lp.LocaleKeyGroup == "Product" && lp.LocaleKey == "Name" && lp.LocaleValue.Contains(keywords)) ||
(searchDescriptions && searchLocalizedValue && lp.LanguageId == languageId && lp.LocaleKeyGroup == "Product" && lp.LocaleKey == "ShortDescription" && lp.LocaleValue.Contains(keywords)) ||
(searchDescriptions && searchLocalizedValue && lp.LanguageId == languageId && lp.LocaleKeyGroup == "Product" && lp.LocaleKey == "FullDescription" && lp.LocaleValue.Contains(keywords))
select p;
}
//product variants
//The function 'CurrentUtcDateTime' is not supported by SQL Server Compact.
//That's why we pass the date value
var nowUtc = DateTime.UtcNow;
query = from p in query
from pv in p.ProductVariants.DefaultIfEmpty()
where
//deleted
(showHidden || !pv.Deleted) &&
//published
(showHidden || pv.Published) &&
//price min
(
!priceMin.HasValue
||
//special price (specified price and valid date range)
((pv.SpecialPrice.HasValue && ((!pv.SpecialPriceStartDateTimeUtc.HasValue || pv.SpecialPriceStartDateTimeUtc.Value < nowUtc) && (!pv.SpecialPriceEndDateTimeUtc.HasValue || pv.SpecialPriceEndDateTimeUtc.Value > nowUtc))) && (pv.SpecialPrice >= priceMin.Value))
||
//regular price (price isn't specified or date range isn't valid)
((!pv.SpecialPrice.HasValue || ((pv.SpecialPriceStartDateTimeUtc.HasValue && pv.SpecialPriceStartDateTimeUtc.Value > nowUtc) || (pv.SpecialPriceEndDateTimeUtc.HasValue && pv.SpecialPriceEndDateTimeUtc.Value < nowUtc))) && (pv.Price >= priceMin.Value))
) &&
//price max
(
!priceMax.HasValue
||
//special price (specified price and valid date range)
((pv.SpecialPrice.HasValue && ((!pv.SpecialPriceStartDateTimeUtc.HasValue || pv.SpecialPriceStartDateTimeUtc.Value < nowUtc) && (!pv.SpecialPriceEndDateTimeUtc.HasValue || pv.SpecialPriceEndDateTimeUtc.Value > nowUtc))) && (pv.SpecialPrice <= priceMax.Value))
||
//regular price (price isn't specified or date range isn't valid)
((!pv.SpecialPrice.HasValue || ((pv.SpecialPriceStartDateTimeUtc.HasValue && pv.SpecialPriceStartDateTimeUtc.Value > nowUtc) || (pv.SpecialPriceEndDateTimeUtc.HasValue && pv.SpecialPriceEndDateTimeUtc.Value < nowUtc))) && (pv.Price <= priceMax.Value))
) &&
//available dates
(showHidden || (!pv.AvailableStartDateTimeUtc.HasValue || pv.AvailableStartDateTimeUtc.Value < nowUtc)) &&
(showHidden || (!pv.AvailableEndDateTimeUtc.HasValue || pv.AvailableEndDateTimeUtc.Value > nowUtc))
select p;
//search by specs
if (filteredSpecs != null && filteredSpecs.Count > 0)
{
query = from p in query
where !filteredSpecs
.Except(
p.ProductSpecificationAttributes.Where(psa => psa.AllowFiltering).Select(
psa => psa.SpecificationAttributeOptionId))
.Any()
select p;
}
//category filtering
if (categoryIds != null && categoryIds.Count > 0)
{
//search in subcategories
query = from p in query
from pc in p.ProductCategories.Where(pc => categoryIds.Contains(pc.CategoryId))
where (!featuredProducts.HasValue || featuredProducts.Value == pc.IsFeaturedProduct)
select p;
}
//manufacturer filtering
if (manufacturerId > 0)
{
query = from p in query
from pm in p.ProductManufacturers.Where(pm => pm.ManufacturerId == manufacturerId)
where (!featuredProducts.HasValue || featuredProducts.Value == pm.IsFeaturedProduct)
select p;
}
//related products filtering
//if (relatedToProductId > 0)
//{
// query = from p in query
// join rp in _relatedProductRepository.Table on p.Id equals rp.ProductId2
// where (relatedToProductId == rp.ProductId1)
// select p;
//}
//tag filtering
if (productTagId > 0)
{
query = from p in query
from pt in p.ProductTags.Where(pt => pt.Id == productTagId)
select p;
}
//only distinct products (group by ID)
//if we use standard Distinct() method, then all fields will be compared (low performance)
//it'll not work in SQL Server Compact when searching products by a keyword)
query = from p in query
group p by p.Id
into pGroup
orderby pGroup.Key
select pGroup.FirstOrDefault();
//sort products
if (orderBy == ProductSortingEnum.Position && categoryIds != null && categoryIds.Count > 0)
{
//category position
var firstCategoryId = categoryIds[0];
query = query.OrderBy(p => p.ProductCategories.Where(pc => pc.CategoryId == firstCategoryId).FirstOrDefault().DisplayOrder);
}
else if (orderBy == ProductSortingEnum.Position && manufacturerId > 0)
{
//manufacturer position
query =
query.OrderBy(p => p.ProductManufacturers.Where(pm => pm.ManufacturerId == manufacturerId).FirstOrDefault().DisplayOrder);
}
//else if (orderBy == ProductSortingEnum.Position && relatedToProductId > 0)
//{
// //sort by related product display order
// query = from p in query
// join rp in _relatedProductRepository.Table on p.Id equals rp.ProductId2
// where (relatedToProductId == rp.ProductId1)
// orderby rp.DisplayOrder
// select p;
//}
else if (orderBy == ProductSortingEnum.Position)
{
//sort by name (there's no any position if category or manufactur is not specified)
query = query.OrderBy(p => p.Name);
}
else if (orderBy == ProductSortingEnum.NameAsc)
{
//Name: A to Z
query = query.OrderBy(p => p.Name);
}
else if (orderBy == ProductSortingEnum.NameDesc)
{
//Name: Z to A
query = query.OrderByDescending(p => p.Name);
}
else if (orderBy == ProductSortingEnum.PriceAsc)
{
//Price: Low to High
query = query.OrderBy(p => p.ProductVariants.FirstOrDefault().Price);
}
else if (orderBy == ProductSortingEnum.PriceDesc)
{
//Price: High to Low
query = query.OrderByDescending(p => p.ProductVariants.FirstOrDefault().Price);
}
else if (orderBy == ProductSortingEnum.CreatedOn)
{
//creation date
query = query.OrderByDescending(p => p.CreatedOnUtc);
}
else
{
//actually this code is not reachable
query = query.OrderBy(p => p.Name);
}
var products = new PagedList<Product>(query, pageIndex, pageSize);
//get filterable specification attribute option identifier
if (loadFilterableSpecificationAttributeOptionIds)
{
var querySpecs = from p in query
join psa in _productSpecificationAttributeRepository.Table on p.Id equals psa.ProductId
where psa.AllowFiltering
select psa.SpecificationAttributeOptionId;
//only distinct attributes
filterableSpecificationAttributeOptionIds = querySpecs
.Distinct()
.ToList();
}
//return products
return products;
#endregion
}
}
/// <summary>
/// Search products
/// </summary>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="categoryIds">Category identifiers</param>
/// <param name="storeId">Store identifier; 0 to load all records</param>
/// <param name="productType">Product type; 0 to load all records</param>
/// <param name="markedAsNewOnly">A values indicating whether to load only products marked as "new"; "false" to load all records; "true" to load "marked as new" only</param>
/// <param name="featuredProducts">A value indicating whether loaded products are marked as featured (relates only to categories). 0 to load featured products only, 1 to load not featured products only, null to load all products</param>
/// <param name="productTagId">Product tag identifier; 0 to load all records</param>
/// <param name="keywords">Keywords</param>
/// <param name="searchDescriptions">A value indicating whether to search by a specified "keyword" in product descriptions</param>
/// <param name="searchSku">A value indicating whether to search by a specified "keyword" in product SKU</param>
/// <param name="searchProductTags">A value indicating whether to search by a specified "keyword" in product tags</param>
/// <param name="languageId">Language identifier (search for text searching)</param>
/// <param name="orderBy">Order by</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <param name="overridePublished">
/// null - process "Published" property according to "showHidden" parameter
/// true - load only "Published" products
/// false - load only "Unpublished" products
/// </param>
/// <returns>Products</returns>
public virtual IPagedList <Product> SearchProducts(
int pageIndex = 0,
int pageSize = int.MaxValue,
IList <int> categoryIds = null,
int storeId = 0,
ProductType?productType = null,
bool visibleIndividuallyOnly = false,
bool markedAsNewOnly = false,
bool?featuredProducts = null,
int productTagId = 0,
string keywords = null,
bool searchDescriptions = false,
bool searchSku = true,
bool searchProductTags = false,
int languageId = 0,
ProductSortingEnum orderBy = ProductSortingEnum.Position,
bool showHidden = false,
bool?overridePublished = null)
{
//search by keyword
bool searchLocalizedValue = false;
if (languageId > 0)
{
if (showHidden)
{
searchLocalizedValue = true;
}
else
{
//ensure that we have at least two published languages
var totalPublishedLanguages = _languageService.GetAllLanguages().Count;
searchLocalizedValue = totalPublishedLanguages >= 2;
}
}
//validate "categoryIds" parameter
if (categoryIds != null && categoryIds.Contains(0))
{
categoryIds.Remove(0);
}
//Access control list. Allowed customer roles
var allowedCustomerRolesIds = _workContext.CurrentCustomer.GetCustomerRoleIds();
if (_commonSettings.UseStoredProceduresIfSupported && _dataProvider.StoredProceduredSupported)
{
//stored procedures are enabled and supported by the database.
//It's much faster than the LINQ implementation below
#region Use stored procedure
//pass category identifiers as comma-delimited string
string commaSeparatedCategoryIds = categoryIds == null ? "" : string.Join(",", categoryIds);
//pass customer role identifiers as comma-delimited string
string commaSeparatedAllowedCustomerRoleIds = string.Join(",", allowedCustomerRolesIds);
//some databases don't support int.MaxValue
if (pageSize == int.MaxValue)
{
pageSize = int.MaxValue - 1;
}
//prepare parameters
var pCategoryIds = _dataProvider.GetParameter();
pCategoryIds.ParameterName = "CategoryIds";
pCategoryIds.Value = commaSeparatedCategoryIds;
pCategoryIds.DbType = DbType.String;
var pStoreId = _dataProvider.GetParameter();
pStoreId.ParameterName = "StoreId";
pStoreId.Value = !_catalogSettings.IgnoreStoreLimitations ? storeId : 0;
pStoreId.DbType = DbType.Int32;
var pProductTypeId = _dataProvider.GetParameter();
pProductTypeId.ParameterName = "ProductTypeId";
pProductTypeId.Value = productType.HasValue ? (object)productType.Value : DBNull.Value;
pProductTypeId.DbType = DbType.Int32;
var pMarkedAsNewOnly = _dataProvider.GetParameter();
pMarkedAsNewOnly.ParameterName = "MarkedAsNewOnly";
pMarkedAsNewOnly.Value = markedAsNewOnly;
pMarkedAsNewOnly.DbType = DbType.Int32;
var pProductTagId = _dataProvider.GetParameter();
pProductTagId.ParameterName = "ProductTagId";
pProductTagId.Value = productTagId;
pProductTagId.DbType = DbType.Int32;
var pFeaturedProducts = _dataProvider.GetParameter();
pFeaturedProducts.ParameterName = "FeaturedProducts";
pFeaturedProducts.Value = featuredProducts.HasValue ? (object)featuredProducts.Value : DBNull.Value;
pFeaturedProducts.DbType = DbType.Boolean;
var pKeywords = _dataProvider.GetParameter();
pKeywords.ParameterName = "Keywords";
pKeywords.Value = keywords != null ? (object)keywords : DBNull.Value;
pKeywords.DbType = DbType.String;
var pSearchDescriptions = _dataProvider.GetParameter();
pSearchDescriptions.ParameterName = "SearchDescriptions";
pSearchDescriptions.Value = searchDescriptions;
pSearchDescriptions.DbType = DbType.Boolean;
var pSearchSku = _dataProvider.GetParameter();
pSearchSku.ParameterName = "SearchSku";
pSearchSku.Value = searchSku;
pSearchSku.DbType = DbType.Boolean;
var pSearchProductTags = _dataProvider.GetParameter();
pSearchProductTags.ParameterName = "SearchProductTags";
pSearchProductTags.Value = searchProductTags;
pSearchProductTags.DbType = DbType.Boolean;
var pUseFullTextSearch = _dataProvider.GetParameter();
pUseFullTextSearch.ParameterName = "UseFullTextSearch";
pUseFullTextSearch.Value = _commonSettings.UseFullTextSearch;
pUseFullTextSearch.DbType = DbType.Boolean;
var pFullTextMode = _dataProvider.GetParameter();
pFullTextMode.ParameterName = "FullTextMode";
pFullTextMode.Value = (int)_commonSettings.FullTextMode;
pFullTextMode.DbType = DbType.Int32;
var pLanguageId = _dataProvider.GetParameter();
pLanguageId.ParameterName = "LanguageId";
pLanguageId.Value = searchLocalizedValue ? languageId : 0;
pLanguageId.DbType = DbType.Int32;
var pOrderBy = _dataProvider.GetParameter();
pOrderBy.ParameterName = "OrderBy";
pOrderBy.Value = (int)orderBy;
pOrderBy.DbType = DbType.Int32;
var pAllowedCustomerRoleIds = _dataProvider.GetParameter();
pAllowedCustomerRoleIds.ParameterName = "AllowedCustomerRoleIds";
pAllowedCustomerRoleIds.Value = !_catalogSettings.IgnoreAcl ? commaSeparatedAllowedCustomerRoleIds : "";
pAllowedCustomerRoleIds.DbType = DbType.String;
var pPageIndex = _dataProvider.GetParameter();
pPageIndex.ParameterName = "PageIndex";
pPageIndex.Value = pageIndex;
pPageIndex.DbType = DbType.Int32;
var pPageSize = _dataProvider.GetParameter();
pPageSize.ParameterName = "PageSize";
pPageSize.Value = pageSize;
pPageSize.DbType = DbType.Int32;
var pShowHidden = _dataProvider.GetParameter();
pShowHidden.ParameterName = "ShowHidden";
pShowHidden.Value = showHidden;
pShowHidden.DbType = DbType.Boolean;
var pOverridePublished = _dataProvider.GetParameter();
pOverridePublished.ParameterName = "OverridePublished";
pOverridePublished.Value = overridePublished != null ? (object)overridePublished.Value : DBNull.Value;
pOverridePublished.DbType = DbType.Boolean;
var pTotalRecords = _dataProvider.GetParameter();
pTotalRecords.ParameterName = "TotalRecords";
pTotalRecords.Direction = ParameterDirection.Output;
pTotalRecords.DbType = DbType.Int32;
//invoke stored procedure
var products = _dbContext.ExecuteStoredProcedureList <Product>(
"ProductLoadAllPaged",
pCategoryIds,
pStoreId,
pProductTypeId,
pMarkedAsNewOnly,
pProductTagId,
pFeaturedProducts,
pKeywords,
pSearchDescriptions,
pSearchSku,
pSearchProductTags,
pUseFullTextSearch,
pFullTextMode,
pLanguageId,
pOrderBy,
pAllowedCustomerRoleIds,
pPageIndex,
pPageSize,
pShowHidden,
pOverridePublished,
pTotalRecords);
//return products
int totalRecords = (pTotalRecords.Value != DBNull.Value) ? Convert.ToInt32(pTotalRecords.Value) : 0;
return(new PagedList <Product>(products, pageIndex, pageSize, totalRecords));
#endregion
}
else
{
//stored procedures aren't supported. Use LINQ
#region Search products
//products
var query = _productRepository.Table;
query = query.Where(p => !p.Deleted);
if (!overridePublished.HasValue)
{
//process according to "showHidden"
if (!showHidden)
{
query = query.Where(p => p.Published);
}
}
else if (overridePublished.Value)
{
//published only
query = query.Where(p => p.Published);
}
else if (!overridePublished.Value)
{
//unpublished only
query = query.Where(p => !p.Published);
}
//The function 'CurrentUtcDateTime' is not supported by SQL Server Compact.
//That's why we pass the date value
var nowUtc = DateTime.UtcNow;
if (markedAsNewOnly)
{
query = query.Where(p => p.MarkAsNew);
query = query.Where(p =>
(!p.MarkAsNewStartDateTimeUtc.HasValue || p.MarkAsNewStartDateTimeUtc.Value < nowUtc) &&
(!p.MarkAsNewEndDateTimeUtc.HasValue || p.MarkAsNewEndDateTimeUtc.Value > nowUtc));
}
if (productType.HasValue)
{
var productTypeId = (int)productType.Value;
query = query.Where(p => p.ProductTypeId == productTypeId);
}
//searching by keyword
if (!String.IsNullOrWhiteSpace(keywords))
{
query = from p in query
join lp in _localizedPropertyRepository.Table on p.Id equals lp.EntityId into p_lp
from lp in p_lp.DefaultIfEmpty()
from pt in p.ProductTags.DefaultIfEmpty()
where (p.Name.Contains(keywords)) ||
(searchDescriptions && p.ShortDescription.Contains(keywords)) ||
(searchDescriptions && p.FullDescription.Contains(keywords)) ||
//sku (exact match)
(searchSku && p.Sku == keywords) ||
//product tags (exact match)
(searchProductTags && pt.Name == keywords) ||
//localized values
(searchLocalizedValue && lp.LanguageId == languageId && lp.LocaleKeyGroup == "Product" && lp.LocaleKey == "Name" && lp.LocaleValue.Contains(keywords)) ||
(searchDescriptions && searchLocalizedValue && lp.LanguageId == languageId && lp.LocaleKeyGroup == "Product" && lp.LocaleKey == "ShortDescription" && lp.LocaleValue.Contains(keywords)) ||
(searchDescriptions && searchLocalizedValue && lp.LanguageId == languageId && lp.LocaleKeyGroup == "Product" && lp.LocaleKey == "FullDescription" && lp.LocaleValue.Contains(keywords))
select p;
}
if (!showHidden && !_catalogSettings.IgnoreAcl)
{
//ACL (access control list)
query = from p in query
join acl in _aclRepository.Table
on new { c1 = p.Id, c2 = "Product" } equals new { c1 = acl.EntityId, c2 = acl.EntityName } into p_acl
from acl in p_acl.DefaultIfEmpty()
where !p.SubjectToAcl || allowedCustomerRolesIds.Contains(acl.CustomerRoleId)
select p;
}
if (storeId > 0 && !_catalogSettings.IgnoreStoreLimitations)
{
//Store mapping
query = from p in query
join sm in _storeMappingRepository.Table
on new { c1 = p.Id, c2 = "Product" } equals new { c1 = sm.EntityId, c2 = sm.EntityName } into p_sm
from sm in p_sm.DefaultIfEmpty()
where !p.LimitedToStores || storeId == sm.StoreId
select p;
}
//category filtering
if (categoryIds != null && categoryIds.Any())
{
query = from p in query
from pc in p.ProductCategories.Where(pc => categoryIds.Contains(pc.CategoryId))
where (!featuredProducts.HasValue || featuredProducts.Value == pc.IsFeaturedProduct)
select p;
}
//related products filtering
//if (relatedToProductId > 0)
//{
// query = from p in query
// join rp in _relatedProductRepository.Table on p.Id equals rp.ProductId2
// where (relatedToProductId == rp.ProductId1)
// select p;
//}
//tag filtering
if (productTagId > 0)
{
query = from p in query
from pt in p.ProductTags.Where(pt => pt.Id == productTagId)
select p;
}
//only distinct products (group by ID)
//if we use standard Distinct() method, then all fields will be compared (low performance)
//it'll not work in SQL Server Compact when searching products by a keyword)
query = from p in query
group p by p.Id
into pGroup
orderby pGroup.Key
select pGroup.FirstOrDefault();
//sort products
if (orderBy == ProductSortingEnum.Position && categoryIds != null && categoryIds.Any())
{
//category position
var firstCategoryId = categoryIds[0];
query = query.OrderBy(p => p.ProductCategories.FirstOrDefault(pc => pc.CategoryId == firstCategoryId).DisplayOrder);
}
else if (orderBy == ProductSortingEnum.Position)
{
//otherwise sort by name
query = query.OrderBy(p => p.Name);
}
else if (orderBy == ProductSortingEnum.NameAsc)
{
//Name: A to Z
query = query.OrderBy(p => p.Name);
}
else if (orderBy == ProductSortingEnum.NameDesc)
{
//Name: Z to A
query = query.OrderByDescending(p => p.Name);
}
else if (orderBy == ProductSortingEnum.CreatedOn)
{
//creation date
query = query.OrderByDescending(p => p.CreatedOnUtc);
}
else
{
//actually this code is not reachable
query = query.OrderBy(p => p.Name);
}
var products = new PagedList <Product>(query, pageIndex, pageSize);
//return products
return(products);
#endregion
}
}
/// <summary>
/// Gets all products
/// </summary>
/// <param name="categoryId">Category identifier; 0 to load all recordss</param>
/// <param name="manufacturerId">Manufacturer identifier; 0 to load all recordss</param>
/// <param name="productTagId">Product tag identifier; 0 to load all recordss</param>
/// <param name="featuredProducts">A value indicating whether loaded products are marked as featured (relates only to categories and manufacturers). 0 to load featured products only, 1 to load not featured products only, null to load all products</param>
/// <param name="priceMin">Minimum price</param>
/// <param name="priceMax">Maximum price</param>
/// <param name="relatedToProductId">Filter by related product; 0 to load all recordss</param>
/// <param name="keywords">Keywords</param>
/// <param name="searchDescriptions">A value indicating whether to search in descriptions</param>
/// <param name="pageSize">Page size</param>
/// <param name="pageIndex">Page index</param>
/// <param name="filteredSpecs">Filtered product specification identifiers</param>
/// <param name="languageId">Language identifier</param>
/// <param name="orderBy">Order by</param>
/// <param name="totalRecords">Total records</param>
/// <returns>Product collection</returns>
public List<Product> GetAllProducts(int categoryId,
int manufacturerId, int productTagId, bool? featuredProducts,
decimal? priceMin, decimal? priceMax,
int relatedToProductId, string keywords, bool searchDescriptions, int pageSize,
int pageIndex, List<int> filteredSpecs, int languageId,
ProductSortingEnum orderBy, out int totalRecords)
{
if (pageSize <= 0)
pageSize = 10;
if (pageSize == int.MaxValue)
pageSize = int.MaxValue - 1;
if (pageIndex < 0)
pageIndex = 0;
if (pageIndex == int.MaxValue)
pageIndex = int.MaxValue - 1;
bool showHidden = NopContext.Current.IsAdmin;
string commaSeparatedSpecIds = string.Empty;
if (filteredSpecs != null)
{
filteredSpecs.Sort();
for (int i = 0; i < filteredSpecs.Count; i++)
{
commaSeparatedSpecIds += filteredSpecs[i].ToString();
if (i != filteredSpecs.Count - 1)
{
commaSeparatedSpecIds += ",";
}
}
}
ObjectParameter totalRecordsParameter = new ObjectParameter("TotalRecords", typeof(int));
var products = _context.Sp_ProductLoadAllPaged(categoryId,
manufacturerId, productTagId, featuredProducts,
priceMin, priceMax, relatedToProductId,
keywords, searchDescriptions, showHidden, pageIndex, pageSize, commaSeparatedSpecIds,
languageId, (int)orderBy, totalRecordsParameter).ToList();
totalRecords = Convert.ToInt32(totalRecordsParameter.Value);
return products;
}
protected void BindData()
{
var manufacturer = this.ManufacturerService.GetManufacturerById(this.ManufacturerId);
lName.Text = Server.HtmlEncode(manufacturer.LocalizedName);
lDescription.Text = manufacturer.LocalizedDescription;
//featured products
var featuredProducts = manufacturer.FeaturedProducts;
if (featuredProducts.Count > 0)
{
dlFeaturedProducts.DataSource = featuredProducts;
dlFeaturedProducts.DataBind();
}
else
{
pnlFeaturedProducts.Visible = false;
}
//price ranges
this.ctrlPriceRangeFilter.PriceRanges = manufacturer.PriceRanges;
if (string.IsNullOrEmpty(ctrlPriceRangeFilter.PriceRanges))
{
this.pnlFilters.Visible = false;
}
//page size
int totalRecords = 0;
int pageSize = 10;
if (manufacturer.PageSize > 0)
{
pageSize = manufacturer.PageSize;
}
//price ranges
decimal?minPrice = null;
decimal?maxPrice = null;
decimal?minPriceConverted = null;
decimal?maxPriceConverted = null;
if (ctrlPriceRangeFilter.SelectedPriceRange != null)
{
minPrice = ctrlPriceRangeFilter.SelectedPriceRange.From;
if (minPrice.HasValue)
{
minPriceConverted = this.CurrencyService.ConvertCurrency(minPrice.Value, NopContext.Current.WorkingCurrency, this.CurrencyService.PrimaryStoreCurrency);
}
maxPrice = ctrlPriceRangeFilter.SelectedPriceRange.To;
if (maxPrice.HasValue)
{
maxPriceConverted = this.CurrencyService.ConvertCurrency(maxPrice.Value, NopContext.Current.WorkingCurrency, this.CurrencyService.PrimaryStoreCurrency);
}
}
//sorting
ProductSortingEnum orderBy = ProductSortingEnum.Position;
if (this.SettingManager.GetSettingValueBoolean("Common.AllowProductSorting"))
{
CommonHelper.SelectListItem(this.ddlSorting, CommonHelper.QueryStringInt("orderby"));
orderBy = (ProductSortingEnum)Enum.ToObject(typeof(ProductSortingEnum), int.Parse(ddlSorting.SelectedItem.Value));
}
var productCollection = this.ProductService.GetAllProducts(0,
this.ManufacturerId, 0, false, minPriceConverted, maxPriceConverted,
string.Empty, false, pageSize, this.CurrentPageIndex,
null, orderBy, out totalRecords);
if (productCollection.Count > 0)
{
this.productsPager.PageSize = pageSize;
this.productsPager.TotalRecords = totalRecords;
this.productsPager.PageIndex = this.CurrentPageIndex;
this.dlProducts.DataSource = productCollection;
this.dlProducts.DataBind();
}
else
{
this.dlProducts.Visible = false;
this.pnlSorting.Visible = false;
}
}
/// <summary>
/// Search products
/// </summary>
/// <param name="filterableSpecificationAttributeOptionIds">The specification attribute option identifiers applied to loaded products (all pages)</param>
/// <param name="loadFilterableSpecificationAttributeOptionIds">A value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)</param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="categoryIds">Category identifiers</param>
/// <param name="manufacturerId">Manufacturer identifier; 0 to load all records</param>
/// <param name="storeId">Store identifier; 0 to load all records</param>
/// <param name="vendorId">Vendor identifier; 0 to load all records</param>
/// <param name="warehouseId">Warehouse identifier; 0 to load all records</param>
/// <param name="productType">Product type; 0 to load all records</param>
/// <param name="visibleIndividuallyOnly">A values indicating whether to load only products marked as "visible individually"; "false" to load all records; "true" to load "visible individually" only</param>
/// <param name="markedAsNewOnly">A values indicating whether to load only products marked as "new"; "false" to load all records; "true" to load "marked as new" only</param>
/// <param name="featuredProducts">A value indicating whether loaded products are marked as featured (relates only to categories and manufacturers). 0 to load featured products only, 1 to load not featured products only, null to load all products</param>
/// <param name="priceMin">Minimum price; null to load all records</param>
/// <param name="priceMax">Maximum price; null to load all records</param>
/// <param name="productTagId">Product tag identifier; 0 to load all records</param>
/// <param name="keywords">Keywords</param>
/// <param name="searchDescriptions">A value indicating whether to search by a specified "keyword" in product descriptions</param>
/// <param name="searchManufacturerPartNumber">A value indicating whether to search by a specified "keyword" in manufacturer part number</param>
/// <param name="searchSku">A value indicating whether to search by a specified "keyword" in product SKU</param>
/// <param name="searchProductTags">A value indicating whether to search by a specified "keyword" in product tags</param>
/// <param name="languageId">Language identifier (search for text searching)</param>
/// <param name="filteredSpecs">Filtered product specification identifiers</param>
/// <param name="orderBy">Order by</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <param name="overridePublished">
/// null - process "Published" property according to "showHidden" parameter
/// true - load only "Published" products
/// false - load only "Unpublished" products
/// </param>
/// <returns>Products</returns>
public virtual IPagedList<Product> SearchProducts(
out IList<int> filterableSpecificationAttributeOptionIds,
bool loadFilterableSpecificationAttributeOptionIds = false,
int pageIndex = 0,
int pageSize = int.MaxValue,
IList<int> categoryIds = null,
int manufacturerId = 0,
int storeId = 0,
int vendorId = 0,
int warehouseId = 0,
ProductType? productType = null,
bool visibleIndividuallyOnly = false,
bool markedAsNewOnly = false,
bool? featuredProducts = null,
decimal? priceMin = null,
decimal? priceMax = null,
int productTagId = 0,
string keywords = null,
bool searchDescriptions = false,
bool searchManufacturerPartNumber = true,
bool searchSku = true,
bool searchProductTags = false,
int languageId = 0,
IList<int> filteredSpecs = null,
ProductSortingEnum orderBy = ProductSortingEnum.Position,
bool showHidden = false,
bool? overridePublished = null)
{
filterableSpecificationAttributeOptionIds = new List<int>();
//search by keyword
bool searchLocalizedValue = false;
if (languageId > 0)
{
if (showHidden)
{
searchLocalizedValue = true;
}
else
{
//ensure that we have at least two published languages
var totalPublishedLanguages = _languageService.GetAllLanguages().Count;
searchLocalizedValue = totalPublishedLanguages >= 2;
}
}
//validate "categoryIds" parameter
if (categoryIds !=null && categoryIds.Contains(0))
categoryIds.Remove(0);
//Access control list. Allowed customer roles
var allowedCustomerRolesIds = _workContext.CurrentCustomer.GetCustomerRoleIds();
if (_commonSettings.UseStoredProceduresIfSupported && _dataProvider.StoredProceduredSupported)
{
//stored procedures are enabled and supported by the database.
//It's much faster than the LINQ implementation below
#region Use stored procedure
//pass category identifiers as comma-delimited string
string commaSeparatedCategoryIds = categoryIds == null ? "" : string.Join(",", categoryIds);
//pass customer role identifiers as comma-delimited string
string commaSeparatedAllowedCustomerRoleIds = string.Join(",", allowedCustomerRolesIds);
//pass specification identifiers as comma-delimited string
string commaSeparatedSpecIds = "";
if (filteredSpecs != null)
{
((List<int>)filteredSpecs).Sort();
commaSeparatedSpecIds = string.Join(",", filteredSpecs);
}
//some databases don't support int.MaxValue
if (pageSize == int.MaxValue)
pageSize = int.MaxValue - 1;
//prepare parameters
var pCategoryIds = _dataProvider.GetParameter();
pCategoryIds.ParameterName = "CategoryIds";
pCategoryIds.Value = (object)commaSeparatedCategoryIds ?? DBNull.Value;
pCategoryIds.DbType = DbType.String;
var pManufacturerId = _dataProvider.GetParameter();
pManufacturerId.ParameterName = "ManufacturerId";
pManufacturerId.Value = manufacturerId;
pManufacturerId.DbType = DbType.Int32;
var pStoreId = _dataProvider.GetParameter();
pStoreId.ParameterName = "StoreId";
pStoreId.Value = !_catalogSettings.IgnoreStoreLimitations ? storeId : 0;
pStoreId.DbType = DbType.Int32;
var pVendorId = _dataProvider.GetParameter();
pVendorId.ParameterName = "VendorId";
pVendorId.Value = vendorId;
pVendorId.DbType = DbType.Int32;
var pWarehouseId = _dataProvider.GetParameter();
pWarehouseId.ParameterName = "WarehouseId";
pWarehouseId.Value = warehouseId;
pWarehouseId.DbType = DbType.Int32;
var pProductTypeId = _dataProvider.GetParameter();
pProductTypeId.ParameterName = "ProductTypeId";
pProductTypeId.Value = productType.HasValue ? (object)productType.Value : DBNull.Value;
pProductTypeId.DbType = DbType.Int32;
var pVisibleIndividuallyOnly = _dataProvider.GetParameter();
pVisibleIndividuallyOnly.ParameterName = "VisibleIndividuallyOnly";
pVisibleIndividuallyOnly.Value = visibleIndividuallyOnly;
pVisibleIndividuallyOnly.DbType = DbType.Int32;
var pMarkedAsNewOnly = _dataProvider.GetParameter();
pMarkedAsNewOnly.ParameterName = "MarkedAsNewOnly";
pMarkedAsNewOnly.Value = markedAsNewOnly;
pMarkedAsNewOnly.DbType = DbType.Int32;
var pProductTagId = _dataProvider.GetParameter();
pProductTagId.ParameterName = "ProductTagId";
pProductTagId.Value = productTagId;
pProductTagId.DbType = DbType.Int32;
var pFeaturedProducts = _dataProvider.GetParameter();
pFeaturedProducts.ParameterName = "FeaturedProducts";
pFeaturedProducts.Value = featuredProducts.HasValue ? (object)featuredProducts.Value : DBNull.Value;
pFeaturedProducts.DbType = DbType.Boolean;
var pPriceMin = _dataProvider.GetParameter();
pPriceMin.ParameterName = "PriceMin";
pPriceMin.Value = priceMin.HasValue ? (object)priceMin.Value : DBNull.Value;
pPriceMin.DbType = DbType.Decimal;
var pPriceMax = _dataProvider.GetParameter();
pPriceMax.ParameterName = "PriceMax";
pPriceMax.Value = priceMax.HasValue ? (object)priceMax.Value : DBNull.Value;
pPriceMax.DbType = DbType.Decimal;
var pKeywords = _dataProvider.GetParameter();
pKeywords.ParameterName = "Keywords";
pKeywords.Value = keywords != null ? (object)keywords : DBNull.Value;
pKeywords.DbType = DbType.String;
var pSearchDescriptions = _dataProvider.GetParameter();
pSearchDescriptions.ParameterName = "SearchDescriptions";
pSearchDescriptions.Value = searchDescriptions;
pSearchDescriptions.DbType = DbType.Boolean;
var pSearchManufacturerPartNumber = _dataProvider.GetParameter();
pSearchManufacturerPartNumber.ParameterName = "SearchManufacturerPartNumber";
pSearchManufacturerPartNumber.Value = searchManufacturerPartNumber;
pSearchManufacturerPartNumber.DbType = DbType.Boolean;
var pSearchSku = _dataProvider.GetParameter();
pSearchSku.ParameterName = "SearchSku";
pSearchSku.Value = searchSku;
pSearchSku.DbType = DbType.Boolean;
var pSearchProductTags = _dataProvider.GetParameter();
pSearchProductTags.ParameterName = "SearchProductTags";
pSearchProductTags.Value = searchProductTags;
pSearchProductTags.DbType = DbType.Boolean;
var pUseFullTextSearch = _dataProvider.GetParameter();
pUseFullTextSearch.ParameterName = "UseFullTextSearch";
pUseFullTextSearch.Value = _commonSettings.UseFullTextSearch;
pUseFullTextSearch.DbType = DbType.Boolean;
var pFullTextMode = _dataProvider.GetParameter();
pFullTextMode.ParameterName = "FullTextMode";
pFullTextMode.Value = (int)_commonSettings.FullTextMode;
pFullTextMode.DbType = DbType.Int32;
var pFilteredSpecs = _dataProvider.GetParameter();
pFilteredSpecs.ParameterName = "FilteredSpecs";
pFilteredSpecs.Value = (object)commaSeparatedSpecIds ?? DBNull.Value;
pFilteredSpecs.DbType = DbType.String;
var pLanguageId = _dataProvider.GetParameter();
pLanguageId.ParameterName = "LanguageId";
pLanguageId.Value = searchLocalizedValue ? languageId : 0;
pLanguageId.DbType = DbType.Int32;
var pOrderBy = _dataProvider.GetParameter();
pOrderBy.ParameterName = "OrderBy";
pOrderBy.Value = (int)orderBy;
pOrderBy.DbType = DbType.Int32;
var pAllowedCustomerRoleIds = _dataProvider.GetParameter();
pAllowedCustomerRoleIds.ParameterName = "AllowedCustomerRoleIds";
pAllowedCustomerRoleIds.Value = !_catalogSettings.IgnoreAcl ? commaSeparatedAllowedCustomerRoleIds : "";
pAllowedCustomerRoleIds.DbType = DbType.String;
var pPageIndex = _dataProvider.GetParameter();
pPageIndex.ParameterName = "PageIndex";
pPageIndex.Value = pageIndex;
pPageIndex.DbType = DbType.Int32;
var pPageSize = _dataProvider.GetParameter();
pPageSize.ParameterName = "PageSize";
pPageSize.Value = pageSize;
pPageSize.DbType = DbType.Int32;
var pShowHidden = _dataProvider.GetParameter();
pShowHidden.ParameterName = "ShowHidden";
pShowHidden.Value = showHidden;
pShowHidden.DbType = DbType.Boolean;
var pOverridePublished = _dataProvider.GetParameter();
pOverridePublished.ParameterName = "OverridePublished";
pOverridePublished.Value = overridePublished != null ? (object)overridePublished.Value : DBNull.Value;
pOverridePublished.DbType = DbType.Boolean;
var pLoadFilterableSpecificationAttributeOptionIds = _dataProvider.GetParameter();
pLoadFilterableSpecificationAttributeOptionIds.ParameterName = "LoadFilterableSpecificationAttributeOptionIds";
pLoadFilterableSpecificationAttributeOptionIds.Value = loadFilterableSpecificationAttributeOptionIds;
pLoadFilterableSpecificationAttributeOptionIds.DbType = DbType.Boolean;
var pFilterableSpecificationAttributeOptionIds = _dataProvider.GetParameter();
pFilterableSpecificationAttributeOptionIds.ParameterName = "FilterableSpecificationAttributeOptionIds";
pFilterableSpecificationAttributeOptionIds.Direction = ParameterDirection.Output;
pFilterableSpecificationAttributeOptionIds.Size = int.MaxValue - 1;
pFilterableSpecificationAttributeOptionIds.DbType = DbType.String;
var pTotalRecords = _dataProvider.GetParameter();
pTotalRecords.ParameterName = "TotalRecords";
pTotalRecords.Direction = ParameterDirection.Output;
pTotalRecords.DbType = DbType.Int32;
//invoke stored procedure
var products = _dbContext.ExecuteStoredProcedureList<Product>(
"ProductLoadAllPaged",
pCategoryIds,
pManufacturerId,
pStoreId,
pVendorId,
pWarehouseId,
pProductTypeId,
pVisibleIndividuallyOnly,
pMarkedAsNewOnly,
pProductTagId,
pFeaturedProducts,
pPriceMin,
pPriceMax,
pKeywords,
pSearchDescriptions,
pSearchManufacturerPartNumber,
pSearchSku,
pSearchProductTags,
pUseFullTextSearch,
pFullTextMode,
pFilteredSpecs,
pLanguageId,
pOrderBy,
pAllowedCustomerRoleIds,
pPageIndex,
pPageSize,
pShowHidden,
pOverridePublished,
pLoadFilterableSpecificationAttributeOptionIds,
pFilterableSpecificationAttributeOptionIds,
pTotalRecords);
//get filterable specification attribute option identifier
string filterableSpecificationAttributeOptionIdsStr = (pFilterableSpecificationAttributeOptionIds.Value != DBNull.Value) ? (string)pFilterableSpecificationAttributeOptionIds.Value : "";
if (loadFilterableSpecificationAttributeOptionIds &&
!string.IsNullOrWhiteSpace(filterableSpecificationAttributeOptionIdsStr))
{
filterableSpecificationAttributeOptionIds = filterableSpecificationAttributeOptionIdsStr
.Split(new[] {','}, StringSplitOptions.RemoveEmptyEntries)
.Select(x => Convert.ToInt32(x.Trim()))
.ToList();
}
//return products
int totalRecords = (pTotalRecords.Value != DBNull.Value) ? Convert.ToInt32(pTotalRecords.Value) : 0;
return new PagedList<Product>(products, pageIndex, pageSize, totalRecords);
#endregion
}
else
{
//stored procedures aren't supported. Use LINQ
#region Search products
//products
var query = _productRepository.Table;
query = query.Where(p => !p.Deleted);
if (!overridePublished.HasValue)
{
//process according to "showHidden"
if (!showHidden)
{
query = query.Where(p => p.Published);
}
}
else if (overridePublished.Value)
{
//published only
query = query.Where(p => p.Published);
}
else if (!overridePublished.Value)
{
//unpublished only
query = query.Where(p => !p.Published);
}
if (visibleIndividuallyOnly)
{
query = query.Where(p => p.VisibleIndividually);
}
//The function 'CurrentUtcDateTime' is not supported by SQL Server Compact.
//That's why we pass the date value
var nowUtc = DateTime.UtcNow;
if (markedAsNewOnly)
{
query = query.Where(p => p.MarkAsNew);
query = query.Where(p =>
(!p.MarkAsNewStartDateTimeUtc.HasValue || p.MarkAsNewStartDateTimeUtc.Value < nowUtc) &&
(!p.MarkAsNewEndDateTimeUtc.HasValue || p.MarkAsNewEndDateTimeUtc.Value > nowUtc));
}
if (productType.HasValue)
{
var productTypeId = (int) productType.Value;
query = query.Where(p => p.ProductTypeId == productTypeId);
}
if (priceMin.HasValue)
{
//min price
query = query.Where(p =>
//special price (specified price and valid date range)
((p.SpecialPrice.HasValue &&
((!p.SpecialPriceStartDateTimeUtc.HasValue ||
p.SpecialPriceStartDateTimeUtc.Value < nowUtc) &&
(!p.SpecialPriceEndDateTimeUtc.HasValue ||
p.SpecialPriceEndDateTimeUtc.Value > nowUtc))) &&
(p.SpecialPrice >= priceMin.Value))
||
//regular price (price isn't specified or date range isn't valid)
((!p.SpecialPrice.HasValue ||
((p.SpecialPriceStartDateTimeUtc.HasValue &&
p.SpecialPriceStartDateTimeUtc.Value > nowUtc) ||
(p.SpecialPriceEndDateTimeUtc.HasValue &&
p.SpecialPriceEndDateTimeUtc.Value < nowUtc))) &&
(p.Price >= priceMin.Value)));
}
if (priceMax.HasValue)
{
//max price
query = query.Where(p =>
//special price (specified price and valid date range)
((p.SpecialPrice.HasValue &&
((!p.SpecialPriceStartDateTimeUtc.HasValue ||
p.SpecialPriceStartDateTimeUtc.Value < nowUtc) &&
(!p.SpecialPriceEndDateTimeUtc.HasValue ||
p.SpecialPriceEndDateTimeUtc.Value > nowUtc))) &&
(p.SpecialPrice <= priceMax.Value))
||
//regular price (price isn't specified or date range isn't valid)
((!p.SpecialPrice.HasValue ||
((p.SpecialPriceStartDateTimeUtc.HasValue &&
p.SpecialPriceStartDateTimeUtc.Value > nowUtc) ||
(p.SpecialPriceEndDateTimeUtc.HasValue &&
p.SpecialPriceEndDateTimeUtc.Value < nowUtc))) &&
(p.Price <= priceMax.Value)));
}
if (!showHidden)
{
//available dates
query = query.Where(p =>
(!p.AvailableStartDateTimeUtc.HasValue || p.AvailableStartDateTimeUtc.Value < nowUtc) &&
(!p.AvailableEndDateTimeUtc.HasValue || p.AvailableEndDateTimeUtc.Value > nowUtc));
}
//searching by keyword
if (!String.IsNullOrWhiteSpace(keywords))
{
query = from p in query
join lp in _localizedPropertyRepository.Table on p.Id equals lp.EntityId into p_lp
from lp in p_lp.DefaultIfEmpty()
from pt in p.ProductTags.DefaultIfEmpty()
where (p.Name.Contains(keywords)) ||
(searchDescriptions && p.ShortDescription.Contains(keywords)) ||
(searchDescriptions && p.FullDescription.Contains(keywords)) ||
//manufacturer part number
(searchManufacturerPartNumber && p.ManufacturerPartNumber == keywords) ||
//sku (exact match)
(searchSku && p.Sku == keywords) ||
//product tags (exact match)
(searchProductTags && pt.Name == keywords) ||
//localized values
(searchLocalizedValue && lp.LanguageId == languageId && lp.LocaleKeyGroup == "Product" && lp.LocaleKey == "Name" && lp.LocaleValue.Contains(keywords)) ||
(searchDescriptions && searchLocalizedValue && lp.LanguageId == languageId && lp.LocaleKeyGroup == "Product" && lp.LocaleKey == "ShortDescription" && lp.LocaleValue.Contains(keywords)) ||
(searchDescriptions && searchLocalizedValue && lp.LanguageId == languageId && lp.LocaleKeyGroup == "Product" && lp.LocaleKey == "FullDescription" && lp.LocaleValue.Contains(keywords))
select p;
}
if (!showHidden && !_catalogSettings.IgnoreAcl)
{
//ACL (access control list)
query = from p in query
join acl in _aclRepository.Table
on new { c1 = p.Id, c2 = "Product" } equals new { c1 = acl.EntityId, c2 = acl.EntityName } into p_acl
from acl in p_acl.DefaultIfEmpty()
where !p.SubjectToAcl || allowedCustomerRolesIds.Contains(acl.CustomerRoleId)
select p;
}
if (storeId > 0 && !_catalogSettings.IgnoreStoreLimitations)
{
//Store mapping
query = from p in query
join sm in _storeMappingRepository.Table
on new { c1 = p.Id, c2 = "Product" } equals new { c1 = sm.EntityId, c2 = sm.EntityName } into p_sm
from sm in p_sm.DefaultIfEmpty()
where !p.LimitedToStores || storeId == sm.StoreId
select p;
}
//category filtering
if (categoryIds != null && categoryIds.Any())
{
query = from p in query
from pc in p.ProductCategories.Where(pc => categoryIds.Contains(pc.CategoryId))
where (!featuredProducts.HasValue || featuredProducts.Value == pc.IsFeaturedProduct)
select p;
}
//manufacturer filtering
if (manufacturerId > 0)
{
query = from p in query
from pm in p.ProductManufacturers.Where(pm => pm.ManufacturerId == manufacturerId)
where (!featuredProducts.HasValue || featuredProducts.Value == pm.IsFeaturedProduct)
select p;
}
//vendor filtering
if (vendorId > 0)
{
query = query.Where(p => p.VendorId == vendorId);
}
//warehouse filtering
if (warehouseId > 0)
{
var manageStockInventoryMethodId = (int)ManageInventoryMethod.ManageStock;
query = query.Where(p =>
//"Use multiple warehouses" enabled
//we search in each warehouse
(p.ManageInventoryMethodId == manageStockInventoryMethodId &&
p.UseMultipleWarehouses &&
p.ProductWarehouseInventory.Any(pwi => pwi.WarehouseId == warehouseId))
||
//"Use multiple warehouses" disabled
//we use standard "warehouse" property
((p.ManageInventoryMethodId != manageStockInventoryMethodId ||
!p.UseMultipleWarehouses) &&
p.WarehouseId == warehouseId));
}
//related products filtering
//if (relatedToProductId > 0)
//{
// query = from p in query
// join rp in _relatedProductRepository.Table on p.Id equals rp.ProductId2
// where (relatedToProductId == rp.ProductId1)
// select p;
//}
//tag filtering
if (productTagId > 0)
{
query = from p in query
from pt in p.ProductTags.Where(pt => pt.Id == productTagId)
select p;
}
//get filterable specification attribute option identifier
if (loadFilterableSpecificationAttributeOptionIds)
{
var querySpecs = from p in query
join psa in _productSpecificationAttributeRepository.Table on p.Id equals psa.ProductId
where psa.AllowFiltering
select psa.SpecificationAttributeOptionId;
//only distinct attributes
filterableSpecificationAttributeOptionIds = querySpecs.Distinct().ToList();
}
//search by specs
if (filteredSpecs != null && filteredSpecs.Any())
{
var filteredAttributes = _specificationAttributeOptionRepository.Table
.Where(sao => filteredSpecs.Contains(sao.Id)).Select(sao => sao.SpecificationAttributeId).Distinct();
query = query.Where(p => !filteredAttributes.Except
(
_specificationAttributeOptionRepository.Table.Where(
sao => p.ProductSpecificationAttributes.Where(
psa => psa.AllowFiltering && filteredSpecs.Contains(psa.SpecificationAttributeOptionId))
.Select(psa => psa.SpecificationAttributeOptionId).Contains(sao.Id))
.Select(sao => sao.SpecificationAttributeId).Distinct()
).Any());
}
//only distinct products (group by ID)
//if we use standard Distinct() method, then all fields will be compared (low performance)
//it'll not work in SQL Server Compact when searching products by a keyword)
query = from p in query
group p by p.Id
into pGroup
orderby pGroup.Key
select pGroup.FirstOrDefault();
//sort products
if (orderBy == ProductSortingEnum.Position && categoryIds != null && categoryIds.Any())
{
//category position
var firstCategoryId = categoryIds[0];
query = query.OrderBy(p => p.ProductCategories.FirstOrDefault(pc => pc.CategoryId == firstCategoryId).DisplayOrder);
}
else if (orderBy == ProductSortingEnum.Position && manufacturerId > 0)
{
//manufacturer position
query =
query.OrderBy(p => p.ProductManufacturers.FirstOrDefault(pm => pm.ManufacturerId == manufacturerId).DisplayOrder);
}
else if (orderBy == ProductSortingEnum.Position)
{
//otherwise sort by name
query = query.OrderBy(p => p.Name);
}
else if (orderBy == ProductSortingEnum.NameAsc)
{
//Name: A to Z
query = query.OrderBy(p => p.Name);
}
else if (orderBy == ProductSortingEnum.NameDesc)
{
//Name: Z to A
query = query.OrderByDescending(p => p.Name);
}
else if (orderBy == ProductSortingEnum.PriceAsc)
{
//Price: Low to High
query = query.OrderBy(p => p.Price);
}
else if (orderBy == ProductSortingEnum.PriceDesc)
{
//Price: High to Low
query = query.OrderByDescending(p => p.Price);
}
else if (orderBy == ProductSortingEnum.CreatedOn)
{
//creation date
query = query.OrderByDescending(p => p.CreatedOnUtc);
}
else
{
//actually this code is not reachable
query = query.OrderBy(p => p.Name);
}
var products = new PagedList<Product>(query, pageIndex, pageSize);
//return products
return products;
#endregion
}
}
protected void BindData()
{
var category = this.CategoryService.GetCategoryById(this.CategoryId);
lName.Text = Server.HtmlEncode(category.LocalizedName);
lDescription.Text = category.LocalizedDescription;
//subcategories
var subCategories = this.CategoryService.GetAllCategoriesByParentCategoryId(this.CategoryId);
if (subCategories.Count > 0)
{
rptrSubCategories.DataSource = subCategories;
rptrSubCategories.DataBind();
}
else
{
rptrSubCategories.Visible = false;
}
//price ranges
this.ctrlPriceRangeFilter.PriceRanges = category.PriceRanges;
//page size
int totalRecords = 0;
int pageSize = 10;
if (category.PageSize > 0)
{
pageSize = category.PageSize;
}
//price ranges
decimal?minPrice = null;
decimal?maxPrice = null;
decimal?minPriceConverted = null;
decimal?maxPriceConverted = null;
if (ctrlPriceRangeFilter.SelectedPriceRange != null)
{
minPrice = ctrlPriceRangeFilter.SelectedPriceRange.From;
if (minPrice.HasValue)
{
minPriceConverted = this.CurrencyService.ConvertCurrency(minPrice.Value, NopContext.Current.WorkingCurrency, this.CurrencyService.PrimaryStoreCurrency);
}
maxPrice = ctrlPriceRangeFilter.SelectedPriceRange.To;
if (maxPrice.HasValue)
{
maxPriceConverted = this.CurrencyService.ConvertCurrency(maxPrice.Value, NopContext.Current.WorkingCurrency, this.CurrencyService.PrimaryStoreCurrency);
}
}
//specification filter
var psoFilterOption = ctrlProductSpecificationFilter.GetAlreadyFilteredSpecOptionIds();
//sorting
ProductSortingEnum orderBy = ProductSortingEnum.Position;
if (this.SettingManager.GetSettingValueBoolean("Common.AllowProductSorting"))
{
CommonHelper.SelectListItem(this.ddlSorting, CommonHelper.QueryStringInt("orderby"));
orderBy = (ProductSortingEnum)Enum.ToObject(typeof(ProductSortingEnum), int.Parse(ddlSorting.SelectedItem.Value));
}
//featured products are not supported by this template
//that's hwhy we load all featured and non-featured products
var productCollection = this.ProductService.GetAllProducts(this.CategoryId,
0, 0, null, minPriceConverted, maxPriceConverted,
string.Empty, false, pageSize, this.CurrentPageIndex,
psoFilterOption, orderBy, out totalRecords);
if (productCollection.Count > 0)
{
this.catalogPager.PageSize = pageSize;
this.catalogPager.TotalRecords = totalRecords;
this.catalogPager.PageIndex = this.CurrentPageIndex;
this.lvCatalog.DataSource = productCollection;
this.lvCatalog.DataBind();
}
else
{
this.lvCatalog.Visible = false;
this.pnlSorting.Visible = false;
}
}
