/// <summary> /// 查询商品 /// </summary> /// <param name="search">查询条件</param> /// <returns>商品数据结果</returns> public ProductViewResult Query(SearchCondition search) { string sql = null; ProductViewResult result = new ProductViewResult(); List <ProductView> products = new List <ProductView>(); //主属性条件 + 商品名索引 List <string> gsql = getMasterAndNameIndexSql(search); sql = gsql[0]; result.Keys = gsql.Skip(1).ToList(); //目录分类索引 if (search.CategoryId > 0) { if (search.shopId > 0) { sql = string.Format("select pname.* from({0}) pname inner join himall_productshopcategories c on pname.id = c.ProductId where c.ShopCategoryId = {1}", sql, search.ShopCategoryId); } else { sql = string.Format("select pname.* from({0}) pname inner join himall_indexcategory c on pname.id = c.id where c.CategoryId = {1}", sql, search.CategoryId); } } //属性索引 if (search.AttrIds.Count > 0) { sql = string.Format(" select pcategory.* from({0}) pcategory inner join ({1}) a on pcategory.id = a.id ", sql, getAttrSql(search.AttrIds)); } //排序 sql = string.Format("select SQL_CALC_FOUND_ROWS presult.ShopId,presult.ImagePath,presult.Id,presult.Address,presult.Price,presult.ShopName,presult.ProductName,presult.CategoryId,presult.SaleCount,presult.Comments from({0}) presult ", sql); switch (search.OrderKey) { case 2: if (!search.OrderType) { sql = string.Format("{0}order by SaleCount desc", sql); } else { sql = string.Format("{0}order by SaleCount asc", sql); } break; case 3: if (!search.OrderType) { sql = string.Format("{0}order by Price desc", sql); } else { sql = string.Format("{0}order by Price asc", sql); } break; case 4: if (!search.OrderType) { sql = string.Format("{0}order by Comments desc", sql); } else { sql = string.Format("{0}order by Comments asc", sql); } break; case 5: if (!search.OrderType) { sql = string.Format("{0}order by AddedDate desc", sql); } else { sql = string.Format("{0}order by AddedDate asc", sql); } break; default: sql = string.Format("{0}order by Id desc ", sql); break; } //分页 sql = string.Format("{0} limit {1},{2}", sql, (search.PageNumber - 1) * search.PageSize, search.PageSize); MySqlConnection connection = Connection.GetConnection(); connection.Open(); MySqlCommand cmd = new MySqlCommand(sql, connection); MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { ProductView model = new ProductView(); model.Id = long.Parse(reader["Id"].ToString()); model.Address = reader["Address"].ToString(); model.ImagePath = reader["ImagePath"].ToString(); model.Price = decimal.Parse(reader["Price"].ToString()); model.ShopName = reader["ShopName"].ToString(); model.ProductName = reader["ProductName"].ToString(); model.CategoryId = long.Parse(reader["CategoryId"].ToString()); model.SaleCount = int.Parse(reader["SaleCount"].ToString()); model.Comments = int.Parse(reader["Comments"].ToString()); model.ShopId = long.Parse(reader["ShopId"].ToString()); products.Add(model); } reader.Dispose(); MySqlCommand total = new MySqlCommand("select found_rows()", connection); result.Total = int.Parse(total.ExecuteScalar().ToString()); connection.Close(); connection.Dispose(); result.Data = products; return(result); }
public ProductViewResult QueryProduct(SearchCondition search) { ProductViewResult result = new ProductViewResult(); MySqlConnection connection = Connection.GetConnection(); MySqlCommand cmd = new MySqlCommand(); StringBuilder sql = new StringBuilder(); sql.Append("SELECT SQL_CALC_FOUND_ROWS _p.Id,_p.ProductName,_p.MinSalePrice,_p.ShopId,_p.CategoryId,_p.ImagePath,_p.SaleCounts,_f.SourceAddress,_s.ShopName,_c.CommentCount FROM Himall_Products _p "); sql.Append("LEFT JOIN Himall_FreightTemplate _f ON _p.FreightTemplateId=_f.Id "); sql.Append("LEFT JOIN Himall_Shops _s ON _p.ShopId=_s.Id "); sql.Append("LEFT JOIN (SELECT COUNT(1) AS CommentCount,ProductId FROM Himall_ProductComments GROUP BY ProductId) _c ON _p.Id=_c.ProductId "); sql.Append("WHERE _p.IsDeleted=0 AND _p.AuditStatus=2 AND _p.SaleStatus=1 "); #region 店铺过滤 if (search.shopId > 0) { sql.Append(" AND _p.ShopId=@ShopId "); cmd.Parameters.AddWithValue("@ShopId", search.shopId); } #endregion #region 品牌过滤 if (search.BrandId > 0) { sql.Append(" AND _p.BrandId=@BrandId "); cmd.Parameters.AddWithValue("@BrandId", search.BrandId); } #endregion #region 分类过滤 if (search.CategoryId > 0) { if (search.shopId > 0) { sql.Append(" AND _p.Id IN (SELECT DISTINCT ProductId FROM Himall_ProductShopCategories WHERE ShopCategoryId = @ShopCategoryId)"); cmd.Parameters.AddWithValue("@ShopCategoryId", search.ShopCategoryId); } else { sql.AppendFormat("AND _p.CategoryId IN (SELECT Id FROM Himall_Categories WHERE CONCAT('|',Path,'|') LIKE '%|{0}|%')", search.CategoryId); } } #endregion #region 属性过滤 if (search.AttrIds.Count > 0) { string sqlAttrWhere = string.Empty; for (int i = 0; i < search.AttrIds.Count; i++) { if (search.AttrIds[i].Contains("_")) { string attrId = search.AttrIds[i].Split(new char[] { '_' })[0]; string valId = search.AttrIds[i].Split(new char[] { '_' })[1]; sqlAttrWhere += string.Format("(AttributeId=@AttrId{0} AND ValueId=@ValId{0}) OR", i.ToString()); cmd.Parameters.AddWithValue(string.Format("@AttrId{0}", i.ToString()), attrId); cmd.Parameters.AddWithValue(string.Format("@ValId{0}", i.ToString()), valId); } } if (!string.IsNullOrEmpty(sqlAttrWhere)) { sqlAttrWhere = sqlAttrWhere.Substring(0, sqlAttrWhere.Length - 3); sql.AppendFormat(" AND _p.Id IN (SELECT DISTINCT ProductId FROM Himall_ProductAttributes WHERE {0}) ", sqlAttrWhere); } } #endregion #region 价格区间 if (search.StartPrice > 0) { sql.Append(" AND _p.MinSalePrice>=@StartPrice "); cmd.Parameters.AddWithValue("@StartPrice", search.StartPrice); } if (search.EndPrice > 0 && search.EndPrice >= search.StartPrice) { sql.Append(" AND _p.MinSalePrice <= @EndPrice "); cmd.Parameters.AddWithValue("@EndPrice", search.EndPrice); } #endregion #region 关键词处理 if (!string.IsNullOrEmpty(search.Keyword)) { //分隔关键词 List <string> words = new List <string>(); List <string> ws = _segment.DoSegment(search.Keyword); foreach (string s in ws) { if (!words.Contains(s)) { words.Add(s); } } if (search.Ex_Keyword != null && !string.IsNullOrEmpty(search.Ex_Keyword.Trim()) && search.Ex_Keyword.Trim().Length <= 5) { words.Add(search.Ex_Keyword.Trim()); } else { if (!string.IsNullOrEmpty(search.Ex_Keyword)) { ws = _segment.DoSegment(search.Ex_Keyword); foreach (string s in ws) { if (!words.Contains(s)) { words.Add(s); } } } } //分词搜索 if (words.Count > 0) { MySqlCommand IdCmd = new MySqlCommand(); string sqlWhere = "SELECT Id FROM Himall_Products WHERE ProductName=@ProductName UNION ALL SELECT a.ProductId FROM Himall_ProductWords a INNER JOIN Himall_SegmentWords b ON a.WordId=b.Id WHERE b.Word IN (SELECT WORD FROM ({0}) AS _WordTable) OR MATCH(b.Word) AGAINST(@WordList IN BOOLEAN MODE)"; string wordList = string.Empty; string wordInList = string.Empty; string IdList = string.Empty; for (int i = 0; i < words.Count; i++) { wordList += words[i] + "*,"; wordInList += string.Format("SELECT @word{0} AS WORD {1} ", i.ToString(), (i == words.Count - 1 ? string.Empty : " UNION ALL ")); IdCmd.Parameters.AddWithValue(string.Format("@word{0}", i.ToString()), words[i]); } IdCmd.Parameters.AddWithValue("@WordList", wordList.TrimEnd(new char[] { ',' })); IdCmd.Parameters.AddWithValue("@ProductName", search.Keyword.Trim()); IdCmd.CommandText = string.Format(sqlWhere, wordInList); IdCmd.Connection = connection; if (IdCmd.Connection.State != ConnectionState.Open) { connection.Open(); } MySqlDataReader IdReader = IdCmd.ExecuteReader(); while (IdReader.Read()) { IdList += IdReader["Id"].ToString() + ","; } IdReader.Dispose(); sql.AppendFormat(" AND _p.Id IN ({0})", string.IsNullOrEmpty(IdList) ? "0" : IdList.TrimEnd(new char[] { ',' })); result.Keys = words; } } #endregion #region 处理排序 switch (search.OrderKey) { case 2: sql.Append(" ORDER BY _p.SaleCounts "); break; case 3: sql.Append(" ORDER BY _p.MinSalePrice "); break; case 4: sql.Append(" ORDER BY _c.CommentCount "); break; case 5: sql.Append(" ORDER BY _p.AddedDate "); break; default: sql.Append(" ORDER BY _p.Id "); break; } if (!search.OrderType) { sql.Append(" DESC "); } else { sql.Append(" ASC "); } #endregion #region 处理分页 sql.AppendFormat(" LIMIT {0},{1}", (search.PageNumber - 1) * search.PageSize, search.PageSize); #endregion cmd.Connection = connection; if (cmd.Connection.State != ConnectionState.Open) { connection.Open(); } cmd.CommandText = sql.ToString(); MySqlDataReader reader = cmd.ExecuteReader(); List <ProductView> products = new List <ProductView>(); while (reader.Read()) { ProductView model = new ProductView(); model.Id = long.Parse(reader["Id"].ToString()); model.Address = reader["SourceAddress"].ToString(); model.ImagePath = reader["ImagePath"].ToString(); model.Price = decimal.Parse(reader["MinSalePrice"].ToString()); model.ShopName = reader["ShopName"].ToString(); model.ProductName = reader["ProductName"].ToString(); model.CategoryId = long.Parse(reader["CategoryId"].ToString()); model.SaleCount = int.Parse(reader["SaleCounts"].ToString()); model.Comments = int.Parse(string.IsNullOrEmpty(reader["CommentCount"].ToString()) ? "0" : reader["CommentCount"].ToString()); model.ShopId = long.Parse(reader["ShopId"].ToString()); products.Add(model); } reader.Dispose(); MySqlCommand total = new MySqlCommand("select found_rows()", connection); result.Total = int.Parse(total.ExecuteScalar().ToString()); connection.Close(); connection.Dispose(); result.Data = products; return(result); }