Seperate Pricing Per Customer v3.5

[q97]

Just noticed one more glitch...after I signed up a new account, then logged off - all the wholesale prices were showing!

 

Now I'm really going to bed...

[q97]

Woohoo! That fixed it.

 

I now get the wholesale prices displayed on my wholesale account as expected. The only problem I have now is that when I click on an item, it shows the full retail price...any ideas on that one?

 

Again that comes down to files uploaded (or not) in the wrong location. I don't think my ftp transfer went smoothly...

[katiallo]

Hello,

 

I have a small problem. I also have 'all products' contribution installled and all products there are visible even if I hide from groups. Is there code to use in this situation?

 

Thank you

[q97]

Ok, I seem to have fixed most of my problems (the ones I know about anyway) - most of the problems seem to be that the files ended up either in the wrong place or the FTP program didn't overwrite the files on the server :rolleyes: Anyhow, as far as I know it is working correctly. I'll have to do some test orders and invoicing etc though to be certain.

 

Thanks for your help :)

[Adam.A]

Hi JanZ,

 

I am getting the following error when I try to check out and I cant seem to find any other posts relating to it can you help.

 

1052 - Column 'products_id' in where clause is ambiguous

 

select products_status, options_id, options_values_id, attributes_hide_from_groups, '0' as hide_attr_status from products left join products_attributes using(products_id) where products_id = '29'

 

[TEP STOP]

 

-Adam

[Jan Zonjee]

I am getting the following error when I try to check out and I cant seem to find any other posts relating to it can you help.

This post should help IMO.

[Guest]

Thankyou Janz,

 

As I an not a php programmer I was very scared to change the code (in what is now an active shop) www.money-works.com.au.

 

However I plucked up courage AND IT WORKS!!

 

Regards,

Peter :lol:

[katiallo]

Janz,

 

I got these contributions finaly working but I do have a small problem.

Products are visible on 'Printable Catalog' and "View all Products' how can I correct that?

 

Thank you again

[Jan Zonjee]

I got these contributions finaly working but I do have a small problem.

Products are visible on 'Printable Catalog' and "View all Products' how can I correct that?

Good to hear most is working correctly now.

For the 'Printable Catalog' and 'View all Products' contribution that show hidden products you will need to find the queries that pick up the products. Just before that you add the code that determines the $customer_group_id:

  if (isset($_SESSION['sppc_customer_group_id']) && $_SESSION['sppc_customer_group_id'] != '0') {
 $customer_group_id = $_SESSION['sppc_customer_group_id'];
 } else {
  $customer_group_id = '0';
 }

then change the query as needed. Basically, you need to add the table categories in the query (with a join on products or products_description through products_to_categories) and then you use the find_in_set commands to look for hidden products/categories. The check_query in product_info.php is a good example.

[katiallo]

Good to hear most is working correctly now.

For the 'Printable Catalog' and 'View all Products' contribution that show hidden products you will need to find the queries that pick up the products. Just before that you add the code that determines the $customer_group_id:

  if (isset($_SESSION['sppc_customer_group_id']) && $_SESSION['sppc_customer_group_id'] != '0') {
 $customer_group_id = $_SESSION['sppc_customer_group_id'];
 } else {
  $customer_group_id = '0';
 }

then change the query as needed. Basically, you need to add the table categories in the query (with a join on products or products_description through products_to_categories) and then you use the find_in_set commands to look for hidden products/categories. The check_query in product_info.php is a good example.

 

Thank you JanZ

 

I'm very new to this and I tried to change the query but I'm getting errors.

Is it possible to help me?

 

here is my allproducts query:

$listing_sql = "select p.products_id, p.products_model, pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id $where order by pd.products_name";

 

and print_catalog

$print_catalog_query_raw = "select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, cd.categories_name, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id=p2c.products_id left join " . TABLE_CATEGORIES . " c on p2c.categories_id=c.categories_id left join " . TABLE_CATEGORIES_DESCRIPTION . " cd on c.parent_id='0' and c.categories_id=cd.categories_id left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by cd.categories_name, c.parent_id, c.sort_order, c.categories_id, pd.products_name";

 

I'm very thankfull for the contribution and help you provided.

[Jan Zonjee]

I'm very new to this and I tried to change the query but I'm getting errors.

 

here is my allproducts query:

$listing_sql = "select p.products_id, p.products_model, pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id $where order by pd.products_name";

This should be working (see below). Note that I made products_id distinct (to avoid doubling of products when they appear in two categories, which can not happen, not before). Left specials_new_products_price at NULL (also took out the join to table specials).

 

Special prices should be retrieved in a second query, possibly you can use similar code as used in includes/modules/new_products.php.

 

$listing_sql = "select distinct(p.products_id), p.products_model, pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, NULL as specials_new_products_price, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on pd.products_id = p2c.products_id left join " . TABLE_CATEGORIES . " c using(categories_id) $where and find_in_set('".$customer_group_id."', products_hide_from_groups) = 0 and find_in_set('" . $customer_group_id . "', categories_hide_from_groups) = 0 order by pd.products_name";

The $where above should contain the word where otherwise the and find_in_set will trigger an error. I guess it will be not a problem though.

and print_catalog

$print_catalog_query_raw = "select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, cd.categories_name, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id=p2c.products_id left join " . TABLE_CATEGORIES . " c on p2c.categories_id=c.categories_id left join " . TABLE_CATEGORIES_DESCRIPTION . " cd on c.parent_id='0' and c.categories_id=cd.categories_id left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by cd.categories_name, c.parent_id, c.sort_order, c.categories_id, pd.products_name";

This one works also (products will double if they are in two categories). Left out the specials stuff as this cannot be used in SPPC due to the way the table specials has been changed.

$print_catalog_query_raw = "select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_image, p.products_price, p.products_tax_class_id, NULL as specials_new_products_price, p.products_date_added, cd.categories_name, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id=p2c.products_id left join " . TABLE_CATEGORIES . " c on p2c.categories_id=c.categories_id left join " . TABLE_CATEGORIES_DESCRIPTION . " cd on c.parent_id='0' and c.categories_id=cd.categories_id left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' where products_status = '1' and find_in_set('".$customer_group_id."', products_hide_from_groups) = 0 and find_in_set('" . $customer_group_id . "', categories_hide_from_groups) = 0 order by cd.categories_name, c.parent_id, c.sort_order, c.categories_id, pd.products_name";

[katiallo]

This should be working (see below). Note that I made products_id distinct (to avoid doubling of products when they appear in two categories, which can not happen, not before). Left specials_new_products_price at NULL (also took out the join to table specials).

 

Special prices should be retrieved in a second query, possibly you can use similar code as used in includes/modules/new_products.php.

 

$listing_sql = "select distinct(p.products_id), p.products_model, pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, NULL as specials_new_products_price, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on pd.products_id = p2c.products_id left join " . TABLE_CATEGORIES . " c using(categories_id) $where and find_in_set('".$customer_group_id."', products_hide_from_groups) = 0 and find_in_set('" . $customer_group_id . "', categories_hide_from_groups) = 0 order by pd.products_name";

The $where above should contain the word where otherwise the and find_in_set will trigger an error. I guess it will be not a problem though.

 

This one works also (products will double if they are in two categories). Left out the specials stuff as this cannot be used in SPPC due to the way the table specials has been changed.

$print_catalog_query_raw = "select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_image, p.products_price, p.products_tax_class_id, NULL as specials_new_products_price, p.products_date_added, cd.categories_name, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id=p2c.products_id left join " . TABLE_CATEGORIES . " c on p2c.categories_id=c.categories_id left join " . TABLE_CATEGORIES_DESCRIPTION . " cd on c.parent_id='0' and c.categories_id=cd.categories_id left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' where products_status = '1' and find_in_set('".$customer_group_id."', products_hide_from_groups) = 0 and find_in_set('" . $customer_group_id . "', categories_hide_from_groups) = 0 order by cd.categories_name, c.parent_id, c.sort_order, c.categories_id, pd.products_name";

 

 

Thank you JanZ for all your help :) :)

Everything is now working

[lucass]

Hi,

 

In my OsCommerce Shop I need to specify different % discounts for users. For example, for customer "john smith" I would like to give a 20% of discount in his shipping. for user "Peter Bill" a 15$% of discount in all prices, etc...

 

But What I need is that when John Smith logins in the site, he ONLY see the prices with the 20% of the discount. I mean, if a product costs $100, he would see $80.

 

If Peter Bill logins, he would see $85 for that product.

 

But if they surf the site without logging in the both would see $100 for that product.

 

Yours,

Lucas

[lucass]

Hi,

 

In my OsCommerce Shop I need to specify different % discounts for users. For example, for customer "john smith" I would like to give a 20% of discount in his shipping. for user "Peter Bill" a 15$% of discount in all prices, etc...

 

But What I need is that when John Smith logins in the site, he ONLY see the prices with the 20% of the discount. I mean, if a product costs $100, he would see $80.

 

If Peter Bill logins, he would see $85 for that product.

 

But if they surf the site without logging in the both would see $100 for that product.

 

Yours,

Lucas

[lucass]

Hi,

 

In my OsCommerce Shop I need to specify different % discounts for users. For example, for customer "john smith" I would like to give a 20% of discount in his shipping. for user "Peter Bill" a 15$% of discount in all prices, etc...

 

But What I need is that when John Smith logins in the site, he ONLY see the prices with the 20% of the discount. I mean, if a product costs $100, he would see $80.

 

If Peter Bill logins, he would see $85 for that product.

 

But if they surf the site without logging in the both would see $100 for that product.

 

Yours,

Lucas

[Jan Zonjee]

In my OsCommerce Shop I need to specify different % discounts for users. For example, for customer "john smith" I would like to give a 20% of discount in his shipping. for user "Peter Bill" a 15$% of discount in all prices, etc...

 

But What I need is that when John Smith logins in the site, he ONLY see the prices with the 20% of the discount. I mean, if a product costs $100, he would see $80.

 

If Peter Bill logins, he would see $85 for that product.

 

But if they surf the site without logging in the both would see $100 for that product.

Well, yes SPPC can do all that so what is your question?

[TracyS]

Hi Janz,

 

I'm finally attempting to add the Quantity Price Breaks for SPPC. Looks like I need to first add v1, then v1_02 and then v2 for v1_02 - right?

 

Anyway - the first bit of instructions for v1 are in the product_listing.php page. My product_listing.php does not have a "Buy Now" button. With Master Products you enter the quantity into a text box and then click the Add to Cart button. So I'm not sure if the same edits apply or not.

 

In my master_listing.php file I have the case MASTER_LIST_BUY_NOW in the file twice as follows:

 

case 'MASTER_LIST_BUY_NOW':
	$lc_text = TABLE_HEADING_BUY_NOW;
	$lc_align = 'center';
	break;

 

a little further down the page:

if ( ($column_list[$col] != 'MASTER_LIST_BUY_NOW') && ($column_list[$col] != 'MASTER_LIST_IMAGE') && ($column_list[$col] != 'MASTER_LIST_MULTIPLE') && ($column_list[$col] != 'MASTER_LIST_DESCRIPTION') && ($column_list[$col] != 'MASTER_LIST_OPTIONS') ) {
  $lc_text = tep_create_sort_heading($HTTP_GET_VARS['sort'], $col+1, $lc_text);
}

 

And further on still:

case 'MASTER_LIST_BUY_NOW':
	  $lc_align = 'center';
	  $lc_valign = 'top';
	  if ((STOCK_CHECK == 'true')&&(tep_get_products_stock($listing[$x]['products_id']) < 1)) {
	  $lc_text = TEXT_STOCK;
	} elseif ((STOCK_CHECK == 'false')&&(tep_get_products_stock($listing[$x]['products_id']) < 1)) {
	  $qty_array = array();
	  for ($i=0; $ns = 20, $i <= $ns; $i++) {
	  $qty_array[] = array('id' => $i, 'text' => $i);

	  $lc_text = tep_draw_input_field('Qty_ProdId_' . $listing[$x]['products_id'], '', 'size="4"');

	  }
	} else {
	  $quantity = tep_get_products_stock($listing[$x]['products_id']);  
	  $qty_array = array();
	  for ($i=0; $ns = (($quantity < 20) ? $quantity : 20), $i <= $ns; $i++) {
	  $qty_array[] = array('id' => $i, 'text' => $i);

	  $lc_text = tep_draw_input_field('Qty_ProdId_' . $listing[$x]['products_id'], '', 'size="4"');

	  }
  }
	 break;

 

Any ideas on what to do with that code for the Quantity Price Breaks to work? Do you need to see the entire master_listing.php file?

 

Thanks! I'll let you know if I encounter any other pages that I have questions on :blush:

[lucass]

Well, yes SPPC can do all that so what is your question?

 

Thanks JanZ!

[Jan Zonjee]

I'm finally attempting to add the Quantity Price Breaks for SPPC. Looks like I need to first add v1, then v1_02 and then v2 for v1_02 - right?

No, go straight for the latest version.

 

Anyway - the first bit of instructions for v1 are in the product_listing.php page. My product_listing.php does not have a "Buy Now" button. With Master Products you enter the quantity into a text box and then click the Add to Cart button. So I'm not sure if the same edits apply or not.

Had to think about that one. Has been a while ;)

 

Actually, the instructions tell you to replace a large chunk of code (line 78-211). Like with SPPC it gets the products_id's first, then queries the price breaks, special prices and then the only real change is that instead of products price it shows the price range (lowest price-highest price). That is all. Not a big issue if you leave it out (at least for the moment) :rolleyes:

[TracyS]

No, go straight for the latest version.

Had to think about that one. Has been a while ;)

 

Actually, the instructions tell you to replace a large chunk of code (line 78-211). Like with SPPC it gets the products_id's first, then queries the price breaks, special prices and then the only real change is that instead of products price it shows the price range (lowest price-highest price). That is all. Not a big issue if you leave it out (at least for the moment) :rolleyes:

 

LOL - Ok - now we have a new issue. My product_info.php does not have this line:

<td class="main" align="right"><?php echo tep_draw_hidden_field('products_id', $product_info['products_id']) . tep_image_submit('button_in_cart.gif', IMAGE_BUTTON_IN_CART); ?></td>

 

That seems to be controlled in the master_listing.php file through the case 'MASTER_LIST_BUY_NOW' posted above. I am a little bit confused though on how to tweak this so that both Master Products and the Price Breaks both work as the input field exists and looks like this:

$lc_text = tep_draw_input_field('Qty_ProdId_' . $listing[$x]['products_id'], '', 'size="4"');

 

while yours looks like this:

<?php echo TEXT_ENTER_QUANTITY . ":" . tep_draw_input_field('cart_quantity', $pf->adjustQty(1), 'size="6"'); ?>

 

So - can I change 'Qty_ProdID_' to 'cart_quantity' ? Do I need to keep the .$listing[$x] in there somewhere?

 

The fun of getting multiple contrib's to play nice together - LOL :blush:

[TracyS]

LOL - Ok - now we have a new issue. My product_info.php does not have this line:

<td class="main" align="right"><?php echo tep_draw_hidden_field('products_id', $product_info['products_id']) . tep_image_submit('button_in_cart.gif', IMAGE_BUTTON_IN_CART); ?></td>

 

Ok - my product_info.php DOES have that line - LOL But I still am wondering about needing changes to the master_listing.php file :blush:

[TracyS]

Ok - this is the line in my application_top.php that goes with the adding of a slave product to the cart (which all of the products that are purchasable in our store are slave products)

 

with attributes

$cart->add_cart($prodId, $cart->get_quantity(tep_get_uprid($prodId,$HTTP_POST_VARS["id_$prodId"]))+$qty, $HTTP_POST_VARS["id_$prodId"]);

 

without attributes

$cart->add_cart($prodId, $cart->get_quantity($prodId)+$qty);

 

So - the $prodId here and the $qty here would need to be changed according to whatever is put into the master_listing.php file - correct?

 

Still not sure what to put in that file - but at least trying to piece together what changes would need to be made elsewhere too - LOL :blush:

Edited April 27, 2007 by TracyS

[Jan Zonjee]

$lc_text = tep_draw_input_field('Qty_ProdId_' . $listing[$x]['products_id'], '', 'size="4"');

 

while yours looks like this:

<?php echo TEXT_ENTER_QUANTITY . ":" . tep_draw_input_field('cart_quantity', $pf->adjustQty(1), 'size="6"'); ?>

 

So - can I change 'Qty_ProdID_' to 'cart_quantity' ? Do I need to keep the .$listing[$x] in there somewhere?

Just change the '' to $pf->adjustQty(1) otherwise you would need to change all the other stuff for master products. Not needed, that code only inserts the minimum quantity for a product in the input box:

$lc_text = tep_draw_input_field('Qty_ProdId_' . $listing[$x]['products_id'], $pf->adjustQty(1), 'size="4"');

[TracyS]

Just change the '' to $pf->adjustQty(1) otherwise you would need to change all the other stuff for master products. Not needed, that code only inserts the minimum quantity for a product in the input box:

$lc_text = tep_draw_input_field('Qty_ProdId_' . $listing[$x]['products_id'], $pf->adjustQty(1), 'size="4"');

 

Well - I have it up on my localhost and it appears to be running - however;

 

With the above change my product_info.php now has a box generated by master_listing.php that looks like this:

 

Item# Description Name Options Select Price

1302 1 4oz bottle prod None $pf->adjustQty(1) $19.95

 

Where the "Select" is now that bit of code rather than just the empty text box for them to enter the quantity they would like to order.

 

Then - just above the "Add to Cart" button is a new text box asking for Quantity with a default of 1 already entered.

 

If I highlight the code in the Select text box and enter the quantity, then remove the 1 from the Quantity text box, it does do the price break correctly.

 

Now I'm going to go back and see if I remove the $pf->adjustQty(1) bit of code and remove the new Quantity 1 text box if it will still work correctly. Just thought I'd post my current results thus far :thumbsup:

[TracyS]

Now I'm going to go back and see if I remove the $pf->adjustQty(1) bit of code and remove the new Quantity 1 text box if it will still work correctly. Just thought I'd post my current results thus far :thumbsup:

 

Ok - I removed the $pf->adjustQty(1) from the master_listing.php and I also removed the Quantity 1 text box from product_info.php. It is correctly adjusting the product price for quantity purchases! YAY!! :thumbsup:

 

Now for the update process - LOL