/// <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); } }