Stocktaking Cost 0.3 problem.

[AndreD]

I have a working staocktaking cost contribution installed.

The most important output, is the sum.

The problem is, that it sums each category, and one product may be inside 2 or even three categories. - so the value of that product is summed 2 or 3 times.

 

I'd like a fix that ignores categories, and just lists products - or at least does not add a product more than once to the total.

[steve_s]

I have a working staocktaking cost contribution installed.

The most important output, is the sum.

The problem is, that it sums each category, and one product may be inside 2 or even three categories. - so the value of that product is summed 2 or 3 times.

 

I'd like a fix that ignores categories, and just lists products - or at least does not add a product more than once to the total.

Hi

 

If you give us the contribution name or link to it, in add ons, others might be able to help you out

 

Steve

[AndreD]

The name was both in the subject and the post.

but sure; here's the link http://www.oscommerce.com/community/contributions,3161/page,6

:)

For somebody that knows PHP well it's surely a minor fix - to remove the treatment of categories, and just process all products in stock.

 

Thanks for looking at this case.

[steve_s]

The name was both in the subject and the post.

but sure; here's the link http://www.oscommerce.com/community/contributions,3161/page,6

:)

For somebody that knows PHP well it's surely a minor fix - to remove the treatment of categories, and just process all products in stock.

 

Thanks for looking at this case.

Hi,

 

It looks to me all you need is a distinct in the sql statement,

 

try admin/stocktaking_cost.php

find line that begins

 $products_query = tep_db_query("SELECT * FROM " .TABLE_PRODUCTS." p

change that to

 $products_query = tep_db_query("SELECT distinct p.products_id, p.products_quantity, p.products_cost, p.products_price,  FROM " .TABLE_PRODUCTS." p

 

Steve

Edited December 20, 2009 by steve_s

[AndreD]

Thank you, I got an error:

 

Array1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM products p LEFT JOIN products_description pd ON p.products_id = pd.prod' at line 1

SELECT distinct p.products_id, p.products_quantity, p.products_cost, p.products_price, FROM products p LEFT JOIN products_description pd ON p.products_id = pd.products_id LEFT JOIN specials sp ON p.products_id = sp.products_id LEFT JOIN products_to_categories p2c ON p.products_id = p2c.products_id WHERE p.products_status = 1 AND pd.language_id = FLOOR(4) AND p2c.categories_id not in (65,66) ORDER BY p2c.categories_id, pd.products_name

 

 

Old query was (complete)

$products_query = tep_db_query("SELECT * FROM " .TABLE_PRODUCTS." p
	  LEFT JOIN products_description pd ON p.products_id = pd.products_id 
	  LEFT JOIN specials sp ON p.products_id = sp.products_id 
	  LEFT JOIN products_to_categories p2c ON p.products_id = p2c.products_id 
	  WHERE p.products_status = 1 
	  AND pd.language_id = FLOOR($languages_id)
	  AND p2c.categories_id not in (" . implode(",", $exc_cat_id) . ")
	  ORDER BY p2c.categories_id, pd.products_name");

 

The one that resulted in error;

$products_query = tep_db_query("SELECT distinct p.products_id, p.products_quantity, p.products_cost, p.products_price,  FROM " .TABLE_PRODUCTS." p
	  LEFT JOIN products_description pd ON p.products_id = pd.products_id 
	  LEFT JOIN specials sp ON p.products_id = sp.products_id 
	  LEFT JOIN products_to_categories p2c ON p.products_id = p2c.products_id 
	  WHERE p.products_status = 1 
	  AND pd.language_id = FLOOR($languages_id)
	  AND p2c.categories_id not in (" . implode(",", $exc_cat_id) . ")
	  ORDER BY p2c.categories_id, pd.products_name");

 

Thank you

[steve_s]

Thank you, I got an error:

 

Array1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM products p LEFT JOIN products_description pd ON p.products_id = pd.prod' at line 1

SELECT distinct p.products_id, p.products_quantity, p.products_cost, p.products_price, FROM products p LEFT JOIN products_description pd ON p.products_id = pd.products_id LEFT JOIN specials sp ON p.products_id = sp.products_id LEFT JOIN products_to_categories p2c ON p.products_id = p2c.products_id WHERE p.products_status = 1 AND pd.language_id = FLOOR(4) AND p2c.categories_id not in (65,66) ORDER BY p2c.categories_id, pd.products_name

 

 

Old query was (complete)

$products_query = tep_db_query("SELECT * FROM " .TABLE_PRODUCTS." p
	  LEFT JOIN products_description pd ON p.products_id = pd.products_id 
	  LEFT JOIN specials sp ON p.products_id = sp.products_id 
	  LEFT JOIN products_to_categories p2c ON p.products_id = p2c.products_id 
	  WHERE p.products_status = 1 
	  AND pd.language_id = FLOOR($languages_id)
	  AND p2c.categories_id not in (" . implode(",", $exc_cat_id) . ")
	  ORDER BY p2c.categories_id, pd.products_name");

 

The one that resulted in error;

$products_query = tep_db_query("SELECT distinct p.products_id, p.products_quantity, p.products_cost, p.products_price,  FROM " .TABLE_PRODUCTS." p
	  LEFT JOIN products_description pd ON p.products_id = pd.products_id 
	  LEFT JOIN specials sp ON p.products_id = sp.products_id 
	  LEFT JOIN products_to_categories p2c ON p.products_id = p2c.products_id 
	  WHERE p.products_status = 1 
	  AND pd.language_id = FLOOR($languages_id)
	  AND p2c.categories_id not in (" . implode(",", $exc_cat_id) . ")
	  ORDER BY p2c.categories_id, pd.products_name");

 

Thank you

found the error

replace

 $products_query = tep_db_query("SELECT distinct p.products_id, p.products_quantity, p.products_cost, p.products_price,  FROM " .TABLE_PRODUCTS." p
                 LEFT JOIN products_description pd ON p.products_id = pd.products_id 
                 LEFT JOIN specials sp ON p.products_id = sp.products_id 
                 LEFT JOIN products_to_categories p2c ON p.products_id = p2c.products_id 
                 WHERE p.products_status = 1 
                 AND pd.language_id = FLOOR($languages_id)
                 AND p2c.categories_id not in (" . implode(",", $exc_cat_id) . ")
                 ORDER BY p2c.categories_id, pd.products_name");

 

with

 $products_query = tep_db_query("SELECT distinct p.products_id, p.products_quantity, p.products_cost, p.products_price  FROM " .TABLE_PRODUCTS." p
                 LEFT JOIN products_description pd ON p.products_id = pd.products_id 
                 LEFT JOIN specials sp ON p.products_id = sp.products_id 
                 LEFT JOIN products_to_categories p2c ON p.products_id = p2c.products_id 
                 WHERE p.products_status = 1 
                 AND pd.language_id = FLOOR($languages_id)
                 AND p2c.categories_id not in (" . implode(",", $exc_cat_id) . ")
                 ORDER BY p2c.categories_id, pd.products_name");

 

it was the comma after p,products_price that was the problem

 

Steve

[AndreD]

actually, I suspected the comma too, and tried to remove it, I got another error, so I did not mention it as I thought it was wrong.

 

So here's the error I get without the comma:

Array1054 - Unknown column 'p.products_cost' in 'field list'

SELECT distinct p.products_id, p.products_quantity, p.products_cost, p.products_price FROM products p LEFT JOIN products_description pd ON p.products_id = pd.products_id LEFT JOIN specials sp ON p.products_id = sp.products_id LEFT JOIN products_to_categories p2c ON p.products_id = p2c.products_id WHERE p.products_status = 1 AND pd.language_id = FLOOR(4) AND p2c.categories_id not in (65,66) ORDER BY p2c.categories_id, pd.products_name

[steve_s]

actually, I suspected the comma too, and tried to remove it, I got another error, so I did not mention it as I thought it was wrong.

 

So here's the error I get without the comma:

Array1054 - Unknown column 'p.products_cost' in 'field list'

SELECT distinct p.products_id, p.products_quantity, p.products_cost, p.products_price FROM products p LEFT JOIN products_description pd ON p.products_id = pd.products_id LEFT JOIN specials sp ON p.products_id = sp.products_id LEFT JOIN products_to_categories p2c ON p.products_id = p2c.products_id WHERE p.products_status = 1 AND pd.language_id = FLOOR(4) AND p2c.categories_id not in (65,66) ORDER BY p2c.categories_id, pd.products_name

Hi

 

ok replace

 $products_query = tep_db_query("SELECT distinct p.products_id, p.products_quantity, p.products_cost, p.products_price  FROM " .TABLE_PRODUCTS." p
                 LEFT JOIN products_description pd ON p.products_id = pd.products_id 
                 LEFT JOIN specials sp ON p.products_id = sp.products_id 
                 LEFT JOIN products_to_categories p2c ON p.products_id = p2c.products_id 
                 WHERE p.products_status = 1 
                 AND pd.language_id = FLOOR($languages_id)
                 AND p2c.categories_id not in (" . implode(",", $exc_cat_id) . ")
                 ORDER BY p2c.categories_id, pd.products_name");

 

with

 $products_query = tep_db_query("SELECT distinct p.products_id, p.products_quantity, p.products_price_cost, p.products_price  FROM " .TABLE_PRODUCTS." p
                 LEFT JOIN products_description pd ON p.products_id = pd.products_id 
                 LEFT JOIN specials sp ON p.products_id = sp.products_id 
                 LEFT JOIN products_to_categories p2c ON p.products_id = p2c.products_id 
                 WHERE p.products_status = 1 
                 AND pd.language_id = FLOOR($languages_id)
                 AND p2c.categories_id not in (" . implode(",", $exc_cat_id) . ")
                 ORDER BY p2c.categories_id, pd.products_name");

 

seems they renamed the field in that version from products_cost to products_price_cost

 

Steve

,

Edited December 20, 2009 by steve_s

[AndreD]

Thanks, you are good at this :)

I see that you are solving this in theory only, and that's impressive.

I've been fooling with the while.. routines to try to achive this result.

now it works, but the product name column is empty, so it's a little hard to verify what's what. - maybe it's just a minor typo somewhere ? (it did work before this change)

 

Update: adding "pd.products_name," to teh SELECT command solved it..

 

Will re-check everything now, as it does not seem to be all sorted alphabetic ..

Edited December 20, 2009 by AndreD

[AndreD]

Products are still not sorted in alphabetic order ..

 

update 2: "ORDER BY pd.products_name, p2c.categories_id");" fixed that.

 

 

Thank you *very* much.

I'll upload the complete fix to the contribution's page. It's most likely very useful to others

should I credit you as "steve_s" - or maybe you prefer to do it yourself ?

 

The complete, nice result is here:

 

$products_query = tep_db_query("SELECT distinct pd.products_name, p.products_id, p.products_quantity, p.products_price_cost, p.products_price  FROM " .TABLE_PRODUCTS." p
                 LEFT JOIN products_description pd ON p.products_id = pd.products_id 
                 LEFT JOIN specials sp ON p.products_id = sp.products_id 
                 LEFT JOIN products_to_categories p2c ON p.products_id = p2c.products_id 
                 WHERE p.products_status = 1 
                 AND pd.language_id = FLOOR($languages_id)
                 AND p2c.categories_id not in (" . implode(",", $exc_cat_id) . ")
                 ORDER BY pd.products_name, p2c.categories_id");

[steve_s]

Products are still not sorted in alphabetic order ..

 

update 2: "ORDER BY pd.products_name, p2c.categories_id");" fixed that.

 

 

Thank you *very* much.

I'll upload the complete fix to the contribution's page. It's most likely very useful to others

should I credit you as "steve_s" - or maybe you prefer to do it yourself ?

 

The complete, nice result is here:

 

$products_query = tep_db_query("SELECT distinct pd.products_name, p.products_id, p.products_quantity, p.products_price_cost, p.products_price  FROM " .TABLE_PRODUCTS." p
                 LEFT JOIN products_description pd ON p.products_id = pd.products_id 
                 LEFT JOIN specials sp ON p.products_id = sp.products_id 
                 LEFT JOIN products_to_categories p2c ON p.products_id = p2c.products_id 
                 WHERE p.products_status = 1 
                 AND pd.language_id = FLOOR($languages_id)
                 AND p2c.categories_id not in (" . implode(",", $exc_cat_id) . ")
                 ORDER BY pd.products_name, p2c.categories_id");

Hi

 

credit both of us, as you made changes too, you can upload it, as i don't use that contribution

 

Steve

[redrum]

Does anyone have any idea of why the name of the categories disappear after this update, and how to solve that?