public static void UpdateServiceCalculatedDuration(int calculatedDuration, int serviceId)
{
string sql = "UPDATE OR IGNORE ServiceTemplates SET CalculatedDuration=@CalculatedDuration WHERE Id=@ServiceId";
SQLiteData.SaveData <dynamic>(sql,
new { calculatedDuration, serviceId }, SQLiteData.GetConnectionString());
}
public static void DeleteTimeEvent(int timeEventId)
{
string sql = "PRAGMA foreign_keys = ON;" +
"DELETE FROM TimeEvents WHERE TimeEvents.Id=@TimeEventId;";
SQLiteData.SaveData <dynamic>(sql, new { timeEventId }, SQLiteData.GetConnectionString());
}
public static void InsertConnection(int serviceId, int timetableId)
{
string sql = @"INSERT OR IGNORE INTO ConnectTtSi (ServiceId, TimetableId)
VALUES(@ServiceId, @TimetableId);";
SQLiteData.SaveData <dynamic>(sql,
new { serviceId, timetableId }, SQLiteData.GetConnectionString());
}
public static List <ServiceClassModel> GetAllServiceClasses()
{
var sql = "SELECT Id, ServiceClassname, ServiceClassDescription, Category, Color FROM \"ServiceClasses\";";
var serviceClassList =
SQLiteData.LoadData <ServiceClassModel, dynamic>(sql, new { }, SQLiteData.GetConnectionString()).ToList();
return(serviceClassList);
}
public static void DeleteConnection(int serviceId, int timetableId)
{
string sql = "DELETE FROM ConnectTtSi WHERE ServiceId=@ServiceId AND TimetableId=@TimetableId";
SQLiteData.SaveData <dynamic>(sql,
new { serviceId, timetableId },
SQLiteData.GetConnectionString());
}
public static List <TimeEventTypeModel> GetAllTimeEventTypes()
{
var sql = "SELECT Id, EventType, EventTypeDescription FROM \"TimeEventTypes\";";
var timeEventList =
SQLiteData.LoadData <TimeEventTypeModel, dynamic>(sql, new {}, SQLiteData.GetConnectionString()).ToList();
return(timeEventList);
}
public static List <TimetableModel> GetAllTimetablesPerRoute(int routeId)
{
string sql = "SELECT * FROM Timetables WHERE RouteId=@RouteId";
var timetableList =
SQLiteData.LoadData <TimetableModel, dynamic>(sql, new { routeId }, SQLiteData.GetConnectionString()).ToList();
return(timetableList);
}
public static List <LocationModel> GetAllLocationsPerRoute(int routeId)
{
string sql = "SELECT * FROM Locations WHERE RouteId=@RouteId ORDER BY Locations.[Order] ASC";
var locationList =
SQLiteData.LoadData <LocationModel, dynamic>(sql, new { routeId }, SQLiteData.GetConnectionString()).ToList();
return(locationList);
}
public static int InsertTimeEventForServiceTemplate(TimeEventModel timeEvent)
{
string sql = @"INSERT OR IGNORE INTO TimeEvents (EventType, ArrivalTime, WaitTime, ServiceTemplateId, LocationId, [Order])
VALUES(@EventType, @ArrivalTime, @WaitTime, @ServiceTemplateId, @LocationId, @Order)";
return(SQLiteData.SaveData <dynamic>(sql, new { timeEvent.EventType, timeEvent.ArrivalTime
, timeEvent.WaitTime, timeEvent.ServiceTemplateId, timeEvent.LocationId, timeEvent.Order }
, SQLiteData.GetConnectionString()));
}
public static void UpdateService(ServiceModel service)
{
string sql = "UPDATE OR IGNORE Services SET ServiceName=@ServiceName, ServiceAbbreviation=@ServiceAbbreviation, StartTime=@StartTime, EndTime=@EndTime, ServiceTemplateId=@ServiceTemplateId WHERE Id=@Id";
SQLiteData.SaveData <dynamic>(sql,
new { service.ServiceName, service.ServiceAbbreviation,
service.StartTime, service.EndTime, service.ServiceTemplateId, service.Id },
SQLiteData.GetConnectionString());
}
public static List <RouteModel> GetAllRoutes()
{
string sql = "SELECT * FROM Routes";
var routesList =
SQLiteData.LoadData <RouteModel, dynamic>(sql, new { }, SQLiteData.GetConnectionString()).ToList();
return(routesList);
}
public static RouteModel GetRouteById(int routeId)
{
string sql = "SELECT * FROM Routes WHERE Id= @RouteId";
var route =
SQLiteData.LoadData <RouteModel, dynamic>(sql, new { routeId }, SQLiteData.GetConnectionString()).FirstOrDefault();
return(route);
}
public static List <string> GetAllTimeEventTypeStrings()
{
var sql = "SELECT Id, EventType, EventTypeDescription FROM \"TimeEventTypes\";";
var timeEventList =
SQLiteData.LoadData <TimeEventTypeModel, dynamic>(sql, new {}, SQLiteData.GetConnectionString()).ToList();
var output = timeEventList.Select(x => x.EventType).ToList();
return(output);
}
public static ServiceModel GetServiceById(int serviceId)
{
string sql = "SELECT * FROM Services WHERE Id= @serviceId";
var timeTable =
SQLiteData.LoadData <ServiceModel, dynamic>(sql, new { serviceId }, SQLiteData.GetConnectionString()).FirstOrDefault();
return(timeTable);
}
public static List <TimeEventModel> GetAllTimeEventsPerRoute(int routeId)
{
string sql = "SELECT * FROM TimeEvents, ServiceTemplates " +
"WHERE TimeEvents.ServiceTemplateId=ServiceTemplates.Id AND ServiceTemplates.RouteId=@RouteId";
var timeEventList = SQLiteData.LoadData <TimeEventModel, dynamic>(sql, new { routeId }
, SQLiteData.GetConnectionString()).ToList();
return(timeEventList);
}
public static int InsertService(ServiceModel service)
{
string sql = @"INSERT OR IGNORE INTO Services (ServiceName, ServiceAbbreviation, StartTime, EndTime, ServiceTemplateId)
VALUES(@ServiceName, @ServiceAbbreviation, @StartTime, @EndTime, @ServiceTemplateId);SELECT last_insert_rowid();";
return(SQLiteData.SaveData <dynamic>(sql,
new { service.ServiceName, service.ServiceAbbreviation,
service.StartTime, service.EndTime, service.ServiceTemplateId },
SQLiteData.GetConnectionString()));
}
public static List <TimetableRouteModel> GetAllTimetables()
{
string sql = "SELECT Timetables.Id AS TimetableId, Routes.Id AS RouteId, Timetables.TimetableAbbreviation, Timetables.TimetableName, " +
"Timetables.TimetableDescription, Timetables.IsMultiDirection, Timetables.ServiceDirectionId, " +
"Routes.RouteName, Routes.RouteAbbreviation " +
"FROM Timetables, Routes WHERE Timetables.RouteId=Routes.Id ORDER BY Routes.RouteAbbreviation";
var timetableList =
SQLiteData.LoadData <TimetableRouteModel, dynamic>(sql, new {}, SQLiteData.GetConnectionString()).ToList();
return(timetableList);
}
public static int InsertTimetableForRoute(TimetableModel timetable)
{
string sql = @"INSERT OR IGNORE INTO Timetables
(TimetableName, TimetableAbbreviation, TimetableDescription,
ServiceDirectionId, IsMultiDirection, RouteId)
VALUES(@TimetableName, @TimetableAbbreviation, @TimetableDescription,
@ServiceDirectionId, @IsMultiDirection, @RouteId);SELECT last_insert_rowid();";
return(SQLiteData.SaveData <dynamic>(sql, new { timetable.TimetableName, timetable.TimetableAbbreviation,
timetable.TimetableDescription, timetable.ServiceDirectionId,
timetable.IsMultiDirection, timetable.RouteId },
SQLiteData.GetConnectionString()));
}
public static int InsertServiceTemplate(ServiceTemplateModel service)
{
string sql = @"INSERT OR IGNORE INTO ServiceTemplates
(ServiceTemplateName, ServiceTemplateAbbreviation, ServiceTemplateDescription,
ServiceType, ServiceDirectionId, CalculatedDuration, RouteId)
VALUES(@ServiceTemplateName, @ServiceTemplateAbbreviation, @ServiceTemplateDescription,
@ServiceType, @ServiceDirectionId, @CalculatedDuration, @RouteId);SELECT last_insert_rowid();";
return(SQLiteData.SaveData <dynamic>(sql,
new { service.ServiceTemplateName, service.ServiceTemplateAbbreviation, service.ServiceTemplateDescription,
service.ServiceType, service.ServiceDirectionId, service.CalculatedDuration, service.RouteId },
SQLiteData.GetConnectionString()));
}
public static List <ServiceModel> GetServicesPerRoute(int routeId)
{
string sql = @"SELECT
Services.Id AS Id,
Services.ServiceName,
Services.ServiceAbbreviation,
Services.StartTime,
Services.EndTime,
Services.ServiceTemplateId
FROM Services, ServiceTemplates WHERE @RouteId=ServiceTemplates.RouteId AND Services.ServiceTemplateId== ServiceTemplates.Id";
var serviceList =
SQLiteData.LoadData <ServiceModel, dynamic>(sql, new { routeId }, SQLiteData.GetConnectionString()).ToList();
return(serviceList);
}
public static void UpdateTimetable(TimetableModel timetable)
{
string sql = @"UPDATE OR IGNORE Timetables
SET
TimetableName=@TimetableName,
TimetableAbbreviation=@TimetableAbbreviation,
TimetableDescription=@TimetableDescription,
ServiceDirectionId=@ServiceDirectionId,
IsMultiDirection=@IsMultiDirection,
RouteId=@RouteId WHERE Id=@Id";
SQLiteData.SaveData <dynamic>(sql, new { timetable.TimetableName, timetable.TimetableAbbreviation,
timetable.TimetableDescription, timetable.ServiceDirectionId,
timetable.IsMultiDirection, timetable.RouteId, timetable.Id },
SQLiteData.GetConnectionString());
}
public static void ServiceTemplateMigration()
{
// Make sure to never do this twice!
var sql = "SELECT 1 FROM sqlite_master WHERE type='table' AND name='ServiceTemplates'";
int tableExists = SQLiteData.LoadData <int, dynamic>(sql, new { }, SQLiteData.GetConnectionString()).FirstOrDefault();
if (tableExists == 0)
{
sql = File.ReadAllText("SQL\\ServiceTemplatesChange.sql");
SQLiteData.SaveData <dynamic>(sql, new { }, SQLiteData.GetConnectionString());
if (Settings.DatabaseVersion == 2)
{
VersionDataAccess.UpdateDatabaseVersion(3);
}
}
}
public static void UpdateServiceTemplate(ServiceTemplateModel service)
{
string sql = @"UPDATE OR IGNORE ServiceTemplates
SET
ServiceTemplateName=@ServiceTemplateName,
ServiceTemplateAbbreviation=@ServiceTemplateAbbreviation,
ServiceTemplateDescription=@ServiceTemplateDescription,
ServiceType=@ServiceType,
ServiceDirectionId=@ServiceDirectionId,
CalculatedDuration=@CalculatedDuration,
RouteId=@RouteId
WHERE Id=@Id";
SQLiteData.SaveData <dynamic>(sql,
new { service.ServiceTemplateName, service.ServiceTemplateAbbreviation, service.ServiceTemplateDescription,
service.ServiceType, service.ServiceDirectionId, service.CalculatedDuration, service.RouteId, service.Id },
SQLiteData.GetConnectionString());
}
public static int GetCurrentDatabaseVersion()
{
string sql1 = "SELECT 1 FROM sqlite_master WHERE type='table' AND name='Version'";
string sql2 = "SELECT VersionNr FROM Version";
try
{
int tableExists = SQLiteData.LoadData <int, dynamic>(sql1, new {}, SQLiteData.GetConnectionString()).FirstOrDefault();
if (tableExists == 1)
{
return(SQLiteData.LoadData <int, dynamic>(sql2, new { }, SQLiteData.GetConnectionString())
.FirstOrDefault());
}
return(0);
}
catch (SQLiteException e)
{
return(0);
}
}
public static void DeleteRoute(int routeId)
{
string sql = "PRAGMA foreign_keys = ON;DELETE FROM Routes WHERE Routes.Id=@RouteId;";
SQLiteData.SaveData <dynamic>(sql, new { routeId }, SQLiteData.GetConnectionString());
}
public static void UpdateRoute(RouteModel route)
{
string sql = "UPDATE OR IGNORE Routes SET RouteName=@RouteName, RouteAbbreviation=@RouteAbbreviation, RouteDescription=@RouteDescription WHERE Id= @Id";
SQLiteData.SaveData <dynamic>(sql, new { route.RouteName, route.RouteAbbreviation, route.RouteDescription, route.Id }, SQLiteData.GetConnectionString());
}
public static int InsertRoute(RouteModel route)
{
string sql = "INSERT OR IGNORE INTO Routes (RouteName, RouteAbbreviation, RouteDescription) VALUES(@RouteName, @RouteAbbreviation, @RouteDescription);SELECT last_insert_rowid();";
return(SQLiteData.SaveData <dynamic>(sql, new { route.RouteName, route.RouteAbbreviation, route.RouteDescription }, SQLiteData.GetConnectionString()));
}
public static List <ServiceTemplateModel> GetServiceTemplatesPerRoute(int routeId)
{
string sql = "SELECT * FROM ServiceTemplates WHERE RouteId=@RouteId";
var serviceList =
SQLiteData.LoadData <ServiceTemplateModel, dynamic>(sql, new { routeId }, SQLiteData.GetConnectionString()).ToList();
return(serviceList);
}
public static void DeleteServiceTemplate(int serviceTemplateId)
{
string sql = "PRAGMA foreign_keys = ON;DELETE FROM ServiceTemplates WHERE ServiceTemplates.Id=@ServiceTemplateId;";
SQLiteData.SaveData <dynamic>(sql, new { serviceTemplateId }, SQLiteData.GetConnectionString());
}
public static List <ConnectTtSiModel> GetAllConnectTtSiPerRoute(int routeId)
{
string sql = "SELECT ConnectTtSi.Id, ConnectTtSi.TimetableId, ConnectTtSi.ServiceId FROM ConnectTtSi, Timetables WHERE ConnectTtSi.TimetableId= Timetables.Id AND Timetables.RouteId=@RouteId";
var connectList =
SQLiteData.LoadData <ConnectTtSiModel, dynamic>(sql, new { routeId }, SQLiteData.GetConnectionString()).ToList();
return(connectList);
}