Main Scripts
Custom Order Processing Script Documentation

11min
Introduction
This document provides an overview and documentation for an order processing script that automates various tasks related to order data manipulation. The script is designed to be run periodically to process orders, update shipping costs, VAT rates, and generate CSV files for further processing. The files ae then uploaded to the clients dropbox for processing. Below, you'll find details on how the script works, its main functions, and the dependencies it relies on.
Installation
1.Clone the repository and install the dependencies
Shell
git clone https://github.com/E-MPS-CO-UK/TL-FileExport
cd TL-FileExport
npm install
git clone https://github.com/E-MPS-CO-UK/TL-FileExport
cd TL-FileExport
npm install
﻿
2.Run the script
Shell
node app.js
node app.js
﻿
Script Overview
The custom order processing script is written in JavaScript and serves the purpose of processing order data obtained from Linworks. It performs the following key tasks:
Retrieving order data from Linnworks.
Updating shipping costs for orders based on various conditions provided by the client.
Updating location references for orders based on specific criteria.
Updating VAT (Value Added Tax) rates for items in orders.
Converting currency for order prices to GBP (British Pound).
Generating two CSV files for further use: one for normal orders and another for orders with location references.
Script Structure
The script consists of several functions and steps. Here's an overview of the key functions and what they do:
getLinnsData()
This function retrieves order data from Linnworks using an endpoint, /Dashboards/ExecuteCustomScriptQuery, that allows you to send a custom SQL query. It performs data filtering and processing to obtain the required order information.
Text
  SELECT 
  o.nOrderId AS 'trans note',
  o.ExternalReference,
  o.SecondaryReferenceNum,
  oi.ChannelSKU AS 'item ref',
  oi.nQty AS 'quantity',
  CASE
    WHEN o.Source = 'FRUUGO' and lc.cCountry != 'United Kingdom' THEN oi.CostIncTax * 1.20
    ELSE oi.CostIncTax
  END AS 'sale price',
  oi.SalesTax as 'vat rate',
  o.cCurrency as 'currency',
  o.fPostageCost,
  o.Source,
  sl.Location as 'location',
  lc.cCountry as country,
  o.SubSource,
  o.cFullName,
  o.cEmailAddress,
  '2' as 'tender type',
  FORMAT(o.dProcessedOn, 'yyyy-MM-dd HH:mm:ss') as 'transaction date',
  'WHS' as 'location ref',
  '' as 'donor id',
  '' as 'order line note'
FROM [Order] o
INNER JOIN OrderItem oi ON oi.fkOrderID = o.pkOrderID
INNER JOIN StockLocation sl ON sl.pkStockLocationId = o.FulfillmentLocationId
INNER JOIN ListCountries lc ON lc.pkCountryId = o.fkCountryId
WHERE 
    o.bProcessed = 1 AND 
    o.Subtotal > 0 AND
    (o.SubSource != 'Resend' OR o.SubSource != 'Gift' ) AND 
    o.dProcessedOn >= DATEADD(dd,-1, GETDATE()) AND  
    o.pkOrderID NOT IN
    (
        SELECT fkOrderID
        FROM [order].orderreturn
    )
  `
  SELECT 
  o.nOrderId AS 'trans note',
  o.ExternalReference,
  o.SecondaryReferenceNum,
  oi.ChannelSKU AS 'item ref',
  oi.nQty AS 'quantity',
  CASE
    WHEN o.Source = 'FRUUGO' and lc.cCountry != 'United Kingdom' THEN oi.CostIncTax * 1.20
    ELSE oi.CostIncTax
  END AS 'sale price',
  oi.SalesTax as 'vat rate',
  o.cCurrency as 'currency',
  o.fPostageCost,
  o.Source,
  sl.Location as 'location',
  lc.cCountry as country,
  o.SubSource,
  o.cFullName,
  o.cEmailAddress,
  '2' as 'tender type',
  FORMAT(o.dProcessedOn, 'yyyy-MM-dd HH:mm:ss') as 'transaction date',
  'WHS' as 'location ref',
  '' as 'donor id',
  '' as 'order line note'
FROM [Order] o
INNER JOIN OrderItem oi ON oi.fkOrderID = o.pkOrderID
INNER JOIN StockLocation sl ON sl.pkStockLocationId = o.FulfillmentLocationId
INNER JOIN ListCountries lc ON lc.pkCountryId = o.fkCountryId
WHERE 
    o.bProcessed = 1 AND 
    o.Subtotal > 0 AND
    (o.SubSource != 'Resend' OR o.SubSource != 'Gift' ) AND 
    o.dProcessedOn >= DATEADD(dd,-1, GETDATE()) AND  
    o.pkOrderID NOT IN
    (
        SELECT fkOrderID
        FROM [order].orderreturn
    )
  `
﻿
To Understand the SQL script youll have to go through the Linnworks API Documentation and Database Structure
updateShipping(data)
This function updates shipping costs for items in orders based on various conditions, such as currency and postage cost.
JS
const shippingData = groupedData.map((group) => {
    group.map((item) => {
      logger.info(`-------updating shipping for ${item["trans note"]}`);
      if (item.country !== 'United Kingdom') {
        if (item.currency === "GBP") {
          if (item.fPostageCost == 0) {
            if (item["sale price"] > 10) {
              item["sale price"] = (parseFloat(item["sale price"]) - 5);
            }
          }
        }
      }
const shippingData = groupedData.map((group) => {
    group.map((item) => {
      logger.info(`-------updating shipping for ${item["trans note"]}`);
      if (item.country !== 'United Kingdom') {
        if (item.currency === "GBP") {
          if (item.fPostageCost == 0) {
            if (item["sale price"] > 10) {
              item["sale price"] = (parseFloat(item["sale price"]) - 5);
            }
          }
        }
      }
﻿
updatelocationRef(data)
This function updates location references for orders based on specific criteria, including source, sub-source, and email addresses.
JS
if (order.Source.toLowerCase() === "shopify") {
      if (order.cFullName.toLowerCase().includes("love the sales")) {
        order["location ref"] = "WHS";
      } else {
        order["location ref"] = "WEB";
      }
    }
if (order.Source.toLowerCase() === "shopify") {
      if (order.cFullName.toLowerCase().includes("love the sales")) {
        order["location ref"] = "WHS";
      } else {
        order["location ref"] = "WEB";
      }
    }
﻿
makeOtputCSV(data)
This function generates a CSV file for normal orders (without location references) after processing.
makeLocationOtputCSV(data)
This function generates a CSV file for orders with location references after processing.
updateVAT(data)
This function updates VAT rates for items in orders by reading data from the vat.csv file.
main()
The main function orchestrates the entire order processing workflow. It performs the following steps:
Retrieves order data from the SQL database.
Filters out already processed orders.
Updates VAT rates.
Updates shipping costs.
Updates location references.
Converts currency.
Generates CSV files.
Uploads generated files to Dropbox and an SFTP server.
Logs processing information.
Scheduling
The script uses the node-cron library to run periodically. It schedules the main() function to run at specified intervals (e.g., every 30 minutes).
Conclusion
This custom order processing script is designed to automate various tasks related to order data manipulation, shipping cost updates, VAT rate updates, and more.
Updated 25 Mar 2024
Did this page help you?
Dropshipping Script
Titles Generation Script Documentation