public static async Task InsertSensorHistory(SensorOutput sensorInput)
{
// Insert newly received data into DB, table history
try
{
using (MySqlConnection conn = GetConnection())
{
string tlat, tlon, ttime;
if (sensorInput.DevEUI_uplink.lat is null)
{
tlat = sensorInput.DevEUI_uplink.latLrrLAT;
}
else
{
tlat = sensorInput.DevEUI_uplink.lat;
}
if (sensorInput.DevEUI_uplink.lon is null)
{
tlon = sensorInput.DevEUI_uplink.latLrrLON;
}
else
{
tlon = sensorInput.DevEUI_uplink.lon;
}
if (sensorInput.DevEUI_uplink.time is null)
{
ttime = sensorInput.DevEUI_uplink.Time;
}
else
{
ttime = sensorInput.DevEUI_uplink.time;
}
// It is neccessary to convert the format to datetime accepted by mysql
string format = "yyyy-mm-dd hh:mm:ss";
string time = Convert.ToDateTime(ttime).ToString(format);
conn.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO `history` (`devid`, `time`, `lat`, `lon`, `alt`, `channel`, `rssi`, `snr`, `freq`, `port`, `payload_hex`)" +
"VALUES ('" + sensorInput.DevEUI_uplink.devid + "', '" + ttime + "', '" + tlat + "', '" + tlon + "', '" + sensorInput.DevEUI_uplink.alt + "', '" + sensorInput.DevEUI_uplink.channel +
"', '" + sensorInput.DevEUI_uplink.rssi + "', '" + sensorInput.DevEUI_uplink.snr + "', '" + sensorInput.DevEUI_uplink.freq + "', '" + sensorInput.DevEUI_uplink.port + "', '" + sensorInput.DevEUI_uplink.payload_hex + "');", conn);
await cmd.ExecuteNonQueryAsync();
}
}
catch (Exception e)
{
string t = e.Message;
}
}
public static async Task InsertNewSensorId(SensorOutput sensorInput)
{
// If the sensor in new in DB, then add it to the table sensors
try
{
using (MySqlConnection conn = GetConnection())
{
string tlat, tlon, ttime;
if (sensorInput.DevEUI_uplink.lat is null)
{
tlat = sensorInput.DevEUI_uplink.latLrrLAT;
}
else
{
tlat = sensorInput.DevEUI_uplink.lat;
}
if (sensorInput.DevEUI_uplink.lon is null)
{
tlon = sensorInput.DevEUI_uplink.latLrrLON;
}
else
{
tlon = sensorInput.DevEUI_uplink.lon;
}
if (sensorInput.DevEUI_uplink.time is null)
{
ttime = sensorInput.DevEUI_uplink.Time;
}
else
{
ttime = sensorInput.DevEUI_uplink.time;
}
string format = "yyyy-mm-dd hh:mm:ss";
string time = Convert.ToDateTime(ttime).ToString(format);
conn.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO `sensors` (`devid`, `tennantid`, `name`, `Last_time`, `lat`, `lon`, `alt`, `channel`)" +
"VALUES ('" + sensorInput.DevEUI_uplink.devid + "', '1', '" + sensorInput.DevEUI_uplink.devid + "', '" + ttime + "', '" + tlat + "', '" + tlon + "', '" +
sensorInput.DevEUI_uplink.alt + "', '" + sensorInput.DevEUI_uplink.channel + "');", conn);
await cmd.ExecuteNonQueryAsync();
}
}
catch (Exception e)
{
string t = e.Message;
}
}
public static async Task UpdateSensorTime(SensorOutput sensorInput)
{
// If the sensor is already known, just update its last_time and geo data
try
{
using (MySqlConnection conn = GetConnection())
{
string tlat, tlon, ttime;
if (sensorInput.DevEUI_uplink.lat is null)
{
tlat = sensorInput.DevEUI_uplink.latLrrLAT;
}
else
{
tlat = sensorInput.DevEUI_uplink.lat;
}
if (sensorInput.DevEUI_uplink.lon is null)
{
tlon = sensorInput.DevEUI_uplink.latLrrLON;
}
else
{
tlon = sensorInput.DevEUI_uplink.lon;
}
if (sensorInput.DevEUI_uplink.time is null)
{
ttime = sensorInput.DevEUI_uplink.Time;
}
else
{
ttime = sensorInput.DevEUI_uplink.time;
}
string format = "yyyy-mm-dd hh:mm:ss";
string time = Convert.ToDateTime(ttime).ToString(format);
conn.Open();
MySqlCommand cmd = new MySqlCommand("UPDATE `sensors` SET last_time='" + ttime + "', lat='" + tlat + "', lon='" + tlon + "', alt='" + sensorInput.DevEUI_uplink.alt + "' WHERE devid='" + sensorInput.DevEUI_uplink.devid + "';", conn);
await cmd.ExecuteNonQueryAsync();
}
}
catch (Exception e)
{
string t = e.Message;
}
}
private static async Task WriteReceivedBatchToInfluxDbAsync(string sensorIpAddress, int sensorPort, string data)
{
// TODO: Implement buffered writing
var sensorOutput = new SensorOutput
{
Timestamp = DateTime.SpecifyKind(DateTime.UtcNow, DateTimeKind.Utc),
SensorIpAddress = sensorIpAddress,
SensorPort = sensorPort,
Data = data
};
var outputs = new List <SensorOutput>()
{
sensorOutput
};
// await Task.Delay(2000);
await _influxClient.WriteAsync(AppSettings.InfluxDatabaseName, _measurementName, outputs);
}