/// <summary>
/// 获取搜索排序sql
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
private void GetAppletSearchOrder(SearchProductQuery query, Sql order)
{
switch (query.OrderKey)
{
case 2:
order.Append(" ORDER BY ps.SaleCount ");
break;
case 3:
order.Append(" ORDER BY ps.SalePrice ");
break;
case 4:
order.Append(" ORDER BY ps.Comments ");
break;
case 5:
order.Append(" ORDER BY ps.OnSaleTime ");
break;
default:
order.Append(" ORDER BY ps.Id ");
break;
}
if (!query.OrderType)
{
order.Append(" DESC ");
}
else
{
order.Append(" ASC ");
}
}
/// <summary>
/// 非主键排序时强制使用索引
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
private string GetForceIndex(SearchProductQuery query)
{
if (!string.IsNullOrEmpty(query.Keyword))
{
return(string.Empty);
}
string index = string.Empty;
switch (query.OrderKey)
{
case 2:
index = " FORCE INDEX(IX_SaleCount) ";
break;
case 3:
index = " FORCE INDEX(IX_SalePrice) ";
break;
case 4:
index = " FORCE INDEX(IX_Comments) ";
break;
case 5:
index = " FORCE INDEX(IX_OnSaleTime) ";
break;
}
return(index);
}
public ActionResult Index(ProductSearchCriteria criteria)
{
var sqlQuery = new SqlSearchProductQuery(criteria);
var watch = System.Diagnostics.Stopwatch.StartNew();
var result = sqlQuery.Execute();
watch.Stop();
var sqlElapsedMs = watch.ElapsedMilliseconds;
var redisQuery = new SearchProductQuery(criteria);
watch = System.Diagnostics.Stopwatch.StartNew();
result = redisQuery.Execute();
watch.Stop();
var redisElapsedMs = watch.ElapsedMilliseconds;
var viewModel = new SearchResultViewModel <ProductModel>();
viewModel.Data = result;
viewModel.SqlQueryTime = sqlElapsedMs;
viewModel.RedisQueryTime = redisElapsedMs;
ViewBag.Heading = "Compare View";
return(View(viewModel));
}
/// <summary>
/// 商品搜索
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public SearchProductResult SearchProduct(SearchProductQuery query)
{
//string selComments = "s.Comments";
//以前评论条数是搜索表里的 统计的所有评论条数
string selComments = "(SELECT COUNT(*) FROM Mall_productcomment pc WHERE pc.productid=s.productid AND pc.Ishidden=0) as Comments";//搜索这里评论数获取排除隐藏的条数
SearchProductResult result = new SearchProductResult();
string countsql = "select count(1) from Mall_searchproduct s ";
string sql = "select (p.SaleCounts+ IFNULL(p.VirtualSaleCounts,0)) as SaleCount, s.ProductId,s.ProductName,s.SalePrice,s.ImagePath,s.ShopId,s.ShopName,s.ThirdCateId,p.DisplaySequence,p.ShopDisplaySequence,p.ProductType,IFNULL(p.VirtualSaleCounts,0) as VirtualSaleCounts,p.MinSalePrice," + selComments + " from Mall_searchproduct s ";
var where = new Sql();
GetSearchWhere(query, where);
var order = new Sql();
GetSearchOrder(query, order);
string index = GetForceIndex(query);
string page = GetSearchPage(query);
index += string.Format(" left join Mall_Product p on p.Id=s.ProductId ");//FORCE INDEX要放在join之前
countsql += string.Format(" left join Mall_Product p on p.Id=s.ProductId ");
result.Data = DbFactory.Default.Query <ProductView>(string.Concat(sql, index, where.SQL, order.SQL, page), where.Arguments).ToList();
result.Total = DbFactory.Default.ExecuteScalar <int>(string.Concat(countsql, where.SQL), where.Arguments);
return(result);
}
public JsonResult GetSearchFilter(string keyword, long cid = 0, long b_id = 0, string a_id = "")
{
if (string.IsNullOrEmpty(keyword) && cid <= 0 && b_id <= 0 && a_id == "")
{
keyword = Application.SiteSettingApplication.SiteSettings.Keyword;
}
string cacheKey = CacheKeyCollection.CACHE_SEARCHFILTER(keyword, cid, b_id, a_id);
//if (Core.Cache.Exists(cacheKey))
// return Core.Cache.Get(cacheKey) as JsonResult;
SearchProductQuery query = new SearchProductQuery()
{
Keyword = keyword,
AttrValIds = a_id.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList(),
BrandId = b_id,
FilterVirtualProduct = true
};
if (cid != 0)
{
var catelist = _iCategoryService.GetCategories();
var cate = catelist.FirstOrDefault(r => r.Id == cid);
if (cate.Depth == 1)
{
query.FirstCateId = cid;
}
else if (cate.Depth == 2)
{
query.SecondCateId = cid;
}
else if (cate.Depth == 3)
{
query.ThirdCateId = cid;
}
}
if (!string.IsNullOrWhiteSpace(keyword) && keyword.Length == 1)
{
query.IsLikeSearch = true;
}
var result = _iSearchProductService.SearchProductFilter(query);
foreach (BrandView brand in result.Brand)
{
brand.Logo = Mall.Core.MallIO.GetImagePath(brand.Logo);
}
JsonResult json = Json <dynamic>(success: true, data: new { Attr = result.Attribute, Brand = result.Brand, Category = result.Category }); //json = Json(new { success = true, Attr = result.Attribute, Brand = result.Brand, Category = result.Category });
json = Json <dynamic>(success: true, data: new { Attr = result.Attribute, Brand = result.Brand, Category = result.Category });
Core.Cache.Insert(cacheKey, json, 300);
return(json);
}
public ActionResult Search(ProductSearchCriteria criteria)
{
var watch = System.Diagnostics.Stopwatch.StartNew();
var query = new SearchProductQuery(criteria);
var result = query.Execute();
watch.Stop();
var elapsedMs = watch.ElapsedMilliseconds;
var viewModel = new SearchResultViewModel <ProductModel>();
viewModel.Data = result;
viewModel.QueryTime = elapsedMs;
return(PartialView("~/Views/Products/_Search.cshtml", viewModel));
}
public ActionResult Index(ProductSearchCriteria criteria)
{
var watch = System.Diagnostics.Stopwatch.StartNew();
var query = new SearchProductQuery(criteria);
var result = query.Execute();
watch.Stop();
var elapsedMs = watch.ElapsedMilliseconds;
var viewModel = new SearchResultViewModel <ProductModel>();
viewModel.Data = result;
viewModel.QueryTime = elapsedMs;
ViewBag.Heading = "Query with Redis and Elastic Search";
ViewBag.IsSql = false;
return(View(viewModel));
}
public JsonResult GetSearchFilter(string keyword, long cid, long b_id, string a_id)
{
string cacheKey = CacheKeyCollection.CACHE_SEARCHFILTER(keyword, cid, b_id, a_id);
if (Core.Cache.Exists(cacheKey))
{
return(Core.Cache.Get(cacheKey) as JsonResult);
}
SearchProductQuery query = new SearchProductQuery()
{
Keyword = keyword,
AttrValIds = a_id.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList(),
BrandId = b_id,
};
if (cid != 0)
{
var catelist = _iCategoryService.GetCategories();
var cate = catelist.FirstOrDefault(r => r.Id == cid);
if (cate.Depth == 1)
{
query.FirstCateId = cid;
}
else if (cate.Depth == 2)
{
query.SecondCateId = cid;
}
else if (cate.Depth == 3)
{
query.ThirdCateId = cid;
}
}
var result = _iSearchProductService.SearchProductFilter(query);
foreach (BrandView brand in result.Brand)
{
brand.Logo = Himall.Core.HimallIO.GetImagePath(brand.Logo);
}
JsonResult json = Json(new { success = true, Attr = result.Attribute, Brand = result.Brand, Category = result.Category }, JsonRequestBehavior.AllowGet);
Core.Cache.Insert(cacheKey, json, 300);
return(json);
}
/// <summary>
/// 获取搜索排序sql
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
private void GetSearchOrder(SearchProductQuery query, Sql order)
{
switch (query.OrderKey)
{
case 2:
order.Append(" ORDER BY SaleCount ");
break;
case 3:
order.Append(" ORDER BY s.SalePrice ");
break;
case 4:
//order.Append(" ORDER BY s.Comments ");
order.Append(" ORDER BY Comments "); //Comments现是子查询字段不是搜索产品表字段，则不需s.别名
break;
case 5:
order.Append(" ORDER BY s.OnSaleTime ");
break;
default:
if (query.ShopId > 0 || query.VShopId > 0)
{
order.Append(" ORDER BY p.ShopDisplaySequence desc , SaleCount desc , s.OnSaleTime desc ");
}
else
{
//按最新的排序规则作为默认排序【序号越大，在前台展示的商品越靠前，序号一致时，优先销量排前，销量一致时，优先上架时间排前】
order.Append(" ORDER BY p.DisplaySequence desc , SaleCount desc ,s.OnSaleTime desc ");
}
break;
}
if (query.OrderKey > 1)//如果是默认，则按上面执行
{
if (!query.OrderType)
{
order.Append(" DESC ");
}
else
{
order.Append(" ASC ");
}
}
}
public object GetSearchFilter(string keyword = "", long cid = 0, long b_id = 0, string a_id = "")
{
if (string.IsNullOrEmpty(keyword) && cid <= 0 && b_id <= 0 && a_id == "")
{
keyword = Application.SiteSettingApplication.SiteSettings.Keyword;
}
SearchProductQuery query = new SearchProductQuery()
{
Keyword = keyword,
AttrValIds = a_id.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList(),
BrandId = b_id,
FilterVirtualProduct = true
};
if (cid != 0)
{
var catelist = ServiceProvider.Instance <ICategoryService> .Create.GetCategories();
var cate = catelist.FirstOrDefault(r => r.Id == cid);
if (cate.Depth == 1)
{
query.FirstCateId = cid;
}
else if (cate.Depth == 2)
{
query.SecondCateId = cid;
}
else if (cate.Depth == 3)
{
query.ThirdCateId = cid;
}
}
var result = ServiceProvider.Instance <ISearchProductService> .Create.SearchProductFilter(query);
foreach (BrandView brand in result.Brand)
{
brand.Logo = Himall.Core.HimallIO.GetImagePath(brand.Logo);
}
return(new { success = true, Attrs = result.Attribute, Brand = result.Brand, Category = result.Category });
}
/// <summary>
/// 诊疗项目搜索
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public SearchProductResult SearchAppletProduct(SearchProductQuery query)
{
SearchProductResult result = new SearchProductResult();
DynamicParameters parms = new DynamicParameters();
string countsql = "select count(1) from himall_searchproducts ps ";
string sql = "select ps.ProductId,ps.ProductName,ps.SalePrice,ps.ImagePath,ps.ShopId,ps.ShopName,ps.SaleCount,ps.ThirdCateId,ps.Comments,pt.HasSKU,pt.MinSalePrice,(select id from Himall_SKUs where ProductId=ps.ProductId order by id desc limit 1) as SkuId,IFNULL((select Sum(Quantity) from Himall_ShoppingCarts cs where cs.ProductId=ps.ProductId),0) as cartquantity from himall_searchproducts ps left join Himall_Products pt on ps.ProductId=pt.Id ";
string where = GetAppletSearchWhere(query, parms);
string order = GetAppletSearchOrder(query);
string index = GetForceIndex(query);
string page = GetSearchPage(query);
using (MySqlConnection conn = new MySqlConnection(Connection.ConnectionString))
{
result.Data = conn.Query <ProductView>(string.Concat(sql, index, where, order, page), parms).ToList();
result.Total = int.Parse(conn.ExecuteScalar(string.Concat(countsql, where), parms).ToString());
}
return(result);
}
/// <summary>
/// 诊疗项目搜索
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public SearchProductResult SearchProduct(SearchProductQuery query)
{
SearchProductResult result = new SearchProductResult();
DynamicParameters parms = new DynamicParameters();
string countsql = "select count(1) from himall_searchproducts ";
string sql = "select ProductId,ProductName,SalePrice,ImagePath,ShopId,ShopName,SaleCount,ThirdCateId,Comments from himall_searchproducts ";
string where = GetSearchWhere(query, parms);
string order = GetSearchOrder(query);
string index = GetForceIndex(query);
string page = GetSearchPage(query);
using (MySqlConnection conn = new MySqlConnection(Connection.ConnectionString))
{
result.Data = conn.Query <ProductView>(string.Concat(sql, index, where, order, page), parms).ToList();
result.Total = int.Parse(conn.ExecuteScalar(string.Concat(countsql, where), parms).ToString());
}
return(result);
}
/// <summary>
/// 获取搜索排序sql
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
private string GetAppletSearchOrder(SearchProductQuery query)
{
string order = string.Empty;
switch (query.OrderKey)
{
case 2:
order = " ORDER BY ps.SaleCount ";
break;
case 3:
order = " ORDER BY ps.SalePrice ";
break;
case 4:
order = " ORDER BY ps.Comments ";
break;
case 5:
order = " ORDER BY ps.OnSaleTime ";
break;
default:
order = " ORDER BY ps.Id ";
break;
}
if (!query.OrderType)
{
order += " DESC ";
}
else
{
order += " ASC ";
}
return(order);
}
private SearchProductResult DoSearch(
string keywords = "", /* 搜索关键字 */
long cid = 0, /* 分类ID */
long b_id = 0, /* 品牌ID */
string a_id = "", /* 属性ID, 表现形式：attrId_attrValueId */
int orderKey = 1, /* 排序项（1：默认，2：销量，3：价格，4：评论数，5：上架时间） */
int orderType = 1, /* 排序方式（1：升序，2：降序） */
int pageNo = 1, /*页码*/
int pageSize = 10, /*每页显示数据量*/
long vshopId = 0 //店铺ID
)
{
#region 初始化查询Model
SearchProductQuery model = new SearchProductQuery();
model.ShopId = 0;
model.BrandId = b_id;
if (cid != 0)
{
var catelist = _iCategoryService.GetCategories();
var cate = catelist.FirstOrDefault(r => r.Id == cid);
if (cate != null)
{
if (cate.Depth == 1)
{
model.FirstCateId = cid;
}
else if (cate.Depth == 2)
{
model.SecondCateId = cid;
}
else if (cate.Depth == 3)
{
model.ThirdCateId = cid;
}
}
}
orderType = orderKey == 3 ? orderType : 2;
model.AttrValIds = a_id.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
model.Keyword = keywords;
model.OrderKey = orderKey;
model.OrderType = orderType == 1;
model.PageNumber = pageNo;
model.PageSize = pageSize;
model.VShopId = vshopId;
if (!string.IsNullOrWhiteSpace(model.Keyword) && model.Keyword.Length == 1)
{
model.IsLikeSearch = true;
}
#endregion
SearchProductResult result = _iSearchProductService.SearchProduct(model);
int total = result.Total;
var siteSetingInfo = SiteSettingApplication.SiteSettings;
bool isShow = siteSetingInfo != null && siteSetingInfo.ProductSaleCountOnOff == 1;
//当查询的结果少于一页时用like进行补偿（与PC端同步）
if (result.Total < pageSize)
{
model.IsLikeSearch = true;
SearchProductResult result2 = _iSearchProductService.SearchProduct(model);
var idList1 = result.Data.Select(a => a.ProductId).ToList();
var nresult = result2.Data.Where(a => !idList1.Contains(a.ProductId)).ToList();
if (nresult.Count > 0)
{
result.Total += nresult.Count;
result.Data.AddRange(nresult);
}
//补充数据后，重新排序
Func <IEnumerable <ProductView>, IOrderedEnumerable <ProductView> > orderby = null;
Func <IEnumerable <ProductView>, IOrderedEnumerable <ProductView> > orderByDesc = null;
switch (model.OrderKey)
{
case 2:
//order.Append(" ORDER BY SaleCount ");
orderby = e => e.OrderBy(p => p.SaleCount + Core.Helper.TypeHelper.ObjectToInt(p.VirtualSaleCounts));
orderByDesc = e => e.OrderByDescending(p => p.SaleCount + Core.Helper.TypeHelper.ObjectToInt(p.VirtualSaleCounts));
break;
case 3:
//order.Append(" ORDER BY SalePrice ");
orderby = e => e.OrderBy(p => p.SalePrice);
orderByDesc = e => e.OrderByDescending(p => p.SalePrice);
break;
case 4:
//order.Append(" ORDER BY Comments ");
orderby = e => e.OrderBy(p => p.Comments);
orderByDesc = e => e.OrderByDescending(p => p.Comments);
break;
default:
//order.Append(" ORDER BY Id ");
//orderby = e => e.OrderBy(p => p.ProductId);
//orderByDesc = e => e.OrderByDescending(p => p.ProductId);
//break;
//按最新的排序规则作为默认排序【序号越大，在前台展示的商品越靠前，序号一致时，优先销量排前，销量一致时，优先上架时间排前】
if (isShow)
{
orderByDesc = e => e.OrderByDescending(p => p.DisplaySequence).ThenByDescending(p => p.SaleCount + Core.Helper.TypeHelper.ObjectToInt(p.VirtualSaleCounts)).ThenByDescending(p => p.ProductId);
}
else
{
orderByDesc = e => e.OrderByDescending(p => p.DisplaySequence).ThenByDescending(p => p.ProductId);
}
break;
}
if (model.OrderKey > 1)
{
if (model.OrderType)
{
result.Data = orderby(result.Data).ToList();
}
else
{
result.Data = orderByDesc(result.Data).ToList();
}
}
else
{
result.Data = orderByDesc(result.Data).ToList();
}
}
total = result.Total;
//补商品状态
foreach (var item in result.Data)
{
var _pro = _iProductService.GetProduct(item.ProductId);
var skus = _iProductService.GetSKUs(item.ProductId);
if (_pro == null || skus == null)
{
continue;
}
if (_pro.SaleStatus == Entities.ProductInfo.ProductSaleStatus.OnSale && _pro.AuditStatus == Entities.ProductInfo.ProductAuditStatus.Audited)
{
item.ShowStatus = 0;
if (skus.Sum(d => d.Stock) < 1)
{
item.ShowStatus = 2;
}
}
else
{
if (_pro.AuditStatus == Entities.ProductInfo.ProductAuditStatus.Audited && _pro.SaleStatus == Entities.ProductInfo.ProductSaleStatus.InStock)
{
item.ShowStatus = 3;
}
else
{
item.ShowStatus = 1;
}
}
}
if (Core.MallIO.GetMallIO().GetType().FullName.Equals("Mall.Strategy.OSS"))
{
ViewBag.IsOss = true;
}
else
{
ViewBag.IsOss = false;
}
ViewBag.keywords = model.Keyword;
ViewBag.Total = total;
ViewBag.cid = cid;
ViewBag.b_id = b_id;
ViewBag.a_id = a_id;
ViewBag.orderKey = orderKey;
ViewBag.orderType = orderType;
ViewBag.isSaleCountOnOff = isShow;
return(result);
}
// GET: Web/Search/SearchAd
/// <summary>
/// 商品搜索页面
/// </summary>
/// <param name="keywords">搜索关键字</param>
/// <param name="cid">分类ID</param>
/// <param name="b_id">品牌ID</param>
/// <param name="a_id">属性ID, 表现形式：attrId_attrValueId</param>
/// <param name="orderKey">序项（1：默认，2：销量，3：价格，4：评论数，5：上架时间）</param>
/// <param name="orderType">排序方式（1：升序，2：降序）</param>
/// <param name="pageNo">页码</param>
/// <param name="pageSize">每页显示数据量</param>
/// <returns></returns>
public ActionResult SearchAd(
string keywords = "", /* 搜索关键字 */
long cid = 0, /* 分类ID */
long b_id = 0, /* 品牌ID */
string a_id = "", /* 属性值ID, 表现形式：valueid,valueid */
int orderKey = 1, /* 排序项（1：默认，2：销量，3：价格，4：评论数，5：上架时间） */
int orderType = 1, /* 排序方式（1：升序，2：降序） */
int pageNo = 1, /*页码*/
int pageSize = 60 /*每页显示数据量*/
)
{
try
{
var siteSetingInfo = SiteSettingApplication.SiteSettings;
bool isShow = siteSetingInfo.ProductSaleCountOnOff == 1;
if (string.IsNullOrEmpty(keywords) && cid <= 0 && b_id <= 0 && a_id == "")
{
keywords = siteSetingInfo.Keyword;
}
#region 初始化查询Model
SearchProductQuery model = new SearchProductQuery();
model.ShopId = 0;
model.BrandId = b_id;
if (cid != 0)
{
var catelist = _iCategoryService.GetCategories();
var cate = catelist.FirstOrDefault(r => r.Id == cid);
if (cate != null)
{
if (cate.Depth == 1)
{
model.FirstCateId = cid;
}
else if (cate.Depth == 2)
{
model.SecondCateId = cid;
}
else if (cate.Depth == 3)
{
model.ThirdCateId = cid;
}
ViewBag.pageTitle = cate.Name;
}
}
model.AttrValIds = a_id.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
model.Keyword = keywords;
model.OrderKey = orderKey;
model.OrderType = orderType == 1;
model.PageNumber = pageNo;
model.PageSize = pageSize;
#endregion
SearchProductResult result = _iSearchProductService.SearchProduct(model);
int total = result.Total;
//当查询的结果少于一页时用like进行补偿
if (result.Total < pageSize)
{
model.IsLikeSearch = true;
SearchProductResult result2 = _iSearchProductService.SearchProduct(model);
var idList1 = result.Data.Select(a => a.ProductId).ToList();
var nresult = result2.Data.Where(a => !idList1.Contains(a.ProductId)).ToList();
if (nresult.Count > 0)
{
result.Total += nresult.Count;
result.Data.AddRange(nresult);
}
//补充数据后，重新排序
Func <IEnumerable <ProductView>, IOrderedEnumerable <ProductView> > orderby = null;
Func <IEnumerable <ProductView>, IOrderedEnumerable <ProductView> > orderByDesc = null;
switch (model.OrderKey)
{
case 2:
//order.Append(" ORDER BY SaleCount ");
orderby = e => e.OrderBy(p => p.SaleCount + TypeHelper.ObjectToInt(p.VirtualSaleCounts));
orderByDesc = e => e.OrderByDescending(p => p.SaleCount + TypeHelper.ObjectToInt(p.VirtualSaleCounts));
break;
case 3:
//order.Append(" ORDER BY SalePrice ");
orderby = e => e.OrderBy(p => p.SalePrice);
orderByDesc = e => e.OrderByDescending(p => p.SalePrice);
break;
case 4:
//order.Append(" ORDER BY Comments ");
orderby = e => e.OrderBy(p => p.Comments);
orderByDesc = e => e.OrderByDescending(p => p.Comments);
break;
default:
//order.Append(" ORDER BY Id ");
//按最新的排序规则作为默认排序【序号越大，在前台展示的商品越靠前，序号一致时，优先销量排前，销量一致时，优先上架时间排前】
//orderby = e => e.OrderBy(p => p.ProductId);
if (isShow)
{ //底层已经将虚拟销量累加到销量中
orderByDesc = e => e.OrderByDescending(p => p.DisplaySequence).ThenByDescending(p => p.SaleCount).ThenByDescending(p => p.ProductId);
}
else
{
orderByDesc = e => e.OrderByDescending(p => p.DisplaySequence).ThenByDescending(p => p.ProductId);
}
break;
}
if (model.OrderKey > 1)
{
if (model.OrderType)
{
result.Data = orderby(result.Data).ToList();
}
else
{
result.Data = orderByDesc(result.Data).ToList();
}
}
else
{
result.Data = orderByDesc(result.Data).ToList();
}
}
if (result.Total == 0)
{
ViewBag.BrowsedHistory = BrowseHistrory.GetBrowsingProducts(13, CurrentUser == null ? 0 : CurrentUser.Id);
var category = _iCategoryService.GetCategory(model.ThirdCateId);
string categoryName = category == null ? string.Empty : category.Name;
var brand = _iBrandService.GetBrand(b_id) ?? new Entities.BrandInfo();
string bname = brand == null ? "" : brand.Name;
ViewBag.categoryName = categoryName;
ViewBag.bName = bname;
}
total = result.Total;
//补商品状态
foreach (var item in result.Data)
{
var _pro = _iProductService.GetProduct(item.ProductId);
var skus = ProductManagerApplication.GetSKUs(item.ProductId);
if (_pro == null || skus == null)
{
continue;
}
if (_pro.SaleStatus == Entities.ProductInfo.ProductSaleStatus.OnSale && _pro.AuditStatus == Entities.ProductInfo.ProductAuditStatus.Audited)
{
item.ShowStatus = 0;
if (skus.Sum(d => d.Stock) < 1)
{
item.ShowStatus = 2;
}
}
else
{
if (_pro.AuditStatus == Entities.ProductInfo.ProductAuditStatus.Audited && _pro.SaleStatus == Entities.ProductInfo.ProductSaleStatus.InStock)
{
item.ShowStatus = 3;
}
else
{
item.ShowStatus = 1;
}
}
}
if (Core.HimallIO.GetHimallIO().GetType().FullName.Equals("Himall.Strategy.OSS"))
{
ViewBag.IsOss = true;
}
else
{
ViewBag.IsOss = false;
}
ViewBag.keywords = model.Keyword;
ViewBag.cid = cid;
ViewBag.b_id = b_id;
ViewBag.a_id = a_id;
ViewBag.orderKey = orderKey;
ViewBag.orderType = orderType;
#region 分页控制
PagingInfo info = new PagingInfo
{
CurrentPage = model.PageNumber,
ItemsPerPage = pageSize,
TotalItems = total
};
ViewBag.pageInfo = info;
#endregion
ViewBag.isSaleCountOnOff = isShow;
return(View(result.Data));
}
catch (Exception e)
{
throw e;
}
}
public object GetSearchProducts(
string keywords = "", /* 搜索关键字 */
long cid = 0, /* 分类ID */
long b_id = 0, /* 品牌ID */
string a_id = "", /* 属性ID, 表现形式：attrId_attrValueId */
int orderKey = 1, /* 排序项（1：默认，2：销量，3：价格，4：评论数，5：上架时间） */
int orderType = 1, /* 排序方式（1：升序，2：降序） */
int pageNo = 1, /*页码*/
int pageSize = 10, /*每页显示数据量*/
long vshopId = 0,
long sid = 0 /*商家ID*/
)
{
if (string.IsNullOrEmpty(keywords) && vshopId == 0 && cid <= 0 && b_id <= 0 && a_id == "")
{
keywords = Application.SiteSettingApplication.GetSiteSettings().Keyword;
}
#region 初始化查询Model
SearchProductQuery model = new SearchProductQuery();
model.VShopId = vshopId;
model.ShopId = sid;
model.BrandId = b_id;
if (vshopId == 0 && cid != 0)
{
var catelist = ServiceProvider.Instance <ICategoryService> .Create.GetCategories();
var cate = catelist.FirstOrDefault(r => r.Id == cid);
if (cate.Depth == 1)
{
model.FirstCateId = cid;
}
else if (cate.Depth == 2)
{
model.SecondCateId = cid;
}
else if (cate.Depth == 3)
{
model.ThirdCateId = cid;
}
}
else if (vshopId != 0 && cid != 0)
{
model.ShopCategoryId = cid;
}
model.AttrValIds = a_id.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
model.Keyword = keywords;
model.OrderKey = orderKey;
model.OrderType = orderType == 1;
model.PageNumber = pageNo;
model.PageSize = pageSize;
#endregion
SearchProductResult result = ServiceProvider.Instance <ISearchProductService> .Create.SearchProduct(model);
int total = result.Total;
//当查询的结果少于一页时用like进行补偿（与PC端同步）
if (result.Total < pageSize)
{
model.IsLikeSearch = true;
SearchProductResult result2 = ServiceProvider.Instance <ISearchProductService> .Create.SearchProduct(model);
var idList1 = result.Data.Select(a => a.ProductId).ToList();
var nresult = result2.Data.Where(a => !idList1.Contains(a.ProductId)).ToList();
if (nresult.Count > 0)
{
result.Total += nresult.Count;
result.Data.AddRange(nresult);
}
}
total = result.Total;
#region 价格更新
//会员折扣
decimal discount = 1M;
long SelfShopId = 0;
if (CurrentUser != null)
{
discount = CurrentUser.MemberDiscount;
var shopInfo = ShopApplication.GetSelfShop();
SelfShopId = shopInfo.Id;
}
var limit = LimitTimeApplication.GetLimitProducts();
var fight = FightGroupApplication.GetFightGroupPrice();
foreach (var item in result.Data)
{
item.ImagePath = Core.HimallIO.GetRomoteProductSizeImage(Core.HimallIO.GetImagePath(item.ImagePath), 1, (int)Himall.CommonModel.ImageSize.Size_350);
if (item.ShopId == SelfShopId)
{
item.SalePrice = item.SalePrice * discount;
}
var isLimit = limit.Where(r => r.ProductId == item.ProductId).FirstOrDefault();
var isFight = fight.Where(r => r.ProductId == item.ProductId).FirstOrDefault();
if (isLimit != null)
{
item.SalePrice = isLimit.MinPrice;
}
if (isFight != null)
{
item.SalePrice = isFight.ActivePrice;
}
}
#endregion
return(Json(new
{
Success = "true",
Product = result.Data,
keywords = model.Keyword,
Total = total,
cid = cid,
b_id = b_id,
a_id = a_id,
orderKey = orderKey,
orderType = orderType
}));
}
public object GetSearchProducts(
string keywords = "", /* 搜索关键字 */
long cid = 0, /* 分类ID */
long b_id = 0, /* 品牌ID */
string a_id = "", /* 属性ID, 表现形式：attrId_attrValueId */
int orderKey = 1, /* 排序项（1：默认，2：销量，3：价格，4：评论数，5：上架时间） */
int orderType = 1, /* 排序方式（1：升序，2：降序） */
int pageNo = 1, /*页码*/
int pageSize = 10, /*每页显示数据量*/
long vshopId = 0,
long sid = 0 /*商家ID*/
)
{
var siteSetingInfo = SiteSettingApplication.SiteSettings;
bool isShow = siteSetingInfo.ProductSaleCountOnOff == 1;
if (string.IsNullOrEmpty(keywords) && vshopId == 0 && cid <= 0 && b_id <= 0 && a_id == "")
{
keywords = siteSetingInfo.Keyword;
}
#region 初始化查询Model
SearchProductQuery model = new SearchProductQuery();
model.VShopId = vshopId;
model.ShopId = sid;
model.BrandId = b_id;
if (vshopId == 0 && cid != 0)
{
var catelist = ServiceProvider.Instance <ICategoryService> .Create.GetCategories();
var cate = catelist.FirstOrDefault(r => r.Id == cid);
if (cate != null)
{
if (cate.Depth == 1)
{
model.FirstCateId = cid;
}
else if (cate.Depth == 2)
{
model.SecondCateId = cid;
}
else if (cate.Depth == 3)
{
model.ThirdCateId = cid;
}
}
}
else if (vshopId != 0 && cid != 0)
{
model.ShopCategoryId = cid;
}
model.AttrValIds = a_id.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
model.Keyword = keywords;
model.OrderKey = orderKey;
model.OrderType = orderType == 1;
model.PageNumber = pageNo;
model.PageSize = pageSize;
#endregion
SearchProductResult result = ServiceProvider.Instance <ISearchProductService> .Create.SearchProduct(model);
int total = result.Total;
//当查询的结果少于一页时用like进行补偿（与PC端同步）
if (result.Total < pageSize)
{
model.IsLikeSearch = true;
SearchProductResult result2 = ServiceProvider.Instance <ISearchProductService> .Create.SearchProduct(model);
var idList1 = result.Data.Select(a => a.ProductId).ToList();
var nresult = result2.Data.Where(a => !idList1.Contains(a.ProductId)).ToList();
if (nresult.Count > 0)
{
result.Total += nresult.Count;
result.Data.AddRange(nresult);
}
//补充数据后，重新排序
Func <IEnumerable <ProductView>, IOrderedEnumerable <ProductView> > orderby = null;
Func <IEnumerable <ProductView>, IOrderedEnumerable <ProductView> > orderByDesc = null;
switch (model.OrderKey)
{
case 2:
//order.Append(" ORDER BY SaleCount ");
orderby = e => e.OrderBy(p => p.SaleCount + Himall.Core.Helper.TypeHelper.ObjectToInt(p.VirtualSaleCounts));
orderByDesc = e => e.OrderByDescending(p => p.SaleCount + Himall.Core.Helper.TypeHelper.ObjectToInt(p.VirtualSaleCounts));
break;
case 3:
//order.Append(" ORDER BY SalePrice ");
orderby = e => e.OrderBy(p => p.SalePrice);
orderByDesc = e => e.OrderByDescending(p => p.SalePrice);
break;
case 4:
//order.Append(" ORDER BY Comments ");
orderby = e => e.OrderBy(p => p.Comments);
orderByDesc = e => e.OrderByDescending(p => p.Comments);
break;
default:
//order.Append(" ORDER BY Id ");
//orderby = e => e.OrderBy(p => p.ProductId);
//orderByDesc = e => e.OrderByDescending(p => p.ProductId);
//break;
//按最新的排序规则作为默认排序【序号越大，在前台展示的商品越靠前，序号一致时，优先销量排前，销量一致时，优先上架时间排前】
if (isShow)
{
orderByDesc = e => e.OrderByDescending(p => p.DisplaySequence).ThenByDescending(p => p.SaleCount + Himall.Core.Helper.TypeHelper.ObjectToInt(p.VirtualSaleCounts)).ThenByDescending(p => p.ProductId);
}
else
{
orderByDesc = e => e.OrderByDescending(p => p.DisplaySequence).ThenByDescending(p => p.ProductId);
}
break;
}
if (model.OrderKey > 1)
{
if (model.OrderType)
{
result.Data = orderby(result.Data).ToList();
}
else
{
result.Data = orderByDesc(result.Data).ToList();
}
}
else
{
result.Data = orderByDesc(result.Data).ToList();
}
}
total = result.Total;
//补商品状态
foreach (var item in result.Data)
{
var pro = ServiceProvider.Instance <IProductService> .Create.GetProduct(item.ProductId);
var skus = ProductManagerApplication.GetSKUs(pro.Id);
if (pro == null || skus == null)
{
continue;
}
if (pro.SaleStatus == Entities.ProductInfo.ProductSaleStatus.OnSale && pro.AuditStatus == Entities.ProductInfo.ProductAuditStatus.Audited)
{
item.ShowStatus = 0;
if (skus.Sum(d => d.Stock) < 1)
{
item.ShowStatus = 2;
}
}
else
{
if (pro.AuditStatus == Entities.ProductInfo.ProductAuditStatus.Audited && pro.SaleStatus == Entities.ProductInfo.ProductSaleStatus.InStock)
{
item.ShowStatus = 3;
}
else
{
item.ShowStatus = 1;
}
}
}
#region 价格更新
//会员折扣
//decimal discount = 1M;
//long SelfShopId = 0;
//if (CurrentUser != null)
//{
// discount = CurrentUser.MemberDiscount;
// var shopInfo = ShopApplication.GetSelfShop();
// SelfShopId = shopInfo.Id;
//}
//var limit = LimitTimeApplication.GetLimitProducts();
var fight = FightGroupApplication.GetFightGroupPrice();
foreach (var item in result.Data)
{
item.ImagePath = Core.HimallIO.GetRomoteProductSizeImage(Core.HimallIO.GetImagePath(item.ImagePath), 1, (int)Himall.CommonModel.ImageSize.Size_350);
//if (item.ShopId == SelfShopId)
// item.SalePrice = item.SalePrice * discount;
//var isLimit = limit.Where(r => r.ProductId == item.ProductId).FirstOrDefault();
var isFight = fight.Where(r => r.ProductId == item.ProductId).FirstOrDefault();
//if (isLimit != null)
// item.SalePrice = isLimit.MinPrice;
if (isFight != null)
{
//item.SalePrice = isFight.ActivePrice;
item.FightGroupId = isFight.ActiveId;
}
}
#endregion
return(new
{
success = true,
Product = result.Data,
total = total,
keywords = model.Keyword,
cid = cid,
b_id = b_id,
a_id = a_id,
orderKey = orderKey,
orderType = orderType,
isSaleCountOnOff = isShow
});
}
/// <summary>
/// 获取搜索过滤sql
/// </summary>
/// <param name="query"></param>
/// <param name="parms"></param>
/// <returns></returns>
private void GetAppletSearchWhere(SearchProductQuery query, Sql where)
{
where.Append("WHERE CanSearch=1 ");
#region 过滤条件
if (query.ShopId != 0)
{
where.Append("AND ps.ShopId=@0 ", query.ShopId);
}
if (query.VShopId != 0)
{
where.Append(" AND ps.ShopId IN (SELECT ShopId FROM Mall_vshop where Id=@0) ", query.VShopId);
}
if ((query.VShopId != 0 || query.ShopId != 0) && query.ShopCategoryId != 0)
{
where.Append(" AND ps.ProductId IN (select ProductId from Mall_productshopcategory where ShopCategoryId in (select id from Mall_ShopCategory where ShopId = @1 and(id = @0 or ParentCategoryId = @0))) ", query.ShopCategoryId, query.ShopId);
}
if (query.BrandId != 0)
{
where.Append("AND ps.BrandId=@0 ", query.BrandId);
}
if (query.FirstCateId != 0)
{
where.Append("AND ps.FirstCateId=@0 ", query.FirstCateId);
}
else if (query.SecondCateId != 0)
{
where.Append("AND ps.SecondCateId=@0 ", query.SecondCateId);
}
else if (query.ThirdCateId != 0)
{
where.Append("AND ps.ThirdCateId=@0 ", query.ThirdCateId);
}
if (query.StartPrice > 0)
{
where.Append(" AND ps.SalePrice>=@0 ", query.StartPrice);
}
if (query.EndPrice > 0 && query.EndPrice >= query.StartPrice)
{
where.Append(" AND ps.SalePrice <= @EndPrice ");
}
if (query.AttrValIds.Count > 0)
{
where.Append(" AND ps.ProductId IN (SELECT DISTINCT ProductId FROM Mall_ProductAttribute WHERE ValueId IN (@0)) ", query.AttrValIds);
}
if (!string.IsNullOrEmpty(query.Keyword))
{
if (!query.IsLikeSearch)
{
where.Append("AND MATCH(ps.ProductName) AGAINST(@0 IN BOOLEAN MODE) ", string.Concat(UrnHtml(query.Keyword), "*").Replace(" ", "*"));
}
else
{
where.Append("AND ps.ProductName like @0 ", "%" + query.Keyword + "%");
}
}
#endregion
}
/// <summary>
/// 获取搜索商品分页sql
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
private string GetSearchPage(SearchProductQuery query)
{
return(string.Format(" LIMIT {0},{1} ", (query.PageNumber - 1) * query.PageSize, query.PageSize));
}
/// <summary>
/// 获取搜索过滤sql
/// </summary>
/// <param name="query"></param>
/// <param name="parms"></param>
/// <returns></returns>
private string GetAppletSearchWhere(SearchProductQuery query, DynamicParameters parms)
{
StringBuilder where = new StringBuilder();
where.Append("WHERE CanSearch=1 ");
#region 过滤条件
if (query.ShopId != 0)
{
where.Append("AND ps.ShopId=@ShopId ");
parms.Add("@ShopId", query.ShopId);
}
if (query.VShopId != 0)
{
where.Append(" AND ps.ShopId IN (SELECT ShopId FROM himall_vshop where Id=@VShopId) ");
parms.Add("@VShopId", query.VShopId);
}
if ((query.VShopId != 0 || query.ShopId != 0) && query.ShopCategoryId != 0)
{
where.Append(" AND ps.ProductId IN (select ProductId from himall_productshopcategories where ShopCategoryId in(select id from Himall_ShopCategories where ShopId = @ShopId and(id = @ShopCategoryId or ParentCategoryId = @ShopCategoryId)))");
parms.Add("@ShopCategoryId", query.ShopCategoryId);
}
if (query.BrandId != 0)
{
where.Append("AND ps.BrandId=@BrandId ");
parms.Add("@BrandId", query.BrandId);
}
if (query.FirstCateId != 0)
{
where.Append("AND ps.FirstCateId=@FirstCateId ");
parms.Add("@FirstCateId", query.FirstCateId);
}
else if (query.SecondCateId != 0)
{
where.Append("AND ps.SecondCateId=@SecondCateId ");
parms.Add("@SecondCateId", query.SecondCateId);
}
else if (query.ThirdCateId != 0)
{
where.Append("AND ps.ThirdCateId=@ThirdCateId ");
parms.Add("@ThirdCateId", query.ThirdCateId);
}
if (query.StartPrice > 0)
{
where.Append(" AND ps.SalePrice>=@StartPrice ");
parms.Add("@StartPrice", query.StartPrice);
}
if (query.EndPrice > 0 && query.EndPrice >= query.StartPrice)
{
where.Append(" AND ps.SalePrice <= @EndPrice ");
parms.Add("@EndPrice", query.EndPrice);
}
if (query.AttrValIds.Count > 0)
{
where.Append(" AND ps.ProductId IN (SELECT DISTINCT ProductId FROM Himall_ProductAttributes WHERE ValueId IN @ValueIds) ");
parms.Add("@ValueIds", query.AttrValIds.ToArray());
}
if (!string.IsNullOrEmpty(query.Keyword))
{
if (!query.IsLikeSearch)
{
where.Append("AND MATCH(ps.ProductName) AGAINST(@ProductName IN BOOLEAN MODE) ");
parms.Add("@ProductName", string.Concat(query.Keyword, "*").Replace(" ", "*"));
}
else
{
where.Append("AND ps.ProductName like @ProductName ");
parms.Add("@ProductName", "%" + query.Keyword + "%");
}
}
return(where.ToString());
#endregion
}
/// <summary>
/// 获取搜索过滤sql
/// </summary>
/// <param name="query"></param>
/// <param name="parms"></param>
/// <returns></returns>
private void GetSearchWhere(SearchProductQuery query, Sql where)
{
where.Append(" WHERE s.CanSearch=1 ");
#region 过滤条件
if (query.FilterVirtualProduct.HasValue && query.FilterVirtualProduct.Value)
{
where.Append(" AND p.ProductType=0 ");
}
if (query.ShopId != 0)
{
where.Append(" AND s.ShopId=@0 ", query.ShopId);
//parms.Add("@ShopId", query.ShopId);
}
if (query.VShopId != 0)
{
where.Append(" AND s.ShopId IN (SELECT ShopId FROM Mall_vshop where Id=@0) ", query.VShopId);
//parms.Add("@VShopId", query.VShopId);
}
if ((query.VShopId != 0 || query.ShopId != 0) && query.ShopCategoryId != 0)
{
where.Append(" AND s.ProductId IN (select ProductId from Mall_productshopcategory where ShopCategoryId in(select id from Mall_ShopCategory where ShopId = @0 and(id = @1 or ParentCategoryId = @1))) ", query.ShopId, query.ShopCategoryId);
//parms.Add("@ShopCategoryId", query.ShopCategoryId);
}
if (query.BrandId != 0)
{
where.Append(" AND s.BrandId=@0 ", query.BrandId);
//parms.Add("@BrandId", query.BrandId);
}
if (query.FirstCateId != 0)
{
where.Append(" AND s.FirstCateId=@0 ", query.FirstCateId);
//parms.Add("@FirstCateId", query.FirstCateId);
}
else if (query.SecondCateId != 0)
{
where.Append(" AND s.SecondCateId=@0 ", query.SecondCateId);
//parms.Add("@SecondCateId", query.SecondCateId);
}
else if (query.ThirdCateId != 0)
{
where.Append(" AND s.ThirdCateId=@0 ", query.ThirdCateId);
//parms.Add("@ThirdCateId", query.ThirdCateId);
}
if (query.StartPrice >= 0)
{
where.Append(" AND s.SalePrice>=@0 ", query.StartPrice);
//parms.Add("@StartPrice", query.StartPrice);
}
if (query.EndPrice > 0 && query.EndPrice >= query.StartPrice)
{
where.Append(" AND s.SalePrice <= @0 ", query.EndPrice);
//parms.Add("@EndPrice", query.EndPrice);
}
if (query.AttrValIds.Count > 0)
{
where.Append(" AND s.ProductId IN (SELECT DISTINCT ProductId FROM Mall_ProductAttribute ");
//此处属性筛选，要取交集非并集
foreach (var item in query.AttrValIds)
{
where.Append(" INNER JOIN (SELECT DISTINCT ProductId FROM Mall_ProductAttribute WHERE (ValueId = " + item + " ) ) t" + query.AttrValIds.IndexOf(item) + " USING (ProductId) ");
}
where.Append(")");
}
if (!string.IsNullOrEmpty(query.Keyword))
{
if (!query.IsLikeSearch)
{
where.Append(" AND MATCH(s.ProductName) AGAINST(@0 IN BOOLEAN MODE) ", string.Concat(UrnHtml(query.Keyword.TrimEnd(' ')), "*").Replace(" ", "*"));
//parms.Add("@ProductName", string.Concat(query.Keyword, "*").Replace(" ", "*"));
}
else
{
where.Append(" AND s.ProductName like @0 ", "%" + query.Keyword + "%");
//parms.Add("@ProductName", "%" + query.Keyword + "%");
}
}
#endregion
}
/// <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());
}
}
public async Task <IActionResult> Get([FromQuery] SearchProductQuery searchProductQuery)
{
return(Ok(await Mediator.Send(searchProductQuery)));
}
// GET: Web/Search/SearchAd
/// <summary>
/// 商品搜索页面
/// </summary>
/// <param name="keywords">搜索关键字</param>
/// <param name="cid">分类ID</param>
/// <param name="b_id">品牌ID</param>
/// <param name="a_id">属性ID, 表现形式：attrId_attrValueId</param>
/// <param name="orderKey">序项（1：默认，2：销量，3：价格，4：评论数，5：上架时间）</param>
/// <param name="orderType">排序方式（1：升序，2：降序）</param>
/// <param name="pageNo">页码</param>
/// <param name="pageSize">每页显示数据量</param>
/// <returns></returns>
public ActionResult SearchAd(
string keywords = "", /* 搜索关键字 */
long cid = 0, /* 分类ID */
long b_id = 0, /* 品牌ID */
string a_id = "", /* 属性值ID, 表现形式：valueid,valueid */
int orderKey = 1, /* 排序项（1：默认，2：销量，3：价格，4：评论数，5：上架时间） */
int orderType = 1, /* 排序方式（1：升序，2：降序） */
int pageNo = 1, /*页码*/
int pageSize = 60 /*每页显示数据量*/
)
{
try
{
if (string.IsNullOrEmpty(keywords) && cid <= 0 && b_id <= 0 && a_id == "")
{
keywords = Application.SiteSettingApplication.GetSiteSettings().Keyword;
}
#region 初始化查询Model
SearchProductQuery model = new SearchProductQuery();
model.ShopId = 0;
model.BrandId = b_id;
if (cid != 0)
{
var catelist = _iCategoryService.GetCategories();
var cate = catelist.FirstOrDefault(r => r.Id == cid);
if (cate.Depth == 1)
{
model.FirstCateId = cid;
}
else if (cate.Depth == 2)
{
model.SecondCateId = cid;
}
else if (cate.Depth == 3)
{
model.ThirdCateId = cid;
}
}
model.AttrValIds = a_id.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
model.Keyword = keywords;
model.OrderKey = orderKey;
model.OrderType = orderType == 1;
model.PageNumber = pageNo;
model.PageSize = pageSize;
#endregion
SearchProductResult result = _iSearchProductService.SearchProduct(model);
int total = result.Total;
//当查询的结果少于一页时用like进行补偿
if (result.Total < pageSize)
{
model.IsLikeSearch = true;
SearchProductResult result2 = _iSearchProductService.SearchProduct(model);
var idList1 = result.Data.Select(a => a.ProductId).ToList();
var nresult = result2.Data.Where(a => !idList1.Contains(a.ProductId)).ToList();
if (nresult.Count > 0)
{
result.Total += nresult.Count;
result.Data.AddRange(nresult);
}
}
if (result.Total == 0)
{
ViewBag.BrowsedHistory = BrowseHistrory.GetBrowsingProducts(13, CurrentUser == null ? 0 : CurrentUser.Id);
var category = _iCategoryService.GetCategory(model.ThirdCateId);
string categoryName = category == null ? string.Empty : category.Name;
var brand = _iBrandService.GetBrand(b_id) ?? new BrandInfo();
string bname = brand == null ? "" : brand.Name;
ViewBag.categoryName = categoryName;
ViewBag.bName = bname;
}
total = result.Total;
if (Core.HimallIO.GetHimallIO().GetType().FullName.Equals("Himall.Strategy.OSS"))
{
ViewBag.IsOss = true;
}
else
{
ViewBag.IsOss = false;
}
ViewBag.keywords = model.Keyword;
ViewBag.cid = cid;
ViewBag.b_id = b_id;
ViewBag.a_id = a_id;
ViewBag.orderKey = orderKey;
ViewBag.orderType = orderType;
#region 分页控制
PagingInfo info = new PagingInfo
{
CurrentPage = model.PageNumber,
ItemsPerPage = pageSize,
TotalItems = total
};
ViewBag.pageInfo = info;
#endregion
return(View(result.Data));
}
catch (Exception e)
{
throw e;
}
}
/// <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());
}
}
public ActionResult SearchAd(string sid, long cid = 0, string keywords = "", int pageNo = 1, decimal startPrice = 0, decimal endPrice = decimal.MaxValue)
{
int pageSize = 40;
long shopId = 0;
ShopInfo shopObj = null;
endPrice = endPrice <= 0 || endPrice < startPrice ? decimal.MaxValue : endPrice;
startPrice = startPrice < 0 ? 0 : startPrice;
//shopId 不是数字
if (!long.TryParse(sid, out shopId))
{
return(RedirectToAction("Error404", "Error", new { area = "Web" }));
//404 页面
}
//店铺Id不存在
shopObj = _iShopService.GetShop(shopId);
if (null == shopObj)
{
return(RedirectToAction("Error404", "Error", new { area = "Web" }));
//404 页面
}
#region 初始化Model
ShopHomeModel model = new ShopHomeModel
{
HotAttentionProducts = new List <HotProductInfo>(),
HotSaleProducts = new List <HotProductInfo>(),
Floors = new List <ShopHomeFloor>(),
Navignations = new List <BannerInfo>(),
Shop = new ShopInfoModel(),
ShopCategory = new List <CategoryJsonModel>(),
Slides = new List <SlideAdInfo>(),
Logo = ""
};
#endregion
#region 导航和3个推荐商品
//导航
model.Navignations = _iNavigationService.GetSellerNavigations(shopObj.Id).ToList();
//banner和3个推荐商品
model.ImageAds = _iSlideAdsService.GetImageAds(shopObj.Id).OrderBy(item => item.Id).ToList();
model.Slides = _iSlideAdsService.GetSlidAds(shopObj.Id, SlideAdInfo.SlideAdType.ShopHome).ToList();
#endregion
#region 店铺分类
var categories = _iShopCategoryService.GetShopCategory(shopObj.Id).ToArray();
foreach (var main in categories.Where(s => s.ParentCategoryId == 0))
{
var topC = new CategoryJsonModel()
{
Name = main.Name,
Id = main.Id.ToString(),
SubCategory = new List <SecondLevelCategory>()
};
foreach (var secondItem in categories.Where(s => s.ParentCategoryId == main.Id))
{
var secondC = new SecondLevelCategory()
{
Name = secondItem.Name,
Id = secondItem.Id.ToString(),
};
topC.SubCategory.Add(secondC);
}
model.ShopCategory.Add(topC);
}
#endregion
#region 店铺信息
var mark = ShopServiceMark.GetShopComprehensiveMark(shopObj.Id);
model.Shop.Name = shopObj.ShopName;
model.Shop.CompanyName = shopObj.CompanyName;
model.Shop.Id = shopObj.Id;
model.Shop.PackMark = mark.PackMark;
model.Shop.ServiceMark = mark.ServiceMark;
model.Shop.ComprehensiveMark = mark.ComprehensiveMark;
model.Shop.Phone = shopObj.CompanyPhone;
model.Shop.Address = _iRegionService.GetFullName(shopObj.CompanyRegionId);
model.Logo = shopObj.Logo;
#endregion
SearchProductQuery query = new SearchProductQuery()
{
ShopId = long.Parse(sid),
ShopCategoryId = cid,
Keyword = keywords,
StartPrice = startPrice,
EndPrice = endPrice,
PageNumber = pageNo,
PageSize = pageSize
};
SearchProductResult result = _iSearchProductService.SearchProduct(query);
model.Products = result.Data;
//#endregion
#region 热门销售
var sale = _iProductService.GetHotSaleProduct(shopObj.Id, 5);
if (sale != null)
{
foreach (var item in sale)
{
model.HotSaleProducts.Add(new HotProductInfo
{
ImgPath = item.ImagePath,
Name = item.ProductName,
Price = item.MinSalePrice,
Id = item.Id,
SaleCount = (int)item.SaleCounts
});
}
}
#endregion
#region 热门关注
var hot = _iProductService.GetHotConcernedProduct(shopObj.Id, 5).ToList();
if (hot != null)
{
foreach (var item in hot)
{
model.HotAttentionProducts.Add(new HotProductInfo
{
ImgPath = item.ImagePath,
Name = item.ProductName,
Price = item.MinSalePrice,
Id = item.Id,
SaleCount = (int)item.ConcernedCount
});
}
}
#endregion
#region 获取店铺的评价统计
var shopStatisticOrderComments = _iShopService.GetShopStatisticOrderComments(shopId);
var productAndDescription = shopStatisticOrderComments.Where(c => c.CommentKey == StatisticOrderCommentsInfo.EnumCommentKey.ProductAndDescription).FirstOrDefault();
var sellerServiceAttitude = shopStatisticOrderComments.Where(c => c.CommentKey == StatisticOrderCommentsInfo.EnumCommentKey.SellerServiceAttitude).FirstOrDefault();
var sellerDeliverySpeed = shopStatisticOrderComments.Where(c => c.CommentKey ==
StatisticOrderCommentsInfo.EnumCommentKey.SellerDeliverySpeed).FirstOrDefault();
var productAndDescriptionPeer = shopStatisticOrderComments.Where(c => c.CommentKey == StatisticOrderCommentsInfo.EnumCommentKey.ProductAndDescriptionPeer).FirstOrDefault();
var sellerServiceAttitudePeer = shopStatisticOrderComments.Where(c => c.CommentKey == StatisticOrderCommentsInfo.EnumCommentKey.SellerServiceAttitudePeer).FirstOrDefault();
var sellerDeliverySpeedPeer = shopStatisticOrderComments.Where(c => c.CommentKey ==
StatisticOrderCommentsInfo.EnumCommentKey.SellerDeliverySpeedPeer).FirstOrDefault();
var productAndDescriptionMax = shopStatisticOrderComments.Where(c => c.CommentKey == StatisticOrderCommentsInfo.EnumCommentKey.ProductAndDescriptionMax).FirstOrDefault();
var productAndDescriptionMin = shopStatisticOrderComments.Where(c => c.CommentKey == StatisticOrderCommentsInfo.EnumCommentKey.ProductAndDescriptionMin).FirstOrDefault();
var sellerServiceAttitudeMax = shopStatisticOrderComments.Where(c => c.CommentKey == StatisticOrderCommentsInfo.EnumCommentKey.SellerServiceAttitudeMax).FirstOrDefault();
var sellerServiceAttitudeMin = shopStatisticOrderComments.Where(c => c.CommentKey == StatisticOrderCommentsInfo.EnumCommentKey.SellerServiceAttitudeMin).FirstOrDefault();
var sellerDeliverySpeedMax = shopStatisticOrderComments.Where(c => c.CommentKey == StatisticOrderCommentsInfo.EnumCommentKey.SellerDeliverySpeedMax).FirstOrDefault();
var sellerDeliverySpeedMin = shopStatisticOrderComments.Where(c => c.CommentKey == StatisticOrderCommentsInfo.EnumCommentKey.SellerDeliverySpeedMin).FirstOrDefault();
decimal defaultValue = 5;
//宝贝与描述
if (productAndDescription != null && productAndDescriptionPeer != null)
{
ViewBag.ProductAndDescription = productAndDescription.CommentValue;
ViewBag.ProductAndDescriptionPeer = productAndDescriptionPeer.CommentValue;
ViewBag.ProductAndDescriptionMin = productAndDescriptionMin.CommentValue;
ViewBag.ProductAndDescriptionMax = productAndDescriptionMax.CommentValue;
}
else
{
ViewBag.ProductAndDescription = defaultValue;
ViewBag.ProductAndDescriptionPeer = defaultValue;
ViewBag.ProductAndDescriptionMin = defaultValue;
ViewBag.ProductAndDescriptionMax = defaultValue;
}
//卖家服务态度
if (sellerServiceAttitude != null && sellerServiceAttitudePeer != null)
{
ViewBag.SellerServiceAttitude = sellerServiceAttitude.CommentValue;
ViewBag.SellerServiceAttitudePeer = sellerServiceAttitudePeer.CommentValue;
ViewBag.SellerServiceAttitudeMax = sellerServiceAttitudeMax.CommentValue;
ViewBag.SellerServiceAttitudeMin = sellerServiceAttitudeMin.CommentValue;
}
else
{
ViewBag.SellerServiceAttitude = defaultValue;
ViewBag.SellerServiceAttitudePeer = defaultValue;
ViewBag.SellerServiceAttitudeMax = defaultValue;
ViewBag.SellerServiceAttitudeMin = defaultValue;
}
//卖家发货速度
if (sellerDeliverySpeedPeer != null && sellerDeliverySpeed != null)
{
ViewBag.SellerDeliverySpeed = sellerDeliverySpeed.CommentValue;
ViewBag.SellerDeliverySpeedPeer = sellerDeliverySpeedPeer.CommentValue;
ViewBag.SellerDeliverySpeedMax = sellerDeliverySpeedMax.CommentValue;
ViewBag.sellerDeliverySpeedMin = sellerDeliverySpeedMin.CommentValue;
}
else
{
ViewBag.SellerDeliverySpeed = defaultValue;
ViewBag.SellerDeliverySpeedPeer = defaultValue;
ViewBag.SellerDeliverySpeedMax = defaultValue;
ViewBag.sellerDeliverySpeedMin = defaultValue;
}
#endregion
#region 分页控制
PagingInfo info = new PagingInfo
{
CurrentPage = pageNo,
ItemsPerPage = pageSize,
TotalItems = result.Total
};
ViewBag.pageInfo = info;
#endregion
var categoryName = string.Empty;
if (keywords == string.Empty)
{
if (cid != 0)
{
var category = _iShopCategoryService.GetCategory(cid) ?? new ShopCategoryInfo()
{
};
categoryName = category.Name;
}
}
ViewBag.CategoryName = categoryName;
ViewBag.Keyword = keywords;
ViewBag.cid = cid;
ViewBag.BrowsedHistory = BrowseHistrory.GetBrowsingProducts(13, CurrentUser == null ? 0 : CurrentUser.Id);
//补充当前店铺红包功能
ViewBag.isShopPage = true;
ViewBag.CurShopId = shopId;
TempData["isShopPage"] = true;
TempData["CurShopId"] = shopId;
//统计店铺访问人数
StatisticApplication.StatisticShopVisitUserCount(shopId);
return(View(model));
}
private SearchProductResult DoSearch(
string keywords = "", /* 搜索关键字 */
long cid = 0, /* 分类ID */
long b_id = 0, /* 品牌ID */
string a_id = "", /* 属性ID, 表现形式：attrId_attrValueId */
int orderKey = 1, /* 排序项（1：默认，2：销量，3：价格，4：评论数，5：上架时间） */
int orderType = 1, /* 排序方式（1：升序，2：降序） */
int pageNo = 1, /*页码*/
int pageSize = 10, /*每页显示数据量*/
long vshopId = 0 //店铺ID
)
{
#region 初始化查询Model
SearchProductQuery model = new SearchProductQuery();
model.ShopId = 0;
model.BrandId = b_id;
if (cid != 0)
{
var catelist = _iCategoryService.GetCategories();
var cate = catelist.FirstOrDefault(r => r.Id == cid);
if (cate.Depth == 1)
{
model.FirstCateId = cid;
}
else if (cate.Depth == 2)
{
model.SecondCateId = cid;
}
else if (cate.Depth == 3)
{
model.ThirdCateId = cid;
}
}
model.AttrValIds = a_id.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
model.Keyword = keywords;
model.OrderKey = orderKey;
model.OrderType = orderType == 1;
model.PageNumber = pageNo;
model.PageSize = pageSize;
model.VShopId = vshopId;
#endregion
SearchProductResult result = _iSearchProductService.SearchProduct(model);
int total = result.Total;
//当查询的结果少于一页时用like进行补偿（与PC端同步）
if (result.Total < pageSize)
{
model.IsLikeSearch = true;
SearchProductResult result2 = _iSearchProductService.SearchProduct(model);
var idList1 = result.Data.Select(a => a.ProductId).ToList();
var nresult = result2.Data.Where(a => !idList1.Contains(a.ProductId)).ToList();
if (nresult.Count > 0)
{
result.Total += nresult.Count;
result.Data.AddRange(nresult);
}
}
total = result.Total;
if (Core.HimallIO.GetHimallIO().GetType().FullName.Equals("Himall.Strategy.OSS"))
{
ViewBag.IsOss = true;
}
else
{
ViewBag.IsOss = false;
}
ViewBag.keywords = model.Keyword;
ViewBag.Total = total;
ViewBag.cid = cid;
ViewBag.b_id = b_id;
ViewBag.a_id = a_id;
ViewBag.orderKey = orderKey;
ViewBag.orderType = orderType;
return(result);
}
