Import: Event Participant

Related reading

1. Overview

The Event Participant import registers (or updates) participants in a named event from an XLSX file. Rows are matched to existing EventParticipant records via RegistrationID within the event scope, so the same file can be re-run with corrections without creating duplicates. Each row produces one of five outcomes (CREATED / UPDATED / SKIPPED / WARNING / ERROR), and the aggregated response is returned as EventParticipantImportResultDTO.

The HTTP surface is asynchronous from release 2.4: the upload returns HTTP 202 immediately and the caller polls a companion GET endpoint for the result DTO.

2. API

2.1. Endpoint Reference

Endpoint Purpose

PUT /api/event-participants/import

Upload an XLSX and start the async import. Returns HTTP 202 Accepted with an ImportJobDTO — the job is queued on the generic async framework.

GET /api/event-participants/import/{jobId}

Retrieve the EventParticipantImportResultDTO once the job reaches COMPLETED or FAILED. Returns HTTP 409 while still processing; HTTP 404 for unknown ids or wrong importType.

2.2. Async Delivery Mode

The per-entity endpoint is a thin facade over the generic async framework. Behind the scenes EventParticipantRowProcessor runs in whole-file mode, delegating to EventParticipantImportXLS.process(…​) — the same bulk importer that produced the response DTO inline in the pre-async implementation. The resulting EventParticipantImportResultDTO is JSON-serialised to ImportJob.resultPayloadJson and echoed back verbatim by the GET endpoint. Response shape is byte-identical to what the prior synchronous PUT returned inline — only the delivery mode changed.

PUT /api/event-participants/import
  ?eventId={long}                                    (required)
  &orgId={long}                                      (optional — defaults to caller's tenant)
  &sheetIndex={int}                                  (default: 0)
  &createCustom1={true|false}                        (default: true)
  &createCustom2={true|false}                        (default: true)
  &createCustom3={true|false}                        (default: true)
  Content-Type: multipart/form-data
  Body: file=<xlsx>

HTTP/1.1 202 Accepted
Location: /api/event-participants/import/{jobId}
{ "identifier": "<jobId>", "status": "PROCESSING", "skipMappingPhases": true, ... }

# operator polls ...

GET /api/event-participants/import/{jobId}

HTTP/1.1 200 OK                     # once COMPLETED or FAILED
{ ... EventParticipantImportResultDTO ... }
HTTP/1.1 409 Conflict               # while still processing

See Import Operations Runbook for the polling procedure.

2.3. Request Body

Multipart form with a single file part containing the XLSX workbook. One sheet per upload; sheetIndex selects which sheet (0-based, default 0).

Query parameters:

  • eventId (required) — target event; scopes the registrationId upsert

  • orgId (optional) — override the caller’s tenant; normally inferred from the event

  • sheetIndex (default 0) — 0-based sheet index within the workbook

  • createCustom1/2/3 (default true) — if true, a missing value in CUSTOM_LIST_1/2/3 is auto-created on the corresponding CustomList; if false, missing values produce a WARNING without creating the custom value

2.4. Response: EventParticipantImportResultDTO

{
  "totalRows": 185,
  "created": 20,
  "updated": 160,
  "skipped": 0,
  "warnings": 4,
  "errors": 1,
  "issues": [
    { "outcome": "WARNING", "firstName": "Jane", "lastName": "Doe",
      "registrationId": "29364", "message": "Ambiguous person match" },
    { "outcome": "ERROR",   "firstName": "Xyz", "lastName": "Abc",
      "message": "CustomList value not found and createCustom1=false" }
  ]
}

Invariant: totalRows == created + updated + skipped + warnings + errors.

The issues list contains only rows with SKIPPED / WARNING / ERROR. Successful CREATED / UPDATED rows are counted but not individually listed, keeping the payload focused on what the operator must action.

2.5. What to Look At

After polling returns the DTO, these are the patterns to confirm the import landed the way you expected:

  • Clean first run against a fresh event: created ≈ totalRows, updated = 0, issues = []

  • Re-run of the same file: created = 0, updated ≈ totalRows, issues = []

  • Correcting a partial first run: some updated + some created is fine; inspect issues for the remainder

The Import Operations Runbook covers the recovery procedures when the numbers don’t match expectations.

3. Column Definitions

The import supports 32 columns, with 2 required fields.

3.1. Required Fields

Column Type Aliases

FIRST_NAME

STRING

FirstName, Name, FN

LAST_NAME

STRING

LastName, Surname, SN, LN

3.2. Person Fields

Column Type Aliases

TITLE

STRING

Title

ID_NUMBER

STRING

ID, IDNumber, IdentityNumber, Passport, IDPassport

ID_TYPE

STRING

IDType (default: NATIONAL)

ID_COUNTRY

STRING (FK)

Country, IDCountry, Nationality (default: ZA)

GENDER

STRING

Gender, Gen, Sex

DOB

DATE

DOB, DateOfBirth, Birthday

CONTACT_NUMBER

STRING

ContactNumber, Cellphone, CellphoneNumber, CellNumber, Telephone, TelephoneNumber, Mobile

EMAIL

STRING

Email, EmailAddress

USER_KEY

LONG

UserKey

MAIN_MEMBER_ID

STRING

MainMember, MainMemberID

PERSON_EXTERNAL_ID

LONG

UID, PID

3.3. Address Fields

Column Type Aliases

ADDRESS

STRING

Address, Address1

TOWN

STRING

Town, City, TownCity, CityTown

POSTAL_CODE

STRING

PostalCode, Code, ZIP

3.4. Event Participation Fields

Column Type Aliases

EVENT_PARTICIPANT_ID

LONG

EPID, EventParticipantID

EVENT_PARTICIPANT_EXTERNAL_ID

STRING

EPREF (also RegistrationID, ExternalID, ExternalEPID — see Upsert by registrationId)

EVENT_CATEGORY_NAME

STRING (FK)

Category, CategoryName, EventCategory, EventCategoryName

OTHER_NUMBER

STRING

OtherNumber

3.5. Race Number Fields

Column Type Aliases

NUMBER

STRING

Number, Bib, BibNumber, RaceNumber, No

NUMBER_ID

INTEGER

NumberId

TAG_NUMBER

STRING

Tag, TagRef, TagReference, Chip

TAG_ID

INTEGER

TagId

3.6. Order Fields

Column Type Aliases

ORDER_ID

INTEGER

OrderID

ORDER_NUMBER

STRING

OrderNumber, Order#, ReceiptNumber

ORDER_STATUS

STRING

OrderStatus (default: P for Pending)

ORDER_PRODUCT_ID

INTEGER

Product, ProductID, OrderProduct, OrderProductID

ORDER_PRODUCT_AMOUNT

DECIMAL

ProductAmount, OrderProductAmount, Amount

3.7. Custom Fields

Column Type Aliases

CUSTOM_1

STRING

(none)

CUSTOM_2

STRING

(none)

CUSTOM_3

STRING

(none)

CUSTOM_LIST_1

STRING (FK)

Schoolyouareattending (legacy alias; see Outstanding #3)

CUSTOM_LIST_2

STRING (FK)

(none)

CUSTOM_LIST_3

STRING (FK)

(none)

4. Foreign Key Resolution

Fields marked (FK) require resolution to entity IDs:

Field Target Entity Resolution Strategy

ID_COUNTRY

Country

Lookup by code (ZA, US) or name

EVENT_CATEGORY_NAME

EventCategory

Lookup by name within event

CUSTOM_LIST_1/2/3

CustomListValue

Lookup by value within list; auto-create if createCustom{1,2,3}=true

5. Processing Flow

5.1. Row-Level Flow

1. Receive InputStream + orgId + eventId + sheetIndex
2. Load workbook into memory (XSSFWorkbook)
3. Lookup Organisation (tenantService.getOrganisation)
4. Lookup Event (eventService.findOneEntity)
5. For each row:
   a. Skip row 0 (header row, detect columns)
   b. Check if blank (first 5 cells empty)
   c. Create EventParticipantAddRequestDTO
   d. Call eventParticipantService.register()
   e. Collect response
6. Return list of responses, aggregated into EventParticipantImportResultDTO

5.2. Row Extraction

The createRequestDTO method maps spreadsheet values to DTO fields:

EventParticipantAddRequestDTO request = new EventParticipantAddRequestDTO();
request.setId(parseAsLong(row, map, Column.EVENT_PARTICIPANT_ID));
request.setExternalId(parseAsString(row, map, Column.EVENT_PARTICIPANT_EXTERNAL_ID));

PersonDTO person = new PersonDTO();
person.setFirstName(parseAsString(row, map, Column.FIRST_NAME));
person.setLastName(parseAsString(row, map, Column.LAST_NAME));
// ... more fields

5.3. Default Values

defaultValues.put(Column.ID_TYPE, "NATIONAL");
defaultValues.put(Column.ID_COUNTRY, Country.DEFAULT_COUNTRY_CODE);
defaultValues.put(Column.ORDER_STATUS, "P");

5.4. Upsert by registrationId

A RegistrationID column (aliases: ExternalID, ExternalEPID) is the natural upsert key for EventParticipant within the target event. On each row:

  1. Resolve existing EP via eventParticipantRepository.findByEventIdAndRegistrationId(eventId, registrationId)

  2. If found: set wasExisting = true on the result DTO; update the existing EP’s fields (outcome = UPDATED)

  3. If not found: create new EP; set wasExisting = false (outcome = CREATED)

The upsert is scoped to eventId, so the same registrationId can appear in multiple events without collision. This is the primary enabler for cross-system migration (WPCA → EMS), where legacy system PKs are preserved in EventParticipant.registrationId.

A row without a registrationId always takes the CREATED path, relying on Person matching (identity number / name+DOB) to avoid duplicate Person records.

6. Outcome Classification

Every row produces exactly one outcome via the ImportAddResponseDTO.Outcome enum:

Outcome Meaning

CREATED

New EventParticipant (and possibly new Person) created.

UPDATED

Existing EventParticipant matched via registrationId and updated in place.

SKIPPED

Row not processed — missing required field, duplicate within the same file, or other pre-validation failure.

WARNING

EP was created/updated, but a non-fatal issue requires operator review (ambiguous Person match, custom-list value not auto-created, race-number conflict).

ERROR

Exception occurred processing the row; the row was not committed. Subsequent rows continue.

EventParticipantServiceEx.register() classifies the outcome. The inner try-catch sets ERROR outcome with the exception message rather than swallowing.

7. Source Code

File Purpose

EventParticipantImportXLS.java

Main import service: column definitions, row extraction, per-row orchestration.

EventParticipantRowProcessor.java

Whole-file bridge — parses ImportJob.configJson (eventId, sheetIndex, createCustom flags), delegates to EventParticipantImportXLS.process(…​), serialises the resulting DTO to ImportJob.resultPayloadJson. See Async Import Architecture — Whole-File Bridge.

EventParticipantServiceEx.java

Registration logic invoked per row; classifies outcome.

EventParticipantResourceEx.java

REST endpoints PUT /import (async upload) and GET /import/{jobId} (result retrieval). Thin facade over ImportJobService.createWholeFileImportJob.

EventParticipantAddRequestDTO.java

Request payload for registration.

EventParticipantAddResponseDTO.java

Response with outcome per row (aggregated into EventParticipantImportResultDTO).

Location: admin-service/src/main/java/za/co/idealogic/event/admin/

8. Outstanding Items

The items below pre-date Feature #442; some were partially addressed during the async cutover. Tracked informally — open an ADO ticket before acting on any of them.

# Topic Status

1

Refactor identity-type parsing (duplicated in MembershipImportXLS) into PersonService.parseIdentityType(String)

Open

2

Refactor gender parsing (duplicated in MembershipImportXLS) into PersonService.parseGender(String)

Open

3

Make CUSTOM_LIST_1 header generic — remove the hardcoded Schoolyouareattending alias or make it organisation-configurable

Open

4

Only create OrderDTO when ORDER_NUMBER or ORDER_ID is present on the row (instead of always instantiating)

Resolved — null-guard added; OrderLineItem is now created only when the corresponding SalesOrder resolves or is created

5

Validate ORDER_STATUS parsing properly (current code uses only the first character)

Open — partial hardening in place

6

Reduce redundant Organisation lookups in the import call stack

Open (performance; not blocking)

7

Reinstate access-control check (filter(e → org.equals(e.getOrganiser()))) on eventService.findOneEntity(eventId)

Open — security gap; validate before closing

8

Replace printStackTrace() swallows in the outer process() catch blocks with a thrown ImportException carrying a meaningful message

Partially resolved — inner try-catch now sets ERROR outcome with exception message; outer process() still prints stack trace (mirror of results-side ADO-454 work)

9. Related Documentation