IoT with an ESP8266 (Part 4) – IoT Web Application

The IoT web application I planned was fairly simple. It needed to handle a limited set of use cases:

• Accept data sent from devices
• Allow devices to be moved to different locations, including newly added locations
• Display some basic info on each device – likely the last days’ worth of measurements and possibly a graph

Before diving into the code, I’ll mention once again that all the code is currently available on GitHub here. I’ll discuss some of the highlights next, but for more detail, review the source code.

Measurements

The first thing I needed for my IoT web application was a way to get measurements from the device to the database. I already knew what I would be sending: measurements for temperature/humidity/light and information about the device, namely ID and IP address. The examples I’d found on the Huzzah/ESP8266 typically showed GET methods, so I created an action based on that:

public ActionResult PostData(int id, string ip, decimal? temp, decimal? humidity, decimal? light)
{
    var results = "Success";
    var reported = DateTime.Now;

    try
    {
        var device = _context.ReportingDevices.FirstOrDefault(d => d.ReportingDeviceId == id);

        if (device == null)
        {
            results = "Unknown device";
        }
        else
        {
            // update the ip address first
            device.LastIpAddress = ip;

            if (temp.HasValue)
            {
                // add temperature
                _context.Measurements.Add(new Measurement
                {
                    MeasurementTypeId = (int)MeasureTypeEnum.Temperature,
                    ReportingDeviceId = device.ReportingDeviceId,
                    LocationId = device.LocationId,
                    MeasuredValue = temp.Value,
                    MeasuredDate = reported
                });
            }

            if (humidity.HasValue)
            {
                // add humidity
                _context.Measurements.Add(new Measurement
                {
                    MeasurementTypeId = (int)MeasureTypeEnum.Humidity,
                    ReportingDeviceId = device.ReportingDeviceId,
                    LocationId = device.LocationId,
                    MeasuredValue = humidity.Value,
                    MeasuredDate = reported
                });
            }

            if (light.HasValue)
            {
                // add light
                _context.Measurements.Add(new Measurement
                {
                    MeasurementTypeId = (int)MeasureTypeEnum.Light,
                    ReportingDeviceId = device.ReportingDeviceId,
                    LocationId = device.LocationId,
                    MeasuredValue = light.Value,
                    MeasuredDate = reported
                });
            }

            // save it all
            _context.SaveChanges();
        }
    }
    catch (Exception ex)
    {
        results = "Exception: " + ex.Message;
    }

    return Content(results);
}

If a device with this ID exists, it first locates that device and updates it with the sent IP address. This information would then show on the home page as a link. Provided the browser is run on the same network as the device, this would allow me to browse to it directly if I needed to. It also meant I didn’t have to plug the device into a PC and watch serial output to find the IP address.

Next I needed to create entries for each of the three measurements, which I kept simple. Since the device itself does not have a calendar or even a clock, it was up to the method/action itself to provide that information.

I also added code to return with the results some indication of whether there was success or failure.

Locations

Another use case I had was actually something that would typically occur chronologically before data was ever sent. I needed to set the location for the device. The device itself has no idea where it is. In theory I could hook up a GPS to it and find out that way, but that added extra cost. Also, GPS data, i.e. longitude and latitude, was not specific enough for my purposes. I wanted a description.

Prior to data arriving, the device itself – or the representation of it in the database – needed to know it’s location. I’d already pre-seeded my database with some default possibilities, so I started there with a method that would just accept device and location IDs:

public ActionResult ChangeLocation(int reportingDeviceId, int locationId)
{
    var model = new LocationHandler { ReportingDeviceId = reportingDeviceId, Success = false };
    var device = _context.ReportingDevices.Include(t => t.DeviceType)
        .FirstOrDefault(d => d.ReportingDeviceId == reportingDeviceId);
    var location = _context.Locations.FirstOrDefault(l => l.LocationId == locationId);

    if (device == null) { model.Message = $"Device with ID {reportingDeviceId} not found"; }
    else if (location == null) { model.Message = $"Location with ID {locationId} not found"; }
    else
    {
        device.LocationId = location.LocationId;
        _context.ReportingDevices.Attach(device);
        _context.Entry(device).State = EntityState.Modified;
        _context.SaveChanges();

        model.DeviceTypeName = device.DeviceType.Name;
        model.LocationName = location.Name;
        model.Success = true;
    }

    return View(model);
}

To this method I also added some basic checking, for both the device and the location. Assuming both were found in the database the update went through. If either were missing or if something else went wrong, a message was sent along to the view in a view model created to handle location changes:

    public class LocationHandler
    {
        public int ReportingDeviceId { get; set; }
        public string DeviceTypeName { get; set; }

        [DisplayName("Location Name")]
        [Required(ErrorMessage = "Please enter a location name")]
        public string LocationName { get; set; }

        [DisplayName("Location Description")]
        public string LocationDesc { get; set; }

        public bool Success { get; set; }
        public string Message { get; set; }
    }

I used this same view model for handling addition of locations, since it seemed likely I’d wind up getting measurements somewhere not listed in the defaults I’d loaded so far.

Main page / Status / Control Panel

Finally, for my IoT application, I had the main/home page to tackle. This would be dashboard, so the view model I’d use needed to be fairly complex. It needed to contain:

• General info about current device:
  • Device ID
  • Type
  • Current location
  • Local IP address
• Lists
  • Devices
  • Locations
• Measurements, or the last set of measurements for the device

I created a view model to contain the relevant info:

public class DeviceInfo
{
    public int? ReportingDeviceId { get; set; }
    public string TypeName { get; set; }
    public string LocationName { get; set; }
    public string LocalIp { get; set; }
    public List<ReportingDevice> Devices { get; set; } = new List<ReportingDevice>();
    public List<Location> Locations { get; set; } = new List<Location>();
    public MeasurementSet LastSet { get; set; } = new MeasurementSet();
}

Fetching the lists and info about current device was fairly simple, but I wanted to check that I’d been sent a real device ID before loading anything. If I hadn’t, I’d planned to default to whatever the first device in the list was. So I tied my loading of device lists to setting the first device by default:

    var vm = new DeviceInfo { ReportingDeviceId = id };
    ReportingDevice device = null;

    // set lists of devices and locations for display
    if (_context.ReportingDevices.Any())
    {
        vm.Devices = _context.ReportingDevices.ToList();

        // get first device here since we know the list has data
        device = vm.Devices.First();
    }

If the ID sent is present, the I’d try and load that device:

    // load given device if possible
    if (id.HasValue)
    {
        device = _context.ReportingDevices.FirstOrDefault(d => d.ReportingDeviceId == id.Value);
    }

Finally, I’d finish loading the entire view model provided I had device information to load:

    if (device != null)
    {
        vm.ReportingDeviceId = device.ReportingDeviceId;
        vm.TypeName = device.Name;
        vm.LocationName = device.Location.Name;
        vm.LocalIp = device.LastIpAddress;
        vm.LastSet = MostRecentSet(device.ReportingDeviceId);
    }

The last call fetches a MeasurementSet. This is another view model I used for “flattening” a set of measurements that all share the same date:

    public class MeasurementSet
    {
        public DateTime? MeasuredDate { get; set; }
        public string LocationName { get; set; }
        public decimal? Temperature { get; set; }
        public decimal? Humidity { get; set; }
        public decimal? Light { get; set; }
    }

I needed this because I’d decided to store my measurements by row and type, not by column. This allowed me flexibility to include different types of measurements in the future, but it doesn’t make it easy to display measurements taken at the same time in one line.

Getting this last set involved pulling the most recent 3 measurements for a given device:

    var last3 = _context.Measurements
        .Where(m => m.ReportingDeviceId == reportingDeviceId)
        .Select(m => m).Include(l => l.Location).Distinct().
        OrderByDescending(m => m.MeasuredDate).Take(3).ToList();

Last but not least I wanted to show a line graph of the 3 measurements over the last 24 hours. I had initially planned to show the average measurements over that span as a simple table on the home page and leave it at that, but after some deliberation it seemed a graph or a chart would help in viewing this data as well.

My initial version in MVC4 included a control from Microsoft called “Chart”. With this I could feed data from within the controller and generate an image which could be pulled via a link in the view. Problem was, the library it lived in – System.Web.Helpers – was not a part of the core libraries. While you can include and reference these from a core project, I decided against it and went looking for an alternative.

After some research, I decided to use Google Charts. To do this, I could reference a JavaScript library from their site and then designate a load and callback to a method of my own.

I placed a scripts section at the bottom of my Index page/view including these calls along with a reference to the window resize to ensure the graph/chart was rebuilt every time the window was resized. This was, I found, the best I could come up with to make the graph itself responsive. It’s also worth noting that putting this Javascript in a “scripts” section ensures it is added at the end after other required libraries, such as jQuery, are loaded.

@section Scripts {
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
        google.charts.load('current', { 'packages': ['line'] });
        google.charts.setOnLoadCallback(function () { drawChart(@Model.ReportingDeviceId); });
        $(window).resize(function () { drawChart(@Model.ReportingDeviceId); });
    </script>
}

The “drawChart” method was then placed in the site.js file, which the template created an empty version of. This method makes an AJAX call after the page is loaded to fetch data from a controller method & take the resulting JSON and feed it to the Google chart:

function drawChart(deviceId) {

    $.ajax(
    {
        url: '/Home/DeviceDay',
        dataType: "json",
        data: { id: deviceId },
        type: "GET",
        success: function (jsonData) {
            var data = new google.visualization.DataTable(jsonData);
            var options = { chart: { title: 'Most recent 24 hours of measurements' } };
            var chart = new google.charts.Line(document.getElementById('chart_div'));
            chart.draw(data, options);
        }
    });

    return false;
}

The controller method needed to feed JSON data to the Google chart in a format with which it was familiar. Google charts use their own DataTable objects, which are basic, two-dimensional tables with some simple rules to define them:

• All data in each column must have the same data type
• Each column has a descriptor that includes its data type, a label for that column, and an ID
• Each cell in the table hold a value which can be null or contain a value
• Cells can optionally take a formatted version of the data, or a string version

I found some rather lengthy info about this in Google’s reference. You can provide a chart this DataTable as JSON like so:

{
  "cols": [
    {"label": "Time of Day", "type": "datetime"},
    {"label": "Temp F", "type": "number"},
    {"label": "Humidity %", "type": "number"},
    {"label": "Light %", "type": "number"}
  ],
  "rows": [
    {"c": [ {"v": "Date(2016,12,15,11,30,0,0)" }, { "v": "65.9" }, { "v": "35.0" }, { "v": "40.0" } ] },
    {"c": [ {"v": "Date(2016,12,15,11,31,0,0)" }, { "v": "65.7" }, { "v": "34.7" }, { "v": "51.0" } ] },
    {"c": [ {"v": "Date(2016,12,15,11,32,0,0)" }, { "v": "65.5" }, { "v": "34.3" }, { "v": "68.0" } ] }
  ]
}

In order to return my results as JSON in this way, I needed a data structure that would mimic the structure:

    public class GoogleVizDataTable
    {
        public IList<Col> cols { get; set; } = new List<Col>();

        public IList<Row> rows { get; set; } = new List<Row>();

        public class Col
        {
            public string label { get; set; }
            public string type { get; set; }
        }

        public class Row
        {
            public IEnumerable<RowValue> c { get; set; }

            public class RowValue
            {
                public object v;
            }
        }
    }

My controller action/method then needed to generate a generic list of these structures which it would convert to JSON. I created an action called “DeviceDay” which accepted the device ID as a parameter. If the ID was valid and measurements had been recorded for that device, it would then proceed to find the most recent measurement on record. Using the date from that device it would count back a day and retrieve all measurements in the intervening period and load that info into a generic list of the GoogleVisDataTable objects.

public IActionResult DeviceDay(int? id)
{
    // establish an empty table
    var gdataTable = new GoogleVizDataTable();

    gdataTable.cols.Add(new GoogleVizDataTable.Col { label = "Time of Day", type = "datetime" });
    gdataTable.cols.Add(new GoogleVizDataTable.Col { label = "Temp F", type = "number" });
    gdataTable.cols.Add(new GoogleVizDataTable.Col { label = "Humidity %", type = "number" });
    gdataTable.cols.Add(new GoogleVizDataTable.Col { label = "Light %", type = "number" });

    // if ID given is present
    if (id.HasValue)
    {
        // next get the most recent measurement for this device
        var mostRecent = _context.Measurements.Where(d => d.ReportingDeviceId == id.Value)
            .Select(m => m).OrderByDescending(m => m.MeasuredDate).Take(1).FirstOrDefault();

        // if we have a recent measurement for this device
        if (mostRecent != null)
        {
            // establish a range of previous to current day/time
            var finish = mostRecent.MeasuredDate;
            var start = finish.AddDays(-1);

            // fetch a set of measurements for that range
            var recentSet = MeasureSetRange(id.Value, start, finish);

            // build out the google datatable using this data
            gdataTable.rows =
                (from set in recentSet
                select new GoogleVizDataTable.Row
                {
                    c = new List<GoogleVizDataTable.Row.RowValue>
                    {
                        new GoogleVizDataTable.Row.RowValue { v = set.GoogleDate },
                        new GoogleVizDataTable.Row.RowValue { v = set.TempString },
                        new GoogleVizDataTable.Row.RowValue { v = set.HumidString },
                        new GoogleVizDataTable.Row.RowValue { v = set.LightString }
                    }
                }).ToList();
            }
        }

    return Json(gdataTable);
}

The actual work of loading the measurements from the database ended up in a different method that would accept the ID along with the start and end date. My thinking here was I could later include a separate page that allowed the user to specify a date/time range & view a graph of that range. This method returned just measurement “sets”, or the same object I’d used for the most recent set:

public List<MeasurementSet> MeasureSetRange(int reportingDeviceId, DateTime start, DateTime finish)
{
    var measureSet =
        (from m in _context.Measurements
        where m.ReportingDeviceId == reportingDeviceId
        && m.MeasuredDate >= start
        && m.MeasuredDate <= finish
        orderby m.MeasuredDate
        group m by new { MeasuredDate = 
            DateTime.Parse(m.MeasuredDate.ToString("yyyy-MM-dd HH:mm:ss")), m.Location.Name }
        into g
        select new MeasurementSet
        {
            MeasuredDate = g.Key.MeasuredDate,
            LocationName = g.Key.Name,
            Temperature = g.Where(m => m.MeasurementTypeId == 1)
                .Select(r => r.MeasuredValue).FirstOrDefault(),
            Humidity = g.Where(m => m.MeasurementTypeId == 2)
                .Select(r => r.MeasuredValue).FirstOrDefault(),
            Light = g.Where(m => m.MeasurementTypeId == 3)
                .Select(r => r.MeasuredValue).FirstOrDefault()
        }).ToList();

    return measureSet;
}

This Linq-to-Entity statement performs a “grouping” by the measurement date/time to get a set of three for each row taken at the same time. This process essentially flattens the three measurements into one row so they can be displayed at the same time, or same spot on the chart.

My IoT web application ended up looking something like this:

It was interesting to see how this data turned out. I’d left the device gathering measurements every minute near the window of my home office. This room is in the basement of my house and the time when I’d been gathering data was during a cold snap, or “polar vortex” weather pattern we’d encountered in Minnesota. External temperatures dropped to negatives in the Fahrenheit.

In reviewing this data, it occurred to me that particular window, which was original with the house nearly 20 year old, wasn’t the greatest at keeping cold air out. I also noticed there wasn’t necessarily a relationship between cold and humidity in that room. I figured the colder it was, the lower the humidity, but they seemed inversely related during this measurement period. That could be attributed to many other factors, though, such as when someone in our house showered or when we ran the dishwasher. Finally it was interesting to note I could easily track when I’d entered the office and turned on the lights.

At this point I had a functional site, though running locally only. Initial tests with my device proved it could send data as long as both my IoT web application and the device were on the same network. But I had wanted to move this device and get measurements from a number of different locations. So my next step was to deploy it to a location that was publicly accessible to the device (or, eventually, devices) no matter where I set it up. To accomplish this, I decided to try using Azure.

Give the other posts in this series a read:

IoT with an ESP8266 (Part 1) – The Hardware

IoT with an ESP8266 (Part 2) – Arduino Sketch

IoT with an ESP8266 (Part 3) – ASP.NET Core Project and Database Setup

IoT with an ESP8266 (Part 5) – IoT with Azure

Next: IoT with an ESP8266 series (Part 5) – Deploying to Azure