public CachedTrainDetails GetActivatedTrainMovementByHeadcodeAndStop(string headcode, DateTime date, string stanox)
{
// get tiploc id to improve query
var tiplocs = _tiplocRepository.GetTiplocsByStanox(stanox)
.Select(t => t.TiplocId)
.ToArray();
if (!tiplocs.Any())
return null;
const string getSchedulesSql = @"
SELECT
[LiveTrain].[Id],
[LiveTrain].[ScheduleTrainUid] AS [TrainUid],
[LiveTrain].[OriginDepartTimestamp]
FROM [LiveTrain]
INNER JOIN [ScheduleTrain] ON [ScheduleTrain].[ScheduleId] = [LiveTrain].[ScheduleTrain]
INNER JOIN [ScheduleTrainStop] ON [ScheduleTrainStop].[ScheduleId] = [ScheduleTrain].[ScheduleId]
WHERE [ScheduleTrainStop].[TiplocId] IN @tiplocs
AND [ScheduleTrain].[Headcode] = @headcode
AND [ScheduleTrain].[Runs{0}] = 1
AND @date >= [ScheduleTrain].[StartDate]
AND @date <= [ScheduleTrain].[EndDate]
ORDER BY [OriginDepartTimeStamp] DESC";
return Query<CachedTrainDetails>(string.Format(getSchedulesSql, date.DayOfWeek), new
{
headcode,
date = date.Date,
tiplocs
})
.FirstOrDefault();
}