LessonRecordStatsWay.cs 9.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133
  1. using Azure.Cosmos;
  2. using DocumentFormat.OpenXml.Office2010.PowerPoint;
  3. using System;
  4. using System.Collections.Generic;
  5. using System.Linq;
  6. using System.Text;
  7. using System.Threading.Tasks;
  8. using TEAMModelOS.SDK.Models.Cosmos.BI;
  9. using TEAMModelOS.SDK.Models.Service.BI;
  10. namespace TEAMModelOS.SDK.Models.Service.BIStatsWay
  11. {
  12. public static class LessonRecordStatsWay
  13. {
  14. /// <summary>
  15. /// 课例
  16. /// </summary>
  17. /// <param name="cosmosClient"></param>
  18. /// <param name="id"></param>
  19. /// <returns></returns>
  20. public static async Task<LessonStats> GetSchoolAll(CosmosClient cosmosClient, string id)
  21. {
  22. LessonStats lessStats = new();
  23. DateTimeOffset dateTime = DateTimeOffset.UtcNow;
  24. var (lastDayS, lastdayE) = TimeHelper.GetStartOrEnd(dateTime.AddDays(-1)); //昨天开始时间
  25. var (dayS, dayE) = TimeHelper.GetStartOrEnd(dateTime); //今天开始时间
  26. var (lastWeekS, lastWeekE) = TimeHelper.GetStartOrEnd(dateTime, "lastweek"); //计算上周开始/结束时间
  27. var (weekS, weekE) = TimeHelper.GetStartOrEnd(dateTime, "week"); //计算本周开始/结束时间
  28. var (lastTermS, lastTermE) = TimeHelper.GetStartOrEnd(dateTime, "lastterm"); //计算上学期开始/结束时间
  29. var (termS, termE) = TimeHelper.GetStartOrEnd(dateTime, "term"); //计算本学期开始/结束时间
  30. var (lastMthS, LastmthE) = TimeHelper.GetStartOrEnd(dateTime, "lastMonth"); //上月开始/结束时间
  31. var (mthS, mthE) = TimeHelper.GetStartOrEnd(dateTime, "month"); //本月开始/结束时间
  32. var (lastYearS, lastYearE) = TimeHelper.GetStartOrEnd(DateTimeOffset.Parse($"{dateTime.Year - 1}-1-1"), "year"); //计算去年开始/结束时间
  33. var (yearS, yearE) = TimeHelper.GetStartOrEnd(dateTime, "year"); //计算今年开始/结束时间
  34. string currSql = "select value(count(c.id)) from c where c.pk='LessonRecord'";
  35. lessStats.all = await JointlySingleQuery.GetValueInt(cosmosClient, "School", currSql, $"LessonRecord-{id}");
  36. lessStats.open = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"{currSql} and c.upload = 0", $"LessonRecord-{id}");
  37. lessStats.less = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"{currSql} and c.upload = 1", $"LessonRecord-{id}");
  38. List<LessRelStats> lessRelStats = new();
  39. 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}";
  40. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<LessRelStats>(queryText: sql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"LessonRecord-{id}") }))
  41. {
  42. lessRelStats.Add(item);
  43. }
  44. lessStats.lastDay = lessRelStats.FindAll(f => f.startTime >= lastDayS && f.startTime <= lastdayE).ToList().Count;
  45. lessStats.day = lessRelStats.FindAll(f => f.startTime >= dayS && f.startTime <= dayE).ToList().Count;
  46. lessStats.lastWeek = lessRelStats.FindAll(f => f.startTime >= lastWeekS && f.startTime <= lastWeekE).ToList().Count;
  47. lessStats.week = lessRelStats.FindAll(f => f.startTime >= weekS && f.startTime <= weekE).ToList().Count;
  48. lessStats.lastTerm = lessRelStats.FindAll(f => f.startTime >= lastTermS && f.startTime <= lastTermE).ToList().Count;
  49. lessStats.term = lessRelStats.FindAll(f => f.startTime >= termS && f.startTime <= termE).ToList().Count;
  50. lessStats.lastDayInter = lessRelStats.FindAll(f => f.startTime >= lastDayS && f.startTime <= lastdayE).ToList().Select(s => s.clientInteractionCount).Sum();
  51. lessStats.dayInter = lessRelStats.FindAll(f => f.startTime >= dayS && f.startTime <= dayS).ToList().Select(s => s.clientInteractionCount).Sum();
  52. lessStats.lastMonthInter = lessRelStats.FindAll(f => f.startTime >= lastWeekS && f.startTime <= lastWeekE).ToList().Select(s => s.clientInteractionCount).Sum();
  53. lessStats.monthInter = lessRelStats.FindAll(f => f.startTime >= weekS && f.startTime <= weekS).ToList().Select(s => s.clientInteractionCount).Sum();
  54. lessStats.lastYearInter = lessRelStats.FindAll(f => f.startTime >= lastTermS && f.startTime <= lastTermE).ToList().Select(s => s.clientInteractionCount).Sum();
  55. lessStats.yearInter = lessRelStats.FindAll(f => f.startTime >= termS && f.startTime <= termE).ToList().Select(s => s.clientInteractionCount).Sum();
  56. DateTimeOffset lyearDay = new(DateTimeOffset.UtcNow.Year - 1, DateTimeOffset.UtcNow.Month, DateTimeOffset.UtcNow.Day, DateTimeOffset.UtcNow.Hour, DateTimeOffset.UtcNow.Minute, DateTimeOffset.UtcNow.Second, TimeSpan.Zero);
  57. List<StartEndTime> leveryDay = TimeHelper.GetYearEveryDay(lyearDay);
  58. List<StartEndTime> everyDay = TimeHelper.GetYearEveryDay(DateTimeOffset.UtcNow);
  59. //for (int i = 0; i < leveryDay.Count; i++)
  60. //{
  61. // if (lessStats.LastYear.Count == 366)
  62. // lessStats.LastYear[i] = (double)lessRelStats.FindAll(f => (f.startTime >= leveryDay[i].start && f.startTime <= leveryDay[i].end)).Count;
  63. // else
  64. // lessStats.LastYear.Add((double)lessRelStats.FindAll(f => (f.startTime >= leveryDay[i].start && f.startTime <= leveryDay[i].end)).Count);
  65. //}
  66. for (int i = 0; i < everyDay.Count; i++)
  67. {
  68. if (lessStats.year.Count == 366)
  69. lessStats.year[i] = lessRelStats.FindAll(f => (f.startTime >= everyDay[i].start && f.startTime <= everyDay[i].end)).Count;
  70. else
  71. lessStats.year.Add(lessRelStats.FindAll(f => (f.startTime >= everyDay[i].start && f.startTime <= everyDay[i].end)).Count);
  72. }
  73. //string lastDaySql = $"{currSql} and c.startTime >= {lastDayS} and c.startTime <= {lastDayS}";
  74. //lessStats.lastDay = await JointlySingleQuery.GetValueInt(cosmosClient, "School", lastDaySql, code: $"LessonRecord-{id}");
  75. //string daySql = $"{currSql} and c.startTime >= {dayS} and c.startTime <= {dayE}";
  76. //lessStats.day = await JointlySingleQuery.GetValueInt(cosmosClient, "School", daySql, code: $"LessonRecord-{id}");
  77. //string lastWeekSql = $"{currSql} and c.startTime >= {lastWeekS} and c.startTime <= {lastWeekE}";
  78. //lessStats.lastWeek = await JointlySingleQuery.GetValueInt(cosmosClient, "School", lastWeekSql, code: $"LessonRecord-{id}");
  79. //string weekSql = $"{currSql} and c.startTime >= {weekS} and c.startTime <= {weekE}";
  80. //lessStats.week = await JointlySingleQuery.GetValueInt(cosmosClient, "School", weekSql, code: $"LessonRecord-{id}");
  81. //string lastTermSql = $"{currSql} and c.startTime >= {lastTermS} and c.startTime <= {lastTermE}";
  82. //lessStats.lastTerm = await JointlySingleQuery.GetValueInt(cosmosClient, "School", lastTermSql, code: $"LessonRecord-{id}");
  83. //string termSql = $"{currSql} and c.startTime >= {termS} and c.startTime <= {termE}";
  84. //lessStats.term = await JointlySingleQuery.GetValueInt(cosmosClient, "School", termSql, code: $"LessonRecord-{id}");
  85. //string InterSql = "select value(sum(c.clientInteractionCount)) from c where c.pk='LessonRecord'";
  86. //string lastDayInterSql = $"{InterSql} and c.startTime >= {lastDayS} and c.startTime <= {lastdayE}";
  87. //lessStats.lastDayInter = await JointlySingleQuery.GetValueInt(cosmosClient, "School", lastDayInterSql, code: $"LessonRecord-{id}");
  88. //string dayInterSql = $"{InterSql} and c.startTime >= {dayS} and c.startTime <= {dayE}";
  89. //lessStats.dayInter = await JointlySingleQuery.GetValueInt(cosmosClient, "School", dayInterSql, code: $"LessonRecord-{id}");
  90. //string lastMthInterSql = $"{InterSql} and c.startTime >= {lastMthS} and c.startTime <= {LastmthE}";
  91. //lessStats.lastMonthInter = await JointlySingleQuery.GetValueInt(cosmosClient, "School", lastMthInterSql, code: $"LessonRecord-{id}");
  92. //string mthInterSql = $"{InterSql} and c.startTime >= {mthS} and c.startTime <= {mthE}";
  93. //lessStats.monthInter = await JointlySingleQuery.GetValueInt(cosmosClient, "School", mthInterSql, code: $"LessonRecord-{id}");
  94. //string lastYearInterSql = $"{InterSql} and c.startTime >= {lastYearS} and c.startTime <= {lastYearE}";
  95. //lessStats.lastYearInter = await JointlySingleQuery.GetValueInt(cosmosClient, "School", lastYearInterSql, code: $"LessonRecord-{id}");
  96. //string yearInterSql = $"{InterSql} and c.startTime >= {yearS} and c.startTime <= {yearE}";
  97. //lessStats.yearInter = await JointlySingleQuery.GetValueInt(cosmosClient, "School", yearInterSql, code: $"LessonRecord-{id}");
  98. lessStats.upTime = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
  99. return lessStats;
  100. }
  101. }
  102. }