public static void Update(this DatabaseWrapper conn, List <FactActivity> list)
{
if (list.Count == 0)
{
return;
}
foreach (FactActivity activity in list)
{
StringBuilder sb = new StringBuilder("UPDATE Fact_Activity SET ");
sb.Append($"evttime = {SqlConvert.Convert(activity.Evttime)}, ");
sb.Append($"inserttime = {SqlConvert.Convert(activity.Inserttime)}, ");
sb.Append($"serial = {SqlConvert.Convert(activity.Serial)}, ");
sb.Append($"evttype = {SqlConvert.Convert(activity.Evttype)}, ");
sb.Append($"lat = {SqlConvert.Convert(activity.Lat)}, ");
sb.Append($"lon = {SqlConvert.Convert(activity.Lon)}, ");
sb.Append($"src = {SqlConvert.Convert(activity.Src)}, ");
sb.Append($"accuracy = {SqlConvert.Convert(activity.Accuracy)}, ");
sb.Append($"address = {SqlConvert.Convert(activity.Address)}, ");
sb.Append($"geozones = {SqlConvert.Convert(activity.Geozones)}, ");
sb.Append($"trackerrecid = {SqlConvert.Convert(activity.Trackerrecid)} ");
sb.Append($"WHERE id = {SqlConvert.Convert(activity.Id)}");
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
}
public static List <FactActivity> GetActivities(this DatabaseWrapper conn, DateTime from, DateTime to)
{
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = $"SELECT * FROM Fact_Activity WHERE evttime BETWEEN '" +
$"{from.ToString("yyyy-MM-dd HH:mm:ss")}' AND '" +
$"{to.ToString("yyyy-MM-dd HH:mm:ss")}'";
List <FactActivity> data = new List <FactActivity>();
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
FactActivity obj = new FactActivity();
obj.Id = (long)reader["id"];
obj.Evttime = (DateTime)reader["evttime"];
obj.Inserttime = (DateTime)reader["inserttime"];
obj.Serial = (string)reader["serial"];
obj.Evttype = (string)reader["evttype"];
obj.Lat = (DBNull.Value == reader["lat"]) ? null : (double?)reader["lat"];
obj.Lon = (DBNull.Value == reader["lon"]) ? null : (double?)reader["lon"];
obj.Src = (string)reader["src"];
obj.Accuracy = (DBNull.Value == reader["accuracy"]) ? null : (int?)reader["accuracy"];
obj.Address = (DBNull.Value == reader["address"]) ? null : (string)reader["address"];
obj.Geozones = (DBNull.Value == reader["geozones"]) ? null : (string)reader["geozones"];
obj.Trackerrecid = (long)reader["trackerrecid"];
data.Add(obj);
}
}
return(data);
}
public static List <DimTracker> GetDimTrackers(this DatabaseWrapper conn)
{
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM Dim_Tracker WHERE validto IS NULL;";
List <DimTracker> data = new List <DimTracker>();
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DimTracker obj = new DimTracker();
obj.Recid = (long)reader["recid"];
obj.Serial = (string)reader["serial"];
obj.Validfrom = (DateTime)reader["validfrom"];
obj.Validto = (DBNull.Value == reader["validto"]) ? null : (DateTime?)reader["validto"];
obj.Name = (DBNull.Value == reader["name"]) ? null : (string)reader["name"];
obj.Productkey = (DBNull.Value == reader["productkey"]) ? null : (string)reader["productkey"];
obj.Thirdpartyid = (DBNull.Value == reader["thirdpartyid"]) ? null : (string)reader["thirdpartyid"];
obj.Tags = (DBNull.Value == reader["tags"]) ? null : (string)reader["tags"];
obj.TBRefId = (DBNull.Value == reader["tbref"]) ? null : (long?)reader["tbref"];
data.Add(obj);
}
}
data.ForEach(x => { if (x.TBRefId != null)
{
x.TBRef = conn.GetTBMap((long)x.TBRefId);
}
});
return(data);
}
public static void ClearActivities(this DatabaseWrapper conn)
{
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Fact_Activity;";
cmd.ExecuteNonQuery();
}
public static DimTracker GetDimTracker(this DatabaseWrapper conn, string serial)
{
DimTracker obj = new DimTracker();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = $"SELECT * FROM Dim_Tracker WHERE validto IS NULL AND serial = {SqlConvert.Convert(serial)};";
using (IDataReader reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
return(null);
}
obj.Recid = (long)reader["recid"];
obj.Serial = (string)reader["serial"];
obj.Validfrom = (DateTime)reader["validfrom"];
obj.Validto = (DBNull.Value == reader["validto"]) ? null : (DateTime?)reader["validto"];
obj.Name = (DBNull.Value == reader["name"]) ? null : (string)reader["name"];
obj.Productkey = (DBNull.Value == reader["productkey"]) ? null : (string)reader["productkey"];
obj.Thirdpartyid = (DBNull.Value == reader["thirdpartyid"]) ? null : (string)reader["thirdpartyid"];
obj.Tags = (DBNull.Value == reader["tags"]) ? null : (string)reader["tags"];
obj.TBRefId = (DBNull.Value == reader["tbref"]) ? null : (long?)reader["tbref"];
}
if (obj.TBRefId != null)
{
obj.TBRef = conn.GetTBMap((long)obj.TBRefId);
}
return(obj);
}
public static void ClearDimTrackers(this DatabaseWrapper conn)
{
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Dim_Tracker;";
cmd.ExecuteNonQuery();
}
public static void Insert(this DatabaseWrapper conn, List <FactActivity> list)
{
if (list.Count == 0)
{
return;
}
StringBuilder sb = new StringBuilder("INSERT INTO Fact_Activity (id, evttime, inserttime, serial, evttype, lat, lon, src, accuracy, address, geozones, trackerrecid) VALUES ");
sb.Append(String.Join(", ", list.Select(x => $"({SqlConvert.Convert(x.Id)}," +
$"{SqlConvert.Convert(x.Evttime)}," +
$"{SqlConvert.Convert(x.Inserttime)}," +
$"{SqlConvert.Convert(x.Serial)}," +
$"{SqlConvert.Convert(x.Evttype)}," +
$"{SqlConvert.Convert(x.Lat)}," +
$"{SqlConvert.Convert(x.Lon)}," +
$"{SqlConvert.Convert(x.Src)}," +
$"{SqlConvert.Convert(x.Accuracy)}," +
$"{SqlConvert.Convert(x.Address)}," +
$"{SqlConvert.Convert(x.Geozones)}," +
$"{SqlConvert.Convert(x.Trackerrecid)})")));
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
public static List <DimRule> GetRules(this DatabaseWrapper conn)
{
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM Dim_Rule WHERE validto IS NULL;";
List <DimRule> data = new List <DimRule>();
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DimRule obj = new DimRule();
obj.Recid = (long)reader["recid"];
obj.Id = (long)reader["id"];
obj.Title = (DBNull.Value == reader["title"]) ? null : (string)reader["title"];
obj.Type = (string)reader["type"];
obj.Deviceserials = (DBNull.Value == reader["deviceserials"]) ? null : (string)reader["deviceserials"];
obj.Zonelst = (DBNull.Value == reader["zonelst"]) ? null : (string)reader["zonelst"];
obj.Validfrom = (DateTime)reader["validfrom"];
obj.Validto = (DBNull.Value == reader["validto"]) ? null : (DateTime?)reader["validto"];
data.Add(obj);
}
}
return(data);
}
public static FactActivity GetLatestActivity(this DatabaseWrapper conn, string serial)
{
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM Fact_Activity WHERE serial = " + SqlConvert.Convert(serial) + " ORDER BY inserttime DESC LIMIT 1";
using (IDataReader reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
return(null);
}
FactActivity obj = new FactActivity();
obj.Id = (long)reader["id"];
obj.Evttime = (DateTime)reader["evttime"];
obj.Inserttime = (DateTime)reader["inserttime"];
obj.Serial = (string)reader["serial"];
obj.Evttype = (string)reader["evttype"];
obj.Lat = (DBNull.Value == reader["lat"]) ? null : (double?)reader["lat"];
obj.Lon = (DBNull.Value == reader["lon"]) ? null : (double?)reader["lon"];
obj.Src = (string)reader["src"];
obj.Accuracy = (DBNull.Value == reader["accuracy"]) ? null : (int?)reader["accuracy"];
obj.Address = (DBNull.Value == reader["address"]) ? null : (string)reader["address"];
obj.Geozones = (DBNull.Value == reader["geozones"]) ? null : (string)reader["geozones"];
obj.Trackerrecid = (long)reader["trackerrecid"];
return(obj);
}
}
public static List <FactAlert> GetAlerts(this DatabaseWrapper conn)
{
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM Fact_Alert;";
List <FactAlert> data = new List <FactAlert>();
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
FactAlert obj = new FactAlert();
obj.Alerttime = (DateTime)reader["alerttime"];
obj.Alerttype = (string)reader["alerttype"];
obj.Alertruleid = (long)reader["alertruleid"];
obj.Trackerserial = (string)reader["trackerserial"];
obj.Severity = (string)reader["severity"];
obj.Alerttitle = (DBNull.Value == reader["alerttitle"]) ? null : (string)reader["alerttitle"];
obj.Alertactivity = (long)reader["alertactivity"];
obj.Rulerecid = (long)reader["rulerecid"];
obj.Alertclear = (DBNull.Value == reader["alertclear"]) ? null : (DateTime?)reader["alertclear"];
data.Add(obj);
}
}
return(data);
}
public static void Update(this DatabaseWrapper conn, List <FactAlert> list)
{
if (list.Count == 0)
{
return;
}
StringBuilder sb = new StringBuilder("DELETE FROM Fact_Alert WHERE concat_ws(',', alerttime, alerttype, alertruleid, trackerserial) in (");
sb.Append(String.Join(", ", list.Select(x => $"'{x.Alerttime.ToString("yyyy-MM-dd HH:mm:sszz").Replace("'", "''")},{x.Alerttype},{x.Alertruleid},{x.Trackerserial}'")) + ')');
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
conn.Insert(list);
}
public static void Delete(this DatabaseWrapper conn, List <DimTracker> list)
{
if (list.Count == 0)
{
return;
}
StringBuilder sb = new StringBuilder("UPDATE Dim_Tracker SET validto = now() WHERE validto IS NULL AND serial IN (");
sb.Append(String.Join(", ", list.Select(x => $"'{x.Serial.Replace("'", "''")}'")));
sb.Append(')');
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
public static void Delete(this DatabaseWrapper conn, List <FactActivity> list)
{
if (list.Count == 0)
{
return;
}
StringBuilder sb = new StringBuilder("DELETE FROM Fact_Activity WHERE id IN (");
sb.Append(String.Join(", ", list.Select(x => SqlConvert.Convert(x.Id))));
sb.Append(')');
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
public static void Delete(this DatabaseWrapper conn, List <DimRule> list)
{
if (list.Count == 0)
{
return;
}
StringBuilder sb = new StringBuilder("UPDATE Dim_Rule SET validto = now() WHERE validto IS NULL AND id IN (");
sb.Append(String.Join(", ", list.Select(x => $"{x.Id}")));
sb.Append(')');
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sb.ToString();
int i = cmd.ExecuteNonQuery();
}
public static void Insert(this DatabaseWrapper conn, List<FactTracker> list)
{
if(list.Count == 0)
{
return;
}
StringBuilder sb = new StringBuilder("INSERT INTO Fact_Tracker (ts, serial, trackerrecid, battpercent, estbattlife) VALUES ");
sb.Append(String.Join(", ", list.Select(x => $"({SqlConvert.Convert(x.Ts)}," +
$"{SqlConvert.Convert(x.Serial)}, " +
$"{SqlConvert.Convert(x.Trackerrecid)}, " +
$"{SqlConvert.Convert(x.Battpercent)}, " +
$"{SqlConvert.Convert(x.Estbattlife)})")));
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
public static List<FactTracker> GetFactTrackers(this DatabaseWrapper conn)
{
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM Fact_Tracker;";
List<FactTracker> data = new List<FactTracker>();
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
FactTracker obj = new FactTracker();
obj.Ts = (DateTime)reader["ts"];
obj.Serial = (string)reader["serial"];
obj.Trackerrecid = (long)reader["trackerrecid"];
obj.Battpercent = (DBNull.Value == reader["battpercent"]) ? null : (int?)reader["battpercent"];
obj.Estbattlife = (DBNull.Value == reader["estbattlife"]) ? null : (int?)reader["estbattlife"];
data.Add(obj);
}
}
return data;
}
public static void Insert(this DatabaseWrapper conn, List <DimZone> list)
{
if (list.Count == 0)
{
return;
}
StringBuilder sb = new StringBuilder("INSERT INTO Dim_Zone (id, name, geometry, tags) VALUES ");
sb.Append(
String.Join(", ", list.Select(x => $"({x.Id}, " +
$"{SqlConvert.Convert(x.Name)}, " +
$"{SqlConvert.Convert(x.Geometry)}, " +
$"{SqlConvert.Convert(x.Tags)})")
)
);
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
public static void Insert(this DatabaseWrapper conn, List <DimRule> list)
{
if (list.Count == 0)
{
return;
}
StringBuilder sb = new StringBuilder("INSERT INTO Dim_Rule (id, title, type, deviceserials, zonelst) VALUES ");
sb.Append(
String.Join(", ", list.Select(x => $"({x.Id}, " +
$"{SqlConvert.Convert(x.Title)}, " +
$"{SqlConvert.Convert(x.Type)}, " +
$"{SqlConvert.Convert(x.Deviceserials)}, " +
$"{SqlConvert.Convert(x.Zonelst)})")
)
);
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
public static void Insert(this DatabaseWrapper conn, List <DimTracker> list)
{
if (list.Count == 0)
{
return;
}
StringBuilder sb = new StringBuilder("INSERT INTO Dim_Tracker (serial, name, productkey, thirdpartyid, tags, tbref) VALUES ");
sb.Append(
String.Join(", ", list.Select(x => $"({SqlConvert.Convert(x.Serial)}, " +
$"{SqlConvert.Convert(x.Name)}, " +
$"{SqlConvert.Convert(x.Productkey)}, " +
$"{SqlConvert.Convert(x.Thirdpartyid)}, " +
$"{SqlConvert.Convert(x.Tags)}, " +
$"{SqlConvert.Convert(x.TBRef?.TBRef)})")
)
);
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
public static void Insert(this DatabaseWrapper conn, List <FactAlert> list)
{
if (list.Count == 0)
{
return;
}
StringBuilder sb = new StringBuilder("INSERT INTO Fact_Alert (alerttime, alerttype, alertruleid, trackerserial, severity, alerttitle, alertactivity, rulerecid, alertclear) VALUES ");
sb.Append(String.Join(", ", list.Select(x => $"({SqlConvert.Convert(x.Alerttime)}," +
$"{SqlConvert.Convert(x.Alerttype)}, " +
$"{SqlConvert.Convert(x.Alertruleid)}, " +
$"{SqlConvert.Convert(x.Trackerserial)}, " +
$"{SqlConvert.Convert(x.Severity)}, " +
$"{SqlConvert.Convert(x.Alerttitle)}, " +
$"{SqlConvert.Convert(x.Alertactivity)}, " +
$"{SqlConvert.Convert(x.Rulerecid)}, " +
$"{SqlConvert.Convert(x.Alertclear)})").ToList()));
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
public static List <DimZone> GetZones(this DatabaseWrapper conn)
{
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM Dim_Zone WHERE validto IS NULL;";
List <DimZone> data = new List <DimZone>();
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DimZone obj = new DimZone();
obj.Recid = (long)reader["recid"];
obj.Id = (long)reader["id"];
obj.Validfrom = (DateTime)reader["validfrom"];
obj.Validto = (DBNull.Value == reader["validto"]) ? null : (DateTime?)reader["validto"];
obj.Name = (string)reader["name"];
obj.Geometry = (string)reader["geometry"];
obj.Tags = (DBNull.Value == reader["tags"]) ? null : (string)reader["tags"];
data.Add(obj);
}
}
return(data);
}
public void Run(DbProviderFactory factory)
{
IDbTransaction transaction = null;
api.ResetCounter();
DateTime start = DateTime.Now;
try
{
var connectionStringBuilder = factory.CreateConnectionStringBuilder();
connectionStringBuilder.ConnectionString = $"Server={cfg.Host};Database={cfg.Database};User ID={cfg.User};Password={cfg.Password}";
using (DatabaseWrapper client = new DatabaseWrapper(factory, connectionStringBuilder.ToString()))
{
client.Open();
transaction = client.BeginTransaction();
if (cfg.Clean)
{
client.ClearAlerts();
client.ClearActivities();
client.ClearFactTrackers();
client.ClearZones();
client.ClearRules();
client.ClearDimTrackers();
}
FirstPhase(client);
SecondPhase(client, cfg.Interval);
string[] serials = client.GetDimTrackers().Select(x => x.Serial).ToArray();
ThirdPhase(client, serials);
transaction.Commit();
transaction = null;
}
}
catch (SqlException e)
{
Serilog.Log.Error(e.Message);
Serilog.Log.Error(e.StackTrace);
transaction?.Rollback();
return;
}
catch (ArgumentException e)
{
//wrong SQL Connection string
Serilog.Log.Error("Wrong SQL Connection string: " + $"Server={cfg.Host};Database={cfg.Database};User ID={cfg.User};Password=*****");
Serilog.Log.Error(e.Message);
Serilog.Log.Error(e.StackTrace);
return;
}
catch (Exception e)
{
Serilog.Log.Error("Unexpected error");
Serilog.Log.Error(e.Message);
Serilog.Log.Error(e.StackTrace);
return;
}
TimeSpan duration = DateTime.Now - start;
try
{
var connectionStringBuilder = factory.CreateConnectionStringBuilder();
connectionStringBuilder.ConnectionString = $"Server={cfg.Host};Database={cfg.Database};User ID={cfg.User};Password={cfg.Password}";
using (DatabaseWrapper client = new DatabaseWrapper(factory, connectionStringBuilder.ToString()))
{
client.Open();
IDbCommand cmd = client.CreateCommand();
cmd.CommandText = $"INSERT INTO log (ts, duration, reqcount, initevent) VALUES ({SqlConvert.Convert(DateTime.Now)}, {duration.TotalMilliseconds}, {api.Count}, 'TS')";
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Serilog.Log.Error("Could not make log record");
Serilog.Log.Error(e.Message);
Serilog.Log.Error(e.StackTrace);
return;
}
Serilog.Log.Information($"ts: {DateTime.Now}, duration: {duration.TotalMilliseconds}, reqCount: {api.Count}, initevent: TS");
}
private async void _handleActivityNotification(InvokeRequest request)
{
Uri uri = new Uri("http://localhost" + request.Parameters[SensolusConstants.PATH].Value <string>());
if (uri.Segments.Length <= 1)
{
return;
}
string context = uri.Segments[1].Replace("/", "");
if (!connCfgs.ContainsKey(context))
{
return;
}
SensolusCfg cfg = connCfgs[context];
var query = System.Web.HttpUtility.ParseQueryString(uri.Query);
DateTime start = DateTime.Now;
DataProcessor dp = new DataProcessor(cfg);
IDbTransaction transaction = null;
string serial = "";
try
{
var connectionStringBuilder = NpgsqlFactory.Instance.CreateConnectionStringBuilder();
connectionStringBuilder.ConnectionString = $"Server={cfg.Host};Database={cfg.Database};User ID={cfg.User};Password={cfg.Password}";
using (DatabaseWrapper client = new DatabaseWrapper(NpgsqlFactory.Instance, connectionStringBuilder.ToString()))
{
client.Open();
transaction = client.BeginTransaction();
//dp.FirstPhase(client);
List <DimTracker> trackers = client.GetDimTrackers();
FactActivity activity = new FactActivity();
activity.Id = request.Parameters[SensolusConstants.ACTIVITY_ID].Value <long>();
activity.Evttime = DateTime.Parse(request.Parameters[SensolusConstants.ACTIVITY_TIME].Value <string>());
activity.Inserttime = DateTime.Parse(request.Parameters[SensolusConstants.ACTIVITY_INSERTTIME].Value <string>());
activity.Serial = request.Parameters[SensolusConstants.SERIAL].Value <string>();
activity.Evttype = request.Parameters[SensolusConstants.ACTIVITY_STATE]?.Value <string>();
activity.Lat = request.Parameters[SensolusConstants.ACTIVITY_LAT]?.Value <double?>();
activity.Lon = request.Parameters[SensolusConstants.ACTIVITY_LON]?.Value <double?>();
activity.Src = request.Parameters[SensolusConstants.ACTIVITY_SRC]?.Value <string>();
activity.Accuracy = request.Parameters[SensolusConstants.ACTIVITY_ACCURACY]?.Value <int?>();
activity.Address = request.Parameters[SensolusConstants.ACTIVITY_ADDRESS]?.Value <string>();
activity.Geozones = request.Parameters[SensolusConstants.ACTIVITY_GEOZONE]?.Value <string>();
serial = activity.Serial;
DimTracker tracker = trackers.Find(x => x.Serial == activity.Serial);
if (tracker == default(DimTracker))
{
dp.FirstPhase(client);
tracker = client.GetDimTrackers().Find(x => x.Serial == activity.Serial);
}
activity.Trackerrecid = tracker.Recid;
DateTime epoch = new DateTime(1970, 1, 1);
if (tracker.TBRef != null && ThingsBoardURL != "")
{
// send to TB
JObject data = new JObject();
if (activity.Evttype == "GeozoneInsideAlertType")
{
data["inGz"] = true;
JArray geozones = JArray.Parse(activity.Geozones);
data["lastGz"] = geozones[0].Value <string>();
if (client.GetZones().Find(x => x.Id == long.Parse(data["lastGz"])) == default(DimZone))
{
dp.FirstPhase(client);
}
data["lastGzEntry"] = (activity.Evttime - epoch).TotalMilliseconds;
}
else if (activity.Evttype == "GeozoneOutsideAlertType")
{
data["inGz"] = false;
data["lastGzExit"] = (activity.Evttime - epoch).TotalMilliseconds;
}
if (activity.Lat != null)
{
data["latitude"] = activity.Lat;
}
if (activity.Lon != null)
{
data["longitude"] = activity.Lon;
}
if (activity.Accuracy != null)
{
data["locAccuracy"] = activity.Accuracy;
}
if (activity.Lat != null || activity.Lon != null || activity.Accuracy != null)
{
data["lastLocTime"] = (activity.Evttime - epoch).TotalMilliseconds;
}
data["tracker"] = activity.Serial;
// send POST request to ThingsBoard
using (var httpClient = new HttpClient())
{
var response = await httpClient.PostAsync(
$"{ThingsBoardURL}/api/v1/{tracker.TBRef.Value.TBAt}/attributes",
new StringContent(data.ToString(), Encoding.UTF8, "application/json"));
}
}
transaction.Commit();
}
}
catch (SqlException e)
{
Serilog.Log.Error(e.Message);
Serilog.Log.Error(e.StackTrace);
transaction?.Rollback();
return;
}
catch (ArgumentException e)
{
//wrong SQL Connection string
Serilog.Log.Error("Wrong SQL Connection string: " + $"Server={cfg.Host};Database={cfg.Database};User ID={cfg.User};Password=*****");
Serilog.Log.Error(e.Message);
Serilog.Log.Error(e.StackTrace);
return;
}
catch (Exception e)
{
Serilog.Log.Error("Unexpected error");
Serilog.Log.Error(e.Message);
Serilog.Log.Error(e.StackTrace);
return;
}
TimeSpan duration = DateTime.Now - start;
try
{
var connectionStringBuilder = NpgsqlFactory.Instance.CreateConnectionStringBuilder();
connectionStringBuilder.ConnectionString = $"Server={cfg.Host};Database={cfg.Database};User ID={cfg.User};Password={cfg.Password}";
using (DatabaseWrapper client = new DatabaseWrapper(NpgsqlFactory.Instance, connectionStringBuilder.ToString()))
{
client.Open();
IDbCommand cmd = client.CreateCommand();
cmd.CommandText = $"INSERT INTO log (ts, duration, reqcount, initevent, serial) VALUES ({SqlConvert.Convert(DateTime.Now)}, {duration.TotalMilliseconds}, {dp.api.Count}, 'PN', {SqlConvert.Convert(serial)})";
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Serilog.Log.Error("Could not make log record");
Serilog.Log.Error(e.Message);
Serilog.Log.Error(e.StackTrace);
return;
}
Serilog.Log.Information($"ts: {DateTime.Now}, duration: {duration.TotalMilliseconds}, reqCount: {dp.api.Count}, initevent: PN, serial: {serial}");
}
private void _handleAlertNotification(InvokeRequest request)
{
Uri uri = new Uri("http://localhost" + request.Parameters[SensolusConstants.PATH].Value <string>());
if (uri.Segments.Length <= 1)
{
return;
}
string context = uri.Segments[1].Replace("/", "");
if (!connCfgs.ContainsKey(context))
{
return;
}
SensolusCfg cfg = connCfgs[context];
var query = System.Web.HttpUtility.ParseQueryString(uri.Query);
DateTime start = DateTime.Now;
DataProcessor dp = new DataProcessor(cfg);
IDbTransaction transaction = null;
string serial = "";
try
{
var connectionStringBuilder = NpgsqlFactory.Instance.CreateConnectionStringBuilder();
connectionStringBuilder.ConnectionString = $"Server={cfg.Host};Database={cfg.Database};User ID={cfg.User};Password={cfg.Password}";
using (DatabaseWrapper client = new DatabaseWrapper(NpgsqlFactory.Instance, connectionStringBuilder.ToString()))
{
client.Open();
transaction = client.BeginTransaction();
//dp.FirstPhase(client);
serial = request.Parameters[SensolusConstants.SERIAL]?.Value <string>();
FactActivity latestActivity = client.GetLatestActivity(serial);
int halfMinutes = (int)Math.Ceiling((DateTime.Now - latestActivity.Evttime).TotalMinutes / 2);
List <DimTracker> trackers = new List <DimTracker>();
DimTracker tracker = client.GetDimTracker(serial);
if (tracker == default(DimTracker))
{
dp.FirstPhase(client);
tracker = client.GetDimTracker(serial);
}
trackers.Add(tracker);
dp.SecondPhase(client, halfMinutes, trackers);
dp.ThirdPhase(client, new string[] { serial });
transaction.Commit();
}
}
catch (SqlException e)
{
Serilog.Log.Error(e.Message);
Serilog.Log.Error(e.StackTrace);
transaction?.Rollback();
return;
}
catch (ArgumentException e)
{
//wrong SQL Connection string
Serilog.Log.Error("Wrong SQL Connection string: " + $"Server={cfg.Host};Database={cfg.Database};User ID={cfg.User};Password=*****");
Serilog.Log.Error(e.Message);
Serilog.Log.Error(e.StackTrace);
return;
}
catch (Exception e)
{
Serilog.Log.Error("Unexpected error");
Serilog.Log.Error(e.Message);
Serilog.Log.Error(e.StackTrace);
return;
}
TimeSpan duration = DateTime.Now - start;
try
{
var connectionStringBuilder = NpgsqlFactory.Instance.CreateConnectionStringBuilder();
connectionStringBuilder.ConnectionString = $"Server={cfg.Host};Database={cfg.Database};User ID={cfg.User};Password={cfg.Password}";
using (DatabaseWrapper client = new DatabaseWrapper(NpgsqlFactory.Instance, connectionStringBuilder.ToString()))
{
client.Open();
IDbCommand cmd = client.CreateCommand();
cmd.CommandText = $"INSERT INTO log (ts, duration, reqcount, initevent, serial) VALUES ({SqlConvert.Convert(DateTime.Now)}, {duration.TotalMilliseconds}, {dp.api.Count}, 'PN', {SqlConvert.Convert(serial)})";
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Serilog.Log.Error("Could not make log record");
Serilog.Log.Error(e.Message);
Serilog.Log.Error(e.StackTrace);
return;
}
Serilog.Log.Information($"ts: {DateTime.Now}, duration: {duration.TotalMilliseconds}, reqCount: {dp.api.Count}, initevent: PN, serial: {serial}");
}
