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;

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;
        public readonly List<string> types = new List<string> { "Exam", "Survey", "Vote", "Homework" };

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

        /// <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>
        /// 依据活动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 SchoolActivity 
        {
            public string id { get; set; }
            public string name { get; set; }

            public long total { 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>>();

        }
    }
}