/// <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);
}
/// <summary>
/// 新增修改方法
/// </summary>
/// <param name="temp"></param>
/// <param name="sql"></param>
/// <param name="mem"></param>
/// <returns></returns>
public int AddAndUpdateMemmberInfo(int temp, string sql, MemmberInfo mem)
{
SQLiteParameter[] ps =
{
new SQLiteParameter("@MemName", mem.MemName),
new SQLiteParameter("@MemMobilePhone", mem.MemMobilePhone),
new SQLiteParameter("@MemAddress", mem.MemAddress),
new SQLiteParameter("@MemType", mem.MemType),
new SQLiteParameter("@MemNum", mem.MemNum),
new SQLiteParameter("@MemGender", mem.MemGender),
new SQLiteParameter("@MemDiscount", mem.MemDiscount),
new SQLiteParameter("@MemMoney", mem.MemMoney),
new SQLiteParameter("@MemIntegral", mem.MemIntegral),
new SQLiteParameter("@MemEndServerTime", mem.MemEndServerTime),
new SQLiteParameter("@MemBirthdaty", mem.MemBirthdaty)
};
List <SQLiteParameter> list = new List <SQLiteParameter>();
list.AddRange(ps);
if (temp == 1)
{
list.Add(new SQLiteParameter("@DelFlag", mem.DelFlag));
list.Add(new SQLiteParameter("@SubTime", mem.SubTime));
}
else if (temp == 2)
{
list.Add(new SQLiteParameter("@MemmberId", mem.MemmberId));
}
return(Sqlitehelper.ExecuteNonQuery(sql, list.ToArray()));
}
/// <summary>
/// 新增和修改产品总方法
/// </summary>
/// <param name="cat"></param>
/// <returns></returns>
public int AddAndUpdateProductInfo(int temp, string sql, ProductInfo pro)
{
SQLiteParameter[] ps =
{
new SQLiteParameter("@CatId", pro.CatId),
new SQLiteParameter("@ProName", pro.ProName),
new SQLiteParameter("@ProNum", pro.ProNum),
new SQLiteParameter("@ProPrice", pro.ProPrice),
new SQLiteParameter("@ProSpell", pro.ProSpell),
new SQLiteParameter("@ProStock", pro.ProStock),
new SQLiteParameter("@ProUnit", pro.ProUnit),
new SQLiteParameter("@Remark", pro.Remark),
new SQLiteParameter("@ProCost", pro.ProCost)
};
List <SQLiteParameter> list = new List <SQLiteParameter>();
list.AddRange(ps);
if (temp == 3)
{
list.Add(new SQLiteParameter("@DelFlag", pro.DelFlag));
list.Add(new SQLiteParameter("@SubTime", pro.SubTime));
list.Add(new SQLiteParameter("@SubBy", pro.SubBy));
}
else if (temp == 4)
{
list.Add(new SQLiteParameter("@ProId", pro.ProId));
}
return(Sqlitehelper.ExecuteNonQuery(sql, list.ToArray()));
}
/// <summary>
/// 根据ID查询产品对象信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ProductInfo GetProductInfoById(int id)
{
string sql = "select * from ProductInfo where Delflag=0 and ProId=@ProId";
DataTable dt = Sqlitehelper.ExecuteTable(sql, new SQLiteParameter("@ProId", id));
ProductInfo pro = new ProductInfo();
if (dt.Rows.Count > 0)
{
pro = RowToProductInfo(dt.Rows[0]);
}
return(pro);
}
/// <summary>
/// 根据用户性别查询用户信息
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public MemmberInfo GetMemmberInfoByMemmberName(string name)
{
string sql = "select * from MemmberInfo where DelFlag=0 and MemName=@MemName";
DataTable dt = Sqlitehelper.ExecuteTable(sql, new SQLiteParameter("@MemName", name));
MemmberInfo mem = null;
if (dt.Rows.Count > 0)
{
mem = RowToMemmberInfo(dt.Rows[0]);
}
return(mem);
}
/// <summary>
/// 根据用户编号查询具体信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public MemmberInfo GetMemmberInfoByMemmberId(int id)
{
string sql = "select * from MemmberInfo where DelFlag=0 and MemmberId=" + id;
DataTable dt = Sqlitehelper.ExecuteTable(sql);
MemmberInfo mem = null;
if (dt.Rows.Count > 0)
{
mem = RowToMemmberInfo(dt.Rows[0]);
}
return(mem);
}
/// <summary>
/// 根据类别的id查询类别对象
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public CategoryInfo GetCategoryInfoById(int id)
{
string sql = "select * from CategoryInfo where DelFlag=0 and CatId=" + id;
//string sql = "select * from CategoryInfo where DelFlag=0 and CatId="+id;
CategoryInfo ct = null;
DataTable dt = Sqlitehelper.ExecuteTable(sql);
if (dt.Rows.Count > 0)
{
ct = RowToCategoryInfo(dt.Rows[0]);
}
return(ct);
}
/// <summary>
/// 查询所有的商品类别
/// </summary>
/// <param name="delFlag"></param>
/// <returns></returns>
public List <CategoryInfo> GetAllCategoryInfoByDelFlag(int delFlag)
{
string sql = "select CatId,CatName,CatNum,Remark from CategoryInfo where DelFlag=" + delFlag;
DataTable dt = Sqlitehelper.ExecuteTable(sql);
List <CategoryInfo> list = new List <CategoryInfo>();
foreach (DataRow dr in dt.Rows)
{
CategoryInfo cat = RowToCategoryInfo(dr);
list.Add(cat);
}
return(list);
}
/// <summary>
/// 更新订单
/// </summary>
/// <param name="order"></param>
/// <returns></returns>
public int UpdateOrderInfoMoney(OrderInfo order)
{
string sql = "update OrderInfo set OrderState=2,OrderMemId=@OrderMemId,EndTime=@EndTime,OrderMoney=@OrderMoney,DisCount=@DisCount where OrderId=@OrderId";
SQLiteParameter[] ps =
{
new SQLiteParameter("@OrderMemId", order.OrderMemId),
new SQLiteParameter("@EndTime", order.EndTime),
new SQLiteParameter("@OrderMoney", order.OrderMoney),
new SQLiteParameter("@DisCount", order.DisCount),
new SQLiteParameter("@OrderId", order.OrderId)
};
return(Sqlitehelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 根据房间的ID查询该房间下所有餐桌
/// </summary>
/// <param name="roomId"></param>
/// <returns></returns>
public List <DeskInfo> GetAllDeskInfoByRoomId(int roomId)
{
List <DeskInfo> list = new List <DeskInfo>();
string sql = "select * from DeskInfo where DelFlag=0 and RoomId=@RoomId";
DataTable dt = Sqlitehelper.ExecuteTable(sql, new SQLiteParameter("@RoomId", roomId));
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToDeskInfo(dr));
}
}
return(list);
}
/// <summary>
/// 添加产品
/// </summary>
/// <param name="rop"></param>
/// <returns></returns>
public int AddROrderInfoProduct(R_OrderInfo_Product rop)
{
string sql = "insert into R_OrderInfo_Product(OrderId,ProId,DelFlag,SubTime,State,UnitCount) values (@OrderId,@ProId,@DelFlag,@SubTime,@State,@UnitCount)";
SQLiteParameter[] ps =
{
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));
}
public UserInfo IsLoginBtLoginName(string loginName)
{
string sql = "select * from UserInfo where LoginUserName=@LoginUserName";
DataTable dt = Sqlitehelper.ExecuteTable(sql, new SQLiteParameter("@LoginUserName", loginName));
UserInfo user = null;
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
user = RowToUserInfo(dr);
}
}
return(user);
}
/// <summary>
/// 获取所有的房间对象
/// </summary>
/// <param name="delFlag"></param>
/// <returns></returns>
public List <RoomInfo> GetAllRoomInfoByDelFlag(int delFlag)
{
List <RoomInfo> list = new List <RoomInfo>();
string sql = "select * from RoomInfo where DelFlag=" + delFlag;
DataTable dt = Sqlitehelper.ExecuteTable(sql);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToRoomInfo(dr));
}
}
return(list);
}
/// <summary>
/// 添加一个订单
/// </summary>
/// <param name="order"></param>
/// <returns></returns>
public object AddOrderInfo(OrderInfo order)
{
string sql = "insert into OrderInfo(SubTime,Remark,Orderstate,DelFlag,SubBy,OrderMoney)values(@SubTime,@Remark,@Orderstate,@DelFlag,@SubBy,@OrderMoney);select last_insert_rowid();";
SQLiteParameter[] ps =
{
new SQLiteParameter("@SubTime", order.SubTime),
new SQLiteParameter("@Remark", order.Remark),
new SQLiteParameter("@Orderstate", order.OrderState),
new SQLiteParameter("@DelFlag", order.DelFlag),
new SQLiteParameter("@SubBy", order.SubBy),
new SQLiteParameter("@OrderMoney", order.OrderMoney)
};
return(Sqlitehelper.ExecuteScalar(sql, ps));
}
/// <summary>
/// 根据产品编号模糊查询
/// </summary>
/// <param name="proNum"></param>
/// <returns></returns>
public List <ProductInfo> GetProductInfoByProNum(string proNum)
{
List <ProductInfo> list = new List <ProductInfo>();
string sql = "select * from ProductInfo where DelFlag=0 and ProNum like @ProNum";
DataTable dt = Sqlitehelper.ExecuteTable(sql, new SQLiteParameter("@ProNum", "%" + proNum + "%"));
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToProductInfo(dr));
}
}
return(list);
}
/// <summary>
/// 根据商品种类得到属于该商品分类下的所有产品
/// </summary>
/// <param name="catId"></param>
/// <returns></returns>
public List <ProductInfo> GetProductInfoByCatid(int catId)
{
List <ProductInfo> list = new List <ProductInfo>();
string sql = "select * from ProductInfo where DelFlag=0 and CatId=@CatId";
DataTable dt = Sqlitehelper.ExecuteTable(sql, new SQLiteParameter("@CatId", catId));
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
ProductInfo ps = RowToProductInfo(dr);
list.Add(ps);
}
}
return(list);
}
/// <summary>
/// 查询所有的产品
/// </summary>
/// <param name="delFlag"></param>
/// <returns></returns>
public List <ProductInfo> GetAllProductInfoByDelFlag(int delFlag)
{
string sql = "select ProId,CatId,ProName,ProCost,ProSpell,ProPrice,ProUnit,Remark,ProStock,ProNum from ProductInfo where DelFlag=" + delFlag;
List <ProductInfo> list = new List <ProductInfo>();
DataTable dt = Sqlitehelper.ExecuteTable(sql);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToProductInfo(dr));
}
}
return(list);
}
//根据delflag会员是否删除0未删除1已经删除
/// <summary>
/// 把表给全部导出来
/// </summary>
/// <param name="delFlag"></param>
/// <returns></returns>
public List <MemmberInfo> GetAllMemmberInfoByDelflag(int delFlag)
{
string sql = "select MemmberId,MemName,MemMobilePhone,MemAddress,MemType,MemNum,MemGender,MemDiscount,MemMoney,SubTime,MemIntegral,MemEndServerTime,MemBirthdaty from MemmberInfo where DelFlag=@DelFlag";
DataTable dt = Sqlitehelper.ExecuteTable(sql, new SQLiteParameter("@DelFlag", delFlag));
List <MemmberInfo> list = new List <MemmberInfo>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
MemmberInfo mem = RowToMemmberInfo(dr);
list.Add(mem);
}
}
return(list);
}
/// <summary>
/// 增加房间
/// </summary>
/// <param name="room"></param>
/// <returns></returns>
public int AddRoom(RoomInfo room)
{
string sql = "insert into RoomInfo(RoomName,RoomType,RoomMinimunConsume,RoomMaxConsumer,IsDefault,DelFlag,SubTime,SubBy) values(@RoomName,@RoomType,@RoomMinimunConsume,@RoomMaxConsumer,@IsDefault,@DelFlag,@SubTime,@SubBy)";
SQLiteParameter[] ps =
{
new SQLiteParameter("@RoomName", room.RoomName),
new SQLiteParameter("@RoomType", room.RoomType),
new SQLiteParameter("@RoomMinimunConsume", room.RoomMinimunConsume),
new SQLiteParameter("@RoomMaxConsumer", room.RoomMaxConsumer),
new SQLiteParameter("@IsDefault", room.IsDefault),
new SQLiteParameter("@DelFlag", room.DelFlag),
new SQLiteParameter("@SubTime", room.SubTime),
new SQLiteParameter("@SubBy", room.SubBy)
};
return(Sqlitehelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 查询所有会员等级
/// </summary>
/// <param name="delFlag"></param>会员等级
/// <returns>会员等级所有集合</returns>
public List <MemmberType> GetAllMemmberTypeByDelflag(int delFlag)
{
string sql = "select MemType,MemTpName from MemmberType where delFlag=" + delFlag;
DataTable dt = Sqlitehelper.ExecuteTable(sql);
List <MemmberType> list = new List <MemmberType>();
MemmberType memtp = new MemmberType();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
memtp = RowToMemmberType(dr);
list.Add(memtp);
}
}
return(list);
}
/// <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 ProductInfo on R_OrderInfo_Product.ProId=ProductInfo.ProId inner join CategoryInfo on ProductInfo.CatId=CategoryInfo.CatId where OrderId=@OrderId and R_OrderInfo_Product.DelFlag=0";
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 dr in dt.Rows)
{
list.Add(RowToROrderInfoProduct(dr));
}
}
return(list);
}
/// <summary>
/// 新增和修改商品总方法
/// </summary>
/// <param name="cat"></param>
/// <returns></returns>
public int AddAndUpdateCategoryInfo(int temp, string sql, CategoryInfo cat)
{
SQLiteParameter[] ps =
{
new SQLiteParameter("@CatName", cat.Catname),
new SQLiteParameter("@CatNum", cat.Catnum),
new SQLiteParameter("@Remark", cat.Remark)
};
List <SQLiteParameter> list = new List <SQLiteParameter>();
list.AddRange(ps);
if (temp == 1)
{
list.Add(new SQLiteParameter("@DelFlag", cat.Delflag));
list.Add(new SQLiteParameter("@SubTime", cat.Subtime));
list.Add(new SQLiteParameter("@SubBy", cat.Subby));
}
else if (temp == 2)
{
list.Add(new SQLiteParameter("@CatId", cat.Catid));
}
return(Sqlitehelper.ExecuteNonQuery(sql, list.ToArray()));
}
/// <summary>
/// 根据拼音或者编号查询产品
/// </summary>
/// <param name="num">可以是拼音，可以是编号</param>
/// <param name="temp">1---拼音，2---编号</param>
/// <returns></returns>
public List <ProductInfo> GetProductInfoBySpellOrNum(string num, int temp)
{
string sql = "select * from ProductInfo where DelFlag=0";
if (temp == 1)//拼音
{
sql += " and ProSpell like @ProSpell";
}
else if (temp == 2)
{
sql += " and ProNum like @ProSpell";
}
List <ProductInfo> list = new List <ProductInfo>();
DataTable dt = Sqlitehelper.ExecuteTable(sql, new SQLiteParameter("@ProSpell", "%" + num + "%"));
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToProductInfo(dr));
}
}
return(list);
}
/// <summary>
/// 退菜
/// </summary>
/// <param name="rOrderId"></param>
/// <returns></returns>
public int SoftDeleteROrderProName(int rOrderId)
{
string sql = "update R_OrderInfo_Product set DelFlag=1 where ROrderProId=@ROrderProId";
return(Sqlitehelper.ExecuteNonQuery(sql, new SQLiteParameter("@ROrderProId", rOrderId)));
}
/// <summary>
/// 软删除产品
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int SoftDeleteProductInfoByProId(int id)
{
string sql = "update ProductInfo set Delflag=1 Where ProId=@ProId";
return(Sqlitehelper.ExecuteNonQuery(sql, new SQLiteParameter("@ProId", id)));
}
/// <summary>
/// 更新订单表中的金额信息
/// </summary>
/// <param name="orderId"></param>
/// <param name="money"></param>
/// <returns></returns>
public int UpdateMoney(int orderId, decimal money)
{
string sql = "update OrderInfo set OrderMoney=@OrderMoney where OrderId=@OrderId and DelFlag=0";
return(Sqlitehelper.ExecuteNonQuery(sql, new SQLiteParameter("@OrderMoney", money), new SQLiteParameter("@OrderId", orderId)));
}
/// <summary>
/// 根据餐桌ID查找该餐桌正在使用的订单
/// </summary>
/// <param name="deskId"></param>
/// <returns></returns>
public object GetOrderIdByDeskId(int deskId)
{
string sql = "select OrderInfo.OrderId from R_Order_Desk inner join OrderInfo on R_Order_Desk.OrderId=OrderInfo.OrderId where OrderInfo.OrderState=1 and DeskId=@DeskId";
return(Sqlitehelper.ExecuteScalar(sql, new SQLiteParameter("@DeskId", deskId)));
}
/// <summary>
/// 软删除商品种类
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int SoftDeleteCategoryInfoByCatId(int id)
{
string sql = "update CategoryInfo set Delflag=1 Where CatId=@CatId";
return(Sqlitehelper.ExecuteNonQuery(sql, new SQLiteParameter("@CatId", id)));
}
/// <summary>
/// 添加一个中间表
/// </summary>
/// <param name="rod"></param>
/// <returns></returns>
public int AddOrderDesk(R_Order_Desk rod)
{
string sql = "insert into R_Order_Desk(OrderId,DeskId)values(@OrderId,@DeskId)";
return(Sqlitehelper.ExecuteNonQuery(sql, new SQLiteParameter("@OrderId", rod.OrderId), new SQLiteParameter("@DeskId", rod.DeskId)));
}
/// <summary>
/// 更改餐桌状态
/// </summary>
/// <param name="deskId"></param>
/// <param name="state"></param>
/// <returns></returns>
public int UpdateDeskStateByDeskId(int deskId, int state)
{
string sql = "update DeskInfo set DeskState=@DeskState where DelFlag=0 and DeskId=@DeskId";
return(Sqlitehelper.ExecuteNonQuery(sql, new SQLiteParameter("@DeskState", state), new SQLiteParameter("@DeskId", deskId)));
}