private int SaveNewRouteStops(TrolleyTrackerContext db, string jsonStops)
{
JavaScriptSerializer jss = new JavaScriptSerializer();
jss.RegisterConverters(new JavaScriptConverter[] { new DynamicJsonConverter() });
dynamic stopData = jss.Deserialize(jsonStops, typeof(object)) as dynamic;
int stopCount = stopData.Length;
for (int i = 0; i < stopCount; i++)
{
var importStop = stopData[i];
var newStop = new Stop();
newStop.Lat = Convert.ToDouble(importStop.Lat);
newStop.Lon = Convert.ToDouble(importStop.Lon);
newStop.Name = importStop.Name;
newStop.Description = importStop.Name;
newStop.RouteStops = new List <RouteStop>();
db.Stops.Add(newStop);
}
db.SaveChanges();
return(stopCount);
}
// GET: api/Trolleys
public List <Trolley> GetTrolleys()
{
using (var db = new TrolleyTrackerContext())
{
return(db.Trolleys.ToList());
}
}
public ActionResult CreateAtPosition([Bind(Include = "ID,StopSequence,Name,Description,Lat,Lon")] Stop stop)
{
if (ModelState.IsValid)
{
var file = Request.Files["Picture"];
if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName))
{
string fileName = file.FileName;
string fileContentType = file.ContentType;
byte[] fileBytes = new byte[file.ContentLength];
file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
stop.Picture = fileBytes;
}
using (var db = new TrolleyTrackerContext())
{
db.Stops.Add(stop);
db.SaveChanges();
}
logger.Info($"Created stop '{stop.Name}' - '{stop.Description}' at {stop.Lat}, {stop.Lon}");
return(RedirectToAction("Index"));
}
return(View(stop));
}
public ActionResult Edit([Bind(Include = "ID,StopSequence,Name,Description,Lat,Lon")] Stop stop)
{
if (ModelState.IsValid)
{
using (var db = new TrolleyTrackerContext())
{
var newStop = new Stop();
newStop.ID = stop.ID;
db.Stops.Attach(newStop); // Attach used instead of EntityState.Modified so that only changed fields are saved
newStop.Lat = stop.Lat;
newStop.Lon = stop.Lon;
newStop.Name = stop.Name;
newStop.Description = stop.Description;
var file = Request.Files["Picture"];
if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName))
{
string fileName = file.FileName;
string fileContentType = file.ContentType;
byte[] fileBytes = new byte[file.ContentLength];
file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
newStop.Picture = fileBytes;
}
//db.Entry(stop).State = EntityState.Modified;
db.SaveChanges();
logger.Info($"Updated stop '{newStop.Name}' - '{newStop.Description}' at {newStop.Lat}, {newStop.Lon}");
}
return(RedirectToAction("Index"));
}
return(View(stop));
}
public void AddRouteDetail(TrolleyTrackerContext db, Route route)
{
var stops = (from stop in db.Stops
from routeStop in db.RouteStops
orderby routeStop.StopSequence
where (routeStop.StopID == stop.ID) && (routeStop.RouteID == route.ID)
select stop).ToList();
foreach (var stop in stops)
{
// Construct with route info so route shape segment index is included
var stopSummary = new StopSummary(stop, route);
// Use arrival times if available
var stopWithArrivalTime = StopArrivalTime.GetStopSummaryWithArrivalTimes(stop.ID);
if (stopWithArrivalTime != null)
{
stopSummary.NextTrolleyArrivalTime = stopWithArrivalTime.NextTrolleyArrivalTime;
}
this.Stops.Add(stopSummary);
}
var shapes = from shape in db.Shapes
orderby shape.Sequence
where (shape.RouteID == route.ID)
select shape;
foreach (var shape in shapes)
{
var coordinate = new Location();
coordinate.Lat = shape.Lat;
coordinate.Lon = shape.Lon;
this.RoutePath.Add(coordinate);
}
}
// GET: Logs
/// <summary>
/// Get specified page of log entries, newest first
/// </summary>
/// <param name="pageIndex">0 based page number</param>
/// <returns></returns>
public ActionResult Index(int pageIndex = 0)
{
using (var db = new TrolleyTrackerContext())
{
if (pageIndex == 0)
{
// On first page view
PurgeOldLogs(db);
}
if (pageIndex < 0)
{
pageIndex = 0;
}
int pageSize = 40;
int totalRecords = db.Logs.Count();
int totalPageCount = (totalRecords / pageSize) + ((totalRecords % pageSize) > 0 ? 1 : 0);
var query = db.Logs.OrderByDescending(l => l.Logged).Skip((pageIndex * pageSize)).Take(pageSize).ToList();
ReformatQueryItems(query);
ViewBag.dbCount = totalRecords;
ViewBag.pageSize = pageSize;
ViewBag.totalPageCount = totalPageCount;
return(View(query));
}
}
private String TrolleysJSON(TrolleyTrackerContext db)
{
var trolleysList = db.Trolleys.ToList();
string trolleysJSON = JsonConvert.SerializeObject(trolleysList);
return(trolleysJSON);
}
// **** NOT USED - This is handle by normal EF / code first
// private void CreateLoggingTableIfNotExist(TrolleyTracker.Models.TrolleyTrackerContext dbContext)
// {
// dbContext.Database.ExecuteSqlCommand(
//@"if not exists(select * from sys.tables t where t.name = 'Log')
// CREATE TABLE [dbo].[Log] (
// [Id] [int] IDENTITY(1,1) NOT NULL,
// [Logged] [datetime] NOT NULL,
// [Level] [nvarchar](50) NOT NULL,
// [Message] [nvarchar](max) NOT NULL,
// [UserName] [nvarchar](250) NULL,
// [RemoteAddress] [nvarchar](100) NULL,
// [Callsite] [nvarchar](max) NULL,
// [Exception] [nvarchar](max) NULL,
// CONSTRAINT [PK_dbo.Log] PRIMARY KEY CLUSTERED ([Id] ASC)
// WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
// ) ON [PRIMARY]
//");
// }
/// <summary>
/// Populate database with starting dataset. Note: this script must always be updated so that it can
/// be applied to the latest model.
/// </summary>
/// <param name="context"></param>
private void InitialSeedFromScript(TrolleyTrackerContext context)
{
var baseDir = AppDomain.CurrentDomain.BaseDirectory.Replace(@"\bin", string.Empty) + @"\Migrations";
var filePath = baseDir + "\\InitialTableSeedInserts.sql";
using (var scriptFile = new StreamReader(filePath))
{
var sqlCommand = new StringBuilder();
while (!scriptFile.EndOfStream)
{
// Series of commands separated by GO
var line = scriptFile.ReadLine().Trim();
if (line == "GO")
{
context.Database.ExecuteSqlCommand(sqlCommand.ToString());
sqlCommand.Clear();
}
else
{
sqlCommand.AppendLine(line);
}
}
if (sqlCommand.Length > 0)
{
context.Database.ExecuteSqlCommand(sqlCommand.ToString());
}
}
}
/// <summary>
/// Build effective schedule string for client web page, with optional single route schedule list
/// </summary>
/// <param name="db"></param>
/// <param name="runsOnSchedule">String list to hold optional single route list</param>
/// <param name="routeID">Route to create runsOnSchedule list for, -1 for don't care</param>
/// <returns></returns>
private string EffectiveScheduleJSON(TrolleyTrackerContext db, List <String> runsOnSchedule, int routeID)
{
var effectiveScheduleList = WebAPI.RouteSchedulesController.GetSchedules();
// Massage and convert for display on web page - web page uses in reverse order
// "<b><a href=\"schedule/18/\">Heart Of Main:</a></b><br>Sunday 5:30 PM - 8:00 PM",
// var runson = ["Sunday: 1:00 PM - 8:00 PM", "Saturday: 10:00 AM - 11:00 PM", "Friday: 6:00 PM - 11:00 PM", "Thursday: 6:00 PM - 11:00 PM"];
var webScheduleList = new List <String>();
foreach (var schedule in effectiveScheduleList)
{
var thisSchedule = $"<b> <span style=\"background-color: {schedule.RouteColorRGB}\"> </span> <a href=\"/ClientWeb/RouteView/{schedule.RouteID}/\">{HttpUtility.HtmlEncode(schedule.RouteLongName)}:</a></b><br>{schedule.DayOfWeek} {schedule.StartTime} - {schedule.EndTime}";
webScheduleList.Insert(0, thisSchedule); // For reverse sorting
if (schedule.RouteID == routeID)
{
runsOnSchedule.Add($"{schedule.DayOfWeek} {schedule.StartTime} - {schedule.EndTime}");
}
}
string effectiveScheduleJSON = JsonConvert.SerializeObject(webScheduleList);
return(effectiveScheduleJSON);
}
// GET: ClientWeb/RouteView/5
public ActionResult RouteView(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
using (var db = new TrolleyTrackerContext())
{
var route = db.Routes.Find(id);
if (route == null)
{
return(HttpNotFound());
}
var scheduleWebTemplate = System.IO.File.ReadAllText(Server.MapPath("/Content/ClientWeb/schedule.html"));
scheduleWebTemplate = scheduleWebTemplate.Replace("%routedata%", SingleRouteDetailJSON(route, db));
var runsOnSchedule = new List <String>();
scheduleWebTemplate = scheduleWebTemplate.Replace("%scheduledata%", EffectiveScheduleJSON(db, runsOnSchedule, route.ID));
string runsOnJSON = JsonConvert.SerializeObject(runsOnSchedule);
scheduleWebTemplate = scheduleWebTemplate.Replace("%runs_on%", runsOnJSON);
ViewBag.ClientWebPage = scheduleWebTemplate;
// Use PartialView so that page is shown without any standard layout
return(PartialView());
}
}
// GET: AppSettings
public ActionResult Index()
{
using (var db = new TrolleyTrackerContext())
{
return(View(db.AppSettings.ToList()));
}
}
private List <SelectListItem> GetRouteSelectList(int?selectedID)
{
using (var db = new TrolleyTrackerContext())
{
var routes = from r in db.Routes
orderby r.ShortName
select r;
var items = new SelectList(routes, "ID", "ShortName").ToList();
// Set selected item if specified
if (selectedID.HasValue)
{
var strSelValue = selectedID.ToString();
foreach (var item in items)
{
if (item.Value == strSelValue)
{
item.Selected = true;
}
}
}
items.Insert(0, (new SelectListItem {
Text = "-- Select Route --", Value = ""
}));
return(items);
}
}
// GET: RouteStops
public ActionResult Index()
{
using (var db = new TrolleyTrackerContext())
{
var routeStops = db.RouteStops.Include(r => r.Route).Include(r => r.Stop);
return(View(routeStops.ToList()));
}
}
// GET: RouteStops/Create
public ActionResult Create()
{
using (var db = new TrolleyTrackerContext())
{
ViewBag.RouteID = new SelectList(db.Routes, "ID", "ShortName");
ViewBag.StopID = new SelectList(db.Stops, "ID", "Name");
return(View());
}
}
private List <Trolley> GetAllTrolleys()
{
using (var db = new TrolleyTrackerContext())
{
var trolleys = from t in db.Trolleys
select t;
return(trolleys.ToList());
}
}
// GET: Trolleys
public ActionResult Index()
{
using (var db = new TrolleyTrackerContext())
{
var trolleys = from t in db.Trolleys
orderby t.Number, t.TrolleyName
select t;
return(View(trolleys.ToList()));
}
}
public ActionResult DeleteConfirmed(int id)
{
using (var db = new TrolleyTrackerContext())
{
AppSettings appSettings = db.AppSettings.Find(id);
db.AppSettings.Remove(appSettings);
db.SaveChanges();
return(RedirectToAction("Index"));
}
}
public ActionResult DeleteConfirmed(int id)
{
using (var db = new TrolleyTrackerContext())
{
RouteStop routeStop = db.RouteStops.Find(id);
db.RouteStops.Remove(routeStop);
db.SaveChanges();
return(RedirectToAction("Index"));
}
}
// GET: Routes
public ActionResult Index()
{
using (var db = new TrolleyTrackerContext())
{
var routeList = (from r in db.Routes
orderby r.ShortName
select r).ToList();
return(View(routeList));
}
}
public ActionResult DeleteConfirmed(int id)
{
using (var db = new TrolleyTrackerContext())
{
RouteScheduleOverride routeScheduleOverride = db.RouteScheduleOverrides.Find(id);
logger.Info($"Deleted special schedule type '{routeScheduleOverride.OverrideType.ToString()}' at {routeScheduleOverride.OverrideDate} '{routeScheduleOverride.StartTime.TimeOfDay} - {routeScheduleOverride.EndTime.TimeOfDay}");
db.RouteScheduleOverrides.Remove(routeScheduleOverride);
db.SaveChanges();
}
return(RedirectToAction("Index"));
}
public static void LoadAppSettings()
{
using (var db = new TrolleyTrackerContext())
{
var appSettings = (from a in db.AppSettings select a).FirstOrDefault();
if (appSettings != null)
{
UpdateSettings(appSettings);
}
}
}
// GET: Stops/List
public ActionResult List()
{
using (var db = new TrolleyTrackerContext())
{
var stops = from s in db.Stops
orderby s.Name
select s;
return(View(stops.ToList()));
}
}
public ActionResult DeleteConfirmed(int id)
{
using (var db = new TrolleyTrackerContext())
{
Route route = db.Routes.Find(id);
logger.Info($"Deleted route '{route.ShortName}' ({route.Description})");
db.Routes.Remove(route);
db.SaveChanges();
}
return(RedirectToAction("Index"));
}
private String SingleRouteDetailJSON(Route route, TrolleyTrackerContext db)
{
var routeDetailList = new List <RouteDetail>(); // Schedule web page expects array, even for single route
var routeDetail = new RouteDetail(route);
routeDetail.AddRouteDetail(db, route);
routeDetailList.Add(routeDetail);
string routeDetailJSON = JsonConvert.SerializeObject(routeDetailList);
return(routeDetailJSON);
}
private void RemovePreviousRouteStops(TrolleyTrackerContext db, int routeID)
{
var routeStops = from RouteStop rs in db.RouteStops
where rs.RouteID == routeID
select rs;
foreach (var rs in routeStops)
{
db.RouteStops.Remove(rs);
}
db.SaveChanges();
}
public ActionResult DeleteConfirmed(int id)
{
using (var db = new TrolleyTrackerContext())
{
RouteSchedule routeSchedule = db.RouteSchedules.Find(id);
routeSchedule.Route = db.Routes.Find(routeSchedule.RouteID);
logger.Info($"Deleted fixed route schedule '{routeSchedule.Route.ShortName}' - '{BuildScheduleView.daysOfWeek[routeSchedule.DayOfWeek]}' {routeSchedule.StartTime.TimeOfDay}-{routeSchedule.EndTime.TimeOfDay} ");
db.RouteSchedules.Remove(routeSchedule);
db.SaveChanges();
}
return(RedirectToAction("Index"));
}
public ActionResult UpdatePosition(int id, double Lat, double Lon)
{
using (var db = new TrolleyTrackerContext())
{
Stop stop = db.Stops.Find(id);
stop.Lat = Lat;
stop.Lon = Lon;
db.SaveChanges();
logger.Info($"Updated stop position '{stop.Name}' - '{stop.Description}' at {stop.Lat}, {stop.Lon}");
}
return(RedirectToAction("Index"));
}
public ActionResult DeleteConfirmed(int id)
{
using (var db = new TrolleyTrackerContext())
{
Stop stop = db.Stops.Find(id);
logger.Info($"Deleted stop '{stop.Name}' - '{stop.Description}'");
db.Stops.Remove(stop);
db.SaveChanges();
}
return(RedirectToAction("Index"));
}
/// <summary>
/// Create sorted route list, with first item representing null entry choice as ID=-1
/// </summary>
/// <param name="nullLabel"></param>
/// <returns></returns>
private List <Route> RouteSelectList(string nullLabel)
{
using (var db = new TrolleyTrackerContext())
{
var routeList = db.Routes.OrderBy(r => r.ShortName).ToList();
var nullRoute = new Route();
nullRoute.ID = -1;
nullRoute.ShortName = nullLabel;
routeList.Insert(0, nullRoute);
return(routeList);
}
}
public IHttpActionResult GetTrolley(int id)
{
using (var db = new TrolleyTrackerContext())
{
Trolley trolley = db.Trolleys.Find(id);
if (trolley == null)
{
return(NotFound());
}
return(Ok(trolley));
}
}