Jump to content
Sign in to follow this  
homewetbar

Low Stock Report v2.0

Recommended Posts

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.


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

Share this post


Link to post
Share on other sites

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?


· willross

··········

Share this post


Link to post
Share on other sites
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?

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.

Share this post


Link to post
Share on other sites
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?

 

delete that field from the query, it is not used anywhere as far as I can tell


Treasurer MFC

Share this post


Link to post
Share on other sites
delete that field from the query, it is not used anywhere as far as I can tell

 

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?


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

Share this post


Link to post
Share on other sites
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.

 

When you delete p.last_one from the query do you still get this error?

Edited by homewetbar

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

Share this post


Link to post
Share on other sites
When you delete p.last_one from the query do you still get this error?

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.

Share this post


Link to post
Share on other sites

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;

}

Share this post


Link to post
Share on other sites
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;

}

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.

Share this post


Link to post
Share on other sites

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 by Huey_AU

Share this post


Link to post
Share on other sites
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>';
}
?>

Everything is working now. Thanks a lot. It really missed some }.

Great contrib, added some useful fields.

 

Thanks again,

Irina.

Share this post


Link to post
Share on other sites
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.

 

 

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

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

Share this post


Link to post
Share on other sites
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;
}

Thanks a lot, but the problem is fixed already. :thumbsup: It missed some } in stats_low_stock.php.

 

Regards,

Irina.

Share this post


Link to post
Share on other sites

Hi,

 

Will this contribution show the stock level of different color attributes of a product or just the stock of the product?

Share this post


Link to post
Share on other sites
Hi,

 

Will this contribution show the stock level of different color attributes of a product or just the stock of the product?

 

Just stock of the product


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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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?

 

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


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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by modom

Share this post


Link to post
Share on other sites
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)?

Share this post


Link to post
Share on other sites
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?

 

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


?,???`???,?? God must love stupid people, he made so many ??,???`???,?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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 by Zuncan

So what?! Who care in a hundred years anyway?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×