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