Mr.Arsi
Новичок
/*
* Создание временной таблиц, куда будут заноситься данные из excel-файла
*/
public function create_tmp_table()
{
$this->db->query('DROP TABLE IF EXISTS '.$this->tmp_tbl_name);
$this->db->query('CREATE TABLE '.$this->tmp_tbl_name.' (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
manufacturer int(10) NOT NULL,
sku varchar(32) NOT NULL,
descr tinytext DEFAULT NULL,
in_stock tinytext NOT NULL,
price float(8, 2) UNSIGNED DEFAULT NULL,
price_new float(8, 2) UNSIGNED NOT NULL DEFAULT "0.00",
category int(10) NOT NULL,
PRIMARY KEY (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci');
}
/**
* Наполнение вр. таблицы
* @param array $data
*/
public function fill_tmp_table(array $data)
{
$this->db->query(sprintf('INSERT INTO %s SET
`manufacturer`=%d,
`sku`="%s",
`descr`="%s",
`in_stock`=%d,
`price`=%f,
`price_new`=`price`,
`category`="%s"',
$this->tmp_tbl_name,
$this->db->escape($data['manufacturer']),
$data['sku'] = strtoupper(preg_replace('/[^a-zA-Zа-яА-Я0-9_]/u', '', $this->db->escape($data['sku']))),
$this->db->escape($data['descr']),
$this->db->escape($data['in_stock']),
$this->db->escape($data['price']),
$this->db->escape($data['category'])
));
}
/**
* Получение всех данных подгруженного прайса
* @param integer $limit
* @param integer $offset
* @return array
*/
public function get_tmp_data($start, $limit)
{
return $query = $this->db->query(sprintf('
SELECT
t2.`name` AS `manufacturer`,
t1.`sku`,
t1.`descr`,
t1.`in_stock`,
t1.`price`,
t1.`price_new`,
t1.`category`
FROM
%s AS t1
INNER JOIN
%s AS t2
ON t1.manufacturer=t2.manufacturer_id
LIMIT %d, %d
', $this->tmp_tbl_name, DB_PREFIX.'manufacturer', $start, $limit));
}
public function insert_products()
{
// Если в таблице product у колонки sku отсутствует индекс - добавляем
$this->check_index('product', 'sku', 1);
// Первое. Обновляем существующие товарные позиции
// Обновляем только наличие и цену
$this->db->query('UPDATE '.DB_PREFIX.'product AS p SET p.`quantity`=0');
$this->db->query('
UPDATE '.DB_PREFIX.'product AS t1, '.$this->tmp_tbl_name.' AS t2
SET t1.`price` = t2.price_new,
t1.`quantity` = t2.in_stock,
t1.`date_modified` = NOW(),
t1.`status` = 1
WHERE t1.sku = t2.sku AND t1.manufacturer_id = t2.manufacturer;
');
// Второе. Если во временной таблице есть позиции, которых нет в основной
// добавляем их.
$this->db->query('
INSERT INTO '.DB_PREFIX.'product (`model`, `sku`, `upc`, `ean`, `jan`, `isbn`, `mpn`, `location`, `quantity`, `stock_status_id`, `image`, `manufacturer_id`, `shipping`, `price`, `tax_class_id`, `date_available`, `status`, `date_added`)
SELECT `sku`, `sku`, "", "", "", "", "", "", `in_stock`, '.(int)$this->config->get('config_stock_status_id').', "no_image.jpg", `manufacturer`, 1, `price_new`, 0, NOW(), 1, NOW() FROM
(SELECT
t1.`sku`, t1.`in_stock`, t1.`manufacturer`, t1.`price_new`
FROM '.$this->tmp_tbl_name.' AS t1
LEFT JOIN '.DB_PREFIX.'product AS t2
ON t1.sku = t2.sku AND t1.manufacturer = t2.manufacturer_id
WHERE t2.sku IS NULL) AS subtable
');
// Третье. Если во временной таблице есть позиции, которых нет в основной
// добавляем описание к ним
$languages = $this->cache->get('language');
if (empty($languages)) {
$this->load->model('localisation/language');
$languages = $this->model_localisation_language->getLanguages();
}
foreach ($languages as $language)
{
$this->db->query('
INSERT IGNORE INTO '.DB_PREFIX.'product_description (`product_id`, `language_id`, `name`, `description`, `meta_description`, `meta_keyword`, `tag`) SELECT * FROM (
SELECT
t1.product_id,
'. (int) $language['language_id'].',
CONCAT(t3.descr) AS `name`,
"" AS description,
t3.descr AS meta_description,
"" AS meta_keyword,
t1.sku AS tag
FROM '.DB_PREFIX.'product AS t1
LEFT JOIN '.DB_PREFIX.'product_description AS t2
ON t2.product_id = t1.product_id
INNER JOIN '.$this->tmp_tbl_name.' AS t3
ON t3.sku = t1.sku AND t3.manufacturer = t1.manufacturer_id
INNER JOIN '.DB_PREFIX.'manufacturer AS t4 ON t4.manufacturer_id=t1.manufacturer_id
WHERE t2.product_id IS NULL OR t2.language_id <> '. (int) $language['language_id'].'
) AS subtable
');
}
Вот тут ошибка
// Добавляем категории
/$this->db->query('INSERT IGNORE INTO '.DB_PREFIX.'product_to_category SELECT p.product_id, ttp.category FROM '.DB_PREFIX.'product p INNER JOIN '.$this->tmp_tbl_name.' ttp ON ttp.sku=p.sku');
ошибка говорит не совпадения заполняемых ячеек
* Создание временной таблиц, куда будут заноситься данные из excel-файла
*/
public function create_tmp_table()
{
$this->db->query('DROP TABLE IF EXISTS '.$this->tmp_tbl_name);
$this->db->query('CREATE TABLE '.$this->tmp_tbl_name.' (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
manufacturer int(10) NOT NULL,
sku varchar(32) NOT NULL,
descr tinytext DEFAULT NULL,
in_stock tinytext NOT NULL,
price float(8, 2) UNSIGNED DEFAULT NULL,
price_new float(8, 2) UNSIGNED NOT NULL DEFAULT "0.00",
category int(10) NOT NULL,
PRIMARY KEY (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci');
}
/**
* Наполнение вр. таблицы
* @param array $data
*/
public function fill_tmp_table(array $data)
{
$this->db->query(sprintf('INSERT INTO %s SET
`manufacturer`=%d,
`sku`="%s",
`descr`="%s",
`in_stock`=%d,
`price`=%f,
`price_new`=`price`,
`category`="%s"',
$this->tmp_tbl_name,
$this->db->escape($data['manufacturer']),
$data['sku'] = strtoupper(preg_replace('/[^a-zA-Zа-яА-Я0-9_]/u', '', $this->db->escape($data['sku']))),
$this->db->escape($data['descr']),
$this->db->escape($data['in_stock']),
$this->db->escape($data['price']),
$this->db->escape($data['category'])
));
}
/**
* Получение всех данных подгруженного прайса
* @param integer $limit
* @param integer $offset
* @return array
*/
public function get_tmp_data($start, $limit)
{
return $query = $this->db->query(sprintf('
SELECT
t2.`name` AS `manufacturer`,
t1.`sku`,
t1.`descr`,
t1.`in_stock`,
t1.`price`,
t1.`price_new`,
t1.`category`
FROM
%s AS t1
INNER JOIN
%s AS t2
ON t1.manufacturer=t2.manufacturer_id
LIMIT %d, %d
', $this->tmp_tbl_name, DB_PREFIX.'manufacturer', $start, $limit));
}
public function insert_products()
{
// Если в таблице product у колонки sku отсутствует индекс - добавляем
$this->check_index('product', 'sku', 1);
// Первое. Обновляем существующие товарные позиции
// Обновляем только наличие и цену
$this->db->query('UPDATE '.DB_PREFIX.'product AS p SET p.`quantity`=0');
$this->db->query('
UPDATE '.DB_PREFIX.'product AS t1, '.$this->tmp_tbl_name.' AS t2
SET t1.`price` = t2.price_new,
t1.`quantity` = t2.in_stock,
t1.`date_modified` = NOW(),
t1.`status` = 1
WHERE t1.sku = t2.sku AND t1.manufacturer_id = t2.manufacturer;
');
// Второе. Если во временной таблице есть позиции, которых нет в основной
// добавляем их.
$this->db->query('
INSERT INTO '.DB_PREFIX.'product (`model`, `sku`, `upc`, `ean`, `jan`, `isbn`, `mpn`, `location`, `quantity`, `stock_status_id`, `image`, `manufacturer_id`, `shipping`, `price`, `tax_class_id`, `date_available`, `status`, `date_added`)
SELECT `sku`, `sku`, "", "", "", "", "", "", `in_stock`, '.(int)$this->config->get('config_stock_status_id').', "no_image.jpg", `manufacturer`, 1, `price_new`, 0, NOW(), 1, NOW() FROM
(SELECT
t1.`sku`, t1.`in_stock`, t1.`manufacturer`, t1.`price_new`
FROM '.$this->tmp_tbl_name.' AS t1
LEFT JOIN '.DB_PREFIX.'product AS t2
ON t1.sku = t2.sku AND t1.manufacturer = t2.manufacturer_id
WHERE t2.sku IS NULL) AS subtable
');
// Третье. Если во временной таблице есть позиции, которых нет в основной
// добавляем описание к ним
$languages = $this->cache->get('language');
if (empty($languages)) {
$this->load->model('localisation/language');
$languages = $this->model_localisation_language->getLanguages();
}
foreach ($languages as $language)
{
$this->db->query('
INSERT IGNORE INTO '.DB_PREFIX.'product_description (`product_id`, `language_id`, `name`, `description`, `meta_description`, `meta_keyword`, `tag`) SELECT * FROM (
SELECT
t1.product_id,
'. (int) $language['language_id'].',
CONCAT(t3.descr) AS `name`,
"" AS description,
t3.descr AS meta_description,
"" AS meta_keyword,
t1.sku AS tag
FROM '.DB_PREFIX.'product AS t1
LEFT JOIN '.DB_PREFIX.'product_description AS t2
ON t2.product_id = t1.product_id
INNER JOIN '.$this->tmp_tbl_name.' AS t3
ON t3.sku = t1.sku AND t3.manufacturer = t1.manufacturer_id
INNER JOIN '.DB_PREFIX.'manufacturer AS t4 ON t4.manufacturer_id=t1.manufacturer_id
WHERE t2.product_id IS NULL OR t2.language_id <> '. (int) $language['language_id'].'
) AS subtable
');
}
Вот тут ошибка
// Добавляем категории
/$this->db->query('INSERT IGNORE INTO '.DB_PREFIX.'product_to_category SELECT p.product_id, ttp.category FROM '.DB_PREFIX.'product p INNER JOIN '.$this->tmp_tbl_name.' ttp ON ttp.sku=p.sku');
ошибка говорит не совпадения заполняемых ячеек
(
