using Azure; using Azure.Cosmos; using Azure.Messaging.ServiceBus; using HTEXLib.COMM.Helpers; using Microsoft.Extensions.Configuration; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Net; using System.Text; using System.Text.Json; using System.Threading.Tasks; using TEAMModelOS.Models; using TEAMModelOS.SDK.DI; using TEAMModelOS.SDK.Extension; using TEAMModelOS.SDK.Models; namespace TEAMModelOS.SDK { public class StudentService { /// /// /// /// /// /// public static async Task> GeStudentData(AzureCosmosFactory _azureCosmos, string schoolId, IEnumerable students) { List studentDatas = new List(); if (students.Any()) { string queryText = $"SELECT c.id, c.code ,c.classId FROM c WHERE c.id IN ({string.Join(",", students.Select(o => $"'{o}'"))})"; await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student").GetItemQueryIterator(queryText: queryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{schoolId}") })) { studentDatas.Add(item); } } return studentDatas; } /// /// /// /// 学校编码 /// 前端要修改的学生 /// 操作 /// 变更前的学生 /// public static async Task> CheckStudent(AzureServiceBusFactory _serviceBus, IConfiguration _configuration, AzureCosmosFactory _azureCosmos, string schoolId, List students, List prestudents) { List aftstudents = await StudentService.GeStudentData(_azureCosmos, schoolId, students?.Select(x => x.id)); Dictionary dictChange = new Dictionary(); if (prestudents.Count >= aftstudents.Count) { foreach (var pstu in prestudents) { var afstu = aftstudents.Find(x => x.id.Equals(pstu.id)); if (afstu != null) { if (string.IsNullOrEmpty(pstu.classId) && !string.IsNullOrEmpty(afstu.classId)) { //则是加入的学生 if (dictChange.ContainsKey(afstu.classId)) { dictChange[afstu.classId].stujoin.Add( new Member { id = afstu.id, code = $"{schoolId }", type = 2 }); } else { GroupChange change = new GroupChange { scope = "school", school = schoolId, type = "student", originCode = schoolId, listid = afstu.classId, stujoin = new List { new Member { id= afstu.id, code= $"{schoolId}", type =2 } } }; dictChange.Add(afstu.classId, change); } } else if (!string.IsNullOrEmpty(pstu.classId) && string.IsNullOrEmpty(afstu.classId)) { //则该学生是解除了班级状态 if (dictChange.ContainsKey(pstu.classId)) { dictChange[pstu.classId].stuleave.Add( new Member { id = pstu.id, code = $"{schoolId }", type = 2 }); } else { GroupChange change = new GroupChange { scope = "school", school = schoolId, type = "student", originCode = schoolId, listid = pstu.classId, stuleave = new List { new Member { id= pstu.id, code= $"{schoolId}", type=2 } } }; dictChange.Add(pstu.classId, change); } } else if (!string.IsNullOrEmpty(pstu.classId) && !string.IsNullOrEmpty(afstu.classId)) { //id不同则有异动 if (!pstu.classId.Equals(afstu.classId)) { //则是加入的学生 if (dictChange.ContainsKey(afstu.classId)) { dictChange[afstu.classId].stujoin.Add( new Member { id = afstu.id, code = $"{schoolId }", type = 2 }); } else { GroupChange change = new GroupChange { scope = "school", school = schoolId, type = "student", originCode = schoolId, listid = afstu.classId, stujoin = new List { new Member { id= afstu.id, code= $"{schoolId}", type = 2 } } }; dictChange.Add(afstu.classId, change); }//则该学生是解除了班级状态 if (dictChange.ContainsKey(pstu.classId)) { dictChange[pstu.classId].stuleave.Add( new Member { id = pstu.id, code = $"{schoolId }", type = 2 }); } else { GroupChange change = new GroupChange { scope = "school", school = schoolId, type = "student", originCode = schoolId, listid = pstu.classId, stuleave = new List { new Member { id= pstu.id, code= $"{schoolId}", type= 2 } } }; dictChange.Add(pstu.classId, change); } } else { //相同则不管 } } else { //变更前后都没绑定班级则不管。 } } else { if (!string.IsNullOrEmpty(pstu.classId)) { //则该学生是被删除的学生 if (dictChange.ContainsKey(pstu.classId)) { dictChange[pstu.classId].stuleave.Add( new Member { id = pstu.id, code = $"Base-{schoolId }", type = 2 }); } else { GroupChange change = new GroupChange { scope = "school", school = schoolId, type = "student", originCode = schoolId, listid = pstu.classId, stuleave = new List { new Member { id= pstu.id, code= $"{schoolId}", type = 2 } } }; dictChange.Add(pstu.classId, change); } } } } } else { foreach (var afstu in aftstudents) { var pstu = prestudents.Find(x => x.id.Equals(afstu.id)); if (pstu != null) { if (string.IsNullOrEmpty(pstu.classId) && !string.IsNullOrEmpty(afstu.classId)) { //则是加入的学生 if (dictChange.ContainsKey(afstu.classId)) { dictChange[afstu.classId].stujoin.Add( new Member { id = afstu.id, code = $"{schoolId }", type = 2 }); } else { GroupChange change = new GroupChange { scope = "school", school = schoolId, type = "student", originCode = schoolId, listid = afstu.classId, stujoin = new List { new Member { id= afstu.id, code= $"Base-{schoolId}", type = 2 } } }; dictChange.Add(afstu.classId, change); } } else if (!string.IsNullOrEmpty(pstu.classId) && string.IsNullOrEmpty(afstu.classId)) { //则该学生是解除了班级状态 if (dictChange.ContainsKey(pstu.classId)) { dictChange[pstu.classId].stuleave.Add( new Member { id = pstu.id, code = $"{schoolId }", type = 2 }); } else { GroupChange change = new GroupChange { scope = "school", school = schoolId, type = "student", originCode = schoolId, listid = pstu.classId, stuleave = new List { new Member { id= pstu.id, code= $"{schoolId}", type= 2 } } }; dictChange.Add(pstu.classId, change); } } else if (!string.IsNullOrEmpty(pstu.classId) && !string.IsNullOrEmpty(afstu.classId)) { //id不同则有异动 if (!pstu.classId.Equals(afstu.classId)) { //则是加入的学生 if (dictChange.ContainsKey(afstu.classId)) { dictChange[afstu.classId].stujoin.Add( new Member { id = afstu.id, code = $"{schoolId }", type = 2 }); } else { GroupChange change = new GroupChange { scope = "school", school = schoolId, type = "student", originCode = schoolId, listid = afstu.classId, stujoin = new List { new Member { id= afstu.id, code= $"{schoolId}", type = 2 } } }; dictChange.Add(afstu.classId, change); }//则该学生是解除了班级状态 if (dictChange.ContainsKey(pstu.classId)) { dictChange[pstu.classId].stuleave.Add( new Member { id = pstu.id, code = $"{schoolId }", type = 2 }); } else { GroupChange change = new GroupChange { scope = "school", school = schoolId, type = "student", originCode = schoolId, listid = pstu.classId, stuleave = new List { new Member { id= pstu.id, code= $"{schoolId}", type=2 } } }; dictChange.Add(pstu.classId, change); } } else { //相同则不管 } } else { //变更前后都没绑定班级则不管。 } } else { if (!string.IsNullOrEmpty(afstu.classId)) { //代表学生是新增的 if (dictChange.ContainsKey(afstu.classId)) { dictChange[afstu.classId].stujoin.Add( new Member { id = afstu.id, code = $"{schoolId }", type = 2 }); } else { GroupChange change = new GroupChange { scope = "school", school = schoolId, type = "student", originCode = schoolId, listid = afstu.classId, stujoin = new List { new Member { id= afstu.id, code= $"{schoolId}", type = 2 } } }; dictChange.Add(afstu.classId, change); } } } } } foreach (var changed in dictChange.Keys) { var change = dictChange[changed]; if (change.stujoin.Count != 0 || change.stuleave.Count != 0) { var messageChange = new ServiceBusMessage(change.ToJsonString()); messageChange.ApplicationProperties.Add("name", "GroupChange"); var ActiveTask = _configuration.GetValue("Azure:ServiceBus:ActiveTask"); await _serviceBus.GetServiceBusClient().SendMessageAsync(ActiveTask, messageChange); } } return dictChange; } public record StudentInfo { public string studentId { get; set; } public string picture { get; set; } public string name { get; set; } public string mobile { get; set; } public string mail { get; set; } /// /// f女性 m男性 n 保密 /// public string gender { get; set; } } /// /// 整理前端匯入的學生資訊 /// /// /// /// private static (Dictionary studs, Dictionary classInfo, Dictionary> classStudNo, List errorYear, List duplId) doSortImpStuds(string schoolId, JsonElement.ArrayEnumerator students) { //批量匯入 檢查輸入數據 確認座號 確認教室(創建教室) 確認學生存不存在或是要不要更新 //存放輸入的學生資訊 key:stud id value:學生詳細資料 Dictionary dicStuds = new Dictionary(); //存放教室資訊用 key:classNo value:className Dictionary dicClassInfo = new Dictionary(); //存放欲加入該間教室的學生座號清單 key:classNo value:no list Dictionary> dicClassStudNo = new Dictionary>(); //存放輸入id重複 List duplId = new List(); List errorYear = new List(); while (students.MoveNext()) { //string id = null, no = null, name = null, year = null, pw = null, classNo = null, className = null; JsonElement student = students.Current; //ClassNo內的座號 //欲加入的教室 //查學生 //該間教室的座號與目前欲更新的是否有重複 有些可能是同教室換座號 反正都要將學生讀出來 //舊學生完整資料+新學生資料 //進行輸入資料的整理 if (student.TryGetProperty("id", out var tmpId)) { string id = tmpId.GetString(); //如果id欄位是空的，則跳過該筆資料 if (string.IsNullOrWhiteSpace(id)) continue; //輸入的id不應有重複 if (dicStuds.ContainsKey(id)) { //如果id重複，則將之從整理清單內清除 duplId.Add(id); dicStuds.Remove(id); } (string name, string no, int year, string salt, string pw, string classNo, string className, string periodId, int classYear) studentInfo = (null, null, 0, null, null, null, null, null, 0); if (student.TryGetProperty("name", out var tmpName) && !string.IsNullOrWhiteSpace(tmpName.GetString())) studentInfo.name = tmpName.GetString(); //入學學年為必須，故若是無給值則將之紀錄並跳過該筆資料 if (student.TryGetProperty("year", out var tmpYear) && !string.IsNullOrWhiteSpace(Convert.ToString(tmpYear))) studentInfo.year = tmpYear.GetInt32(); else { errorYear.Add(id); continue; } //Password，若沒給則使用學號當密碼 studentInfo.salt = Utils.CreatSaltString(8); studentInfo.pw = student.TryGetProperty("pw", out var tmpPw) && !string.IsNullOrWhiteSpace(tmpPw.GetString()) ? Utils.HashedPassword(tmpPw.GetString(), studentInfo.salt) : Utils.HashedPassword(id, studentInfo.salt); if (student.TryGetProperty("periodId", out var tmpPeriodId) && !string.IsNullOrWhiteSpace(tmpPeriodId.GetString())) studentInfo.periodId = tmpPeriodId.GetString(); // if (student.TryGetProperty("gradeIndex", out var tmpGradeIndex)) studentInfo.gradeIndex = tmpGradeIndex.GetInt32(); if (student.TryGetProperty("classNo", out var tmpClassNo) && !string.IsNullOrWhiteSpace(tmpClassNo.GetString())) { studentInfo.classNo = tmpClassNo.GetString(); //在新建帳號上，應要給classNo才能設定no，但是若只是已存在的帳號要進行座號更新呢? 為避免使用者出錯，故已存在的帳號也應當給classNo。 if (student.TryGetProperty("no", out var tmpNo) && !string.IsNullOrWhiteSpace(tmpNo.GetString())) { studentInfo.no = tmpNo.GetString(); //這邊先將該間教室欲使用到的no整理出來 if (dicClassStudNo.ContainsKey(tmpClassNo.GetString())) dicClassStudNo[tmpClassNo.GetString()].Add((id, tmpNo.GetString())); else dicClassStudNo.Add(tmpClassNo.GetString(), new List<(string id, string no)>() { (id, tmpNo.GetString()) }); } //有給classNo才會紀錄className，classNo屬於實體教室門牌號，全校理當只會有一個。 int year = 0; if (student.TryGetProperty("classYear", out var tmpClassYear) && tmpClassYear.TryGetInt32(out int syear)) { year = syear; studentInfo.classYear = syear; } if (student.TryGetProperty("className", out var tmpClassName) && !string.IsNullOrWhiteSpace(tmpClassName.GetString())) { studentInfo.className = tmpClassName.GetString(); if (!dicClassInfo.ContainsKey($"{studentInfo.periodId}_{year}_{tmpClassNo.GetString()}")) { dicClassInfo.Add($"{studentInfo.periodId}_{year}_{tmpClassNo.GetString()}", (tmpClassName.GetString(), studentInfo.periodId, year, tmpClassNo.GetString())); } } } //將最後結果加到字典內 dicStuds.Add(id, studentInfo); } } return (dicStuds, dicClassInfo, dicClassStudNo, errorYear, duplId); } /// /// 更新或是新增學生 /// /// /// /// public static async Task<(List studs, Dictionary> classDuplNos, List errorIds)> upsertStudents(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId, JsonElement.ArrayEnumerator students) { try { var sortedImpData = doSortImpStuds(schoolId, students); //var classNos = sortedImpData.classInfo.Select(o => new {key= o.Key, periodId=o.Value.periodId,index= o.Value.gradeIndex,year = o.Value.year }).ToList(); //抓到教室資訊 var classInfos = await getClassInfoUseNo(_azureCosmos, _dingDing, _option, schoolId, sortedImpData.classInfo); //取出已存在教室的classId，後面查座號要用。 List tasks = new List(); //Key:ClassNo Value:No 匯入時只有ClassNo Dictionary> classStudNos = new Dictionary>(); //Key:ClassNo_gradeId Value:ClassId 存放教室no及id的變數 Dictionary classNoId = new Dictionary(); foreach (var classInfo in classInfos) { string classGradeId = classInfo.Value.gradeId; int classYear = classInfo.Value.year; classNoId.Add(classInfo.Value.no + "_" + classYear, (classInfo.Value.id, classInfo.Value.name, classInfo.Value.periodId, classGradeId, classYear)); tasks.Add( Task.Run( async () => { //(id,no) var studNo = await checkStudNo(_azureCosmos, _dingDing, _option, schoolId, classInfo.Value.id); classStudNos.Add(classInfo.Value.no + "_" + classInfo.Value.year, studNo); })); } //這邊整理出不存在的教室，之後創建新教室用(比對classNo)。 //var nonexistentClassNo = classNos.Except(classInfos.Select(o => o.Key).ToList()); List exsitkey = new List(); foreach (var classInfo in classInfos) { //$"{studentInfo.periodId}_{year}_{tmpClassNo.GetString()}" var key = $"{classInfo.Value.periodId}_{classInfo.Value.year}_{classInfo.Value.no}"; exsitkey.Add(key); } List> nonexistentClassNo = new List>(); foreach (var key in sortedImpData.classInfo.Keys) { if (!exsitkey.Contains(key)) { nonexistentClassNo.Add(new KeyValuePair(key, sortedImpData.classInfo[key])); } } // var nonexistentClassNo = exsitkey.Except(sortedImpData.classInfo.Select(o => o.Key).ToList()); if (nonexistentClassNo.Count() != 0) { var gradesInfo = await getGrades(_azureCosmos, _dingDing, _option, schoolId); foreach (var item in nonexistentClassNo) { string gradeId = string.Empty; string periodId = periodId = sortedImpData.classInfo[item.Key].periodId; int year = sortedImpData.classInfo[item.Key].year; //確認該學段存在及輸入的年級index正確(-1後大於等於0) //if (gradesInfo.ContainsKey(sortedImpData.classInfo[item].periodId) && sortedImpData.classInfo[item].gradeIndex - 1>=0) // { // periodId = sortedImpData.classInfo[item].periodId; //gradeId = gradesInfo[sortedImpData.classInfo[item].periodId][sortedImpData.classInfo[item].gradeIndex - 1].gradeId; //} //建立新教室 (string classId, string classNo, string className, string periodId, string gradeId, int classYear) retCreateClassInfo = await createClassInfo(_azureCosmos, _dingDing, _option, schoolId, null, sortedImpData.classInfo[item.Key].className, sortedImpData.classInfo[item.Key].no, periodId, gradeId, year); classStudNos.Add(retCreateClassInfo.classNo + "_" + retCreateClassInfo.classYear, new List<(string id, string no)>()); classNoId.Add(retCreateClassInfo.classNo + "_" + retCreateClassInfo.classYear, (retCreateClassInfo.classId, retCreateClassInfo.className, periodId, gradeId, year)); } } var taskWhenAll = Task.WhenAll(tasks); taskWhenAll.Wait(); //------------------------------------------------------------------------- //建立學生或是更新學生，並且要確認座號是否重複 //每間教室的全部座號 欲更新的教室座號 //先新建帳號若出現409則進行資料更新 //紀錄有重複做號的id Dictionary> duplNos = new Dictionary>(); List errorIds = new List(); List retStuds = new List(); CosmosContainer cosmosContainer = _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student"); long now = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds(); //並行處理 foreach (var stud in sortedImpData.studs) { //這邊一樣要確認已存在和欲加入還有欲修改的座號。 //欲修改的不會有重複 string classId = null; bool isContinue = false; if (!string.IsNullOrWhiteSpace(stud.Value.no) && !string.IsNullOrWhiteSpace(stud.Value.classNo)) { classId = classNoId[stud.Value.classNo + "_" + stud.Value.classYear].classId; (string id, string no) existNoInfo = (null, null); //檢查要更新的座號是否已存在於雲端座號(已被其他同學使用) classStudNos[stud.Value.classNo + "_" + stud.Value.classYear].ForEach( o => { if (o.no.Equals(stud.Value.no) && !o.id.Equals(stud.Key)) { existNoInfo = (o.id, o.no); if (duplNos.ContainsKey(stud.Value.classNo)) duplNos[stud.Value.classNo].Add(stud.Key); else duplNos.Add(stud.Value.classNo, new List() { stud.Key }); } }); //如果不是空的代表有座號重覆到，此時要再確認這個重複座號的id，是否存在於這次的更新，而且是要更新座號的。 if (!string.IsNullOrWhiteSpace(existNoInfo.id)) { isContinue = true; //輪巡所有匯入的學生資料，並檢查匯入的座號。 sortedImpData.classStudNo[stud.Value.classNo].ForEach( o => { if (o.id.Equals(existNoInfo.id) && !o.no.Equals(existNoInfo.no)) { //可以更新該座號 isContinue = false; duplNos[stud.Value.classNo].Remove(stud.Key); return; } }); } } if (isContinue) continue; (string id, string name, string picture, int year, string no, string classId, string classNo, string className, string gradeId, string periodId) tmpStudInfo = (stud.Key, stud.Value.name, null, stud.Value.year, stud.Value.no, null, stud.Value.classNo, null, null, null); using var memoryStream = new MemoryStream(); using var writerNew = new Utf8JsonWriter(memoryStream); writerNew.WriteStartObject(); writerNew.WriteString("id", stud.Key); writerNew.WriteString("pk", $"Base"); writerNew.WriteString("code", $"Base-{schoolId}"); writerNew.WriteString("schoolId", schoolId); writerNew.WriteNumber("year", stud.Value.year); writerNew.WriteNumber("createTime", now); writerNew.WriteString("salt", stud.Value.salt); writerNew.WriteString("pw", stud.Value.pw); if (string.IsNullOrWhiteSpace(stud.Value.name)) writerNew.WriteNull("name"); else writerNew.WriteString("name", stud.Value.name); writerNew.WriteString("gender", "M"); writerNew.WriteNull("picture"); writerNew.WriteNull("mail"); writerNew.WriteNull("mobile"); writerNew.WriteNull("country"); if (!string.IsNullOrWhiteSpace(stud.Value.periodId)) { writerNew.WriteString("periodId", stud.Value.periodId); } if (string.IsNullOrWhiteSpace(stud.Value.classNo)) writerNew.WriteNull("classId"); else { writerNew.WriteString("classId", classId); tmpStudInfo.classId = classId; tmpStudInfo.className = classNoId[stud.Value.classNo + "_" + stud.Value.classYear].className; tmpStudInfo.gradeId = classNoId[stud.Value.classNo + "_" + stud.Value.classYear].gradeId; tmpStudInfo.periodId = classNoId[stud.Value.classNo + "_" + stud.Value.classYear].periodId; } if (string.IsNullOrWhiteSpace(stud.Value.no)) { writerNew.WriteNull("no"); writerNew.WriteNull("irs"); } else { writerNew.WriteString("irs", stud.Value.no); writerNew.WriteString("no", stud.Value.no); }; writerNew.WriteNull("groupId"); writerNew.WriteNull("groupName"); writerNew.WriteEndObject(); writerNew.Flush(); var response = await cosmosContainer.CreateItemStreamAsync(memoryStream, new PartitionKey($"Base-{schoolId}")); if (response.Status == (int)HttpStatusCode.Created) { //如果是Created則啥都不做，讓他去下面進行資料的彙整。 } //查到已存在的id，則進行基本資料更新。 else if (response.Status == (int)HttpStatusCode.Conflict) { try { bool isUpPwDone = false; Student student = await _azureCosmos .GetCosmosClient() .GetContainer(Constant.TEAMModelOS, "Student") .ReadItemAsync(stud.Key, new PartitionKey($"Base-{schoolId}")); if (!string.IsNullOrWhiteSpace(stud.Value.name)) { student.name = stud.Value.name; } if (stud.Value.year > 0) { student.year = stud.Value.year; } if (!string.IsNullOrWhiteSpace(stud.Value.no)) { student.no = stud.Value.no; } if (!string.IsNullOrWhiteSpace(stud.Value.classNo)) { student.classId = classId; } if (!string.IsNullOrWhiteSpace(stud.Value.periodId)) { student.periodId = stud.Value.periodId; } if (!isUpPwDone) { student.pw = stud.Value.pw; student.salt = stud.Value.salt; isUpPwDone = true; } tmpStudInfo.picture = student.picture; await cosmosContainer.ReplaceItemAsync(student, stud.Key, new PartitionKey($"Base-{schoolId}")); } catch (CosmosException ex) { errorIds.Add(stud.Key); continue; } } else { errorIds.Add(stud.Key); continue; } //整理輸出用資料 retStuds.Add( new { tmpStudInfo.id, tmpStudInfo.name, tmpStudInfo.picture, tmpStudInfo.year, tmpStudInfo.no, tmpStudInfo.classId, tmpStudInfo.classNo, tmpStudInfo.className, // tmpStudInfo.gradeId, // tmpStudInfo.periodId }); } return (retStuds, duplNos.Where(o => o.Value.Count != 0).ToDictionary(o => o.Key, o => o.Value), errorIds); } catch (Exception ex) { await _dingDing.SendBotMsg( $"IES5，{_option.Location}，StudentController/upsertStudents()\nex:{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); } return (null, null, null); } /// /// 單純建立單一學生 /// /// /// public static async Task createStudent(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId, studCreateInfo studCreateInfo) { try { using var stream = new MemoryStream(); using var writer = new Utf8JsonWriter(stream); writer.WriteStartObject(); writer.WriteString("pk", $"Base"); writer.WriteString("code", $"Base-{schoolId}"); writer.WriteString("id", studCreateInfo.id); if (string.IsNullOrWhiteSpace(studCreateInfo.name)) writer.WriteNull("name"); else writer.WriteString("name", studCreateInfo.name); if (string.IsNullOrWhiteSpace(studCreateInfo.gender)) writer.WriteNull("gender"); else writer.WriteString("gender", studCreateInfo.gender); writer.WriteString("schoolId", schoolId); //20210713 huanghb add 增加学段 if (string.IsNullOrWhiteSpace(studCreateInfo.periodId)) writer.WriteNull("periodId"); else writer.WriteString("periodId", studCreateInfo.periodId); writer.WriteNumber("year", studCreateInfo.year); writer.WriteNumber("createTime", DateTimeOffset.UtcNow.ToUnixTimeMilliseconds()); writer.WriteNull("picture"); writer.WriteNull("mail"); writer.WriteNull("mobile"); writer.WriteNull("country"); //Password，若沒給則使用學號當密碼 string salt = Utils.CreatSaltString(8); string hashPw = string.IsNullOrWhiteSpace(studCreateInfo.pw) ? Utils.HashedPassword(studCreateInfo.id, salt) : Utils.HashedPassword(studCreateInfo.pw, salt); writer.WriteString("salt", salt); writer.WriteString("pw", hashPw); if (string.IsNullOrWhiteSpace(studCreateInfo.classId)) writer.WriteNull("classId"); else writer.WriteString("classId", studCreateInfo.classId); if (string.IsNullOrWhiteSpace(studCreateInfo.no)) { writer.WriteNull("no"); writer.WriteNull("irs"); } else { writer.WriteString("no", studCreateInfo.no); writer.WriteString("irs", studCreateInfo.no); } writer.WriteNull("groupId"); writer.WriteNull("groupName"); writer.WriteEndObject(); writer.Flush(); var response = await _azureCosmos .GetCosmosClient() .GetContainer(Constant.TEAMModelOS, "Student") .CreateItemStreamAsync(stream, new PartitionKey($"Base-{schoolId}")); if (response.Status == (int)HttpStatusCode.Created || response.Status == (int)HttpStatusCode.OK) return true; if (response.Status == (int)HttpStatusCode.Conflict) return false; else { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createStudent()\nCosmosDB Create response status = {response.Status}\nID:{studCreateInfo.id}", GroupNames.醍摩豆服務運維群組); return false; } } catch (Exception ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createStudent()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); return false; } } /// /// 生成Class資料 /// /// /// /// /// /// private static async Task<(string classId, string classNo, string className, string periodId, string gradeId, int classYear)> createClassInfo( AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId, string classId, string className, string classNo, string periodId, string gradeId, int classYear) { //組Class JSON try { string cId = classId; using var memoryStream = new MemoryStream(); using var writer = new Utf8JsonWriter(memoryStream); writer.WriteStartObject(); writer.WriteString("pk", "Class"); writer.WriteString("code", $"Class-{schoolId}"); //如果classId是空的，則生成一組GUID。 if (string.IsNullOrWhiteSpace(classId)) { cId = Guid.NewGuid().ToString(); writer.WriteString("id", cId); } else writer.WriteString("id", classId); if (string.IsNullOrWhiteSpace(classNo)) writer.WriteNull("no"); else writer.WriteString("no", classNo); writer.WriteNull("x"); writer.WriteNull("y"); if (string.IsNullOrWhiteSpace(className)) writer.WriteNull("name"); else writer.WriteString("name", className); writer.WritePropertyName("teacher"); writer.WriteStartObject(); writer.WriteNull("id"); writer.WriteNull("name"); writer.WriteEndObject(); //if (string.IsNullOrWhiteSpace(gradeId)) writer.WriteNull("gradeId"); //else writer.WriteString("gradeId", gradeId); if (string.IsNullOrWhiteSpace(periodId)) writer.WriteNull("periodId"); else writer.WriteString("periodId", periodId); writer.WriteNumber("year", classYear); writer.WriteNumber("createTime", DateTimeOffset.UtcNow.ToUnixTimeMilliseconds()); writer.WriteNull("sn"); writer.WriteString("style", "smart"); writer.WriteString("openType", "1"); writer.WriteString("scope", "school"); writer.WriteEndObject(); writer.Flush(); var ret = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "School").CreateItemStreamAsync(memoryStream, new PartitionKey($"Class-{schoolId}")); if (ret.Status != (int)HttpStatusCode.Created) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createClassInfo()\nStatus:{ret.Status}\nSchoolId:{schoolId},ClassId:{classId}", GroupNames.醍摩豆服務運維群組); } return (cId, classNo, className, periodId, gradeId, classYear); } catch (CosmosException ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createClassInfo()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); } catch (Exception ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createClassInfo()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); } return (null, null, null, null, null, 0); } /// /// 使用學校代碼查詢該校所有學生，並且在查詢該學生所屬的教室及座號，支援offset和limit操作已及ContinuationToken，若有ContinuationToken，則會優先使用ContinuationToken。 /// /// /// 透過Name或Id來查，所以不會管學制、學級和教室 /// /// /// /// /// /// /// private async Task<(List students, string continuationToken)> getStudents( AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId, string byNameOrId = null, string byPeriod = null, string byGrade = null, string byClassId = null, int offset = -1, int limit = -1, string token = default) { try { //以學校學生角度去抓資料 List<(string id, string name, string picture, int year)> listStudent = new List<(string id, string name, string picture, int year)>(); string queryText = $"SELECT c.id, c.name, c.picture, c.year FROM c WHERE c.code = 'Base-{schoolId}'"; //如果有選擇ClassId的話，則先取得該教室內的學生。 List searchId = new List(); if (!string.IsNullOrWhiteSpace(byClassId)) { var classInfos = await getClassInfoUseId(_azureCosmos, _dingDing, _option, schoolId, new List() { byClassId }); foreach (var classInfo in classInfos) { var students = classInfo.Value.GetProperty("students").EnumerateArray(); while (students.MoveNext()) { JsonElement stud = students.Current; string id = stud.GetProperty("id").GetString(); searchId.Add(id); } } //將使用者過濾classId所取得的學生ID加入sql字串內 if (searchId.Count != 0) { queryText = $"{queryText} AND c.id IN ({string.Join(",", searchId.Select(o => $"'{o}'"))})"; } } //檢查是否有接續token及是否要在sql語法內多增加offset及limit if (string.IsNullOrWhiteSpace(token)) { token = default; if (offset != -1 && limit != -1) queryText = $"{queryText} OFFSET {offset} LIMIT {limit}"; } //回傳用ContinuationToken string continuationToken = string.Empty; //進行學生資料的查詢 await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student") .GetItemQueryStreamIterator( queryText: queryText, continuationToken: token, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{schoolId}") })) { continuationToken = item.GetContinuationToken(); using var json = await JsonDocument.ParseAsync(item.ContentStream); if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0) { var accounts = json.RootElement.GetProperty("Documents").EnumerateArray(); while (accounts.MoveNext()) { JsonElement account = accounts.Current; listStudent.Add((account.GetProperty("id").GetString(), account.GetProperty("name").GetString(), account.GetProperty("picture").GetString(), account.GetProperty("year").GetInt32())); } } //單筆查詢上限為100條，所以查完一次即返回，並且給接續token。 break; } //查學生所屬的教室及座號 List ret = new List(); //查教室資訊，使用上面的學生id並透過子查詢查詢。 queryText = $"SELECT c.id, c.name, c.gradeId, c.students FROM c JOIN (SELECT VALUE t FROM t IN c.students WHERE t.id IN ({string.Join(",", listStudent.Select(o => $"'{o.id}'"))}))"; await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "School") .GetItemQueryStreamIterator( queryText: queryText, //continuationToken: token, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{schoolId}") })) { using var json = await JsonDocument.ParseAsync(item.ContentStream); if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0) { var classrooms = json.RootElement.GetProperty("Documents").EnumerateArray(); while (classrooms.MoveNext()) { JsonElement classroom = classrooms.Current; var studs = classroom.GetProperty("students").EnumerateArray(); while (studs.MoveNext()) { JsonElement stud = studs.Current; string id = stud.GetProperty("id").GetString(); //整理出前端所需的資訊 var tmp = listStudent .Where(o => o.id.Equals(id, StringComparison.Ordinal)) .Select(o => new { o.id, o.name, o.picture, o.year, no = stud.GetProperty("no").GetString(), gradeId = classroom.GetProperty("gradeId").GetString(), className = classroom.GetProperty("name").GetString() }); ret.AddRange(tmp); //刪除已整理完的ID listStudent.RemoveAll(o => o.id.Equals(id, StringComparison.Ordinal)); } } } } var notJoinClassStuds = listStudent.Select(o => new { o.id, o.name, o.picture, o.year, no = (string)null, gradeId = (string)null, className = (string)null }); ret.AddRange(notJoinClassStuds); return (ret, continuationToken); } catch (CosmosException ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); } catch (Exception ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); } return (null, null); } /// /// 取得該學校的所有學生。 /// /// /// [{id,name,picture,year,no,classId,classNo,className,gradeId,periodId},{id,name,picture,..}..] public static async Task> getAllStudent(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId) { try { //TODO : 進階查詢選項調整、部分地方可用並行處理 //以學校學生角度去抓資料 Dictionary> dicClassStuds = new Dictionary>(); List<(string id, string name, string picture, int year, string no, string periodId, string irs)> notJoinClassStuds = new List<(string id, string name, string picture, int year, string no, string periodId, string irs)>(); string queryText = $"SELECT c.id, c.name, c.picture, c.year, c.classId, c.no,c.irs ,c.periodId FROM c WHERE c.code = 'Base-{schoolId}'"; //回傳用ContinuationToken string continuationToken = string.Empty; var container = _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student"); //進行學生資料的查詢 TEAMModelOS-Student await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student") .GetItemQueryStreamIterator( queryText: queryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{schoolId}"), MaxItemCount = -1 })) { continuationToken = item.GetContinuationToken(); using var json = await JsonDocument.ParseAsync(item.ContentStream); if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0) { List<(string id, string name, string picture, int year, string no)> students = new List<(string id, string name, string picture, int year, string no)>(); var accounts = json.RootElement.GetProperty("Documents").EnumerateArray(); while (accounts.MoveNext()) { JsonElement acc = accounts.Current; string classId = acc.GetProperty("classId").GetString(); acc.TryGetProperty("irs", out JsonElement irs); if (string.IsNullOrWhiteSpace(classId)) { notJoinClassStuds.Add( ( acc.GetProperty("id").GetString(), acc.GetProperty("name").GetString(), acc.GetProperty("picture").GetString(), acc.GetProperty("year").GetInt32(), acc.GetProperty("no").GetString(), acc.TryGetProperty("periodId", out JsonElement _periodId) && _periodId.ValueKind.Equals(JsonValueKind.String) ? _periodId.GetString() : null, $"{irs}" ) ); } else { if (dicClassStuds.ContainsKey(classId)) { dicClassStuds[classId].Add( ( acc.GetProperty("id").GetString(), acc.GetProperty("name").GetString(), acc.GetProperty("picture").GetString(), acc.GetProperty("year").GetInt32(), acc.GetProperty("no").GetString(), acc.TryGetProperty("periodId", out JsonElement _periodId) && _periodId.ValueKind.Equals(JsonValueKind.String) ? _periodId.GetString() : null, $"{irs}" ) ); } else { dicClassStuds.Add(classId, new List<(string id, string name, string picture, int year, string no, string periodId, string irs)>() { ( acc.GetProperty("id").GetString(), acc.GetProperty("name").GetString(), acc.GetProperty("picture").GetString(), acc.GetProperty("year").GetInt32(), acc.GetProperty("no").GetString(), acc.TryGetProperty("periodId",out JsonElement _periodId)&& _periodId.ValueKind.Equals(JsonValueKind.String) ? _periodId.GetString() : null,$"{irs}" ) } ); } } } } } //查學生所屬的教室及座號 List ret = new List(); //查教室的資訊，用以取得gradeId,periodId資訊。 var classInfos = await getClassInfoUseId(_azureCosmos, _dingDing, _option, schoolId, dicClassStuds.Keys.ToList()); //輪循所有教室學生的資料 foreach (var classStud in dicClassStuds) { string classId = null, classNo = null, className = null, gradeId = null, periodId = null; int classYear = -1; if (classInfos.ContainsKey(classStud.Key)) { classId = classInfos[classStud.Key].GetProperty("id").GetString(); classNo = classInfos[classStud.Key].GetProperty("no").GetString(); className = classInfos[classStud.Key].GetProperty("name").GetString(); periodId = classInfos[classStud.Key].TryGetProperty("periodId", out JsonElement _periodId) && _periodId.ValueKind.Equals(JsonValueKind.String) ? _periodId.GetString() : null; if (classInfos[classStud.Key].TryGetProperty("year", out JsonElement year)) { if (year.ValueKind.Equals(JsonValueKind.Number)) { classYear = classInfos[classStud.Key].GetProperty("year").GetInt32(); } } } var tmp = classStud.Value.Select(o => new { o.id, o.name, o.picture, o.year, o.no, classId, classNo, className, gradeId, periodId = string.IsNullOrEmpty(periodId) ? o.periodId : periodId, classYear, irs = o.irs }); ret.AddRange(tmp); } //彙整沒有加入教室的學生 notJoinClassStuds.ForEach(o => ret.Add( new { o.id, o.name, o.picture, o.year, o.no, classId = (string)null, classNo = (string)null, className = (string)null, gradeId = (string)null, o.periodId, classYear = -1, irs = o.irs })); return ret; } catch (Exception ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getStudents()\n{ex.Message}\n{ex.StackTrace},", GroupNames.醍摩豆服務運維群組); } return null; } /// /// 取得該校所有教室內的名單 /// /// /// public async Task> getClassStudent(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId, string classId = null) { try { string queryText = $"SELECT VALUE FROM c WHERE c."; //if (!string.IsNullOrWhiteSpace(classId)) queryText += $" AND c.id = '{classId}'"; Dictionary listStudent = new Dictionary(); await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "School") .GetItemQueryStreamIterator(queryText: queryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{schoolId}") })) { using var json = await JsonDocument.ParseAsync(item.ContentStream); if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0) { JsonElement.ArrayEnumerator accounts = json.RootElement.GetProperty("Documents").EnumerateArray(); while (accounts.MoveNext()) { JsonElement account = accounts.Current; string cId = account.GetProperty("id").GetString(); var students = account.GetProperty("students").Clone(); listStudent.Add(cId, students); } } } return listStudent; } catch (Exception ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getClassStudentAsync()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); } return null; } /// /// 刪除學生，非透過批量刪除方法。 /// /// /// /// public static async Task> deleteStudents(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId, JsonElement.ArrayEnumerator students) { List sucIds = new List(); try { var exceptions = new List(); List scGroupLists = new List(); List teGroupLists = new List(); var container = _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student"); while (students.MoveNext()) { string id = string.Empty; try { JsonElement student = students.Current; id = student.GetProperty("id").GetString(); var ret = await container.DeleteItemStreamAsync(id, new PartitionKey($"Base-{schoolId}")); if (ret.Status == (int)HttpStatusCode.NoContent) sucIds.Add(id); await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator(queryText: $"select value(c) from c join A0 in c.members where A0.id = '{id}' and A0.code='{schoolId}' ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"GroupList-{schoolId}") })) { scGroupLists.Add(item); } await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryIterator(queryText: $"select value(c) from c join A0 in c.members where A0.id = '{id}' and A0.code='{schoolId}' ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"GroupList") })) { teGroupLists.Add(item); } if (scGroupLists.Count > 0) { foreach (GroupList stuList in scGroupLists) { for (int j = 0; j < stuList.members.Count; j++) { if (id.Equals(stuList.members[j].id) && stuList.members[j].code.Equals(schoolId)) { stuList.members.RemoveAt(j); stuList.scount -= 1; break; } } await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "School").ReplaceItemAsync(stuList, stuList.id, new PartitionKey(stuList.code)); } } if (teGroupLists.Count > 0) { foreach (GroupList stuList in teGroupLists) { for (int j = 0; j < stuList.members.Count; j++) { if (id.Equals(stuList.members[j].id) && stuList.members[j].code.Equals(schoolId)) { stuList.members.RemoveAt(j); stuList.scount -= 1; break; } } await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Teacher").ReplaceItemAsync(stuList, stuList.id, new PartitionKey(stuList.code)); } } } catch (CosmosException ex) { exceptions.Add(ex); } catch (Exception ex) { exceptions.Add(ex); } } if (exceptions.Count == 0) return sucIds; else if (exceptions.Count > 1) throw new AggregateException(exceptions); else if (exceptions.Count == 1) throw exceptions.Single(); } catch (CosmosException ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/deleteStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); } catch (Exception ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/deleteStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); } return sucIds; } /// /// 將學生基本資料內的classId、no、groupId及groupName清為null。 /// /// /// ["id1","id2",...] /// public static async Task<(List studs, List nonexistentIds, List errorIds)> removeStudentClassInfo(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId, JsonElement.ArrayEnumerator students) { //紀錄輸入的學生 List impStuds = new List(); //紀錄更新成功的學生 List sucStuds = new List(); //記錄沒查到的學生 List nonexistentIds = new List(); //紀錄更新出錯的學生 List errorIds = new List(); //整理輸入的學生資訊 while (students.MoveNext()) { JsonElement student = students.Current; impStuds.Add(student.GetString()); } if (impStuds.Count == 0) return (null, null, null); string queryText = $"SELECT VALUE c FROM c WHERE c.id IN ({string.Join(",", impStuds.Select(o => $"'{o}'"))})"; await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student") .GetItemQueryStreamIterator( queryText: queryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{schoolId}") })) { using var json = await JsonDocument.ParseAsync(item.ContentStream); if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0) { JsonElement.ArrayEnumerator docs = json.RootElement.GetProperty("Documents").EnumerateArray(); while (docs.MoveNext()) { JsonElement doc = docs.Current; doc.TryGetProperty("id", out var tmpId); var id = tmpId.GetString(); using var stream = new MemoryStream(); using var writer = new Utf8JsonWriter(stream); writer.WriteStartObject(); foreach (var element in doc.EnumerateObject()) { //將教室相關欄位清空 switch (true) { case bool _ when element.Name.Equals("classId", StringComparison.Ordinal): writer.WriteNull("classId"); break; case bool _ when element.Name.Equals("no", StringComparison.Ordinal): writer.WriteNull("no"); break; case bool _ when element.Name.Equals("groupId", StringComparison.Ordinal): writer.WriteNull("groupId"); break; case bool _ when element.Name.Equals("groupName", StringComparison.Ordinal): writer.WriteNull("groupName"); break; case bool _ when element.Name.Equals("irs", StringComparison.Ordinal): writer.WriteNull("irs"); break; case bool _ when element.Name.StartsWith("_", StringComparison.Ordinal): break; default: element.WriteTo(writer); break; } } writer.WriteEndObject(); writer.Flush(); var ret = await _azureCosmos .GetCosmosClient() .GetContainer(Constant.TEAMModelOS, "Student") .ReplaceItemStreamAsync(stream, id, new PartitionKey($"Base-{schoolId}")); if (ret.Status == (int)HttpStatusCode.OK) { sucStuds.Add(id); } else { impStuds.Remove(id); errorIds.Add(id); await _dingDing.SendBotMsg( $"IES5，{_option.Location}，StudentController/removeStudentClassInfo()，CosmosDB response:{ret.Status}\nBase-{schoolId}，id:{id}", GroupNames.醍摩豆服務運維群組); } } } } //將impStuds內的資料移除sucStuds及errorIds，所得的結果就是不存在於資料庫的id。 sucStuds.ForEach(o => impStuds.Remove(o)); errorIds.ForEach(o => impStuds.Remove(o)); return (sucStuds, impStuds, errorIds); } /// /// 取得教室資訊，使用classId進行查詢。 /// /// private static async Task> getClassInfoUseId(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId, List classIds) { try { if (!(classIds == null || classIds.Count == 0)) { string queryText = $"SELECT * FROM c WHERE c.code = 'Class-{schoolId}' AND c.id IN ({string.Join(",", classIds.Select(o => $"'{o}'"))})"; Dictionary dicClassInfo = new Dictionary(); await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "School") .GetItemQueryStreamIterator(queryText: queryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{schoolId}") })) { using var json = await JsonDocument.ParseAsync(item.ContentStream); if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0) { var classInfos = json.RootElement.GetProperty("Documents").EnumerateArray(); while (classInfos.MoveNext()) { JsonElement account = classInfos.Current; string id = account.GetProperty("id").GetString(); dicClassInfo.Add(id, account.Clone()); } } } return dicClassInfo; } else return null; } catch (CosmosException ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getClassInfoUseId()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); } catch (Exception ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getClassInfoUseId()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); } return null; } /// /// 取得教室資訊，使用classNo進行查詢。 /// /// private static async Task> getClassInfoUseNo(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId, Dictionary classNos) { try { Dictionary dicClassInfo = new Dictionary(); if (!(classNos == null || classNos.Count == 0)) { foreach (var key in classNos.Keys) { string queryText = $"SELECT * FROM c WHERE c.code = 'Class-{schoolId}' AND c.no='{classNos[key].no}' and c.year={classNos[key].year} and c.periodId='{classNos[key].periodId}' "; await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "School") .GetItemQueryIterator(queryText: queryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{schoolId}") })) { dicClassInfo[item.id] = item; item.name = classNos[key].className; await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "School").ReplaceItemAsync(item, item.id, new PartitionKey(item.code)); } } return dicClassInfo; } else return null; } catch (CosmosException ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getClassInfoUseId()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); } catch (Exception ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getClassInfoUseId()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); } return null; } /// /// 批量更新學生資訊，目前支持更新姓名、密碼、座號、性別及教室id，匯入時ClassId為必填。 /// /// /// /// public static async Task<(List studs, Dictionary> classDuplNos, List nonexistentIds, List errorIds, Dictionary> errorNos, List errorClassId)> updateStudents(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId, JsonElement.ArrayEnumerator students) { try { //整理輸入的資料->檢查輸入資料有沒有重複座號->取得欲加入的教室資訊->查詢學生並將資料更新並寫入 //Key:id Value:學生基本資訊 var studentInfos = new Dictionary(); //用於進行座號是否重複查詢時使用 var classStuds = new Dictionary>(); //紀錄教室"輸入"的學生座號是否有重複 var impClassDuplNo = new Dictionary>(); //紀錄不存在的學生id var nonexistentIds = new List(); //紀錄跟現有雲端學生座號重複的 var errorNos = new Dictionary>(); //紀錄處理錯誤的id，cosmosdb寫入時錯誤等... var errorIds = new List(); //紀錄沒找到的classId var errorClassId = new List(); //紀錄輸出結果 var retStuds = new List(); //整理輸入資料 while (students.MoveNext()) { JsonElement student = students.Current; if (student.TryGetProperty("id", out var id)) { //確認是否有id欄位，並且確認是否有給pw欄位，若無給或是null empty等，則使用id當密碼。 if (!string.IsNullOrWhiteSpace(id.GetString())) { string salt = null, pw = null, name = null, gender = null, mail = null, mobile = null, classId = null, periodId = null, irs = null, no = null; int year = 0; //有給pw欄位才進行處理 if (student.TryGetProperty("pw", out var tmpPw)) { var response = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student").ReadItemStreamAsync(id.GetString(), new PartitionKey($"Base-{schoolId}")); if (response.Status == 200) { var rjson = await JsonDocument.ParseAsync(response.ContentStream); rjson.RootElement.TryGetProperty("salt", out JsonElement _salt); salt = $"{_salt}"; } if (string.IsNullOrWhiteSpace(salt)) { salt = Utils.CreatSaltString(8); } pw = !string.IsNullOrWhiteSpace(tmpPw.GetString()) ? Utils.HashedPassword(tmpPw.GetString(), salt) : Utils.HashedPassword(id.GetString(), salt); } if (student.TryGetProperty("name", out var tmpName)) name = tmpName.GetString(); if (student.TryGetProperty("gender", out var tmpGender)) gender = tmpGender.GetString(); if (student.TryGetProperty("mail", out var tmpMail)) mail = tmpMail.GetString(); if (student.TryGetProperty("mobile", out var tmpMobile)) mobile = tmpMobile.GetString(); if (student.TryGetProperty("year", out var tmpYear)) year = tmpYear.GetInt32(); if (student.TryGetProperty("periodId", out var tmpperiodId)) periodId = tmpperiodId.GetString(); if (student.TryGetProperty("irs", out var tmpIrs)) irs = tmpIrs.GetString(); if (student.TryGetProperty("classId", out var tmpclassId)) classId = tmpclassId.GetString(); else { errorClassId.Add(id.GetString()); continue; } //如果有給該欄位，且是給空的，代表要清空 if (student.TryGetProperty("no", out var tmpNo)) no = tmpNo.GetString(); if (!studentInfos.ContainsKey(id.GetString())) { //如果有給classId且是給空的，則也將no設為空，後續才能將no欄位清空。 if (classId != null && classId.Length == 0) no = string.Empty; //classId => 沒給欄位(null) 有給欄位("") 但更新一定得給教室? if (classId == null) { classId = ""; } if (classStuds.ContainsKey(classId)) { classStuds[classId].Add((id.GetString(), salt, pw, name, year, null, gender, null, null, classId, no, periodId)); } else { classStuds.Add( classId, new List<(string id, string salt, string pw, string name, int year, string picture, string gender, string mail, string mobile, string classId, string no, string periodId)>() { (id.GetString(), salt, pw, name, year, null, gender, null, null, classId, no,periodId) }); } //picture,mail,mobile暫不支持批量更新 studentInfos.Add(id.GetString(), (salt, pw, name, year, null, gender, null, null, classId, no, periodId, irs)); //先將id加進去後面再做刪除動作 nonexistentIds.Add(id.GetString()); } } } } //檢查所有輸入的班級資料內，學生座號是否有重複。 for (int ii = 0; ii <= classStuds.Values.Count - 1; ii++) { var duplicateNo = classStuds.ElementAt(ii).Value.GroupBy(o => o.no).Where(o => o.Count() > 1).Select(o => o.Key).ToList(); duplicateNo.Remove(""); duplicateNo.Remove(null); var wrongStuds = classStuds.ElementAt(ii).Value.Where(o => duplicateNo.Contains(o.no)).Select(o => o).ToList(); impClassDuplNo.Add(classStuds.ElementAt(ii).Key, wrongStuds.Select(o => o.id).ToList()); wrongStuds.ForEach(o => { classStuds.ElementAt(ii).Value.Remove(o); nonexistentIds.Remove(o.id); }); classStuds[classStuds.ElementAt(ii).Key] = classStuds.ElementAt(ii).Value; } //查詢欲加入的教室資訊。 var classInfo = await getClassInfoUseId(_azureCosmos, _dingDing, _option, schoolId, classStuds.Keys.ToList()); //準備查詢db資料 CosmosContainer cosmosContainer = _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student"); //查要移除教室或是沒加入教室的學生 foreach (var item in classStuds) { //如果沒有任何學生要更新，則跳過該間教室。 if (item.Value.Count == 0) continue; string classId = null, className = null, classNo = null, gradeId = null, periodId = null; //如果教室不存在的話(填錯教室之類的狀況)，則記錄教室的id及學生id。 if (classInfo.ContainsKey(item.Key)) { classId = item.Key; className = classInfo[item.Key].GetProperty("name").GetString(); classNo = classInfo[item.Key].GetProperty("no").GetString(); //gradeId = classInfo[item.Key].GetProperty("gradeId").GetString(); 此欄位已不使用 periodId = classInfo[item.Key].GetProperty("periodId").GetString(); } else if (item.Key.Length == 0) { } else { //沒查到有該間教室的資訊，故將該間教室的ID及學生資料清單記起來，並且跳過不處理該資料。 errorClassId.Add(item.Key); item.Value.ForEach(o => nonexistentIds.Remove(o.id)); continue; } //檢查座號是否有重複 //若只是改基本資料，該處還是會查到相同的座號。 var sutdNos = item.Value.Select(o => o.no).ToList(); var existNos = await checkStudNo(_azureCosmos, _dingDing, _option, schoolId, item.Key, sutdNos); //更新並寫入學生資料 if (item.Value.Count != 0) { //查學生的基本資料(該間教室全部的學生) string queryText = $"SELECT * FROM c WHERE c.id IN ({string.Join(",", item.Value.Select(o => $"'{o.id}'"))})"; List listStudent = new List(); await foreach (Response responseItem in cosmosContainer .GetItemQueryStreamIterator( queryText: queryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{schoolId}") })) { using var json = await JsonDocument.ParseAsync(responseItem.ContentStream); if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0) { var accounts = json.RootElement.GetProperty("Documents").EnumerateArray(); while (accounts.MoveNext()) { JsonElement account = accounts.Current; string id = $"{ account.GetProperty("id")}"; nonexistentIds.Remove(id); //舊的座號，基本上不會重複，但可能會是空的 string no = $"{account.GetProperty("no")}"; account.TryGetProperty("irs", out JsonElement irsjson); string irs = $"{irsjson}"; //用來記錄最後更改完的資料 (string salt, string pw, string name, int year, string picture, string gender, string mail, string mobile, string classId, string no, string periodId, string irs) tmpData = (studentInfos[id].salt, studentInfos[id].pw, studentInfos[id].name, studentInfos[id].year, studentInfos[id].picture, studentInfos[id].gender, studentInfos[id].mail, studentInfos[id].mobile, studentInfos[id].classId, studentInfos[id].no, studentInfos[id].periodId, studentInfos[id].irs); bool isUpPwDone = false; bool isWrong = false; //開始組Json using var memoryStream = new MemoryStream(); using var writer = new Utf8JsonWriter(memoryStream); writer.WriteStartObject(); foreach (var element in account.EnumerateObject()) { if (isWrong) break; switch (true) { case bool _ when element.Name.Equals("name", StringComparison.Ordinal): if (string.IsNullOrWhiteSpace(studentInfos[id].name)) { element.WriteTo(writer); tmpData.name = element.Value.GetString(); } else { writer.WriteString("name", studentInfos[id].name); } break; case bool _ when element.Name.Equals("pw", StringComparison.Ordinal): case bool _ when element.Name.Equals("salt", StringComparison.Ordinal): if (!isUpPwDone && !string.IsNullOrWhiteSpace(studentInfos[id].salt) && !string.IsNullOrWhiteSpace(studentInfos[id].pw)) { writer.WriteString("salt", studentInfos[id].salt); writer.WriteString("pw", studentInfos[id].pw); isUpPwDone = true; } break; case bool _ when element.Name.Equals("periodId", StringComparison.Ordinal): if (string.IsNullOrWhiteSpace(studentInfos[id].periodId)) { element.WriteTo(writer); tmpData.periodId = element.Value.GetString(); } else { writer.WriteString("periodId", studentInfos[id].periodId); } break; case bool _ when element.Name.Equals("gender", StringComparison.Ordinal): if (string.IsNullOrWhiteSpace(studentInfos[id].gender)) { element.WriteTo(writer); tmpData.gender = element.Value.GetString(); } else { writer.WriteString("gender", studentInfos[id].gender); } break; case bool _ when element.Name.Equals("year", StringComparison.Ordinal): if (studentInfos[id].year == 0) { element.WriteTo(writer); tmpData.year = element.Value.GetInt32(); } else { writer.WriteNumber("year", studentInfos[id].year); } break; case bool _ when element.Name.Equals("classId", StringComparison.Ordinal): if (studentInfos[id].classId != null && studentInfos[id].classId.Length == 0) { writer.WriteNull("classId"); writer.WriteNull("groupId"); writer.WriteNull("groupName"); tmpData.classId = null; } else if (string.IsNullOrWhiteSpace(studentInfos[id].classId)) { element.WriteTo(writer); tmpData.classId = element.Value.GetString(); } else { writer.WriteString("classId", studentInfos[id].classId); } break; case bool _ when element.Name.Equals("no", StringComparison.Ordinal): //移除座號的話會給空的 if (studentInfos[id].no != null && studentInfos[id].no.Length == 0) { writer.WriteNull("no"); tmpData.no = null; } else if (string.IsNullOrWhiteSpace(studentInfos[id].no)) { element.WriteTo(writer); tmpData.no = element.Value.GetString(); } else { //如果要更新的座號，跟已存在的座號相同，則不進行更新。 //沒有設定過舊no，或舊座號與新座號不同，則要進行重複座號的檢查。 舊no=null or 舊no!=新no //if (string.IsNullOrWhiteSpace(no) || (!string.IsNullOrWhiteSpace(no) && !no.Equals(studentInfos[id].no))) //{ // //如果有檢查到新座號和舊座號重複 // if (existNos.Any(o => o.Item2.Contains(studentInfos[id].no))) //.Contains(studentInfos[id].no)) // { // if (errorNos.ContainsKey(id)) // { // errorNos[id].Add(studentInfos[id].no); // } // else // { // errorNos.Add(id, new List() { studentInfos[id].no }); // } // isWrong = true; // break; // } //} writer.WriteString("no", studentInfos[id].no); } break; case bool _ when element.Name.Equals("irs", StringComparison.Ordinal): //移除座號的話會給空的 if (studentInfos[id].irs != null && studentInfos[id].irs.Length == 0) { writer.WriteNull("irs"); tmpData.irs = null; } else if (string.IsNullOrWhiteSpace(studentInfos[id].irs)) { element.WriteTo(writer); tmpData.irs = element.Value.GetString(); } else { writer.WriteString("irs", studentInfos[id].irs); } break; case bool _ when element.Name.StartsWith("_", StringComparison.Ordinal): break; default: element.WriteTo(writer); break; } } //如果有錯誤，如座號重覆等，就會跳過該次更新。 if (isWrong) { await writer.DisposeAsync(); continue; } if (!account.TryGetProperty("irs", out JsonElement _irs) || _irs.ValueKind.Equals(JsonValueKind.Undefined)) { writer.WriteString("irs", studentInfos[id].irs); } //若密碼和鹽沒有更新，就把舊的資料寫回去 if (!isUpPwDone) { writer.WriteString("salt", account.GetProperty("salt").GetString()); writer.WriteString("pw", account.GetProperty("pw").GetString()); } writer.WriteEndObject(); writer.Flush(); try { var ret = await cosmosContainer.ReplaceItemStreamAsync(memoryStream, id, new PartitionKey($"Base-{schoolId}")); //將更新完的id從字典內移除，保留沒查到的。 if (ret.Status == (int)HttpStatusCode.OK) { nonexistentIds.Remove(id); retStuds.Add(new { id, tmpData.name, tmpData.picture, tmpData.year, tmpData.no, classId, classNo, className, gradeId, periodId, tmpData.irs }); } else errorIds.Add(id); } catch (CosmosException ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/updateStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); errorIds.Add(id); } catch (Exception ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/updateStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); errorIds.Add(id); } } } //將輸入不存在的資料移除。 // nonexistentIds.ForEach(o => studentInfos.Remove(o)); } } } errorClassId.ForEach(o => impClassDuplNo.Remove(o)); return (retStuds, impClassDuplNo.Where(o => o.Value.Count != 0).ToDictionary(o => o.Key, o => o.Value), nonexistentIds, errorIds, errorNos, errorClassId); } catch (CosmosException ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/updateStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); } catch (Exception ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/updateStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); } return (null, null, null, null, null, null); } /// /// 創建學生帳號，目前SDK4.0預覽版還不支援批量創建(TransactionalBatch)，待SDK正式發行時在優化此代碼。 /// /// /// 已存在的ID private async Task<(bool isSuc, List existId)> createStudents(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, List userStudents) { var existId = new List(); var exceptions = new List(); try { var container = _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student"); Parallel.ForEach(userStudents, async item => { try { await container.CreateItemAsync(item); } catch (CosmosException ex) { if (ex.Status == (int)HttpStatusCode.Conflict) existId.Add(item.id); else exceptions.Add(ex); } catch (Exception ex) { exceptions.Add(ex); } }); if (exceptions.Count == 0) return (true, existId); else if (exceptions.Count > 1) throw new AggregateException(exceptions); else if (exceptions.Count == 1) throw exceptions.Single(); } catch (AggregateException ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); } catch (Exception ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); } return (false, existId); } /// /// 取得該教室的學生座號，若有給座號LIST，則座號存在才會被查到；反之，若沒給則會將該間教室所有座號抓出來。 /// /// /// /// /// public static async Task> checkStudNo(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId, string classId, List nos = null) { List<(string id, string no)> ret = new List<(string id, string no)>(); string queryText = $"SELECT c.id, c.no FROM c WHERE c.classId = '{classId}' AND c.code = 'Base-{schoolId}'"; if (nos != null) queryText += $"AND c.no IN ({string.Join(",", nos.Select(o => $"'{o}'"))})"; await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student") .GetItemQueryStreamIterator(queryText: queryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{schoolId}") })) { using var json = await JsonDocument.ParseAsync(item.ContentStream); if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0) { var classInfos = json.RootElement.GetProperty("Documents").EnumerateArray(); while (classInfos.MoveNext()) { JsonElement account = classInfos.Current; string id = account.GetProperty("id").GetString(); string no = account.GetProperty("no").GetString(); ret.Add((id, no)); } } } return ret; } /// /// 取得年級資訊 /// /// /// Key:periodId Vaule:list gradeInfo public static async Task>> getGrades(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId) { try { //Key:學制 Value:年級資訊list Dictionary> dicPeriod = new Dictionary>(); var response = await _azureCosmos .GetCosmosClient() .GetContainer(Constant.TEAMModelOS, "School") .ReadItemStreamAsync(schoolId, new PartitionKey("Base")); if (response.Status != (int)HttpStatusCode.OK) return null; using Stream stream = response.ContentStream; var jsonDoc = await JsonDocument.ParseAsync(stream); var emumObject = jsonDoc.RootElement.EnumerateObject(); var period = jsonDoc.RootElement.GetProperty("period").EnumerateArray(); while (period.MoveNext()) { List<(int gradeId, string gradeName)> gradeInfos = new List<(int gradeId, string gradeName)>(); JsonElement jsonPeriod = period.Current; var periodId = jsonPeriod.GetProperty("id").GetString(); var grades = jsonPeriod.GetProperty("grades").ToObject>(); for (int index = 0; index < grades.Count; index++) { gradeInfos.Add((index, grades[index])); } //var grades = jsonPeriod.GetProperty("grades").EnumerateArray(); //while (grades.MoveNext()) //{ // JsonElement grade = grades.Current; // var gradeId = grade.GetInt32(); // var gradeName = grade.GetProperty("name").GetString(); // gradeInfos.Add((gradeId, gradeName)); //} dicPeriod.Add(periodId, gradeInfos); } return dicPeriod; } catch (Exception ex) { await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getGrades()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組); return null; } } public struct studCreateInfo { public studCreateInfo(string id, string name, string gender, int year, string pw, string classId, string no, string periodId) { this.id = id; this.name = name; this.gender = gender; this.year = year; this.pw = pw; this.classId = classId; this.no = no; this.periodId = periodId; } public string id { get; } public string name { get; } public string gender { get; } public int year { get; } public string pw { get; } public string classId { get; } public string no { get; } public string periodId { get; set; } } } }