Magento Integration Plugin
- Administrator
- Mike Sheen
About Magento
Magento is an open-source e-commerce solution, written in PHP. Magento has two editions - Community Edition (free) and Enterprise Edition - this plugin has been developed against version 1.9.2 of the Community edition.
Links
Official Magento website.
Sample Jiwa Magento webstore.
This sample web store runs Magento 1.9.2, on Debian 8.2 using MariaDB 10.1 on the Microsoft Azure platform. You are free to choose the OS and database of your choice. We chose Debian simply because it was the Linux distribution most familiar to our developers, and we chose MariaDB because Debian has now chosen that over MySQL, but MySQL works as well. We chose Azure to host the sample store as we already had infrastructure using Azure. Amazon, or any other hosting provider would be suitable.
We also chose the Infortis Ultimo theme for the site (see below in Recommendations).
Recommendations
It is not recommended to host your webstore on-premise. Use a reputable hosting or cloud provider. Why?
- Bandwidth most business do not have the internet bandwidth required to cope with the potential demand of a public facing website.
- DoS/DDoS most businesses do not have adequate Denial of Service protections. Encountering a DoS/DDoS attack when with a hosted or cloud provider will not only be mitigated by the provider, but will not result in your on-premise connection being isolated from the internet.
- Availability You want your webstore online as much as possible. Hosted or cloud providers offer backup diesel power generators, redundant internet connections, fire suppression systems and 24 hour physical security. Cloud providers offer server redundancy to mitigate hardware failure, and automatic scale out to meet demand (if configured).
- Security A public facing website is a target for malicious attacks - separating your sensitive corporate data from such targets mitigates risk.
The Jiwa Magento integration is designed specifically with hosted or cloud hosting in mind. Even if the connection to the Jiwa system is down, your store can still take orders and when the connection is reestablished, those orders will then flow into Jiwa.
We recommend the Infortis Ultimo theme for Magento. It's not free, but at only $99USD it provides a visually attractive and responsive (usable on mobile devices and tablets) skin to the site.
About the Jiwa Magento Integration
The Jiwa Magento integration enables a Magento Web Store to be connected to the Jiwa ERP system. Magento collects orders and these are imported into Jiwa as Sales Orders. The allocation of stock and dispatch of goods is controlled by the workflow chosen.
Both account and cash (non-account) sales are supported. Account sales depend on the Magento payment method "Purchase Order" being enabled.
Supported Workflows
There are two (currently) supported worflows. Additional workflows are possible through plugin customisations.
On-Premise Warehouse
- Orders placed in Magento are imported into Jiwa as sales orders - non-serialised stock is allocated on a FIFO basis, serialised stock is forced onto backorder.
- Payments attached to Magento orders (PayPal, VISA, et cetera) are processed into Cash book receipt batches, grouped by payment type and date.
- Closed orders will cause a Shipment to be created in Magento, which includes tracking numbers from the Freight / Consignment section of the Jiwa sales order.
3PL
A Third Party Logistics (3PL) workflow is also supported. In this scenario, a 3PL will dispatch the goods and create the Magento Shipping document in the Magento Store.
- Orders placed in Magento are imported into Jiwa as sales orders - all items are placed on Backorder.
- Payments attached to Magento orders (PayPal, VISA, et cetera) are processed into Cash book receipt batches, grouped by payment type and date
- Shipments found in Magento will cause the associated Jiwa sales order to be delivered, processed and updated with the Magento Shipment tracking information
- Credit Memos placed in Magento are imported into Jiwa as credit notes.
In both workflows the following options are available:
- Stock Levels in Jiwa are exported to the Magento store.
- Products in Magento are imported into Jiwa as Inventory Items
- Inventory Items in Jiwa are export as Magento Products
- Customers in Magento are imported into Jiwa as Debtors
- Jiwa Inventory Categories are exported to Magento Product Categories
- Magento Product Categories are imported into Jiwa as Inventory Categories1...5
All of the above are termed "Integration Actions" in the context of the plugin. The above integration actions are provided, but by creating a new plugin it is possible to introduce a new integration action to replace one of the existing integration actions, or to be used in addition to the existing actions.
Installation
- Import the plugin
Import the plugin via the Plugin Maintenance form in Jiwa. Ensure the "Enabled" checkbox is checked and save the plugin.
- Run SQL Script
The plugin depends on some SQL Tables and Triggers being created. The SQL Script is found on the documents tab of the plugin - open the SQL Script with Query Analyzer and run it.
- Exit Jiwa, and log back into Jiwa
- Import Menu, User Groups
- Open the Plugin Maintenance form again and locate the Magento Integration plugin - on the documents tab, extract the Menu and User Group XML documents.
- Open Menu Maintenance (System Settings -> Menu Configuration -> Menu Maintenance) and Import the menu XML. Save.
- Open Staff User Group Maintenance (System Settings -> Staff Configuration -> User Group Maintenance) and import the User Group XML. Save.
- Add staff members to the Magento User group - only these staff members will see the Magento Menu.
- Exit Jiwa, and log back into Jiwa
Configuration
System Settings
Several system settings are introduced by the plugin - Only some of these need to be set. Edit the values on the Magento Integration tab of the System Configuration form.
An explanation of each system setting is as follows:
System Settings| Setting | Description | Notes |
|---|---|---|
| MagentoProductLastSyncDate | The date and time the Magento product changes were last synchronised. Used to obtain only a list of products changed in Magento since the last sync. | This setting is used when importing product information from Magento. There should be no need to set this, as it is set when the "Import Magento Products" integration action is run. |
| MagentoCustomerLastSyncDate | The date and time the Magento customer changes were last synchronised. Used to obtain only a list of customers changed in Magento since the last sync. | This setting is used when importing customer information from Magento. There should be no need to set this, as it is set when the "Import Magento Customers" integration action is run. |
| MagentoOrdersLastSyncDate | The date and time the Magento order changes were last synchronised. Used to obtain only a list of orders changed in Magento since the last sync. | This setting is used when importing orders from Magento. There should be no need to set this, as it is set when the "Import Magento Orders" integration action is run. |
| MagentoShipmentsLastSyncDate | The date and time the Magento shipment changes were last synchronised. Used to obtain only a list of shipments changed in Magento since the last sync. | This setting is used when importing shipments from Magento. There should be no need to set this, as it is set when the "Import Magento Shipments" integration action is run. |
| MagentoCreditMemoLastSyncDate | The date and time the Magento credit memos were last synchronised. Used to obtain only a list of credit memos changed in Magento since the last sync. | This setting is used when importing credit memos from Magento. There should be no need to set this, as it is set when the "Import Magento Credit Memos" integration action is run. |
| JiwaSalesOrdersLastSyncDateTime | The date and time the Jiwa sales orders were last synchronised. Used to obtain only a list of sales orders changed in Jiwa since the last sync. | This setting is used when exporting completed sales orders from Jiwa. There should be no need to set this, as it is set when the "Export Jiwa Sales Orders" integration action is run. |
| JiwaDebtorsLastSyncDateTime | The date and time the Jiwa debtors were last synchronised. Used to obtain only a list of debtors changed in Jiwa since the last sync. | This setting is used when exporting debtors from Jiwa. There should be no need to set this, as it is set when the "Export Debtors" integration action is run. |
| MagentoWebStoreURL | The URL of the SOAP API endpoint of the Magento store | |
| MagentoWebStoreUsesSSL | Indicates if the store uses SSL (HTTPS) | |
| MagentoUserName | The username as configured in Magento (Web Services -> SOAP/XML-RPC Users) | Use the Magento Admin panel to add a username for the SOAP Services |
| MagentoAPIKey | The API key as configured in Magento (Web Services -> SOAP/XML-RPC Users) | Use the Magento Admin panel to add an API Key for the SOAP Services |
| MagentoStoreWarehouse | The warehouse in Jiwa the Magento store will use | All orders in Jiwa will be created in this nominated warehouse. |
| CashSalesDebtor | The debtor in Jiwa to use as a cash sales debtor for Magento orders which are for guest orders |
Magento Configuration Form
If you are logged in as a user which belongs to the Magento Integration user group (imported earlier), then a new menu option will have appeared on the main menu - Magento -> Magento Configuration.
By default there are two Integration Configurations defined in the system - "Magento" and "Magento 3PL". Many configurations are supported. Each configuration consists of Selected Actions, Schedules and Logs. When a configuration is executed, it runs the selected actions in the order they are in the Selected Actions grid. The order can be changed by dragging-and dropping the selected action row up or down. Selected actions can be added to, or removed. You can add Selected actions from the Available Actions grid.
Available Integration Actions
An integration action is an action or process. By default there are 18 available Integration Actions - 13 Import and 5 Export. Using another plugin, you can introduce additional integration actions to either complement or replace the default integration actions.
Default Available Actions| Name | Direction | Description | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Import Magento Customer Pricing Groups | Import | Obtains a list of all Magento Customer groups, and attempts to find a matching Jiwa Debtor Pricing Group Name to the Magento Customer group code - if none exists, then it is created. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Import Magento Customers | Import | Obtains a list of all Magento Customers whose "updated_at" date is newer than the Jiwa System Setting "MagentoCustomerLastSyncDate" (Note the date is adjusted from local time to UTC as the Magento "updated_at" for customers is stored in UTC), and adds these to the "Magento Customer" queue - then this queue is processed in order of datetime added. If a Jiwa Debtor with the custom field "customer_id" contents matches that of the Magento customer, then the debtor is updated with the Name, billing and shipping address information. If no debtor was found, then one is created and the "customer_id" custom field on the debtor is set to be that of the Magento customer_id. Mappings - Default Magento Billing Address To Jiwa Debtor Address
Mappings - Shipping Addresses to Jiwa Delivery Addresses
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Import Magento Product Categories | Import | Retrieves the catalog tree from Magento, and attempts to match a Jiwa Inventory category based on name and depth within the catalog tree. Categories at depth level 1 are considered Jiwa Inventory Category 1, depth level 2 are considered Jiwa Inventory Category 2, et cetera. If a match on name and depth is not found, the Jiwa Inventory Category is created. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Enqueue - Import Magento Products | Import | Obtains a list of all Magento Products whose "updated_at" date is newer than the Jiwa System Setting "MagentoProductLastSyncDate" (Note the date is adjusted from local time to UTC as the Magento "updated_at" for products is stored in UTC), and adds these to the "Magento Product" queue. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Dequeue - Import Magento Products | Import | Creates or updates Jiwa inventory items from the "Magento Product" queue. If a Jiwa Inventory item with the Part No. matches that of the Magento sku, then the Inventory item is updated with the magento product details. If no Inventory item was found, then one is created with the PartNo as the sku of the Magento product. Categories encountered which do not exist in Jiwa will be created as needed. The following mapping occurs when updating or creating a product: Mappings
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Enqueue - Magento Orders | Import | Imports a list of all Magento Orders whose "updated_at" date is newer than the Jiwa System Setting "MagentoOrdersLastSyncDate" (Note the date is adjusted from local time to UTC as the Magento "updated_at" for orders is stored in UTC), and adds them to the queue "Magento Order". | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Dequeue - Import Magento Orders (All BackOrdered) | Import | Creates sales orders for all orders in the "Magento Order" queue, setting all stock deliveries to 0 - forcing all items on backorder, and processes the sales order. If a Jiwa Sales Order with the custom field "magento_increment_id" contents matches that of the Magento order increment_id, then the Sales Order will only append comments and update two custom fields - "MagentoOrderState" and "MagentoOrderStatus". If no Sales order was found, then the following occurs:
Any comments on the Magento Order not found on the Jiwa Sales Order as notes will be added as a note to the sales order. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Dequeue - Import Magento Orders (FIFO Stock) | Import | Creates sales orders for all orders in the "Magento Order" queue, allocating stock on a FIFO basis. If a Jiwa Sales Order with the custom field "magento_increment_id" contents matches that of the Magento order increment_id, then the Sales Order will only append comments and update two custom fields - "MagentoOrderState" and "MagentoOrderStatus". If no Sales order was found, then the following occurs:
If the sales order already exists in Jiwa, then Any comments on the Magento Sales Order not found on the Jiwa Sales Order as notes will be added as a note to the sales order. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Enqueue - Import Magento Shipments | Import | Adds to the "Magento Shipment" queue all Magento Shipments whose "updated_at" date is newer than the Jiwa System Setting "MagentoShipmentsLastSyncDate" (Note the date is adjusted from local time to UTC as the Magento "updated_at" for shipments is stored in UTC) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Dequeue - Import Magento Shipments | Import | Updates sales orders in the "Magento Shipment" queue - sets tracking information, and processes and closes the order. If a Jiwa Sales Order with the custom field "magento_increment_id" contents matches that of the Magento order increment_id associated with the shipment (i.e.: If we had previously created a sales order this shipment is for), then the following occurs:
Any comments on the Magento Shipment not found on the Jiwa Sales Order as notes will be added as a note to the sales order. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Enqueue - Magento Credit Memos | Import | Imports a list of all Magento Credit Memos whose "updated_at" date is newer than the Jiwa System Setting "MagentoCreditMemoLastSyncDate" (Note the date is adjusted from local time to UTC as the Magento "updated_at" for credit memos is stored in UTC), and adds them to the queue "Magento Credit Memo". | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Dequeue - Magento Credit Memos | Import | Creates credit notes for all credit memos in the "Magento Credit Memo" queue. If a Jiwa Credit Note with the custom field "magento_credit_id" contents matches that of the Magento credit memo increment_id, then the Sales Order will only append comments and update two custom fields - "MagentoOrderState" and "MagentoOrderStatus". If no credit note was found, then the following occurs:
Any comments on the Magento Credit Memo not found on the Jiwa Credit Note as notes will be added as a note to the credit note. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Export Jiwa Stock Levels | Export | Updates Magento products with the current available (SOH - Backorders) stock. Magento products are matched by sku to the Jiwa Part No. Which items to update is controlled by two triggers - one on the IN_SOH table, and one on the IN_Onbackorder table - whenever stock levels or backorders change, the item is placed into a queue if the item is not already queued. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Export Jiwa Product Categories | Export | Creates Magento categories from the Jiwa Inventory Category1,2,3,4,5. Category 1 in Jiwa is mapped to the Magento categories of depth level 1, Category 2 is mapped to depth level 2, et cetera. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Export Jiwa Inventory Items | Export | Creates or Updates Magento products from Jiwa Inventory Items. Items to update are taken from the queue "Jiwa Inventory", which is fed by a trigger on the table IN_Main. This queue is then processed in order they were added. Mappings
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Export Jiwa Sales Orders | Export | Creates shipments in Magento. Obtains a list of all Jiwa Sales Orders whose "LastSavedDateTime" date is newer than the Jiwa System Setting "JiwaSalesOrdersLastSyncDateTime", where the History status is "Delivery" and the custom field "magento_shipment_id" is not set and the custom field "magento_increment_id" indicates this order originated from Magento, and adds them to the queue "Jiwa Sales Order" - then this queue is processed in order of datetime added. The consignment note numbers on the Freight / consignment section of the sales order are added as tracking numbers to the shipment. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Export Jiwa Debtors | Export | Creates customers in Magento. Obtains a list of all Jiwa debtors whose "LastSavedDateTime" date is newer than the Jiwa System Setting "JiwaDebtorsLastSyncDateTime", where the debtor is "Web Enabled", and adds them to the queue "Jiwa Debtor" - then this queue is processed in order of datetime added. Debtors are created only - if the debtor already exists as a customer in magento then no action is taken. The debtor custom field "customer_id" is populated with the Magento customer_id when the customer is created. Mappings
|
Selected Integration Actions
When an available integration action is added as a selected integration action, you can set the Exception and Logging policies.
Exception Policy
The exception policy allows you to control the behaviour when a problem occurs. There are 3 options - Ignore, Abort Integration Action or Abort Batch.
- Ignore will tell the system to behave as nothing occurred, and continue on. If the Integration Action is drawing from a queue (such as the "Magento Order" queue), then that queue entry will be flagged as in error, the error message attached to the queue entry. The next time the queue is processed, those queue items in error will be attempted to be processed again.
- Abort Integration Action means move to the next step. If the Integration Action is drawing from a queue, then those unprocessed entries will remain in the queue
- Abort Batch means to stop all remaining processing.
Logging Policy
The logging policy is useful for troubleshooting problems. There are 3 options - Exceptions, Information and Verbose
- Exceptions means only log errors
- Information means log exceptions AND informational details - typically these are when the system has done something, like create a product or order
- Verbose means log exceptions and information and verbose log types - this can be useful in isolating performance issues.
About Queues
The plugin uses one physical table - Magento_Queue which contains many logical queues. An example of a logical queue is the "Magento Product" queue, which is a queue of all the Magento Products to import. An item can only go into a queue once, except when it is in a completed state - then there may be many completed entries for the same item in the same queue. This means that a product queued for updating can only appear once in the queue - this prevents unnecessary duplication of work. If an item is attempted to be added to a logical queue when it already is present, then it simply updates LastChangedDateTime field.
When queues are processed, only items in the queue which are of type pending or exception are examined. The items are processed in the order they were added to the queue.
Completed items will remain in the queue for as long as many days as the "Completed Queue Retention" setting is set to. Queue items can be manually removed using the "bin" button on the appropriate queue grid.
Queues are scoped global - they are visible to all integration configurations.
Schedules
The plugin allows the integration actions to be run manually, or on a scheduled basis. When running on a scheduled basis, the JiwaPluginScheduler service is used. Multiple schedules can be added to allow for different frequencies on different days / times. Typically one might want orders to be imported every 1 to 10 minutes during business hours, but every four hours outside business hours (allowing a sufficient window of time for any maintenance operations on either the Magento store or the Jiwa database) - so two schedules could be used in that instance.
Schedules available to be added are those defined on the Magento Integration plugin. Only these schedules can be attached to the Magento Configuration.
The plugin comes with a number of pre-defined schedules:
- Every 1 minute during work hours
- Weekends
- Every 10 minutes
- Every 1 minute
By default, only the "Every 1 minute" schedule is enabled. This means, by default, only Magento Configurations with a schedule of "Every 1 minute" attached will run.
Logs
The logs are scoped to an integration configuration. The log can be filtered by right-clicking the header row and selecting "Show Filter Row" - this may be useful when trying to isolate certain log entries. Logs also have a setting controlling the number of days to retain log entries for. The log can be emptied at any time using the "Clear Log" button.
Note there is an Elapsed Milliseconds and Duration Milliseconds column in the log. Elapsed Milliseconds is the number of milliseconds elapsed since the batch was started. The duration is merely the difference in milliseconds between the current and previous log entry. Using this information, performance bottlenecks are able to be identified.
Running an Integration Configuration
Running the selected actions in configuration is done by simply pressing the "Run Selected Actions" button on the Magento Configuration form. As the actions are executed, the selected action grid will update with an indicator showing the state of the action. The queues and log grids will also update as entries are added.
Customising Integration Actions
It may be required to alter the built-in mappings between Jiwa and Magento, or there may be a need to introduce a completely different integration action.
This is achieved by the plugin system. It is not recommended to edit or alter the Magento Integration plugin - the recommended way to alter the logic or behaviour of an integration action is to create a new plugin. An example of this is provided as an document attachment to the Magento Integration plugin - the document "Sample Custom Integration Action Plugin" is in fact another plugin demonstrating how to author a new integration action - in this case it is an alternate to the "Import Magento Products" integration action, named "NEW Import Magento Products".
To make the "NEW Import Magento Products" available as an integration action, save the document to file by right clicking the document and selecting "Save As..." from the context menu which appears.
Now, using the XML Import within the Plugin Maintenance form, import the previously saved plugin XML and save. This has now created the integration action plugin. Of note is the inclusion of the "Magento Integration" on the Plugin References tab - this reference makes the code within the Magento Integration plugin accessible to this new plugin.
Exit Jiwa and log back in. Now when the Magento Configuration form is loaded the "NEW Import Magento Products" is seen in the list of available integration actions.
Tutorials
Existing Jiwa installation to new Magento store
Magento Configuration
Magento needs to be configured to expose the SOAP webservices API. Login to Magento's Admin Panel
Add SOAP/XML-RPC User
From the System menu, select Web Services SOAP/XML-RPC User, and then press the Add New User button.
Fill out the form and then press the Save User button
Add SOAP/XML-RPC Role From the System menu, select Web Services SOAP/XML-RPC Roles, and then press the Add New Role button.
Key in a Role name and enter the Magento admin password
Click on the Role Resources tab, and change the Resource Access from "Custom" to "All", Then press the Save Role button.
From the System menu, select Web Services SOAP/XML-RPC Users and then select the user added previously, click on the User Role tab, check the "Assigned" optionbox next to the previously added role, and press Save User.
Under the System / Configuration menu, Services / Magento Core API section, Enable WSDL Cache and press Save Config.
Jiwa Configuration
- Open Plugin Maintenance, Enable the Plugin "Magento Integration" and save.
- On the documents tab of the plugin, save the Menu, User Group amd SQL Script documents to file (right click, save as)
- Exit Jiwa
- Run the SQL Script (be sure to run against the correct database)
- Login to Jiwa
- Open System Settings / Menu Configuration / Menu Maintenance, Import from XML the menu XML saved previously. Save.
- Open System Settings / Staff Configuration / User Group Maintenance, Import from XML the User group XML saved previously. Add your Jiwa staff user (eg: Admin) to the members of the group. Save.
- Open the System Configuration Form, and on the "Magento Integration" tab enter values for the following settings:
- MagentoWebStoreURL
- MagentoWebStoreUsesSSL
- MagentoUserName
- MagentoUserName
- MagentoStoreWarehouse
- CashSalesDebtor
- Open the Magento Configuration form
- Create a new configuration by pressing the "New" button on the ribbon. Enter a Name and save. This configuration will be used to perform the initial export of Jiwa inventory categories and products to Magento.
- Export Jiwa Inventory Categories to Magento
- Locate and select the "Export Jiwa Product Categories" action from the "Available Actions" grid and then press the "<" button to move the available action to the selected actions grid. Then save the configuration.
- Select the "Logs" tab on the bottom part of the form, then press the "Run Selected Actions" button. The Jiwa Inventory Categories will then be exported to Magento. Only categories not found in Magento are created. Changing the Log Policy to "Verbose" will display more information in the log as the integration runs. By default "Exceptions" is the log policy and only problems are logged.
- Export Jiwa Products to Magento
- Remove the "Export Jiwa Product Categories" action from the Selected actions grid by pressing the ">" button. Locate the "Export Jiwa Inventory Items" from the available actions grid and add it to the selected actions grid by pressing the "<" button. Save.
The plugin will detect which inventory items are to be exported by examining the Magento_Queue table for items with a name of "Jiwa Inventory". When we ran the magento.sql script earlier it also created a trigger on IN_Main - this trigger will automatically insert into the export queue "Jiwa Inventory" whenever a product changes or is created. To cause all products to appear in the queue, we can run a SQL script to force the trigger to run and queue all items for export:
UPDATE IN_Main SET Description = Description
This query does not change any data, but it will cause the trigger to fire and the queue table to be populated.
Refresh the Magento Configuration form and select the Queues tab and then the Jiwa Inventory Tab - it should be filled with all the products.
- Select the Logs tab again, and change the Log policy for the selected action to be "Verbose". Click the Run Selected actions button and the export should begin and the log should update as the export progresses.
