/// <summary>
/// 插入一条记录到表ProductSpecial，如果表中存在自增字段，则返回值为新记录的自增字段值，否则返回0
/// </summary>
/// <param name="db">数据库操作对象</param>
/// <param name="productSpecial">待插入的实体对象</param>
public int AddProductSpecial(ProductSpecialEntity entity)
{
string sql = @"insert into ProductSpecial( [ShortName],[FullName],[Remark],[EnPicUrl],[EnPicSuffix],[ExPicUrl],[ExPicSuffix],[Sort],[BeginTime],[EndTime],[IsActive],[SystemType],[SpecialType],RedirectUrl)VALUES
( @ShortName,@FullName,@Remark,@EnPicUrl,@EnPicSuffix,@ExPicUrl,@ExPicSuffix,@Sort,@BeginTime,@EndTime,@IsActive,@SystemType,@SpecialType,@RedirectUrl);
SELECT SCOPE_IDENTITY();" ;
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@ShortName", DbType.String, entity.ShortName);
db.AddInParameter(cmd, "@FullName", DbType.String, entity.FullName);
db.AddInParameter(cmd, "@Remark", DbType.String, entity.Remark);
db.AddInParameter(cmd, "@EnPicUrl", DbType.String, entity.EnPicUrl);
db.AddInParameter(cmd, "@EnPicSuffix", DbType.String, entity.EnPicSuffix);
db.AddInParameter(cmd, "@ExPicUrl", DbType.String, entity.ExPicUrl);
db.AddInParameter(cmd, "@ExPicSuffix", DbType.String, entity.ExPicSuffix);
db.AddInParameter(cmd, "@Sort", DbType.Int32, entity.Sort);
db.AddInParameter(cmd, "@BeginTime", DbType.DateTime, entity.BeginTime);
db.AddInParameter(cmd, "@EndTime", DbType.DateTime, entity.EndTime);
db.AddInParameter(cmd, "@IsActive", DbType.Int32, entity.IsActive);
db.AddInParameter(cmd, "@SystemType", DbType.Int32, entity.SystemType);
db.AddInParameter(cmd, "@SpecialType", DbType.Int32, entity.SpecialType);
db.AddInParameter(cmd, "@RedirectUrl", DbType.String, entity.RedirectUrl);
object identity = db.ExecuteScalar(cmd);
if (identity == null || identity == DBNull.Value)
{
return(0);
}
return(Convert.ToInt32(identity));
}
/// <summary>
/// 根据主键值更新记录的全部字段（注意：该方法不会对自增字段、timestamp类型字段以及主键字段更新！如果要更新主键字段，请使用Update方法）。
/// 如果数据库有数据被更新了则返回True，否则返回False
/// </summary>
/// <param name="db">数据库操作对象</param>
/// <param name="productSpecial">待更新的实体对象</param>
public int UpdateProductSpecial(ProductSpecialEntity entity)
{
string sql = @" UPDATE dbo.[ProductSpecial] SET
[ShortName]=@ShortName,[FullName]=@FullName,[Remark]=@Remark,[EnPicUrl]=@EnPicUrl,[EnPicSuffix]=@EnPicSuffix,[ExPicUrl]=@ExPicUrl,[ExPicSuffix]=@ExPicSuffix,[Sort]=@Sort,[BeginTime]=@BeginTime,[EndTime]=@EndTime,[IsActive]=@IsActive,[SystemType]=@SystemType,[SpecialType]=@SpecialType,RedirectUrl=@RedirectUrl
WHERE [Id]=@id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@Id", DbType.Int32, entity.Id);
db.AddInParameter(cmd, "@ShortName", DbType.String, entity.ShortName);
db.AddInParameter(cmd, "@FullName", DbType.String, entity.FullName);
db.AddInParameter(cmd, "@Remark", DbType.String, entity.Remark);
db.AddInParameter(cmd, "@EnPicUrl", DbType.String, entity.EnPicUrl);
db.AddInParameter(cmd, "@EnPicSuffix", DbType.String, entity.EnPicSuffix);
db.AddInParameter(cmd, "@ExPicUrl", DbType.String, entity.ExPicUrl);
db.AddInParameter(cmd, "@ExPicSuffix", DbType.String, entity.ExPicSuffix);
db.AddInParameter(cmd, "@Sort", DbType.Int32, entity.Sort);
db.AddInParameter(cmd, "@BeginTime", DbType.DateTime, entity.BeginTime);
db.AddInParameter(cmd, "@EndTime", DbType.DateTime, entity.EndTime);
db.AddInParameter(cmd, "@IsActive", DbType.Int32, entity.IsActive);
db.AddInParameter(cmd, "@SystemType", DbType.Int32, entity.SystemType);
db.AddInParameter(cmd, "@SpecialType", DbType.Int32, entity.SpecialType);
db.AddInParameter(cmd, "@RedirectUrl", DbType.String, entity.RedirectUrl);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 读取记录列表。
/// </summary>
/// <param name="db">数据库操作对象</param>
/// <param name="columns">需要返回的列，不提供任何列名时默认将返回所有列</param>
public IList <ProductSpecialEntity> GetProductSpecialAll()
{
string sql = @"SELECT [Id],[ShortName],[FullName],[Remark],[EnPicUrl],[EnPicSuffix],[ExPicUrl],[ExPicSuffix],[Sort],[BeginTime],[EndTime],[IsActive],[SystemType],[SpecialType],RedirectUrl from dbo.[ProductSpecial] WITH(NOLOCK) ";
IList <ProductSpecialEntity> entityList = new List <ProductSpecialEntity>();
DbCommand cmd = db.GetSqlStringCommand(sql);
using (IDataReader reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
ProductSpecialEntity entity = new ProductSpecialEntity();
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.ShortName = StringUtils.GetDbString(reader["ShortName"]);
entity.FullName = StringUtils.GetDbString(reader["FullName"]);
entity.Remark = StringUtils.GetDbString(reader["Remark"]);
entity.EnPicUrl = StringUtils.GetDbString(reader["EnPicUrl"]);
entity.EnPicSuffix = StringUtils.GetDbString(reader["EnPicSuffix"]);
entity.ExPicUrl = StringUtils.GetDbString(reader["ExPicUrl"]);
entity.ExPicSuffix = StringUtils.GetDbString(reader["ExPicSuffix"]);
entity.Sort = StringUtils.GetDbInt(reader["Sort"]);
entity.BeginTime = StringUtils.GetDbDateTime(reader["BeginTime"]);
entity.EndTime = StringUtils.GetDbDateTime(reader["EndTime"]);
entity.IsActive = StringUtils.GetDbInt(reader["IsActive"]);
entity.SystemType = StringUtils.GetDbInt(reader["SystemType"]);
entity.SpecialType = StringUtils.GetDbInt(reader["SpecialType"]);
entity.RedirectUrl = StringUtils.GetDbString(reader["RedirectUrl"]);
entityList.Add(entity);
}
}
return(entityList);
}
/// <summary>
/// 判断当前节点是否已存在相同的
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public int ExistNum(ProductSpecialEntity entity)
{
///id=0,判断总数，ID>0判断除自己之外的总数
string sql = @"Select count(1) from dbo.[ProductSpecial] WITH(NOLOCK) ";
string where = "where ";
if (entity.Id == 0)
{
where = where + " (ShortName=@ShortName) ";
where = where + " (FullName=@FullName) ";
}
else
{
where = where + " id<>@Id and (ShortName=@ShortName) ";
where = where + " id<>@Id and (FullName=@FullName) ";
}
sql = sql + where;
DbCommand cmd = db.GetSqlStringCommand(sql);
if (entity.Id > 0)
{
db.AddInParameter(cmd, "@Id", DbType.Int32, entity.Id);
}
db.AddInParameter(cmd, "@ShortName", DbType.String, entity.ShortName);
db.AddInParameter(cmd, "@FullName", DbType.String, entity.FullName);
object identity = db.ExecuteScalar(cmd);
if (identity == null || identity == DBNull.Value)
{
return(0);
}
return(Convert.ToInt32(identity));
}
public ProductSpecialEntity GetProductSpecialByCode(string code)
{
string sql = @"SELECT [Id],[ShortName],[FullName],[Remark],[EnPicUrl],[EnPicSuffix],[ExPicUrl],[ExPicSuffix],[Sort],[BeginTime],[EndTime],[IsActive],[SystemType],[SpecialType],RedirectUrl
FROM
dbo.[ProductSpecial] WITH(NOLOCK)
WHERE [Code]=@Code " ;
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@Code", DbType.String, code);
ProductSpecialEntity entity = new ProductSpecialEntity();
using (IDataReader reader = db.ExecuteReader(cmd))
{
if (reader.Read())
{
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.ShortName = StringUtils.GetDbString(reader["ShortName"]);
entity.FullName = StringUtils.GetDbString(reader["FullName"]);
entity.Remark = StringUtils.GetDbString(reader["Remark"]);
entity.EnPicUrl = StringUtils.GetDbString(reader["EnPicUrl"]);
entity.EnPicSuffix = StringUtils.GetDbString(reader["EnPicSuffix"]);
entity.ExPicUrl = StringUtils.GetDbString(reader["ExPicUrl"]);
entity.ExPicSuffix = StringUtils.GetDbString(reader["ExPicSuffix"]);
entity.Sort = StringUtils.GetDbInt(reader["Sort"]);
entity.BeginTime = StringUtils.GetDbDateTime(reader["BeginTime"]);
entity.EndTime = StringUtils.GetDbDateTime(reader["EndTime"]);
entity.IsActive = StringUtils.GetDbInt(reader["IsActive"]);
entity.SystemType = StringUtils.GetDbInt(reader["SystemType"]);
entity.SpecialType = StringUtils.GetDbInt(reader["SpecialType"]);
entity.RedirectUrl = StringUtils.GetDbString(reader["RedirectUrl"]);
}
}
return(entity);
}
/// <summary>
/// 读取记录列表。
/// </summary>
/// <param name="db">数据库操作对象</param>
/// <param name="columns">需要返回的列，不提供任何列名时默认将返回所有列</param>
public IList <ProductSpecialEntity> GetProductSpecialList(int pagesize, int pageindex, ref int recordCount)
{
string sql = @"SELECT [Id],[ShortName],[FullName],[Remark],[EnPicUrl],[EnPicSuffix],[ExPicUrl],[ExPicSuffix],[Sort],[BeginTime],[EndTime],[IsActive],[SystemType],[SpecialType],RedirectUrl
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Id desc) AS ROWNUMBER,
[Id],[ShortName],[FullName],[Remark],[EnPicUrl],[EnPicSuffix],[ExPicUrl],[ExPicSuffix],[Sort],[BeginTime],[EndTime],[IsActive],[SystemType],[SpecialType],RedirectUrl from dbo.[ProductSpecial] WITH(NOLOCK)
WHERE 1=1 ) as temp
where rownumber BETWEEN ((@PageIndex - 1) * @PageSize + 1) AND @PageIndex * @PageSize" ;
string sql2 = @"Select count(1) from dbo.[ProductSpecial] with (nolock) ";
IList <ProductSpecialEntity> entityList = new List <ProductSpecialEntity>();
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())
{
ProductSpecialEntity entity = new ProductSpecialEntity();
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.ShortName = StringUtils.GetDbString(reader["ShortName"]);
entity.FullName = StringUtils.GetDbString(reader["FullName"]);
entity.Remark = StringUtils.GetDbString(reader["Remark"]);
entity.EnPicUrl = StringUtils.GetDbString(reader["EnPicUrl"]);
entity.EnPicSuffix = StringUtils.GetDbString(reader["EnPicSuffix"]);
entity.ExPicUrl = StringUtils.GetDbString(reader["ExPicUrl"]);
entity.ExPicSuffix = StringUtils.GetDbString(reader["ExPicSuffix"]);
entity.Sort = StringUtils.GetDbInt(reader["Sort"]);
entity.BeginTime = StringUtils.GetDbDateTime(reader["BeginTime"]);
entity.EndTime = StringUtils.GetDbDateTime(reader["EndTime"]);
entity.IsActive = StringUtils.GetDbInt(reader["IsActive"]);
entity.SystemType = StringUtils.GetDbInt(reader["SystemType"]);
entity.SpecialType = StringUtils.GetDbInt(reader["SpecialType"]);
entity.RedirectUrl = StringUtils.GetDbString(reader["RedirectUrl"]);
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);
}
public ActionResult Special()
{
string title = "小店专区-";
int _specialid = QueryString.IntSafeQ("sid");
string _specialcode = QueryString.SafeQ("code");
int pageindex = QueryString.IntSafeQ("pageindex");
if (pageindex == 0)
{
pageindex = 1;
}
int _pagesize = CommonKey.PageSizeProductMobile;
int isactive = 1;
int _recordCount = 0;
ProductSpecialEntity sentity = new ProductSpecialEntity();
if (_specialid > 0)
{
sentity = ProductSpecialBLL.Instance.GetProductSpecial(_specialid);
}
else if (!string.IsNullOrEmpty(_specialcode))
{
sentity = ProductSpecialBLL.Instance.GetProductSpecialByCode(_specialcode);
}
title = sentity.FullName;
IList <VWProductSpecialDetailsEntity> sdlist = ProductSpecialDetailsBLL.Instance.GetProductSpecialDetailsList(pageindex, _pagesize, ref _recordCount, sentity.Id, isactive);
MemberLoginEntity member = CookieBLL.GetLoginCookie();
if (member != null && member.MemId > 0)
{
ViewBag.MemId = member.MemId;
ViewBag.MemStatus = member.Status;
if (sdlist != null && sdlist.Count > 0)
{
foreach (VWProductSpecialDetailsEntity entity in sdlist)
{
if (entity.ProductDetail != null && entity.ProductDetail.ProductId > 0)
{
entity.Price = Calculate.GetPrice(member.Status, member.IsStore, member.StoreType, member.MemGrade, entity.ProductDetail.TradePrice, entity.ProductDetail.Price, entity.ProductDetail.IsBP, entity.ProductDetail.DealerPrice);
}
}
}
}
ViewBag.ProductSpecialEntity = sentity;
ViewBag.SpecialDetailList = sdlist;
string url = "/Product/Special?sid=" + _specialid + "&code=" + _specialcode;
string pagehtml = HTMLPage.SetOrderListPage(_recordCount, _pagesize, pageindex, url);
ViewBag.PageHtml = pagehtml;
ViewBag.Title = title;
return(View());
}
/// <summary>
/// 专区产品显示
/// </summary>
/// <returns></returns>
public ActionResult ProductS()
{
int _specialid = QueryString.IntSafeQ("sid");
string _specialcode = QueryString.SafeQ("code");
int pageindex = 1;
int _pagesize = CommonKey.PageSizeProductMobile;
int isactive = 1;
int _recordCount = 0;
ProductSpecialEntity sentity = new ProductSpecialEntity();
if (_specialid > 0)
{
sentity = ProductSpecialBLL.Instance.GetProductSpecial(_specialid);
}
else if (!string.IsNullOrEmpty(_specialcode))
{
sentity = ProductSpecialBLL.Instance.GetProductSpecialByCode(_specialcode);
}
IList <VWProductSpecialDetailsEntity> sdlist = ProductSpecialDetailsBLL.Instance.GetProductSpecialDetailsList(pageindex, _pagesize, ref _recordCount, sentity.Id, isactive);
MemberLoginEntity member = CookieBLL.GetLoginCookie();
if (member != null && member.MemId > 0)
{
ViewBag.MemId = member.MemId;
ViewBag.MemStatus = member.Status;
if (sdlist != null && sdlist.Count > 0)
{
foreach (VWProductSpecialDetailsEntity entity in sdlist)
{
if (entity.ProductDetail != null && entity.ProductDetail.ProductId > 0)
{
entity.Price = Calculate.GetPrice(member.Status, member.IsStore, member.StoreType, member.MemGrade, entity.ProductDetail.TradePrice, entity.ProductDetail.Price, entity.ProductDetail.IsBP, entity.ProductDetail.DealerPrice);
}
}
}
}
ViewBag.ProductSpecialEntity = sentity;
ViewBag.SpecialDetailList = sdlist;
int maxpage = _recordCount / _pagesize;
if (_recordCount % _pagesize > 0)
{
maxpage = maxpage + 1;
}
ViewBag.MaxPageIndex = maxpage;
return(View());
}
/// <summary>
/// 插入一条记录到表ProductSpecial，如果表中存在自增字段，则返回值为新记录的自增字段值，否则返回0。
/// 该方法提供给界面等UI层调用
/// </summary>
/// <param name="productSpecial">要添加的ProductSpecial数据实体对象</param>
public int AddProductSpecial(ProductSpecialEntity productSpecial)
{
if (productSpecial.Id > 0)
{
return(UpdateProductSpecial(productSpecial));
}
else if (string.IsNullOrEmpty(productSpecial.ShortName))
{
return((int)CommonStatus.ADD_Fail_Empty);
}
else if (string.IsNullOrEmpty(productSpecial.FullName))
{
return((int)CommonStatus.ADD_Fail_Empty);
}
else if (ProductSpecialBLL.Instance.IsExist(productSpecial))
{
return((int)CommonStatus.ADD_Fail_Exist);
}
else
{
return(ProductSpecialDA.Instance.AddProductSpecial(productSpecial));
}
}
/// <summary>
/// 更新一条ProductSpecial记录。
/// 该方法提供给界面等UI层调用
/// </summary>
/// <param name="productSpecial">待更新的实体对象</param>
/// <param name="columns">要更新的列名，不提供任何列名时默认将更新主键之外的所有列</param>
public int UpdateProductSpecial(ProductSpecialEntity productSpecial)
{
return(ProductSpecialDA.Instance.UpdateProductSpecial(productSpecial));
}
/// <summary>
/// 判断对象是否存在
/// </summary>
/// <param name="dicEnum"></param>
/// <returns></returns>
public bool IsExist(ProductSpecialEntity productSpecial)
{
return(ProductSpecialDA.Instance.ExistNum(productSpecial) > 0);
}