public static int AddToOrder(int userID, int shopID, int orderID, double price, string orderState)
{
int res;
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
res = context.Database.ExecuteSqlCommand(@"INSERT INTO dbo.TB_ORDER
( ORDER_USER_ID ,
ORDER_TIME ,
ORDER_SHOP_ID ,
ORDER_NO ,
ORDER_ALLPRICE ,
ORDER_STATE
)
VALUES ( {0} , -- ORDER_USER_ID - int
GETDATE() , -- ORDER_TIME - datetime
{1} , -- ORDER_SHOP_ID - int
{2} , -- ORDER_NO - int
{3} , -- ORDER_ALLPRICE - int
{4} -- ORDER_STATE - varchar(20)
)" , userID, shopID, orderID, price, orderState);
}
return(res);
}
/// <summary>
/// 卖家登录
/// </summary>
/// <param name="account"></param>
/// <param name="pwd"></param>
/// <returns></returns>
public static TB_SELLER LoginSeller(string account, string pwd)
{
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
var obj = context.TB_SELLER.FirstOrDefault(t => t.ACCOUNT == account && t.PWD == pwd);
return(obj);
}
}
public static TB_BUYER GetBuyerById(int id)
{
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
var res = context.TB_BUYER.FirstOrDefault(t => t.ID == id);
return(res);
}
}
/// <summary>
/// 模糊查询
/// </summary>
/// <param name="RiderID"></param>
/// <param name="keyOrderID"></param>
/// <returns></returns>
public static List <TB_ORDER> SearchOrderID(int RiderID, string keyOrderID)
{
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
var list = context.Database.SqlQuery <TB_ORDER>("SELECT * FROM dbo.TB_ORDER WHERE ORDER_RIDER_ID=" + RiderID + "AND ID LIKE '%" + keyOrderID + "%'").ToList();
return(list);
}
}
public static TB_GOODS GetGoodsInfoByID(int id)
{
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
var res = context.TB_GOODS.FirstOrDefault(t => t.ID == id);
return(res);
}
}
public static List <TB_GOODS> SearchGoods(int shopID, string keyWord)
{
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
var list = context.Database.SqlQuery <TB_GOODS>("SELECT * FROM TB_GOODS WHERE GOODS_SHOP_ID=" + shopID + " AND GOODS_NAME LIKE '%" + keyWord + "%'").ToList();
return(list);
}
}
/// <summary>
/// 根据店铺ID返回所有商品信息
/// </summary>
/// <param name="shopID"></param>
/// <returns></returns>
public static List <TB_GOODS> GetGoodsInfoByShopID(int shopID)
{
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
var list = context.Database.SqlQuery <TB_GOODS>("SELECT * FROM TB_GOODS WHERE GOODS_SHOP_ID=" + shopID).ToList();
return(list);
}
}
/// <summary>
/// 根据id修改订单状态
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static void ConfirmDelivery(int id)
{
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
TB_ORDER confirm = context.TB_ORDER.FirstOrDefault(t => t.ID == id);
confirm.ORDER_STATE = "待评价";
context.SaveChanges();
}
}
/// <summary>
/// 通过店铺ID查询店铺信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static TB_SHOP GetShopByID(int id)
{
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
//var list = context.Database.SqlQuery<TB_SHOP>("SELECT * FROM TB_SHOP WHERE ID=" + id).ToList();
var res = context.TB_SHOP.FirstOrDefault(t => t.ID == id);
return(res);
}
}
//订单查询
public static List <TB_ORDER> OrderQuery()
{
//实例化EMD对象ARTICLE_DBEntities，数据库上下文
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
var list = context.Database.SqlQuery <TB_ORDER>("SELECT * FROM TB_ORDER").ToList();
return(list);
}
}
public static List <Orders> GetUnevaluateOrders(int?id)
{
List <Orders> lstGoods = new List <Orders>();
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
lstGoods = context.Database.SqlQuery <Orders>("SELECT DISTINCT dbo.TB_ORDER.ORDER_NO,dbo.TB_ORDER.ORDER_TIME,dbo.TB_ORDER.ORDER_STATE FROM dbo.TB_ORDER_DETAILS, dbo.TB_GOODS, dbo.TB_ORDER WHERE dbo.TB_ORDER_DETAILS.DETORSER_GOODS_ID = dbo.TB_GOODS.ID AND dbo.TB_ORDER.ORDER_NO = dbo.TB_ORDER_DETAILS.ORDER_ID AND ORDER_STATE = '待评价' AND dbo.TB_ORDER.ORDER_USER_ID=" + id).ToList();
}
return(lstGoods);
}
public static List <TB_SHOP> GetShopInfo(int?id)
{
List <TB_SHOP> lstBuyers = new List <TB_SHOP>();
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
lstBuyers = context.Database.SqlQuery <TB_SHOP>("SELECT * FROM dbo.TB_SHOP WHERE ID=" + id).ToList();
}
return(lstBuyers);
}
public static List <TB_GOODS> GetHomeGoodsByNameOrType(string val)
{
List <TB_GOODS> lstGoods = new List <TB_GOODS>();
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
lstGoods = context.Database.SqlQuery <TB_GOODS>("SELECT * FROM TB_GOODS WHERE GOODS_NAME LIKE {0} OR GOODS_TYPE LIKE {1}", "%" + val + "%", "%" + val + "%").ToList();
}
return(lstGoods);
}
public static int GetCartGoodsNum(int userID)
{
List <int> lst = new List <int>();
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
lst = context.Database.SqlQuery <int>(@"SELECT COUNT(*) FROM dbo.TB_CART WHERE CART_USER_ID = {0};", userID).ToList();
}
return(lst[0]);
}
/// <summary>
/// 用户登录
/// </summary>
/// <param name="account"></param>
/// <param name="pwd"></param>
/// <returns></returns>
public static TB_BUYER LoginBuyer(string account, string pwd)
{
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
var obj = context.TB_BUYER.FirstOrDefault(t => t.ACCOUNT == account && t.PWD == pwd);
//TB_BUYER entity = context.TB_BUYER.SqlQuery("SELECT * FROM TB_BUYER WHERE ACCOUNT='account' AND PWD='pwd'", account, pwd).FirstOrDefault();
//return entity;
return(obj);
}
}
public static List <TB_GOODS> GetHomeGoods()
{
List <TB_GOODS> lstGoods = new List <TB_GOODS>();
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
lstGoods = context.Database.SqlQuery <TB_GOODS>("SELECT * FROM TB_GOODS").ToList();
}
return(lstGoods);
}
/// <summary>
/// 骑手-抢单
/// </summary>
/// <returns></returns>
public static List <TB_ORDER> GetOrderNotShipped()
{
List <TB_ORDER> lstOrder = new List <TB_ORDER>();
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
lstOrder = context.Database.SqlQuery <TB_ORDER>("SELECT * FROM dbo.TB_ORDER WHERE ORDER_STATE ='付款待发货';").ToList();
}
return(lstOrder);
}
public static List <TB_BUYER> GetBuyersInfo(int?id)
{
List <TB_BUYER> lstBuyers = new List <TB_BUYER>();
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
lstBuyers = context.Database.SqlQuery <TB_BUYER>("SELECT * FROM TB_BUYER WHERE id=" + id).ToList();
}
return(lstBuyers);
}
/// <summary>
/// 抢单
/// </summary>
/// <param name="id"></param>
public static void GrabSingle(int id, int RiderID)
{
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
TB_ORDER confirm = context.TB_ORDER.FirstOrDefault(t => t.ID == id);
//confirm.ORDER_STATE = "付款待发货";
confirm.ORDER_RIDER_ID = RiderID;
context.SaveChanges();
}
}
public static List <Collect_Shops> GetCollectShopsInfo(int?id)
{
List <Collect_Shops> lstShops = new List <Collect_Shops>();
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
lstShops = context.Database.SqlQuery <Collect_Shops>("SELECT DISTINCT dbo.TB_SHOP.SHOP_NAME,TB_SHOP.ID,dbo.TB_SHOP.SHOP_IMAGE_URL FROM dbo.TB_SHOP, dbo.TB_BUYER_COLLECT, dbo.TB_BUYER WHERE dbo.TB_BUYER_COLLECT.COLLECT_ID = dbo.TB_SHOP.ID AND dbo.TB_BUYER_COLLECT.ACCOUNT = dbo.TB_BUYER.ID AND dbo.TB_BUYER.ID = " + id).ToList();
}
return(lstShops);
}
/// <summary>
/// 检查用户账号是否已经注册
/// </summary>
/// <param name="account"></param>
/// <returns></returns>
public static int CheckSeller(string account)
{
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
var res = context.TB_SELLER.Count(t => t.ACCOUNT == account);
var sqls = new SqlParameter[] {
new SqlParameter("@ACCOUNT", account)
};
return(res);
}
}
/// <summary>
/// 检查卖家账号是否存在
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public static int CheckSellerName(string name)
{
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
var res = context.TB_SELLER.Count(t => t.SELLER_NAME == name);
var sqls = new SqlParameter[] {
new SqlParameter("@SELLER_NAME", name)
};
return(res);
}
}
public static int DeleteCartGoods(int cartID)
{
int res;
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
res = context.Database.ExecuteSqlCommand(@"DELETE FROM dbo.TB_CART WHERE ID = {0};", cartID);
}
return(res);
}
/// <summary>
/// 骑手-抢单（分页查询）
/// </summary>
/// <param name="page"></param>
/// <returns></returns>
public static Pager <TB_ORDER> GetOrderNotShippedPager(PageParam page)
{
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
Pager <TB_ORDER> pg = new Pager <TB_ORDER>();
var res = context.TB_ORDER.Where(t => t.ORDER_STATE == "付款待发货" && t.ORDER_RIDER_ID == null);
//var res = context.Database.SqlQuery<OrderZong>("SELECT dbo.TB_ORDER.ID,BUYER_NAME,BUYER_ADDRESS,SHOP_NAME,SHOP_ADDRESS,ORDER_RIDER_ID,ORDER_TIME,ORDER_STATE FROM dbo.TB_ORDER,dbo.TB_BUYER,dbo.TB_SHOP WHERE dbo.TB_BUYER.ID = dbo.TB_ORDER.ORDER_USER_ID AND dbo.TB_SHOP.ID = dbo.TB_ORDER.ORDER_SHOP_ID AND ORDER_STATE = '付款待发货' AND ORDER_RIDER_ID IS NULL");
pg.Total = res.Count();
pg.Rows = res.OrderBy(t => t.ORDER_TIME).Skip(page.Skip).Take(page.PageSize).ToList();
return(pg);
}
}
/// <summary>
/// 骑手-未完成的订单（分页查询）
/// </summary>
/// <param name="page"></param>
/// <returns></returns>
public static Pager <TB_ORDER> GetOrderUnfinishedPager(PageParam page, int RiderID)
{
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
Pager <TB_ORDER> pg = new Pager <TB_ORDER>();
var res = context.TB_ORDER.Where(t => t.ORDER_STATE == "发货待收货" && t.ORDER_RIDER_ID == RiderID);
//var res = context.Database.SqlQuery<TB_ORDER>("SELECT * FROM dbo.TB_ORDER,dbo.TB_BUYER,dbo.TB_SHOP WHERE dbo.TB_BUYER.ID = dbo.TB_ORDER.ORDER_USER_ID AND dbo.TB_SHOP.ID = dbo.TB_ORDER.ORDER_SHOP_ID AND ORDER_STATE = '发货待收货' AND ORDER_RIDER_ID = " + RiderID).ToList();
pg.Total = res.Count();
pg.Rows = res.OrderBy(t => t.ORDER_TIME).Skip(page.Skip).Take(page.PageSize).ToList();
return(pg);
}
}
public static int RemoveGoods(int id)
{
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
var category = context.TB_GOODS.FirstOrDefault(t => t.ID == id);
if (null != category)
{
context.TB_GOODS.Remove(category);
}
return(context.SaveChanges());
}
}
public static List <CartDetail> GetCartGoods(int userID)
{
List <CartDetail> lstCart = new List <CartDetail>();
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
lstCart = context.Database.SqlQuery <CartDetail>(@"SELECT GOODS_SHOP_ID,TB_CART.ID,GOODS_NAME,GOODS_PRICE,GOODS_IMAGE_URL,CART_GOODS_ID,CART_GOODS_NUM FROM dbo.TB_CART
INNER JOIN dbo.TB_GOODS ON TB_GOODS.ID = CART_GOODS_ID
WHERE CART_USER_ID = {0};", userID).ToList();
}
return(lstCart);
}
public static List <Evaluate> GetEvaluate(int goodsID)
{
List <Evaluate> lstEvaluate = new List <Evaluate>();
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
lstEvaluate = context.Database.SqlQuery <Evaluate>(@"SELECT BUYER_NAME,BUYER_IMAGE_URL,EVALUATE_CONTENT,EVALUATE_STAR
FROM dbo.TB_EVALUATE_GOODS
INNER JOIN dbo.TB_BUYER ON TB_BUYER.ID = EVALUATE_USER_ID WHERE GOODS_ID = {0};", goodsID).ToList();
}
return(lstEvaluate);
}
public static void ClearOrder(int userID)
{
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
TB_CART category = context.TB_CART.FirstOrDefault(t => t.CART_USER_ID == userID);//先查找出要修改的对象
if (null != category)
{
context.TB_CART.Remove(category);
context.SaveChanges();
}
}
}
/// <summary>
/// 通过订单ID修改订单状态（卖家派送）
/// </summary>
/// <param name="id"></param>
public static void UpdateStateByID(int id)
{
using (JANESS_SYSTEM_DBEntities context = new JANESS_SYSTEM_DBEntities())
{
TB_ORDER category = context.TB_ORDER.FirstOrDefault(t => t.ID == id);//先查找出要修改的对象
if (null != category)
{
category.ORDER_STATE = "发货待收货";//修改数据
context.SaveChanges();
}
}
}
