Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Stocktaking Cost 0.3 problem.


AndreD

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by steve_s
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by steve_s
Link to comment
Share on other sites

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 by AndreD
Link to comment
Share on other sites

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");

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 5 years later...

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