public ActionResult List(Models.StudyReport.List vm)
{
return(Code.MvcHelper.Post(null, Url.Action("List", new
{
searchText = vm.SearchText,
studyId = vm.StudyId,
dateSearchFrom = vm.DateSearchFrom,
dateSearchTo = vm.DateSearchTo
})));
}
public ActionResult ListExport()
{
var vm = new Models.StudyReport.List();
using (var db = new XkSystem.Models.DbContext())
{
var file = System.IO.Path.GetTempFileName();
var fromDate = Convert.ToDateTime(vm.DateSearchFrom);
var toDate = Convert.ToDateTime(vm.DateSearchTo).AddDays(1);
var tbStudyDataList = from p in db.Table <Study.Entity.tbStudyData>()
.Include(d => d.tbStudy)
.Include(d => d.tbStudent)
.Include(d => d.tbStudyOption)
where p.InputDate >= fromDate && p.InputDate < toDate &&
p.tbStudent.IsDeleted == false &&
p.tbStudyOption.IsDeleted == false &&
p.tbSysUser.IsDeleted == false
select p;
var studyIds = (from p in tbStudyDataList
select p.tbStudy.Id).Distinct().ToList();
var tbStudy = from p in db.Table <Study.Entity.tbStudy>()
where studyIds.Contains(p.Id)
select p;
if (vm.StudyId != 0)
{
tbStudy = tbStudy.Where(d => d.Id == vm.StudyId);
}
var tbStudyList = (from p in tbStudy
orderby p.No descending
select p).ToList();
var classList = new List <Dto.StudyClassStudent.List>();
foreach (var study in tbStudyList)
{
if (study.IsRoom)//教室模式
{
var tb = from p in db.Table <Study.Entity.tbStudyRoomStudent>()
where p.tbStudy.Id == study.Id &&
p.tbStudy.IsDeleted == false &&
p.tbRoom.IsDeleted == false &&
p.tbStudent.IsDeleted == false
select p;
if (string.IsNullOrEmpty(vm.SearchText) == false)
{
tb = tb.Where(d => d.tbRoom.RoomName.Contains(vm.SearchText));
}
var tbClassList = (from p in tb
select new Dto.StudyClassStudent.List
{
ClassId = p.tbRoom.Id,
ClassName = p.tbRoom.RoomName,
StudentId = p.tbStudent.Id,
StudentCode = p.tbStudent.StudentCode,
StudentName = p.tbStudent.StudentName,
StudyId = p.tbStudy.Id,
StudyName = p.tbStudy.StudyName
}).ToList();
classList.AddRange(tbClassList);
}
else//班级模式
{
var tb = from p in db.Table <Study.Entity.tbStudyClassStudent>()
where p.tbStudy.Id == study.Id &&
p.tbStudy.IsDeleted == false &&
p.tbClass.IsDeleted == false &&
p.tbStudent.IsDeleted == false
select p;
if (string.IsNullOrEmpty(vm.SearchText) == false)
{
tb = tb.Where(d => d.tbClass.ClassName.Contains(vm.SearchText));
}
var tbClassList = (from p in tb
select new Dto.StudyClassStudent.List
{
ClassId = p.tbClass.Id,
ClassName = p.tbClass.ClassName,
StudentId = p.tbStudent.Id,
StudentCode = p.tbStudent.StudentCode,
StudentName = p.tbStudent.StudentName,
StudyId = p.tbStudy.Id,
StudyName = p.tbStudy.StudyName
}).ToList();
classList.AddRange(tbClassList);
}
}
vm.StudyReportList = (from p in classList
group p by new { p.StudyId, p.StudyName, p.ClassId, p.ClassName } into g
select new Dto.StudyReport.List
{
StudyId = g.Key.StudyId,
StudyName = g.Key.StudyName,
ClassId = g.Key.ClassId,
ClassName = g.Key.ClassName,
StudentCount = 0
}).ToList();
var StudyDataList = (from p in tbStudyDataList
select new Dto.StudyData.List
{
StudyId = p.tbStudy.Id,
StudentId = p.tbStudent.Id,
ClassId = 0
}).Distinct().ToList();
foreach (var item in StudyDataList)
{
var studyData = classList.Where(d => d.StudyId == item.StudyId && d.StudentId == item.StudentId).FirstOrDefault();
if (studyData != null)
{
item.ClassId = studyData.ClassId;
}
}
var StudyDataGroupList = (from p in StudyDataList
group p by new { p.StudyId, p.ClassId } into g
select new
{
StudyId = g.Key.StudyId,
ClassId = g.Key.ClassId,
StudentCount = g.Count()
}).ToList();
foreach (var item in vm.StudyReportList)
{
item.StudentCount = StudyDataGroupList.Where(d => d.StudyId == item.StudyId && d.ClassId == item.ClassId).Select(d => d.StudentCount).DefaultIfEmpty().FirstOrDefault();
}
var dt = new System.Data.DataTable();
dt.Columns.AddRange(new System.Data.DataColumn[]
{
new System.Data.DataColumn("序号"),
new System.Data.DataColumn("晚自习名称"),
new System.Data.DataColumn("班级"),
new System.Data.DataColumn("异常人数")
});
var index = 0;
foreach (var a in vm.StudyReportList)
{
index++;
var dr = dt.NewRow();
dr["序号"] = index.ToString();
dr["晚自习名称"] = a.StudyName;
dr["班级"] = a.ClassName;
dr["异常人数"] = a.StudentCount;
dt.Rows.Add(dr);
}
Code.NpoiHelper.DataTableToExcel(file, dt);
if (string.IsNullOrEmpty(file) == false)
{
return(File(file, Code.Common.DownloadType, Code.Common.ExportByExcel));
}
else
{
return(View());
}
}
}
public ActionResult List()
{
using (var db = new XkSystem.Models.DbContext())
{
var vm = new Models.StudyReport.List();
vm.StudyList = Controllers.StudyController.SelectList();
vm.StudyList.Insert(0, new SelectListItem {
Text = "==全部==", Value = "0"
});
var dateTimeNow = DateTime.Now;
if (string.IsNullOrEmpty(vm.DateSearchFrom))
{
vm.DateSearchFrom = new DateTime(dateTimeNow.Year, dateTimeNow.Month, 1).ToString(Code.Common.StringToDate);
}
if (string.IsNullOrEmpty(vm.DateSearchTo))
{
vm.DateSearchTo = new DateTime(dateTimeNow.Year, dateTimeNow.Month, 1).AddMonths(1).AddDays(-1).ToString(Code.Common.StringToDate);
}
var fromDate = Convert.ToDateTime(vm.DateSearchFrom);
var toDate = Convert.ToDateTime(vm.DateSearchTo).AddDays(1);
var tbStudyDataList = from p in db.Table <Study.Entity.tbStudyData>()
.Include(d => d.tbStudy)
.Include(d => d.tbStudent)
.Include(d => d.tbStudyOption)
where p.InputDate >= fromDate && p.InputDate < toDate &&
p.tbStudent.IsDeleted == false &&
p.tbStudyOption.IsDeleted == false &&
p.tbSysUser.IsDeleted == false
select p;
var studyIds = (from p in tbStudyDataList
select p.tbStudy.Id).Distinct().ToList();
var tbStudy = from p in db.Table <Study.Entity.tbStudy>()
where studyIds.Contains(p.Id)
select p;
if (vm.StudyId != 0)
{
tbStudy = tbStudy.Where(d => d.Id == vm.StudyId);
}
var tbStudyList = (from p in tbStudy
orderby p.No descending
select p).ToList();
var classList = new List <Dto.StudyClassStudent.List>();
foreach (var study in tbStudyList)
{
if (study.IsRoom)//教室模式
{
var tb = from p in db.Table <Study.Entity.tbStudyRoomStudent>()
where p.tbStudy.Id == study.Id &&
p.tbStudy.IsDeleted == false &&
p.tbRoom.IsDeleted == false &&
p.tbStudent.IsDeleted == false
select p;
if (string.IsNullOrEmpty(vm.SearchText) == false)
{
tb = tb.Where(d => d.tbRoom.RoomName.Contains(vm.SearchText));
}
var tbClassList = (from p in tb
select new Dto.StudyClassStudent.List
{
ClassId = p.tbRoom.Id,
ClassName = p.tbRoom.RoomName,
StudentId = p.tbStudent.Id,
StudentCode = p.tbStudent.StudentCode,
StudentName = p.tbStudent.StudentName,
StudyId = p.tbStudy.Id,
StudyName = p.tbStudy.StudyName
}).ToList();
classList.AddRange(tbClassList);
}
else//班级模式
{
var tb = from p in db.Table <Study.Entity.tbStudyClassStudent>()
where p.tbStudy.Id == study.Id &&
p.tbStudy.IsDeleted == false &&
p.tbClass.IsDeleted == false &&
p.tbStudent.IsDeleted == false
select p;
if (string.IsNullOrEmpty(vm.SearchText) == false)
{
tb = tb.Where(d => d.tbClass.ClassName.Contains(vm.SearchText));
}
var tbClassList = (from p in tb
select new Dto.StudyClassStudent.List
{
ClassId = p.tbClass.Id,
ClassName = p.tbClass.ClassName,
StudentId = p.tbStudent.Id,
StudentCode = p.tbStudent.StudentCode,
StudentName = p.tbStudent.StudentName,
StudyId = p.tbStudy.Id,
StudyName = p.tbStudy.StudyName
}).ToList();
classList.AddRange(tbClassList);
}
}
vm.StudyReportList = (from p in classList
group p by new { p.StudyId, p.StudyName, p.ClassId, p.ClassName } into g
select new Dto.StudyReport.List
{
StudyId = g.Key.StudyId,
StudyName = g.Key.StudyName,
ClassId = g.Key.ClassId,
ClassName = g.Key.ClassName,
StudentCount = 0
}).ToList();
var StudyDataList = (from p in tbStudyDataList
select new Dto.StudyData.List
{
StudyId = p.tbStudy.Id,
StudentId = p.tbStudent.Id,
ClassId = 0
}).Distinct().ToList();
foreach (var item in StudyDataList)
{
var studyData = classList.Where(d => d.StudyId == item.StudyId && d.StudentId == item.StudentId).FirstOrDefault();
if (studyData != null)
{
item.ClassId = studyData.ClassId;
}
}
var StudyDataGroupList = (from p in StudyDataList
group p by new { p.StudyId, p.ClassId } into g
select new
{
StudyId = g.Key.StudyId,
ClassId = g.Key.ClassId,
StudentCount = g.Count()
}).ToList();
foreach (var item in vm.StudyReportList)
{
item.StudentCount = StudyDataGroupList.Where(d => d.StudyId == item.StudyId && d.ClassId == item.ClassId).Select(d => d.StudentCount).DefaultIfEmpty().FirstOrDefault();
}
return(View(vm));
}
}