diff options
Diffstat (limited to 'src')
39 files changed, 2717 insertions, 0 deletions
diff --git a/src/Costasdev.Busurbano.ServiceViewer/.gitignore b/src/Costasdev.Busurbano.ServiceViewer/.gitignore new file mode 100644 index 0000000..ff5b00c --- /dev/null +++ b/src/Costasdev.Busurbano.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/Costasdev.Busurbano.ServiceViewer/AppDbContextDesignTimeFactory.cs b/src/Costasdev.Busurbano.ServiceViewer/AppDbContextDesignTimeFactory.cs new file mode 100644 index 0000000..4caaabc --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/AppDbContextDesignTimeFactory.cs @@ -0,0 +1,41 @@ +using Costasdev.ServiceViewer.Data; +using Microsoft.EntityFrameworkCore; +using Microsoft.EntityFrameworkCore.Design; + +namespace Costasdev.ServiceViewer; + +public class AppDbContextDesignTimeFactory : IDesignTimeDbContextFactory<AppDbContext> +{ + 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<AppDbContext>(); + 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.UseMySQL( + connectionString, + options => options.MigrationsAssembly(typeof(AppDbContext).Assembly.FullName) + ); + + return new AppDbContext(builder.Options); + } +} diff --git a/src/Costasdev.Busurbano.ServiceViewer/Controllers/ServicesController.cs b/src/Costasdev.Busurbano.ServiceViewer/Controllers/ServicesController.cs new file mode 100644 index 0000000..64f1084 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Controllers/ServicesController.cs @@ -0,0 +1,358 @@ +using Costasdev.ServiceViewer.Data; +using Costasdev.ServiceViewer.Data.Gtfs; +using Costasdev.ServiceViewer.Data.Gtfs.Enums; +using Costasdev.ServiceViewer.Data.QueryExtensions; +using Costasdev.ServiceViewer.Views.Services; +using Microsoft.AspNetCore.Mvc; +using Microsoft.EntityFrameworkCore; + +namespace Costasdev.ServiceViewer.Controllers; + +[Route("")] +public class ServicesController : Controller +{ + private readonly AppDbContext _db; + + public ServicesController(AppDbContext db) + { + _db = db; + } + + [HttpGet("")] + public async Task<IActionResult> 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<string> 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<ServiceInformation> serviceInformations = []; + List<string> tripsWhoseFirstStopWeWant = []; + List<string> tripsWhoseLastStopWeWant = []; + foreach (var (service, trips) in tripsByService) + { + var orderedTrips = trips + .Select(t => new + { + Trip = t, + Sequence = int.TryParse(t.TripId.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().TripId); + tripsWhoseLastStopWeWant.Add(orderedTrips.Last().TripId); + 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.DepartureTime); + + 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.ArrivalTime); + + // 4. Create a view model + List<ServicesInDayItem> 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.TripId.Split('_').LastOrDefault() ?? string.Empty)) + .ToList(); + + List<TripGroup> 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.TripId, out var shiftStart) + ? shiftStart + : string.Empty, + lastStopTimePerTrip.TryGetValue(serviceInfo.LastTrip.TripId, 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<string> 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.TripId.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<ServiceDetailsItem> 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.TripId) + .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.ArrivalTimeOnly - firstStop.DepartureTimeOnly); + + items.Add(new ServiceDetailsItem + { + TripId = trip.TripId, + 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.DepartureTime, + LastStopName = lastStop.GtfsStop.Name, + LastStopTime = lastStop.ArrivalTime + }); + } + + 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<GtfsTrip> Trips { get; } + internal GtfsTrip FirstTrip { get; } + internal GtfsTrip LastTrip { get; } + + internal ServiceInformation( + string serviceId, + string serviceName, + List<GtfsTrip> trips, + GtfsTrip firstTrip, + GtfsTrip lastTrip + ) + { + ServiceId = serviceId; + ServiceName = serviceName; + Trips = trips; + FirstTrip = firstTrip; + LastTrip = lastTrip; + } +} diff --git a/src/Costasdev.Busurbano.ServiceViewer/Controllers/StylesheetController.cs b/src/Costasdev.Busurbano.ServiceViewer/Controllers/StylesheetController.cs new file mode 100644 index 0000000..00654db --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Controllers/StylesheetController.cs @@ -0,0 +1,39 @@ +using System.Text; +using Costasdev.ServiceViewer.Data; +using Microsoft.AspNetCore.Mvc; +using Microsoft.EntityFrameworkCore; + +namespace Costasdev.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/Costasdev.Busurbano.ServiceViewer/Costasdev.Busurbano.ServiceViewer.csproj b/src/Costasdev.Busurbano.ServiceViewer/Costasdev.Busurbano.ServiceViewer.csproj new file mode 100644 index 0000000..4aab7d4 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Costasdev.Busurbano.ServiceViewer.csproj @@ -0,0 +1,15 @@ +<Project Sdk="Microsoft.NET.Sdk.Web"> + + <PropertyGroup> + <TargetFramework>net9.0</TargetFramework> + <Nullable>enable</Nullable> + <ImplicitUsings>enable</ImplicitUsings> + <RootNamespace>Costasdev.ServiceViewer</RootNamespace> + <UserSecretsId>17600c95-53dd-43b7-9116-24ed4d24eae0</UserSecretsId> + </PropertyGroup> + + <ItemGroup> + <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="9.0.8" /> + <PackageReference Include="MySql.EntityFrameworkCore" Version="9.0.6" /> + </ItemGroup> +</Project> diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/AppDbContext.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/AppDbContext.cs new file mode 100644 index 0000000..55a5a08 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/AppDbContext.cs @@ -0,0 +1,52 @@ +using Costasdev.ServiceViewer.Data.Gtfs; +using Costasdev.ServiceViewer.Data.Gtfs.Enums; +using Microsoft.EntityFrameworkCore; + +namespace Costasdev.ServiceViewer.Data; + +public class AppDbContext : DbContext +{ + public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) + { + } + + protected override void OnModelCreating(ModelBuilder modelBuilder) + { + // Relación Trip -> StopTimes (cascade delete) + modelBuilder.Entity<GtfsTrip>() + .HasMany<GtfsStopTime>() + .WithOne(st => st.GtfsTrip) + .HasForeignKey(st => st.TripId) + .OnDelete(DeleteBehavior.Cascade); + + // Relación Stop -> StopTimes (cascade delete) + modelBuilder.Entity<GtfsStop>() + .HasMany<GtfsStopTime>() + .WithOne(st => st.GtfsStop) + .HasForeignKey(st => st.StopId) + .OnDelete(DeleteBehavior.Cascade); + + // Relación Route -> Trips (cascade delete) + modelBuilder.Entity<GtfsRoute>() + .HasMany<GtfsTrip>() + .WithOne(t => t.Route) + .HasForeignKey(t => t.RouteId) + .OnDelete(DeleteBehavior.Cascade); + + modelBuilder.Entity<GtfsTrip>() + .Property(t => t.TripWheelchairAccessible) + .HasDefaultValue(TripWheelchairAccessible.Empty); + + modelBuilder.Entity<GtfsTrip>() + .Property(t => t.TripBikesAllowed) + .HasDefaultValue(TripBikesAllowed.Empty); + } + + public DbSet<GtfsAgency> Agencies { get; set; } + public DbSet<GtfsCalendar> Calendars { get; set; } + public DbSet<GtfsCalendarDate> CalendarDates { get; set; } + public DbSet<GtfsRoute> Routes { get; set; } + public DbSet<GtfsStop> Stops { get; set; } + public DbSet<GtfsStopTime> StopTimes { get; set; } + public DbSet<GtfsTrip> Trips { get; set; } +} diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/DirectionId.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/DirectionId.cs new file mode 100644 index 0000000..cbcf80b --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/DirectionId.cs @@ -0,0 +1,7 @@ +namespace Costasdev.ServiceViewer.Data.Gtfs.Enums; + +public enum DirectionId +{ + Outbound = 0, + Inbound = 1 +} diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/ExceptionType.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/ExceptionType.cs new file mode 100644 index 0000000..0ad0345 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/ExceptionType.cs @@ -0,0 +1,7 @@ +namespace Costasdev.ServiceViewer.Data.Gtfs.Enums; + +public enum ExceptionType +{ + Added = 1, + Removed = 2 +} diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/RouteType.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/RouteType.cs new file mode 100644 index 0000000..e19d02a --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/RouteType.cs @@ -0,0 +1,15 @@ +namespace Costasdev.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/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/TripBikesAllowed.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/TripBikesAllowed.cs new file mode 100644 index 0000000..838bc81 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/TripBikesAllowed.cs @@ -0,0 +1,8 @@ +namespace Costasdev.ServiceViewer.Data.Gtfs.Enums; + +public enum TripBikesAllowed +{ + Empty = 0, + CanAccommodate = 1, + NotAllowed = 2 +} diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/TripWheelchairAccessible.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/TripWheelchairAccessible.cs new file mode 100644 index 0000000..e84b699 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/TripWheelchairAccessible.cs @@ -0,0 +1,8 @@ +namespace Costasdev.ServiceViewer.Data.Gtfs.Enums; + +public enum TripWheelchairAccessible +{ + Empty = 0, + CanAccommodate = 1, + NotAccessible = 2 +} diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/WheelchairBoarding.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/WheelchairBoarding.cs new file mode 100644 index 0000000..3cc550f --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Enums/WheelchairBoarding.cs @@ -0,0 +1,8 @@ +namespace Costasdev.ServiceViewer.Data.Gtfs.Enums; + +public enum WheelchairBoarding +{ + Unknown = 0, + SomeVehicles = 1, + NotPossible = 2 +} diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsAgency.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsAgency.cs new file mode 100644 index 0000000..999adb8 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsAgency.cs @@ -0,0 +1,41 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; + +namespace Costasdev.ServiceViewer.Data.Gtfs; + +[Table("agencies")] +public class GtfsAgency +{ + [Key] + [Column("agency_id")] + [MaxLength(255)] + public required string Id { 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/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsCalendar.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsCalendar.cs new file mode 100644 index 0000000..56f3f85 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsCalendar.cs @@ -0,0 +1,40 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; + +namespace Costasdev.ServiceViewer.Data.Gtfs; + +[Table("calendar")] +public class GtfsCalendar +{ + [Key] + [Column("service_id")] + [MaxLength(32)] + public string ServiceId { get; set; } = null!; + + [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/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsCalendarDate.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsCalendarDate.cs new file mode 100644 index 0000000..977fb74 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsCalendarDate.cs @@ -0,0 +1,21 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Costasdev.ServiceViewer.Data.Gtfs.Enums; +using Microsoft.EntityFrameworkCore; + +namespace Costasdev.ServiceViewer.Data.Gtfs; + +[Table("calendar_dates")] +[PrimaryKey(nameof(ServiceId), nameof(Date))] +public class GtfsCalendarDate +{ + [Column("service_id")] + [MaxLength(32)] + public required string ServiceId { get; set; } + + [Column("date")] + public required DateTime Date { get; set; } + + [Column("exception_type")] + public required ExceptionType ExceptionType { get; set; } +} diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsRoute.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsRoute.cs new file mode 100644 index 0000000..261e183 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsRoute.cs @@ -0,0 +1,66 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Costasdev.ServiceViewer.Data.Gtfs.Enums; + +namespace Costasdev.ServiceViewer.Data.Gtfs; + +[Table("routes")] +public class GtfsRoute +{ + [Key] + [Column("route_id")] + [MaxLength(255)] + public required string Id { get; set; } + + public string SafeId => Id.Replace(" ", "_").Replace("-", "_"); + + [Column("agency_id")] + [ForeignKey(nameof(GtfsAgency))] + [MaxLength(255)] + public required string AgencyId { get; set; } + + [ForeignKey(nameof(AgencyId))] + public GtfsAgency GtfsAgency { get; set; } = null!; + + /// <summary> + /// 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. + /// </summary> + [Column("route_short_name")] + [MaxLength(32)] + public string ShortName { get; set; } = string.Empty; + + /// <summary> + /// 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. + /// </summary> + [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; + + /// <summary> + /// Orders the routes in a way which is ideal for presentation to customers. + /// Routes with smaller route_sort_order values should be displayed first. + /// </summary> + [Column("route_sort_order")] + public int SortOrder { get; set; } = 1; +} diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsStop.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsStop.cs new file mode 100644 index 0000000..20900d7 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsStop.cs @@ -0,0 +1,49 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Costasdev.ServiceViewer.Data.Gtfs.Enums; + +namespace Costasdev.ServiceViewer.Data.Gtfs; + +[Table("stops")] +public class GtfsStop +{ + [Key] + [Column("stop_id")] + [MaxLength(32)] + public required string Id { 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_lat")] + public double Latitude { get; set; } + + [Column("stop_lon")] + public double Longitude { 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/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsStopTime.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsStopTime.cs new file mode 100644 index 0000000..07b6732 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsStopTime.cs @@ -0,0 +1,40 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Microsoft.EntityFrameworkCore; + +namespace Costasdev.ServiceViewer.Data.Gtfs; + +[Table("stop_times")] +[PrimaryKey(nameof(TripId), nameof(StopSequence))] +public class GtfsStopTime +{ + [Column("trip_id")] + [ForeignKey("TripId")] + [MaxLength(32)] + public string TripId { get; set; } = null!; + + [ForeignKey(nameof(TripId))] public GtfsTrip GtfsTrip { get; set; } = null!; + + [Column("arrival_time")] public string ArrivalTime { get; set; } + public TimeOnly ArrivalTimeOnly => TimeOnly.Parse(ArrivalTime); + + [Column("departure_time")] public string DepartureTime { get; set; } + public TimeOnly DepartureTimeOnly => TimeOnly.Parse(DepartureTime); + + [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/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsTrip.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsTrip.cs new file mode 100644 index 0000000..d68cbdd --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsTrip.cs @@ -0,0 +1,60 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Costasdev.ServiceViewer.Data.Gtfs.Enums; + +namespace Costasdev.ServiceViewer.Data.Gtfs; + +[Table("trips")] +public class GtfsTrip +{ + [Key] + [Column("trip_id")] + [MaxLength(32)] + public string TripId { get; set; } = null!; + + [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; + + /// <summary> + /// 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. + /// </summary> + [Column("block_id")] + [MaxLength(32)] + public string? BlockId { get; set; } + + /// <summary> + /// Identifies a geospatial shape describing the vehicle travel path for a trip. + /// </summary> + /// <remarks>To be implemented: will be stored as a GeoJSON file instead of database records.</remarks> + [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/Costasdev.Busurbano.ServiceViewer/Data/Migrations/20250821135143_InitialGtfsData.Designer.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Migrations/20250821135143_InitialGtfsData.Designer.cs new file mode 100644 index 0000000..d123034 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Migrations/20250821135143_InitialGtfsData.Designer.cs @@ -0,0 +1,398 @@ +// <auto-generated /> +using System; +using Costasdev.Busurbano.Database; +using Costasdev.ServiceViewer; +using Costasdev.ServiceViewer.Data; +using Microsoft.EntityFrameworkCore; +using Microsoft.EntityFrameworkCore.Infrastructure; +using Microsoft.EntityFrameworkCore.Migrations; +using Microsoft.EntityFrameworkCore.Storage.ValueConversion; + +#nullable disable + +namespace Costasdev.Busurbano.Database.Migrations +{ + [DbContext(typeof(AppDbContext))] + [Migration("20250821135143_InitialGtfsData")] + partial class InitialGtfsData + { + /// <inheritdoc /> + protected override void BuildTargetModel(ModelBuilder modelBuilder) + { +#pragma warning disable 612, 618 + modelBuilder + .HasAnnotation("ProductVersion", "9.0.8") + .HasAnnotation("Relational:MaxIdentifierLength", 64); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.Agency", b => + { + b.Property<string>("Id") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("agency_id"); + + b.Property<string>("Email") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("agency_email"); + + b.Property<string>("FareUrl") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("agency_fare_url"); + + b.Property<string>("Language") + .IsRequired() + .HasMaxLength(5) + .HasColumnType("varchar(5)") + .HasColumnName("agency_lang"); + + b.Property<string>("Name") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("agency_name"); + + b.Property<string>("Phone") + .HasMaxLength(30) + .HasColumnType("varchar(30)") + .HasColumnName("agency_phone"); + + b.Property<string>("Timezone") + .IsRequired() + .HasMaxLength(50) + .HasColumnType("varchar(50)") + .HasColumnName("agency_timezone"); + + b.Property<string>("Url") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("agency_url"); + + b.HasKey("Id"); + + b.ToTable("agencies"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.Calendar", b => + { + b.Property<string>("ServiceId") + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("service_id"); + + b.Property<DateOnly>("EndDate") + .HasColumnType("date") + .HasColumnName("end_date"); + + b.Property<bool>("Friday") + .HasColumnType("tinyint(1)") + .HasColumnName("friday"); + + b.Property<bool>("Monday") + .HasColumnType("tinyint(1)") + .HasColumnName("monday"); + + b.Property<bool>("Saturday") + .HasColumnType("tinyint(1)") + .HasColumnName("saturday"); + + b.Property<DateOnly>("StartDate") + .HasColumnType("date") + .HasColumnName("start_date"); + + b.Property<bool>("Sunday") + .HasColumnType("tinyint(1)") + .HasColumnName("sunday"); + + b.Property<bool>("Thursday") + .HasColumnType("tinyint(1)") + .HasColumnName("thursday"); + + b.Property<bool>("Tuesday") + .HasColumnType("tinyint(1)") + .HasColumnName("tuesday"); + + b.Property<bool>("Wednesday") + .HasColumnType("tinyint(1)") + .HasColumnName("wednesday"); + + b.HasKey("ServiceId"); + + b.ToTable("calendar"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.CalendarDate", b => + { + b.Property<string>("ServiceId") + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("service_id"); + + b.Property<DateOnly>("Date") + .HasColumnType("date") + .HasColumnName("date"); + + b.Property<int>("ExceptionType") + .HasColumnType("int") + .HasColumnName("exception_type"); + + b.HasKey("ServiceId", "Date"); + + b.ToTable("calendar_dates"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.Route", b => + { + b.Property<string>("Id") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("route_id"); + + b.Property<string>("AgencyId") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("agency_id"); + + b.Property<string>("Color") + .HasMaxLength(7) + .HasColumnType("varchar(7)") + .HasColumnName("route_color"); + + b.Property<string>("Description") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("route_desc"); + + b.Property<string>("LongName") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("route_long_name"); + + b.Property<string>("ShortName") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("route_short_name"); + + b.Property<int>("SortOrder") + .HasColumnType("int") + .HasColumnName("route_sort_order"); + + b.Property<string>("TextColor") + .HasMaxLength(7) + .HasColumnType("varchar(7)") + .HasColumnName("route_text_color"); + + b.Property<int>("Type") + .HasColumnType("int") + .HasColumnName("route_type"); + + b.Property<string>("Url") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("route_url"); + + b.HasKey("Id"); + + b.HasIndex("AgencyId"); + + b.ToTable("routes"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.Stop", b => + { + b.Property<string>("Id") + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("stop_id"); + + b.Property<string>("Code") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("stop_code"); + + b.Property<string>("Description") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("stop_desc"); + + b.Property<double>("Latitude") + .HasColumnType("double") + .HasColumnName("stop_lat"); + + b.Property<double>("Longitude") + .HasColumnType("double") + .HasColumnName("stop_lon"); + + b.Property<string>("Name") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("stop_name"); + + b.Property<string>("Timezone") + .HasMaxLength(50) + .HasColumnType("varchar(50)") + .HasColumnName("stop_timezone"); + + b.Property<string>("Url") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("stop_url"); + + b.Property<int>("WheelchairBoarding") + .HasColumnType("int") + .HasColumnName("wheelchair_boarding"); + + b.HasKey("Id"); + + b.ToTable("stops"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.StopTime", b => + { + b.Property<string>("TripId") + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("trip_id"); + + b.Property<int>("StopSequence") + .HasColumnType("int") + .HasColumnName("stop_sequence"); + + b.Property<TimeOnly>("ArrivalTime") + .HasMaxLength(8) + .HasColumnType("varchar(8)") + .HasColumnName("arrival_time"); + + b.Property<TimeOnly>("DepartureTime") + .HasMaxLength(8) + .HasColumnType("varchar(8)") + .HasColumnName("departure_time"); + + b.Property<double?>("ShapeDistTraveled") + .HasColumnType("double") + .HasColumnName("shape_dist_traveled"); + + b.Property<string>("StopId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("stop_id"); + + b.HasKey("TripId", "StopSequence"); + + b.HasIndex("StopId"); + + b.ToTable("stop_times"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.Trip", b => + { + b.Property<string>("TripId") + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("trip_id"); + + b.Property<string>("BlockId") + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("block_id"); + + b.Property<int>("DirectionId") + .HasColumnType("int") + .HasColumnName("direction_id"); + + b.Property<string>("RouteId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("route_id"); + + b.Property<string>("ServiceId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("service_id"); + + b.Property<string>("ShapeId") + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("shape_id"); + + b.Property<int>("TripBikesAllowed") + .ValueGeneratedOnAdd() + .HasColumnType("int") + .HasDefaultValue(0) + .HasColumnName("trip_bikes_allowed"); + + b.Property<string>("TripHeadsign") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("trip_headsign"); + + b.Property<string>("TripShortName") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("trip_short_name"); + + b.Property<int>("TripWheelchairAccessible") + .ValueGeneratedOnAdd() + .HasColumnType("int") + .HasDefaultValue(0) + .HasColumnName("trip_wheelchair_accessible"); + + b.HasKey("TripId"); + + b.HasIndex("RouteId"); + + b.ToTable("trips"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.Route", b => + { + b.HasOne("Costasdev.Busurbano.Database.Gtfs.Agency", "Agency") + .WithMany() + .HasForeignKey("AgencyId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Agency"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.StopTime", b => + { + b.HasOne("Costasdev.Busurbano.Database.Gtfs.Stop", "Stop") + .WithMany() + .HasForeignKey("StopId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Costasdev.Busurbano.Database.Gtfs.Trip", "Trip") + .WithMany() + .HasForeignKey("TripId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Stop"); + + b.Navigation("Trip"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.Trip", b => + { + b.HasOne("Costasdev.Busurbano.Database.Gtfs.Route", null) + .WithMany() + .HasForeignKey("RouteId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); +#pragma warning restore 612, 618 + } + } +} diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Migrations/20250821135143_InitialGtfsData.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Migrations/20250821135143_InitialGtfsData.cs new file mode 100644 index 0000000..3cf6ab8 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Migrations/20250821135143_InitialGtfsData.cs @@ -0,0 +1,215 @@ +using System; +using Microsoft.EntityFrameworkCore.Migrations; + +#nullable disable + +namespace Costasdev.Busurbano.Database.Migrations +{ + /// <inheritdoc /> + public partial class InitialGtfsData : Migration + { + /// <inheritdoc /> + protected override void Up(MigrationBuilder migrationBuilder) + { + migrationBuilder.AlterDatabase() + .Annotation("MySQL:Charset", "utf8mb4"); + + migrationBuilder.CreateTable( + name: "agencies", + columns: table => new + { + agency_id = table.Column<string>(type: "varchar(255)", maxLength: 255, nullable: false), + agency_name = table.Column<string>(type: "varchar(255)", maxLength: 255, nullable: false), + agency_url = table.Column<string>(type: "varchar(255)", maxLength: 255, nullable: false), + agency_timezone = table.Column<string>(type: "varchar(50)", maxLength: 50, nullable: false), + agency_lang = table.Column<string>(type: "varchar(5)", maxLength: 5, nullable: false), + agency_phone = table.Column<string>(type: "varchar(30)", maxLength: 30, nullable: true), + agency_email = table.Column<string>(type: "varchar(255)", maxLength: 255, nullable: true), + agency_fare_url = table.Column<string>(type: "varchar(255)", maxLength: 255, nullable: true) + }, + constraints: table => + { + table.PrimaryKey("PK_agencies", x => x.agency_id); + }) + .Annotation("MySQL:Charset", "utf8mb4"); + + migrationBuilder.CreateTable( + name: "calendar", + columns: table => new + { + service_id = table.Column<string>(type: "varchar(32)", maxLength: 32, nullable: false), + monday = table.Column<bool>(type: "tinyint(1)", nullable: false), + tuesday = table.Column<bool>(type: "tinyint(1)", nullable: false), + wednesday = table.Column<bool>(type: "tinyint(1)", nullable: false), + thursday = table.Column<bool>(type: "tinyint(1)", nullable: false), + friday = table.Column<bool>(type: "tinyint(1)", nullable: false), + saturday = table.Column<bool>(type: "tinyint(1)", nullable: false), + sunday = table.Column<bool>(type: "tinyint(1)", nullable: false), + start_date = table.Column<DateOnly>(type: "date", nullable: false), + end_date = table.Column<DateOnly>(type: "date", nullable: false) + }, + constraints: table => + { + table.PrimaryKey("PK_calendar", x => x.service_id); + }) + .Annotation("MySQL:Charset", "utf8mb4"); + + migrationBuilder.CreateTable( + name: "calendar_dates", + columns: table => new + { + service_id = table.Column<string>(type: "varchar(32)", maxLength: 32, nullable: false), + date = table.Column<DateOnly>(type: "date", nullable: false), + exception_type = table.Column<int>(type: "int", nullable: false) + }, + constraints: table => + { + table.PrimaryKey("PK_calendar_dates", x => new { x.service_id, x.date }); + }) + .Annotation("MySQL:Charset", "utf8mb4"); + + migrationBuilder.CreateTable( + name: "stops", + columns: table => new + { + stop_id = table.Column<string>(type: "varchar(32)", maxLength: 32, nullable: false), + stop_code = table.Column<string>(type: "varchar(32)", maxLength: 32, nullable: false), + stop_name = table.Column<string>(type: "varchar(255)", maxLength: 255, nullable: false), + stop_desc = table.Column<string>(type: "varchar(255)", maxLength: 255, nullable: true), + stop_lat = table.Column<double>(type: "double", nullable: false), + stop_lon = table.Column<double>(type: "double", nullable: false), + stop_url = table.Column<string>(type: "varchar(255)", maxLength: 255, nullable: true), + stop_timezone = table.Column<string>(type: "varchar(50)", maxLength: 50, nullable: true), + wheelchair_boarding = table.Column<int>(type: "int", nullable: false) + }, + constraints: table => + { + table.PrimaryKey("PK_stops", x => x.stop_id); + }) + .Annotation("MySQL:Charset", "utf8mb4"); + + migrationBuilder.CreateTable( + name: "routes", + columns: table => new + { + route_id = table.Column<string>(type: "varchar(255)", maxLength: 255, nullable: false), + agency_id = table.Column<string>(type: "varchar(255)", maxLength: 255, nullable: false), + route_short_name = table.Column<string>(type: "varchar(32)", maxLength: 32, nullable: false), + route_long_name = table.Column<string>(type: "varchar(255)", maxLength: 255, nullable: false), + route_desc = table.Column<string>(type: "varchar(255)", maxLength: 255, nullable: true), + route_type = table.Column<int>(type: "int", nullable: false), + route_url = table.Column<string>(type: "varchar(255)", maxLength: 255, nullable: true), + route_color = table.Column<string>(type: "varchar(7)", maxLength: 7, nullable: true), + route_text_color = table.Column<string>(type: "varchar(7)", maxLength: 7, nullable: true), + route_sort_order = table.Column<int>(type: "int", nullable: false) + }, + constraints: table => + { + table.PrimaryKey("PK_routes", x => x.route_id); + table.ForeignKey( + name: "FK_routes_agencies_agency_id", + column: x => x.agency_id, + principalTable: "agencies", + principalColumn: "agency_id", + onDelete: ReferentialAction.Cascade); + }) + .Annotation("MySQL:Charset", "utf8mb4"); + + migrationBuilder.CreateTable( + name: "trips", + columns: table => new + { + trip_id = table.Column<string>(type: "varchar(32)", maxLength: 32, nullable: false), + route_id = table.Column<string>(type: "varchar(32)", maxLength: 32, nullable: false), + service_id = table.Column<string>(type: "varchar(32)", maxLength: 32, nullable: false), + trip_headsign = table.Column<string>(type: "varchar(255)", maxLength: 255, nullable: true), + trip_short_name = table.Column<string>(type: "varchar(255)", maxLength: 255, nullable: true), + direction_id = table.Column<int>(type: "int", nullable: false), + block_id = table.Column<string>(type: "varchar(32)", maxLength: 32, nullable: true), + shape_id = table.Column<string>(type: "varchar(32)", maxLength: 32, nullable: true), + trip_wheelchair_accessible = table.Column<int>(type: "int", nullable: false, defaultValue: 0), + trip_bikes_allowed = table.Column<int>(type: "int", nullable: false, defaultValue: 0) + }, + constraints: table => + { + table.PrimaryKey("PK_trips", x => x.trip_id); + table.ForeignKey( + name: "FK_trips_routes_route_id", + column: x => x.route_id, + principalTable: "routes", + principalColumn: "route_id", + onDelete: ReferentialAction.Cascade); + }) + .Annotation("MySQL:Charset", "utf8mb4"); + + migrationBuilder.CreateTable( + name: "stop_times", + columns: table => new + { + trip_id = table.Column<string>(type: "varchar(32)", maxLength: 32, nullable: false), + stop_sequence = table.Column<int>(type: "int", nullable: false), + arrival_time = table.Column<TimeOnly>(type: "varchar(8)", maxLength: 8, nullable: false), + departure_time = table.Column<TimeOnly>(type: "varchar(8)", maxLength: 8, nullable: false), + stop_id = table.Column<string>(type: "varchar(32)", maxLength: 32, nullable: false), + shape_dist_traveled = table.Column<double>(type: "double", nullable: true) + }, + constraints: table => + { + table.PrimaryKey("PK_stop_times", x => new { x.trip_id, x.stop_sequence }); + table.ForeignKey( + name: "FK_stop_times_stops_stop_id", + column: x => x.stop_id, + principalTable: "stops", + principalColumn: "stop_id", + onDelete: ReferentialAction.Cascade); + table.ForeignKey( + name: "FK_stop_times_trips_trip_id", + column: x => x.trip_id, + principalTable: "trips", + principalColumn: "trip_id", + onDelete: ReferentialAction.Cascade); + }) + .Annotation("MySQL:Charset", "utf8mb4"); + + migrationBuilder.CreateIndex( + name: "IX_routes_agency_id", + table: "routes", + column: "agency_id"); + + migrationBuilder.CreateIndex( + name: "IX_stop_times_stop_id", + table: "stop_times", + column: "stop_id"); + + migrationBuilder.CreateIndex( + name: "IX_trips_route_id", + table: "trips", + column: "route_id"); + } + + /// <inheritdoc /> + protected override void Down(MigrationBuilder migrationBuilder) + { + migrationBuilder.DropTable( + name: "calendar"); + + migrationBuilder.DropTable( + name: "calendar_dates"); + + migrationBuilder.DropTable( + name: "stop_times"); + + migrationBuilder.DropTable( + name: "stops"); + + migrationBuilder.DropTable( + name: "trips"); + + migrationBuilder.DropTable( + name: "routes"); + + migrationBuilder.DropTable( + name: "agencies"); + } + } +} diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Migrations/AppDbContextModelSnapshot.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Migrations/AppDbContextModelSnapshot.cs new file mode 100644 index 0000000..a77ecf5 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Migrations/AppDbContextModelSnapshot.cs @@ -0,0 +1,395 @@ +// <auto-generated /> +using System; +using Costasdev.Busurbano.Database; +using Costasdev.ServiceViewer; +using Costasdev.ServiceViewer.Data; +using Microsoft.EntityFrameworkCore; +using Microsoft.EntityFrameworkCore.Infrastructure; +using Microsoft.EntityFrameworkCore.Storage.ValueConversion; + +#nullable disable + +namespace Costasdev.Busurbano.Database.Migrations +{ + [DbContext(typeof(AppDbContext))] + partial class AppDbContextModelSnapshot : ModelSnapshot + { + protected override void BuildModel(ModelBuilder modelBuilder) + { +#pragma warning disable 612, 618 + modelBuilder + .HasAnnotation("ProductVersion", "9.0.8") + .HasAnnotation("Relational:MaxIdentifierLength", 64); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.Agency", b => + { + b.Property<string>("Id") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("agency_id"); + + b.Property<string>("Email") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("agency_email"); + + b.Property<string>("FareUrl") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("agency_fare_url"); + + b.Property<string>("Language") + .IsRequired() + .HasMaxLength(5) + .HasColumnType("varchar(5)") + .HasColumnName("agency_lang"); + + b.Property<string>("Name") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("agency_name"); + + b.Property<string>("Phone") + .HasMaxLength(30) + .HasColumnType("varchar(30)") + .HasColumnName("agency_phone"); + + b.Property<string>("Timezone") + .IsRequired() + .HasMaxLength(50) + .HasColumnType("varchar(50)") + .HasColumnName("agency_timezone"); + + b.Property<string>("Url") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("agency_url"); + + b.HasKey("Id"); + + b.ToTable("agencies"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.Calendar", b => + { + b.Property<string>("ServiceId") + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("service_id"); + + b.Property<DateOnly>("EndDate") + .HasColumnType("date") + .HasColumnName("end_date"); + + b.Property<bool>("Friday") + .HasColumnType("tinyint(1)") + .HasColumnName("friday"); + + b.Property<bool>("Monday") + .HasColumnType("tinyint(1)") + .HasColumnName("monday"); + + b.Property<bool>("Saturday") + .HasColumnType("tinyint(1)") + .HasColumnName("saturday"); + + b.Property<DateOnly>("StartDate") + .HasColumnType("date") + .HasColumnName("start_date"); + + b.Property<bool>("Sunday") + .HasColumnType("tinyint(1)") + .HasColumnName("sunday"); + + b.Property<bool>("Thursday") + .HasColumnType("tinyint(1)") + .HasColumnName("thursday"); + + b.Property<bool>("Tuesday") + .HasColumnType("tinyint(1)") + .HasColumnName("tuesday"); + + b.Property<bool>("Wednesday") + .HasColumnType("tinyint(1)") + .HasColumnName("wednesday"); + + b.HasKey("ServiceId"); + + b.ToTable("calendar"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.CalendarDate", b => + { + b.Property<string>("ServiceId") + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("service_id"); + + b.Property<DateOnly>("Date") + .HasColumnType("date") + .HasColumnName("date"); + + b.Property<int>("ExceptionType") + .HasColumnType("int") + .HasColumnName("exception_type"); + + b.HasKey("ServiceId", "Date"); + + b.ToTable("calendar_dates"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.Route", b => + { + b.Property<string>("Id") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("route_id"); + + b.Property<string>("AgencyId") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("agency_id"); + + b.Property<string>("Color") + .HasMaxLength(7) + .HasColumnType("varchar(7)") + .HasColumnName("route_color"); + + b.Property<string>("Description") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("route_desc"); + + b.Property<string>("LongName") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("route_long_name"); + + b.Property<string>("ShortName") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("route_short_name"); + + b.Property<int>("SortOrder") + .HasColumnType("int") + .HasColumnName("route_sort_order"); + + b.Property<string>("TextColor") + .HasMaxLength(7) + .HasColumnType("varchar(7)") + .HasColumnName("route_text_color"); + + b.Property<int>("Type") + .HasColumnType("int") + .HasColumnName("route_type"); + + b.Property<string>("Url") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("route_url"); + + b.HasKey("Id"); + + b.HasIndex("AgencyId"); + + b.ToTable("routes"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.Stop", b => + { + b.Property<string>("Id") + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("stop_id"); + + b.Property<string>("Code") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("stop_code"); + + b.Property<string>("Description") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("stop_desc"); + + b.Property<double>("Latitude") + .HasColumnType("double") + .HasColumnName("stop_lat"); + + b.Property<double>("Longitude") + .HasColumnType("double") + .HasColumnName("stop_lon"); + + b.Property<string>("Name") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("stop_name"); + + b.Property<string>("Timezone") + .HasMaxLength(50) + .HasColumnType("varchar(50)") + .HasColumnName("stop_timezone"); + + b.Property<string>("Url") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("stop_url"); + + b.Property<int>("WheelchairBoarding") + .HasColumnType("int") + .HasColumnName("wheelchair_boarding"); + + b.HasKey("Id"); + + b.ToTable("stops"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.StopTime", b => + { + b.Property<string>("TripId") + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("trip_id"); + + b.Property<int>("StopSequence") + .HasColumnType("int") + .HasColumnName("stop_sequence"); + + b.Property<TimeOnly>("ArrivalTime") + .HasMaxLength(8) + .HasColumnType("varchar(8)") + .HasColumnName("arrival_time"); + + b.Property<TimeOnly>("DepartureTime") + .HasMaxLength(8) + .HasColumnType("varchar(8)") + .HasColumnName("departure_time"); + + b.Property<double?>("ShapeDistTraveled") + .HasColumnType("double") + .HasColumnName("shape_dist_traveled"); + + b.Property<string>("StopId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("stop_id"); + + b.HasKey("TripId", "StopSequence"); + + b.HasIndex("StopId"); + + b.ToTable("stop_times"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.Trip", b => + { + b.Property<string>("TripId") + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("trip_id"); + + b.Property<string>("BlockId") + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("block_id"); + + b.Property<int>("DirectionId") + .HasColumnType("int") + .HasColumnName("direction_id"); + + b.Property<string>("RouteId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("route_id"); + + b.Property<string>("ServiceId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("service_id"); + + b.Property<string>("ShapeId") + .HasMaxLength(32) + .HasColumnType("varchar(32)") + .HasColumnName("shape_id"); + + b.Property<int>("TripBikesAllowed") + .ValueGeneratedOnAdd() + .HasColumnType("int") + .HasDefaultValue(0) + .HasColumnName("trip_bikes_allowed"); + + b.Property<string>("TripHeadsign") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("trip_headsign"); + + b.Property<string>("TripShortName") + .HasMaxLength(255) + .HasColumnType("varchar(255)") + .HasColumnName("trip_short_name"); + + b.Property<int>("TripWheelchairAccessible") + .ValueGeneratedOnAdd() + .HasColumnType("int") + .HasDefaultValue(0) + .HasColumnName("trip_wheelchair_accessible"); + + b.HasKey("TripId"); + + b.HasIndex("RouteId"); + + b.ToTable("trips"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.Route", b => + { + b.HasOne("Costasdev.Busurbano.Database.Gtfs.Agency", "Agency") + .WithMany() + .HasForeignKey("AgencyId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Agency"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.StopTime", b => + { + b.HasOne("Costasdev.Busurbano.Database.Gtfs.Stop", "Stop") + .WithMany() + .HasForeignKey("StopId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Costasdev.Busurbano.Database.Gtfs.Trip", "Trip") + .WithMany() + .HasForeignKey("TripId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Stop"); + + b.Navigation("Trip"); + }); + + modelBuilder.Entity("Costasdev.Busurbano.Database.Gtfs.Trip", b => + { + b.HasOne("Costasdev.Busurbano.Database.Gtfs.Route", null) + .WithMany() + .HasForeignKey("RouteId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); +#pragma warning restore 612, 618 + } + } +} diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/QueryExtensions/GtfsCalendarQueryExtensions.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/QueryExtensions/GtfsCalendarQueryExtensions.cs new file mode 100644 index 0000000..f5fc2bb --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Data/QueryExtensions/GtfsCalendarQueryExtensions.cs @@ -0,0 +1,21 @@ +using Costasdev.ServiceViewer.Data.Gtfs; + +namespace Costasdev.ServiceViewer.Data.QueryExtensions; + +public static class GtfsCalendarQueryExtensions +{ + public static IQueryable<GtfsCalendar> WhereDayOfWeek(this IQueryable<GtfsCalendar> 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/Costasdev.Busurbano.ServiceViewer/Program.cs b/src/Costasdev.Busurbano.ServiceViewer/Program.cs new file mode 100644 index 0000000..285afc2 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Program.cs @@ -0,0 +1,32 @@ +using Costasdev.ServiceViewer; +using Costasdev.ServiceViewer.Data; +using Microsoft.EntityFrameworkCore; + +var builder = WebApplication.CreateBuilder(args); + +builder.Services.AddControllersWithViews(); + +builder.Services.AddDbContext<AppDbContext>(db => +{ + var connectionString = builder.Configuration.GetConnectionString("Database"); + if (string.IsNullOrEmpty(connectionString)) + { + throw new InvalidOperationException("Connection string 'Database' is not configured."); + } + db.UseMySQL(connectionString); +}); + +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/Costasdev.Busurbano.ServiceViewer/Properties/launchSettings.json b/src/Costasdev.Busurbano.ServiceViewer/Properties/launchSettings.json new file mode 100644 index 0000000..34eab40 --- /dev/null +++ b/src/Costasdev.Busurbano.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/Costasdev.Busurbano.ServiceViewer/Views/Services/DaysInFeed.cshtml b/src/Costasdev.Busurbano.ServiceViewer/Views/Services/DaysInFeed.cshtml new file mode 100644 index 0000000..84f30a3 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Views/Services/DaysInFeed.cshtml @@ -0,0 +1,22 @@ +@model Costasdev.ServiceViewer.Views.Services.DaysInFeedModel +@{ + ViewData["Title"] = "Fechas con datos"; +} + +@section Head +{ + <link rel="stylesheet" href="~/styles/days_in_feed.css" /> +} + +<header> + <h1>Fechas con datos</h1> +</header> + +<main> + @foreach (var day in Model.Days) + { + <article> + <a asp-controller="Services" asp-action="ServicesInDay" asp-route-day="@day.ToString("yyyy-MM-dd")">@day.ToString("M")</a> + </article> + } +</main> diff --git a/src/Costasdev.Busurbano.ServiceViewer/Views/Services/DaysInFeed.cshtml.cs b/src/Costasdev.Busurbano.ServiceViewer/Views/Services/DaysInFeed.cshtml.cs new file mode 100644 index 0000000..02fe5b0 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Views/Services/DaysInFeed.cshtml.cs @@ -0,0 +1,7 @@ +namespace Costasdev.ServiceViewer.Views.Services; + +public class DaysInFeedModel +{ + public List<DateTime> Days { get; set; } = []; + public DateOnly Today { get; set; } +} diff --git a/src/Costasdev.Busurbano.ServiceViewer/Views/Services/ServiceDetails.cshtml b/src/Costasdev.Busurbano.ServiceViewer/Views/Services/ServiceDetails.cshtml new file mode 100644 index 0000000..8eae631 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Views/Services/ServiceDetails.cshtml @@ -0,0 +1,63 @@ +@using Costasdev.ServiceViewer.Data.Gtfs +@using Humanizer +@using Humanizer.Localisation +@model Costasdev.ServiceViewer.Views.Services.ServiceDetailsModel +@{ + ViewData["Title"] = Model.ServiceName; +} + +@section Head +{ + <link rel="stylesheet" href="~/styles/service_details.css" /> + <link rel="stylesheet" href="/stylesheets/routecolours.css" /> + <style> + + </style> +} + +<header> + <h1>@ViewData["Title"]</h1> +</header> + +<nav class="navigation-bar"> + <a asp-action="DaysInFeed">Feed Vitrasa</a> + > + <a asp-action="ServicesInDay" asp-route-day="@Model.Date.ToString("yyyy-MM-dd")"> + @Model.Date.ToString("dd 'de' MMMM 'de' yyyy") + </a> + > + <span>@Model.ServiceName</span> +</nav> + +<section id="service-cards"> + @foreach (ServiceDetailsItem item in Model.Items) + { + <article class="trip-container route-@item.SafeRouteId"> + <div class="trip-header"> + <div class="route">@item.ShortName</div> + <div class="headsign">@item.LongName</div> + <div class="distance"> + @item.TotalDistance + </div> + </div> + <div class="trip-details"> + <div class="trip-leg"> + <div class="trip-time">@item.FirstStopTime</div> + <div class="trip-stop">@item.FirstStopName</div> + </div> + <div class="trip-leg"> + <div class="trip-time">@item.LastStopTime</div> + <div class="trip-stop">@item.LastStopName</div> + </div> + </div> + <div class="trip-footer" > + <a class="trip-details-link">Ver detalle del viaje →</a> + </div> + </article> + } +</section> + +<footer> + Tiempo de conducción: @Model.TotalDrivingTime.Hours horas y @Model.TotalDrivingTime.Minutes minutos.<br /> + Distancia total: @Model.TotalDistanceKm +</footer> diff --git a/src/Costasdev.Busurbano.ServiceViewer/Views/Services/ServiceDetails.cshtml.cs b/src/Costasdev.Busurbano.ServiceViewer/Views/Services/ServiceDetails.cshtml.cs new file mode 100644 index 0000000..a89efae --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Views/Services/ServiceDetails.cshtml.cs @@ -0,0 +1,29 @@ +namespace Costasdev.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<ServiceDetailsItem> 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/Costasdev.Busurbano.ServiceViewer/Views/Services/ServicesInDay.cshtml b/src/Costasdev.Busurbano.ServiceViewer/Views/Services/ServicesInDay.cshtml new file mode 100644 index 0000000..a5ac66f --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Views/Services/ServicesInDay.cshtml @@ -0,0 +1,40 @@ +@model Costasdev.ServiceViewer.Views.Services.ServiceInDayModel +@{ + ViewData["Title"] = "Servicios a realizar en " + Model.Date.ToString("dd 'de' MMMM 'de' yyyy"); +} + +@section Head +{ + <link rel="stylesheet" href="~/styles/services_in_day.css" /> + <link rel="stylesheet" href="/stylesheets/routecolours.css" /> +} + +<header> + <h1> + @ViewData["Title"] + </h1> +</header> + +<section id="service-cards"> + @foreach (ServicesInDayItem card in Model.Items) + { + <article> + <header> + <a asp-action="ServiceDetails" asp-route-day="@Model.Date.ToString("yyyy-MM-dd")" asp-route-serviceId="@card.ServiceId"> + @card.ServiceName + </a> + </header> + <main> + @card.ShiftStart → @card.ShiftEnd + </main> + <footer> + @foreach (var cardTripGroup in card.TripGroups) + { + <span class="route-group route-@cardTripGroup.route.SafeId"> + @cardTripGroup.route.ShortName (@cardTripGroup.count) + </span> + } + </footer> + </article> + } +</section> diff --git a/src/Costasdev.Busurbano.ServiceViewer/Views/Services/ServicesInDay.cshtml.cs b/src/Costasdev.Busurbano.ServiceViewer/Views/Services/ServicesInDay.cshtml.cs new file mode 100644 index 0000000..b0c57c3 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Views/Services/ServicesInDay.cshtml.cs @@ -0,0 +1,39 @@ +using Costasdev.ServiceViewer.Data.Gtfs; + +namespace Costasdev.ServiceViewer.Views.Services; + +public class ServiceInDayModel +{ + public List<ServicesInDayItem> Items { get; set; } = []; + public DateOnly Date { get; set; } +} + +public class ServicesInDayItem +{ + public string ServiceId { get; set; } + public string ServiceName { get; set; } + public List<GtfsTrip> Trips { get; set; } + public List<TripGroup> TripGroups { get; set; } + + public string ShiftStart { get; set; } + public string ShiftEnd { get; set; } + + public ServicesInDayItem( + string serviceId, + string serviceName, + List<GtfsTrip> trips, + List<TripGroup> 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/Costasdev.Busurbano.ServiceViewer/Views/Shared/_Layout.cshtml b/src/Costasdev.Busurbano.ServiceViewer/Views/Shared/_Layout.cshtml new file mode 100644 index 0000000..88d5b83 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Views/Shared/_Layout.cshtml @@ -0,0 +1,22 @@ +<!DOCTYPE html> +<html lang="es"> +<head> + <meta charset="UTF-8"> + + <meta name="viewport" content="width=device-width, initial-scale=1.0"> + <meta rel="robots" content="noindex, nofollow"> + + <meta charset="utf-8"/> + <meta name="viewport" content="width=device-width, initial-scale=1.0"/> + <title>@ViewData["Title"] - VentaSync</title> + + <link rel="stylesheet" href="https://fonts.bunny.net/css?family=inter:300,400,700"> + <link rel="stylesheet" href="~/styles/common.css" /> + @await RenderSectionAsync("Head", required: false) +</head> + +<body> + +@RenderBody() +</body> +</html> diff --git a/src/Costasdev.Busurbano.ServiceViewer/Views/_ViewImports.cshtml b/src/Costasdev.Busurbano.ServiceViewer/Views/_ViewImports.cshtml new file mode 100644 index 0000000..785dc40 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Views/_ViewImports.cshtml @@ -0,0 +1,2 @@ +@using Costasdev.ServiceViewer.Views.Services +@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers diff --git a/src/Costasdev.Busurbano.ServiceViewer/Views/_ViewStart.cshtml b/src/Costasdev.Busurbano.ServiceViewer/Views/_ViewStart.cshtml new file mode 100644 index 0000000..a5f1004 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/Views/_ViewStart.cshtml @@ -0,0 +1,3 @@ +@{ + Layout = "_Layout"; +} diff --git a/src/Costasdev.Busurbano.ServiceViewer/appsettings.json b/src/Costasdev.Busurbano.ServiceViewer/appsettings.json new file mode 100644 index 0000000..10f68b8 --- /dev/null +++ b/src/Costasdev.Busurbano.ServiceViewer/appsettings.json @@ -0,0 +1,9 @@ +{ + "Logging": { + "LogLevel": { + "Default": "Information", + "Microsoft.AspNetCore": "Warning" + } + }, + "AllowedHosts": "*" +} diff --git a/src/Costasdev.Busurbano.ServiceViewer/wwwroot/styles/common.css b/src/Costasdev.Busurbano.ServiceViewer/wwwroot/styles/common.css new file mode 100644 index 0000000..a0e0750 --- /dev/null +++ b/src/Costasdev.Busurbano.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/Costasdev.Busurbano.ServiceViewer/wwwroot/styles/days_in_feed.css b/src/Costasdev.Busurbano.ServiceViewer/wwwroot/styles/days_in_feed.css new file mode 100644 index 0000000..b3c46d1 --- /dev/null +++ b/src/Costasdev.Busurbano.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/Costasdev.Busurbano.ServiceViewer/wwwroot/styles/service_details.css b/src/Costasdev.Busurbano.ServiceViewer/wwwroot/styles/service_details.css new file mode 100644 index 0000000..570de3a --- /dev/null +++ b/src/Costasdev.Busurbano.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/Costasdev.Busurbano.ServiceViewer/wwwroot/styles/services_in_day.css b/src/Costasdev.Busurbano.ServiceViewer/wwwroot/styles/services_in_day.css new file mode 100644 index 0000000..ce847ef --- /dev/null +++ b/src/Costasdev.Busurbano.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; +} |