[Solved] link in contract special prices by customer

HI

Very happy with how easy Open cart is to setup and get working (new to Opencart).
Using Ver 1.5.1.3 I have set up 3 different product price levels and linked to Customer Groups successfully,

But I need to link in another table of customer special contract prices for products to override the customer group price. As I am already using Customer groups, I cannot allocate yet another customer group to the client, so I need to link it to the data I have from the accounting system directly as some custom code. I have the data in a seperate table with a client code, product id and contract price.

I can see the code where it looks for a $special in product.tpl but unsure where this is generated.

Can someone point me in the right direction to the SQL code location that sets $special.

Regards
Brenton

HI

Had a friend help me with this(advanced SQL and php is not my best language) and came up with the following that shows the correct contract price for a client if one exists. But Problem now is that it doesn't add to cart with the correct contract price - it still used the product_special customer group price.

Any Ideas where I need to edit next to get this to work when products get added to the shopping cart??

regards
Brenton


Copy table product_special -> product_contract and remove any 'group' keyword from the table field names.

catalog / model / catalog / product.php

old code
replace lines 8 - 16 with

if ($this->customer->isLogged()) {
$customer_group_id = $this->customer->getCustomerGroupId();

$query = $this->db->query("SELECT price FROM " . DB_PREFIX . "product_contract ps WHERE ps.product_id = '" . (int)$product_id . "' AND ps.customer_id = '" . (int)$this->customer->getId() . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1");
if (!isset($query->row['price'])) {

$query = $this->db->query("SELECT price as price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = '" . (int)$product_id . "' AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1");

}

if (isset($query->row['price'])) {
$customer_special = $query->row['price'];
} else {
$customer_special = 'null';
}


} else {
$customer_group_id = $this->config->get('config_customer_group_id');
$customer_special = 'null';
}

$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, " . $customer_special . " AS special, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");

HI

Good to have friends drop by and help solve coding issues all in a day!

The Add to Cart Button calls a function in /system/library/cart.php

Add this at line 181 to get the cart to use specified customers contract pricing over general Customer group pricing.

This also allows Customer contract pricing to have a start and stop date just like Customer Group pricing.
(Next - Adding an extra field would give Quantity break contract pricing!)

Can this code change be done as a VQMod to make it easier for future updates?

Hope this code helps someone.


Add this at line 181 in /system/library/cart.php

// Product contract
$product_contract_query = $this->db->query("SELECT price FROM " . DB_PREFIX . "product_contract WHERE product_id = '" . (int)$product_id . "' AND customer_id = '" . (int)$this->customer->getId() . "' AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) ORDER BY priority ASC, price ASC LIMIT 1");

if ($product_contract_query->num_rows) {
$price = $product_contract_query->row['price'];
}

Hi,

I've tried to apply this to a clean installed site but I get the following error.....

Parse error: syntax error, unexpected T_ELSE, expecting T_FUNCTION in /home/s1u69/public_html/shop/catalog/model/catalog/product.php on line 32

I have copied the code and pasted in the place of lines 8-16.

Any ideas