仅仅作为自己的学习使用笔记
eg:
eg:
/// <summary> /// *************学手段 /// </summary> /// <param name="subjectId"></param> /// <param name="secId"></param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> public List<ShowTeacherDevicesResult> GetTeachingDevices(string subjectId, string secId, string startDate, string endDate) { if (string.IsNullOrEmpty(subjectId) || string.IsNullOrEmpty(secId)) { return null; } //班级记录 var classesLogs = _classesLogic.GetClassLog(); var functionRecords = _guideLearnLogic.GetFunctionRecord(); //**信息 var personnel = _personLogic.GetPersonnel().Where(s => s.isdel == 0); //**信息 var subjects = _subjectLogic.GetSubject().Where(s => s.isdel == 0); if (classesLogs != null) { var data = from L in classesLogs join R in functionRecords on L.cll_id equals R.cll_id into tempR from RData in tempR.DefaultIfEmpty() join P in personnel on L.per_id equals P.per_id into tempP from Pdata in tempP.DefaultIfEmpty() join S in subjects on L.sub_id equals S.sub_id into tempS from SData in tempS.DefaultIfEmpty() where SData.sub_id == subjectId && Pdata.isdel == 0 && SData.isdel == 0 select new ShowTeachingDevices { per_id = Pdata.per_id, per_name = Pdata.per_name, sub_name = SData.sub_name, fcd_code = RData.fcd_code.StartsWith("C") ? "C" : ( RData.fcd_code.StartsWith("D") ? "D" : "ELSE"), sub_id = SData.sub_id, sec_id = SData.sec_id, cll_cdate = L.cll_cdate, }; if (!string.IsNullOrEmpty(subjectId)) { data = data.Where(c => c.sub_id == subjectId); } if (!string.IsNullOrEmpty(secId)) { data = data.Where(c => c.sec_id == secId); } //时间搜索 if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate)) { var formatStartDate = DateTime.Parse(startDate); var formatEndDate = DateTime.Parse(endDate); data = data.Where(c => c.cll_cdate >= formatStartDate && c.cll_cdate <= formatEndDate); } /* SELECT per_id,per_name, SUM(count_num),fcd_code FROM( SELECT P.per_id,P.per_name, COUNT(1) AS count_num, CASE WHEN R.fcd_code LIKE 'C%' THEN 'C' WHEN R.fcd_code LIKE 'D%' THEN 'D' ELSE 'ELSE' END fcd_code FROM class_log L LEFT JOIN function_record R ON R.cll_id=L.cll_id LEFT JOIN personnel P ON L.per_id= P.per_id LEFT JOIN subject S ON L.sub_id=S.sub_id WHERE L.sub_id='95301ab4-1e5f-11e5-b161-1c4bd611580c' AND L.cll_cdate BETWEEN '2016-01-10' AND '2020-01-10' GROUP BY P.per_id,R.fcd_code ORDER BY L.cll_cdate DESC )T GROUP BY per_id,fcd_code; */ data = data.OrderByDescending(c => c.cll_cdate); var groupByData = (from G in data group G by new { G.per_id, G.per_name, G.fcd_code } into G select new ShowTeacherDevicesResult { per_id = G.Key.per_id, per_name = G.Key.per_name, fcd_code = G.Key.fcd_code, count = G.Count() }).ToList(); var dataList = (from T in groupByData group T by new { T.per_id, T.per_name, T.fcd_code } into Td let sumCount = Td.Where(c => c.per_id == Td.Key.per_id && c.fcd_code == Td.Key.fcd_code).Sum(c => c.count) select new ShowTeacherDevicesTemp { per_id = Td.Key.per_id, per_name = Td.Key.per_name, fcd_code = Td.Key.fcd_code, count = sumCount }).ToList(); if (dataList != null && dataList.Count > 0) { List<ShowTeacherDevicesResult> result = new List<ShowTeacherDevicesResult>(); foreach (ShowTeacherDevicesTemp item in dataList) { ShowTeacherDevicesResult r = new ShowTeacherDevicesResult(); r.per_id = item.per_id; r.per_name = item.per_name; r.fcd_code = item.fcd_code=="C"?"富媒体":(item.fcd_code=="D"?"教学工具":"互动工具"); int pcount = dataList.Where(c => c.per_id == item.per_id).Count();//多少个分组 int itemCount = item.count; int totalCount = item.count; if (pcount >= 2) totalCount = dataList.Where(c => c.per_id == item.per_id).Sum(c => c.count); r.count = itemCount; r.perfentRate = $"{(itemCount / totalCount * 1.0 * 100).ToString("f2")}%"; result.Add(r); } return result; } } return null; } /// <summary> /// *****学准备 /// </summary> /// <param name="subjectId"></param> /// <param name="secId"></param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> public async Task<List<ShowTeacherCourseResult>> GetTeachingPreparation(string subjectId, string secId, string startDate, string endDate) { if (string.IsNullOrEmpty(subjectId)) { return null; } var classLogic = _classesLogic.GetClassLog(); var courseLogic = _courseLogic.GetCourse().Where(c => c.isdel == 0); var functionRecordLogic = _guideLearnLogic.GetFunctionRecord(); var personnelLgic = _personLogic.GetPersonnel().Where(c => c.isdel == 0); var subjectLogic = _subjectLogic.GetSubject().Where(c => c.isdel == 0); #region 没有分组的所有过滤的数据 var data = (from L in classLogic join C in courseLogic on L.cou_id equals C.cou_id into Ctemp from Cdata in Ctemp.DefaultIfEmpty() join R in functionRecordLogic on L.cll_id equals R.cll_id join P in personnelLgic on L.per_id equals P.per_id join S in subjectLogic on L.sub_id equals S.sub_id into Stemp from Sdata in Stemp.DefaultIfEmpty() orderby Cdata.cdate descending where (new string[] { "in", "ou" }).Contains(L.cll_type) select new ShowTeachingPreparationBase { sub_id = Sdata.sub_id, sub_name = Sdata.sub_name, cll_id = L.cll_id, cll_type = L.cll_type, level = Cdata.level, per_id = Cdata.per_id, per_name = P.per_name, per_userid = P.per_userid, cll_edate = L.cll_edate, cll_cdate = L.cll_cdate, fcd_code = R.fcd_code.StartsWith("C") ? "C" : (R.fcd_code.StartsWith("D") ? "D" : "ELSE"), sec_id = Sdata.sec_id, cdate = R.cdate }); //科目不为空时 if (!string.IsNullOrEmpty(subjectId)) { data = data.Where(c => c.sub_id == subjectId); } //年段,小学，中学，高中... if (!string.IsNullOrEmpty(secId)) { data = data.Where(c => c.sec_id == secId); } //开始与结束时间 if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate)) { var formatStartDate = Convert.ToDateTime(startDate); var formatEndDate = Convert.ToDateTime(endDate); data = data.Where(c => c.cdate >= formatStartDate && c.cdate <= formatEndDate); data = data.Where(c => c.cll_cdate >= formatStartDate && c.cll_edate <= formatEndDate); } #endregion #region 分组后的数据 var tempdata = ( from tt in data where tt.per_id != null group tt by new { tt.cll_id, tt.sub_id, tt.per_id, tt.per_name, tt.level, tt.cll_type, tt.fcd_code } into G select new ShowTeachingPreparationTemp { cll_id = G.Key.cll_id, per_id = G.Key.per_id, per_name = G.Key.per_name, sub_id = G.Key.sub_id, level = G.Key.level, cll_type = G.Key.cll_type, fcd_code = G.Key.fcd_code, count_num = G.Count() }).ToList(); // 按照老系统的数据展示，需要分组两次 var newGroup = (from TD in tempdata group TD by new { TD.sub_id, TD.per_id, TD.per_name, TD.level, TD.cll_type, TD.fcd_code, } into DD select new ShowTeachingPreparationTemp { per_id = DD.Key.per_id, per_name = DD.Key.per_name, sub_id = DD.Key.sub_id, level = DD.Key.level, cll_type = DD.Key.cll_type, fcd_code = DD.Key.fcd_code, count_num = DD.Count() }).ToList(); #endregion List<ShowTeacherCourseResult> resultList = new List<ShowTeacherCourseResult>(); // Key 的分组的总的次数 int totalRowCount = newGroup.Count; //-有做数据的合并(同一个老师的相同的课程会存在多条数据，如相同老师的相同课程相同年段 C,D,ELSE的情况需要合并) for (int i = 0; i < totalRowCount; i++) { var sub_id = newGroup[i].sub_id; var level = newGroup[i].level;//年级 var cll_type = newGroup[i].cll_type; var fcd_code = newGroup[i].fcd_code; var per_id = newGroup[i].per_id; var per_name = newGroup[i].per_name;//课程教师 //获取当前这个老师的所有 cll_id var M = tempdata.Where(c => c.per_id == per_id && c.sub_id == sub_id && c.level == c.level).ToList(); string[] getcll_idList =M.Select(c=>c.cll_id).ToArray() ; int nowGroupCount = M.Count();//登录的次数 ShowTeacherCourseResult re = new ShowTeacherCourseResult(); await AssemblingModel(M, nowGroupCount, re, getcll_idList, per_id, level, sub_id, per_name, cll_type); resultList.Add(re); } return resultList; } private async Task AssemblingModel(List<ShowTeachingPreparationTemp> M, int nowGroupCount, ShowTeacherCourseResult re, string[] cll_idList, string per_id, string level, string sub_id, string per_name, string cll_type) { re.level = level;//年级 re.sub_id = sub_id; re.per_id = per_id; re.per_name = per_name;//教师姓名 re.loginCount = nowGroupCount.ToString(); //登录的次数 string type = cll_type; re.loginCount = type == "in" ? $"{nowGroupCount}/0" : $"0/{nowGroupCount}"; //获取到这个人的该课程的所有的cll_id，方便来获取总的时间 var getCllid_list = M.Select(c => new cll_idList { cll_id = c.cll_id }).ToList(); //使用的时间 string havaUseTime = await Task.Factory.StartNew(() => ((from T in _classesLogic.GetClassLog() where cll_idList.Contains(T.cll_id) select new { T.cll_edate, T.cll_cdate }).ToList().Sum(c => (c.cll_edate - c.cll_cdate).TotalSeconds / 60.0).ToString("f2"))); re.havaUseTime = type == "in" ? $"{havaUseTime}/0" : $"0/{havaUseTime}"; //C 富媒体 int dataC = GetDataCDEDataRateCount(M, "C", level, per_id, sub_id); //D 教学工具 int dataD = GetDataCDEDataRateCount(M, "D", level, per_id, sub_id); //ELSE 互动工具 int dataE = GetDataCDEDataRateCount(M, "ELSE", level, per_id, sub_id); int totalCount = dataC + dataD + dataE; string dataC_PercentRage = $"{(dataC * 1.0 * 100 / totalCount).ToString("f2")}"; string dataD_PercentRage = $"{(dataD * 1.0 * 100 / totalCount).ToString("f2")}"; string dataE_PercentRage = $"{(dataE * 1.0 * 100 / totalCount).ToString("f2")}"; re.richMedia = type == "in" ? $"{dataC_PercentRage}%/0.00%" : $"0.00%/{dataC_PercentRage}%"; re.teacherTool = type == "in" ? $"{dataD_PercentRage}%/0.00%" : $"0.00%/{dataD_PercentRage}%"; re.interactiveTools = type == "in" ? $"{dataE_PercentRage}%/0.00%" : $"0.00%/{dataE_PercentRage}%"; } private static int GetDataCDEDataRateCount(List<ShowTeachingPreparationTemp> M, string fcd_code, string level, string per_id, string sub_id) { return M.Where(c => c.fcd_code == fcd_code && c.level == level && c.per_id == per_id && c.sub_id == sub_id).Count(); }
混合处理
public async Task<List<GetKnowledgePointSXDShowData>> KnowledgePointSxidu(KnowledgePointSXDInputData inputData) { bool flag = inputData.KnowledgePointEnum == Domain.Enum.KnowledgePointEnum.AllClass; StringBuilder sb = new StringBuilder(); sb.Append(@"SELECT K.kno_id,K.kno_name, HR.correction_type,HR.per_id,P.per_userid,P.per_name,COUNT(1)tcount FROM guide_learning_assignment GLA LEFT JOIN guide_learning_item GLI ON GLI.gul_id=GLA.gul_id LEFT JOIN guide_learning_resources GRS ON GRS.gli_id=GLI.gli_id AND GRS.glr_type ='h' LEFT JOIN homework_question HQ ON HQ.hom_id=GRS.glr_source LEFT JOIN homework_respond HR ON HQ.hoq_id=HQ.hoq_id LEFT JOIN question Q ON HQ.que_id=Q.que_id LEFT JOIN knowledge K ON K.kno_id=Q.que_kno1 AND K.isdel=0 LEFT JOIN personnel P ON HR.per_id=P.per_id AND P.isdel=0 WHERE GLA.cou_id=@cou_id AND HR.per_id IN( SELECT R.per_id FROM class_role R WHERE R.class_uid IN( SELECT C.class_uid FROM course C WHERE C.cou_id=@cou_id AND C.isdel=0 ) AND R.class_role_type='0') and HR.isdel=0 AND GLA.isdel=0 AND GLI.isdel=0 and K.kno_id IS NOT NULL "); if (inputData.session_week > 0) { sb.Append(" and GLA.session_week=@session_week "); } if (flag) //全班 { sb.Append(" GROUP BY K.kno_id,K.kno_name, HR.correction_type "); } else { sb.Append(" AND HR.per_id=@per_id "); sb.Append(" GROUP BY HR.per_id, K.kno_id,K.kno_name, HR.correction_type "); //学生 } sb.Append(@" UNION all SELECT K.kno_id,K.kno_name, HR.correction_type,HR.per_id,P.per_userid,P.per_name,COUNT(1)tcount FROM guide_learning_assignment GLA LEFT JOIN guide_learning_item GLI ON GLA.gul_id=GLI.gul_id LEFT JOIN guide_learning_resources GRS ON GRS.gli_id=GLI.gli_id AND GRS.glr_type='p' LEFT JOIN pre_learning_question HQ ON HQ.prl_id=GRS.glr_source LEFT JOIN pre_learning_respond HR ON HQ.plq_id=HQ.plq_id inner JOIN question Q ON HQ.que_id=Q.que_id inner JOIN knowledge K ON K.kno_id=Q.que_kno1 LEFT JOIN personnel P ON HR.per_id=P.per_id AND P.isdel=0 WHERE GLA.cou_id=@cou_id AND HR.per_id IN( SELECT R.per_id FROM class_role R WHERE R.class_uid IN( SELECT C.class_uid FROM course C WHERE C.cou_id=@cou_id AND C.isdel=0 ) AND R.class_role_type='0' ) AND HR.isdel=0 AND GLA.isdel=0 AND GLI.isdel=0 AND K.kno_id IS NOT NULL AND K.isdel=0 AND HR.isdel=0 "); if (inputData.session_week > 0) { sb.Append(" and GLA.session_week=@session_week "); } if (flag) //全班 { sb.Append(" GROUP BY K.kno_id,K.kno_name, HR.correction_type "); } else { sb.Append(" AND HR.per_id=@per_id "); sb.Append(" GROUP BY HR.per_id, K.kno_id,K.kno_name, HR.correction_type "); //学生 } var getTempData = await ZRF_DapperHelper.QueryAsync<GetKnowledgePointSXDTempData>(sb.ToString(), new { cou_id = inputData.cou_id, per_id = inputData.per_id, session_week = inputData.session_week }); var listinfo = new List<GetKnowledgePointSXDShowData>(); if (getTempData != null && getTempData.Any()) { var groupKname = (from N in getTempData group N by new { N.kno_id, N.kno_name } into Ndata select new { kid = Ndata.Key.kno_id, name = Ndata.Key.kno_name, count = Ndata.Count() }).ToList(); GetKnowledgePointSXDShowData info = default; for (int i = 0; i < groupKname.Count; i++) { info = new GetKnowledgePointSXDShowData(); string kid = groupKname[i].kid; string kname = groupKname[i].name; info.KnowledgePointName = kname; //null:作答正确 int Familiarity_correction_type_null = getTempData.Where(c => (c.correction_type == "" || c.correction_type == null) && c.kno_id == kid).ToList().DefaultIfEmpty().Sum(c => c == null ? 0 : c.tcount); //0:作答错误 int Familiarity_correction_type_0 = getTempData.Where(c => c.correction_type == "0" && c.kno_id == kid).ToList().DefaultIfEmpty().Sum(c => c == null ? 0 : c.tcount); int totalF = Familiarity_correction_type_null + Familiarity_correction_type_0; info.FamiliarityWithAnsweringRate = $"{((Familiarity_correction_type_null * 1.0 / (totalF > 0 ? totalF : 1)) * 100).ToString("f2")}%"; //null:订正熟悉度 int Revised_correction_type_2 = getTempData.Where(c => c.correction_type == "2" && c.kno_id == kid).ToList().DefaultIfEmpty().Sum(c => c == null ? 0 : c.tcount); //2:订正正确 int Revised_correction_type_1 = getTempData.Where(c => c.correction_type == "1" && c.kno_id == kid).ToList().DefaultIfEmpty().Sum(c => c == null ? 0 : c.tcount); //1:订正正确 int totalRevised = Revised_correction_type_2 + Revised_correction_type_1; //$"{((Revised_correction_type_2*1.0/(totalRevised > 0 ? totalRevised : 1))*100).ToString("f2")}%"; info.RevisedFamiliarityRate = $"{((Revised_correction_type_2 * 1.0 / (totalRevised > 0 ? totalRevised : 1)) * 100).ToString("f2")}%"; listinfo.Add(info); } } return listinfo; }