//添加到追踪列表 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); }