JonQuick wrote: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
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!