public List <TableInfo> GetList(TableInfo ti)
{
List <TableInfo> list = new List <TableInfo>();
string sql = "select ti.*,hi.htitle as halltitle from tableinfo ti" +
" inner join hallinfo hi" +
" on ti.thallid=hi.hid" +
" where ti.tisdelete=0";
List <SQLiteParameter> listP = new List <SQLiteParameter>();
if (ti.THallId > -1)
{
sql += " and ti.thallid=@thallid";
listP.Add(new SQLiteParameter("@thallid", ti.THallId));
}
if (ti.IsFreeSearch > -1)
{
sql += " and ti.tisfree=@tisfree";
listP.Add(new SQLiteParameter("@tisfree", ti.IsFreeSearch));
}
DataTable dt = SqliteHelper.GetList(sql, listP.ToArray());
foreach (DataRow row in dt.Rows)
{
list.Add(new TableInfo()
{
TId = Convert.ToInt32(row["tid"]),
TTitle = row["ttitle"].ToString(),
TIsFree = Convert.ToBoolean(row["tisfree"]),
HallTitle = row["halltitle"].ToString(),
});
}
return(list);
}
public List <HallInfo> GetList()
{
//构造sql语句
string sql = "select * from HallInfo where hIsDelete=0";
//拼接查询，得到结果
// List<SQLiteParameter> listP = new List<SQLiteParameter>();
//执行查询，获取数据
//这里突然发现，在传递参数的时候，可以只传一个spl语句，可以正常运行出正确结果
DataTable table = SqliteHelper.GetList(sql);
//构造集合对象
//定义一个集合用于转存数据
List <HallInfo> list = new List <HallInfo>();
//逐行遍历数据，将表中的数据存到集合中
foreach (DataRow row in table.Rows)
{
//1.list.Add()
//2.new Managerinfo()
//3.对象的初始化
list.Add(new HallInfo()
{
HId = Convert.ToInt32(row["hid"]),
HTitle = row["htitle"].ToString(),
});
}
//返回数据
return(list);
}
public List <ManagerInfo> GetList(ManagerInfo mi)
{
//定义一个集合，用于转存数据
List <ManagerInfo> list = new List <ManagerInfo>();
//执行查询，得到结果
List <SQLiteParameter> listP = new List <SQLiteParameter>();
string sql = "select * from managerinfo";
if (mi != null)
{
sql += " where mname=@name and mpwd=@pwd";
listP.Add(new SQLiteParameter("@name", mi.MName));
listP.Add(new SQLiteParameter("@pwd", Md5Helper.GetMd5(mi.MPwd)));
}
DataTable dt = SqliteHelper.GetList(sql, listP.ToArray());
//逐行遍历数据，将表中的数据存到集合中
foreach (DataRow row in dt.Rows)
{
//1、list.Add()
//2、new ManagerInfo()
//3、{对象的初始化}
list.Add(new ManagerInfo()
{
Mid = Convert.ToInt32(row["mid"]),
MName = row["mname"].ToString(),
MPwd = row["mpwd"].ToString(),
MType = Convert.ToInt32(row["mtype"])
});
}
//返回集合
return(list);
}
public List <TableInfo> GetList(TableInfo ti)
{
string sql = "select ti.*,hi.HTitle from TableInfo ti join HallInfo hi on ti.THallId=hi.Hid where TIsDelete=0";
List <SQLiteParameter> listP = new List <SQLiteParameter>();
if (ti.HState > -1)
{
sql += " and ti.TIsFree=@free";
listP.Add(new SQLiteParameter("@free", ti.HState));
}
if (ti.THallId > 0)
{
sql += " and ti.THallId=@id";
listP.Add(new SQLiteParameter("@id", ti.THallId));
}
DataTable dt = SqliteHelper.GetList(sql, listP.ToArray());
List <TableInfo> list = new List <TableInfo>();
foreach (DataRow row in dt.Rows)
{
list.Add(new TableInfo()
{
HTitle = row["HTitle"].ToString(),
THallId = Convert.ToInt32(row["THallId"]),
TId = Convert.ToInt32(row["TId"]),
TIsFree = Convert.ToBoolean(row["TIsFree"]),
TTitle = row["TTitle"].ToString()
});
}
return(list);
}
public List <MemberCTypeInfo> GetList(int id)
{
string sql = "select * from MemberCTypeInfo where mcisdelete=0";
//条件查询
List <SQLiteParameter> listP = new List <SQLiteParameter>();
if (id > 0)
{
sql += " and mcid = @id";
listP.Add(new SQLiteParameter("@id", id));
}
DataTable table = SqliteHelper.GetList(sql, listP.ToArray());
List <MemberCTypeInfo> list = new List <MemberCTypeInfo>();
foreach (DataRow row in table.Rows)
{
list.Add(new MemberCTypeInfo()
{
MCId = Convert.ToInt32(row["mcid"]),
MCType = row["mctype"].ToString(),
MCDay = row["mcday"].ToString(),
MCBZ = row["mcbz"].ToString()
});
}
return(list);
}
public List <OrderDetailInfo> GetDetaillist(int orderid)
{
var list = new List <OrderDetailInfo>();
var sql = "select odi.*,di.dtitle,di.dprice from orderdetailinfo odi " +
"inner join dishinfo di " +
"on odi.dishid=di.did " +
"where odi.orderid=@orderid";
var p = new SQLiteParameter("@orderid", orderid);
var dt = SqliteHelper.GetList(sql, p);
foreach (DataRow row in dt.Rows)
{
list.Add(new OrderDetailInfo
{
OId = Convert.ToInt32(row["oid"]),
OrderId = orderid,
DishId = Convert.ToInt32(row["dishid"]),
Count = Convert.ToInt32(row["count"]),
DishPrice = Convert.ToDecimal(row["dprice"]),
DishTitle = row["dtitle"].ToString()
});
}
return(list);
}
public List <DishTypeInfo> GetList()
{
string sql = "select * from DishTypeInfo where dIsDelete=0";
List <SQLiteParameter> listP = new List <SQLiteParameter>();
//这里必须要用集合，我也不明白为什么，这个集合可以存放一些查询条件，用于拼接查询。
//这里并没有用到拼接查询，集合是空的，但也不能用null代替，否则后面运行会报错。
DataTable table = SqliteHelper.GetList(sql, listP.ToArray());
//构造集合对象
//定义一个集合用于转存数据
List <DishTypeInfo> list = new List <DishTypeInfo>();
//逐行遍历数据，将表中的数据存到集合中
foreach (DataRow row in table.Rows)
{
//1.list.Add()
//2.new Managerinfo()
//3.对象的初始化
list.Add(new DishTypeInfo()
{
DId = Convert.ToInt32(row["did"]),
DTitle = row["dtitle"].ToString(),
});
}
//返回数据
return(list);
}
public List <ManagerInfo> GetList(ManagerInfo mi)
{
var list = new List <ManagerInfo>();
var sql = "select * from managerinfo ";
var ps = new List <SQLiteParameter>();
if (mi != null)
{
sql += "where mname=@name and mpwd=@pwd";
ps.Add(new SQLiteParameter("@name", mi.MName));
ps.Add(new SQLiteParameter("@pwd", MD5Helper.GetMD5(mi.MPwd)));
}
var dt = SqliteHelper.GetList(sql, ps.ToArray());
foreach (DataRow row in dt.Rows)
{
list.Add(new ManagerInfo
{
Mid = Convert.ToInt32(row["mid"]),
MName = row["mname"].ToString(),
MPwd = row["mpwd"].ToString(),
MType = Convert.ToInt32(row["mtype"])
});
}
return(list);
}
public List <ManagerInfo> GetList(ManagerInfo mi)
{
string sql = "select * from managerinfo";
List <SQLiteParameter> listP = new List <SQLiteParameter>();
if (mi != null)
{
sql += " where mname=@name and mpwd=@pwd";
listP.Add(new SQLiteParameter("@name", mi.MName));
listP.Add(new SQLiteParameter("@pwd", Md5Helper.GetMd5(mi.MPwd)));
}
DataTable table = SqliteHelper.GetList(sql, listP.ToArray());
List <ManagerInfo> list = new List <ManagerInfo>();
foreach (DataRow row in table.Rows)
{
list.Add(new ManagerInfo()
{
MId = Convert.ToInt32(row["mid"]),
MName = row["mname"].ToString(),
MPwd = row["mpwd"].ToString(),
MType = Convert.ToInt32(row["mtype"])
});
}
return(list);
}
public List <OrderDetailInfo> GetOrderDetail(int orderId)
{
string sql = "select odi.*,di.DTitle,di.DPrice" +
" from orderDetailInfo odi" +
" inner join dishInfo di" +
" on odi.dishid=di.did" +
" where odi.orderid=@oid";
SQLiteParameter p = new SQLiteParameter("@oid", orderId);
DataTable dt = SqliteHelper.GetList(sql, p);
List <OrderDetailInfo> list = new List <OrderDetailInfo>();
foreach (DataRow row in dt.Rows)
{
list.Add(new OrderDetailInfo()
{
OId = Convert.ToInt32(row["oid"]),
OrderId = orderId,
DishiId = Convert.ToInt32(row["DishId"]),
Count = Convert.ToInt32(row["Count"]),
DishTitle = row["dtitle"].ToString(),
DishPrice = Convert.ToDecimal(row["dprice"])
});
}
return(list);
}
public List <DishInfo> GetList(DishInfo di)
{
//构造sql语句
//链接查询，需要查到MTypeId的会员名称
string sql = "select di.*,dti.dTitle as typetitle from DishInfo di" +
" inner join dishtypeinfo dti" +
" on di.dtypeid=dti.did" +
" where di.dIsDelete=0";
//拼接查询，得到结果
List <SQLiteParameter> listP = new List <SQLiteParameter>();
if (!string.IsNullOrEmpty(di.DTitle))
{
//模糊查询
sql += " and di.dtitle like @title";
listP.Add(new SQLiteParameter("@title", "%" + di.DTitle + "%"));
}
if (di.DTypeId > 0)
{
sql += " and di.dtypeid like @typeid";
listP.Add(new SQLiteParameter("@typeid", "%" + di.DTypeId + "%"));
}
//在点餐的时候根据拼音首字母查询
if (!string.IsNullOrEmpty(di.DChar))
{
//模糊查询
sql += " and di.dchar like @char";
listP.Add(new SQLiteParameter("@char", "%" + di.DChar + "%"));
}
sql += " order by di.did desc";
//执行查询，获取数据
DataTable table = SqliteHelper.GetList(sql, listP.ToArray());
//构造集合对象
//定义一个集合用于转存数据
List <DishInfo> list = new List <DishInfo>();
//逐行遍历数据，将表中的数据存到集合中
foreach (DataRow row in table.Rows)
{
//1.list.Add()
//2.new Managerinfo()
//3.对象的初始化
list.Add(new DishInfo()
{
DId = Convert.ToInt32(row["did"]),
DTypeId = Convert.ToInt32(row["dtypeid"]),
DTitle = row["dtitle"].ToString(),
DChar = row["dchar"].ToString(),
DPrice = Convert.ToDecimal(row["dprice"]),
TypeTitle = row["typetitle"].ToString(),
});
}
//返回数据
return(list);
}
public List <MemberInfo> GetList(MemberInfo mei)
{
//构造sql语句
//链接查询，需要查到MTypeId的会员名称
string sql = "select mei.*,mti.mTitle,mti.mDiscount from MemberInfo mei" +
" inner join membertypeinfo mti" +
" on mei.mtypeid=mti.mid" +
" where mei.mIsDelete=0";
//拼接查询，得到结果
List <SQLiteParameter> listP = new List <SQLiteParameter>();
if (!string.IsNullOrEmpty(mei.MName))
{
//模糊查询
sql += " and mei.mname like @name";
listP.Add(new SQLiteParameter("@name", "%" + mei.MName + "%"));
}
if (!string.IsNullOrEmpty(mei.MPhone))
{
sql += " and mei.mphone like @phone";
listP.Add(new SQLiteParameter("@phone", "%" + mei.MPhone + "%"));
}
//执行查询，获取数据
DataTable table = SqliteHelper.GetList(sql, listP.ToArray());
//构造集合对象
//定义一个集合用于转存数据
List <MemberInfo> list = new List <MemberInfo>();
//逐行遍历数据，将表中的数据存到集合中
foreach (DataRow row in table.Rows)
{
//1.list.Add()
//2.new Managerinfo()
//3.对象的初始化
list.Add(new MemberInfo()
{
MId = Convert.ToInt32(row["mid"]),
MTypeId = Convert.ToInt32(row["mtypeid"]),
MName = row["mname"].ToString(),
MPhone = row["mphone"].ToString(),
MMoney = Convert.ToDecimal(row["mmoney"]),
TypeTitle = row["mtitle"].ToString(),
TypeDiscount = Convert.ToDecimal(row["mDiscount"]),
});
}
//返回数据
return(list);
}
public List <DishTypeInfo> GetList()
{
string sql = "select * from DishTypeInfo where DIsDelete=0";
DataTable dt = SqliteHelper.GetList(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);
}
public List <HallInfo> GetList()
{
string sql = "select * from HallInfo where HIsDelete=0";
DataTable dt = SqliteHelper.GetList(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);
}
public List <MemberTypeInfo> GetList()
{
var sql = "select * from membertypeinfo where misdelete = 0 ";
var dt = SqliteHelper.GetList(sql);
var 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);
}
public List <MemberInfo> GetList(MemberInfo mi)
{
//查询会有问题
string sql = "select mi.*,mti.mtitle,mti.mdiscount " +
"from memberinfo mi inner join membertypeinfo mti " +
"on mi.MTypeId==mti.MID " +
"where mi.MIsDelete=0";
List <SQLiteParameter> listp = new List <SQLiteParameter>();
if (mi.Mid > 0)
{
sql += " and mi.mid=@mid";
listp.Add(new SQLiteParameter("@mid", mi.Mid));
}
if (!string.IsNullOrEmpty(mi.MName))
{
sql += " and mi.mname like @name";
listp.Add(new SQLiteParameter("@name", "%" + mi.MName + "%"));
}
if (!string.IsNullOrEmpty(mi.MPhone))
{
sql += " and mi.mphone like @phone";
listp.Add(new SQLiteParameter("@phone", "%" + mi.MPhone + "%"));
}
DataTable dt = SqliteHelper.GetList(sql, listp.ToArray());
List <MemberInfo> list = new List <MemberInfo>();
foreach (DataRow row in dt.Rows)
{
list.Add(new MemberInfo()
{
Mid = Convert.ToInt32(row["mid"]),
MMoney = Convert.ToDecimal(row["mmoney"]),
MTypeId = Convert.ToInt32(row["mtypeid"]),
MPhone = row["mphone"].ToString(),
MName = row["mname"].ToString(),
TyptTitle = row["mtitle"].ToString(),
TypeDiscount = Convert.ToDecimal(row["mdiscount"])
});
}
return(list);
}
public List <DishTypeInfo> GetList()
{
var list = new List <DishTypeInfo>();
var sql = "select * from dishtypeinfo where disdelete=0";
var dt = SqliteHelper.GetList(sql);
foreach (DataRow row in dt.Rows)
{
list.Add(new DishTypeInfo
{
DId = Convert.ToInt32(row["did"]),
DTitle = row["dtitle"].ToString()
});
}
return(list);
}
public List <MemberTypeInfo> Select()
{
string sql = "select * from MemberTypeInfo where MisDelete=0";
DataTable dt = SqliteHelper.GetList(sql);
List <MemberTypeInfo> memberTypeInfoList = new List <MemberTypeInfo>();
foreach (DataRow item in dt.Rows)
{
memberTypeInfoList.Add(new MemberTypeInfo()
{
MId = Convert.ToInt32(item["MId"]),
MDiscount = Convert.ToDecimal(item["MDiscount"]),
MTitle = item["Mtitle"].ToString(),
MIsDelete = Convert.ToInt32(item["MIsDelete"])
});
}
return(memberTypeInfoList);
}
//获得所有订单数据，以便存储到xls表格，导出数据
public List <OrderInfo> GetOrderInfo()
{
//连接查询，根据订单编号查到会员名称，会员手机号
//这样查询，只能查到会员点菜记录，非会员查询不到
string sql = "select oi.*,mei.MName,mei.MPhone" +
" from orderInfo oi" +
" inner join MemberInfo mei" +
" on oi.memberid=mei.mid" +
" where oi.ispay=1 and omoney is NOT NULL";
// inner join MemberInfo mei
DataTable dt = SqliteHelper.GetList(sql);
//查询一般客户订餐记录
sql = "select * from orderInfo where memberId is NULL and ispay=1 and omoney is NOT NULL";
//这里NULL必须用大写，这里之所以让订单金额不能为空，担心有意外导致的金额为空
DataTable dt2 = SqliteHelper.GetList(sql);
List <OrderInfo> list = new List <OrderInfo>();
foreach (DataRow row in dt.Rows)
{
list.Add(new OrderInfo()
{
OId = Convert.ToInt32(row["oid"]),
ODate = Convert.ToDateTime(row["odate"]),
MemberName = row["mname"].ToString(),
MemberPhone = row["mphone"].ToString(),
OMoney = Convert.ToDecimal(row["omoney"]),
});
}
foreach (DataRow row in dt2.Rows)
{
list.Add(new OrderInfo()
{
OId = Convert.ToInt32(row["oid"]),
ODate = Convert.ToDateTime(row["odate"]),
MemberName = "普通客户",
MemberPhone = "10000000000",
OMoney = Convert.ToDecimal(row["omoney"]),
});
}
return(list);
}
public List <DishInfo> GetList(DishInfo di)
{
List <DishInfo> list = new List <DishInfo>();
string sql = "select di.*,dti.dtitle as typetitle from dishinfo di" +
" inner join dishtypeinfo dti on" +
" di.dtypeid=dti.did" +
" where di.disdelete=0";
//条件 title id char
List <SQLiteParameter> listp = new List <SQLiteParameter>();
if (!string.IsNullOrEmpty(di.DTitle))
{
sql += " and di.dtitle like @dtitle";
listp.Add(new SQLiteParameter("@dtitle", "%" + di.DTitle + "%"));
}
if (di.DTypeId > 0)
{
sql += " and di.dtypeid=@dtypeid";
listp.Add(new SQLiteParameter("@dtypeid", di.DTypeId));
}
if (!string.IsNullOrEmpty(di.DChar))
{
sql += " and di.dchar like @dchar";
listp.Add(new SQLiteParameter("@dchar", "%" + di.DChar + "%"));
}
sql += " order by di.did desc";
DataTable dt = SqliteHelper.GetList(sql, listp.ToArray());
foreach (DataRow row in dt.Rows)
{
list.Add(new DishInfo()
{
DTitle = row["dtitle"].ToString(),
DChar = row["dchar"].ToString(),
DPrice = Convert.ToDecimal(row["dprice"]),
DTypeId = Convert.ToInt32(row["dtypeid"]),
Did = Convert.ToInt32(row["did"]),
TypeTitle = row["typetitle"].ToString()
});
}
return(list);
}
public List <Managerinfo> GetList(Managerinfo mi)
{
//构造sql语句
string sql = "select * from Managerinfo";
//拼接查询，得到结果
List <SQLiteParameter> listP = new List <SQLiteParameter>();
if (mi != null)
{
sql += " where mname=@name and mpwd=@pwd";
listP.Add(new SQLiteParameter("@name", mi.MName));
listP.Add(new SQLiteParameter("@pwd", MD5Helper.GetMd5(mi.MPwd)));
}
//执行查询，获取数据
DataTable table = SqliteHelper.GetList(sql, listP.ToArray());
//构造集合对象
//定义一个集合用于转存数据
List <Managerinfo> list = new List <Managerinfo>();
//逐行遍历数据，将表中的数据存到集合中
foreach (DataRow row in table.Rows)
{
//1.list.Add()
//2.new Managerinfo()
//3.对象的初始化
list.Add(new Managerinfo()
{
MId = Convert.ToInt32(row["mid"]),
MName = row["mname"].ToString(),
MPwd = row["mpwd"].ToString(),
MType = Convert.ToInt32(row["mtype"])
});
}
//返回数据
return(list);
}
public List <MemberInfo> GetList(MemberInfo memberInfo)
{
string sql = "select mi.*,mti.MTitle,mti.MDiscount from MemberInfo mi join MemberTypeInfo mti on mi.MTypeId=mti.MId where mi.MIsDelete=0";
List <SQLiteParameter> listP = new List <SQLiteParameter>();
if (!string.IsNullOrEmpty(memberInfo.MName))
{
sql += " and MName like @name";
listP.Add(new SQLiteParameter("@name", "%" + memberInfo.MName + "%"));
}
if (memberInfo.MId > 0)
{
sql += " and mi.MId=@id";
listP.Add(new SQLiteParameter("@id", memberInfo.MId));
}
if (!string.IsNullOrEmpty(memberInfo.MPhone))
{
sql += " and MPhone like @phone";
listP.Add(new SQLiteParameter("@phone", "%" + memberInfo.MPhone + "%"));
}
DataTable dt = SqliteHelper.GetList(sql, listP.ToArray());
List <MemberInfo> list = new List <MemberInfo>();
foreach (DataRow row in dt.Rows)
{
list.Add(new MemberInfo()
{
MId = Convert.ToInt32(row["MId"]),
MMoney = Convert.ToDecimal(row["MMoney"]),
MName = row["MName"].ToString(),
MPhone = row["MPhone"].ToString(),
MTypeId = Convert.ToInt32(row["MtypeId"]),
MTitle = row["MTitle"].ToString(),
MDiscount = Convert.ToDecimal(row["MDiscount"])
});
}
return(list);
}
public List <DishInfo> GetList(DishInfo dishInfo)
{
string sql = "select di.*,dti.DTitle typeTitle from DishInfo di join DishTypeInfo dti on di.DTypeId=dti.DId where di.DIsDelete=0";
List <SQLiteParameter> listP = new List <SQLiteParameter>();
if (!string.IsNullOrEmpty(dishInfo.DTitle))
{
listP.Add(new SQLiteParameter("@title", "%" + dishInfo.DTitle + "%"));
sql += " and di.DTitle like @title";
}
if (dishInfo.DTypeId > 0)
{
listP.Add(new SQLiteParameter("@typeId", dishInfo.DTypeId));
sql += " and DTypeId=@typeId";
}
if (!string.IsNullOrEmpty(dishInfo.DChar))
{
listP.Add(new SQLiteParameter("@Dchar", "%" + dishInfo.DChar + "%"));
sql += " and DChar like @Dchar";
}
DataTable dt = SqliteHelper.GetList(sql, listP.ToArray());
List <DishInfo> list = new List <DishInfo>();
foreach (DataRow row in dt.Rows)
{
list.Add(new DishInfo()
{
DId = Convert.ToInt32(row["DId"]),
DChar = row["DChar"].ToString(),
DTitle = row["DTitle"].ToString(),
DTypeId = Convert.ToInt32(row["DtypeId"]),
DPrice = Convert.ToDecimal(row["DPrice"]),
DTypeTitle = row["typeTitle"].ToString()
});
}
return(list);
}
public List <MemberTypeInfo> GetList()
{
//构造sql语句
string sql = "select * from MemberTypeInfo where mIsDelete=0";
//拼接查询，得到结果
List <SQLiteParameter> listP = new List <SQLiteParameter>();
//if (mti != null)
//{
// sql += "and mtitle=@title and mdiscount=@discount";
// listP.Add(new SQLiteParameter("@title", mti.MTitle));
// listP.Add(new SQLiteParameter("@discount", mti.MDiscount));
//}
//执行查询，获取数据
DataTable table = SqliteHelper.GetList(sql, listP.ToArray());
//构造集合对象
//定义一个集合用于转存数据
List <MemberTypeInfo> list = new List <MemberTypeInfo>();
//逐行遍历数据，将表中的数据存到集合中
foreach (DataRow row in table.Rows)
{
//1.list.Add()
//2.new Managerinfo()
//3.对象的初始化
list.Add(new MemberTypeInfo()
{
MId = Convert.ToInt32(row["mid"]),
MTitle = row["mtitle"].ToString(),
MDiscount = Convert.ToDecimal(row["mdiscount"]),
});
}
//返回数据
return(list);
}
public List <TableInfo> GetList(TableInfo ti)
{
//构造sql语句
//链接查询，需要查到MTypeId的会员名称
string sql = "select ti.*,hi.hTitle from TableInfo ti" +
" inner join hallinfo hi" +
" on ti.thallid=hi.hid" +
" where ti.tIsDelete=0";
//拼接查询，得到结果
List <SQLiteParameter> listP = new List <SQLiteParameter>();
if (ti.THallId > 0)
{
sql += " and ti.THallId=@hid";
listP.Add(new SQLiteParameter("@hid", ti.THallId));
}
if (ti.IsFreeSearch > -1)
{
sql += " and ti.TIsFree=@isFree";
listP.Add(new SQLiteParameter("@isFree", ti.IsFreeSearch));
}
if (ti.TId != 0)//这里用来拼接查询，根据餐桌ID查询餐桌名称和所属包厅，在点菜时使用。
{
sql += " and ti.TId=@id";
listP.Add(new SQLiteParameter("@id", ti.TId));
}
//if (!string.IsNullOrEmpty(mei.MName))
//{
// //模糊查询
// sql += " and mei.mname like @name";
// listP.Add(new SQLiteParameter("@name", "%" + mei.MName + "%"));
//}
//if (!string.IsNullOrEmpty(mei.MPhone))
//{
// sql += " and mei.mphone like @phone";
// listP.Add(new SQLiteParameter("@phone", "%" + mei.MPhone + "%"));
//}
sql += " order by ti.tid desc";
//执行查询，获取数据
DataTable table = SqliteHelper.GetList(sql, listP.ToArray());
//构造集合对象
//定义一个集合用于转存数据
List <TableInfo> list = new List <TableInfo>();
//逐行遍历数据，将表中的数据存到集合中
foreach (DataRow row in table.Rows)
{
//1.list.Add()
//2.new Managerinfo()
//3.对象的初始化
list.Add(new TableInfo()
{
TId = Convert.ToInt32(row["tid"]),
THallId = Convert.ToInt32(row["thallid"]),
TTitle = row["ttitle"].ToString(),
TIsFree = Convert.ToBoolean(row["tisfree"]),
HallType = row["htitle"].ToString(),
});
}
//返回数据
return(list);
}
public List <MemberInfo> GetList(MemberInfo mi)
{
string sql = "select mi.*,mti.mctype,mti.mcbz from memberinfo mi" +
" inner join memberctypeinfo mti" +
" on mi.mtypeid=mti.mcid" +
" where mi.misdelete=0";
//条件查询
List <SQLiteParameter> listP = new List <SQLiteParameter>();
if (!string.IsNullOrEmpty(mi.MName))
{
sql += " and mi.mname like @name";
listP.Add(new SQLiteParameter("@name", "%" + mi.MName + "%"));//"%%" 表示模糊查询（不精确）
}
if (!string.IsNullOrEmpty(mi.MPhone))
{
sql += " and mi.mphone like @phone";
listP.Add(new SQLiteParameter("@phone", "%" + mi.MPhone + "%"));
}
if (!string.IsNullOrEmpty(mi.MCId))
{
sql += " and mi.mcid = @mcid";
listP.Add(new SQLiteParameter("@mcid", mi.MCId));
}
if (mi.MId > 0)
{
sql += " and mi.mid=@id";
listP.Add(new SQLiteParameter("@id", mi.MId));
}
if (mi.MTag > 0)
{
switch (mi.MTag)
{
case 1:
sql += " and mi.menddate < datetime('now','7 day')";
break;
case 2:
sql += " and mi.menddate < datetime('now','30 day')";
break;
case 3:
sql += " and mi.mstartdate > datetime('now','-7 day')";
break;
case 4:
sql += " and mi.mstartdate > datetime('now','-30 day')";
break;
case 5:
sql += " and mi.menddate < datetime('now')";
break;
}
}
sql += " order by mi.mid desc";//倒叙
DataTable dt = SqliteHelper.GetList(sql, listP.ToArray());
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(),
MAdress = row["madress"].ToString(),
MCId = row["mcid"].ToString(),
MSex = row["msex"].ToString(),
MTypeId = Convert.ToInt32(row["mtypeid"]),
MCount = Convert.ToInt32(row["mcount"]),
MStartDate = Convert.ToDateTime(row["mstartdate"]),
MEndDate = Convert.ToDateTime(row["menddate"]),
MDate = Convert.ToDateTime(row["mdate"]),
MBZ = row["mbz"].ToString(),
MPhoto = row["mphoto"].ToString(),
TypeTitle = row["mctype"].ToString(),
TypeBz = row["mcbz"].ToString(),
});
}
return(list);
}