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