namespace Yw.DAL
|
{
|
/// <summary>
|
///
|
/// </summary>
|
public partial class HealthQuotaEvaluationRecord
|
{
|
/// <summary>
|
///
|
/// </summary>
|
public ConnectionConfig ConnectionConfig
|
{
|
get { return ConfigHelper.RecordConnectionConfig; }
|
}
|
|
#region Query
|
|
#region 日期
|
|
/// <summary>
|
/// 通过 SignalID 获取某天的全部数据
|
/// </summary>
|
public List<Entity.HealthQuotaEvaluationRecord> GetBySignalIDOfDay(long SignalID, DateTime Day)
|
{
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Queryable<Entity.HealthQuotaEvaluationRecord>()
|
.Where(x => x.SignalID == SignalID)
|
.Where(x => x.EvaluateTime >= Day.Date && x.EvaluateTime < Day.Date.AddDays(1))
|
.OrderBy(x => x.EvaluateTime).ToList();
|
}
|
}
|
|
/// <summary>
|
/// 通过 SignalID 获取某天限制数量的全部数据
|
/// </summary>
|
public List<Entity.HealthQuotaEvaluationRecord> GetLimitBySignalIDOfDay(long SignalID, DateTime Day, int Limit)
|
{
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
var list = db.Queryable<Entity.HealthQuotaEvaluationRecord>()
|
.Where(x => x.SignalID == SignalID)
|
.Where(x => x.EvaluateTime >= Day.Date && x.EvaluateTime < Day.Date.AddDays(1))
|
.OrderBy(x => x.EvaluateTime).ToList();
|
return GetFixedList(list, Limit);
|
}
|
}
|
|
#endregion
|
|
#region 日期区间
|
|
/// <summary>
|
/// 通过 SignalID 获取日期区间内全部数据
|
/// </summary>
|
public List<Entity.HealthQuotaEvaluationRecord> GetBySignalIDOfDayRange(long SignalID, DateTime StartDay, DateTime EndDay)
|
{
|
if (StartDay.Date > EndDay.Date)
|
{
|
return default;
|
}
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Queryable<Entity.HealthQuotaEvaluationRecord>()
|
.Where(x => x.SignalID == SignalID)
|
.Where(x => x.EvaluateTime >= StartDay.Date && x.EvaluateTime < EndDay.Date.AddDays(1))
|
.OrderBy(x => x.EvaluateTime).ToList();
|
}
|
}
|
|
/// <summary>
|
/// 通过 SignalID 获取日期区间内限制数量的数据
|
/// </summary>
|
public List<Entity.HealthQuotaEvaluationRecord> GetLimitBySignalIDOfDayRange(long SignalID, DateTime StartDay, DateTime EndDay, int Limit)
|
{
|
if (StartDay.Date > EndDay.Date)
|
return default;
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
var list = db.Queryable<Entity.HealthQuotaEvaluationRecord>()
|
.Where(x => x.SignalID == SignalID)
|
.Where(x => x.EvaluateTime >= StartDay.Date && x.EvaluateTime < EndDay.Date.AddDays(1))
|
.OrderBy(x => x.EvaluateTime).ToList();
|
return GetFixedList(list, Limit);
|
}
|
}
|
|
#endregion
|
|
#region 时间区间
|
|
/// <summary>
|
/// 通过 SignalID 获取时间区间内全部数据
|
/// </summary>
|
public List<Entity.HealthQuotaEvaluationRecord> GetBySignalIDOfTimeRange(long SignalID, DateTime StartTime, DateTime EndTime)
|
{
|
if (StartTime > EndTime)
|
{
|
return default;
|
}
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Queryable<Entity.HealthQuotaEvaluationRecord>()
|
.Where(x => x.SignalID == SignalID)
|
.Where(x => x.EvaluateTime > StartTime.AddSeconds(-1) && x.EvaluateTime < EndTime.AddSeconds(1))
|
.OrderBy(x => x.EvaluateTime).ToList();
|
}
|
}
|
|
/// <summary>
|
/// 通过 SignalID 获取时间区间内限制数量的全部数据
|
/// </summary>
|
public List<Entity.HealthQuotaEvaluationRecord> GetLimitBySignalIDOfTimeRange(long SignalID, DateTime StartTime, DateTime EndTime, int Limit)
|
{
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
var list = db.Queryable<Entity.HealthQuotaEvaluationRecord>()
|
.Where(x => x.SignalID == SignalID)
|
.Where(x => x.EvaluateTime > StartTime.AddSeconds(-1) && x.EvaluateTime < EndTime.AddSeconds(1))
|
.OrderBy(x => x.EvaluateTime).ToList();
|
return GetFixedList(list, Limit);
|
}
|
}
|
|
#endregion
|
|
#region 时间
|
|
/// <summary>
|
/// 通过 SignalID 获取某一时间的数据
|
/// </summary>
|
public Entity.HealthQuotaEvaluationRecord GetBySignalIDOfTime(long SignalID, DateTime Time)
|
{
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Queryable<Entity.HealthQuotaEvaluationRecord>()
|
.Where(x => x.SignalID == SignalID)
|
.Where(x => x.EvaluateTime > Time.AddSeconds(-1) && x.EvaluateTime < Time.AddSeconds(1))
|
.OrderBy(x => x.EvaluateTime, OrderByType.Desc).First();
|
}
|
}
|
|
#endregion
|
|
#region 限制方法
|
|
//返回固定数量的列表(包含首位和末位、均匀分布)
|
private static List<T> GetFixedList<T>(List<T> s, int n)
|
{
|
if (s == null || s.Count < 1)
|
return s;
|
if (n <= 0)
|
return null;
|
if (n == 1)
|
return new List<T>() { s.First() };
|
if (s.Count <= n)
|
return s;
|
var r = new List<T>();
|
float step = (s.Count - 1f) / (n - 1f);
|
for (int i = 0; i < n; i++)
|
{
|
r.Add(s[(int)Math.Round(step * i)]);
|
}
|
return r;
|
}
|
|
#endregion
|
|
#region 步长
|
|
/// <summary>
|
/// 通过 SignalID 获取时间区间内的步长数据
|
/// </summary>
|
public List<Entity.HealthQuotaEvaluationStepRecord> GetStepBySignalIDOfTimeRange(long SignalID, DateTime StartTime, DateTime EndTime, int Step)
|
{
|
var sb = new StringBuilder();
|
sb.AppendLine($"select signalid,time_bucket('{Step} second',evaluatetime) as recordtime,");
|
sb.AppendLine("first(evaluatemodelid,evaluatetime) as recordmodelid,");
|
sb.AppendLine("first(evaluatemode,evaluatetime) as recordmode,");
|
sb.AppendLine("first(evaluatevalue,evaluatetime) as recordvalue");
|
sb.AppendLine("from health_quota_evaluation_record");
|
sb.AppendLine($"where signalid='{SignalID}' and evaluatetime>'{StartTime.AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss")}' and evaluatetime<'{EndTime.AddSeconds(1).ToString("yyyy-MM-dd HH:mm:ss")}'");
|
sb.AppendLine("group by signalid,recordtime");
|
sb.AppendLine("order by recordtime");
|
var strSql = sb.ToString();
|
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Ado.SqlQuery<Entity.HealthQuotaEvaluationStepRecord>(strSql).ToList();
|
}
|
}
|
|
/// <summary>
|
/// 通过 SignalID 获取时间区间内的步长填充数据
|
/// </summary>
|
public List<Entity.HealthQuotaEvaluationStepRecord> GetStepFillBySignalIDOfTimeRange(long SignalID, DateTime StartTime, DateTime EndTime, int Step)
|
{
|
var sb = new StringBuilder();
|
sb.AppendLine($"select signalid,time_bucket_gapfill('{Step} second',evaluatetime) as recordtime,");
|
sb.AppendLine("first(evaluatemodelid,evaluatetime) as recordmodelid,");
|
sb.AppendLine("first(evaluatemode,evaluatetime) as recordmode,");
|
sb.AppendLine("first(evaluatevalue,evaluatetime) as recordvalue");
|
sb.AppendLine("from health_quota_evaluation_record");
|
sb.AppendLine($"where signalid='{SignalID}' and evaluatetime>'{StartTime.AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss")}' and evaluatetime<'{EndTime.AddSeconds(1).ToString("yyyy-MM-dd HH:mm:ss")}'");
|
sb.AppendLine("group by signalid,recordtime");
|
sb.AppendLine("order by recordtime");
|
var strSql = sb.ToString();
|
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
return db.Ado.SqlQuery<Entity.HealthQuotaEvaluationStepRecord>(strSql).ToList();
|
}
|
}
|
|
#endregion
|
|
#endregion
|
|
#region InsertOrUpdate
|
|
/// <summary>
|
/// 插入
|
/// </summary>
|
public bool InsertOrUpdate(Entity.HealthQuotaEvaluationRecord entity)
|
{
|
if (entity == null)
|
return false;
|
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
var sql = CreateSql();
|
var paras = CreateParameters(entity);
|
return db.Ado.ExecuteCommand(sql, paras) > 0;
|
}
|
}
|
|
/// <summary>
|
/// 批量插入
|
/// </summary>
|
public bool InsertOrUpdate(List<Entity.HealthQuotaEvaluationRecord> list)
|
{
|
if (list == null || list.Count < 1)
|
return false;
|
using (var db = new SqlSugarClient(ConnectionConfig))
|
{
|
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 health_quota_evaluation_record");
|
sb.AppendLine($" VALUES(@signalid,@evaluatemodelid,@evaluatemode,@evaluatetime,@evaluatevalue)");
|
sb.AppendLine("ON CONFLICT(signalid, evaluatetime) DO UPDATE");
|
sb.AppendLine("SET evaluatemodelid = excluded.evaluatemodelid,evaluatemode = excluded.evaluatemode,evaluatevalue = excluded.evaluatevalue;");
|
return sb.ToString();
|
}
|
|
//创建参数
|
private List<SugarParameter> CreateParameters(Entity.HealthQuotaEvaluationRecord entity)
|
{
|
var list = new List<SugarParameter>();
|
list.Add(new SugarParameter("@signalid", entity.SignalID));
|
list.Add(new SugarParameter("@evaluatemodelid", entity.EvaluateModelID));
|
list.Add(new SugarParameter("@evaluatemode", entity.EvaluateMode));
|
list.Add(new SugarParameter("@evaluatetime", entity.EvaluateTime));
|
list.Add(new SugarParameter("@evaluatevalue", entity.EvaluateValue));
|
return list;
|
}
|
|
#endregion
|
|
|
|
}
|
}
|
