You are currently viewing documentation for Linnworks Desktop, if you are looking for Linnworks.net documentation, click here.






Website Integration - Bespoke e-commerce platform

Overview

Linnworks generic website integration module can interface with bespoke websites and e-commerce platforms by querying and updating the underlying database. This documentation outlines how to develop your own integration scripts for bespoke sites.

Note: if you are using generic e-commerce platform such as zencart, osCommerce, nopCommerce, x-cart, opencart, cubecart or any variations of it. It is most likely we already have the script for it – check out Website Integration – Gateway.

How It works

Linnworks website integration wizard will deploy so-called gateway script to your website. The purpose of the script is to receive SQL statements over HTTP protocol, query the e-commerce platform database and respond with XML formatted data, hence the gateway script needs connection details for the underlying database, see Website Integration – Gateway – Step 2.

Once the gateway is deployed you will need to write SQL queries that return data in a specific format. The types of queries and expected data is outlined below.

Implementing generic integration

  • Go to Settings > Channel Integration > Add New channel
  • Select Generic Integration
  • Choose Bespoke Site from the drop down box
  • Deploy the gateway as described in Website Integration – Gateway – Step 2.
  • Proceed to Query Designer and write the queries to the specification detailed below
  • Test the queries
  • Save the integration

 

Queries needed for downloading orders

GetNewOrderQuery

Gets the order header information, such as order reference, address, totals etc

orderId

Unique order id on your website. Used to match order header to order item, order note and order item option

fPostageCost

Shipping costs (inc tax)

dReceievedDate

Datetime order has been received in ODBC format yyyy-MM-dd HH:mm:ss Any other format will throw an error

cFullName

Ship to Customer name

cEmailAddress

Customer email address

cShippingAddress

-        no longer in use, leave empty

cPostCode

Ship to postcode/zipcode

fTotalCharge

Order Total

cCurrency

Order Currency

fTax

Order tax (as value)

Country

Country name or country ISO-2 code

PackagingGroup

- no longer in use, leave empty0000

ReferenceNum

Order Reference number as will appear in Linnworks. Make sure this is unique reference number. Generally it is recommend to append the orderid with prefix. i.e. MYWEBCART+orderId

addAddress1

Ship to address line 1

addAddress2

Ship to address line 2

addAddress3

Ship to address line 3

addTown

Ship to town/city name

addRegion

Ship to State/Region/Province

CustomerPhoneNumber

Customer phone number

addCompany

Ship to company name

BillingCompany

Billing company name

BillingName

Billing customer name

BillingAddress1

Billing address 1

BillingAddress2

Billing address 2

BillingAddress3

Billing address 3

BillingRegion

Billing State/Region/Province

BillingTown

Billing town/city

BillingCountry

Billing country

BillingPostCode

Billing postcode/zipcode

PaymentMethod

Payment method tag (see GetPaymentService query)

ShippingMethod

Shipping service tag (see GetShippingService query)

 

GetOrderItemsQuery

gets order items for the new orders. Matches order items to order by orderid column

orderid

Unique order id. This field is used to match order items to order headers

orderitemid

Unique order item id. Normally row id of the record  - used for matching order item option to order item

ItemNumber

SKU of the product

Quantity

Quantity of products ordered

CostPerUnit

Price Per unit (including all the taxes, excluding any discounts)

ItemTitle

Product Title

ProductOption

Single product option if you have one. If you have multiple options, additional info, use GetOrderItemOptionQuery

ItemTaxRate

Tax rate as percentage for single item. If you want Linnworks to recount the tax rate based on internal settings, do not provide this field in the query

DiscountPercent

Discount percentage

GetOrderNotesQuery

gets order notes for new orders. Matches order note to an order by orderId.

orderid

Unique order id, used to match order note record to an order

Note

Note text

Internal

0 – display to the customer, 1-  internal note

UserName

Username that logged the order note. Default = CUSTOMER

GetOrderItemOptionsQuery

contains order item options, additional information and attributes. Matches an option to order item by orderid and orderitemid in GetOrderItemsQuery table.

orderid

Unique order id. This field is used to match order items to order headers

orderitemid

Unique order item id. Normally row id of the record  - used for matching order item option to order item

ItemNumber

SKU of the product

Quantity

Quantity of products ordered

CostPerUnit

Price Per unit (including all the taxes, excluding any discounts)

ItemTitle

Product Title

ProductOption

Single product option if you have one. If you have multiple options, additional info, use GetOrderItemOptionQuery

ItemTaxRate

Tax rate as percentage for single item. If you want Linnworks to recount the tax rate based on internal settings, do not provide this field in the query

DiscountPercent

Discount percentage

GetOrderNotesQuery

gets order notes for new orders. Matches order note to an order by orderId.

orderid

Unique order id, used to match order note record to an order

orderitemid

Note text

nternal

0 – display to the customer, 1-  internal note

AdditionalInfoPropertyName

Username that logged the order note. Default = CUSTOMER

AdditionalInfoPropertyValue

Additional info property value (ex. A trinket)

Marking orders as despatched

Once the order is processed/despatched/shipped in Linnworks, the system will submit single UPDATE command to the website gateway, changing the status of the order to mark it as Shipped. The update statement has to be specified in MarkAsProcessedOrders. Use query tags to supply parameters into the query.

[{ReferenceNum}]

Order reference number, must be the same format as in ReferenceNum in GetNewOrderQuery. See example below

[{PostalTrackingNumber}]

Tracking number for the order

[{PostalServiceName}]

Shipping service name (as defined in Linnworks)

[{PostalServiceTag}]

Shipping service tag (as defined in Linnworks)

[{PostalServiceCode}]

Shipping service code (as defined in Linnworks)

[{Vendor}]

Shipping vendor name (as defined in Linnworks)

[{dProcessedOn}]

Datetime (format yyyy-MM-dd HH:mm:ss) of when the order has been despatched. (it is always UTC on Linnworks Anywhere)

 

UPDATE
    orders
SET
    orders_status = 3
    orders_date_finished = '[{dProcessedOn}]'
WHERE concat(orders_id,'-',CONVERT(date_purchased, UNSIGNED)) = '[{ReferenceNum}]';

 

Notice that WHERE clause contains concatenation of order id and date_purchase, this is because in the GetNewOrdersQuery we have used this concatenation to create unique order reference number for the order. Therefore Linnworks will supply exactly the same reference number in the '[{ReferenceNum}]'

 

Update inventory levels

In order to update inventory levels on your website cart you need to specify UpdateInventory statement. The update sql statement expects two tags [{ChannelSKU}] and [{NewQuantity}].

UPDATE
     products
SET
products_quantity = [{NewQuantity}],
products_status = CASE WHEN [{NewQuantity}] = 0 THEN 0 ELSE 1 END
WHERE products_model = '[{ChannelSKU}]';

Inventory list

List of inventory items on your website is used in Mapping tool to map website listed items to Linnworks Inventory.

The query can be constructed to return the whole list in one query or split the return into pages. If you have over 20000 items on your website its recommended to split the data into pages (explained below)

GetInventoryList

retrieves inventory from your website. Used in the Mapping Tool

SKU

Product SKU/id

ItemTitle

Product Title

AvailableQuantity

Stock Level

RetailPrice

Retail price (whatever currency). Used only if you want to create stock item in Linnworks from your website

Description

- not in use

GetInventoryListCount

if you want to split the GetInventoryList into pages, you must provide query to count the number of stock items and determine how many pages should be downloaded.

TotalCount

Total product count

To create paged GetInventoryList query use [{ROW_FROM}][{LIMIT}] for My SQL and [{ROW_FROM}] and [{ROW_TO}] for MS SQL.

Example My SQL -

 

GetInventoryList

SELECT     p.products_model as SKU,
              pd.products_name as ItemTitle,
    p.products_quantity as AvailableQuantity,
    p.products_price as RetailPrice
FROM zen_products p
INNER JOIN zen_products_description pd on pd.products_id = p.products_id
LIMIT [{ROW_FROM}],[{LIMIT}];

GetInventoryListCount

SELECT     Count(*) as TotalCount
FROM zen_products p
INNER JOIN zen_products_description pd on pd.products_id = p.products_id

 

GetInventoryList

SELECT * FROM (
   SELECT
    pv.Sku as [SKU],
    CASE WHEN pv.Name IS NOT NULL THEN p.Name + ' ' + pv.name ELSE  isnull(pv.Name,p.Name) END as [ItemTitle],
    pv.StockQuantity as [AvailableQuantity],
    pv.Price as [RetailPrice],
    ROW_NUMBER() OVER (ORDER BY pv.Sku) AS 'RowNumber'
FROM
    [ProductVariant] pv
INNER JOIN [Product] p on p.Id = pv.ProductId) as innerselect
WHERE innerselect.RowNumber >=[{ROW_FROM}] AND innerselect.RowNumber<=[{ROW_TO}];

GetInventoryListCount

SELECT
    Count(*) as TotalCount
FROM
    [ProductVariant] pv
INNER JOIN [Product] p on p.Id = pv.ProductId

 

List of shipping services and payment methods

To enable Shipping Service mapping and payment method mapping you need to provide queries to enable Linnworks to retrieve list of services from your website. Both queries expect two columns Tag and ServiceName. Tag is what is used to match the service when the order is downloaded from your website to the list of services in Linnworks.

GetShippingServices

SELECT id as Tag,
             Description as ServiceName
FROM ShippingMethods
Note: In order to make shipping service to work the ShippingMethod column in GetNewOrdersQuery should contains ShippingMethods.id
GetPaymentMethods

SELECT
        id as Tag,
        PaymentMethodSystemName as ServiceName
FROM [PaymentMethods]
Note: In order to make payment method mapping to work the PaymentMethod column in GetNewOrdersQuery should contains PaymentMethods.id