Website Integration - Gateway

Content


Overview

This document will walk you through the steps of setting up integration with your website in Linnworks. Integrating with a generic website requires some technical knowledge and an understanding of your website database. If you are having any problems please use the ticket support system or post your questions on the forum.

Generic Website Integration allows Linnworks to do the following:

  • Automatically download new orders
  • Update the status of orders
  • Submit fulfilment data
  • Synchronise stock levels

By default there are templates for the most popular e-commerce solutions such as osCommerce, ZenCart, JSHOP and many more. These templates are for the default website integration without any modifications or plugins.

For stock to sync with your website the SKU (Item Number) needs to be the same as the item number on your website. If the item is not found, it will create a new stock item with the quantity of 99. Linnworks does this as it needs to have items in inventory before creating an order. When an order is downloaded with an order item that is not in inventory, Linnworks creates the item with 99 stock so that it can fulfil all orders that are being downloaded.

Note: If custom modifications have been installed or the database schema has changed then you may require a new script to fully integrate your site. Please also note that integration scripts are supplied on an as-is basis and thus may not take into account the configuration of your website tax calcualtions or other information.

 

Installing e-commerce website integration

Setting up e-commerce website integration

To set up Generic Website Order Book integration go to Settings > Channel Integration. From this screen you can add, delete and modify selling channels that you wish Linnworks to integrate with.

Click the Add new channel button on the right of the screen to bring up the New Channel selection screen. Select Generic Website from the list and click Integrate.

genericIntegration1.png

You will be presented with the Generic Site Config screen which will walk you through the process of integrating your website with Linnworks.

First of all, click the Get Template button to display a list of scripts available to use. Once you have found the template you need, select it and click the use button.

genericIntegration4.png

You will be asked to confirm your choice and then enter the database table prefix for your cart setup. Leave this as blank for default before clicking OK to continue. Linnworks will automatically copy across the scripts into the setup screen.

Next click the Upload Gateway button to bring up the Web Integration Script Deployment screen. From here you will need to enter the following details:

genericIntegration5.png

FTP Address – FTP server and path to the file being created i.e. ftp.linnworks.com/scripts

FTP Username – The username for your sites FTP address

FTP Password – The password for your sites FTP address

Script Password protect – You can protect your script with a password

Script URL – Where you will be deploying the integration script i.e. http://linnworks.com/scripts

Database Server – The host for your eCommerce database

Database Name – The name of your eCommerce database

Database Username – Your username for accessing the database

Database Password – The password for accessing the database

 

Once entered, click the Deploy button to submit these changes to the system and deploy the script to your website.

Back on the Config Generic Site screen you will need to enter the following details:

Gateway URL – The gateway URL is the location of the uploading gateway file. i.e. http://www.linnworks.com/scripts/linnworks_xml.php

Gateway Password – The password for your site

SiteID – The name that you wish Linnworks to label your site in Channel Integration

To add your website to the list of integrated channels, click the Save button remembering to Test the connection before you finish.

Creating your own Integration script

If you can't find already created template for your type of e-commerce website, you can create your own integration definition. In order to do that you need to know the database schema that drives your e-commerce website. If you are not confident you know the schema well enough to create the integration template ask your web designer or contact us.

GetNewOrdersQuery

SQL statement to retrieve new orders from the server. The set of orders will be retrieved every time you synchronize. Order Reference is the uniqueidentifier for the order, if the order already with the OrderReference already on the system it will be ignored without attempting to overwrite or update information. The query requires the following fields to be returned (matching the column names exactly, case sensitive).

orderID order id on the web (this will not get imported into the linnworks, it is only used to update the status of the order after it has been downloaded. And also to relate this order to order items.
shippingTotal This is the total for the shipping including tax.
dReceievedDate Date of the order, this needs to be in the format of YYYY-MM-DD.
fTotalCharge Total charge for the roder including shipping tax
fTax Total tax for the order including shipping tax
cCurrency Currency code of the order, this needs to be the international currency code of the order.
PostalServiceTag
Postage service tag which will be matched to Postage Service Tag in linnworks
ReferenceNum

Order Reference number. This is the reference number by which the order will be updated after it has been processed (dispatched).  Good practice is to add some sort of identifier to separate the reference number from all other integrated sources. For example if your order reference is simply autoincremented number (1,2,3 ...n) it might clash with order imported from other sources. To make it unique simply add something at the beginning of the order reference like 'MYWEB'+orderid to make reference number MYWEB1,MYWEB2, ... MYWEBn

This reference number should be unique for linnworks. Make sure you make it unique for all integrated system.
cFullName The combined name of the customers name.
cShippingAddress Shipping address as will appear on the postage label or an invoice. If you don't store it in the database as a single field text value. Use addCompany, addName and leave cShippingAddress blank. When the system imports data it will make cShippingAddress field from these columns while properly formating it to make an address
cPostCode Postal or Zip Code
Country Country of the delivery address, this needs to be the same as as in linnworks, for example if an order is marked as UK it needs to be converted to United Kingdom
cEmailAddress Email address of the customer.
PackagingGroup Packaging group assigned to the order. This need to be the same as the group in linnworks
addCompany Additional fields to make up shipping address
addName Additional fields to make up shipping address
addAddress1 Additional fields to make up shipping address
addAddress2 Additional fields to make up shipping address
addTown Additional fields to make up shipping address
addCounty Additional fields to make up shipping address
addCountry Additional fields to make up shipping address
addPostcode

Additional fields to make up shipping address
OrderNote

Customer Order Notes/Despatch notes
fPostageCost

This is the total for the shipping including tax.

GetOrderItemQuery

This SQL statement is executed against your database everytime you synchronize. It will be executed in conjunction with GetNewOrdersQuery where first can be seen as Order Header and second is Order Lines

orderid Order id, this should match order id in GetNewOrdersQuery
ItemNumber Item number as in linnworks (if item does not exists it will be created)
Quantity Quantity of products purchased
CostPerUnit Cost per product (not the total cost per order line). This must be including tax
ItemTitle Item title

MarkAsProcessedOrders

Once an order has been processed, ideally you need to mark it on the web as despatched/processed. The query will be executed for every website downloaded order. The query takes the following parameters:

[{dProcessedOn}]

[{OrderReference}]

[{PostalTrackingNumber}]

For example:

UPDATE
order_header
set
`status` = 6,
process_date = '[{dProcessedOn}]'
where orderreference = '[{OrderReference}]'

UpdateStockLevel

The query will get executed for every changed stock level in linnworks and for every item currently in open orders. The quer takes in two parameters [{Level_LessOrderBook}] and [{ItemNumber}]

update products
set
stockLevel=[{Level_LessOrderBook}]
where ItemNumber = '[{ItemNumber}]'

Getting help

We completely understand that the integration with the website requires some technical knowledge and understanding of your website database. Please use the ticket system to submit your query or post the question on the forum.

See Also
Selling Channels Integration
Website Integration
Inventory Management & Stock Control
Managing Inventory
Order Management
Processing an Order
Installation and setup