81 replies to this topic

[homewetbar]

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]

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]

willross, on Aug 9 2005, 09:10 PM, said:

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]

willross, on Aug 10 2005, 10:10 AM, said:

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]

boxtel, on Aug 10 2005, 04:10 AM, said:

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]

Quote

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 by homewetbar, 10 August 2005, 14:38.

[Irin]

homewetbar, on Aug 10 2005, 09:37 AM, said:

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.

[Huey_AU]

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]

Huey_AU, on Aug 10 2005, 11:22 AM, said:

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.

[Huey_AU]

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, 10 August 2005, 17:40.

[Irin]

Huey_AU, on Aug 10 2005, 12:38 PM, said:

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]

Irin, on Aug 10 2005, 05:18 PM, said:

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 by homewetbar, 10 August 2005, 18:26.

[Irin]

homewetbar, on Aug 10 2005, 01:23 PM, said:

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. It missed some } in stats_low_stock.php.

Regards,
Irina.

[Huey_AU]

Good to hear

Edited by Huey_AU, 10 August 2005, 18:36.

[Melinda Odom]

Hi,

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

[homewetbar]

modom, on Aug 11 2005, 08:25 PM, said:

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

[Melinda Odom]

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]

modom, on Aug 15 2005, 02:38 AM, said:

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]

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.

[Melinda Odom]

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, 15 August 2005, 16:37.