using Azure.Cosmos; using Azure.Storage.Blobs; using Azure.Storage.Blobs.Models; using Microsoft.AspNetCore.Http; using Microsoft.AspNetCore.Mvc; using StackExchange.Redis; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Text.Json; using System.Threading.Tasks; using TEAMModelBI.Models.RecordM; using TEAMModelBI.Tool; using TEAMModelOS.SDK.Context.BI; 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; } /// /// 总数统计 /// /// [HttpPost("get-count")] public async Task GetCount(JsonElement jsonElement) { var cosmosClient = _azureCosmos.GetCosmosClient(); var table = _azureStorage.GetCloudTableClient().GetTableReference("IESLogin"); var blobClient = _azureStorage.GetBlobContainerClient($"0-public"); jsonElement.TryGetProperty("site", out JsonElement site); if ($"{site}".Equals(BIConst.Global)) { cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global); table = _azureStorage.GetCloudTableClient(BIConst.Global).GetTableReference("IESLogin"); blobClient = _azureStorage.GetBlobContainerClient($"0-public", BIConst.Global); } DateTimeOffset dateTime = DateTimeOffset.UtcNow; string cDay = dateTime.ToString("yyyyMMdd"); var (daySt, dayEt) = TimeHelper.GetStartOrEnd(dateTime); //今天开始时间 13位 var (daySf, dayEf) = TimeHelper.GetStartOrEnd(dateTime, dateLenth: false); //今天开始时间 10位 var (lastDayS, lastdayE) = TimeHelper.GetStartOrEnd(dateTime.AddDays(-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 apiCnt = 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.createTime >={daySt} and c.createTime <= {dayEt}"; todayScCnt = await CommonFind.GetSqlValueCount(cosmosClient, "School", addSql, "Base"); todayTchCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", addSql, "Base"); todayStuCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Student", addSql, "Base"); string onStuSql = $"select value(count(c.id)) from c join t in c.loginInfos where array_length(c.loginInfos) > 0 and t.expire > {hour1} and c.pk='Base'"; onTchCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Teacher", onStuSql); onStuCnt = await CommonFind.GetSqlValueCount(cosmosClient, "Student", onStuSql); //接口访问量 List recCnts = new(); await foreach (BlobItem blobItem in blobClient.GetBlobsAsync(BlobTraits.None, BlobStates.None, $"visitCnt/{cDay}")) { BlobClient tempBlobClient = blobClient.GetBlobClient(blobItem.Name); if (await tempBlobClient.ExistsAsync()) { using (var meomoryStream = new MemoryStream()) { var response = blobClient.GetBlobClient($"{blobItem.Name}").DownloadTo(meomoryStream); RecCnt recCnt = Encoding.UTF8.GetString(meomoryStream.ToArray()).ToString().ToObject(); recCnts.Add(recCnt); } } } apiCnt = recCnts.Select(x => x.apiCnt.Select(s => s.count).Sum()).Sum(); //List recStuOnLines = new(); //await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Student").GetItemQueryIterator(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); //} //List recTecOnLines = new(); //await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryIterator(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); //} ////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 { rss.loginInfos.Find(f => f.expire >= hour1) } }).Where(w => w.loginInfos.FirstOrDefault() != null).ToList().Count(); //lambda 表达式查询 教师查询人数 ////onTchCnt = (from rs in recTecOnLines from l in rs.loginInfos where l.expire >= hour1 select rs).ToList().Count(); //linq查询 教师查询人数 //onTchCnt = recTecOnLines.Select(rss => new RecOnLine { id = rss.id,code=rss.code, name =rss.name,loginInfos = new List { 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, apiCnt }); } /// /// 在线人数趋势图 /// /// [HttpPost("get-trend")] public async Task GetTrend(JsonElement jsonElement) { var table = _azureStorage.GetCloudTableClient().GetTableReference("IESLogin"); var redisClinet = _azureRedis.GetRedisClient(8); jsonElement.TryGetProperty("site", out JsonElement site); if ($"{site}".Equals(BIConst.Global)) { table = _azureStorage.GetCloudTableClient(BIConst.Global).GetTableReference("IESLogin"); redisClinet = _azureRedis.GetRedisClient(dbnum: 8, name: BIConst.Global); } DateTimeOffset dateTime = DateTimeOffset.UtcNow; var (daySt, dayEt) = TimeHelper.GetStartOrEnd(dateTime); //今天开始时间 13位 var (strDaySt, strDayEt) = TimeHelper.GetUnixToDate(daySt, dayEt, "yyyyMMddHH"); var dateDay = dateTime.ToString("yyyyMMdd"); //获取当天的日期 //daySt.ToString("yyyyMMddHH"); Dictionary allDays = new(); //所有在线人数 Dictionary tchDays = new(); //教师在线人数 Dictionary stuDays = new(); //学生在线人数 Dictionary tmdDays = new(); //醍摩豆账户学生 SortedSetEntry[] tchDay = redisClinet.SortedSetRangeByScoreWithScores($"Login:IES:teacher:{dateDay}"); if (tchDay.Length > 0) { foreach (var item in tchDay) { int val = ((int)item.Score); int key = ((int)item.Element); //var utcTo = new DateTimeOffset(new DateTime(dateTime.Year, dateTime.Month, dateTime.Day, key, 0, 0)).Hour; //var hour = int.Parse(DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {key}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH")); tchDays.Add(key, val); if (allDays.ContainsKey(key)) allDays[key] = (allDays[key] + val); else allDays.Add(key, val); } } else { string tableSqlTch = $"PartitionKey eq 'HourLogin' and RowKey ge '{strDaySt}' and RowKey le '{strDayEt}'"; List hourLoginsTch = await table.QueryWhereString(tableSqlTch); if (hourLoginsTch.Count > 0) { foreach (var item in hourLoginsTch) { await redisClinet.SortedSetIncrementAsync($"Login:IES:teacher:{dateDay}", $"{item.Hour}", item.Teacher);//存一天24小时 //var utcTo = new DateTimeOffset(new DateTime(dateTime.Year, dateTime.Month, dateTime.Day, item.Hour, 0, 0)).Hour; //var hour = int.Parse(DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {item.Hour}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH")); tchDays.Add(item.Hour, item.Teacher); if (allDays.ContainsKey(item.Hour)) allDays[item.Hour] = (allDays[item.Hour] + item.Teacher); else allDays.Add(item.Hour, item.Teacher); } } } SortedSetEntry[] stuDay = redisClinet.SortedSetRangeByScoreWithScores($"Login:IES:student:{dateDay}"); if (stuDay.Length > 0) { foreach (var item in stuDay) { int val = (int)item.Score; int key = (int)item.Element; //var utcTo = new DateTimeOffset(new DateTime(dateTime.Year, dateTime.Month, dateTime.Day, key, 0, 0)).Hour; //var hour = int.Parse(DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {key}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH")); stuDays.Add(key, val); if (allDays.ContainsKey(key)) allDays[key] = (allDays[key] + val); else allDays.Add(key, val); } } else { string tableSqlStu = $"PartitionKey eq 'HourLogin' and RowKey ge '{strDaySt}' and RowKey le '{strDayEt}'"; List hourLoginsStu = await table.QueryWhereString(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 (hourLoginsStu.Count > 0) { foreach (var item in hourLoginsStu) { await redisClinet.SortedSetIncrementAsync($"Login:IES:student:{dateDay}", $"{item.Hour}", item.Student);//存一天24小时 //var utcTo = new DateTimeOffset(new DateTime(dateTime.Year, dateTime.Month, dateTime.Day, item.Hour, 0, 0)).Hour; //var hour = int.Parse(DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {item.Hour}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH")); stuDays.Add(item.Hour, item.Student); if (allDays.ContainsKey(item.Hour)) allDays[item.Hour] = (allDays[item.Hour] + item.Student); else allDays.Add(item.Hour, item.Student); } } } SortedSetEntry[] tmdDay = redisClinet.SortedSetRangeByScoreWithScores($"Login:IES:tmduser:{dateDay}"); if (tmdDay.Length > 0) { foreach (var item in tmdDay) { int val = (int)item.Score; int key = (int)item.Element; //var utcTo = new DateTimeOffset(new DateTime(dateTime.Year, dateTime.Month, dateTime.Day, key, 00, 00)).Hour; //var hour = int.Parse(DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {key}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH")); tmdDays.Add(key, val); if (allDays.ContainsKey(key)) allDays[key] = (allDays[key] + val); else allDays.Add(key, val); } } else { string tableSqlTmd = $"PartitionKey eq 'HourLogin' and RowKey ge '{strDaySt}' and RowKey le '{strDayEt}'"; List hourLoginsTmd = await table.QueryWhereString(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 (hourLoginsTmd.Count > 0) { foreach (var item in hourLoginsTmd) { await redisClinet.SortedSetIncrementAsync($"Login:IES:tmduser:{dateDay}", $"{item.Hour}", item.TmdUser);//存一天24小时 var utcTo = new DateTimeOffset(new DateTime(dateTime.Year, dateTime.Month, dateTime.Day, item.Hour, 00, 00)).Hour; //var hour = int.Parse(DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {item.Hour}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH")); tmdDays.Add(utcTo, item.TmdUser); if (allDays.ContainsKey(utcTo)) allDays[utcTo] = (allDays[utcTo] + item.TmdUser); else allDays.Add(utcTo, item.TmdUser); } } } 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() }); } /// /// 课例趋势图 /// /// [HttpPost("get-lessontrend")] public async Task GetLessonTrend(JsonElement jsonElement) { DateTimeOffset dateTime = DateTimeOffset.UtcNow; var cosmosClient = _azureCosmos.GetCosmosClient(); jsonElement.TryGetProperty("site", out JsonElement site); if ($"{site}".Equals(BIConst.Global)) { cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global); } int year = dateTime.Year; //当前年 int month = dateTime.Month; //当前月 int day = dateTime.Day; //当天 var lestDate = dateTime.AddDays(-1); //昨天 int hour = int.Parse(DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {dateTime.Hour}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH")); //当前小时 var (daySt, dayEt) = TimeHelper.GetStartOrEnd(dateTime); //今天开始时间 13位 var (lastDayS, lastdayE) = TimeHelper.GetStartOrEnd(lestDate); //昨天开始时间 Dictionary sdOpenLesn = new(); //今日开课 Dictionary sdUpdLesn = new(); //今日上传课例 Dictionary ydOpenLesn = new(); //昨日开课 Dictionary ydUpdLesn = new(); //昨日上传课例 List sameDayLesn = new(); //今天课例 List yesterDayLesn = new(); //昨日课例 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}"; await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator(queryText: lesnSql, requestOptions: new QueryRequestOptions() { })) { sameDayLesn.Add(item); } await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryIterator(queryText: lesnSql, requestOptions: new QueryRequestOptions() { })) { sameDayLesn.Add(item); } string yesterDayLesnSql = $"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}"; await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator(queryText: yesterDayLesnSql, requestOptions: new QueryRequestOptions() { })) { yesterDayLesn.Add(item); } await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryIterator(queryText: yesterDayLesnSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("LessonRecord") })) { yesterDayLesn.Add(item); } 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"); var openLesn = sameDayLesn.Where(item => item.startTime >= hourS && item.startTime <= hourE && item.upload == 0); sdOpenLesn.Add(i, openLesn.Count()); var UpdLesn = sameDayLesn.Where(item => item.startTime >= hourS && item.startTime <= hourE && item.upload == 1); sdUpdLesn.Add(i, openLesn.Count()); } DateTimeOffset yesterday = new DateTime(lestDate.Year, lestDate.Month, lestDate.Day, i, 0, 0); var (yHourS, yHourE) = TimeHelper.GetStartOrEnd(yesterday, type: "hour"); var yOpenLesn = yesterDayLesn.Where(item => item.startTime >= yHourS && item.startTime <= yHourE && item.upload == 0).ToList(); ydOpenLesn.Add(i, yOpenLesn.Count); var yUpdLesn = yesterDayLesn.Where(item => item.startTime >= yHourS && item.startTime <= yHourE && item.upload == 1).ToList(); ydUpdLesn.Add(i, yUpdLesn.Count); } return Ok(new { state = 200, sdOpenLesn = sdOpenLesn.ToList(), sdUpdLesn = sdUpdLesn.ToList(), ydOpenLesn = ydOpenLesn.ToList(), ydUpdLesn = ydUpdLesn.ToList() }); } /// /// 版本数量占比 /// /// [HttpPost("get-edition")] public async Task GetEdition(JsonElement jsonElement) { var cosmosClient = _azureCosmos.GetCosmosClient(); jsonElement.TryGetProperty("site", out JsonElement site); if ($"{site}".Equals(BIConst.Global)) { cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global); } int beCnt = 0; //基础班 int seCnt = 0; //标准版 int peCnt = 0; //专业版 List 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(queryText: ScSql, requestOptions:new QueryRequestOptions() { PartitionKey= new PartitionKey("Base")})) { scEdCnt.Add(item); } scEdCnt.ForEach(async scProCnt => { var response = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(scProCnt.id, new PartitionKey("ProductSum")); if (response.Status == 200) { using var json = await JsonDocument.ParseAsync(response.ContentStream); SchoolProductSum ScProductSum = json.ToObject(); //scProCnt.serial = ScProductSum.serial.Count(); //scProCnt.service = ScProductSum.service.Count(); //scProCnt.hard = ScProductSum.hard.Count(); int pSeriCnt = ScProductSum.serial.Count(); int pServCnt = ScProductSum.service.Count(); int pHardCnt = ScProductSum.hard.Count(); scProCnt.serial = pSeriCnt; scProCnt.service = pServCnt; scProCnt.hard = pHardCnt; if (scProCnt.scale >= 500 && (pSeriCnt > 0 || pServCnt > 0 || pHardCnt > 0)) peCnt += 1; } if (scProCnt.scale >= 500 && scProCnt.serial == 0 && scProCnt.service == 0 && scProCnt.hard == 0) seCnt += 1; if (scProCnt.scale == 0) beCnt += 1; }); return Ok(new { state = 200, beCnt, seCnt, peCnt, scEdCnt }); } /// /// 开课 /// /// /// public async Task GetTmdCount(JsonElement jsonElement) { if(jsonElement.TryGetProperty("tmdId", out JsonElement tmdId)) return BadRequest(); var cosmosClient = _azureCosmos.GetCosmosClient(); var table = _azureStorage.GetCloudTableClient().GetTableReference("IESLogin"); var blobClient = _azureStorage.GetBlobContainerClient($"0-public"); jsonElement.TryGetProperty("site", out JsonElement site); if ($"{site}".Equals(BIConst.Global)) { cosmosClient = _azureCosmos.GetCosmosClient(name: BIConst.Global); table = _azureStorage.GetCloudTableClient(BIConst.Global).GetTableReference("IESLogin"); blobClient = _azureStorage.GetBlobContainerClient($"0-public", BIConst.Global); } List schools = await CommonFind.FindSchoolIds(cosmosClient, $"{tmdId}"); return Ok(new { state = 200 }); } /// /// 记录在线人数 /// public record RecOnLine { public string id { get; set; } public string name { get; set; } public string code { get; set; } public List loginInfos { get; set; } } /// /// 记录学校版本信息 /// public record 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; //硬体 } /// /// 记录课例 /// public record RecLesn { public string id { get; set; } public string name { get; set; } public string code { get; set; } public string school { get; set; } public string scope{get;set;} public long startTime { get; set; } public int upload { get; set; } } } }