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