Last active
September 28, 2024 14:19
-
-
Save Zszywaczyk/779d5feaca3344315ed4960920fcbc15 to your computer and use it in GitHub Desktop.
MySQL script for exporting products, users from PrestaShop 1.6.1, intended for migration to WooCommerce.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SET @base_url := 'https://example.com'; | |
| SELECT * FROM ( | |
| -- Part 1: Basic Products (without combinations) | |
| SELECT | |
| p1.id_product AS product_id, | |
| pl1.name AS product_name, | |
| pl1.link_rewrite AS link_rewrite, | |
| pl1.meta_description AS meta_description, | |
| pl1.meta_keywords AS meta_keywords, | |
| pl1.meta_title AS meta_title, | |
| p1.date_add AS date_add, | |
| sa1.quantity AS quantity, | |
| CONCAT(@base_url, '/product/', REPLACE(LOWER(pl1.name), ' ', '-'), '/') AS product_link, | |
| pl1.description AS description, | |
| pl1.description_short AS short_description, | |
| GROUP_CONCAT(DISTINCT CONCAT_WS('>', cl3_1.name, cl2_1.name, cl1_1.name) ORDER BY cl3_1.name, cl2_1.name, cl1_1.name SEPARATOR ', ') AS category_path, | |
| (CASE | |
| WHEN p1.is_virtual = 1 THEN 'Wirtualny' | |
| WHEN pack1.id_product_pack IS NOT NULL THEN 'Pakiet produktów' | |
| ELSE 'simple' | |
| END) AS product_type, | |
| CONCAT(@base_url, '/img/p/', | |
| SUBSTRING(pi1.id_image, 1, 1), '/', | |
| IF(LENGTH(pi1.id_image) > 1, CONCAT(SUBSTRING(pi1.id_image, 2, 1), '/'), ''), | |
| IF(LENGTH(pi1.id_image) > 2, CONCAT(SUBSTRING(pi1.id_image, 3, 1), '/'), ''), | |
| IF(LENGTH(pi1.id_image) > 3, CONCAT(SUBSTRING(pi1.id_image, 4, 1), '/'), ''), | |
| IF(LENGTH(pi1.id_image) > 4, CONCAT(SUBSTRING(pi1.id_image, 5, 1), '/'), ''), | |
| IF(LENGTH(pi1.id_image) > 5, CONCAT(SUBSTRING(pi1.id_image, 6, 1), '/'), ''), | |
| IF(LENGTH(pi1.id_image) > 6, CONCAT(SUBSTRING(pi1.id_image, 7, 1), '/'), ''), | |
| pi1.id_image, '.jpg') AS main_image, | |
| GROUP_CONCAT(DISTINCT CONCAT(@base_url, '/img/p/', | |
| SUBSTRING(pi1.id_image, 1, 1), '/', | |
| IF(LENGTH(pi1.id_image) > 1, CONCAT(SUBSTRING(pi1.id_image, 2, 1), '/'), ''), | |
| IF(LENGTH(pi1.id_image) > 2, CONCAT(SUBSTRING(pi1.id_image, 3, 1), '/'), ''), | |
| IF(LENGTH(pi1.id_image) > 3, CONCAT(SUBSTRING(pi1.id_image, 4, 1), '/'), ''), | |
| IF(LENGTH(pi1.id_image) > 4, CONCAT(SUBSTRING(pi1.id_image, 5, 1), '/'), ''), | |
| IF(LENGTH(pi1.id_image) > 5, CONCAT(SUBSTRING(pi1.id_image, 6, 1), '/'), ''), | |
| IF(LENGTH(pi1.id_image) > 6, CONCAT(SUBSTRING(pi1.id_image, 7, 1), '/'), ''), | |
| pi1.id_image, '.jpg') SEPARATOR ', ') AS image_links, | |
| NULL AS parent_ean13, | |
| p1.ean13 AS ean13, | |
| p1.price AS price, | |
| '' AS attr_id, | |
| '' AS attr_1, | |
| '' AS attr_value_1, | |
| '' AS attr_2, | |
| '' AS attr_value_2, | |
| '' AS attr_3, | |
| '' AS attr_value_3, | |
| '' AS attr_4, | |
| '' AS attr_value_4, | |
| IF(pack1.id_product_pack IS NOT NULL, GROUP_CONCAT(DISTINCT pack1.id_product_item SEPARATOR ', '), '') AS related_products, | |
| IF(p1.is_virtual = 1, GROUP_CONCAT(DISTINCT CONCAT(@base_url, '/download/', pd1.filename) SEPARATOR ', '), '') AS virtual_files, | |
| (CASE | |
| WHEN p1.active = 1 THEN 'publish' | |
| ELSE 'draft' | |
| END) AS product_status | |
| FROM | |
| ps_product p1 | |
| JOIN | |
| ps_product_lang pl1 ON p1.id_product = pl1.id_product AND pl1.id_lang = 6 | |
| LEFT JOIN | |
| ps_category_product cp1 ON p1.id_product = cp1.id_product | |
| LEFT JOIN | |
| ps_category c1_1 ON cp1.id_category = c1_1.id_category | |
| LEFT JOIN | |
| ps_category_lang cl1_1 ON c1_1.id_category = cl1_1.id_category AND cl1_1.id_lang = 6 | |
| LEFT JOIN | |
| ps_category c2_1 ON c1_1.id_parent = c2_1.id_category | |
| LEFT JOIN | |
| ps_category_lang cl2_1 ON c2_1.id_category = cl2_1.id_category AND cl2_1.id_lang = 6 | |
| LEFT JOIN | |
| ps_category c3_1 ON c2_1.id_parent = c3_1.id_category | |
| LEFT JOIN | |
| ps_category_lang cl3_1 ON c3_1.id_category = cl3_1.id_category AND cl3_1.id_lang = 6 | |
| LEFT JOIN | |
| ps_image pi1 ON p1.id_product = pi1.id_product | |
| LEFT JOIN | |
| (SELECT id_product, id_image FROM ps_image WHERE cover = 1) i1 ON p1.id_product = i1.id_product | |
| LEFT JOIN | |
| ps_pack pack1 ON p1.id_product = pack1.id_product_pack | |
| LEFT JOIN | |
| ps_product_download pd1 ON p1.id_product = pd1.id_product | |
| JOIN | |
| ps_stock_available sa1 ON p1.id_product = sa1.id_product AND sa1.id_product_attribute = 0 | |
| JOIN | |
| ps_product_shop ps1 ON p1.id_product = ps1.id_product AND ps1.id_shop = 1 | |
| WHERE | |
| ps1.id_shop = 1 | |
| GROUP BY | |
| p1.id_product | |
| UNION ALL | |
| -- Part 2: Product Combinations | |
| SELECT | |
| p2.id_product AS product_id, | |
| pl2.name AS product_name, | |
| pl2.link_rewrite AS link_rewrite, | |
| pl2.meta_description AS meta_description, | |
| pl2.meta_keywords AS meta_keywords, | |
| pl2.meta_title AS meta_title, | |
| p2.date_add AS date_add, | |
| sa2.quantity AS quantity, | |
| CONCAT(@base_url, '/product/', REPLACE(LOWER(pl2.name), ' ', '-'), '/') AS product_link, | |
| pl2.description AS description, | |
| pl2.description_short AS short_description, | |
| GROUP_CONCAT(DISTINCT CONCAT_WS('>', cl3_2.name, cl2_2.name, cl1_2.name) ORDER BY cl3_2.name, cl2_2.name, cl1_2.name SEPARATOR ', ') AS category_path, | |
| 'variable' AS product_type, | |
| CONCAT(@base_url, '/img/p/', | |
| SUBSTRING(pi2.id_image, 1, 1), '/', | |
| IF(LENGTH(pi2.id_image) > 1, CONCAT(SUBSTRING(pi2.id_image, 2, 1), '/'), ''), | |
| IF(LENGTH(pi2.id_image) > 2, CONCAT(SUBSTRING(pi2.id_image, 3, 1), '/'), ''), | |
| IF(LENGTH(pi2.id_image) > 3, CONCAT(SUBSTRING(pi2.id_image, 4, 1), '/'), ''), | |
| IF(LENGTH(pi2.id_image) > 4, CONCAT(SUBSTRING(pi2.id_image, 5, 1), '/'), ''), | |
| IF(LENGTH(pi2.id_image) > 5, CONCAT(SUBSTRING(pi2.id_image, 6, 1), '/'), ''), | |
| IF(LENGTH(pi2.id_image) > 6, CONCAT(SUBSTRING(pi2.id_image, 7, 1), '/'), ''), | |
| pi2.id_image, '.jpg') AS main_image, | |
| GROUP_CONCAT(DISTINCT CONCAT(@base_url, '/img/p/', | |
| SUBSTRING(pi2.id_image, 1, 1), '/', | |
| IF(LENGTH(pi2.id_image) > 1, CONCAT(SUBSTRING(pi2.id_image, 2, 1), '/'), ''), | |
| IF(LENGTH(pi2.id_image) > 2, CONCAT(SUBSTRING(pi2.id_image, 3, 1), '/'), ''), | |
| IF(LENGTH(pi2.id_image) > 3, CONCAT(SUBSTRING(pi2.id_image, 4, 1), '/'), ''), | |
| IF(LENGTH(pi2.id_image) > 4, CONCAT(SUBSTRING(pi2.id_image, 5, 1), '/'), ''), | |
| IF(LENGTH(pi2.id_image) > 5, CONCAT(SUBSTRING(pi2.id_image, 6, 1), '/'), ''), | |
| IF(LENGTH(pi2.id_image) > 6, CONCAT(SUBSTRING(pi2.id_image, 7, 1), '/'), ''), | |
| pi2.id_image, '.jpg') SEPARATOR ', ') AS image_links, | |
| p2.ean13 AS parent_ean13, | |
| pa2.ean13 AS ean13, | |
| p2.price + pa2.price AS price, | |
| pa2.id_product_attribute AS attr_id, | |
| agl2.name AS attr_1, | |
| atl2.name AS attr_value_1, | |
| '' AS attr_2, | |
| '' AS attr_value_2, | |
| '' AS attr_3, | |
| '' AS attr_value_3, | |
| '' AS attr_4, | |
| '' AS attr_value_4, | |
| IF(pack2.id_product_pack IS NOT NULL, GROUP_CONCAT(DISTINCT pack2.id_product_item SEPARATOR ', '), '') AS related_products, | |
| IF(p2.is_virtual = 1, GROUP_CONCAT(DISTINCT CONCAT(@base_url, '/download/', pd2.filename) SEPARATOR ', '), '') AS virtual_files, | |
| (CASE | |
| WHEN p2.active = 1 THEN 'publish' | |
| ELSE 'draft' | |
| END) AS product_status | |
| FROM | |
| ps_product p2 | |
| JOIN | |
| ps_product_lang pl2 ON p2.id_product = pl2.id_product AND pl2.id_lang = 6 | |
| LEFT JOIN | |
| ps_category_product cp2 ON p2.id_product = cp2.id_product | |
| LEFT JOIN | |
| ps_category c1_2 ON cp2.id_category = c1_2.id_category | |
| LEFT JOIN | |
| ps_category_lang cl1_2 ON c1_2.id_category = cl1_2.id_category AND cl1_2.id_lang = 6 | |
| LEFT JOIN | |
| ps_category c2_2 ON c1_2.id_parent = c2_2.id_category | |
| LEFT JOIN | |
| ps_category_lang cl2_2 ON c2_2.id_category = cl2_2.id_category AND cl2_2.id_lang = 6 | |
| LEFT JOIN | |
| ps_category c3_2 ON c2_2.id_parent = c3_2.id_category | |
| LEFT JOIN | |
| ps_category_lang cl3_2 ON c3_2.id_category = cl3_2.id_category AND cl3_2.id_lang = 6 | |
| LEFT JOIN | |
| ps_image pi2 ON p2.id_product = pi2.id_product | |
| LEFT JOIN | |
| (SELECT id_product, id_image FROM ps_image WHERE cover = 1) i2 ON p2.id_product = i2.id_product | |
| LEFT JOIN | |
| ps_pack pack2 ON p2.id_product = pack2.id_product_pack | |
| LEFT JOIN | |
| ps_product_download pd2 ON p2.id_product = pd2.id_product | |
| JOIN | |
| ps_product_attribute pa2 ON p2.id_product = pa2.id_product | |
| JOIN | |
| ps_stock_available sa2 ON pa2.id_product_attribute = sa2.id_product_attribute | |
| JOIN | |
| ps_product_attribute_combination pac2 ON pa2.id_product_attribute = pac2.id_product_attribute | |
| JOIN | |
| ps_attribute a2 ON pac2.id_attribute = a2.id_attribute | |
| JOIN | |
| ps_attribute_lang atl2 ON a2.id_attribute = atl2.id_attribute AND atl2.id_lang = 6 | |
| JOIN | |
| ps_attribute_group ag2 ON a2.id_attribute_group = ag2.id_attribute_group | |
| JOIN | |
| ps_attribute_group_lang agl2 ON ag2.id_attribute_group = agl2.id_attribute_group AND agl2.id_lang = 6 | |
| JOIN | |
| ps_product_shop ps2 ON p2.id_product = ps2.id_product AND ps2.id_shop = 1 | |
| WHERE | |
| ps2.id_shop = 1 | |
| GROUP BY | |
| pa2.id_product_attribute | |
| ) AS combined_results | |
| ORDER BY | |
| combined_results.product_id; |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| cu.id_customer, | |
| cu.firstname, | |
| cu.lastname, | |
| cu.email, | |
| COALESCE(NULLIF(ca.phone, ''), ca.phone_mobile) AS phone, -- Jeśli phone jest puste, to używa phone_mobile | |
| CASE | |
| WHEN cu.is_guest = 1 AND cnon_guest.passwd IS NOT NULL THEN cnon_guest.passwd | |
| ELSE cu.passwd | |
| END AS passwd, -- Pobieranie hasła z rekordu klienta, jeśli is_guest = 1 | |
| cu.date_add AS date_added, | |
| cu.birthday, | |
| cu.newsletter, | |
| cu.ip_registration_newsletter, | |
| cu.newsletter_date_add, | |
| cu.optin, | |
| ca.company, | |
| ca.address1, | |
| ca.address2, | |
| co.iso_code AS country_iso_code, | |
| ca.city, | |
| ca.postcode | |
| FROM ps_customer cu | |
| LEFT JOIN ps_address ca ON cu.id_customer = ca.id_customer | |
| LEFT JOIN ps_country co ON ca.id_country = co.id_country | |
| LEFT JOIN ps_customer cnon_guest ON cu.email = cnon_guest.email AND cnon_guest.is_guest = 0 -- Łączenie z rekordem klienta (nie gościa) | |
| WHERE cu.id_customer IS NOT NULL | |
| GROUP BY cu.id_customer; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment