Jump to content
scendent

Seperate Pricing Per Customer v3.5

Recommended Posts

LOL I have moved the warning from the address box to the shipping modules box. Along with the customer group drop down menu. So I am assuming I have moved it to the wrong spot.

 

How do I tell where the html begins?

That is where the doctype line begins (around line 300):

	$existing_customers_query = tep_db_query("select customers_group_id, customers_group_name from " . TABLE_CUSTOMERS_GROUPS . " order by customers_group_id ");
// EOF Separate Pricing Per Customer
	$customers = tep_db_fetch_array($customers_query);
	$cInfo = new objectInfo($customers);
}
 }
?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">

Share this post


Link to post
Share on other sites
Try to move the query for the customers group (close to where the HTML of the page admin/customers.php begins) to right before where the results are needed around line 830:

 

ok hang on maybe I miss understood what you said. So I dont really need to find where the html begins, that was said just to help me find the code to move?

 

What I need to do is move it to just before where the results are needed? If this is the case how do I tell where the results are needed, since I am assuming it is still in the wrong spot, still getting the same warning, and still cannot assign to group.

 

Thanks so much for your help I really appreciate it. :)

Share this post


Link to post
Share on other sites
ok hang on maybe I miss understood what you said. So I dont really need to find where the html begins, that was said just to help me find the code to move?

 

What I need to do is move it to just before where the results are needed? If this is the case how do I tell where the results are needed, since I am assuming it is still in the wrong spot, still getting the same warning, and still cannot assign to group.

 

Thanks so much for your help I really appreciate it. :)

 

ok never mind me. When I first noticed problems first thing I did was re-check I had not missed anything on install. Then after help it was still not working I reverted to the original file and re did the install. Still had problems.

 

Thought to myself, ok one more re-do and still have trouble I know its nothing I have done. The third install worked a charm. What are the chances of making the same mistake twice, gee I feel like a dill :blush:

 

Thanks heaps for all the help.

Share this post


Link to post
Share on other sites

I can not get the admin/categories to block a single product. I can get it to block categories.

when you check the retail box in admin/categories then preview then update is says:

 

Hidden from groups: none

 

I am not sure where to check to get this to work.

 

Can someone help please

 

Thanks

Mike

Share this post


Link to post
Share on other sites

hi, I have a question before install this mod, is it possible to set a default group to new costumers based on costumer's country?

 

thank you

Share this post


Link to post
Share on other sites
is it possible to set a default group to new costumers based on costumer's country?

In principle yes, but you have to hard code that in create_account.php. It is not an admin setting or something like that.

Share this post


Link to post
Share on other sites
I can not get the admin/categories to block a single product. I can get it to block categories.

when you check the retail box in admin/categories then preview then update is says:

 

Hidden from groups: none

 

I am not sure where to check to get this to work.

You could start by finding this piece of code and add some echo statements to it and stop execution to see if that went through:

// BOF Separate Price Per Customer, Hide products from groups modification
	  $hide_from_these_groups = '@,';
		 if ( $HTTP_POST_VARS['hide'] ) { // if any of the checkboxes are checked
		foreach($HTTP_POST_VARS['hide'] as $val) {
		$hide_from_these_groups .= tep_db_prepare_input($val).','; 
		} // end foreach
		$hide_from_these_groups = substr($hide_from_these_groups,0,strlen($hide_from_these_groups)-1); // remove last comma
  }
echo '<pre>Hide from these groups is now: ';
echo $hide_from_these_groups;
exit(); // code stops here so you can check if that is OK
// EOF Separate Price Per Customer, Hide products from groups modification

If that is OK you should compare your code a few lines further to see if this is added to the sql_array:

// BOF Separate Price Per Customer, hide for these groups modification
			  'products_hide_from_groups' => $hide_from_these_groups,
// EOF Separate Price Per Customer, hide for these groups modification

If it is not OK you should check if the values for hide (which is an array) are in the POST array. Somewhere in the top:

echo '<pre>';
print_r($HTTP_POST_VARS);
exit;

Share this post


Link to post
Share on other sites

just installed it and I get this message every time I add a product to the shopping cart:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/public_html/test/includes/functions/database.php on line 103

 

 

line 103 is just this:

 

function tep_db_num_rows($db_query) {

return mysql_num_rows($db_query);

}

 

what's wrong? I checked all the modifications and it's ok

Share this post


Link to post
Share on other sites
just installed it and I get this message every time I add a product to the shopping cart:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/public_html/test/includes/functions/database.php on line 103

line 103 is just this:

 

function tep_db_num_rows($db_query) {

return mysql_num_rows($db_query);

}

 

what's wrong? I checked all the modifications and it's ok

 

I just found this: http://forums.oscommerce.com/index.php?sho...p;#entry1148183

 

but I don't know which one is the affected file that needs to be modified

 

thank you

Share this post


Link to post
Share on other sites

I just installed SPPC along with Quick Updates for SPPC and when I try to add a product I get this message. How do I fix that?

 

Thanks

 

 

1136 - Column count doesn't match value count at row 1

 

insert into products_attributes values (NULL, '573', '1', '1', '', '+', '@')

 

[TEP STOP]

Share this post


Link to post
Share on other sites
You could start by finding this piece of code and add some echo statements to it and stop execution to see if that went through:

// BOF Separate Price Per Customer, Hide products from groups modification
	  $hide_from_these_groups = '@,';
		 if ( $HTTP_POST_VARS['hide'] ) { // if any of the checkboxes are checked
		foreach($HTTP_POST_VARS['hide'] as $val) {
		$hide_from_these_groups .= tep_db_prepare_input($val).','; 
		} // end foreach
		$hide_from_these_groups = substr($hide_from_these_groups,0,strlen($hide_from_these_groups)-1); // remove last comma
  }
echo '<pre>Hide from these groups is now: ';
echo $hide_from_these_groups;
exit(); // code stops here so you can check if that is OK
// EOF Separate Price Per Customer, Hide products from groups modification

If that is OK you should compare your code a few lines further to see if this is added to the sql_array:

// BOF Separate Price Per Customer, hide for these groups modification
			  'products_hide_from_groups' => $hide_from_these_groups,
// EOF Separate Price Per Customer, hide for these groups modification

If it is not OK you should check if the values for hide (which is an array) are in the POST array. Somewhere in the top:

echo '<pre>';
print_r($HTTP_POST_VARS);
exit;

 

 

when I did what you said in first code I got this:

Hide from these groups is now: @,0

 

Then I compared the code as you said in second suggestion and got this:

// BOF Separate Price Per Customer, hide for these groups modification

'products_hide_from_groups' => $hide_from_these_groups,

// EOF Separate Price Per Customer, hide for these groups modification

 

Then I tried to find the code in the third suggestion and I found this:

case 'update_product':

if (isset($HTTP_POST_VARS['edit_x']) || isset($HTTP_POST_VARS['edit_y'])) {

$action = 'new_product';

} else {

if (isset($HTTP_GET_VARS['pID'])) $products_id = tep_db_prepare_input($HTTP_GET_VARS['pID']);

$products_date_available = tep_db_prepare_input($HTTP_POST_VARS['products_date_available']);

// BOF Separate Pricing Per Customer, hide products and categories from groups

$hide_from_these_groups = '@,';

if ( $HTTP_POST_VARS['hide'] ) { // if any of the checkboxes are checked

foreach($HTTP_POST_VARS['hide'] as $val) {

$hide_from_these_groups .= tep_db_prepare_input($val).',';

} // end foreach

 

$hide_from_these_groups = substr($hide_from_these_groups,0,strlen($hide_from_these_groups)-1); // remove last comma

}

// EOF Separate Pricing Per Customer, hide products and categories from groups

 

Not sure if third suggestion is what you were talking about but it is the only one I could find that had to do with products and not categories

 

Thanks

Mike

Share this post


Link to post
Share on other sites
Not sure if third suggestion is what you were talking about but it is the only one I could find that had to do with products and not categories

You could make a deliberate error in the query and see what it is trying to insert. For example change that:

// BOF Separate Price Per Customer, hide for these groups modification
			  'products_hide_from_groups_nonsense' => $hide_from_these_groups,
// EOF Separate Price Per Customer, hide for these groups modification

When you update you should get an error that the column products_hide_from_groups_nonsense doesn't exist with a part of the query. Then you can see if @,0 is in.

Share this post


Link to post
Share on other sites
what's wrong? I checked all the modifications and it's ok

Apparently not. The code is asking for the fetching of the results of a query where there hasn't been any.

Share this post


Link to post
Share on other sites

I need to add a new customer group into position 0 and essentially shift all the others up one slot. So right now customer group 0 is "retail", group 1 is "wholesale level 1" etc. I want to make group 0 something like "pending", group 1 would be "retail", group 2 "wholesale level 1" etc.

 

What would be the appropriate SQL statement to insert this new group while maintaining all the current pricing on the other groups. Oh yeah, the pricing for all the products for the new default group 0 will also be 0 (so I can not show pricing until they are assigned a group).

 

Thanks in advance.

Share this post


Link to post
Share on other sites
I just installed SPPC along with Quick Updates for SPPC and when I try to add a product I get this message. How do I fix that?

 

Thanks

1136 - Column count doesn't match value count at row 1

 

insert into products_attributes values (NULL, '573', '1', '1', '', '+', '@')

 

[TEP STOP]

Quick Updates for SPPC has nothing to do with products_attributes. Also when adding a product in admin/categories.php there are not products_attributes involved. The query seems fine since there are seven columns and that is correct for SPPC because the last one (column attributes_hide_from_groups) is added for that. You will have run the sppc_v41_install.sql and that adds that column so I don't see what went wrong here.

Share this post


Link to post
Share on other sites
I need to add a new customer group into position 0 and essentially shift all the others up one slot. So right now customer group 0 is "retail", group 1 is "wholesale level 1" etc. I want to make group 0 something like "pending", group 1 would be "retail", group 2 "wholesale level 1" etc.

 

What would be the appropriate SQL statement to insert this new group while maintaining all the current pricing on the other groups. Oh yeah, the pricing for all the products for the new default group 0 will also be 0 (so I can not show pricing until they are assigned a group).

I would start with making a backup of the relevant tables you will be changing (I think only specials, products_groups, and products_attributes_group are needed, other ones will auto-update).

Then start with the highest customer_group_id and update that one to one higher and then go to the next lower one:

update products_groups set customers_group_id = '6' where customers_group_id = '5';
update products_groups set customers_group_id = '5' where customers_group_id = '4';

Similar with specials and products_attributes_group.

 

You can change the names of the groups in the admin of course.

Share this post


Link to post
Share on other sites

OK I uninstalled the V42_install.sql and ran the V41_install.sql and it works now but I'm running the latest version don't I have to use the V42_install or add anything that might be missing from the V42_install?

 

Thanks

 

 

Quick Updates for SPPC has nothing to do with products_attributes. Also when adding a product in admin/categories.php there are not products_attributes involved. The query seems fine since there are seven columns and that is correct for SPPC because the last one (column attributes_hide_from_groups) is added for that. You will have run the sppc_v41_install.sql and that adds that column so I don't see what went wrong here.
Edited by computel

Share this post


Link to post
Share on other sites
I ran the sppc_v42_install.sql. Do I need to run the v42?

Do you mean you ran v41 and wondered if you need 42? If you ran an older one you missed at least:

ALTER TABLE `newsletters`
ADD send_to_customer_groups VARCHAR(32) DEFAULT NULL;

ALTER TABLE `products_attributes`
ADD `attributes_hide_from_groups` varchar(255) NOT NULL default '@';

DROP TABLE IF EXISTS `products_attributes_groups`;
CREATE TABLE `products_attributes_groups` (
 `products_attributes_id` int(11) NOT NULL default '0',
 `customers_group_id` smallint(5) NOT NULL default '0',
 `options_values_price` decimal(15,4) NOT NULL default '0.0000',
 `price_prefix` char(1) NOT NULL default '',
 `products_id` int(11) NOT NULL default '0',
 PRIMARY KEY  (`customers_group_id`,`products_attributes_id`)
);

Share this post


Link to post
Share on other sites

Funny thing is I removed the v42 and ran the 41 install and I can ad a product but when I view the product I get this when I view the product at store level. what should I do?

 

 

 

 

1054 - Unknown column 'attributes_hide_from_groups' in 'where clause'

 

select count(*) as total from products_options popt, products_attributes patrib where patrib.products_id='267' and patrib.options_id = popt.products_options_id and popt.language_id = '1' and find_in_set('0', attributes_hide_from_groups) = 0

 

[TEP STOP]

 

Do you mean you ran v41 and wondered if you need 42? If you ran an older one you missed at least:

ALTER TABLE `newsletters`
ADD send_to_customer_groups VARCHAR(32) DEFAULT NULL;

ALTER TABLE `products_attributes`
ADD `attributes_hide_from_groups` varchar(255) NOT NULL default '@';

DROP TABLE IF EXISTS `products_attributes_groups`;
CREATE TABLE `products_attributes_groups` (
 `products_attributes_id` int(11) NOT NULL default '0',
 `customers_group_id` smallint(5) NOT NULL default '0',
 `options_values_price` decimal(15,4) NOT NULL default '0.0000',
 `price_prefix` char(1) NOT NULL default '',
 `products_id` int(11) NOT NULL default '0',
 PRIMARY KEY  (`customers_group_id`,`products_attributes_id`)
);

Share this post


Link to post
Share on other sites

Ok here is what I'm doing?

 

I'm installing a new copy of 2.2 Release Candidate 2a + buySAFE. I'm also importing products from another database. Here are the fields categories, categories_description, products, products_attributes, products_attributes_download, products_attributes_groups, products_description, products_groups, products_options, products_options_values, products_options_values_to_products_options & products_to_categories

 

Not sure if I sould install SPPC 4.2 & Quick Update for sppc and then import the data but not delete the database table but just install the info from the insert lines?

 

How should I go about this? as I need to get SPPC and Quick Updates and the 500+ Products into OSC?

 

I hope this helps

 

Thanks

Share this post


Link to post
Share on other sites
You could make a deliberate error in the query and see what it is trying to insert. For example change that:

// BOF Separate Price Per Customer, hide for these groups modification
			  'products_hide_from_groups_nonsense' => $hide_from_these_groups,
// EOF Separate Price Per Customer, hide for these groups modification

When you update you should get an error that the column products_hide_from_groups_nonsense doesn't exist with a part of the query. Then you can see if @,0 is in.

 

Thanks for the suggestion. I did what you said and I got the following error:

 

1054 - Unknown column 'products_hide_from_groups_nonsense' in 'field list'

 

update products set products_quantity = '90', products_model = 'trial', products_price = '12.0000', products_date_available = null, products_weight = '0.00', products_status = '1', products_tax_class_id = '1', products_hide_from_groups_nonsense = '@,0', manufacturers_id = '', products_last_modified = now() where products_id = '2613'

 

[TEP STOP]

 

Is that what should have happened?

 

Thanks

Mike

Share this post


Link to post
Share on other sites
Thanks for the suggestion. I did what you said and I got the following error:

 

1054 - Unknown column 'products_hide_from_groups_nonsense' in 'field list'

 

update products set products_quantity = '90', products_model = 'trial', products_price = '12.0000', products_date_available = null, products_weight = '0.00', products_status = '1', products_tax_class_id = '1', products_hide_from_groups_nonsense = '@,0', manufacturers_id = '', products_last_modified = now() where products_id = '2613'

 

[TEP STOP]

 

Is that what should have happened?

 

Thanks

Mike

 

 

Thanks for all the help JanZ.

I figured it out. I went back and was checking the database and I noticed that the default value for products_hide_from_groups was not set. I set it to @ and now everything works like it should. I guess I should have checked it earlier. I am new to php and databases and I would have never thought that would keep it from working.

 

Sorry I wasted your time

 

Mike

 

:blush:

Share this post


Link to post
Share on other sites

I have the sppc installed and i have the categories dropdown installed is there a way to get the two to work together?

below is the inludes/boxes/categories:

 

<?php

/*

osCommerce, Open Source E-Commerce Solutions

http://www.oscommerce.com

 

Copyright © 2001 osCommerce

 

Released under the GNU General Public License

*/

?>

<!-- categories //-->

<tr>

<td>

<?php

 

function tep_get_paths($categories_array = '', $parent_id = '0', $indent = '', $path='') {

global $languages_id;

 

if (!is_array($categories_array)) $categories_array = array();

 

 

 

$categories_query = tep_db_query("select c.categories_id, cd.categories_name from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where parent_id = '" . (int)$parent_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by sort_order, cd.categories_name");

while ($categories = tep_db_fetch_array($categories_query)) {

if ($parent_id=='0'){

$categories_array[] = array('id' => $categories['categories_id'],

'text' => $indent . $categories['categories_name']);

}

else{

$categories_array[] = array('id' => $path . $parent_id . '_' .$categories['categories_id'],

'text' => $indent . $categories['categories_name']);

}

 

if ($categories['categories_id'] != $parent_id) {

$this_path=$path;

if ($parent_id != '0')

$this_path = $path . $parent_id . '_';

$categories_array = tep_get_paths($categories_array, $categories['categories_id'], $indent . '  ', $this_path);

}

}

 

return $categories_array;

}

 

$info_box_contents = array();

$info_box_contents[] = array('align' => 'left',

'text' => BOX_HEADING_CATEGORIES

);

new infoBoxHeading($info_box_contents, true, false);

 

$info_box_contents = array();

$info_box_contents[] = array('form' => '<form action="' . tep_href_link(FILENAME_DEFAULT) . '" method="get">' . tep_hide_session_id(),

'align' => 'left',

'text' => tep_draw_pull_down_menu('cPath', tep_get_paths(array(array('id' => '', 'text' => PULL_DOWN_DEFAULT))), $cPath, 'onchange="this.form.submit();"')

);

new infoBox($info_box_contents);

?>

</td>

</tr>

<!-- categories_eof //-->

 

Any help is appreciated

Mike

Share this post


Link to post
Share on other sites
I would start with making a backup of the relevant tables you will be changing (I think only specials, products_groups, and products_attributes_group are needed, other ones will auto-update).

Then start with the highest customer_group_id and update that one to one higher and then go to the next lower one:

update products_groups set customers_group_id = '6' where customers_group_id = '5';
update products_groups set customers_group_id = '5' where customers_group_id = '4';

Similar with specials and products_attributes_group.

 

You can change the names of the groups in the admin of course.

 

Thanks, that certainly has me on the right track. There's also modifying the customers table to assign the customers to their new group number.

 

The big problem I see right now is how to assign all the pricing from customer group 0 to customer group 1 since they are actually contained in different tables. Changing pricing for groups 1 and 2 is easy enough (same idea as you already gave me) but how would I get the pricing from products -> products_price into products_groups -> customers_group_price and assign the customer_group_id to "1"? Then I'll need to set all the products -> products_price to 0.

 

Thanks.

Share this post


Link to post
Share on other sites
Thanks, that certainly has me on the right track. There's also modifying the customers table to assign the customers to their new group number.

You are right, haven't thought about that.

 

The big problem I see right now is how to assign all the pricing from customer group 0 to customer group 1 since they are actually contained in different tables. Changing pricing for groups 1 and 2 is easy enough (same idea as you already gave me) but how would I get the pricing from products -> products_price into products_groups -> customers_group_price and assign the customer_group_id to "1"? Then I'll need to set all the products -> products_price to 0.

Backup, backup, backup...

Something like:

insert into products_groups select '1' as customers_group_id, p.products_price as customers_group_price, p.products_id from products p;

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

×