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>
        /// <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>
        /// 依据区统id分析数据区级数据分析  //已对接
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [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 (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");

            var cosmosClient = _azureCosmos.GetCosmosClient();
            if ($"{site}".Equals(BIConst.Global))
                cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);

            int scCnt = 0; //学校人数
            int weekScCnt = 0; //本周学校人数
            int motnhScCnt = 0; //本月学校人数

            int tchCnt = 0;   //所有区级老师
            int weekTchCnt = 0;   //所有区级老师
            int monthTchCnt = 0;   //所有区级老师

            int stuCnt = 0;   //所有区级学生
            int weekStuCnt = 0;   //所有区级学生
            int monthStuCnt = 0;   //所有区级学生

            int areaSize = 0;   //区级空间
            int weekSize = 0;   //本周空间
            int monthSize = 0;   //本月空间

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

            int totalTime = 0;   //累计学时
            int allActCnt = 0;   //所有活动
            int weekActivity = 0; //周活动数量
            int monthActCnt = 0; //月活动数量
            int termActivity = 0;   //学期活动

            int onLineCount = 0;    //线上研修人数
            int offlineCount = 0;   //线下研修人数
            int classRoomCount = 0;  //课堂实录人数
            int submitCount = 0;     //认证材料人数

            int fulfilCount = 0;    //已完成
            int carryOnCount = 0;   //进行中
            int noCount = 0;      //未开始
            List<AreaStudy> trains = new();

            AreaSetting setting = null;
            try
            {
                setting = await cosmosClient.GetContainer("TEAMModelOS", "Normal").ReadItemAsync<AreaSetting>($"{areaId}", new PartitionKey("AreaSetting"));
            }
            catch (CosmosException)
            {
                setting = null;
            }
            if (setting == null)
            {
                setting = new AreaSetting
                {
                    allTime = 50,
                    classTime = 5,
                    submitTime = 15,
                    onlineTime = 20,
                    offlineTime = 10,
                    lessonMinutes = 45,
                };
            }

            string oftenSql = "select value(count(c.id)) from c";
            string scSql = $"{oftenSql} where c.areaId='{areaId}'";
            List<string> scIds = await CommonFind.GetValueSingle(cosmosClient, "School", $"select value(c.id) from c where c.areaId='{areaId}'", "Base");
            if (scIds.Count > 0)
            {
                string weekWhereSql = $"c.createTime >= {weekS} and c.createTime <= {weekE}";
                string monthWhereSql = $"c.createTime >= {monthS} and c.createTime <= {monthE}";

                //学校查询拼接
                string comSql = BICommonWay.ManyScSql("c.school", scIds);
                //学校数据查询
                scCnt = scIds.Count;
                weekScCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{oftenSql} where {comSql} and {weekWhereSql}", "Base");
                motnhScCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{oftenSql} where {comSql} and {monthWhereSql}", "Base");

                //查教师
                string tchSpl = BICommonWay.ManyScSql("REPLACE(c.code, 'Teacher-', '')", scIds);
                string tchSql = $"{oftenSql} where c.pk='Teacher' and {tchSpl}";
                tchCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", tchSql);
                weekTchCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{tchSql} and {weekWhereSql}");
                monthTchCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{tchSql} and {monthWhereSql}");

                string stuSpl = BICommonWay.ManyScSql("REPLACE(c.code, 'Base-', '')", scIds);
                //查学生
                string stuSql = $"{oftenSql} where c.pk='Base' and {stuSpl}";
                stuCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Student", $"{oftenSql} where c.pk='Base' and {stuSpl}");
                weekStuCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Student", $"{oftenSql} where c.pk='Base' and {stuSpl} and {weekWhereSql}");
                monthStuCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Student", $"{oftenSql} where c.pk='Base' and {stuSpl} and {monthWhereSql}");

                //空间
                //学校查询拼接
                string sizeSpl = BICommonWay.ManyScSql("c.id", scIds);
                string sizeSql = "select value(sum(c.size)) from c";
                areaSize = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{sizeSql} where {sizeSpl}","Base");
                weekSize = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{sizeSql} where {sizeSpl} and {weekWhereSql}", "Base");
                monthSize = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{sizeSql} where {sizeSpl} and {monthWhereSql}", "Base");

                //课例
                allLess = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{oftenSql} where c.pk='LessonRecord' and {comSql}");
                weekLess = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{oftenSql} where c.pk='LessonRecord' and {comSql} and c.startTime >= {weekS} and c.startTime <= {weekE}");
                termLess = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{oftenSql} where c.pk='LessonRecord' and {comSql} and c.startTime >= {termS} and c.startTime <= {termE}");
                monthLess = await CommonFind.GetSqlValueCount(cosmosClient, "School", $"{oftenSql} where c.pk='LessonRecord' and {comSql} and c.startTime >= {monthS} and c.startTime <= {monthE}");

                //学时
                totalTime = await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", $"select value(sum(c.totalTime)) from c where c.pk='TeacherTrain' and {comSql}");

                //活动
                string actSpl = BICommonWay.ManyScSql(" and c.school", scIds);
                allActCnt = await ActivityWay.GetCnt(cosmosClient, condSql: actSpl);
                weekActivity = await ActivityWay.GetCnt(cosmosClient, condSql: $"{actSpl} and {weekWhereSql} ");
                termActivity = await ActivityWay.GetCnt(cosmosClient, condSql: $"{actSpl} and c.createTime >= {termS} and c.createTime <= {termE}");
                monthActCnt = await ActivityWay.GetCnt(cosmosClient, condSql: $"{actSpl} and {monthWhereSql} ");

                string onLineSql = $"{oftenSql} where  c.TeacherTrain and {comSql} and c.onlineTime = 0 or c.onlineTime >= {setting.onlineTime} ";
                onLineCount = await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", onLineSql);

                //string offLineSql = $"{oftenSql} where  c.TeacherTrain and {comSql} and c.offlineTime = 0 or c.offlineTime >= {setting.offlineTime} ";
                //offlineCount = await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", offLineSql);

                //string ctSql = $"{oftenSql} where  c.TeacherTrain and {comSql} and c.classTime = 0 or c.classTime >= {setting.classTime} ";
                //classRoomCount = await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", ctSql);

                //string stSql = $"{oftenSql} join s in c.currency where c.TeacherTrain and {comSql} and s.submitTime = 0 or s.submitTime >= {setting.submitTime}";
                //submitCount = await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", ctSql);

                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryStreamIterator(queryText: $"select c.id,c.code,c.onlineTime,c.offlineTime,c.classTime,c.currency from c where c.pk='TeacherTrain' and {comSql}", requestOptions: new QueryRequestOptions() { })) 
                {
                    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())
                        {                            
                            trains.Add(obj.ToObject<AreaStudy>());
                        }
                    }
                }

                trains.ForEach(x =>
                {
                    //线上研修
                    if (x.onlineTime == 0) onLineCount++; else if (x.onlineTime >= setting.onlineTime) onLineCount++; else onLineCount++;

                    //线下研修
                    if (x.offlineTime == 0) offlineCount++; else if (x.onlineTime >= setting.offlineTime) offlineCount++; else offlineCount++;

                    //课堂实录
                    if (x.classTime == 0) classRoomCount++; else if (x.classTime >= setting.classTime) classRoomCount++; else classRoomCount++;

                    //认证材料
                    if (x.currency.submitTime == 0) submitCount++; else if (x.currency.submitTime >= setting.submitTime) submitCount++; else submitCount++;

                    if (x.currency.submitTime == 0 && x.classTime == 0 && x.offlineTime == 0 && x.onlineTime == 0) noCount++;
                    else if (x.currency.submitTime >= setting.submitTime && x.classTime >= setting.classTime && x.onlineTime >= setting.offlineTime && x.onlineTime >= setting.onlineTime) fulfilCount++;
                    else carryOnCount++;
                });
            }

            return Ok(new { state = RespondCode.Ok, scCnt, weekScCnt, motnhScCnt, tchCnt, weekTchCnt, monthTchCnt, stuCnt, weekStuCnt, monthStuCnt, areaSize, weekSize, monthSize, allLess, weekLess, monthLess, termLess, totalTime, allActCnt, weekActivity, monthActCnt, termActivity, study = new { onLineCount, offlineCount, classRoomCount, submitCount, fulfilCount, carryOnCount, noCount } });

            //int countArea = 0;//区级人员
            //int appraiseArea = 0;//区级评审人员
            //long examAreaCount = 0;   //试卷活动
            //long surveyAreaCount = 0;  //问卷活动
            //long voteAreaCount = 0;   //投票活动
            //long homeworkAreaCount = 0;  //作业活动
            //int basics = 0; //基础版数
            //int standard = 0; //标准版数
            //int major = 0;   //专业版数
            //int geCount = 0; //普教
            //int heCount = 0; //高教
            //int oeCount = 0; //其他教育
            //int dayLess = 0;  //当天课例
            //double teachCount = 0;    //课例教师

            //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);
            //}
            //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>={weekS}   and c.createTime<={weekE}";
            //        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>={termS}   and c.createTime<={termE}";
            //        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 >= dayS && x.startTime <= dayE) dayLess += 1; });
            //    records.ForEach(x => { if (x.startTime >= weekS && x.startTime <= weekE) weekLess += 1; });
            //    records.ForEach(x => { if (x.startTime >= monthS && x.startTime <= monthE) monthLess += 1; });
            //    records.ForEach(x => { if (x.startTime >= termS && x.startTime <= termE) 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 >= {weekStart} and c.createTime <= {weekEnd}", "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 >= {weekStart} and c.createTime <= {weekEnd}", "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, condSql: weekSql);

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

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

            //课例
            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", monthLSql);

            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, condSql: weekSql);

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

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

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


        /// <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 value(COUNT(c.id)) 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.GetSqlValueCount(cosmosClient, "Common", sqlTxt.ToString());

                        tempCount.census.Add(new KeyValuePair<object, long>(type, totals));
                    }
                    activityCount.Add(tempCount);
                }
            }
            else
            {
                foreach (var type in StaticValue.activityTypes)
                {
                    StringBuilder sqlTxt = new StringBuilder($"SELECT value(COUNT(c.id)) 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.GetSqlValueCount(cosmosClient, "Common", sqlTxt.ToString());

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

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

        /// <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 value(count(c.id)) FROM c WHERE c.pk='{type}' AND c.school='{itemId}'";
                    long totals = await CommonFind.GetSqlValueCount(cosmosClient, "Common", activitySql);

                    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 value(COUNT(c.id)) FROM c WHERE c.pk='{type}' AND c.school='{school}' and c.createTime >= {start} and c.createTime <= {end}";
                        long totals = await CommonFind.GetSqlValueCount(cosmosClient, "Common", activitySql);

                        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 value(COUNT(c.id)) FROM c where c.pk='{type}' and c.createTime >= {start} and c.createTime <= {end}";
                    long totals = await CommonFind.GetSqlValueCount(cosmosClient, "Common", querySql);

                    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 value(COUNT(c.id)) from c where c.pk='{type}' and c.school='{school.id}' ");
                    long totals = await CommonFind.GetSqlValueCount(cosmosClient, "Common", sqlTxt.ToString());

                    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>
        /// 依据活动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;
        }


        public class AreaStudy 
        {
            public string id { get; set; }

            public string code { get; set; }

            /// <summary>
            /// 线上观看视频的学时
            /// </summary>
            public long onlineTime { get; set; }

            /// <summary>
            /// 线下研修学时
            /// </summary>
            public long offlineTime { get; set; }
            /// <summary>
            /// 课堂实录学时
            /// </summary>
            public long classTime { get; set; }
            /// <summary>
            /// 必修的
            /// </summary>
            public Currency currency { get; set; } = new Currency();

        }

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