public async Task <ActionResult> UploadResult(HttpPostedFileBase excelfile)
{
if (excelfile == null || excelfile.ContentLength == 0)
{
ViewBag.Error = "Please Select a excel file <br/>";
return(View());
}
HttpPostedFileBase file = Request.Files["excelfile"];
if (excelfile.FileName.EndsWith("xls") || excelfile.FileName.EndsWith("xlsx"))
{
string lastrecord = "";
int recordCount = 0;
string message = "";
string fileContentType = file.ContentType;
byte[] fileBytes = new byte[file.ContentLength];
var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
// Read data from excel file
using (var package = new ExcelPackage(file.InputStream))
{
var currentSheet = package.Workbook.Worksheets;
foreach (var sheet in currentSheet)
{
ExcelValidation myExcel = new ExcelValidation();
//var workSheet = currentSheet.First();
var noOfCol = sheet.Dimension.End.Column;
var noOfRow = sheet.Dimension.End.Row;
int requiredField = 10;
string validCheck = myExcel.ValidateExcel(noOfRow, sheet, requiredField);
if (!validCheck.Equals("Success"))
{
string[] ssizes = validCheck.Split(' ');
string[] myArray = new string[2];
for (int i = 0; i < ssizes.Length; i++)
{
myArray[i] = ssizes[i];
}
string lineError = $"Please Check sheet {sheet}, Line/Row number {myArray[0]} and column {myArray[1]} is not rightly formatted, Please Check for anomalies ";
//ViewBag.LineError = lineError;
TempData["UserMessage"] = lineError;
TempData["Title"] = "Error.";
return(View());
}
for (int row = 2; row <= noOfRow; row++)
{
string studentId = sheet.Cells[row, 1].Value.ToString().ToUpper().Trim();
string subjectValue = sheet.Cells[row, 2].Value.ToString().ToUpper().Trim();
string termName = sheet.Cells[row, 7].Value.ToString().Trim().ToUpper();
string className = sheet.Cells[row, 10].Value.ToString().Trim().ToUpper();
string sessionName = sheet.Cells[row, 8].Value.ToString().Trim();
var subjectName = Db.Subjects.Where(x => x.SubjectCode.Equals(subjectValue))
.Select(c => c.SubjectId).FirstOrDefault();
var CA = Db.ContinuousAssessments.Where(x => x.ClassName.Equals(className) &&
x.TermName.Contains(termName) &&
x.SessionName.Equals(sessionName) &&
x.StudentId.Equals(studentId) &&
x.SubjectId.Equals(subjectName));
var countFromDb = await CA.CountAsync();
if (countFromDb >= 1)
{
return(View("Error2"));
}
var mycontinuousAssessment = new ContinuousAssessment
{
StudentId = studentId,
SubjectId = subjectName,
FirstTest = double.Parse(sheet.Cells[row, 3].Value.ToString().Trim()),
SecondTest = double.Parse(sheet.Cells[row, 4].Value.ToString().Trim()),
ThirdTest = double.Parse(sheet.Cells[row, 5].Value.ToString().Trim()),
ExamScore = double.Parse(sheet.Cells[row, 6].Value.ToString().Trim()),
TermName = termName,
SessionName = sessionName,
StaffName = sheet.Cells[row, 9].Value.ToString().Trim().ToUpper(),
ClassName = className,
SchoolId = userSchool
//SubjectCategory = mysubjectCategory
};
Db.ContinuousAssessments.Add(mycontinuousAssessment);
recordCount++;
lastrecord = $"The last Updated record has the Student Id {studentId} and Subject Name is {subjectName}. Please Confirm!!!";
}
}
}
await Db.SaveChangesAsync();
message = $"You have successfully Uploaded {recordCount} records... and {lastrecord}";
TempData["UserMessage"] = message;
TempData["Title"] = "Success.";
ViewBag.TermName = new SelectList(Db.Terms.AsNoTracking(), "TermName", "TermName");
ViewBag.SessionName = new SelectList(Db.Sessions.AsNoTracking(), "SessionName", "SessionName");
if (User.IsInRole("Teacher"))
{
string name = User.Identity.GetUserName();
var subjectList = Db.AssignSubjectTeachers.AsNoTracking().Where(x => x.StaffName.Equals(name));
ViewBag.SubjectCode = new SelectList(subjectList.AsNoTracking(), "SubjectName", "SubjectName");
ViewBag.ClassName = new SelectList(subjectList.AsNoTracking(), "ClassName", "ClassName");
}
else
{
ViewBag.ClassName = new SelectList(Db.Classes.AsNoTracking(), "FullClassName", "FullClassName");
ViewBag.SubjectCode = new SelectList(Db.Subjects.AsNoTracking(), "CourseName", "CourseName");
}
return(View());
}
else
{
ViewBag.Error = "File type is Incorrect <br/>";
return(View());
}
}
public async Task <ActionResult> UpLoadStudent(HttpPostedFileBase excelfile)
{
if (excelfile == null || excelfile.ContentLength == 0)
{
ViewBag.Error = "Please Select a excel file <br/>";
return(View("UpLoadStudent"));
}
else
{
HttpPostedFileBase file = Request.Files["excelfile"];
if (excelfile.FileName.EndsWith("xls") || excelfile.FileName.EndsWith("xlsx"))
{
string lastrecord = "";
int recordCount = 0;
string message = "";
string fileContentType = file.ContentType;
byte[] fileBytes = new byte[file.ContentLength];
var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
// Read data from excel file
using (var package = new ExcelPackage(file.InputStream))
{
ExcelValidation myExcel = new ExcelValidation();
var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First();
var noOfCol = workSheet.Dimension.End.Column;
var noOfRow = workSheet.Dimension.End.Row;
int requiredField = 13;
string validCheck = myExcel.ValidateExcel(noOfRow, workSheet, requiredField);
if (!validCheck.Equals("Success"))
{
//string row = "";
//string column = "";
string[] ssizes = validCheck.Split(' ');
string[] myArray = new string[2];
for (int i = 0; i < ssizes.Length; i++)
{
myArray[i] = ssizes[i];
// myArray[i] = ssizes[];
}
string lineError = $"Line/Row number {myArray[0]} and column {myArray[1]} is not rightly formatted, Please Check for anomalies ";
//ViewBag.LineError = lineError;
TempData["UserMessage"] = lineError;
TempData["Title"] = "Error.";
return(View());
}
for (int row = 2; row <= noOfRow; row++)
{
string studentId = workSheet.Cells[row, 1].Value.ToString().Trim();
string firstName = workSheet.Cells[row, 2].Value.ToString().Trim();
string middleName = workSheet.Cells[row, 3].Value.ToString().Trim();
string lastName = workSheet.Cells[row, 4].Value.ToString().Trim();
string gender = workSheet.Cells[row, 5].Value.ToString().Trim();
DateTime dateOfBirth = DateTime.Parse(workSheet.Cells[row, 6].Value.ToString().Trim());
string placeofBirth = workSheet.Cells[row, 7].Value.ToString().Trim();
string state = workSheet.Cells[row, 8].Value.ToString().Trim();
string religion = workSheet.Cells[row, 9].Value.ToString().Trim();
string tribe = workSheet.Cells[row, 10].Value.ToString().Trim();
DateTime addmision = DateTime.Parse(workSheet.Cells[row, 11].Value.ToString().Trim());
string phoneNumber = workSheet.Cells[row, 12].Value.ToString().Trim();
string password = workSheet.Cells[row, 13].Value.ToString().Trim();
string username = lastName.Trim() + " " + firstName.Trim();
try
{
var student = new Student()
{
StudentId = studentId,
FirstName = firstName,
MiddleName = middleName,
LastName = lastName,
PhoneNumber = phoneNumber,
Gender = gender,
Religion = religion,
PlaceOfBirth = placeofBirth,
StateOfOrigin = state,
Tribe = tribe,
DateOfBirth = dateOfBirth,
AdmissionDate = addmision
};
Db.Students.Add(student);
recordCount++;
lastrecord =
$"The last Updated record has the Last Name {lastName} and First Name {firstName} with Phone Number {phoneNumber}";
}
catch (Exception e)
{
message = $"You have successfully Uploaded {recordCount} records... and {lastrecord}";
TempData["UserMessage"] = message;
TempData["Title"] = "Success.";
return(View("Error3"));
}
}
await Db.SaveChangesAsync();
message = $"You have successfully Uploaded {recordCount} records... and {lastrecord}";
TempData["UserMessage"] = message;
TempData["Title"] = "Success.";
return(RedirectToAction("Index", "Students"));
}
}
else
{
ViewBag.Error = "File type is Incorrect <br/>";
return(View("UploadStudent"));
}
}
}
public async Task <IActionResult> UploadEmployeeRecords(IFormFile excelfile)
{
if (excelfile == null || excelfile.Length == 0)
{
ViewBag.Error = "Please Select a excel file <br/>";
TempData["UserMessage"] = "Please Select a excel file.";
TempData["Title"] = "Error.";
return(View(nameof(Index)));
}
IFormFile file = Request.Form.Files["excelfile"];
if (excelfile.FileName.EndsWith("xls") || excelfile.FileName.EndsWith("xlsx"))
{
string lastrecord = "";
int recordCount = 0;
string message = "";
string fileContentType = file.ContentType;
byte[] fileBytes = new byte[file.Length];
var data = file.OpenReadStream();
using (var package = new ExcelPackage(file.OpenReadStream()))
{
ExcelValidation myExcel = new ExcelValidation();
var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First();
var noOfCol = workSheet.Dimension.End.Column;
var noOfRow = workSheet.Dimension.End.Row;
int requiredField = 4;
string validCheck = myExcel.ValidateExcel(noOfRow, workSheet, requiredField);
if (!validCheck.Equals("Success"))
{
//string row = "";
//string column = "";
string[] ssizes = validCheck.Split(' ');
string lineError = $"Line/Row number {ssizes[0]} and column {ssizes[1]} is not rightly formatted, Please Check for anomalies ";
//ViewBag.LineError = lineError;
ViewBag.Message = lineError;
RedirectToAction(nameof(Create));
}
var studentId = DateTime.Now.Ticks;
for (int row = 2; row <= noOfRow; row++)
{
string firstName = workSheet.Cells[row, 1].Value.ToString().Trim();
string lastName = workSheet.Cells[row, 2].Value.ToString().Trim();
string mobileNumber = workSheet.Cells[row, 3].Value.ToString().Trim();
string department = workSheet.Cells[row, 4].Value.ToString().Trim();
var dept = _dept?.SearchDepartment(department);
var generateNumber = new EmployeeRepo();
try
{
var employee = new Employee()
{
FirstName = firstName,
LastName = lastName,
MobileNumber = mobileNumber,
Department = dept,
EmployeeNumber = generateNumber.GenerateEmployeeNumber(dept.Name)
};
_context.Employees.Add(employee);
recordCount++;
// lastrecord = $"The last Updated record has the Last Name {student.LastName} and First Name {student.FirstName} with Student Id {student.StudentId}";
}
catch (Exception ex)
{
ViewBag.ErrorInfo = "Please Leave no column or row Empty/Blank";
ViewBag.ErrorMessage = ex.Message;
return(View("ErrorException"));
}
}
await _context.SaveChangesAsync();
message = $"You have successfully Uploaded {recordCount} records... and {lastrecord}";
TempData["UserMessage"] = message;
TempData["Title"] = "Success.";
}
return(RedirectToAction("Index", "Employees"));
}
return(View());
}
public async Task <ActionResult> UpLoadGuardian(HttpPostedFileBase excelfile)
{
if (excelfile == null || excelfile.ContentLength == 0)
{
ViewBag.Error = "Please Select a excel file <br/>";
return(View("UpLoadGuardian"));
}
HttpPostedFileBase file = Request.Files["excelfile"];
if (excelfile.FileName.EndsWith("xls") || excelfile.FileName.EndsWith("xlsx"))
{
string lastrecord = "";
int recordCount = 0;
string message = "";
string fileContentType = file.ContentType;
byte[] fileBytes = new byte[file.ContentLength];
var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
// Read data from excel file
using (var package = new ExcelPackage(file.InputStream))
{
ExcelValidation myExcel = new ExcelValidation();
var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First();
var noOfCol = workSheet.Dimension.End.Column;
var noOfRow = workSheet.Dimension.End.Row;
int requiredField = 16;
string validCheck = myExcel.ValidateExcel(noOfRow, workSheet, requiredField);
if (!validCheck.Equals("Success"))
{
//string row = "";
//string column = "";
string[] ssizes = validCheck.Split(' ');
string[] myArray = new string[2];
for (int i = 0; i < ssizes.Length; i++)
{
myArray[i] = ssizes[i];
// myArray[i] = ssizes[];
}
string lineError = $"Line/Row number {myArray[0]} and column {myArray[1]} is not rightly formatted, Please Check for anomalies ";
//ViewBag.LineError = lineError;
TempData["UserMessage"] = lineError;
TempData["Title"] = "Error.";
return(View());
}
for (int row = 2; row <= noOfRow; row++)
{
try
{
string studentId = workSheet.Cells[row, 1].Value.ToString().Trim();
string salutation = workSheet.Cells[row, 2].Value.ToString().Trim();
string firstName = workSheet.Cells[row, 3].Value.ToString().Trim();
string middleName = workSheet.Cells[row, 4].Value.ToString().Trim();
string lastName = workSheet.Cells[row, 5].Value.ToString().Trim();
string email = workSheet.Cells[row, 6].Value.ToString().Trim();
string gender = workSheet.Cells[row, 7].Value.ToString().Trim();
string phoneNumber = workSheet.Cells[row, 8].Value.ToString().Trim();
string religion = workSheet.Cells[row, 9].Value.ToString().Trim();
string address = workSheet.Cells[row, 10].Value.ToString().Trim();
string occupation = workSheet.Cells[row, 11].Value.ToString().Trim();
string relationship = workSheet.Cells[row, 12].Value.ToString().Trim();
string lga = workSheet.Cells[row, 13].Value.ToString().Trim();
string state = workSheet.Cells[row, 14].Value.ToString().Trim();
string motherName = workSheet.Cells[row, 15].Value.ToString().Trim();
string mothermaidenName = workSheet.Cells[row, 16].Value.ToString().Trim();
var guardian = new Guardian()
{
StudentId = studentId,
Salutation = salutation.Trim(),
FirstName = firstName.Trim(),
MiddleName = middleName.Trim(),
LastName = lastName.Trim(),
Gender = gender.Trim(),
Address = address.Trim(),
PhoneNumber = phoneNumber.Trim(),
Email = email.Trim(),
Relationship = relationship.Trim(),
Occupation = occupation.Trim(),
Religion = religion,
LGAOforigin = lga,
StateOfOrigin = state,
MotherName = motherName,
MotherMaidenName = mothermaidenName
};
Db.Guardians.Add(guardian);
recordCount++;
lastrecord =
$"The last Updated record has the Last Name {lastName} and First Name {firstName} with Phone Number {phoneNumber}";
}
catch (Exception e)
{
return(View("Error3"));
}
await Db.SaveChangesAsync();
message = $"You have successfully Uploaded {recordCount} records... and {lastrecord}";
TempData["UserMessage"] = message;
TempData["Title"] = "Success.";
}
return(RedirectToAction("Index", "Guardians"));
}
}
ViewBag.Error = "File type is Incorrect <br/>";
return(View("UpLoadGuardian"));
}