NopInventoryImport: Difference between revisions
No edit summary |
No edit summary |
||
| (3 intermediate revisions by 3 users not shown) | |||
| Line 6: | Line 6: | ||
select i.inventoryid as ProductID, ISNULL(id.ItemDescription,'') as Name, id.ShortDescription as ShortDescription, id.DetailedDescription as FullDescription, i.SKU, | select i.inventoryid as ProductID, ISNULL(id.ItemDescription,<nowiki>''</nowiki>) as Name, id.ShortDescription as ShortDescription, id.DetailedDescription as FullDescription, i.SKU, | ||
case when i.service = 0 then 'TRUE' else 'FALSE' end as IsShipEnabled, 'FALSE' as IsTaxExempt, 'TRUE' as Published, '' as TaxCategory, 'Manage Stock' as ManageInventoryMethod, 1000 as StockQuantity, i.RetailPrice as Price, | |||
3 as Weight, 2 as Length, 2 as Width, 2 as Height, 'Others' as Categories, '' as Picture1, '' as Picture2, '' as Picture3 | case when i.service = 0 then 'TRUE' else 'FALSE' end as IsShipEnabled, 'FALSE' as IsTaxExempt, 'TRUE' as Published, <nowiki>''</nowiki> as TaxCategory, 'Manage Stock' as ManageInventoryMethod, 1000 as StockQuantity, i.RetailPrice as Price, | ||
3 as Weight, 2 as Length, 2 as Width, 2 as Height, <nowiki>'Others' as Categories, '' as Picture1, '' as Picture2, ''</nowiki> as Picture3 | |||
from inventory i | from inventory i | ||
inner join InventoryDescriptions id | inner join InventoryDescriptions id | ||
on id.InventoryID = i.InventoryID | on id.InventoryID = i.InventoryID | ||
where status = 'a' | where status = 'a' | ||
and isnull(HasSubItems,0) = 0 | and isnull(HasSubItems,0) = 0 | ||
and i.warehouseid = 1 | and i.warehouseid = 1 | ||
and MasterInventoryID = 0 | and MasterInventoryID = 0 | ||
and id.LanguageKey = 1 | and id.LanguageKey = 1 | ||
--order by i.inventoryid | --order by i.inventoryid | ||
union --FOR GROUP PRODUCTS IF NEEDED-- | union --FOR GROUP PRODUCTS IF NEEDED-- | ||
select i.GroupID as ProductID, ISNULL(id.ItemDescription,'') as Name, id.ShortDescription as ShortDescription, id.DetailedDescription as FullDescription, i.SKU, | |||
case when i.service = 0 then 'TRUE' else 'FALSE' end as IsShipEnabled, 'FALSE' as IsTaxExempt, 'TRUE' as Published, '' as TaxCategory, 'Manage Stock' as ManageInventoryMethod, 1000 as StockQuantity, i.RetailPrice as Price, | select i.GroupID as ProductID, ISNULL(id.ItemDescription,<nowiki>''</nowiki>) as Name, id.ShortDescription as ShortDescription, id.DetailedDescription as FullDescription, i.SKU, | ||
3 as Weight, 2 as Length, 2 as Width, 2 as Height, 'Others' as Categories, '' as Picture1, '' as Picture2, '' as Picture3 | |||
case when i.service = 0 then 'TRUE' else 'FALSE' end as IsShipEnabled, 'FALSE' as IsTaxExempt, 'TRUE' as Published, <nowiki>''</nowiki> as TaxCategory, 'Manage Stock' as ManageInventoryMethod, 1000 as StockQuantity, i.RetailPrice as Price, | |||
3 as Weight, 2 as Length, 2 as Width, 2 as Height, <nowiki>'Others' as Categories, '' as Picture1, '' as Picture2, ''</nowiki> as Picture3 | |||
from InventoryGroups i | from InventoryGroups i | ||
inner join InventoryDescriptions id | inner join InventoryDescriptions id | ||
on id.InventoryID = i.GroupID | on id.InventoryID = i.GroupID | ||
where status = 'a' | where status = 'a' | ||
and i.warehouseid = 1 | and i.warehouseid = 1 | ||
and id.LanguageKey = 1 | and id.LanguageKey = 1 | ||
--order by i.GroupID | --order by i.GroupID | ||
Latest revision as of 22:51, 26 March 2024
Run this on given client and copy the results to an excel file. This query is to put items in the "Others" category in NOP which doesn't exist by default. you will need to create the "Others" Category then upload on nop.
In the NOP Administration Dashboard, go to catalog > products > and click the import button at the top and use the excel file.
select i.inventoryid as ProductID, ISNULL(id.ItemDescription,'') as Name, id.ShortDescription as ShortDescription, id.DetailedDescription as FullDescription, i.SKU,
case when i.service = 0 then 'TRUE' else 'FALSE' end as IsShipEnabled, 'FALSE' as IsTaxExempt, 'TRUE' as Published, '' as TaxCategory, 'Manage Stock' as ManageInventoryMethod, 1000 as StockQuantity, i.RetailPrice as Price,
3 as Weight, 2 as Length, 2 as Width, 2 as Height, 'Others' as Categories, '' as Picture1, '' as Picture2, '' as Picture3
from inventory i
inner join InventoryDescriptions id
on id.InventoryID = i.InventoryID
where status = 'a'
and isnull(HasSubItems,0) = 0
and i.warehouseid = 1
and MasterInventoryID = 0
and id.LanguageKey = 1
--order by i.inventoryid
union --FOR GROUP PRODUCTS IF NEEDED--
select i.GroupID as ProductID, ISNULL(id.ItemDescription,'') as Name, id.ShortDescription as ShortDescription, id.DetailedDescription as FullDescription, i.SKU,
case when i.service = 0 then 'TRUE' else 'FALSE' end as IsShipEnabled, 'FALSE' as IsTaxExempt, 'TRUE' as Published, '' as TaxCategory, 'Manage Stock' as ManageInventoryMethod, 1000 as StockQuantity, i.RetailPrice as Price,
3 as Weight, 2 as Length, 2 as Width, 2 as Height, 'Others' as Categories, '' as Picture1, '' as Picture2, '' as Picture3
from InventoryGroups i
inner join InventoryDescriptions id
on id.InventoryID = i.GroupID
where status = 'a'
and i.warehouseid = 1
and id.LanguageKey = 1
--order by i.GroupID