/// <summary>
/// 模糊查询
/// </summary>
/// <param name="txt">编号或者是拼音</param>
/// <param name="temp">标识1--编号,2---拼音,3---啥也没有</param>
/// <returns></returns>
public List <ProductInfo> GetProductBySpellOrNum(string txt, int temp)
{
string sql = "select * from ProductInfo where DelFlag=0";
List <ProductInfo> list = new List <ProductInfo>();
List <SQLiteParameter> listParam = new List <SQLiteParameter>();
if (temp == 1)
{
sql += " and ProNum like @ProNum";
listParam.Add(new SQLiteParameter("@ProNum", "%" + txt + "%"));
}
else if (temp == 2)
{
sql += " and ProSpell like @ProSpell";
listParam.Add(new SQLiteParameter("@ProSpell", "%" + txt + "%"));
}
DataTable dt = SqliteHelper.ExecuteTable(sql, listParam.ToArray());
if (dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
ProductInfo pro = RowToProductInfo(item);
list.Add(pro);
}
}
return(list);
}
/// <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="proId">产品id</param>
/// <returns>对象</returns>
public ProductInfo GetProductInfoByProId(int proId)
{
string sql = "select * from ProductInfo where ProId=@ProId";
DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@ProId", proId));
ProductInfo pro = null;
if (dt.Rows.Count > 0)
{
pro = RowToProductInfo(dt.Rows[0]);
}
return(pro);
}
/// <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 dk = null;
if (dt.Rows.Count > 0)
{
dk = RowToDeskInfo(dt.Rows[0]);
}
return(dk);
}
/// <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="id">id</param>
/// <returns></returns>
public CategoryInfo GetCategoryInfoByCaitId(int id)
{
string sql = "select * from CategoryInfo where CatId=@CatId";
DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@CatId", id));
CategoryInfo cat = null;
if (dt.Rows.Count > 0)
{
cat = RowToCategoryInfo(dt.Rows[0]);
}
return(cat);
}
//去数据库查询数据
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);
}
//模糊查询
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>
/// 查询点的菜,根据订单的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="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 <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 dk = RowToDeskInfo(item);
list.Add(dk);
}
}
return(list);
}
/// <summary>
/// 查询所有没被删除的商品类别
/// </summary>
/// <param name="delFlag">0没被删除，1被删除</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>
/// 根据商品类别的id查询商品
/// </summary>
/// <param name="catId">商品类别id</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);
}
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);
}