get Customers from Shop2Date for Magento

shop2date_customers2magento.sql

SELECT
    email,
    "base" as _website,
    CASE country
        WHEN "DE,DEU,276:Deutschland" THEN "eu"
        WHEN "AT,AUT,040:Österreich" THEN "eu"
        WHEN "CH,CHE,756:Schweiz" THEN "ch"
        WHEN "BE,BEL,056:Belgien" THEN "eu"
        WHEN "CY,CYP,196:Zypern" THEN "eu"
        WHEN "CZ,CZE,203:Tschechien" THEN "eu"
        WHEN "DK,DNK,208:Dänemark" THEN "eu"
        WHEN "EE,EST,233:Estland" THEN "eu"
        WHEN "ES,ESP,724:Spanien" THEN "eu"
        WHEN "FR,FRA,250:Frankreich" THEN "eu"
        WHEN "HU,HUN,348:Ungarn" THEN "eu"
        WHEN "IT,ITA,380:Italien" THEN "eu"
        WHEN "LU,LUX,442:Luxemburg" THEN "eu"
        WHEN "NL,NLD,528:Niederlande" THEN "eu"
        WHEN "PL,POL,616:Polen" THEN "eu"
        WHEN "PT,PRT,620:Portugal" THEN "eu"
        WHEN "SE,SWE,752:Schweden" THEN "eu"
        WHEN "SI,SVN,705:Slowenien" THEN "eu"
        WHEN "SK,SVK,703:Slowakei" THEN "eu"
        WHEN "GB,GBR,826:Großbritannien" THEN "eu"
    END as _store,
    "" as confirmation,
    NOW() as created_at,
    CASE country
        WHEN "DE,DEU,276:Deutschland" THEN "EU"
        WHEN "AT,AUT,040:Österreich" THEN "EU"
        WHEN "CH,CHE,756:Schweiz" THEN "CH"
        WHEN "BE,BEL,056:Belgien" THEN "EU"
        WHEN "CY,CYP,196:Zypern" THEN "EU"
        WHEN "CZ,CZE,203:Tschechien" THEN "EU"
        WHEN "DK,DNK,208:Dänemark" THEN "EU"
        WHEN "EE,EST,233:Estland" THEN "EU"
        WHEN "ES,ESP,724:Spanien" THEN "EU"
        WHEN "FR,FRA,250:Frankreich" THEN "EU"
        WHEN "HU,HUN,348:Ungarn" THEN "EU"
        WHEN "IT,ITA,380:Italien" THEN "EU"
        WHEN "LU,LUX,442:Luxemburg" THEN "EU"
        WHEN "NL,NLD,528:Niederlande" THEN "EU"
        WHEN "PL,POL,616:Polen" THEN "EU"
        WHEN "PT,PRT,620:Portugal" THEN "EU"
        WHEN "SE,SWE,752:Schweden" THEN "EU"
        WHEN "SI,SVN,705:Slowenien" THEN "EU"
        WHEN "SK,SVK,703:Slowakei" THEN "EU"
        WHEN "GB,GBR,826:Großbritannien" THEN "EU"
    END as created_in,
    "" as dob,
    "" as failed_logins,
    firstname as firstname,
    "" as gender,
    "1" as group_id,
    lastname as lastname,
    "" as last_failed_login,
    "" as last_unlocktime,
    "" as middlename,
    password as password_hash,
    salutation as prefix,
    "" as rp_token,
    "" as rp_token_created_at,
    CASE country
        WHEN "DE,DEU,276:Deutschland" THEN "12"
        WHEN "AT,AUT,040:Österreich" THEN "12"
        WHEN "CH,CHE,756:Schweiz" THEN "9"
        WHEN "BE,BEL,056:Belgien" THEN "12"
        WHEN "CY,CYP,196:Zypern" THEN "12"
        WHEN "CZ,CZE,203:Tschechien" THEN "12"
        WHEN "DK,DNK,208:Dänemark" THEN "12"
        WHEN "EE,EST,233:Estland" THEN "12"
        WHEN "ES,ESP,724:Spanien" THEN "12"
        WHEN "FR,FRA,250:Frankreich" THEN "12"
        WHEN "HU,HUN,348:Ungarn" THEN "12"
        WHEN "IT,ITA,380:Italien" THEN "12"
        WHEN "LU,LUX,442:Luxemburg" THEN "12"
        WHEN "NL,NLD,528:Niederlande" THEN "12"
        WHEN "PL,POL,616:Polen" THEN "12"
        WHEN "PT,PRT,620:Portugal" THEN "12"
        WHEN "SE,SWE,752:Schweden" THEN "12"
        WHEN "SI,SVN,705:Slowenien" THEN "12"
        WHEN "SK,SVK,703:Slowakei" THEN "12"
        WHEN "GB,GBR,826:Großbritannien" THEN "12"
    END as store_id,
    "4" as website_id,
    salutation as _address_prefix,
    firstname as _address_firstname,
    "" as _address_middlename,
    lastname as _address_lastname,
    "" as _address_suffix,
    IFNULL(company,"0") as _address_company,
    concat(street, " ", streetnumber) as _address_street,
    city as _address_city,
    CASE country
        WHEN "DE,DEU,276:Deutschland" THEN "DE"
        WHEN "AT,AUT,040:Österreich" THEN "AT"
        WHEN "CH,CHE,756:Schweiz" THEN "CH"
        WHEN "BE,BEL,056:Belgien" THEN "BE"
        WHEN "CY,CYP,196:Zypern" THEN "CY"
        WHEN "CZ,CZE,203:Tschechien" THEN "CZ"
        WHEN "DK,DNK,208:Dänemark" THEN "DK"
        WHEN "EE,EST,233:Estland" THEN "EE"
        WHEN "ES,ESP,724:Spanien" THEN "ES"
        WHEN "FR,FRA,250:Frankreich" THEN "FR"
        WHEN "HU,HUN,348:Ungarn" THEN "HU"
        WHEN "IT,ITA,380:Italien" THEN "IT"
        WHEN "LU,LUX,442:Luxemburg" THEN "LU"
        WHEN "NL,NLD,528:Niederlande" THEN "NL"
        WHEN "PL,POL,616:Polen" THEN "PL"
        WHEN "PT,PRT,620:Portugal" THEN "PT"
        WHEN "SE,SWE,752:Schweden" THEN "SE"
        WHEN "SI,SVN,705:Slowenien" THEN "SI"
        WHEN "SK,SVK,703:Slowakei" THEN "SK"
        WHEN "GB,GBR,826:Großbritannien" THEN "GB"
    END as _address_country_id,
    "Not selected." as _address_region,
    zip as _address_postcode,
    IFNULL(IFNULL(phone,cellphone),"0") as _address_telephone,
    IFNULL(fax,"") as _address_fax,
    "" as _address_payone_credit_rating_score,
    "" as _address_payone_credit_rating_date,
    "" as _address_payone_credit_rating_secscore,
    "1" as _address_default_billing_,
    "1" as _address_default_shipping_

FROM w2d6_main_clients
WHERE
    password IS NOT NULL
AND
    country IS NOT NULL
AND
    firstname IS NOT NULL
AND
    lastname IS NOT NULL
AND
    email IS NOT NULL
GROUP BY  email
ORDER BY order_id DESC, _address_region ASC;