Custom Order Processing Script Documentation

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 git clone https //github com/e mps co uk/tl fileexport cd tl fileexport npm install 2 run the script 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 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 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 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