public void CreateFailStudentReport(int id, String FileName) { //Tao 1 roll call book rong ExcelPackage Package = new ExcelPackage(); ExcelWorksheet GeneralWorksheet = CreateGeneralWorksheet(id); Package.Workbook.Worksheets.Add(GeneralWorksheet.Name, GeneralWorksheet); RollCallBusiness rc = new RollCallBusiness(); SubjectBusiness sub = new SubjectBusiness(); List<RollCall> rollcalls = new List<RollCall>(); rollcalls = rc.GetList().Where(r => r.SemesterID == id).ToList(); List<Subject> subjectlist = new List<Subject>(); List<int> subj = new List<int>(); foreach (var rollcall in rollcalls) { AttendanceBusiness BO = new AttendanceBusiness(); var AttendanceLogs = BO.GetRollCallAttendanceLog(rollcall.RollCallID); int NumberOfSlot = rollcall.StudySessions.Count; var Students = rollcall.Students; for (int i = 0; i < Students.Count; i++) { int RowIndex = 7 + i; Student CurrentStudent = Students.ElementAt(i); double AbsentSession = CurrentStudent. StudentAttendances.Count(sa => AttendanceLogs.Select(a => a.LogID) .Contains(sa.AttendanceLog.LogID) && !sa.IsPresent); double AbsentRate = AbsentSession / NumberOfSlot * 100; //Neu nghi qua 20% if (AbsentRate > 20) { bool checkfailsubject = subj.Exists(r => r == rollcall.SubjectID); if (!checkfailsubject) { subj.Add(rollcall.SubjectID); var subject = sub.GetSubjectByID(rollcall.SubjectID); subjectlist.Add(subject); } } } } foreach (var subject in subjectlist) { //Tao detail sheet ExcelWorksheet RollWorkSheet = CreateDetailWorksheet(subject.SubjectID, id); Package.Workbook.Worksheets.Add(RollWorkSheet.Name, RollWorkSheet); } ExcelWriter.WriteExcelFile(Package, FileName); Package.Dispose(); }
public List <String> ValidRollCall(RollCall InRollCall, TimeSpan?otherTime) { SubjectBusiness SubBO = new SubjectBusiness(this.RollSystemDB); ClassBusiness ClassBO = new ClassBusiness(this.RollSystemDB); RollCallBusiness RcBO = new RollCallBusiness(this.RollSystemDB); List <string> ErrorList = new List <string>(); //var rollSubject = SubBO.GetSubjectByID(InRollCall.SubjectID); ////Check may cai nhu gio hoc, giao vien dang day v...v trong nay //if ((InRollCall.StartTime.ToString(@"hh\:mm") == "10:30" // || InRollCall.StartTime.ToString(@"hh\:mm") == "16:00") && //rollSubject.NumberOfSlot == 2) //{ // ErrorList.Add("Mon nay 2 slot, gio nay ko phu hop"); //} ////Check thu xem class cua roll call nay co dang hoc roll call nao ko //List<RollCall> ClassRcList = RcBO.GetList().Where(r => r.ClassID == InRollCall.ClassID).ToList(); //Boolean Classflag = true; //foreach (var item in ClassRcList) //{ // if (otherTime.ToString() != "") // { // InRollCall.StartTime = TimeSpan.Parse(otherTime.ToString()); // } // if (item.StartTime == InRollCall.StartTime && item.BeginDate <= InRollCall.BeginDate && InRollCall.BeginDate <= item.EndDate) // { // Classflag = false; // } //} //if (Classflag == false) //{ // ErrorList.Add("class"); //} //Check xem giao vien roll call nay co dang day roll call nao cung gio ko Boolean Insflag = true; List <RollCall> InsRcList = RcBO.GetList().Where(r => r.InstructorID == InRollCall.InstructorID).ToList(); foreach (var item in InsRcList) { if (item.StartTime == otherTime && item.BeginDate < InRollCall.BeginDate && InRollCall.BeginDate < item.EndDate) { Insflag = false; } } if (Insflag == false) { ErrorList.Add("instructor"); } //Nho check luon, start date va end date cua roll call so voi semester return(ErrorList); }
public bool Insert(RollCall InRollCall) { SubjectBusiness SubBO = new SubjectBusiness(this.RollSystemDB); ClassBusiness ClassBO = new ClassBusiness(this.RollSystemDB); RollCall rollcall = InRollCall; //Set thoi gian EndTime, dua vao start time var rollSubject = SubBO.GetSubjectByID(InRollCall.SubjectID); rollcall.EndTime = rollcall.StartTime. Add(TimeSpan.FromMinutes(90 * rollSubject.NumberOfSlot)). Add(TimeSpan.FromMinutes(15 * (rollSubject.NumberOfSlot - 1))); //VD: 20 slot se la 28 ngay. 15 slot la 21 ngay, 18 slot van 28 ngay int TotalDate = (int)Math.Ceiling((double)rollSubject.NumberOfSession / 5) * 7; //Tru 1 ngay de ket thuc vao chu nhat rollcall.EndDate = rollcall.BeginDate.AddDays(TotalDate).AddDays(-1); //Dua toan bo hoc sinh hien tai cua class vao var rollClass = ClassBO.GetClassByID(InRollCall.ClassID); foreach (var Student in rollClass.Students) { rollcall.Students.Add(Student); } //Tao cac studying session cua roll call nay DateTime SessionDate = rollcall.BeginDate; for (int i = 0; i < rollSubject.NumberOfSession; i++) { StudySession StuSes = new StudySession() { InstructorID = InRollCall.InstructorID, ClassID = rollcall.ClassID, StartTime = rollcall.StartTime, EndTime = rollcall.EndTime, SessionDate = SessionDate, Note = SessionDate.ToString("dd-MM-yyyy") + " : " }; rollcall.StudySessions.Add(StuSes); do { SessionDate = SessionDate.AddDays(1); } while (SessionDate.DayOfWeek == DayOfWeek.Saturday || SessionDate.DayOfWeek == DayOfWeek.Sunday); } //Set trang thai của roll call la incoming rollcall.Status = 1; return(base.Insert(rollcall)); }
private ExcelWorksheet CreateDetailWorksheet(int subjectID, int semesterID) { SubjectBusiness SuBO = new SubjectBusiness(); SemesterBusiness sem = new SemesterBusiness(); //get value semester, student, subject String semestername = sem.GetSemesterByID(semesterID).SemesterName; String subjectname = SuBO.GetSubjectByID(subjectID).FullName; String subshortname = SuBO.GetSubjectByID(subjectID).ShortName; ExcelWorksheet DetailWorkSheet = new ExcelPackage().Workbook.Worksheets.Add(subshortname + "_" + semestername); // get student list absent > 20% RollCallBusiness RoBO = new RollCallBusiness(); AttendanceBusiness BO = new AttendanceBusiness(); List<Student> students = new List<Student>(); List<RollCall> rollcalls = RoBO.GetList().Where(r => r.SubjectID == subjectID && r.SemesterID == semesterID).ToList(); List<int> studentID = new List<int>(); foreach (var rollcall in rollcalls) { var AttendanceLogs = BO.GetRollCallAttendanceLog(rollcall.RollCallID); int NumberOfSlot = rollcall.StudySessions.Count; var Students = rollcall.Students; for (int i = 0; i < Students.Count; i++) { int RowIndex = 7 + i; Student CurrentStudent = Students.ElementAt(i); double AbsentSession = CurrentStudent. StudentAttendances.Count(sa => AttendanceLogs.Select(a => a.LogID) .Contains(sa.AttendanceLog.LogID) && !sa.IsPresent); double AbsentRate = AbsentSession / NumberOfSlot * 100; //Neu nghi qua 20% if (AbsentRate > 20) { // check student is exist in list bool checkstuexit = studentID.Exists(r => r == CurrentStudent.StudentID); if (!checkstuexit) { studentID.Add(CurrentStudent.StudentID); students.Add(CurrentStudent); } } } } //write file DetailWorkSheet.Cells["A:XFD"].Style.Font.Name = "Arial"; DetailWorkSheet.Cells["C2"].Value = "List of student unqualified for examination report"; DetailWorkSheet.Cells["C2"].Style.Font.Size = 18; DetailWorkSheet.Cells["C2"].Style.Font.Bold = true; //semester detail DetailWorkSheet.Cells["C3"].Value = "Semester"; DetailWorkSheet.Cells["C4"].Value = "Subject"; DetailWorkSheet.Cells["C3:C4"].Style.Font.Size = 12; DetailWorkSheet.Cells["C3:C4"].Style.Font.Bold = true; DetailWorkSheet.Cells["D3"].Value = semestername; DetailWorkSheet.Cells["D4"].Value = subjectname; //title table DetailWorkSheet.Cells["B6"].Value = "No."; DetailWorkSheet.Cells["C6"].Value = "Student Name"; DetailWorkSheet.Cells["D6"].Value = "Student Code"; DetailWorkSheet.Cells["E6"].Value = "Class"; DetailWorkSheet.Cells["B6:E6"].Style.Font.Size = 12; DetailWorkSheet.Cells["B6:E6"].Style.Font.Bold = true; //body table var studentlist = students; for (int i = 0; i < studentlist.Count(); i++) { int RowIndex = 7 + i; DetailWorkSheet.Cells["B" + RowIndex].Value = i + 1; DetailWorkSheet.Cells["C" + RowIndex].Value = studentlist.ElementAt(i).FullName; DetailWorkSheet.Cells["D" + RowIndex].Value = studentlist.ElementAt(i).StudentCode; DetailWorkSheet.Cells["E" + RowIndex].Value = studentlist.ElementAt(i).Class.ClassName; } //set border table for (int column = 2; column <= 5; column++) { for (int row = 6; row <= 6 + studentlist.Count(); row++) { DetailWorkSheet.Cells[row, column].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); } } //set height , width DetailWorkSheet.Cells["C2:D2"].Merge = true; DetailWorkSheet.Cells["C2:D2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; DetailWorkSheet.Column(2).Width = 5; DetailWorkSheet.Column(3).Width = 25; DetailWorkSheet.Column(4).Width = 60; DetailWorkSheet.Column(5).Width = 12; return DetailWorkSheet; }
private ExcelWorksheet CreateGeneralWorksheet(int id) { ExcelWorksheet GeneralWorksheet = new ExcelPackage().Workbook.Worksheets.Add("General Report"); GeneralWorksheet.Cells["A:XFD"].Style.Font.Name = "Arial"; GeneralWorksheet.Cells["C2"].Value = "List of student unqualified for examination report"; GeneralWorksheet.Cells["C2"].Style.Font.Size = 18; GeneralWorksheet.Cells["C2"].Style.Font.Bold = true; //get value semester, student, subject SemesterBusiness sem = new SemesterBusiness(); SubjectBusiness sub = new SubjectBusiness(); String semestername = sem.GetSemesterByID(id).SemesterName; String begindate = sem.GetSemesterByID(id).BeginDate.ToString("dd-MM-yyyy"); String enddate = sem.GetSemesterByID(id).EndDate.ToString("dd-MM-yyyy"); RollCallBusiness rc = new RollCallBusiness(); List<RollCall> rollcalls = new List<RollCall>(); int numsub = 0; int numstu = 0; int numfstu = 0; rollcalls = rc.GetList().Where(r => r.SemesterID == id).ToList(); List<int> subid = new List<int>(); List<int> stuid = new List<int>(); List<int> fstuid = new List<int>(); // list num student absent for each subject List<int> numabsent = new List<int>(); // list check subject list have student relearn. List<int> subj = new List<int>(); List<Subject> subjectlist = new List<Subject>(); foreach (var rollcall in rollcalls) { // check subject is exit in list bool checksub = subid.Exists(r => r == rollcall.SubjectID); if (!checksub) { subid.Add(rollcall.SubjectID); numsub++; } List<Student> students = new List<Student>(); students = rollcall.Students.ToList(); foreach (var student in students) { bool checkstu = stuid.Exists(r => r == student.StudentID); if (!checkstu) { stuid.Add(student.StudentID); numstu++; } } AttendanceBusiness BO = new AttendanceBusiness(); var AttendanceLogs = BO.GetRollCallAttendanceLog(rollcall.RollCallID); int NumberOfSlot = rollcall.StudySessions.Count; var Students = rollcall.Students; for (int i = 0; i < Students.Count; i++) { int RowIndex = 7 + i; Student CurrentStudent = Students.ElementAt(i); double AbsentSession = CurrentStudent. StudentAttendances.Count(sa => AttendanceLogs.Select(a => a.LogID) .Contains(sa.AttendanceLog.LogID) && !sa.IsPresent); double AbsentRate = AbsentSession / NumberOfSlot * 100; //Neu nghi qua 20% if (AbsentRate > 20) { bool checkfstu = fstuid.Exists(r => r == CurrentStudent.StudentID); if (!checkfstu) { fstuid.Add(CurrentStudent.StudentID); numfstu++; } bool checkfailsubject = subj.Exists(r => r == rollcall.SubjectID); if (!checkfailsubject) { subj.Add(rollcall.SubjectID); var subject = sub.GetSubjectByID(rollcall.SubjectID); subjectlist.Add(subject); } numabsent.Add(rollcall.SubjectID); } } } //write file GeneralWorksheet.Cells["C2:D2"].Merge = true; GeneralWorksheet.Cells["C2:D2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; GeneralWorksheet.Cells["C3"].Value = "Semester "; GeneralWorksheet.Cells["C4"].Value = "Time "; GeneralWorksheet.Cells["C5"].Value = "Total subjects"; GeneralWorksheet.Cells["C6"].Value = "Total students"; GeneralWorksheet.Cells["C7"].Value = "Total students unqualified for examination"; GeneralWorksheet.Cells["C3:C7"].Style.Font.Size = 12; GeneralWorksheet.Cells["C3:C7"].Style.Font.Bold = true; GeneralWorksheet.Cells["D3"].Value = semestername; GeneralWorksheet.Cells["D4"].Value = "From " + begindate + " to " + enddate; GeneralWorksheet.Cells["D5"].Value = numsub; GeneralWorksheet.Cells["D6"].Value = numstu; GeneralWorksheet.Cells["D7"].Value = numfstu; GeneralWorksheet.Cells["B9"].Value = "No."; GeneralWorksheet.Cells["C9"].Value = "Subject"; GeneralWorksheet.Cells["D9"].Value = "Number of students unqualified for examination"; GeneralWorksheet.Cells["B9:D9"].Style.Font.Size = 12; GeneralWorksheet.Cells["B9:D9"].Style.Font.Bold = true; var subjects = subjectlist; for (int i = 0; i < subjects.Count(); i++) { int RowIndex = 10 + i; GeneralWorksheet.Cells["B" + RowIndex].Value = i + 1; GeneralWorksheet.Cells["C" + RowIndex].Value = subjects.ElementAt(i).FullName; GeneralWorksheet.Cells["D" + RowIndex].Value = numabsent.Count(r => r == subjects.ElementAt(i).SubjectID); } //set border table for (int column = 2; column <= 4; column++) { for (int row = 9; row <= 9 + subjects.Count(); row++) { GeneralWorksheet.Cells[row, column].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); } } //set height , width GeneralWorksheet.Cells["C2:D2"].Merge = true; GeneralWorksheet.Cells["C2:D2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; GeneralWorksheet.Column(2).Width = 8; GeneralWorksheet.Column(3).Width = 47; GeneralWorksheet.Column(4).Width = 53; return GeneralWorksheet; }