Import address book to RoundCube from Group Office using SQL

import_contacts_from_groupoffice_to_roundcube.sql

-- ALTER TABLE roundcube.contacts ADD UNIQUE INDEX `uniq_email_user_id` (`email` (128), `user_id`);
SET @CrLf='\r\n';
SET sql_mode='PIPES_AS_CONCAT';

INSERT IGNORE roundcube.contacts
SELECT
    NULL contact_id,
    changed,
    0 del,
    IF(firstname,firstname || ' ','') || surname name,
    email,
    firstname,
    surname,

    'BEGIN:VCARD' || @CrLf ||
    'VERSION:3.0' || @CrLf ||
    'FN:' || firstName || @CrLf ||
    'N:' ||
        COALESCE(surname,'') || ';' ||
        COALESCE(firstName,'') || ';' ||
        COALESCE(title,'')  || @CrLf ||
    'TEL;TYPE=work:' || COALESCE(work_phone,'')  || @CrLf ||
    'TEL;TYPE=home:' || COALESCE(home_phone,'')  || @CrLf ||
    'TEL;TYPE=home,cell:' || COALESCE(cellular,'')  || @CrLf ||
    'ADR;TYPE=home:;;' ||
        COALESCE(address, IF(address_no<>'',COALESCE(' ',address_no),'') ,'') || ';' ||
        COALESCE(city,'') || ';' ||
        COALESCE('') || ';' ||
        COALESCE(zip,'') || ';;'  || @CrLf ||
    'EMAIL;TYPE=internet,home,pref:' || COALESCE(email,'')  || @CrLf ||
    'NOTE:' || COALESCE(comment,'')  || @CrLf ||
    'END:VCARD'  || @CrLf vcard,

    IF(firstname,firstname || ' ','') || IF(surname,surname || ' ','') || email words,
    user_id
FROM (
    SELECT from_unixtime(ctime)               changed,
         IF(email=first_name,'',first_name) firstname,
         last_name                          surname,
         email,
         u.user_id user_id,
         title,
         home_phone,
         cellular,
         work_phone,
         country,
         state,
         city,
         zip,
         address,
         address_no,
         comment,
         salutation,
         department,
         sex
    FROM   groupofficecom.ab_addressbooks ab
    JOIN   groupofficecom.ab_contacts ac
    ON     ab.id = ac.addressbook_id
    JOIN   roundcube.users u
    WHERE  ab.name = concat(u.username,' ',u.username)
) dat;