/// <summary>
/// 根据桌号查询出订单号
/// </summary>
/// <param name="tableId"></param>
/// <returns></returns>
public int GetOrderIdByTableId(int tableId)
{
string sql = "SELECT OId FROM OrderInfo " +
" WHERE TableId=@TableId AND IsPay=0 ORDER BY OId DESC LIMIT 1";
MySqlParameter p = new MySqlParameter("@TableId", tableId);
return(Convert.ToInt32(MysqlHelper.ExecuteScalar(sql, p)));
}
/// <summary>
/// 添加数据
/// </summary>
/// <param name="hi"></param>
/// <returns></returns>
public int Insert(HallInfo hi)
{
//构造sql语句及参数
string sql = "INSERT INTO HallInfo (HTitle, HIsDelete) VALUES (@HTitle,0) ";
MySqlParameter p = new MySqlParameter("@HTitle", hi.HTitle);
//执行并返回影响行数
return(MysqlHelper.ExecuteNonQuery(sql, p));
}
/// <summary>
/// 软删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int Delete(int id)
{
//构造sql语句及参数
string sql = "UPDATE MemberInfo SET MIsDelete=1 WHERE MId= @MId";
MySqlParameter p = new MySqlParameter("@MId", id);
//执行并返回
return(MysqlHelper.ExecuteNonQuery(sql, p));
}
/// <summary>
/// 根据id进行软删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int Delete(int id)
{
//构造sql语句及参数
string sql = "UPDATE HallInfo SET HIsDelete=1 WHERE HId = @HId";
MySqlParameter p = new MySqlParameter("@HId", id);
//执行并返回影响行数
return(MysqlHelper.ExecuteNonQuery(sql, p));
}
/// <summary>
/// 添加操作
/// </summary>
/// <param name="dti"></param>
/// <returns></returns>
public int Insert(DishTypeInfo dti)
{
//构造sql语句及参数
string sql = "INSERT INTO DishTypeInfo (DTitle, DIsDelete) VALUES (@DTitle, 0)";
MySqlParameter p = new MySqlParameter("@DTitle", dti.DTitle);
//执行并返回
return(MysqlHelper.ExecuteNonQuery(sql, p));
}
/// <summary>
/// 根据id进行软删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int Delete(int id)
{
//构造sql语句及参数
string sql = "UPDATE DishInfo SET DIsDelete=1 WHERE DId = @DId";
MySqlParameter p = new MySqlParameter("@DId", id);
//执行，并返回影响条数
return(MysqlHelper.ExecuteNonQuery(sql, p));
}
/// <summary>
/// 根据id删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int Delete(int id)
{
//构造删除的sql语句
string sql = "delete from ManagerInfo where MId=@MId";
//构造sql语句的参数
MySqlParameter p = new MySqlParameter("@MId", id);
//执行操作，并返回
return(MysqlHelper.ExecuteNonQuery(sql, p));
}
/// <summary>
/// 软删除数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int Delete(int id)
{
//构造软删除sql语句
string sql = "UPDATE MemberTypeInfo SET MIsDelete=1 WHERE MId =@MId";
//构造参数
MySqlParameter p = new MySqlParameter("@MId", id);
//调用操作数据方法
return(MysqlHelper.ExecuteNonQuery(sql, p));
}
/// <summary>
/// 开单操作,插入订单,更新餐桌状态
/// </summary>
/// <param name="tableId"></param>
/// <returns></returns>
public int KaiOrder(int tableId)
{
string sql =
"INSERT INTO OrderInfo (ODate,IsPay,TableId) VALUES (NOW(),0,@TableId)" + //插入订单
"; UPDATE TableInfo SET TIsFree =0 WHERE TId = @TableId"; //更新餐桌状态
MySqlParameter p = new MySqlParameter("@TableId", tableId);
return(MysqlHelper.ExecuteNonQuery(sql, p));
}
/// <summary>
/// 根据订单详表编号oId修改数量
/// </summary>
/// <param name="oId"></param>
/// <param name="count"></param>
/// <returns></returns>
public int UpdateCountByOid(int oId, int count)
{
string sql = "UPDATE OrderDetailInfo SET Count= @Count WHERE OId=@OId";
MySqlParameter[] ps =
{
new MySqlParameter("@Count", count),
new MySqlParameter("@OId", oId),
};
return(MysqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 判断订单中这个菜是否已经存在
/// </summary>
/// <param name="orderId"></param>
/// <param name="dishId"></param>
/// <returns></returns>
public bool HasCai(int orderId, int dishId)
{
string sql = "SELECT COUNT(*) FROM OrderDetailInfo WHERE OrderId=@OrderId AND DishId=@DishId";
MySqlParameter[] ps =
{
new MySqlParameter("@OrderId", orderId),
new MySqlParameter("@DishId", dishId),
};
return(Convert.ToInt32(MysqlHelper.ExecuteScalar(sql, ps)) > 0);
}
/// <summary>
/// 点菜操作
/// </summary>
/// <param name="orderId"></param>
/// <param name="dishId"></param>
/// <returns></returns>
public int DianCai(int orderId, int dishId)
{
string sql = "INSERT INTO OrderDetailInfo (OrderId, DishId, Count) VALUES (@OrderId, @DishId,1)";
MySqlParameter[] ps =
{
new MySqlParameter("@OrderId", orderId),
new MySqlParameter("@DishId", dishId),
};
return(MysqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 将总金额存入订单信息表
/// </summary>
/// <param name="orderId"></param>
/// <param name="totalMoney"></param>
/// <returns></returns>
public int SetOrderMoney(int orderId, decimal totalMoney)
{
string sql = "UPDATE OrderInfo SET OMoney= @OMoney WHERE OId=@OId";
MySqlParameter[] ps =
{
new MySqlParameter("@OMoney", totalMoney),
new MySqlParameter("@OId", orderId),
};
return(MysqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 菜的数量加一
/// </summary>
/// <param name="orderId"></param>
/// <param name="dishId"></param>
/// <returns></returns>
public int CaiJiaYiFeng(int orderId, int dishId)
{
string sql = "UPDATE OrderDetailInfo SET Count= Count+1 WHERE OrderId=@OrderId AND DishId=@DishId";
MySqlParameter[] ps =
{
new MySqlParameter("@OrderId", orderId),
new MySqlParameter("@DishId", dishId),
};
return(MysqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="hi"></param>
/// <returns></returns>
public int Update(HallInfo hi)
{
//构造sql语句及参数
string sql = "UPDATE HallInfo SET HTitle =@HTitle WHERE HId = @HId";
MySqlParameter[] ps =
{
new MySqlParameter("@HId", hi.HId),
new MySqlParameter("@HTitle", hi.HTitle)
};
//执行并返回影响行数
return(MysqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="dti"></param>
/// <returns></returns>
public int Update(DishTypeInfo dti)
{
//构造sql语句及参数
string sql = "UPDATE DishTypeInfo SET DTitle = @DTitle WHERE DId = @DId";
MySqlParameter[] ps =
{
new MySqlParameter("@DTitle", dti.DTitle),
new MySqlParameter("@DId", dti.DId),
};
//执行并返回
return(MysqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 添加数据
/// </summary>
/// <param name="mti"></param>
/// <returns></returns>
public int Insert(MemberTypeInfo mti)
{
//构造添加sql语句
string sql = "INSERT INTO MemberTypeInfo (MTitle,MDiscount,MIsDelete) VALUES(@MTitle,@MDiscount,0)";
//构造参数
MySqlParameter[] ps =
{
new MySqlParameter("@MTitle", mti.MTitle),
new MySqlParameter("@MDiscount", mti.MDiscount),
};
//调用操作数据方法
return(MysqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 插入数据
/// </summary>
/// <param name="mi">ManagerInfo类型的对象</param>
/// <returns></returns>
public int Insert(ManagerInfo mi)
{
//构造insert语句
string sql = "insert into ManagerInfo(MName,MPwd,MType) values(@MName,@MPwd,@MType)";
//构造sql语句的参数
MySqlParameter[] ps = //使用数组初始化器
{
new MySqlParameter("@MName", mi.MName),
new MySqlParameter("@MPwd", Md5Helper.EncryptString(mi.MPwd)),
new MySqlParameter("@MType", mi.MType)
};
//执行插入操作
return(MysqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 根据订单，计算订单总金额
/// </summary>
/// <param name="orderId"></param>
/// <returns></returns>
public decimal GetTotalMoneyByOrderId(int orderId)
{
string sql = "SELECT SUM(di.DPrice*odi.Count) FROM OrderDetailInfo AS odi " +
" INNER JOIN DishInfo AS di " +
" ON odi.DishId=di.DId " +
" WHERE odi.OrderId=@OrderId";
MySqlParameter p = new MySqlParameter("@OrderId", orderId);
var result = MysqlHelper.ExecuteScalar(sql, p);
if (result != DBNull.Value)
{
return(Convert.ToDecimal(result));
}
return(0);
}
/// <summary>
/// 添加数据
/// </summary>
/// <param name="mi"></param>
/// <returns></returns>
public int Insert(MemberInfo mi)
{
//构造sql语句及参数
string sql = "INSERT INTO MemberInfo (MTypeId, MName, MPhone, MMoney, MIsDelete) VALUES (@MTypeId, @MName, @MPhone, @MMoney, 0)";
MySqlParameter[] ps =
{
new MySqlParameter("@MTypeId", mi.MTypeId),
new MySqlParameter("@MName", mi.MName),
new MySqlParameter("@MPhone", mi.MPhone),
new MySqlParameter("@MMoney", mi.MMoney),
};
//执行并返回
return(MysqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="ti"></param>
/// <returns></returns>
public int Update(TableInfo ti)
{
//构造sql语句及参数
string sql = "UPDATE TableInfo SET TTitle = @TTitle, THallId = @THallId, TIsFree = @TIsFree WHERE TId = @TId ";
MySqlParameter[] ps =
{
new MySqlParameter("@TId", ti.TId),
new MySqlParameter("@TTitle", ti.TTitle),
new MySqlParameter("@THallId", ti.THallId),
new MySqlParameter("TIsFree", ti.TIsFree),
};
//执行并返回
return(MysqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 添加数据
/// </summary>
/// <param name="ti"></param>
/// <returns></returns>
public int Insert(TableInfo ti)
{
//构造sql语句及参数
string sql = "INSERT INTO TableInfo (TTitle, THallId, TIsFree, TIsDelete ) VALUES " +
"(@TTitle, @THallId, @TIsFree, 0)";
MySqlParameter[] ps =
{
new MySqlParameter("@TTitle", ti.TTitle),
new MySqlParameter("@THallId", ti.THallId),
new MySqlParameter("TIsFree", ti.TIsFree),
};
//执行并返回
return(MysqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="mti"></param>
/// <returns></returns>
public int Update(MemberTypeInfo mti)
{
//构造修改sql语句
string sql = "UPDATE MemberTypeInfo SET MTitle=@MTitle,MDiscount=@MDiscount WHERE MId =@MId";
//构造参数
MySqlParameter[] ps =
{
new MySqlParameter("@MTitle", mti.MTitle),
new MySqlParameter("@MDiscount", mti.MDiscount),
new MySqlParameter("@MId", mti.MId),
};
//调用操作数据方法
return(MysqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 添加数据
/// </summary>
/// <param name="di"></param>
/// <returns></returns>
public int Insert(DishInfo di)
{
//构造sql语句及对应参数
string sql = "INSERT INTO DishInfo (DTitle, DTypeId, DPrice, DChar, DIsDelete) VALUES (@DTitle, @DTypeId, @DPrice, @DChar, 0)";
MySqlParameter[] ps =
{
new MySqlParameter("@DTitle", di.DTitle),
new MySqlParameter("@DTypeId", di.DTypeId),
new MySqlParameter("@DPrice", di.DPrice),
new MySqlParameter("@DChar", di.DChar),
};
//执行，并返回影响条数
return(MysqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="di"></param>
/// <returns></returns>
public int Update(DishInfo di)
{
//构造sql语句及参数
string sql = "UPDATE DishInfo SET DTitle = @DTitle, DTypeId = @DTypeId, DPrice = @DPrice, DChar = @DChar WHERE DId = @DId";
MySqlParameter[] ps =
{
new MySqlParameter("@DId", di.DId),
new MySqlParameter("@DTitle", di.DTitle),
new MySqlParameter("@DTypeId", di.DTypeId),
new MySqlParameter("@DPrice", di.DPrice),
new MySqlParameter("@DChar", di.DChar),
};
//执行，并返回影响条数
return(MysqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="mi"></param>
/// <returns></returns>
public int Update(MemberInfo mi)
{
//构造sql语句及参数
string sql = "UPDATE MemberInfo SET MTypeId = @MTypeId, MName = @MName, MPhone = @MPhone, MMoney = @MMoney WHERE MId= @MId";
MySqlParameter[] ps =
{
new MySqlParameter("@MId", mi.MId),
new MySqlParameter("@MTypeId", mi.MTypeId),
new MySqlParameter("@MName", mi.MName),
new MySqlParameter("@MPhone", mi.MPhone),
new MySqlParameter("@MMoney", mi.MMoney),
};
//执行并返回
return(MysqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 获取列表
/// </summary>
/// <returns></returns>
public List <DishTypeInfo> GetList()
{
//构造查询sql语句
string sql = "SELECT DId, DTitle, DIsDelete FROM DishTypeInfo WHERE DIsDelete=0";
//执行查询，取得表格
DataTable dt = MysqlHelper.GetDataTable(sql);
//定义列表，遍历表格，填充列表并返回
List <DishTypeInfo> list = new List <DishTypeInfo>();
foreach (DataRow row in dt.Rows)
{
list.Add(new DishTypeInfo()
{
DId = Convert.ToInt32(row["DId"]),
DTitle = row["DTitle"].ToString()
});
}
return(list);
}
/// <summary>
/// 列表查询
/// </summary>
/// <param name="dic"></param>
/// <returns></returns>
public List <MemberInfo> GetList(Dictionary <string, string> dic)
{
//构造查询sql语句，连接查询得到会员类型的名称
string sql = "SELECT mi.MId,mi.MName,mti.MTitle AS MTypeTitle,mi.MPhone,mi.MMoney,mi.MTypeId,mti.MDiscount " +
" FROM MemberInfo AS mi " +
" INNER JOIN MemberTypeInfo AS mti " +
" ON mi.MTypeId=mti.MId " +
" WHERE mi.MIsDelete=0";
//拼接查询条件
List <MySqlParameter> listP = new List <MySqlParameter>();
if (dic.Count > 0)
{
foreach (var pair in dic)
{
//sql+=" AND "+pair.Key+" LIKE '%"+pair.Value+"%'";
//写成参数化，防注入
sql += " AND mi." + pair.Key + " LIKE @" + pair.Key;
listP.Add(new MySqlParameter("@" + pair.Key, "%" + pair.Value + "%"));
}
}
//查询排序
sql += " ORDER BY mi.MId";
//执行查询
DataTable dt = MysqlHelper.GetDataTable(sql, listP.ToArray());
//定义list，完成转存
List <MemberInfo> list = new List <MemberInfo>();
foreach (DataRow row in dt.Rows)
{
list.Add(new MemberInfo()
{
MId = Convert.ToInt32(row["MId"]),
MName = row["MName"].ToString(),
MPhone = row["MPhone"].ToString(),
MMoney = Convert.ToDecimal(row["MMoney"]),
MTypeId = Convert.ToInt32(row["MTypeId"]),
MTypeTitle = row["MTypeTitle"].ToString(),
MDiscount = Convert.ToDecimal(row["MDiscount"])
});
}
return(list);
}
/// <summary>
/// 查询未删除数据
/// </summary>
/// <returns></returns>
public List <MemberTypeInfo> GetList()
{
//构造查询sql语句
string sql = "SELECT MID,MTitle,MDiscount,MIsDelete FROM MemberTypeInfo WHERE MIsDelete=0";
//执行查询,返回表格
DataTable dt = MysqlHelper.GetDataTable(sql);
//遍历表格,填充列表并返回
List <MemberTypeInfo> list = new List <MemberTypeInfo>();
foreach (DataRow row in dt.Rows)
{
list.Add(new MemberTypeInfo()
{
MId = Convert.ToInt32(row["MId"]),
MTitle = row["MTitle"].ToString(),
MDiscount = Convert.ToDecimal(row["MDiscount"])
});
}
return(list);
}
/// <summary>
/// 获取列表
/// </summary>
/// <returns></returns>
public List <HallInfo> GetList()
{
//构造sql语句
string sql = "SELECT HId, HTitle, HIsDelete FROM HallInfo WHERE HIsDelete=0";
//执行,并得到表格
DataTable dt = MysqlHelper.GetDataTable(sql);
//定义列表对象,遍历表格,填充列表并返回
List <HallInfo> list = new List <HallInfo>();
foreach (DataRow row in dt.Rows)
{
list.Add(new HallInfo()
{
HId = Convert.ToInt32(row["HId"]),
HTitle = row["HTitle"].ToString()
});
}
return(list);
}
