Migration from EXT:seminars

We migrated an instance from EXT:seminars v2.1 to EXT:sf_event_mgt v4.3

What’s included?

  • Events
  • Registrations
  • Categories
  • Locations (multiple => single)
  • Organizers (multiple => single)
  • Speaker

We also migrated event types and target groups. The sql queries are basically the same like the category queries. You will need two more category fields in tx_sfeventmgt_domain_model_event for them.

SQL queries

We are starting with a fresh install of sf_event_mgt so we can use ids mostly 1 to 1.

/* Organizers */
INSERT INTO tx_sfeventmgt_domain_model_organisator (uid,pid,name,email,email_signature)
SELECT
    uid,
    pid,
    title,
    email,
    email_footer,
FROM
    tx_seminars_organizers
WHERE
    deleted = 0;

/* Locations */
INSERT INTO tx_sfeventmgt_domain_model_location (uid,pid,title,address,city,description)
SELECT
    uid,
    pid,
    title,
    address,
    city,
    directions
FROM
    tx_seminars_sites
WHERE
    deleted = 0;

/* Speakers */
INSERT INTO tx_sfeventmgt_domain_model_speaker (uid,pid,name,description)
SELECT
    uid,
    pid,
    title,
    description
FROM
    tx_seminars_speakers
WHERE
    deleted = 0;

/* Description of speakers */
UPDATE tx_sfeventmgt_domain_model_speaker
SET
    description = (
        SELECT
            CONCAT(
                IF (organization!='', CONCAT(organization, '<br>'), ''),
                IF (homepage!='', CONCAT('<a href="', homepage,'">', homepage, '</a><br>'), ''),
                IF (email !='', CONCAT('<a href="mailto:', email, '">', email, '</a><br>'), ''),
                description
            )
        FROM
            tx_seminars_speakers
        WHERE
            tx_sfeventmgt_domain_model_speaker.uid = tx_seminars_speakers.uid
    );

/* Events */
INSERT INTO tx_sfeventmgt_domain_model_event (
    uid,
    pid,
    hidden,
    title,
    teaser,
    description,
    startdate,
    enddate,
    registration_deadline,
    cancel_deadline,
    enable_cancel,
    location,
    room,
    speaker,
    price,
    enable_registration,
    unique_email_check,
    max_participants,
    registration,
    enable_autoconfirm,
    enable_waitlist,
    notify_organisator
)
SELECT
    uid,
    pid,
    hidden,
    title,
    teaser,
    CONCAT(
        IF (
            subtitle!='',
            CONCAT(
                '<h2>',
                subtitle,
                '</h2>'
            ),
            ''
        ),
        description,
        additional_information
    ),
    begin_date,
    end_date,
    deadline_registration,
    deadline_unregistration,
    1, /* Enable cancellation */
    place,
    room,
    speakers,
    price_regular,
    needs_registration,
    IF(
        allows_multiple_registrations = 1,
        '0',
        '1'
    ),
    attendees_max,
    registrations,
    1, /* auto confirm registration */
    queue_size,
    1 /* notify organisator on new registration */
FROM tx_seminars_seminars
WHERE
    deleted = 0 AND
    (end_date = 0 OR end_date >= UNIX_TIMESTAMP()); /* filter out past events */

/* Events <=> Locations */
UPDATE tx_sfeventmgt_domain_model_event
    SET location = (
        SELECT
            uid_foreign
        FROM
            tx_seminars_seminars_place_mm
        WHERE
            tx_sfeventmgt_domain_model_event.uid = tx_seminars_seminars_place_mm.uid_local
        LIMIT 1 /* seminars has multiple locations, this extension has only a single location */
    );

/* Events <=> Speaker */
INSERT INTO tx_sfeventmgt_event_speaker_mm (uid_local,uid_foreign,sorting)
SELECT
    uid_local,
    uid_foreign,
    sorting
FROM tx_seminars_seminars_speakers_mm
WHERE
    uid_local IN (SELECT uid FROM tx_sfeventmgt_domain_model_event);

/* Events <=> Organizers */
UPDATE tx_sfeventmgt_domain_model_event
    SET organisator = (
        SELECT
            uid
        FROM tx_seminars_organizers
        LEFT JOIN tx_seminars_seminars_organizers_mm
            ON
                tx_seminars_seminars_organizers_mm.uid_foreign = tx_seminars_organizers.uid
        WHERE
            tx_seminars_seminars_organizers_mm.uid_local = tx_sfeventmgt_domain_model_event.uid AND
            tx_seminars_organizers.deleted = 0
        ORDER BY
            sorting ASC
        LIMIT 1 /* seminars has multiple organizers, this extension only a single organizer */
    );

/* Categories */
INSERT INTO sys_category (uid,pid,title,parent)
SELECT
    uid + 10000, /* bigger than your highest category uid */
    pid,
    title,
    823  /* Parent category for our event categories */
FROM
     tx_seminars_categories
WHERE
     deleted = 0;

/* Events <=> Categories */
INSERT INTO sys_category_record_mm (uid_local,uid_foreign,tablenames,fieldname)
SELECT
    uid_foreign + 10000,
    uid_local,
    'tx_sfeventmgt_domain_model_event',
    'category'
FROM tx_seminars_seminars_categories_mm
WHERE
    tx_seminars_seminars_categories_mm.uid_local IN (SELECT uid FROM tx_sfeventmgt_domain_model_event);

/* Registrations */
INSERT INTO tx_sfeventmgt_domain_model_registration (
    uid,
    pid,
    hidden,
    tstamp,
    crdate,
    fe_user,
    `event`,
    waitlist,
    notes,
    firstname,
    lastname,
    email,
    confirmed,
    amount_of_registrations
)
SELECT
    uid,
    pid, /* Your need to make sure registrations the same pid as event records (Inline relation) */
    hidden,
    tstamp,
    crdate,
    `user`,
    seminar,
    registration_queue,
    CONCAT(
        IF (attendees_names!='', CONCAT('Weitere Namen:\n',attendees_names,'\n\n'), ''),
        IF (interests!='', CONCAT('Interessen:\n',interests,'\n\n'), ''),
        IF (expectations!='', CONCAT('Erwartungen:\n',expectations,'\n\n'), ''),
        IF (background_knowledge!='', CONCAT('Vorkenntnisse:\n',background_knowledge,'\n\n'), ''),
        IF (known_from!='', CONCAT('Wie haben Sie von dieser Veranstaltung erfahren?\n',known_from,'\n\n'), ''),
        IF (notes!='', CONCAT('Sonstiges:\n',notes,'\n\n'), '')
    ),
    fe_users.first_name,
    fe_users.last_name,
    fe_users.email,
    1,
    seats
FROM tx_seminars_attendances
LEFT JOIN fe_users
    ON fe_users.uid=tx_seminars_attendances.user
LEFT JOIN tx_seminars_seminars ON
    tx_seminars_seminars.uid = tx_seminars_attendances.seminar
WHERE
    tx_seminars_attendances.deleted = 0 AND
    tx_seminars_seminars.deleted = 0 AND
    tx_seminars_attendances.seminar IN (
        SELECT
            uid
        FROM
            tx_sfeventmgt_domain_model_event
    );