public Int32 ImportCommandersLog(String Filename)
{
DataSet Data;
String sqlString;
Int32 added = 0;
List<EDSystem> Systems = new List<EDSystem>();
List<EDStation> Stations = new List<EDStation>();
Int32 currentIndex = 0;
Int32 Counter = 0;
Dictionary<int, int> changedSystemIDs;
DBConnector lDBCon = new DBConnector(Program.DBCon.ConfigData, true);
try
{
Data = new DataSet();
Data.ReadXml(Filename);
lDBCon.Execute("set global innodb_flush_log_at_trx_commit=2");
if(Data.Tables.Contains("CommandersLogEvent"))
{
sendProgressEvent(new ProgressEventArgs() { Info="import log", CurrentValue=Counter, TotalValue=Data.Tables["CommandersLogEvent"].Rows.Count });
foreach(DataRow Event in Data.Tables["CommandersLogEvent"].AsEnumerable())
{
String System = Event["System"].ToString().Trim();
Systems.Add(new EDSystem{Name = System});
}
changedSystemIDs = ImportSystems_Own(ref Systems, true);
foreach(DataRow Event in Data.Tables["CommandersLogEvent"].AsEnumerable())
{
String Station = StructureHelper.CombinedNameToStationName((String)Event["Station"]).Trim();
Stations.Add(new EDStation{Name = Station, SystemId = Systems[currentIndex].Id});
currentIndex++;
}
ImportStations_Own(Stations, changedSystemIDs, true);
foreach(DataRow Event in Data.Tables["CommandersLogEvent"].AsEnumerable())
{
DateTime EventTime = DateTime.Parse((String)Event["EventDate"], CultureInfo.CurrentUICulture , DateTimeStyles.AssumeUniversal);
String System = Event["System"].ToString().Trim();
String Station = StructureHelper.CombinedNameToStationName((String)Event["Station"]).Trim();
String EventType = Event["EventType"].ToString().Trim().Length == 0 ? "Other" : Event["EventType"].ToString().Trim();
// add a new log entry
sqlString = String.Format("INSERT INTO tbLog(time, system_id, station_id, event_id, commodity_id," +
" cargoaction_id, cargovolume, credits_transaction, credits_total, notes)" +
" SELECT d.* FROM (SELECT" +
" {0} AS time," +
" (select id from tbSystems" +
" where systemname = {1}" +
" order by updated_at limit 1" +
" ) AS system_id," +
" (select id from tbStations where stationname = {2} " +
" and system_id = (select id from tbSystems" +
" where systemname = {1}" +
" order by updated_at limit 1)" +
" ) AS station_id," +
" (select id from tbEventType where eventtype = {3}) As event_id," +
" (select id from tbCommodity where commodity = {4} or loccommodity = {4} limit 1) As commodity_id," +
" (select id from tbCargoAction where cargoaction = {5}) AS cargoaction_id," +
" {6} AS cargovolume," +
" {7} AS credits_transaction," +
" {8} AS credits_total," +
" {9} AS notes) AS d" +
" WHERE 0 IN (SELECT COUNT(*)" +
" FROM tbLog" +
" WHERE time = {0})",
DBConnector.SQLDateTime(EventTime),
DBConnector.SQLAString(DBConnector.SQLEscape(Event["System"].ToString())),
DBConnector.SQLAString(DBConnector.SQLEscape(Station)),
DBConnector.SQLAString(EventType),
DBConnector.SQLAString(Event["Cargo"].ToString()),
DBConnector.SQLAString(Event["CargoAction"].ToString()),
Event["CargoVolume"],
Event["TransactionAmount"],
Event["Credits"],
Event["Notes"].ToString().Trim() == String.Empty ? "null" : String.Format("'{0}'", DBConnector.SQLEscape(Event["Notes"].ToString())));
added += lDBCon.Execute(sqlString);
if ((added > 0) && ((added % 10) == 0))
Debug.Print(added.ToString());
Counter++;
sendProgressEvent(new ProgressEventArgs() { Info="import log", CurrentValue=Counter, TotalValue=Data.Tables["CommandersLogEvent"].Rows.Count });
}
}
// reset freaky performance
lDBCon.Execute("set global innodb_flush_log_at_trx_commit=1");
lDBCon.Dispose();
return added;
}
catch (Exception ex)
{
// reset freaky performance
lDBCon.Execute("set global innodb_flush_log_at_trx_commit=1");
lDBCon.Dispose();
throw new Exception("Error when importing the Commander's Log ", ex);
}
}
