Migration GoldShop to PrestaShop

MigrationGoldShopToPrestaShop.txt

MIGRACJA UŻYTKOWNIKÓW krok po kroku Z GOLDSHOP NA PRESTASHOP

=====================================
# Zgranie użytkownków tych co nie ma jeszcze
# Wynik Teraz trzeba pobrać cvs tego zapytania i wgrać 
# Pojawi się tabela ~TABLE 296 do następnie poustawiać nazwy column
# COL1, COL2....
# Na takie jakie istnieją w bazie np col1 na np email
#
# Po zmianie kolum i pobraniu plik będzie miał
# INSERT INTO `TABLE 296`
#
# zmien nazwę na ps_customer i wgraj ją gdyby nigdy nic
=====================================

    SELECT 
        customers_firstname as firstname, 
        customers_lastname as lastname, 
        customers_email_address as email,
        NOW() as date_add
    FROM pp_customers_tmp_goldshop
    LEFT JOIN ps_customer ON (ps_customer.email = pp_customers_tmp_goldshop.customers_email_address) 
    WHERE ps_customer.email IS NULL ORDER BY `ps_customer`.`email` ASC


=====================================
# Aktualizacja haseł wszystkich oraz source_key
=====================================


    UPDATE ps_customer SET passwd=MD5(concat(
        substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@lid)*4294967296))*36+1, 1),
        substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
        substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
        substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
        substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
        substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
        substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
        substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
        substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
        substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
        substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
        substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
        substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
        substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed)*36+1, 1)
    )),   
    secure_key=MD5(concat(
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@lid)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed)*36+1, 1)
    )), 
    active=1



===============================
# Transfer punktów
#
# Pobieramy tabele ze starej bazy _points wgrywamy na nowy serwer zmieniamy nazwę bez spacji w table
# Następnie znów pobrać cvs wgrać  zmien nazwę na ps_j2trewardaccount i wgraj ją gdyby nigdy nic
===============================

Pobieramy punkty



    SELECT 
        `customers`.`customers_email_address`,
        `customers_points`.`customers_id`, 
        sum(`customers_points`.`points`) AS plus, 
       `customers_points`.`date_added`
    FROM  `customers_points` 
    LEFT JOIN customers ON `customers`.`customers_id` = `customers_points`.`customers_id`
    WHERE 
        `customers_points`.`points_status` = '2'
    GROUP BY `customers_points`.`customers_id` 




    SELECT 
        `customers_points`.`customers_id`, 
        sum(`customers_points`.`points`)*-1 AS minus
    FROM  `customers_points` 
    LEFT JOIN customers ON `customers`.`customers_id` = `customers_points`.`customers_id`
    WHERE 
        `customers_points`.`points_status` = '4' 
    GROUP BY `customers_points`.`customers_id` 


=====
Wgrywamy plus i minus tabele i robimy SELECT sumującego (Pamiętać o zmianie spacji w nazwie tabeli)
Oraz pamietac by odpowiednio zmienic COL1, COL2 na nazwy tabel


    SELECT 
        TABLE301.customers_email_address,
        TABLE301.customers_id,
        TABLE301.date_added,
        (TABLE301.plus+IFNULL(TABLE302.minus,0)) as points 
    FROM TABLE301
    LEFT JOIN TABLE302 ON TABLE301.customers_id = TABLE302.customers_id 
    GROUP BY `TABLE301`.`customers_id` 



Łaczymy już w nowej bazie 


===== 
Dla walidacji 

    SELECT
        ps_customer.id_customer AS customer_id, 
        TABLE297.points AS points_current,
        TABLE297.date_add AS date_insertion,
        "1" AS store_id,
        "-1" AS order_id, "0" AS points_spent, 
        "0000-00-00" AS date_start,
        "0000-00-00" AS date_end,
        "4" AS order_state
    FROM `TABLE297`
    LEFT JOIN ps_customer ON ps_customer.email = `TABLE297`.`email`

====
Dla łaczenia 

    SELECT 
        (TABLE301.plus+IFNULL(TABLE302.minus,0)) as points_current,
        ps_customer.id_customer AS customer_id, 
        TABLE301.date_added AS date_insertion,
        "1" AS store_id,
        "-1" AS order_id, "0" AS points_spent, 
        "0000-00-00" AS date_start,
        "0000-00-00" AS date_end,
        "4" AS order_state
    FROM TABLE301
    LEFT JOIN TABLE302 ON TABLE301.customers_id = TABLE302.customers_id 
    LEFT JOIN ps_customer ON ps_customer.email =  TABLE301.customers_email_address
    GROUP BY `TABLE301`.`customers_id` 



Pobieramy csv wgrywamy do bazy zmieniamy nazwy tabel i pobieramy znów sql wgrywając ostatecznie do ps_j2trewardaccount

W razie czego użyć http://www.convertcsv.com/csv-to-sql.htm
=======
Domyślne grupy
Gdy będzie problem z grupami to tu jest komenda na ustawienie domyślnej grupy użytkownikom

INSERT INTO ps_customer_group (id_customer, id_group)
    SELECT 
        t1.id_customer,
        '3'
    FROM ps_customer  t1
    LEFT JOIN ps_customer_group t2 ON t2.id_customer = t1.id_customer
    WHERE t2.id_group IS NULL
    
====

Notki: Gdy będzie problem z bazą niewłaściwy pierwszy wiersz wrzycić to wtedy to jakiegoś onlinowego generatora cvs to sql