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($"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 (weekStart, weekEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "week");
            var (termStart, termEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "term");

            var cosmosClient = _azureCosmos.GetCosmosClient();
            List<RecSchool> schools = new();


            StringBuilder scSqlTxt = new("select c.id,c.name,c.picture,c.size,c.scale,c.type from c");
            if (!string.IsNullOrEmpty($"{areaId}"))
            {
                scSqlTxt.Append($" where c.areaId='{areaId}'");
            }

            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<RecSchool>(queryText: scSqlTxt.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
            {
                schools.Add(item);
            }
            
            int countArea = 0;//区级人员
            int appraiseArea = 0;//区级评审人员
            int areaSize = 0;   //区级空间
            long examAreaCount = 0;   //试卷活动
            long surveyAreaCount = 0;  //问卷活动
            long voteAreaCount = 0;   //投票活动
            long homeworkAreaCount = 0;  //作业活动
            long weekActivity = 0; //周活动数量
            long termActivity=0;   //学期活动
            int Basics = 0; //基础版数
            int standard = 0; //标准版数
            int major = 0;   //专业版数
            int geCount = 0; //普教
            int heCount = 0; //高教
            int oeCount = 0; //其他教育

            List<SchoolLesson> schoolLessons = new();  //学校课例集合 
            List<SchoolInfo> schoolInfos = new();


            int totalTime = 0;
            foreach (var school in schools)
            {
                if (school.type == 2)
                    heCount += 1;
                else if (school.type == 1)
                    geCount += 1;
                else oeCount += 1;

                areaSize += school.size;

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

                //查询是否有服务
                int serCount = await CommonFind.FindTotals(cosmosClient, $"select array_length(c.service) as totals from c where c.id='{school.id}'", "School", "ProductSum");
                if (serCount > 0)
                    major += 1;
                else if (school.size >= 300 && school.scale >= 500)
                    standard += 1;
                else Basics += 1;

                //课例
                int lessCount = await CommonFind.FindTotals(cosmosClient, "select count(c.id) totals from c", "School", $"LessonRecord-{school.id}");
                SchoolLesson schoolLesson = new()
                {
                    id = school.id,
                    name = school.name,
                    picture = school.picture,
                    count = lessCount
                };


                SchoolInfo schoolInfo = new() { 
                    id = school.id, 
                    name = school.name,
                    picture = school.picture,
                    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)
                {
                    long totals = await CommonFind.FindTotals(cosmosClient, $"select COUNT(c.id) AS totals from c where c.pk='{type}' and c.school='{school.id}' ", new List<string>() { "Common" });

                    string weekSql= $"select count(c.id) as totals from c where c.pk='{type}' and c.school='{school.id}' and c.createTime>={weekStart}   and c.createTime<={weekEnd}";
                    long weekCount = await CommonFind.FindTotals(cosmosClient, weekSql, new List<string>() { "Common" });
                    weekActivity += weekCount;

                    string termSql = $"select count(c.id) as totals from c where c.pk='{type}' and c.school='{school.id}' and c.createTime>={termStart}   and c.createTime<={termEnd}";
                    long termCount = await CommonFind.FindTotals(cosmosClient, termSql, new List<string>() { "Common" });
                    termActivity += termCount;

                    switch (type)
                    {
                        case "Exam":
                            examAreaCount += totals;
                            break;
                        case "Survey":
                            surveyAreaCount += totals;
                            break;
                        case "Vote":
                            voteAreaCount += totals;
                            break;
                        case "Homework":
                            homeworkAreaCount += totals;
                            break;
                    }
                    schoolInfo.census.Add(new KeyValuePair<object, long>(type, totals));
                }
                schoolInfos.Add(schoolInfo);
                schoolLessons.Add(schoolLesson);
            }

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

        /// <summary>
        /// 所有区的统计接口
        /// </summary>
        /// <returns></returns>
        [HttpPost("get-all")]
        public async Task<IActionResult> GetAll() 
        {
            var cosmosClient = _azureCosmos.GetCosmosClient();

            List<AreaInfo> areaInfos = new();
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Normal").GetItemQueryIterator<AreaInfo>(queryText: $"select c.id,c.name,c.standard,c.standardName from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base-Area") }))
            {
                areaInfos.Add(item);
            }

            var (weekStart, weekEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "week");
            var (termStart, termEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "term");
            int heCount = 0;//高教
            int geCount = 0;//普教
            int oeCount = 0; //其他教育
            int allSize = 0;//空间总量
            Dictionary<string, long> activitys = new(); //活动类型集合
            long allActivity = 0;  //活动累计
            long weekActivity = 0;//本周活动
            long termActivity = 0;//本学期活动
            long weekLess = 0;      //本周课例
            long termLess = 0;     //本学期课例
            long allLess = 0;     //所有课例
            int tecCount = 0;    //教师数量
            int scCount = 0;    //学校数量

            foreach (var area in areaInfos) 
            {
                List<RecSchool> recSchools = new();
                await foreach (var school in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<RecSchool>(queryText: $"select c.id,c.name,c.picture,c.type,c.size,c.scale from c where c.areaId='{area.id}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") })) 
                {
                    recSchools.Add(school);
                }
                scCount += recSchools.Count;
                recSchools.ForEach(x => { if (x.type == 2) heCount += 1; else if (x.type == 1) geCount += 1; else oeCount += 1; });
                allSize += recSchools.Select(s => s.size).Sum();
                area.schoolCount = recSchools.Count;

                int tempCount = await CommonFind.GetPeopleNumber(cosmosClient, "School", recSchools?.Select(x => x.id).ToList(), "Teacher");

                tecCount += tempCount;
                //查教师
                area.techCount = tempCount;
                //查询学生
                area.stuCount = await CommonFind.GetPeopleNumber(cosmosClient, "Student", recSchools?.Select(x => x.id).ToList(), "Base");
            }

            //统计活动
            foreach (var type in types)
            {
                string sqlTxt = $"select COUNT(c.id) AS totals from c where c.pk='{type}'";
                long totals = await CommonFind.FindTotals(cosmosClient, sqlTxt, new List<string>() { "Common" });

                string weekSql = $"select COUNT(c.id) AS totals from c where c.pk='{type}' and c.createTime>={weekStart} and c.createTime<={weekStart} ";
                weekActivity  += await CommonFind.FindTotals(cosmosClient, weekSql,new List<string> { "Common" });

                string termSql = $"select COUNT(c.id) AS totals from c where c.pk='{type}' and c.createTime>={termStart} and c.createTime<={termEnd} ";
                termActivity += await CommonFind.FindTotals(cosmosClient, termSql, new List<string> { "Common" });

                allActivity += totals;
                activitys.Add(type, totals);
            }
            
            allLess = await CommonFind.FindTotals(cosmosClient, "select count(c.id) as totals from c where c.pk='LessonRecord'", new List<string>() { "School" });
            weekLess = await CommonFind.FindTotals(cosmosClient, $"select count(c.id) as totals from c where c.pk='LessonRecord' and c.startTime>={weekStart} and c.startTime <={weekEnd}", new List<string>() { "School", "Teacher" });
            termLess = await CommonFind.FindTotals(cosmosClient, $"select count(c.id) as totals from c where c.pk='LessonRecord' and c.startTime>={termStart} and c.startTime <={termEnd}", new List<string>() { "School","Teacher" });

            return Ok(new { state = 200, areaCount = areaInfos.Count, allSize, heCount, geCount, oeCount, allLess, termLess, weekLess, allActivity, termActivity, weekActivity, areaInfos });
        }
                

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

        /// <summary>
        /// 区级信息
        /// </summary>
        public record AreaInfo
        {
            public string id { get; set; }
            public string name { get; set; }
            public string standard { get; set; }
            public string standardName { get; set; }
            public int schoolCount { get; set; }
            public int techCount { get; set;}
            public int stuCount { get; set; }
        }

        /// <summary>
        /// 区级下学校信息显示
        /// </summary>
        public record RecSchool
        {
            public string id { get; set; }
            public string name { get; set; }
            public string picture { get; set; }
            public int type { get; set; }
            public int size { get; set; }
            public int scale { get; set; }
        }

        private class SchoolInfo
        {
            public string id { get; set; }
            public string name { get; set; }
            public string picture { get; set; }
            //教师人数
            public int teacherCount { get; set; }
            //学生人数
            public long studentCount { get; set; }
            //评审人数
            public int appraiseCount { get; set; }
            //参训人数
            public int trainCount { get; set; }
            public List<KeyValuePair<object, long>> census { get; set; } = new List<KeyValuePair<object, long>>();
        }


        private record SchoolLesson
        {
            public string id { get; set; }
            public string name { get; set; }
            public string picture { get; set; }
            public int count { 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>>();
        }
    }
}