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