Low Stock Report v2.0

homewetbar · 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.

willross · 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?

Irin · 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.

boxtel · 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

homewetbar · 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?

homewetbar · August 10, 2005

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

Irin · 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.

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;

}

Irin · 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.

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>';
}
?>

Edited August 10, 2005 by Huey_AU

Irin · 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.

homewetbar · August 10, 2005

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

Irin · 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.

August 10, 2005

Good to hear :)

Edited August 10, 2005 by Huey_AU

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?

homewetbar · 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

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?

homewetbar · 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 ;.

radders · 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.

August 15, 2005

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

kukkuu · 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)?

shaytaan · 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

iweblogix · 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

lambro · 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!

Zuncan · January 7, 2006

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

Low Stock Report v2.0

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation