Jump to content
scendent

Seperate Pricing Per Customer v3.5

Recommended Posts

Hi all

 

Love the contribution however I have a problem with only retail group being visible and I am unable to add another group eg wholesale. When I try to add wholesale (say with a new customer) I get this error.

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/*****public_html/catalog/admin/includes/functions/database.php on line 99.

 

Am I doing something wrong?

 

Am I blind......I would really appreciate some help as I would love to have other groups. I am not really sure where to start to look to correct this.

 

Thanking you in advance

Trap

Share this post


Link to post
Share on other sites
Hi all

 

Love the contribution however I have a problem with only retail group being visible and I am unable to add another group eg wholesale.  When I try to add wholesale (say with a new customer) I get this error. 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/*****public_html/catalog/admin/includes/functions/database.php on line 99.

 

Am I doing something wrong?

 

Am I blind......I would really appreciate some help as I would love to have other groups.  I am not really sure where to start to look to correct this.

 

Thanking you in advance

Trap

 

 

You are doing nothing wrong, the version of SPPC that is in the contribution area now is old and has many many bugs.

 

Some of the great members here have redone this script and are testing it now, look for its release in the next few weeks.

Share this post


Link to post
Share on other sites

Thanks for you quick reply. Look forward to the new release. You guys are doing a great job.

 

I took a gamble and reinstalled my database to before I used easypopulate, now I can add the new groups. I set up easypopulate to recognise the groups, however the separate still not showing in excel spreadsheet the separate prices? Should it show Up. Please excuse my ignorance.

 

Could this be the problem? Does this contribution and or the new release not play well with easypopulate? Therefore when I use easypopoulate it wipes out (so to speak) my groups and ability to add groups?

 

Look forward to you response.

 

Cheers

Trap

Share this post


Link to post
Share on other sites

Mujina,

SaleMaker - All products marked down

 

Would you mind to have a look at it and tell us if it could be compatible with the new version of SPPC???

I looked at the code for getting a special price (general.php) and boy that is something totally different. I'm sure this will not go well with SPPC, where you have to take into account the prices in three different tables for several customers_groups. On cursory inspection I think it will take a major effort to port this functionality to SPPC.

Share this post


Link to post
Share on other sites
It wouldnt really be a mod, I dont think.  It would be more like and if statement.

 

if($usergroup == 'wholesale') {

show the NET payment module

} else {

dont show

}

 

;)

 

Thanks for your reply.

 

So where you have 'show the NET payment module' would you actually put the text from the contribution there? If you don't mind, could you possibley PM me as this has little to do w/ the SPPC, I just thought that people using SPPC might have a need for it.

 

Thanks again,

 

Nate

Share this post


Link to post
Share on other sites

Just an update.....

 

I think Jan & I are happy with the final code for the New Separate Price contribution so I've started in on documenting it thoroughly :D

 

I expect to post it in the next few days so it won't be long now :) When I do I'll post here with a complete description and pictures so that people can see how it works, what it fixes, the new features and more....

 

Barring no last minute revisions it should be out soon :thumbsup:


Best & Thanks;
Marvin
----------------------
osCommerce 2.3.3.4 

Share this post


Link to post
Share on other sites
Just an update.....

 

I think Jan & I are happy with the final code for the New Separate Price contribution so I've started in on documenting it thoroughly  :D

 

I expect to post it in the next few days so it won't be long now  :)  When I do I'll post here with a complete description and pictures so that people can see how it works, what it fixes, the new features and more....

 

Barring no last minute revisions it should be out soon  :thumbsup:

 

Woo hoo!! I can't wait. that's great news Marvin.

Share this post


Link to post
Share on other sites
Just an update.....

 

I think Jan & I are happy with the final code for the New Separate Price contribution so I've started in on documenting it thoroughly  :D

 

I expect to post it in the next few days so it won't be long now  :)  When I do I'll post here with a complete description and pictures so that people can see how it works, what it fixes, the new features and more....

 

Barring no last minute revisions it should be out soon  :thumbsup:

 

 

(Drool..... Drool..... ) Good news! Fabulous !

 

 

-= Dave =-

Share this post


Link to post
Share on other sites

RE: salemaker - global markdown of product.

 

Mujina,

I looked at the code for getting a special price (general.php) and boy that is something totally different. I'm sure this will not go well with SPPC, where you have to take into account the prices in three different tables for several customers_groups. On cursory inspection I think it will take a major effort to port this functionality to SPPC.

 

I too have a need to periodically reduce the prices of all my stock. Is there a way that anyone can suggest that I could do it whilst stil retaining separate pricing for different categories of customer?

 

One method that does spring to mind is to export the database to excel and make modifications there and then upload it back again. But being on a mac and using a different date format to the one used on the server, the last time I tried the spreadsheet approach I created an awful mess.

 

any other suggestions of contributions that would work with the SPPC contribution?

Share this post


Link to post
Share on other sites
any other suggestions of contributions that would work with the SPPC contribution?
What is wrong with running an sql query like mentioned e.g. in the general support section?

 

The example there mentions:

UPDATE `products`
SET `products_price` = ROUND(`products_price` + ((`products_price` / 100) * percentage), 2);

The "percentage" can also be a negative figure like -10.

 

I would suggest "dumping" the products_groups table (the new one will have one column less: products_price is not in there any more, no need for it), restore the table locally and try it out. If you only use it to change the price for a certain customer group something like this might work:

UPDATE products_groups
SET customers_group_price = ROUND(customers_group_price + ((customers_group_price / 100) * percentage), 2) where customers_group_id = '1';

 

Once you got it working, you can either dump the new table again and restore it on the site with phpMyAdmin or execute the query you now know is working correctly directly on the mysql table on the site.

 

If you work on Mac OSX, mysql is easily installed and YourSQL is a very nice alternative for phpMyAdmin.

 

I never tried EasyPopulate, but I suspect that due to the way the prices for the customers_groups are stored in the table, Excel or OpenOffice can't due much with it.

Share this post


Link to post
Share on other sites

Can someone working on the new release answer if this issue will be addressed?

 

I have the Separate Pricing per Customer v3.5 mod and the Printable Catalog mod installed on my osc.

 

I'm setting up wholesale pricing for a customer and would like for them to be able to use the printable catalog for easy product selection but it does NOT display the pricing set up in the customer's group when they are logged in.

 

How do you get the printable catalog to display the "Customer Group" pricing if the customer is logged in?


Brian Neuman

Share this post


Link to post
Share on other sites
Can someone working on the new release answer if this issue will be addressed?

 

I have the Separate Pricing per Customer v3.5 mod and the Printable Catalog mod installed on my osc.

 

I'm setting up wholesale pricing for a customer and would like for them to be able to use the printable catalog for easy product selection but it does NOT display the pricing set up in the customer's group when they are logged in.

 

How do you get the printable catalog to display the "Customer Group" pricing if the customer is logged in?

 

 

Add some custom code in it that would display the correct price.

Share this post


Link to post
Share on other sites

I checked today and there are close to 3,000 contributions for osC. I don't think you expect us to make sure SPPC will work with all of them.

 

SPPC checks the customer group id of the logged in person and changes the prices accordingly by pulling prices from the table specials and the table products_groups where customer_group_id = $sppc_customer_group_id.

 

If you re-write Printable Catalog to do the same I guess it should work, as Dennis implied. Both contributions are Open Source so you are free to change them to your liking.

Share this post


Link to post
Share on other sites
I checked today and there are close to 3,000 contributions for osC. I don't think you expect us to make sure SPPC will work with all of them.

 

SPPC checks the customer group id of the logged in person and changes the prices accordingly by pulling prices from the table specials and the table products_groups where customer_group_id = $sppc_customer_group_id.

 

If you re-write Printable Catalog to do the same I guess it should work, as Dennis implied. Both contributions are Open Source so you are free to change them to your liking.

 

I don't mean to seem ungrateful or too demanding - you guys writing these contributions are awesome and the time and effort you put into them is certainly not lost on me.

 

I'm just a hopeless newbie learning as I go and was looking for some help on the code in the printable catalog to modify. I think the pertinent section is this:

 

$products_query_raw = "select distinct p.products_id, p.products_image, p.products_model, p.products_quantity, p.products_weight, pd.products_name, p.manufacturers_id, p.products_price, p.products_date_added, 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, m.manufacturers_name, pd.products_description, cd.categories_name from  " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_CATEGORIES_DESCRIPTION . " cd on p2c.categories_id = cd.categories_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . $languages_id . "' and cd.language_id = '" . $languages_id . "' order by $order";
 $products_query = tep_db_query($products_query_raw);

 

I just can't figure out what to change to get it to check for the customer group price and then insert it into the final_price variable.


Brian Neuman

Share this post


Link to post
Share on other sites

Do you have sppc installed? IF so, steal the code from orders.php. There should be a customers_group_query in there that will query the right price, then there should be some

 

if customer group == whatever

$finalprice = this;

else

$finalprice = regprice;

 

 

You should get the idea. The code is already done for you, you just need to steal it and mod it to work for what you want.

 

Dennis

Share this post


Link to post
Share on other sites
Do you have sppc installed?  IF so, steal the code from orders.php.  There should be a customers_group_query in there that will query the right price, then there should be some

 

if customer group == whatever

$finalprice = this;

else

$finalprice = regprice;

You should get the idea.  The code is already done for you, you just need to steal it and mod it to work for what you want.

 

Dennis

 

Thanks - I see the code in order.php but it appears the two files do the query differently. In order.php, the customers_group_price is loaded within a while statement. The code I posted above doesn't use a while statement and I'm unsure of how to match up the values in the array the way they do with $index in order.php...


Brian Neuman

Share this post


Link to post
Share on other sites

A while statement is just a loop. It queries some information and while that informatin exists, it repeats the query over and over for each case. You just need to setup your query's correctly and then setup a variable for the price like i showed you. Then just echo the variable where the price was echo'ed before.

 

And your printable catalog should have some kind of while statment or loop in it, to print out each row after row.

 

Dennis

Share this post


Link to post
Share on other sites

Well, here it is - the long awaited Separate Price Per Customer Version 4.0 !!!

 

This is a much needed complete revision of the Separate Pricing Per Customer contribution originally created by James Warner and modified by Scott Everson et al.

 

This new version not only addresses the plethora of bugs discussed in this thread but also addresses many bugs that went unnoticed. Enhancements were added as complimentary features and changes were made to make the contribution more user-friendly and seamless in it's osCommerce integration. In short, it's a more mature, feature filled, logical and well thought out contribution that works!

 

What it Does

 

Separate Pricing Per Customer 4.0 allows you to have pre-defined user groups with different pricing for each group. This is useful for sites that need to Retail & Wholesale products or just want to have different pricing levels for different customers. When a client logs into the site they are displayed prices based on the group they belong to.

 

How it Works - Overview

 

You define the customer groups in osC's Admin. Retail is installed by default, after that you can create groups such as Wholesale, Volume Purchase Agreement, Level 5 or whatever you like.

 

admingrouplist.gif

 

When you enter (or edit) a product in the Catalog you are then presented with pricing boxes for each of the groups you have defined.

 

editproduct.gif

 

If an unregistered user browses the site they see Retail pricing. If they register without giving a tax number during registration, all prices shown are Retail as this is the default group. If, during registration, they fill out the tax number box......

 

 

createaccount.gif

 

then the site's administrator is sent an email notifying them that a user has requested a Wholesale account and needs to be authorized. Until authorized they are members of the Retail group.

 

In the Customers section of the osC Admin, the list will show a red light next to each user requiring authorization.

 

customerlist.gif

 

You can then edit the customer's information and assign which group they belong to. If they are approved for a Wholesale account, then the next time they log into the site they will see the Wholesale pricing for products.

 

editcustomer.gif

 

If you approve them for a Wholesale account, then the next time they log into the site they will see the Wholesale pricing for products.

 

Taxes can also be turned on or off per customer group. This way, customers in the Retail Group can be charged taxes and customers in other groups, such as Wholesale, won't be charged tax because they have a tax number. In addition, you can also choose to display prices with or without tax on a customer group basis.

 

newgroup.gif

 

 

Furthermore, specials can be defined on a customer group basis....

 

createspecial.gif

 

...and then show up in a list when you're done.

 

specialslist.gif

 

To make testing easier for the Site Administrator you can now log-in and choose what group you want to be a part of. This is used for tesing and only the Admin can do it.

 

adminlogin.gif

 

Some of the bug fixes include;

  • Proper pricing is now displayed site-wide in all pages (reviews, new products etc.)
  • Specials work properly whether they are defined by percentage, date or price
  • All Search features work properly in the store and show correct prices.
  • and much, much, much more (we forgot all the bugs we fixed!)

Enhancements from Previous Versions

  • Customer groups can be pre-defined via the osC admin regardless of how many customers you have.
  • During registration a customer can enter a Tax Number
  • Site Admin receives email notification if a customer registers with a Tax Number
  • Customer List in the Customers section of the osC Admin shows if they need to be Authorized (tax number)
  • Customer List in the Customers section of the osC Admin shows if they need to be Authorized (tax number)
  • Pricing boxes were moved so that they are now under the default osC Product's Price (Gross) box
  • Customers section of the osC Admin now shows the Customer Group that a customer belongs to
  • Customer List in the Customers section of the osC Admin are sortable by any field
  • Customer Groups in the osC Admin are sortable by Name
  • Taxes can be applied or exempted on a per customer group basis
  • Prices can be displayed in the store with or without tax included on a per customer group basis
  • Site Admin can log in under any of the defined customer groups for checking functionality
  • SQL Removal File included to make removal easier
  • Contribution code was streamlined & optimized making it MUCH faster then the original.

Credits

 

JanZ for re-coding & re-designing practically the whole thing. Originally I contacted Jan and asked if he would be kind enough to fix a few simple bugs. Jan kindly agreed and as he went along he found that the existing contrib needed more & more work.

 

What was supposed to be a quick detour to help someone turned into a month and a half of revamp & re-design. Jan never quit or complained - instead, he not only continued the work but even added new functionality and bug fixes, cheerfully, right up until the very end :)

 

This contrib is based on the foundation that James Warner so extensively laid in the original contribution as well as other people/contribs...

 

hOZONE for the admin/customers_groups.php file from TotalB2B which was the basis for this page in SPPC v4.0, Gary Burton (burt) for the sorting feature used in admin/customers.php and admin/customers_groups.php (customer_sort_admin_v1), Oliver Huebner/Mario Peters for the show tax feature in SPPC, ArtRat/Michael for the tax exempt feature, orginally for B2BSuite, dblake/Dennis for his suggestions of new features and last but not least, myself (Marvin Miller) for Beta testing/design enhancement and documentation of this extensive contrib! (plus the Nifty New!! Call-Out boxes :rolleyes: )

 

The packaged zip file will be uploaded shortly in the contribs section. Considerably more information can be found in the included documentation & files then can be posted here. Enjoy! :)


Best & Thanks;
Marvin
----------------------
osCommerce 2.3.3.4 

Share this post


Link to post
Share on other sites

Hi Marvin!!!

 

Thankkkkkkkkkks!!!! I was about to sleep and I found your post. I just can't realized it has been released. :D The new version seems to be great. Really good work, congratulations!!!!!!!!!

 

I'll install it on tomorrow and I'll post my comments!!!

 

THANKS JAN!!!

 

THANKS MARVIN!!!!


OSC2.2

Share this post


Link to post
Share on other sites
I'll install it on tomorrow and I'll post my comments!!!

 

Thanks very much - Jan did all the work :D I'd appreciate knowing if you are doing a manual install (following the directions for each file) or a bulk file upload.

 

My part has been the install directions so if there are any errors I'd like to hear about them :D


Best & Thanks;
Marvin
----------------------
osCommerce 2.3.3.4 

Share this post


Link to post
Share on other sites

Jan very nice, looked like you took some of those ideas I threw at you ;) This actually almost looks like what I have done, only mine is for salons. Also I didnt' see if you added in the stats on the main admin page... Total Retail, Total Wholesale, or whatever. It's not a necessity, just something to make it even better ;) And if you did do that my appologies as I have not looked over the code.

 

Good Job guys!!!

Share this post


Link to post
Share on other sites
WOW Great!!!!!!!!!!!!!. I'm gonna install it now. But first, do I need to remove reference to SPPC  3.5??? :'(

 

Did you mean you have an older version installed? If your store is stock (other then an older version) you should be able to mow it over with the new files.


Best & Thanks;
Marvin
----------------------
osCommerce 2.3.3.4 

Share this post


Link to post
Share on other sites

I was just about to do the older mod until I saw this. Thsi is awesome. I just have one questionn though. Once I install this will I have the chance to charge different shipping fees for these custom people?

 

What I mena by this is right now if people buy at retail from my store they do not have to pay shipping. But if I am doing wholesale or quantity discounts then I want to make sure I can charge them shipping for the item. Also is it possible to have the discount coupon mod added to this after I create this? I want to be able to send out discount coupons to my retail customers to give them a discount. On certain items later on.

 

Let me know if these are available or what I have to do to make this possible.

 

Thanks

 

Nick

www.preciousmetalbars.com

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

×