Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Low Stock Report v2.0


homewetbar

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

Link to comment
Share on other sites

  • Replies 80
  • Created
  • Last Reply

Top Posters In This Topic

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

··········

Link to comment
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.

Link to comment
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

Link to comment
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

Link to comment
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

Link to comment
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.

Link to comment
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;

}

Link to comment
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.

Link to comment
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
Link to comment
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.

Link to comment
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

Link to comment
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.

Link to comment
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

Link to comment
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?

Link to comment
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

Link to comment
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
Link to comment
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)?

Link to comment
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 ??,???`???,?

Link to comment
Share on other sites

  • 1 month later...
  • 5 weeks later...

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!

Link to comment
Share on other sites

  • 1 month later...

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?

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
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
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...