public ActionResult ImportTable(string projectGuid) { return(ActionUtils.Json(() => { var project = m_dbAdapter.Project.GetProjectByGuid(projectGuid); CheckHandleContactPermission(project); var file = Request.Files[0]; byte[] bytes = new byte[file.InputStream.Length]; file.InputStream.Read(bytes, 0, bytes.Length); file.InputStream.Seek(0, SeekOrigin.Begin); Stream newStream = new MemoryStream(bytes); var tableHeaderRowsCount = ExcelUtils.GetTableHeaderRowsCount(newStream, 0, 0, tableHeader); var table = ExcelUtils.ParseExcel(file.InputStream, 0, tableHeaderRowsCount, 0, 6); var validation = new ExcelValidation(); validation.Add(CellRange.Column(0), new CellTextValidation(1, 50)); Func <string, string> checkEDutyType = (cellText) => { try { CommUtils.ParseEnum <EDutyType>(cellText); } catch { return "无法将[" + cellText + "]解析为[职责]"; } return string.Empty; }; validation.Add(CellRange.Column(1), new CellCustomValidation(checkEDutyType, false)); validation.Add(CellRange.Column(2), new CellTextValidation(0, 30)); validation.Add(CellRange.Column(3), new CellTextValidation(0, 38)); validation.Add(CellRange.Column(3), new CellEmailValidation()); validation.Add(CellRange.Column(4), new CellTelValidation()); validation.Check(table, tableHeaderRowsCount); Func <List <object>, int, int, Contact> ParseRow = (objs, index, projectId) => { return this.ParseRow(objs, index, projectId); }; List <Contact> contacts = ExcelUtils.ParseTable <Contact>(table, project.ProjectId, ParseRow).ToList(); contacts.ForEach(x => m_dbAdapter.Contact.AddContact(x)); var logicModel = Platform.GetProject(project.ProjectGuid); contacts.ForEach(x => logicModel.Activity.Add(project.ProjectId, ActivityObjectType.Contact, x.Guid, "增加机构:" + x.OrganizationName)); return ActionUtils.Success(contacts.Count); })); }
public void TestValidation() { var table = new List <List <object> >(); table.Add(new List <object> { 7657, 1, 2, 3, "2016-09-12" }); table.Add(new List <object> { 0, 1, 2, 3, "2016-09-12" }); table.Add(new List <object> { 0, 1, 2, 3, "2016/09/12" }); table.Add(new List <object> { 0, "123.5", 2, 3, "2016-09-12" }); table.Add(new List <object> { 0, "123.5", 2, 3, "2016-09-12" }); table.Add(new List <object> { 0, "23.5", 2, 3, "2016-09-12" }); table.Add(new List <object> { 0, "23.5", 2, 3, "2016-09-12" }); var validation = new ExcelValidation(); validation.Add(CellRange.Cell(0, 0), new CellTextValidation(0, 10)); validation.Add(CellRange.Column(4), new CellTextValidation(0, 10)); validation.Add(CellRange.Column(1), new CellNumberValidation()); validation.Add(CellRange.Column(4), new CellDateValidation()); validation.Add(CellRange.Row(6), new CellNumberValidation()); var tableHeaderRowsCount = 1; validation.Check(table, tableHeaderRowsCount); }
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 <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 ActionResult ImportTable(string projectSeriesGuid) { return(ActionUtils.Json(() => { var projectSeriesLogicModel = new ProjectSeriesLogicModel(CurrentUserName, projectSeriesGuid); var project = projectSeriesLogicModel.CurrentProject.Instance; CheckPermission(PermissionObjectType.Project, project.ProjectGuid, PermissionType.Write); var file = Request.Files[0]; byte[] bytes = new byte[file.InputStream.Length]; file.InputStream.Read(bytes, 0, bytes.Length); file.InputStream.Seek(0, SeekOrigin.Begin); Stream newStream = new MemoryStream(bytes); var tableHeaderRowsCount = ExcelUtils.GetTableHeaderRowsCount(newStream, 0, 0, tableHeader); var table = ExcelUtils.ParseExcel(file.InputStream, 0, tableHeaderRowsCount, 0, 9); var validation = new ExcelValidation(); validation.Add(CellRange.Column(0), new CellTextValidation(1, 30)); validation.Add(CellRange.Column(1), new CellTextValidation(1, 500)); validation.Add(CellRange.Column(2), new CellTextValidation(1, 30)); validation.Add(CellRange.Column(3), new CellDateValidation()); validation.Add(CellRange.Column(4), new CellDateValidation(false)); //检查工作状态 Func <string, string> checkTaskStatus = (cellText) => { if (!string.IsNullOrWhiteSpace(cellText) && cellText != "-" && cellText != TranslateUtils.ToCnString(TaskStatus.Waitting) && cellText != TranslateUtils.ToCnString(TaskStatus.Overdue)) { return "导入工作的工作状态只能为空、[等待]或[逾期]"; } return string.Empty; }; validation.Add(CellRange.Column(5), new CellCustomValidation(checkTaskStatus)); //检查负责人 int projectId = project.ProjectId; var personInCharges = GetPersonInCharges(projectSeriesLogicModel, projectId); Func <List <string>, string, string> checkPersonInCharge = (personInChargeList, cellText) => { var userName = GetPersonInCharge(cellText); if (!personInChargeList.Contains(userName)) { return "负责人[" + cellText + "]不存在"; } return string.Empty; }; validation.Add(CellRange.Column(6), new CellCustomValidation(checkPersonInCharge, personInCharges)); //不检查只有taskGroup列,其他列均为空的行 var columns = new List <CellRange>() { CellRange.Column(2), CellRange.Column(3), CellRange.Column(4), CellRange.Column(5), CellRange.Column(6), CellRange.Column(7), CellRange.Column(8) }; Func <string, bool> ExceptCondition = (x) => { return x != string.Empty; }; validation.JudgeColumn = new ColumnJudge(columns, ExceptCondition); //检查开始时间是否大于截止时间 Func <object, object, string> lessThanOrEqualTo = (left, right) => { var leftText = left.ToString(); var rightText = right.ToString(); if (!string.IsNullOrWhiteSpace(leftText) && leftText != "-") { var startTime = DateTime.Parse(leftText); var endTime = DateTime.Parse(rightText); if (startTime > endTime) { return "开始时间[" + leftText + "]不能大于结束时间[" + rightText + "]"; } } return string.Empty; }; validation.CompareColumn = new ColumnComparison(CellRange.Column(3), CellRange.Column(4), lessThanOrEqualTo); validation.Check(table, tableHeaderRowsCount); //创建taskGroups List <TaskGroup> newTaskGroups = new List <TaskGroup>(); for (int iRow = 0; iRow < table.Count; iRow++) { var row = table[iRow]; var taskGroup = ParseTaskGroups(newTaskGroups, row, iRow, projectId); if (taskGroup != null) { newTaskGroups.Add(taskGroup); } } var projectLogicModel = new ProjectLogicModel(CurrentUserName, project); var permissionLogicModel = new PermissionLogicModel(projectLogicModel); var projectSeries = projectSeriesLogicModel.Instance; newTaskGroups.ForEach(x => { var taskGroup = m_dbAdapter.TaskGroup.NewTaskGroup(x.ProjectId, x.Name, x.Description); permissionLogicModel.SetPermission(projectSeries, project, taskGroup.Guid, PermissionObjectType.TaskGroup); projectLogicModel.Activity.Add(x.ProjectId, ActivityObjectType.TaskGroup, taskGroup.Guid, "新建工作组:" + taskGroup.Name); }); //创建tasks var taskGroups = m_dbAdapter.TaskGroup.GetByProjectId(projectId); var dicTaskGroups = taskGroups.ToDictionary(x => x.Name); List <Task> tasks = new List <Task>(); for (int iRow = 0; iRow < table.Count; iRow++) { var row = table[iRow].ToList(); if (row.Count > 2 && (!string.IsNullOrWhiteSpace(row[2].ToString())) && row[2].ToString() != "-") { var taskGroup = dicTaskGroups[row[0].ToString()]; tasks.Add(ParseTasks(row, iRow, taskGroup.Id, project)); } } tasks.ForEach(x => m_dbAdapter.Task.NewTask(x)); Dictionary <string, string> msgDic = new Dictionary <string, string>(); foreach (var task in tasks) { var msg = string.Format("创建task:shortCode={0};name={1};startTime={2};endTime={3};personInCharge={4};" + "taskStatus={5};taskTarget={6};taskDetail={7};projectId={8};projectName={9};taskGroupId={10}", task.ShortCode, task.Description, task.StartTime, task.EndTime, task.PersonInCharge, task.TaskStatus, task.TaskTarget, task.TaskDetail, task.ProjectId, task.ProjectName, task.TaskGroupId); msgDic[task.ShortCode] = msg; } tasks.ForEach(x => { permissionLogicModel.SetPermission(projectSeries, project, x.ShortCode, PermissionObjectType.Task); projectLogicModel.Activity.Add(projectId, ActivityObjectType.Task, x.ShortCode, "创建工作:" + x.Description); m_dbAdapter.Project.NewEditProductLog(EditProductType.CreateTask, x.ProjectId, msgDic[x.ShortCode], ""); }); var result = new { taskGroupCount = newTaskGroups.Count, taskCount = tasks.Count }; return ActionUtils.Success(result); })); }
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 <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")); }