Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Seperate Pricing Per Customer v3.5


scendent

Recommended Posts

i have installed the SPPC 4.1.5... but the cash on delivery it dosen't appear in the payments method.. i want it to appear how????

Did you enable that payment method for the customer group in the admin (customer_groups.php)?

Link to comment
Share on other sites

Hi Jan,

 

I am looking at "optimization" now to try and lower the number of queries that happen per page. Right now I'm focusing on the files in one of contrib's I added (STS) and I was curious if this section of code could be done with one query and then append the "if" statement after the query? This is in my includes/modules/sts_inc/product_info.php file:

 

// BOF Separate Price per Customer

	$scustomer_group_price_query = tep_db_query("select customers_group_price from " . TABLE_PRODUCTS_GROUPS . " where products_id = '" . $products_id . "' and customers_group_id =  '" . $customer_group_id . "'");
	if ($scustomer_group_price = tep_db_fetch_array($scustomer_group_price_query)) {
	$product_info['products_price']= $scustomer_group_price['customers_group_price'];
}
// EOF Separate Price per Customer
$products_price = '<s>' . $currencies->display_price($product_info['products_price'], tep_get_tax_rate($product_info['products_tax_class_id'])) . '</s> <span class="productSpecialPrice">' . $currencies->display_price($new_price, tep_get_tax_rate($product_info['products_tax_class_id'])) . '</span>';
} else {

// BOF Separate Price per Customer
	$scustomer_group_price_query = tep_db_query("select customers_group_price from " . TABLE_PRODUCTS_GROUPS . " where products_id = '" . $products_id . "' and customers_group_id =  '" . $customer_group_id . "'");
	if ($scustomer_group_price = tep_db_fetch_array($scustomer_group_price_query)) {
	$product_info['products_price']= $scustomer_group_price['customers_group_price'];
}
// EOF Separate Price per Customer


  $products_price = $currencies->display_price($product_info['products_price'], tep_get_tax_rate($product_info['products_tax_class_id']));
}

 

It looks to me like one query is for Product Specials and one is for Products that aren't on Special. I'm guessing it could be done in one query - but thought I'd check :blush:

~Tracy
 

Link to comment
Share on other sites

Hi Jan,

 

I am looking at "optimization" now to try and lower the number of queries that happen per page.

 

Ok - I admit this is a little "off-topic" - but I'm not sure where a better place to ask would be :blush: The DynaMenu contrib was not setup originally to include products within the menu so that thread doesn't really offer much in the way of support for that particular concept.

 

I have my category menu (DynaMenu contrib) setup to show BOTH the categories AND the products all via one vertical fly-out menu. I've found that the Categories query is just ONE query on my page - but getting the Products for the menu results in 63 Queries that all say the same thing - only difference is the category_id.

 

So I have 63 of these (with the only changing item being p2c.categories_id='*':

[20] => select p.products_id, p.products_master_status, pd.products_name as product, pd.products_id as product_id from products p, products_to_categories p2c, products_description pd where p.products_id = p2c.products_id and p2c.categories_id = '1' AND pd.language_id = '1' and find_in_set('0', products_hide_from_groups) = 0 and p.products_id = pd.products_id AND p.products_master_status='1' order by p.products_sort_order asc, pd.products_name

 

This is the query (in includes/functions/general.php) that I believe is generating the above

//for products in Dynamenu
function tep_products_in_category($category_id, $language = '', $include_inactive = false) {

global $languages_id, $customer_group_id;

if (empty($language)) $language = $languages_id;

if ($include_inactive) {
$products_query = tep_db_query("select p.products_id, p.products_master_status, pd.products_name as product, pd.products_id as product_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = p2c.products_id and p2c.categories_id = '" . $category_id . "' AND pd.language_id = '" . (int)$language . "' and find_in_set('".$customer_group_id."', products_hide_from_groups) = 0 and p.products_id = pd.products_id AND p.products_master_status='1' order by p.products_sort_order asc, pd.products_name");
} else {
$products_query = tep_db_query("select p.products_id, p.products_master_status, pd.products_name as product, pd.products_id as product_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = p2c.products_id and p2c.categories_id = '" . $category_id . "' AND pd.language_id = '" . (int)$language . "' and find_in_set('".$customer_group_id."', products_hide_from_groups) = 0 and p.products_id = pd.products_id AND p.products_master_status='1' order by p.products_sort_order asc, pd.products_name");
}

return $products_query;
}

 

And it is called in includes/boxes/dm_categories.php (the DynaMenu) via this:

$products_in_category_query = tep_products_in_category($key);

while ($products_in_category = tep_db_fetch_array($products_in_category_query)) {

$product_id_h = 'products_id='.$products_in_category ['product_id'];
$product_name_h = $products_in_category ['product'];

if ($GLOBALS['products_id'] == $products_in_category ['product_id']) {
$that_expanded = '1';
$that_selected = 'dmselected';
} else {
$that_expanded = '';
$that_selected = '';
}

if ($menu_use_titles) {
$that_title = $product_name_h;
} else {
$that_title = '';
}

$output .= str_repeat(".", $level+2).'|'.$product_name_h.'|'.tep_href_link(FILENAME_PRODUCT_INFO, $product_id_h).'|'.$that_title.'|'.$menu_icon_file.'|'.$that_selected.'|'.$that_expanded."\n";
}

 

Any thoughts come to mind on how to optimize this :blush:

Edited by TracyS

~Tracy
 

Link to comment
Share on other sites

I am looking at "optimization" now to try and lower the number of queries that happen per page.

 

Ok - I admit this is a little "off-topic" - but I'm not sure where a better place to ask would be :blush: The DynaMenu contrib was not setup originally to include products within the menu so that thread doesn't really offer much in the way of support for that particular concept.

 

I have my category menu (DynaMenu contrib) setup to show BOTH the categories AND the products all via one vertical fly-out menu. I've found that the Categories query is just ONE query on my page - but getting the Products for the menu results in 63 Queries that all say the same thing - only difference is the category_id.

 

So I have 63 of these (with the only changing item being p2c.categories_id='*':

[20] => select p.products_id, p.products_master_status, pd.products_name as product, pd.products_id as product_id from products p, products_to_categories p2c, products_description pd where p.products_id = p2c.products_id and p2c.categories_id = '1' AND pd.language_id = '1' and find_in_set('0', products_hide_from_groups) = 0 and p.products_id = pd.products_id AND p.products_master_status='1' order by p.products_sort_order asc, pd.products_name

 

Any thoughts come to mind on how to optimize this

I agree you should do something about it. I had a similar problem with QPBPP (Quantity Price Break Per Product) where someone found out that PriceFormatter does the same query 5 times for each product in the cart.

 

My tactic would be to create a class (something like PriceFormatterStore in the QPBPP) that is loaded in application_top.php. What it would do first is a query for the categories (make sure you add your logic like status = '1' or something), implode it around a comma and do that query (with p2c.categories_id in it):

[20] => select p2c.categories_id, p.products_id, p.products_master_status, pd.products_name as product, pd.products_id as product_id from products p, products_to_categories p2c, products_description pd where p.products_id = p2c.products_id and p2c.categories_id IN (comma_separated_list_of_categories) AND pd.language_id = '1' and find_in_set('0', products_hide_from_groups) = 0 and p.products_id = pd.products_id AND p.products_master_status='1' order by p.products_sort_order asc, pd.products_name

Then build an array that has as a key the categories_id.

Then any time that former query is used I would check if that key is in that object/class and iterate through the subarray with that key instead of querying one by one. That would bring it back from 63 to 2.

 

For "just in case" you could add that query if it cannot be retrieved from the object.

 

That will take some time to code though...

Link to comment
Share on other sites

It looks to me like one query is for Product Specials and one is for Products that aren't on Special. I'm guessing it could be done in one query - but thought I'd check
Trouble is you can't join the table specials with any other table. If the product is not a special the query won't return the info from the other table (because of the customer_group_id that has to be added).
Link to comment
Share on other sites

I agree you should do something about it. I had a similar problem with QPBPP (Quantity Price Break Per Product) where someone found out that PriceFormatter does the same query 5 times for each product in the cart.

 

My tactic would be to create a class (something like PriceFormatterStore in the QPBPP) that is loaded in application_top.php. What it would do first is a query for the categories (make sure you add your logic like status = '1' or something), implode it around a comma and do that query (with p2c.categories_id in it):

[20] => select p2c.categories_id, p.products_id, p.products_master_status, pd.products_name as product, pd.products_id as product_id from products p, products_to_categories p2c, products_description pd where p.products_id = p2c.products_id and p2c.categories_id IN (comma_separated_list_of_categories) AND pd.language_id = '1' and find_in_set('0', products_hide_from_groups) = 0 and p.products_id = pd.products_id AND p.products_master_status='1' order by p.products_sort_order asc, pd.products_name

Then build an array that has as a key the categories_id.

Then any time that former query is used I would check if that key is in that object/class and iterate through the subarray with that key instead of querying one by one. That would bring it back from 63 to 2.

 

For "just in case" you could add that query if it cannot be retrieved from the object.

 

That will take some time to code though...

 

Wow - that's a lot to wrap my brain around at the moment - LOL :blush:

 

Ok - considering this is something that I use for the DynaMenu and would like to figure out how to add (possibly) to the Site Map contrib - what about this idea:

 

Is there a way to use the existing code that generates the stock osC categories box (assuming it looks at subcategories and where they go in the structure) and modify it to include the products? I know I've seen the global cPath-array and there's the tep_get_path($current_category_id) in includes/functions/general.php I believe that DynaMenu uses some portion of the cPath to generate it's category menu - was just thinking that maybe it would be easier to somehow add the products into the existing category array ? :blink:

 

I'll have to double check what makes the DynaMenu tick again - since the categories part of it seems to be created from one query - there may be a way to include the products in that same query :huh:

~Tracy
 

Link to comment
Share on other sites

Is there a way to use the existing code that generates the stock osC categories box (assuming it looks at subcategories and where they go in the structure) and modify it to include the products? I know I've seen the global cPath-array and there's the tep_get_path($current_category_id) in includes/functions/general.php I believe that DynaMenu uses some portion of the cPath to generate it's category menu - was just thinking that maybe it would be easier to somehow add the products into the existing category array ?
osC suffers from the same problem really (query for each top category). Fixed in osC 3 though I understood.

 

Actually, you could make osC use that object and save those queries too :rolleyes:

Link to comment
Share on other sites

osC suffers from the same problem really (query for each top category). Fixed in osC 3 though I understood.

 

Actually, you could make osC use that object and save those queries too :rolleyes:

 

Hmmm - well, since my PHP/MySQL book barely mentions objects (LOL) I think I will have to set this one aside for separate optimization and work on it when I have the other issues dealt with. I might check the forum for the script that was used to create DynaMenu and see if they have any suggestions for incorporating the products into the menu using the scripting that runs DynaMenu.

 

Maybe I will learn enough with the other optimizations to be able to figure it out when I re-visit it :blush:

~Tracy
 

Link to comment
Share on other sites

Silly question - but can this be done?

 

I need to pull data from 3 different tables and like to Join them all ON(products_id) - but when I try to do two LEFT JOIN table_name_here tnh ON(products_id) I get an error that 'column products_id in ON clause is ambiguous'

 

I've tried USING(products_id) for one and ON(products_id) for another but that doesn't work either. I've also tried LEFT JOIN table1 t1 && table2 t2 ON(products_id) and then tried the same but replaced && with AND but again I get errors that I'm not using proper syntax.

 

Is it not possible to "use" the same column to join all 3 tables without having to do the WHERE table.products_id = table2.products_id bit?

 

I was thinking the JOIN would help speed up the query process - but I'm stumped as to how to do it - LOL

 

Here's the bit I'm playing with in phpMyAdmin at the moment:

 

select p.products_id, p.products_master_status, pd.products_name as product, pd.products_id as product_id from products p LEFT JOIN products_to_categories p2c using(products_id) LEFT JOIN products_description pd ON(products_id) where p2c.categories_id = "17" AND pd.language_id = "1" AND p.products_master_status="1" order by p.products_sort_order asc, pd.products_name;

~Tracy
 

Link to comment
Share on other sites

Hi Jan,

 

I am looking at "optimization" now to try and lower the number of queries that happen per page. Right now I'm focusing on the files in one of contrib's I added (STS) and I was curious if this section of code could be done with one query and then append the "if" statement after the query? This is in my includes/modules/sts_inc/product_info.php file:

 

// BOF Separate Price per Customer

	$scustomer_group_price_query = tep_db_query("select customers_group_price from " . TABLE_PRODUCTS_GROUPS . " where products_id = '" . $products_id . "' and customers_group_id =  '" . $customer_group_id . "'");
	if ($scustomer_group_price = tep_db_fetch_array($scustomer_group_price_query)) {
	$product_info['products_price']= $scustomer_group_price['customers_group_price'];
}
// EOF Separate Price per Customer
$products_price = '<s>' . $currencies->display_price($product_info['products_price'], tep_get_tax_rate($product_info['products_tax_class_id'])) . '</s> <span class="productSpecialPrice">' . $currencies->display_price($new_price, tep_get_tax_rate($product_info['products_tax_class_id'])) . '</span>';
} else {

// BOF Separate Price per Customer
	$scustomer_group_price_query = tep_db_query("select customers_group_price from " . TABLE_PRODUCTS_GROUPS . " where products_id = '" . $products_id . "' and customers_group_id =  '" . $customer_group_id . "'");
	if ($scustomer_group_price = tep_db_fetch_array($scustomer_group_price_query)) {
	$product_info['products_price']= $scustomer_group_price['customers_group_price'];
}
// EOF Separate Price per Customer


  $products_price = $currencies->display_price($product_info['products_price'], tep_get_tax_rate($product_info['products_tax_class_id']));
}

 

It looks to me like one query is for Product Specials and one is for Products that aren't on Special. I'm guessing it could be done in one query - but thought I'd check :blush:

 

Any thoughts on this one? Couldn't I just take the 2nd query out and have the $products_price = $currencies->display_price bit after the }else{ ?

~Tracy
 

Link to comment
Share on other sites

I need to pull data from 3 different tables and like to Join them all ON(products_id) - but when I try to do two LEFT JOIN table_name_here tnh ON(products_id) I get an error that 'column products_id in ON clause is ambiguous'

 

I've tried USING(products_id) for one and ON(products_id) for another but that doesn't work either. I've also tried LEFT JOIN table1 t1 && table2 t2 ON(products_id) and then tried the same but replaced && with AND but again I get errors that I'm not using proper syntax.

 

Is it not possible to "use" the same column to join all 3 tables without having to do the WHERE table.products_id = table2.products_id bit?

It should work with using(products_id) but if you use ON you have to use ON p.products_id = p2c.products_id etcetera.
Link to comment
Share on other sites

Any thoughts on this one? Couldn't I just take the 2nd query out and have the $products_price = $currencies->display_price bit after the }else{ ?
Looks to me that either one of the those customer_group_price queries is done, not both so it is not a problem.

 

Perhaps you can cut some queries in includes/new_products.php. In standard osC the products_description is picked up with a separate query for each product. Total nonsense (not something I found out, I read about it).

 

This will be the v4.2.0 version:

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
// BOF Separate Pricing Per Customer
$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, p.products_price as products_price, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where products_status = '1' and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
 } else {
$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, p.products_price, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c using(products_id) left join " . TABLE_CATEGORIES . " c using(categories_id) where p.products_id = pd.products_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added desc limit ". MAX_DISPLAY_NEW_PRODUCTS);
 }

Link to comment
Share on other sites

Hi,

 

I have just finished uploading the modified PHP files, but am having some difficulties. I'm sorry if this issue have been brought up earlier -- a quick search didn't tell me anything interesting.

 

The issue is pretty clear: My front page is empty :) There are lots of products in the database, but they don't show up: http://dev.idgbooks.no.nordkapp.net/ ("Kjøp nå" means "Buy now" and the occurrences "0,00kr" should have been individual prices).

 

Any suggestions of what the problem might be?

 

Thanks in advance,

 

Espen Andersson

Link to comment
Share on other sites

Any suggestions of what the problem might be?
Probably, you have a template product listing in which you added the logic for the price, but did not change all (seems like not a single one) the $listing['field'] to $listing[$x]['field'].
Link to comment
Share on other sites

A few things that I think should be changed:

// BOF Separate Pricing per Customer
$no_of_listings = tep_db_num_rows($listing_query);
// never know if it is a global already:
global $sppc_customer_group_id;
// global variable (session) $sppc_customer_group_id -> local variable customer_group_id

Further on:

$list_box_contents[$cur_row][] = array('align' => $lc_align,
										   'params' => 'class="productListing-data"',
										   'text'  => $lc_text);
	$product_contents[] = $lc_text;	   
  }
// listing => listing[$x]
  $product_query = tep_db_query("select products_description  from " . TABLE_PRODUCTS_DESCRIPTION . " where products_id = '" . (int)$listing[$x]['products_id'] . "' and language_id = '" . (int)1 . "'");
  $product = tep_db_fetch_array($product_query);
  $new_products['products_description'] = $product['products_description'];

 

Hi JanZ, thanks for the reply, i tried making the changes you suggested above but the product_listing.php is still showing only 1 product cloned multiple times... i enabled some debug code on the page to echo whats happening and you can see in the output text that the products are different in the array but down the page it still shows the one product multiple times, have a look at this link to see what i mean:

 

http://www.bwear.ie/catalog_separatepricin...php/cPath/21_25

 

When i use the product_listing.php-SPPCv414 file, everything works fine but the layout is rows instead of columns. Any other ideas what could be causing the array to be displayed wrong? Cheers.

 

Here's my product_listing.php file again:

 

<?php
/*
 $Id: product_listing.php,v 1.44 2003/06/09 22:49:59 hpdl Exp $

 adapted for Separate Pricing Per Customer v4 2005/02/26

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

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/
 $listing_split = new splitPageResults($listing_sql, MAX_DISPLAY_SEARCH_RESULTS, 'p.products_id');

 if ( ($listing_split->number_of_rows > 0) && ( (PREV_NEXT_BAR_LOCATION == '1') || (PREV_NEXT_BAR_LOCATION == '3') ) ) {
?>
<table border="0" width="100%" cellspacing="0" cellpadding="2" bgcolor="#E9E6E3">
 <tr>
<td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
<td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>
<?php
 }

 $list_box_contents = array();

 for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
switch ($column_list[$col]) {
  case 'PRODUCT_LIST_MODEL':
	$lc_text = TABLE_HEADING_MODEL;
	$lc_align = '';
	break;
  case 'PRODUCT_LIST_NAME':
	$lc_text = TABLE_HEADING_PRODUCTS;
	$lc_align = '';
	break;
  case 'PRODUCT_LIST_MANUFACTURER':
	$lc_text = TABLE_HEADING_MANUFACTURER;
	$lc_align = '';
	break;
  case 'PRODUCT_LIST_PRICE':
	$lc_text = TABLE_HEADING_PRICE;
	$lc_align = 'right';
	break;
  case 'PRODUCT_LIST_QUANTITY':
	$lc_text = TABLE_HEADING_QUANTITY;
	$lc_align = 'right';
	break;
  case 'PRODUCT_LIST_WEIGHT':
	$lc_text = TABLE_HEADING_WEIGHT;
	$lc_align = 'right';
	break;
  case 'PRODUCT_LIST_IMAGE':
	$lc_text = TABLE_HEADING_IMAGE;
	$lc_align = 'center';
	break;
  case 'PRODUCT_LIST_BUY_NOW':
	$lc_text = TABLE_HEADING_BUY_NOW;
	$lc_align = 'center';
	break;
}

if ( ($column_list[$col] != 'PRODUCT_LIST_BUY_NOW') && ($column_list[$col] != 'PRODUCT_LIST_IMAGE') ) {
  $lc_text = tep_create_sort_heading($HTTP_GET_VARS['sort'], $col+1, $lc_text);
}

$list_box_contents[0][] = array('align' => $lc_align,
								'params' => 'class="productListing-heading"',
								'text' => ' ' . $lc_text . ' ');
 }

 if ($listing_split->number_of_rows > 0) {
$rows = 0;
$column = 0;
echo '   
		<table width="533" cellpadding="0" cellspacing="0" bgcolor="#ffffff">
		<tr>
	<td height="33"  bgcolor="#F4F0ED" valign="middle">
	<table bgcolor="#F4F0ED" cellpadding="0" cellspacing="0" width="531">
<tr>
		   <td width="4"></td>
<td width="32"><img src="images/m05.jpg"></td>
<td width="5"></td>
<td width="488"><span class="ch">Categories » '.$breadcrumb->trail(' » ').'</span></td>
							</tr>
							  </table>
										  </td>
										</tr>
										<tr><td height="1"></td></tr>
										<tr>
														<td height="20" bgcolor="#E9E6E3">
															<table cellpadding="0" border=0  cellspacing="0">
																	<tr>
																	<td width="5"></td>
																	<td></td>
																</tr>
		</table>
														</td>
					  </tr>
					<tr>
		<td valign="top"><table width="100%" cellpadding="1" cellspacing="1">
						  <tr>

	 ';

$listing_query = tep_db_query($listing_split->sql_query);
// BOF Separate Pricing per Customer
$no_of_listings = tep_db_num_rows($listing_query);
// global variable (session) $sppc_customer_group_id -> local variable customer_group_id

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

while ($_listing = tep_db_fetch_array($listing_query)) {
$listing[] = $_listing;
$list_of_prdct_ids[] = $_listing['products_id'];
}
// next part is a debug feature, when uncommented it will print the info that this module receives

  echo '<pre>';
  print_r($listing);
  echo '</pre>';

 $select_list_of_prdct_ids = "products_id = '".$list_of_prdct_ids[0]."' ";
 if ($no_of_listings > 1) {
  for ($n = 1; $n < count($list_of_prdct_ids); $n++) {
  $select_list_of_prdct_ids .= "or products_id = '".$list_of_prdct_ids[$n]."' ";
  }
}

// get all product prices for products with the particular customer_group_id
// however not necessary for customer_group_id = 0
if ($customer_group_id != '0') {
 $pg_query = tep_db_query("select pg.products_id, customers_group_price as price from " . TABLE_PRODUCTS_GROUPS . " pg where (".$select_list_of_prdct_ids.") and pg.customers_group_id = '".$customer_group_id."' ");
//   $no_of_pg_products = tep_db_num_rows($pg_query);
while ($pg_array = tep_db_fetch_array($pg_query)) {
$new_prices[] = array ('products_id' => $pg_array['products_id'], 'products_price' => $pg_array['price'], 'specials_new_products_price' => '', 'final_price' => $pg_array['price']);
}
  for ($x = 0; $x < $no_of_listings; $x++) {
// replace products prices with those from customers_group table
  if(!empty($new_prices)) {
	 for ($i = 0; $i < count($new_prices); $i++) {
	if( $listing[$x]['products_id'] == $new_prices[$i]['products_id'] ) {
	$listing[$x]['products_price'] = $new_prices[$i]['products_price'];
	$listing[$x]['final_price'] = $new_prices[$i]['final_price'];
	}
	}
} // end if(!empty($new_prices)
$listing[$x]['specials_new_products_price'] = ''; // makes sure that a retail specials price doesn't carry over to another customer group
$listing[$x]['final_price'] = $listing[$x]['products_price']; // final price should not be the retail special price
  } // end for ($x = 0; $x < $no_of_listings; $x++)
} // end if ($customer_group_id != '0')

// an extra query is needed for all the specials

$specials_query = tep_db_query("select products_id, specials_new_products_price from " . TABLE_SPECIALS . " where (".$select_list_of_prdct_ids.") and status = '1' and customers_group_id = '" .$customer_group_id. "'");
while ($specials_array = tep_db_fetch_array($specials_query)) {
$new_s_prices[] = array ('products_id' => $specials_array['products_id'], 'products_price' => '', 'specials_new_products_price' => $specials_array['specials_new_products_price'] , 'final_price' => $specials_array['specials_new_products_price']);
}

// add the correct specials_new_products_price and replace final_price
for ($x = 0; $x < $no_of_listings; $x++) {

	if(!empty($new_s_prices)) {
	for ($i = 0; $i < count($new_s_prices); $i++) {
	 if( $listing[$x]['products_id'] == $new_s_prices[$i]['products_id'] ) {
	   $listing[$x]['specials_new_products_price'] = $new_s_prices[$i]['specials_new_products_price'];
	   $listing[$x]['final_price'] = $new_s_prices[$i]['final_price'];
	 }
	   }
   } // end if(!empty($new_s_prices)
} // end for ($x = 0; $x < $no_of_listings; $x++)

//	while ($listing = tep_db_fetch_array($listing_query)) { (was original code)
for ($x = 0; $x < $no_of_listings; $x++) {

  $rows++;

  if (($rows/2) == floor($rows/2)) {
	$list_box_contents[] = array('params' => 'class="productListing-even"');
  } else {
	$list_box_contents[] = array('params' => 'class="productListing-odd"');
  }

  $cur_row = sizeof($list_box_contents) - 1;

  for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
	$lc_align = '';

	switch ($column_list[$col]) {
	  case 'PRODUCT_LIST_MODEL':
		$lc_align = '';
		 $lc_text = ' ' . $listing[$x]['products_model'] . ' ';
		break;
	  case 'PRODUCT_LIST_NAME':
		$lc_align = '';
		if (isset($HTTP_GET_VARS['manufacturers_id'])) {
		  $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing[$x]['products_id']) . '">' . $listing[$x]['products_name'] . '</a>';
		} else {
		  $lc_text = ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing[$x]['products_id']) . '">' . $listing[$x]['products_name'] . '</a> ';
		}
		break;
	  case 'PRODUCT_LIST_MANUFACTURER':
		$lc_align = '';
		$lc_text = ' <a href="' . tep_href_link(FILENAME_DEFAULT, 'manufacturers_id=' . $listing[$x]['manufacturers_id']) . '">' . $listing[$x]['manufacturers_name'] . '</a> ';
		break;
	  case 'PRODUCT_LIST_PRICE':
		$lc_align = 'right';

		if (tep_not_null($listing[$x]['specials_new_products_price'])) {
		  $lc_text = ' <s>' .  $currencies->display_price($listing[$x]['products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . '</s>  <span class="productSpecialPrice">' . $currencies->display_price($listing[$x]['specials_new_products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . '</span> ';
		} else {
		  $lc_text = ' ' . $currencies->display_price($listing[$x]['products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . ' ';
		}
		break;
	  case 'PRODUCT_LIST_QUANTITY':
		$lc_align = 'right';
		$lc_text = ' ' . $listing[$x]['products_quantity'] . ' ';
		break;
	  case 'PRODUCT_LIST_WEIGHT':
		$lc_align = 'right';
		$lc_text = ' ' . $listing[$x]['products_weight'] . ' ';
		break;
	  case 'PRODUCT_LIST_IMAGE':
		$lc_align = 'center';
		if (isset($HTTP_GET_VARS['manufacturers_id'])) {
		  $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing[$x]['products_image'], $listing[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a>';
		} else {
		  $lc_text = ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing[$x]['products_image'], $listing[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a> ';
		}
		break; // EOF Separate Pricing per Customer
	  case 'PRODUCT_LIST_BUY_NOW':
		$lc_align = 'center';
		$lc_text = '<a href="' . tep_href_link(basename($PHP_SELF), tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $listing[$x]['products_id']) . '">' . tep_image_button('button_buy_now.gif', IMAGE_BUTTON_BUY_NOW) . '</a> ';

		break;
	}

	$list_box_contents[$cur_row][] = array('align' => $lc_align,
										   'params' => 'class="productListing-data"',
										   'text'  => $lc_text);
	$product_contents[] = $lc_text;	   
  }


  $product_query = tep_db_query("select products_description  from " . TABLE_PRODUCTS_DESCRIPTION . " where products_id = '" . (int)$listing['products_id'] . "' and language_id = '" . (int)1 . "'");
  $product = tep_db_fetch_array($product_query);
  $new_products['products_description'] = $product['products_description'];

  echo '

											<td valign="top" bgcolor="#E9E6E3">
												<table width="100%" cellpadding="0" cellspacing="0">
										<tr>
		<td valign="top">
		<table cellpadding="0" border="0" cellspacing="0" width=100%>
																<tr>
																	<td width="1"></td>
																	<td valign="top">
																	<table width="100%" border=0 cellpadding="0" cellspacing="0">

																	<tr><td height=19 valign=top><br><div align="center">'.$product_contents[1].'</div></td></tr>
																	<tr>
																	<td><br><div align="center">
																		  '.$product_contents[0].'
																		  </div><div align="left"></div></td>
																		</tr>
																	<tr><td><div align="center"><b>Price:</b></div></td></tr>
																		<tr>
																		<td height=30 valign=middle><div align="center"><span class="ch3">'.$product_contents[2].'</span></div></td>
																					</tr>
																					<tr><td>
																					<table align="center" cellpadding="0" cellspacing="0">
																					<tr><td height="5"></td></tr>
																<tr>
																<td width="100" valign="top">
																	<table width="120" cellpadding="0" border="0" cellspacing="0">
																	<tr>
																		<td><div align="center"><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $listing['products_id']) . '">' . tep_image_button('small_view.gif') . '</a></div></td>
																		<td width="4"><div align="center"></div></td>
																		<td><div align="center"><a href="' . tep_href_link(basename($PHP_SELF), tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $listing['products_id']) . '">' . tep_image_button('button_in_cart.gif') . '</a></div></td>
																	</tr>
																  </table>																	  </td>
																			</tr>
																					</table>
																	</td>
																</tr>
																						<tr>
																						  <td> </td>
																		  </tr>
																	</table>
																</td>
</tr>
			  </table>
													  </td>
													</tr>
											  </table>
											</td>

  ';
  $column ++;
  if ($column >= 2) {
	$rows ++;
	$column = 0;
	echo '
					</tr>
						<tr>
		';
  } else echo '
  ';

}

echo '
						  </tr>
						</table>
						</td>
						</tr>
</table>
	 ';

//new productListingBox($list_box_contents);
 } else {
$list_box_contents = array();

$list_box_contents[0] = array('params' => 'class="productListing-odd"');
$list_box_contents[0][] = array('params' => 'class="productListing-data"',
							   'text' => TEXT_NO_PRODUCTS);

echo '<div class=PageHeading>Categories</div>';
new productListingBox($list_box_contents);
 }

 if ( ($listing_split->number_of_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '2') || (PREV_NEXT_BAR_LOCATION == '3')) ) {
?>

<table border="0" width="100%" cellspacing="0" cellpadding="2">
 <tr><td height=2></td></tr>
 <tr>
<td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
<td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>

<?php
 }
?>

Link to comment
Share on other sites

Probably, you have a template product listing in which you added the logic for the price, but did not change all (seems like not a single one) the $listing['field'] to $listing[$x]['field'].

 

Hm, could be. I'll take a look, but what file manages the box on the front page?

Link to comment
Share on other sites

Hi JanZ, thanks for the reply, i tried making the changes you suggested above but the product_listing.php is still showing only 1 product cloned multiple times... i enabled some debug code on the page to echo whats happening and you can see in the output text that the products are different in the array but down the page it still shows the one product multiple times, have a look at this link to see what i mean:

 

http://www.bwear.ie/catalog_separatepricin...php/cPath/21_25

 

When i use the product_listing.php-SPPCv414 file, everything works fine but the layout is rows instead of columns. Any other ideas what could be causing the array to be displayed wrong? Cheers.

 

Here's my product_listing.php file again:

 

Well - I'm not positive - but it's worth a try :blush: Look at this section of your code:

$list_box_contents[0][] = array('align' => $lc_align,
								'params' => 'class="productListing-heading"',
								'text' => ' ' . $lc_text . ' ');
 }

 if ($listing_split->number_of_rows > 0) {
$rows = 0;
$column = 0;
echo '   
		<table width="533" cellpadding="0" cellspacing="0" bgcolor="#ffffff">

 

I see that you have both $rows = 0; AND $column = 0;

 

In my store I display the products in rows - I do not have the $column = 0; bit of code here. Maybe for your store, if you're trying to show everything in columns, you need to remove the $rows = 0; portion?

 

My other thought - if you are trying to display in columns rather than rows - are you using a contribution do that? Did you do a comparison (like in WinMerge) of the SPPC product_listing.php vs the contributions product_listing.php to make sure there aren't any other changes in how things like "column list" and "num_rows" should be handled?

 

Again - not sure if this will help - but figured it's worth a try :blush:

~Tracy
 

Link to comment
Share on other sites

It should work with using(products_id) but if you use ON you have to use ON p.products_id = p2c.products_id etcetera.

 

Ah Ha! So that's what I was doing wrong - LOL :blush: Thank you! I did change the queries for the products in the menu to the LEFT JOIN queries so hopefully it is querying faster now - but it still results in 60+ queries so I am gonna check out the contrib you mentioned to see their code and see if I can figure something out - :-"

 

PS - thanks for the tip on the new_products.php file - just curious - why doesn't the first query check for the category?

 

Also - I made the changes to my includes/functions/general.php that were listed here:

http://www.oscommerce.com/forums/index.php?s=&showtopic=119077&view=findpost&p=1024887

 

But I still get this weird set of queries on my category pages:

[20] => select parent_id from categories where categories_id = '17'

[21] => select parent_id from categories where categories_id = '25'

[22] => select parent_id from categories where categories_id = '17'

[23] => select parent_id from categories where categories_id = '29'

[24] => select parent_id from categories where categories_id = '17'

[25] => select parent_id from categories where categories_id = '28'

[26] => select parent_id from categories where categories_id = '17'

[27] => select parent_id from categories where categories_id = '30'

etc.... etc.... for about 30 queries :blink:

 

Any thoughts as to where else this might be caused?

~Tracy
 

Link to comment
Share on other sites

I agree you should do something about it. I had a similar problem with QPBPP (Quantity Price Break Per Product) where someone found out that PriceFormatter does the same query 5 times for each product in the cart.

 

Ok - this may seem like a really dumb question - but I am at a loss looking through the QPBPP files (both for SPPC, v1.02 and update v1.02) as to which part of the code is actually creating the $pfs object. I think if I can understand which part is creating the object I might be able to start grasping how I should create a class? object? something anyway - for this issue :blush:

~Tracy
 

Link to comment
Share on other sites

which part of the code is actually creating the $pfs object.
That is in application_top.php (the SPPC update, see the upgrade_instructions.txt).

 

When it is callled:

require(DIR_WS_CLASSES . 'PriceFormatterStore.php');
 $pfs = new PriceFormatterStore;

the function PriceFormatterStore in that class is called. This particular one gets the products_id list from the cart and does a number of queries to get all the correct prices for the customer group. Then almost at the end it calls a class function:

$this->addPriceBreakData($product_info[$x]['products_id'], $product_info[$x]);

and if you check what that function does is store all the price information in an array:

function addPriceBreakData ($products_id, $productinfo) {
		$this->pricebreaks[$products_id] = array('products_id' => tep_get_prid($productinfo['products_id']),
			'products_price' => $productinfo['products_price'],
			'products_name' => $productinfo['products_name'], etcetera

If you look up PriceFormatter.php (the class) you can see how the object is used:

function loadProduct($product_id, $language_id=1)
 {
 global $sppc_customer_group_id, $pfs;

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

$pricebreak = $pfs->getPriceBreak($product_id);
// returns false if the price break information is not yet stored
if ($pricebreak != false) {
	$product_info = $pricebreak;
} else {
$sql = "select pd.products_name, p.products_model, p.products_image, p.products_id," .
	" p.products_price, p.products_weight, p.products_quantity, " . etcetera

In PriceFormatter, if the info is not yet stored in $pfs the query is done and the info is added to $pfs later on.

 

IMO you can do something similar, but you have to loop through the part of that array that has as key (categories_id) to get all the products in that category in your menu.

Link to comment
Share on other sites

the new_products.php file - just curious - why doesn't the first query check for the category?
Because it is the default one, for the front page.

 

Also - I made the changes to my includes/functions/general.php that were listed here:

http://www.oscommerce.com/forums/index.php?s=&showtopic=119077&view=findpost&p=1024887

 

But I still get this weird set of queries on my category pages:

[20] => select parent_id from categories where categories_id = '17'

 

Any thoughts as to where else this might be caused?

I don't know if that post prohibits all those queries. Harald suggested (quite some time ago) to port the category class in osC 3 back to MS 2.2 to solve that. I briefly looked at that and decided to let it pass :)

Link to comment
Share on other sites

I don't know if that post prohibits all those queries. Harald suggested (quite some time ago) to port the category class in osC 3 back to MS 2.2 to solve that. I briefly looked at that and decided to let it pass :)

 

Well - since I'm using osC 2.2 MS2-060817 I don't think that is the problem. Thanks for trying though! I'll just have to keep looking. Maybe there is a redundant function in STS like the tep_get_path function in general.php that just needs the same edits :blush:

~Tracy
 

Link to comment
Share on other sites

yes i have enabled that payment and the credit card... only the credit card is shown in the payment method
There is no reason IMHO why this should not work when you have SPPC installed. Unless the user you are logged-in has would not have been presented that option either.
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...