/// <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; } }