123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314 |
- using Azure.Cosmos;
- using Microsoft.AspNetCore.Http;
- using Microsoft.AspNetCore.Mvc;
- using StackExchange.Redis;
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text.Json;
- using System.Threading.Tasks;
- using TEAMModelBI.Tool;
- using TEAMModelOS.SDK.DI;
- using TEAMModelOS.SDK.Extension;
- using TEAMModelOS.SDK.Models;
- using TEAMModelOS.SDK.Models.Table;
- namespace TEAMModelBI.Controllers.BIHome
- {
- [Route("online")]
- [ApiController]
- public class OnLineController : ControllerBase
- {
- private readonly AzureCosmosFactory _azureCosmos;
- private readonly AzureStorageFactory _azureStorage;
- private readonly AzureRedisFactory _azureRedis;
- public OnLineController(AzureCosmosFactory azureCosmos, AzureStorageFactory azureStorage, AzureRedisFactory azureRedis)
- {
- _azureCosmos = azureCosmos;
- _azureStorage = azureStorage;
- _azureRedis = azureRedis;
- }
- /// <summary>
- /// 总数统计
- /// </summary>
- /// <returns></returns>
- [HttpPost("get-count")]
- public async Task<IActionResult> GetCount()
- {
- var cosmosClient = _azureCosmos.GetCosmosClient();
- var table = _azureStorage.GetCloudTableClient().GetTableReference("IESLogin");
- DateTimeOffset dateTime = DateTimeOffset.UtcNow;
- var (daySt, dayEt) = TimeHelper.GetStartOrEnd(dateTime); //今天开始时间 13位
- var (daySf, dayEf) = TimeHelper.GetStartOrEnd(dateTime, dateLenth: false); //今天开始时间 10位
- var (lastDayS, lastdayE) = TimeHelper.GetStartOrEnd(DateTimeOffset.Parse($"{dateTime.Year}-{dateTime.Month}-{dateTime.Day - 1}")); //昨天开始时间
-
- var near7S = dateTime.AddDays(-7).ToUnixTimeMilliseconds(); //前七天的开始时间
- var near7E = dateTime.ToUnixTimeMilliseconds(); //当前结束时间
- long hour1 = dateTime.AddHours(-1).ToUnixTimeMilliseconds(); //一小时前时间戳
- int areaCnt = 0; //学区总数
- int scCnt = 0; //学校总数
- int tchCnt = 0; //教师总数
- int stuCnt = 0; //学生总数
- int onStuCnt = 0; //学生在线人数
- int onTchCnt = 0; //教师在线人数
- int todayScCnt = 0; //今日新增学校数
- int todayTchCnt = 0; //今日新增教师
- int todayStuCnt = 0; //今日新增学生数
- string currentSql = "select value(count(c.id)) from c";
- areaCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Normal", currentSql, "Base-Area");
- scCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", currentSql, "Base");
- tchCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", currentSql, "Base");
- stuCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Student", "select value(count(c.id)) from c where c.pk='Base'");
- string addSql = $"select value(count(c.id)) from c where c.pk='Base' and c.createTime >={daySf} and c.createTime <= {dayEf}";
- todayScCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", addSql, "Base");
- todayTchCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", addSql, "Base");
- todayStuCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Student", addSql, "Base");
- List<RecOnLine> recStuOnLines = new();
- 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() { }))
- {
- recStuOnLines.Add(item);
- }
- onStuCnt = (from rs in recStuOnLines from l in rs.loginInfos where l.expire >= hour1 select rs).ToList().Count(); //linq查询
- //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 表达式查询
- List<RecOnLine> recTecOnLines = new();
- 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") }))
- {
- recTecOnLines.Add(item);
- }
- onTchCnt = (from rs in recTecOnLines from l in rs.loginInfos where l.expire >= hour1 select rs).ToList().Count(); //linq查询
- //onStuCnt = 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 表达式查询
-
- return Ok(new { state = 200, areaCnt, scCnt, tchCnt, stuCnt, todayScCnt, todayTchCnt, todayStuCnt, onStuCnt, onTchCnt});
- }
- /// <summary>
- /// 在线人数趋势图
- /// </summary>
- /// <returns></returns>
- [HttpPost("get-trend")]
- public async Task<IActionResult> GetTrend()
- {
- var table = _azureStorage.GetCloudTableClient().GetTableReference("IESLogin");
- DateTimeOffset dateTime = DateTimeOffset.UtcNow;
- var (daySt, dayEt) = TimeHelper.GetStartOrEnd(dateTime); //今天开始时间 13位
- var dateDay = dateTime.ToString("yyyyMMdd"); //获取当天的日期
- Dictionary<int, int> allDays = new(); //所有在线人数
- Dictionary<int, int> tchDays = new(); //教师在线人数
- Dictionary<int, int> stuDays = new(); //学生在线人数
- Dictionary<int, int> tmdDays = new(); //醍摩豆账户学生
- SortedSetEntry[] tchDay = _azureRedis.GetRedisClient(8).SortedSetRangeByScoreWithScores($"Login:IES:teacher:{dateDay}");
- if (tchDay.Length > 0)
- {
- foreach (var item in tchDay)
- {
- int val = ((int)item.Score);
- int key = ((int)item.Element);
- tchDays.Add(key, val);
- if (allDays.ContainsKey(key))
- allDays.Add(key, (allDays[key] + val));
- else
- allDays.Add(key, val);
- }
- }
- else
- {
- string tableSqlTch = $"PartitionKey eq 'HourLogin' and RowKey ge '{daySt}' and RowKey le '{dayEt}' and Teacher ne 0";
- List<HourLogin> hourLoginsTch = await table.QueryWhereString<HourLogin>(tableSqlTch);
- var hourTchCnt = hourLoginsTch.GroupBy(x => x.Hour).Select(k => new { key = int.Parse(k.Key.ToString().Substring(8, 2)), value = k.Count() }).ToList();
- if (hourTchCnt.Count > 0)
- {
- foreach (var item in hourTchCnt)
- {
- await _azureRedis.GetRedisClient(8).SortedSetIncrementAsync($"Login:IES:teacher:{dateDay}", $"{item.key}", item.value);//存一天24小时
- tchDays.Add(item.key, item.value);
- if (allDays.ContainsKey(item.key))
- allDays.Add(item.key, (allDays[item.key] + item.value));
- else
- allDays.Add(item.key, item.value);
- }
- }
- }
- SortedSetEntry[] stuDay = _azureRedis.GetRedisClient(8).SortedSetRangeByScoreWithScores($"Login:IES:student:{dateDay}");
- if (stuDay.Length > 0)
- {
- foreach (var item in stuDay)
- {
- int val = (int)item.Score;
- int key = (int)item.Element;
- stuDays.Add(key, val);
- if (allDays.ContainsKey(key))
- allDays.Add(key, (allDays[key] + val));
- else
- allDays.Add(key, val);
- }
- }
- else
- {
- string tableSqlStu = $"PartitionKey eq 'HourLogin' and RowKey ge '{daySt}' and RowKey le '{dayEt}' and Student ne 0";
- List<HourLogin> hourLoginsStu = await table.QueryWhereString<HourLogin>(tableSqlStu);
- var hourStuCnt = hourLoginsStu.GroupBy(x => x.Hour).Select(k => new { key = int.Parse(k.Key.ToString().Substring(8, 2)), value = k.Count() }).ToList();
- if (hourStuCnt.Count > 0)
- {
- foreach (var item in hourStuCnt)
- {
- await _azureRedis.GetRedisClient(8).SortedSetIncrementAsync($"Login:IES:student:{dateDay}", $"{item.key}", item.value);//存一天24小时
- stuDays.Add(item.key, item.value);
- if (allDays.ContainsKey(item.key))
- allDays.Add(item.key, (allDays[item.key] + item.value));
- else
- allDays.Add(item.key, item.value);
- }
- }
- }
- SortedSetEntry[] tmdDay = _azureRedis.GetRedisClient(8).SortedSetRangeByScoreWithScores($"Login:IES:tmduser:{dateDay}");
- if (tmdDay.Length > 0)
- {
- foreach (var item in stuDay)
- {
- int val = (int)item.Score;
- int key = (int)item.Element;
- tmdDays.Add(key, val);
- if (allDays.ContainsKey(key))
- allDays.Add(key, (allDays[key] + val));
- else
- allDays.Add(key, val);
- }
- }
- else
- {
- string tableSqlTmd = $"PartitionKey eq 'HourLogin' and RowKey ge '{daySt}' and RowKey le '{dayEt}' and TmdUser ne 0";
- List<HourLogin> hourLoginsTmd = await table.QueryWhereString<HourLogin>(tableSqlTmd);
- var hourTmdCnt = hourLoginsTmd.GroupBy(x => x.Hour).Select(k => new { key = int.Parse(k.Key.ToString().Substring(8, 2)), value = k.Count() }).ToList();
- if (hourTmdCnt.Count > 0)
- {
- foreach (var item in hourTmdCnt)
- {
- await _azureRedis.GetRedisClient(8).SortedSetIncrementAsync($"Login:IES:tmduser:{dateDay}", $"{item.key}", item.value);//存一天24小时
- tmdDays.Add(item.key, item.value);
- if (allDays.ContainsKey(item.key))
- allDays.Add(item.key, (allDays[item.key] + item.value));
- else
- allDays.Add(item.key, item.value);
- }
- }
- }
-
- return Ok(new { state = 200, allDays= allDays.ToList(), tchDays=tchDays.ToList(), stuDays= stuDays.ToList(), tmdDays= tmdDays.ToList() });
- }
- /// <summary>
- /// 课例趋势图
- /// </summary>
- /// <returns></returns>
- [HttpPost("get-lessontrend")]
- public async Task<IActionResult> GetLessonTrend()
- {
- DateTimeOffset dateTime = DateTimeOffset.UtcNow;
- var cosmosClient = _azureCosmos.GetCosmosClient();
- int year = dateTime.Year; //当前年
- int month = dateTime.Month; //当前月
- int day = dateTime.Day; //当天
- int hour = dateTime.Hour; //当前小时
- Dictionary<int, int> scLessCnt = new(); //学校课例
- Dictionary<int, int> tecLessCnt = new(); //教师课例
- Dictionary<int, int> yasterdayCnt = new(); //昨天24小时课例
- for (int i = 0; i < 24; i++)
- {
- if (hour >= i)
- {
- DateTimeOffset timeHour = new DateTime(year, month, day, i, 0, 0);
- var (hourS, hourE) = TimeHelper.GetStartOrEnd(timeHour, type: "hour");
- 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() { }))
- {
- scLessCnt.Add(i, item);
- }
- 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") }))
- {
- tecLessCnt.Add(i, item);
- }
- }
- DateTimeOffset yesterday = new DateTime(year, month, day - 1, i, 0, 0);
- var (yHourS, yHourE) = TimeHelper.GetStartOrEnd(yesterday, type: "hour");
- string sql = $"select value(count(c.id)) from c where c.pk='LessonRecord' and c.startTime >= {yHourS} and c.startTime <= {yHourE}";
- int hourLessCnt = await CommonFind.GetSqlValueCount(cosmosClient, new List<string> { "School", "Teacher" }, sql);
- yasterdayCnt.Add(i, hourLessCnt);
- }
- return Ok(new { state = 200, scLessCnt = scLessCnt.ToList(), tecLessCnt = tecLessCnt.ToList(), yasterdayCnt = yasterdayCnt.ToList() });
- }
- /// <summary>
- /// 版本数量占比
- /// </summary>
- /// <returns></returns>
- [HttpPost("get-edition")]
- public async Task<IActionResult> GetEdition()
- {
- var cosmosClient = _azureCosmos.GetCosmosClient();
- List<RecScEd> scEdCnt = new();
- var ScSql = $"select c.id,c.name,c.size,c.scale from c";
- await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<RecScEd>(queryText: ScSql, requestOptions:new QueryRequestOptions() { PartitionKey= new PartitionKey("Base")}))
- {
- scEdCnt.Add(item);
- }
- scEdCnt.ForEach(async scProductCnt =>
- {
- var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(scProductCnt.id, new PartitionKey("ProductSum"));
- if (response.Status == 200)
- {
- using var json = await JsonDocument.ParseAsync(response.ContentStream);
- SchoolProductSum ScProductSum = json.ToObject<SchoolProductSum>();
- scProductCnt.serial = ScProductSum.serial.Count();
- scProductCnt.service = ScProductSum.service.Count();
- scProductCnt.hard = ScProductSum.hard.Count();
- }
- });
- return Ok(new { state = 200, scEdCnt });
- }
- public record RecOnLine
- {
- public string id { get; set; }
- public string name { get; set; }
- public string code { get; set; }
- public List<Teacher.LoginInfo> loginInfos { get; set; }
- }
- public class RecScEd
- {
- public string id { get; set; }
- public string name { get; set; }
- public int size { get; set; }
- public int scale { get; set; }
- public int serial { get; set; } = 0;//软体
- public int service { get; set; } = 0; //服务
- public int hard { get; set; } = 0; //硬体
- }
- }
- }
|