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 Microsoft.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;
using Azure.Core;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Context.Constant;
using TEAMModelOS.SDK.Models.Service.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-list")]
        public async Task<IActionResult> GetList(JsonElement jsonElement) 
        {
            if (!jsonElement.TryGetProperty("code", out JsonElement code)) return BadRequest();
            jsonElement.TryGetProperty("scope", out JsonElement scope);
            Dictionary<string, object> dic = new();
            if (jsonElement.TryGetProperty("periodId", out JsonElement periodId))
                dic.Add("periodId", periodId);
            if (jsonElement.TryGetProperty("subjectId", out JsonElement subjectId))
                dic.Add("subjectId", subjectId);
            if (jsonElement.TryGetProperty("gradeIds", out JsonElement gradeIds))
                dic.Add("gradeIds", gradeIds);
            if (jsonElement.TryGetProperty("type", out JsonElement type))
                dic.Add("type", type);
            if (jsonElement.TryGetProperty("level", out JsonElement level))
                dic.Add("level", level);
            if (jsonElement.TryGetProperty("field", out JsonElement field))
                dic.Add("field", field);
            if (jsonElement.TryGetProperty("isSort", out JsonElement isSort))
            {
                if (!string.IsNullOrEmpty($"{isSort}"))
                {
                    if (bool.Parse($"{isSort}") == true)
                        dic.Add("@DESC", "createTime");
                    else
                        dic.Add("@ASC", "createTime");
                }
                else
                    dic.Add("@ASC", "createTime");
            }
            if (jsonElement.TryGetProperty("pid", out JsonElement pd))
            {
                if (pd.ValueKind != JsonValueKind.Null)
                    dic.Add("pid", pd.ToString());
                else
                    dic.Add("pid", null);
            }

            var cosmosClinet = _azureCosmos.GetCosmosClient();
            StringBuilder sql = new("select c.id,c.code,c.repairResource, c.periodId,c.question,c.useCount,c.level,c.field,c.knowledge,c.type,c.option,c.createTime,c.answer,c.explain,c.children,c.score,c.gradeIds,c.subjectId,c.blob,c.scope from c ");
            AzureCosmosQuery cosmosDbQuery = SQLHelper.GetSQL(dic, sql);
            List<object> items = new();
            if (scope.ToString().Equals("private"))
            {
                await foreach (var item in cosmosClinet.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIteratorQuery(queryDefinition: cosmosDbQuery.CosmosQueryDefinition, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Item-{code}") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.Content);

                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            items.Add(obj.ToObject<object>());
                        }
                    }
                }
            }
            else if (scope.ToString().Equals("school"))
            {
                await foreach (var item in cosmosClinet.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIteratorQuery(queryDefinition: cosmosDbQuery.CosmosQueryDefinition, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Item-{code}") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.Content);

                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            items.Add(obj.ToObject<object>());
                        }
                    }
                }
            }
            else
            {
                await foreach (var item in cosmosClinet.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIteratorQuery(queryDefinition: cosmosDbQuery.CosmosQueryDefinition, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Item-{code}") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.Content);

                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            items.Add(obj.ToObject<object>());
                        }
                    }
                }

                await foreach (var item in cosmosClinet.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIteratorQuery(queryDefinition: cosmosDbQuery.CosmosQueryDefinition, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Item-{code}") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.Content);

                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            items.Add(obj.ToObject<object>());
                        }
                    }
                }
            }

            return Ok(new { state = RespondCode.Ok ,cnt = items.Count, items });
        }


        /// <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.StatusCode == System.Net.HttpStatusCode.OK)
                    {
                        using var json = await JsonDocument.ParseAsync(rsponse.Content);
                        school = json.ToObject<School>();
                    }

                    ItemTatols itempTatols = new() { 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() { "single", "multiple", "judge", "complete", "subjective", "connector", "correct", "compose" };
            List<object> typeCount = new();

            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.StatusCode == System.Net.HttpStatusCode.OK) 
                    {
                        using var json = await JsonDocument.ParseAsync(rsponse.Content);
                        school = json.ToObject<School>();
                    }

                    Census tempCensus = new() { 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();

            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                List<string> schoolIds = new();
                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.StatusCode == System.Net.HttpStatusCode.OK) 
                    {
                        using var json = await JsonDocument.ParseAsync(response.Content);
                        school = json.ToObject<School>();
                    }

                    Census tempCensus = new() { 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();

            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                List<string> schoolIds = new();
                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.StatusCode == System.Net.HttpStatusCode.OK) 
                    {
                        using var json = await JsonDocument.ParseAsync(response.Content);
                        school = json.ToObject<School>();
                    }

                    Census tempCensus = new() { 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($"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();
            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).GetItemQueryStreamIteratorSql(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { }))
                {
                    using var json = await JsonDocument.ParseAsync(item.Content);
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        objItems.Add(obj.ToObject<object>());
                    }
                }
            }
            else 
            {
                List<string> tableName = new() { "School", "Teacher" };
                foreach (string key in tableName) 
                {
                    await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", key).GetItemQueryStreamIteratorSql(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.Content);
                        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>>();
        }
    }
}