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 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;
}