protected override void ProcessDataTable(EducationSecurityPrincipal user, DataTable dataTable, ServiceUploadModel model)
{
string studentSISId, subjectName;
Subject subject = new Subject();
int duration;
DateTime? dateAttended;
Student student;
for (int i = StartRow; i < dataTable.Rows.Count; i++)
{
studentSISId = dataTable.Rows[i][1].ToString();
subjectName = dataTable.Rows[i][3].ToString();
student = StudentRepository.Items.Where(s => s.StudentSISId == studentSISId).SingleOrDefault();
subject = RetrieveSubject(subjectName);
if (IsValidServiceOffering(user, i, studentSISId, student, dataTable, out dateAttended, out duration, model, subject))
{
string notes = dataTable.Rows[i][5].ToString();
var studentAssignedOffering = StudentAssignedOfferingRepository.Items.Where(s => s.StudentId == student.Id && s.ServiceOfferingId == model.ServiceOfferingId).FirstOrDefault();
if (studentAssignedOffering == null)
{
studentAssignedOffering = CreateNewStudentAssignedOffering(student, model, user);
}
Create(studentAssignedOffering, (DateTime)dateAttended, subject, duration, notes, user);
model.SuccessfulRowsCount++;
}
model.ProcessedRowCount++;
}
}
private bool IsValidServiceOffering(EducationSecurityPrincipal user, int index, string studentSISId, Student student, DataTable dataTable, out DateTime? startDate, out DateTime? endDate, ServiceUploadModel model)
{
startDate = endDate = null;
if (studentSISId == null || student == null)
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "Malformed Student Id on row {0}", index + 1), model);
return false;
}
else if (!GrantUserAccessToSchedulingAnOffering(user, student))
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "You do not have permission to interact with the referenced student on row {0}", index + 1), model);
return false;
}
else if (!ExcelUtility.TryGetOADate(dataTable.Rows[index][2].ToString(), out startDate))
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "Malformed Start Date on row {0}", index + 1), model);
return false;
}
else if (!ExcelUtility.TryGetOADate(dataTable.Rows[index][3].ToString(), out endDate))
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "Malformed End Date on row {0}", index + 1), model);
return false;
}
return true;
}
protected override void CreateFileUploadErrorRows(DataRow row, ServiceUploadModel model)
{
List<string> rowOfErrors = new List<string>();
rowOfErrors.Add(row[1].ToString());
rowOfErrors.Add(row[2].ToString());
rowOfErrors.Add(row[3].ToString());
rowOfErrors.Add(row[4].ToString());
model.RowErrorValues.Add(new FileRowModel
{
RowErrors = rowOfErrors
});
}
private void CreateNewStudentAssignedOffering(Student student, ServiceUploadModel model, DateTime? startDate, DateTime? endDate, string notes, EducationSecurityPrincipal user)
{
var newOffering = new StudentAssignedOffering()
{
StudentId = student.Id,
CreatingUser = user.Identity.User,
ServiceOfferingId = model.ServiceOfferingId,
StartDate = startDate,
EndDate = endDate,
Notes = notes,
IsActive = true
};
StudentAssignedOfferingRepository.Add(newOffering);
}
protected override void ProcessDataTable(EducationSecurityPrincipal user, DataTable dataTable, ServiceUploadModel model)
{
string studentSISId, notes;
DateTime? startDate, endDate;
Student student;
for (int i = StartRow; i < dataTable.Rows.Count; i++)
{
studentSISId = dataTable.Rows[i][1].ToString();
student = StudentRepository.Items.Where(s => s.StudentSISId == studentSISId).SingleOrDefault();
if (IsValidServiceOffering(user, i, studentSISId, student, dataTable, out startDate, out endDate, model))
{
notes = dataTable.Rows[i][4].ToString();
CreateNewStudentAssignedOffering(student, model, startDate, endDate, notes, user);
model.SuccessfulRowsCount++;
}
model.ProcessedRowCount++;
}
}
private ServiceUploadModel ExecuteFileUploadPosted(int mockedContentLength)
{
DataTable expectedTable = new DataTable();
ServiceUploadModel expectedModel = new ServiceUploadModel();
IServiceOfferingManager tempLogicManager = MockRepository.GenerateMock<IServiceOfferingManager>();
ServiceOfferingController tempTarget = new ServiceOfferingController(tempLogicManager, MockServiceTypeManager, MockProviderManager, MockProgramManager, MockFileProcessor);
tempTarget.ControllerContext = new ControllerContext(MockHttpContext, new RouteData(), tempTarget);
var file = MockRepository.GenerateStub<UploadExcelFileModel>();
file.File = MockRepository.GenerateStub<HttpPostedFileBase>();
file.File.Expect(f => f.ContentLength).Return(mockedContentLength);
file.File.Expect(f => f.ContentType).Return(ExcelWriter.ContentType);
file.File.Expect(f => f.FileName).Return("HappyPath.xlsx");
MockFileProcessor.Expect(m => m.ConsumeFile(file)).Return(expectedTable);
MockFileProcessor.Expect(m => m.Import(User, Path.Combine(UploadTemplateFolderPath, ServiceOfferingController.TemplateFile), expectedTable)).Return(expectedModel);
var result = tempTarget.FileUpload(file) as ViewResult;
return result.AssertGetViewModel(expectedModel);
}
public void WhenUploadFile_ThenFileProcessorConsumesFile_AndResultingDataTableIsImported_AndResultingModelReturnedInViewResult()
{
ServiceUploadModel expected = new ServiceUploadModel();
DataTable expectedTable = new DataTable();
var path = Path.Combine(UploadTemplateFolderPath, ServiceAttendanceController.TemplateFile);
var file = MockRepository.GenerateStub<UploadExcelFileModel>();
file.File = MockRepository.GenerateStub<HttpPostedFileBase>();
file.File.Expect(f => f.ContentLength).Return(1);
MockFileProcessor.Expect(m => m.ConsumeFile(file)).Return(expectedTable);
MockFileProcessor.Expect(m => m.Import(User, path, expectedTable)).Return(expected); ;
ViewResult actual = Target.FileUpload(file) as ViewResult;
actual.AssertGetViewModel(expected);
}
public ActionResult FileUpload(UploadExcelFileModel uploadFile)
{
var model = new ServiceUploadModel();
if (!ModelState.IsValid)
{
foreach (ModelState state in ModelState.Values)
{
foreach (ModelError error in state.Errors)
{
model.RowErrors.Add(error.ErrorMessage);
}
}
return View("FileUploadComplete", model);
}
DataTable dataTable = null;
try
{
dataTable = FileProcessor.ConsumeFile(uploadFile);
}
catch
{
model.RowErrors.Add("Corrupted file format. Please redownload the template and try again.");
model.ProcessedRowCount = model.SuccessfulRowsCount = 0;
return View("FileUploadComplete", model);
}
model = FileProcessor.Import((EducationSecurityPrincipal)User, TemplatePath, dataTable);
return View("FileUploadComplete", model);
}
public void GivenIConsumeAValidServiceUploadFile_ThenTheModelHasNoErrors()
{
var filePath = UploadFileTemplatePath + "HappyPath.xlsx";
var destinationPath = CopyTestFile(filePath);
var model = new ServiceUploadModel();
using (FileStream fs = File.Open(destinationPath, FileMode.Open))
{
var uploadFile = new UploadExcelFileModel();
uploadFile.File = MockRepository.GenerateMock<HttpPostedFileBase>();
uploadFile.File.Expect(u => u.InputStream).Return(fs);
var table = Target.ConsumeFile(uploadFile);
Assert.IsNotNull(model);
Assert.IsTrue(model.RowErrors.Count == 0);
}
DestroyTestFile(destinationPath);
}
public void GivenServiceOfferingDoesNotExist_WhenImportServiceAttendances_ThenModelErrors()
{
ServiceUploadModel model = new ServiceUploadModel();
DataRow row = FileData.NewRow();
row["Id"] = "10";
row["DateAttended"] = "41390";
FileData.Rows.Add(row);
FileData.Rows[1]["Id"] = "0";
ServiceUploadModel actual = Target.Import(User, ServiceAttendanceTemplatePath, FileData);
Assert.AreEqual(1, actual.RowErrors.Count);
Assert.AreEqual(0, actual.SuccessfulRowsCount);
Assert.AreEqual(0, actual.ProcessedRowCount);
}
private void ProcessErrorFile(EducationSecurityPrincipal user, ServiceOffering offering, ServiceUploadModel model, string templatePath)
{
var fileName = string.Format(CultureInfo.InvariantCulture, "{0}{1}{2}", offering.Name.GetSafeFileName(), DateTime.Now.Ticks, ".xlsx");
var blobAddress = string.Format("{0}-{1}-{2}", user.Identity.User.DisplayName, user.Identity.User.Id, fileName);
model.ErrorDownloadFile = new DownloadFileModel
{
FileName = fileName,
BlobAddress = blobAddress
};
var worksheetWriter = CreateWorksheetWriter(offering);
ExcelWriter writer = new ExcelWriter();
writer.InitializeFrom(templatePath, worksheetWriter);
foreach (var error in model.RowErrorValues)
{
worksheetWriter.ErrorRows.Add(error);
}
writer.AppendErrorRows(ServiceOfferingSheetName, worksheetWriter);
writer.Write(BlobClient.CreateContainer(ServiceFileContainerName), model.ErrorDownloadFile.BlobAddress);
}
protected abstract void ProcessDataTable(EducationSecurityPrincipal user, DataTable dataTable, ServiceUploadModel model);
protected abstract void CreateFileUploadErrorRows(DataRow row, ServiceUploadModel model);
public void ProcessError(DataRow row, string rowError, ServiceUploadModel model)
{
model.RowErrors.Add(rowError);
CreateFileUploadErrorRows(row, model);
}
public ServiceUploadModel Import(EducationSecurityPrincipal user, string templatePath, DataTable dataTable)
{
var headerRow = dataTable.Rows[1];
int serviceOfferingId;
ServiceOffering offering = null;
if (int.TryParse(headerRow[1].ToString(), out serviceOfferingId))
{
offering = ServiceOfferingRepository.Items.Include(s => s.Provider).
Include(s => s.ServiceType).
Include(s => s.Program).
SingleOrDefault(s => s.Id == serviceOfferingId);
}
ServiceUploadModel model = new ServiceUploadModel();
if (offering != null && offering.IsActive)
{
serviceOfferingId = int.Parse(headerRow[1].ToString());
if (!GrantUserAccessToUploadOffering(user, offering))
{
throw new EntityAccessUnauthorizedException("Not authorized to schedule service offerings with this provider.");
}
model.ServiceOfferingId = serviceOfferingId;
ProcessDataTable(user, dataTable, model);
RepositoryContainer.Save();
if (model.RowErrors.Any())
{
ProcessErrorFile(user, offering, model, templatePath);
}
}
else
{
model.ProcessedRowCount = model.SuccessfulRowsCount = 0;
model.RowErrors.Add("Invalid Service Offering ID");
}
return model;
}
private StudentAssignedOffering CreateNewStudentAssignedOffering(Student student, ServiceUploadModel model, EducationSecurityPrincipal user)
{
var newOffering = new StudentAssignedOffering();
newOffering.Student = student;
newOffering.StudentId = student.Id;
newOffering.CreatingUser = user.Identity.User;
newOffering.ServiceOfferingId = model.ServiceOfferingId;
newOffering.StartDate = DateTime.Now;
newOffering.CreateTime = DateTime.Now;
newOffering.IsActive = true;
StudentAssignedOfferingRepository.Add(newOffering);
return newOffering;
}
public void GivenIConsumeAValidServiceOfferingUploadFile_ThenTheCorrectDataTableIsReturned()
{
var filePath = UploadFileTemplatePath + "HappyPath.xlsx";
var destinationPath = CopyTestFile(filePath);
var model = new ServiceUploadModel();
DataTable expected = new DataTable();
expected.Columns.Add("blank", typeof(string));
expected.Columns.Add("Id", typeof(string));
expected.Columns.Add("StartDate", typeof(string));
expected.Columns.Add("EndDate", typeof(string));
expected.Columns.Add("Notes", typeof(string));
expected.Columns.Add(new DataColumn());
expected.Columns.Add(new DataColumn());
expected.Columns.Add(new DataColumn());
expected.Columns.Add(new DataColumn());
expected.Columns.Add(new DataColumn());
expected.Columns.Add(new DataColumn());
expected.Columns.Add(new DataColumn());
DataRow row = expected.NewRow();
row["Id"] = "Upload and Assign Students to a Service Offering";
expected.Rows.Add(row);
row = expected.NewRow();
row["Id"] = "1";
row["StartDate"] = "YMCA";
row["EndDate"] = "After School";
expected.Rows.Add(row);
row = expected.NewRow();
row["Id"] = "STUDENT ID";
row["StartDate"] = "START DATE";
row["EndDate"] = "END DATE";
row["Notes"] = "NOTES";
expected.Rows.Add(row);
row = expected.NewRow();
row["Id"] = "10";
row["StartDate"] = "40920";
row["EndDate"] = "40920";
row["Notes"] = "testing";
expected.Rows.Add(row);
row = expected.NewRow();
row["Id"] = "20";
row["StartDate"] = "40962";
row["EndDate"] = "40963";
row["Notes"] = "testing 2";
expected.Rows.Add(row);
using (FileStream fs = File.Open(destinationPath, FileMode.Open))
{
var uploadFile = new UploadExcelFileModel();
uploadFile.File = MockRepository.GenerateMock<HttpPostedFileBase>();
uploadFile.File.Expect(u => u.InputStream).Return(fs);
var actual = Target.ConsumeFile(uploadFile);
CollectionAssert.AreEqual(expected.Rows[0].ItemArray, actual.Rows[0].ItemArray);
CollectionAssert.AreEqual(expected.Rows[1].ItemArray, actual.Rows[1].ItemArray);
CollectionAssert.AreEqual(expected.Rows[2].ItemArray, actual.Rows[2].ItemArray);
CollectionAssert.AreEqual(expected.Rows[3].ItemArray, actual.Rows[3].ItemArray);
CollectionAssert.AreEqual(expected.Rows[4].ItemArray, actual.Rows[4].ItemArray);
}
DestroyTestFile(destinationPath);
}
private bool IsValidServiceOffering(EducationSecurityPrincipal user, int index, string studentSISId, Student student, DataTable dataTable, out DateTime? dateAttended, out int duration, ServiceUploadModel model, Subject subject)
{
dateAttended = null;
duration = 0;
string subjectName = dataTable.Rows[index][3].ToString();
if (studentSISId == null || student == null)
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "Malformed Student Id on row {0}", index + 1), model);
return false;
}
else if (!GrantUserAccessToSchedulingAnOffering(user, student))
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "You do not have permission to interact with the referenced student on row {0}", index + 1), model);
return false;
}
else if (!ExcelUtility.TryGetOADate(dataTable.Rows[index][2].ToString(), out dateAttended))
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "Malformed Date Attended on row {0}", index + 1), model);
return false;
}
else if (subject == null)
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "Malformed Subject on row {0}", index + 1), model);
return false;
}
else if (dataTable.Rows[index][4].ToString() != string.Empty && !int.TryParse(dataTable.Rows[index][4].ToString(), out duration))
{
ProcessError(dataTable.Rows[index], string.Format(CultureInfo.CurrentCulture, "Malformed Duration on row {0}", index + 1), model);
return false;
}
return true;
}
