Jump to content
scendent

Seperate Pricing Per Customer v3.5

Recommended Posts

Hey Jan,

 

Question on the quantity price breaks. I currently in product info have it viewing like this:

 

MSRP: $20.00

Your Price: $4.00

 

And line in product_info.php is,

 

<?php

if ($customer_group_id > 0) {

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

echo TEXT_RETAIL_PRICE; echo $retail_price;

}

?>

<BR>

<?php echo TEXT_YOUR_PRICE; ?><?php echo $products_price; ?>

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

 

And when I initiate a quantity discount on an item I get this,

 

MSRP: $20.00

Your Price:

$4.00

3+ $3.00

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

I would prefer to remove the $4.00 from current line and bring back to original view, so it looks like this,

 

MSRP: $20.00

Your Price: $4.00

3+ $3.00

 

Thanks JR

Share this post


Link to post
Share on other sites
Question on the quantity price breaks. I currently in product info have it viewing like this:

 

MSRP: $20.00

Your Price: $4.00

$products_price has become a table that is made in PriceFormatter.php (getPriceString is the name of the function I think. It is better to change the appearance there.

Share this post


Link to post
Share on other sites

Hey Jan,

 

Thank you. I did fined it there, but one issue I can not get around. Now if a product has a quantity discount it shows,

 

MSRP: $20.00

Your Price: $4.00

3+ $3.00

 

Done here in getPriceString function,

 

else

{

$lc_text = '<table align="top" border="0" cellspacing="0" cellpadding="0">';

$lc_text .= '<tr><TD class="pageHeading">Your Price:  </TD><td align="center" class="pageHeading" colspan="2">'

. $currencies->display_price($this->thePrice,

tep_get_tax_rate($this->taxClass))

. '</td></tr>';

 

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

 

But item without quanity discount, I loose the "Your Price"?

 

MSRP: $300.00

$60.00

 

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

 

I am trying to see in Priceformatter.php where when an item does not have quanity discount, where the group price is loaded?

 

Thanks Jr

Share this post


Link to post
Share on other sites
I am trying to see in Priceformatter.php where when an item does not have quanity discount, where the group price is loaded?

As far as I can see it is in the last else before it says return $lc_text in the function getPriceString:

else {
		$lc_text = ' '
		  . $currencies->display_price($this->thePrice,
				   tep_get_tax_rate($this->taxClass))
		  . ' ';
	}
	  }

return $lc_text;
 }

 function getPriceStringShort() {

Share this post


Link to post
Share on other sites

Hello All, Please Help If this has already been covered Im sorry but my head is spinning from trying to find the answer.

My problem is that none of my products show up, they all say "Product not found" It doesnt matter if I am signed in or not.

 

Never mind Im an Idiot, I replaced a line in product_info instead of adding it :sweating:

Edited by 20Bps

Share this post


Link to post
Share on other sites

Can anyone please help with this problem.

 

When I try and use the Manufacturer drop down box, I get this error message.

 

 

Let's See What We Have Here Let's See What We Have Here

1054 - Unknown column 'p.products_id' in 'on clause'

 

select count(p.products_id) as total from products p, products_description pd, manufacturers m left join specials_retail_prices s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '11'

 

[TEP STOP]

 

Can anyone please help. I know a few people have come across, but not sure if its been solved.

 

Thanks in advance.

Share this post


Link to post
Share on other sites
Can anyone please help. I know a few people have come across, but not sure if its been solved.

Looks like your host upgraded to MySQL5. Try the index.php and the advanced_search_results from version 4.2.0 which are adapted to work with MySQL5.

Share this post


Link to post
Share on other sites

Hi Jan!

 

Way back when you helped me with a project - and I'm now attempting to tweak it and I'm stumped. I'm sure I'm missing something quite simple (but nicotine withdrawl seems to be keeping me from finding it - the hazards of quitting smoking- LOL).

 

Would you be willing to give it a look? Remember the "products in dynamenu too" fun? Well, I was trying to implement that into the simpler "Category Box as Nested Unordered List" contribution. The good thing is that it is pulling the products and inserting them into the menu - the bad thing is that they are not getting the <ul><li> info in front or the closing </li>'s and </ul> at the end.

 

I can see the script that is doing this for the categories and subcategories - but haven't figured out how to tweak it for the products.

 

The other issue is that it is showing the link rather than creating the <a href= and </a> tags around the link being pulled by the tep_href_link( section (only for the products).

 

This is what I have thus far for ul_categories.php

<?php
/*
 $Id: ul_categories.php,v 1.00 2006/04/30 01:13:58 nate_02631 Exp $

Outputs the store category list as a proper unordered list, opening up
possibilities to use CSS to style as drop-down/flyout, collapsable or 
other menu types.

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

 Copyright (c) 2006 Nate Welch http://www.natewelch.com

 Released under the GNU General Public License
*/

// BEGIN Configuration options

 // Set to false to display the unordered list only. Set to true to display in
// a regular box. The former is useful for better integrating the menu with your layout.
$show_ulcats_as_box = true;

// Indicates whether or not to render your entire category list or just the root categories
// and the currently selected submenu tree. Rendering the full list is useful for dynamic menu
// generation where you want the user to have instant access to all categories. The other option
// is the default oSC behaviour, when the subcats aren't available until the parent is clicked. 
$show_full_tree = true;	

// This is the CSS *ID* you want to assign to the UL (unordered list) containing
// your category menu. Used in conjuction with the CSS list you create for the menu.
// This value cannot be blank.
$idname_for_menu = 'nav';

// This is the *CLASSNAME* you want to tag a LI to indicate the selected category.
// The currently selected category (and its parents, if any) will be tagged with
// this class. Modify your stylesheet as appropriate. Leave blank or set to false to not assign a class. 
$classname_for_selected = 'selected';

// This is the *CLASSNAME* you want to tag a LI to indicate a category has subcategores.
// Modify your stylesheet to draw an indicator to show the users that subcategories are
// available. Leave blank or set to false to not assign a class. 	
$classname_for_parent = 'daddy';

// This is the HTML that you would like to appear before your categories menu if *not*
// displaying in a standard "box". This is useful for reconciling tables or clearing
// floats, depending on your layout needs.	
$before_nobox_html = '';

// This is the HTML that you would like to appear after your categories menu if *not*
// displaying in a standard "box". This is useful for reconciling tables or clearing
// floats, depending on your layout needs.	
 $after_nobox_html = '<div style="clear: both;">';	


// END Configuration options

// Global Variables
$GLOBALS['this_level'] = 0;

// Initialize HTML and info_box class if displaying inside a box
if ($show_ulcats_as_box) {
echo '<tr><td>';
$info_box_contents = array();
$info_box_contents[] = array('text' => BOX_HEADING_CATEGORIES);
new infoBoxHeading($info_box_contents, true, false);					
}

// Generate a bulleted list (uses configuration options above)
$categories_string = tep_make_cat_ullist();

// Output list inside a box if specified, otherwise just output unordered list
if ($show_ulcats_as_box) {
$info_box_contents = array();
$info_box_contents[] = array('text' => $categories_string);
new infoBox($info_box_contents);
	echo '</td></tr>';	
} else {
	echo $before_nobox_html;	
echo $categories_string;
	echo $after_nobox_html;
}

// Create the root unordered list
function tep_make_cat_ullist($rootcatid = 0, $maxlevel = 0){

global $idname_for_menu, $cPath_array, $show_full_tree, $languages_id, $customer_group_id;

// Modify category query if not fetching all categories (limit to root cats and selected subcat tree)
	if (!$show_full_tree) {
	$parent_query	= 'AND (c.parent_id = "0"';	

			if (isset($cPath_array)) {

				$cPath_array_temp = $cPath_array;

				foreach($cPath_array_temp AS $key => $value) {
						$parent_query	.= ' OR c.parent_id = "'.$value.'"';
					}

					unset($cPath_array_temp);
			}	

	$parent_query .= ')';				
	} else {
	$parent_query	= '';	
	}

	$result = tep_db_query('select c.categories_id, cd.categories_id, cd.categories_name, c.parent_id from ' . TABLE_CATEGORIES . ' c LEFT JOIN ' . TABLE_CATEGORIES_DESCRIPTION . ' cd USING(categories_id) where cd.language_id="' . (int)$languages_id .'" and find_in_set("' . $customer_group_id . '", categories_hide_from_groups) = 0 '.$parent_query.' order by sort_order, cd.categories_name');

	while ($row = tep_db_fetch_array($result)) {				
	$table[$row['parent_id']][$row['categories_id']] = $row['categories_name'];
}

$output .= '<ul id="'.$idname_for_menu.'">';
$output .= tep_make_cat_ulbranch($rootcatid, $table, 0, $maxlevel);

	// Close off nested lists
for ($nest = 0; $nest <= $GLOBALS['this_level']; $nest++) {
	$output .= '</ul>';		
	}

return $output;
}

// Create the branches of the unordered list
function tep_make_cat_ulbranch($parcat, $table, $level, $maxlevel) {

global $cPath_array, $classname_for_selected, $classname_for_parent, $customer_group_id, $pic;

$list = $table[$parcat];

while(list($key,$val) = each($list)){

	if ($GLOBALS['this_level'] != $level) {

			if ($GLOBALS['this_level'] < $level) {
					$output .= "\n".'<ul>';
				} else {
			for ($nest = 1; $nest <= ($GLOBALS['this_level'] - $level); $nest++) {
				$output .= '</ul></li>'."\n";	
				}
/*							
							if ($GLOBALS['this_level'] -1 == $level)
$output .= '</ul></li>'."\n";
elseif ($GLOBALS['this_level'] -2 == $level)
$output .= '</ul></li></ul></li>'."\n";
elseif ($GLOBALS['this_level'] -3 == $level)
$output .= '</ul></li></ul></li></ul></li>'."\n";
elseif ($GLOBALS['this_level'] -4 == $level)
$output .= '</ul></li></ul></li></ul></li></ul></li>'."\n"; 
*/							
					}			

			$GLOBALS['this_level'] = $level;
	}

	if (isset($cPath_array) && in_array($key, $cPath_array) && $classname_for_selected) {
		$this_cat_class = ' class="'.$classname_for_selected.'"';
	} else {
		$this_cat_class = '';		
		}	

	$output .= '<li'.$this_cat_class.'><a href="';

	if (!$level) {
				unset($GLOBALS['cPath_set']);
					$GLOBALS['cPath_set'][0] = $key;
		$cPath_new = 'cPath=' . $key;

	} else {
					$GLOBALS['cPath_set'][$level] = $key;		
		$cPath_new = 'cPath=' . implode("_", array_slice($GLOBALS['cPath_set'], 0, ($level+1)));
	}

	if (tep_has_category_subcategories($key) && $classname_for_parent) {
		$this_parent_class = ' class="'.$classname_for_parent.'"';
	} else {
		$this_parent_class = '';		
		}				

	$output .= tep_href_link(FILENAME_DEFAULT, $cPath_new) . '"'.$this_parent_class.'>'.$val;		

	if (SHOW_COUNTS == 'true') {
		$products_in_category = tep_count_products_in_category($key);
		if ($products_in_category > 0) {
			$output .= ' (' . $products_in_category . ')';
		}
	}

	$output .= '</a>';	

	if (!tep_has_category_subcategories($key)) {
		$output .= '</li>'."\n";	
	}	

//BOF product listing
$_products_in_category_array = $pic->getProductsInCategory($key);
// returns false if no products in the category
if ($_products_in_category_array != false) {
	$products_in_category_array = $_products_in_category_array;

foreach ($products_in_category_array as $array_key => $products_in_category) {

$product_id_h = 'products_id='.$products_in_category ['products_id'];
$product_name_h = $products_in_category ['products_name'];

if ($GLOBALS['products_id'] == $products_in_category ['products_id']) {
  $this_parent_class = ' class="'.$classname_for_parent.'"';
 } else {
  $this_parent_class = '';
 }

$output .= tep_href_link(FILENAME_PRODUCT_INFO, $product_id_h).$product_name_h . $this_parent_class . $val;
}// end if ($_products_in_category_array != false)
} //EOF product listing				


	if ((isset($table[$key])) AND (($maxlevel > $level + 1) OR ($maxlevel == '0'))) {
		$output .= tep_make_cat_ulbranch($key,$table,$level + 1,$maxlevel);
	}

	} // End while loop

return $output;
}	

?>

 

I don't think I need to change anything in the includes/classes file we created - but just in case I've pasted it below:

<?php
/* $Id: ProductsInCategory.php v1.0 2007/02/16
an object to store the products within each category once queried by the box dm_categories.php
to avoid it being queried multiple times

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

Copyright (c) 2007 osCommerce

Released under the GNU General Public License
*/

class ProductsInCategory {
var $pic_data = array();

function ProductsInCategory() {
	global $languages_id;

	$products_query = tep_db_query("select p2c.categories_id, p.products_id, p.products_status, pd.products_name as product, pd.products_id as product_id from " . TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c USING(products_id) LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd USING(products_id) where pd.language_id = '" . (int)$languages_id . "' AND p.products_status='1' order by p2c.categories_id, pd.products_name");

	while ($product_info = tep_db_fetch_array($products_query)) {
		$this->addProductsInCategory($product_info['categories_id'], $product_info);
		} // end while ($product_info = tep_db_fetch_array($products_query))
	} // end function ProductsInCategory

function addProductsInCategory ($categories_id, $product_info) {
	$this->pic_data[$categories_id][] = array('products_id' => $product_info['products_id'], 
											  'products_name' => $product_info['product'],
											  // we already know the category but might be handy to have it here too
											  'products_category' => $categories_id);
	} // end function addProductsInCategory ($categories_id, $product_info)

	function getProductsInCategory($categories_id) {
		if (isset($this->pic_data[$categories_id])){
			foreach ($this->pic_data[$categories_id] as $key => $_product_data) {
				$product_data[] = $_product_data;
				} // end foreach
					 return $product_data;
			}else{
	return false;
					   }
	} // end function getProductsInCategory($categories_id)
} // end Class ProductsInCategory
?>

 

If you are able to help it is greatly appreciated! I will keep attempting to plug away at it over here - who knows - I might stumble upon what I need to do - LOL :blush:


~Tracy
 

Share this post


Link to post
Share on other sites
Looks like your host upgraded to MySQL5. Try the index.php and the advanced_search_results from version 4.2.0 which are adapted to work with MySQL5.

 

Thank you Jan, Can you point me in the direction where I can find these files. I have STS also installed.

Share this post


Link to post
Share on other sites
Thank you Jan, Can you point me in the direction where I can find these files. I have STS also installed.

 

This is the Error:

 

1054 - Unknown column 'p.products_id' in 'on clause'

 

select count(p.products_id) as total from products p, products_description pd, manufacturers m left join specials_retail_prices s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '24'

 

[TEP STOP]

 

and this is my index.php file:

<?php
/*
 $Id: index.php,v 1.1 2003/06/11 17:37:59 hpdl Exp $ 
 adapted for Separate Pricing per Customer 2005/02/06
 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/

 require('includes/application_top.php');
#################
$page_query = tep_db_query("select 
						   p.pages_id, 
						   p.sort_order, 
						   p.status, 
						   s.pages_title, 
						   s.pages_html_text
						from 
						   " . TABLE_PAGES . " p LEFT JOIN " .TABLE_PAGES_DESCRIPTION . " s on p.pages_id = s.pages_id 
						where 
						   p.status = 1
						and
						   s.language_id = '" . (int)$languages_id . "'
						and 
						   p.page_type = 1");


$page_check = tep_db_fetch_array($page_query);

$pagetext=stripslashes($page_check[pages_html_text]);


#####################
global $customer_group_id;
 if(!isset($customer_group_id)) { $customer_group_id = '0'; }
// the following cPath references come from application_top.php
 $category_depth = 'top';
 if (isset($cPath) && tep_not_null($cPath)) {
$categories_products_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS_TO_CATEGORIES . " where categories_id = '" . (int)$current_category_id . "'");
$cateqories_products = tep_db_fetch_array($categories_products_query);
if ($cateqories_products['total'] > 0) {
  $category_depth = 'products'; // display products
} else {
  $category_parent_query = tep_db_query("select count(*) as total from " . TABLE_CATEGORIES . " where parent_id = '" . (int)$current_category_id . "'");
  $category_parent = tep_db_fetch_array($category_parent_query);
  if ($category_parent['total'] > 0) {
	$category_depth = 'nested'; // navigate through the categories
  } else {
	$category_depth = 'products'; // category has no products, but display the 'no products' message
  }
}
 }

 require(DIR_WS_LANGUAGES . $language . '/' . FILENAME_DEFAULT);
?>
<!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; ?>"> 
<title><?php echo TITLE; ?></title>
<base href="<?php echo (($request_type == 'SSL') ? HTTPS_SERVER : HTTP_SERVER) . DIR_WS_CATALOG; ?>">
<link rel="stylesheet" type="text/css" href="stylesheet.css">
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="3" cellpadding="3">
 <tr>
<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="0" cellpadding="2">
<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
</table></td>
<!-- body_text //-->
<?php
 if ($category_depth == 'nested') {
$category_query = tep_db_query("select cd.categories_name, c.categories_image from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = '" . (int)$current_category_id . "' and cd.categories_id = '" . (int)$current_category_id . "' and cd.language_id = '" . (int)$languages_id . "'");
$category = tep_db_fetch_array($category_query);
?>
<td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0">
  <tr>
	<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
	  <tr>
		<td class="pageHeading"><?php echo $category['categories_name'] ?></td>
		<td class="pageHeading" align="right"><?php echo tep_image(DIR_WS_IMAGES . $category['categories_image'], $category['categories_name'], HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
	  </tr>
	</table></td>
  </tr>
  <tr>
	<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
  </tr>
  <tr>
	<td><table border="0" width="100%" cellspacing="0" cellpadding="2">
	  <tr>
		<td><table border="0" width="100%" cellspacing="0" cellpadding="2">
		  <tr>
<?php
if (isset($cPath) && strpos('_', $cPath)) {
// check to see if there are deeper categories within the current category
  $category_links = array_reverse($cPath_array);
  for($i=0, $n=sizeof($category_links); $i<$n; $i++) {
	$categories_query = tep_db_query("select count(*) as total from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$category_links[$i] . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "'");
	$categories = tep_db_fetch_array($categories_query);
	if ($categories['total'] < 1) {
	  // do nothing, go through the loop
	} else {
	  $categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$category_links[$i] . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by sort_order, cd.categories_name");
	  break; // we've found the deepest category the customer is in
	}
  }
} else {
  $categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$current_category_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by sort_order, cd.categories_name");
}

$number_of_categories = tep_db_num_rows($categories_query);

$rows = 0;
while ($categories = tep_db_fetch_array($categories_query)) {
  $rows++;
  $cPath_new = tep_get_path($categories['categories_id']);
  $width = (int)(100 / MAX_DISPLAY_CATEGORIES_PER_ROW) . '%';
  echo '				<td align="center" class="smallText" width="' . $width . '" valign="top"><a href="' . tep_href_link(FILENAME_DEFAULT, $cPath_new) . '">' . tep_image(DIR_WS_IMAGES . $categories['categories_image'], $categories['categories_name'], SUBCATEGORY_IMAGE_WIDTH, SUBCATEGORY_IMAGE_HEIGHT) . '<br>' . $categories['categories_name'] . '</a></td>' . "\n";
  if ((($rows / MAX_DISPLAY_CATEGORIES_PER_ROW) == floor($rows / MAX_DISPLAY_CATEGORIES_PER_ROW)) && ($rows != $number_of_categories)) {
	echo '			  </tr>' . "\n";
	echo '			  <tr>' . "\n";
  }
}

// needed for the new products module shown below
$new_products_category_id = $current_category_id;
?>
		  </tr>
		</table></td>
	  </tr>
	  <tr>
		<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
	  </tr>
	  <tr>
		<td><?php include(DIR_WS_MODULES . FILENAME_NEW_PRODUCTS); ?></td>
	  </tr>
	</table></td>
  </tr>
</table></td>
<?php
 } elseif ($category_depth == 'products' || isset($HTTP_GET_VARS['manufacturers_id'])) {
// create column list
$define_list = array('PRODUCT_LIST_MODEL' => PRODUCT_LIST_MODEL,
					 'PRODUCT_LIST_NAME' => PRODUCT_LIST_NAME,
					 'PRODUCT_LIST_MANUFACTURER' => PRODUCT_LIST_MANUFACTURER,
					 'PRODUCT_LIST_PRICE' => PRODUCT_LIST_PRICE,
					 'PRODUCT_LIST_QUANTITY' => PRODUCT_LIST_QUANTITY,
					 'PRODUCT_LIST_WEIGHT' => PRODUCT_LIST_WEIGHT,
					 'PRODUCT_LIST_IMAGE' => PRODUCT_LIST_IMAGE,
					 'PRODUCT_LIST_BUY_NOW' => PRODUCT_LIST_BUY_NOW);

asort($define_list);

$column_list = array();
reset($define_list);
while (list($key, $value) = each($define_list)) {
  if ($value > 0) $column_list[] = $key;
}

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

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

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

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

$select_column_list = '';

for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
  switch ($column_list[$i]) {
	case 'PRODUCT_LIST_MODEL':
	  $select_column_list .= 'p.products_model, ';
	  break;
	case 'PRODUCT_LIST_NAME':
	  $select_column_list .= 'pd.products_name, pd.products_description,';		  break;
	case 'PRODUCT_LIST_MANUFACTURER':
	  $select_column_list .= 'm.manufacturers_name, ';
	  break;
	case 'PRODUCT_LIST_QUANTITY':
	  $select_column_list .= 'p.products_quantity, ';
	  break;
	case 'PRODUCT_LIST_IMAGE':
	  $select_column_list .= 'p.products_image, ';
	  break;
	case 'PRODUCT_LIST_WEIGHT':
	  $select_column_list .= 'p.products_weight, ';
	  break;
  }
}

// show the products of a specified manufacturer
if (isset($HTTP_GET_VARS['manufacturers_id'])) {
  if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only a specific category
// BOF Separate Pricing Per Customer
if ($status_product_prices_table == true) {
$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, tmp_pp.products_price, p.products_tax_class_id, IF(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . $product_prices_table . " as tmp_pp using(products_id), " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";		
} else { // either retail or no need to get correct special prices
$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";
} // end else { // either retail...
// EOF Separate Pricing Per Customer
  } else {
// We show them all
// BOF Separate Pricing Per Customer
	if ($status_product_prices_table == true) {
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, tmp_pp.products_price, p.products_tax_class_id, IF(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . $product_prices_table . " as tmp_pp using(products_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";	
} else { // either retail or no need to get correct special prices
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m  left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
} // end else { // either retail...
// EOF Separate Pricing Per Customer
  }
} else {
// show the products in a given categorie
  if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only specific catgeory;  
// BOF Separate Pricing Per Customer
	if ($status_product_prices_table == true) {
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, tmp_pp.products_price, p.products_tax_class_id, IF(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . $product_prices_table . " as tmp_pp using(products_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";	
	} else { // either retail or no need to get correct special prices
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS_RETAIL_PRICES . " s using(products_id) where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
	} // end else { // either retail...
// EOF Separate Pricing Per Customer
  } else {
// We show them all
// BOF Separate Pricing Per Customer
	if ($status_product_prices_table == true) {
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, tmp_pp.products_price, p.products_tax_class_id, IF(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . $product_prices_table . " as tmp_pp using(products_id) left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS . " p" . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
	} else { // either retail or no need to get correct special prices
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
  } // end else { // either retail...
// EOF Separate Pricing per Customer
  }
}

if ( (!isset($HTTP_GET_VARS['sort'])) || (!ereg('[1-8][ad]', $HTTP_GET_VARS['sort'])) || (substr($HTTP_GET_VARS['sort'], 0, 1) > sizeof($column_list)) ) {
  for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
	if ($column_list[$i] == 'PRODUCT_LIST_NAME') {
	  $HTTP_GET_VARS['sort'] = $i+1 . 'a';
	  $listing_sql .= " order by pd.products_name";
	  break;
	}
  }
} else {
  $sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1);
  $sort_order = substr($HTTP_GET_VARS['sort'], 1);
  $listing_sql .= ' order by ';
  switch ($column_list[$sort_col-1]) {
	case 'PRODUCT_LIST_MODEL':
	  $listing_sql .= "p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
	case 'PRODUCT_LIST_NAME':
	  $listing_sql .= "pd.products_name " . ($sort_order == 'd' ? 'desc' : '');
	  break;
	case 'PRODUCT_LIST_MANUFACTURER':
	  $listing_sql .= "m.manufacturers_name " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
	case 'PRODUCT_LIST_QUANTITY':
	  $listing_sql .= "p.products_quantity " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
	case 'PRODUCT_LIST_IMAGE':
	  $listing_sql .= "pd.products_name";
	  break;
	case 'PRODUCT_LIST_WEIGHT':
	  $listing_sql .= "p.products_weight " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
	case 'PRODUCT_LIST_PRICE':
	  $listing_sql .= "final_price " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
  }
}
?>
<td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0">
  <tr>
	<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
	  <tr>
		<td class="pageHeading"><?php
$category_query1 = tep_db_query("select cd.categories_name,
c.categories_image from " . TABLE_CATEGORIES . " c, " .
TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = '" .
(int)$current_category_id . "' and cd.categories_id = '" . (int)$current_category_id
. "' and cd.language_id = '" . (int)$languages_id . "'");
$category1 = tep_db_fetch_array($category_query1);
if ($category1['categories_name'] != "") {echo $category1['categories_name'];}
else { echo HEADING_TITLE;}
?></td>
<?php
// optional Product List Filter
if (PRODUCT_LIST_FILTER > 0) {
  if (isset($HTTP_GET_VARS['manufacturers_id'])) {
	$filterlist_sql = "select distinct c.categories_id as id, cd.categories_name as name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' order by cd.categories_name";
  } else {
	$filterlist_sql= "select distinct m.manufacturers_id as id, m.manufacturers_name as name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and p.products_id = p2c.products_id and p2c.categories_id = '" . (int)$current_category_id . "' order by m.manufacturers_name";
  }
  $filterlist_query = tep_db_query($filterlist_sql);
  if (tep_db_num_rows($filterlist_query) > 1) {
	echo '			<td align="center" class="main">' . tep_draw_form('filter', FILENAME_DEFAULT, 'get') . TEXT_SHOW . ' ';
	if (isset($HTTP_GET_VARS['manufacturers_id'])) {
	  echo tep_draw_hidden_field('manufacturers_id', $HTTP_GET_VARS['manufacturers_id']);
	  $options = array(array('id' => '', 'text' => TEXT_ALL_CATEGORIES));
	} else {
	  echo tep_draw_hidden_field('cPath', $cPath);
	  $options = array(array('id' => '', 'text' => TEXT_ALL_MANUFACTURERS));
	}
	echo tep_draw_hidden_field('sort', $HTTP_GET_VARS['sort']);
	while ($filterlist = tep_db_fetch_array($filterlist_query)) {
	  $options[] = array('id' => $filterlist['id'], 'text' => $filterlist['name']);
	}
	echo tep_draw_pull_down_menu('filter_id', $options, (isset($HTTP_GET_VARS['filter_id']) ? $HTTP_GET_VARS['filter_id'] : ''), 'onchange="this.form.submit()"');
	echo '</form></td>' . "\n";
  }
}

// Get the right image for the top-right
$image = DIR_WS_IMAGES . 'table_background_list.gif';
if (isset($HTTP_GET_VARS['manufacturers_id'])) {
  $image = tep_db_query("select manufacturers_image from " . TABLE_MANUFACTURERS . " where manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'");
  $image = tep_db_fetch_array($image);
  $image = $image['manufacturers_image'];
} elseif ($current_category_id) {
  $image = tep_db_query("select categories_image from " . TABLE_CATEGORIES . " where categories_id = '" . (int)$current_category_id . "'");
  $image = tep_db_fetch_array($image);
  $image = $image['categories_image'];
}
?>
		<td align="right"><?php echo tep_image(DIR_WS_IMAGES . $image, HEADING_TITLE, HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
	  </tr>
	</table></td>
  </tr>
  <tr>
	<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
  </tr>
  <tr>
	<td><?php include(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING); ?></td>
  </tr>
</table></td>
<?php
 } else { // default page
?>
<td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0">
  <tr>
	<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
	  <tr>
		<td class="pageHeading"><?php echo HEADING_TITLE; ?></td>
		<td class="pageHeading" align="right"><?php echo tep_image(DIR_WS_IMAGES . 'table_background_default.gif', HEADING_TITLE, HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
	  </tr>
	</table></td>
  </tr>
  <tr>
	<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
  </tr>
  <tr>
	<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
	  <tr>
		<td class="main"><?php echo tep_customer_greeting(); ?></td>
	  </tr>
	  <tr>
		<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
	  </tr>
	  <tr>
		<td class="main"><?php echo $pagetext; ?></td>
	  </tr>
	  <tr>
		<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
	  </tr>
	  <tr>
		<td><?php include(DIR_WS_MODULES . FILENAME_NEW_PRODUCTS); ?></td>
	  </tr>
<?php
include(DIR_WS_MODULES . FILENAME_UPCOMING_PRODUCTS);
?>
	</table></td>
  </tr>
</table></td>
<?php
}
?>
<!-- body_text_eof //-->
<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="0" cellpadding="2">
<!-- right_navigation //-->
<?php  require(DIR_WS_INCLUDES . 'column_right.php'); ?>
<!-- right_navigation_eof //-->
</table></td>
 </tr>
</table>
<!-- body_eof //-->

<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
<br>
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

 

Can anyone see the offending code.

 

Thanks in advance.

Share this post


Link to post
Share on other sites
This is the Error:

 

1054 - Unknown column 'p.products_id' in 'on clause'

 

select count(p.products_id) as total from products p, products_description pd, manufacturers m left join specials_retail_prices s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '24'

 

[TEP STOP]

 

Thanks in advance.

 

Looks like you need to update your MySQL queries to work with MySQL 5 - check your queries against these (keep in mind I have Master Products installed as well on this version - you don't need p.products_master or p.products_master_status information out of the database :

 

// show the products of a specified manufacturer
if (isset($HTTP_GET_VARS['manufacturers_id'])) {
  if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only a specific category
// BOF Separate Pricing Per Customer
if ($status_product_prices_table == true) { // ok in mysql 5
$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, tmp_pp.products_price, p.products_tax_class_id, IF(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . $product_prices_table . " as tmp_pp using(products_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c using(products_id) left join " . TABLE_CATEGORIES . " c using(categories_id), " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";		
} else { // either retail or no need to get correct special prices -- changed for mysql 5
$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_CATEGORIES . " c using(categories_id) where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";
} // end else { // either retail...
// EOF Separate Pricing Per Customer
  } else {
// We show them all
// BOF Separate Pricing Per Customer
	if ($status_product_prices_table == true) { // ok in mysql 5
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, tmp_pp.products_price, p.products_tax_class_id, IF(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . $product_prices_table . " as tmp_pp using(products_id) left join " .TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id = p2c.products_id left join " . TABLE_CATEGORIES . " c using(categories_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";	
} else { // either retail or no need to get correct special prices -- changed for mysql 5
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id left join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id = p2c.products_id left join " . TABLE_CATEGORIES . " c using(categories_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
} // end else { // either retail...
// EOF Separate Pricing Per Customer
  }
} else {
// show the products in a given categorie
  if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only specific category  
// BOF Separate Pricing Per Customer
	if ($status_product_prices_table == true) { // ok for mysql 5
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, tmp_pp.products_price, p.products_tax_class_id, IF(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . $product_prices_table . " as tmp_pp using(products_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_CATEGORIES . " c using(categories_id) where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";	
	} else { // either retail or no need to get correct special prices -- ok in mysql 5
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS_RETAIL_PRICES . " s using(products_id) left join " . TABLE_CATEGORIES . " c on p2c.categories_id = c.categories_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
	} // end else { // either retail...
// EOF Separate Pricing Per Customer
  } else {
// We show them all
//Master Products
//BOF Separate Pricing Per Customer --last query changed for mysql 5 compaiblity
 if ($status_product_prices_table == true) {
// original, no need to change for mysql 5
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, tmp_pp.products_price, p.products_master, p.products_master_status, p.products_tax_class_id, IF(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . $product_prices_table . " as tmp_pp using(products_id) left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS . " p" . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
  } else { //either retail or no need to get correct special prices -- changed for mysql 5 
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_master, p.products_master_status, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
  } // end else { // either retail...
// EOF Separate Pricing per Customer
 }
}

//Master Products EOF

 

Hope that helps :)


~Tracy
 

Share this post


Link to post
Share on other sites

Thanks Tracy, I thought I was at a dead end.

 

Should I remove the entries from the database, or from the query in the above file.

Share this post


Link to post
Share on other sites
Thanks Tracy, I thought I was at a dead end.

 

Should I remove the entries from the database, or from the query in the above file.

Tracy means to say you have to copy and paste the part that starts with:

// show the products of a specified manufacturer
    if (isset($HTTP_GET_VARS['manufacturers_id'])) {
      if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only a specific category
// BOF Separate Pricing Per Customer
    if ($status_product_prices_table == true) {

and ends with:

      } // end else { // either retail...
// EOF Separate Pricing per Customer

that contain the several $listing_sql's with the ones from the index.php in 4.2.0. That is all (for index.php)

Share this post


Link to post
Share on other sites
Tracy means to say you have to copy and paste the part that starts with:

// show the products of a specified manufacturer
if (isset($HTTP_GET_VARS['manufacturers_id'])) {
  if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only a specific category
// BOF Separate Pricing Per Customer
if ($status_product_prices_table == true) {

and ends with:

	  } // end else { // either retail...
// EOF Separate Pricing per Customer

that contain the several $listing_sql's with the ones from the index.php in 4.2.0. That is all (for index.php)

Thanks Jan & Tracy, you help and advise is excellant as always. Problem Fixed - at least for know. :rolleyes:

Share this post


Link to post
Share on other sites
Thanks Jan & Tracy, you help and advise is excellant as always. Problem Fixed - at least for know. :rolleyes:

 

Thanks Jan!

Rob - yw & just remember to remove the products_master and products_master_status references if they give you any errors :)


~Tracy
 

Share this post


Link to post
Share on other sites
Thanks Jan!

Rob - yw & just remember to remove the products_master and products_master_status references if they give you any errors :)

 

Its working Perfect, I have been pulling my hair out (not that I have much left).

 

I am so appreciative of the help from forum members, If I could help in any way I would as well.

 

Thanks Again.

Share this post


Link to post
Share on other sites

LOL - You are helping - even if you don't realize it ;)

 

Whenever you post a "problem" on the forums, and then also make sure the "solution" is posted as well - it helps everyone else who ever comes across that same problem can search for their problem and find your posts with the solution :thumbsup:

 

I don't know how many hours of hair pulling I've been able to save by searching these forums :blush:

 

 

Its working Perfect, I have been pulling my hair out (not that I have much left).

 

I am so appreciative of the help from forum members, If I could help in any way I would as well.

 

Thanks Again.


~Tracy
 

Share this post


Link to post
Share on other sites

Screen-prices excl. TAX BUT Invoice incl. TAX

 

I am using "CreLoaded Pro 6.2 B2B patch-12". In the Netherlands the Wholesale-customers are used to see their prices on the screen excl. TAX. But the invoice should be incl. TAX.

 

My problem is that for Wholesale-customers the screen-prices AND the invoice are both excl. TAX. That is always the case, no matter the value of Tax-Exempt. What I want is the screen-prices during shopping excl. TAX and at the checkout-proces: An invoice with included TAX.

 

I have checked 4 evenings now in the sources. Finally found out that the product-records in the Order-table do contain a field products_tax, but that field is left 0 if the prices have to be shown excl. TAX.

 

Does anybody know a solution (screen-prices excl. TAX BUT invoice incl. TAX)?

 

Gr. Geert Maris

Share this post


Link to post
Share on other sites
I am using "CreLoaded Pro 6.2 B2B patch-12".

I have no knowledge of that fork...

 

In the Netherlands the Wholesale-customers are used to see their prices on the screen excl. TAX. But the invoice should be incl. TAX.

 

My problem is that for Wholesale-customers the screen-prices AND the invoice are both excl. TAX. That is always the case, no matter the value of Tax-Exempt. What I want is the screen-prices during shopping excl. TAX and at the checkout-proces: An invoice with included TAX.

 

I have checked 4 evenings now in the sources. Finally found out that the product-records in the Order-table do contain a field products_tax, but that field is left 0 if the prices have to be shown excl. TAX.

 

Does anybody know a solution (screen-prices excl. TAX BUT invoice incl. TAX)?

Obviously the wholesale customers are not tax exempt so they cannot be made tax exempt. Then it is a matter of finding where a price is shown and make sure that a tax rate of zero is used for formatting the price.

 

So let's say this code that sets the product price in product_info.php (around line 105):

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

Instead of the tep_get_tax_rate($product_info['products_tax_class_id']) you will have to use your own variable.

Something like:

if ($customer_group_id > 0) {
 $tax_rate = 0;
 } else {
 $tax_rate = tep_get_tax_rate($product_info['products_tax_class_id']);
} 
$products_price = $currencies->display_price($product_info['products_price'], $tax_rate);

Since I do not know CreLoaded you will have to see if this applies there also.

Share this post


Link to post
Share on other sites

I can go in to a customers account and change them to dealer from retail pricing. I would also like to so i can edit there address to be Commercial so they get the discounted Commercial shipping rates from UPS. I use the UPS XML shipping mod witch is defaulted to residential.

 

Thanks

Share this post


Link to post
Share on other sites
I can go in to a customers account and change them to dealer from retail pricing. I would also like to so i can edit there address to be Commercial so they get the discounted Commercial shipping rates from UPS. I use the UPS XML shipping mod witch is defaulted to residential.

I'm sure this could be hacked into UPSXML but from what I understood from the UPS docs this is no longer necessary: UPS decides themselves if an address is commercial or residential and gives rates accordingly.

Share this post


Link to post
Share on other sites
The quotes iv been getting are way over what commercial rates should be. I might be using a older UPS XML but i have a radial button clicked on residential.

Then change it to commercial and try again I would say.

Share this post


Link to post
Share on other sites

Multi Vendor Store (MVS) adding Separate Pricing Per Customer (SPPC)

 

Hi Guys.

 

I've spent hours and hours looking through this thread and trying various things to get this contribution working on my MVS installation. However some of the files that have been modified for MVS are way different from what is required for SPPC. Not being a programmer has stopped me getting this working.

 

Is there any kind sole (season of goodwill etc. :) ) out there who could forward me a set of files that have been modified for MVS and SPPC. Alternatively is there anyone who could do an install for me on a sponsored basis please?

 

Thanks in advance for any help :)

 

Steve


____________________________________________________________________

____________________________________________________________________

Share this post


Link to post
Share on other sites

I'm sure it is in this thread and I've missed this somewhere, but an hour of searching has turned up nada.

 

I've got 3 price groups setup; retail, member, and wholesale. Right now if I create a discount coupon for say 10% - it takes the 10% off of the discount group pricing for the item. I'd like it to always take the discount off of the retail price.

 

This is probably easier than I'm making it, but I can't find it. I've tried several solutions - but nothing has worked correctly yet.

 

Thanks for any help and thanks for this contribution! It is working great. I just opened this new store (still adding products every day) www.store.commoto.com and have enjoyed being able to setup the discount groups!

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

×