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