TEAMModelOS/TEAMModelBI/Controllers/Census/SchoolController.cs

using Azure.Cosmos;
using DocumentFormat.OpenXml.Bibliography;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
using NUnit.Framework.Constraints;
using Pipelines.Sockets.Unofficial.Arenas;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using TEAMModelBI.Models;
using TEAMModelBI.Tool;
using TEAMModelBI.Tool.CosmosBank;
using TEAMModelOS.Models;
using TEAMModelOS.SDK.Context.BI;
using TEAMModelOS.SDK.Context.Constant;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Extension;
using TEAMModelOS.SDK.Models;
using TEAMModelOS.SDK.Models.Cosmos.BI;
using TEAMModelOS.SDK.Models.Service.BI;
using TEAMModelOS.SDK.Models.Service.BIStatsWay;

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

        public SchoolController(AzureCosmosFactory azureCosmos, AzureStorageFactory azureStorage, DingDing dingDing, IOptionsSnapshot<Option> option, HttpTrigger httpTrigger)
        {
            _azureCosmos = azureCosmos;
            _azureStorage = azureStorage;
            _dingDing = dingDing;
            _option = option?.Value;
            _httpTrigger = httpTrigger;
        }

        /// <summary>
        /// 统计所有分析：基础、课例、活动、资源 
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-all")]
        public async Task<IActionResult> GetAll(JsonElement jsonElement)
        {
            //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释
            var cosmosClient = _azureCosmos.GetCosmosClient();
            //////分开部署，就不需要，一站多用时，取消注释
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

            long schoolCount = 0;   //学校数量
            int tecCount = 0;  //教师数量
            int stuCount = 0;  //学生数量
            long roomCount = 0;  //教室数量
            long wisdomRoomCount = 0; //智慧教室数量
            long allClassCount = 0;  //所有班级

            long allLessCount = 0; //所有课例          
            long lastYearLessCount = 0; //去年课例
            long yearLessCount = 0;//今年课例
            long lastWeekLessCount = 0; //上周课例
            long weekLessCount = 0;   //本周课例
            long lastTermLessCount = 0; //上学期课例
            long termLessCount = 0;  //本学期课例

            long allActivityCount = 0; //所有活动
            long lastActivityCount = 0; //去年活动
            long activityCount = 0;   //今年活动
            long lastWeekActivitCount = 0; //上周活动
            long weekActivitCount = 0;  //本周活动
            long lastTermActivitCount = 0;  //上学期活动
            long TermActivitCount = 0;  //本学期学期活动

            long rercCount = 0;         //所有资源数量
            long weekRercCount = 0;     //本周资源数量
            long lastWeekRercCount = 0; //上周资源数量
            long lastTermRercCount = 0; //上学期资源
            long termRercCount = 0; //本学期资源
            long lastYearRercCount = 0; //去年资源
            long yearRercCount = 0; //去年资源

            var (lastYearStart, lastYearEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.Parse($"{DateTimeOffset.UtcNow.Year - 1}-1-1"), "year"); //计算去年开始/结束时间
            var (yearStart, yearEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "year");         //计算今年开始/结束时间
            var (lastWeekStart, lastWeekEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "lastweek");   //计算上周开始/结束时间
            var (weekStart, weekEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "week");             //计算本周开始/结束时间
            var (lastTermStart, lastTermEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "lastterm");   //计算上学期开始/结束时间
            var (termStart, termEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "term");   //计算本学期开始/结束时间

            schoolCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", "select value(count(c.id)) from c", "Base");  //所有学校数量
            tecCount = await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", "select value(count(c.id)) from c", "Base");  //所有教师数量
            stuCount = await CommonFind.GetSqlValueCount(cosmosClient, "Student", "select value(count(c.id)) from c", "Base");    //所有学生数量
            allClassCount = await CommonFind.GetSqlValueCount(cosmosClient, new List<string>() { "School" }, "select count(c.id) totals from c where c.pk = 'Class'"); //所有班级数量
            roomCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", "select value(count(c.id)) from c where c.pk = 'Room'"); //所有教室数量
            wisdomRoomCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", "select value(count(c.id)) from c where c.pk = 'Room' and c.serial != null"); //智慧教室数量

            List<string> containers = new() { "School", "Teacher" };
            string lessSqlTxt = "select value(count(c.id)) from c where c.pk='LessonRecord' and c.startTime>={0} and c.startTime<={1}";
            allLessCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, $"select count(c.id) as totals from c where c.pk='LessonRecord'");//所有课例
            lastYearLessCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(lessSqlTxt, lastYearStart, lastYearEnd));  //去年课例            
            yearLessCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(lessSqlTxt, yearStart, yearEnd)); //今年课例
            lastWeekLessCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(lessSqlTxt, lastWeekStart, lastWeekEnd)); //上周课例
            weekLessCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(lessSqlTxt, weekStart, weekEnd)); //本周课例
            lastTermLessCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(lessSqlTxt, lastTermStart, lastTermEnd)); //上学期课例
            termLessCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(lessSqlTxt, termStart, termEnd)); //上学期课例

            string typeSqlTxt = "select value(count(c.id)) from c where c.pk='{0}' and c.createTime >={1} and c.createTime<= {2}";

            foreach (var type in StaticValue.activityTypes)
            {
                allActivityCount += await CommonFind.GetSqlValueCount(cosmosClient, "Common", $"select value(count(c.id)) from c where c.pk='{type}' ");//所有活动
                lastActivityCount += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(typeSqlTxt, type, lastYearStart, lastYearEnd));  //去年活动
                activityCount += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(typeSqlTxt, type, yearStart, yearEnd)); //今年活动
                lastWeekActivitCount += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(typeSqlTxt, type, lastWeekStart, lastWeekEnd)); //上周活动
                weekActivitCount += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(typeSqlTxt, type, weekStart, weekEnd));    //本周活动
                lastTermActivitCount += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(typeSqlTxt, type, lastTermStart, lastTermEnd));  //上学期活动
                TermActivitCount += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(typeSqlTxt, type, termStart, termEnd));  //本学期学期活动
            }

            string bloblSqlTxt = "select value(count(c.id)) from c where c.pk='Bloblog' and c.time>={0} and c.time<={1}";

            rercCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, "select value(count(c.id)) from c where c.pk='Bloblog'");  //所有资源
            lastWeekRercCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(bloblSqlTxt, lastWeekStart, lastWeekEnd));   //上周资源
            weekRercCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(bloblSqlTxt, weekStart, weekEnd));   //本周资源
            lastTermRercCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(bloblSqlTxt, lastTermStart, lastTermEnd));   //上学期资源
            termRercCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(bloblSqlTxt, termStart, termEnd));   //这学期资源
            lastYearRercCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(bloblSqlTxt, lastYearStart, lastYearEnd));   //去年资源
            yearRercCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(bloblSqlTxt, yearStart, yearEnd));   //今年资源

            return Ok(new { state = 200, schoolCount, tecCount, stuCount, allClassCount, roomCount, wisdomRoomCount, allLessCount, lastYearLessCount, yearLessCount, lastWeekLessCount, weekLessCount, lastTermLessCount, termLessCount, allActivityCount, lastActivityCount, activityCount, lastWeekActivitCount, weekActivitCount, lastTermActivitCount, TermActivitCount, rercCount, lastWeekRercCount, weekRercCount, lastTermRercCount, termRercCount, lastYearRercCount, yearRercCount });
        }

        /// <summary>
        /// 查询顾问相关的学校统计数据
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-assist")]
        public async Task<IActionResult> GetAssistStatis(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return BadRequest();
            //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释

            int tecCount = 0;   //教师数量
            int stuCount = 0;   //学校数量
            int classCount = 0; //班级数量
            int roomCount = 0; //智慧教师数量
            int allLessonCount = 0;  //课例数量
            int lastWeekLessCount = 0;// 上周课例数
            int weekLessCount = 0;   //本周课例
            int lastTermLessCount = 0;// 上学期课例数
            int termLessCount = 0;   //本学期课例
            int lastYearLessCount = 0;  //去年课例
            int yearLessCount = 0;  //今年课例

            int allBloblog = 0;  //学校资源
            int lastYearBloblog = 0;  //去年学校资源
            int yearBloblog = 0;  //今年学校资源

            long allActivity = 0;  //学校所有活动
            long lastYearActivity = 0;  //去年学校所有活动
            long yearActivity = 0;  //今年学校所有活动
            long lastWeekActivity = 0;  //上周学校所有活动
            long weekActivity = 0;  //本周学校所有活动

            var cosmosClient = _azureCosmos.GetCosmosClient();
            ////分开部署，就不需要，一站多用时，取消注释
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");

            var (lastWeekStart, lastWeekEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "lastweek");   //计算上周开始/结束时间
            var (weekStart, weekEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "week");             //计算本周开始/结束时间
            var (lastTermStart, lastTermEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "lastterm");   //计算上学期开始/结束时间
            var (termStart, termEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "term");   //计算本学期开始/结束时间
            var (lastYearStart, lastYearEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.Parse($"{DateTimeOffset.UtcNow.Year - 1}-1-1"), "year"); //计算去年开始/结束时间
            var (yearStart, yearEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "year");         //计算今年开始/结束时间

            string unifySqlTxt = "select value(count(c.id)) from c";
            string unifyTimeSql = "select value(count(c.id)) from c where c.startTime>={0} and c.startTime<={1}";
            string blobTimeSql = "select value(count(c.id)) from c where c.time>={0} and c.time<={1}";
            List<RecSchoolDate> recSchoolDates = new();

            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>();
                }
                tecCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", $"select value(count(c.id)) from c where ARRAY_CONTAINS(c.roles,'teacher',true) and c.status = 'join'", $"Teacher-{itemId}");
                stuCount += await CommonFind.GetSqlValueCount(cosmosClient, "Student", unifySqlTxt, $"Base-{itemId}");
                classCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", unifySqlTxt, $"Class-{itemId}");
                roomCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", unifySqlTxt, $"Room-{itemId}");
                //学校所有课例
                int tempLessCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", unifySqlTxt, $"LessonRecord-{itemId}");
                lastWeekLessCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(unifyTimeSql, lastWeekStart, lastWeekEnd), $"LessonRecord-{itemId}");
                weekLessCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(unifyTimeSql, weekStart, weekEnd), $"LessonRecord-{itemId}");
                lastTermLessCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(unifyTimeSql, lastTermStart, lastTermEnd), $"LessonRecord-{itemId}");
                termLessCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(unifyTimeSql, termStart, termEnd), $"LessonRecord-{itemId}");
                lastYearLessCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(unifyTimeSql, lastYearStart, lastYearEnd), $"LessonRecord-{itemId}");
                yearLessCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(unifyTimeSql, yearStart, yearEnd), $"LessonRecord-{itemId}");

                //学校资源
                int tempBloblog = await CommonFind.GetSqlValueCount(cosmosClient, "School", unifySqlTxt, $"Bloblog-{itemId}");
                lastYearBloblog += await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(blobTimeSql, lastYearStart, lastYearEnd), $"Bloblog-{itemId}");
                yearBloblog += await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(blobTimeSql, yearStart, yearEnd), $"Bloblog-{itemId}");

                //学校活动
                long tempallActivity = 0;
                //统计活动
                foreach (var type in StaticValue.activityTypes)
                {
                    string sqlTime = "SELECT value(count(c.id)) FROM c where c.pk = '{0}' and c.school = '{1}' and c.createTime>={2} and c.createTime<={3}";
                    tempallActivity += await CommonFind.GetSqlValueCount(cosmosClient, "Common", $"SELECT value(count(c.id)) FROM c where c.pk = '{type}' and c.school = '{itemId}'");
                    lastYearActivity += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(sqlTime, type, itemId, lastYearStart, lastYearEnd));
                    yearActivity += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(sqlTime, type, itemId, yearStart, yearEnd));
                    lastWeekActivity += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(sqlTime, type, itemId, lastWeekStart, lastWeekEnd));
                    weekActivity += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(sqlTime, type, itemId, weekStart, weekEnd));
                }
                allLessonCount += tempLessCount;
                allActivity += tempallActivity;
                allBloblog += tempBloblog;

                recSchoolDates.Add(new RecSchoolDate() { id = school.id, name = school.name, dataCount = (tempLessCount + tempallActivity + tempBloblog) });
            }

            return Ok(new { state = 200, schoolCount = schoolIds.Count, tecCount, stuCount, classCount, roomCount, allLessonCount, lastWeekLessCount, weekLessCount, lastTermLessCount, termLessCount, lastYearLessCount, yearLessCount, allBloblog, lastYearBloblog, yearBloblog, allActivity, lastYearActivity, yearActivity, lastWeekActivity, weekActivity, recSchoolDates });
        }

        /// <summary>
        /// 依据学校Id统计学校分析
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-idstatis")]
        public async Task<IActionResult> GetIdStatis(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("schoolId", out JsonElement schoolId)) return BadRequest();
            
            //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释

            int tecCount = 0;  //学校教师数量
            int stuCount = 0;  //学校学生数量
            int classCount = 0; //班级数量
            int roomCount = 0;  //教室教师数量

            long allLessCount = 0;  //课例总数
            int lastWeekLess = 0;  //上周的总数
            int weekLess = 0;    //本周的总数
            int lastTermLess = 0;   //上学期的总数
            int termLess = 0;      //本学期的总数
            int lessYearLess = 0;    //去年的总数
            int yearLess = 0;    //去年的总数

            int allActivity = 0;  //学校所有活动
            int lastYearActivity = 0;  //去年学校所有活动
            int yearActivity = 0;  //今年学校所有活动
            int lastWeekActivity = 0;  //上周学校所有活动
            int weekActivity = 0;  //本周学校所有活动

            int allBlob = 0;  //所有资源
            int lastYearBlob = 0;  //去年的资源
            int yearBlob = 0;  //去年的资源

            var cosmosClient = _azureCosmos.GetCosmosClient();
            ////分开部署，就不需要，一站多用时，取消注释
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            var (lastWeekStart, lastWeekEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "lastweek");   //计算上周开始/结束时间
            var (weekStart, weekEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "week");             //计算本周开始/结束时间
            var (lastTermStart, lastTermEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "lastterm");   //计算上学期开始/结束时间
            var (termStart, termEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "term");   //计算本学期开始/结束时间
            var (lastYearStart, lastYearEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.Parse($"{DateTimeOffset.UtcNow.Year - 1}-1-1"), "year"); //计算去年开始/结束时间
            var (yearStart, yearEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "year");         //计算今年开始/结束时间

            tecCount = await JointlySingleQuery.GetValueInt(cosmosClient, "School", "SELECT value(count(c.id)) FROM c WHERE ARRAY_CONTAINS(c.roles, 'teacher', true) AND c.status = 'join'", code: $"Teacher-{schoolId}");
            stuCount = await JointlySingleQuery.GetValueInt(cosmosClient, "Student", code: $"Base-{schoolId}");
            classCount = await JointlySingleQuery.GetValueInt(cosmosClient, "School", code: $"Class-{schoolId}");
            roomCount = await JointlySingleQuery.GetValueInt(cosmosClient, "School", code: $"Room-{schoolId}");

            string unifyTimeSql = "select value(count(c.id)) from c where c.startTime>={0} and c.startTime<={1}";
            allLessCount = await LessonStatisWay.GetSchoolIdLessonCount(cosmosClient, $"{schoolId}");
            lastWeekLess = await JointlySingleQuery.GetValueInt(cosmosClient, "School", string.Format(unifyTimeSql, lastWeekStart, lastWeekEnd), $"LessonRecord-{schoolId}");
            weekLess = await JointlySingleQuery.GetValueInt(cosmosClient, "School", string.Format(unifyTimeSql, weekStart, lastWeekEnd), $"LessonRecord-{schoolId}");
            lastTermLess = await JointlySingleQuery.GetValueInt(cosmosClient, "School", string.Format(unifyTimeSql, lastTermStart, lastTermEnd), $"LessonRecord-{schoolId}");
            termLess = await JointlySingleQuery.GetValueInt(cosmosClient, "School", string.Format(unifyTimeSql, termStart, termEnd), $"LessonRecord-{schoolId}");
            lessYearLess = await JointlySingleQuery.GetValueInt(cosmosClient, "School", string.Format(unifyTimeSql, lastYearStart, lastYearEnd), $"LessonRecord-{schoolId}");
            yearLess = await JointlySingleQuery.GetValueInt(cosmosClient, "School", string.Format(unifyTimeSql, yearStart, yearEnd), $"LessonRecord-{schoolId}");

            //统计活动
            foreach (var type in StaticValue.activityTypes)
            {
                string sqlTime = "select value(count(c.id)) from c where c.pk = '{0}' and c.school = '{1}' and c.createTime>={2} and c.createTime<={3}";
                allActivity += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"select value(count(c.id)) from c where c.pk = '{type}' and c.school = '{schoolId}'");
                lastYearActivity += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", string.Format(sqlTime, type, schoolId, lastYearStart, lastYearEnd));
                yearActivity += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", string.Format(sqlTime, type, schoolId, yearStart, yearEnd));
                lastWeekActivity += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", string.Format(sqlTime, type, schoolId, lastWeekStart, lastWeekEnd));
                weekActivity += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", string.Format(sqlTime, type, schoolId, weekStart, weekEnd));
            }

            //学校资源
            string blobTimeSql = "select value(count(c.id)) from c where c.time>={0} and c.time<={1}";
            allBlob = await JointlySingleQuery.GetValueInt(cosmosClient, "School", code: $"Bloblog-{schoolId}");
            lastYearBlob = await JointlySingleQuery.GetValueInt(cosmosClient, "School", string.Format(blobTimeSql, lastYearStart, lastYearEnd), $"Bloblog-{schoolId}");
            yearBlob = await JointlySingleQuery.GetValueInt(cosmosClient, "School", string.Format(blobTimeSql, yearStart, yearEnd), $"Bloblog-{schoolId}");

            //获取所有的课程记录
            List<LessonRecord> records = new();
            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<LessonRecord>(queryText: "select value(c) from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"LessonRecord-{schoolId}") }))
            {
                records.Add(item);
            }

            List<(string name, int count)> gradeCount = new();
            if (records.Count > 0)
            {
                var response = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync($"{schoolId}", new PartitionKey($"Base"));
                School sc = new();
                if (response.Status == 200)
                {
                    using var json = await JsonDocument.ParseAsync(response.ContentStream);
                    sc = json.ToObject<School>();
                }

                List<string> grades = new();

                foreach (var item in records)
                {
                    foreach (string gId in item.grade)
                    {
                        if (!grades.Contains(gId))
                        {
                            grades.Add(gId);
                        }
                    }
                }
                foreach (var gId in grades)
                {
                    var c = records.Where(r => r.grade.Contains(gId)).Count();
                    gradeCount.Add((gId, c));
                }
            }

            return Ok(new { state = 200, tecCount, stuCount, classCount, roomCount, allLessCount, lastWeekLess, weekLess, lastTermLess, termLess, lessYearLess, yearLess, allActivity, lastYearActivity, yearActivity, lastWeekActivity, weekActivity, allBlob, lastYearBlob, yearBlob });
        }


        /// <summary>
        /// 未区域学校统计   
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-noarea")]
        public async Task<IActionResult> GetNoAreaCnt(JsonElement jsonElement)
        {
            var cosmosClient = _azureCosmos.GetCosmosClient();

            int tLessCnt = 0;  //课例
            int tActCnt = 0;   //活动
            List<string> scIds = new(); //学校id集合

            string scIdSql = "select value(c.id) from c where c.areaId=null or c.areaId=''";
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<string>(queryText: scIdSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base") }))
            {
                scIds.Add(item);
            }

            string comSql = BICommonWay.ManyScSql("c.school", scIds);
            string lesSql = $"select value(count(c.id)) from c where c.pk='LessonRecord' and {comSql}";
            tLessCnt = await CommonFind.GetSqlValueCount(cosmosClient, new List<string>() { "School", "Teacher" }, lesSql);

            comSql = BICommonWay.ManyScSql(" and c.school", scIds);
            tActCnt = await ActivityWay.GetCnt(cosmosClient, condSql: comSql);

            return Ok(new { state = 200, scCnt=scIds.Count(), tLessCnt, tActCnt });
        }

        /// <summary>
        /// 未加区的学校统计
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-noareasc")]
        public async Task<IActionResult> GetNoAareaScCnt(JsonElement jsonElement)
        {
            var cosmosClient = _azureCosmos.GetCosmosClient();
            var tableClient = _azureStorage.GetCloudTableClient();
            var table = tableClient.GetTableReference("BIDDUserInfo");

            var (dayS, dayE) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow);
            var (weekS, weekE) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "week");
            var (monthS, monthE) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "month");
            var (termS, termE) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "term");

            List<AreaAssist> areaAssists = new();  //顾问信息

            int tchCnt = 0;   //所有区级老师
            int stuCnt = 0;   //所有区级学生
            int tLessCnt = 0;  //课例数量
            int tActCnt = 0;   //活动数量

            HashSet<string> tchIds = new();
            List<string> scIds = new(); //学校id集合

            string scIdSql = "select value(c.id) from c where c.areaId=null or c.areaId=''";
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<string>(queryText: scIdSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base") }))
            {
                scIds.Add(item);
            }

            string scsSql = BICommonWay.ManyScSql("c.school", scIds);
            string cntSql = $"select value(count(c.id)) from c where {scsSql}";

            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryIterator<int>(queryText: cntSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
            {
                tchCnt += item;
            }

            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Student").GetItemQueryIterator<int>(queryText: cntSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
            {
                stuCnt += item;
            }

            foreach (var scId in scIds)
            {
                string tchIdSql = $"select value(c.id) from c where array_contains(c.roles,'assist',true) and c.pk='Teacher'";
                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<string>(queryText: tchIdSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Teacher-{scId}") }))
                {
                    tchIds.Add(item);
                }
            }

            List<string> noIds = new();
            foreach (var item in tchIds)
            {
                List<DingDingUserInfo> ddUsers = await table.QueryWhereString<DingDingUserInfo>($"PartitionKey eq '{_option.Location}' and tmdId eq '{item}'");
                if (ddUsers.Count > 0)
                {
                    foreach (var dduser in ddUsers)
                    {
                        AreaAssist areaAssist = new()
                        {
                            userId = dduser.userId,
                            unionId = dduser.unionId,
                            name = dduser.name,
                            mobile = dduser.mobile,
                            avatar = dduser.avatar,
                            tmdId = dduser.tmdId,
                            tmdName = dduser.tmdName,
                            tmdMobile = dduser.tmdMobile,
                            picture = dduser.picture,
                            roles = dduser.roles,
                            schoolIds = dduser.schoolIds
                        };
                        areaAssists.Add(areaAssist);
                    }
                }
                else { noIds.Add(item); }
            }

            if (scIds.Count > 0)
            {
                string lesSql = $"select value(count(c.id)) from c where c.pk='LessonRecord' and {scsSql}";
                tLessCnt = await CommonFind.GetSqlValueCount(cosmosClient, new List<string>() { "School", "Teacher" }, lesSql);

                string  actComSql = BICommonWay.ManyScSql(" and c.school", scIds);
                tActCnt = await ActivityWay.GetCnt(cosmosClient, condSql: actComSql);
            }

            return Ok(new { state = RespondCode.Ok, scCnt = scIds.Count, tchCnt, stuCnt, allCnt = tLessCnt + tActCnt, tLessCnt, tActCnt});
        }

        /// <summary>
        /// 学校统计     
        /// 新的数据结构
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-scstats")]
        public async Task<IActionResult> GetSchoolStats(JsonElement jsonElement) 
        {
            if (!jsonElement.TryGetProperty("scId", out JsonElement scId)) return BadRequest();
            var cosmosClient = _azureCosmos.GetCosmosClient();

            School school = new();
            ScStats scStats = new();

            var respSc = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync($"{scId}", new PartitionKey($"Base"));
            if (respSc.Status == 200)
            {
                using var fileJson = await JsonDocument.ParseAsync(respSc.ContentStream);
                school = fileJson.ToObject<School>();
            }
            else
                return Ok(new { state = RespondCode.NotFound, msg = $"未找到id：{scId}；相关的学校，请检查" });

            try
            {
                bool isExist = true;
                StatsInfo statsInfo = new();
                var scDataStats = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync($"{scId}", new PartitionKey("Stats"));
                if (scDataStats.Status == 200)
                {
                    using var fileJson = await JsonDocument.ParseAsync(scDataStats.ContentStream);
                    statsInfo = fileJson.ToObject<StatsInfo>();
                }
                else
                {
                    scStats.id = school.id;
                    isExist = false;
                }

                bool isnew = false;
                DateTimeOffset dateTime = DateTimeOffset.UtcNow;
                long now = dateTime.ToUnixTimeMilliseconds();
                if (!string.IsNullOrEmpty(statsInfo.id)) 
                {
                    scStats.id= statsInfo.id;
                    scStats.name = statsInfo.name;
                    scStats.picture = statsInfo.picture;
                    scStats.tch = statsInfo.tch;
                    scStats.dayTch = statsInfo.dayTch;
                    scStats.weekTch = statsInfo.weekTch;
                    scStats.monthTch = statsInfo.monthTch;
                    scStats.stu = statsInfo.stu;
                    scStats.dayStu = statsInfo.dayStu;
                    scStats.weekStu = statsInfo.weekStu;
                    scStats.monthStu = statsInfo.monthStu;
                    scStats.room = statsInfo.room;
                    scStats.witRoom = statsInfo.witRoom;
                    scStats.size = statsInfo.size;
                    scStats.scCreateTime = statsInfo.scCreateTime;
                    scStats.upTime = statsInfo.upTime;
                    scStats.lessStats.open = statsInfo.lesson.open;
                    scStats.lessStats.less = statsInfo.lesson.less;
                    scStats.lessStats.lastDay = statsInfo.lesson.lastDay;
                    scStats.lessStats.day = statsInfo.lesson.day;
                    scStats.lessStats.lastWeek = statsInfo.lesson.lastWeek;
                    scStats.lessStats.week = statsInfo.lesson.week;
                    scStats.lessStats.lastTerm = statsInfo.lesson.lastTerm;
                    scStats.lessStats.term = statsInfo.lesson.term;
                    scStats.lessStats.lastDayInter = statsInfo.lesson.lastDayInter;
                    scStats.lessStats.dayInter = statsInfo.lesson.dayInter;
                    scStats.lessStats.lastMonthInter = statsInfo.lesson.lastMonthInter;
                    scStats.lessStats.monthInter = statsInfo.lesson.monthInter;
                    scStats.lessStats.lastYearInter = statsInfo.lesson.lastYearInter;
                    scStats.lessStats.yearInter=statsInfo.lesson.yearInter;
                    scStats.lessStats.LastYear = TimeHelper.GetYearMonth(statsInfo.lesson.LastYear, dateTime.Year, dateTime.Month);
                    scStats.lessStats.year = TimeHelper.GetYearMonth(statsInfo.lesson.year, dateTime.Year, dateTime.Month);
                    scStats.actStats.cnt = statsInfo.activity.cnt;
                    scStats.actStats.exam = statsInfo.activity.exam;
                    scStats.actStats.survey = statsInfo.activity.survey;
                    scStats.actStats.vote = statsInfo.activity.vote;
                    scStats.actStats.homework = statsInfo.activity.homework;
                    scStats.actStats.lastDay = statsInfo.activity.lastDay;
                    scStats.actStats.dayCnt = statsInfo.activity.dayCnt;
                    scStats.actStats.lastWeek = statsInfo.activity.lastWeek;
                    scStats.actStats.week = statsInfo.activity.week;
                    scStats.actStats.lastTerm = statsInfo.activity.lastTerm;
                    scStats.actStats.term = statsInfo.activity.term;
                    scStats.actStats.LastYear = TimeHelper.GetYearMonth(statsInfo.activity.LastYear ,dateTime.Year,dateTime.Month);
                    scStats.actStats.year = TimeHelper.GetYearMonth(statsInfo.activity.year, dateTime.Year, dateTime.Month);
                    if (statsInfo.study != null)
                    {
                        scStats.srStats.learnTime = statsInfo.study.learnTime;
                        scStats.srStats.online = statsInfo.study.online;
                        scStats.srStats.offline = statsInfo.study.offline;
                        scStats.srStats.classRoom = statsInfo.study.classRoom;
                        scStats.srStats.submit = statsInfo.study.submit;
                        scStats.srStats.notStarted = statsInfo.study.notStarted;
                        scStats.srStats.ongoing = statsInfo.study.ongoing;
                        scStats.srStats.finish = statsInfo.study.finish;
                    }
                }

                if(statsInfo.upTime > 0 && !isnew) {
                    long timedis = 20 * 60 * 1000;//20分钟
                    if (now - statsInfo.upTime < timedis)
                        isnew = true;
                }
                if (statsInfo.upTime == 0 && isnew)
                    statsInfo.upTime = now;
                if (statsInfo.upTime == 0 && isnew)
                    statsInfo.upTime = DateTimeOffset.UtcNow.Add(new TimeSpan(-1, 0, 0, 0)).ToUnixTimeMilliseconds();
                if (!isnew)
                {
                    //Function 处理Function 更新或者新增
                    //_ = _httpTrigger.RequestHttpTrigger(new { schoolId = $"{school.id}"}, _option.Location, "stats-sc-info");

                    //异步方法处理 同步更新
                    statsInfo = await SchoolStatsWay.GetSingleSc(cosmosClient, school.id);

                    statsInfo.upTime = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
                    if (isExist)
                        statsInfo = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReplaceItemAsync<StatsInfo>(statsInfo, statsInfo.id, new PartitionKey("Stats"));
                    else
                        statsInfo = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").CreateItemAsync<StatsInfo>(statsInfo, new PartitionKey("Stats"));
                }

                return Ok(new { state = RespondCode.Ok, statsInfo });
            }
            catch (Exception ex)
            {
                //await _dingDing.SendBotMsg($"BI，{_option.Location}  /school/set-scstats   \n    {ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 区域统计
        /// 新的数据结构
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-areastats")]
        public async Task<IActionResult> GetAreaStats(JsonElement jsonElement) 
        {
            if (!jsonElement.TryGetProperty("areaId", out JsonElement areaId)) return BadRequest();

            var cosmosClient = _azureCosmos.GetCosmosClient();

            Area area = new();
            ScStats areaScStats = new();

            var respSc = await cosmosClient.GetContainer(Constant.TEAMModelOS, "Normal").ReadItemStreamAsync($"{areaId}", new PartitionKey($"Base-Area"));
            if (respSc.Status == 200)
            {
                using var fileJson = await JsonDocument.ParseAsync(respSc.ContentStream);
                area = fileJson.ToObject<Area>();
            }
            else
                return Ok(new { state = RespondCode.NotFound, msg = $"未找到id：{areaId}；相关的学区，请检查!" });

            List<string> scIds = new();
            //scIds.Contains
            string scSql = $"select value(c.id) from c where c.areaId='{areaId}'";

            scIds = await CommonFind.FindScIds(cosmosClient, scSql, "Base");

            //await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<string>(queryText: scSql,requestOptions:new QueryRequestOptions() { PartitionKey = new PartitionKey("Base")} ))
            //{
            //    scIds.Add(item);
            //}

            StringBuilder statsSql = new("select value(c) from c");
            if (scIds.Count > 0) 
            {
                statsSql.Append($" where {BICommonWay.ManyScSql("c.id", scIds)}");
            }

            List<ScStats> scStats = new();
            List<StatsInfo> statsInfos = new();
            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS,"School").GetItemQueryIterator<StatsInfo>(queryText:statsSql.ToString(),requestOptions:new QueryRequestOptions() { PartitionKey = new PartitionKey("Stats") }))
            {
                statsInfos.Add(item);
            }

            StatsInfo statsInfo = new();
            statsInfo = await SchoolStatsWay.GetAreaStats(cosmosClient, _httpTrigger, _option, statsInfos, scIds);

            return Ok(new { state = RespondCode.Ok ,area, statsInfo, scIds });
        }


        /// <summary>
        /// 依据Id查询School容器  数据管理工具——查询工具
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-info")]
        public async Task<IActionResult> GetSchool(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("id", out JsonElement id)) return BadRequest();
            //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署，就不需要，一站多用时，取消注释
            var cosmosClient = _azureCosmos.GetCosmosClient();
            //if ($"{site}".Equals(BIConst.Global))
            //    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            List<object> infos = new List<object>();

            string sqlTxt = $"select value(c) from c where c.id='{id}'";
            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())
                {
                    infos.Add(obj.ToObject<object>());
                }
            }

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

        public record RecSchoolDate
        {
            public string id { get; set; }
            public string name { get; set; }
            public long dataCount { get; set; }
        }

        /// <summary>
        /// 前端显示学校统计数据
        /// </summary>
        public record ScStats
        {
            public string id { get; set; }
            public string name { get; set; }
            public string picture { get; set; }
            public int tch { get; set; }
            public int dayTch { get; set; }
            public int weekTch { get; set; }
            public int monthTch { get; set; }
            public int stu { get; set; }
            public int dayStu { get; set; }
            public int weekStu { get; set; }
            public int monthStu { get; set; }
            public int room { get; set; }
            public int witRoom { get; set; }
            public int size { get; set; }
            public long scCreateTime { get; set; }
            public long upTime { get; set; }
            public ScLessStats lessStats { get; set; } = new ScLessStats();
            public ScActStats actStats { get; set; } = new ScActStats();

            public ScSRStats srStats { get; set; } = new ScSRStats();
        }

        /// <summary>
        /// 课例活动数据
        /// </summary>
        public record ScLessStats
        {
            public int open { get; set; }
            public int less { get; set; }
            public int lastDay { get; set; }
            public int day { get; set; }
            public int lastWeek { get; set; }
            public int week { get; set; }
            public int lastTerm { get; set; }
            public int term { get; set; }
            public int lastDayInter { get; set; }
            public int dayInter { get; set; }
            public int lastMonthInter { get; set; }
            public int monthInter { get; set; }
            public int lastYearInter { get; set; }
            public int yearInter { get; set; }
            //public List<YearMonth> yearInter { get; set; } = new List<YearMonth>();//12个月
            public List<YearMonth> LastYear { get; set; } = new List<YearMonth>();//12个月
            public List<YearMonth> year { get; set; } = new List<YearMonth>();    //12个月
        }

        /// <summary>
        /// 学校活动数据
        /// </summary>
        public record ScActStats 
        {
            public int cnt { get; set; }
            public int exam { get; set; }
            public int survey { get; set; }
            public int vote { get; set; }
            public int homework { get; set; }
            public int lastDay { get; set; }
            public int dayCnt { get; set; }
            public int lastWeek { get; set; }
            public int week { get; set; }
            public int lastTerm { get; set; }
            public int term { get; set; }
            public List<YearMonth> LastYear { get; set; } = new List<YearMonth>(); //12个月
            public List<YearMonth> year { get; set; } = new List<YearMonth>();    //12个月
        }

        /// <summary>
        /// 研修统计数据
        /// </summary>
        public record ScSRStats 
        { 
            public int learnTime { get; set; }
            public int online { get; set; }
            public int offline { get; set; }
            public int classRoom { get; set; }
            public int submit { get; set; }
            public int notStarted { get; set; }
            public int ongoing { get; set; }
            public int finish { get; set; }
        }
    }
}