/// <summary>
/// 商品属性、分类、品牌搜索
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public SearchProductFilterResult SearchProductFilter(SearchProductQuery query)
{
try
{
var result = new SearchProductFilterResult();
var where = new Sql();
GetSearchWhere(query, where);
//将参数转为MySql参数
//var @params = temp.ParameterNames.Select(name => new MySqlParameter("@" + name, temp.Get<object>(name))).ToArray();
//取出缓存中的属性数据
var listAttr = Cache.Get <List <AttributeInfo> >(CacheKeyCollection.CACHE_ATTRIBUTE_LIST);
var listAttrVal = Cache.Get <List <AttributeValueInfo> >(CacheKeyCollection.CACHE_ATTRIBUTEVALUE_LIST);
//如果没有则从数据库查询半缓存，缓存时间为默认时间
if (listAttr == null)
{
listAttr = DbFactory.Default.Get <AttributeInfo>().ToList();
Cache.Insert(CacheKeyCollection.CACHE_ATTRIBUTE_LIST, listAttr);
}
//如果没有则从数据库查询半缓存，缓存时间为默认时间
if (listAttrVal == null)
{
listAttrVal = DbFactory.Default.Get <AttributeValueInfo>().ToList();
Cache.Insert(CacheKeyCollection.CACHE_ATTRIBUTEVALUE_LIST, listAttrVal);
}
//查出符合条件的属性值
string attrSql = "select AttrValues from Mall_searchproduct s left join Mall_Product p on p.Id=s.ProductId " + where.SQL;
var sttrValueIds = DbFactory.Default.Query <string>(attrSql, where.Arguments).ToList();
//按','拆分
var valueIds = sttrValueIds.Where(i => !string.IsNullOrWhiteSpace(i)).SelectMany(item => item.Split(',')).ToList();
//过滤符合结果的属性值
listAttrVal = listAttrVal.Where(r => valueIds.Contains(r.Id.ToString())).ToList();
listAttr = listAttr.Where(r => listAttrVal.Select(z => z.AttributeId).Contains(r.Id)).ToList();
result.Attribute = listAttr.Select(r => new AttributeView()
{
AttrId = r.Id,
Name = r.Name,
AttrValues = listAttrVal.Where(z => z.AttributeId == r.Id).Select(s =>
new AttributeValue()
{
Id = s.Id,
Name = s.Value
}).ToList()
}).ToList();
//查询符合条件的品牌
var brandSql = "select DISTINCT s.BrandId Id,BrandName Name,BrandLogo Logo from Mall_searchproduct s left join Mall_Product p on p.Id=s.ProductId " + where.SQL + " and s.BrandId is not null AND s.BrandId<>0";
result.Brand = DbFactory.Default.Query <BrandView>(brandSql, where.Arguments).ToList();
var allCategorys = EngineContext.Current.Resolve <ICategoryService>().GetCategories();
//查询符合条件的分类
var categorySql = "select FirstCateId,MAX(FirstCateName) FirstCateName,SecondCateId,MAX(SecondCateName) SecondCateName,ThirdCateId,MAX(ThirdCateName) ThirdCateName from Mall_searchproduct s left join Mall_Product p on p.Id=s.ProductId " + where.SQL + " group by FirstCateId , SecondCateId , ThirdCateId ";
var data = DbFactory.Default.Query <CategorySeachModel>(categorySql, where.Arguments).ToList();
result.Category = data.GroupBy(item => item.FirstCateId).Select(first => new CategoryView//根据一级分类分组
{
Id = first.Key,
Name = first.Select(item => item.FirstCateName).FirstOrDefault(),
IsShow = allCategorys.FirstOrDefault(a => a.Id == first.Key) != null ? allCategorys.FirstOrDefault(a => a.Id == first.Key).IsShow : false,
SubCategory = first.GroupBy(item => item.SecondCateId).Select(second => new CategoryView//根据二级分类分组
{
Id = second.Key,
Name = second.Select(item => item.SecondCateName).FirstOrDefault(),
IsShow = allCategorys.FirstOrDefault(a => a.Id == second.Key) != null ? allCategorys.FirstOrDefault(a => a.Id == second.Key).IsShow : false,
SubCategory = second.GroupBy(item => item.ThirdCateId).Select(three => new CategoryView//根据三级分类分组
{
Id = three.Key,
Name = three.Select(item => item.ThirdCateName).FirstOrDefault(),
IsShow = allCategorys.FirstOrDefault(a => a.Id == three.Key) != null ? allCategorys.FirstOrDefault(a => a.Id == three.Key).IsShow : false
}).ToList()
}).ToList()
}).ToList();
result.Category = result.Category.Where(a => a.IsShow.HasValue && a.IsShow.Value).ToList();
result.Category.ForEach(r =>
{
r.SubCategory = r.SubCategory.Where(a => a.IsShow.HasValue && a.IsShow.Value).ToList();
r.SubCategory.ForEach(p =>
{
p.SubCategory = p.SubCategory.Where(b => b.IsShow.HasValue && b.IsShow.Value).ToList();
});
});
return(result);
}
catch (Exception ex)
{
Log.Error("搜索不出来了：", ex);
return(new SearchProductFilterResult());
}
}
/// <summary>
/// 诊疗项目属性、分类、品牌搜索
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public SearchProductFilterResult SearchProductFilter(SearchProductQuery query)
{
try
{
SearchProductFilterResult result = new SearchProductFilterResult();
DynamicParameters parms = new DynamicParameters();
string sql = "select DISTINCT Id,FirstCateId,FirstCateName,SecondCateId,SecondCateName,ThirdCateId,ThirdCateName,BrandId,BrandName,BrandLogo,AttrValues from himall_searchproducts ps ";
string where = GetSearchWhere(query, parms);
string order = GetSearchOrder(query);
string index = GetForceIndex(query);
string page = string.Empty;
string AttrValueIds = string.Empty;
bool hasAttrCache = false;
List <dynamic> data = null;
List <AttributeInfo> listAttr = new List <AttributeInfo>();
List <AttributeValueInfo> listAttrVal = new List <AttributeValueInfo>();
if (Cache.Exists(CacheKeyCollection.CACHE_ATTRIBUTE_LIST) && Cache.Exists(CacheKeyCollection.CACHE_ATTRIBUTEVALUE_LIST))
{
hasAttrCache = true;
listAttr = Cache.Get <List <AttributeInfo> >(CacheKeyCollection.CACHE_ATTRIBUTE_LIST);
listAttrVal = Cache.Get <List <AttributeValueInfo> >(CacheKeyCollection.CACHE_ATTRIBUTEVALUE_LIST);
}
using (MySqlConnection conn = new MySqlConnection(Connection.ConnectionString))
{
data = conn.Query(string.Concat(sql, index, where, order, page), parms).ToList();
foreach (dynamic o in data)
{
AttrValueIds += o.AttrValues;
}
if (!hasAttrCache)
{
sql = "SELECT * FROM HiMall_Attributes";
listAttr = conn.Query <AttributeInfo>(sql).ToList();
sql = "SELECT * FROM himall_attributevalues";
listAttrVal = conn.Query <AttributeValueInfo>(sql).ToList();
}
}
if (!hasAttrCache)
{
Cache.Insert <List <AttributeInfo> >(CacheKeyCollection.CACHE_ATTRIBUTE_LIST, listAttr);
Cache.Insert <List <AttributeValueInfo> >(CacheKeyCollection.CACHE_ATTRIBUTEVALUE_LIST, listAttrVal);
}
List <string> ValueIds = AttrValueIds.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
listAttrVal = listAttrVal.Where(r => ValueIds.Contains(r.Id.ToString())).ToList();
listAttr = listAttr.Where(r => listAttrVal.Select(z => z.AttributeId).Contains(r.Id)).ToList();
result.Attribute = listAttr.Select(r => new AttributeView()
{
AttrId = r.Id,
Name = r.Name,
AttrValues = listAttrVal.Where(z => z.AttributeId == r.Id).Select(s =>
new AttributeValue()
{
Id = s.Id,
Name = s.Value
}).ToList()
}).ToList();
result.Brand = data.Where((x, i) => data.FindIndex(z => z.BrandId == x.BrandId && z.BrandId > 0) == i)
.Select(p => new BrandView()
{
Id = p.BrandId, Name = p.BrandName, Logo = p.BrandLogo
}).ToList();
result.Category = data.Where((x, i) => data.FindIndex(z => z.FirstCateId == x.FirstCateId) == i) //去重
.Select(f => new CategoryView()
{
Id = f.FirstCateId,
Name = f.FirstCateName,
SubCategory = data.Where((x, i) => data.FindIndex(z => z.SecondCateId == x.SecondCateId) == i) //二级去重
.Where(r => r.FirstCateId == f.FirstCateId) //查找指定一级分类的下级
.Select(s => new CategoryView()
{
Id = s.SecondCateId,
Name = s.SecondCateName,
SubCategory = data.Where((x, i) => data.FindIndex(z => z.ThirdCateId == x.ThirdCateId) == i) //三级去重
.Where(r => r.SecondCateId == s.SecondCateId) //查找指定二级分类的下级
.Select(t => new CategoryView()
{
Id = t.ThirdCateId,
Name = t.ThirdCateName
}).ToList()
}).ToList()
}).ToList();
return(result);
}
catch (Exception ex)
{
Log.Error("搜索不出来了：", ex);
return(new SearchProductFilterResult());
}
}