/// <summary> /// 从IDataReader创建StoreProductInfo /// </summary> public static StoreProductInfo BuildStoreProductFromReader(IDataReader reader) { StoreProductInfo storeProductInfo = new StoreProductInfo(); storeProductInfo.Pid = TypeHelper.ObjectToInt(reader["pid"]); storeProductInfo.PSN = reader["psn"].ToString(); storeProductInfo.CateId = TypeHelper.ObjectToInt(reader["cateid"]); storeProductInfo.BrandId = TypeHelper.ObjectToInt(reader["brandid"]); storeProductInfo.StoreId = TypeHelper.ObjectToInt(reader["storeid"]); storeProductInfo.StoreCid = TypeHelper.ObjectToInt(reader["storecid"]); storeProductInfo.StoreSTid = TypeHelper.ObjectToInt(reader["storestid"]); storeProductInfo.SKUGid = TypeHelper.ObjectToInt(reader["skugid"]); storeProductInfo.Name = reader["name"].ToString(); storeProductInfo.ShopPrice = TypeHelper.ObjectToDecimal(reader["shopprice"]); storeProductInfo.MarketPrice = TypeHelper.ObjectToDecimal(reader["marketprice"]); storeProductInfo.CostPrice = TypeHelper.ObjectToDecimal(reader["costprice"]); storeProductInfo.State = TypeHelper.ObjectToInt(reader["state"]); storeProductInfo.IsBest = TypeHelper.ObjectToInt(reader["isbest"]); storeProductInfo.IsHot = TypeHelper.ObjectToInt(reader["ishot"]); storeProductInfo.IsNew = TypeHelper.ObjectToInt(reader["isnew"]); storeProductInfo.DisplayOrder = TypeHelper.ObjectToInt(reader["displayorder"]); storeProductInfo.Weight = TypeHelper.ObjectToInt(reader["weight"]); storeProductInfo.ShowImg = reader["showimg"].ToString(); storeProductInfo.SaleCount = TypeHelper.ObjectToInt(reader["salecount"]); storeProductInfo.VisitCount = TypeHelper.ObjectToInt(reader["visitcount"]); storeProductInfo.ReviewCount = TypeHelper.ObjectToInt(reader["reviewcount"]); storeProductInfo.Star1 = TypeHelper.ObjectToInt(reader["star1"]); storeProductInfo.Star2 = TypeHelper.ObjectToInt(reader["star2"]); storeProductInfo.Star3 = TypeHelper.ObjectToInt(reader["star3"]); storeProductInfo.Star4 = TypeHelper.ObjectToInt(reader["star4"]); storeProductInfo.Star5 = TypeHelper.ObjectToInt(reader["star5"]); storeProductInfo.AddTime = TypeHelper.ObjectToDateTime(reader["addtime"]); storeProductInfo.StoreName = reader["storename"].ToString(); return storeProductInfo; }
/// <summary> /// 搜索商城商品 /// </summary> /// <param name="pageSize">每页数</param> /// <param name="pageNumber">当前页数</param> /// <param name="keyword">关键词</param> /// <param name="cateId">分类id</param> /// <param name="brandId">品牌id</param> /// <param name="filterPrice">筛选价格</param> /// <param name="catePriceRangeList">分类价格范围列表</param> /// <param name="attrValueIdList">属性值id列表</param> /// <param name="onlyStock">是否只显示有货</param> /// <param name="sortColumn">排序列</param> /// <param name="sortDirection">排序方向</param> /// <returns></returns> public List<StoreProductInfo> SearchMallProducts(int pageSize, int pageNumber, string keyword, int cateId, int brandId, int filterPrice, string[] catePriceRangeList, List<int> attrValueIdList, int onlyStock, int sortColumn, int sortDirection) { StringBuilder commandText = new StringBuilder(); if (pageNumber == 1) { commandText.AppendFormat("SELECT TOP {1} [p].[pid],[p].[psn],[p].[cateid],[p].[brandid],[p].[storeid],[p].[storecid],[p].[storestid],[p].[skugid],[p].[name],[p].[shopprice],[p].[marketprice],[p].[costprice],[p].[state],[p].[isbest],[p].[ishot],[p].[isnew],[p].[displayorder],[p].[weight],[p].[showimg],[p].[salecount],[p].[visitcount],[p].[reviewcount],[p].[star1],[p].[star2],[p].[star3],[p].[star4],[p].[star5],[p].[addtime],[s].[name] AS [storename] FROM [{0}products] AS [p]", RDBSHelper.RDBSTablePre, pageSize); if (onlyStock == 1) commandText.AppendFormat(" LEFT JOIN [{0}productstocks] AS [ps] ON [p].[pid]=[ps].[pid]", RDBSHelper.RDBSTablePre); commandText.AppendFormat(" LEFT JOIN [{0}productkeywords] AS [pk] ON [p].[pid]=[pk].[pid]", RDBSHelper.RDBSTablePre); commandText.AppendFormat(" LEFT JOIN [{0}stores] AS [s] ON [p].[storeid]=[s].[storeid]", RDBSHelper.RDBSTablePre); commandText.AppendFormat(" WHERE [p].[cateid]={0}", cateId); if (brandId > 0) commandText.AppendFormat(" AND [p].[brandid]={0}", brandId); if (filterPrice > 0 && filterPrice <= catePriceRangeList.Length) { string[] priceRange = StringHelper.SplitString(catePriceRangeList[filterPrice - 1], "-"); if (priceRange.Length == 1) commandText.AppendFormat(" AND [p].[shopprice]>='{0}'", priceRange[0]); else if (priceRange.Length == 2) commandText.AppendFormat(" AND [p].[shopprice]>='{0}' AND [p].[shopprice]<'{1}'", priceRange[0], priceRange[1]); } commandText.Append(" AND [p].[state]=0"); if (attrValueIdList.Count > 0) { commandText.Append(" AND [p].[pid] IN (SELECT [pa1].[pid] FROM"); for (int i = 0; i < attrValueIdList.Count; i++) { if (i == 0) commandText.AppendFormat(" (SELECT [pid] FROM [{0}productattributes] WHERE [attrvalueid]={1}) AS [pa1]", RDBSHelper.RDBSTablePre, attrValueIdList[i]); else commandText.AppendFormat(" INNER JOIN (SELECT [pid] FROM [{0}productattributes] WHERE [attrvalueid]={1}) AS [pa{2}] ON [pa{2}].[pid]=[pa{3}].[pid]", RDBSHelper.RDBSTablePre, attrValueIdList[i], i + 1, i); } commandText.Append(")"); } if (onlyStock == 1) commandText.Append(" AND [ps].[number]>0"); commandText.AppendFormat(" AND [pk].[keyword]='{0}'", keyword); commandText.Append(" AND [s].[state]=0"); commandText.Append(" ORDER BY "); switch (sortColumn) { case 0: commandText.Append("[pk].[relevancy]"); break; case 1: commandText.Append("[p].[salecount]"); break; case 2: commandText.Append("[p].[shopprice]"); break; case 3: commandText.Append("[p].[reviewcount]"); break; case 4: commandText.Append("[p].[addtime]"); break; case 5: commandText.Append("[p].[visitcount]"); break; default: commandText.Append("[pk].[relevancy]"); break; } switch (sortDirection) { case 0: commandText.Append(" DESC"); break; case 1: commandText.Append(" ASC"); break; default: commandText.Append(" DESC"); break; } } else { commandText.Append("SELECT [pid],[psn],[cateid],[brandid],[storeid],[storecid],[storestid],[skugid],[name],[shopprice],[marketprice],[costprice],[state],[isbest],[ishot],[isnew],[displayorder],[weight],[showimg],[salecount],[visitcount],[reviewcount],[star1],[star2],[star3],[star4],[star5],[addtime],[storename] FROM"); commandText.Append(" (SELECT ROW_NUMBER() OVER (ORDER BY "); switch (sortColumn) { case 0: commandText.Append("[pk].[relevancy]"); break; case 1: commandText.Append("[p].[salecount]"); break; case 2: commandText.Append("[p].[shopprice]"); break; case 3: commandText.Append("[p].[reviewcount]"); break; case 4: commandText.Append("[p].[addtime]"); break; case 5: commandText.Append("[p].[visitcount]"); break; default: commandText.Append("[pk].[relevancy]"); break; } switch (sortDirection) { case 0: commandText.Append(" DESC"); break; case 1: commandText.Append(" ASC"); break; default: commandText.Append(" DESC"); break; } commandText.AppendFormat(") AS [rowid],[p].[pid],[p].[psn],[p].[cateid],[p].[brandid],[p].[storeid],[p].[storecid],[p].[storestid],[p].[skugid],[p].[name],[p].[shopprice],[p].[marketprice],[p].[costprice],[p].[state],[p].[isbest],[p].[ishot],[p].[isnew],[p].[displayorder],[p].[weight],[p].[showimg],[p].[salecount],[p].[visitcount],[p].[reviewcount],[p].[star1],[p].[star2],[p].[star3],[p].[star4],[p].[star5],[p].[addtime],[s].[name] AS [storename] FROM [{0}products] AS [p]", RDBSHelper.RDBSTablePre); if (onlyStock == 1) commandText.AppendFormat(" LEFT JOIN [{0}productstocks] AS [ps] ON [p].[pid]=[ps].[pid]", RDBSHelper.RDBSTablePre); commandText.AppendFormat(" LEFT JOIN [{0}productkeywords] AS [pk] ON [p].[pid]=[pk].[pid]", RDBSHelper.RDBSTablePre); commandText.AppendFormat(" LEFT JOIN [{0}stores] AS [s] ON [p].[storeid]=[s].[storeid]", RDBSHelper.RDBSTablePre); commandText.AppendFormat(" WHERE [p].[cateid]={0}", cateId); if (brandId > 0) commandText.AppendFormat(" AND [p].[brandid]={0}", brandId); if (filterPrice > 0 && filterPrice <= catePriceRangeList.Length) { string[] priceRange = StringHelper.SplitString(catePriceRangeList[filterPrice - 1], "-"); if (priceRange.Length == 1) commandText.AppendFormat(" AND [p].[shopprice]>='{0}'", priceRange[0]); else if (priceRange.Length == 2) commandText.AppendFormat(" AND [p].[shopprice]>='{0}' AND [p].[shopprice]<'{1}'", priceRange[0], priceRange[1]); } commandText.Append(" AND [p].[state]=0"); if (attrValueIdList.Count > 0) { commandText.Append(" AND [p].[pid] IN (SELECT [pa1].[pid] FROM"); for (int i = 0; i < attrValueIdList.Count; i++) { if (i == 0) commandText.AppendFormat(" (SELECT [pid] FROM [{0}productattributes] WHERE [attrvalueid]={1}) AS [pa1]", RDBSHelper.RDBSTablePre, attrValueIdList[i]); else commandText.AppendFormat(" INNER JOIN (SELECT [pid] FROM [{0}productattributes] WHERE [attrvalueid]={1}) AS [pa{2}] ON [pa{2}].[pid]=[pa{3}].[pid]", RDBSHelper.RDBSTablePre, attrValueIdList[i], i + 1, i); } commandText.Append(")"); } if (onlyStock == 1) commandText.Append(" AND [ps].[number]>0"); commandText.AppendFormat(" AND [pk].[keyword]='{0}'", keyword); commandText.Append(" AND [s].[state]=0"); commandText.Append(") AS [temp]"); commandText.AppendFormat(" WHERE [rowid] BETWEEN {0} AND {1}", pageSize * (pageNumber - 1) + 1, pageSize * pageNumber); } List<StoreProductInfo> storeProductList = new List<StoreProductInfo>(); IDataReader reader = RDBSHelper.ExecuteReader(CommandType.Text, commandText.ToString()); while (reader.Read()) { StoreProductInfo storeProductInfo = new StoreProductInfo(); storeProductInfo.Pid = TypeHelper.ObjectToInt(reader["pid"]); storeProductInfo.PSN = reader["psn"].ToString(); storeProductInfo.CateId = TypeHelper.ObjectToInt(reader["cateid"]); storeProductInfo.BrandId = TypeHelper.ObjectToInt(reader["brandid"]); storeProductInfo.StoreId = TypeHelper.ObjectToInt(reader["storeid"]); storeProductInfo.StoreCid = TypeHelper.ObjectToInt(reader["storecid"]); storeProductInfo.StoreSTid = TypeHelper.ObjectToInt(reader["storestid"]); storeProductInfo.SKUGid = TypeHelper.ObjectToInt(reader["skugid"]); storeProductInfo.Name = reader["name"].ToString(); storeProductInfo.ShopPrice = TypeHelper.ObjectToDecimal(reader["shopprice"]); storeProductInfo.MarketPrice = TypeHelper.ObjectToDecimal(reader["marketprice"]); storeProductInfo.CostPrice = TypeHelper.ObjectToDecimal(reader["costprice"]); storeProductInfo.State = TypeHelper.ObjectToInt(reader["state"]); storeProductInfo.IsBest = TypeHelper.ObjectToInt(reader["isbest"]); storeProductInfo.IsHot = TypeHelper.ObjectToInt(reader["ishot"]); storeProductInfo.IsNew = TypeHelper.ObjectToInt(reader["isnew"]); storeProductInfo.DisplayOrder = TypeHelper.ObjectToInt(reader["displayorder"]); storeProductInfo.Weight = TypeHelper.ObjectToInt(reader["weight"]); storeProductInfo.ShowImg = reader["showimg"].ToString(); storeProductInfo.SaleCount = TypeHelper.ObjectToInt(reader["salecount"]); storeProductInfo.VisitCount = TypeHelper.ObjectToInt(reader["visitcount"]); storeProductInfo.ReviewCount = TypeHelper.ObjectToInt(reader["reviewcount"]); storeProductInfo.Star1 = TypeHelper.ObjectToInt(reader["star1"]); storeProductInfo.Star2 = TypeHelper.ObjectToInt(reader["star2"]); storeProductInfo.Star3 = TypeHelper.ObjectToInt(reader["star3"]); storeProductInfo.Star4 = TypeHelper.ObjectToInt(reader["star4"]); storeProductInfo.Star5 = TypeHelper.ObjectToInt(reader["star5"]); storeProductInfo.AddTime = TypeHelper.ObjectToDateTime(reader["addtime"]); storeProductInfo.StoreName = reader["storename"].ToString(); storeProductList.Add(storeProductInfo); } reader.Close(); return storeProductList; }