void ImportStudentResultAction_Execute(object sender, DevExpress.ExpressApp.Actions.SimpleActionExecuteEventArgs e)
{
ObjectSpace objectSpace = Application.CreateObjectSpace();
//ObjectSpace objectSpace = ObjectSpaceInMemory.CreateNew();
CollectionSource collectionSource = new CollectionSource(objectSpace, typeof(MyImportResult));
int count = 0;
int iLine = 0;
if ((collectionSource.Collection as XPBaseCollection) != null)
{
((XPBaseCollection)collectionSource.Collection).LoadingEnabled = false;
}
foreach (StudentResultFile actFile in View.SelectedObjects)
{
if (actFile.Note == null || actFile.Note == "")
{
throw new UserFriendlyException("Vui lòng thêm thông tin Ghi chú trước khi import!!!");
}
string tempStudentFile;
string tempStudentFolderPath;
string tempStudentLogFile;
string templogname = "";
if (HttpContext.Current != null)
{
tempStudentFolderPath = HttpContext.Current.Request.MapPath("~/tempFolder");
tempStudentFile = HttpContext.Current.Request.MapPath("~/tempFolder/" + actFile.CsvFile.FileName);
templogname = actFile.CsvFile.FileName + DateTime.Now.ToString("dd-MM-yyyy-HHmmss") + "-log.html";
tempStudentLogFile = HttpContext.Current.Request.MapPath("~/tempFolder/" + templogname);
}
else
{
tempStudentFolderPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tempFolder");
tempStudentFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tempFolder/", actFile.CsvFile.FileName);
templogname = actFile.CsvFile.FileName + DateTime.Now.ToString("dd-MM-yyyy-HHmmss") + "-log.html";
tempStudentLogFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tempFolder/", templogname);
}
if (!Directory.Exists(tempStudentFolderPath))
{
Directory.CreateDirectory(tempStudentFolderPath);
}
using (System.IO.FileStream fileStream = new FileStream(tempStudentFile, FileMode.OpenOrCreate))
{
Dictionary <string, int> columnIndexs = new Dictionary <string, int>();
Dictionary <string, object> valueIndexs = new Dictionary <string, object>();
valueIndexs.Add("NHHK", "");
valueIndexs.Add("MSSV", "");
valueIndexs.Add("HO", "");
valueIndexs.Add("TEN", "");
valueIndexs.Add("NHOMLOPMH", "");
valueIndexs.Add("MAMH", "");
valueIndexs.Add("TENMH", "");
valueIndexs.Add("SOTC", "");
valueIndexs.Add("DTB10", "");
valueIndexs.Add("DTB4", "");
valueIndexs.Add("DIEMCHU", "");
columnIndexs.Add("NHHK", -1);
columnIndexs.Add("MSSV", -1);
columnIndexs.Add("HO", -1);
columnIndexs.Add("TEN", -1);
columnIndexs.Add("NHOMLOPMH", -1);
columnIndexs.Add("MAMH", -1);
columnIndexs.Add("TENMH", -1);
columnIndexs.Add("SOTC", -1);
columnIndexs.Add("DTB10", -1);
columnIndexs.Add("DTB4", -1);
columnIndexs.Add("DIEMCHU", -1);
// open xls file
actFile.CsvFile.SaveToStream(fileStream);
fileStream.Close();
Workbook book = Workbook.Open(tempStudentFile);
Worksheet sheet = book.Worksheets[0];
bool foundHeader = false;
Row row;
//Tìm dòng chứa TEN cột
for (iLine = sheet.Cells.FirstRowIndex;
iLine <= sheet.Cells.LastRowIndex && !foundHeader; iLine++)
{
row = sheet.Cells.GetRow(iLine);
for (int colIndex = row.FirstColIndex;
colIndex <= row.LastColIndex; colIndex++)
{
Cell cell = row.GetCell(colIndex);
if (columnIndexs.ContainsKey(cell.Value.ToString().ToUpper().Trim()))
{
columnIndexs[cell.Value.ToString().ToUpper().Trim()] = colIndex; //Đã tìm thấy dòng chứa TEN cột. Xác định vị trí của cột
}
}
if (!columnIndexs.Values.Contains(-1))
{
foundHeader = true;
}
else
{
for (int colIndex = row.FirstColIndex; colIndex <= row.LastColIndex; colIndex++)
{
Cell cell = row.GetCell(colIndex);
if (columnIndexs.ContainsKey(cell.Value.ToString().ToUpper().Trim()))
{
columnIndexs[cell.Value.ToString().ToUpper().Trim()] = -1; //không tìm thấy dòng chứa TEN cột. Xác định vị trí của cột
}
}
}
}
if (!foundHeader)
{
throw new UserFriendlyException("Lỗi cấu trúc file");
}
using (System.IO.StreamWriter fileStreamlog = new System.IO.StreamWriter(tempStudentLogFile, true))
{
fileStreamlog.WriteLine("<html><header><title>" + actFile.CsvFile.FileName + DateTime.Now.ToString("dd-MM-yyyy-HHmmss") + "-log </title> "+
"<meta http-equiv=\"content-type\" content=\"text/html; charset=UTF-8\" />" +
"</head><body>\r\n<table border=\"1px\"> <tr><Th>DÒNG</Th><th>TÌNH TRẠNG</th><th>THÔNG ĐIỆP</th></Tr>");
//Các dòng sau đó đều là dòng dữ liệu
List <Student> listStudent = new List <Student>();
List <Lesson> listLessons = new List <Lesson>();
List <Subject> listSubject = new List <Subject>();
List <Semester> listSemester = new List <Semester>();
for (; iLine <= sheet.Cells.LastRowIndex; iLine++)
{
row = sheet.Cells.GetRow(iLine);
try
{
foreach (var column in columnIndexs)
{
Cell cell = row.GetCell(column.Value);
valueIndexs[column.Key] = cell.Value;
}
//tìm sinh viên
Student student = listStudent.Find(s => s.StudentCode == valueIndexs["MSSV"].ToString().Trim());
if (student == null)
{
student = objectSpace.FindObject <Student>(CriteriaOperator.Parse("StudentCode = ?", valueIndexs["MSSV"].ToString().Trim()));
}
if (student == null)
{
fileStreamlog.WriteLine("<TR><td>{0}</td><td>{1}</td><td>{2}</td></tr>", iLine, "ERROR",
string.Format("Cannot find student: \"{0} - {1} {2} \" on {3:dd-mm-yy HH:MM:ss} - CANNOT IMPORT THIS LINE",
valueIndexs["MSSV"], valueIndexs["HO"], valueIndexs["TEN"], DateTime.Now));
continue;
}
else
{
if (!(student.FirstName.Contains(valueIndexs["HO"].ToString()) && student.LastName.Contains(valueIndexs["TEN"].ToString())))
{
fileStreamlog.WriteLine("<TR><td>{0}</td><td>{1}</td><td>{2}</td></tr>", iLine, "WARNING",
string.Format("Found student: \"{0}\" but Name:\"{1} {2}\" is not Like \"{3} {4}\" on {5:dd-mm-yy HH:MM:ss}",
valueIndexs["MSSV"], student.FirstName, student.LastName, valueIndexs["HO"], valueIndexs["TEN"], DateTime.Now));
}
listStudent.Add(student);
}
//found student
//tìm nhóm lớp
int nhomlop;
if (!int.TryParse(valueIndexs["NHOMLOPMH"].ToString().Trim(), out nhomlop))
{
fileStreamlog.WriteLine("<TR><td>{0}</td><td>{1}</td><td>{2}</td></tr>", iLine, "ERROR",
string.Format("CANNNOT CONVERT TO NUMBER for LessonCode: \"{0}\" on {3:dd-mm-yy HH:MM:ss}- CANNOT IMPORT THIS LINE",
valueIndexs["NHOMLOPMH"], DateTime.Now));
continue;
}
Lesson lesson = listLessons.Find(l => l.LessonCode == nhomlop);
if (lesson == null)
{
lesson = objectSpace.FindObject <Lesson>(CriteriaOperator.Parse("LessonCode = ?", nhomlop));
}
if (lesson != null)
{
if (lesson.Semester.SemesterName.Trim() != valueIndexs["NHHK"].ToString().Trim())
{
fileStreamlog.WriteLine("<TR><td>{0}</td><td>{1}</td><td>{2}</td></tr>", iLine, "ERROR",
string.Format("Found Lesson \"{0}\" but Semester {1} not same {2} on {3:dd-mm-yy HH:MM:ss} - CANNOT IMPORT THIS LINE",
valueIndexs["NHOMLOPMH"], lesson.Semester.SemesterName, valueIndexs["NHHK"], DateTime.Now));
continue;
}
if (lesson.Subject.SubjectCode != valueIndexs["MAMH"].ToString().Trim())
{
fileStreamlog.WriteLine("<TR><td>{0}</td><td>{1}</td><td>{2}</td></tr>", iLine, "ERROR",
string.Format("Found Lesson \"{0}\" but Subject Code {1} not same {2} on {3:dd-mm-yy HH:MM:ss} - CANNOT IMPORT THIS LINE",
valueIndexs["NHOMLOPMH"], lesson.Subject.SubjectCode, valueIndexs["MAMH"], DateTime.Now));
continue;
}
if (!lesson.ClassIDs.Contains(student.StudentClass.ClassCode))
{
lesson.ClassIDs += "," + student.StudentClass.ClassCode;
}
if (!listLessons.Contains(lesson))
{
listLessons.Add(lesson);
}
}
else //create new lesson
{
Semester semester = listSemester.Find(s => s.SemesterName == valueIndexs["NHHK"].ToString());
if (semester == null)
{
semester = objectSpace.FindObject <Semester>(CriteriaOperator.Parse("SemesterName = ?", valueIndexs["NHHK"]));
}
if (semester == null) //create new semester
{
semester = objectSpace.CreateObject <Semester>();
semester.SemesterName = valueIndexs["NHHK"].ToString();
semester.Save();
fileStreamlog.WriteLine("<TR><td>{0}</td><td>{1}</td><td>{2}</td></tr>", iLine, "CREATE NEW",
string.Format("Create Semester:{0} - on {1:dd-mm-yy HH:MM:ss} ",
valueIndexs["NHHK"], DateTime.Now));
listSemester.Add(semester);
}
else if (!listSemester.Contains(semester))
{
listSemester.Add(semester);
}
Subject subject = listSubject.Find(s => s.SubjectCode == valueIndexs["MAMH"].ToString());
if (subject == null)
{
subject = objectSpace.FindObject <Subject>(CriteriaOperator.Parse("SubjectCode = ?", valueIndexs["MAMH"]));
}
if (subject != null)
{
if (subject.SubjectName != valueIndexs["TENMH"].ToString())
{
fileStreamlog.WriteLine("<TR><td>{0}</td><td>{1}</td><td>{2}</td></tr>", iLine, "WARNING",
string.Format("Found Subject \"{0}\" for lesson {1} but Name {2} not same {3} on {4:dd-mm-yy HH:MM:ss}",
valueIndexs["MAMH"], valueIndexs["NHOMLOPMH"], subject.SubjectName, valueIndexs["TENMH"], DateTime.Now));
}
if (!listSubject.Contains(subject))
{
listSubject.Add(subject);
}
}
else//create new subject
{
subject = objectSpace.CreateObject <Subject>();
subject.SubjectCode = valueIndexs["MAMH"].ToString();
subject.SubjectName = valueIndexs["TENMH"].ToString();
subject.Credit = Convert.ToDouble(valueIndexs["SOTC"]);
subject.Save();
fileStreamlog.WriteLine("<TR><td>{0}</td><td>{1}</td><td>{2}</td></tr>", iLine, "CREATE NEW",
string.Format("Create Subject:{0} - {1} ({2}TC) on {3:dd-mm-yy HH:MM:ss} ",
valueIndexs["MAMH"], valueIndexs["TENMH"], valueIndexs["SOTC"], DateTime.Now));
listSubject.Add(subject);
}
lesson = objectSpace.CreateObject <Lesson>();
lesson.Semester = semester;
lesson.Subject = subject;
lesson.LessonCode = nhomlop;
lesson.CanRegister = false;
lesson.LessonNote = "Tạo mới cho phân hệ điểm";
lesson.ClassIDs = student.StudentClass.ClassCode;
lesson.Save();
fileStreamlog.WriteLine("<TR><td>{0}</td><td>{1}</td><td>{2}</td></tr>", iLine, "CREATE NEW",
string.Format("Create Lesson :{0} - {1} ({2}TC) on {3:dd-mm-yy HH:MM:ss}",
nhomlop, valueIndexs["MAMH"], valueIndexs["NHHK"], DateTime.Now));
listLessons.Add(lesson);
}
try
{
StudentResult studResult = objectSpace.FindObject <StudentResult>(CriteriaOperator.Parse("Student = ? and Lesson=?", student, lesson));
if (studResult == null)
{
studResult = objectSpace.CreateObject <StudentResult>();
studResult.Student = student;
studResult.Lesson = lesson;
studResult.AvgMark10 = Convert.ToDouble(valueIndexs["DTB10"]);
studResult.AvgMark4 = Convert.ToDouble(valueIndexs["DTB4"]);
studResult.AvgChar = valueIndexs["DIEMCHU"].ToString();
studResult.Save();
fileStreamlog.WriteLine("<TR><td>{0}</td><td>{1}</td><td>{2}</td></tr>", iLine, "CREATE NEW",
string.Format("Create StudentResult Lesson {0} with Subject Code {1} for student: \"{2}\"-\"{3}\" on {4:dd-mm-yy HH:MM:ss}",
lesson.LessonCode, lesson.Subject.SubjectCode, student.StudentCode, student.FullName, DateTime.Now));
}
else
{
studResult.AvgMark10 = Convert.ToDouble(valueIndexs["DTB10"]);
studResult.AvgMark4 = Convert.ToDouble(valueIndexs["DTB4"]);
studResult.AvgChar = valueIndexs["DIEMCHU"].ToString();
studResult.Save();
fileStreamlog.WriteLine("<TR><td>{0}</td><td>{1}</td><td>{2}</td></tr>", iLine, "WARNING",
string.Format("Update StudentResult Lesson {0} with Subject Code {1} for student: \"{2}\"-\"{3}\" on {4:dd-mm-yy HH:MM:ss}",
lesson.LessonCode, lesson.Subject.SubjectCode, student.StudentCode, student.FullName, DateTime.Now));
}
count++;
}
catch (Exception ex)
{
fileStreamlog.WriteLine("<TR><td>{0}</td><td>{1}</td><td>{2}</td></tr>", iLine, "ERROR",
string.Format("Cannot create StudentResult for student \"{0}\"-{1} on {2:dd-mm-yy HH:MM:ss} <BR/> {3} <BR/>{4}",
student.StudentCode, student.FullName, DateTime.Now, ex.Message, ex.StackTrace));
}
objectSpace.CommitChanges();
}
catch (Exception ex)
{
fileStreamlog.WriteLine("<TR><td>{0}</td><td>{1}</td><td>{2}</td></tr>", iLine, "ERROR",
ex.Message + ex.StackTrace);
}
}
fileStreamlog.WriteLine("</table></body></html>");
fileStreamlog.Close();
}
View.ObjectSpace.SetModified(actFile);
actFile.IsImported = true;
actFile.ResultLink = "/tempFolder/" + templogname;
View.ObjectSpace.CommitChanges();
}
}
PopUpMessage ms;
DialogController dc;
ms = objectSpace.CreateObject <PopUpMessage>();
ms.Title = "Kết quả import";
ms.Message = string.Format("Đã thực hiện import {0} kết quả cho {1} dòng trong file\r\n Vui lòng xem link kết quả", count, iLine);
e.ShowViewParameters.CreatedView = Application.CreateDetailView(
objectSpace, ms);
e.ShowViewParameters.TargetWindow = TargetWindow.NewModalWindow;
e.ShowViewParameters.CreatedView.Caption = "Thông báo";
dc = Application.CreateController <DialogController>();
dc.AcceptAction.Active.SetItemValue("object", false);
dc.CancelAction.Caption = "Đóng";
dc.SaveOnAccept = false;
e.ShowViewParameters.Controllers.Add(dc);
}