SQL Script Help Needed

Hi

I am integrating NopCommerce with a 3rd party channel management tool (Linnworks) that will keep my sales and inventory synced with NopCommerce, eBay and Amazon.

Unfortunately, I am having difficulty with the SQL script that posts the shipping info back to Nop. I have raised a ticket with the vendor but they are closed over the weekend and I really hoped to complete the integration this weekend. I have no SQL skill whatsoever and hope someone can take a quick look and help me out.

The problem seems to be the script is looking to one table (Order) to post all  the information however I can see from the database tables that the fields "ShippedDateUtc" and "TrackingNumber" are in a different table altogether called "Shipping". so the script would need to update two tables at the same time. Script below and thanks in advance.

UPDATE o
SET
  o.ShippingStatusId ='30',
                     o.OrderStatusId = '30',
  o.ShippedDateUtc = getdate(),
  TrackingNumber ='[{PostalTrackingNumber}]'
FROM [Order] o
WHERE
cast(o.[id] as varchar(15))+'-'+replace(replace(replace(convert(varchar, o.CreatedOnUtc, 120),' ',''),':',''),'-','')='[{ReferenceNum}]'

Use a transaction with two UPDATE statements
Also, I'd recommend that you parse the Order Id out of the {ReferenceNum} (the right hand side), because Id is the PK
(and I don't think you want the [  ] inside the quotes)

BEGIN TRAN

UPDATE [Order]
SET ...
WHERE [Id] = substring('{ReferenceNum}',?,?)

UPDATE [Shipment]
SET ...
WHERE [OrderId] = substring('{ReferenceNum}',?,?)

COMMIT


However, you will have to deal with the possibility of multiple Shipment records with same [OrderId].  Come to think of it, if you're doing the shipping in Linnworks, then there probably won't be a [Shipment] record to UPDATE, and you would insert one instead:

INSERT [Shipment] (
       [OrderId],
       [TrackingNumber],
       [ShippedDateUtc],
       [DeliveryDateUtc],
       [CreatedOnUtc],
       [TotalWeight] )
VALUES (
      substring('{ReferenceNum}',?,?)
...
)

Thanks for the help but unfortunately that didn't work.

There are about 10 scripts included that all work fine for downloading orders, updating inventory etc, just this one that is a problem.

It talks to a SQL gateway script on the server and all the order processing is done in Linnworks. Once an order is shipped in Linnworks then this script is meant to mark the order as "delivered" in NopCommerce and add a tracking number and date.
I have posted on the Linnworks forum too but no joy yet.

RE: "... that didn't work. "
Would you provide details? (Error messages?)

RE: "... It talks to a SQL gateway script on the server "
Is "It" Linnworks, and "SQL gateway script on the server" your plugin?

Finally got this to work with help from Linnworks. This is valid for NopCommerce version 2.50 onwards where shipping was moved from the order table into a separate shipping table

In case anyone has similar issues the script for Linnworks is:

UPDATE s
    SET s.TrackingNumber = '[{PostalTrackingNumber}]',
    s.ShippedDateUtc = getdate()
FROM [Order] o
INNER JOIN Shipment s ON s.orderid = o.[id]
WHERE cast(o.[id] as varchar(15))+'-'+replace(replace(replace(convert(varchar, o.CreatedOnUtc, 120),' ',''),':',''),'-','')='[{ReferenceNum}]'
--go;--
UPDATE o
    SET o.ShippingStatusId = '30',
     o.OrderStatusId = '30'
FROM [Order] o
WHERE cast(o.[id] as varchar(15))+'-'+replace(replace(replace(convert(varchar, o.CreatedOnUtc, 120),' ',''),':',''),'-','')='[{ReferenceNum}]'

Also, add the following to the end of the "GetInventoryList" script. This makes sure any deleted products are ignored as they are still in the SQL Database:

WHERE pv.deleted = 0