I'm building an MVC web app with ASP.CORE 3.1 and EF connected to MSSQL.
Today, finally after 3 days, I've somewhat reached a working code for filtering my view.
I've got database of
- Date stamps (15 minute intervals) and
- INT values of cars (3 types (OA, NA, NS))
- going through Border (13 of them).
- Data are for both directions (Plus, Minus).
- I need to filter this by above plus change time interval (60, 30, 15 minutes)
- and by Weekdays (Monday, Friday, Sat, Sun, and Ordinary (Tuesday-Thursday)).
But I'm doing filtering for the first time in my life here. Happy it works but would love to hear how can I improve this. Dataset is not big. Around 17 columns x 300 000 rows.
Visualization
Model
Borders.cs
public class Borders
{
[Key]
public int Id { get; set; }
public int TransitId { get; set; }
public DateTime Day { get; set; }
public DateTime Start { get; set; }
public DateTime End { get; set; }
public int OA_Plus { get; set; }
public int NA_Plus { get; set; }
public int NS_Plus { get; set; }
public int ALL_Plus { get; set; }
public int OA_Minus { get; set; }
public int NA_Minus { get; set; }
public int NS_Minus { get; set; }
public int ALL_Minus { get; set; }
public int OA_Sum { get; set; }
public int NA_Sum { get; set; }
public int NS_Sum { get; set; }
public int ALL_Sum { get; set; }
}
ViewModel
BordersViewModel.cs
public class BordersViewModel
{
// DB items
public IEnumerable<Borders> Borders { get; set; }
// Other ViewModels
public IEnumerable<ChartIntensityVM> ChartIntensityVM { get; set; }
// Contains only String Key, Int Value
// Filters
public string FilterTransitNumber { get; set; } = "All";
public string FilterSeason { get; set; } = "All";
public string FilterDay { get; set; } = "All";
public string FilterTimeInterval { get; set; } = "1";
public string FilterDirection { get; set; } = "All";
public bool FilterChkboxOA { get; set; } = true;
public bool FilterChkboxNA { get; set; } = true;
public bool FilterChkboxNS { get; set; } = true;
// SelectListItems
public List<SelectListItem> ListTransitNumbers { get; set; } = new List<SelectListItem>()
{
new SelectListItem { Value = "All", Text = "All" },
};
public List<SelectListItem> ListSeasons { get; set; } = new List<SelectListItem>()
{
new SelectListItem { Value = "All", Text = "All" },
new SelectListItem { Value = "Q1", Text = "Spring (1-3)" },
new SelectListItem { Value = "Q2", Text = "Summer (3-6)" },
new SelectListItem { Value = "Q3", Text = "Fall (6-9)" },
new SelectListItem { Value = "Q4", Text = "Winter (9-12)" },
};
public List<SelectListItem> ListDays { get; set; } = new List<SelectListItem>()
{
new SelectListItem { Value = "All", Text = "All" },
new SelectListItem { Value = "Ordinary", Text = "Ordinary day (Tue, Wed, Thr)" },
new SelectListItem { Value = "Friday", Text = "Friday" },
new SelectListItem { Value = "Sunday", Text = "Sunday" },
new SelectListItem { Value = "Monday", Text = "Monday" },
};
public List<SelectListItem> ListTimeIntervals { get; set; } = new List<SelectListItem>()
{
new SelectListItem { Value = "60", Text = "1 hr" },
new SelectListItem { Value = "30", Text = "0.5 hr" },
new SelectListItem { Value = "15", Text = "0.25 hr" },
};
public List<SelectListItem> ListDirections { get; set; } = new List<SelectListItem>()
{
new SelectListItem { Value = "All", Text = "All" },
new SelectListItem { Value = "Plus", Text = "Plus" },
new SelectListItem { Value = "Minus", Text = "Minus" },
};
}
View
Index.cshtml
classical view, you can see that in the picture above
SelectLists, Checkboxes, Filterbutton that is doing form POST method
Controller
BordersController.cs
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Index(BordersViewModel vm)
{
if (ModelState.IsValid)
{
vm.Borders = await _db.Borders.OrderBy(x => x.Start).ToListAsync();
populateListTransitNumbers(vm);
}
// FILTER - TransitNumber (1 - 16)
//================================
if (vm.FilterTransitNumber != "All")
{
vm.Borders = vm.Borders.Where(x => x.TransitId == Convert.ToInt32(vm.FilterTransitNumber));
}
// // FILTER - Day (Monday, Friday, Sunday, Ordinary [Tuesday, Wednesday, Thursday])
//==================================================================================
if (vm.FilterDay != "All")
{
List<string> ordinaryDays = new List<string>() { "Tuesday", "Wednesday", "Thursday" };
switch (vm.FilterDay)
{
case "Monday":
case "Friday":
case "Sunday":
vm.Borders = vm.Borders.Where(x => x.Start.DayOfWeek.ToString() == vm.FilterDay);
break;
default:
vm.Borders = vm.Borders.Where(x => ordinaryDays.Contains(x.Start.DayOfWeek.ToString()));
break;
}
}
// FILTER - Season (Q1 - Q4)
//==========================
if (vm.FilterSeason != "All")
{
switch (vm.FilterSeason)
{
case "Q1":
vm.Borders = vm.Borders.Where(x => x.Start.Month >= 1 && x.Start.Month <= 3);
break;
case "Q2":
vm.Borders = vm.Borders.Where(x => x.Start.Month >= 3 && x.Start.Month <= 6);
break;
case "Q3":
vm.Borders = vm.Borders.Where(x => x.Start.Month >= 6 && x.Start.Month <= 9);
break;
case "Q4":
vm.Borders = vm.Borders.Where(x => x.Start.Month >= 9 && x.Start.Month <= 12);
break;
}
}
// FILTER - TimeInterval (60, 30, 15)
//===================================
var filteredBordersInterval = vm.Borders
.GroupBy(x =>
{
var stamp = x.Start;
stamp = stamp.AddMinutes(-(stamp.Minute % Convert.ToInt32(vm.FilterTimeInterval)));
stamp = stamp.AddMilliseconds(-stamp.Millisecond - 1000 * stamp.Second);
return stamp;
})
.Select(g => new
{
TransitId = g.First().TransitId,
Start = g.Key,
OA_Plus = g.Sum(gi => gi.OA_Plus),
NA_Plus = g.Sum(gi => gi.NA_Plus),
NS_Plus = g.Sum(gi => gi.NS_Plus),
OA_Minus = g.Sum(gi => gi.OA_Minus),
NA_Minus = g.Sum(gi => gi.NA_Minus),
NS_Minus = g.Sum(gi => gi.NS_Minus),
});
// ORDER BY TIME INTERVAL
// Ignore the whole DateTime day, just group by HH:mm and SUM column rows
//=======================
var filteredBordersGrouped = filteredBordersInterval
.GroupBy(x => x.Start.ToString("HH:mm"))
.Select(g => new
{
DayTime = g.Key,
OA_Plus = g.Sum(gi => gi.OA_Plus),
NA_Plus = g.Sum(gi => gi.NA_Plus),
NS_Plus = g.Sum(gi => gi.NS_Plus),
OA_Minus = g.Sum(gi => gi.OA_Minus),
NA_Minus = g.Sum(gi => gi.NA_Minus),
NS_Minus = g.Sum(gi => gi.NS_Minus),
});
// FINAL FILTER into X/Y values for the Chart
// Sum only those cars that are checked and both or individual directions
//===========================================
vm.ChartIntensityVM = filteredBordersGrouped
.GroupBy(x => x.DayTime)
.Select(g =>
{
int PlusSum = g.Sum(gi => vm.FilterChkboxOA == true ? gi.OA_Plus : 0)
+ g.Sum(gi => vm.FilterChkboxNA == true ? gi.NA_Plus : 0)
+ g.Sum(gi => vm.FilterChkboxNS == true ? gi.NS_Plus : 0);
int MinusSum = g.Sum(gi => vm.FilterChkboxOA == true ? gi.OA_Minus : 0)
+ g.Sum(gi => vm.FilterChkboxNA == true ? gi.NA_Minus : 0)
+ g.Sum(gi => vm.FilterChkboxNS == true ? gi.NS_Minus : 0);
int AllSum = vm.FilterDirection == "All" ? PlusSum + MinusSum : (vm.FilterDirection == "Plus" ? PlusSum : MinusSum);
return new ChartIntensityVM
{
Key = g.Key,
Value = AllSum,
};
});
return View(vm);
}