public async Task <byte[]> ExportStudentTerm()
{
// Lấy dữ liệu trong database
StudentTermFilter studentTermFilter = new StudentTermFilter()
{
OrderBy = StudentTermOrder.StudentNumber
};
List <StudentTerm> studentTerms = await UOW.StudentTermRepository.List(studentTermFilter);
// Mở excelPackage
using (ExcelPackage excel = new ExcelPackage())
{
// đặt header
var studentTermHeaders = new List <string[]>()
{
new string[]
{
"STT",
"Mã số sinh viên",
"Họ",
"Tên",
"Tên môn học",
"Đủ điều kiện dự thi"
}
};
// tạo data
List <object[]> data = new List <object[]>();
for (int i = 0; i < studentTerms.Count; i++)
{
data.Add(new object[] {
i + 1,
studentTerms[i].StudentNumber,
studentTerms[i].StudentLastName,
studentTerms[i].StudentGivenName,
studentTerms[i].SubjectName,
studentTerms[i].IsQualified ? "x" : ""
});
}
// tạo worksheet
excel.GenerateWorksheet("Sinh viên - Môn học", studentTermHeaders, data);
// trả về dữ liệu dạng byte
return(excel.GetAsByteArray());
}
}
public async Task <List <StudentTerm> > List(StudentTermFilter filter)
{
if (filter == null)
{
return(new List <StudentTerm>());
}
IQueryable <StudentTermDAO> query = examRegContext.StudentTerm.AsNoTracking();
query = DynamicFilter(query, filter);
query = DynamicOrder(query, filter);
List <StudentTerm> list = await query.Select(s => new StudentTerm()
{
StudentId = s.StudentId,
TermId = s.TermId,
IsQualified = s.IsQualified,
SubjectName = s.Term.SubjectName,
StudentGivenName = s.Student.GivenName,
StudentLastName = s.Student.LastName,
StudentNumber = s.Student.StudentNumber
}).ToListAsync();
return(list);
}
private IQueryable <StudentTermDAO> DynamicOrder(IQueryable <StudentTermDAO> query, StudentTermFilter filter)
{
switch (filter.OrderType)
{
case OrderType.ASC:
switch (filter.OrderBy)
{
case StudentTermOrder.StudentNumber:
query = query.OrderBy(q => q.Student.StudentNumber);
break;
case StudentTermOrder.SubjectName:
query = query.OrderBy(q => q.Term.SubjectName);
break;
case StudentTermOrder.StudentLastName:
query = query.OrderBy(q => q.Student.LastName);
break;
case StudentTermOrder.StudentGivenName:
query = query.OrderBy(q => q.Student.GivenName);
break;
case StudentTermOrder.IsQualified:
query = query.OrderBy(q => q.IsQualified);
break;
default:
query = query.OrderBy(q => q.CX);
break;
}
break;
case OrderType.DESC:
switch (filter.OrderBy)
{
case StudentTermOrder.StudentNumber:
query = query.OrderByDescending(q => q.Student.StudentNumber);
break;
case StudentTermOrder.SubjectName:
query = query.OrderByDescending(q => q.Term.SubjectName);
break;
case StudentTermOrder.StudentLastName:
query = query.OrderByDescending(q => q.Student.LastName);
break;
case StudentTermOrder.StudentGivenName:
query = query.OrderByDescending(q => q.Student.GivenName);
break;
case StudentTermOrder.IsQualified:
query = query.OrderByDescending(q => q.IsQualified);
break;
default:
query = query.OrderByDescending(q => q.CX);
break;
}
break;
default:
query = query.OrderBy(q => q.CX);
break;
}
return(query.Skip(filter.Skip).Take(filter.Take));
}
private IQueryable <StudentTermDAO> DynamicFilter(IQueryable <StudentTermDAO> query, StudentTermFilter filter)
{
if (filter == null)
{
return(query.Where(q => 1 == 0));
}
if (filter.StudentId != null)
{
query = query.Where(q => q.StudentId, filter.StudentId);
}
if (filter.TermId != null)
{
query = query.Where(q => q.TermId, filter.TermId);
}
if (filter.IsQualified != null)
{
query = query.Where(c => c.IsQualified == filter.IsQualified);
}
return(query);
}
