TEAMModelOS/TEAMModelBI/Controllers/Census/LessonSticsController.cs

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using TEAMModelOS.Models;
using TEAMModelOS.SDK.DI;
using Microsoft.Extensions.Options;
using Azure.Cosmos;
using System.Text.Json;
using TEAMModelOS.SDK.Models.Cosmos.Common;
using TEAMModelOS.SDK.Models;
using TEAMModelBI.Models;
using TEAMModelOS.SDK.Extension;
using System.Text;
using TEAMModelBI.Tool;
using MathNet.Numerics.LinearAlgebra.Double;

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

        public LessonSticsController(AzureCosmosFactory azureCosmos, AzureStorageFactory azureFactory, DingDing dingDing, IOptionsSnapshot<Option> option) 
        {
            _azureCosmos = azureCosmos;
            _azureStorage = azureFactory;
            _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 cosmosClient = _azureCosmos.GetCosmosClient();
            var (start, end) = TimeHelper.GetTermStartOrEnd(DateTime.Now);
            object totals = new object();
            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                List<SchoolLen> schoolLens = new List<SchoolLen>();

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

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

                    SchoolLen schoolLen = new SchoolLen() { id = itemId, name = school.name != null ? school.name : itemId };

                    //string sqlTxt = $"SELECT COUNT(c.id) AS totals FROM c WHERE c.code='LessonRecord-{itemId}'";
                    StringBuilder sqlTxt = new StringBuilder($"SELECT COUNT(c.id) AS totals FROM c WHERE c.code='LessonRecord-{itemId}'");
                    if (bool.Parse($"{term}") == true) 
                    {
                        sqlTxt.Append($" and c.startTime >= {start} and c.startTime <= {end}");
                    }

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

                    schoolLens.Add(schoolLen);
                }
                totals = schoolLens;
            }
            else 
            {
                StringBuilder sqlTxt = new StringBuilder($"select COUNT(c.id) AS totals  from c where c.pk='LessonRecord'");
                if (bool.Parse($"{term}") == true)
                {
                    sqlTxt.Append($" and c.startTime >= {start} and c.startTime <= {end}");
                }

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

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

        /// <summary>
        /// 统计所有课例数量
        /// </summary>
        /// <returns></returns>
        [HttpPost("get-total")]
        public async Task<IActionResult> GetCount()
        {
            try
            {
                var cosmosClient = _azureCosmos.GetCosmosClient();
                string lessonSql = $"select COUNT(c.id) AS totals from c where c.pk='LessonRecord'";
                long total = await CommonFind.FindTotals(cosmosClient, lessonSql, new List<string>() { "School" });

                return Ok(new { state = 200, total });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI，{_option.Location}  /lesson/get-total  {ex.Message}{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 管家所关联的课例数据
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-assiist")]
        public async Task<IActionResult> GetAssiist(JsonElement jsonElement) 
        {
            try
            {
                if (!jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return BadRequest();
                var cosmosClient = _azureCosmos.GetCosmosClient();
                List<SchoolLen> schoolLens = new List<SchoolLen>();

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

                    SchoolLen schoolLen = new SchoolLen() { id = itemId, name = school != null ? school.name : itemId };
                    string sqlTxt = $"SELECT COUNT(c.id) AS totals FROM c WHERE c.code='LessonRecord-{itemId}'";
                    schoolLen.totals = await CommonFind.FindTotals(cosmosClient, sqlTxt, new List<string>() { "School" });

                    schoolLens.Add(schoolLen);
                }

                return Ok(new { state = 200, schoolLens });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI， {_option.Location}  /lesson/get-assiist  {ex.Message}{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 统计所有的课例数据
        /// </summary>
        /// <returns></returns>
        [HttpPost("get-diccount")]
        public async Task<IActionResult> GetDicCount(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            var cosmosClient = _azureCosmos.GetCosmosClient();

            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                jsonElement.TryGetProperty("years", out JsonElement _years);
                int years = DateTime.UtcNow.Year;
                if (!string.IsNullOrEmpty($"{_years}")) 
                {
                    years = _years.GetInt32();
                }

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

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

                    List<List<double>> begin = new();
                    await foreach (var lcount in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<LessonCount>(queryText: "select value(c) from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"LessonCount-{schoolId}-{years}") }))
                    {
                        begin.Add(lcount.beginCount);
                    }
                    schoolLen.totals = (long)DenseMatrix.OfColumns(begin).ColumnSums().Sum();
                    schoolLens.Add(schoolLen);
                }

                return Ok(new { state = 200, schoolLens });
            }
            else
            {
                List<List<double>> begin = new();
                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<LessonCount>(queryText: "select value(c) from c where c.pk='LessonCount'", requestOptions: new QueryRequestOptions() { }))
                {
                    begin.Add(item.beginCount);
                }

                var count = DenseMatrix.OfColumns(begin).ColumnSums().Sum();

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

        /// <summary>
        /// 顾问关联的学校统计本学期的课例
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-termcount")]
        public async Task<IActionResult> GetTermCount(JsonElement jsonElement) 
        {
            if(jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) BadRequest();
            var cosmosClient = _azureCosmos.GetCosmosClient();
            List<SchoolLen> schoolLens = new List<SchoolLen>();
            List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");
            foreach (var scid in schoolIds)
            {
                School school = new();
                var response = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(scid, new PartitionKey("Base"));
                if (response.Status == 200)
                {
                    using var json = await JsonDocument.ParseAsync(response.ContentStream);
                    school = json.ToObject<School>();
                }

                SchoolLen schoolLen = new SchoolLen() { id = scid, name = !string.IsNullOrEmpty(school.name) ? school.name : scid };

                DateTimeOffset dateTime = DateTimeOffset.UtcNow;
                int year = (dateTime.Month <= 8 && dateTime.Month >= 3) ? dateTime.Year : dateTime.Year - 1;

                long stime = DateTimeOffset.Parse($"{year}-9-1").ToUnixTimeMilliseconds();
                //long etime = DateTimeOffset.Parse($"{year}-2-{((year % 4 == 0 && year % 100 != 0 || year % 400 == 0) ? 29 : 28)}").ToUnixTimeMilliseconds();

                double totals = 0;
                var syear = DateTimeOffset.FromUnixTimeMilliseconds(stime).Year;
                var tyear = DateTimeOffset.UtcNow.Year;
                var tday = DateTimeOffset.UtcNow.DayOfYear;
                //今年多少天
                int tdays = (tyear % 4 == 0 && tyear % 100 != 0 || tyear % 400 == 0) ? 366 : 365;
                //去年多少天
                int pydays = (syear % 4 == 0 && syear % 100 != 0 || syear % 400 == 0) ? 366 : 365;
                List<LessonCount> scount = new();
                List<LessonCount> tcount = new();
                DenseMatrix dense = null;
                var queryClass = $"select value(c) from c ";
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<LessonCount>(
                            queryText: queryClass,
                            requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"LessonCount-{scid}-{syear}") }))
                {
                    scount.Add(item);
                }
                if (tyear > syear)
                {
                    await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<LessonCount>(queryText: queryClass,  requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"LessonCount-{scid}-{tyear}") }))
                    {
                        tcount.Add(item);
                    }
                    if (tcount.Count > 0)
                    {
                        List<List<double>> be = new();
                        foreach (var item in tcount)
                        {
                            be.Add(item.beginCount);
                        }
                        dense = DenseMatrix.OfColumns(be);
                    }
                }
                if (scount.Count > 0)
                {
                    List<List<double>> begin = new List<List<double>>();
                    foreach (LessonCount lesson in scount)
                    {
                        begin.Add(lesson.beginCount);
                    }
                    var matrix = DenseMatrix.OfColumns(begin);

                    //求本学期
                    var sdays = DateTimeOffset.FromUnixTimeMilliseconds(stime).DayOfYear;
                    if (tday - sdays < 0)
                    {
                        //var tmatrix = DenseMatrix.OfColumns(scount.beginCount);
                        //跨年后开始到本学期结束
                        double endMonth = 0;
                        if (null != dense)
                        {
                            endMonth = dense.SubMatrix(0, tday, 0, dense.ColumnCount).ColumnSums().Sum();
                        }
                        var startMonth = matrix.SubMatrix(sdays - 1, pydays - sdays, 0, matrix.ColumnCount).ColumnSums().Sum();
                        totals = (endMonth + startMonth);
                    }
                    else
                    {
                        var allMonth = matrix.SubMatrix(sdays - 1, tday - sdays + 1, 0, matrix.ColumnCount).ColumnSums().Sum();
                        totals = allMonth;
                    }
                }
                schoolLen.totals = (long)totals;
                schoolLens.Add(schoolLen);
            }

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

        /// <summary>
        /// 依据课例Id获取课例详情   数据管理工具——查询工具
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-info")]
        public async Task<IActionResult> GetInfo(JsonElement jsonElement) 
        {
            if (!jsonElement.TryGetProperty("lessonId", out JsonElement lessonId)) return BadRequest();
            var cosmosClient = _azureCosmos.GetCosmosClient();
            List<object> lessons = new List<object>();
            string sqlTxt = $"select * from c where c.id='{lessonId}'";
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { })) 
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                {
                    lessons.Add(obj.ToObject<object>());
                }
            }

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

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

            public string name { get; set;}

            public long totals { get; set; }
        }

    }
}