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.DI;
using TEAMModelOS.SDK.Models;

namespace TEAMModelBI.Controllers.BISchool
{
    /// <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>
        [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();
            var (start, end) = DateTimeHeloer.GetTermStartOrEnd(DateTime.Now);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            object paperCount = new object();

            StringBuilder sqlTxt = new StringBuilder("select count(c.id) AS totals from c");

            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                List<string> schoolIds = new List<string>();
                List<SchoolPaper> schoolPapers = new List<SchoolPaper>();

                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("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());
                    }
                }
                if (bool.Parse($"{term}") == true)
                {
                    sqlTxt.Append($" where c.createTime >={start} and c.createTime <={end}");
                }

                foreach (var schoolId in schoolIds)
                {
                    SchoolPaper schoolPaper = new SchoolPaper() { id = schoolId };
                    School school = new();
                    try
                    {
                        school = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemAsync<School>(schoolId, new PartitionKey("Base"));
                    }
                    catch
                    {
                    }
                    schoolPaper.name = school != null ? school.name : 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.GetInt16() > 0)
                        {
                            foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                schoolPaper.total += obj.GetProperty("totals").GetInt64();
                            }
                        }
                    }

                    schoolPapers.Add(schoolPaper);
                }

                paperCount = schoolPapers;
            }
            else
            {
                long tempTotal = 0;
                sqlTxt.Append(" where c.pk='Paper' ");
                if (bool.Parse($"{term}") == true)
                {
                    sqlTxt.Append($" and c.createTime >= {start} and c.createTime <={end}");
                }
                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: sqlTxt.ToString(), requestOptions: new QueryRequestOptions() { }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetInt64() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            tempTotal += obj.GetProperty("totals").GetInt64();
                        }
                    }
                }
                paperCount = tempTotal;
            }

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

        /// <summary>
        /// 查询所有的试题数量
        /// </summary>
        /// <returns></returns>
        [HttpPost("get-total")]
        public async Task<IActionResult> GetTotal()
        {
            var cosmosClient = _azureCosmos.GetCosmosClient();
            long total = 0;
            //string sqlText = $"select c.id from c where c.pk='Paper'";
            string sqlText = $"select COUNT(c.id) AS totals from c where c.pk='Paper'";
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: sqlText, requestOptions: new QueryRequestOptions() { }))
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetInt64() > 0)
                {
                    //total += count.GetInt64();
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        total += obj.GetProperty("totals").GetInt64();
                    }
                }
            }

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

        /// <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();
            var cosmosClient = _azureCosmos.GetCosmosClient();
            List<string> schoolIds = new List<string>();
            List<SchoolPaper> schoolPapers = new List<SchoolPaper>();

            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 schoolId in schoolIds)
            {
                SchoolPaper schoolPaper = new SchoolPaper() { id = schoolId };
                School school = new();
                try
                {
                    school = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemAsync<School>(schoolId, new PartitionKey("Base"));
                }
                catch
                {
                }
                schoolPaper.name = school != null ? school.name : schoolId;

                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator($"select c.id from c", 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.GetInt64() > 0)
                    {
                        schoolPaper.total += count.GetInt64();
                    }
                }

                schoolPapers.Add(schoolPaper);
            }

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

        /// <summary>
        /// 一年的12个月的
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-yerar")]
        public async Task<IActionResult> GetNewYear(JsonElement jsonElement) 
        {
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            List<MonthStartEnd> endList1 = DateTimeHeloer.GetYearMonthlyStartEnd13(DateTimeOffset.UtcNow.Year);
            if (string.IsNullOrEmpty($"{tmdId}"))
            {
                Dictionary<string, long> schoolYears = new Dictionary<string, long>();
                foreach (var temp in endList1)
                {
                    long total = 0;
                    string sqlText = $"SELECT  COUNT(c.id) AS totals FROM c WHERE c.createTime >= {temp.start} and c.createTime <= {temp.end} and c.pk='Paper'";
                    await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: sqlText, requestOptions: new QueryRequestOptions() { }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.ContentStream);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetInt64() > 0)
                        {
                            foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                total += obj.GetProperty("totals").GetInt64();
                            }
                        }
                    }

                    await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryStreamIterator(queryText: sqlText, requestOptions: new QueryRequestOptions() { }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.ContentStream);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetInt64() > 0)
                        {
                            foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                total += obj.GetProperty("totals").GetInt64();
                            }
                        }
                    }

                    schoolYears.Add(temp.yearMonth, total);
                }

                return Ok(new { state = 200, schoolYears });
            }
            else
            {
                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<SchoolYear> schoolYears = new List<SchoolYear>();
                foreach (var schoolId in schoolIds)
                {
                    SchoolYear schoolYear = new SchoolYear() { id = schoolId };
                    School school = new();
                    try
                    {
                        school = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemAsync<School>(schoolId, new PartitionKey("Base"));
                    }
                    catch
                    {
                    }
                    schoolYear.name = school != null ? school.name : schoolId;

                    Dictionary<string, long> yearCount = new Dictionary<string, long>();
                    foreach (var temp in endList1)
                    {                        
                        long total = 0;
                        string sqlText = $"SELECT  COUNT(c.id) AS totals FROM c WHERE c.createTime >= {temp.start} and c.createTime <= {temp.end} and c.pk='Paper'";
                        await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: sqlText, 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.GetInt64() > 0)
                            {
                                foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                                {
                                    total += obj.GetProperty("totals").GetInt64();
                                }
                            }
                        }

                        await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryStreamIterator(queryText: sqlText, 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.GetInt64() > 0)
                            {
                                foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                                {
                                    total += obj.GetProperty("totals").GetInt64();
                                }
                            }
                        }

                        yearCount.Add(temp.yearMonth, total);
                    }
                    schoolYear.yearPaper = yearCount;
                    schoolYears.Add(schoolYear);
                }
                return Ok(new { state = 200, schoolYears });
            }

        }

        /// <summary>
        /// 统计当前学期的数量，传醍摩豆则统计该账户相关的学校试题统计，不传则统计当前学期的试卷
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-termpaper")]
        public async Task<IActionResult> GetTermPaper(JsonElement jsonElement) 
        {
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            var (start, end) = DateTimeHeloer.GetTermStartOrEnd(DateTime.Now);

             var total = new object();
            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                List<string> schoolIds = new List<string>();
                List<SchoolPaper> schoolPapers = new List<SchoolPaper>();

                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("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 schoolId in schoolIds)
                {
                    SchoolPaper schoolPaper = new SchoolPaper() { id = schoolId };
                    School school = new();
                    try
                    {
                        school = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemAsync<School>(schoolId, new PartitionKey("Base"));
                    }
                    catch
                    {
                    }
                    schoolPaper.name = school != null ? school.name : schoolId;

                    await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator($"select count(c.id) AS totals from c where c.createTime >={start} and c.createTime <={end}", 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.GetInt64() > 0)
                        {
                            foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                schoolPaper.total += obj.GetProperty("totals").GetInt64();
                            }
                            //schoolPaper.total += count.GetInt64();
                        }
                    }

                    schoolPapers.Add(schoolPaper);
                }

                total = schoolPapers;
            }
            else
            {
                long tempTotal = 0;
                string sqlTxt = $"SELECT COUNT(c.id) AS totals FROM c where c.pk='Paper' and c.createTime >= {start} and c.createTime <= {end}";
                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetInt64() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray()) 
                        {
                            //total += obj.GetProperty("totals").GetInt64();
                            tempTotal += obj.GetProperty("totals").GetInt64();
                        }
                    }
                }
                total = tempTotal;
            }

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

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

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

            public string name { get; set; }

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

    }
}