public void TestGetPODetailsID()
{
foreach (DbConnection connection in emptyConnections)
{
PredictedObservedDetails details = new PredictedObservedDetails();
Assert.AreEqual(0, DBFunctions.GetAcceptedPredictedObservedDetailsId(connection, 0, null, details));
Assert.AreEqual(0, DBFunctions.GetAcceptedPredictedObservedDetailsId(connection, 0, "wheat.apsimx", details));
Assert.AreEqual(0, DBFunctions.GetAcceptedPredictedObservedDetailsId(connection, 1, null, details));
Assert.AreEqual(0, DBFunctions.GetAcceptedPredictedObservedDetailsId(connection, 1, "wheat.apsimx", details));
details.DatabaseTableName = "PredictedObserved";
Assert.AreEqual(0, DBFunctions.GetAcceptedPredictedObservedDetailsId(connection, 0, null, details));
Assert.AreEqual(0, DBFunctions.GetAcceptedPredictedObservedDetailsId(connection, 0, "wheat.apsimx", details));
Assert.AreEqual(0, DBFunctions.GetAcceptedPredictedObservedDetailsId(connection, 1, null, details));
Assert.AreEqual(0, DBFunctions.GetAcceptedPredictedObservedDetailsId(connection, 1, "wheat.apsimx", details));
}
foreach (DbConnection connection in populousConnections)
{
PredictedObservedDetails details = new PredictedObservedDetails();
Assert.AreEqual(0, DBFunctions.GetAcceptedPredictedObservedDetailsId(connection, 0, null, details));
Assert.AreEqual(0, DBFunctions.GetAcceptedPredictedObservedDetailsId(connection, 0, "wheat.apsimx", details));
Assert.AreEqual(0, DBFunctions.GetAcceptedPredictedObservedDetailsId(connection, 1, null, details));
Assert.AreEqual(0, DBFunctions.GetAcceptedPredictedObservedDetailsId(connection, 1, "wheat.apsimx", details));
details.DatabaseTableName = "PredictedObserved";
Assert.AreEqual(0, DBFunctions.GetAcceptedPredictedObservedDetailsId(connection, 0, null, details));
Assert.AreEqual(0, DBFunctions.GetAcceptedPredictedObservedDetailsId(connection, 0, "wheat.apsimx", details));
Assert.AreEqual(0, DBFunctions.GetAcceptedPredictedObservedDetailsId(connection, 1, null, details));
Assert.AreEqual(1, DBFunctions.GetAcceptedPredictedObservedDetailsId(connection, 1, "wheat.apsimx", details));
}
}
/// <summary>
/// Searches the specified file and returns all instances of PredictedObserved data.
/// </summary>
/// <param name="fileName">Path to the .apsimx file to be searched.</param>
private static List <PredictedObservedDetails> GetPredictedObservedDetails(string fullFileName)
{
List <Exception> errors;
// note - if we get a badformat exception thrown here, it's because .net is trying to
// load a 64-bit version of sqlite3.dll for some reason. To fix this, we need to
// copy the 32-bit version from ApsimX/DeploymentSupport/Windows/Bin/sqlite3.dll to
// APSIM.PerformanceTests.Collector/Bin/Debug/ (or release if building in release mode).
Simulations sims = FileFormat.ReadFromFile <Simulations>(fullFileName, out errors);
if (errors != null && errors.Count > 0)
{
// Write all errors except for the last to a log file, and throw the last error
// to ensure that we don't proceed further.
for (int i = 0; i < errors.Count; i++)
{
if (i == errors.Count - 1)
{
throw errors[i];
}
WriteToLogFile(string.Format(" ERROR opening file {0}: {1}", fullFileName, errors[i].ToString()));
}
}
List <PredictedObservedDetails> predictedObservedDetailList = new List <PredictedObservedDetails>();
foreach (PredictedObserved poModel in Apsim.ChildrenRecursively(sims, typeof(PredictedObserved)))
{
PredictedObservedDetails instance = new PredictedObservedDetails()
{
DatabaseTableName = poModel.Name,
PredictedTableName = poModel.PredictedTableName,
ObservedTableName = poModel.ObservedTableName,
FieldNameUsedForMatch = poModel.FieldNameUsedForMatch,
FieldName2UsedForMatch = poModel.FieldName2UsedForMatch ?? string.Empty,
FieldName3UsedForMatch = poModel.FieldName3UsedForMatch ?? string.Empty,
};
instance.Data = GetPredictedObservedDataTable(poModel.Name, Path.ChangeExtension(fullFileName, ".db"));
// Only add this instance if there is data.
if ((instance.Data != null) && (instance.Data.Rows.Count > 0))
{
predictedObservedDetailList.Add(instance);
}
else
{
WriteToLogFile(string.Format(" No PredictedObserved data was found for table {0} of file {1}", poModel.Name, fullFileName));
}
}
return(predictedObservedDetailList);
}
private ApsimFile GetSimpleApsimFile()
{
DataTable simsTable = TableFactory.CreateEmptyApsimSimulationsTable();
simsTable.Rows.Add(1, "sim1");
simsTable.Rows.Add(2, "sim2");
DataTable poData = new DataTable("PredictedObserved");
poData.Columns.Add("SimulationID", typeof(int));
poData.Columns.Add("Predicted.GrainWt", typeof(double));
poData.Columns.Add("Observed.GrainWt", typeof(double));
poData.Columns.Add("xval", typeof(double));
poData.Rows.Add(1, 0.9, 1.1, 0.1);
poData.Rows.Add(2, 0.5, 1.0, 0.1);
PredictedObservedDetails poDetails = new PredictedObservedDetails()
{
DatabaseTableName = "PredictedObserved",
PredictedTableName = "Report",
ObservedTableName = "HarvestReport",
FieldNameUsedForMatch = "xval",
FieldName2UsedForMatch = string.Empty,
FieldName3UsedForMatch = string.Empty,
Data = poData,
};
return(new ApsimFile()
{
ID = 1,
AcceptedPullRequestId = -1,
FileName = "wheat.apsimx",
FullFileName = "~/wheat.apsimx",
IsMerged = true,
PullRequestId = 1,
RunDate = new DateTime(2020, 1, 1),
StatsAccepted = true,
SubmitDetails = "submitdetails",
Simulations = simsTable,
PredictedObserved = new List <PredictedObservedDetails>()
{
poDetails
},
});
}
public void TestGetApsimFilesRelatedPredictedObservedDetailsData()
{
foreach (DbConnection connection in emptyConnections)
{
List <ApsimFile> apsimFiles = DBFunctions.GetApsimFilesRelatedPredictedObservedData(connection, 1);
Assert.AreEqual(0, apsimFiles.Count);
}
foreach (DbConnection connection in populousConnections)
{
Assert.AreEqual(0, DBFunctions.GetApsimFilesRelatedPredictedObservedData(connection, 0).Count);
List <ApsimFile> apsimFiles = DBFunctions.GetApsimFilesRelatedPredictedObservedData(connection, 1);
Assert.AreEqual(1, apsimFiles.Count);
ApsimFile file = apsimFiles[0];
Assert.AreEqual(-1, file.AcceptedPullRequestId);
Assert.AreEqual("wheat.apsimx", file.FileName);
Assert.AreEqual("~/wheat.apsimx", file.FullFileName);
Assert.AreEqual(1, file.ID);
Assert.AreEqual(true, file.IsMerged);
Assert.AreEqual(1, file.PullRequestId);
Assert.AreEqual(new DateTime(2020, 1, 1), file.RunDate);
Assert.AreEqual(null, file.Simulations); // todo: should we implement this?
Assert.AreEqual(false, file.StatsAccepted);
Assert.AreEqual("submitdetails", file.SubmitDetails);
Assert.AreEqual(1, file.PredictedObserved.Count());
PredictedObservedDetails details = file.PredictedObserved.ElementAt(0);
Assert.AreEqual(0, details.AcceptedPredictedObservedDetailsId);
Assert.AreEqual(null, details.ApsimFile); // Not brave enough to change this yet
Assert.AreEqual(1, details.ApsimID);
Assert.AreEqual(null, details.Data); // Not brave enough to change this yet
Assert.AreEqual("PredictedObserved", details.DatabaseTableName);
Assert.AreEqual("xval", details.FieldNameUsedForMatch);
Assert.AreEqual(null, details.FieldName2UsedForMatch);
Assert.AreEqual(null, details.FieldName3UsedForMatch);
Assert.AreEqual(1, details.HasTests);
Assert.AreEqual(1, details.ID);
Assert.AreEqual("Observations", details.ObservedTableName);
Assert.AreEqual(0, details.PassedTests);
Assert.AreEqual("HarvestReport", details.PredictedTableName);
}
}
/// <summary>
/// Retrieves the ApsimFile details and related child Predicted Observed Details for a specific Pull Request
/// </summary>
/// <param name="connectStr"></param>
/// <param name="pullRequestId"></param>
/// <returns></returns>
private List <ApsimFile> GetApsimFilesRelatedPredictedObservedData(SqlConnection sqlCon, int pullRequestId)
{
string strSQL;
List <ApsimFile> apsimFilesList = new List <ApsimFile>();
try
{
strSQL = "SELECT * FROM ApsimFiles WHERE PullRequestId = @PullRequestId ORDER BY RunDate DESC";
using (SqlCommand commandER = new SqlCommand(strSQL, sqlCon))
{
commandER.CommandType = CommandType.Text;
commandER.Parameters.AddWithValue("@PullRequestId", pullRequestId);
//SqlDataReader reader = commandER.ExecuteReader();
//while (reader.Read())
//{
// ApsimFile apsim = new ApsimFile
// {
// ID = reader.GetInt32(0),
// PullRequestId = reader.GetInt32(1),
// FileName = reader.GetString(2),
// FullFileName = reader.GetString(3),
// RunDate = reader.GetDateTime(4),
// StatsAccepted = reader.GetBoolean(5),
// IsMerged = reader.GetBoolean(6),
// SubmitDetails = reader.GetString(7)
// };
// if (reader.IsDBNull(8))
// {
// apsim.AcceptedPullRequestId = 0;
// }
// else
// {
// apsim.AcceptedPullRequestId = reader.GetInt32(8);
// }
// apsimFilesList.Add(apsim);
//}
//reader.Close();
string response = Comms.SendQuery(commandER, "reader");
var jsonObject = JsonConvert.DeserializeObject(response);
DataTable dt = JsonConvert.DeserializeObject <DataTable>(jsonObject.ToString());
foreach (DataRow row in dt.Rows)
{
ApsimFile apsim = new ApsimFile
{
ID = Convert.ToInt32(row[0].ToString()),
PullRequestId = Convert.ToInt32(row[1].ToString()),
FileName = row[2].ToString(),
FullFileName = row[3].ToString(),
RunDate = Convert.ToDateTime(row[4].ToString()),
StatsAccepted = Convert.ToBoolean(row[5].ToString()),
IsMerged = Convert.ToBoolean(row[6].ToString()),
SubmitDetails = row[7].ToString(),
AcceptedPullRequestId = Convert.ToInt32(row[8].ToString())
};
apsimFilesList.Add(apsim);
}
}
foreach (ApsimFile currentApsimFile in apsimFilesList)
{
List <PredictedObservedDetails> currentPredictedObservedDetails = new List <PredictedObservedDetails>();
//retrieve the predicted observed details for this apsim file
strSQL = "SELECT * FROM PredictedObservedDetails WHERE ApsimFilesId = @ApsimFilesId ORDER BY ID";
using (SqlCommand commandER = new SqlCommand(strSQL, sqlCon))
{
commandER.CommandType = CommandType.Text;
commandER.Parameters.AddWithValue("@ApsimFilesId", currentApsimFile.ID);
//SqlDataReader reader = commandER.ExecuteReader();
//while (reader.Read())
//{
// PredictedObservedDetails predictedObserved = new PredictedObservedDetails
// {
// ID = reader.GetInt32(0),
// ApsimID = reader.GetInt32(1),
// DatabaseTableName = reader.GetString(2),
// PredictedTableName = reader.GetString(3),
// ObservedTableName = reader.GetString(4),
// FieldNameUsedForMatch = reader.GetString(5),
// FieldName2UsedForMatch = reader.GetString(6),
// FieldName3UsedForMatch = reader.GetString(7),
// PassedTests = reader.GetDouble(8),
// HasTests = reader.GetInt32(9)
// };
// if (reader.IsDBNull(10))
// {
// predictedObserved.AcceptedPredictedObservedDetailsId = 0;
// }
// else
// {
// predictedObserved.AcceptedPredictedObservedDetailsId = reader.GetInt32(10);
// }
// currentPredictedObservedDetails.Add(predictedObserved);
//}
//reader.Close();
string response = Comms.SendQuery(commandER, "reader");
var jsonObject = JsonConvert.DeserializeObject(response);
DataTable dt = JsonConvert.DeserializeObject <DataTable>(jsonObject.ToString());
foreach (DataRow row in dt.Rows)
{
PredictedObservedDetails predictedObserved = new PredictedObservedDetails
{
ID = (int)row[0],
ApsimID = (int)row[1],
DatabaseTableName = (string)row[2],
PredictedTableName = (string)row[3],
ObservedTableName = (string)row[4],
FieldNameUsedForMatch = (string)row[5],
FieldName2UsedForMatch = (string)row[6],
FieldName3UsedForMatch = (string)row[7],
PassedTests = (double)row[8],
HasTests = (int)row[9]
};
if (row[10] == DBNull.Value)
{
predictedObserved.AcceptedPredictedObservedDetailsId = 0;
}
else
{
predictedObserved.AcceptedPredictedObservedDetailsId = (int)row[10];
}
currentPredictedObservedDetails.Add(predictedObserved);
}
}
currentApsimFile.PredictedObserved = currentPredictedObservedDetails;
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(string.Format("ERROR: Unable to retrieve Apsim Files and PredictedObservedDetails for Pull Request Id {0}: {1}", pullRequestId.ToString(), ex.Message.ToString()));
}
return(apsimFilesList);
}
/// <summary>
/// Returns the PredictedObservedTests data for 'Accepted' data set, based on matching 'Current' Details
/// </summary>
/// <param name="conStr"></param>
/// <param name="currentApsimID"></param>
/// <param name="currentApsim"></param>
/// <param name="poDetail"></param>
/// <param name="predictedObservedId"></param>
/// <param name="acceptedPredictedObservedDetailsID"></param>
/// <returns></returns>
private static DataTable RetrieveAcceptedStatsData(DbConnection connection, int currentApsimID, ApsimFile currentApsim, PredictedObservedDetails poDetail, int predictedObservedId, ref int acceptedPredictedObservedDetailsID)
{
DataTable acceptedStats = new DataTable();
ApsimFile acceptedApsim = new ApsimFile();
try
{
string strSQL = "SELECT * FROM ApsimFiles WHERE StatsAccepted = 1 AND PullRequestId != @PullRequestId ORDER BY RunDate DESC";
strSQL = Utilities.Limit(connection, strSQL, 1);
using (DbCommand command = connection.CreateCommand(strSQL))
{
command.CommandType = CommandType.Text;
command.AddParamWithValue("@PullRequestId", currentApsim.PullRequestId);
using (DbDataReader sdReader = command.ExecuteReader())
{
while (sdReader.Read())
{
acceptedApsim.ID = sdReader.GetInt32(0);
acceptedApsim.PullRequestId = sdReader.GetInt32(1);
acceptedApsim.FileName = sdReader.GetString(2);
acceptedApsim.FullFileName = sdReader.GetString(3);
acceptedApsim.RunDate = sdReader.GetDateTime(4);
acceptedApsim.StatsAccepted = sdReader.GetBoolean(5);
acceptedApsim.IsMerged = sdReader.GetBoolean(6);
acceptedApsim.SubmitDetails = sdReader.GetString(7);
acceptedApsim.AcceptedPullRequestId = sdReader.IsDBNull(8) ? 0 : sdReader.GetInt32(8);
}
}
}
if (acceptedApsim.PullRequestId > 0)
{
DBFunctions.UpdateApsimFileAcceptedDetails(connection, currentApsim.PullRequestId, acceptedApsim.PullRequestId, acceptedApsim.RunDate);
////get the PredictedObservedDetail.ID for the records that match our current record 'matching' criteria
acceptedPredictedObservedDetailsID = DBFunctions.GetAcceptedPredictedObservedDetailsId(connection, acceptedApsim.PullRequestId, currentApsim.FileName, poDetail);
////Now retreieve the matching tests data for our predicted observed details
acceptedStats = DBFunctions.GetPredictedObservedTestsData(connection, acceptedPredictedObservedDetailsID);
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(string.Format(" ERROR: Unable to RetrieveAcceptedStatsData for ApsimFile {0}: Pull Request Id {1}: {2}.", currentApsim.FileName, currentApsim.PullRequestId, ex.Message.ToString()));
}
return(acceptedStats);
}
public void TestPostSimpleApsimFile()
{
foreach (DbConnection connection in emptyConnections)
{
// Create a simple apsim file.
ApsimFile file = GetSimpleApsimFile();
PredictedObservedDetails poDetails = file.PredictedObserved.ElementAt(0);
// Insert it into the database.
ApsimFilesController.InsertApsimFile(connection, file, out string err, out int id);
// Verify results.
DataTable result = new DataTable();
// Check ApsimFiles table.
using (DbCommand command = connection.CreateCommand("SELECT * FROM ApsimFiles"))
using (DbDataReader reader = command.ExecuteReader())
result.Load(reader);
Assert.NotNull(result);
Assert.AreEqual(1, result.Rows.Count);
Assert.AreEqual(10, result.Columns.Count);
DataRow row = result.Rows[0];
Assert.AreEqual(file.ID, row["ID"]);
Assert.AreEqual(1, row["PullRequestID"]);
Assert.AreEqual(file.FileName, row["FileName"]);
Assert.AreEqual(file.FullFileName, row["FullFileName"]);
if (connection is SQLiteConnection) // fixme - change sql server implementation to use varchar for dates????
{
Assert.AreEqual(file.RunDate.ToString("yyyy-MM-dd HH:mm:ss"), row["RunDate"]);
}
else
{
Assert.AreEqual(file.RunDate, row["RunDate"]);
}
Assert.AreEqual(file.IsMerged, row["IsMerged"]);
Assert.AreEqual(file.StatsAccepted, row["StatsAccepted"]);
Assert.AreEqual(file.SubmitDetails, row["SubmitDetails"]);
Assert.AreEqual(file.AcceptedPullRequestId, row["AcceptedPullRequestId"]);
//Assert.AreEqual("", row["AcceptedRunDate"]);//fixme
// Check Simulations table.
result = new DataTable();
using (DbCommand command = connection.CreateCommand("SELECT * FROM Simulations"))
using (DbDataReader reader = command.ExecuteReader())
result.Load(reader);
Assert.AreEqual(2, result.Rows.Count);
Assert.AreEqual(4, result.Columns.Count);
row = result.Rows[0];
Assert.AreEqual(1, row["ID"]);
Assert.AreEqual(1, row["ApsimFilesID"]);
Assert.AreEqual("sim1", row["Name"]);
Assert.AreEqual(1, row["OriginalSimulationID"]);
row = result.Rows[1];
Assert.AreEqual(2, row["ID"]);
Assert.AreEqual(1, row["ApsimFilesID"]);
Assert.AreEqual("sim2", row["Name"]);
Assert.AreEqual(2, row["OriginalSimulationID"]);
// Check PredictedObservedDetails.
result = new DataTable();
using (DbCommand command = connection.CreateCommand("SELECT * FROM PredictedObservedDetails"))
using (DbDataReader reader = command.ExecuteReader())
result.Load(reader);
Assert.AreEqual(1, result.Rows.Count);
Assert.AreEqual(11, result.Columns.Count);
row = result.Rows[0];
Assert.AreEqual(1, row["ID"]);
Assert.AreEqual(file.ID, row["ApsimFilesID"]);
Assert.AreEqual(poDetails.DatabaseTableName, row["TableName"]);
Assert.AreEqual(poDetails.PredictedTableName, row["PredictedTableName"]);
Assert.AreEqual(poDetails.ObservedTableName, row["ObservedTableName"]);
Assert.AreEqual(poDetails.FieldNameUsedForMatch, row["FieldNameUsedForMatch"]);
Assert.AreEqual(poDetails.FieldName2UsedForMatch, row["FieldName2UsedForMatch"]);
Assert.AreEqual(poDetails.FieldName3UsedForMatch, row["FieldName3UsedForMatch"]);
// todo: test the last 3 columns after implementing tests
Assert.AreEqual(0, row["PassedTests"]);
Assert.AreEqual(1, row["HasTests"]);
Assert.AreEqual(DBNull.Value, row["AcceptedPredictedObservedDetailsID"]);
// Check PredictedObservedValues.
result = new DataTable();
using (DbCommand command = connection.CreateCommand("SELECT * FROM PredictedObservedValues"))
using (DbDataReader reader = command.ExecuteReader())
result.Load(reader);
// #Rows = #Sims * #Variables * #PredictedObservedTables
// Note that this isn't a general rule, because not every simulation
// will necessarily generate predicted data for this p/o table.
int nSims = file.Simulations.Rows.Count;
int nVars = poDetails.Data.Columns.Cast <DataColumn>().Where(c => c.ColumnName.StartsWith("Predicted.")).Count();
int nTables = file.PredictedObserved.Count();
int nRows = nSims * nVars * nTables;
Assert.AreEqual(nRows, result.Rows.Count);
Assert.AreEqual(12, result.Columns.Count);
for (int i = 0; i < result.Rows.Count; i++)
{
row = result.Rows[i];
Assert.AreEqual(i + 1, row["ID"]);
Assert.AreEqual(1, row["PredictedObservedDetailsID"]);
// Note: this will not always be true in the more general case.
Assert.AreEqual(poDetails.Data.Rows[i]["SimulationID"], row["SimulationsID"]);
Assert.AreEqual(poDetails.FieldNameUsedForMatch, row["MatchName"]);
// Match value column will be of type string, to allow for matching on stage name.
Assert.AreEqual(poDetails.Data.Rows[i][poDetails.FieldNameUsedForMatch].ToString(), row["MatchValue"]);
Assert.AreEqual(poDetails.FieldName2UsedForMatch, row["MatchName2"]);
if (!string.IsNullOrEmpty(poDetails.FieldName2UsedForMatch))
{
Assert.AreEqual(poDetails.Data.Rows[i][poDetails.FieldName2UsedForMatch].ToString(), row["MatchValue2"]);
}
Assert.AreEqual(poDetails.FieldName3UsedForMatch, row["MatchName3"]);
if (!string.IsNullOrEmpty(poDetails.FieldName3UsedForMatch))
{
Assert.AreEqual(poDetails.Data.Rows[i][poDetails.FieldName3UsedForMatch].ToString(), row["MatchValue3"]);
}
Assert.AreEqual("GrainWt", row["ValueName"]);
Assert.AreEqual(poDetails.Data.Rows[i]["Predicted.GrainWt"], row["PredictedValue"]);
Assert.AreEqual(poDetails.Data.Rows[i]["Observed.GrainWt"], row["ObservedValue"]);
}
// Check PredictedObservedTests.
result = new DataTable();
using (DbCommand command = connection.CreateCommand("SELECT * FROM PredictedObservedTests"))
using (DbDataReader reader = command.ExecuteReader())
result.Load(reader);
string[] variables = new[]
{
"n",
"Slope",
"Intercept",
"SEslope",
"SEintercept",
"R2",
"RMSE",
"NSE",
"ME",
"MAE",
"RSR",
};
Assert.AreEqual(variables.Length, result.Rows.Count);
Assert.AreEqual(12, result.Columns.Count);
List <string> actualVariables = new List <string>();
for (int i = 0; i < result.Rows.Count; i++)
{
row = result.Rows[i];
Assert.AreEqual(i + 1, row["ID"]);
Assert.AreEqual(1, row["PredictedObservedDetailsID"]);
Assert.AreEqual("GrainWt", row["Variable"]);
actualVariables.Add(row["Test"].ToString());
Assert.AreEqual(DBNull.Value, row["Accepted"]);
}
Assert.AreEqual(variables, actualVariables);
Assert.AreEqual(2, GetCurrentValue(result, "n"), 1e-10);
Assert.AreEqual(4, GetCurrentValue(result, "Slope"), 1e-10);
Assert.AreEqual(-3.5, GetCurrentValue(result, "Intercept"), 1e-10);
Assert.AreEqual(0, GetCurrentValue(result, "SEslope"), 1e-10);
Assert.AreEqual(0, GetCurrentValue(result, "SEintercept"), 1e-10);
Assert.AreEqual(1, GetCurrentValue(result, "R2"), 1e-10);
Assert.AreEqual(0.380789, GetCurrentValue(result, "RMSE"), 1e-10);
Assert.AreEqual(-57, GetCurrentValue(result, "NSE"), 1e-10);
Assert.AreEqual(-0.35, GetCurrentValue(result, "ME"), 1e-10);
Assert.AreEqual(0.35, GetCurrentValue(result, "MAE"), 1e-10);
Assert.AreEqual(5.385165, GetCurrentValue(result, "RSR"), 1e-10);
}
}
/// <summary>
/// Gets the PredictedObservedDetail.ID for the records that match our current record 'matching' criteria
/// </summary>
/// <param name="connectStr"></param>
/// <param name="acceptedPullRequestID"></param>
/// <param name="currentApsimFileFileName"></param>
/// <param name="currentPODetails"></param>
/// <returns></returns>
public static int GetAcceptedPredictedObservedDetailsId(SqlConnection sqlCon, int acceptedPullRequestID, string currentApsimFileFileName, PredictedObservedDetails currentPODetails)
{
int acceptedPredictedObservedDetailsID = 0;
try
{
string strSQL = "SELECT p.ID "
+ " FROM PredictedObservedDetails p INNER JOIN ApsimFiles a ON p.ApsimFilesID = a.ID "
+ " WHERE a.PullRequestId = @pullRequestId "
+ " AND a.FileName = @filename "
+ " AND p.TableName = @tablename "
+ " AND p.PredictedTableName = @predictedTableName "
+ " AND p.ObservedTableName = @observedTableName "
+ " AND p.FieldNameUsedForMatch = @fieldNameUsedForMatch ";
if ((currentPODetails.FieldName2UsedForMatch != null) && (currentPODetails.FieldName2UsedForMatch.Length > 0))
{
strSQL = strSQL + " AND p.FieldName2UsedForMatch = @fieldName2UsedForMatch ";
}
if ((currentPODetails.FieldName3UsedForMatch != null) && (currentPODetails.FieldName3UsedForMatch.Length > 0))
{
strSQL = strSQL + " AND p.FieldName3UsedForMatch = @fieldName3UsedForMatch ";
}
using (SqlCommand commandES = new SqlCommand(strSQL, sqlCon))
{
commandES.CommandType = CommandType.Text;
commandES.Parameters.AddWithValue("@PullRequestId", acceptedPullRequestID);
commandES.Parameters.AddWithValue("@filename", currentApsimFileFileName);
commandES.Parameters.AddWithValue("@tablename", currentPODetails.DatabaseTableName);
commandES.Parameters.AddWithValue("@predictedTableName", currentPODetails.PredictedTableName);
commandES.Parameters.AddWithValue("@observedTableName", currentPODetails.ObservedTableName);
commandES.Parameters.AddWithValue("@fieldNameUsedForMatch", currentPODetails.FieldNameUsedForMatch);
if ((currentPODetails.FieldName2UsedForMatch != null) && (currentPODetails.FieldName2UsedForMatch.Length > 0))
{
commandES.Parameters.AddWithValue("@fieldName2UsedForMatch", currentPODetails.FieldName2UsedForMatch);
}
if ((currentPODetails.FieldName3UsedForMatch != null) && (currentPODetails.FieldName3UsedForMatch.Length > 0))
{
commandES.Parameters.AddWithValue("@fieldName3UsedForMatch", currentPODetails.FieldName3UsedForMatch);
}
//object obj = commandES.ExecuteScalar();
string response = Comms.SendQuery(commandES, "scalar");
object obj = JsonConvert.DeserializeObject(response);
if (obj != null)
{
acceptedPredictedObservedDetailsID = int.Parse(obj.ToString());
}
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(string.Format(" ERROR in GetAcceptedPredictedObservedDetailsId: Unable to retrieve 'Accepted' PredictedObservedDetailsID for Pull Request {0}: {1}", acceptedPullRequestID, ex.Message.ToString()));
}
return(acceptedPredictedObservedDetailsID);
}
/// <summary>
/// Gets the PredictedObservedDetail.ID for the records that match our current record 'matching' criteria
/// </summary>
/// <param name="connectStr"></param>
/// <param name="acceptedPullRequestID"></param>
/// <param name="currentApsimFileFileName"></param>
/// <param name="currentPODetails"></param>
/// <returns></returns>
public static int GetAcceptedPredictedObservedDetailsId(DbConnection connection, int acceptedPullRequestID, string currentApsimFileFileName, PredictedObservedDetails currentPODetails)
{
int acceptedPredictedObservedDetailsID = 0;
try
{
string strSQL = "SELECT p.ID "
+ " FROM PredictedObservedDetails p INNER JOIN ApsimFiles a ON p.ApsimFilesID = a.ID "
+ " WHERE a.PullRequestId = @pullRequestId "
+ " AND a.FileName = @filename "
+ " AND p.TableName = @tablename ";
//modLCM - 22/02/2018 - As per instructions from Dean, remove matching on extra columns in PO Details Table
//+ " AND p.PredictedTableName = @predictedTableName "
//+ " AND p.ObservedTableName = @observedTableName "
//+ " AND p.FieldNameUsedForMatch = @fieldNameUsedForMatch ";
//if ((currentPODetails.FieldName2UsedForMatch != null) && (currentPODetails.FieldName2UsedForMatch.Length > 0))
//{
// strSQL = strSQL + " AND p.FieldName2UsedForMatch = @fieldName2UsedForMatch ";
//}
//if ((currentPODetails.FieldName3UsedForMatch != null) && (currentPODetails.FieldName3UsedForMatch.Length > 0))
//{
// strSQL = strSQL + " AND p.FieldName3UsedForMatch = @fieldName3UsedForMatch ";
//}
using (DbCommand commandES = connection.CreateCommand(strSQL))
{
commandES.CommandType = CommandType.Text;
commandES.AddParamWithValue("@PullRequestId", acceptedPullRequestID);
commandES.AddParamWithValue("@filename", currentApsimFileFileName);
commandES.AddParamWithValue("@tablename", currentPODetails.DatabaseTableName);
//commandES.Parameters.AddWithValue("@predictedTableName", currentPODetails.PredictedTableName);
//commandES.Parameters.AddWithValue("@observedTableName", currentPODetails.ObservedTableName);
//commandES.Parameters.AddWithValue("@fieldNameUsedForMatch", currentPODetails.FieldNameUsedForMatch);
//if ((currentPODetails.FieldName2UsedForMatch != null) && (currentPODetails.FieldName2UsedForMatch.Length > 0))
//{
// commandES.Parameters.AddWithValue("@fieldName2UsedForMatch", currentPODetails.FieldName2UsedForMatch);
//}
//if ((currentPODetails.FieldName3UsedForMatch != null) && (currentPODetails.FieldName3UsedForMatch.Length > 0))
//{
// commandES.Parameters.AddWithValue("@fieldName3UsedForMatch", currentPODetails.FieldName3UsedForMatch);
//}
object obj = commandES.ExecuteScalar();
if (obj != null)
{
acceptedPredictedObservedDetailsID = int.Parse(obj.ToString());
}
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(string.Format(" ERROR in GetAcceptedPredictedObservedDetailsId: Unable to retrieve 'Accepted' PredictedObservedDetailsID for Pull Request {0}: {1}", acceptedPullRequestID, ex.Message.ToString()));
}
return(acceptedPredictedObservedDetailsID);
}
/// <summary>
/// Retrieves the ApsimFile details and related child Predicted Observed Details for a specific Pull Request
/// </summary>
/// <param name="connectStr"></param>
/// <param name="pullRequestId"></param>
/// <returns></returns>
public static List <ApsimFile> GetApsimFilesRelatedPredictedObservedData(DbConnection sqlCon, int pullRequestId)
{
List <ApsimFile> apsimFilesList = new List <ApsimFile>();
try
{
string sql = "SELECT * FROM ApsimFiles WHERE PullRequestId = @PullRequestId ORDER BY RunDate DESC";
using (DbCommand commandER = sqlCon.CreateCommand(sql))
{
commandER.CommandType = CommandType.Text;
commandER.AddParamWithValue("@PullRequestId", pullRequestId);
DbDataReader reader = commandER.ExecuteReader();
while (reader.Read())
{
ApsimFile apsim = new ApsimFile
{
ID = reader.GetInt32(0),
PullRequestId = reader.GetInt32(1),
FileName = reader.GetString(2),
FullFileName = reader.GetString(3),
RunDate = reader.GetDateTime(4),
StatsAccepted = reader.GetBoolean(5),
IsMerged = reader.GetBoolean(6),
SubmitDetails = reader.GetString(7)
};
if (reader.IsDBNull(8))
{
apsim.AcceptedPullRequestId = 0;
}
else
{
apsim.AcceptedPullRequestId = reader.GetInt32(8);
}
apsimFilesList.Add(apsim);
}
reader.Close();
}
foreach (ApsimFile currentApsimFile in apsimFilesList)
{
List <PredictedObservedDetails> currentPredictedObservedDetails = new List <PredictedObservedDetails>();
//retrieve the predicted observed details for this apsim file
sql = "SELECT * FROM PredictedObservedDetails WHERE ApsimFilesId = @ApsimFilesId ORDER BY ID";
using (DbCommand commandER = sqlCon.CreateCommand(sql))
{
commandER.CommandType = CommandType.Text;
commandER.AddParamWithValue("@ApsimFilesId", currentApsimFile.ID);
DbDataReader reader = commandER.ExecuteReader();
while (reader.Read())
{
PredictedObservedDetails predictedObserved = new PredictedObservedDetails()
{
ID = reader.GetInt32(0),
ApsimID = reader.GetInt32(1),
DatabaseTableName = reader.GetString(2),
PredictedTableName = reader.GetString(3),
ObservedTableName = reader.GetString(4),
FieldNameUsedForMatch = reader.GetString(5),
FieldName2UsedForMatch = reader.GetNullOrString(6),
FieldName3UsedForMatch = reader.GetNullOrString(7),
PassedTests = reader.GetDouble(8),
HasTests = reader.GetInt32(9),
};
if (reader.IsDBNull(10))
{
predictedObserved.AcceptedPredictedObservedDetailsId = 0;
}
else
{
predictedObserved.AcceptedPredictedObservedDetailsId = reader.GetInt32(10);
}
currentPredictedObservedDetails.Add(predictedObserved);
}
reader.Close();
}
currentApsimFile.PredictedObserved = currentPredictedObservedDetails;
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(string.Format("ERROR: Unable to retrieve Apsim Files and PredictedObservedDetails for Pull Request Id {0}: {1}", pullRequestId.ToString(), ex.Message.ToString()));
}
return(apsimFilesList);
}
/// <summary>
// Returns the PredictedObservedTests data for 'Accepted' data set, based on matching 'Current' Details
/// </summary>
/// <param name="conStr"></param>
/// <param name="currentApsimID"></param>
/// <param name="currentApsim"></param>
/// <param name="poDetail"></param>
/// <param name="predictedObservedId"></param>
/// <param name="acceptedPredictedObservedDetailsID"></param>
/// <returns></returns>
private static DataTable RetrieveAcceptedStatsData(SqlConnection sqlCon, int currentApsimID, ApsimFile currentApsim, PredictedObservedDetails poDetail, int predictedObservedId, ref int acceptedPredictedObservedDetailsID)
{
DataTable acceptedStats = new DataTable();
ApsimFile acceptedApsim = new ApsimFile();
try
{
string strSQL = "SELECT TOP 1 * FROM ApsimFiles WHERE StatsAccepted = 1 AND PullRequestId != @PullRequestId ORDER BY RunDate DESC";
using (SqlCommand commandER = new SqlCommand(strSQL, sqlCon))
{
commandER.CommandType = CommandType.Text;
commandER.Parameters.AddWithValue("@PullRequestId", currentApsim.PullRequestId);
//SqlDataReader sdReader = commandER.ExecuteReader();
//while (sdReader.Read())
//{
// acceptedApsim.ID = sdReader.GetInt32(0);
// acceptedApsim.PullRequestId = sdReader.GetInt32(1);
// acceptedApsim.FileName = sdReader.GetString(2);
// acceptedApsim.FullFileName = sdReader.GetString(3);
// acceptedApsim.RunDate = sdReader.GetDateTime(4);
// acceptedApsim.StatsAccepted = sdReader.GetBoolean(5);
// acceptedApsim.IsMerged = sdReader.GetBoolean(6);
// acceptedApsim.SubmitDetails = sdReader.GetString(7);
// if (sdReader.IsDBNull(8))
// {
// acceptedApsim.AcceptedPullRequestId = 0;
// }
// else
// {
// acceptedApsim.AcceptedPullRequestId = sdReader.GetInt32(8);
// }
//}
//sdReader.Close();
string response = Comms.SendQuery(commandER, "reader");
var jsonObject = JsonConvert.DeserializeObject(response);
DataTable dt = JsonConvert.DeserializeObject <DataTable>(jsonObject.ToString());
foreach (DataRow row in dt.Rows)
{
acceptedApsim.ID = Convert.ToInt32(row[0].ToString());
acceptedApsim.PullRequestId = Convert.ToInt32(row[1].ToString());
acceptedApsim.FileName = row[2].ToString();
acceptedApsim.FullFileName = row[3].ToString();
acceptedApsim.RunDate = Convert.ToDateTime(row[4].ToString());
acceptedApsim.StatsAccepted = Convert.ToBoolean(row[5].ToString());
acceptedApsim.IsMerged = Convert.ToBoolean(row[6].ToString());
acceptedApsim.SubmitDetails = row[7].ToString();
if (row[8] == DBNull.Value)
{
acceptedApsim.AcceptedPullRequestId = 0;
}
else
{
acceptedApsim.AcceptedPullRequestId = Convert.ToInt32(row[8].ToString());
}
}
}
if (acceptedApsim.PullRequestId > 0)
{
DBFunctions.UpdateApsimFileAcceptedDetails(sqlCon, currentApsim.PullRequestId, acceptedApsim.PullRequestId, acceptedApsim.RunDate);
////get the PredictedObservedDetail.ID for the records that match our current record 'matching' criteria
acceptedPredictedObservedDetailsID = DBFunctions.GetAcceptedPredictedObservedDetailsId(sqlCon, acceptedApsim.PullRequestId, currentApsim.FileName, poDetail);
////Now retreieve the matching tests data for our predicted observed details
acceptedStats = DBFunctions.GetPredictedObservedTestsData(sqlCon, acceptedPredictedObservedDetailsID);
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(string.Format(" ERROR: Unable to RetrieveAcceptedStatsData for ApsimFile {0}: Pull Request Id {1}: {2}.", currentApsim.FileName, currentApsim.PullRequestId, ex.Message.ToString()));
}
return(acceptedStats);
}
