I'm not sure where to begin with this one. I have a database table called Trip. In this table, there is the following info on each row of data:
TripId
ClientName
PickUpDateTime
PickUpLocation
PickUpDriver
PickUpVehicle
ApptDateTime
ApptLocation
ReturnDateTime
ReturnLocation
ReturnDriver
ReturnVehicle
The user would like a Driver Schedule that has all of the "from" info in one column and all of the "to" info in another column.
Effectively speaking, this means they would like Pickup info and the Appt info in the same column (as you are coming "from" the Pickup and then when you are done with the Appt, you would be coming "from" the Appt.) The same goes for the "to" info. Your are going "to" an appt and then you are going "to" the return place.
Since the entire trip is stored in one row of the database, Trip, how to I do this?
I tried making 3 Iqueryables for temporary tables and naming all fields in 2 Iqueryables the same, but then when I join them with a 3rd Iqueryable, I don't know how to get the query to look at both "FromDateTime"s, for example. Here is what I mean:
Iqueryables:
IQueryable<ReportModelPU> scheduledTripsPU = from x in _context.Trip
join y in _context.Client on x.ClientName equals y.DisplayName
select new ReportModelPU
{
ClientName = y.DisplayName,
Driver = x.PickUpDriver,
Vehicle = x.PickUpVehicle,
FromDateTime = x.PickUpDateTime,
FromLocation = x.PickUpLocation,
ToDateTime = x.ApptDateTime,
ToLocation = x.ApptLocation,...
IQueryable<ReportModelRT> scheduledTripsRT = from x in _context.Trip
join y in _context.Client on x.ClientName equals y.DisplayName
select new ReportModelRT
{
ClientName = y.DisplayName,
Driver = x.ReturnDriver,
Vehicle = x.ReturnVehicle,
FromDateTime = x.ReturnDateTime,
FromLocation = x.ApptLocation,
ToLocation = x.ReturnLocation,...
IQueryable<ReportModel> scheduledTripsALL = from x in scheduledTripsPU
where (x.FromDateTime >= v && x.FromDateTime <= to && x.Driver == selDriver) || (x.FromDateTime >= v && x.FromDateTime <= to && x.Driver == selDriver)
join y in scheduledTripsRT on x.ClientName equals y.ClientName
where (y.FromDateTime >= v && y.FromDateTime <= to && y.Driver == selDriver) || (y.FromDateTime >= v && y.FromDateTime <= to && y.Driver == selDriver)
select new ReportModel
{
ClientName = x.ClientName,
FromDateTime = x.FromDateTime || y.FromDateTime,
FromLocation = x.FromLocation || y.FromLocation,...
Hope that makes sense. User would like this resulting report to be in chronological order so the driver can just read down the page and know the next leg in the journey. NOTE: The pickup driver and return driver may be different for a single client. So, Driver John Doe might pick up Amy at 8:30am and drop her off at a 9am appt, then go to pick up Nellie to drop her off somewhere else, etc. All of these times need to be in order of TIME, not client.
Any ideas would be most welcome. Would it be possible to concatenate these queries? I am not an advanced programmer and just manage to muddle through the simple stuff. Thanks!
UPDATE: Made some progress and actually got a working query.Changed and to just .
I then edited my IQueryable scheduledTripsALL to be a concat of the first 2 queries:
Queryable<ReportModel> scheduledTripsALL = scheduledTripsPU.Concat(scheduledTripsRT);
My only issue now is that while the report will work, if the original Trip table is edited, the report then doesn't return any values. Thinking there must be some type of reset or refresh I should be doing? If you know, please tell me. Struggling with this part.
EDITED TO ADD: Index.cshtml:
@page
@model Transport.Pages.Reports.DriverSchedule.IndexModel
@{
ViewData["Title"] = "Daily Driver Schedule";
}
<h1>Daily Driver Schedule</h1>
<form asp-page="./Index" method="get">
<div class="form-actions no-color">
<p>
<h5>Report Date Range</h5>
<div class="col-25">
<table class="table table-borderless" style="width:100%">
<tr width="100%">
<td width="33%" style="text-align: left">
Input Report Date: <input asp-for="ReportDate" />
</td>
<td align="left" width="34%" style="text-align: left">
<select asp-for="ScheduledTripsALL" name="selDriver" id="selDriver" class="form-select" asp-items="@(new SelectList(Model.DisplayDriverData.OrderBy(x => x.DisplayName),"DisplayName", "DisplayName"))"><option value="" selected disabled>---Select Driver---</option></select>
</td>
</tr>
</table>
<input type="submit" value="Filter Report" class="btn btn-primary mx-2" />
<a asp-page="./Index" class="btn btn-link mx-2">Clear Dates</a>
</div>
</p>
</div>
</form>
<div id="PrintThis" style="width:contain; overflow-x:auto">
<div class="col-25">
<h2 class="text-primary" align="center">Ontario County Transportation Scheduling</h2>
<h5 class="text-primary" align="center">Daily Driver Schedule</h5>
<h5 class="text-primary" align="center">@ViewData["startparameter"]</h5>
<h5 class="text-primary" align="center">@ViewData["driverparameter"]</h5>
</div>
<br />
<table class="table table-bordered" style="width:100%">
<thead>
<tr>
<th style="text-align: left" width="25%">
Client Data
</th>
<th style="text-align: left" width="25%">
From Data
</th>
<th style="text-align: left" width="25%">
To Data
</th>
</tr>
</thead>
<tbody>
@foreach(var item in Model.ScheduledTripsALL.OrderBy(i =>i.FromDateTime))
{
<tr width="100%">
<td width="25%" style="text-align: left">
<b>@Html.DisplayNameFor(modelItem => item.ClientName):</b> @Html.DisplayFor(modelItem => item.ClientName) <br/>
</td>
<td width="25%" style="text-align: left">
<b>Time:</b> <b><font color="red">@Convert.ToString(string.Format("{0:hh:mm tt}", item.FromDateTime))</font></b><br />
<b>Address:</b><br />
@Html.DisplayFor(modelItem => item.FromLocation)@Html.DisplayFor(modelItem => item.FromNonSiteLocation)<br />
@Html.DisplayFor(modelItem => item.FromAddr1)<br />
@Html.DisplayFor(modelItem => item.FromAddr2)<br />
@Html.DisplayFor(modelItem => item.FromCity)<br />
</td>
<td width="25%" style="text-align: left">
<b>Time:</b> <b>
<font color="red">
@if (item.ToDateTime == item.FromDateTime)
{
}
else
{
@Convert.ToString(string.Format("{0:hh:mm tt}", item.ToDateTime))
}
</font></b><br />
<b>Address:</b><br />
@Html.DisplayFor(modelItem => item.ToLocation)@Html.DisplayFor(modelItem => item.ToNonSiteLocation)<br />
@Html.DisplayFor(modelItem => item.ToAddr1)<br />
@Html.DisplayFor(modelItem => item.ToAddr2)<br />
@Html.DisplayFor(modelItem => item.ToCity)<br />
</td>
</tr>
}
</tbody>
</table>
</div>
<button type="submit" onclick="printDiv('PrintThis')" class="btn btn-primary" style="width:300px;">Print (Select Landscape)</button>
<br />
<br />
<div>
@{
var prevDisabled = !Model.ScheduledTripsALL.HasPreviousPage ? "disabled" : "";
var nextDisabled = !Model.ScheduledTripsALL.HasNextPage ? "disabled" : "";
}
<a asp-page="./Index"
asp-route-pageIndex="@(Model.ScheduledTripsALL.PageIndex - 1)"
asp-route-reportDate="@Model.ReportDate"
class="btn btn-primary @prevDisabled">
Previous
</a>
<br />
<br />
<a asp-page="./Index"
asp-route-pageIndex="@(Model.ScheduledTripsALL.PageIndex + 1)"
asp-route-reportDate="@Model.ReportDate"
class="btn btn-primary @nextDisabled">
Next
</a>
</div>
@section Scripts
{
<script language="javascript">
function printDiv(divName) {
var printContents = document.getElementById(divName).innerHTML;
var originalContents = document.body.innerHTML;
document.body.innerHTML = printContents;
window.print();
document.body.innerHTML = originalContents;
}
</script>
And the Index.cs:
using Transport.Data;
using Transport.Model;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using Microsoft.AspNetCore.Mvc;
using System.ComponentModel.DataAnnotations;
namespace Transport.Pages.Reports.DriverSchedule;
public class IndexModel : PageModel
{
private readonly ApplicationDbContext _db;
public IndexModel(ApplicationDbContext db)
{
_db = db;
}
public Trip Trip { get; set; }
public Client Client { get; set; }
public Driver Driver { get; set; }
[BindProperty(SupportsGet = true), DataType(DataType.Date), DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
public DateTime ReportDate { get; set; }
public string? DriverName { get; set; }
public string? DriverPhone { get; set; }
public IEnumerable<Driver> DisplayDriverData { get; set; }
public PaginatedList<ReportModel> ScheduledTripsPU { get; set; } = default!;
public PaginatedList<ReportModel> ScheduledTripsRT { get; set; } = default!;
public PaginatedList<ReportModel> ScheduledTripsALL { get; set; } = default!;
public async Task OnGet(string? selDriver, int? pageIndex)
{
var from = ReportDate;
var v = from;
var s = from.ToShortDateString();
var to = ReportDate.AddDays(1);
DriverName = selDriver;
await _db.Driver.Select(a => a.DriverId).ToListAsync();
DisplayDriverData = await _db.Driver.ToListAsync();
IQueryable<ReportModel> scheduledTripsPU = (IQueryable<ReportModel>)(from x in _db.Trip
join y in _db.Client on x.ClientName equals y.DisplayName
where (x.PickUpDateTime >= v && x.PickUpDateTime <= to && x.PickUpDriver == selDriver) || (x.PickUpDateTime >= v && x.PickUpDateTime <= to && x.PickUpDriver == selDriver)
orderby x.PickUpDateTime
select new ReportModel
{
ClientName = y.DisplayName,
ClientSex = y.Gender,
ClientAge = y.Age,
ClientAgeCategory = y.Category,
ClientCarSeat = y.CarSeat,
ClientWeight = y.Weight,
ClientCaseWorker = y.CaseWorkerName,
ClientCaseWorkerPhone = y.CaseWorkerPhone,
Driver = x.PickUpDriver,
VehiclePhone = x.PickUpVehiclePhone,
Vehicle = x.PickUpVehicle,
FromDateTime = x.PickUpDateTime,
FromLocation = x.PickUpLocation,
FromNonSiteLocation = x.NonSitePickUpLocation,
FromAddr1 = x.PickUpAddr1,
FromAddr2 = x.PickUpAddr2,
FromCity = x.PickUpCity,
FromDirections = x.PickUpDirections,
FromPhone = x.PickUpPhone,
ToDateTime = x.ApptDateTime,
ToLocation = x.ApptLocation,
ToNonSiteLocation = x.NonSiteApptLocation,
ToAddr1 = x.ApptAddr1,
ToAddr2 = x.ApptAddr2,
ToCity = x.ApptCity,
ToDirections = x.ApptDirections,
ToPhone = x.ApptPhone,
}); ;
IQueryable<ReportModel> scheduledTripsRT = (IQueryable<ReportModel>)(from x in _db.Trip
join y in _db.Client on x.ClientName equals y.DisplayName
where (x.ReturnDateTime >= v && x.ReturnDateTime <= to) || (x.ReturnDateTime >= v && x.ReturnDateTime <= to)
orderby x.ReturnDateTime
select new ReportModel
{
ClientName = y.DisplayName,
ClientSex = y.Gender,
ClientAge = y.Age,
ClientAgeCategory = y.Category,
ClientCarSeat = y.CarSeat,
ClientWeight = y.Weight,
ClientCaseWorker = y.CaseWorkerName,
ClientCaseWorkerPhone = y.CaseWorkerPhone,
Driver = x.ReturnDriver,
VehiclePhone = x.ReturnVehiclePhone,
Vehicle = x.ReturnVehicle,
FromDateTime = x.ReturnDateTime,
FromLocation = x.ApptLocation,
FromNonSiteLocation = x.ApptLocation,
FromAddr1 = x.ApptAddr1,
FromAddr2 = x.ApptAddr2,
FromCity = x.ApptCity,
FromDirections = x.ApptDirections,
FromPhone = x.ApptPhone,
ToDateTime = x.ReturnDateTime,
ToLocation = x.ReturnLocation,
ToNonSiteLocation = x.NonSiteReturnLocation,
ToAddr1 = x.ReturnAddr1,
ToAddr2 = x.ReturnAddr2,
ToCity = x.ReturnCity,
ToDirections = x.ReturnDirections,
ToPhone = x.ReturnPhone,
}); ;
IQueryable<ReportModel> scheduledTripsALL = scheduledTripsPU.Concat(scheduledTripsRT);
int casesPerPage = 20;
ScheduledTripsALL = await PaginatedList<ReportModel>.CreateAsync(
scheduledTripsALL, pageIndex ?? 1, casesPerPage);
ViewData["startparameter"] = $"Selected Report Date: {s}";
ViewData["driverparameter"] = $"Driver: {selDriver}";
}
}
I shortened the page contents just for levity, so that is why there are more fields in the .cs queries.
Your solution does not maintain a live connection to the original data sources. This means that if the underlying data in the Trip table changes, those changes won't automatically reflect in your report. To ensure that your report reflects any changes made to the Trip table, you need to re-query the data each time you generate the report.
or if you want to dynamically update the report when the table updates, for that what you can do is:
- Periodically Refresh report: (polling Event) For this you can create a function which gets called on small intervals and re-generates report, or updates part of report: (sample code example)
using System;
using System.Linq;
using System.Threading;
class Program
{
static IQueryable<ReportModel> scheduledTripsALL; // Define this variable at a broader scope
static void Main()
{
scheduledTripsALL = RefreshReport(null); // Initialize scheduledTripsALL
Timer timer = new Timer(RefreshReport, null, TimeSpan.Zero, TimeSpan.FromMinutes(5));
Thread.Sleep(Timeout.Infinite);
}
static void RefreshReport(object state)
{
scheduledTripsALL = scheduledTripsPU.Concat(scheduledTripsRT);
}
}
- Refresh Report on trip table update: For this you can create a database trigger and function which will notify on table update and you can capture that event and refresh your report accordingly:
CREATE OR REPLACE FUNCTION notify_trip_change()
RETURNS trigger AS
$BODY$
BEGIN
PERFORM pg_notify('trip_change', '');
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER trip_trigger
AFTER INSERT OR UPDATE OR DELETE
ON Trip
FOR EACH ROW
EXECUTE FUNCTION notify_trip_change();
using System;
using Npgsql;
class Program
{
static void Main()
{
var connString = "Host=myhost;Username=myuser;Password=mypass;Database=mydb";
using var conn = new NpgsqlConnection(connString);
conn.Open();
conn.Notification += (sender, args) =>
{
if (args.Payload == "trip_change")
{
RefreshReport();
}
};
using var cmd = new NpgsqlCommand("LISTEN trip_change", conn);
cmd.ExecuteNonQuery();
}
static void RefreshReport(object state)
{
scheduledTripsALL = scheduledTripsPU.Concat(scheduledTripsRT);
}
}
Hope this is what you are looking for.
EDIT You can create a new function to periodically refresh your report, lets say every 5 minutes:
public async Task<IActionResult> OnGetRefreshDataAsync(string? selDriver)
{
var from = ReportDate;
var to = ReportDate.AddDays(1);
IQueryable<ReportModel> scheduledTripsPU = (IQueryable<ReportModel>)(
from x in _db.Trip
join y in _db.Client on x.ClientName equals y.DisplayName
where (x.PickUpDateTime >= v && x.PickUpDateTime <= to && x.PickUpDriver == selDriver) || (x.PickUpDateTime >= v && x.PickUpDateTime <= to && x.PickUpDriver == selDriver)orderby x.PickUpDateTime
select new ReportModel
{
ClientName = y.DisplayName,
ClientSex = y.Gender,
ClientAge = y.Age,
ClientAgeCategory = y.Category,
ClientCarSeat = y.CarSeat,
ClientWeight = y.Weight,
ClientCaseWorker = y.CaseWorkerName,
ClientCaseWorkerPhone = y.CaseWorkerPhone,
Driver = x.PickUpDriver,
VehiclePhone = x.PickUpVehiclePhone,
Vehicle = x.PickUpVehicle,
FromDateTime = x.PickUpDateTime,
FromLocation = x.PickUpLocation,
FromNonSiteLocation = x.NonSitePickUpLocation,
FromAddr1 = x.PickUpAddr1,
FromAddr2 = x.PickUpAddr2,
FromCity = x.PickUpCity,
FromDirections = x.PickUpDirections,
FromPhone = x.PickUpPhone,
ToDateTime = x.ApptDateTime,
ToLocation = x.ApptLocation,
ToNonSiteLocation = x.NonSiteApptLocation,
ToAddr1 = x.ApptAddr1,
ToAddr2 = x.ApptAddr2,
ToCity = x.ApptCity,
ToDirections = x.ApptDirections,
ToPhone = x.ApptPhone,
}
);
IQueryable<ReportModel> scheduledTripsRT = (IQueryable<ReportModel>)(
from x in _db.Trip
join y in _db.Client on x.ClientName equals y.DisplayName
where (x.ReturnDateTime >= v && x.ReturnDateTime <= to) || (x.ReturnDateTime >= v && x.ReturnDateTime <= to)orderby x.ReturnDateTime
select new ReportModel
{
ClientName = y.DisplayName,
ClientSex = y.Gender,
ClientAge = y.Age,
ClientAgeCategory = y.Category,
ClientCarSeat = y.CarSeat,
ClientWeight = y.Weight,
ClientCaseWorker = y.CaseWorkerName,
ClientCaseWorkerPhone = y.CaseWorkerPhone,
Driver = x.ReturnDriver,
VehiclePhone = x.ReturnVehiclePhone,
Vehicle = x.ReturnVehicle,
FromDateTime = x.ReturnDateTime,
FromLocation = x.ApptLocation,
FromNonSiteLocation = x.ApptLocation,
FromAddr1 = x.ApptAddr1,
FromAddr2 = x.ApptAddr2,
FromCity = x.ApptCity,
FromDirections = x.ApptDirections,
FromPhone = x.ApptPhone,
ToDateTime = x.ReturnDateTime,
ToLocation = x.ReturnLocation,
ToNonSiteLocation = x.NonSiteReturnLocation,
ToAddr1 = x.ReturnAddr1,
ToAddr2 = x.ReturnAddr2,
ToCity = x.ReturnCity,
ToDirections = x.ReturnDirections,
ToPhone = x.ReturnPhone,
}
);
;
var scheduledTripsALL = await scheduledTripsPU.Concat(scheduledTripsRT).ToListAsync();
ViewData["startparameter"] = $"Selected Report Date: {s}";
ViewData["driverparameter"] = $"Driver: {selDriver}";
return new JsonResult(scheduledTripsALL);
}
and to call this function add code in your scipts section on your page:
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script>
function fetchData() {
var selDriver = $('#selDriver').val();
$.ajax({
url: '@Url.Page("./Index", "RefreshData")',
data: { selDriver: selDriver },
success: function(data) {
updateUI(data);
},
error: function(err) {
console.error("Error fetching data:", err);
}
});
}
function updateUI(data) {
var tableBody = $('#PrintThis tbody');
tableBody.empty();
data.forEach(function(item) {
var fromDateTime = new Date(item.FromDateTime).toLocaleTimeString([], { hour: '2-digit', minute: '2-digit' });
var toDateTime = new Date(item.ToDateTime).toLocaleTimeString([], { hour: '2-digit', minute: '2-digit' });
var row = `
<tr width="100%">
<td width="25%" style="text-align: left">
<b>Client Name:</b> ${item.ClientName} <br/>
</td>
<td width="25%" style="text-align: left">
<b>Time:</b> <b><font color="red">${fromDateTime}</font></b><br />
<b>Address:</b><br />
${item.FromLocation}${item.FromNonSiteLocation}<br />
${item.FromAddr1}<br />
${item.FromAddr2}<br />
${item.FromCity}<br />
</td>
<td width="25%" style="text-align: left">
<b>Time:</b> <b><font color=" red">${toDateTime}</font></b><br />
<b>Address:</b><br />
${item.ToLocation}${item.ToNonSiteLocation}<br />
${item.ToAddr1}<br />
${item.ToAddr2}<br />
${item.ToCity}<br />
</td>
</tr>`;
tableBody.append(row);
});
}
setInterval(fetchData, 5 * 60 * 1000);
$(document).ready(function() {
fetchData();
});
function printDiv(divName) {
var printContents = document.getElementById(divName).innerHTML;
var originalContents = document.body.innerHTML;
document.body.innerHTML = printContents;
window.print();
document.body.innerHTML = originalContents;
}
</script>
EDIT In order to pursue Refresh Report on trip table update approach:
Create PostgreSQL trigger and function in your database environment with the queries i mentioned above.
Install SignalR Package:
dotnet add package Microsoft.AspNetCore.SignalR
Create a hub to manage the communication with the clients:
using Microsoft.AspNetCore.SignalR; using System.Threading.Tasks;
public class TripChangeHub : Hub { public async Task NotifyTripChange() { await Clients.All.SendAsync("TripChanged"); } }
Configure SignalR in Startup.cs:
public class Startup { public void ConfigureServices(IServiceCollection services) { services.AddSignalR(); services.AddSingleton(); }
public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
app.UseSignalR(routes =>
{
routes.MapHub<TripChangeHub>("/tripChangeHub");
});
}
}
Modify Your Background Service to Use SignalR: Inject IHubContext into your TripChangeListener service and use it to notify clients when a change is detected.
using Microsoft.Extensions.Hosting; using Microsoft.Extensions.Logging; using Microsoft.AspNetCore.SignalR; using Npgsql; using System; using System.Threading; using System.Threading.Tasks; using Transport.Data; using Microsoft.EntityFrameworkCore; using System.Linq;
public class TripChangeListener : BackgroundService { private readonly ApplicationDbContext _db; private readonly ILogger _logger; private readonly IHubContext _hubContext; private readonly string _connectionString;
public TripChangeListener(ApplicationDbContext db, ILogger<TripChangeListener> logger, IHubContext<TripChangeHub> hubContext, string connectionString)
{
_db = db;
_logger = logger;
_hubContext = hubContext;
_connectionString = connectionString;
}
protected override async Task ExecuteAsync(CancellationToken stoppingToken)
{
await Task.Yield(); // To avoid blocking the calling thread
try
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync(stoppingToken);
conn.Notification += async (o, e) => {
if (e.Condition == "trip_change")
{
_logger.LogInformation("Trip change detected. Refreshing report.");
await RefreshReportAsync();
await _hubContext.Clients.All.SendAsync("TripChanged");
}
};
await using var listenCmd = new NpgsqlCommand("LISTEN trip_change;", conn);
await listenCmd.ExecuteNonQueryAsync(stoppingToken);
_logger.LogInformation("Listening for trip changes.");
while (!stoppingToken.IsCancellationRequested)
{
await conn.WaitAsync(stoppingToken); // This will block until a notification is received
}
}
catch (Exception ex)
{
_logger.LogError(ex, "An error occurred while listening for trip changes.");
}
}
private async Task RefreshReportAsync()
{
var reportDate = DateTime.Today;
var from = reportDate;
var to = reportDate.AddDays(1);
IQueryable scheduledTripsPU = (IQueryable)( from x in _db.Trip join y in _db.Client on x.ClientName equals y.DisplayName where (x.PickUpDateTime >= v && x.PickUpDateTime <= to && x.PickUpDriver == selDriver) || (x.PickUpDateTime >= v && x.PickUpDateTime <= to && x.PickUpDriver == selDriver)orderby x.PickUpDateTime select new ReportModel { ClientName = y.DisplayName, ClientSex = y.Gender, ClientAge = y.Age, ClientAgeCategory = y.Category, ClientCarSeat = y.CarSeat, ClientWeight = y.Weight, ClientCaseWorker = y.CaseWorkerName, ClientCaseWorkerPhone = y.CaseWorkerPhone, Driver = x.PickUpDriver, VehiclePhone = x.PickUpVehiclePhone, Vehicle = x.PickUpVehicle, FromDateTime = x.PickUpDateTime, FromLocation = x.PickUpLocation, FromNonSiteLocation = x.NonSitePickUpLocation, FromAddr1 = x.PickUpAddr1, FromAddr2 = x.PickUpAddr2, FromCity = x.PickUpCity, FromDirections = x.PickUpDirections, FromPhone = x.PickUpPhone, ToDateTime = x.ApptDateTime, ToLocation = x.ApptLocation, ToNonSiteLocation = x.NonSiteApptLocation, ToAddr1 = x.ApptAddr1, ToAddr2 = x.ApptAddr2, ToCity = x.ApptCity, ToDirections = x.ApptDirections, ToPhone = x.ApptPhone, }
);
IQueryable<ReportModel> scheduledTripsRT = (IQueryable<ReportModel>)(
from x in _db.Trip
join y in _db.Client on x.ClientName equals y.DisplayName
where (x.ReturnDateTime >= v && x.ReturnDateTime <= to) || (x.ReturnDateTime >= v && x.ReturnDateTime <= to)orderby x.ReturnDateTime
select new ReportModel
{
ClientName = y.DisplayName,
ClientSex = y.Gender,
ClientAge = y.Age,
ClientAgeCategory = y.Category,
ClientCarSeat = y.CarSeat,
ClientWeight = y.Weight,
ClientCaseWorker = y.CaseWorkerName,
ClientCaseWorkerPhone = y.CaseWorkerPhone,
Driver = x.ReturnDriver,
VehiclePhone = x.ReturnVehiclePhone,
Vehicle = x.ReturnVehicle,
FromDateTime = x.ReturnDateTime,
FromLocation = x.ApptLocation,
FromNonSiteLocation = x.ApptLocation,
FromAddr1 = x.ApptAddr1,
FromAddr2 = x.ApptAddr2,
FromCity = x.ApptCity,
FromDirections = x.ApptDirections,
FromPhone = x.ApptPhone,
ToDateTime = x.ReturnDateTime,
ToLocation = x.ReturnLocation,
ToNonSiteLocation = x.NonSiteReturnLocation,
ToAddr1 = x.ReturnAddr1,
ToAddr2 = x.ReturnAddr2,
ToCity = x.ReturnCity,
ToDirections = x.ReturnDirections,
ToPhone = x.ReturnPhone,
}
);
var scheduledTripsALL = scheduledTripsPU.Concat(scheduledTripsRT).ToList();
// You might need to store it in a shared location or signal a refresh to the UI
}
}
Set Up the Front-End to Listen for Notifications.
Hope this helps