TEAMModelOS/TEAMModelBI/Controllers/BIHome/OnLineController.cs

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.Data.SqlTypes;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using TEAMModelBI.Tool;
using TEAMModelOS.SDK.Context.BI;
using TEAMModelOS.SDK.Context.Constant;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Extension;
using TEAMModelOS.SDK.Models;
using TEAMModelOS.SDK.Models.Cosmos.BI;
using TEAMModelOS.SDK.Models.Service.BI;
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>
        [ProducesDefaultResponseType]
        [HttpPost("get-count")]
        public async Task<IActionResult> 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;   //今日新增学生数

            long datetime = dateTime.ToUnixTimeMilliseconds();
            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<RecCnt> recCnts = new();
            await foreach (BlobItem blobItem in blobClient.GetBlobsAsync(BlobTraits.None, BlobStates.None, $"visitCnt/{cDay}"))
            {
                if (blobItem.Name.EndsWith(".json") && !blobItem.Name.EndsWith("/days.json"))
                {
                    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<RecCnt>();
                            recCnts.Add(recCnt);
                        }
                    }

                }
            }
            apiCnt = recCnts.Select(x => x.apiCnt.Select(s => s.count).Sum()).Sum();
            string yymmss = TimeHelper.GetDateTime(datetime).ToString();
            return Ok(new { state = 200, yymmss, datetime, areaCnt, scCnt, tchCnt, stuCnt, todayScCnt, todayTchCnt, todayStuCnt, onStuCnt, onTchCnt, apiCnt });
        }

        /// <summary>
        /// 在线人数趋势图   //已对接
        /// </summary>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-trend")]
        public async Task<IActionResult> 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<long, int> allDays = new();  //所有在线人数
            Dictionary<long, int> tchDays = new();  //教师在线人数
            Dictionary<long, int> stuDays = new();  //学生在线人数
            Dictionary<long, int> 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<HourLogin> hourLoginsTch = await table.QueryWhereString<HourLogin>(tableSqlTch);

                if (hourLoginsTch.Count > 0) 
                {
                    foreach (var item in hourLoginsTch)
                    {
                        await redisClinet.SortedSetIncrementAsync($"Login:IES:teacher:{dateDay}", $"{item.Hour}", item.Teacher);//存一天24小时
                        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;
                    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<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 (hourLoginsStu.Count > 0)
                {
                    foreach (var item in hourLoginsStu)
                    {
                        await redisClinet.SortedSetIncrementAsync($"Login:IES:student:{dateDay}", $"{item.Hour}", item.Student);//存一天24小时
                        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;
                    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<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 (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;
                        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() });
        }

        /// <summary>
        /// 课例趋势图   //已对接
        /// </summary>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-lessontrend")]
        public async Task<IActionResult> 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);   //昨天开始时间

            var (monthDayS, monthDayE) = TimeHelper.GetStartOrEnd(lestDate, "monthDay");   //本月开始/结束时间
            var (yearS, yearE) = TimeHelper.GetStartOrEnd(lestDate, "yearMonth");   //本月开始/结束时间

            Dictionary<int, int> sdOpenLesn = new();  //今日开课
            Dictionary<int, int> sdUpdLesn = new();  //今日上传课例
            Dictionary<int, int> ydOpenLesn = new();  //昨日开课
            Dictionary<int, int> ydUpdLesn = new();  //昨日上传课例

            Dictionary<int, int> mthOpenLesn = new();  //本月开课
            Dictionary<int, int> mthUpdLesn = new();  //本月上传课例

            Dictionary<int, int> yrOpenLesn = new();  //本年开课
            Dictionary<int, int> yrUpdLesn = new();  //本年上传课例

            Dictionary<int, int> allOpenLesn = new();  //所有年开课
            Dictionary<int, int> allUpdLesn = new();  //所有年上传课例

            List<RecLesn> yearLeson = new();
            List<RecLesn> allLess = new();

            string allSql = "select c.id,c.name,c.code,c.school,c.scope,c.startTime  from c where c.pk='LessonRecord'";
            //所有的课例
            allLess = await CommonFind.GetObject<RecLesn>(cosmosClient, new List<string>() { "School", "Teacher" }, allSql);

            if (allLess.Count > 0)
            {
                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 openLesn1 = allLess.Where(item => item.startTime >= hourS && item.startTime <= hourE && item.upload == 0).Count();
                        var openLesn = allLess.Where(item => item.startTime >= hourS && item.startTime <= hourE && item.upload == 0).ToList();
                        sdOpenLesn.Add(i, openLesn.Count());

                        var UpdLesn = allLess.Where(item => item.startTime >= hourS && item.startTime <= hourE && item.upload == 1).ToList();
                        sdUpdLesn.Add(i, UpdLesn.Count());

                    }

                    DateTimeOffset yesterday = new DateTime(lestDate.Year, lestDate.Month, lestDate.Day, i, 0, 0);
                    var (yHourS, yHourE) = TimeHelper.GetStartOrEnd(yesterday, type: "hour");

                    var yOpenLesn = allLess.Where(item => item.startTime >= yHourS && item.startTime <= yHourE && item.upload == 0).ToList();
                    ydOpenLesn.Add(i, yOpenLesn.Count);

                    var yUpdLesn = allLess.Where(item => item.startTime >= yHourS && item.startTime <= yHourE && item.upload == 1).ToList();
                    ydUpdLesn.Add(i, yUpdLesn.Count);
                }

                for (int i = 1; i <= day; i++)
                {
                    DateTimeOffset timeDay = new DateTime(year, month, i, 0, 0, 0);
                    var (dayS, dayE) = TimeHelper.GetStartOrEnd(timeDay);   //本月开始/结束时间

                    var openLesn = allLess.Where(item => item.startTime >= dayS && item.startTime <= dayE && item.upload == 0).ToList();
                    mthOpenLesn.Add(i, openLesn.Count());

                    var UpdLesn = allLess.Where(item => item.startTime >= dayS && item.startTime <= dayE && item.upload == 1).ToList();
                    mthUpdLesn.Add(i, UpdLesn.Count());
                }

                for (int i = 1; i <= month; i++)
                {
                    DateTimeOffset timeDay = DateTimeOffset.UtcNow;
                    if (i == 2)
                    {
                        int tempDay = (year % 4 == 0 && year % 100 != 0 || year % 400 == 0) ? tempDay = 29 : tempDay = 28;
                        timeDay = new DateTime(year, i, tempDay, 0, 0, 0);
                    }
                    else
                    {
                        timeDay = new DateTime(year, i, 1, 0, 0, 0);
                    }

                    var (monthS, monthE) = TimeHelper.GetStartOrEnd(timeDay, "month");   //本月开始/结束时间

                    var openLesn = allLess.Where(item => item.startTime >= monthS && item.startTime <= monthE && item.upload == 0).ToList();
                    yrOpenLesn.Add(i, openLesn.Count());

                    var UpdLesn = allLess.Where(item => item.startTime >= monthS && item.startTime <= monthE && item.upload == 1).ToList();
                    yrUpdLesn.Add(i, UpdLesn.Count());
                }

                yearLeson = allLess.Select(item => { item.startTime = TimeHelper.GetDateTime(item.startTime).Year; return item; }).ToList();
                List<RecLesn> oRecLess = yearLeson.Where(o => o.upload == 0).ToList();
                var allo = oRecLess.GroupBy(g => g.startTime).Select(s => new { key = s.Key, value = s.Count() }).ToList();
                if (allo.Count > 0)
                    allo.ForEach(a => { allOpenLesn.Add((int)a.key, a.value); });

                List<RecLesn> uRecLesns = yearLeson.Where(o => o.upload == 1).ToList();
                var allu = uRecLesns.GroupBy(g => g.startTime).Select(s => new { key = s.Key, value = s.Count() }).ToList();
                if (allu.Count > 0)
                    allu.ForEach(a => { allUpdLesn.Add((int)a.key, a.value); });
            }

            return Ok(new
            {
                state = RespondCode.Ok,
                hours = new { sdOpenLesn = sdOpenLesn.ToList(), sdUpdLesn = sdUpdLesn.ToList(), ydOpenLesn = ydOpenLesn.ToList(), ydUpdLesn = ydUpdLesn.ToList() },
                days = new { sdOpenLesn = mthOpenLesn.ToList(), sdUpdLesn = mthUpdLesn.ToList() },
                months = new { sdOpenLesn = yrOpenLesn.ToList(), sdUpdLesn = yrUpdLesn.ToList() },
                years = new { sdOpenLesn = allOpenLesn.ToList(), sdUpdLesn = allUpdLesn.ToList() }
            }) ;
        }

        /// <summary>
        /// 版本数量占比   //已对接
        /// </summary>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("get-edition")]
        public async Task<IActionResult> 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; //专业版
            int cuCnt = 0; //自訂版

            List<RecScEd> scEdCnt = new();
         

            var ScSql = $"select c.id,c.name,c.size,c.scale, c.edition, c.service from c";
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<RecScEd>(queryText: ScSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("BIRel") }))
            {
                scEdCnt.Add(item);
            }
            
            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<RecScEd>(queryText: ScSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("VirtualBase") }))
            {
                scEdCnt.Add(item);
            }          

            foreach (var item in scEdCnt)
            {
                bool isHaveScaleVersion = false;

                if (item.edition != null)
                {
                    if (!string.IsNullOrWhiteSpace(item.edition.scaleVersion))
                    {
                        isHaveScaleVersion = true;
                    }
                }
                // ※版本判定：1.學校管理(IPDYZYLC) + 學情分析(YMPCVCIM)=「標準版」 
                //           2.學校管理(IPDYZYLC) + 學情分析(YMPCVCIM) + 五育看板(YPXSJ6NJ) =「專業版」
                bool isIP = item.service.Exists(t => t == "IPDYZYLC");
                bool isYM = item.service.Exists(t => t == "YMPCVCIM");
                bool isYP = item.service.Exists(t => t == "YPXSJ6NJ");
                if (isHaveScaleVersion)
                {  // 自訂版
                    cuCnt += 1; 
                }
                else if (isIP && isYM && isYP)
                {  // 專業版
                    peCnt += 1;
                }
                else if (isIP && isYM && !isYP)
                {  // 標準版
                    seCnt += 1; 
                }
                else 
                {  // 基礎版
                    beCnt += 1;
                }                           
            }

            return Ok(new { state = 200, beCnt, seCnt, peCnt, cuCnt, scEdCnt });
        }

        /// <summary>
        /// 记录学校版本信息
        /// </summary>
        public record RecScEd
        {
            public string id { get; set; }
            public string name { get; set; }
            public int size { get; set; }
            public int scale { get; set; }
            public List<string> serial { get; set; } = new List<string>();//软体
            public List<string> service { get; set; } = new List<string>(); //服务
            public List<string> hard { get; set; } = new List<string>(); //硬体

            public Edition edition { get; set; }
        }

        /// <summary>
        /// 记录课例
        /// </summary>
        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; }
        }
    }
}