Jump to content
scendent

Seperate Pricing Per Customer v3.5

Recommended Posts

Hi !

 

I want to install SPPC contribution, it's very impressive contribution !!!

 

Could someone advise please regarding the following issues:

 

1. I know that this contribution can work with the "easy populate" contribution. My question..

When downloading prices by "easy populate" and then updating the group prices and uploading them

(by EP) do they new group prices is showing in the SPPC admin interface?

 

2. Is the email order confirmation for each group could be modified to have specific comment in the

output ? for example: In Dealer group the output will be like this:

* Prices are without Taxes & Shipment costs ??

 

3. Is this contribution supports Group prices by percent discounts ?

 

Any replies will be much appreciated !! Thanks in advance ! :)

 

Regards,

Elazar

Share this post


Link to post
Share on other sites
I was wondering if the code changes for hiding product attributes for customer groups was available.
Sure, I can email the instructions and files to you (PM me your email address in that case). It is not completely finished yet (actually I should add something to the instructions). The code that should throw out attributes that can be added as retail customer but are hidden for a group is not working perfectly yet.

Share this post


Link to post
Share on other sites
1. I know that this contribution can work with the "easy populate" contribution. My question..

When downloading prices by "easy populate" and then updating the group prices and uploading them

(by EP) do they new group prices is showing in the SPPC admin interface?

I guess so, since the admin gets it prices from the database. I have no experience using EP though so...
2. Is the email order confirmation for each group could be modified to have specific comment in the

output ? for example: In Dealer group the output will be like this:

* Prices are without Taxes & Shipment costs ??

I think that should be possible.
3. Is this contribution supports Group prices by percent discounts ?
No, but in the thread a sql query is mentioned a number of times to add all your percentage discounted prices for a group at once.

Share this post


Link to post
Share on other sites

Well, my large project involving SPPC is coming along slowly but surely. (Link to a description here.)

 

Here's some outstanding points I'm still not sure how to handle, in case anyone has any ideas that might help:

 

- I'd to see the regular price and then their discounted price. Is that Show Price List for SPPC?

- Also, it'd be nice to show how much they saved on the final confirmation page. Is this out there anywhere?

- Create reports that show the sales being done under accounts with each PIN. Have master reports on all PINs for the admin, and inidividual reports for each pro about their PIN only.

- Link the ability to see these individual reports to the pros' regular account logins.

- Oh, and I believe there's something coming that will give more control over attribute pricing? Is that right? I'll have to keep an eye on that, since everything's supposed to be at a 10% discount, but some of my products have attributes that add to the base cost, and 10% is not getting taken off those charges.

 

Thanks again/still! ;-)

Share this post


Link to post
Share on other sites
Here's some outstanding points I'm still not sure how to handle, in case anyone has any ideas that might help:

 

- I'd to see the regular price and then their discounted price. Is that Show Price List for SPPC?

Yes, it does show the list price next to the group price.
- Also, it'd be nice to show how much they saved on the final confirmation page. Is this out there anywhere?
Not that I know but shouldn't be to difficult to make.
- Create reports that show the sales being done under accounts with each PIN. Have master reports on all PINs for the admin, and inidividual reports for each pro about their PIN only.

- Link the ability to see these individual reports to the pros' regular account logins.

Custom jobs for your site I believe. I'm not aware of any contributions that will do this.
- Oh, and I believe there's something coming that will give more control over attribute pricing? Is that right?
Yes, see a few posts back for more details on the progress.

Share this post


Link to post
Share on other sites

Hi,

 

I've been scrolling through the pages for this contrib but haven't had any luck finding what i'm looking for (yes, I'll freely admit i didn't read all 143 pages!).

 

Anyway,probably a simple problem with permissions or something, but when I add a new product the prices don't show up. If I edit the product I can enter the prices (in my case wholesale and retail), but then when I preview it shows the price as 0. If I log in as a customer (either wholesale or retail) it also shows 0. If I go to edit the product again, the correct prices show within the edit window!

 

Any ideas?

Share this post


Link to post
Share on other sites

Hi, sorry, I figured it out...I was entering Canadian dollar amounts but english.php was still looking for USD, which I had deleted...

Share this post


Link to post
Share on other sites

www.mydomain.com/store/checkout_payment.php

showing this error

 

1146 - Table 'tiffanyc_osc1.TABLE_CUSTOMERS_GROUPS' doesn't exist

 

select IF(c.customers_payment_allowed <> '', c.customers_payment_allowed, cg.group_payment_allowed) as payment_allowed from customers c, TABLE_CUSTOMERS_GROUPS cg where c.customers_id = '2' and cg.customers_group_id = '0'

 

[TEP STOP]

 

I can see from phpmyadmin TABLE_CUSTOMERS_GROUPS is there. I've run the SQL supplied in this contribution.

 

thank you for help me

Share this post


Link to post
Share on other sites
www.mydomain.com/store/checkout_payment.php

showing this error

 

1146 - Table 'tiffanyc_osc1.TABLE_CUSTOMERS_GROUPS' doesn't exist

Clearly a case where you haven't added the define for that table (yet?) to catalog\includes\database_tables.php (around line 2516 in the new_install_directions.txt).

Share this post


Link to post
Share on other sites

Sorry I have one more problem with editing advanced_search_result.php

1066 - Not unique table/alias: 'pd'

 

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c, products_description pd, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%open%' or p.products_model like '%open%' or m.manufacturers_name like '%open%') )

 

[TEP STOP]

Share this post


Link to post
Share on other sites
Sorry I have one more problem with editing advanced_search_result.php

1066 - Not unique table/alias: 'pd'

 

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c, products_description pd, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%open%' or p.products_model like '%open%' or m.manufacturers_name like '%open%') )

 

[TEP STOP]

 

I'm no PHP expert, but it would appear to me that you are declaring the alias pd for the products table twice in this statement. Remove one of the "products_description pd" bits of code (I would recommend the 2nd one as the first one is being told to left join with specials s on p.products_id etc....). I think that should fix the problem. But as I said, I'm no PHP expert, so it may not :blush:

 

Tracy


~Tracy
 

Share this post


Link to post
Share on other sites
Sorry I have one more problem with editing advanced_search_result.php

1066 - Not unique table/alias: 'pd'

Tracy is right about the declaration of the alias twice. I am not sure how that got in because it just isn't right. Anyway, I think the problem is due to MySQL5. Follow the directions in this post on page 122 for the Mysql5 safe queries for advanced_search_result.php and index.php.

 

And no, you can't specify a percentage as discount although you can add all those prices in one query in the database (somewhere in the thread it is mentioned a couple of times).

Share this post


Link to post
Share on other sites

Anybody know what page has the sql query for the product_listing.php page? I need to find the code (or add the code if it isn't there) that tells the query not to list a product if product_status=0

 

Thanks

Tracy


~Tracy
 

Share this post


Link to post
Share on other sites
Anybody know what page has the sql query for the product_listing.php page? I need to find the code (or add the code if it isn't there) that tells the query not to list a product if product_status=0
The product listing (includes/modules/product_listing.php) uses $listing_sql which is built in index.php. But if you look at the queries mentioned in the post I refer to in the previous post they look for p.products_status = '1'. :blink:

Share this post


Link to post
Share on other sites
The product listing (includes/modules/product_listing.php) uses $listing_sql which is built in index.php. But if you look at the queries mentioned in the post I refer to in the previous post they look for p.products_status = '1'. :blink:

 

 

Great! Thank you! I did have that bit of code in all of the queries, so I have no idea why it wasn't hiding products with a status of 0 - but I did manage to fix my dilemma. I am running Master Products (as well as several other contrib's at the moment) and wanted to only show the masters on the product_listing page - turned out that I need to add the " and p.products_master_status = '1' " bit of code into each of the queries and now it works like a charm :thumbsup:

 

Tracy


~Tracy
 

Share this post


Link to post
Share on other sites

wow, feeling kind of punch-drunk. started a manual install on a heavily modded and template overlayed shop about 7 hours ago, and can finally see the light!! JanZ, thanks for the great contrib and the excellent documentation, would never have been possible otherwise. :thumbsup:

 

so the final hurdle, when i use a manufacturer filter, there are duplicate product listings if i have a 'special' placed in both the wholesale and retail groups. the duplicate product displays the same [correct] price for the appropriate login [retail or wholesale], but i'm not sure why the duplication. seems to have been a similar issue posted here, but not close enough.

 

i was having the same issue on the product listing page, but i updated index.php based on a combination of the available bugfix contributions (1054 error & v4.1.2), which seemed to resolve that problem. i'm thinking this is related, but i just can't get there. any assistance would be much appreciated - i know i won't sleep tonight until i get this figured out. :blink:

 

here's the section of index.php where i'm thinking the problem lies:

// show the products of a specified manufacturer
if (isset($HTTP_GET_VARS['manufacturers_id'])) {
if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only a specific category
$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 . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_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)$HTTP_GET_VARS['filter_id'] . "'";
} else {
// We show them all
$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) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
}
} else {
// show the products in a given categorie
if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only specific catgeory
$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 . " s on p.products_id = s.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 . "'";
} else {
// We show them all
$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 . "'";

Share this post


Link to post
Share on other sites
wow, feeling kind of punch-drunk. started a manual install on a heavily modded and template overlayed shop about 7 hours ago, and can finally see the light!! JanZ, thanks for the great contrib and the excellent documentation, would never have been possible otherwise. :thumbsup:

 

see, punch-drunk - found an earlier reply here that resolved. :blush:

Share this post


Link to post
Share on other sites

JanZ, how critical is this line of code in index.php? Including it greatly increase the load time of any page with product listed; however, once the customer logs in, everything seems to return to normal.

 

Can you maybe explain what won't work if not included? Commenting out the code doesn't seem to cause anything to 'break', but thought I'd get your input first. Thanks.

 

 

// BOF Separate Pricing Per Customer
  if(!tep_session_is_registered('sppc_customer_group_id')) {
 $customer_group_id = '0';
 } else {
  $customer_group_id = $sppc_customer_group_id;
  }
  // this will build the table with specials prices for the retail group or update it if needed
  // this function should have been added to includes/functions/database.php
  if ($customer_group_id == '0') {
  tep_db_check_age_specials_retail_table();
  }
  $status_product_prices_table = false;
  $status_need_to_get_prices = false;

  // find out if sorting by price has been requested
  if ( (isset($HTTP_GET_VARS['sort'])) && (ereg('[1-8][ad]', $HTTP_GET_VARS['sort'])) && (substr($HTTP_GET_VARS['sort'], 0, 1) <= sizeof($column_list)) && $customer_group_id != '0' ){
$_sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1);
if ($column_list[$_sort_col-1] == 'PRODUCT_LIST_PRICE') {
  $status_need_to_get_prices = true;
  }
  }

  if ($status_need_to_get_prices == true && $customer_group_id != '0') {
  $product_prices_table = TABLE_PRODUCTS_GROUP_PRICES.$customer_group_id;
  // the table with product prices for a particular customer group is re-built only a number of times per hour
  // (setting in /includes/database_tables.php called MAXIMUM_DELAY_UPDATE_PG_PRICES_TABLE, in minutes)
  // to trigger the update the next function is called (new function that should have been
  // added to includes/functions/database.php)
  tep_db_check_age_products_group_prices_cg_table($customer_group_id);
  $status_product_prices_table = true;

  } // end if ($status_need_to_get_prices == true && $customer_group_id != '0')
// EOF Separate Pricing Per Customer

Share this post


Link to post
Share on other sites
JanZ, how critical is this line of code in index.php? Including it greatly increase the load time of any page with product listed; however, once the customer logs in, everything seems to return to normal.

 

Can you maybe explain what won't work if not included? Commenting out the code doesn't seem to cause anything to 'break', but thought I'd get your input first. Thanks.

Nothing breaks really, not immediately anyway. What those lines do is to check whether the tables with products prices (including special prices) need to be updated and if so update them (mentioned in the comments). If you comment it out those tables will not get refreshed with new prices. They are only used (and triggered by the request for) for sorting by price in the product listing table. I suppose it could be rebuilt every hour using a cron job (which might not be available to everone) calling a script that calls that function.

 

I can't imagine why the tep_db_check_age_specials_retail_table(); would slow things down so much but I guess it might when you have a lot of products and does in your case.

Share this post


Link to post
Share on other sites

JanZ, thanks for the reply, and please excuse my ignorance. I read those notes in your comments, but wasn't [and still not] really clear on what that means.

 

If you comment it out those tables will not get refreshed with new prices.

This doesn't sound good! So there's a chance the database doesn't get updated when I modify price through the admin catalog? Or does this mean that, say a user happens to be logged in for a period of a few hours, if I modify the price while that user is still in the same session, the price won't get updated? I'm just confused because I've done a fair bit of testing, and turning a special on/off seems to update right away - even without the code. Also added/removed special pricing and sorted by price (no refresh) under both the wholesale and retail logins - seems the correct price gets updated upon sort.

 

By no means challenging your judgement, just want to make sure I understand the risks involved. Of course, correct pricing is way more important than a few seconds load time, but if I can have my cake and eat it too... :thumbsup:

 

By the way, the SPPC install is on my local dev environment, with only 5 test products. Thinking the code probably just isn't jiving with one of the other contributions installed [and there are many].

Share this post


Link to post
Share on other sites

Hi,

 

I am using this contrib and it's working very well! However, for my 900 products I wasn't able to bulk upload my wholesale prices with Easy Populate (something I am trying to resolve seperately).

 

Quick question...is it possible with MySQL to copy my retail prices to wholesale? I just want to get some prices in there until I get Easy Populate worked out.

 

I don't know much about MySQL, so any tips would have to be pretty straightforward :blush:

 

thanks for any help!

Share this post


Link to post
Share on other sites
I just want to get some prices in there until I get Easy Populate worked out.

 

Hi,

 

I use Easy Populate woith SPPC, it's work very well, you have to do some change in Easy Populate. Do the following :

 

Open admin/easypopulate.php and search the following lines :

 

// uncomment the customer_price and customer_group to support multi-price per product contrib
$filelayout = array(
'v_products_model' => $iii++,
'v_products_price' => $iii++,
'v_products_quantity' => $iii++,
'v_customer_price_1' => $iii++,
'v_customer_group_id_1' => $iii++,
'v_customer_price_2' => $iii++,
'v_customer_group_id_2' => $iii++,
'v_customer_price_3' => $iii++,
'v_customer_group_id_3' => $iii++,
'v_customer_price_4' => $iii++,
'v_customer_group_id_4' => $iii++,


These line will be commented out. For each customer group you have, uncomment one of the lines... If you have normal prices, and two another price like me, uncomment group id #1 and #2 and #3.

After that, a little lower, you can find the text file about sql data. It looks like this :

CODE
// and insert the new record
if ($v_customer_price_1 != ''){
$result = tep_db_query('
INSERT INTO
'.TABLE_PRODUCTS_GROUPS.'
VALUES
(
' . $v_customer_group_id_1 . ',
' . $v_customer_price_1 . ',
' . $v_products_id . ',
' . $v_products_price .'
)'
);
}
if ($v_customer_price_2 != ''){
$result = tep_db_query('
INSERT INTO
'.TABLE_PRODUCTS_GROUPS.'
VALUES
(
' . $v_customer_group_id_2 . ',
' . $v_customer_price_2 . ',
' . $v_products_id . ',
' . $v_products_price . '
)'
);
}
if ($v_customer_price_3 != ''){
$result = tep_db_query('
INSERT INTO
'.TABLE_PRODUCTS_GROUPS.'
VALUES
(
' . $v_customer_group_id_3 . ',
' . $v_customer_price_3 . ',
' . $v_products_id . ',
' . $v_products_price . '
)'
);
}
if ($v_customer_price_4 != ''){
$result = tep_db_query('
INSERT INTO
'.TABLE_PRODUCTS_GROUPS.'
VALUES
(
' . $v_customer_group_id_4 . ',
' . $v_customer_price_4 . ',
' . $v_products_id . ',
' . $v_products_price . '
)'
);
}

}

 

The line ' . $v_products_price . ' is not needed. Erase that whole line. I don't know why commenting the line won't work...but it won't.

 

Anyway, the line above the one you just erased has a comma at the end of it. Erase the comma so that sql knows to start another line.

 

That's it.

 

Just upload your excel file in admin panel via Easy Populate and Bingo.

 

Good luck... :thumbsup:


John

--------------------

osCommerce 2.3.4 Bootstrap Edge

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×