/// <summary>This refreshes data being displayed on the graphs, based on the value of the startYear and showYear values </summary> /// <param name="table">The data set</param> /// <param name="startDate">The start date</param> /// <param name="endDate">The end date</param> /// <param name="updateYears">Update the years</param> private void DisplayGraphTemperature(DataTable table, DateTime startDate, DateTime endDate, bool updateYears) { try { if (table != null && table.Rows.Count > 0) { DateTime[] dailyDates = DataTableUtilities.GetColumnAsDates(table, "Date", startDate, endDate); double[] dailyMaxTemp = DataTableUtilities.GetColumnAsDoubles(table, "maxt", startDate, endDate); double[] dailyMinTemp = DataTableUtilities.GetColumnAsDoubles(table, "mint", startDate, endDate); if (dailyMaxTemp.Length != 0) { this.PopulateTemperatureGraph("Temperature", dailyDates, dailyMaxTemp, dailyMinTemp); } } } catch (Exception e) { throw new Exception("Unable to display Detailed Graphs due to insufficient data: " + e.Message.ToString()); } }
/// <summary> /// Overlay data in fromData on top of toData for all years found in toData. /// </summary> /// <param name="fromData">Source data</param> /// <param name="toData">Destination data</param> public static void OverlayDataAllYears(DataTable fromData, DataTable toData) { DataTable clonedData = fromData.Copy(); // Loop through all years in the long term weather data and overlay the from data onto // each year of the to data if (clonedData.Rows.Count > 0) { int firstYear = DataTableUtilities.GetDateFromRow(toData.Rows[0]).Year; int lastYear = DataTableUtilities.GetDateFromRow(toData.Rows[toData.Rows.Count - 1]).Year; for (int year = firstYear; year <= lastYear; year++) { // Before overlaying the from data we need to change the year because the // OverlayData method uses date matching. SetYearInDateColumn(clonedData, year); // Now overlay the patch data. OverlayData(clonedData, toData, true); } } }
/// <summary>Get a list of database fieldnames. /// Returns the names associated with the first table name in the property list /// </summary> /// <returns>A list of fieldnames.</returns> private string[] GetFieldNames() { string[] fieldNames = null; for (int i = 0; i < this.properties.Count; i++) { if (this.properties[i].DisplayType == DisplayAttribute.DisplayTypeEnum.TableName) { IGridCell cell = this.grid.GetCell(1, i); if (cell.Value != null && cell.Value.ToString() != string.Empty) { DataTable data = this.storage.RunQuery("SELECT * FROM " + cell.Value.ToString() + " LIMIT 1"); if (data != null) { fieldNames = DataTableUtilities.GetColumnNames(data); } } } } return(fieldNames); }
private void OnDoWeather(object sender, EventArgs e) { if (currentRowIndex >= data.Rows.Count) { throw new Exception("Have run out of weather data"); } var dateInFile = DataTableUtilities.GetDateFromRow(data.Rows[currentRowIndex]); if (currentYearIndex == -1 || (dateInFile.Day == StartDate.Day && dateInFile.Month == StartDate.Month)) { // Need to change years to next one in sequence. currentYearIndex++; if (currentYearIndex < Years.Length) { var dateToFind = new DateTime(Convert.ToInt32(Years[currentYearIndex]), StartDate.Month, StartDate.Day); currentRowIndex = FindRowForDate(dateToFind); } } MaxT = Convert.ToDouble(data.Rows[currentRowIndex]["MaxT"]); MinT = Convert.ToDouble(data.Rows[currentRowIndex]["MinT"]); Radn = Convert.ToDouble(data.Rows[currentRowIndex]["Radn"]); Rain = Convert.ToDouble(data.Rows[currentRowIndex]["Rain"]); if (data.Columns.Contains("VP")) { VP = Convert.ToDouble(data.Rows[currentRowIndex]["VP"]); } if (data.Columns.Contains("Wind")) { Wind = Convert.ToDouble(data.Rows[currentRowIndex]["Wind"]); } if (AirPressure == 0) { this.AirPressure = 1010; } currentRowIndex++; PreparingNewWeatherData?.Invoke(this, new EventArgs()); }
/// <summary>Main run method for performing our calculations and storing data.</summary> public void Run() { if (dataStore?.Writer != null) { using (var connection = new SqlConnection(ConnectionString)) { connection.Open(); foreach (var tableName in TableNames) { if (dataStore.Reader.TableNames.Contains(tableName)) { var data = dataStore.Reader.GetData(tableName); data.TableName = tableName; if (data != null) { // Strip out unwanted columns. data.Columns.Remove("CheckpointName"); data.Columns.Remove("CheckpointID"); data.Columns.Remove("SimulationID"); CreateTableIfNotExists(connection, data); var columnNames = DataTableUtilities.GetColumnNames(data).ToList(); var sql = CreateInsertSQL(tableName, columnNames); using (SqlCommand cmd = new SqlCommand(sql, connection)) { cmd.Prepare(); foreach (DataRow row in data.Rows) { BindParametersAndRunQuery(cmd, columnNames, row.ItemArray); } } } } } } } }
/// <summary> /// Add year, month, day and date columns to the specified Table. /// </summary> public static void AddDateToTable(DataTable table) { if (!table.Columns.Contains("Date")) { List <DateTime> dates = new List <DateTime>(); foreach (DataRow Row in table.Rows) { dates.Add(DataTableUtilities.GetDateFromRow(Row)); } DataTableUtilities.AddColumnOfObjects(table, "Date", dates.ToArray()); table.Columns["Date"].SetOrdinal(0); // remove year, day, pan, vp, code columns. int yearColumn = table.Columns.IndexOf("Year"); if (yearColumn != -1) { table.Columns.RemoveAt(yearColumn); } int dayColumn = table.Columns.IndexOf("Day"); if (dayColumn != -1) { table.Columns.RemoveAt(dayColumn); } int panColumn = table.Columns.IndexOf("pan"); if (panColumn != -1) { table.Columns.RemoveAt(panColumn); } int vpColumn = table.Columns.IndexOf("vp"); if (vpColumn != -1) { table.Columns.RemoveAt(vpColumn); } int codeColumn = table.Columns.IndexOf("code"); if (codeColumn != -1) { table.Columns.RemoveAt(codeColumn); } } }
/// <summary> /// Setup the profile grid based on the properties in the model. /// The column index of the cell that has changed. /// </summary> /// <returns>The filled data table. Never returns null.</returns> private DataTable CreateTable() { DataTable table = new DataTable(); foreach (VariableProperty property in this.propertiesInGrid) { string columnName = property.Description; if (property.UnitsLabel != null) { columnName += "\r\n" + property.UnitsLabel; } // add a total to the column header if necessary. double total = property.Total; if (!double.IsNaN(total)) { columnName = columnName + "\r\n" + total.ToString("N1"); } Array values = null; try { values = property.Value as Array; } catch (Exception) { } if (table.Columns.IndexOf(columnName) == -1) { table.Columns.Add(columnName, property.DataType.GetElementType()); } else { } DataTableUtilities.AddColumnOfObjects(table, columnName, values); } return(table); }
public static void TestCommentsInEventNames() { Simulations file = Utilities.GetRunnableSim(); Report report = file.FindInScope <Report>(); report.Name = "Report"; // Just to make sure report.VariableNames = new string[] { "[Clock].Today.DayOfYear as doy" }; report.EventNames = new string[] { "[Clock].StartOfWeek // works normally", "// Should be ignored", "//[Clock].EndOfWeek // entire line should be ignored" }; Clock clock = file.FindInScope <Clock>(); clock.StartDate = new DateTime(2017, 1, 1); clock.EndDate = new DateTime(2017, 3, 1); Runner runner = new Runner(file); List <Exception> errors = runner.Run(); if (errors != null && errors.Count > 0) { throw errors[0]; } List <string> fieldNames = new List <string>() { "doy" }; IDataStore storage = file.FindInScope <IDataStore>(); DataTable data = storage.Reader.GetData("Report", fieldNames: fieldNames); double[] actual = DataTableUtilities.GetColumnAsDoubles(data, "doy"); double[] expected = new double[] { 1, 8, 15, 22, 29, 36, 43, 50, 57 }; Assert.AreEqual(expected, actual); }
/// <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>Create a datatable based on the properties.</summary> /// <returns>The filled data table. Never returns null.</returns> private DataTable CreateTable() { DataTable table = new DataTable(); foreach (var column in this.columnMetadata) { string columnName = column.ColumnName; // add a total to the column name if necessary. if (column.AddTotalToColumnName) { columnName = GetColumnNameWithTotal(column.Values, columnName); } // Get values DataTableUtilities.AddColumnOfObjects(table, columnName, column.Values as IEnumerable); } // Add in a dummy column so that the far right column with data isn't really wide. table.Columns.Add(" "); return(table); }
/// <summary>Gets a filter that includes rowid to implement data pagination (rolling cursor).</summary> /// <param name="from"></param> /// <param name="count"></param> /// <returns></returns> private string GetRollingCursorRowFilter(int from, int count) { string filter = GetFilter(); var data = dataStore.GetDataUsingSql($"SELECT rowid FROM keyset WHERE rowid >= {from+1} ORDER BY rowid LIMIT {count}"); if (data is null) { return(""); } var rowIds = DataTableUtilities.GetColumnAsIntegers(data, "rowid"); var rowIdsCSV = StringUtilities.Build(rowIds, ","); var returnFilter = $"RowID in ({rowIdsCSV})"; if (!string.IsNullOrEmpty(filter)) { returnFilter += $" AND ({filter})"; } return(returnFilter); }
/// <summary>This refreshes data being displayed on the graphs, based on the value of the startYear and showYear values </summary> /// <param name="table"></param> /// <param name="startDate"></param> /// <param name="endDate"></param> private void DisplayGraphRadiation(DataTable table, DateTime startDate, DateTime endDate, Boolean updateYears) { try { if (table != null && table.Rows.Count > 0) { DateTime[] dailyDates = DataTableUtilities.GetColumnAsDates(table, "Date", startDate, endDate); double[] dailyRain = DataTableUtilities.GetColumnAsDoubles(table, "rain", startDate, endDate); double[] dailyRadn = DataTableUtilities.GetColumnAsDoubles(table, "radn", startDate, endDate); double[] dailyMaxRadn = DataTableUtilities.GetColumnAsDoubles(table, "Qmax", startDate, endDate); if (dailyRadn.Length != 0) { this.PopulateRadiationGraph("Radiation", dailyDates, dailyRain, dailyRadn, dailyMaxRadn); } } } catch (Exception e) { throw new Exception("Unable to display Detailed Graphs due to insufficient data: " + e.Message.ToString()); } }
public void TestArrayRangeWithStartAndEndSpecification() { var mod = new MockModel() { Z = new double[] { 1, 2, 3 } }; simulation.Children.Add(mod); simulation.Children.Remove(storage); var datastore = new DataStore(); simulation.Children.Add(datastore); Utilities.InitialiseModel(simulation); report.VariableNames = new string[] { "[MockModel].Z[2:3]" }; List <Exception> errors = runner.Run(); Assert.NotNull(errors); Assert.AreEqual(0, errors.Count); datastore.Writer.Stop(); datastore.Reader.Refresh(); var data = datastore.Reader.GetData("Report"); var columnNames = DataTableUtilities.GetColumnNames(data); Assert.IsFalse(columnNames.Contains("MockModel.Z(0)")); Assert.IsFalse(columnNames.Contains("MockModel.Z(1)")); Assert.IsTrue(columnNames.Contains("MockModel.Z(2)")); Assert.IsTrue(columnNames.Contains("MockModel.Z(3)")); Assert.IsFalse(columnNames.Contains("MockModel.Z(4)")); Assert.AreEqual(DataTableUtilities.GetColumnAsDoubles(data, "MockModel.Z(2)"), new double[] { 2, 2, 2, 2, 2, 2, 2, 2, 2, 2 }); Assert.AreEqual(DataTableUtilities.GetColumnAsDoubles(data, "MockModel.Z(3)"), new double[] { 3, 3, 3, 3, 3, 3, 3, 3, 3, 3 }); }
/// <summary> /// Overlay data from table1 on top of table2 using the date in each row. Date /// dates in both tables have to exactly match before the data is overlaid. /// </summary> /// <param name="fromData">First data table</param> /// <param name="toData">The data table that will change</param> /// <param name="lowercaseCode">Lowercase the code?</param> public static void OverlayData(DataTable fromData, DataTable toData, bool lowercaseCode = false) { if (fromData != null && toData.Rows.Count > 0) { // This algorithm assumes that toData does not have missing days. DateTime firstDate = DataTableUtilities.GetDateFromRow(toData.Rows[0]); DateTime lastDate = DataTableUtilities.GetDateFromRow(toData.Rows[toData.Rows.Count - 1]); // Filter fromData so that it is in the same range as table2. DataView table1View = new DataView(fromData); table1View.RowFilter = string.Format("Date >= #{0:yyyy-MM-dd}# and Date <= #{1:yyyy-MM-dd}#", firstDate, lastDate); foreach (DataRowView table1Row in table1View) { DateTime table1Date = DataTableUtilities.GetDateFromRow(table1Row.Row); int table2RowIndex = (table1Date - firstDate).Days; if (table2RowIndex >= 0 && table2RowIndex < toData.Rows.Count) { DataRow table2Row = toData.Rows[table2RowIndex]; if (DataTableUtilities.GetDateFromRow(table2Row) == table1Date) { // Found the matching row OverlayRowData(table1Row.Row, table2Row, lowercaseCode); } else { throw new Exception("Non consecutive dates found in SILO data"); } } else { // Table 1 data is outside the range of table 2. } } } }
/// <summary>Gets a column of data from a view.</summary> /// <param name="data">The table</param> /// <param name="fieldName">Name of the field.</param> /// <returns>The column of data.</returns> private IEnumerable GetDataFromView(DataView data, string fieldName) { if (fieldName != null && data != null && data.Table.Columns.Contains(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 if (data.Table.Columns[fieldName].DataType == typeof(int)) { return(DataTableUtilities.GetColumnAsIntegers(data, fieldName)); } else { return(DataTableUtilities.GetColumnAsDoubles(data, fieldName)); } } 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) { 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> /// Add a column to the specified table based on values in the 'value' /// </summary> /// <param name="heading">The new column heading</param> /// <param name="dataTypeName">The data type of the value</param> /// <param name="displayFormat">The display format to use when writing the column</param> /// <param name="showTotal">A value indicating whether a total should be added.</param> /// <param name="value">The values containing the array</param> /// <param name="table">The table where a column should be added to</param> private static void AddArrayToTable(string heading, string dataTypeName, string displayFormat, bool showTotal, object value, DataTable table) { if (displayFormat == null) { displayFormat = "N3"; } string[] stringValues = value.ToString().Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries); if (dataTypeName == "Double[]") { List <double> values = new List <double>(); values.AddRange(MathUtilities.StringsToDoubles(stringValues)); if (showTotal) { values.Add(MathUtilities.Sum(values)); } stringValues = MathUtilities.DoublesToStrings(values, displayFormat); } else if (dataTypeName == "Int32[]") { List <double> values = new List <double>(); values.AddRange(MathUtilities.StringsToDoubles(stringValues)); if (showTotal) { values.Add(MathUtilities.Sum(values)); } stringValues = MathUtilities.DoublesToStrings(values, "N0"); } else if (dataTypeName != "String[]") { // throw new ApsimXException(null, "Invalid property type: " + dataTypeName); } DataTableUtilities.AddColumn(table, heading, stringValues); }
public static void TestReportingOnModelEvents() { string json = ReflectionUtilities.GetResourceAsString("UnitTests.Report.ReportOnEvents.apsimx"); Simulations file = FileFormat.ReadFromString <Simulations>(json, e => throw e, false); // This simulation needs a weather node, but using a legit // met component will just slow down the test. IModel sim = file.FindInScope <Simulation>(); Model weather = new MockWeather(); sim.Children.Add(weather); weather.Parent = sim; // Run the file. var Runner = new Runner(file); Runner.Run(); // Check that the report reported on the correct dates. var storage = file.FindInScope <IDataStore>(); List <string> fieldNames = new List <string>() { "doy" }; DataTable data = storage.Reader.GetData("ReportOnFertilisation", fieldNames: fieldNames); double[] values = DataTableUtilities.GetColumnAsDoubles(data, "doy"); double[] expected = new double[] { 1, 32, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335, 364 }; Assert.AreEqual(expected, values); data = storage.Reader.GetData("ReportOnIrrigation", fieldNames: fieldNames); values = DataTableUtilities.GetColumnAsDoubles(data, "doy"); // There is one less irrigation event, as the manager script doesn't irrigate. expected = new double[] { 1, 32, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335 }; Assert.AreEqual(expected, values); }
private void DoReadCommand(ReadCommand command, IConnectionManager connection) { List <DataTable> tables = new List <DataTable>(); foreach (string podName in workers) { V1Pod pod = GetWorkerPod(podName); if (string.IsNullOrEmpty(pod.Status.PodIP)) { throw new NotImplementedException("Pod IP not set."); } // Create a new socket connection to the pod. string ip = pod.Status.PodIP; WriteToLog($"Attempting connection to pod {podName} on {ip}:{portNo}"); using (NetworkSocketClient conn = new NetworkSocketClient(relayOptions.Verbose, ip, portNo, Protocol.Managed)) { WriteToLog($"Connection to {podName} established. Sending command..."); // Relay the command to the pod. try { tables.Add(conn.ReadOutput(command)); } catch (Exception err) { WriteToLog($"Unable to read output from pod {podName}:"); WriteToLog(err.ToString()); } WriteToLog($"Closing connection to {podName}..."); } } WriteToLog($"Merging {tables.Count} DataTables (from {workers.Count()} pods)..."); command.Result = DataTableUtilities.Merge(tables); connection.OnCommandFinished(command); }
public void RunAPSIMGetOutputs() { APSIMSpecification simulation = GetDefaultSimulationSpec(); List <APSIMSpecification> simulations = new List <APSIMSpecification>(); simulations.Add(simulation); RuntimeEnvironment environment = new RuntimeEnvironment { APSIMRevision = "Apsim7.8-R4013" }; RunYPJob job = new RunYPJob(simulations, environment); IJobRunner runner = new JobRunnerAsync(); runner.Run(job, wait: true); // Make sure we don't have an error. Assert.AreEqual(job.Errors.Count, 0); // Make sure we have a daily output table. Assert.AreEqual(job.Outputs.Tables.Count, 3); Assert.AreEqual(job.Outputs.Tables[0].TableName, "Summary"); Assert.AreEqual(job.Outputs.Tables[1].TableName, "YieldProphetDaily"); Assert.AreEqual(job.Outputs.Tables[1].Rows.Count, 92); double[] biomass = DataTableUtilities.GetColumnAsDoubles(job.Outputs.Tables[1], "biomass"); Assert.IsTrue(MathUtilities.Max(biomass) > 20.0); // make sure something is growing. // Make sure we have a depth table. Assert.AreEqual(job.Outputs.Tables[2].TableName, "YieldProphetDepth"); Assert.AreEqual(job.Outputs.Tables[2].Rows.Count, 8); double[] sw = DataTableUtilities.GetColumnAsDoubles(job.Outputs.Tables[2], "sw"); Assert.IsTrue(MathUtilities.Max(sw) > 0.0); // make sure there are sw values }
/// <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>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>This refreshes data being displayed on the graphs, based on the value of the startYear and showYear values </summary> /// <param name="table">The data set</param> /// <param name="startDate">The start date</param> /// <param name="endDate">The end date</param> /// <param name="updateYears">Update the years</param> private void DisplayGraphMonthlyRain(DataTable table, DateTime startDate, DateTime endDate, bool updateYears) { try { if (table != null && table.Rows.Count > 0) { double[] monthlyRainfall = DataTableUtilities.AverageMonthlyTotals(table, "rain", startDate, endDate); if (monthlyRainfall.Length != 0) { double[] avgMonthlyRainfall = DataTableUtilities.AverageMonthlyTotals(table, "rain", this.dataFirstDate, this.dataLastDate); this.PopulateMonthlyRainfallGraph( "Monthly Rainfall", this.monthsToDisplay, monthlyRainfall, avgMonthlyRainfall); } } } catch (Exception e) { throw new Exception("Unable to display Detailed Graphs due to insufficient data: " + e.Message.ToString()); } }
/// <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(); 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> /// Merge a collection of tables and ensure that the resultant table /// contains all rows from all of the tables. /// </summary> /// <param name="tables">Tables to be merged.</param> private void TestMerge(params DataTable[] tables) { // Merge the tables. DataTable merged = DataTableUtilities.Merge(tables); // Ensure that merge worked correctly. string expectedName = tables.FirstOrDefault()?.TableName ?? ""; Assert.AreEqual(expectedName, merged.TableName); int i = 0; foreach (DataTable table in tables) { foreach (DataRow row in table.Rows) { for (int j = 0; j < table.Columns.Count; j++) { Assert.AreEqual(row[j], merged.Rows[i][j]); } i++; } } }
/// <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>Format a summary string about the weather file</summary> private void WriteSummary(DataTable table) { StringBuilder summary = new StringBuilder(); summary.AppendLine("File name : " + this.weatherData.FileName); if (this.weatherData.ExcelWorkSheetName.Length > 0) { summary.AppendLine("Sheet Name: " + this.weatherData.ExcelWorkSheetName.ToString()); } summary.AppendLine("Latitude : " + this.weatherData.Latitude.ToString()); summary.AppendLine("TAV : " + String.Format("{0, 2:f2}", this.weatherData.Tav)); summary.AppendLine("AMP : " + String.Format("{0, 2:f2}", this.weatherData.Amp)); summary.AppendLine("Start : " + this.weatherData.StartDate.ToShortDateString()); summary.AppendLine("End : " + this.weatherData.EndDate.ToShortDateString()); summary.AppendLine(""); if (table != null && table.Rows.Count > 0) { dataFirstDate = DataTableUtilities.GetDateFromRow(table.Rows[0]); dataLastDate = DataTableUtilities.GetDateFromRow(table.Rows[table.Rows.Count - 1]); TimeSpan diff = dataLastDate - dataFirstDate; //modLMC - 16/03/2016 - don't change dates if data is within the same year if (diff.Days > 365) { if (dataFirstDate.DayOfYear != 1) { dataFirstDate = new DateTime(dataFirstDate.Year + 1, 1, 1); } } //modLMC - 16/03/2016 - don't change dates if data is within the same year if (dataFirstDate.Year != dataLastDate.Year) { if (dataLastDate.Day != 31 || dataLastDate.Month != 12) { dataLastDate = new DateTime(dataLastDate.Year - 1, 12, 31); } } double[] yearlyRainfall = MathUtilities.YearlyTotals(table, "Rain", dataFirstDate, dataLastDate); double[] monthlyRainfall = MathUtilities.AverageMonthlyTotals(table, "rain", dataFirstDate, dataLastDate); double[] monthlyMaxT = MathUtilities.AverageDailyTotalsForEachMonth(table, "maxt", dataFirstDate, dataLastDate); double[] monthlyMinT = MathUtilities.AverageDailyTotalsForEachMonth(table, "mint", dataFirstDate, dataLastDate); //what do we do if the date range is less than 1 year. //modlmc - 15/03/2016 - modified to pass in the "Month" values, and they may/may not contain a full year. if (monthlyRainfall.Length <= 12) { monthsToDisplay = DataTableUtilities.GetDistinctMonthsasStrings(table, dataFirstDate, dataLastDate); } // long term average rainfall if (yearlyRainfall.Length != 0) { double totalYearlyRainfall = MathUtilities.Sum(yearlyRainfall); int numYears = dataLastDate.Year - dataFirstDate.Year + 1; double meanYearlyRainfall = totalYearlyRainfall / numYears; double stddev = MathUtilities.StandardDeviation(yearlyRainfall); summary.AppendLine(String.Format("For years : {0} - {1}", dataFirstDate.Year, dataLastDate.Year)); summary.AppendLine("Long term average yearly rainfall : " + String.Format("{0,3:f2}mm", meanYearlyRainfall)); summary.AppendLine("Yearly rainfall std deviation : " + String.Format("{0,3:f2}mm", stddev)); string title = String.Format("Long term average data for years : {0} - {1}", dataFirstDate.Year, dataLastDate.Year); //modlmc - 15/03/2016 - modified to pass in the "Month" values, and they may/may not contain a full year. this.PopulateSummaryGraph(title, monthsToDisplay, monthlyRainfall, monthlyMaxT, monthlyMinT); } this.weatherDataView.Summarylabel = summary.ToString(); } }
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()); } } }
public List <string> ColumnNames(string tableName) { return(DataTableUtilities.GetColumnNames(data).ToList()); }
/// <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)); }