//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));
}