TEAMModelOS/TEAMModelBI/Controllers/Census/ActivitySticsController.cs

using Azure.Cosmos;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using TEAMModelOS.Models;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Models;
using System.Text.Json;
using TEAMModelBI.Tool;
using System.Text;
using TEAMModelOS.SDK.Extension;
using TEAMModelBI.Models;
using TEAMModelOS.SDK;
using TEAMModelBI.Tool.CosmosBank;
using TEAMModelOS.SDK.Models.Cosmos.BI;
using TEAMModelOS.SDK.Context.BI;
using TEAMModelOS.SDK.Context.Constant;
using TEAMModelOS.SDK.Models.Service.BI;

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

        public ActivitySticsController(AzureCosmosFactory azureCosmos, AzureStorageFactory azureStorage, DingDing dingDing, IOptionsSnapshot<Option> option, CoreAPIHttpService coreAPIHttpService)
        {
            _azureCosmos = azureCosmos;
            _dingDing = dingDing;
            _azureStorage = azureStorage;
            _option = option?.Value;
            _coreAPIHttpService = coreAPIHttpService;
        }

        /// <summary>
        /// 统计活动 传醍摩豆则查询相关的学校活动
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-count")]
        public async Task<IActionResult> GetCount(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            if (!jsonElement.TryGetProperty("term", out JsonElement term)) return BadRequest();
            jsonElement.TryGetProperty("site", out JsonElement site);
            long start = 0, end = 0;
            if (bool.Parse($"{term}") == true)
            {
                (start, end) = TimeHelper.GetTermStartOrEnd(DateTime.Now);
            }
            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            List<object> activityCount = new List<object>();

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

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

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

                    foreach (var type in StaticValue.activityTypes)
                    {
                        StringBuilder sqlTxt = new StringBuilder($"select COUNT(c.id) AS totals from c where c.pk='{type}' and c.school='{itemId}' ");
                        if (bool.Parse($"{term}") == true)
                        {
                            sqlTxt.Append($" and c.createTime >= {start} and c.createTime <= {end}");
                        }
                        long totals = await CommonFind.FindTotals(cosmosClient, sqlTxt.ToString(), new List<string>() { "Common" });

                        tempCount.census.Add(new KeyValuePair<object, long>(type, totals));
                    }
                    activityCount.Add(tempCount);
                }
            }
            else
            {
                foreach (var type in StaticValue.activityTypes)
                {
                    StringBuilder sqlTxt = new StringBuilder($"SELECT COUNT(c.id) AS totals FROM c where c.pk='{type}' ");
                    if (bool.Parse($"{term}") == true)
                    {
                        sqlTxt.Append($" and c.createTime >= {start} and c.createTime <= {end}");
                    }

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

                    activityCount.Add(new KeyValuePair<string, object>(type, totals));
                }
            }

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

        /// <summary>
        /// 统计所有的评量活动，问卷调查，投票活动，作业
        /// </summary>
        /// <returns></returns>
        [HttpPost("get-allactivity")]
        public async Task<IActionResult> GetAllActivity(JsonElement jsonElement)
        {
            try
            {
                jsonElement.TryGetProperty("areaId", out JsonElement areaId);
                jsonElement.TryGetProperty("site", out JsonElement site);
                var cosmosClient = _azureCosmos.GetCosmosClient();
                if ($"{site}".Equals(BIConst.Global))
                    cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
                List<KeyValuePair<string, long>> typeCount = new();

                string inSql = null;
                List<string> scIds = new();

                if (!string.IsNullOrEmpty($"{areaId}"))
                {
                    string areaSc = $"select value(c.id) from c where c.pk='School' and c.areaId='{areaId}'";
                    await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<string>(queryText: areaSc, requestOptions: new QueryRequestOptions() { })) 
                    {
                        scIds.Add(item);
                    }

                    inSql = $" and {BICommonWay.ManyScSql("c.school", scIds)}";
                }

                foreach (var type in StaticValue.activityTypes)
                {
                    string querySql = $"SELECT value(count(c.id)) FROM c where c.pk='{type}' ";
                    long totals = 0;
                    if (!string.IsNullOrEmpty($"{areaId}"))
                    {
                        if (!string.IsNullOrEmpty(inSql) && scIds.Count > 0)
                        {
                            querySql = $"{querySql}{inSql}";
                            totals = await CommonFind.GetSqlValueCount(cosmosClient, "Common", querySql);
                        }
                    }
                    else 
                        totals = await CommonFind.GetSqlValueCount(cosmosClient, "Common", querySql);

                    KeyValuePair<string, long> valuePair = new(type, totals);
                    typeCount.Add(valuePair);
                }

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

        /// <summary>
        /// 统计顾问关联的学校活动数量：评测、问卷、投票、作业
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-assistactivity")]
        public async Task<IActionResult> GetAssistSchoolActivity(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return BadRequest();
            jsonElement.TryGetProperty("site", out JsonElement site);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

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

            List<ActivityCount> activityCounts = new List<ActivityCount>();
            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>();
                }
                ActivityCount activityCount = new ActivityCount() { id = itemId, name = school.name != null ? school.name : itemId };
                foreach (var type in StaticValue.activityTypes)
                {
                    string activitySql = $"SELECT Count(c.id) as totals FROM c WHERE c.pk='{type}' AND c.school='{itemId}'";
                    long totals = await CommonFind.FindTotals(cosmosClient, activitySql, new List<string>() { "Common" });

                    activityCount.census.Add(new KeyValuePair<object, long>(type, totals));
                }

                activityCounts.Add(activityCount);
            }

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

        /// <summary>
        /// 统计当前学期的活动,传醍摩豆账户则统计该账户当前学期
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-termactivity")]
        public async Task<IActionResult> GetTermActivity(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
            jsonElement.TryGetProperty("site", out JsonElement site);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

            var (start, end) = TimeHelper.GetTermStartOrEnd(DateTime.Now);

            if (!string.IsNullOrEmpty($"{tmdId}"))
            {
                List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");
                List<ActivityCount> activityCounts = new List<ActivityCount>();
                foreach (var schoolId in schoolIds)
                {
                    School school = new();
                    var response = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(schoolId, new PartitionKey("Base"));
                    if (response.Status == 200)
                    {
                        using var json = await JsonDocument.ParseAsync(response.ContentStream);
                        school = json.ToObject<School>();
                    }
                    ActivityCount activityCount = new ActivityCount() { id = schoolId, name = school.name != null ? school.name : schoolId };

                    foreach (var type in StaticValue.activityTypes)
                    {
                        string activitySql = $"SELECT COUNT(c.id) AS totals  FROM c WHERE c.pk='{type}' AND c.school='{school}' and c.createTime >= {start} and c.createTime <= {end}";
                        long totals = await CommonFind.FindTotals(cosmosClient, activitySql, new List<string>() { "Common" });

                        activityCount.census.Add(new KeyValuePair<object, long>(type, totals));
                    }
                    activityCounts.Add(activityCount);
                }

                return Ok(new { state = 200, activityCounts });
            }
            else
            {
                List<KeyValuePair<string, object>> typeCount = new List<KeyValuePair<string, object>>();
                foreach (var type in StaticValue.activityTypes)
                {
                    string querySql = $"SELECT COUNT(c.id) AS totals FROM c where c.pk='{type}' and c.createTime >= {start} and c.createTime <= {end}";
                    long totals = await CommonFind.FindTotals(cosmosClient, querySql, new List<string>() { "Common" });

                    KeyValuePair<string, object> valuePair = new KeyValuePair<string, object>(type, totals);
                    typeCount.Add(valuePair);
                }

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

        /// <summary>
        /// 统计区域的活动
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-area")]
        public async Task<IActionResult> GetAreaActovoty(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("areaId", out JsonElement areaId)) return BadRequest();
            jsonElement.TryGetProperty("site", out JsonElement site);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            List<RecSchool> schools = new();

            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<RecSchool>(queryText: $"select c.id,c.name,c.picture,c.scale from c where c.areaId='{areaId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
            {
                schools.Add(item);
            }

            long exemAreaCount = 0;  //评测活动
            long surveyAreaCount = 0;  //问卷
            long voteAreaCount = 0;   //投票
            long homeworkAreaCount = 0;   //作业活动

            List<ActivityCount> activityCount = new();
            foreach (var school in schools)
            {

                ActivityCount tempCount = new ActivityCount() { id = school.id, name = school.name != null ? school.name : school.id };
                foreach (var type in StaticValue.activityTypes)
                {
                    StringBuilder sqlTxt = new($"select COUNT(c.id) AS totals from c where c.pk='{type}' and c.school='{school.id}' ");
                    long totals = await CommonFind.FindTotals(cosmosClient, sqlTxt.ToString(), new List<string>() { "Common" });

                    switch (type)
                    {
                        case "Exam":
                            exemAreaCount += totals;
                            break;
                        case "Survey":
                            surveyAreaCount += totals;
                            break;
                        case "Vote":
                            voteAreaCount += totals;
                            break;
                        case "Homework":
                            homeworkAreaCount += totals;
                            break;
                    }

                    tempCount.census.Add(new KeyValuePair<object, long>(type, totals));
                }
                activityCount.Add(tempCount);
            }

            return Ok(new { state = 200, exemAreaCount, surveyAreaCount, voteAreaCount, homeworkAreaCount, activityCount });
        }

        /// <summary>
        /// 依据区统计区级相关信息包括活动统计
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-areastics")]
        public async Task<IActionResult> GetAreaStics(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("areaId", out JsonElement areaId)) return BadRequest();
            jsonElement.TryGetProperty("site", out JsonElement site);
            var (dayStart, dayEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow);
            var (weekStart, weekEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "week");
            var (monthStart, monthEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "month");
            var (termStart, termEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "term");

            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            List<RecSchool> schools = new();


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

            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<RecSchool>(queryText: scSqlTxt.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
            {
                schools.Add(item);
            }

            int countArea = 0;//区级人员
            int appraiseArea = 0;//区级评审人员
            int areaSize = 0;   //区级空间
            long examAreaCount = 0;   //试卷活动
            long surveyAreaCount = 0;  //问卷活动
            long voteAreaCount = 0;   //投票活动
            long homeworkAreaCount = 0;  //作业活动
            long weekActivity = 0; //周活动数量
            long termActivity = 0;   //学期活动
            int basics = 0; //基础版数
            int standard = 0; //标准版数
            int major = 0;   //专业版数
            int geCount = 0; //普教
            int heCount = 0; //高教
            int oeCount = 0; //其他教育
            int allLess = 0; //所有课例
            int dayLess = 0;  //当天课例
            int weekLess = 0;  //周课例
            int monthLess = 0;  //月课例
            int termLess = 0;    //学期课例
            double teachCount = 0;    //课例教师

            List<SchoolLesson> schoolLessons = new();  //学校课例集合 
            List<SchoolInfo> schoolInfos = new();
            List<LessonRecord> records = new();//所有的课程记录

            List<string> scIds = schools.Select(x => x.id).ToList();

            int totalTime = 0;

            heCount = schools.Select(s => s.type == 2).Count();
            geCount = schools.Select(s => s.type == 1).Count();
            oeCount = schools.Select(s => s.type != 2 && s.type != 1).Count();
            areaSize = schools.Select(s => s.size).Sum();

            foreach (var school in schools)
            {
                int count = 0;
                int appraise = 0;
                await foreach (var info in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<bool>(queryText: $"select value(array_contains(c.permissions,'train-appraise')) from c", requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey($"Teacher-{school.id}") }))
                {
                    if (info)
                    {
                        appraise += 1;
                        appraiseArea += 1;
                    }
                    countArea += 1;
                    count += 1;
                }

                //查询学校的总学时
                totalTime += await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", "SELECT value(sum(c.totalTime)) FROM c", $"TeacherTrain-{school.id}");
                //查询学校参训人数
                int tempCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"select value(array_length(c.members)) from c where c.type='yxtrain'", $"GroupList-{school.id}");
                //学校学生人数
                long stuCount = await CommonFind.GetSqlValueCount(cosmosClient, "Student", $"select value(count(c.id)) from c", $"Base-{school.id}");

                //查询是否有服务
                int serCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"select value(array_length(c.service)) from c where c.id='{school.id}'", "ProductSum");
                if (serCount > 0)
                    major += 1;
                else if (school.size >= 300 && school.scale >= 500)
                    standard += 1;
                else basics += 1;

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

                //课例
                int lessCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", "select value(count(c.id)) from c", $"LessonRecord-{school.id}");
                SchoolLesson schoolLesson = new()
                {
                    id = school.id,
                    name = school.name,
                    picture = school.picture,
                    count = lessCount
                };

                SchoolInfo schoolInfo = new()
                {
                    id = school.id,
                    name = school.name,
                    picture = school.picture,
                    teacherCount = count,
                    studentCount = stuCount,
                    appraiseCount = appraise,
                    trainCount = tempCount
                };

                ActivityCount tempActivity = new() { id = school.id, name = school.name != null ? school.name : school.id };
                foreach (var type in StaticValue.activityTypes)
                {
                    long totals = await CommonFind.GetSqlValueCount(cosmosClient, "Common", $"select value(COUNT(c.id)) from c where c.pk='{type}' and c.school='{school.id}' ");

                    string weekSql = $"select value(count(c.id)) from c where c.pk='{type}' and c.school='{school.id}' and c.createTime>={weekStart}   and c.createTime<={weekEnd}";
                    long weekActCount = await CommonFind.GetSqlValueCount(cosmosClient, "Common", weekSql);
                    weekActivity += weekActCount;

                    string termSql = $"select value(count(c.id)) from c where c.pk='{type}' and c.school='{school.id}' and c.createTime>={termStart}   and c.createTime<={termEnd}";
                    long termActCount = await CommonFind.GetSqlValueCount(cosmosClient, "Common", termSql);
                    termActivity += termActCount;

                    switch (type)
                    {
                        case "Exam":
                            examAreaCount += totals;
                            break;
                        case "Survey":
                            surveyAreaCount += totals;
                            break;
                        case "Vote":
                            voteAreaCount += totals;
                            break;
                        case "Homework":
                            homeworkAreaCount += totals;
                            break;
                    }
                    schoolInfo.census.Add(new KeyValuePair<object, long>(type, totals));
                }
                schoolInfos.Add(schoolInfo);
                schoolLessons.Add(schoolLesson);
            }

            List<string> tecIds = await CommonFind.FindRolesId(cosmosClient, scIds);

            //查询去下面所有学校教师课例
            foreach (var tecId in tecIds)
            {
                string sqlTxt = $"select value(c) from c where c.id='{tecId}'";
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryIterator<LessonRecord>(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"LessonRecord") }))
                {
                    records.Add(item);
                }
            }

            //课例统计
            if (records.Count > 0)
            {
                records.ForEach(x => { if (x.startTime >= dayStart && x.startTime <= dayEnd) dayLess += 1; });
                records.ForEach(x => { if (x.startTime >= weekStart && x.startTime <= weekEnd) weekLess += 1; });
                records.ForEach(x => { if (x.startTime >= monthStart && x.startTime <= monthEnd) monthLess += 1; });
                records.ForEach(x => { if (x.startTime >= termStart && x.startTime <= termEnd) termLess += 1; });
                teachCount = records.Where(r => r.tmdid != null).Where((x, i) => records.FindIndex(z => z.tmdid == x.tmdid) == i).ToList().Count;
            }

            return Ok(new { state = 200, schoolCount = schools.Count, countArea, weekActivity, termActivity, totalTime, appraiseArea, examAreaCount, surveyAreaCount, voteAreaCount, homeworkAreaCount, major, standard, basics, oeCount, dayLess, weekLess, monthLess, termLess, teachCount, allLess = records.Count, schools = schoolInfos, schoolLessons });
        }

        /// <summary>
        /// 所有区的统计接口 学区情况
        /// </summary>
        /// <returns></returns>
        [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);

            var (weekStart, weekEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "week");
            var (termStart, termEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "term");
            var (monthS, monthE) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "month");

            int areaCount = 0;  //区域数量
            int scCount = 0;    //学校数量
            int tecCount = 0;    //教师数量
            int stuCount = 0;   //学生数量
            int allSize = 0;   //空间总量

            int weekScCnt = 0;  //本周学校
            int weekTchCnt = 0;  //本周教师
            int monthScCnt = 0;  //本月学校
            int monthTchCnt = 0;  //本月教师

            int heCount = 0;//高教
            int geCount = 0;//普教
            int oeCount = 0; //其他教育

            int allActivity = 0;  //活动累计
            int weekActivity = 0;//本周活动
            int termActivity = 0;//本学期活动
            int monthActCnt = 0; //本月活动

            int weekLess = 0;      //本周课例
            int termLess = 0;     //本学期课例
            int allLess = 0;     //所有课例
            int monthLesCnt = 0;  //本月课例

            int resourceCount = 0; //累计资源
            int totalTime = 0; //总学时

            string commSql = "select value(count(c.id)) from c";

            areaCount = await CommonFind.GetSqlValueCount(cosmosClient, "Normal", commSql, "Base-Area");
            scCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", commSql,  "Base");
            tecCount = await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", commSql,"Base");
            stuCount = await CommonFind.GetSqlValueCount(cosmosClient, "Student", commSql,  "Base");
            allSize = await CommonFind.GetSqlValueCount(cosmosClient, "School", commSql,  "Base");


            weekScCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{commSql} where c.createTime >= {termStart} and c.createTime >= {termEnd}", "Base");
            monthScCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{commSql} where c.createTime >= {monthS} and c.createTime >= {monthE}", "Base");

            weekTchCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", $"{commSql} where c.createTime >= {termStart} and c.createTime >= {termEnd}", "Base");
            monthTchCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", $"{commSql} where c.createTime >= {monthS} and c.createTime >= {monthE}", "Base");

            heCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{commSql} where c.type = 1", "Base");
            geCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{commSql} where c.type = 2", "Base");
            oeCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{commSql} where c.type != 1 and c.type != 2", "Base");

            //活动
            allActivity = await ActivityWay.GetCnt(cosmosClient);

            string weekSql = $" and c.createTime >= {weekStart}  and c.createTime <={weekEnd}";
            weekActivity = await ActivityWay.GetCnt(cosmosClient, weekSql);

            string termSql = $" and c.createTime >= {termStart}  and c.createTime <={termEnd}";
            termActivity = await ActivityWay.GetCnt(cosmosClient, termSql);

            string monthActSql = $" and c.createTime >= {monthS}  and c.createTime <={monthE}";
            monthActCnt = await ActivityWay.GetCnt(cosmosClient, termSql);

            //课例
            string allLessSql = $"select value(count(c.id)) from c where c.pk='LessonRecord'";
            allLess = await CommonFind.GetSqlValueCount(cosmosClient, "School", allLessSql);

            string weekLSql = $"{allLessSql} and c.startTime >= {weekStart}  and c.startTime <={weekEnd}";
            weekLess = await CommonFind.GetSqlValueCount(cosmosClient, "School", weekLSql);

            string termLSql = $"{allLessSql} and c.startTime >= {termStart}  and c.startTime <={termEnd}";
            termLess = await CommonFind.GetSqlValueCount(cosmosClient, "School", termLSql);

            string monthLSql = $"{allLessSql} and c.startTime >= {monthS}  and c.startTime <={monthE}";
            monthLesCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", termLSql);

            totalTime = await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", "select value(sum(c.totalTime)) from c where c.pk='TeacherTrain'");
            resourceCount = await CommonFind.GetSqlValueCount(cosmosClient, new List<string>() { "School", "Teacher" }, "select value(count(c.id)) from c where c.pk='Bloblog'");

            return Ok(new { state = RespondCode.Ok, areaCount, scCount, tecCount, stuCount, allSize, weekScCnt, weekTchCnt, monthScCnt, monthTchCnt, heCount, geCount, oeCount, allActivity, weekActivity, termActivity, monthActCnt, weekLess, termLess, allLess, monthLesCnt, resourceCount, totalTime });
        }

        /// <summary>
        /// 分析所有区的人数和学校  学区情况
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-areasanls")]
        public async Task<IActionResult> GetAreasAnls(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("site", out JsonElement site);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

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

            foreach (var area in areaInfos)
            {
                string scSql = $"select value(c.id) from c where c.areaId='{area.id}'";
                List<string> scIds = await CommonFind.GetValueSingle(cosmosClient, "School", scSql,"Base");
                //allSize += recSchools.Select(s => s.size).Sum();
                area.scCnt = scIds.Count;
                int tTchCnt = 0;
                int tStuCnt = 0;
                if (scIds.Count > 0)
                {
                    scSql = BICommonWay.ManyScSql("REPLACE(c.code, 'Teacher-', '')", scIds);

                    //查教师
                    string tchSql = $"select value(count(c.id)) from c where c.pk='Teacher' and {scSql}";
                    tTchCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", tchSql);

                    scSql = BICommonWay.ManyScSql("REPLACE(c.code, 'Base-', '')", scIds);
                    //查学生
                    string stuSql = $"select value(count(c.id)) from c where c.pk='Base' and {scSql}";
                    tStuCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Student", stuSql);
                }
                area.tchCnt = tTchCnt;
                area.stuCnt = tStuCnt;
            }

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


        /// <summary>
        /// 所有活动分析
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-allanls")]
        public async Task<IActionResult> GetAllAnls(JsonElement jsonElement) 
        {
            jsonElement.TryGetProperty("site", out JsonElement site);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            DateTimeOffset dateTime = DateTimeOffset.UtcNow;
            var (weeks, weeke) = TimeHelper.GetStartOrEnd(dateTime, "week");
            var (terms, terme) = TimeHelper.GetStartOrEnd(dateTime, "term");
            var (months, monthe) = TimeHelper.GetStartOrEnd(dateTime, "month");

            int allLess = 0;     //所有课例
            int weekLess = 0;      //本周课例
            int termLess = 0;     //本学期课例
            int monthLessCnt = 0;  //本月课例

            int actAllCnt = 0;  //所有活动
            int actWeekCnt = 0; //本周活动
            int actTermCnt = 0; //本学期活动
            int actMonthCnt = 0; //本月活动

            //课例
            string allLessSql = $"select value(count(c.id)) from c where c.pk='LessonRecord'";
            allLess = await CommonFind.GetSqlValueCount(cosmosClient,"School", allLessSql);

            string weekLSql = $"{allLessSql} and c.startTime >= {weeks}  and c.startTime <={weeke}";
            weekLess = await CommonFind.GetSqlValueCount(cosmosClient, "School", weekLSql);

            string termLSql = $"{allLessSql} and c.startTime >= {terms}  and c.startTime <={terme}";
            termLess = await CommonFind.GetSqlValueCount(cosmosClient, "School", termLSql);

            string monthLSql = $"{allLessSql} and c.startTime >= {months}  and c.startTime <={monthe}";
            monthLessCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", monthLSql);

            //活动
            actAllCnt = await ActivityWay.GetCnt(cosmosClient);

            string weekSql = $" and c.createTime >= {weeks}  and c.createTime <={weeke}";
            actWeekCnt = await ActivityWay.GetCnt(cosmosClient, weekSql);

            string termSql = $" and c.createTime >= {terms}  and c.createTime <={terme}";
            actTermCnt = await ActivityWay.GetCnt(cosmosClient,termSql);

            string monthSql = $" and c.createTime >= {months}  and c.createTime <={monthe}";
            actMonthCnt = await ActivityWay.GetCnt(cosmosClient, monthSql);

            return Ok(new {state = RespondCode.Ok, allLess, weekLess, termLess, monthLessCnt, actAllCnt, actWeekCnt, actTermCnt, actMonthCnt });
        }



        /// <summary>
        /// 依据活动Id查询活动详情信息  数据管理工具——查询工具
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-info")]
        public async Task<IActionResult> GetInfo(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("id", out JsonElement id)) return BadRequest();
            jsonElement.TryGetProperty("activity", out JsonElement activity);
            jsonElement.TryGetProperty("isPersonal", out JsonElement isPersonal);
            jsonElement.TryGetProperty("site", out JsonElement site);
            //if (jsonElement.TryGetProperty("", out JsonElement code)) return BadRequest();

            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
            List<object> infos = new List<object>();
            StringBuilder sqlTxt = new StringBuilder($"select * from c where c.id='{id}'");
            if (!string.IsNullOrEmpty($"{activity}"))
            {
                sqlTxt.Append($" and c.pk='{activity}'");
            }

            if (!string.IsNullOrEmpty($"{isPersonal}"))
            {
                if (bool.Parse($"{isPersonal}") == true)
                {
                    sqlTxt.Append($" and c.scope='private'");
                }
                else
                {
                    sqlTxt.Append($" and c.scope='school'");
                }
            }

            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Common").GetItemQueryStreamIterator(queryText: sqlTxt.ToString(), 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 });
        }

        /// <summary>
        /// 区级信息
        /// </summary>
        public record AreaInfo
        {
            public string id { get; set; }
            public string name { get; set; }
            public string standard { get; set; }
            public string standardName { get; set; }
            public int scCnt { get; set; } = 0;
            public int tchCnt { get; set; } = 0;
            public int stuCnt { get; set; } = 0;
        }


        private class SchoolInfo
        {
            public string id { get; set; }
            public string name { get; set; }
            public string picture { get; set; }
            //教师人数
            public int teacherCount { get; set; }
            //学生人数
            public long studentCount { get; set; }
            //评审人数
            public int appraiseCount { get; set; }
            //参训人数
            public int trainCount { get; set; }
            public List<KeyValuePair<object, long>> census { get; set; } = new List<KeyValuePair<object, long>>();
        }


        private record SchoolLesson
        {
            public string id { get; set; }
            public string name { get; set; }
            public string picture { get; set; }
            public int count { get; set; }
        }

        public record ActivityCount
        {
            public string id { get; set; }
            public string name { get; set; }
            public List<KeyValuePair<object, long>> census { get; set; } = new List<KeyValuePair<object, long>>();
        }
    }
}