using Azure; using Azure.Cosmos; using Azure.Messaging.ServiceBus; using HTEXLib.COMM.Helpers; using Microsoft.Extensions.Configuration; using OpenXmlPowerTools; 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; using TEAMModelOS.SDK.Models.Service; 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 guardians,string gender)> studs, Dictionary classInfo, Dictionary> classStudNo, List errorYear, List duplId) doSortImpStuds(string schoolId, JsonElement.ArrayEnumerator students) { //批量匯入 檢查輸入數據 確認座號 確認教室(創建教室) 確認學生存不存在或是要不要更新 //存放輸入的學生資訊 key:stud id value:學生詳細資料 Dictionary guardians,string gender) > dicStuds = new Dictionary guardians,string gender)>(); //存放教室資訊用 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, string guardian, string gName, string gPhone, string imei, List guardians, string gender) studentInfo = (null, null, 0, null, null, null, null, null, 0, null, null, null, null, null,null) ; 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())); } } } ///导入的时候 if (student.TryGetProperty("guardian", out var guardian) && !string.IsNullOrWhiteSpace($"{guardian}")) { studentInfo.guardian =$"{guardian}"; student.TryGetProperty("gName", out var gName); student.TryGetProperty("gPhone", out var gPhone); studentInfo.gName = $"{gName}"; studentInfo.gPhone = $"{gPhone}"; } //更新的时候 if (student.TryGetProperty("guardians", out var _guardians) && _guardians.ValueKind.Equals(JsonValueKind.Array)) { List guardians = _guardians.Deserialize>(); studentInfo.guardians = guardians; } if (student.TryGetProperty("imei", out var tmpImei) && !string.IsNullOrWhiteSpace($"{tmpImei}")) studentInfo.imei = tmpImei.GetString(); if (student.TryGetProperty("gender", out var tmpgender) && !string.IsNullOrWhiteSpace($"{tmpgender}")) studentInfo.gender = tmpgender.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(); List classes = new List(); foreach (var classInfo in classInfos) { classes.Add(classInfo.Value); 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); if (string.IsNullOrWhiteSpace(stud.Value.gender)) writerNew.WriteNull("gender"); else writerNew.WriteString("gender", stud.Value.gender); 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"); ///写入监护人 if (!string.IsNullOrWhiteSpace(stud.Value.gPhone)) { writerNew.WriteStartArray("guardians"); writerNew.WriteStartObject(); writerNew.WriteString("relation", stud.Value.guardian); writerNew.WriteString("name", stud.Value.gName); writerNew.WriteString("mobile", stud.Value.gPhone); writerNew.WriteEndObject(); writerNew.WriteEndArray(); } writerNew.WriteEndObject(); writerNew.Flush(); if (!string.IsNullOrWhiteSpace(stud.Value.imei)) { await upsertImei(stud.Key, stud.Value.imei, schoolId, "keep", cosmosContainer); } 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.gender)) { student.gender = stud.Value.gender; } if (!string.IsNullOrWhiteSpace(stud.Value.classNo)) { student.classId = classId; } if (!string.IsNullOrWhiteSpace(stud.Value.periodId)) { student.periodId = stud.Value.periodId; } if (!string.IsNullOrWhiteSpace(student.classId)) { var clazz = classes.FindAll(x => x.id.Equals(student.classId)); if (clazz.IsNotEmpty()) { student.graduate = clazz[0].graduate; } } if (!isUpPwDone) { student.pw = stud.Value.pw; student.salt = stud.Value.salt; isUpPwDone = true; } tmpStudInfo.picture = student.picture; if (!string.IsNullOrWhiteSpace(stud.Value.gPhone)) { List guardians = new List(); string sql = $"select distinct value (c) from c where c.mobile='{stud.Value.gPhone}' "; await foreach (var item in cosmosContainer.GetItemQueryIterator(queryText: sql, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey("Base-Guardian") })) { guardians.Add(item); } if (guardians.Any()) { guardians.ForEach(async x => { GuardianStudent guardianStudent = x.students.Find(s => s.id.Equals(stud.Key) && s.code.Equals(schoolId)); if (guardianStudent == null) { x.students.Add(new GuardianStudent { type=2,id=stud.Key,code=schoolId,name=stud.Value.name }); } await cosmosContainer.ReplaceItemAsync(x, x.id, new PartitionKey(x.code)); }); } else { Guardian guardian = new Guardian { id = Guid.NewGuid().ToString(), code= "Base-Guardian", pk= "Guardian", mobile=stud.Value.gPhone, name = stud.Value.gName, students = new List { new GuardianStudent { type = 2, id = stud.Key, code = schoolId, name = stud.Value.name } } }; await cosmosContainer.CreateItemAsync(guardian, new PartitionKey(guardian.code)); } var studentGuardian = student.guardians.Find(x => !string.IsNullOrWhiteSpace(x.mobile) && x.mobile.Equals(stud.Value.gPhone)); if (studentGuardian != null) { studentGuardian.name = stud.Value.gName; studentGuardian.relation = stud.Value.guardian; } else { student.guardians.Add(new StudentGuardian { mobile = stud.Value.gPhone, name = stud.Value.gName, relation = stud.Value.guardian }); } } if (stud.Value.guardians.IsNotEmpty()) { student.guardians=stud.Value.guardians; } 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); } /// /// grand_type /// 为import：如果导入则已最新，没导入则不动这个字段的值，维持现状 /// 为create:如果有值，则绑定，并删除stuid关联的别的imei. /// 为update /// 为delete /// /// /// /// /// /// /// clean 当传入的电子学生证值为空，判断要强制清除关联的学生电子学生证 ， /// keep 保持现状，导入时 /// delete 因学生被删除，强制删除电子学生证。 /// public static async Task upsertImei(string stuid ,string imeiid,string schoolId,string grand_type, CosmosContainer cosmosContainer) { List imeis = new List(); string sql = $"select value c from c where c.stuid='{stuid}' and c.school='{schoolId}' "; await foreach (var item in cosmosContainer.GetItemQueryIterator(queryText: sql, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey("Imei") })) { imeis.Add(item); } switch (grand_type) { case "clean": case "keep": bool notin = true; List update = new List(); List delete = new List(); imeis.ForEach(x => { //如果传入的电子学生证id不存在，且是单个更新或创建，则解除学生id的电子学生证绑定，并删除该电子学生证。 if (string.IsNullOrWhiteSpace(imeiid)) { if (grand_type.Equals("clean") ) { delete.Add(x); }// 如果是导入模式，且电子学生证没有值则不动。 } else { //如果电子学生证有值则，解除之前的所有与当前电子学生证id不符的绑定。 if (!x.id.Equals(imeiid)) { x.stuid = null; x.school = null; delete.Add(x); } else { //存在且正确绑定 notin = false; } } }); //当前学生还未有任何电子学生证，且有新的电子学生证id传入，则需要捞出电子学生证，绑定，如果没有捞出，则新建电子学生证，并绑定 if (notin && !string.IsNullOrWhiteSpace(imeiid)) { var imeiResponse = await cosmosContainer.ReadItemStreamAsync(imeiid, new PartitionKey("Imei")); if (imeiResponse.Status == 200) { Imei imeiDb = JsonDocument.Parse(imeiResponse.Content).RootElement.Deserialize(); imeiDb.stuid = stuid; imeiDb.school = schoolId; await cosmosContainer.ReplaceItemAsync(imeiDb, imeiDb.id, new PartitionKey("Imei")); } else { Imei imei = new Imei { id = imeiid, code = "Imei", pk = "Imei", stuid = stuid, school = schoolId }; await cosmosContainer.CreateItemAsync(imei, new PartitionKey($"Imei")); }; } if (delete.Any()) { await cosmosContainer.DeleteItemsStreamAsync(delete.Select(x => x.id).ToList(), "Imei"); } break; case "delete": if (imeis.Any()) { await cosmosContainer.DeleteItemsStreamAsync(imeis.Select(x => x.id).ToList(), "Imei"); } break; } } /// /// 單純建立單一學生 /// /// /// 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"); if (studCreateInfo.guardians.IsNotEmpty()) { writer.WriteStartArray("guardians"); foreach (var guardian in studCreateInfo.guardians) { writer.WriteStartObject(); writer.WriteString("relation", guardian.relation); writer.WriteString("name", guardian.name); writer.WriteString("mobile", guardian.mobile); writer.WriteEndObject(); } writer.WriteEndArray(); foreach (var studentGuardian in studCreateInfo.guardians) { List guardians = new List(); string sql = $"select distinct value (c) from c where c.mobile='{studentGuardian.mobile}' "; await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS,Constant.Student).GetItemQueryIterator(queryText: sql, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey("Base-Guardian") })) { guardians.Add(item); } if (guardians.Any()) { guardians.ForEach(async x => { GuardianStudent guardianStudent = x.students.Find(s => s.id.Equals(studCreateInfo.id) && s.code.Equals(schoolId)); if (guardianStudent == null) { x.students.Add(new GuardianStudent { type = 2, id = studCreateInfo.id, code = schoolId, name = studCreateInfo.name }); } await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.Student).ReplaceItemAsync(x, x.id, new PartitionKey(x.code)); }); } else { Guardian guardian = new Guardian { id = Guid.NewGuid().ToString(), code = "Base-Guardian", pk = "Guardian", mobile = studentGuardian.mobile, name = studentGuardian.name, students = new List { new GuardianStudent { type = 2, id = studCreateInfo.id, code = schoolId, name = studCreateInfo.name } } }; await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.Student).CreateItemAsync(guardian, new PartitionKey(guardian.code)); } } } else { writer.WriteStartArray("guardians"); writer.WriteStartObject(); writer.WriteEndObject(); writer.WriteEndArray(); } writer.WriteEndObject(); writer.Flush(); var response = await _azureCosmos .GetCosmosClient() .GetContainer(Constant.TEAMModelOS, "Student") .CreateItemStreamAsync(stream, new PartitionKey($"Base-{schoolId}")); //更新电子学生证、 await upsertImei(studCreateInfo.id, studCreateInfo.imei, schoolId, "keep", _azureCosmos .GetCosmosClient() .GetContainer(Constant.TEAMModelOS, "Student")); 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,int inyear ,int graduate=0 ) { try { //TODO : 進階查詢選項調整、部分地方可用並行處理 //以學校學生角度去抓資料 Dictionary guardians)>> dicClassStuds = new Dictionary guardians)>>(); List<(string id, string name, string picture, int year, string no, string periodId, string irs, string imei, string gender, int graduate, List guardians)> notJoinClassStuds = new List<(string id, string name, string picture, int year, string no, string periodId, string irs, string imei, string gender, int graduate, List guardians)>(); List imeis= new List(); string imeiQueryText = $"SELECT * FROM c WHERE c.school = '{schoolId}'"; await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student") .GetItemQueryIterator(queryText:imeiQueryText,requestOptions:new QueryRequestOptions { PartitionKey= new PartitionKey("Imei")})){ imeis.Add(item); } List graduate_students = new List(); List school_classes = new List(); string queryText = ""; if (graduate == 0) { queryText = $"SELECT * FROM c WHERE c.code = 'Base-{schoolId}' and (c.graduate = 0 or IS_DEFINED(c.graduate) = false )"; } else { queryText = $"SELECT * FROM c WHERE c.code = 'Base-{schoolId}' and c.graduate = 1 and c.year ={inyear} "; ///查询当年的毕业班 string sql = sql = $"SELECT value c FROM c where c.graduate =1 and c.year ={inyear}"; ; var client = _azureCosmos.GetCosmosClient(); await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator (queryText: sql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{schoolId}") })) { school_classes.Add(item); } //如果已经毕业的班级不为空。 if (school_classes.IsNotEmpty()) { string classSql = $"SELECT value c FROM c WHERE c.code = 'Base-{schoolId}' and c.graduate = 1 and c.classId in( {string.Join(",", school_classes.Select(x => $"'{x.id}'"))} )"; await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student").GetItemQueryIterator( queryText: classSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{schoolId}")})) { graduate_students.Add(item); } } } //回傳用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, List guardians)> students = new List<(string id, string name, string picture, int year, string no, List guardians)>(); 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); acc.TryGetProperty("guardians", out JsonElement _guardians); List guardians = new List(); if (_guardians.ValueKind.Equals(JsonValueKind.Array)) { guardians= _guardians.Deserialize>(); if (guardians.Any()) { guardians= guardians.FindAll(x => !string.IsNullOrWhiteSpace(x.mobile)) ; } } var imeiObj= imeis.Find(x => x.stuid.Equals(acc.GetProperty("id").GetString())); 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}", imeiObj?.id,//imei acc.TryGetProperty("gender", out JsonElement _gender) && _gender.ValueKind.Equals(JsonValueKind.String) ? _gender.GetString() : null, acc.TryGetProperty("graduate", out JsonElement _graduate) && _graduate.ValueKind.Equals(JsonValueKind.Number) ? int.Parse($"{_graduate}"): 0, guardians ) ); } 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}", imeiObj?.id,//imei acc.TryGetProperty("gender", out JsonElement _gender) && _gender.ValueKind.Equals(JsonValueKind.String) ? _gender.GetString() : null, acc.TryGetProperty("graduate", out JsonElement _graduate) && _graduate.ValueKind.Equals(JsonValueKind.Number) ? int.Parse($"{_graduate}") : 0, guardians ) ); } else { dicClassStuds.Add(classId, new List<(string id, string name, string picture, int year, string no, string periodId, string irs,string imei,string gender,int graduate, List guardians)>() { ( 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}" , imeiObj?.id,//imei acc.TryGetProperty("gender", out JsonElement _gender) && _gender.ValueKind.Equals(JsonValueKind.String) ? _gender.GetString() : null, acc.TryGetProperty("graduate", out JsonElement _graduate) && _graduate.ValueKind.Equals(JsonValueKind.Number) ? int.Parse($"{_graduate}"): 0, guardians ) } ); } } } } } //查學生所屬的教室及座號 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, imei=o.imei, gender = o.gender, graduate= o.graduate, guardians =o.guardians, }); 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, imei = o.imei, gender = o.gender, guardians =o.guardians, })); graduate_students.ForEach(o => { var imeiObj = imeis.Find(x => x.stuid.Equals(o.id)); Class stuClass = school_classes.Find(x => x.id.Equals(o.classId)); ret.Add( new { o.id, o.name, o.picture, o.year, o.no, classId = stuClass?.id, classNo = stuClass?.no, className = stuClass?.name, gradeId = stuClass?.gradeId, o.periodId, classYear = stuClass?.year, irs = o.irs, imei = imeiObj?.id, gender = o.gender, guardians = o.guardians, }); }); 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}")); await upsertImei(id, null, schoolId, "delete", container); 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 guardians)>(); //用於進行座號是否重複查詢時使用 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, imei = null, no = null; List guardians = 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("imei", out var tmpImei)) imei = tmpImei.GetString(); if (student.TryGetProperty("classId", out var tmpclassId)) classId = tmpclassId.GetString(); else { errorClassId.Add(id.GetString()); continue; } if (student.TryGetProperty("guardians", out var _guardians) && _guardians.ValueKind.Equals(JsonValueKind.Array) ) { guardians = _guardians.Deserialize>(); } //如果有給該欄位，且是給空的，代表要清空 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,imei, guardians)); //先將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 ,string imei, List guardians) 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, studentInfos[id].imei, studentInfos[id]. guardians); 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; } } var stu = account.Deserialize(); if (stu.guardians.IsNotEmpty() && studentInfos[id].guardians!=null) { var mobiles = stu.guardians.Select(m => m.mobile); var except = mobiles.Except(studentInfos[id].guardians.Select(x => x.mobile)); if (except.Any()) { //清除移除的监护人 List guardians = new List(); string sql = $"select distinct value (c) from c where c.mobile in ({string.Join(",", except.Select(z => $"'{z}'"))} )"; await foreach (var guard in cosmosContainer.GetItemQueryIterator(queryText: sql, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey("Base-Guardian") })) { guard.students.RemoveAll(s => s.id.Equals(id) && s.code.Equals(schoolId)); guardians.Add(guard); } guardians.ForEach(async x => { if (x.students.IsNotEmpty()) { try{await cosmosContainer.ReplaceItemAsync(x, x.id, new PartitionKey(x.code)); } catch {} } else { try { await cosmosContainer.DeleteItemStreamAsync(x.id, new PartitionKey(x.code)); } catch{ } } }); } } if (studentInfos[id].guardians.IsNotEmpty()) { foreach (var gtudentGuardian in studentInfos[id].guardians) { List guardians = new List(); string sql = $"select distinct value (c) from c where c.mobile='{gtudentGuardian.mobile}' "; await foreach (var guard in cosmosContainer.GetItemQueryIterator(queryText: sql, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey("Base-Guardian") })) { guardians.Add(guard); } if (guardians.Any()) { guardians.ForEach(async x => { GuardianStudent guardianStudent = x.students.Find(s => s.id.Equals(id) && s.code.Equals(schoolId)); if (guardianStudent == null) { x.students.Add(new GuardianStudent { type = 2, id = id, code = schoolId, name = studentInfos[id].name }); } try { await cosmosContainer.ReplaceItemAsync(x, x.id, new PartitionKey(x.code)); } catch { } }); } else { Guardian guardian = new Guardian { id = Guid.NewGuid().ToString(), code = "Base-Guardian", pk = "Guardian", mobile = gtudentGuardian.mobile, name = gtudentGuardian.name, students = new List { new GuardianStudent { type = 2, id = id, code = schoolId, name = studentInfos[id].name } } }; try { await cosmosContainer.CreateItemAsync(guardian, new PartitionKey(guardian.code)); } catch { } } } writer.WriteStartArray("guardians"); foreach (var guardian in studentInfos[id].guardians) { writer.WriteStartObject(); writer.WriteString("relation", guardian.relation); writer.WriteString("name", guardian.name); writer.WriteString("mobile", guardian.mobile); writer.WriteString("mail", guardian.mail); writer.WriteString("picture", guardian.picture); writer.WriteString("tmdid", guardian.tmdid); writer.WriteEndObject(); } writer.WriteEndArray(); } else { writer.WriteStartArray("guardians"); //writer.WriteStartObject(); //writer.WriteEndObject(); writer.WriteEndArray(); } //如果有錯誤，如座號重覆等，就會跳過該次更新。 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(); //编辑是是否要明确清除电子学生证。 await upsertImei(id, studentInfos[id].imei, schoolId, "clean", _azureCosmos .GetCosmosClient() .GetContainer(Constant.TEAMModelOS, "Student")); 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,string imei, List guardians) { this.id = id; this.name = name; this.gender = gender; this.year = year; this.pw = pw; this.classId = classId; this.no = no; this.periodId = periodId; this.imei = imei; this.guardians = guardians; } 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; } public string imei { get; set; } public List guardians { get; set; } } } }