Stock update from CSV

Before I start writing a script to do this I though I should check if anyone already has one. My clients instore system generates a stock report every few hours. On this we have the Sku, Quantity and Price. I want to use this data to update the online stock.
This is what I plan to do:
In their premises I write a script that checks to see if the CSV date and time has changed, if so, if uploads to the server via ftp.
On the SQL server I have another Script that checks for a change in the FTP file. When found, it creates a temporary table, imports the CSV to that table, updates [ProductAttributeCombination] or where there are no Attributes [Product] with the new quantity. Then deletes the temporary table to run again next time.
Does anything similar already exist?

So you are updating the quantity - Is it only for new products added ?
If not does that mean you are closing the website when you run the update ?
Otherwise Orders coming in / other operations are going to mess up your stock count

Yidna wrote:

Thanks for your concern but you have misunderstood the situation. More product is sold in store than online and online sales are processed at the store. As such the store system is where current stock holding is maintained.
Ideally, we would want a live quantity update. As an item is sold in store or online both systems would be updated but this is not possible and so the next best think is to schedule a task to run at set intervals.
The online stock will be updated using the data in the CSV at those set intervals. it is acknowledged that this will never be 100% accurate but it seems the best compromise.
Are you saying that the web site must be restarted for the changes made behind the scenes to take effect? If so, I hadn't appreciated that.

Hi Brian,
On one of my projects the nop store runs 'virtual' stock levels based on a CSV file provided by the supplier/manufacturer, which we download and update into nop db frequently.
I can confirm a stop/restart of the nop website is not required to reflect QTY changes at the db level.

To note: this method will  by-pass the 'Stock quantity history' audit trail on a product in nop, but as your bricks-and-mortar store is the inventory master, it probably is not a concern


Jon

JonQuick wrote:

Great news Jon, in that case I think this should do it:

CREATE PROCEDURE [dbo].[UpdateWebStock]
  -- Add the parameters for the stored procedure here
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

    -- If webstock table exists drop it
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME = N'webstock')
    BEGIN
      PRINT 'webstock exitst, will drop'
      DROP TABLE [dbo].[webstock]
    END
  ELSE
    BEGIN
      PRINT 'webstock not found'
    END
  -- Create webstock table
  CREATE TABLE [dbo].[webstock](
  [CODE] [nvarchar](400) NULL,
  [Quantity] [int] NULL,
  [NewPrice] [decimal](18, 4) NULL) ON [PRIMARY]
  
  -- Bulk insert from CSV
  BULK INSERT [dbo].[webstock]
  FROM 'C:\Users\nopCom\Documents\webstock.csv'
  WITH
  (
  FIELDTERMINATOR = ',', ROWTERMINATOR = '\n'
  )
  BEGIN
    PRINT 'webstock updated'
  END
  -- Update ProductAttributeCombination where matched record exists
  UPDATE [dbo].[ProductAttributeCombination]
  SET  [dbo].[ProductAttributeCombination].[StockQuantity] = [dbo].[webstock].[Quantity]
  FROM
  [dbo].[ProductAttributeCombination]
  INNER JOIN
              dbo.webstock ON [dbo].[ProductAttributeCombination].[Sku] = [dbo].[webstock].[CODE]
  WHERE [dbo].[ProductAttributeCombination].[Sku] = [dbo].[webstock].[Code]
  BEGIN
    PRINT 'ProductAttributeCombination updated'
  END
  -- Update Product where matched record exists
  UPDATE [dbo].[Product]
  SET  [dbo].[Product].[StockQuantity] = [dbo].[webstock].[Quantity]
  FROM
  [dbo].[Product]
  INNER JOIN
              dbo.webstock ON [dbo].[Product].[Sku] = [dbo].[webstock].[CODE]
  WHERE [dbo].[Product].[Sku] = [dbo].[webstock].[Code]
  BEGIN
    PRINT 'Product updated'
  END
END

The client side script will then upload the CSV and invoke the Stored Procedure. The Print Comments will be removed, those are only there for me to check what is happening.

If you spot any errors or can think of an easier way I'm all ears!

Just thought I could set a waitfor delay into the procedure so that it runs every hour then all the client side needs to do is ftp the file,

No I was not saying that you need to switch the website off for the values to update
I was just concerned that your stock wont be correct on the website if you are updating it via the import at the same time you are have some sales online
i.e. Stock = 50 items online
Then you sell 30 online but you have not packed them yet
At the same time you do an import and make the quantity 50 because you just did a stock take
Then what is the correct stock quanty you want on the website 20 or 50 ?
Anyway sounds like its not a problem if sales online are minimal

[email protected] wrote:

if this is SQL Server, set up an Agent Job on an hourly schedule:  It can then email status updates on job success/failure.

I would also recommend setting up a windows user account with very limited permissions to access that file/directory, then setup a Proxy in SQL Server to that user account for file access.

As it is SQL Express SQL Server Agent is not available.

Thanks for the recommendation vis-à-vis file permission.

JonQuick wrote:
Maybe your actual experience is accurate, but nopC does caching, so this should be verified, and not by looking at the quantity in the admin, but in the public store:
- In Admin, on a product,  enable "Display stock quantity"
- In the public store, by looking at a product, to confirm quantity.