Pricing Report

[Lizzie]

Hi guys,

I'm looking for a way to generate a simple report in uStore for one store, listing all product names and prices. Is anyone able to help me with this?

Thanks,

Lizzie

[BrianS]

Here's a little SQL to get you going.

Someone with more knowledge on how the Pricing Step values are stored in the Database will need to comment on how to gather the Product Pricing details.

select sc.Name As [Store]

, pc.name as [Product Name]

from Product P

join Product_Culture PC

on p.ProductID = pc.ProductID

join Store_Culture SC

on p.StoreID = sc.StoreID

where p.StoreID = 2

[Lizzie]

Thanks for this Brian :) can anyone give me some info on how to retrieve the Product Pricing details?

[couch]

Price Step names are in the PricingModelPriceStep_Culture table.

Price Step quantities are in the PricingModelPriceStep table.

Prices for each step are in PricingModelPrice table.

Extending on Brian's SQL, this might help you get closer to what you want?

SELECT SC.Name AS Store, PC.Name AS [Product Name], PricingModelPriceStep_Culture.Name, PricingModelPriceStep.FromValue, PricingModelPrice.Price

FROM Product AS P INNER JOIN Product_Culture AS PC ON P.ProductID = PC.ProductID INNER JOIN

Store_Culture AS SC ON P.StoreID = SC.StoreID INNER JOIN PricingModelPriceStep ON P.ProductID = PricingModelPriceStep.ProductId INNER JOIN

PricingModelPriceStep_Culture ON PricingModelPriceStep.PriceStepId = PricingModelPriceStep_Culture.PriceStepID INNER JOIN

 PricingModelPrice ON P.ProductID = PricingModelPrice.ProductId AND PricingModelPriceStep.PriceStepId = PricingModelPrice.PricingStepId

WHERE P.StoreID = 2

[BrianS]

Yowsers - Localization makes things complicated! 

Couch,

Your query results in 65 results when I run in my DB. Checking uStore Website, I have 22 Products configured in StoreID = 2.

Tweaked the Query to limit to a Specific Culture (CultureID = 1) which may or not be the desired result, I suppose the Product Pricing could be different for each Culture.

SELECT SC.Name AS Store

, PC.Name AS [Product Name]

, P.ProductID

, PricingModelPriceStep_Culture.Name

, PricingModelPriceStep.FromValue

, PricingModelPrice.Price

FROM Product AS P 

JOIN Product_Culture AS PC

ON P.ProductID = PC.ProductID

JOIN Store_Culture AS SC

ON P.StoreID = SC.StoreID

JOIN PricingModelPriceStep

ON P.ProductID = PricingModelPriceStep.ProductId

JOIN PricingModelPriceStep_Culture

ON PricingModelPriceStep.PriceStepId = PricingModelPriceStep_Culture.PriceStepID

JOIN PricingModelPrice

ON P.ProductID = PricingModelPrice.ProductId AND PricingModelPriceStep.PriceStepId = PricingModelPrice.PricingStepId

WHERE P.StoreID = 2 and PC.CultureID = 1 and SC.CultureID = 1 and PricingModelPriceStep_Culture.CultureID = 1

GROUP by PricingModelPriceStep.PriceStepId, SC.Name, PC.Name, P.ProductID, PricingModelPriceStep_Culture.Name, PricingModelPriceStep.FromValue, PricingModelPrice.Price.

I'm still not 100% convinced this is correct since I have 22 Products in my Store. But I get 20 results when I run this updated query.

~B

[BrianS]

Okay,

So eating my words a little bit. Couch, your 65 Records appear to be correct.

I've added the additional tables that put the Price Steps into context.

Added:

PropertyOnly_Culture and FieldOption_Culture tables to show the Pricing Steps and the Options that affect the price.

SELECT SC.Name AS Store

, PC.Name AS [Product Name]

, PricingModelPriceStep_Culture.Name

, p.ProductID

, PropertyOnly_Culture.FriendlyName as [Price Step Description]

, FieldOption_Culture.Text as [Price Option]

, PricingModelPriceStep.FromValue

, PricingModelPrice.Price

FROM Product AS P 

JOIN Product_Culture AS PC

ON P.ProductID = PC.ProductID

JOIN Store_Culture AS SC

ON P.StoreID = SC.StoreID

JOIN PricingModelPriceStep

ON P.ProductID = PricingModelPriceStep.ProductId

JOIN PricingModelPriceStep_Culture

ON PricingModelPriceStep.PriceStepId = PricingModelPriceStep_Culture.PriceStepID

JOIN PricingModelPrice

ON P.ProductID = PricingModelPrice.ProductId AND PricingModelPriceStep.PriceStepId = PricingModelPrice.PricingStepId

JOIN PropertyOnly_Culture

on PricingModelPrice.DialId = PropertyOnly_Culture.DialID

JOIN FieldOption_Culture

on PricingModelPrice.FieldOptionId = FieldOption_Culture.FieldOptionID

WHERE P.StoreID = 2 

GROUP by PricingModelPriceStep.PriceStepId

, SC.Name

, PC.Name

, P.ProductID

, PricingModelPriceStep_Culture.Name

, p.ProductID, PropertyOnly_Culture.FriendlyName

, FieldOption_Culture.Text

, PricingModelPriceStep.FromValue

, PricingModelPrice.Price

[couch]

:) once you start playing with the _cultures tables you will find multiple rows returned for each product describing the data in different languages. You can work around that by adding the culture id to the query for the language you are wanting.

[Ashley Drennan]

I found this and it worked great for me, however, a little...too great?

It returned EVERY single item that has ever been entered on the store, including items that have been deleted or turned offline.

Is there a way to edit the sql to only output items that are currently on the store/only turned on?

[couch]

In the line where it says: WHERE P.StoreID = 2 

Change to: WHERE P.SToreID = 2  and P.StatusID = 1

[Ashley Drennan]

THANK YOU!!!

I do have a quick question - is there a way to change the language of P.StoreID = # to make it return pricing for all the stores rather than 1 store at a time?

[BrianS]

Haven't looked at the Query or Table in a while, but while editing the Query in SQL Server Management Server you'll want to use and IN Operator in the Where Clause.

https://www.w3schools.com/sql/sql_in.asp

Like this:

WHERE P.SToreID in (2,4,6,8)  and P.StatusID = 1

[Ashley Sledge]

THANK YOU!!!!!!!!!!!!!