using Microsoft.Azure.Cosmos;
using MathNet.Numerics.LinearAlgebra.Double;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Options;
using Newtonsoft.Json;
using StackExchange.Redis;
using System;
using System.Collections.Generic;
using System.Dynamic;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Net.Http.Json;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using TEAMModelBI.Filter;
using TEAMModelBI.Models;
using TEAMModelBI.Tool;
using TEAMModelBI.Tool.CosmosBank;
using TEAMModelBI.Tool.Extension;
using TEAMModelOS.Models;
using TEAMModelOS.SDK;
using TEAMModelOS.SDK.Context.BI;
using TEAMModelOS.SDK.Context.Constant;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Extension;
using TEAMModelOS.SDK.Models;
using TEAMModelOS.SDK.Models.Cosmos.BI;
using TEAMModelOS.SDK.Models.Cosmos.Common;
using TEAMModelOS.SDK.Models.Service.BI;

namespace TEAMModelBI.Controllers.BISchool
{
    [Route("schoolcheck")]
    [ApiController]
    public class SchoolController : ControllerBase
    {
        private readonly AzureCosmosFactory _azureCosmos;
        private readonly DingDing _dingDing;
        private readonly Option _option;
        private readonly AzureStorageFactory _azureStorage;
        private readonly AzureRedisFactory _azureRedis;
        private readonly IConfiguration _configuration;
        private readonly CoreAPIHttpService _coreAPIHttpService;
        private readonly IWebHostEnvironment _environment; //读取文件
        private readonly IHttpClientFactory _httpClient;

        public SchoolController(AzureCosmosFactory azureCosmos, DingDing dingDing, AzureStorageFactory azureStorage, IOptionsSnapshot<Option> option, AzureRedisFactory azureRedis, IConfiguration configuration,   CoreAPIHttpService coreAPIHttpService, IHttpClientFactory httpClient, IWebHostEnvironment hostingEnvironment)
        {
            _azureCosmos = azureCosmos;
            _dingDing = dingDing;
            _azureStorage = azureStorage;
            _option = option?.Value;
            _azureRedis = azureRedis;
            _configuration = configuration;
            _coreAPIHttpService = coreAPIHttpService;
            _httpClient = httpClient;
            _environment = hostingEnvironment;
        }

        /// <summary>
        /// 查询未加入区域的学校   //已对接
        /// </summary>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-notarea")]
        public async Task<IActionResult> GetNotAreaSchool(JsonElement jsonElement)
        {
            try
            {
                jsonElement.TryGetProperty("areaId", out JsonElement areaId);
                //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释
                var cosmosClient = _azureCosmos.GetCosmosClient();
                ////分开部署，就不需要，一站多用时，取消注释
                //if ($"{site}".Equals(BIConst.Global))
                //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

                //默认不指定返回大小
                int? pageSize = null;
                string continuationToken = string.Empty;
                string pageToken = default;
                if (jsonElement.TryGetProperty("pageSize", out JsonElement jsonPageSize))
                {
                    if (!jsonPageSize.ValueKind.Equals(JsonValueKind.Undefined) && !jsonPageSize.ValueKind.Equals(JsonValueKind.Null) && jsonPageSize.TryGetInt32(out int tempPageSize))
                        pageSize = tempPageSize;
                }
                //是否需要进行分页查询，默认不分页
                bool iscontinuation = false;
                if (pageSize != null && pageSize.Value > 0)
                    iscontinuation = true;

                if (jsonElement.TryGetProperty("contToken", out JsonElement ContToken))
                    pageToken = ContToken.GetString();


                StringBuilder sqltxt = new($"SELECT c.id,c.name,c.schoolCode,c.province,c.city,c.dist,c.picture,c.period,c.areaId,c.standard,c.manyAreas FROM c");
                if (!string.IsNullOrEmpty($"{areaId}"))
                    sqltxt.Append($" WHERE c.pk='School' and c.areaId !='{areaId}'");
                else
                    sqltxt.Append($" WHERE c.pk='School' and (c.areaId = '' or c.areaId = null or IS_DEFINED(c.areaId) = false)");

                List<NotAreaSchool> tempNotAreaSchools = new();
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIteratorSql(queryText: sqltxt.ToString(), continuationToken: pageToken, requestOptions: new QueryRequestOptions() { MaxItemCount = pageSize, PartitionKey = new PartitionKey("Base") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.Content);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            NotAreaSchool notAreaSchool = new()
                            {
                                id = obj.GetProperty("id").GetString(),
                                name = obj.GetProperty("name").GetString(),
                                schoolCode = obj.GetProperty("schoolCode").GetString(),
                                picture = obj.GetProperty("picture").GetString(),
                                period = obj.GetProperty("period").ToObject<List<Period>>().Select(x => x.name).ToList(),
                                province = obj.GetProperty("province").GetString(),
                                city = obj.GetProperty("city").GetString(),
                            };
                            try
                            {
                                notAreaSchool.dist = obj.GetProperty("dist").GetString();
                                notAreaSchool.areaId = obj.GetProperty("areaId").GetString();
                                notAreaSchool.standard = obj.GetProperty("standard").GetString();
                                notAreaSchool.areas = obj.GetProperty("manyAreas").ToObject<List<SchoolArea>>();
                            }
                            catch { }
                            tempNotAreaSchools.Add(notAreaSchool);
                        }

                        if (iscontinuation)
                        {
                            continuationToken = item.ContinuationToken;
                            break;
                        }
                    }
                }

                List<NotAreaSchool> notAreaSchools = new();
                tempNotAreaSchools.ForEach(nas =>
                {
                    if (nas.areas == null)
                        notAreaSchools.Add(nas);
                    else
                    {
                        var not = nas.areas.Find(f => f.areaId == $"{areaId}");
                        if (not == null)
                            notAreaSchools.Add(nas);
                    }
                });

                //if (!string.IsNullOrEmpty($"{areaId}"))                    
                //    notAreaSchools = notAreaSchools.Select(na => new NotAreaSchool { id = na.id, name = na.name, schoolCode = na.schoolCode, picture = na.picture, period = na.period, province = na.province, city = na.city = na.city, dist = na.dist,  manyAreas =  new List<ManyArea> { na.manyAreas.Find(m => m.areaId != $"{areaId}") } }).ToList();

                return Ok(new { state = 200, continuationToken, pageSize, notAreaSchools });

            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI，{_option.Location}  /schoolcheck/get-notarea   \n    {ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 查询未加入区域的学校 国际站接口 不分页
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-nojoinsc")]
        public async Task<IActionResult> GetNoJoinAreaSc()
        {
            try
            {
                var cosmosClient = _azureCosmos.GetCosmosClient();
                var partitionKeys = new List<string>{"Base","VirtualBase"};
                //StringBuilder sqltxt = new($"SELECT c.id,c.name,c.schoolCode,c.province,c.city,c.dist,c.picture,c.period,c.areaId,c.standard,c.manyAreas FROM c WHERE c.pk='School' and (c.areaId = '' or c.areaId = null or IS_DEFINED(c.areaId) = false)");
                string sqltxt = $"SELECT value(c) FROM c WHERE (c.areaId = '' or c.areaId = null or IS_DEFINED(c.areaId) = false)";
                List<NotAreaSchool> notAreaSchools = new();
                foreach (string pk in partitionKeys)
                {
                    await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIteratorSql(queryText: sqltxt, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"{pk}") }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.Content);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        {
                            foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                NotAreaSchool notAreaSchool = new()
                                {
                                    code = obj.GetProperty("code").GetString(),
                                    id = obj.GetProperty("id").GetString(),
                                    name = obj.GetProperty("name").GetString(),
                                    schoolCode = obj.GetProperty("schoolCode").GetString(),
                                    picture = obj.GetProperty("picture").GetString(),
                                    period = obj.GetProperty("period").ToObject<List<Period>>().Select(x => x.name).ToList(),
                                    province = obj.GetProperty("province").GetString(),
                                    city = obj.GetProperty("city").GetString()
                                };
                                try
                                {
                                    notAreaSchool.dist = obj.GetProperty("dist").GetString();
                                    notAreaSchool.areaId = obj.GetProperty("areaId").GetString();
                                    notAreaSchool.standard = obj.GetProperty("standard").GetString();
                                    notAreaSchool.areas = obj.GetProperty("manyAreas").ToObject<List<SchoolArea>>();
                                }
                                catch { }
                                notAreaSchools.Add(notAreaSchool);
                            }
                        }
                    }
                }
                return Ok(new { state = 200, notAreaSchools });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI，{_option.Location}  /schoolcheck/get-nojoinsc   \n    {ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 学校加入区    //已对接
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [AuthToken(Roles = "admin,rdc,assist,sales")]
        [HttpPost("set-schooljoinarea")]
        public async Task<IActionResult> SetSchoolJoinArea(JsonElement jsonElement)
        {
            try
            {
                if (!jsonElement.TryGetProperty("standard", out JsonElement standard)) return BadRequest();
                if (!jsonElement.TryGetProperty("schoolCode", out JsonElement _schoolCode)) return BadRequest();
                if (!jsonElement.TryGetProperty("areaId", out JsonElement _areaId)) return BadRequest();
                jsonElement.TryGetProperty("areaName", out JsonElement areaName);
                jsonElement.TryGetProperty("isDefault", out JsonElement isDefault);
                List<string> codes = (jsonElement.TryGetProperty("code", out JsonElement codeJobj)) ? codeJobj.ToObject<List<string>>() : new List<string>();
                //jsonElement.TryGetProperty("site", out JsonElement site); //分开部署，就不需要，一站多用时，取消注释
                var isManyArea = false;
                if (!string.IsNullOrEmpty($"{isDefault}"))
                    isManyArea = true;

                var (_tmdId, _tmdName, pic, did, dname, dpic) = HttpJwtAnalysis.JwtXAuthBI(HttpContext.GetXAuth("AuthToken"), _option);
                List<string> schoolCodes = _schoolCode.ToObject<List<string>>();
                if (schoolCodes.Count > 0 && codes.Count.Equals(0)) //分區鍵值初始化
                {
                    for (int i = 0; i < schoolCodes.Count; i++)
                    {
                        codes.Add("Base");
                    }
                }
                for (int i = 0; i < codes.Count; i++)
                {
                    if (codes[i].Equals("BIRel")) codes[i] = "Base";
                }
                StringBuilder msg = new($"{_tmdName}【{_tmdId}】操作学校加入区域功能，加入的区域：{standard},学校ID：{string.Join("|", schoolCodes.ToArray())}");
                var cosmosClient = _azureCosmos.GetCosmosClient();
                var tableClient = _azureStorage.GetCloudTableClient();
                var blobClient = _azureStorage.GetBlobContainerClient(containerName: "0-public");
                ////分开部署，就不需要，一站多用时，取消注释
                //if ($"{site}".Equals(BIConst.Global))
                //{
                //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
                //    tableClient = _azureStorage.GetCloudTableClient(BIConst.Global);
                //    blobClient = _azureStorage.GetBlobContainerClient(containerName: "0-public", BIConst.Global);
                //}

                var responseSet = await cosmosClient.GetContainer(Constant.TEAMModelOS, "Normal").ReadItemStreamAsync($"{_areaId}", new PartitionKey("AreaSetting"));
                if (responseSet.StatusCode == HttpStatusCode.OK)
                {
                    using var fileJson = await JsonDocument.ParseAsync(responseSet.Content);
                    AreaSetting delSet = fileJson.ToObject<AreaSetting>();
                    if (!string.IsNullOrEmpty(delSet.accessConfig))
                        return Ok(new { state = 401, msg = "区域已经规定了，不能切换能能力" });
                }

                if (schoolCodes.Count > 0)
                {
                    foreach (var tempCode in schoolCodes)
                    {
                        int index = schoolCodes.IndexOf(tempCode);
                        string code = codes[index];
                        School school = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemAsync<School>(tempCode, new PartitionKey($"{code}"));
                        if (school != null)
                        {
                            if (isManyArea)
                            {
                                if (school.areas == null)
                                    school.areas = new List<SchoolArea>() { new SchoolArea() { areaId = $"{_areaId}", name = $"{areaName}", status = "join" } };
                                else
                                {
                                    var marea = school.areas.Find(ma => ma.areaId == $"{_areaId}");
                                    if (marea == null)
                                        school.areas.Add(new SchoolArea() { areaId = $"{_areaId}", name = $"{areaName}", status = $"join" });
                                }
                            }
                            school.standard = $"{standard}";
                            school.areaId = $"{_areaId}";

                            msg.Append($"{school.name}【{school.id}】学校移除多区域的名单：");
                            List<Teacher> teachers = new();
                            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryIteratorSql<Teacher>(queryText: $"SELECT value(c) FROM c join s in c.schools where s.schoolId ='{school.id}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
                            {
                                teachers.Add(item);
                            }

                            foreach (var item in teachers)
                            {
                                msg.Append($"{item.name}[{item.id}]");
                                var tchSchool = item.schools.Where(f => f.schoolId.Equals($"{school.id}")).FirstOrDefault();
                                if (tchSchool == null)
                                {
                                    Teacher.TeacherSchool teacherSchool = new() { schoolId = school.id, name = school.name, status = "join", time = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds(), picture = school.picture, areaId = school.areaId };
                                    item.schools.Add(teacherSchool);
                                }
                                else
                                    tchSchool.areaId = school.areaId;

                                await cosmosClient.GetContainer("TEAMModelOS", "Teacher").ReplaceItemAsync<Teacher>(item, item.id, new PartitionKey("Base"));
                            }
                            await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReplaceItemAsync<School>(school, school.id, new PartitionKey(school.code));
                            if (school.code.Equals("Base"))
                            {
                                await BIStats.SetSchoolBIRelation(cosmosClient, blobClient, tableClient, _dingDing, school);
                            }
                        }
                    }
                }

                //保存操作记录
                await AzureStorageBlobExtensions.SaveBILog(blobClient, tableClient, "school-update", msg.ToString(), _dingDing, httpContext: HttpContext);

                return Ok(new { state = 200 });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI，{_option.Location}  /schoolcheck/set-schooljoinarea   \n    {ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 依据学校编号查询学校信息   //已对接
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-schoolid")]
        public async Task<IActionResult> GetSchoolId(JsonElement jsonElement)
        {
            try
            {
                if (!jsonElement.TryGetProperty("schoolId", out JsonElement schoolId)) return BadRequest();
                //jsonElement.TryGetProperty("site", out JsonElement site);  //分开部署，就不需要，一站多用时，取消注释

                var cosmosClient = _azureCosmos.GetCosmosClient();
                ////分开部署，就不需要，一站多用时，取消注释
                //if ($"{site}".Equals(BIConst.Global))
                //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

                AssistSchool schoolAssists = new(); //返回学校列表集合
                string sqlTxt = $"select c.id,c.code,c.schoolCode,c.name,c.region,c.province,c.city,c.dist,c.size,c.address,c.picture,c.type,c.scale,c.areaId,c.standard,c.period,c.campuses from c where c.id='{schoolId}'";
                await foreach (var itemSchool in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIteratorSql<AssistSchool>(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
                {
                    schoolAssists = itemSchool;
                }
                //IES5實體學校
                if (schoolAssists.id != null)
                {
                    var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(schoolAssists.id, new PartitionKey("ProductSum"));
                    if (response.StatusCode == HttpStatusCode.OK)
                    {
                        using var json = await JsonDocument.ParseAsync(response.Content);
                        if (json.RootElement.TryGetProperty("serial", out JsonElement serial) && !serial.ValueKind.Equals(JsonValueKind.Null))
                        {
                            schoolAssists.serial = serial.ToObject<List<SchoolProductSumData>>().Select(x => x.prodCode).ToList();
                        }
                        if (json.RootElement.TryGetProperty("service", out JsonElement service) && !service.ValueKind.Equals(JsonValueKind.Null))
                        {
                            schoolAssists.service = service.ToObject<List<SchoolProductSumData>>().Select(x => x.prodCode).ToList();
                        }
                        if (json.RootElement.TryGetProperty("hard", out JsonElement hard) && !hard.ValueKind.Equals(JsonValueKind.Null))
                        {
                            schoolAssists.hard = hard.ToObject<List<SchoolProductSumDataHard>>().Select(x => x.prodCode).ToList();
                        }
                    }

                    schoolAssists.assists = await CommonFind.FindSchoolRoles(cosmosClient, schoolAssists.id, "assist");
                    schoolAssists.lessonCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"select value(count(c.id)) from c ", $"LessonRecord-{schoolAssists.id}");

                    return Ok(new { state = 200, schoolAssists });
                }
                //無實體學校 => 取得虛擬學校
                else
                {
                    await foreach (var itemSchool in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIteratorSql<AssistSchool>(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("VirtualBase") }))
                    {
                        schoolAssists = itemSchool;
                    }
                    if (schoolAssists.id != null) return Ok(new { state = 200, schoolAssists });
                    else return Ok(new { state = 404, msg = "未找到该学校！" });
                }
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI，{_option.Location}   /schoolcheck/get-schoolid  \n {ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 修改学校信息   //已对接
        /// </summary>
        /// <param name="school"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [AuthToken(Roles = "admin,rdc,assist,sales")]
        [HttpPost("upd-school")]
        public async Task<IActionResult> UpdSchool(School school, [FromHeader] string site)
        {
            try
            {
                School schoolInfo = new School();
                var cosmosClient = _azureCosmos.GetCosmosClient();
                var tableClient = _azureStorage.GetCloudTableClient();
                var blobClient = _azureStorage.GetBlobContainerClient(containerName: "0-public");
                if ($"{site}".Equals(BIConst.Global))
                {
                    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
                    tableClient = _azureStorage.GetCloudTableClient(BIConst.Global);
                    blobClient = _azureStorage.GetBlobContainerClient(containerName: "0-public", BIConst.Global);
                }

                string _auth = HttpContext.GetXAuth("AuthToken");
                var (tmdId, tmdName, pic, did, dname, dpic) = HttpJwtAnalysis.JwtXAuthBI(_auth, _option);
                var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(school.id, new PartitionKey($"Base"));
                if (response.StatusCode == HttpStatusCode.OK)
                {
                    string sql = $"SELECT distinct value(c) FROM c join A1 in c.schools where A1.schoolId='{school.id}'";
                    List<Teacher> teachers = new();
                    await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryIteratorSql<Teacher>(sql, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey("Base") }))
                    {
                        teachers.Add(item);
                    }
                    foreach (var item in teachers)
                    {
                        Teacher.TeacherSchool teacherSchool = item.schools.Find(x => x.schoolId.Equals(school.id));
                        if (teacherSchool != null)
                        {
                            teacherSchool.name = school.name;
                            teacherSchool.picture = school.picture;
                            teacherSchool.areaId = school.areaId;
                        }
                        await cosmosClient.GetContainer("TEAMModelOS", "Teacher").ReplaceItemAsync(item, item.id, new PartitionKey($"Base"));
                    }
                    schoolInfo = await cosmosClient.GetContainer("TEAMModelOS", "School").ReplaceItemAsync<School>(school, school.id, new PartitionKey($"Base"));

                    //保存操作记录
                    await AzureStorageBlobExtensions.SaveBILog(blobClient, tableClient, "school-update", $"{tmdName}【{tmdId}】修改学校信息，学校和ID:{school.name}【{school.id}】", _dingDing, httpContext: HttpContext);
                }
                else return Ok(new { state = 400, message = "请求错误！" });

                return Ok(new { state = 200, schoolInfo });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI,{_option.Location}  /schoolcheck/upd-school \n {ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 顾问学校空间使用情况
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-assistspace")]
        public async Task<IActionResult> GetAssistSchoolSpace(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return BadRequest();
            //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释
            var cosmosClient = _azureCosmos.GetCosmosClient();
            ////分开部署，就不需要，一站多用时，取消注释
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

            List<string> schools = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");
            List<SchoolSpace> schoolSpaces = new();
            foreach (var sid in schools)
            {
                School school = new();
                var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(sid, new PartitionKey("Base"));
                if (response.StatusCode == HttpStatusCode.OK)
                {
                    using var json = await JsonDocument.ParseAsync(response.Content);
                    school = json.ToObject<School>();
                }

                long teach = 0;
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIteratorSql(queryText: $"SELECT sum(c.size) as size FROM c ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Teacher-{sid}") }))
                {
                    var json = await JsonDocument.ParseAsync(item.Content);
                    foreach (var elmt in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        if (elmt.TryGetProperty("size", out JsonElement _size) && _size.ValueKind.Equals(JsonValueKind.Number))
                        {
                            teach = _size.GetInt32();
                            break;
                        }
                    }
                }

                SchoolSpace schoolSpace = new() { id = sid, name = school != null ? school.name : sid };

                Space space = new Space();
                long blobsize = 0; //使用大小                
                RedisValue value = default;
                value = _azureRedis.GetRedisClient(8).HashGet($"Blob:Record", sid);

                if (value != default && !value.IsNullOrEmpty)
                {
                    JsonElement record = value.ToString().ToObject<JsonElement>();
                    if (record.TryGetInt64(out blobsize))
                    {
                    }
                }
                else
                {
                    var client = _azureStorage.GetBlobContainerClient(sid);
                    var size = await client.GetBlobsCatalogSize();
                    await _azureRedis.GetRedisClient(8).HashSetAsync($"Blob:Record", sid, size.Item1);
                    foreach (var key in size.Item2.Keys)
                    {
                        await _azureRedis.GetRedisClient(8).SortedSetRemoveAsync($"Blob:Catalog:{sid}", key);
                        await _azureRedis.GetRedisClient(8).SortedSetIncrementAsync($"Blob:Catalog:{sid}", key, size.Item2[key].HasValue ? size.Item2[key].Value : 0);
                    }
                    space.useSize = (long)size.Item1; space.tSize = teach; space.catalogSize = size.Item2;
                    schoolSpace.space = space;
                    schoolSpaces.Add(schoolSpace);
                    continue;
                }

                Dictionary<string, double?> catalog = new Dictionary<string, double?>();
                SortedSetEntry[] Scores = _azureRedis.GetRedisClient(8).SortedSetRangeByScoreWithScores($"Blob:Catalog:{sid}");
                if (Scores != null)
                {
                    foreach (var score in Scores)
                    {
                        double val = score.Score;
                        string key = score.Element.ToString();
                        catalog.Add(key, val);
                    }

                    space.useSize = blobsize; space.tSize = teach; space.catalogSize = catalog;

                    schoolSpace.space = space;
                    schoolSpaces.Add(schoolSpace);
                    continue;
                }
                else
                {
                    var client = _azureStorage.GetBlobContainerClient(sid);
                    var size = await client.GetBlobsCatalogSize();
                    await _azureRedis.GetRedisClient(8).HashSetAsync($"Blob:Record", sid, size.Item1);
                    foreach (var key in size.Item2.Keys)
                    {
                        await _azureRedis.GetRedisClient(8).SortedSetRemoveAsync($"Blob:Catalog:{sid}", key);
                        await _azureRedis.GetRedisClient(8).SortedSetIncrementAsync($"Blob:Catalog:{sid}", key, size.Item2[key].HasValue ? size.Item2[key].Value : 0);
                    }

                    space.useSize = (long)size.Item1;
                    space.tSize = teach;
                    space.catalogSize = size.Item2;
                    schoolSpace.space = space;
                    schoolSpaces.Add(schoolSpace);
                    continue;
                }
            }

            return Ok(new { state = 200, schoolSpaces });
        }

        /// <summary>
        /// 修改顾问学校    //已经对接
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [AuthToken(Roles = "admin,rdc")]
        [HttpPost("set-aistschool")]
        public async Task<IActionResult> SetAssistSchool(JsonElement jsonElement)
        {
            try
            {
                if (!jsonElement.TryGetProperty("partitionKey", out JsonElement parKey)) return BadRequest();
                jsonElement.TryGetProperty("userId", out JsonElement userId);
                jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
                jsonElement.TryGetProperty("role", out JsonElement role);
                if (!jsonElement.TryGetProperty("schoolIds", out JsonElement jsSchoolIds)) return BadRequest();
                if (!jsonElement.TryGetProperty("busy", out JsonElement busy)) return BadRequest();
                //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释

                List<string> schoolIds = jsSchoolIds.ToObject<List<string>>();
                var table = _azureStorage.GetCloudTableClient().GetTableReference("BIDDUserInfo");
                var cosmosClient = _azureCosmos.GetCosmosClient();
                string divide = _option.Location;
                ////分开部署，就不需要，一站多用时，取消注释
                //if ($"{site}".Equals(BIConst.Global))
                //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

                Dictionary<string, object> dic = new() { { "PartitionKey", $"{parKey}" } };
                if (!string.IsNullOrEmpty($"{userId}"))
                {
                    dic.Add("userId", $"{userId}");
                }
                if (string.IsNullOrEmpty($"{userId}") && string.IsNullOrEmpty($"{tmdId}"))
                {
                    return Ok(new { state = 400, msg = "钉钉账户或醍摩豆账户需要至少要传一个！" });
                }

                Dictionary<string, List<string>> noSchools = new();
                List<DingDingUserInfo> ddUserInfoList = await table.FindListByDict<DingDingUserInfo>(dic);
                List<DingDingUserInfo> updUsers = new();

                if (ddUserInfoList.Count > 0)
                {
                    foreach (var ddInfo in ddUserInfoList)
                    {
                        if (schoolIds.Count > 0)
                        {
                            if (string.IsNullOrEmpty(ddInfo.tmdId))
                            {
                                var coreUser = await _coreAPIHttpService.GetUserInfo(new Dictionary<string, string> { { "key", $"{ddInfo.mobile}" } }, _option.Location, _configuration);
                                if (coreUser != null && coreUser.id != null)
                                {
                                    ddInfo.tmdId = coreUser.id;
                                    ddInfo.tmdName = coreUser.name;
                                    ddInfo.tmdMobile = coreUser.mobile;
                                    ddInfo.picture = coreUser.picture;
                                    ddInfo.mail = coreUser.mail;
                                }
                                else return Ok(new { state = 404, msg = "依据钉钉手机号未找到醍摩豆账号！" });

                                //List<string> moblie = new() { $"{ddInfo.mobile}" };
                                //var content = new StringContent(moblie.ToJsonString(), Encoding.UTF8, "application/json");
                                //string json = await _coreAPIHttpService.GetUserInfos(content);
                                //if (!string.IsNullOrEmpty(json))
                                //{
                                //    List<JsonElement> json_id = json.ToObject<List<JsonElement>>();
                                //    foreach (var tmd in json_id)
                                //    {
                                //        ddInfo.tmdId = tmd.GetProperty("id").ToString();
                                //        ddInfo.tmdName = tmd.GetProperty("name").ToString();
                                //        ddInfo.tmdMobile = tmd.GetProperty("mobile").ToString();
                                //        ddInfo.picture = tmd.GetProperty("picture").ToString();
                                //        ddInfo.mail = tmd.GetProperty("mail").ToString();
                                //    }
                                //}
                                //else return Ok(new { state = 404, msg = "依据钉钉手机号未找到醍摩豆账号！" });
                            }
                            List<string> noSchool = await SchoolWay.SchoolAdviser(cosmosClient, ddInfo, schoolIds, $"{role}", $"{busy}");
                            if (noSchool.Count > 0)
                            {
                                noSchools.Add(ddInfo.tmdId, noSchool);
                            }
                        }
                        List<string> userSchoolAdv = new(ddInfo.schoolIds.Split("|", StringSplitOptions.RemoveEmptyEntries));
                        if ($"{busy}".Equals("add"))
                        {
                            userSchoolAdv.AddRange(schoolIds);
                            ddInfo.schoolIds = userSchoolAdv.Count > 0 ? string.Join("|", userSchoolAdv) : "";
                        }

                        if ($"{busy}".Equals("del"))
                        {
                            var temp = userSchoolAdv.Where(p => !schoolIds.Any(x => x == p)).ToList();
                            ddInfo.schoolIds = temp.Count > 0 ? string.Join("|", temp) : "";
                        }
                        updUsers.Add(ddInfo);
                    }
                }

                updUsers = await table.SaveOrUpdateAll(updUsers);
                if (noSchools.Count > 0)
                    return Ok(new { state = 201, recUsers = updUsers, noSchools });
                else
                    return Ok(new { state = 200, recUsers = updUsers });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI,{_option.Location}  /schoolcheck/set-aistschool \n {ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 依据学校ID查学校管理员信息    //已对接
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-scmanage")]
        public async Task<IActionResult> GetSchoolManage(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("scId", out JsonElement scId)) return BadRequest();
            //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释
            var cosmosClient = _azureCosmos.GetCosmosClient();
            ////分开部署，就不需要，一站多用时，取消注释
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

            List<BaseInfo> scManages = new();
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIteratorSql<BaseInfo>(queryText: $"select c.id,c.name,c.picture from c where ARRAY_CONTAINS(c.roles,'admin',true) and c.code='Teacher-{scId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Teacher-{scId}") }))
            {
                scManages.Add(item);
            }

            return Ok(new { state = RespondCode.Ok, scManages });
        }

        /// <summary>
        /// 设置学校管理员   //已对接
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [AuthToken(Roles = "admin,rdc")]
        [HttpPost("set-manage")]
        public async Task<IActionResult> SetSchoolManage(JsonElement jsonElement)
        {
            try
            {
                var (_tmdId, _tmdName, _, _, _, _) = HttpJwtAnalysis.JwtXAuthBI(HttpContext.GetXAuth("AuthToken"), _option);

                if (!jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return BadRequest();
                if (!jsonElement.TryGetProperty("tmdName", out JsonElement tmdName)) return BadRequest();
                jsonElement.TryGetProperty("tmdPic", out JsonElement picture);
                if (!jsonElement.TryGetProperty("scId", out JsonElement schoolId)) return BadRequest();
                if (!jsonElement.TryGetProperty("scName", out JsonElement schoolName)) return BadRequest();
                jsonElement.TryGetProperty("scAreaId", out JsonElement scAreaId);
                jsonElement.TryGetProperty("scPic", out JsonElement scpicture);
                //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释

                var cosmosClient = _azureCosmos.GetCosmosClient();
                var tableClient = _azureStorage.GetCloudTableClient();
                var blobClient = _azureStorage.GetBlobContainerClient(containerName: "0-public");

                ////分开部署，就不需要，一站多用时，取消注释
                //if ($"{site}".Equals(BIConst.Global))
                //{
                //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
                //    tableClient = _azureStorage.GetCloudTableClient(BIConst.Global);
                //    blobClient = _azureStorage.GetBlobContainerClient(containerName: "0-public", BIConst.Global);
                //}
                StringBuilder strMsg = new($"{_tmdName}【{_tmdId}】账户将{tmdName}【{tmdId}】");
                string areaId = null;
                if (string.IsNullOrEmpty($"{scAreaId}"))
                {
                    var resScInfo = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync($"{schoolId}", new PartitionKey("Base"));

                    if (resScInfo.StatusCode == HttpStatusCode.OK)
                    {
                        using var tchJson = await JsonDocument.ParseAsync(resScInfo.Content);
                        School scInfo = tchJson.ToObject<School>();
                        areaId = scInfo.areaId;
                    }
                    else areaId = $"{scAreaId}";
                }
                else { areaId = $"{scAreaId}"; }

                Teacher teacher = new();
                SchoolTeacher schoolTeacher = new();
                var resTeacher = await cosmosClient.GetContainer("TEAMModelOS", "Teacher").ReadItemStreamAsync($"{tmdId}", new PartitionKey($"Base"));
                if (resTeacher.StatusCode == HttpStatusCode.OK)
                {
                    using var tchJson = await JsonDocument.ParseAsync(resTeacher.Content);
                    teacher = tchJson.ToObject<Teacher>();

                    var existArea = teacher.schools.Find(f => f.schoolId.Equals($"{schoolId}"));
                    if (existArea == null)
                    {
                        teacher.schools.Add(new Teacher.TeacherSchool { schoolId = $"{schoolId}", name = $"{schoolName}", status = "join", time = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds(), picture = string.IsNullOrEmpty($"{scpicture}") ? "" : $"{scpicture}", areaId = areaId });
                        teacher = await cosmosClient.GetContainer("TEAMModelOS", "Teacher").ReplaceItemAsync<Teacher>(teacher, teacher.id, new PartitionKey("Base"));
                        strMsg.Append($"账户基础信息添加学校，{schoolName}【{schoolId}】;");
                    }
                }
                else
                {
                    teacher.id = $"{tmdId}";
                    teacher.name = $"{tmdName}";
                    teacher.picture = string.IsNullOrEmpty($"{picture}") ? "" : $"{picture}";
                    teacher.pk = "Base";
                    teacher.code = "Base";
                    teacher.size = 1;
                    teacher.createTime = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
                    //教师存在，在该教师信息中添加要管理的学校信息
                    teacher.schools.Add(new Teacher.TeacherSchool { schoolId = $"{schoolId}", name = $"{schoolName}", status = "join", time = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds(), picture = string.IsNullOrEmpty($"{scpicture}") ? "" : $"{scpicture}", areaId = areaId });
                    teacher = await cosmosClient.GetContainer(Constant.TEAMModelOS, "Teacher").CreateItemAsync<Teacher>(teacher, new PartitionKey("Base"));
                    strMsg.Append($"添加基础信息，并添加学校,{schoolName}【{schoolId}】;");
                }

                if (teacher != null)
                {
                    var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync($"{tmdId}", new PartitionKey($"Teacher-{schoolId}"));
                    if (response.StatusCode == HttpStatusCode.OK)
                    {
                        using var json = await JsonDocument.ParseAsync(response.Content);
                        schoolTeacher = json.ToObject<SchoolTeacher>();
                        if (!schoolTeacher.roles.Contains("admin"))
                        {
                            if(!schoolTeacher.roles.Contains("teacher")) schoolTeacher.roles.Add("teacher");
                            schoolTeacher.roles.Add("admin");
                            schoolTeacher.status = "join";
                            schoolTeacher = await cosmosClient.GetContainer("TEAMModelOS", "School").ReplaceItemAsync<SchoolTeacher>(schoolTeacher, schoolTeacher.id, new PartitionKey($"Teacher-{schoolId}"));

                            #region 同時更新teacher的school.status
                            var school = teacher.schools.Find(f => f.schoolId.Equals($"{schoolId}"));
                            school.status = "join";
                            await cosmosClient.GetContainer("TEAMModelOS", "Teacher").ReplaceItemAsync<Teacher>(teacher, teacher.id, new PartitionKey("Base"));                            
                            #endregion
                            strMsg.Append($"并设置管理员，学校信息{schoolName}【{schoolId}】");
                        }
                        else
                            return Ok(new { state = RespondCode.Conflict, schoolTeacher = "已经是该校的管理人员" });
                    }
                    else
                    {
                        schoolTeacher = new()
                        {
                            id = $"{tmdId}",
                            code = $"Teacher-{schoolId}",
                            roles = new List<string> { "admin", "teacher" },
                            job = "管理员",
                            name = $"{tmdName}",
                            picture = string.IsNullOrEmpty($"{picture}") ? "" : $"{picture}",
                            status = "join",
                            createTime = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds(),
                            pk = "Teacher",
                            ttl = -1
                        };

                        schoolTeacher = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").CreateItemAsync<SchoolTeacher>(schoolTeacher, new PartitionKey($"Teacher-{schoolId}"));
                        strMsg.Append($"并新建学校管理员，学校信息{schoolName}【{schoolId}】");
                    }
                }
                //保存操作记录
                await AzureStorageBlobExtensions.SaveBILog(blobClient, tableClient, "schoolTeacher-add", strMsg.ToString(), _dingDing, httpContext: HttpContext);

                return Ok(new { state = RespondCode.Ok, teacher, schoolTeacher });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI,{_option.Location}  /schoolcheck/set-schoolme \n {ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 移除学校管理员    //已对接
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [AuthToken(Roles = "admin,rdc")]
        [HttpPost("del-manage")]
        public async Task<IActionResult> DelSchoolManage(JsonElement jsonElement)
        {
            var (_tmdId, _tmdName, _, _, _, _) = HttpJwtAnalysis.JwtXAuthBI(HttpContext.GetXAuth("AuthToken"), _option);
            if (!jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return BadRequest();
            if (!jsonElement.TryGetProperty("scIds", out JsonElement _scIds)) return BadRequest();
            bool rmvFromSc = (jsonElement.TryGetProperty("rmvFromSc", out JsonElement rmvFromScJobj)) ? rmvFromScJobj.GetBoolean() : false; //rmvFromSc Bool true:移除SchoolTeacher document false:只移除roles中"admin"身分
            //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释

            List<string> scIds = _scIds.ToObject<List<string>>();
            var cosmosClient = _azureCosmos.GetCosmosClient();
            var tableClient = _azureStorage.GetCloudTableClient();
            var blobClient = _azureStorage.GetBlobContainerClient(containerName: "0-public");
            ////分开部署，就不需要，一站多用时，取消注释
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            List<string> existScId = new();
            StringBuilder strMsg = new($"{_tmdName}【{_tmdId}】管理员账户将{tmdId}的账户移除学校：");
            //老師
            if (rmvFromSc) //從老師的學校列表移除該學校
            {
                var response = await cosmosClient.GetContainer("TEAMModelOS", "Teacher").ReadItemStreamAsync($"{tmdId}", new PartitionKey("Base"));
                if (response.StatusCode == HttpStatusCode.OK)
                {
                    using var json = await JsonDocument.ParseAsync(response.Content);
                    Teacher teacher = json.ToObject<Teacher>();
                    teacher.schools.RemoveAll(s => scIds.Contains(s.schoolId));
                    await cosmosClient.GetContainer("TEAMModelOS", "Teacher").ReplaceItemAsync<Teacher>(teacher, teacher.id, new PartitionKey("Base"));
                }
            }
            //學校
            SchoolTeacher scTeacher = null;
            foreach (var scId in scIds)
            {
                var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync($"{tmdId}", new PartitionKey($"Teacher-{scId}"));
                if (response.StatusCode == HttpStatusCode.OK)
                {
                    if (rmvFromSc) //將該老師從學校的老師名單中移除
                    {
                        //將該老師從學校的老師名單中移除
                        await cosmosClient.GetContainer("TEAMModelOS", "School").DeleteItemAsync<SchoolTeacher>($"{tmdId}", new PartitionKey($"Teacher-{scId}"));
                        strMsg.Append($"{scId},");
                    }
                    else
                    {
                        using var json = await JsonDocument.ParseAsync(response.Content);
                        scTeacher = json.ToObject<SchoolTeacher>();
                        if (scTeacher.roles.Contains("admin"))
                        {
                            scTeacher.roles.Remove("admin");
                            strMsg.Append($"{scId},");
                            scTeacher = await cosmosClient.GetContainer("TEAMModelOS", "School").ReplaceItemAsync<SchoolTeacher>(scTeacher, scTeacher.id, new PartitionKey($"Teacher-{scId}"));
                        }
                        else
                            existScId.Add(scId);
                    }
                }
                else
                    existScId.Add(scId);
            }

            //保存操作记录
            await AzureStorageBlobExtensions.SaveBILog(blobClient, tableClient, "schoolTeacher-updatee", strMsg.ToString(), _dingDing, httpContext: HttpContext);

            return Ok(new { state = RespondCode.Ok, existScId });
        }

        /// <summary>
        /// 一个账户设置多个学校的管理员
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [AuthToken(Roles = "admin,rdc")]
        [HttpPost("set-batchmageage")]
        public async Task<IActionResult> SetBatchScManage(JsonElement jsonElement)
        {
            try
            {
                var (_tmdId, _tmdName, _, _, _, _) = HttpJwtAnalysis.JwtXAuthBI(HttpContext.GetXAuth("AuthToken"), _option);

                if (!jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return BadRequest();
                if (!jsonElement.TryGetProperty("tmdName", out JsonElement tmdName)) return BadRequest();
                jsonElement.TryGetProperty("tmdPic", out JsonElement picture);
                if (!jsonElement.TryGetProperty("scSimplles", out JsonElement scSimplles)) return BadRequest();
                List<BatchScManage> schools = scSimplles.ToObject<List<BatchScManage>>();
                List<BatchScManage> existManageSc = new();
                Teacher teacher = new();
                StringBuilder msg = new($"{_tmdName}[{_tmdId}]设置账号：");

                var cosmosClient = _azureCosmos.GetCosmosClient();
                var tableClient = _azureStorage.GetCloudTableClient();
                var blobClient = _azureStorage.GetBlobContainerClient(containerName: "0-public");

                if (schools.Count > 0)
                {
                    var resTeacher = await cosmosClient.GetContainer("TEAMModelOS", "Teacher").ReadItemStreamAsync($"{tmdId}", new PartitionKey($"Base"));
                    if (resTeacher.StatusCode == HttpStatusCode.OK)
                    {
                        using var tchJson = await JsonDocument.ParseAsync(resTeacher.Content);
                        teacher = tchJson.ToObject<Teacher>();
                    }
                    else
                    {
                        teacher.id = $"{tmdId}";
                        teacher.name = $"{tmdName}";
                        teacher.picture = string.IsNullOrEmpty($"{picture}") ? "" : $"{picture}";
                        teacher.pk = "Base";
                        teacher.code = "Base";
                        teacher.size = 1;
                        teacher.createTime = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
                    }
                    msg.Append($"{teacher.name}[{teacher.id}]设置学校管理员；学校信息：");

                    foreach (var school in schools)
                    {
                        SchoolTeacher schoolTeacher = null;
                        var existSchool = teacher.schools.Find(f => f.schoolId.Equals($"{school.id}"));
                        if (existSchool == null)
                        {
                            teacher.schools.Add(new Teacher.TeacherSchool { schoolId = $"{school.id}", name = $"{school.name}", status = "join", time = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds(), picture = string.IsNullOrEmpty($"{school.picture}") ? "" : $"{school.picture}", areaId = string.IsNullOrEmpty($"{school.areaId}") ? "" : $"{school.areaId}" });
                        }
                        else
                            existSchool.name = school.name;

                        var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync($"{tmdId}", new PartitionKey($"Teacher-{school.id}"));
                        if (response.StatusCode == HttpStatusCode.OK)
                        {
                            using var json = await JsonDocument.ParseAsync(response.Content);
                            schoolTeacher = json.ToObject<SchoolTeacher>();
                            if (!schoolTeacher.roles.Contains("admin"))
                            {
                                schoolTeacher.roles.Add("admin");
                                schoolTeacher = await cosmosClient.GetContainer("TEAMModelOS", "School").ReplaceItemAsync<SchoolTeacher>(schoolTeacher, schoolTeacher.id, new PartitionKey($"Teacher-{school.id}"));
                            }
                            else
                                existManageSc.Add(school);
                        }
                        else
                        {
                            schoolTeacher = new()
                            {
                                id = $"{tmdId}",
                                code = $"Teacher-{school.id}",
                                roles = new List<string> { "admin" },
                                job = "管理员",
                                name = $"{tmdName}",
                                picture = string.IsNullOrEmpty($"{picture}") ? "" : $"{picture}",
                                status = "join",
                                createTime = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds(),
                                pk = "Teacher",
                                ttl = -1
                            };

                            schoolTeacher = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").CreateItemAsync<SchoolTeacher>(schoolTeacher, new PartitionKey($"Teacher-{school.id}"));
                        }
                        msg.Append($"{school.name}[{school.id}]");
                    }

                    if (resTeacher.StatusCode == HttpStatusCode.OK)
                        teacher = await cosmosClient.GetContainer("TEAMModelOS", "Teacher").ReplaceItemAsync<Teacher>(teacher, teacher.id, new PartitionKey("Base"));
                    else
                        teacher = await cosmosClient.GetContainer(Constant.TEAMModelOS, "Teacher").CreateItemAsync<Teacher>(teacher, new PartitionKey("Base"));
                }

                //保存操作记录
                await AzureStorageBlobExtensions.SaveBILog(blobClient, tableClient, "schoolTeacher-add", msg.ToString(), _dingDing, httpContext: HttpContext);

                return Ok(new { state = RespondCode.Ok, teacher });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI,{_option.Location}  /schoolcheck/set-batchmageage \n {ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 删除学校、学校相关的教师、学生   //已对接
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [AuthToken(Roles = "admin,rdc")]
        [HttpPost("set-del")]
        public async Task<IActionResult> DelSchool(JsonElement jsonElement)
        {
            try
            {
                if (!jsonElement.TryGetProperty("schoolId", out JsonElement schoolId)) return BadRequest();
                List<string> schools = schoolId.ToObject<List<string>>();

                //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释
                string _auth = HttpContext.GetXAuth("AuthToken");
                var (tmdId, tmdName, pic, did, dname, dpic) = HttpJwtAnalysis.JwtXAuthBI(_auth, _option);
                var cosmosClient = _azureCosmos.GetCosmosClient();
                var tableClient = _azureStorage.GetCloudTableClient();
                var blobClient = _azureStorage.GetBlobContainerClient(containerName: "0-public");
                List<string> codes = (jsonElement.TryGetProperty("code", out JsonElement codeJobj)) ? codeJobj.ToObject<List<string>>() : new List<string>();
                if (schools.Count > 0 && codes.Count.Equals(0)) //分區鍵值初始化
                {
                    for (int i = 0; i < schools.Count; i++)
                    {
                        codes.Add("Base");
                    }
                }
                ////分开部署，就不需要，一站多用时，取消注释
                //if ($"{site}".Equals(BIConst.Global))
                //{
                //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
                //    tableClient = _azureStorage.GetCloudTableClient(BIConst.Global);
                //    blobClient = _azureStorage.GetBlobContainerClient(containerName: "0-public", BIConst.Global);
                //}
                StringBuilder msg = new();
                List<DelSchoolRel> delSchoolRels = new();

                foreach (var tempId in schools)
                {
                    int index = schools.IndexOf(tempId);
                    string code = codes[index];
                    //IES5實體學校刪除
                    if (!code.Equals("VirtualBase"))
                    {
                        List<string> scTchIds = new();
                        List<string> scStuIds = new();
                        string scTecSql = $"select value(c.id) from c where ARRAY_LENGTH(c.roles) > 0 and c.status = 'join'";
                        await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIteratorSql<string>(queryText: scTecSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Teacher-{tempId}") }))
                        {
                            scTchIds.Add(item);
                        }

                        string scStuSql = $"select value(c.id) from c";
                        await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Student").GetItemQueryIteratorSql<string>(queryText: scStuSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{tempId}") }))
                        {
                            scStuIds.Add(item);
                        }

                        var response = await cosmosClient.GetContainer("TEAMModelOS", "School").DeleteItemStreamAsync($"{tempId}", new PartitionKey($"Base"));
                        if (response.StatusCode == HttpStatusCode.NoContent)
                            msg.AppendLine($"{tmdName}【{tmdId}】删除学校，删除状态：{response.StatusCode}，删除ID:{tempId}");
                        else
                            delSchoolRels.Add(new DelSchoolRel() { id = $"{tempId}", code = "Base", type = 1, status = (int)HttpStatusCode.NoContent });

                        //删除学校信息中间
                        var resBiRel = await cosmosClient.GetContainer("TEAMModelOS", "School").DeleteItemStreamAsync($"{tempId}", new PartitionKey($"BIRel"));
                        if (resBiRel.StatusCode == HttpStatusCode.NoContent)
                            msg.AppendLine($"{tmdName}【{tmdId}】删除学校信息中间件，删除状态：{resBiRel.StatusCode}，删除ID:{tempId}");
                        else
                            delSchoolRels.Add(new DelSchoolRel() { id = $"{tempId}", code = "Base", type = 1, status = (int)response.StatusCode });

                        foreach (var item in scTchIds)
                        {
                            //学校教师信息
                            var tchRespnse = await cosmosClient.GetContainer("TEAMModelOS", "School").DeleteItemStreamAsync($"{item}", new PartitionKey($"Teacher-{tempId}"));
                            if (tchRespnse.StatusCode == HttpStatusCode.NoContent)
                                msg.AppendLine($"删除教师，删除状态：{tchRespnse.StatusCode}，删除ID:{item}");
                            else
                                delSchoolRels.Add(new DelSchoolRel() { id = $"{item}", code = $"Teacher-{tempId}", type = 2, status = (int)response.StatusCode });

                            //教师基础信息
                            var tchBaseResponse = await cosmosClient.GetContainer("TEAMModelOS", "Teacher").ReadItemStreamAsync($"{item}", new PartitionKey("Base"));
                            if (tchBaseResponse.StatusCode == HttpStatusCode.OK)
                            {
                                using var json = await JsonDocument.ParseAsync(tchBaseResponse.Content);
                                Teacher teacher = json.ToObject<Teacher>();
                                var tempSc = teacher.schools.Find(f => f.schoolId.Equals($"{tempId}"));
                                if (tempSc != null)
                                {
                                    teacher.schools.Remove(tempSc);
                                    await cosmosClient.GetContainer("TEAMModelOS", "Teacher").ReplaceItemAsync<Teacher>(teacher, teacher.id, new PartitionKey("Base"));
                                }
                            }
                            else
                                delSchoolRels.Add(new DelSchoolRel { id = $"{item}", code = "Base", type = 2, status = (int)response.StatusCode });
                        }
                        //删除学校学生
                        foreach (var item in scStuIds)
                        {
                            var stuRespnse = await cosmosClient.GetContainer("TEAMModelOS", "Student").DeleteItemStreamAsync($"{item}", new PartitionKey($"Base-{tempId}"));
                            if (stuRespnse.StatusCode == HttpStatusCode.NoContent)
                                msg.AppendLine($"删除学生，删除状态：{stuRespnse.StatusCode}，删除ID:{item}");
                            else
                                delSchoolRels.Add(new DelSchoolRel() { id = $"{item}", code = $"Base-{tempId}", type = 3, status = (int)response.StatusCode });
                        }
                    }
                    //虛擬學校刪除
                    else
                    {
                        var response = await cosmosClient.GetContainer("TEAMModelOS", "School").DeleteItemStreamAsync($"{tempId}", new PartitionKey($"VirtualBase"));
                        if (response.StatusCode == HttpStatusCode.NoContent)
                            msg.AppendLine($"{tmdName}【{tmdId}】删除学校，删除状态：{response.StatusCode}，删除ID:{tempId}");
                        else
                            delSchoolRels.Add(new DelSchoolRel() { id = $"{tempId}", code = "VirtualBase", type = 1, status = (int)response.StatusCode });
                    }
                }


                //string scTecSql = $"select value(c.id) from c where ARRAY_LENGTH(c.roles) > 0 and c.status = 'join'";
                //await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIteratorSql<string>(queryText: scTecSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Teacher-{schoolId}") }))
                //{
                //    scTchIds.Add(item);
                //}

                //string scStuSql = $"select value(c.id) from c";
                //await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIteratorSql<string>(queryText: scStuSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{schoolId}") }))
                //{
                //    scStuIds.Add(item);
                //}

                //var response = await cosmosClient.GetContainer("TEAMModelOS", "School").DeleteItemStreamAsync($"{schoolId}", new PartitionKey($"Base"));
                //if (response.Status == 204)
                //    msg.AppendLine($"{tmdName}【{tmdId}】删除学校，删除状态：{response.Status}，删除ID:{schoolId}");
                //else
                //    delSchoolRels.Add(new DelSchoolRel() { id = $"{schoolId}", code = "Base", type = 1, status = response.Status });

                //foreach (var item in scTchIds)
                //{
                //    var tchRespnse = await cosmosClient.GetContainer("TEAMModelOS", "School").DeleteItemStreamAsync($"{item}", new PartitionKey($"Teacher-{schoolId}"));
                //    if (tchRespnse.Status == 204)
                //        msg.AppendLine($"删除教师，删除状态：{tchRespnse.Status}，删除ID:{item}");
                //    else
                //        delSchoolRels.Add(new DelSchoolRel() { id = $"{item}", code = $"Teacher-{schoolId}", type = 2, status = response.Status });
                //}

                //foreach (var item in scStuIds)
                //{
                //    var stuRespnse = await cosmosClient.GetContainer("TEAMModelOS", "Student").DeleteItemStreamAsync($"{item}", new PartitionKey($"Base-{schoolId}"));
                //    if (stuRespnse.Status == 204)
                //        msg.AppendLine($"删除学生，删除状态：{stuRespnse.Status}，删除ID:{item}");
                //    else
                //        delSchoolRels.Add(new DelSchoolRel() { id = $"{item}", code = $"Base-{schoolId}", type = 3, status = response.Status });
                //}

                //保存操作记录
                await AzureStorageBlobExtensions.SaveBILog(blobClient, tableClient, "set-del", msg.ToString(), _dingDing, httpContext: HttpContext);
                if (delSchoolRels.Count > 0)
                    return Ok(new { state = 201, delSchoolRels });
                else
                    return Ok(new { state = 200 });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI,{_option.Location}  /schoolcheck/set-del \n {ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 依据学校统计     //已对接
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-schoolcnt")]
        public async Task<IActionResult> GetSchoolCnt(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("schoolId", out JsonElement schoolId)) return BadRequest();
            //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释
            var cosmosClient = _azureCosmos.GetCosmosClient();
            ////分开部署，就不需要，一站多用时，取消注释
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

            DateTimeOffset dtime = DateTimeOffset.UtcNow;
            var (weekS, weekE) = TimeHelper.GetStartOrEnd(dtime, "week");
            var (lastWeekS, lastWeekE) = TimeHelper.GetStartOrEnd(dtime, "lastweek");
            var (termS, termE) = TimeHelper.GetStartOrEnd(dtime, "term");
            var (lastTermS, lastTermE) = TimeHelper.GetStartOrEnd(dtime, "lastterm");
            var (yearS, yearE) = TimeHelper.GetStartOrEnd(dtime, "year");
            var (lastYearS, lastYearE) = TimeHelper.GetStartOrEnd(dtime, "lastYear");

            int tecCnt = 0;// 教师数量
            int classCnt = 0; //班级
            int stuCnt = 0; //学生
            int roomCnt = 0;  //智慧教室
            int lesCnt = 0;  //课例总数

            int weekLesCnt = 0;  //本周课例
            int lastWeekLesCnt = 0;  //上周课例
            int weekACTCnt = 0;  //本周活动
            int lastWeekACTCnt = 0;  //上周活动

            int termLesCnt = 0;  //本学期课例
            int lastTermLesCnt = 0;  //上学学期课例
            int termACTCnt = 0;  //本学期活动
            int lastTermActCnt = 0;  //上学学期活动

            int yearLesCnt = 0;  //今年课例
            int lastYearLesCnt = 0;  //去年课例
            int yearACTCnt = 0;  //今年活动
            int lastYearACTCnt = 0;  //去年活动

            SchoolInfo schoolInfo = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemAsync<SchoolInfo>($"{schoolId}", new PartitionKey("Base"));
            if (schoolInfo != null)
            {
                var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(schoolInfo.id, new PartitionKey("ProductSum"));
                if (response.StatusCode == HttpStatusCode.OK)
                {
                    using var json = await JsonDocument.ParseAsync(response.Content);
                    if (json.RootElement.TryGetProperty("serial", out JsonElement serial) && !serial.ValueKind.Equals(JsonValueKind.Null))
                        schoolInfo.serial = serial.ToObject<List<ProductSumInfos>>();

                    if (json.RootElement.TryGetProperty("service", out JsonElement service) && !service.ValueKind.Equals(JsonValueKind.Null))
                        schoolInfo.service = service.ToObject<List<ProductSumInfos>>();

                    if (json.RootElement.TryGetProperty("hard", out JsonElement hard) && !hard.ValueKind.Equals(JsonValueKind.Null))
                        schoolInfo.hard = hard.ToObject<List<SchoolProductSumDataHard>>();
                }

                schoolInfo.assists = await CommonFind.FindSchoolRoles(cosmosClient, schoolInfo.id, "assist");

                string currencySql = "select value(count(c.id)) from c";

                tecCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", currencySql, $"Teacher-{schoolInfo.id}"); // 教师数量
                classCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", currencySql, $"Class-{schoolInfo.id}"); //班级
                stuCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Student", currencySql, $"Base-{schoolInfo.id}"); //学生
                roomCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", currencySql, $"Room-{schoolInfo.id}");  //智慧教室
                lesCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", currencySql, $"LessonRecord-{schoolInfo.id}");  //课例总数

                weekLesCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{currencySql} where c.startTime >= {weekS} and  c.startTime<= {weekE}", $"LessonRecord-{schoolInfo.id}");  //本周课例
                lastWeekLesCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{currencySql} where c.startTime >= {lastWeekS} and  c.startTime<= {lastWeekE}", $"LessonRecord-{schoolInfo.id}");  //上周课例

                termLesCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{currencySql} where c.startTime >= {termS} and c.startTime <= {termE}", $"LessonRecord-{schoolInfo.id}");  //本学期课例
                lastTermLesCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{currencySql} where c.startTime >= {lastTermS} and c.startTime <= {lastTermE}", $"LessonRecord-{schoolInfo.id}"); //上学学期课例

                yearLesCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{currencySql} where c.startTime >= {yearS} and c.startTime <= {yearE}", $"LessonRecord-{schoolInfo.id}");  //今年课例
                lastYearLesCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{currencySql} where c.startTime >= {lastYearS} and c.startTime <= {lastYearE}", $"LessonRecord-{schoolInfo.id}"); //去年课例
                int ACTCnt = 0; //活动

                foreach (var type in StaticValue.activityTypes)
                {
                    ACTCnt += await CommonFind.GetSqlValueCount(cosmosClient, "Common", $"{currencySql}  where c.pk='{type}' and c.school='{schoolInfo.id}'");

                    weekACTCnt += await CommonFind.GetSqlValueCount(cosmosClient, "Common", $"{currencySql}  where c.pk='{type}' and c.school='{schoolInfo.id}' and c.createTime >= {weekS} and c.createTime <= {weekE} ");  //本周活动

                    lastWeekACTCnt += await CommonFind.GetSqlValueCount(cosmosClient, "Common", $"{currencySql}  where c.pk='{type}' and c.school='{schoolInfo.id}' and c.createTime >= {lastWeekS} and c.createTime <= {lastWeekE} ");  //上周活动

                    termACTCnt += await CommonFind.GetSqlValueCount(cosmosClient, "Common", $"{currencySql} where c.pk='{type}' and c.school='{schoolInfo.id}' and c.createTime >= {termS} and c.createTime <= {termE}", $"LessonRecord-{schoolInfo.id}");  //本学期活动
                    lastTermActCnt += await CommonFind.GetSqlValueCount(cosmosClient, "Common", $"{currencySql} where c.pk='{type}' and c.school='{schoolInfo.id}' and c.createTime >= {lastTermS} and c.createTime <= {lastTermE}", $"LessonRecord-{schoolInfo.id}");  //上学学期活动

                    yearACTCnt += await CommonFind.GetSqlValueCount(cosmosClient, "Common", $"{currencySql} where c.pk='{type}' and c.school='{schoolInfo.id}' and c.createTime >= {yearS} and c.createTime <= {yearE}", $"LessonRecord-{schoolInfo.id}");  //今年活动
                    lastYearACTCnt += await CommonFind.GetSqlValueCount(cosmosClient, "Common", $"{currencySql} where c.pk='{type}' and c.school='{schoolInfo.id}' and c.createTime >= {lastYearS} and c.createTime <= {lastYearE}", $"LessonRecord-{schoolInfo.id}");  //去年活动

                }

                //取得購買紀錄
                List<object> order = await GetOrderHisFromCoreBBAsync(schoolId.GetString());

                return Ok(new { state = RespondCode.Ok, tecCnt, classCnt, stuCnt, roomCnt, lesCnt, weekLesCnt, lastWeekLesCnt, weekACTCnt, lastWeekACTCnt, termLesCnt, lastTermLesCnt, termACTCnt, lastTermActCnt, yearLesCnt, lastYearLesCnt, yearACTCnt, lastYearACTCnt, schoolInfo, ACTCnt, buy = new { yearS, yearE, lastYearS, lastYearE, order } });
            }
            else
                return Ok(new { state = RespondCode.NotFound, msg = "未找到该学校信息！" });
        }

        /// <summary>
        /// 通过学校Id 统计学校信息   //已对接
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-analyse")]
        public async Task<IActionResult> GetAnalyse(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("schoolId", out JsonElement schoolId)) return BadRequest();

            //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释
            var cosmosClient = _azureCosmos.GetCosmosClient();
            ////分开部署，就不需要，一站多用时，取消注释
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

            RecSchool recSchool = new();
            var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync($"{schoolId}", new PartitionKey("Base"));
            if (response.StatusCode == HttpStatusCode.OK)
            {
                using var json = await JsonDocument.ParseAsync(response.Content);
                recSchool = json.ToObject<RecSchool>();
            }
            else return Ok(new { state = RespondCode.NotFound, msg = "未找到该学校" });


            DateTimeOffset dateTime = DateTimeOffset.UtcNow;
            var (days, daye) = TimeHelper.GetStartOrEnd(dateTime);
            var (lastDays, lastDaye) = TimeHelper.GetStartOrEnd(dateTime.AddDays(-1));
            var (years, yeare) = TimeHelper.GetStartOrEnd(dateTime, "year");
            var (lastYears, lastYeare) = TimeHelper.GetStartOrEnd(dateTime, "lastYear");

            int tchCnt = 0;  //教师数量
            int stuCnt = 0;  //学校数量
            int roomCnt = 0; //智慧教室数量
            int lessAllCant = 0; //课例总数

            int lessLastdayCnt = 0;  //昨天的课例
            int lessDayCnt = 0;    //今天的课例
            int lessLastYearCnt = 0; //去年的课例
            int lessYearCnt = 0; //今年的课例

            double allInterCnt = 0;   //所有互动
            double lastDayInterCnt = 0;   // 昨天课例互动
            double interCnt = 0;   // 今天课例互动
            double lastYearInterCnt = 0;   // 去年课例互动
            double yearInterCnt = 0;   // 今年课例互动

            int allActCnt = 0;    //所有活动
            int lastActCnt = 0;    //昨天活动
            int actCnt = 0;       //今天活动
            int lastYearActCnt = 0;       //去年活动
            int yearActCnt = 0;       //今年活动

            string commSql = "select value(count(c.id)) from c ";
            tchCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", commSql, $"Teacher-{schoolId}");
            stuCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Student", commSql, $"Base-{schoolId}");
            roomCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", commSql, $"Room-{schoolId}");
            lessAllCant = await CommonFind.GetSqlValueCount(cosmosClient, "School", commSql, $"LessonRecord-{schoolId}");

            string lessCode = $"LessonRecord-{schoolId}";
            string lessLastdaySql = $"{commSql} where c.startTime >= {lastDays} and c.startTime <= {lastDaye} ";
            lessLastdayCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", lessLastdaySql, lessCode);

            string lessDaySql = $"{commSql} where c.startTime >= {days} and c.startTime <= {daye} ";
            lessDayCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", lessDaySql, lessCode);

            string lessLastYarSql = $"{commSql} where c.startTime >= {lastYears} and c.startTime <= {lastYeare} ";
            lessLastYearCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", lessLastYarSql, lessCode);

            string lessYearSql = $"{commSql} where c.startTime >= {years} and c.startTime <= {yeare} ";
            lessYearCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", lessYearSql, lessCode);

            //课例互动
            string strInterSql = $"select value(sum(c.clientInteractionAverge)) from c";
            allInterCnt = await CommonFind.GetSqlValueDoubleCounnt(cosmosClient, "School", strInterSql, lessCode);

            string lastdayInterSql = $"{strInterSql} where c.startTime >= {lastDays} and c.startTime <= {lastDays} ";
            lastDayInterCnt = await CommonFind.GetSqlValueDoubleCounnt(cosmosClient, "School", lastdayInterSql, lessCode);

            string dayInterSql = $"{strInterSql} where c.startTime >= {days} and c.startTime <= {daye} ";
            interCnt = await CommonFind.GetSqlValueDoubleCounnt(cosmosClient, "School", dayInterSql, lessCode);

            string lastYarInterSql = $"{strInterSql} where c.startTime >= {lastYears} and c.startTime <= {lastYeare} ";
            lastYearInterCnt = await CommonFind.GetSqlValueDoubleCounnt(cosmosClient, "School", lastYarInterSql, lessCode);

            string yearInterSql = $"{strInterSql} where c.startTime >= {years} and c.startTime <= {yeare} ";
            yearInterCnt = await CommonFind.GetSqlValueDoubleCounnt(cosmosClient, "School", yearInterSql, lessCode);

            //活动
            allActCnt = await ActivityWay.GetCnt(cosmosClient, condSql: $"and c.school='{schoolId}'");

            string lastActSql = $" and c.school='{schoolId}' and c.createTime >= {lastDays} and c.createTime <= {lastDays}";
            lastActCnt = await ActivityWay.GetCnt(cosmosClient, condSql: lastActSql);

            string actSql = $" and c.school='{schoolId}' and c.createTime >= {days} and c.createTime <= {daye}";
            actCnt = await ActivityWay.GetCnt(cosmosClient, condSql: actSql);

            string lastYearActSql = $" and c.school='{schoolId}' and c.createTime >= {lastYears} and c.createTime <= {lastYeare}";
            lastYearActCnt = await ActivityWay.GetCnt(cosmosClient, condSql: lastYearActSql);

            string yearActSql = $" and c.school='{schoolId}' and c.createTime >= {years} and c.createTime <= {yeare}";
            yearActCnt = await ActivityWay.GetCnt(cosmosClient, condSql: yearActSql);

            return Ok(new { state = 200, recSchool, tchCnt, stuCnt, roomCnt, lessAllCant, lessLastdayCnt, lessDayCnt, lessLastYearCnt, lessYearCnt, allInterCnt, lastDayInterCnt, interCnt, lastYearInterCnt, yearInterCnt, allActCnt, lastActCnt, actCnt, lastYearActCnt, yearActCnt });
        }

        /// <summary>
        /// 学校每周课例趋势   //已对接
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-schooldate")]
        public async Task<IActionResult> GetSchoolDate(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("schoolId", out JsonElement schoolId)) return BadRequest();
            //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释
            var cosmosClient = _azureCosmos.GetCosmosClient();
            ////分开部署，就不需要，一站多用时，取消注释
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

            List<double> weekTrend = new();
            int year = DateTimeOffset.UtcNow.Year;
            int dayOfweek = (int)DateTimeOffset.Parse($"{year}-1-1").DayOfWeek;
            int currentTime = DateTimeOffset.UtcNow.DayOfYear / 7 + 1;
            int currentTime1 = DateTimeOffset.UtcNow.DayOfYear / 7;

            //获取当前学期所有的课程记录
            List<LessAnalyse> records = new List<LessAnalyse>();
            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIteratorSql<LessAnalyse>(queryText: $"select value(c) from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"LessonRecord-{schoolId}") }))
            {
                records.Add(item);
            }

            List<(string name, string periodId, int count)> grades = LessonStatisWay.GetGradeCount(records);
            var subs = records.GroupBy(x => x.subjectId).Where(w => !string.IsNullOrEmpty(w.Key)).Select(y => new { key = y.Key, count = y.ToList().Count }).ToList();

            var gradeCnt = grades.Select(x => new { x.name, x.periodId, value = x.count });
            var subCnt = subs.Select(x => new { name = x.key, value = x.count });

            List<LessonCount> scount = new();
            List<LessonCount> tcount = new();
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIteratorSql<LessonCount>(queryText: "select value(c) from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"LessonCount-{schoolId}-{year}") }))
            {
                scount.Add(item);
            }

            List<string> teacIds = await CommonFind.FindRolesId(cosmosClient, new List<string>() { $"{schoolId}" });
            foreach (var tId in teacIds)
            {
                var sqlTxtt = $"select value(c) from c where c.id='{tId}'";
                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryIteratorSql<LessonCount>(queryText: sqlTxtt, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"LessonCount-{year}") }))
                {
                    tcount.Add(item);
                }
            }

            int days = (year % 4 == 0 && year % 100 != 0 || year % 400 == 0) ? days = 366 : days = 365;

            List<List<double>> lessons = new();
            if (scount.Count > 0)
            {
                foreach (LessonCount item in scount)
                {
                    lessons.Add(item.beginCount);
                }
            }
            if (tcount.Count > 0)
            {
                foreach (LessonCount item in tcount)
                {
                    lessons.Add(item.beginCount);
                }
            }

            if (lessons.Count > 0)
            {
                var bmatrix = DenseMatrix.OfColumns(lessons);

                //开学第一周周内开课                   
                if (dayOfweek == 0)
                {
                    dayOfweek = 7;
                }
                //第一周多少天
                var dd = 7 - dayOfweek + 1;
                //一年有几周
                int sweeks = days / 7;
                //查询天数
                int dayYear = 0;
                if (currentTime > 0)
                {
                    for (int i = 0; i <= currentTime; i++)
                    {
                        if (i == 0)
                        {
                            var bsum = bmatrix.SubMatrix(dayYear, dd, 0, bmatrix.ColumnCount).ColumnSums().Sum();
                            dayYear += dd;
                            //weeks.Add(i, bsum);
                            weekTrend.Add(bsum);
                        }
                        else
                        {
                            var bsum = bmatrix.SubMatrix(dayYear, 7, 0, bmatrix.ColumnCount).ColumnSums().Sum();
                            dayYear += 7;
                            //weeks.Add(i, bsum);
                            weekTrend.Add(bsum);
                        }
                    }
                }
                //最后一周是否有余
                int stary = days - dayYear;
                if (stary > 0 && stary < 7)
                {
                    var bsum = bmatrix.SubMatrix(dayYear, stary - 1, 0, bmatrix.ColumnCount).ColumnSums().Sum();
                    //weeks.Add((sweeks + 1), bsum);
                    weekTrend.Add(bsum);
                }
            }

            return Ok(new { state = 200, gradeCnt, subCnt, weekTrend });
        }

        /// <summary>
        /// 顾问学校基础统计、以及排行    //已对接
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-assisscbase")]
        public async Task<IActionResult> GetAssistSchoolCnt(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            jsonElement.TryGetProperty("role", out JsonElement role);

            //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释
            var cosmosClient = _azureCosmos.GetCosmosClient();
            ////分开部署，就不需要，一站多用时，取消注释
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

            DateTimeOffset dateTime = DateTimeOffset.UtcNow;
            int scAllCnt = 0; //学校
            int tchAllCnt = 0;   //教师数量
            int tchMonthCnt = 0;  //教师本月新增
            int stuAllCnt = 0;   //所有学校学生
            int lessAllCnt = 0;  //课例总数
            int lessMthCnt = 0;   //本月新增
            List<ScRankCnt> scRankCnts = new();   //学校开课、上传， 课例排名
            var (start, end) = TimeHelper.GetStartOrEnd(dateTime, "month");

            List<string> schoolIds = new();
            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                switch ($"{role}")
                {
                    case "admin":
                        schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}", "admin");
                        break;
                    case "sales":
                        schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}", "sales");
                        break;
                    default:
                        schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");
                        break;
                }
            }
            else
                schoolIds = await CommonFind.GetValueSingle(cosmosClient, "School", "select value(c.id) from c", "Base");

            if (schoolIds.Count > 0)
            {
                string scIdSql = BICommonWay.ManyScSql("c.schoolId", schoolIds);
                string stuSql = $"select value(count(c.id)) from c where {scIdSql}";
                stuAllCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Student", stuSql);

                string scSql = BICommonWay.ManyScSql("c.school", schoolIds);
                string lessMoSql = $"select value(count(c.id)) from c where c.pk='LessonRecord' and {scSql} and c.startTime >= {start} and c.startTime <= {end}";
                lessMthCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", lessMoSql);

                List<SchoolBase> scInfos = new();
                string scInfoSql = $"select c.id,c.name,c.code,c.picture from c ";
                string idSql = BICommonWay.ManyScSql("c.id", schoolIds);
                scInfos = await CommonFind.GetObject<SchoolBase>(cosmosClient, "School", $"{scInfoSql} where {idSql}", "Base");

                List<string> tchMonIds = new();
                foreach (var item in scInfos)
                {
                    ScRankCnt lessRankCnt = new() { id = item.id, name = item.name, code = item.code, picture = item.picture };
                    string tchSql = $"select value(count(c.id)) from c where  array_contains(c.roles,'teacher',true) and c.createTime >= {start} and c.createTime <= {end}";
                    tchMonthCnt += await CommonFind.GetSqlValueCount(cosmosClient, "School", tchSql, $"Teacher-{item.id}");

                    string tchAllSql = $"select value(count(c.id)) from c where ARRAY_CONTAINS(c.roles,'teacher',true) and c.status = 'join'";
                    tchAllCnt += await CommonFind.GetSqlValueCount(cosmosClient, "School", tchAllSql, $"Teacher-{item.id}");

                    string lessALLSql = $"select value(count(c.id)) from c where c.pk='LessonRecord'";
                    lessAllCnt += await CommonFind.GetSqlValueCount(cosmosClient, "School", lessALLSql, $"LessonRecord-{item.id}");

                    string lessUpSql = $"select value(count(c.id)) from c where c.pk='LessonRecord' and c.upload = 0";
                    lessRankCnt.openCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", lessUpSql, $"LessonRecord-{item.id}");

                    string lessSql = $"select value(count(c.id)) from c where c.pk='LessonRecord' and c.upload = 1";
                    lessRankCnt.lessCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", lessSql, $"LessonRecord-{item.id}");

                    string interactSql = $"select value(sum(c.clientInteractionCount)) from c where c.pk='LessonRecord'";
                    lessRankCnt.interCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", interactSql, $"LessonRecord-{item.id}");

                    string actSql = $" and c.school='{item.id}'";
                    lessRankCnt.actCnt = await ActivityWay.GetCnt(cosmosClient, condSql: actSql);

                    scRankCnts.Add(lessRankCnt);
                }

                scAllCnt = schoolIds.Count();
                tchMonthCnt = tchMonIds.Distinct().Count();
            }

            return Ok(new { state = RespondCode.Ok, scAllCnt, tchAllCnt, tchMonthCnt, stuAllCnt, lessAllCnt, lessMthCnt, scRankCnts });
        }

        /// <summary>
        /// 一年学校和多个学校的课例、互动、活动统计分析    //已对接
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-assissc")]
        public async Task<IActionResult> GetAssisSc(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            jsonElement.TryGetProperty("role", out JsonElement role);
            jsonElement.TryGetProperty("schoolId", out JsonElement schooId);
            //jsonElement.TryGetProperty("site", out JsonElement site); //分开部署，就不需要，一站多用时，取消注释
            var cosmosClient = _azureCosmos.GetCosmosClient();

            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

            List<YearCnt> yearCnts = new();  //当前的课例，活动，互动统计
            List<string> schoolIds = new();
            string strSql = "SELECT value(count(c.id)) FROM c where c.pk='LessonRecord'";
            string scSql = null;
            if (!string.IsNullOrEmpty($"{tmdId}") && string.IsNullOrEmpty($"{schooId}"))
            {
                //List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");

                if (!string.IsNullOrEmpty($"{tmdId}"))
                {
                    switch ($"{role}")
                    {
                        case "admin":
                            schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}", "admin");
                            scSql = BICommonWay.ManyScSql("c.school", schoolIds);
                            break;
                        case "sales":
                            schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}", "sales");
                            scSql = BICommonWay.ManyScSql("c.school", schoolIds);
                            break;
                        default:
                            schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");
                            scSql = BICommonWay.ManyScSql("c.school", schoolIds);
                            break;
                    }
                }
            }
            else if (!string.IsNullOrEmpty($"{schooId}") && string.IsNullOrEmpty($"{tmdId}"))
                scSql = $"c.school = '{schooId}'";
            else 
            {
                schoolIds = await CommonFind.GetValueSingle(cosmosClient, "School", "select value(c.id) from c", "Base");

                if (schoolIds.Count == 0)
                    return Ok(new { state = RespondCode.Ok, yearCnts, });
                else
                    scSql = BICommonWay.ManyScSql("c.school", schoolIds);
            }

            //if ((string.IsNullOrEmpty($"{tmdId}") && string.IsNullOrEmpty($"{schooId}")) || (!string.IsNullOrEmpty($"{tmdId}") && !string.IsNullOrEmpty($"{schooId}")))
                
            List<StartEndTime> mthStartEnds = await TimeHelper.GetYearSataMthCtMth(DateTimeOffset.UtcNow);
            if (mthStartEnds.Count > 0)
            {
                foreach (var item in mthStartEnds)
                {
                    YearCnt yearCnt = new() { name = item.date };
                    string selessSql = $"{strSql} and {scSql.ToString()} and c.startTime >= {item.start} and c.startTime <= {item.end}";
                    yearCnt.lessCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", selessSql);

                    string interactSql = $"select value(sum(c.clientInteractionAverge)) from c where c.pk='LessonRecord' and {scSql} and c.startTime >= {item.start} and c.startTime <= {item.end}";
                    //double iCnt = await CommonFind.GetSqlValueDoubleCounnt(cosmosClient, "School", interactSql);
                    yearCnt.interCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", interactSql);

                    string mthActSql = $"and {scSql} and c.createTime >= {item.start} and c.createTime <= {item.end}";
                    yearCnt.actCnt = await ActivityWay.GetCnt(cosmosClient, condSql: mthActSql);

                    yearCnts.Add(yearCnt);
                }
            }

            return Ok(new { state = RespondCode.Ok, yearCnts, });
        }

        /// <summary>
        /// 顾问学校统计    //已对接
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-assissccnt")]
        public async Task<IActionResult> GetAssisScCnt(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            jsonElement.TryGetProperty("role", out JsonElement role);
            //jsonElement.TryGetProperty("site", out JsonElement site); //分开部署，就不需要，一站多用时，取消注释
            var cosmosClient = _azureCosmos.GetCosmosClient();
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            DateTimeOffset dateTime = DateTimeOffset.UtcNow;
            var (days, daye) = TimeHelper.GetStartOrEnd(dateTime);
            var (lastDays, lastDaye) = TimeHelper.GetStartOrEnd(dateTime.AddDays(-1));
            var (years, yeare) = TimeHelper.GetStartOrEnd(dateTime, "year");
            var (lastYears, lastYeare) = TimeHelper.GetStartOrEnd(dateTime, "lastYear");

            int lessAll = 0;  //所以课例
            int lessLastdayCnt = 0;  //昨天的课例
            int lessDayCnt = 0;    //今天的课例
            int lessLastYearCnt = 0; //去年的课例
            int lessYearCnt = 0; //今年的课例

            double interAll = 0;    //所有互动总数
            double lastDayInterCnt = 0;   // 昨天课例互动
            double interCnt = 0;   // 今天课例互动
            double lastYearInterCnt = 0;   // 去年课例互动
            double yearInterCnt = 0;   // 今年课例互动

            int actAllCnt = 0;   //所有活动
            int lastActCnt = 0;    //昨天活动
            int actCnt = 0;       //今天活动
            int lastYearActCnt = 0;       //去年活动
            int yearActCnt = 0;       //今年活动

            List<string> schoolIds = new();
            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                switch ($"{role}")
                {
                    case "admin":
                        schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}", "admin");
                        break;
                    case "sales":
                        schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}", "sales");
                        //scSql = BICommonWay.ManyScSql("c.school", schoolIds);
                        break;
                    default:
                        schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");
                        break;
                }
            }
            else
                schoolIds = await CommonFind.GetValueSingle(cosmosClient, "School", "select value(c.id) from c", "Base");

            if (schoolIds.Count > 0)
            {
                string scSql = BICommonWay.ManyScSql("c.school", schoolIds);

                string strSql = "SELECT value(count(c.id)) FROM c where c.pk='LessonRecord'";
                //课例
                lessAll = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{strSql} and {scSql}");

                string lessLastdaySql = $"{strSql} and {scSql} and c.startTime >= {lastDays} and c.startTime <= {lastDaye} ";
                lessLastdayCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", lessLastdaySql);

                string lessDaySql = $"{strSql} and {scSql} and c.startTime >= {days} and c.startTime <= {daye} ";
                lessDayCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", lessDaySql);

                string lessLastYarSql = $"{strSql} and {scSql} and c.startTime >= {lastYears} and c.startTime <= {lastYeare} ";
                lessLastYearCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", lessLastYarSql);

                string lessYearSql = $"{strSql} and {scSql} and c.startTime >= {years} and c.startTime <= {yeare} ";
                lessYearCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", lessYearSql);

                //课例互动
                string strInterSql = $"select value(sum(c.clientInteractionCount)) from c where c.pk='LessonRecord' and {scSql}";
                interAll = await CommonFind.GetSqlValueDoubleCounnt(cosmosClient, "School", strInterSql);

                string lastdayInterSql = $"{strInterSql} and c.startTime >= {lastDays} and c.startTime <= {lastDaye} ";
                lastDayInterCnt = await CommonFind.GetSqlValueDoubleCounnt(cosmosClient, "School", lastdayInterSql);

                string dayInterSql = $"{strInterSql} and c.startTime >= {days} and c.startTime <= {daye} ";
                interCnt = await CommonFind.GetSqlValueDoubleCounnt(cosmosClient, "School", dayInterSql);

                string lastYarInterSql = $"{strInterSql} and c.startTime >= {lastYears} and c.startTime <= {lastYeare} ";
                lastYearInterCnt = await CommonFind.GetSqlValueDoubleCounnt(cosmosClient, "School", lastYarInterSql);

                string yearInterSql = $"{strInterSql} and c.startTime >= {years} and c.startTime <= {yeare} ";
                yearInterCnt = await CommonFind.GetSqlValueDoubleCounnt(cosmosClient, "School", yearInterSql);

                //活动
                string actAllSql = $"and {scSql} ";
                actAllCnt = await ActivityWay.GetCnt(cosmosClient, condSql: actAllSql);

                string lastActSql = $"and {scSql} and c.createTime >= {lastDays} and c.createTime <= {lastDaye}";
                lastActCnt = await ActivityWay.GetCnt(cosmosClient, condSql: lastActSql);

                string actSql = $"and {scSql} and c.createTime >= {days} and c.createTime <= {daye}";
                actCnt = await ActivityWay.GetCnt(cosmosClient, condSql: actSql);

                string lastYearActSql = $" and {scSql} and c.createTime >= {lastYears} and c.createTime <= {lastYeare}";
                lastYearActCnt = await ActivityWay.GetCnt(cosmosClient, condSql: lastYearActSql);

                string yearActSql = $"and {scSql} and c.createTime >= {years} and c.createTime <= {yeare}";
                yearActCnt = await ActivityWay.GetCnt(cosmosClient, condSql: yearActSql);
            }

            return Ok(new { state = RespondCode.Ok, lessAll, lessLastdayCnt, lessDayCnt, lessLastYearCnt, lessYearCnt, interAll, lastDayInterCnt, interCnt, lastYearInterCnt, yearInterCnt, actAllCnt, lastActCnt, actCnt, lastYearActCnt, yearActCnt });
        }

        /// <summary>
        /// 查询学校基础信息，和学校年级、科目数据统计  //已对接
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-ratio")]
        public async Task<IActionResult> GetRatio(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("schoolId", out JsonElement schoolId)) return BadRequest();

            //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释
            var cosmosClient = _azureCosmos.GetCosmosClient();
            ////分开部署，就不需要，一站多用时，取消注释
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            AnSchool school = new();  //学校基础基础信息

            var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync($"{schoolId}", new PartitionKey("Base"));
            if (response.StatusCode == HttpStatusCode.OK)
            {
                using var json = await JsonDocument.ParseAsync(response.Content);
                school = json.ToObject<AnSchool>();
            }
            else return Ok(new { state = RespondCode.NotFound, msg = "未找到该学校" });
            List<LessAnalyse> lessAnalyses = new();

            await foreach (var lessItem in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIteratorSql<LessAnalyse>(queryText: "select c.id,c.code,c.periodId,c.subjectId,c.grade from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"LessonRecord-{school.id}") }))
            {
                lessAnalyses.Add(lessItem);
            }
            List<(string name, string periodId, int count)> grades = LessonStatisWay.GetGradeCount(lessAnalyses);
            var subs = lessAnalyses.GroupBy(x => x.subjectId).Where(y => !string.IsNullOrEmpty(y.Key)).Select(y => new { key = y.Key, count = y.ToList().Count }).ToList();

            var gradeCnt = grades.Select(x => new { x.name, x.periodId, value = x.count });
            var subCnt = subs.Select(x => new { name = x.key, value = x.count });

            var responseProduct = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(school.id, new PartitionKey("ProductSum"));
            if (responseProduct.StatusCode == HttpStatusCode.OK)
            {
                using var json = await JsonDocument.ParseAsync(responseProduct.Content);
                if (json.RootElement.TryGetProperty("serial", out JsonElement _serial) && !_serial.ValueKind.Equals(JsonValueKind.Null))
                {
                    school.serial = _serial.ToObject<List<SchoolProductSumData>>().Select(x => x.prodCode).ToList();
                }
                if (json.RootElement.TryGetProperty("service", out JsonElement _service) && !_service.ValueKind.Equals(JsonValueKind.Null))
                {
                    school.service = _service.ToObject<List<SchoolProductSumData>>().Select(x => x.prodCode).ToList();
                }
                if (json.RootElement.TryGetProperty("hard", out JsonElement _hard) && !_hard.ValueKind.Equals(JsonValueKind.Null))
                {
                    school.hard = _hard.ToObject<List<SchoolProductSumDataHard>>().Select(x => x.prodCode).ToList();
                }
            }

            return Ok(new { state = RespondCode.Ok, school, gradeCnt, subCnt });
        }

        /// <summary>
        /// 学校简要信息
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-schools")]
        public async Task<IActionResult> GetSchools (JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("areaId", out JsonElement areaId);
            List<ScBriefInfo> scBriefs = new();
            var cosmosClient = _azureCosmos.GetCosmosClient();

            StringBuilder sql = new("select c.id,c.name,c.picture,c.areaId,c.size,c.period from c ");
            if (!string.IsNullOrEmpty($"{areaId}"))
                sql.Append($" where c.areaId = '{areaId}'");

            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS,"School").GetItemQueryIteratorSql<ScBriefInfo>(queryText: sql.ToString(), requestOptions:new QueryRequestOptions() { PartitionKey = new PartitionKey("Base")}))
            {
                scBriefs.Add(item);
            }

            return Ok(new { state = RespondCode.Ok, scBriefs });
        }

        /// <summary>
        /// 通过tmdID 账户查询学校专业和模组   //已对接
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-proded")]
        public async Task<IActionResult> GetProdEd(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            jsonElement.TryGetProperty("role", out JsonElement role);
            //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释
            var cosmosClient = _azureCosmos.GetCosmosClient();
            ////分开部署，就不需要，一站多用时，取消注释
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

            int adCnt = 0;
            int allCnt = 0;
            List<ScProdEd> scInfos = new();
            List<string> products = new();
            //List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");
            List<string> schoolIds = new();
            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                switch ($"{role}")
                {
                    case "admin":
                        schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}", "admin");
                        break;
                    case "sales":
                        schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}", "sales");
                        break;
                    default:
                        schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");
                        break;
                }
            }
            else
                schoolIds = await CommonFind.GetValueSingle(cosmosClient, "School", "select value(c.id) from c", "Base");

            if (schoolIds.Count > 0)
            {
                string scInfoSql = $"select c.id,c.name,c.code,c.picture,c.type,c.size,c.scale from c ";
                string idSql = BICommonWay.ManyScSql("c.id", schoolIds);

                scInfos = await CommonFind.GetObject<ScProdEd>(cosmosClient, "School", $"{scInfoSql} where {idSql}", "Base");

                string serialSql = "SELECT value(count(ARRAY_LENGTH(c.deviceBound))) FROM c ";
                string serialAllSql = "SELECT value(sum((c.deviceMax))) FROM c";
                if (schoolIds.Count > 0)
                {
                    foreach (var scProd in scInfos)
                    {
                        adCnt += await CommonFind.GetSqlValueCount(cosmosClient, "School", serialSql, $"Product-{scProd.id}");
                        allCnt += await CommonFind.GetSqlValueCount(cosmosClient, "School", serialAllSql, $"Product-{scProd.id}");

                        var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(scProd.id, new PartitionKey("ProductSum"));
                        if (response.StatusCode == HttpStatusCode.OK)
                        {
                            using var json = await JsonDocument.ParseAsync(response.Content);
                            if (json.RootElement.TryGetProperty("serial", out JsonElement serial) && !serial.ValueKind.Equals(JsonValueKind.Null))
                            {
                                List<string> serials = serial.ToObject<List<SchoolProductSumData>>().Select(x => x.prodCode).ToList();
                                scProd.serial = serials;
                                products.AddRange(serials);
                            }
                            if (json.RootElement.TryGetProperty("service", out JsonElement service) && !service.ValueKind.Equals(JsonValueKind.Null))
                            {
                                List<string> services = service.ToObject<List<SchoolProductSumData>>().Select(x => x.prodCode).ToList();
                                scProd.service = services;
                                products.AddRange(services);
                            }
                            if (json.RootElement.TryGetProperty("hard", out JsonElement hard) && !hard.ValueKind.Equals(JsonValueKind.Null))
                            {
                                List<string> hards = hard.ToObject<List<SchoolProductSumDataHard>>().Select(x => x.prodCode).ToList();
                                scProd.hard = hards;
                                products.AddRange(hards);
                            }
                        }
                    }

                    //scInfos.ForEach(async scProd =>
                    //{
                    //    var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(scProd.id, new PartitionKey("ProductSum"));
                    //    if (response.Status == 200)
                    //    {
                    //        using var json = await JsonDocument.ParseAsync(response.Content);
                    //        if (json.RootElement.TryGetProperty("serial", out JsonElement serial) && !serial.ValueKind.Equals(JsonValueKind.Null))
                    //        {
                    //            scProd.serial = serial.ToObject<List<SchoolProductSumData>>().Select(x => x.prodCode).ToList(); 
                    //        }
                    //        if (json.RootElement.TryGetProperty("service", out JsonElement service) && !service.ValueKind.Equals(JsonValueKind.Null))
                    //        {
                    //            scProd.service = service.ToObject<List<SchoolProductSumData>>().Select(x => x.prodCode).ToList();
                    //        }
                    //        if (json.RootElement.TryGetProperty("hard", out JsonElement hard) && !hard.ValueKind.Equals(JsonValueKind.Null))
                    //        {
                    //            scProd.hard = hard.ToObject<List<SchoolProductSumDataHard>>().Select(x => x.prodCode).ToList(); 
                    //        }
                    //    }
                    //});

                }
            }

            var productAn = products.GroupBy(g => g).Select(s => new { key = s.Key, cnt = s.ToList().Count }).ToList();

            return Ok(new { state = RespondCode.Ok, allCnt, adCnt, scInfos, productAn });
        }

        /// <summary>
        /// 通过学校Id查询详情   数据管理工具——查询工具
        /// </summary>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-info")]
        public async Task<IActionResult> GetSchool(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("schoolId", out JsonElement schoolId);
            jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释
            var cosmosClient = _azureCosmos.GetCosmosClient();
            ////分开部署，就不需要，一站多用时，取消注释
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            StringBuilder sqlTxt = new("select * from c");
            if (!string.IsNullOrEmpty($"{schoolId}"))
            {
                sqlTxt.Append($" where c.id='{schoolId}'");
            }

            List<School> schools = new();

            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIteratorSql(queryText: sqlTxt.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
            {
                using var json = await JsonDocument.ParseAsync(item.Content);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                {
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        schools.Add(obj.ToObject<School>());
                    }
                }
            }

            return Ok(new { state = 200, schools });
        }

        /// <summary>
        /// 依据区域Id查询区域空间统计
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-spance")]
        public async Task<IActionResult> GetAreaSpace(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("areaId", out JsonElement areaId);
            jsonElement.TryGetProperty("schoolId", out JsonElement schoolId);
            //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释
            var cosmosClient = _azureCosmos.GetCosmosClient();
            ////分开部署，就不需要，一站多用时，取消注释
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            long allSize = 0;    //全部大小
            int teacherSpace = 0;  //学校分配给教师的空间
            long useSize = 0;   //已使用大小 
            Dictionary<string, double?> useSpaceInfo = new();  //学校使用详情

            //查询区域所有学校
            if (!string.IsNullOrEmpty($"{areaId}"))
            {
                List<string> schools = new();
                schools = await CommonFind.FindScIds(cosmosClient, $"select value(c.id) from c where c.areaId='{areaId}'", "Base");

                allSize = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"select value(sum(c.size)) from c where c.areaId='{areaId}'", "Base"); //区域所有学校空间

                foreach (var school in schools)
                {
                    await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIteratorSql(queryText: $"SELECT sum(c.size) as size FROM c ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Teacher-{school}") }))
                    {
                        var json = await JsonDocument.ParseAsync(item.Content);
                        foreach (var elmt in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            if (elmt.TryGetProperty("size", out JsonElement _size) && _size.ValueKind.Equals(JsonValueKind.Number))
                            {
                                teacherSpace += _size.GetInt32();
                                break;
                            }
                        }
                    }

                    long blobsize = 0;
                    RedisValue value = _azureRedis.GetRedisClient(8).HashGet($"Blob:Record", $"{school}");
                    if (!value.IsNullOrEmpty)
                    {
                        JsonElement record = value.ToString().ToObject<JsonElement>();
                        if (record.TryGetInt64(out blobsize))
                        {
                        }
                    }
                    else
                    {
                        var storageClient = _azureStorage.GetBlobContainerClient("school");
                        var size = await storageClient.GetBlobsCatalogSize();
                        await _azureRedis.GetRedisClient(8).HashSetAsync($"Blob:Record", $"{school}", size.Item1);
                        foreach (var key in size.Item2.Keys)
                        {
                            await _azureRedis.GetRedisClient(8).SortedSetRemoveAsync($"Bolb:Catalog:{school}", key);
                            await _azureRedis.GetRedisClient(8).SortedSetIncrementAsync($"Bolb:Catalog:school", key, size.Item2[key].HasValue ? size.Item2[key].Value : 0);
                        }

                        useSize += size.Item1.Value;
                        useSpaceInfo = useSpaceInfo.Concat(size.Item2).GroupBy(g => g.Key).ToDictionary(k => k.Key, k => k.Sum(kvp => kvp.Value));   //lamebda表达式
                        //useSpaceInfo = (from e in useSpaceInfo.Concat(size.Item2) group e by e.Key into g select new { Name = g.Key, value = g.Sum(kvp => kvp.Value) }).ToDictionary(item => item.Name, item => item.value);  //linq 方式合并
                    }

                    SortedSetEntry[] Scores = _azureRedis.GetRedisClient(8).SortedSetRangeByScoreWithScores($"Blob:Catalog:{school}");
                    if (Scores != null)
                    {
                        Dictionary<string, double?> catalog = new();
                        foreach (var score in Scores)
                        {
                            double val = score.Score;
                            string key = score.Element.ToString();
                            catalog.Add(key, val);
                        }
                        useSpaceInfo = useSpaceInfo.Concat(catalog).GroupBy(g => g.Key).ToDictionary(k => k.Key, k => k.Sum(kvp => kvp.Value));  //lamebda表达式
                    }
                    else
                    {
                        var client = _azureStorage.GetBlobContainerClient("school");
                        var size = await client.GetBlobsCatalogSize();
                        await _azureRedis.GetRedisClient(8).HashSetAsync($"Blob:Record", $"{school}", size.Item1);
                        foreach (var key in size.Item2.Keys)
                        {
                            await _azureRedis.GetRedisClient(8).SortedSetRemoveAsync($"Blob:Catalog:{school}", key);
                            await _azureRedis.GetRedisClient(8).SortedSetIncrementAsync($"Blob:Catalog:{school}", key, size.Item2[key].HasValue ? size.Item2[key].Value : 0);
                        }
                        useSize += size.Item1.Value;
                        useSpaceInfo = useSpaceInfo.Concat(size.Item2).GroupBy(g => g.Key).ToDictionary(k => k.Key, k => k.Sum(kvp => kvp.Value));   //lamebda表达式
                    }
                }
            }

            //查询学校
            if (!string.IsNullOrEmpty($"{schoolId}"))
            {
                allSize = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"SELECT value(c.size) FROM c where c.id='{schoolId}'", "Base");

                teacherSpace = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"SELECT value(sum(c.size)) FROM c", $"Teacher-{schoolId}");

                long blobsize = 0;
                RedisValue value = _azureRedis.GetRedisClient(8).HashGet($"Blob:Record", $"{schoolId}");
                if (!value.IsNullOrEmpty)
                {
                    JsonElement record = value.ToString().ToObject<JsonElement>();
                    if (record.TryGetInt64(out blobsize))
                    {
                        useSize = blobsize;
                    }
                }
                else
                {
                    var storageClient = _azureStorage.GetBlobContainerClient("school");
                    var size = await storageClient.GetBlobsCatalogSize();
                    await _azureRedis.GetRedisClient(8).HashSetAsync($"Blob:Record", $"{schoolId}", size.Item1);
                    foreach (var key in size.Item2.Keys)
                    {
                        await _azureRedis.GetRedisClient(8).SortedSetRemoveAsync($"Bolb:Catalog:{schoolId}", key);
                        await _azureRedis.GetRedisClient(8).SortedSetIncrementAsync($"Bolb:Catalog:school", key, size.Item2[key].HasValue ? size.Item2[key].Value : 0);
                    }
                    useSpaceInfo = size.Item2;
                }

                Dictionary<string, double?> catalog = new();
                SortedSetEntry[] Scores = _azureRedis.GetRedisClient(8).SortedSetRangeByScoreWithScores($"Blob:Catalog:{schoolId}");
                if (Scores != null)
                {
                    foreach (var score in Scores)
                    {
                        double val = score.Score;
                        string key = score.Element.ToString();
                        catalog.Add(key, val);
                    }

                    useSpaceInfo = catalog;
                }
                else
                {
                    var client = _azureStorage.GetBlobContainerClient("school");
                    var size = await client.GetBlobsCatalogSize();
                    await _azureRedis.GetRedisClient(8).HashSetAsync($"Blob:Record", $"{schoolId}", size.Item1);
                    foreach (var key in size.Item2.Keys)
                    {
                        await _azureRedis.GetRedisClient(8).SortedSetRemoveAsync($"Blob:Catalog:{schoolId}", key);
                        await _azureRedis.GetRedisClient(8).SortedSetIncrementAsync($"Blob:Catalog:{schoolId}", key, size.Item2[key].HasValue ? size.Item2[key].Value : 0);
                    }

                    useSize = size.Item1.Value;
                    useSpaceInfo = size.Item2;
                }
            }

            return Ok(new { state = 200, allSize, useSize, teacherSpace, useSpaceInfo });
        }

        /// <summary>
        /// 移交学校顾问
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-shift")]
        public async Task<IActionResult> GetShift(JsonElement jsonElement)
        {
            var (_tmdId, _tmdName, pic, did, dname, dpic) = HttpJwtAnalysis.JwtXAuthBI(HttpContext.GetXAuth("AuthToken"), _option);
            if (!jsonElement.TryGetProperty("school", out JsonElement _school)) return BadRequest();
            //if (!jsonElement.TryGetProperty("teacher", out JsonElement _teacher)) return BadRequest();
            if (!jsonElement.TryGetProperty("shiftTeacher", out JsonElement _shiftTeacher)) return BadRequest();
            //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释

            var cosmosClient = _azureCosmos.GetCosmosClient();
            var tableClient = _azureStorage.GetCloudTableClient();
            var blobClient = _azureStorage.GetBlobContainerClient(containerName: "0-public");
            //if ($"{site}".Equals(BIConst.Global))
            //{
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            //    tableClient = _azureStorage.GetCloudTableClient(BIConst.Global);
            //    blobClient = _azureStorage.GetBlobContainerClient(containerName: "0-public", BIConst.Global);
            //}

            SchoolTeacher schoolTeacher = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemAsync<SchoolTeacher>($"{_tmdId}", new PartitionKey($"Teacher-{_school}"));
            if (schoolTeacher.roles.IsNotEmpty() && schoolTeacher.roles.Contains("assist"))
            {
                schoolTeacher.roles.Remove("assist");
                SchoolTeacher newSchoolTeacher = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemAsync<SchoolTeacher>($"{_shiftTeacher}", new PartitionKey($"Teacher-{_school}"));
                if (!newSchoolTeacher.roles.Contains("assist"))
                {
                    newSchoolTeacher.roles.Add("assist");
                    newSchoolTeacher = await cosmosClient.GetContainer("TEAMModelOS", "School").ReplaceItemAsync<SchoolTeacher>(newSchoolTeacher, $"{_shiftTeacher}", new PartitionKey($"Teacher-{_school}"));
                }

                await cosmosClient.GetContainer("TEAMModelOS", "School").ReplaceItemAsync<SchoolTeacher>(schoolTeacher, $"{_tmdId}", new PartitionKey($"Teacher-{_school}"));

                School schoolBase = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemAsync<School>($"{_school}", new PartitionKey($"Base"));

                //发送消息实体
                string shiftCode = "shift-assist";
                //保存操作记录
                await AzureStorageBlobExtensions.SaveBILog(blobClient, tableClient, "schoolTeacher-add", $"{_tmdName}【{_tmdId}】移交顾问给{newSchoolTeacher.name}【{newSchoolTeacher.id}】", _dingDing, httpContext: HttpContext);
                return Ok(new { state = 200 });
            }
            else { return Ok(new { status = 201, msg = $"{_tmdName}【{_tmdId}】账号不是顾问" }); }
        }

        /// <summary>
        /// 管理员所管理的学校信息
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-managescs")]
        public async Task<IActionResult> GetManageSclist(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return BadRequest();
            jsonElement.TryGetProperty("areaId", out JsonElement areaId);

            var cosmosClient = _azureCosmos.GetCosmosClient();
            List<ManageScInfo> mScInfos = new();
            List<ManageScInfo> noScInfos = new();
            List<string> scIds = new();

            string mScSql = $"SELECT value(REPLACE(c.code, 'Teacher-', '')) FROM c where array_contains(c.roles,'admin',true) and c.pk='Teacher' and c.id='{tmdId}'";
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIteratorSql<string>(queryText: mScSql, requestOptions: new QueryRequestOptions() { }))
            {
                scIds.Add(item);
            }

            if (scIds.Count > 0)
            {
                string scInfoSql = $"select c.id,c.name,c.code,c.picture,c.region,c.province,c.city,c.dist,c.areaId from c ";
                string idSql = BICommonWay.ManyScSql("c.id", scIds);
                string scSql = $"{scInfoSql} where {idSql}";
                if (!string.IsNullOrEmpty($"{areaId}"))
                {
                    scSql = $"{scInfoSql} where {idSql} and c.areaId='{areaId}'";
                }

                mScInfos = await CommonFind.GetObject<ManageScInfo>(cosmosClient, "School", scSql, "Base");
            }

            if (!string.IsNullOrEmpty($"{areaId}"))
            {
                string noScInfoSql = $"select c.id,c.name,c.code,c.picture,c.region,c.province,c.city,c.dist,c.areaId from c where c.areaId='{areaId}'";
                List<ManageScInfo> areaAllScInfos = await CommonFind.GetObject<ManageScInfo>(cosmosClient, "School", noScInfoSql, "Base");

                noScInfos = areaAllScInfos.Where(w => !mScInfos.Any(a => a.id == w.id)).ToList();
                //noScInfos = areaAllScInfos.Except(mScInfos).ToList();
            }

            return Ok(new { state = RespondCode.Ok, mScInfos, noScInfos });
        }

        /// <summary>
        /// 查询学校简单信息
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-scsimple")]
        public async Task<IActionResult> GetScSimple(JsonElement jsonElement)
        {
            var cosmosClient = _azureCosmos.GetCosmosClient();
            jsonElement.TryGetProperty("scName", out JsonElement scName);

            StringBuilder scSql = new("select c.id,c.name,c.picture,c.createTime from c");
            if (!string.IsNullOrEmpty($"{scName}"))
                scSql.Append($" where c.name='{scName}'");

            List<ScSimple> scSimple = new();

            await foreach (var itemSchool in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIteratorSql<ScSimple>(queryText: scSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
            {
                scSimple.Add(itemSchool);
            }

            return Ok(new { state = RespondCode.Ok, scSimple });
        }
        
        #region 购买记录

        /// <summary>
        /// 取得CoreBB學校訂單履歷資訊  暂时这样处理
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        private async Task<List<object>> GetOrderHisFromCoreBBAsync(string schoolCode)
        {
            List<object> result = new List<object>();
            try
            {
                string url = _configuration.GetValue<string>("HaBookAuth:CoreBBAPI") + "/ies5/get-school-order";
                string AccessToken = await getCoreAccessToken();
                var client = _httpClient.CreateClient();
                client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", AccessToken);
                string paramJson = JsonConvert.SerializeObject(new { school_code = schoolCode });
                var content = new StringContent(paramJson, Encoding.UTF8, "application/json");
                HttpResponseMessage responseMessage = await client.PostAsync(url, content);
                if (responseMessage.StatusCode == HttpStatusCode.OK)
                {
                    string responseBody = responseMessage.Content.ReadAsStringAsync().Result;
                    result = System.Text.Json.JsonSerializer.Deserialize<List<object>>(responseBody.ToString());
                }
                return result;
            }
            catch (Exception ex)
            {
                return result;
            }
        }

        /// <summary>
        /// 取得某學校產品購買紀錄
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-school-product")]
        public async Task<IActionResult> GetSchoolProductInfo(JsonElement request)
        {
            try
            {
                if (!request.TryGetProperty("school_code", out JsonElement school_code)) return BadRequest();
                var db = _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "School");
                var r8 = _azureRedis.GetRedisClient(8);

                List<SchoolProductSerial> serial = new(); //承接DB資料用：序號
                List<SerialInfoBaseWithdeviceBoundExt> serialResult = new(); //最後要輸出的序號結果
                List<SchoolProductService> serviceProduct = new(); //承接DB資料用：服務
                List<SchoolProductOrder> serviceOrder = new(); //服務各產品購買紀錄
                List<SchoolProductSumDataService> serviceSum = new(); //服務各產品購買紀錄
                List<SchoolProductHard> hard = new(); //承接DB資料用：硬體
                SchoolProductSum productSum = new(); //承接DB資料用：產品目前狀態 
                List<SchoolProductSumProdInfo> prodinfo = new(); //產品資訊列表(名稱、八碼)
                long UTCNow = DateTimeOffset.UtcNow.ToUnixTimeSeconds();

                ////取得各產品目前可得數量
                var response = await db.ReadItemStreamAsync(school_code.ToString(), new PartitionKey($"ProductSum"));
                if (response.StatusCode == HttpStatusCode.OK)
                {
                    using var json = await JsonDocument.ParseAsync(response.Content);
                    productSum = json.ToObject<SchoolProductSum>();
                    prodinfo = productSum.prodinfo;
                    serviceSum = productSum.service;
                }

                //軟體
                List<string> serialPermitList = getSerialPermitProdcodeList();
                string serialPermitJsonStr = JsonConvert.SerializeObject(serialPermitList);
                string serialQueryText = $"SELECT * FROM c WHERE c.dataType = 'serial' AND ARRAY_CONTAINS({serialPermitJsonStr}, c.prodCode)";
                await foreach (var itemsr in db.GetItemQueryStreamIteratorSql(queryText: serialQueryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Product-{school_code}") }))
                {
                    using var json = await JsonDocument.ParseAsync(itemsr.Content);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        deviceForCoreService uuidForCore;
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            serial.Add(obj.ToObject<SchoolProductSerial>());
                        }
                    }
                }

                //服務
                List<SchoolProductSumDataService> service = new List<SchoolProductSumDataService>();
                SchoolProductSum schoolProductSum;
                await foreach (var items in db.GetItemQueryStreamIteratorSql(queryText: $"SELECT * FROM c WHERE c.id = '{school_code}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"ProductSum") }))
                {
                    using var json = await JsonDocument.ParseAsync(items.Content);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            schoolProductSum = obj.ToObject<SchoolProductSum>();
                            service = schoolProductSum.service;
                        }
                    }
                }
                if (service.Count > 0)
                {
                    SchoolProductOrder spOrderRow;
                    foreach (SchoolProductSumDataService serviceRow in service)
                    {
                        spOrderRow = new SchoolProductOrder();
                        spOrderRow.prodCode = serviceRow.prodCode;
                        spOrderRow.avaliable = UTCNow>serviceRow.endDate ? 0 : serviceRow.avaliable;
                        spOrderRow.avaliableStartDate = serviceRow.startDate;
                        spOrderRow.avaliableEndDate = serviceRow.endDate;
                        serviceOrder.Add(spOrderRow);
                    }
                }

                //硬體
                await foreach (var itemhd in db.GetItemQueryStreamIteratorSql(queryText: $"SELECT * FROM c WHERE c.dataType = 'hard'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Product-{school_code}") }))
                {
                    using var json = await JsonDocument.ParseAsync(itemhd.Content);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            hard.Add(obj.ToObject<SchoolProductHard>());
                        }
                    }
                }

                //空間計算
                ////取得教師分配空間
                long teach = 0;
                var querysp = $"SELECT SUM(c.size) as size FROM c";
                await foreach (var item in db.GetItemQueryStreamIteratorSql(queryText: querysp, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Teacher-{school_code}") }))
                {
                    var jsonts = await JsonDocument.ParseAsync(item.Content);
                    foreach (var obj in jsonts.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        teach = obj.GetProperty("size").GetInt64() * 1073741824; //G換算
                    }
                }
                ////學校已使用空間
                long blobsize = 0;
                RedisValue value = default;
                value = r8.HashGet($"Blob:Record", $"{school_code}");
                if (value != default && !value.IsNullOrEmpty)
                {
                    JsonElement record = value.ToString().ToObject<JsonElement>();
                    if (record.TryGetInt64(out blobsize))
                    {
                    }
                }
                ////各項空間數值取得
                Dictionary<string, double> catalog = new();
                SortedSetEntry[] Scores = _azureRedis.GetRedisClient(8).SortedSetRangeByScoreWithScores($"Blob:Catalog:{school_code}");
                if (Scores != null)
                {
                    foreach (var score in Scores)
                    {
                        double val = score.Score;
                        string key = score.Element.ToString();
                        catalog.Add(key, val);
                    }
                }
                dynamic space = new ExpandoObject();
                space.size = blobsize;
                space.teach = teach;
                space.catalog = catalog;

                //取得購買紀錄
                List<object> order = await GetOrderHisFromCoreBBAsync(school_code.GetString());

                //取得CC授權使用狀態
                var hashs = await r8.HashGetAllAsync($"CC:License:{school_code.GetString()}");
                var ccuser = hashs.Select(x => JsonDocument.Parse(x.Value.ToString())).ToList();

                return Ok(new { serial = serial, service = serviceOrder, hard, space, order, ccuser });
            }
            catch (Exception ex)
            {
                return BadRequest();
            }
        }
        private async Task<string> getCoreAccessToken()
        {
            string AccessToken = "";
            try
            {
                string Url = _configuration.GetValue<string>("HaBookAuth:CoreAPI") + "/oauth2/token";
                string GrantType = "device";
                string ClientID = _configuration.GetValue<string>("HaBookAuth:CoreService:clientID");
                string Secret = _configuration.GetValue<string>("HaBookAuth:CoreService:clientSecret");
                var content = new { grant_type = GrantType, client_id = ClientID, client_secret = Secret };
                var response = await _httpClient.CreateClient().PostAsJsonAsync($"{Url}", content);
                if (response.IsSuccessStatusCode)
                {
                    string responseBody = response.Content.ReadAsStringAsync().Result;
                    using (JsonDocument document = JsonDocument.Parse(responseBody.ToString()))
                    {
                        if (document.RootElement.TryGetProperty("access_token", out JsonElement AccessTokenObj))
                        {
                            AccessToken = AccessTokenObj.ToString();
                        }
                    }
                }
                return AccessToken;
            }
            catch (Exception ex)
            {
                return AccessToken;
            }
        }

        //取得序號產品准許使用產品代碼列表
        public static List<string> getSerialPermitProdcodeList()
        {
            List<string> allowProdCode = new List<string>(); //軟體許可產品列：HiTeach STD、TBL、PRO、HiTeach5；
            allowProdCode.Add("J223IZ6M"); //HiTeach STD
            allowProdCode.Add("3222C6D2"); //HiTeach TBL
            allowProdCode.Add("J223IZAM"); //HiTeach PRO
            allowProdCode.Add("J2236ZCX"); //HiTeach Lite
            allowProdCode.Add("3222DNG2"); //HiTeach Mobile
            allowProdCode.Add("3222IAVN"); //HiTeach Premium
            allowProdCode.Add("BYJ6LZ6Z"); //HiTeach5
            allowProdCode.Add("LZLL6ZEI"); //HiTeachCC
            return allowProdCode;
        }

        #endregion

        #region  顾问学校统计返回值

        public record ScRankCnt : SchoolBase
        {
            public int openCnt { get; set; }
            public int lessCnt { get; set; }
            public int interCnt { get; set; }
            public int actCnt { get; set; }
        }

        public record SchoolBase
        {
            public string id { get; set; }
            public string code { get; set; }
            public string name { get; set; }
            public string picture { get; set; }
        }

        #endregion

        #region 統購平台用
        /// <summary>
        /// 取得CoreBB學校訂單履歷資訊  暂时这样处理
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-bbpurchase")]
        public async Task<IActionResult> GetBBpurchase(JsonElement jsonElement)
        {
            List<object> result = new List<object>();
            try
            {
                var client = _azureCosmos.GetCosmosClient(name:"CoreServiceV2");
                string sqlPurchase = $"SELECT * FROM c ";
                List<PurchaseSeats> purchaseSeatsList = new List<PurchaseSeats>();
                await foreach (PurchaseSeats item in client.GetContainer("Habb", "Auth").GetItemQueryIteratorSql<PurchaseSeats>(queryText: sqlPurchase, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("purchase") }))
                {
                    purchaseSeatsList.Add(item);
                }
                Dictionary<string, List<PurchaseSchoolSeats>> purchaseSchoolSeatsDic = new Dictionary<string, List<PurchaseSchoolSeats>>();
                string sqlSchool = $"SELECT * FROM c ";
                await foreach (PurchaseSchoolSeats item in client.GetContainer("Habb", "Auth").GetItemQueryIteratorSql<PurchaseSchoolSeats>(queryText: sqlSchool, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("purchaseSchool") }))
                {
                    if(!purchaseSchoolSeatsDic.ContainsKey(item.purchaseId))
                    {
                        purchaseSchoolSeatsDic.Add(item.purchaseId, new List<PurchaseSchoolSeats>() { item });
                    }
                    else
                    {
                        purchaseSchoolSeatsDic[item.purchaseId].Add(item);
                    }
                }

                return Ok(new { geo = purchaseSeatsList, school = purchaseSchoolSeatsDic });
            }
            catch (Exception ex)
            {
                return BadRequest();
            }
        }
        /// <summary>
        /// 取得CoreService縣市所屬學校
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-school-geo")]
        public async Task<IActionResult> GetCsSchoolByGeo(JsonElement jsonElement)
        {
            List<CsSchoolBase> result = new List<CsSchoolBase>();
            try
            {
                string err = string.Empty;
                string countryId = (jsonElement.TryGetProperty("countryId", out JsonElement _countryId)) ? _countryId.GetString() : string.Empty;
                string provinceId = (jsonElement.TryGetProperty("provinceId", out JsonElement _provinceId)) ? _provinceId.GetString() : string.Empty;
                string cityId = (jsonElement.TryGetProperty("cityId", out JsonElement _cityId)) ? _cityId.GetString() : string.Empty;
                string distId = (jsonElement.TryGetProperty("distId", out JsonElement _distId)) ? _distId.GetString() : string.Empty;
                //輸入項檢驗
                if (string.IsNullOrWhiteSpace(countryId)) { err = "Invalid countryId."; }
                else if(countryId.Equals("CN"))
                {
                    if (string.IsNullOrWhiteSpace(provinceId)) { err = "Invalid provinceId."; }
                    else if (string.IsNullOrWhiteSpace(cityId)) { err = "Invalid cityId."; }
                }
                else if (countryId.Equals("TW"))
                {
                    if (string.IsNullOrWhiteSpace(cityId)) { err = "Invalid cityId."; }
                }
                if(!string.IsNullOrWhiteSpace(err)) ///錯誤訊息輸出
                {
                    return Ok(new { err = err, data = result });
                }
                //取得CS學校
                var client = _azureCosmos.GetCosmosClient(name:"CoreServiceV2CnRead");
                string sql = $"SELECT * FROM c WHERE c.countryId = '{countryId}'";
                if (!string.IsNullOrWhiteSpace(provinceId)) sql += $" AND c.provinceId = '{provinceId}'";
                if (!string.IsNullOrWhiteSpace(cityId)) sql += $" AND c.cityId = '{cityId}'";
                if (!string.IsNullOrWhiteSpace(distId)) sql += $" AND c.distId = '{distId}'";
                await foreach (CsSchoolBase item in client.GetContainer("Core", "School").GetItemQueryIteratorSql<CsSchoolBase>(queryText: sql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("base") }))
                {
                    result.Add(item);
                }

                return Ok(new { err = err, data = result });
            }
            catch (Exception ex)
            {
                return BadRequest();
            }
        }

        #endregion

        /// <summary>
        /// 未加入区域的学校
        /// </summary>
        public record NotAreaSchool
        {
            public string code { get; set; }
            public string id { get; set; }
            public string name { get; set; }
            public string schoolCode { get; set; }
            public string picture { get; set; }
            public List<string> period { get; set; }
            public string province { get; set; }
            public string city { get; set; }
            public string dist { get; set; } = "";
            public string areaId { get; set; } = "";
            public string standard { get; set; } = "";
            public List<SchoolArea> areas { get; set; } = new List<SchoolArea>();
        }

        /// <summary>
        /// 学校空间使用情况
        /// </summary>
        public record SchoolSpace
        {
            public string id { get; set; }
            public string name { get; set; }
            public Space space { get; set; }

        }

        /// <summary>
        /// 空间
        /// </summary>
        public record Space
        {
            /// <summary>
            /// 已使用空间
            /// </summary>
            public long useSize { get; set; }
            /// <summary>
            /// 分配教师空间
            /// </summary>
            public long tSize { get; set; }
            /// <summary>
            /// 空间类型
            /// </summary>
            public Dictionary<string, double?> catalogSize { get; set; }
        }

        public record ScSimple
        {
            public string id { get; set; }
            public string name { get; set; }
            public string picture { get; set; }
            public long createTime { get; set; }
        }

        public record DelSchoolRel
        {
            public string id { get; set; }
            public string code { get; set; }
            /// <summary>
            /// 未删除的类型 1 学校 2教师  3学生
            /// </summary>
            public int type { get; set; }
            public int status { get; set; }
        }
    }
}