/// <summary>
/// Retrieves the Predicted Observed Values for a specified PredictedObserved Id
/// </summary>
/// <param name="connectStr"></param>
/// <param name="predictedObservedId"></param>
/// <returns></returns>
private static DataTable GetPredictedObservedValues(SqlConnection sqlCon, int predictedObservedId)
{
DataTable dtResults = new DataTable();
try
{
string strSQL = "SELECT * FROM PredictedObservedValues WHERE PredictedObservedDetailsId = @PredictedObservedDetailsId ORDER BY ID DESC";
using (SqlCommand commandER = new SqlCommand(strSQL, sqlCon))
{
commandER.CommandType = CommandType.Text;
commandER.Parameters.AddWithValue("@PredictedObservedDetailsId", predictedObservedId);
//SqlDataReader reader = commandER.ExecuteReader();
//dtResults.Load(reader);
//reader.Close();
string response = Comms.SendQuery(commandER, "reader");
var jsonObject = JsonConvert.DeserializeObject(response);
dtResults = JsonConvert.DeserializeObject <DataTable>(jsonObject.ToString());
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(string.Format("ERROR: Unable to retrieve Predicted Observed Values for PredictedObserved Id {0}: {1}", predictedObservedId.ToString(), ex.Message.ToString()));
}
return(dtResults);
}
/// <summary>
/// Deletes all Data for a specified Pull RequestId
/// </summary>
/// <param name="connectStr"></param>
/// <param name="pullRequestId"></param>
private static void DeleteByPullRequest(SqlConnection sqlCon, int pullRequestId)
{
try
{
using (SqlCommand commandENQ = new SqlCommand("usp_DeleteByPullRequestId", sqlCon))
{
// Configure the command and parameter.
commandENQ.CommandType = CommandType.StoredProcedure;
commandENQ.CommandTimeout = 0;
commandENQ.Parameters.AddWithValue("@PullRequestID", pullRequestId);
//commandENQ.ExecuteNonQuery();
Comms.SendQuery(commandENQ, "stored");
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(string.Format(" ERROR: Unable to remove data for Pull Request Id: {0}: {1}.", pullRequestId, ex.Message.ToString()));
}
}
public IHttpActionResult GetAcceptedStatsStatusAndUpdateGithub(int id)
{
Utilities.WriteToLogFile("-----------------------------------");
double PercentPassed = 0;
bool passed = false;
string connectStr = Utilities.GetConnectionString();
using (SqlConnection sqlCon = new SqlConnection(connectStr))
{
sqlCon.Open();
try
{
string strSQL = "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 [dbo].[ApsimFiles] AS a "
+ " INNER JOIN[dbo].[PredictedObservedDetails] AS p ON a.ID = p.ApsimFilesID "
+ " WHERE a.[PullRequestId] = @PullRequestId ";
using (SqlCommand commandES = new SqlCommand(strSQL, sqlCon))
{
commandES.CommandType = CommandType.Text;
commandES.Parameters.AddWithValue("@PullRequestId", id);
//object obj = commandES.ExecuteScalar();
//PercentPassed = double.Parse(obj.ToString());
string response = Comms.SendQuery(commandES, "scalar");
PercentPassed = JsonConvert.DeserializeObject <double>(response);
}
if (PercentPassed == 100)
{
passed = true;
}
}
catch (Exception ex)
{
Utilities.WriteToLogFile(string.Format("ERROR: Pull Request Id {0}, Unable to determine Passed/Failed status: {1}", id.ToString(), ex.Message.ToString()));;
}
CallGitHubWithPassFail(id, passed);
Utilities.WriteToLogFile(string.Format(" Pull Request Id {0}, PassedTestsStatus verified and Github updated.", id.ToString()));;
}
return(Ok());
}
/// <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);
}
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>
// 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 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()));
}
}
