public void checkPotentiallyNewSystemOrStation(String newSystemName, String newStationName, Point3Dbl coordinates = null, Boolean setVisitedFlag = true)
{
String sqlString;
Int32 systemID = 0;
Int32 stationID = 0;
Boolean systemFirstTimeVisited = false;
Boolean stationFirstTimeVisited = false;
DataTable Data = new DataTable();
Boolean Visited;
PerformanceTimer pt = new PerformanceTimer();
try
{
pt.startMeasuring();
newSystemName = newSystemName.Trim();
newStationName = newStationName.Trim();
if (!String.IsNullOrEmpty(newSystemName))
{
sqlString = "select id, visited from tbSystems where Systemname = " + DBConnector.SQLAEscape(newSystemName);
if (Program.DBCon.Execute(sqlString, Data) > 0)
{
// system is existing, check or update the visited-flag
systemID = (Int32)(Data.Rows[0]["ID"]);
Visited = (Boolean)(Data.Rows[0]["visited"]);
if (!Visited && setVisitedFlag)
{
sqlString = String.Format("update tbSystems set visited = 1 where id = {0};" +
"insert ignore into tbVisitedSystems(system_id, time) values" +
" ({0},{1});",
systemID.ToString(),
DBConnector.SQLDateTime(DateTime.UtcNow));
Program.DBCon.Execute(sqlString);
// set flag in the memory table
var system = Program.Data.BaseData.tbsystems.FindByid(systemID);
if(system != null)
system.visited = setVisitedFlag;
systemFirstTimeVisited = setVisitedFlag;
}
if((coordinates != null) && (coordinates.Valid))
{
var system = Program.Data.BaseData.tbsystems.FindByid(systemID);
if(system != null)
{
if((system.x == null) || (Math.Abs(system.x - coordinates.X.Value) > 0.001) ||
(system.y == null) || (Math.Abs(system.y - coordinates.Y.Value) > 0.001) ||
(system.z == null) || (Math.Abs(system.z - coordinates.Z.Value) > 0.001))
{
sqlString = String.Format("update tbSystems set x={0}, y={1}, z={2}, updated_at = UTC_TIMESTAMP()" +
" where ((ABS(x-{0}) > 0.001) or (ABS(y-{1}) > 0.001) or (ABS(z-{2}) > 0.001)) and id = {3}",
DBConnector.SQLDecimal(coordinates.X.Value),
DBConnector.SQLDecimal(coordinates.Y.Value),
DBConnector.SQLDecimal(coordinates.Z.Value),
systemID);
if(Program.DBCon.Execute(sqlString)>0)
{
system.x = coordinates.X.Value;
system.y = coordinates.Y.Value;
system.z = coordinates.Z.Value;
}
}
}
}
}
else
{
// add a new system
Program.DBCon.TransBegin();
try
{
systemID = Program.DBCon.Execute<Int32>("select min(ID)-1 from tbsystems");
sqlString = String.Format("insert into tbSystems(id, systemname, x, y, z, updated_at, visited) values ({0},{1},{2},{3},{4},{5},{6});" +
"insert ignore into tbVisitedsystems(system_id, time) values ({0},{5});",
systemID,
DBConnector.SQLAEscape(newSystemName),
coordinates.Valid ? DBConnector.SQLDecimal(coordinates.X.Value) : "null",
coordinates.Valid ? DBConnector.SQLDecimal(coordinates.Y.Value) : "null",
coordinates.Valid ? DBConnector.SQLDecimal(coordinates.Z.Value) : "null",
DBConnector.SQLDateTime(DateTime.UtcNow),
"1");
Program.DBCon.Execute(sqlString);
Program.DBCon.TransCommit();
}
catch (Exception ex)
{
Program.DBCon.TransRollback();
throw new Exception("Error while inserting a new system");
}
systemFirstTimeVisited = setVisitedFlag;
dsEliteDB.tbsystemsRow newSystemRow = (dsEliteDB.tbsystemsRow)Program.Data.BaseData.tbsystems.NewRow();
newSystemRow.id = systemID;
newSystemRow.systemname = DBConnector.SQLAEscape(newSystemName);
if(coordinates.Valid)
{
newSystemRow.x = coordinates.X.Value;
newSystemRow.y = coordinates.X.Value;
newSystemRow.z = coordinates.X.Value;
}
newSystemRow.updated_at = DateTime.UtcNow;
newSystemRow.visited = setVisitedFlag;
Program.Data.BaseData.tbsystems.Rows.Add(newSystemRow);
}
/////////////////////////////////////////////////////////////////////////////////////////////////////
if (!String.IsNullOrEmpty(newStationName))
{
Data.Clear();
sqlString = "select St.ID, St.visited from tbSystems Sy, tbStations St" +
" where Sy.ID = St. System_ID" +
" and Sy.ID = " + systemID +
" and St.Stationname = " + DBConnector.SQLAEscape(newStationName);
if (Program.DBCon.Execute(sqlString, Data) > 0)
{
// station is existing, check or update the visited-flag
stationID = (Int32)(Data.Rows[0]["ID"]);
Visited = (Boolean)(Data.Rows[0]["visited"]);
if (!Visited && setVisitedFlag)
{
sqlString = String.Format("update tbStations set visited = 1 where id = {0};" +
"insert ignore into tbVisitedStations(station_id, time) values" +
" ({0},{1});", stationID.ToString(), DBConnector.SQLDateTime(DateTime.UtcNow));
Program.DBCon.Execute(sqlString);
// set flag in the memory table
var station = Program.Data.BaseData.tbstations.FindByid(stationID);
if(station != null)
station.visited = setVisitedFlag;
stationFirstTimeVisited = setVisitedFlag;
}
}
else
{
// add a new station
Program.DBCon.TransBegin();
try
{
stationID = Program.DBCon.Execute<Int32>("select min(ID)-1 from tbStations");
sqlString = String.Format("insert into tbStations(id, stationname, system_id, updated_at, visited) values ({0},{1},{2},{3},{4});" +
"insert ignore into tbVisitedstations(station_id, time) values ({0},{3});",
stationID,
DBConnector.SQLAEscape(newSystemName),
systemID,
DBConnector.SQLDateTime(DateTime.UtcNow),
"1");
Program.DBCon.Execute(sqlString);
Program.DBCon.TransCommit();
}
catch (Exception ex)
{
Program.DBCon.TransRollback();
throw new Exception("Error while inserting a new station");
}
stationFirstTimeVisited = setVisitedFlag;
dsEliteDB.tbstationsRow newStationRow = (dsEliteDB.tbstationsRow)Program.Data.BaseData.tbstations.NewRow();
newStationRow.id = stationID;
newStationRow.system_id = systemID;
newStationRow.stationname = DBConnector.SQLAEscape(newStationName);
newStationRow.updated_at = DateTime.UtcNow;
newStationRow.visited = setVisitedFlag;
Program.Data.BaseData.tbstations.Rows.Add(newStationRow);
}
}
if(systemFirstTimeVisited && (Program.Data.BaseData.tbvisitedsystems.Select("System_ID = " + systemID).Count() == 0))
{
dsEliteDB.tbvisitedsystemsRow newVisSystemRow = (dsEliteDB.tbvisitedsystemsRow)Program.Data.BaseData.tbvisitedsystems.NewRow();
newVisSystemRow.system_id = systemID;
newVisSystemRow.time = DateTime.UtcNow;
Program.Data.BaseData.tbvisitedsystems.Rows.Add(newVisSystemRow);
}
if(stationFirstTimeVisited && (Program.Data.BaseData.tbvisitedstations.Select("Station_ID = " + stationID).Count() == 0))
{
dsEliteDB.tbvisitedstationsRow newVisStationRow = (dsEliteDB.tbvisitedstationsRow)Program.Data.BaseData.tbvisitedstations.NewRow();
newVisStationRow.station_id = stationID;
newVisStationRow.time = DateTime.UtcNow;
Program.Data.BaseData.tbvisitedstations.Rows.Add(newVisStationRow);
}
if (((systemFirstTimeVisited) || (stationFirstTimeVisited)) &&
(stationID != 0) &&
(Program.Data.BaseData.visystemsandstations.Select("SystemID = " + systemID + " and StationID = " + stationID).Count() == 0))
{
dsEliteDB.visystemsandstationsRow newVisStationRow = (dsEliteDB.visystemsandstationsRow)Program.Data.BaseData.visystemsandstations.NewRow();
newVisStationRow.SystemName = newSystemName;
newVisStationRow.SystemID = systemID;
newVisStationRow.StationName = newStationName;
newVisStationRow.StationID = stationID;
Program.Data.BaseData.visystemsandstations.Rows.Add(newVisStationRow);
}
}
}
catch (Exception ex)
{
throw new Exception("Error while checking for potentially new system or station", ex);
}
}
