SchoolController.cs 91 KB

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