SchoolController.cs 83 KB

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