private async Task EvaluateAnalysisOutput(IServiceScopeFactory serviceScopeFactory, string jobId, int testId, Guid studentId, Guid userId, QueryExecuteRS actualOutput, AnalysisEvaluationType type, IEnumerable <string> solutionFormat)
{
using (var scope = serviceScopeFactory.CreateScope())
{
try
{
var analysisRepository = scope.ServiceProvider.GetService <IAnalysisRepository>();
var ats = analysisRepository.GetEvaluationData(testId, studentId);
#region Record_Start
var setStatusSuccess = analysisRepository.SaveProgress(ats, type, EvaluationProgress.Evaluating, userId);
if (setStatusSuccess)
{
await _analysisSignalR.SendMessageAsync(jobId, "analysisEvaluationUpdate", new
{
id = studentId,
type,
status = (int)EvaluationProgress.Evaluating
});
}
else
{
throw new AnalysisEvaluationException(type, testId, studentId, $"Failed to set status to {EvaluationProgress.Evaluating}");
}
#endregion
#region Prepare_Data
var providedOutputTableName = "";
switch (type)
{
case AnalysisEvaluationType.QueryOutput:
providedOutputTableName = ats.StudentOutputNameOnServer;
break;
case AnalysisEvaluationType.CorrectOutput:
providedOutputTableName = ats.TeacherOutputNameOnServer;
break;
default:
throw new AnalysisEvaluationException(type, testId, studentId, $"Invalid type of test '{type}'");
}
#endregion
#region Evaluate
var conn = _microsoftSQLService.GetReadOnlyAdminConnParams(_sqlServerAdmin.OutputTablesDbName);
var providedOutput = _microsoftSQLService.ReadTable("dbo", providedOutputTableName, conn);
if (providedOutput.Messages.Any())
{
throw new AnalysisEvaluationException(type, testId, studentId, $"Failed to read provided output table '{providedOutputTableName}'");
}
var result = _evaluatorService.EvaluateQueryOutputs(providedOutput, actualOutput, solutionFormat, true);
#endregion
#region Signal_End_And_Save_Result
var saveResultSuccess = analysisRepository.SaveProgress(ats, type, result.PassStatus, userId, result.Message);
if (saveResultSuccess)
{
await _analysisSignalR.SendMessageAsync(jobId, "analysisEvaluationUpdate", new
{
id = studentId,
type,
status = (int)(result.Pass ? EvaluationProgress.Passed : EvaluationProgress.Failed)
});
}
if (!saveResultSuccess)
{
throw new AnalysisEvaluationException(type, testId, studentId, "Failed to save evaluation result");
}
#endregion
}
catch (Exception ex)
{
var logRepo = scope.ServiceProvider.GetService <ILogRepository>();
logRepo.Log(ex);
}
}
}
private async Task EvaluateAnalysisPaper(IServiceScopeFactory serviceScopeFactory, string jobId, int testId, Guid studentId, Guid userId)
{
using (var scope = serviceScopeFactory.CreateScope())
{
try
{
var type = AnalysisEvaluationType.PrepareData;
var analysisRepository = scope.ServiceProvider.GetService <IAnalysisRepository>();
var ats = analysisRepository.GetEvaluationData(testId, studentId);
#region Record_Start
var setStatusSuccess = analysisRepository.SaveProgress(ats, type, EvaluationProgress.Evaluating, userId);
if (setStatusSuccess)
{
await _analysisSignalR.SendMessageAsync(jobId, "analysisEvaluationUpdate", new
{
id = studentId,
type,
status = (int)EvaluationProgress.Evaluating
});
}
else
{
throw new AnalysisEvaluationException(type, testId, studentId, $"Failed to set status to {EvaluationProgress.Evaluating}");
}
#endregion
#region Find_Data
var sts = ats.AnalysisTest.SynthesisTestStudent;
var studentSolutionSql = sts.SqlScript;
var task = sts.SynthesisTest.Task;
var teacherSolutionSql = task.SolutionSqlScript;
var solutionFormat = task.SolutionColumns.Select(sc => sc.ColumnName);
#endregion
#region Get_Outputs
EvaluationResult result = new EvaluationResult()
{
Pass = true, Message = "Data successfully prepared."
};
var studentOutput = _microsoftSQLService.ExecuteSQLAsReadOnlyAdmin(studentSolutionSql, ats.InputNameOnServer);
if (studentOutput.Messages.Any())
{
result = new EvaluationResult()
{
Pass = false, Message = $"Student Sql execution failed with errors:{string.Join(", ", studentOutput.Messages)}"
}
}
;
QueryExecuteRS teacherOutput = null;
if (result.Pass)
{
teacherOutput = _microsoftSQLService.ExecuteSQLAsReadOnlyAdmin(teacherSolutionSql, ats.InputNameOnServer);
if (teacherOutput.Messages.Any())
{
result = new EvaluationResult()
{
Pass = false, Message = $"Failed to execute solution script: '{string.Join(", ", teacherOutput.Messages)}'"
}
}
;
}
#endregion
#region Invoke_New_Jobs
if (result.Pass)
{
_queue.QueueAsyncTask(() => EvaluateAnalysisInput(_serviceScopeFactory, jobId, testId, studentId, userId, studentOutput, teacherOutput, solutionFormat));
analysisRepository.SaveProgress(ats, AnalysisEvaluationType.FailingInput, EvaluationProgress.Queued, userId);
await _analysisSignalR.SendMessageAsync(jobId, "analysisEvaluationUpdate", new
{
id = studentId,
type = (int)AnalysisEvaluationType.FailingInput,
status = (int)EvaluationProgress.Queued
});
_queue.QueueAsyncTask(() => EvaluateAnalysisOutput(_serviceScopeFactory, jobId, testId, studentId, userId, studentOutput, AnalysisEvaluationType.QueryOutput, solutionFormat));
analysisRepository.SaveProgress(ats, AnalysisEvaluationType.QueryOutput, EvaluationProgress.Queued, userId);
await _analysisSignalR.SendMessageAsync(jobId, "analysisEvaluationUpdate", new
{
id = studentId,
type = (int)AnalysisEvaluationType.QueryOutput,
status = (int)EvaluationProgress.Queued
});
_queue.QueueAsyncTask(() => EvaluateAnalysisOutput(_serviceScopeFactory, jobId, testId, studentId, userId, teacherOutput, AnalysisEvaluationType.CorrectOutput, solutionFormat));
analysisRepository.SaveProgress(ats, AnalysisEvaluationType.CorrectOutput, EvaluationProgress.Queued, userId);
await _analysisSignalR.SendMessageAsync(jobId, "analysisEvaluationUpdate", new
{
id = studentId,
type = (int)AnalysisEvaluationType.CorrectOutput,
status = (int)EvaluationProgress.Queued
});
}
#endregion
#region Signal_End_And_Save_Result
var saveResultSuccess = analysisRepository.SaveProgress(ats, type, result.PassStatus, userId, result.Message);
await _analysisSignalR.SendMessageAsync(jobId, "analysisEvaluationUpdate", new
{
id = studentId,
type = (int)AnalysisEvaluationType.PrepareData,
status = result.Pass ? (int)EvaluationProgress.Passed : (int)EvaluationProgress.Failed
});
if (!saveResultSuccess)
{
throw new AnalysisEvaluationException(type, testId, studentId, "Failed to save evaluation result");
}
#endregion
}
catch (Exception ex)
{
var logRepo = scope.ServiceProvider.GetService <ILogRepository>();
logRepo.Log(ex);
}
}
}
private async Task EvaluateAnalysisInput(IServiceScopeFactory serviceScopeFactory, string jobId, int testId, Guid studentId, Guid userId, QueryExecuteRS studentQueryOutput, QueryExecuteRS teacherQueryOutput, IEnumerable <string> solutionFormat)
{
using (var scope = serviceScopeFactory.CreateScope())
{
try
{
var type = AnalysisEvaluationType.FailingInput;
var analysisRepository = scope.ServiceProvider.GetService <IAnalysisRepository>();
var ats = analysisRepository.GetEvaluationData(testId, studentId);
#region Record_Start
var setStatusSuccess = analysisRepository.SaveProgress(ats, type, EvaluationProgress.Evaluating, userId);
if (setStatusSuccess)
{
await _analysisSignalR.SendMessageAsync(jobId, "analysisEvaluationUpdate", new
{
id = studentId,
type,
status = (int)EvaluationProgress.Evaluating
});
}
else
{
throw new AnalysisEvaluationException(type, testId, studentId, $"Failed to set status to {EvaluationProgress.Evaluating}");
}
#endregion
var result = _evaluatorService.EvaluateQueryOutputs(studentQueryOutput, teacherQueryOutput, solutionFormat, false);
#region Record_End
var saveResultSuccess = analysisRepository.SaveProgress(ats, type, result.PassStatus, userId, result.Message);
if (saveResultSuccess)
{
await _analysisSignalR.SendMessageAsync(jobId, "analysisEvaluationUpdate", new
{
id = studentId,
type,
status = (int)(result.Pass ? EvaluationProgress.Passed : EvaluationProgress.Failed)
});
}
if (!saveResultSuccess)
{
throw new AnalysisEvaluationException(type, testId, studentId, "Failed to save evaluation result");
}
#endregion
}
catch (Exception ex)
{
var logRepo = scope.ServiceProvider.GetService <ILogRepository>();
logRepo.Log(ex);
}
}
}
public EvaluationResult EvaluateQueryOutputs(QueryExecuteRS qr1, QueryExecuteRS qr2, IEnumerable <string> expectedFormat, bool shouldMatch)
{
#region Validation
if (expectedFormat.Count() < 1 || expectedFormat.Where(colName => string.IsNullOrEmpty(colName)).Any())
{
return new EvaluationResult()
{
Message = $"Invalid expected result format"
}
}
;
if (qr1.Tables.Count() != 1)
{
return new EvaluationResult()
{
Message = $"Query result 1 returned {qr1.Tables.Count()} tables"
}
}
;
if (qr2.Tables.Count() != 1)
{
return new EvaluationResult()
{
Message = $"Query result 2 returned {qr1.Tables.Count()} tables"
}
}
;
#endregion
var qr1Table = qr1.Tables[0];
var qr2Table = qr2.Tables[0];
#region Validation
if (expectedFormat.Count() != expectedFormat.Distinct().Count())
{
return new EvaluationResult()
{
Message = "Expected column names are not distinct"
}
}
;
if (qr2Table.Columns.Count() != qr2Table.Columns.Distinct().Count())
{
return new EvaluationResult()
{
Message = "Query result 2 table column names that are not distinct"
}
}
;
if (qr1Table.Columns.Count() != qr1Table.Columns.Distinct().Count())
{
return new EvaluationResult()
{
Message = "Query result 1 table column names that are not distinct"
}
}
;
if (expectedFormat.Count() != expectedFormat.Distinct().Count())
{
return new EvaluationResult()
{
Message = "Expected column names are not distinct"
}
}
;
if (qr2Table.Columns.Count() != expectedFormat.Count())
{
return new EvaluationResult()
{
Message = $"QQuery result 2 table returned {qr2Table.Columns.Count()} columns. Expected {expectedFormat.Count()}"
}
}
;
if (qr1Table.Columns.Count() != expectedFormat.Count())
{
return new EvaluationResult()
{
Message = $"Query result 1 table returned {qr1Table.Columns.Count()} columns. Expected {expectedFormat.Count()}"
}
}
;
if (qr2Table.Columns.Where(col => string.IsNullOrEmpty(col.Name)).Any())
{
return new EvaluationResult()
{
Message = $"Query result 2 table contains invalid column names."
}
}
;
if (qr1Table.Columns.Where(col => string.IsNullOrEmpty(col.Name)).Any())
{
return new EvaluationResult()
{
Message = $"Query result 1 table contains invalid column names."
}
}
;
foreach (var colName in expectedFormat)
{
if (qr2Table.Columns.Select(tc => tc.Name).Count(x => x.Equals(colName)) != 1)
{
return new EvaluationResult()
{
Message = $"Query result 2 table does not containt expected column '{colName}'."
}
}
;
if (qr1Table.Columns.Select(sc => sc.Name).Count(x => x.Equals(colName)) != 1)
{
return new EvaluationResult()
{
Message = $"Query result 1 table does not containt expected column '{colName}'."
}
}
;
}
#endregion
var rowDiff = qr1Table.Rows.Count() - qr2Table.Rows.Count();
if (rowDiff != 0)
{
var moreOrLess = rowDiff > 0 ? "more" : "less";
return(new EvaluationResult(false, shouldMatch, $"Query returned {Math.Abs(rowDiff)} records {moreOrLess} than the solution"));
}
var qr2MatchedRowIndexes = new List <int>();
for (int qr1RowIndex = 0; qr1RowIndex < qr1Table.Rows.Count(); qr1RowIndex++)
{
var qr1Row = qr1Table.Rows[qr1RowIndex];
var qr1RowIndexInQr2 = IndexInTable(qr2Table, qr1Row, qr1Table.Columns, expectedFormat, qr2MatchedRowIndexes);
if (qr1RowIndexInQr2 < 0)
{
return(new EvaluationResult(false, shouldMatch, $"Row {qr1RowIndex} not found in solution"));
}
else
{
qr2MatchedRowIndexes.Add(qr1RowIndexInQr2);
}
}
return(new EvaluationResult(true, shouldMatch, "Match"));
}