Jury Results System Documentation¶

Stufe:         6.0 Punkte  (= A)
Abzug Ausf.:   2.0 Punkte  (= B)
─────────────────────────────────
Formel:        (10 + A) - B
Endwert:       (10 + 6) - 2 = 14.0 ✅ (automatisch berechnet)

┌─────────────────────────┐
│   tfx_disziplinen       │  Discipline Definition
│  ─────────────────────  │
│  int_disziplinenid (PK) │  ID: 111 = "Boden m. P1-P9"
│  var_name               │  Name: "Boden m. P1-P9"
│  var_formel             │  Formula (direct): "(10 + A) - B" oder NULL
│  int_formelid (FK)      │  Formula Reference: 4 = "P-Wettkampf"
│  int_sportid            │  Sport: 11 = "P-Stufen"
│  ...                    │
└───────┬─────────────────┘
        │
        │ 1:N
        ▼
┌─────────────────────────────────┐
│ tfx_disziplinen_felder          │  Field Definitions per Discipline
│ ─────────────────────────────── │
│ int_disziplinen_felderid (PK)   │  ID: 220, 221, 222
│ int_disziplinenid (FK)          │  → 111 (Boden)
│ var_name                        │  "Stufe", "AbzugAusf.", "Endwert"
│ int_sortierung                  │  Sort Order: 1, 2, 3
│ bol_endwert                     │  Is Final Score: false, false, TRUE
│ bol_ausgangswert                │  Is Starting Score: false
│ int_gruppe                      │  Group: 1
│ bol_enabled                     │  Enabled: true
└───────┬─────────────────────────┘
        │
        │ 1:N
        ▼
┌──────────────────────────────────┐
│ tfx_jury_results                 │  Actual Performance Values
│ ────────────────────────────────│
│ int_juryresultsid (PK)           │  ID: 1, 2, 3
│ int_wertungenid (FK)             │  → tfx_wertungen.int_wertungenid
│ int_disziplinen_felderid (FK)    │  → 220 (Stufe)
│ rel_leistung                     │  Value: 6.0, 2.0, 14.0
│ int_versuch                      │  Attempt: 1
│ int_kp                           │  Control Point: 0
└──────────────────────────────────┘

┌─────────────────────────┐
│   tfx_formeln           │  Formula Definitions (Lookup Table)
│  ─────────────────────  │
│  int_formelid (PK)      │  ID: 4
│  var_name               │  Name: "P-Wettkampf"
│  var_formel             │  Formula: "(10 + A) - B"
│  int_typ                │  Type: (unused)
└─────────────────────────┘

┌─────────────────────────────┐
│ tfx_wertungen               │  Base Score Record
│ ─────────────────────────── │
│ int_wertungenid (PK)        │  ID: 116 (Luis Bader)
│ int_teilnehmerid (FK)       │  → Participant
│ int_wettkaempfeid (FK)      │  → Competition
│ var_riege                   │  Squad: "zz"
└─────────┬───────────────────┘
          │
          │ 1:N
          ▼
┌─────────────────────────────┐
│ tfx_wertungen_details       │  Score per Discipline (Legacy Compatible)
│ ─────────────────────────── │
│ int_wertungen_detailsid(PK) │
│ int_wertungenid (FK)        │  → 116
│ int_disziplinenid (FK)      │  → 111 (Boden)
│ rel_leistung                │  Final Score: 14.0 (auto-updated!)
│ int_versuch                 │  Attempt: 1
└─────────────────────────────┘

// 1. Try loading from tfx_formeln via int_formelid (preferred)
const formulaResult = await prisma.$queryRawUnsafe(`
  SELECT 
    d.var_formel as "disciplineFormula",
    f.var_formel as "tableFormula"
  FROM tfx_disziplinen d
  LEFT JOIN tfx_formeln f ON d.int_formelid = f.int_formelid
  WHERE d.int_disziplinenid = $1
`, disciplineId);

// 2. Prioritize tfx_formeln over discipline direct field
formula = formulaResult[0].tableFormula || formulaResult[0].disciplineFormula;

Felder (sortiert nach int_sortierung):
  1. Stufe (220)        → A
  2. AbzugAusf. (221)   → B
  3. Endwert (222)      → (berechnet, nicht in Formel)

Formel: "(10 + A) - B"
Mapping: A = 6.0, B = 2.0
Berechnung: (10 + 6.0) - 2.0 = 14.0

// Regex extracts first number (with optional parenthesis)
const startValueMatch = formula.match(/^[(\s]*(\d+\.?\d*)/);
// "(10 + A) - B" → matches "10"
// "A - B" → no match, use default 10.0

const startValue = startValueMatch ? parseFloat(startValueMatch[1]) : 10.0;

Jury Results in DB:
  - Stufe (220): 6.0 ✓
  - AbzugAusf. (221): 2.0 ✓
  - Endwert (222): ✗ FEHLT!

→ Server berechnet automatisch: (10 + 6) - 2 = 14.0
→ Speichert in tfx_jury_results für Feld 222
→ Aktualisiert tfx_wertungen_details.rel_leistung = 14.0

// 1. Detect missing final score
const hasFinalScore = juryResults.some(jr => jr.isFinalScore);
if (!hasFinalScore && formula && endwertFieldId) {
  needsEndwertCalculation = true;
}

// 2. Build value map from non-final jury results
const valueMap: { [key: string]: number } = {};
const letters = formula.match(/[A-Z]/g) || [];

letters.forEach((letter, index) => {
  if (nonFinalScores[index]) {
    valueMap[letter] = parseFloat(nonFinalScores[index].performance);
  }
});
// Result: { A: 6.0, B: 2.0 }

// 3. Replace variables in formula
let evalFormula = formula; // "(10 + A) - B"
Object.keys(valueMap).forEach(letter => {
  evalFormula = evalFormula.replace(new RegExp(letter, 'g'), 
    valueMap[letter].toString());
});
// Result: "(10 + 6.0) - 2.0"

// 4. Evaluate using JavaScript Function constructor
const calculatedScore = new Function(`return ${evalFormula}`)();
// Result: 14.0

// 5. Insert into tfx_jury_results
await prisma.$executeRawUnsafe(`
  INSERT INTO tfx_jury_results 
    (int_wertungenid, int_disziplinen_felderid, rel_leistung, int_versuch, int_kp)
  VALUES ($1, $2, $3, $4, $5)
`, wertungenId, endwertFieldId, calculatedScore, 1, 0);

// 6. Update legacy table for compatibility
await prisma.$executeRawUnsafe(`
  UPDATE tfx_wertungen_details 
  SET rel_leistung = $1
  WHERE int_wertungenid = $2 AND int_disziplinenid = $3
`, calculatedScore, wertungenId, disciplineId);

try {
  const calculatedScore = new Function(`return ${evalFormula}`)();

  // Validation
  if (isNaN(calculatedScore) || !isFinite(calculatedScore)) {
    throw new Error('Invalid calculation result');
  }

  console.log('✅ Calculated final score:', calculatedScore);
} catch (error) {
  console.error('❌ Error calculating final score:', error);
  // Falls back to manual entry or existing value
}

┌─────────────────────┐
│  Score Capture UI   │
│  (Client)           │
└──────────┬──────────┘
           │ POST /api/scores/capture
           │ {
           │   wertungenId: 116,
           │   fieldValues: [
           │     { fieldId: 220, value: 6.0 },
           │     { fieldId: 221, value: 2.0 }
           │   ]
           │ }
           ▼
┌─────────────────────────┐
│  Server: scores.ts      │
│  (Backend)              │
├─────────────────────────┤
│ 1. Validate fields      │
│ 2. Insert into          │
│    tfx_jury_results     │
│ 3. Check for missing    │
│    final score          │
│ 4. ✨ AUTO-CALCULATE ✨ │
│ 5. Insert Endwert       │
│ 6. Update               │
│    tfx_wertungen_details│
└──────────┬──────────────┘
           │
           ▼
┌─────────────────────────┐
│  Database               │
├─────────────────────────┤
│ tfx_jury_results:       │
│   Row 1: Field 220 = 6  │
│   Row 2: Field 221 = 2  │
│   Row 3: Field 222 = 14 │← Auto-inserted!
│                         │
│ tfx_wertungen_details:  │
│   rel_leistung = 14     │← Auto-updated!
└─────────────────────────┘

┌─────────────────────────┐
│  Database               │
└──────────┬──────────────┘
           │ GET /api/scores?eventId=1&squadName=zz
           ▼
┌─────────────────────────────────────┐
│  Server: scores.ts                  │
├─────────────────────────────────────┤
│ 1. Load tfx_wertungen + details     │
│ 2. For each score:                  │
│    a. Load jury results from        │
│       tfx_jury_results              │
│    b. Load formula from             │
│       tfx_formeln or                │
│       tfx_disziplinen               │
│    c. Extract startValue            │
│ 3. Map to camelCase                 │
└──────────┬──────────────────────────┘
           │ Response:
           │ {
           │   formula: "(10 + A) - B",
           │   startValue: 10,
           │   score: 14,
           │   juryResults: [
           │     { fieldName: "Stufe", performance: 6 },
           │     { fieldName: "AbzugAusf.", performance: 2 },
           │     { fieldName: "Endwert", performance: 14, isFinalScore: true }
           │   ]
           │ }
           ▼
┌─────────────────────────┐
│  Results Page UI        │
│  (Client)               │
├─────────────────────────┤
│ JuryResultsDisplay:     │
│                         │
│ 📊 6.0 (A) - 2.0 (B)    │
│ = 14.00                 │
│                         │
│ Formel: (10 + A) - B    │
│ Ausgehend von 10 Punkten│
└─────────────────────────┘

GET /api/scores?eventId=1&squadName=zz&limit=200

{
  "results": [
    {
      "id": 116,
      "participantId": 100,
      "disciplineId": 111,
      "score": 14.0,
      "formula": "(10 + A) - B",
      "startValue": 10,
      "juryResults": [
        {
          "id": 1,
          "disciplineFieldId": 220,
          "fieldName": "Stufe",
          "performance": 6.0,
          "isFinalScore": false,
          "isStartingScore": false,
          "sortOrder": 1
        },
        {
          "id": 2,
          "disciplineFieldId": 221,
          "fieldName": "AbzugAusf.",
          "performance": 2.0,
          "isFinalScore": false,
          "isStartingScore": false,
          "sortOrder": 2
        },
        {
          "id": 3,
          "disciplineFieldId": 222,
          "fieldName": "Endwert",
          "performance": 14.0,
          "isFinalScore": true,
          "isStartingScore": false,
          "sortOrder": 3
        }
      ]
    }
  ]
}

{
  "wertungenId": 116,
  "fieldValues": [
    { "fieldId": 220, "value": 6.0 },
    { "fieldId": 221, "value": 2.0 }
  ]
}

{
  "success": true,
  "calculatedScore": 14.0,
  "message": "Score saved and final score calculated"
}

6.0 (A) - 2.0 (B) = 14.00
Formel: (10 + A) - B

[ℹ️ Ausgehend von 10.0 Punkten]

interface JuryResultsDisplayProps {
  juryResults: JuryResult[];     // All field values
  finalScore: number;            // Total score
  formula?: string;              // Formula like "(10 + A) - B"
  startValue?: number;           // Starting value (e.g., 10)
  isCompact?: boolean;           // Compact mode for tables
}

const getFormulaSymbol = (index: number): string => {
  if (!formula) return String.fromCharCode(65 + index); // A, B, C

  // Extract letters from formula in order
  const letterMatches = formula.match(/[A-Z]/g);
  return letterMatches?.[index] || String.fromCharCode(65 + index);
};

{score.juryResults && score.juryResults.length > 0 ? (
  <JuryResultsDisplay
    juryResults={score.juryResults}
    finalScore={score.score}
    formula={score.formula}
    startValue={score.startValue}
    isCompact={true}
  />
) : (
  <span className="text-lg font-semibold">
    {score.score.toFixed(2)}
  </span>
)}

{
  key: 'useJuryResults',
  label: 'Jury-Results verwenden',
  description: 'Feldspezifische Jury-Results in Wertungserfassung, Ergebnisanzeige, Live-Results und PDF-Export verwenden',
  type: 'boolean',
  value: true  // DEFAULT: ENABLED
}

WICHTIG: Datenbankstruktur

Diese Einstellung darf NICHT während eines aktiven Wettkampfes geändert werden!

• Jury-Results verwenden unterschiedliche Datenbanktabellen
• Änderungen während laufendem Wettkampf führen zu Dateninkonsistenz
• Standard: Aktiviert (empfohlen)

SELECT int_formelid FROM tfx_disziplinen WHERE int_disziplinenid = 111;

SELECT * FROM tfx_disziplinen_felder 
WHERE int_disziplinenid = 111 AND bol_endwert = true;

PrismaClientInitializationError:
error: Error validating datasource `db`: 
the URL must start with the protocol `postgresql://`

{
  name: 'turnfix-server',
  env: {
    DATABASE_URL: 'postgresql://user:pass@localhost:5432/dbname',
    PORT: 3001,
    DEBUG: 'true'
  }
}

// Formula loading (lines ~180-210)
if (disciplineId) {
  const formulaResult = await prisma.$queryRawUnsafe(`
    SELECT 
      d.var_formel as "disciplineFormula",
      f.var_formel as "tableFormula"
    FROM tfx_disziplinen d
    LEFT JOIN tfx_formeln f ON d.int_formelid = f.int_formelid
    WHERE d.int_disziplinenid = $1
  `, disciplineId);

  formula = formulaResult[0].tableFormula || formulaResult[0].disciplineFormula;
}

// Auto-calculation (lines ~220-280)
if (needsEndwertCalculation && formula && endwertFieldId) {
  const valueMap = buildValueMap(juryResults, formula);
  const evalFormula = replaceVariables(formula, valueMap);
  const calculatedScore = evaluate(evalFormula);

  await insertJuryResult(wertungenId, endwertFieldId, calculatedScore);
  await updateWertungenDetails(wertungenId, disciplineId, calculatedScore);
}

// Results page loads scores with jury results
const { data: scores } = useQuery(['/api/scores', filters], {
  queryKey: ['/api/scores', eventId, squadName],
  queryFn: () => fetchScores(eventId, squadName)
});

// Each score has juryResults array
scores.results.forEach(score => {
  if (score.juryResults && score.juryResults.length > 0) {
    // Display with JuryResultsDisplay component
  }
});

-- Discipline
SELECT * FROM tfx_disziplinen WHERE int_disziplinenid = 111;
-- Result: "Boden m. P1-P9", int_formelid = 4

-- Formula
SELECT * FROM tfx_formeln WHERE int_formelid = 4;
-- Result: "P-Wettkampf", "(10 + A) - B"

-- Fields
SELECT * FROM tfx_disziplinen_felder WHERE int_disziplinenid = 111;
-- Results:
--   220: "Stufe", sortierung=1, endwert=false
--   221: "AbzugAusf.", sortierung=2, endwert=false
--   222: "Endwert", sortierung=3, endwert=TRUE

-- Jury Results
SELECT * FROM tfx_jury_results WHERE int_wertungenid = 116;
-- Results:
--   Field 220: 6.0
--   Field 221: 2.0
--   Field 222: 14.0 ← Auto-calculated!

-- Wertungen Details (Legacy)
SELECT * FROM tfx_wertungen_details 
WHERE int_wertungenid = 116 AND int_disziplinenid = 111;
-- Result: rel_leistung = 14.0 ← Auto-updated!

{
  "results": [
    {
      "id": 116,
      "participantId": 100,
      "disciplineId": 111,
      "competitionId": 14,
      "score": 14.0,
      "formula": "(10 + A) - B",
      "startValue": 10,
      "participant": {
        "firstName": "Luis",
        "lastName": "Bader"
      },
      "discipline": {
        "name": "Boden m. P1-P9"
      },
      "juryResults": [
        {
          "id": 1,
          "disciplineFieldId": 220,
          "fieldName": "Stufe",
          "performance": 6.0,
          "isFinalScore": false,
          "sortOrder": 1
        },
        {
          "id": 2,
          "disciplineFieldId": 221,
          "fieldName": "AbzugAusf.",
          "performance": 2.0,
          "isFinalScore": false,
          "sortOrder": 2
        },
        {
          "id": 3,
          "disciplineFieldId": 222,
          "fieldName": "Endwert",
          "performance": 14.0,
          "isFinalScore": true,
          "sortOrder": 3
        }
      ]
    }
  ],
  "pagination": {
    "total": 1,
    "limit": 200,
    "offset": 0,
    "hasMore": false
  }
}