/// <summary>
/// Updates 'Accepted' ApsimFile details (PullRequestId, RunDate) for ApsimFile using Current Pull RequestId
/// </summary>
/// <param name="connectStr"></param>
/// <param name="currentApsimFileID"></param>
/// <param name="acceptedPullRequestID"></param>
public static void UpdateApsimFileAcceptedDetails(DbConnection sqlCon, int currentPullRequestID, int acceptedPullRequestID, DateTime acceptedRunDate)
{
if ((currentPullRequestID > 0) && (acceptedPullRequestID > 0))
{
try
{
string strSQL = "UPDATE ApsimFiles "
+ " SET AcceptedPullRequestId = @AcceptedPullRequestId, "
+ " AcceptedRunDate = @AcceptedRunDate "
+ " WHERE PullRequestId = @PullRequestID ";
using (DbCommand commandENQ = sqlCon.CreateCommand(strSQL))
{
commandENQ.CommandType = CommandType.Text;
commandENQ.AddParamWithValue("@AcceptedPullRequestId", acceptedPullRequestID);
commandENQ.AddParamWithValue("@AcceptedRunDate", acceptedRunDate);
commandENQ.AddParamWithValue("@PullRequestID", currentPullRequestID);
commandENQ.ExecuteNonQuery();
}
//Utilities.WriteToLogFile(" Accepted ApsimFilesID added to ApsimFiles.");
}
catch (Exception ex)
{
Utilities.WriteToLogFile(String.Format(" ERROR in UpdateApsimFileAcceptedDetails: Unable to update Accepted Pull Request {0} Details for Pull Request ID {1}: {2}", acceptedPullRequestID, currentPullRequestID, ex.Message.ToString()));
}
}
}
/// <summary>
/// Updates the Accepted PredictedObservedDetails.ID for the current PredictedObservedDetails record
/// </summary>
/// <param name="connectStr"></param>
/// <param name="acceptedPredictedObservedDetailsID"></param>
/// <param name="currentPODetailsID"></param>
public static void UpdatePredictedObservedDetails(DbConnection sqlCon, int acceptedPredictedObservedDetailsID, int currentPODetailsID)
{
//Update the accepted reference for Predicted Observed Values, so that it can be
if (acceptedPredictedObservedDetailsID > 0 && currentPODetailsID > 0)
{
try
{
string sql = "UPDATE PredictedObservedDetails "
+ " SET AcceptedPredictedObservedDetailsID = @AcceptedPredictedObservedDetailsID "
+ " WHERE ID = @PredictedObservedDetailsID ";
using (DbCommand command = sqlCon.CreateCommand(sql))
{
command.CommandType = CommandType.Text;
command.AddParamWithValue("@AcceptedPredictedObservedDetailsID", acceptedPredictedObservedDetailsID);
command.AddParamWithValue("@PredictedObservedDetailsID", currentPODetailsID);
command.ExecuteNonQuery();
}
Utilities.WriteToLogFile(" Accepted PredictedObservedDetailsID added to PredictedObservedDetails.");
}
catch (Exception ex)
{
Utilities.WriteToLogFile(string.Format(" ERROR in UpdatePredictedObservedDetails: Unable to update 'Accepted' PredictedObservedDetailsID {0} ON PredictedObservedDetails ID {1}: {2} ", acceptedPredictedObservedDetailsID, currentPODetailsID, ex.Message.ToString()));
}
}
}
/// <summary>
/// Gets the ID of the record in the simulations table with a given ApsimFile ID and SimulationID.
/// </summary>
/// <param name="connection">Database connection.</param>
/// <param name="apsimFileID">ID of the apsim file.</param>
/// <param name="simulationID">ID of the simulation in the _Simulations table in the apsim .db file.</param>
private static int GetSimulationID(DbConnection connection, int apsimFileID, int simulationID)
{
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT ID FROM Simulations WHERE ApsimFilesID = @ApsimFileID AND OriginalSimulationID = @SimulationID";
command.AddParamWithValue("@ApsimFileID", apsimFileID);
command.AddParamWithValue("@SimulationID", simulationID);
return(Convert.ToInt32(command.ExecuteScalar()));
}
}
/// <summary>
/// Update the AcceptStats Log file
/// </summary>
/// <param name="acceptLog"></param>
public static void UpdateAsStatsAccepted(DbConnection connection, string StatsType, AcceptStatsLog acceptLog)
{
Utilities.WriteToLogFile("-----------------------------------");
//make sure this is 0 if we are updating as 'Accepted'
if (StatsType == "Accept")
{
acceptLog.StatsPullRequestId = 0;
//acceptLog.FileCount = 0;
}
//need to authenticate the process
int statsAccepted = Convert.ToInt32(acceptLog.LogStatus);
try
{
string strSQL = "INSERT INTO AcceptStatsLogs (PullRequestId, SubmitPerson, SubmitDate, FileCount, LogPerson, LogReason, LogStatus, LogAcceptDate, StatsPullRequestId) "
+ " Values ( @PullRequestId, @SubmitPerson, @SubmitDate, @FileCount, @LogPerson, @LogReason, @LogStatus, @LogAcceptDate, @StatsPullRequestId )";
using (DbCommand commandENQ = connection.CreateCommand(strSQL))
{
commandENQ.CommandType = CommandType.Text;
commandENQ.AddParamWithValue("@PullRequestId", acceptLog.PullRequestId);
commandENQ.AddParamWithValue("@SubmitPerson", acceptLog.SubmitPerson);
commandENQ.AddParamWithValue("@SubmitDate", acceptLog.SubmitDate);
commandENQ.AddParamWithValue("@FileCount", acceptLog.FileCount);
commandENQ.AddParamWithValue("@LogPerson", acceptLog.LogPerson);
commandENQ.AddParamWithValue("@LogReason", acceptLog.LogReason);
commandENQ.AddParamWithValue("@LogStatus", acceptLog.LogStatus);
commandENQ.AddParamWithValue("@LogAcceptDate", acceptLog.LogAcceptDate);
commandENQ.AddParamWithValue("@StatsPullRequestId", acceptLog.StatsPullRequestId);
commandENQ.ExecuteNonQuery();
}
if (StatsType == "Accept")
{
strSQL = "UPDATE ApsimFiles SET StatsAccepted = @StatsAccepted, IsMerged = @IsMerged WHERE PullRequestId = @PullRequestId";
using (DbCommand commandENQ = connection.CreateCommand(strSQL))
{
commandENQ.CommandType = CommandType.Text;
commandENQ.AddParamWithValue("@StatsAccepted", statsAccepted);
commandENQ.AddParamWithValue("@IsMerged", statsAccepted); //do this the same to during changeover
commandENQ.AddParamWithValue("@PullRequestId", acceptLog.PullRequestId);
commandENQ.ExecuteNonQuery();
}
}
//Utilities.WriteToLogFile(string.Format(" Accept Stats Status updated to {0} by {1} on {2}. Reason: {3}", acceptLog.LogStatus, acceptLog.LogPerson, acceptLog.SubmitDate, acceptLog.LogReason));
}
catch (Exception ex)
{
Utilities.WriteToLogFile(string.Format("ERROR in UpdateAsStatsAccepted: Pull Request Id {0}, Failed to update as 'Stats Accepted': {1}", acceptLog.PullRequestId.ToString(), ex.Message.ToString()));
}
}
/// <summary>
/// Unsure exactly what the idea is here. Seems to rename the P/O table name
/// for all apsim files in the entire DB?
/// </summary>
/// <param name="connection">Database connection.</param>
/// <param name="fileName">Name of the .apsimx file containing the P/O table to be renamed.</param>
/// <param name="oldTableName">Old P/O table name.</param>
/// <param name="newTableName">New P/O table name.</param>
public static void RenamePOTable(DbConnection connection, string fileName, string oldTableName, string newTableName)
{
string sql = ReflectionUtilities.GetResourceAsString("APSIM.PerformanceTests.Service.RenamePOTable.sql");
using (DbCommand command = connection.CreateCommand(sql))
{
command.AddParamWithValue("@NewTableName", newTableName);
command.AddParamWithValue("@OldTableName", oldTableName);
command.AddParamWithValue("@FileName", fileName);
command.ExecuteNonQuery();
}
}
/// <summary>
/// Insert DataTable into a database. This assumes that
/// the table already exists in the database.
/// </summary>
/// <param name="connection">Connection to the database.</param>
/// <param name="table">Table to be inserted.</param>
public static void InsertDataIntoDatabase(DbConnection connection, DataTable table)
{
string[] colNames = table.Columns.Cast <DataColumn>().Select(c => c.ColumnName).ToArray();
string[] paramNames = colNames.Select(n => "@" + n).ToArray();
string[] escapedColNames = colNames.Select(c => $"[{c}]").ToArray();
string sql = $"INSERT INTO {table.TableName} ({string.Join(", ", escapedColNames)})\n" +
$"VALUES ({string.Join(", ", paramNames)});";
using (DbCommand command = connection.CreateCommand(sql))
{
foreach (string param in paramNames)
{
command.AddParamWithValue(param, null);
}
foreach (DataRow row in table.Rows)
{
foreach (DataColumn col in table.Columns)
{
string paramName = "@" + col.ColumnName;
command.Parameters[paramName].Value = row[col.ColumnName];
}
command.ExecuteNonQuery();
}
}
}
public static DbCommand AddParameters(this DbCommand cmd, params object[] parameters)
{
if (cmd.CommandType == CommandType.StoredProcedure)
{
throw new ArgumentException("Cannot use positional parameters with command type StoredProcedure. Use named parameters instead.");
}
var command = cmd.CommandText;
var paramIndex = 0;
for (var index = 0; ; index += ParamPrefix.Length)
{
index = command.IndexOf(ParamPrefix, index, StringComparison.Ordinal);
if (index == -1)
{
break;
}
index++;
var endOf = command.IndexOfAny(NonCharacters, index);
var name = endOf == -1 ? command.Substring(index) : command.Substring(index, endOf - index);
cmd.AddParamWithValue(name, parameters[paramIndex++]);
if (endOf == -1)
{
break;
}
index = endOf;
}
return(cmd);
}
/// <summary>
/// Retreieves the matching Predicted Pbserved Values based on the Predicted Pbserved Id
/// </summary>
/// <param name="sqlCon"></param>
/// <param name="predictedObservedID"></param>
/// <returns></returns>
public static DataTable GetPredictedObservedValues(DbConnection sqlCon, int predictedObservedID)
{
DataTable resultDT = new DataTable();
try
{
string strSQL = "SELECT ID, ValueName, PredictedValue, ObservedValue"
+ " FROM PredictedObservedValues "
+ " WHERE PredictedObservedDetailsID = @PredictedObservedDetailsID "
+ " ORDER BY ValueName, ID ";
using (DbCommand commandER = sqlCon.CreateCommand(strSQL))
{
commandER.AddParamWithValue("@PredictedObservedDetailsID", predictedObservedID);
using (DbDataReader reader = commandER.ExecuteReader())
resultDT.Load(reader);
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(string.Format(" ERROR in getPredictedObservedValues: Unable to retrieve PredictedObserved Values for ID {0}: {1}", predictedObservedID, ex.Message.ToString()));
}
return(resultDT);
}
/// <summary>
/// Retrieves the Tests stats for the 'Accepted' PredictedObservedTests
/// </summary>
/// <param name="connectStr"></param>
/// <param name="acceptedPredictedObservedDetailsID"></param>
/// <returns></returns>
public static DataTable GetPredictedObservedTestsData(DbConnection connection, int acceptedPredictedObservedDetailsID)
{
DataTable acceptedStats = new DataTable();
if (acceptedPredictedObservedDetailsID > 0)
{
try
{
string strSQL = "SELECT Variable, Test, [Current] as 'Accepted', ID As 'AcceptedPredictedObservedTestsID' "
+ " FROM PredictedObservedTests "
+ " WHERE PredictedObservedDetailsID = @PredictedObservedDetailsID "
+ " ORDER BY Variable, LOWER(Test)";
using (DbCommand commandER = connection.CreateCommand(strSQL))
{
commandER.CommandType = CommandType.Text;
commandER.AddParamWithValue("@PredictedObservedDetailsID", acceptedPredictedObservedDetailsID);
DbDataReader reader = commandER.ExecuteReader();
acceptedStats.Load(reader);
reader.Close();
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(string.Format(" ERROR in getPredictedObservedTestsData: Unable to retrieve Tests Data PredictedObserved ID {0}: {1}", acceptedPredictedObservedDetailsID, ex.Message.ToString()));
}
}
return(acceptedStats);
}
/// <summary>
/// Deletes all Data for a specified Pull RequestId
/// </summary>
/// <param name="connectStr"></param>
/// <param name="pullRequestId"></param>
public static void DeleteByPullRequest(DbConnection sqlCon, int pullRequestId)
{
string sql = ReflectionUtilities.GetResourceAsString("APSIM.PerformanceTests.Service.DeleteByPullRequest.sql");
using (DbCommand command = sqlCon.CreateCommand(sql))
{
// Configure the command and parameter.
command.CommandTimeout = 0;
command.AddParamWithValue("@PullRequestID", pullRequestId);
command.ExecuteNonQuery();
}
}
/// <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);
}
/// <summary>
/// Gets the date/time of the latest run date for a given pull request.
/// Throws if the pull request does not exist.
/// </summary>
/// <param name="connection">Database connection.</param>
/// <param name="pullRequestID">Pull request ID.</param>
public static DateTime GetLatestRunDateForPullRequest(DbConnection connection, int pullRequestID)
{
try
{
string sql = "SELECT RunDate FROM ApsimFiles WHERE PullRequestId = @PullRequestId ORDER BY RunDate DESC";
sql = Utilities.Limit(connection, sql, 1);
using (DbCommand command = connection.CreateCommand(sql))
{
command.AddParamWithValue("@PullRequestId", pullRequestID);
return(DateTime.Parse(command.ExecuteScalar().ToString()));
}
}
catch (NullReferenceException)
{
throw new Exception($"No pull request exists with ID {pullRequestID}");
}
}
/// <summary>
/// Calcualte the percentage of tests which a given pull request passed.
/// Throws if pull request not found.
/// </summary>
/// <param name="connection">Database connection.</param>
/// <param name="pullRequestID">ID of the pull request.</param>
public static double GetPercentPassed(DbConnection connection, int pullRequestID)
{
string sql = "SELECT 100 * COUNT(CASE WHEN [PassedTests] = 100 THEN 1 ELSE NULL END) / COUNT(CASE WHEN [PassedTests] IS NOT NULL THEN 1 ELSE 0 END) as PercentPassed "
+ "FROM ApsimFiles AS a "
+ "INNER JOIN PredictedObservedDetails AS p ON a.ID = p.ApsimFilesID "
+ "WHERE a.PullRequestId = @PullRequestId ";
using (DbCommand command = connection.CreateCommand(sql))
{
command.AddParamWithValue("@PullRequestid", pullRequestID);
object res = command.ExecuteScalar();
if (res == null || res == DBNull.Value)
{
throw new Exception($"Pull request not found: #{pullRequestID}");
}
return(Convert.ToDouble(res));
}
}
/// <summary>
/// Get the number of files in a given pull request.
/// </summary>
/// <param name="connection">Database connection.</param>
/// <param name="pullRequestID">Pull request ID.</param>
public static int GetFileCount(DbConnection connection, int pullRequestID)
{
string sql = @"SELECT COUNT(*) as CurrentFileCount " +
"FROM PredictedObservedDetails, ApsimFiles af " +
"WHERE ApsimFilesID = af.ID " +
"AND af.PullRequestId = @PullRequestID";
using (DbCommand command = connection.CreateCommand(sql))
{
command.AddParamWithValue("@PullRequestID", pullRequestID);
object res = command.ExecuteScalar();
if (res == null || res == DBNull.Value)
{
throw new Exception($"Unable to get file count for pull request {pullRequestID}: pull request not found");
}
return(Convert.ToInt32(res));
}
}
/// <summary>
/// Get an apsim file with a given ID.
/// </summary>
/// <param name="connection"></param>
/// <param name="id"></param>
/// <returns></returns>
public static List <ApsimFile> GetApsimFiles(DbConnection connection, int id)
{
List <ApsimFile> files = new List <ApsimFile>();
string strSQL = "SELECT * FROM ApsimFiles WHERE PullRequestId = @PullRequestId ORDER BY RunDate DESC";
using (DbCommand commandER = connection.CreateCommand(strSQL))
{
commandER.AddParamWithValue("@PullRequestId", id);
using (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);
}
files.Add(apsim);
}
return(files);
}
}
}
public static DbCommand AddParameters(this DbCommand cmd, params object[] parameters)
{
var command = cmd.CommandText;
var valueList = new List <object>();
var nameList = new List <string>();
foreach (var p in parameters)
{
if (p is DbParameter dbParameter)
{
cmd.Parameters.Add(dbParameter);
nameList.Add(dbParameter.ParameterName);
}
else
{
valueList.Add(p);
}
}
var values = valueList.ToArray();
if (values.Length == 0)
{
return(cmd);
}
var names = nameList.ToArray();
var paramIndex = 0;
foreach (var name in EnumerateParams(command, names).Select(t => t.name).Distinct())
{
if (cmd.CommandType == CommandType.StoredProcedure)
{
throw new ArgumentException("Cannot use positional parameters that are not DbParameter type with command type StoredProcedure. Use named parameters instead.");
}
cmd.AddParamWithValue(name, values[paramIndex++]);
}
return(cmd);
}
/// <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>
/// 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>
/// Adds PredictedObservedTests Data to the database
/// </summary>
/// <param name="connectStr"></param>
/// <param name="currentApsimFileFileName"></param>
/// <param name="currentPODetailsID"></param>
/// <param name="currentPODetailsDatabaseTableName"></param>
/// <param name="dtTests"></param>
public static void AddPredictedObservedTestsData(DbConnection sqlCon, string currentApsimFileFileName, int currentPODetailsID, string currentPODetailsDatabaseTableName, DataTable dtTests)
{
if (dtTests.Rows.Count > 0)
{
try
{
string sql = @"INSERT INTO [dbo].[PredictedObservedTests] (
[PredictedObservedDetailsID], [Variable], [Test], [Accepted], [Current], [Difference], [PassedTest],
[AcceptedPredictedObservedTestsID], [IsImprovement], [SortOrder])
SELECT @PredictedObservedID, [Variable], [Test], [Accepted], [Current], [Difference], [PassedTest],
[AcceptedPredictedObservedTestsID], [IsImprovement],
(CASE WHEN [Test] = 'n' THEN 0 ELSE 1 END) as [SortOrder]
FROM @Tests
WHERE [Test] != 'Name'
AND ([Current] IS NOT NULL
OR [Accepted] IS NOT NULL);";
using (DbCommand commandENQ = sqlCon.CreateCommand(sql))
{
commandENQ.AddParamWithValue("@PredictedObservedID", currentPODetailsID);
DbParameter tableParam = commandENQ.CreateParameter();
tableParam.ParameterName = "@Tests";
// We don't want to send all columns over to the DB.
// Well, in the long run, it would be tidier. But for
// now, let's just reproduce the old behaviour.
tableParam.Value = new DataView(dtTests).ToTable(false, "Variable", "Test", "Accepted", "Current", "Difference", "PassedTest", "AcceptedPredictedObservedTestsID", "IsImprovement");
if (tableParam is SqlParameter)
{
(tableParam as SqlParameter).TypeName = "PredictedObservedTestsTableType";
}
commandENQ.Parameters.Add(tableParam);
commandENQ.ExecuteNonQuery();
}
// really?
using (DbCommand command = sqlCon.CreateCommand())
{
command.CommandText = @"UPDATE [dbo].[PredictedObservedTests]
SET [DifferencePercent] = ABS(100 * ([Difference]/[Accepted]))
WHERE [PredictedObservedDetailsID] = @PredictedObservedID
AND [Difference] IS NOT NULL AND [Difference] != 0
AND [Accepted] IS NOT NULL;" ;
command.AddParamWithValue("@PredictedObservedID", currentPODetailsID);
command.ExecuteNonQuery();
}
string[] testsToConsider = new[] // really?!
{
"n",
"RMSE",
"NSE",
"RSR",
};
IEnumerable <DataRow> data = dtTests.AsEnumerable().Where(r => testsToConsider.Contains(r["Test"]));
// todo: why is PassedTest sometimes set to null?
data = data.Where(r => r["PassedTest"] != null && !Convert.IsDBNull(r["PassedTest"]));
int totalTests = data.Count();
int totalPassed = data.Where(r => Convert.ToInt32(r["PassedTest"]) == 1).Count();
double percentPassed = 100.0 * totalPassed / totalTests;
// Update the PredictedObservedDetails table and set the PassedTests field appropriately.
sql = "UPDATE PredictedObservedDetails " +
"SET PassedTests = @PassedTests, " +
"HasTests = 1 " +
"WHERE ID = @PredictedObservedID";
using (DbCommand command = sqlCon.CreateCommand(sql))
{
command.AddParamWithValue("@PassedTests", percentPassed); // really?!?!?!
command.AddParamWithValue("@PredictedObservedID", currentPODetailsID);
command.ExecuteNonQuery();
}
Utilities.WriteToLogFile(string.Format(" Tests Data for {0}.{1} import completed successfully!", currentApsimFileFileName, currentPODetailsDatabaseTableName));
}
catch (Exception ex)
{
Utilities.WriteToLogFile(string.Format(" ERROR in AddPredictedObservedTestsData: Unable to save Tests Data for {0}.{1}: {2}", currentApsimFileFileName, currentPODetailsDatabaseTableName, ex.Message.ToString()));
}
}
}
/// <summary>
/// Add an apsim file to the database.
/// </summary>
public static void InsertApsimFile(DbConnection connection, ApsimFile apsimfile, out string ErrMessageHelper, out int ApsimID)
{
ApsimID = 0;
string strSQL;
Utilities.WriteToLogFile(string.Format("Processing PullRequestId {0}, Apsim Filename {1}, dated {2}!", apsimfile.PullRequestId, apsimfile.FileName, apsimfile.RunDate.ToString("dd/MM/yyyy HH:mm")));
//--------------------------------------------------------------------------------------
//Need to check if this Pull Request Id has already been used, if it has, then we need
//to delete everything associated with it before we save the new set of data
//--------------------------------------------------------------------------------------
int pullRequestCount = 0;
Utilities.WriteToLogFile(" Checking for existing Pull Requests Details.");
try
{
strSQL = "SELECT COUNT(ID) FROM ApsimFiles WHERE PullRequestId = @PullRequestId AND RunDate != @RunDate";
using (DbCommand commandES = connection.CreateCommand())
{
commandES.CommandText = strSQL;
commandES.CommandType = CommandType.Text;
commandES.AddParamWithValue("@PullRequestId", apsimfile.PullRequestId);
commandES.AddParamWithValue("@RunDate", apsimfile.RunDate);
pullRequestCount = Convert.ToInt32(commandES.ExecuteScalar());
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(" ERROR: Checking for existing Pull Requests: " + ex.Message.ToString());
throw;
}
if (pullRequestCount > 0)
{
try
{
Utilities.WriteToLogFile(" Removing existing Pull Requests Details.");
using (DbCommand commandENQ = connection.CreateCommand())
{
// Configure the command and parameter.
commandENQ.CommandText = ReflectionUtilities.GetResourceAsString("APSIM.PerformanceTests.Service.DeleteByPRIDNotByDate.sql");
commandENQ.CommandTimeout = 0;
commandENQ.AddParamWithValue("@PullRequestID", apsimfile.PullRequestId);
commandENQ.AddParamWithValue("@RunDate", apsimfile.RunDate);
commandENQ.ExecuteNonQuery(); // 86 seconds
}
Utilities.WriteToLogFile(" Removed original Pull Request Data.");
}
catch (Exception ex)
{
Utilities.WriteToLogFile(" ERROR: Error Removing original Pull Request Data: " + ex.Message.ToString());
throw;
}
}
//--------------------------------------------------------------------------------------
//Add the ApsimFile Record first, so that we can get back the IDENTITY (ID) value
//--------------------------------------------------------------------------------------
//using (SqlConnection con = new SqlConnection(connectStr))
//{
Utilities.WriteToLogFile(" Inserting ApsimFiles details.");
try
{
strSQL = "INSERT INTO ApsimFiles (PullRequestId, FileName, FullFileName, RunDate, StatsAccepted, IsMerged, SubmitDetails, AcceptedPullRequestId, AcceptedRunDate) "
+ "VALUES (@PullRequestId, @FileName, @FullFileName, @RunDate, @StatsAccepted, @IsMerged, @SubmitDetails, @AcceptedPullRequestId, @AcceptedRunDate); "
+ Utilities.Limit(connection, "SELECT ID FROM ApsimFiles ORDER BY ID DESC", 1);
using (DbCommand commandES = connection.CreateCommand())
{
commandES.CommandText = strSQL;
commandES.CommandType = CommandType.Text;
commandES.AddParamWithValue("@PullRequestId", apsimfile.PullRequestId);
commandES.AddParamWithValue("@FileName", apsimfile.FileName);
commandES.AddParamWithValue("@FullFileName", Utilities.GetModifiedFileName(apsimfile.FullFileName));
commandES.AddParamWithValue("@RunDate", apsimfile.RunDate);
commandES.AddParamWithValue("@StatsAccepted", apsimfile.StatsAccepted);
commandES.AddParamWithValue("@IsMerged", apsimfile.IsMerged);
commandES.AddParamWithValue("@SubmitDetails", apsimfile.SubmitDetails);
// The accepted stats data will be set below, after we've inserted all data.
// In the long run, this behaviour should probably be changed.
commandES.AddParamWithValue("@AcceptedPullRequestId", -1);
commandES.AddParamWithValue("@AcceptedRunDate", "");
//this should return the IDENTITY value for this record (which is required for the next update)
ErrMessageHelper = "Filename: " + apsimfile.FileName;
ApsimID = Convert.ToInt32(commandES.ExecuteScalar()); // fast
apsimfile.ID = ApsimID;
ErrMessageHelper = "Filename: " + apsimfile.FileName + "- ApsimID: " + ApsimID;
Utilities.WriteToLogFile(string.Format(" Filename {0} inserted into ApsimFiles successfully!", apsimfile.FileName));
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(" ERROR: Inserting into ApsimFiles failed: " + ex.Message.ToString());
throw;
}
//--------------------------------------------------------------------------------------
//Add the Simulation Details to the database
//--------------------------------------------------------------------------------------
if (apsimfile.Simulations.Rows.Count > 0)
{
try
{
Utilities.WriteToLogFile(" Inserting Simulation details for " + apsimfile.FileName);
using (DbCommand commandENQ = connection.CreateCommand("INSERT INTO Simulations (ApsimFilesID, Name, OriginalSimulationID) SELECT @ApsimID, Name, ID FROM @Simulations;"))
{
commandENQ.AddParamWithValue("@ApsimID", apsimfile.ID);
DbParameter simulations = commandENQ.CreateParameter();
if (simulations is SqlParameter)
{
(simulations as SqlParameter).TypeName = "SimulationDataTableType";
}
simulations.ParameterName = "@Simulations";
simulations.Value = apsimfile.Simulations;
commandENQ.Parameters.Add(simulations);
commandENQ.ExecuteNonQuery(); // ~2s with table-value parameter. ~10m with individual inserts
ErrMessageHelper = "- Simualtion Data for " + apsimfile.FileName;
Utilities.WriteToLogFile(string.Format(" Filename {0} Simulation Data imported successfully!", apsimfile.FileName));
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(" ERROR: usp_SimulationsInsert failed: " + ex.Message.ToString());
throw;
}
}
//--------------------------------------------------------------------------------------
//Add the Predicted Observed Details (metadata) and then the data
//--------------------------------------------------------------------------------------
// Look at each individual set of data
int predictedObservedID = 0;
foreach (PredictedObservedDetails poDetail in apsimfile.PredictedObserved)
{
Utilities.WriteToLogFile(string.Format(" Inserting Filename {0} PredictedObserved Table Details {1}.", apsimfile.FileName, poDetail.DatabaseTableName));
try
{
strSQL = "INSERT INTO PredictedObservedDetails ("
+ "ApsimFilesID, TableName, PredictedTableName, ObservedTableName, FieldNameUsedForMatch, FieldName2UsedForMatch, FieldName3UsedForMatch, HasTests) "
+ "VALUES (@ApsimFilesID, @TableName, @PredictedTableName, @ObservedTableName, @FieldNameUsedForMatch, @FieldName2UsedForMatch, @FieldName3UsedForMatch, 1); "
+ "SELECT ID FROM PredictedObservedDetails ORDER BY ID DESC";
strSQL = Utilities.Limit(connection, strSQL, 1);
using (DbCommand commandES = connection.CreateCommand())
{
commandES.CommandText = strSQL;
commandES.CommandType = CommandType.Text;
commandES.AddParamWithValue("@ApsimFilesID", ApsimID);
commandES.AddParamWithValue("@TableName", poDetail.DatabaseTableName);
commandES.AddParamWithValue("@PredictedTableName", poDetail.PredictedTableName);
commandES.AddParamWithValue("@ObservedTableName", poDetail.ObservedTableName);
commandES.AddParamWithValue("@FieldNameUsedForMatch", poDetail.FieldNameUsedForMatch);
commandES.AddParamWithValue("@FieldName2UsedForMatch", poDetail.FieldName2UsedForMatch);
commandES.AddParamWithValue("@FieldName3UsedForMatch", poDetail.FieldName3UsedForMatch);
//this should return the IDENTITY value for this record (which is required for the next update)
ErrMessageHelper = "PredictedObservedDetails for " + poDetail.DatabaseTableName;
predictedObservedID = Convert.ToInt32(commandES.ExecuteScalar());
ErrMessageHelper = "PredictedObservedDetails for " + poDetail.DatabaseTableName + "(ID: " + predictedObservedID + ")";
Utilities.WriteToLogFile(string.Format(" Filename {0} PredictedObserved Table Details {1}, (Id: {2}) imported successfully!", apsimfile.FileName, poDetail.DatabaseTableName, predictedObservedID));
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(" ERROR: INSERT INTO PredictedObservedDetails failed: " + ex.Message.ToString());
throw;
}
//--------------------------------------------------------------------------------------
//And finally this is where we will insert the actual Predicted Observed DATA
//--------------------------------------------------------------------------------------
Utilities.WriteToLogFile(string.Format(" PredictedObserved Data Values for {0}.{1} - import started!", apsimfile.FileName, poDetail.DatabaseTableName));
for (int i = 0; i < poDetail.Data.Columns.Count; i++)
{
string observedColumnName = poDetail.Data.Columns[i].ColumnName.Trim();
if (observedColumnName.StartsWith("Observed"))
{
//get the corresponding Predicted Column Name
int dotPosn = observedColumnName.IndexOf('.');
string valueName = observedColumnName.Substring(dotPosn + 1);
string predictedColumnName = "Predicted." + valueName;
if (!poDetail.Data.Columns.Contains(predictedColumnName))
{
continue;
}
//need to find the first (and then each instance thereafter) of a field name being with Observed,
//the get the corresponding Predicted field name, and then create a new table definition based on this
//data,
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = @"INSERT INTO [dbo].[PredictedObservedValues] ([PredictedObservedDetailsID], [SimulationsID], [MatchName], [MatchValue], [MatchName2], [MatchValue2], [MatchName3], [MatchValue3], [ValueName], [PredictedValue], [ObservedValue])
SELECT @PredictedObservedID, s.[ID], @MatchName, p.[MatchValue], @MatchName2, p.[MatchValue2], @MatchName3, p.[MatchValue3], @ValueName, p.[PredictedValue], p.[ObservedValue]
FROM @PredictedOabservedData p INNER JOIN [dbo].[Simulations] s
ON s.[OriginalSimulationID] = p.[SimulationID]
WHERE s.[ApsimFilesID] = @ApsimFilesID
AND p.[PredictedValue] IS NOT NULL
AND p.[ObservedValue] IS NOT NULL;" ;
command.AddParamWithValue("@PredictedObservedID", predictedObservedID);
command.AddParamWithValue("@MatchName", poDetail.FieldNameUsedForMatch);
command.AddParamWithValue("@MatchName2", (object)poDetail.FieldName2UsedForMatch ?? DBNull.Value);
command.AddParamWithValue("@MatchName3", (object)poDetail.FieldName3UsedForMatch ?? DBNull.Value);
command.AddParamWithValue("@ValueName", valueName);
command.AddParamWithValue("@ApsimFilesID", apsimfile.ID);
DataTable poData = new DataTable();
poData.Columns.Add("SimulationID", typeof(int));
poData.Columns.Add("MatchValue", typeof(string));
poData.Columns.Add("MatchValue2", typeof(string));
poData.Columns.Add("MatchValue3", typeof(string));
poData.Columns.Add("PredictedValue", poDetail.Data.Columns[predictedColumnName].DataType);
poData.Columns.Add("ObservedValue", poDetail.Data.Columns[observedColumnName].DataType);
poData = poDetail.Data.AsEnumerable().Select(r =>
{
var row = poData.NewRow();
foreach (DataColumn column in poData.Columns)
{
if (column.ColumnName == "MatchValue")
{
row[column.ColumnName] = r[poDetail.FieldNameUsedForMatch];
}
else if (column.ColumnName == "MatchValue2")
{
if (!string.IsNullOrEmpty(poDetail.FieldName2UsedForMatch))
{
row[column.ColumnName] = r[poDetail.FieldName2UsedForMatch];
}
}
else if (column.ColumnName == "MatchValue3")
{
if (!string.IsNullOrEmpty(poDetail.FieldName3UsedForMatch))
{
row[column.ColumnName] = r[poDetail.FieldName3UsedForMatch];
}
}
else if (column.ColumnName == "PredictedValue")
{
row[column.ColumnName] = r[predictedColumnName];
}
else if (column.ColumnName == "ObservedValue")
{
row[column.ColumnName] = r[observedColumnName];
}
else
{
row[column.ColumnName] = r[column.ColumnName];
}
}
return(row);
}).CopyToDataTable();
object[] simulationIDs = poData.AsEnumerable().Select(r => r["SimulationID"]).ToArray();
DbParameter tableParam = command.CreateParameter();
tableParam.ParameterName = "@PredictedOabservedData";
tableParam.Value = poData;
if (tableParam is SqlParameter)
{
(tableParam as SqlParameter).TypeName = "PredictedObservedDataThreeTableType";
}
command.Parameters.Add(tableParam);
Type dataType = poData.Columns["PredictedValue"].DataType;
bool validColumn = dataType != typeof(string) && dataType != typeof(bool) && dataType != typeof(DateTime); // so a char is a testable piece of data but string is not?
if (validColumn)
{
command.ExecuteNonQuery();
}
}
}
}
//Need to run the testing procecedure here, and then save the test data
if (poDetail.Data.Rows.Count > 0)
{
ErrMessageHelper = string.Empty;
Utilities.WriteToLogFile(string.Format(" Tests Data for {0}.{1} import started.", apsimfile.FileName, poDetail.DatabaseTableName));
//need to retrieve data for the "AcceptedStats" version, so that we can update the stats
int acceptedPredictedObservedDetailsID = 0; //this should get updated in 'RetrieveAcceptedStatsData'
ErrMessageHelper = "Processing RetrieveAcceptedStatsData.";
DataTable acceptedStats = RetrieveAcceptedStatsData(connection, ApsimID, apsimfile, poDetail, predictedObservedID, ref acceptedPredictedObservedDetailsID);
ErrMessageHelper = "Processing Tests.DoValidationTest.";
DataTable dtTests = Tests.DoValidationTest(poDetail.DatabaseTableName, poDetail.Data, acceptedStats);
ErrMessageHelper = "Processing DBFunctions.AddPredictedObservedTestsData.";
DBFunctions.AddPredictedObservedTestsData(connection, apsimfile.FileName, predictedObservedID, poDetail.DatabaseTableName, dtTests);
//Update the accepted reference for Predicted Observed Values, so that it can be
ErrMessageHelper = "Processing DBFunctions.UpdatePredictedObservedDetails.";
DBFunctions.UpdatePredictedObservedDetails(connection, acceptedPredictedObservedDetailsID, predictedObservedID);
}
}
}
