/// <summary>
/// 通过产品数量或者拼音查询产品信息
/// </summary>
/// <param name="num">拼音或者数量</param>
/// <param name="flag">1->拼音; 2->数量</param>
/// <returns></returns>
public List <ProductInfo> GetProductInfoBySpellNum(string num, int flag)
{
var sql = "select * from ProductInfo where DelFlag=0";
if (flag == 1)//拼音
{
sql += " and ProSpell like @ProSpell";
}
else if (flag == 2)//v
{
sql += " and ProNum like @ProSpell";
}
var dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@ProSpell", "%" + num + "%"));
var list = new List <ProductInfo>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToProductInfo(dr));
}
}
return(list);
}
/// <summary>
/// 通过会员名称和编号获取会员信息
/// </summary>
/// <param name="name">MemName or MemNum</param>
/// <param name="temp">1->会员名称; 2->会员编号</param>
/// <returns></returns>
public List <MemberInfo> GetMemberInfoBySerach(string name, int temp)
{
var sql = "select * from MemmberInfo where DelFlag=0 ";
if (temp == 1)
{
sql += "and MemName like @name";
}
else if (temp == 2)
{
sql += "and MemNum like @name";
}
var dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@name", "%" + name + "%"));
var list = new List <MemberInfo>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToMemberInfo(dr));
}
}
return(list);
}
/// <summary>
/// 根据房间Id查询该房间信息
/// </summary>
/// <param name="roomId">房间Id</param>
/// <returns>房间对象</returns>
public RoomInfo GetRoomInfoByRoomId(int roomId)
{
string sql = "select * from RoomInfo where RoomId=@RoomId and DelFlag=0";
DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@RoomId", roomId));
RoomInfo room = null;
if (dt.Rows.Count > 0)
{
room = RowToRoomInfo(dt.Rows[0]);
}
return(room);
}
/// <summary>
/// 根据商品类别id查询商品数据
/// </summary>
/// <param name="catId">商品id</param>
/// <returns>商品类别对象</returns>
public CategoryInfo GetCategoryInfoByCatId(int catId)
{
string sql = "select * from CategoryInfo where DelFlag=0 and CatId=@CatId";
DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@CatId", catId));
CategoryInfo cat = null;
if (dt.Rows.Count > 0)
{
cat = RowToCategoryInfo(dt.Rows[0]);
}
return(cat);
}
/// <summary>
/// 根据会员的Id查询该会员的信息
/// </summary>
/// <param name="memmberId">会员Id</param>
/// <returns>会员对象</returns>
public MemmberInfo GetMemmberInfoByMemmberId(int memmberId)
{
string sql = "select * from MemmberInfo where MemmberId=@MemmberId ";
DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@MemmberId", memmberId));
MemmberInfo mem = null;
if (dt.Rows.Count > 0)
{
mem = RowToMemmberInfo(dt.Rows[0]);
}
return(mem);
}
/// <summary>
/// 根据id获取菜的对象的数据
/// </summary>
/// <param name="id">菜的id</param>
/// <returns></returns>
public ProductInfo GetProductByProId(int id)
{
string sql = "select * from ProductInfo where DelFlag=0 and ProId=@ProId";
ProductInfo pro = null;
DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@ProId", id));
if (dt.Rows.Count > 0)
{
pro = RowToProductInfo(dt.Rows[0]);
}
return(pro);
}
//去数据库中查询数据
public UserInfo GetUserPwdByLoginName(string loginName)
{
string sql = "select * from UserInfo where LoginUserName=@LoginUserName and DelFlag=0";
DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@LoginUserName", loginName));
//dt是否有行
UserInfo user = null;
if (dt.Rows.Count > 0)
{
user = RowToUserInfo(dt.Rows[0]);
}
return(user);
}
/// <summary>
/// 通过ProductId获取产品信息
/// </summary>
/// <param name="proId"></param>
/// <returns></returns>
public ProductInfo GetProductInfoById(int proId)
{
var sql = "select * from ProductInfo where ProId=" + proId;
var dt = SqliteHelper.ExecuteTable(sql);
var proi = new ProductInfo();
if (dt.Rows.Count > 0)
{
proi = RowToProductInfo(dt.Rows[0]);
}
return(proi);
}
//我的错误代码，返回了一个Desk的列表
//public List<DeskInfo> GetDeskInfoByDeskId(int deskId)
//{
// string sql = "select * from DeskInfo where DeskId=@DeskId and DelFlag=0";
// DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@DeskId", deskId));
// List<DeskInfo> list = new List<DeskInfo>();
// DeskInfo desk = new DeskInfo();
// if (dt.Rows.Count > 0)
// {
// desk = RowToDeskInfo(dt.Rows[0]);
// }
// list.Add(desk);
// return list;
//}
/// <summary>
/// 根据用户ID获取信息
/// </summary>
/// <param name="deskId"></param>
/// <returns></returns>
public DeskInfo GetDeskInfoByDeskId(int deskId)
{
string sql = "select * from DeskInfo where DeskId=@DeskId and DelFlag=0";
DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@DeskId", deskId));
DeskInfo desk = new DeskInfo();
if (dt.Rows.Count > 0)
{
desk = RowToDeskInfo(dt.Rows[0]);
}
return(desk);
}
/// <summary>
/// 根据登录名 查询数据库
/// </summary>
/// <param name="loginName"></param>
/// <returns>返回的是对象</returns>
public UserInfo LoginByLoginName(string loginName)
{
string sql = "select * from UserInfo where DelFlag=0 and LoginUserName=@loginName";
DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@loginName", loginName));
UserInfo user = null;
if (dt.Rows.Count > 0)//有行
{
user = RowToUserInfo(dt.Rows[0]);
}
return(user);
}
/// <summary>
/// 根据id查对象
/// </summary>
/// <param name="id">会员的id</param>
/// <returns>会员的对象</returns>
public MemberInfo GetMemberInfoByMemberId(int id)
{
var sql = "select * from MemmberInfo where DelFlag=0 and MemmberId=" + id;
var dt = SqliteHelper.ExecuteTable(sql);
MemberInfo mem = null;
if (dt.Rows.Count > 0)
{
mem = RowToMemberInfo(dt.Rows[0]);
}
return(mem);
}
/// <summary>
/// 查询所有未删除的房间
/// </summary>
/// <param name="delFlag">删除标识</param>
/// <returns>返回一个列表</returns>
public List <DeskInfo> GetAllDeskInfoByDelFlag(int delFlag)
{
string sql = "select * from DeskInfo Where DelFlag=@delFlag";
DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@delFlag", delFlag));
List <DeskInfo> list = new List <DeskInfo>();
if (dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
DeskInfo desk = RowToDeskInfo(item);
list.Add(desk);
}
}
return(list);
}
/// <summary>
/// 查询所有没有被删除的会员类型
/// </summary>
/// <returns>会员类型集合</returns>
public List <MemmberType> GetAllMemmberTypeByDelFlag()
{
string sql = "select MemType,MemTpName from MemmberType where DelFlag=0";
DataTable dt = SqliteHelper.ExecuteTable(sql);
List <MemmberType> list = new List <MemmberType>();
if (dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
MemmberType memtp = RowToMemmberType(item);
list.Add(memtp);
}
}
return(list);
}
/// <summary>
/// 根据房间的ID查询该房间下的所有餐桌
/// </summary>
/// <param name="roomId">房间的id</param>
/// <returns>餐桌对象</returns>
public List <DeskInfo> GetDeskInfoByRoomId(int roomId)
{
string sql = "select * from DeskInfo where DelFlag=0 and RoomId=@RoomId";
List <DeskInfo> list = new List <DeskInfo>();
DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@RoomId", roomId));
if (dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
DeskInfo dk = RowToDeskInfo(item);
list.Add(dk);
}
}
return(list);
}
/// <summary>
/// 查询所有没删除的商品
/// </summary>
/// <param name="delflag"></param>
/// <returns></returns>
public List <CategoryInfo> GetAllCategoryInfoByDelFlag(int delflag)
{
string sql = "select * from categoryinfo where DelFlag=@DelFlag";
DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@DelFlag", delflag));
List <CategoryInfo> list = new List <CategoryInfo>();
if (dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
CategoryInfo ct = RowToCategoryInfo(item);
list.Add(ct);
}
}
return(list);
}
/// <summary>
/// 通过删除标识，获取全部房间信息
/// </summary>
public List <RoomInfo> GetAllRoomInfoByDelFlag(int delFlag)
{
var sql = "select * from RoomInfo where DelFlag=" + delFlag;
var list = new List <RoomInfo>();
var dt = SqliteHelper.ExecuteTable(sql);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToRoomInfo(dr));
}
}
return(list);
}
/// <summary>
/// 通过房间标识，获取全部餐桌信息
/// </summary>
public List <DeskInfo> GetAllDeskInfoByRoomId(int roomId)
{
var sql = "select * from DeskInfo where DelFlag=0 and RoomId=@RoomId";
var list = new List <DeskInfo>();
var 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="delFlag">删除表示</param>
/// <returns>会员对象集合</returns>
public List <MemmberInfo> GetAllMemmberInfoByDelFlag(int delFlag)
{
string sql = "select * 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 item in dt.Rows)
{
MemmberInfo mem = RowToMemmberInfo(item);
list.Add(mem);
}
}
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 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>
/// 根据商品类别的id查询产品
/// </summary>
/// <param name="catId"></param>
/// <returns></returns>
public List <ProductInfo> GetProductInfoByCatId(int catId)
{
string sql = "select * from ProductInfo where DelFlag=0 and CatId=@CatId";
DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@CatId", catId));
List <ProductInfo> list = new List <ProductInfo>();
if (dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
ProductInfo pro = RowToProductInfo(item);
list.Add(pro);
}
}
return(list);
}
/// <summary>
/// 模糊查询
/// </summary>
/// <param name="proNum"></param>
/// <returns></returns>
public List <ProductInfo> GetProductByProNum(string proNum)
{
string sql = "select * from ProductInfo where DelFlag=0 and ProNum like @ProNum";
DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@ProNum", "%" + proNum + "%"));
List <ProductInfo> list = new List <ProductInfo>();
if (dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
ProductInfo pro = RowToProductInfo(item);
list.Add(pro);
}
}
return(list);
}
/// <summary>
/// 查询所有没被删除的产品
/// </summary>
/// <param name="delflag"></param>
/// <returns></returns>
public List <ProductInfo> GetAllProductInfoByDelFlag(int delflag)
{
string sql = "select * from productinfo where DelFlag=@DelFlag";
DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@DelFlag", delflag));
List <ProductInfo> list = new List <ProductInfo>();
if (dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
ProductInfo pro = RowToProductInfo(item);
list.Add(pro);
}
}
return(list);
}
/// <summary>
/// 查询所有的会员等级
/// </summary>
/// <param name="delFlag"></param>
/// <returns>会员等级的集合</returns>
public List <MemberType> GetAllMemberTypeByDelFlag(int delFlag)
{
var sql = "select MemType, MemTpName from MemmberType where DelFlag=" + delFlag;
var dt = SqliteHelper.ExecuteTable(sql);
var list = new List <MemberType>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToMemberType(dr));
}
}
return(list);
}
/// <summary>
/// 在数据库中查询账号
/// </summary>
/// <param name="loginName">账号</param>
/// <returns>返回的UserInfo的对象</returns>
public UserInfo IsLoginByName(string loginName)
{
var 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">删除标识0--未删除,1--删除</param>
/// <returns>每个房间对象-集合</returns>
public List <RoomInfo> GetAllRoomInfoByDelFlag(int delFlag)
{
string sql = "select RoomId ,RoomName , RoomType ,RoomMinimunConsume ,RoomMaxConsumer, IsDefault ,SubTime ,SubBy from RoomInfo where DelFlag=@DelFlag";
DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@DelFlag", delFlag));
List <RoomInfo> list = new List <RoomInfo>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
//关系转对象
RoomInfo room = RowToRoomInfo(dr);
list.Add(room);
}
}
return(list);
}
/// <summary>
/// 通过id查询商品信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public CategoryInfo GetCategoryInfoByID(int id)
{
var sql = "select CatId,CatName,CatNum,Remark from CategoryInfo where CatId=@CatId";
var ci = new CategoryInfo();
var dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@CatId", id));
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
ci = RowToCategoryInfo(dr);
}
}
return(ci);
}
/// <summary>
/// 根据删除标志查询结果
/// </summary>
/// <param name="delFlag">0: 未删除;1: 删除</param>
/// <returns></returns>
public List <MemberInfo> GetAllMemberInfoByDelFlag(int delFlag)
{
string sql = "select MemmberId,Memname,MemMobilePhone,MemAddress,MemType,MemNum,MemGender,MemDiscount,MemMoney,SubTime,MemIntegral,MemEndServerTime,MemBirthdaty from MemmberInfo where DelFlag=@DelFlag";
var dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@DelFlag", delFlag));
List <MemberInfo> list = new List <MemberInfo>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToMemberInfo(dr));
}
}
return(list);
}
/// <summary>
/// 查询所有的商品信息
/// </summary>
/// <returns>商品信息的集合</returns>
public List <CategoryInfo> GetAllCategoryInfoByDelFlag(int delFlag)
{
var sql = "select CatId,CatName,CatNum,Remark from CategoryInfo where DelFlag=" + delFlag;
var list = new List <CategoryInfo>();
var dt = SqliteHelper.ExecuteTable(sql);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToCategoryInfo(dr));
}
}
return(list);
}
/// <summary>
/// 通过CatId获取商品信息
/// </summary>
/// <param name="catId"></param>
/// <returns></returns>
public List <ProductInfo> GetProductInfoByCatId(int catId)
{
var sql = "select * from ProductInfo where CatId=" + catId;
var dt = SqliteHelper.ExecuteTable(sql);
var proi = new List <ProductInfo>();
if (dt.Rows.Count != 0)
{
foreach (DataRow dr in dt.Rows)
{
proi.Add(RowToProductInfo(dr));
}
}
return(proi);
}
/// <summary>
/// 模糊查询产品数量获得产品信息
/// </summary>
/// <param name="num"></param>
/// <returns></returns>
public List <ProductInfo> GetProductInforByNum(string num)
{
var sql = "select * from ProductInfo where DelFlag=0 and ProNum like @ProNum";
var list = new List <ProductInfo>();
var dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("ProNum", "%" + num + "%"));
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToProductInfo(dr));
}
}
return(list);
}