TEAMModelOS/TEAMModelBI/Controllers/BIHome/OnLineController.cs

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<string, int> allDays = new();  //所有在线人数
            Dictionary<string, int> tchDays = new();  //教师在线人数
            Dictionary<string, int> stuDays = new();  //学生在线人数
            Dictionary<string, 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);
                    var hour = DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {key}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH:mm");                    
                    tchDays.Add(hour, val);
                    if (allDays.ContainsKey(hour))
                        allDays[hour] = (allDays[hour] + val);
                    else
                        allDays.Add(hour, 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小时
                        var hour = DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {item.key}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH:mm");
                        tchDays.Add(hour, item.value);
                        if (allDays.ContainsKey(hour))
                            allDays[hour] = (allDays[hour] + item.value);
                        else
                            allDays.Add(hour, 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;
                    var hour = DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {key}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH:mm");
                    stuDays.Add(hour, val);
                    if (allDays.ContainsKey(hour))
                        allDays[hour] = (allDays[hour] + val);
                    else
                        allDays.Add(hour, 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小时
                        var hour = DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {item.key}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH:mm");
                        stuDays.Add(hour, item.value);
                        if (allDays.ContainsKey(hour))
                            allDays[hour] = (allDays[hour] + item.value);
                        else
                            allDays.Add(hour, 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;
                    var hour = DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {key}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH:mm");
                    tmdDays.Add(hour, val);
                    if (allDays.ContainsKey(hour))
                        allDays[hour] = (allDays[hour] + val);
                    else
                        allDays.Add(hour, 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小时
                        var hour = DateTime.SpecifyKind(Convert.ToDateTime($"{dateTime.Year}/{dateTime.Month}/{ dateTime.Day} {item.key}:00:00"), DateTimeKind.Utc).ToLocalTime().ToString("HH:mm");
                        tmdDays.Add(hour, item.value);
                        if (allDays.ContainsKey(hour))
                            allDays[hour] = (allDays[hour] + item.value);
                        else
                            allDays.Add(hour, 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; //硬体
        }


    }
}