rgrs.xyz

CAP Last Seen Report

3 min read
code Civil Air Patrol

Generate a "last seen" attendance report in your browser from CAP eServices CSVs — all client‑side, privacy‑friendly, and fast.

CAP Last Seen Report (Web)

I built a small, browser‑based tool to generate a “last seen” attendance report directly from a CAP eServices Post Attendance Report CSV, with optional enrichment from a membership roster CSV. There’s no server and no upload — everything runs locally in your browser for speed and privacy.

Repo: https://github.com/abnormalend/cap-last-seen-report

Features

  • Process CAP Post Attendance Report CSV to compute the most recent date each member (CAPID) was “seen”.
  • Optional Membership CSV enrichment to determine expiration status.
  • Sort by CAPID, Full Name, or Last Seen Date.
  • Export results as CSV or PDF.
  • 100% client‑side: privacy‑friendly and fast.

Quick Start

  • Open web/index.html in your browser (double‑click or use your browser’s Open File dialog).
  • If your browser has strict local file policies, serve it with a simple local server (e.g., python -m http.server).
  • Choose files in the UI:
    • Post Attendance Report CSV (required)
    • Membership CSV (optional)
  • Pick sorting options (default: Last Seen Date, newest first) and click Generate.
  • Export using Download CSV or Download PDF.

Input CSV Formats

Post Attendance Report CSV (required)

Expected headers (case‑sensitive): CAPID, FullName, StartDate, Present, Excused

Accepted StartDate formats include:

  • M/d/yyyy h:mm a (e.g., 10/3/2024 6:00 PM)
  • M/d/yyyy H:mm (e.g., 10/03/2024 18:00)
  • yyyy-MM-dd HH:mm:ss (e.g., 2024-10-03 18:00:00)
  • M/d/yyyy (e.g., 10/03/2024)

A row counts as “seen” if Present == "X" OR Excused == "X".

Membership CSV (optional)

Expected headers (case‑sensitive): CAPID, Expiration1

Accepted Expiration1 formats include:

  • d LLL yyyy (e.g., 31 Aug 2026)
  • d LLLL yyyy (e.g., 31 August 2026)
  • M/d/yyyy (e.g., 08/31/2026)

Expiration logic:

  • If CAPID exists in membership: Expired if Expiration1 < today; show that date (YYYY‑MM‑DD).
  • If CAPID not in membership: show NOT IN ROSTER.
  • If Expiration1 cannot be parsed: treat as not expired (cell empty).

Processing Rules

  • Consider a member “seen” if Present or Excused equals X (case‑insensitive).
  • Only valid CAPIDs and parsable StartDate values are considered.
  • Output is one line per unique CAPID with the latest seen date.
  • When membership CSV is present, a single Expired column is added:
    • Empty = not expired
    • Date = expired on that date
    • NOT IN ROSTER = CAPID missing from membership CSV

Output Columns

Without membership CSV:

  • CAPID, FullName, LastSeenDate

With membership CSV:

  • CAPID, FullName, LastSeenDate, Expired

Dates are ISO‑formatted as YYYY‑MM‑DD.

PDF Export Settings

  • Paper: US Letter
  • Orientation: Portrait
  • Margins: 0.5”
  • Title: 18 pt; Header: 11 pt; Body: 10 pt

Tech Stack

  • Papa Parse — robust CSV parsing
  • Luxon — date parsing/formatting
  • jsPDF + AutoTable — PDF generation

Privacy & Security

All processing happens locally in your browser. No data is uploaded to any server.

Troubleshooting

  • Nothing happens on Generate:
    • Make sure the Post Attendance Report CSV is selected and contains required headers.
    • Check the browser console for errors.
  • Some rows are missing:
    • Only rows with a valid CAPID and a parsable StartDate are considered.
    • Only rows where Present or Excused is X are counted as “seen”.
  • Expired column shows NOT IN ROSTER:
    • The CAPID isn’t present in your Membership CSV or headers don’t match.
  • Dates not parsed:
    • Confirm formats match one of the accepted patterns above; trim whitespace.
  • PDF looks cramped:
    • Try a different sort or export CSV and adjust column widths in a spreadsheet.