SchoolController.cs 83 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386
  1. using Azure.Cosmos;
  2. using DocumentFormat.OpenXml.Bibliography;
  3. using DocumentFormat.OpenXml.Spreadsheet;
  4. using Microsoft.AspNetCore.Http;
  5. using Microsoft.AspNetCore.Mvc;
  6. using Microsoft.Extensions.Options;
  7. using NUnit.Framework.Constraints;
  8. using Pipelines.Sockets.Unofficial.Arenas;
  9. using StackExchange.Redis;
  10. using System;
  11. using System.Collections.Generic;
  12. using System.Diagnostics;
  13. using System.Linq;
  14. using System.Text;
  15. using System.Text.Json;
  16. using System.Threading.Tasks;
  17. using TEAMModelBI.Models;
  18. using TEAMModelBI.Tool;
  19. using TEAMModelBI.Tool.CosmosBank;
  20. using TEAMModelOS.Models;
  21. using TEAMModelOS.SDK.Context.BI;
  22. using TEAMModelOS.SDK.Context.Constant;
  23. using TEAMModelOS.SDK.DI;
  24. using TEAMModelOS.SDK.Extension;
  25. using TEAMModelOS.SDK.Models;
  26. using TEAMModelOS.SDK.Models.Cosmos.BI;
  27. using TEAMModelOS.SDK.Models.Cosmos.BI.BISchool;
  28. using TEAMModelOS.SDK.Models.Service.BI;
  29. using TEAMModelOS.SDK.Models.Service.BIStatsWay;
  30. using LessonStats = TEAMModelOS.SDK.Models.Cosmos.BI.LessonStats;
  31. namespace TEAMModelBI.Controllers.Census
  32. {
  33. [Route("school")]
  34. [ApiController]
  35. public class SchoolController : ControllerBase
  36. {
  37. private readonly AzureCosmosFactory _azureCosmos;
  38. private readonly AzureStorageFactory _azureStorage;
  39. private readonly DingDing _dingDing;
  40. private readonly Option _option;
  41. private readonly HttpTrigger _httpTrigger;
  42. private readonly AzureRedisFactory _azureRedis;
  43. public SchoolController(AzureCosmosFactory azureCosmos, AzureStorageFactory azureStorage, DingDing dingDing, IOptionsSnapshot<Option> option, HttpTrigger httpTrigger, AzureRedisFactory azureRedis)
  44. {
  45. _azureCosmos = azureCosmos;
  46. _azureStorage = azureStorage;
  47. _dingDing = dingDing;
  48. _option = option?.Value;
  49. _httpTrigger = httpTrigger;
  50. _azureRedis = azureRedis;
  51. }
  52. /// <summary>
  53. /// 统计所有分析:基础、课例、活动、资源
  54. /// </summary>
  55. /// <param name="jsonElement"></param>
  56. /// <returns></returns>
  57. [ProducesDefaultResponseType]
  58. [HttpPost("get-all")]
  59. public async Task<IActionResult> GetAll(JsonElement jsonElement)
  60. {
  61. //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署,就不需要,一站多用时,取消注释
  62. var cosmosClient = _azureCosmos.GetCosmosClient();
  63. //////分开部署,就不需要,一站多用时,取消注释
  64. //if ($"{site}".Equals(BIConst.Global))
  65. // cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
  66. long schoolCount = 0; //学校数量
  67. int tecCount = 0; //教师数量
  68. int stuCount = 0; //学生数量
  69. long roomCount = 0; //教室数量
  70. long wisdomRoomCount = 0; //智慧教室数量
  71. long allClassCount = 0; //所有班级
  72. long allLessCount = 0; //所有课例
  73. long lastYearLessCount = 0; //去年课例
  74. long yearLessCount = 0;//今年课例
  75. long lastWeekLessCount = 0; //上周课例
  76. long weekLessCount = 0; //本周课例
  77. long lastTermLessCount = 0; //上学期课例
  78. long termLessCount = 0; //本学期课例
  79. long allActivityCount = 0; //所有活动
  80. long lastActivityCount = 0; //去年活动
  81. long activityCount = 0; //今年活动
  82. long lastWeekActivitCount = 0; //上周活动
  83. long weekActivitCount = 0; //本周活动
  84. long lastTermActivitCount = 0; //上学期活动
  85. long TermActivitCount = 0; //本学期学期活动
  86. long rercCount = 0; //所有资源数量
  87. long weekRercCount = 0; //本周资源数量
  88. long lastWeekRercCount = 0; //上周资源数量
  89. long lastTermRercCount = 0; //上学期资源
  90. long termRercCount = 0; //本学期资源
  91. long lastYearRercCount = 0; //去年资源
  92. long yearRercCount = 0; //去年资源
  93. var (lastYearStart, lastYearEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.Parse($"{DateTimeOffset.UtcNow.Year - 1}-1-1"), "year"); //计算去年开始/结束时间
  94. var (yearStart, yearEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "year"); //计算今年开始/结束时间
  95. var (lastWeekStart, lastWeekEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "lastweek"); //计算上周开始/结束时间
  96. var (weekStart, weekEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "week"); //计算本周开始/结束时间
  97. var (lastTermStart, lastTermEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "lastterm"); //计算上学期开始/结束时间
  98. var (termStart, termEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "term"); //计算本学期开始/结束时间
  99. schoolCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", "select value(count(c.id)) from c", "Base"); //所有学校数量
  100. tecCount = await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", "select value(count(c.id)) from c", "Base"); //所有教师数量
  101. stuCount = await CommonFind.GetSqlValueCount(cosmosClient, "Student", "select value(count(c.id)) from c", "Base"); //所有学生数量
  102. allClassCount = await CommonFind.GetSqlValueCount(cosmosClient, new List<string>() { "School" }, "select count(c.id) totals from c where c.pk = 'Class'"); //所有班级数量
  103. roomCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", "select value(count(c.id)) from c where c.pk = 'Room'"); //所有教室数量
  104. wisdomRoomCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", "select value(count(c.id)) from c where c.pk = 'Room' and c.serial != null"); //智慧教室数量
  105. List<string> containers = new() { "School", "Teacher" };
  106. string lessSqlTxt = "select value(count(c.id)) from c where c.pk='LessonRecord' and c.startTime>={0} and c.startTime<={1}";
  107. allLessCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, $"select count(c.id) as totals from c where c.pk='LessonRecord'");//所有课例
  108. lastYearLessCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(lessSqlTxt, lastYearStart, lastYearEnd)); //去年课例
  109. yearLessCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(lessSqlTxt, yearStart, yearEnd)); //今年课例
  110. lastWeekLessCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(lessSqlTxt, lastWeekStart, lastWeekEnd)); //上周课例
  111. weekLessCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(lessSqlTxt, weekStart, weekEnd)); //本周课例
  112. lastTermLessCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(lessSqlTxt, lastTermStart, lastTermEnd)); //上学期课例
  113. termLessCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(lessSqlTxt, termStart, termEnd)); //上学期课例
  114. string typeSqlTxt = "select value(count(c.id)) from c where c.pk='{0}' and c.createTime >={1} and c.createTime<= {2}";
  115. foreach (var type in StaticValue.activityTypes)
  116. {
  117. allActivityCount += await CommonFind.GetSqlValueCount(cosmosClient, "Common", $"select value(count(c.id)) from c where c.pk='{type}' ");//所有活动
  118. lastActivityCount += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(typeSqlTxt, type, lastYearStart, lastYearEnd)); //去年活动
  119. activityCount += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(typeSqlTxt, type, yearStart, yearEnd)); //今年活动
  120. lastWeekActivitCount += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(typeSqlTxt, type, lastWeekStart, lastWeekEnd)); //上周活动
  121. weekActivitCount += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(typeSqlTxt, type, weekStart, weekEnd)); //本周活动
  122. lastTermActivitCount += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(typeSqlTxt, type, lastTermStart, lastTermEnd)); //上学期活动
  123. TermActivitCount += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(typeSqlTxt, type, termStart, termEnd)); //本学期学期活动
  124. }
  125. string bloblSqlTxt = "select value(count(c.id)) from c where c.pk='Bloblog' and c.time>={0} and c.time<={1}";
  126. rercCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, "select value(count(c.id)) from c where c.pk='Bloblog'"); //所有资源
  127. lastWeekRercCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(bloblSqlTxt, lastWeekStart, lastWeekEnd)); //上周资源
  128. weekRercCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(bloblSqlTxt, weekStart, weekEnd)); //本周资源
  129. lastTermRercCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(bloblSqlTxt, lastTermStart, lastTermEnd)); //上学期资源
  130. termRercCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(bloblSqlTxt, termStart, termEnd)); //这学期资源
  131. lastYearRercCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(bloblSqlTxt, lastYearStart, lastYearEnd)); //去年资源
  132. yearRercCount = await CommonFind.GetSqlValueCount(cosmosClient, containers, string.Format(bloblSqlTxt, yearStart, yearEnd)); //今年资源
  133. return Ok(new { state = 200, schoolCount, tecCount, stuCount, allClassCount, roomCount, wisdomRoomCount, allLessCount, lastYearLessCount, yearLessCount, lastWeekLessCount, weekLessCount, lastTermLessCount, termLessCount, allActivityCount, lastActivityCount, activityCount, lastWeekActivitCount, weekActivitCount, lastTermActivitCount, TermActivitCount, rercCount, lastWeekRercCount, weekRercCount, lastTermRercCount, termRercCount, lastYearRercCount, yearRercCount });
  134. }
  135. /// <summary>
  136. /// 查询顾问相关的学校统计数据
  137. /// </summary>
  138. /// <param name="jsonElement"></param>
  139. /// <returns></returns>
  140. [ProducesDefaultResponseType]
  141. [HttpPost("get-assist")]
  142. public async Task<IActionResult> GetAssistStatis(JsonElement jsonElement)
  143. {
  144. if (!jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return BadRequest();
  145. //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署,就不需要,一站多用时,取消注释
  146. int tecCount = 0; //教师数量
  147. int stuCount = 0; //学校数量
  148. int classCount = 0; //班级数量
  149. int roomCount = 0; //智慧教师数量
  150. int allLessonCount = 0; //课例数量
  151. int lastWeekLessCount = 0;// 上周课例数
  152. int weekLessCount = 0; //本周课例
  153. int lastTermLessCount = 0;// 上学期课例数
  154. int termLessCount = 0; //本学期课例
  155. int lastYearLessCount = 0; //去年课例
  156. int yearLessCount = 0; //今年课例
  157. int allBloblog = 0; //学校资源
  158. int lastYearBloblog = 0; //去年学校资源
  159. int yearBloblog = 0; //今年学校资源
  160. long allActivity = 0; //学校所有活动
  161. long lastYearActivity = 0; //去年学校所有活动
  162. long yearActivity = 0; //今年学校所有活动
  163. long lastWeekActivity = 0; //上周学校所有活动
  164. long weekActivity = 0; //本周学校所有活动
  165. var cosmosClient = _azureCosmos.GetCosmosClient();
  166. ////分开部署,就不需要,一站多用时,取消注释
  167. //if ($"{site}".Equals(BIConst.Global))
  168. // cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
  169. List<string> schoolIds = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}");
  170. var (lastWeekStart, lastWeekEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "lastweek"); //计算上周开始/结束时间
  171. var (weekStart, weekEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "week"); //计算本周开始/结束时间
  172. var (lastTermStart, lastTermEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "lastterm"); //计算上学期开始/结束时间
  173. var (termStart, termEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "term"); //计算本学期开始/结束时间
  174. var (lastYearStart, lastYearEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.Parse($"{DateTimeOffset.UtcNow.Year - 1}-1-1"), "year"); //计算去年开始/结束时间
  175. var (yearStart, yearEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "year"); //计算今年开始/结束时间
  176. string unifySqlTxt = "select value(count(c.id)) from c";
  177. string unifyTimeSql = "select value(count(c.id)) from c where c.startTime>={0} and c.startTime<={1}";
  178. string blobTimeSql = "select value(count(c.id)) from c where c.time>={0} and c.time<={1}";
  179. List<RecSchoolDate> recSchoolDates = new();
  180. foreach (var itemId in schoolIds)
  181. {
  182. School school = new();
  183. var response = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(itemId, new PartitionKey("Base"));
  184. if (response.Status == 200)
  185. {
  186. using var json = await JsonDocument.ParseAsync(response.ContentStream);
  187. school = json.ToObject<School>();
  188. }
  189. tecCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", $"select value(count(c.id)) from c where ARRAY_CONTAINS(c.roles,'teacher',true) and c.status = 'join'", $"Teacher-{itemId}");
  190. stuCount += await CommonFind.GetSqlValueCount(cosmosClient, "Student", unifySqlTxt, $"Base-{itemId}");
  191. classCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", unifySqlTxt, $"Class-{itemId}");
  192. roomCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", unifySqlTxt, $"Room-{itemId}");
  193. //学校所有课例
  194. int tempLessCount = await CommonFind.GetSqlValueCount(cosmosClient, "School", unifySqlTxt, $"LessonRecord-{itemId}");
  195. lastWeekLessCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(unifyTimeSql, lastWeekStart, lastWeekEnd), $"LessonRecord-{itemId}");
  196. weekLessCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(unifyTimeSql, weekStart, weekEnd), $"LessonRecord-{itemId}");
  197. lastTermLessCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(unifyTimeSql, lastTermStart, lastTermEnd), $"LessonRecord-{itemId}");
  198. termLessCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(unifyTimeSql, termStart, termEnd), $"LessonRecord-{itemId}");
  199. lastYearLessCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(unifyTimeSql, lastYearStart, lastYearEnd), $"LessonRecord-{itemId}");
  200. yearLessCount += await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(unifyTimeSql, yearStart, yearEnd), $"LessonRecord-{itemId}");
  201. //学校资源
  202. int tempBloblog = await CommonFind.GetSqlValueCount(cosmosClient, "School", unifySqlTxt, $"Bloblog-{itemId}");
  203. lastYearBloblog += await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(blobTimeSql, lastYearStart, lastYearEnd), $"Bloblog-{itemId}");
  204. yearBloblog += await CommonFind.GetSqlValueCount(cosmosClient, "School", string.Format(blobTimeSql, yearStart, yearEnd), $"Bloblog-{itemId}");
  205. //学校活动
  206. long tempallActivity = 0;
  207. //统计活动
  208. foreach (var type in StaticValue.activityTypes)
  209. {
  210. string sqlTime = "SELECT value(count(c.id)) FROM c where c.pk = '{0}' and c.school = '{1}' and c.createTime>={2} and c.createTime<={3}";
  211. tempallActivity += await CommonFind.GetSqlValueCount(cosmosClient, "Common", $"SELECT value(count(c.id)) FROM c where c.pk = '{type}' and c.school = '{itemId}'");
  212. lastYearActivity += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(sqlTime, type, itemId, lastYearStart, lastYearEnd));
  213. yearActivity += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(sqlTime, type, itemId, yearStart, yearEnd));
  214. lastWeekActivity += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(sqlTime, type, itemId, lastWeekStart, lastWeekEnd));
  215. weekActivity += await CommonFind.GetSqlValueCount(cosmosClient, "Common", string.Format(sqlTime, type, itemId, weekStart, weekEnd));
  216. }
  217. allLessonCount += tempLessCount;
  218. allActivity += tempallActivity;
  219. allBloblog += tempBloblog;
  220. recSchoolDates.Add(new RecSchoolDate() { id = school.id, name = school.name, dataCount = (tempLessCount + tempallActivity + tempBloblog) });
  221. }
  222. return Ok(new { state = 200, schoolCount = schoolIds.Count, tecCount, stuCount, classCount, roomCount, allLessonCount, lastWeekLessCount, weekLessCount, lastTermLessCount, termLessCount, lastYearLessCount, yearLessCount, allBloblog, lastYearBloblog, yearBloblog, allActivity, lastYearActivity, yearActivity, lastWeekActivity, weekActivity, recSchoolDates });
  223. }
  224. /// <summary>
  225. /// 依据学校Id统计学校分析
  226. /// </summary>
  227. /// <param name="jsonElement"></param>
  228. /// <returns></returns>
  229. [ProducesDefaultResponseType]
  230. [HttpPost("get-idstatis")]
  231. public async Task<IActionResult> GetIdStatis(JsonElement jsonElement)
  232. {
  233. if (!jsonElement.TryGetProperty("schoolId", out JsonElement schoolId)) return BadRequest();
  234. //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署,就不需要,一站多用时,取消注释
  235. int tecCount = 0; //学校教师数量
  236. int stuCount = 0; //学校学生数量
  237. int classCount = 0; //班级数量
  238. int roomCount = 0; //教室教师数量
  239. long allLessCount = 0; //课例总数
  240. int lastWeekLess = 0; //上周的总数
  241. int weekLess = 0; //本周的总数
  242. int lastTermLess = 0; //上学期的总数
  243. int termLess = 0; //本学期的总数
  244. int lessYearLess = 0; //去年的总数
  245. int yearLess = 0; //去年的总数
  246. int allActivity = 0; //学校所有活动
  247. int lastYearActivity = 0; //去年学校所有活动
  248. int yearActivity = 0; //今年学校所有活动
  249. int lastWeekActivity = 0; //上周学校所有活动
  250. int weekActivity = 0; //本周学校所有活动
  251. int allBlob = 0; //所有资源
  252. int lastYearBlob = 0; //去年的资源
  253. int yearBlob = 0; //去年的资源
  254. var cosmosClient = _azureCosmos.GetCosmosClient();
  255. ////分开部署,就不需要,一站多用时,取消注释
  256. //if ($"{site}".Equals(BIConst.Global))
  257. // cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
  258. var (lastWeekStart, lastWeekEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "lastweek"); //计算上周开始/结束时间
  259. var (weekStart, weekEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "week"); //计算本周开始/结束时间
  260. var (lastTermStart, lastTermEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "lastterm"); //计算上学期开始/结束时间
  261. var (termStart, termEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "term"); //计算本学期开始/结束时间
  262. var (lastYearStart, lastYearEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.Parse($"{DateTimeOffset.UtcNow.Year - 1}-1-1"), "year"); //计算去年开始/结束时间
  263. var (yearStart, yearEnd) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "year"); //计算今年开始/结束时间
  264. tecCount = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"Teacher-{schoolId}", "SELECT value(count(c.id)) FROM c WHERE ARRAY_CONTAINS(c.roles, 'teacher', true) AND c.status = 'join'");
  265. stuCount = await JointlySingleQuery.GetValueInt(cosmosClient, "Student", $"Base-{schoolId}");
  266. classCount = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"Class-{schoolId}");
  267. roomCount = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"Room-{schoolId}");
  268. string unifyTimeSql = "select value(count(c.id)) from c where c.startTime>={0} and c.startTime<={1}";
  269. allLessCount = await LessonStatisWay.GetSchoolIdLessonCount(cosmosClient, $"{schoolId}");
  270. lastWeekLess = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"LessonRecord-{schoolId}", string.Format(unifyTimeSql, lastWeekStart, lastWeekEnd));
  271. weekLess = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"LessonRecord-{schoolId}", string.Format(unifyTimeSql, weekStart, lastWeekEnd));
  272. lastTermLess = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"LessonRecord-{schoolId}", string.Format(unifyTimeSql, lastTermStart, lastTermEnd));
  273. termLess = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"LessonRecord-{schoolId}", string.Format(unifyTimeSql, termStart, termEnd));
  274. lessYearLess = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"LessonRecord-{schoolId}", string.Format(unifyTimeSql, lastYearStart, lastYearEnd));
  275. yearLess = await JointlySingleQuery.GetValueInt(cosmosClient, "School", string.Format(unifyTimeSql, yearStart, yearEnd), $"LessonRecord-{schoolId}");
  276. //统计活动
  277. foreach (var type in StaticValue.activityTypes)
  278. {
  279. string sqlTime = "select value(count(c.id)) from c where c.pk = '{0}' and c.school = '{1}' and c.createTime>={2} and c.createTime<={3}";
  280. allActivity += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{type}-{schoolId}", $"select value(count(c.id)) from c where c.pk = '{type}' and c.school = '{schoolId}'");
  281. lastYearActivity += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{type}-{schoolId}", string.Format(sqlTime, type, schoolId, lastYearStart, lastYearEnd));
  282. yearActivity += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{type}-{schoolId}", string.Format(sqlTime, type, schoolId, yearStart, yearEnd));
  283. lastWeekActivity += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{type}-{schoolId}", string.Format(sqlTime, type, schoolId, lastWeekStart, lastWeekEnd));
  284. weekActivity += await JointlySingleQuery.GetValueInt(cosmosClient, "Common", $"{type}-{schoolId}", string.Format(sqlTime, type, schoolId, weekStart, weekEnd));
  285. }
  286. //学校资源
  287. string blobTimeSql = "select value(count(c.id)) from c where c.time>={0} and c.time<={1}";
  288. allBlob = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"Bloblog-{schoolId}");
  289. lastYearBlob = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"Bloblog-{schoolId}", string.Format(blobTimeSql, lastYearStart, lastYearEnd));
  290. yearBlob = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"Bloblog-{schoolId}", string.Format(blobTimeSql, yearStart, yearEnd));
  291. //获取所有的课程记录
  292. List<LessonRecord> records = new();
  293. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<LessonRecord>(queryText: "select value(c) from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"LessonRecord-{schoolId}") }))
  294. {
  295. records.Add(item);
  296. }
  297. List<(string name, int count)> gradeCount = new();
  298. if (records.Count > 0)
  299. {
  300. var response = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync($"{schoolId}", new PartitionKey($"Base"));
  301. School sc = new();
  302. if (response.Status == 200)
  303. {
  304. using var json = await JsonDocument.ParseAsync(response.ContentStream);
  305. sc = json.ToObject<School>();
  306. }
  307. List<string> grades = new();
  308. foreach (var item in records)
  309. {
  310. foreach (string gId in item.grade)
  311. {
  312. if (!grades.Contains(gId))
  313. {
  314. grades.Add(gId);
  315. }
  316. }
  317. }
  318. foreach (var gId in grades)
  319. {
  320. var c = records.Where(r => r.grade.Contains(gId)).Count();
  321. gradeCount.Add((gId, c));
  322. }
  323. }
  324. return Ok(new { state = 200, tecCount, stuCount, classCount, roomCount, allLessCount, lastWeekLess, weekLess, lastTermLess, termLess, lessYearLess, yearLess, allActivity, lastYearActivity, yearActivity, lastWeekActivity, weekActivity, allBlob, lastYearBlob, yearBlob });
  325. }
  326. /// <summary>
  327. /// 未区域学校统计
  328. /// </summary>
  329. /// <param name="jsonElement"></param>
  330. /// <returns></returns>
  331. [ProducesDefaultResponseType]
  332. [HttpPost("get-noarea")]
  333. public async Task<IActionResult> GetNoAreaCnt(JsonElement jsonElement)
  334. {
  335. var cosmosClient = _azureCosmos.GetCosmosClient();
  336. int tLessCnt = 0; //课例
  337. int tActCnt = 0; //活动
  338. List<string> scIds = new(); //学校id集合
  339. string scIdSql = "select value(c.id) from c where c.areaId=null or c.areaId=''";
  340. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<string>(queryText: scIdSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base") }))
  341. {
  342. scIds.Add(item);
  343. }
  344. string comSql = BICommonWay.ManyScSql("c.school", scIds);
  345. string lesSql = $"select value(count(c.id)) from c where c.pk='LessonRecord' and {comSql}";
  346. tLessCnt = await CommonFind.GetSqlValueCount(cosmosClient, new List<string>() { "School", "Teacher" }, lesSql);
  347. comSql = BICommonWay.ManyScSql(" and c.school", scIds);
  348. tActCnt = await ActivityWay.GetCnt(cosmosClient, condSql: comSql);
  349. return Ok(new { state = 200, scCnt = scIds.Count(), tLessCnt, tActCnt });
  350. }
  351. /// <summary>
  352. /// 未加区的学校统计
  353. /// </summary>
  354. /// <param name="jsonElement"></param>
  355. /// <returns></returns>
  356. [ProducesDefaultResponseType]
  357. [HttpPost("get-noareasc")]
  358. public async Task<IActionResult> GetNoAareaScCnt(JsonElement jsonElement)
  359. {
  360. var cosmosClient = _azureCosmos.GetCosmosClient();
  361. var tableClient = _azureStorage.GetCloudTableClient();
  362. var table = tableClient.GetTableReference("BIDDUserInfo");
  363. var (dayS, dayE) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow);
  364. var (weekS, weekE) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "week");
  365. var (monthS, monthE) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "month");
  366. var (termS, termE) = TimeHelper.GetStartOrEnd(DateTimeOffset.UtcNow, "term");
  367. List<AreaAssist> areaAssists = new(); //顾问信息
  368. int tchCnt = 0; //所有区级老师
  369. int stuCnt = 0; //所有区级学生
  370. int tLessCnt = 0; //课例数量
  371. int tActCnt = 0; //活动数量
  372. HashSet<string> tchIds = new();
  373. List<string> scIds = new(); //学校id集合
  374. string scIdSql = "select value(c.id) from c where c.areaId=null or c.areaId=''";
  375. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<string>(queryText: scIdSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base") }))
  376. {
  377. scIds.Add(item);
  378. }
  379. string scsSql = BICommonWay.ManyScSql("c.school", scIds);
  380. string cntSql = $"select value(count(c.id)) from c where {scsSql}";
  381. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryIterator<int>(queryText: cntSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
  382. {
  383. tchCnt += item;
  384. }
  385. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Student").GetItemQueryIterator<int>(queryText: cntSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
  386. {
  387. stuCnt += item;
  388. }
  389. foreach (var scId in scIds)
  390. {
  391. string tchIdSql = $"select value(c.id) from c where array_contains(c.roles,'assist',true) and c.pk='Teacher'";
  392. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<string>(queryText: tchIdSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Teacher-{scId}") }))
  393. {
  394. tchIds.Add(item);
  395. }
  396. }
  397. List<string> noIds = new();
  398. foreach (var item in tchIds)
  399. {
  400. List<DingDingUserInfo> ddUsers = await table.QueryWhereString<DingDingUserInfo>($"PartitionKey eq '{_option.Location}' and tmdId eq '{item}'");
  401. if (ddUsers.Count > 0)
  402. {
  403. foreach (var dduser in ddUsers)
  404. {
  405. AreaAssist areaAssist = new()
  406. {
  407. userId = dduser.userId,
  408. unionId = dduser.unionId,
  409. name = dduser.name,
  410. mobile = dduser.mobile,
  411. avatar = dduser.avatar,
  412. tmdId = dduser.tmdId,
  413. tmdName = dduser.tmdName,
  414. tmdMobile = dduser.tmdMobile,
  415. picture = dduser.picture,
  416. roles = dduser.roles,
  417. schoolIds = dduser.schoolIds
  418. };
  419. areaAssists.Add(areaAssist);
  420. }
  421. }
  422. else { noIds.Add(item); }
  423. }
  424. if (scIds.Count > 0)
  425. {
  426. string lesSql = $"select value(count(c.id)) from c where c.pk='LessonRecord' and {scsSql}";
  427. tLessCnt = await CommonFind.GetSqlValueCount(cosmosClient, new List<string>() { "School", "Teacher" }, lesSql);
  428. string actComSql = BICommonWay.ManyScSql(" and c.school", scIds);
  429. tActCnt = await ActivityWay.GetCnt(cosmosClient, condSql: actComSql);
  430. }
  431. return Ok(new { state = RespondCode.Ok, scCnt = scIds.Count, tchCnt, stuCnt, allCnt = tLessCnt + tActCnt, tLessCnt, tActCnt });
  432. }
  433. #region 新的统计接口
  434. /// <summary>
  435. /// 参与的单个学校统计接口
  436. /// </summary>
  437. /// <param name="jsonElement"></param>
  438. /// <returns></returns>
  439. [ProducesDefaultResponseType]
  440. [HttpPost("get-joinschool")]
  441. public async Task<IActionResult> GetJoinSchoolStats(JsonElement jsonElement)
  442. {
  443. if (jsonElement.TryGetProperty("scId", out JsonElement scId)) return BadRequest();
  444. var cosmosClient = _azureCosmos.GetCosmosClient();
  445. School school = new();
  446. ScStats scStats = new();
  447. var respSc = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync($"{scId}", new PartitionKey($"Base"));
  448. if (respSc.Status == 200)
  449. {
  450. using var fileJson = await JsonDocument.ParseAsync(respSc.ContentStream);
  451. school = fileJson.ToObject<School>();
  452. }
  453. else
  454. {
  455. return Ok(new { state = RespondCode.NotFound, msg = $"未找到id:{scId};相关的学校,请检查" });
  456. }
  457. StatsInfo statsInfo = new();
  458. List<LastYearLessAndAct> lastYear = new();
  459. var scDataStats = await cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").ReadItemStreamAsync($"{DateTimeOffset.UtcNow.Year}-{scId}", new PartitionKey("Statistics"));
  460. if (scDataStats.Status == 200)
  461. {
  462. using var fileJson = await JsonDocument.ParseAsync(scDataStats.ContentStream);
  463. statsInfo = fileJson.ToObject<StatsInfo>();
  464. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator<LastYearLessAndAct>(queryText: $"select c.activity,c.lesson from c where c.id='{DateTimeOffset.UtcNow.Year - 1}-{scId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
  465. {
  466. lastYear.Add(item);
  467. }
  468. }
  469. else
  470. {
  471. statsInfo = await cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").ReadItemAsync<StatsInfo>($"{DateTimeOffset.UtcNow.Year - 1}-{scId}'", new PartitionKey("Statistics"));
  472. lastYear.Add(new LastYearLessAndAct() { activity = statsInfo.activity, lesson = statsInfo.lesson });
  473. }
  474. DateTimeOffset dateTime = DateTimeOffset.UtcNow;
  475. long now = dateTime.ToUnixTimeMilliseconds();
  476. if (!string.IsNullOrEmpty(statsInfo.id))
  477. {
  478. scStats.id = statsInfo.id;
  479. scStats.schoolId = statsInfo.schoolId;
  480. scStats.name = statsInfo.name;
  481. scStats.picture = statsInfo.picture;
  482. scStats.areaId = statsInfo.areaId;
  483. scStats.tch = statsInfo.tch;
  484. scStats.dayTch = statsInfo.dayTch;
  485. scStats.weekTch = statsInfo.weekTch;
  486. scStats.monthTch = statsInfo.monthTch;
  487. scStats.stu = statsInfo.stu;
  488. scStats.dayStu = statsInfo.dayStu;
  489. scStats.weekStu = statsInfo.weekStu;
  490. scStats.monthStu = statsInfo.monthStu;
  491. scStats.room = statsInfo.room;
  492. scStats.witRoom = statsInfo.witRoom;
  493. scStats.resourceCnt = statsInfo.resourceCnt;
  494. scStats.size = statsInfo.size;
  495. scStats.scCreateTime = statsInfo.scCreateTime;
  496. scStats.upTime = statsInfo.upTime;
  497. scStats.lessStats.all = statsInfo.lesson.all;
  498. scStats.lessStats.open = statsInfo.lesson.open;
  499. scStats.lessStats.less = statsInfo.lesson.less;
  500. scStats.lessStats.lastDay = statsInfo.lesson.lastDay;
  501. scStats.lessStats.day = statsInfo.lesson.day;
  502. scStats.lessStats.lastWeek = statsInfo.lesson.lastWeek;
  503. scStats.lessStats.week = statsInfo.lesson.week;
  504. scStats.lessStats.lastTerm = statsInfo.lesson.lastTerm;
  505. scStats.lessStats.term = statsInfo.lesson.term;
  506. scStats.lessStats.month = statsInfo.lesson.month;
  507. scStats.lessStats.lastMonth = statsInfo.lesson.lastMonth;
  508. scStats.lessStats.lastDayInter = statsInfo.lesson.lastDayInter;
  509. scStats.lessStats.dayInter = statsInfo.lesson.dayInter;
  510. scStats.lessStats.lastMonthInter = statsInfo.lesson.lastMonthInter;
  511. scStats.lessStats.monthInter = statsInfo.lesson.monthInter;
  512. scStats.lessStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList()).Sum());
  513. scStats.lessStats.lastYearInter = statsInfo.lesson.lastYearInter;
  514. scStats.lessStats.yearInter = statsInfo.lesson.yearInter;
  515. scStats.lessStats.yearInters = TimeHelper.GetYearMonth(statsInfo.lesson.yearInters, dateTime.Year, dateTime.Month);
  516. scStats.lessStats.year = TimeHelper.GetYearMonth(statsInfo.lesson.year, dateTime.Year, dateTime.Month);
  517. scStats.actStats.all = statsInfo.activity.all;
  518. scStats.actStats.exam = statsInfo.activity.exam;
  519. scStats.actStats.survey = statsInfo.activity.survey;
  520. scStats.actStats.vote = statsInfo.activity.vote;
  521. scStats.actStats.homework = statsInfo.activity.homework;
  522. scStats.actStats.lastDay = statsInfo.activity.lastDay;
  523. scStats.actStats.dayCnt = statsInfo.activity.dayCnt;
  524. scStats.actStats.lastWeek = statsInfo.activity.lastWeek;
  525. scStats.actStats.week = statsInfo.activity.week;
  526. scStats.actStats.lastTerm = statsInfo.activity.lastTerm;
  527. scStats.actStats.term = statsInfo.activity.term;
  528. scStats.actStats.lastMonth = statsInfo.activity.lastMonth;
  529. scStats.actStats.month = statsInfo.activity.month;
  530. scStats.actStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.activity.year).Where(w => w.Count > 0).ToList()).Sum());
  531. scStats.actStats.year = TimeHelper.GetYearMonth(statsInfo.activity.year, dateTime.Year, dateTime.Month);
  532. if (statsInfo.study != null)
  533. {
  534. scStats.srStats.learnTime = statsInfo.study.learnTime;
  535. scStats.srStats.online = statsInfo.study.online;
  536. scStats.srStats.offline = statsInfo.study.offline;
  537. scStats.srStats.classRoom = statsInfo.study.classRoom;
  538. scStats.srStats.submit = statsInfo.study.submit;
  539. scStats.srStats.notStarted = statsInfo.study.notStarted;
  540. scStats.srStats.ongoing = statsInfo.study.ongoing;
  541. scStats.srStats.finish = statsInfo.study.finish;
  542. }
  543. }
  544. var weekLess = BICommonWay.weekDoubleMerge(new List<List<double>>() { statsInfo.lesson.year }, dateTime);
  545. var weekAct = BICommonWay.weekDoubleMerge(new List<List<double>>() { statsInfo.activity.year }, dateTime);
  546. return Ok(new { state = RespondCode.Ok, scStats, weekLess, weekAct });
  547. }
  548. /// <summary>
  549. /// 学校统计
  550. /// 新的数据结构
  551. /// </summary>
  552. /// <param name="jsonElement"></param>
  553. /// <returns></returns>
  554. [ProducesDefaultResponseType]
  555. [HttpPost("get-scstats")]
  556. public async Task<IActionResult> GetSchoolStats(JsonElement jsonElement)
  557. {
  558. if (!jsonElement.TryGetProperty("scId", out JsonElement scId)) return BadRequest();
  559. var cosmosClient = _azureCosmos.GetCosmosClient();
  560. School school = new();
  561. ScStats scStats = new();
  562. var respSc = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync($"{scId}", new PartitionKey($"Base"));
  563. if (respSc.Status == 200)
  564. {
  565. using var fileJson = await JsonDocument.ParseAsync(respSc.ContentStream);
  566. school = fileJson.ToObject<School>();
  567. }
  568. else
  569. return Ok(new { state = RespondCode.NotFound, msg = $"未找到id:{scId};相关的学校,请检查" });
  570. try
  571. {
  572. StatsInfo statsInfo = new();
  573. List<LastYearLessAndAct> lastYear = new();
  574. var scDataStats = await cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").ReadItemStreamAsync($"{DateTimeOffset.UtcNow.Year}-{scId}", new PartitionKey("Statistics"));
  575. if (scDataStats.Status == 200)
  576. {
  577. using var fileJson = await JsonDocument.ParseAsync(scDataStats.ContentStream);
  578. statsInfo = fileJson.ToObject<StatsInfo>();
  579. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator<LastYearLessAndAct>(queryText: $"select c.activity,c.lesson from c where c.id='{DateTimeOffset.UtcNow.Year - 1}-{scId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
  580. {
  581. lastYear.Add(item);
  582. }
  583. }
  584. else
  585. {
  586. statsInfo = await cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").ReadItemAsync<StatsInfo>($"{DateTimeOffset.UtcNow.Year - 1}-{scId}'", new PartitionKey("Statistics"));
  587. lastYear.Add(new LastYearLessAndAct() { activity = statsInfo.activity, lesson = statsInfo.lesson });
  588. }
  589. DateTimeOffset dateTime = DateTimeOffset.UtcNow;
  590. long now = dateTime.ToUnixTimeMilliseconds();
  591. if (!string.IsNullOrEmpty(statsInfo.id))
  592. {
  593. scStats.id = statsInfo.id;
  594. scStats.schoolId = statsInfo.schoolId;
  595. scStats.name = statsInfo.name;
  596. scStats.picture = statsInfo.picture;
  597. scStats.areaId = statsInfo.areaId;
  598. scStats.tch = statsInfo.tch;
  599. scStats.dayTch = statsInfo.dayTch;
  600. scStats.weekTch = statsInfo.weekTch;
  601. scStats.monthTch = statsInfo.monthTch;
  602. scStats.stu = statsInfo.stu;
  603. scStats.dayStu = statsInfo.dayStu;
  604. scStats.weekStu = statsInfo.weekStu;
  605. scStats.monthStu = statsInfo.monthStu;
  606. scStats.room = statsInfo.room;
  607. scStats.witRoom = statsInfo.witRoom;
  608. scStats.resourceCnt = statsInfo.resourceCnt;
  609. scStats.size = statsInfo.size;
  610. scStats.scCreateTime = statsInfo.scCreateTime;
  611. scStats.upTime = statsInfo.upTime;
  612. scStats.lessStats.all = statsInfo.lesson.all;
  613. scStats.lessStats.open = statsInfo.lesson.open;
  614. scStats.lessStats.less = statsInfo.lesson.less;
  615. scStats.lessStats.lastDay = statsInfo.lesson.lastDay;
  616. scStats.lessStats.day = statsInfo.lesson.day;
  617. scStats.lessStats.lastWeek = statsInfo.lesson.lastWeek;
  618. scStats.lessStats.week = statsInfo.lesson.week;
  619. scStats.lessStats.lastTerm = statsInfo.lesson.lastTerm;
  620. scStats.lessStats.term = statsInfo.lesson.term;
  621. scStats.lessStats.month = statsInfo.lesson.month;
  622. scStats.lessStats.lastMonth = statsInfo.lesson.lastMonth;
  623. scStats.lessStats.lastDayInter = statsInfo.lesson.lastDayInter;
  624. scStats.lessStats.dayInter = statsInfo.lesson.dayInter;
  625. scStats.lessStats.lastMonthInter = statsInfo.lesson.lastMonthInter;
  626. scStats.lessStats.monthInter = statsInfo.lesson.monthInter;
  627. scStats.lessStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList()).Sum());
  628. scStats.lessStats.lastYearInter = statsInfo.lesson.lastYearInter;
  629. scStats.lessStats.yearInter = statsInfo.lesson.yearInter;
  630. scStats.lessStats.yearInters = TimeHelper.GetYearMonth(statsInfo.lesson.yearInters, dateTime.Year, dateTime.Month);
  631. scStats.lessStats.year = TimeHelper.GetYearMonth(statsInfo.lesson.year, dateTime.Year, dateTime.Month);
  632. scStats.actStats.all = statsInfo.activity.all;
  633. scStats.actStats.exam = statsInfo.activity.exam;
  634. scStats.actStats.survey = statsInfo.activity.survey;
  635. scStats.actStats.vote = statsInfo.activity.vote;
  636. scStats.actStats.homework = statsInfo.activity.homework;
  637. scStats.actStats.lastDay = statsInfo.activity.lastDay;
  638. scStats.actStats.dayCnt = statsInfo.activity.dayCnt;
  639. scStats.actStats.lastWeek = statsInfo.activity.lastWeek;
  640. scStats.actStats.week = statsInfo.activity.week;
  641. scStats.actStats.lastTerm = statsInfo.activity.lastTerm;
  642. scStats.actStats.term = statsInfo.activity.term;
  643. scStats.actStats.lastMonth = statsInfo.activity.lastMonth;
  644. scStats.actStats.month = statsInfo.activity.month;
  645. scStats.actStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.activity.year).Where(w => w.Count > 0).ToList()).Sum());
  646. scStats.actStats.year = TimeHelper.GetYearMonth(statsInfo.activity.year, dateTime.Year, dateTime.Month);
  647. if (statsInfo.study != null)
  648. {
  649. scStats.srStats.learnTime = statsInfo.study.learnTime;
  650. scStats.srStats.online = statsInfo.study.online;
  651. scStats.srStats.offline = statsInfo.study.offline;
  652. scStats.srStats.classRoom = statsInfo.study.classRoom;
  653. scStats.srStats.submit = statsInfo.study.submit;
  654. scStats.srStats.notStarted = statsInfo.study.notStarted;
  655. scStats.srStats.ongoing = statsInfo.study.ongoing;
  656. scStats.srStats.finish = statsInfo.study.finish;
  657. }
  658. }
  659. //if(statsInfo.upTime > 0 && !isnew) {
  660. // long timedis = 20 * 60 * 1000;//20分钟
  661. // if (now - statsInfo.upTime < timedis)
  662. // isnew = true;
  663. //}
  664. //if (statsInfo.upTime == 0 && isnew)
  665. // statsInfo.upTime = now;
  666. //if (statsInfo.upTime == 0 && isnew)
  667. // statsInfo.upTime = DateTimeOffset.UtcNow.Add(new TimeSpan(-1, 0, 0, 0)).ToUnixTimeMilliseconds();
  668. //if (!isnew)
  669. //{
  670. // //Function 处理Function 更新或者新增
  671. // //_ = _httpTrigger.RequestHttpTrigger(new { schoolId = $"{school.id}"}, _option.Location, "stats-sc-info");
  672. // //异步方法处理 同步更新
  673. // statsInfo = await SchoolStatsWay.GetSingleSc(cosmosClient, school.id);
  674. // statsInfo.upTime = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
  675. // if (isExist)
  676. // statsInfo = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReplaceItemAsync<StatsInfo>(statsInfo, statsInfo.id, new PartitionKey("Stats"));
  677. // else
  678. // statsInfo = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").CreateItemAsync<StatsInfo>(statsInfo, new PartitionKey("Stats"));
  679. //}
  680. return Ok(new { state = RespondCode.Ok, scStats });
  681. }
  682. catch (Exception ex)
  683. {
  684. await _dingDing.SendBotMsg($"BI,{_option.Location} /school/set-scstats() \n {ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
  685. return BadRequest();
  686. }
  687. }
  688. /// <summary>
  689. /// 依据身份统计学校
  690. /// 新的数据结构
  691. /// </summary>
  692. /// <param name="jsonElement"></param>
  693. /// <returns></returns>
  694. [ProducesDefaultResponseType]
  695. [HttpPost("get-rolestats")]
  696. public async Task<IActionResult> GetRoleStats(JsonElement jsonElement)
  697. {
  698. //jsonElement.TryGetProperty("areaId", out JsonElement areaId);
  699. jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
  700. jsonElement.TryGetProperty("role", out JsonElement role);
  701. //if (string.IsNullOrEmpty($"{areaId}") && string.IsNullOrEmpty($"{tmdId}"))
  702. // return Ok(new { stats = RespondCode.ParamsError, msg = "areaId/tmdId两者参数不能都为空!" });
  703. var cosmosClient = _azureCosmos.GetCosmosClient();
  704. Area area = null;
  705. ScStats areaScStats = new();
  706. List<string> scIds = new();
  707. DateTimeOffset dateTime = DateTimeOffset.UtcNow;
  708. List<ScStats> scStatss = new();
  709. List<StatsInfo> statsInfos = new();
  710. StringBuilder scSql = new("select c.id,c.name,c.size,c.scale,c.serial,c.service,c.hard from c");
  711. StringBuilder lastScSql = new("select value(c.id) from c");
  712. //if (!string.IsNullOrEmpty($"{areaId}"))
  713. //{
  714. // scSql.Append($" where c.areaId='{areaId}'");
  715. // lastScSql.Append($" where c.areaId='{areaId}'");
  716. // var respSc = await cosmosClient.GetContainer(Constant.TEAMModelOS, "Normal").ReadItemStreamAsync($"{areaId}", new PartitionKey($"Base-Area"));
  717. // if (respSc.Status == 200)
  718. // {
  719. // using var fileJson = await JsonDocument.ParseAsync(respSc.ContentStream);
  720. // area = fileJson.ToObject<Area>();
  721. // }
  722. // else
  723. // return Ok(new { state = RespondCode.NotFound, msg = $"未找到id:{areaId};相关的学区,请检查!" });
  724. //}
  725. if (!string.IsNullOrEmpty($"{tmdId}"))
  726. {
  727. switch ($"{role}")
  728. {
  729. case "assist":
  730. scSql.Append($" join a in c.assists where a.id='{tmdId}'");
  731. lastScSql.Append($" join a in c.assists where a.id='{tmdId}'");
  732. break;
  733. case "sales":
  734. scSql.Append($" join a in c.sales where a.id='{tmdId}'");
  735. lastScSql.Append($" join a in c.sales where a.id='{tmdId}'");
  736. break;
  737. default:
  738. return Ok(new { state = RespondCode.ParamsError, msg = "role参数错误" });
  739. }
  740. }
  741. List<BIRelStats> bIRelStats = new();
  742. List<string> tempProduct = new();
  743. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<BIRelStats>(queryText: scSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("BIRel") }))
  744. {
  745. bIRelStats.Add(item);
  746. tempProduct.AddRange(item.serial);
  747. tempProduct.AddRange(item.service);
  748. tempProduct.AddRange(item.hard);
  749. }
  750. if (bIRelStats.Count > 0)
  751. scIds = bIRelStats.Select(s => s.id).ToList();
  752. //scIds = await CommonFind.FindScIds(cosmosClient, scSql.ToString(), "BIRel");
  753. StringBuilder statsSql = new($"select value(c) from c where c.year={dateTime.Year}");
  754. if (scIds.Count > 0)
  755. {
  756. statsSql.Append($" and {BICommonWay.ManyScSql("c.id", scIds, $"{dateTime.Year}-")}");
  757. }
  758. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator<StatsInfo>(queryText: statsSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
  759. {
  760. statsInfos.Add(item);
  761. }
  762. StringBuilder lastStsSql = new($"select c.lesson,c.activity from c where c.year={dateTime.Year}");
  763. if (scIds.Count > 0)
  764. {
  765. lastStsSql.Append($" and {BICommonWay.ManyScSql("c.id", scIds, $"{DateTimeOffset.UtcNow.Year - 1}-")}");
  766. }
  767. List<LastYearLessAndAct> lastYear = new();
  768. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator<LastYearLessAndAct>(queryText: lastStsSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
  769. {
  770. lastYear.Add(item);
  771. }
  772. StatsInfo statsInfo = null;
  773. statsInfo = SchoolStatsWay.GetAreaStats(cosmosClient, _option, statsInfos, scIds, area);
  774. if (statsInfo != null)
  775. {
  776. areaScStats.id = statsInfo.id;
  777. areaScStats.schoolId = statsInfo.schoolId;
  778. areaScStats.name = statsInfo.name;
  779. areaScStats.picture = statsInfo.picture;
  780. areaScStats.areaId = statsInfo.areaId;
  781. areaScStats.tch = statsInfo.tch;
  782. areaScStats.dayTch = statsInfo.dayTch;
  783. areaScStats.weekTch = statsInfo.weekTch;
  784. areaScStats.monthTch = statsInfo.monthTch;
  785. areaScStats.stu = statsInfo.stu;
  786. areaScStats.dayStu = statsInfo.dayStu;
  787. areaScStats.weekStu = statsInfo.weekStu;
  788. areaScStats.monthStu = statsInfo.monthStu;
  789. areaScStats.room = statsInfo.room;
  790. areaScStats.witRoom = statsInfo.witRoom;
  791. areaScStats.resourceCnt = statsInfo.witRoom;
  792. areaScStats.size = statsInfo.size;
  793. areaScStats.scCreateTime = statsInfo.scCreateTime;
  794. areaScStats.upTime = statsInfo.upTime;
  795. areaScStats.lessStats.open = statsInfo.lesson.all;
  796. areaScStats.lessStats.open = statsInfo.lesson.open;
  797. areaScStats.lessStats.less = statsInfo.lesson.less;
  798. areaScStats.lessStats.lastDay = statsInfo.lesson.lastDay;
  799. areaScStats.lessStats.day = statsInfo.lesson.day;
  800. areaScStats.lessStats.lastWeek = statsInfo.lesson.lastWeek;
  801. areaScStats.lessStats.week = statsInfo.lesson.week;
  802. areaScStats.lessStats.lastTerm = statsInfo.lesson.lastTerm;
  803. areaScStats.lessStats.term = statsInfo.lesson.term;
  804. areaScStats.lessStats.lastDayInter = statsInfo.lesson.lastDayInter;
  805. areaScStats.lessStats.dayInter = statsInfo.lesson.dayInter;
  806. areaScStats.lessStats.lastMonthInter = statsInfo.lesson.lastMonthInter;
  807. areaScStats.lessStats.monthInter = statsInfo.lesson.monthInter;
  808. areaScStats.lessStats.lastYearInter = statsInfo.lesson.lastYearInter;
  809. areaScStats.lessStats.yearInter = statsInfo.lesson.yearInter;
  810. areaScStats.lessStats.yearInters = TimeHelper.GetYearMonth(statsInfo.lesson.yearInters, dateTime.Year, dateTime.Month);
  811. areaScStats.lessStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList()).Sum());
  812. areaScStats.lessStats.year = TimeHelper.GetYearMonth(statsInfo.lesson.year, dateTime.Year, dateTime.Month);
  813. areaScStats.actStats.all = statsInfo.activity.all;
  814. areaScStats.actStats.exam = statsInfo.activity.exam;
  815. areaScStats.actStats.survey = statsInfo.activity.survey;
  816. areaScStats.actStats.vote = statsInfo.activity.vote;
  817. areaScStats.actStats.homework = statsInfo.activity.homework;
  818. areaScStats.actStats.lastDay = statsInfo.activity.lastDay;
  819. areaScStats.actStats.dayCnt = statsInfo.activity.dayCnt;
  820. areaScStats.actStats.lastWeek = statsInfo.activity.lastWeek;
  821. areaScStats.actStats.week = statsInfo.activity.week;
  822. areaScStats.actStats.lastTerm = statsInfo.activity.lastTerm;
  823. areaScStats.actStats.term = statsInfo.activity.term;
  824. areaScStats.actStats.lastMonth = statsInfo.activity.lastMonth;
  825. areaScStats.actStats.month = statsInfo.activity.month;
  826. areaScStats.actStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.activity.year).Where(w => w.Count > 0).ToList()).Sum());
  827. areaScStats.actStats.year = TimeHelper.GetYearMonth(statsInfo.activity.year, dateTime.Year, dateTime.Month);
  828. if (statsInfo.study != null)
  829. {
  830. areaScStats.srStats.learnTime = statsInfo.study.learnTime;
  831. areaScStats.srStats.online = statsInfo.study.online;
  832. areaScStats.srStats.offline = statsInfo.study.offline;
  833. areaScStats.srStats.classRoom = statsInfo.study.classRoom;
  834. areaScStats.srStats.submit = statsInfo.study.submit;
  835. areaScStats.srStats.notStarted = statsInfo.study.notStarted;
  836. areaScStats.srStats.ongoing = statsInfo.study.ongoing;
  837. areaScStats.srStats.finish = statsInfo.study.finish;
  838. }
  839. }
  840. var scInfos = statsInfos.Select(s => new { s.id, s.schoolId, s.name, s.picture, size = bIRelStats.Where(w => w.id.Equals(s.schoolId)).Select(si => si.size).FirstOrDefault(), scale = bIRelStats.Where(w => w.id.Equals(s.schoolId)).Select(si => si.scale).FirstOrDefault(), serial = bIRelStats.Where(w => w.id.Equals(s.schoolId)).Select(si => si.serial).FirstOrDefault(), service = bIRelStats.Where(w => w.id.Equals(s.schoolId)).Select(si => si.service).FirstOrDefault(), hard = bIRelStats.Where(w => w.id.Equals(s.schoolId)).Select(si => si.hard).FirstOrDefault(), actLess = (s.lesson.all + s.activity.all), actCnt = s.activity.all, lessCnt = s.lesson.all, lessOpen = s.lesson.open, lessUp = s.lesson.less }).ToList();
  841. var productGroup = tempProduct.GroupBy(g => g).Select(s => new { code = s.Key, cnt = s.Count() });
  842. return Ok(new { state = RespondCode.Ok, areaScStats, scInfos, productGroup });
  843. }
  844. /// <summary>
  845. /// 依据区级Id统计 新数据结构
  846. /// </summary>
  847. /// <param name="jsonElement"></param>
  848. /// <returns></returns>
  849. [ProducesDefaultResponseType]
  850. [HttpPost("get-areastats")]
  851. public async Task<IActionResult> GetAreaStats(JsonElement jsonElement)
  852. {
  853. if (!jsonElement.TryGetProperty("areaId", out JsonElement areaId)) return BadRequest();
  854. var redisClinet = _azureRedis.GetRedisClient(8);
  855. var cosmosClient = _azureCosmos.GetCosmosClient();
  856. DateTimeOffset dateTime = DateTimeOffset.UtcNow;
  857. Area area = null;
  858. AreaStats areaScStats = new();
  859. List<string> scIds = new();
  860. List<BISchoolInfo> schoolInfos = new();
  861. List<ScStats> scStatss = new();
  862. List<StatsInfo> statsInfos = new();
  863. long useSize = 0;
  864. Dictionary<string, double?> typeStics = new(); //所有类型
  865. var (weekS, weekE) = TimeHelper.GetStartOrEnd(dateTime, "week"); //计算本周开始/结束时间
  866. var (mthS, mthE) = TimeHelper.GetStartOrEnd(dateTime, "month"); //本月开始/结束时间
  867. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<BISchoolInfo>(queryText: $"select c.id,c.name,c.picture,c.areaId,c.areaName,c.size,c.scale,c.assists,c.sales,c.createDate,c.serial,c.service,c.hard from c where c.areaId='{areaId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("BIRel") }))
  868. {
  869. schoolInfos.Add(item);
  870. }
  871. if (schoolInfos.Count > 0)
  872. scIds = schoolInfos.Select(s => s.id).ToList();
  873. StringBuilder statsSql = new($"select value(c) from c where c.year={dateTime.Year}");
  874. if (scIds.Count > 0)
  875. statsSql.Append($" and {BICommonWay.ManyScSql("c.id", scIds, $"{dateTime.Year}-")}");
  876. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator<StatsInfo>(queryText: statsSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
  877. {
  878. statsInfos.Add(item);
  879. }
  880. StringBuilder lastStsSql = new($"select c.lesson,c.activity from c where c.year={dateTime.Year - 1}");
  881. if (scIds.Count > 0)
  882. {
  883. lastStsSql.Append($" and {BICommonWay.ManyScSql("c.id", scIds, $"{dateTime.Year - 1}-")}");
  884. }
  885. List<LastYearLessAndAct> lastYear = new();
  886. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator<LastYearLessAndAct>(queryText: lastStsSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
  887. {
  888. lastYear.Add(item);
  889. }
  890. StatsInfo statsInfo = null;
  891. statsInfo = SchoolStatsWay.GetAreaStats(cosmosClient, _option, statsInfos, scIds, area);
  892. if (statsInfo != null)
  893. {
  894. //areaScStats = new
  895. //{
  896. // statsInfo.id,
  897. // statsInfo.schoolId,
  898. // statsInfo.name,
  899. // statsInfo.picture,
  900. // statsInfo.areaId,
  901. // weekSc = schoolInfos.Where(w => w.createDate >= weekS && w.createDate <= weekE).Count(),
  902. // monthSc = schoolInfos.Where(w => w.createDate >= mthS && w.createDate <= mthE).Count(),
  903. // statsInfo.tch,
  904. // statsInfo.dayTch,
  905. // statsInfo.weekTch,
  906. // statsInfo.monthTch,
  907. // statsInfo.stu,
  908. // statsInfo.dayStu,
  909. // statsInfo.weekStu,
  910. // statsInfo.monthStu,
  911. // statsInfo.room,
  912. // statsInfo.witRoom,
  913. // statsInfo.size,
  914. // statsInfo.scCreateTime,
  915. // statsInfo.upTime,
  916. // lessStats = new
  917. // {
  918. // statsInfo.lesson.all,
  919. // statsInfo.lesson.open,
  920. // statsInfo.lesson.less,
  921. // statsInfo.lesson.lastDay,
  922. // statsInfo.lesson.day,
  923. // statsInfo.lesson.lastWeek,
  924. // statsInfo.lesson.week,
  925. // statsInfo.lesson.lastTerm,
  926. // statsInfo.lesson.term,
  927. // statsInfo.lesson.lastDayInter,
  928. // statsInfo.lesson.dayInter,
  929. // statsInfo.lesson.lastMonthInter,
  930. // statsInfo.lesson.monthInter,
  931. // statsInfo.lesson.lastYearInter,
  932. // statsInfo.lesson.yearInter,
  933. // yearInters = TimeHelper.GetYearMonth(statsInfo.lesson.yearInters, dateTime.Year, dateTime.Month),
  934. // lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList()).Sum()),
  935. // year = TimeHelper.GetYearMonth(statsInfo.lesson.year, dateTime.Year, dateTime.Month)
  936. // },
  937. // actStats = new
  938. // {
  939. // statsInfo.activity.all,
  940. // statsInfo.activity.exam,
  941. // statsInfo.activity.survey,
  942. // statsInfo.activity.vote,
  943. // statsInfo.activity.homework,
  944. // statsInfo.activity.lastDay,
  945. // statsInfo.activity.dayCnt,
  946. // statsInfo.activity.lastWeek,
  947. // statsInfo.activity.week,
  948. // statsInfo.activity.lastTerm,
  949. // statsInfo.activity.term,
  950. // lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.activity.year).Where(w => w.Count > 0).ToList()).Sum()),
  951. // year = TimeHelper.GetYearMonth(statsInfo.activity.year, dateTime.Year, dateTime.Month)
  952. // },
  953. // srStats = new
  954. // {
  955. // statsInfo.study.learnTime,
  956. // statsInfo.study.online,
  957. // statsInfo.study.offline,
  958. // statsInfo.study.classRoom,
  959. // statsInfo.study.submit,
  960. // statsInfo.study.notStarted,
  961. // statsInfo.study.ongoing,
  962. // statsInfo.study.finish
  963. // }
  964. //};
  965. areaScStats.id = statsInfo.id;
  966. areaScStats.name = statsInfo.name;
  967. areaScStats.weekSc = schoolInfos.Where(w => w.createDate >= weekS && w.createDate <= weekE).Count();
  968. areaScStats.monthSc = schoolInfos.Where(w => w.createDate >= mthS && w.createDate <= mthE).Count();
  969. areaScStats.tch = statsInfo.tch;
  970. areaScStats.dayTch = statsInfo.dayTch;
  971. areaScStats.weekTch = statsInfo.weekTch;
  972. areaScStats.monthTch = statsInfo.monthTch;
  973. areaScStats.stu = statsInfo.stu;
  974. areaScStats.dayStu = statsInfo.dayStu;
  975. areaScStats.weekStu = statsInfo.weekStu;
  976. areaScStats.monthStu = statsInfo.monthStu;
  977. areaScStats.room = statsInfo.room;
  978. areaScStats.witRoom = statsInfo.witRoom;
  979. areaScStats.resourceCnt = statsInfo.resourceCnt;
  980. areaScStats.size = statsInfo.size;
  981. //areaScStats.useSize = statsInfo.useSize;
  982. areaScStats.lessStats.open = statsInfo.lesson.all;
  983. areaScStats.lessStats.open = statsInfo.lesson.open;
  984. areaScStats.lessStats.less = statsInfo.lesson.less;
  985. areaScStats.lessStats.lastDay = statsInfo.lesson.lastDay;
  986. areaScStats.lessStats.day = statsInfo.lesson.day;
  987. areaScStats.lessStats.lastWeek = statsInfo.lesson.lastWeek;
  988. areaScStats.lessStats.week = statsInfo.lesson.week;
  989. areaScStats.lessStats.lastTerm = statsInfo.lesson.lastTerm;
  990. areaScStats.lessStats.term = statsInfo.lesson.term;
  991. areaScStats.lessStats.lastDayInter = statsInfo.lesson.lastDayInter;
  992. areaScStats.lessStats.dayInter = statsInfo.lesson.dayInter;
  993. areaScStats.lessStats.lastMonthInter = statsInfo.lesson.lastMonthInter;
  994. areaScStats.lessStats.monthInter = statsInfo.lesson.monthInter;
  995. areaScStats.lessStats.lastYearInter = statsInfo.lesson.lastYearInter;
  996. areaScStats.lessStats.yearInter = statsInfo.lesson.yearInter;
  997. areaScStats.lessStats.yearInters = TimeHelper.GetYearMonth(statsInfo.lesson.yearInters, dateTime.Year, dateTime.Month);
  998. areaScStats.lessStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList()).Sum());
  999. areaScStats.lessStats.year = TimeHelper.GetYearMonth(statsInfo.lesson.year, dateTime.Year, dateTime.Month);
  1000. areaScStats.actStats.all = statsInfo.activity.all;
  1001. areaScStats.actStats.exam = statsInfo.activity.exam;
  1002. areaScStats.actStats.survey = statsInfo.activity.survey;
  1003. areaScStats.actStats.vote = statsInfo.activity.vote;
  1004. areaScStats.actStats.homework = statsInfo.activity.homework;
  1005. areaScStats.actStats.lastDay = statsInfo.activity.lastDay;
  1006. areaScStats.actStats.dayCnt = statsInfo.activity.dayCnt;
  1007. areaScStats.actStats.lastWeek = statsInfo.activity.lastWeek;
  1008. areaScStats.actStats.week = statsInfo.activity.week;
  1009. areaScStats.actStats.lastTerm = statsInfo.activity.lastTerm;
  1010. areaScStats.actStats.term = statsInfo.activity.term;
  1011. areaScStats.actStats.lastMonth = statsInfo.activity.lastMonth;
  1012. areaScStats.actStats.month = statsInfo.activity.month;
  1013. areaScStats.actStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.activity.year).Where(w => w.Count > 0).ToList()).Sum());
  1014. areaScStats.actStats.year = TimeHelper.GetYearMonth(statsInfo.activity.year, dateTime.Year, dateTime.Month);
  1015. if (statsInfo.study != null)
  1016. {
  1017. areaScStats.srStats.learnTime = statsInfo.study.learnTime;
  1018. areaScStats.srStats.online = statsInfo.study.online;
  1019. areaScStats.srStats.offline = statsInfo.study.offline;
  1020. areaScStats.srStats.classRoom = statsInfo.study.classRoom;
  1021. areaScStats.srStats.submit = statsInfo.study.submit;
  1022. areaScStats.srStats.notStarted = statsInfo.study.notStarted;
  1023. areaScStats.srStats.ongoing = statsInfo.study.ongoing;
  1024. areaScStats.srStats.finish = statsInfo.study.finish;
  1025. }
  1026. }
  1027. List<double> weekLess = BICommonWay.weekDoubleMerge(statsInfos.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList(), dateTime);
  1028. schoolInfos.ForEach(fe =>
  1029. {
  1030. var tempSts = statsInfos.Find(f => f.schoolId.Equals(fe.id));
  1031. if (tempSts != null)
  1032. {
  1033. fe.less = tempSts.lesson.all;
  1034. fe.lastWeekLess = tempSts.lesson.lastWeek;
  1035. fe.monthLess = tempSts.lesson.month;
  1036. }
  1037. });
  1038. List<IdInfo> assits = new();
  1039. schoolInfos.ForEach(fe => assits.AddRange(fe.assists));
  1040. List<IdInfo> saless = new();
  1041. schoolInfos.ForEach(fe => saless.AddRange(fe.sales));
  1042. foreach (var itemId in scIds)
  1043. {
  1044. RedisValue value = redisClinet.HashGet($"Blob:Record", itemId);
  1045. if (value != default && !value.IsNullOrEmpty)
  1046. {
  1047. JsonElement record = value.ToString().ToObject<JsonElement>();
  1048. if (record.TryGetInt64(out long blobsize))
  1049. {
  1050. useSize += blobsize;
  1051. }
  1052. }
  1053. SortedSetEntry[] Scores = redisClinet.SortedSetRangeByScoreWithScores($"Blob:Catalog:{itemId}");
  1054. if (Scores.Length > 0)
  1055. {
  1056. Dictionary<string, double?> schoolStics = new(); //学校空间
  1057. foreach (var score in Scores)
  1058. {
  1059. double val = score.Score;
  1060. string key = score.Element.ToString();
  1061. schoolStics.Add(key, val);
  1062. }
  1063. typeStics = typeStics.Concat(schoolStics).GroupBy(g => g.Key).ToDictionary(k => k.Key, k => k.Sum(kvp => kvp.Value)); //lamebda表达式
  1064. }
  1065. }
  1066. return Ok(new { state = RespondCode.Ok, areaScStats, schoolInfos, weekLess, assists = assits.Where((w, i) => assits.FindIndex(s => s.id.Equals(w.id)) == i).ToList(), saless = saless.Where((w, i) => saless.FindIndex(f => f.id.Equals(w.id)) == i).ToList(), useSize, typeStics = typeStics.ToList() });
  1067. }
  1068. /// <summary>
  1069. /// 所有区级的统计
  1070. /// </summary>
  1071. /// <param name="jsonElement"></param>
  1072. /// <returns></returns>
  1073. [ProducesDefaultResponseType]
  1074. [HttpPost("get-allscstats")]
  1075. public async Task<IActionResult> GetAllAreaStats(JsonElement jsonElement)
  1076. {
  1077. var cosmosClient = _azureCosmos.GetCosmosClient();
  1078. AllScStats allScStats = new();
  1079. List<StatsInfo> statsInfos = new();
  1080. DateTimeOffset dateTime = DateTimeOffset.UtcNow;
  1081. var (lastDayS, lastdayE) = TimeHelper.GetStartOrEnd(dateTime.AddDays(-1)); //昨天开始时间
  1082. var (dayS, dayE) = TimeHelper.GetStartOrEnd(dateTime); //今天开始时间
  1083. var (lastWeekS, lastWeekE) = TimeHelper.GetStartOrEnd(dateTime, "lastweek"); //计算上周开始/结束时间
  1084. var (weekS, weekE) = TimeHelper.GetStartOrEnd(dateTime, "week"); //计算本周开始/结束时间
  1085. var (lastTermS, lastTermE) = TimeHelper.GetStartOrEnd(dateTime, "lastterm"); //计算上学期开始/结束时间
  1086. var (termS, termE) = TimeHelper.GetStartOrEnd(dateTime, "term"); //计算本学期开始/结束时间
  1087. var (lastMthS, LastmthE) = TimeHelper.GetStartOrEnd(dateTime, "lastMonth"); //上月开始/结束时间
  1088. var (mthS, mthE) = TimeHelper.GetStartOrEnd(dateTime, "month"); //本月开始/结束时间
  1089. var (lastYearS, lastYearE) = TimeHelper.GetStartOrEnd(DateTimeOffset.Parse($"{dateTime.Year - 1}-1-1"), "year"); //计算去年开始/结束时间
  1090. var (yearS, yearE) = TimeHelper.GetStartOrEnd(dateTime, "year"); //计算今年开始/结束时间
  1091. string sql = "select value(count(c.id)) from c ";
  1092. List<EasyInfo> easyInfos = new();
  1093. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Normal").GetItemQueryIterator<EasyInfo>(queryText: $"select c.id,c.code,c.name from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base-Area") }))
  1094. {
  1095. easyInfos.Add(item);
  1096. }
  1097. int scCnt = await JointlySingleQuery.GetValueInt(cosmosClient, "Normal", "Base", sql);
  1098. int scWeekCnt = await JointlySingleQuery.GetValueInt(cosmosClient, "Normal", "Base", $"{sql} where c.createTime >= {weekS} and c.createTime <= {weekE}");
  1099. int scMonthCnt = await JointlySingleQuery.GetValueInt(cosmosClient, "Normal", "Base", $"{sql} where c.createTime >= {mthS} and c.createTime <= {mthE}");
  1100. List<string> scId = new();
  1101. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator<StatsInfo>(queryText: $"select value(c) from c where c.year={dateTime.Year}", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
  1102. {
  1103. statsInfos.Add(item);
  1104. if (item.activity.year.Count > 366)
  1105. {
  1106. scId.Add(item.id);
  1107. }
  1108. }
  1109. List<AreaInfoStats> areaGroup = statsInfos.GroupBy(p => p.areaId).Select(s => new AreaInfoStats() { areaId = s.Key, name = easyInfos.Where(f => f.id.Equals(s.Key)).Select(sn=>sn.name).FirstOrDefault(), lessAct = (s.Select(sl => sl.lesson.all).Sum() + s.Select(sa => sa.activity.all).Sum()), lessCnt = s.Select(sl => sl.lesson.all).Sum(), actCnt = s.Select(sa => sa.activity.all).Sum() }).ToList();
  1110. StatsInfo statsInfo = null;
  1111. statsInfo = SchoolStatsWay.GetAreaStats(cosmosClient, _option, statsInfos);
  1112. if (statsInfo != null)
  1113. {
  1114. allScStats.areaCnt = easyInfos.Count;
  1115. allScStats.sc = statsInfos.Count;
  1116. allScStats.weekSc = scWeekCnt;
  1117. allScStats.monthSc = scMonthCnt;
  1118. allScStats.tch = statsInfo.tch;
  1119. allScStats.dayTch = statsInfo.dayTch;
  1120. allScStats.weekTch = statsInfo.weekTch;
  1121. allScStats.monthTch = statsInfo.monthTch;
  1122. allScStats.stu = statsInfo.stu;
  1123. allScStats.dayStu = statsInfo.dayStu;
  1124. allScStats.weekStu = statsInfo.weekStu;
  1125. allScStats.monthStu = statsInfo.monthStu;
  1126. allScStats.room = statsInfo.room;
  1127. allScStats.witRoom = statsInfo.witRoom;
  1128. allScStats.resourceCnt = statsInfo.resourceCnt;
  1129. allScStats.size = statsInfo.size;
  1130. allScStats.scCreateTime = statsInfo.scCreateTime;
  1131. allScStats.upTime = statsInfo.upTime;
  1132. allScStats.lessStats.open = statsInfo.lesson.all;
  1133. allScStats.lessStats.open = statsInfo.lesson.open;
  1134. allScStats.lessStats.less = statsInfo.lesson.less;
  1135. allScStats.lessStats.lastDay = statsInfo.lesson.lastDay;
  1136. allScStats.lessStats.day = statsInfo.lesson.day;
  1137. allScStats.lessStats.lastWeek = statsInfo.lesson.lastWeek;
  1138. allScStats.lessStats.week = statsInfo.lesson.week;
  1139. allScStats.lessStats.lastTerm = statsInfo.lesson.lastTerm;
  1140. allScStats.lessStats.term = statsInfo.lesson.term;
  1141. allScStats.lessStats.lastDayInter = statsInfo.lesson.lastDayInter;
  1142. allScStats.lessStats.dayInter = statsInfo.lesson.dayInter;
  1143. allScStats.lessStats.lastMonthInter = statsInfo.lesson.lastMonthInter;
  1144. allScStats.lessStats.monthInter = statsInfo.lesson.monthInter;
  1145. allScStats.lessStats.lastYearInter = statsInfo.lesson.lastYearInter;
  1146. allScStats.lessStats.yearInter = statsInfo.lesson.yearInter;
  1147. allScStats.actStats.all = statsInfo.activity.all;
  1148. allScStats.actStats.exam = statsInfo.activity.exam;
  1149. allScStats.actStats.survey = statsInfo.activity.survey;
  1150. allScStats.actStats.vote = statsInfo.activity.vote;
  1151. allScStats.actStats.homework = statsInfo.activity.homework;
  1152. allScStats.actStats.lastDay = statsInfo.activity.lastDay;
  1153. allScStats.actStats.dayCnt = statsInfo.activity.dayCnt;
  1154. allScStats.actStats.lastWeek = statsInfo.activity.lastWeek;
  1155. allScStats.actStats.week = statsInfo.activity.week;
  1156. allScStats.actStats.lastTerm = statsInfo.activity.lastTerm;
  1157. allScStats.actStats.term = statsInfo.activity.term;
  1158. allScStats.actStats.lastMonth = statsInfo.activity.lastMonth;
  1159. allScStats.actStats.month = statsInfo.activity.month;
  1160. if (statsInfo.study != null)
  1161. {
  1162. allScStats.srStats.learnTime = statsInfo.study.learnTime;
  1163. allScStats.srStats.online = statsInfo.study.online;
  1164. allScStats.srStats.offline = statsInfo.study.offline;
  1165. allScStats.srStats.classRoom = statsInfo.study.classRoom;
  1166. allScStats.srStats.submit = statsInfo.study.submit;
  1167. allScStats.srStats.notStarted = statsInfo.study.notStarted;
  1168. allScStats.srStats.ongoing = statsInfo.study.ongoing;
  1169. allScStats.srStats.finish = statsInfo.study.finish;
  1170. }
  1171. }
  1172. return Ok(new { state = RespondCode.Ok, allScStats, areaGroup });
  1173. }
  1174. #endregion 新的统计接口
  1175. /// <summary>
  1176. /// 学校信息中间件查询接口
  1177. /// </summary>
  1178. /// <param name="jsonElement"></param>
  1179. /// <returns></returns>
  1180. [ProducesDefaultResponseType]
  1181. [HttpPost("get-scinfos")]
  1182. public async Task<IActionResult> GetScInfos(JsonElement jsonElement)
  1183. {
  1184. var cosmosClient = _azureCosmos.GetCosmosClient();
  1185. jsonElement.TryGetProperty("role", out JsonElement role);
  1186. jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
  1187. jsonElement.TryGetProperty("areaId", out JsonElement areaId);
  1188. StringBuilder sql = new($"select value(c) from c");
  1189. if (!string.IsNullOrEmpty($"{role}") && !string.IsNullOrEmpty($"{tmdId}") && string.IsNullOrEmpty($"{areaId}"))
  1190. {
  1191. switch ($"{role}")
  1192. {
  1193. case "assist":
  1194. sql.Append($" join a in c.assist where a.id='{tmdId}'");
  1195. break;
  1196. case "sales":
  1197. sql.Append($" join a in c.sales where a.id='{tmdId}'");
  1198. break;
  1199. }
  1200. }
  1201. if (!string.IsNullOrEmpty($"{areaId}") && !string.IsNullOrEmpty($"{role}"))
  1202. {
  1203. sql.Append($" where c.areaId ='{areaId}'");
  1204. }
  1205. //List<string> scId = await CommonFind.FindScIds(cosmosClient, "select value(c.id) from c ", "Base");
  1206. List<BIRelation> scInfos = new();
  1207. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<BIRelation>(queryText: sql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("BIRel") }))
  1208. {
  1209. scInfos.Add(item);
  1210. }
  1211. return Ok(new { state = RespondCode.Ok, allCnt = scInfos.Count, scInfos });
  1212. }
  1213. /// <summary>
  1214. /// 依据Id查询School容器 数据管理工具——查询工具
  1215. /// </summary>
  1216. /// <param name="jsonElement"></param>
  1217. /// <returns></returns>
  1218. [ProducesDefaultResponseType]
  1219. [HttpPost("get-info")]
  1220. public async Task<IActionResult> GetSchool(JsonElement jsonElement)
  1221. {
  1222. if (!jsonElement.TryGetProperty("id", out JsonElement id)) return BadRequest();
  1223. //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署,就不需要,一站多用时,取消注释
  1224. var cosmosClient = _azureCosmos.GetCosmosClient();
  1225. //if ($"{site}".Equals(BIConst.Global))
  1226. // cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
  1227. List<object> infos = new List<object>();
  1228. string sqlTxt = $"select value(c) from c where c.id='{id}'";
  1229. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { }))
  1230. {
  1231. using var json = await JsonDocument.ParseAsync(item.ContentStream);
  1232. foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
  1233. {
  1234. infos.Add(obj.ToObject<object>());
  1235. }
  1236. }
  1237. return Ok(new { state = 200, infos });
  1238. }
  1239. public record RecSchoolDate
  1240. {
  1241. public string id { get; set; }
  1242. public string name { get; set; }
  1243. public long dataCount { get; set; }
  1244. }
  1245. }
  1246. }