public MemWZShopCartEntity GetCartCookie(int memberid)
{
string sql = @"SELECT top 1 [Id],[MemId],[BuyDate],[EndDate],[AddDate],[CookieValue],[CookieValueXuQiu],[CookieId]
FROM
dbo.[MemWZShopCart] WITH(NOLOCK)
WHERE [MemId]=@MemId order by id desc" ;
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@MemId", DbType.Int32, memberid);
MemWZShopCartEntity entity = new MemWZShopCartEntity();
using (IDataReader reader = db.ExecuteReader(cmd))
{
if (reader.Read())
{
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.MemId = StringUtils.GetDbInt(reader["MemId"]);
entity.BuyDate = StringUtils.GetDbDateTime(reader["BuyDate"]);
entity.EndDate = StringUtils.GetDbDateTime(reader["EndDate"]);
entity.AddDate = StringUtils.GetDbDateTime(reader["AddDate"]);
entity.CookieValue = StringUtils.GetDbString(reader["CookieValue"]);
entity.CookieValueXuQiu = StringUtils.GetDbString(reader["CookieValueXuQiu"]);
entity.CookieId = StringUtils.GetDbString(reader["CookieId"]);
}
}
return(entity);
}
/// <summary>
/// 插入一条记录到表WZ_ShopCart，如果表中存在自增字段，则返回值为新记录的自增字段值，否则返回0。
/// 该方法提供给界面等UI层调用
/// </summary>
/// <param name="wZShopCart">要添加的WZ_ShopCart数据实体对象</param>
public int AddWZShopCartXuQiu(MemWZShopCartEntity wZShopCart)
{
if (wZShopCart.Id > 0 || WZShopCartBLL.Instance.IsExist(wZShopCart))
{
return(UpdateWZShopCartXuQiu(wZShopCart));
}
else
{
return(MemWZShopCartDA.Instance.AddWZShopCart(wZShopCart));
}
}
public string GetXuQiuCookie(int memberid)
{
MemWZShopCartEntity _entity = MemWZShopCartDA.Instance.GetCartCookie(memberid);
if (_entity != null && _entity.CookieValueXuQiu != null && _entity.CookieValueXuQiu != "")
{
return(_entity.CookieValueXuQiu);
}
else
{
return("");
}
}
public int UpdateWZShopCartXuQiu(MemWZShopCartEntity entity)
{
string sql = @" UPDATE dbo.[MemWZShopCart] SET [BuyDate]=@BuyDate,[EndDate]=@EndDate,[CookieValueXuQiu]=@CookieValueXuQiu
WHERE [MemId]=@MemId";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@MemId", DbType.Int32, entity.MemId);
db.AddInParameter(cmd, "@BuyDate", DbType.DateTime, entity.BuyDate);
db.AddInParameter(cmd, "@EndDate", DbType.DateTime, entity.EndDate);
db.AddInParameter(cmd, "@CookieValueXuQiu", DbType.String, entity.CookieValueXuQiu);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 判断当前节点是否已存在相同的
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public int ExistNum(MemWZShopCartEntity entity)
{
///id=0,判断总数，ID>0判断除自己之外的总数
string sql = @" Select count(1) from dbo.[MemWZShopCart] WITH(NOLOCK) WHERE MemId=@MemId ";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@MemId", DbType.Int32, entity.MemId);
object identity = db.ExecuteScalar(cmd);
if (identity == null || identity == DBNull.Value)
{
return(0);
}
return(Convert.ToInt32(identity));
}
/// <summary>
/// 读取记录列表。
/// </summary>
/// <param name="db">数据库操作对象</param>
/// <param name="columns">需要返回的列，不提供任何列名时默认将返回所有列</param>
public IList <MemWZShopCartEntity> GetWZShopCartList(int pagesize, int pageindex, ref int recordCount)
{
string sql = @"SELECT [Id],[MemId],[BuyDate],[EndDate],[AddDate],[CookieValue],[CookieId]
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Id desc) AS ROWNUMBER,
[Id],[MemId],[BuyDate],[EndDate],[AddDate],[CookieValue],[CookieId] from dbo.[MemWZShopCart] WITH(NOLOCK)
WHERE 1=1 ) as temp
where rownumber BETWEEN ((@PageIndex - 1) * @PageSize + 1) AND @PageIndex * @PageSize" ;
string sql2 = @"Select count(1) from dbo.[MemWZShopCart] with (nolock) ";
IList <MemWZShopCartEntity> entityList = new List <MemWZShopCartEntity>();
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@PageIndex", DbType.Int32, pageindex);
db.AddInParameter(cmd, "@PageSize", DbType.Int32, pagesize);
using (IDataReader reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
MemWZShopCartEntity entity = new MemWZShopCartEntity();
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.MemId = StringUtils.GetDbInt(reader["MemId"]);
entity.BuyDate = StringUtils.GetDbDateTime(reader["BuyDate"]);
entity.EndDate = StringUtils.GetDbDateTime(reader["EndDate"]);
entity.AddDate = StringUtils.GetDbDateTime(reader["AddDate"]);
entity.CookieValue = StringUtils.GetDbString(reader["CookieValue"]);
entity.CookieId = StringUtils.GetDbString(reader["CookieId"]);
entityList.Add(entity);
}
}
cmd = db.GetSqlStringCommand(sql2);
using (IDataReader reader = db.ExecuteReader(cmd))
{
if (reader.Read())
{
recordCount = StringUtils.GetDbInt(reader[0]);
}
else
{
recordCount = 0;
}
}
return(entityList);
}
/// <summary>
/// 生成购物车cookie
/// </summary>
/// <param name="shoppingCart"></param>
public static void WriteShoppingXuQiuCookie(VWShoppingCartInfo shoppingcart)
{
if (shoppingcart == null)
{
return;
}
string jsonRst = SuperMarket.Core.Json.JsonJC.ObjectToJson(shoppingcart.CartItems);
//保存购物车cookie
System.Web.HttpCookie cookie = System.Web.HttpContext.Current.Request.Cookies[SysCookieName.ShoppingXuQiu_Data];
if (cookie == null)
{
cookie = new System.Web.HttpCookie(SysCookieName.ShoppingXuQiu_Data);
}
cookie.Value = HttpUtility.UrlEncode(jsonRst, Encoding.GetEncoding("UTF-8"));
cookie.Expires = DateTime.Now.AddDays(15);
System.Web.HttpContext.Current.Response.Cookies.Add(cookie);
//修改购物车数量cookie
SetShoppingXuQiuCount(shoppingcart.CartCount);
//如果登录保存COOKIE至数据库
if (SuperMarket.Core.ConfigCore.Instance.ConfigCommonEntity.XuQiuCookie == 1)
{
if (shoppingcart != null)
{
MemberLoginEntity member = CookieBLL.GetLoginCookie();
if (member != null && member.MemId > 0 && jsonRst != "")
{
MemWZShopCartEntity model = new MemWZShopCartEntity();
model.AddDate = DateTime.Now;
model.BuyDate = DateTime.Now.Date;
model.EndDate = DateTime.Now.Date.AddDays(15);
model.MemId = member.MemId;
model.CookieValueXuQiu = jsonRst;
WZShopCartBLL.Instance.AddWZShopCartXuQiu(model);
}
}
}
}
/// <summary>
/// 插入一条记录到表WZShopCart，如果表中存在自增字段，则返回值为新记录的自增字段值，否则返回0
/// </summary>
/// <param name="db">数据库操作对象</param>
/// <param name="wZShopCart">待插入的实体对象</param>
public int AddWZShopCart(MemWZShopCartEntity entity)
{
string sql = @"insert into MemWZShopCart( [MemId],[BuyDate],[EndDate],[AddDate],[CookieValue],[CookieValueXuQiu],[CookieId])VALUES
( @MemId,@BuyDate,@EndDate,@AddDate,@CookieValue,@CookieValueXuQiu,@CookieId);
SELECT SCOPE_IDENTITY();" ;
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@MemId", DbType.Int32, entity.MemId);
db.AddInParameter(cmd, "@BuyDate", DbType.DateTime, entity.BuyDate);
db.AddInParameter(cmd, "@EndDate", DbType.DateTime, entity.EndDate);
db.AddInParameter(cmd, "@AddDate", DbType.DateTime, entity.AddDate);
db.AddInParameter(cmd, "@CookieValue", DbType.String, entity.CookieValue);
db.AddInParameter(cmd, "@CookieValueXuQiu", DbType.String, entity.CookieValueXuQiu);
db.AddInParameter(cmd, "@CookieId", DbType.String, entity.CookieId);
object identity = db.ExecuteScalar(cmd);
if (identity == null || identity == DBNull.Value)
{
return(0);
}
return(Convert.ToInt32(identity));
}
/// <summary>
/// 读取记录列表。
/// </summary>
/// <param name="db">数据库操作对象</param>
/// <param name="columns">需要返回的列，不提供任何列名时默认将返回所有列</param>
public IList <MemWZShopCartEntity> GetWZShopCartAll()
{
string sql = @"SELECT [Id],[MemId],[BuyDate],[EndDate],[AddDate],[CookieValue],[CookieId] from dbo.[MemWZShopCart] WITH(NOLOCK) ";
IList <MemWZShopCartEntity> entityList = new List <MemWZShopCartEntity>();
DbCommand cmd = db.GetSqlStringCommand(sql);
using (IDataReader reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
MemWZShopCartEntity entity = new MemWZShopCartEntity();
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.MemId = StringUtils.GetDbInt(reader["MemId"]);
entity.BuyDate = StringUtils.GetDbDateTime(reader["BuyDate"]);
entity.EndDate = StringUtils.GetDbDateTime(reader["EndDate"]);
entity.AddDate = StringUtils.GetDbDateTime(reader["AddDate"]);
entity.CookieValue = StringUtils.GetDbString(reader["CookieValue"]);
entity.CookieId = StringUtils.GetDbString(reader["CookieId"]);
entityList.Add(entity);
}
}
return(entityList);
}
public int UpdateWZShopCartXuQiu(MemWZShopCartEntity wZShopCart)
{
return(MemWZShopCartDA.Instance.UpdateWZShopCartXuQiu(wZShopCart));
}
/// <summary>
/// 判断对象是否存在
/// </summary>
/// <param name="dicEnum"></param>
/// <returns></returns>
public bool IsExist(MemWZShopCartEntity wZShopCart)
{
return(MemWZShopCartDA.Instance.ExistNum(wZShopCart) > 0);
}
