TEAMModelOS/TEAMModelBI/Controllers/Census/SchoolController.cs

using Azure.Core;
using Azure.Cosmos;
using Azure.Storage.Blobs;
using DocumentFormat.OpenXml.Bibliography;
using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
using NUnit.Framework.Constraints;
using Pipelines.Sockets.Unofficial.Arenas;
using StackExchange.Redis;
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.Cosmos.BI.BISchool;
using TEAMModelOS.SDK.Models.Service.BI;
using TEAMModelOS.SDK.Models.Service.BIStatsWay;
using LessonStats = TEAMModelOS.SDK.Models.Cosmos.BI.LessonStats;

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;
        private readonly AzureRedisFactory _azureRedis;

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

        /// <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", $"Teacher-{schoolId}", "SELECT value(count(c.id)) FROM c WHERE ARRAY_CONTAINS(c.roles, 'teacher', true) AND c.status = 'join'");
            stuCount = await JointlySingleQuery.GetValueInt(cosmosClient, "Student", $"Base-{schoolId}");
            classCount = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"Class-{schoolId}");
            roomCount = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"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", $"LessonRecord-{schoolId}", string.Format(unifyTimeSql, lastWeekStart, lastWeekEnd));
            weekLess = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"LessonRecord-{schoolId}", string.Format(unifyTimeSql, weekStart, lastWeekEnd));
            lastTermLess = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"LessonRecord-{schoolId}", string.Format(unifyTimeSql, lastTermStart, lastTermEnd));
            termLess = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"LessonRecord-{schoolId}", string.Format(unifyTimeSql, termStart, termEnd));
            lessYearLess = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"LessonRecord-{schoolId}", string.Format(unifyTimeSql, lastYearStart, lastYearEnd));
            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", $"{type}-{schoolId}", $"select value(count(c.id)) from c where c.pk = '{type}' and c.school = '{schoolId}'");
                lastYearActivity += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{type}-{schoolId}", string.Format(sqlTime, type, schoolId, lastYearStart, lastYearEnd));
                yearActivity += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{type}-{schoolId}", string.Format(sqlTime, type, schoolId, yearStart, yearEnd));
                lastWeekActivity += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{type}-{schoolId}", string.Format(sqlTime, type, schoolId, lastWeekStart, lastWeekEnd));
                weekActivity += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{type}-{schoolId}", 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", $"Bloblog-{schoolId}");
            lastYearBlob = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"Bloblog-{schoolId}", string.Format(blobTimeSql, lastYearStart, lastYearEnd));
            yearBlob = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"Bloblog-{schoolId}", string.Format(blobTimeSql, yearStart, yearEnd));

            //获取所有的课程记录
            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 });
        }

        #region   新的统计接口

        /// <summary>
        /// 参与的单个学校统计接口
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-joinschool")]
        public async Task<IActionResult> GetJoinSchoolStats(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}；相关的学校，请检查" });
            }

            StatsInfo statsInfo = null;
            List<LastYearLessAndAct> lastYear = new();
            var scDataStats = await cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").ReadItemStreamAsync($"{DateTimeOffset.UtcNow.Year}-{scId}", new PartitionKey("Statistics"));
            if (scDataStats.Status == 200)
            {
                using var fileJson = await JsonDocument.ParseAsync(scDataStats.ContentStream);
                statsInfo = fileJson.ToObject<StatsInfo>();
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator<LastYearLessAndAct>(queryText: $"select c.activity,c.lesson from c where c.id='{DateTimeOffset.UtcNow.Year - 1}-{scId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
                {
                    lastYear.Add(item);
                }
            }
            {
                scStats.id = $"{DateTimeOffset.UtcNow.Year}-{school.id}";
                scStats.schoolId = school.id;
                scStats.name = school.name;
                scStats.picture = school.picture;
                scStats.areaId = school.areaId;
            }

            DateTimeOffset dateTime = DateTimeOffset.UtcNow;
            long now = dateTime.ToUnixTimeMilliseconds();
            if (statsInfo != null)
            {
                scStats.id = statsInfo.id;
                scStats.schoolId = statsInfo.schoolId;
                scStats.name = statsInfo.name;
                scStats.picture = statsInfo.picture;
                scStats.areaId = statsInfo.areaId;
                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.resourceCnt = statsInfo.resourceCnt;
                scStats.size = statsInfo.size;
                scStats.scCreateTime = statsInfo.scCreateTime;
                scStats.upTime = statsInfo.upTime;
                scStats.lessStats.all = statsInfo.lesson.all;
                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.month = statsInfo.lesson.month;
                scStats.lessStats.lastMonth = statsInfo.lesson.lastMonth;
                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.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList()).Sum());
                scStats.lessStats.lastYearInter = statsInfo.lesson.lastYearInter;
                scStats.lessStats.yearInter = statsInfo.lesson.yearInter;
                scStats.lessStats.yearInters = TimeHelper.GetYearMonth(statsInfo.lesson.yearInters, dateTime.Year, dateTime.Month);
                scStats.lessStats.year = TimeHelper.GetYearMonth(statsInfo.lesson.year, dateTime.Year, dateTime.Month);
                scStats.actStats.all = statsInfo.activity.all;
                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.lastMonth = statsInfo.activity.lastMonth;
                scStats.actStats.month = statsInfo.activity.month;
                scStats.actStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.activity.year).Where(w => w.Count > 0).ToList()).Sum());

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

            var weekLess = BICommonWay.weekDoubleMerge(new List<List<double>>() { statsInfo.lesson.year }, dateTime);
            var weekAct = BICommonWay.weekDoubleMerge(new List<List<double>>() { statsInfo.activity.year }, dateTime);

            return Ok(new { state = RespondCode.Ok, scStats, weekLess, weekAct });
        }


        /// <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
            {
                StatsInfo statsInfo = null;
                List<LastYearLessAndAct> lastYear = new();
                var scDataStats = await cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").ReadItemStreamAsync($"{DateTimeOffset.UtcNow.Year}-{scId}", new PartitionKey("Statistics"));
                if (scDataStats.Status == 200)
                {
                    using var fileJson = await JsonDocument.ParseAsync(scDataStats.ContentStream);
                    statsInfo = fileJson.ToObject<StatsInfo>();
                    await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator<LastYearLessAndAct>(queryText: $"select c.activity,c.lesson from c where c.id='{DateTimeOffset.UtcNow.Year - 1}-{scId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
                    {
                        lastYear.Add(item);
                    }
                }
                else
                {
                    scStats.id = $"{DateTimeOffset.UtcNow.Year}-{school.id}";
                    scStats.schoolId = school.id;
                    scStats.name = school.name;
                    scStats.picture = school.picture;
                    scStats.areaId = school.areaId;
                }

                DateTimeOffset dateTime = DateTimeOffset.UtcNow;
                long now = dateTime.ToUnixTimeMilliseconds();
                if (statsInfo != null)
                {
                    scStats.id = statsInfo.id;
                    scStats.schoolId = statsInfo.schoolId;
                    scStats.name = statsInfo.name;
                    scStats.picture = statsInfo.picture;
                    scStats.areaId = statsInfo.areaId;
                    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.resourceCnt = statsInfo.resourceCnt;
                    scStats.size = statsInfo.size;
                    scStats.scCreateTime = statsInfo.scCreateTime;
                    scStats.upTime = statsInfo.upTime;
                    scStats.lessStats.all = statsInfo.lesson.all;
                    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.month = statsInfo.lesson.month;
                    scStats.lessStats.lastMonth = statsInfo.lesson.lastMonth;
                    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.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList()).Sum());
                    scStats.lessStats.lastYearInter = statsInfo.lesson.lastYearInter;
                    scStats.lessStats.yearInter = statsInfo.lesson.yearInter;
                    scStats.lessStats.yearInters = TimeHelper.GetYearMonth(statsInfo.lesson.yearInters, dateTime.Year, dateTime.Month);
                    scStats.lessStats.year = TimeHelper.GetYearMonth(statsInfo.lesson.year, dateTime.Year, dateTime.Month);
                    scStats.actStats.all = statsInfo.activity.all;
                    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.lastMonth = statsInfo.activity.lastMonth;
                    scStats.actStats.month = statsInfo.activity.month;
                    scStats.actStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.activity.year).Where(w => w.Count > 0).ToList()).Sum());

                    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, scStats });
            }
            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-rolestats")]
        public async Task<IActionResult> GetRoleStats(JsonElement jsonElement)
        {
            //jsonElement.TryGetProperty("areaId", out JsonElement areaId);
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            jsonElement.TryGetProperty("role", out JsonElement role);

            //if (string.IsNullOrEmpty($"{areaId}") && string.IsNullOrEmpty($"{tmdId}"))
            //    return Ok(new { stats = RespondCode.ParamsError, msg = "areaId/tmdId两者参数不能都为空！" });

            var cosmosClient = _azureCosmos.GetCosmosClient();
            Area area = null;
            ScStats areaScStats = new();
            List<string> scIds = new();
            DateTimeOffset dateTime = DateTimeOffset.UtcNow;
            List<ScStats> scStatss = new();
            List<StatsInfo> statsInfos = new();

            StringBuilder scSql = new("select c.id,c.name,c.size,c.scale,c.serial,c.service,c.hard from c");
            StringBuilder lastScSql = new("select value(c.id) from c");
            //if (!string.IsNullOrEmpty($"{areaId}"))
            //{
            //    scSql.Append($" where c.areaId='{areaId}'");
            //    lastScSql.Append($" where c.areaId='{areaId}'");
            //    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}；相关的学区，请检查!" });
            //}

            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                switch ($"{role}")
                {
                    case "assist":
                        scSql.Append($" join a in c.assists where a.id='{tmdId}'");
                        lastScSql.Append($" join a in c.assists where a.id='{tmdId}'");
                        break;
                    case "sales":
                        scSql.Append($" join a in c.sales where a.id='{tmdId}'");
                        lastScSql.Append($" join a in c.sales where a.id='{tmdId}'");
                        break;
                    default:
                        return Ok(new { state = RespondCode.ParamsError, msg = "role参数错误" });
                }
            }
            List<BIRelStats> bIRelStats = new();

            List<string> tempProduct = new();

            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<BIRelStats>(queryText: scSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("BIRel") }))
            {
                if (item.serial == null) item.serial = new List<string>();
                if (item.service == null) item.service = new List<string>();
                if (item.hard == null) item.hard = new List<string>();
                bIRelStats.Add(item);
                tempProduct.AddRange(item.serial);
                tempProduct.AddRange(item.service);
                tempProduct.AddRange(item.hard);
            }

            if (bIRelStats.Count > 0)
                scIds = bIRelStats.Select(s => s.id).ToList();
            //scIds = await CommonFind.FindScIds(cosmosClient, scSql.ToString(), "BIRel");

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

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

            StringBuilder lastStsSql = new($"select c.lesson,c.activity from c where c.year={dateTime.Year}");
            if (scIds.Count > 0)
            {
                lastStsSql.Append($" and {BICommonWay.ManyScSql("c.id", scIds, $"{DateTimeOffset.UtcNow.Year - 1}-")}");
            }
            List<LastYearLessAndAct> lastYear = new();

            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator<LastYearLessAndAct>(queryText: lastStsSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
            {
                lastYear.Add(item);
            }

            StatsInfo statsInfo = null;
            statsInfo = SchoolStatsWay.GetAreaStats(cosmosClient, _option, statsInfos, scIds, area);
            if (statsInfo != null)
            {
                areaScStats.id = statsInfo.id;
                areaScStats.schoolId = statsInfo.schoolId;
                areaScStats.name = statsInfo.name;
                areaScStats.picture = statsInfo.picture;
                areaScStats.areaId = statsInfo.areaId;
                areaScStats.tch = statsInfo.tch;
                areaScStats.dayTch = statsInfo.dayTch;
                areaScStats.weekTch = statsInfo.weekTch;
                areaScStats.monthTch = statsInfo.monthTch;
                areaScStats.stu = statsInfo.stu;
                areaScStats.dayStu = statsInfo.dayStu;
                areaScStats.weekStu = statsInfo.weekStu;
                areaScStats.monthStu = statsInfo.monthStu;
                areaScStats.room = statsInfo.room;
                areaScStats.witRoom = statsInfo.witRoom;
                areaScStats.resourceCnt = statsInfo.witRoom;
                areaScStats.size = statsInfo.size;
                areaScStats.scCreateTime = statsInfo.scCreateTime;
                areaScStats.upTime = statsInfo.upTime;
                areaScStats.lessStats.open = statsInfo.lesson.all;
                areaScStats.lessStats.open = statsInfo.lesson.open;
                areaScStats.lessStats.less = statsInfo.lesson.less;
                areaScStats.lessStats.lastDay = statsInfo.lesson.lastDay;
                areaScStats.lessStats.day = statsInfo.lesson.day;
                areaScStats.lessStats.lastWeek = statsInfo.lesson.lastWeek;
                areaScStats.lessStats.week = statsInfo.lesson.week;
                areaScStats.lessStats.lastTerm = statsInfo.lesson.lastTerm;
                areaScStats.lessStats.term = statsInfo.lesson.term;
                areaScStats.lessStats.lastDayInter = statsInfo.lesson.lastDayInter;
                areaScStats.lessStats.dayInter = statsInfo.lesson.dayInter;
                areaScStats.lessStats.lastMonthInter = statsInfo.lesson.lastMonthInter;
                areaScStats.lessStats.monthInter = statsInfo.lesson.monthInter;
                areaScStats.lessStats.lastYearInter = statsInfo.lesson.lastYearInter;
                areaScStats.lessStats.yearInter = statsInfo.lesson.yearInter;
                areaScStats.lessStats.yearInters = TimeHelper.GetYearMonth(statsInfo.lesson.yearInters, dateTime.Year, dateTime.Month);
                areaScStats.lessStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList()).Sum());
                areaScStats.lessStats.year = TimeHelper.GetYearMonth(statsInfo.lesson.year, dateTime.Year, dateTime.Month);
                areaScStats.actStats.all = statsInfo.activity.all;
                areaScStats.actStats.exam = statsInfo.activity.exam;
                areaScStats.actStats.survey = statsInfo.activity.survey;
                areaScStats.actStats.vote = statsInfo.activity.vote;
                areaScStats.actStats.homework = statsInfo.activity.homework;
                areaScStats.actStats.lastDay = statsInfo.activity.lastDay;
                areaScStats.actStats.dayCnt = statsInfo.activity.dayCnt;
                areaScStats.actStats.lastWeek = statsInfo.activity.lastWeek;
                areaScStats.actStats.week = statsInfo.activity.week;
                areaScStats.actStats.lastTerm = statsInfo.activity.lastTerm;
                areaScStats.actStats.term = statsInfo.activity.term;
                areaScStats.actStats.lastMonth = statsInfo.activity.lastMonth;
                areaScStats.actStats.month = statsInfo.activity.month;
                areaScStats.actStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.activity.year).Where(w => w.Count > 0).ToList()).Sum());
                areaScStats.actStats.year = TimeHelper.GetYearMonth(statsInfo.activity.year, dateTime.Year, dateTime.Month);
                if (statsInfo.study != null)
                {
                    areaScStats.srStats.learnTime = statsInfo.study.learnTime;
                    areaScStats.srStats.online = statsInfo.study.online;
                    areaScStats.srStats.offline = statsInfo.study.offline;
                    areaScStats.srStats.classRoom = statsInfo.study.classRoom;
                    areaScStats.srStats.submit = statsInfo.study.submit;
                    areaScStats.srStats.notStarted = statsInfo.study.notStarted;
                    areaScStats.srStats.ongoing = statsInfo.study.ongoing;
                    areaScStats.srStats.finish = statsInfo.study.finish;
                }
            }

            var scInfos = statsInfos.Select(s => new { s.id, s.schoolId, s.name, s.picture, size = bIRelStats.Where(w => w.id.Equals(s.schoolId)).Select(si => si.size).FirstOrDefault(), scale = bIRelStats.Where(w => w.id.Equals(s.schoolId)).Select(si => si.scale).FirstOrDefault(), serial = bIRelStats.Where(w => w.id.Equals(s.schoolId)).Select(si => si.serial).FirstOrDefault(), service = bIRelStats.Where(w => w.id.Equals(s.schoolId)).Select(si => si.service).FirstOrDefault(), hard = bIRelStats.Where(w => w.id.Equals(s.schoolId)).Select(si => si.hard).FirstOrDefault(), actLess = (s.lesson.all + s.activity.all), actCnt = s.activity.all, lessCnt = s.lesson.all, lessOpen = s.lesson.open, lessUp = s.lesson.less }).ToList();

            var productGroup = tempProduct.GroupBy(g => g).Select(s => new { code = s.Key, cnt = s.Count() });

            return Ok(new { state = RespondCode.Ok, areaScStats, scInfos, productGroup });
        }

        /// <summary>
        ///  依据区级Id统计 新数据结构
        /// </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 redisClinet = _azureRedis.GetRedisClient(8);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            var tableClient = _azureStorage.GetCloudTableClient();
            var blobClient = _azureStorage.GetBlobContainerClient(containerName: "0-public");
            DateTimeOffset dateTime = DateTimeOffset.UtcNow;
            Area area = null;
            AreaStats areaScStats = new();
            List<string> scIds = new();
            List<BISchoolInfo> schoolInfos = new();
            List<ScStats> scStatss = new();
            List<StatsInfo> statsInfos = new();
            long useSize = 0;
            Dictionary<string, double?> typeStics = new(); //所有类型
            var (weekS, weekE) = TimeHelper.GetStartOrEnd(dateTime, "week");             //计算本周开始/结束时间
            var (mthS, mthE) = TimeHelper.GetStartOrEnd(dateTime, "month");  //本月开始/结束时间
            //學區成員修正：若學校(Base)有學區ID而學校中間件無 ->補足
            List<string> schInBIRel = new List<string>();
            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<string>(queryText: $"SELECT VALUE c.id FROM c WHERE c.areaId='{areaId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("BIRel") }))
            {
                schInBIRel.Add(item);
            }
            await foreach (School school in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<School>(queryText: $"SELECT * FROM c WHERE c.areaId='{areaId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
            {
                string schId = school.id.ToString();
                if (!schInBIRel.Contains(schId))
                {
                    await BIStats.SetSchoolBIRelation(cosmosClient, blobClient, tableClient, _dingDing, school);
                }
            }
            //學校中間件資料取得
            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<BISchoolInfo>(queryText: $"select c.id,c.name,c.picture,c.areaId,c.areaName,c.size,c.scale,c.assists,c.sales,c.createDate,c.serial,c.service,c.hard from c where c.areaId='{areaId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("BIRel") }))
            {
                schoolInfos.Add(item);
            }
            if (schoolInfos.Count > 0)
                scIds = schoolInfos.Select(s => s.id).ToList();
            StringBuilder statsSql = new($"select value(c) from c where c.year={dateTime.Year}");
            if (scIds.Count > 0)
            {
                statsSql.Append($" and {BICommonWay.ManyScSql("c.id", scIds, $"{dateTime.Year}-")}");
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator<StatsInfo>(queryText: statsSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
                {
                    statsInfos.Add(item);
                }
            }
            StringBuilder lastStsSql = new($"select c.lesson,c.activity from c where c.year={dateTime.Year - 1}");
            List<LastYearLessAndAct> lastYear = new();
            if (scIds.Count > 0)
            {
                lastStsSql.Append($" and {BICommonWay.ManyScSql("c.id", scIds, $"{dateTime.Year - 1}-")}");
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator<LastYearLessAndAct>(queryText: lastStsSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
                {
                lastYear.Add(item);
                }
            }
            //本學區虛擬學校數取得
            string schcntSql = $"SELECT VALUE COUNT(c.id) FROM c WHERE c.areaId='{areaId}'";
            int sbCnt = await JointlySingleQuery.GetValueInt(cosmosClient, "School", "Base", schcntSql);
            int vrCnt = await JointlySingleQuery.GetValueInt(cosmosClient, "School", "VirtualBase", schcntSql);
            int schCnt = sbCnt + vrCnt;

            StatsInfo statsInfo = null;
            statsInfo = SchoolStatsWay.GetAreaStats(cosmosClient, _option, statsInfos, scIds, area);
            if (statsInfo != null)
            {
                //areaScStats = new
                //{
                //    statsInfo.id,
                //    statsInfo.schoolId,
                //    statsInfo.name,
                //    statsInfo.picture,
                //    statsInfo.areaId,
                //    weekSc = schoolInfos.Where(w => w.createDate >= weekS && w.createDate <= weekE).Count(),
                //    monthSc = schoolInfos.Where(w => w.createDate >= mthS && w.createDate <= mthE).Count(),
                //    statsInfo.tch,
                //    statsInfo.dayTch,
                //    statsInfo.weekTch,
                //    statsInfo.monthTch,
                //    statsInfo.stu,
                //    statsInfo.dayStu,
                //    statsInfo.weekStu,
                //    statsInfo.monthStu,
                //    statsInfo.room,
                //    statsInfo.witRoom,
                //    statsInfo.size,
                //    statsInfo.scCreateTime,
                //    statsInfo.upTime,
                //    lessStats = new
                //    {
                //        statsInfo.lesson.all,
                //        statsInfo.lesson.open,
                //        statsInfo.lesson.less,
                //        statsInfo.lesson.lastDay,
                //        statsInfo.lesson.day,
                //        statsInfo.lesson.lastWeek,
                //        statsInfo.lesson.week,
                //        statsInfo.lesson.lastTerm,
                //        statsInfo.lesson.term,
                //        statsInfo.lesson.lastDayInter,
                //        statsInfo.lesson.dayInter,
                //        statsInfo.lesson.lastMonthInter,
                //        statsInfo.lesson.monthInter,
                //        statsInfo.lesson.lastYearInter,
                //        statsInfo.lesson.yearInter,
                //        yearInters = TimeHelper.GetYearMonth(statsInfo.lesson.yearInters, dateTime.Year, dateTime.Month),
                //        lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList()).Sum()),
                //        year = TimeHelper.GetYearMonth(statsInfo.lesson.year, dateTime.Year, dateTime.Month)
                //    },

                //    actStats = new
                //    {
                //        statsInfo.activity.all,
                //        statsInfo.activity.exam,
                //        statsInfo.activity.survey,
                //        statsInfo.activity.vote,
                //        statsInfo.activity.homework,
                //        statsInfo.activity.lastDay,
                //        statsInfo.activity.dayCnt,
                //        statsInfo.activity.lastWeek,
                //        statsInfo.activity.week,
                //        statsInfo.activity.lastTerm,
                //        statsInfo.activity.term,
                //        lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.activity.year).Where(w => w.Count > 0).ToList()).Sum()),
                //        year = TimeHelper.GetYearMonth(statsInfo.activity.year, dateTime.Year, dateTime.Month)
                //    },
                //    srStats = new
                //    {
                //        statsInfo.study.learnTime,
                //        statsInfo.study.online,
                //        statsInfo.study.offline,
                //        statsInfo.study.classRoom,
                //        statsInfo.study.submit,
                //        statsInfo.study.notStarted,
                //        statsInfo.study.ongoing,
                //        statsInfo.study.finish
                //    }
                //};

                areaScStats.id = statsInfo.id;
                areaScStats.name = statsInfo.name;
                areaScStats.weekSc = schoolInfos.Where(w => w.createDate >= weekS && w.createDate <= weekE).Count();
                areaScStats.monthSc = schoolInfos.Where(w => w.createDate >= mthS && w.createDate <= mthE).Count();
                areaScStats.tch = statsInfo.tch;
                areaScStats.dayTch = statsInfo.dayTch;
                areaScStats.weekTch = statsInfo.weekTch;
                areaScStats.monthTch = statsInfo.monthTch;
                areaScStats.stu = statsInfo.stu;
                areaScStats.dayStu = statsInfo.dayStu;
                areaScStats.weekStu = statsInfo.weekStu;
                areaScStats.monthStu = statsInfo.monthStu;
                areaScStats.room = statsInfo.room;
                areaScStats.witRoom = statsInfo.witRoom;
                areaScStats.resourceCnt = statsInfo.resourceCnt;
                areaScStats.size = statsInfo.size;
                //areaScStats.useSize = statsInfo.useSize;
                areaScStats.lessStats.open = statsInfo.lesson.all;
                areaScStats.lessStats.open = statsInfo.lesson.open;
                areaScStats.lessStats.less = statsInfo.lesson.less;
                areaScStats.lessStats.lastDay = statsInfo.lesson.lastDay;
                areaScStats.lessStats.day = statsInfo.lesson.day;
                areaScStats.lessStats.lastWeek = statsInfo.lesson.lastWeek;
                areaScStats.lessStats.week = statsInfo.lesson.week;
                areaScStats.lessStats.lastTerm = statsInfo.lesson.lastTerm;
                areaScStats.lessStats.term = statsInfo.lesson.term;
                areaScStats.lessStats.lastDayInter = statsInfo.lesson.lastDayInter;
                areaScStats.lessStats.dayInter = statsInfo.lesson.dayInter;
                areaScStats.lessStats.lastMonthInter = statsInfo.lesson.lastMonthInter;
                areaScStats.lessStats.monthInter = statsInfo.lesson.monthInter;
                areaScStats.lessStats.lastYearInter = statsInfo.lesson.lastYearInter;
                areaScStats.lessStats.yearInter = statsInfo.lesson.yearInter;
                areaScStats.lessStats.yearInters = TimeHelper.GetYearMonth(statsInfo.lesson.yearInters, dateTime.Year, dateTime.Month);
                areaScStats.lessStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList()).Sum());
                areaScStats.lessStats.year = TimeHelper.GetYearMonth(statsInfo.lesson.year, dateTime.Year, dateTime.Month);
                areaScStats.actStats.all = statsInfo.activity.all;
                areaScStats.actStats.exam = statsInfo.activity.exam;
                areaScStats.actStats.survey = statsInfo.activity.survey;
                areaScStats.actStats.vote = statsInfo.activity.vote;
                areaScStats.actStats.homework = statsInfo.activity.homework;
                areaScStats.actStats.lastDay = statsInfo.activity.lastDay;
                areaScStats.actStats.dayCnt = statsInfo.activity.dayCnt;
                areaScStats.actStats.lastWeek = statsInfo.activity.lastWeek;
                areaScStats.actStats.week = statsInfo.activity.week;
                areaScStats.actStats.lastTerm = statsInfo.activity.lastTerm;
                areaScStats.actStats.term = statsInfo.activity.term;
                areaScStats.actStats.lastMonth = statsInfo.activity.lastMonth;
                areaScStats.actStats.month = statsInfo.activity.month;
                areaScStats.actStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.activity.year).Where(w => w.Count > 0).ToList()).Sum());
                areaScStats.actStats.year = TimeHelper.GetYearMonth(statsInfo.activity.year, dateTime.Year, dateTime.Month);
                if (statsInfo.study != null)
                {
                    areaScStats.srStats.learnTime = statsInfo.study.learnTime;
                    areaScStats.srStats.online = statsInfo.study.online;
                    areaScStats.srStats.offline = statsInfo.study.offline;
                    areaScStats.srStats.classRoom = statsInfo.study.classRoom;
                    areaScStats.srStats.submit = statsInfo.study.submit;
                    areaScStats.srStats.notStarted = statsInfo.study.notStarted;
                    areaScStats.srStats.ongoing = statsInfo.study.ongoing;
                    areaScStats.srStats.finish = statsInfo.study.finish;
                }
            }

            List<double> weekLess = BICommonWay.weekDoubleMerge(statsInfos.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList(), dateTime);
            schoolInfos.ForEach(fe =>
            {
                var tempSts = statsInfos.Find(f => f.schoolId.Equals(fe.id));
                if (tempSts != null)
                {
                    fe.less = tempSts.lesson.all;
                    fe.lastWeekLess = tempSts.lesson.lastWeek;
                    fe.monthLess = tempSts.lesson.month;
                }
            });

            List<IdInfo> assits = new();
            schoolInfos.ForEach(fe => assits.AddRange(fe.assists));
            List<IdInfo> saless = new();
            schoolInfos.ForEach(fe => saless.AddRange(fe.sales));
            foreach (var itemId in scIds)
            {
                RedisValue value = redisClinet.HashGet($"Blob:Record", itemId);
                if (value != default && !value.IsNullOrEmpty)
                {
                    JsonElement record = value.ToString().ToObject<JsonElement>();
                    if (record.TryGetInt64(out long blobsize)) 
                    {
                        useSize += blobsize;
                    }
                }

                SortedSetEntry[] Scores = redisClinet.SortedSetRangeByScoreWithScores($"Blob:Catalog:{itemId}");
                if (Scores.Length > 0)
                {
                    Dictionary<string, double?> schoolStics = new(); //学校空间
                    foreach (var score in Scores)
                    {
                        double val = score.Score;
                        string key = score.Element.ToString();
                        schoolStics.Add(key, val);
                    }
                    typeStics = typeStics.Concat(schoolStics).GroupBy(g => g.Key).ToDictionary(k => k.Key, k => k.Sum(kvp => kvp.Value));  //lamebda表达式

                }
            }

            return Ok(new { state = RespondCode.Ok, areaScStats, schCnt, schoolInfos, weekLess, assists = assits.Where((w, i) => assits.FindIndex(s => s.id.Equals(w.id)) == i).ToList(), saless = saless.Where((w, i) => saless.FindIndex(f => f.id.Equals(w.id)) == i).ToList(), useSize, typeStics = typeStics.ToList() });
        }

        /// <summary>
        /// 所有区级的统计
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-allscstats")]
        public async Task<IActionResult> GetAllAreaStats(JsonElement jsonElement)
        {
            var cosmosClient = _azureCosmos.GetCosmosClient();

            AllScStats allScStats = new();
            List<StatsInfo> statsInfos = new();
            DateTimeOffset dateTime = DateTimeOffset.UtcNow;
            var (lastDayS, lastdayE) = TimeHelper.GetStartOrEnd(dateTime.AddDays(-1));   //昨天开始时间
            var (dayS, dayE) = TimeHelper.GetStartOrEnd(dateTime);  //今天开始时间   

            var (lastWeekS, lastWeekE) = TimeHelper.GetStartOrEnd(dateTime, "lastweek");   //计算上周开始/结束时间
            var (weekS, weekE) = TimeHelper.GetStartOrEnd(dateTime, "week");             //计算本周开始/结束时间

            var (lastTermS, lastTermE) = TimeHelper.GetStartOrEnd(dateTime, "lastterm");   //计算上学期开始/结束时间
            var (termS, termE) = TimeHelper.GetStartOrEnd(dateTime, "term");   //计算本学期开始/结束时间

            var (lastMthS, LastmthE) = TimeHelper.GetStartOrEnd(dateTime, "lastMonth");  //上月开始/结束时间
            var (mthS, mthE) = TimeHelper.GetStartOrEnd(dateTime, "month");  //本月开始/结束时间

            var (lastYearS, lastYearE) = TimeHelper.GetStartOrEnd(DateTimeOffset.Parse($"{dateTime.Year - 1}-1-1"), "year"); //计算去年开始/结束时间
            var (yearS, yearE) = TimeHelper.GetStartOrEnd(dateTime, "year");         //计算今年开始/结束时间

            string sql = "select value(count(c.id)) from c ";
            List<EasyInfo> easyInfos = new();
            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Normal").GetItemQueryIterator<EasyInfo>(queryText: $"select c.id,c.code,c.name from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base-Area") }))
            {
                easyInfos.Add(item);
            }

            int scCnt = await JointlySingleQuery.GetValueInt(cosmosClient, "School", "Base", sql);
            int vrCnt = await JointlySingleQuery.GetValueInt(cosmosClient, "School", "VirtualBase", sql);
            int scWeekCnt = await JointlySingleQuery.GetValueInt(cosmosClient, "Normal", "Base", $"{sql} where c.createTime >= {weekS} and c.createTime <= {weekE}");
            int scMonthCnt = await JointlySingleQuery.GetValueInt(cosmosClient, "Normal", "Base", $"{sql} where c.createTime >= {mthS} and c.createTime <= {mthE}");
            List<string> scId = new();
            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator<StatsInfo>(queryText: $"select value(c) from c where c.year={dateTime.Year}", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
            {
                statsInfos.Add(item);
                if (item.activity.year.Count > 366) 
                {
                    scId.Add(item.id);
                }
            }
            List<AreaInfoStats> areaGroup = statsInfos.GroupBy(p => p.areaId).Select(s => new AreaInfoStats() { areaId = s.Key, name = easyInfos.Where(f => f.id.Equals(s.Key)).Select(sn=>sn.name).FirstOrDefault(), lessAct = (s.Select(sl => sl.lesson.all).Sum() + s.Select(sa => sa.activity.all).Sum()), lessCnt = s.Select(sl => sl.lesson.all).Sum(), actCnt = s.Select(sa => sa.activity.all).Sum() }).ToList();

            StatsInfo statsInfo = null;
            statsInfo = SchoolStatsWay.GetAreaStats(cosmosClient, _option, statsInfos);
            if (statsInfo != null)
            {
                allScStats.areaCnt = easyInfos.Count;
                //allScStats.sc = statsInfos.Count;
                allScStats.weekSc = scWeekCnt;
                allScStats.monthSc = scMonthCnt;
                allScStats.tch = statsInfo.tch;
                allScStats.dayTch = statsInfo.dayTch;
                allScStats.weekTch = statsInfo.weekTch;
                allScStats.monthTch = statsInfo.monthTch;
                allScStats.stu = statsInfo.stu;
                allScStats.dayStu = statsInfo.dayStu;
                allScStats.weekStu = statsInfo.weekStu;
                allScStats.monthStu = statsInfo.monthStu;
                allScStats.room = statsInfo.room;
                allScStats.witRoom = statsInfo.witRoom;
                allScStats.resourceCnt = statsInfo.resourceCnt;
                allScStats.size = statsInfo.size;
                allScStats.scCreateTime = statsInfo.scCreateTime;
                allScStats.upTime = statsInfo.upTime;
                allScStats.lessStats.open = statsInfo.lesson.all;
                allScStats.lessStats.open = statsInfo.lesson.open;
                allScStats.lessStats.less = statsInfo.lesson.less;
                allScStats.lessStats.lastDay = statsInfo.lesson.lastDay;
                allScStats.lessStats.day = statsInfo.lesson.day;
                allScStats.lessStats.lastWeek = statsInfo.lesson.lastWeek;
                allScStats.lessStats.week = statsInfo.lesson.week;
                allScStats.lessStats.lastTerm = statsInfo.lesson.lastTerm;
                allScStats.lessStats.term = statsInfo.lesson.term;
                allScStats.lessStats.lastDayInter = statsInfo.lesson.lastDayInter;
                allScStats.lessStats.dayInter = statsInfo.lesson.dayInter;
                allScStats.lessStats.lastMonthInter = statsInfo.lesson.lastMonthInter;
                allScStats.lessStats.monthInter = statsInfo.lesson.monthInter;
                allScStats.lessStats.lastYearInter = statsInfo.lesson.lastYearInter;
                allScStats.lessStats.yearInter = statsInfo.lesson.yearInter;
                allScStats.actStats.all = statsInfo.activity.all;
                allScStats.actStats.exam = statsInfo.activity.exam;
                allScStats.actStats.survey = statsInfo.activity.survey;
                allScStats.actStats.vote = statsInfo.activity.vote;
                allScStats.actStats.homework = statsInfo.activity.homework;
                allScStats.actStats.lastDay = statsInfo.activity.lastDay;
                allScStats.actStats.dayCnt = statsInfo.activity.dayCnt;
                allScStats.actStats.lastWeek = statsInfo.activity.lastWeek;
                allScStats.actStats.week = statsInfo.activity.week;
                allScStats.actStats.lastTerm = statsInfo.activity.lastTerm;
                allScStats.actStats.term = statsInfo.activity.term;
                allScStats.actStats.lastMonth = statsInfo.activity.lastMonth;
                allScStats.actStats.month = statsInfo.activity.month;
                if (statsInfo.study != null)
                {
                    allScStats.srStats.learnTime = statsInfo.study.learnTime;
                    allScStats.srStats.online = statsInfo.study.online;
                    allScStats.srStats.offline = statsInfo.study.offline;
                    allScStats.srStats.classRoom = statsInfo.study.classRoom;
                    allScStats.srStats.submit = statsInfo.study.submit;
                    allScStats.srStats.notStarted = statsInfo.study.notStarted;
                    allScStats.srStats.ongoing = statsInfo.study.ongoing;
                    allScStats.srStats.finish = statsInfo.study.finish;
                }
            }
            allScStats.sc = scCnt + vrCnt; //學校數

            return Ok(new { state = RespondCode.Ok, allScStats, areaGroup });
        }

        #endregion   新的统计接口

        /// <summary>
        /// 学校课例
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-leseoncnt")]
        public async Task<IActionResult> GetLessonCnt(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("code", out JsonElement code)) return BadRequest();
            List<LessonRecord> records = new();

            DateTimeOffset dateTime = DateTimeOffset.UtcNow;
            var (dayS, dayE) = TimeHelper.GetStartOrEnd(dateTime);  //今天开始时间   
            var (weekS, weekE) = TimeHelper.GetStartOrEnd(dateTime, "week");             //计算本周开始/结束时间
            var (monthS, monthE) = TimeHelper.GetStartOrEnd(dateTime, "month");  //本月开始/结束时间

            var client = _azureCosmos.GetCosmosClient();
            await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<LessonRecord>(queryText: "select value(c) from c ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"LessonRecord-{code}") }))
            {
                records.Add(item);
            }
            double itemCount = 0;
            await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"SELECT count(1) as items FROM c where c.scope = 'school' ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Item-{code}") }))
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                {
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        if (obj.TryGetProperty("items", out JsonElement items))
                        {
                            itemCount = items.GetDouble();
                        }
                    }
                }
            }

            double paperCount = 0;
            await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"SELECT count(1) as papers FROM c where c.scope = 'school' ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Paper-{code}") }))
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                {
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        if (obj.TryGetProperty("papers", out JsonElement papers))
                        {
                            paperCount = papers.GetDouble();
                        }
                    }
                }
            }

            double resourcesCount = itemCount + paperCount; //线上资源
            double attendCount = records.Select(c => c.attendCount).Sum(); //学生参加人数
            double clientInteractionCount = records.Select(c => c.clientInteractionCount).Sum(); //互动总次数
            double workCount = records.Select(c => c.collateCount).Sum();//课堂任务总次数
            double interactionCount = records.Select(c => c.interactionCount).Sum(); //课堂提问中题数
            double duration = records.Select(c => c.duration).Sum(); //课堂总时长
            double dayCnt = records.Where(c => c.startTime >= dayS && c.startTime <= dayE).Count(); //今天课例数
            double weekCnt = records.Where(c => c.startTime >= weekS && c.startTime <= weekE).Count(); //本周课例数
            double monthCnt = records.Where(c => c.startTime >= monthS && c.startTime <= monthE).Count(); //本月课例数


            return Ok(new { state = RespondCode.Ok, cnt = records.Count, dayCnt, weekCnt, monthCnt, resourcesCount, attendCount, clientInteractionCount, workCount, interactionCount, duration });
        }

        /// <summary>
        /// 学校信息中间件查询接口
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-scinfos")]
        public async Task<IActionResult> GetScInfos(JsonElement jsonElement)
        {
            var cosmosClient = _azureCosmos.GetCosmosClient();
            jsonElement.TryGetProperty("role", out JsonElement role);
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            jsonElement.TryGetProperty("areaId", out JsonElement areaId);

            //IES5實體學校
            StringBuilder sql = new($"select value(c) from c");

            if (!string.IsNullOrEmpty($"{role}") && !string.IsNullOrEmpty($"{tmdId}") && string.IsNullOrEmpty($"{areaId}"))
            {
                switch ($"{role}")
                {
                    case "assist":
                        sql.Append($" join a in c.assist where a.id='{tmdId}'");
                        break;
                    case "sales":
                        sql.Append($" join a in c.sales where a.id='{tmdId}'");
                        break;
                }
            }

            if (!string.IsNullOrEmpty($"{areaId}") && !string.IsNullOrEmpty($"{role}"))
            {
                sql.Append($" where c.areaId ='{areaId}'");
            }

            //List<string> scId = await CommonFind.FindScIds(cosmosClient, "select value(c.id) from c ", "Base");
            //取得學校BI統計中間件
            List<BIRelation> scInfos = new();
            List<string> schIds = new();
            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<BIRelation>(queryText: sql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("BIRel") }))
            {
                scInfos.Add(item);
                schIds.Add(item.id);
            }
            //取得學校產品列表
            if(schIds.Count > 0)
            {
                string schIdListStr = JsonSerializer.Serialize(schIds);
                StringBuilder sqlPSum = new($"SELECT * FROM c WHERE ARRAY_CONTAINS({schIdListStr}, c.id, true)");
                await foreach (SchoolProductSum item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<SchoolProductSum>(queryText: sqlPSum.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("ProductSum") }))
                {
                    List<string> serialProdList = item.serial.Select(s => s.prodCode).ToList();
                    List<string> serviceProdList = item.service.Select(s => s.prodCode).ToList();
                    List<string> hardProdList = item.hard.Select(s => s.prodCode).ToList();
                    BIRelation scInfo = scInfos.Where(s => s.id.Equals(item.id)).FirstOrDefault();
                    if(scInfo != null)
                    {
                        scInfo.serial = serialProdList;
                        scInfo.service = serviceProdList;
                        scInfo.hard = hardProdList;
                    }
                }
            }

            //虛擬學校
            ////取得虛擬學校學區名稱
            List<string> vrAreaIdList = new List<string>();
            StringBuilder sqlVrArea = new($"SELECT DISTINCT VALUE c.areaId FROM c WHERE IS_DEFINED(c.areaId) AND NOT IS_NULL(c.areaId) AND c.areaId != ''");
            await foreach (string item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<string>(queryText: sqlVrArea.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("VirtualBase") }))
            {
                vrAreaIdList.Add(item);
            }
            Dictionary<string, string> areaDic = new Dictionary<string, string>();
            string vrAreaListStr = JsonSerializer.Serialize(vrAreaIdList);
            StringBuilder sqlArea = new($"SELECT c.id, c.name FROM c WHERE ARRAY_CONTAINS({vrAreaListStr}, c.id, true)");
            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Normal").GetItemQueryStreamIterator(queryText: sqlArea.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base-Area") }))
            {
                var json = await JsonDocument.ParseAsync(item.ContentStream);
                foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                {
                    string arId = obj.GetProperty("id").GetString();
                    string arName = obj.GetProperty("name").GetString();
                    areaDic.Add(arId, arName);
                }
            }
            //取得虛擬學校資料
            StringBuilder sqlV = new($"select value(c) from c");
            if (!string.IsNullOrEmpty($"{areaId}") && !string.IsNullOrEmpty($"{role}"))
            {
                sqlV.Append($" where c.areaId ='{areaId}'");
            }
            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<BIRelation>(queryText: sqlV.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("VirtualBase") }))
            {
                if(!string.IsNullOrWhiteSpace(item.areaId) && areaDic.ContainsKey(item.areaId))
                {
                    item.areaName = areaDic[item.areaId];
                }
                scInfos.Add(item);
            }

            return Ok(new { state = RespondCode.Ok, allCnt = scInfos.Count, scInfos });
        }


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

    }
}