homewetbar Posted August 10, 2005 Share Posted August 10, 2005 Please post your questions regarding the Low Stock Report v2.0 contribution here. WHATS NEW IN 2.0? ------------------------- NEW FEATURES: * It now shows you your sales on this item in the last 30 days so you can really know if this item is really selling or not * Estimated days stock, this is based off of the last 30 days of your sales, if you have 0 in stock it will display NA in red, if you have 15 days or less it will display the days in red, if you have no sales but stock it will display +30 Days. Pretty niffty ehh? Much more accurate. NOTE: It will only display items that need restocked that are at or below your restock level set in admin -> Stock, regardless of how many estimated days stock you have. This is because it has to execute a DB query for every item it estimates days of stock for so this is a way of limiting the queries to the most relevant items (item below restock levels). Maybe someone can further optimize my queries if they know how... This should be a hugely helpful addition to your store, thanks to all the other authors who wrote the previous versions and the people who wrote the stats products purchased contrib, these two contrib are which this code is based on. Regards, -Keith W. Quote Most Valuable OsCommerce Contributions: Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294 FedEx Automated Labels -- Contribution 2244 RMA Returns system -- Contribution 1136 Sort Products By Dropdown -- Contribution 4312 Ultimate SEO URLs -- Contribution 2823 Credit Class & Gift Voucher -- Contribution 282 Cross-Sell -- Contribution 5347 Link to comment Share on other sites More sharing options...
willross Posted August 10, 2005 Share Posted August 10, 2005 1054 - Unknown column 'p.last_one' in 'field list' select p.products_id, p.last_one, p.products_quantity, pd.products_name, p.products_model from products p, products_description pd where p.products_id = pd.products_id and pd.language_id = '1' and p.products_quantity <= 5 group by pd.products_id order by pd.products_name ASC limit -20, 20 [TEP STOP] Any ideas? Quote · willross ·········· Link to comment Share on other sites More sharing options...
Irin Posted August 10, 2005 Share Posted August 10, 2005 1054 - Unknown column 'p.last_one' in 'field list' select p.products_id, p.last_one, p.products_quantity, pd.products_name, p.products_model from products p, products_description pd where p.products_id = pd.products_id and pd.language_id = '1' and p.products_quantity <= 5 group by pd.products_id order by pd.products_name ASC limit -20, 20 [TEP STOP] Any ideas? <{POST_SNAPBACK}> Hi, I had the same error after installing an update. I just added a last_one to TABLE Products. Now I get another error: Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Can anybody suggest a correct way to fix an errors? Thanks, Irina. Quote Link to comment Share on other sites More sharing options...
boxtel Posted August 10, 2005 Share Posted August 10, 2005 1054 - Unknown column 'p.last_one' in 'field list' select p.products_id, p.last_one, p.products_quantity, pd.products_name, p.products_model from products p, products_description pd where p.products_id = pd.products_id and pd.language_id = '1' and p.products_quantity <= 5 group by pd.products_id order by pd.products_name ASC limit -20, 20 [TEP STOP] Any ideas? <{POST_SNAPBACK}> delete that field from the query, it is not used anywhere as far as I can tell Quote Treasurer MFC Link to comment Share on other sites More sharing options...
homewetbar Posted August 10, 2005 Author Share Posted August 10, 2005 delete that field from the query, it is not used anywhere as far as I can tell <{POST_SNAPBACK}> It's not used, that is a field on my site which I accidently left in the query. :-" Proper query should be: $products_query_raw = "select p.products_id, p.products_quantity, pd.products_name, p.products_model from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = pd.products_id and pd.language_id = '" . $languages_id. "' and p.products_quantity <= " . STOCK_REORDER_LEVEL . " group by pd.products_id order by $db_orderby $sorted"; I've posted the update to the contribution page. So what do you all think of the new features? Quote Most Valuable OsCommerce Contributions: Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294 FedEx Automated Labels -- Contribution 2244 RMA Returns system -- Contribution 1136 Sort Products By Dropdown -- Contribution 4312 Ultimate SEO URLs -- Contribution 2823 Credit Class & Gift Voucher -- Contribution 282 Cross-Sell -- Contribution 5347 Link to comment Share on other sites More sharing options...
homewetbar Posted August 10, 2005 Author Share Posted August 10, 2005 (edited) Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Warning: Division by zero in .............\osCommerce\admin\stats_low_stock.php on line 121 Can anybody suggest a correct way to fix an errors? Thanks, Irina. <{POST_SNAPBACK}> When you delete p.last_one from the query do you still get this error? Edited August 10, 2005 by homewetbar Quote Most Valuable OsCommerce Contributions: Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294 FedEx Automated Labels -- Contribution 2244 RMA Returns system -- Contribution 1136 Sort Products By Dropdown -- Contribution 4312 Ultimate SEO URLs -- Contribution 2823 Credit Class & Gift Voucher -- Contribution 282 Cross-Sell -- Contribution 5347 Link to comment Share on other sites More sharing options...
Irin Posted August 10, 2005 Share Posted August 10, 2005 When you delete p.last_one from the query do you still get this error? <{POST_SNAPBACK}> Now I get: Warning: Division by zero in ......\osCommerce\admin\stats_low_stock.php on line 143 Warning: Division by zero in ......\osCommerce\admin\stats_low_stock.php on line 143 Warning: Division by zero in ......\osCommerce\admin\stats_low_stock.php on line 143 Warning: Division by zero in ......\osCommerce\admin\stats_low_stock.php on line 143 Warning: Division by zero in ......\osCommerce\admin\stats_low_stock.php on line 143 Warning: Division by zero in ......\osCommerce\admin\stats_low_stock.php on line 143 Warning: Division by zero in ......\osCommerce\admin\stats_low_stock.php on line 143 Warning: Division by zero in ......\osCommerce\admin\stats_low_stock.php on line 143 Even if I upload your new v2.0a, I still get this error. Regards, irina. Quote Link to comment Share on other sites More sharing options...
Guest Posted August 10, 2005 Share Posted August 10, 2005 Hi irina, I had the same problem and updated the stats_low_stock.php file with the following from line 137 until 145 <-- ORIGINAL --> // Calculating days stock if ($products['products_quantity'] > 0) { $StockOnHand = $products['products_quantity']; $SalesPerDay = $productSold['quantitysum'] / 30; round ($SalesPerDay, 2); $daysSupply = 0; $daysSupply = $StockOnHand / $SalesPerDay; <-- CHANGED --> // Calculating days stock if ($products['products_quantity'] > 0) { $StockOnHand = $products['products_quantity']; $SalesPerDay = $productSold['quantitysum'] / 30; round ($SalesPerDay, 2); $daysSupply = 0; if ($SalesPerDay > 0) { $daysSupply = $StockOnHand / $SalesPerDay; } Quote Link to comment Share on other sites More sharing options...
Irin Posted August 10, 2005 Share Posted August 10, 2005 Hi irina, I had the same problem and updated the stats_low_stock.php file with the following from line 137 until 145 <-- ORIGINAL --> // Calculating days stock if ($products['products_quantity'] > 0) { $StockOnHand = $products['products_quantity']; $SalesPerDay = $productSold['quantitysum'] / 30; round ($SalesPerDay, 2); $daysSupply = 0; $daysSupply = $StockOnHand / $SalesPerDay; <-- CHANGED --> // Calculating days stock if ($products['products_quantity'] > 0) { $StockOnHand = $products['products_quantity']; $SalesPerDay = $productSold['quantitysum'] / 30; round ($SalesPerDay, 2); $daysSupply = 0; if ($SalesPerDay > 0) { $daysSupply = $StockOnHand / $SalesPerDay; } <{POST_SNAPBACK}> If I change it as you said, I get another error: Parse error: parse error in ........\osCommerce\admin\stats_low_stock.php on line 200 So, your fix doesn't work for me. Regards, Irina. Quote Link to comment Share on other sites More sharing options...
Guest Posted August 10, 2005 Share Posted August 10, 2005 (edited) strange .. I've copy pasted from // Sold in Last x Months Query (approx 133) to ?> after $dayssupply=blahblah on line 159.. it might be a missing } or something.. I also just moved the round($daysSupply); into the if ($SalesPerDay > 0) statement too. // Sold in Last x Months Query $productSold_query = tep_db_query("select sum(op.products_quantity) as quantitysum FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op WHERE o.date_purchased BETWEEN '" . $start_date . "' AND '" . $end_date . " 23:59:59' AND o.orders_id = op.orders_id AND op.products_id = $products_id GROUP BY op.products_id ORDER BY quantitysum DESC, op.products_id"); $productSold = tep_db_fetch_array($productSold_query); // Calculating days stock if ($products['products_quantity'] > 0) { $StockOnHand = $products['products_quantity']; $SalesPerDay = $productSold['quantitysum'] / 30; round ($SalesPerDay, 2); $daysSupply = 0; if ($SalesPerDay > 0) { $daysSupply = $StockOnHand / $SalesPerDay; round($daysSupply); } if ($daysSupply <= '15') { $daysSupply = '<font color=red>' . round($daysSupply) . ' ' . DAYS . '</font>'; } else { $daysSupply .= ' ' . DAYS; } if ($SalesPerDay == 0) { $daysSupply = '+30 '. DAYS; } } else { $daysSupply = '<font color=red>NA</font>'; } ?> Edited August 10, 2005 by Huey_AU Quote Link to comment Share on other sites More sharing options...
Irin Posted August 10, 2005 Share Posted August 10, 2005 strange .. I've copy pasted from // Sold in Last x Months Query (approx 133) to ?> after $dayssupply=blahblah on line 159.. it might be a missing } or something.. I also just moved the round($daysSupply); into the if ($SalesPerDay > 0) statement too. // Sold in Last x Months Query ?$productSold_query = tep_db_query("select sum(op.products_quantity) as quantitysum FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op WHERE o.date_purchased BETWEEN '" . $start_date . "' AND '" . $end_date . " 23:59:59' AND o.orders_id = op.orders_id AND op.products_id = $products_id GROUP BY op.products_id ORDER BY quantitysum DESC, op.products_id"); ?$productSold = tep_db_fetch_array($productSold_query); // Calculating days stock if ($products['products_quantity'] > 0) { $StockOnHand = $products['products_quantity']; $SalesPerDay = $productSold['quantitysum'] / 30; round ($SalesPerDay, 2); $daysSupply = 0; if ($SalesPerDay > 0) { $daysSupply = $StockOnHand / $SalesPerDay; round($daysSupply); } if ($daysSupply <= '15') { ? $daysSupply = '<font color=red>' . round($daysSupply) . ' ' . DAYS . '</font>'; } else { ? $daysSupply .= ' ' . DAYS; } if ($SalesPerDay == 0) { ? $daysSupply = '+30 '. DAYS; } } else { $daysSupply = '<font color=red>NA</font>'; } ?> <{POST_SNAPBACK}> Everything is working now. Thanks a lot. It really missed some }. Great contrib, added some useful fields. Thanks again, Irina. Quote Link to comment Share on other sites More sharing options...
homewetbar Posted August 10, 2005 Author Share Posted August 10, 2005 (edited) If I change it as you said, I get another error: Parse error: parse error in ........\osCommerce\admin\stats_low_stock.php on line 200 So, your fix doesn't work for me. Regards, Irina. <{POST_SNAPBACK}> I'm surprised his fix did not work maybe your error is being caused by is $productSold['quantitysum'] = 0 then it would be 0/30. Try this I think it should fix it if that is the problem. CHANGE: // Calculating days stock if ($products['products_quantity'] > 0) { $StockOnHand = $products['products_quantity']; $SalesPerDay = $productSold['quantitysum'] / 30; round ($SalesPerDay, 2); $daysSupply = 0; $daysSupply = $StockOnHand / $SalesPerDay; TO: // Calculating days stock if ($products['products_quantity'] > 0) { $StockOnHand = $products['products_quantity']; if ($productSold['quantitysum'] > 0) { ?$SalesPerDay = $productSold['quantitysum'] / 30; ?round ($SalesPerDay, 2); ?$daysSupply = 0; ?$daysSupply = $StockOnHand / $SalesPerDay; } else { $daysSupply = '+30 '. DAYS; } Edited August 10, 2005 by homewetbar Quote Most Valuable OsCommerce Contributions: Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294 FedEx Automated Labels -- Contribution 2244 RMA Returns system -- Contribution 1136 Sort Products By Dropdown -- Contribution 4312 Ultimate SEO URLs -- Contribution 2823 Credit Class & Gift Voucher -- Contribution 282 Cross-Sell -- Contribution 5347 Link to comment Share on other sites More sharing options...
Irin Posted August 10, 2005 Share Posted August 10, 2005 I'm surprised his fix did not work maybe your error is being caused by is $productSold['quantitysum'] = 0 then it would be 0/30. Try this I think it should fix it if that is the problem. CHANGE: // Calculating days stock if ($products['products_quantity'] > 0) { $StockOnHand = $products['products_quantity']; $SalesPerDay = $productSold['quantitysum'] / 30; round ($SalesPerDay, 2); $daysSupply = 0; $daysSupply = $StockOnHand / $SalesPerDay; TO: // Calculating days stock SalesPerDay = ''; if ($products['products_quantity'] > 0) { $StockOnHand = $products['products_quantity']; if ($productSold['quantitysum'] > 0) { ?$SalesPerDay = $productSold['quantitysum'] / 30; ?round ($SalesPerDay, 2); ?$daysSupply = 0; ?$daysSupply = $StockOnHand / $SalesPerDay; } else { $daysSupply = '+30 '. DAYS; } <{POST_SNAPBACK}> Thanks a lot, but the problem is fixed already. :thumbsup: It missed some } in stats_low_stock.php. Regards, Irina. Quote Link to comment Share on other sites More sharing options...
Guest Posted August 10, 2005 Share Posted August 10, 2005 (edited) Good to hear :) Edited August 10, 2005 by Huey_AU Quote Link to comment Share on other sites More sharing options...
Guest Posted August 11, 2005 Share Posted August 11, 2005 Hi, Will this contribution show the stock level of different color attributes of a product or just the stock of the product? Quote Link to comment Share on other sites More sharing options...
homewetbar Posted August 11, 2005 Author Share Posted August 11, 2005 Hi, Will this contribution show the stock level of different color attributes of a product or just the stock of the product? <{POST_SNAPBACK}> Just stock of the product Quote Most Valuable OsCommerce Contributions: Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294 FedEx Automated Labels -- Contribution 2244 RMA Returns system -- Contribution 1136 Sort Products By Dropdown -- Contribution 4312 Ultimate SEO URLs -- Contribution 2823 Credit Class & Gift Voucher -- Contribution 282 Cross-Sell -- Contribution 5347 Link to comment Share on other sites More sharing options...
Guest Posted August 15, 2005 Share Posted August 15, 2005 I have the latest contribution for this and get this error: 1064 - You have an error in your SQL syntax near '' at line 1 select parent_id from categories where categories_id = [TEP STOP] How do you fix this? Quote Link to comment Share on other sites More sharing options...
homewetbar Posted August 15, 2005 Author Share Posted August 15, 2005 I have the latest contribution for this and get this error:1064 - You have an error in your SQL syntax near '' at line 1 select parent_id from categories where categories_id = [TEP STOP] How do you fix this? <{POST_SNAPBACK}> You didn't mention which file this was.... I would suggest you get an editing tool like JEdit and install the php plugin it finds these little errors for you. Probably a missing " or ' or ;. Quote Most Valuable OsCommerce Contributions: Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294 FedEx Automated Labels -- Contribution 2244 RMA Returns system -- Contribution 1136 Sort Products By Dropdown -- Contribution 4312 Ultimate SEO URLs -- Contribution 2823 Credit Class & Gift Voucher -- Contribution 282 Cross-Sell -- Contribution 5347 Link to comment Share on other sites More sharing options...
♥radders Posted August 15, 2005 Share Posted August 15, 2005 Yes that error goes back a long way. I used to get that with the second release of the low stock report so I just went back and installed the original version. Quote Link to comment Share on other sites More sharing options...
Guest Posted August 15, 2005 Share Posted August 15, 2005 (edited) It didn't tell me which file it was except the page url it was on in the store admin: Low Stock Report contribution: http://www.theliquidleaf.com/admin/stats_low_stock.php Inventory Report: http://www.theliquidleaf.com/admin/inventory_report.php They both give the very same error. I will try the original contributions on these. ======================== update I found this problem. I had removed a product_to_category listing in the db and left the product listed thus the error. Edited August 15, 2005 by modom Quote Link to comment Share on other sites More sharing options...
kukkuu Posted August 16, 2005 Share Posted August 16, 2005 Please post your questions regarding the Low Stock Report v2.0 contribution here. NOTE: It will only display items that need restocked that are at or below your restock level set in admin -> Stock, regardless of how many estimated days stock you have. Hi guys, this is a little bit problematic for me. I have same boxes with the different sizes and taste. So if I'm checking the low stock report, it doesn't show all the combinations. Actually it shows only one, which I can re-stocked (so customer can order the box, which I don't have exact size or taste). Is there any way to see all the combinations what I have on my store from the low stock report (even if I got those on the stock)? Quote Link to comment Share on other sites More sharing options...
shaytaan Posted August 23, 2005 Share Posted August 23, 2005 I have the latest contribution for this and get this error:1064 - You have an error in your SQL syntax near '' at line 1 select parent_id from categories where categories_id = [TEP STOP] How do you fix this? <{POST_SNAPBACK}> I have the same problem..can anyone fix this please? I don't wanna remove the products_to_categories from the my database PLease help Quote ?,???`???,?? God must love stupid people, he made so many ??,???`???,? Link to comment Share on other sites More sharing options...
iweblogix Posted October 8, 2005 Share Posted October 8, 2005 Receiving the following error when I click on "Low Stock Report" Parse error: parse error, unexpected T_REQUIRE in /www/b/billingsley/htdocs/ocart/catalog/admin/stats_low_stock.php on line 1 Please help thanks, Andy Quote Link to comment Share on other sites More sharing options...
lambro Posted November 8, 2005 Share Posted November 8, 2005 Got this working no problems, superb update to the fact I dont think the stock OCS handles min/max stock levels very well! How ever, one small thing for all you clever ones out there to think of (and help me)! Is there a way to sort the order of display out for the estimated stock. 0 stock levels is fine as you can ascend and descend that, but when you have sorted all those out, the products left with low stock it might be nice to be able to ascend and decend the amount of days that this contributtions states you have until you run out! Quote Link to comment Share on other sites More sharing options...
Zuncan Posted January 7, 2006 Share Posted January 7, 2006 (edited) Wrong calculation! This is just the thing I need. Greatful for this contribution. I have installed it on my store and at first look it seem like its working, but it calculates the remaining days left on a product - totally wrong! For example: Name: Rollerblades Qty avaiable: 6 Model: xxxx 60 days sales: 17 Est. Stock: 116 days Ive installed the latest version of low stock report (v2.01) Could it be something wrong in stats_low_stock.php? Please help me out. I really need this one to work /Zuncan Edited January 7, 2006 by Zuncan Quote So what?! Who care in a hundred years anyway? 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.