Import: Membership

1. Overview

The Membership import service processes spreadsheet files to bulk-add members into a membership period. This involves complex orchestration including person lookup/creation, race number assignment, and order handling.

2. Source Code

File Purpose

MembershipImportXLS.java

Spreadsheet parsing, column definitions, row extraction

MembershipImportService.java

Business logic orchestration, person/number/order handling

MembershipAddRequestDTO.java

Request payload for member addition

MembershipAddResponseDTO.java

Response with outcome per row

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

3. Column Definitions

The import supports 30 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

EMAIL

STRING

Email, EmailAddress

USER_KEY

LONG

UserKey

MAIN_MEMBER_ID

STRING

MainMember, MainMemberID

EXTERNAL_ID

LONG

UID, PID

OTHER_NUMBER

STRING

OtherNumber

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. Membership Fields

Column Type Aliases

MEMBERSHIP_ID

INTEGER

MID, MembershipID

MEMBERSHIP_NUMBER

STRING

Member#, MemberNo, MemberNumber, Membership#, MembershipNo, MembershipNumber

STATUS

STRING

Status, MembershipStatus (default: ACTIVE)

MEMBERSHIP_TYPE_ID

INTEGER (FK)

MembershipType, MembershipTypeID

MEMBERSHIP_PERIOD_ID

INTEGER (FK)

MembershipPeriod, MembershipPeriodID, Period, PeriodID

MEMBERSHIP_CRITERIA_ID

INTEGER (FK)

MembershipCriteria, MembershipCriteriaID, Criteria, CriteriaID

MEMBERSHIP_VALID_FROM

DATE

ValidFrom

MEMBERSHIP_VALID_TO

DATE

ValidTo

3.5. Order Fields

Column Type Aliases

ORDER_ID

INTEGER

OrderID

ORDER_NUMBER

STRING

OrderNumber, 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.6. Control Fields

Column Type Aliases

FORCE_CHANGE

BOOLEAN

Fix, Force

4. Architecture

4.1. Two-Layer Design

The membership import uses a two-layer architecture:

MembershipImportXLS (Spreadsheet Layer)
    |
    +--> createRequestDTO()    [Parse cells to DTO]
    |
    +--> extractRow()          [Process single row]
            |
            v
MembershipImportService (Business Logic Layer)
    |
    +--> addMember()           [Orchestrate creation]
            |
            +--> findOrCreateNumber()
            +--> lookupOrCreateUser()
            +--> findOrCreateOrder()
            +--> findOrCreateMembership()

4.2. Transaction Handling

Uses REQUIRES_NEW propagation for row isolation:

@Transactional(propagation = Propagation.REQUIRES_NEW)
public MembershipAddResponseDTO addMember(Organisation organisation,
    MembershipAddRequestDTO request, MembershipPeriod period)

This ensures each row is processed in its own transaction, preventing one failure from rolling back the entire import.

5. Processing Flow

5.1. MembershipImportXLS Flow

1. Receive InputStream + orgId + periodId + sheetIndex
2. Load workbook into memory (XSSFWorkbook)
3. Lookup Organisation (tenantService.getOrganisation)
4. Lookup MembershipPeriod (verify belongs to org)
5. For each row:
   a. Skip row 0 (header row, detect columns)
   b. Check if blank (first 5 cells empty)
   c. Create MembershipAddRequestDTO
   d. Call membershipImportService.addMember()
   e. Collect response
6. Return list of responses

5.2. MembershipImportService.addMember() Flow

1. Validate request (period, person data)
2. Find or create RaceNumber
   - Lookup by ID or number
   - Create new if not exists
3. Find or create User (Person)
   - Lookup by person ID if provided
   - Or lookup/match by identity details
   - Or get from existing number assignment
4. Set gender based on title (if unknown)
5. Set identity country based on type
6. Update identification details
7. Assign personal details
8. Save user
9. Find or create Order
10. Find or create Membership
11. Assign person to number
12. Return response

6. TODO Items

6.1. MembershipImportXLS TODOs

6.1.1. TODO 1: Reduce Organisation Lookups (Line 281-283)

Current Code:

//TODO Too many org lookups in this call stack. Review and optimise
Organisation org = tenantService.getOrganisation(orgId);

Issue: Same as EventParticipant - organisation looked up multiple times.

Recommendation: Pass organisation through call stack.

6.1.2. TODO 2: Order Status Parsing (Line 225-232)

Current Code:

//TODO Ensure order status can be parsed
final String orderStatusStr = parseAsString(row, map, Column.ORDER_STATUS);
OrderStatus orderStatus = null;
if(orderStatusStr != null)
    orderStatus = OrderStatus.fromChar(orderStatusStr.substring(0, 1).toUpperCase());

Issue: Only uses first character, may fail unexpectedly.

Recommendation: Add validation and meaningful error message.

6.1.3. TODO 3: Improve Exception Handling (Line 332-341)

Current Code:

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

Issue: Exceptions swallowed with stack trace only.

Recommendation: Throw or return error response.

6.2. MembershipImportService TODOs

6.2.1. TODO 4: Lookup by ID First (Line 90)

Current Code:

//TODO Lookup by ID first, then fall back to lookup by number
RaceNumber number = null;
if(request.getNumber() != null) {
    if(request.getNumber().getId() != null) {
        number = raceNumberRepository.findById(request.getNumber().getId()).orElse(null);
    } else if (request.getNumber().getNumber() != null) {
        // ...

Issue: The code already does ID lookup first, but TODO suggests this was added later.

Status: Appears to be resolved. TODO comment can be removed.

6.2.2. TODO 5: Generate Next Number (Line 121)

Current Code:

} else {
    //We do not proceed if the number was not provided
    //TODO In future we may want to generate the next number
    throw new MembershipImportException("Membership number not supplied");
}

Issue: Cannot auto-generate membership numbers.

Recommendation: Add optional auto-generation:

if (period.isAutoGenerateNumbers()) {
    number = generateNextNumber(period);
} else {
    throw new MembershipImportException("Membership number not supplied");
}

6.2.3. TODO 6: Use External Order ID (Line 285)

Current Code:

final Order targetOrder = orderRepository
    //TODO We should use the externalOrderId here.
    .findByOrganisationAndExternalId(org, sourceOrder.getNumber())

Issue: Lookup uses externalId field but TODO questions this.

Recommendation: Review order identification strategy. If order number should map to Order.number not Order.externalId, update the query.

6.2.4. TODO 7: Locate LineItem for Amount (Line 356-358)

Current Code:

//TODO Locate relevant LineItem and update that amount
//		if(request.getProduct() != null)
//			membership.setProductAmount(request.getProduct().getPrice());

Issue: Product amount cannot be set on membership - needs to go to order line item.

Recommendation: Implement line item creation/update:

if (request.getProduct() != null && request.getProduct().getPrice() != null) {
    LineItem item = findOrCreateLineItem(order, membership);
    item.setAmount(request.getProduct().getPrice());
    lineItemRepository.save(item);
}

7. Person Resolution Strategy

The membership import has sophisticated person resolution:

7.1. Resolution Priority

  1. By Person ID: If person.id is provided, lookup directly

  2. By Identity Details: Call personService.matchOrAdd() with name/ID number

  3. By Number Assignment: If number exists and has person assigned, use that

7.2. Multi-Organisation Support

List<User> persons = personService.matchOrAdd(
    candidate,
    null,
    organisation);

The matchOrAdd method can find persons across organisations and add them to the current organisation if not already a member.

7.3. Identity Matching Logic

// Verify identity number matches
if(candidate.getIdentityNumber() != null &&
   candidate.getIdentityNumber().equals(personWrapper.getIdentityNumber()))
    return persons.get(0);
else
    throw new MembershipImportException("Identity number does not match");

8. Gender Inference

If gender is unknown or not provided, infer from title:

if (person.getTitle() != null &&
    (person.getGender() == null || person.getGender() == Gender.UNKNOWN)) {
    String title = person.getTitle().replace(".", "").toUpperCase();
    if (title.equals("MS") || title.equals("MRS") || title.equals("MISS"))
        person.setGender(Gender.FEMALE);
    else
        person.setGender(Gender.MALE);
}

9. Identity Country Inference

Set identity type based on country code:

if (countryCode.equals("ZA")) {
    person.setIdentityType(IdentityType.NATIONAL);
} else {
    person.setIdentityType(IdentityType.PASSPORT);
}

10. Design Suggestions for Async Migration

10.1. 1. Separate Validation from Processing

// Phase 1: Validate all rows, collect FK values
ValidationResult validate(ImportJob job);

// Phase 2: Process validated rows
void processValidated(ImportJob job, Map<String, Long> resolvedFKs);

10.2. 2. Batch Number Creation

Instead of creating numbers one at a time:

// Pre-create all missing numbers in batch
Set<String> missingNumbers = findMissingNumbers(job);
raceNumberRepository.saveAll(createNumbers(missingNumbers, period));

10.3. 3. Cache Period Metadata

ImportContext context = new ImportContext();
context.setPeriod(period);
context.setCriteriaList(criteriaRepository.findByPeriod(period));
context.setNumberType(period.getNumberTypeCascade());

10.4. 4. Handle Duplicate Detection

Current implementation may create duplicates if person matching fails:

// Add duplicate check before creation
if (membershipRepository.existsByPeriodAndIdentityNumber(period, idNumber)) {
    return MembershipAddResponseDTO.error("Member already exists");
}

11. Related Documentation