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