/// <summary>
/// Get a list of new predicted/observed tables added by a pull request.
/// The returned table names will be of the form "FileName.TableName".
/// </summary>
/// <param name="pullRequestId">Pull request ID.</param>
/// <param name="acceptedPullRequestId">ID of the pull request of the accepted stats.</param>
public static List <string> GetNewTables(int pullRequestId, int acceptedPullRequestId)
{
string returnStr = string.Empty;
using (ApsimDBContext context = new ApsimDBContext())
{
string strsql = $@"SELECT curr.[Filename]
FROM (
SELECT 'currentPO' as 'Source', a1.[FileName] + '.' + pod1.[TableName] as [FileName]
FROM [dbo].[ApsimFiles] a1
INNER JOIN [dbo].[PredictedObservedDetails] pod1 on a1.ID = pod1.ApsimFilesID
WHERE a1.[PullRequestId] = {pullRequestId}
) as curr
LEFT OUTER JOIN (
SELECT 'acceptedPO' as 'Source', a1.[FileName] + '.' + pod1.[TableName] as [FileName]
FROM[dbo].[ApsimFiles] a1
INNER JOIN [dbo].[PredictedObservedDetails] pod1 on a1.ID = pod1.ApsimFilesID
WHERE a1.[PullRequestId] = {acceptedPullRequestId}
) AS acc
ON curr.[FileName] = acc.[FileName]
WHERE acc.[FileName] IS NULL;";
return(context.Database.SqlQuery <string>(strsql).ToList());
}
}
public static List <vSimulationPredictedObserved> GetCurrentAcceptedSimulationValues(int currentPoID, int acceptedPoId)
{
using (ApsimDBContext context = new ApsimDBContext())
{
SqlParameter param1 = new SqlParameter("@CurrentPredictedObservedId", currentPoID);
SqlParameter param2 = new SqlParameter("@AcceptedPredictedObservedId", acceptedPoId);
var results = context.Database
.SqlQuery <vSimulationPredictedObserved>("usp_GetCurrentAcceptedSimulationValues @CurrentPredictedObservedId, @AcceptedPredictedObservedId", param1, param2)
.Select(v => new vSimulationPredictedObserved
{
ID = v.ID,
MatchNames = v.MatchNames,
MatchValues = v.MatchNames,
ValueName = v.ValueName,
CurrentPredictedValue = (double?)v.CurrentPredictedValue,
CurrentObservedValue = (double?)v.CurrentObservedValue,
AcceptedPredictedValue = (double?)v.AcceptedPredictedValue,
AcceptedObservedValue = (double?)v.AcceptedObservedValue,
SimulationName = v.SimulationName,
SimulationsID = (int)v.ID
})
.ToList();
return(results);
}
}
/// <summary>
/// Gets Details of the individual Apsim simulation Files that make up this specified Pull Request and Run Date
/// NOTE: No longer in use as can no longer have multiple pull requests with different dates.
/// </summary>
/// <param name="pullRequestId"></param>
/// <param name="runDate"></param>
/// <returns></returns>
public static List <vSimFile> GetSimFilesByPullRequestIDandDate(int pullRequestId, DateTime runDate)
{
using (ApsimDBContext context = new ApsimDBContext())
{
string pathStr = @"C:\Jenkins\workspace\1. GitHub pull request\ApsimX\Tests\Validation\Maize\Maize.apsimx";
int posn = pathStr.IndexOf(@"ApsimX\Tests");
posn += 7;
return(context.ApsimFiles.Join(context.PredictedObservedDetails,
af => af.ID,
pod => pod.ApsimFilesID,
(af, pod) => new { ApsimFiles = af, PredictedObservedDetails = pod })
.Where(sf => sf.ApsimFiles.PullRequestId == pullRequestId && sf.ApsimFiles.RunDate == runDate)
.Select(sf => new vSimFile
{
PullRequestId = sf.ApsimFiles.PullRequestId,
FileName = sf.ApsimFiles.FileName,
FullFileName = ((sf.ApsimFiles.FullFileName.Contains("GitHub")) ? sf.ApsimFiles.FullFileName.Substring(posn) : sf.ApsimFiles.FullFileName),
SubmitDetails = sf.ApsimFiles.SubmitDetails,
PredictedObservedID = sf.PredictedObservedDetails.ID,
strPredictedObservedID = sf.PredictedObservedDetails.ID.ToString(),
PredictedObservedTableName = sf.PredictedObservedDetails.TableName,
PassedTests = sf.PredictedObservedDetails.PassedTests,
AcceptedPredictedObservedDetailsID = sf.PredictedObservedDetails.AcceptedPredictedObservedDetailsID
})
.OrderBy(sf => sf.FileName)
.ToList());
}
}
/// <summary>
/// Returns the PredictedObservedValues and corresponding Simulation details for a specific PredictedObserved Table and variable
/// </summary>
/// <param name="predictedObservedId"></param>
/// <param name="variable"></param>
/// <returns></returns>
//public static List<vSimulationPredictedObserved> GetByPredictedObservedIdAndVariable(int predictedObservedId, string variable)
//{
// //TODO: Convert this to Indexed View in Sql Server
// using (ApsimDBContext context = new ApsimDBContext())
// {
// SqlParameter param1 = new SqlParameter("@PredictedObservedId", predictedObservedId);
// SqlParameter param2 = new SqlParameter("@ValueName", variable);
// var results = context.Database
// .SqlQuery<vSimulationPredictedObserved>("usp_GetByPredictedObservedIdAndVariable @PredictedObservedId, @ValueName",
// param1, param2).ToList();
// return results;
// }
//}
/// <summary>
/// This takes two PredictedObservedDetails ID's, and returns a comparison of the values for both sets of PredictedObservedValues.
/// This is used to compare the 'Current' and 'Accepted' Values for a specific variable.
/// </summary>
/// <param name="variable"></param>
/// <param name="currentPoID"></param>
/// <param name="acceptedPoId"></param>
/// <returns></returns>
//public static List<vCurrentAndAccepted> GetCurrentAcceptedValues(string variable, int currentPoID, int acceptedPoId)
//{
// using (ApsimDBContext context = new ApsimDBContext())
// {
// SqlParameter param1 = new SqlParameter("@CurrentPredictedObservedId", currentPoID);
// SqlParameter param2 = new SqlParameter("@AcceptedPredictedObservedId", acceptedPoId);
// SqlParameter param3 = new SqlParameter("@ValueName", variable);
// var results = context.Database
// .SqlQuery<vCurrentAndAccepted>("usp_GetCurrentAcceptedValues @CurrentPredictedObservedId, @AcceptedPredictedObservedId, @ValueName",
// param1, param2, param3).ToList();
// return results;
// }
//}
public static List <vCurrentAndAccepted> GetCurrentAcceptedValuesWithNulls(string variable, int currentPoID, int acceptedPoId)
{
using (ApsimDBContext context = new ApsimDBContext())
{
SqlParameter param1 = new SqlParameter("@CurrentPredictedObservedId", currentPoID);
SqlParameter param2 = new SqlParameter("@AcceptedPredictedObservedId", acceptedPoId);
SqlParameter param3 = new SqlParameter("@ValueName", variable);
var results = context.Database
.SqlQuery <vCurrentAndAccepted>("usp_GetCurrentAcceptedValuesWithNulls @CurrentPredictedObservedId, @AcceptedPredictedObservedId, @ValueName",
param1, param2, param3)
.Select(v => new vCurrentAndAccepted
{
ID = v.ID,
TableName = v.TableName,
SimulationName = v.SimulationName,
MatchNames = v.MatchNames,
MatchValues = v.MatchNames,
CurrentPredictedValue = (double?)v.CurrentPredictedValue,
CurrentObservedValue = (double?)v.CurrentObservedValue,
AcceptedPredictedValue = (double?)v.AcceptedPredictedValue,
AcceptedObservedValue = (double?)v.AcceptedObservedValue
})
.ToList();
return(results);
}
}
/// <summary>
/// Gets the Predicted Observed Details based on the id (int)
/// </summary>
/// <param name="predictedObservedId"></param>
/// <returns></returns>
public static PredictedObservedDetail GetByPredictedObservedID(int predictedObservedId)
{
using (ApsimDBContext context = new ApsimDBContext())
{
return(context.PredictedObservedDetails
.Where(d => d.ID == predictedObservedId)
.SingleOrDefault());
}
}
//NOTE: Dont forget that these need to have the build property set to compile
/// <summary>
/// Returns details of ApsimFile based on the ID (int) of the record
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static ApsimFile GetByID(int id)
{
using (ApsimDBContext context = new ApsimDBContext())
{
return(context.ApsimFiles
.Where(a => a.ID == id)
.SingleOrDefault());
}
}
//NOTE: Dont forget that these need to have the build property set to compile
/// <summary>
/// Retrieves the Table Name for a Predicted Observed table, based on the Id (int)
/// </summary>
/// <param name="predictedObservedId"></param>
/// <returns></returns>
public static string GetFilenameByPredictedObservedID(int predictedObservedId)
{
using (ApsimDBContext context = new ApsimDBContext())
{
return((from pod in context.PredictedObservedDetails
where (pod.ID == predictedObservedId)
select pod.TableName)
.SingleOrDefault());
}
}
/// <summary>
/// Returns details of ApsimFile (parent record) based on the PredictedObservedDetails ID (int) of the record
/// </summary>
/// <param name="predictedObservedId"></param>
/// <returns></returns>
public static ApsimFile GetByPredictedObservedId(int predictedObservedId)
{
using (ApsimDBContext context = new ApsimDBContext())
{
return((from pod in context.PredictedObservedDetails
join af in context.ApsimFiles on pod.ApsimFilesID equals af.ID
where pod.ID == predictedObservedId
select af)
.SingleOrDefault());
}
}
/// <summary>
/// Returns the lastest (last) Pull Request Id that has been flagged as a release version,
/// excluding the specified Pull Request Id
/// </summary>
/// <param name="currentPullRequestId"></param>
/// <returns></returns>
public static int GetLatestMergedPullRequestId(int currentPullRequestId)
{
using (ApsimDBContext context = new ApsimDBContext())
{
return(context.ApsimFiles
.Where(a => a.StatsAccepted == true && a.PullRequestId != currentPullRequestId)
.OrderByDescending(a => a.RunDate)
.Select(a => a.PullRequestId)
.First());
}
}
/// <summary>
/// This is used tor retrieve the Tests details for a specifice Predicted Observed Id and variable.
/// </summary>
/// <param name="variable"></param>
/// <param name="currentPoID"></param>
/// <returns></returns>
public static List <PredictedObservedTest> GetPredictedObservedTest(int currentPoID, string variable)
{
using (ApsimDBContext context = new ApsimDBContext())
{
return(context.PredictedObservedTests
.Where(v => v.PredictedObservedDetailsID == currentPoID && v.Variable == variable)
.OrderBy(v => v.SortOrder)
.ThenBy(v => v.Test)
.ToList());
}
}
public static List <vPredictedObservedTests> GetCurrentAcceptedTestsFiltered(int pullRequestId)
{
using (ApsimDBContext context = new ApsimDBContext())
{
SqlParameter param1 = new SqlParameter("@PullRequestId", pullRequestId);
//[Test] IN ('RMSE', 'NSE', 'Bias', 'RSR')
var results = context.Database
.SqlQuery <vPredictedObservedTests>("usp_GetPredictedObservedTestsFiltered @PullRequestId", param1)
.ToList();
return(results);
}
}
public static List <vPredictedObservedTests> GetCurrentAcceptedTestsDiffsSubset(int pullRequestId)
{
using (ApsimDBContext context = new ApsimDBContext())
{
SqlParameter param1 = new SqlParameter("@PullRequestId", pullRequestId);
var results = context.Database
.SqlQuery <vPredictedObservedTests>("usp_GetPredictedObservedTestswithDifferences @PullRequestId", param1)
.ToList();
return(results);
}
}
/// <summary>
/// Thes the Predicted Observed Details ID, for a specific Pull Request ID (int), by matching specific
/// PredictedObservedDetails information. This is used when the 'Accepted' PredictedObservedDetailsID is
/// not saved with the 'Current' PredictedObservedDetails record.
/// </summary>
/// <param name="pullRequestId"></param>
/// <param name="filename"></param>
/// <param name="tablename"></param>
/// <param name="predictedTableName"></param>
/// <param name="observedTableName"></param>
/// <param name="fieldNameUsedForMatch"></param>
/// <returns></returns>
public static int GetIDByMatchingDetails(int pullRequestId, string filename, string tablename)
{
using (ApsimDBContext context = new ApsimDBContext())
{
return((from pod in context.PredictedObservedDetails
join af in context.ApsimFiles on pod.ApsimFilesID equals af.ID
where af.PullRequestId == pullRequestId &&
af.FileName == filename &&
pod.TableName == tablename
select pod.ID)
.SingleOrDefault());
}
}
public static List <vPredictedObservedTestsFormatted> GetPredictedObservedTestFormatted(int currentPoID, string variable)
{
using (ApsimDBContext context = new ApsimDBContext())
{
SqlParameter param1 = new SqlParameter("@PredictedObservedId", currentPoID);
SqlParameter param2 = new SqlParameter("@Variable", variable);
var results = context.Database
.SqlQuery <vPredictedObservedTestsFormatted>("usp_GetPredictedObservedTestsFormatted @PredictedObservedId, @Variable", param1, param2)
.ToList();
return(results);
}
}
/// <summary>
/// Get limited details (Pull Request ID, Run Date and Is Merged status) for all Apsim Files
/// in reverse date order
/// </summary>
/// <returns></returns>
//public static List<vApsimFile> GetAllApsimFiles()
//{
// using (ApsimDBContext context = new ApsimDBContext())
// {
// return context.ApsimFiles
// .Select(h => new vApsimFile
// {
// PullRequestId = h.PullRequestId,
// RunDate = h.RunDate,
// SubmitDetails = h.SubmitDetails,
// StatsAccepted = h.StatsAccepted
// })
// .Distinct()
// .OrderByDescending(h => h.RunDate)
// .ThenByDescending(h => h.PullRequestId)
// .ToList();
// }
//}
public static List <vVariable> GetDistinctApsimFileNames()
{
using (ApsimDBContext context = new ApsimDBContext())
{
return(context.ApsimFiles
.Select(v => new vVariable
{
Name = v.FileName,
Value = v.FileName
})
.Distinct()
.OrderBy(v => v.Name)
.ToList());
}
}
public static AcceptStatsLog GetLatestAcceptedStatsLog()
{
using (ApsimDBContext context = new ApsimDBContext())
{
//Need to ignore any records with LogStats = false, as these may have been deleted Pull Requests, or 'Updates (below).
//Need to ignore any records with a StatsPullRequestId, as these mean that the stats were updated to this pull request,
//and they are not a 'Stats' Accepted it.
var acceptStats = context.AcceptStatsLogs
.Where(a => a.LogStatus == true && a.StatsPullRequestId == 0)
.OrderByDescending(a => a.LogAcceptDate)
.ThenByDescending(a => a.PullRequestId)
.FirstOrDefault();
return(acceptStats);
}
}
/// <summary>
/// Returns a list of the Variables being reported on, in the PredictedObservedValues table,
/// for a specific PredictedObservedDetailsID (int)
/// </summary>
/// <param name="predictedObservedId"></param>
/// <returns></returns>
public static List <vVariable> GetVariablesByPredictedObservedID(int predictedObservedId)
{
using (ApsimDBContext context = new ApsimDBContext())
{
return(context.PredictedObservedValues
.Where(v => v.PredictedObservedDetailsID == predictedObservedId)
.Select(v => new vVariable
{
Name = v.ValueName,
Value = v.ValueName
})
.Distinct()
.OrderBy(v => v.Name)
.ToList());
}
}
public static List <vVariable> GetDistinctTableNames(string fileName)
{
using (ApsimDBContext context = new ApsimDBContext())
{
return((from pod in context.PredictedObservedDetails
join af in context.ApsimFiles on pod.ApsimFilesID equals af.ID
where af.FileName == fileName
select new vVariable
{
Name = pod.TableName,
Value = pod.TableName
})
.Distinct()
.OrderBy(v => v.Name)
.ToList());
}
}
/// <summary>
/// This is used tor retrieve the Tests details for a specifice Predicted Observed Id and variable.
/// </summary>
/// <param name="currentPoID"></param>
/// <returns>List<PredictedObservedTest></returns>
public static List <PredictedObservedTest> GetCurrentAcceptedTestsSubset(int currentPoID)
{
List <string> testList = new List <string>();
testList.Add("n");
testList.Add("R2");
testList.Add("RMSE");
testList.Add("NSE");
testList.Add("RSR");
using (ApsimDBContext context = new ApsimDBContext())
{
//modLMC - 24/08/2017 - only retrieve reduced set of stats data (n, R2, RMSE, NSE and RSR) as per email from Dean.
//modLMC - 31/01/2018 - modified to exclude records where both Current and Accepted are null (fudge until 'Service' is udpated to not add them).
return(context.PredictedObservedTests
.Where(v => testList.Contains(v.Test) && v.PredictedObservedDetailsID == currentPoID && !(v.Current == null && v.Accepted == null))
.Distinct()
.OrderBy(v => v.PassedTest)
.ThenByDescending(v => v.DifferencePercent)
.ThenBy(v => v.SortOrder)
.ThenBy(v => v.Test)
.ToList());
}
}
//public static vApsimFile GetLatestAcceptedPullRequestDetails()
//{
// using (ApsimDBContext context = new ApsimDBContext())
// {
// return context.ApsimFiles
// .Where(a => a.StatsAccepted == true)
// .OrderByDescending(a => a.RunDate)
// .ThenByDescending(a => a.PullRequestId)
// .Select(a => new vApsimFile
// {
// PullRequestId = a.PullRequestId,
// RunDate = a.RunDate,
// SubmitDetails = a.SubmitDetails,
// StatsAccepted = a.StatsAccepted
// })
// .First();
// }
//}
/// <summary>
/// Get limited details (Pull Request ID, Run Date and Is Merged status, as well as the number of Apsim Files that
/// make up each Pull Request, and the Percentage of these files that have Passed Tests) for all Apsim Files
/// in reverse date order
/// </summary>
/// <returns></returns>
public static List <vApsimFile> GetPullRequestsWithStatus()
{
using (ApsimDBContext context = new ApsimDBContext())
{
return((from pod in context.PredictedObservedDetails
join af in context.ApsimFiles on pod.ApsimFilesID equals af.ID
select new { pod, af } into t1
group t1 by new { t1.af.PullRequestId, t1.af.RunDate, t1.af.SubmitDetails, t1.af.StatsAccepted } into grp
select new vApsimFile
{
PullRequestId = grp.FirstOrDefault().af.PullRequestId,
RunDate = grp.FirstOrDefault().af.RunDate,
SubmitDetails = grp.FirstOrDefault().af.SubmitDetails,
StatsAccepted = grp.FirstOrDefault().af.StatsAccepted,
PercentPassed = 100 * grp.Count(m => m.pod.PassedTests == 100) / grp.Count(m => m.pod.PassedTests != null),
Total = grp.Count(),
AcceptedPullRequestId = grp.FirstOrDefault().af.AcceptedPullRequestId,
AcceptedRunDate = grp.FirstOrDefault().af.AcceptedRunDate
})
.OrderByDescending(h => h.RunDate)
.ThenByDescending(h => h.PullRequestId)
.ToList());
}
}
