public static void DatabaseUserInitialization()
{
// Make sure a database exists.
int databaseExists = SQLiteData.InitDatabase(Settings.ConnectionString, Settings.DatabasePath);
Settings.DatabaseVersion = VersionDataAccess.GetCurrentDatabaseVersion();
DeleteTableMigration(); // Checks if the loaded database version can support the Delete functions
if (Settings.DatabaseVersion < 3 && databaseExists == 1)
{
// Execute a migration to make sure the renaming of the tables Services to ServiceTemplates and ServiceInstances to Services is Executed.
// One this is done, the database version will be upgraded to 3.
ServiceTemplateMigration();
}
if (Settings.DatabaseVersion >= 3 && Settings.DatabaseVersion < 5)
{
// You need ServiceClasses table end the Time Eventstable pus their data initialization.
SQLiteData.CreateTable("SQL\\UpdateToVersion5.sql");
Settings.DatabaseVersion = VersionDataAccess.GetCurrentDatabaseVersion();
}
if (Settings.UseDemoData && databaseExists == 0)
{
DemoDataSetup();
}
}
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 List <ExtendedServiceModel> GetExtendedServicesPerRoute(int routeId, int serviceDirectionId)
{
List <ServiceClassModel> serviceClasses = ServiceClassDataAccess.GetAllServiceClasses();
string sql = @"SELECT
Services.Id AS Id,
Services.ServiceName,
Services.ServiceAbbreviation,
Services.StartTime,
Services.EndTime,
ServiceTemplates.ServiceType,
Services.ServiceTemplateId
FROM Services, ServiceTemplates
WHERE
@RouteId=ServiceTemplates.RouteId
AND Services.ServiceTemplateId== ServiceTemplates.Id
AND ServiceTemplates.ServiceDirectionId=@ServiceDirectionId;";
var serviceList =
SQLiteData.LoadData <ExtendedServiceModel, dynamic>(sql, new { routeId, serviceDirectionId }, SQLiteData.GetConnectionString()).ToList();
foreach (var item in serviceList)
{
var classVar =
ServiceClassDataAccess.GetServiceClassModelFromString(item.ServiceType, serviceClasses);
item.Category = classVar.Category;
}
return(serviceList);
}
public static void UpdateTimeEvent(TimeEventModel timeEvent)
{
string sql = @"UPDATE OR IGNORE TimeEvents SET EventType=@EventType, ArrivalTime=@ArrivalTime, WaitTime=@WaitTime, ServiceTemplateId=@ServiceTemplateId, LocationId=@LocationId, [Order]=@Order WHERE Id=@Id";
SQLiteData.SaveData <dynamic>(sql, new { timeEvent.EventType, timeEvent.ArrivalTime,
timeEvent.WaitTime, timeEvent.ServiceTemplateId, timeEvent.LocationId, timeEvent.Order, timeEvent.Id }, SQLiteData.GetConnectionString());
}
// Query to get all serviceDetails
public static List <ServiceTimingModel> GetServiceTiming(int serviceId, List <LocationModel> locations)
{
var sql = @"
select Locations.Id as LocationId,
TimeEvents.id as TimeEventId,
Locations.LocationName as LocationName,
Locations.LocationAbbreviation as LocationAbbrev,
Locations.[Order] AS LocationsOrder,
TimeEvents.EventType as EventType,
TimeEvents.ArrivalTime as ArrivalTime,
TimeEvents.WaitTime as WaitTime,
'--' as TimeString
from Locations, TimeEvents
where TimeEvents.LocationId=Locations.Id and
TimeEvents.ServiceTemplateId=(
select ServiceTemplates.Id
from ServiceTemplates, Services
where ServiceTemplates.Id= Services.ServiceTemplateId
and Services.id=@serviceId)
order by Locations.[order] asc
" ;
var serviceTiming =
SQLiteData.LoadData <ServiceTimingModel, dynamic>(sql, new { serviceId }, SQLiteData.GetConnectionString()).ToList();
// Now we need to complete the list, using
return(serviceTiming);
}
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 int InsertLocationForRoute(LocationModel location)
{
string sql = "INSERT OR IGNORE INTO Locations (LocationName, LocationAbbreviation, NumberOfTracks, [Order], RouteId) " +
"VALUES(@LocationName, @LocationAbbreviation, @NumberOfTracks, @Order, @RouteId);" +
"SELECT last_insert_rowid();";
return(SQLiteData.SaveData <dynamic>(sql, new { location.LocationName, location.LocationAbbreviation, location.NumberOfTracks, location.Order, location.RouteId }, SQLiteData.GetConnectionString()));
}
public void MapToRecruitInfoTest()
{
var recruitInfoExpected = MemoryData.Build(1, 1, _photoExtension);
var priz = SQLiteData.Build(1, 1, _photoExtension);
var recruitInfoActual = RecruitInfoMapper.Map(priz);
Assert.AreEqual(recruitInfoExpected, recruitInfoActual);
}
public void MapToPrizTest()
{
var prizExpected = SQLiteData.Build(1, 1, _photoExtension);
var recruitInfo = MemoryData.Build(1, 1, _photoExtension);
var prizActual = RecruitInfoMapper.Map(recruitInfo);
Assert.AreEqual(prizExpected, prizActual);
}
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 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 int InsertServiceDirection(ServiceDirectionModel serviceDirection)
{
string sql = "INSERT OR IGNORE INTO ServiceDirections (ServiceDirectionName, ServiceDirectionAbbreviation, RouteId, IsDescending) " +
"VALUES(@ServiceDirectionName, @ServiceDirectionAbbreviation, @RouteId, @IsDescending);" +
"SELECT last_insert_rowid();";
return(SQLiteData.SaveData <dynamic>(sql, new { serviceDirection.ServiceDirectionName, serviceDirection.ServiceDirectionAbbreviation, serviceDirection.RouteId, serviceDirection.IsDescending }, 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 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 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 ServiceTemplateModel GetServiceTemplateById(int serviceTemplateId)
{
string sql = "SELECT * FROM ServiceTemplates WHERE Id= @serviceTemplateId";
var timeTable =
SQLiteData.LoadData <ServiceTemplateModel, dynamic>(sql, new { serviceTemplateId }, SQLiteData.GetConnectionString()).FirstOrDefault();
return(timeTable);
}
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 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 LocationModel GetLocationById(int locationId)
{
string sql = "SELECT * FROM Locations WHERE Id= @LocationId";
var location =
SQLiteData.LoadData <LocationModel, dynamic>(sql, new { locationId }, SQLiteData.GetConnectionString()).FirstOrDefault();
return(location);
}
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 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);
}
internal static ServiceDirectionModel GetServiceDirectionByServiceTemplateId(int serviceTemplateId)
{
string sql = "SELECT ServiceDirections.Id, ServiceDirections.ServiceDirectionName, ServiceDirections.ServiceDirectionAbbreviation, ServiceDirections.RouteId, ServiceDirections.IsDescending " +
"FROM ServiceDirections, ServiceTemplates WHERE ServiceTemplates.id=@ServiceTemplateId AND ServiceDirections.Id= ServiceTemplates.ServiceDirectionId";
var serviceDirection =
SQLiteData.LoadData <ServiceDirectionModel, dynamic>(sql, new { serviceTemplateId }, SQLiteData.GetConnectionString()).FirstOrDefault();
return(serviceDirection);
}
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 <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);
}
public static TimetableModel GetTimetableById(int timeTableId)
{
string sql = "SELECT * FROM Timetables WHERE Id= @timeTableId";
var timeTable =
SQLiteData.LoadData <TimetableModel, dynamic>(sql, new { timeTableId }, SQLiteData.GetConnectionString()).FirstOrDefault();
return(timeTable);
}
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 ServiceDirectionModel GetServiceDirectionById(int serviceDirectionId)
{
string sql = "SELECT * FROM ServiceDirections WHERE Id= @ServiceDirectionId";
var serviceDirection =
SQLiteData.LoadData <ServiceDirectionModel, dynamic>(sql, new { serviceDirectionId }, SQLiteData.GetConnectionString()).FirstOrDefault();
return(serviceDirection);
}
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 TimeEventModel GetTimeEventById(int timeEventId)
{
string sql = "SELECT * FROM TimeEvents " +
"WHERE Id= @TimeEventId";
var timeEvent =
SQLiteData.LoadData <TimeEventModel, dynamic>(sql, new { timeEventId }, SQLiteData.GetConnectionString()).FirstOrDefault();
return(timeEvent);
}
public static List <TimeEventModel> GetAllTimeEventsPerServiceTemplate(int serviceTemplateId)
{
string sql = "SELECT * FROM TimeEvents " +
"WHERE ServiceTemplateId=@ServiceTemplateId";
var timeEventList =
SQLiteData.LoadData <TimeEventModel, dynamic>(sql, new { serviceTemplateId }, SQLiteData.GetConnectionString()).ToList();
return(timeEventList);
}
