public static bool UpdateOneByStr(int equipmentID, string str, DateTime date)
{
string sql2 = string.Format("select ID from tb_Alert where EquipmentID = {0} and AlertName = '{1}' limit 0,1", equipmentID, str);
DataSet ds = SqliteHelper.Query(sql2);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
long id = Convert.ToInt64(ds.Tables[0].Rows[0]["ID"]);
string sql = string.Format("update tb_Alert set EndTime='{0}' where ID={1}", date.ToString("yyyy/MM/dd HH:mm:ss"), id);
if (SqliteHelper.ExecuteNonQuery(sql) == 1)
{
return(true);
}
else
{
LogLib.Log.GetLogger("AlertDal").Warn("更新报警记录失败！");
return(false);
}
}
else
{
LogLib.Log.GetLogger("AlertDal").Warn("更新报警记录失败！");
return(false);
}
}
public static List <Alert> GetListByTime(DateTime t1, DateTime t2)
{
string sql = string.Format("select a.ID, a.EquipmentID,a.StartTime,a.EndTime,a.AlertName,a.AlertValue,b.Address,b.EName,b.Place from tb_Alert a left join tb_Equipment b on a.EquipmentID=b.ID where a.StartTime >= '{0}' and a.EndTime <= '{1}'", t1.ToString("yyyy/MM/dd HH:mm:ss"), t2.ToString("yyyy/MM/dd HH:mm:ss"));
DataSet ds = new DataSet();
ds = SqliteHelper.Query(sql);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <Alert> list = new List <Alert>();
foreach (DataRow row in ds.Tables[0].Rows)
{
Alert at = new Alert();
at.ID = Convert.ToInt64(row["ID"]);
at.EquipmentID = Convert.ToInt64(row["EquipmentID"]);
at.StartTime = Convert.ToDateTime(row["StartTime"]);
at.EndTime = Convert.ToDateTime(row["EndTime"]);
at.AlertName = row["AlertName"].ToString();
at.AlertValue = Convert.ToSingle(row["AlertValue"]);
at.Address = Convert.ToByte(row["Address"]);
at.EName = row["EName"].ToString();
at.Place = row["Place"].ToString();
list.Add(at);
}
return(list);
}
LogLib.Log.GetLogger("AlertDal").Warn("获取报警记录列表失败！");
return(null);
}
public static List <Alert> GetListByWH(string wherestr)
{
string sql = string.Format("select ID, EquipmentID,StartTime,EndTime,AlertName,AlertValue from tb_Alert where {0}", wherestr);
DataSet ds = new DataSet();
ds = SqliteHelper.Query(sql);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <Alert> list = new List <Alert>();
foreach (DataRow row in ds.Tables[0].Rows)
{
Alert at = new Alert();
at.ID = Convert.ToInt64(row["Address"]);
at.EquipmentID = Convert.ToInt64(row["Address"]);
at.StartTime = Convert.ToDateTime(row["StartTime"]);
at.EndTime = Convert.ToDateTime(row["EndTime"]);
at.AlertName = row["AlertName"].ToString();
at.AlertValue = Convert.ToSingle(row["AlertValue"]);
list.Add(at);
}
return(list);
}
LogLib.Log.GetLogger("AlertDal").Warn("获取报警记录列表失败！");
return(null);
}
public static Alert AddOneR(Alert info)
{
string sql = string.Format("insert into tb_Alert (EquipmentID,StartTime,EndTime,AlertName,AlertValue) values ({0},'{1}','{2}','{3}',{4})", info.EquipmentID, info.StartTime.ToString("yyyy/MM/dd HH:mm:ss"), info.EndTime.ToString("yyyy/MM/dd HH:mm:ss"), info.AlertName, info.AlertValue);
if (SqliteHelper.ExecuteNonQuery(sql) == 1)
{
string sql2 = string.Format("select ID from tb_Alert where EquipmentID = {0} and AlertName = '{1}' and StartTime = '{2}' limit 0,1", info.EquipmentID, info.AlertName, info.StartTime.ToString("yyyy/MM/dd HH:mm:ss"));
DataSet ds = SqliteHelper.Query(sql2);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
info.ID = Convert.ToInt64(ds.Tables[0].Rows[0]["ID"]);
return(info);
}
else
{
LogLib.Log.GetLogger("AlertDal").Warn("插入报警记录失败！");
return(null);
}
}
else
{
LogLib.Log.GetLogger("AlertDal").Warn("插入报警记录失败！");
return(null);
}
}
public static Equipment GetOneByWh(string where)
{
string sql = string.Format("select ID,Address,AlertType,EName,Place,Range,Unit,Point,Revise,LowAlert,HighAlert,Addtime from tb_Equipment where {0} limit 0,1", where);
DataSet ds = new DataSet();
ds = SqliteHelper.Query(sql);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
Equipment eq = new Equipment();
eq.ID = Convert.ToInt64(ds.Tables[0].Rows[0]["ID"]);
eq.EName = ds.Tables[0].Rows[0]["EName"].ToString();
eq.Address = Convert.ToByte(ds.Tables[0].Rows[0]["Address"]);
eq.AlertType = Convert.ToByte(ds.Tables[0].Rows[0]["AlertType"]);
eq.Place = ds.Tables[0].Rows[0]["Place"].ToString();
eq.Range = Convert.ToSingle(ds.Tables[0].Rows[0]["Range"]);
eq.Unit = ds.Tables[0].Rows[0]["Unit"].ToString();
eq.Point = Convert.ToByte(ds.Tables[0].Rows[0]["Point"]);
eq.Revise = Convert.ToSingle(ds.Tables[0].Rows[0]["Revise"]);
eq.LowAlert = Convert.ToSingle(ds.Tables[0].Rows[0]["LowAlert"]);
eq.HighAlert = Convert.ToSingle(ds.Tables[0].Rows[0]["HighAlert"]);
eq.Addtime = Convert.ToDateTime(ds.Tables[0].Rows[0]["Addtime"]);
return(eq);
}
LogLib.Log.GetLogger("EquipmentDal").Warn("获取设备传感器失败");
return(null);
}
public static List <Alert> GetListByTime(int equipmentID, DateTime t1, DateTime t2)
{
string sql = string.Format("select a.ID,a.EquipmentID,a.StratTime,a.EndTime,a.AddTime,a.AlertName,b.GasName,b.Name,b.SensorTypeB,b.Address from tb_Alert a left join tb_Equipment b on a.EquipmentID=b.ID where a.EquipmentID = {0} and a.StratTime >= '{1}' and a.EndTime <= '{2}'and a.AlertName != '无报警'", equipmentID, t1.ToString("yyyy-MM-dd HH:mm:ss"), t2.ToString("yyyy-MM-dd HH:mm:ss"));
DataSet ds = new DataSet();
ds = SqliteHelper.Query(sql);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <Alert> list = new List <Alert>();
foreach (DataRow row in ds.Tables[0].Rows)
{
Alert at = new Alert();
at.EquipmentID = row["EquipmentID"] == DBNull.Value ? -1 : Convert.ToInt32(row["EquipmentID"]);
at.StratTime = row["StratTime"] == DBNull.Value ? DateTime.MaxValue : Convert.ToDateTime(row["StratTime"]);
at.EndTime = row["EndTime"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(row["EndTime"]);
at.AddTime = row["AddTime"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(row["AddTime"]);
at.AlertName = row["AlertName"].ToString();
at.EquipmentName = row["Name"].ToString();
// at.GasType = row["GasType"] == DBNull.Value ? (byte)0 : Convert.ToByte(row["GasType"]);
// at.SensorType = row["SensorType"] == DBNull.Value ? EM_HighType.通用 : (EM_HighType)row["SensorType"];
at.GasName = row["GasName"].ToString();
at.Address = Convert.ToByte(row["Address"]);
// at.SensorTypeB = Convert.ToByte(row["SensorTypeB"]);
list.Add(at);
}
return(list);
}
LogLib.Log.GetLogger("AlertDal").Warn("获取报警记录列表失败！");
return(null);
}
public static List <Equipment> GetAllList()
{
string sql = string.Format("select ID, Address,AlertType,EName,Place,Range,Unit,Point,Revise,LowAlert,HighAlert,Addtime from tb_Equipment");
DataSet ds = new DataSet();
ds = SqliteHelper.Query(sql);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <Equipment> list = new List <Equipment>();
foreach (DataRow row in ds.Tables[0].Rows)
{
Equipment eq = new Equipment();
eq.ID = Convert.ToInt64(row["ID"]);
eq.EName = row["EName"].ToString();
eq.Address = Convert.ToByte(row["Address"]);
eq.AlertType = Convert.ToByte(row["AlertType"]);
eq.Place = row["Place"].ToString();
eq.Range = Convert.ToSingle(row["Range"]);
eq.Unit = row["Unit"].ToString();
eq.Point = Convert.ToByte(row["Point"]);
eq.Revise = Convert.ToSingle(row["Revise"]);
eq.LowAlert = Convert.ToSingle(row["LowAlert"]);
eq.HighAlert = Convert.ToSingle(row["HighAlert"]);
eq.Addtime = Convert.ToDateTime(row["Addtime"]);
list.Add(eq);
}
return(list);
}
LogLib.Log.GetLogger("EquipmentDal").Warn("获取设备传感器列表失败");
return(new List <Equipment>());
}
public static Equipment GetOneByWh(string where)
{
string sql = string.Format("select Name,Address,SensorTypeB,Low,High,Max,UnitType,CreateTime,UpDateTime,Point,LowChroma,IfShowSeries from tb_Equipment where {0} limit 0,1", where);
DataSet ds = new DataSet();
ds = SqliteHelper.Query(sql);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
Equipment eq = new Equipment();
eq.Name = ds.Tables[0].Rows[0]["Name"].ToString();
eq.Address = Convert.ToByte(ds.Tables[0].Rows[0]["Address"]);
//eq.GasType = Convert.ToByte(ds.Tables[0].Rows[0]["GasType"]);
eq.SensorTypeB = ds.Tables[0].Rows[0]["SensorTypeB"].ToString();
eq.A1 = Convert.ToSingle(ds.Tables[0].Rows[0]["Low"]);
eq.A2 = Convert.ToSingle(ds.Tables[0].Rows[0]["High"]);
eq.Max = Convert.ToUInt32(ds.Tables[0].Rows[0]["Max"]);
eq.UnitType = Convert.ToByte(ds.Tables[0].Rows[0]["UnitType"]);
eq.CreateTime = Convert.ToDateTime(ds.Tables[0].Rows[0]["CreateTime"]);
eq.UpDateTime = Convert.ToDateTime(ds.Tables[0].Rows[0]["UpDateTime"]);
eq.Point = Convert.ToByte(ds.Tables[0].Rows[0]["Point"]);
eq.LowChroma = Convert.ToSingle(ds.Tables[0].Rows[0]["LowChroma"]);
eq.IfShowSeries = Convert.ToBoolean(ds.Tables[0].Rows[0]["IfShowSeries"]);
return(eq);
}
LogLib.Log.GetLogger("EquipmentDal").Warn("获取设备传感器失败");
return(null);
}
/// <summary>
/// 获取所有列表，包括已删除的
/// </summary>
/// <returns></returns>
public static List <Equipment> GetListIn()
{
string sql = string.Format("select ID,Name,Address,GasName,SensorTypeB,Low,High,Max,UnitType,CreateTime,UpDateTime,Point,LowChroma,IsDel,IfShowSeries from tb_Equipment");
DataSet ds = new DataSet();
ds = SqliteHelper.Query(sql);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <Equipment> list = new List <Equipment>();
foreach (DataRow row in ds.Tables[0].Rows)
{
Equipment eq = new Equipment();
eq.ID = Convert.ToInt32(row["ID"]);
eq.Name = row["Name"].ToString();
eq.Address = Convert.ToByte(row["Address"]);
eq.GasName = row["GasName"].ToString();
eq.SensorTypeB = row["SensorTypeB"].ToString();
eq.A1 = Convert.ToSingle(row["Low"]);
eq.A2 = Convert.ToSingle(row["High"]);
eq.Max = Convert.ToUInt32(row["Max"]);
eq.UnitType = Convert.ToByte(row["UnitType"]);
eq.CreateTime = Convert.ToDateTime(row["CreateTime"]);
eq.UpDateTime = Convert.ToDateTime(row["UpDateTime"]);
eq.Point = Convert.ToByte(row["Point"]);
eq.LowChroma = Convert.ToSingle(row["LowChroma"]);
eq.IsDel = Convert.ToBoolean(row["IsDel"]);
eq.IfShowSeries = Convert.ToBoolean(row["IfShowSeries"]);
list.Add(eq);
}
return(list);
}
LogLib.Log.GetLogger("EquipmentDal").Warn("获取设备传感器列表失败");
return(new List <Equipment>());
}
//9.18 以0X格式补齐日期和时间
//public static List<EquipmentData> GetListByTime(long equipmentID,string dt1,string dt2)
public static List <EquipmentData> GetListByTime(long equipmentID, DateTime dt1, DateTime dt2)
{
//dt2 = dt2.AddDays(1);
string sql = string.Format("select a.EquipmentID,a.Chroma,a.Temperature,a.Humidity,a.AddTime,b.UnitType from tb_EquipmentData a left join tb_Equipment b on a.EquipmentID=b.ID where EquipmentID={0} and AddTime >='{1}' and AddTime <='{2}'", equipmentID, dt1.ToString("yyyy-MM-dd HH:mm:ss"), dt2.ToString("yyyy-MM-dd HH:mm:ss"));
DataSet ds = new DataSet();
ds = SqliteHelper.Query(sql);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <EquipmentData> list = new List <EquipmentData>();
foreach (DataRow row in ds.Tables[0].Rows)
{
EquipmentData eq = new EquipmentData();
eq.EquipmentID = Convert.ToInt32(row["EquipmentID"]);
if (Convert.ToSingle(row["Chroma"]) < 10000)
{
eq.Chroma = Convert.ToSingle(row["Chroma"]);
}
//eq.Temperature = Convert.ToSingle(row["Temperature"]);
//eq.Humidity = Convert.ToSingle(row["Humidity"]);
eq.AddTime = Convert.ToDateTime(row["AddTime"]);
eq.UnitType = Convert.ToByte(row["UnitType"]);
list.Add(eq);
}
return(list);
}
LogLib.Log.GetLogger("EquipmentDataDal").Warn("获取浓度数据失败");
return(null);
}
public static List <EquipmentData> GetListByTime(long equipmentID, DateTime dt1, DateTime dt2)
{
//dt2 = dt2.AddDays(1);
string sql = string.Format("select a.EquipmentID,a.Chroma,a.AddTime,b.Unit,b.EName from [tb_Chroma] a left join [tb_Equipment] b on a.EquipmentID=b.ID where EquipmentID={0} and a.AddTime >='{1}' and a.AddTime <='{2}'", equipmentID, dt1.ToString("yyyy/MM/dd HH:mm:ss"), dt2.ToString("yyyy/MM/dd HH:mm:ss"));
DataSet ds = new DataSet();
ds = SqliteHelper.Query(sql);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <EquipmentData> list = new List <EquipmentData>();
foreach (DataRow row in ds.Tables[0].Rows)
{
EquipmentData eq = new EquipmentData();
eq.EquipmentID = Convert.ToInt64(row["EquipmentID"]);
eq.Chroma = Convert.ToSingle(row["Chroma"]);
eq.AddTime = Convert.ToDateTime(row["AddTime"]);
eq.Unit = row["Unit"].ToString();
eq.EName = row["EName"].ToString();
list.Add(eq);
}
return(list);
}
LogLib.Log.GetLogger("EquipmentDataDal").Warn("获取浓度数据失败");
return(null);
}
/// <summary>
/// 获取所有列表，包括已删除的
/// </summary>
/// <returns></returns>
//public static List<Equipment> GetListIn()
//{
// string sql = string.Format("select ID,Name,Address,GasType,SensorType,Low,High,Max,UnitType,CreateTime,UpDateTime,Point,LowChroma from tb_Equipment");
// DataSet ds = new DataSet();
// ds = SqliteHelper.Query(sql);
// if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
// {
// List<Equipment> list = new List<Equipment>();
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// Equipment eq = new Equipment();
// eq.ID = Convert.ToInt32(ds.Tables[0].Rows[i]["ID"]);
// eq.Name = ds.Tables[0].Rows[i]["Name"].ToString();
// eq.Address = Convert.ToByte(ds.Tables[0].Rows[i]["Address"]);
// eq.GasType = Convert.ToByte(ds.Tables[0].Rows[i]["GasType"]);
// eq.SensorType = (EM_HighType)ds.Tables[0].Rows[i]["SensorType"];
// eq.A1 = Convert.ToSingle(ds.Tables[0].Rows[i]["Low"]);
// eq.A2 = Convert.ToSingle(ds.Tables[0].Rows[i]["High"]);
// eq.Max = Convert.ToUInt32(ds.Tables[0].Rows[i]["Max"]);
// eq.UnitType = Convert.ToByte(ds.Tables[0].Rows[i]["UnitType"]);
// eq.CreateTime = (DateTime)ds.Tables[0].Rows[i]["CreateTime"];
// eq.UpDateTime = Convert.ToDateTime(ds.Tables[0].Rows[i]["UpDateTime"]);
// eq.Point = Convert.ToByte(ds.Tables[0].Rows[0]["Point"]);
// eq.LowChroma = Convert.ToSingle(ds.Tables[0].Rows[0]["LowChroma"]);
// list.Add(eq);
// }
// return list;
// }
// LogLib.Log.GetLogger("EquipmentDal").Warn("获取设备传感器列表失败");
// return new List<Equipment>();
//}
/// <summary>
/// 获取有效的设备地址
/// </summary>
/// <returns></returns>
public static List <byte> GetAddress()
{
string sql = string.Format("select Address from tb_Equipment");
DataSet ds = new DataSet();
ds = SqliteHelper.Query(sql);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <byte> list = new List <byte>();
foreach (DataRow row in ds.Tables[0].Rows)
{
list.Add(Convert.ToByte(row["Address"]));
}
return(list.Distinct().ToList());
}
LogLib.Log.GetLogger("EquipmentDal").Warn("获取设备传感器地址列表失败");
return(new List <byte>());
}
public static UserInfo GetOneByUser(string account, string password)
{
string sql = string.Format("select ID,Account,PassWord,UserName,Level from tb_User where Account='{0}' and PassWord='******' limit 0,1", account, password);
DataSet ds = new DataSet();
ds = SqliteHelper.Query(sql);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
UserInfo eq = new UserInfo();
eq.ID = Convert.ToInt64(ds.Tables[0].Rows[0]["ID"]);
eq.Account = ds.Tables[0].Rows[0]["Account"].ToString();
eq.PassWord = ds.Tables[0].Rows[0]["PassWord"].ToString();
eq.UserName = ds.Tables[0].Rows[0]["UserName"].ToString();
eq.Level = (EM_UserType)ds.Tables[0].Rows[0]["Level"];
return(eq);
}
return(null);
}
/// <summary>
/// 获取所有设备名称，包括已经删除的
/// </summary>
/// <returns></returns>
public static List <string> GetNames()
{
string sql = string.Format("select Name from tb_Equipment");
DataSet ds = new DataSet();
ds = SqliteHelper.Query(sql);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
List <string> list = new List <string>();
foreach (DataRow row in ds.Tables[0].Rows)
{
list.Add(row["Name"].ToString());
}
return(list.Distinct().ToList());
}
LogLib.Log.GetLogger("EquipmentDal").Warn("获取设备传感器名称列表失败");
return(new List <string>());
}
public static UserInfo GetOneByID(int id)
{
string sql = string.Format("select ID,Account,PassWord,UserName,[Level] from [tb_User] where ID={0} limit 0,1", id);
DataSet ds = new DataSet();
ds = SqliteHelper.Query(sql);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
UserInfo eq = new UserInfo();
eq.ID = Convert.ToInt64(ds.Tables[0].Rows[0]["ID"]);
eq.Account = ds.Tables[0].Rows[0]["Account"].ToString();
eq.PassWord = ds.Tables[0].Rows[0]["PassWord"].ToString();
eq.UserName = ds.Tables[0].Rows[0]["UserName"].ToString();
eq.Level = (EM_UserType)ds.Tables[0].Rows[0]["Level"];
return(eq);
}
return(null);
}
