/// <summary>
/// Imports the prices from a list of csv-strings in EDDB format
/// </summary>
/// <param name="CSV_Strings">data to import</param>
/// <param name="importBehaviour">filter, which prices to import</param>
/// <param name="dataSource">if data has no information about the datasource, this setting will count</param>
public void ImportPricesFromEDDBStrings(String[] CSV_Strings, enImportBehaviour importBehaviour, enDataSource dataSource, PriceImportParameters importParams)
{
List<EDStation> StationData;
Boolean updateTables = false;
ProgressEventArgs eva=new ProgressEventArgs();
Int32 initialSize=0;
try
{
StationData = fromCSV_EDDB(CSV_Strings);
if(importParams != null)
{
DataTable data = Program.Data.GetNeighbourSystems(importParams.SystemID, importParams.Radius);
String info = "filter data to the bubble (radius " + importParams.Radius+ " ly) : " + data.Rows.Count +" systems...";
eva = new ProgressEventArgs() { Info=info, NewLine=true};
if(!sendProgressEvent(eva))
{
if(data.Rows.Count > 0)
{
updateTables = true;
initialSize = StationData.Count();
for (int i = StationData.Count()-1 ; i >= 0 ; i--)
{
if(data.Rows.Find(StationData[i].SystemId) == null)
{
// system is not in the bubble
StationData.Remove(StationData[i]);
}
else
{
// system is in the bubble - set as visited
Program.Data.checkPotentiallyNewSystemOrStation(StationData[i].SystemName, StationData[i].Name, null, true);
}
eva = new ProgressEventArgs() { Info=info, CurrentValue=initialSize-i, TotalValue=initialSize };
sendProgressEvent(eva);
if(eva.Cancelled)
break;
}
}
else
StationData.Clear();
}
eva = new ProgressEventArgs() { Info=info, CurrentValue=initialSize, TotalValue=initialSize, ForceRefresh=true };
sendProgressEvent(eva);
}
if((!eva.Cancelled) && (updateTables))
{
eva = new ProgressEventArgs() { Info = "refreshing basetables in memory...", NewLine=true };
sendProgressEvent(eva);
if(!eva.Cancelled)
{
Program.Data.updateVisitedFlagsFromBase();
eva = new ProgressEventArgs() { Info = "refreshing basetables in memory...", CurrentValue=25, TotalValue=100, ForceRefresh=true };
sendProgressEvent(eva);
}
if(!eva.Cancelled)
{
Program.Data.PrepareBaseTables(Program.Data.BaseData.tbsystems.TableName);
eva = new ProgressEventArgs() { Info = "refreshing basetables in memory...", CurrentValue=50, TotalValue=100, ForceRefresh=true };
sendProgressEvent(eva);
}
if(!eva.Cancelled)
{
Program.Data.PrepareBaseTables(Program.Data.BaseData.tbstations.TableName);
eva = new ProgressEventArgs() { Info = "refreshing basetables in memory...", CurrentValue=75, TotalValue=100, ForceRefresh=true };
sendProgressEvent(eva);
}
if(!eva.Cancelled)
{
Program.Data.PrepareBaseTables(Program.Data.BaseData.visystemsandstations.TableName);
eva = new ProgressEventArgs() { Info = "refreshing basetables in memory...", CurrentValue=100, TotalValue=100, ForceRefresh=true };
sendProgressEvent(eva);
}
}
// now import the prices
if(!eva.Cancelled)
{
ImportPrices(StationData, importBehaviour, dataSource);
}
}
catch (Exception ex)
{
throw new Exception("Error while importing self collected price data", ex);
}
}
/// <summary>
/// Imports the prices from a file with csv-strings (e.g. the old autosave-file)
/// </summary>
/// <param name="filename"></param>
/// <param name="importBehaviour"></param>
/// <param name="dataSource"></param>
/// <param name="importParams">only for control import behaviour from EDDB files, can be null</param>
/// <returns></returns>
public Int32 ImportPricesFromCSVFile(String filename, enImportBehaviour importBehaviour, enDataSource dataSource, PriceImportParameters importParams = null)
{
try
{
List<String> CSV_Strings = new List<string>();
ProgressEventArgs eva;
var reader = new StreamReader(File.OpenRead(filename));
Int32 counter = 0;
string header = reader.ReadLine();
sendProgressEvent(new ProgressEventArgs() { Info="reading data from file ...", AddSeparator=true });
if(header.StartsWith("System;Station;Commodity;Sell;Buy;Demand;;Supply"))
{
// old RN format
do
{
CSV_Strings.Add(reader.ReadLine());
counter ++;
if(sendProgressEvent(new ProgressEventArgs() { Info="reading data from file ...", CurrentValue=counter}))
break;
} while (!reader.EndOfStream);
reader.Close();
if(!sendProgressEvent(new ProgressEventArgs() { Info="reading data from file ...", CurrentValue=counter, TotalValue=counter, ForceRefresh = true}))
ImportPricesFromCSVStrings(CSV_Strings.ToArray(), importBehaviour, dataSource);
}
else if(header.StartsWith("id,station_id,commodity_id,supply,buy_price,sell_price,demand"))
{
// EDDB format
do
{
CSV_Strings.Add(reader.ReadLine());
counter ++;
if(sendProgressEvent(new ProgressEventArgs() { Info="reading data from file ...", CurrentValue=counter}))
break;
} while (!reader.EndOfStream);
reader.Close();
if(!sendProgressEvent(new ProgressEventArgs() { Info="reading data from file ...", CurrentValue=counter, TotalValue=counter, ForceRefresh = true}))
ImportPricesFromEDDBStrings(CSV_Strings.ToArray(), importBehaviour, dataSource, importParams);
}
return CSV_Strings.Count();
}
catch (Exception ex)
{
throw new Exception("Error while importing self collected price data", ex);
}
}
/// <summary>
/// Imports the prices from a list of csv-strings
/// </summary>
/// <param name="CSV_Strings">data to import</param>
/// <param name="importBehaviour">filter, which prices to import</param>
/// <param name="dataSource">if data has no information about the datasource, this setting will count</param>
/// <returns>a list of converted station data (including correct station ids) </returns>
public List<EDStation> ImportPricesFromCSVStrings(String[] CSV_Strings, enImportBehaviour importBehaviour, enDataSource dataSource)
{
Boolean MissingSystem = false;
Boolean MissingStation = false;
String currentLanguage;
DataTable newData;
List<EDStation> StationData;
List<EDSystem> SystemData = null;
List<CsvRow> csvRowList = new List<CsvRow>();
ProgressEventArgs eva;
Int32 counter = 0;
Dictionary<String, String> foundNames = new Dictionary<string,string>(); // quick cache for finding commodity names
try
{
// *****************************************************************
// START :section for automatically add unknown commodities
currentLanguage = Program.DBCon.getIniValue(IBESettingsView.DB_GROUPNAME, "Language", Program.BASE_LANGUAGE, false);
newData = new DataTable();
newData.TableName = "Names";
newData.Columns.Add(Program.BASE_LANGUAGE, typeof(String));
if(currentLanguage != Program.BASE_LANGUAGE)
newData.Columns.Add(currentLanguage, typeof(String));
eva = new ProgressEventArgs() { Info="analysing data...", AddSeparator = true};
sendProgressEvent(eva);
for (int i = 0; i < CSV_Strings.Length; i++)
{
String currentName;
List<dsEliteDB.tbcommoditylocalizationRow> currentCommodity;
if (CSV_Strings[i].Trim().Length > 0)
{
currentName = new CsvRow(CSV_Strings[i]).CommodityName;
if (!String.IsNullOrEmpty(currentName))
{
// check if we need to remap this name
Datasets.dsEliteDB.tbdnmap_commodityRow mappedName = (Datasets.dsEliteDB.tbdnmap_commodityRow)BaseData.tbdnmap_commodity.Rows.Find(new object[] {currentName, ""});
if (mappedName != null)
{
CSV_Strings[i] = CSV_Strings[i].Replace(mappedName.CompanionName, mappedName.GameName);
currentName = mappedName.GameName;
}
if (!foundNames.ContainsKey(currentName))
{
currentCommodity = Program.Data.BaseData.tbcommoditylocalization.Where(x => x.locname.Equals(currentName, StringComparison.InvariantCultureIgnoreCase)).ToList();
if (currentCommodity.Count == 0)
{
if (currentLanguage == Program.BASE_LANGUAGE)
newData.Rows.Add(currentName);
else
newData.Rows.Add(currentName, currentName);
}
foundNames.Add(currentName, "");
}
}
}
counter++;
eva = new ProgressEventArgs() { Info="analysing data...", CurrentValue=counter, TotalValue=CSV_Strings.GetUpperBound(0) + 1 };
sendProgressEvent(eva);
if(eva.Cancelled)
break;
}
eva = new ProgressEventArgs() { Info="analysing data...", CurrentValue=counter, TotalValue=counter, ForceRefresh=true };
sendProgressEvent(eva);
if (!eva.Cancelled)
if(newData.Rows.Count > 0)
{
// add found unknown commodities
var ds = new DataSet();
ds.Tables.Add(newData);
ImportCommodityLocalizations(ds);
// refresh translation columns
Program.Data.updateTranslation();
// refresh working tables
Program.Data.PrepareBaseTables(Program.Data.BaseData.tbcommoditylocalization.TableName);
Program.Data.PrepareBaseTables(Program.Data.BaseData.tbcommodity.TableName);
}
// END : section for automatically add unknown commodities
// *****************************************************************
// convert csv-strings to EDStation-objects
StationData = fromCSV(CSV_Strings, ref SystemData, ref csvRowList);
// check if we've unknown systems or stations
if(!eva.Cancelled)
foreach (EDStation Station in StationData)
{
if (Station.SystemId == 0)
MissingSystem = true;
else if(Station.Id == 0)
MissingStation = true;
}
if ((!eva.Cancelled) && MissingSystem)
{
// add unknown systems
ImportSystems_Own(ref SystemData, true);
}
if (!eva.Cancelled && (MissingSystem || MissingStation))
{
// add unknown stations
foreach (EDStation Station in StationData)
{
// first get all missing system ids
if (Station.SystemId == 0)
{
EDSystem thisSystem = SystemData.FirstOrDefault(x => x.Name == Station.SystemName);
if(thisSystem != null)
{
// got it - set the id
Station.SystemId = thisSystem.Id;
}
}
}
ImportStations_Own(StationData, new Dictionary<Int32, Int32>(), true);
}
// now import the prices
ImportPrices(StationData, importBehaviour, dataSource);
if (MissingSystem)
{
// reloading of base tables
Program.Data.PrepareBaseTables(Program.Data.BaseData.tbsystems.TableName);
}
if (MissingSystem || MissingStation)
{
// reloading of base tables
Program.Data.PrepareBaseTables(Program.Data.BaseData.tbstations.TableName);
Program.Data.PrepareBaseTables(Program.Data.BaseData.visystemsandstations.TableName);
}
return StationData;
}
catch (Exception ex)
{
throw new Exception("Error while importing self collected price data", ex);
}
}
/// <summary>
/// Imports the prices from the list of stations. It's clever
/// firstly to import the stations from the same file.
/// </summary>
/// <param name="Stations"></param>
private void ImportPrices(List<EDStation> Stations, enImportBehaviour importBehaviour, enDataSource dataSource)
{
DBConnector lDBCon = new DBConnector(Program.DBCon.ConfigData, true);
ProgressEventArgs eva;
try
{
StringBuilder sqlStringB = new StringBuilder();
String timeFilter = "";
Int32 Counter;
Dictionary<Int32, Int32> commodityIDs = new Dictionary<Int32, Int32>();
List<Listing> missedListings = new List<Listing>();
Listing[] currentListing;
Boolean currentListingDone;
Int32 priceCountTotal = 0;
Int32 priceCount = 0;
Int32 SourceID;
enDataSource initialDataSource = dataSource;
if ((dataSource == enDataSource.fromRN) || (dataSource == enDataSource.fromIBE_OCR))
dataSource = enDataSource.fromIBE;
if(dataSource == enDataSource.fromEDDN_T)
dataSource = enDataSource.fromEDDN;
// for the prices is no transaction necessary, because we're changing
// only a single table
// count the prices for messages
foreach (EDStation Station in Stations)
priceCountTotal += Station.Listings.Count();
Counter = 0;
sendProgressEvent(new ProgressEventArgs() { Info="updating prices...", AddSeparator=true });
Boolean AddComma = false;
int? DemandLevel = null;
int? SupplyLevel = null;
Dictionary<String, int?> Levels = new Dictionary<String, int?>();
// gettin' some freaky performance
lDBCon.Execute("set global innodb_flush_log_at_trx_commit=2");
// now add the commodities and prices
foreach (EDStation Station in Stations)
{
currentListingDone = false;
missedListings.Clear();
currentListing = Station.Listings;
do
{
commodityIDs.Clear();
currentListingDone = true;
if ((Station.Id != 0) && (currentListing.Count() > 0))
{
sqlStringB.Clear();
sqlStringB.Append("insert into tbCommodityData(id, station_id, commodity_id, Sell, Buy," +
"Demand, DemandLevel, Supply, SupplyLevel, Sources_id, timestamp) ");
foreach (Listing StationListing in currentListing)
{
// is this commodity already added in this round ? ....
if (!commodityIDs.ContainsKey(StationListing.CommodityId))
{
// ... no
if (!String.IsNullOrEmpty(StationListing.DataSource))
SourceID = (Int32)BaseTableNameToID("source", StationListing.DataSource);
else
SourceID = (Int32)dataSource;
if (dataSource <= 0)
throw new Exception("Illegal SourceID for import : " + SourceID);
if (AddComma)
sqlStringB.Append(" union all ");
// cache level-ids
getLevels(ref DemandLevel, ref SupplyLevel, Levels, StationListing);
switch (importBehaviour)
{
case enImportBehaviour.OnlyNewer:
timeFilter = String.Format("SC1.timestamp < {0}) or (SC1.timestamp is null)", DBConnector.SQLDateTime(DateTimeOffset.FromUnixTimeSeconds(StationListing.CollectedAt).DateTime));
break;
case enImportBehaviour.NewerOrEqual:
timeFilter = String.Format("SC1.timestamp <= {0}) or (SC1.timestamp is null)", DBConnector.SQLDateTime(DateTimeOffset.FromUnixTimeSeconds(StationListing.CollectedAt).DateTime));
break;
case enImportBehaviour.All:
timeFilter = String.Format("SC1.timestamp = SC1.timestamp", DBConnector.SQLDateTime(DateTimeOffset.FromUnixTimeSeconds(StationListing.CollectedAt).DateTime));
break;
}
sqlStringB.Append(String.Format("(select if(SC1.cnt = 0, 0, SC1.id),{0},{1},{2},{3},{4},{5},{6},{7},{8},{9}" +
" from (select ID, station_id, commodity_id, Count(*) As cnt, timestamp from tbCommodityData" +
" where station_id = {0}" +
" and commodity_id = {1}) SC1" +
" where ({10})",
Station.Id,
StationListing.CommodityId,
StationListing.SellPrice,
StationListing.BuyPrice,
StationListing.Demand,
DemandLevel.ToNString("null"),
StationListing.Supply,
SupplyLevel.ToNString("null"),
SourceID,
DBConnector.SQLDateTime(DateTimeOffset.FromUnixTimeSeconds(StationListing.CollectedAt).DateTime),
timeFilter));
AddComma = true;
commodityIDs.Add(StationListing.CommodityId, 0);
}
else
{
// If we add the same commodity multiple times in one command the database will not recognize
// the doubled price and add both. So we add multiple prices step by step- only the newest
// price will remain by this way.
currentListingDone = false;
missedListings.Add(StationListing);
}
priceCount++;
}
sqlStringB.Append(" on duplicate key update " +
" Sell = Values(Sell)" +
", Buy = Values(Buy)" +
", Demand = Values(Demand)" +
", DemandLevel = Values(DemandLevel)" +
", Supply = Values(Supply)" +
", SupplyLevel = Values(SupplyLevel)" +
", Sources_id = Values(Sources_id)" +
", timestamp = Values(timestamp)");
lDBCon.Execute(sqlStringB.ToString());
}
AddComma = false;
Counter++;
eva = new ProgressEventArgs() { Info="updating prices...", CurrentValue=priceCount, TotalValue=priceCountTotal };
if(sendProgressEvent(eva))
break;
currentListing = missedListings.ToArray();
} while (!currentListingDone);
if(((initialDataSource == enDataSource.fromIBE) || (initialDataSource == enDataSource.fromEDDN_T)) &&
Program.DBCon.getIniValue<Boolean>(frmDataIO.DB_GROUPNAME, "AutoPurgeNotMoreExistingDataDays", true.ToString(), false))
{
// remove old prices if we got the data from ourself or from trusted eddn senders
Program.Data.DeleteNoLongerExistingMarketData(Program.DBCon.getIniValue<Int32>(frmDataIO.DB_GROUPNAME, "PurgeNotMoreExistingDataDays", "30", false), Station.Id);
}
if(eva.Cancelled)
break;
}
eva = new ProgressEventArgs() { Info="updating prices...", CurrentValue=priceCount, TotalValue=priceCountTotal, ForceRefresh=true };
// gettin' some freaky performance
lDBCon.Execute("set global innodb_flush_log_at_trx_commit=1");
lDBCon.Dispose();
}
catch (Exception ex)
{
try{
// gettin' some freaky performance
lDBCon.Execute("set global innodb_flush_log_at_trx_commit=1");
lDBCon.Dispose();
}catch (Exception) { }
throw new Exception("Error while importing prices", ex);
}
}