ActivityStatsWay.cs 15 KB

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