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;
using TEAMModelBI.Tool.CosmosBank;

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();
            StringBuilder sqlTxt = new($"select COUNT(c.id) AS totals  from c where c.pk='LessonRecord'");
            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                List<SchoolLen> schoolLens = new();

                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() { id = itemId, name =
                        
                        school.name != null ? school.name : itemId };

                    //string sqlTxt = $"SELECT COUNT(c.id) AS totals FROM c WHERE c.code='LessonRecord-{itemId}'";
                    sqlTxt.Append($" 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
            {
                sqlTxt.Append($" 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","Teacher" });
            }

            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","Teacher" });

                return Ok(new { state = 200, total });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI，{_option.Location}  /lesson/get-total \n {ex.Message}\n{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<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() { 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}\n{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<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() { 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<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() { 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();
                    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>
        /// 统计区级课例
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-areacount")]
        public async Task<IActionResult> GetAreaCount(JsonElement jsonElement) 
        {
            if(!jsonElement.TryGetProperty("areaId", out JsonElement areaId)) return BadRequest();
            List<string> schools = new();
            var cosmosClient = _azureCosmos.GetCosmosClient();

            StringBuilder scSqlTxt = new("select c.id from c");
            if (!string.IsNullOrEmpty($"{areaId}"))
            {
                scSqlTxt.Append($" where c.areaId='{areaId}'");
            }

            schools = await CommonFind.FindSchoolIds(cosmosClient, scSqlTxt.ToString(), "Base");

            //所有的课程记录
            List<LessonRecord> records = new();
            List<string> tecIds = new();

            foreach (var school in schools)
            {
                string sqlTxt = $"select value(c) from c where c.code='LessonRecord-{school}'";
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<LessonRecord>(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"LessonRecord-{school}") }))
                {
                    records.Add(item);
                }
            }

            tecIds = await CommonFind.FindRolesId(cosmosClient, schools);
            foreach (var tecId in tecIds)
            {
                string sqlTxt = $"select value(c) from c where c.id='{tecId}'";
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryIterator<LessonRecord>(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"LessonRecord") }))
                {
                    records.Add(item);
                }
            }

            int dayCount = 0;
            var (dayStart, dayEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow);
            records.ForEach(x => { if (x.startTime >= dayStart && x.startTime <= dayEnd) dayCount += 1; });

            int weekCount = 0;
            var (weekStart, weekEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "week");
            records.ForEach(x => { if (x.startTime >= weekStart && x.startTime <= weekEnd) weekCount += 1; });

            int monthCount = 0;
            var (monthStart, monthEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "month");
            records.ForEach(x => { if (x.startTime >= monthStart && x.startTime <= monthEnd) monthCount += 1; });

            int termCount = 0;
            var (termStart, termEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "term");
            records.ForEach(x => { if (x.startTime >= termStart && x.startTime <= termEnd) termCount += 1; });


            double teachCount = records.Where(r => r.tmdid != null).Where((x, i) => records.FindIndex(z => z.tmdid == x.tmdid) == i).ToList().Count;

            return Ok(new { state = 200, lessonCount = records.Count, teachCount, dayCount, weekCount, monthCount, termCount });
        }


        /// <summary>
        /// 统计区级一年每周的课例数据趋势
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-weekcount")]
        public async Task<IActionResult> GetWeekCount(JsonElement jsonElement) 
        {
            jsonElement.TryGetProperty("areaId", out JsonElement areaId);

            //Dictionary<int, double> weeks = new();
            List<double> weeks = new();
            var cosmosClient = _azureCosmos.GetCosmosClient();

            int year = DateTimeOffset.UtcNow.Year;
            int dayOfweek = (int)DateTimeOffset.Parse($"{year}-1-1").DayOfWeek;
            int currentTime = DateTimeOffset.UtcNow.DayOfYear / 7 + 1;
            int currentTime1 = DateTimeOffset.UtcNow.DayOfYear / 7;

            var sqlTxts = "select value(c) from c";
            List<LessonCount> scount = new();
            List<LessonCount> tcount = new();

            StringBuilder sqlTxt = new("select c.id from c");
            if (!string.IsNullOrEmpty($"{areaId}"))
            {
                sqlTxt.Append($" where c.areaId='{areaId}'");
            }

            List<string> schools = await CommonFind.FindSchoolIds(cosmosClient, sqlTxt.ToString(), "Base");

            foreach (var sId in schools)
            {
                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<LessonCount>(queryText:sqlTxts,requestOptions:new QueryRequestOptions() { PartitionKey =new PartitionKey($"LessonCount-{sId}-{year}")}))
                {
                    scount.Add(item);
                }
            }

            List<string> teacIds = await CommonFind.FindRolesId(cosmosClient, schools);            
            foreach (var tId in teacIds)
            {
                var sqlTxtt = $"select value(c) from c where c.id='{tId}'";
                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryIterator<LessonCount>(queryText: sqlTxtt, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"LessonCount-{year}") }))
                {
                    tcount.Add(item);
                }
            }

            int days = (year % 4 == 0 && year % 100 != 0 || year % 400 == 0) ? days = 366 : days = 365;

            List<List<double>> lessons = new();
            if (scount.Count > 0)
            {
                foreach (LessonCount item in scount)
                {
                    lessons.Add(item.beginCount);
                }
            }
            if (tcount.Count > 0)
            {
                foreach (LessonCount item in tcount)
                {
                    lessons.Add(item.beginCount);
                }
            }

            if (lessons.Count > 0) 
            { 
                var bmatrix = DenseMatrix.OfColumns(lessons);

                //开学第一周周内开课                   
                if (dayOfweek == 0)
                {
                    dayOfweek = 7;
                }
                //第一周多少天
                var dd = 7 - dayOfweek + 1;
                //一年有几周
                int sweeks = days / 7;
                //查询天数
                int dayYear = 0;
                if (currentTime > 0)
                {
                    for (int i = 0; i <= currentTime; i++)
                    {
                        if (i == 0)
                        {
                            var bsum = bmatrix.SubMatrix(dayYear, dd, 0, bmatrix.ColumnCount).ColumnSums().Sum();
                            dayYear += dd;
                            //weeks.Add(i, bsum);
                            weeks.Add(bsum);
                        }
                        else
                        {
                            var bsum = bmatrix.SubMatrix(dayYear, 7, 0, bmatrix.ColumnCount).ColumnSums().Sum();
                            dayYear += 7;
                            //weeks.Add(i, bsum);
                            weeks.Add(bsum);
                        }
                    }
                }
                //最后一周是否有余
                int stary = days - dayYear;
                if (stary > 0 && stary < 7)
                {
                    var bsum = bmatrix.SubMatrix(dayYear, stary - 1, 0, bmatrix.ColumnCount).ColumnSums().Sum();
                    //weeks.Add((sweeks + 1), bsum);
                    weeks.Add(bsum);
                }
            }

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

        /// <summary>
        /// 统计所有区级的课例和活动
        /// </summary>
        /// <returns></returns>
        [HttpPost("get-allarea")]
        public async Task<IActionResult> GetAllArea()
        {
            var cosmosClient = _azureCosmos.GetCosmosClient();

            List<AllAreaInfo> areaInfos = new();
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Normal").GetItemQueryIterator<AllAreaInfo>(queryText: $"select c.id,c.name,c.standard,c.standardName from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base-Area") }))
            {
                areaInfos.Add(item);
            }

            foreach (var area in areaInfos)
            {
                List<string> schooId = await CommonFind.FindSchoolIds(cosmosClient,$"select c.id from c where c.areaId='{area.id}'","Base");
                List<string> tecId = await CommonFind.FindRolesId(cosmosClient, schooId);

                area.lessCount = await LessonStatisWay.GetAll(cosmosClient, schooId, tecId);
                area.activityCount = await ActivityWay.GetAll(cosmosClient, schooId, tecId);
            }


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


        /// <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();
            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 AllAreaInfo
        {
            public string id { get; set; }
            public string name { get; set; }
            public string standard { get; set; }
            public string standardName { get; set; }
            public int lessCount { get; set; }
            public int activityCount { get; set; }

        }

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

            public string name { get; set;}

            public long totals { get; set; }
        }
    }
}