public StoreProductInfo GetStoreProductModel(int ProductId, int StoreId)
{
StoreProductInfo result = null;
DbCommand sqlStringCommand = base.database.GetSqlStringCommand("SELECT * FROM [Hishop_StoreProducts] WHERE ProductID = @ProductId AND StoreId = @StoreId");
base.database.AddInParameter(sqlStringCommand, "ProductID", DbType.Int32, ProductId);
base.database.AddInParameter(sqlStringCommand, "storeId", DbType.Int32, StoreId);
using (IDataReader objReader = base.database.ExecuteReader(sqlStringCommand))
{
result = DataHelper.ReaderToModel <StoreProductInfo>(objReader);
}
return(result);
}
/// <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);
}
/// <summary>
/// 搜索商城商品
/// </summary>
/// <param name="pageSize">每页数</param>
/// <param name="pageNumber">当前页数</param>
/// <param name="word">搜索词</param>
/// <param name="cateId">分类id</param>
/// <param name="brandId">品牌id</param>
/// <param name="filterPrice">筛选价格</param>
/// <param name="attrValueIdList">属性值id列表</param>
/// <param name="onlyStock">是否只显示有货</param>
/// <param name="sortColumn">排序列</param>
/// <param name="sortDirection">排序方向</param>
/// <param name="categoryInfo">分类信息</param>
/// <param name="catePriceRangeList">分类价格范围列表</param>
/// <param name="cateAAndVList">分类筛选属性及其值列表</param>
/// <param name="categoryList">分类列表</param>
/// <param name="brandInfo">品牌信息</param>
/// <param name="brandList">品牌列表</param>
/// <param name="totalCount">商品总数量</param>
/// <param name="productList">商品列表</param>
public void SearchMallProducts(int pageSize, int pageNumber, string word, int cateId, int brandId, int filterPrice, List <int> attrValueIdList, int onlyStock, int sortColumn, int sortDirection, ref CategoryInfo categoryInfo, ref string[] catePriceRangeList, ref List <KeyValuePair <AttributeInfo, List <AttributeValueInfo> > > cateAAndVList, ref List <CategoryInfo> categoryList, ref BrandInfo brandInfo, ref List <BrandInfo> brandList, ref int totalCount, ref List <StoreProductInfo> productList)
{
//针对用户搜索词进行分词
List <string> keywordList = Analyse(word);
//构建关键词查询sql
string keywordSql = BuildKeywordSql(keywordList);
#region 设置分类列表
categoryList = new List <CategoryInfo>();
StringBuilder commandText3 = new StringBuilder();
commandText3.AppendFormat("SELECT [cateid],[displayorder],[name],[pricerange],[parentid],[layer],[haschild],[path] FROM [{0}categories] WHERE [cateid] IN (SELECT DISTINCT [cateid] FROM [{0}products] WHERE [state]=0 AND [pid] IN (SELECT [pid] FROM [{0}productkeywords] WHERE [keyword] IN ({1})))",
RDBSHelper.RDBSTablePre,
keywordSql);
IDataReader reader2 = RDBSHelper.ExecuteReader(CommandType.Text, commandText3.ToString());
while (reader2.Read())
{
CategoryInfo info = new CategoryInfo();
info.CateId = TypeHelper.ObjectToInt(reader2["cateid"]);
info.DisplayOrder = TypeHelper.ObjectToInt(reader2["displayorder"]);
info.Name = reader2["name"].ToString();
info.PriceRange = reader2["pricerange"].ToString();
info.ParentId = TypeHelper.ObjectToInt(reader2["parentid"]);
info.Layer = TypeHelper.ObjectToInt(reader2["layer"]);
info.HasChild = TypeHelper.ObjectToInt(reader2["haschild"]);
info.Path = reader2["path"].ToString();
categoryList.Add(info);
}
reader2.Close();
#endregion
//当关联分类列表中一个分类也不存在时，认为商品也不存在，直接返回
if (categoryList.Count < 1)
{
return;
}
if (cateId > 0)
{
foreach (CategoryInfo info in categoryList)
{
if (info.CateId == cateId)
{
categoryInfo = info;
break;
}
}
//当筛选了分类，但是分类不在分类列表中，认为商品也不存在，直接返回
if (categoryInfo == null)
{
return;
}
}
if (categoryInfo != null)
{
cateAAndVList = Categories.GetCategoryFilterAAndVList(categoryInfo.CateId);
catePriceRangeList = StringHelper.SplitString(categoryInfo.PriceRange, "\r\n");
}
#region 设置品牌列表
brandList = new List <BrandInfo>();
StringBuilder commandText4 = new StringBuilder();
commandText4.AppendFormat("SELECT [brandid],[displayorder],[name],[logo] FROM [{0}brands] WHERE [brandid] IN (SELECT DISTINCT [brandid] FROM [{0}products] WHERE [state]=0 {1} AND [pid] IN (SELECT [pid] FROM [{0}productkeywords] WHERE [keyword] IN ({2})))",
RDBSHelper.RDBSTablePre,
categoryInfo == null ? "" : "AND [cateid]=" + categoryInfo.CateId,
keywordSql);
IDataReader reader3 = RDBSHelper.ExecuteReader(CommandType.Text, commandText4.ToString());
while (reader3.Read())
{
BrandInfo info = new BrandInfo();
info.BrandId = TypeHelper.ObjectToInt(reader3["brandid"]);
info.DisplayOrder = TypeHelper.ObjectToInt(reader3["displayorder"]);
info.Name = reader3["name"].ToString();
info.Logo = reader3["logo"].ToString();
brandList.Add(info);
}
reader3.Close();
#endregion
//当品牌列表中一个品牌也不存在时，认为商品也不存在，直接返回
if (brandList.Count < 1)
{
return;
}
//当筛选了品牌，但是品牌不在品牌列表中，认为商品也不存在，直接返回
if (brandId > 0)
{
bool flag = true;
foreach (BrandInfo info in brandList)
{
if (info.BrandId == brandId)
{
brandInfo = info;
flag = false;
break;
}
}
if (flag)
{
return;
}
}
#region 获取商品列表
StringBuilder commandText1 = new StringBuilder();
if (pageNumber == 1)
{
commandText1.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)
{
commandText1.AppendFormat(" LEFT JOIN [{0}productstocks] AS [ps] ON [p].[pid]=[ps].[pid]", RDBSHelper.RDBSTablePre);
}
commandText1.AppendFormat(" LEFT JOIN [{0}productkeywords] AS [pk] ON [p].[pid]=[pk].[pid]", RDBSHelper.RDBSTablePre);
commandText1.AppendFormat(" LEFT JOIN [{0}stores] AS [s] ON [p].[storeid]=[s].[storeid]", RDBSHelper.RDBSTablePre);
commandText1.Append(" WHERE [p].[state]=0");
if (categoryInfo != null)
{
commandText1.AppendFormat(" AND [p].[cateid]={0}", categoryInfo.CateId);
}
if (brandId > 0)
{
commandText1.AppendFormat(" AND [p].[brandid]={0}", brandId);
}
if (categoryInfo != null && filterPrice > 0 && filterPrice <= catePriceRangeList.Length)
{
string[] priceRange = StringHelper.SplitString(catePriceRangeList[filterPrice - 1], "-");
if (priceRange.Length == 1)
{
commandText1.AppendFormat(" AND [p].[shopprice]>='{0}'", priceRange[0]);
}
else if (priceRange.Length == 2)
{
commandText1.AppendFormat(" AND [p].[shopprice]>='{0}' AND [p].[shopprice]<'{1}'", priceRange[0], priceRange[1]);
}
}
if (categoryInfo != null && attrValueIdList.Count > 0 && attrValueIdList.Count <= cateAAndVList.Count)
{
commandText1.Append(" AND [p].[pid] IN (SELECT [pa1].[pid] FROM");
for (int i = 0; i < attrValueIdList.Count; i++)
{
if (i == 0)
{
commandText1.AppendFormat(" (SELECT [pid] FROM [{0}productattributes] WHERE [attrvalueid]={1}) AS [pa1]", RDBSHelper.RDBSTablePre, attrValueIdList[i]);
}
else
{
commandText1.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);
}
}
commandText1.Append(")");
}
if (onlyStock == 1)
{
commandText1.Append(" AND [ps].[number]>0");
}
commandText1.AppendFormat(" AND [pk].[keyword] IN ({0})", keywordSql);
commandText1.Append(" AND [s].[state]=0");
commandText1.Append(" ORDER BY ");
switch (sortColumn)
{
case 0:
commandText1.Append("[pk].[relevancy]");
break;
case 1:
commandText1.Append("[p].[salecount]");
break;
case 2:
commandText1.Append("[p].[shopprice]");
break;
case 3:
commandText1.Append("[p].[reviewcount]");
break;
case 4:
commandText1.Append("[p].[addtime]");
break;
case 5:
commandText1.Append("[p].[visitcount]");
break;
default:
commandText1.Append("[pk].[relevancy]");
break;
}
switch (sortDirection)
{
case 0:
commandText1.Append(" DESC");
break;
case 1:
commandText1.Append(" ASC");
break;
default:
commandText1.Append(" DESC");
break;
}
commandText1.Append(",[p].[pid] DESC");
}
else
{
commandText1.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");
commandText1.Append(" (SELECT ROW_NUMBER() OVER (ORDER BY ");
switch (sortColumn)
{
case 0:
commandText1.Append("[pk].[relevancy]");
break;
case 1:
commandText1.Append("[p].[salecount]");
break;
case 2:
commandText1.Append("[p].[shopprice]");
break;
case 3:
commandText1.Append("[p].[reviewcount]");
break;
case 4:
commandText1.Append("[p].[addtime]");
break;
case 5:
commandText1.Append("[p].[visitcount]");
break;
default:
commandText1.Append("[pk].[relevancy]");
break;
}
switch (sortDirection)
{
case 0:
commandText1.Append(" DESC");
break;
case 1:
commandText1.Append(" ASC");
break;
default:
commandText1.Append(" DESC");
break;
}
commandText1.Append(",[p].[pid] DESC");
commandText1.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)
{
commandText1.AppendFormat(" LEFT JOIN [{0}productstocks] AS [ps] ON [p].[pid]=[ps].[pid]", RDBSHelper.RDBSTablePre);
}
commandText1.AppendFormat(" LEFT JOIN [{0}productkeywords] AS [pk] ON [p].[pid]=[pk].[pid]", RDBSHelper.RDBSTablePre);
commandText1.AppendFormat(" LEFT JOIN [{0}stores] AS [s] ON [p].[storeid]=[s].[storeid]", RDBSHelper.RDBSTablePre);
commandText1.Append(" WHERE [p].[state]=0");
if (categoryInfo != null)
{
commandText1.AppendFormat(" AND [p].[cateid]={0}", categoryInfo.CateId);
}
if (brandId > 0)
{
commandText1.AppendFormat(" AND [p].[brandid]={0}", brandId);
}
if (categoryInfo != null && filterPrice > 0 && filterPrice <= catePriceRangeList.Length)
{
string[] priceRange = StringHelper.SplitString(catePriceRangeList[filterPrice - 1], "-");
if (priceRange.Length == 1)
{
commandText1.AppendFormat(" AND [p].[shopprice]>='{0}'", priceRange[0]);
}
else if (priceRange.Length == 2)
{
commandText1.AppendFormat(" AND [p].[shopprice]>='{0}' AND [p].[shopprice]<'{1}'", priceRange[0], priceRange[1]);
}
}
if (categoryInfo != null && attrValueIdList.Count > 0 && attrValueIdList.Count == cateAAndVList.Count)
{
commandText1.Append(" AND [p].[pid] IN (SELECT [pa1].[pid] FROM");
for (int i = 0; i < attrValueIdList.Count; i++)
{
if (i == 0)
{
commandText1.AppendFormat(" (SELECT [pid] FROM [{0}productattributes] WHERE [attrvalueid]={1}) AS [pa1]", RDBSHelper.RDBSTablePre, attrValueIdList[i]);
}
else
{
commandText1.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);
}
}
commandText1.Append(")");
}
if (onlyStock == 1)
{
commandText1.Append(" AND [ps].[number]>0");
}
commandText1.AppendFormat(" AND [pk].[keyword] IN ({0}) ", keywordSql);
commandText1.Append(" AND [s].[state]=0");
commandText1.Append(") AS [temp]");
commandText1.AppendFormat(" WHERE [rowid] BETWEEN {0} AND {1}", pageSize * (pageNumber - 1) + 1, pageSize * pageNumber);
}
productList = new List <StoreProductInfo>();
IDataReader reader1 = RDBSHelper.ExecuteReader(CommandType.Text, commandText1.ToString());
while (reader1.Read())
{
StoreProductInfo storeProductInfo = new StoreProductInfo();
storeProductInfo.Pid = TypeHelper.ObjectToInt(reader1["pid"]);
storeProductInfo.PSN = reader1["psn"].ToString();
storeProductInfo.CateId = TypeHelper.ObjectToInt(reader1["cateid"]);
storeProductInfo.BrandId = TypeHelper.ObjectToInt(reader1["brandid"]);
storeProductInfo.StoreId = TypeHelper.ObjectToInt(reader1["storeid"]);
storeProductInfo.StoreCid = TypeHelper.ObjectToInt(reader1["storecid"]);
storeProductInfo.StoreSTid = TypeHelper.ObjectToInt(reader1["storestid"]);
storeProductInfo.SKUGid = TypeHelper.ObjectToInt(reader1["skugid"]);
storeProductInfo.Name = reader1["name"].ToString();
storeProductInfo.ShopPrice = TypeHelper.ObjectToDecimal(reader1["shopprice"]);
storeProductInfo.MarketPrice = TypeHelper.ObjectToDecimal(reader1["marketprice"]);
storeProductInfo.CostPrice = TypeHelper.ObjectToDecimal(reader1["costprice"]);
storeProductInfo.State = TypeHelper.ObjectToInt(reader1["state"]);
storeProductInfo.IsBest = TypeHelper.ObjectToInt(reader1["isbest"]);
storeProductInfo.IsHot = TypeHelper.ObjectToInt(reader1["ishot"]);
storeProductInfo.IsNew = TypeHelper.ObjectToInt(reader1["isnew"]);
storeProductInfo.DisplayOrder = TypeHelper.ObjectToInt(reader1["displayorder"]);
storeProductInfo.Weight = TypeHelper.ObjectToInt(reader1["weight"]);
storeProductInfo.ShowImg = reader1["showimg"].ToString();
storeProductInfo.SaleCount = TypeHelper.ObjectToInt(reader1["salecount"]);
storeProductInfo.VisitCount = TypeHelper.ObjectToInt(reader1["visitcount"]);
storeProductInfo.ReviewCount = TypeHelper.ObjectToInt(reader1["reviewcount"]);
storeProductInfo.Star1 = TypeHelper.ObjectToInt(reader1["star1"]);
storeProductInfo.Star2 = TypeHelper.ObjectToInt(reader1["star2"]);
storeProductInfo.Star3 = TypeHelper.ObjectToInt(reader1["star3"]);
storeProductInfo.Star4 = TypeHelper.ObjectToInt(reader1["star4"]);
storeProductInfo.Star5 = TypeHelper.ObjectToInt(reader1["star5"]);
storeProductInfo.AddTime = TypeHelper.ObjectToDateTime(reader1["addtime"]);
storeProductInfo.StoreName = reader1["storename"].ToString();
productList.Add(storeProductInfo);
}
reader1.Close();
#endregion
#region 设置商品总数量
StringBuilder commandText2 = new StringBuilder();
commandText2.AppendFormat("SELECT COUNT([p].[pid]) FROM [{0}products] AS [p]", RDBSHelper.RDBSTablePre);
if (onlyStock == 1)
{
commandText2.AppendFormat(" LEFT JOIN [{0}productstocks] AS [ps] ON [p].[pid]=[ps].[pid]", RDBSHelper.RDBSTablePre);
}
commandText2.AppendFormat(" LEFT JOIN [{0}productkeywords] AS [pk] ON [p].[pid]=[pk].[pid]", RDBSHelper.RDBSTablePre);
commandText2.AppendFormat(" LEFT JOIN [{0}stores] AS [s] ON [p].[storeid]=[s].[storeid]", RDBSHelper.RDBSTablePre);
commandText2.Append(" WHERE [p].[state]=0");
if (categoryInfo != null)
{
commandText2.AppendFormat(" AND [p].[cateid]={0}", categoryInfo.CateId);
}
if (brandId > 0)
{
commandText2.AppendFormat(" AND [p].[brandid]={0}", brandId);
}
if (categoryInfo != null && filterPrice > 0 && filterPrice <= catePriceRangeList.Length)
{
string[] priceRange = StringHelper.SplitString(catePriceRangeList[filterPrice - 1], "-");
if (priceRange.Length == 1)
{
commandText2.AppendFormat(" AND [p].[shopprice]>='{0}'", priceRange[0]);
}
else if (priceRange.Length == 2)
{
commandText2.AppendFormat(" AND [p].[shopprice]>='{0}' AND [p].[shopprice]<'{1}'", priceRange[0], priceRange[1]);
}
}
if (categoryInfo != null && attrValueIdList.Count > 0 && attrValueIdList.Count <= cateAAndVList.Count)
{
commandText2.Append(" AND [p].[pid] IN (SELECT [pa1].[pid] FROM");
for (int i = 0; i < attrValueIdList.Count; i++)
{
if (i == 0)
{
commandText2.AppendFormat(" (SELECT [pid] FROM [{0}productattributes] WHERE [attrvalueid]={1}) AS [pa1]", RDBSHelper.RDBSTablePre, attrValueIdList[i]);
}
else
{
commandText2.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);
}
}
commandText2.Append(")");
}
if (onlyStock == 1)
{
commandText2.Append(" AND [ps].[number]>0");
}
commandText2.AppendFormat(" AND [pk].[keyword] IN ({0}) ", keywordSql);
commandText2.Append(" AND [s].[state]=0");
totalCount = TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(CommandType.Text, commandText2.ToString()));
#endregion
}
public bool AddStoreProduct(DataTable dtStoreStock, DataTable dtlog, List <int> lstProductId, int StoreId)
{
using (SqlConnection sqlConnection = new SqlConnection(base.database.ConnectionString))
{
sqlConnection.Open();
SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
try
{
foreach (int item in lstProductId)
{
StoreProductInfo storeProductModel = this.GetStoreProductModel(item, StoreId);
if (storeProductModel != null)
{
storeProductModel.SaleStatus = 1;
storeProductModel.UpdateTime = DateTime.Now;
if (!this.Update(storeProductModel, sqlTransaction))
{
sqlTransaction.Rollback();
return(false);
}
}
else
{
storeProductModel = new StoreProductInfo();
storeProductModel.ProductId = item;
storeProductModel.StoreId = StoreId;
storeProductModel.SaleCounts = 0;
storeProductModel.SaleStatus = 1;
storeProductModel.UpdateTime = DateTime.Now;
if (this.Add(storeProductModel, sqlTransaction) <= 0)
{
sqlTransaction.Rollback();
return(false);
}
}
}
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.CheckConstraints, sqlTransaction);
sqlBulkCopy.DestinationTableName = dtStoreStock.TableName;
for (int i = 0; i < dtStoreStock.Columns.Count; i++)
{
sqlBulkCopy.ColumnMappings.Add(dtStoreStock.Columns[i].ColumnName, dtStoreStock.Columns[i].ColumnName);
}
sqlBulkCopy.WriteToServer(dtStoreStock);
SqlBulkCopy sqlBulkCopy2 = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.CheckConstraints, sqlTransaction);
sqlBulkCopy2.DestinationTableName = dtlog.TableName;
for (int j = 0; j < dtlog.Columns.Count; j++)
{
sqlBulkCopy2.ColumnMappings.Add(dtlog.Columns[j].ColumnName, dtlog.Columns[j].ColumnName);
}
sqlBulkCopy2.WriteToServer(dtlog);
sqlTransaction.Commit();
return(true);
}
catch (Exception)
{
sqlTransaction.Rollback();
return(false);
}
finally
{
sqlConnection.Close();
}
}
}
