/// <summary>
/// 转换获取到的值
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="devid"></param>
/// <returns></returns>
private List <TelemetryDataDto> SqlToTDD(PinusConnection db, string sql, Guid devid)
{
List <TelemetryDataDto> dt = new List <TelemetryDataDto>();
var kts = db.CreateCommand(sql).ExecuteReader().ToList <(long keyid, string keyname, string datatype)>();
kts.ForEach(kf =>
{
TelemetryDataDto telemetry = new TelemetryDataDto()
{
KeyName = kf.keyname
};
try
{
DataType datatype = (DataType)Convert.ToInt32(kf.datatype);
var queryvalue = db.CreateCommand($"SELECT {datatype.ToFieldName()},tstamp from {GetDevTableName(devid)} where devid ={kf.keyid} order by tstamp desc limit 1 ")
.ExecuteReader().ToList <(object obj, DateTime dt)>();
telemetry.AttachValue(datatype, queryvalue.FirstOrDefault().obj);
telemetry.DateTime = queryvalue.FirstOrDefault().dt;
}
catch (Exception ex)
{
_logger.LogError(ex, $"{telemetry.KeyName}遇到{ex.Message}, sql:{sql}");
}
if (!string.IsNullOrEmpty(telemetry.KeyName))
{
dt.Add(telemetry);
}
});
return(dt);
}
private long?GetKeyId(Guid devid, string keyname, PinusConnection _pinus)
{
return(_pinus.CreateCommand($"select devid from sys_dev where tabname='{GetDevTableName(devid)}' and devname='{keyname}'").ExecuteScalar() as long?);
}
public Task <bool> StoreTelemetryAsync(RawMsg msg)
{
bool result = false;
PinusConnection _pinus = _pinuspool.Get();
try
{
string tablename = GetDevTableName(msg);
var _havedev = _pinus.CreateCommand($"select count(*) from sys_table where tabname = '{tablename}'").ExecuteScalar() as long?;
if ((long)_havedev == 0)
{
_pinus.CreateCommand($"CREATE TABLE {tablename} (devid bigint,tstamp datetime,value_type tinyint,value_boolean bool, value_string string, value_long bigint,value_datetime datetime,value_double double)").ExecuteNonQuery();
}
msg.MsgBody.ToList().ForEach(kp =>
{
if (kp.Value != null)
{
List <PinusParameter> parameters = new List <PinusParameter>();
TelemetryData tdata = new TelemetryData()
{
DateTime = DateTime.Now, DeviceId = msg.DeviceId, KeyName = kp.Key, Value_DateTime = new DateTime(1970, 1, 1)
};
tdata.FillKVToMe(kp);
string _type = "";
var cmd = _pinus.CreateCommand();
switch (tdata.Type)
{
case DataType.Boolean:
_type = "value_boolean";
cmd.Parameters.Add(new PinusParameter(_type, tdata.Value_Boolean));
break;
case DataType.String:
_type = "value_string";
cmd.Parameters.Add(new PinusParameter(_type, tdata.Value_String));
break;
case DataType.Long:
_type = "value_long";
cmd.Parameters.Add(new PinusParameter(_type, tdata.Value_Long));
break;
case DataType.Double:
_type = "value_double";
cmd.Parameters.Add(new PinusParameter(_type, tdata.Value_Double));
break;
case DataType.Json:
_type = "value_string";
cmd.Parameters.Add(new PinusParameter(_type, tdata.Value_String));
break;
case DataType.XML:
_type = "value_string";
cmd.Parameters.Add(new PinusParameter(_type, tdata.Value_XML));
break;
case DataType.Binary:
_type = "value_string";
cmd.Parameters.Add(new PinusParameter(_type, Hex.ToHexString(tdata.Value_Binary)));
break;
case DataType.DateTime:
_type = "value_datetime";
cmd.Parameters.Add(new PinusParameter(_type, tdata.Value_DateTime));
break;
default:
break;
}
long?_keyid = GetKeyId(msg.DeviceId, kp.Key, _pinus);
if (!_keyid.HasValue)
{
long?maxid = _pinus.CreateCommand($"select max(devid) from sys_dev where tabname='{tablename}'").ExecuteScalar() as long?;
long currdev = maxid.GetValueOrDefault() + 1;
_pinus.CreateCommand($"INSERT INTO sys_dev(tabname, devname,devid,expand) VALUES('{tablename}','{kp.Key}',{currdev},'{(int)tdata.Type}')").ExecuteNonQuery();
_keyid = GetKeyId(msg.DeviceId, kp.Key, _pinus);
}
cmd.CommandText = $"INSERT INTO {tablename} (devid,tstamp,value_type,{_type}) VALUES({_keyid}, now(), {(int)tdata.Type}, @{_type})";
_logger.LogInformation(cmd.CommandText);
try
{
int dt = cmd.ExecuteNonQuery();
result = dt > 0;
}
catch (Exception ex)
{
_logger.LogError(ex, $"{msg.DeviceId}数据处理失败{ex.Message} {ex.InnerException?.Message}");
}
}
});
}
catch (Exception ex)
{
_logger.LogError(ex, $"{msg.DeviceId}数据处理失败{ex.Message} {ex.InnerException?.Message} ");
}
finally
{
_pinuspool.Return(_pinus);
}
return(Task.FromResult(result));
}