Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Seperate Pricing Per Customer v3.5


scendent

Recommended Posts

Yes, tell us what is not working.

 

For the moment I got the name_trail working with this code:

// add the products model to the breadcrumb trail KISSMT
 if (isset($HTTP_GET_VARS['products_id'])) {
   $name_query = tep_db_query("select products_name from " . TABLE_PRODUCTS_DESCRIPTION . " where products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' AND language_id=" . (int)$languages_id . "");
   if (tep_db_num_rows($name_query)) {
     $name = tep_db_fetch_array($name_query);
     $breadcrumb->add($name['products_name'], tep_href_link(FILENAME_PRODUCT_INFO, 'cPath=' . $cPath . '&products_id=' . $HTTP_GET_VARS['products_id']));
   }
 }

 

 

But I dont know how to integrate the sppc code, specially the followith code:

 

ref:

// BOF SPPC Hide products and categories from groups
//    $model_query = tep_db_query("select p.products_model from " . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c using(products_id) left join " . TABLE_CATEGORIES . " c using(categories_id) where p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and find_in_set('".$customer_group_id."', products_hide_from_groups) = 0 and find_in_set('" . $customer_group_id . "', categories_hide_from_groups) = 0");
// EOF SPPC Hide products and categories from groups

 

I need a name_query not the model_query. What could be the coorect code for this??

Link to comment
Share on other sites

I need a name_query not the model_query. What could be the coorect code for this??

 

This seems to work (this in includes/application_top.php as you know).

 

// add the products model to the breadcrumb trail
 if (isset($HTTP_GET_VARS['products_id'])) {
// BOF SPPC hide categories from groups
// "distinct pd.products_name" is necessary because if a products is in more than one  
// category you will get as many rows as categories
   $model_query = tep_db_query("select distinct pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_CATEGORIES . " c using(categories_id) where p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and find_in_set('".$customer_group_id."', products_hide_from_groups) = 0 and find_in_set('" . $customer_group_id . "', categories_hide_from_groups) = 0");

Link to comment
Share on other sites

Hi All,

and most importantly hi Jan.

First of all thanks for taking care of this contribution, I've been running it for a year now and had multiple occasions to search thru the assistance you provided on this forums during years! amazing.

This time it's unfortunately my turn.

 

I'm trying to figure out a good way to create an "auto-assign to group" scheme, on which upon registration the user gets assigned automatically based on his country of origin. The currency issue wouldn't be a problem as the currency for my customers would be just the same.

I searched through the forum and I could find two "hints" on the matter, HERE and HERE

 

Here's what I don't get: In Loadmaster's code I do not understand where is the actual action of assigning the user to a group and how could this code be linked to a country of origin instead of a pre-defined value set on top of create_account.php

 

can you assist?

thank you

Link to comment
Share on other sites

Ok some updates:

I figured, by using other snippets of code, how to make it so that a user coming from a specific country (say id 204, switzerland) can be assigned to a specific group.

 

// put everybody in group

 

if ($country == 204) $sql_data_array['customers_group_id'] = '3';

 

 

But, how can I associate more than a country to a specific group without setting it manually?

Link to comment
Share on other sites

Ok some updates:

I figured, by using other snippets of code, how to make it so that a user coming from a specific country (say id 204, switzerland) can be assigned to a specific group.

 

// put everybody in group

 

if ($country == 204) $sql_data_array['customers_group_id'] = '3';

 

 

But, how can I associate more than a country to a specific group without setting it manually?

 

I take your word for it that this works. Assuming that you can make an array first with all those countries: $country_cg_3 = array(204,106,344,102); and then

 

if (in_array($country, $country_cg_3)) { 
 $sql_data_array['customers_group_id'] = '3'; 
}

Link to comment
Share on other sites

Hello Jon,

 

Been using SPPC for many years and it has worked flawlessly since first installed, thank you. I have a site we need to add the MSRP contribution, and was wondering if SPPC works with MSRP? We have both retail and wholesale sales.

 

Thanks JR

Link to comment
Share on other sites

I have a site we need to add the MSRP contribution, and was wondering if SPPC works with MSRP? We have both retail and wholesale sales.

I'm not that familiar with the MSRP contribution but since the first query is always getting the products_price (the retail price) and then queries for the customer group price you can always assign products_price to a retail price (new variable) and then change products_price to be the wholesale price later on.

Link to comment
Share on other sites

I take your word for it that this works. Assuming that you can make an array first with all those countries: $country_cg_3 = array(204,106,344,102); and then

 

if (in_array($country, $country_cg_3)) { 
 $sql_data_array['customers_group_id'] = '3'; 
}

Thank you Jan,

perfect help. I also confirm you that the previous snipped code works and also successfully get's imported in a Vtiger / OSC bridge we're working on.

Take care,

francesco

Link to comment
Share on other sites

  • 2 weeks later...

There is a misplacement of " . TABLE_PRODUCTS . " p in your code in that part (top query in your post 5592 that causes that error:

 

From SPPC:

tmp_pp using(products_id), " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c

Yours:

tmp_pp using(products_id) left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS . " p" . TABLE_PRODUCTS_TO_CATEGORIES . " p2c

Hi Jan, not sure what happened this time, but all was working fine and all of a sudden this error occurs.

 

1054 - Unknown column 'p.products_id' in 'on clause'

select count(p.products_id) as total from products p, products_description pd, manufacturers m left join specials_retail_prices s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '27'

[TEP STOP]

 

have looked back through the post to see if i could find the error, but I just cant see it.

here is the section of the code that I believe the error belongs to:

// BOF Separate Pricing Per Customer
       if ($status_product_prices_table == true) {
       $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, tmp_pp.products_price, p.products_tax_class_id, IF(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . $product_prices_table . " as tmp_pp using(products_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";	
       } else { // either retail or no need to get correct special prices
       $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS_RETAIL_PRICES . " s using(products_id) where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
       } // end else { // either retail...
// EOF Separate Pricing Per Customer
     } else {
       // We show them all
// BOF Separate Pricing Per Customer
       if ($status_product_prices_table == true) {
       $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, tmp_pp.products_price, p.products_tax_class_id, IF(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . $product_prices_table . " as tmp_pp using(products_id), " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
       } else { // either retail or no need to get correct special prices
       $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
     } // end else { // either retail...
// EOF Separate Pricing per Customer

thanks in advance.

Link to comment
Share on other sites

Hi Jan, not sure what happened this time, but all was working fine and all of a sudden this error occurs.

 

1054 - Unknown column 'p.products_id' in 'on clause'

Could it be that you have an old version of SPPC? The queries on index.php were changed for mysql5 some time ago. Those have a comment behind them like:

 

// BOF Separate Pricing Per Customer
if ($status_product_prices_table == true) { // ok in mysql 5

 

In that case you would also have to change the advanced_search_results.php I believe.

Link to comment
Share on other sites

Hi Jan, not sure what happened this time, but all was working fine and all of a sudden this error occurs.

 

1054 - Unknown column 'p.products_id' in 'on clause'

select count(p.products_id) as total from products p, products_description pd, manufacturers m left join specials_retail_prices s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '27'

[TEP STOP]

 

have looked back through the post to see if i could find the error, but I just cant see it.

here is the section of the code that I believe the error belongs to:

// BOF Separate Pricing Per Customer
       if ($status_product_prices_table == true) {
       $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, tmp_pp.products_price, p.products_tax_class_id, IF(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . $product_prices_table . " as tmp_pp using(products_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";	
       } else { // either retail or no need to get correct special prices
       $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS_RETAIL_PRICES . " s using(products_id) where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
       } // end else { // either retail...
// EOF Separate Pricing Per Customer
     } else {
       // We show them all
// BOF Separate Pricing Per Customer
       if ($status_product_prices_table == true) {
       $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, tmp_pp.products_price, p.products_tax_class_id, IF(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . $product_prices_table . " as tmp_pp using(products_id), " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
       } else { // either retail or no need to get correct special prices
       $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
     } // end else { // either retail...
// EOF Separate Pricing per Customer

thanks in advance.

Thanks Jan for responding. How do I go about fixing them. Do I have to upgrade to the new version of SPPC.

Edited by pingpongrob
Link to comment
Share on other sites

Thanks Jan for responding. How do I go about fixing them. Do I have to upgrade to the new version of SPPC.

No need to upgrade, just compare your index.php with the one from a newer version of SPPC and exchange the queries. Do the same for advanced_search_results.php (or perhaps you can just exchange them for the newer versions).

Link to comment
Share on other sites

No need to upgrade, just compare your index.php with the one from a newer version of SPPC and exchange the queries. Do the same for advanced_search_results.php (or perhaps you can just exchange them for the newer versions).

 

Hello Jan,

When I replace the queries from the Update, I get the following error.

 

Parse error: syntax error, unexpected ';', expecting T_PAAMAYIM_NEKUDOTAYIM in /home/affordab/public_html/shop/index.php on line 233

Link to comment
Share on other sites

When I replace the queries from the Update, I get the following error.

 

Parse error: syntax error, unexpected ';', expecting T_PAAMAYIM_NEKUDOTAYIM in /home/affordab/public_html/shop/index.php on line 233

A quick search in Google indicates it has something to do with a double colon ::

Probably a tiny cut and paste problem.

 

If you can't find it show some lines of your code above and including that line here (like 10 lines or so).

Link to comment
Share on other sites

A quick search in Google indicates it has something to do with a double colon ::

Probably a tiny cut and paste problem.

 

If you can't find it show some lines of your code above and including that line here (like 10 lines or so).

Thank you Jan,

I copied the index.php file from my sister in Laws Website (which I created), and all is fine. Would really like to know how these problems arise.

Link to comment
Share on other sites

  • 2 weeks later...

Hi all!

I've got 11 customer groups and more than 1000 procucts. Everytime I create a new customer group I select it and type in the prices in Quick Updates (for SPPC).

Then I have to hide a couple of hundred of products for that customer group. Can Quick Updates be modified so that it can handle hiding of products too? That would save a lot of hours...

//Micke

Link to comment
Share on other sites

Hi Jan

 

Thanks for this huge contribution

 

Everything seems to check out fine except index.php page

At the bottom of the page I have "New Products For September" - this area no images is displayed (missing new products images)and product path when clicked = http://localhost/shop/product_info.php?products_id=&osCsid=c94532e70abb5728bb6f13f70c52b167

 

Tying to find error with Winmerge on a heavy modified store but still no noticeable errors

 

Also using pcpro creator addon(working) - when I build a product I get error " 1136 - Column count doesn't match value count at row 1

 

insert into products_attributes values (NULL, '490', '244', '362', '0', '+')

 

[TEP STOP]"

 

Please help

Getting the Phoenix off the ground

Link to comment
Share on other sites

I feel a bit stupid now, but i have looked for several hours after what might be the problem, but cant figure it out.

 

The contribution, seems to be successfully installed, and i have created a new group, and a product with an different price. I have also created a test account, but nothing seems to happen. The tax still displays, even if i disable it, and the price dont change.

 

What could be the problem?

Link to comment
Share on other sites

I feel a bit stupid now, but i have looked for several hours after what might be the problem, but cant figure it out.

 

The contribution, seems to be successfully installed, and i have created a new group, and a product with an different price. I have also created a test account, but nothing seems to happen. The tax still displays, even if i disable it, and the price dont change.

 

What could be the problem?

Hard to say. If it doesn't work anywhere then the correct session variables are probably not set (set when you go through login.php). Echo them in the footer.php e.g.

 

<!--
<?php
print_r($_SESSION);
?>
-->

and do a view source to see them.

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