Integrate sage300 with a micro controller such as NodeMCU

In this article I will show you how you can use an ESP8266 and integrate it with an ERP system such as Sage 300. Reasons and possibilities are endless. This demonstration will simply create an order but you don’t only have create an order. You could possibly automatically update your item master when products are scanned on anĀ  incoming production line. Or say you have a system that will detect low consumables, this could automatically create an order to replenish stock ect ect.

Opensource IoT devices such as the Arduino and ESP8266 has come a long way. They offer some really interesting features and have become very capable to integrate with physical environments. They can do almost anything you imagine.

For this demonstration i’m using my favorite micro controller ESP8266 NodeMCU. So let’s get straight to the code.
It’s advisable to make use of the code from my Git_hub Page.
https://github.com/mailmartinviljoen/SAGE300_IoT_ESP8266_Integration

We start off with by including the below libraries into our project.

ESP8266WiFi.h Is a wifi connection manager used to connect to a Wifi network.
ESP8266HTTPClient.h Is a HTTP web client that will add functionality similar to an internet browser.

#include <ESP8266WiFi.h>
#include <ESP8266HTTPClient.h>

We then create some variables where we will store our Wifi username and password.

const char* ssid     = "APSSID_NAME";
const char* password = "AP_PASSWORD";

In the setup() void we tell the ESP9266 to do the following.
1. Use Flash button as an input button. When we press the button an order will be created.
2. Initialize the built in LED, when the order is created the LED will turn on and when done off again.
3. Start the serial port so that we have some output to the console to troubleshoot and see what’s happening.
4. Start the wifi.

void setup() {
  pinMode(0, INPUT_PULLUP); //flash button will create order
  pinMode(2, OUTPUT); //flash LED when creating order
  digitalWrite(2, HIGH);//Turn off  LED
  Serial.begin(115200);
  delay(10);

  // We start by connecting to a WiFi network

  Serial.println();
  Serial.println();
  Serial.print("Connecting to ");
  Serial.println(ssid);

  WiFi.mode(WIFI_STA);
  WiFi.begin(ssid, password);
  
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }

  Serial.println("");
  Serial.println("WiFi connected");  
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
}

In the loop() void we will monitor the button and once pressed it will execute PostOrder() void.

void loop() {
if(digitalRead(0)==LOW){ PostOrder(); }
}

 

 

The PostOrder() void will add all of the required variables needed to create an order to the HTTP header and post it to our server running PHP and Apache.
In my case i use XAMP. But you can use any other stack you wish. I.e WAMP or LAMP


void PostOrder()
{
Serial.println("Creating order");
digitalWrite(2, LOW);//Turn on LED
//Setup Order header
String ACCOUNT_CODE ="1100";
String CUST_PO_NUMBER="PO000002";
String ORDER_DESC="Order Test Desc";
String ORDER_REFERENCE="Order Test Ref";
String CUSTOMER_SHIPTO_LOCATION="001";
String SHIP_VIA_CODE="CCT";
//Setup Order detail
String ORDER_COMMENT="This is a ESP9266 Test comment";
String fItem="A14010";
String QTY_ORDERED="1";
String QTY_BACKORDERED="1";
String QTY_COMITTED="0";
String QTY_SHIPPED="0";


if(WiFi.status()== WL_CONNECTED){ //Check WiFi connection status

HTTPClient http; //Declare object of class HTTPClient

http.begin("http://192.168.1.245:8080/sagemicro/esp/create_oe_order.php"); //Specify request destination
http.addHeader("Content-Type", "application/x-www-form-urlencoded");

String POSTDATA="";
POSTDATA = POSTDATA + "ACCOUNT_CODE=" + ACCOUNT_CODE;
POSTDATA = POSTDATA + "&CUST_PO_NUMBER=" + CUST_PO_NUMBER;
POSTDATA = POSTDATA + "&ORDER_DESC=" + ORDER_DESC;
POSTDATA = POSTDATA + "&ORDER_REFERENCE=" + ORDER_REFERENCE;
POSTDATA = POSTDATA + "&CUSTOMER_SHIPTO_LOCATION=" + CUSTOMER_SHIPTO_LOCATION;
POSTDATA = POSTDATA + "&SHIP_VIA_CODE=" + SHIP_VIA_CODE;
POSTDATA = POSTDATA + "&ORDER_COMMENT=" + ORDER_COMMENT;
POSTDATA = POSTDATA + "&fItem=" + fItem;
POSTDATA = POSTDATA + "&QTY_ORDERED=" + QTY_ORDERED;
POSTDATA = POSTDATA + "&QTY_BACKORDERED=" + QTY_BACKORDERED;
POSTDATA = POSTDATA + "&QTY_COMITTED=" + QTY_COMITTED;
POSTDATA = POSTDATA + "&QTY_SHIPPED=" + QTY_SHIPPED;

int httpCode = http.POST(POSTDATA); //Send the request
String payload = http.getString(); //Get the response payload

Serial.println(httpCode); //Print HTTP return code
Serial.println(payload); //Print request response payload

http.end(); //Close connection

}else{

Serial.println("Error in WiFi connection");

}
Serial.println("Done creating order, wait 2 seconds before creating another one.");
delay(2000); //Send a request every 2 seconds
digitalWrite(2, HIGH);//Turn off LED
}

In the above example data is posted to http://192.168.1.245:8080/sagemicro/esp/create_oe_order.php
Which will collect the order header/detail fields and then properly format it into JSON to be sent to the Sage 300 Web-API.
Off coarse this can be sent directly to the Web API but, sometimes it’s better to segregate systems from each other in a way that it’s still direct in a seamless fashion.


//Order Header
if(!empty($_POST["ACCOUNT_CODE"])){$ACCOUNT_CODE=$_POST["ACCOUNT_CODE"];}else{$ACCOUNT_CODE='';}
if(!empty($_POST["CUST_PO_NUMBER"])){$CUST_PO_NUMBER=$_POST["CUST_PO_NUMBER"];}else{$CUST_PO_NUMBER='';}
if(!empty($_POST["ORDER_DESC"])){$ORDER_DESC=$_POST["ORDER_DESC"];}else{$ORDER_DESC='';}
if(!empty($_POST["ORDER_REFERENCE"])){$ORDER_REFERENCE=$_POST["ORDER_REFERENCE"];}else{$ORDER_REFERENCE='';}
if(!empty($_POST["CUSTOMER_SHIPTO_LOCATION"])){$CUSTOMER_SHIPTO_LOCATION=$_POST["CUSTOMER_SHIPTO_LOCATION"];}else{$CUSTOMER_SHIPTO_LOCATION='';}
if(!empty($_POST["SHIP_VIA_CODE"])){$SHIP_VIA_CODE=$_POST["SHIP_VIA_CODE"];}else{$SHIP_VIA_CODE='';}
if(!empty($_POST["ORDER_COMMENT"])){$ORDER_COMMENT=$_POST["ORDER_COMMENT"];}else{$ORDER_COMMENT='';}



//Order Detail
if(!empty($_POST["fItem"])){$fItem=$_POST["fItem"];}else{$fItem='';}
if(!empty($_POST["QTY_ORDERED"])){$QTY_ORDERED=$_POST["QTY_ORDERED"];}else{$QTY_ORDERED='0';}
if(!empty($_POST["QTY_BACKORDERED"])){$QTY_BACKORDERED=$_POST["QTY_BACKORDERED"];}else{$QTY_BACKORDERED='0';}
if(!empty($_POST["QTY_COMITTED"])){$QTY_COMITTED=$_POST["QTY_COMITTED"];}else{$QTY_COMITTED='0';}
if(!empty($_POST["QTY_SHIPPED"])){$QTY_SHIPPED=$_POST["QTY_SHIPPED"];}else{$QTY_SHIPPED='0';}



//Declare Array Item detail row
$ItemData='';  
//After collecting row information into $ItemData, keep adding to $allItemData array
$allItemData=array(); 
$result='';

//Add above row of data to $ItemData
$ItemData = array(
				'Item' => $fItem ,
				'QuantityOrdered' => (int)$QTY_ORDERED,
				'QuantityBackordered' => (int)$QTY_BACKORDERED,
				'QuantityCommitted' => (int)$QTY_COMITTED,
				'QuantityShipped' => (int)$QTY_SHIPPED
			);	
//Add $ItemData row set to 	$allItemData		
$allItemData[] = $ItemData;	



//Setup Order Header
$ACCOUNT_CODE='1100';
$CUST_PO_NUMBER='PO00001';
$ORDER_DESC='Order description';
$ORDER_REFERENCE='Order Reference';
$CUSTOMER_SHIPTO_LOCATION='001';
$SHIP_VIA_CODE='CCT';
$ORDER_COMMENT='This is a comment';

$data = array(
'CustomerNumber' => $ACCOUNT_CODE,
'PurchaseOrderNumber' => $CUST_PO_NUMBER,
'OrderDescription' => $ORDER_DESC,
'OrderReference' => $ORDER_REFERENCE,			
'ShipToLocationCode' => $CUSTOMER_SHIPTO_LOCATION,
'ShipViaCode' => $SHIP_VIA_CODE,
'OrderComment' => $ORDER_COMMENT,		
//Add All Item Data to the Order
'OrderDetails' => $allItemData
);

//Format Arrays into JSON 
$payload = json_encode($data);



//Output the JSON payload to screen
//header('Content-type: application/json');
//echo $payload ;



//POST the JSON DATA USING CURL

	$ENDPOINT_URL = 'http://localhost/Sage300WebApi/v1.0/-/SAMINC/OE/OEOrders';
	$ENDPOINT_USER='WEBAPI';
	$ENDPOINT_PASS='WEBAPI';	

			$CurlHeader = curl_init($ENDPOINT_URL);		
			//set user and pass
			curl_setopt($CurlHeader, CURLOPT_USERPWD, "$ENDPOINT_USER:$ENDPOINT_PASS"); //Your credentials goes here
			//attach encoded JSON string to the POST fields
			curl_setopt($CurlHeader, CURLOPT_POSTFIELDS, $payload);
			//set the content type to application/json
			curl_setopt($CurlHeader, CURLOPT_HTTPHEADER, array('Content-Type:application/json'));
			//return response instead of outputting
			curl_setopt($CurlHeader, CURLOPT_RETURNTRANSFER, true);	
			//execute the POST request after checking if it may be posted
			$result = curl_exec($CurlHeader);
				
			//close cURL resource
			curl_close($CurlHeader);
		
		
		//Check if error exist
		$JSON_OBJ='{}';
		$JSON_OBJ = json_decode($result,true); //Decode data from URL into JSON Format
		if(isset($JSON_OBJ["error"])){
			$Error_Check = $JSON_OBJ["error"]; //Extractet field value with  ObjectName:error
			echo $Error_Check['message']['value'];
		}	
		if(isset($JSON_OBJ["OrderNumber"])){
			$SystemOrderNumber = $JSON_OBJ["OrderNumber"]; //Extractet field value 		
			$OrderUniquifier = $JSON_OBJ["OrderUniquifier"]; //Extractet field value 
			echo 'SUCCESS=ORDER CREATED&ORDERNUMBER=' . $SystemOrderNumber;
		}	

When you finally press the flash button on the ESP8266 the order will be created in Sage 300.
Note: At this stage you can disconnect the ESP8266 from your computer and power it as a stand alone device and it will connect via wifi to your network.

You May Also Like

About the Author: Martin Viljoen

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA ImageChange Image