/// <summary>
/// Get product count for each of existing product tag
/// </summary>
/// <param name="storeId">Store identifier</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <returns>Dictionary of "product tag ID : product count"</returns>
private Dictionary <int, int> GetProductCount(int storeId, bool showHidden)
{
var allowedCustomerRolesIds = string.Empty;
if (!showHidden && !_catalogSettings.IgnoreAcl)
{
//Access control list. Allowed customer roles
//pass customer role identifiers as comma-delimited string
allowedCustomerRolesIds = string.Join(",", _customerService.GetCustomerRoleIds(_workContext.CurrentCustomer));
}
var key = _cacheKeyService.PrepareKeyForDefaultCache(NopCatalogDefaults.ProductTagCountCacheKey, storeId,
_customerService.GetCustomerRoleIds(_workContext.CurrentCustomer),
showHidden);
return(_staticCacheManager.Get(key, () =>
{
//prepare input parameters
var pStoreId = SqlParameterHelper.GetInt32Parameter("StoreId", storeId);
var pAllowedCustomerRoleIds = SqlParameterHelper.GetStringParameter("AllowedCustomerRoleIds", allowedCustomerRolesIds);
//invoke stored procedure
return _dataProvider.QueryProc <ProductTagWithCount>("ProductTagCountLoadAll",
pStoreId,
pAllowedCustomerRoleIds)
.ToDictionary(item => item.ProductTagId, item => item.ProductCount);
}));
}
public void UpdateCustomerPassword(UserPassword userPassword)
{
try
{
//prepare parameters
var pUserId = SqlParameterHelper.GetInt32Parameter("@_UserId", userPassword.UserId);
var pPasswordHash = SqlParameterHelper.GetStringParameter("@_PasswordHash", userPassword.PasswordHash);
_dataProvider.ExecuteNonQuery("UpdatePasswordUser", pUserId, pPasswordHash);
var userId = _userRepository.GetById(userPassword.UserId);
_eventPublisher.EntityUpdated(userId);
}
catch (Exception ex)
{
_logger.Error("UpdateCustomerPassword error", ex);
}
}
/// <summary>
/// Gets all categories
/// </summary>
/// <param name="categoryName">Category name</param>
/// <param name="storeId">Store identifier; 0 if you want to get all records</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>Categories</returns>
public virtual IPagedList <Category> GetAllCategories(string categoryName, int storeId = 0,
int pageIndex = 0, int pageSize = int.MaxValue, bool showHidden = false)
{
if (_commonSettings.UseStoredProcedureForLoadingCategories)
{
//stored procedures are enabled for loading categories and supported by the database.
//It's much faster with a large number of categories than the LINQ implementation below
//prepare parameters
var showHiddenParameter = SqlParameterHelper.GetBooleanParameter("ShowHidden", showHidden);
var nameParameter = SqlParameterHelper.GetStringParameter("Name", categoryName ?? string.Empty);
var storeIdParameter = SqlParameterHelper.GetInt32Parameter("StoreId", !_catalogSettings.IgnoreStoreLimitations ? storeId : 0);
var pageIndexParameter = SqlParameterHelper.GetInt32Parameter("PageIndex", pageIndex);
var pageSizeParameter = SqlParameterHelper.GetInt32Parameter("PageSize", pageSize);
//pass allowed customer role identifiers as comma-delimited string
var customerRoleIdsParameter = SqlParameterHelper.GetStringParameter("CustomerRoleIds", !_catalogSettings.IgnoreAcl ? string.Join(",", _customerService.GetCustomerRoleIds(_workContext.CurrentCustomer)) : string.Empty);
var totalRecordsParameter = SqlParameterHelper.GetOutputInt32Parameter("TotalRecords");
//invoke stored procedure
var categories = _categoryRepository.EntityFromSql("CategoryLoadAllPaged",
showHiddenParameter, nameParameter, storeIdParameter, customerRoleIdsParameter,
pageIndexParameter, pageSizeParameter, totalRecordsParameter).ToList();
var totalRecords = totalRecordsParameter.Value != DBNull.Value ? Convert.ToInt32(totalRecordsParameter.Value) : 0;
//paging
return(new PagedList <Category>(categories, pageIndex, pageSize, totalRecords));
}
//don't use a stored procedure. Use LINQ
var query = _categoryRepository.Table;
if (!showHidden)
{
query = query.Where(c => c.Published);
}
if (!string.IsNullOrWhiteSpace(categoryName))
{
query = query.Where(c => c.Name.Contains(categoryName));
}
query = query.Where(c => !c.Deleted);
query = query.OrderBy(c => c.ParentCategoryId).ThenBy(c => c.DisplayOrder).ThenBy(c => c.Id);
if ((storeId > 0 && !_catalogSettings.IgnoreStoreLimitations) || (!showHidden && !_catalogSettings.IgnoreAcl))
{
if (!showHidden && !_catalogSettings.IgnoreAcl)
{
//ACL (access control list)
var allowedCustomerRolesIds = _customerService.GetCustomerRoleIds(_workContext.CurrentCustomer);
query = from c in query
join acl in _aclRepository.Table
on new { c1 = c.Id, c2 = nameof(Category) } equals new { c1 = acl.EntityId, c2 = acl.EntityName } into c_acl
from acl in c_acl.DefaultIfEmpty()
where !c.SubjectToAcl || allowedCustomerRolesIds.Contains(acl.CustomerRoleId)
select c;
}
if (storeId > 0 && !_catalogSettings.IgnoreStoreLimitations)
{
//Store mapping
query = from c in query
join sm in _storeMappingRepository.Table
on new { c1 = c.Id, c2 = nameof(Category) } equals new { c1 = sm.EntityId, c2 = sm.EntityName } into c_sm
from sm in c_sm.DefaultIfEmpty()
where !c.LimitedToStores || storeId == sm.StoreId
select c;
}
query = query.Distinct().OrderBy(c => c.ParentCategoryId).ThenBy(c => c.DisplayOrder).ThenBy(c => c.Id);
}
var unsortedCategories = query.ToList();
//sort categories
var sortedCategories = SortCategoriesForTree(unsortedCategories);
//paging
return(new PagedList <Category>(sortedCategories, pageIndex, pageSize));
}
/// <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));
}