//OrderInfo 谁 在那个桌子上 什么时候 多少钱 买单没 //OrderDetailInfo 点了什么 点了多少 public int KaiDan(int id) { string sql = //订单表插入数据 "insert into OrderInfo(ODate,IsPay,TableId) values(datetime('now','localtime'),0,@id);" //更新餐桌 + "update TableInfo set TIsFree=0 where TId=@id;" //获得单号 + "select Oid from OrderInfo order by ODate desc limit 0,1"; SQLiteParameter param = new SQLiteParameter("@id", id); return(Convert.ToInt32(SqliteHelper.ExecuteScalar(sql, param))); }
public decimal GetTotalMoneyByOrderId(int orderId) { string sql = "SELECT SUM(odi.Count*di.DPrice) FROM OrderDetailInfo AS odi INNER JOIN DishInfo AS di ON odi.DishId = di.DId WHERE odi.OrderId = @orderId"; SQLiteParameter p = new SQLiteParameter("@orderId", orderId); try { return(Convert.ToDecimal(SqliteHelper.ExecuteScalar(sql, p))); } catch { return(0); } }
public int KaiOrder(int tableId) { //插入订单数据 //更新餐桌状态 //写在一起执行,只需要和数据库交互一次 //下订单 string sql = "insert into orderinfo(odate,ispay,tableId) values(datetime('now', 'localtime'),0,@tid);" + //更新餐桌状态 "update tableinfo set tIsFree=0 where tid=@tid;" + //获取最新的订单编号 "select oid from orderinfo order by oid desc limit 0,1"; SQLiteParameter p = new SQLiteParameter("@tid", tableId); return(Convert.ToInt32(SqliteHelper.ExecuteScalar(sql, p))); }
public int CreateOrder(int tableId) { //插入订单数据 //更新餐桌状态 //写在一起执行,只需要和数据库交互一次. //两个sql语句用分号隔开 //下订单 string sql = "INSERT INTO OrderInfo(ODate, IsPay, TableId) VALUES(datetime('now','localtime'),0,@tableId);" + //更新餐桌状态 "UPDATE TableInfo SET TIsFree = 0 WHERE TId = @tableId;" + //获取最新的订单编号 "SELECT OId FROM OrderInfo WHERE TableId = @tableId AND IsPay = 0 ORDER BY OId DESC LIMIT 0,1"; SQLiteParameter p = new SQLiteParameter("@tableId", tableId); return(Convert.ToInt32(SqliteHelper.ExecuteScalar(sql, p))); }
//get total money public decimal GetTotalMoneyByOrderId(int orderid) { string sql = @" select sum(oti.count*di.dprice) from orderdetailinfo as oti inner join dishinfo as di on oti.dishid=di.did where oti.orderid=@orderid" ; SQLiteParameter p = new SQLiteParameter("@orderid", orderid); object obj = SqliteHelper.ExecuteScalar(sql, p); if (obj == DBNull.Value) { return(0); } return(Convert.ToDecimal(obj)); }
public int DianCai(int Did, int Oid) { string sql = "select Count(*) from OrderDetailInfo where OrderId=@OId and DishId=@DId"; SQLiteParameter[] param = { new SQLiteParameter("@Oid", Oid), new SQLiteParameter("@Did", Did) }; if (Convert.ToInt32(SqliteHelper.ExecuteScalar(sql, param)) > 0) { sql = "update OrderDetailInfo set count=count+1 where OrderId=@OId and DishId=@DId"; } else { sql = "insert into OrderDetailInfo(OrderId,DishId,Count) values(@Oid,@Did,1)"; } return(SqliteHelper.ExecuteNonQuery(sql, param)); }
//order dish public int DianCai(int orderId, int dishId) { string sql = "select count(*) from orderDetailInfo where orderId=@oid and dishId=@did"; SQLiteParameter[] ps = { new SQLiteParameter("@oid", orderId), new SQLiteParameter("@did", dishId) }; int count = Convert.ToInt32(SqliteHelper.ExecuteScalar(sql, ps)); if (count > 0) { //这个订单已经点过这个菜,让数量加1 sql = "update orderDetailInfo set count=count+1 where orderId=@oid and dishId=@did"; } else { sql = "insert into orderDetailInfo(orderId,dishId,count) values(@oid,@did,1)"; } return(SqliteHelper.ExecuteNonQuery(sql, ps)); }
public int ChooseDishes(int orderId, int dishId) { string sql = "SELECT COUNT(*) FROM OrderDetailInfo WHERE DishId = @dishId AND OrderId = @orderId"; SQLiteParameter[] ps = { new SQLiteParameter("@orderId", orderId), new SQLiteParameter("@dishId", dishId) }; int index = Convert.ToInt32(SqliteHelper.ExecuteScalar(sql, ps)); if (index > 0) { //已点菜品增加份数 sql = "UPDATE OrderDetailInfo SET Count = Count + 1 WHERE DishId = @dishId AND OrderId = @orderId"; } else { //点新蔡 sql = "INSERT INTO OrderDetailInfo(OrderId, DishId, Count) VALUES(@orderId, @dishId, 1)"; } return(SqliteHelper.ExecuteNonQuery(sql, ps)); }
public int TakeOrder(int orderId, int dishId) { //Is the current cuisine being ordered? string sql = "select count(*) from orderDetailInfo where orderId=@oid and dishId=@did"; SQLiteParameter[] ps = { new SQLiteParameter("@oid", orderId), new SQLiteParameter("@did", dishId) }; int count = Convert.ToInt32(SqliteHelper.ExecuteScalar(sql, ps)); if (count > 0) { //If is being ordered, plus the quantity by one sql = "update orderDetailInfo set count=count+1 where orderId=@oid and dishId=@did"; } else { //If not being ordered so far, add the cuisine to the order sql = "insert into orderDetailInfo(orderid,dishId,count) values(@oid,@did,1)"; } return(SqliteHelper.ExecuteNonQuery(sql, ps)); }
//根据桌子的ID获得订单的id public int GetOrderIdByTableId(int tableId) { string sql="select oid from orderinfo where tableId=@tableid and ispay=0"; SQLiteParameter p = new SQLiteParameter("@tableid", tableId); return Convert.ToInt32(SqliteHelper.ExecuteScalar(sql, p)); }