using AutoMapper; using Castle.Core.Internal; using Microsoft.AspNetCore.Http; using Microsoft.AspNetCore.Mvc; using Microsoft.AspNetCore.Mvc.RazorPages; using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.DependencyInjection; using Newtonsoft.Json; using OfficeOpenXml.FormulaParsing.Excel.Functions.DateTime; using OfficeOpenXml.FormulaParsing.Excel.Functions.Math; using OfficeOpenXml.FormulaParsing.Excel.Functions.Text; using Quartz.Util; using System; using System.Collections.Generic; using System.Dynamic; using System.Linq; using System.Linq.Expressions; using System.Text; using System.Threading.Tasks; using Tea.Utils; using VOL.Business.IRepositories; using VOL.Business.IServices.Norm; using VOL.Business.IServices.School; using VOL.Business.IServices.Training; using VOL.Business.Services.Norm; using VOL.Business.Services.School; using VOL.Core.CacheManager; using VOL.Core.Extensions; using VOL.Core.Extensions.AutofacManager; using VOL.Core.ManageUser; using VOL.Core.Utilities; using VOL.Entity.DomainModels; using VOL.Entity.DomainModels.Business.People; using VOL.Entity.DomainModels.XinWei; using VOL.Entity.Enum; using VOL.Model; using VOL.Model.Ai; using VOL.Model.IOT.Request; using VOL.Model.IOT.Response; using VOL.Model.Norm.Request; using VOL.Model.Norm.Response; using VOL.Model.School.Request; using VOL.Model.School.Response; using VOL.Model.Training.Response; using VOL.System.IRepositories; using VOL.System.Repositories; using YD_XinWei.Api.SmartSportsEntitys; using static Dapper.SqlMapper; using static Microsoft.EntityFrameworkCore.DbLoggerCategory; using static Microsoft.ML.Transforms.Text.LatentDirichletAllocationTransformer.ModelParameters; namespace VOL.Business.Services.Training { public class I_TrainingDataService : II_TrainingDataService, IDependency { #region 初始化 private readonly IMapper _mapper; private readonly ICacheService _cacheService; private readonly ICacheQueryService _cacheQueryService; private readonly S_TeacherRepository _teacherRepository; private readonly IN_SportsTestCategoryRepository _sportsTestCategoryRepository; [ActivatorUtilitiesConstructor] public I_TrainingDataService(IMapper mapper, ICacheService cacheService, ICacheQueryService cacheQueryService, S_TeacherRepository teacherRepository, IN_SportsTestCategoryRepository sportsTestCategoryRepository) { _mapper = mapper; _cacheService = cacheService; _teacherRepository = teacherRepository; _sportsTestCategoryRepository = sportsTestCategoryRepository; _cacheQueryService = cacheQueryService; } #endregion #region 项目类型 /// /// 获取体测项目来源 /// public DataSource GetSportsTestItemTypeDataSource(int itemType) { var sportsTestItemType = (SportsTestItemType)itemType; if ( sportsTestItemType == SportsTestItemType.BMI || sportsTestItemType == SportsTestItemType.VitalCapacity || sportsTestItemType == SportsTestItemType.One_Minute_Sit_Up || sportsTestItemType == SportsTestItemType.OneMinuteJumpRope || sportsTestItemType == SportsTestItemType.Sit_And_Reach || sportsTestItemType == SportsTestItemType.Pull_Up || sportsTestItemType == SportsTestItemType.StandingLongJump || sportsTestItemType == SportsTestItemType.MeterRun_50 || sportsTestItemType == SportsTestItemType.ShuttleRun_50x8 || sportsTestItemType == SportsTestItemType.MeterRun_1000 || sportsTestItemType == SportsTestItemType.MeterRun_800 ) { return DataSource.IOT; } return 0; } /// /// 获取训练项目来源 /// public DataSource GetTrainingItemTypeDataSource(int itemType) { var trainingItemType = (TrainingItemType)itemType; if ( trainingItemType == TrainingItemType.Coordinate || trainingItemType == TrainingItemType.Agility || trainingItemType == TrainingItemType.Strength || trainingItemType == TrainingItemType.Balance || trainingItemType == TrainingItemType.Speed || trainingItemType == TrainingItemType.HeartRate ) { return DataSource.IOT; } if ( trainingItemType == TrainingItemType.HighKnee || trainingItemType == TrainingItemType.JumpingJack || trainingItemType == TrainingItemType.JumpSquat || trainingItemType == TrainingItemType.KneeDrivePalmarPalpation || trainingItemType == TrainingItemType.CrossQuadrantJump ) { return DataSource.AI; } return 0; } /// /// 获取年级对应测试项目 /// /// /// /// public async Task> GetCategoryColumnNameList(int gradeId) { var categoryList = await (from s in _teacherRepository.DbContext.Set() join n in _teacherRepository.DbContext.Set() on s.CategoryValue equals n.CategoryValue where s.GradeId == gradeId select new CategoryColumnNameModel() { ColumnName = n.CategoryEnum, ColumnCnName = n.CategoryName }).ToListAsync(); return categoryList; } /// /// 获取训练项目类型 /// /// public async Task ItemTypeList() { var trainingList = await (from t in _sportsTestCategoryRepository.DbContext.Set() join a in _sportsTestCategoryRepository.DbContext.Set() on t.CategoryValue equals a.CategoryValue select new { Id = a.CategoryValue, t.ModeId, t.DataSource, ItemTypeName = a.CategoryName }).ToListAsync(); List trainingResult = Enum.GetValues(typeof(Ai_ModeEnum)) .Cast() .Select(modeEnum => new ModeAndCategory { ModeId = modeEnum, ModeName = modeEnum.GetDescription(), ItemTypeList = trainingList .Where(x => x.ModeId == (int)modeEnum) .Select(x => new IOT_ItemTypeModel { Id = x.Id, ItemTypeName = x.ItemTypeName }).DistinctBy(x => x.Id) .ToList() }) .Where(model => model.ItemTypeList.Any()) .ToList(); return new DataTypeAndCategory() { DataType = SportsTestDataType.TrainingData, ModeAndCategoryList = trainingResult }; } /// /// 获取体测项目类型 /// /// /// public async Task CategoryList() { var trainingList = await (from t in _sportsTestCategoryRepository.DbContext.Set() join a in _sportsTestCategoryRepository.DbContext.Set() on t.CategoryValue equals a.CategoryValue select new { Id = a.CategoryValue, t.ModeId, t.DataSource, ItemTypeName = a.CategoryName }).ToListAsync(); List trainingResult = Enum.GetValues(typeof(Ai_ModeEnum)) .Cast() .Select(modeEnum => new ModeAndCategory { ModeId = modeEnum, ModeName = modeEnum.GetDescription(), ItemTypeList = trainingList .Where(x => x.ModeId == (int)Ai_ModeEnum.ClassRoomMode) .Select(x => new IOT_ItemTypeModel { Id = x.Id, ItemTypeName = x.ItemTypeName }).DistinctBy(x => x.Id) .ToList() }) .Where(model => model.ItemTypeList.Any()) .ToList(); return new DataTypeAndCategory() { DataType = SportsTestDataType.TrainingData, ModeAndCategoryList = trainingResult }; } #endregion #region 训练数据 /// /// 训练数据课堂模式列表 /// /// /// /// public async Task> GetTrainingClassRoomModeDataList(ClassRoomModeDataParam paramDto) { var res = new PageDataDto(); if (paramDto.DataSource == 0) { paramDto.DataSource = GetTrainingItemTypeDataSource(paramDto.ItemType); } if (paramDto.DataSource == DataSource.IOT) { res = await QueryIotTrainingDataList(paramDto); } else { res = await QueryAiTrainingDataList(paramDto); } return res; } /// /// 训练数据课堂模式导出 /// /// /// /// public async Task> GetTrainingClassRoomModeDataExport(ClassRoomModeExportParam paramDto) { if (paramDto.DataSource == 0) { paramDto.DataSource = GetTrainingItemTypeDataSource(paramDto.ItemType); } if (paramDto.DataSource == DataSource.IOT) { return await QueryIotTrainingDataExport(paramDto); } else { return await QueryAiTrainingDataExport(paramDto); } } /// /// 获取IOT训练数据 /// /// /// public async Task> QueryIotTrainingDataList(ClassRoomModeDataParam paramDto) { var res = new PageDataDto(); var query = from td in _teacherRepository.DbContext.Set() join t in _teacherRepository.DbContext.Set() on td.TeacherId equals t.Id into teacherDatas from teacher in teacherDatas.DefaultIfEmpty() where td.IsDisplay && //td.ModelName == (int)Ai_ModeEnum.ClassRoomMode && td.SchoolCode.Equals(UserContext.Current.TenantId) && td.ItemType == (int)paramDto.ItemType select new ClassRoomModeDataModel() { Id = td.Id, ClassId = td.ClassId, TeacherName = teacher.TeacherName, ItemType = td.ItemType, StartTime = td.InsertTime, GradeAndClassName = td.ClassName, ModelName = td.ModelName, Avg = td.ClassNumAvg, Max = td.ClassMaxNum, Min = td.ClassMinNum, CenterNumber = td.ClassMedian, FinishNumber = td.TotalNumber, NotFinishedNumber = (td.ClassNum - td.TotalNumber) < 0 ? 0 : (td.ClassNum - td.TotalNumber) }; if (paramDto.StartTime.HasValue) { query = query.Where(x => x.StartTime >= paramDto.StartTime); } if (paramDto.EndTime.HasValue) { query = query.Where(x => x.StartTime <= paramDto.StartTime); } if (paramDto.ClassId.HasValue) { query = query.Where(x => x.ClassId.Equals(paramDto.ClassId)); } if (!paramDto.TeacherName.IsNullOrEmpty()) { query = query.Where(x => x.TeacherName.Contains(paramDto.TeacherName ?? "")); } res.Total = await query.CountAsync(); var list = await query .OrderByDescending(c => c.Id) .Skip((paramDto.PageIndex - 1) * paramDto.PageSize) .Take(paramDto.PageSize) .ToListAsync(); res.Datas = list; return res; } /// /// 获取IOT训练数据导出 /// /// /// public async Task> QueryIotTrainingDataExport(ClassRoomModeExportParam paramDto) { var query = from td in _teacherRepository.DbContext.Set() join t in _teacherRepository.DbContext.Set() on td.TeacherId equals t.Id into teacherDatas from teacher in teacherDatas.DefaultIfEmpty() where td.IsDisplay && td.ModeType == (int)Ai_ModeEnum.ClassRoomMode && td.SchoolCode.Equals(UserContext.Current.TenantId) && td.ItemType == (int)paramDto.ItemType select new ClassRoomModeDataModel() { Id = td.Id, ClassId = td.ClassId, TeacherName = teacher.TeacherName, ItemType = td.ItemType, StartTime = td.InsertTime, GradeAndClassName = td.ClassName, ModelName = td.ModelName, Avg = td.ClassNumAvg, Max = td.ClassMaxNum, Min = td.ClassMinNum, CenterNumber = td.ClassMedian, FinishNumber = td.TotalNumber, NotFinishedNumber = (td.ClassNum - td.TotalNumber) < 0 ? 0 : (td.ClassNum - td.TotalNumber) }; if (paramDto.StartTime.HasValue) { query = query.Where(x => x.StartTime >= paramDto.StartTime); } if (paramDto.EndTime.HasValue) { query = query.Where(x => x.StartTime <= paramDto.StartTime); } if (paramDto.ClassId.HasValue) { query = query.Where(x => x.ClassId.Equals(paramDto.ClassId)); } if (!paramDto.TeacherName.IsNullOrEmpty()) { query = query.Where(x => x.TeacherName.Contains(paramDto.TeacherName ?? "")); } var list = await query.OrderByDescending(c => c.Id).ToListAsync(); return list; } /// /// 课堂模式数据详情 /// /// /// /// public async Task> GetClassRoomModeDataDetails(ClassRoomModeDataDetailsParam paramDto) { var res = new PageDataDto(); var tenantId = UserContext.Current.TenantId; IQueryable query; if (paramDto.DataSource == 0) { paramDto.DataSource = GetTrainingItemTypeDataSource(paramDto.ItemType); } if (paramDto.DataSource == DataSource.IOT) { query = from student in _teacherRepository.DbContext.Set() join trainingData in _teacherRepository.DbContext.Set() on student.TrainingDataId equals trainingData.Id where trainingData.IsDisplay && trainingData.SchoolCode == tenantId && trainingData.Id == paramDto.Id && trainingData.ItemType == paramDto.ItemType select new ClassRoomModeDataDetails { ClassId = student.ClassId, GradeAndClassName = $"{trainingData.GradeName}{trainingData.ClassName}", StartTime = trainingData.EndTime, ItemType = trainingData.ItemType, Id = student.Id, Sex = student.Gender, ResultLevel = student.ResultLevel, StudentName = student.StudentName, StudentNo = student.StudentNo, JumpValue = student.JumpValue, SpeedTime = student.SpeedTime, ErrorNumber = student.ErrorNumber, Kcal = student.Kcal }; } else { query = from trainingData in _teacherRepository.DbContext.Set() where trainingData.IsDisplay && trainingData.SchoolCode == tenantId && trainingData.ClassRoomRecordId == paramDto.Id && trainingData.CategoryValue == paramDto.ItemType select new ClassRoomModeDataDetails { Id = trainingData.Id, ClassId = trainingData.ClassId, GradeAndClassName = $"{trainingData.GradeName}{trainingData.ClassName}", StartTime = trainingData.StartTime, ItemType = trainingData.CategoryValue, Sex = (int)trainingData.Sex, ResultLevel = (int)trainingData.RankEnum, StudentName = trainingData.StudentName, StudentNo = trainingData.StudentNo, JumpValue = (int)trainingData.Value, ErrorNumber = trainingData.ErrorNumber }; } if (!paramDto.StudentName.IsNullOrEmpty()) { query = query.Where(x => x.StudentName.Contains(paramDto.StudentName)); } if (!paramDto.StudentNo.IsNullOrEmpty()) { query = query.Where(x => x.StudentNo.Contains(paramDto.StudentNo)); } if (paramDto.Sex.HasValue) { query = query.Where(x => x.Sex == paramDto.Sex); } if (paramDto.ResultLevel.HasValue) { query = query.Where(x => x.ResultLevel == paramDto.ResultLevel); } // 获取总数 res.Total = await query.CountAsync(); // 分页和排序 var list = await query .OrderBy(c => c.Id) .Skip((paramDto.PageIndex - 1) * paramDto.PageSize) .Take(paramDto.PageSize) .ToListAsync(); res.Datas = list; return res; } /// /// 课堂模式数据详情导出 /// /// /// /// public async Task> ClassRoomModeDataDetailsExport(ClassRoomModeDataExportParam paramDto) { var res = new PageDataDto(); var tenantId = UserContext.Current.TenantId; IQueryable query; if (paramDto.DataSource == 0) { paramDto.DataSource = GetTrainingItemTypeDataSource(paramDto.ItemType); } if (paramDto.DataSource == DataSource.IOT) { query = from trainingData in _teacherRepository.DbContext.Set() join student in _teacherRepository.DbContext.Set() on trainingData.Id equals student.TrainingDataId where trainingData.IsDisplay && trainingData.SchoolCode == tenantId && trainingData.Id == paramDto.Id && trainingData.ItemType == paramDto.ItemType select new ClassRoomModeDataDetails { ClassId = trainingData.ClassId, GradeAndClassName = $"{trainingData.GradeName}{trainingData.ClassName}", StartTime = trainingData.InsertTime, ItemType = trainingData.ItemType, Id = student.Id, Sex = student.Gender, ResultLevel = student.ResultLevel, StudentName = student.StudentName, StudentNo = student.StudentNo, JumpValue = student.JumpValue, SpeedTime = student.SpeedTime, ErrorNumber = student.ErrorNumber, Kcal = student.Kcal }; } else { query = from trainingData in _teacherRepository.DbContext.Set() where trainingData.IsDisplay && trainingData.SchoolCode == tenantId && trainingData.ClassRoomRecordId == paramDto.Id && trainingData.CategoryValue == paramDto.ItemType select new ClassRoomModeDataDetails { Id = trainingData.Id, ClassId = trainingData.ClassId, GradeAndClassName = $"{trainingData.GradeName}{trainingData.ClassName}", StartTime = trainingData.StartTime, ItemType = trainingData.CategoryValue, Sex = (int)trainingData.Sex, ResultLevel = (int)trainingData.RankEnum, StudentName = trainingData.StudentName, StudentNo = trainingData.StudentNo, JumpValue = (int)trainingData.Value, ErrorNumber = trainingData.ErrorNumber }; } if (!paramDto.StudentName.IsNullOrEmpty()) { query = query.Where(x => x.StudentName.Contains(paramDto.StudentName)); } if (!paramDto.StudentNo.IsNullOrEmpty()) { query = query.Where(x => x.StudentNo.Contains(paramDto.StudentNo)); } if (paramDto.Sex.HasValue) { query = query.Where(x => x.Sex == paramDto.Sex); } if (paramDto.ResultLevel.HasValue) { query = query.Where(x => x.ResultLevel == paramDto.ResultLevel); } return await query.ToListAsync(); } #endregion #region 自由模式 /// /// 自由模式 /// /// /// /// public async Task> FreeModeDataList(FreeModeDataParam paramDto) { var res = new PageDataDto(); var tenantId = UserContext.Current.TenantId; var projectId = await _teacherRepository.DbContext.Set() .Where(x => x.CategoryValue == paramDto.ItemCode) .Select(x => x.ProjectId) .FirstOrDefaultAsync(); var orgId = await _teacherRepository.DbContext.Set() .Where(x => x.SchoolCode == tenantId) .Select(x => x.Id) .FirstOrDefaultAsync(); // First query var queryBW = from a in _teacherRepository.DbContext.Set() where a.IsDisplay && a.SchoolCode.Equals(tenantId) && a.ModeType == Ai_ModeEnum.FreeMode && a.CategoryValue == paramDto.ItemCode select new FreeModeDataModel() { Id = a.Id, StartTime = a.StartTime, StudentNo = a.StudentNo, StudentName = a.StudentName, Weight = a.Weight, Height = a.Height, Sex = (int)a.Sex, ClassId = a.ClassId, Result = a.Value, GradeAndClassName = $"{a.GradeName}{a.ClassName}", }; // Second query var queryXW = from s in _teacherRepository.DbContext.Set() join t in _teacherRepository.DbContext.Set() on s.Id equals t.XW_SportsTestDataId where s.OrgId == orgId && s.ProjectId == projectId select new FreeModeDataModel() { Id = s.Id, StartTime = Convert.ToDateTime(s.CreateTime), StudentNo = t.StudentNo, StudentName = t.StudentName, Weight = s.ProjectId == 21 ? t.OriginalAchievement / 100 : 0, Height = s.ProjectId == 21 ? t.Achievement / 10 : 0, Sex = (int)t.Sex, ClassId = t.ClassId, Result = s.ProjectId == 21 ? (double)t.Achievement2 : (double)t.Achievement, GradeAndClassName = $"{t.GradeName}{t.ClassName}", }; // 异步执行查询，获取结果 var dataBW = await queryBW.ToListAsync(); var dataXW = await queryXW.ToListAsync(); // 合并数据 var combinedData = dataBW.Concat(dataXW); // 过滤条件 if (paramDto.StartTime.HasValue) { combinedData = combinedData.Where(x => x.StartTime >= paramDto.StartTime); } if (paramDto.EndTime.HasValue) { combinedData = combinedData.Where(x => x.StartTime <= paramDto.EndTime); } if (paramDto.ClassId.HasValue) { combinedData = combinedData.Where(x => x.ClassId.Equals(paramDto.ClassId)); } if (!paramDto.StudentName.IsNullOrEmpty()) { combinedData = combinedData.Where(x => x.StudentName.Contains(paramDto.StudentName ?? "")); } if (!paramDto.StudentNo.IsNullOrEmpty()) { combinedData = combinedData.Where(x => x.StudentNo.Contains(paramDto.StudentNo ?? "")); } if (paramDto.Sex.HasValue) { combinedData = combinedData.Where(x => x.Sex == paramDto.Sex); } // 计算总记录数 res.Total = combinedData.Count(); // 分页处理 var list = combinedData .OrderByDescending(c => c.Id) .Skip((paramDto.PageIndex - 1) * paramDto.PageSize) .Take(paramDto.PageSize) .ToList(); res.Datas = list; return res; } /// /// 自由模式导出 /// /// /// /// public async Task> FreeModeDataExport(FreeModeDataExportParam paramDto) { var tenantId = UserContext.Current.TenantId; var projectId = await _teacherRepository.DbContext.Set() .Where(x => x.CategoryValue == paramDto.ItemCode) .Select(x => x.ProjectId) .FirstAsync(); var orgId = await _teacherRepository.DbContext.Set() .Where(x => x.SchoolCode == tenantId) .Select(x => x.Id) .FirstAsync(); // First query var queryBW = from a in _teacherRepository.DbContext.Set() where a.IsDisplay && a.SchoolCode.Equals(tenantId) && a.ModeType == Ai_ModeEnum.FreeMode && a.CategoryValue == paramDto.ItemCode select new FreeModeDataModel() { Id = a.Id, StartTime = a.StartTime, StudentNo = a.StudentNo, StudentName = a.StudentName, Weight = a.Weight, Height = a.Height, Sex = (int)a.Sex, ClassId = a.ClassId, Result = a.Value, GradeAndClassName = $"{a.GradeName}{a.ClassName}", }; // Second query var queryXW = from s in _teacherRepository.DbContext.Set() join t in _teacherRepository.DbContext.Set() on s.Id equals t.XW_SportsTestDataId where s.OrgId == orgId && s.ProjectId == projectId select new FreeModeDataModel() { Id = s.Id, StartTime = Convert.ToDateTime(s.CreateTime), StudentNo = t.StudentNo, StudentName = t.StudentName, Weight = s.ProjectId == 21 ? t.OriginalAchievement / 100 : 0, Height = s.ProjectId == 21 ? t.Achievement / 10 : 0, Sex = (int)t.Sex, ClassId = t.ClassId, Result = s.ProjectId == 21 ? (double)t.Achievement2 : (double)t.Achievement, GradeAndClassName = $"{t.GradeName}{t.ClassName}", }; // 异步执行查询，获取结果 var dataBW = await queryBW.ToListAsync(); var dataXW = await queryXW.ToListAsync(); // 合并数据 var combinedData = dataBW.Concat(dataXW); // 过滤条件 if (paramDto.StartTime.HasValue) { combinedData = combinedData.Where(x => x.StartTime >= paramDto.StartTime); } if (paramDto.EndTime.HasValue) { combinedData = combinedData.Where(x => x.StartTime <= paramDto.EndTime); } if (paramDto.ClassId.HasValue) { combinedData = combinedData.Where(x => x.ClassId.Equals(paramDto.ClassId)); } if (!paramDto.StudentName.IsNullOrEmpty()) { combinedData = combinedData.Where(x => x.StudentName.Contains(paramDto.StudentName ?? "")); } if (!paramDto.StudentNo.IsNullOrEmpty()) { combinedData = combinedData.Where(x => x.StudentNo.Contains(paramDto.StudentNo ?? "")); } if (paramDto.Sex.HasValue) { combinedData = combinedData.Where(x => x.Sex == paramDto.Sex); } return combinedData.OrderByDescending(c => c.Id).ToList(); } #endregion #region AI数据 /// /// 获取AI训练和体测数据 /// /// /// public async Task> QueryAiTrainingDataList(ClassRoomModeDataParam paramDto) { var res = new PageDataDto(); var query = from room in _teacherRepository.DbContext.Set() join a in _teacherRepository.DbContext.Set() on room.Id equals a.ClassRoomRecordId into sportsTestDatas from a in sportsTestDatas.DefaultIfEmpty() // Left join where a.IsDisplay && room.SchoolCode.Equals(UserContext.Current.TenantId) && a.ModeType == Ai_ModeEnum.ClassRoomMode && a.DataType == SportsTestDataType.TrainingData && a.CategoryValue == paramDto.ItemType select new { room.SchoolCode, room.Code, room.Id, a.UniqueId, a.GradeId, a.ClassId, a.GradeName, a.ClassName, a.StudentCount, a.TeacherId, room.TeacherName, room.StartTime, room.Name, a.ModeType, a.ModelName, a.DataType, a.CategoryValue, a.StudentNo, a.StudentName, a.Value, a.MaxValue, a.MinValue, a.RankEnum }; if (paramDto.StartTime.HasValue) { query = query.Where(x => x.StartTime >= paramDto.StartTime); } if (paramDto.EndTime.HasValue) { query = query.Where(x => x.StartTime <= paramDto.EndTime); } if (paramDto.ClassId.HasValue) { query = query.Where(x => x.ClassId.Equals(paramDto.ClassId)); } if (!paramDto.TeacherName.IsNullOrEmpty()) { query = query.Where(x => x.TeacherName.Contains(paramDto.TeacherName ?? "")); } // 按照 ClassRoomRecordId 分组 var list = await query .GroupBy(t => new { t.Id, t.CategoryValue, t.GradeId, t.GradeName, t.ClassId, t.ClassName, t.TeacherId, t.TeacherName }) .Select(g => new { g.Key.Id, g.Key.CategoryValue, g.Key.GradeId, g.Key.GradeName, g.Key.ClassId, g.Key.ClassName, g.Key.TeacherId, g.Key.TeacherName, Data = g.ToList() }) .OrderByDescending(x => x.Id) .Skip((paramDto.PageIndex - 1) * paramDto.PageSize) .Take(paramDto.PageSize) .ToListAsync(); // 计算总记录数 var totalCount = list.Count(); var studentCountList = await (from c in _teacherRepository.DbContext.Set() join g in _teacherRepository.DbContext.Set() on c.GradeId equals g.Id join a in _teacherRepository.DbContext.Set() on g.Id equals a.GradeId where c.SchoolCode == UserContext.Current.TenantId select new { c.Id, c.ClassName, c.GradeId, g.GradeName, StudentCount = _teacherRepository.DbContext.Set() .Where(s => s.ClassId == c.Id && s.SchoolCode == UserContext.Current.TenantId) .Count() }).Distinct().ToListAsync(); var result = list.Select(g => new ClassRoomModeDataModel() { Id = g.Id, ItemType = g.Data.FirstOrDefault()?.CategoryValue ?? 0, ClassId = g.ClassId, GradeAndClassName = $"{g.GradeName}{g.ClassName}", TeacherName = g.TeacherName, ModelName = ((TrainingItemType)g.CategoryValue).GetDescription(), FinishNumber = g.Data.Count, NotFinishedNumber = studentCountList.FirstOrDefault(c => c.Id == g.ClassId).StudentCount - g.Data.Count(), Avg = g.Data.Average(d => d.Value), CenterNumber = g.Data.Select(d => (float)d.Value).ToList().Median(), Max = g.Data.Max(d => d.Value), Min = g.Data.Min(d => d.Value), StartTime = g.Data.Max(d => d.StartTime) }).ToList(); res.Total = totalCount; res.Datas = result; return res; } /// /// 获取AI训练和体测数据导出 /// /// /// public async Task> QueryAiTrainingDataExport(ClassRoomModeExportParam paramDto) { // 获取所有符合条件的主表数据 var query = from a in _teacherRepository.DbContext.Set() join t in _teacherRepository.DbContext.Set() on a.TeacherId equals t.Id into teacherDatas from teacher in teacherDatas.DefaultIfEmpty() where a.IsDisplay && a.SchoolCode.Equals(UserContext.Current.TenantId) && a.ModeType == Ai_ModeEnum.ClassRoomMode && a.DataType == SportsTestDataType.TrainingData && a.CategoryValue == paramDto.ItemType select new { a.SchoolCode, a.Code, a.ClassRoomRecordId, a.UniqueId, a.GradeId, a.ClassId, a.GradeName, a.ClassName, a.StudentCount, a.TeacherId, teacher.TeacherName, a.ModeType, a.ModelName, a.DataType, a.CategoryValue, a.StartTime, a.StudentNo, a.StudentName, a.Value, a.MaxValue, a.MinValue, a.RankEnum }; if (paramDto.StartTime.HasValue) { query = query.Where(x => x.StartTime >= paramDto.StartTime); } if (paramDto.EndTime.HasValue) { query = query.Where(x => x.StartTime <= paramDto.StartTime); } if (paramDto.ClassId.HasValue) { query = query.Where(x => x.ClassId.Equals(paramDto.ClassId)); } if (!paramDto.TeacherName.IsNullOrEmpty()) { query = query.Where(x => x.TeacherName.Contains(paramDto.TeacherName ?? "")); } // 按照指定字段分组 var list = await query .GroupBy(t => new { t.CategoryValue, t.GradeId, t.GradeName, t.ClassId, t.ClassName, t.TeacherId, t.TeacherName }) .Select(g => new { g.Key.CategoryValue, g.Key.GradeId, g.Key.GradeName, g.Key.ClassId, g.Key.ClassName, g.Key.TeacherId, g.Key.TeacherName, Data = g.GroupBy(x => x.StudentNo) .Select(studentGroup => studentGroup.OrderByDescending(x => x.StartTime).FirstOrDefault()) .ToList() }).ToListAsync(); // 计算总记录数 var totalCount = await query .GroupBy(t => new { t.CategoryValue, t.GradeId, t.GradeName, t.ClassId, t.ClassName, t.TeacherId, t.TeacherName }) .CountAsync(); var studentCountList = await (from c in _teacherRepository.DbContext.Set() join g in _teacherRepository.DbContext.Set() on c.GradeId equals g.Id join a in _teacherRepository.DbContext.Set() on g.Id equals a.GradeId where c.SchoolCode == UserContext.Current.TenantId select new { c.Id, c.ClassName, c.GradeId, g.GradeName, StudentCount = _teacherRepository.DbContext.Set() .Where(s => s.ClassId == c.Id && s.SchoolCode == UserContext.Current.TenantId) .Count() }).Distinct().ToListAsync(); var result = list.Select(g => new ClassRoomModeDataModel() { ItemType = g.CategoryValue, ClassId = g.ClassId, GradeAndClassName = $"{g.GradeName}{g.ClassName}", TeacherName = g.TeacherName, ModelName = ((TrainingItemType)g.CategoryValue).GetDescription(), FinishNumber = g.Data.Count, NotFinishedNumber = studentCountList.FirstOrDefault(c => c.Id == g.ClassId).StudentCount - g.Data.Count(), Avg = g.Data.Average(d => d.Value), CenterNumber = g.Data.Select(d => (float)d.Value).ToList().Median(), Max = g.Data.Max(d => d.Value), Min = g.Data.Min(d => d.Value), StartTime = g.Data.Max(d => d.StartTime) }).ToList(); return result; } #endregion #region 体测数据 /// /// 体测数据课堂模式列表 /// /// /// public async Task> GetTestClassRoomModeDataList(ClassRoomModeDataParam paramDto) { var res = new PageDataDto(); if (paramDto.DataSource == 0) { paramDto.DataSource = GetSportsTestItemTypeDataSource(paramDto.ItemType); } if (paramDto.DataSource == DataSource.IOT) { res = await QueryIotTestDataList(paramDto); } else { res = await QueryAiTrainingDataList(paramDto); } return res; } /// /// 获取IOT体测数据 /// /// /// public async Task> QueryIotTestDataList(ClassRoomModeDataParam paramDto) { var res = new PageDataDto(); // 查询基础数据 var query = _teacherRepository.DbContext.Set() .Where(s => s.IsDisplay && s.SchoolCode == UserContext.Current.TenantId && s.CategoryValue == paramDto.ItemType && s.DataSource != DataSource.XW); // 添加条件过滤 if (paramDto.StartTime.HasValue) query = query.Where(x => x.CreateDate >= paramDto.StartTime); if (paramDto.EndTime.HasValue) query = query.Where(x => x.CreateDate <= paramDto.EndTime); if (paramDto.ClassId.HasValue) query = query.Where(x => x.ClassId == paramDto.ClassId); if (!paramDto.TeacherName.IsNullOrEmpty()) query = query.Where(x => x.TeacherName.Contains(paramDto.TeacherName ?? "")); // 获取学生人数数据（按班级分组计算总人数） var classValues = await query.Select(x => new { x.ClassId, x.CreateDate, x.Value }).Distinct().ToListAsync(); var classIds = classValues.Select(x => x.ClassId).ToList(); var studentList = await _teacherRepository.DbContext.Set() .Where(x => classIds.Contains(x.ClassId) && x.SchoolCode == UserContext.Current.TenantId) .ToListAsync(); var studentCountDictionary = studentList .GroupBy(x => x.ClassId) .ToDictionary(g => g.Key, g => g.Count()); // 获取分页数据 var groupedQuery = query .GroupBy(x => new { x.ClassId, Date = x.CreateDate }) .Select(g => new { g.Key.ClassId, g.Key.Date, g.FirstOrDefault().GradeName, g.FirstOrDefault().ClassName, g.FirstOrDefault().TeacherName, g.FirstOrDefault().CategoryValue, FinishNumber = g.Count(), Avg = Math.Round(g.Average(x => x.Value), 0), Max = Math.Round(g.Max(d => d.Value), 0), Min = Math.Round(g.Min(d => d.Value), 0) }); // 计算总记录数（此时是分组后的记录数） var totalCount = await groupedQuery.CountAsync(); // 获取分页数据 var pagedData = await groupedQuery .OrderByDescending(c => c.ClassId) .Skip((paramDto.PageIndex - 1) * paramDto.PageSize) .Take(paramDto.PageSize) .ToListAsync(); var result = pagedData.Select(g => new ClassRoomModeDataModel() { ItemType = g.CategoryValue, ClassId = g.ClassId, StartTime = g.Date, GradeAndClassName = $"{g.GradeName}{g.ClassName}", TeacherName = g.TeacherName, FinishNumber = g.FinishNumber, NotFinishedNumber = studentCountDictionary.ContainsKey(g.ClassId) ? studentCountDictionary[g.ClassId] - g.FinishNumber : 0, Avg = g.Avg, CenterNumber = Math.Round(classValues.Where(x => x.ClassId == g.ClassId && x.CreateDate == g.Date).Select(x => x.Value).ToList().Median(), 0), Max = g.Max, Min = g.Min, ModelName = "/" }).ToList(); // 设置返回值 res.Total = totalCount; res.Datas = result; return res; } /// /// 获取IOT体测数据导出 /// /// /// public async Task> QueryIotTestDataExport(ClassRoomModeExportParam paramDto) { var res = new PageDataDto(); // 查询基础数据 var query = _teacherRepository.DbContext.Set() .Where(s => s.IsDisplay && s.SchoolCode == UserContext.Current.TenantId && s.CategoryValue == paramDto.ItemType); // 添加条件过滤 if (paramDto.StartTime.HasValue) query = query.Where(x => x.CreateDate >= paramDto.StartTime); if (paramDto.EndTime.HasValue) query = query.Where(x => x.CreateDate <= paramDto.EndTime); if (paramDto.ClassId.HasValue) query = query.Where(x => x.ClassId == paramDto.ClassId); if (!paramDto.TeacherName.IsNullOrEmpty()) query = query.Where(x => x.TeacherName.Contains(paramDto.TeacherName ?? "")); // 获取学生人数数据（按班级分组计算总人数） var classValues = await query.Select(x => new { x.ClassId, x.CreateDate, x.Value }).Distinct().ToListAsync(); var classIds = classValues.Select(x => x.ClassId).ToList(); var studentList = await _teacherRepository.DbContext.Set() .Where(x => classIds.Contains(x.ClassId) && x.SchoolCode == UserContext.Current.TenantId) .ToListAsync(); var studentCountDictionary = studentList .GroupBy(x => x.ClassId) .ToDictionary(g => g.Key, g => g.Count()); // 获取分页数据 var groupedQuery = query .GroupBy(x => new { x.ClassId, Date = x.CreateDate }) .Select(g => new { g.Key.ClassId, g.Key.Date, g.FirstOrDefault().GradeName, g.FirstOrDefault().ClassName, g.FirstOrDefault().TeacherName, g.FirstOrDefault().CategoryValue, FinishNumber = g.Count(), Avg = Math.Round(g.Average(x => x.Value), 0), Max = Math.Round(g.Max(d => d.Value), 0), Min = Math.Round(g.Min(d => d.Value), 0) }); // 获取分页数据 var pagedData = await groupedQuery .OrderByDescending(c => c.ClassId) .ToListAsync(); var result = pagedData.Select(g => new ClassRoomModeDataModel() { ItemType = g.CategoryValue, ClassId = g.ClassId, StartTime = g.Date, GradeAndClassName = $"{g.GradeName}{g.ClassName}", TeacherName = g.TeacherName, FinishNumber = g.FinishNumber, NotFinishedNumber = studentCountDictionary.ContainsKey(g.ClassId) ? studentCountDictionary[g.ClassId] - g.FinishNumber : 0, Avg = g.Avg, CenterNumber = Math.Round(classValues.Where(x => x.ClassId == g.ClassId && x.CreateDate == g.Date).Select(x => x.Value).ToList().Median(), 0), Max = g.Max, Min = g.Min, ModelName = "/" }).ToList(); return result; } /// /// 体测数据导出 /// /// /// /// public async Task> SportsTestDataExport(ClassRoomModeExportParam paramDto) { if (paramDto.DataSource == 0) { paramDto.DataSource = GetSportsTestItemTypeDataSource(paramDto.ItemType); } if (paramDto.DataSource == DataSource.IOT) { return await QueryIotTestDataExport(paramDto); } else { return await QueryAiTrainingDataExport(paramDto); } } /// /// 体测数据详情 /// /// /// /// public async Task> GetSportsTestDetails(SportsTestDetailsParam paramDto) { var res = new PageDataDto(); var tenantId = UserContext.Current.TenantId; IQueryable query; if (paramDto.DataSource == 0) { paramDto.DataSource = GetSportsTestItemTypeDataSource((int)paramDto.ItemType); } if (paramDto.DataSource == DataSource.IOT) { query = from s in _teacherRepository.DbContext.Set() where s.IsDisplay && s.SchoolCode == tenantId && s.ClassId == paramDto.ClassId && s.CategoryValue == (int)paramDto.ItemType && s.CreateDate.Value.Year == paramDto.StartTime.Year && s.CreateDate.Value.Month == paramDto.StartTime.Month && s.CreateDate.Value.Day == paramDto.StartTime.Day && s.CreateDate.Value.Hour == paramDto.StartTime.Hour && s.CreateDate.Value.Minute == paramDto.StartTime.Minute && s.CreateDate.Value.Second == paramDto.StartTime.Second select new SportsTestDetailsModel { Id = s.Id, ClassId = s.ClassId, Grade = s.Rank, StudentNo = s.StudentNo, StudentName = s.StudentName ?? "", Sex = (int)s.Sex, Result = (double)s.Value, Height = s.Height, Weight = s.Weight, GradeAndClassName = $"{s.GradeName ?? ""}{s.ClassName ?? ""}", }; } else { query = from trainingData in _teacherRepository.DbContext.Set() where trainingData.IsDisplay && trainingData.SchoolCode == tenantId && trainingData.ClassRoomRecordId == paramDto.Id select new SportsTestDetailsModel { Id = trainingData.Id, ClassId = trainingData.ClassId, Grade = trainingData.Rank, StudentNo = trainingData.StudentNo, StudentName = trainingData.StudentName ?? "", Sex = (int)trainingData.Sex, Result = (double)Math.Round(trainingData.Value), GradeAndClassName = $"{trainingData.GradeName ?? ""}{trainingData.ClassName ?? ""}", }; } if (!paramDto.StudentName.IsNullOrEmpty()) { query = query.Where(x => x.StudentName.Contains(paramDto.StudentName)); } if (!paramDto.StudentNo.IsNullOrEmpty()) { query = query.Where(x => x.StudentNo.Contains(paramDto.StudentNo)); } if (paramDto.Sex.HasValue) { query = query.Where(x => x.Sex == paramDto.Sex); } if (paramDto.ResultLevel.HasValue) { var grade = paramDto.ResultLevel.GetDescription(); if (paramDto.ItemType == SportsTestItemType.BMI) { switch (grade) { case "优秀": grade = "正常"; break; case "及格": grade = "超重"; break; case "良好": grade = "偏瘦"; break; case "不及格": grade = "肥胖"; break; } } query = query.Where(x => x.Grade == grade); // 修改为使用 Rank 过滤 } // 获取总数 res.Total = await query.CountAsync(); // 分页和排序 var list = await query .OrderBy(c => c.Id) .Skip((paramDto.PageIndex - 1) * paramDto.PageSize) .Take(paramDto.PageSize) .ToListAsync(); //if (paramDto.ItemType == SportsTestItemType.BMI) //{ // foreach (var item in list) // { // if (item.Height.HasValue && Math.Abs(item.Height.Value - Math.Round(item.Height.Value, 2)) > 0) // { // item.Height = Math.Round(item.Height.Value, 2); // } // if (item.Weight.HasValue && Math.Abs(item.Weight.Value - Math.Round(item.Weight.Value, 2)) > 0) // { // item.Weight = Math.Round(item.Weight.Value, 2); // } // switch (item.Grade) // { // case "正常": // item.Grade = "优秀"; // break; // case "超重": // item.Grade = "及格"; // break; // case "偏瘦": // item.Grade = "良好"; // break; // case "肥胖": // item.Grade = "不及格"; // break; // } // } //} res.Datas = list; return res; } /// /// 体测数据详情导出 /// /// /// /// public async Task> GetSportsTestDetailsExport(SportsTestExportParam paramDto) { var res = new List(); var tenantId = UserContext.Current.TenantId; IQueryable query; if (paramDto.DataSource == 0) { paramDto.DataSource = GetSportsTestItemTypeDataSource((int)paramDto.ItemType); } if (paramDto.DataSource == DataSource.IOT) { query = from s in _teacherRepository.DbContext.Set() where s.IsDisplay && s.SchoolCode == tenantId && s.ClassId == paramDto.ClassId && s.CategoryValue == (int)paramDto.ItemType && s.CreateDate.Value.Year == paramDto.StartTime.Year && s.CreateDate.Value.Month == paramDto.StartTime.Month && s.CreateDate.Value.Day == paramDto.StartTime.Day && s.CreateDate.Value.Hour == paramDto.StartTime.Hour && s.CreateDate.Value.Minute == paramDto.StartTime.Minute && s.CreateDate.Value.Second == paramDto.StartTime.Second select new SportsTestDetailsModel { Id = s.Id, ClassId = s.ClassId, Grade = s.Rank, StudentNo = s.StudentNo, StudentName = s.StudentName ?? "", Sex = (int)s.Sex, Result = (double)s.Value, GradeAndClassName = $"{s.GradeName ?? ""}{s.ClassName ?? ""}", }; } else { query = from trainingData in _teacherRepository.DbContext.Set() where trainingData.IsDisplay && trainingData.SchoolCode == tenantId && trainingData.ClassRoomRecordId == paramDto.Id select new SportsTestDetailsModel { Id = trainingData.Id, ClassId = trainingData.ClassId, Grade = trainingData.Rank, StudentNo = trainingData.StudentNo, StudentName = trainingData.StudentName ?? "", Sex = (int)trainingData.Sex, Result = (double)trainingData.Value, GradeAndClassName = $"{trainingData.GradeName ?? ""}{trainingData.ClassName ?? ""}", }; } if (!paramDto.StudentName.IsNullOrEmpty()) { query = query.Where(x => x.StudentName.Contains(paramDto.StudentName)); } if (!paramDto.StudentNo.IsNullOrEmpty()) { query = query.Where(x => x.StudentNo.Contains(paramDto.StudentNo)); } if (paramDto.Sex.HasValue) { query = query.Where(x => x.Sex == paramDto.Sex); } if (!string.IsNullOrWhiteSpace(paramDto.ResultLevel)) { query = query.Where(x => x.Grade == paramDto.ResultLevel); // 修改为使用 Rank 过滤 } // 分页和排序 var list = await query .ToListAsync(); if (paramDto.ItemType == SportsTestItemType.BMI) { foreach (var item in list) { if (item.Height.HasValue && Math.Abs(item.Height.Value - Math.Round(item.Height.Value, 2)) > 0) { item.Height = Math.Round(item.Height.Value, 2); } if (item.Weight.HasValue && Math.Abs(item.Weight.Value - Math.Round(item.Weight.Value, 2)) > 0) { item.Weight = Math.Round(item.Weight.Value, 2); } switch (item.Grade) { case "正常": item.Grade = "优秀"; break; case "超重": item.Grade = "及格"; break; case "偏瘦": item.Grade = "良好"; break; case "肥胖": item.Grade = "不及格"; break; } } } res = list; return res; } /// /// 总体数据列表 /// /// /// /// public async Task> GetOverallDataPageList(GetOverallDataPageListParam paramDto) { var classList = await _teacherRepository.DbContext.Set() .Where(x => x.SchoolCode == UserContext.Current.TenantId && x.GradeId == paramDto.GradeId) .ToListAsync(); var classIds = classList.Select(c => c.Id).ToList(); // 获取体测成绩 var list = await _cacheQueryService.GeSportsTestDataCacheAsync (s => (paramDto.DataSource == null || s.DataSource == paramDto.DataSource) && s.GradeId == paramDto.GradeId && classIds.Contains(s.ClassId) ); // 获取年级关联的分类 var categoryList = from s in _teacherRepository.DbContext.Set() join n in _teacherRepository.DbContext.Set() on s.CategoryValue equals n.CategoryValue where s.GradeId == paramDto.GradeId select new { s.CategoryValue, n.CategoryName, s.Weight }; // 获取所有与条件匹配的学生 var stuNos = list.Select(s => s.StudentNo).Distinct().ToList(); // 初始化查询 var query = _teacherRepository.DbContext.Set() .Where(x => stuNos.Contains(x.StudentNo) && x.SchoolCode == UserContext.Current.TenantId && classIds.Contains(x.ClassId)) .AsQueryable(); // 动态添加条件 if (paramDto.ClassId > 0) { query = query.Where(s => s.ClassId == paramDto.ClassId); } if (!string.IsNullOrEmpty(paramDto.StudentNo)) { query = query.Where(s => s.StudentNo == paramDto.StudentNo); } if (!string.IsNullOrEmpty(paramDto.StudentName)) { query = query.Where(s => s.StudentName.Contains(paramDto.StudentName)); } if (paramDto.Sex.HasValue) { query = query.Where(s => s.Sex == (SexType)paramDto.Sex); } // 执行查询并进行分页 var stuList = await query .Skip((paramDto.PageIndex - 1) * paramDto.PageSize) .Take(paramDto.PageSize) .Select(s => new { s.ClassId, s.StudentName, s.StudentNo, s.Sex }) .ToListAsync(); // 准备返回的动态列表 var result = new List(); foreach (var stu in stuList) { dynamic row = new ExpandoObject(); var classStu = classList.FirstOrDefault(i => i.Id == stu.ClassId); row.GradeNameAndClassName = $"{classStu?.GradeName}{classStu?.ClassName}"; row.StudentName = stu.StudentName; row.StudentNo = stu.StudentNo; row.Sex = stu.Sex == SexType.Male ? "男" : "女"; // 用于累积 currentScore 的变量 double totalScore = 0; foreach (var category in categoryList) { // 获取与该学生和分类匹配的体测数据 var data = list.FirstOrDefault(i => i.CategoryValue == category.CategoryValue && i.StudentNo == stu.StudentNo); double currentValue = data?.Value ?? 0; double currentScore = data?.Score ?? 0; double currentAdditionalScore = data?.AdditionalScore ?? 0; // 将分类的体测数据值赋给动态对象 ((IDictionary)row)[category.CategoryName] = currentValue; // 累积分数 totalScore += ((currentScore + currentAdditionalScore) * category.Weight); } // 计算总分 row.TotalScore = totalScore; //// 计算 Rank //var rank = totalScore / (categoryList.Count() * 100) * 100; row.Rank = totalScore.GetRank(); result.Add(row); } // 构造分页数据 var pageData = new PageDataDto { Total = await query.CountAsync(), Datas = result }; return pageData; } /// /// 总体数据导出 /// /// /// public async Task>> GetOverallDataExport(GetOverallDataExportParam paramDto) { var classList = await _teacherRepository.DbContext.Set() .Where(x => x.SchoolCode == UserContext.Current.TenantId && x.GradeId == paramDto.GradeId) .ToListAsync(); var classIds = classList.Select(c => c.Id).ToList(); // 获取体测成绩 var list = await _cacheQueryService.GeSportsTestDataCacheAsync (s => (paramDto.DataSource == null || s.DataSource == paramDto.DataSource) && s.GradeId == paramDto.GradeId && classIds.Contains(s.ClassId) ); // 获取年级关联的分类 var categoryList = from s in _teacherRepository.DbContext.Set() join n in _teacherRepository.DbContext.Set() on s.CategoryValue equals n.CategoryValue where s.GradeId == paramDto.GradeId select new { n.CategoryEnum, s.CategoryValue, n.CategoryName, s.Weight }; // 获取所有涉及的学生 var stuNos = list.Select(s => s.StudentNo).Distinct().ToList(); // 初始化查询 var query = _teacherRepository.DbContext.Set() .Where(x => stuNos.Contains(x.StudentNo) && x.SchoolCode == UserContext.Current.TenantId && classIds.Contains(x.ClassId)) .AsQueryable(); // 动态添加条件 if (paramDto.ClassId > 0) { query = query.Where(s => s.ClassId == paramDto.ClassId); } if (!string.IsNullOrEmpty(paramDto.StudentNo)) { query = query.Where(s => s.StudentNo == paramDto.StudentNo); } if (!string.IsNullOrEmpty(paramDto.StudentName)) { query = query.Where(s => s.StudentName.Contains(paramDto.StudentName)); } if (paramDto.Sex.HasValue) { query = query.Where(s => s.Sex == (SexType)paramDto.Sex); } // 执行查询并进行分页 var stuList = await query .Select(s => new { s.ClassId, s.StudentName, s.StudentNo, s.Sex }) .ToListAsync(); // 准备返回的动态列表 var result = new List>(); foreach (var stu in stuList) { var row = new Dictionary(); // 获取该学生的体测数据 var iotStu = list.FirstOrDefault(i => i.StudentNo == stu.StudentNo); row["学籍号"] = stu.StudentNo; row["姓名"] = stu.StudentName; row["性别"] = stu.Sex == SexType.Male ? "男" : "女"; row["年级-班级"] = $"{iotStu?.GradeName ?? string.Empty}{iotStu?.ClassName ?? string.Empty}"; // 用于累积 currentScore 的变量 double totalScore = 0; foreach (var category in categoryList) { var data = list.FirstOrDefault(i => i.CategoryValue == category.CategoryValue && i.StudentNo == stu.StudentNo); double currentValue = data?.Value ?? 0; double currentScore = data?.Score ?? 0; double currentAdditionalScore = data?.AdditionalScore ?? 0; // BMI相关数据 if (category.CategoryValue == (int)SportsTestItemType.BMI) { var height = list.FirstOrDefault(i => i.CategoryEnum == "Height" && i.StudentNo == stu.StudentNo); var weight = list.FirstOrDefault(i => i.CategoryEnum == "Weight" && i.StudentNo == stu.StudentNo); row["身高（cm）"] = height?.Value.ToString("F2") ?? "0"; row["体重（kg）"] = weight?.Value.ToString("F2") ?? "0"; } // 根据导出类型处理 row[category.CategoryName] = paramDto.ExportType switch { OverallDataExportType.Result => currentValue.ToString("F2"), OverallDataExportType.ScoreAndRank => $"{currentScore:F2} {data?.Rank}", OverallDataExportType.AdditionalScore => currentAdditionalScore.ToString("F2"), _ => string.Empty }; // 累积 currentScore totalScore += ((currentScore + currentAdditionalScore) * category.Weight); } // 计算总分和等级 if (paramDto.ExportType == OverallDataExportType.ScoreAndRank) { // 计算总分 row["测试总成绩"] = totalScore.ToString("F2"); // 计算 Rank //var rank = totalScore / (categoryList.Count() * 100) * 100; row["等级"] = totalScore.GetRank(); } result.Add(row); } return result; } #endregion #region 视力记录 /// /// 导入视力记录 /// /// /// public async Task ImportVisionData(IFormFile file) { if (file == null || file.Length <= 0) throw new Exception("操作失败"); var tenantId = UserContext.Current.TenantId; var dataObjects = new List(); using (var fileStream = file.OpenReadStream()) { dataObjects = Tool.ConvertExcelToList(fileStream); } var greadNames = dataObjects.Select(x => x.GradeName).Distinct().ToList(); var studentNos = dataObjects.Select(x => x.StudentNo).Distinct().ToList(); var greadList = await _sportsTestCategoryRepository.DbContext.Set().Where(x => greadNames.Contains(x.GradeName)).ToListAsync(); var greadIds = greadList.Select(x => x.Id).ToList(); var classList = await _sportsTestCategoryRepository.DbContext.Set().Where(x => greadIds.Contains(x.GradeId) && x.SchoolCode == tenantId).ToListAsync(); var studentList = await _sportsTestCategoryRepository.DbContext.Set().Where(x => studentNos.Contains(x.StudentNo)).ToListAsync(); var dbEntitys = await _sportsTestCategoryRepository.DbContext.Set().Where(x => studentNos.Contains(x.StudentNo)).ToListAsync(); var entitys = new List(); var updateEntitys = new List(); foreach (var data in dataObjects) { var greadModel = greadList.Where(x => x.GradeName.Equals(data.GradeName)).FirstOrDefault(); if (greadModel == null) throw new Exception("未找到年级信息"); var classModel = classList.Where(x => x.GradeId == greadModel.Id && x.ClassName == data.ClassName).FirstOrDefault(); if (classModel == null) throw new Exception("未找到班级信息"); var studentModel = studentList.Where(x => x.StudentNo.Equals(data.StudentNo) && x.ClassId == classModel.Id).FirstOrDefault(); if (studentModel == null) throw new Exception("未找到学生信息"); var dbEntity = dbEntitys.Where(x => x.StudentNo.Equals(data.StudentNo) && x.ClassId.Equals(classModel.Id) && x.GradeId.Equals(greadModel.Id)).FirstOrDefault(); if (dbEntity != null) { dbEntity.VisionLeft = data.VisionLeft; dbEntity.VisionReight = data.VisionReight; dbEntity.ImportDate = DateTime.Now; dbEntity.Modifier = UserContext.Current.UserId; dbEntity.ModifyDate = DateTime.Now; updateEntitys.Add(dbEntity); } else { var studentEntity = new N_VisionData() { SchoolCode = tenantId, StudentName = studentModel.StudentName, StudentNo = studentModel.StudentNo, ClassId = classModel.Id, ClassName = data.ClassName, GradeId = greadModel.Id, GradeName = greadModel.GradeName, ImportDate = DateTime.Now, VisionLeft = data.VisionLeft, VisionReight = data.VisionReight, Creator = UserContext.Current.UserId, CreateDate = DateTime.Now }; entitys.Add(studentEntity); } } await _sportsTestCategoryRepository.DbContext.Set().AddRangeAsync(entitys); _sportsTestCategoryRepository.DbContext.Set().UpdateRange(updateEntitys); await _sportsTestCategoryRepository.SaveChangesAsync(); } /// /// 视力记录列表 /// /// /// public async Task> GetVisionDataList(VisionDataListParam paramDto) { var res = new PageDataDto(); var query = from vd in _teacherRepository.DbContext.Set() where vd.SchoolCode.Equals(UserContext.Current.TenantId) select new VisionDataModel() { Id = vd.Id, ClassId = vd.ClassId, GradeAndClass = $"{vd.GradeName}-{vd.ClassName}", StudentName = vd.StudentName, VisionLeft = vd.VisionLeft, VisionReight = vd.VisionReight, ImportDate = vd.ImportDate.ToString("yyyy-MM-dd HH:mm") }; if (paramDto.ClassId.HasValue) { query = query.Where(x => x.ClassId.Equals(paramDto.ClassId)); } if (!paramDto.StudentName.IsNullOrEmpty()) { query = query.Where(x => x.StudentName.Contains(paramDto.StudentName ?? "")); } res.Total = await query.CountAsync(); var list = await query .OrderBy(c => c.Id) .Skip((paramDto.PageIndex - 1) * paramDto.PageSize) .Take(paramDto.PageSize) .ToListAsync(); res.Datas = list; return res; } #endregion #region 课堂报告 /// /// 课堂记录列表 /// /// /// public async Task> GetClassReportList(ClassReportListParam paramDto) { var res = new PageDataDto(); var query = from td in _teacherRepository.DbContext.Set() where td.SchoolCode.Equals(UserContext.Current.TenantId) select new ClassReportModel() { Id = td.Id, ClassId = td.ClassId, GradeAndClass = $"{td.GradeName}-{td.ClassName}", TeacherName = td.TeacherName, StartingEndingTime = $"{(td.StartTime.HasValue ? td.StartTime.Value.ToString("yyyy-MM-dd HH:mm") : string.Empty)}{(td.EndTime.HasValue ? " - " + td.EndTime.Value.ToString("yyyy-MM-dd HH:mm") : string.Empty)}" }; if (paramDto.ClassId.HasValue) { query = query.Where(x => x.ClassId.Equals(paramDto.ClassId)); } if (!paramDto.TeacherName.IsNullOrEmpty()) { query = query.Where(x => x.TeacherName.Contains(paramDto.TeacherName ?? "")); } res.Total = await query.CountAsync(); var list = await query .OrderByDescending(c => c.Id) .Skip((paramDto.PageIndex - 1) * paramDto.PageSize) .Take(paramDto.PageSize) .ToListAsync(); res.Datas = list; return res; } /// /// 课堂报告详情 /// /// public async Task GetClassReportDetails(int id) { var res = new GetClassReportDetailsModel(); var query = from hrd in _teacherRepository.DbContext.Set() //where hrd.SchoolCode.Equals(UserContext.Current.TenantId) && hrd.ClassRoomRecordId.Equals(id) && hrd.Strength > 0 where hrd.SchoolCode.Equals(UserContext.Current.TenantId) && hrd.ClassRoomRecordId == id select hrd; var heartRateDataList = await query.ToListAsync(); if (heartRateDataList.Count == 0) return res; var classRoom = await _teacherRepository.DbContext.Set().Include(x => x.ClassroomStudentRecord) .Where(x => x.SchoolCode == UserContext.Current.TenantId && x.Id == id) .FirstAsync(); var classRoomStudent = await _teacherRepository.DbContext.Set() .Where(x => x.SchoolCode == UserContext.Current.TenantId && x.ClassRoomRecordId == id) .ToListAsync(); res.GradeAndClass = $"{classRoom.GradeName}-{classRoom.ClassName}"; res.TeacherName = classRoom.TeacherName; res.StartingEndingTime = $"{(classRoom.StartTime.HasValue ? classRoom.StartTime.Value.ToString("yyyy-MM-dd HH:mm") : string.Empty)}{(classRoom.EndTime.HasValue ? " - " + classRoom.EndTime.Value.ToString("yyyy-MM-dd HH:mm") : string.Empty)}"; res.PeopleNumber = $"{heartRateDataList.GroupBy(x => x.StudentNo).Count()}/{classRoomStudent.Count}"; res.PeopleNumberBySex = $"{classRoomStudent.Count(x => x.Sex == SexType.Male)}/{classRoomStudent.Count(x => x.Sex == SexType.Female)}"; res.AvgHR = $"{(int)(heartRateDataList.Sum(x => x.Value) / heartRateDataList.Count)} 次/分"; res.Consumption = $"{Math.Abs(heartRateDataList.Sum(x => x.Consumption ?? 0) / heartRateDataList.Count)} 千卡"; //res.Density = $"{(int)(heartRateDataList.Where(x => x.Strength > 50).Sum(x => x.Strength) / heartRateDataList.Count)} %"; res.Density = $"{(int)CalculatePercentage(heartRateDataList.Count(x => x.Strength > 50), heartRateDataList.Count)} %"; res.HighIntensity = $"{heartRateDataList.Where(x => x.Strength > 50).GroupBy(x => x.StudentNo).Count()} 人"; var studentTrainingRecordList = classRoom.ClassroomStudentRecord.ToList(); List studentList = new List(); foreach (var item in studentTrainingRecordList) { var studentTrainingData = heartRateDataList.Where(x => x.StudentNo == item.StudentNo).ToList(); if (studentTrainingData.Count > 0) { var student = new StudentTrainingRecordDto() { StudentNo = item.StudentNo, Age = item.Age, Sex = item.Sex }; student.StudentName = studentTrainingData[0].StudentName; student.AvgHR = (int)(studentTrainingData.Sum(x => x.Value) / studentTrainingData.Count); student.Density = (int)CalculatePercentage(studentTrainingData.Count(x => x.Strength > 50), studentTrainingData.Count); student.Strength = (int)studentTrainingData.Average(x => x.Strength); student.Consumption = Math.Abs((int)studentTrainingData.Average(x => x.Consumption ?? 0)); studentList.Add(student); } } res.HeartRateNumber = GetHeartRateNumber(heartRateDataList); res.HeartRateTrend = GetHeartRateTrend(heartRateDataList); res.StudentTrainingRecordList = studentList; //return new GetClassReportDetailsModel() //{ // HeartRateNumber = GetHeartRateNumber(heartRateDataList), // HeartRateTrend = GetHeartRateTrend(heartRateDataList), // HeartRateIntensityNumber = GetHeartRateIntensityNumber(heartRateDataList), // TimeIntervalHeartRateIntensityNumber = GetTimeIntervalHeartRateIntensityNumber(heartRateDataList) //}; return res; } /// /// 学生课堂报告详情 /// /// /// public async Task GetStudentClassReportDetails(GetStudentClassReportDetailsDto paramDto) { var res = new GetStudentClassReportDetailsModel(); var schoolCode = UserContext.Current.TenantId; var query = from hrd in _teacherRepository.DbContext.Set() where hrd.SchoolCode == schoolCode && hrd.ClassRoomRecordId == paramDto.ClassRoomRecordId && hrd.StudentNo == paramDto.StudentNo select hrd; var heartRateDataList = await query.ToListAsync(); if (heartRateDataList.Count == 0) return res; var student = heartRateDataList[0]; res.StudentNo = student.StudentNo; res.StudentName = student.StudentName; res.Sex = student.Sex; res.GradeAndClass = $"{student.GradeName}-{student.ClassName}"; res.AvgHR = (int)(heartRateDataList.Sum(x => x.Value) / heartRateDataList.Count); res.Density = (int)CalculatePercentage(heartRateDataList.Count(x => x.Strength > 50), heartRateDataList.Count); res.Strength = (int)heartRateDataList.Average(x => x.Strength); res.Consumption = Math.Abs((int)heartRateDataList.Average(x => x.Consumption ?? 0)); //var baseTime = heartRateDataList.Min(x => x.ScoreTime); var baseTime = await _teacherRepository.DbContext.Set().Where(x => x.SchoolCode == schoolCode && x.ClassRoomRecordId == paramDto.ClassRoomRecordId).MinAsync(x => x.ScoreTime); var heartRateWithMinutes = heartRateDataList .Select(data => new { Data = data, MinuteBucket = (int)(data.ScoreTime - baseTime).TotalMinutes }) .ToList(); var maxMinute = heartRateWithMinutes.Max(x => x.MinuteBucket); for (int minute = 0; minute <= maxMinute; minute++) { var minuteData = heartRateWithMinutes .Where(x => x.MinuteBucket == minute) .Select(x => x.Data) .ToList(); if (minuteData.Any()) { res.HeartRateTrend.Add(new SportsProportionData() { Name = $"{minute + 1} 分钟", Datas = new List { new StudentSportsProportionData { Title = "心率", Value = (int)minuteData.Average(x => x.Value) } } }); } } return res; } /// /// 心率个数 /// /// /// public List GetHeartRateNumber(List heartRateDataList) { var result = new List(); var maleData = heartRateDataList.Where(x => x.Sex == SexType.Male).ToList(); var femaleData = heartRateDataList.Where(x => x.Sex == SexType.Female).ToList(); var m_avg = maleData.Any() ? (int)maleData.Average(x => x.Value) : 0; var f_avg = femaleData.Any() ? (int)femaleData.Average(x => x.Value) : 0; var avgData = new SportsProportionData { Name = "平均", Datas = new List { new StudentSportsProportionData { Title = "男", Value = m_avg }, new StudentSportsProportionData { Title = "女", Value = f_avg } } }; var m_max = maleData.Any() ? maleData.Max(x => x.Value) : 0; var f_max = femaleData.Any() ? femaleData.Max(x => x.Value) : 0; var maxData = new SportsProportionData { Name = "最高", Datas = new List { new StudentSportsProportionData { Title = "男", Value = m_max }, new StudentSportsProportionData { Title = "女", Value = f_max } } }; var m_min = maleData.Any() ? maleData.Min(x => x.Value) : 0; var f_min = femaleData.Any() ? femaleData.Min(x => x.Value) : 0; var minData = new SportsProportionData { Name = "最低", Datas = new List { new StudentSportsProportionData { Title = "男", Value = m_min }, new StudentSportsProportionData { Title = "女", Value = f_min } } }; result.Add(avgData); result.Add(minData); result.Add(maxData); return result; } /// /// 心率变化趋势 /// /// /// public List GetHeartRateTrend(List heartRateDataList) { var result = new List(); if (heartRateDataList == null || !heartRateDataList.Any()) return result; var baseTime = heartRateDataList.Min(x => x.ScoreTime); var heartRateWithMinutes = heartRateDataList .Select(data => new { Data = data, MinuteBucket = (int)(data.ScoreTime - baseTime).TotalMinutes }) .ToList(); var maxMinute = heartRateWithMinutes.Max(x => x.MinuteBucket); for (int minute = 0; minute <= maxMinute; minute++) { var minuteData = heartRateWithMinutes .Where(x => x.MinuteBucket == minute) .Select(x => x.Data) .ToList(); if (minuteData.Any()) { result.Add(new SportsProportionData() { Name = $"{minute + 1} 分钟", // Adding 1 to make it 1-based instead of 0-based Datas = new List { new StudentSportsProportionData { Title = "心率", Value = (int)minuteData.Average(x => x.Value) } } }); } } return result; } /// /// 心率各强度达成人数 /// /// /// public List GetHeartRateIntensityNumber(List heartRateDataList) { // 初始化返回结果 var result = new List(); // 定义强度区间 var intensityRanges = new List<(string Name, string Title, double Min, double Max)> { ("区间(<50% MHR)" ,"基本热身", 0, 50), ("区间(50~60% MHR)" ,"低等强度", 50, 60), ("区间(60~70% MHR)" ,"中等强度", 60, 70), ("区间(70~85% MHR)" ,"高等强度", 70, 85), ("区间(>85% MHR)" ,"安全预警", 85, 100) }; // 针对每个强度区间计算学生数量 foreach (var range in intensityRanges) { var sportsData = new SportsProportionData { Name = range.Name, // 设置区间名称 Datas = new List() }; // 筛选出符合当前强度区间的数据 var intensityData = heartRateDataList .Where(hrd => hrd.Strength >= range.Min && hrd.Strength < range.Max) .DistinctBy(x => x.StudentNo) .ToList(); // 计算符合该区间的学生人数 int studentCountInRange = intensityData.Count; sportsData.Datas.Add(new StudentSportsProportionData { Title = range.Title, Value = studentCountInRange }); result.Add(sportsData); } return result; } /// /// 各时间区间中高强度人数 /// /// /// public List GetTimeIntervalHeartRateIntensityNumber(List heartRateDataList) { // 初始化返回结果 var result = new List(); // 获取基准时间 var baseTime = heartRateDataList.Min(x => x.ScoreTime); // 将心率数据按分钟转换 var heartRateWithMinutes = heartRateDataList .Select(data => new { Data = data, Minutes = (data.ScoreTime - baseTime).TotalMinutes }) .ToList(); // 计算最大时间值 var maxMinutes = heartRateWithMinutes.Max(x => x.Minutes); // 每10分钟一个区间 for (int i = 1; i <= Math.Ceiling(maxMinutes / 10.0); i++) { var upperBound = i * 10; var lowerBound = (i - 1) * 10; var sportsData = new SportsProportionData { Name = $"{lowerBound}-{upperBound}分钟", // 时间区间 Datas = new List() }; // 筛选出符合当前时间区间的数据 var timeIntervalData = heartRateWithMinutes .Where(hrd => hrd.Minutes > lowerBound && hrd.Minutes <= upperBound) .Select(hrd => hrd.Data) .ToList(); // 计算中等强度和高等强度人数 int moderateIntensityCount = timeIntervalData.Where(hrd => hrd.Strength >= 60 && hrd.Strength < 70).DistinctBy(x => x.StudentNo).Count(); int highIntensityCount = timeIntervalData.Where(hrd => hrd.Strength >= 70 && hrd.Strength < 85).DistinctBy(x => x.StudentNo).Count(); // 计算总人数 int totalCount = moderateIntensityCount + highIntensityCount; // 添加总人数数据 sportsData.Datas.Add(new StudentSportsProportionData { Title = "总人数", Value = totalCount }); // 添加中等强度人数数据 sportsData.Datas.Add(new StudentSportsProportionData { Title = "中等强度人数", Value = moderateIntensityCount }); // 添加高等强度人数数据 sportsData.Datas.Add(new StudentSportsProportionData { Title = "高等强度人数", Value = highIntensityCount }); result.Add(sportsData); } return result; } /// /// 通用百分比计算方法 /// /// /// /// private double CalculatePercentage(int count, int totalCount) { if (totalCount == 0) return 0; return Math.Round((double)count / totalCount * 100, 0); } #endregion #region 用户训练数据 /// /// 获取用户训练数据列表 /// /// 查询参数 /// 用户训练数据列表 public async Task> GetUserTrainingDataList(UserTrainingDataQueryParam paramDto) { var res = new PageDataDto(); var tenantId = UserContext.Current.TenantId; // 查询用户训练数据 var query = from td in _teacherRepository.DbContext.Set() join sd in _teacherRepository.DbContext.Set() on td.Id equals sd.TrainingDataId where td.SchoolCode == tenantId && td.TeacherId == paramDto.UserId select new UserTrainingDataModel { Id = td.Id, TrainingType = td.ItemType, TrainingTypeName = td.ItemType == 1 ? "个人" : "团队", ModeType = td.ModeType, ModeTypeName = td.ModelName, Duration = td.TrainTimer.GetValueOrDefault() / 60, JumpCount = sd.JumpValue, Kcal = sd.Kcal, TrainingTime = td.ClassTime }; // 训练类型筛选（可选） if (paramDto.TrainingType.HasValue) { query = query.Where(x => x.TrainingType == paramDto.TrainingType); } // 训练模式筛选（可选） if (paramDto.ModeType.HasValue) { query = query.Where(x => x.ModeType == paramDto.ModeType); } // 时间范围筛选（可选） if (paramDto.StartTime.HasValue) { var startTimeStr = paramDto.StartTime.Value.ToString("yyyy-MM-dd"); query = query.Where(x => EF.Functions.Like(x.TrainingTime, $"{startTimeStr}%") || x.TrainingTime.CompareTo(startTimeStr) > 0); } if (paramDto.EndTime.HasValue) { var endTimeStr = paramDto.EndTime.Value.ToString("yyyy-MM-dd"); query = query.Where(x => EF.Functions.Like(x.TrainingTime, $"{endTimeStr}%") || x.TrainingTime.CompareTo(endTimeStr) < 0); } // 获取总记录数 res.Total = await query.CountAsync(); // 分页查询数据 var list = await query .OrderByDescending(x => x.TrainingTime) .Skip((paramDto.PageIndex - 1) * paramDto.PageSize) .Take(paramDto.PageSize) .ToListAsync(); res.Datas = list; return res; } #endregion } }