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;

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);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            List<KeyValuePair<string, object>> typeCount = new List<KeyValuePair<string, object>>();

            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                List<string> schoolIds = new List<string>();
                string schoolSql = $"SELECT DISTINCT REPLACE(c.code,'Teacher-','') AS schoolId,c.code,c.roles,c.id,c.name From c where ARRAY_CONTAINS(c.roles,'assist',true) AND c.pk = 'Teacher' AND c.status = 'join' AND c.id='{tmdId}'";

                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: schoolSql, requestOptions: new QueryRequestOptions() { }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        schoolIds.Add(obj.GetProperty("schoolId").GetString());
                    }
                }

                foreach (var type in types)
                {
                    long acount = 0;
                    List<SchoolActivity> schoolActivities = new List<SchoolActivity>();
                    foreach (var itemId in schoolIds)
                    {
                        School school = new();
                        try
                        {
                            school = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemAsync<School>(itemId, new PartitionKey("Base"));
                        }
                        catch
                        {
                        }

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

                        string activitySql = $"SELECT DISTINCT c.id,c.code,c.name FROM c WHERE c.pk='{type}' AND c.school='{itemId}'";
                        await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryStreamIterator(queryText: activitySql, requestOptions: new QueryRequestOptions() { }))
                        {
                            using var json = await JsonDocument.ParseAsync(item.ContentStream);
                            if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                            {
                                acount += count.GetInt32();
                                schoolActivity.total += count.GetInt64();
                                //foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                                //{
                                //    acount += 1;
                                //}
                            }
                        }
                        schoolActivities.Add(schoolActivity);
                    }

                    KeyValuePair<string, object> valuePair = new KeyValuePair<string, object>(type, schoolActivities);
                    typeCount.Add(valuePair);
                }
            }
            else 
            {
                foreach (var type in types)
                {
                    int acount = 0;
                    string querySql = $"SELECT distinct c.id,c.code,c.name,c.pk FROM c where c.pk='{type}'  ";
                    await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryStreamIterator(queryText: querySql, requestOptions: new QueryRequestOptions() { }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.ContentStream);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetInt32() > 0)
                        {
                            acount += count.GetInt32();
                            //foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            //{
                            //    acount += 1;
                            //}
                        }
                    }

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

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

        /// <summary>
        /// 统计所有的评量活动，问卷调查，投票活动，作业
        /// </summary>
        /// <returns></returns>
        [HttpPost("get-allactivity")]
        public async Task<IActionResult> GetAllActivity() 
        {
            try
            {
                var cosmosClient = _azureCosmos.GetCosmosClient();
                List<KeyValuePair<string, int>> typeCount = new List<KeyValuePair<string, int>>();
                foreach (var type in types)
                {
                    int acount = 0;
                    string querySql = $"SELECT distinct c.id,c.code,c.name,c.pk FROM c where c.pk='{type}'  ";
                    await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryStreamIterator(queryText: querySql, requestOptions: new QueryRequestOptions() { }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.ContentStream);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetInt32() > 0) 
                        {
                            acount += count.GetInt32();
                            //foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            //{
                            //    acount += 1;
                            //}
                        }
                    }

                    KeyValuePair<string, int> valuePair = new KeyValuePair<string, int>(type, acount);
                    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 = new List<string>();

            string schoolSql = $"SELECT DISTINCT REPLACE(c.code,'Teacher-','') AS schoolId,c.code,c.roles,c.id,c.name From c where ARRAY_CONTAINS(c.roles,'assist',true) AND c.pk = 'Teacher' AND c.status = 'join' AND c.id='{tmdId}'";

            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText:schoolSql,requestOptions:new QueryRequestOptions() { }))
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray()) 
                {
                    schoolIds.Add(obj.GetProperty("schoolId").GetString());
                }
            }
            List<KeyValuePair<string, object>> typeCount = new List<KeyValuePair<string, object>>();
            foreach (var type in types)
            {
                long acount = 0;
                List<SchoolActivity> schoolActivities = new List<SchoolActivity>();
                foreach (var itemId in schoolIds)
                {
                    School school = new();
                    try
                    {
                        school = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemAsync<School>(itemId, new PartitionKey("Base"));
                    }
                    catch 
                    {
                    }

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

                    string activitySql = $"SELECT DISTINCT c.id,c.code,c.name FROM c WHERE c.pk='{type}' AND c.school='{itemId}'";
                    await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryStreamIterator(queryText: activitySql, requestOptions: new QueryRequestOptions() { }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.ContentStream);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        {
                            acount += count.GetInt32();
                            schoolActivity.total += count.GetInt64();
                            //foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            //{
                            //    acount += 1;
                            //}
                        }
                    }
                    schoolActivities.Add(schoolActivity);
                }

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

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

        public record SchoolActivity 
        {
            public string id { get; set; }
            public string name { get; set; }

            public long total { get; set; }
        }
    }
}