public List <RollCall> GetStudentLearningCourse(int StudentID)
{
StudentBusiness StuBO = new StudentBusiness();
Student FoundStu = StuBO.GetStudentByID(StudentID);
var LearningRollCall = FoundStu.RollCalls.Where(r => r.BeginDate <= DateTime.Now && r.EndDate > DateTime.Now).ToList();
return(LearningRollCall);
}
private static string GetUserName(FaceRecognizer.PredictionResult PR)
{
if (PR.Label == -1)
{
return("Unknown");
}
else
{
StudentBusiness StuBO = new StudentBusiness();
var Student = StuBO.GetStudentByID(PR.Label);
return(Student.StudentCode + " - " + Student.FullName);
}
}
public double GetStudentAbsentRate(int StudentID, int RollCallID)
{
//Dung nhieu nen phai de trong private
if (RollBO == null || StuBO == null)
{
RollBO = new RollCallBusiness(RollSystemDB);
StuBO = new StudentBusiness(RollSystemDB);
}
var AttendanceLogs = GetRollCallAttendanceLog(RollCallID);
var Student = StuBO.GetStudentByID(StudentID);
var RollCall = RollBO.GetRollCallByID(RollCallID);
int NumberOfSlot = RollCall.StudySessions.Count;
double AbsentSession = Student.StudentAttendances.Count(sa => AttendanceLogs.Select(rc => rc.LogID)
.Contains(sa.AttendanceLog.LogID) && !sa.IsPresent);
double AbsentRate = AbsentSession / NumberOfSlot * 100;
return(AbsentRate);
}
public void CreateStudentReport(int StudentID, int SemesterID, String FileName)
{
//Tao 1 roll call book rong
ExcelPackage Package = new ExcelPackage();
ExcelWorksheet GeneralWorksheet = CreateGeneralWorksheet(StudentID, SemesterID);
Package.Workbook.Worksheets.Add(GeneralWorksheet.Name, GeneralWorksheet);
var stu = new StudentBusiness().GetStudentByID(StudentID);
var rollcalls = stu.RollCalls.Where(rc => rc.SemesterID == SemesterID).ToList();
foreach (var rollcall in rollcalls)
{
//Tao sheet
ExcelWorksheet RollWorkSheet = CreateDetailWorksheet(StudentID, rollcall.RollCallID);
Package.Workbook.Worksheets.Add(RollWorkSheet.Name, RollWorkSheet);
}
ExcelWriter.WriteExcelFile(Package, FileName);
Package.Dispose();
}
private ExcelWorksheet CreateDetailWorksheet(int StudentID, int RollCallID)
{
RollCallBusiness RollBO = new RollCallBusiness();
var rollCall = RollBO.GetRollCallByID(RollCallID);
StudentBusiness StuBO = new StudentBusiness();
var student = StuBO.GetStudentByID(StudentID);
AttendanceBusiness AttenBO = new AttendanceBusiness();
List <AttendanceLog> RollCallLogs = AttenBO.GetRollCallAttendanceLog(RollCallID);
var StudentAttendances = student.StudentAttendances.Where(sa => sa.AttendanceLog.RollCallID == RollCallID &&
RollCallLogs.Select(r => r.LogID).Contains(sa.LogID))
.OrderBy(sa => sa.AttendanceLog.LogDate).ToList();
ExcelWorksheet DetailWorkSheet = new ExcelPackage().
Workbook.Worksheets.Add(rollCall.Subject.ShortName + "_" + rollCall.BeginDate.ToString("dd-MM-yyyy"));
int Absent = StudentAttendances.Count(sa => !sa.IsPresent);
double AbsentRate = (double)Absent / rollCall.StudySessions.Count * 100;
DetailWorkSheet.Cells["A:XFD"].Style.Font.Name = "Arial";
DetailWorkSheet.Cells["C2"].Value = "Attendance Report Detail";
DetailWorkSheet.Cells["C2"].Style.Font.Size = 18;
DetailWorkSheet.Cells["C2"].Style.Font.Bold = true;
//student detail
DetailWorkSheet.Cells["C3"].Value = "Subject";
DetailWorkSheet.Cells["C4"].Value = "Date";
DetailWorkSheet.Cells["C5"].Value = "Instructor";
DetailWorkSheet.Cells["C6"].Value = "Total Session";
DetailWorkSheet.Cells["C7"].Value = "Completed Session";
DetailWorkSheet.Cells["C8"].Value = "Absent";
DetailWorkSheet.Cells["C9"].Value = "Absent Rate";
DetailWorkSheet.Cells["C3:C9"].Style.Font.Size = 12;
DetailWorkSheet.Cells["C3:C9"].Style.Font.Bold = true;
DetailWorkSheet.Cells["D3"].Value = rollCall.Subject.FullName;
DetailWorkSheet.Cells["D4"].Value = "From " + rollCall.BeginDate.ToString("dd-MM-yyyy") + " to " + rollCall.EndDate.ToString("dd-MM-yyyy");
DetailWorkSheet.Cells["D5"].Value = rollCall.Instructor.Fullname;
DetailWorkSheet.Cells["D6"].Value = rollCall.Subject.NumberOfSession;
DetailWorkSheet.Cells["D7"].Value = StudentAttendances.Count();
DetailWorkSheet.Cells["D8"].Value = Absent;
DetailWorkSheet.Cells["D9"].Value = String.Format("{0:0}%", AbsentRate);
DetailWorkSheet.Cells["D6:D9"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//title table
DetailWorkSheet.Cells["B11"].Value = "No.";
DetailWorkSheet.Cells["C11"].Value = "Date";
DetailWorkSheet.Cells["D11"].Value = "Present";
DetailWorkSheet.Cells["E11"].Value = "Note";
DetailWorkSheet.Cells["B11:E11"].Style.Font.Size = 12;
DetailWorkSheet.Cells["B11:E11"].Style.Font.Bold = true;
for (int i = 0; i < StudentAttendances.Count(); i++)
{
int RowIndex = 12 + i;
DetailWorkSheet.Cells["B" + RowIndex].Value = i + 1;
DetailWorkSheet.Cells["C" + RowIndex].Value = StudentAttendances.ElementAt(i).AttendanceLog.LogDate.ToString("dd-MM-yyyy");
if (StudentAttendances.ElementAt(i).IsPresent)
{
DetailWorkSheet.Cells["D" + RowIndex].Value = "Present";
}
else
{
DetailWorkSheet.Cells["D" + RowIndex].Value = "Absent";
DetailWorkSheet.Cells["D" + RowIndex].Style.Font.Bold = true;
}
DetailWorkSheet.Cells["E" + RowIndex].Value = StudentAttendances.ElementAt(i).Note;
}
//set border table
for (int column = 2; column <= 5; column++)
{
for (int row = 11; row <= 11 + StudentAttendances.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 = 30;
DetailWorkSheet.Column(4).Width = 30;
DetailWorkSheet.Column(5).Width = 20;
return(DetailWorkSheet);
}