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}"); }