OnLineController.cs 21 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391
  1. using Azure.Cosmos;
  2. using Microsoft.AspNetCore.Http;
  3. using Microsoft.AspNetCore.Mvc;
  4. using StackExchange.Redis;
  5. using System;
  6. using System.Collections.Generic;
  7. using System.Linq;
  8. using System.Text.Json;
  9. using System.Threading.Tasks;
  10. using TEAMModelBI.Tool;
  11. using TEAMModelOS.SDK.DI;
  12. using TEAMModelOS.SDK.Extension;
  13. using TEAMModelOS.SDK.Models;
  14. using TEAMModelOS.SDK.Models.Table;
  15. namespace TEAMModelBI.Controllers.BIHome
  16. {
  17. [Route("online")]
  18. [ApiController]
  19. public class OnLineController : ControllerBase
  20. {
  21. private readonly AzureCosmosFactory _azureCosmos;
  22. private readonly AzureStorageFactory _azureStorage;
  23. private readonly AzureRedisFactory _azureRedis;
  24. public OnLineController(AzureCosmosFactory azureCosmos, AzureStorageFactory azureStorage, AzureRedisFactory azureRedis)
  25. {
  26. _azureCosmos = azureCosmos;
  27. _azureStorage = azureStorage;
  28. _azureRedis = azureRedis;
  29. }
  30. /// <summary>
  31. /// 总数统计
  32. /// </summary>
  33. /// <returns></returns>
  34. [HttpPost("get-count")]
  35. public async Task<IActionResult> GetCount()
  36. {
  37. var cosmosClient = _azureCosmos.GetCosmosClient();
  38. var table = _azureStorage.GetCloudTableClient().GetTableReference("IESLogin");
  39. DateTimeOffset dateTime = DateTimeOffset.UtcNow;
  40. var (daySt, dayEt) = TimeHelper.GetStartOrEnd(dateTime); //今天开始时间 13位
  41. var (daySf, dayEf) = TimeHelper.GetStartOrEnd(dateTime, dateLenth: false); //今天开始时间 10位
  42. var (lastDayS, lastdayE) = TimeHelper.GetStartOrEnd(DateTimeOffset.Parse($"{dateTime.Year}-{dateTime.Month}-{dateTime.Day - 1}")); //昨天开始时间
  43. var near7S = dateTime.AddDays(-7).ToUnixTimeMilliseconds(); //前七天的开始时间
  44. var near7E = dateTime.ToUnixTimeMilliseconds(); //当前结束时间
  45. long hour1 = dateTime.AddHours(-1).ToUnixTimeMilliseconds(); //一小时前时间戳
  46. int areaCnt = 0; //学区总数
  47. int scCnt = 0; //学校总数
  48. int tchCnt = 0; //教师总数
  49. int stuCnt = 0; //学生总数
  50. int onStuCnt = 0; //学生在线人数
  51. int onTchCnt = 0; //教师在线人数
  52. int todayScCnt = 0; //今日新增学校数
  53. int todayTchCnt = 0; //今日新增教师
  54. int todayStuCnt = 0; //今日新增学生数
  55. string currentSql = "select value(count(c.id)) from c";
  56. areaCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Normal", currentSql, "Base-Area");
  57. scCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", currentSql, "Base");
  58. tchCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", currentSql, "Base");
  59. stuCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Student", "select value(count(c.id)) from c where c.pk='Base'");
  60. string addSql = $"select value(count(c.id)) from c where c.pk='Base' and c.createTime >={daySf} and c.createTime <= {dayEf}";
  61. todayScCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", addSql, "Base");
  62. todayTchCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", addSql, "Base");
  63. todayStuCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Student", addSql, "Base");
  64. List<RecOnLine> recStuOnLines = new();
  65. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Student").GetItemQueryIterator<RecOnLine>(queryText: "select c.id,c.name,c.code,c.loginInfos from c where c.pk='Base' and array_length(c.loginInfos) > 0 ", requestOptions:new QueryRequestOptions() { }))
  66. {
  67. recStuOnLines.Add(item);
  68. }
  69. List<RecOnLine> recTecOnLines = new();
  70. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryIterator<RecOnLine>(queryText: "select c.id,c.name,c.code,c.loginInfos from c where array_length(c.loginInfos) > 0 ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
  71. {
  72. recTecOnLines.Add(item);
  73. }
  74. //onStuCnt = (from rs in recStuOnLines from l in rs.loginInfos where l.expire >= hour1 select rs).ToList().Count(); //linq查询 学生在线人数
  75. onStuCnt = recStuOnLines.Select(rss => new RecOnLine { id = rss.id,code=rss.code, name =rss.name,loginInfos = new List<Teacher.LoginInfo> { rss.loginInfos.Find(f => f.expire >= hour1) } }).Where(w => w.loginInfos.FirstOrDefault() != null).ToList().Count(); //lambda 表达式查询 教师查询人数
  76. //onTchCnt = (from rs in recTecOnLines from l in rs.loginInfos where l.expire >= hour1 select rs).ToList().Count(); //linq查询 教师查询人数
  77. onTchCnt = recTecOnLines.Select(rss => new RecOnLine { id = rss.id,code=rss.code, name =rss.name,loginInfos = new List<Teacher.LoginInfo> { rss.loginInfos.Find(f => f.expire >= hour1) } }).Where(w => w.loginInfos.FirstOrDefault() != null).ToList().Count(); //lambda 表达式查询 教师查询人数
  78. return Ok(new { state = 200, areaCnt, scCnt, tchCnt, stuCnt, todayScCnt, todayTchCnt, todayStuCnt, onStuCnt, onTchCnt});
  79. }
  80. /// <summary>
  81. /// 在线人数趋势图
  82. /// </summary>
  83. /// <returns></returns>
  84. [HttpPost("get-trend")]
  85. public async Task<IActionResult> GetTrend()
  86. {
  87. var table = _azureStorage.GetCloudTableClient().GetTableReference("IESLogin");
  88. DateTimeOffset dateTime = DateTimeOffset.UtcNow;
  89. var (daySt, dayEt) = TimeHelper.GetStartOrEnd(dateTime); //今天开始时间 13位
  90. var (strDaySt, strDayEt) = TimeHelper.GetUnixToDate(daySt, dayEt, "yyyyMMddHH");
  91. var dateDay = dateTime.ToString("yyyyMMdd"); //获取当天的日期
  92. daySt.ToString("yyyyMMddHH");
  93. Dictionary<int, int> allDays = new(); //所有在线人数
  94. Dictionary<int, int> tchDays = new(); //教师在线人数
  95. Dictionary<int, int> stuDays = new(); //学生在线人数
  96. Dictionary<int, int> tmdDays = new(); //醍摩豆账户学生
  97. SortedSetEntry[] tchDay = _azureRedis.GetRedisClient(8).SortedSetRangeByScoreWithScores($"Login:IES:teacher:{dateDay}");
  98. if (tchDay.Length > 0)
  99. {
  100. foreach (var item in tchDay)
  101. {
  102. int val = ((int)item.Score);
  103. int key = ((int)item.Element);
  104. var hour = int.Parse(DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {key}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH"));
  105. tchDays.Add(hour, val);
  106. if (allDays.ContainsKey(hour))
  107. allDays[hour] = (allDays[hour] + val);
  108. else
  109. allDays.Add(hour, val);
  110. }
  111. }
  112. else
  113. {
  114. string tableSqlTch = $"PartitionKey eq 'HourLogin' and RowKey ge '{strDaySt}' and RowKey le '{strDayEt}'";
  115. List<HourLogin> hourLoginsTch = await table.QueryWhereString<HourLogin>(tableSqlTch);
  116. if (hourLoginsTch.Count > 0)
  117. {
  118. foreach (var item in hourLoginsTch)
  119. {
  120. await _azureRedis.GetRedisClient(8).SortedSetIncrementAsync($"Login:IES:teacher:{dateDay}", $"{item.Hour}", item.Teacher);//存一天24小时
  121. var hour = int.Parse(DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {item.Hour}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH"));
  122. tchDays.Add(hour, item.Teacher);
  123. if (allDays.ContainsKey(hour))
  124. allDays[hour] = (allDays[hour] + item.Teacher);
  125. else
  126. allDays.Add(hour, item.Teacher);
  127. }
  128. }
  129. }
  130. SortedSetEntry[] stuDay = _azureRedis.GetRedisClient(8).SortedSetRangeByScoreWithScores($"Login:IES:student:{dateDay}");
  131. if (stuDay.Length > 0)
  132. {
  133. foreach (var item in stuDay)
  134. {
  135. int val = (int)item.Score;
  136. int key = (int)item.Element;
  137. var hour = int.Parse(DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {key}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH"));
  138. stuDays.Add(hour, val);
  139. if (allDays.ContainsKey(hour))
  140. allDays[hour] = (allDays[hour] + val);
  141. else
  142. allDays.Add(hour, val);
  143. }
  144. }
  145. else
  146. {
  147. string tableSqlStu = $"PartitionKey eq 'HourLogin' and RowKey ge '{strDaySt}' and RowKey le '{strDayEt}'";
  148. List<HourLogin> hourLoginsStu = await table.QueryWhereString<HourLogin>(tableSqlStu);
  149. //var hourStuCnt = hourLoginsStu.GroupBy(x => x.Hour).Select(k => new { key = int.Parse(k.Key.ToString().Substring(8, 2)), value = k.Count() }).ToList();
  150. if (hourLoginsStu.Count > 0)
  151. {
  152. foreach (var item in hourLoginsStu)
  153. {
  154. await _azureRedis.GetRedisClient(8).SortedSetIncrementAsync($"Login:IES:student:{dateDay}", $"{item.Hour}", item.Student);//存一天24小时
  155. var hour = int.Parse(DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {item.Hour}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH"));
  156. stuDays.Add(hour, item.Student);
  157. if (allDays.ContainsKey(hour))
  158. allDays[hour] = (allDays[hour] + item.Student);
  159. else
  160. allDays.Add(hour, item.Student);
  161. }
  162. }
  163. }
  164. SortedSetEntry[] tmdDay = _azureRedis.GetRedisClient(8).SortedSetRangeByScoreWithScores($"Login:IES:tmduser:{dateDay}");
  165. if (tmdDay.Length > 0)
  166. {
  167. foreach (var item in stuDay)
  168. {
  169. int val = (int)item.Score;
  170. int key = (int)item.Element;
  171. var hour = int.Parse(DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {key}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH"));
  172. tmdDays.Add(hour, val);
  173. if (allDays.ContainsKey(hour))
  174. allDays[hour] = (allDays[hour] + val);
  175. else
  176. allDays.Add(hour, val);
  177. }
  178. }
  179. else
  180. {
  181. string tableSqlTmd = $"PartitionKey eq 'HourLogin' and RowKey ge '{strDaySt}' and RowKey le '{strDayEt}'";
  182. List<HourLogin> hourLoginsTmd = await table.QueryWhereString<HourLogin>(tableSqlTmd);
  183. //var hourTmdCnt = hourLoginsTmd.GroupBy(x => x.Hour).Select(k => new { key = int.Parse(k.Key.ToString().Substring(8, 2)), value = k.Count() }).ToList();
  184. if (hourLoginsTmd.Count > 0)
  185. {
  186. foreach (var item in hourLoginsTmd)
  187. {
  188. await _azureRedis.GetRedisClient(8).SortedSetIncrementAsync($"Login:IES:tmduser:{dateDay}", $"{item.Hour}", item.TmdUser);//存一天24小时
  189. var hour = int.Parse(DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {item.Hour}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH"));
  190. tmdDays.Add(hour, item.TmdUser);
  191. if (allDays.ContainsKey(hour))
  192. allDays[hour] = (allDays[hour] + item.TmdUser);
  193. else
  194. allDays.Add(hour, item.TmdUser);
  195. }
  196. }
  197. }
  198. return Ok(new { state = 200,allDays = allDays.OrderBy(o=>o.Key).ToList(), tchDays=tchDays.OrderBy(o => o.Key).ToList(), stuDays= stuDays.OrderBy(o => o.Key).ToList(), tmdDays= tmdDays.OrderBy(o => o.Key).ToList() });
  199. }
  200. /// <summary>
  201. /// 课例趋势图
  202. /// </summary>
  203. /// <returns></returns>
  204. [HttpPost("get-lessontrend")]
  205. public async Task<IActionResult> GetLessonTrend()
  206. {
  207. DateTimeOffset dateTime = DateTimeOffset.UtcNow;
  208. var cosmosClient = _azureCosmos.GetCosmosClient();
  209. int year = dateTime.Year; //当前年
  210. int month = dateTime.Month; //当前月
  211. int day = dateTime.Day; //当天
  212. int hour = int.Parse(DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {dateTime.Hour}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH")); //当前小时
  213. Dictionary<int, int> scLessCnt = new(); //学校课例
  214. Dictionary<int, int> tchLessCnt = new(); //教师课例
  215. Dictionary<int, int> yesterdayCnt = new(); //昨天24小时课例
  216. var (daySt, dayEt) = TimeHelper.GetStartOrEnd(dateTime); //今天开始时间 13位
  217. var (lastDayS, lastdayE) = TimeHelper.GetStartOrEnd(DateTimeOffset.Parse($"{dateTime.Year}-{dateTime.Month}-{dateTime.Day - 1}")); //昨天开始时间
  218. List<RecLesn> scRecLesn = new(); //学校课例
  219. List<RecLesn> tchRecLesn = new(); //个人课例
  220. List<RecLesn> allRecLesn = new(); //昨天所有课例
  221. string lesnSql = $"select c.id,c.name,c.code,c.school,c.scope,c.startTime from c where c.pk='LessonRecord' and c.startTime >={daySt} and c.startTime <= {dayEt}";
  222. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<RecLesn>(queryText: lesnSql, requestOptions: new QueryRequestOptions() { }))
  223. {
  224. scRecLesn.Add(item);
  225. }
  226. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryIterator<RecLesn>(queryText: lesnSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("LessonRecord") }))
  227. {
  228. tchRecLesn.Add(item);
  229. }
  230. string allLesnSql = $"select c.id,c.name,c.code,c.school,c.scope,c.startTime from c where c.pk='LessonRecord' and c.startTime >={lastDayS} and c.startTime <= {lastdayE}";
  231. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<RecLesn>(queryText: allLesnSql, requestOptions: new QueryRequestOptions() { }))
  232. {
  233. allRecLesn.Add(item);
  234. }
  235. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryIterator<RecLesn>(queryText: allLesnSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("LessonRecord") }))
  236. {
  237. allRecLesn.Add(item);
  238. }
  239. for (int i = 0; i < 24; i++)
  240. {
  241. if (hour >= i)
  242. {
  243. DateTimeOffset timeHour = new DateTime(year, month, day, i, 0, 0);
  244. var (hourS, hourE) = TimeHelper.GetStartOrEnd(timeHour, type: "hour");
  245. var scLesn = scRecLesn.Where(item => item.startTime >= hourS && item.startTime <= hourE).ToList();
  246. scLessCnt.Add(i, scLesn.Count());
  247. var tchLesn = scRecLesn.Where(item => item.startTime >= hourS && item.startTime <= hourE).ToList();
  248. tchLessCnt.Add(i, tchLesn.Count());
  249. }
  250. DateTimeOffset yesterday = new DateTime(year, month, day - 1, i, 0, 0);
  251. var (yHourS, yHourE) = TimeHelper.GetStartOrEnd(yesterday, type: "hour");
  252. var allLesn = allRecLesn.Where(item => item.startTime >= yHourS && item.startTime <= yHourE).ToList();
  253. yesterdayCnt.Add(i, allLesn.Count());
  254. }
  255. ////通过循环实时查询课例统计
  256. //for (int i = 0; i < 24; i++)
  257. //{
  258. // if (hour >= i)
  259. // {
  260. // DateTimeOffset timeHour = new DateTime(year, month, day, i, 0, 0);
  261. // var (hourS, hourE) = TimeHelper.GetStartOrEnd(timeHour, type: "hour");
  262. // await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<int>(queryText: $"select value(count(c.id)) from c where c.pk='LessonRecord' and c.startTime >={hourS} and c.startTime <= {hourE}", requestOptions: new QueryRequestOptions() { }))
  263. // {
  264. // scLessCnt.Add(i, item);
  265. // }
  266. // await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryIterator<int>(queryText: $"select value(count(c.id)) from c where c.startTime >={hourS} and c.startTime <= {hourE}", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("LessonRecord") }))
  267. // {
  268. // tchLessCnt.Add(i, item);
  269. // }
  270. // }
  271. // DateTimeOffset yesterday = new DateTime(year, month, day - 1, i, 0, 0);
  272. // var (yHourS, yHourE) = TimeHelper.GetStartOrEnd(yesterday, type: "hour");
  273. // string sql = $"select value(count(c.id)) from c where c.pk='LessonRecord' and c.startTime >= {yHourS} and c.startTime <= {yHourE}";
  274. // int hourLessCnt = await CommonFind.GetSqlValueCount(cosmosClient, new List<string> { "School", "Teacher" }, sql);
  275. // yesterdayCnt.Add(i, hourLessCnt);
  276. //}
  277. return Ok(new { state = 200, scLessCnt = scLessCnt.ToList(), tchLessCnt = tchLessCnt.ToList(), yesterdayCnt = yesterdayCnt.ToList() });
  278. }
  279. /// <summary>
  280. /// 版本数量占比
  281. /// </summary>
  282. /// <returns></returns>
  283. [HttpPost("get-edition")]
  284. public async Task<IActionResult> GetEdition()
  285. {
  286. var cosmosClient = _azureCosmos.GetCosmosClient();
  287. List<RecScEd> scEdCnt = new();
  288. var ScSql = $"select c.id,c.name,c.size,c.scale from c";
  289. await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<RecScEd>(queryText: ScSql, requestOptions:new QueryRequestOptions() { PartitionKey= new PartitionKey("Base")}))
  290. {
  291. scEdCnt.Add(item);
  292. }
  293. scEdCnt.ForEach(async scProductCnt =>
  294. {
  295. var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(scProductCnt.id, new PartitionKey("ProductSum"));
  296. if (response.Status == 200)
  297. {
  298. using var json = await JsonDocument.ParseAsync(response.ContentStream);
  299. SchoolProductSum ScProductSum = json.ToObject<SchoolProductSum>();
  300. scProductCnt.serial = ScProductSum.serial.Count();
  301. scProductCnt.service = ScProductSum.service.Count();
  302. scProductCnt.hard = ScProductSum.hard.Count();
  303. }
  304. });
  305. return Ok(new { state = 200, scEdCnt });
  306. }
  307. /// <summary>
  308. /// 记录在线人数
  309. /// </summary>
  310. public record RecOnLine
  311. {
  312. public string id { get; set; }
  313. public string name { get; set; }
  314. public string code { get; set; }
  315. public List<Teacher.LoginInfo> loginInfos { get; set; }
  316. }
  317. /// <summary>
  318. /// 记录学校版本信息
  319. /// </summary>
  320. public record RecScEd
  321. {
  322. public string id { get; set; }
  323. public string name { get; set; }
  324. public int size { get; set; }
  325. public int scale { get; set; }
  326. public int serial { get; set; } = 0;//软体
  327. public int service { get; set; } = 0; //服务
  328. public int hard { get; set; } = 0; //硬体
  329. }
  330. /// <summary>
  331. /// 记录课例
  332. /// </summary>
  333. public record RecLesn
  334. {
  335. public string id { get; set; }
  336. public string name { get; set; }
  337. public string code { get; set; }
  338. public string school { get; set; }
  339. public string scope{get;set;}
  340. public long startTime { get; set; }
  341. }
  342. }
  343. }