Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Seperate Pricing Per Customer v3.5


scendent

Recommended Posts

Pricing per x quantity is another contribution. The one I briefly looked at, added columns to the table products (the table where products_price is found). The prices for customers_groups are in another table: products_groups.....

 

To have the functionality of pricing per x quantity added to this one (I believe there are several different ones) will probably mean a substantial rewrite of the code - not looking forward to that.

Link to comment
Share on other sites

Pricing per x quantity is another contribution. The one I briefly looked at, added columns to the table products (the table where products_price is found). The prices for customers_groups are in another table: products_groups.....

 

To have the functionality of pricing per x quantity added to this one (I believe there are several different ones) will probably mean a substantial rewrite of the code - not looking forward to that.

 

Thanks for your fast response!

 

I may have made it sound more complicated than it really is... the wholesale folks buy by the case, but not all the cases have the same quantity in them. So I need a way to just display some special text that only wholesale customers would see, that tells them how many is in a case... but it would just be one price per customer group, not actually adding x quantities. Does that clarification make sense?

 

Thanks!

Janet

Link to comment
Share on other sites

Installation issue with my Categories box, otherwise the software is great! I keep getting this error.

 

1064 - You have an error in your SQL syntax near '-_db' at line 1

 

show table status from xxxx_-_db (xxx is the name of db)

 

[TEP STOP]

 

the url it is trying to access is http://www.xaudiocovers.com/catalog/index.php?cPath=25

 

A little background, i did the manual install and had a few hicups along the way but otherwise it went smooth, long but smooth. But since, I all ready had my Linkpoint Contrib and mulitple product images working sufficiently i decided to go the manual route.

 

With my lack of knowlege on what is happening without having a specific location to look for I am at a loss.

 

This is a live site and I need to get it fixed ASAP, otherwise I will be forced to roll it back.

 

Any help will be appreciated.

 

Jeff

Link to comment
Share on other sites

1064 - You have an error in your SQL syntax near '-_db' at line 1

 

show table status from xxxx_-_db (xxx is the name of db)

According to the mysql website this statement was added in MySQL 3.23. Could it be that you are working with a really old mysql version? If so, that means big trouble, because this contribution depends on a few features that were added to the 4 series of MySQL, which I assumed would by now be generally used.

SHOW TABLE STATUS works likes SHOW TABLE, but provides a lot  of information about each table. You can also get this list using the  mysqlshow --status db_name command. This statement was added in MySQL 3.23.
Link to comment
Share on other sites

Well i just verified that i am using the following MySql from the site admin panel

 

Welcome to phpMyAdmin 2.5.6

 

 

MySQL 3.23.58 running on localhost as jstumpf@localhost. Now where should i look? Thanks for the help. I have no idea or even concept of how to start to try and debug this error.

 

I only have two contribs installed. Linkpoint and Big Images

Link to comment
Share on other sites

Well i just verified that i am using the following MySql from the site admin panel

 

Welcome to phpMyAdmin 2.5.6

 

 

MySQL 3.23.58 running on localhost as jstumpf@localhost.

 

I ran the following query in PhPAdmin and got these results. same as my previous post, notice that the error is being caught on the hyphenated character.

 

Database xaudiocovers_com_-_db running on localhost

 

 

Error

 

 

SQL-query?:?

 

 

SHOW TABLE STATUS FROM xaudiocovers_com_ - _db

 

 

MySQL said:

 

#1064 - You have an error in your SQL syntax near '- _db' at line 1

 

[Documentation]???[back]

 

Now it seems to me that the way my hosting company names it's databases might be causing the problem. Is the naming convention wrong for the DB?

Link to comment
Share on other sites

Janet,

So I need a way to just display some special text that only wholesale customers would see, that tells them how many is in a case... but it would just be one price per customer group, not actually adding x quantities.
I think that wouldn't be too hard to do. For that you would have to add php to the product description (like <?php if ($customer_group_id != '0') { echo 'Buy by case of 8'; } ?> ) and then make sure that the product description is eval'ed before echo'ed. This has been discussed a number of times on the forums:

 

http://www.oscommerce.com/forums/index.php?showtopic=36462

http://www.oscommerce.com/forums/index.php?showtopic=48806

http://www.oscommerce.com/forums/index.php?showtopic=70099

http://www.oscommerce.com/forums/index.php?showtopic=111922

http://www.oscommerce.com/forums/index.php?showtopic=132397

 

Instead of an echo you could also include a file that contains the html/text that you want to show the wholesale customers.

Link to comment
Share on other sites

Greetings All!

 

OK, I installed this contribution early last week, found that first bug, fixed it... but then I became extremely busy, so I haven't had time to look into this issue until now:

 

With regards to the shopping cart box that you see on the upper right... whenever I add a product there now, the product shows up, but no price, and the total shows up as $0.00 all the time.

 

I'm assuming that this isn't the way it's suppossed to be? I mean, the $0.00 total is an obvious problem with my install. But I was wondering if it's suppossed to show a price next to each item as well, or is that not displayed and all we're suppossed to see is a total?

 

 

Thanks!

 

-= Dave =-

Link to comment
Share on other sites

Janet,

I think that wouldn't be too hard to do. For that you would  have to add php to the product description (like <?php if ($customer_group_id != '0') { echo 'Buy by case of 8'; } ?> ) and then make sure that the product description is eval'ed before echo'ed. This has been discussed a number of times on the forums:

 

Instead of an echo you could also include a file that contains the html/text that you want to show the wholesale customers.

 

Excellent! Thank you for the leads. I'll check them out and see what I can accomplish with this contribution. It sounds perfect!

 

Janet

Link to comment
Share on other sites

JanZ,

 

Thanks i just tried that approach on my PHPAdmin and it works great, now all i have to do is make the mod in database.php if adding the back ticks do not work in the congif file first.

 

Love this stuff, many thanks.

 

According to http://www.unitedforums.co.uk/vb/archive/i....php/t-675.html it should help if you put backticks around the table name (personally I don't even know where they are on my keyboard ;) ). The "problem" code is in the new functions in: includes/functions/database.php

Link to comment
Share on other sites

Dave,

I'm assuming that this isn't the way it's suppossed to be? I mean, the $0.00 total is an obvious problem with my install. But I was wondering if it's suppossed to show a price next to each item as well, or is that not displayed and all we're suppossed to see is a total?
No, you shouldn't see $0.00 as a total, but you do are supposed only to see a total.

 

With SPPC there is no deviation from the standard behaviour of that box (at least not intended ;) ).

Link to comment
Share on other sites

Dave,

No, you shouldn't see $0.00 as a total, but you do are supposed only to see a total.

 

With SPPC there is no deviation from the standard behaviour of that box (at least not intended ;) ).

 

OK, thanks, I found the problem!

 

-= Dave =-

Link to comment
Share on other sites

JanZ,

 

My site is up and running now thanks to your input. I marked the code mod for other unfortunate souls who may have their database names embedded with Hyphens (hosting company derived).

 

I can now test the Contrib, will keep everyone posted.

 

 

<?php
/*
 $Id: database.php,v 1.21 2003/06/09 21:21:59 hpdl Exp $
 adapted for Separate Pricing Per Customer 2005/02/06
 1.0 BOF Wrap DB_DATABASE with Back Ticks, Fixes Hyphens in Database Name
 $result = tep_db_query("show table status from `" . DB_DATABASE . "`");
  

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/

  // BOF Separate Pricing Per Customer, adapted from sample code in user comments on
 // http://www.php.net/manual/en/function.mysql-list-tables.php
 function tep_db_table_exists($table, $link = 'db_link') {
   /// 1.0 BOF Wrap DB_DATABASE with Back Ticks, Fixes Hyphens in Database Name
  $result = tep_db_query("show table status from `" . DB_DATABASE . "`");
  /// 1.0 EOF
  while ($list_tables = tep_db_fetch_array($result)) {
  if ($list_tables['Name'] == $table) {
   return true;
  }
  }
  return false;
 }

 function tep_db_check_age_specials_retail_table() {
   /// 1.0 BOF Wrap DB_DATABASE with Back Ticks, Fixes Hyphens in Database Name
  $result = tep_db_query("show table status from `" . DB_DATABASE . "`");
  /// 1.0 EOF

  $last_update_table_specials = "2000-01-01 12:00:00";
  $table_srp_exists = false;
  while ($list_tables = tep_db_fetch_array($result)) {
  if ($list_tables['Name'] == TABLE_SPECIALS_RETAIL_PRICES) {
  $table_srp_exists = true;
  $last_update_table_srp = $list_tables['Update_time'];
  }
  if ($list_tables['Name'] == TABLE_SPECIALS) {
  $last_update_table_specials = $list_tables['Update_time'];
  }
  } // end while

  if(!$table_srp_exists || ($last_update_table_specials > $last_update_table_srp)) {
     if ($table_srp_exists) {
      $query1 = "truncate " . TABLE_SPECIALS_RETAIL_PRICES . "";
      if (tep_db_query($query1)) {
  $query2 = "insert into " . TABLE_SPECIALS_RETAIL_PRICES . " select s.products_id, s.specials_new_products_price, s.status, s.customers_group_id from " . TABLE_SPECIALS . " s where s.customers_group_id = '0'";
  $result =  tep_db_query($query2);
  }
     } else { // table specials_retail_prices does not exist
      $query1 = "create table " . TABLE_SPECIALS_RETAIL_PRICES . " (products_id int NOT NULL default '0', specials_new_products_price decimal(15,4) NOT NULL default '0.0000', status tinyint, customers_group_id smallint, primary key (products_id) )";
      $query2 = "insert into " . TABLE_SPECIALS_RETAIL_PRICES . " select s.products_id, s.specials_new_products_price, s.status, s.customers_group_id from " . TABLE_SPECIALS . " s where s.customers_group_id = '0'";
      if( tep_db_query($query1) && tep_db_query($query2) ) {
 ; // execution succesfull
     }
     } // end else
  } // end if(!$table_srp_exists || ($last_update_table_specials....
 }

 function tep_db_check_age_products_group_prices_cg_table($customer_group_id) {
 /// 1.0 BOF Wrap DB_DATABASE with Back Ticks, Fixes Hyphens in Database Name
  $result = tep_db_query("show table status from `" . DB_DATABASE . "`");
 /// 1.0 EOF

Link to comment
Share on other sites

Well I have it installed and the hardest part was removing reference to SPPC 3.5 :'(

But I have a weird problem. When I go to custmers in admin it showes I have none. I have since set up fake custmers and nothing. I can see the orders and custmer info in the order screen but nothing in the custmers section????....I'm stuck :(

Link to comment
Share on other sites

I am looking for help integrating this great contrib with two others:

 

Printable Catalog

My Webpage

 

Quick Price Updates

My Webpage

 

Wondered if anyone installing this contrib had modified these two to work along side. I've looked through both and am just too much of a newbie to figure out how and where to add code for the right queries from SPPC...

Brian Neuman

Link to comment
Share on other sites

Has someones installed this mod to make it work with OscMax package?

 

If so, I would like to get in touch, I am having a few problems with (most of the time) the BTS template mod that isn't always obvious for implementing the new SPPC module and have a few questions.

If there isn't anybody around installing this mod on oscmax, maybe someone is trying to install it with the BTS template mod?

 

Thanx.

 

I just finished the install and am debugging it now. The "What's New" box is broken but I've seen that others are having the same problem.

 

Most of the files in catalog/ have to be broken into the two pieces that BTS is set up for - one in catalog/ and the other half in catalog/templates/templatename/content/. I've got all of the files modified and broken down into the proper directory structure and would be happy to email it to you to use or at least reference if you'd like. Send me an email if so...

Brian Neuman

Link to comment
Share on other sites

Well I have it installed and the hardest part was removing reference to SPPC 3.5  :'(

But I have a weird problem. When I go to custmers in admin it showes I have none. I have since set up fake custmers and nothing. I can see the orders and custmer info in the order screen but nothing in the custmers section????....I'm stuck :(

 

 

Well after reading the post a quick bug fix took care of the problem :thumbsup:

 

 

Now for the last question ( I hope) I have scrolling specials installed does any one know how to incorporate that box into SPPC? right now the scrolling special shows all the curent specials with all the different prices??

Any help would be great.

Link to comment
Share on other sites

I have scrolling specials installed does any one know how to incorporate that box into  SPPC? right now the scrolling special shows all the curent specials with all the different prices??

You will have to change the mysql query of that mod, no way around that. It should take the customers_group_id into account. Look at includes/boxes/specials.php from SPPC 4 for inspiration ;)
Link to comment
Share on other sites

You will have to change the mysql query of that mod, no way around that. It should take the customers_group_id into account. Look at includes/boxes/specials.php from SPPC 4 for inspiration ;)

Ok thanks a lot I'll give it a try. :D

BTW I also have featured products. Any suggestions on that?

Link to comment
Share on other sites

BTW I also have featured products.
Sorry, don't know that contribution. Should be solvable though, through changing the mysql query. Probably something similar as the new products box I suppose.
Link to comment
Share on other sites

Sorry, don't know that contribution. Should be solvable though, through changing the mysql query. Probably something similar as the new products box I suppose.

 

 

Hi everyone, this is a very nice contrubtion, well done!

Can anyone point me in the direction to change scrolling special box and Featured products to work with SPPC? I am lost and really don't understand what part of the code is mysql querry. if someone can give an example or if needed I can attache the two files.

Thanks in advance :thumbsup:

Link to comment
Share on other sites

I am lost and really don't  understand what part of the code is mysql querry. if someone can give an example or if needed I can attache the two files.

Thanks in advance :thumbsup:

 

look for tep_db_query thats the query command.

the stuff inside the ( )'s is what the query is suposed to "do"

 

basically look for the word query in your file.

that's the area you will need to modify.

Link to comment
Share on other sites

Can anyone point me in the direction to change scrolling special box
As suspected the code for the query is really very similar. Just changing a few names and chopping of: limit " . MAX_RANDOM_SELECT_SPECIALS) is enough to get you going:

// next line original code
/*  $rp_query = tep_db_query("select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and p.products_id = s.products_id and pd.products_id = s.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' order by s.specials_date_added desc"); */

 if(!tep_session_is_registered('sppc_customer_group_id')) { 
 $customer_group_id = '0';
 } else {
  $customer_group_id = $sppc_customer_group_id;
 }

 if ($customer_group_id == '0')  {
     $rp_query = tep_db_query("select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and p.products_id = s.products_id and pd.products_id = s.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' and s.customers_group_id = '0' order by s.specials_date_added desc");
 } else { // $sppc_customer_group_id is in the session variables, so must be set
     $rp_query = tep_db_query("select p.products_id, pd.products_name, IF(pg.customers_group_price IS NOT NULL,pg.customers_group_price, p.products_price) as products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s LEFT JOIN " . TABLE_PRODUCTS_GROUPS . " pg using (products_id, customers_group_id) where p.products_status = '1' and p.products_id = s.products_id and pd.products_id = s.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' and s.customers_group_id= '".$customer_group_id."' order by s.specials_date_added desc");
   }

Link to comment
Share on other sites

As suspected the code for the query is really very similar. Just changing a few names and chopping of: limit " . MAX_RANDOM_SELECT_SPECIALS) is enough to get you going:

// next line original code
/*  $rp_query = tep_db_query("select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and p.products_id = s.products_id and pd.products_id = s.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' order by s.specials_date_added desc"); */

 if(!tep_session_is_registered('sppc_customer_group_id')) { 
 $customer_group_id = '0';
 } else {
  $customer_group_id = $sppc_customer_group_id;
 }

 if ($customer_group_id == '0')  {
     $rp_query = tep_db_query("select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and p.products_id = s.products_id and pd.products_id = s.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' and s.customers_group_id = '0' order by s.specials_date_added desc");
 } else { // $sppc_customer_group_id is in the session variables, so must be set
     $rp_query = tep_db_query("select p.products_id, pd.products_name, IF(pg.customers_group_price IS NOT NULL,pg.customers_group_price, p.products_price) as products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s LEFT JOIN " . TABLE_PRODUCTS_GROUPS . " pg using (products_id, customers_group_id) where p.products_status = '1' and p.products_id = s.products_id and pd.products_id = s.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' and s.customers_group_id= '".$customer_group_id."' order by s.specials_date_added desc");
   }

 

 

WOW!! Thanks it works! I should be able to figure the featured product and I will post it when it is done.

 

Now.....one last problem. Retail is not the default group. I think the problem is this part of the install I didn't understand:

INSERT INTO customers_groups VALUES('0','Retail','1','0');

I don't know how to do it. I have the custmers_group table but I can't figure out how to add values to a table >_< . Thanks for the help! :D

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