123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226 |
- using Azure.Cosmos;
- 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 ActivityStatsWay
- {
- /// <summary>
- /// 统计学校活动
- /// </summary>
- /// <param name="cosmosClient"></param>
- /// <param name="scId"></param>
- /// <returns></returns>
- public static async Task<ActivityStats> GetSchoolAll(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 (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;
- 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").GetItemQueryIterator<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").GetItemQueryIterator<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").GetItemQueryIterator<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").GetItemQueryIterator<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;
- }
- 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;
- }
- 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;
- }
- 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;
- }
- 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.cnt = (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;
- return actStats;
- }
- }
- }
|