TEAMModelOS/TEAMModelBI/Controllers/Census/PaperController.cs

using Azure.Cosmos;
using Azure.Storage.Blobs;
using Azure.Storage.Blobs.Models;
using Azure.Storage.Sas;
using DocumentFormat.OpenXml.Drawing.Diagrams;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
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;
using TEAMModelOS.SDK.Models.Cosmos.BI;

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");
            }
            string scope = "school";
            if (string.IsNullOrEmpty($"{_scope}"))
                scope = $"{_scope}";
            
            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);
                }
            }
            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) 
        {
            if (!jsonElement.TryGetProperty("papers", out JsonElement jsPapers)) return BadRequest();

            List<CopyPaper> papers = jsPapers.ToObject<List<CopyPaper>>();

            var cosmosClinet = _azureCosmos.GetCosmosClient();
            foreach (var cItem in papers)
            {
                Paper paper = await cosmosClinet.GetContainer(Constant.TEAMModelOS, "School").ReadItemAsync<Paper>(cItem.oldId, new PartitionKey($"Paper-{cItem.oldSc}"));
                paper.periodId = cItem.newPrdId;
                paper.subjectId = cItem.newSubId;
                paper.subjectName = cItem.newSubName;
                paper.gradeIds = cItem.newGrId;
                paper.code = $"Paper-{cItem.newSc}";

                var resState = await cosmosClinet.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(paper.id, new PartitionKey(paper.code));
                if (resState.Status == 200) 
                {
                    paper.id = Guid.NewGuid().ToString();
                }

                //paper = await cosmosClinet.GetContainer(Constant.TEAMModelOS, "School").CreateItemAsync<Paper>(paper, new PartitionKey(paper.code));


                var oldBobCilent = _azureStorage.GetBlobContainerClient(containerName: $"{cItem.oldSc}");
                var newBobCilent = _azureStorage.GetBlobContainerClient(containerName: $"{cItem.newSc}");

                string blobUrl = "";
                if (paper.blob.IndexOf('/') == 0)
                    blobUrl = $"{paper.blob}".Substring(1, paper.blob.Length - 1);
                else
                    blobUrl = $"{paper.blob}";

                List<Task<CopyFromUriOperation>> filelist = new();   //可复制256M以上文件
                await foreach (BlobItem blobItem in oldBobCilent.GetBlobsAsync(BlobTraits.None, BlobStates.None, blobUrl))
                {
                    if (blobItem.Name.EndsWith(".json"))
                    {
                        BlobClient tempClient = oldBobCilent.GetBlobClient(blobItem.Name);
                        using (var meomoryStream = new MemoryStream())
                        {
                            StringBuilder itemStr = new();
                            BlobClient tempBlobClient = oldBobCilent.GetBlobClient(blobItem.Name);
                            BlobDownloadInfo download = tempBlobClient.Download();
                            var content = download.Content;
                            string text;
                            using (var streamReader = new StreamReader(content))
                            {
                                while ((text = streamReader.ReadLine()) != null)
                                {
                                    if (streamReader.EndOfStream)
                                        itemStr.Append($"{text.ToString()}");
                                    else
                                        itemStr.Append($"{text.ToString()},");
                                }
                                streamReader.Close();
                            }
                            string input = itemStr.ToString();
                            BlobItemInfo blobbItemInfo = input.ToObject<BlobItemInfo>();

                            //var response = oldBobCilent.GetBlobClient($"{blobItem.Name}").DownloadTo(meomoryStream);
                            //BlobItemInfo blobbItemInfo = Encoding.UTF8.GetString(meomoryStream.ToArray()).ToString().ToObject<BlobItemInfo>();
                            blobbItemInfo.exercise.periodId = cItem.newPrdId;
                            blobbItemInfo.exercise.gradeIds = cItem.newGrId;

                            blobbItemInfo.exercise.subjectId = cItem.newSubId;

                            var urlSas = _azureStorage.GetBlobSAS($"{cItem.newSc}", blobItem.Name, BlobSasPermissions.Read | BlobSasPermissions.List);   //获取容器sas和有效期

                            var ster = newBobCilent.UploadFileByContainer(blobbItemInfo.ToJsonString(), blobItem.Name, blobbItemInfo.id);
                        }
                    }
                }


            }
            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; }
        }


        public record CopyPaper 
        {
            public string oldId { get; set; }
            public string oldSc { get; set; }
            public string newSc { get; set; }
            public string newPrdId { get; set; }
            public string newSubId { get; set; }
            public string newSubName { get; set; }
            public List<string> newGrId { get; set; }

        }

    }
}