Product List including products with attributes

Hi,

Is there any way to get a full list of products, prices, SKUs and stock information. I have created the following that gives me a list, but the name of each product is the same, as the attributes can only be associated to products using the AttributesXml info and I don't know how to use that to show the associated product attributes, why is this info not in a link table?

My user uses excel to do his updates, as doing it through the admin section is very time consuming and they usually change all prices in one go. I then have update statements created using excel formulas to do the updates in one go.

Any help appreciated.

Anthony

SELECT p.[Id]
      ,[Name]
      ,[Published]
      ,[Deleted]
      ,coalesce(pvac.sku,p.[SKU])
      ,[IsShipEnabled]
      ,[IsFreeShipping]
      ,[AdditionalShippingCharge]
      ,coalesce(pvac.[StockQuantity],p.[StockQuantity])
      ,[DisableBuyButton]
      ,[CallForPrice]
      ,coalesce(pvac.OverriddenPrice, p.[Price]) as Price
    ,coalesce(pvac.id,0) as attributeId
  FROM [dbo].[Product] p
  left join [dbo].[ProductVariantAttributeCombination] pvac
  on (pvac.productid = p.id)

The following should get all of the info for three product attributes, ( I only use two now)
All I need to do now is write the formulas in Excel to create the SQL scripts for each product, product attribute and product attribute combination update. Maybe this will help others.

SELECT p.[Id]
      ,coalesce(pvac.sku,p.[SKU]) as SKU
      ,p.[Name]
      ,p.[Price]
    ,pvacid as ProdAttCombinationId
    ,coalesce(pvac.att1Id,999999) as AttributeId1
      ,coalesce(pvac.att1Name,'No Variant1') as AttributeName1
      ,coalesce(pvac.Att1Adjustment,0) as PriceAdjustment1
    ,coalesce(pvac.att2Id,999999) as AttributeId2
      ,coalesce(pvac.att2Name,'No Variant2') as AttributeName2
      ,coalesce(pvac.Att2Adjustment,0) as PriceAdjustment2
    ,coalesce(pvac.att3Id,999999) as AttributeId3
      ,coalesce(pvac.att3Name,'No Variant3') as AttributeName3
      ,coalesce(pvac.Att3Adjustment,0) as PriceAdjustment3
      ,[Published]
      ,[Deleted]
      ,[IsShipEnabled]
      ,[IsFreeShipping]
      ,[AdditionalShippingCharge]
      ,[DisableBuyButton]
      ,[CallForPrice]
      ,coalesce(pvac.[StockQuantity],p.[StockQuantity]) as StockQuantity
  FROM [dbo].[Product] p
  left join (select  y.[ProductId]
                  ,y.pvacid
          ,y.sku
          ,y.[StockQuantity]
          ,pvav1.id as att1Id
          ,pvav1.Name as att1Name
          ,pvav1.PriceAdjustment as Att1Adjustment
          ,pvav2.id as att2Id
          ,pvav2.Name as att2Name
          ,pvav2.PriceAdjustment as Att2Adjustment
          ,pvav3.id as att3Id
          ,pvav3.Name as att3Name
          ,pvav3.PriceAdjustment as Att3Adjustment
       from (Select x.pvacid
          ,xml
          ,xml.value('(/Attributes/ProductVariantAttribute/@ID)[1]','int') as ProductVariantAttributeID1 
          ,xml.value('(/Attributes/ProductVariantAttribute/ProductVariantAttributeValue/Value)[1]','int') as ProductVariantAttributeValue1
          ,xml.value('(/Attributes/ProductVariantAttribute/@ID)[2]','int') as ProductVariantAttributeID2
          ,xml.value('(/Attributes/ProductVariantAttribute/ProductVariantAttributeValue/Value)[2]','int') as ProductVariantAttributeValue2
          ,xml.value('(/Attributes/ProductVariantAttribute/@ID)[3]','int') as ProductVariantAttributeID3
          ,xml.value('(/Attributes/ProductVariantAttribute/ProductVariantAttributeValue/Value)[3]','int') as ProductVariantAttributeValue3
          ,x.sku
          ,x.[StockQuantity]
          ,x.[ProductId]
          FROM (SELECT convert(xml , attributesxml ) as xml
            ,pvac.sku
            ,pvac.[StockQuantity]
            ,pvac.id as pvacid
            ,pvac.[ProductId]
            FROM [activele_leisure_nop].[dbo].[ProductVariantAttributeCombination] pvac ) as x) as y
      left join [dbo].[ProductVariantAttributeValue] as pvav1 
            on(  y.ProductVariantAttributeValue1 = pvav1.Id) 
      left join [dbo].[ProductVariantAttributeValue] as pvav2 
            on(  y.ProductVariantAttributeValue2 = pvav2.Id) 
      left join [dbo].[ProductVariantAttributeValue] as pvav3 
            on(  y.ProductVariantAttributeValue3 = pvav3.Id) 
            ) as pvac
        on (pvac.productid = p.id)

cheers,

Anthony

Hi,

this is very interesting, because I am facing the same problem. Sadly, I cannot get this query to work because there is no table with ProductVariantAttributeCombination anymore in NopCommerce 3.6. But does this query allow you to update the product attributes as well?
I would like to add product information in an Excel sheet as well and add the attributes in there to upload directly in the database.

thanks for your help.

kind regards,

Hi,

The query does not need to be so complicated to do an insert. Just create the xml using a formula in Excel, including the insert then copy and paste the statements to the SSMS console and execute. (Do test first to ensure the structure of the xml is correct).
Structure of insert as below, but altered for appropriate table and fields:

INSERT INTO myxmltable
  (field1, xmlfield) 
VALUES 
  ('text field', N'<?xml version="1.0" encoding="utf-16" standalone="yes"?>......';

How to list Stock Quantity of Products with Inventory Method set to "Track inventory by product attributes"

SELECT TOP 1000 p.[Id] AS ProductId,
      p.[Name] AS ProductName,
      p.[Sku] AS ProductSKU,
      p.[ManufacturerPartNumber],
      p.[StockQuantity] AS ProductStockQuantity,
      pa.[Name] AS ProductAttributeName,
      pam.[TextPrompt] + ': ' + pav.[Name] AS ProductAttributeValue,
      pac.[Sku] AS ProductAttributeValueSKU,
      pac.[StockQuantity] AS ProductAttributeValueStockQuantity

  FROM [dbo].[Product] p
                                JOIN [dbo].[Product_ProductAttribute_Mapping] pam ON p.Id = pam.ProductId
                                                JOIN [dbo].[ProductAttribute] pa ON pam.[ProductAttributeId] = pa.Id
                                                JOIN [dbo].[ProductAttributeValue] pav ON pam.Id = pav.[ProductAttributeMappingId]
                                                JOIN [dbo].[ProductAttributeCombination] pac ON p.Id = pac.ProductId 
                                                                AND pam.[Id] = CAST(pac.[AttributesXml] AS XML).value('(/Attributes/ProductAttribute/@ID)[1]', 'int')
                                                                AND pav.[Id] = CAST(pac.[AttributesXml] AS XML).value('(/Attributes/ProductAttribute/ProductAttributeValue/Value)[1]', 'int')

  ORDER BY p.SKU, pa.[Name], pam.[TextPrompt], pav.[Name]