If this is set to 'Default', the Special Offers show when not logged-on but when logged-on, only Special Offers for the related Customer Group are shown (and the default ones disappear if they do not belong to the default group). This means that if there are ten Customer Groups and it is desired to always show a Special Offer, the same discount needs to be added ten times; one for each Customer Group.
This is very bad for me so I changed it so that logged-on visitors see Special Offers that are either applied to the 'Default' Customer Group, OR to their own Customer Group.
Changed getproductspecials() in catalog/model/catalog/product.php
From:
Code: Select all
$sql = "SELECT *, pd.name AS name, p.price, (SELECT ps2.price FROM " . DB_PREFIX . "product_special ps2 WHERE p.product_id = ps2.product_id AND ps2.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps2.date_start = '0000-00-00' OR ps2.date_start < NOW()) AND (ps2.date_end = '0000-00-00' OR ps2.date_end > NOW())) ORDER BY ps2.priority ASC, ps2.price ASC LIMIT 1) AS special, p.image, m.name AS manufacturer, ss.name AS stock, (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating 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 . "product_special ps ON (p.product_id = ps.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_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())) AND ps.product_id NOT IN (SELECT pd2.product_id FROM " . DB_PREFIX . "product_discount pd2 WHERE p.product_id = pd2.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()))) GROUP BY p.product_id";Code: Select all
$sql = "SELECT *, pd.name AS name, p.price,
(SELECT ps2.price
FROM " . DB_PREFIX . "product_special ps2
WHERE p.product_id = ps2.product_id
AND (ps2.customer_group_id = '" . (int)$customer_group_id . "' OR ps2.customer_group_id = '1')
AND ((ps2.date_start = '0000-00-00' OR ps2.date_start < NOW())
AND (ps2.date_end = '0000-00-00' OR ps2.date_end > NOW()))
ORDER BY ps2.priority ASC, ps2.price ASC LIMIT 1) AS special,
p.image, m.name AS manufacturer, ss.name AS stock,
(SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r
WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating
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 . "product_special ps ON (p.product_id = ps.product_id)
LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id)
LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id)
WHERE p.status = '1'
AND p.date_available <= NOW()
AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'
AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'
AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'
AND (ps.customer_group_id = '" . (int)$customer_group_id . "' OR ps.customer_group_id = '1')
AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW())
AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW()))
AND ps.product_id NOT IN
(SELECT pd2.product_id FROM " . DB_PREFIX . "product_discount pd2
WHERE p.product_id = pd2.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())))
GROUP BY p.product_id";
