Ankündigung

Einklappen
Keine Ankündigung bisher.

Migration von Magento zu Zen Cart

Einklappen
X
 
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

    Migration von Magento zu Zen Cart

    Hallo,
    ich habe kürzlich Produkte, Kategorien, Kunden und Kundendaten von einem Magento-Shop zu Zen Cart 1.5.5 gehoben. Wenn jemand etwas ähnliches vorhaben sollte, könnten die SQL Schnipsel vielleicht hilfreich sein.
    Code:
    # Erstelle in der Magento DB eine Tabelle 'AA_ZC_countries' und importiere die Daten aus der Zen Cart Datenbank Tabelle 'countries'
    # Create Views in der Magento Datenbank. ACHTUNG!!! Die ids musst Du vorher prüfen, ich kann nicht sagen ob die bei jeder Magento-Installation gleich sind; die meissten findest Du
    # in der Tabelle 'eav_attribute'. Wenn Du weitere Produkt-Daten wie z.B. Bilder übernehmen willst, musst Du die Abfrage erweitern.
    # OK? Dann los!
    
    # Products
    CREATE VIEW `products` AS
    SELECT DISTINCT `cpf1`.`entity_id` AS `products_id`,
    `cpf1`.`price` AS `products_price`,
    `cpf1`.`sku` AS `products_model`,
    `cpf1`.`created_at` AS `products_date_added`,
    `cpf1`.`updated_at` AS `products_last_modified`,
    `cpf1`.`weight` AS `products_weight`,
    `cpei`.`value` AS `manufacturers_id`,
    `ccp`.`category_id` AS `master_categories_id`,
    `cpev`.`value` AS `products_ean`
     FROM `catalog_product_flat_1` `cpf1`
     LEFT JOIN `catalog_category_product` `ccp` ON `cpf1`.`entity_id`=`ccp`.`product_id`
     LEFT JOIN `catalog_product_entity_int` `cpei` ON `cpf1`.`entity_id`=`cpei`.`entity_id` AND `cpei`.`attribute_id`='81'
     LEFT JOIN `catalog_product_entity_varchar` `cpev` ON `cpf1`.`entity_id`=`cpev`.`entity_id` AND `cpev`.`attribute_id`='136';
    
    #Products_description
    CREATE VIEW `products_description` AS
    SELECT `cpet`.`entity_id` AS `products_id`,`cpet`.`value` AS `products_description`, `cpev`.`value` AS `products_name` FROM `catalog_product_entity_text` `cpet` LEFT JOIN `catalog_product_entity_varchar` `cpev` ON `cpev`.`entity_id`=`cpet`.`entity_id` AND `cpev`.`store_id`='0' AND `cpev`.`attribute_id`='82' WHERE `cpet`.`attribute_id`='72' AND `cpet`.`store_id`='0';
    
    # products_to_categories
    CREATE VIEW `products_to_categories` AS
    SELECT DISTINCT `category_id` AS `categories_id`, `product_id` AS `products_id` FROM `catalog_category_product`;
    
    # manufacturers
    CREATE VIEW `manufacturers` AS
    SELECT `option_id` AS `manufacturers_id`, `value` AS `manufacturers_name` FROM `eav_attribute_option_value` WHERE `store_id`='0' AND `value_id`>'1000';
    
    # categories
    CREATE VIEW `categories` AS
    SELECT `entity_id` AS `categories_id`, `parent_id`, `created_at` AS `date_added`, `updated_at` AS `last_modified`, `position` AS `sort_order` FROM `catalog_category_flat_store_1` WHERE `meta_keywords`!='';
    
    # categories_description
    CREATE VIEW `categories_description` AS
    SELECT `ccfs1`.`entity_id` AS `categories_id`,`ccfs1`.`name` AS `categories_name`, `ccet`.`value` AS `categories_description` FROM `catalog_category_flat_store_1` `ccfs1` LEFT JOIN `catalog_category_entity_text` `ccet` ON `ccfs1`.`entity_id`=`ccet`.`entity_id` AND `ccet`.`attribute_id`='44' AND `ccet`.`store_id`='0' WHERE `ccfs1`.`meta_keywords`!='';
    
    # meta_tags_categories_description
    CREATE VIEW `meta_tags_categories_description` AS
    SELECT DISTINCT `ccet`.`entity_id` AS `categories_id`,
    (SELECT `ccet1`.`value`FROM `catalog_category_entity_text` `ccet1` WHERE `ccet1`.`attribute_id`='47' AND `ccet1`.`entity_id`=`ccet`.`entity_id` AND `ccet1`.`value`!='' AND `ccet1`.`store_id`='1') AS `metatags_keywords`,
    (SELECT `ccet2`.`value`FROM `catalog_category_entity_text` `ccet2` WHERE `ccet2`.`attribute_id`='48' AND `ccet2`.`entity_id`=`ccet`.`entity_id` AND `ccet2`.`value`!='' AND `ccet2`.`store_id`='1') AS `metatags_description`,
    IF(`ccev`.`attribute_id`='41' , `ccev`.`value`, '') AS `metatags_title`
    FROM `catalog_category_entity_text` `ccet`
    LEFT JOIN `catalog_category_entity_varchar` `ccev` ON `ccev`.`entity_id`=`ccet`.`entity_id` AND `ccev`.`store_id`='1' AND `ccev`.`value`!='' AND `ccev`.`attribute_id`='41' WHERE `ccet`.`store_id`='1' AND `ccet`.`value`!='' AND (`ccet`.`attribute_id`='47' OR `ccet`.`attribute_id`='48');
    
    # meta_tags_products_description
    CREATE VIEW `meta_tags_products_description` AS
    SELECT DISTINCT `ccet`.`entity_id` AS `products_id`,
    (SELECT `ccet1`.`value`FROM `catalog_product_entity_text` `ccet1` WHERE `ccet1`.`attribute_id`='83' AND `ccet1`.`entity_id`=`ccet`.`entity_id` AND `ccet1`.`value`!='' AND `ccet1`.`store_id`='0') AS `metatags_keywords`,
    (SELECT `ccet2`.`value`FROM `catalog_product_entity_text` `ccet2` WHERE `ccet2`.`attribute_id`='73' AND `ccet2`.`entity_id`=`ccet`.`entity_id` AND `ccet2`.`value`!='' AND `ccet2`.`store_id`='0') AS `metatags_description`,
    IF(`ccev`.`attribute_id`='71' , `ccev`.`value`, '') AS `metatags_title`
    FROM `catalog_product_entity_text` `ccet`
    LEFT JOIN `catalog_product_entity_varchar` `ccev` ON `ccev`.`entity_id`=`ccet`.`entity_id` AND `ccev`.`store_id`='0' AND `ccev`.`value`!='' AND `ccev`.`attribute_id`='71' WHERE `ccet`.`store_id`='0' AND `ccet`.`value`!='' AND (`ccet`.`attribute_id`='83' OR `ccet`.`attribute_id`='73');
    
    # customers
    CREATE VIEW `customers` AS
    SELECT `c`.`entity_id` AS `customers_id`, `c`.`email` AS `customers_email_address`,
    (SELECT `fn`.`value` FROM `customer_entity_varchar` `fn` WHERE `c`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '5') AS `customers_firstname`,
    (SELECT `fn`.`value` FROM `customer_entity_varchar` `fn` WHERE `c`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '7') AS `customers_lastname`,
    (SELECT `fn`.`value` FROM `customer_address_entity_varchar` `fn` WHERE `ca`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '31') AS `customers_telephone`,
    (SELECT `fn`.`value` FROM `customer_address_entity_varchar` `fn` WHERE `ca`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '32') AS `customers_fax`
    FROM `customer_entity` AS `c` LEFT JOIN `customer_address_entity` AS `ca` ON `c`.`entity_id` = `ca`.`parent_id` LEFT JOIN `customer_address_entity_text` AS `cat` ON `cat`.`entity_id` = `ca`.`entity_id` GROUP BY `c`.`entity_id`;
    
    # address_book
    CREATE VIEW `address_book` AS
    SELECT `c`.`entity_id` AS `customers_id`,
    (SELECT `fn`.`value` FROM `customer_entity_varchar` `fn` WHERE `c`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '24') AS `entry_company`,
    (SELECT `fn`.`value` FROM `customer_entity_varchar` `fn` WHERE `c`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '5') AS `entry_firstname`,
    (SELECT `fn`.`value` FROM `customer_entity_varchar` `fn` WHERE `c`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '7') AS `entry_lastname`,
    (SELECT `fn`.`value` FROM `customer_address_entity_text` `fn` WHERE `ca`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '25') AS `entry_street_address`,
    (SELECT `fn`.`value` FROM `customer_address_entity_varchar` `fn` WHERE `ca`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '30') AS `entry_postcode`,
    (SELECT `fn`.`value` FROM `customer_address_entity_varchar` `fn` WHERE `ca`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '26') AS `entry_city`,
    (SELECT `fn`.`value` FROM `customer_address_entity_varchar` `fn` WHERE `ca`.`entity_id` = `fn.entity_id` AND `fn`.`attribute_id` = '28') AS `entry_state`,
    (SELECT `countries_id` FROM `AA_ZC_countries` WHERE `countries_iso_code_2`=(SELECT `fn`.`value` FROM `customer_address_entity_varchar` `fn` WHERE `ca`.`entity_id` = `fn`.`entity_id` AND `fn`.`attribute_id` = '27')) AS `entry_country_id`
    FROM `customer_entity` AS `c` LEFT JOIN `customer_address_entity` AS `ca` ON `c`.`entity_id` = `ca`.`parent_id` LEFT JOIN `customer_address_entity_text` AS `cat` ON `cat`.`entity_id` = `ca`.`entity_id` GROUP BY `c`.`entity_id`;
    
    # customers_info
    CREATE VIEW `customers_info` AS
    SELECT `entity_id` AS `customers_info_id`, `created_at` AS `customers_info_date_of_last_logon`, '1' AS `customers_info_number_of_logons`, `created_at` AS `customers_info_date_account_created`, `updated_at` AS `customers_info_date_account_last_modified`, '0' AS `global_product_notifications` FROM `customer_entity`;
    
    # Jetzt kannst Du die Views als Tabellen im PhpMyAdmin exportieren (einzeln, alle zusammen ging, jedenfalls bei mir, nicht)
    # und in die Zen Cart Datenbank importieren (auch mit PhpMyAdmin). Wenn die Daten schon einmal importiert wurden und sich geändert haben, benutze 'REPLACE'
    #Jetzt noch 'customers' mit 'address_book' verknüppern
    
    UPDATE `customers` SET `customers_default_address_id`= (SELECT `address_book_id` FROM `address_book` WHERE `customers`.`customers_id`=`address_book`.`customers_id`);
    
    # und Einträge ohne Adresse löschen.
    DELETE FROM `address_book` WHERE `entry_street_address`='';
    DELETE FROM `customers_info` WHERE `customers_info_id` NOT IN (SELECT `customers_id` FROM `address_book`);
    DELETE FROM `customers` WHERE `customers_id` NOT IN (SELECT `customers_id` FROM `address_book`);
    # Fertig!
    ... vielleicht hilft's
    Gruß Knut
Info zu diesem Forenarchiv:
Mit Release von 1.5.7 wurde die deutsche Zen Cart Version auf eine reine DIY-Lösung umgestellt.
Für einen Support via Forum stehen keine personellen und zeitlichen Ressourcen mehr zur Verfügung.
Dieses Supportforum bleibt im Nur-Lesen-Modus als Wissensarchiv noch online verfügbar.
PM Funktionalität, Registrierung und Posten neuer Beiträge sind deaktiviert.
Zugriff auf Anhänge in den Postings ist auch ohne Registrierung/Einloggen möglich.
FAQ und Downloadbereich des Forums wurden in die neue umfangreiche Knowledgebase auf der zen-cart-pro.at Website übernommen.

Das Development der deutschen Zen Cart Version geht wie bisher auf Github weiter.
Wir werden auch weiterhin neue Versionen bereitstellen und die Onlinedokumentation/Knowledgebase aktualisieren.
Fehler in der Software können auf Github als Issues gemeldet werden.
Follow us
aktuelle version
Zen Cart 1.5.7g deutsch
vom 12.12.2023
[Download]
Lädt...
X