/// <summary>
/// Obtener registros de asistencia
/// </summary>
/// <returns></returns>
public DataTable GetAll()
{
string sql = "select * from AttLog order by AttTime desc";
return(SqliteHelper.GetDataTable(sql));
}
/// <summary>
/// Registros claros de asistencia
/// </summary>
/// <returns></returns>
public int ClearAll()
{
string sql = "delete from AttLog";
return(SqliteHelper.ExecuteNonQuery(sql));
}
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);
}
public static int DeleteAll()
{
string sql = "delete from tb_EquipmentData";
return(SqliteHelper.ExecuteNonQuery(sql));
}
/// <summary>
/// 按ID删除设备
/// </summary>
/// <param name="equipmentID"></param>
/// <returns></returns>
public static int DeleteByEquipmentID(long equipmentID)
{
string sql = string.Format("delete from tb_EquipmentData where EquipmentID={0}", equipmentID);
return(SqliteHelper.ExecuteNonQuery(sql));
}
public static int DeleteByTime(long equipmentID, DateTime dt1, DateTime dt2)
{
string sql = string.Format("delete from tb_EquipmentData where EquipmentID={0} and AddTime >='{1}' and AddTime <='{2}'", equipmentID, dt1.ToString(), dt2.ToString());
return(SqliteHelper.ExecuteNonQuery(sql));
}
/// <summary>Update
/// </summary>
/// <param name="device"></param>
/// <returns></returns>
public int Update(DeviceModel device)
{
string sql = string.Format(@"
update Device set
TransInterval = @TransInterval ,
TransTimes = @TransTimes ,
Encrypt = @Encrypt ,
LastRequestTime = @LastRequestTime ,
DevIP = @DevIP ,
DevMac = @DevMac ,
DevFirmwareVersion = @DevFirmwareVersion ,
UserCount = @UserCount ,
AttCount = @AttCount ,
DevName = @DevName ,
TimeZone = @TimeZone ,
Timeout = @Timeout ,
SyncTime = @SyncTime ,
ATTLOGStamp = @ATTLOGStamp ,
OPERLOGStamp = @OPERLOGStamp ,
ATTPHOTOStamp = @ATTPHOTOStamp ,
ErrorDelay = @ErrorDelay ,
Delay = @Delay ,
TransFlag = @TransFlag ,
Realtime = @Realtime,
VendorName = @VendorName,
IRTempDetectionFunOn = @IRTempDetectionFunOn,
MaskDetectionFunOn = @MaskDetectionFunOn,
MultiBioDataSupport = @MultiBioDataSupport,
MultiBioPhotoSupport = @MultiBioPhotoSupport,
MultiBioVersion = @MultiBioVersion,
MultiBioCount = @MultiBioCount,
MaxMultiBioDataCount = @MaxMultiBioDataCount,
MaxMultiBioPhotoCount = @MaxMultiBioPhotoCount
where DevSN=@DevSN
");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@DevSN", device.DevSN),
new SQLiteParameter("@TransInterval", device.TransInterval),
new SQLiteParameter("@TransTimes", device.TransTimes),
new SQLiteParameter("@Encrypt", device.Encrypt),
new SQLiteParameter("@LastRequestTime", device.LastRequestTime),
new SQLiteParameter("@DevIP", device.DevIP),
new SQLiteParameter("@DevMac", device.DevMac),
new SQLiteParameter("@DevFirmwareVersion", device.DevFirmwareVersion),
new SQLiteParameter("@UserCount", device.UserCount),
new SQLiteParameter("@AttCount", device.AttCount),
new SQLiteParameter("@DevName", device.DevName),
new SQLiteParameter("@TimeZone", device.TimeZone),
new SQLiteParameter("@Timeout", device.Timeout),
new SQLiteParameter("@SyncTime", device.SyncTime),
new SQLiteParameter("@ATTLOGStamp", device.AttLogStamp),
new SQLiteParameter("@OPERLOGStamp", device.OperLogStamp),
new SQLiteParameter("@ATTPHOTOStamp", device.AttPhotoStamp),
new SQLiteParameter("@ErrorDelay", device.ErrorDelay),
new SQLiteParameter("@Delay", device.Delay),
new SQLiteParameter("@TransFlag", device.TransFlag),
new SQLiteParameter("@Realtime", device.Realtime),
new SQLiteParameter("@VendorName", device.VendorName),
new SQLiteParameter("@IRTempDetectionFunOn", device.IRTempDetectionFunOn),
new SQLiteParameter("@MaskDetectionFunOn", device.MaskDetectionFunOn),
new SQLiteParameter("@MultiBioDataSupport", device.MultiBioDataSupport),
new SQLiteParameter("@MultiBioPhotoSupport", device.MultiBioPhotoSupport),
new SQLiteParameter("@MultiBioVersion", device.MultiBioVersion),
new SQLiteParameter("@MultiBioCount", device.MultiBioCount),
new SQLiteParameter("@MaxMultiBioDataCount", device.MaxMultiBioDataCount),
new SQLiteParameter("@MaxMultiBioPhotoCount", device.MaxMultiBioPhotoCount)
};
return(SqliteHelper.ExecuteNonQuery(sql, parameters));
}
/// <summary>
/// 清空
/// </summary>
/// <returns></returns>
public int ClearAll()
{
string sql = "delete from TmpBioPhoto ";
return(SqliteHelper.ExecuteNonQuery(sql));
}
public static int DeleteByEqID(long eqid)
{
string sql = string.Format("delete from [tb_Chroma] where EquipmentID={0}", eqid);
return(SqliteHelper.ExecuteNonQuery(sql));
}
public static int DeleteAll()
{
string sql = "delete from [tb_Chroma]";
return(SqliteHelper.ExecuteNonQuery(sql));
}