Upload Spreadsheet
        │
        ▼
Read Workbook
        │
        ▼
Import Records
        │
        ▼
Validate Records
        │
        ▼
Display Errors
        │
        ▼
Transform Records
        │
        ▼
Persist Data
        │
        ▼
Generate Payroll Summary
        │
        ▼
Export Excel
        │
        ▼
Download File

Transportation Management System — Blazor Payroll Processor

A Blazor Server rebuild of a legacy ASP.NET MVC payroll processing application, built for a real paratransit/mobility transport client. Takes a raw driver trip-log Excel export, validates it, calculates payable time per driver per day, and produces a payroll-ready Excel export — with no database layer.

Why this exists

The original MVC application persisted trip data to SQL Server purely as an intermediate step in a process that is, in practice, used as a one-shot pipeline: upload a file, get back a summarized export. This rebuild eliminates that database dependency entirely, replacing it with an in-memory processing pipeline, while also correcting several accuracy issues discovered in the legacy system's payroll calculations (see Notable Findings below).

Architecture

The solution follows a clean architecture / dependency-inversion structure:

TransportationManagementSystem.Blazor.Core           → interfaces, domain models, DTOs, pure business logic (no external dependencies)
TransportationManagementSystem.Blazor.Infrastructure  → ClosedXML-based Excel import/export implementations
TransportationManagementSystem.Blazor.Web             → Blazor Server UI, dependency injection wiring
TransportationManagementSystem.Blazor.Tests           → xUnit test project

Dependency direction: .Web and .Infrastructure both depend on .Core. .Core depends on nothing — it contains only plain C# (TimeSpan, LINQ, collections), which keeps the business logic fully unit-testable without any framework or file-format dependencies.

The Pipeline

Every uploaded file flows through five stages, orchestrated by FileProcessingService:

1. Import (IFileImportService, implemented in .Infrastructure via ClosedXML) — reads the raw Excel file into a flat list of TripImportRow DTOs. Purely mechanical: no interpretation of the data.

2. Aggregate (IAggregationService, .Core) — groups flat rows by (Driver, RideDate) into DriverDay objects, each containing a list of TripSegments. Also detects no-show trips (rows where ActualPickupTime and ActualDropoffTime are both zero) and flags them so they're excluded from pay.

3. Validate (IValidationService, .Core) — runs sanity checks on the aggregated data before any payroll math happens:

   • All trips for a day marked no-show (unusual — flagged for review)
   • Clock-out time occurring at or before the driver's first actual pickup (a known historical data-entry failure mode)
   • Clock-out at or before clock-in
   • Dropoff at or before pickup, for real (non-no-show) trips

   If validation fails, the pipeline stops — no summary or export is produced from unvalidated data.

4. Summarize (ISummaryService, .Core) — calculates, per driver-day:

   • Start: later of (actual clock-in, first scheduled pickup − 30 min)
   • End: earlier of (actual clock-out, last dropoff + 30 min)
   • Breaks: overlapping trips (multiple simultaneous riders) are merged into continuous "busy" blocks; the gap between one block's last dropoff and the next block's adjusted arrival time (MAX(scheduled, arrival) − 30 min) becomes an unpaid break — no minimum threshold
   • Paid Time: total span minus all unpaid breaks
   • Weekly Time: running total per driver, per week, resetting automatically per (Driver, Week) key

5. Export (IExportService, .Infrastructure via ClosedXML) — writes a payroll-ready .xlsx file matching the legacy system's column layout (Driver, Date, Week, Start, Out1–4/In1–4, End, Paid Time, Weekly Time), returned as bytes and delivered to the browser as a direct download (no server-side file storage).

Notable Findings

This rebuild surfaced two real defects in the legacy production system:

• Independent per-column sorting bug: the old system sorted each time-value column (pickup, dropoff, etc.) independently before pairing them into "breaks," which could desynchronize rows and silently miss real unpaid gaps.
• Missed break detection: verified against a real driver's narrated timesheet, the legacy output was found to omit at least one legitimate unpaid break per day in some cases, meaning the old system had likely been over-paying drivers for years.

All business rules in this rebuild were derived and verified directly against real production data and cross-checked with actual driver activity narratives, not just ported from legacy code.

Tech Stack

• .NET 10 / Blazor Server (Interactive Server render mode)
• ClosedXML (MIT licensed — chosen over EPPlus for its commercial-friendly license)
• Bootstrap 5
• xUnit (planned/in progress for regression coverage of validation and payroll-calculation edge cases)

Running Locally

1. Clone the repository and open the solution in Visual Studio or Rider.
2. Restore NuGet packages (ClosedXML is required in .Infrastructure).
3. Set TransportationManagementSystem.Blazor.Web as the startup project.
4. Run — navigate to /FileImport to upload a trip-log .xlsx file.

Known Limitations

• Export supports a fixed maximum of 4 breaks per driver-day, matching the legacy system's column layout. No production data has ever exceeded this in practice.
• No persistence layer — this is intentionally a single-session, upload-to-download tool, not a system of record.