Хранение цены в магазине в разных валютах.
Что-то я нигде это тему не видел, поэтому делюсь своим фиксом + пожелание в следующем релизе его очень хотелось бы его видеть.
Позволит без всяких преобразований и трудностей меняться данными между например 1с.
Итак погнали:
1.
Делаем новое поле+индекс на таблице с продуктами:
ALTER TABLE `oc_product` ADD COLUMN `currency_id` INTEGER(11) NOT NULL DEFAULT '1';
ALTER TABLE `oc_product` ADD INDEX `currency_id` (`currency_id`);
2. Дальше везде где SQL запросом мы получаем цену товара нужно его немного исправить и получать цену и учетом валюты и курса.
2.1 Корзина:
/system/library/cart.php
p.price/pcur.value AS price - получение цены с учетом курса и обзываем полученное также как и был это чтобы минимум перделок и переписываний.эту строку нужно добавить после "product p"
"LEFT JOIN " . DB_PREFIX . "currency pcur ON (p.currency_id = pcur.currency_id)"дальше строки которые я поменял у себя можно копировать и вставлять.примерно 31 строка
$product_query = $this->db->query("SELECT *, p.price/pcur.value AS price, wcd.unit AS weight_class, mcd.unit AS length_class FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "currency pcur ON (p.currency_id = pcur.currency_id) LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "weight_class wc ON (p.weight_class_id = wc.weight_class_id) LEFT JOIN " . DB_PREFIX . "weight_class_description wcd ON (wc.weight_class_id = wcd.weight_class_id) LEFT JOIN " . DB_PREFIX . "length_class mc ON (p.length_class_id = mc.length_class_id) LEFT JOIN " . DB_PREFIX . "length_class_description mcd ON (mc.length_class_id = mcd.length_class_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.date_available <= NOW() AND p.status = '1'");
дальше все аналогично сторока ~79
$product_discount_query = $this->db->query("SELECT pdis.price/pcur.value AS price FROM " . DB_PREFIX . "product AS p, " . DB_PREFIX . "currency as pcur, " . DB_PREFIX . "product_discount AS pdis WHERE (p.product_id=pdis.product_id)and(p.currency_id=pcur.currency_id)and pdis.product_id = '" . (int)$product_id . "' AND pdis.customer_group_id = '" . (int)$customer_group_id . "' AND pdis.quantity <= '" . (int)$discount_quantity . "' AND ((pdis.date_start = '0000-00-00' OR pdis.date_start < NOW()) AND (pdis.date_end = '0000-00-00' OR pdis.date_end > NOW())) ORDER BY pdis.quantity DESC, pdis.priority ASC, price ASC LIMIT 1");
2.2 Продукты:
catalog/model/catalog/product.php
~5 строка функция getProduct
$query = $this->db->query("SELECT DISTINCT *, p.price/pcur.value AS price, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, p.currency_id AS currency FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "currency pcur ON (p.currency_id = pcur.currency_id) 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) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE p.product_id = '" . (int)$product_id . "' 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 p.date_available <= NOW() AND p.status = '1'");
~10 строка функция getProducts
$query = $this->db->query("SELECT DISTINCT *, p.price/pcur.value AS price, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, wcd.unit AS weight_class FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "currency pcur ON (p.currency_id = pcur.currency_id) 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) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) LEFT JOIN " . DB_PREFIX . "weight_class_description wcd ON (p.weight_class_id = wcd.weight_class_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.date_available <= NOW() AND p.status = '1'");
~15 строка функция getProductsByCategoryId
$sql = "SELECT *, p.price/pcur.value AS price, pd.name AS name, 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 . "currency pcur ON (p.currency_id = pcur.currency_id) 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) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_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 p2c.category_id = '" . (int)$category_id . "'";
ну и дальше все запросы почти один в одинgetProductsByManufacturerId:
$sql = "SELECT *,p.price/pcur.value AS price, pd.name AS name, 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 . "currency pcur ON (p.currency_id = pcur.currency_id) 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) 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 m.manufacturer_id = '" . (int)$manufacturer_id. "'";
getProductsByKeyword:
$sql = "SELECT *,p.price/pcur.value AS price, pd.name AS name, 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 . "currency pcur ON (p.currency_id = pcur.currency_id) 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) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE 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') . "'";
getProductDiscount:
$query = $this->db->query("SELECT pdis.price/pcur.value AS price FROM " . DB_PREFIX . "product AS p, " . DB_PREFIX . "currency as pcur ," . DB_PREFIX . "product_discount AS pdis WHERE (p.product_id=pdis.product_id)and(p.currency_id=pcur.currency_id)and pdis.product_id = '" . (int)$product_id . "' AND customer_group_id = '" . (int)$customer_group_id . "' AND pdis.quantity = '1' AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) ORDER BY pdis.priority ASC, price ASC LIMIT 1");
getProductDiscounts:
$query = $this->db->query("SELECT pdis.*,pdis.price/pcur.value AS price FROM " . DB_PREFIX . "product AS p, " . DB_PREFIX . "currency as pcur ," . DB_PREFIX . "product_discount AS pdis WHERE (p.product_id=pdis.product_id)and(p.currency_id=pcur.currency_id)and pdis.product_id = '" . (int)$product_id . "' AND pdis.customer_group_id = '" . (int)$customer_group_id . "' AND pdis.quantity > 1 AND ((pdis.date_start = '0000-00-00' OR pdis.date_start < NOW()) AND (pdis.date_end = '0000-00-00' OR pdis.date_end > NOW())) ORDER BY pdis.quantity ASC, pdis.priority ASC, price ASC");
getProductSpecial:
$query = $this->db->query("SELECT pspec.price/pcur.value AS price FROM " . DB_PREFIX . "product_special AS pspec, " . DB_PREFIX . "product AS p, " . DB_PREFIX . "currency as pcur WHERE (p.product_id=pspec.product_id)and(p.currency_id=pcur.currency_id)and pspec.product_id = '" . (int)$product_id . "' AND pspec.customer_group_id = '" . (int)$customer_group_id . "' AND ((date_start = '0000-00-00' OR pspec.date_start < NOW()) AND (pspec.date_end = '0000-00-00' OR pspec.date_end > NOW())) ORDER BY pspec.priority ASC, price ASC LIMIT 1");
getProductSpecials:
$sql = "SELECT *, pd.name AS name, p.price/pcur.value AS 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 . "currency pcur ON (p.currency_id = pcur.currency_id) 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";
Уф-уф, яя! вроде все еcли что-то пропустил то дописать по аналогии...
3. редактирование товара в админке, добавление выпадающего списка c валютами.
3.1
файл:
admin/controller/catalog/product.php
строка ~500 можно в любом месте я поставил после
$this->data['entry_price'] = $this->language->get('entry_price');
добавляем
$this->data['entry_currency_id'] = $this->language->get('entry_currency_id');
_________
строка ~550 проверяем что перед " if (isset($this->error['warning'])) { "
есть 2 строки
$this->load->model('localisation/currency');
$this->data['currencies'] = $this->model_localisation_currency->getCurrencies();я просто не помню добавлял я их или нет, поэтому - проверить и добавить если нет.____________
Строка ~702 после ,блока
if (isset($this->request->post['keyword'])) {
$this->data['keyword'] = $this->request->post['keyword'];
} elseif (isset($product_info)) {
$this->data['keyword'] = $product_info['keyword'];
} else {
$this->data['keyword'] = '';
}
надо дописать
if (isset($this->request->post['currency'])) {
$this->data['currency_id'] = $this->request->post['currency_id'];
} elseif (isset($product_info)) {
$this->data['currency_id'] = $product_info['currency_id'];
} else {
$this->data['currency_id'] = '1';
}
файл:
admin/model/catalog/product.php
~4 строка функция addProduct нужно добавить новое поле при создании базы ( currency_id = '" . (int)$data['currency_id'] )
готовая строка:
$this->db->query("INSERT INTO " . DB_PREFIX . "product SET model = '" . $this->db->escape($data['model']) . "', sku = '" . $this->db->escape($data['sku']) . "', location = '" . $this->db->escape($data['location']) . "', quantity = '" . (int)$data['quantity'] . "', minimum = '" . (int)$data['minimum'] . "', subtract = '" . (int)$data['subtract'] . "', currency_id = '" . (int)$data['currency_id'] . "', stock_status_id = '" . (int)$data['stock_status_id'] . "', date_available = '" . $this->db->escape($data['date_available']) . "', manufacturer_id = '" . (int)$data['manufacturer_id'] . "', shipping = '" . (int)$data['shipping'] . "', price = '" . (float)$data['price'] . "', cost = '" . (float)$data['cost'] . "', weight = '" . (float)$data['weight'] . "', weight_class_id = '" . (int)$data['weight_class_id'] . "', length = '" . (float)$data['length'] . "', width = '" . (float)$data['width'] . "', height = '" . (float)$data['height'] . "', length_class_id = '" . (int)$data['length_class_id'] . "', status = '" . (int)$data['status'] . "', tax_class_id = '" . (int)$data['tax_class_id'] . "', sort_order = '" . (int)$data['sort_order'] . "', date_added = NOW()");
~104 строка функция editProduct
$this->db->query("UPDATE " . DB_PREFIX . "product SET currency_id = '" . (int)$data['currency_id'] . "', model = '" . $this->db->escape($data['model']) . "', sku = '" . $this->db->escape($data['sku']) . "', location = '" . $this->db->escape($data['location']) . "', quantity = '" . (int)$data['quantity'] . "', minimum = '" . (int)$data['minimum'] . "', subtract = '" . (int)$data['subtract'] . "', stock_status_id = '" . (int)$data['stock_status_id'] . "', date_available = '" . $this->db->escape($data['date_available']) . "', manufacturer_id = '" . (int)$data['manufacturer_id'] . "', shipping = '" . (int)$data['shipping'] . "', price = '" . (float)$data['price'] . "', cost = '" . (float)$data['cost'] . "', weight = '" . (float)$data['weight'] . "', weight_class_id = '" . (int)$data['weight_class_id'] . "', length = '" . (float)$data['length'] . "', width = '" . (float)$data['width'] . "', height = '" . (float)$data['height'] . "', length_class_id = '" . (int)$data['length_class_id'] . "', status = '" . (int)$data['status'] . "', tax_class_id = '" . (int)$data['tax_class_id'] . "', sort_order = '" . (int)$data['sort_order'] . "', date_modified = NOW() WHERE product_id = '" . (int)$product_id . "'");
Файл:
admin/view/template/catalog/product_form.tpl
Лепим после
<td><input type="text" name="cost" value="<?php echo $cost; ?>" /></td>
</tr>
<tr>
Строка ~96:
Вот этот блок:
<td><?php echo $entry_currency_id; ?></td>
<td><select name="currency_id">
<?php foreach ($currencies as $currency) { ?>
<?php if ($currency['currency_id'] == $currency_id) { ?>
<option value="<?php echo $currency['currency_id']; ?>" selected="selected"><?php echo $currency['title']; ?></option>
<?php } else { ?>
<option value="<?php echo $currency['currency_id']; ?>"><?php echo $currency['title']; ?></option>
<?php } ?>
<?php } ?>
</select></td>
</tr>
4. Все вроде готовченко.
