Actinic to Linnworks Script

[gabrielcrowe]

On a side note, Linnworks (and any sort of courier integration like the one i did for parcelforce) loves you more if you feed it nice clean addresses from http://www.postcodeanywhere.co.uk/ . If you dont, you'll be correcting crappy broken postcodes manually.

[gabrielcrowe]

I'll be releasing it for testing for people who want to play with it next week, likely mid week after some testing on the SQL given to me just above. Hopefully stock sync will work and then I can go back to rocking back and forwards in the corner mumbling about APIs and PHP.

[gabrielcrowe]

First Alpha - Handle with care

Download here:
http://www.gabrielcrowe.co.uk/upload...-connector.zip
(Install instructions included in zip)

If you get it working and feel compelled to donate to further development then paypal me a beer:
paypal@angrydinosaur.co.uk

BACK UP YOUR SHOP BACK UP YOUR SHOP BACK UP YOUR SHOP BACK UP YOUR SHOP BACK UP YOUR SHOP BACK UP YOUR SHOP BACK UP YOUR SHOP BACK UP YOUR SHOP BACK UP YOUR SHOP BACK UP YOUR SHOP BACK UP YOUR SHOP.

Got it? Good.


### Linking Linnworks to Actinic to fetch orders.

This document explains how to get data out of Actinic Catalog and into Linnworks for processing. Due to limitations in the sync, the data is really only useful for postal processing (label automation) and invoice printing (Linnworks supports easily editable custom templates.)

It DOES:
- Sync orders and associated line items
- Sync delivery and shipping addresses separately
- Highlight orders in Actinic Catalog when you 'complete' them in Linnworks *
- Get the email address so you can email from Linnworks
- Sync 'Order Notes' or special delivery instructions.
- Sync only 'Full Payment Received' AND 'Pre-authorized (PSP)' orders
- Sync only 'New' orders **.

It DOES NOT:
- Complete orders in Actinic catalog
- Sync ANY stock back to Actinic Catalog
- Sync Deleted Orders
- Care what payment provider you use
- Sync payment 'Pending'
- Work for delivery addresses outside the UK ***



NOTES:

* Linnworks cannot complete orders in the traditional sense because Actinic does a lot more than simply set a status. Linnworks sends a single SQL query to Actinic and this query isn't nearly advanced enough. My workaround was to simply highlight the row in Actinic for manual processing using a real human being. Actinic lets you complete in batches so I don't see this as a big hurdle.

** Because of the behaviour or syncing only pending orders, the moment you 'complete' an order in Actinic Catalog, it will not sync. You should sync BEFORE you fiddle with your orders in Actinic.

*** To get this working as soon as possible I assume that all countries are in the UK. this will be fixed in a later version.

(Copy of this post and some more good mouth words here:
http://www.gabrielcrowe.co.uk/2011/1...k-in-progress/)

[fergusw]

Gabe,
Did you find a problem with the updating back into Actinic? Did it not work or was there another unforseen issue with it?

[gabrielcrowe]

This release has no stock updating. its missing entirely.

I wanted to get the basics released first and open that 'can of worms' later.

I'm going to work on that proper stock mechanism for the next release.

[gabrielcrowe]

I just want to confirm this will work. All I have is a single SQL statement to update the stock level. Linnworks loops over the items ordered in the last batch and sends the number back through the sync manager.

I get

I can do:
UPDATE Product SET Product.[Product Reference] = "[Reference_Number]", Product.nStockOnHand = [STOCK_IN_HAND];

for each affected product.... but i dont think I can do

UPDATE OutstandingStockChanges SET OutstandingStockChanges.sProdRef = "ABC123", OutstandingStockChanges.nStatus = 0, OutstandingStockChanges.nChange = 1;

in the same statement. Can anyone think of a way to do this in a single sql statement?

[fergusw]

Could you not just execute both statements in one command separated by ';' ?

If you have the pre-change stock level (psl) and the new level you can get the variance in stock i.e. +10 or -5. With the new stock level and this variance as variable1 and variable2 could you not execute

'UPDATE Product SET Product.[Product Reference] = "[Reference_Number]", Product.nStockOnHand = [variable1];UPDATE OutstandingStockChanges SET OutstandingStockChanges.sProdRef = "ABC123", OutstandingStockChanges.nStatus = 0, OutstandingStockChanges.nChange = [variable2];'

Non?

[gabrielcrowe]

nope, it chokes if you pass 2 sql statements.

[gabrielcrowe]

The Linnworks integration script is a php file that sits near the database and catches sql to execute against a database. In this case the server is Actinet and the database is 'Actiniccatalog.mdb'.

In the config, you paste SQL statements to run when things happen in the Linnworks software. When doing the 'updating stock' bit a generic script runs for each item affected by recent transactions/stock movements:

UPDATE Product 
SET Product.nStockOnHand = [{Level_LessOrderBook}]
WHERE Product.[Product Reference] = '[{ItemNumber}]';

The only variables I can use are the ones shown.

From their own docs:

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

Cant I just blank the other table, reset to 0 so that no changes occur?

[gabrielcrowe]

can I get an actinic developer person to chime in with a way to do this given the variables shown?

I may have to write a custom thingummy to catch any extra variables.

[fergusw]

I think a custom "thingummy" would possible be required.

If your only stock variable from LinnWorks is {Level_LessOrderBook} and this represents the new stock level to be updated back to Actinic then you're going to need an intermediate query to Actinic to retrieve the current stock level difference from Actinic to insert back into the "OutstandingStockChanges"

e.g. Something like

UPDATE OutstandingStockChanges SET OutstandingStockChanges.sProdRef = "{ItemNumber}", OutstandingStockChanges.nStatus = 0, OutstandingStockChanges.nChange = (SELECT Product.nStockOnHand - {Level_LessOrderBook} WHERE Product.[Product Reference] = {ItemNumber});


Just a possible....

[gabrielcrowe]

I think what i'll do is write something into the actinet connector that looks in the query for a trigger term like: 'STOCK_UPDATE'. If the query contains this word then i can simply pass this:

STOCK_UPDATE|ITEMREF|NEWSTOCK

instead of SQL and create a function in the php to manage the stock. the function could pull the current stock and do all the things on this page. Back to the drawing board.

So can anybody help me determine what versions of actinic i would use this in? Is this only version 10/11 or can I simply insert the stock number into the table without all this fuss in v9/8?

[gabrielcrowe]

Ferg, you're a hero btw.

[gabrielcrowe]

HOTFIX: Due to a pathetic schoolboy error on my part, the time was not correctly passed to Linnworks, meaning all orders look like they happened at midnight.

On line 170 of the connector look for this line:

$thisvalue = $thisyear.'-'.$thismonth.'-'.$thisday;

and make it say this:

$thisvalue = $thisyear.'-'.$thismonth.'-'.$thisday.' '.$thisdatearr[1];

Now the correct times will be passed on your next sync.

[malbro]

I think what i'll do is write something into the actinet connector that looks in the query for a trigger term like: 'STOCK_UPDATE'. If the query contains this word then i can simply pass this:

STOCK_UPDATE|ITEMREF|NEWSTOCK

instead of SQL and create a function in the php to manage the stock. the function could pull the current stock and do all the things on this page. Back to the drawing board.

So can anybody help me determine what versions of actinic i would use this in? Is this only version 10/11 or can I simply insert the stock number into the table without all this fuss in v9/8?

V10 is the first version with online stock control, which introduced the concept of synchronising stock between the desktop and the web site. Prior to that you only had the link with EPOS to consider