TEAMModelOS/TEAMModelBI/Controllers/BISchool/SchoolController.cs

using Azure.Cosmos;
using HTEXLib.COMM.Helpers;
using MathNet.Numerics.LinearAlgebra;
using MathNet.Numerics.LinearAlgebra.Double;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Options;
using StackExchange.Redis;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using TEAMModelBI.DI.BIAzureStorage;
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;
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 NotificationService _notificationService;
        private readonly CoreAPIHttpService _coreAPIHttpService;

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

        /// <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.areaId !='{areaId}'");
                }
                else
                {
                    sqltxt.Append($" WHERE c.areaId ='' and c.standard = ''");
                }

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

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

                List<NotAreaSchool> notAreaSchools = new List<NotAreaSchool>();
                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]
        [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);
                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>>();

                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.Status == 200)
                {
                    using var fileJson = await JsonDocument.ParseAsync(responseSet.ContentStream);
                    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)
                    {
                        School school = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemAsync<School>(tempCode, new PartitionKey("Base"));
                        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").GetItemQueryIterator<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.Find(f => f.schoolId.Equals($"{school.id}") && f.areaId.Equals($"{_areaId}"));
                                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);
                                    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));
                        }
                    }
                }

                //保存操作记录
                //await _azureStorage.SaveBILog("school-update", $"{_tmdName}【{_tmdId}】操作学校加入区域功能，加入的区域：{standard},学校ID：{string.Join("|", schoolCodes.ToArray())}", _dingDing, httpContext: HttpContext);
                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-assistschool")]
        public async Task<IActionResult> GetAssistSchool(JsonElement jsonElement)
        {
            try
            {
                if (!jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return BadRequest();
                jsonElement.TryGetProperty("site", out JsonElement site);

                List<object> schools = new List<object>();
                var cosmosClient = _azureCosmos.GetCosmosClient();
                if ($"{site}".Equals(BIConst.Global))
                    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
                //List<string> schoolIds = new List<string>();
                //string sqlTxt = $"SELECT DISTINCT REPLACE(c.code, 'Teacher-', '') AS schoolId FROM c WHERE c.pk = 'Teacher' AND c.status = 'join' AND ARRAY_CONTAINS(c.roles, 'assist', true) AND c.id='{tmdId}' ";
                //await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { }))
                //{
                //    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                //    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                //    {
                //        schoolIds.Add(obj.GetProperty("schoolId").GetString());
                //    }
                //}

                List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");

                foreach (var item in schoolIds)
                {
                    await foreach (var itemBase in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"select c.id,c.code,c.name,c.picture,c.region,c.province,c.city,c.dist from c where c.id='{item}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
                    {
                        var json = await JsonDocument.ParseAsync(itemBase.ContentStream);
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            schools.Add(obj.ToObject<object>());
                        }
                    }
                }

                return Ok(new { state = 200, schools });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI，{_option.Location}   /schoolcheck/get-assustschool  \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 from c where c.id='{schoolId}'";
                await foreach (var itemSchool in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<AssistSchool>(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
                {
                    //schoolAssists.Add(itemSchool);
                    schoolAssists = itemSchool;
                    //school = itemSchool;

                }

                if (schoolAssists.id != null)
                {
                    var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(schoolAssists.id, new PartitionKey("ProductSum"));
                    if (response.Status == 200)
                    {
                        using var json = await JsonDocument.ParseAsync(response.ContentStream);
                        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.FindTotals(cosmosClient, $"select count(c.id) as totals from c ", "School", $"LessonRecord-{schoolAssists.id}");


                    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) = HttpJwtAnalysis.JwtXAuth(_auth, _option);
                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.Status == 200)
                {
                    string sql = $"SELECT distinct value(c) FROM c join A1 in c.schools where A1.schoolId='{school.id}'";
                    List<Teacher> teachers = new List<Teacher>();
                    await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryIterator<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 _azureStorage.SaveBILog("school-update", $"{tmdName}【{tmdId}】修改学校信息，学校和ID:{school.name}【{school.id}】", _dingDing, httpContext: HttpContext);
                    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 });

                //School tempShool = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemAsync<School>(replaceSchool.school.id, new PartitionKey("Base"));
                //if (tempShool != null)
                //{
                //    List<Teacher> teachers = new List<Teacher>();
                //    string sqltxt = $"select distinct value(c) from c join a1 in c.schools where a1.schoolId='{replaceSchool.school.id}'";
                //    await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryIterator<Teacher>(queryText: sqltxt, 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(replaceSchool.school.id));
                //        if (teacherSchool != null)
                //        {
                //            teacherSchool.name = replaceSchool.school.name;
                //            teacherSchool.picture = replaceSchool.school.picture;
                //            teacherSchool.areaId = replaceSchool.school.areaId;
                //        }
                //        await cosmosClient.GetContainer(Constant.TEAMModelOS, "Teacher").ReplaceItemAsync(item, item.id, new PartitionKey("Base"));
                //    }

                //    schoolInfo = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReplaceItemAsync<School>(replaceSchool.school, replaceSchool.school.id, new PartitionKey("Base"));

                //    //保存操作记录
                //    await _azureStorage.SaveBILog("school-update", $"{replaceSchool.tmdName}【{replaceSchool.tmdId}】修改学校信息，学校和ID:{replaceSchool.school.name}【{replaceSchool.school.id}】", _dingDing, httpContext: HttpContext);

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

        /// <summary>
        /// 顾问学校空间使用情况
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [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.Status == 200)
                {
                    using var json = await JsonDocument.ParseAsync(response.ContentStream);
                    school = json.ToObject<School>();
                }

                long teach = 0;
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"SELECT sum(c.size) as size FROM c ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Teacher-{sid}") }))
                {
                    var json = await JsonDocument.ParseAsync(item.ContentStream);
                    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>
        [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";
                Notification notification = new Notification
                {
                    hubName = "hita",
                    type = "msg",
                    from = $"BI:{_option.Location}:{_school}",
                    to = new List<string> { $"{_shiftTeacher}" },
                    label = $"{shiftCode}_school",
                    body = new { location = _option.Location, biz = shiftCode, tmdid = $"{_tmdId}", tmdname = $"{_tmdName}", status = 1, time = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds() }.ToJsonString(),
                    expires = DateTimeOffset.UtcNow.AddDays(7).ToUnixTimeSeconds()
                };

                if (notification != null)
                {
                    var url = _configuration.GetValue<string>("HaBookAuth:CoreService:sendnotification");
                    var clientID = _configuration.GetValue<string>("HaBookAuth:CoreService:clientID");
                    var clientSecret = _configuration.GetValue<string>("HaBookAuth:CoreService:clientSecret");
                    var location = _option.Location;
                    await _notificationService.SendNotification(clientID, clientSecret, location, url, notification);//站内发送消息
                }

                //保存操作记录
                //await _azureStorage.SaveBILog("schoolTeacher-add", $"{_tmdName}【{_tmdId}】移交顾问给{newSchoolTeacher.name}【{newSchoolTeacher.id}】", _dingDing, httpContext: HttpContext);
                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]
        [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);
                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, $"{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}  /batchschool/set-aistschool \n {ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 依据学校ID查学校管理员信息
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [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").GetItemQueryIterator<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("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}】");

                Teacher teacher = null;
                SchoolTeacher schoolTeacher = null;
                var resTeacher = await cosmosClient.GetContainer("TEAMModelOS", "Teacher").ReadItemStreamAsync($"{tmdId}", new PartitionKey($"Base"));
                if (resTeacher.Status == 200)
                {
                    using var tchJson = await JsonDocument.ParseAsync(resTeacher.ContentStream);
                    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 = "" });
                        teacher = await cosmosClient.GetContainer("TEAMModelOS", "Teacher").ReplaceItemAsync<Teacher>(teacher, teacher.id, new PartitionKey("Base"));
                        strMsg.Append($"账户基础信息添加学校，");
                    }
                }
                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 = "" });
                    teacher = await cosmosClient.GetContainer(Constant.TEAMModelOS, "Teacher").CreateItemAsync<Teacher>(teacher, new PartitionKey("Base"));
                    strMsg.Append($"添加基础信息，并添加学校");
                }

                if (teacher != null)
                {
                    var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync($"{tmdId}", new PartitionKey($"Teacher-{schoolId}"));
                    if (response.Status == 200)
                    {
                        using var json = await JsonDocument.ParseAsync(response.ContentStream);
                        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-{schoolId}"));
                            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", $"{_tmdName}【{_tmdId}】账户创建{tmdName}【{tmdName}】账户，并设学校管理员。学校ID:{schoolId}。", _dingDing, httpContext: HttpContext);

                return Ok(new { state = RespondCode.Ok, teacher, schoolTeacher });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI,{_option.Location}  /batchschool/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)
        {
            if (!jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return BadRequest();
            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);

            SchoolTeacher scTeacher = null;
            var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync($"{tmdId}", new PartitionKey($"Teacher-{scId}"));
            if (response.Status == 200)
            {
                using var json = await JsonDocument.ParseAsync(response.ContentStream);
                scTeacher = json.ToObject<SchoolTeacher>();
                if (scTeacher.roles.Contains("admin"))
                {
                    scTeacher.roles.Remove("admin");
                    scTeacher = await cosmosClient.GetContainer("TEAMModelOS", "School").ReplaceItemAsync<SchoolTeacher>(scTeacher, scTeacher.id, new PartitionKey($"Teacher-{scId}"));
                }
                else
                    return Ok(new { state = RespondCode.NotFound, msg = "该账户不是管理员" });
            }
            else
                return Ok(new { state = RespondCode.NotFound, msg = "未找到醍摩豆管理员信息" });

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

        /// <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");
                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)
                {
                    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").GetItemQueryIterator<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").GetItemQueryIterator<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.Status == 204)
                        msg.AppendLine($"{tmdName}【{tmdId}】删除学校，删除状态：{response.Status}，删除ID:{tempId}");
                    else
                        delSchoolRels.Add(new DelSchoolRel() { id = $"{tempId}", code = "Base", type = 1, status = response.Status });

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

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


                //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").GetItemQueryIterator<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").GetItemQueryIterator<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 _azureStorage.SaveBILog("set-del", msg.ToString(), _dingDing, httpContext: HttpContext);
                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}  /batchschool/set-del \n {ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 查询所有学校  第三方开放平台关联学校显示所有学校
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-applyschools")]
        public async Task<IActionResult> GetApplySchools(JsonElement jsonElement)
        {
            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);
            }

            List<ApplySchool> applySchools = new();

            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<ApplySchool>(queryText: "select c.id,c.code,c.name,c.picture from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
            {
                applySchools.Add(item);
            }

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


        /// <summary>
        /// 通过学校Id查询详情   数据管理工具——查询工具
        /// </summary>
        /// <returns></returns>
        [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 StringBuilder("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").GetItemQueryStreamIterator(queryText: sqlTxt.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                {
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        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.FindSchoolIds(cosmosClient, $"select c.id from c where c.areaId='{areaId}'", "Base");

                allSize = await CommonFind.FindTotals(cosmosClient, $"select sum(c.size) as totals from c where c.areaId='{areaId}'", new List<string> { "School" }); //区域所有学校空间

                foreach (var school in schools)
                {
                    await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: $"SELECT sum(c.size) as size FROM c ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Teacher-{school}") }))
                    {
                        var json = await JsonDocument.ParseAsync(item.ContentStream);
                        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表达式
                        //useSpaceInfo = (from e in useSpaceInfo.Concat(catalog) 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 方式合并
                    }
                    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表达式
                        //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 方式合并
                    }
                }
            }

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

                teacherSpace = await CommonFind.FindTotals(cosmosClient, $"SELECT sum(c.size) as totals FROM c", "School", $"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>
        [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.Status == 200)
                {
                    using var json = await JsonDocument.ParseAsync(response.ContentStream);
                    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";

                //schoolInfo.lessonCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", currencySql,  $"LessonRecord-{schoolInfo.id}");

                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}");  //去年活动

                }

                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 });
            }
            else
                return Ok(new { state = RespondCode.NotFound, msg = "未找到该学校信息！" });
        }

        /// <summary>
        /// 通过学校Id 统计学校信息
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [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.Status == 200)
            {
                using var json = await JsonDocument.ParseAsync(response.ContentStream);
                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; //今年的课例

            int allInterCnt = 0;   //所有互动
            int lastDayInterCnt = 0;   // 昨天课例互动
            int interCnt = 0;   // 今天课例互动
            int lastYearInterCnt = 0;   // 去年课例互动
            int 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 <= {lastDays} ";
            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.GetSqlValueCount(cosmosClient, "School", strInterSql, lessCode);

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

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

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

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

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

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

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

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

            string yearActSql = $" and c.school='{schoolId}' and c.createTime >= {years} and c.createTime <= {yeare}";
            yearActCnt = await ActivityWay.GetCnt(cosmosClient, 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>
        [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").GetItemQueryIterator<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").GetItemQueryIterator<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").GetItemQueryIterator<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)
        {
            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);
            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 = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");
            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 ";
                scInfos = await CommonFind.GetObject<SchoolBase>(cosmosClient, "School", scInfoSql, "Base", schoolIds);

                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", lessMoSql, $"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, 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("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();  //当前的课例，活动，互动统计

            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 (schoolIds.Count == 0) return Ok(new { state = RespondCode.Ok, yearCnts, });
                scSql = BICommonWay.ManyScSql("c.school", schoolIds);
            }

            if (!string.IsNullOrEmpty($"{schooId}") && string.IsNullOrEmpty($"{tmdId}"))
                scSql = $"c.school = '{schooId}'";

            if ((string.IsNullOrEmpty($"{tmdId}") && string.IsNullOrEmpty($"{schooId}")) || (!string.IsNullOrEmpty($"{tmdId}") && !string.IsNullOrEmpty($"{schooId}")))
                return Ok(new { state = RespondCode.ParamsError, msg = "参数错误" });

            List<MonthStartEnd> mthStartEnds = await TimeHelper.GetYearSataMthCtMth(DateTimeOffset.UtcNow);
            if (mthStartEnds.Count > 0)
            {
                foreach (var item in mthStartEnds)
                {
                    YearCnt yearCnt = new() { name = item.yearMonth };
                    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}";
                    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, 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)
        {
            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);
            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; //今年的课例

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

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

            List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");
            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.clientInteractionAverge)) from c where c.pk='LessonRecord' and {scSql}";
                interAll = await CommonFind.GetSqlValueCount(cosmosClient, "School", strInterSql);

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

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

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

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

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

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

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

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

                string yearActSql = $" and {scSql} and c.createTime >= {years} and c.createTime <= {yeare}";
                yearActCnt = await ActivityWay.GetCnt(cosmosClient, 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();  //学校基础基础信息
            int serial = 0;     //学校软体数量
            int service = 0;    //学校服务数量
            int hard = 0;     //学校硬件数量


            var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync($"{schoolId}", new PartitionKey("Base"));
            if (response.Status == 200)
            {
                using var json = await JsonDocument.ParseAsync(response.ContentStream);
                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").GetItemQueryIterator<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.Status == 200)
            {
                using var json = await JsonDocument.ParseAsync(responseProduct.ContentStream);
                if (json.RootElement.TryGetProperty("serial", out JsonElement _serial) && !_serial.ValueKind.Equals(JsonValueKind.Null))
                {
                    serial = _serial.ToObject<List<SchoolProductSumData>>().Count;//.Select(x => x.prodCode).ToList();
                }
                if (json.RootElement.TryGetProperty("service", out JsonElement _service) && !_service.ValueKind.Equals(JsonValueKind.Null))
                {
                    service = _service.ToObject<List<SchoolProductSumData>>().Count;//.Select(x => x.prodCode).ToList();
                }
                if (json.RootElement.TryGetProperty("hard", out JsonElement _hard) && !_hard.ValueKind.Equals(JsonValueKind.Null))
                {
                    hard = _hard.ToObject<List<SchoolProductSumDataHard>>().Count();//.Select(x => x.prodCode).ToList();
                }
            }

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

        /// <summary>
        /// 通过tmdID 账户查询学校专业和模组
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-proded")]
        public async Task<IActionResult> GetProdEd(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return Ok(new { state = RespondCode.ParamsError, nsg = "参数错误" });
            jsonElement.TryGetProperty("site", out JsonElement site);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

            List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");

            List<ScProdEd> scInfos = new();
            List<string> products = new();
            if (schoolIds.Count > 0)
            {
                string scInfoSql = $"select c.id,c.name,c.code,c.picture,c.type,c.size,c.scale from c ";
                scInfos = await CommonFind.GetObject<ScProdEd>(cosmosClient, "School", scInfoSql, "Base", schoolIds);

                if (schoolIds.Count > 0)
                {
                    foreach (var scProd in scInfos)
                    {
                        var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(scProd.id, new PartitionKey("ProductSum"));
                        if (response.Status == 200)
                        {
                            using var json = await JsonDocument.ParseAsync(response.ContentStream);
                            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.ContentStream);
                    //        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,  scInfos, productAn });
            // var productAn = products.GroupBy(x => x).Select(y => new { key = y.Key, count = y.ToList().Count }).ToList();

        }


        



        #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

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

        /// <summary>
        /// 未加入区域的学校
        /// </summary>
        public record NotAreaSchool
        {
            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 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; }
        }
    }
}