//添加到追踪列表
public static void AddItemToList(int userId, int itemId)
{
DbHelper.Action(string.Format("replace into Trace_List " +
"VALUES('{0}','{1}'," +
"(select Item_Price from Item where Item_ID='{1}')" +
")", userId, itemId));
}
//删除订单
public static void DeleteOrder(int orderId)
{
//删除订单内商品列表
DbHelper.Action(string.Format("delete from Order_List where Order_ID='{0}'", orderId));
//删除订单
DbHelper.Action(string.Format("delete from Order_Detail where Order_ID='{0}'", orderId));
}
//修改用户信息（不涉及管理员权限）
public static bool AlterUserInfo(User user)
{
int result = 0;
//密码不为空 则修改密码
if (!string.IsNullOrEmpty(user.User_Password))
{
result = DbHelper.Action(string.Format("update User set " +
"First_Name='{0}',Last_Name='{1}',Email='{2}',Address='{3}',User_Password='******' where User_ID='{5}'",
user.First_Name,
user.Last_Name,
user.Email,
user.Address,
user.User_Password,
user.User_ID
));
}
else
{
result = DbHelper.Action(string.Format("update User set " +
"First_Name='{0}',Last_Name='{1}',Email='{2}',Address='{3}' where User_ID='{4}'",
user.First_Name,
user.Last_Name,
user.Email,
user.Address,
user.User_ID
));
}
return(result == 1);
}
//添加用户
public static bool AddUser(User user)
{
return(DbHelper.Action(string.Format("insert into User values(null,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
user.User_Name,
user.User_Password,
user.First_Name,
user.Last_Name,
user.Email,
user.Address,
(user.Admin == true ? 1 : 0)//转换为tinyint
)) == 1);
}
//删除商品
public static void DeleteItem(int id)
{
//将该商品评论删除
//DbHelper.Action(string.Format("delete from Review where Item_ID='{0}';", id));
//将购物车内的该商品删除
DbHelper.Action(string.Format("delete from Cart where Item_ID='{0}';", id));
//将用户追踪列表中该商品id设为null
DbHelper.Action(string.Format("update Trace_List set Item_ID=NULL where Item_ID='{0}';", id));
//将订单内该商品id设为null
DbHelper.Action(string.Format("update Order_List set Item_ID=NULL where Item_ID='{0}';", id));
//删除商品
DbHelper.Action(string.Format("delete from Item where Item_ID='{0}';", id));
}
//更新购物车
public static void UpdateCart(int id, List <Cart> carts)
{
//清空原有的购物车
DbHelper.Action(string.Format("delete from Cart where User_ID='{0}'", id));
if (carts.Count != 0)
{
foreach (Cart item in carts)
{
//逐条添加
DbHelper.Action(string.Format("insert into Cart values(null,'{0}','{1}','{2}')", id, item.Item_ID, item.Quantity));
}
}
}
//添加商品
public static bool AddItem(Item item)
{
return(DbHelper.Action(string.Format("insert into Item values(null,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')",
item.Item_Name,
item.Image_Url,
item.Description,
item.Item_Price,
item.Category_ID,
item.Click_Counts,
item.Stock,
item.Date_added.ToString("yyyy-MM-ddTHH:mm:ss"),
item.Cost,
item.Sold,
item.Discount
)) == 1);
}
//将商品加入购物车
public static void AddItemToCart(int userId, int itemId, int itemCount)
{
//查找该商品是否已存在购物车中
bool exist = Convert.ToInt32(DbHelper.Read(string.Format("select count(*) from Cart where User_ID='{0}' and Item_ID='{1}'", userId, itemId))) != 0;
if (exist)
{
//已存在，添加数量
DbHelper.Action(string.Format("update Cart set Quantity=(Quantity+{2}) where User_ID='{0}' and Item_ID='{1}'", userId, itemId, itemCount));
}
else
{
//不存在，添加一条
DbHelper.Action(string.Format("insert into Cart values(null,'{0}','{1}','{2}')", userId, itemId, itemCount));
}
//商品点击数加一
DbHelper.Action(string.Format("update Item set Click_Counts=Click_Counts+1 where Item_ID='{0}'", itemId));
}
//修改商品信息
public static bool AlterItemInfo(Item item)
{
return(DbHelper.Action(string.Format("update Item set " +
"Item_Name='{0}', Image_Url='{1}', Description='{2}', Item_Price='{3}', Category_ID='{4}', Click_Counts='{5}', " +
"Stock='{6}', Date_added='{7}', Cost='{8}', Sold='{9}', Discount='{10}' where Item_ID='{11}'",
item.Item_Name,
item.Image_Url,
item.Description,
item.Item_Price,
item.Category_ID,
item.Click_Counts,
item.Stock,
item.Date_added.ToString("yyyy-MM-ddTHH:mm:ss"),
item.Cost,
item.Sold,
item.Discount,
item.Item_ID
)) == 1);
}
//删除用户
//同时删除：购物车、追踪列表
//FK id更改为0：评论、订单
public static void DeleteUser(int userId)
{
/*
* -- delete from `User` where User_ID='1'
* -- delete from Cart where User_ID='1'
* -- delete from Trace_List where User_ID='1'
* -- update Review set User_ID='0' where User_ID='5'
*/
//删除购物车
DbHelper.Action(string.Format("delete from Cart where User_ID='{0}'", userId));
//删除追踪列表
DbHelper.Action(string.Format("delete from Trace_List where User_ID='{0}'", userId));
//评论 id设置为null
//DbHelper.Action(string.Format("update Review set User_ID=null where User_ID='{0}'", userId));
//订单 id设置为null
DbHelper.Action(string.Format("update Order_Detail set User_ID=null where User_ID='{0}'", userId));
//删除用户
DbHelper.Action(string.Format("delete from User where User_ID='{0}'", userId));
}
//扣除商品库存
public static void ReduceStock(int id, int count)
{
DbHelper.Action(string.Format("update Item set Stock=Stock-'{1}' where Item_ID='{0}'", id, count));
}
//设置订单发货状态
public static bool SetOrderStatus(int orderId, int status)
{
return(Convert.ToInt32(DbHelper.Action(string.Format("update Order_Detail set Shipment='{1}' where Order_ID='{0}'", orderId, status))) == 1);
}
//创建新订单
public static bool NewOrder(User user, int delivery, int fee = 30)
{
//获取购物车信息
List <Cart> carts = ItemManager.GetUserCart(user.User_ID);
//创建订单列表
List <Order_List> list = new List <Order_List>();
//创建订单id
int orderId = 0;
//总价格
double totalPrice = 0;
//如果生成的id有重复，则一直取到无重复为止
do
{
orderId = Tool.GetRandomNumber(1, 99999999);
} while (CheckOrderIdExist(orderId));
//检查库存
foreach (Cart cart in carts)
{
int itemStock = ItemManager.GetItem(cart.Item_ID).Stock;
if (itemStock < cart.Quantity)
{
//商品无库存，创建订单失败
return(false);
}
}
//计算订单成本
double totalCost = 0;
//生成列表
foreach (Cart cart in carts)
{
//减少商品库存
ItemManager.ReduceStock(cart.Item_ID, cart.Quantity);
//应用商品折扣
double itemPrice = cart.Item_Price;
if (cart.Discount != 0)
{
//折扣价四舍五入
double calculate = itemPrice * ((100.0 - cart.Discount) / 100.0);
itemPrice = Tool.Rounde(calculate);
}
//商品成本
double itemCost = ItemManager.GetItemCost(cart.Item_ID);
//创建一条记录
Order_List order_List = new Order_List();
order_List.Order_ID = orderId; //生成的id
order_List.Item_ID = cart.Item_ID;
order_List.Order_Cost = itemCost; //商品进价
order_List.Order_Price = itemPrice; //折扣价格
order_List.Quantity = cart.Quantity;
//计入总价（价格*数量）
totalPrice += itemPrice * cart.Quantity;
//记入成本（价格*数量）
totalCost += itemCost * cart.Quantity;
//添加到订单列表中
list.Add(order_List);
}
//如果派送方式非自取，加配送费
if (delivery == 1)
{
totalPrice += fee;
}
//总价四舍五入（保留两位）
totalPrice = Tool.Rounde(totalPrice);
//创建订单
Order_Detail detail = new Order_Detail();
detail.Order_ID = orderId;//生成的id
detail.User_ID = user.User_ID;
detail.Order_Sum = totalPrice;
detail.Order_Cost = totalCost;
detail.Delivery = delivery;
detail.Shipment = 1;//默认未发货
detail.Address = user.Address;
detail.Order_Date = DateTime.Now;
//写入订单详情
DbHelper.Action(string.Format("insert into Order_Detail values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')",
detail.Order_ID,
detail.User_ID,
detail.Order_Sum,
detail.Order_Cost,
detail.Delivery,
detail.Shipment,
detail.Address,
detail.Order_Date.ToString("yyyy-MM-ddTHH:mm:ss")
));
//写入订单列表
foreach (Order_List item in list)
{
DbHelper.Action(string.Format("insert into Order_List values('{0}','{1}','{2}','{3}','{4}')",
item.Order_ID,
item.Item_ID,
item.Order_Price,
item.Order_Cost,
item.Quantity
));
}
//下单成功后清空原有的购物车
DbHelper.Action(string.Format("delete from Cart where User_ID='{0}'", user.User_ID));
return(true);
}
//从追踪列表中移除
public static void RemoveItemFromList(int userId, int itemId)
{
DbHelper.Action(string.Format("delete from Trace_List " +
"where User_ID='{0}' and Item_ID='{1}'", userId, itemId));
}
//修改用户权限
public static bool ChangeUserPrivilege(int userId, bool isAdmin)
{
return(DbHelper.Action(string.Format("update User set Admin={1} where User_ID='{0}'", userId, isAdmin)) == 1);
}
