# Aggregated Fields Calculation Reference

This document explains how all aggregated metric fields on the `Application` (`applications`) and `AppFile` (`appfiles`) collections are calculated, and which code paths produce them.

---

## Collections and Fields

### Application (`applications`)

| Field | Unit | Description |
|---|---|---|
| `totalSprayed` | Hectares | Total area covered with spray ON |
| `totalSprLength` | Meters | Total path distance during spray-ON periods |
| `totalFlightLength` | Meters | Total flight path distance (spray ON + turns) |
| `totalSprayTime` | Seconds | Total time with spray ON |
| `totalTurnTime` | Seconds | Total turning time between spray lines |
| `totalFlightTime` | Seconds | Total flight time (all GPS intervals) |
| `totalSprayMat` | L or Kg | Total material dispensed |
| `totalSprayMatUnit` | Code | Rate unit: `3` = L/ha, `4` = Kg/ha |
| `avgSpraySpeed` | m/s | Average ground speed during spray-ON periods |
| `avgXtError` | m | Average absolute cross-track error across spray-on records (stat 1 & 3), valid segments only; `null` when the device firmware does not record xTrack (all binary values are 0) |
| `avgHdop` | — | Average HDOP across spray-ON records; lower = better (< 1 excellent, 1–2 good, > 5 poor) |
| `flowAccuracyPct` | % | Flow control accuracy: `(totalSprayMat / totalSprayed / appRate) × 100`. `null` when any source field is zero/absent |
| `appRate` | L/ha or Kg/ha | Average application rate recorded from data |
| `startDateTime` | String `YYYYMMDDTHHmmss` | Timestamp of the first GPS record |
| `endDateTime` | String `YYYYMMDDTHHmmss` | Timestamp of the last GPS record |

### AppFile (`appfiles`)

Mirrors the same set of aggregated fields as `Application`, computed per individual data file within the upload archive. Fields: `totalSprayed`, `totalSprLength`, `totalFlightLength`, `totalSprayTime`, `totalTurnTime`, `totalFlightTime`, `totalSprayMat`, `totalSprayMatUnit`.

### ApplicationDetail (`application_details`)

Stores the raw GPS + sensor records parsed from each data file. These are the source rows from which all aggregated fields above are derived. Key fields used in aggregation: `gpsTime`, `utmX`, `utmY`, `swath`, `sprayStat`, `llnum`, `grSpeed`, `lhaReq`, `lhaApp`, `lminApp`, `xTrack`, `sprayHeight`, `radarAlt`.

### Job (`jobs`)

| Field | Unit | Description |
|---|---|---|
| `ttSprArea` | Hectares | Planned spray area minus exclusion zones (from GeoJSON polygons, not from flight data) |

---

## Code Paths

There are two independent processing pipelines that calculate these fields:

```
Upload (AgNav binary / Shape)
  └─► job_worker.js ──► importData()
        └─► importDataFiles() (per file)
              ├─► readNTFile()        (AgNav binary .nt files)
              └─► readShapeDataFile() (ESRI Shape .dbf files)

Partner sync (SatLoc partner logs)
  └─► partner_sync_worker.js
        └─► satloc_application_processor.js ──► processJobGroup()
```

---

## Pipeline 1 — AgNav Binary / Shape Files (`job_worker.js`)

### Entry point: `importData()`  (`workers/job_worker.js` line 943)

Orchestrates the full import for one uploaded archive. Steps:

1. Scans the unzipped folder for data files matching known patterns.
2. Classifies files: `FILE.DATA_AGNAV` (`.nt` binary), `FILE.DATA_SHAPE` (`.dbf` shape), or legacy `FILE.DATA_SALOG` (`.asc` ASCII — no longer processed).
3. Sorts files by AGN timestamp prefix to process them in chronological order.
4. Calls `importDataFiles()` for each file/pair and accumulates the per-file sub-totals.
5. After all files are processed, computes the final `appData` object:
   ```javascript
   appRate = mean(avgRates[])           // average of per-file average rates
   totalSprayed = sum(data.totalSprayed)   // m² – converted to ha at the Application level
   totalSprLength = sum(data.totalSprLength)
   totalFlightLength = sum(data.totalFlightLength)
   totalSprayTime = sum(data.sprayTime)
   totalTurnTime = sum(data.turnTime)
   totalFlightTime = sum(data.totalTime)
   totalSprayMat = sum(data.totalSprayMat)
   avgSpraySpeed = weightedMean(avgSpraySpeed * spraySpeedCount)
   avgHdop       = weightedMean(hdopSum across files) / totalHdopCount
   ```
6. Writes the computed totals to the `Application` document (with `m² → ha` unit conversion for `totalSprayed`).
7. After all Application fields are set, `work()` computes `flowAccuracyPct = (totalSprayMat / totalSprayed / appRate) × 100` when all three are positive, and writes it to Application.

### Per-file processing: `importDataFiles()` (line 1108)

For each data file (or spray-on/spray-off pair):

1. Reads the companion `q*` metadata file to obtain configured application rate, rate unit, and flow controller type.
2. Creates an `AppFile` document.
3. Calls `readNTFile()` or `readShapeDataFile()` to get per-record data and per-file totals.
4. Inserts all `ApplicationDetail` records into MongoDB in 1,000-record batches.
5. Iterates the sorted record array to compute **time-based and point-metric fields** (`totalFlightTime`, `totalSprayTime`, `totalTurnTime`, `avgSpraySpeed`, `avgHdop`, `avgXtError`) — this loop runs over the stored `ApplicationDetail` records after all files in the pair are merged and sorted.
6. Saves the per-file totals to the `AppFile` document.

#### Time calculations (loop in `importDataFiles`, line ~1310)

```
totalFlightTime += timeDif  where  0 < timeDif ≤ 120 s  (between every consecutive GPS record)

totalSprayTime  += timeDif  where  0 < timeDif ≤ 120 s  (only when sprayStat > 0)

turnTime: counted from spray-OFF on one line number to spray-ON on the next line number
  turnTime += timeDif  where  5 ≤ timeDif ≤ 120 s

avgSpraySpeed = sum(grSpeed) / count  (all spray-ON records except sprayStat === 3)

avgHdop = sum(stdHdop) / count  (all spray-ON records where stdHdop > 0)

avgXtError = sum(|xTrack|) / count  (spray-ON records where sprayStat ∈ {1, 3} and xTrack ≠ 0)
             null when the device firmware does not record xTrack (all binary values are 0)

AppDetail.xTrack unit: metres for all file types.
  - AgNav binary (.nt) — decoded from raw cm integer at parse time (÷ 100 in _readAgnBinary / _readAmsRpm DRY)
  - SatLoc ASCII (.asc/.log) — X-Track field is natively in metres; no conversion applied
  - AgNav Shape (.shp) — XTRACK field is natively in metres; no conversion applied
Application.avgXtError is therefore in metres for all application types.
No further conversion is applied in the dashboard API or migration.
```

The 120-second cap on time differences rejects GPS dropouts or large gaps between segments.  
Turn time is line-number-aware: only the gap between the end of one spray line and the start of the **next** line number qualifies.

### AgNav binary reader: `readNTFile()` (line 1423)

Reads the raw AgNav binary packet stream (fixed-size `FILE.AGN_PACK_SIZE` packets).

**Spray area and material per binary record:**

```
sprayStat 3  → "start of line" marker — updates prevUTM_X/Y, prevSwath, prevLine but does NOT accumulate area

sprayStat 1 or 2 (spray ON):
  if prevStat > 0 AND prevLine === record.llnum (same spray line):
    sprayedSeg = hypot(utmX - prevUTM_X, utmY - prevUTM_Y) × prevSwath   [m²]
    totalSprays += sprayedSeg
    totalSprayMat += (sprayedSeg × SM2HA) × appliedRate

appliedRate priority:
  1. Q-file configured rate (converted to metric L/ha or Kg/ha)
  2. Fallback to record.lminApp → converted via appRateFromFlowRate()
  3. Fallback to record.lhaReq

appRate = mean(lhaReq across all spray-ON records)
```

Area accumulates only within a single spray line (`prevLine === record.llnum`), preventing cross-line area double-counting.

### Shape file reader: `readShapeDataFile()` (line 1598)

Reads spray-on DBF attributes. Area and material logic is identical to `readNTFile()` except:
- Spray-off file contributes only timing records (no area).
- There is no "start-of-line" `sprayStat 3` marker — the same-line guard uses `prevLine === record.llnum`.

### Distance computation — inline streaming

`totalSprLength` and `totalFlightLength` are computed **incrementally during the file-read loop** in `readNTFile()`, `readShapeDataFile()`, and `readSatLogAsc()`. No separate rescan of the record array is needed.

For multi-file shape uploads (spray-on + spray-off pair merged into one `importInfo`), a cross-file boundary segment is also computed once when the two record sets are joined.

Validity gates applied to **every** consecutive pair:

| Gate | Threshold | Reason |
|---|---|---|
| Time gap | `0 < dt ≤ 120 s` | Skip GPS dropouts, instrument pauses, file boundaries |
| Distance | `dist ≤ 1000 m` | Reject GPS position outliers |
| Spray status (sprLength only) | `prev.sprayStat > 0 \|\| curr.sprayStat > 0` | Skip pure turn/off segments |

Midnight rollover (`gpsTime` is seconds-of-day) is handled in every loop: if `dt < 0` and `|dt| ≥ 80000`, then `dt = 86400 − prevTime + currTime`.

```javascript
// Pseudocode (applied inside readNTFile / readShapeDataFile)
for each record after timeOffset adjustment:
  dt = record.gpsTime - prevRecTime              // handle midnight rollover
  if (dt > 0 && dt <= 120):
    segDist = hypot(record.utmX - prev.utmX, record.utmY - prev.utmY)
    if (segDist <= 1000):
      totalFlightLen += segDist
      if (prevSprStat > 0 || record.sprayStat > 0):
        totalSprLen += segDist
```

`_computeFlightLength()` and `_computeSprLength()` remain as **fallback helpers** (used only when inline totals are not available, e.g. from the migration script). They apply the same two-gate logic:

```javascript
// _computeFlightLength() fallback — all GPS pairs, dt ≤ 120 s AND dist ≤ 1000 m
// _computeSprLength()    fallback — spray-ON pairs, dt ≤ 120 s AND dist ≤ 1000 m
```

---

## Pipeline 2 — SatLoc Partner Logs (`satloc_application_processor.js`)

### Entry point: `processJobGroup()` (`helpers/satloc_application_processor.js` line ~157)

Called by `partner_sync_worker.js` after `satloc_log_parser.js` has parsed the binary SatLoc log into `ApplicationDetail`-compatible records.

**UTM conversion** (per record, using `@mickeyjohn/geodesy/utm.js`):
```javascript
{ easting: utmX, northing: utmY } = LatLon(lat, lon).toUtm(zone, hemisphere)
```

**Flight time** (same 120 s cap as job_worker):
```javascript
if (0 < timeDif ≤ MAX_TIME_DIFF)   // MAX_TIME_DIFF = 120 s
  totalFlightTime += timeDif
```

**Spray time:**
```javascript
if (prevSprayStat > 0 AND curSprayStat > 0 AND 0 < timeDif ≤ 120)
  totalSprayTime += timeDif
```

**Spray area and material** (triggered while `curSprayStat > 0` and `prevSprayStat > 0`):
```
distance  = hypot(utmX - prevUTM_X, utmY - prevUTM_Y)   [m]
swathArea = distance × record.swath                       [m²]

totalSprayed    += swathArea
totalSprayLength += distance

appRate = record.lhaApp || record.lhaReq
totalSprayMat   += (swathArea × appRate) / 10000   [L or Kg]
```

Note: `prevUTM_X/Y` is updated **only** while spray is ON, so the distance for area never bridges a spray-OFF gap.

**Unit conversion after the loop:**
```javascript
totalSprayed = totalSprayed × 1E-4   // m² → hectares
```

**Spray segments** are tracked in addition for map rendering: each continuous spray-ON run is stored as a `{ startTime, endTime, startLat/Lon, endLat/Lon, distance, area, points[] }` segment object.

**Material unit** is determined from SatLoc flow controller type:
```javascript
sprayMatUnit = (fcType === FCTypes.LIQUID) ? RateUnits.LIT_PER_HA : RateUnits.KG_PER_HA
```

**Database writes:**
```javascript
ApplicationFile.updateOne({ _id: appFile._id }, { $set: {
  totalSprLength, totalSprayTime, totalFlightTime,
  totalSprayed, totalSprayMat, totalSprayMatUnit
}});

Application.updateOne({ _id: application._id }, { $set: {
  status: AppStatus.DONE,
  totalSprayTime, totalFlightTime, totalSprayed,
  totalSprayMat, totalSprayMatUnit, totalSprLength,
  appRate: 0,        // Not yet calculated for SatLoc path
  startDateTime, endDateTime
}});
```

---

## Job Planned Spray Area (`job_worker.js` + `job_util.js`)

`Job.ttSprArea` is **not** derived from flight data — it is calculated from the GeoJSON spray-area polygons drawn by the operator when the job is created or updated.

**`jobUtil.calcTTSprayAreas(sprayAreas, excludedAreas)`** (`helpers/job_util.js` line 117):

```
for each sprayArea polygon:
  realArea = turf.area(sprayArea)                   // m²

  for each exclusionZone that intersects this polygon (R-tree spatial index):
    realArea -= turf.area( turf.intersect(sprayArea, exclusionZone) )

ttSprArea += realArea

job.ttSprArea = calcTTSprayAreas(...) × SM2HA       // m² → ha
```

This is recalculated every time a job's spray areas or exclusion zones change.

---

## Unit Conversion Constants

| Constant | Value | Purpose |
|---|---|---|
| `1E-4` | `0.0001` | m² → hectares |
| `CVCST.SM2HA` | `1E-4` | Same as above (used for material calculation) |
| `CVCST.SM2ACR` | `0.000247105` | m² → acres (subscription limit checks) |
| Max time gap | `120 s` | Outlier rejection for all time accumulators |
| Max GPS segment | `1000 m` | Outlier rejection in all distance loops (inline + `_computeFlightLength` / `_computeSprLength` fallbacks) |
| Max time gap (distance) | `120 s` | Skip GPS dropouts in all distance loops — same cap used for flight/spray time accumulators |

---

## Summary: Which Code Sets Which Field

| Field | AgNav binary | Shape | SatLoc partner | Notes |
|---|---|---|---|---|
| `totalSprayed` | `readNTFile` → `importData` | `readShapeDataFile` → `importData` | `processJobGroup` | m² accumulated, converted × 1E-4 to ha before DB write |
| `totalSprLength` | inline in `readNTFile` (fallback: `_computeSprLength`) | inline in `readShapeDataFile` (fallback: `_computeSprLength`) | loop in `processJobGroup` | spray-ON segments, dt ≤ 120 s, dist ≤ 1000 m |
| `totalFlightLength` | inline in `readNTFile` (fallback: `_computeFlightLength`) | inline in `readShapeDataFile` (fallback: `_computeFlightLength`) | not computed | all GPS segments, dt ≤ 120 s, dist ≤ 1000 m |
| `totalSprayTime` | loop in `importDataFiles` | loop in `importDataFiles` | loop in `processJobGroup` | 120 s cap |
| `totalTurnTime` | loop in `importDataFiles` | loop in `importDataFiles` | not computed | line-number-aware |
| `totalFlightTime` | loop in `importDataFiles` | loop in `importDataFiles` | loop in `processJobGroup` | 120 s cap |
| `totalSprayMat` | `readNTFile` | `readShapeDataFile` | `processJobGroup` | L or Kg depending on unit |
| `totalSprayMatUnit` | from Q-file or record | from Q-file or record | from SatLoc `fcType` | 3=L/ha, 4=Kg/ha |
| `avgSpraySpeed` | loop in `importDataFiles` | loop in `importDataFiles` | `processJobGroup` | spray-ON records |
| `avgXtError` | loop in `importDataFiles` (fallback: `migrate_applications.js`) | loop in `importDataFiles` (fallback: `migrate_applications.js`) | `migrate_applications.js` | spray-ON (stat 1 & 3), xTrack ≠ 0 |
| `avgHdop` | loop in `importDataFiles` | loop in `importDataFiles` | not computed | spray-ON records, stdHdop > 0; backfilled by migration |
| `flowAccuracyPct` | `work()` post-field-set | `work()` post-field-set | not computed | (totalSprayMat/totalSprayed/appRate)×100; backfilled by migration |
| `avgSpraySpeed` | loop in `importDataFiles` | loop in `importDataFiles` | not computed | mean `grSpeed` while spray ON |
| `appRate` | mean `lhaReq` in `readNTFile` | mean `lhaReq` in `readShapeDataFile` | not computed (set to 0) | |
| `ttSprArea` (Job) | `calcTTSprayAreas` | `calcTTSprayAreas` | `calcTTSprayAreas` | from planned GeoJSON polygons, not flight data |