public void ExportMarketDataToCSV(string fileName, Boolean inCurrentLanguage, Boolean extendedFormat)
{
String sqlString;
DataTable data;
Int32 Counter;
StringBuilder sBuilder = new StringBuilder();
Char filterCharacter=(char)(48);
Int32 totalDataCount = 0;
ProgressEventArgs eva;
try
{
if(System.IO.File.Exists(fileName))
System.IO.File.Delete(fileName);
var writer = new StreamWriter(File.OpenWrite(fileName));
if(extendedFormat)
writer.WriteLine("System;Station;Commodity;Sell;Buy;Demand;;Supply;;Date;SourceFileName;Source");
else
writer.WriteLine("System;Station;Commodity;Sell;Buy;Demand;;Supply;;Date;");
data = new DataTable();
Counter = 0;
totalDataCount = Program.DBCon.Execute<Int32>("select count(*) from tbCommodityData;");
for (int i = 0; i < 36; i++)
{
if(i < 10)
filterCharacter = (char)(48 + i);
else
filterCharacter = (char)(65 + i - 10);
if (inCurrentLanguage)
{
// export names in user language
sqlString = String.Format("select Sy.systemname, St.stationname, C.loccommodity as commodity, D.sell, D.buy, D.demand, D.demandlevel, D.supply, D.supplylevel, D.timestamp, S.source" +
" from tbSystems Sy, tbStations St, tbCommodityData D, tbCommodity C, tbSource S" +
" where Sy.id = St.system_id" +
" and St.id = D.station_id" +
" and D.commodity_id = C.id" +
" and D.sources_id = S.id" +
" and Sy.systemname like '{0}%'" +
" order by Sy.systemname, St.stationname, C.loccommodity",
filterCharacter);
}
else
{
// export names in default language (english)
sqlString = String.Format("select Sy.systemname, St.stationname, C.commodity, D.sell, D.buy, D.demand, D.demandlevel, D.supply, D.supplylevel, D.timestamp, S.source" +
" from tbSystems Sy, tbStations St, tbCommodityData D, tbCommodity C, tbSource S" +
" where Sy.id = St.system_id" +
" and St.id = D.station_id" +
" and D.commodity_id = C.id" +
" and D.sources_id = S.id" +
" and Sy.systemname like '{0}%'" +
" order by Sy.systemname, St.stationname, C.commodity",
filterCharacter);
}
eva = new ProgressEventArgs() { Info=String.Format("collecting data '{0}'...", filterCharacter), CurrentValue=Counter, TotalValue=totalDataCount, NewLine=true};
sendProgressEvent(eva);
Program.DBCon.Execute(sqlString, data);
foreach (DataRow row in data.Rows)
{
String Demand = "";
String Supply = "";
if (inCurrentLanguage)
{
Demand = ((String)BaseTableIDToName("economylevel", SQL.DBConvert.To<int?>(row["demandlevel"]), "loclevel")).NToString("");
Supply = ((String)BaseTableIDToName("economylevel", SQL.DBConvert.To<int?>(row["supplylevel"]), "loclevel")).NToString("");
}
else
{
Demand = ((String)BaseTableIDToName("economylevel", SQL.DBConvert.To<int?>(row["demandlevel"]), "level")).NToString("");
Supply = ((String)BaseTableIDToName("economylevel", SQL.DBConvert.To<int?>(row["supplylevel"]), "level")).NToString("");
}
sBuilder.Append(row["systemname"] + ";" +
row["stationname"] + ";" +
row["commodity"] + ";" +
row["sell"] + ";" +
row["buy"] + ";" +
row["demand"] + ";" +
Demand + ";" +
row["supply"] + ";" +
Supply + ";" +
row["timestamp"]);
if(extendedFormat)
{
sBuilder.Append(";;" + //sourceFileName, no more in use but for compatibility
row["source"]); //source
}
writer.WriteLine(sBuilder.ToString());
sBuilder.Clear();
Counter++;
eva = new ProgressEventArgs() { Info=String.Format("export prices '{0}'...", filterCharacter), CurrentValue=Counter, TotalValue=totalDataCount};
sendProgressEvent(eva);
if(eva.Cancelled)
break;
}
eva = new ProgressEventArgs() { Info=String.Format("export prices '{0}'...", filterCharacter), CurrentValue=Counter, TotalValue=totalDataCount, ForceRefresh=true};
sendProgressEvent(eva);
if(eva.Cancelled)
break;
if(eva.Cancelled)
break;
}
eva = new ProgressEventArgs() { Info=String.Format("export prices '{0}'...", filterCharacter), CurrentValue=Counter, TotalValue=totalDataCount, ForceRefresh=true};
sendProgressEvent(eva);
writer.Close();
writer.Dispose();
}
catch (Exception ex)
{
throw new Exception("Error while exporting to csv file", ex);
}
}