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