Sage 200 Integration with Magento 2 – Step by Step Guide

Home >> Blog >> Magento 2 >> Sage 200 Integration with Magento 2 – Step by Step Guide

Magento 2

Magneto 2 is a leading eCommerce platform with lots of advanced features to build a fully functional B2B or B2C web shop. It’s flexibility to implement custom functionalities and scalability makes it the best eCommerce platform in the world. Hire professional magento 2 upgrade service provider to migrate your website to this wonderful eCommerce platform

Sage 200

Sage 200 is an ERP system suitable for small and medium businesses. It helps to manage your stock, accounts etc all at one place

Recently one of our customers wanted to integrate sage 200 ERP with their Magento 2 web store

After discussion with Sage 200 support, we come to know that currently there are no integrations available for synchronization between Magento 2 and Sage 200 software

There is some information provided on website of Sage 200 about the integration but ultimately it leads you to contact their support and at the end it results into the situation where you need to do customization for such integration as there is no channel available which can serve as a bridge between Magento 2 and Sage 200

So our expert magento 2 developers successfully developed a custom solution for the integration that is working really well for our customer

Since there is no pre made solution available for this in the market, this step by step guide will help you to integrate Sage 200 with Magento 2 website without wasting much time

Sage 200 Setup

First you need to make sure that your Sage 200 software is setup to work with the eCommerce website for import and export procedures. This includes process of identifying the details of specific procedures and how all the details are going to be mapped with Magento because Sage 200 software has a bit different terminology for processing compared to Magento.

Also, you’ll need to set it up so that it can connect to your Magento store directory with FTP login details and in which directory it should access to fetch the updated file or do the upload.

You can also specify the interval for the import and export procedures to run periodically to process the updates. Make sure that you set this intervals as per the number of procedures and amount of data these procedures process because execution of these updates require Magento’s resources and specifying wrong intervals can easily exhaust resource limit of your Magento 2 instance and depending on the server resource limit it can result in downtime of the store.

Once you are done with this setup you need to check which action Import/Export you need to synchronize with your Magento 2 store and map the fields between Sage 200 and Magento.

Let’s take the Sales Order Import procedure for example which requires the Sales Orders of Magento store to be imported and synced with Sage 200 software for stock management control and internal processing.

The Sales Order Import procedure in Sage 200 Software accepts attributes such as OrderType, CustAccRef, OrderWarehouse, DelPostalName, InvPostalName, PaymentInFull, PaymentRef, PaymentMethod, OrderProcessing, OrderPriority, SettlementDiscountDays, LineType, ProductCode, UnitPrice, CostCentre, Department, ProjectCode, ProspectPostalName, etc.

Let’s go over few of them so we can identify how they are different in terminology compared to Magento ecommerce platform

OrderType: Sage 200 has 4 type orders acceptable – Sales Order, Sales Return, Quotation and Quotation for Prospect. Sales Order is the normal order in Magento store and Sales Return is the Credit Memo in terms of Magento store where you can refund the order amount to customers. While Quotation and Quotation for Prospect do not apply to Magento because those are like providing a Sales Quote for potential customer which can result in a Sales Order or cannot.
CustAccRef: You may understand that this might be the Customer Number from Magento store but you are wrong because Sage 200 does not have any feature to store customers and map them with orders. This is kind of a grouping option in Sage 200 where you can identify the Sales Order based on a group filter. For example, you can specify this value to be Guest or Registered Customer or specify Payment Methods and based on that you can get the Orders data by those groups.
OrderWarehouse: This helps you with tracking items and its stock through different warehouses in Sage 200 but in Magento there is no default feature available to manage stock by warehouses
DelPostalName: All the fields related to “Del” in Sage 200 can be mapped directly with “Shipping” Details in Magento 2 website. Sage 200 uses “Delivery” term instead of “Shipping”
InvPostalName: Same as above, all the fields related to “Inv” in Sage 200 can be mapped directly with “Billing” Details in Magento. Sage 200 uses “Invoice” term instead of “Billing”
PaymentInFull: This seems like a Purchase Order or Check/Money Order but it specifies if the Payment was done on the order or not with Yes/No. This is used with Quotation order type so it’s not relevant in Magento so it should be set to “Yes” always.
PaymentRef: This is supposed to contain the transaction number of the payment but Sage 200 only accepts maximum of 20 characters for this field and the actual numbers on Magento are more than 20 characters so this field can be filled with any dynamic unique value in case of disputes you require to find it with Order matching.
AnalysisCodes: Now we know that we cannot use the actual PaymentRef field as correct Transaction Id with Magento but we do require that information as it’s vital for any Sales Order. For that we can use extra fields provided by Sage 200 and fill up the details. We can use AnalysisCodeName1 as TransactionID and pass its value in AnalysisCodeValue1 from Magento store and this will be mapped in Sage 200 as a custom additional field of TransactionID. Like this we can use any other additional info in the pairs AnalysisCodeName2-AnalysisCodeValue2 to pass from Magento store
PaymentMethod: This the Payment Method name which maps with Magento but it has restriction of 20 characters as well so you may want to add short names for payment methods in Sage 200 if they go above 20 characters.
OrderProcessing: This could map with Magento’s Order Status but the Sales process in Magento is different than Sage 200. In this Sage 200 accepts orders as Allocate, Despatch, Invoice and Post Invoice while Magento process as Processing, Ship, Invoice and Complete. Though this has different it can work with mapping on some level as per guidelines with Sage 200.
OrderPriority: Magento does not have any priorities set in its Sales Order processing so we can consider all the Sales Orders to be in priority in Sage 200.
SettlementDiscountDays: We can ignore this as Magento does not have any feature for any Settlement terminology.
LineType: Sage 200 has different Types available for Products but Magento only deals with one type as Stock Items.
ProductCode: This is the SKU of Products in Magento store.
UnitPrice: This is the price of each quantity in Sales Order from Magento store.
CostCentre: Sage 200 provides this feature if the item is of different Type so you can specify its overridden price with this but Magento does not have that feature.
Department: We can ignore this as Magento does not have that feature as well but can serve as a Category based on the Business’s requirement.
ProjectCode :Sage 200 has option to map Sales Order with specific Customers which belong to a specific Project but there is no such feature available in Magento store.
ProspectPostalName: All the fields related to “Prospect” can be used if this Sales Order was related to a Quotation but we can ignore this as Magento does not have that feature.

These were only a few from the Sales Order Import procedure and there are many more but following the above instructions you can now identify them with ease to differentiate and map them with your Magento 2 website

Magento 2 Setup

Now that we have some info on the Sales Order Import procedure, we can go ahead and work on Magento for the required setup.

As per the PDF guide Data Exchange for Sage200, it needs to have CSV files created on website server for import into Sage 200 software and it can upload CSV file on server for import in Magento.

For this purpose it is recommended to create new different directories in /var/ directory of your Magento root directory and it should be for each procedure so none of them conflict with each other. For example, we can create /var/salesorderimport/ directory and in that you have only the files exported for Sales Order Import and /var/stockitemexport/ directory and in that have only the files imported for Stock Item Export procedure which exports data of Products from Sage 200 on to website server and Magento can import that data to add new Products or Update existing ones.

Make sure that the directory permissions are setup so that files can be read and written by Sage 200 software by using FTP connection.

There are 29 Import actions and 24 Export actions listed so for the synchronization it’s recommended to go through all of them and only implement which are required because each one needs customization after going through setup of Sage 200 and Magento mapping both

Integration

Once you have done setup and mapping of Sage 200 and Magento then next thing is the integration process which serves as a connection bridge to synchronize the data between Sage 200 and Magento store

For this we need to create a custom script and need to set it up from Cron Jobs in the website server to execute the script at regular intervals corresponding with the Import and Export schedule setup in Sage 200 software so it can be synced correspondingly with Magento and vice versa.

Now let’s create the script required for synchronization. We will take Sales Order Import procedure to export Sales Orders from Magento 2 store and import in Sage 200 software:

1. Start with defining the Object and method for procedure script:

class SageSalesOrderImport extends AbstractApp
{
public function run()
{
……………………
…………………...
}
}

class SageSalesOrderImport extends AbstractApp

{

public function run()

{

……………………

…………………...

}

2. Now, we need to get authorization from Magento to execute the custom functionality smoothly and using as much less resources as possible. We can easily achieve this by REST or SOAP API already available in Magento 2. We will take REST for this process so make sure you have the Username and Password with role and permissions to access Sales Order data. Once you have those details then we can get the Access Token from Magento for that user:

$this->_objectManager->get('Magento\Framework\Registry')
->register('isSecureArea', true);
$tokenURL='http://www.yourwebsite.com/index.php/rest/V1/integration/admin/token';          
$curlConnection = curl_init();
$dataUser = array("username" => "YOUR_USER", "password" => 'PASSWORD');                                                                    
$userData = json_encode($dataUser);                       
$curlConnection = curl_init($tokenURL); 
curl_setopt($curlConnection, CURLOPT_CUSTOMREQUEST, "POST");                                                                     
curl_setopt($curlConnection, CURLOPT_POSTFIELDS, $userData);                                                                  
curl_setopt($curlConnection, CURLOPT_RETURNTRANSFER, true); 
curl_setopt($curlConnection, CURLOPT_HTTPHEADER, array(                                                                          
'Content-Type: application/json',                                                                                
'Content-Length: ' . strlen($userData))                                                                       
);       
$userToken = curl_exec($curlConnection);

$this->_objectManager->get('Magento\Framework\Registry')

->register('isSecureArea', true);

$tokenURL='http://www.yourwebsite.com/index.php/rest/V1/integration/admin/token';

$curlConnection = curl_init();

$dataUser = array("username" => "YOUR_USER", "password" => 'PASSWORD');

$userData = json_encode($dataUser);

$curlConnection = curl_init($tokenURL);

curl_setopt($curlConnection, CURLOPT_CUSTOMREQUEST, "POST");

curl_setopt($curlConnection, CURLOPT_POSTFIELDS, $userData);

curl_setopt($curlConnection, CURLOPT_RETURNTRANSFER, true);

curl_setopt($curlConnection, CURLOPT_HTTPHEADER, array(

'Content-Type: application/json',

'Content-Length: ' . strlen($userData))

);

$userToken = curl_exec($curlConnection);

3. After that we will fetch all the Sales Orders and Credit Memos from last 15 minutes with the consideration that you have set your Sage 200 to fetch the Sales Orders and Credit Memos every 15 minutes.

$allOrders = $this->_objectManager->create('\Magento\Sales\Model\ResourceModel\Order\Collection')->addAttributeToSelect('*')->addFieldToFilter('created_at', array('from' => $fromTime, 'to' => $toTime))->setOrder('entity_id','ASC')->load();  
$allCreditMemos = $this->_objectManager->create('\Magento\Sales\Model\ResourceModel\Order\Creditmemo\Collection')->addAttributeToSelect('*')->addFieldToFilter('created_at', array('from' => $fromTime, 'to' => $toTime))->setOrder('entity_id','ASC')->load();

$allOrders = $this->_objectManager->create('\Magento\Sales\Model\ResourceModel\Order\Collection')->addAttributeToSelect('*')->addFieldToFilter('created_at', array('from' => $fromTime, 'to' => $toTime))->setOrder('entity_id','ASC')->load();

$allCreditMemos = $this->_objectManager->create('\Magento\Sales\Model\ResourceModel\Order\Creditmemo\Collection')->addAttributeToSelect('*')->addFieldToFilter('created_at', array('from' => $fromTime, 'to' => $toTime))->setOrder('entity_id','ASC')->load();

4. Next thing is to start creating the export file or check if it’s required to create the file if any new orders are not placed. We will create the file with the required headings in the specific directory and grant it required access.

$orderCount = count($allOrders);  
$creditMemoCount = count($allCreditMemos);
if($orderCount == 0 && $ creditMemoCount == 0) {
exit();  // No need for export file as no new Orders or Credit Memos created
}
// Continue if new Orders or Credit Memos created
$salesOrderFile = fopen('/var/salesorderimport/SalesOrders'.date('dmYHi').'.csv', 'w');
fputcsv($salesOrderFile, 
array('OrderType', 'CustAccRef', 'OrderWarehouse', 'DelPostalName', 'InvPostalName', 'PaymentInFull', 'PaymentRef', 'PaymentMethod', 'OrderProcessing',  'OrderPriority', 'SettlementDiscountDays', 'LineType', 'ProductCode', 'UnitPrice', 'CostCentre', 'Department', 'ProjectCode', 'ProspectPostalName'……)
);

$orderCount = count($allOrders);

$creditMemoCount = count($allCreditMemos);

if($orderCount == 0 && $ creditMemoCount == 0) {

exit(); // No need for export file as no new Orders or Credit Memos created

}

// Continue if new Orders or Credit Memos created

$salesOrderFile = fopen('/var/salesorderimport/SalesOrders'.date('dmYHi').'.csv', 'w');

fputcsv($salesOrderFile,

array('OrderType', 'CustAccRef', 'OrderWarehouse', 'DelPostalName', 'InvPostalName', 'PaymentInFull', 'PaymentRef', 'PaymentMethod', 'OrderProcessing', 'OrderPriority', 'SettlementDiscountDays', 'LineType', 'ProductCode', 'UnitPrice', 'CostCentre', 'Department', 'ProjectCode', 'ProspectPostalName'……)

);

5. Now we can start fetching the Orders and insert in the export file. For this we will use the REST API with the User Token we obtained.

foreach($allOrders as $oneOrder) {
$orderId = $ oneOrder ->getEntityId();
$orderURL = 'http://www.yourwebsite.com/index.php/rest/V1/orders/'
$curlConnection = curl_init($orderURL.$orderId);
curl_setopt($curlConnection, CURLOPT_CUSTOMREQUEST, "GET");
curl_setopt($curlConnection, CURLOPT_RETURNTRANSFER, true);
curl_setopt($curlConnection, CURLOPT_HTTPHEADER, array("Content-Type: application/json", "Authorization: Bearer " . json_decode($userToken)));
$orderDetails = curl_exec($curlConnection);
$orderDetails = json_decode($orderDetails, 1);
$creditMemoIds = array();
$creditMemos = $oneOrder->getCreditmemosCollection();
foreach ($creditMemos as $creditMemo) { //Go through all the credit memos for the current order.
$creditMemoIds[] = $creditMemo->getId();
}
// Fetch all the order details and set up the values as per Sage 200 requirement
$incrementid = $orderDetails['increment_id'];
$orderType = 1; // For Sales Order Order Type is 1
…………………
…………………
// Fetch all the product’s details from order and set up the values
foreach($orderDetails['items'] as $orderItem) {
$productCode = $orderItem['sku']; 
$productDescription = $orderItem['name']; 
$itemQty = $orderItem['qty_ordered'];
…………………
…………………
}
}

foreach($allOrders as $oneOrder) {

$orderId = $ oneOrder ->getEntityId();

$orderURL = 'http://www.yourwebsite.com/index.php/rest/V1/orders/'

$curlConnection = curl_init($orderURL.$orderId);

curl_setopt($curlConnection, CURLOPT_CUSTOMREQUEST, "GET");

curl_setopt($curlConnection, CURLOPT_RETURNTRANSFER, true);

curl_setopt($curlConnection, CURLOPT_HTTPHEADER, array("Content-Type: application/json", "Authorization: Bearer " . json_decode($userToken)));

$orderDetails = curl_exec($curlConnection);

$orderDetails = json_decode($orderDetails, 1);

$creditMemoIds = array();

$creditMemos = $oneOrder->getCreditmemosCollection();

foreach ($creditMemos as $creditMemo) { //Go through all the credit memos for the current order.

$creditMemoIds[] = $creditMemo->getId();

}

// Fetch all the order details and set up the values as per Sage 200 requirement

$incrementid = $orderDetails['increment_id'];

$orderType = 1; // For Sales Order Order Type is 1

…………………

// Fetch all the product’s details from order and set up the values

foreach($orderDetails['items'] as $orderItem) {

$productCode = $orderItem['sku'];

$productDescription = $orderItem['name'];

$itemQty = $orderItem['qty_ordered'];

…………………

}

6. Along with fetching the details, we will keep adding them in the file in new rows for each Sales Order and Credit Memo

$orderSaveDetails = array($orderType, $orderId, $cashAccount, $custAccRef, ….);
fputcsv($salesOrderFile, $orderSaveDetails);
if(count($creditMemoIds)){
$orderType = 2; // For Credit Memos Order Order Type is 2
$creditMemoDetails = array($ordertype, $orderId, $cashAccount, $custAccRef, ….);
}
fputcsv($salesOrderFile, $creditMemoDetails);
fclose($salesOrderFile);

$orderSaveDetails = array($orderType, $orderId, $cashAccount, $custAccRef, ….);

fputcsv($salesOrderFile, $orderSaveDetails);

if(count($creditMemoIds)){

$orderType = 2; // For Credit Memos Order Order Type is 2

$creditMemoDetails = array($ordertype, $orderId, $cashAccount, $custAccRef, ….);

}

fputcsv($salesOrderFile, $creditMemoDetails);

fclose($salesOrderFile);

By following the above example, you can get all the order details and set them as the Sage 200 requires and create the order export file from Magento 2.

Once you are done with this process and setup this script to execute periodically in the Cron Jobs then you are ready to fetch updated orders from your Magento 2 store into Sage 200 software and have successfully synchronized Sales Order Import procedure

If you are looking to integrate sage 200 with your Magento 2 web store, contact a professional magento 2 development company today.

Please feel free to write in the comments section below if you have any further questions