public void DeleteNoLongerExistingMarketData(int days, int? singleStationID=null)
{
String sqlString;
Int32 deletedRows = 0;
Int32 deletedRowsSum = 0;
Int32 stationsToCheck;
String sqlBaseString_Get;
String sqlBaseString_Delete;
DataTable data = new DataTable();
Int32 firstPosition = 0;
Int32 quantity = 500;
Int32 deleted = 0;
Int32 checkedStations = 0;
try
{
sqlBaseString_Get = " select * from" +
" (select * from tbStations) L1" +
" left join " +
" (select id " +
" from " +
" (select * from tbStations) L3 " +
" order by L3.id asc" +
" limit {0}) L2 " +
" on L1.id = L2.id " +
" where L2.id is null " +
" order by L1.id asc" +
" limit {1}";
sqlBaseString_Delete = " delete C2 from tbCommodityData C2 " +
" where C2.station_id = {0} " +
" and C2.timestamp < " +
" (select * from " +
" (select Date_Add(max(timestamp), INTERVAL -{1} DAY) " +
" from tbCommodityData where station_id = {0} " +
" ) D1 " +
" ) ";
if(!singleStationID.HasValue)
stationsToCheck = Program.DBCon.Execute<Int32>("select count(*) from tbstations");
else
stationsToCheck = 1;
sqlString = String.Format("select id from tbStations",days);
sendProgressEvent(new ProgressEventArgs() { Info=String.Format("delete prices of no longer existing commodities on stations...", days), CurrentValue=0, TotalValue=stationsToCheck, AddSeparator=true });
do
{
if(singleStationID.HasValue)
{
deleted += Program.DBCon.Execute(String.Format(sqlBaseString_Delete, singleStationID.Value, days));
}
else
{
Program.DBCon.Execute(String.Format(sqlBaseString_Get, firstPosition, quantity), data);
foreach (DataRow stationID in data.Rows)
{
deleted += Program.DBCon.Execute(String.Format(sqlBaseString_Delete, stationID["id"], days));
checkedStations++;
if(sendProgressEvent(new ProgressEventArgs() { Info=String.Format("delete prices of no longer existing commodities on stations - deleted prices={0}...", deleted), CurrentValue=checkedStations, TotalValue = stationsToCheck, Unit=" stations" }))
break;
}
firstPosition+=quantity;
}
} while ((data.Rows.Count > 0) && (!singleStationID.HasValue));
sendProgressEvent(new ProgressEventArgs() { Info=String.Format("delete prices of no longer existing commodities on stations - deleted prices={0}...", deleted), CurrentValue=checkedStations, TotalValue = stationsToCheck, Unit=" stations", ForceRefresh=true });
}
catch (Exception ex)
{
throw new Exception("Error while deleting no more existing commodities from stations", ex);
}
}
