/// <summary>
/// 根据订单的id查询该订单的总金额和数量
/// </summary>
/// <param name="orderId"></param>
/// <returns></returns>
public R_OrderInfo_Product GetMoneyAndCount(int orderId)
{
string sql = "select count(*),sum(ProPrice*UnitCount) from R_OrderInfo_Product inner join ProductInfo on ProductInfo.ProId= R_OrderInfo_Product.ProId where R_OrderInfo_Product.DelFlag=0 and OrderId=" + orderId;
R_OrderInfo_Product rop = null;
using (SQLiteDataReader reader = Sqlitehelper.ExecuteReader(sql))
{
if (reader.HasRows)
{
while (reader.Read())
{
rop = new R_OrderInfo_Product();
rop.CT = Convert.ToInt32(reader[0]);
if (DBNull.Value == reader[1])
{
rop.MONEY = 0;
}
else
{
rop.MONEY = Convert.ToDecimal(reader[1]);
}
}
}
}
return(rop);
}
//点菜
private void dgvProduct_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
if (dgvProduct.SelectedRows.Count > 0)
{
//首先获取该餐桌的订单id
//int id = this.ID;
//获取点菜的菜的id
int proId = Convert.ToInt32(dgvProduct.SelectedRows[0].Cells[0].Value);
//插入到订单和菜单的中间表
//数量
int count = string.IsNullOrEmpty(txtCount.Text) || txtCount.Text == "1" ? 1 : Convert.ToInt32(txtCount.Text);
R_OrderInfo_ProductBLL bll = new R_OrderInfo_ProductBLL();
R_OrderInfo_Product roip = new R_OrderInfo_Product();
roip.OrderId = this.ID;
roip.DelFlag = 0;
roip.ProId = proId;
roip.State = 0;
roip.SubTime = System.DateTime.Now;
roip.UnitCount = count;
// bll.AddR_OrderInfo_Product(roip);
if (bll.AddR_OrderInfo_Product(roip))
{
LoadROrderProduct();
txtCount.Text = "";
}
else
{
MessageBox.Show("点菜失败");
}
}
}
/// <summary>
/// 查询该餐桌点的所有的菜
/// </summary>
/// <param name="orderId">订单的id</param>
/// <returns>菜对象集合</returns>
public List <R_OrderInfo_Product> GetProduct(int orderId)
{
string sql = "select ROrderProId,ProName,ProPrice,UnitCount,ProUnit,CatName,R_OrderInfo_Product.SubTime from R_OrderInfo_Product inner join ProductInfo on ProductInfo.ProId=R_OrderInfo_Product.ProId inner join CategoryInfo on ProductInfo.CatId=CategoryInfo.CatId where R_OrderInfo_Product.DelFlag=0 and OrderId=@OrderId";
List <R_OrderInfo_Product> list = new List <R_OrderInfo_Product>();
SQLiteDataReader reader = SqliteHelper.ExecuteReader(sql, new SQLiteParameter("@OrderId", orderId));
if (reader.HasRows)
{
while (reader.Read())
{
R_OrderInfo_Product rop = new R_OrderInfo_Product();
rop.ROrderProId = Convert.ToInt32(reader["ROrderProId"]);
rop.CatName = reader["CatName"].ToString();
rop.ProName = reader["ProName"].ToString();
rop.ProPrice = Convert.ToDecimal(reader["ProPrice"]);
rop.UnitCount = Convert.ToDecimal(reader["UnitCount"]);
rop.ProUnit = reader["ProUnit"].ToString();
rop.SubTime = Convert.ToDateTime(reader["SubTime"]);
rop.ProMoney = rop.ProPrice * rop.UnitCount;//总价
list.Add(rop);
//rop.MONEY=
}
}
return(list);//
}
//双击左侧菜单添加消费项目
private void dgvProduct_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
R_OrderInfo_Product rop = new R_OrderInfo_Product();
//获取当前选中的菜的id
string strId = dgvProduct.SelectedRows[0].Cells[0].Value.ToString();
rop.ProId = Convert.ToInt32(strId);
//获取点菜的数量
rop.UnitCount = string.IsNullOrEmpty(txtCount.Text) || txtCount.Text == "1" ? 1 : Convert.ToInt32(txtCount.Text);//判断是否能转换成功===我省略了.
//订单的id
rop.OrderId = this.ordId;
//把这些数据全部插入到订单和菜单的中间表去.
//其他的数据
rop.DelFlag = 0;
rop.State = 0;
rop.SubTime = DateTime.Now;
R_OrderInfo_ProductBLL bll = new R_OrderInfo_ProductBLL();
//如果菜添加成功了,就要在窗体的右侧显示点的什么菜
if (bll.AddOrderProduct(rop))
{
//刷新右侧的菜单
//添加菜成功后要刷新--窗体加载的时候要刷新. 主界面还要查询
LoadOrderProduct();
txtCount.Text = "";
}
else
{
MessageBox.Show("对不起点菜失败");
}
}
private void dgvProduct_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
int proId = Convert.ToInt32(dgvProduct.SelectedRows[0].Cells[0].Value.ToString());
R_OrderInfo_Product rop = new R_OrderInfo_Product();
rop.OrderId = Convert.ToInt32(labDeskId.Text); //订单的id
rop.ProId = proId; //产品的id
//,,DelFlag,SubTime,State,UnitCount
rop.DelFlag = 0;
rop.SubTime = System.DateTime.Now;
rop.State = 0;
if (string.IsNullOrEmpty(txtCount.Text) || txtCount.Text == "0" || txtCount.Text == "1")
{
rop.UnitCount = 1;
}
else
{
rop.UnitCount = Convert.ToInt32(txtCount.Text);//有异常--坑,
}
R_OrderInfo_ProductBll bll = new R_OrderInfo_ProductBll();
bll.AddROrderInfoProduct(rop);
LoadROrderInfoProductByOrderId(Convert.ToInt32(rop.OrderId));//查询点的什么产品
}
/// <summary>
/// 根据订单的id查询总数量和总金额
/// </summary>
/// <param name="orderId">订单的id</param>
/// <returns></returns>
public R_OrderInfo_Product GetMoneyAndCount(int orderId)
{
string sql = "select count(*),sum(UnitCount*ProPrice)from R_OrderInfo_Product inner join ProductInfo on R_OrderInfo_Product.ProId=ProductInfo.ProId where OrderId=@OrderId";
//释放
SQLiteDataReader sda = SqliteHelper.ExecuteReader(sql, new SQLiteParameter("@OrderId", orderId));
R_OrderInfo_Product r = null;
if (sda.HasRows)
{
while (sda.Read())
{
try
{
r = new R_OrderInfo_Product();
r.CT = Convert.ToInt32(sda[0]);
r.MONEY = Convert.ToDecimal(sda[1]);
}
catch //没有消费时会报错 ，try一下
{
r = new R_OrderInfo_Product();
r.CT = 0;
r.MONEY = 0;
}
}
}
return(r);
}
private void LoadROrderProduct()
{
R_OrderInfo_ProductBLL bll = new R_OrderInfo_ProductBLL();
//计算总金额还有总数量
R_OrderInfo_Product r = bll.GetMoneyAndCount(this.ID);
label1.Text = "亲，您共消费了" + r.MONEY.ToString() + "元";//总金额
}
//关系转对象
private R_OrderInfo_Product RowToROrderInfoProduct(DataRow dr)
{
R_OrderInfo_Product rop = new R_OrderInfo_Product();
rop.CatName = dr["CatName"].ToString();
rop.ProPrice = Convert.ToDecimal(dr["ProPrice"]);
rop.ProUnit = dr["ProUnit"].ToString();
rop.ROrderProId = Convert.ToInt32(dr["ROrderProId"]);
rop.SubTime = Convert.ToDateTime(dr["SubTime"]);
rop.UnitCount = Convert.ToDecimal(dr["UnitCount"]);
rop.ProName = dr["ProName"].ToString();
rop.ProMoney = rop.ProPrice * rop.UnitCount;//这个菜的总价
return(rop);
}
/// <summary>
/// 点菜，添加到订单表中
/// </summary>
/// <param name="rop"></param>
/// <returns></returns>
public int AddROrderInfoProduct(R_OrderInfo_Product rop)
{
var sql = "insert into R_OrderInfo_Product(OrderId,ProId,DelFlag,SubTime,State,UnitCount) values(@OrderId,@ProId,@DelFlag,@SubTime,@State,@UnitCount)";
var ps = new SQLiteParameter[] {
new SQLiteParameter("@OrderId", rop.OrderId),
new SQLiteParameter("@ProId", rop.ProId),
new SQLiteParameter("@DelFlag", rop.DelFlag),
new SQLiteParameter("@SubTime", rop.SubTime),
new SQLiteParameter("@State", rop.State),
new SQLiteParameter("@UnitCount", rop.UnitCount)
};
return(SqliteHelper.ExecuteNonQuery(sql, ps));
}
//加载点的菜
private void LoadROrderProduct()
{
R_OrderInfo_ProductBLL bll = new R_OrderInfo_ProductBLL();
dgvROrderProduct.AutoGenerateColumns = false;
dgvROrderProduct.DataSource = bll.GetROrderInfoProduct(this.ID);
//dgvROrderProduct.SelectedRows[0].Selected = false;
//计算总金额还有总数量
R_OrderInfo_Product r = bll.GetMoneyAndCount(this.ID);
labSumMoney.Text = r.MONEY.ToString(); //总金额
labCount.Text = r.CT.ToString(); //总数量
}
//显示用户点的什么菜
private void LoadOrderProduct()
{
R_OrderInfo_ProductBLL bll = new R_OrderInfo_ProductBLL();
dgvROrderProduct.AutoGenerateColumns = false; //禁止自动生成列
dgvROrderProduct.DataSource = bll.GetProduct(this.ordId);
dgvROrderProduct.SelectedRows[0].Selected = false; //默认不选中
//不为空
R_OrderInfo_Product rop = bll.GetCountAndMoney(this.ordId);
labSumMoney.Text = rop.MONEY.ToString(); //总金额
labCount.Text = rop.CT.ToString(); //点了多少个菜
}
/// <summary>
/// 查询点的菜,根据订单的id
/// </summary>
/// <param name="orderId">订单的id</param>
/// <returns>菜的集合</returns>
public List <R_OrderInfo_Product> GetROrderInfoProduct(int orderId)
{
string sql = "select ROrderProId, ProName,ProPrice,UnitCount,ProUnit,CatName,R_OrderInfo_Product.SubTime from R_OrderInfo_Product inner join CategoryInfo on CategoryInfo.CatId=ProductInfo.CatId inner join ProductInfo on ProductInfo.ProId=R_OrderInfo_Product.ProId where R_OrderInfo_Product.DelFlag=0 and OrderId=@OrderId";
DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@OrderId", orderId));
List <R_OrderInfo_Product> list = new List <R_OrderInfo_Product>();
if (dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
R_OrderInfo_Product rop = RowToROrderInfoProduct(item);
list.Add(rop);
}
}
return(list);
}
/// <summary>
/// 向中间表中插入一条数据,加菜
/// </summary>
/// <param name="roip">对象</param>
/// <returns></returns>
public int AddR_OrderInfo_Product(R_OrderInfo_Product roip)
{
string sql = "insert into R_OrderInfo_Product(OrderId,ProId,DelFlag,SubTime,State,UnitCount) values(@OrderId,@ProId,@DelFlag,@SubTime,@State,@UnitCount)";
SQLiteParameter[] param =
{
new SQLiteParameter("@OrderId", roip.OrderId),
new SQLiteParameter("@ProId", roip.ProId),
new SQLiteParameter("@DelFlag", roip.DelFlag),
new SQLiteParameter("@SubTime", roip.SubTime),
new SQLiteParameter("@State", roip.State),
new SQLiteParameter("@UnitCount", roip.UnitCount)
};
return(SqliteHelper.ExecuteNonquery(sql, param));
}
//显示点的什么菜
/// <summary>
///
/// </summary>
/// <param name="p">订单的id</param>
private void LoadROrderInfoProductByOrderId(int p)
{
R_OrderInfo_ProductBll bll = new R_OrderInfo_ProductBll();
dgvROrderProduct.AutoGenerateColumns = false;
dgvROrderProduct.DataSource = bll.GetROrderInfoProduct(p);
if (dgvROrderProduct.SelectedRows.Count > 0)
{
dgvROrderProduct.SelectedRows[0].Selected = false;
}
//没判断
R_OrderInfo_Product rop = bll.GetMoneyAndCount(p);
labSumMoney.Text = rop.MONEY.ToString();
labCount.Text = rop.CT.ToString();
}
private R_OrderInfo_Product RowToROrderInfo(DataRow dr)
{
var rop = new R_OrderInfo_Product
{
CatName = dr["CatName"].ToString(),
ProName = dr["ProName"].ToString(),
ProPrice = Convert.ToDecimal(dr["ProPrice"]),
ProUnit = dr["ProUnit"].ToString(),
UnitCount = Convert.ToDecimal(dr["UnitCount"]),
SubTime = Convert.ToDateTime(dr["SubTime"]),
ROrderProId = Convert.ToInt32(dr["ROrderProId"])
};
rop.ProMoney = rop.ProPrice * rop.UnitCount;
return(rop);
}
//
/// <summary>
/// 查消费的数量还有总价格
/// </summary>
/// <param name="orderId">订单的id</param>
/// <returns>总数量和总价格</returns>
public R_OrderInfo_Product GetCountAndMoney(int orderId)
{
string sql = "select count(*),sum(UnitCount*ProPrice) from R_OrderInfo_Product inner join ProductInfo on ProductInfo.ProId=R_OrderInfo_Product.ProId where R_OrderInfo_Product.DelFlag=0 and OrderId=@OrderId";
SQLiteDataReader reader = SqliteHelper.ExecuteReader(sql, new SQLiteParameter("@OrderId", orderId));
R_OrderInfo_Product rop = null;
if (reader.HasRows)
{
while (reader.Read())
{
rop = new R_OrderInfo_Product();//这是个大坑
rop.CT = reader.GetInt32(0);
rop.MONEY = reader.GetDecimal(1);
}
}
return(rop);
}
//退菜
private void btnDeleteRorderPro_Click(object sender, EventArgs e)
{
//获取当前选中的id
int id = Convert.ToInt32(dgvROrderProduct.SelectedRows[0].Cells[0].Value);
R_OrderInfo_ProductBLL bll = new R_OrderInfo_ProductBLL();
if (bll.UpdateROrderProduct(id))
{
MessageBox.Show("退菜成功");
LoadROrderProduct();//刷新
R_OrderInfo_Product r = bll.GetMoneyAndCount(this.ID);
labSumMoney.Text = r.MONEY.ToString(); //总金额
labCount.Text = r.CT.ToString(); //总数量
}
else
{
MessageBox.Show("退菜失败");
}
}
private void dgvProduct_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
if (dgvProduct.SelectedRows.Count > 0)
{
//OrderId,ProId,DelFlag,SubTime,State,UnitCount
var rop = new R_OrderInfo_Product();
rop.OrderId = Convert.ToInt32(labOrderId.Text);
rop.ProId = Convert.ToInt32(dgvProduct.SelectedRows[0].Cells[0].Value);
rop.DelFlag = 0;
rop.SubTime = DateTime.Now;
rop.State = 0;
if (txtCount.Text != "")
{
try
{
rop.UnitCount = Convert.ToInt32(txtCount.Text) > 1 ? Convert.ToInt32(txtCount.Text) : 1;
}
catch
{
MessageBox.Show("消费数量输入错误");
return;
}
}
else
{
rop.UnitCount = 1;
}
var bll = new R_OrderInfo_ProductBLL();
bll.AddROrderInfoProduct(rop);
LoadROrderInfoProductByOrderId(Convert.ToInt32(rop.OrderId));
}
else
{
MessageBox.Show("未选中行");
}
}
/// <summary>
/// 点菜，添加到订单表中
/// </summary>
/// <param name="rop"></param>
/// <returns></returns>
public bool AddROrderInfoProduct(R_OrderInfo_Product rop)
{
return(dal.AddROrderInfoProduct(rop) > 0 ? true : false);
}
/// <summary>
/// 添加产品
/// </summary>
/// <param name="rop"></param>
/// <returns></returns>
public bool AddROrderInfoProduct(R_OrderInfo_Product rop)
{
return(rdal.AddROrderInfoProduct(rop) > 0);
}
