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