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