TEAMModelOS/TEAMModeBI/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;

namespace TEAMModeBI.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;

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

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

                //依据学校查询教师人数
                List<string> teacherCount_list = new();
                //依据学校查询学生信息
                List<string> studentCount_List = new();
                //学校数
                List<string> schoolCount_List = new();
                //学校空间大小
                int schoolsize = 0;

                //查询全部教师人数
                await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIterator(queryText: $"select * 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 += int.Parse(account.GetProperty("size").ToString());
                        }
                    }
                }

                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;

                    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 = schoolCount;

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

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

                    //查询学校下面的教师人数
                    foreach (var itemSchool in schoolIds)
                    {
                        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;
                                }
                            }
                        }

                        //查询全部学生人数
                        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;
                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-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)
                    {
                        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;
                        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;
                                    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;

                    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)
                        {
                            //查询学校教师人数
                            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;
                                    }
                                }
                            }
                        }                        
                    }
                    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>();

                        try
                        {
                            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;
                                    }
                                }
                            }
                        }
                        catch
                        {
                        }
                        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();
            }
        }


        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; }
        }
    }
}