TEAMModelOS/TEAMModelBI/Tool/CosmosBank/StatsWay.cs

using Microsoft.Azure.Cosmos;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.Json;
using System.Threading.Tasks;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Extension;
using TEAMModelOS.SDK.Models;
using TEAMModelOS.SDK.Models.Cosmos.BI;
using TEAMModelOS.SDK.Models.Service.BI;

namespace TEAMModelBI.Tool.CosmosBank
{
    public static class StatsWay
    {

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="scId"></param>
        /// <returns></returns>
        public static async Task<StatsInfo> upSingleSc(CosmosClient cosmosClient, string scId)
        {

            StatsInfo statsInfo = new() { id = $"{scId}" };

            DateTimeOffset dateTime = DateTimeOffset.UtcNow;
            var (dayS, dayE) = TimeHelper.GetStartOrEnd(dateTime);  //今天开始时间    13位
            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 currSql = "select value(count(c.id)) from c";
            string dayWhereSql = $"c.createTime >= {dayS} and c.createTime <= {dayE}";
            string weekWhereSql = $"c.createTime >= {weekS} and c.createTime <= {weekE}";
            string monthWhereSql = $"c.createTime >= {mthS} and c.createTime <= {mthE}";

            ScBase scBase = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemAsync<ScBase>(statsInfo.id, new PartitionKey("Base"));
            statsInfo.name = scBase.name;
            statsInfo.picture = scBase.picture;
            statsInfo.size = scBase.size;
            statsInfo.scCreateTime = scBase.createTime;
            statsInfo.areaId = scBase.areaId;

            string tchSql = $"{currSql} where ARRAY_CONTAINS(c.roles, 'teacher', true) AND c.status = 'join'";
            statsInfo.tch = await JointlySingleQuery.GetValueInt(cosmosClient, "School", tchSql, $"Teacher-{scBase.id}");
            statsInfo.dayTch = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"{tchSql} and {dayWhereSql} ", $"Teacher-{scBase.id}");
            statsInfo.weekTch = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"{tchSql} and  {weekWhereSql}", $"Teacher-{scBase.id}");
            statsInfo.monthTch = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"{tchSql} and  {monthWhereSql}", $"Teacher-{scBase.id}");

            statsInfo.stu = await JointlySingleQuery.GetValueInt(cosmosClient, "Student", currSql, $"Base-{scBase.id}");
            statsInfo.dayStu = await JointlySingleQuery.GetValueInt(cosmosClient, "Student", $"{currSql} where {dayWhereSql}", $"Base-{scBase.id}");
            statsInfo.weekStu = await JointlySingleQuery.GetValueInt(cosmosClient, "Student", $"{currSql} where {weekWhereSql}", $"Base-{scBase.id}");
            statsInfo.monthStu = await JointlySingleQuery.GetValueInt(cosmosClient, "Student", $"{currSql} where {monthWhereSql}", $"Base-{scBase.id}");

            statsInfo.room = await JointlySingleQuery.GetValueInt(cosmosClient, "School", currSql, $"Room-{scBase.id}");
            statsInfo.witRoom = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"{currSql} where (c.serial != null or c.serial != '' or IS_DEFINED(c.serial) = true)", $"Room-{scBase.id}");
            statsInfo.resourceCnt = await JointlySingleQuery.GetValueInt(cosmosClient, "School", currSql, $"Bloblog-{scBase.id}");

            statsInfo.lesson = await GetLessStats(cosmosClient, statsInfo.id);
            statsInfo.activity = await GetActStats(cosmosClient, statsInfo.id);
            statsInfo.study = await GetStudyStats(cosmosClient, statsInfo.id);

            return statsInfo;
        }


        /// <summary>
        /// 课例
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static async Task<LessonStats> GetLessStats(CosmosClient cosmosClient, string id) 
        {
            LessonStats lessStats = 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 currSql = "select value(count(c.id)) from c  where c.pk='LessonRecord'";
            lessStats.all = await JointlySingleQuery.GetValueInt(cosmosClient, "School", code: $"LessonRecord-{id}", $"{currSql}");
            lessStats.open = await JointlySingleQuery.GetValueInt(cosmosClient, "School", code: $"LessonRecord-{id}", $"{currSql} and c.upload = 0");
            lessStats.less = await JointlySingleQuery.GetValueInt(cosmosClient, "School", code: $"LessonRecord-{id}", $"{currSql} and c.upload = 1");

            List<LessRelStats> lessRelStats = new();
            string sql = $"select c.id,c.code,c.startTime,c.clientInteractionCount from c where c.pk='LessonRecord' and c.startTime >= {lastYearS} and c.startTime <= {yearE}";
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIteratorSql<LessRelStats>(queryText: sql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"LessonRecord-{id}") })) 
            {
                lessRelStats.Add(item);
            }

            lessStats.lastDay = lessRelStats.FindAll(f => f.startTime >= lastDayS && f.startTime <= lastdayE).ToList().Count;
            lessStats.day = lessRelStats.FindAll(f => f.startTime >= dayS && f.startTime <= dayE).ToList().Count;
            lessStats.lastWeek = lessRelStats.FindAll(f => f.startTime >= lastWeekS && f.startTime <= lastWeekE).ToList().Count;
            lessStats.week = lessRelStats.FindAll(f => f.startTime >= weekS && f.startTime <= weekE).ToList().Count;
            lessStats.lastTerm = lessRelStats.FindAll(f => f.startTime >= lastTermS && f.startTime <= lastTermE).ToList().Count;
            lessStats.term = lessRelStats.FindAll(f => f.startTime >= termS && f.startTime <= termE).ToList().Count;

            lessStats.lastDayInter = lessRelStats.FindAll(f => f.startTime >= lastDayS && f.startTime <= lastdayE).ToList().Select(s=>s.clientInteractionCount).Sum();
            lessStats.dayInter = lessRelStats.FindAll(f => f.startTime >= dayS && f.startTime <= dayS).ToList().Select(s => s.clientInteractionCount).Sum();
            lessStats.lastMonthInter = lessRelStats.FindAll(f => f.startTime >= lastWeekS && f.startTime <= lastWeekE).ToList().Select(s => s.clientInteractionCount).Sum();
            lessStats.monthInter = lessRelStats.FindAll(f => f.startTime >= weekS && f.startTime <= weekS).ToList().Select(s => s.clientInteractionCount).Sum();
            lessStats.lastYearInter = lessRelStats.FindAll(f => f.startTime >= lastTermS && f.startTime <= lastTermE).ToList().Select(s => s.clientInteractionCount).Sum();
            lessStats.yearInter = lessRelStats.FindAll(f => f.startTime >= termS && f.startTime <= termE).ToList().Select(s => s.clientInteractionCount).Sum();

            DateTimeOffset lyearDay = new(DateTimeOffset.UtcNow.Year - 1, DateTimeOffset.UtcNow.Month, DateTimeOffset.UtcNow.Day, DateTimeOffset.UtcNow.Hour, DateTimeOffset.UtcNow.Minute, DateTimeOffset.UtcNow.Second, TimeSpan.Zero);
            List<StartEndTime> leveryDay = TimeHelper.GetYearEveryDay(lyearDay);
            List<StartEndTime> everyDay = TimeHelper.GetYearEveryDay(DateTimeOffset.UtcNow);

            //for (int i = 0; i < leveryDay.Count; i++)
            //{
            //    if (lessStats.LastYear.Count == 366)
            //        lessStats.LastYear[i] = (double)lessRelStats.FindAll(f => (f.startTime >= leveryDay[i].start && f.startTime <= leveryDay[i].end)).Count;
            //    else
            //        lessStats.LastYear.Add((double)lessRelStats.FindAll(f => (f.startTime >= leveryDay[i].start && f.startTime <= leveryDay[i].end)).Count);
            //}

            for (int i = 0; i < everyDay.Count; i++)
            {
                if (lessStats.year.Count == 366)
                    lessStats.year[i] = lessRelStats.FindAll(f => (f.startTime >= everyDay[i].start && f.startTime <= everyDay[i].end)).Count;
                else
                    lessStats.year.Add(lessRelStats.FindAll(f => (f.startTime >= everyDay[i].start && f.startTime <= everyDay[i].end)).Count);
            }

            //string lastDaySql = $"{currSql} and c.startTime >= {lastDayS} and c.startTime <= {lastDayS}";
            //lessStats.lastDay = await JointlySingleQuery.GetValueInt(cosmosClient, "School", lastDaySql, code: $"LessonRecord-{id}");

            //string daySql = $"{currSql} and c.startTime >= {dayS} and c.startTime <= {dayE}";
            //lessStats.day = await JointlySingleQuery.GetValueInt(cosmosClient, "School", daySql, code: $"LessonRecord-{id}");

            //string lastWeekSql = $"{currSql}  and c.startTime >= {lastWeekS} and c.startTime <= {lastWeekE}";
            //lessStats.lastWeek = await JointlySingleQuery.GetValueInt(cosmosClient, "School", lastWeekSql, code: $"LessonRecord-{id}");

            //string weekSql = $"{currSql}  and c.startTime >= {weekS} and c.startTime <= {weekE}";
            //lessStats.week = await JointlySingleQuery.GetValueInt(cosmosClient, "School", weekSql, code: $"LessonRecord-{id}");

            //string lastTermSql = $"{currSql} and c.startTime >= {lastTermS} and c.startTime <= {lastTermE}";
            //lessStats.lastTerm = await JointlySingleQuery.GetValueInt(cosmosClient, "School", lastTermSql, code: $"LessonRecord-{id}");

            //string termSql = $"{currSql} and c.startTime >= {termS} and c.startTime <= {termE}";
            //lessStats.term = await JointlySingleQuery.GetValueInt(cosmosClient, "School", termSql, code: $"LessonRecord-{id}");


            //string InterSql = "select value(sum(c.clientInteractionCount)) from c where c.pk='LessonRecord'";
            //string lastDayInterSql = $"{InterSql} and c.startTime >= {lastDayS} and c.startTime <= {lastdayE}";
            //lessStats.lastDayInter = await JointlySingleQuery.GetValueInt(cosmosClient, "School", lastDayInterSql, code: $"LessonRecord-{id}");

            //string dayInterSql = $"{InterSql} and c.startTime >= {dayS} and c.startTime <= {dayE}";
            //lessStats.dayInter = await JointlySingleQuery.GetValueInt(cosmosClient, "School", dayInterSql, code: $"LessonRecord-{id}");

            //string lastMthInterSql = $"{InterSql} and c.startTime >= {lastMthS} and c.startTime <= {LastmthE}";
            //lessStats.lastMonthInter = await JointlySingleQuery.GetValueInt(cosmosClient, "School", lastMthInterSql, code: $"LessonRecord-{id}");

            //string mthInterSql = $"{InterSql} and c.startTime >= {mthS} and c.startTime <= {mthE}";
            //lessStats.monthInter = await JointlySingleQuery.GetValueInt(cosmosClient, "School", mthInterSql, code: $"LessonRecord-{id}");

            //string lastYearInterSql = $"{InterSql} and c.startTime >= {lastYearS} and c.startTime <= {lastYearE}";
            //lessStats.lastYearInter = await JointlySingleQuery.GetValueInt(cosmosClient, "School", lastYearInterSql, code: $"LessonRecord-{id}");

            //string yearInterSql = $"{InterSql} and c.startTime >= {yearS} and c.startTime <= {yearE}";
            //lessStats.yearInter = await JointlySingleQuery.GetValueInt(cosmosClient, "School", yearInterSql, code: $"LessonRecord-{id}");


            return lessStats;
        }


        /// <summary>
        /// 统计学校活动
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="scId"></param>
        /// <returns></returns>
        public static async Task<ActivityStats> GetActStats(CosmosClient cosmosClient, string scId) 
        {
            ActivityStats actStats = 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 (lastMonthS, lastMonthE) = TimeHelper.GetStartOrEnd(dateTime, "lastMonth");   //上月开始/结束时间
            var (monthS, monthE) = 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 currSql = "select value(count(c.id)) from c ";
            string yearSql = $"select c.id,c.code,c.createTime from c  where c.createTime >= {lastYearS} and c.createTime <= {yearE}";
            int exam = 0;
            int survey = 0;
            int vote = 0;
            int homework = 0;

            int lastDay = 0;
            int dayCnt = 0;
            int lastWeek = 0;
            int week = 0;
            int lastTerm = 0;
            int term = 0;
            int lastMonth = 0;
            int month = 0;

            DateTimeOffset lyearDay = new(DateTimeOffset.UtcNow.Year - 1, DateTimeOffset.UtcNow.Month, DateTimeOffset.UtcNow.Day, DateTimeOffset.UtcNow.Hour, DateTimeOffset.UtcNow.Minute, DateTimeOffset.UtcNow.Second, TimeSpan.Zero);
            List<StartEndTime> leveryDay = TimeHelper.GetYearEveryDay(lyearDay);
            List<StartEndTime> everyDay = TimeHelper.GetYearEveryDay(DateTimeOffset.UtcNow);
            List<ArtRelStats> examSts = new();
            List<ArtRelStats> surveySts = new();
            List<ArtRelStats> voteSts = new();
            List<ArtRelStats> homeworkSts = new();

            foreach (var artType in StaticValue.activityTypes)
            {
                switch (artType)
                {
                    case "Exam":
                        exam = await JointlySingleQuery.GetValueInt(cosmosClient, "Common", currSql, $"Exam-{scId}");
                        await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Common").GetItemQueryIteratorSql<ArtRelStats>(queryText: yearSql, requestOptions:new QueryRequestOptions() { PartitionKey = new PartitionKey($"Exam-{scId}")}))
                        {
                            examSts.Add(item);
                        }
                        break;
                    case "Survey":
                        survey = await JointlySingleQuery.GetValueInt(cosmosClient, "Common", currSql, $"Survey-{scId}");
                        await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Common").GetItemQueryIteratorSql<ArtRelStats>(queryText: yearSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Survey-{scId}") }))
                        {
                            surveySts.Add(item);
                        }

                        break;
                    case "Vote":
                        vote = await JointlySingleQuery.GetValueInt(cosmosClient, "Common", currSql, $"Vote-{scId}");
                        await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Common").GetItemQueryIteratorSql<ArtRelStats>(queryText: yearSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Vote-{scId}") }))
                        {
                            voteSts.Add(item);
                        }
                        break;
                    case "Homework":
                        homework = await JointlySingleQuery.GetValueInt(cosmosClient, "Common", currSql, $"Homework-{scId}");
                        await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Common").GetItemQueryIteratorSql<ArtRelStats>(queryText: yearSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Homework-{scId}") }))
                        {
                            homeworkSts.Add(item);
                        }
                        break;
                }
            }


            List<ArtRelStats> tempAll = new();
            if (examSts.Count > 0) 
            {
                tempAll.AddRange(examSts);
                lastDay += examSts.FindAll(f => f.createTime >= lastDayS && f.createTime <= lastdayE).ToList().Count;
                dayCnt += examSts.FindAll(f => f.createTime >= dayS && f.createTime <= dayE).ToList().Count;
                lastWeek += examSts.FindAll(f => f.createTime >= lastWeekS && f.createTime <= lastWeekE).ToList().Count;
                week += examSts.FindAll(f => f.createTime >= weekS && f.createTime <= weekE).ToList().Count;
                lastTerm += examSts.FindAll(f => f.createTime >= lastTermS && f.createTime <= lastTermE).ToList().Count;
                term += examSts.FindAll(f => f.createTime >= termS && f.createTime <= termE).ToList().Count;
                lastMonth += examSts.FindAll(f => f.createTime >= lastMonthS && f.createTime <= lastMonthE).ToList().Count;
                month += examSts.FindAll(f => f.createTime >= monthS && f.createTime <= monthE).ToList().Count;
            }
            
            if (surveySts.Count > 0)
            {
                tempAll.AddRange(surveySts);
                lastDay += surveySts.FindAll(f => f.createTime >= lastDayS && f.createTime <= lastdayE).ToList().Count;
                dayCnt += surveySts.FindAll(f => f.createTime >= dayS && f.createTime <= dayE).ToList().Count;
                lastWeek += surveySts.FindAll(f => f.createTime >= lastWeekS && f.createTime <= lastWeekE).ToList().Count;
                week += surveySts.FindAll(f => f.createTime >= weekS && f.createTime <= weekE).ToList().Count;
                lastTerm += surveySts.FindAll(f => f.createTime >= lastTermS && f.createTime <= lastTermE).ToList().Count;
                term += surveySts.FindAll(f => f.createTime >= termS && f.createTime <= termE).ToList().Count;
                lastMonth += examSts.FindAll(f => f.createTime >= lastMonthS && f.createTime <= lastMonthE).ToList().Count;
                month += examSts.FindAll(f => f.createTime >= monthS && f.createTime <= monthE).ToList().Count;
            }

            if (voteSts.Count > 0)
            {
                tempAll.AddRange(voteSts);
                lastDay += voteSts.FindAll(f => f.createTime >= lastDayS && f.createTime <= lastdayE).ToList().Count;
                dayCnt += voteSts.FindAll(f => f.createTime >= dayS && f.createTime <= dayE).ToList().Count;
                lastWeek += voteSts.FindAll(f => f.createTime >= lastWeekS && f.createTime <= lastWeekE).ToList().Count;
                week += voteSts.FindAll(f => f.createTime >= weekS && f.createTime <= weekE).ToList().Count;
                lastTerm += voteSts.FindAll(f => f.createTime >= lastTermS && f.createTime <= lastTermE).ToList().Count;
                term += voteSts.FindAll(f => f.createTime >= termS && f.createTime <= termE).ToList().Count;
                lastMonth += examSts.FindAll(f => f.createTime >= lastMonthS && f.createTime <= lastMonthE).ToList().Count;
                month += examSts.FindAll(f => f.createTime >= monthS && f.createTime <= monthE).ToList().Count;
            }

            if (homeworkSts.Count > 0)
            {
                tempAll.AddRange(homeworkSts);
                lastDay += homeworkSts.FindAll(f => f.createTime >= lastDayS && f.createTime <= lastdayE).ToList().Count;
                dayCnt += homeworkSts.FindAll(f => f.createTime >= dayS && f.createTime <= dayE).ToList().Count;
                lastWeek += homeworkSts.FindAll(f => f.createTime >= lastWeekS && f.createTime <= lastWeekE).ToList().Count;
                week += homeworkSts.FindAll(f => f.createTime >= weekS && f.createTime <= weekE).ToList().Count;
                lastTerm += homeworkSts.FindAll(f => f.createTime >= lastTermS && f.createTime <= lastTermE).ToList().Count;
                term += homeworkSts.FindAll(f => f.createTime >= termS && f.createTime <= termE).ToList().Count;
                lastMonth += examSts.FindAll(f => f.createTime >= lastMonthS && f.createTime <= lastMonthE).ToList().Count;
                month += examSts.FindAll(f => f.createTime >= monthS && f.createTime <= monthE).ToList().Count;
            }

            if (tempAll.Count > 0) 
            {
                //for (int i = 0; i < leveryDay.Count; i++)
                //{
                //    if (actStats.LastYear.Count == 366)
                //        actStats.LastYear[i] = (double)tempAll.FindAll(f => (f.createTime >= leveryDay[i].start && f.createTime <= leveryDay[i].end)).Count;
                //    else
                //        actStats.LastYear.Add((double)tempAll.FindAll(f => (f.createTime >= leveryDay[i].start && f.createTime <= leveryDay[i].end)).Count);
                //}

                for (int i = 0; i < everyDay.Count; i++)
                {
                    if (actStats.year.Count == 366)
                        actStats.year[i] = tempAll.FindAll(f => (f.createTime >= everyDay[i].start && f.createTime <= everyDay[i].end)).Count;
                    else
                        actStats.year.Add(tempAll.FindAll(f => (f.createTime >= everyDay[i].start && f.createTime <= everyDay[i].end)).Count);
                }
            }

            //string lastDaySql = $"c.createTime >= {lastDayS} and c.createTime <= {lastdayE}";
            //string daySql = $"c.createTime >= {dayS} and c.createTime <= {dayE}";
            //string lastWeekSql = $"c.createTime >= {lastWeekS} and c.createTime <= {lastWeekE}";
            //string weekSql = $"c.createTime >= {weekS} and c.createTime <= {weekE}";
            //string lastTermSql = $"c.createTime >= {lastTermS} and c.createTime <= {lastTermE}";
            //string termSql = $"c.createTime >= {termS} and c.createTime <= {termE}";
            //foreach (var type in StaticValue.activityTypes)
            //{
            //    switch (type)
            //    {
            //        case "Exam":
            //            exam += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Exam'", code: $"Exam-{scId}");
            //            lastDay += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Exam' and {lastDaySql}", code: $"Exam-{scId}");
            //            dayCnt += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Exam' and {daySql}", code: $"Exam-{scId}");
            //            lastWeek+= await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Exam' and {lastWeekSql}", code: $"Exam-{scId}");
            //            week += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Exam' and {weekSql}", code: $"Exam-{scId}");
            //            lastTerm += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Exam' and {lastTermSql}", code: $"Exam-{scId}");
            //            term += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Exam' and {termSql}", code: $"Exam-{scId}");                        
            //            break;
            //        case "Survey":
            //            survey += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Survey'", code: $"Survey-{scId}");
            //            lastDay += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Survey' and {lastDaySql}", code: $"Survey-{scId}");
            //            dayCnt += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Survey' and {daySql}", code: $"Survey-{scId}");
            //            lastWeek += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Survey' and {lastWeekSql}", code: $"Survey-{scId}");
            //            week += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Survey' and {weekSql}", code: $"Survey-{scId}");
            //            lastTerm += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Survey' and {lastTermSql}", code: $"Survey-{scId}");
            //            term += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Survey' and {termSql}", code: $"Survey-{scId}");
                        
            //            break;
            //        case "Vote":
            //            vote += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Vote'", code: $"Vote-{scId}");
            //            lastDay += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Vote' and {lastDaySql}", code: $"Vote-{scId}");
            //            dayCnt += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Vote' and {daySql}", code: $"Vote-{scId}");
            //            lastWeek += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Vote' and {lastWeekSql}", code: $"Vote-{scId}");
            //            week += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Vote' and {weekSql}", code: $"Vote-{scId}");
            //            lastTerm += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Vote' and {lastTermSql}", code: $"Vote-{scId}");
            //            term += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Vote' and {termSql}", code: $"Vote-{scId}");                        
            //            break;
            //        case "Homework":
            //            homework += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Homework'", code: $"Homework-{scId}");
            //            lastDay += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Homework' and {lastDaySql}", code: $"Homework-{scId}");
            //            dayCnt += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Homework' and {daySql}", code: $"Homework-{scId}");
            //            lastWeek += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Homework' and {lastWeekSql}", code: $"Homework-{scId}");
            //            week += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Homework' and {weekSql}", code: $"Homework-{scId}");
            //            lastTerm += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Homework' and {lastTermSql}", code: $"Homework-{scId}");
            //            term += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Homework' and {termSql}", code: $"Homework-{scId}");                        
            //            break;
            //    }
            //}
            actStats.all = (exam + survey + vote + homework);
            actStats.exam = exam;
            actStats.survey = survey;
            actStats.vote = vote;
            actStats.homework = homework;
            actStats.lastDay = lastDay;
            actStats.dayCnt = dayCnt;
            actStats.lastWeek = lastWeek;
            actStats.week = week;
            actStats.lastTerm = lastTerm;
            actStats.term = term;
            actStats.lastMonth = lastMonth;
            actStats.month = month;
            actStats.upTime = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();

            return actStats;
        }

        /// <summary>
        /// 研修
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="scId"></param>
        /// <returns></returns>
        public static async Task<StudyStats> GetStudyStats(CosmosClient cosmosClient, string scId) 
        {
            StudyStats studyStats = new();


            List<AreaStudy> trains = new();

            School school = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemAsync<School>($"{scId}", new PartitionKey("Base"));
            //cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIteratorSql<string>(queryText:$"select value(c.areaId) from c where c.id='{scId}'", requestOptions:new QueryRequestOptions() { PartitionKey = new PartitionKey("Base")});

            AreaSetting setting = null;
            var responSett = await cosmosClient.GetContainer("TEAMModelOS", "Normal").ReadItemStreamAsync($"{school.areaId}", new PartitionKey("AreaSetting"));
            if (responSett.StatusCode == System.Net.HttpStatusCode.OK)
            {
                using var fileJson = await JsonDocument.ParseAsync(responSett.Content);
                setting = fileJson.ToObject<AreaSetting>();
            }
            if (setting == null)
            {
                setting = new AreaSetting
                {
                    allTime = 50,
                    classTime = 5,
                    submitTime = 15,
                    onlineTime = 20,
                    offlineTime = 10,
                    lessonMinutes = 45,
                };
            }

            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryIteratorSql<AreaStudy>(queryText: $"select c.id,c.code,c.totalTime,c.onlineTime,c.offlineTime,c.classTime,c.currency from c where c.pk='TeacherTrain' and c.school='{scId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"TeacherTrain-{scId}") }))
            {
                trains.Add(item);
            }

            studyStats.learnTime = trains.Select(s => s.totalTime).Sum();
            trains.ForEach(x =>
            {
                //线上研修
                if (x.onlineTime == 0) studyStats.online++; else if (x.onlineTime >= setting.onlineTime) studyStats.online++; else studyStats.online++;

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

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

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

                if (x.currency.submitTime == 0 && x.classTime == 0 && x.offlineTime == 0 && x.onlineTime == 0) studyStats.notStarted++;
                else if (x.currency.submitTime >= setting.submitTime && x.classTime >= setting.classTime && x.onlineTime >= setting.offlineTime && x.onlineTime >= setting.onlineTime) studyStats.finish++;
                else studyStats.ongoing++;
            });

            return studyStats;
        }


    }
}