SchoolController.cs 82 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391
  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. {
  470. scStats.id = $"{DateTimeOffset.UtcNow.Year}-{school.id}";
  471. scStats.schoolId = school.id;
  472. scStats.name = school.name;
  473. scStats.picture = school.picture;
  474. scStats.areaId = school.areaId;
  475. }
  476. DateTimeOffset dateTime = DateTimeOffset.UtcNow;
  477. long now = dateTime.ToUnixTimeMilliseconds();
  478. if (!string.IsNullOrEmpty(statsInfo.id))
  479. {
  480. scStats.id = statsInfo.id;
  481. scStats.schoolId = statsInfo.schoolId;
  482. scStats.name = statsInfo.name;
  483. scStats.picture = statsInfo.picture;
  484. scStats.areaId = statsInfo.areaId;
  485. scStats.tch = statsInfo.tch;
  486. scStats.dayTch = statsInfo.dayTch;
  487. scStats.weekTch = statsInfo.weekTch;
  488. scStats.monthTch = statsInfo.monthTch;
  489. scStats.stu = statsInfo.stu;
  490. scStats.dayStu = statsInfo.dayStu;
  491. scStats.weekStu = statsInfo.weekStu;
  492. scStats.monthStu = statsInfo.monthStu;
  493. scStats.room = statsInfo.room;
  494. scStats.witRoom = statsInfo.witRoom;
  495. scStats.resourceCnt = statsInfo.resourceCnt;
  496. scStats.size = statsInfo.size;
  497. scStats.scCreateTime = statsInfo.scCreateTime;
  498. scStats.upTime = statsInfo.upTime;
  499. scStats.lessStats.all = statsInfo.lesson.all;
  500. scStats.lessStats.open = statsInfo.lesson.open;
  501. scStats.lessStats.less = statsInfo.lesson.less;
  502. scStats.lessStats.lastDay = statsInfo.lesson.lastDay;
  503. scStats.lessStats.day = statsInfo.lesson.day;
  504. scStats.lessStats.lastWeek = statsInfo.lesson.lastWeek;
  505. scStats.lessStats.week = statsInfo.lesson.week;
  506. scStats.lessStats.lastTerm = statsInfo.lesson.lastTerm;
  507. scStats.lessStats.term = statsInfo.lesson.term;
  508. scStats.lessStats.month = statsInfo.lesson.month;
  509. scStats.lessStats.lastMonth = statsInfo.lesson.lastMonth;
  510. scStats.lessStats.lastDayInter = statsInfo.lesson.lastDayInter;
  511. scStats.lessStats.dayInter = statsInfo.lesson.dayInter;
  512. scStats.lessStats.lastMonthInter = statsInfo.lesson.lastMonthInter;
  513. scStats.lessStats.monthInter = statsInfo.lesson.monthInter;
  514. scStats.lessStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList()).Sum());
  515. scStats.lessStats.lastYearInter = statsInfo.lesson.lastYearInter;
  516. scStats.lessStats.yearInter = statsInfo.lesson.yearInter;
  517. scStats.lessStats.yearInters = TimeHelper.GetYearMonth(statsInfo.lesson.yearInters, dateTime.Year, dateTime.Month);
  518. scStats.lessStats.year = TimeHelper.GetYearMonth(statsInfo.lesson.year, dateTime.Year, dateTime.Month);
  519. scStats.actStats.all = statsInfo.activity.all;
  520. scStats.actStats.exam = statsInfo.activity.exam;
  521. scStats.actStats.survey = statsInfo.activity.survey;
  522. scStats.actStats.vote = statsInfo.activity.vote;
  523. scStats.actStats.homework = statsInfo.activity.homework;
  524. scStats.actStats.lastDay = statsInfo.activity.lastDay;
  525. scStats.actStats.dayCnt = statsInfo.activity.dayCnt;
  526. scStats.actStats.lastWeek = statsInfo.activity.lastWeek;
  527. scStats.actStats.week = statsInfo.activity.week;
  528. scStats.actStats.lastTerm = statsInfo.activity.lastTerm;
  529. scStats.actStats.term = statsInfo.activity.term;
  530. scStats.actStats.lastMonth = statsInfo.activity.lastMonth;
  531. scStats.actStats.month = statsInfo.activity.month;
  532. scStats.actStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.activity.year).Where(w => w.Count > 0).ToList()).Sum());
  533. scStats.actStats.year = TimeHelper.GetYearMonth(statsInfo.activity.year, dateTime.Year, dateTime.Month);
  534. if (statsInfo.study != null)
  535. {
  536. scStats.srStats.learnTime = statsInfo.study.learnTime;
  537. scStats.srStats.online = statsInfo.study.online;
  538. scStats.srStats.offline = statsInfo.study.offline;
  539. scStats.srStats.classRoom = statsInfo.study.classRoom;
  540. scStats.srStats.submit = statsInfo.study.submit;
  541. scStats.srStats.notStarted = statsInfo.study.notStarted;
  542. scStats.srStats.ongoing = statsInfo.study.ongoing;
  543. scStats.srStats.finish = statsInfo.study.finish;
  544. }
  545. }
  546. var weekLess = BICommonWay.weekDoubleMerge(new List<List<double>>() { statsInfo.lesson.year }, dateTime);
  547. var weekAct = BICommonWay.weekDoubleMerge(new List<List<double>>() { statsInfo.activity.year }, dateTime);
  548. return Ok(new { state = RespondCode.Ok, scStats, weekLess, weekAct });
  549. }
  550. /// <summary>
  551. /// 学校统计
  552. /// 新的数据结构
  553. /// </summary>
  554. /// <param name="jsonElement"></param>
  555. /// <returns></returns>
  556. [ProducesDefaultResponseType]
  557. [HttpPost("get-scstats")]
  558. public async Task<IActionResult> GetSchoolStats(JsonElement jsonElement)
  559. {
  560. if (!jsonElement.TryGetProperty("scId", out JsonElement scId)) return BadRequest();
  561. var cosmosClient = _azureCosmos.GetCosmosClient();
  562. School school = new();
  563. ScStats scStats = new();
  564. var respSc = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync($"{scId}", new PartitionKey($"Base"));
  565. if (respSc.Status == 200)
  566. {
  567. using var fileJson = await JsonDocument.ParseAsync(respSc.ContentStream);
  568. school = fileJson.ToObject<School>();
  569. }
  570. else
  571. return Ok(new { state = RespondCode.NotFound, msg = $"未找到id:{scId};相关的学校,请检查" });
  572. try
  573. {
  574. StatsInfo statsInfo = new();
  575. List<LastYearLessAndAct> lastYear = new();
  576. var scDataStats = await cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").ReadItemStreamAsync($"{DateTimeOffset.UtcNow.Year}-{scId}", new PartitionKey("Statistics"));
  577. if (scDataStats.Status == 200)
  578. {
  579. using var fileJson = await JsonDocument.ParseAsync(scDataStats.ContentStream);
  580. statsInfo = fileJson.ToObject<StatsInfo>();
  581. 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") }))
  582. {
  583. lastYear.Add(item);
  584. }
  585. }
  586. else
  587. {
  588. scStats.id = $"{DateTimeOffset.UtcNow.Year}-{school.id}";
  589. scStats.schoolId = school.id;
  590. scStats.name = school.name;
  591. scStats.picture = school.picture;
  592. scStats.areaId = school.areaId;
  593. }
  594. DateTimeOffset dateTime = DateTimeOffset.UtcNow;
  595. long now = dateTime.ToUnixTimeMilliseconds();
  596. if (!string.IsNullOrEmpty(statsInfo.id))
  597. {
  598. scStats.id = statsInfo.id;
  599. scStats.schoolId = statsInfo.schoolId;
  600. scStats.name = statsInfo.name;
  601. scStats.picture = statsInfo.picture;
  602. scStats.areaId = statsInfo.areaId;
  603. scStats.tch = statsInfo.tch;
  604. scStats.dayTch = statsInfo.dayTch;
  605. scStats.weekTch = statsInfo.weekTch;
  606. scStats.monthTch = statsInfo.monthTch;
  607. scStats.stu = statsInfo.stu;
  608. scStats.dayStu = statsInfo.dayStu;
  609. scStats.weekStu = statsInfo.weekStu;
  610. scStats.monthStu = statsInfo.monthStu;
  611. scStats.room = statsInfo.room;
  612. scStats.witRoom = statsInfo.witRoom;
  613. scStats.resourceCnt = statsInfo.resourceCnt;
  614. scStats.size = statsInfo.size;
  615. scStats.scCreateTime = statsInfo.scCreateTime;
  616. scStats.upTime = statsInfo.upTime;
  617. scStats.lessStats.all = statsInfo.lesson.all;
  618. scStats.lessStats.open = statsInfo.lesson.open;
  619. scStats.lessStats.less = statsInfo.lesson.less;
  620. scStats.lessStats.lastDay = statsInfo.lesson.lastDay;
  621. scStats.lessStats.day = statsInfo.lesson.day;
  622. scStats.lessStats.lastWeek = statsInfo.lesson.lastWeek;
  623. scStats.lessStats.week = statsInfo.lesson.week;
  624. scStats.lessStats.lastTerm = statsInfo.lesson.lastTerm;
  625. scStats.lessStats.term = statsInfo.lesson.term;
  626. scStats.lessStats.month = statsInfo.lesson.month;
  627. scStats.lessStats.lastMonth = statsInfo.lesson.lastMonth;
  628. scStats.lessStats.lastDayInter = statsInfo.lesson.lastDayInter;
  629. scStats.lessStats.dayInter = statsInfo.lesson.dayInter;
  630. scStats.lessStats.lastMonthInter = statsInfo.lesson.lastMonthInter;
  631. scStats.lessStats.monthInter = statsInfo.lesson.monthInter;
  632. scStats.lessStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList()).Sum());
  633. scStats.lessStats.lastYearInter = statsInfo.lesson.lastYearInter;
  634. scStats.lessStats.yearInter = statsInfo.lesson.yearInter;
  635. scStats.lessStats.yearInters = TimeHelper.GetYearMonth(statsInfo.lesson.yearInters, dateTime.Year, dateTime.Month);
  636. scStats.lessStats.year = TimeHelper.GetYearMonth(statsInfo.lesson.year, dateTime.Year, dateTime.Month);
  637. scStats.actStats.all = statsInfo.activity.all;
  638. scStats.actStats.exam = statsInfo.activity.exam;
  639. scStats.actStats.survey = statsInfo.activity.survey;
  640. scStats.actStats.vote = statsInfo.activity.vote;
  641. scStats.actStats.homework = statsInfo.activity.homework;
  642. scStats.actStats.lastDay = statsInfo.activity.lastDay;
  643. scStats.actStats.dayCnt = statsInfo.activity.dayCnt;
  644. scStats.actStats.lastWeek = statsInfo.activity.lastWeek;
  645. scStats.actStats.week = statsInfo.activity.week;
  646. scStats.actStats.lastTerm = statsInfo.activity.lastTerm;
  647. scStats.actStats.term = statsInfo.activity.term;
  648. scStats.actStats.lastMonth = statsInfo.activity.lastMonth;
  649. scStats.actStats.month = statsInfo.activity.month;
  650. scStats.actStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.activity.year).Where(w => w.Count > 0).ToList()).Sum());
  651. scStats.actStats.year = TimeHelper.GetYearMonth(statsInfo.activity.year, dateTime.Year, dateTime.Month);
  652. if (statsInfo.study != null)
  653. {
  654. scStats.srStats.learnTime = statsInfo.study.learnTime;
  655. scStats.srStats.online = statsInfo.study.online;
  656. scStats.srStats.offline = statsInfo.study.offline;
  657. scStats.srStats.classRoom = statsInfo.study.classRoom;
  658. scStats.srStats.submit = statsInfo.study.submit;
  659. scStats.srStats.notStarted = statsInfo.study.notStarted;
  660. scStats.srStats.ongoing = statsInfo.study.ongoing;
  661. scStats.srStats.finish = statsInfo.study.finish;
  662. }
  663. }
  664. //if(statsInfo.upTime > 0 && !isnew) {
  665. // long timedis = 20 * 60 * 1000;//20分钟
  666. // if (now - statsInfo.upTime < timedis)
  667. // isnew = true;
  668. //}
  669. //if (statsInfo.upTime == 0 && isnew)
  670. // statsInfo.upTime = now;
  671. //if (statsInfo.upTime == 0 && isnew)
  672. // statsInfo.upTime = DateTimeOffset.UtcNow.Add(new TimeSpan(-1, 0, 0, 0)).ToUnixTimeMilliseconds();
  673. //if (!isnew)
  674. //{
  675. // //Function 处理Function 更新或者新增
  676. // //_ = _httpTrigger.RequestHttpTrigger(new { schoolId = $"{school.id}"}, _option.Location, "stats-sc-info");
  677. // //异步方法处理 同步更新
  678. // statsInfo = await SchoolStatsWay.GetSingleSc(cosmosClient, school.id);
  679. // statsInfo.upTime = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
  680. // if (isExist)
  681. // statsInfo = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReplaceItemAsync<StatsInfo>(statsInfo, statsInfo.id, new PartitionKey("Stats"));
  682. // else
  683. // statsInfo = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").CreateItemAsync<StatsInfo>(statsInfo, new PartitionKey("Stats"));
  684. //}
  685. return Ok(new { state = RespondCode.Ok, scStats });
  686. }
  687. catch (Exception ex)
  688. {
  689. await _dingDing.SendBotMsg($"BI,{_option.Location} /school/set-scstats() \n {ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
  690. return BadRequest();
  691. }
  692. }
  693. /// <summary>
  694. /// 依据身份统计学校
  695. /// 新的数据结构
  696. /// </summary>
  697. /// <param name="jsonElement"></param>
  698. /// <returns></returns>
  699. [ProducesDefaultResponseType]
  700. [HttpPost("get-rolestats")]
  701. public async Task<IActionResult> GetRoleStats(JsonElement jsonElement)
  702. {
  703. //jsonElement.TryGetProperty("areaId", out JsonElement areaId);
  704. jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
  705. jsonElement.TryGetProperty("role", out JsonElement role);
  706. //if (string.IsNullOrEmpty($"{areaId}") && string.IsNullOrEmpty($"{tmdId}"))
  707. // return Ok(new { stats = RespondCode.ParamsError, msg = "areaId/tmdId两者参数不能都为空!" });
  708. var cosmosClient = _azureCosmos.GetCosmosClient();
  709. Area area = null;
  710. ScStats areaScStats = new();
  711. List<string> scIds = new();
  712. DateTimeOffset dateTime = DateTimeOffset.UtcNow;
  713. List<ScStats> scStatss = new();
  714. List<StatsInfo> statsInfos = new();
  715. StringBuilder scSql = new("select c.id,c.name,c.size,c.scale,c.serial,c.service,c.hard from c");
  716. StringBuilder lastScSql = new("select value(c.id) from c");
  717. //if (!string.IsNullOrEmpty($"{areaId}"))
  718. //{
  719. // scSql.Append($" where c.areaId='{areaId}'");
  720. // lastScSql.Append($" where c.areaId='{areaId}'");
  721. // var respSc = await cosmosClient.GetContainer(Constant.TEAMModelOS, "Normal").ReadItemStreamAsync($"{areaId}", new PartitionKey($"Base-Area"));
  722. // if (respSc.Status == 200)
  723. // {
  724. // using var fileJson = await JsonDocument.ParseAsync(respSc.ContentStream);
  725. // area = fileJson.ToObject<Area>();
  726. // }
  727. // else
  728. // return Ok(new { state = RespondCode.NotFound, msg = $"未找到id:{areaId};相关的学区,请检查!" });
  729. //}
  730. if (!string.IsNullOrEmpty($"{tmdId}"))
  731. {
  732. switch ($"{role}")
  733. {
  734. case "assist":
  735. scSql.Append($" join a in c.assists where a.id='{tmdId}'");
  736. lastScSql.Append($" join a in c.assists where a.id='{tmdId}'");
  737. break;
  738. case "sales":
  739. scSql.Append($" join a in c.sales where a.id='{tmdId}'");
  740. lastScSql.Append($" join a in c.sales where a.id='{tmdId}'");
  741. break;
  742. default:
  743. return Ok(new { state = RespondCode.ParamsError, msg = "role参数错误" });
  744. }
  745. }
  746. List<BIRelStats> bIRelStats = new();
  747. List<string> tempProduct = new();
  748. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<BIRelStats>(queryText: scSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("BIRel") }))
  749. {
  750. bIRelStats.Add(item);
  751. tempProduct.AddRange(item.serial);
  752. tempProduct.AddRange(item.service);
  753. tempProduct.AddRange(item.hard);
  754. }
  755. if (bIRelStats.Count > 0)
  756. scIds = bIRelStats.Select(s => s.id).ToList();
  757. //scIds = await CommonFind.FindScIds(cosmosClient, scSql.ToString(), "BIRel");
  758. StringBuilder statsSql = new($"select value(c) from c where c.year={dateTime.Year}");
  759. if (scIds.Count > 0)
  760. {
  761. statsSql.Append($" and {BICommonWay.ManyScSql("c.id", scIds, $"{dateTime.Year}-")}");
  762. }
  763. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator<StatsInfo>(queryText: statsSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
  764. {
  765. statsInfos.Add(item);
  766. }
  767. StringBuilder lastStsSql = new($"select c.lesson,c.activity from c where c.year={dateTime.Year}");
  768. if (scIds.Count > 0)
  769. {
  770. lastStsSql.Append($" and {BICommonWay.ManyScSql("c.id", scIds, $"{DateTimeOffset.UtcNow.Year - 1}-")}");
  771. }
  772. List<LastYearLessAndAct> lastYear = new();
  773. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator<LastYearLessAndAct>(queryText: lastStsSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
  774. {
  775. lastYear.Add(item);
  776. }
  777. StatsInfo statsInfo = null;
  778. statsInfo = SchoolStatsWay.GetAreaStats(cosmosClient, _option, statsInfos, scIds, area);
  779. if (statsInfo != null)
  780. {
  781. areaScStats.id = statsInfo.id;
  782. areaScStats.schoolId = statsInfo.schoolId;
  783. areaScStats.name = statsInfo.name;
  784. areaScStats.picture = statsInfo.picture;
  785. areaScStats.areaId = statsInfo.areaId;
  786. areaScStats.tch = statsInfo.tch;
  787. areaScStats.dayTch = statsInfo.dayTch;
  788. areaScStats.weekTch = statsInfo.weekTch;
  789. areaScStats.monthTch = statsInfo.monthTch;
  790. areaScStats.stu = statsInfo.stu;
  791. areaScStats.dayStu = statsInfo.dayStu;
  792. areaScStats.weekStu = statsInfo.weekStu;
  793. areaScStats.monthStu = statsInfo.monthStu;
  794. areaScStats.room = statsInfo.room;
  795. areaScStats.witRoom = statsInfo.witRoom;
  796. areaScStats.resourceCnt = statsInfo.witRoom;
  797. areaScStats.size = statsInfo.size;
  798. areaScStats.scCreateTime = statsInfo.scCreateTime;
  799. areaScStats.upTime = statsInfo.upTime;
  800. areaScStats.lessStats.open = statsInfo.lesson.all;
  801. areaScStats.lessStats.open = statsInfo.lesson.open;
  802. areaScStats.lessStats.less = statsInfo.lesson.less;
  803. areaScStats.lessStats.lastDay = statsInfo.lesson.lastDay;
  804. areaScStats.lessStats.day = statsInfo.lesson.day;
  805. areaScStats.lessStats.lastWeek = statsInfo.lesson.lastWeek;
  806. areaScStats.lessStats.week = statsInfo.lesson.week;
  807. areaScStats.lessStats.lastTerm = statsInfo.lesson.lastTerm;
  808. areaScStats.lessStats.term = statsInfo.lesson.term;
  809. areaScStats.lessStats.lastDayInter = statsInfo.lesson.lastDayInter;
  810. areaScStats.lessStats.dayInter = statsInfo.lesson.dayInter;
  811. areaScStats.lessStats.lastMonthInter = statsInfo.lesson.lastMonthInter;
  812. areaScStats.lessStats.monthInter = statsInfo.lesson.monthInter;
  813. areaScStats.lessStats.lastYearInter = statsInfo.lesson.lastYearInter;
  814. areaScStats.lessStats.yearInter = statsInfo.lesson.yearInter;
  815. areaScStats.lessStats.yearInters = TimeHelper.GetYearMonth(statsInfo.lesson.yearInters, dateTime.Year, dateTime.Month);
  816. areaScStats.lessStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList()).Sum());
  817. areaScStats.lessStats.year = TimeHelper.GetYearMonth(statsInfo.lesson.year, dateTime.Year, dateTime.Month);
  818. areaScStats.actStats.all = statsInfo.activity.all;
  819. areaScStats.actStats.exam = statsInfo.activity.exam;
  820. areaScStats.actStats.survey = statsInfo.activity.survey;
  821. areaScStats.actStats.vote = statsInfo.activity.vote;
  822. areaScStats.actStats.homework = statsInfo.activity.homework;
  823. areaScStats.actStats.lastDay = statsInfo.activity.lastDay;
  824. areaScStats.actStats.dayCnt = statsInfo.activity.dayCnt;
  825. areaScStats.actStats.lastWeek = statsInfo.activity.lastWeek;
  826. areaScStats.actStats.week = statsInfo.activity.week;
  827. areaScStats.actStats.lastTerm = statsInfo.activity.lastTerm;
  828. areaScStats.actStats.term = statsInfo.activity.term;
  829. areaScStats.actStats.lastMonth = statsInfo.activity.lastMonth;
  830. areaScStats.actStats.month = statsInfo.activity.month;
  831. areaScStats.actStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.activity.year).Where(w => w.Count > 0).ToList()).Sum());
  832. areaScStats.actStats.year = TimeHelper.GetYearMonth(statsInfo.activity.year, dateTime.Year, dateTime.Month);
  833. if (statsInfo.study != null)
  834. {
  835. areaScStats.srStats.learnTime = statsInfo.study.learnTime;
  836. areaScStats.srStats.online = statsInfo.study.online;
  837. areaScStats.srStats.offline = statsInfo.study.offline;
  838. areaScStats.srStats.classRoom = statsInfo.study.classRoom;
  839. areaScStats.srStats.submit = statsInfo.study.submit;
  840. areaScStats.srStats.notStarted = statsInfo.study.notStarted;
  841. areaScStats.srStats.ongoing = statsInfo.study.ongoing;
  842. areaScStats.srStats.finish = statsInfo.study.finish;
  843. }
  844. }
  845. 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();
  846. var productGroup = tempProduct.GroupBy(g => g).Select(s => new { code = s.Key, cnt = s.Count() });
  847. return Ok(new { state = RespondCode.Ok, areaScStats, scInfos, productGroup });
  848. }
  849. /// <summary>
  850. /// 依据区级Id统计 新数据结构
  851. /// </summary>
  852. /// <param name="jsonElement"></param>
  853. /// <returns></returns>
  854. [ProducesDefaultResponseType]
  855. [HttpPost("get-areastats")]
  856. public async Task<IActionResult> GetAreaStats(JsonElement jsonElement)
  857. {
  858. if (!jsonElement.TryGetProperty("areaId", out JsonElement areaId)) return BadRequest();
  859. var redisClinet = _azureRedis.GetRedisClient(8);
  860. var cosmosClient = _azureCosmos.GetCosmosClient();
  861. DateTimeOffset dateTime = DateTimeOffset.UtcNow;
  862. Area area = null;
  863. AreaStats areaScStats = new();
  864. List<string> scIds = new();
  865. List<BISchoolInfo> schoolInfos = new();
  866. List<ScStats> scStatss = new();
  867. List<StatsInfo> statsInfos = new();
  868. long useSize = 0;
  869. Dictionary<string, double?> typeStics = new(); //所有类型
  870. var (weekS, weekE) = TimeHelper.GetStartOrEnd(dateTime, "week"); //计算本周开始/结束时间
  871. var (mthS, mthE) = TimeHelper.GetStartOrEnd(dateTime, "month"); //本月开始/结束时间
  872. 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") }))
  873. {
  874. schoolInfos.Add(item);
  875. }
  876. if (schoolInfos.Count > 0)
  877. scIds = schoolInfos.Select(s => s.id).ToList();
  878. StringBuilder statsSql = new($"select value(c) from c where c.year={dateTime.Year}");
  879. if (scIds.Count > 0)
  880. statsSql.Append($" and {BICommonWay.ManyScSql("c.id", scIds, $"{dateTime.Year}-")}");
  881. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator<StatsInfo>(queryText: statsSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
  882. {
  883. statsInfos.Add(item);
  884. }
  885. StringBuilder lastStsSql = new($"select c.lesson,c.activity from c where c.year={dateTime.Year - 1}");
  886. if (scIds.Count > 0)
  887. {
  888. lastStsSql.Append($" and {BICommonWay.ManyScSql("c.id", scIds, $"{dateTime.Year - 1}-")}");
  889. }
  890. List<LastYearLessAndAct> lastYear = new();
  891. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator<LastYearLessAndAct>(queryText: lastStsSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
  892. {
  893. lastYear.Add(item);
  894. }
  895. StatsInfo statsInfo = null;
  896. statsInfo = SchoolStatsWay.GetAreaStats(cosmosClient, _option, statsInfos, scIds, area);
  897. if (statsInfo != null)
  898. {
  899. //areaScStats = new
  900. //{
  901. // statsInfo.id,
  902. // statsInfo.schoolId,
  903. // statsInfo.name,
  904. // statsInfo.picture,
  905. // statsInfo.areaId,
  906. // weekSc = schoolInfos.Where(w => w.createDate >= weekS && w.createDate <= weekE).Count(),
  907. // monthSc = schoolInfos.Where(w => w.createDate >= mthS && w.createDate <= mthE).Count(),
  908. // statsInfo.tch,
  909. // statsInfo.dayTch,
  910. // statsInfo.weekTch,
  911. // statsInfo.monthTch,
  912. // statsInfo.stu,
  913. // statsInfo.dayStu,
  914. // statsInfo.weekStu,
  915. // statsInfo.monthStu,
  916. // statsInfo.room,
  917. // statsInfo.witRoom,
  918. // statsInfo.size,
  919. // statsInfo.scCreateTime,
  920. // statsInfo.upTime,
  921. // lessStats = new
  922. // {
  923. // statsInfo.lesson.all,
  924. // statsInfo.lesson.open,
  925. // statsInfo.lesson.less,
  926. // statsInfo.lesson.lastDay,
  927. // statsInfo.lesson.day,
  928. // statsInfo.lesson.lastWeek,
  929. // statsInfo.lesson.week,
  930. // statsInfo.lesson.lastTerm,
  931. // statsInfo.lesson.term,
  932. // statsInfo.lesson.lastDayInter,
  933. // statsInfo.lesson.dayInter,
  934. // statsInfo.lesson.lastMonthInter,
  935. // statsInfo.lesson.monthInter,
  936. // statsInfo.lesson.lastYearInter,
  937. // statsInfo.lesson.yearInter,
  938. // yearInters = TimeHelper.GetYearMonth(statsInfo.lesson.yearInters, dateTime.Year, dateTime.Month),
  939. // lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList()).Sum()),
  940. // year = TimeHelper.GetYearMonth(statsInfo.lesson.year, dateTime.Year, dateTime.Month)
  941. // },
  942. // actStats = new
  943. // {
  944. // statsInfo.activity.all,
  945. // statsInfo.activity.exam,
  946. // statsInfo.activity.survey,
  947. // statsInfo.activity.vote,
  948. // statsInfo.activity.homework,
  949. // statsInfo.activity.lastDay,
  950. // statsInfo.activity.dayCnt,
  951. // statsInfo.activity.lastWeek,
  952. // statsInfo.activity.week,
  953. // statsInfo.activity.lastTerm,
  954. // statsInfo.activity.term,
  955. // lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.activity.year).Where(w => w.Count > 0).ToList()).Sum()),
  956. // year = TimeHelper.GetYearMonth(statsInfo.activity.year, dateTime.Year, dateTime.Month)
  957. // },
  958. // srStats = new
  959. // {
  960. // statsInfo.study.learnTime,
  961. // statsInfo.study.online,
  962. // statsInfo.study.offline,
  963. // statsInfo.study.classRoom,
  964. // statsInfo.study.submit,
  965. // statsInfo.study.notStarted,
  966. // statsInfo.study.ongoing,
  967. // statsInfo.study.finish
  968. // }
  969. //};
  970. areaScStats.id = statsInfo.id;
  971. areaScStats.name = statsInfo.name;
  972. areaScStats.weekSc = schoolInfos.Where(w => w.createDate >= weekS && w.createDate <= weekE).Count();
  973. areaScStats.monthSc = schoolInfos.Where(w => w.createDate >= mthS && w.createDate <= mthE).Count();
  974. areaScStats.tch = statsInfo.tch;
  975. areaScStats.dayTch = statsInfo.dayTch;
  976. areaScStats.weekTch = statsInfo.weekTch;
  977. areaScStats.monthTch = statsInfo.monthTch;
  978. areaScStats.stu = statsInfo.stu;
  979. areaScStats.dayStu = statsInfo.dayStu;
  980. areaScStats.weekStu = statsInfo.weekStu;
  981. areaScStats.monthStu = statsInfo.monthStu;
  982. areaScStats.room = statsInfo.room;
  983. areaScStats.witRoom = statsInfo.witRoom;
  984. areaScStats.resourceCnt = statsInfo.resourceCnt;
  985. areaScStats.size = statsInfo.size;
  986. //areaScStats.useSize = statsInfo.useSize;
  987. areaScStats.lessStats.open = statsInfo.lesson.all;
  988. areaScStats.lessStats.open = statsInfo.lesson.open;
  989. areaScStats.lessStats.less = statsInfo.lesson.less;
  990. areaScStats.lessStats.lastDay = statsInfo.lesson.lastDay;
  991. areaScStats.lessStats.day = statsInfo.lesson.day;
  992. areaScStats.lessStats.lastWeek = statsInfo.lesson.lastWeek;
  993. areaScStats.lessStats.week = statsInfo.lesson.week;
  994. areaScStats.lessStats.lastTerm = statsInfo.lesson.lastTerm;
  995. areaScStats.lessStats.term = statsInfo.lesson.term;
  996. areaScStats.lessStats.lastDayInter = statsInfo.lesson.lastDayInter;
  997. areaScStats.lessStats.dayInter = statsInfo.lesson.dayInter;
  998. areaScStats.lessStats.lastMonthInter = statsInfo.lesson.lastMonthInter;
  999. areaScStats.lessStats.monthInter = statsInfo.lesson.monthInter;
  1000. areaScStats.lessStats.lastYearInter = statsInfo.lesson.lastYearInter;
  1001. areaScStats.lessStats.yearInter = statsInfo.lesson.yearInter;
  1002. areaScStats.lessStats.yearInters = TimeHelper.GetYearMonth(statsInfo.lesson.yearInters, dateTime.Year, dateTime.Month);
  1003. areaScStats.lessStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList()).Sum());
  1004. areaScStats.lessStats.year = TimeHelper.GetYearMonth(statsInfo.lesson.year, dateTime.Year, dateTime.Month);
  1005. areaScStats.actStats.all = statsInfo.activity.all;
  1006. areaScStats.actStats.exam = statsInfo.activity.exam;
  1007. areaScStats.actStats.survey = statsInfo.activity.survey;
  1008. areaScStats.actStats.vote = statsInfo.activity.vote;
  1009. areaScStats.actStats.homework = statsInfo.activity.homework;
  1010. areaScStats.actStats.lastDay = statsInfo.activity.lastDay;
  1011. areaScStats.actStats.dayCnt = statsInfo.activity.dayCnt;
  1012. areaScStats.actStats.lastWeek = statsInfo.activity.lastWeek;
  1013. areaScStats.actStats.week = statsInfo.activity.week;
  1014. areaScStats.actStats.lastTerm = statsInfo.activity.lastTerm;
  1015. areaScStats.actStats.term = statsInfo.activity.term;
  1016. areaScStats.actStats.lastMonth = statsInfo.activity.lastMonth;
  1017. areaScStats.actStats.month = statsInfo.activity.month;
  1018. areaScStats.actStats.lastYear = ((int)BICommonWay.ManyDoubleMerge(lastYear.Select(s => s.activity.year).Where(w => w.Count > 0).ToList()).Sum());
  1019. areaScStats.actStats.year = TimeHelper.GetYearMonth(statsInfo.activity.year, dateTime.Year, dateTime.Month);
  1020. if (statsInfo.study != null)
  1021. {
  1022. areaScStats.srStats.learnTime = statsInfo.study.learnTime;
  1023. areaScStats.srStats.online = statsInfo.study.online;
  1024. areaScStats.srStats.offline = statsInfo.study.offline;
  1025. areaScStats.srStats.classRoom = statsInfo.study.classRoom;
  1026. areaScStats.srStats.submit = statsInfo.study.submit;
  1027. areaScStats.srStats.notStarted = statsInfo.study.notStarted;
  1028. areaScStats.srStats.ongoing = statsInfo.study.ongoing;
  1029. areaScStats.srStats.finish = statsInfo.study.finish;
  1030. }
  1031. }
  1032. List<double> weekLess = BICommonWay.weekDoubleMerge(statsInfos.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList(), dateTime);
  1033. schoolInfos.ForEach(fe =>
  1034. {
  1035. var tempSts = statsInfos.Find(f => f.schoolId.Equals(fe.id));
  1036. if (tempSts != null)
  1037. {
  1038. fe.less = tempSts.lesson.all;
  1039. fe.lastWeekLess = tempSts.lesson.lastWeek;
  1040. fe.monthLess = tempSts.lesson.month;
  1041. }
  1042. });
  1043. List<IdInfo> assits = new();
  1044. schoolInfos.ForEach(fe => assits.AddRange(fe.assists));
  1045. List<IdInfo> saless = new();
  1046. schoolInfos.ForEach(fe => saless.AddRange(fe.sales));
  1047. foreach (var itemId in scIds)
  1048. {
  1049. RedisValue value = redisClinet.HashGet($"Blob:Record", itemId);
  1050. if (value != default && !value.IsNullOrEmpty)
  1051. {
  1052. JsonElement record = value.ToString().ToObject<JsonElement>();
  1053. if (record.TryGetInt64(out long blobsize))
  1054. {
  1055. useSize += blobsize;
  1056. }
  1057. }
  1058. SortedSetEntry[] Scores = redisClinet.SortedSetRangeByScoreWithScores($"Blob:Catalog:{itemId}");
  1059. if (Scores.Length > 0)
  1060. {
  1061. Dictionary<string, double?> schoolStics = new(); //学校空间
  1062. foreach (var score in Scores)
  1063. {
  1064. double val = score.Score;
  1065. string key = score.Element.ToString();
  1066. schoolStics.Add(key, val);
  1067. }
  1068. typeStics = typeStics.Concat(schoolStics).GroupBy(g => g.Key).ToDictionary(k => k.Key, k => k.Sum(kvp => kvp.Value)); //lamebda表达式
  1069. }
  1070. }
  1071. 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() });
  1072. }
  1073. /// <summary>
  1074. /// 所有区级的统计
  1075. /// </summary>
  1076. /// <param name="jsonElement"></param>
  1077. /// <returns></returns>
  1078. [ProducesDefaultResponseType]
  1079. [HttpPost("get-allscstats")]
  1080. public async Task<IActionResult> GetAllAreaStats(JsonElement jsonElement)
  1081. {
  1082. var cosmosClient = _azureCosmos.GetCosmosClient();
  1083. AllScStats allScStats = new();
  1084. List<StatsInfo> statsInfos = new();
  1085. DateTimeOffset dateTime = DateTimeOffset.UtcNow;
  1086. var (lastDayS, lastdayE) = TimeHelper.GetStartOrEnd(dateTime.AddDays(-1)); //昨天开始时间
  1087. var (dayS, dayE) = TimeHelper.GetStartOrEnd(dateTime); //今天开始时间
  1088. var (lastWeekS, lastWeekE) = TimeHelper.GetStartOrEnd(dateTime, "lastweek"); //计算上周开始/结束时间
  1089. var (weekS, weekE) = TimeHelper.GetStartOrEnd(dateTime, "week"); //计算本周开始/结束时间
  1090. var (lastTermS, lastTermE) = TimeHelper.GetStartOrEnd(dateTime, "lastterm"); //计算上学期开始/结束时间
  1091. var (termS, termE) = TimeHelper.GetStartOrEnd(dateTime, "term"); //计算本学期开始/结束时间
  1092. var (lastMthS, LastmthE) = TimeHelper.GetStartOrEnd(dateTime, "lastMonth"); //上月开始/结束时间
  1093. var (mthS, mthE) = TimeHelper.GetStartOrEnd(dateTime, "month"); //本月开始/结束时间
  1094. var (lastYearS, lastYearE) = TimeHelper.GetStartOrEnd(DateTimeOffset.Parse($"{dateTime.Year - 1}-1-1"), "year"); //计算去年开始/结束时间
  1095. var (yearS, yearE) = TimeHelper.GetStartOrEnd(dateTime, "year"); //计算今年开始/结束时间
  1096. string sql = "select value(count(c.id)) from c ";
  1097. List<EasyInfo> easyInfos = new();
  1098. 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") }))
  1099. {
  1100. easyInfos.Add(item);
  1101. }
  1102. int scCnt = await JointlySingleQuery.GetValueInt(cosmosClient, "Normal", "Base", sql);
  1103. int scWeekCnt = await JointlySingleQuery.GetValueInt(cosmosClient, "Normal", "Base", $"{sql} where c.createTime >= {weekS} and c.createTime <= {weekE}");
  1104. int scMonthCnt = await JointlySingleQuery.GetValueInt(cosmosClient, "Normal", "Base", $"{sql} where c.createTime >= {mthS} and c.createTime <= {mthE}");
  1105. List<string> scId = new();
  1106. 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") }))
  1107. {
  1108. statsInfos.Add(item);
  1109. if (item.activity.year.Count > 366)
  1110. {
  1111. scId.Add(item.id);
  1112. }
  1113. }
  1114. 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();
  1115. StatsInfo statsInfo = null;
  1116. statsInfo = SchoolStatsWay.GetAreaStats(cosmosClient, _option, statsInfos);
  1117. if (statsInfo != null)
  1118. {
  1119. allScStats.areaCnt = easyInfos.Count;
  1120. allScStats.sc = statsInfos.Count;
  1121. allScStats.weekSc = scWeekCnt;
  1122. allScStats.monthSc = scMonthCnt;
  1123. allScStats.tch = statsInfo.tch;
  1124. allScStats.dayTch = statsInfo.dayTch;
  1125. allScStats.weekTch = statsInfo.weekTch;
  1126. allScStats.monthTch = statsInfo.monthTch;
  1127. allScStats.stu = statsInfo.stu;
  1128. allScStats.dayStu = statsInfo.dayStu;
  1129. allScStats.weekStu = statsInfo.weekStu;
  1130. allScStats.monthStu = statsInfo.monthStu;
  1131. allScStats.room = statsInfo.room;
  1132. allScStats.witRoom = statsInfo.witRoom;
  1133. allScStats.resourceCnt = statsInfo.resourceCnt;
  1134. allScStats.size = statsInfo.size;
  1135. allScStats.scCreateTime = statsInfo.scCreateTime;
  1136. allScStats.upTime = statsInfo.upTime;
  1137. allScStats.lessStats.open = statsInfo.lesson.all;
  1138. allScStats.lessStats.open = statsInfo.lesson.open;
  1139. allScStats.lessStats.less = statsInfo.lesson.less;
  1140. allScStats.lessStats.lastDay = statsInfo.lesson.lastDay;
  1141. allScStats.lessStats.day = statsInfo.lesson.day;
  1142. allScStats.lessStats.lastWeek = statsInfo.lesson.lastWeek;
  1143. allScStats.lessStats.week = statsInfo.lesson.week;
  1144. allScStats.lessStats.lastTerm = statsInfo.lesson.lastTerm;
  1145. allScStats.lessStats.term = statsInfo.lesson.term;
  1146. allScStats.lessStats.lastDayInter = statsInfo.lesson.lastDayInter;
  1147. allScStats.lessStats.dayInter = statsInfo.lesson.dayInter;
  1148. allScStats.lessStats.lastMonthInter = statsInfo.lesson.lastMonthInter;
  1149. allScStats.lessStats.monthInter = statsInfo.lesson.monthInter;
  1150. allScStats.lessStats.lastYearInter = statsInfo.lesson.lastYearInter;
  1151. allScStats.lessStats.yearInter = statsInfo.lesson.yearInter;
  1152. allScStats.actStats.all = statsInfo.activity.all;
  1153. allScStats.actStats.exam = statsInfo.activity.exam;
  1154. allScStats.actStats.survey = statsInfo.activity.survey;
  1155. allScStats.actStats.vote = statsInfo.activity.vote;
  1156. allScStats.actStats.homework = statsInfo.activity.homework;
  1157. allScStats.actStats.lastDay = statsInfo.activity.lastDay;
  1158. allScStats.actStats.dayCnt = statsInfo.activity.dayCnt;
  1159. allScStats.actStats.lastWeek = statsInfo.activity.lastWeek;
  1160. allScStats.actStats.week = statsInfo.activity.week;
  1161. allScStats.actStats.lastTerm = statsInfo.activity.lastTerm;
  1162. allScStats.actStats.term = statsInfo.activity.term;
  1163. allScStats.actStats.lastMonth = statsInfo.activity.lastMonth;
  1164. allScStats.actStats.month = statsInfo.activity.month;
  1165. if (statsInfo.study != null)
  1166. {
  1167. allScStats.srStats.learnTime = statsInfo.study.learnTime;
  1168. allScStats.srStats.online = statsInfo.study.online;
  1169. allScStats.srStats.offline = statsInfo.study.offline;
  1170. allScStats.srStats.classRoom = statsInfo.study.classRoom;
  1171. allScStats.srStats.submit = statsInfo.study.submit;
  1172. allScStats.srStats.notStarted = statsInfo.study.notStarted;
  1173. allScStats.srStats.ongoing = statsInfo.study.ongoing;
  1174. allScStats.srStats.finish = statsInfo.study.finish;
  1175. }
  1176. }
  1177. return Ok(new { state = RespondCode.Ok, allScStats, areaGroup });
  1178. }
  1179. #endregion 新的统计接口
  1180. /// <summary>
  1181. /// 学校信息中间件查询接口
  1182. /// </summary>
  1183. /// <param name="jsonElement"></param>
  1184. /// <returns></returns>
  1185. [ProducesDefaultResponseType]
  1186. [HttpPost("get-scinfos")]
  1187. public async Task<IActionResult> GetScInfos(JsonElement jsonElement)
  1188. {
  1189. var cosmosClient = _azureCosmos.GetCosmosClient();
  1190. jsonElement.TryGetProperty("role", out JsonElement role);
  1191. jsonElement.TryGetProperty("tmdId", out JsonElement tmdId);
  1192. jsonElement.TryGetProperty("areaId", out JsonElement areaId);
  1193. StringBuilder sql = new($"select value(c) from c");
  1194. if (!string.IsNullOrEmpty($"{role}") && !string.IsNullOrEmpty($"{tmdId}") && string.IsNullOrEmpty($"{areaId}"))
  1195. {
  1196. switch ($"{role}")
  1197. {
  1198. case "assist":
  1199. sql.Append($" join a in c.assist where a.id='{tmdId}'");
  1200. break;
  1201. case "sales":
  1202. sql.Append($" join a in c.sales where a.id='{tmdId}'");
  1203. break;
  1204. }
  1205. }
  1206. if (!string.IsNullOrEmpty($"{areaId}") && !string.IsNullOrEmpty($"{role}"))
  1207. {
  1208. sql.Append($" where c.areaId ='{areaId}'");
  1209. }
  1210. //List<string> scId = await CommonFind.FindScIds(cosmosClient, "select value(c.id) from c ", "Base");
  1211. List<BIRelation> scInfos = new();
  1212. await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<BIRelation>(queryText: sql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("BIRel") }))
  1213. {
  1214. scInfos.Add(item);
  1215. }
  1216. return Ok(new { state = RespondCode.Ok, allCnt = scInfos.Count, scInfos });
  1217. }
  1218. /// <summary>
  1219. /// 依据Id查询School容器 数据管理工具——查询工具
  1220. /// </summary>
  1221. /// <param name="jsonElement"></param>
  1222. /// <returns></returns>
  1223. [ProducesDefaultResponseType]
  1224. [HttpPost("get-info")]
  1225. public async Task<IActionResult> GetSchool(JsonElement jsonElement)
  1226. {
  1227. if (!jsonElement.TryGetProperty("id", out JsonElement id)) return BadRequest();
  1228. //jsonElement.TryGetProperty("site", out JsonElement site);//分开部署,就不需要,一站多用时,取消注释
  1229. var cosmosClient = _azureCosmos.GetCosmosClient();
  1230. //if ($"{site}".Equals(BIConst.Global))
  1231. // cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global);
  1232. List<object> infos = new List<object>();
  1233. string sqlTxt = $"select value(c) from c where c.id='{id}'";
  1234. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: sqlTxt, requestOptions: new QueryRequestOptions() { }))
  1235. {
  1236. using var json = await JsonDocument.ParseAsync(item.ContentStream);
  1237. foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
  1238. {
  1239. infos.Add(obj.ToObject<object>());
  1240. }
  1241. }
  1242. return Ok(new { state = 200, infos });
  1243. }
  1244. public record RecSchoolDate
  1245. {
  1246. public string id { get; set; }
  1247. public string name { get; set; }
  1248. public long dataCount { get; set; }
  1249. }
  1250. }
  1251. }