TEAMModelOS/TEAMModelOS.SDK/Models/Service/BIStatsWay/LessonRecordStatsWay.cs

using Azure.Cosmos;
using DocumentFormat.OpenXml.Office2010.PowerPoint;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TEAMModelOS.SDK.Models.Cosmos.BI;
using TEAMModelOS.SDK.Models.Service.BI;

namespace TEAMModelOS.SDK.Models.Service.BIStatsWay
{
    public static class LessonRecordStatsWay
    {

        /// <summary>
        /// 课例
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static async Task<LessonStats> GetSchoolAll(CosmosClient cosmosClient, string id,int year = 0)
        {
            LessonStats lessStats = new();

            DateTimeOffset dateTime = DateTimeOffset.UtcNow;
            if (year == 0)
                year = dateTime.Year;
            else
                dateTime = new(year, 12, 31, 23, 59, 59, TimeSpan.Zero);

            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", currSql, $"LessonRecord-{id}");
            lessStats.open = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"{currSql} and c.upload = 0", $"LessonRecord-{id}");
            lessStats.less = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"{currSql} and c.upload = 1", $"LessonRecord-{id}");

            List<LessRelStats> lessRelStats = new();
            string sql = $"select c.id,c.code,c.startTime,c.clientInteractionCount from c where c.pk='LessonRecord' and c.startTime >= {yearS} and c.startTime <= {yearE}";
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<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 >= lastMthS && f.startTime <= LastmthE).ToList().Select(s => s.clientInteractionCount).Sum();
            //lessStats.monthInter = lessRelStats.FindAll(f => f.startTime >= mthS && f.startTime <= mthE).ToList().Select(s => s.clientInteractionCount).Sum();
            //lessStats.lastYearInter = lessRelStats.FindAll(f => f.startTime >= lastYearS && f.startTime <= lastYearE).ToList().Select(s => s.clientInteractionCount).Sum();
            lessStats.allInter = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"select value(sum(c.clientInteractionCount)) from c where c.startTime <= {yearE}", $"LessonRecord-{id}");
            lessStats.lastYearInter = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"select value(sum(c.clientInteractionCount)) from c where c.startTime >= {lastYearS} and c.startTime <= {lastYearE}", $"LessonRecord-{id}");
            //lessStats.yearInter = lessRelStats.FindAll(f => f.startTime >= yearS && f.startTime <= yearE).ToList().Select(s => s.clientInteractionCount).Sum();

            DateTimeOffset lyearDay = new(year, dateTime.Month, dateTime.Day, dateTime.Hour, dateTime.Minute, dateTime.Second, TimeSpan.Zero);
            List<StartEndTime> everyDay = TimeHelper.GetYearEveryDay(lyearDay);

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

            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}");
            lessStats.upTime = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds(); 

            return lessStats;
        }

    }
}