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);
                }
            }
        }
Example #4
0
        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"));
        }