public JsonResult GetStatisticDataByClass(Guid projectId, string NJDM, DateTime beginTime, DateTime endTime)
{
try
{
beginTime = beginTime.Date;
endTime = endTime.Date;
using (EvaluationSystemEntities evaluationSystem = new EvaluationSystemEntities())
using (StudentEntities studentEntities = new StudentEntities())
{
var classesInfo =
studentEntities.Basic_ZZJX0202.Where(p => p.XXID == xxid && p.NJDM == NJDM)
.Select(p => p.BJID).ToList();
var classesInfo2 = studentEntities.Stu_ZZXS0101.Where(p => classesInfo.Contains(p.BJID.Value))
.GroupBy(p => p.BJID).Select(p => new { BJID = p.Key, StudentCount = p.Count() }).ToList();
var classesInfo3 = evaluationSystem.EvaluationSystemRecord.
Where(
p =>
classesInfo.Contains(p.ClassId) && p.ProjectId == projectId && p.HappenDate >= beginTime &&
p.HappenDate <= endTime).
GroupBy(p => p.ClassId).Select(p => new
{
BJID = p.Key,
Score = p.Sum(x => x.Score)
}).ToList();
var result = (from classInfo in classesInfo
join classStudent in classesInfo2 on classInfo equals classStudent.BJID into var1
join classScore in classesInfo3 on classInfo equals classScore.BJID into var2
from var3 in var1.DefaultIfEmpty()
from var4 in var2.DefaultIfEmpty()
select new
{
BJID = classInfo,
StudentCount = var3 == null?0:var3.StudentCount,
Score = var4 == null?0:var4.Score,
Average = var4 == null ? "0" : (var3 == null ? var4.Score.ToString("N0") : (var4.Score / var3.StudentCount).ToString("F"))
}).OrderByDescending(p => p.Score).ToList();
return(Json(new { flag = true, result }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception e)
{
return(Json(new { flag = false, message = e.Message }, JsonRequestBehavior.AllowGet));
}
}
public JsonResult GetStatisticFile(LevelModel levelModel, Guid projectId, Guid[] classIds, DateTime beginTime, DateTime endTime)
{
try
{
beginTime = beginTime.Date;
endTime = endTime.Date;
using (EvaluationSystemEntities evaluation = new EvaluationSystemEntities())
using (StudentEntities studentEntities = new StudentEntities())
{
var project = evaluation.EvaluationProject.Single(p => p.Id == projectId);
var evalutionSystem = evaluation.EvaluationSystem.Single(p => p.Id == project.SystemId).EvaluationSystem1.OrderBy(p => p.Order);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
//居中
ICellStyle cellStyle = hssfWorkbook.CreateCellStyle();
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.Alignment = HorizontalAlignment.Center;
//表头
var titleStyle = hssfWorkbook.CreateCellStyle();
titleStyle.CloneStyleFrom(cellStyle);
titleStyle.FillForegroundColor = HSSFColor.Grey40Percent.Index;
titleStyle.FillPattern = FillPattern.SolidForeground;
titleStyle.FillBackgroundColor = HSSFColor.Grey40Percent.Index;
var font = hssfWorkbook.CreateFont();
font.IsBold = true;
font.FontHeightInPoints = 14;
titleStyle.SetFont(font);
var classesInfo = studentEntities.View_Basic_SchClass.Where(p => classIds.Contains(p.BJID))
.Select(p => new { p.BJID, p.NJMC, p.XZBMC }).OrderBy(p => p.XZBMC).ToList();
var classesStudents = studentEntities.Stu_ZZXS0101.Where(p => classIds.Contains(p.BJID.Value))
.Select(p => new
{
p.BJID,
p.XSID,
p.XJH,
p.XM
}).ToList();
var classesRecords = evaluation.EvaluationSystemRecord.Where(p =>
p.HappenDate >= beginTime && p.HappenDate <= endTime && p.ProjectId == projectId &&
classIds.Contains(p.ClassId))
.Select(p => new
{
p.StudentId,
p.PropertyId,
p.Score
});
foreach (var classInfo in classesInfo)
{
var sheet = hssfWorkbook.CreateSheet(classInfo.NJMC + classInfo.XZBMC);
var colIndex = 0;
var rowIndex = 0;
CreateMergedCell(sheet, rowIndex, colIndex++, 1, "学生姓名", titleStyle);
CreateMergedCell(sheet, rowIndex, colIndex++, 1, "学籍号", titleStyle);
CreateMergedCell(sheet, rowIndex, colIndex++, 1, "记分总计", titleStyle);
if (levelModel.on)
{
CreateMergedCell(sheet, rowIndex, colIndex++, 1, "得分", titleStyle);
CreateMergedCell(sheet, rowIndex, colIndex++, 1, "等级", titleStyle);
}
CreateMergedCell(sheet, rowIndex, colIndex++, 1, "记分类别", titleStyle);
CreateMergedCell(sheet, rowIndex, colIndex++, 1, "记分项", titleStyle);
CreateMergedCell(sheet, rowIndex, colIndex, 1, "记分值", titleStyle);
rowIndex++;
var classStudent =
classesStudents.Where(p => p.BJID == classInfo.BJID).OrderBy(p => p.XJH).ToList();
foreach (var student in classStudent)
{
var studentRecords = classesRecords.Where(p => p.StudentId == student.XSID).ToList();
var score = studentRecords.Sum(p => p.Score);
var propertyIds = studentRecords.Select(p => p.PropertyId).Distinct();
var rowSpan = propertyIds.Count();
colIndex = 0;
CreateMergedCell(sheet, rowIndex, colIndex++, rowSpan, student.XM, cellStyle);
CreateMergedCell(sheet, rowIndex, colIndex++, rowSpan, student.XJH, cellStyle);
CreateMergedCell(sheet, rowIndex, colIndex++, rowSpan, score.ToString("N0"), cellStyle);
if (levelModel.on)
{
var scoreAndLevel = getScoreAndLevel(levelModel, score);
CreateMergedCell(sheet, rowIndex, colIndex++, rowSpan, scoreAndLevel[0], cellStyle);
CreateMergedCell(sheet, rowIndex, colIndex++, rowSpan, scoreAndLevel[1], cellStyle);
}
foreach (var system in evalutionSystem)
{
var thisColIndex = colIndex;
var systemRecords =
system.EvaluationSystem1.Where(p => propertyIds.Contains(p.Id))
.OrderBy(p => p.Order)
.ToList();
if (systemRecords.Count > 0)
{
CreateMergedCell(sheet, rowIndex, thisColIndex++, systemRecords.Count, system.Name, cellStyle);
foreach (var record in systemRecords)
{
var scores = studentRecords.Where(p => p.PropertyId == record.Id).Sum(p => p.Score);
CreateMergedCell(sheet, rowIndex, thisColIndex, 1, record.Name, cellStyle);
CreateMergedCell(sheet, rowIndex, thisColIndex + 1, 1, scores.ToString("N0"), cellStyle);
rowIndex++;
}
}
}
if (rowSpan == 0)
{
rowIndex++;
}
}
for (int i = 0; i < colIndex + 3; i++)
{
sheet.AutoSizeColumn(i);
}
}
var fileName = SaveHsskworkStream(hssfWorkbook);
return(Json(new { flag = true, result = fileName }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception e)
{
return(Json(new { flag = false, result = e.Message }, JsonRequestBehavior.AllowGet));
}
}
public DatabaseService()
{
context = new EvaluationSystemEntities();
}
