TEAMModelOS/TEAMModelBI/Controllers/BIHome/HomeStatisController.cs

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Text.Json;
using TEAMModelOS.SDK.DI;
using Azure.Cosmos;
using Microsoft.Extensions.Options;
using TEAMModelOS.SDK.Models;
using TEAMModelOS.Models;
using System.Text;
using StackExchange.Redis;
using TEAMModelOS.SDK.Extension;
using TEAMModelBI.Models;

namespace TEAMModelBI.Controllers.BIHome
{
    [Route("homestatis")]
    [ApiController]
    public class HomeStatisController : ControllerBase
    {
        private readonly AzureCosmosFactory _azureCosmos;
        private readonly AzureStorageFactory _azureStorage;
        private readonly DingDing _dingDing;
        private readonly Option _option;
        private readonly AzureRedisFactory _azureRedis;

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

        /// <summary>
        /// 获取全部教师数量、学生数量、已创校数量、全部学校总空间大小
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-allnumber")]
        public async Task<IActionResult> GetAllNumber()
        {
            try
            {
                var client = _azureCosmos.GetCosmosClient();;

                //查询全部教师人数
                long teacherCount = await CommonFind.FindTotals(client, "select count(c.id) as totals from c where c.code='Base'", new List<string>() { "Teacher" });
                //查询全部教师人数
                long studentCount = await CommonFind.FindTotals(client, "select count(c.id) as totals from c where c.pk='Base'", new List<string>() { "Student" });
                //查询已创建多少学校
                long schoolCount = await CommonFind.FindTotals(client, "select count(c.id) as totals from c where c.code='Base'", new List<string>() { "School" });
                //空间
                long schoolSize = await CommonFind.FindTotals(client, "select sum(c.size) as totals from c where c.code='Base'", new List<string>() { "School" });

                return Ok(new { state = 200, teacherCount, studentCount,  schoolCount, schoolSize });

                ////依据学校查询教师人数
                //List<string> teacherCount_list = new();
                ////依据学校查询学生信息
                //List<string> studentCount_List = new();
                ////学校数
                //List<string> schoolCount_List = new();
                ////学校空间大小
                //long schoolsize = 0
                ////查询全部教师人数
                //await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIterator(queryText: $"select c.id from c ", 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)
                //    {
                //        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                //        while (accounts.MoveNext())
                //        {
                //            JsonElement account = accounts.Current;
                //            teacherCount_list.Add(account.GetProperty("id").GetString());
                //        }
                //    }
                //}

                ////查询全部学生人数
                //await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "Student").GetItemQueryStreamIterator(queryText: $"select c.id from c where c.pk='Base'"))
                //{
                //    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                //    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                //    {
                //        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                //        while (accounts.MoveNext())
                //        {
                //            JsonElement account = accounts.Current;
                //            studentCount_List.Add(account.GetProperty("id").GetString());
                //        }
                //    }
                //}

                ////查询已创建多少学校
                //await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"select c.id,c.size from c", 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)
                //    {
                //        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                //        while (accounts.MoveNext())
                //        {
                //            JsonElement account = accounts.Current;
                //            schoolCount_List.Add(account.GetProperty("id").GetString());
                //            schoolsize += account.GetProperty("size").GetInt64();
                //        }
                //    }
                //}

                ////查询教师的大小和教师集合信息
                //await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIterator(queryText: $"select sum(c.size) as size from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
                //{
                //    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                //    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                //    {
                //        schoolsize += obj.GetProperty("size").GetInt64();
                //    }
                //}
                //return Ok(new { state = 200, teacherCount = teacherCount_list.Count, studentCount = studentCount_List.Count, schoolCount = schoolCount_List.Count, schoolSize = schoolsize });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI，{_option.Location} /homestatis/get-numberpeople \n {ex.Message}{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 获取其它类型的统计:问卷调查、评量检测、投票活动
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-othertypes")]
        public async Task<IActionResult> GetOtherTypes(JsonElement jsonElement) 
        {
            try
            {
                if (!jsonElement.TryGetProperty("tmdid", out JsonElement _tmdid)) return BadRequest();

                int surveyJoinCount = 0;        //调查参加数量
                int surveyDoneCount = 0;        //调查完成数量
                int surveyAreaJoinCount = 0;    //调查区域参加数量
                int surveyAreaDoneCount = 0;    //调查区域完成数量
                int examJoinCount = 0;          //评量检测参加数量
                int examDoneCount = 0;          //评量检测完成数量
                int examAreaJoinCount = 0;      //评量检测区域参加数量
                int examAreaDoneCount = 0;      //评量检测区域完成数量
                int voteJoinCount = 0;          //投票参加数量
                int voteDoneCount = 0;          //投票完成数量
                int voteAreaJoinCount = 0;      //投票区域参加数量  
                int voteAreaDoneCount = 0;      //投票区域完成数量

                //问卷调查
                await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Teacher")
                 .GetItemQueryIterator<StuActivity>(queryText: $"select c.owner, c.taskStatus from c where c.type = 'Survey' ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Activity-{_tmdid}") }))
                {
                    if (!string.IsNullOrEmpty(item.owner))
                    {
                        if (item.owner.Equals("school"))
                        {
                            surveyJoinCount += 1;
                            if (item.taskStatus > 0)
                            {
                                surveyDoneCount += 1;
                            }
                        }
                        else if (item.owner.Equals("area"))
                        {
                            surveyAreaJoinCount += 1;
                            if (item.taskStatus > 0)
                            {
                                surveyAreaDoneCount += 1;
                            }
                        }
                    }
                }

                //评量检测
                await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Teacher")
                  .GetItemQueryIterator<StuActivity>(queryText: $"select c.owner, c.taskStatus from c where c.type = 'ExamLite' ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Activity-{_tmdid}") }))
                {
                    if (!string.IsNullOrEmpty(item.owner))
                    {
                        if (item.owner.Equals("school"))
                        {
                            examJoinCount += 1;
                            if (item.taskStatus > 0)
                            {
                                examDoneCount += 1;
                            }
                        }
                        else if (item.owner.Equals("area"))
                        {
                            examAreaJoinCount += 1;
                            if (item.taskStatus > 0)
                            {
                                examAreaDoneCount += 1;
                            }
                        }
                    }
                }

                //投票活动
                await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Teacher")
                 .GetItemQueryIterator<StuActivity>(queryText: $"select c.owner, c.taskStatus from c where c.type = 'Vote' ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Activity-{_tmdid}") }))
                {
                    if (!string.IsNullOrEmpty(item.owner))
                    {
                        if (item.owner.Equals("school"))
                        {
                            voteJoinCount += 1;
                            if (item.taskStatus > 0)
                            {
                                voteDoneCount += 1;
                            }
                        }
                        else if (item.owner.Equals("area"))
                        {
                            voteAreaJoinCount += 1;
                            if (item.taskStatus > 0)
                            {
                                voteAreaDoneCount += 1;
                            }
                        }
                    }
                }

                return Ok(new { state = 200, surveyJoinCount, surveyDoneCount, surveyAreaJoinCount, surveyAreaDoneCount, examJoinCount, examDoneCount, examAreaJoinCount, examAreaDoneCount, voteJoinCount, voteDoneCount, voteAreaJoinCount, voteAreaDoneCount });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI，{_option.Location} /homestatis/get-othertypes \n {ex.Message}{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 查询所有区级信息统计数据
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-alldiststics")]
        public async Task<IActionResult> GetAllDistStics() 
        {
            try
            {
                var cosmosClient = _azureCosmos.GetCosmosClient();
                List<SticsCity> standards = new List<SticsCity>();
                StringBuilder stringBuder = new StringBuilder("select c.name,c.provName,c.cityName,c.standard from c");
                //查询省份区域
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Normal").GetItemQueryIterator<Area>(queryText: stringBuder.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-Area") }))
                {
                    SticsCity sticsCity = new SticsCity
                    {
                        provName = item.provName,
                        cityName = item.cityName,
                        distName = item.name,
                        standard = item.standard
                    };
                    standards.Add(sticsCity);
                }
                List<SticsDist> sticsDists = new List<SticsDist>();

                //查询所有下面的学校数量
                foreach (var itemStandrds in standards) 
                {
                    SticsDist sticsDist = new SticsDist();

                    sticsDist.provName = itemStandrds.provName;
                    sticsDist.cityName = itemStandrds.cityName;
                    sticsDist.distName = itemStandrds.distName;
                    sticsDist.standard = itemStandrds.standard;

                    //
                    string sqlTxt = $"select c.id from c where c.standard='{itemStandrds.standard}'";
                    List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, sqlTxt, "Base");

                    //List<string> schoolIds = new List<string>();
                    //int schoolCount = 0;

                    //await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"select c.id from c where c.standard='{itemStandrds.standard}'", 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)
                    //    {
                    //        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                    //        while (accounts.MoveNext())
                    //        {
                    //            JsonElement account = accounts.Current;
                    //            schoolIds.Add(account.GetProperty("id").GetString());
                    //            schoolCount += 1;
                    //        }
                    //    }
                    //}

                    sticsDist.schoolCount = schoolIds.Count;

                    int teacherCount = 0;  //教师数量
                    int studentCount = 0;  //学生数量
                    List<string> teacherIds = new List<string>();

                    List<string> studentIds = new List<string>();

                    //查询学校下面的教师人数
                    foreach (var itemSchool in schoolIds)
                    {
                        //查询全部教师人数
                        string sqlT = $"select count(c.id) as totals from c join a1 in c.schools where a1.schoolId='{itemSchool}'";
                        teacherCount += await CommonFind.FindTotals(cosmosClient, sqlT, "Teacher","Base");

                        //string sqlTeacherTxt = $"select distinct value(c) from c join a1 in c.schools where a1.schoolId='{itemSchool}'";
                        //await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIterator(queryText: sqlTeacherTxt, 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)
                        //    {
                        //        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                        //        while (accounts.MoveNext())
                        //        {
                        //            //JsonElement account = accounts.Current;
                        //            //teacherIds.Add(account.GetProperty("id").GetString());
                        //            teacherCount += 1;
                        //        }
                        //    }
                        //}

                        //查询全部学生人数
                        string sqlS = $"select count(c.id) as totals from c";
                        teacherCount += await CommonFind.FindTotals(cosmosClient, sqlS, "Student", $"Base-{itemSchool}");

                        //await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Student").GetItemQueryStreamIterator(queryText: $"select c.id from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{itemSchool}") }))
                        //{
                        //    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                        //    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        //    {
                        //        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                        //        while (accounts.MoveNext())
                        //        {
                        //            //JsonElement account = accounts.Current;
                        //            //studentIds.Add(account.GetProperty("id").GetString());
                        //            studentCount += 1;
                        //        }
                        //    }
                        //}
                    }

                    sticsDist.teacherCount = teacherCount;
                    sticsDist.studentCount = studentCount;

                    sticsDists.Add(sticsDist);
                }

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

                return BadRequest();
            }
        }

        /// <summary>
        /// 依据城市Code查询学校，教师数量，学生数量
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-citystics")]
        public async Task<IActionResult> GetCityStics(JsonElement jsonElement) 
        {
            try
            {
                if(!jsonElement.TryGetProperty("cityCode", out JsonElement _cityCode))  return BadRequest();

                var cosmosClient = _azureCosmos.GetCosmosClient();
                List<string> areaStandard = new List<string>();
                //查询省份区域
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Normal").GetItemQueryIterator<Area>(queryText: $"select c.name,c.cityName,c.standard from c where c.cityCode='{_cityCode}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-Area") }))
                {
                    areaStandard.Add(item.standard);
                }
                int citySchoolCount = 0;
                int teachCount = 0;
                int stuCount = 0;

                foreach (var tempSatndard in areaStandard)
                {
                    string sqlSchool = $"select c.id from c where c.standard='{tempSatndard}'";
                    List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, sqlSchool, "Base");
                    citySchoolCount += schoolIds.Count;
                    //查询学校下面的教师人数
                    foreach (var itemSchool in schoolIds)
                    {
                        //查询全部教师人数
                        string sqlT = $"select count(c.id) as totals from c join a1 in c.schools where a1.schoolId='{itemSchool}'";
                        teachCount += await CommonFind.FindTotals(cosmosClient, sqlT, "Teacher", "Base");

                        //查询全部学生人数
                        string sqlS = $"select count(c.id) as totals from c";
                        stuCount += await CommonFind.FindTotals(cosmosClient, sqlS, "Student", $"Base-{itemSchool}");
                    }
                }
                return Ok(new { state = 200, schoolCount = citySchoolCount, teacherCount = teachCount, tudentCount = stuCount });


                //List<string> teacherIds = new List<string>();
                //List<string> studentIds = new List<string>();

                //foreach (var tempSatndard in areaStandard)
                //{
                //    List<string> schoolIds = new List<string>();
                //    await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"select c.id from c where c.standard='{tempSatndard}'", 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)
                //        {
                //            var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                //            while (accounts.MoveNext())
                //            {
                //                JsonElement account = accounts.Current;
                //                schoolIds.Add(account.GetProperty("id").GetString()); 
                //                citySchoolCount += 1;
                //            }
                //        }
                //    }

                //    //查询学校下面的教师人数
                //    foreach (var itemSchool in schoolIds)
                //    {
                //        //查询全部教师人数
                //        await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIterator(queryText: $"select c.id from c join a1 in c.schools where a1.schoolId='{itemSchool}'", 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)
                //            {
                //                var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                //                while (accounts.MoveNext())
                //                {
                //                    JsonElement account = accounts.Current;
                //                    teacherIds.Add(account.GetProperty("id").GetString());
                //                }
                //            }
                //        }

                //        //查询全部学生人数
                //        await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Student").GetItemQueryStreamIterator(queryText: $"select c.id from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{itemSchool}") }))
                //        {
                //            using var json = await JsonDocument.ParseAsync(item.ContentStream);
                //            if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                //            {
                //                var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                //                while (accounts.MoveNext())
                //                {
                //                    JsonElement account = accounts.Current;
                //                    studentIds.Add(account.GetProperty("id").GetString());
                //                }
                //            }
                //        }
                //    }
                //}

                //return Ok(new { state = 200, schoolCount = citySchoolCount, teacherCount = teacherIds.Count, tudentCount = studentIds.Count });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI, {_option.Location}  /homestatis/get-citystics  \n  {ex.Message}{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 统计所有省份下的数据
        /// </summary>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-provincestics")]
        public async Task<IActionResult> GetProvinceStics() 
        {
            try
            {
                var cosmosClient = _azureCosmos.GetCosmosClient();
                List<ProvinceStandard> standards = new List<ProvinceStandard>();
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Normal").GetItemQueryStreamIterator(queryText: $"select c.provCode,c.provName,c.standard from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base-Area") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                        while (accounts.MoveNext())
                        {
                            JsonElement account = accounts.Current;
                            ProvinceStandard provinceStandard = new ProvinceStandard
                            {
                                provCode = account.GetProperty("provCode").GetString(),
                                provName = account.GetProperty("provName").GetString(),
                                standard = account.GetProperty("standard").GetString()
                            };
                            standards.Add(provinceStandard);
                        }
                    }
                }

                List<ProvinceStics> provinceStics = new List<ProvinceStics>();
                foreach (var itemStandard in standards) 
                {
                    ProvinceStics tempProvinceStics = new ProvinceStics();
                    //ProvinceStics tempCode = new ProvinceStics();
                    var tempCode = provinceStics.Find(x => x.provCode == itemStandard.provCode);
                    if (tempCode != null)
                    {
                        string sqlSc = $"select c.id from c where c.standard='{itemStandard.standard}'";
                        List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, sqlSc, "Base");
                        tempCode.schoolCount += schoolIds.Count;
                        //List<string> schoolIds = new List<string>();
                        //await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"select c.id from c where c.standard='{itemStandard.standard}'", 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)
                        //    {
                        //        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                        //        while (accounts.MoveNext())
                        //        {
                        //            JsonElement account = accounts.Current;
                        //            schoolIds.Add(account.GetProperty("id").GetString());
                        //            tempCode.schoolCount += 1;
                        //        }
                        //    }
                        //}
                        if (schoolIds.Count > 0)
                        {
                            foreach (var itemSchool in schoolIds)
                            {
                                //查询学校教师人数
                                string sqlT = $"select count(c.id) as totals from c join a1 in c.schools where a1.schoolId='{itemSchool}'";
                                tempCode.teacherCount += await CommonFind.FindTotals(cosmosClient, sqlT, "Teacher", "Base");

                                //await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIterator(queryText: $"select c.id from c join a1 in c.schools where a1.schoolId='{itemSchool}'", 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)
                                //    {
                                //        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                                //        while (accounts.MoveNext())
                                //        {
                                //            JsonElement account = accounts.Current;
                                //            tempCode.teacherCount += 1;
                                //        }
                                //    }
                                //}

                                //查询学校学生人数
                                string sqlStu = $"select count(c.id) as totals from c";
                                tempCode.studentCount += await CommonFind.FindTotals(cosmosClient, sqlStu, "Student", $"Base-{itemSchool}");

                                //await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Student").GetItemQueryStreamIterator(queryText: $"select c.id from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{itemSchool}") }))
                                //{
                                //    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                                //    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                                //    {
                                //        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                                //        while (accounts.MoveNext())
                                //        {
                                //            JsonElement account = accounts.Current;
                                //            tempCode.studentCount += 1;
                                //        }
                                //    }
                                //}
                            }
                        }

                        var tempModel = provinceStics.Where(x => x.provCode == tempCode.provCode).FirstOrDefault();
                        if (tempModel != null)
                        {
                            tempModel.schoolCount = tempCode.schoolCount;
                            tempModel.teacherCount = tempCode.teacherCount;
                            tempModel.studentCount = tempCode.studentCount;
                            tempModel.standardCount +=1;
                        }
                    }
                    else
                    {
                        tempProvinceStics.provCode = itemStandard.provCode;
                        tempProvinceStics.provName = itemStandard.provName;
                        tempProvinceStics.standardCount += 1;

                        string sqlSc = $"select c.id from c where c.standard='{itemStandard.standard}'";
                        List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, sqlSc, "Base");
                        tempProvinceStics.schoolCount += schoolIds.Count;
                        //List<string> schoolIds = new List<string>();
                        //await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"select c.id from c where c.standard='{itemStandard.standard}'", 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)
                        //    {
                        //        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                        //        while (accounts.MoveNext())
                        //        {
                        //            JsonElement account = accounts.Current;
                        //            schoolIds.Add(account.GetProperty("id").GetString());
                        //            tempProvinceStics.schoolCount += 1;
                        //        }
                        //    }
                        //}
                        if (schoolIds.Count > 0)
                        {
                            foreach (var itemSchool in schoolIds)
                            {
                                //查询学校教师人数
                                string sqlT = $"select count(c.id) as totals from c join a1 in c.schools where a1.schoolId='{itemSchool}'";
                                tempProvinceStics.teacherCount += await CommonFind.FindTotals(cosmosClient, sqlT, "Teacher", "Base");

                                //await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIterator(queryText: $"select c.id from c join a1 in c.schools where a1.schoolId='{itemSchool}'", 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)
                                //    {
                                //        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                                //        while (accounts.MoveNext())
                                //        {
                                //            JsonElement account = accounts.Current;
                                //            tempProvinceStics.teacherCount += 1;
                                //        }
                                //    }
                                //}

                                //查询学校学生人数
                                string sqlStu = "select count(c.id) as totals from c";
                                tempProvinceStics.studentCount += await CommonFind.FindTotals(cosmosClient, sqlStu, "Student", $"Base-{itemSchool}");

                                //await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Student").GetItemQueryStreamIterator(queryText: $"select c.id from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{itemSchool}") }))
                                //{
                                //    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                                //    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                                //    {
                                //        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                                //        while (accounts.MoveNext())
                                //        {
                                //            JsonElement account = accounts.Current;
                                //            tempProvinceStics.studentCount += 1;
                                //        }
                                //    }
                                //}
                            }
                        }

                        provinceStics.Add(tempProvinceStics);
                    }
                }

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

        /// <summary>
        /// 查询所有市级下的学校数量
        /// </summary>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-cityschool")]
        public async Task<IActionResult> GetCitySchool() 
        {
            try
            {
                var cosmosClient = _azureCosmos.GetCosmosClient();

                List<CityStandard> standards = new List<CityStandard>();
                //查询省份区域
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Normal").GetItemQueryIterator<Area>(queryText: $"select c.cityCode,c.cityName,c.standard from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-Area") }))
                {
                    CityStandard Citystics = new CityStandard
                    {
                        cityCode = item.cityCode,
                        cityName = item.cityName,
                        standard = item.standard
                    };
                    standards.Add(Citystics);
                }

                List<CitySchool> citySchools = new List<CitySchool>();  //返回数量
                foreach (var itemStandrd in standards)
                {
                    CitySchool citySchool = new CitySchool();
                    var tempCode = citySchools.Find(x => x.cityCode == itemStandrd.cityCode);
                    if (tempCode != null)
                    {
                        string sqlTxt = $"select count(c.id) totals from c where c.standard='{itemStandrd.standard}'";
                        tempCode.schoolCount += await CommonFind.FindTotals(cosmosClient, sqlTxt, "School", "Base");

                        //await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"select c.id from c where c.standard='{itemStandrd.standard}'", 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)
                        //    {
                        //        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                        //        while (accounts.MoveNext())
                        //        {
                        //            JsonElement account = accounts.Current;
                        //            tempCode.schoolCount += 1;
                        //        }
                        //    }
                        //}
                        var tempModel = citySchools.Where(x => x.cityCode == tempCode.cityCode).FirstOrDefault();
                        if (tempModel != null)
                        {
                            tempModel.schoolCount = tempCode.schoolCount;
                        }
                    }
                    else 
                    {
                        citySchool.cityCode = itemStandrd.cityCode;
                        citySchool.cityName = itemStandrd.cityName;

                        string sqlTxt = $"select count(c.id) totals from c where c.standard='{itemStandrd.standard}'";
                        citySchool.schoolCount += await CommonFind.FindTotals(cosmosClient, sqlTxt, "School", "Base");

                        //List<string> schoolIds = new List<string>();
                        //await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"select c.id from c where c.standard='{itemStandrd.standard}'", 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)
                        //    {
                        //        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                        //        while (accounts.MoveNext())
                        //        {
                        //            JsonElement account = accounts.Current;
                        //            schoolIds.Add(account.GetProperty("id").GetString());
                        //            citySchool.schoolCount += 1;
                        //        }
                        //    }
                        //}

                        citySchools.Add(citySchool);
                    }
                }
                return Ok(new { state = 200, citySchools });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI, {_option.Location}   /homestatis/get-cityschool   \n {ex.Message}{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }
        
        /// <summary>
        /// 依据市级ID查询区域（区、县、郡）的学校、教师、学生数量
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-districtstics")]
        public async Task<IActionResult> GetDistrictStics(JsonElement jsonElement) 
        {
            try
            {
                if (!jsonElement.TryGetProperty("cityCode", out JsonElement _cityCode)) return BadRequest();
                var cosmosClient = _azureCosmos.GetCosmosClient();

                List<DistrictStandard> districtStandards = new List<DistrictStandard>();
                //查询省份区域
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Normal").GetItemQueryIterator<Area>(queryText: $"select c.name,c.cityName,c.standard from c where c.cityCode='{_cityCode}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-Area") }))
                {
                    DistrictStandard districtStandard = new DistrictStandard
                    {
                        cityName = item.cityName,
                        distName = item.name,
                        standard = item.standard
                    };

                    districtStandards.Add(districtStandard);
                }

                List<DistrictStics> districtSticss = new List<DistrictStics>(); //返回数据

                foreach (var itemStandrd in districtStandards)
                {
                    DistrictStics districtStics = new DistrictStics();
                    districtStics.cityName = itemStandrd.cityName;
                    districtStics.distName = itemStandrd.distName;

                    string sqlTxt = $"select c.id from c where c.standard='{itemStandrd.standard}'";
                    List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient,sqlTxt, "Base");
                    districtStics.schoolCount += schoolIds.Count;

                    //List<string> schoolIds = new List<string>();
                    ////查询区域学校数量
                    //await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"select c.id from c where c.standard='{itemStandrd.standard}'", 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)
                    //    {
                    //        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                    //        while (accounts.MoveNext())
                    //        {
                    //            JsonElement account = accounts.Current;
                    //            schoolIds.Add(account.GetProperty("id").GetString());
                    //            districtStics.schoolCount += 1;
                    //        }
                    //    }
                    //}

                    if (schoolIds.Count > 0) 
                    {
                        foreach (var itemSchool in schoolIds)
                        {
                            //查询学校教师人数
                            string sqlT = $"select count(c.id) as totals from c join a1 in c.schools where a1.schoolId='{itemSchool}'";
                            districtStics.teacherCount += await CommonFind.FindTotals(cosmosClient, sqlT, "Teacher", "Base");

                            //await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIterator(queryText: $"select c.id from c join a1 in c.schools where a1.schoolId='{itemSchool}'", 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)
                            //    {
                            //        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                            //        while (accounts.MoveNext())
                            //        {
                            //            JsonElement account = accounts.Current;
                            //            districtStics.teacherCount += 1;
                            //        }
                            //    }
                            //}

                            //查询学校学生人数

                            string sqlS = $"select count(c.id) as totals from c ";
                            districtStics.studentCount += await CommonFind.FindTotals(cosmosClient, sqlS, "Student", $"Base-{itemSchool}");

                            //await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Student").GetItemQueryStreamIterator(queryText: $"select c.id from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{itemSchool}") }))
                            //{
                            //    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                            //    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                            //    {
                            //        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                            //        while (accounts.MoveNext())
                            //        {
                            //            JsonElement account = accounts.Current;
                            //            districtStics.studentCount += 1;
                            //        }
                            //    }
                            //}
                        }                        
                    }
                    districtSticss.Add(districtStics);
                }
                return Ok(new { state = 200, districtSticss });

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

        /// <summary>
        /// 查询市的学校数据，以及市级下的区中的学校，教师、学生数量
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-cityallstics")]
        public async Task<IActionResult> GetCityAllStics(JsonElement jsonElement)
        {
            try
            {
                var cosmosClient = _azureCosmos.GetCosmosClient();

                List<AllCityStics> tempAllCityStics = new List<AllCityStics>();
                //查询省份区域
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Normal").GetItemQueryIterator<Area>(queryText: $"select c.name,c.cityCode,c.cityName,c.standard from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-Area") }))
                {
                    AllCityStics Citystics = new AllCityStics
                    {
                        cityCode = item.cityCode,
                        cityName = item.cityName,
                        distName = item.name,
                        standard = item.standard
                    };
                    tempAllCityStics.Add(Citystics);
                }

                List<SticsCitys> sticsCitys = new List<SticsCitys>();
                foreach(var itemStandrd in tempAllCityStics)
                {
                    SticsCitys citySchool = new SticsCitys();
                    var tempCode = sticsCitys.Find(x => (x.cityCode) == (itemStandrd.cityCode));
                    if (tempCode != null)
                    {
                        List<string> schoolIds = new List<string>();

                        await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"select c.id from c where c.standard='{itemStandrd.standard}'", 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)
                            {
                                var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                                while (accounts.MoveNext())
                                {
                                    JsonElement account = accounts.Current;
                                    schoolIds.Add(account.GetProperty("id").GetString());
                                    tempCode.schoolCount += 1;
                                }
                            }
                        }

                        var tempModel = sticsCitys.Where(x => x.cityCode == tempCode.cityCode).FirstOrDefault();
                        if (tempModel != null)
                        {
                            tempModel.schoolCount = tempCode.schoolCount;
                        }

                        DistrictStics districtStics = new DistrictStics();
                        districtStics.cityName = itemStandrd.cityName;
                        districtStics.distName = itemStandrd.distName;
                        districtStics.schoolCount = schoolIds.Count;

                        if (schoolIds.Count > 0)
                        {
                            foreach (var itemSchool in schoolIds) 
                            {
                                //查询学校教师人数
                                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIterator(queryText: $"select c.id from c join a1 in c.schools where a1.schoolId='{itemSchool}'", 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)
                                    {
                                        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                                        while (accounts.MoveNext())
                                        {
                                            JsonElement account = accounts.Current;
                                            districtStics.teacherCount += 1;
                                        }
                                    }
                                }

                                //查询学校学生人数
                                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Student").GetItemQueryStreamIterator(queryText: $"select c.id from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{itemSchool}") }))
                                {
                                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                                    {
                                        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                                        while (accounts.MoveNext())
                                        {
                                            JsonElement account = accounts.Current;
                                            districtStics.studentCount += 1;
                                        }
                                    }
                                }
                            }
                        }

                        var tempDistrict = sticsCitys.Where(x => x.cityCode == tempCode.cityCode).FirstOrDefault();
                        if (tempDistrict != null)
                        {
                            tempDistrict.districtSticss.Add(districtStics);
                        }
                    }
                    else
                    {
                        citySchool.cityCode = itemStandrd.cityCode;
                        citySchool.cityName = itemStandrd.cityName;

                        List<string> schoolIds = new List<string>();
                        await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"select c.id from c where c.standard='{itemStandrd.standard}'", 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)
                            {
                                var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                                while (accounts.MoveNext())
                                {
                                    JsonElement account = accounts.Current;
                                    schoolIds.Add(account.GetProperty("id").GetString());
                                    citySchool.schoolCount += 1;
                                }
                            }
                        }

                        List<DistrictStics> tempDistrictStics = new List<DistrictStics>();
                        if (schoolIds.Count > 0)
                        {
                            DistrictStics districtStics = new DistrictStics();
                            districtStics.cityName = itemStandrd.cityName;
                            districtStics.distName= itemStandrd.distName;
                            districtStics.schoolCount = schoolIds.Count;
                            foreach (var itemSchool in schoolIds)
                            {
                                //查询学校教师人数
                                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIterator(queryText: $"select c.id from c join a1 in c.schools where a1.schoolId='{itemSchool}'", 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)
                                    {
                                        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                                        while (accounts.MoveNext())
                                        {
                                            JsonElement account = accounts.Current;
                                            districtStics.teacherCount += 1;
                                        }
                                    }
                                }

                                //查询学校学生人数
                                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Student").GetItemQueryStreamIterator(queryText: $"select c.id from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{itemSchool}") }))
                                {
                                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                                    {
                                        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                                        while (accounts.MoveNext())
                                        {
                                            JsonElement account = accounts.Current;
                                            districtStics.studentCount += 1;
                                        }
                                    }
                                }
                            }
                            tempDistrictStics.Add(districtStics);
                        }

                        citySchool.districtSticss = tempDistrictStics;
                        sticsCitys.Add(citySchool);
                    }

                }

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

        /// <summary>
        /// 查询BI权限开放人数
        /// </summary>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-sticsbipower")]
        public async Task<IActionResult> GetSticsBIPower()
        {
            try
            {
                Dictionary<string, object> dic = new Dictionary<string, object> { { "PartitionKey", "authority-bi" } };
                List<Authority> authorityBIList = await _azureStorage.FindListByDict<Authority>(dic);

                var cosmosClient = _azureCosmos.GetCosmosClient();
                List<PowerStics> powerStics = new List<PowerStics>(); 
                foreach (var temp in authorityBIList)
                {
                    PowerStics tempPowerStics = new PowerStics();
                    tempPowerStics.powerName = temp.Discription;

                    //查询学校权限
                    string sqlTxt = $"SELECT DISTINCT REPLACE(c.code, 'Teacher-', '') AS schoolId, c.id, c.name FROM c WHERE ARRAY_CONTAINS(c.permissions, '{temp.RowKey}', true) AND c.pk = 'Teacher' AND c.status = 'join'";
                    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())
                        {
                            tempPowerStics.powerCount += 1;
                        }
                    }
                    powerStics.Add(tempPowerStics);
                }

                return Ok(new { state = 200, powerStics });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI,{_option.Location} /homestatis/get-sticsbipower  {ex.Message}{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 统计学校和教师空间类型
        /// </summary>
        /// <returns></returns>
        [HttpPost("get-datatypestics")]
        public async Task<IActionResult> GetDataTypeStics() 
        {
            try
            {
                long totalSize = 0; //总空间
                long useSize = 0; //已使用空间                
                long teach = 0;  //学校已经分配给所有教师的空间大小GB。
                Dictionary<string, long> typeStics = new Dictionary<string, long>(); //所有类型
                Dictionary<string, double?> typeStics1 = new Dictionary<string, double?>(); //所有类型

                List<string> schoolId = new List<string>();
                var cosmosClient = _azureCosmos.GetCosmosClient();
                //查询学校空间和学校Id
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS,"School").GetItemQueryStreamIterator(queryText:$"select c.id,c.size from c",requestOptions:new QueryRequestOptions() { PartitionKey = new PartitionKey("Base")}))
                {
                    var json = await JsonDocument.ParseAsync(item.ContentStream);
                    foreach (var tempItem in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        totalSize += tempItem.GetProperty("size").GetInt64();
                        schoolId.Add(tempItem.GetProperty("id").GetString());
                    }
                }

                //查询学校已使用空间大小
                foreach (var itemId in schoolId)
                {
                    await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"SELECT sum(c.size) as size FROM c ",
                        requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Teacher-{itemId}") }))
                    {
                        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;
                            }
                        }
                    }

                    Dictionary<string, double?> schoolStics = new Dictionary<string, double?>(); //学校空间
                    long blobsize = 0;
                    RedisValue value = default;
                    value = _azureRedis.GetRedisClient(8).HashGet($"Blob:Record", itemId);
                    if (value != default && !value.IsNullOrEmpty)
                    {
                        JsonElement record = value.ToString().ToObject<JsonElement>();
                        if (record.TryGetInt64(out blobsize))
                        {
                        }
                    }
                    else
                    {
                        var client = _azureStorage.GetBlobContainerClient(itemId);
                        var size = await client.GetBlobsCatalogSize();
                        await _azureRedis.GetRedisClient(8).HashSetAsync($"Blob:Record", itemId, size.Item1);
                        foreach (var key in size.Item2.Keys)
                        {
                            await _azureRedis.GetRedisClient(8).SortedSetRemoveAsync($"Blob:Catalog:{itemId}", key);
                            await _azureRedis.GetRedisClient(8).SortedSetIncrementAsync($"Blob:Catalog:{itemId}", key, size.Item2[key].HasValue ? size.Item2[key].Value : 0);
                        }
                        useSize += (long)size.Item1;
                        schoolStics = size.Item2;
                        typeStics1 = (from e in typeStics1.Concat(schoolStics) group e by e.Key into g select new { Name = g.Key, Count = g.Sum(kvp => kvp.Value) }).ToDictionary(item => item.Name, item => item.Count);
                        continue;
                    }

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

                        useSize += blobsize;
                        typeStics1 = (from e in typeStics1.Concat(schoolStics) group e by e.Key into g select new { Name = g.Key, Count = g.Sum(kvp => kvp.Value) }).ToDictionary(item => item.Name, item => item.Count);
                        continue;

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

                        useSize += (long)size.Item1;
                        schoolStics = size.Item2;
                        typeStics1 = (from e in typeStics1.Concat(schoolStics) group e by e.Key into g select new { Name = g.Key, Count = g.Sum(kvp => kvp.Value) }).ToDictionary(item => item.Name, item => item.Count);
                        continue;
                    }

                    ////RedisValue value = _azureRedis.GetRedisClient(8).HashGet($"Blob:Record", itemId);
                    //if (!value.IsNullOrEmpty)
                    //{
                    //    useSize += Convert.ToInt64(value);
                    //    //JsonElement record = value.ToString().ToObject<JsonElement>();
                    //    //long tempSize = 0;
                    //    //if (record.TryGetInt64(out tempSize))
                    //    //{
                    //    //    sizeS += tempSize;
                    //    //}
                    //}
                    //else
                    //{
                    //    var client = _azureStorage.GetBlobContainerClient(itemId);
                    //    var size = await client.GetBlobsCatalogSize();
                    //    var temp = await _azureRedis.GetRedisClient(8).HashSetAsync($"Blob:Record", itemId, size.Item1);
                    //    foreach (var itemKey in size.Item2.Keys)
                    //    {
                    //        var temp1 = await _azureRedis.GetRedisClient(8).SortedSetRemoveAsync($"Blob:Catalog:{itemId}", itemKey);
                    //        var temp2 = await _azureRedis.GetRedisClient(8).SortedSetIncrementAsync($"Blob:Catalog:{itemId}", itemKey, size.Item2[itemKey].HasValue ? size.Item2[itemKey].Value : 0);
                    //        useSize += Convert.ToInt64(temp2);
                    //    }
                    //}

                    //SortedSetEntry[] sortedSetEntries = await _azureRedis.GetRedisClient(8).SortedSetRangeByRankWithScoresAsync($"Blob:Catalog:{itemId}");
                    //if (sortedSetEntries != null)
                    //{
                    //    foreach (var tempSorted in sortedSetEntries)
                    //    {
                    //        if (typeStics.TryGetValue($"{tempSorted.Element}", out long val))
                    //            typeStics[$"{tempSorted.Element}"] = Convert.ToInt64(tempSorted.Score) != 0 ? val + Convert.ToInt64(tempSorted.Score) : 0;
                    //        else
                    //            typeStics.Add($"{tempSorted.Element}", Convert.ToInt64(tempSorted.Score));
                    //    }
                    //}
                }
                
                ////教师数据
                //List<string> teacherId = new List<string>(); //教师Id集合
                ////查询教师的大小和教师集合信息
                //await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIterator(queryText: $"select c.id,c.size from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
                //{
                //    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                //    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                //    {
                //        totalSize += obj.GetProperty("size").GetInt64();
                //        teacherId.Add(obj.GetProperty("id").GetString());
                //    }
                //}

                ////查询教师已使用空间大小
                //foreach (var itemTeach in teacherId)
                //{

                //    Dictionary<string, double?> teachStics = new Dictionary<string, double?>(); //学校空间

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

                //        useSize += (long)size.Item1;
                //        teachStics = size.Item2;
                //        typeStics1 = (from e in typeStics1.Concat(teachStics) group e by e.Key into g select new { Name = g.Key, Count = g.Sum(kvp => kvp.Value) }).ToDictionary(item => item.Name, item => item.Count);
                //        continue;

                //    }

                //    SortedSetEntry[] Scores = _azureRedis.GetRedisClient(8).SortedSetRangeByScoreWithScores($"Blob:Catalog:{itemTeach}");
                //    if (Scores != null)
                //    {
                //        foreach (var score in Scores)
                //        {
                //            double val = score.Score;
                //            string key = score.Element.ToString();
                //            teachStics.Add(key, val);
                //        }

                //        useSize += blobsize;
                //        typeStics1 = (from e in typeStics1.Concat(teachStics) group e by e.Key into g select new { Name = g.Key, Count = g.Sum(kvp => kvp.Value) }).ToDictionary(item => item.Name, item => item.Count);
                //        continue;

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

                //        useSize += (long)size.Item1;
                //        teachStics = size.Item2;
                //        typeStics1 = (from e in typeStics1.Concat(teachStics) group e by e.Key into g select new { Name = g.Key, Count = g.Sum(kvp => kvp.Value) }).ToDictionary(item => item.Name, item => item.Count);
                //        continue;
                //    }

                //    //RedisValue tempValue = _azureRedis.GetRedisClient(8).HashGet($"Blob:Record", itemTeach);
                //    //if (!tempValue.IsNullOrEmpty)
                //    //{
                //    //    useSize += Convert.ToInt64(tempValue);
                //    //    //JsonElement record = tempValue.ToString().ToObject<JsonElement>();
                //    //    //long tempSize = 0;
                //    //    //if (record.TryGetInt64(out tempSize))
                //    //    //{
                //    //    //    sizeT += tempSize;
                //    //    //}
                //    //}

                //    //SortedSetEntry[] tempSorted = await _azureRedis.GetRedisClient(8).SortedSetRangeByRankWithScoresAsync($"Blob:Catalog:{itemTeach}");
                //    //if (tempSorted != null)
                //    //{
                //    //    foreach (var itemSorted in tempSorted)
                //    //    {
                //    //        if (typeStics.TryGetValue($"{itemSorted.Element}", out long val))
                //    //            typeStics[$"{itemSorted.Element}"] = Convert.ToInt64(itemSorted.Score) != 0 ? val + Convert.ToInt64(itemSorted.Score) : 0;
                //    //        else
                //    //            typeStics.Add($"{itemSorted.Element}", Convert.ToInt64(itemSorted.Score));
                //    //    }
                //    //}
                //}

                return Ok(new { state = 200, totalSize, teach, useSize, stics = typeStics1.ToList() });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI，{_option.Location}   /homestatis/get-datatypestics   \n  {ex.Message}{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 统计教师的空间类型
        /// </summary>
        /// <returns></returns>
        [HttpPost("get-teachetypestics")]
        public async Task<IActionResult> GetTeachDataTypeStics() 
        {
            try
            {
                long teacheSize = 0; //教师空间
                long sizeT = 0; //教师空间
                List<string> teacherId = new List<string>(); //教师Id集合
                Dictionary<string, long> typeStics = new Dictionary<string, long>(); //所有类型
                var cosmosClient = _azureCosmos.GetCosmosClient();

                //查询教师的大小和教师集合信息
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIterator(queryText: $"select c.id,c.size from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        teacheSize += obj.GetProperty("size").GetInt64();
                        teacherId.Add(obj.GetProperty("id").GetString());
                    }
                }

                //查询教师已使用空间大小
                foreach (var itemTeach in teacherId)
                {
                    RedisValue tempValue = _azureRedis.GetRedisClient(8).HashGet($"Blob:Record", itemTeach);
                    if (!tempValue.IsNullOrEmpty)
                    {
                        sizeT += Convert.ToInt64(tempValue);
                        //JsonElement record = tempValue.ToString().ToObject<JsonElement>();
                        //long tempSize = 0;
                        //if (record.TryGetInt64(out tempSize))
                        //{
                        //    sizeT += tempSize;
                        //}
                    }

                    SortedSetEntry[] tempSorted = await _azureRedis.GetRedisClient(8).SortedSetRangeByRankWithScoresAsync($"Blob:Catalog:{itemTeach}");
                    if (tempSorted != null)
                    {
                        foreach (var itemSorted in tempSorted)
                        {
                            if (typeStics.TryGetValue($"{itemSorted.Element}", out long val))
                                typeStics[$"{itemSorted.Element}"] = Convert.ToInt64(itemSorted.Score) != 0 ? val + Convert.ToInt64(itemSorted.Score) : 0;
                            else
                                typeStics.Add($"{itemSorted.Element}", Convert.ToInt64(itemSorted.Score));
                        }
                    }
                }

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

        }



        /// <summary>
        /// 存放的类型
        /// </summary>
        public record AllDataType
        {
            public long vote { get; set; }/// 投票josn
            public long thum { get; set; }//视频和封面缩略图
            public long avatar { get; set; }//学生头像
            public long test { get; set; }//
            public long survey { get; set; }//问卷调查 josn
            public long image { get; set; }// PNG
            public long other { get; set; }//其他
            public long doc { get; set; }//文档
            public long homework { get; set; }//作业  json
            public long item { get; set; }// 题库 json
            public long paper { get; set; }//试卷  json
            public long train { get; set; }// 研修
            public long res { get; set; }//  教程 htx
            public long exam { get; set; }//评测  json
            public long syllabus { get; set; }// 课纲 json
            public long video { get; set; }// 视频
            public long yxpt { get; set; }//研修平台
            public long jyzx { get; set; }// 教研中心
            public long records { get; set; } //HiTeach上传的数据
        }

        /// <summary>
        /// 省的数据标准
        /// </summary>
        public record ProvinceStics 
        {
            /// <summary>
            /// 省份ID
            /// </summary>
            public string provCode { get; set; }

            /// <summary>
            /// 省份名称
            /// </summary>
            public string provName { get; set; }

            /// <summary>
            /// 省份下的方案数量
            /// </summary>
            public int standardCount { get; set; }

            /// <summary>
            /// 省份下的学校
            /// </summary>
            public int schoolCount { get; set; }

            /// <summary>
            /// 省份下的教师数量
            /// </summary>
            public int teacherCount { get; set; }

            /// <summary>
            /// 省份下的学生数量
            /// </summary>
            public int studentCount { get; set; }
        }

        /// <summary>
        /// 省以下的区域标准
        /// </summary>
        public record ProvinceStandard
        {
            /// <summary>
            /// 城市Code
            /// </summary>
            public string provCode { get; set; }

            /// <summary>
            /// 城市名称
            /// </summary>
            public string provName { get; set; }

            /// <summary>
            /// 区域标准
            /// </summary>
            public string standard { get; set; }
        }


        public record PowerStics 
        {
            public string powerName { get; set; }

            public int powerCount { get; set; }
        
        }

        /// <summary>
        /// 市的学校数据，及以下数据
        /// </summary>
        public record SticsCitys
        {
            /// <summary>
            /// 市级Code
            /// </summary>
            public string cityCode { get; set; }

            /// <summary>
            /// 市级名称
            /// </summary>
            public string cityName { get; set; }

            /// <summary>
            /// 学校数量
            /// </summary>
            public int schoolCount { get; set; }

            /// <summary>
            /// 市级下的区域，学校、
            /// </summary>
            public List<DistrictStics> districtSticss { get; set; }
        }

        /// <summary>
        /// 所有区域标准
        /// </summary>
        public record AllCityStics
        {
            /// <summary>
            /// 城市Code
            /// </summary>
            public string cityCode { get; set; }

            /// <summary>
            /// 城市名称
            /// </summary>
            public string cityName { get; set; }
            /// <summary>
            /// 区名称
            /// </summary>
            public string distName { get; set; }

            /// <summary>
            /// 区域标准
            /// </summary>
            public string standard { get; set; }
        }

        /// <summary>
        /// 统计区域数量
        /// </summary>
        public record DistrictStics 
        {
            /// <summary>
            /// 市级名称
            /// </summary>
            public string cityName { get; set; }

            /// <summary>
            /// 地区名称
            /// </summary>
            public string distName { get; set; }

            /// <summary>
            /// 学校数量
            /// </summary>
            public int schoolCount { get; set; }

            /// <summary>
            /// 教师数量
            /// </summary>
            public int teacherCount { get; set; }

            /// <summary>
            /// 学生数量
            /// </summary>
            public int studentCount { get; set; }
        }

        /// <summary>
        /// 区域标准
        /// </summary>
        public record DistrictStandard() 
        {
            /// <summary>
            /// 市名称
            /// </summary>
            public string cityName { get; set; }

            /// <summary>
            /// 区域名称
            /// </summary>
            public string distName { get; set; }

            /// <summary>
            /// 区域标准
            /// </summary>
            public string standard { get; set; }
        }

        /// <summary>
        /// 市级学校数量
        /// </summary>
        public record CitySchool 
        {
            /// <summary>
            /// 市级ID
            /// </summary>
            public string cityCode { get; set; }

            /// <summary>
            /// 市级名称
            /// </summary>
            public string cityName { get; set; }

            /// <summary>
            /// 市级学校数量
            /// </summary>
            public int schoolCount { get; set;}
        }

        /// <summary>
        /// 市级信息标准
        /// </summary>
        public record CityStandard
        {
            /// <summary>
            /// 城市Code
            /// </summary>
            public string cityCode { get; set; }

            /// <summary>
            /// 城市名称
            /// </summary>
            public string cityName { get; set; }

            /// <summary>
            /// 区域标准
            /// </summary>
            public string standard { get; set; }
        }

        /// <summary>
        /// 区域
        /// </summary>
        public record SticsCity
        {
            /// <summary>
            /// 省份
            /// </summary>
            public string provName { get; set; }

            /// <summary>
            /// 市名称
            /// </summary>
            public string cityName { get; set; }

            /// <summary>
            /// 区域名称
            /// </summary>
            public string distName { get; set; }

            /// <summary>
            /// 区域标准
            /// </summary>
            public string standard { get; set; }
        }

        /// <summary>
        /// 返回统计数据
        /// </summary>
        public record SticsDist 
        {
            /// <summary>
            /// 省份
            /// </summary>
            public string provName { get; set; }

            /// <summary>
            /// 市名
            /// </summary>
            public string cityName { get; set; }

            /// <summary>
            /// 区域标准名称
            /// </summary>
            public string distName { get; set; }

            /// <summary>
            /// 区域标准
            /// </summary>
            public string standard { get; set; }

            /// <summary>
            /// 学校数量
            /// </summary>
            public int schoolCount { get; set; }

            /// <summary>
            /// 教师人数
            /// </summary>
            public int teacherCount { get; set; }

            /// <summary>
            /// 学生人数
            /// </summary>
            public int studentCount { get; set; }
        }
    }
}