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; }
}
}
}