Guest ham Posted March 2, 2011 Share Posted March 2, 2011 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 :) Quote Link to comment Share on other sites More sharing options...
bsmither Posted March 2, 2011 Share Posted March 2, 2011 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. Quote Link to comment Share on other sites More sharing options...
Guest ham Posted March 8, 2011 Share Posted March 8, 2011 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. Quote Link to comment Share on other sites More sharing options...
bsmither Posted March 8, 2011 Share Posted March 8, 2011 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. Quote Link to comment Share on other sites More sharing options...
Guest BEEcycle Posted March 9, 2011 Share Posted March 9, 2011 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. Quote Link to comment Share on other sites More sharing options...
bsmither Posted March 9, 2011 Share Posted March 9, 2011 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. Quote Link to comment Share on other sites More sharing options...
Guest BEEcycle Posted March 9, 2011 Share Posted March 9, 2011 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" } } Quote Link to comment Share on other sites More sharing options...
bsmither Posted March 9, 2011 Share Posted March 9, 2011 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. Quote Link to comment Share on other sites More sharing options...
Guest BEEcycle Posted March 11, 2011 Share Posted March 11, 2011 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 Quote Link to comment Share on other sites More sharing options...
Guest ham Posted March 15, 2011 Share Posted March 15, 2011 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 Quote Link to comment Share on other sites More sharing options...
bsmither Posted March 17, 2011 Share Posted March 17, 2011 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. Quote Link to comment Share on other sites More sharing options...
Guest ham Posted March 21, 2011 Share Posted March 21, 2011 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 Quote Link to comment Share on other sites More sharing options...
bsmither Posted March 21, 2011 Share Posted March 21, 2011 Email me your copy of this file: \admin\categories\index.php Quote Link to comment Share on other sites More sharing options...
bsmither Posted March 21, 2011 Share Posted March 21, 2011 If you have more than one store, did you tweak the right one? Quote Link to comment Share on other sites More sharing options...
[email protected]. Posted March 15, 2013 Share Posted March 15, 2013 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? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.