TEAMModelOS/TEAMModelBI/Controllers/Census/ActivitySticsController.cs

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

namespace TEAMModelBI.Controllers.Census
{
    [Route("activity")]
    [ApiController]
    public class ActivitySticsController : ControllerBase
    {
        private readonly AzureCosmosFactory _azureCosmos;
        private readonly AzureStorageFactory _azureStorage;
        private readonly DingDing _dingDing;
        private readonly Option _option;
        private readonly CoreAPIHttpService _coreAPIHttpService;
        public readonly List<string> types = new List<string> { "Exam", "Survey", "Vote", "Homework" };

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

        /// <summary>
        /// 统计活动 传醍摩豆则查询相关的学校活动
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-count")]
        public async Task<IActionResult> GetCount(JsonElement jsonElement) 
        {
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            if(!jsonElement.TryGetProperty("term", out JsonElement term)) return BadRequest();
            long start = 0, end = 0;
            if (bool.Parse($"{term}") == true) 
            {
                (start, end) = TimeHelper.GetTermStartOrEnd(DateTime.Now);
            }
            var cosmosClient = _azureCosmos.GetCosmosClient();
            List<object> activityCount = new List<object>();

            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");
                
                foreach (var itemId in schoolIds)
                {
                    School school = new();
                    var response = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(itemId, new PartitionKey("Base"));
                    if (response.Status == 200)
                    {
                        using var json = await JsonDocument.ParseAsync(response.ContentStream);
                        school = json.ToObject<School>();
                    }

                    ActivityCount tempCount = new ActivityCount() { id = itemId, name = school.name != null ? school.name : itemId };

                    foreach (var type in types)
                    {
                        StringBuilder sqlTxt = new StringBuilder($"select COUNT(c.id) AS totals from c where c.pk='{type}' and c.school='{itemId}' ");
                        if (bool.Parse($"{term}") == true)
                        {
                            sqlTxt.Append($" and c.createTime >= {start} and c.createTime <= {end}");
                        }
                        long totals = await CommonFind.FindTotals(cosmosClient, sqlTxt.ToString(), new List<string>() { "Common" });

                        tempCount.census.Add(new KeyValuePair<object, long>(type, totals));
                    }
                    activityCount.Add(tempCount);
                }
            }
            else 
            {
                foreach (var type in types)
                {
                    StringBuilder sqlTxt = new StringBuilder($"SELECT COUNT(c.id) AS totals FROM c where c.pk='{type}' ");
                    if (bool.Parse($"{term}") == true)
                    {
                        sqlTxt.Append($" and c.createTime >= {start} and c.createTime <= {end}");
                    }

                    long totals = await CommonFind.FindTotals(cosmosClient, sqlTxt.ToString(), new List<string>() { "Common" });

                    activityCount.Add(new KeyValuePair<string, object>(type, totals));
                }
            }

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

        /// <summary>
        /// 统计所有的评量活动，问卷调查，投票活动，作业
        /// </summary>
        /// <returns></returns>
        [HttpPost("get-allactivity")]
        public async Task<IActionResult> GetAllActivity() 
        {
            try
            {
                var cosmosClient = _azureCosmos.GetCosmosClient();
                List<KeyValuePair<string, long>> typeCount = new List<KeyValuePair<string, long>>();
                foreach (var type in types)
                {
                    string querySql = $"SELECT Count(c.id) as totals FROM c where c.pk='{type}'  ";
                    
                    long totals = await CommonFind.FindTotals(cosmosClient, querySql, new List<string>() { "Common" });

                    KeyValuePair<string, long> valuePair = new KeyValuePair<string, long>(type, totals);
                    typeCount.Add(valuePair);
                }

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

        /// <summary>
        /// 统计顾问关联的学校活动数量：评测、问卷、投票、作业
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-assistactivity")]
        public async Task<IActionResult> GetAssistSchoolActivity(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return BadRequest();
            var cosmosClient = _azureCosmos.GetCosmosClient();
            List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");

            List<ActivityCount> activityCounts = new List<ActivityCount>();
            foreach (var itemId in schoolIds)
            {
                School school = new();
                var response = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(itemId, new PartitionKey("Base"));
                if (response.Status == 200)
                {
                    using var json = await JsonDocument.ParseAsync(response.ContentStream);
                    school = json.ToObject<School>();
                }
                ActivityCount activityCount = new ActivityCount() { id = itemId, name = school.name != null ? school.name : itemId };
                foreach (var type in types)
                {
                    string activitySql = $"SELECT Count(c.id) as totals FROM c WHERE c.pk='{type}' AND c.school='{itemId}'";
                    long totals = await CommonFind.FindTotals(cosmosClient, activitySql, new List<string>() { "Common" });

                    activityCount.census.Add(new KeyValuePair<object, long>(type, totals));
                }

                activityCounts.Add(activityCount);
            }

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

        /// <summary>
        /// 统计当前学期的活动,传醍摩豆账户则统计该账户当前学期
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-termactivity")]
        public async Task<IActionResult> GetTermActivity(JsonElement jsonElement) 
        {
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            var cosmosClient = _azureCosmos.GetCosmosClient();

            var (start, end) = TimeHelper.GetTermStartOrEnd(DateTime.Now);

            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");
                List<ActivityCount> activityCounts = new List<ActivityCount>();
                foreach (var schoolId in schoolIds)
                {
                    School school = new();
                    var response = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(schoolId, new PartitionKey("Base"));
                    if (response.Status == 200)
                    {
                        using var json = await JsonDocument.ParseAsync(response.ContentStream);
                        school = json.ToObject<School>();
                    }
                    ActivityCount activityCount = new ActivityCount() { id = schoolId, name = school.name != null ? school.name : schoolId };

                    foreach (var type in types) 
                    {
                        string activitySql = $"SELECT COUNT(c.id) AS totals  FROM c WHERE c.pk='{type}' AND c.school='{school}' and c.createTime >= {start} and c.createTime <= {end}";
                        long totals = await CommonFind.FindTotals(cosmosClient, activitySql, new List<string>() { "Common" });

                        activityCount.census.Add(new KeyValuePair<object, long>(type, totals));
                    }
                    activityCounts.Add(activityCount);
                }

                return Ok(new { state = 200, activityCounts });
            }
            else
            {
                List<KeyValuePair<string, object>> typeCount = new List<KeyValuePair<string, object>>();
                foreach (var type in types)
                {
                    string querySql = $"SELECT COUNT(c.id) AS totals FROM c where c.pk='{type}' and c.createTime >= {start} and c.createTime <= {end}";
                    long totals = await CommonFind.FindTotals(cosmosClient, querySql, new List<string>() { "Common" });

                    KeyValuePair<string, object> valuePair = new KeyValuePair<string, object>(type, totals);
                    typeCount.Add(valuePair);
                }

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

        /// <summary>
        /// 统计区域的活动
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-area")]
        public async Task<IActionResult> GetAreaActovoty(JsonElement jsonElement) 
        {
            if (!jsonElement.TryGetProperty("areaId", out JsonElement areaId)) return BadRequest();
            var cosmosClient = _azureCosmos.GetCosmosClient();
            List<RecSchool> schools = new();

            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<RecSchool>(queryText: $"select c.id,c.name,c.picture,c.scale from c where c.areaId='{areaId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
            {
                schools.Add(item);
            }

            long exemAreaCount = 0;
            long surveyAreaCount = 0;
            long voteAreaCount = 0;
            long homeworkAreaCount = 0;

            List<ActivityCount> activityCount = new();
            foreach (var school in schools)
            {
                ActivityCount tempCount = new ActivityCount() { id = school.id, name = school.name != null ? school.name : school.id };
                foreach (var type in types)
                {
                    StringBuilder sqlTxt = new StringBuilder($"select COUNT(c.id) AS totals from c where c.pk='{type}' and c.school='{school.id}' ");                
                    long totals = await CommonFind.FindTotals(cosmosClient, sqlTxt.ToString(), new List<string>() { "Common" });

                    switch (type)
                    {
                        case "Exam":
                            exemAreaCount += totals;
                            break;
                        case "Survey":
                             surveyAreaCount += totals;
                            break;
                        case "Vote":
                             voteAreaCount += totals;
                            break;
                        case "Homework":
                             homeworkAreaCount += totals;
                            break;
                    }

                    tempCount.census.Add(new KeyValuePair<object, long>(type, totals));
                }
                activityCount.Add(tempCount);
            }

            return Ok(new { state = 200, exemAreaCount, surveyAreaCount, voteAreaCount, homeworkAreaCount, activityCount });
        }

        /// <summary>
        /// 依据区统计区级相关信息包括活动统计
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-areastics")]
        public async Task<IActionResult> GetAreaStics(JsonElement jsonElement) 
        {
            if (!jsonElement.TryGetProperty("areaId", out JsonElement areaId)) return BadRequest();

            var cosmosClient = _azureCosmos.GetCosmosClient();
            List<RecSchool> schools = new();
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<RecSchool>(queryText: $"select c.id,c.name,c.picture,c.scale from c where c.areaId='{areaId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
            {
                schools.Add(item);
            }
            //区级人员
            int countArea = 0;
            //区级评审人员
            int appraiseArea = 0;
            long examAreaCount = 0;   //试卷活动
            long surveyAreaCount = 0;  //问卷活动
            long voteAreaCount = 0;   //投票活动
            long homeworkAreaCount = 0;  //作业活动
            List<SchoolInfos> schoolInfos = new();
            List<ActivityCount> activityCount = new();

            int totalTime = 0;
            foreach (var school in schools)
            {
                int count = 0;
                int appraise = 0;
                await foreach (var info in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<bool>(queryText: $"select value(array_contains(c.permissions,'train-appraise')) from c", requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey($"Teacher-{school.id}") }))
                {
                    if (info)
                    {
                        appraise += 1;
                        appraiseArea += 1;
                    }
                    countArea += 1;
                    count += 1;
                }

                //查询学校的总学时
                totalTime += await CommonFind.FindTotals(cosmosClient, "SELECT sum(c.totalTime) as totals FROM c", "Teacher", $"TeacherTrain-{school.id}");
                //查询学校参训人数
                int tempCount = await CommonFind.FindTotals(cosmosClient, $"select array_length(c.members) as totals from c where c.type='yxtrain'", "School", $"GroupList-{school.id}");
                //学校学生人数
                long stuCount = await CommonFind.FindTotals(cosmosClient, $"select count(c.id) as totals from c", "Student", $"Base-{school.id}");

                schoolInfos.Add(new SchoolInfos { schoolId = school.id, schoolName = school.name, picture = school.picture, scale = school.scale, teacherCount = count, studentCount = stuCount, appraiseCount = appraise, trainCount = tempCount });

                ActivityCount tempActivity = new ActivityCount() { id = school.id, name = school.name != null ? school.name : school.id };
                foreach (var type in types)
                {
                    StringBuilder sqlTxt = new StringBuilder($"select COUNT(c.id) AS totals from c where c.pk='{type}' and c.school='{school.id}' ");
                    long totals = await CommonFind.FindTotals(cosmosClient, sqlTxt.ToString(), new List<string>() { "Common" });

                    switch (type)
                    {
                        case "Exam":
                            examAreaCount += totals;
                            break;
                        case "Survey":
                            surveyAreaCount += totals;
                            break;
                        case "Vote":
                            voteAreaCount += totals;
                            break;
                        case "Homework":
                            homeworkAreaCount += totals;
                            break;
                    }

                    tempActivity.census.Add(new KeyValuePair<object, long>(type, totals));
                }
                activityCount.Add(tempActivity);
            }

            return Ok(new { state = 200, schoolCount = schools.Count, countArea, totalTime, appraiseArea, examAreaCount, surveyAreaCount, voteAreaCount, homeworkAreaCount, schools = schoolInfos, schoolActivity = activityCount });
        }

        /// <summary>
        /// 依据活动Id查询活动详情信息  数据管理工具——查询工具
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-info")]
        public async Task<IActionResult> GetInfo(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("id", out JsonElement id)) return BadRequest();
            jsonElement.TryGetProperty("activity", out JsonElement activity);
            jsonElement.TryGetProperty("isPersonal", out JsonElement isPersonal);
            //if (jsonElement.TryGetProperty("", out JsonElement code)) return BadRequest();

            var cosmosClient = _azureCosmos.GetCosmosClient();
            List<object> infos = new List<object>();
            StringBuilder sqlTxt = new StringBuilder($"select * from c where c.id='{id}'");
            if (!string.IsNullOrEmpty($"{activity}"))
            {
                sqlTxt.Append($" and c.pk='{activity}'");
            }

            if (!string.IsNullOrEmpty($"{isPersonal}"))
            {
                if (bool.Parse($"{isPersonal}") == true)
                {
                    sqlTxt.Append($" and c.scope='private'");
                }
                else
                {
                    sqlTxt.Append($" and c.scope='school'");
                }
            }

            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Common").GetItemQueryStreamIterator(queryText: sqlTxt.ToString(), requestOptions: new QueryRequestOptions() { }))
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                {
                    infos.Add(obj.ToObject<object>());
                }
            }

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


        public record RecSchool
        {
            public string id { get; set; }
            public string name { get; set; }
            public string picture { get; set; }
            public int scale { get; set; }
        }

        private class SchoolInfos
        {
            public string schoolId { get; set; }
            public string schoolName { get; set; }
            public string picture { get; set; }
            //规模
            public int scale { get; set; }
            //教师人数
            public int teacherCount { get; set; }
            //学生人数
            public long studentCount { get; set; }
            //评审人数
            public int appraiseCount { get; set; }
            //参训人数
            public int trainCount { get; set; }
        }

        public record ActivityCount 
        {
            public string id { get; set; }
            public string name { get; set; }
            public List<KeyValuePair<object, long>> census { get; set; } = new List<KeyValuePair<object, long>>();
        }
    }
}