/// <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="saleId">Store identifier; 0 to load all records</param>
/// <param name="storeId">Store 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<AUCombLotProc> GetAllLots2(
out IList<int> filterableSpecificationAttributeOptionIds,
bool loadFilterableSpecificationAttributeOptionIds = false,
int pageIndex = 0,
int pageSize = 2147483647, //Int32.MaxValue
IList<int> categoryIds = null,
IList<int> productIds = null,
int saleId = 0,
int storeId = 0,
int warehouseId = 0,
ProductType? productType = null,
bool visibleIndividuallyOnly = 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<int> filteredSpecs = null,
AUProductSortingEnum orderBy = AUProductSortingEnum.Position,
bool showHidden = false,
bool? overridePublished = null,
bool excludeSale = false,
int excludeSaleId = 0,
AUSoldUnsoldEnum soldUnsold = 0,
int countryId = 0,
int stateProvinceId = 0)
{
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();
#region Region - Use stored procedure only to get all lots
//pass category identifiers as comma-delimited string
string commaSeparatedCategoryIds = categoryIds == null ? "" : string.Join(",", categoryIds);
//04/11/16: pass category identifiers as comma-delimited string
string commaSeparatedProductIds = productIds == null ? "" : string.Join(",", productIds);
//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 = commaSeparatedCategoryIds != null ? (object)commaSeparatedCategoryIds : DBNull.Value;
pCategoryIds.DbType = DbType.String;
//prepare parameters
var pProductIds = _dataProvider.GetParameter();
pProductIds.ParameterName = "ProductIds";
pProductIds.Value = commaSeparatedProductIds != null ? (object)commaSeparatedProductIds : DBNull.Value;
pProductIds.DbType = DbType.String;
var pSaleId = _dataProvider.GetParameter();
pSaleId.ParameterName = "SaleId";
pSaleId.Value = saleId;
pSaleId.DbType = DbType.Int32;
var pStoreId = _dataProvider.GetParameter();
pStoreId.ParameterName = "StoreId";
pStoreId.Value = !_catalogSettings.IgnoreStoreLimitations ? storeId : 0;
pStoreId.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 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 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 = 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 pAllowedCustomerRoleIds = _dataProvider.GetParameter();
pAllowedCustomerRoleIds.ParameterName = "AllowedCustomerRoleIds";
pAllowedCustomerRoleIds.Value = 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 pExcludeSale = _dataProvider.GetParameter();
pExcludeSale.ParameterName = "ExcludeSale";
pExcludeSale.Value = excludeSale;
pExcludeSale.DbType = DbType.Boolean;
var pExcludeSaleID = _dataProvider.GetParameter();
pExcludeSaleID.ParameterName = "ExcludeSaleID";
pExcludeSaleID.Value = excludeSaleId;
pExcludeSaleID.DbType = DbType.Int32;
var pSoldUnsold = _dataProvider.GetParameter();
pSoldUnsold.ParameterName = "SoldUnsold";
pSoldUnsold.Value = soldUnsold; //AUSoldUnsoldEnum is decoded here
//pSoldUnsold.Value = 1;
pSoldUnsold.DbType = DbType.Int32;
var pCountryId = _dataProvider.GetParameter();
pCountryId.ParameterName = "CountryId";
pCountryId.Value = countryId;
pCountryId.DbType = DbType.Int32;
var pStateProvinceId = _dataProvider.GetParameter();
pStateProvinceId.ParameterName = "StateProvinceId";
pStateProvinceId.Value = stateProvinceId;
pStateProvinceId.DbType = DbType.Int32;
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. AUCombLotProc is the result set coming back. The parameters must match the signature of the stored proc.
var products = _dbContext.ExecuteStoredProcedureList<AUCombLotProc>(
"AULotSearchPaged",
pCategoryIds,
pProductIds,
pSaleId,
pStoreId,
pWarehouseId,
pProductTypeId,
pVisibleIndividuallyOnly,
pProductTagId,
pFeaturedProducts,
pPriceMin,
pPriceMax,
pKeywords,
pSearchDescriptions,
pSearchSku,
pSearchProductTags,
pUseFullTextSearch,
pFullTextMode,
pFilteredSpecs,
pLanguageId,
pOrderBy,
pAllowedCustomerRoleIds,
pPageIndex,
pPageSize,
pShowHidden,
pOverridePublished,
pExcludeSale,
pExcludeSaleID,
pSoldUnsold,
pCountryId,
pStateProvinceId,
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<AUCombLotProc>(products, pageIndex, pageSize, totalRecords);
#endregion
}
////public virtual IPagedList<AUCombLotProc> SearchLots(
//// out IList<int> filterableSpecificationAttributeOptionIds,
//// bool loadFilterableSpecificationAttributeOptionIds = false,
//// int pageIndex = 0,
//// int pageSize = int.MaxValue,
//// IList<int> categoryIds = null,
//// IList<int> productIds = null, //added 04/11/16
//// int saleId = 0,
//// int storeId = 0,
//// int warehouseId = 0,
//// ProductType? productType = null,
//// bool visibleIndividuallyOnly = 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<int> filteredSpecs = null,
//// AUProductSortingEnum orderBy = AUProductSortingEnum.Position,
//// bool showHidden = false,
//// bool? overridePublished = null,
//// bool excludeSale = false,
//// int excludeSaleId = 0)
////{
//// return GetAllLots2(out filterableSpecificationAttributeOptionIds, false,
//// pageIndex, pageSize, categoryIds, productIds,
//// saleId, storeId, warehouseId,
//// productType, visibleIndividuallyOnly, featuredProducts,
//// priceMin, priceMax, productTagId, keywords, searchDescriptions, searchSku,
//// searchProductTags, languageId, filteredSpecs,
//// orderBy, showHidden, overridePublished, excludeSale, excludeSaleId);
////}
#region GetAllLots
//NJM: Note that the Product Service asks if the config allows stored procedures before executing the stored proc. AUConsignor is developed for SQL Server
//so it assumes store procedure only. This may need to change in the future
/// <summary>
/// Search products
/// </summary>
/// <param name="pageIndex">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="categoryIds">Category identifiers</param>
/// <param name="saleId">Store identifier; 0 to load all records</param>
/// <param name="storeId">Store 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<AUCombLotProc> GetAllLots(
int pageIndex = 0,
int pageSize = int.MaxValue,
IList<int> categoryIds = null,
IList<int> productIds = null, //added 04/11/16
int saleId = 0,
int storeId = 0,
int warehouseId = 0,
ProductType? productType = null,
bool visibleIndividuallyOnly = 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<int> filteredSpecs = null,
AUProductSortingEnum orderBy = AUProductSortingEnum.Position,
bool showHidden = false,
bool? overridePublished = null,
bool excludeSale = false,
int excludeSaleId = 0,
AUSoldUnsoldEnum soldUnsold = 0,
int countryId = 0,
int stateProvinceId = 0)
{
IList<int> filterableSpecificationAttributeOptionIds;
return GetAllLots2(out filterableSpecificationAttributeOptionIds, false,
pageIndex, pageSize, categoryIds,productIds,
saleId, storeId, warehouseId,
productType, visibleIndividuallyOnly, featuredProducts,
priceMin, priceMax, productTagId, keywords, searchDescriptions, searchSku,
searchProductTags, languageId, filteredSpecs,
orderBy, showHidden, overridePublished, excludeSale, excludeSaleId, soldUnsold, countryId, stateProvinceId);
}
