public void ImportStations(String Filename, Boolean addPrices)
{
DBConnector lDBCon = null;
String sqlString;
List<EDStation> Stations;
dsEliteDB.tbstations_orgRow[] FoundRows_org;
dsEliteDB.tbstationsRow[] FoundRows;
DateTime Timestamp_new, Timestamp_old;
Int32 ImportCounter = 0;
dsEliteDB Data;
Int32 Counter = 0;
UInt32 currentComodityClassificationID=0;
Int32 updated = 0;
Int32 added = 0;
Data = new dsEliteDB();
try
{
lDBCon = new DBConnector(Program.DBCon.ConfigData, true);
// gettin' some freaky performance
lDBCon.Execute("set global innodb_flush_log_at_trx_commit=2");
Stations = JsonConvert.DeserializeObject<List<EDStation>>(File.ReadAllText(Filename));
sendProgressEvent(new ProgressEventArgs() { Info="import systems", NewLine = true });
lDBCon.TransBegin();
sqlString = "select * from tbStations lock in share mode";
lDBCon.TableRead(sqlString, Data.tbstations);
sqlString = "select * from tbStations_org lock in share mode";
lDBCon.TableRead(sqlString, Data.tbstations_org);
sqlString = "select * from tbStationEconomy lock in share mode";
lDBCon.TableRead(sqlString, Data.tbstationeconomy);
sqlString = "select * from tbsource";
lDBCon.Execute(sqlString, Data.tbsource);
sqlString = "select * from tbCommodityClassification lock in share mode";
lDBCon.TableRead(sqlString, Data.tbcommodityclassification);
sqlString = "select * from tbcommodity_has_attribute lock in share mode";
lDBCon.TableRead(sqlString, Data.tbcommodity_has_attribute);
sqlString = "select * from tbattribute lock in share mode";
lDBCon.TableRead(sqlString, Data.tbattribute);
currentComodityClassificationID = getFreeIndex("tbCommodityClassification");
lDBCon.Execute(sqlString, Data.tbsource);
foreach (EDStation Station in Stations)
{
FoundRows = (dsEliteDB.tbstationsRow[])Data.tbstations.Select("id=" + Station.Id.ToString());
if (FoundRows.Count() > 0)
{
// Location is existing
if ((bool)(FoundRows[0]["is_changed"]))
{
// data is changed by user - hold it ...
// ...and check table "tbStations_org" for the original data
FoundRows_org = (dsEliteDB.tbstations_orgRow[])Data.tbstations_org.Select("id=" + Station.Id.ToString());
if ((FoundRows_org != null) && (FoundRows_org.Count() > 0))
{
// Location is in "tbStations_org" existing - keep the newer version
Timestamp_old = (DateTime)(FoundRows_org[0]["updated_at"]);
Timestamp_new = DateTimeOffset.FromUnixTimeSeconds(Station.UpdatedAt).DateTime;
if (Timestamp_new > Timestamp_old)
{
// data from file is newer
CopyEDStationToDataRow(Station, (DataRow)FoundRows_org[0], false, null, true);
CopyEDStationEconomiesToDataRows(Station, Data.tbstationeconomy);
CopyEDStationCommodityToDataRow(Station, Data, ref currentComodityClassificationID);
ImportCounter += 1;
}
}
}
else
{
// Location is existing - keep the newer version
Timestamp_old = (DateTime)(FoundRows[0]["updated_at"]);
Timestamp_new = DateTimeOffset.FromUnixTimeSeconds(Station.UpdatedAt).DateTime;
if (Timestamp_new > Timestamp_old)
{
// data from file is newer
CopyEDStationToDataRow(Station, (DataRow)FoundRows[0], false, null, true);
CopyEDStationEconomiesToDataRows(Station, Data.tbstationeconomy);
CopyEDStationCommodityToDataRow(Station, Data, ref currentComodityClassificationID);
ImportCounter += 1;
}
}
}
else
{
// self-created stations don't have the correct id so they must be identified by name
FoundRows = (dsEliteDB.tbstationsRow[])Data.tbstations.Select("stationname = " + DBConnector.SQLAString(DBConnector.DTEscape(Station.Name.ToString())) + " and " +
" system_id = " + Station.SystemId + " and " +
" id < 0");
if (FoundRows.Count() > 0)
{
// self created station is existing -> correct id and get new data from EDDB
CopyEDStationToDataRow(Station, (DataRow)FoundRows[0], false, null, true);
// update immediately because otherwise the references are wrong after changing a id
lDBCon.TableUpdate(Data.tbstations);
lDBCon.TableUpdate(Data.tbstations_org);
lDBCon.TableUpdate(Data.tbstationeconomy);
lDBCon.TableUpdate(Data.tbcommodityclassification);
lDBCon.TableUpdate(Data.tbcommodity_has_attribute);
lDBCon.TableUpdate(Data.tbattribute);
lDBCon.TableRefresh(Data.tbstationeconomy);
lDBCon.TableRefresh(Data.tbcommodityclassification);
lDBCon.TableRefresh(Data.tbcommodity_has_attribute);
lDBCon.TableRefresh(Data.tbattribute);
}
else
{
// add a new Location
dsEliteDB.tbstationsRow newStationRow = (dsEliteDB.tbstationsRow)Data.tbstations.NewRow();
CopyEDStationToDataRow(Station, (DataRow)newStationRow, false, null, true);
Data.tbstations.Rows.Add(newStationRow);
added++;
}
CopyEDStationEconomiesToDataRows(Station, Data.tbstationeconomy);
CopyEDStationCommodityToDataRow(Station, Data, ref currentComodityClassificationID);
ImportCounter += 1;
}
if ((ImportCounter > 0) && ((ImportCounter % 100) == 0))
{
// save changes
Debug.Print("added Stations : " + ImportCounter.ToString());
lDBCon.TableUpdate(Data.tbstations);
lDBCon.TableUpdate(Data.tbstations_org);
lDBCon.TableUpdate(Data.tbstationeconomy);
lDBCon.TableUpdate(Data.tbcommodityclassification);
lDBCon.TableUpdate(Data.tbcommodity_has_attribute);
lDBCon.TableUpdate(Data.tbattribute);
}
Counter++;
if(sendProgressEvent(new ProgressEventArgs() { Info = String.Format("import stations : analysed={0}, updated={1}, added={2}", Counter, ImportCounter-added, added), CurrentValue=Counter, TotalValue=Stations.Count}))
break;
}
// save changes
lDBCon.TableUpdate(Data.tbstations);
lDBCon.TableUpdate(Data.tbstations_org);
lDBCon.TableUpdate(Data.tbstationeconomy);
lDBCon.TableUpdate(Data.tbcommodityclassification);
lDBCon.TableUpdate(Data.tbcommodity_has_attribute);
lDBCon.TableUpdate(Data.tbattribute);
lDBCon.TransCommit();
// now add the prices if wanted
if (addPrices)
{
ImportPrices(Stations, enImportBehaviour.OnlyNewer, enDataSource.fromEDDN);
}
// reset freaky performance
lDBCon.Execute("set global innodb_flush_log_at_trx_commit=1");
lDBCon.Dispose();
}
catch (Exception ex)
{
if(lDBCon != null)
{
if (lDBCon.TransActive())
lDBCon.TransRollback();
try
{
// reset freaky performance
lDBCon.Execute("set global innodb_flush_log_at_trx_commit=1");
lDBCon.Dispose();
}
catch (Exception) { }
}
throw new Exception("Error while importing Station data", ex);
}
}
