Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        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);
            }
        }
Esempio n. 3
0
 public bool Equals(FactActivity other)
 {
     return(Id == other.Id &&
            Evttime == other.Evttime &&
            Inserttime == other.Inserttime &&
            Serial == other.Serial &&
            Evttype == other.Evttype &&
            Lat == other.Lat &&
            Lon == other.Lon &&
            Src == other.Src &&
            Accuracy == other.Accuracy &&
            Address == other.Address &&
            Geozones == other.Geozones &&
            Trackerrecid == other.Trackerrecid);
 }
Esempio n. 4
0
        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}");
        }
Esempio n. 5
0
        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}");
        }