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