/// <summary>
/// 获得满减商品列表
/// </summary>
/// <param name="pageSize">每页数</param>
/// <param name="pageNumber">当前页数</param>
/// <param name="fullCutPromotionInfo">满减促销活动</param>
/// <param name="startPrice">开始价格</param>
/// <param name="endPrice">结束价格</param>
/// <param name="sortColumn">排序列</param>
/// <param name="sortDirection">排序方向</param>
/// <returns></returns>
public IDataReader GetFullCutProductList(int pageSize, int pageNumber, FullCutPromotionInfo fullCutPromotionInfo, int startPrice, int endPrice, int sortColumn, int sortDirection)
{
StringBuilder commandText = new StringBuilder();
if (pageNumber == 1)
{
commandText.AppendFormat("SELECT TOP {1} [pid],[psn],[cateid],[brandid],[skugid],[name],[shopprice],[marketprice],[costprice],[state],[isbest],[ishot],[isnew],[displayorder],[weight],[showimg],[salecount],[visitcount],[reviewcount],[star1],[star2],[star3],[star4],[star5],[addtime] FROM [{0}products]", RDBSHelper.RDBSTablePre, pageSize);
if (fullCutPromotionInfo.Type == 1)
commandText.AppendFormat(" WHERE [pid] IN (SELECT [pid] FROM [{0}fullcutproducts] WHERE [pmid]={1})", RDBSHelper.RDBSTablePre, fullCutPromotionInfo.PmId);
else if (fullCutPromotionInfo.Type == 2)
commandText.AppendFormat(" WHERE [pid] NOT IN (SELECT [pid] FROM [{0}fullcutproducts] WHERE [pmid]={1})", RDBSHelper.RDBSTablePre, fullCutPromotionInfo.PmId);
if (startPrice > 0)
commandText.AppendFormat(" AND [shopprice]>={0}", startPrice);
if (endPrice > 0)
commandText.AppendFormat(" AND [shopprice]<={0}", endPrice);
commandText.Append(" AND [state]=0");
commandText.Append(" ORDER BY ");
switch (sortColumn)
{
case 0:
commandText.Append("[salecount]");
break;
case 1:
commandText.Append("[shopprice]");
break;
case 2:
commandText.Append("[reviewcount]");
break;
case 3:
commandText.Append("[addtime]");
break;
case 4:
commandText.Append("[visitcount]");
break;
default:
commandText.Append("[salecount]");
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],[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("[salecount]");
break;
case 1:
commandText.Append("[shopprice]");
break;
case 2:
commandText.Append("[reviewcount]");
break;
case 3:
commandText.Append("[addtime]");
break;
case 4:
commandText.Append("[visitcount]");
break;
default:
commandText.Append("[salecount]");
break;
}
switch (sortDirection)
{
case 0:
commandText.Append(" DESC");
break;
case 1:
commandText.Append(" ASC");
break;
default:
commandText.Append(" DESC");
break;
}
commandText.AppendFormat(") AS [rowid],[pid],[psn],[cateid],[brandid],[skugid],[name],[shopprice],[marketprice],[costprice],[state],[isbest],[ishot],[isnew],[displayorder],[weight],[showimg],[salecount],[visitcount],[reviewcount],[star1],[star2],[star3],[star4],[star5],[addtime] FROM [{0}products]", RDBSHelper.RDBSTablePre);
if (fullCutPromotionInfo.Type == 1)
commandText.AppendFormat(" WHERE [pid] IN (SELECT [pid] FROM [{0}fullcutproducts] WHERE [pmid]={1})", RDBSHelper.RDBSTablePre, fullCutPromotionInfo.PmId);
else if (fullCutPromotionInfo.Type == 2)
commandText.AppendFormat(" WHERE [pid] NOT IN (SELECT [pid] FROM [{0}fullcutproducts] WHERE [pmid]={1})", RDBSHelper.RDBSTablePre, fullCutPromotionInfo.PmId);
if (startPrice > 0)
commandText.AppendFormat(" AND [shopprice]>={0}", startPrice);
if (endPrice > 0)
commandText.AppendFormat(" AND [shopprice]<={0}", endPrice);
commandText.Append(" AND [state]=0");
commandText.Append(") AS [temp]");
commandText.AppendFormat(" WHERE [rowid] BETWEEN {0} AND {1}", pageSize * (pageNumber - 1) + 1, pageSize * pageNumber);
}
return RDBSHelper.ExecuteReader(CommandType.Text, commandText.ToString());
}
/// <summary>
/// 更新满减促销活动
/// </summary>
public void UpdateFullCutPromotion(FullCutPromotionInfo fullCutPromotionInfo)
{
DbParameter[] parms = {
GenerateInParam("@type", SqlDbType.TinyInt, 1, fullCutPromotionInfo.Type),
GenerateInParam("@starttime", SqlDbType.DateTime,8,fullCutPromotionInfo.StartTime),
GenerateInParam("@endtime", SqlDbType.DateTime,8,fullCutPromotionInfo.EndTime),
GenerateInParam("@userranklower", SqlDbType.SmallInt,2,fullCutPromotionInfo.UserRankLower),
GenerateInParam("@state", SqlDbType.TinyInt,1,fullCutPromotionInfo.State),
GenerateInParam("@name", SqlDbType.NVarChar,50,fullCutPromotionInfo.Name),
GenerateInParam("@limitmoney1", SqlDbType.Int, 4, fullCutPromotionInfo.LimitMoney1),
GenerateInParam("@cutmoney1", SqlDbType.Int, 4, fullCutPromotionInfo.CutMoney1),
GenerateInParam("@limitmoney2", SqlDbType.Int, 4, fullCutPromotionInfo.LimitMoney2),
GenerateInParam("@cutmoney2", SqlDbType.Int, 4, fullCutPromotionInfo.CutMoney2),
GenerateInParam("@limitmoney3", SqlDbType.Int, 4, fullCutPromotionInfo.LimitMoney3),
GenerateInParam("@cutmoney3", SqlDbType.Int, 4, fullCutPromotionInfo.CutMoney3),
GenerateInParam("@pmid", SqlDbType.Int,4,fullCutPromotionInfo.PmId)
};
string commandText = string.Format("UPDATE [{0}fullcutpromotions] SET [type]=@type,[starttime]=@starttime,[endtime]=@endtime,[userranklower]=@userranklower,[state]=@state,[name]=@name,[limitmoney1]=@limitmoney1,[cutmoney1]=@cutmoney1,[limitmoney2]=@limitmoney2,[cutmoney2]=@cutmoney2,[limitmoney3]=@limitmoney3,[cutmoney3]=@cutmoney3 WHERE [pmid]=@pmid",
RDBSHelper.RDBSTablePre);
RDBSHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
}
/// <summary>
/// 创建满减促销活动
/// </summary>
public void CreateFullCutPromotion(FullCutPromotionInfo fullCutPromotionInfo)
{
DbParameter[] parms = {
GenerateInParam("@type", SqlDbType.TinyInt, 1, fullCutPromotionInfo.Type),
GenerateInParam("@starttime", SqlDbType.DateTime,8,fullCutPromotionInfo.StartTime),
GenerateInParam("@endtime", SqlDbType.DateTime,8,fullCutPromotionInfo.EndTime),
GenerateInParam("@userranklower", SqlDbType.SmallInt,2,fullCutPromotionInfo.UserRankLower),
GenerateInParam("@state", SqlDbType.TinyInt,1,fullCutPromotionInfo.State),
GenerateInParam("@name", SqlDbType.NVarChar,50,fullCutPromotionInfo.Name),
GenerateInParam("@limitmoney1", SqlDbType.Int, 4, fullCutPromotionInfo.LimitMoney1),
GenerateInParam("@cutmoney1", SqlDbType.Int, 4, fullCutPromotionInfo.CutMoney1),
GenerateInParam("@limitmoney2", SqlDbType.Int, 4, fullCutPromotionInfo.LimitMoney2),
GenerateInParam("@cutmoney2", SqlDbType.Int, 4, fullCutPromotionInfo.CutMoney2),
GenerateInParam("@limitmoney3", SqlDbType.Int, 4, fullCutPromotionInfo.LimitMoney3),
GenerateInParam("@cutmoney3", SqlDbType.Int, 4, fullCutPromotionInfo.CutMoney3)
};
string commandText = string.Format("INSERT INTO [{0}fullcutpromotions]([type],[starttime],[endtime],[userranklower],[state],[name],[limitmoney1],[cutmoney1],[limitmoney2],[cutmoney2],[limitmoney3],[cutmoney3]) VALUES(@type,@starttime,@endtime,@userranklower,@state,@name,@limitmoney1,@cutmoney1,@limitmoney2,@cutmoney2,@limitmoney3,@cutmoney3)",
RDBSHelper.RDBSTablePre);
RDBSHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
}
/// <summary>
/// 获得满减商品数量
/// </summary>
/// <param name="fullCutPromotionInfo">满减促销活动</param>
/// <param name="startPrice">开始价格</param>
/// <param name="endPrice">结束价格</param>
/// <returns></returns>
public int GetFullCutProductCount(FullCutPromotionInfo fullCutPromotionInfo, int startPrice, int endPrice)
{
StringBuilder commandText = new StringBuilder();
commandText.AppendFormat("SELECT COUNT([pid]) FROM [{0}products] WHERE", RDBSHelper.RDBSTablePre);
if (fullCutPromotionInfo.Type == 1)
commandText.AppendFormat(" [pid] IN (SELECT [pid] FROM [{0}fullcutproducts] WHERE [pmid]={1})", RDBSHelper.RDBSTablePre, fullCutPromotionInfo.PmId);
else if (fullCutPromotionInfo.Type == 2)
commandText.AppendFormat(" [pid] NOT IN (SELECT [pid] FROM [{0}fullcutproducts] WHERE [pmid]={1})", RDBSHelper.RDBSTablePre, fullCutPromotionInfo.PmId);
if (startPrice > 0)
commandText.AppendFormat(" AND [shopprice]>={0}", startPrice);
if (endPrice > 0)
commandText.AppendFormat(" AND [shopprice]<={0}", endPrice);
commandText.Append(" AND [state]=0");
return TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(CommandType.Text, commandText.ToString()));
}
/// <summary>
/// 生成商品的促销信息
/// </summary>
/// <param name="singlePromotionInfo">单品促销活动</param>
/// <param name="buySendPromotionList">买送促销活动列表</param>
/// <param name="fullSendPromotionInfo">满赠促销活动</param>
/// <param name="fullCutPromotionInfo">满减促销活动</param>
/// <returns></returns>
public static string GeneratePromotionMsg(SinglePromotionInfo singlePromotionInfo, List<BuySendPromotionInfo> buySendPromotionList, FullSendPromotionInfo fullSendPromotionInfo, FullCutPromotionInfo fullCutPromotionInfo)
{
StringBuilder promotionMsg = new StringBuilder();
//单品促销
if (singlePromotionInfo != null)
{
//折扣类别
switch (singlePromotionInfo.DiscountType)
{
case 0://折扣
promotionMsg.AppendFormat("折扣：{0}折<br/>", singlePromotionInfo.DiscountValue);
break;
case 1://直降
promotionMsg.AppendFormat("直降：{0}元<br/>", singlePromotionInfo.DiscountValue);
break;
case 2://折后价
promotionMsg.AppendFormat("折后价：{0}元<br/>", singlePromotionInfo.DiscountValue);
break;
}
//积分
if (singlePromotionInfo.PayCredits > 0)
promotionMsg.AppendFormat("赠送{0}：{1}<br/>", Credits.PayCreditName, singlePromotionInfo.PayCredits);
//优惠劵
if (singlePromotionInfo.CouponTypeId > 0)
{
CouponTypeInfo couponTypeInfo = Coupons.GetCouponTypeById(singlePromotionInfo.CouponTypeId);
if (couponTypeInfo != null)
promotionMsg.AppendFormat("赠送优惠劵：{0}<br/>", couponTypeInfo.Name);
}
}
//买送促销
if (buySendPromotionList != null && buySendPromotionList.Count > 0)
{
promotionMsg.Append("买送促销：");
foreach (BuySendPromotionInfo buySendPromotionInfo in buySendPromotionList)
promotionMsg.AppendFormat("买{0}送{1},", buySendPromotionInfo.BuyCount, buySendPromotionInfo.SendCount);
promotionMsg.Remove(promotionMsg.Length - 1, 1);
promotionMsg.Append("<br/>");
}
//满赠促销
if (fullSendPromotionInfo != null)
{
promotionMsg.Append("满赠促销：");
promotionMsg.AppendFormat("满{0}元加{1}元<br/>", fullSendPromotionInfo.LimitMoney, fullSendPromotionInfo.AddMoney);
}
//满减促销
if (fullCutPromotionInfo != null)
{
promotionMsg.Append("满减促销：");
promotionMsg.AppendFormat("满{0}元减{1}元,", fullCutPromotionInfo.LimitMoney1, fullCutPromotionInfo.CutMoney1);
if (fullCutPromotionInfo.LimitMoney2 > 0 && fullCutPromotionInfo.CutMoney2 > 0)
promotionMsg.AppendFormat("满{0}元减{1}元,", fullCutPromotionInfo.LimitMoney2, fullCutPromotionInfo.CutMoney2);
if (fullCutPromotionInfo.LimitMoney3 > 0 && fullCutPromotionInfo.CutMoney3 > 0)
promotionMsg.AppendFormat("满{0}元减{1}元,", fullCutPromotionInfo.LimitMoney3, fullCutPromotionInfo.CutMoney3);
promotionMsg.Remove(promotionMsg.Length - 1, 1);
promotionMsg.Append("<br/>");
}
return promotionMsg.Length > 0 ? promotionMsg.Remove(promotionMsg.Length - 5, 5).ToString() : "";
}
public ActionResult AddFullCutPromotion(FullCutPromotionModel model)
{
if (ModelState.IsValid)
{
FullCutPromotionInfo fullCutPromotionInfo = new FullCutPromotionInfo()
{
Name = model.PromotionName,
Type = model.Type,
StartTime = model.StartTime,
EndTime = model.EndTime,
UserRankLower = model.UserRankLower,
State = model.State,
LimitMoney1 = model.LimitMoney1,
CutMoney1 = model.CutMoney1,
LimitMoney2 = model.LimitMoney2,
CutMoney2 = model.CutMoney2,
LimitMoney3 = model.LimitMoney3,
CutMoney3 = model.CutMoney3
};
AdminPromotions.CreateFullCutPromotion(fullCutPromotionInfo);
AddAdminOperateLog("添加满减促销活动", "添加满减促销活动,满减促销活动为:" + model.PromotionName);
return PromptView("满减促销活动添加成功");
}
LoadFullCutPromotion();
return View(model);
}
/// <summary>
/// 获得满减商品列表
/// </summary>
/// <param name="pageSize">每页数</param>
/// <param name="pageNumber">当前页数</param>
/// <param name="fullCutPromotionInfo">满减促销活动</param>
/// <param name="startPrice">开始价格</param>
/// <param name="endPrice">结束价格</param>
/// <param name="sortColumn">排序列</param>
/// <param name="sortDirection">排序方向</param>
/// <returns></returns>
public static List<PartProductInfo> GetFullCutProductList(int pageSize, int pageNumber, FullCutPromotionInfo fullCutPromotionInfo, int startPrice, int endPrice, int sortColumn, int sortDirection)
{
return BrnShop.Data.Promotions.GetFullCutProductList(pageSize, pageNumber, fullCutPromotionInfo, startPrice, endPrice, sortColumn, sortDirection);
}
/// <summary>
/// 获得满减商品数量
/// </summary>
/// <param name="fullCutPromotionInfo">满减促销活动</param>
/// <param name="startPrice">开始价格</param>
/// <param name="endPrice">结束价格</param>
/// <returns></returns>
public static int GetFullCutProductCount(FullCutPromotionInfo fullCutPromotionInfo, int startPrice, int endPrice)
{
return BrnShop.Data.Promotions.GetFullCutProductCount(fullCutPromotionInfo, startPrice, endPrice);
}
/// <summary>
/// 创建满减促销活动
/// </summary>
public static void CreateFullCutPromotion(FullCutPromotionInfo fullCutPromotionInfo)
{
BrnShop.Core.BSPData.RDBS.CreateFullCutPromotion(fullCutPromotionInfo);
if (_promotionnosql != null)
_promotionnosql.ClearAllFullCutPromotion();
}
/// <summary>
/// 从IDataReader创建FullCutPromotionInfo
/// </summary>
public static FullCutPromotionInfo BuildFullCutPromotionFromReader(IDataReader reader)
{
FullCutPromotionInfo fullCutPromotionInfo = new FullCutPromotionInfo();
fullCutPromotionInfo.PmId = TypeHelper.ObjectToInt(reader["pmid"]);
fullCutPromotionInfo.Type = TypeHelper.ObjectToInt(reader["type"]);
fullCutPromotionInfo.StartTime = TypeHelper.ObjectToDateTime(reader["starttime"]);
fullCutPromotionInfo.EndTime = TypeHelper.ObjectToDateTime(reader["endtime"]);
fullCutPromotionInfo.UserRankLower = TypeHelper.ObjectToInt(reader["userranklower"]);
fullCutPromotionInfo.State = TypeHelper.ObjectToInt(reader["state"]);
fullCutPromotionInfo.Name = reader["name"].ToString();
fullCutPromotionInfo.LimitMoney1 = TypeHelper.ObjectToInt(reader["limitmoney1"]);
fullCutPromotionInfo.CutMoney1 = TypeHelper.ObjectToInt(reader["cutmoney1"]);
fullCutPromotionInfo.LimitMoney2 = TypeHelper.ObjectToInt(reader["limitmoney2"]);
fullCutPromotionInfo.CutMoney2 = TypeHelper.ObjectToInt(reader["cutmoney2"]);
fullCutPromotionInfo.LimitMoney3 = TypeHelper.ObjectToInt(reader["limitmoney3"]);
fullCutPromotionInfo.CutMoney3 = TypeHelper.ObjectToInt(reader["cutmoney3"]);
return fullCutPromotionInfo;
}
/// <summary>
/// 获得满减商品列表
/// </summary>
/// <param name="pageSize">每页数</param>
/// <param name="pageNumber">当前页数</param>
/// <param name="fullCutPromotionInfo">满减促销活动</param>
/// <param name="startPrice">开始价格</param>
/// <param name="endPrice">结束价格</param>
/// <param name="sortColumn">排序列</param>
/// <param name="sortDirection">排序方向</param>
/// <returns></returns>
public static List<PartProductInfo> GetFullCutProductList(int pageSize, int pageNumber, FullCutPromotionInfo fullCutPromotionInfo, int startPrice, int endPrice, int sortColumn, int sortDirection)
{
List<PartProductInfo> partProductList = new List<PartProductInfo>();
IDataReader reader = BrnShop.Core.BSPData.RDBS.GetFullCutProductList(pageSize, pageNumber, fullCutPromotionInfo, startPrice, endPrice, sortColumn, sortDirection);
while (reader.Read())
{
PartProductInfo partProductInfo = Products.BuildPartProductFromReader(reader);
partProductList.Add(partProductInfo);
}
reader.Close();
return partProductList;
}