TEAMModelOS/TEAMModelOS/Controllers/School/StudentController.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.Dynamic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using Azure;
using Azure.Cosmos;
using Azure.Storage.Sas;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Cryptography.KeyDerivation;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
using TEAMModelOS.Models;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Extension;
using TEAMModelOS.SDK.Models;

namespace TEAMModelOS.Controllers
{
    [Route("student")]
    [ApiController]
    public class StudentController : Controller
    {
        private readonly AzureCosmosFactory _azureCosmos;
        private readonly AzureStorageFactory _azureStorage;
        private readonly DingDing _dingDing;
        private readonly Option _option;

        public StudentController(
            AzureCosmosFactory azureCosmos,
            AzureStorageFactory azureStorage,
            DingDing dingDing,
            IOptionsSnapshot<Option> option
            )
        {
            _azureCosmos = azureCosmos;
            _azureStorage = azureStorage;
            _dingDing = dingDing;
            _option = option?.Value;
        }

        /// <summary>
        /// 學生帳號管理
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpPost("student-manage")]
        public async Task<IActionResult> StudentManage(JsonElement request)
        {
            try
            {
                //TODO : 權限檢查、學校檢查。
                if (!request.TryGetProperty("grant_type", out JsonElement grant_type) || !request.TryGetProperty("schoolId", out JsonElement schoolId)) return BadRequest();

                switch (grant_type.GetString())
                {
                    case "create":
                        //創建學生->將學生加入教室->若無教室則創建教室
                        var (classInfo, classStudent, unclassStudent, existId, errorYear) = await createStudents(schoolId.GetString(), request.GetProperty("students").EnumerateArray());
                        var existNo = new List<string>();
                        var classStuds = new Dictionary<string, (string periodId, string gradeId, List<string> ids)>();
                        //確認是否有教室資訊
                        if (classInfo != null && classInfo.Count != 0)
                        {
                            //取得欲加入的教室資訊。
                            Dictionary<string, JsonElement> existClass = await getClassInfoUseId(schoolId.GetString(), classInfo.Select(o => o.Key).ToList());

                            //確認存在與不存在的教室，若不存在則新創建
                            foreach (var item in classInfo)
                            {
                                if (existClass.ContainsKey(item.Key))
                                {
                                    //將學生加入教室內。
                                    var classStudents
                                        = new Dictionary<string, List<(string id, string name, string no)>>() { { item.Key, classStudent[item.Key].Select(o => (o.id, o.name, o.no)).ToList() } };
                                    (Dictionary<string, (string periodId, string gradeId, List<string> id)> classStuds, List<string> existId, List<string> existNo) retJoinClass
                                        = await joinClass(schoolId.GetString(), classStudents);
                                    classStuds.Add(item.Key, retJoinClass.classStuds[item.Key]);
                                    existNo = retJoinClass.existNo;
                                }
                                else
                                {
                                    var classStudents = await createClassInfo(schoolId.GetString(), item.Key, item.Value, classStudent[item.Key]);
                                    classStuds.Add(item.Key, classStudents[item.Key]);
                                }
                            }
                        }

                        var retStudents = classStudent.SelectMany(o => o.Value.Select(p => new { p.id, p.name, p.no, p.year, classId = o.Key, classStuds[o.Key].gradeId, classStuds[o.Key].periodId }));
                        var retUnclassStudents = unclassStudent.Select(o => new { o.id, o.name, o.no, o.year, classId = (string)null, gradeId = (string)null, periodId = (string)null });

                        IEnumerable ret = new string[] { };
                        if (retStudents.Count() != 0 && retUnclassStudents.Count() != 0)
                        {
                            ret = retStudents.Union(retUnclassStudents);
                        }
                        else if (retStudents.Count() != 0)
                        {
                            ret = retStudents;
                        }
                        else if (retUnclassStudents.Count() != 0)
                        {
                            ret = retUnclassStudents;
                        }
                        return this.Ok(new { students = ret, existId, errorYear, existNo });
                    case "read":
                        var students = await getAllStudent(schoolId.GetString());
                        return this.Ok(new { students });
                    case "update":
                        //更新學生資料，批量密碼重置，基本資訊更新(姓名、教室ID及座號)
                        var (dicStudent, nonexistentIds, errorIds) = await updateStudents(schoolId.GetString(), request.GetProperty("students").EnumerateArray());
                        var studentClass = await updateClassStudents(schoolId.GetString(), dicStudent);
                        if (errorIds.Count == 0)
                        {
                            return this.Ok(new
                            {
                                students = dicStudent.Select(
                                o => new
                                {
                                    id = o.Key,
                                    o.Value.name,
                                    o.Value.pic,
                                    o.Value.year,
                                    no = studentClass.ContainsKey(o.Key) ? studentClass[o.Key].no : null,
                                    classId = studentClass.ContainsKey(o.Key) ? studentClass[o.Key].classId : null,
                                    periodId = studentClass.ContainsKey(o.Key) ? studentClass[o.Key].periodId : null,
                                    gradeId = studentClass.ContainsKey(o.Key) ? studentClass[o.Key].gradeId : null
                                })
                            });
                        }
                        else
                        {
                            return this.Ok(new
                            {
                                students = dicStudent.Select(
                                o => new
                                {
                                    id = o.Key,
                                    o.Value.name,
                                    o.Value.pic,
                                    o.Value.year,
                                    no = studentClass.ContainsKey(o.Key) ? studentClass[o.Key].no : null,
                                    classId = studentClass.ContainsKey(o.Key) ? studentClass[o.Key].classId : null,
                                    periodId = studentClass.ContainsKey(o.Key) ? studentClass[o.Key].periodId : null,
                                    gradeId = studentClass.ContainsKey(o.Key) ? studentClass[o.Key].gradeId : null
                                }),
                                errorIds = errorIds.ToList()
                            });
                        }
                    case "delete":
                        //刪除學生資料及從教室學生名單內移除該學生
                        var sucDelIds = await deleteStudents(schoolId.GetString(), request.GetProperty("students").EnumerateArray());
                        if (sucDelIds.Count != 0) await removeStudentFromClass(schoolId.GetString(), request.GetProperty("students").EnumerateArray());
                        return this.Ok(new { ids = sucDelIds });
                    default:
                        return BadRequest();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/StudentManage()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return BadRequest();
        }

        /// <summary>
        /// 創建學生帳號，目前SDK4.0預覽版還不支援批量創建(TransactionalBatch)，待SDK正式發行時在優化此代碼。
        /// </summary>
        /// <param name="schoolId">學校簡碼</param>
        /// <param name="students">[{ "id":"","name":"","no":"","pw":"","year":"","classId":"","className":"" }]</param>
        /// <returns></returns>
        private async Task<(
            Dictionary<string, string> classInfo,
            Dictionary<string, List<(string id, string name, string no, string year)>> classStudent,
            List<(string id, string name, string no, string year)> unclassStudent,
            List<string> existId,
            List<string> errorYear)>
            createStudents(string schoolId, JsonElement.ArrayEnumerator students)
        {
            var existId = new List<string>();
            var errorYear = new List<string>();
            var exceptions = new List<Exception>();
            Dictionary<string, List<(string id, string name, string no, string year)>> classStudent
                = new Dictionary<string, List<(string id, string name, string no, string year)>>();
            List<(string id, string name, string no, string year)> unclassStudent = new List<(string id, string name, string no, string year)>();
            Dictionary<string, string> classInfo = new Dictionary<string, string>();
            try
            {
                var container = _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Student");
                while (students.MoveNext())
                {
                    JsonElement current = students.Current;
                    if (current.TryGetProperty("id", out var id))
                    {
                        (string id, string name, string no, string year) studentInfo = (null, null, null, null);

                        if (string.IsNullOrWhiteSpace(id.GetString())) continue;
                        else studentInfo.id = id.GetString();

                        if (current.TryGetProperty("year", out var year))
                        {
                            studentInfo.year = year.GetString();
                            if (string.IsNullOrWhiteSpace(year.GetString()))
                            {
                                errorYear.Add(id.GetString());
                                continue;
                            }
                        }
                        else
                        {
                            errorYear.Add(id.GetString());
                            continue;
                        }
                        if (current.TryGetProperty("no", out var no))
                        {
                            studentInfo.no = no.GetString();
                        }

                        //Password，若沒給則使用學號當密碼
                        string salt = Utils.CreatSaltString(8);
                        string pw = current.TryGetProperty("pw", out var tmpPw)
                                            ? Utils.HashedPassword(tmpPw.GetString(), salt)
                                            : Utils.HashedPassword(id.GetString(), salt);

                        using var stream = new MemoryStream();
                        using var writer = new Utf8JsonWriter(stream);
                        writer.WriteStartObject();
                        if (current.TryGetProperty("name", out var tmpName) && !string.IsNullOrWhiteSpace(tmpName.GetString()))
                        {
                            writer.WriteString("name", tmpName.GetString());
                            studentInfo.name = tmpName.GetString();
                        }
                        else writer.WriteNull("name");
                        if (current.TryGetProperty("gender", out var tmpGender) && !string.IsNullOrWhiteSpace(tmpGender.GetString()))
                        {
                            writer.WriteString("gender", tmpGender.GetString());
                        }
                        else writer.WriteNull("gender");
                        writer.WriteString("pk", $"Base");
                        writer.WriteString("code", $"Base-{schoolId}");
                        writer.WriteString("id", id.GetString());
                        writer.WriteString("schoolId", schoolId);
                        writer.WriteString("salt", salt);
                        writer.WriteString("pw", pw);
                        writer.WriteString("year", year.GetString());
                        writer.WriteNull("picture");
                        writer.WriteNull("mail");
                        writer.WriteNull("mobile");
                        writer.WriteNull("country");
                        writer.WriteEndObject();
                        writer.Flush();
                        try
                        {
                            //將資料存入CosmosDB內
                            var response = await container.CreateItemStreamAsync(stream, new PartitionKey($"Base-{schoolId}"));
                            if (response.Status == (int)HttpStatusCode.Conflict)
                            {
                                existId.Add(id.GetString());
                                continue;
                            }

                            //取得有給教室ID的學生
                            if (current.TryGetProperty("classId", out var tmpClassId) && !string.IsNullOrWhiteSpace(tmpClassId.GetString()))
                            {
                                string classId = tmpClassId.GetString();
                                //歸類學生所屬的教室
                                if (!string.IsNullOrWhiteSpace(classId) && classStudent.ContainsKey(classId))
                                {
                                    if (classStudent.TryGetValue(classId, out var keyValues))
                                    {
                                        keyValues.Add(studentInfo);
                                    }
                                }
                                else
                                {
                                    classStudent.Add(classId, new List<(string id, string name, string no, string year)>() { studentInfo });

                                    //將教室id 教室name記錄到另一個字典內
                                    string strClassName = null;
                                    if (current.TryGetProperty("className", out var className))
                                    {
                                        strClassName = className.GetString();
                                    }
                                    classInfo.Add(classId, strClassName);
                                }
                            }
                            else
                            {
                                unclassStudent.Add(studentInfo);
                            }
                        }
                        catch (CosmosException ex)
                        {
                            if (ex.Status == (int)HttpStatusCode.Conflict) existId.Add(id.GetString());
                            else exceptions.Add(ex);
                        }
                        catch (Exception ex)
                        {
                            exceptions.Add(ex);
                        }
                    }
                    else
                    {
                        //沒給ID，無法處理
                    }
                }
                if (exceptions.Count == 0) return (classInfo, classStudent, unclassStudent, existId, errorYear);
                else if (exceptions.Count > 1) throw new AggregateException(exceptions);
                else if (exceptions.Count == 1) throw exceptions.Single();
            }
            catch (AggregateException ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/createStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/createStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return (classInfo, classStudent, unclassStudent, existId, errorYear);
        }

        /// <summary>
        /// 生成Class資料
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="classId"></param>
        /// <param name="className"></param>
        /// <param name="students"></param>
        /// <returns></returns>
        private async Task<Dictionary<string, (string periodId, string gradeId, List<string> id)>> createClassInfo(string schoolId, string classId, string className, List<(string id, string name, string no, string year)> students)
        {
            //組Class JSON
            try
            {
                List<string> ids = new List<string>();

                using var memoryStream = new MemoryStream();
                using var writer = new Utf8JsonWriter(memoryStream);
                writer.WriteStartObject();
                writer.WriteString("pk", "Classroom");
                writer.WriteString("code", $"Class-{schoolId}");
                writer.WriteString("id", classId);
                writer.WriteNull("x");
                writer.WriteNull("y");
                writer.WritePropertyName("students");
                writer.WriteStartArray();

                //寫入學生資料，若id為空則不寫入
                foreach (var (id, name, no, year) in students)
                {
                    if (string.IsNullOrWhiteSpace(id)) continue;
                    writer.WriteStartObject();
                    writer.WriteString("id", id);
                    if (string.IsNullOrWhiteSpace(name)) writer.WriteNull("name");
                    else writer.WriteString("name", name);
                    if (string.IsNullOrWhiteSpace(no)) writer.WriteNull("no");
                    else writer.WriteString("no", no);
                    writer.WriteNull("groupId");
                    writer.WriteNull("groupName");
                    writer.WriteEndObject();
                    ids.Add(id);
                }

                writer.WriteEndArray();

                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();
                writer.WriteNull("gradeId");
                writer.WriteNull("periodId");
                writer.WriteNull("sn");
                writer.WriteNull("style");
                writer.WriteNull("timetable");
                writer.WriteString("scope", "school");//school单词拼写错误修正
                writer.WriteNull("status");
                writer.WriteEndObject();
                writer.Flush();

                var ret = await _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "School").CreateItemStreamAsync(memoryStream, new PartitionKey($"Class-{schoolId}"));
                if (ret.Status != (int)HttpStatusCode.Created)
                {
                    await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/createClassInfo()\nStatus:{ret.Status}\nSchoolId:{schoolId},ClassId:{classId}", GroupNames.醍摩豆服務運維群組);
                }
                Dictionary<string, (string periodId, string gradeId, List<string> id)> retClassStud = new Dictionary<string, (string periodId, string gradeId, List<string> id)>
                {
                    { classId, (null, null, ids) }
                };
                return retClassStud;
            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/createClassInfo()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/createClassInfo()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return null;
        }

        /// <summary>
        /// 使用學校代碼查詢該校所有學生，並且在查詢該學生所屬的教室及座號，支援offset和limit操作已及ContinuationToken，若有ContinuationToken，則會優先使用ContinuationToken。
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="byNameOrId">透過Name或Id來查，所以不會管學制、學級和教室</param>
        /// <param name="byPeriod"></param>
        /// <param name="byGrade"></param>
        /// <param name="byClassId"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="token"></param>
        /// <returns></returns>
        private async Task<(List<object> students, string continuationToken)> getStudents(string schoolId, string byNameOrId = null, string byPeriod = null, string byGrade = null, string byClassId = null, int offset = -1, int limit = -1, string token = default)
        {
            try
            {   //TODO : 進階查詢選項調整 
                //以學校學生角度去抓資料
                List<(string id, string name, string pic, string year)> listStudent = new List<(string id, string name, string pic, string year)>();
                string queryText = $"SELECT c.id, c.name, c.picture, c.year FROM c WHERE c.code = 'Base-{schoolId}'";

                //如果有選擇ClassId的話，則先取得該教室內的學生。
                List<string> searchId = new List<string>();
                if (!string.IsNullOrWhiteSpace(byClassId))
                {
                    var classInfos = await getClassInfoUseId(schoolId, new List<string>() { 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}'"))})";
                    }
                }

                //if (!string.IsNullOrWhiteSpace(byYear))
                //{
                //    queryText = $"{queryText} AND c.year = '{byYear}'";
                //}

                //檢查是否有接續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("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").GetString()));
                        }
                    }
                    //單筆查詢上限為100條，所以查完一次即返回，並且給接續token。
                    break;
                }

                //查學生所屬的教室及座號
                List<object> ret = new List<object>();
                //查教室資訊，使用上面的學生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("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.pic,
                                            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.pic,
                                            o.year,
                                            no = (string)null,
                                            gradeId = (string)null,
                                            className = (string)null
                                        });
                ret.AddRange(notJoinClassStuds);
                return (ret, continuationToken);
            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/getStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/getStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return (null, null);
        }

        /// <summary>
        /// 取得該學校的所有學生。
        /// </summary>
        /// <param name="schoolId"></param>
        /// <returns></returns>
        private async Task<List<object>> getAllStudent(string schoolId)
        {
            try
            {   //TODO : 進階查詢選項調整 
                //以學校學生角度去抓資料
                List<(string id, string name, string pic, string year)> listStudent = new List<(string id, string name, string pic, string year)>();
                string queryText = $"SELECT c.id, c.name, c.picture, c.year FROM c WHERE c.code = 'Base-{schoolId}'";

                //回傳用ContinuationToken
                string continuationToken = string.Empty;

                //進行學生資料的查詢 TEAMModelOS-Student
                await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer("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)
                    {
                        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").GetString()));
                        }
                    }
                }

                //查學生所屬的教室及座號
                List<object> ret = new List<object>();
                //查教室資訊，使用上面的學生id並透過子查詢查詢。
                queryText = $"SELECT c.id, c.students, c.gradeId , c.periodId FROM c WHERE c.code = 'Class-{schoolId}'";
                await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer("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();
                                string no = stud.GetProperty("no").GetString();
                                string classId = null, gradeId = null, periodId = null;
                                if (classroom.TryGetProperty("id", out var tmpClassId)
                                    && !string.IsNullOrWhiteSpace(tmpClassId.GetString()))
                                {
                                    classId = tmpClassId.GetString();
                                }
                                if (classroom.TryGetProperty("gradeId", out var tmpGrageId)
                                    && !string.IsNullOrWhiteSpace(tmpGrageId.GetString()))
                                {
                                    gradeId = tmpGrageId.GetString();
                                }

                                if (classroom.TryGetProperty("periodId", out var tmpPeriodId)
                                   && !string.IsNullOrWhiteSpace(tmpPeriodId.GetString()))
                                {
                                    periodId = tmpPeriodId.GetString();
                                }
                                //整理出前端所需的資訊
                                var tmp = listStudent
                                    .Where(o => o.id.Equals(id, StringComparison.Ordinal))
                                    .Select(o =>
                                        new
                                        {
                                            o.id,
                                            o.name,
                                            o.pic,
                                            o.year,
                                            no = stud.GetProperty("no").GetString(),
                                            gradeId,
                                            classId,
                                            periodId
                                        });
                                ret.AddRange(tmp);
                                //刪除已整理完的ID
                                listStudent.RemoveAll(o => o.id.Equals(id, StringComparison.Ordinal));
                            }
                        }
                    }
                }
                var notJoinClassStuds = listStudent.Select(o =>
                                        new
                                        {
                                            o.id,
                                            o.name,
                                            o.pic,
                                            o.year,
                                            no = (string)null,
                                            gradeId = (string)null,
                                            classId = (string)null,
                                            periodId = (string)null
                                        });
                ret.AddRange(notJoinClassStuds);
                return ret;
            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/getStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/getStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return null;
        }

        /// <summary>
        /// 使用學生ID來查詢所屬的教室
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="ids"></param>
        /// <returns></returns>
        private async Task<List<JsonElement>> getClassInfoUseStudent(string schoolId, List<string> ids)
        {
            //string schoolId = "hbcn";
            //List<string> ids = new List<string>() { "20191501" };
            try
            {
                List<string> existId = new List<string>();
                List<JsonElement> listClass = new List<JsonElement>();

                string queryText = $"SELECT DISTINCT VALUE c FROM c JOIN (SELECT VALUE t FROM t IN c.students WHERE t.id IN ({string.Join(",", ids.Select(o => $"'{o}'"))}))";
                List<object> listStudent = new List<object>();

                await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer("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 Documents = json.RootElement.GetProperty("Documents").EnumerateArray();
                        while (Documents.MoveNext())
                        {
                            listClass.Add(Documents.Current.Clone());
                        }
                    }
                }
                return listClass;
            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/getClassInfoUseStudent()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/getClassInfoUseStudent()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return null;
        }

        /// <summary>
        /// 取得該校所有教室內的名單
        /// </summary>
        /// <param name="schoolId"></param>
        /// <returns></returns>
        public async Task<Dictionary<string, JsonElement>> getClassStudentAsync(string schoolId, string classId = null)
        {
            try
            {
                string queryText = $"SELECT c.id, c.students FROM c WHERE c = c";
                //if (!string.IsNullOrWhiteSpace(classId)) queryText += $" AND c.id = '{classId}'";
                Dictionary<string, JsonElement> listStudent = new Dictionary<string, JsonElement>();

                await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer("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($"OS，{_option.Location}，Student/getClassStudentAsync()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return null;
        }

        /// <summary>
        /// 刪除學生
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="students"></param>
        /// <returns></returns>
        private async Task<List<string>> deleteStudents(string schoolId, JsonElement.ArrayEnumerator students)
        {
            List<string> sucIds = new List<string>();
            try
            {
                var exceptions = new List<Exception>();

                var container = _azureCosmos.GetCosmosClient().GetContainer("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);
                    }
                    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($"OS，{_option.Location}，Student/deleteStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/deleteStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return sucIds;
        }

        /// <summary>
        /// 將學生從教室內移除
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="students"></param>
        private async Task removeStudentFromClass(string schoolId, JsonElement.ArrayEnumerator students)
        {
            try
            {
                Dictionary<string, List<string>> classStudent = new Dictionary<string, List<string>>();
                List<string> studs = new List<string>();
                //整理教室學生資訊
                while (students.MoveNext())
                {
                    JsonElement student = students.Current;
                    string id, classId = string.Empty;
                    if (student.TryGetProperty("id", out var tmpId) && !string.IsNullOrWhiteSpace(tmpId.GetString()))
                    {
                        id = tmpId.GetString();
                        studs.Add(id);
                    }
                    else continue;

                    if (student.TryGetProperty("classId", out var tmpClassId) && !string.IsNullOrWhiteSpace(tmpClassId.GetString()))
                    {
                        classId = tmpClassId.GetString();
                        if (!string.IsNullOrWhiteSpace(classId) && classStudent.ContainsKey(classId)) classStudent[classId].Add(id);
                        else classStudent.Add(classId, new List<string>() { id });
                    }
                }

                if (studs.Count != 0)
                {
                    //使用子查詢來查詢students欄位裡面是否有相符的學生
                    var queryText = $"SELECT VALUE c FROM c JOIN (SELECT VALUE t FROM t IN c.students WHERE t.id IN ({string.Join(",", studs.Select(o => $"'{o}'"))}))";
                    await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer("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 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 memoryStream = new MemoryStream();
                                using var writer = new Utf8JsonWriter(memoryStream);
                                writer.WriteStartObject();
                                foreach (var element in doc.EnumerateObject())
                                {
                                    if (element.Name.Equals("students", StringComparison.Ordinal))
                                    {
                                        writer.WritePropertyName("students");
                                        writer.WriteStartArray();
                                        var s = element.Value.EnumerateArray();
                                        while (s.MoveNext())
                                        {
                                            JsonElement stud = s.Current;
                                            string sutdId = stud.GetProperty("id").GetString();
                                            if (!studs.Contains(sutdId))
                                            {
                                                string name = stud.GetProperty("name").GetString();
                                                string no = stud.GetProperty("no").GetString();

                                                writer.WriteStartObject();
                                                writer.WriteString("id", sutdId);
                                                if (string.IsNullOrWhiteSpace(name)) writer.WriteNull("name");
                                                else writer.WriteString("name", name);
                                                if (string.IsNullOrWhiteSpace(no)) writer.WriteNull("no");
                                                else writer.WriteString("no", no);
                                                writer.WriteEndObject();
                                            }
                                        }
                                        writer.WriteEndArray();
                                    }
                                    else
                                    {
                                        element.WriteTo(writer);
                                    }
                                }
                                writer.WriteEndObject();
                                writer.Flush();

                                var ret = await _azureCosmos
                                    .GetCosmosClient()
                                    .GetContainer("TEAMModelOS", "School")
                                    .ReplaceItemStreamAsync(memoryStream, id, new PartitionKey($"Class-{schoolId}"));
                                if (ret.Status != (int)HttpStatusCode.OK)
                                {
                                    await _dingDing.SendBotMsg(
                                        $"OS，{_option.Location}，Student/removeStudentFromClass()\nClass-{schoolId}，{string.Join(",", studs.Select(o => $"'{o}'"))}",
                                        GroupNames.醍摩豆服務運維群組);
                                }
                            }
                        }
                    }
                }
            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/removeStudentFromClass()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/removeStudentFromClass()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
        }

        //TODO:檢查學號是否重複，及處理存到CosmosDB錯誤的處理
        /// <summary>
        /// 將學生加入教室，並且會比對id及座號是否有重複
        /// </summary>
        /// <returns></returns>
        private async Task<(Dictionary<string, (string periodId, string gradeId, List<string> id)> ret, List<string> existId, List<string> existNo)> joinClass(string schoolId, Dictionary<string, List<(string id, string name, string no)>> classStudents)
        {
            try
            {
                List<string> existId = new List<string>();
                List<string> existNo = new List<string>();
                Dictionary<string, (string periodId, string gradeId, List<string> id)> retClassStud = new Dictionary<string, (string periodId, string gradeId, List<string> id)>();

                foreach (var importItem in classStudents)
                {
                    List<(string id, string name, string no, string groupId, string groupName)> retStudents
                        = new List<(string id, string name, string no, string groupId, string groupName)>();
                    retStudents.AddRange(importItem.Value.Select(o => (o.id, o.name, o.no, (string)null, (string)null)).ToList());

                    //檢查輸入資料的id及no是否有重複
                    var duplicateId = retStudents.GroupBy(o => o.id).Where(o => o.Count() > 1).Select(o => o.Key).ToList();
                    foreach (var id in duplicateId)
                    {
                        retStudents.RemoveAll(o => o.id.Equals(id));
                        existId.Add(id);
                    }

                    var duplicateNo = retStudents.GroupBy(o => o.no).Where(o => o.Count() > 1).Select(o => o.Key).ToList();
                    foreach (var no in duplicateNo)
                    {
                        retStudents.RemoveAll(o => o.no.Equals(no));
                        existNo.Add(no);
                    }

                    string classId = string.Empty, periodId = string.Empty, gradeId = string.Empty;

                    //查該教室的學生
                    using var response = await _azureCosmos
                        .GetCosmosClient()
                        .GetContainer("TEAMModelOS", "School")
                        .ReadItemStreamAsync(importItem.Key, new PartitionKey($"Class-{schoolId}"));
                    {
                        //組出新的JSON
                        using var memoryStream = new MemoryStream();
                        using var writer = new Utf8JsonWriter(memoryStream);
                        writer.WriteStartObject();

                        //確認該教室資訊存不存在，不存在則額外創建
                        if (response.Status == 200)
                        {
                            using Stream stream = response.ContentStream;
                            var existClassData = await JsonDocument.ParseAsync(stream);

                            existClassData.RootElement.TryGetProperty("id", out var tmpId);
                            classId = tmpId.GetString();
                            existClassData.RootElement.TryGetProperty("periodId", out var tmpPeriodId);
                            periodId = tmpPeriodId.GetString();
                            existClassData.RootElement.TryGetProperty("gradeId", out var tmpGradeId);
                            gradeId = tmpGradeId.GetString();

                            //取出目前該教室的學生清單，若存在則檢查匯入的學生是否已存在，反之則創建新的教室資訊
                            existClassData.RootElement.TryGetProperty("students", out var existStudents);

                            if (existStudents.GetArrayLength() != 0)
                            {
                                //若學生數量不為0，代表裡面已有資料，故要檢查是否有重複
                                List<(string id, string name, string no, string groupId, string groupName)> tmpStuds
                                    = new List<(string id, string name, string no, string groupId, string groupName)>();
                                //取得雲端教室的名單，並檢查id及座號是否重複
                                var studs = existStudents.EnumerateArray();

                                //將雲端的學生名單記錄到tmp內
                                while (studs.MoveNext())
                                {
                                    JsonElement stud = studs.Current;
                                    string id = stud.GetProperty("id").GetString();
                                    string no = stud.GetProperty("no").GetString();
                                    string name = stud.GetProperty("name").GetString();
                                    string groupId = stud.GetProperty("groupId").GetString();
                                    string groupName = stud.GetProperty("groupName").GetString();

                                    tmpStuds.Add((id, name, no, groupId, groupName));
                                }

                                //先檢查ID及座號是否重複，重複的則記錄在duplicate內

                                //var duplicate = importItem.Value.Where(
                                //   i => tmpStuds.Exists(t => i.no.Contains(t.no))
                                //   || tmpStuds.Exists(t => i.id.Contains(t.id))).ToList();
                                //var duplicate = importItem.Value.Where(
                                //    a => tmpStuds.Exists(t => a.no.Contains(t.no))
                                //    || tmpStuds.Exists(t => a.id.Contains(t.id))).ToList();
                                var duplicate = retStudents.Where(
                                    a => tmpStuds.Any(p => (p.id == a.id) || (p.no == a.no))).Select(o => o);

                                foreach (var item in duplicate)
                                {
                                    retStudents.RemoveAll(o => o.id.Equals(item.id) || o.no.Equals(item.no));
                                }

                                //將現有資料加回去
                                retStudents.AddRange(tmpStuds);
                            }

                            foreach (var testDataElement in existClassData.RootElement.EnumerateObject())
                            {
                                if (!testDataElement.Name.Equals("students", StringComparison.Ordinal)) testDataElement.WriteTo(writer);
                            }
                        }
                        else if (response.Status == 404)
                        {
                            writer.WriteString("pk", "Classroom");
                            writer.WriteString("code", $"Class-{schoolId}");
                            writer.WriteString("id", importItem.Key);
                            writer.WriteNull("x");
                            writer.WriteNull("y");
                            writer.WritePropertyName("teacher");
                            writer.WriteStartObject();
                            writer.WriteNull("id");
                            writer.WriteNull("name");
                            writer.WriteEndObject();
                            writer.WriteNull("gradeId");
                            writer.WriteNull("sn");
                            writer.WriteNull("style");
                            writer.WriteNull("timetable");
                            writer.WriteString("scope", "shcool");
                            writer.WriteNull("status");
                        }
                        else
                        {
                            //response不為200及404，代表該次查詢有問題。
                            continue;
                        }

                        writer.WritePropertyName("students");
                        writer.WriteStartArray();

                        //進行no排序
                        retStudents = retStudents.OrderBy(o => string.IsNullOrWhiteSpace(o.no)).ThenBy(o => string.IsNullOrEmpty(o.no) ? 0 : int.Parse(o.no)).ToList();
                        foreach (var item in retStudents)
                        {
                            writer.WriteStartObject();
                            writer.WriteString("id", item.id);
                            if (string.IsNullOrWhiteSpace(item.name)) writer.WriteNull("name");
                            else writer.WriteString("name", item.name);
                            if (string.IsNullOrWhiteSpace(item.no)) writer.WriteNull("no");
                            else writer.WriteString("no", item.no);
                            if (string.IsNullOrWhiteSpace(item.groupId)) writer.WriteNull("groupId");
                            else writer.WriteString("groupId", item.groupId);
                            if (string.IsNullOrWhiteSpace(item.groupName)) writer.WriteNull("groupName");
                            else writer.WriteString("groupName", item.groupName);
                            writer.WriteEndObject();
                        }

                        writer.WriteEndArray();

                        writer.WriteEndObject();
                        writer.Flush();

                        var ret = await _azureCosmos
                                        .GetCosmosClient()
                                        .GetContainer("TEAMModelOS", "School")
                                        .UpsertItemStreamAsync(memoryStream, new PartitionKey($"Class-{schoolId}"));

                        retClassStud.Add(classId, (periodId, gradeId, retStudents.Select(o => o.id).ToList()));
                    }
                }
                return (retClassStud, existId, existNo);
            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/joinClass()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/joinClass()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return (null, null, null);
        }

        /// <summary>
        /// 取得教室資訊
        /// </summary>
        /// <returns></returns>
        private async Task<Dictionary<string, JsonElement>> getClassInfoUseId(string schoolId, List<string> 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<string, JsonElement> dicClassInfo = new Dictionary<string, JsonElement>();

                    await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer("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($"OS，{_option.Location}，Student/getClassInfoUseId()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/getClassInfoUseId()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return null;
        }

        /// <summary>
        /// 批量更新學生資訊，目前支持更新姓名、密碼。
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="students"></param>
        /// <returns></returns>
        private async Task<(Dictionary<string, (string name, string year, string pic, string gender, string mail, string mobile, string classId, string no)> students, List<string> nonexistentIds, List<string> errorIds)> updateStudents(string schoolId, JsonElement.ArrayEnumerator students)
        {
            try
            {
                //Key:id Value:學生基本資訊
                var studentsInfos
                    = new Dictionary<string, (string salt, string pw, string name, string year, string pic, string gender, string mail, string mobile, string classId, string no)>();
                var nonexistentIds = new List<string>();
                var errorIds = new List<string>();

                //整理輸入資料
                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 = string.Empty, pw = string.Empty, name = string.Empty, year = string.Empty, gender = string.Empty, mail = string.Empty, mobile = string.Empty, classId = string.Empty, no = string.Empty;
                            //有給pw欄位才進行處理
                            if (student.TryGetProperty("pw", out var tmpPw))
                            {
                                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.GetString();
                            }
                            if (student.TryGetProperty("classId", out var tmpclassId))
                            {
                                classId = tmpclassId.GetString();
                            }
                            if (student.TryGetProperty("no", out var tmpNo))
                            {
                                no = tmpNo.GetString();
                            }
                            if (!studentsInfos.ContainsKey(id.GetString()))
                            {
                                //pic,mail,mobile暫不支持更新
                                studentsInfos.Add(id.GetString(), (salt, pw, name, year, null, gender, null, null, classId, no));
                                nonexistentIds.Add(id.GetString());
                            }
                        }
                    }
                }

                //將資料更新到資料庫
                if (studentsInfos.Count != 0)
                {
                    CosmosContainer cosmosContainer = _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Student");
                    //查學生的基本資料
                    string queryText = $"SELECT * FROM c WHERE c.id IN ({string.Join(",", studentsInfos.Select(o => $"'{o.Key}'"))})";

                    List<JsonElement> listStudent = new List<JsonElement>();

                    await foreach (Response item in cosmosContainer
                        .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 accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                            while (accounts.MoveNext())
                            {
                                JsonElement account = accounts.Current;
                                string id = account.GetProperty("id").GetString();

                                (string salt, string pw, string name, string year, string pic, string gender, string mail, string mobile, string classId, string no) oldData;
                                oldData = (studentsInfos[id].salt, studentsInfos[id].pw, studentsInfos[id].name, studentsInfos[id].year, studentsInfos[id].pic, studentsInfos[id].gender, studentsInfos[id].mail, studentsInfos[id].mobile, studentsInfos[id].classId, studentsInfos[id].no);
                                bool upPwDone = false;
                                using var memoryStream = new MemoryStream();
                                using var writer = new Utf8JsonWriter(memoryStream);
                                writer.WriteStartObject();
                                foreach (var element in account.EnumerateObject())
                                {
                                    switch (true)
                                    {
                                        case bool _ when element.Name.Equals("name", StringComparison.Ordinal):
                                            if (string.IsNullOrWhiteSpace(studentsInfos[id].name))
                                            {
                                                element.WriteTo(writer);
                                                oldData.name = element.Value.GetString();
                                            }
                                            else
                                            {
                                                writer.WriteString("name", studentsInfos[id].name);
                                            }
                                            break;
                                        case bool _ when element.Name.Equals("pw", StringComparison.Ordinal):
                                        case bool _ when element.Name.Equals("salt", StringComparison.Ordinal):
                                            if (!upPwDone && !string.IsNullOrWhiteSpace(studentsInfos[id].salt) && !string.IsNullOrWhiteSpace(studentsInfos[id].pw))
                                            {
                                                writer.WriteString("salt", studentsInfos[id].salt);
                                                writer.WriteString("pw", studentsInfos[id].pw);
                                                upPwDone = true;
                                            }
                                            break;
                                        case bool _ when element.Name.Equals("gender", StringComparison.Ordinal):
                                            if (string.IsNullOrWhiteSpace(studentsInfos[id].gender))
                                            {
                                                element.WriteTo(writer);
                                                oldData.gender = element.Value.GetString();
                                            }
                                            else
                                            {
                                                writer.WriteString("gender", studentsInfos[id].gender);
                                            }
                                            break;
                                        case bool _ when element.Name.Equals("year", StringComparison.Ordinal):
                                            if (string.IsNullOrWhiteSpace(studentsInfos[id].year))
                                            {
                                                element.WriteTo(writer);
                                                oldData.year = element.Value.GetString();
                                            }
                                            else
                                            {
                                                writer.WriteString("year", studentsInfos[id].year);
                                            }
                                            break;
                                        case bool _ when element.Name.StartsWith("_", StringComparison.Ordinal):
                                            break;
                                        default:
                                            element.WriteTo(writer);
                                            break;
                                    }
                                }
                                //若密碼和鹽沒有更新，就把舊的資料寫回去
                                if (!upPwDone)
                                {
                                    writer.WriteString("salt", account.GetProperty("salt").GetString());
                                    writer.WriteString("pw", account.GetProperty("pw").GetString());
                                }
                                writer.WriteEndObject();
                                writer.Flush();
                                studentsInfos[id] = oldData;

                                try
                                {
                                    var ret = await cosmosContainer.ReplaceItemStreamAsync(memoryStream, id, new PartitionKey($"Base-{schoolId}"));
                                    //將更新完的id從字典內移除，保留沒查到的。
                                    if (ret.Status == (int)HttpStatusCode.OK) nonexistentIds.Remove(id);
                                    else errorIds.Add(id);
                                }
                                catch (CosmosException ex)
                                {
                                    await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/updateStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
                                    errorIds.Add(id);
                                }
                                catch (Exception ex)
                                {
                                    await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/updateStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
                                    errorIds.Add(id);
                                }
                            }
                        }
                        //將輸入不存在的資料移除。
                        nonexistentIds.ForEach(o => studentsInfos.Remove(o));
                    }
                    //整理輸出數據
                    var retStudentsInfo = new Dictionary<string, (string name, string year, string pic, string gender, string mail, string mobile, string classId, string no)>();
                    foreach (var item in studentsInfos)
                    {
                        retStudentsInfo.Add(item.Key, (item.Value.name, item.Value.year, item.Value.pic, item.Value.gender, item.Value.mail, item.Value.mobile, item.Value.classId, item.Value.no));
                    }
                    return (retStudentsInfo, nonexistentIds, errorIds);
                }
            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/updateStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/updateStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return (null, null, null);
        }

        /// <summary>
        /// 批量更新學生所屬教室及學號
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="students"></param>
        /// <returns></returns>
        private async Task<Dictionary<string, (string classId, string periodId, string gradeId, string name, string no)>> updateClassStudents(string schoolId, Dictionary<string, (string name, string year, string pic, string gender, string mail, string mobile, string classId, string no)> students)
        {
            try
            {
                //最後輸出給前端用的資料
                var retStudentsClassInfo = new Dictionary<string, (string classId, string periodId, string gradeId, string name, string no)>();
                /**
                整理輸入的資料
                var studentsClassInfo = new Dictionary<string, (string classId, string name, string no)>();
                while (students.MoveNext())
                {
                    JsonElement student = students.Current;
                    if (student.TryGetProperty("id", out var tmpId))
                    {
                        if (!string.IsNullOrWhiteSpace(tmpId.GetString()))
                        {
                            string classId = string.Empty, className = string.Empty, no = string.Empty, name = string.Empty;

                            if (student.TryGetProperty("name", out var tmpName)) name = tmpName.GetString();
                            if (student.TryGetProperty("classId", out var tmpClassId)) classId = tmpClassId.GetString();
                            if (student.TryGetProperty("no", out var tmpNo)) no = tmpNo.GetString();

                            studentsClassInfo.Add(tmpId.GetString(), (classId, name, no));
                        }
                    }
                }
                */
                if (students.Count == 0) return retStudentsClassInfo;

                var tmpStuds = new Dictionary<string, (string name, string year, string pic, string gender, string mail, string mobile, string classId, string no)>(students);

                //透過id查找已加入的教室
                var classInfos = await getClassInfoUseStudent(schoolId, students.Select(o => o.Key).ToList());

                //如果有查到，代表該學生已經加入過某間教室(Class)
                if (classInfos.Count != 0)
                {
                    foreach (var classInfo in classInfos)
                    {
                        //教室資訊的id
                        if (classInfo.TryGetProperty("id", out var tmpClassId))
                        {
                            string classId = tmpClassId.GetString();
                            string periodId = string.Empty, gradeId = string.Empty;

                            if (classInfo.TryGetProperty("periodId", out var tmpPeriodId)) periodId = tmpPeriodId.GetString();
                            if (classInfo.TryGetProperty("gradeId", out var tmpGradeId)) gradeId = tmpGradeId.GetString();
                            
                            //用以紀錄教室內已存在的學生
                            List<(string id, string name, string no, string groupId, string groupName)> existStudents 
                                = new List<(string id, string name, string no, string groupId, string groupName)>();
                            var Documents = classInfo.GetProperty("students").EnumerateArray();
                            while (Documents.MoveNext())
                            {
                                JsonElement Document = Documents.Current;
                                string studId = null, no = null, name = null, groupId = null, groupName = null;

                                if (Document.TryGetProperty("id", out var tmpId)) studId = tmpId.GetString();
                                if (Document.TryGetProperty("name", out var tmpName)) name = tmpName.GetString();
                                if (Document.TryGetProperty("no", out var tmpNo)) no = tmpNo.GetString();
                                if (Document.TryGetProperty("groupId", out var tmpgroupId)) groupId = tmpgroupId.GetString();
                                if (Document.TryGetProperty("groupName", out var tmpgroupName)) groupName = tmpgroupName.GetString();

                                //檢查輸入資料內學生要加入的教室是否一致
                                if (students.ContainsKey(studId))
                                {
                                    if (students[studId].classId.Equals(classId, StringComparison.Ordinal))
                                    {
                                        //座號及姓名檢查，如果現有資料與欲更新資料不同則進行更新
                                        if (
                                            !(students[studId].no.Equals(no, StringComparison.Ordinal)
                                            && students[studId].name.Equals(name, StringComparison.Ordinal))
                                            )
                                        {
                                            name = students[studId].name;
                                            no = students[studId].no;
                                            //retStudentsClassInfo.Add(studId, (classId, periodId, gradeId, name, no));
                                        }
                                        tmpStuds.Remove(studId);
                                    }
                                    else
                                    {
                                        //retStudentsClassInfo.Add(studId, (students[studId].classId, null, null, name, no));
                                        tmpStuds[studId] = (tmpStuds[studId].name, tmpStuds[studId].year, tmpStuds[studId].pic, tmpStuds[studId].gender, tmpStuds[studId].mail, tmpStuds[studId].mobile, tmpStuds[studId].classId, no);
                                        continue;
                                    }
                                }
                                retStudentsClassInfo.Add(studId, (classId, periodId, gradeId, name, no));
                                existStudents.Add((studId, name, no, groupId, groupName));
                            }
                            existStudents = existStudents.OrderBy(o => string.IsNullOrWhiteSpace(o.no)).ThenBy(o => string.IsNullOrEmpty(o.no) ? 0 : int.Parse(o.no)).ToList();
                            
                            using var memoryStream = new MemoryStream();
                            using var writer = new Utf8JsonWriter(memoryStream);

                            writer.WriteStartObject();
                            //將非students的欄位資料寫回去
                            foreach (var element in classInfo.EnumerateObject())
                            {
                                if (!(element.Name.Equals("students", StringComparison.Ordinal) || element.Name.StartsWith("_")))
                                {
                                    element.WriteTo(writer);
                                }
                            }

                            writer.WritePropertyName("students");
                            writer.WriteStartArray();

                            foreach (var item in existStudents)
                            {
                                writer.WriteStartObject();
                                writer.WriteString("id", item.id);
                                if (string.IsNullOrWhiteSpace(item.name)) writer.WriteNull("name");
                                else writer.WriteString("name", item.name);
                                if (string.IsNullOrWhiteSpace(item.no)) writer.WriteNull("no");
                                else writer.WriteString("no", item.no);
                                if (string.IsNullOrWhiteSpace(item.groupId)) writer.WriteNull("groupId");
                                else writer.WriteString("groupId", item.groupId);
                                if (string.IsNullOrWhiteSpace(item.groupName)) writer.WriteNull("groupName");
                                else writer.WriteString("groupName", item.groupName);
                                writer.WriteEndObject();
                            }

                            writer.WriteEndArray();
                            writer.WriteEndObject();
                            writer.Flush();

                            /**
                            //while (Documents.MoveNext())
                            //{
                            //    JsonElement Document = Documents.Current;
                            //    string studId = string.Empty, no = string.Empty, name = string.Empty;

                            //    if (Document.TryGetProperty("name", out var tmpName)) name = tmpName.GetString();
                            //    if (Document.TryGetProperty("id", out var tmpId)) studId = tmpId.GetString();
                            //    if (Document.TryGetProperty("no", out var tmpNo)) no = tmpNo.GetString();

                            //    //檢查輸入資料內學生要加入的教室是否一致
                            //    if (students.ContainsKey(studId))
                            //    {
                            //        //如果是相同的教室id
                            //        if (students[studId].classId.Equals(classId, StringComparison.Ordinal))
                            //        {
                            //            retStudentsClassInfo.Add(studId, (classId, periodId, gradeId, name, no));

                            //            //座號及姓名檢查，如果不相同則進行更新
                            //            if (
                            //                students[studId].no.Equals(no, StringComparison.Ordinal)
                            //                && students[studId].name.Equals(name, StringComparison.Ordinal)
                            //                )
                            //            {
                            //                writer.WriteStartObject();
                            //                writer.WriteString("id", studId);
                            //                if (string.IsNullOrWhiteSpace(name)) writer.WriteNull("name");
                            //                else writer.WriteString("name", name);
                            //                if (string.IsNullOrWhiteSpace(no)) writer.WriteNull("no");
                            //                else writer.WriteString("no", no);
                            //                writer.WriteEndObject();
                            //            }
                            //            else
                            //            {
                            //                writer.WriteStartObject();
                            //                writer.WriteString("id", studId);
                            //                if (string.IsNullOrWhiteSpace(students[studId].name)) writer.WriteNull("name");
                            //                else writer.WriteString("name", students[studId].name);
                            //                if (string.IsNullOrWhiteSpace(students[studId].no)) writer.WriteNull("no");
                            //                else writer.WriteString("no", students[studId].no);
                            //                writer.WriteEndObject();
                            //                //更新輸出結果的資料
                            //                retStudentsClassInfo[studId] = (classId, periodId, gradeId, students[studId].name, students[studId].no);
                            //            }
                            //            //將已處理好的學生從字典裡移除
                            //            students.Remove(studId);
                            //        }
                            //        //不是相同教室id，則要移除該學生的資訊，不寫入
                            //        else continue;
                            //    }
                            //    //寫入原本已存在的學生資訊
                            //    else
                            //    {
                            //        writer.WriteStartObject();
                            //        writer.WriteString("id", studId);
                            //        if (string.IsNullOrWhiteSpace(name)) writer.WriteNull("name");
                            //        else writer.WriteString("name", name);
                            //        if (string.IsNullOrWhiteSpace(no)) writer.WriteNull("no");
                            //        else writer.WriteString("no", no);
                            //        writer.WriteEndObject();
                            //    }
                            //}
                            //writer.WriteEndArray();
                            //writer.WriteEndObject();
                            //writer.Flush();
                            */

                            try
                            {
                                var ret = await _azureCosmos
                                    .GetCosmosClient()
                                    .GetContainer("TEAMModelOS", "School")
                                    .ReplaceItemStreamAsync(memoryStream, classId, new PartitionKey($"Class-{schoolId}"));
                            }
                            catch (CosmosException ex)
                            {
                                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/updateClassStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
                            }
                            catch (Exception ex)
                            {
                                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/updateClassStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
                            }
                        }
                    }
                }
                //始終會加入新教室，除非只是單純的換座號或是姓名
                if (classInfos.Count == 0 || tmpStuds.Count != 0) //透過學生id查找教室，但沒找到任何已加入的教室
                {
                    //使用classId來查詢教室資訊
                    var dicClassInfo = await getClassInfoUseId(schoolId, tmpStuds.Select(o => o.Value.classId).ToList());
                    if (dicClassInfo.Count != 0)
                    {
                        foreach (var classInfo in dicClassInfo)
                        {
                            string classId = classInfo.Key;
                            string periodId = string.Empty, gradeId = string.Empty;

                            if (classInfo.Value.TryGetProperty("periodId", out var tmpPeriodId)) periodId = tmpPeriodId.GetString();
                            if (classInfo.Value.TryGetProperty("gradeId", out var tmpGradeId)) gradeId = tmpGradeId.GetString();

                            //用以紀錄教室內已存在的學生
                            List<(string id, string name, string no, string groupId, string groupName)> existStudents
                                = new List<(string id, string name, string no, string groupId, string groupName)>();
                            var Documents = classInfo.Value.GetProperty("students").EnumerateArray();
                            while (Documents.MoveNext())
                            {
                                JsonElement Document = Documents.Current;
                                string studId = null, no = null, name = null, groupId = null, groupName = null;

                                if (Document.TryGetProperty("id", out var tmpId)) studId = tmpId.GetString();
                                if (Document.TryGetProperty("name", out var tmpName)) name = tmpName.GetString();
                                if (Document.TryGetProperty("no", out var tmpNo)) no = tmpNo.GetString();
                                if (Document.TryGetProperty("groupId", out var tmpgroupId)) groupId = tmpgroupId.GetString();
                                if (Document.TryGetProperty("groupName", out var tmpgroupName)) groupName = tmpgroupName.GetString();
                                existStudents.Add((studId, name, no, groupId, groupName));
                            }

                            //如果classId相同，則將該學生加入現有的學生清單內。
                            foreach (var item in tmpStuds)
                            {
                                if (item.Value.classId.Equals(classId, StringComparison.Ordinal))
                                {
                                    existStudents.Add((item.Key, item.Value.name, item.Value.no, null, null));
                                    retStudentsClassInfo.Add(item.Key, (classId, periodId, gradeId, item.Value.name, item.Value.no));
                                }
                            }
                            existStudents = existStudents.OrderBy(o => string.IsNullOrWhiteSpace(o.no)).ThenBy(o => string.IsNullOrEmpty(o.no) ? 0 : int.Parse(o.no)).ToList();


                            #region 組JSON
                            using var memoryStream = new MemoryStream();
                            using var writer = new Utf8JsonWriter(memoryStream);
                            writer.WriteStartObject();
                            //寫入除了students及開頭為"_"的數據資料
                            foreach (var element in classInfo.Value.EnumerateObject())
                            {
                                if (!(element.Name.Equals("students", StringComparison.Ordinal) || element.Name.StartsWith("_")))
                                {
                                    element.WriteTo(writer);
                                }
                            }
                            writer.WritePropertyName("students");
                            writer.WriteStartArray();

                            foreach (var item in existStudents)
                            {
                                writer.WriteStartObject();
                                writer.WriteString("id", item.id);
                                if (string.IsNullOrWhiteSpace(item.name)) writer.WriteNull("name");
                                else writer.WriteString("name", item.name);
                                if (string.IsNullOrWhiteSpace(item.no)) writer.WriteNull("no");
                                else writer.WriteString("no", item.no);
                                if (string.IsNullOrWhiteSpace(item.groupId)) writer.WriteNull("groupId");
                                else writer.WriteString("groupId", item.groupId);
                                if (string.IsNullOrWhiteSpace(item.groupName)) writer.WriteNull("groupName");
                                else writer.WriteString("groupName", item.groupName);
                                writer.WriteEndObject();
                            }

                            /**
                            將原本已存在的學生寫回去
                            var Documents = item.Value.GetProperty("students").EnumerateArray();
                            while (Documents.MoveNext())
                            {
                                JsonElement Document = Documents.Current;
                                string name = string.Empty, no = string.Empty, studId = string.Empty;

                                if (Document.TryGetProperty("name", out var tmpName)) name = tmpName.GetString();
                                if (Document.TryGetProperty("id", out var tmpId)) studId = tmpId.GetString();
                                if (Document.TryGetProperty("no", out var tmpNo)) no = tmpNo.GetString();

                                writer.WriteStartObject();
                                writer.WriteString("id", studId);
                                if (string.IsNullOrWhiteSpace(name)) writer.WriteNull("name");
                                else writer.WriteString("name", name);
                                if (string.IsNullOrWhiteSpace(no)) writer.WriteNull("no");
                                else writer.WriteString("no", no);
                                writer.WriteEndObject();
                            }

                            將欲加入的學生寫入該教室名單內
                            var studList = students
                                .Where(o => o.Value.classId.Equals(classId, StringComparison.Ordinal))
                                .Select(o => new { id = o.Key, o.Value.name, o.Value.no }).ToList();
                            foreach (var stud in studList)
                            {
                                writer.WriteStartObject();
                                writer.WriteString("id", stud.id);
                                if (string.IsNullOrWhiteSpace(stud.name)) writer.WriteNull("name");
                                else writer.WriteString("name", stud.name);
                                if (string.IsNullOrWhiteSpace(stud.no)) writer.WriteNull("no");
                                else writer.WriteString("no", stud.no);
                                writer.WriteEndObject();

                                retStudentsClassInfo.Add(stud.id, (classId, periodId, gradeId, stud.name, stud.no));
                            }
                            */

                            writer.WriteEndArray();
                            writer.WriteEndObject();
                            writer.Flush();
                            #endregion
                            //var resultJson = Encoding.UTF8.GetString(memoryStream.ToArray());

                            try
                            {
                                var ret = await _azureCosmos
                                    .GetCosmosClient()
                                    .GetContainer("TEAMModelOS", "School")
                                    .ReplaceItemStreamAsync(memoryStream, classId, new PartitionKey($"Class-{schoolId}"));
                            }
                            catch (CosmosException ex)
                            {
                                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/updateClassStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
                            }
                            catch (Exception ex)
                            {
                                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/updateClassStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
                            }
                        }
                    }
                    else
                    {
                        //透過教室ID進行查詢，但查不到這間教室
                    }
                }

                return retStudentsClassInfo;
            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/updateClassStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/updateClassStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return null;
        }

        /// <summary>
        /// 創建學生帳號，目前SDK4.0預覽版還不支援批量創建(TransactionalBatch)，待SDK正式發行時在優化此代碼。
        /// </summary>
        /// <param name="userStudents"></param>
        /// <returns>已存在的ID</returns>
        private async Task<(bool isSuc, List<string> existId)> createStudents(List<Student> userStudents)
        {
            var existId = new List<string>();
            var exceptions = new List<Exception>();
            try
            {
                var container = _azureCosmos.GetCosmosClient().GetContainer("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)
            {
                Console.WriteLine(ex.Message);
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/createStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/createStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return (false, existId);
        }

        /// <summary>
        /// 批量重設密碼，若無給密碼則預設使用學號當密碼
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="students"></param>
        /// <returns>不存在的id</returns>
        private async Task<List<string>> resetPW(string schoolId, JsonElement.ArrayEnumerator students)
        {
            try
            {
                //取得前端欲重置的id與pw
                Dictionary<string, (string salt, string pw)> studentsInfo = new Dictionary<string, (string salt, string pw)>();

                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 = Utils.CreatSaltString(8);
                            string pw = student.TryGetProperty("pw", out var tmpPw) && !string.IsNullOrWhiteSpace(tmpPw.GetString())
                                ? Utils.HashedPassword(tmpPw.GetString(), salt)
                                : Utils.HashedPassword(id.GetString(), salt);
                            if (!studentsInfo.ContainsKey(id.GetString())) studentsInfo.Add(id.GetString(), (salt, pw));
                        }
                    }
                }

                CosmosContainer cosmosContainer = _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Student");
                //查學生的基本資料
                string queryText = $"SELECT * FROM c WHERE c.code = 'Base-{schoolId}' AND c.id IN ({string.Join(",", studentsInfo.Select(o => $"'{o.Key}'"))})";

                List<JsonElement> listStudent = new List<JsonElement>();

                await foreach (Response item in cosmosContainer
                    .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 accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                        while (accounts.MoveNext())
                        {
                            JsonElement account = accounts.Current;
                            string id = account.GetProperty("id").GetString();
                            bool isGetNameSuc = account.TryGetProperty("name", out var name);
                            bool isGetClassIdSuc = account.TryGetProperty("classId", out var classId);
                            bool isGetNoSuc = account.TryGetProperty("no", out var no);

                            using var memoryStream = new MemoryStream();
                            using var writer = new Utf8JsonWriter(memoryStream);
                            writer.WriteStartObject();
                            foreach (var element in account.EnumerateObject())
                            {
                                if (
                                     !(
                                        element.Name.Equals("salt", StringComparison.Ordinal)
                                     || element.Name.Equals("pw", StringComparison.Ordinal)
                                     || element.Name.StartsWith("_", StringComparison.Ordinal)))
                                {
                                    element.WriteTo(writer);
                                }
                            }
                            writer.WriteString("salt", studentsInfo[id].salt);
                            writer.WriteString("pw", studentsInfo[id].pw);
                            writer.WriteEndObject();
                            writer.Flush();

                            try
                            {
                                var ret = await cosmosContainer.ReplaceItemStreamAsync(memoryStream, id, new PartitionKey($"Base-{schoolId}"));
                            }
                            catch (CosmosException ex)
                            {
                                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/resetPW()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
                            }
                            catch (Exception ex)
                            {
                                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/resetPW()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
                            }

                            //將更新完的id從字典內移除
                            studentsInfo.Remove(id);
                        }
                    }
                }
                return studentsInfo.Select(o => o.Key).ToList();
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，Student/resetPW()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return null;
        }

        /// <summary>
        /// 學生登入
        /// </summary>
        /// <param name = "request" ></ param >
        [AllowAnonymous]
        [HttpPost("login")]

        public async Task<IActionResult> Login(JsonElement request)
        {
            try
            {
                var client = _azureCosmos.GetCosmosClient();
                //參數取得
                if (!request.TryGetProperty("school_code", out JsonElement school_code)) return BadRequest();
                if (!request.TryGetProperty("id", out JsonElement id)) return BadRequest();
                if (!request.TryGetProperty("pw", out JsonElement pw)) return BadRequest();  

                var response = await client.GetContainer("TEAMModelOS", "Student").ReadItemStreamAsync(id.GetString(), new PartitionKey($"Base-{school_code.GetString().ToLower()}"));
                if (response.Status == 200)
                {
                    var rjson = await JsonDocument.ParseAsync(response.ContentStream);
                    rjson.RootElement.TryGetProperty("salt", out JsonElement salt);
                    rjson.RootElement.TryGetProperty("pw", out JsonElement dbpw);
                    rjson.RootElement.TryGetProperty("name", out JsonElement name);
                    rjson.RootElement.TryGetProperty("picture", out JsonElement picture);                    

                    var HashedPW = Utils.HashedPassword(pw.ToString(), salt.ToString());
                    if (HashedPW.Equals(dbpw.GetString()))
                    {
                        string classid = string.Empty;
                        object classinfo = null;
                        List<object> courses = new List<object>();
                        //去學校找出所屬校本預設班級信息
                        var query = $"SELECT c.code, c.id, c.name, c.periodId, c.gradeId FROM c JOIN cs IN c.students WHERE cs.id = '{id}'";
                        var school = client.GetContainer("TEAMModelOS", "School");
                        await foreach (var item in school.GetItemQueryStreamIterator(queryText: query, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{school_code}") }))
                        {
                            using var json = await JsonDocument.ParseAsync(item.ContentStream);
                            if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                            {
                                foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                                {
                                    classid = obj.GetProperty("id").GetString();
                                    classinfo = obj.ToObject<object>();                                    
                                }
                            }
                        }
                        //找出所屬班級的課程列表                                    
                        var queryc = $"SELECT VALUE cc.course FROM c JOIN cc IN c.courses WHERE c.id = '{classid}'";
                        await foreach (var item in school.GetItemQueryStreamIterator(queryText: queryc, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"CourseManagement-{school_code}") }))
                        {
                            using var json = await JsonDocument.ParseAsync(item.ContentStream);
                            if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                            {
                                foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                                {
                                    courses.Add(obj.ToObject<object>());
                                }
                            }
                        }
                        // BLOB(學校，唯讀)
                        var (blob_uri, blob_sas) = _azureStorage.GetBlobContainerSAS(school_code.GetString().ToLower(), BlobContainerSasPermissions.Read);
                        //換取AuthToken，提供給前端
                        var auth_token = JwtAuthExtension.CreateAuthToken(_option.HostName, id.GetString(), name.GetString(), picture.GetString(), _option.JwtSecretKey, schoolID: school_code.GetString(), roles: new[] { "student" });
                        return Ok(new { error = 0, auth_token, blob_uri, blob_sas, classinfo, courses });
                    }
                    else
                    {
                        return Ok(new { error = 1, message = "账号或密码错误" });
                    }
                }
                else
                {
                    return Ok(new { error = 2, message = "無此帳號存在" });
                }                
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，student/login()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }
        }
    }
}