/// <summary>
/// 更新一条团购数据
/// </summary>
/// <param name="groupBuy">团购模型变量</param>
public static void UpdateGroupBuy(GroupBuyInfo groupBuy)
{
string sql = "UPDATE " + GroupBuyAccessHelper.TablePrefix + "GroupBuy SET [Name]=@name,[Photo]=@photo,[Description]=@description,[ProductID]=@productID,[StartDate]=@startDate,[EndDate]=@endDate,[Price]=@price,[MinCount]=@minCount,[MaxCount]=@maxCount,[EachNumber]=@eachNumber WHERE [ID]=" + groupBuy.ID.ToString();
OleDbParameter[] parameters =
{
new OleDbParameter("@name", OleDbType.VarWChar),
new OleDbParameter("@photo", OleDbType.VarWChar),
new OleDbParameter("@description", OleDbType.VarWChar),
new OleDbParameter("@productID", OleDbType.Integer),
new OleDbParameter("@startDate", OleDbType.VarWChar),
new OleDbParameter("@endDate", OleDbType.VarWChar),
new OleDbParameter("@price", OleDbType.Decimal),
new OleDbParameter("@minCount", OleDbType.Integer),
new OleDbParameter("@maxCount", OleDbType.Integer),
new OleDbParameter("@eachNumber", OleDbType.Integer)
};
parameters[0].Value = groupBuy.Name;
parameters[1].Value = groupBuy.Photo;
parameters[2].Value = groupBuy.Description;
parameters[3].Value = groupBuy.ProductID;
parameters[4].Value = groupBuy.StartDate;
parameters[5].Value = groupBuy.EndDate;
parameters[6].Value = groupBuy.Price;
parameters[7].Value = groupBuy.MinCount;
parameters[8].Value = groupBuy.MaxCount;
parameters[9].Value = groupBuy.EachNumber;
GroupBuyAccessHelper.ExecuteNonQuery(sql, parameters);
UploadBLL.UpdateUpload(TableID, 0, groupBuy.ID, Cookies.Admin.GetRandomNumber(false));
}
/// <summary>
/// 读取一条用户团购数据
/// </summary>
/// <param name="userID">用户ID</param>
/// <returns>用户团购用户团购数据模型</returns>
public static UserGroupBuyInfo ReadUserGroupBuyByUser(int groupBuyID, int userID)
{
string sql = "SELECT [ID],[GroupBuyID],[Date],[IP],[BuyCount],[OrderID],[UserID],[UserName],[Consignee],[RegionID],[Address],[ZipCode],[Tel],[Email],[Mobile] FROM " + GroupBuyAccessHelper.TablePrefix + "UserGroupBuy WHERE [GroupBuyID]=" + groupBuyID.ToString() + " AND [UserID]=" + userID.ToString();
UserGroupBuyInfo userGroupBuy = new UserGroupBuyInfo();
using (OleDbDataReader dr = GroupBuyAccessHelper.ExecuteReader(sql))
{
if (dr.Read())
{
userGroupBuy.ID = dr.GetInt32(0);
userGroupBuy.GroupBuyID = dr.GetInt32(1);
userGroupBuy.Date = dr.GetDateTime(2);
userGroupBuy.IP = dr[3].ToString();
userGroupBuy.BuyCount = dr.GetInt32(4);
userGroupBuy.OrderID = dr.GetInt32(5);
userGroupBuy.UserID = dr.GetInt32(6);
userGroupBuy.UserName = dr[7].ToString();
userGroupBuy.Consignee = dr[8].ToString();
userGroupBuy.RegionID = dr[9].ToString();
userGroupBuy.Address = dr[10].ToString();
userGroupBuy.ZipCode = dr[11].ToString();
userGroupBuy.Tel = dr[12].ToString();
userGroupBuy.Email = dr[13].ToString();
userGroupBuy.Mobile = dr[14].ToString();
}
}
return(userGroupBuy);
}
/// <summary>
/// 增加一条团购数据
/// </summary>
/// <param name="groupBuy">团购模型变量</param>
public static int AddGroupBuy(GroupBuyInfo groupBuy)
{
string sql = "INSERT INTO " + GroupBuyAccessHelper.TablePrefix + "GroupBuy([Name],[Photo],[Description],[ProductID],[StartDate],[EndDate],[Price],[MinCount],[MaxCount],[EachNumber]) VALUES (@name,@photo,@description,@productID,@startDate,@endDate,@price,@minCount,@maxCount,@eachNumber)";
OleDbParameter[] parameters =
{
new OleDbParameter("@name", OleDbType.VarWChar),
new OleDbParameter("@photo", OleDbType.VarWChar),
new OleDbParameter("@description", OleDbType.VarWChar),
new OleDbParameter("@productID", OleDbType.Integer),
new OleDbParameter("@startDate", OleDbType.VarWChar),
new OleDbParameter("@endDate", OleDbType.VarWChar),
new OleDbParameter("@price", OleDbType.Decimal),
new OleDbParameter("@minCount", OleDbType.Integer),
new OleDbParameter("@maxCount", OleDbType.Integer),
new OleDbParameter("@eachNumber", OleDbType.Integer)
};
parameters[0].Value = groupBuy.Name;
parameters[1].Value = groupBuy.Photo;
parameters[2].Value = groupBuy.Description;
parameters[3].Value = groupBuy.ProductID;
parameters[4].Value = groupBuy.StartDate;
parameters[5].Value = groupBuy.EndDate;
parameters[6].Value = groupBuy.Price;
parameters[7].Value = groupBuy.MinCount;
parameters[8].Value = groupBuy.MaxCount;
parameters[9].Value = groupBuy.EachNumber;
GroupBuyAccessHelper.ExecuteNonQuery(sql, parameters);
Object id = GroupBuyAccessHelper.ExecuteScalar("SELECT MAX([ID]) FROM " + GroupBuyAccessHelper.TablePrefix + "GroupBuy");
groupBuy.ID = Convert.ToInt32(id);
UploadBLL.UpdateUpload(TableID, 0, groupBuy.ID, Cookies.Admin.GetRandomNumber(false));
return(groupBuy.ID);
}
/// <summary>
/// 按分类删除用户团购数据
/// </summary>
/// <param name="strGroupBuyID">分类ID,以,号分隔</param>
public static void DeleteUserGroupBuyByGroupBuyID(string strGroupBuyID)
{
if (strGroupBuyID == string.Empty)
{
return;
}
string sql = "DELETE FROM " + GroupBuyAccessHelper.TablePrefix + "UserGroupBuy WHERE [GroupBuyID] IN(" + strGroupBuyID + ")";
GroupBuyAccessHelper.ExecuteNonQuery(sql);
}
/// <summary>
/// 获得用户团购数据列表
/// </summary>
/// <param name="groupBuyID">分类ID</param>
/// <returns>用户团购数据列表</returns>
public static List <UserGroupBuyInfo> ReadUserGroupBuyList(int groupBuyID)
{
List <UserGroupBuyInfo> userGroupBuyList = new List <UserGroupBuyInfo>();
string sql = "SELECT * FROM " + GroupBuyAccessHelper.TablePrefix + "UserGroupBuy WHERE [GroupBuyID]=" + groupBuyID;
using (OleDbDataReader dr = GroupBuyAccessHelper.ExecuteReader(sql))
{
PrepareUserGroupBuyModel(dr, userGroupBuyList);
}
return(userGroupBuyList);
}
/// <summary>
/// 删除多条团购数据
/// </summary>
/// <param name="strID">团购的主键值,以,号分隔</param>
public static void DeleteGroupBuy(string strID)
{
if (strID == string.Empty)
{
return;
}
UploadBLL.DeleteUploadByRecordID(TableID, strID);
UserGroupBuyBLL.DeleteUserGroupBuyByGroupBuyID(strID);
string sql = "DELETE FROM " + GroupBuyAccessHelper.TablePrefix + "GroupBuy WHERE [ID] IN(" + strID + ")";
GroupBuyAccessHelper.ExecuteNonQuery(sql);
}
/// <summary>
/// 获得用户团购数据的产品数量
/// </summary>
/// <param name="groupBuyID">分类ID</param>
public static Dictionary <int, int> ReadUserGroupBuyCount(string groupBuyIDList)
{
Dictionary <int, int> dicCount = new Dictionary <int, int>();
string sql = "SELECT [GroupBuyID],SUM(BuyCount) FROM " + GroupBuyAccessHelper.TablePrefix + "UserGroupBuy WHERE [GroupBuyID] IN(" + groupBuyIDList + ") GROUP BY [GroupBuyID]";
using (OleDbDataReader dr = GroupBuyAccessHelper.ExecuteReader(sql))
{
while (dr.Read())
{
dicCount.Add(dr.GetInt32(0), Convert.ToInt32(dr[1].ToString()));
}
}
return(dicCount);
}
/// <summary>
/// 获得用户团购数据的产品数量
/// </summary>
/// <param name="groupBuyID">分类ID</param>
public static int ReadUserGroupBuyCount(int groupBuyID)
{
string sql = "SELECT SUM(BuyCount) FROM " + GroupBuyAccessHelper.TablePrefix + "UserGroupBuy WHERE [GroupBuyID]=" + groupBuyID;
object count = GroupBuyAccessHelper.ExecuteScalar(sql);
if (count != null && count != DBNull.Value)
{
return(Convert.ToInt32(count));
}
else
{
return(0);
}
}
/// <summary>
/// 增加一条用户团购数据
/// </summary>
/// <param name="userGroupBuy">用户团购模型变量</param>
public static int AddUserGroupBuy(UserGroupBuyInfo userGroupBuy)
{
string sql = "INSERT INTO " + GroupBuyAccessHelper.TablePrefix + "UserGroupBuy([GroupBuyID],[Date],[IP],[BuyCount],[OrderID],[UserID],[UserName],[Consignee],[RegionID],[Address],[ZipCode],[Tel],[Email],[Mobile]) VALUES (@groupBuyID,@date,@iP,@buyCount,@orderID,@userID,@userName,@consignee,@regionID,@address,@zipCode,@tel,@email,@mobile)";
OleDbParameter[] parameters =
{
new OleDbParameter("@groupBuyID", OleDbType.Integer),
new OleDbParameter("@date", OleDbType.VarWChar),
new OleDbParameter("@iP", OleDbType.VarWChar),
new OleDbParameter("@buyCount", OleDbType.Integer),
new OleDbParameter("@orderID", OleDbType.Integer),
new OleDbParameter("@userID", OleDbType.Integer),
new OleDbParameter("@userName", OleDbType.VarWChar),
new OleDbParameter("@consignee", OleDbType.VarWChar),
new OleDbParameter("@regionID", OleDbType.VarWChar),
new OleDbParameter("@address", OleDbType.VarWChar),
new OleDbParameter("@zipCode", OleDbType.VarWChar),
new OleDbParameter("@tel", OleDbType.VarWChar),
new OleDbParameter("@email", OleDbType.VarWChar),
new OleDbParameter("@mobile", OleDbType.VarWChar)
};
parameters[0].Value = userGroupBuy.GroupBuyID;
parameters[1].Value = userGroupBuy.Date;
parameters[2].Value = userGroupBuy.IP;
parameters[3].Value = userGroupBuy.BuyCount;
parameters[4].Value = userGroupBuy.OrderID;
parameters[5].Value = userGroupBuy.UserID;
parameters[6].Value = userGroupBuy.UserName;
parameters[7].Value = userGroupBuy.Consignee;
parameters[8].Value = userGroupBuy.RegionID;
parameters[9].Value = userGroupBuy.Address;
parameters[10].Value = userGroupBuy.ZipCode;
parameters[11].Value = userGroupBuy.Tel;
parameters[12].Value = userGroupBuy.Email;
parameters[13].Value = userGroupBuy.Mobile;
GroupBuyAccessHelper.ExecuteNonQuery(sql, parameters);
Object id = GroupBuyAccessHelper.ExecuteScalar("SELECT MAX([ID]) FROM " + GroupBuyAccessHelper.TablePrefix + "UserGroupBuy");
return(Convert.ToInt32(id));
}
/// <summary>
/// 读取一条团购数据
/// </summary>
/// <param name="id">团购的主键值</param>
/// <returns>团购数据模型</returns>
public static GroupBuyInfo ReadGroupBuy(int id)
{
string sql = "SELECT [ID],[Name],[Photo],[Description],[ProductID],[StartDate],[EndDate],[Price],[MinCount],[MaxCount],[EachNumber] FROM " + GroupBuyAccessHelper.TablePrefix + "GroupBuy WHERE [ID]=" + id.ToString();
GroupBuyInfo groupBuy = new GroupBuyInfo();
using (OleDbDataReader dr = GroupBuyAccessHelper.ExecuteReader(sql))
{
if (dr.Read())
{
groupBuy.ID = dr.GetInt32(0);
groupBuy.Name = dr[1].ToString();
groupBuy.Photo = dr[2].ToString();
groupBuy.Description = dr[3].ToString();
groupBuy.ProductID = dr.GetInt32(4);
groupBuy.StartDate = dr.GetDateTime(5);
groupBuy.EndDate = dr.GetDateTime(6);
groupBuy.Price = dr.GetDecimal(7);
groupBuy.MinCount = dr.GetInt32(8);
groupBuy.MaxCount = dr.GetInt32(9);
groupBuy.EachNumber = dr.GetInt32(10);
}
}
return(groupBuy);
}
/// <summary>
/// 更新一条用户团购数据
/// </summary>
/// <param name="userGroupBuyID">用户团购ID</param>
/// <param name="orderID">订单ID号</param>
/// <param name="userID">用户ID</param>
public static void UpdateUserGroupBuy(int userGroupBuyID, int orderID)
{
string sql = "UPDATE " + GroupBuyAccessHelper.TablePrefix + "UserGroupBuy SET [OrderID]=" + orderID.ToString() + " WHERE [ID]=" + userGroupBuyID.ToString();
GroupBuyAccessHelper.ExecuteNonQuery(sql);
}
/// <summary>
/// 返回DataTable对像
/// </summary>
/// <returns></returns>
public override DataTable ExecuteDataTable()
{
return(GroupBuyAccessHelper.ExecuteDataTable(this.PrepareSQL()));
}
/// <summary>
/// 返回DataReader对像
/// </summary>
/// <returns></returns>
public override OleDbDataReader ExecuteReader()
{
return(GroupBuyAccessHelper.ExecuteReader(this.PrepareSQL()));
}