Jump to content

Total Stock Valuation


Guest ham

Recommended Posts

Hi everyone i am new to this forum so go easy on me please.

I am just learning cubecart (version 3 to be precise) and been fiddling with some of the features it offers.

I was just wondering is there any report feature out there that will figure out a total price of how much all of the products added to the catalogue are worth?

Any suggestions will be greatly appreciated.

Thanks in advance :)

Link to comment
Share on other sites

This could be a very simple SQL query against the database to come up with this figure. However, that would be for the base price of the product. Any mandatory options that add to or subtract from that price won't calculate into it. And the figure will be erroneous if any of the products are digital and can be sold ad infinitum without stock control.

So, for each product, if you have useStockLevel = 1, then sum into the totalValuation the price*stock_level.

You can put the result of that query on the Admin Summary screen.

Let me know if you want help with the coding.

Link to comment
Share on other sites

Hi bsmither thank you for the prompt reply.

I was thinking that it would be a simple SQL query. The items that will be sold are of a physical nature so stock will be counted.

Yes a little help with the coding would be much appreciated.

Link to comment
Share on other sites

In the file /admin/index.php

Find:

// no Products


Add Before:
// Stock Valuation

$query = "SELECT SUM(stock_level * price) as stockVal FROM ".$glob['dbprefix']."CubeCart_inventory WHERE useStockLevel='1'";

$stockVal = $db->select($query);


Find:
<td width="25%" class="tdText"><?php echo number_format($noProducts[0]['noProducts']); ?></td>


Within That, Change
['noProducts']); ?></td>
to
['noProducts']) . "valued at " . priceFormat($stockVal[0]['stockVal']); ?></td>

This gets all the inventory records where you have said to use stock levels, and for every record as such, sums the stock level multiplied by its price.

Note that the number of products is a count of unique products. That number is not the total inventory. For example, you may have three products but your inventory is actually 300 units each. The valuation is based on the 300 count for each distinct product and each product's price.

Let me know if we need to tweak this.

Link to comment
Share on other sites

Guest BEEcycle

In the file /admin/index.php

Find:

// no Products


Add Before:
// Stock Valuation

$query = "SELECT SUM(stock_level * price) as stockVal FROM ".$glob['dbprefix']."CubeCart_inventory WHERE useStockLevel='1'";

$stockVal = $db->select($query);


Find:
<td width="25%" class="tdText"><?php echo number_format($noProducts[0]['noProducts']); ?></td>


Within That, Change
['noProducts']); ?></td>
to
['noProducts']) . "valued at " . priceFormat($stockVal[0]['stockVal']); ?></td>

This gets all the inventory records where you have said to use stock levels, and for every record as such, sums the stock level multiplied by its price.

Note that the number of products is a count of unique products. That number is not the total inventory. For example, you may have three products but your inventory is actually 300 units each. The valuation is based on the 300 count for each distinct product and each product's price.

Let me know if we need to tweak this.

Using your code I got: Number of Products: 28 valued at 0.000000

25 of the listed products are in stock with multiple prices.

Link to comment
Share on other sites

Can you better explain what you mean by "multiple prices"? If this is a mod that results in having an empty or zero value in the inventory price field, thus the product's price comes from some other table or field, we will need to know that.

Access your database directly and look at the _inventory table. Are there values other than zero in the price column? Are there values other than zero in the stock_level column? And, also, is there '1' in the useStockLevel column?

If all that shows good, then we must be using the $stockVal variable wrong. Temporarily, after this line:

$stockVal = $db->select($query);

add this:

echo var_dump($stockVal);

You will see some strange text somewhere on your screen, probably at the top-left.

Link to comment
Share on other sites

Guest BEEcycle

Can you better explain what you mean by "multiple prices"? If this is a mod that results in having an empty or zero value in the inventory price field, thus the product's price comes from some other table or field, we will need to know that.

Access your database directly and look at the _inventory table. Are there values other than zero in the price column? Are there values other than zero in the stock_level column? And, also, is there '1' in the useStockLevel column?

If all that shows good, then we must be using the $stockVal variable wrong. Temporarily, after this line:

$stockVal = $db->select($query);

add this:

echo var_dump($stockVal);

You will see some strange text somewhere on your screen, probably at the top-left.

Sorry, multiple prices meant sme items £197 some cost £100 and some £300 etc

There are 3 items that cost 0.00 which are free downloads. 4 items that have 0 in the use stock field. And 5 items have 0 in the stock level field. Everything else is complete as usual

After adding that line of code I get this: array(1) { [0]=> array(1) { ["stockVal"]=> string(8) "39472.26" } }

Link to comment
Share on other sites

Ok, so we are getting a response: 39472.26.

But this display: "28 valued at 0.000000" tells me that the priceFormat function is giving back something other than what we want. The function is using the $currencyVars array, and I'm guessing it is not what we think it may be.

In fact, at this point in the index.php file, CC does not know about the currencies in use. So,

After:

include_once("includes/header.inc.php");

Add:

// get exchange rates etc

include_once("../includes/currencyVars.inc.php");

You can remove the var_dump statement.

Link to comment
Share on other sites

Guest BEEcycle

Ok, so we are getting a response: 39472.26.

But this display: "28 valued at 0.000000" tells me that the priceFormat function is giving back something other than what we want. The function is using the $currencyVars array, and I'm guessing it is not what we think it may be.

In fact, at this point in the index.php file, CC does not know about the currencies in use. So,

After:

include_once("includes/header.inc.php");

Add:

// get exchange rates etc

include_once("../includes/currencyVars.inc.php");

You can remove the var_dump statement.

Works perfect! Thank you

Link to comment
Share on other sites

Ive implemented the code you suggested and indeed it does work a treat thank you for the help.

I was also wondering, how hard would it be to generate sort of an itemised stock value for individual categories?

Thanks again

Link to comment
Share on other sites

Let's start with the SQL query needed to get what we want:

SELECT `cat_id`, SUM(`stock_level` * `price`) FROM CubeCart_inventory WHERE `useStockLevel` = '1' GROUP BY `cat_id`;

However, since there may be any number of product master categories and the list could get quite lengthy, I believe we should create a separate report in the View Stats section of the admin screens.

So, I will start a conversation over at cubecartforums.org and develop this report.

On the other hand, since CC3 already delivers a list of categories (View Categories page), it will be a simple matter of changing the SQL query for this page and add a table column to display inventory valuations by category.

In the file \admin\categories\index.php, find (near line 122):

$query = "SELECT * FROM ".$glob['dbprefix']."CubeCart_category ORDER BY cat_id, cat_father_id ASC";


Change to:


$query = "SELECT `valuation`, cat.* FROM ".$glob['dbprefix']."CubeCart_category cat

LEFT JOIN (

  SELECT SUM(`stock_level` * `price`) AS `valuation`, cat_id FROM ".$glob['dbprefix']."CubeCart_inventory WHERE `useStockLevel` = '1' GROUP BY cat_id

) AS inv

ON cat.cat_id = inv.cat_id

ORDER BY cat.cat_id, cat_father_id ASC";


A few lines after that is this:


if(!isset($_GET['mode']) && !isset($_GET['edit'])){
and a few more lines after starts a table. We will add a new column after the 'no_products' column.




<td align="center" class="tdTitle"><?php echo "Valuation"; ?></td>


Then, a few more lines down, find this:


<td align="center" valign="middle" nowrap='nowrap'  class="<?php echo $cellColor; ?>"><span class="copyText"><?php echo $results[$i]['noProducts']; ?></span></td>
and after it, add this:
<td align="center" valign="middle" nowrap='nowrap'  class="<?php echo $cellColor; ?>"><span class="copyText"><?php echo $results[$i]['valuation']; ?></span></td>

Be aware that the valuation only counts products that are using stock levels. You may have many more products that aren't using stock levels and those products are included in the "No Products" (Number of Products) column.

Also be aware that a product can have assigned to it more than one category. However, only one specific category will be its "Master Category". The valuations are relevant to the Master Category only. So, the valuation of ProdA will contribute to the Master Category "DooDads", but will not contribute to any alternate categories "Widgets", "RoundTuits", etc.

Link to comment
Share on other sites

Hi bsmither

forgive me if i have done something wrong but i cant seem to get this bit of code working, any ideas?

could it be anything to do with language settings?

thanks ham

Link to comment
Share on other sites

  • 1 year later...

I am beginning to understand why the stock reports are so inaccurate! When I export my website there are only about 170 lines showing and any items that have options show zero stock but considering some of our etched brass trees come in 13 sizes and 5 colours we obviously have way more than 170 products! This of course must be awkward for other people I.E. if I sold Reebok trainers I would only want one line of 'classic trainers' but they would be available in say four colours and maybe 20+ sizes from infants through to mens but the system could only report on the general tab of stock which of course is totally wrong. This also isn't of any use to us as each size option attracts a different price.

Is this any further forward with V5?

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...