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 DingDing _dingDing;
        private readonly Option _option;

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

        /// <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 { 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 { 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(JsonElement jsonElement) 
        {
            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>
        /// 区域
        /// </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; }
        }


    }
}