Presta Shop товары-атрибуты

[AndreyB]

да я имел ввиду СТУС.
на скриншоте видно что гланый товар находит и если отрыть редактор товаров атрибутов то там товары атрибуты есть, а в СТУС нету.

[AndreyB]

Если отключить "Проверять уникальность категорий и товаров по символьному идентификатору UUID (external_id)" то товары аттрибуты отображаются в СТУС см. скриншот.

перед выполнением скрипта нужно добавить поля в таблицы presta.

ALTER TABLE `ps_category` ADD `e_trade_uuid` VARCHAR(36) NOT NULL, ADD KEY (`e_trade_uuid`);
ALTER TABLE `ps_product` ADD `e_trade_uuid` VARCHAR(36) NOT NULL, ADD KEY (`e_trade_uuid`);
ALTER TABLE `ps_product_attribute` ADD `e_trade_uuid` VARCHAR(36) NOT NULL, ADD KEY (`e_trade_uuid`)

SQL Скрипт:


SET @PS_DB='shop_presta', @SHOP_LANG = 1, @SHOP_ID = 1, @OUT_OF_STOCK = 2;;;

UPDATE ps_category SET ps_category.e_trade_uuid = UUID() WHERE ps_category.e_trade_uuid = '' OR ps_category.e_trade_uuid IS NULL;;;

UPDATE ps_product SET ps_product.e_trade_uuid = UUID() WHERE ps_product.e_trade_uuid = '' OR ps_product.e_trade_uuid IS NULL;;;

UPDATE ps_product_attribute SET ps_product_attribute.e_trade_uuid = UUID() WHERE ps_product_attribute.e_trade_uuid = '' OR ps_product_attribute.e_trade_uuid IS NULL;;;

SELECT 'g' AS op_type, ps_category.id_category AS cat_id, ps_category.id_parent AS parent_id, ps_category_lang.name AS cat_name, '' AS cat_desc, ps_category_shop.`position` AS sort_order, '' AS meta_title, '' AS meta_desc, '' AS meta_keywords, '' AS cat_image, '' AS punkt, ps_category.level_depth AS nLev, ps_category.nleft AS nLft, ps_category.nright AS nRgt, '' AS CatNameFull, ps_category_lang.link_rewrite AS cat_seo_url, ps_category.active AS cat_active, ps_category.e_trade_uuid AS cat_guid, parent_pc.e_trade_uuid AS cat_parent_guid, '' AS block_id, '' AS cat_text, '' AS cat_h1, 0 AS row_exist FROM ps_category LEFT JOIN ps_category_lang ON ps_category_lang.id_category = ps_category.id_category LEFT JOIN ps_category_shop ON ps_category_shop.id_category = ps_category.id_category LEFT JOIN ps_category parent_pc ON parent_pc.id_category = ps_category.id_parent WHERE ps_category_shop.id_shop = @SHOP_ID AND ps_category_lang.id_lang = @SHOP_LANG AND ps_category_lang.id_shop = @SHOP_ID ORDER BY ps_category.id_category;;;

SELECT 'm' AS op_type, ps_manufacturer.id_manufacturer AS man_id, ps_manufacturer.name AS man_name, '' AS man_image, '' AS meta_title, '' AS meta_desc, '' AS meta_keywords, '' AS man_url, '' AS man_seo_url, '' AS man_guid, '' AS man_address, '' AS man_service, 0 AS row_exist FROM ps_manufacturer LEFT JOIN ps_manufacturer_lang ON ps_manufacturer_lang.id_manufacturer = ps_manufacturer.id_manufacturer LEFT JOIN ps_manufacturer_shop ON ps_manufacturer_shop.id_manufacturer = ps_manufacturer.id_manufacturer WHERE ps_manufacturer_lang.id_lang = @SHOP_LANG AND ps_manufacturer_shop.id_shop = @SHOP_ID ORDER BY ps_manufacturer.id_manufacturer;;;

SELECT 'p' AS op_type, ps_product.id_product AS tov_id, ps_product.id_category_default AS tov_cat_id, ps_product.reference AS tov_art, ps_product_lang.name AS tov_name, ps_product.price AS tov_price, ps_product.id_supplier AS supply_id, ps_product.id_manufacturer AS manufac_id, '' AS head_title, '' AS head_desc, '' AS head_keywords, 0 AS on_main_page, '' AS desc_small, ps_stock_available.quantity AS tov_quantity, 0 AS content_tov_id, ps_manufacturer.name AS manufac_name, '' AS model, '' AS desc_full, '' AS img1sm, '' AS img2big, 0 AS sort_order, '' AS nalich_c, '' AS addon_prices_list, 0 AS yml, ps_product_lang.link_rewrite AS tov_seo_url, ps_product.active AS tov_active, ps_product.e_trade_uuid AS tov_guid, '' AS weight, '' AS warranty, '' AS tov_art2, '' AS block_id, ps_category.e_trade_uuid AS tov_cat_guid, 0 AS best_sale, 0 AS spec_price, 0 AS tov_dissale, 0 AS tov_new, ps_product_supplier.product_supplier_reference AS tov_art_supply, '' AS tov_size, ps_product_lang.name AS tov_name1, IF(ps_stock_available.quantity > 0, 1, 2) AS tov_remain_id, 0.0 AS tov_delivery_cost, '' AS tov_color, '' AS tov_unit_measure, DATE_FORMAT(ps_product.date_upd, '%d/%m/%Y') AS price_update, 0 AS price_id, ps_product_supplier.product_supplier_price_te AS price_cost, 0 AS podtv_skl, 0.0 AS recom_price, '' AS tov_volume, 0.0 AS old_price, 0 AS tov_id_kit, '' AS tov_guid_kit, '' AS tov_h1, 0 AS sp_id, 0 AS row_exist FROM ps_product LEFT JOIN ps_product_lang ON ps_product_lang.id_product = ps_product.id_product LEFT JOIN ps_product_shop ON ps_product_shop.id_product = ps_product.id_product LEFT JOIN ps_manufacturer ON ps_manufacturer.id_manufacturer = ps_product.id_manufacturer LEFT JOIN ps_product_supplier ON ps_product_supplier.id_product = ps_product.id_product AND ps_product_supplier.id_supplier = ps_product.id_supplier AND ps_product_supplier.id_product_attribute = 0 LEFT JOIN ps_stock_available ON ps_stock_available.id_product = ps_product.id_product AND ps_stock_available.id_product_attribute = 0 AND ps_stock_available.id_shop = @SHOP_ID LEFT JOIN ps_category ON ps_category.id_category = ps_product.id_category_default WHERE ps_product_lang.id_lang = @SHOP_LANG AND ps_product_lang.id_shop = @SHOP_ID AND ps_product_shop.id_shop = @SHOP_ID ORDER BY ps_product.id_product;;;

SELECT 'pl' AS op_type, product_attribute.id_product_attribute AS tov_id, ps_product.id_category_default AS tov_cat_id, product_attribute.reference AS tov_art, CONCAT(ps_product_lang.name, ' ', product_attribute.name) AS tov_name, product_attribute.price AS tov_price, ps_product.id_supplier AS supply_id, ps_product.id_manufacturer AS manufac_id, '' AS head_title, '' AS head_desc, '' AS head_keywords, 0 AS on_main_page, '' AS desc_small, ps_stock_available.quantity AS tov_quantity, 0 AS content_tov_id, ps_manufacturer.name AS manufac_name, '' AS model, '' AS desc_full, '' AS img1sm, '' AS img2big, 0 AS sort_order, '' AS nalich_c, '' AS addon_prices_list, 0 AS yml, ps_product_lang.link_rewrite AS tov_seo_url, ps_product.active AS tov_active, product_attribute.e_trade_uuid AS tov_guid, '' AS weight, '' AS warranty, '' AS tov_art2, '' AS block_id, ps_category.e_trade_uuid AS tov_cat_guid, 0 AS best_sale, 0 AS spec_price, 0 AS tov_dissale, 0 AS tov_new, ps_product_supplier.product_supplier_reference AS tov_art_supply, '' AS tov_size, CONCAT(ps_product_lang.name, ' ', product_attribute.name) AS tov_name1, IF(ps_stock_available.quantity > 0, 1, 2) AS tov_remain_id, 0.0 AS tov_delivery_cost, '' AS tov_color, '' AS tov_unit_measure, DATE_FORMAT(ps_product.date_upd, '%d/%m/%Y') AS price_update, 0 AS price_id, ps_product_supplier.product_supplier_price_te AS price_cost, 0 AS podtv_skl, 0.0 AS recom_price, '' AS tov_volume, 0.0 AS old_price, ps_product.id_product AS tov_id_kit, ps_product.e_trade_uuid AS tov_guid_kit, '' AS tov_h1, 0 AS sp_id, 0 AS row_exist FROM ps_product INNER JOIN (SELECT ps_product_attribute.id_product, ps_product_attribute.id_product_attribute, ps_product_attribute.e_trade_uuid, ps_product_attribute.reference, GROUP_CONCAT(ps_attribute_lang.name SEPARATOR ' ') AS 'name', ps_product_attribute.price FROM ps_product_attribute INNER JOIN ps_product_attribute_combination ON ps_product_attribute_combination.id_product_attribute = ps_product_attribute.id_product_attribute INNER JOIN ps_attribute_lang ON ps_attribute_lang.id_attribute = ps_product_attribute_combination.id_attribute AND ps_attribute_lang.id_lang = @SHOP_LANG INNER JOIN ps_attribute_shop ON ps_attribute_shop.id_attribute = ps_product_attribute_combination.id_attribute WHERE ps_attribute_shop.id_shop = @SHOP_ID GROUP BY ps_product_attribute.id_product_attribute ORDER BY ps_product_attribute_combination.id_attribute) product_attribute ON ps_product.id_product = product_attribute.id_product LEFT JOIN ps_category ON ps_category.id_category = ps_product.id_category_default LEFT JOIN ps_product_lang ON ps_product_lang.id_product = ps_product.id_product LEFT JOIN ps_product_shop ON ps_product_shop.id_product = ps_product.id_product LEFT JOIN ps_manufacturer ON ps_manufacturer.id_manufacturer = ps_product.id_manufacturer LEFT JOIN ps_product_supplier ON ps_product_supplier.id_product = ps_product.id_product AND ps_product_supplier.id_supplier = ps_product.id_supplier AND ps_product_supplier.id_product_attribute = product_attribute.id_product_attribute LEFT JOIN ps_stock_available ON ps_stock_available.id_product = ps_product.id_product AND ps_stock_available.id_product_attribute = product_attribute.id_product_attribute AND ps_stock_available.id_shop = @SHOP_ID WHERE ps_product_lang.id_lang = @SHOP_LANG AND ps_product_lang.id_shop = @SHOP_ID AND ps_product_shop.id_shop = @SHOP_ID ORDER BY ps_product.id_product;;;

SELECT 'pg' AS op_type, ps_category_product.id_product AS tov_id, ps_category_product.id_category AS tov_cat_id, ps_product.e_trade_uuid AS tov_guid, ps_category.e_trade_uuid AS tov_cat_guid, 0 AS row_exist FROM ps_category_product LEFT JOIN ps_product_shop ON ps_product_shop.id_product = ps_category_product.id_product INNER JOIN ps_category ON ps_category.id_category = ps_category_product.id_category INNER JOIN ps_product ON ps_product.id_product = ps_category_product.id_product WHERE ps_product_shop.id_shop = @SHOP_ID ORDER BY ps_category_product.id_category;;;

SELECT 'af' AS op_type, product_attribute.id_product_attribute AS tov_id, product_attribute.e_trade_uuid AS tov_guid, CONCAT('_', LOWER(REPLACE(TRIM(product_attribute.group_name), ' ', '_'))) AS field_name, product_attribute.group_public_name AS field_header, product_attribute.name AS field_value, '' AS field_desc, ps_product.id_category_default AS tov_cat_id, ps_category.e_trade_uuid AS tov_cat_guid, 1 AS field_is_option, product_attribute.group_position AS field_sort_order, product_attribute.group_id AS field_id, 0 AS field_is_price, '' AS field_currency FROM ps_product INNER JOIN (SELECT ps_product_attribute.id_product, ps_product_attribute.id_product_attribute, ps_product_attribute.e_trade_uuid, ps_attribute_lang.name AS 'name', ps_attribute_group.id_attribute_group AS group_id, ps_attribute_group.`position` AS group_position, ps_attribute_group_lang.name AS group_name, ps_attribute_group_lang.public_name AS group_public_name FROM ps_product_attribute INNER JOIN ps_product_attribute_combination ON ps_product_attribute_combination.id_product_attribute = ps_product_attribute.id_product_attribute INNER JOIN ps_attribute ON ps_attribute.id_attribute = ps_product_attribute_combination.id_attribute INNER JOIN ps_attribute_lang ON ps_attribute_lang.id_attribute = ps_attribute.id_attribute AND ps_attribute_lang.id_lang = @SHOP_LANG INNER JOIN ps_attribute_group ON ps_attribute_group.id_attribute_group = ps_attribute.id_attribute_group INNER JOIN ps_attribute_group_lang ON ps_attribute_group_lang.id_attribute_group = ps_attribute_group.id_attribute_group AND ps_attribute_group_lang.id_lang = @SHOP_LANG INNER JOIN ps_attribute_shop ON ps_attribute_shop.id_attribute = ps_attribute.id_attribute INNER JOIN ps_attribute_group_shop ON ps_attribute_group_shop.id_attribute_group = ps_attribute_group.id_attribute_group WHERE ps_attribute_shop.id_shop = @SHOP_ID AND ps_attribute_group_shop.id_shop = @SHOP_ID) product_attribute ON ps_product.id_product = product_attribute.id_product LEFT JOIN ps_category ON ps_category.id_category = ps_product.id_category_default LEFT JOIN ps_product_shop ON ps_product_shop.id_product = ps_product.id_product WHERE ps_product_shop.id_shop = @SHOP_ID ORDER BY product_attribute.id_product_attribute;;;

[TechAdmin]

Если у вас есть желание, можем проверить импорт данных у себя, для этого нужен дамп вашей базы данных и список SQL запросов, данные можно отправить на почту техн. поддержки.

[TechAdmin]

Не хватает информации о категориях для товаров-атрибутов. Попробуйте заменить этот SQL запрос:

Код: Выделить всё

SELECT 'pg' AS op_type, ps_category_product.id_product AS tov_id, ps_category_product.id_category AS tov_cat_id, ps_product.e_trade_uuid AS tov_guid, ps_category.e_trade_uuid AS tov_cat_guid, 0 AS row_exist FROM ps_category_product LEFT JOIN ps_product_shop ON ps_product_shop.id_product = ps_category_product.id_product INNER JOIN ps_category ON ps_category.id_category = ps_category_product.id_category INNER JOIN ps_product ON ps_product.id_product = ps_category_product.id_product WHERE ps_product_shop.id_shop = @SHOP_ID ORDER BY ps_category_product.id_category;;;

на этот:

Код: Выделить всё

SELECT 'pg' AS op_type, ps_category_product.id_product AS tov_id, ps_category_product.id_category AS tov_cat_id, ps_product.e_trade_uuid AS tov_guid, ps_category.e_trade_uuid AS tov_cat_guid, 0 AS row_exist 
FROM ps_category_product
LEFT JOIN ps_product_shop ON ps_product_shop.id_product = ps_category_product.id_product 
INNER JOIN ps_category ON ps_category.id_category = ps_category_product.id_category INNER JOIN ps_product ON ps_product.id_product = ps_category_product.id_product 
WHERE ps_product_shop.id_shop = @SHOP_ID 
UNION 
SELECT 'pg' AS op_type, product_attribute.id_product_attribute AS tov_id, ps_category_product.id_category AS tov_cat_id, product_attribute.e_trade_uuid AS tov_guid, ps_category.e_trade_uuid AS tov_cat_guid, 0 AS row_exist 
FROM ps_category_product
LEFT JOIN ps_product_shop ON ps_product_shop.id_product = ps_category_product.id_product 
INNER JOIN ps_category ON ps_category.id_category = ps_category_product.id_category INNER JOIN ps_product ON ps_product.id_product = ps_category_product.id_product 
INNER JOIN (SELECT ps_product_attribute.id_product, ps_product_attribute.id_product_attribute, ps_product_attribute.e_trade_uuid, ps_product_attribute.reference, GROUP_CONCAT(ps_attribute_lang.name SEPARATOR ' ') AS 'name', ps_product_attribute.price FROM ps_product_attribute INNER JOIN ps_product_attribute_combination ON ps_product_attribute_combination.id_product_attribute = ps_product_attribute.id_product_attribute INNER JOIN ps_attribute_lang ON ps_attribute_lang.id_attribute = ps_product_attribute_combination.id_attribute AND ps_attribute_lang.id_lang = @SHOP_LANG INNER JOIN ps_attribute_shop ON ps_attribute_shop.id_attribute = ps_product_attribute_combination.id_attribute WHERE ps_attribute_shop.id_shop = @SHOP_ID GROUP BY ps_product_attribute.id_product_attribute ORDER BY ps_product_attribute_combination.id_attribute) product_attribute ON ps_product.id_product = product_attribute.id_product 
WHERE ps_product_shop.id_shop = @SHOP_ID;;;

[AndreyB]

Спасибо, это помогло, но теперь наблюдаются непонятные пока мне глюки при импорте.

При первом импорте все нормально, все товары импортируются и отображаются.
При последующих вызовах импорта количество товаров уменьшалось, но после очередного импорта восстановилось, при этом в магазине ничего не менял.

[AndreyB]

Немного переделал sql, товары аттрибуты отображаются, но после каждого второго импорта пропадают. Видео: https://youtu.be/1ZOVy3AfHUA

SQL запрос ограничет только товаром с id=2 для тестирования.

Код: Выделить всё

SET @PS_DB='shop_presta', @SHOP_LANG = 1, @SHOP_ID = 1, @OUT_OF_STOCK = 2;;;

DROP TABLE IF EXISTS `etrade_cats_from_site_tmp`;;;

DROP TABLE IF EXISTS `etrade_products_from_site_tmp`;;;

DROP TABLE IF EXISTS `etrade_products_kits_from_site_tmp`;;;

DROP TABLE IF EXISTS `etrade_products_addon_fields_from_site_tmp`;;;

DROP TABLE IF EXISTS `etrade_products_cats_from_site_tmp`;;;

DROP TABLE IF EXISTS `etrade_mans_from_site_tmp`;;;

DROP TABLE IF EXISTS `etrade_currency_from_site_tmp`;;;

DROP TABLE IF EXISTS `etrade_contractor_from_site_tmp`;;;

CREATE TABLE IF NOT EXISTS `etrade_cats_from_site_tmp` ( `cat_id` int(11) NOT NULL, `parent_id` int(11) NOT NULL, `cat_name` varchar(240) NOT NULL, `cat_desc` text NOT NULL, `sort_order` int(11) NOT NULL, `meta_title` text NOT NULL, `meta_desc` text NOT NULL, `meta_keywords` text NOT NULL, `cat_image` varchar(240) NOT NULL, `punkt` varchar(120) NOT NULL, `nLev` int(11) NOT NULL, `nLft` int(11) NOT NULL, `nRgt` int(11) NOT NULL, `CatNameFull` varchar(240) NOT NULL, `cat_seo_url` varchar(240) NOT NULL, `cat_active` tinyint(1) NOT NULL, `cat_guid` varchar(80) NOT NULL, `cat_parent_guid` varchar(80) NOT NULL, `block_id` varchar(80) NOT NULL, `cat_text` varchar(240) NOT NULL, `cat_h1` varchar(240) NOT NULL, `row_exist` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`cat_id`), KEY `parent_id` (`parent_id`), KEY `sort_order` (`sort_order`), KEY `nLev` (`nLev`), KEY `nLft` (`nLft`), KEY `nRgt` (`nRgt`), KEY `cat_seo_url` (`cat_seo_url`), KEY `cat_active` (`cat_active`), KEY `cat_guid` (`cat_guid`), KEY `cat_parent_guid` (`cat_parent_guid`), KEY `block_id` (`block_id`), KEY `row_exist` (`row_exist`) ) ENGINE = MYISAM DEFAULT charset = utf8;;;

CREATE TABLE IF NOT EXISTS `etrade_products_from_site_tmp` ( `tov_id` int(11) NOT NULL, `tov_cat_id` int(11) NOT NULL, `tov_art` varchar(120) NOT NULL, `tov_name` text NOT NULL, `tov_price` decimal(15, 2) NOT NULL, `supply_id` int(11) NOT NULL, `manufac_id` int(11) NOT NULL, `head_title` text NOT NULL, `head_desc` text NOT NULL, `head_keywords` text NOT NULL, `on_main_page` tinyint(4) NOT NULL, `desc_small` text NOT NULL, `tov_quantity` int(11) NOT NULL, `content_tov_id` int(11) NOT NULL, `manufac_name` varchar(120) NOT NULL, `model` varchar(120) NOT NULL, `desc_full` text NOT NULL, `img1sm` varchar(240) NOT NULL, `img2big` varchar(240) NOT NULL, `sort_order` int(11) NOT NULL, `nalich_c` varchar(60) NOT NULL, `addon_prices_list` varchar(240) NOT NULL, `yml` tinyint(1) NOT NULL, `tov_seo_url` varchar(240) NOT NULL, `tov_active` tinyint(1) NOT NULL, `tov_guid` varchar(80) NOT NULL, `weight` varchar(16) NOT NULL, `warranty` varchar(60) NOT NULL, `tov_art2` varchar(60) NOT NULL, `block_id` varchar(80) NOT NULL, `tov_cat_guid` varchar(80) NOT NULL, `best_sale` tinyint(1) NOT NULL, `spec_price` tinyint(1) NOT NULL, `tov_dissale` tinyint(1) NOT NULL, `tov_new` tinyint(1) NOT NULL, `tov_art_supply` varchar(30) NOT NULL, `tov_size` varchar(60) NOT NULL, `tov_name1` text NOT NULL, `tov_remain_id` tinyint(1) NOT NULL, `tov_delivery_cost` decimal(15, 2) NOT NULL, `tov_color` varchar(60) NOT NULL, `tov_unit_measure` varchar(16) NOT NULL, `price_update` varchar(12) NOT NULL, `price_id` int(11) NOT NULL, `price_cost` decimal(15, 2) NOT NULL, `podtv_skl` tinyint(1) NOT NULL, `recom_price` decimal(15, 2) NOT NULL, `tov_volume` varchar(16) NOT NULL, `old_price` decimal(15, 2) NOT NULL, `tov_id_kit` int(11) NOT NULL, `tov_guid_kit` varchar(80) NOT NULL, `tov_h1` varchar(240) NOT NULL, `row_exist` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`tov_guid`), KEY `tov_id` (`tov_id`), KEY `tov_cat_id` (`tov_cat_id`), KEY `tov_art` (`tov_art`), KEY `tov_art2` (`tov_art2`), KEY `supply_id` (`supply_id`), KEY `manufac_id` (`manufac_id`), KEY `manufac_name` (`manufac_name`), KEY `model` (`model`), KEY `nalich_c` (`nalich_c`), KEY `tov_active` (`tov_active`), KEY `block_id` (`block_id`), KEY `tov_cat_guid` (`tov_cat_guid`), KEY `tov_size` (`tov_size`), KEY `tov_id_kit` (`tov_id_kit`), KEY `tov_guid_kit` (`tov_guid_kit`), KEY `tov_remain_id` (`tov_remain_id`), KEY `row_exist` (`row_exist`) ) ENGINE = MYISAM DEFAULT charset = utf8;;;

CREATE TABLE IF NOT EXISTS `etrade_products_kits_from_site_tmp` ( `tov_id` int(11) NOT NULL, `tov_cat_id` int(11) NOT NULL, `tov_art` varchar(120) NOT NULL, `tov_name` text NOT NULL, `tov_price` decimal(15, 2) NOT NULL, `supply_id` int(11) NOT NULL, `manufac_id` int(11) NOT NULL, `head_title` text NOT NULL, `head_desc` text NOT NULL, `head_keywords` text NOT NULL, `on_main_page` tinyint(4) NOT NULL, `desc_small` text NOT NULL, `tov_quantity` int(11) NOT NULL, `content_tov_id` int(11) NOT NULL, `manufac_name` varchar(120) NOT NULL, `model` varchar(120) NOT NULL, `desc_full` text NOT NULL, `img1sm` varchar(240) NOT NULL, `img2big` varchar(240) NOT NULL, `sort_order` int(11) NOT NULL, `nalich_c` varchar(60) NOT NULL, `addon_prices_list` varchar(240) NOT NULL, `yml` tinyint(1) NOT NULL, `tov_seo_url` varchar(240) NOT NULL, `tov_active` tinyint(1) NOT NULL, `tov_guid` varchar(80) NOT NULL, `weight` varchar(16) NOT NULL, `warranty` varchar(60) NOT NULL, `tov_art2` varchar(60) NOT NULL, `block_id` varchar(80) NOT NULL, `tov_cat_guid` varchar(80) NOT NULL, `best_sale` tinyint(1) NOT NULL, `spec_price` tinyint(1) NOT NULL, `tov_dissale` tinyint(1) NOT NULL, `tov_new` tinyint(1) NOT NULL, `tov_art_supply` varchar(30) NOT NULL, `tov_size` varchar(60) NOT NULL, `tov_name1` text NOT NULL, `tov_remain_id` tinyint(1) NOT NULL, `tov_delivery_cost` decimal(15, 2) NOT NULL, `tov_color` varchar(60) NOT NULL, `tov_unit_measure` varchar(16) NOT NULL, `price_update` varchar(12) NOT NULL, `price_id` int(11) NOT NULL, `price_cost` decimal(15, 2) NOT NULL, `podtv_skl` tinyint(1) NOT NULL, `recom_price` decimal(15, 2) NOT NULL, `tov_volume` varchar(16) NOT NULL, `old_price` decimal(15, 2) NOT NULL, `tov_id_kit` int(11) NOT NULL, `tov_guid_kit` varchar(80) NOT NULL, `tov_h1` varchar(240) NOT NULL, `row_exist` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`tov_guid`), KEY `tov_id` (`tov_id`), KEY `tov_cat_id` (`tov_cat_id`), KEY `tov_art` (`tov_art`), KEY `tov_art2` (`tov_art2`), KEY `supply_id` (`supply_id`), KEY `manufac_id` (`manufac_id`), KEY `manufac_name` (`manufac_name`), KEY `model` (`model`), KEY `nalich_c` (`nalich_c`), KEY `tov_active` (`tov_active`), KEY `block_id` (`block_id`), KEY `tov_cat_guid` (`tov_cat_guid`), KEY `tov_size` (`tov_size`), KEY `tov_id_kit` (`tov_id_kit`), KEY `tov_guid_kit` (`tov_guid_kit`), KEY `tov_remain_id` (`tov_remain_id`), KEY `row_exist` (`row_exist`) ) ENGINE = MYISAM DEFAULT charset = utf8;;;

CREATE TABLE IF NOT EXISTS `etrade_products_cats_from_site_tmp` ( `tov_id` int(11) NOT NULL, `tov_cat_id` int(11) NOT NULL, `tov_guid` varchar(80) NOT NULL, `tov_cat_guid` varchar(80) NOT NULL, `row_exist` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`tov_guid`, `tov_cat_guid`), KEY (`tov_id`), KEY (`tov_cat_id`), KEY `row_exist` (`row_exist`) ) ENGINE = MYISAM DEFAULT charset = utf8;;;

CREATE TABLE IF NOT EXISTS `etrade_products_addon_fields_from_site_tmp` ( `tov_id` int(11) NOT NULL, `tov_guid` varchar(80) NOT NULL, `field_name` varchar(30) NOT NULL, `field_header` varchar(60) NOT NULL, `field_value` text NOT NULL, `field_desc` varchar(240) NOT NULL, `tov_cat_id` int(11) NOT NULL, `tov_cat_guid` varchar(80) NOT NULL, `field_is_option` tinyint(1) NOT NULL, `field_sort_order` int(11) NOT NULL, `field_id` int(11) NOT NULL, `field_is_price` tinyint(1) NOT NULL, `field_currency` varchar(40) NOT NULL, KEY `tov_id` (`tov_id`), KEY `tov_guid` (`tov_guid`), KEY `tov_cat_id` (`tov_cat_id`), KEY `tov_cat_guid` (`tov_cat_guid`), KEY `field_header` (`field_header`), KEY `field_is_option` (`field_is_option`), KEY `field_id` (`field_id`), KEY `field_is_price` (`field_is_price`), KEY `field_name` (`field_name`) ) ENGINE = MYISAM DEFAULT charset = utf8;;;

CREATE TABLE IF NOT EXISTS `etrade_mans_from_site_tmp` ( `man_id` int(11) NOT NULL, `man_name` varchar(60) NOT NULL, `man_image` varchar(240) NOT NULL, `meta_title` varchar(240) NOT NULL, `meta_desc` varchar(240) NOT NULL, `meta_keywords` varchar(240) NOT NULL, `man_url` varchar(80) NOT NULL, `man_seo_url` varchar(240) NOT NULL, `man_guid` varchar(80) NOT NULL, `man_address` text NOT NULL, `man_service` text NOT NULL, `row_exist` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`man_id`), KEY `man_seo_url` (`man_seo_url`), KEY `man_guid` (`man_guid`), KEY `row_exist` (`row_exist`) ) ENGINE = MYISAM DEFAULT charset = utf8;;;

CREATE TABLE IF NOT EXISTS `etrade_currency_from_site_tmp` ( `currency_id` int(11) NOT NULL, `curr_sname` varchar(20) NOT NULL, `curr_iso` int(11) NOT NULL, `curr_name` varchar(80) NOT NULL, `curr_kurs` decimal(15, 8) NOT NULL, `curr_date` varchar(12) NOT NULL, `curr_default` tinyint(1) NOT NULL DEFAULT '0', `row_exist` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`currency_id`), KEY `curr_sname` (`curr_sname`), KEY `curr_default` (`curr_default`), KEY `row_exist` (`row_exist`) ) ENGINE = MYISAM DEFAULT charset = utf8;;;

CREATE TABLE IF NOT EXISTS `etrade_contractor_from_site_tmp` ( `contractor_id` int(11) NOT NULL, `contractor_type` int(11) NOT NULL, `contractor_name` varchar(120) NOT NULL, `contractor_url` varchar(120) NOT NULL, `contractor_email` varchar(120) NOT NULL, `contractor_county` varchar(120) NOT NULL, `contractor_zone` varchar(120) NOT NULL, `contractor_city` varchar(120) NOT NULL, `contractor_addresslocation` varchar(240) NOT NULL, `contractor_addresslaw` varchar(240) NOT NULL, `contractor_phones` varchar(120) NOT NULL, `contractor_faxes` varchar(120) NOT NULL, `contractor_taxcode` varchar(120) NOT NULL, `contractor_taxnumber` varchar(120) NOT NULL, `contractor_bankaccountno` varchar(120) NOT NULL, `contractor_bankokpo` varchar(120) NOT NULL, `contractor_bankmfo` varchar(120) NOT NULL, `contractor_bankname` varchar(120) NOT NULL, `contractor_contactperson` varchar(120) NOT NULL, `contractor_transport_price` decimal(15, 8) NOT NULL, `contractor_date_add` varchar(12) NOT NULL, `contractor_comment` varchar(240) NOT NULL, `contractor_id_ext` varchar(80) NOT NULL, `row_exist` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`contractor_id`), KEY `contractor_type` (`contractor_type`), KEY `contractor_id_ext` (`contractor_id_ext`), KEY `row_exist` (`row_exist`) ) ENGINE = MYISAM DEFAULT charset = utf8;;;

UPDATE ps_category SET ps_category.e_trade_uuid = UUID() WHERE ps_category.e_trade_uuid = '' OR ps_category.e_trade_uuid IS NULL;;;

UPDATE ps_product SET ps_product.e_trade_uuid = UUID() WHERE ps_product.e_trade_uuid = '' OR ps_product.e_trade_uuid IS NULL;;;

UPDATE ps_product_attribute SET ps_product_attribute.e_trade_uuid = UUID() WHERE ps_product_attribute.e_trade_uuid = '' OR ps_product_attribute.e_trade_uuid IS NULL;;;

INSERT INTO etrade_cats_from_site_tmp (cat_id, parent_id, cat_name, sort_order, nLev, nLft, nRgt, cat_seo_url, cat_active, cat_guid, cat_parent_guid) SELECT ps_category.id_category AS cat_id, ps_category.id_parent AS parent_id, ps_category_lang.name AS cat_name, ps_category_shop.`position` AS sort_order, ps_category.level_depth AS nLev, ps_category.nleft AS nLft, ps_category.nright AS nRgt, ps_category_lang.link_rewrite AS cat_seo_url, ps_category.active AS cat_active, ps_category.e_trade_uuid AS cat_guid, parent_pc.e_trade_uuid AS cat_parent_guid FROM ps_category LEFT JOIN ps_category_lang ON ps_category_lang.id_category = ps_category.id_category LEFT JOIN ps_category_shop ON ps_category_shop.id_category = ps_category.id_category LEFT JOIN ps_category parent_pc ON parent_pc.id_category = ps_category.id_parent WHERE ps_category_shop.id_shop = @SHOP_ID AND ps_category_lang.id_lang = @SHOP_LANG AND ps_category_lang.id_shop = @SHOP_ID;;;

INSERT INTO etrade_mans_from_site_tmp (man_id, man_name) SELECT ps_manufacturer.id_manufacturer AS man_id, ps_manufacturer.name AS man_name FROM ps_manufacturer LEFT JOIN ps_manufacturer_lang ON ps_manufacturer_lang.id_manufacturer = ps_manufacturer.id_manufacturer LEFT JOIN ps_manufacturer_shop ON ps_manufacturer_shop.id_manufacturer = ps_manufacturer.id_manufacturer WHERE ps_manufacturer_lang.id_lang = @SHOP_LANG AND ps_manufacturer_shop.id_shop = @SHOP_ID;;;

INSERT INTO etrade_products_from_site_tmp (tov_id, tov_cat_id, tov_art, tov_name, tov_price, supply_id, manufac_id, tov_quantity, manufac_name, tov_seo_url, tov_active, tov_guid, tov_cat_guid, tov_art_supply, tov_name1, tov_remain_id, price_update, price_cost) SELECT ps_product.id_product AS tov_id, ps_product.id_category_default AS tov_cat_id, ps_product.reference AS tov_art, ps_product_lang.name AS tov_name, ps_product.price AS tov_price, ps_product.id_supplier AS supply_id, ps_product.id_manufacturer AS manufac_id, ps_stock_available.quantity AS tov_quantity, ps_manufacturer.name AS manufac_name, ps_product_lang.link_rewrite AS tov_seo_url, ps_product.active AS tov_active, ps_product.e_trade_uuid AS tov_guid, ps_category.e_trade_uuid AS tov_cat_guid, ps_product_supplier.product_supplier_reference AS tov_art_supply, ps_product_lang.name AS tov_name1, IF(ps_stock_available.quantity > 0, 1, 2) AS tov_remain_id, DATE_FORMAT(ps_product.date_upd, '%d/%m/%Y') AS price_update, ps_product_supplier.product_supplier_price_te AS price_cost FROM ps_product LEFT JOIN ps_product_lang ON ps_product_lang.id_product = ps_product.id_product LEFT JOIN ps_product_shop ON ps_product_shop.id_product = ps_product.id_product LEFT JOIN ps_manufacturer ON ps_manufacturer.id_manufacturer = ps_product.id_manufacturer LEFT JOIN ps_product_supplier ON ps_product_supplier.id_product = ps_product.id_product AND ps_product_supplier.id_supplier = ps_product.id_supplier AND ps_product_supplier.id_product_attribute = 0 LEFT JOIN ps_stock_available ON ps_stock_available.id_product = ps_product.id_product AND ps_stock_available.id_product_attribute = 0 AND ps_stock_available.id_shop = @SHOP_ID LEFT JOIN ps_category ON ps_category.id_category = ps_product.id_category_default WHERE ps_product.id_product = 2 AND ps_product_lang.id_lang = @SHOP_LANG AND ps_product_lang.id_shop = @SHOP_ID AND ps_product_shop.id_shop = @SHOP_ID;;;

INSERT INTO etrade_products_kits_from_site_tmp (tov_art, tov_name, tov_price, supply_id, manufac_id, tov_quantity, manufac_name, tov_seo_url, tov_active, tov_guid, tov_cat_guid, tov_art_supply, tov_name1, tov_remain_id, price_update, price_cost, tov_guid_kit) SELECT product_attribute.reference AS tov_art, CONCAT(ps_product_lang.name, ' ', product_attribute.name) AS tov_name, product_attribute.price AS tov_price, ps_product.id_supplier AS supply_id, ps_product.id_manufacturer AS manufac_id, ps_stock_available.quantity AS tov_quantity, ps_manufacturer.name AS manufac_name, ps_product_lang.link_rewrite AS tov_seo_url, ps_product.active AS tov_active, product_attribute.e_trade_uuid AS tov_guid, ps_category.e_trade_uuid AS tov_cat_guid, ps_product_supplier.product_supplier_reference AS tov_art_supply, CONCAT(ps_product_lang.name, ' ', product_attribute.name) AS tov_name1, IF(ps_stock_available.quantity > 0, 1, 2) AS tov_remain_id, DATE_FORMAT(ps_product.date_upd, '%d/%m/%Y') AS price_update, ps_product_supplier.product_supplier_price_te AS price_cost, ps_product.e_trade_uuid AS tov_guid_kit FROM ps_product INNER JOIN (SELECT ps_product_attribute.id_product, ps_product_attribute.id_product_attribute, ps_product_attribute.e_trade_uuid, ps_product_attribute.reference, GROUP_CONCAT(ps_attribute_lang.name SEPARATOR ' ') AS 'name', ps_product_attribute.price FROM ps_product_attribute INNER JOIN ps_product_attribute_combination ON ps_product_attribute_combination.id_product_attribute = ps_product_attribute.id_product_attribute INNER JOIN ps_attribute_lang ON ps_attribute_lang.id_attribute = ps_product_attribute_combination.id_attribute AND ps_attribute_lang.id_lang = @SHOP_LANG INNER JOIN ps_attribute_shop ON ps_attribute_shop.id_attribute = ps_product_attribute_combination.id_attribute WHERE ps_attribute_shop.id_shop = @SHOP_ID GROUP BY ps_product_attribute.id_product_attribute ORDER BY ps_product_attribute_combination.id_attribute) product_attribute ON ps_product.id_product = product_attribute.id_product LEFT JOIN ps_category ON ps_category.id_category = ps_product.id_category_default LEFT JOIN ps_product_lang ON ps_product_lang.id_product = ps_product.id_product LEFT JOIN ps_product_shop ON ps_product_shop.id_product = ps_product.id_product LEFT JOIN ps_manufacturer ON ps_manufacturer.id_manufacturer = ps_product.id_manufacturer LEFT JOIN ps_product_supplier ON ps_product_supplier.id_product = ps_product.id_product AND ps_product_supplier.id_supplier = ps_product.id_supplier AND ps_product_supplier.id_product_attribute = product_attribute.id_product_attribute LEFT JOIN ps_stock_available ON ps_stock_available.id_product = ps_product.id_product AND ps_stock_available.id_product_attribute = product_attribute.id_product_attribute AND ps_stock_available.id_shop = @SHOP_ID WHERE ps_product.id_product = 2 AND ps_product_lang.id_lang = @SHOP_LANG AND ps_product_lang.id_shop = @SHOP_ID AND ps_product_shop.id_shop = @SHOP_ID;;;

INSERT INTO etrade_products_cats_from_site_tmp (tov_id, tov_cat_id, tov_guid, tov_cat_guid) SELECT ps_category_product.id_product AS tov_id, ps_category_product.id_category AS tov_cat_id, ps_product.e_trade_uuid AS tov_guid, ps_category.e_trade_uuid AS tov_cat_guid FROM ps_category_product LEFT JOIN ps_product_shop ON ps_product_shop.id_product = ps_category_product.id_product INNER JOIN ps_category ON ps_category.id_category = ps_category_product.id_category INNER JOIN ps_product ON ps_product.id_product = ps_category_product.id_product WHERE ps_category_product.id_product = 2 AND ps_product_shop.id_shop = @SHOP_ID;;;

INSERT INTO etrade_products_cats_from_site_tmp (tov_id, tov_cat_id, tov_guid, tov_cat_guid) SELECT product_attribute.id_product_attribute AS tov_id, ps_product.id_category_default AS tov_cat_id, product_attribute.e_trade_uuid AS tov_guid, ps_category.e_trade_uuid AS tov_cat_guid FROM ps_product INNER JOIN (SELECT ps_product_attribute.id_product, ps_product_attribute.id_product_attribute, ps_product_attribute.e_trade_uuid FROM ps_product_attribute INNER JOIN ps_product_attribute_combination ON ps_product_attribute_combination.id_product_attribute = ps_product_attribute.id_product_attribute INNER JOIN ps_attribute_lang ON ps_attribute_lang.id_attribute = ps_product_attribute_combination.id_attribute AND ps_attribute_lang.id_lang = @SHOP_LANG INNER JOIN ps_attribute_shop ON ps_attribute_shop.id_attribute = ps_product_attribute_combination.id_attribute WHERE ps_attribute_shop.id_shop = @SHOP_ID GROUP BY ps_product_attribute.id_product_attribute ORDER BY ps_product_attribute_combination.id_attribute) product_attribute ON ps_product.id_product = product_attribute.id_product LEFT JOIN ps_category ON ps_category.id_category = ps_product.id_category_default LEFT JOIN ps_product_lang ON ps_product_lang.id_product = ps_product.id_product LEFT JOIN ps_product_shop ON ps_product_shop.id_product = ps_product.id_product WHERE ps_product.id_product = 2 AND ps_product_lang.id_lang = @SHOP_LANG AND ps_product_lang.id_shop = @SHOP_ID AND ps_product_shop.id_shop = @SHOP_ID ORDER BY product_attribute.id_product_attribute;;;

INSERT INTO etrade_products_addon_fields_from_site_tmp (tov_id, tov_guid, field_name, field_header, field_value, tov_cat_id, tov_cat_guid, field_sort_order, field_is_option, field_id, field_is_price) SELECT product_attribute.id_product_attribute AS tov_id, product_attribute.e_trade_uuid AS tov_guid, CONCAT('_', LOWER(REPLACE(TRIM(product_attribute.group_name), ' ', '_'))) AS field_name, product_attribute.group_public_name AS field_header, product_attribute.name AS field_value, ps_product.id_category_default AS tov_cat_id, ps_category.e_trade_uuid AS tov_cat_guid, 1 AS field_is_option, product_attribute.group_position AS field_sort_order, product_attribute.group_id AS field_id, 0 AS field_is_price FROM ps_product INNER JOIN (SELECT ps_product_attribute.id_product, ps_product_attribute.id_product_attribute, ps_product_attribute.e_trade_uuid, ps_attribute_lang.name AS 'name', ps_attribute_group.id_attribute_group AS group_id, ps_attribute_group.`position` AS group_position, ps_attribute_group_lang.name AS group_name, ps_attribute_group_lang.public_name AS group_public_name FROM ps_product_attribute INNER JOIN ps_product_attribute_combination ON ps_product_attribute_combination.id_product_attribute = ps_product_attribute.id_product_attribute INNER JOIN ps_attribute ON ps_attribute.id_attribute = ps_product_attribute_combination.id_attribute INNER JOIN ps_attribute_lang ON ps_attribute_lang.id_attribute = ps_attribute.id_attribute AND ps_attribute_lang.id_lang = @SHOP_LANG INNER JOIN ps_attribute_group ON ps_attribute_group.id_attribute_group = ps_attribute.id_attribute_group INNER JOIN ps_attribute_group_lang ON ps_attribute_group_lang.id_attribute_group = ps_attribute_group.id_attribute_group AND ps_attribute_group_lang.id_lang = @SHOP_LANG INNER JOIN ps_attribute_shop ON ps_attribute_shop.id_attribute = ps_attribute.id_attribute INNER JOIN ps_attribute_group_shop ON ps_attribute_group_shop.id_attribute_group = ps_attribute_group.id_attribute_group WHERE ps_attribute_shop.id_shop = @SHOP_ID AND ps_attribute_group_shop.id_shop = @SHOP_ID) product_attribute ON ps_product.id_product = product_attribute.id_product LEFT JOIN ps_category ON ps_category.id_category = ps_product.id_category_default LEFT JOIN ps_product_shop ON ps_product_shop.id_product = ps_product.id_product WHERE ps_product.id_product = 2 AND ps_product_shop.id_shop = @SHOP_ID;;;

SELECT 'g' AS op_type, etrade_cats_from_site_tmp.* FROM etrade_cats_from_site_tmp ORDER BY etrade_cats_from_site_tmp.cat_id;;;

SELECT 'm' AS op_type, etrade_mans_from_site_tmp.* FROM etrade_mans_from_site_tmp ORDER BY etrade_mans_from_site_tmp.man_id;;;

SELECT 'p' AS op_type, etrade_products_from_site_tmp.* FROM etrade_products_from_site_tmp ORDER BY etrade_products_from_site_tmp.tov_id;;;

SELECT 'pl' AS op_type, etrade_products_kits_from_site_tmp.* FROM etrade_products_kits_from_site_tmp ORDER BY etrade_products_kits_from_site_tmp.tov_id;;;

SELECT 'pg' AS op_type, etrade_products_cats_from_site_tmp.* FROM etrade_products_cats_from_site_tmp ORDER BY etrade_products_cats_from_site_tmp.tov_cat_id;;;

SELECT 'af' AS op_type, etrade_products_addon_fields_from_site_tmp.* FROM etrade_products_addon_fields_from_site_tmp ORDER BY etrade_products_addon_fields_from_site_tmp.tov_id;;;

[TechAdmin]

При активации флажка "Удалять категории и товары в учётной системе, которых нет в импортируемом файле" удаление происходит на основании числовых ID, не учитывается настройка учитывать уникальность по UUID, поэтому могут быть проблемы с очисткой каталога. Доработали для вас программу, чтобы учитывалась "настройка UUID", уже доступна новая версия, попробуйте на этой версии импорт данных с сайта.

[AndreyB]

Спасибо, работает, есть еще вопрос.
Есть существующая огромная база PLI - товаров более 100000. Сейчас работает с Simpla. синхронизируется по ID.
Возможно ли переключить ее на синхронизацию по UUID с Simpla, а после переключить на синхронизацию с Presta при условии что в Presta сохранятся теже товары и их uuid что и в Simpla?

[AndreyB]

И еще похоже нашел место где не учитывается UUID - дополнительные поля (af).
Столбец Color и Size на скриншоте - дополнительные поля импортируются с сайта.

[TechAdmin]

Доработали импорт доп. полей, с учётом удаления по полю UUID.