TEAMModelOS/TEAMModelOS.SDK/Models/Service/StudentService.cs

using Azure;
using Azure.Cosmos;
using Azure.Messaging.ServiceBus;
using HTEXLib.COMM.Helpers;
using Microsoft.Extensions.Configuration;
using OpenXmlPowerTools;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using TEAMModelOS.Models;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Extension;
using TEAMModelOS.SDK.Models;
using TEAMModelOS.SDK.Models.Service;

namespace TEAMModelOS.SDK
{
    public class StudentService
    {
        public class StudentActivity
        {

            public string id { get; set; }
            public string code { get; set; }

            /// <summary>
            /// 源数据的发布层级 类型 school  teacher
            /// </summary>
            public string owner { get; set; }
          
            public string pk {  get; set; }
            //评测模式
            public string source { get; set; }
            public string name { get; set; }
            public long startTime { get; set; }
            public long endTime { get; set; }
            public string scope { get; set; }
            public string school { get; set; }
            public string creatorId { get; set; }
        
            public List<string> subjects { get; set; }
         
            public List<string> classes { get; set; }
            public List<string> stuLists { get; set; }
            public List<string> tchLists { get; set; }
            public long createTime { get; set; } = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
            public Dictionary<string, JsonElement> ext { get; set; } = new Dictionary<string, JsonElement>();
            /// 评分状态，0，未评分， 1已评分
            public int sStatus { get; set; } = 0;
            //评测 纸本1或者书面问答0
            public int qamode { get; set; } = 0;
            //标记作业活动是否比交
            public bool mustSubmit { get; set; } = false;
            //标记问卷是否重复提交 0 否 1 是
            public int isSub { get; set; } = 0;
            /// <summary>
            /// 任务完成状态，-1 未参与，0，未完成， 1已完成
            /// </summary>
            public int taskStatus { get; set; } = -1;
            public string recordUrl { get; set; }
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="request"></param>
        /// <param name="userId"></param>
        /// <param name="userType"></param>
        /// <param name="groupListIds"></param>
        /// <param name="school"></param>
        /// <param name="types">Exam ,Vote,Homework,Survey</param>
        /// <param name="_azureCosmos"></param>
        /// <param name="azureRedis"></param>
        public static async void FindActivity(JsonElement request,string userId ,string userType, List<string>groupListIds,List<string> subjects, string school,List<string> types, AzureCosmosFactory _azureCosmos, AzureRedisFactory azureRedis)
        {
            string filed = "c.id ,c.code,c.owner,c.pk,c.qamode,c.name,c.school,c.startTime,c.endTime,c.classes,c.stuLists,c.tchLists,c.createTime,c.creatorId,c.ext,c.isSub,c.mustSubmit,c.sStatus,c.scope,c.source,c.subjects";
            if (string.IsNullOrWhiteSpace(school))
            {
                if (request.TryGetProperty("school", out JsonElement schooljson))
                {
                    if (!schooljson.ValueKind.Equals(JsonValueKind.Undefined) && !schooljson.ValueKind.Equals(JsonValueKind.Null) && schooljson.ValueKind.Equals(JsonValueKind.String))
                    {
                        school = schooljson.GetString();
                    }
                }
            }
            long stime = DateTimeOffset.UtcNow.AddDays(-30).ToUnixTimeMilliseconds(), etime = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds(); 
            if (request.TryGetProperty("stime", out JsonElement _stime))
            {
                if (!_stime.ValueKind.Equals(JsonValueKind.Undefined) && !_stime.ValueKind.Equals(JsonValueKind.Null) && _stime.TryGetInt64(out long data))
                {
                    stime= data;   
                }
            }
            if (request.TryGetProperty("etime", out JsonElement _etime))
            {
                if (!_etime.ValueKind.Equals(JsonValueKind.Undefined) && !_etime.ValueKind.Equals(JsonValueKind.Null) && _etime.TryGetInt64(out long data))
                {
                    etime= data;
                }
            }
            string code = null;
            string  groupListSQL =string .Empty ;


            if (groupListIds.IsNotEmpty())
            {
                List<string >  arrayStr= new List<string>();
                groupListIds.ForEach(z => {
                    arrayStr.Add($"array_contains(c.classes,'{z}') ");
                    arrayStr.Add($"array_contains(c.stuLists,'{z}') ");
                    arrayStr.Add($"array_contains(c.tchLists,'{z}') ");
                });
                groupListSQL= $"and ({string.Join(" or ", arrayStr)})";
            }
            else
            {
                throw new Exception("名单为空");
            }
            
            List<StudentActivity> activities = new List<StudentActivity>();
            if (types.IsEmpty()|| types.Contains("Exam")) {
                string subjectSQL = "";
                string subjectJoin = "";
                if (subjects.IsNotEmpty()) {
                    subjectJoin = "join s in c.subjects";
                    subjectSQL = $" and  c.id in ({string.Join(",",subjects.Select(z=>$"'{z}'"))}) ";
                }
                StringBuilder SQL = new StringBuilder( $"select { filed} from c {subjectJoin} where c.pk='Exam' {subjectSQL} {groupListSQL} and c.createTime>={stime} and c.createTime <= {etime} and c.startTime <= {DateTimeOffset.UtcNow.ToUnixTimeMilliseconds()}");
                //获取学校发布的活动
                if (userType.Equals(Constant.ScopeStudent)  && ! string.IsNullOrWhiteSpace(school)) {
                   var resultSchool =   await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.Common).GetList<StudentActivity>($"{SQL.ToString()}  and c.owner='school' ", $"Exam-{school}");
                    activities.AddRange(resultSchool.list);
                }
                //获取教师发布的活动（个人名单，学校名单）
                {
                    var resultTeacher = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.Common).GetList<StudentActivity>($"{SQL.ToString()}  and c.owner='teacher' ");
                    activities.AddRange(resultTeacher.list);
                }
            }

            if (types.IsEmpty()|| types.Contains("Vote"))
            {
               
                StringBuilder SQL = new StringBuilder($"select {filed} from c  where c.pk='Vote' {groupListSQL} and c.createTime>={stime} and c.createTime <= {etime} and c.startTime <= {DateTimeOffset.UtcNow.ToUnixTimeMilliseconds()}");
                //获取学校发布的活动
                if (userType.Equals(Constant.ScopeStudent)  && !string.IsNullOrWhiteSpace(school))
                {
                    var resultSchool = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.Common).GetList<StudentActivity>($"{SQL.ToString()}  and c.owner='school' ", $"Vote-{school}");
                    activities.AddRange(resultSchool.list);
                }
                //获取教师发布的活动（个人名单，学校名单）
                {
                    var resultTeacher = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.Common).GetList<StudentActivity>($"{SQL.ToString()}  and c.owner='teacher' ");
                    activities.AddRange(resultTeacher.list);
                }
            }
            if (types.IsEmpty()|| types.Contains("Survey"))
            {

                StringBuilder SQL = new StringBuilder($"select {filed} from c  where c.pk='Survey' {groupListSQL} and c.createTime>={stime} and c.createTime <= {etime} and c.startTime <= {DateTimeOffset.UtcNow.ToUnixTimeMilliseconds()}");
                //获取学校发布的活动
                if (userType.Equals(Constant.ScopeStudent)  && !string.IsNullOrWhiteSpace(school))
                {
                    var resultSchool = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.Common).GetList<StudentActivity>($"{SQL.ToString()}  and c.owner='school' ", $"Survey-{school}");
                    activities.AddRange(resultSchool.list);
                }
                //获取教师发布的活动（个人名单，学校名单）
                {
                    var resultTeacher = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.Common).GetList<StudentActivity>($"{SQL.ToString()}  and c.owner='teacher' ");
                    activities.AddRange(resultTeacher.list);
                }
            }
            if (types.IsEmpty()|| types.Contains("Homework"))
            {

                StringBuilder SQL = new StringBuilder($"select {filed} from c  where c.pk='Homework' {groupListSQL} and c.createTime>={stime} and c.createTime <= {etime} and c.startTime <= {DateTimeOffset.UtcNow.ToUnixTimeMilliseconds()}");
                //获取学校发布的活动
                if (userType.Equals(Constant.ScopeStudent)  && !string.IsNullOrWhiteSpace(school))
                {
                    var resultSchool = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.Common).GetList<StudentActivity>($"{SQL.ToString()}  and c.owner='school' ", $"Homework-{school}");
                    activities.AddRange(resultSchool.list);
                }
                //获取教师发布的活动（个人名单，学校名单）
                {
                    var resultTeacher = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.Common).GetList<StudentActivity>($"{SQL.ToString()}  and c.owner='teacher' ");
                    activities.AddRange(resultTeacher.list);
                }
            }
            if (types.IsEmpty()|| types.Contains("Art"))
            {

                StringBuilder SQL = new StringBuilder($"select {filed} from c  where c.pk='Art' {groupListSQL} and c.createTime>={stime} and c.createTime <= {etime} and c.startTime <= {DateTimeOffset.UtcNow.ToUnixTimeMilliseconds()}");
                //获取学校发布的活动
                if (userType.Equals(Constant.ScopeStudent)  && !string.IsNullOrWhiteSpace(school))
                {
                    var resultSchool = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.Common).GetList<StudentActivity>($"{SQL.ToString()}  and c.school='school' ", $"Art-{school}");
                    activities.AddRange(resultSchool.list);
                }
            }
            //作答 记录recordUrl  taskStatus
        }

       




        /// <summary>
        /// 
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="students"></param>
        /// <returns></returns>
        public static async Task<List<Student>> GeStudentData(AzureCosmosFactory _azureCosmos, string schoolId, IEnumerable<string> students)
        {
            List<Student> studentDatas = new List<Student>();
            if (students.Any())
            {
                string queryText = $"SELECT c.id, c.code ,c.classId ,c.year FROM c  WHERE c.id IN ({string.Join(",", students.Select(o => $"'{o}'"))})";
                await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student").GetItemQueryIterator<Student>(queryText: queryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{schoolId}") }))
                {
                    studentDatas.Add(item);
                }
            }
            return studentDatas;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="schoolId">学校编码</param>
        /// <param name="students">前端要修改的学生</param>
        /// <param name="opt">操作</param>
        /// <param name="prestudents">变更前的学生</param>
        /// <returns></returns>
        public static async Task<Dictionary<string, GroupChange>> CheckStudent(AzureServiceBusFactory _serviceBus, IConfiguration _configuration, AzureCosmosFactory _azureCosmos, string schoolId, List<Student> students, List<Student> prestudents)
        {
            List<Student> aftstudents = await StudentService.GeStudentData(_azureCosmos, schoolId, students?.Select(x => x.id));
            Dictionary<string, GroupChange> dictChange = new Dictionary<string, GroupChange>();
            if (prestudents.Count >= aftstudents.Count)
            {
                foreach (var pstu in prestudents)
                {
                    var afstu = aftstudents.Find(x => x.id.Equals(pstu.id));
                    if (afstu != null)
                    {
                        if (string.IsNullOrEmpty(pstu.classId) && !string.IsNullOrEmpty(afstu.classId))
                        {
                            //则是加入的学生
                            if (dictChange.ContainsKey(afstu.classId))
                            {
                                dictChange[afstu.classId].stujoin.Add(
                                new Member
                                {
                                    id = afstu.id,
                                    code = $"{schoolId }",
                                    type = 2
                                });
                            }
                            else
                            {
                                GroupChange change = new GroupChange
                                {
                                    scope = "school",
                                    school = schoolId,
                                    type = "student",
                                    originCode = schoolId,
                                    listid = afstu.classId,
                                    stujoin = new List<Member>
                                    {
                                        new Member
                                        {
                                            id= afstu.id,
                                            code= $"{schoolId}",
                                            type =2
                                        }
                                    }
                                };
                                dictChange.Add(afstu.classId, change);
                            }
                        }
                        else if (!string.IsNullOrEmpty(pstu.classId) && string.IsNullOrEmpty(afstu.classId))
                        {
                            //则该学生是解除了班级状态
                            if (dictChange.ContainsKey(pstu.classId))
                            {
                                dictChange[pstu.classId].stuleave.Add(
                                new Member
                                {
                                    id = pstu.id,
                                    code = $"{schoolId }",
                                    type = 2
                                });
                            }
                            else
                            {
                                GroupChange change = new GroupChange
                                {
                                    scope = "school",
                                    school = schoolId,
                                    type = "student",
                                    originCode = schoolId,
                                    listid = pstu.classId,
                                    stuleave = new List<Member>
                                    {
                                        new Member
                                        {
                                            id= pstu.id,
                                            code= $"{schoolId}",
                                            type=2
                                        }
                                    }
                                };
                                dictChange.Add(pstu.classId, change);
                            }
                        }
                        else if (!string.IsNullOrEmpty(pstu.classId) && !string.IsNullOrEmpty(afstu.classId))
                        {
                            //id不同则有异动
                            if (!pstu.classId.Equals(afstu.classId))
                            {
                                //则是加入的学生
                                if (dictChange.ContainsKey(afstu.classId))
                                {
                                    dictChange[afstu.classId].stujoin.Add(
                                    new Member
                                    {
                                        id = afstu.id,
                                        code = $"{schoolId }",
                                        type = 2
                                    });
                                }
                                else
                                {
                                    GroupChange change = new GroupChange
                                    {
                                        scope = "school",
                                        school = schoolId,
                                        type = "student",
                                        originCode = schoolId,
                                        listid = afstu.classId,
                                        stujoin = new List<Member>
                                    {
                                        new Member
                                        {
                                            id= afstu.id,
                                            code= $"{schoolId}",
                                            type = 2
                                        }
                                    }
                                    };
                                    dictChange.Add(afstu.classId, change);
                                }//则该学生是解除了班级状态
                                if (dictChange.ContainsKey(pstu.classId))
                                {
                                    dictChange[pstu.classId].stuleave.Add(
                                    new Member
                                    {
                                        id = pstu.id,
                                        code = $"{schoolId }",
                                        type = 2
                                    });
                                }
                                else
                                {
                                    GroupChange change = new GroupChange
                                    {
                                        scope = "school",
                                        school = schoolId,
                                        type = "student",
                                        originCode = schoolId,
                                        listid = pstu.classId,
                                        stuleave = new List<Member>
                                    {
                                        new Member
                                        {
                                            id= pstu.id,
                                            code= $"{schoolId}",
                                            type= 2
                                        }
                                    }
                                    };
                                    dictChange.Add(pstu.classId, change);
                                }
                            }
                            else
                            {
                                //相同则不管
                            }
                        }
                        else
                        {
                            //变更前后都没绑定班级则不管。
                        }
                    }
                    else
                    {
                        if (!string.IsNullOrEmpty(pstu.classId))
                        {
                            //则该学生是被删除的学生
                            if (dictChange.ContainsKey(pstu.classId))
                            {
                                dictChange[pstu.classId].stuleave.Add(
                                new Member
                                {
                                    id = pstu.id,
                                    code = $"Base-{schoolId }",
                                    type = 2
                                });
                            }
                            else
                            {
                                GroupChange change = new GroupChange
                                {
                                    scope = "school",
                                    school = schoolId,
                                    type = "student",
                                    originCode = schoolId,
                                    listid = pstu.classId,
                                    stuleave = new List<Member>
                                    {
                                        new Member
                                        {
                                            id= pstu.id,
                                            code= $"{schoolId}",
                                            type = 2
                                        }
                                    }
                                };
                                dictChange.Add(pstu.classId, change);
                            }
                        }
                    }
                }
            }
            else
            {
                foreach (var afstu in aftstudents)
                {
                    var pstu = prestudents.Find(x => x.id.Equals(afstu.id));
                    if (pstu != null)
                    {
                        if (string.IsNullOrEmpty(pstu.classId) && !string.IsNullOrEmpty(afstu.classId))
                        {
                            //则是加入的学生
                            if (dictChange.ContainsKey(afstu.classId))
                            {
                                dictChange[afstu.classId].stujoin.Add(
                                new Member
                                {
                                    id = afstu.id,
                                    code = $"{schoolId }",
                                    type = 2
                                });
                            }
                            else
                            {
                                GroupChange change = new GroupChange
                                {
                                    scope = "school",
                                    school = schoolId,
                                    type = "student",
                                    originCode = schoolId,
                                    listid = afstu.classId,
                                    stujoin = new List<Member>
                                    {
                                        new Member
                                        {
                                            id= afstu.id,
                                            code= $"Base-{schoolId}",
                                            type = 2
                                        }
                                    }
                                };
                                dictChange.Add(afstu.classId, change);
                            }
                        }
                        else if (!string.IsNullOrEmpty(pstu.classId) && string.IsNullOrEmpty(afstu.classId))
                        {
                            //则该学生是解除了班级状态
                            if (dictChange.ContainsKey(pstu.classId))
                            {
                                dictChange[pstu.classId].stuleave.Add(
                                new Member
                                {
                                    id = pstu.id,
                                    code = $"{schoolId }",
                                    type = 2
                                });
                            }
                            else
                            {
                                GroupChange change = new GroupChange
                                {
                                    scope = "school",
                                    school = schoolId,
                                    type = "student",
                                    originCode = schoolId,
                                    listid = pstu.classId,
                                    stuleave = new List<Member>
                                    {
                                        new Member
                                        {
                                            id= pstu.id,
                                            code= $"{schoolId}",
                                            type= 2
                                        }
                                    }
                                };
                                dictChange.Add(pstu.classId, change);
                            }
                        }
                        else if (!string.IsNullOrEmpty(pstu.classId) && !string.IsNullOrEmpty(afstu.classId))
                        {
                            //id不同则有异动
                            if (!pstu.classId.Equals(afstu.classId))
                            {
                                //则是加入的学生
                                if (dictChange.ContainsKey(afstu.classId))
                                {
                                    dictChange[afstu.classId].stujoin.Add(
                                    new Member
                                    {
                                        id = afstu.id,
                                        code = $"{schoolId }",
                                        type = 2
                                    });
                                }
                                else
                                {
                                    GroupChange change = new GroupChange
                                    {
                                        scope = "school",
                                        school = schoolId,
                                        type = "student",
                                        originCode = schoolId,
                                        listid = afstu.classId,
                                        stujoin = new List<Member>
                                    {
                                        new Member
                                        {
                                            id= afstu.id,
                                            code= $"{schoolId}",
                                            type = 2
                                        }
                                    }
                                    };
                                    dictChange.Add(afstu.classId, change);
                                }//则该学生是解除了班级状态
                                if (dictChange.ContainsKey(pstu.classId))
                                {
                                    dictChange[pstu.classId].stuleave.Add(
                                    new Member
                                    {
                                        id = pstu.id,
                                        code = $"{schoolId }",
                                        type = 2
                                    });
                                }
                                else
                                {
                                    GroupChange change = new GroupChange
                                    {
                                        scope = "school",
                                        school = schoolId,
                                        type = "student",
                                        originCode = schoolId,
                                        listid = pstu.classId,
                                        stuleave = new List<Member>
                                    {
                                        new Member
                                        {
                                            id= pstu.id,
                                            code= $"{schoolId}",
                                            type=2
                                        }
                                    }
                                    };
                                    dictChange.Add(pstu.classId, change);
                                }
                            }
                            else
                            {
                                //相同则不管
                            }
                        }
                        else
                        {
                            //变更前后都没绑定班级则不管。
                        }
                    }
                    else
                    {
                        if (!string.IsNullOrEmpty(afstu.classId))
                        {
                            //代表学生是新增的
                            if (dictChange.ContainsKey(afstu.classId))
                            {
                                dictChange[afstu.classId].stujoin.Add(
                                new Member
                                {
                                    id = afstu.id,
                                    code = $"{schoolId }",
                                    type = 2
                                });
                            }
                            else
                            {
                                GroupChange change = new GroupChange
                                {
                                    scope = "school",
                                    school = schoolId,
                                    type = "student",
                                    originCode = schoolId,
                                    listid = afstu.classId,
                                    stujoin = new List<Member>
                                    {
                                        new Member
                                        {
                                            id= afstu.id,
                                            code= $"{schoolId}",
                                            type = 2
                                        }
                                    }
                                };
                                dictChange.Add(afstu.classId, change);
                            }
                        }
                    }
                }
            }
            foreach (var changed in dictChange.Keys)
            {
                var change = dictChange[changed];
                if (change.stujoin.Count != 0 || change.stuleave.Count != 0)
                {
                    var messageChange = new ServiceBusMessage(change.ToJsonString());
                    messageChange.ApplicationProperties.Add("name", "GroupChange");
                    var ActiveTask = _configuration.GetValue<string>("Azure:ServiceBus:ActiveTask");
                    await _serviceBus.GetServiceBusClient().SendMessageAsync(ActiveTask, messageChange);
                }
            }

            return dictChange;
        }

        public record StudentInfo
        {
            public string studentId { get; set; }
            public string picture { get; set; }
            public string name { get; set; }
            public string mobile { get; set; }
            public string mail { get; set; }
            /// <summary>
            /// f女性 m男性 n 保密 
            /// </summary>
            public string gender { get; set; }

        }
        /// <summary>
        /// 整理前端匯入的學生資訊
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="students"></param>
        /// <returns></returns>
        private static (Dictionary<string, (string name, string no, int year, string salt, string pw, string classNo, string className, string periodId, int classYear,
            string guardian, string gName, string gPhone,string imei, List<StudentGuardian> guardians,string gender)> studs,
            Dictionary<string, (string className, string periodId, int year, string no)> classInfo,
            Dictionary<string, List<(string id, string no)>> classStudNo,
            List<string> errorYear,
            List<string> duplId) doSortImpStuds(string schoolId, JsonElement.ArrayEnumerator students)
        {
            //批量匯入 檢查輸入數據 確認座號 確認教室(創建教室) 確認學生存不存在或是要不要更新

            //存放輸入的學生資訊 key:stud id value:學生詳細資料
            Dictionary<string, (string name, string no, int year, string salt, string pw, string classNo, string className, string periodId, int classYear,
                string guardian, string gName, string gPhone, string imei, List<StudentGuardian> guardians,string gender) > dicStuds =
                new Dictionary<string, (string name, string no, int year, string salt, string pw, string classNo, string className, string periodId, int classYear,
                string guardian, string gName, string gPhone, string imei, List<StudentGuardian> guardians,string gender)>();

            //存放教室資訊用 key:classNo value:className
            Dictionary<string, (string className, string periodId, int year, string classNo)> dicClassInfo = new Dictionary<string, (string className, string periodId, int year, string classNo)>();
            //存放欲加入該間教室的學生座號清單 key:classNo value:no list
            Dictionary<string, List<(string id, string no)>> dicClassStudNo = new Dictionary<string, List<(string id, string no)>>();
            //存放輸入id重複
            List<string> duplId = new List<string>();
            List<string> errorYear = new List<string>();

            while (students.MoveNext())
            {
                //string id = null, no = null, name = null, year = null, pw = null, classNo = null, className = null;
                JsonElement student = students.Current;

                //ClassNo內的座號
                //欲加入的教室
                //查學生 
                //該間教室的座號與目前欲更新的是否有重複 有些可能是同教室換座號 反正都要將學生讀出來
                //舊學生完整資料+新學生資料

                //進行輸入資料的整理
                if (student.TryGetProperty("id", out var tmpId))
                {
                    string id = tmpId.GetString();
                    //如果id欄位是空的，則跳過該筆資料
                    if (string.IsNullOrWhiteSpace(id)) continue;

                    //輸入的id不應有重複
                    if (dicStuds.ContainsKey(id))
                    {
                        //如果id重複，則將之從整理清單內清除
                        duplId.Add(id);
                        dicStuds.Remove(id);
                    }

                    (string name, string no, int year, string salt, string pw, string classNo, string className, string periodId, int classYear, 
                        string guardian, string gName, string gPhone, string imei, List<StudentGuardian> guardians, string gender) studentInfo =
                        (null, null, 0, null, null, null, null, null, 0, null, null, null, null, null,null) ;

                    if (student.TryGetProperty("name", out var tmpName) && !string.IsNullOrWhiteSpace(tmpName.GetString())) studentInfo.name = tmpName.GetString();


                    //入學學年為必須，故若是無給值則將之紀錄並跳過該筆資料
                    if (student.TryGetProperty("year", out var tmpYear) && !string.IsNullOrWhiteSpace(Convert.ToString(tmpYear))) studentInfo.year = tmpYear.GetInt32();
                    else
                    {
                        errorYear.Add(id);
                        continue;
                    }

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

                    if (student.TryGetProperty("periodId", out var tmpPeriodId) && !string.IsNullOrWhiteSpace(tmpPeriodId.GetString())) studentInfo.periodId = tmpPeriodId.GetString();
                    //  if (student.TryGetProperty("gradeIndex", out var tmpGradeIndex)) studentInfo.gradeIndex = tmpGradeIndex.GetInt32();


                    if (student.TryGetProperty("classNo", out var tmpClassNo) && !string.IsNullOrWhiteSpace(tmpClassNo.GetString()))
                    {
                        studentInfo.classNo = tmpClassNo.GetString();

                        //在新建帳號上，應要給classNo才能設定no，但是若只是已存在的帳號要進行座號更新呢? 為避免使用者出錯，故已存在的帳號也應當給classNo。
                        if (student.TryGetProperty("no", out var tmpNo) && !string.IsNullOrWhiteSpace(tmpNo.GetString()))
                        {
                            studentInfo.no = tmpNo.GetString();

                            //這邊先將該間教室欲使用到的no整理出來
                            if (dicClassStudNo.ContainsKey(tmpClassNo.GetString())) dicClassStudNo[tmpClassNo.GetString()].Add((id, tmpNo.GetString()));
                            else dicClassStudNo.Add(tmpClassNo.GetString(), new List<(string id, string no)>() { (id, tmpNo.GetString()) });
                        }

                        //有給classNo才會紀錄className，classNo屬於實體教室門牌號，全校理當只會有一個。 
                        int year = 0;
                        if (student.TryGetProperty("classYear", out var tmpClassYear) && tmpClassYear.TryGetInt32(out int syear))
                        {
                            year = syear;
                            studentInfo.classYear = syear;
                        }
                        if (student.TryGetProperty("className", out var tmpClassName) && !string.IsNullOrWhiteSpace(tmpClassName.GetString()))
                        {

                            studentInfo.className = tmpClassName.GetString();
                            if (!dicClassInfo.ContainsKey($"{studentInfo.periodId}_{year}_{tmpClassNo.GetString()}"))
                            { dicClassInfo.Add($"{studentInfo.periodId}_{year}_{tmpClassNo.GetString()}", (tmpClassName.GetString(), studentInfo.periodId, year, tmpClassNo.GetString())); }
                        }
                    }
                    ///导入的时候
                    if (student.TryGetProperty("guardian", out var guardian) && !string.IsNullOrWhiteSpace($"{guardian}")) {
                        studentInfo.guardian =$"{guardian}";
                        student.TryGetProperty("gName", out var gName);
                        student.TryGetProperty("gPhone", out var gPhone);
                        studentInfo.gName = $"{gName}";
                        studentInfo.gPhone = $"{gPhone}";
                    }
                    //更新的时候
                    if (student.TryGetProperty("guardians", out var _guardians) && _guardians.ValueKind.Equals(JsonValueKind.Array)) {
                        List<StudentGuardian> guardians = _guardians.Deserialize<List<StudentGuardian>>();
                        studentInfo.guardians = guardians;
                    }
                    if (student.TryGetProperty("imei", out var tmpImei) && !string.IsNullOrWhiteSpace($"{tmpImei}")) studentInfo.imei = tmpImei.GetString();
                    if (student.TryGetProperty("gender", out var tmpgender) && !string.IsNullOrWhiteSpace($"{tmpgender}")) studentInfo.gender = tmpgender.GetString();
                    //將最後結果加到字典內
                    dicStuds.Add(id, studentInfo);
                }
            }
            return (dicStuds, dicClassInfo, dicClassStudNo, errorYear, duplId);
        }

        /// <summary>
        /// 更新或是新增學生
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="students"></param>
        /// <returns></returns>
        public static async Task<(List<object> studs, Dictionary<string, List<string>> classDuplNos, List<string> errorIds)> upsertStudents(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option,
            string schoolId,
            JsonElement.ArrayEnumerator students)
        {
            try
            {
                var sortedImpData = doSortImpStuds(schoolId, students);

                //var   classNos = sortedImpData.classInfo.Select(o => new {key= o.Key, periodId=o.Value.periodId,index= o.Value.gradeIndex,year = o.Value.year }).ToList();
                //抓到教室資訊
                var classInfos = await getClassInfoUseNo(_azureCosmos, _dingDing, _option, schoolId, sortedImpData.classInfo);
                //取出已存在教室的classId，後面查座號要用。

                List<Task> tasks = new List<Task>();
                //Key:ClassNo Value:No 匯入時只有ClassNo
                Dictionary<string, List<(string id, string no)>> classStudNos = new Dictionary<string, List<(string id, string no)>>();

                //Key:ClassNo_gradeId Value:ClassId 存放教室no及id的變數
                Dictionary<string, (string classId, string className, string periodId, string gradeId, int year)> classNoId = new Dictionary<string, (string classId, string className, string periodId, string gradeId, int year)>();
                List<Class> classes = new List<Class>();
                foreach (var classInfo in classInfos)
                {
                    classes.Add(classInfo.Value);
                    string classGradeId = classInfo.Value.gradeId;
                    int classYear = classInfo.Value.year;
                    classNoId.Add(classInfo.Value.no + "_" + classYear,
                        (classInfo.Value.id, classInfo.Value.name, classInfo.Value.periodId, classGradeId, classYear));
                    tasks.Add(
                        Task.Run(
                            async () =>
                            {
                                //(id,no)
                                var studNo = await checkStudNo(_azureCosmos, _dingDing, _option, schoolId, classInfo.Value.id);
                                classStudNos.Add(classInfo.Value.no + "_" + classInfo.Value.year, studNo);
                            }));
                }

                //這邊整理出不存在的教室，之後創建新教室用(比對classNo)。
                //var nonexistentClassNo = classNos.Except(classInfos.Select(o => o.Key).ToList());
                List<string> exsitkey = new List<string>();
                foreach (var classInfo in classInfos)
                {
                    //$"{studentInfo.periodId}_{year}_{tmpClassNo.GetString()}"
                    var key = $"{classInfo.Value.periodId}_{classInfo.Value.year}_{classInfo.Value.no}";
                    exsitkey.Add(key);
                }
                List<KeyValuePair<string, (string className, string periodId, int year, string no)>> nonexistentClassNo = new List<KeyValuePair<string, (string className, string periodId, int year, string no)>>();
                foreach (var key in sortedImpData.classInfo.Keys)
                {
                    if (!exsitkey.Contains(key))
                    {
                        nonexistentClassNo.Add(new KeyValuePair<string, (string className, string periodId, int year, string no)>(key, sortedImpData.classInfo[key]));
                    }
                }

                // var nonexistentClassNo = exsitkey.Except(sortedImpData.classInfo.Select(o => o.Key).ToList());
                if (nonexistentClassNo.Count() != 0)
                {
                    var gradesInfo = await getGrades(_azureCosmos, _dingDing, _option, schoolId);
                    foreach (var item in nonexistentClassNo)
                    {
                        string gradeId = string.Empty;
                        string periodId = periodId = sortedImpData.classInfo[item.Key].periodId;
                        int year = sortedImpData.classInfo[item.Key].year;
                        //確認該學段存在及輸入的年級index正確(-1後大於等於0)
                        //if (gradesInfo.ContainsKey(sortedImpData.classInfo[item].periodId) && sortedImpData.classInfo[item].gradeIndex - 1>=0)
                        // {
                        // periodId = sortedImpData.classInfo[item].periodId;

                        //gradeId = gradesInfo[sortedImpData.classInfo[item].periodId][sortedImpData.classInfo[item].gradeIndex - 1].gradeId;
                        //}
                        //建立新教室
                        (string classId, string classNo, string className, string periodId, string gradeId, int classYear) retCreateClassInfo =
                        await createClassInfo(_azureCosmos, _dingDing, _option,
                            schoolId,
                            null,
                            sortedImpData.classInfo[item.Key].className,
                            sortedImpData.classInfo[item.Key].no,
                            periodId,
                            gradeId,
                            year);

                        classStudNos.Add(retCreateClassInfo.classNo + "_" + retCreateClassInfo.classYear, new List<(string id, string no)>());
                        classNoId.Add(retCreateClassInfo.classNo + "_" + retCreateClassInfo.classYear, (retCreateClassInfo.classId, retCreateClassInfo.className, periodId, gradeId, year));
                    }
                }

                var taskWhenAll = Task.WhenAll(tasks);
                taskWhenAll.Wait();

                //-------------------------------------------------------------------------

                //建立學生或是更新學生，並且要確認座號是否重複
                //每間教室的全部座號 欲更新的教室座號
                //先新建帳號若出現409則進行資料更新

                //紀錄有重複做號的id
                Dictionary<string, List<string>> duplNos = new Dictionary<string, List<string>>();
                List<string> errorIds = new List<string>();
                List<object> retStuds = new List<object>();


                CosmosContainer cosmosContainer = _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student");

                long now = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
                //並行處理
                foreach (var stud in sortedImpData.studs)
                {
                    //這邊一樣要確認已存在和欲加入還有欲修改的座號。
                    //欲修改的不會有重複
                    string classId = null;
                    bool isContinue = false;
                    if (!string.IsNullOrWhiteSpace(stud.Value.no) && !string.IsNullOrWhiteSpace(stud.Value.classNo))
                    {
                        classId = classNoId[stud.Value.classNo + "_" + stud.Value.classYear].classId;
                        (string id, string no) existNoInfo = (null, null);
                        //檢查要更新的座號是否已存在於雲端座號(已被其他同學使用)
                        classStudNos[stud.Value.classNo + "_" + stud.Value.classYear].ForEach(
                            o =>
                            {
                                if (o.no.Equals(stud.Value.no) && !o.id.Equals(stud.Key))
                                {
                                    existNoInfo = (o.id, o.no);
                                    if (duplNos.ContainsKey(stud.Value.classNo)) duplNos[stud.Value.classNo].Add(stud.Key);
                                    else duplNos.Add(stud.Value.classNo, new List<string>() { stud.Key });
                                }
                            });

                        //如果不是空的代表有座號重覆到，此時要再確認這個重複座號的id，是否存在於這次的更新，而且是要更新座號的。
                        if (!string.IsNullOrWhiteSpace(existNoInfo.id))
                        {
                            isContinue = true;
                            //輪巡所有匯入的學生資料，並檢查匯入的座號。
                            sortedImpData.classStudNo[stud.Value.classNo].ForEach(
                                o =>
                                {
                                    if (o.id.Equals(existNoInfo.id) && !o.no.Equals(existNoInfo.no))
                                    {
                                        //可以更新該座號
                                        isContinue = false;
                                        duplNos[stud.Value.classNo].Remove(stud.Key);
                                        return;
                                    }
                                });
                        }
                    }
                    if (isContinue) continue;

                    (string id, string name, string picture, int year, string no, string classId, string classNo, string className, string gradeId, string periodId) tmpStudInfo
                       = (stud.Key, stud.Value.name, null, stud.Value.year, stud.Value.no, null, stud.Value.classNo, null, null, null);

                    using var memoryStream = new MemoryStream();
                    using var writerNew = new Utf8JsonWriter(memoryStream);
                    writerNew.WriteStartObject();

                    writerNew.WriteString("id", stud.Key);
                    writerNew.WriteString("pk", $"Base");
                    writerNew.WriteString("code", $"Base-{schoolId}");
                    writerNew.WriteString("schoolId", schoolId);
                    writerNew.WriteNumber("year", stud.Value.year);
                    writerNew.WriteNumber("createTime", now);
                    writerNew.WriteString("salt", stud.Value.salt);
                    writerNew.WriteString("pw", stud.Value.pw);
                    if (string.IsNullOrWhiteSpace(stud.Value.name)) writerNew.WriteNull("name");
                    else writerNew.WriteString("name", stud.Value.name);
                    if (string.IsNullOrWhiteSpace(stud.Value.gender)) writerNew.WriteNull("gender");
                    else writerNew.WriteString("gender", stud.Value.gender);

                    writerNew.WriteNull("picture");
                    writerNew.WriteNull("mail");
                    writerNew.WriteNull("mobile");
                    writerNew.WriteNull("country");

                    if (!string.IsNullOrWhiteSpace(stud.Value.periodId))
                    {
                        writerNew.WriteString("periodId", stud.Value.periodId);
                    }

                    if (string.IsNullOrWhiteSpace(stud.Value.classNo)) writerNew.WriteNull("classId");
                    else
                    {
                        writerNew.WriteString("classId", classId);
                        tmpStudInfo.classId = classId;
                        tmpStudInfo.className = classNoId[stud.Value.classNo + "_" + stud.Value.classYear].className;
                        tmpStudInfo.gradeId = classNoId[stud.Value.classNo + "_" + stud.Value.classYear].gradeId;
                        tmpStudInfo.periodId = classNoId[stud.Value.classNo + "_" + stud.Value.classYear].periodId;
                    }
                    
                  
                     
                    if (string.IsNullOrWhiteSpace(stud.Value.no))
                    {
                        writerNew.WriteNull("no");
                        writerNew.WriteNull("irs");
                    }
                    else
                    {
                        writerNew.WriteString("irs", stud.Value.no);
                        writerNew.WriteString("no", stud.Value.no);
                    };
                    writerNew.WriteNull("groupId");
                    writerNew.WriteNull("groupName");
                    ///写入监护人
                    if (!string.IsNullOrWhiteSpace(stud.Value.gPhone)) {
                        writerNew.WriteStartArray("guardians");
                        writerNew.WriteStartObject();
                        writerNew.WriteString("relation", stud.Value.guardian);
                        writerNew.WriteString("name", stud.Value.gName);
                        writerNew.WriteString("mobile", stud.Value.gPhone);
                        writerNew.WriteEndObject();
                        writerNew.WriteEndArray();
                    }
                    
                    writerNew.WriteEndObject();
                    writerNew.Flush();
                    if (!string.IsNullOrWhiteSpace(stud.Value.imei))
                    {
                        await upsertImei(stud.Key, stud.Value.imei, schoolId, "keep", cosmosContainer);
                    }
                    var response = await cosmosContainer.CreateItemStreamAsync(memoryStream, new PartitionKey($"Base-{schoolId}"));

                    if (response.Status == (int)HttpStatusCode.Created)
                    {
                        //如果是Created則啥都不做，讓他去下面進行資料的彙整。
                    }
                    //查到已存在的id，則進行基本資料更新。
                    else if (response.Status == (int)HttpStatusCode.Conflict)
                    {
                        try
                        {
                            bool isUpPwDone = false;
                            Student student = await _azureCosmos
                                  .GetCosmosClient()
                                  .GetContainer(Constant.TEAMModelOS, "Student")
                                  .ReadItemAsync<Student>(stud.Key, new PartitionKey($"Base-{schoolId}"));
                            if (!string.IsNullOrWhiteSpace(stud.Value.name))
                            {
                                student.name = stud.Value.name;
                            }
                            if (stud.Value.year > 0)
                            {
                                student.year = stud.Value.year;
                            }
                            if (!string.IsNullOrWhiteSpace(stud.Value.no))
                            {
                                student.no = stud.Value.no;
                                student.irs=stud.Value.no;
                            }
                            if (!string.IsNullOrWhiteSpace(stud.Value.gender))
                            {
                                student.gender = stud.Value.gender;
                            }
                            if (!string.IsNullOrWhiteSpace(stud.Value.classNo))
                            {
                                student.classId = classId;
                            }
                            if (!string.IsNullOrWhiteSpace(stud.Value.periodId))
                            {
                                student.periodId = stud.Value.periodId;
                            }
                            if (!string.IsNullOrWhiteSpace(student.classId)) {
                                var clazz =  classes.FindAll(x => x.id.Equals(student.classId));
                                if (clazz.IsNotEmpty()) {
                                    student.graduate = clazz[0].graduate;
                                }
                            }
                            if (!isUpPwDone)
                            {
                                student.pw = stud.Value.pw;
                                student.salt = stud.Value.salt;
                                isUpPwDone = true;
                            }
                            tmpStudInfo.picture = student.picture;
                            if (!string.IsNullOrWhiteSpace(stud.Value.gPhone)) {
                                List<Guardian> guardians = new List<Guardian>();
                                string sql = $"select distinct value (c) from c   where  c.mobile='{stud.Value.gPhone}' ";
                                await foreach (var item in cosmosContainer.GetItemQueryIterator<Guardian>(queryText: sql, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey("Base-Guardian") })) {
                                    guardians.Add(item);
                                }
                                if (guardians.Any())
                                {
                                    guardians.ForEach(async x => {
                                        GuardianStudent guardianStudent =  x.students.Find(s => s.id.Equals(stud.Key)  && s.code.Equals(schoolId));
                                        if (guardianStudent == null) {
                                            x.students.Add(new GuardianStudent { relation=stud.Value.guardian, type=2,id=stud.Key,code=schoolId,name=stud.Value.name });
                                        }
                                       await cosmosContainer.ReplaceItemAsync(x, x.id, new PartitionKey(x.code));
                                    });
                                }
                                else {
                                    Guardian guardian = new Guardian {
                                        id = Guid.NewGuid().ToString(),
                                        code= "Base-Guardian",
                                        pk= "Guardian",
                                        mobile=stud.Value.gPhone,
                                        name = stud.Value.gName,
                                        students = new List<GuardianStudent> { new GuardianStudent { relation=stud.Value.guardian,  type = 2, id = stud.Key, code = schoolId, name = stud.Value.name } }
                                    };
                                    await  cosmosContainer.CreateItemAsync(guardian,   new PartitionKey(guardian.code));
                                }
                                var studentGuardian = student.guardians.Find(x => !string.IsNullOrWhiteSpace(x.mobile) && x.mobile.Equals(stud.Value.gPhone));
                                if (studentGuardian != null)
                                {
                                    studentGuardian.name = stud.Value.gName;
                                    studentGuardian.relation = stud.Value.guardian;
                                }
                                else
                                {
                                    student.guardians.Add(new StudentGuardian { mobile = stud.Value.gPhone, name = stud.Value.gName, relation = stud.Value.guardian });
                                }
                            }
                            if (stud.Value.guardians.IsNotEmpty()) {
                                student.guardians=stud.Value.guardians;
                            }
                            await cosmosContainer.ReplaceItemAsync(student, stud.Key, new PartitionKey($"Base-{schoolId}"));
                        }
                        catch (CosmosException ex)
                        {
                            errorIds.Add(stud.Key);
                            continue;
                        }
                    }
                    else
                    {
                        errorIds.Add(stud.Key);
                        continue;
                    }

                    //整理輸出用資料
                    retStuds.Add(
                        new
                        {
                            tmpStudInfo.id,
                            tmpStudInfo.name,
                            tmpStudInfo.picture,
                            tmpStudInfo.year,
                            tmpStudInfo.no,
                            tmpStudInfo.classId,
                            tmpStudInfo.classNo,
                            tmpStudInfo.className,
                            // tmpStudInfo.gradeId,
                            // tmpStudInfo.periodId
                        });
                }
                return (retStuds, duplNos.Where(o => o.Value.Count != 0).ToDictionary(o => o.Key, o => o.Value), errorIds);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg(
                            $"IES5，{_option.Location}，StudentController/upsertStudents()\nex:{ex.Message}\n{ex.StackTrace}",
                            GroupNames.醍摩豆服務運維群組);
            }
            return (null, null, null);
        }
        /// <summary>
        /// grand_type 
        /// 为import：如果导入则已最新，没导入则不动这个字段的值，维持现状
        /// 为create:如果有值，则绑定，并删除stuid关联的别的imei.
        /// 为update 
        /// 为delete
        /// </summary>
        /// <param name="stuid"></param>
        /// <param name="imei"></param>
        /// <param name="schoolId"></param>
        /// <param name="grand_type"></param>
        /// <param name="cosmosContainer"> 
        /// clean 当传入的电子学生证值为空，判断要强制清除关联的学生电子学生证 ，
        /// keep 保持现状，导入时
        /// delete 因学生被删除，强制删除电子学生证。</param>
        /// <returns></returns>
        public static async Task upsertImei(string stuid ,string imeiid,string schoolId,string grand_type, CosmosContainer cosmosContainer) {
            List<Imei> imeis = new List<Imei>();
            string sql = $"select value c from c where c.stuid='{stuid}'  and c.school='{schoolId}' ";
            await foreach (var item in cosmosContainer.GetItemQueryIterator<Imei>(queryText: sql, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey("Imei") }))
            {
                imeis.Add(item);
            }
            switch (grand_type)
            {
                case "clean":
                case "keep":
                    bool notin = true;
                    List<Imei> update = new List<Imei>();
                    List<Imei> delete = new List<Imei>();
                    imeis.ForEach(x => {
                        //如果传入的电子学生证id不存在，且是单个更新或创建，则解除学生id的电子学生证绑定，并删除该电子学生证。
                        if (string.IsNullOrWhiteSpace(imeiid)) {
                            if (grand_type.Equals("clean") )
                            {
                                delete.Add(x);
                            }// 如果是导入模式，且电子学生证没有值则不动。
                        }
                        else {
                            //如果电子学生证有值则，解除之前的所有与当前电子学生证id不符的绑定。
                            if (!x.id.Equals(imeiid))
                            {
                                x.stuid = null;
                                x.school = null;
                                delete.Add(x);
                            }
                            else
                            {
                                //存在且正确绑定
                                notin = false;
                            }
                        }
                        
                    });
                    //当前学生还未有任何电子学生证，且有新的电子学生证id传入，则需要捞出电子学生证，绑定，如果没有捞出，则新建电子学生证，并绑定
                    if (notin && !string.IsNullOrWhiteSpace(imeiid))
                    {
                        var imeiResponse = await cosmosContainer.ReadItemStreamAsync(imeiid, new PartitionKey("Imei"));
                        if (imeiResponse.Status == 200)
                        {
                            Imei imeiDb = JsonDocument.Parse(imeiResponse.Content).RootElement.Deserialize<Imei>();
                            imeiDb.stuid = stuid;
                            imeiDb.school = schoolId;
                            await cosmosContainer.ReplaceItemAsync(imeiDb, imeiDb.id, new PartitionKey("Imei"));
                        }
                        else
                        {
                            Imei imei = new Imei { id = imeiid, code = "Imei", pk = "Imei", stuid = stuid, school = schoolId };
                            await cosmosContainer.CreateItemAsync(imei, new PartitionKey($"Imei"));
                        };
                    }
                    if (delete.Any())
                    {
                        await cosmosContainer.DeleteItemsStreamAsync(delete.Select(x => x.id).ToList(), "Imei");
                    }
                    break;
                case "delete":
                    if (imeis.Any()) {
                        await cosmosContainer.DeleteItemsStreamAsync(imeis.Select(x => x.id).ToList(), "Imei");
                    }
                    break;
            }
            
        }

        /// <summary>
        /// 單純建立單一學生
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="studCreateInfo"></param>
        public static async Task<bool> createStudent(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId, studCreateInfo studCreateInfo)
        {
            try
            {
                using var stream = new MemoryStream();
                using var writer = new Utf8JsonWriter(stream);

                writer.WriteStartObject();
                writer.WriteString("pk", $"Base");
                writer.WriteString("code", $"Base-{schoolId}");
                writer.WriteString("id", studCreateInfo.id);
                if (string.IsNullOrWhiteSpace(studCreateInfo.name)) writer.WriteNull("name");
                else writer.WriteString("name", studCreateInfo.name);
                if (string.IsNullOrWhiteSpace(studCreateInfo.gender)) writer.WriteNull("gender");
                else writer.WriteString("gender", studCreateInfo.gender);
                writer.WriteString("schoolId", schoolId);
                //20210713 huanghb add 增加学段
                if (string.IsNullOrWhiteSpace(studCreateInfo.periodId)) writer.WriteNull("periodId");
                else writer.WriteString("periodId", studCreateInfo.periodId);
                writer.WriteNumber("year", studCreateInfo.year);
                writer.WriteNumber("createTime", DateTimeOffset.UtcNow.ToUnixTimeMilliseconds());
                writer.WriteNull("picture");
                writer.WriteNull("mail");
                writer.WriteNull("mobile");
                writer.WriteNull("country");
                //Password，若沒給則使用學號當密碼
                string salt = Utils.CreatSaltString(8);
                string hashPw = string.IsNullOrWhiteSpace(studCreateInfo.pw)
                                    ? Utils.HashedPassword(studCreateInfo.id, salt)
                                    : Utils.HashedPassword(studCreateInfo.pw, salt);
                writer.WriteString("salt", salt);
                writer.WriteString("pw", hashPw);

                if (string.IsNullOrWhiteSpace(studCreateInfo.classId)) writer.WriteNull("classId");
                else writer.WriteString("classId", studCreateInfo.classId);
                if (string.IsNullOrWhiteSpace(studCreateInfo.no))
                {
                    writer.WriteNull("no");
                    writer.WriteNull("irs");
                }
                else
                {
                    writer.WriteString("no", studCreateInfo.no);
                    writer.WriteString("irs", studCreateInfo.no);
                }
                writer.WriteNull("groupId");
                writer.WriteNull("groupName");
                if (studCreateInfo.guardians.IsNotEmpty())
                {
                    writer.WriteStartArray("guardians");

                    foreach (var guardian in studCreateInfo.guardians)
                    {
                        writer.WriteStartObject();
                        writer.WriteString("relation", guardian.relation);
                        writer.WriteString("name", guardian.name);
                        writer.WriteString("mobile", guardian.mobile);
                        writer.WriteEndObject();
                    }
                    writer.WriteEndArray();

                    foreach (var studentGuardian in studCreateInfo.guardians) {
                        List<Guardian> guardians = new List<Guardian>();
                        string sql = $"select distinct value (c) from c   where  c.mobile='{studentGuardian.mobile}' ";
                        await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS,Constant.Student).GetItemQueryIterator<Guardian>(queryText: sql, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey("Base-Guardian") }))
                        {
                            guardians.Add(item);
                        }
                        if (guardians.Any())
                        {
                            guardians.ForEach(async x => {
                                GuardianStudent guardianStudent = x.students.Find(s => s.id.Equals(studCreateInfo.id) && s.code.Equals(schoolId));
                                if (guardianStudent == null)
                                {
                                    x.students.Add(new GuardianStudent {relation=studentGuardian.relation, type = 2, id = studCreateInfo.id, code = schoolId, name = studCreateInfo.name });
                                }
                                await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.Student).ReplaceItemAsync(x, x.id, new PartitionKey(x.code));
                            });
                        }
                        else
                        {
                            Guardian guardian = new Guardian
                            {
                                id = Guid.NewGuid().ToString(),
                                code = "Base-Guardian",
                                pk = "Guardian",
                                mobile = studentGuardian.mobile,
                                name = studentGuardian.name,
                                students = new List<GuardianStudent> { new GuardianStudent { relation=studentGuardian.relation, type = 2, id = studCreateInfo.id, code = schoolId, name = studCreateInfo.name } }
                            };
                            await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.Student).CreateItemAsync(guardian, new PartitionKey(guardian.code));
                        }
                    }
                }
                else
                {
                    writer.WriteStartArray("guardians");
                    writer.WriteStartObject();
                    writer.WriteEndObject();
                    writer.WriteEndArray();
                }
                writer.WriteEndObject();
                writer.Flush();
                
                var response = await _azureCosmos
                                .GetCosmosClient()
                                .GetContainer(Constant.TEAMModelOS, "Student")
                                .CreateItemStreamAsync(stream, new PartitionKey($"Base-{schoolId}"));
                //更新电子学生证、
                await upsertImei(studCreateInfo.id, studCreateInfo.imei, schoolId, "keep", _azureCosmos
                               .GetCosmosClient()
                               .GetContainer(Constant.TEAMModelOS, "Student"));
                if (response.Status == (int)HttpStatusCode.Created || response.Status == (int)HttpStatusCode.OK) return true;
                if (response.Status == (int)HttpStatusCode.Conflict) return false;
                else
                {
                    await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createStudent()\nCosmosDB Create response status = {response.Status}\nID:{studCreateInfo.id}", GroupNames.醍摩豆服務運維群組);
                    return false;
                }
                
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createStudent()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
                return false;
            }
        }

        /// <summary>
        /// 生成Class資料
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="classId"></param>
        /// <param name="className"></param>
        /// <param name="students"></param>
        /// <returns></returns>
        private static async Task<(string classId, string classNo, string className, string periodId, string gradeId, int classYear)> createClassInfo(
            AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option,
            string schoolId, string classId, string className, string classNo, string periodId, string gradeId, int classYear)
        {
            //組Class JSON
            try
            {
                string cId = classId;
                using var memoryStream = new MemoryStream();
                using var writer = new Utf8JsonWriter(memoryStream);
                writer.WriteStartObject();
                writer.WriteString("pk", "Class");
                writer.WriteString("code", $"Class-{schoolId}");
                //如果classId是空的，則生成一組GUID。
                if (string.IsNullOrWhiteSpace(classId))
                {
                    cId = Guid.NewGuid().ToString();
                    writer.WriteString("id", cId);
                }
                else writer.WriteString("id", classId);
                if (string.IsNullOrWhiteSpace(classNo)) writer.WriteNull("no");
                else writer.WriteString("no", classNo);
                writer.WriteNull("x");
                writer.WriteNull("y");
                if (string.IsNullOrWhiteSpace(className)) writer.WriteNull("name");
                else writer.WriteString("name", className);

                writer.WritePropertyName("teacher");
                writer.WriteStartObject();
                writer.WriteNull("id");
                writer.WriteNull("name");
                writer.WriteEndObject();

                //if (string.IsNullOrWhiteSpace(gradeId)) writer.WriteNull("gradeId");
                //else writer.WriteString("gradeId", gradeId);

                if (string.IsNullOrWhiteSpace(periodId)) writer.WriteNull("periodId");
                else writer.WriteString("periodId", periodId);
                writer.WriteNumber("year", classYear);
                writer.WriteNumber("createTime", DateTimeOffset.UtcNow.ToUnixTimeMilliseconds());
                writer.WriteNull("sn");
                writer.WriteString("style", "smart");
                writer.WriteString("openType", "1");
                writer.WriteString("scope", "school");
                writer.WriteEndObject();
                writer.Flush();

                var ret = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "School").CreateItemStreamAsync(memoryStream, new PartitionKey($"Class-{schoolId}"));
                if (ret.Status != (int)HttpStatusCode.Created)
                {
                    await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createClassInfo()\nStatus:{ret.Status}\nSchoolId:{schoolId},ClassId:{classId}", GroupNames.醍摩豆服務運維群組);
                }
                return (cId, classNo, className, periodId, gradeId, classYear);
            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createClassInfo()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createClassInfo()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
            }
            return (null, null, null, null, null, 0);
        }

        /// <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(
              AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option,
            string schoolId, string byNameOrId = null, string byPeriod = null, string byGrade = null, string byClassId = null, int offset = -1, int limit = -1, string token = default)
        {
            try
            {
                //以學校學生角度去抓資料
                List<(string id, string name, string picture, int year)> listStudent = new List<(string id, string name, string picture, int year)>();
                string queryText = $"SELECT c.id, c.name, c.picture, c.year FROM c WHERE c.code = 'Base-{schoolId}'";

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

                //檢查是否有接續token及是否要在sql語法內多增加offset及limit
                if (string.IsNullOrWhiteSpace(token))
                {
                    token = default;
                    if (offset != -1 && limit != -1) queryText = $"{queryText} OFFSET {offset} LIMIT {limit}";
                }

                //回傳用ContinuationToken
                string continuationToken = string.Empty;
                //進行學生資料的查詢
                await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student")
                   .GetItemQueryStreamIterator(
                       queryText: queryText,
                       continuationToken: token,
                       requestOptions: new QueryRequestOptions()
                       { PartitionKey = new PartitionKey($"Base-{schoolId}") }))
                {
                    continuationToken = item.GetContinuationToken();
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                        while (accounts.MoveNext())
                        {
                            JsonElement account = accounts.Current;
                            listStudent.Add((account.GetProperty("id").GetString(), account.GetProperty("name").GetString(), account.GetProperty("picture").GetString(), account.GetProperty("year").GetInt32()));
                        }
                    }
                    //單筆查詢上限為100條，所以查完一次即返回，並且給接續token。
                    break;
                }

                //查學生所屬的教室及座號
                List<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(Constant.TEAMModelOS, "School")
                   .GetItemQueryStreamIterator(
                   queryText: queryText,
                   //continuationToken: token,
                   requestOptions: new QueryRequestOptions()
                   { PartitionKey = new PartitionKey($"Class-{schoolId}") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        var classrooms = json.RootElement.GetProperty("Documents").EnumerateArray();
                        while (classrooms.MoveNext())
                        {
                            JsonElement classroom = classrooms.Current;

                            var studs = classroom.GetProperty("students").EnumerateArray();
                            while (studs.MoveNext())
                            {
                                JsonElement stud = studs.Current;
                                string id = stud.GetProperty("id").GetString();
                                //整理出前端所需的資訊
                                var tmp = listStudent
                                    .Where(o => o.id.Equals(id, StringComparison.Ordinal))
                                    .Select(o =>
                                        new
                                        {
                                            o.id,
                                            o.name,
                                            o.picture,
                                            o.year,
                                            no = stud.GetProperty("no").GetString(),
                                            gradeId = classroom.GetProperty("gradeId").GetString(),
                                            className = classroom.GetProperty("name").GetString()
                                        });
                                ret.AddRange(tmp);
                                //刪除已整理完的ID
                                listStudent.RemoveAll(o => o.id.Equals(id, StringComparison.Ordinal));
                            }
                        }
                    }
                }
                var notJoinClassStuds = listStudent.Select(o =>
                                        new
                                        {
                                            o.id,
                                            o.name,
                                            o.picture,
                                            o.year,
                                            no = (string)null,
                                            gradeId = (string)null,
                                            className = (string)null
                                        });
                ret.AddRange(notJoinClassStuds);
                return (ret, continuationToken);
            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
            }
            return (null, null);
        }

        /// <summary>
        /// 取得該學校的所有學生。
        /// </summary>
        /// <param name="schoolId"></param>
        /// <returns> [{id,name,picture,year,no,classId,classNo,className,gradeId,periodId},{id,name,picture,..}..]</returns>
        public static async Task<List<object>> getAllStudent(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId,int inyear ,int graduate=0 )
        {
            try
            {
                //TODO : 進階查詢選項調整、部分地方可用並行處理
                //以學校學生角度去抓資料
                Dictionary<string, List<(string id, string name, string picture, int year, string no, string periodId, string irs,string imei,string gender,int graduate, List<StudentGuardian> guardians)>> dicClassStuds =
                    new Dictionary<string, List<(string id, string name, string picture, int year, string no, string periodId, string irs, string imei, string gender, int graduate, List<StudentGuardian> guardians)>>();
                List<(string id, string name, string picture, int year, string no, string periodId, string irs, string imei, string gender, int graduate, List<StudentGuardian> guardians)> notJoinClassStuds = 
                    new List<(string id, string name, string picture, int year, string no, string periodId, string irs, string imei, string gender, int graduate, List<StudentGuardian> guardians)>();


                List<Imei> imeis= new List<Imei>();
                string imeiQueryText = $"SELECT  *  FROM c WHERE c.school = '{schoolId}'";
                await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student")
                   .GetItemQueryIterator<Imei>(queryText:imeiQueryText,requestOptions:new QueryRequestOptions { PartitionKey= new PartitionKey("Imei")})){
                    imeis.Add(item);
                }
                List<Student> graduate_students = new List<Student>();
                List<Class> school_classes = new List<Class>();
                string queryText = "";
                if (graduate == 0)
                {
                    queryText = $"SELECT  *  FROM c WHERE c.code = 'Base-{schoolId}' and (c.graduate = 0 or IS_DEFINED(c.graduate) = false )";
                }
                else {
                    queryText = $"SELECT  *  FROM c WHERE c.code = 'Base-{schoolId}' and  c.graduate = 1 and c.year ={inyear} ";


                    ///查询当年的毕业班
                    string sql = sql = $"SELECT value c FROM c where  c.graduate =1 and c.year ={inyear}"; ;
                   
                    var client = _azureCosmos.GetCosmosClient();
                    await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<Class>
                    (queryText: sql,
                       requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{schoolId}") }))
                    {
                        school_classes.Add(item);
                    }
                    //如果已经毕业的班级不为空。
                    if (school_classes.IsNotEmpty()) {
                        string classSql = $"SELECT  value c    FROM c WHERE c.code = 'Base-{schoolId}' and  c.graduate = 1  and c.classId in(   {string.Join(",", school_classes.Select(x => $"'{x.id}'"))}  )";
                        await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student").GetItemQueryIterator<Student>( queryText: classSql,
                        requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{schoolId}")})) 
                        {
                            graduate_students.Add(item);
                        }
                    }
                }
                 

                //回傳用ContinuationToken
                string continuationToken = string.Empty;
                var container = _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student");

                //進行學生資料的查詢 TEAMModelOS-Student
                await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student")
                   .GetItemQueryStreamIterator(
                       queryText: queryText,
                       requestOptions: new QueryRequestOptions()
                       { PartitionKey = new PartitionKey($"Base-{schoolId}"), MaxItemCount = -1 }))
                {
                    continuationToken = item.GetContinuationToken();
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        List<(string id, string name, string picture, int year, string no, List<StudentGuardian> guardians)> students = new List<(string id, string name, string picture, int year, string no, List<StudentGuardian> guardians)>();

                        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                        while (accounts.MoveNext())
                        {
                            JsonElement acc = accounts.Current;

                            string classId = acc.GetProperty("classId").GetString();
                            acc.TryGetProperty("irs", out JsonElement irs);
                            acc.TryGetProperty("guardians", out JsonElement _guardians);
                            List<StudentGuardian> guardians = new List<StudentGuardian>();
                          
                            if (_guardians.ValueKind.Equals(JsonValueKind.Array)) {
                                guardians= _guardians.Deserialize<List<StudentGuardian>>();
                                if (guardians.Any()) {
                                    guardians= guardians.FindAll(x => !string.IsNullOrWhiteSpace(x.mobile)) ;
                                }
                            }

                            var imeiObj= imeis.Find(x => x.stuid.Equals(acc.GetProperty("id").GetString()));
                            if (string.IsNullOrWhiteSpace(classId))
                            {
                                notJoinClassStuds.Add(
                                        (
                                            acc.GetProperty("id").GetString(),
                                            acc.GetProperty("name").GetString(),
                                            acc.GetProperty("picture").GetString(),
                                            acc.GetProperty("year").GetInt32(),
                                            acc.GetProperty("no").GetString(),
                                            acc.TryGetProperty("periodId", out JsonElement _periodId) && _periodId.ValueKind.Equals(JsonValueKind.String) ? _periodId.GetString() : null,
                                            $"{irs}",
                                            imeiObj?.id,//imei
                                            acc.TryGetProperty("gender", out JsonElement _gender) && _gender.ValueKind.Equals(JsonValueKind.String) ? _gender.GetString() : null,
                                              acc.TryGetProperty("graduate", out JsonElement _graduate) && _graduate.ValueKind.Equals(JsonValueKind.Number) ? int.Parse($"{_graduate}"): 0,
                                            guardians
                                        )
                                    );
                            }
                            else
                            {
                                if (dicClassStuds.ContainsKey(classId))
                                {
                                    dicClassStuds[classId].Add(
                                            (
                                                acc.GetProperty("id").GetString(),
                                                acc.GetProperty("name").GetString(),
                                                acc.GetProperty("picture").GetString(),
                                                acc.GetProperty("year").GetInt32(),
                                                acc.GetProperty("no").GetString(), acc.TryGetProperty("periodId", out JsonElement _periodId) && _periodId.ValueKind.Equals(JsonValueKind.String) ? _periodId.GetString() : null,
                                                $"{irs}",
                                                imeiObj?.id,//imei
                                                acc.TryGetProperty("gender", out JsonElement _gender) && _gender.ValueKind.Equals(JsonValueKind.String) ? _gender.GetString() : null,
                                                  acc.TryGetProperty("graduate", out JsonElement _graduate) && _graduate.ValueKind.Equals(JsonValueKind.Number) ? int.Parse($"{_graduate}") : 0,
                                                guardians
                                            )
                                        );
                                }
                                else
                                {
                                    dicClassStuds.Add(classId,
                                            new List<(string id, string name, string picture, int year, string no, string periodId, string irs,string imei,string gender,int graduate, List<StudentGuardian> guardians)>()
                                            {
                                                (
                                                    acc.GetProperty("id").GetString(),
                                                    acc.GetProperty("name").GetString(),
                                                    acc.GetProperty("picture").GetString(),
                                                    acc.GetProperty("year").GetInt32(),
                                                    acc.GetProperty("no").GetString(),  acc.TryGetProperty("periodId",out JsonElement _periodId)&&  _periodId.ValueKind.Equals(JsonValueKind.String) ? _periodId.GetString() : null,
                                                    $"{irs}"  ,
                                                    imeiObj?.id,//imei
                                                    acc.TryGetProperty("gender", out JsonElement _gender) && _gender.ValueKind.Equals(JsonValueKind.String) ? _gender.GetString() : null,
                                                      acc.TryGetProperty("graduate", out JsonElement _graduate) && _graduate.ValueKind.Equals(JsonValueKind.Number) ? int.Parse($"{_graduate}"): 0,
                                                    guardians
                                                )
                                            }
                                        );
                                }
                            }
                        }
                    }
                }

                //查學生所屬的教室及座號
                List<object> ret = new List<object>();

                //查教室的資訊，用以取得gradeId,periodId資訊。
                var classInfos = await getClassInfoUseId(_azureCosmos, _dingDing, _option, schoolId, dicClassStuds.Keys.ToList());

                //輪循所有教室學生的資料
                foreach (var classStud in dicClassStuds)
                {
                    string classId = null, classNo = null, className = null, gradeId = null, periodId = null;
                    int classYear = -1;
                    if (classInfos.ContainsKey(classStud.Key))
                    {
                        classId = classInfos[classStud.Key].GetProperty("id").GetString();
                        classNo = classInfos[classStud.Key].GetProperty("no").GetString();
                        className = classInfos[classStud.Key].GetProperty("name").GetString();
                        periodId = classInfos[classStud.Key].TryGetProperty("periodId", out JsonElement _periodId) && _periodId.ValueKind.Equals(JsonValueKind.String) ? _periodId.GetString() : null;
                        if (classInfos[classStud.Key].TryGetProperty("year", out JsonElement year))
                        {
                            if (year.ValueKind.Equals(JsonValueKind.Number))
                            {
                                classYear = classInfos[classStud.Key].GetProperty("year").GetInt32();
                            }
                        }
                    }

                    var tmp = classStud.Value.Select(o =>
                                     new
                                     {
                                         o.id,
                                         o.name,
                                         o.picture,
                                         o.year,
                                         o.no,
                                         classId,
                                         classNo,
                                         className,
                                         gradeId,
                                         periodId = string.IsNullOrEmpty(periodId) ? o.periodId : periodId,
                                         classYear,
                                         irs = o.irs,
                                         imei=o.imei,
                                         gender = o.gender,
                                         graduate=   o.graduate,
                                         guardians =o.guardians,
                                     });
                    ret.AddRange(tmp);
                }

                //彙整沒有加入教室的學生
                notJoinClassStuds.ForEach(o => ret.Add(
                    new
                    {
                        o.id,
                        o.name,
                        o.picture,
                        o.year,
                        o.no,
                        classId = (string)null,
                        classNo = (string)null,
                        className = (string)null,
                        gradeId = (string)null,
                        o.periodId,
                        classYear = -1,
                        irs = o.irs,
                        imei = o.imei,
                        gender = o.gender,
                        guardians =o.guardians,
                    }));

                graduate_students.ForEach(o => {
                    var imeiObj = imeis.Find(x => x.stuid.Equals(o.id));
                    Class stuClass = school_classes.Find(x => x.id.Equals(o.classId));
                    ret.Add(
                    new
                    {
                        o.id,
                        o.name,
                        o.picture,
                        o.year,
                        o.no,
                        classId = stuClass?.id,
                        classNo = stuClass?.no,
                        className = stuClass?.name,
                        gradeId = stuClass?.gradeId,
                        o.periodId,
                        classYear = stuClass?.year,
                        irs = o.irs,
                        imei = imeiObj?.id,
                        gender = o.gender,
                        guardians = o.guardians,
                    });
                });
                return ret;
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getStudents()\n{ex.Message}\n{ex.StackTrace},", GroupNames.醍摩豆服務運維群組);
            }
            return null;
        }

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

                await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "School")
                    .GetItemQueryStreamIterator(queryText: queryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{schoolId}") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        JsonElement.ArrayEnumerator accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                        while (accounts.MoveNext())
                        {
                            JsonElement account = accounts.Current;
                            string cId = account.GetProperty("id").GetString();
                            var students = account.GetProperty("students").Clone();
                            listStudent.Add(cId, students);
                        }
                    }
                }
                return listStudent;
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getClassStudentAsync()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
            }
            return null;
        }

        /// <summary>
        /// 刪除學生，非透過批量刪除方法。
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="students"></param>
        /// <returns></returns>
        public static async Task<List<string>> deleteStudents(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId, JsonElement.ArrayEnumerator students)
        {
            List<string> sucIds = new List<string>();
            try
            {
                var exceptions = new List<Exception>();
                List<GroupList> scGroupLists = new List<GroupList>();
                List<GroupList> teGroupLists = new List<GroupList>();
                var container = _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student");
                while (students.MoveNext())
                {
                    string id = string.Empty;
                    try
                    {
                       
                        JsonElement student = students.Current;
                        id = student.GetProperty("id").GetString();
                        var ret = await container.DeleteItemStreamAsync(id, new PartitionKey($"Base-{schoolId}"));
                        await upsertImei(id, null, schoolId, "delete", container);
                        if (ret.Status == (int)HttpStatusCode.NoContent) sucIds.Add(id);
                        await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<GroupList>(queryText: $"select value(c) from c join A0 in c.members where A0.id = '{id}' and A0.code='{schoolId}' ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"GroupList-{schoolId}") }))
                        {
                            scGroupLists.Add(item);
                        }
                        await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryIterator<GroupList>(queryText: $"select value(c) from c join A0 in c.members where A0.id = '{id}' and A0.code='{schoolId}'  ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"GroupList") }))
                        {
                            teGroupLists.Add(item);
                        }
                        if (scGroupLists.Count > 0)
                        {
                            foreach (GroupList stuList in scGroupLists)
                            {
                                for (int j = 0; j < stuList.members.Count; j++)
                                {
                                    if (id.Equals(stuList.members[j].id) && stuList.members[j].code.Equals(schoolId))
                                    {
                                        stuList.members.RemoveAt(j);
                                        stuList.scount -= 1;
                                        break;
                                    }
                                }
                                await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "School").ReplaceItemAsync(stuList, stuList.id, new PartitionKey(stuList.code));
                            }
                        }
                        if (teGroupLists.Count > 0)
                        {
                            foreach (GroupList stuList in teGroupLists)
                            {
                                for (int j = 0; j < stuList.members.Count; j++)
                                {
                                    if (id.Equals(stuList.members[j].id) && stuList.members[j].code.Equals(schoolId))
                                    {
                                        stuList.members.RemoveAt(j);
                                        stuList.scount -= 1;
                                        break;
                                    }
                                }
                                await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Teacher").ReplaceItemAsync(stuList, stuList.id, new PartitionKey(stuList.code));
                            }
                        }
                    }
                    catch (CosmosException ex)
                    {
                        exceptions.Add(ex);
                    }
                    catch (Exception ex)
                    {
                        exceptions.Add(ex);
                    }
                }
                if (exceptions.Count == 0) return sucIds;
                else if (exceptions.Count > 1) throw new AggregateException(exceptions);
                else if (exceptions.Count == 1) throw exceptions.Single();
            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/deleteStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/deleteStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
            }
            return sucIds;
        }

        /// <summary>
        /// 將學生基本資料內的classId、no、groupId及groupName清為null。
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="students">["id1","id2",...]</param>
        /// <returns></returns>
        public static async Task<(List<string> studs, List<string> nonexistentIds, List<string> errorIds)> removeStudentClassInfo(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId, JsonElement.ArrayEnumerator students)
        {
            //紀錄輸入的學生
            List<string> impStuds = new List<string>();
            //紀錄更新成功的學生
            List<string> sucStuds = new List<string>();
            //記錄沒查到的學生
            List<string> nonexistentIds = new List<string>();
            //紀錄更新出錯的學生
            List<string> errorIds = new List<string>();

            //整理輸入的學生資訊
            while (students.MoveNext())
            {
                JsonElement student = students.Current;
                impStuds.Add(student.GetString());
            }

            if (impStuds.Count == 0) return (null, null, null);

            string queryText = $"SELECT VALUE c FROM c WHERE c.id IN ({string.Join(",", impStuds.Select(o => $"'{o}'"))})";
            await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student")
                            .GetItemQueryStreamIterator(
                                queryText: queryText,
                                requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{schoolId}") }))
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                {
                    JsonElement.ArrayEnumerator docs = json.RootElement.GetProperty("Documents").EnumerateArray();
                    while (docs.MoveNext())
                    {
                        JsonElement doc = docs.Current;

                        doc.TryGetProperty("id", out var tmpId);
                        var id = tmpId.GetString();

                        using var stream = new MemoryStream();
                        using var writer = new Utf8JsonWriter(stream);

                        writer.WriteStartObject();
                        foreach (var element in doc.EnumerateObject())
                        {

                            //將教室相關欄位清空
                            switch (true)
                            {
                                case bool _ when element.Name.Equals("classId", StringComparison.Ordinal):
                                    writer.WriteNull("classId");
                                    break;
                                case bool _ when element.Name.Equals("no", StringComparison.Ordinal):
                                    writer.WriteNull("no");
                                    break;
                                case bool _ when element.Name.Equals("groupId", StringComparison.Ordinal):
                                    writer.WriteNull("groupId");
                                    break;
                                case bool _ when element.Name.Equals("groupName", StringComparison.Ordinal):
                                    writer.WriteNull("groupName");
                                    break;
                                case bool _ when element.Name.Equals("irs", StringComparison.Ordinal):
                                    writer.WriteNull("irs");
                                    break;
                                case bool _ when element.Name.StartsWith("_", StringComparison.Ordinal):
                                    break;
                                default:
                                    element.WriteTo(writer);
                                    break;
                            }
                        }
                        writer.WriteEndObject();
                        writer.Flush();
                        var ret = await _azureCosmos
                                  .GetCosmosClient()
                                  .GetContainer(Constant.TEAMModelOS, "Student")
                                  .ReplaceItemStreamAsync(stream, id, new PartitionKey($"Base-{schoolId}"));

                        if (ret.Status == (int)HttpStatusCode.OK)
                        {
                            sucStuds.Add(id);
                        }
                        else
                        {
                            impStuds.Remove(id);
                            errorIds.Add(id);
                            await _dingDing.SendBotMsg(
                                $"IES5，{_option.Location}，StudentController/removeStudentClassInfo()，CosmosDB response:{ret.Status}\nBase-{schoolId}，id:{id}",
                                GroupNames.醍摩豆服務運維群組);
                        }
                    }
                }
            }
            //將impStuds內的資料移除sucStuds及errorIds，所得的結果就是不存在於資料庫的id。
            sucStuds.ForEach(o => impStuds.Remove(o));
            errorIds.ForEach(o => impStuds.Remove(o));
            return (sucStuds, impStuds, errorIds);
        }

        /// <summary>
        /// 取得教室資訊，使用classId進行查詢。
        /// </summary>
        /// <returns></returns>
        private static async Task<Dictionary<string, JsonElement>> getClassInfoUseId(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, 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(Constant.TEAMModelOS, "School")
                        .GetItemQueryStreamIterator(queryText: queryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{schoolId}") }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.ContentStream);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        {
                            var classInfos = json.RootElement.GetProperty("Documents").EnumerateArray();
                            while (classInfos.MoveNext())
                            {
                                JsonElement account = classInfos.Current;
                                string id = account.GetProperty("id").GetString();
                                dicClassInfo.Add(id, account.Clone());
                            }
                        }
                    }
                    return dicClassInfo;
                }
                else return null;

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

        /// <summary>
        /// 取得教室資訊，使用classNo進行查詢。
        /// </summary>
        /// <returns></returns>
        private static async Task<Dictionary<string, Class>> getClassInfoUseNo(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId, Dictionary<string, (string className, string periodId, int year, string no)> classNos)
        {
            try
            {
                Dictionary<string, Class> dicClassInfo = new Dictionary<string, Class>();
                if (!(classNos == null || classNos.Count == 0))
                {

                    foreach (var key in classNos.Keys)
                    {
                        string queryText = $"SELECT * FROM c WHERE c.code = 'Class-{schoolId}' AND c.no='{classNos[key].no}' and c.year={classNos[key].year} and c.periodId='{classNos[key].periodId}' ";
                        await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "School")
                       .GetItemQueryIterator<Class>(queryText: queryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{schoolId}") }))
                        {
                            dicClassInfo[item.id] = item;
                            item.name = classNos[key].className;
                            await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "School").ReplaceItemAsync<Class>(item, item.id, new PartitionKey(item.code));
                        }
                    }
                    return dicClassInfo;
                }
                else return null;

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

        /// <summary>
        /// 批量更新學生資訊，目前支持更新姓名、密碼、座號、性別及教室id，匯入時ClassId為必填。
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="students"></param>
        /// <returns></returns>
        public static async Task<(List<object> studs, Dictionary<string, List<string>> classDuplNos, List<string> nonexistentIds, List<string> errorIds,
            Dictionary<string, List<string>> errorNos, List<string> errorClassId)>
            updateStudents(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId, JsonElement.ArrayEnumerator students)
        {
            try
            {
                //整理輸入的資料->檢查輸入資料有沒有重複座號->取得欲加入的教室資訊->查詢學生並將資料更新並寫入

                //Key:id Value:學生基本資訊
                var studentInfos
                    = new Dictionary<string, (string salt, string pw, string name, int year, string picture, string gender, string mail, string mobile, 
                    string classId, string no, string periodId, string irs,string imei, List<StudentGuardian> guardians)>();
                //用於進行座號是否重複查詢時使用
                var classStuds
                    = new Dictionary<string, List<(string id, string salt, string pw, string name, int year, string picture, string gender, string mail, string mobile,
                    string classId, string no, string periodId)>>();
                //紀錄教室"輸入"的學生座號是否有重複
                var impClassDuplNo = new Dictionary<string, List<string>>();
                //紀錄不存在的學生id
                var nonexistentIds = new List<string>();
                //紀錄跟現有雲端學生座號重複的
                var errorNos = new Dictionary<string, List<string>>();
                //紀錄處理錯誤的id，cosmosdb寫入時錯誤等...
                var errorIds = new List<string>();
                //紀錄沒找到的classId
                var errorClassId = new List<string>();
                //紀錄輸出結果
                var retStuds = new List<object>();

                //整理輸入資料
                while (students.MoveNext())
                {
                    JsonElement student = students.Current;
                    if (student.TryGetProperty("id", out var id))
                    {
                        //確認是否有id欄位，並且確認是否有給pw欄位，若無給或是null empty等，則使用id當密碼。
                        if (!string.IsNullOrWhiteSpace(id.GetString()))
                        {
                            string salt = null,
                                   pw = null,
                                   name = null,
                                   gender = null,
                                   mail = null,
                                   mobile = null,
                                   classId = null,
                                   periodId = null,
                                   irs = null,
                                   imei = null,
                                   no = null;

                            List<StudentGuardian> guardians = null;
                            int year = 0;
                            //有給pw欄位才進行處理
                            if (student.TryGetProperty("pw", out var tmpPw) && !"******".Equals($"{tmpPw}"))
                            {
                                var response = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student").ReadItemStreamAsync(id.GetString(), new PartitionKey($"Base-{schoolId}"));
                                if (response.Status == 200)
                                {
                                    var rjson = await JsonDocument.ParseAsync(response.ContentStream);
                                    rjson.RootElement.TryGetProperty("salt", out JsonElement _salt);
                                    salt = $"{_salt}";
                                }
                                if (string.IsNullOrWhiteSpace(salt))
                                {
                                    salt = Utils.CreatSaltString(8);
                                }
                                pw = !string.IsNullOrWhiteSpace(tmpPw.GetString())
                                    ? Utils.HashedPassword(tmpPw.GetString(), salt)
                                    : Utils.HashedPassword(id.GetString(), salt);
                            }
                            if (student.TryGetProperty("name", out var tmpName)) name = tmpName.GetString();
                            if (student.TryGetProperty("gender", out var tmpGender)) gender = tmpGender.GetString();
                            if (student.TryGetProperty("mail", out var tmpMail)) mail = tmpMail.GetString();
                            if (student.TryGetProperty("mobile", out var tmpMobile)) mobile = tmpMobile.GetString();
                            if (student.TryGetProperty("year", out var tmpYear)) year = tmpYear.GetInt32();
                            if (student.TryGetProperty("periodId", out var tmpperiodId)) periodId = tmpperiodId.GetString();
                            if (student.TryGetProperty("irs", out var tmpIrs)) irs = tmpIrs.GetString();
                            if (student.TryGetProperty("imei", out var tmpImei)) imei = tmpImei.GetString();
                            if (student.TryGetProperty("classId", out var tmpclassId)) classId = tmpclassId.GetString();
                            else
                            {
                                errorClassId.Add(id.GetString());
                                continue;
                            }
                            if (student.TryGetProperty("guardians", out var _guardians) && _guardians.ValueKind.Equals(JsonValueKind.Array) ) 
                            {
                                guardians = _guardians.Deserialize<List<StudentGuardian>>();
                            }
                            //如果有給該欄位，且是給空的，代表要清空
                            if (student.TryGetProperty("no", out var tmpNo)) no = tmpNo.GetString();

                            if (!studentInfos.ContainsKey(id.GetString()))
                            {
                                //如果有給classId且是給空的，則也將no設為空，後續才能將no欄位清空。
                                if (classId != null && classId.Length == 0) no = string.Empty;

                                //classId => 沒給欄位(null) 有給欄位("") 但更新一定得給教室?
                                if (classId == null)
                                {
                                    classId = "";
                                }
                                if (classStuds.ContainsKey(classId))
                                {
                                    classStuds[classId].Add((id.GetString(), salt, pw, name, year, null, gender, null, null, classId, no, periodId));
                                }
                                else
                                {
                                    classStuds.Add(
                                        classId,
                                        new List<(string id, string salt, string pw, string name, int year, string picture, string gender, string mail, string mobile, string classId, string no, string periodId)>()
                                        { (id.GetString(), salt, pw, name, year, null, gender, null, null, classId, no,periodId) });
                                }

                                //picture,mail,mobile暫不支持批量更新
                                studentInfos.Add(id.GetString(), (salt, pw, name, year, null, gender, null, null, classId, no, periodId, irs,imei, guardians));

                                //先將id加進去後面再做刪除動作
                                nonexistentIds.Add(id.GetString());
                            }
                        }
                    }
                }

                //檢查所有輸入的班級資料內，學生座號是否有重複。
                for (int ii = 0; ii <= classStuds.Values.Count - 1; ii++)
                {
                    var duplicateNo = classStuds.ElementAt(ii).Value.GroupBy(o => o.no).Where(o => o.Count() > 1).Select(o => o.Key).ToList();
                    duplicateNo.Remove("");
                    duplicateNo.Remove(null);
                    var wrongStuds = classStuds.ElementAt(ii).Value.Where(o => duplicateNo.Contains(o.no)).Select(o => o).ToList();
                    impClassDuplNo.Add(classStuds.ElementAt(ii).Key, wrongStuds.Select(o => o.id).ToList());

                    wrongStuds.ForEach(o => { classStuds.ElementAt(ii).Value.Remove(o); nonexistentIds.Remove(o.id); });
                    classStuds[classStuds.ElementAt(ii).Key] = classStuds.ElementAt(ii).Value;
                }

                //查詢欲加入的教室資訊。
                var classInfo = await getClassInfoUseId(_azureCosmos, _dingDing, _option, schoolId, classStuds.Keys.ToList());

                //準備查詢db資料
                CosmosContainer cosmosContainer = _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student");

                //查要移除教室或是沒加入教室的學生

                foreach (var item in classStuds)
                {
                    //如果沒有任何學生要更新，則跳過該間教室。
                    if (item.Value.Count == 0) continue;

                    string classId = null, className = null, classNo = null, gradeId = null, periodId = null;

                    //如果教室不存在的話(填錯教室之類的狀況)，則記錄教室的id及學生id。

                    if (classInfo.ContainsKey(item.Key))
                    {
                        classId = item.Key;
                        className = classInfo[item.Key].GetProperty("name").GetString();
                        classNo = classInfo[item.Key].GetProperty("no").GetString();
                        //gradeId = classInfo[item.Key].GetProperty("gradeId").GetString(); 此欄位已不使用
                        periodId = classInfo[item.Key].GetProperty("periodId").GetString();
                    }
                    else if (item.Key.Length == 0)
                    {
                    }
                    else
                    {
                        //沒查到有該間教室的資訊，故將該間教室的ID及學生資料清單記起來，並且跳過不處理該資料。
                        errorClassId.Add(item.Key);
                        item.Value.ForEach(o => nonexistentIds.Remove(o.id));
                        continue;
                    }

                    //檢查座號是否有重複
                    //若只是改基本資料，該處還是會查到相同的座號。
                    var sutdNos = item.Value.Select(o => o.no).ToList();
                    var existNos = await checkStudNo(_azureCosmos, _dingDing, _option, schoolId, item.Key, sutdNos);

                    //更新並寫入學生資料
                    if (item.Value.Count != 0)
                    {
                        //查學生的基本資料(該間教室全部的學生)
                        string queryText = $"SELECT * FROM c WHERE c.id IN ({string.Join(",", item.Value.Select(o => $"'{o.id}'"))})";

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

                        await foreach (Response responseItem in cosmosContainer
                            .GetItemQueryStreamIterator(
                                queryText: queryText,
                                requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{schoolId}") }))
                        {
                            using var json = await JsonDocument.ParseAsync(responseItem.ContentStream);
                            if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                            {
                                var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                                while (accounts.MoveNext())
                                {
                                    JsonElement account = accounts.Current;
                                    string id = $"{ account.GetProperty("id")}";
                                    nonexistentIds.Remove(id);
                                    //舊的座號，基本上不會重複，但可能會是空的
                                    string no = $"{account.GetProperty("no")}";
                                    account.TryGetProperty("irs", out JsonElement irsjson);
                                    string irs = $"{irsjson}";
                                    //用來記錄最後更改完的資料
                                    (string salt, string pw, string name, int year, string picture, string gender, string mail, string mobile, string classId, string no, string periodId, string irs ,string imei, List<StudentGuardian> guardians) tmpData
                                     = (studentInfos[id].salt, studentInfos[id].pw, studentInfos[id].name, studentInfos[id].year, studentInfos[id].picture, studentInfos[id].gender, studentInfos[id].mail, studentInfos[id].mobile, studentInfos[id].classId, studentInfos[id].no, studentInfos[id].periodId, 
                                     studentInfos[id].irs, studentInfos[id].imei, studentInfos[id]. guardians);

                                    bool isUpPwDone = false;
                                    bool isWrong = false;

                                    //開始組Json
                                    using var memoryStream = new MemoryStream();
                                    using var writer = new Utf8JsonWriter(memoryStream);
                                    writer.WriteStartObject();
                                    foreach (var element in account.EnumerateObject())
                                    {
                                        if (isWrong) break;
                                        switch (true)
                                        {
                                            case bool _ when element.Name.Equals("name", StringComparison.Ordinal):
                                                if (string.IsNullOrWhiteSpace(studentInfos[id].name))
                                                {
                                                    element.WriteTo(writer);
                                                    tmpData.name = element.Value.GetString();
                                                }
                                                else
                                                {
                                                    writer.WriteString("name", studentInfos[id].name);
                                                }
                                                break;
                                            case bool _ when element.Name.Equals("pw", StringComparison.Ordinal):
                                            case bool _ when element.Name.Equals("salt", StringComparison.Ordinal):
                                                if (!isUpPwDone && !string.IsNullOrWhiteSpace(studentInfos[id].salt) && !string.IsNullOrWhiteSpace(studentInfos[id].pw))
                                                {
                                                    writer.WriteString("salt", studentInfos[id].salt);
                                                    writer.WriteString("pw", studentInfos[id].pw);
                                                    isUpPwDone = true;
                                                }
                                                break;
                                            case bool _ when element.Name.Equals("periodId", StringComparison.Ordinal):
                                                if (string.IsNullOrWhiteSpace(studentInfos[id].periodId))
                                                {
                                                    element.WriteTo(writer);
                                                    tmpData.periodId = element.Value.GetString();
                                                }
                                                else
                                                {
                                                    writer.WriteString("periodId", studentInfos[id].periodId);
                                                }
                                                break;
                                            case bool _ when element.Name.Equals("gender", StringComparison.Ordinal):
                                                if (string.IsNullOrWhiteSpace(studentInfos[id].gender))
                                                {
                                                    element.WriteTo(writer);
                                                    tmpData.gender = element.Value.GetString();
                                                }
                                                else
                                                {
                                                    writer.WriteString("gender", studentInfos[id].gender);
                                                }
                                                break;
                                            case bool _ when element.Name.Equals("year", StringComparison.Ordinal):
                                                if (studentInfos[id].year == 0)
                                                {
                                                    element.WriteTo(writer);
                                                    tmpData.year = element.Value.GetInt32();
                                                }
                                                else
                                                {
                                                    writer.WriteNumber("year", studentInfos[id].year);
                                                }
                                                break;
                                            case bool _ when element.Name.Equals("classId", StringComparison.Ordinal):
                                                if (studentInfos[id].classId != null && studentInfos[id].classId.Length == 0)
                                                {
                                                    writer.WriteNull("classId");
                                                    writer.WriteNull("groupId");
                                                    writer.WriteNull("groupName");
                                                    tmpData.classId = null;
                                                }
                                                else if (string.IsNullOrWhiteSpace(studentInfos[id].classId))
                                                {
                                                    element.WriteTo(writer);
                                                    tmpData.classId = element.Value.GetString();
                                                }
                                                else
                                                {
                                                    writer.WriteString("classId", studentInfos[id].classId);
                                                }
                                                break;
                                            case bool _ when element.Name.Equals("no", StringComparison.Ordinal):
                                                //移除座號的話會給空的
                                                if (studentInfos[id].no != null && studentInfos[id].no.Length == 0)
                                                {
                                                    writer.WriteNull("no");
                                                    tmpData.no = null;
                                                }
                                                else if (string.IsNullOrWhiteSpace(studentInfos[id].no))
                                                {
                                                    element.WriteTo(writer);
                                                    tmpData.no = element.Value.GetString();
                                                    //#2769 C343 学生管理--导入学生名单，IRS编号默认与座号一致
                                                    tmpData.irs=element.Value.GetString();
                                                }
                                                else
                                                {
                                                    //如果要更新的座號，跟已存在的座號相同，則不進行更新。
                                                    //沒有設定過舊no，或舊座號與新座號不同，則要進行重複座號的檢查。 舊no=null or 舊no!=新no

                                                    //if (string.IsNullOrWhiteSpace(no) || (!string.IsNullOrWhiteSpace(no) && !no.Equals(studentInfos[id].no)))
                                                    //{
                                                    //    //如果有檢查到新座號和舊座號重複
                                                    //    if (existNos.Any(o => o.Item2.Contains(studentInfos[id].no))) //.Contains(studentInfos[id].no))
                                                    //    {
                                                    //        if (errorNos.ContainsKey(id))
                                                    //        {
                                                    //            errorNos[id].Add(studentInfos[id].no);
                                                    //        }
                                                    //        else
                                                    //        {
                                                    //            errorNos.Add(id, new List<string>() { studentInfos[id].no });
                                                    //        }
                                                    //        isWrong = true;
                                                    //        break;
                                                    //    }
                                                    //}
                                                    writer.WriteString("no", studentInfos[id].no);
                                                    //#2769 C343 学生管理--导入学生名单，IRS编号默认与座号一致
                                                    writer.WriteString("irs", studentInfos[id].no);
                                                }
                                                break;
                                            //case bool _ when element.Name.Equals("irs", StringComparison.Ordinal):
                                            //    //移除座號的話會給空的
                                            //    if (studentInfos[id].irs != null && studentInfos[id].irs.Length == 0)
                                            //    {
                                            //        writer.WriteNull("irs");
                                            //        tmpData.irs = null;
                                            //    }
                                            //    else if (string.IsNullOrWhiteSpace(studentInfos[id].irs))
                                            //    {
                                            //        element.WriteTo(writer);
                                            //        tmpData.irs = element.Value.GetString();
                                            //    }
                                            //    else
                                            //    {
                                            //        writer.WriteString("irs", studentInfos[id].irs);
                                            //    }
                                            //    break;
                                            case bool _ when element.Name.StartsWith("_", StringComparison.Ordinal):
                                                break;
                                            default:
                                                element.WriteTo(writer);
                                                break;
                                        }
                                    }
                                    var stu = account.Deserialize<Student>();
                                    if (stu.guardians.IsNotEmpty() && studentInfos[id].guardians!=null)
                                    {
                                        var  mobiles   = stu.guardians.Select(m => m.mobile);
                                        var except = mobiles.Except(studentInfos[id].guardians.Select(x => x.mobile));
                                        if (except.Any()) {
                                            //清除移除的监护人
                                            List<Guardian> guardians = new List<Guardian>();
                                            string sql = $"select distinct value (c) from c   where  c.mobile in ({string.Join(",", except.Select(z => $"'{z}'"))} )";
                                            await foreach (var guard in cosmosContainer.GetItemQueryIterator<Guardian>(queryText: sql,
                                                requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey("Base-Guardian") }))
                                            {
                                                guard.students.RemoveAll(s => s.id.Equals(id) && s.code.Equals(schoolId));
                                                guardians.Add(guard);
                                            }
                                            guardians.ForEach(async x => {
                                                if (x.students.IsNotEmpty())
                                                {
                                                    try{await cosmosContainer.ReplaceItemAsync(x, x.id, new PartitionKey(x.code));  } catch {}
                                                }
                                                else
                                                {
                                                    try {
                                                        await cosmosContainer.DeleteItemStreamAsync(x.id, new PartitionKey(x.code));
                                                    }
                                                    catch{ 
                                                    }
                                                }
                                                
                                            });
                                        }
                                    }
                                    if (studentInfos[id].guardians.IsNotEmpty())
                                    {
                                       
                                        foreach (var gtudentGuardian in studentInfos[id].guardians)
                                        {
                                            List<Guardian> guardians = new List<Guardian>();
                                            string sql = $"select distinct value (c) from c   where  c.mobile='{gtudentGuardian.mobile}' ";
                                            await foreach (var guard in cosmosContainer.GetItemQueryIterator<Guardian>(queryText: sql, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey("Base-Guardian") }))
                                            {
                                                guardians.Add(guard);
                                            }
                                            if (guardians.Any())
                                            {
                                                guardians.ForEach(async x => {
                                                    GuardianStudent guardianStudent = x.students.Find(s => s.id.Equals(id) && s.code.Equals(schoolId));
                                                    if (guardianStudent == null)
                                                    {
                                                        x.students.Add(new GuardianStudent { relation =gtudentGuardian.relation, type = 2, id = id, code = schoolId, name = studentInfos[id].name });
                                                    }
                                                    try { await cosmosContainer.ReplaceItemAsync(x, x.id, new PartitionKey(x.code)); } catch { }
                                                });
                                            }
                                            else
                                            {
                                                Guardian guardian = new Guardian
                                                {
                                                    id = Guid.NewGuid().ToString(),
                                                    code = "Base-Guardian",
                                                    pk = "Guardian",
                                                    mobile = gtudentGuardian.mobile,
                                                    name = gtudentGuardian.name,
                                                    students = new List<GuardianStudent> { new GuardianStudent {relation=gtudentGuardian.relation, type = 2, id = id, code = schoolId, name = studentInfos[id].name } }
                                                };
                                                try { await cosmosContainer.CreateItemAsync(guardian, new PartitionKey(guardian.code)); } catch { }
                                            }
                                        }
                                        writer.WriteStartArray("guardians");

                                        foreach (var guardian in studentInfos[id].guardians)
                                        {
                                            writer.WriteStartObject();
                                            writer.WriteString("relation", guardian.relation);
                                            writer.WriteString("name", guardian.name);
                                            writer.WriteString("mobile", guardian.mobile);
                                            writer.WriteString("mail", guardian.mail);
                                            writer.WriteString("picture", guardian.picture);
                                            writer.WriteString("tmdid", guardian.tmdid);
                                            writer.WriteEndObject();
                                        }
                                        writer.WriteEndArray();

                                       
                                    }
                                    else
                                    {
                                        writer.WriteStartArray("guardians");
                                        //writer.WriteStartObject();
                                        //writer.WriteEndObject();
                                        writer.WriteEndArray();
                                    }
                                    //如果有錯誤，如座號重覆等，就會跳過該次更新。
                                    if (isWrong)
                                    {
                                        await writer.DisposeAsync();
                                        continue;
                                    }
                                    //#2769 C343 学生管理--导入学生名单，IRS编号默认与座号一致
                                    //if (!account.TryGetProperty("irs", out JsonElement _irs) || _irs.ValueKind.Equals(JsonValueKind.Undefined))
                                    //{
                                    //    writer.WriteString("irs", studentInfos[id].irs);
                                    //}
                                    writer.WriteString("irs", studentInfos[id].no);

                                    //若密碼和鹽沒有更新，就把舊的資料寫回去
                                    if (!isUpPwDone)
                                    {
                                        writer.WriteString("salt", account.GetProperty("salt").GetString());
                                        writer.WriteString("pw", account.GetProperty("pw").GetString());
                                    }
                                    
                                    writer.WriteEndObject();
                                    writer.Flush();
                                    //编辑是是否要明确清除电子学生证。
                                    await upsertImei(id, studentInfos[id].imei, schoolId, "clean", _azureCosmos
                                                         .GetCosmosClient()
                                                         .GetContainer(Constant.TEAMModelOS, "Student"));
                                    try
                                    {
                                        var ret = await cosmosContainer.ReplaceItemStreamAsync(memoryStream, id, new PartitionKey($"Base-{schoolId}"));
                                        //將更新完的id從字典內移除，保留沒查到的。
                                        if (ret.Status == (int)HttpStatusCode.OK)
                                        {
                                            nonexistentIds.Remove(id);
                                            retStuds.Add(new { id, tmpData.gender,tmpData.mail,tmpData.imei, tmpData.mobile, tmpData.name, tmpData.picture, tmpData.year, tmpData.no, classId, classNo, className, gradeId, periodId, tmpData.irs });
                                        }
                                        else errorIds.Add(id);
                                    }
                                    catch (CosmosException ex)
                                    {
                                        await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/updateStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
                                        errorIds.Add(id);
                                    }
                                    catch (Exception ex)
                                    {
                                        await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/updateStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
                                        errorIds.Add(id);
                                    }
                                }
                            }
                            //將輸入不存在的資料移除。
                            // nonexistentIds.ForEach(o => studentInfos.Remove(o));
                        }
                    }
                }
                errorClassId.ForEach(o => impClassDuplNo.Remove(o));

                return (retStuds, impClassDuplNo.Where(o => o.Value.Count != 0).ToDictionary(o => o.Key, o => o.Value), nonexistentIds, errorIds, errorNos, errorClassId);
            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/updateStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/updateStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
            }
            return (null, null, null, null, null, null);
        }

        /// <summary>
        /// 創建學生帳號，目前SDK4.0預覽版還不支援批量創建(TransactionalBatch)，待SDK正式發行時在優化此代碼。
        /// </summary>
        /// <param name="userStudents"></param>
        /// <returns>已存在的ID</returns>
        private async Task<(bool isSuc, List<string> existId)> createStudents(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, List<Student> userStudents)
        {
            var existId = new List<string>();
            var exceptions = new List<Exception>();
            try
            {
                var container = _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student");

                Parallel.ForEach(userStudents, async item =>
                {
                    try
                    {
                        await container.CreateItemAsync(item);
                    }
                    catch (CosmosException ex)
                    {
                        if (ex.Status == (int)HttpStatusCode.Conflict) existId.Add(item.id);
                        else exceptions.Add(ex);
                    }
                    catch (Exception ex)
                    {
                        exceptions.Add(ex);
                    }
                });
                if (exceptions.Count == 0) return (true, existId);
                else if (exceptions.Count > 1) throw new AggregateException(exceptions);
                else if (exceptions.Count == 1) throw exceptions.Single();
            }
            catch (AggregateException ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createStudents()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
            }
            return (false, existId);
        }

        /// <summary>
        /// 取得該教室的學生座號，若有給座號LIST，則座號存在才會被查到；反之，若沒給則會將該間教室所有座號抓出來。
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="classId"></param>
        /// <param name="nos"></param>
        /// <returns></returns>
        public static async Task<List<(string id, string no)>> checkStudNo(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId, string classId, List<string> nos = null)
        {
            List<(string id, string no)> ret = new List<(string id, string no)>();

            string queryText = $"SELECT c.id, c.no FROM c WHERE c.classId = '{classId}' AND c.code = 'Base-{schoolId}'";
            if (nos != null) queryText += $"AND c.no IN ({string.Join(",", nos.Select(o => $"'{o}'"))})";
            await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Student")
                .GetItemQueryStreamIterator(queryText: queryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{schoolId}") }))
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                {
                    var classInfos = json.RootElement.GetProperty("Documents").EnumerateArray();
                    while (classInfos.MoveNext())
                    {
                        JsonElement account = classInfos.Current;
                        string id = account.GetProperty("id").GetString();
                        string no = account.GetProperty("no").GetString();
                        ret.Add((id, no));
                    }
                }
            }
            return ret;
        }

        /// <summary>
        /// 取得年級資訊
        /// </summary>
        /// <param name="schoolId"></param>
        /// <returns>Key:periodId  Vaule:list gradeInfo</returns>
        public static async Task<Dictionary<string, List<(int gradeId, string gradeName)>>> getGrades(AzureCosmosFactory _azureCosmos, DingDing _dingDing, Option _option, string schoolId)
        {
            try
            {
                //Key:學制 Value:年級資訊list
                Dictionary<string, List<(int gradeId, string gradeName)>> dicPeriod = new Dictionary<string, List<(int gradeId, string gradeName)>>();

                var response = await _azureCosmos
                    .GetCosmosClient()
                    .GetContainer(Constant.TEAMModelOS, "School")
                    .ReadItemStreamAsync(schoolId, new PartitionKey("Base"));

                if (response.Status != (int)HttpStatusCode.OK) return null;

                using Stream stream = response.ContentStream;
                var jsonDoc = await JsonDocument.ParseAsync(stream);
                var emumObject = jsonDoc.RootElement.EnumerateObject();
                var period = jsonDoc.RootElement.GetProperty("period").EnumerateArray();

                while (period.MoveNext())
                {
                    List<(int gradeId, string gradeName)> gradeInfos = new List<(int gradeId, string gradeName)>();
                    JsonElement jsonPeriod = period.Current;
                    var periodId = jsonPeriod.GetProperty("id").GetString();
                    var grades = jsonPeriod.GetProperty("grades").ToObject<List<string>>();
                    for (int index = 0; index < grades.Count; index++)
                    {
                        gradeInfos.Add((index, grades[index]));
                    }
                    //var grades = jsonPeriod.GetProperty("grades").EnumerateArray();
                    //while (grades.MoveNext())
                    //{
                    //    JsonElement grade = grades.Current;
                    //    var gradeId = grade.GetInt32();
                    //    var gradeName = grade.GetProperty("name").GetString();
                    //    gradeInfos.Add((gradeId, gradeName));
                    //}

                    dicPeriod.Add(periodId, gradeInfos);
                }
                return dicPeriod;
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getGrades()\n{ex.Message}\n{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
                return null;
            }
        }

        public struct studCreateInfo
        {
            public studCreateInfo(string id, string name, string gender, int year, string pw, string classId, string no, string periodId,string imei, List<StudentGuardian> guardians)
            {
                this.id = id;
                this.name = name;
                this.gender = gender;
                this.year = year;
                this.pw = pw;
                this.classId = classId;
                this.no = no;
                this.periodId = periodId;
                this.imei = imei;
                this.guardians = guardians;

            }
            public string id { get; }
            public string name { get; }
            public string gender { get; }
            public int year { get; }
            public string pw { get; }
            public string classId { get; }
            public string no { get; }
            public string periodId { get; set; }
            public string imei { get; set; }
            public List<StudentGuardian> guardians { get; set; }
        }
    }
}