public async Task <IHttpActionResult> PostApsimFile(ApsimFile apsimfile)
{
Utilities.WriteToLogFile(" ");
Utilities.WriteToLogFile("==========================================================");
Utilities.WriteToLogFile("Post Apsim File: Ready to process apsimfile.");
string ErrMessageHelper = "";
try
{
// fixme - dont' want an out parameter here, but
// don't want to change anything until we have tests.
int ApsimID;
using (SqlConnection conn = new SqlConnection(Utilities.GetConnectionString()))
{
conn.Open();
InsertApsimFile(conn, apsimfile, out ErrMessageHelper, out ApsimID);
}
return(CreatedAtRoute("DefaultApi", new { id = ApsimID }, apsimfile));
}
catch (Exception ex)
{
Utilities.WriteToLogFile(string.Format(" ERROR in PostApsimFile: {0} - {1}", ErrMessageHelper, ex.ToString()));
throw new Exception(string.Format(" ERROR in PostApsimFile: {0} - {1}", ErrMessageHelper, ex.Message), ex);
}
}
public void TestComplicatedInsert()
{
foreach (DbConnection conn in populousConnections)
{
// Get the accepted data for the HarvestObsPred table.
// This was generated by querying the production DB
// and saving the result to a csv file.
string json = ReflectionUtilities.GetResourceAsString("APSIM.PerformanceTests.Tests.Resources.accepted-wheat.json");
DataTable poTests = JsonConvert.DeserializeObject <DataTable>(json);
poTests.TableName = "PredictedObservedTests";
Utility.InsertDataIntoDatabase(conn, poTests);
// Read the ApsimFile object from json. This json file was generated
// by running the collector on Wheat.apsimx and serializing the result
// to json. Note that the DB is only partially full - not all sims had been run.
json = ReflectionUtilities.GetResourceAsString("APSIM.PerformanceTests.Tests.Resources.WheatHarvestObsPred.json");
ApsimFile file = JsonConvert.DeserializeObject <ApsimFile>(json);
ApsimFilesController.InsertApsimFile(conn, file, out string error, out int apsimID);
using (DbCommand command = conn.CreateCommand("SELECT * FROM PredictedObservedTests"))
{
using (DbDataReader reader = command.ExecuteReader())
{
DataTable table = new DataTable();
table.Load(reader);
Assert.AreEqual(1649, table.Rows.Count);
}
}
}
}
private List <vCurrentAndAccepted> BindCurrentAcceptedValuesssssss(String variable, PredictedObservedDetail currPODetails)
{
List <vCurrentAndAccepted> POCurrentValuesList;
//some of the older records may not have this yet. So find it the old way.
if ((currPODetails.AcceptedPredictedObservedDetailsID == null) || (currPODetails.AcceptedPredictedObservedDetailsID <= 0))
{
//Retrieve the curresponding (parent) ApsimFile for this PredictedObservedDetail
ApsimFile apsimFile = ApsimFilesDS.GetByID(currPODetails.ApsimFilesID);
//get the Pull Request Id for the lastest released pull request that is not the current one
int acceptPullRequestId = ApsimFilesDS.GetLatestMergedPullRequestId(apsimFile.PullRequestId);
//get the PredictedObservedDetail.ID for the records that match our current record 'matching' criteria
currPODetails.AcceptedPredictedObservedDetailsID = PredictedObservedDS.GetIDByMatchingDetails(acceptPullRequestId, apsimFile.FileName, currPODetails.TableName,
currPODetails.PredictedTableName, currPODetails.ObservedTableName, currPODetails.FieldNameUsedForMatch);
}
POCurrentValuesList = PredictedObservedDS.GetCurrentAcceptedValuesWithNulls(variable, currPODetails.ID, (int)currPODetails.AcceptedPredictedObservedDetailsID);
//POCurrentValuesList = PredictedObservedDS.GetCurrentAcceptedValues(variable, currPODetails.ID, (int)currPODetails.AcceptedPredictedObservedDetailsID);
//get the data to be displayed in the chart and grid
//POValuesList = PredictedObservedDS.GetCurrentAcceptedValues(variable, currPODetails.ID, (int)currPODetails.AcceptedPredictedObservedDetailsID);
//POValuesDT = Genfuncs.ToDataTable(POCurrentValuesList);
return(POCurrentValuesList);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
int predictedObservedId = 0;
if (!string.IsNullOrEmpty(Request.QueryString["PO_Id"]))
{
predictedObservedId = int.Parse(Request.QueryString["PO_Id"]);
}
if (predictedObservedId > 0)
{
hfPredictedObservedID.Value = predictedObservedId.ToString();
PredictedObservedDetail currPODetails = PredictedObservedDS.GetByPredictedObservedID(predictedObservedId);
ApsimFile apsim = ApsimFilesDS.GetByID(currPODetails.ApsimFilesID);
hfPullRequestID.Value = apsim.PullRequestId.ToString();
lblPullRequest.Text = "Pull Request: " + apsim.PullRequestId.ToString();
lblApsimFile.Text = "Apsim File: " + apsim.FileName;
lblPOTableName.Text = "Table: " + currPODetails.TableName + " (PO Id: " + currPODetails.ID.ToString() + ")";
//now bind the data
RetrieveDataAndBindCharts(currPODetails);
}
}
}
public void TestCreate2Simulations()
{
// -------------------------------------------------------------
// Use case: Drag a node from standard toolbox and drop on a
// simulation set. Drag same node and drop again.
// Ensure there is not a simulation name clash.
// -------------------------------------------------------------
const string SimulationFromToolbox =
"<simulation name=\"My Sim\">" +
" <clock>" +
" <start_date>1/01/1940</start_date>" +
" <end_date>31/01/1940</end_date>" +
" </clock>" +
" <outputfile>" +
" <variable>variable1</variable>" +
" <variable>variable2</variable>" +
" </outputfile>" +
" <metfile>" +
" <filename>c:\\dummy.met</filename>" +
" </metfile>" +
"</simulation>";
ApsimFile SimulationSet = new ApsimFile();
SimulationSet.New();
SimulationSet.RootComponent.Add(SimulationFromToolbox);
SimulationSet.RootComponent.Add(SimulationFromToolbox);
Assert.AreEqual(SimulationSet.RootComponent.ChildNodes.Count, 2);
Assert.AreEqual(SimulationSet.RootComponent.ChildNodes[0].Name, "My Sim");
Assert.AreEqual(SimulationSet.RootComponent.ChildNodes[1].Name, "My Sim1");
Assert.IsTrue(Simulations.IsDirty);
}
public void EnsureOldPullRequestDataIsDeleted()
{
foreach (DbConnection connection in populousConnections)
{
using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM ApsimFiles"))
Assert.AreEqual(1, command.ExecuteScalar());
using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM PredictedObservedDetails"))
Assert.AreEqual(1, command.ExecuteScalar());
using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM PredictedObservedTests"))
Assert.AreEqual(11, command.ExecuteScalar());
using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM PredictedObservedValues"))
Assert.AreEqual(2, command.ExecuteScalar());
using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM Simulations"))
Assert.AreEqual(2, command.ExecuteScalar());
// Now insert the new apsimfile. Number of rows in all tables should be unchanged.
// The only difference is the run date.
ApsimFile file = GetSimpleApsimFile();
file.RunDate = file.RunDate.AddMinutes(1);
ApsimFilesController.InsertApsimFile(connection, file, out _, out _);
using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM ApsimFiles"))
Assert.AreEqual(1, command.ExecuteScalar());
using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM PredictedObservedDetails"))
Assert.AreEqual(1, command.ExecuteScalar());
using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM PredictedObservedTests"))
Assert.AreEqual(11, command.ExecuteScalar());
using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM PredictedObservedValues"))
Assert.AreEqual(2, command.ExecuteScalar());
using (DbCommand command = connection.CreateCommand("SELECT COUNT(*) FROM Simulations"))
Assert.AreEqual(2, command.ExecuteScalar());
using (DbCommand command = connection.CreateCommand(Utilities.Limit(connection, "SELECT RunDate FROM ApsimFiles", 1)))
{
object actual = command.ExecuteScalar();
if (connection is SQLiteConnection)
{
Assert.AreEqual("2020-01-01 00:01:00", actual);
}
else
{
Assert.AreEqual(new DateTime(2020, 1, 1).AddMinutes(1), actual);
}
}
}
}
//ULTIMATELY, THIS WILL RETURN MULTIPLE, BUT FOR NOW, JUST LEAVE IT AS ONE
private static async Task GetApsimFileByPullRequestID(HttpClient cons, int id)
{
HttpResponseMessage response = await cons.GetAsync("api/apsimfiles/" + id);
response.EnsureSuccessStatusCode();
if (response.IsSuccessStatusCode)
{
ApsimFile apsim = await response.Content.ReadAsAsync <ApsimFile>();
WriteToLogFile(string.Format("{0}\t{1}\t{2}\t{3}\t{4}", apsim.PullRequestId, apsim.FileName, apsim.FullFileName, apsim.RunDate, apsim.IsMerged));
}
}
public void Init()
{
PlugIns.LoadAll();
Simulations = new ApsimFile();
XmlDocument Doc = new XmlDocument();
Doc.LoadXml(ApsimFileContents.Replace("$version$", APSIMChangeTool.CurrentVersion.ToString()));
Simulations.Open(Doc.DocumentElement);
Assert.IsTrue(Simulations.IsDirty);
Assert.AreEqual(Simulations.FileName, "Untitled");
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
int predictedObservedId = 0;
string variable = string.Empty;
if (!string.IsNullOrEmpty(Request.QueryString["PO_Id"]))
{
predictedObservedId = int.Parse(Request.QueryString["PO_Id"]);
}
if (predictedObservedId > 0)
{
hfPredictedObservedID.Value = predictedObservedId.ToString();
PredictedObservedDetail currPODetails = PredictedObservedDS.GetByPredictedObservedID(predictedObservedId);
ApsimFile apsim = ApsimFilesDS.GetByID(currPODetails.ApsimFilesID);
hfPullRequestID.Value = apsim.PullRequestId.ToString();
Session["POTestsDT"] = null;
BindCurrentAcceptedTestsDataTable();
lblPullRequest.Text = "Pull Request: " + apsim.PullRequestId.ToString();
lblApsimFile.Text = "Apsim File: " + apsim.FileName;
lblPOTableName.Text = "Table: " + currPODetails.TableName + " (PO Id: " + currPODetails.ID.ToString() + ")";
if (!string.IsNullOrEmpty(Request.QueryString["Variable"]))
{
//LoadTimer.Enabled = false;
variable = Request.QueryString["Variable"].ToString();
BindPredictedObservedVariables(currPODetails.ID);
ddlVariables.SelectedValue = variable;
ClientScript.RegisterStartupScript(this.GetType(), "ScrollScript", "scrollToDiv();", true);
}
else
{
BindPredictedObservedVariables(currPODetails.ID);
}
//if (gvPOTests.Rows.Count > 0)
//{
// //NOTE: This is registered using the ClientScript (not ScriptManager), with different parameters, as this grid is NOT in an update panel
// ClientScript.RegisterStartupScript(this.GetType(), "CreateGridHeader", "<script>CreateGridHeader('GridDataDiv_POTests', 'ContentPlaceHolder1_gvPOTests', 'GridHeaderDiv_POTests');</script>");
//}
}
}
//if (gvPOValues.Rows.Count > 0)
//{
// //NOTE: This is registered using the ScriptManager (not ClientScript), with different parameters, as this grid is nested in an update panel
// ScriptManager.RegisterStartupScript(this, GetType(), "CreateGridHeader_POValues", "CreateGridHeader('GridDataDiv_POValues', 'ContentPlaceHolder1_gvPOValues', 'GridHeaderDiv_POValues');", true);
//}
}
/// <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 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);
}
}
public void TestAcceptedStatsIDAfterInsert()
{
foreach (DbConnection connection in populousConnections)
{
// Let's pretend that we've accepted the existing pull request's stats.
using (DbCommand command = connection.CreateCommand("UPDATE ApsimFiles SET StatsAccepted = 1"))
command.ExecuteNonQuery();
// Now insert another pull request - its accepted stats ID should
// be the ID of this pull request (1).
ApsimFile file = GetSimpleApsimFile();
file.PullRequestId = 2;
ApsimFilesController.InsertApsimFile(connection, file, out _, out _);
string sql = "SELECT AcceptedPullRequestId FROM ApsimFiles ORDER BY ID DESC";
sql = Utilities.Limit(connection, sql, 1);
using (DbCommand command = connection.CreateCommand(sql))
Assert.AreEqual(1, command.ExecuteScalar());
}
}
public void TestPostApsimFileWithLongSimulationID()
{
ApsimFile file = GetSimpleApsimFile();
DataTable poData = new DataTable("PredictedObserved");
poData.Columns.Add("SimulationID", typeof(long));
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);
file.PredictedObserved.ElementAt(0).Data = poData;
foreach (DbConnection connection in populousConnections)
{
ApsimFilesController.InsertApsimFile(connection, file, out _, out _);
using (DbCommand command = connection.CreateCommand(Utilities.Limit(connection, "SELECT SimulationsID FROM PredictedObservedValues", 1)))
Assert.AreEqual(1, command.ExecuteScalar());
}
}
/// <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);
}
}
}
/// <summary>
///
/// </summary>
/// <param name="apsimInstance"></param>
static async Task PostApsimRun(ApsimFile apsimInstance)
{
WriteToLogFile(string.Format(" Calling httpClient with ApsimFile {0}", apsimInstance.FileName));
string apsimFileName = string.Empty;
apsimFileName = apsimInstance.FileName;
try
{
string json = JsonConvert.SerializeObject(apsimInstance);
//this will call the service on www..apsim.info.au
HttpResponseMessage response = await httpClient.PostAsJsonAsync("api/apsimfiles", apsimInstance);
response.EnsureSuccessStatusCode();
if (response.IsSuccessStatusCode)
{
WriteToLogFile(string.Format(" Successfully posted ApsimFile {0}", apsimFileName));
}
else
{
WriteToLogFile(string.Format(" ERROR posting ApsimFile {0}: {1}", apsimFileName, response.StatusCode.ToString()));
}
}
catch (TaskCanceledException ex)
{
string errorMessage = $" ERROR posting Apsim File {apsimFileName} to Web API. ";
if (!ex.CancellationToken.IsCancellationRequested)
{
errorMessage += "This is probably due to a timeout: ";
}
errorMessage += ex.ToString();
WriteToLogFile(errorMessage);
throw ex;
}
catch (Exception ex)
{
WriteToLogFile(string.Format(" ERROR posting Apsim File {0} to Web API: {1} ", apsimFileName, ex.ToString()));
throw ex;
}
}
private void RetrieveDataAndBindCharts(PredictedObservedDetail currPODetails)
{
//some of the older records may not have this yet. So find it the old way.
if ((currPODetails.AcceptedPredictedObservedDetailsID == null) || (currPODetails.AcceptedPredictedObservedDetailsID <= 0))
{
//Retrieve the curresponding (parent) ApsimFile for this PredictedObservedDetail
ApsimFile apsimFile = ApsimFilesDS.GetByID(currPODetails.ApsimFilesID);
//get the Pull Request Id for the lastest released pull request that is not the current one
int acceptPullRequestId = ApsimFilesDS.GetLatestMergedPullRequestId(apsimFile.PullRequestId);
//get the PredictedObservedDetail.ID for the records that match our current record 'matching' criteria
currPODetails.AcceptedPredictedObservedDetailsID = PredictedObservedDS.GetIDByMatchingDetails(acceptPullRequestId, apsimFile.FileName, currPODetails.TableName,
currPODetails.PredictedTableName, currPODetails.ObservedTableName, currPODetails.FieldNameUsedForMatch);
}
List <vSimulationPredictedObserved> POCurrentValuesList = PredictedObservedDS.GetCurrentAcceptedSimulationValues(currPODetails.ID, (int)currPODetails.AcceptedPredictedObservedDetailsID);
string holdVariable = string.Empty;
string holdSimulationName = string.Empty;
string tooltip = string.Empty;
string textAnnotation = string.Empty;
List <PODataPoint> acceptedData = new List <PODataPoint>();
List <PODataPoint> currentData = new List <PODataPoint>();
double maxXYValue = 0;
bool newchart = false;
int chartNo = 0;
foreach (vSimulationPredictedObserved item in POCurrentValuesList)
{
if (holdVariable == string.Empty)
{
holdVariable = item.ValueName;
holdSimulationName = item.SimulationName;
tooltip = String.Format("{0} - {1}", item.SimulationName, item.ValueName);
}
newchart = false;
//if (item.SimulationName != holdSimulationName) { newchart = true; }
if (item.ValueName != holdVariable)
{
newchart = true;
}
if (newchart == true)
{
//create the chart
if ((currentData.Count > 0) || (currentData.Count > 0))
{
chartNo += 1;
textAnnotation = GetPredictedObservedTests(currPODetails.ID, holdVariable);
BindCurrentAcceptedChart(chartNo, holdVariable, currPODetails.ID, tooltip, currentData.ToArray(), acceptedData.ToArray(), maxXYValue, textAnnotation);
}
//reset the variables
acceptedData.Clear();
currentData.Clear();
maxXYValue = 0;
holdVariable = item.ValueName;
holdSimulationName = item.SimulationName;
}
if ((item.AcceptedObservedValue != null) && (item.AcceptedPredictedValue != null))
{
acceptedData.Add(new PODataPoint()
{
X = (double)item.AcceptedObservedValue,
Y = (double)item.AcceptedPredictedValue,
SimulationName = item.SimulationName
});
if ((double)item.AcceptedObservedValue > maxXYValue)
{
maxXYValue = (double)item.AcceptedObservedValue;
}
if ((double)item.AcceptedPredictedValue > maxXYValue)
{
maxXYValue = (double)item.AcceptedPredictedValue;
}
}
if ((item.CurrentObservedValue != null) && (item.CurrentPredictedValue != null))
{
currentData.Add(new PODataPoint()
{
X = (double)item.CurrentObservedValue,
Y = (double)item.CurrentPredictedValue,
SimulationName = item.SimulationName
});
if ((double)item.CurrentObservedValue > maxXYValue)
{
maxXYValue = (double)item.CurrentObservedValue;
}
if ((double)item.CurrentPredictedValue > maxXYValue)
{
maxXYValue = (double)item.CurrentPredictedValue;
}
}
}
if ((currentData.Count > 0) || (acceptedData.Count > 0))
{
chartNo += 1;
textAnnotation = GetPredictedObservedTests(currPODetails.ID, holdVariable);
BindCurrentAcceptedChart(chartNo, holdVariable, currPODetails.ID, tooltip, currentData.ToArray(), acceptedData.ToArray(), maxXYValue, textAnnotation);
}
}
public async Task <IHttpActionResult> PostApsimFile(ApsimFile apsimfile)
{
int ApsimID = 0;
string ErrMessageHelper = string.Empty;
Utilities.WriteToLogFile(" ");
Utilities.WriteToLogFile("==========================================================");
Utilities.WriteToLogFile("Post Apsim File: Ready to process apsimfile.");
try
{
string connectStr = Utilities.GetConnectionString();
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;
using (SqlConnection sqlCon = new SqlConnection(connectStr))
{
sqlCon.Open();
Utilities.WriteToLogFile(" Checking for existing Pull Requests Details.");
try
{
strSQL = "SELECT COUNT(ID) FROM ApsimFiles WHERE PullRequestId = @PullRequestId AND RunDate != @RunDate";
using (SqlCommand commandES = new SqlCommand(strSQL, sqlCon))
{
commandES.CommandType = CommandType.Text;
commandES.Parameters.AddWithValue("@PullRequestId", apsimfile.PullRequestId);
commandES.Parameters.AddWithValue("@RunDate", apsimfile.RunDate);
//pullRequestCount = (int)commandES.ExecuteScalar();
string response = Comms.SendQuery(commandES, "scalar");
pullRequestCount = JsonConvert.DeserializeObject <int>(response);
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(" ERROR: Checking for existing Pull Requests: " + ex.Message.ToString());
}
if (pullRequestCount > 0)
{
try
{
Utilities.WriteToLogFile(" Removing existing Pull Requests Details.");
using (SqlCommand commandENQ = new SqlCommand("usp_DeleteByPullRequestIdButNotRunDate", sqlCon))
{
// Configure the command and parameter.
commandENQ.CommandType = CommandType.StoredProcedure;
commandENQ.CommandTimeout = 0;
commandENQ.Parameters.AddWithValue("@PullRequestID", apsimfile.PullRequestId);
commandENQ.Parameters.AddWithValue("@RunDate", apsimfile.RunDate);
//commandENQ.ExecuteNonQuery();
Comms.SendQuery(commandENQ, "stored");
}
Utilities.WriteToLogFile(" Removed original Pull Request Data.");
}
catch (Exception ex)
{
Utilities.WriteToLogFile(" ERROR: Error Removing original Pull Request Data: " + ex.Message.ToString());
}
}
//--------------------------------------------------------------------------------------
//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) "
+ " OUTPUT INSERTED.ID Values ("
+ "@PullRequestId, @FileName, @FullFileName, @RunDate, @StatsAccepted, @IsMerged, @SubmitDetails "
+ " )";
using (SqlCommand commandES = new SqlCommand(strSQL, sqlCon))
{
commandES.CommandType = CommandType.Text;
commandES.Parameters.AddWithValue("@PullRequestId", apsimfile.PullRequestId);
commandES.Parameters.AddWithValue("@FileName", apsimfile.FileName);
commandES.Parameters.AddWithValue("@FullFileName", Utilities.GetModifiedFileName(apsimfile.FullFileName));
commandES.Parameters.AddWithValue("@RunDate", apsimfile.RunDate);
commandES.Parameters.AddWithValue("@StatsAccepted", apsimfile.StatsAccepted);
commandES.Parameters.AddWithValue("@IsMerged", apsimfile.IsMerged);
commandES.Parameters.AddWithValue("@SubmitDetails", apsimfile.SubmitDetails);
//this should return the IDENTITY value for this record (which is required for the next update)
ErrMessageHelper = "Filename: " + apsimfile.FileName;
//ApsimID = (int)commandES.ExecuteScalar();
string response = Comms.SendQuery(commandES, "scalar");
ApsimID = JsonConvert.DeserializeObject <int>(response);
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());
}
//--------------------------------------------------------------------------------------
//Add the Simulation Details to the database
//--------------------------------------------------------------------------------------
if (apsimfile.Simulations.Rows.Count > 0)
{
try
{
Utilities.WriteToLogFile(" Inserting Simualtion details for " + apsimfile.FileName);
using (SqlCommand commandENQ = new SqlCommand("usp_SimulationsInsert", sqlCon))
{
commandENQ.CommandType = CommandType.StoredProcedure;
commandENQ.Parameters.AddWithValue("@ApsimID", ApsimID);
commandENQ.Parameters.AddWithValue("@Simulations", apsimfile.Simulations);
//SqlParameter tvpParam = commandENQ.Parameters.AddWithValue("@Simulations", apsimfile.Simulations);
//tvpParam.SqlDbType = SqlDbType.Structured;
//tvpParam.TypeName = "dbo.SimulationDataTableType";
ErrMessageHelper = "- Simualtion Data for " + apsimfile.FileName;
//commandENQ.ExecuteNonQuery();
Comms.SendQuerySP(commandENQ, "storedTableType", "@Simulations", apsimfile.Simulations, "dbo.SimulationDataTableType");
Utilities.WriteToLogFile(string.Format(" Filename {0} Simulation Data imported successfully!", apsimfile.FileName));
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(" ERROR: usp_SimulationsInsert failed: " + ex.Message.ToString());
}
}
//--------------------------------------------------------------------------------------
//Add the Predited Observed Details (MetaData) and then the data
//--------------------------------------------------------------------------------------
//now look at each individual set of data
foreach (PredictedObservedDetails poDetail in apsimfile.PredictedObserved)
{
int predictedObservedID = 0;
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 "
+ " ) OUTPUT INSERTED.ID Values ("
+ " @ApsimFilesID, @TableName, @PredictedTableName, @ObservedTableName, @FieldNameUsedForMatch, @FieldName2UsedForMatch, @FieldName3UsedForMatch, 0 "
+ " )";
using (SqlCommand commandES = new SqlCommand(strSQL, sqlCon))
{
commandES.CommandType = CommandType.Text;
commandES.Parameters.AddWithValue("@ApsimFilesID", ApsimID);
commandES.Parameters.AddWithValue("@TableName", poDetail.DatabaseTableName);
commandES.Parameters.AddWithValue("@PredictedTableName", poDetail.PredictedTableName);
commandES.Parameters.AddWithValue("@ObservedTableName", poDetail.ObservedTableName);
commandES.Parameters.AddWithValue("@FieldNameUsedForMatch", poDetail.FieldNameUsedForMatch);
commandES.Parameters.AddWithValue("@FieldName2UsedForMatch", poDetail.FieldName2UsedForMatch);
commandES.Parameters.AddWithValue("@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 = (int)commandES.ExecuteScalar();
string response = Comms.SendQuery(commandES, "scalar");
predictedObservedID = JsonConvert.DeserializeObject <int>(response);
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());
}
//--------------------------------------------------------------------------------------
//And finally this is where we will insert the actual Predited Observed DATA
//--------------------------------------------------------------------------------------
DataView PredictedObservedView = new DataView(poDetail.PredictedObservedData);
string ObservedColumName, PredictedColumnName;
Utilities.WriteToLogFile(string.Format(" PredictedObserved Data Values for {0}.{1} - import started!", apsimfile.FileName, poDetail.DatabaseTableName));
//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,
for (int i = 0; i < poDetail.PredictedObservedData.Columns.Count; i++)
{
ObservedColumName = poDetail.PredictedObservedData.Columns[i].ColumnName.Trim();
if (ObservedColumName.StartsWith("Observed"))
{
//get the corresponding Predicted Column Name
int dotPosn = ObservedColumName.IndexOf('.');
string valueName = ObservedColumName.Substring(dotPosn + 1);
PredictedColumnName = "Predicted." + valueName;
DataTable selectedData;
try
{
if (poDetail.FieldName3UsedForMatch.Length > 0)
{
selectedData = PredictedObservedView.ToTable(false, "SimulationID", poDetail.FieldNameUsedForMatch, poDetail.FieldName2UsedForMatch, poDetail.FieldName3UsedForMatch, PredictedColumnName, ObservedColumName);
strSQL = "usp_PredictedObservedDataThreeInsert";
}
else if (poDetail.FieldName2UsedForMatch.Length > 0)
{
selectedData = PredictedObservedView.ToTable(false, "SimulationID", poDetail.FieldNameUsedForMatch, poDetail.FieldName2UsedForMatch, PredictedColumnName, ObservedColumName);
strSQL = "usp_PredictedObservedDataTwoInsert";
}
else
{
selectedData = PredictedObservedView.ToTable(false, "SimulationID", poDetail.FieldNameUsedForMatch, PredictedColumnName, ObservedColumName);
strSQL = "usp_PredictedObservedDataInsert";
}
bool validColumn = true;
if (selectedData.Columns[PredictedColumnName].DataType == typeof(string))
{
validColumn = false;
}
if (selectedData.Columns[PredictedColumnName].DataType == typeof(bool))
{
validColumn = false;
}
if (selectedData.Columns[PredictedColumnName].DataType == typeof(DateTime))
{
validColumn = false;
}
if (validColumn == true)
{
using (SqlCommand commandENQ = new SqlCommand(strSQL, sqlCon))
{
commandENQ.CommandType = CommandType.StoredProcedure;
commandENQ.Parameters.AddWithValue("@PredictedObservedID", predictedObservedID);
commandENQ.Parameters.AddWithValue("@ApsimFilesID", ApsimID);
commandENQ.Parameters.AddWithValue("@ValueName", valueName);
if (poDetail.FieldName3UsedForMatch.Length > 0)
{
commandENQ.Parameters.AddWithValue("@MatchName", poDetail.FieldNameUsedForMatch);
commandENQ.Parameters.AddWithValue("@MatchName2", poDetail.FieldName2UsedForMatch);
commandENQ.Parameters.AddWithValue("@MatchName3", poDetail.FieldName3UsedForMatch);
}
else if (poDetail.FieldName2UsedForMatch.Length > 0)
{
commandENQ.Parameters.AddWithValue("@MatchName", poDetail.FieldNameUsedForMatch);
commandENQ.Parameters.AddWithValue("@MatchName2", poDetail.FieldName2UsedForMatch);
}
else
{
commandENQ.Parameters.AddWithValue("@MatchName", poDetail.FieldNameUsedForMatch);
}
commandENQ.Parameters.AddWithValue("@PredictedOabservedData", selectedData);
//SqlParameter tvtpPara = commandENQ.Parameters.AddWithValue("@PredictedOabservedData", selectedData);
//tvtpPara.SqlDbType = SqlDbType.Structured;
string tabletypeName = string.Empty;
if (poDetail.FieldName3UsedForMatch.Length > 0)
{
tabletypeName = "dbo.PredictedObservedDataThreeTableType";
}
else if (poDetail.FieldName2UsedForMatch.Length > 0)
{
tabletypeName = "dbo.PredictedObservedDataTwoTableType";
}
else
{
tabletypeName = "dbo.PredictedObservedDataTableType";
}
ErrMessageHelper = "PredictedObservedDetails Id " + predictedObservedID + ", ValueName: " + valueName;
//commandENQ.ExecuteNonQuery();
//Comms.SendQuery(commandENQ, "stored");
Comms.SendQuerySP(commandENQ, "storedTableType", "@PredictedOabservedData", selectedData, tabletypeName);
Utilities.WriteToLogFile(string.Format(" PredictedObserved Data for {0} import completed successfully!", valueName));
}
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(" ERROR: Unable to import PredictedObserved Data: " + ErrMessageHelper.ToString() + " - " + ex.Message.ToString());
}
} //ObservedColumName.StartsWith("Observed")
} // for (int i = 0; i < poDetail.PredictedObservedData.Columns.Count; i++)
//Need to run the testing procecedure here, and then save the test data
if (poDetail.PredictedObservedData.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(sqlCon, ApsimID, apsimfile, poDetail, predictedObservedID, ref acceptedPredictedObservedDetailsID);
ErrMessageHelper = "Processing Tests.DoValidationTest.";
DataTable dtTests = Tests.DoValidationTest(poDetail.DatabaseTableName, poDetail.PredictedObservedData, acceptedStats);
ErrMessageHelper = "Processing DBFunctions.AddPredictedObservedTestsData.";
DBFunctions.AddPredictedObservedTestsData(sqlCon, 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(sqlCon, acceptedPredictedObservedDetailsID, predictedObservedID);
}
} //foreach (PredictedObservedDetails poDetail in apsimfile.PredictedObserved)
}
return(CreatedAtRoute("DefaultApi", new { id = ApsimID }, apsimfile));
}
catch (Exception ex)
{
Utilities.WriteToLogFile(string.Format(" ERROR in PostApsimFile: {0} - {1}", ErrMessageHelper.ToString(), ex.Message.ToString()));
throw new Exception(string.Format(" ERROR in PostApsimFile: {0} - {1}", ErrMessageHelper.ToString(), ex.Message.ToString()));
}
}
/// <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(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);
}
public List <ApsimFile> GetApsimFile(int id)
{
List <ApsimFile> apsimFiles = new List <ApsimFile>();
string connectStr = Utilities.GetConnectionString();
using (SqlConnection sqlCon = new SqlConnection(connectStr))
{
sqlCon.Open();
string 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", id);
//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);
// }
// apsimFiles.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())
};
apsimFiles.Add(apsim);
}
}
}
return(apsimFiles);
}
/// <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>
/// 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);
}
}
}
/// <summary>
/// THIS IS THE MAIN FUNCTION WITHIN THIS PROGRAM
/// Retreieves all Apsimx simulation files with for the search directory specified in the App.config file
/// and then process these files.
///
/// Returns true iff an error is encountered.
/// </summary>
/// <param name="pullId"></param>
/// <param name="runDate"></param>
/// <param name="submitDetails"></param>
private static bool RetrieveData(int pullId, DateTime runDate, string submitDetails)
{
bool error = false;
//"C:/Jenkins/workspace/1. GitHub pull request/ApsimX/Tests/C:/Jenkins/workspace/1. GitHub pull request/ApsimX/Prototypes/";
//need to allow for "Tests" and "ProtoTypes" directory
//searchDir = @"C:/Users/cla473/Dropbox/APSIMInitiative/ApsimX/Tests/;C:/Users/cla473/Dropbox/APSIMInitiative/ApsimX/Prototypes/;";
string searchDir = ConfigurationManager.AppSettings["searchDirectory"].ToString();
#if DEBUG
searchDir = "C:/ApsimX/Tests/UnderReview/Chickpea";
#endif
string[] filePaths = searchDir.Split(';');
foreach (string filePath in filePaths)
{
string currentPath = filePath.Trim();
DirectoryInfo info = new DirectoryInfo(@currentPath);
//FileInfo[] files = info.GetFiles("*.apsimx", SearchOption.AllDirectories).Where(p => p.CreationTime >= runDate).OrderBy(p => p.CreationTime).ToArray();
FileInfo[] files = info.GetFiles("*.apsimx", SearchOption.AllDirectories).ToArray();
foreach (FileInfo fi in files)
{
try
{
//We don't need to save the full pathing here
WriteToLogFile("--------------------------------");
WriteToLogFile(string.Format("Apsimx file {0} found, Pull Request Id {1}, dated {2}", fi.FullName, pullId, runDate));
ApsimFile apsimFile = new ApsimFile();
apsimFile.FullFileName = fi.FullName;
apsimFile.FileName = Path.GetFileNameWithoutExtension(fi.FullName);
apsimFile.PullRequestId = pullId;
apsimFile.RunDate = runDate;
apsimFile.PredictedObserved = GetPredictedObservedDetails(fi.FullName);
apsimFile.SubmitDetails = submitDetails;
if (apsimFile.PredictedObserved.Count() > 0)
{
apsimFile.Simulations = GetSimulationDataTable(apsimFile.FileName, apsimFile.FullFileName);
try
{
PostApsimRun(apsimFile).Wait();
}
catch (Exception ex)
{
error = true;
WriteToLogFile(string.Format(" ERROR Posting Apsim File: {0}, Pull Request Id {1}, dated {2}: {3}", apsimFile.FileName, pullId, runDate, ex.ToString()));
}
}
}
catch (Exception ex)
{
error = true;
WriteToLogFile(ex.ToString());
}
}
}
#if DEBUG
//We don't need to call Gitub if we are in debug mode (and running local)
//LogFileName = "CsiroApsim";
#endif
//Call the Service to check the status of the Pull Request, (and subsequently call/update GitHub)
//if (LogFileName != "CsiroApsim")
//{
UpdatePullRequestsPassedTestsStatus(pullId).Wait();
//}
return(error);
}
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);
}
}
