Import: Results

1. Overview

The Results import service processes spreadsheet files to bulk-import race results into a result set. This includes position, time, laps, and automatic points calculation.

2. Source Code

File Purpose

ResultImportXLS.java

Spreadsheet parsing, column definitions, result creation

ResultSetServiceEx.java

Result set management

RaceResultServiceEx.java

Individual result handling

SASchoolCyclingSeriesPointsCalculator.java

Points calculation based on position

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

3. Column Definitions

The import supports 12 columns, with 1 required field:

3.1. Required Fields

Column Type Aliases

POSITION

INTEGER

Place

3.2. Result Fields

Column Type Aliases

EVENT_PARTICIPANT_ID

STRING

ExternalReferenceID

TIME

TIME

Total Time

LAPS

INTEGER

Num. Laps

NUMBER

STRING

Bib

3.3. Participant Fields (Currently Unused)

Column Type Aliases

FIRST_NAME

STRING

(no aliases defined)

LAST_NAME

STRING

(no aliases defined)

TAG_NUMBER

STRING

(no aliases defined)

3.4. Race Context Fields (Currently Unused)

Column Type Aliases

RACE_ID

INTEGER

(no aliases defined)

RACE_NAME

STRING

(no aliases defined)

EVENT_CATEGORY_ID

INTEGER

(no aliases defined)

EVENT_CATEGORY_NAME

STRING

(no aliases defined)

4. Processing Flow

1. Receive InputStream + raceId + sheetIndex
2. Load workbook into memory (XSSFWorkbook)
3. Lookup Race
4. Lookup Event from Race
5. Find or create ResultSet (name: "Results")
6. CLEAR existing results from ResultSet  <-- Destructive!
7. For each row:
   a. Skip row 0 (header row, detect columns)
   b. Check if blank (first 5 cells empty)
   c. Lookup EventParticipant by ID
   d. Create RaceResult with position, time, laps
   e. Calculate points using SASchoolCyclingSeriesPointsCalculator
   f. Save result and add to ResultSet
8. Save ResultSet
9. Return response with ResultSet ID

5. Result Status Logic

Position determines result status:

final String position = parseAsString(row, map, Column.POSITION);
if ("-".equals(position)) {
    request.setStatus(ResultStatus.LAPPED);
} else {
    request.setStatus(ResultStatus.FINISHED);
    request.setPosition(parseAsInt(row, map, Column.POSITION));
}

  • Position "-" = LAPPED (did not finish all laps)

  • Any number = FINISHED with that position

6. Points Calculation

Points are calculated using a pluggable calculator:

request.setPoints(pointsCalculator.getPoints(request.getPosition()));

The SASchoolCyclingSeriesPointsCalculator implements series-specific points tables (e.g., 1st = 100pts, 2nd = 80pts, etc.).

7. TODO Items

7.1. TODO 1: Implement Access Control (Line 156-157)

Current Code:

Race race = raceService.findOneEntity(raceId)
    //TODO Fix to implement access control
    //.filter(e -> org.equals(e.getOrganiser()))
    .orElseThrow(() -> new NotFoundException(...));

Issue: Access control check is commented out.

Recommendation: Implement proper access control:

Race race = raceService.findOneEntity(raceId)
    .filter(r -> hasAccessToRace(currentUser, r))
    .orElseThrow(() -> new NotFoundException(...));

7.2. TODO 2: Reuse Existing Results (Line 168-170)

Current Code:

final ResultSet resultSet = resultSetQueryService.createOrReplace("Results", ...);
//TODO Rather reuse as many of these results as possible
//raceResultService.deleteAll(resultSet.getResults());
resultSet.getResults().clear();

Issue: All existing results are cleared on every import. This is destructive and loses any manual edits or additional data attached to results.

Recommendation: Match existing results and update instead of replace:

// Build lookup map of existing results by participant
Map<Long, RaceResult> existingResults = resultSet.getResults().stream()
    .collect(Collectors.toMap(
        r -> r.getEventParticipant().getId(),
        r -> r));

// For each row, update existing or create new
for (Row row : sheet) {
    Long epId = parseAsLong(row, map, Column.EVENT_PARTICIPANT_ID);
    RaceResult result = existingResults.getOrDefault(epId, new RaceResult());
    // ... update result fields
}

7.3. TODO 3: Improve Exception Handling (Line 209-218)

Current Code:

} catch (FileNotFoundException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
} catch (NotFoundException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
} catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}
return null;

Issue: Exceptions swallowed with stack trace only.

Recommendation: Return error response:

} catch (FileNotFoundException | IOException e) {
    log.error("File processing error", e);
    return ResultAddResponseDTO.error("Failed to read file: " + e.getMessage());
} catch (NotFoundException e) {
    log.error("Entity not found", e);
    return ResultAddResponseDTO.error("Race not found: " + e.getMessage());
}

8. Missing Column Aliases

Several columns have no aliases defined (commented out):

//		addHeaderDictionary("", Column.FIRST_NAME);
//		addHeaderDictionary("", Column.LAST_NAME);
//		addHeaderDictionary("", Column.TAG_NUMBER);
//		addHeaderDictionary("", Column.RACE_ID);
//		addHeaderDictionary("", Column.RACE_NAME);
//		addHeaderDictionary("", Column.EVENT_CATEGORY_NAME);
//		addHeaderDictionary("", Column.EVENT_CATEGORY_ID);

Recommendation: Add common aliases:

addHeaderDictionary("FirstName", Column.FIRST_NAME);
addHeaderDictionary("Name", Column.FIRST_NAME);
addHeaderDictionary("LastName", Column.LAST_NAME);
addHeaderDictionary("Surname", Column.LAST_NAME);
addHeaderDictionary("Tag", Column.TAG_NUMBER);
addHeaderDictionary("Chip", Column.TAG_NUMBER);
addHeaderDictionary("Category", Column.EVENT_CATEGORY_NAME);

9. Design Suggestions for Async Migration

9.1. 1. Non-Destructive Import Mode

Add option to preserve existing results:

enum ImportMode {
    REPLACE,        // Current behavior - clear and import
    UPDATE,         // Match and update existing, add new
    APPEND          // Only add new, error on duplicates
}

9.2. 2. Participant Resolution Options

Currently requires EVENT_PARTICIPANT_ID. Add fallback resolution:

EventParticipant ep = null;

// Option 1: By ID
if (epId != null) {
    ep = eventParticipantService.findOne(epId);
}

// Option 2: By bib number
if (ep == null && bibNumber != null) {
    ep = eventParticipantService.findByRaceAndNumber(race, bibNumber);
}

// Option 3: By name + category
if (ep == null && firstName != null && lastName != null) {
    ep = eventParticipantService.findByRaceAndName(race, firstName, lastName);
}

9.3. 3. Batch Points Calculation

Calculate points after all results loaded:

// Import all results first (without points)
List<RaceResult> results = importAllRows(sheet);

// Sort by position
results.sort(Comparator.comparing(RaceResult::getPosition));

// Calculate points in order
pointsCalculator.calculateAll(results);

// Batch save
raceResultService.saveAll(results);

9.4. 4. Result Validation

Add validation before saving:

// Check for duplicate positions
Set<Integer> positions = new HashSet<>();
for (RaceResult r : results) {
    if (!positions.add(r.getPosition())) {
        return ResultAddResponseDTO.error("Duplicate position: " + r.getPosition());
    }
}

// Check for missing positions (gaps)
// 1, 2, 4 = missing 3

10. Time Parsing

Time is converted from LocalTime to milliseconds:

request.setDurationMilliSecond(
    parseAsLocalTime(row, map, Column.TIME)
        .toNanoOfDay() / 1000000
);

Note: This assumes the TIME column contains a valid time value. Excel may store race times as: - Time format (e.g., "1:23:45.678") - Decimal hours (e.g., "1.395833") - Text (e.g., "01:23:45")

The current implementation may need enhancement for all formats.

11. Related Documentation