public JsonResult GetTestStatusSelectList() { var statusList = new List <SelectListItem>(); var list = TestStatuses.GetTestStatusesList(); foreach (var s in list) { statusList.Add(new SelectListItem() { Value = s, Text = s }); } var statusSelectList = new SelectList(statusList, "Value", "Text"); return(Json(statusSelectList)); }
public async Task <IActionResult> Import([Bind("ProjectID, DataFile")] ImportTestCaseViewModel importModel) { if (ModelState.IsValid) { if (!CanWrite(User.Identity.Name, importModel.ProjectID)) { return(Forbid()); } using (var stream = importModel.DataFile.OpenReadStream()) { using (var excelPackage = new ExcelPackage(stream)) { var workSheet = excelPackage.Workbook.Worksheets["TestCase"]; if (workSheet == null) { ModelState.AddModelError("", "Sheet 'TestCase' is not found."); return(View(importModel)); } int rowCount = workSheet.Dimension.Rows + 1; List <TestCaseViewModel> inputTestCaseList = new List <TestCaseViewModel>(); for (int i = 2; i < rowCount; i++) { var inputTestCase = new TestCaseViewModel() { Index = i, ScenarioID = workSheet.Cells[i, 10].GetValue <string>(), VariantName = workSheet.Cells[i, 1].GetValue <string>(), TestCase = new TestCase() { ID = workSheet.Cells[i, 11].GetValue <string>(), TestCaseName = workSheet.Cells[i, 2].GetValue <string>(), TestCaseSteps = workSheet.Cells[i, 3].GetValue <string>(), ExpectedResult = workSheet.Cells[i, 4].GetValue <string>(), ActualResult = workSheet.Cells[i, 5].GetValue <string>(), TestStatus = workSheet.Cells[i, 6].GetValue <string>(), Note = workSheet.Cells[i, 7].GetValue <string>(), TesterName = workSheet.Cells[i, 8].GetValue <string>(), PlannedDate = workSheet.Cells[i, 9].GetValue <DateTime>().Date } }; inputTestCaseList.Add(inputTestCase); } int errorCount = 0; var newVariantList = new List <TestVariant>(); var newTestCaseList = new List <TestCase>(); var updatedTestCaseList = new List <TestCase>(); var inputScenarioIDList = inputTestCaseList.Select(i => new { i.ScenarioID }).Distinct().ToList(); var existingScenarioList = await(from s in _context.TestScenario.Where(i => i.ProjectID.Equals(importModel.ProjectID, StringComparison.OrdinalIgnoreCase)) join iS in inputScenarioIDList on s.ID equals iS.ScenarioID select s).ToListAsync(); var inputVariantIDList = inputTestCaseList.Select(i => new { i.ScenarioID, i.VariantName }).Distinct().ToList(); var existingVariantList = await(from v in _context.TestVariant join iS in existingScenarioList on v.ScenarioID equals iS.ID join iv in inputVariantIDList on new { v.ScenarioID, v.VariantName } equals new { iv.ScenarioID, iv.VariantName } select v).ToListAsync(); var inputTestCaseIDList = inputTestCaseList.Select(i => new { i.TestCase.ID }).Distinct().ToList(); var existingTestCaseList = await(from c in _context.TestCase join ic in inputTestCaseIDList on c.ID equals ic.ID select c).ToListAsync(); foreach (var inputTestCase in inputTestCaseList) { string errorMessage = string.Empty; if (string.IsNullOrEmpty(inputTestCase.VariantName)) { errorCount++; errorMessage += "TestVariant is required. "; } if (string.IsNullOrEmpty(inputTestCase.TestCase.TestCaseName)) { errorCount++; errorMessage += "TestCaseName is required. "; } if (string.IsNullOrEmpty(inputTestCase.TestCase.TestCaseSteps)) { errorCount++; errorMessage += "TestCaseSteps is required. "; } if (string.IsNullOrEmpty(inputTestCase.TestCase.ExpectedResult)) { errorCount++; errorMessage += "ExpectedResult is required. "; } if (string.IsNullOrEmpty(inputTestCase.TestCase.TesterName)) { errorCount++; errorMessage += "TesterName is required. "; } var minDate = new DateTime(2000, 1, 1); if (inputTestCase.TestCase.PlannedDate < minDate) { errorCount++; errorMessage += $"PlannedDate must be a Date value >= {minDate.ToString("yyyy/MM/dd")}. "; } if (!TestStatuses.GetTestStatusesList().Contains(inputTestCase.TestCase.TestStatus)) { errorCount++; errorMessage += $"TestStatus must be {TestStatuses.GetTestStatusesString()}. "; } if (string.IsNullOrEmpty(inputTestCase.ScenarioID)) { errorCount++; errorMessage += "ScenarioID is required. "; } else { if (!existingScenarioList.Any(c => c.ID.Equals(inputTestCase.ScenarioID, StringComparison.OrdinalIgnoreCase))) { errorCount++; errorMessage += "ScenarioID is invalid. "; } } TestCase updatedTestCase = null; if (!string.IsNullOrEmpty(inputTestCase.TestCase.ID)) { updatedTestCase = existingTestCaseList.FirstOrDefault(c => c.ID.Equals(inputTestCase.TestCase.ID, StringComparison.OrdinalIgnoreCase)); if (updatedTestCase == null) { errorCount++; errorMessage += "TestCaseID is invalid. "; } } if (!string.IsNullOrEmpty(errorMessage)) { workSheet.Cells[inputTestCase.Index, 12].Value = errorMessage; } else { var variant = existingVariantList.FirstOrDefault(v => v.VariantName.Equals(inputTestCase.VariantName, StringComparison.OrdinalIgnoreCase) && v.ScenarioID.Equals(inputTestCase.ScenarioID, StringComparison.OrdinalIgnoreCase)); if (variant == null) { variant = newVariantList.FirstOrDefault(v => v.VariantName.Equals(inputTestCase.VariantName, StringComparison.OrdinalIgnoreCase) && v.ScenarioID.Equals(inputTestCase.ScenarioID, StringComparison.OrdinalIgnoreCase)); if (variant == null) { variant = new TestVariant() { ScenarioID = inputTestCase.ScenarioID, VariantName = inputTestCase.VariantName }; variant.SetCreator(User.Identity.Name); newVariantList.Add(variant); } } if (updatedTestCase != null) { updatedTestCase.TestVariantID = variant.ID; updatedTestCase.TestCaseName = inputTestCase.TestCase.TestCaseName; updatedTestCase.TestCaseSteps = inputTestCase.TestCase.TestCaseSteps; updatedTestCase.ExpectedResult = inputTestCase.TestCase.ExpectedResult; updatedTestCase.ActualResult = inputTestCase.TestCase.ActualResult; updatedTestCase.TestStatus = inputTestCase.TestCase.TestStatus; updatedTestCase.Note = inputTestCase.TestCase.Note; updatedTestCase.TesterName = inputTestCase.TestCase.TesterName; updatedTestCase.PlannedDate = inputTestCase.TestCase.PlannedDate; updatedTestCase.SetUpdater(User.Identity.Name); updatedTestCaseList.Add(updatedTestCase); } else { var newTestCase = new TestCase() { TestVariantID = variant.ID, TestCaseName = inputTestCase.TestCase.TestCaseName, TestCaseSteps = inputTestCase.TestCase.TestCaseSteps, ExpectedResult = inputTestCase.TestCase.ExpectedResult, ActualResult = inputTestCase.TestCase.ActualResult, TestStatus = inputTestCase.TestCase.TestStatus, Note = inputTestCase.TestCase.Note, TesterName = inputTestCase.TestCase.TesterName, PlannedDate = inputTestCase.TestCase.PlannedDate }; newTestCase.SetCreator(User.Identity.Name); newTestCaseList.Add(newTestCase); } } } if (errorCount > 0) { return(File(excelPackage.GetAsByteArray(), importModel.DataFile.ContentType, importModel.DataFile.FileName)); } await _context.AddRangeAsync(newVariantList); _context.UpdateRange(updatedTestCaseList); await _context.AddRangeAsync(newTestCaseList); await _context.SaveChangesAsync(); } } return(RedirectToAction(nameof(Index), new { ProjectID = importModel.ProjectID })); } return(View(importModel)); }