From a304c24b32c0327436bbd8c2853e60668e161b42 Mon Sep 17 00:00:00 2001 From: Ariel Costas Guerrero Date: Mon, 29 Dec 2025 00:41:52 +0100 Subject: Rename a lot of stuff, add Santiago real time --- src/Enmarcha.Experimental.ServiceViewer/.gitignore | 264 ++++++++++ .../AppDbContextDesignTimeFactory.cs | 41 ++ .../Controllers/ServicesController.cs | 358 ++++++++++++++ .../Controllers/StylesheetController.cs | 39 ++ .../Data/AppDbContext.cs | 59 +++ .../Data/Extensions/TimeExtensions.cs | 20 + .../Data/Gtfs/Enums/DirectionId.cs | 7 + .../Data/Gtfs/Enums/ExceptionType.cs | 7 + .../Data/Gtfs/Enums/RouteType.cs | 15 + .../Data/Gtfs/Enums/TripBikesAllowed.cs | 8 + .../Data/Gtfs/Enums/TripWheelchairAccessible.cs | 8 + .../Data/Gtfs/Enums/WheelchairBoarding.cs | 8 + .../Data/Gtfs/Feed.cs | 21 + .../Data/Gtfs/GtfsAgency.cs | 46 ++ .../Data/Gtfs/GtfsCalendar.cs | 45 ++ .../Data/Gtfs/GtfsCalendarDate.cs | 24 + .../Data/Gtfs/GtfsRoute.cs | 70 +++ .../Data/Gtfs/GtfsStop.cs | 51 ++ .../Data/Gtfs/GtfsStopTime.cs | 44 ++ .../Data/Gtfs/GtfsTrip.cs | 57 +++ .../Migrations/20251211153852_Initial.Designer.cs | 547 +++++++++++++++++++++ .../Data/Migrations/20251211153852_Initial.cs | 318 ++++++++++++ .../Data/Migrations/AppDbContextModelSnapshot.cs | 544 ++++++++++++++++++++ .../QueryExtensions/GtfsCalendarQueryExtensions.cs | 21 + .../Enmarcha.Experimental.ServiceViewer.csproj | 17 + src/Enmarcha.Experimental.ServiceViewer/Program.cs | 35 ++ .../Properties/launchSettings.json | 14 + .../Views/Services/DaysInFeed.cshtml | 22 + .../Views/Services/DaysInFeed.cshtml.cs | 7 + .../Views/Services/ServiceDetails.cshtml | 63 +++ .../Views/Services/ServiceDetails.cshtml.cs | 29 ++ .../Views/Services/ServicesInDay.cshtml | 40 ++ .../Views/Services/ServicesInDay.cshtml.cs | 39 ++ .../Views/Shared/_Layout.cshtml | 22 + .../Views/_ViewImports.cshtml | 2 + .../Views/_ViewStart.cshtml | 3 + .../appsettings.json | 9 + .../wwwroot/styles/common.css | 23 + .../wwwroot/styles/days_in_feed.css | 28 ++ .../wwwroot/styles/service_details.css | 146 ++++++ .../wwwroot/styles/services_in_day.css | 70 +++ 41 files changed, 3191 insertions(+) create mode 100644 src/Enmarcha.Experimental.ServiceViewer/.gitignore create mode 100644 src/Enmarcha.Experimental.ServiceViewer/AppDbContextDesignTimeFactory.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Controllers/ServicesController.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Controllers/StylesheetController.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/AppDbContext.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Extensions/TimeExtensions.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/DirectionId.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/ExceptionType.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/RouteType.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/TripBikesAllowed.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/TripWheelchairAccessible.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/WheelchairBoarding.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Feed.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsAgency.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsCalendar.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsCalendarDate.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsRoute.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsStop.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsStopTime.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsTrip.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/20251211153852_Initial.Designer.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/20251211153852_Initial.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/AppDbContextModelSnapshot.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Data/QueryExtensions/GtfsCalendarQueryExtensions.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Enmarcha.Experimental.ServiceViewer.csproj create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Program.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Properties/launchSettings.json create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Views/Services/DaysInFeed.cshtml create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Views/Services/DaysInFeed.cshtml.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Views/Services/ServiceDetails.cshtml create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Views/Services/ServiceDetails.cshtml.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Views/Services/ServicesInDay.cshtml create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Views/Services/ServicesInDay.cshtml.cs create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Views/Shared/_Layout.cshtml create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Views/_ViewImports.cshtml create mode 100644 src/Enmarcha.Experimental.ServiceViewer/Views/_ViewStart.cshtml create mode 100644 src/Enmarcha.Experimental.ServiceViewer/appsettings.json create mode 100644 src/Enmarcha.Experimental.ServiceViewer/wwwroot/styles/common.css create mode 100644 src/Enmarcha.Experimental.ServiceViewer/wwwroot/styles/days_in_feed.css create mode 100644 src/Enmarcha.Experimental.ServiceViewer/wwwroot/styles/service_details.css create mode 100644 src/Enmarcha.Experimental.ServiceViewer/wwwroot/styles/services_in_day.css (limited to 'src/Enmarcha.Experimental.ServiceViewer') diff --git a/src/Enmarcha.Experimental.ServiceViewer/.gitignore b/src/Enmarcha.Experimental.ServiceViewer/.gitignore new file mode 100644 index 0000000..ff5b00c --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/.gitignore @@ -0,0 +1,264 @@ +## Ignore Visual Studio temporary files, build results, and +## files generated by popular Visual Studio add-ons. + +# Azure Functions localsettings file +local.settings.json + +# User-specific files +*.suo +*.user +*.userosscache +*.sln.docstates + +# User-specific files (MonoDevelop/Xamarin Studio) +*.userprefs + +# Build results +[Dd]ebug/ +[Dd]ebugPublic/ +[Rr]elease/ +[Rr]eleases/ +x64/ +x86/ +bld/ +[Bb]in/ +[Oo]bj/ +[Ll]og/ + +# Visual Studio 2015 cache/options directory +.vs/ +# Uncomment if you have tasks that create the project's static files in wwwroot +#wwwroot/ + +# MSTest test Results +[Tt]est[Rr]esult*/ +[Bb]uild[Ll]og.* + +# NUNIT +*.VisualState.xml +TestResult.xml + +# Build Results of an ATL Project +[Dd]ebugPS/ +[Rr]eleasePS/ +dlldata.c + +# DNX +project.lock.json +project.fragment.lock.json +artifacts/ + +*_i.c +*_p.c +*_i.h +*.ilk +*.meta +*.obj +*.pch +*.pdb +*.pgc +*.pgd +*.rsp +*.sbr +*.tlb +*.tli +*.tlh +*.tmp +*.tmp_proj +*.log +*.vspscc +*.vssscc +.builds +*.pidb +*.svclog +*.scc + +# Chutzpah Test files +_Chutzpah* + +# Visual C++ cache files +ipch/ +*.aps +*.ncb +*.opendb +*.opensdf +*.sdf +*.cachefile +*.VC.db +*.VC.VC.opendb + +# Visual Studio profiler +*.psess +*.vsp +*.vspx +*.sap + +# TFS 2012 Local Workspace +$tf/ + +# Guidance Automation Toolkit +*.gpState + +# ReSharper is a .NET coding add-in +_ReSharper*/ +*.[Rr]e[Ss]harper +*.DotSettings.user + +# JustCode is a .NET coding add-in +.JustCode + +# TeamCity is a build add-in +_TeamCity* + +# DotCover is a Code Coverage Tool +*.dotCover + +# NCrunch +_NCrunch_* +.*crunch*.local.xml +nCrunchTemp_* + +# MightyMoose +*.mm.* +AutoTest.Net/ + +# Web workbench (sass) +.sass-cache/ + +# Installshield output folder +[Ee]xpress/ + +# DocProject is a documentation generator add-in +DocProject/buildhelp/ +DocProject/Help/*.HxT +DocProject/Help/*.HxC +DocProject/Help/*.hhc +DocProject/Help/*.hhk +DocProject/Help/*.hhp +DocProject/Help/Html2 +DocProject/Help/html + +# Click-Once directory +publish/ + +# Publish Web Output +*.[Pp]ublish.xml +*.azurePubxml +# TODO: Comment the next line if you want to checkin your web deploy settings +# but database connection strings (with potential passwords) will be unencrypted +#*.pubxml +*.publishproj + +# Microsoft Azure Web App publish settings. Comment the next line if you want to +# checkin your Azure Web App publish settings, but sensitive information contained +# in these scripts will be unencrypted +PublishScripts/ + +# NuGet Packages +*.nupkg +# The packages folder can be ignored because of Package Restore +**/packages/* +# except build/, which is used as an MSBuild target. +!**/packages/build/ +# Uncomment if necessary however generally it will be regenerated when needed +#!**/packages/repositories.config +# NuGet v3's project.json files produces more ignoreable files +*.nuget.props +*.nuget.targets + +# Microsoft Azure Build Output +csx/ +*.build.csdef + +# Microsoft Azure Emulator +ecf/ +rcf/ + +# Windows Store app package directories and files +AppPackages/ +BundleArtifacts/ +Package.StoreAssociation.xml +_pkginfo.txt + +# Visual Studio cache files +# files ending in .cache can be ignored +*.[Cc]ache +# but keep track of directories ending in .cache +!*.[Cc]ache/ + +# Others +ClientBin/ +~$* +*~ +*.dbmdl +*.dbproj.schemaview +*.jfm +*.pfx +*.publishsettings +node_modules/ +orleans.codegen.cs + +# Since there are multiple workflows, uncomment next line to ignore bower_components +# (https://github.com/github/gitignore/pull/1529#issuecomment-104372622) +#bower_components/ + +# RIA/Silverlight projects +Generated_Code/ + +# Backup & report files from converting an old project file +# to a newer Visual Studio version. Backup files are not needed, +# because we have git ;-) +_UpgradeReport_Files/ +Backup*/ +UpgradeLog*.XML +UpgradeLog*.htm + +# SQL Server files +*.mdf +*.ldf + +# Business Intelligence projects +*.rdl.data +*.bim.layout +*.bim_*.settings + +# Microsoft Fakes +FakesAssemblies/ + +# GhostDoc plugin setting file +*.GhostDoc.xml + +# Node.js Tools for Visual Studio +.ntvs_analysis.dat + +# Visual Studio 6 build log +*.plg + +# Visual Studio 6 workspace options file +*.opt + +# Visual Studio LightSwitch build output +**/*.HTMLClient/GeneratedArtifacts +**/*.DesktopClient/GeneratedArtifacts +**/*.DesktopClient/ModelManifest.xml +**/*.Server/GeneratedArtifacts +**/*.Server/ModelManifest.xml +_Pvt_Extensions + +# Paket dependency manager +.paket/paket.exe +paket-files/ + +# FAKE - F# Make +.fake/ + +# JetBrains Rider +.idea/ +*.sln.iml + +# CodeRush +.cr/ + +# Python Tools for Visual Studio (PTVS) +__pycache__/ +*.pyc \ No newline at end of file diff --git a/src/Enmarcha.Experimental.ServiceViewer/AppDbContextDesignTimeFactory.cs b/src/Enmarcha.Experimental.ServiceViewer/AppDbContextDesignTimeFactory.cs new file mode 100644 index 0000000..4dcd320 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/AppDbContextDesignTimeFactory.cs @@ -0,0 +1,41 @@ +using Enmarcha.Experimental.ServiceViewer.Data; +using Microsoft.EntityFrameworkCore; +using Microsoft.EntityFrameworkCore.Design; + +namespace Enmarcha.Experimental.ServiceViewer; + +public class AppDbContextDesignTimeFactory : IDesignTimeDbContextFactory +{ + public AppDbContext CreateDbContext(string[] args) + { + IConfigurationRoot configuration = new ConfigurationBuilder() + .AddJsonFile("appsettings.json", optional: true) + .AddJsonFile($"appsettings.{Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT")}.json", + optional: true) + .AddUserSecrets(typeof(AppDbContext).Assembly, optional: true) + .AddEnvironmentVariables() + .Build(); + + var builder = new DbContextOptionsBuilder(); + var connectionString = configuration.GetConnectionString("Database"); + if (string.IsNullOrEmpty(connectionString)) + { + throw new InvalidOperationException("Connection string 'Database' not found."); + } + + var loggerFactory = LoggerFactory.Create(lb => + { + lb + .AddConsole() + .SetMinimumLevel(LogLevel.Information); + }); + builder.UseLoggerFactory(loggerFactory); + + builder.UseNpgsql( + connectionString, + options => options.UseNetTopologySuite() + ); + + return new AppDbContext(builder.Options); + } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Controllers/ServicesController.cs b/src/Enmarcha.Experimental.ServiceViewer/Controllers/ServicesController.cs new file mode 100644 index 0000000..1da38aa --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Controllers/ServicesController.cs @@ -0,0 +1,358 @@ +using Enmarcha.Experimental.ServiceViewer.Data; +using Enmarcha.Experimental.ServiceViewer.Data.Gtfs; +using Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; +using Enmarcha.Experimental.ServiceViewer.Data.QueryExtensions; +using Enmarcha.Experimental.ServiceViewer.Views.Services; +using Microsoft.AspNetCore.Mvc; +using Microsoft.EntityFrameworkCore; + +namespace Enmarcha.Experimental.ServiceViewer.Controllers; + +[Route("")] +public class ServicesController : Controller +{ + private readonly AppDbContext _db; + + public ServicesController(AppDbContext db) + { + _db = db; + } + + [HttpGet("")] + public async Task DaysInFeed() + { + // FIXME: Use calendar too, but it requires getting the feed information + var days = await _db.CalendarDates + .Where(cd => cd.ExceptionType == ExceptionType.Added) + .Select(cd => cd.Date) + .Distinct() + .OrderBy(d => d) + .ToListAsync(); + + var model = new DaysInFeedModel + { + Days = days, + Today = DateOnly.FromDateTime(DateTime.Now), + }; + + return View(model); + } + + [HttpGet("{day}")] + public IActionResult ServicesInDay( + [FromRoute] string day + ) + { + var dateParsed = DateOnly.TryParseExact(day, "yyyy-MM-dd", out var dateOnly); + if (!dateParsed) + { + return BadRequest("Invalid date format. Please use 'yyyy-MM-dd'."); + } + + var dateTime = dateOnly.ToDateTime(TimeOnly.MinValue); + + // 1. Get all the calendars running that day + var dayOfWeek = dateOnly.DayOfWeek; + + var calendars = _db.Calendars + .WhereDayOfWeek(dayOfWeek) + .ToList(); + + var calendarDates = _db.CalendarDates + .Where(cd => cd.Date.Date == dateTime.Date) + .ToList(); + + // 2. Combine the two lists + HashSet activeServiceIds = []; + foreach (var calendar in calendars) + { + if (calendarDates.All(cd => + cd.ServiceId != calendar.ServiceId || cd.ExceptionType != ExceptionType.Removed)) + { + activeServiceIds.Add(calendar.ServiceId); + } + } + + foreach (var calendarDate in calendarDates.Where(cd => cd.ExceptionType == ExceptionType.Added)) + { + activeServiceIds.Add(calendarDate.ServiceId); + } + + // 3. Get the trips for those services + var tripsByService = _db.Trips + .AsSplitQuery() + .Include(t => t.Route) + .Where(t => activeServiceIds.Contains(t.ServiceId)) + .GroupBy(t => t.ServiceId) + .ToDictionary(g => g.Key, g => g.ToList()); + + /* + * For each shift, we extract the trip sequence number from the trip_id, order them ascending and take first + * one's first stop_time departure_time as shift start time and last one's last stop_time arrival_time as shift end time + * FIXME: Heuristic only for Vitrasa, not other feeds + * A 01LP001_008001_2, A 01LP001_008001_3, A 01LP001_008001_4, A 01LP001_008001_5... + */ + List serviceInformations = []; + List tripsWhoseFirstStopWeWant = []; + List tripsWhoseLastStopWeWant = []; + foreach (var (service, trips) in tripsByService) + { + var orderedTrips = trips + .Select(t => new + { + Trip = t, + Sequence = int.TryParse(t.Id.Split('_').LastOrDefault(), out var seq) ? seq : int.MaxValue + }) + .OrderBy(t => t.Sequence) + .ThenBy(t => t.Trip.TripHeadsign) // Secondary sort to ensure consistent ordering + .Select(t => t.Trip) + .ToList(); + + if (orderedTrips.Count == 0) + { + continue; + } + + tripsWhoseFirstStopWeWant.Add(orderedTrips.First().Id); + tripsWhoseLastStopWeWant.Add(orderedTrips.Last().Id); + serviceInformations.Add(new ServiceInformation( + service, + GetNameForServiceId(service), + orderedTrips, + orderedTrips.First(), + orderedTrips.Last() + )); + } + + var firstStopTimePerTrip = _db.StopTimes + .AsSplitQuery().AsNoTracking() + .Where(st => tripsWhoseFirstStopWeWant.Contains(st.TripId)) + .OrderBy(st => st.StopSequence) + .GroupBy(st => st.TripId) + .Select(g => g.First()) + .ToDictionary(st => st.TripId, st => st.Departure); + + var lastStopTimePerTrip = _db.StopTimes + .AsSplitQuery().AsNoTracking() + .Where(st => tripsWhoseLastStopWeWant.Contains(st.TripId)) + .OrderByDescending(st => st.StopSequence) + .GroupBy(st => st.TripId) + .Select(g => g.First()) + .ToDictionary(st => st.TripId, st => st.Arrival); + + // 4. Create a view model + List serviceCards = []; + foreach (var serviceInfo in serviceInformations) + { + // For lines 16-24 switching during the day we want (16, 2), (24,2), (16,1), (24,2)... in sequence + // TODO: Fix getting the trip sequence for any operator + var tripsOrdered = serviceInfo.Trips + .OrderBy(t => int.Parse(t.Id.Split('_').LastOrDefault() ?? string.Empty)) + .ToList(); + + List tripGroups = []; + GtfsRoute currentRoute = tripsOrdered.First().Route; + int currentRouteCount = 1; + foreach (var trip in tripsOrdered.Skip(1)) + { + if (trip.Route.Id == currentRoute.Id) + { + currentRouteCount++; + } + else + { + tripGroups.Add(new TripGroup(currentRoute, currentRouteCount)); + currentRoute = trip.Route; + currentRouteCount = 1; + } + } + + tripGroups.Add(new TripGroup(currentRoute, currentRouteCount)); + + serviceCards.Add(new ServicesInDayItem( + serviceInfo.ServiceId, + serviceInfo.ServiceName, + serviceInfo.Trips, + tripGroups, + firstStopTimePerTrip.TryGetValue(serviceInfo.FirstTrip.Id, out var shiftStart) + ? shiftStart + : string.Empty, + lastStopTimePerTrip.TryGetValue(serviceInfo.LastTrip.Id, out var shiftEnd) ? shiftEnd : string.Empty + )); + } + + return View(new ServiceInDayModel + { + Items = serviceCards, + Date = dateOnly + }); + } + + [HttpGet("{day}/{serviceId}")] + public IActionResult ServiceDetails( + [FromRoute] string day, + [FromRoute] string serviceId + ) + { + #region Validation + + var dateParsed = DateOnly.TryParseExact(day, "yyyy-MM-dd", out var dateOnly); + if (!dateParsed) + { + return BadRequest("Invalid date format. Please use 'yyyy-MM-dd'."); + } + + var dateTime = dateOnly.ToDateTime(TimeOnly.MinValue); + + // 1. Get all the calendars running that day + var dayOfWeek = dateOnly.DayOfWeek; + + var calendars = _db.Calendars + .WhereDayOfWeek(dayOfWeek) + .ToList(); + + var calendarDates = _db.CalendarDates + .Where(cd => cd.Date.Date == dateTime.Date) + .ToList(); + + // 2. Combine the two lists + HashSet activeServiceIds = []; + foreach (var calendar in calendars) + { + if (calendarDates.All(cd => + cd.ServiceId != calendar.ServiceId || cd.ExceptionType != ExceptionType.Removed)) + { + activeServiceIds.Add(calendar.ServiceId); + } + } + + foreach (var calendarDate in calendarDates.Where(cd => cd.ExceptionType == ExceptionType.Added)) + { + activeServiceIds.Add(calendarDate.ServiceId); + } + + if (!activeServiceIds.Contains(serviceId)) + { + return NotFound("Service not found for the given day."); + } + + #endregion + + var trips = _db.Trips + .AsSplitQuery() + .Include(t => t.Route) + .Where(t => t.ServiceId == serviceId) + .ToList(); + + var orderedTrips = trips + .Select(t => new + { + Trip = t, + Sequence = int.TryParse(t.Id.Split('_').LastOrDefault(), out var seq) ? seq : int.MaxValue + }) + .OrderBy(t => t.Sequence) + .ThenBy(t => t.Trip.TripHeadsign) // Secondary sort to ensure consistent ordering + .Select(t => t.Trip) + .ToList(); + + List items = []; + int totalDistance = 0; + TimeSpan totalDrivingMinutes = TimeSpan.Zero; + foreach (var trip in orderedTrips) + { + var stopTimes = _db.StopTimes + .AsSplitQuery().AsNoTracking() + .Include(gtfsStopTime => gtfsStopTime.GtfsStop) + .Where(st => st.TripId == trip.Id) + .OrderBy(st => st.StopSequence) + .ToList(); + + if (stopTimes.Count == 0) + { + continue; + } + + var firstStop = stopTimes.First(); + var lastStop = stopTimes.Last(); + + var tripDistance = (int?)(lastStop.ShapeDistTraveled - firstStop.ShapeDistTraveled); + totalDistance += tripDistance ?? 0; + totalDrivingMinutes += (lastStop.ArrivalTime - firstStop.DepartureTime); + + items.Add(new ServiceDetailsItem + { + TripId = trip.Id, + SafeRouteId = trip.Route.SafeId, + ShortName = trip.Route.ShortName, + LongName = trip.TripHeadsign ?? trip.Route.LongName, + TotalDistance = tripDistance.HasValue ? $"{tripDistance.Value/1_000:F2} km" : "N/A", + FirstStopName = firstStop.GtfsStop.Name, + FirstStopTime = firstStop.Departure, + LastStopName = lastStop.GtfsStop.Name, + LastStopTime = lastStop.Arrival + }); + } + + return View(new ServiceDetailsModel + { + Date = dateOnly, + ServiceId = serviceId, + ServiceName = GetNameForServiceId(serviceId), + TotalDrivingTime = totalDrivingMinutes, + TotalDistance = totalDistance, + Items = items + }); + } + + private string GetNameForServiceId(string serviceId) + { + var serviceIndicator = serviceId[^6..]; // "008001" or "202006" + if (string.IsNullOrEmpty(serviceIndicator)) + { + return serviceId; + } + + var lineNumber = int.Parse(serviceIndicator[..3]); // "008" + var shiftNumber = int.Parse(serviceIndicator[3..]); // "001" + var lineName = lineNumber switch + { + 1 => "C1", + 3 => "C3", + 30 => "N1", + 33 => "N4", + 8 => "A", + 101 => "H", + 201 => "U1", + 202 => "U2", + 150 => "REF", + 500 => "TUR", + _ => $"L{lineNumber}" + }; + + return $"Servicio {lineName}-{shiftNumber}º ({serviceId[^6..]})"; + } +} + +internal class ServiceInformation +{ + internal string ServiceId { get; } + public string ServiceName { get; set; } + internal List Trips { get; } + internal GtfsTrip FirstTrip { get; } + internal GtfsTrip LastTrip { get; } + + internal ServiceInformation( + string serviceId, + string serviceName, + List trips, + GtfsTrip firstTrip, + GtfsTrip lastTrip + ) + { + ServiceId = serviceId; + ServiceName = serviceName; + Trips = trips; + FirstTrip = firstTrip; + LastTrip = lastTrip; + } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Controllers/StylesheetController.cs b/src/Enmarcha.Experimental.ServiceViewer/Controllers/StylesheetController.cs new file mode 100644 index 0000000..7c0a9c2 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Controllers/StylesheetController.cs @@ -0,0 +1,39 @@ +using System.Text; +using Enmarcha.Experimental.ServiceViewer.Data; +using Microsoft.AspNetCore.Mvc; +using Microsoft.EntityFrameworkCore; + +namespace Enmarcha.Experimental.ServiceViewer.Controllers; + +[Controller] +[Route("")] +public class StylesheetController : Controller +{ + private readonly AppDbContext _db; + public StylesheetController(AppDbContext db) + { + _db = db; + } + + [HttpGet("stylesheets/routecolours.css")] + public IActionResult GetRouteColoursSheet() + { + var routeColours = _db.Routes + .Select(r => new { Id = r.SafeId, r.Color, r.TextColor }) + .ToListAsync(); + + StringBuilder sb = new(); + foreach (var route in routeColours.Result) + { + sb.Append($".route-{route.Id} {{"); + sb.Append($"--route-color: #{route.Color};"); + sb.Append($"--route-text: #{route.TextColor};"); + sb.Append($"--route-color-semi: #{route.Color}4d;"); + sb.Append($"--route-text-semi: #{route.TextColor}4d;"); + sb.Append('}'); + } + sb.Append('}'); + + return Content(sb.ToString(), "text/css", Encoding.UTF8); + } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/AppDbContext.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/AppDbContext.cs new file mode 100644 index 0000000..bb3c6c8 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/AppDbContext.cs @@ -0,0 +1,59 @@ +using Enmarcha.Experimental.ServiceViewer.Data.Gtfs; +using Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; +using Microsoft.EntityFrameworkCore; + +namespace Enmarcha.Experimental.ServiceViewer.Data; + +public class AppDbContext : DbContext +{ + public AppDbContext(DbContextOptions options) : base(options) + { + } + + protected override void OnModelCreating(ModelBuilder modelBuilder) + { + // Route -> Agency + modelBuilder.Entity() + .HasOne(r => r.Agency) + .WithMany() + .HasForeignKey(r => new { r.AgencyId, r.FeedId }) + .HasPrincipalKey(a => new { a.Id, a.FeedId }); + + // Trip -> Route + modelBuilder.Entity() + .HasOne(t => t.Route) + .WithMany() + .HasForeignKey(t => new { t.RouteId, t.FeedId }) + .HasPrincipalKey(a => new { a.Id, a.FeedId }); + + // Relación StopTimes -> Trip + modelBuilder.Entity() + .HasOne(st => st.GtfsTrip) + .WithMany() + .HasForeignKey(st => new { st.TripId, st.FeedId }) + .HasPrincipalKey(a => new { a.Id, a.FeedId }); + + // Relación StopTimes -> Stop + modelBuilder.Entity() + .HasOne(st => st.GtfsStop) + .WithMany() + .HasForeignKey(st => new { st.StopId, st.FeedId }) + .HasPrincipalKey(a => new { a.Id, a.FeedId }); + + modelBuilder.Entity() + .Property(t => t.TripWheelchairAccessible) + .HasDefaultValue(TripWheelchairAccessible.Empty); + + modelBuilder.Entity() + .Property(t => t.TripBikesAllowed) + .HasDefaultValue(TripBikesAllowed.Empty); + } + + public DbSet Agencies { get; set; } + public DbSet Calendars { get; set; } + public DbSet CalendarDates { get; set; } + public DbSet Routes { get; set; } + public DbSet Stops { get; set; } + public DbSet StopTimes { get; set; } + public DbSet Trips { get; set; } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Extensions/TimeExtensions.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Extensions/TimeExtensions.cs new file mode 100644 index 0000000..5f79b75 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Extensions/TimeExtensions.cs @@ -0,0 +1,20 @@ +namespace Enmarcha.Experimental.ServiceViewer.Data.Extensions; + +public static class TimeExtensions +{ + extension(TimeSpan) { + public static TimeSpan FromGtfsTime(string gtfsTime) + { + var parts = gtfsTime.Split(":", 3); + + var hours = int.Parse(parts[0]); + var minutes = int.Parse(parts[1]); + var seconds = int.Parse(parts[2]); + + int days = hours / 24; + int leftoverHours = hours % 24; + + return new TimeSpan(days, leftoverHours, minutes, seconds); + } + } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/DirectionId.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/DirectionId.cs new file mode 100644 index 0000000..6a41d1a --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/DirectionId.cs @@ -0,0 +1,7 @@ +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; + +public enum DirectionId +{ + Outbound = 0, + Inbound = 1 +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/ExceptionType.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/ExceptionType.cs new file mode 100644 index 0000000..02ab612 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/ExceptionType.cs @@ -0,0 +1,7 @@ +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; + +public enum ExceptionType +{ + Added = 1, + Removed = 2 +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/RouteType.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/RouteType.cs new file mode 100644 index 0000000..e487c32 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/RouteType.cs @@ -0,0 +1,15 @@ +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; + +public enum RouteType +{ + Tram = 0, + Subway = 1, + Rail = 2, + Bus = 3, + Ferry = 4, + CableTram = 5, + AerialLift = 6, + Funicular = 7, + Trolleybus = 11, + Monorail = 12 +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/TripBikesAllowed.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/TripBikesAllowed.cs new file mode 100644 index 0000000..346b93a --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/TripBikesAllowed.cs @@ -0,0 +1,8 @@ +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; + +public enum TripBikesAllowed +{ + Empty = 0, + CanAccommodate = 1, + NotAllowed = 2 +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/TripWheelchairAccessible.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/TripWheelchairAccessible.cs new file mode 100644 index 0000000..6bcdb22 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/TripWheelchairAccessible.cs @@ -0,0 +1,8 @@ +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; + +public enum TripWheelchairAccessible +{ + Empty = 0, + CanAccommodate = 1, + NotAccessible = 2 +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/WheelchairBoarding.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/WheelchairBoarding.cs new file mode 100644 index 0000000..d62f60e --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/WheelchairBoarding.cs @@ -0,0 +1,8 @@ +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; + +public enum WheelchairBoarding +{ + Unknown = 0, + SomeVehicles = 1, + NotPossible = 2 +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Feed.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Feed.cs new file mode 100644 index 0000000..db9b283 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Feed.cs @@ -0,0 +1,21 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; + +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +[Table("feeds")] +public class Feed +{ + /// + /// Auto-incrementing ID value for each feed, to identify it and its version + /// + [Key] + public int Id { get; set; } + + [MaxLength(32)] public string ShortName { get; set; } + [MaxLength(32)] public string LongName { get; set; } + [MaxLength(255)] public string DownloadUrl { get; set; } + [MaxLength(32)] public string Etag { get; set; } + + public DateTime InsertedAt { get; set; } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsAgency.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsAgency.cs new file mode 100644 index 0000000..8ce129c --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsAgency.cs @@ -0,0 +1,46 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Microsoft.EntityFrameworkCore; + +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +[Table("gtfs_agencies")] +[PrimaryKey(nameof(Id), nameof(FeedId))] +public class GtfsAgency +{ + [Key] + [Column("agency_id")] + [MaxLength(255)] + public required string Id { get; set; } + + [Column("feed_id")] public int FeedId { get; set; } + [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; } + + [Column("agency_name")] + [MaxLength(255)] + public string Name { get; set; } = string.Empty; + + [Column("agency_url")] + [MaxLength(255)] + public string Url { get; set; } = string.Empty; + + [Column("agency_timezone")] + [MaxLength(50)] + public string Timezone { get; set; } = string.Empty; + + [Column("agency_lang")] + [MaxLength(5)] + public string Language { get; set; } = string.Empty; + + [Column("agency_phone")] + [MaxLength(30)] + public string? Phone { get; set; } = string.Empty; + + [Column("agency_email")] + [MaxLength(255)] + public string? Email { get; set; } = string.Empty; + + [Column("agency_fare_url")] + [MaxLength(255)] + public string? FareUrl { get; set; } = string.Empty; +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsCalendar.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsCalendar.cs new file mode 100644 index 0000000..bcc7c39 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsCalendar.cs @@ -0,0 +1,45 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Microsoft.EntityFrameworkCore; + +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +[Table("gtfs_calendar")] +[PrimaryKey(nameof(ServiceId), nameof(FeedId))] +public class GtfsCalendar +{ + [Key] + [Column("service_id")] + [MaxLength(32)] + public string ServiceId { get; set; } = null!; + + [Column("feed_id")] public int FeedId { get; set; } + [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; } + + [Column("monday")] + public bool Monday { get; set; } + + [Column("tuesday")] + public bool Tuesday { get; set; } + + [Column("wednesday")] + public bool Wednesday { get; set; } + + [Column("thursday")] + public bool Thursday { get; set; } + + [Column("friday")] + public bool Friday { get; set; } + + [Column("saturday")] + public bool Saturday { get; set; } + + [Column("sunday")] + public bool Sunday { get; set; } + + [Column("start_date")] + public DateOnly StartDate { get; set; } + + [Column("end_date")] + public DateOnly EndDate { get; set; } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsCalendarDate.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsCalendarDate.cs new file mode 100644 index 0000000..e9b5a92 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsCalendarDate.cs @@ -0,0 +1,24 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; +using Microsoft.EntityFrameworkCore; + +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +[Table("gtfs_calendar_dates")] +[PrimaryKey(nameof(ServiceId), nameof(Date), nameof(FeedId))] +public class GtfsCalendarDate +{ + [Column("service_id")] + [MaxLength(32)] + public required string ServiceId { get; set; } + + [Column("date")] + public required DateTime Date { get; set; } + + [Column("feed_id")] public int FeedId { get; set; } + [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; } + + [Column("exception_type")] + public required ExceptionType ExceptionType { get; set; } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsRoute.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsRoute.cs new file mode 100644 index 0000000..80ef38a --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsRoute.cs @@ -0,0 +1,70 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; +using Microsoft.EntityFrameworkCore; + +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +[Table("gtfs_routes")] +[PrimaryKey(nameof(Id), nameof(FeedId))] +public class GtfsRoute +{ + [Column("route_id")] + [MaxLength(255)] + public required string Id { get; set; } + + public string SafeId => Id.Replace(" ", "_").Replace("-", "_"); + + [Column("feed_id")]public int FeedId { get; set; } + [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; } + + [Column("agency_id")] + [ForeignKey(nameof(Agency))] + [MaxLength(255)] + public required string AgencyId { get; set; } + + [ForeignKey(nameof(AgencyId))] + public GtfsAgency Agency { get; set; } = null!; + + /// + /// Short name of a route. Often a short, abstract identifier (e.g., "32", "100X", "Green") + /// that riders use to identify a route. Both route_short_name and route_long_name may be defined. + /// + [Column("route_short_name")] + [MaxLength(32)] + public string ShortName { get; set; } = string.Empty; + + /// + /// Full name of a route. This name is generally more descriptive than the route_short_name and often + /// includes the route's destination or stop. Both route_short_name and route_long_name may be defined. + /// + [Column("route_long_name")] + [MaxLength(255)] + public string LongName { get; set; } = string.Empty; + + [Column("route_desc")] + [MaxLength(255)] + public string? Description { get; set; } = string.Empty; + + [Column("route_type")] + public RouteType Type { get; set; } = RouteType.Bus; + + [Column("route_url")] + [MaxLength(255)] + public string? Url { get; set; } = string.Empty; + + [Column("route_color")] + [MaxLength(7)] + public string? Color { get; set; } = string.Empty; + + [Column("route_text_color")] + [MaxLength(7)] + public string? TextColor { get; set; } = string.Empty; + + /// + /// Orders the routes in a way which is ideal for presentation to customers. + /// Routes with smaller route_sort_order values should be displayed first. + /// + [Column("route_sort_order")] + public int SortOrder { get; set; } = 1; +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsStop.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsStop.cs new file mode 100644 index 0000000..6198ffa --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsStop.cs @@ -0,0 +1,51 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; +using Microsoft.EntityFrameworkCore; +using NetTopologySuite.Geometries; + +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +[Table("gtfs_stops")] +[PrimaryKey(nameof(Id), nameof(FeedId))] +public class GtfsStop +{ + [Column("stop_id")] + [MaxLength(32)] + public required string Id { get; set; } + + [Column("feed_id")]public int FeedId { get; set; } + [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; } + + [Column("stop_code")] + [MaxLength(32)] + public string Code { get; set; } = string.Empty; + + [Column("stop_name")] + [MaxLength(255)] + public string Name { get; set; } = string.Empty; + + [Column("stop_desc")] + [MaxLength(255)] + public string? Description { get; set; } + + [Column("stop_pos")] + public Point? Position { get; set; } + + [Column("stop_url")] + [MaxLength(255)] + public string? Url { get; set; } + + [Column("stop_timezone")] + [MaxLength(50)] + public string? Timezone { get; set; } + + [Column("wheelchair_boarding")] + public WheelchairBoarding WheelchairBoarding { get; set; } = WheelchairBoarding.Unknown; + + // [Column("location_type")] + // public int LocationType { get; set; } + // + // [Column("parent_station")] + // public int? ParentStationId { get; set; } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsStopTime.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsStopTime.cs new file mode 100644 index 0000000..2bed623 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsStopTime.cs @@ -0,0 +1,44 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Enmarcha.Experimental.ServiceViewer.Data.Extensions; +using Microsoft.EntityFrameworkCore; + +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +[Table("gtfs_stop_times")] +[PrimaryKey(nameof(TripId), nameof(StopSequence), nameof(FeedId))] +public class GtfsStopTime +{ + [Column("trip_id")] + [ForeignKey("TripId")] + [MaxLength(32)] + public string TripId { get; set; } = null!; + + [Column("feed_id")]public int FeedId { get; set; } + [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; } + + [ForeignKey(nameof(TripId))] public GtfsTrip GtfsTrip { get; set; } = null!; + + [Column("arrival_time")] public string Arrival { get; set; } + public TimeSpan ArrivalTime => TimeSpan.FromGtfsTime(Arrival); + + [Column("departure_time")] public string Departure { get; set; } + public TimeSpan DepartureTime => TimeSpan.FromGtfsTime(Departure); + + [Column("stop_id")] + [ForeignKey(nameof(GtfsStop))] + [MaxLength(32)] + public required string StopId { get; set; } + + [ForeignKey(nameof(StopId))] public GtfsStop GtfsStop { get; set; } = null!; + + [Column("stop_sequence")] public int StopSequence { get; set; } = 0; + + // [Column("pickup_type")] + // public int? PickupType { get; set; } + // + // [Column("drop_off_type")] + // public int? DropOffType { get; set; } + + [Column("shape_dist_traveled")] public double? ShapeDistTraveled { get; set; } = null!; +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsTrip.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsTrip.cs new file mode 100644 index 0000000..8dd5271 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsTrip.cs @@ -0,0 +1,57 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; +using Microsoft.EntityFrameworkCore; + +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +[Table("gtfs_trips")] +[PrimaryKey(nameof(Id), nameof(FeedId))] +public class GtfsTrip +{ + [Column("trip_id")] [MaxLength(32)] public string Id { get; set; } = null!; + + [Column("feed_id")] public int FeedId { get; set; } + [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; } + + [Column("route_id")] + [MaxLength(32)] + [ForeignKey(nameof(Route))] + public string RouteId { get; set; } = null!; + + [ForeignKey(nameof(RouteId))] public GtfsRoute Route { get; set; } = null!; + + [Column("service_id")] [MaxLength(32)] public string ServiceId { get; set; } = null!; + + [Column("trip_headsign")] + [MaxLength(255)] + public string? TripHeadsign { get; set; } + + [Column("trip_short_name")] + [MaxLength(255)] + public string? TripShortName { get; set; } + + [Column("direction_id")] public DirectionId DirectionId { get; set; } = DirectionId.Outbound; + + /// + /// Identifies the block to which the trip belongs. A block consists of a single trip or many + /// sequential trips made using the same vehicle, defined by shared service days and block_id. + /// A block_id may have trips with different service days, making distinct blocks. + /// + [Column("block_id")] + [MaxLength(32)] + public string? BlockId { get; set; } + + /// + /// Identifies a geospatial shape describing the vehicle travel path for a trip. + /// + /// To be implemented: will be stored as a GeoJSON file instead of database records. + [Column("shape_id")] + [MaxLength(32)] + public string? ShapeId { get; set; } + + [Column("trip_wheelchair_accessible")] + public TripWheelchairAccessible TripWheelchairAccessible { get; set; } = TripWheelchairAccessible.Empty; + + [Column("trip_bikes_allowed")] public TripBikesAllowed TripBikesAllowed { get; set; } = TripBikesAllowed.Empty; +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/20251211153852_Initial.Designer.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/20251211153852_Initial.Designer.cs new file mode 100644 index 0000000..af6f5d9 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/20251211153852_Initial.Designer.cs @@ -0,0 +1,547 @@ +// +using System; +using Enmarcha.Experimental.ServiceViewer.Data; +using Microsoft.EntityFrameworkCore; +using Microsoft.EntityFrameworkCore.Infrastructure; +using Microsoft.EntityFrameworkCore.Migrations; +using Microsoft.EntityFrameworkCore.Storage.ValueConversion; +using NetTopologySuite.Geometries; +using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata; + +#nullable disable + +namespace Data.Migrations +{ + [DbContext(typeof(AppDbContext))] + [Migration("20251211153852_Initial")] + partial class Initial + { + /// + protected override void BuildTargetModel(ModelBuilder modelBuilder) + { +#pragma warning disable 612, 618 + modelBuilder + .HasAnnotation("ProductVersion", "10.0.1") + .HasAnnotation("Relational:MaxIdentifierLength", 63); + + NpgsqlModelBuilderExtensions.HasPostgresExtension(modelBuilder, "postgis"); + NpgsqlModelBuilderExtensions.UseIdentityByDefaultColumns(modelBuilder); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("integer"); + + NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); + + b.Property("DownloadUrl") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)"); + + b.Property("Etag") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)"); + + b.Property("InsertedAt") + .HasColumnType("timestamp with time zone"); + + b.Property("LongName") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)"); + + b.Property("ShortName") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)"); + + b.HasKey("Id"); + + b.ToTable("feeds"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsAgency", b => + { + b.Property("Id") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_id"); + + b.Property("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property("Email") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_email"); + + b.Property("FareUrl") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_fare_url"); + + b.Property("Language") + .IsRequired() + .HasMaxLength(5) + .HasColumnType("character varying(5)") + .HasColumnName("agency_lang"); + + b.Property("Name") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_name"); + + b.Property("Phone") + .HasMaxLength(30) + .HasColumnType("character varying(30)") + .HasColumnName("agency_phone"); + + b.Property("Timezone") + .IsRequired() + .HasMaxLength(50) + .HasColumnType("character varying(50)") + .HasColumnName("agency_timezone"); + + b.Property("Url") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_url"); + + b.HasKey("Id", "FeedId"); + + b.HasIndex("FeedId"); + + b.ToTable("gtfs_agencies"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsCalendar", b => + { + b.Property("ServiceId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("service_id"); + + b.Property("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property("EndDate") + .HasColumnType("date") + .HasColumnName("end_date"); + + b.Property("Friday") + .HasColumnType("boolean") + .HasColumnName("friday"); + + b.Property("Monday") + .HasColumnType("boolean") + .HasColumnName("monday"); + + b.Property("Saturday") + .HasColumnType("boolean") + .HasColumnName("saturday"); + + b.Property("StartDate") + .HasColumnType("date") + .HasColumnName("start_date"); + + b.Property("Sunday") + .HasColumnType("boolean") + .HasColumnName("sunday"); + + b.Property("Thursday") + .HasColumnType("boolean") + .HasColumnName("thursday"); + + b.Property("Tuesday") + .HasColumnType("boolean") + .HasColumnName("tuesday"); + + b.Property("Wednesday") + .HasColumnType("boolean") + .HasColumnName("wednesday"); + + b.HasKey("ServiceId", "FeedId"); + + b.HasIndex("FeedId"); + + b.ToTable("gtfs_calendar"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsCalendarDate", b => + { + b.Property("ServiceId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("service_id"); + + b.Property("Date") + .HasColumnType("timestamp with time zone") + .HasColumnName("date"); + + b.Property("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property("ExceptionType") + .HasColumnType("integer") + .HasColumnName("exception_type"); + + b.HasKey("ServiceId", "Date", "FeedId"); + + b.HasIndex("FeedId"); + + b.ToTable("gtfs_calendar_dates"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsRoute", b => + { + b.Property("Id") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("route_id"); + + b.Property("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property("AgencyId") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_id"); + + b.Property("Color") + .HasMaxLength(7) + .HasColumnType("character varying(7)") + .HasColumnName("route_color"); + + b.Property("Description") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("route_desc"); + + b.Property("LongName") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("route_long_name"); + + b.Property("ShortName") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("route_short_name"); + + b.Property("SortOrder") + .HasColumnType("integer") + .HasColumnName("route_sort_order"); + + b.Property("TextColor") + .HasMaxLength(7) + .HasColumnType("character varying(7)") + .HasColumnName("route_text_color"); + + b.Property("Type") + .HasColumnType("integer") + .HasColumnName("route_type"); + + b.Property("Url") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("route_url"); + + b.HasKey("Id", "FeedId"); + + b.HasIndex("FeedId"); + + b.HasIndex("AgencyId", "FeedId"); + + b.ToTable("gtfs_routes"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStop", b => + { + b.Property("Id") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("stop_id"); + + b.Property("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property("Code") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("stop_code"); + + b.Property("Description") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("stop_desc"); + + b.Property("Name") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("stop_name"); + + b.Property("Position") + .HasColumnType("geometry") + .HasColumnName("stop_pos"); + + b.Property("Timezone") + .HasMaxLength(50) + .HasColumnType("character varying(50)") + .HasColumnName("stop_timezone"); + + b.Property("Url") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("stop_url"); + + b.Property("WheelchairBoarding") + .HasColumnType("integer") + .HasColumnName("wheelchair_boarding"); + + b.HasKey("Id", "FeedId"); + + b.HasIndex("FeedId"); + + b.ToTable("gtfs_stops"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStopTime", b => + { + b.Property("TripId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("trip_id"); + + b.Property("StopSequence") + .HasColumnType("integer") + .HasColumnName("stop_sequence"); + + b.Property("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property("Arrival") + .IsRequired() + .HasColumnType("text") + .HasColumnName("arrival_time"); + + b.Property("Departure") + .IsRequired() + .HasColumnType("text") + .HasColumnName("departure_time"); + + b.Property("ShapeDistTraveled") + .HasColumnType("double precision") + .HasColumnName("shape_dist_traveled"); + + b.Property("StopId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("stop_id"); + + b.HasKey("TripId", "StopSequence", "FeedId"); + + b.HasIndex("FeedId"); + + b.HasIndex("StopId", "FeedId"); + + b.HasIndex("TripId", "FeedId"); + + b.ToTable("gtfs_stop_times"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsTrip", b => + { + b.Property("Id") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("trip_id"); + + b.Property("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property("BlockId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("block_id"); + + b.Property("DirectionId") + .HasColumnType("integer") + .HasColumnName("direction_id"); + + b.Property("RouteId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("route_id"); + + b.Property("ServiceId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("service_id"); + + b.Property("ShapeId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("shape_id"); + + b.Property("TripBikesAllowed") + .ValueGeneratedOnAdd() + .HasColumnType("integer") + .HasDefaultValue(0) + .HasColumnName("trip_bikes_allowed"); + + b.Property("TripHeadsign") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("trip_headsign"); + + b.Property("TripShortName") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("trip_short_name"); + + b.Property("TripWheelchairAccessible") + .ValueGeneratedOnAdd() + .HasColumnType("integer") + .HasDefaultValue(0) + .HasColumnName("trip_wheelchair_accessible"); + + b.HasKey("Id", "FeedId"); + + b.HasIndex("FeedId"); + + b.HasIndex("RouteId", "FeedId"); + + b.ToTable("gtfs_trips"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsAgency", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsCalendar", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsCalendarDate", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsRoute", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsAgency", "Agency") + .WithMany() + .HasForeignKey("AgencyId", "FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Agency"); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStop", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStopTime", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStop", "GtfsStop") + .WithMany() + .HasForeignKey("StopId", "FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsTrip", "GtfsTrip") + .WithMany() + .HasForeignKey("TripId", "FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + + b.Navigation("GtfsStop"); + + b.Navigation("GtfsTrip"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsTrip", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsRoute", "Route") + .WithMany() + .HasForeignKey("RouteId", "FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + + b.Navigation("Route"); + }); +#pragma warning restore 612, 618 + } + } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/20251211153852_Initial.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/20251211153852_Initial.cs new file mode 100644 index 0000000..5a75d40 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/20251211153852_Initial.cs @@ -0,0 +1,318 @@ +using System; +using Microsoft.EntityFrameworkCore.Migrations; +using NetTopologySuite.Geometries; +using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata; + +#nullable disable + +namespace Data.Migrations +{ + /// + public partial class Initial : Migration + { + /// + protected override void Up(MigrationBuilder migrationBuilder) + { + migrationBuilder.AlterDatabase() + .Annotation("Npgsql:PostgresExtension:postgis", ",,"); + + migrationBuilder.CreateTable( + name: "feeds", + columns: table => new + { + Id = table.Column(type: "integer", nullable: false) + .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn), + ShortName = table.Column(type: "character varying(32)", maxLength: 32, nullable: false), + LongName = table.Column(type: "character varying(32)", maxLength: 32, nullable: false), + DownloadUrl = table.Column(type: "character varying(255)", maxLength: 255, nullable: false), + Etag = table.Column(type: "character varying(32)", maxLength: 32, nullable: false), + InsertedAt = table.Column(type: "timestamp with time zone", nullable: false) + }, + constraints: table => + { + table.PrimaryKey("PK_feeds", x => x.Id); + }); + + migrationBuilder.CreateTable( + name: "gtfs_agencies", + columns: table => new + { + agency_id = table.Column(type: "character varying(255)", maxLength: 255, nullable: false), + feed_id = table.Column(type: "integer", nullable: false), + agency_name = table.Column(type: "character varying(255)", maxLength: 255, nullable: false), + agency_url = table.Column(type: "character varying(255)", maxLength: 255, nullable: false), + agency_timezone = table.Column(type: "character varying(50)", maxLength: 50, nullable: false), + agency_lang = table.Column(type: "character varying(5)", maxLength: 5, nullable: false), + agency_phone = table.Column(type: "character varying(30)", maxLength: 30, nullable: true), + agency_email = table.Column(type: "character varying(255)", maxLength: 255, nullable: true), + agency_fare_url = table.Column(type: "character varying(255)", maxLength: 255, nullable: true) + }, + constraints: table => + { + table.PrimaryKey("PK_gtfs_agencies", x => new { x.agency_id, x.feed_id }); + table.ForeignKey( + name: "FK_gtfs_agencies_feeds_feed_id", + column: x => x.feed_id, + principalTable: "feeds", + principalColumn: "Id", + onDelete: ReferentialAction.Cascade); + }); + + migrationBuilder.CreateTable( + name: "gtfs_calendar", + columns: table => new + { + service_id = table.Column(type: "character varying(32)", maxLength: 32, nullable: false), + feed_id = table.Column(type: "integer", nullable: false), + monday = table.Column(type: "boolean", nullable: false), + tuesday = table.Column(type: "boolean", nullable: false), + wednesday = table.Column(type: "boolean", nullable: false), + thursday = table.Column(type: "boolean", nullable: false), + friday = table.Column(type: "boolean", nullable: false), + saturday = table.Column(type: "boolean", nullable: false), + sunday = table.Column(type: "boolean", nullable: false), + start_date = table.Column(type: "date", nullable: false), + end_date = table.Column(type: "date", nullable: false) + }, + constraints: table => + { + table.PrimaryKey("PK_gtfs_calendar", x => new { x.service_id, x.feed_id }); + table.ForeignKey( + name: "FK_gtfs_calendar_feeds_feed_id", + column: x => x.feed_id, + principalTable: "feeds", + principalColumn: "Id", + onDelete: ReferentialAction.Cascade); + }); + + migrationBuilder.CreateTable( + name: "gtfs_calendar_dates", + columns: table => new + { + service_id = table.Column(type: "character varying(32)", maxLength: 32, nullable: false), + date = table.Column(type: "timestamp with time zone", nullable: false), + feed_id = table.Column(type: "integer", nullable: false), + exception_type = table.Column(type: "integer", nullable: false) + }, + constraints: table => + { + table.PrimaryKey("PK_gtfs_calendar_dates", x => new { x.service_id, x.date, x.feed_id }); + table.ForeignKey( + name: "FK_gtfs_calendar_dates_feeds_feed_id", + column: x => x.feed_id, + principalTable: "feeds", + principalColumn: "Id", + onDelete: ReferentialAction.Cascade); + }); + + migrationBuilder.CreateTable( + name: "gtfs_stops", + columns: table => new + { + stop_id = table.Column(type: "character varying(32)", maxLength: 32, nullable: false), + feed_id = table.Column(type: "integer", nullable: false), + stop_code = table.Column(type: "character varying(32)", maxLength: 32, nullable: false), + stop_name = table.Column(type: "character varying(255)", maxLength: 255, nullable: false), + stop_desc = table.Column(type: "character varying(255)", maxLength: 255, nullable: true), + stop_pos = table.Column(type: "geometry", nullable: true), + stop_url = table.Column(type: "character varying(255)", maxLength: 255, nullable: true), + stop_timezone = table.Column(type: "character varying(50)", maxLength: 50, nullable: true), + wheelchair_boarding = table.Column(type: "integer", nullable: false) + }, + constraints: table => + { + table.PrimaryKey("PK_gtfs_stops", x => new { x.stop_id, x.feed_id }); + table.ForeignKey( + name: "FK_gtfs_stops_feeds_feed_id", + column: x => x.feed_id, + principalTable: "feeds", + principalColumn: "Id", + onDelete: ReferentialAction.Cascade); + }); + + migrationBuilder.CreateTable( + name: "gtfs_routes", + columns: table => new + { + route_id = table.Column(type: "character varying(255)", maxLength: 255, nullable: false), + feed_id = table.Column(type: "integer", nullable: false), + agency_id = table.Column(type: "character varying(255)", maxLength: 255, nullable: false), + route_short_name = table.Column(type: "character varying(32)", maxLength: 32, nullable: false), + route_long_name = table.Column(type: "character varying(255)", maxLength: 255, nullable: false), + route_desc = table.Column(type: "character varying(255)", maxLength: 255, nullable: true), + route_type = table.Column(type: "integer", nullable: false), + route_url = table.Column(type: "character varying(255)", maxLength: 255, nullable: true), + route_color = table.Column(type: "character varying(7)", maxLength: 7, nullable: true), + route_text_color = table.Column(type: "character varying(7)", maxLength: 7, nullable: true), + route_sort_order = table.Column(type: "integer", nullable: false) + }, + constraints: table => + { + table.PrimaryKey("PK_gtfs_routes", x => new { x.route_id, x.feed_id }); + table.ForeignKey( + name: "FK_gtfs_routes_feeds_feed_id", + column: x => x.feed_id, + principalTable: "feeds", + principalColumn: "Id", + onDelete: ReferentialAction.Cascade); + table.ForeignKey( + name: "FK_gtfs_routes_gtfs_agencies_agency_id_feed_id", + columns: x => new { x.agency_id, x.feed_id }, + principalTable: "gtfs_agencies", + principalColumns: new[] { "agency_id", "feed_id" }, + onDelete: ReferentialAction.Cascade); + }); + + migrationBuilder.CreateTable( + name: "gtfs_trips", + columns: table => new + { + trip_id = table.Column(type: "character varying(32)", maxLength: 32, nullable: false), + feed_id = table.Column(type: "integer", nullable: false), + route_id = table.Column(type: "character varying(32)", maxLength: 32, nullable: false), + service_id = table.Column(type: "character varying(32)", maxLength: 32, nullable: false), + trip_headsign = table.Column(type: "character varying(255)", maxLength: 255, nullable: true), + trip_short_name = table.Column(type: "character varying(255)", maxLength: 255, nullable: true), + direction_id = table.Column(type: "integer", nullable: false), + block_id = table.Column(type: "character varying(32)", maxLength: 32, nullable: true), + shape_id = table.Column(type: "character varying(32)", maxLength: 32, nullable: true), + trip_wheelchair_accessible = table.Column(type: "integer", nullable: false, defaultValue: 0), + trip_bikes_allowed = table.Column(type: "integer", nullable: false, defaultValue: 0) + }, + constraints: table => + { + table.PrimaryKey("PK_gtfs_trips", x => new { x.trip_id, x.feed_id }); + table.ForeignKey( + name: "FK_gtfs_trips_feeds_feed_id", + column: x => x.feed_id, + principalTable: "feeds", + principalColumn: "Id", + onDelete: ReferentialAction.Cascade); + table.ForeignKey( + name: "FK_gtfs_trips_gtfs_routes_route_id_feed_id", + columns: x => new { x.route_id, x.feed_id }, + principalTable: "gtfs_routes", + principalColumns: new[] { "route_id", "feed_id" }, + onDelete: ReferentialAction.Cascade); + }); + + migrationBuilder.CreateTable( + name: "gtfs_stop_times", + columns: table => new + { + trip_id = table.Column(type: "character varying(32)", maxLength: 32, nullable: false), + feed_id = table.Column(type: "integer", nullable: false), + stop_sequence = table.Column(type: "integer", nullable: false), + arrival_time = table.Column(type: "text", nullable: false), + departure_time = table.Column(type: "text", nullable: false), + stop_id = table.Column(type: "character varying(32)", maxLength: 32, nullable: false), + shape_dist_traveled = table.Column(type: "double precision", nullable: true) + }, + constraints: table => + { + table.PrimaryKey("PK_gtfs_stop_times", x => new { x.trip_id, x.stop_sequence, x.feed_id }); + table.ForeignKey( + name: "FK_gtfs_stop_times_feeds_feed_id", + column: x => x.feed_id, + principalTable: "feeds", + principalColumn: "Id", + onDelete: ReferentialAction.Cascade); + table.ForeignKey( + name: "FK_gtfs_stop_times_gtfs_stops_stop_id_feed_id", + columns: x => new { x.stop_id, x.feed_id }, + principalTable: "gtfs_stops", + principalColumns: new[] { "stop_id", "feed_id" }, + onDelete: ReferentialAction.Cascade); + table.ForeignKey( + name: "FK_gtfs_stop_times_gtfs_trips_trip_id_feed_id", + columns: x => new { x.trip_id, x.feed_id }, + principalTable: "gtfs_trips", + principalColumns: new[] { "trip_id", "feed_id" }, + onDelete: ReferentialAction.Cascade); + }); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_agencies_feed_id", + table: "gtfs_agencies", + column: "feed_id"); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_calendar_feed_id", + table: "gtfs_calendar", + column: "feed_id"); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_calendar_dates_feed_id", + table: "gtfs_calendar_dates", + column: "feed_id"); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_routes_agency_id_feed_id", + table: "gtfs_routes", + columns: new[] { "agency_id", "feed_id" }); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_routes_feed_id", + table: "gtfs_routes", + column: "feed_id"); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_stop_times_feed_id", + table: "gtfs_stop_times", + column: "feed_id"); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_stop_times_stop_id_feed_id", + table: "gtfs_stop_times", + columns: new[] { "stop_id", "feed_id" }); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_stop_times_trip_id_feed_id", + table: "gtfs_stop_times", + columns: new[] { "trip_id", "feed_id" }); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_stops_feed_id", + table: "gtfs_stops", + column: "feed_id"); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_trips_feed_id", + table: "gtfs_trips", + column: "feed_id"); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_trips_route_id_feed_id", + table: "gtfs_trips", + columns: new[] { "route_id", "feed_id" }); + } + + /// + protected override void Down(MigrationBuilder migrationBuilder) + { + migrationBuilder.DropTable( + name: "gtfs_calendar"); + + migrationBuilder.DropTable( + name: "gtfs_calendar_dates"); + + migrationBuilder.DropTable( + name: "gtfs_stop_times"); + + migrationBuilder.DropTable( + name: "gtfs_stops"); + + migrationBuilder.DropTable( + name: "gtfs_trips"); + + migrationBuilder.DropTable( + name: "gtfs_routes"); + + migrationBuilder.DropTable( + name: "gtfs_agencies"); + + migrationBuilder.DropTable( + name: "feeds"); + } + } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/AppDbContextModelSnapshot.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/AppDbContextModelSnapshot.cs new file mode 100644 index 0000000..1932251 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/AppDbContextModelSnapshot.cs @@ -0,0 +1,544 @@ +// +using System; +using Enmarcha.Experimental.ServiceViewer.Data; +using Microsoft.EntityFrameworkCore; +using Microsoft.EntityFrameworkCore.Infrastructure; +using Microsoft.EntityFrameworkCore.Storage.ValueConversion; +using NetTopologySuite.Geometries; +using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata; + +#nullable disable + +namespace Data.Migrations +{ + [DbContext(typeof(AppDbContext))] + partial class AppDbContextModelSnapshot : ModelSnapshot + { + protected override void BuildModel(ModelBuilder modelBuilder) + { +#pragma warning disable 612, 618 + modelBuilder + .HasAnnotation("ProductVersion", "10.0.1") + .HasAnnotation("Relational:MaxIdentifierLength", 63); + + NpgsqlModelBuilderExtensions.HasPostgresExtension(modelBuilder, "postgis"); + NpgsqlModelBuilderExtensions.UseIdentityByDefaultColumns(modelBuilder); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("integer"); + + NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property("Id")); + + b.Property("DownloadUrl") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)"); + + b.Property("Etag") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)"); + + b.Property("InsertedAt") + .HasColumnType("timestamp with time zone"); + + b.Property("LongName") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)"); + + b.Property("ShortName") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)"); + + b.HasKey("Id"); + + b.ToTable("feeds"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsAgency", b => + { + b.Property("Id") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_id"); + + b.Property("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property("Email") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_email"); + + b.Property("FareUrl") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_fare_url"); + + b.Property("Language") + .IsRequired() + .HasMaxLength(5) + .HasColumnType("character varying(5)") + .HasColumnName("agency_lang"); + + b.Property("Name") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_name"); + + b.Property("Phone") + .HasMaxLength(30) + .HasColumnType("character varying(30)") + .HasColumnName("agency_phone"); + + b.Property("Timezone") + .IsRequired() + .HasMaxLength(50) + .HasColumnType("character varying(50)") + .HasColumnName("agency_timezone"); + + b.Property("Url") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_url"); + + b.HasKey("Id", "FeedId"); + + b.HasIndex("FeedId"); + + b.ToTable("gtfs_agencies"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsCalendar", b => + { + b.Property("ServiceId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("service_id"); + + b.Property("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property("EndDate") + .HasColumnType("date") + .HasColumnName("end_date"); + + b.Property("Friday") + .HasColumnType("boolean") + .HasColumnName("friday"); + + b.Property("Monday") + .HasColumnType("boolean") + .HasColumnName("monday"); + + b.Property("Saturday") + .HasColumnType("boolean") + .HasColumnName("saturday"); + + b.Property("StartDate") + .HasColumnType("date") + .HasColumnName("start_date"); + + b.Property("Sunday") + .HasColumnType("boolean") + .HasColumnName("sunday"); + + b.Property("Thursday") + .HasColumnType("boolean") + .HasColumnName("thursday"); + + b.Property("Tuesday") + .HasColumnType("boolean") + .HasColumnName("tuesday"); + + b.Property("Wednesday") + .HasColumnType("boolean") + .HasColumnName("wednesday"); + + b.HasKey("ServiceId", "FeedId"); + + b.HasIndex("FeedId"); + + b.ToTable("gtfs_calendar"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsCalendarDate", b => + { + b.Property("ServiceId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("service_id"); + + b.Property("Date") + .HasColumnType("timestamp with time zone") + .HasColumnName("date"); + + b.Property("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property("ExceptionType") + .HasColumnType("integer") + .HasColumnName("exception_type"); + + b.HasKey("ServiceId", "Date", "FeedId"); + + b.HasIndex("FeedId"); + + b.ToTable("gtfs_calendar_dates"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsRoute", b => + { + b.Property("Id") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("route_id"); + + b.Property("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property("AgencyId") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_id"); + + b.Property("Color") + .HasMaxLength(7) + .HasColumnType("character varying(7)") + .HasColumnName("route_color"); + + b.Property("Description") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("route_desc"); + + b.Property("LongName") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("route_long_name"); + + b.Property("ShortName") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("route_short_name"); + + b.Property("SortOrder") + .HasColumnType("integer") + .HasColumnName("route_sort_order"); + + b.Property("TextColor") + .HasMaxLength(7) + .HasColumnType("character varying(7)") + .HasColumnName("route_text_color"); + + b.Property("Type") + .HasColumnType("integer") + .HasColumnName("route_type"); + + b.Property("Url") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("route_url"); + + b.HasKey("Id", "FeedId"); + + b.HasIndex("FeedId"); + + b.HasIndex("AgencyId", "FeedId"); + + b.ToTable("gtfs_routes"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStop", b => + { + b.Property("Id") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("stop_id"); + + b.Property("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property("Code") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("stop_code"); + + b.Property("Description") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("stop_desc"); + + b.Property("Name") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("stop_name"); + + b.Property("Position") + .HasColumnType("geometry") + .HasColumnName("stop_pos"); + + b.Property("Timezone") + .HasMaxLength(50) + .HasColumnType("character varying(50)") + .HasColumnName("stop_timezone"); + + b.Property("Url") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("stop_url"); + + b.Property("WheelchairBoarding") + .HasColumnType("integer") + .HasColumnName("wheelchair_boarding"); + + b.HasKey("Id", "FeedId"); + + b.HasIndex("FeedId"); + + b.ToTable("gtfs_stops"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStopTime", b => + { + b.Property("TripId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("trip_id"); + + b.Property("StopSequence") + .HasColumnType("integer") + .HasColumnName("stop_sequence"); + + b.Property("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property("Arrival") + .IsRequired() + .HasColumnType("text") + .HasColumnName("arrival_time"); + + b.Property("Departure") + .IsRequired() + .HasColumnType("text") + .HasColumnName("departure_time"); + + b.Property("ShapeDistTraveled") + .HasColumnType("double precision") + .HasColumnName("shape_dist_traveled"); + + b.Property("StopId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("stop_id"); + + b.HasKey("TripId", "StopSequence", "FeedId"); + + b.HasIndex("FeedId"); + + b.HasIndex("StopId", "FeedId"); + + b.HasIndex("TripId", "FeedId"); + + b.ToTable("gtfs_stop_times"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsTrip", b => + { + b.Property("Id") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("trip_id"); + + b.Property("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property("BlockId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("block_id"); + + b.Property("DirectionId") + .HasColumnType("integer") + .HasColumnName("direction_id"); + + b.Property("RouteId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("route_id"); + + b.Property("ServiceId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("service_id"); + + b.Property("ShapeId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("shape_id"); + + b.Property("TripBikesAllowed") + .ValueGeneratedOnAdd() + .HasColumnType("integer") + .HasDefaultValue(0) + .HasColumnName("trip_bikes_allowed"); + + b.Property("TripHeadsign") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("trip_headsign"); + + b.Property("TripShortName") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("trip_short_name"); + + b.Property("TripWheelchairAccessible") + .ValueGeneratedOnAdd() + .HasColumnType("integer") + .HasDefaultValue(0) + .HasColumnName("trip_wheelchair_accessible"); + + b.HasKey("Id", "FeedId"); + + b.HasIndex("FeedId"); + + b.HasIndex("RouteId", "FeedId"); + + b.ToTable("gtfs_trips"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsAgency", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsCalendar", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsCalendarDate", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsRoute", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsAgency", "Agency") + .WithMany() + .HasForeignKey("AgencyId", "FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Agency"); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStop", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStopTime", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStop", "GtfsStop") + .WithMany() + .HasForeignKey("StopId", "FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsTrip", "GtfsTrip") + .WithMany() + .HasForeignKey("TripId", "FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + + b.Navigation("GtfsStop"); + + b.Navigation("GtfsTrip"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsTrip", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsRoute", "Route") + .WithMany() + .HasForeignKey("RouteId", "FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + + b.Navigation("Route"); + }); +#pragma warning restore 612, 618 + } + } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/QueryExtensions/GtfsCalendarQueryExtensions.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/QueryExtensions/GtfsCalendarQueryExtensions.cs new file mode 100644 index 0000000..5de48ce --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/QueryExtensions/GtfsCalendarQueryExtensions.cs @@ -0,0 +1,21 @@ +using Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +namespace Enmarcha.Experimental.ServiceViewer.Data.QueryExtensions; + +public static class GtfsCalendarQueryExtensions +{ + public static IQueryable WhereDayOfWeek(this IQueryable query, DayOfWeek dayOfWeek) + { + return dayOfWeek switch + { + DayOfWeek.Monday => query.Where(c => c.Monday), + DayOfWeek.Tuesday => query.Where(c => c.Tuesday), + DayOfWeek.Wednesday => query.Where(c => c.Wednesday), + DayOfWeek.Thursday => query.Where(c => c.Thursday), + DayOfWeek.Friday => query.Where(c => c.Friday), + DayOfWeek.Saturday => query.Where(c => c.Saturday), + DayOfWeek.Sunday => query.Where(c => c.Sunday), + _ => query + }; + } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Enmarcha.Experimental.ServiceViewer.csproj b/src/Enmarcha.Experimental.ServiceViewer/Enmarcha.Experimental.ServiceViewer.csproj new file mode 100644 index 0000000..2d0c1e1 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Enmarcha.Experimental.ServiceViewer.csproj @@ -0,0 +1,17 @@ + + + + net10.0 + enable + enable + Enmarcha.Experimental.ServiceViewer + 17600c95-53dd-43b7-9116-24ed4d24eae0 + + + + + + + + + diff --git a/src/Enmarcha.Experimental.ServiceViewer/Program.cs b/src/Enmarcha.Experimental.ServiceViewer/Program.cs new file mode 100644 index 0000000..952b273 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Program.cs @@ -0,0 +1,35 @@ +using Enmarcha.Experimental.ServiceViewer; +using Enmarcha.Experimental.ServiceViewer.Data; +using Microsoft.EntityFrameworkCore; + +var builder = WebApplication.CreateBuilder(args); + +builder.Services.AddControllersWithViews(); + +builder.Services.AddDbContext(db => +{ + var connectionString = builder.Configuration.GetConnectionString("Database"); + if (string.IsNullOrEmpty(connectionString)) + { + throw new InvalidOperationException("Connection string 'Database' is not configured."); + } + db.UseNpgsql(connectionString, npg => + { + npg.UseNetTopologySuite(); + }); +}); + +builder.Services.AddHttpClient(); +builder.Services.AddMemoryCache(); + +var app = builder.Build(); + +app.UseHttpsRedirection(); +app.UseRouting(); + +app.UseStaticFiles(); +app.MapStaticAssets(); + +app.MapControllers(); + +app.Run(); diff --git a/src/Enmarcha.Experimental.ServiceViewer/Properties/launchSettings.json b/src/Enmarcha.Experimental.ServiceViewer/Properties/launchSettings.json new file mode 100644 index 0000000..34eab40 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Properties/launchSettings.json @@ -0,0 +1,14 @@ +{ + "$schema": "https://json.schemastore.org/launchsettings.json", + "profiles": { + "https": { + "commandName": "Project", + "dotnetRunMessages": true, + "launchBrowser": true, + "applicationUrl": "https://localhost:7265;http://localhost:5154", + "environmentVariables": { + "ASPNETCORE_ENVIRONMENT": "Development" + } + } + } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Views/Services/DaysInFeed.cshtml b/src/Enmarcha.Experimental.ServiceViewer/Views/Services/DaysInFeed.cshtml new file mode 100644 index 0000000..4e252dc --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Views/Services/DaysInFeed.cshtml @@ -0,0 +1,22 @@ +@model Enmarcha.Experimental.ServiceViewer.Views.Services.DaysInFeedModel +@{ + ViewData["Title"] = "Fechas con datos"; +} + +@section Head +{ + +} + +
+

Fechas con datos

+
+ +
+ @foreach (var day in Model.Days) + { + + } +
diff --git a/src/Enmarcha.Experimental.ServiceViewer/Views/Services/DaysInFeed.cshtml.cs b/src/Enmarcha.Experimental.ServiceViewer/Views/Services/DaysInFeed.cshtml.cs new file mode 100644 index 0000000..e633fcb --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Views/Services/DaysInFeed.cshtml.cs @@ -0,0 +1,7 @@ +namespace Enmarcha.Experimental.ServiceViewer.Views.Services; + +public class DaysInFeedModel +{ + public List Days { get; set; } = []; + public DateOnly Today { get; set; } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Views/Services/ServiceDetails.cshtml b/src/Enmarcha.Experimental.ServiceViewer/Views/Services/ServiceDetails.cshtml new file mode 100644 index 0000000..969e0dd --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Views/Services/ServiceDetails.cshtml @@ -0,0 +1,63 @@ +@using Enmarcha.Experimental.ServiceViewer.Data.Gtfs +@using Humanizer +@using Humanizer.Localisation +@model Enmarcha.Experimental.ServiceViewer.Views.Services.ServiceDetailsModel +@{ + ViewData["Title"] = Model.ServiceName; +} + +@section Head +{ + + + +} + +
+

@ViewData["Title"]

+
+ + + +
+ @foreach (ServiceDetailsItem item in Model.Items) + { +
+
+
@item.ShortName
+
@item.LongName
+
+ @item.TotalDistance +
+
+
+
+
@item.FirstStopTime
+
@item.FirstStopName
+
+
+
@item.LastStopTime
+
@item.LastStopName
+
+
+
+ Ver detalle del viaje → +
+
+ } +
+ +
+ Tiempo de conducción: @Model.TotalDrivingTime.Hours horas y @Model.TotalDrivingTime.Minutes minutos.
+ Distancia total: @Model.TotalDistanceKm +
diff --git a/src/Enmarcha.Experimental.ServiceViewer/Views/Services/ServiceDetails.cshtml.cs b/src/Enmarcha.Experimental.ServiceViewer/Views/Services/ServiceDetails.cshtml.cs new file mode 100644 index 0000000..dbe7ad8 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Views/Services/ServiceDetails.cshtml.cs @@ -0,0 +1,29 @@ +namespace Enmarcha.Experimental.ServiceViewer.Views.Services; + +public class ServiceDetailsModel +{ + public DateOnly Date { get; set; } + public string ServiceId { get; set; } = string.Empty; + public string ServiceName { get; set; } = string.Empty; + + public List Items { get; set; } = []; + public TimeSpan TotalDrivingTime { get; set; } + + public int TotalDistance { get; set; } + public string TotalDistanceKm => (TotalDistance / 1000.0).ToString("0.00 km"); +} + +public class ServiceDetailsItem +{ + public string TripId { get; set; } = string.Empty; + public string SafeRouteId { get; set; } = string.Empty; + public string ShortName { get; set; } = string.Empty; + public string LongName { get; set; } = string.Empty; + public string TotalDistance { get; set; } = string.Empty; + + public string FirstStopTime { get; set; } = string.Empty; + public string FirstStopName { get; set; } = string.Empty; + + public string LastStopTime { get; set; } = string.Empty; + public string LastStopName { get; set; } = string.Empty; +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Views/Services/ServicesInDay.cshtml b/src/Enmarcha.Experimental.ServiceViewer/Views/Services/ServicesInDay.cshtml new file mode 100644 index 0000000..2161410 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Views/Services/ServicesInDay.cshtml @@ -0,0 +1,40 @@ +@model Enmarcha.Experimental.ServiceViewer.Views.Services.ServiceInDayModel +@{ + ViewData["Title"] = "Servicios a realizar en " + Model.Date.ToString("dd 'de' MMMM 'de' yyyy"); +} + +@section Head +{ + + +} + +
+

+ @ViewData["Title"] +

+
+ +
+ @foreach (ServicesInDayItem card in Model.Items) + { +
+
+ + @card.ServiceName + +
+
+ @card.ShiftStart → @card.ShiftEnd +
+
+ @foreach (var cardTripGroup in card.TripGroups) + { + + @cardTripGroup.route.ShortName (@cardTripGroup.count) + + } +
+
+ } +
diff --git a/src/Enmarcha.Experimental.ServiceViewer/Views/Services/ServicesInDay.cshtml.cs b/src/Enmarcha.Experimental.ServiceViewer/Views/Services/ServicesInDay.cshtml.cs new file mode 100644 index 0000000..6b55e64 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Views/Services/ServicesInDay.cshtml.cs @@ -0,0 +1,39 @@ +using Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +namespace Enmarcha.Experimental.ServiceViewer.Views.Services; + +public class ServiceInDayModel +{ + public List Items { get; set; } = []; + public DateOnly Date { get; set; } +} + +public class ServicesInDayItem +{ + public string ServiceId { get; set; } + public string ServiceName { get; set; } + public List Trips { get; set; } + public List TripGroups { get; set; } + + public string ShiftStart { get; set; } + public string ShiftEnd { get; set; } + + public ServicesInDayItem( + string serviceId, + string serviceName, + List trips, + List tripGroups, + string shiftStart, + string shiftEnd + ) { + ServiceId = serviceId; + ServiceName = serviceName; + Trips = trips; + TripGroups = tripGroups; + + ShiftStart = shiftStart; + ShiftEnd = shiftEnd; + } +} + +public record TripGroup(GtfsRoute route, int count); diff --git a/src/Enmarcha.Experimental.ServiceViewer/Views/Shared/_Layout.cshtml b/src/Enmarcha.Experimental.ServiceViewer/Views/Shared/_Layout.cshtml new file mode 100644 index 0000000..88d5b83 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Views/Shared/_Layout.cshtml @@ -0,0 +1,22 @@ + + + + + + + + + + + @ViewData["Title"] - VentaSync + + + + @await RenderSectionAsync("Head", required: false) + + + + +@RenderBody() + + diff --git a/src/Enmarcha.Experimental.ServiceViewer/Views/_ViewImports.cshtml b/src/Enmarcha.Experimental.ServiceViewer/Views/_ViewImports.cshtml new file mode 100644 index 0000000..2cbb86c --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Views/_ViewImports.cshtml @@ -0,0 +1,2 @@ +@using Enmarcha.Experimental.ServiceViewer.Views.Services +@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers diff --git a/src/Enmarcha.Experimental.ServiceViewer/Views/_ViewStart.cshtml b/src/Enmarcha.Experimental.ServiceViewer/Views/_ViewStart.cshtml new file mode 100644 index 0000000..a5f1004 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Views/_ViewStart.cshtml @@ -0,0 +1,3 @@ +@{ + Layout = "_Layout"; +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/appsettings.json b/src/Enmarcha.Experimental.ServiceViewer/appsettings.json new file mode 100644 index 0000000..10f68b8 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/appsettings.json @@ -0,0 +1,9 @@ +{ + "Logging": { + "LogLevel": { + "Default": "Information", + "Microsoft.AspNetCore": "Warning" + } + }, + "AllowedHosts": "*" +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/wwwroot/styles/common.css b/src/Enmarcha.Experimental.ServiceViewer/wwwroot/styles/common.css new file mode 100644 index 0000000..a0e0750 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/wwwroot/styles/common.css @@ -0,0 +1,23 @@ +body { + font-family: 'Inter', 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; + padding: 2rem; + background-color: #fff; + max-width: 800px; + margin: 0 auto; + line-height: 1.5; +} + +h1 { + font-size: 1.75rem; + margin-bottom: 1.5rem; + text-align: center; +} + +body > footer { + text-align: center; + font-size: 0.85rem; + color: #666; + margin-top: 2rem; + padding-top: 1rem; + border-top: 1px solid #ddd; +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/wwwroot/styles/days_in_feed.css b/src/Enmarcha.Experimental.ServiceViewer/wwwroot/styles/days_in_feed.css new file mode 100644 index 0000000..b3c46d1 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/wwwroot/styles/days_in_feed.css @@ -0,0 +1,28 @@ +main article { + background: #f9f9f9; + border: 1px solid #ddd; + border-radius: 4px; + padding: 0.75rem 1rem; + margin: 0.5rem 0; + text-align: center; +} + +main article.current-day { + background: #e3f2fd; + border-color: #0074d9; +} + +main article.current-day a { + font-weight: 700; +} + +main article a { + font-size: 1rem; + font-weight: 500; + color: #0074d9; + text-decoration: none; +} + +main article a:hover { + text-decoration: underline; +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/wwwroot/styles/service_details.css b/src/Enmarcha.Experimental.ServiceViewer/wwwroot/styles/service_details.css new file mode 100644 index 0000000..570de3a --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/wwwroot/styles/service_details.css @@ -0,0 +1,146 @@ +.navigation-bar { + margin-bottom: 1.5rem; + font-size: 0.9rem; +} + +.trip-container { + border: 1px solid #ddd; + border-radius: 0.25rem; + background-color: var(--route-color-semi, #fafafa); + box-shadow: 0 1px 2px rgba(0, 0, 0, 0.08); + margin-bottom: 1.5rem; + overflow: hidden; +} + +.trip-header { + display: flex; + background-color: #fff; + border-bottom: 1px solid #eee; + padding: 0.6rem 0.8rem; +} + +.trip-header .route { + font-weight: bold; + font-size: 1.1rem; + min-width: 60px; + display: flex; + align-items: center; + justify-content: center; + border-right: 1px solid #ddd; + margin-right: 0.5rem; + padding-right: 0.5rem; +} + +.trip-header .headsign { + flex-grow: 1; + font-weight: 500; +} + +.trip-header .distance { + min-width: 80px; + text-align: right; + font-weight: 500; +} + +.trip-details { + display: flex; + flex-wrap: wrap; +} + +.trip-leg { + flex: 1; + min-width: 280px; + padding: 0.75rem; +} + +.trip-leg:first-child { + border-right: 1px solid #ddd; +} + +.trip-time { + font-weight: 600; + font-family: 'Consolas', monospace; + font-size: 1.1rem; +} + +.trip-stop { + margin-top: 0.25rem; + color: #333; +} + +.trip-footer { + text-align: right; + margin: 0.5em 1em 0.5em 0; +} + +.trip-details-link { + color: #0074d9; + text-decoration: underline; + font-size: 0.98em; +} + +/* Day change marker */ +.day-change-marker { + background-color: #fff3cd; + border: 2px solid #f5c86a; + color: #856404; + text-align: center; + padding: 0.75rem; + margin: 1rem 0; + border-radius: 4px; + font-weight: bold; +} + +/* Total distance counter */ +.total-distance { + text-align: right; + font-weight: bold; + padding: 0.75rem; + margin-top: 1.5rem; + border-top: 1px solid #ddd; + background-color: #f9f9f9; + border-radius: 4px; +} + +/* Highlight current trip */ +.trip-container.highlight { + box-shadow: 0 0 8px rgba(0, 116, 217, 0.6); + border-color: #0074d9; + padding: 1rem; +} + +/* Media query for print */ +@media print { + body { + padding: 0; + margin: 0; + } + + .trip-container { + page-break-inside: avoid; + border: 1px solid #000; + margin-bottom: 0.5rem; + } + + .trip-header { + background-color: #f5f5f5 !important; + } + + .day-change-marker { + border: 1px solid #000; + background-color: #f5f5f5 !important; + color: #000; + } + + .navigation { + display: none; + } +} + +.trip-line--N1 { + background-color: rgba(191, 191, 191, 0.30); +} + +.trip-line-VITRASA { + background-color: rgba(0, 153, 0, 0.30); +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/wwwroot/styles/services_in_day.css b/src/Enmarcha.Experimental.ServiceViewer/wwwroot/styles/services_in_day.css new file mode 100644 index 0000000..ce847ef --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/wwwroot/styles/services_in_day.css @@ -0,0 +1,70 @@ +a { + color: #0074d9; + text-decoration: none; +} + +a:hover { + text-decoration: underline; +} + +#service-cards { + list-style: none; + padding: 0; + margin: 0; +} + +#service-cards article { + background: #f9f9f9; + border: 1px solid #ddd; + border-radius: 4px; + margin: 0.5rem 0; + padding: 1rem; +} + +#service-cards article header { + font-weight: 600; + font-size: 1.1rem; + margin-bottom: 0.5rem; +} + +#service-cards article main { + font-size: 0.9rem; + margin-bottom: 0.75rem; + color: #555; +} + +#service-cards article footer { + margin-top: 0.5rem; + display: flex; + flex-wrap: wrap; + gap: 0.125rem 0.75rem; +} + +#service-cards article footer span.route-group { + display: inline-block; + padding: 0.3rem 0.6rem; + margin: 0.25rem 0.1rem; + font-size: 0.85rem; + font-weight: 600; + + background: #FFF; + border-bottom: 4px solid var(--route-color, #000); + color: var(--route-text, #000); +} + +/* Filter button styles */ +.filter-btn { + padding: 0.4rem 0.8rem; + margin: 0.2rem; + border: 1px solid #0074d9; + border-radius: 4px; + background: #fff; + color: #0074d9; + cursor: pointer; + transition: background 0.2s, color 0.2s; +} + +.filter-btn.active { + background: #0074d9; + color: #fff; +} -- cgit v1.3