/// <summary>
/// imports the data from the file into the database
/// (only newer data will be imported)
/// </summary>
/// <param name="fileName"></param>
public void ImportSystems(String Filename)
{
DBConnector lDBCon = null;
String sqlString;
List<EDSystem> Systems;
EDSystem importSystem;
dsEliteDB.tbsystemsRow[] FoundRows;
dsEliteDB.tbsystems_orgRow[] FoundRows_org;
DateTime Timestamp_new, Timestamp_old;
Int32 ImportCounter = 0;
Dictionary<Int32, Int32> changedSystemIDs = new Dictionary<Int32, Int32>();
dsEliteDB localDataSet;
Int32 counter = 0;
Boolean dataChanged;
localDataSet = new dsEliteDB();
Int32 updated = 0;
Int32 added = 0;
MySql.Data.MySqlClient.MySqlDataAdapter dataAdapter_sys = null;
MySql.Data.MySqlClient.MySqlDataAdapter dataAdapter_sysorg = null;
Int32 systemsTotal=0;
try
{
lDBCon = new DBConnector(Program.DBCon.ConfigData, true);
// gettin' some freaky performance
lDBCon.Execute("set global innodb_flush_log_at_trx_commit=2");
StreamReader rawDataStream;
JsonTextReader jsonReader;
JsonSerializer serializer = new JsonSerializer();
rawDataStream = new StreamReader(Filename);
jsonReader = new JsonTextReader(rawDataStream);
sendProgressEvent(new ProgressEventArgs() { Info="import systems...", NewLine = true } );
while (jsonReader.Read())
if((jsonReader.TokenType == JsonToken.StartObject) && (jsonReader.Depth == 1))
systemsTotal++;
jsonReader.Close();
rawDataStream.Close();
rawDataStream.Dispose();
rawDataStream = new StreamReader(Filename);
jsonReader = new JsonTextReader(rawDataStream);
while(jsonReader.Read())
{
if((jsonReader.TokenType == JsonToken.StartObject) && (jsonReader.Depth == 1))
{
dataChanged = false;
importSystem = serializer.Deserialize<EDSystem>(jsonReader);
localDataSet.Clear();
if(dataAdapter_sys != null)
{
dataAdapter_sys.Dispose();
dataAdapter_sys = null;
}
if(dataAdapter_sysorg != null)
{
dataAdapter_sysorg.Dispose();
dataAdapter_sysorg = null;
}
lDBCon.TableRead(String.Format("select * from tbSystems where id = {0} lock in share mode;", importSystem.Id), localDataSet.tbsystems, ref dataAdapter_sys);
//sqlString = "select * from tbSystems_org lock in share mode";
//lDBCon.TableRead(sqlString, Data.tbsystems_org);
if (localDataSet.tbsystems.Rows.Count > 0)
{
// system is existing
if ((bool)(localDataSet.tbsystems.Rows[0]["is_changed"]))
{
// data is changed by user - hold it ...
// ...and check table "tbSystems_org" for the original data
lDBCon.TableRead(String.Format("select * from tbSystems_org where id = {0} lock in share mode;", importSystem.Id), localDataSet.tbsystems_org, ref dataAdapter_sysorg);
if (localDataSet.tbsystems_org.Rows.Count > 0)
{
// system is in "tbSystems_org" existing - keep the newer version
Timestamp_old = (DateTime)(localDataSet.tbsystems_org.Rows[0]["updated_at"]);
Timestamp_new = DateTimeOffset.FromUnixTimeSeconds(importSystem.UpdatedAt).DateTime;
if (Timestamp_new > Timestamp_old)
{
// data from file is newer
CopyEDSystemToDataRow(importSystem, (DataRow)localDataSet.tbsystems_org.Rows[0], false, null, true);
ImportCounter += 1;
dataChanged = true;
}
}
}
else
{
// system is existing - keep the newer version
Timestamp_old = (DateTime)(localDataSet.tbsystems.Rows[0]["updated_at"]);
Timestamp_new = DateTimeOffset.FromUnixTimeSeconds(importSystem.UpdatedAt).DateTime;
if (Timestamp_new > Timestamp_old)
{
// data from file is newer
CopyEDSystemToDataRow(importSystem, localDataSet.tbsystems.Rows[0], false, null, true);
ImportCounter += 1;
dataChanged = true;
updated += 1;
}
}
}
else
{
if(dataAdapter_sys != null)
{
dataAdapter_sys.Dispose();
dataAdapter_sys = null;
}
// check if there's a user generated system
// self-created systems don't have the correct id so it must be identified by name
lDBCon.TableRead(String.Format("select * from tbSystems where systemname = {0} and id < 0 lock in share mode;", DBConnector.SQLAEscape(importSystem.Name.ToString()) ), localDataSet.tbsystems, ref dataAdapter_sys);
if (localDataSet.tbsystems.Rows.Count > 0)
{
// self created systems is existing -> correct id and get new data from EDDB
// (changed system_id in tbStations are automatically internal updated by the database itself)
CopyEDSystemToDataRow(importSystem, (DataRow)localDataSet.tbsystems.Rows[0], false, null, true);
dataChanged = true;
}
else
{
// add a new system
dsEliteDB.tbsystemsRow newRow = (dsEliteDB.tbsystemsRow)localDataSet.tbsystems.NewRow();
CopyEDSystemToDataRow(importSystem, (DataRow)newRow, false, null, true);
localDataSet.tbsystems.Rows.Add(newRow);
dataChanged = true;
}
added += 1;
ImportCounter += 1;
}
if(dataChanged)
{
if(localDataSet.tbsystems.Rows.Count > 0)
lDBCon.TableUpdate(localDataSet.tbsystems, dataAdapter_sys);
if(localDataSet.tbsystems_org.Rows.Count > 0)
lDBCon.TableUpdate(localDataSet.tbsystems_org, dataAdapter_sysorg);
dataChanged = false;
}
counter++;
if(sendProgressEvent(new ProgressEventArgs() { Info = String.Format("import systems : analysed={0}, updated={1}, added={2}", counter, ImportCounter-added, added), CurrentValue=counter, TotalValue=systemsTotal}))
break;
}
}
// 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 system data", ex);
}
}
/// <summary>
/// loads all possible trading data from one to another station (one direction)
/// </summary>
/// <param name="Data"></param>
/// <param name="Station_From"></param>
/// <param name="Station_To"></param>
public void loadBestProfitStationCommodities(dsEliteDB.tmpa_s2s_stationdataDataTable Data, int? Station_From, int? Station_To, List<Int32> commodityFilter)
{
String sqlString;
String orString = "";
try
{
if ((Station_From == null) || (Station_To == null))
{
// if a id equals null it will result in a exception from mysql:
// "There is already an open DataReader associated with this Connection which must be closed first."
Data.Clear();
}
else
{
if(commodityFilter.Count > 0)
orString = " and " + DBConnector.GetString_Or<Int32>("commodity_id", commodityFilter);
sqlString = String.Format(
"select Sd1.Station_ID, Sd1.Commodity_Id, Cm.LocCommodity As Commodity, " +
" Sd1.Buy, Sd1.Supply, Sd1.SupplyLevel, Sd1.timestamp As Timestamp1, " +
" Sd2.Sell, Sd2.Demand, Sd2.DemandLevel, Sd2.timestamp As Timestamp2, " +
" (nullif(Sd2.Sell, 0) - nullif(Sd1.Buy,0)) As Profit, Sd1.Sources_ID from " +
" (select * from tbCommodityData " +
" where Station_ID = {0} " +
orString + ") Sd1 " +
" join" +
" (select * from tbCommodityData " +
" where Station_ID = {1}) Sd2" +
" on Sd1.Commodity_ID = Sd2.Commodity_ID" +
" join" +
" tbCommodity Cm" +
" on Sd1.Commodity_ID = Cm.ID" +
" having Profit is not null" +
" order by Profit Desc;", Station_From.ToNString("null"), Station_To.ToNString("null"));
m_lDBCon.Execute(sqlString, Data);
}
}
catch (Exception ex)
{
throw new Exception("Error while loading trading data", ex);
}
}