Coverage for app/flights/airframe_import.py: 100%
226 statements
« prev ^ index » next coverage.py v7.15.4, created at 2026-08-31 10:13 +0000
1"""Bulk import of a historical airframe logbook (CSV/Excel) for one aircraft.
3Reuses the Phase 28 pilot-logbook machinery wholesale — file parsing, header
4auto-detection, subtotal-row skipping, value parsers — mapped onto
5Flight fields. Counter continuity is validated with per-row warnings
6(historical paper logs often carry small corrections), free-text pilot names
7are written to Flight.pic_name (pic_user_id left NULL), and an optional "opening
8counters" baseline supports importing from a cutover date forward.
9"""
11from __future__ import annotations
13import json
14from dataclasses import dataclass, field
15from datetime import date, timedelta
16from typing import Any
18from flask_babel import gettext as _ # pyright: ignore[reportMissingImports]
19from pilots.logbook_import import ( # pyright: ignore[reportMissingImports]
20 ParsedFile,
21 parse_date_value,
22 parse_duration_value,
23 parse_int_value,
24 parse_time_value,
25)
27AIRFRAME_TARGET_FIELDS: list[str] = [
28 "date",
29 "crew_name",
30 "departure_icao",
31 "arrival_icao",
32 "takeoff_time",
33 "landing_time",
34 "flight_time",
35 "flight_counter_start",
36 "flight_counter_end",
37 "engine_counter_start",
38 "engine_counter_end",
39 "landing_count",
40 "passenger_count",
41 "nature_of_flight",
42 "notes",
43]
45# Normalised source column name → airframe target field.
46_AIRFRAME_ALIASES: dict[str, str] = {
47 "date": "date",
48 "date dd/mm/yy": "date",
49 "pilot": "crew_name",
50 "pilot in command": "crew_name",
51 "pic": "crew_name",
52 "crew": "crew_name",
53 "name": "crew_name",
54 "from": "departure_icao",
55 "departure": "departure_icao",
56 "dep": "departure_icao",
57 "to": "arrival_icao",
58 "arrival": "arrival_icao",
59 "arr": "arrival_icao",
60 # Airframe-log block/flight times feed takeoff_time/landing_time
61 # (flight_time_counter_*/flight_time — airframe-log-facing), never
62 # departure_time/arrival_time (engine_time_counter_*/engine_time —
63 # pilot-log-facing; see models.py's Flight docstring). The two pairs
64 # are never defaulted from each other.
65 "time": "takeoff_time", # first TIME → takeoff
66 "time_2": "landing_time", # second TIME → landing
67 "departure time": "takeoff_time",
68 "off block": "takeoff_time",
69 "off-block": "takeoff_time",
70 "arrival time": "landing_time",
71 "on block": "landing_time",
72 "on-block": "landing_time",
73 "flight time": "flight_time",
74 "block time": "flight_time",
75 "duration": "flight_time",
76 "total time": "flight_time",
77 "landings": "landing_count",
78 "ldg": "landing_count",
79 "ldgs": "landing_count",
80 "landing count": "landing_count",
81 "pax": "passenger_count",
82 "passengers": "passenger_count",
83 "nature": "nature_of_flight",
84 "nature of flight": "nature_of_flight",
85 "remarks": "notes",
86 "remarks and endorsements": "notes",
87 "notes": "notes",
88 "hobbs start": "engine_counter_start",
89 "hobbs end": "engine_counter_end",
90 "hobbs": "engine_counter_end",
91 "engine start": "engine_counter_start",
92 "engine end": "engine_counter_end",
93 "engine counter start": "engine_counter_start",
94 "engine counter end": "engine_counter_end",
95 "tach start": "engine_counter_start",
96 "tach end": "engine_counter_end",
97 "flight counter start": "flight_counter_start",
98 "flight counter end": "flight_counter_end",
99 "counter start": "flight_counter_start",
100 "counter end": "flight_counter_end",
101}
103# Target field → parser (None = keep trimmed string)
104_AIRFRAME_PARSERS: dict[str, Any] = {
105 "takeoff_time": parse_time_value,
106 "landing_time": parse_time_value,
107 "flight_time": parse_duration_value,
108 "flight_counter_start": parse_duration_value,
109 "flight_counter_end": parse_duration_value,
110 "engine_counter_start": parse_duration_value,
111 "engine_counter_end": parse_duration_value,
112 "landing_count": parse_int_value,
113 "passenger_count": parse_int_value,
114}
116# How far apart a row's start counter may be from the previous row's end
117# counter before a continuity warning is raised.
118_COUNTER_TOLERANCE = 0.05
121@dataclass
122class AirframeImportResult:
123 imported: int = 0
124 subtotals: int = 0
125 skipped: list[tuple[int, str]] = field(default_factory=list)
126 # (row_num, reason) — rows that matched a flight already in this aircraft's log
127 duplicates: list[tuple[int, str]] = field(default_factory=list)
128 parse_warnings: list[tuple[int, str, str, str]] = field(default_factory=list)
129 # (row_num, counter_label, previous_end, this_start)
130 continuity_warnings: list[tuple[int, str, float, float]] = field(
131 default_factory=list
132 )
133 has_opening_counters: bool = False
136def _num(v: Any) -> float | None:
137 # Numeric columns come back as decimal.Decimal; freshly parsed values are
138 # plain float — normalise both sides before building/comparing keys (see
139 # the matching note in pilots/logbook_import.py for why this matters).
140 return None if v is None else float(v)
143def _dup_key(fields: dict[str, Any]) -> tuple[Any, ...]:
144 """Exact-duplicate key: date + route + duration + landings. Re-importing
145 the same airframe logbook file (by mistake, or deliberately after
146 appending new rows) must only add genuinely new flights, not double up
147 everything already imported."""
148 return (
149 fields["date"],
150 fields.get("departure_icao") or "ZZZZ",
151 fields.get("arrival_icao") or "ZZZZ",
152 _num(fields.get("flight_time")),
153 fields.get("landing_count"),
154 )
157def _fetch_existing_dedup_keys(aircraft_id: int) -> set[tuple[Any, ...]]:
158 from models import Flight, db # pyright: ignore[reportMissingImports]
160 return {
161 (row[0], row[1], row[2], _num(row[3]), row[4])
162 for row in db.session.query(
163 Flight.date,
164 Flight.departure_icao,
165 Flight.arrival_icao,
166 Flight.flight_time,
167 Flight.landing_count,
168 ).filter_by(aircraft_id=aircraft_id)
169 }
172def propose_airframe_mapping(
173 parsed: ParsedFile, saved: list[Any]
174) -> tuple[dict[str, str], str]:
175 """Return (mapping, match_type) — exact fingerprint reuse, else aliases."""
176 for m in saved:
177 if m.source_fingerprint == parsed.fingerprint:
178 stored = json.loads(m.column_mapping)
179 mapping = {
180 col: stored.get(col, "ignore")
181 if stored.get(col) in AIRFRAME_TARGET_FIELDS
182 else "ignore"
183 for col in parsed.norm_cols
184 }
185 return mapping, "exact"
186 mapping = {col: _AIRFRAME_ALIASES.get(col, "ignore") for col in parsed.norm_cols}
187 return mapping, "alias"
190_HINT_SAMPLE_ROWS = 25
192_TYPE_NAMES: dict[str, str] = {
193 "takeoff_time": "time",
194 "landing_time": "time",
195 "flight_time": "duration",
196 "flight_counter_start": "counter value",
197 "flight_counter_end": "counter value",
198 "engine_counter_start": "counter value",
199 "engine_counter_end": "counter value",
200 "landing_count": "whole number",
201 "passenger_count": "whole number",
202}
205def airframe_type_hints(parsed: ParsedFile, mapping: dict[str, str]) -> dict[str, str]:
206 """{col: hint} where sample data fails to parse as the proposed type."""
207 col_index = {col: i for i, col in enumerate(parsed.norm_cols)}
208 hints: dict[str, str] = {}
209 for col, target in mapping.items():
210 parser = _AIRFRAME_PARSERS.get(target)
211 idx = col_index.get(col)
212 if parser is None or idx is None:
213 continue
214 sample = [
215 row[idx]
216 for row in parsed.data_rows[:_HINT_SAMPLE_ROWS]
217 if idx < len(row) and not _is_blank_cell(row[idx])
218 ]
219 if not sample:
220 continue
221 failed = [v for v in sample if parser(v) is None]
222 if failed:
223 example = str(failed[0])[:30]
224 hints[col] = (
225 f"Sample data doesn't look like a {_TYPE_NAMES[target]} "
226 f"(e.g. {example!r})"
227 )
228 return hints
231def _clean_icao(raw: Any) -> str:
232 """Normalise a place cell to the 4-char ICAO field; ZZZZ when unusable."""
233 val = str(raw).strip().upper() if raw is not None else ""
234 if not val:
235 return "ZZZZ"
236 return val[:4]
239def _is_subtotal(row: list[Any], date_idx: int | None) -> bool:
240 from pilots.logbook_import import (
241 _is_subtotal_row, # pyright: ignore[reportMissingImports]
242 )
244 return _is_subtotal_row(row, date_idx)
247# Paper airframe logbooks routinely leave a counter blank — e.g. flying a
248# leg and back on one continuous engine run only has one landing counter
249# reading in between, so the arrival counter of the first leg and the
250# departure counter of the return leg were never written down. When such a
251# gap is transcribed, it's marked with this literal placeholder rather than
252# left as a truly blank cell (to distinguish "known blank" from "OCR missed
253# it") — treated identically to a blank cell everywhere a cell is read: no
254# value, no parse warning.
255_BLANK_CELL_MARKER = "[empty]"
258def _is_blank_cell(raw: Any) -> bool:
259 if raw is None:
260 return True
261 s = str(raw).strip()
262 return not s or s.casefold() == _BLANK_CELL_MARKER
265def _row_get(row: list[Any], col_index: dict[str, int], col: str) -> Any:
266 i = col_index.get(col)
267 val = row[i] if i is not None and i < len(row) else None
268 return None if _is_blank_cell(val) else val
271def _parse_row_date(
272 row: list[Any], mapping: dict[str, str], col_index: dict[str, int]
273) -> date | None:
274 for col, target in mapping.items():
275 if target == "date":
276 return parse_date_value(_row_get(row, col_index, col))
277 return None
280def _build_airframe_fields(
281 row: list[Any],
282 mapping: dict[str, str],
283 col_index: dict[str, int],
284 date_val: date,
285) -> tuple[dict[str, Any], str | None, list[tuple[str, str, str]]]:
286 """Build Flight field values (+ crew name) for one data row.
288 Returns (fields, crew_name, parse_warnings), where parse_warnings is a
289 list of (col, target, raw_repr) for non-empty cells that couldn't be
290 parsed as their target field's type. Shared by the normal import pass
291 and the near-match conflict finder below, so the two can never compute a
292 row's fields differently.
293 """
294 fields: dict[str, Any] = {"date": date_val}
295 crew_name: str | None = None
296 parse_warnings: list[tuple[str, str, str]] = []
297 for col, target in mapping.items():
298 if target in ("ignore", "date"):
299 continue
300 raw = _row_get(row, col_index, col)
301 if target == "crew_name":
302 crew_name = str(raw).strip() if raw is not None else None
303 crew_name = crew_name or None
304 continue
305 if target in ("departure_icao", "arrival_icao"):
306 fields[target] = _clean_icao(raw)
307 continue
308 parser = _AIRFRAME_PARSERS.get(target)
309 if parser is not None:
310 parsed_val = parser(raw)
311 if parsed_val is None and raw is not None and str(raw).strip():
312 parse_warnings.append((col, target, repr(str(raw)[:40])))
313 fields[target] = parsed_val
314 else: # nature_of_flight, notes — free text
315 val = str(raw).strip() if raw is not None else None
316 fields[target] = val or None
317 return fields, crew_name, parse_warnings
320def _fields_to_flight_entry_kwargs(fields: dict[str, Any]) -> dict[str, Any]:
321 """Map parsed row *fields* (mapping-target-field keys) to Flight
322 constructor kwargs (model-column keys) — the two differ for the counter
323 fields (flight_counter_end → flight_time_counter_end etc). Shared by the
324 normal insert path and the review step's overwrite/new-entry handling.
326 Only ever touches the airframe-facing takeoff_time/landing_time pair —
327 never departure_time/arrival_time, which is pilot-log-facing (fed by
328 pilots/logbook_import.py instead) and must not be clobbered when this
329 row is merged onto an existing placeholder created from that side."""
330 return {
331 "date": fields["date"],
332 "departure_icao": fields.get("departure_icao") or "ZZZZ",
333 "arrival_icao": fields.get("arrival_icao") or "ZZZZ",
334 "takeoff_time": fields.get("takeoff_time"),
335 "landing_time": fields.get("landing_time"),
336 "flight_time": fields.get("flight_time"),
337 "flight_time_counter_start": fields.get("flight_counter_start"),
338 "flight_time_counter_end": fields.get("flight_counter_end"),
339 "engine_time_counter_start": fields.get("engine_counter_start"),
340 "engine_time_counter_end": fields.get("engine_counter_end"),
341 "landing_count": fields.get("landing_count"),
342 "passenger_count": fields.get("passenger_count"),
343 "nature_of_flight": fields.get("nature_of_flight"),
344 "notes": fields.get("notes"),
345 }
348def execute_airframe_import(
349 parsed: ParsedFile,
350 mapping: dict[str, str],
351 aircraft: Any,
352 batch_id: int,
353 opening_counters: dict[str, float | None] | None = None,
354 skip_row_nums: set[int] | None = None,
355) -> AirframeImportResult:
356 """Create Flight rows from *parsed* using *mapping*.
358 Rows are added to db.session but NOT committed — the caller commits after
359 updating the batch record. Counter continuity is checked in date order
360 against the previous imported row (and the opening counters, if given),
361 producing warnings rather than errors. *skip_row_nums* lets the caller
362 carve out rows it's handling separately — near-match conflicts routed to
363 the interactive review step in app/flights/routes.py — so they're
364 excluded entirely from this pass.
365 """
366 from models import Flight, db # pyright: ignore[reportMissingImports]
368 result = AirframeImportResult()
369 col_index = {col: i for i, col in enumerate(parsed.norm_cols)}
370 date_idx = next(
371 (col_index[c] for c, t in mapping.items() if t == "date" and c in col_index),
372 None,
373 )
374 skip_row_nums = skip_row_nums or set()
376 existing_keys = _fetch_existing_dedup_keys(aircraft.id)
377 rows: list[tuple[int, dict[str, Any], str | None]] = []
378 for row_num, row in enumerate(parsed.data_rows, start=1):
379 if row_num in skip_row_nums:
380 continue
381 if _is_subtotal(row, date_idx):
382 result.subtotals += 1
383 continue
385 date_val = _parse_row_date(row, mapping, col_index)
386 if date_val is None:
387 raw_date = (
388 row[date_idx] if date_idx is not None and date_idx < len(row) else None
389 )
390 result.skipped.append((row_num, f"unparseable date: {raw_date!r}"))
391 continue
393 fields, crew_name, parse_warnings = _build_airframe_fields(
394 row, mapping, col_index, date_val
395 )
396 for col, target, raw_repr in parse_warnings:
397 result.parse_warnings.append((row_num, col, target, raw_repr))
399 dup_key = _dup_key(fields)
400 if dup_key in existing_keys:
401 result.duplicates.append(
402 (
403 row_num,
404 _(
405 "matches a flight already in this aircraft's log "
406 "(same date, route, duration and landings)"
407 ),
408 )
409 )
410 continue
411 existing_keys.add(dup_key)
413 rows.append((row_num, fields, crew_name))
415 # Continuity checks run in chronological order (file order as tiebreaker).
416 rows.sort(key=lambda item: (item[1]["date"], item[0]))
417 prev_end: dict[str, float | None] = {
418 "flight": (opening_counters or {}).get("flight"),
419 "engine": (opening_counters or {}).get("engine"),
420 }
421 for row_num, fields, _crew in rows:
422 for kind, start_key, end_key in (
423 ("flight", "flight_counter_start", "flight_counter_end"),
424 ("engine", "engine_counter_start", "engine_counter_end"),
425 ):
426 start = fields.get(start_key)
427 prev = prev_end[kind]
428 if (
429 start is not None
430 and prev is not None
431 and abs(start - prev) > _COUNTER_TOLERANCE
432 ):
433 result.continuity_warnings.append((row_num, kind, prev, start))
434 if fields.get(end_key) is not None:
435 prev_end[kind] = fields[end_key]
437 earliest = rows[0][1]["date"] if rows else date.today()
438 if opening_counters and any(v is not None for v in opening_counters.values()):
439 # Baseline entry seeding the counters: zero-length deltas so hours
440 # statistics are unaffected, dated before the first imported flight.
441 baseline = Flight(
442 aircraft_id=aircraft.id,
443 airframe_import_batch_id=batch_id,
444 source="import",
445 date=earliest - timedelta(days=1),
446 departure_icao="ZZZZ",
447 arrival_icao="ZZZZ",
448 flight_time_counter_start=opening_counters.get("flight"),
449 flight_time_counter_end=opening_counters.get("flight"),
450 engine_time_counter_start=opening_counters.get("engine"),
451 engine_time_counter_end=opening_counters.get("engine"),
452 notes="Opening counters (imported)",
453 )
454 db.session.add(baseline)
455 result.has_opening_counters = True
457 for _row_num, fields, crew_name in rows:
458 fe = Flight(
459 aircraft_id=aircraft.id,
460 airframe_import_batch_id=batch_id,
461 source="import",
462 pic_name=crew_name,
463 **_fields_to_flight_entry_kwargs(fields),
464 )
465 db.session.add(fe)
466 result.imported += 1
468 return result
471# ── Near-match conflict detection (possible corrections) ───────────────────────
473_CANDIDATE_MIN_SCORE = 3
474_CANDIDATE_DURATION_TOLERANCE = 1.0
475_CANDIDATE_COUNTER_TOLERANCE = 0.3
478@dataclass
479class AirframeConflictRow:
480 """A parsed row that isn't an exact duplicate but scores highly enough
481 against one or more existing Flight rows to plausibly be an edited
482 version of one of them — needs a human decision, not a guess."""
484 row_num: int
485 fields: dict[str, Any]
486 crew_name: str | None
487 candidates: list[tuple[float, int]] # (score, existing_flight_id), best first
490def _score_airframe_non_time_signals(fields: dict[str, Any], existing: Any) -> float:
491 """The 5 of 7 _score_airframe_candidate signals that aren't a time of
492 day: departure, arrival, duration, landings, flight counter reading.
493 Factored out so aircraft/gps_import.py's fuzzy match can reuse them with
494 its own time-matching logic (a GPS track's single recorded start/end
495 instant needs a wider comparison than a same-source re-import does —
496 see _score_airframe_candidate below) instead of duplicating this.
497 """
498 score: float = 0.0
500 dep_new = (fields.get("departure_icao") or "ZZZZ").strip().upper()
501 dep_old = (existing.departure_icao or "ZZZZ").strip().upper()
502 if dep_new != "ZZZZ" and dep_old != "ZZZZ" and dep_new == dep_old:
503 score += 1
505 arr_new = (fields.get("arrival_icao") or "ZZZZ").strip().upper()
506 arr_old = (existing.arrival_icao or "ZZZZ").strip().upper()
507 if arr_new != "ZZZZ" and arr_old != "ZZZZ" and arr_new == arr_old:
508 score += 1
510 dur_new = _num(fields.get("flight_time"))
511 dur_old = _num(existing.flight_time)
512 if (
513 dur_new is not None
514 and dur_old is not None
515 and abs(dur_new - dur_old) <= _CANDIDATE_DURATION_TOLERANCE
516 ):
517 score += 1
519 land_new = fields.get("landing_count")
520 land_old = existing.landing_count
521 if land_new is not None and land_old is not None and land_new == land_old:
522 score += 1
524 ctr_new = _num(fields.get("flight_counter_end"))
525 ctr_old = _num(existing.flight_time_counter_end)
526 if (
527 ctr_new is not None
528 and ctr_old is not None
529 and abs(ctr_new - ctr_old) <= _CANDIDATE_COUNTER_TOLERANCE
530 ):
531 score += 1
533 return score
536def _score_airframe_candidate(
537 fields: dict[str, Any], existing: Any, offset_hours: float
538) -> float:
539 """Score how likely *existing* (a Flight row) is the same real-world
540 flight as *fields* (a freshly parsed row), across 7 points: departure,
541 arrival, takeoff time, landing time, duration, landings, flight
542 counter reading. A point only counts toward the score if both sides
543 have meaningful data for it — "ZZZZ" (unmapped ICAO) and missing values
544 are neutral, never a mismatch. The two time points can be fractional —
545 see services.time_band_matching.
546 """
547 from services.time_band_matching import ( # pyright: ignore[reportMissingImports]
548 offset_ring_step_minutes,
549 shift_time,
550 time_band_score,
551 )
553 score = _score_airframe_non_time_signals(fields, existing)
555 # Both same-pair (existing.takeoff_time/landing_time set — likely a
556 # previous airframe import) and cross-pair (existing only has
557 # departure_time/arrival_time — a pilot-import placeholder) use the same
558 # ring width: 1/3 of this aircraft's flight_counter_offset. Pilots log
559 # to a fixed precision (0.1h/6min at the 0.3h default) — a flat few-
560 # minutes tolerance would put an ordinary rounding difference in the
561 # wrong ring, so the same offset-derived step is used for both instead
562 # of a separate hardcoded constant. Cross-pair additionally shifts the
563 # centre by that offset, the closest estimate available of the
564 # block-to-airborne time gap.
565 offset_step = offset_ring_step_minutes(offset_hours)
566 offset_minutes = round(offset_hours * 60)
568 takeoff_new = fields.get("takeoff_time")
569 if existing.takeoff_time is not None:
570 score += time_band_score(takeoff_new, (existing.takeoff_time,), offset_step)
571 elif existing.departure_time is not None:
572 center = shift_time(existing.departure_time, offset_minutes)
573 score += time_band_score(takeoff_new, (center,), offset_step)
575 landing_new = fields.get("landing_time")
576 if existing.landing_time is not None:
577 score += time_band_score(landing_new, (existing.landing_time,), offset_step)
578 elif existing.arrival_time is not None:
579 center = shift_time(existing.arrival_time, -offset_minutes)
580 score += time_band_score(landing_new, (center,), offset_step)
582 return score
585def find_conflicting_airframe_rows(
586 parsed: ParsedFile,
587 mapping: dict[str, str],
588 aircraft_id: int,
589 exclude_row_nums: set[int] | None = None,
590) -> list[AirframeConflictRow]:
591 """Find rows that aren't an exact duplicate but plausibly match an
592 existing Flight row closely enough (score >= _CANDIDATE_MIN_SCORE) to
593 need a human decision: keep the existing entry, overwrite it with the
594 new data, or import as a genuinely separate new flight.
596 Skips subtotal rows, rows with an unparseable date, and exact duplicates
597 (same as execute_airframe_import's own dedup), plus any row_num in
598 *exclude_row_nums* — typically rows already resolved in an earlier pass
599 of this same review.
600 """
601 from models import Aircraft, Flight, db # pyright: ignore[reportMissingImports]
603 exclude_row_nums = exclude_row_nums or set()
604 col_index = {col: i for i, col in enumerate(parsed.norm_cols)}
605 date_idx = next(
606 (col_index[c] for c, t in mapping.items() if t == "date" and c in col_index),
607 None,
608 )
609 aircraft = db.session.get(Aircraft, aircraft_id)
610 offset_hours = float(aircraft.flight_counter_offset) if aircraft else 0.0
611 existing_keys = _fetch_existing_dedup_keys(aircraft_id)
612 conflicts: list[AirframeConflictRow] = []
614 for row_num, row in enumerate(parsed.data_rows, start=1):
615 if row_num in exclude_row_nums:
616 continue
617 if _is_subtotal(row, date_idx):
618 continue
619 date_val = _parse_row_date(row, mapping, col_index)
620 if date_val is None:
621 continue
623 fields, crew_name, _parse_warnings = _build_airframe_fields(
624 row, mapping, col_index, date_val
625 )
626 if _dup_key(fields) in existing_keys:
627 continue # exact duplicate — execute_airframe_import's own dedup handles this
629 same_day = Flight.query.filter_by(aircraft_id=aircraft_id, date=date_val).all()
630 scored = [
631 (score, existing.id)
632 for existing in same_day
633 if (score := _score_airframe_candidate(fields, existing, offset_hours))
634 >= _CANDIDATE_MIN_SCORE
635 ]
636 if scored:
637 scored.sort(key=lambda t: -t[0])
638 conflicts.append(
639 AirframeConflictRow(
640 row_num=row_num,
641 fields=fields,
642 crew_name=crew_name,
643 candidates=scored,
644 )
645 )
647 return conflicts