TEAMModelOS/TEAMModelBI/Controllers/Census/ItemSticsController.cs

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

namespace TEAMModelBI.Controllers.Census
{
    [Route("item")]
    [ApiController]
    public class ItemSticsController : ControllerBase
    {
        private readonly AzureCosmosFactory _azureCosmos;
        private readonly AzureStorageFactory _azureStorage;
        private readonly DingDing _dingDing;
        private readonly Option _option;

        public ItemSticsController(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-totals")]
        public async Task<IActionResult> GetTotals(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            jsonElement.TryGetProperty("site", out JsonElement site);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

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

                    ItemTatols itempTatols = new ItemTatols() { id = scid, name = school.name != null ? school.name : scid };

                    string sqlTxt = $"select value(COUNT(c.id)) from c where c.code='Item-{scid}'";
                    long totals = await CommonFind.GetSqlValueCount(cosmosClient, new List<string>() { "School", "Teacher" }, sqlTxt.ToString());
                    itempTatols.totals = totals;
                    itemTatols.Add(itempTatols);
                }

                return Ok(new { state = 200, itemTatols });
            }
            else
            {
                string sqlTxt = $"select value(COUNT(c.id)) from c where c.pk='Item'";
                long totals = await CommonFind.GetSqlValueCount(cosmosClient, new List<string>() { "School", "Teacher" }, sqlTxt.ToString());
                return Ok(new { state = 200, totals });
            }
        }

        /// <summary>
        /// 试题类型统计
        /// </summary>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-type")]
        public async Task<IActionResult> GetTypeCount(JsonElement jsonElement) 
        {
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            if (!jsonElement.TryGetProperty("term", out JsonElement term)) return BadRequest();
            jsonElement.TryGetProperty("site", out JsonElement site);

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

            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            List<string> itemType = new List<string> { "single", "multiple", "judge", "complete", "subjective", "connector", "correct", "compose" };
            List<object> typeCount = new List<object>();

            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");

                foreach (var schoolId in schoolIds)
                {
                    School school = new();
                    var rsponse = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(schoolId, new PartitionKey("Base"));
                    if (rsponse.Status == 200) 
                    {
                        using var json = await JsonDocument.ParseAsync(rsponse.ContentStream);
                        school = json.ToObject<School>();
                    }

                    Census tempCensus = new Census() { id = schoolId, name = school.name != null ? school.name : schoolId };

                    foreach (var type in itemType)
                    {
                        StringBuilder sqlTxt = new($"select value(COUNT(c.id)) from c where c.code='Item-{schoolId}' and c.type='{type}'");

                        if (bool.Parse($"{term}") == true)
                        {
                            sqlTxt.Append($" and c.createTime >= {start} and c.createTime <= {end}");
                        }

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

                        tempCensus.census.Add(new KeyValuePair<object, long>(type, totals));
                    }
                    typeCount.Add(tempCensus);
                }
            }
            else
            {
                foreach (var type in itemType)
                {
                    StringBuilder sqlTxt = new($"select value(COUNT(c.id)) from c where c.pk='Item' and c.type='{type}'");
                    if (bool.Parse($"{term}") == true)
                    {
                        sqlTxt.Append($" and c.createTime >= {start} and c.createTime <= {end}");
                    }

                    long totals = await CommonFind.GetSqlValueCount(cosmosClient, new List<string>() { "School", "Teacher" }, sqlTxt.ToString());
                    typeCount.Add(new KeyValuePair<string, object>(type, totals));
                }
            }
            return Ok(new { state = 200, typeCount });
        }

        /// <summary>
        ///  依据难度统计
        /// </summary>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-level")]
        public async Task<IActionResult> GetLevelCount(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            if(!jsonElement.TryGetProperty("term", out JsonElement term)) return BadRequest();
            jsonElement.TryGetProperty("site", out JsonElement site);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            var (start, end) = TimeHelper.GetTermStartOrEnd(DateTime.Now);

            List<object> levelCount = new List<object>();

            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                List<string> schoolIds = new List<string>();
                schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");

                foreach (var schoolId in schoolIds)
                {
                    School school = new();

                    var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(schoolId, new PartitionKey("Base"));
                    if (response.Status == 200) 
                    {
                        using var json = await JsonDocument.ParseAsync(response.ContentStream);
                        school = json.ToObject<School>();
                    }

                    Census tempCensus = new Census() { id = schoolId, name = school.name != null ? school.name : schoolId };

                    for (int i = 1; i < 6; i++)
                    {
                        StringBuilder sqlTxt = new($"SELECT value(COUNT(c.id)) FROM c WHERE c.code='Item-{schoolId}' and c.level={i}");
                        if (bool.Parse($"{term}") == true)
                        {
                            sqlTxt.Append($" and c.createTime >= {start} and c.createTime <= {end}");
                        }

                        long totals = await CommonFind.GetSqlValueCount(cosmosClient, new List<string>() { "School", "Teacher" }, sqlTxt.ToString());
                        tempCensus.census.Add(new KeyValuePair<object, long>(i, totals));
                    }
                    levelCount.Add(tempCensus);
                }
            }
            else
            {
                for (int i = 1; i < 6; i++)
                {
                    StringBuilder sqlTxt = new($"select value(COUNT(c.id)) from c where c.pk='Item' and c.level={i}");
                    if (bool.Parse($"{term}") == true)
                    {
                        sqlTxt.Append($" and c.createTime >= {start} and c.createTime <= {end}");
                    }

                    long total = await CommonFind.GetSqlValueCount(cosmosClient, new List<string>() { "School", "Teacher" }, sqlTxt.ToString());
                    levelCount.Add(new KeyValuePair<int, long>(i, total));
                }
            }

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

        /// <summary>
        /// 依据层次查询
        /// </summary>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-layer")]
        public async Task<IActionResult> GetLayerCount(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("site", out JsonElement site);
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            if (!jsonElement.TryGetProperty("term", out JsonElement term)) return BadRequest();

            var (start, end) = TimeHelper.GetTermStartOrEnd(DateTime.Now);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            List<object> layerCount = new List<object>();

            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                List<string> schoolIds = new List<string>();
                schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");

                foreach (var schoolId in schoolIds)
                {
                    School school = new();
                    var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(schoolId, new PartitionKey("Base"));
                    if (response.Status == 200) 
                    {
                        using var json = await JsonDocument.ParseAsync(response.ContentStream);
                        school = json.ToObject<School>();
                    }

                    Census tempCensus = new Census() { id = schoolId, name = school.name != null ? school.name : schoolId };

                    for (int i = 1; i <= 6; i++)
                    {
                        StringBuilder sqlTxt = new($"select value(COUNT(c.id)) from c where c.code='Item-{schoolId}' and c.field={i}");
                        if (bool.Parse($"{term}") == true)
                        {
                            sqlTxt.Append($" and c.createTime >= {start} and c.createTime <= {end}");
                        }

                        long totals = await CommonFind.GetSqlValueCount(cosmosClient, new List<string>() { "School", "Teacher" }, sqlTxt.ToString());
                        tempCensus.census.Add(new KeyValuePair<object, long>(i, totals));
                    }

                    layerCount.Add(tempCensus);
                }
            }
            else 
            {
                for (int i = 1; i <= 6; i++)
                {
                    StringBuilder sqlTxt = new StringBuilder($"select value(COUNT(c.id)) from c where c.pk='Item' and c.field={i}");
                    if (bool.Parse($"{term}") == true)
                    {
                        sqlTxt.Append($" and c.createTime >= {start} and c.createTime <= {end}");
                    }

                    long totals = await CommonFind.GetSqlValueCount(cosmosClient, new List<string>() { "School", "Teacher" }, sqlTxt.ToString());
                    layerCount.Add(new KeyValuePair<object, long>(i, totals));
                }
            }

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

        /// <summary>
        /// 依据试题id查询试题信息  数据管理工具——查询工具
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-info")]
        public async Task<IActionResult> GetInfo(JsonElement jsonElement) 
        {
            if (!jsonElement.TryGetProperty("itemId", out JsonElement itemId)) return BadRequest();
            jsonElement.TryGetProperty("isPersonal", out JsonElement isPersonal);
            jsonElement.TryGetProperty("site", out JsonElement site);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

            List<object> objItems = new List<object>();
            string sqlTxt = $"select * from c where c.id='{itemId}' and c.pk='Item'";

            if (!string.IsNullOrEmpty($"{isPersonal}"))
            {
                string tableName = "School";
                if (bool.Parse($"{isPersonal}") == true)
                {
                    tableName = "Teacher";
                }

                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", tableName).GetItemQueryStreamIterator(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        objItems.Add(obj.ToObject<object>());
                    }
                }
            }
            else 
            {
                List<string> tableName = new List<string> { "School", "Teacher" };
                foreach (string key in tableName) 
                {
                    await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", key).GetItemQueryStreamIterator(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.ContentStream);
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            objItems.Add(obj.ToObject<object>());
                        }
                    }
                }
            }

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

        public record ItemTatols
        {
            /// <summary>
            /// 学校ID
            /// </summary>
            public string id { get; set; }
            /// <summary>
            /// 学校名称
            /// </summary>
            public string name { get; set; }

            /// <summary>
            /// 总数
            /// </summary>
            public long totals { get; set; }

        }

        /// <summary>
        /// 统计实体
        /// </summary>
        public record Census
        {
            /// <summary>
            /// 学校ID
            /// </summary>
            public string id { get; set; }
            /// <summary>
            /// 学校名称
            /// </summary>
            public string name { get; set; }

            /// <summary>
            /// 统计信息
            /// </summary>
            public List<KeyValuePair<object, long>> census { get; set; } = new List<KeyValuePair<object, long>>();
        }
    }
}