/// <summary>
/// Write a table of data. Uses the TableName property of the specified DataTable.
/// </summary>
/// <param name="table">The data to write.</param>
public void WriteTable(DataTable table)
{
if (table == null)
{
return;
}
// NOTE: This can be called from many threads. Don't actually
// write to the database on these threads. We have a single worker
// thread to do that.
Start();
// Delete old rows in table.
if (table.Columns.Contains("SimulationName"))
{
var simulationNames = DataTableUtilities.GetColumnAsStrings(table, "SimulationName").ToList().Distinct();
DeleteOldRowsInTable(table.TableName, "Current",
simulationNamesThatMayNeedCleaning: simulationNames);
}
else
{
DeleteOldRowsInTable(table.TableName, "Current");
}
AddIndexColumns(table, "Current", null, null);
lock (lockObject)
{
commands.Add(new WriteTableCommand(Connection, table));
}
}
/// <summary>Alter an existing table ensuring all columns exist.</summary>
/// <param name="connection">The SQLite connection to write to</param>
private void AlterTable(SQLite connection)
{
DataTable columnData = connection.ExecuteQuery("pragma table_info('" + Name + "')");
List <string> existingColumns = DataTableUtilities.GetColumnAsStrings(columnData, "Name").ToList();
lock (lockObject)
{
foreach (Column col in Columns)
{
if (!existingColumns.Contains(col.Name))
{
string dataTypeString;
if (col.SQLiteDataType == null)
{
dataTypeString = "integer";
}
else
{
dataTypeString = col.SQLiteDataType;
}
string sql = "ALTER TABLE " + Name + " ADD COLUMN [" + col.Name + "] " + dataTypeString;
connection.ExecuteNonQuery(sql);
}
}
}
}
/// <summary>Refresh our tables structure and simulation Ids</summary>
private void Refresh()
{
// Get a list of table names.
DataTable tableData = connection.ExecuteQuery("SELECT * FROM sqlite_master");
foreach (string tableName in DataTableUtilities.GetColumnAsStrings(tableData, "Name"))
{
Table table = tables.Find(t => t.Name == tableName);
if (table == null)
{
table = new Table(tableName);
tables.Add(table);
}
table.SetConnection(connection);
}
// Get a list of simulation names
simulationIDs.Clear();
bool haveSimulationTable = tables.Find(table => table.Name == "_Simulations") != null;
if (haveSimulationTable)
{
DataTable simulationTable = connection.ExecuteQuery("SELECT ID, Name FROM _Simulations ORDER BY Name");
foreach (DataRow row in simulationTable.Rows)
{
string name = row["Name"].ToString();
if (!simulationIDs.ContainsKey(name))
{
simulationIDs.Add(name, Convert.ToInt32(row["ID"]));
}
}
}
}
public void Version9()
{
Directory.SetCurrentDirectory(Path.GetTempPath());
string fileName = Path.Combine(Path.GetTempPath(), "TestConverter.db");
File.Delete(fileName);
SQLite connection = new SQLite();
connection.OpenDatabase(fileName, false);
try
{
connection.ExecuteNonQuery("CREATE TABLE Simulations (ID INTEGER PRIMARY KEY ASC, Name TEXT COLLATE NOCASE)");
connection.ExecuteNonQuery("CREATE TABLE Messages (SimulationID INTEGER, ComponentName TEXT, Date TEXT, Message TEXT, MessageType INTEGER)");
connection.ExecuteNonQuery("CREATE TABLE _Units (TableName TEXT, ColumnHeading TEXT, Units TEXT)");
connection.ExecuteNonQuery("CREATE TABLE Report (Col1 TEXT, Col2 TEXT, Col3 TEXT)");
string fromXML = "<Simulation Version=\"8\"/>";
XmlDocument doc = new XmlDocument();
doc.LoadXml(fromXML);
Assert.IsTrue(APSIMFileConverter.ConvertToLatestVersion(doc.DocumentElement, fileName));
DataTable tableData = connection.ExecuteQuery("SELECT * FROM sqlite_master");
string[] tableNames = DataTableUtilities.GetColumnAsStrings(tableData, "Name");
Assert.AreEqual(tableNames, new string[] { "_Simulations", "_Messages", "_Units", "Report" });
}
finally
{
connection.CloseDatabase();
File.Delete(fileName);
}
}
/// <summary>
/// Rename the "Simulations", "Messages", "InitialConditions" .db tables to be
/// prefixed with an underscore.
/// </summary>
/// <param name="node">The node to upgrade.</param>
/// <param name="fileName">The name of the .apsimx file</param>
private static void UpgradeToVersion14(XmlNode node, string fileName)
{
string dbFileName = Path.ChangeExtension(fileName, ".db");
if (File.Exists(dbFileName))
{
SQLite connection = new SQLite();
connection.OpenDatabase(dbFileName, false);
try
{
DataTable tableData = connection.ExecuteQuery("SELECT * FROM sqlite_master");
foreach (string tableName in DataTableUtilities.GetColumnAsStrings(tableData, "Name"))
{
if (tableName == "Simulations" || tableName == "Messages" || tableName == "InitialConditions")
{
connection.ExecuteNonQuery("ALTER TABLE " + tableName + " RENAME TO " + "_" + tableName);
}
}
}
finally
{
connection.CloseDatabase();
}
}
}
/// <summary>
/// Create series definitions assuming the vary by fields are text fields in the table.
/// </summary>
/// <param name="reader">The reader to read from.</param>
/// <param name="varyByFieldNames">The vary by fields.</param>
/// <param name="whereClauseForInScopeData">An SQL WHERE clause for rows that are in scope.</param>
private List <SeriesDefinition> CreateDefinitionsFromFieldInTable(IStorageReader reader, List <string> varyByFieldNames, string whereClauseForInScopeData)
{
List <SeriesDefinition> definitions = new List <SeriesDefinition>();
var fieldsThatExist = reader.ColumnNames(TableName);
var varyByThatExistInTable = varyByFieldNames.Where(v => fieldsThatExist.Contains(v)).ToList();
var validValuesForEachVaryByField = new List <List <string> >();
foreach (var varyByFieldName in varyByThatExistInTable)
{
var data = reader.GetData(TableName,
fieldNames: new string[] { varyByFieldName },
filter: whereClauseForInScopeData,
distinct: true);
var values = DataTableUtilities.GetColumnAsStrings(data, varyByFieldName).Distinct().ToList();
validValuesForEachVaryByField.Add(values);
}
foreach (var combination in MathUtilities.AllCombinationsOf(validValuesForEachVaryByField.ToArray(), reverse:true))
{
var descriptors = new List <SimulationDescription.Descriptor>();
for (int i = 0; i < combination.Count; i++)
{
descriptors.Add(new SimulationDescription.Descriptor(varyByThatExistInTable[i],
combination[i]));
}
definitions.Add(new SeriesDefinition(this, whereClauseForInScopeData, Filter, descriptors));
}
return(definitions);
}
/// <summary>
/// Gets the data table for this series.
/// </summary>
private IEnumerable GetData(GraphValues graphValues)
{
if (graphValues != null && graphValues.TableName == null && graphValues.FieldName != null)
{
// Use reflection to access a property.
return(graphValues.GetData(this.graph));
}
else if (graphValues != null && graphValues.TableName != null && graphValues.FieldName != null)
{
// Create the data if we haven't already
if (this.data == null && this.dataStore.TableExists(graphValues.TableName))
{
this.data = this.dataStore.GetFilteredData(graphValues.TableName, filter);
}
// If the field exists in our data table then return it.
if (this.data != null && graphValues.FieldName != null && this.data.Columns[graphValues.FieldName] != null)
{
if (this.data.Columns[graphValues.FieldName].DataType == typeof(DateTime))
{
return(DataTableUtilities.GetColumnAsDates(this.data, graphValues.FieldName));
}
else if (this.data.Columns[graphValues.FieldName].DataType == typeof(string))
{
return(DataTableUtilities.GetColumnAsStrings(this.data, graphValues.FieldName));
}
else
{
return(DataTableUtilities.GetColumnAsDoubles(this.data, graphValues.FieldName));
}
}
}
return(null);
}
/// <summary>Imports factor information from a csv file.</summary>
/// <param name="sender">Sender object.</param>
/// <param name="args">Event arguments.</param>
private void OnImportCsv(object sender, EventArgs args)
{
ApsimTextFile textFile = new ApsimTextFile();
try
{
var path = explorerPresenter.MainPresenter.AskUserForOpenFileName("*.csv");
if (path != null)
{
textFile.Open(path);
var disabledSimsTable = new DataView(textFile.ToTable());
disabledSimsTable.RowFilter = "Enabled = False";
experiment.DisabledSimNames = DataTableUtilities.GetColumnAsStrings(disabledSimsTable, "SimulationName").ToList();
}
PopulateView();
explorerPresenter.MainPresenter.ShowMessage("Successfully imported data from " + path, Simulation.MessageType.Information);
}
catch (Exception e)
{
explorerPresenter.MainPresenter.ShowError(e);
}
finally
{
textFile.Close();
}
}
public void TestEnumReporting()
{
Simulations sims = Utilities.GetRunnableSim();
IModel paddock = sims.FindInScope <Zone>();
Manager script = new Manager();
script.Name = "Manager";
script.Code = @"using System;
using Models.Core;
using Models.PMF;
namespace Models
{
[Serializable]
public class Script : Model
{
public enum TestEnum
{
Red,
Green,
Blue
};
public TestEnum Value { get; set; }
}
}";
paddock.Children.Add(script);
script.Parent = paddock;
script.OnCreated();
Report report = sims.FindInScope <Report>();
report.VariableNames = new string[]
{
"[Manager].Script.Value as x"
};
Runner runner = new Runner(sims);
List <Exception> errors = runner.Run();
if (errors != null && errors.Count > 0)
{
throw new Exception("Errors while running sims", errors[0]);
}
List <string> fieldNames = new List <string>()
{
"x"
};
IDataStore storage = sims.FindInScope <IDataStore>();
DataTable data = storage.Reader.GetData("Report", fieldNames: fieldNames);
string[] actual = DataTableUtilities.GetColumnAsStrings(data, "x");
// The enum values should have been cast to strings before being reported.
string[] expected = Enumerable.Repeat("Red", actual.Length).ToArray();
Assert.AreEqual(expected, actual);
}
/// <summary>Return a list of code values for the specified variable.</summary>
private static string[] GetCodeValues(DataTable table, string variableName, int row, int numRows)
{
if (!table.Columns.Contains(variableName))
{
return(null);
}
return(DataTableUtilities.GetColumnAsStrings(table, variableName, numRows, row));
}
/// <summary>Get a list of values for a field.</summary>
/// <param name="fieldName">The name of the field.</param>
public IEnumerable <object> GetValues(string fieldName)
{
if (filter.Table.Columns[fieldName].DataType == typeof(DateTime))
{
return(DataTableUtilities.GetColumnAsDates(filter, fieldName).Cast <object>());
}
else
{
return(DataTableUtilities.GetColumnAsStrings(filter, fieldName).Cast <string>());
}
}
/// <summary>
/// Return an array of values for the specified column.
/// </summary>
private static string[] GetStringValues(DataTable table, string variableName, int startRow, int numRows)
{
if (table.Columns.Contains(variableName))
{
string[] Values = DataTableUtilities.GetColumnAsStrings(table, variableName, numRows, startRow);
if (MathUtilities.ValuesInArray(Values))
{
return(Values);
}
}
return(null);
}
/// <summary>
/// Using the SimulationName column in the specified 'table', add a
/// SimulationID column.
/// </summary>
/// <param name="table">The table.</param>
/// <exception cref="Models.Core.ApsimXException">Cannot find Simulations table</exception>
private void AddSimulationIDColumnToTable(DataTable table)
{
// Get a list of simulations that are in the DB
DataTable DB = Connection.ExecuteQuery("SELECT * FROM Simulations");
List <string> simulationNamesInDB = DataTableUtilities.GetColumnAsStrings(DB, "Name").ToList();
// Tell SQLite that we're beginning a transaction.
Connection.ExecuteNonQuery("BEGIN");
try
{
// For those simulations in 'table' that aren't in the DB, add them
// to the simulations table
List <string> simulationNamesInTable = DataTableUtilities.GetDistinctValues(table, "SimulationName");
foreach (string simulationNameInTable in simulationNamesInTable)
{
if (!StringUtilities.Contains(simulationNamesInDB, simulationNameInTable))
{
RunQueryWithNoReturnData("INSERT INTO [Simulations] (Name) VALUES ('" + simulationNameInTable + "')");
}
}
}
finally
{
// Tell SQLite that we're ending a transaction.
Connection.ExecuteNonQuery("END");
}
// Get a list of simulation names and IDs from DB
DB = Connection.ExecuteQuery("SELECT * FROM Simulations");
List <double> ids = DataTableUtilities.GetColumnAsDoubles(DB, "ID").ToList();
simulationNamesInDB = DataTableUtilities.GetColumnAsStrings(DB, "Name").ToList();
table.Columns.Add("SimulationID", typeof(int)).SetOrdinal(0);
foreach (DataRow row in table.Rows)
{
string simulationName = row["SimulationName"].ToString();
if (simulationName != null)
{
int index = StringUtilities.IndexOfCaseInsensitive(simulationNamesInDB, simulationName);
if (index == -1)
{
throw new Exception("Cannot find simulation name: " + simulationName);
}
else
{
row["SimulationID"] = ids[index];
}
}
}
}
/// <summary>Gets a column of data from a table.</summary>
/// <param name="data">The table</param>
/// <param name="fieldName">Name of the field.</param>
/// <returns>The column of data.</returns>
private IEnumerable GetDataFromTable(DataView data, string fieldName)
{
if (data.Table.Columns[fieldName].DataType == typeof(DateTime))
{
return(DataTableUtilities.GetColumnAsDates(data, fieldName));
}
else if (data.Table.Columns[fieldName].DataType == typeof(string))
{
return(DataTableUtilities.GetColumnAsStrings(data, fieldName));
}
else
{
return(DataTableUtilities.GetColumnAsDoubles(data, fieldName));
}
}
public void DeleteTable_TableName_TableDeleted()
{
using (DataStore storage = new DataStore(fileName))
{
CreateTable(storage);
storage.DeleteTable("Report1");
Assert.IsFalse(storage.TableNames.Contains("Report1"));
}
SQLite database = new SQLite();
database.OpenDatabase(fileName, true);
DataTable tableData = database.ExecuteQuery("SELECT * FROM sqlite_master");
database.CloseDatabase();
string[] tableNames = DataTableUtilities.GetColumnAsStrings(tableData, "Name");
Assert.AreEqual(Array.IndexOf(tableNames, "Report1"), -1);
}
/// <summary>Called by the graph presenter to get a list of all annotations to put on the graph.</summary>
/// <param name="annotations">A list of annotations to add to.</param>
public void GetAnnotationsToPutOnGraph(List <Annotation> annotations)
{
Graph parentGraph = Parent.Parent as Graph;
if (data != null && ColumnName != null && xFieldName != null)
{
string phenologyColumnName = FindPhenologyStageColumn(data);
if (phenologyColumnName != null && data.Columns.Contains(xFieldName))
{
string[] names = DataTableUtilities.GetColumnAsStrings(data, phenologyColumnName);
DateTime[] dates = DataTableUtilities.GetColumnAsDates(data, xFieldName);
if (names.Length == dates.Length)
{
for (int i = 0; i < names.Length; i++)
{
if (names[i] != "?" && !string.IsNullOrEmpty(names[i]))
{
// Add a line annotation.
LineAnnotation line = new LineAnnotation();
line.colour = Color.Black;
line.type = LineType.Dot;
line.x1 = dates[i];
line.y1 = double.MinValue;
line.x2 = dates[i];
line.y2 = double.MaxValue;
annotations.Add(line);
// Add a text annotation.
TextAnnotation text = new TextAnnotation();
text.text = names[i];
text.colour = Color.Black;
text.leftAlign = true;
text.x = dates[i];
text.y = double.MinValue;
text.textRotation = 270;
annotations.Add(text);
}
}
}
}
}
}
/// <summary>Gets a column of data from a table.</summary>
/// <param name="data">The table</param>
/// <param name="fieldName">Name of the field.</param>
/// <returns>The column of data.</returns>
private IEnumerable GetDataFromTable(DataTable data, string fieldName)
{
if (fieldName != null && data != null && data.Columns.Contains(fieldName))
{
if (data.Columns[fieldName].DataType == typeof(DateTime))
{
return(DataTableUtilities.GetColumnAsDates(data, fieldName));
}
else if (data.Columns[fieldName].DataType == typeof(string))
{
return(DataTableUtilities.GetColumnAsStrings(data, fieldName));
}
else
{
return(DataTableUtilities.GetColumnAsDoubles(data, fieldName));
}
}
return(null);
}
/// <summary>
/// Upgrades to version 25. Add checkpoint fields and table to .db
/// </summary>
/// <param name="node">The node to upgrade.</param>
/// <param name="fileName">The name of the .apsimx file</param>
private static void UpgradeToVersion25(XmlNode node, string fileName)
{
string dbFileName = Path.ChangeExtension(fileName, ".db");
if (File.Exists(dbFileName))
{
SQLite connection = new SQLite();
connection.OpenDatabase(dbFileName, false);
try
{
DataTable tableData = connection.ExecuteQuery("SELECT * FROM sqlite_master");
List <string> tableNames = DataTableUtilities.GetColumnAsStrings(tableData, "Name").ToList();
if (!tableNames.Contains("_Checkpoints"))
{
connection.ExecuteNonQuery("BEGIN");
foreach (string tableName in tableNames)
{
List <string> columnNames = connection.GetColumnNames(tableName);
if (columnNames.Contains("SimulationID"))
{
connection.ExecuteNonQuery("ALTER TABLE " + tableName + " ADD COLUMN CheckpointID INTEGER DEFAULT 1");
}
}
// Now add a _checkpointfiles table.
connection.ExecuteNonQuery("CREATE TABLE _Checkpoints (ID INTEGER PRIMARY KEY ASC, Name TEXT, Version TEXT, Date TEXT)");
connection.ExecuteNonQuery("CREATE TABLE _CheckpointFiles (CheckpointID INTEGER, FileName TEXT, Contents BLOB)");
connection.ExecuteNonQuery("INSERT INTO [_Checkpoints] (Name) VALUES (\"Current\")");
connection.ExecuteNonQuery("END");
}
}
finally
{
connection.CloseDatabase();
}
}
}
/// <summary>
/// Using the SimulationName column in the specified 'table', add a
/// SimulationID column.
/// </summary>
/// <param name="table">The table.</param>
/// <exception cref="Models.Core.ApsimXException">Cannot find Simulations table</exception>
private void AddSimulationIDColumnToTable(DataTable table)
{
DataTable idTable = Connection.ExecuteQuery("SELECT * FROM Simulations");
if (idTable == null)
{
throw new ApsimXException(this, "Cannot find Simulations table");
}
List <double> ids = new List <double>();
ids.AddRange(DataTableUtilities.GetColumnAsDoubles(idTable, "ID"));
List <string> simulationNames = new List <string>();
simulationNames.AddRange(DataTableUtilities.GetColumnAsStrings(idTable, "Name"));
table.Columns.Add("SimulationID", typeof(int)).SetOrdinal(0);
foreach (DataRow row in table.Rows)
{
string simulationName = row["SimulationName"].ToString();
if (simulationName != null)
{
int index = StringUtilities.IndexOfCaseInsensitive(simulationNames, simulationName);
if (index != -1)
{
row["SimulationID"] = ids[index];
}
else
{
int id = GetSimulationID(simulationName);
ids.Add(id);
simulationNames.Add(simulationName);
row["SimulationID"] = id;
}
}
}
}
/// <summary>Return a list of table names</summary>
/// <param name="connection">Database connection.</param>
public List <string> GetTableNames(SqlConnection connection)
{
List <string> tableNames = new List <string>();
var sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES";
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
using (var reader = cmd.ExecuteReader())
{
var tableData = new DataTable();
tableData.Load(reader);
var names = DataTableUtilities.GetColumnAsStrings(tableData, "TABLE_NAME");
var types = DataTableUtilities.GetColumnAsStrings(tableData, "TABLE_TYPE");
for (int i = 0; i < names.Length; i++)
{
if (types[i].Contains("TABLE"))
{
tableNames.Add(names[i]);
}
}
return(tableNames);
}
}
}
/// <summary>
/// Get a list of row filters that define the blocks of data that we have
/// to calculate stats for.
/// </summary>
/// <returns></returns>
private List <string> GetRowFilters(DataTable data)
{
var rowFilters = new List <string>();
List <List <string> > fieldValues = new List <List <string> >();
foreach (var fieldName in FieldNamesToSplitOn)
{
if (!data.Columns.Contains(fieldName))
{
throw new Exception($"Cannot find field {fieldName} in table {data.TableName}");
}
fieldValues.Add(DataTableUtilities.GetColumnAsStrings(data, fieldName).Distinct().ToList());
}
var permutations = MathUtilities.AllCombinationsOf <string>(fieldValues.ToArray());
foreach (var permutation in permutations)
{
rowFilters.Add(CreateRowFilter(permutation, data));
}
return(rowFilters);
}
/// <summary>Main run method for performing our calculations and storing data.</summary>
public void Run()
{
if (dataStore?.Writer != null && !dataStore.Writer.TablesModified.Contains("Report"))
{
return;
}
DataTable predictedData = dataStore.Reader.GetData("Report", filter: "SimulationName LIKE '" + Name + "%'", orderBy: "SimulationID");
if (predictedData != null)
{
IndexedDataTable variableValues = new IndexedDataTable(null);
// Determine how many years we have per simulation
DataView view = new DataView(predictedData);
view.RowFilter = "SimulationName='" + Name + "Simulation1'";
var Years = DataTableUtilities.GetColumnAsIntegers(view, "Clock.Today.Year");
// Create a results table.
IndexedDataTable results;
if (Years.Count() > 1)
{
results = new IndexedDataTable(new string[] { "Year" });
}
else
{
results = new IndexedDataTable(null);
}
// Loop through all years and perform analysis on each.
List <string> errorsFromR = new List <string>();
foreach (double year in Years)
{
view.RowFilter = "Clock.Today.Year=" + year;
foreach (DataColumn predictedColumn in predictedData.Columns)
{
if (predictedColumn.DataType == typeof(double))
{
var values = DataTableUtilities.GetColumnAsDoubles(view, predictedColumn.ColumnName);
if (values.Distinct().Count() > 1)
{
variableValues.SetValues(predictedColumn.ColumnName, values);
}
}
}
string paramNames = StringUtilities.Build(Parameters.Select(p => p.Name), ",", "\"", "\"");
string sobolx1FileName = GetTempFileName("sobolx1", ".csv");
string sobolx2FileName = GetTempFileName("sobolx2", ".csv");
string sobolVariableValuesFileName = GetTempFileName("sobolvariableValues", ".csv");
// Write variables file
using (var writer = new StreamWriter(sobolVariableValuesFileName))
DataTableUtilities.DataTableToText(variableValues.ToTable(), 0, ",", true, writer, excelFriendly: false, decimalFormatString: "F6");
// Write X1
using (var writer = new StreamWriter(sobolx1FileName))
DataTableUtilities.DataTableToText(X1, 0, ",", true, writer, excelFriendly: false, decimalFormatString: "F6");
// Write X2
using (var writer = new StreamWriter(sobolx2FileName))
DataTableUtilities.DataTableToText(X2, 0, ",", true, writer, excelFriendly: false, decimalFormatString: "F6");
string script = string.Format(
$".libPaths(c('{R.PackagesDirectory}', .libPaths()))" + Environment.NewLine +
$"library('boot', lib.loc = '{R.PackagesDirectory}')" + Environment.NewLine +
$"library('sensitivity', lib.loc = '{R.PackagesDirectory}')" + Environment.NewLine +
"params <- c({0})" + Environment.NewLine +
"n <- {1}" + Environment.NewLine +
"nparams <- {2}" + Environment.NewLine +
"X1 <- read.csv(\"{3}\")" + Environment.NewLine +
"X2 <- read.csv(\"{4}\")" + Environment.NewLine +
"sa <- sobolSalt(model = NULL, X1, X2, scheme=\"A\", nboot = 100)" + Environment.NewLine +
"variableValues = read.csv(\"{5}\")" + Environment.NewLine +
"for (columnName in colnames(variableValues))" + Environment.NewLine +
"{{" + Environment.NewLine +
" sa$y <- variableValues[[columnName]]" + Environment.NewLine +
" tell(sa)" + Environment.NewLine +
" sa$S$Parameter <- params" + Environment.NewLine +
" sa$T$Parameter <- params" + Environment.NewLine +
" sa$S$ColumnName <- columnName" + Environment.NewLine +
" sa$T$ColumnName <- columnName" + Environment.NewLine +
" sa$S$Indices <- \"FirstOrder\"" + Environment.NewLine +
" sa$T$Indices <- \"Total\"" + Environment.NewLine +
" if (!exists(\"allData\"))" + Environment.NewLine +
" allData <- rbind(sa$S, sa$T)" + Environment.NewLine +
" else" + Environment.NewLine +
" allData <- rbind(allData, sa$S, sa$T)" + Environment.NewLine +
"}}" + Environment.NewLine +
"write.table(allData, sep=\",\", row.names=FALSE)" + Environment.NewLine
,
paramNames, NumPaths, Parameters.Count,
sobolx1FileName.Replace("\\", "/"),
sobolx1FileName.Replace("\\", "/"),
sobolVariableValuesFileName.Replace("\\", "/"));
DataTable resultsForYear = null;
try
{
resultsForYear = RunR(script);
// Put output from R into results table.
if (Years.Count() > 1)
{
results.SetIndex(new object[] { year.ToString() });
}
foreach (DataColumn col in resultsForYear.Columns)
{
if (col.DataType == typeof(string))
{
results.SetValues(col.ColumnName, DataTableUtilities.GetColumnAsStrings(resultsForYear, col.ColumnName));
}
else
{
results.SetValues(col.ColumnName, DataTableUtilities.GetColumnAsDoubles(resultsForYear, col.ColumnName));
}
}
}
catch (Exception err)
{
string msg = err.Message;
if (Years.Count() > 1)
{
msg = "Year " + year + ": " + msg;
}
errorsFromR.Add(msg);
}
}
var resultsRawTable = results.ToTable();
resultsRawTable.TableName = Name + "Statistics";
dataStore.Writer.WriteTable(resultsRawTable);
if (errorsFromR.Count > 0)
{
string msg = StringUtilities.BuildString(errorsFromR.ToArray(), Environment.NewLine);
throw new Exception(msg);
}
}
}
/// <summary>
/// Save the grid back to the model.
/// </summary>
private void SaveGrid()
{
this.presenter.CommandHistory.ModelChanged -= this.OnModelChanged;
// Get the data source of the profile grid.
DataTable data = this.xYPairsView.VariablesGrid.DataSource;
// Maintain a list of all property changes that we need to make.
List <Commands.ChangeProperty.Property> properties = new List <Commands.ChangeProperty.Property>();
// add missing data as 0 otherwise it will throw an exception
// could make this work as an entire row, but will stick to X & Y columns for now
/*
* for (int Row = 0; Row != data.Rows.Count; Row++)
* {
* if (data.Rows[Row]["Y"].ToString() == "" && data.Rows[Row]["X"].ToString() != "")
* data.Rows[Row]["Y"] = "0";
* if (data.Rows[Row]["X"].ToString() == "" && data.Rows[Row]["Y"].ToString() != "")
* data.Rows[Row]["X"] = "0";
* if (data.Rows[Row]["Y"].ToString() == "" && data.Rows[Row]["X"].ToString() == "")
* break;
* }
*/
//// Loop through all non-readonly properties, get an array of values from the data table
//// for the property and then set the property value.
for (int i = 0; i < this.propertiesInGrid.Count; i++)
{
// If this property is NOT readonly then set its value.
if (!this.propertiesInGrid[i].IsReadOnly)
{
// Get an array of values for this property.
Array values;
if (this.propertiesInGrid[i].DataType.GetElementType() == typeof(double))
{
values = DataTableUtilities.GetColumnAsDoubles(data, data.Columns[i].ColumnName);
if (!MathUtilities.ValuesInArray((double[])values))
{
values = null;
}
else
{
values = MathUtilities.RemoveMissingValuesFromBottom((double[])values);
}
}
else
{
values = DataTableUtilities.GetColumnAsStrings(data, data.Columns[i].ColumnName);
values = MathUtilities.RemoveMissingValuesFromBottom((string[])values);
}
// Is the value any different to the former property value?
bool changedValues;
if (this.propertiesInGrid[i].DataType == typeof(double[]))
{
changedValues = !MathUtilities.AreEqual((double[])values, (double[])this.propertiesInGrid[i].Value);
}
else
{
changedValues = !MathUtilities.AreEqual((string[])values, (string[])this.propertiesInGrid[i].Value);
}
if (changedValues)
{
// Store the property change.
Commands.ChangeProperty.Property property =
new Commands.ChangeProperty.Property(this.propertiesInGrid[i].Object, this.propertiesInGrid[i].Name, values);
properties.Add(property);
}
}
}
// If there are property changes pending, then commit the changes in a block.
if (properties.Count > 0)
{
Commands.ChangeProperty command = new Commands.ChangeProperty(properties);
this.presenter.CommandHistory.Add(command);
}
this.presenter.CommandHistory.ModelChanged += this.OnModelChanged;
}
/// <summary>Called by the graph presenter to get a list of all annotations to put on the graph.</summary>
/// <param name="annotations">A list of annotations to add to.</param>
public void GetAnnotationsToPutOnGraph(List <Annotation> annotations)
{
Graph parentGraph = Parent.Parent as Graph;
if (data != null && ColumnName != null && xFieldName != null)
{
string columnName = FindColumn(data);
if (columnName != null && data.Columns.Contains(xFieldName))
{
string[] names = DataTableUtilities.GetColumnAsStrings(data, columnName);
List <object> x;
Type columnType = data.Columns[xFieldName].DataType;
if (columnType == typeof(DateTime))
{
x = DataTableUtilities.GetColumnAsDates(data, xFieldName).Cast <object>().ToList();
}
else if (columnType == typeof(int))
{
x = DataTableUtilities.GetColumnAsIntegers(data, xFieldName).Cast <object>().ToList();
}
else if (columnType == typeof(double))
{
x = DataTableUtilities.GetColumnAsDoubles(data, xFieldName).Cast <object>().ToList();
}
else
{
throw new Exception($"Error in EventNamesOnGraph {Name}: unknown column type '{columnType.FullName}' in column '{xFieldName}'");
}
if (names.Length == x.Count)
{
var baseColour = Color.LightBlue;
var colourMap = new Dictionary <string, Color>();
int startIndex = -1;
string startName = string.Empty;
for (int i = 0; i < names.Length; i++)
{
if (startIndex == -1)
{
startIndex = i;
startName = names[i];
}
else if (names[i] != startName)
{
if (!string.IsNullOrEmpty(startName))
{
// Add a line annotation.
AddAnnotation(annotations, x, baseColour, colourMap, startIndex, startName, i);
}
startName = names[i];
startIndex = i;
}
}
if (startIndex != -1)
{
AddAnnotation(annotations, x, baseColour, colourMap, startIndex, startName, names.Length);
}
}
}
}
}
public DataTable GetData(string tableName, string checkpointName = null, IEnumerable <string> simulationNames = null, IEnumerable <string> fieldNames = null, string filter = null, int from = 0, int count = 0, IEnumerable <string> orderBy = null, bool distinct = false)
{
string rowFilter = null;
if (checkpointName != null)
{
rowFilter += "CheckpointName = '" + checkpointName + "'";
}
if (simulationNames != null && simulationNames.Any())
{
if (rowFilter != null)
{
rowFilter += " AND ";
}
rowFilter += $"SimulationName in ({simulationNames.Enclose("'","'").Join(",")})";
}
if (filter != null)
{
if (rowFilter != null)
{
rowFilter += " AND ";
}
rowFilter += "(" + filter + ")";
}
if (rowFilter == null)
{
return(data);
}
else if (fieldNames == null)
{
var view = new DataView(data);
view.RowFilter = rowFilter;
return(view.ToTable());
}
else
{
var dataCopy = data.Copy();
foreach (DataColumn column in data.Columns)
{
if (!fieldNames.Contains(column.ColumnName) &&
column.ColumnName != "CheckpointName" &&
column.ColumnName != "SimulationName" &&
column.ColumnName != "SimulationID")
{
dataCopy.Columns.Remove(column.ColumnName);
}
}
// Add in a simulation name column if it doesn't exist.
if (dataCopy.Columns.Contains("SimulationID") && !dataCopy.Columns.Contains("SimulationName"))
{
dataCopy.Columns.Add("SimulationName", typeof(string));
foreach (DataRow row in dataCopy.Rows)
{
row["SimulationName"] = "Sim" + row["SimulationID"].ToString();
}
}
var view = new DataView(dataCopy);
view.RowFilter = rowFilter;
if (distinct)
{
var column = dataCopy.Columns[fieldNames.First()];
var columnName = column.ColumnName;
var columnType = column.DataType;
var values = DataTableUtilities.GetColumnAsStrings(view, columnName).Distinct().ToArray();
var data = new DataTable();
//data.Columns.Add(columnName, columnType);
DataTableUtilities.AddColumn(data, columnName, values);
return(data);
}
else
{
return(view.ToTable());
}
}
}
/// <summary>Does the specified table exist?</summary>
/// <param name="connection">SQLite connection</param>
/// <param name="tableName">The table name to look for</param>
private bool TableExists(SQLite connection, string tableName)
{
List <string> tableNames = DataTableUtilities.GetColumnAsStrings(connection.ExecuteQuery("SELECT * FROM sqlite_master"), "Name").ToList();
return(tableNames.Contains(tableName));
}
/// <summary>Gets all series data and stores in the specified definition.</summary>
/// <param name="definition">The definition to store the data in.</param>
private void GetData(SeriesDefinition definition)
{
// If the table name is null then use reflection to get data from other models.
if (TableName == null)
{
if (!String.IsNullOrEmpty(XFieldName))
{
definition.x = GetDataFromModels(XFieldName);
}
if (!String.IsNullOrEmpty(YFieldName))
{
definition.y = GetDataFromModels(YFieldName);
}
if (!String.IsNullOrEmpty(X2FieldName))
{
definition.x2 = GetDataFromModels(X2FieldName);
}
if (!String.IsNullOrEmpty(Y2FieldName))
{
definition.y2 = GetDataFromModels(Y2FieldName);
}
}
else
{
Graph parentGraph = Parent as Graph;
if (parentGraph != null)
{
DataTable data = parentGraph.GetBaseData(TableName);
if (data != null)
{
string[] names = DataTableUtilities.GetColumnAsStrings(data, "SimulationName");
string FilterExpression = "";
if (Filter != null && Filter != string.Empty)
{
FilterExpression = Filter.Replace("[", "");
FilterExpression = FilterExpression.Replace("]", "");
}
string where = "(";
if (Filter != null && Filter != string.Empty)
{
where += "(";
}
where += definition.Filter;
if (Filter != null && Filter != string.Empty)
{
where += ") AND (" + FilterExpression + ")";
}
where += ")";
DataView dataView = new DataView(data);
if (where != "()")
{
try
{
dataView.RowFilter = where;
}
catch (Exception)
{
}
}
// If the field exists in our data table then return it.
if (data != null &&
XFieldName != null &&
YFieldName != null &&
data.Columns.Contains(XFieldName) &&
data.Columns.Contains(YFieldName) &&
dataView.Count > 0)
{
definition.x = GetDataFromTable(dataView, XFieldName);
definition.y = GetDataFromTable(dataView, YFieldName);
if (Cumulative)
{
definition.y = MathUtilities.Cumulative(definition.y as IEnumerable <double>);
}
if (CumulativeX)
{
definition.x = MathUtilities.Cumulative(definition.x as IEnumerable <double>);
}
if (X2FieldName != null && Y2FieldName != null &&
data.Columns.Contains(X2FieldName) && data.Columns.Contains(Y2FieldName))
{
definition.x2 = GetDataFromTable(dataView, X2FieldName);
definition.y2 = GetDataFromTable(dataView, Y2FieldName);
}
definition.simulationNamesForEachPoint = (IEnumerable <string>)GetDataFromTable(dataView, "SimulationName");
}
}
}
}
}
/// <summary>
/// Save the grid back to the model.
/// </summary>
private void SaveGrid()
{
try
{
this.explorerPresenter.CommandHistory.ModelChanged -= this.OnModelChanged;
// Get the data source of the profile grid.
DataTable data = this.view.ProfileGrid.DataSource;
// Maintain a list of all property changes that we need to make.
List <Commands.ChangeProperty.Property> properties = new List <Commands.ChangeProperty.Property>();
// Loop through all non-readonly properties, get an array of values from the data table
// for the property and then set the property value.
for (int i = 0; i < this.propertiesInGrid.Count; i++)
{
// If this property is NOT readonly then set its value.
if (!this.propertiesInGrid[i].IsReadOnly)
{
// Get an array of values for this property.
Array values;
if (this.propertiesInGrid[i].DataType.GetElementType() == typeof(double))
{
values = DataTableUtilities.GetColumnAsDoubles(data, data.Columns[i].ColumnName);
if (!MathUtilities.ValuesInArray((double[])values))
{
values = null;
}
else
{
values = MathUtilities.RemoveMissingValuesFromBottom((double[])values);
}
}
else
{
values = DataTableUtilities.GetColumnAsStrings(data, data.Columns[i].ColumnName);
values = MathUtilities.RemoveMissingValuesFromBottom((string[])values);
}
// Is the value any different to the former property value?
bool changedValues;
if (this.propertiesInGrid[i].DataType == typeof(double[]))
{
changedValues = !MathUtilities.AreEqual((double[])values, (double[])this.propertiesInGrid[i].Value);
}
else
{
changedValues = !MathUtilities.AreEqual((string[])values, (string[])this.propertiesInGrid[i].Value);
}
if (changedValues)
{
// Store the property change.
Commands.ChangeProperty.Property property =
new Commands.ChangeProperty.Property(this.propertiesInGrid[i].Object, this.propertiesInGrid[i].Name, values);
properties.Add(property);
}
}
}
// If there are property changes pending, then commit the changes in a block.
if (properties.Count > 0)
{
Commands.ChangeProperty command = new Commands.ChangeProperty(properties);
this.explorerPresenter.CommandHistory.Add(command);
}
this.explorerPresenter.CommandHistory.ModelChanged += this.OnModelChanged;
}
catch (Exception e)
{
if (e is System.Reflection.TargetInvocationException)
{
e = (e as System.Reflection.TargetInvocationException).InnerException;
}
this.explorerPresenter.MainPresenter.ShowError(e);
}
}
/// <summary>Called by the graph presenter to get a list of all annotations to put on the graph.</summary>
public IEnumerable <IAnnotation> GetAnnotations()
{
List <IAnnotation> annotations = new List <IAnnotation>();
Graph parentGraph = Parent.Parent as Graph;
if (data != null && ColumnName != null && xFieldName != null)
{
string phenologyColumnName = FindPhenologyStageColumn(data);
if (phenologyColumnName != null && data.Columns.Contains(xFieldName))
{
string[] names = DataTableUtilities.GetColumnAsStrings(data, phenologyColumnName);
List <object> x;
Type columnType = data.Columns[xFieldName].DataType;
if (columnType == typeof(DateTime))
{
x = DataTableUtilities.GetColumnAsDates(data, xFieldName).Cast <object>().ToList();
}
else if (columnType == typeof(int))
{
x = DataTableUtilities.GetColumnAsIntegers(data, xFieldName).Cast <object>().ToList();
}
else if (columnType == typeof(double))
{
x = DataTableUtilities.GetColumnAsDoubles(data, xFieldName).Cast <object>().ToList();
}
else
{
throw new Exception($"Error in EventNamesOnGraph {Name}: unknown column type '{columnType.FullName}' in column '{xFieldName}'");
}
if (names.Length == x.Count)
{
for (int i = 0; i < names.Length; i++)
{
if (names[i] != "?" && !string.IsNullOrEmpty(names[i]))
{
// Add a line annotation.
LineAnnotation line = new LineAnnotation();
line.colour = Color.Black;
line.type = LineType.Dot;
line.x1 = x[i];
line.y1 = double.MinValue;
line.x2 = x[i];
line.y2 = double.MaxValue;
annotations.Add(line);
// Add a text annotation.
TextAnnotation text = new TextAnnotation();
text.text = names[i];
text.colour = Color.Black;
text.leftAlign = true;
text.x = x[i];
text.y = double.MinValue;
text.textRotation = 270;
annotations.Add(text);
}
}
}
}
}
return(annotations);
}
/// <summary>Main run method for performing our post simulation calculations</summary>
public void Run()
{
// If the predicted table has not been modified, don't do anything.
// This can happen if other simulations were run but the Morris model was not.
if (dataStore?.Writer != null && !dataStore.Writer.TablesModified.Contains(TableName))
{
return;
}
DataTable predictedData = dataStore.Reader.GetData(TableName);
if (predictedData != null)
{
// Determine how many aggregation values we have per simulation
DataView view = new DataView(predictedData);
view.RowFilter = "SimulationName='" + Name + "Simulation1'";
AggregationValues = DataTableUtilities.GetColumnAsStrings(view, AggregationVariableName);
// Create a table of all predicted values
DataTable predictedValues = new DataTable();
List <string> descriptiveColumnNames = new List <string>();
List <string> variableNames = new List <string>();
foreach (string aggregationValue in AggregationValues)
{
string value = aggregationValue;
if (DateTime.TryParse(value, out DateTime date))
{
value = date.ToString("yyyy-MM-dd");
}
view.RowFilter = $"{AggregationVariableName}='{value}'";
foreach (DataColumn predictedColumn in view.Table.Columns)
{
if (predictedColumn.DataType == typeof(double))
{
double[] values = DataTableUtilities.GetColumnAsDoubles(view, predictedColumn.ColumnName);
if (values.Distinct().Count() == 1)
{
if (!descriptiveColumnNames.Contains(predictedColumn.ColumnName))
{
descriptiveColumnNames.Add(predictedColumn.ColumnName);
}
}
else
{
DataTableUtilities.AddColumn(predictedValues, predictedColumn.ColumnName + "_" + value, values);
if (!variableNames.Contains(predictedColumn.ColumnName))
{
variableNames.Add(predictedColumn.ColumnName);
}
}
}
}
}
// Run R
DataTable eeDataRaw;
DataTable statsDataRaw;
RunRPostSimulation(predictedValues, out eeDataRaw, out statsDataRaw);
// Get ee data from R and store in ee table.
// EE data from R looks like:
// "ResidueWt", "FASW", "CN2", "Cona", "variable","path"
// - 22.971008269563,0.00950570342209862,-0.00379987333757356,56.7587080430652,"FallowEvaporation1996",1
// - 25.790599484188, 0.0170777988614538, -0.0265991133629069,58.0240658644712,"FallowEvaporation1996",2
// - 26.113599477728, 0.0113851992409871, 0.0113996200126667,57.9689677010766,"FallowEvaporation1996",3
// - 33.284199334316, 0.0323193916349732, -0.334388853704853,60.5376820772641,"FallowEvaporation1996",4
DataView eeView = new DataView(eeDataRaw);
IndexedDataTable eeTableKey = new IndexedDataTable(new string[] { "Parameter", AggregationVariableName });
// Create a path variable.
var pathValues = Enumerable.Range(1, NumPaths).ToArray();
foreach (var parameter in Parameters)
{
foreach (DataColumn column in predictedValues.Columns)
{
eeView.RowFilter = "variable = '" + column.ColumnName + "'";
if (eeView.Count != NumPaths)
{
throw new Exception("Found only " + eeView.Count + " paths for variable " + column.ColumnName + " in ee table");
}
string aggregationValue = StringUtilities.GetAfter(column.ColumnName, "_");
string variableName = StringUtilities.RemoveAfter(column.ColumnName, '_');
eeTableKey.SetIndex(new object[] { parameter.Name, aggregationValue });
List <double> values = DataTableUtilities.GetColumnAsDoubles(eeView, parameter.Name).ToList();
for (int i = 0; i < values.Count; i++)
{
values[i] = Math.Abs(values[i]);
}
var runningMean = MathUtilities.RunningAverage(values);
eeTableKey.SetValues("Path", pathValues);
eeTableKey.SetValues(variableName + ".MuStar", runningMean);
}
}
DataTable eeTable = eeTableKey.ToTable();
eeTable.TableName = Name + "PathAnalysis";
// Get stats data from R and store in MuStar table.
// Stats data coming back from R looks like:
// "mu", "mustar", "sigma", "param","variable"
// -30.7331368183818, 30.7331368183818, 5.42917964248002,"ResidueWt","FallowEvaporation1996"
// -0.0731299918470997,0.105740687296631,0.450848277601353, "FASW","FallowEvaporation1996"
// -0.83061431285624,0.839772007599748, 1.75541097254145, "CN2","FallowEvaporation1996"
// 62.6942591520838, 62.6942591520838, 5.22778043503867, "Cona","FallowEvaporation1996"
// -17.286285468283, 19.4018404625051, 24.1361388348929,"ResidueWt","FallowRunoff1996"
// 8.09850688306722, 8.09852589447407, 15.1988107373113, "FASW","FallowRunoff1996"
// 18.6196168461051, 18.6196168461051, 15.1496277765849, "CN2","FallowRunoff1996"
// -7.12794888887507, 7.12794888887507, 5.54014788597839, "Cona","FallowRunoff1996"
IndexedDataTable tableKey = new IndexedDataTable(new string[2] {
"Parameter", AggregationVariableName
});
foreach (DataRow row in statsDataRaw.Rows)
{
string variable = row["variable"].ToString();
string aggregationValue = StringUtilities.GetAfter(variable, "_");
variable = StringUtilities.RemoveAfter(variable, '_');
tableKey.SetIndex(new object[] { row["param"], aggregationValue });
tableKey.Set(variable + ".Mu", row["mu"]);
tableKey.Set(variable + ".MuStar", row["mustar"]);
tableKey.Set(variable + ".Sigma", row["sigma"]);
// Need to bring in the descriptive values.
view.RowFilter = $"{AggregationVariableName}='{aggregationValue}'";
foreach (var descriptiveColumnName in descriptiveColumnNames)
{
var values = DataTableUtilities.GetColumnAsStrings(view, descriptiveColumnName);
if (values.Distinct().Count() == 1)
{
tableKey.Set(descriptiveColumnName, view[0][descriptiveColumnName]);
}
}
}
DataTable muStarTable = tableKey.ToTable();
muStarTable.TableName = Name + "Statistics";
dataStore.Writer.WriteTable(eeTable);
dataStore.Writer.WriteTable(muStarTable);
}
}