public ProductSearchResultAdv DoSearchBySite(long siteId, ProductSearchQueryAdv query, int pageNumber,
int pageSize)
{
using (var context = Factory.CreateHccDbContext())
{
var helper = CreateSearchHelper(_hccRequestContext, context, siteId);
using (MiniProfiler.Current.Step("SqlProductSearcher_DoSearch_DrillDown"))
{
return(helper.DoSearch(query, pageNumber, pageSize));
}
}
}
public ProductSearchResultAdv DoSearchBySite(long siteId, ProductSearchQueryAdv query, int pageNumber,
int pageSize)
{
throw new NotImplementedException();
}
public ProductSearchResultAdv DoSearch(List <long> wordIds, ProductSearchQueryAdv query, int pageNumber,
int pageSize)
{
throw new NotImplementedException();
}
public ProductSearchResultAdv DoSearch(ProductSearchQueryAdv query, int pageNumber, int pageSize)
{
_query = query;
var result = new ProductSearchResultAdv();
IQueryable <ProductQuery> dbQuery = null;
IQueryable <ProductQuery> dbQueryAll = null;
_queryCategories = query.Categories.Select(bvin => DataTypeHelper.BvinToGuid(bvin)).ToList();
_queryTypes = query.Types.Select(bvin => DataTypeHelper.BvinToNullableGuid(bvin)).ToList();
_queryManufacturers = query.Manufacturers.Select(bvin => DataTypeHelper.BvinToNullableGuid(bvin)).ToList();
_queryVendors = query.Vendors.Select(bvin => DataTypeHelper.BvinToNullableGuid(bvin)).ToList();
var selPropIds = query.Properties.Keys.ToList();
_querySelectedProperties = _context.hcc_ProductProperty.Where(pp => selPropIds.Contains(pp.Id)).ToList();
var productSecurityFilterQuery = _context.hcc_Product.Where(p => p.Status == (int)ProductStatus.Active);
if (query.IsConsiderSearchable)
{
productSecurityFilterQuery = productSecurityFilterQuery.Where(p => p.IsSearchable == query.IsSearchable);
}
var prodQuery = SecurityFilter(productSecurityFilterQuery);
if (query.SortOrder != CategorySortOrder.ProductName &&
query.SortOrder != CategorySortOrder.ProductNameDescending)
{
dbQuery = prodQuery.Where(p => p.IsAvailableForSale)
.GroupJoin(_propertiesJoin, p => p.bvin, ppvj => ppvj.ppv.Item.ProductBvin,
(p, ppvj) =>
new ProductQuery
{
p = p,
pt = null,
ppvj = ppvj.Where(pj => pj.ptypes.Any(pt => pt.ProductTypeBvin == p.ProductTypeId))
});
}
else
{
var productTranslation = _context.hcc_ProductTranslations
.Where(
it =>
it.Culture == _reqContext.MainContentCulture ||
it.Culture == _reqContext.FallbackContentCulture);
dbQuery = prodQuery.Where(p => p.IsAvailableForSale)
.GroupJoin(productTranslation, p => p.bvin, pt => pt.ProductId, (p, pt)
=>
new
{
p,
pt =
pt.OrderBy(ptr => ptr.Culture == _reqContext.MainContentCulture ? 1 : 2)
.FirstOrDefault()
})
.GroupJoin(_propertiesJoin, p => p.p.bvin, ppvj => ppvj.ppv.Item.ProductBvin,
(p, ppvj) =>
new ProductQuery
{
p = p.p,
pt = p.pt,
ppvj = ppvj.Where(pj => pj.ptypes.Any(pt => pt.ProductTypeBvin == p.p.ProductTypeId))
});
}
// Fill price info before filtering by price is done
result.MinPrice = dbQuery.Min(q => (decimal?)q.p.SitePrice) ?? 0;
result.MaxPrice = dbQuery.Max(q => (decimal?)q.p.SitePrice) ?? 0;
using (MiniProfiler.Current.Step("Filtering"))
{
// Filtering results by price
dbQuery = FilterByPrice(dbQuery);
dbQueryAll = FilterByFacets(dbQuery);
switch (query.SortOrder)
{
case CategorySortOrder.ProductName:
dbQueryAll = dbQueryAll.OrderBy(q => q.pt.ProductName);
break;
case CategorySortOrder.ProductNameDescending:
dbQueryAll = dbQueryAll.OrderByDescending(q => q.pt.ProductName);
break;
case CategorySortOrder.ProductPriceAscending:
dbQueryAll = dbQueryAll.OrderBy(q => q.p.SitePrice);
break;
case CategorySortOrder.ProductPriceDescending:
dbQueryAll = dbQueryAll.OrderByDescending(q => q.p.SitePrice);
break;
case CategorySortOrder.ProductSKUAscending:
dbQueryAll = dbQueryAll.OrderBy(q => q.p.SKU);
break;
case CategorySortOrder.ProductSKUDescending:
dbQueryAll = dbQueryAll.OrderByDescending(q => q.p.SKU);
break;
default:
dbQueryAll =
dbQueryAll.OrderBy(
q =>
q.p.hcc_ProductXCategory.Where(z => _queryCategories.Contains(z.CategoryId))
.Select(z => z.SortOrder)
.FirstOrDefault());
break;
}
}
// Fill facets info
using (MiniProfiler.Current.Step("Build Manufactory Facets"))
{
result.Manufacturers = BuildManufactoryFacets(FilterByFacets(dbQuery, "M"));
}
using (MiniProfiler.Current.Step("Build Vendors Facets"))
{
result.Vendors = BuildVendorsFacets(FilterByFacets(dbQuery, "V"));
}
using (MiniProfiler.Current.Step("Build Types Factes"))
{
result.Types = BuildTypesFactes(FilterByFacets(dbQuery, "T"));
}
using (MiniProfiler.Current.Step("Build Property Facets"))
{
result.Properties = BuildPropertyFacets(FilterByFacets(dbQuery, "P"));
}
using (MiniProfiler.Current.Step("Build Selections"))
{
// Fill selected data
result.SelectedCategories = BuildSelectedCategories();
result.SelectedVendors = BuildSelectedVendors();
result.SelectedManufacturers = BuildSelectedManufacturers();
result.SelectedTypes = BuildSelectedTypes();
result.SelectedProperties = BuildSelectedProperties();
result.SelectedMinPrice = query.MinPrice ?? result.MinPrice;
result.SelectedMaxPrice = query.MaxPrice ?? result.MaxPrice;
}
using (MiniProfiler.Current.Step("Get Products List"))
{
var skip = (pageNumber - 1) * pageSize;
if (skip < 0)
{
skip = 0;
}
var productIds = dbQueryAll.Select(q => q.p.bvin);
result.TotalCount = productIds.Count();
result.Products = productIds.
Skip(skip).
Take(pageSize).
ToList().
Select(guid => DataTypeHelper.GuidToBvin(guid)).
ToList();
}
return(result);
}
public ProductSearchResultAdv DoSearchBySite(long siteId, List <long> wordIds, ProductSearchQueryAdv query,
int pageNumber, int pageSize)
{
using (var context = Factory.CreateHccDbContext())
{
var result = new ProductSearchResultAdv();
var skip = (pageNumber - 1) * pageSize;
if (skip < 0)
{
skip = 0;
}
var dbQuery = context.hcc_SearchObjectWords.
Where(w => wordIds.Contains(w.WordId));
if (siteId > 0)
{
dbQuery = dbQuery.Where(w => w.SiteId == siteId);
}
var productPropertyTranslations = context.hcc_ProductPropertyTranslations.
Where(
it =>
it.Culture == _hccRequestContext.MainContentCulture ||
it.Culture == _hccRequestContext.FallbackContentCulture);
var productProperties = context.hcc_ProductProperty.Where(pp => pp.DisplayOnSearch).
GroupJoin(productPropertyTranslations, i => i.Id, it => it.ProductPropertyId, (i, it) => new
{
Item = i,
ItemTranslation =
it.OrderBy(iit => iit.Culture == _hccRequestContext.MainContentCulture ? 1 : 2)
.FirstOrDefault()
});
var productPropertyValueTranslations = context.hcc_ProductPropertyValueTranslations.
Where(
it =>
it.Culture == _hccRequestContext.MainContentCulture ||
it.Culture == _hccRequestContext.FallbackContentCulture);
var productPropertyValues = context.hcc_ProductPropertyValue.
GroupJoin(productPropertyValueTranslations, i => i.Id, it => it.ProductPropertyValueId,
(i, it) => new
{
Item = i,
ItemTranslation =
it.OrderBy(iit => iit.Culture == _hccRequestContext.MainContentCulture ? 1 : 2)
.FirstOrDefault()
});
var propertiesJoin = productProperties.
Join(productPropertyValues, pp => pp.Item.Id, ppv => ppv.Item.PropertyId, (pp, ppv) => new { pp, ppv })
.
GroupJoin(context.hcc_ProductTypeXProductProperty, pv => pv.pp.Item.Id, ptp => ptp.PropertyId,
(pv, ptp) => new { pv.pp, pv.ppv, ptypes = ptp });
var productSecurityFilterQuery = context.hcc_Product.Where(p => p.Status == (int)ProductStatus.Active);
if (query.IsConsiderSearchable)
{
productSecurityFilterQuery =
productSecurityFilterQuery.Where(p => p.IsSearchable == query.IsSearchable);
}
var products = SecurityFilter(productSecurityFilterQuery);
var dbQueryJ = dbQuery.
Join(context.hcc_SearchObjects, sow => sow.SearchObjectId, so => so.Id, (sow, so) => new { sow, so }).
Where(s => s.so.ObjectType == (int)SearchManagerObjectType.Product).
GroupBy(s => s.so.ObjectId).
Select(g => new
{
ObjectId = g.Key,
Score = g.Sum(y => y.sow.Score),
Count = g.Sum(y => 1)
}).
Join(products, s => s.ObjectId, p => p.bvin, (s, p) => new { so = s, p }).
GroupJoin(propertiesJoin, s => s.p.bvin, ppvj => ppvj.ppv.Item.ProductBvin,
(s, ppvj) =>
new
{
s.so,
s.p,
ppvj = ppvj.Where(pj => pj.ptypes.Any(pt => pt.ProductTypeBvin == s.p.ProductTypeId))
});
var queryCategories = query.Categories.
Select(bvin => DataTypeHelper.BvinToGuid(bvin)).
ToList();
var queryTypes = query.Types.
Select(bvin => DataTypeHelper.BvinToNullableGuid(bvin)).
ToList();
var queryManufactures = query.Manufactures.
Select(bvin => DataTypeHelper.BvinToNullableGuid(bvin)).
ToList();
var queryVendors = query.Vendors.
Select(bvin => DataTypeHelper.BvinToNullableGuid(bvin)).
ToList();
if (queryCategories.Count > 0)
{
foreach (var category in queryCategories)
{
dbQueryJ =
dbQueryJ.Where(s => s.p.hcc_ProductXCategory.Select(c => c.CategoryId).Contains(category));
}
}
if (queryTypes.Count > 0)
{
dbQueryJ = dbQueryJ.Where(s => queryTypes.Contains(s.p.ProductTypeId));
}
if (queryManufactures.Count > 0)
{
dbQueryJ = dbQueryJ.Where(s => queryManufactures.Contains(s.p.ManufacturerID));
}
if (queryVendors.Count > 0)
{
dbQueryJ = dbQueryJ.Where(s => queryVendors.Contains(s.p.VendorID));
}
if (query.Properties.Count > 0)
{
foreach (var pair in query.Properties)
{
var propertyId = pair.Key;
var propertyValue = pair.Value[0];
var property = context.hcc_ProductProperty.FirstOrDefault(p => p.Id == propertyId);
if (property.IsLocalizable)
{
dbQueryJ =
dbQueryJ.Where(
s =>
s.ppvj.Where(
pj =>
pj.ppv.Item.PropertyId == propertyId &&
pj.ppv.ItemTranslation.PropertyLocalizableValue == propertyValue)
.Count() > 0);
}
else
{
dbQueryJ =
dbQueryJ.Where(
s =>
s.ppvj.Where(
pj =>
pj.ppv.Item.PropertyId == propertyId &&
pj.ppv.Item.PropertyValue == propertyValue).Count() > 0);
}
}
}
// Fill price info before filtering by price is done
var minPrice = dbQueryJ.Min(s => (decimal?)s.p.SitePrice);
var maxPrice = dbQueryJ.Max(s => (decimal?)s.p.SitePrice);
result.MinPrice = minPrice ?? 0;
result.MaxPrice = maxPrice ?? 0;
// Filtering results by price
if (query.MinPrice.HasValue)
{
dbQueryJ = dbQueryJ.Where(s => s.p.SitePrice >= query.MinPrice.Value);
}
if (query.MaxPrice.HasValue)
{
dbQueryJ = dbQueryJ.Where(s => s.p.SitePrice <= query.MaxPrice.Value);
}
// Fill facets info
var categoryTranslations = context.hcc_CategoryTranslations.
Where(
it =>
it.Culture == _hccRequestContext.MainContentCulture ||
it.Culture == _hccRequestContext.FallbackContentCulture);
result.Categories = dbQueryJ.
Join(context.hcc_ProductXCategory, s => s.p.bvin, pc => pc.ProductId, (s, pc) => pc.hcc_Category).
GroupJoin(context.hcc_Category, c => c.ParentID, parent => parent.bvin, (c, parentColl) => new
{
Category = c,
Parent = parentColl.FirstOrDefault()
}).
GroupBy(c => new { CategoryId = c.Category.bvin, ParentId = c.Category.ParentID }).
Select(g => new
{
g.Key.CategoryId,
g.Key.ParentId,
Count = g.Sum(t => 1)
}).
GroupJoin(categoryTranslations, i => i.CategoryId, it => it.CategoryId, (i, it) => new
{
i.CategoryId,
i.ParentId,
i.Count,
ItemTranslation =
it.OrderBy(iit => iit.Culture == _hccRequestContext.MainContentCulture ? 1 : 2)
.FirstOrDefault()
}).
GroupJoin(categoryTranslations, i => i.ParentId, it => it.CategoryId, (i, it) => new
{
i.CategoryId,
i.ParentId,
i.Count,
i.ItemTranslation,
ParentTranslation =
it.OrderBy(iit => iit.Culture == _hccRequestContext.MainContentCulture ? 1 : 2)
.FirstOrDefault()
}).
Select(c => new InternalFacetItem
{
Id = c.CategoryId,
Name = c.ItemTranslation.Name,
Count = c.Count,
ParentId = c.ParentId,
ParentName = c.ParentId != null ? c.ParentTranslation.Name : null
}).
OrderByDescending(f => f.Count).
ToList().
Select(f => f.Convert()).
ToList();
result.Categories = ProductSearchHelper.GroupCategories(result.Categories);
result.Manufacturers = dbQueryJ.
Join(context.hcc_Manufacturer, s => s.p.ManufacturerID, m => m.bvin, (s, m) => m).
GroupBy(m => new { ManufacturerId = m.bvin, ManufacturerName = m.DisplayName }).
Select(m => new InternalFacetItem
{
Id = m.Key.ManufacturerId,
Name = m.Key.ManufacturerName,
Count = m.Sum(t => 1)
}).ToList().
Select(f => f.Convert()).
ToList();
result.Vendors = dbQueryJ.
Join(context.hcc_Vendor, s => s.p.VendorID, v => v.bvin, (s, v) => v).
GroupBy(v => new { VendorId = v.bvin, VendorName = v.DisplayName }).
Select(v => new InternalFacetItem
{
Id = v.Key.VendorId,
Name = v.Key.VendorName,
Count = v.Sum(t => 1)
}).ToList().
Select(f => f.Convert()).
ToList();
var productTypeTranslations = context.hcc_ProductTypeTranslations.
Where(
it =>
it.Culture == _hccRequestContext.MainContentCulture ||
it.Culture == _hccRequestContext.FallbackContentCulture);
result.Types = dbQueryJ.
Join(context.hcc_ProductType, s => s.p.ProductTypeId, pt => pt.bvin, (s, pt) => pt).
GroupBy(pt => pt.bvin).
Select(g => new
{
ProductTypeId = g.Key,
Count = g.Sum(t => 1)
}).
GroupJoin(productTypeTranslations, pt => pt.ProductTypeId, ptt => ptt.ProductTypeId, (i, it) => new
{
ItemTranslation =
it.OrderBy(iit => iit.Culture == _hccRequestContext.MainContentCulture ? 1 : 2)
.FirstOrDefault(),
i.Count
}).
Select(pt => new InternalFacetItem
{
Id = pt.ItemTranslation.ProductTypeId,
Name = pt.ItemTranslation.ProductTypeName,
Count = pt.Count
}).
OrderBy(f => f.Name).
ToList().
Select(f => f.Convert()).
ToList();
// Build product type properties facets
var customPropertiesInfo = dbQueryJ.
SelectMany(q => q.ppvj).
GroupBy(q => q.pp.Item.Id).
ToList();
var propertyChoiceTranslations = context.hcc_ProductPropertyChoiceTranslations.
Where(
it =>
it.Culture == _hccRequestContext.MainContentCulture ||
it.Culture == _hccRequestContext.FallbackContentCulture);
foreach (var customPropertyInfo in customPropertiesInfo)
{
var propertyFacet = new PropertyFacetItem();
var property = customPropertyInfo.FirstOrDefault().pp;
List <string> propertyValues = null;
if (property.Item.IsLocalizable)
{
propertyValues = customPropertyInfo.
Where(r => r.ppv.ItemTranslation != null).
Select(r => r.ppv.ItemTranslation.PropertyLocalizableValue).
Distinct().ToList();
}
else
{
propertyValues = customPropertyInfo.
Where(r => r.ppv.Item != null).
Select(r => r.ppv.Item.PropertyValue).
Distinct().ToList();
}
propertyFacet.Id = property.Item.Id;
propertyFacet.PropertyName = property.Item.PropertyName;
if (property.ItemTranslation != null)
{
propertyFacet.DisplayName = property.ItemTranslation.DisplayName;
}
else
{
propertyFacet.DisplayName = property.Item.PropertyName;
}
foreach (var propertyValue in propertyValues)
{
if (!string.IsNullOrEmpty(propertyValue))
{
var facetItem = new FacetItem();
facetItem.Id = propertyValue;
if (property.Item.TypeCode == (int)ProductPropertyType.MultipleChoiceField)
{
var choiceId = int.Parse(propertyValue);
var propChoice = context.hcc_ProductPropertyChoice.
GroupJoin(propertyChoiceTranslations, i => i.Id, it => it.ProductPropertyChoiceId,
(i, it) => new
{
Item = i,
ItemTranslation =
it.OrderBy(
iit => iit.Culture == _hccRequestContext.MainContentCulture ? 1 : 2)
.FirstOrDefault()
}).
FirstOrDefault(ppc => ppc.Item.Id == choiceId);
if (propChoice.ItemTranslation != null)
{
facetItem.Name = propChoice.ItemTranslation.DisplayName;
}
}
else
{
facetItem.Name = propertyValue;
}
if (property.Item.IsLocalizable)
{
facetItem.Count = customPropertyInfo.
Where(r => r.ppv.ItemTranslation != null).
Count(r => r.ppv.ItemTranslation.PropertyLocalizableValue == propertyValue);
}
else
{
facetItem.Count = customPropertyInfo.
Where(r => r.ppv.Item != null).
Count(r => r.ppv.Item.PropertyValue == propertyValue);
}
propertyFacet.FacetItems.Add(facetItem);
}
}
result.Properties.Add(propertyFacet);
}
// End - Build product type properties facets
// Fill selected data
result.SelectedCategories = context.hcc_Category.
Where(c => queryCategories.Contains(c.bvin)).
GroupJoin(categoryTranslations, i => i.bvin, it => it.CategoryId, (i, it) => new
{
Item = i,
ItemTranslation =
it.OrderBy(iit => iit.Culture == _hccRequestContext.MainContentCulture ? 1 : 2)
.FirstOrDefault()
}).
Select(c => new InternalSelectedFacetItem
{
Id = c.Item.bvin,
Name = c.ItemTranslation.Name
}).
ToList().
Select(f => f.Convert()).
ToList();
result.SelectedVendors = context.hcc_Vendor.
Where(v => queryVendors.Contains(v.bvin)).
Select(v => new InternalSelectedFacetItem
{
Id = v.bvin,
Name = v.DisplayName
}).
ToList().
Select(f => f.Convert()).
ToList();
result.SelectedManufacturers = context.hcc_Manufacturer.
Where(m => queryManufactures.Contains(m.bvin)).
Select(m => new InternalSelectedFacetItem
{
Id = m.bvin,
Name = m.DisplayName
}).
ToList().
Select(f => f.Convert()).
ToList();
result.SelectedTypes = context.hcc_ProductType.
Where(pt => queryTypes.Contains(pt.bvin)).
GroupJoin(productTypeTranslations, pt => pt.bvin, ptt => ptt.ProductTypeId, (i, it) => new
{
Item = i,
ItemTranslation =
it.OrderBy(iit => iit.Culture == _hccRequestContext.MainContentCulture ? 1 : 2)
.FirstOrDefault()
}).
Select(pt => new InternalSelectedFacetItem
{
Id = pt.Item.bvin,
Name = pt.ItemTranslation.ProductTypeName
}).
ToList().
Select(f => f.Convert()).
ToList();
result.SelectedProperties = new List <SelectedPropertyFacetItem>();
foreach (var property in query.Properties)
{
var propertyId = property.Key;
var propertyValue = property.Value[0];
var productProperty = productProperties.
Where(pp => pp.Item.Id == propertyId).
FirstOrDefault();
if (productProperty != null)
{
var propFacet = new SelectedPropertyFacetItem();
propFacet.Id = productProperty.Item.Id;
propFacet.PropertyName = productProperty.Item.PropertyName;
propFacet.DisplayName = productProperty.ItemTranslation.DisplayName;
propFacet.PropertyValues = new List <SelectedFacetItem>();
if (productProperty.Item.TypeCode == (int)ProductPropertyType.MultipleChoiceField)
{
var choiceId = int.Parse(propertyValue);
var propChoice = context.hcc_ProductPropertyChoice.
GroupJoin(propertyChoiceTranslations, i => i.Id, it => it.ProductPropertyChoiceId,
(i, it) => new
{
Item = i,
ItemTranslation =
it.OrderBy(
iit => iit.Culture == _hccRequestContext.MainContentCulture ? 1 : 2)
.FirstOrDefault()
}).
FirstOrDefault(ppc => ppc.Item.Id == choiceId);
if (propChoice.ItemTranslation != null)
{
propFacet.PropertyValues.Add(new SelectedFacetItem
{
Id = propertyValue,
Name = propChoice.ItemTranslation.DisplayName
});
}
}
else
{
propFacet.PropertyValues.Add(new SelectedFacetItem
{
Id = propertyValue,
Name = propertyValue
});
}
result.SelectedProperties.Add(propFacet);
}
}
result.SelectedMinPrice = query.MinPrice ?? result.MinPrice;
result.SelectedMaxPrice = query.MaxPrice ?? result.MaxPrice;
var step1a = dbQueryJ.
OrderByDescending(y => y.so.Count).
ThenByDescending(y => y.so.Score).
Select(y => y.p.bvin);
result.TotalCount = step1a.Count();
result.Products = step1a.
Skip(skip).
Take(pageSize).
ToList().
Select(guid => DataTypeHelper.GuidToBvin(guid)).
ToList();
return(result);
}
}
public ProductSearchResultAdv DoSearch(List <long> wordIds, ProductSearchQueryAdv query, int pageNumber,
int pageSize)
{
return(DoSearchBySite(-1, wordIds, query, pageNumber, pageSize));
}
public ProductSearchResultAdv DoSearch(long siteId, string query, string culture, ProductSearchQueryAdv queryAdv,
int pageNumber, int pageSize)
{
if (!string.IsNullOrWhiteSpace(query))
{
// Parse Query into words
var parts = TextParser.ParseText(query, culture);
// Get wordIds for all words in query
var wordIds = FindAllWordIds(parts, culture);
if (siteId > 0)
{
return(provider.DoSearchBySite(siteId, wordIds, queryAdv, pageNumber, pageSize));
}
return(provider.DoSearch(wordIds, queryAdv, pageNumber, pageSize));
}
return(provider.DoSearchBySite(siteId, queryAdv, pageNumber, pageSize));
}
public ProductSearchResultAdv DoSearch(string query, string culture, ProductSearchQueryAdv queryAdv,
int pageNumber, int pageSize)
{
return(DoSearch(-1, query, culture, queryAdv, pageNumber, pageSize));
}
