using SqlSugar;
|
using System.Collections.Generic;
|
|
namespace IStation.DAL.SQLite
|
{
|
/// <summary>
|
///
|
/// </summary>
|
public class MonitorRecord : IMonitorRecord
|
{
|
/// <summary>
|
///
|
/// </summary>
|
public ConnectionConfig ConnectionConfig
|
{
|
get { return ConfigHelper.MonitorConnectionConfig; }
|
}
|
|
#region Query
|
|
/// <summary>
|
/// 查询时间区间内的数据
|
/// </summary>
|
public List<Entity.MonitorRecord> GetByTimeRange(DateTime StartTime, DateTime EndTime)
|
{
|
if (StartTime > EndTime)
|
return default;
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Queryable<Entity.MonitorRecord>()
|
.Where(x => x.DataTime > StartTime.AddSeconds(-1) && x.DataTime < EndTime.AddSeconds(1))
|
.OrderBy(x => x.DataTime, OrderByType.Asc)
|
.ToList();
|
}
|
}
|
|
|
/// <summary>
|
/// 查询接收时间区间内的数据
|
/// </summary>
|
public List<Entity.MonitorRecord> GetByReceiptTimeRange(DateTime StartTime, DateTime EndTime)
|
{
|
if (StartTime > EndTime)
|
return default;
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Queryable<Entity.MonitorRecord>()
|
.Where(x => x.ReceiptTime > StartTime.AddSeconds(-1) && x.ReceiptTime < EndTime.AddSeconds(1))
|
.OrderBy(x => x.ReceiptTime, OrderByType.Asc)
|
.ToList();
|
}
|
}
|
|
#region 时间区间
|
|
/// <summary>
|
/// 通过 DataCode 查询时间区间内的数据
|
/// </summary>
|
public List<Entity.MonitorRecord> GetByDataCodeOfTimeRange(string DataCode, DateTime StartTime, DateTime EndTime)
|
{
|
if (StartTime > EndTime)
|
return default;
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Queryable<Entity.MonitorRecord>()
|
.Where(x => x.DataTime > StartTime.AddSeconds(-1) && x.DataTime < EndTime.AddSeconds(1))
|
.Where(x => x.DataCode == DataCode)
|
.OrderBy(x => x.DataTime, OrderByType.Asc)
|
.ToList();
|
}
|
}
|
|
/// <summary>
|
/// 通过 DataCode 查询时间区间内的第一条数据
|
/// </summary>
|
public Entity.MonitorRecord GetFirstByDataCodeOfTimeRange(string DataCode, DateTime StartTime, DateTime EndTime)
|
{
|
if (StartTime > EndTime)
|
return default;
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Queryable<Entity.MonitorRecord>()
|
.Where(x => x.DataTime > StartTime.AddSeconds(-1) && x.DataTime < EndTime.AddSeconds(1))
|
.Where(x => x.DataCode == DataCode)
|
.OrderBy(x => x.DataTime, OrderByType.Asc)
|
.First();
|
}
|
}
|
|
/// <summary>
|
/// 通过 DataCode 查询时间区间内的最后一条数据
|
/// </summary>
|
public Entity.MonitorRecord GetLastByDataCodeOfTimeRange(string DataCode, DateTime StartTime, DateTime EndTime)
|
{
|
if (StartTime > EndTime)
|
return default;
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Queryable<Entity.MonitorRecord>()
|
.Where(x => x.DataTime > StartTime.AddSeconds(-1) && x.DataTime < EndTime.AddSeconds(1))
|
.Where(x => x.DataCode == DataCode)
|
.OrderBy(x => x.DataTime, OrderByType.Desc)
|
.First();
|
}
|
}
|
|
/// <summary>
|
/// 通过 DataCode 查询时间区间内受限的数据
|
/// </summary>
|
public List<Entity.MonitorRecord> GetLimitByDataCodeOfTimeRange(string DataCode, DateTime StartTime, DateTime EndTime, int Limit)
|
{
|
if (StartTime > EndTime)
|
return default;
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
var list = db.Queryable<Entity.MonitorRecord>()
|
.Where(x => x.DataTime > StartTime.AddSeconds(-1) && x.DataTime < EndTime.AddSeconds(1))
|
.Where(x => x.DataCode == DataCode)
|
.OrderBy(x => x.DataTime, OrderByType.Asc)
|
.ToList();
|
return list?.ToLimitList(Limit);
|
}
|
}
|
|
#endregion
|
|
#region 日期区间
|
|
/// <summary>
|
/// 通过 DataCode 查询日期区间内的数据
|
/// </summary>
|
public List<Entity.MonitorRecord> GetByDataCodeOfDayRange(string DataCode, DateTime StartDay, DateTime EndDay)
|
{
|
if (StartDay.Date > EndDay.Date)
|
return default;
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Queryable<Entity.MonitorRecord>()
|
.Where(x => x.DataTime >= StartDay.Date && x.DataTime < EndDay.AddDays(1).Date)
|
.Where(x => x.DataCode == DataCode)
|
.OrderBy(x => x.DataTime, OrderByType.Asc)
|
.ToList();
|
}
|
}
|
|
/// <summary>
|
/// 通过 DataCode 查询日期区间内的第一条数据
|
/// </summary>
|
public Entity.MonitorRecord GetFirstByDataCodeOfDayRange(string DataCode, DateTime StartDay, DateTime EndDay)
|
{
|
if (StartDay.Date > EndDay.Date)
|
return default;
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Queryable<Entity.MonitorRecord>()
|
.Where(x => x.DataTime >= StartDay.Date && x.DataTime < EndDay.AddDays(1).Date)
|
.Where(x => x.DataCode == DataCode)
|
.OrderBy(x => x.DataTime, OrderByType.Asc)
|
.First();
|
}
|
}
|
|
/// <summary>
|
/// 通过 DataCode 查询日期区间内的最后一条数据
|
/// </summary>
|
public Entity.MonitorRecord GetLastByDataCodeOfDayRange(string DataCode, DateTime StartDay, DateTime EndDay)
|
{
|
if (StartDay.Date > EndDay.Date)
|
return default;
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Queryable<Entity.MonitorRecord>()
|
.Where(x => x.DataTime >= StartDay.Date && x.DataTime < EndDay.AddDays(1).Date)
|
.Where(x => x.DataCode == DataCode)
|
.OrderBy(x => x.DataTime, OrderByType.Desc)
|
.First();
|
}
|
}
|
|
/// <summary>
|
/// 通过 DataCode 查询日期区间内受限的数据
|
/// </summary>
|
public List<Entity.MonitorRecord> GetLimitByDataCodeOfDayRange(string DataCode, DateTime StartDay, DateTime EndDay, int Limit)
|
{
|
if (StartDay.Date > EndDay.Date)
|
return default;
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
var list = db.Queryable<Entity.MonitorRecord>()
|
.Where(x => x.DataTime >= StartDay.Date && x.DataTime < EndDay.AddDays(1).Date)
|
.Where(x => x.DataCode == DataCode)
|
.OrderBy(x => x.DataTime, OrderByType.Asc)
|
.ToList();
|
return list?.ToLimitList(Limit);
|
}
|
}
|
|
#endregion
|
|
#region 日期
|
|
/// <summary>
|
/// 通过 DataCode 获取某日的数据
|
/// </summary>
|
public List<Entity.MonitorRecord> GetByDataCodeOfDay(string DataCode, DateTime Day)
|
{
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Queryable<Entity.MonitorRecord>()
|
.Where(x => x.DataTime >= Day.Date && x.DataTime < Day.AddDays(1).Date)
|
.Where(x => x.DataCode == DataCode)
|
.OrderBy(x => x.DataTime, OrderByType.Asc)
|
.ToList();
|
}
|
}
|
|
/// <summary>
|
/// 通过 DataCode 获取某日的第一条数据
|
/// </summary>
|
public Entity.MonitorRecord GetFirstByDataCodeOfDay(string DataCode, DateTime Day)
|
{
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Queryable<Entity.MonitorRecord>()
|
.Where(x => x.DataTime >= Day.Date && x.DataTime < Day.AddDays(1).Date)
|
.Where(x => x.DataCode == DataCode)
|
.OrderBy(x => x.DataTime, OrderByType.Asc)
|
.First();
|
}
|
}
|
|
/// <summary>
|
/// 通过 DataCode 获取某日的最后一条数据
|
/// </summary>
|
public Entity.MonitorRecord GetLastByDataCodeOfDay(string DataCode, DateTime Day)
|
{
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Queryable<Entity.MonitorRecord>()
|
.Where(x => x.DataTime >= Day.Date && x.DataTime < Day.AddDays(1).Date)
|
.Where(x => x.DataCode == DataCode)
|
.OrderBy(x => x.DataTime, OrderByType.Desc)
|
.First();
|
}
|
}
|
|
/// <summary>
|
/// 通过 DataCode 获取某日受限的数据
|
/// </summary>
|
public List<Entity.MonitorRecord> GetLimitByDataCodeOfDay(string DataCode, DateTime Day, int Limit)
|
{
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
var list = db.Queryable<Entity.MonitorRecord>()
|
.Where(x => x.DataTime >= Day.Date && x.DataTime < Day.AddDays(1).Date)
|
.Where(x => x.DataCode == DataCode)
|
.OrderBy(x => x.DataTime, OrderByType.Asc)
|
.ToList();
|
return list?.ToLimitList(Limit);
|
}
|
}
|
|
#endregion
|
|
#region 时间
|
|
/// <summary>
|
/// 通过时间获取
|
/// </summary>
|
public Entity.MonitorRecord GetByDataCodeOfTime(string DataCode, DateTime Time)
|
{
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Queryable<Entity.MonitorRecord>()
|
.Where(x => x.DataTime > Time.AddSeconds(-1) && x.DataTime < Time.AddSeconds(1))
|
.Where(x => x.DataCode == DataCode)
|
.OrderBy(x => x.DataTime, OrderByType.Asc)
|
.First();
|
}
|
}
|
|
#endregion
|
|
#endregion
|
|
#region InsertOrUpdate
|
|
/// <summary>
|
/// 插入
|
/// </summary>
|
public bool InsertOrUpdate(Entity.MonitorRecord entity)
|
{
|
if (entity == null)
|
return false;
|
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Insertable(entity).ExecuteCommand() > 0;
|
|
//var sql = CreateSql();
|
//var paras = CreateParameters(entity);
|
//return db.Ado.ExecuteCommand(sql, paras) > 0;
|
}
|
}
|
|
/// <summary>
|
/// 批量插入
|
/// </summary>
|
public bool InsertOrUpdate(List<Entity.MonitorRecord> list)
|
{
|
if (list == null || list.Count < 1)
|
return false;
|
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Insertable(list).ExecuteCommand() > 0;
|
|
//try
|
//{
|
// db.Ado.BeginTran();
|
// var sql = CreateSql();
|
// foreach (var item in list)
|
// {
|
// var paras = CreateParameters(item);
|
// var bol = db.Ado.ExecuteCommand(sql, paras) > 0;
|
// if (!bol)
|
// {
|
// db.Ado.RollbackTran();
|
// return false;
|
// }
|
// }
|
// db.Ado.CommitTran();
|
// return true;
|
//}
|
//catch
|
//{
|
// db.Ado.RollbackTran();
|
// throw;
|
//}
|
}
|
}
|
|
|
//创建sql
|
private string CreateSql()
|
{
|
var sb = new StringBuilder();
|
sb.AppendLine("INSERT INTO MonitorRecord");
|
sb.AppendLine($" VALUES(@DataCode,@DataTime,@DataValue,@ReceiptTime)");
|
sb.AppendLine("ON CONFLICT(DataCode, DataTime, ReceiptTime) DO UPDATE");
|
sb.AppendLine("SET DataValue = excluded.DataValue;");
|
return sb.ToString();
|
}
|
|
//创建参数
|
private List<SugarParameter> CreateParameters(Entity.MonitorRecord entity)
|
{
|
var list = new List<SugarParameter>();
|
list.Add(new SugarParameter("@DataCode", entity.DataCode));
|
list.Add(new SugarParameter("@DataTime", entity.DataTime));
|
list.Add(new SugarParameter("@DataValue", entity.DataValue));
|
list.Add(new SugarParameter("@ReceiptTime", entity.ReceiptTime));
|
return list;
|
}
|
|
#endregion
|
|
|
}
|
}
|
