TEAMModelOS/TEAMModelBI/Controllers/Census/PaperController.cs

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

namespace TEAMModelBI.Controllers.Census
{
    /// <summary>
    /// 试卷
    /// </summary>
    [Route("paper")]
    [ApiController]
    public class PaperController : ControllerBase
    {
        private readonly AzureCosmosFactory _azureCosmos;
        private readonly AzureStorageFactory _azureStorage;
        private readonly DingDing _dingDing;
        private readonly Option _option;

        public PaperController(AzureCosmosFactory azureCosmos, AzureStorageFactory azureStorage, DingDing dingDing, IOptionsSnapshot<Option> option)
        {
            _azureCosmos = azureCosmos;
            _azureStorage = azureStorage;
            _dingDing = dingDing;
            _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("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");
            }

            var cosmosClinet = _azureCosmos.GetCosmosClient();
            StringBuilder sql = new("select value(c) from c ");
            AzureCosmosQuery cosmosDbQuery = SQLHelper.GetSQL(dic, sql);

            List<Paper> papers = new();
            if (scope.ToString().Equals("school"))
            {
                await foreach (var item in cosmosClinet.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<Paper>(queryDefinition: cosmosDbQuery.CosmosQueryDefinition, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Paper-{code}") }))
                {
                    papers.Add(item);
                }
            }
            else if (scope.ToString().Equals("private"))
            {
                await foreach (var item in cosmosClinet.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryIterator<Paper>(queryDefinition: cosmosDbQuery.CosmosQueryDefinition, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Paper-{code}") }))
                {
                    papers.Add(item);
                }
            }
            else
            {
                await foreach (var item in cosmosClinet.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<Paper>(queryDefinition: cosmosDbQuery.CosmosQueryDefinition, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Paper-{code}") }))
                {
                    papers.Add(item);
                }
                await foreach (var item in cosmosClinet.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryIterator<Paper>(queryDefinition: cosmosDbQuery.CosmosQueryDefinition, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Paper-{code}") }))
                {
                    papers.Add(item);
                }
            }

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

        /// <summary>
        /// 复制试卷信息到另外一个学校
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("copy-infos")]
        public async Task<IActionResult> CopyInfos(JsonElement jsonElement) 
        {




            return Ok(new { state = RespondCode.Ok});
        }


        /// <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();
            //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);
            object paperCount = new object();            

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

                List<SchoolPaper> schoolPapers = new();
                string time = "";
                bool bos = bool.Parse($"{term}");
                if (bos == true)
                {
                    time = $" where c.createTime >={start} and c.createTime <={end}";
                }

                foreach (var schoolId in schoolIds)
                {
                    StringBuilder sqlTxt = new("select value(count(c.id)) from c");
                    if (bos != true)
                        sqlTxt.Append($" where c.code='Paper-{schoolId}'");
                    else
                    {
                        sqlTxt.Append(time);
                        sqlTxt.Append($" and c.code='Paper-{schoolId}'");
                    };
                    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>();
                    }

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

                    schoolPaper.totals = await CommonFind.GetSqlValueCount(cosmosClient, "School", sqlTxt.ToString());

                    schoolPapers.Add(schoolPaper);
                }

                paperCount = schoolPapers;
            }
            else
            {
                StringBuilder sqlTxt = new ("select value(count(c.id)) from c where c.pk='Paper'");
                if (bool.Parse($"{term}") == true)
                {
                    sqlTxt.Append($" and c.createTime >= {start} and c.createTime <={end}");
                }

                paperCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", sqlTxt.ToString());
            }

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

        /// <summary>
        /// 查询所有的试题数量
        /// </summary>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-total")]
        public async Task<IActionResult> GetTotal(JsonElement jsonElement)
        {
            //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释
            var cosmosClient = _azureCosmos.GetCosmosClient();
            ////分开部署，就不需要，一站多用时，取消注释
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            string sqlTxt = $"select value(COUNT(c.id)) from c where c.pk='Paper'";

            long totals = await CommonFind.GetSqlValueCount(cosmosClient, "School", sqlTxt.ToString());
            return Ok(new { state = 200, totals });
        }

        /// <summary>
        /// 依据醍摩豆账户统计相关联的学校试卷数量
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-assistschool")]
        public async Task<IActionResult> GetAssistSchool(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return BadRequest();
            //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释
            var cosmosClient = _azureCosmos.GetCosmosClient();
            ////分开部署，就不需要，一站多用时，取消注释
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            List<string> schoolIds = new();
            List<SchoolPaper> schoolPapers = 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.Status == 200) 
                {
                    using var json = await JsonDocument.ParseAsync(response.ContentStream);
                    school = json.ToObject<School>();
                }

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

                schoolPaper.totals = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"select value(COUNT(c.id)) from c where c.code='Paper-{schoolId}'");

                schoolPapers.Add(schoolPaper);
            }

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

        /// <summary>
        /// 一年的12个月的
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-yerar")]
        public async Task<IActionResult> GetNewYear(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);
            List<MonthStartEnd> endList1 = TimeHelper.GetYearMonthlyStartEnd(DateTimeOffset.UtcNow.Year);
            if (string.IsNullOrEmpty($"{tmdId}"))
            {
                Dictionary<string, long> schoolYears = new Dictionary<string, long>();
                foreach (var temp in endList1)
                {
                    string sqlText = $"SELECT value(COUNT(c.id)) FROM c WHERE c.createTime >= {temp.start} and c.createTime <= {temp.end} and c.pk='Paper'";

                    long total = await CommonFind.GetSqlValueCount(cosmosClient, new List<string>() { "School", "Teacher" }, sqlText);
                    schoolYears.Add(temp.yearMonth, total);
                }

                return Ok(new { state = 200, schoolYears });
            }
            else
            {
                List<string> schoolIds = new();
                schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");

                List<SchoolYear> schoolYears = new();
                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>();
                    }

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

                    Dictionary<string, long> yearCount = new();
                    foreach (var temp in endList1)
                    {                        
                        string sqlTxt = $"SELECT value(COUNT(c.id)) FROM c WHERE c.code='Paper-{schoolId}' and c.createTime >= {temp.start} and c.createTime <= {temp.end} and c.pk='Paper'";
                        long totals = await CommonFind.GetSqlValueCount(cosmosClient, new List<string>() { "School", "Teacher" }, sqlTxt);
                        yearCount.Add(temp.yearMonth, totals);
                    }
                    schoolYear.yearPaper = yearCount;
                    schoolYears.Add(schoolYear);
                }
                return Ok(new { state = 200, schoolYears });
            }
        }

        /// <summary>
        /// 统计当前学期的数量，传醍摩豆则统计该账户相关的学校试题统计，不传则统计当前学期的试卷
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-termpaper")]
        public async Task<IActionResult> GetTermPaper(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);
            var (start, end) = TimeHelper.GetTermStartOrEnd(DateTime.Now);

             var totals = new object();
            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                List<SchoolPaper> schoolPapers = 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>();
                    }

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

                    string sqlTxt = $"select value(count(c.id)) from c where c.createTime >={start} and c.createTime <={end}";

                    schoolPaper.totals += await CommonFind.GetSqlValueCount(cosmosClient, "School", sqlTxt, $"Paper-{schoolId}");

                    //await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText:sqlTxt.ToString(),requestOptions:new QueryRequestOptions() { PartitionKey = new PartitionKey($"Paper-{schoolId}")}))
                    //{
                    //    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    //    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetInt32() > 0)
                    //    {
                    //        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    //        {
                    //            schoolPaper.totals += obj.GetProperty("totals").GetInt64();
                    //        }
                    //    }
                    //}

                    //schoolPaper.totals = await CommonFind.FindTotals(cosmosClient, sqlTxt, new List<string>() { "School" });
                    schoolPapers.Add(schoolPaper);
                }

                totals = schoolPapers;
            }
            else
            {
                string sqlTxt = $"SELECT value(count(c.id)) FROM c where c.pk='Paper' and c.createTime >= {start} and c.createTime <= {end}";
                totals = await CommonFind.GetSqlValueCount(cosmosClient, new List<string>() { "School" }, sqlTxt);
            }

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

        /// <summary>
        /// 依据试卷Id查询试卷信息 数据管理工具——查询工具
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-info")]
        public async Task<IActionResult> GetInfo(JsonElement jsonElement) 
        {
            if (!jsonElement.TryGetProperty("paperId", out JsonElement paperId)) 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> paperInfos = new();
            string sqlTxt = $"select * from c where c.id='{paperId}' and c.pk='Paper'";

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

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


        public record SchoolPaper 
        {
            public string id { get; set; }
            public string name { get; set; }
            public long totals { get; set; }
        }

        public record SchoolYear 
        {
            public string id { get; set; }

            public string name { get; set; }

            public Dictionary<string, long> yearPaper { get; set; }
        }

    }
}