/// <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>
/// 根据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="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="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="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);
}
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>
/// 根据房间的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="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);
}
/// <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="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);
}
//根据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="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);
}
