Execute COBOL & Fortran in Dynamics AX

A coworker of mine was pondering the idea of integrating R for data analytics into AX the other day and that got me wondering how we could make that work.

Say you have some legacy code in COBOL or Fortran, or maybe some algorithm in a Python script which you want to use from within Dynamics AX. Your options would be some form of integration if supported, or file extract and upload from AX to the legacy platform; or you could study Python and redo it all in X++.

Let’s get started. Our first stop is to register for a demo account at Sphere Engine. This gives us 1000 credits for execution. Sphere Engine has over 60 online compilers and supports languages ranging from COBOL to Fortran, C++ to VB.NET and even some lesser known languages like Lua and Erlang. Sadly I do not see R listed (yet!). It also allows the online execution of code, and supports input and output of parameters. It seems to be aimed at training and testing – so your mileage may vary.  But we’ll give it a go as a test. For production, you would want to replicate what they do by hosting your legacy environment behind an API wrapper. Same idea.

The Sphere API provided is a simple REST interface with JSON.

Below I’ve coded up a simple helper class with a number of static methods:

class SphereEngineAPI
{
    public static str SphereLanguages()
    {
        System.Net.HttpWebRequest myRequest;
        System.IO.Stream s;
        System.IO.StreamReader sr;
        str jsonResponse = "";

        myRequest = System.Net.WebRequest::Create("http://api.compilers.sphere-engine.com/api/v3/languages?access_token=<your engine key>");
        myRequest.Method = "POST";
        myRequest.Timeout = 30000;

        s = myRequest.GetResponse().GetResponseStream();
        sr = new System.IO.StreamReader(s);
        jsonResponse = sr.ReadToEnd();
        s.Close();
        sr.Close();

        return jsonResponse;
    }

    public static str SphereSubmit(str jsonInput)
    {
        System.Net.HttpWebRequest myRequest;
        System.IO.Stream s;
        System.IO.StreamWriter sw;
        System.IO.StreamReader sr;
        str jsonResponse = "";

        myRequest = System.Net.WebRequest::Create("http://api.compilers.sphere-engine.com/api/v3/submissions?access_token=<your engine key>");
        myRequest.Method = "POST";
        myRequest.Timeout = 30000;
        myRequest.ContentType = "application/json";
        s = myRequest.GetRequestStream();
        sw = new System.IO.StreamWriter(s);
        sw.Write(jsonInput);
        sw.Close();
        s.Close();
        s = myRequest.GetResponse().GetResponseStream();
        sr = new System.IO.StreamReader(s);
        jsonResponse = sr.ReadToEnd();
        s.Close();
        sr.Close();

        return jsonResponse;
    }

    public static int SphereStatus(str submissionCode)
    {
        System.Net.HttpWebRequest myRequest;
        System.IO.Stream s;
        System.IO.StreamWriter sw;
        System.IO.StreamReader sr;
        str jsonResponse = "";
        Newtonsoft.Json.Linq.JObject j;
        int statusCode = 0;

        myRequest = System.Net.WebRequest::Create("http://api.compilers.sphere-engine.com/api/v3/submissions/" + submissionCode + "?withOutput=1&access_token=<your engine key>");
        myRequest.Method = "GET";
        myRequest.Timeout = 30000;
        s = myRequest.GetResponse().GetResponseStream();
        sr = new System.IO.StreamReader(s);
        jsonResponse = sr.ReadToEnd();
        s.Close();
        sr.Close();

        j = Newtonsoft.Json.Linq.JObject::Parse(jsonResponse);
        statusCode = str2Int(j.Property("status").Value.ToString());

        return statusCode;
    }

    public static str SphereOutput(str submissionCode)
    {
        System.Net.HttpWebRequest myRequest;
        System.IO.Stream s;
        System.IO.StreamWriter sw;
        System.IO.StreamReader sr;
        str jsonResponse = "";
        Newtonsoft.Json.Linq.JObject j;
        str result = "";

        myRequest = System.Net.WebRequest::Create("http://api.compilers.sphere-engine.com/api/v3/submissions/" + submissionCode + "?withOutput=1&access_token=<your engine key>");
        myRequest.Method = "GET";
        myRequest.Timeout = 30000;
        s = myRequest.GetResponse().GetResponseStream();
        sr = new System.IO.StreamReader(s);
        jsonResponse = sr.ReadToEnd();
        s.Close();
        sr.Close();

        j = Newtonsoft.Json.Linq.JObject::Parse(jsonResponse);
        result = j.Property("output").Value.ToString().Replace("\n","");

        return result;
    }

    public static str SphereTest()
    {
        System.Net.HttpWebRequest myRequest;
        System.IO.Stream s;
        System.IO.StreamReader sr;
        str jsonResponse = "";

        myRequest = System.Net.WebRequest::Create("http://api.compilers.sphere-engine.com/api/v3/test?access_token=<your engine key>");
        myRequest.Method = "POST";
        myRequest.Timeout = 30000;

        s = myRequest.GetResponse().GetResponseStream();
        sr = new System.IO.StreamReader(s);
        jsonResponse = sr.ReadToEnd();
        s.Close();
        sr.Close();

        return jsonResponse;
    }
}

 

SphereTest() executes the API test routine, to make sure everything is up and running.

SphereLanguages() returns a JSON list of supported languages and their corresponding ID’s. Note down the ID for the language you require. Our example is in Pascal, so I’ll select 2.

SphereSubmit() submits a job for compilation and execution. You pass your API Key, language ID, and source code in JSON format.

SphereStatus() checks the execution status of the job.

SphereOutput() returns the result, if one is returned from your legacy code. Inputs we will pass in our example runnable class.

The code below is a simple test class which runs through the API calls listed above. In our example Pascal code, we send in the value 55 (you can send as many values as you want, really) and multiply that by 2, returning the result back to X++.

class TestSphereClass
{        
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {   
        str jsonResult;
        str jsonInput;
        str submissionID;
        Newtonsoft.Json.Linq.JObject j;
        str result = "";
        
        // Test Sphere API is up and running
        jsonResult = SphereEngineAPI::SphereTest();

        // get list of supported programming languages
        jsonResult = SphereEngineAPI::SphereLanguages();

        // submit a Pascal program for execution. Takes a number as input and multiplies it by 2, then returns the result
        jsonInput = '{"language": 2,"sourceCode":"program test; var x: integer; begin readln(x); writeln(x * 2); end.", "input": "55"}';
        jsonResult = SphereEngineAPI::SphereSubmit(jsonInput);

        j = Newtonsoft.Json.Linq.JObject::Parse(jsonResult);
        submissionID = j.Property("id").Value.ToString();
        
        while (SphereEngineAPI::SphereStatus(submissionID) != 0) 
        {
            // while not done, wait patiently...
        }

        // should check API for proper results (error code, failed compile) but add TODO
        result = SphereEngineAPI::SphereOutput(submissionID);

    }
}

 

Let’s run this in debug mode and see how we go:

Sphere_Output

Tada! Execution completed and the result was correctly returned.

This gives us one option to support legacy code, albeit with some limitations.

Long live Pascal!

 

Advertisements

IoT Dashboard in AX using Control Extensibility

I’ve been looking into new the control extensibility API of the latest version of Dynamics AX and it has a lot of potential. Unfortunately, my first thought when reading about it was that it could lead to a way to develop “Custom Controls” pluggable from the ToolBox, similar to other popular commercial toolsets like grids. Sadly, that is not the case (it seems) which is unfortunate. It can potentially create a substantial commercial marketplace for extensions, if possible. Perhaps I missed the point, or maybe Microsoft has plans for it in the future. I sincerely hope so.

Until then, we can still use the control extensibility API to develop small HTML based elements with custom properties, and the first stop to make would be at GitHub where Sharrief Shabazz from Microsoft has uploaded some good examples to get you going.

So starting with the BasicValueControl example, I’m going to play around and see what I can do. First, let’s create our three items required to make this work. We’ll create an HTML file and stick an IMG tag into then, then bind the SRC attribute back to our X++ code.

id="BasicControl"> data-dyn-bind="attr: {src: $control.Url}"/>

 

The X++ code that drives this binding is shown below. We’ve added a Url property which we will access from our Form.

[FormControlAttribute("BasicControl","resources/html/BasicControl", classstr(FormBuildControl))]
class IoTControl extends FormTemplateControl
{
    #define.ValuePropertyName("Url")

    FormProperty valueProperty;

    public void new(FormBuildControl _build, FormRun _formRun)
    {
        super(_build,_formRun);

        this.setTemplateId("BasicControl");
        this.setResourceBundleName("resources/html/BasicControl");

        valueProperty = this.addProperty(methodStr(IoTControl, parmValue), Types::String);

    }

    [FormPropertyAttribute(FormPropertyKind::Value, #ValuePropertyName, true)]
    public str parmValue(str _value = valueProperty.parmValue())
    {
        if(!prmisdefault(_value))
        {
            valueProperty.setValueOrBinding(_value);
        }
        return valueProperty.parmValue();
    }
}

 

So let’s create a Form based on the Custom pattern and see if we can access an offsite image located somewhere on the web. I’ll stick the image URL into the HTML controls’ property and run the form.

[Form]
public class IoTForm extends FormRun
{
    IoTControl iotControl;
    /// <summary>
    ///
    /// </summary>
    public void init()
    {
        super();

        iotControl = this.design().addControlEx(classStr(IoTControl),"IoTControl1");
        iotControl.parmValue("http://www.xxxxxxxx.com/xxxxx.png");
    }
}

 

Okay so that works well and raises a number of interesting use cases. First, instead of having to store a repository of images for your Fleet Management system inside the AX database for say, parts or stock, we can instead point the image to another site where we might have already built up a substantial library of images. Excellent.

ExControlImage

Now let’s take this a bit further. Say we have an IoT hub of sorts, receiving a number of sensor updates, and we want to create a dashboard showing these values in Dynamics AX. Let’s assume that hub is not running on Azure nor Power BI, but on another popular platform called ThingSpeak.

ThingSpeak provides a REST interface for both pushing IoT data into, and then retrieving back using the same API. So we can access the latest field values from a channel in ThingSpeak with a simple call, and with a bit of luck display this back in AX. Let’s have a look at our updated HTML control below.

id="BasicControl"> type="text" data-dyn-bind="attr: {value: $control.Url}"/>

 

We’ve modified our class a little to make the WebRequest call to ThingSpeak, given our channel, API Key and field for temperature (field 1).

[Form]
public class IoTForm extends FormRun
{
    IoTControl iotControl;

    public void init()
    {
        super();

        iotControl = this.design().addControlEx(classStr(IoTControl),"IoTControl1");

        System.Net.HttpWebRequest myRequest;
        System.Net.HttpWebResponse myResponse;
        System.IO.StreamReader reader;
        str iotResult;

        myRequest = System.Net.WebRequest::Create("http://api.thingspeak.com/channels/98558/fields/1/last?key=xxxxxxxxxx”);
        myResponse = myRequest.GetResponse();
        System.IO.Stream stream = myResponse.GetResponseStream();
        reader = new System.IO.StreamReader(stream);
        iotResult = reader.ReadToEnd();

        iotControl.parmValue(iotResult);
    }
}

 

So below we see this works, we receive back “23” into the control which is correct.

IOTControl2

A bit ugly so we can style the HTML slightly as shown below:

id="BasicControl">
type="text" data-dyn-bind="attr: {value: $control.Url}" style="padding:3px; border-color:#cccccc; text-align:center; color:#000000; font-weight:bold; font-size:30px; border-style:solid; border-width:5px; border-radius:17px; box-shadow:0px 0px 4px 0px rgba(42,42,42,.75);"/>

 

That looks a bit better.

IOT2Styled

To improve on this, let’s add two controls, one displaying temperature and the other humidity, and then create a reusable helper class for making the API calls. We’ll update the Form as shown below.

[Form]
public class IoTForm extends FormRun
{
    IoTControl iotControl1;
    IoTControl iotControl2;

    public void init()
    {
        super();

        iotControl1 = this.design().addControlEx(classStr(IoTControl),"IoTControl1");
        iotControl2 = this.design().addControlEx(classStr(IoTControl),"IoTControl2");
        
        str temp = IoTAPI::GetResult("http://api.thingspeak.com/channels/98558/fields/1/last?key=xxxxxx”);
        iotControl1.parmValue(temp);

        str hum = IoTAPI::GetResult("http://api.thingspeak.com/channels/98558/fields/2/last?key=xxxxxxx”);
        iotControl2.parmValue(hum);
    }

}

 

Our helper class accepts the URL and returns the response, as simple as it gets. You might want to add an exception handler that returns an error message instead of the value; always a good idea.

class IoTAPI
{
    public static str GetResult(str theUrl)
    {
        System.Net.HttpWebRequest myRequest;
        System.Net.HttpWebResponse myResponse;
        System.IO.StreamReader reader;
        str iotResult;

        myRequest = System.Net.WebRequest::Create(theUrl);
        myResponse = myRequest.GetResponse();
        System.IO.Stream stream = myResponse.GetResponseStream();
        reader = new System.IO.StreamReader(stream);
        iotResult = reader.ReadToEnd();
        return iotResult;
    }
}

 

So here we have the two controls shown on the form, opening up a world of new possibilities into offsite integration, data retrieval, API calls to protect IP by keeping the logic offsite, etc. etc.

IoTDashboard

Hope this post was worth your time!

 

GPS & sensors in Dynamics AX – Final

In this final part of the series I’ll focus on displaying our IoT data in Microsoft Dynamics AX. My previous project did a “pull” of the data from Windows Azure into an AX data table, and displayed all of that in a grid contained inside a basic dialog.

In this post I’ll instead display an information Tile inside an AX Workspace, containing the latest temperature and humidity information received from the sensors. We will also display the latest GPS location on a map embedded in the Workspace, with a marker that either displays in blue or red (if LPG gas is detected).

I really like the Workspaces in the new AX 7 and adding custom Tiles are pretty straightforward. Tiles can be backed by a Query which retrieves information from the AX database on a timed interval. For our example I’ll just override that functionality and set the Tile text property from code instead.

To display a map, I used the static map generator provided by Google which returns a PNG image based on the query you send to it via HTTP.

In AX, I created a new Workspace and added the required controls based on the Operational Workspace pattern. This required a number of Tab controls and eventually our Tile control. I also added the HTMLViewer control available in AX to be able to display our Google map.

Moving into X++ code mode, I then override the IOTWorkspace_OnInitialized event which gives me an opportunity to find the necessary controls and modify their behavior before the form is displayed.

So first things first, I’ll retrieve the last available record in our SQL Azure data table which is being constantly populated via our Worker Role as data comes in from the Arduino station. Given that, I build up the text to display on the tile, basically the station ID, temperature and humidity for this example. Then, I find the Tile control, and set the text programmatically.

Once this is done, I then need to find the HTMLViewer control to be able to set its URL to point to Google Maps, with the URL containing the marker color (based on the sensor reading, blue or red), latitude and longitude which I retrieved in the query. Very easy stuff.

The full X++ code is shown below:

class IOTWorkspaceClass
{
    /// <summary>
    ///
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    [FormEventHandler(formStr(IOTWorkspace), FormEventType::Initialized)]
    public static void IOTWorkspace_OnInitialized(xFormRun sender, FormEventArgs e)
    {
        HtmlViewerControl htmlViewer;
        TileButtonControl tileButton;

        System.Data.SqlClient.SqlConnection connection;
        System.Data.SqlClient.SqlCommand command;
        System.Data.SqlClient.SqlDataReader reader;

        str station;
        str latitude;
        str longtitude;
        int temp;
        int hum;
        real ratio;
        str color = "blue";
        str tileText;
        
        // retrieve update data from SQL Azure
        connection = new System.Data.SqlClient.SqlConnection("Server=tcp:z2e7xwwdy1.database.windows.net,1433;Database=IoT;User ID=xxxxx;Password=xxxxx;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
        connection.Open();

        command = new System.Data.SqlClient.SqlCommand("SELECT * FROM IoTTable ORDER BY ID DESC", connection);
        reader = command.ExecuteReader();
        while (reader.Read())
        {
            station = reader.GetString(2);
            latitude = reader.GetString(3);
            longtitude = reader.GetString(4);
            temp = reader.GetInt32(5);
            hum = reader.GetInt32(6);
            ratio = reader.GetDecimal(7);
            tileText += 'Worker ';
            tileText += station;
            tileText += ' T:';
            tileText += num2Str(temp, 0, 0, 1, 3);
            tileText += ' H:';
            tileText += num2Str(hum, 0, 0, 1, 3);

            if (ratio == 9.9) color = "red";

            break; // only need the last record
        }
        reader.Close();
        reader.Dispose();
        connection.Close();
        connection.Dispose();

        tileButton = sender.control(sender.controlId("TileButtonControl1"));
        if (tileButton != null)
        {
            tileButton.text(tileText);
        }

        htmlViewer = sender.control(sender.controlId("HtmlViewerControl1"));
        if (htmlViewer != null)
        {
            htmlViewer.parmHtml("<!DOCTYPE html><html lang=\"en\" xmlns=\"http://www.w3.org/1999/xhtml\"><head><meta charset=\"utf-8\" /><title></title></head><body><img src=\"https://maps.googleapis.com/maps/api/staticmap?zoom=13&size=600x300&zoom=16;scale=1;maptype=satelite&markers=color:" + color + "%7Clabel:A%7C" + latitude + "," + longtitude + "&key=xxxxxxxxx" /></body></html>");
        }
    }
}

 

That is pretty much it. A couple of lines of X++ and we’ve got something useful. The final running Workspace is shown below.

AXMap

Hopefully this post has been useful and helpful, and as always comments or questions are welcome.

GPS & sensors in Dynamics AX – Part 2

In the second part of this series I’ll build a base station to receive data from the monitor device constructed in part 1. This is a fairly short and simple post, and differs very slightly from the base station I previously built in another series of posts a few weeks ago. This base station can be extended to act as a repeater station, as I’ll be using the nRF905 transceiver which has a range of around 300m. So if you require better range, use another type of transceiver or build a series of repeater stations to carry the message across the distance you need.

For this station the following items will be used:

  • EtherTen (Arduino Uno) from Freeduino, which comes with Ethernet onboard
  • Two LED’s to display internet connection status
  • The usual wiring and a simple plastic container

Wiring up the station is straightforward, and we add two LED’s on digital pins 4 and 5, one being red (disconnected) the other green (connected to Windows Azure). Two resistors are required between each LED and the digital pin. The nRF905 takes up the rest of the digital ports. I’ve changed the code slightly from the previous series in that it simply accepts the data packet from a monitor, checks the header for a valid signature, and then passes it directly to an Azure Event Hub.

The completed device is shown in the following pictures. Notice there is no power source, as the EtherTen support PoE (Power over Ethernet). If using this as a repeater station, then I would consider using a solar panel as I did in part 1.

Station2

This is a pretty simple construction, with holes drilled for the LED’s, the antenna of the nRF905, and a square hole cut for the Ethernet port at the bottom.

Station1

Closed-up and unplugged it looks like something that will land you a good frisking at the airport, so keep that in mind before screwing the lid on too tight.

The sketch in C is also very simple, as shown below.

#include <nRF905.h>
#include <SPI.h>
#include <Ethernet.h>

#define RXADDR {0x58, 0x6F, 0x2E, 0x10} // Address of this station
#define TXADDR {0xFE, 0x4C, 0xA6, 0xE5} // Address of monitor device
byte mac[] = { 0x90, 0xA2, 0xDA, 0x0D, 0xBC, 0xAE };
EthernetClient client;
bool connected = false;

int ledRed = 4;
int ledGreen = 5;

void setup()
{
Serial.begin(9600);

  pinMode(ledRed, OUTPUT);
  pinMode(ledGreen, OUTPUT);
  digitalWrite(ledRed, HIGH);
  digitalWrite(ledGreen, LOW);

Ethernet.begin(mac);
Serial.println("Ethernet up...");

  nRF905_init();
  byte addr[] = RXADDR;
  nRF905_setRXAddress(addr);
  nRF905_receive();
  Serial.println("Communications up...");
}

void loop()
{
if (!connected)
{
Serial.println("Trying to connect");
char* host = "finitemindsserver.cloudapp.net"; // your Azure service or event hub details go here
client.setTimeout(10000);
connected = (client.connect(host, 10100) == 1);
if (connected) {
Serial.println("Connected to Azure...");
        digitalWrite(ledRed, LOW);
        digitalWrite(ledGreen, HIGH);
}
else
{
Serial.println("Connection unsuccessful...");
client.stop();
}
}
if (connected)
{
// get latest data from remote device
byte buffer[NRF905_MAX_PAYLOAD];
while (!nRF905_getData(buffer, sizeof(buffer)));

// got data inside buffer, check for packet signature 
if ((buffer[0] == 'G') && (buffer[1] == 'V'))
{
// send to Azure worker role for processing
client.write(buffer, sizeof(buffer));
}
// send back an ACK
byte addr[] = TXADDR;
nRF905_setTXAddress(addr);
nRF905_setData(buffer, sizeof(buffer));
while (!nRF905_send());
nRF905_receive();
}
}

 

If we fire up both the GPS monitor and the station while connected via USB, data starts to flow between the two devices. The GPS monitor sends a packet to the station, which validates the header and responds with an ACK signal.

Untitled1

The Azure Worker Role code is again similar to my previous article, in that it accepts an incoming connection and data packet, parses it into segments and sticks this into an SQL Azure table.

using System;
using System.Diagnostics;
using System.Net;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.WindowsAzure.ServiceRuntime;
using System.Net.Sockets;
using System.IO;
using System.Data.SqlClient;
using System.Text;

namespace FiniteWorker
{
    public class WorkerRole : RoleEntryPoint
    {
        private readonly CancellationTokenSource cancellationTokenSource = new CancellationTokenSource();
        private readonly ManualResetEvent runCompleteEvent = new ManualResetEvent(false);
        private AutoResetEvent connectionWaitHandle = new AutoResetEvent(false);
        private string dbConnectionString = "Server=tcp:z2e7xwwdy1.database.windows.net,1433;Database=IoT;User ID=xxxx;Password=xxxx;Trusted_Connection=False;Encrypt=False;Connection Timeout=30;";
        private SqlConnection connection = null;

        public override void Run()
        {
            TcpListener listener = null;

            try
            {
                listener = new TcpListener(RoleEnvironment.CurrentRoleInstance.InstanceEndpoints["FiniteEndPoint"].IPEndpoint);
                listener.ExclusiveAddressUse = false;
                listener.Start();
            }
            catch (SocketException)
            {
                Trace.Write("Finite server could not start.", "Error");
                return;
            }

            while (true)
            {
                 IAsyncResult result = listener.BeginAcceptTcpClient(HandleAsyncConnection, listener); 
                connectionWaitHandle.WaitOne();
            }
        }

        private void HandleAsyncConnection(IAsyncResult result)
        {
            TcpListener listener = (TcpListener)result.AsyncState;
            TcpClient client = listener.EndAcceptTcpClient(result);
            connectionWaitHandle.Set();

            while (client.Connected)
            {
                NetworkStream netStream = client.GetStream();
                StreamReader reader = new StreamReader(netStream);

                byte[] buffer = new byte[32];
                int bytesRead = client.Client.Receive(buffer,0 ,32, SocketFlags.None);
                if (bytesRead == 32) 
                {
                    if ((buffer[0] == 'G') && (buffer[1] == 'V'))
                    {
                        try
                        {
                            string strBuffer = System.Text.Encoding.UTF8.GetString(buffer, 0, buffer.Length).Replace("GV",""); // strip signature
                            string[] split = strBuffer.Split(new char[1] { ',' });
                            string lat = split[0];
                            string lon = split[1];
                            string temp = split[2];
                            string hum = split[3];
                            string ratio = split[4];

                            SqlCommand insert = new SqlCommand("INSERT INTO IOTTable (StationID, Lat, Lon, Temp, Hum, Ratio) VALUES (@stationid, @lat, @lon, @temp, @hum, @ratio)", connection);
                            SqlParameter pstationid = new SqlParameter("@stationid", "001"); // just default to 001 for prototype
                            SqlParameter plat = new SqlParameter("@lat", lat);
                            SqlParameter plon = new SqlParameter("@lon", lon);
                            SqlParameter ptemp = new SqlParameter("@temp", Convert.ToInt32(temp));
                            SqlParameter phum = new SqlParameter("@hum", Convert.ToInt32(hum));
                            SqlParameter pratio = new SqlParameter("@ratio", Convert.ToDouble(ratio));

                            insert.Parameters.Add(pstationid);
                            insert.Parameters.Add(plat);
                            insert.Parameters.Add(plon);
                            insert.Parameters.Add(ptemp);
                            insert.Parameters.Add(phum);
                            insert.Parameters.Add(pratio);
                            insert.ExecuteNonQuery();
                        }
                        catch
                        {
                        }
                    }
                }
            }
            client.Close();
        }

        public override bool OnStart()
        {
            // Set the maximum number of concurrent connections
            ServicePointManager.DefaultConnectionLimit = 12;
            bool result = base.OnStart();
            connection = new SqlConnection(dbConnectionString);
            connection.Open();
            Trace.TraceInformation("FiniteWorker has been started");
            return result;
        }

        public override void OnStop()
        {
            Trace.TraceInformation("FiniteWorker is stopping");
            connection.Close();
            this.cancellationTokenSource.Cancel();
            this.runCompleteEvent.WaitOne();
            base.OnStop();
            Trace.TraceInformation("FiniteWorker has stopped");
        }

        private async Task RunAsync(CancellationToken cancellationToken)
        {
            while (!cancellationToken.IsCancellationRequested)
            {
                Trace.TraceInformation("Working");
                await Task.Delay(1000);
            }
        }
    }
}

 

As the data flows between GPS monitor, station and Azure, the table gets populated as shown below.

IOTTableData

In part 3 we’ll access this data and display it in Microsoft Dynamics AX as a Workspace Tile and Google Map.

Until next time…

GPS & sensors to Dynamics AX- Part 1

In this three-part series of posts I’m going to explore connecting a GPS and flammable gas sensor to Microsoft Dynamics AX using a two Arduino’s. The first part is going to focus on building a small, portable GPS and sensor unit based on an Arduino Nano. We’ll be packing a lot onto the Nano, and take it a step further by powering it using a solar panel. So to start, our shopping list will consist of the following items:

  • Arduino Nano (I use the Freeduino range)
  • MQ-9 gas sensor for detecting LPG, CO and CH4
  • DHT22 temperature and humidity sensor
  • GPS unit
  • nRF905 to transmit to a base/repeater station
  • 6v solar panel
  • Resistors, wiring, connectors
  • Plastic container

Apart from the small dimensions of the Nano, the most frustrating part of making this all work was the fact that both the sensors (MQ-9 & DHT22) shipped as-is, no mounting boards. So this required some resistors and soldering. A quick search revealed what was required, as shown in the circuit below:

images

The GPS took a while to get working with the TinyGPS library as well, and after a number of failed tests and rubbish weather it finally started giving workable readings. I’ve read a number of posts about issues with the SoftwareSerial library, but I used it to read from the GPS unit without any issues. The nRF905 has become my communications module of choice. It works very well, the range is excellent and apart from requiring a substantial number of digital ports I have to vouch again for how well it works, every time. The DHT22 temperature & humidity sensor is excellent, and only required some minor soldering to add a resistor between VCC and OUT to “pull” the voltage up sufficiently back to the OUT.

I have an intense dislike of using batteries, so I opted for a proper “portable” setup by using a small solar panel instead. A quick trip to my local Jaycar shop solved the problem with a $20 unit which was rated at 6v, however I found that the output can vary anywhere between 3v to over 10v depending on the weather here in Brisbane, Australia. The panel came with clamps which I removed and replaced with a male/female socket to properly connect to the unit once done. The output of the panel is of course connected to the GND and VIN on the Nano. The completed unit mounted in a container is shown below.

Monitor2

The GPS ships with a decent length cable and mount, and was surprisingly affordable compared to some of the other modules I’ve seen. The nRF905 has a small antenna which I’ve got poking through a small hole in the container, while keeping the electronics nicely hidden inside. The power connector (to the solar panel) can be seen at the bottom of the picture. Two additional holes were drilled in the container to allow the sensors to peek through to the outside world.

The MQ-9 flammable gas sensor requires a “burn in” time of 24 hours. So what you need to do is mount it (after soldering) onto a breadboard and run it on full power for that duration. This is required to burn off any excess chemicals remaining after the manufacturing process. It produces little heat so it is safe to power on and walk away until the process is completed.

Monitor1

This shows the completed sealed unit next to the solar panel. Dimensions of the solar panel is 16cm x 16cm and it’s about 2cm thick.

To drive all of this I wrote the following sketch in C:

#include <TinyGPS.h>
#include <DHT.h>
#include <SoftwareSerial.h>
#include <nRF905.h>
#include <SPI.h>

// GPS
TinyGPS gps;
SoftwareSerial mySerial(4, 5);

// Temp/Humidity sensor
#define DHTPIN A0
#define DHTTYPE DHT22
DHT dht(DHTPIN, DHTTYPE);

// NRF905 Communications
#define RXADDR {0xFE, 0x4C, 0xA6, 0xE5} // Address of this device
#define TXADDR {0x58, 0x6F, 0x2E, 0x10} // Address of base station
char data[NRF905_MAX_PAYLOAD] = { 0 }; // buffer to send
byte buffer[NRF905_MAX_PAYLOAD]; // buffer for ACK reply

void setup()
{
  Serial.begin(9600);
  mySerial.begin(38400); // GPS
  Serial.println("GPS started...");
  
	dht.begin();
  Serial.println("DHT-22 up...");
	
	nRF905_init();
	byte addr[] = RXADDR;
	nRF905_setRXAddress(addr);
	nRF905_receive();
  Serial.println("Comms up...");
 
  delay(32000); // warm up GPS & stabilize MQ-9 gas sensor
	Serial.println("Searching for satelites...");
}

void loop()
{ 
	while (mySerial.available())
	{
 		int c = mySerial.read(); // read data coming from GPS
		if (gps.encode(c))
		{

			float lat, lon;
			unsigned long fix_age;
			gps.f_get_position(&lat, &lon, &fix_age);     // retrieves +/- lat/long in 100000ths of a degree

			// get MQ-9 gas sensor reading
			float sensor_volt;
			float RS_gas; // Get value of RS in a GAS
			float ratio; // Get ratio RS_GAS/RS_air
			int sensorValue = analogRead(A1);
			sensor_volt = (float)sensorValue / 1024 * 5.0;
			RS_gas = (5.0 - sensor_volt) / sensor_volt;
			ratio = RS_gas / 0.11; // The ratio is 9.9 in LPG gas using our calibration value set at 0.11 during sensor burn-in

			// read temperature and humidity from DHT22 sensor
			int h = (int)dht.readHumidity();
			int t = (int)dht.readTemperature();
     
			if (!(isnan(t) || isnan(h)))
			{
        // build up our data packet
        char clat[15]={0};
        char clon[15]={0};
        char crat[5]={0};

        dtostrf(lat, 10, 7, clat);
        dtostrf(lon, 10, 7, clon);
        dtostrf(ratio, 5, 2, crat);
        sprintf(data,"GV%s,%s,%d,%d,%s",clat, clon, t, h, crat);

				byte addr[] = TXADDR;
				nRF905_setTXAddress(addr);
				nRF905_setData(data, sizeof(data));

				// Send payload (send fails if other transmissions are going on, keep trying until success)
				while (!nRF905_send());
				Serial.println("sent");

				// Put into receive mode to retrieve ACK signal from base station
				nRF905_receive();

				// Wait for 'ACK' reply with timeout
				while (1)
				{
					if (nRF905_getData(buffer, sizeof(buffer)))
					{
						Serial.println("ACK");
						break;
					}
				}
			}
		}
	}
	delay(60000); // delay for a minute
}

 

This GPS requires a baud rate of 38400. I inserted a 32 second delay during setup to give all the sensors time to warm up, and the voltage from the solar panel to stabilize as much as possible. Data is transmitted to a base station with another nRF905 unit in comma-delimited text format. In the next article I’ll construct the base station to receive this data and then pass that to a worker role in Windows Azure, which in turn will store it in an SQL Azure database to be accessed from Dynamics AX.

Happy hacking!