/// <summary>
/// 插入一条记录到表MemPostAddress，如果表中存在自增字段，则返回值为新记录的自增字段值，否则返回0
/// </summary>
/// <param name="db">数据库操作对象</param>
/// <param name="MemPostAddress">待插入的实体对象</param>
public int AddMemPostAddress(MemPostAddressEntity entity)
{
string sql = @"insert into MemPostAddress( [MemId],[AccepterName],[CtType],[CountryCode],[CountryName],[ProvinceId],[CityId],[District],[Address],[Email],[Telephone],[MobilePhone],[IsDefault],[Sort])VALUES
( @MemId,@AccepterName,@CtType,@CountryCode,@CountryName,@ProvinceId,@CityId,@District,@Address,@Email,@Telephone,@MobilePhone,@IsDefault,@Sort);
SELECT SCOPE_IDENTITY();" ;
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@MemId", DbType.Int32, entity.MemId);
db.AddInParameter(cmd, "@AccepterName", DbType.String, entity.AccepterName);
db.AddInParameter(cmd, "@CtType", DbType.Int32, entity.CtType);
db.AddInParameter(cmd, "@CountryCode", DbType.String, entity.CountryCode);
db.AddInParameter(cmd, "@CountryName", DbType.String, entity.CountryName);
db.AddInParameter(cmd, "@ProvinceId", DbType.Int32, entity.ProvinceId);
db.AddInParameter(cmd, "@CityId", DbType.Int32, entity.CityId);
db.AddInParameter(cmd, "@District", DbType.String, entity.District);
db.AddInParameter(cmd, "@Address", DbType.String, entity.Address);
db.AddInParameter(cmd, "@Email", DbType.String, entity.Email);
db.AddInParameter(cmd, "@Telephone", DbType.String, entity.Telephone);
db.AddInParameter(cmd, "@MobilePhone", DbType.String, entity.MobilePhone);
db.AddInParameter(cmd, "@IsDefault", DbType.Int32, entity.IsDefault);
db.AddInParameter(cmd, "@Sort", DbType.Int32, entity.Sort);
object identity = db.ExecuteScalar(cmd);
if (identity == null || identity == DBNull.Value)
{
return(0);
}
return(Convert.ToInt32(identity));
}
public IList <MemPostAddressEntity> GetPostListByMemId(int memid)
{
string sql = @"SELECT [Id],[MemId],[AccepterName],[CtType],[CountryCode],[CountryName],[ProvinceId],[CityId],[District],[Address],[Email],[Telephone],[MobilePhone],[IsDefault],[Sort] from dbo.[MemPostAddress] WITH(NOLOCK) WHERE [MemId]=@memid";
IList <MemPostAddressEntity> entityList = new List <MemPostAddressEntity>();
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@MemId", DbType.Int32, memid);
using (IDataReader reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
MemPostAddressEntity entity = new MemPostAddressEntity();
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.MemId = StringUtils.GetDbInt(reader["MemId"]);
entity.AccepterName = StringUtils.GetDbString(reader["AccepterName"]);
entity.CtType = StringUtils.GetDbInt(reader["CtType"]);
entity.CountryCode = StringUtils.GetDbString(reader["CountryCode"]);
entity.CountryName = StringUtils.GetDbString(reader["CountryName"]);
entity.ProvinceId = StringUtils.GetDbInt(reader["ProvinceId"]);
entity.CityId = StringUtils.GetDbInt(reader["CityId"]);
entity.District = StringUtils.GetDbString(reader["District"]);
entity.Address = StringUtils.GetDbString(reader["Address"]);
entity.Email = StringUtils.GetDbString(reader["Email"]);
entity.Telephone = StringUtils.GetDbString(reader["Telephone"]);
entity.MobilePhone = StringUtils.GetDbString(reader["MobilePhone"]);
entity.IsDefault = StringUtils.GetDbInt(reader["IsDefault"]);
entity.Sort = StringUtils.GetDbInt(reader["Sort"]);
entityList.Add(entity);
}
}
return(entityList);
}
/// <summary>
/// 判断当前节点是否已存在相同的
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public int ExistNum(MemPostAddressEntity entity)
{
///id=0,判断总数，ID>0判断除自己之外的总数
string sql = @"Select count(1) from dbo.[MemPostAddress] WITH(NOLOCK) ";
string where = "where ";
if (entity.Id == 0)
{
where = where + " (AccepterName=@AccepterName) ";
where = where + " (CountryName=@CountryName) ";
}
else
{
where = where + " id<>@Id and (AccepterName=@AccepterName) ";
where = where + " id<>@Id and (CountryName=@CountryName) ";
}
sql = sql + where;
DbCommand cmd = db.GetSqlStringCommand(sql);
if (entity.Id > 0)
{
db.AddInParameter(cmd, "@Id", DbType.Int32, entity.Id);
}
db.AddInParameter(cmd, "@AccepterName", DbType.String, entity.AccepterName);
db.AddInParameter(cmd, "@CountryName", DbType.String, entity.CountryName);
object identity = db.ExecuteScalar(cmd);
if (identity == null || identity == DBNull.Value)
{
return(0);
}
return(Convert.ToInt32(identity));
}
public MemPostAddressEntity GetDefaultMemPostAddress(int memid)
{
string sql = @"SELECT top 1 [Id],[MemId],[AccepterName],[CtType],[CountryCode],[CountryName],[ProvinceId],[CityId],[District],[Address],[Email],[Telephone],[MobilePhone],[IsDefault],[Sort]
FROM
dbo.[MemPostAddress] WITH(NOLOCK)
WHERE [MemId]=@MemId Order By [IsDefault] desc,[Sort] desc" ;
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@MemId", DbType.Int32, memid);
MemPostAddressEntity entity = new MemPostAddressEntity();
using (IDataReader reader = db.ExecuteReader(cmd))
{
if (reader.Read())
{
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.MemId = StringUtils.GetDbInt(reader["MemId"]);
entity.AccepterName = StringUtils.GetDbString(reader["AccepterName"]);
entity.CtType = StringUtils.GetDbInt(reader["CtType"]);
entity.CountryCode = StringUtils.GetDbString(reader["CountryCode"]);
entity.CountryName = StringUtils.GetDbString(reader["CountryName"]);
entity.ProvinceId = StringUtils.GetDbInt(reader["ProvinceId"]);
entity.CityId = StringUtils.GetDbInt(reader["CityId"]);
entity.District = StringUtils.GetDbString(reader["District"]);
entity.Address = StringUtils.GetDbString(reader["Address"]);
entity.Email = StringUtils.GetDbString(reader["Email"]);
entity.Telephone = StringUtils.GetDbString(reader["Telephone"]);
entity.MobilePhone = StringUtils.GetDbString(reader["MobilePhone"]);
entity.IsDefault = StringUtils.GetDbInt(reader["IsDefault"]);
entity.Sort = StringUtils.GetDbInt(reader["Sort"]);
}
}
return(entity);
}
public MemPostAddressEntity GetDefaultPostAddress(int memid)
{
MemPostAddressEntity _entity = MemPostAddressDA.Instance.GetDefaultMemPostAddress(memid);
//_entity.ProvinceName = GYProvinceBLL.Instance.GetGYProvinceByCode(_entity.ProvinceId.ToString()).Name;
_entity.CityName = GYCityBLL.Instance.GetGYCityByCode(_entity.CityId.ToString()).Name;
return(_entity);
}
/// <summary>
/// 读取记录列表。
/// </summary>
/// <param name="db">数据库操作对象</param>
/// <param name="columns">需要返回的列，不提供任何列名时默认将返回所有列</param>
public IList <MemPostAddressEntity> GetMemPostAddressList(int pagesize, int pageindex, ref int recordCount, int memid) //pagesize默认为5
{
string where = " WHERE MemId=@MemId ";
string sql = @"SELECT [Id],[MemId],[AccepterName],[CtType],[CountryCode],[CountryName],[ProvinceId],[CityId],[District],[Address],[Email],[Telephone],[MobilePhone],[IsDefault],[Sort]
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY IsDefault desc, Id desc) AS ROWNUMBER,
[Id],[MemId],[AccepterName],[CtType],[CountryCode],[CountryName],[ProvinceId],[CityId],[District],[Address],[Email],[Telephone],[MobilePhone],[IsDefault],[Sort] from dbo.[MemPostAddress] WITH(NOLOCK)
" + where + @") as temp
where rownumber BETWEEN ((@PageIndex - 1) * @PageSize + 1) AND @PageIndex * @PageSize" ;
string sql2 = @"Select count(1) from dbo.[MemPostAddress] with (nolock) " + where;
IList <MemPostAddressEntity> entityList = new List <MemPostAddressEntity>();
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@PageIndex", DbType.Int32, pageindex);
db.AddInParameter(cmd, "@PageSize", DbType.Int32, pagesize);
db.AddInParameter(cmd, "@MemId", DbType.Int32, memid);
using (IDataReader reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
MemPostAddressEntity entity = new MemPostAddressEntity();
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.MemId = StringUtils.GetDbInt(reader["MemId"]);
entity.AccepterName = StringUtils.GetDbString(reader["AccepterName"]);
entity.CtType = StringUtils.GetDbInt(reader["CtType"]);
entity.CountryCode = StringUtils.GetDbString(reader["CountryCode"]);
entity.CountryName = StringUtils.GetDbString(reader["CountryName"]);
entity.ProvinceId = StringUtils.GetDbInt(reader["ProvinceId"]);
entity.CityId = StringUtils.GetDbInt(reader["CityId"]);
entity.District = StringUtils.GetDbString(reader["District"]);
entity.Address = StringUtils.GetDbString(reader["Address"]);
entity.Email = StringUtils.GetDbString(reader["Email"]);
entity.Telephone = StringUtils.GetDbString(reader["Telephone"]);
entity.MobilePhone = StringUtils.GetDbString(reader["MobilePhone"]);
entity.IsDefault = StringUtils.GetDbInt(reader["IsDefault"]);
entity.Sort = StringUtils.GetDbInt(reader["Sort"]);
entityList.Add(entity);
}
}
cmd = db.GetSqlStringCommand(sql2);
db.AddInParameter(cmd, "@MemId", DbType.Int32, memid);
using (IDataReader reader = db.ExecuteReader(cmd))
{
if (reader.Read())
{
recordCount = StringUtils.GetDbInt(reader[0]);
}
else
{
recordCount = 0;
}
}
return(entityList);
}
/// <summary>
/// 插入一条记录到表PostAddress，如果表中存在自增字段，则返回值为新记录的自增字段值，否则返回0。
/// 该方法提供给界面等UI层调用
/// </summary>
/// <param name="postAddress">要添加的PostAddress数据实体对象</param>
public int AddPostAddress(MemPostAddressEntity postAddress)
{
if (postAddress.Id > 0)
{
return(UpdatePostAddress(postAddress));
}
else
{
return(MemPostAddressDA.Instance.AddMemPostAddress(postAddress));
}
}
/// <summary>
/// 收货地址
/// </summary>
/// <returns></returns>
public string GetReceiptAddress()
{
int _id = FormString.IntSafeQ("addressid");
MemPostAddressEntity _entity = new MemPostAddressEntity();
_entity = PostAddressBLL.Instance.GetPostAddress(_id);
if (_entity.MemId == memid)
{
return(JsonJC.ObjectToJson(_entity));
}
else
{
return(JsonJC.ObjectToJson(new MemPostAddressEntity()));
}
}
/// <summary>
/// 根据主键值更新记录的全部字段（注意：该方法不会对自增字段、timestamp类型字段以及主键字段更新！如果要更新主键字段，请使用Update方法）。
/// 如果数据库有数据被更新了则返回True，否则返回False
/// </summary>
/// <param name="db">数据库操作对象</param>
/// <param name="MemPostAddress">待更新的实体对象</param>
public int UpdateMemPostAddress(MemPostAddressEntity entity)
{
string sql = @" UPDATE dbo.[MemPostAddress] SET
[AccepterName]=@AccepterName,[CtType]=@CtType,[CountryCode]=@CountryCode,[CountryName]=@CountryName,[ProvinceId]=@ProvinceId,[CityId]=@CityId,[District]=@District,[Address]=@Address,[Email]=@Email,[Telephone]=@Telephone,[MobilePhone]=@MobilePhone,[IsDefault]=@IsDefault,[Sort]=@Sort
WHERE [Id]=@id and [MemId]=@MemId";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@Id", DbType.Int32, entity.Id);
db.AddInParameter(cmd, "@MemId", DbType.Int32, entity.MemId);
db.AddInParameter(cmd, "@AccepterName", DbType.String, entity.AccepterName);
db.AddInParameter(cmd, "@CtType", DbType.Int32, entity.CtType);
db.AddInParameter(cmd, "@CountryCode", DbType.String, entity.CountryCode);
db.AddInParameter(cmd, "@CountryName", DbType.String, entity.CountryName);
db.AddInParameter(cmd, "@ProvinceId", DbType.String, entity.ProvinceId);
db.AddInParameter(cmd, "@CityId", DbType.String, entity.CityId);
db.AddInParameter(cmd, "@District", DbType.String, entity.District);
db.AddInParameter(cmd, "@Address", DbType.String, entity.Address);
db.AddInParameter(cmd, "@Email", DbType.String, entity.Email);
db.AddInParameter(cmd, "@Telephone", DbType.String, entity.Telephone);
db.AddInParameter(cmd, "@MobilePhone", DbType.String, entity.MobilePhone);
db.AddInParameter(cmd, "@IsDefault", DbType.Int32, entity.IsDefault);
db.AddInParameter(cmd, "@Sort", DbType.Int32, entity.Sort);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 添加收货地址
/// </summary>
/// <returns></returns>
public string AddPostAddress()
{
ResultObj _result = new ResultObj();
int _id = FormString.IntSafeQ("addressid");
int _province = FormString.IntSafeQ("province");
string _acceptername = FormString.SafeQ("acceptername");
int _city = FormString.IntSafeQ("city");
string _countryname = FormString.SafeQ("countryname", 200);
int _cttype = FormString.IntSafeQ("cttype");
string _mobilephone = FormString.SafeQ("mobilephone");
string _address = FormString.SafeQ("address");
string _telephone = FormString.SafeQ("telephone", 200);
string _email = FormString.SafeQ("email", 200);
int _isdefault = FormString.IntSafeQ("isdefault");
MemPostAddressEntity _entity = new MemPostAddressEntity();
if (_id > 0) //如果是更新
{
_entity = PostAddressBLL.Instance.GetPostAddress(_id); //获取地址实体
if (_entity.MemId != memid)
{
_result.Status = (int)CommonStatus.Fail;
return(JsonJC.ObjectToJson(_result));
}
}
_entity.Id = _id;
_entity.AccepterName = _acceptername;
_entity.CountryName = _countryname;
_entity.Address = _address;
_entity.ProvinceId = _province;
_entity.CityId = _city;
_entity.CtType = _cttype;
_entity.MemId = memid;
_entity.MobilePhone = _mobilephone;
_entity.Telephone = _telephone;
_entity.Email = _email;
_entity.IsDefault = _isdefault;
if (_entity.Id > 0)//address已经存在 表示更新
{
if (PostAddressBLL.Instance.UpdatePostAddress(_entity) > 0)
{
_result.Status = (int)CommonStatus.Success;
_result.Obj = PostAddressBLL.Instance.GetPostAddress(_entity.Id);
return(JsonJC.ObjectToJson(_result));
}
}
else if (PostAddressBLL.Instance.GetNumForAddress(memid) < 10) //address不存在 表示添加
{
_entity.IsDefault = 0;
_entity.Sort = 0;
int _Id = PostAddressBLL.Instance.AddPostAddress(_entity);
if (_Id > 0)
{
_result.Status = (int)CommonStatus.Success;
_result.Obj = PostAddressBLL.Instance.GetPostAddress(_Id);
return(JsonJC.ObjectToJson(_result));
}
}
else
{
_result.Status = (int)CommonStatus.AddressNumSuper;
return(JsonJC.ObjectToJson(_result));
}
_result.Status = (int)CommonStatus.Fail;
return(JsonJC.ObjectToJson(_result));
}
/// <summary>
/// 更新一条PostAddress记录。
/// 该方法提供给界面等UI层调用
/// </summary>
/// <param name="postAddress">待更新的实体对象</param>
/// <param name="columns">要更新的列名，不提供任何列名时默认将更新主键之外的所有列</param>
public int UpdatePostAddress(MemPostAddressEntity postAddress)
{
return(MemPostAddressDA.Instance.UpdateMemPostAddress(postAddress));
}
/// <summary>
/// 判断对象是否存在
/// </summary>
/// <param name="dicEnum"></param>
/// <returns></returns>
public bool IsExist(MemPostAddressEntity postAddress)
{
return(MemPostAddressDA.Instance.ExistNum(postAddress) > 0);
}
