ActivityStatsWay.cs 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226
  1. using Azure.Cosmos;
  2. using System;
  3. using System.Collections.Generic;
  4. using System.Linq;
  5. using System.Text;
  6. using System.Threading.Tasks;
  7. using TEAMModelOS.SDK.Models.Cosmos.BI;
  8. using TEAMModelOS.SDK.Models.Service.BI;
  9. namespace TEAMModelOS.SDK.Models.Service.BIStatsWay
  10. {
  11. public static class ActivityStatsWay
  12. {
  13. /// <summary>
  14. /// 统计学校活动
  15. /// </summary>
  16. /// <param name="cosmosClient"></param>
  17. /// <param name="scId"></param>
  18. /// <returns></returns>
  19. public static async Task<ActivityStats> GetSchoolAll(CosmosClient cosmosClient, string scId)
  20. {
  21. ActivityStats actStats = new();
  22. DateTimeOffset dateTime = DateTimeOffset.UtcNow;
  23. var (lastDayS, lastdayE) = TimeHelper.GetStartOrEnd(dateTime.AddDays(-1)); //昨天开始时间
  24. var (dayS, dayE) = TimeHelper.GetStartOrEnd(dateTime); //今天开始时间
  25. var (lastWeekS, lastWeekE) = TimeHelper.GetStartOrEnd(dateTime, "lastweek"); //计算上周开始/结束时间
  26. var (weekS, weekE) = TimeHelper.GetStartOrEnd(dateTime, "week"); //计算本周开始/结束时间
  27. var (lastTermS, lastTermE) = TimeHelper.GetStartOrEnd(dateTime, "lastterm"); //计算上学期开始/结束时间
  28. var (termS, termE) = TimeHelper.GetStartOrEnd(dateTime, "term"); //计算本学期开始/结束时间
  29. var (lastYearS, lastYearE) = TimeHelper.GetStartOrEnd(DateTimeOffset.Parse($"{dateTime.Year - 1}-1-1"), "year"); //计算去年开始/结束时间
  30. var (yearS, yearE) = TimeHelper.GetStartOrEnd(dateTime, "year"); //计算今年开始/结束时间
  31. string currSql = "select value(count(c.id)) from c ";
  32. string yearSql = $"select c.id,c.code,c.createTime from c where c.createTime >= {lastYearS} and c.createTime <= {yearE}";
  33. int exam = 0;
  34. int survey = 0;
  35. int vote = 0;
  36. int homework = 0;
  37. int lastDay = 0;
  38. int dayCnt = 0;
  39. int lastWeek = 0;
  40. int week = 0;
  41. int lastTerm = 0;
  42. int term = 0;
  43. DateTimeOffset lyearDay = new(DateTimeOffset.UtcNow.Year - 1, DateTimeOffset.UtcNow.Month, DateTimeOffset.UtcNow.Day, DateTimeOffset.UtcNow.Hour, DateTimeOffset.UtcNow.Minute, DateTimeOffset.UtcNow.Second, TimeSpan.Zero);
  44. List<StartEndTime> leveryDay = TimeHelper.GetYearEveryDay(lyearDay);
  45. List<StartEndTime> everyDay = TimeHelper.GetYearEveryDay(DateTimeOffset.UtcNow);
  46. List<ArtRelStats> examSts = new();
  47. List<ArtRelStats> surveySts = new();
  48. List<ArtRelStats> voteSts = new();
  49. List<ArtRelStats> homeworkSts = new();
  50. foreach (var artType in StaticValue.activityTypes)
  51. {
  52. switch (artType)
  53. {
  54. case "Exam":
  55. exam = await JointlySingleQuery.GetValueInt(cosmosClient, "Common", currSql, $"Exam-{scId}");
  56. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Common").GetItemQueryIterator<ArtRelStats>(queryText: yearSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Exam-{scId}") }))
  57. {
  58. examSts.Add(item);
  59. }
  60. break;
  61. case "Survey":
  62. survey = await JointlySingleQuery.GetValueInt(cosmosClient, "Common", currSql, $"Survey-{scId}");
  63. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Common").GetItemQueryIterator<ArtRelStats>(queryText: yearSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Survey-{scId}") }))
  64. {
  65. surveySts.Add(item);
  66. }
  67. break;
  68. case "Vote":
  69. vote = await JointlySingleQuery.GetValueInt(cosmosClient, "Common", currSql, $"Vote-{scId}");
  70. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Common").GetItemQueryIterator<ArtRelStats>(queryText: yearSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Vote-{scId}") }))
  71. {
  72. voteSts.Add(item);
  73. }
  74. break;
  75. case "Homework":
  76. homework = await JointlySingleQuery.GetValueInt(cosmosClient, "Common", currSql, $"Homework-{scId}");
  77. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Common").GetItemQueryIterator<ArtRelStats>(queryText: yearSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Homework-{scId}") }))
  78. {
  79. homeworkSts.Add(item);
  80. }
  81. break;
  82. }
  83. }
  84. List<ArtRelStats> tempAll = new();
  85. if (examSts.Count > 0)
  86. {
  87. tempAll.AddRange(examSts);
  88. lastDay += examSts.FindAll(f => f.createTime >= lastDayS && f.createTime <= lastdayE).ToList().Count;
  89. dayCnt += examSts.FindAll(f => f.createTime >= dayS && f.createTime <= dayE).ToList().Count;
  90. lastWeek += examSts.FindAll(f => f.createTime >= lastWeekS && f.createTime <= lastWeekE).ToList().Count;
  91. week += examSts.FindAll(f => f.createTime >= weekS && f.createTime <= weekE).ToList().Count;
  92. lastTerm += examSts.FindAll(f => f.createTime >= lastTermS && f.createTime <= lastTermE).ToList().Count;
  93. term += examSts.FindAll(f => f.createTime >= termS && f.createTime <= termE).ToList().Count;
  94. }
  95. if (surveySts.Count > 0)
  96. {
  97. tempAll.AddRange(surveySts);
  98. lastDay += surveySts.FindAll(f => f.createTime >= lastDayS && f.createTime <= lastdayE).ToList().Count;
  99. dayCnt += surveySts.FindAll(f => f.createTime >= dayS && f.createTime <= dayE).ToList().Count;
  100. lastWeek += surveySts.FindAll(f => f.createTime >= lastWeekS && f.createTime <= lastWeekE).ToList().Count;
  101. week += surveySts.FindAll(f => f.createTime >= weekS && f.createTime <= weekE).ToList().Count;
  102. lastTerm += surveySts.FindAll(f => f.createTime >= lastTermS && f.createTime <= lastTermE).ToList().Count;
  103. term += surveySts.FindAll(f => f.createTime >= termS && f.createTime <= termE).ToList().Count;
  104. }
  105. if (voteSts.Count > 0)
  106. {
  107. tempAll.AddRange(voteSts);
  108. lastDay += voteSts.FindAll(f => f.createTime >= lastDayS && f.createTime <= lastdayE).ToList().Count;
  109. dayCnt += voteSts.FindAll(f => f.createTime >= dayS && f.createTime <= dayE).ToList().Count;
  110. lastWeek += voteSts.FindAll(f => f.createTime >= lastWeekS && f.createTime <= lastWeekE).ToList().Count;
  111. week += voteSts.FindAll(f => f.createTime >= weekS && f.createTime <= weekE).ToList().Count;
  112. lastTerm += voteSts.FindAll(f => f.createTime >= lastTermS && f.createTime <= lastTermE).ToList().Count;
  113. term += voteSts.FindAll(f => f.createTime >= termS && f.createTime <= termE).ToList().Count;
  114. }
  115. if (homeworkSts.Count > 0)
  116. {
  117. tempAll.AddRange(homeworkSts);
  118. lastDay += homeworkSts.FindAll(f => f.createTime >= lastDayS && f.createTime <= lastdayE).ToList().Count;
  119. dayCnt += homeworkSts.FindAll(f => f.createTime >= dayS && f.createTime <= dayE).ToList().Count;
  120. lastWeek += homeworkSts.FindAll(f => f.createTime >= lastWeekS && f.createTime <= lastWeekE).ToList().Count;
  121. week += homeworkSts.FindAll(f => f.createTime >= weekS && f.createTime <= weekE).ToList().Count;
  122. lastTerm += homeworkSts.FindAll(f => f.createTime >= lastTermS && f.createTime <= lastTermE).ToList().Count;
  123. term += homeworkSts.FindAll(f => f.createTime >= termS && f.createTime <= termE).ToList().Count;
  124. }
  125. if (tempAll.Count > 0)
  126. {
  127. for (int i = 0; i < leveryDay.Count; i++)
  128. {
  129. if (actStats.LastYear.Count == 366)
  130. actStats.LastYear[i] = (double)tempAll.FindAll(f => (f.createTime >= leveryDay[i].start && f.createTime <= leveryDay[i].end)).Count;
  131. else
  132. actStats.LastYear.Add((double)tempAll.FindAll(f => (f.createTime >= leveryDay[i].start && f.createTime <= leveryDay[i].end)).Count);
  133. }
  134. for (int i = 0; i < everyDay.Count; i++)
  135. {
  136. if (actStats.year.Count == 366)
  137. actStats.year[i] = tempAll.FindAll(f => (f.createTime >= everyDay[i].start && f.createTime <= everyDay[i].end)).Count;
  138. else
  139. actStats.year.Add(tempAll.FindAll(f => (f.createTime >= everyDay[i].start && f.createTime <= everyDay[i].end)).Count);
  140. }
  141. }
  142. //string lastDaySql = $"c.createTime >= {lastDayS} and c.createTime <= {lastdayE}";
  143. //string daySql = $"c.createTime >= {dayS} and c.createTime <= {dayE}";
  144. //string lastWeekSql = $"c.createTime >= {lastWeekS} and c.createTime <= {lastWeekE}";
  145. //string weekSql = $"c.createTime >= {weekS} and c.createTime <= {weekE}";
  146. //string lastTermSql = $"c.createTime >= {lastTermS} and c.createTime <= {lastTermE}";
  147. //string termSql = $"c.createTime >= {termS} and c.createTime <= {termE}";
  148. //foreach (var type in StaticValue.activityTypes)
  149. //{
  150. // switch (type)
  151. // {
  152. // case "Exam":
  153. // exam += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Exam'", code: $"Exam-{scId}");
  154. // lastDay += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Exam' and {lastDaySql}", code: $"Exam-{scId}");
  155. // dayCnt += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Exam' and {daySql}", code: $"Exam-{scId}");
  156. // lastWeek += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Exam' and {lastWeekSql}", code: $"Exam-{scId}");
  157. // week += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Exam' and {weekSql}", code: $"Exam-{scId}");
  158. // lastTerm += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Exam' and {lastTermSql}", code: $"Exam-{scId}");
  159. // term += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Exam' and {termSql}", code: $"Exam-{scId}");
  160. // break;
  161. // case "Survey":
  162. // survey += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Survey'", code: $"Survey-{scId}");
  163. // lastDay += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Survey' and {lastDaySql}", code: $"Survey-{scId}");
  164. // dayCnt += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Survey' and {daySql}", code: $"Survey-{scId}");
  165. // lastWeek += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Survey' and {lastWeekSql}", code: $"Survey-{scId}");
  166. // week += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Survey' and {weekSql}", code: $"Survey-{scId}");
  167. // lastTerm += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Survey' and {lastTermSql}", code: $"Survey-{scId}");
  168. // term += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Survey' and {termSql}", code: $"Survey-{scId}");
  169. // break;
  170. // case "Vote":
  171. // vote += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Vote'", code: $"Vote-{scId}");
  172. // lastDay += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Vote' and {lastDaySql}", code: $"Vote-{scId}");
  173. // dayCnt += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Vote' and {daySql}", code: $"Vote-{scId}");
  174. // lastWeek += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Vote' and {lastWeekSql}", code: $"Vote-{scId}");
  175. // week += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Vote' and {weekSql}", code: $"Vote-{scId}");
  176. // lastTerm += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Vote' and {lastTermSql}", code: $"Vote-{scId}");
  177. // term += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Vote' and {termSql}", code: $"Vote-{scId}");
  178. // break;
  179. // case "Homework":
  180. // homework += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Homework'", code: $"Homework-{scId}");
  181. // lastDay += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Homework' and {lastDaySql}", code: $"Homework-{scId}");
  182. // dayCnt += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Homework' and {daySql}", code: $"Homework-{scId}");
  183. // lastWeek += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Homework' and {lastWeekSql}", code: $"Homework-{scId}");
  184. // week += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Homework' and {weekSql}", code: $"Homework-{scId}");
  185. // lastTerm += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Homework' and {lastTermSql}", code: $"Homework-{scId}");
  186. // term += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{currSql} where c.pk='Homework' and {termSql}", code: $"Homework-{scId}");
  187. // break;
  188. // }
  189. //}
  190. actStats.cnt = (exam + survey + vote + homework);
  191. actStats.exam = exam;
  192. actStats.survey = survey;
  193. actStats.vote = vote;
  194. actStats.homework = homework;
  195. actStats.lastDay = lastDay;
  196. actStats.dayCnt = dayCnt;
  197. actStats.lastWeek = lastWeek;
  198. actStats.week = week;
  199. actStats.lastTerm = lastTerm;
  200. actStats.term = term;
  201. return actStats;
  202. }
  203. }
  204. }