.net core linq mysql_.NetCore 3.1 WebApi 使用Mysql临时表Dapper与Linq处理报表

1 public async Task TheMostVolatileClassGetStudentExamInfo(string uni_id, int level, int gp_id, int gc_id, intga_id)2 {3 //--AND M.ttm_date >= @sd AND M.ttm_date <= @ed 暂时注释，方便测试

4 var data = newTheMostUnitAndClassStudentDataShowokFormart5 {6 examNames = new List(),7 studentEveryExamTotalScore = new List(),8 studentName = new List()9 };10 var listdata = new List();11 if (!uni_id.IsNullOrEmptyStr())//校级调用的时候,求波动最大的班级

12 {13 string sql = @"DROP temporary TABLE if EXISTS mytempdatatable01;14 CREATE TEMPORARY table mytempdatatable0115 SELECT tu.unit_session_uid,us.unit_session_sdate,us.unit_session_edate FROM unit tu16 INNER JOIN unit_session us ON tu.uni_id=us.uni_id AND us.unit_session_type='1'17 WHERE tu.uni_id=@uni_id AND tu.isdel=0 and us.isdel=0 LIMIT 1;18 SET @sd='1990-01-01',@ed='1990-01-01';19 SET @sd=(SELECT unit_session_sdate FROM mytempdatatable01);20 SET @ed=(SELECT unit_session_edate FROM mytempdatatable01);21

22 SELECT distinct23 U.ttm_id,U.ttm_name,U.uni_id ,U.stt_avgscore ,U.stt_id,U.class_uid24 FROM(25 SELECT distinct26 M.ttm_id,M.ttm_name,D.class_uid,M.uni_id,SM.cou_id ,SM.stt_avgscore,TD.stt_id,C.class_level27 FROM test_main M28 LEFT JOIN test_detail D ON M.ttm_id=D.ttm_id29 LEFT JOIN school_test_transcripts SM ON M.ttm_id=SM.ttm_id30 LEFT JOIN school_test_transcripts_detail TD ON SM.stt_id=TD.stt_id31 INNER JOIN class C ON D.class_uid=C.class_uid32 WHERE 1=133 AND M.uni_id=@uni_id34 AND SM.uni_id=@uni_id35 AND M.isdel=0 AND D.isdel=0 AND TD.isdel=0 AND TD.score_type=036 -- AND M.ttm_date>=@sd AND M.ttm_date<=@ed37 GROUP BY M.uni_id,M.ttm_id,M.ttm_name,TD.stt_id38 )U39 INNER JOIN unit_session S ON U.uni_id=S.uni_id AND S.unit_session_type=140 AND U.stt_avgscore IS NOT null41 AND U.uni_id=@uni_id42 WHERE U.class_level=@class_level43 GROUP BY U.uni_id, U.ttm_id,U.ttm_name,U.stt_id;";44 var modelFirst = await ZRF_DapperHelper.QueryAsync(sql, new { uni_id = uni_id, class_level =level });45 if (modelFirst == null || !modelFirst.Any())46 returndata;47

48 var ttm_ids = modelFirst.Select(c => c.ttm_id).Distinct().ToList();//有多少次 ttm_id考试

49

50 var tempDic = new Dictionary();51 var tempEveryStudentScoreInfoList = new List();52 for (int m = 0; m < ttm_ids.Count; m++)53 {54 //每次考试有多少班级考试的次数，可能是同一个班级考试

55 var everyExamStudentCoreInfo = (await ZRF_DapperHelper.QueryAsync(@"

56 SELECT57 M.ttm_id,D.stt_id,D.per_id,D.sttd_score,P.per_name58 FROM59 school_test_transcripts M60 LEFT join school_test_transcripts_detail D on M.stt_id=D.stt_id61 INNER JOIN personnel P ON D.per_id=P.per_id AND P.isdel=062 WHERE M.isdel=0 AND D.isdel=0 AND M.ttm_id=@ttm_id GROUP BY D.stt_id,D.per_id,D.sttd_score", new { ttm_id =ttm_ids[m] })).ToList();63 if (everyExamStudentCoreInfo != null &&everyExamStudentCoreInfo.Any())64 {65 tempEveryStudentScoreInfoList.AddRange(everyExamStudentCoreInfo);66 var examCout = everyExamStudentCoreInfo.Select(c => c.stt_id).Distinct().ToList();//其中考试 考了几次

67 var disticntStudentPer_Id = everyExamStudentCoreInfo.Select(c => c.per_id).Distinct().ToList();//这一批考试的所有学生去重

68 decimal getThisExamAvgScore = everyExamStudentCoreInfo.Select(c => c.sttd_score).Average();//这次考试的平均分

69 for (int s = 0; s < disticntStudentPer_Id.Count; s++)//求每一个学生的标准差

70 {71 string thisStudent = disticntStudentPer_Id[s];//这个学生的per_id72 //string studentName = everyExamStudentCoreInfo.FirstOrDefault(c => c.per_id == thisStudent).per_name;73 //decimal totalScore = everyExamStudentCoreInfo.Where(c => c.per_id == thisStudent).Select(c => c.sttd_score).Sum();//这个学生的总分

74 decimal bzc = 0;75 List thisStudentScore = everyExamStudentCoreInfo.Where(c => c.per_id == thisStudent).Select(c =>c.sttd_score).ToList();76 for (int studentScorecount = 0; studentScorecount < thisStudentScore.Count; studentScorecount++)//该考了几次考试

77 {78 bzc += (thisStudentScore[studentScorecount] - getThisExamAvgScore) * (thisStudentScore[studentScorecount] -getThisExamAvgScore);79 }80 double tempbzf = double.Parse(bzc.ToString()) * 1.0 /examCout.Count;81

82 bzc = decimal.Parse(Math.Sqrt(tempbzf).ToString("f2"));83 //listper_id.Add($"{thisStudent}_{ttm_ids[m]}_{studentName}", bzc);

84 if (!tempDic.ContainsKey(thisStudent))85 {86 tempDic.Add(thisStudent, bzc);87 }88 }89 }90 }91 var dictinctDicStudent = tempDic.OrderByDescending(c => c.Value).Take(5).ToList();//不重复的5个学生

92

93 List examName = new List();//考试的名称

94 List studentName = new List();//学生名称

95 List top5Score = new List();//学生分数

96

97 foreach (var item indictinctDicStudent)98 {99 string per_id =item.Key;100 string per_name = tempEveryStudentScoreInfoList.FirstOrDefault(c => c.per_id ==per_id).per_name;101 studentName.Add(per_name);102

103 List everyExamScoreForStudent = new List();104 for (int i = 0; i < ttm_ids.Count; i++)105 {106 string exam_Name = modelFirst.FirstOrDefault(c => c.ttm_id ==ttm_ids[i]).ttm_name;107 if (!examName.Contains(exam_Name))108 {109 examName.Add(exam_Name);110 }111 everyExamScoreForStudent.Add(tempEveryStudentScoreInfoList.Where(c => c.per_id == per_id && c.ttm_id == ttm_ids[i]).Select(c =>c.sttd_score).Sum());112 }113 top5Score.Add(everyExamScoreForStudent.ToArray());114 }115

116 #region MyRegion 存在重复的逻辑

117 //var diclist = listper_id.OrderByDescending(c => c.Value).Take(5).ToList();118

119 //List top5Studentid = new List();//学生id120 //List top5Score = new List();//学生分数121 //List examName = new List();

122 5个学生

123 //diclist.Select(c => c.Key).ToList().ForEach(c =>124 //{125 //string ttm_id = c.Split("_")[1];126 //string per_id = c.Split("_")[0];127 //string _examName = modelFirst.FirstOrDefault(c => c.ttm_id == ttm_id).ttm_name;128 //top5Studentid.Add(c.Split("_")[2]);129 //if (!examName.Contains(_examName))130 //{131 //examName.Add(_examName);132 //}133 //var lscore = new List();134 //for (int i = 0; i < ttm_ids.Count; i++)135 //{136 //decimal totalscore = tempEveryStudentScoreInfoList.Where(c => c.ttm_id == ttm_ids[i] && c.per_id == per_id).Select(c => c.sttd_score).Sum();137 //lscore.Add(totalscore);138 //}139 //top5Score.Add(lscore.ToArray());140 //});

141 #endregion

142

143 data.examNames =examName;144 data.studentName =studentName;145 data.studentEveryExamTotalScore =top5Score;146 }147 returndata;148 }