/// <summary> /// 获得分类列表 /// </summary> /// <param name="keyword">关键词</param> /// <returns></returns> public List <CategoryInfo> GetCategoryListByKeyword(string keyword) { DbParameter[] parms = { GenerateInParam("@keyword", SqlDbType.NChar, 40, keyword) }; List <CategoryInfo> categoryList = new List <CategoryInfo>(); IDataReader reader = RDBSHelper.ExecuteReader(CommandType.StoredProcedure, string.Format("{0}getcategorylistbykeyword", RDBSHelper.RDBSTablePre), parms); while (reader.Read()) { CategoryInfo categoryInfo = new CategoryInfo(); categoryInfo.CateId = TypeHelper.ObjectToInt(reader["cateid"]); categoryInfo.DisplayOrder = TypeHelper.ObjectToInt(reader["displayorder"]); categoryInfo.Name = reader["name"].ToString(); categoryInfo.PriceRange = reader["pricerange"].ToString(); categoryInfo.ParentId = TypeHelper.ObjectToInt(reader["parentid"]); categoryInfo.Layer = TypeHelper.ObjectToInt(reader["layer"]); categoryInfo.HasChild = TypeHelper.ObjectToInt(reader["haschild"]); categoryInfo.Path = reader["path"].ToString(); categoryList.Add(categoryInfo); } reader.Close(); return(categoryList); }
/// <summary> /// 获得分类品牌列表 /// </summary> /// <param name="cateId">分类id</param> /// <param name="keyword">关键词</param> /// <returns></returns> public List <BrandInfo> GetCategoryBrandListByKeyword(int cateId, string keyword) { DbParameter[] parms = { GenerateInParam("@cateid", SqlDbType.Int, 4, cateId), GenerateInParam("@keyword", SqlDbType.NChar, 40, keyword) }; List <BrandInfo> brandList = new List <BrandInfo>(); IDataReader reader = RDBSHelper.ExecuteReader(CommandType.StoredProcedure, string.Format("{0}getcategorybrandlistbykeyword", RDBSHelper.RDBSTablePre), parms); while (reader.Read()) { BrandInfo brandInfo = new BrandInfo(); brandInfo.BrandId = TypeHelper.ObjectToInt(reader["brandid"]); brandInfo.DisplayOrder = TypeHelper.ObjectToInt(reader["displayorder"]); brandInfo.Name = reader["name"].ToString(); brandInfo.Logo = reader["logo"].ToString(); brandList.Add(brandInfo); } reader.Close(); return(brandList); }
public IDataReader GetProductList(string productClassId, int count, string sortColumn) { if (string.IsNullOrWhiteSpace(sortColumn)) { sortColumn = "ProductID Desc"; } string commandText; if (string.IsNullOrWhiteSpace(productClassId)) { commandText = string.Format("SELECT top {2} {1} from [{0}Product] where isShow = 1 Order by {3}", RDBSHelper.RDBSTablePre, RDBSFields.PRODUCT, count, sortColumn); } else { commandText = string.Format("SELECT top {3} {1} from [{0}Product] where isShow = 1 and ProductClassID in ({2}) Order by {4}", RDBSHelper.RDBSTablePre, RDBSFields.PRODUCT, productClassId, count, sortColumn); } return(RDBSHelper.ExecuteReader(CommandType.Text, commandText)); }
/// <summary> /// 获得店铺行业列表 /// </summary> public IDataReader GetStoreIndustryList() { string commandText = string.Format("SELECT {1} FROM [{0}storeindustries] ORDER BY [displayorder] DESC", RDBSHelper.RDBSTablePre, RDBSFields.STORE_INDUSTRIES); return(RDBSHelper.ExecuteReader(CommandType.Text, commandText)); }
/// <summary> /// 获得店铺等级列表 /// </summary> /// <returns></returns> public IDataReader GetStoreRankList() { string commandText = string.Format("SELECT {1} FROM [{0}storeranks]", RDBSHelper.RDBSTablePre, RDBSFields.STORE_RANKS); return(RDBSHelper.ExecuteReader(CommandType.Text, commandText)); }
/// <summary> /// 获取产品分类 /// </summary> /// <returns></returns> public IDataReader GetProductClassList() { string commandText = string.Format("SELECT {1} FROM [{0}ProductClass] ORDER BY [displayorder],ProductClassID ", RDBSHelper.RDBSTablePre, RDBSFields.PRODUCT_CLASS); return(RDBSHelper.ExecuteReader(CommandType.Text, commandText)); }
/// <summary> /// 获得订单商品列表 /// </summary> /// <param name="oidList">订单id列表</param> /// <returns></returns> public IDataReader GetOrderProductList(string oidList) { DbParameter[] parms = { GenerateInParam("@oidlist", SqlDbType.NVarChar, 1000, oidList) }; return(RDBSHelper.ExecuteReader(CommandType.StoredProcedure, string.Format("{0}getorderproductlistbyoidlist", RDBSHelper.RDBSTablePre), parms)); }
/// <summary> /// 获得订单信息 /// </summary> /// <param name="osn">订单编号</param> /// <returns>订单信息</returns> public IDataReader GetOrderByOSN(string osn) { DbParameter[] parms = { GenerateInParam("@osn", SqlDbType.Char, 30, osn) }; return(RDBSHelper.ExecuteReader(CommandType.StoredProcedure, string.Format("{0}getorderbyosn", RDBSHelper.RDBSTablePre), parms)); }
/// <summary> /// 获得订单处理列表 /// </summary> /// <param name="oid">订单id</param> /// <returns></returns> public IDataReader GetOrderActionList(int oid) { DbParameter[] parms = { GenerateInParam("@oid", SqlDbType.Int, 4, oid) }; return(RDBSHelper.ExecuteReader(CommandType.StoredProcedure, string.Format("{0}getorderactionlist", RDBSHelper.RDBSTablePre), parms)); }
/// <summary> /// 获得店铺评价 /// </summary> /// <param name="oid">订单id</param> public IDataReader GetStoreReviewByOid(int oid) { DbParameter[] parms = { GenerateInParam("@oid", SqlDbType.Int, 4, oid) }; return(RDBSHelper.ExecuteReader(CommandType.StoredProcedure, string.Format("{0}getstorereviewbyoid", RDBSHelper.RDBSTablePre), parms)); }
/// <summary> /// 获得店铺分类列表 /// </summary> /// <param name="storeId">店铺id</param> /// <returns></returns> public IDataReader GetStoreClassList(int storeId) { DbParameter[] parms = { GenerateInParam("@storeid", SqlDbType.Int, 4, storeId) }; return(RDBSHelper.ExecuteReader(CommandType.StoredProcedure, string.Format("{0}getstoreclasslist", RDBSHelper.RDBSTablePre), parms)); }
/// <summary> /// 获得店铺配送模板 /// </summary> /// <param name="storeSTid">店铺配送模板id</param> /// <returns></returns> public IDataReader GetStoreShipTemplateById(int storeSTid) { DbParameter[] parms = { GenerateInParam("@storestid", SqlDbType.Int, 4, storeSTid) }; return(RDBSHelper.ExecuteReader(CommandType.StoredProcedure, string.Format("{0}getstoreshiptemplatebyid", RDBSHelper.RDBSTablePre), parms)); }
/// <summary> /// 获取指定用户一年内的已购订单商品(指定数量,默认10条) /// </summary> /// <param name="uid"></param> /// <returns></returns> public IDataReader GetOrderProductListByUid(int uid, int count = 10) { DbParameter[] parms = { GenerateInParam("@uid", SqlDbType.Int, 4, uid), GenerateInParam("@addtime", SqlDbType.DateTime, 8, DateTime.Now.AddYears(-1)), }; string sql = string.Format("select top {1} [uid],[oid],[pid],[name],[showimg],[shopprice],[addtime] from [{0}orderproducts] where [addtime] >@addtime and [uid]=@uid and [oid]>0 order by [oid] desc", RDBSHelper.RDBSTablePre, count); return(RDBSHelper.ExecuteReader(CommandType.Text, sql, parms)); }
/// <summary> /// 获取用户所有基本信息 /// </summary> /// <param name="uid"></param> /// <returns></returns> public IDataReader GetUserById(int uid) { DbParameter[] parms = { GenerateInParam("@UserID", SqlDbType.Int, 4, uid) }; string commandText = string.Format("SELECT top 1 a.*,b.* FROM [{0}User] a,[{0}UserDetails] b WHERE a.UserID=b.UserID and a.UserID =@UserID", RDBSHelper.RDBSTablePre); return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms)); }
/// <summary> /// 获得店铺配送费用 /// </summary> /// <param name="storeSTid">店铺模板id</param> /// <param name="provinceId">省id</param> /// <param name="cityId">市id</param> /// <returns></returns> public IDataReader GetStoreShipFeeByStoreSTidAndRegion(int storeSTid, int provinceId, int cityId) { DbParameter[] parms = { GenerateInParam("@storestid", SqlDbType.Int, 4, storeSTid), GenerateInParam("@provinceid", SqlDbType.SmallInt, 2, provinceId), GenerateInParam("@cityid", SqlDbType.SmallInt, 2, cityId) }; return(RDBSHelper.ExecuteReader(CommandType.StoredProcedure, string.Format("{0}getstoreshipfeebystorestidandregion", RDBSHelper.RDBSTablePre), parms)); }
/// <summary> /// 根据ID获取产品信息 /// </summary> /// <param name="productId"></param> /// <returns></returns> public IDataReader GetProductById(int productId) { DbParameter[] parms = { GenerateInParam("@productId", SqlDbType.Int, 4, productId) }; string commandText = string.Format("SELECT {1} FROM [{0}Product] WHERE [productId]=@productId", RDBSHelper.RDBSTablePre, RDBSFields.PRODUCT); return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms)); }
/// <summary> /// 获取用户详细信息 /// </summary> /// <param name="uid"></param> /// <returns></returns> public IDataReader GetUserDetailById(int uid) { DbParameter[] parms = { GenerateInParam("@UserID", SqlDbType.Int, 4, uid) }; string commandText = string.Format("SELECT {1} FROM [{0}UserDetails] WHERE [UserID]=@UserID", RDBSHelper.RDBSTablePre, RDBSFields.USERDETAILS); return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms)); }
/// <summary> /// 获得店铺配送费用 /// </summary> /// <param name="recordId">记录id</param> /// <returns></returns> public IDataReader GetStoreShipFeeById(int recordId) { DbParameter[] parms = { GenerateInParam("@recordid", SqlDbType.Int, 4, recordId) }; string commandText = string.Format("SELECT {1} FROM [{0}storeshipfees] WHERE [recordid]=@recordId", RDBSHelper.RDBSTablePre, RDBSFields.STORE_SHIPFEES); return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms)); }
/// <summary> /// 根据邮箱获取用户基本信息 /// </summary> /// <param name="email"></param> /// <returns></returns> public IDataReader GetPartUserByEmail(string email) { DbParameter[] parms = { GenerateInParam("@Email", SqlDbType.NVarChar, 50, email) }; string commandText = string.Format("SELECT {1} FROM [{0}User] WHERE [Email]=@Email", RDBSHelper.RDBSTablePre, RDBSFields.PARTUSERS); return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms)); }
/// <summary> /// 获得店铺配送模板列表 /// </summary> /// <param name="storeId">店铺id</param> /// <returns></returns> public IDataReader GetStoreShipTemplateList(int storeId) { DbParameter[] parms = { GenerateInParam("@storeid", SqlDbType.Int, 4, storeId) }; string commandText = string.Format("SELECT {1} FROM [{0}storeshiptemplates] WHERE [storeid]=@storeid", RDBSHelper.RDBSTablePre, RDBSFields.STORE_SHIPTEMPLATES); return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms)); }
/// <summary> /// 获得店长 /// </summary> /// <param name="storeId">店铺id</param> /// <returns></returns> public IDataReader GetStoreKeeperById(int storeId) { DbParameter[] parms = { GenerateInParam("@storeid", SqlDbType.Int, 4, storeId) }; string commandText = string.Format("SELECT {1} FROM [{0}storekeepers] WHERE [storeid]=@storeid", RDBSHelper.RDBSTablePre, RDBSFields.STORE_KEEPERS); return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms)); }
public System.Data.IDataReader GetProductFeedbacksInfoById(int id) { DbParameter[] parms = { GenerateInParam("@id", SqlDbType.Int, 4, id) }; string commandText = string.Format("SELECT {1} FROM [{0}ProductFeedbacks] WHERE [id]=@id", RDBSHelper.RDBSTablePre, RDBSFields.PRODUCTFEEDBACKS); return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms)); }
public System.Data.IDataReader GetShopById(int shopId) { DbParameter[] parms = { GenerateInParam("@shopId", SqlDbType.Int, 4, shopId) }; string commandText = string.Format("SELECT {1} FROM [{0}Shops] WHERE [shopId]=@shopId", RDBSHelper.RDBSTablePre, RDBSFields.SHOPS); return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms)); }
public System.Data.IDataReader GetServiceEvalInfoById(int id) { DbParameter[] parms = { GenerateInParam("@id", SqlDbType.Int, 4, id) }; string commandText = string.Format("SELECT {1} FROM [{0}ServiceEval] WHERE [id]=@id", RDBSHelper.RDBSTablePre, RDBSFields.SERVICEEVAL); return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms)); }
public System.Data.IDataReader GetJobById(int jobId) { DbParameter[] parms = { GenerateInParam("@jobId", SqlDbType.Int, 4, jobId) }; string commandText = string.Format("SELECT {1} FROM [{0}jobs] WHERE [jobId]=@jobId", RDBSHelper.RDBSTablePre, RDBSFields.JOBS); return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms)); }
/// <summary> /// 获得订单退款列表 /// </summary> /// <param name="pageSize">每页数</param> /// <param name="pageNumber">当前页数</param> /// <param name="condition">条件</param> /// <returns></returns> public IDataReader GetOrderRefundList(int pageSize, int pageNumber, string condition) { bool noCondition = string.IsNullOrWhiteSpace(condition); string commandText; if (pageNumber == 1) { if (noCondition) { commandText = string.Format("SELECT TOP {0} {2} FROM [{1}orderrefunds] ORDER BY [refundid] DESC", pageSize, RDBSHelper.RDBSTablePre, RDBSFields.ORDER_REFUNDS); } else { commandText = string.Format("SELECT TOP {0} {3} FROM [{1}orderrefunds] WHERE {2} ORDER BY [refundid] DESC", pageSize, RDBSHelper.RDBSTablePre, condition, RDBSFields.ORDER_REFUNDS); } } else { if (noCondition) { commandText = string.Format("SELECT TOP {0} {3} FROM [{1}orderrefunds] WHERE [refundid] < (SELECT MIN([refundid]) FROM (SELECT TOP {2} [refundid] FROM [{1}orderrefunds] ORDER BY [refundid] DESC) AS [temp]) ORDER BY [refundid] DESC", pageSize, RDBSHelper.RDBSTablePre, (pageNumber - 1) * pageSize, RDBSFields.ORDER_REFUNDS); } else { commandText = string.Format("SELECT TOP {0} {4} FROM [{1}orderrefunds] WHERE [refundid] < (SELECT MIN([refundid]) FROM (SELECT TOP {2} [refundid] FROM [{1}orderrefunds] WHERE {3} ORDER BY [refundid] DESC) AS [temp]) AND {3} ORDER BY [refundid] DESC", pageSize, RDBSHelper.RDBSTablePre, (pageNumber - 1) * pageSize, condition, RDBSFields.ORDER_REFUNDS); } } return(RDBSHelper.ExecuteReader(CommandType.Text, commandText)); }
/// <summary> /// 获得商品关键词列表 /// </summary> /// <param name="pid">商品id</param> /// <returns></returns> public List <ProductKeywordInfo> GetProductKeywordList(int pid) { List <ProductKeywordInfo> productKeywordList = new List <ProductKeywordInfo>(); DbParameter[] parms = { GenerateInParam("@pid", SqlDbType.Int, 4, pid) }; string commandText = string.Format("SELECT [keyword],[pid],[relevancy] FROM [{0}productkeywords] WHERE [pid]=@pid", RDBSHelper.RDBSTablePre); IDataReader reader = RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms); while (reader.Read()) { ProductKeywordInfo productKeywordInfo = BuildProductKeyWordFromReader(reader); productKeywordList.Add(productKeywordInfo); } reader.Close(); return(productKeywordList); }
/// <summary> /// 获得购物车商品列表 /// </summary> /// <param name="sid">用户sid</param> /// <returns></returns> public List <OrderProductInfo> GetCartProductList(string sid) { List <OrderProductInfo> orderProductList = new List <OrderProductInfo>(); DbParameter[] parms = { GenerateInParam("@sid", SqlDbType.Char, 16, sid) }; IDataReader reader = RDBSHelper.ExecuteReader(CommandType.StoredProcedure, string.Format("{0}getcartproductlistbysid", RDBSHelper.RDBSTablePre), parms); while (reader.Read()) { OrderProductInfo orderProductInfo = BuildOrderProductFromReader(reader); orderProductList.Add(orderProductInfo); } reader.Close(); return(orderProductList); }
/// <summary> /// 搜索店铺商品 /// </summary> /// <param name="pageSize">每页数</param> /// <param name="pageNumber">当前页数</param> /// <param name="keyword">关键词</param> /// <param name="storeId">店铺id</param> /// <param name="storeCid">店铺分类id</param> /// <param name="startPrice">开始价格</param> /// <param name="endPrice">结束价格</param> /// <param name="sortColumn">排序列</param> /// <param name="sortDirection">排序方向</param> /// <returns></returns> public List <PartProductInfo> SearchStoreProducts(int pageSize, int pageNumber, string keyword, int storeId, int storeCid, int startPrice, int endPrice, 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] FROM [{0}products] AS [p]", RDBSHelper.RDBSTablePre, pageSize); commandText.AppendFormat(" LEFT JOIN [{0}productkeywords] AS [pk] ON [p].[pid]=[pk].[pid]", RDBSHelper.RDBSTablePre); commandText.AppendFormat(" WHERE [p].[storeid]={0}", storeId); if (storeCid > 0) { commandText.AppendFormat(" AND [p].[storecid]={0}", storeCid); } if (startPrice > 0) { commandText.AppendFormat(" AND [p].[shopprice]>={0}", startPrice); } if (endPrice > 0) { commandText.AppendFormat(" AND [p].[shopprice]<={0}", endPrice); } commandText.Append(" AND [p].[state]=0"); commandText.AppendFormat(" AND [pk].[keyword]='{0}'", keyword); 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] 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] FROM [{0}products] AS [p]", RDBSHelper.RDBSTablePre); commandText.AppendFormat(" LEFT JOIN [{0}productkeywords] AS [pk] ON [p].[pid]=[pk].[pid]", RDBSHelper.RDBSTablePre); commandText.AppendFormat(" WHERE [p].[storeid]={0}", storeId); if (storeCid > 0) { commandText.AppendFormat(" AND [p].[storecid]={0}", storeCid); } if (startPrice > 0) { commandText.AppendFormat(" AND [p].[shopprice]>={0}", startPrice); } if (endPrice > 0) { commandText.AppendFormat(" AND [p].[shopprice]<={0}", endPrice); } commandText.Append(" AND [p].[state]=0"); commandText.AppendFormat(" AND [pk].[keyword]='{0}'", keyword); commandText.Append(") AS [temp]"); commandText.AppendFormat(" WHERE [rowid] BETWEEN {0} AND {1}", pageSize * (pageNumber - 1) + 1, pageSize * pageNumber); } List <PartProductInfo> partProductList = new List <PartProductInfo>(); IDataReader reader = RDBSHelper.ExecuteReader(CommandType.Text, commandText.ToString()); while (reader.Read()) { PartProductInfo partProductInfo = new PartProductInfo(); partProductInfo.Pid = TypeHelper.ObjectToInt(reader["pid"]); partProductInfo.PSN = reader["psn"].ToString(); partProductInfo.CateId = TypeHelper.ObjectToInt(reader["cateid"]); partProductInfo.BrandId = TypeHelper.ObjectToInt(reader["brandid"]); partProductInfo.StoreId = TypeHelper.ObjectToInt(reader["storeid"]); partProductInfo.StoreCid = TypeHelper.ObjectToInt(reader["storecid"]); partProductInfo.StoreSTid = TypeHelper.ObjectToInt(reader["storestid"]); partProductInfo.SKUGid = TypeHelper.ObjectToInt(reader["skugid"]); partProductInfo.Name = reader["name"].ToString(); partProductInfo.ShopPrice = TypeHelper.ObjectToDecimal(reader["shopprice"]); partProductInfo.MarketPrice = TypeHelper.ObjectToDecimal(reader["marketprice"]); partProductInfo.CostPrice = TypeHelper.ObjectToDecimal(reader["costprice"]); partProductInfo.State = TypeHelper.ObjectToInt(reader["state"]); partProductInfo.IsBest = TypeHelper.ObjectToInt(reader["isbest"]); partProductInfo.IsHot = TypeHelper.ObjectToInt(reader["ishot"]); partProductInfo.IsNew = TypeHelper.ObjectToInt(reader["isnew"]); partProductInfo.DisplayOrder = TypeHelper.ObjectToInt(reader["displayorder"]); partProductInfo.Weight = TypeHelper.ObjectToInt(reader["weight"]); partProductInfo.ShowImg = reader["showimg"].ToString(); partProductInfo.SaleCount = TypeHelper.ObjectToInt(reader["salecount"]); partProductInfo.VisitCount = TypeHelper.ObjectToInt(reader["visitcount"]); partProductInfo.ReviewCount = TypeHelper.ObjectToInt(reader["reviewcount"]); partProductInfo.Star1 = TypeHelper.ObjectToInt(reader["star1"]); partProductInfo.Star2 = TypeHelper.ObjectToInt(reader["star2"]); partProductInfo.Star3 = TypeHelper.ObjectToInt(reader["star3"]); partProductInfo.Star4 = TypeHelper.ObjectToInt(reader["star4"]); partProductInfo.Star5 = TypeHelper.ObjectToInt(reader["star5"]); partProductInfo.AddTime = TypeHelper.ObjectToDateTime(reader["addtime"]); partProductList.Add(partProductInfo); } reader.Close(); return(partProductList); }
/// <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); }