Seperate Pricing Per Customer v3.5

[Jan Zonjee]

LizzyShi,

Our clients can enjoy preferrable price if buy 5 products in sports category, either all in main category or some in different subcategories.

Can you tell me how to achieve this funtion?

I think the only thing that needs changing is the sql in the classes PriceFormatter.php and shopping_cart.php. For the PriceFormatter the one in the function loadProduct should become:

//   BOF Price Break for SPPC mod, price break per category
	" p.products_tax_class_id, IF (c.parent_id = '0', c.categories_id, parent_id) AS categories_id," .
	" NULL as specials_new_products_price" .
	" from " . TABLE_PRODUCTS_DESCRIPTION . " pd," .
	"	  " . TABLE_PRODUCTS . " p" .
" LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c using(products_id) LEFT JOIN " . TABLE_CATEGORIES . " c using(categories_id) " .
//   EOF Price Break for SPPC mod, price break per category

and the product_query around line 50 in classes/shopping_cart.php:

	  $products_query = tep_db_query("select p2c.products_id, IF (c.parent_id = '0', c.categories_id, c.parent_id) AS categories_id, customers_basket_quantity from " . TABLE_CUSTOMERS_BASKET . " LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c using(products_id) LEFT JOIN " . TABLE_CATEGORIES . " c using(categories_id) where customers_id = '" . (int)$customer_id . "'");

I think that takes care of it.

[lizzyshi]

LizzyShi,

I think the only thing that needs changing is the sql in the classes PriceFormatter.php and shopping_cart.php. For the PriceFormatter the one in the function loadProduct should become:

...........

I think that takes care of it.

 

Dear Janz,

 

I've tried with your code but it doesn't work. Is there anything wrong with my code?

 

Priceformatter.php

<?php
/*
 $Id: PriceFormatter.php,v 1.6 2003/06/25 08:29:26 petri Exp $
 adapted for Separate Pricing Per Customer v4 2005/03/20
 adapted for price break per category 2005/09/03
 including an optimization to avoid double queries for the same info

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

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/

/*
PriceFormatter.php - module to support quantity pricing

Created 2003, Beezle Software based on some code mods by WasaLab Oy (Thanks!)
*/

class PriceFormatter {
 var $hiPrice;
 var $lowPrice;
 var $quantity;
 var $hasQuantityPrice;

 function PriceFormatter($prices=NULL) {
$this->productsID = -1;
//   BOF Price Break for SPPC mod, price break per category
$this->category = '';
//   EOF Price Break for SPPC mod, price break per category
$this->hasQuantityPrice=false;
$this->hasSpecialPrice=false;

$this->hiPrice=-1;
$this->lowPrice=-1;

for ($i=1; $i<=8; $i++){
  $this->quantity[$i] = -1;
  $this->prices[$i] = -1;
}
$this->thePrice = -1;
$this->specialPrice = -1;
$this->qtyBlocks = 1;

if($prices)
  $this->parse($prices);
 }

 function encode() {
$str = $this->productsID . ":"
	   . (($this->hasQuantityPrice == true) ? "1" : "0") . ":"
	   . (($this->hasSpecialPrice == true) ? "1" : "0") . ":"
	   . $this->quantity[1] . ":"
	   . $this->quantity[2] . ":"
	   . $this->quantity[3] . ":"
	   . $this->quantity[4] . ":"
	   . $this->quantity[5] . ":"
	   . $this->quantity[6] . ":"
	   . $this->quantity[7] . ":"
	   . $this->quantity[8] . ":"
	   . $this->price[1] . ":"
	   . $this->price[2] . ":"
	   . $this->price[3] . ":"
	   . $this->price[4] . ":"
	   . $this->price[5] . ":"
	   . $this->price[6] . ":"
	   . $this->price[7] . ":"
	   . $this->price[8] . ":"
	   . $this->thePrice . ":"
	   . $this->specialPrice . ":"
	   . $this->qtyBlocks . ":"
	   . $this->taxClass;
return $str;
 }

 function decode($str) {
list($this->productsID,
	 $this->hasQuantityPrice,
	 $this->hasSpecialPrice,
	 $this->quantity[1],
	 $this->quantity[2],
	 $this->quantity[3],
	 $this->quantity[4],
	 $this->quantity[5],
	 $this->quantity[6],
	 $this->quantity[7],
	 $this->quantity[8],
	 $this->price[1],
	 $this->price[2],
	 $this->price[3],
	 $this->price[4],
	 $this->price[5],
	 $this->price[6],
	 $this->price[7],
	 $this->price[8],
	 $this->thePrice,
	 $this->specialPrice,
	 $this->qtyBlocks,
	 $this->taxClass) = explode(":", $str);

$this->hasQuantityPrice = (($this->hasQuantityPrice == 1) ? true : false);
$this->hasSpecialPrice = (($this->hasSpecialPrice == 1) ? true : false);
 }

 function parse($prices) {
$this->productsID = $prices['products_id'];
//   BOF Price Break for SPPC mod, price break per category
$this->category = $prices['categories_id'];
//   EOF Price Break for SPPC mod, price break per category
$this->hasQuantityPrice=false;
$this->hasSpecialPrice=false;

$this->quantity[1]=$prices['products_price1_qty'];
$this->quantity[2]=$prices['products_price2_qty'];
$this->quantity[3]=$prices['products_price3_qty'];
$this->quantity[4]=$prices['products_price4_qty'];
$this->quantity[5]=$prices['products_price5_qty'];
$this->quantity[6]=$prices['products_price6_qty'];
$this->quantity[7]=$prices['products_price7_qty'];
$this->quantity[8]=$prices['products_price8_qty'];

$this->thePrice=$prices['products_price'];
$this->specialPrice=$prices['specials_new_products_price'];
$this->hasSpecialPrice=tep_not_null($this->specialPrice);

$this->price[1]=$prices['products_price1'];
$this->price[2]=$prices['products_price2'];
$this->price[3]=$prices['products_price3'];
$this->price[4]=$prices['products_price4'];
$this->price[5]=$prices['products_price5'];
$this->price[6]=$prices['products_price6'];
$this->price[7]=$prices['products_price7'];
$this->price[8]=$prices['products_price8'];


 /*
   Change support special prices
   If any price level has a price greater than the special
   price lower it to the special price
*/
if ($this->hasSpecialPrice == true) {
	for($i=1; $i<=8; $i++) {
		if ($this->price[$i] > $this->specialPrice)
			$this->price[$i] = $this->specialPrice;
	}
}
//end changes to support special prices

$this->qtyBlocks=$prices['products_qty_blocks'];

$this->taxClass=$prices['products_tax_class_id'];

if ($this->quantity[1] > 0) {
  $this->hasQuantityPrice = true;
  $this->hiPrice = $this->thePrice;
  $this->lowPrice = $this->thePrice;

  for($i=1; $i<=8; $i++) {
if($this->quantity[$i] > 0) {
  if ($this->price[$i] > $this->hiPrice) {
	$this->hiPrice = $this->price[$i];
  }
  if ($this->price[$i] < $this->lowPrice) {
	$this->lowPrice = $this->price[$i];
  }
}
  }
}
 }
 // function loadProductSppc is Separate Pricing Per Customer only
 function loadProductSppc($product_id, $language_id=1, $product_info)
 {

 global $sppc_customer_group_id;
 if(!tep_session_is_registered('sppc_customer_group_id')) { 
 $customer_group_id = '0';
 } else {
  $customer_group_id = $sppc_customer_group_id;
 }
 if ($customer_group_id != '0') {
  $customer_group_price_query = tep_db_query("select customers_group_price, products_price1, products_price2, products_price3, products_price4, products_price5, products_price6, products_price7, products_price8, products_price1_qty, products_price2_qty, products_price3_qty, products_price4_qty, products_price5_qty, products_price6_qty, products_price7_qty, products_price8_qty, products_qty_blocks from " . TABLE_PRODUCTS_GROUPS . " where products_id = '" . (int)$product_id. "' and customers_group_id =  '" . $customer_group_id . "'");

	if ($customer_group_price = tep_db_fetch_array($customer_group_price_query)) {
	$product_info['products_price']= $customer_group_price['customers_group_price'];
for ($i = 1; $i < 9; $i++) {
	$product_info['products_price'.$i.''] = $customer_group_price['products_price'.$i.''];
	$product_info['products_price'.$i.'_qty'] = $customer_group_price['products_price'.$i.'_qty'];
} // end if ($customer_group_price = tep_db_fetch_array($customer_group_price_query))
$product_info['products_qty_blocks'] = $customer_group_price['products_qty_blocks'];
} else { // there is no price for the item in products_groups: retail price breaks need to nulled
	for ($i = 1; $i < 9; $i++) {
	$product_info['products_price'.$i.''] = '0.0000';
	$product_info['products_price'.$i.'_qty'] = '0';
	} // end if ($customer_group_price = tep_db_fetch_array($customer_group_price_query))
	$product_info['products_qty_blocks'] = '1';
}
 } // end if ($customer_group_id != '0')
 // now get the specials price for this customer_group and add it to product_info array
 $special_price_query = tep_db_query("select specials_new_products_price from " . TABLE_SPECIALS . " where products_id = " . (int)$product_id . " and status ='1' and customers_group_id = '" . $customer_group_id . "'");
 if ($specials_price = tep_db_fetch_array($special_price_query)) {
  $product_info['specials_new_products_price'] = $specials_price['specials_new_products_price'];
 }

$this->parse($product_info);
return $product_info;
 }

function loadProduct($product_id, $language_id=1)
 {
 global $sppc_customer_group_id;
 if(!tep_session_is_registered('sppc_customer_group_id')) { 
 $customer_group_id = '0';
 } else {
  $customer_group_id = $sppc_customer_group_id;
 }

$sql = "select pd.products_name, p.products_model, p.products_image, p.products_id," .
" p.products_price, p.products_weight," .
" p.products_price1,p.products_price2,p.products_price3,p.products_price4, p.products_price5,p.products_price6,p.products_price7,p.products_price8," .
" p.products_price1_qty,p.products_price2_qty,p.products_price3_qty,p.products_pri
ce4_qty, p.products_price5_qty,p.products_price6_qty,p.products_price7_qty,p.products_pri
ce8_qty," .
" p.products_qty_blocks," .
// BOF Price Break for SPPC mod, price break per category
" p.products_tax_class_id, IF (c.parent_id = '0', c.categories_id, c.parent_id) AS categories_id," .
" NULL as specials_new_products_price" .
" from " . TABLE_PRODUCTS_DESCRIPTION . " pd," .
" " . TABLE_PRODUCTS . " p" .
" LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c using(products_id) LEFT JOIN " . TABLE_CATEGORIES . " c using(categories_id) " .
// EOF Price Break for SPPC mod, price break per category
	" where p.products_status = '1'" .
	"   and p.products_id = '" . (int)$product_id . "'" .
	"   and pd.products_id = '" . (int)$product_id . "'" .
	"   and pd.language_id = '". (int)$language_id ."'";

$product_info_query = tep_db_query($sql);
$product_info = tep_db_fetch_array($product_info_query);

 if ($customer_group_id != '0') {
  $customer_group_price_query = tep_db_query("select customers_group_price, products_price1, products_price2, products_price3, products_price4, products_price5, products_price6, products_price7, products_price8, products_price1_qty, products_price2_qty, products_price3_qty, products_price4_qty, products_price5_qty, products_price6_qty, products_price7_qty, products_price8_qty, products_qty_blocks from " . TABLE_PRODUCTS_GROUPS . " where products_id = '" . (int)$product_id. "' and customers_group_id =  '" . $customer_group_id . "'");

	if ($customer_group_price = tep_db_fetch_array($customer_group_price_query)) {
	$product_info['products_price']= $customer_group_price['customers_group_price'];
for ($i = 1; $i < 9; $i++) {
	$product_info['products_price'.$i.''] = $customer_group_price['products_price'.$i.''];
	$product_info['products_price'.$i.'_qty'] = $customer_group_price['products_price'.$i.'_qty'];
} // end if ($customer_group_price = tep_db_fetch_array($customer_group_price_query))
$product_info['products_qty_blocks'] = $customer_group_price['products_qty_blocks'];
} else { // there is no price for the item in products_groups: retail price breaks need to nulled
	for ($i = 1; $i < 9; $i++) {
	$product_info['products_price'.$i.''] = '0.0000';
	$product_info['products_price'.$i.'_qty'] = '0';
	} // end if ($customer_group_price = tep_db_fetch_array($customer_group_price_query))
	$product_info['products_qty_blocks'] = '1';
}
 } // end if ($customer_group_id != '0')
 // now get the specials price for this customer_group and add it to product_info array
 $special_price_query = tep_db_query("select specials_new_products_price from " . TABLE_SPECIALS . " where products_id = " . (int)$product_id . " and status ='1' and customers_group_id = '" . $customer_group_id . "'");
 if ($specials_price = tep_db_fetch_array($special_price_query)) {
  $product_info['specials_new_products_price'] = $specials_price['specials_new_products_price'];
 }

$this->parse($product_info);
return $product_info;
 }

 function computePrice($qty, $no_of_other_items_in_cart_from_same_cat = 0)
 {
$qty = $this->adjustQty($qty);
$qty += $no_of_other_items_in_cart_from_same_cat;

// Compute base price, taking into account the possibility of a special
$price = ($this->hasSpecialPrice === TRUE) ? $this->specialPrice : $this->thePrice;

for ($i=1; $i<=8; $i++)
	if (($this->quantity[$i] > 0) && ($qty >= $this->quantity[$i]))
		$price = $this->price[$i];

return $price;
 }

 function adjustQty($qty) {
// Force QTY_BLOCKS granularity
$qb = $this->getQtyBlocks();
if ($qty < 1)
	$qty = 1;

if ($qb >= 1)
{
	if ($qty < $qb)
		$qty = $qb;

	if (($qty % $qb) != 0)
		$qty += ($qb - ($qty % $qb));
}
return $qty;
 }

 function getQtyBlocks() {
return $this->qtyBlocks;
 }

 //   BOF Price Break for SPPC mod, price break per category
 function get_category() {
return $this->category;
 }
//   EOF Price Break for SPPC mod, price break per category

 function getPrice() {
return $this->thePrice;
 }

 function getLowPrice() {
return $this->lowPrice;
 }

 function getHiPrice() {
return $this->hiPrice;
 }

 function hasSpecialPrice() {
return $this->hasSpecialPrice;
 }

 function hasQuantityPrice() {
return $this->hasQuantityPrice;
 }

 function getPriceString($style='productPriceInBox') {
global $currencies;

if ($this->hasSpecialPrice == true) {
	$lc_text = '<table align="top" border="1" cellspacing="0" cellpadding="0">';
	$lc_text .= '<tr><td align="center" class="pageHeading"' . $style. ' colspan="2">';
	  $lc_text .= ' <s>'
	. $currencies->display_price($this->thePrice,
				 tep_get_tax_rate($this->taxClass))
	. '</s>  <span class="productSpecialPrice">'
	. $currencies->display_price($this->specialPrice,
				 tep_get_tax_rate($this->taxClass))
	. '</span> '
	.'</td></tr>';
}
else
{
	$lc_text = '<table align="top" border="1" cellspacing="0" cellpadding="0">';
	$lc_text .= '<tr><td align="center" class="pageHeading"' . $style. ' colspan="2">'
	. $currencies->display_price($this->thePrice,
	tep_get_tax_rate($this->taxClass))
	. '</td></tr>';
}
  // If you want to change the format of the price/quantity table
  // displayed on the product information page, here is where you do it.

if($this->hasQuantityPrice == true) {
$lc_text = '<table align="top" border="0" cellspacing="1" cellpadding="0" class="infoBox">';
	$lc_text .= '<tr><td align="center" width="50" class="infoBoxHeading">QTY</td><td width="80" align="center" class="infoBoxHeading">Price</td>'
//			 . $currencies->display_price($this->thePrice, 
//					   tep_get_tax_rate($this->taxClass))
		   . '</tr>';

	for($i=1; $i<=8; $i++) {
		if($this->quantity[$i] > 0) {
			$lc_text .= '<tr valign="top"><td align="center" width="50" class="infoBoxContents"'.$style.'>'
			. $this->quantity[$i]
			.'+ </td><td align="center" width="80" class="infoBoxContents"'.$style.'>'
			. $currencies->display_price($this->price[$i],
			tep_get_tax_rate($this->taxClass))
			.'</td></tr>';
		}
	}

	$lc_text .= '</table>';

  }
  else {
	if ($this->hasSpecialPrice == true) {
		$lc_text = ' <s>'
		  . $currencies->display_price($this->thePrice, tep_get_tax_rate($this->taxClass))
		  . '</s>  <span class="productSpecialPrice">'
		  . $currencies->display_price($this->specialPrice, tep_get_tax_rate($this->taxClass))
		  . '</span> ';
	}
	else {
		$lc_text = ' '
		  . $currencies->display_price($this->thePrice,
				   tep_get_tax_rate($this->taxClass))
		  . ' ';
	}
	  }

return $lc_text;
 }

 function getPriceStringShort() {
global $currencies;

if ($this->hasSpecialPrice == true) {
  $lc_text = ' <s>'
. $currencies->display_price($this->thePrice,
				 tep_get_tax_rate($this->taxClass))
. '</s>  <span class="productSpecialPrice">'
. $currencies->display_price($this->specialPrice,
				 tep_get_tax_rate($this->taxClass))
. '</span> ';
}
else {
  if($this->hasQuantityPrice == true) {
$lc_text = ' '
  . $currencies->display_price($this->lowPrice,
				   tep_get_tax_rate($this->taxClass))
  . ' - '
  . $currencies->display_price($this->hiPrice,
				   tep_get_tax_rate($this->taxClass))
  . ' ';
  }
  else {
$lc_text = ' '
  . $currencies->display_price($this->thePrice,
				   tep_get_tax_rate($this->taxClass))
  . ' ';
  }
}
return $lc_text;
 }
}

?>

 

Shopping_cart.php

<?php
/*
 $Id: shopping_cart.php,v 1.35 2003/06/25 21:14:33 hpdl Exp $
 adapted for Separate Pricing Per Customer v4 and Price Break 1.11.3 2005/03/12
 adapted for price break per category 2005/09/03

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

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/

 class shoppingCart {
var $contents, $total, $weight, $cartID, $content_type;

function shoppingCart() {
  $this->reset();
}

function restore_contents() {
  global $customer_id;

  if (!tep_session_is_registered('customer_id')) return false;

// insert current cart contents in database
  if (is_array($this->contents)) {
	reset($this->contents);
	while (list($products_id, ) = each($this->contents)) {
	  $qty = $this->contents[$products_id]['qty'];
	  $product_query = tep_db_query("select products_id from " . TABLE_CUSTOMERS_BASKET . " where customers_id = '" . (int)$customer_id . "' and products_id = '" . tep_db_input($products_id) . "'");
	  if (!tep_db_num_rows($product_query)) {
		tep_db_query("insert into " . TABLE_CUSTOMERS_BASKET . " (customers_id, products_id, customers_basket_quantity, customers_basket_date_added) values ('" . (int)$customer_id . "', '" . tep_db_input($products_id) . "', '" . $qty . "', '" . date('Ymd') . "')");
		if (isset($this->contents[$products_id]['attributes'])) {
		  reset($this->contents[$products_id]['attributes']);
		  while (list($option, $value) = each($this->contents[$products_id]['attributes'])) {
			tep_db_query("insert into " . TABLE_CUSTOMERS_BASKET_ATTRIBUTES . " (customers_id, products_id, products_options_id, products_options_value_id) values ('" . (int)$customer_id . "', '" . tep_db_input($products_id) . "', '" . (int)$option . "', '" . (int)$value . "')");
		  }
		}
	  } else {
		tep_db_query("update " . TABLE_CUSTOMERS_BASKET . " set customers_basket_quantity = '" . $qty . "' where customers_id = '" . (int)$customer_id . "' and products_id = '" . tep_db_input($products_id) . "'");
	  }
	}
  }

// reset per-session cart contents, but not the database contents
  $this->reset(false);

// BOF Price Break for SPPC mod, price break per category
$products_query = tep_db_query("select products_to_categories.products_id, IF (c.parent_id = '0', c.categories_id, c.parent_id) AS categories_id, customers_basket_quantity from " . TABLE_CUSTOMERS_BASKET . " LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c using(products_id) LEFT JOIN " . TABLE_CATEGORIES . " c using(categories_id) where customers_id = '" . (int)$customer_id . "'");
  while ($products = tep_db_fetch_array($products_query)) {
	$this->contents[$products['products_id']] = array('qty' => $products['customers_basket_quantity'], 'category' => $products['categories_id']);
// EOF Price Break for SPPC mod, price break per category
// attributes
	$attributes_query = tep_db_query("select products_options_id, products_options_value_id from " . TABLE_CUSTOMERS_BASKET_ATTRIBUTES . " where customers_id = '" . (int)$customer_id . "' and products_id = '" . tep_db_input($products['products_id']) . "'");
	while ($attributes = tep_db_fetch_array($attributes_query)) {
	  $this->contents[$products['products_id']]['attributes'][$attributes['products_options_id']] = $attributes['products_options_value_id'];
	}
  }

  $this->cleanup();
}

function reset($reset_database = false) {
  global $customer_id;

  $this->contents = array();
  $this->total = 0;
  $this->weight = 0;
  $this->content_type = false;

  if (tep_session_is_registered('customer_id') && ($reset_database == true)) {
	tep_db_query("delete from " . TABLE_CUSTOMERS_BASKET . " where customers_id = '" . (int)$customer_id . "'");
	tep_db_query("delete from " . TABLE_CUSTOMERS_BASKET_ATTRIBUTES . " where customers_id = '" . (int)$customer_id . "'");
  }

  unset($this->cartID);
  if (tep_session_is_registered('cartID')) tep_session_unregister('cartID');
}

function add_cart($products_id, $qty = '1', $attributes = '', $notify = true) {
  // BOF Separate Pricing Per Customer, Price Break 1.11.3 modification
  global $new_products_id_in_cart, $customer_id, $languages_id;

  $pf = new PriceFormatter;
  $pf->loadProduct($products_id, $languages_id);
  $qty = $pf->adjustQty($qty);
  $category = $pf->get_category();

  // EOF Separate Pricing Per Customer, Price Break 1.11.3 modification
  $products_id = tep_get_uprid($products_id, $attributes);
  if ($notify == true) {
	$new_products_id_in_cart = $products_id;
	tep_session_register('new_products_id_in_cart');
  }

// BOF Price Break for SPPC mod, price break per category
  if ($this->in_cart($products_id)) {
	$this->update_quantity($products_id, $qty, $attributes, $category); //added category
  } else {
	$this->contents[] = array($products_id);
	$this->contents[$products_id] = array('qty' => $qty, 'category' => $category); //added category
// EOF Price Break for SPPC mod, price break per category
// insert into database
	if (tep_session_is_registered('customer_id')) tep_db_query("insert into " . TABLE_CUSTOMERS_BASKET . " (customers_id, products_id, customers_basket_quantity, customers_basket_date_added) values ('" . (int)$customer_id . "', '" . tep_db_input($products_id) . "', '" . $qty . "', '" . date('Ymd') . "')");

	if (is_array($attributes)) {
	  reset($attributes);
	  while (list($option, $value) = each($attributes)) {
		$this->contents[$products_id]['attributes'][$option] = $value;
// insert into database
		if (tep_session_is_registered('customer_id')) tep_db_query("insert into " . TABLE_CUSTOMERS_BASKET_ATTRIBUTES . " (customers_id, products_id, products_options_id, products_options_value_id) values ('" . (int)$customer_id . "', '" . tep_db_input($products_id) . "', '" . (int)$option . "', '" . (int)$value . "')");
	  }
	}
  }
  $this->cleanup();

// assign a temporary unique ID to the order contents to prevent hack attempts during the checkout procedure
  $this->cartID = $this->generate_cart_id();
}

// BOF Price Break for SPPC mod, price break per category
function update_quantity($products_id, $quantity = '', $attributes = '', $category = '') { //added category
  global $customer_id;

  if (empty($quantity)) return true; // nothing needs to be updated if theres no quantity, so we return true..

  $this->contents[$products_id] = array('qty' => $quantity, 'category' => $category); //added category
// EOF Price Break for SPPC mod, price break per category
// update database
  if (tep_session_is_registered('customer_id')) tep_db_query("update " . TABLE_CUSTOMERS_BASKET . " set customers_basket_quantity = '" . $quantity . "' where customers_id = '" . (int)$customer_id . "' and products_id = '" . tep_db_input($products_id) . "'");

  if (is_array($attributes)) {
	reset($attributes);
	while (list($option, $value) = each($attributes)) {
	  $this->contents[$products_id]['attributes'][$option] = $value;
// update database
	  if (tep_session_is_registered('customer_id')) tep_db_query("update " . TABLE_CUSTOMERS_BASKET_ATTRIBUTES . " set products_options_value_id = '" . (int)$value . "' where customers_id = '" . (int)$customer_id . "' and products_id = '" . tep_db_input($products_id) . "' and products_options_id = '" . (int)$option . "'");
	}
  }
}

function cleanup() {
  global $customer_id;

  reset($this->contents);
  while (list($key,) = each($this->contents)) {
	if ($this->contents[$key]['qty'] < 1) {
	  unset($this->contents[$key]);
// remove from database
	  if (tep_session_is_registered('customer_id')) {
		tep_db_query("delete from " . TABLE_CUSTOMERS_BASKET . " where customers_id = '" . (int)$customer_id . "' and products_id = '" . tep_db_input($key) . "'");
		tep_db_query("delete from " . TABLE_CUSTOMERS_BASKET_ATTRIBUTES . " where customers_id = '" . (int)$customer_id . "' and products_id = '" . tep_db_input($key) . "'");
	  }
	}
  }
}

function count_contents() {  // get total number of items in cart 
  $total_items = 0;
  if (is_array($this->contents)) {
	reset($this->contents);
	while (list($products_id, ) = each($this->contents)) {
	  $total_items += $this->get_quantity($products_id);
	}
  }

  return $total_items;
}

function get_quantity($products_id) {
  if (isset($this->contents[$products_id])) {
	return $this->contents[$products_id]['qty'];
  } else {
	return 0;
  }
}

function in_cart($products_id) {
  if (isset($this->contents[$products_id])) {
	return true;
  } else {
	return false;
  }
}

function remove($products_id) {
  global $customer_id;

  unset($this->contents[$products_id]);
// remove from database
  if (tep_session_is_registered('customer_id')) {
	tep_db_query("delete from " . TABLE_CUSTOMERS_BASKET . " where customers_id = '" . (int)$customer_id . "' and products_id = '" . tep_db_input($products_id) . "'");
	tep_db_query("delete from " . TABLE_CUSTOMERS_BASKET_ATTRIBUTES . " where customers_id = '" . (int)$customer_id . "' and products_id = '" . tep_db_input($products_id) . "'");
  }

// assign a temporary unique ID to the order contents to prevent hack attempts during the checkout procedure
  $this->cartID = $this->generate_cart_id();
}

function remove_all() {
  $this->reset();
}

function get_product_id_list() {
  $product_id_list = '';
  if (is_array($this->contents)) {
	reset($this->contents);
	while (list($products_id, ) = each($this->contents)) {
	  $product_id_list .= ', ' . $products_id;
	}
  }

  return substr($product_id_list, 2);
}

function calculate() {
  $this->total = 0;
  $this->weight = 0;
  // BOF Price Break for SPPC mod, price break per category
  global $languages_id;
  if (!is_array($this->contents)) return 0;
 $category_quantity = array(); // calculates no of items per category in shopping basket
 // note that the function adjustQty has not been called yet (invoked by $pf->computePrice), 
 // so this may not be the correct number of items yet
  foreach ($this->contents as $products_id => $contents_array) {
	  if (!isset($category_quantity[$contents_array['category']])) {
	 $category_quantity[$contents_array['category']] = $contents_array['qty'];
	  } else {
	 $category_quantity[$contents_array['category']] += $contents_array['qty'];
	  }
  } // end foreach

  $pf = new PriceFormatter;
//   EOF Price Break for SPPC mod, price break per category

  reset($this->contents);
  while (list($products_id, ) = each($this->contents)) {
	$qty = $this->contents[$products_id]['qty'];
$number_of_items_in_cart_same_category = $category_quantity[$this->contents[$products_id]['category']];
$no_of_other_items_in_cart_from_same_cat = $number_of_items_in_cart_same_category - $qty;

// products price
//	$product_query = tep_db_query("select products_id, products_price, products_tax_class_id, products_weight from " . TABLE_PRODUCTS . " where products_id = '" . (int)$products_id . "'");

//	if ($product = tep_db_fetch_array($product_query)) {
	if ($product = $pf->loadProduct($products_id, $languages_id)){		
	  $prid = $product['products_id'];
	 $products_tax = tep_get_tax_rate($product['products_tax_class_id']);
 //		$products_price = $product['products_price'];
	 $products_price = $pf->computePrice($qty, $no_of_other_items_in_cart_from_same_cat);
	  $products_weight = $product['products_weight'];
// EOF Separate Pricing Per Customer, Price Break 1.11.3 mod
	  $this->total += tep_add_tax($products_price, $products_tax) * $qty;
	  $this->weight += ($qty * $products_weight);
	}

// attributes price
	if (isset($this->contents[$products_id]['attributes'])) {
	  reset($this->contents[$products_id]['attributes']);
	  while (list($option, $value) = each($this->contents[$products_id]['attributes'])) {
		$attribute_price_query = tep_db_query("select options_values_price, price_prefix from " . TABLE_PRODUCTS_ATTRIBUTES . " where products_id = '" . (int)$prid . "' and options_id = '" . (int)$option . "' and options_values_id = '" . (int)$value . "'");
		$attribute_price = tep_db_fetch_array($attribute_price_query);
		if ($attribute_price['price_prefix'] == '+') {
		  $this->total += $qty * tep_add_tax($attribute_price['options_values_price'], $products_tax);
		} else {
		  $this->total -= $qty * tep_add_tax($attribute_price['options_values_price'], $products_tax);
		}
	  }
	}
  }
}

function attributes_price($products_id) {
  $attributes_price = 0;

  if (isset($this->contents[$products_id]['attributes'])) {
	reset($this->contents[$products_id]['attributes']);
	while (list($option, $value) = each($this->contents[$products_id]['attributes'])) {
	  $attribute_price_query = tep_db_query("select options_values_price, price_prefix from " . TABLE_PRODUCTS_ATTRIBUTES . " where products_id = '" . (int)$products_id . "' and options_id = '" . (int)$option . "' and options_values_id = '" . (int)$value . "'");
	  $attribute_price = tep_db_fetch_array($attribute_price_query);
	  if ($attribute_price['price_prefix'] == '+') {
		$attributes_price += $attribute_price['options_values_price'];
	  } else {
		$attributes_price -= $attribute_price['options_values_price'];
	  }
	}
  }

  return $attributes_price;
}

function get_products() {
	global $languages_id;
// BOF Separate Pricing Per Customer v4, Price Break 1.11.3 modification
  if (!is_array($this->contents)) return false;
//   BOF Price Break for SPPC mod, price break per category	 
  $category_quantity = array();
  foreach ($this->contents as $products_id => $contents_array) {
	  if (!isset($category_quantity[$contents_array['category']])) {
	 $category_quantity[$contents_array['category']] = $contents_array['qty'];
	  } else {
	 $category_quantity[$contents_array['category']] += $contents_array['qty'];
	  }
  } // end foreach

  $pf = new PriceFormatter;
//   EOF Price Break for SPPC mod, price break per category

  $products_array = array();
  reset($this->contents);
  while (list($products_id, ) = each($this->contents)) {
/*		$products_query = tep_db_query("select p.products_id, pd.products_name, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_tax_class_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = '" . (int)$products_id . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");
	if ($products = tep_db_fetch_array($products_query)) {
	  $prid = $products['products_id'];
	  $products_price = $products['products_price'];

	  $specials_query = tep_db_query("select specials_new_products_price from " . TABLE_SPECIALS . " where products_id = '" . (int)$prid . "' and status = '1'");
	  if (tep_db_num_rows($specials_query)) {
		$specials = tep_db_fetch_array($specials_query);
		$products_price = $specials['specials_new_products_price'];
	  } */

  if ($products = $pf->loadProduct($products_id, $languages_id)) {
	  $qty = $this->contents[$products_id]['qty'];
$number_of_items_in_cart_same_category = $category_quantity[$this->contents[$products_id]['category']];
$no_of_other_items_in_cart_from_same_cat = $number_of_items_in_cart_same_category - $qty;

	 $products_price = $pf->computePrice($this->contents[$products_id]['qty'], $no_of_other_items_in_cart_from_same_cat);
// EOF Separate Pricing Per Customer v4, Price Break 1.11.3 modification	  
	  $products_array[] = array('id' => $products_id,
								'name' => $products['products_name'],
								'model' => $products['products_model'],
								'image' => $products['products_image'],
//   BOF Price Break for SPPC mod, price break per category
				'category' => $this->contents[$products_id]['category'],
//   EOF Price Break for SPPC mod, price break per category

								'price' => $products_price,
								'quantity' => $this->contents[$products_id]['qty'],
								'weight' => $products['products_weight'],
								'final_price' => ($products_price + $this->attributes_price($products_id)),
								'tax_class_id' => $products['products_tax_class_id'],
								'attributes' => (isset($this->contents[$products_id]['attributes']) ? $this->contents[$products_id]['attributes'] : ''));
	}
  }

  return $products_array;
}

function show_total() {
  $this->calculate();

  return $this->total;
}

function show_weight() {
  $this->calculate();

  return $this->weight;
}

function generate_cart_id($length = 5) {
  return tep_create_random_value($length, 'digits');
}

function get_content_type() {
  $this->content_type = false;

  if ( (DOWNLOAD_ENABLED == 'true') && ($this->count_contents() > 0) ) {
	reset($this->contents);
	while (list($products_id, ) = each($this->contents)) {
	  if (isset($this->contents[$products_id]['attributes'])) {
		reset($this->contents[$products_id]['attributes']);
		while (list(, $value) = each($this->contents[$products_id]['attributes'])) {
		  $virtual_check_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pad where pa.products_id = '" . (int)$products_id . "' and pa.options_values_id = '" . (int)$value . "' and pa.products_attributes_id = pad.products_attributes_id");
		  $virtual_check = tep_db_fetch_array($virtual_check_query);

		  if ($virtual_check['total'] > 0) {
			switch ($this->content_type) {
			  case 'physical':
				$this->content_type = 'mixed';

				return $this->content_type;
				break;
			  default:
				$this->content_type = 'virtual';
				break;
			}
		  } else {
			switch ($this->content_type) {
			  case 'virtual':
				$this->content_type = 'mixed';

				return $this->content_type;
				break;
			  default:
				$this->content_type = 'physical';
				break;
			}
		  }
		}
	  } else {
		switch ($this->content_type) {
		  case 'virtual':
			$this->content_type = 'mixed';

			return $this->content_type;
			break;
		  default:
			$this->content_type = 'physical';
			break;
		}
	  }
	}
  } else {
	$this->content_type = 'physical';
  }

  return $this->content_type;
}

function unserialize($broken) {
  for(reset($broken);$kv=each($broken);) {
	$key=$kv['key'];
	if (gettype($this->$key)!="user function")
	$this->$key=$kv['value'];
  }
}

 }
?>

 

Thanks for your help.

 

Lizzy

[Sid04]

I can't see a reason why SPPC (login as shop owner) could cause that. Of course it is strange that you can succeed in 10% of the cases.

Just to be certain I would move the shop to the root directory (there is even a contribution: Store in root that explains it) if I were you. Something else to look at would be to check the url that is associated with the cookie when you login. Is it the old site or the new site?

 

its an old store.....made the changes as decribed in my links. Basically just giving it a new URL.

 

I have another complete site in the root so id rather not move both that site and store around unless absolutely needed........but will if you think its the best alternative.

 

Like I said, I THINK its working for customers....and always worked fine in the /catalog directory before switching over to this addon domain. I just cant figure out what the issue is with this thing all of the sudden :(

[Jan Zonjee]

Lizzy,

I've tried with your code but it doesn't work. Is there anything wrong with my code?

The code looks exactly as I think it should be to work. I tried these changes in the "category mod" again tonight. To me it seems to work fine. :huh:

[Guest]

Hello all

 

I am having a sericous problem..

 

I am getting this error

 

Groups Search:

1146 - Table 'ellehost_cart.TABLE_CUSTOMERS_GROUPS' doesn't exist

 

select count(*) as total from TABLE_CUSTOMERS_GROUPS g

I get this when I am in the admin and under Customers and I click on gruops

 

and it also happens when I click on the Categories/Products

 

1146 - Table 'ellehost_cart.TABLE_CUSTOMERS_GROUPS' doesn't exist

 

select customers_group_id, customers_group_name from TABLE_CUSTOMERS_GROUPS where customers_group_id != '0' order by customers_group_id

 

and what is so strange is the database table is there I just loaded it.

 

please help me.

'thank you

noppie

[theman]

Hi,

Just wanted to know how I could create 3 groups with 5% 10% 15% off respectively and have it automatically insert that into the database when i set the retail price? and also how could i update all the retail prices already in my db and add the 3 groups?

 

thanks

[Jan Zonjee]

Naomi,

and what is so strange is the database table is there I just loaded it.

But did you also add this code to catalog/includes/database_tables.php:

**ADD**
// BOF Separate Pricing per Customer
 define('TABLE_PRODUCTS_GROUPS', 'products_groups');
 define('TABLE_SPECIALS_RETAIL_PRICES', 'specials_retail_prices');
 define('TABLE_PRODUCTS_GROUP_PRICES', 'products_group_prices_cg_');
 define('TABLE_CUSTOMERS_GROUPS', 'customers_groups');
 // this will define the maximum time in minutes between updates of a products_group_prices_cg_# table
 // changes in table specials will trigger an immediate update if a query needs this particular table
 define('MAXIMUM_DELAY_UPDATE_PG_PRICES_TABLE', '15');
 // EOF Separate Pricing per Customer

The error proves otherwise ;)

[Jan Zonjee]

Just wanted to know how I could create 3 groups with 5% 10% 15% off respectively and have it automatically insert that into the database when i set the retail price?

SPPC isn't configured to handle that. However, if you change or add a retail price you should be able to use JavaScript to fill in those values for you in the input boxes of your groups. Look e.g. at how texmaxx added JavaScript to the price break modification to do that automatically.

and also how could i update all the retail prices already in my db and add the 3 groups?

You can do that with sql (using phpMyAdmin). Let's assume that group 2 gets 10% off:

insert into products_groups select '2' as customers_group_id, (0.9 * p.products_price) as customers_group_price, p.products_id from products p;

And if you change you mind and want to use another discount just delete those again from that table with:

delete from products_groups where customers_group_id = '2';

and try again.

[theman]

SPPC isn't configured to handle that. However, if you change or add a retail price you should be able to use JavaScript to fill in those values for you in the input boxes of your groups. Look e.g. at how texmaxx added JavaScript to the price break modification to do that automatically.

You can do that with sql (using phpMyAdmin). Let's assume that group 2 gets 10% off:

insert into products_groups select '2' as customers_group_id, (0.9 * p.products_price) as customers_group_price, p.products_id from products p;

And if you change you mind and want to use another discount just delete those again from that table with:

delete from products_groups where customers_group_id = '2';

and try again.

 

 

thanks alot ill try that out....instead of using Java to update prices would it be possible when inserting the products price to insert price for group 2, 3 and 4 as well? with like a sql statement? or would java be easier

[Jan Zonjee]

instead of using Java to update prices would it be possible when inserting the products price to insert price for group 2, 3 and 4 as well? with like a sql statement? or would java be easier

Not Java, but JavaScript. Kind of like when you change the products price in the admin section, the price included tax is also shown/updated. It is done with a piece of JavaScipt that is found right after the SpiffyCalendarBox code:

function updateGross() {
 var taxRate = getTaxRate();
 var grossValue = document.forms["new_product"].products_price.value;

 if (taxRate > 0) {
grossValue = grossValue * ((taxRate / 100) + 1);
 }

 document.forms["new_product"].products_price_gross.value = doRound(grossValue, 4);
}

function updateNet() {
 var taxRate = getTaxRate();
 var netValue = document.forms["new_product"].products_price_gross.value;

 if (taxRate > 0) {
netValue = netValue / ((taxRate / 100) + 1);
 }

 document.forms["new_product"].products_price.value = doRound(netValue, 4);
}

Something similar for the prices of groups 2, 3, and 4 to me looks like the most simple solution to implement (and you can deviate from the discount rates if you don't use an updateNet function for it....).

[Guest]

QUOTE(noppie @ Nov 12 2005, 02:24 AM) *

and what is so strange is the database table is there I just loaded it.

But did you also add this code to catalog/includes/database_tables.php:

CODE

**ADD**

// BOF Separate Pricing per Customer

define('TABLE_PRODUCTS_GROUPS', 'products_groups');

define('TABLE_SPECIALS_RETAIL_PRICES', 'specials_retail_prices');

define('TABLE_PRODUCTS_GROUP_PRICES', 'products_group_prices_cg_');

define('TABLE_CUSTOMERS_GROUPS', 'customers_groups');

// this will define the maximum time in minutes between updates of a products_group_prices_cg_# table

// changes in table specials will trigger an immediate update if a query needs this particular table

define('MAXIMUM_DELAY_UPDATE_PG_PRICES_TABLE', '15');

// EOF Separate Pricing per Customer

 

The error proves otherwise wink.gif

 

yes that is there.. I think I Have to go about this in a different way,

noppie

[Jan Zonjee]

noppie,

yes that is there.. I think I Have to go about this in a different way,

You did also add this to admin/includes/database_tables.php?

  // BOF Separate Pricing per Customer
 define('TABLE_PRODUCTS_GROUPS', 'products_groups');
 define('TABLE_CUSTOMERS_GROUPS', 'customers_groups');
 // EOF Separate Pricing per Customer

[theman]

Not Java, but JavaScript. Kind of like when you change the products price in the admin section, the price included tax is also shown/updated. It is done with a piece of JavaScipt that is found right after the SpiffyCalendarBox code:

function updateGross() {
 var taxRate = getTaxRate();
 var grossValue = document.forms["new_product"].products_price.value;

 if (taxRate > 0) {
grossValue = grossValue * ((taxRate / 100) + 1);
 }

 document.forms["new_product"].products_price_gross.value = doRound(grossValue, 4);
}

function updateNet() {
 var taxRate = getTaxRate();
 var netValue = document.forms["new_product"].products_price_gross.value;

 if (taxRate > 0) {
netValue = netValue / ((taxRate / 100) + 1);
 }

 document.forms["new_product"].products_price.value = doRound(netValue, 4);
}

Something similar for the prices of groups 2, 3, and 4 to me looks like the most simple solution to implement (and you can deviate from the discount rates if you don't use an updateNet function for it....).

could u maybe help me with this? im not php savvy enough to do it my self;(

[Guest]

JanZ Thank you for your help where you helped me.

 

I am almost there I just have a few more things I dont' understand.

 

This is My Index file now!!

 

and this is the subscription code I am trying to merge with the seperate price

go here

 

and this is what I got in a more condensed verison than the whole index file

 

CONDENSED VERISON

 

I got the

su.price_period, su.oneday_price, su.days_as_period, su.pay_period_type

 

and most of the

TABLE_SUBSCRIPTIONS ." su, " .

 

added.. on the bottom part of the I can't figure out where to put the

TABLE_SUBSCRIPTIONS ." su, " .

 

CONDENSED VERISON

 

and there is su.productid part of the subscription code. do I need this in there also.. I hope you can understand my notes..

I am almost there so close I can tasted it LOL and as I said as soon I we get this I willl post the whole thing for anyone who whants it..

 

what this is the subscription and seprate prices together.

 

I got everything but the index.php.. just don't know enought about mysql and php

 

Thank you again.

Noppie

[meira99]

Hi there.

 

I'm a complete newbie... I'm not even a programmer but I am a webmaster and know a lot of internet stuff.

 

I recently set up osCommerce for a client of mine. He then insisted that he needed the Quantity Price Break pricing structure. So I downloaded the Seperate Pring for Customer v4.0. As the installation notes said, all I had to do was copy the files across and run the spcc_v40_install.sql file. Since I'm not the one doing the actual hosting, I asked the hosting provider to run the SQL file, which he did.

 

However, now when I go to the front end of the site and click on a product, I get the following error:

 

1146 - Table 'dimexcor_oscommerce.TABLE_PRODUCTS_GROUPS' doesn't exist

 

select customers_group_price from TABLE_PRODUCTS_GROUPS where products_id = '30' and customers_group_id = '0'

 

[TEP STOP]

 

When I go to the Admin side of things and click on a product, I get the following error:

 

1146 - Table 'dimexcor_oscommerce.TABLE_CUSTOMERS_GROUPS' doesn't exist

 

select customers_group_id, customers_group_name from

TABLE_CUSTOMERS_GROUPS where customers_group_id != '0' order by

customers_group_id

 

The little I know would suggest that the tables don't exist, but the guy who ran the SQL file said that they do exist. He asks:

 

I suspect that the reason why it's not working is because the plugin might need a higher version of MySQL. We are currently running MySQL 4.1.12. Could you perhaps confirm that the plugin that you have loaded is compatible with this version of MySQL?

 

Please can someone help me with this! Is it the version of MySQL or is it something else? I thought installing the contribution (the first one for the site) would be so easy....

 

Thank you so much!

 

Meira

[carlost]

Please can someone help me with this! Is it the version of MySQL or is it something else? I thought installing the contribution (the first one for the site) would be so easy....

 

Thank you so much!

 

Meira

 

Hello,

 

I can tell you that I am using an older version of MySql than you are. It is not your version of MySql that is the problem.

 

Also, If it is just quantity price breaking that you want there is a contribution, found here, which does just that.

 

This contribution (SPPC) allows for separate group pricing, and has an add on for Quantity Price Breaks.

 

I think too, that the version you installed of SPPC, is what may be part of your problem. The version I have installed is 4.1, and although I did need help (which I got here), I needed it because my OSC is heavily modified, but the initial installation of SPPC worked out of the box, so to speak.

 

If you do not have any other contributions installed into your OSC, and it is SPPC (Separate Pricing Per Customer) that you want to install, perhaps the easiest way to go would be to start over with a clean version of OSC, and download SPPC 4.1 (The download you want is: Version 4.1.1.: minor change Jan Zonjee 20 Mar 2005) and use the files in it as they are. Then you will not have to modify the OSC files. Then you can add Quantity Price Breaks for Separate Pricing Per Customer in the same way, by just replacing files, instead of having to modify.

 

I hope this is a little helpful to you. If it isn't, there are others here with much more knowledge that will be able to help you better.

 

Regards,

 

Carlos

[meira99]

Also, If it is just quantity price breaking that you want there is a contribution, found here, which does just that.

 

This contribution (SPPC) allows for separate group pricing, and has an add on for Quantity Price Breaks.

...

 

If you do not have any other contributions installed into your OSC, and it is SPPC (Separate Pricing Per Customer) that you want to install, perhaps the easiest way to go would be to start over with a clean version of OSC, and download SPPC 4.1 (The download you want is: Version 4.1.1.: minor change Jan Zonjee 20 Mar 2005) and use the files in it as they are. Then you will not have to modify the OSC files. Then you can add Quantity Price Breaks for Separate Pricing Per Customer in the same way, by just replacing files, instead of having to modify.

 

Hi Carlos

 

Thanks for replying to my post so quickly!

 

I didn't know about that first contribution you mentioned. I found the second one and it said I needed to first install SPPC.

 

Now I'm not sure which one to rather install!

 

I assume that SPPC offers more features...

 

Anyone got any ideas which is better - to install the Quantity Price Break stand-alone contribution or to first instal SPPC and then the QPB add-on?

 

Thanks!

Meira

[Jan Zonjee]

I hope you can understand my notes..

These are a bunch of complicated queries... I think it should be this:

// 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 . " su.price_period, su.oneday_price, su.days_as_period, su.pay_period_type, 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_SUBSCRIPTIONS . " su, " . 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 . " su.price_period, su.oneday_price, su.days_as_period, su.pay_period_type, 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_SUBSCRIPTIONS . " su, " . 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) { // noppie, you forgot to add "and su.productid = p2c.products_id
	$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 su.productid = p2c.products_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";	
} else { // either retail or no need to get correct special prices, ditto
	$listing_sql = "select " . $select_column_list . " su.price_period, su.oneday_price, su.days_as_period, su.pay_period_type, 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_SUBSCRIPTIONS . " su, " . 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 su.productid = p2c.products_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) { // noppie: moved table su before table_products_description
	$listing_sql = "select " . $select_column_list . " su.price_period, su.oneday_price, su.days_as_period, su.pay_period_type, 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_SUBSCRIPTIONS ." su, " . 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 su.productid = 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_SUBSCRIPTIONS ." su, " . 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 su.productid = 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 . " su.price_period, su.oneday_price, su.days_as_period, su.pay_period_type, 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), " . TABLE_SUBSCRIPTIONS ." su, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_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 su.productid = p2c.products_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 . " su.price_period, su.oneday_price, su.days_as_period, su.pay_period_type, 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_SUBSCRIPTIONS ." su, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . 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.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and su.productid = p2c.products_id and p2c.categories_id = '" . (int)$current_category_id . "'";
  } // end else { // either retail...
// EOF Separate Pricing per Customer
  }
}

You started well (added a few more spaces) but IMO you forgot something and I moved the table subscription to another spot. Oh well, let's see what this code does.

[G.T.Willemsen]

Hi,

 

I just did an fresh basic install (with a prefix in the DB) and it works fine.

After this I tried to install the SPPC by simply uploading the files and alter the DB (again with prefix offcourse).

 

Now I get error msgs in my main page (index);

It isn't able to locate the specials table...

Offcourse not, because everything got a prefix, wich shouldn't be a problem as long as the database files are moderated with prefixes aswell... (?)

 

I guess: I forgot to edit some file(s), or there is a direct link towards a the specials-table ??!??

 

The following error displays:

1146 - Table 'database_name.specials' doesn't exist

select products_id, specials_new_products_price from specials where (products_id = '27' or products_id = '26' or products_id = '25' or products_id = '24' or products_id = '23' or products_id = '22' or products_id = '21' or products_id = '20' or products_id = '19' ) and status = '1' and customers_group_id = '0'

 

I editted the following files for the prefix;

admin/includes/database_tables.php

includes/database_tables.php

 

and offcourse the prefix (oscommerce_) is filled in the sppc_v41_install file;

ALTER TABLE oscommerce_customers
ADD customers_group_id smallint UNSIGNED NOT NULL default '0',
ADD customers_group_ra enum('0','1') NOT NULL,
ADD customers_payment_allowed varchar(255) NOT NULL default '',
ADD customers_shipment_allowed varchar(255) NOT NULL default '';

DROP TABLE IF EXISTS oscommerce_products_groups;
CREATE TABLE oscommerce_products_groups (
 customers_group_id smallint UNSIGNED NOT NULL default '0',
 customers_group_price decimal(15,4) NOT NULL default '0.0000',
 products_id int(11) NOT NULL default '0',
 PRIMARY KEY  (customers_group_id, products_id)
);

ALTER TABLE oscommerce_specials
ADD customers_group_id smallint UNSIGNED NOT NULL default '0';

DROP TABLE IF EXISTS oscommerce_customers_groups;
CREATE TABLE oscommerce_customers_groups (
customers_group_id smallint UNSIGNED NOT NULL,
customers_group_name varchar(32) NOT NULL default '',
customers_group_show_tax enum('1','0') NOT NULL,
customers_group_tax_exempt enum('0','1') NOT NULL,
group_payment_allowed varchar(255) NOT NULL default '',
group_shipment_allowed varchar(255) NOT NULL default '',
PRIMARY KEY (customers_group_id)
);

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

ALTER TABLE oscommerce_address_book 
ADD entry_company_tax_id VARCHAR(32) DEFAULT NULL AFTER entry_company;

 

Any1 any idea what I've done wrong??

Or where to fix this problem??

 

Thanks in advance!

[G.T.Willemsen]

Offcourse what I try to reach is the following:

 

select.... from oscommerce_specials where ...

 

instead of

 

select.... from specials where ...

 

GRTZ GT

Edited November 15, 2005 by G.T.Willemsen

[G.T.Willemsen]

I read my own post and just to be shure I do not get the first solution I might have given based on the posts :blush:

 

database_tables looks like this;

  define('TABLE_SPECIALS', 'oscommerce_specials');

 

excuse me for the multiple posts, GT

Edited November 15, 2005 by G.T.Willemsen

[Jan Zonjee]

database_tables looks like this;

  define('TABLE_SPECIALS', 'oscommerce_specials');

Well, you are right with that. Alas, in includes/modules/new_products.php, around line 69 the table specials is hard coded. Should have been " . TABLE_SPECIALS . " instead of specials.

[G.T.Willemsen]

Great THNX !

 

I couldn't find it, I already looked over it a couple of times :-"

 

It works,.. and finnally I dare to continue deleting the original stuff and fill in the shop! :D

 

 

:thumbsup:

 

thanks,

 

GT

[meira99]

I think too, that the version you installed of SPPC, is what may be part of your problem. The version I have installed is 4.1, and although I did need help (which I got here), I needed it because my OSC is heavily modified, but the initial installation of SPPC worked out of the box, so to speak.

 

If you do not have any other contributions installed into your OSC, and it is SPPC (Separate Pricing Per Customer) that you want to install, perhaps the easiest way to go would be to start over with a clean version of OSC, and download SPPC 4.1 (The download you want is: Version 4.1.1.: minor change Jan Zonjee 20 Mar 2005) and use the files in it as they are. Then you will not have to modify the OSC files. Then you can add Quantity Price Breaks for Separate Pricing Per Customer in the same way, by just replacing files, instead of having to modify.

 

I tried doing what you said, but I got exactly the same errors.

 

I even started afresh and installed that other contribution but that was even worse! It was referring to many files which didn't exist.

 

I'm getting so frustrated, I just don't know what to do anymore!!!

 

:(

[carlost]

I tried doing what you said, but I got exactly the same errors.

 

I even started afresh and installed that other contribution but that was even worse! It was referring to many files which didn't exist.

 

I'm getting so frustrated, I just don't know what to do anymore!!!

 

:(

 

Okay, sorry that you're having so much frustration.

 

I'll share with you one more time some thoughts, and then I'll leave this alone as it doesn't seem to be of help to you.

 

One, I don't know anything about the quantity price break contribution, other than that it existed. I mentioned it because it seemed like you were just wanting a quantity price break feature. If that is what you want then you will likely get some help with whatever is not working for you in the forum that is set up for that contribution.

 

If what you want is SPPC with quantity price breaks, then as I mentioned, if you do use a clean OSC install with the correct SPPC version, and still have problems, you will find help here. There are people here who are extremely generous with helping those who are having difficulties.

 

So, maybe, take a deep breath, decide which contribution you want, and have another go at it

 

G'Luck & Regards,

 

Carlos