315 lines
14 KiB
C#
315 lines
14 KiB
C#
using DTS.Common.Enums.Sensors;
|
|
using DTS.Common.Interface.Sensors;
|
|
using DTS.Common.Storage;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Globalization;
|
|
using System.Linq;
|
|
|
|
namespace DTS.SensorDB
|
|
{
|
|
/// <summary>
|
|
/// represents a change to the offset tolerance in the db
|
|
/// it implements ISensorChange so can be easily commited and read
|
|
/// </summary>
|
|
public class OffsetToleranceChange : IOffsetToleranceChange
|
|
{
|
|
public int SensorId { get; set; }
|
|
public double LowMvValue
|
|
{
|
|
get => double.Parse(Value1, NumberStyles.Any, CultureInfo.InvariantCulture);
|
|
set => Value1 = value.ToString(CultureInfo.InvariantCulture);
|
|
}
|
|
|
|
public double HighMvValue
|
|
{
|
|
get => double.Parse(Value2, NumberStyles.Any, CultureInfo.InvariantCulture);
|
|
set => Value2 = value.ToString(CultureInfo.InvariantCulture);
|
|
}
|
|
|
|
public double LowEUValue
|
|
{
|
|
get => double.Parse(Value3, NumberStyles.Any, CultureInfo.InvariantCulture);
|
|
set => Value3 = value.ToString(CultureInfo.InvariantCulture);
|
|
}
|
|
|
|
public double HighEUValue
|
|
{
|
|
get => double.Parse(Value4, NumberStyles.Any, CultureInfo.InvariantCulture);
|
|
set => Value4 = value.ToString(CultureInfo.InvariantCulture);
|
|
}
|
|
|
|
public SensorChangeTypes ChangeType => SensorChangeTypes.OffsetTolerance;
|
|
|
|
public string Value1 { get; private set; } = 0D.ToString(CultureInfo.InvariantCulture);
|
|
|
|
public string Value2 { get; private set; } = 0D.ToString(CultureInfo.InvariantCulture);
|
|
|
|
public string Value3 { get; private set; } = 0D.ToString(CultureInfo.InvariantCulture);
|
|
|
|
public string Value4 { get; private set; } = 0D.ToString(CultureInfo.InvariantCulture);
|
|
|
|
public const int NEW_RECORD = -1;
|
|
public int RecordId { get; set; } = NEW_RECORD;
|
|
|
|
public DateTime TimeStamp { get; set; }
|
|
|
|
public string UserName { get; set; }
|
|
public OffsetToleranceChange() { }
|
|
|
|
public OffsetToleranceChange(string value1, string value2, string value3, string value4)
|
|
{
|
|
Value1 = value1;
|
|
Value2 = value2;
|
|
Value3 = value3;
|
|
Value4 = value4;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// this class simplifies sensor change histories and types in the db
|
|
/// </summary>
|
|
public abstract class SensorChangeTypeHelper
|
|
{
|
|
private static Dictionary<SensorChangeTypes, int> _mappingCache = null;
|
|
private static object MyLock = new object();
|
|
|
|
public static void Commit(ISensorData sensor, ISensorChange[] changes)
|
|
{
|
|
using (var sql = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
sql.CommandType = CommandType.StoredProcedure;
|
|
sql.CommandText = "sp_SensorsChangeHistoryDelete";
|
|
sql.Parameters.Add(new SqlParameter("@RecordId", SqlDbType.Int) { Value = DBNull.Value });
|
|
sql.Parameters.Add(new SqlParameter("@SensorId", SqlDbType.Int) { Value = sensor.DatabaseId });
|
|
sql.ExecuteNonQuery();
|
|
}
|
|
finally
|
|
{
|
|
sql.Connection.Dispose();
|
|
}
|
|
}
|
|
|
|
if (changes.Any())
|
|
{
|
|
foreach (var change in changes)
|
|
{
|
|
using (var sql = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
sql.CommandType = CommandType.StoredProcedure;
|
|
sql.CommandText = "sp_SensorsChangeHistoryInsert";
|
|
|
|
sql.Parameters.Add(new SqlParameter("@SensorId", SqlDbType.Int)
|
|
{ Value = sensor.DatabaseId });
|
|
sql.Parameters.Add(new SqlParameter("@ChangeType", SqlDbType.Int)
|
|
{ Value = GetAllSensorChangeTypes()[change.ChangeType] });
|
|
sql.Parameters.Add(new SqlParameter("@Timestamp", SqlDbType.DateTime)
|
|
{ Value = change.TimeStamp });
|
|
sql.Parameters.Add(new SqlParameter("@Username", SqlDbType.NVarChar)
|
|
{ Value = change.UserName });
|
|
sql.Parameters.Add(new SqlParameter("@Value1", SqlDbType.NVarChar) { Value = change.Value1 });
|
|
sql.Parameters.Add(new SqlParameter("@Value2", SqlDbType.NVarChar) { Value = change.Value2 });
|
|
sql.Parameters.Add(new SqlParameter("@Value3", SqlDbType.NVarChar) { Value = change.Value3 });
|
|
sql.Parameters.Add(new SqlParameter("@Value4", SqlDbType.NVarChar) { Value = change.Value4 });
|
|
|
|
var errorNumber = new SqlParameter("@errorNumber", SqlDbType.Int)
|
|
{ Direction = ParameterDirection.Output };
|
|
sql.Parameters.Add(errorNumber);
|
|
|
|
var errorMessage = new SqlParameter("@errorMessage", SqlDbType.NVarChar, 255)
|
|
{ Direction = ParameterDirection.Output };
|
|
sql.Parameters.Add(errorMessage);
|
|
|
|
var new_id = new SqlParameter("@new_id", SqlDbType.Int)
|
|
{ Direction = ParameterDirection.Output };
|
|
sql.Parameters.Add(new_id);
|
|
|
|
sql.ExecuteNonQuery();
|
|
|
|
if (null != errorNumber.Value && !DBNull.Value.Equals(errorNumber.Value))
|
|
{
|
|
if (0 != Convert.ToInt32(errorNumber.Value))
|
|
{
|
|
throw new Exception((string)errorMessage.Value);
|
|
}
|
|
}
|
|
}
|
|
finally
|
|
{
|
|
sql.Connection.Dispose();
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// writes a single change to the db
|
|
/// </summary>
|
|
/// <param name="change"></param>
|
|
public static void Commit(ISensorChange change)
|
|
{
|
|
//can't commit when id < 0...
|
|
if (change.SensorId < 0)
|
|
{
|
|
return;
|
|
}
|
|
using (var sql = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
sql.CommandType = CommandType.StoredProcedure;
|
|
sql.CommandText = "sp_SensorsChangeHistoryInsert";
|
|
sql.Parameters.Add(new SqlParameter("@SensorId", SqlDbType.Int) { Value = change.SensorId });
|
|
sql.Parameters.Add(new SqlParameter("@UserName", SqlDbType.NVarChar) { Value = change.UserName });
|
|
sql.Parameters.Add(new SqlParameter("@Timestamp", SqlDbType.DateTime) { Value = change.TimeStamp });
|
|
sql.Parameters.Add(new SqlParameter("@ChangeType", SqlDbType.Int) { Value = GetAllSensorChangeTypes()[SensorChangeTypes.OffsetTolerance] });
|
|
sql.Parameters.Add(new SqlParameter("@Value1", SqlDbType.NVarChar) { Value = change.Value1 });
|
|
sql.Parameters.Add(new SqlParameter("@Value2", SqlDbType.NVarChar) { Value = change.Value2 });
|
|
sql.Parameters.Add(new SqlParameter("@Value3", SqlDbType.NVarChar) { Value = change.Value3 });
|
|
sql.Parameters.Add(new SqlParameter("@Value4", SqlDbType.NVarChar) { Value = change.Value4 });
|
|
|
|
var errorNumber = new SqlParameter("@errorNumber", SqlDbType.Int)
|
|
{ Direction = ParameterDirection.Output };
|
|
sql.Parameters.Add(errorNumber);
|
|
|
|
var errorMessage = new SqlParameter("@errorMessage", SqlDbType.NVarChar, 255)
|
|
{ Direction = ParameterDirection.Output };
|
|
sql.Parameters.Add(errorMessage);
|
|
|
|
var newId = new SqlParameter("@new_id", SqlDbType.Int) { Direction = ParameterDirection.Output };
|
|
sql.Parameters.Add(newId);
|
|
|
|
sql.ExecuteNonQuery();
|
|
|
|
if (null != errorNumber.Value && !DBNull.Value.Equals(errorNumber.Value))
|
|
{
|
|
if (0 != Convert.ToInt32(errorNumber.Value))
|
|
{
|
|
throw new Exception((string)errorMessage.Value);
|
|
}
|
|
}
|
|
}
|
|
finally
|
|
{
|
|
sql.Connection.Dispose();
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// returns all sensor change history for sensor
|
|
/// </summary>
|
|
/// <param name="sensor"></param>
|
|
/// <returns></returns>
|
|
public static ISensorChange[] GetAllSensorChanges(ISensorData sensor)
|
|
{
|
|
var list = new List<ISensorChange>();
|
|
var lookup = SensorChangeTypeHelper.GetAllSensorChangeTypes();
|
|
var intToType = new Dictionary<int, SensorChangeTypes>();
|
|
using (var e = lookup.GetEnumerator())
|
|
{
|
|
while (e.MoveNext())
|
|
{
|
|
intToType[e.Current.Value] = e.Current.Key;
|
|
}
|
|
}
|
|
using (var sql = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
sql.CommandType = CommandType.StoredProcedure;
|
|
sql.CommandText = "sp_SensorsChangeHistoryGet";
|
|
sql.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = sensor.DatabaseId });
|
|
sql.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar)
|
|
{ Value = sensor.SerialNumber });
|
|
|
|
var reader = sql.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
var recordId = Convert.ToInt32(reader["RecordId"]);
|
|
var sensorId = Convert.ToInt32(reader["SensorId"]);
|
|
var ichangeType = Convert.ToInt32(reader["ChangeType"]);
|
|
var userName = (string)reader["UserName"];
|
|
var timeStamp = Convert.ToDateTime(reader["Timestamp"]);
|
|
var value1 = (string)reader["Value1"];
|
|
var value2 = (string)reader["Value2"];
|
|
var value3 = (string)reader["Value3"];
|
|
var value4 = (string)reader["Value4"];
|
|
if (!intToType.ContainsKey(ichangeType))
|
|
{
|
|
throw new NotImplementedException($"unknown change type: {ichangeType}");
|
|
}
|
|
|
|
switch (intToType[ichangeType])
|
|
{
|
|
case SensorChangeTypes.OffsetTolerance:
|
|
list.Add(new OffsetToleranceChange(value1, value2, value3, value4)
|
|
{
|
|
RecordId = recordId,
|
|
SensorId = sensorId,
|
|
TimeStamp = timeStamp,
|
|
UserName = userName
|
|
});
|
|
break;
|
|
default:
|
|
throw new NotImplementedException(
|
|
$"Unknown change type: {intToType[ichangeType].ToString()}");
|
|
}
|
|
}
|
|
}
|
|
finally
|
|
{
|
|
sql.Connection.Dispose();
|
|
}
|
|
}
|
|
|
|
return list.ToArray();
|
|
}
|
|
/// <summary>
|
|
/// returns a lookup table keyed by change type with corresponding change id in db
|
|
/// this is designed to reduce db hits when there are many sensors to commit by allowing
|
|
/// caching the sensor change type lookup
|
|
/// </summary>
|
|
/// <param name="UseCache"></param>
|
|
/// <returns></returns>
|
|
public static Dictionary<SensorChangeTypes, int> GetAllSensorChangeTypes(bool UseCache = true)
|
|
{
|
|
lock (MyLock)
|
|
{
|
|
if (!UseCache || null == _mappingCache)
|
|
{
|
|
_mappingCache = new Dictionary<SensorChangeTypes, int>();
|
|
using (var cmd = DbOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
cmd.CommandText = "sp_SensorsChangeTypesGet";
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
|
|
var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
var id = Convert.ToInt32(reader["Id"]);
|
|
var sType = Convert.ToString(reader["Name"]);
|
|
if (Enum.TryParse(sType, out SensorChangeTypes changeType))
|
|
{
|
|
_mappingCache[changeType] = id;
|
|
}
|
|
}
|
|
}
|
|
finally
|
|
{
|
|
cmd.Connection.Dispose();
|
|
}
|
|
}
|
|
}
|
|
return _mappingCache;
|
|
}
|
|
}
|
|
}
|
|
}
|