I would really like an extension that can show the total value of all the items that are in stock in my shop. The total value must be of the price that I bought my items, not what I'm selling it for.
I guess I need some kind of inventory system, where I must enter the cost price for all my items. Can someone recommend a free or cheap extention, that can handle this ?
- Kim -
I'm not aware of any extension that does this - however it should be straight-forward enough to get a MySQL query together that will show you this... I've done a basic one below - this query basically just adds up all the 'price' fields in your store.
Obviously you can substitute price for MSRP or another price attribute if you have it stored elsewhere. If its not a price attribute; you'd have to change _entity_decimal to the correct type for that attribute.. so either entity_int or enttiy_varchar likely.
SELECT sum(`value`) FROM catalog_product_entity_decimal WHERE attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'price')
Thanks, but I'm not sure that this is the solution I need. As I can see, your SQL calculates the total selling price of my items in stock.
What I need, is to know the total value of all my stocks, at the price that I have bought them for.
English is not my native language, so sometimes I miss the point, and sometimes It can be hard to explain what I mean
Do you have an attribute against your products that stores the item value? If not then obviously you will need to put this information in before any reporting can be done on it.
If you do have it in an attribute the SQL can be changed to work from that attribute - however the SQL needed will differ depending on what type you create that attribute as ... e.g. text input, dropdown, price field, multiselect.
No, I don't have an attribute to store the item value. But creating one myself, is not the solution.
There will be a problem if I buy the same item, but at a different prices eact time.
Let's say I buy 10 blue t-shirt for $10 each. Then my stock value will be $100. If I later buy 10 more of the exact same blue t-shirts (same SKU), but now they cost $15. Then my stock value will be 10 x $10 + 10 x $15 = $250. That means each t-shirt is worth 250/20 = $12.5 If I then sell one t-shirt, my stock value will be $250 - $12.5 = $237.5
Is it really true, that there is no extention that can do this calculation ?
- Kim -