Linnworks Integration And Stock Updates

[dajster]

Hi, this is my first scream for help, my head has exploded a few times in the last few weeks, but this one has me beat.

 

I'm using 1.54 version and have managed to build the site, integrated with Linnworks etc. all working fine with a few modifications to Linnworks script, my sql tables are showing the stock on hand at Linnworks, This is being pulled in by the reference colum in the database table.

 

My issue arises from the fact prestashop is not looking up the database qty colum to bring the stock into the store, it just sits at the amount I manually added initially, anyone got any ideas ?

 

Any help would be greatly apreciated

 

Many thanks Daj

[dajster]

Fixed, you need to update the product quantities in Prestashop to the actual quantities in Linnworks as it doesn't push the stock holding through to prestashop, it updates then the changes you make in linnworks, i.e. you update new stock on Linnworks it adds it to prestashop, you sell something on another channel it updates prestashop.

 

Hope this helps guys.

 

Rgds ..Daj

[dajster]

Upadte of the problems I had and here are my settings in Linnworks hope this helps, remember you need to manually make sure your SKUs have the correct volume of product against them in Prestashop, i.e. SKU 1 has 100 in Linnworks, it must have 100 in prestashop, as linnworks will only push changes to prestashop, hope that makes sense.

 

Here are the php scripts that are in my config settings, check yours against these, obviously back up your ones before you change them, but these work for me prestashop 1.541, Hope this helps you out, if not open a support ticket in Linnworks.

 

Getinventorylist

 

SELECT

IF(p.reference = '' ,CONCAT('PS-',p.id_product, '-', 0), p.reference) as SKU,

pl.name as ItemTitle,

IFNULL(sa.quantity, p.quantity) as AvailableQuantity,

ROUND(p.price, 2) as RetailPrice

FROM ps_product p

INNER JOIN ps_product_lang pl ON pl.id_product = p.id_product

LEFT OUTER JOIN ps_product_attribute pa ON pa.id_product = p.id_product

LEFT JOIN ps_stock_available sa ON sa.id_product = p.id_product AND sa.id_product_attribute = 0

WHERE pl.id_lang = 1 AND pa.id_product_attribute IS NULL

UNION

SELECT

IF(pa.reference = '' ,CONCAT('PS-',pa.id_product, '-', pa.id_product_attribute), pa.reference) as SKU,

pl.name as ItemTitle,

IFNULL(sa.quantity, pa.quantity) as AvailableQuantity,

ROUND((p.price + pa.price), 2) as RetailPrice

FROM ps_product_attribute pa

INNER JOIN ps_product_lang pl ON pl.id_product = pa.id_product

INNER JOIN ps_product p ON pl.id_product = p.id_product

LEFT JOIN ps_stock_available sa ON sa.id_product = p.id_product AND sa.id_product_attribute = pa.id_product_attribute

WHERE pl.id_lang = 1

 

UdateInventory

 

UPDATE ps_product

SET

quantity = CONVERT('[{NewQuantity}]', signed integer),

date_upd = '[{dProcessedOn}]'

WHERE reference = '[{ChannelSKU}]';

--GO;--

UPDATE ps_product_attribute

SET

quantity = CONVERT('[{NewQuantity}]', signed integer)

WHERE reference = '[{ChannelSKU}]';

--GO;--

UPDATE ps_stock_available sa, ps_product_attribute pa

set sa.quantity = CONVERT('[{NewQuantity}]', signed integer)

WHERE sa.id_product = pa.id_product AND sa.id_product_attribute = pa.id_product_attribute AND pa.reference = '[{ChannelSKU}]';

--GO;--

UPDATE ps_stock_available sa, ps_product pa

set sa.quantity = CONVERT('[{NewQuantity}]', signed integer)

WHERE sa.id_product = pa.id_product AND sa.id_product_attribute = 0 AND pa.reference = '[{ChannelSKU}]';

[charlie123]

Upadte of the problems I had and here are my settings in Linnworks hope this helps, remember you need to manually make sure your SKUs have the correct volume of product against them in Prestashop, i.e. SKU 1 has 100 in Linnworks, it must have 100 in prestashop, as linnworks will only push changes to prestashop, hope that makes sense.

 

Here are the php scripts that are in my config settings, check yours against these, obviously back up your ones before you change them, but these work for me prestashop 1.541, Hope this helps you out, if not open a support ticket in Linnworks.

 

Getinventorylist

 

SELECT

IF(p.reference = '' ,CONCAT('PS-',p.id_product, '-', 0), p.reference) as SKU,

pl.name as ItemTitle,

IFNULL(sa.quantity, p.quantity) as AvailableQuantity,

ROUND(p.price, 2) as RetailPrice

FROM ps_product p

INNER JOIN ps_product_lang pl ON pl.id_product = p.id_product

LEFT OUTER JOIN ps_product_attribute pa ON pa.id_product = p.id_product

LEFT JOIN ps_stock_available sa ON sa.id_product = p.id_product AND sa.id_product_attribute = 0

WHERE pl.id_lang = 1 AND pa.id_product_attribute IS NULL

UNION

SELECT

IF(pa.reference = '' ,CONCAT('PS-',pa.id_product, '-', pa.id_product_attribute), pa.reference) as SKU,

pl.name as ItemTitle,

IFNULL(sa.quantity, pa.quantity) as AvailableQuantity,

ROUND((p.price + pa.price), 2) as RetailPrice

FROM ps_product_attribute pa

INNER JOIN ps_product_lang pl ON pl.id_product = pa.id_product

INNER JOIN ps_product p ON pl.id_product = p.id_product

LEFT JOIN ps_stock_available sa ON sa.id_product = p.id_product AND sa.id_product_attribute = pa.id_product_attribute

WHERE pl.id_lang = 1

 

UdateInventory

 

UPDATE ps_product

SET

quantity = CONVERT('[{NewQuantity}]', signed integer),

date_upd = '[{dProcessedOn}]'

WHERE reference = '[{ChannelSKU}]';

--GO;--

UPDATE ps_product_attribute

SET

quantity = CONVERT('[{NewQuantity}]', signed integer)

WHERE reference = '[{ChannelSKU}]';

--GO;--

UPDATE ps_stock_available sa, ps_product_attribute pa

set sa.quantity = CONVERT('[{NewQuantity}]', signed integer)

WHERE sa.id_product = pa.id_product AND sa.id_product_attribute = pa.id_product_attribute AND pa.reference = '[{ChannelSKU}]';

--GO;--

UPDATE ps_stock_available sa, ps_product pa

set sa.quantity = CONVERT('[{NewQuantity}]', signed integer)

WHERE sa.id_product = pa.id_product AND sa.id_product_attribute = 0 AND pa.reference = '[{ChannelSKU}]';

 

Thank's, this works fine for me!

 

Regards

[LeGastronome]

it's prefered to use UpdateQuantities Prestashop function as it's triger many other function (alert customer when restock, etc...)

 

small script can help