TEAMModelOS/TEAMModelOS.SDK/Models/Service/BIStatsWay/SchoolStatsWay.cs

using Azure.Cosmos;
using DocumentFormat.OpenXml.Bibliography;
using DocumentFormat.OpenXml.Math;
using MathNet.Numerics.LinearAlgebra.Double;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.Amqp.Framing;
using Newtonsoft.Json;
using StackExchange.Redis;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http.Headers;
using System.Net.Http;
using System.Net;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using TEAMModelOS.Models;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Extension;
using TEAMModelOS.SDK.Models.Cosmos.BI;
using TEAMModelOS.SDK.Models.Service.BI;
using Microsoft.Extensions.Configuration;
using System.Configuration;
using System.Net.Http.Json;

namespace TEAMModelOS.SDK.Models.Service.BIStatsWay
{
    public static class SchoolStatsWay
    {
        /// <summary>
        /// 统计单个学校
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="scId"></param>
        /// <returns></returns>
        public static async Task<StatsInfo> GetSingleSc(CosmosClient cosmosClient, IDatabase redisClinet, string scId, int year = 0)
        {
            DateTimeOffset dateTime = DateTimeOffset.UtcNow;
            if (year < dateTime.Year)
                dateTime = new(year, 12, 31, 23, 59, 59, TimeSpan.Zero);
            long userSize = 0;
            RedisValue redisValue = redisClinet.HashGet($"Blob:Record", scId);
            if (redisValue != default && !redisValue.IsNullOrEmpty)
            {
                JsonElement record = redisValue.ToString().ToObject<JsonElement>();
                if (record.TryGetInt64(out userSize)) { }
            }

            StatsInfo statsInfo = new() { id = $"{year}-{scId}" };
            var (dayS, dayE) = TimeHelper.GetStartOrEnd(dateTime);  //今天开始时间    13位
            var (lastWeekS, lastWeekE) = TimeHelper.GetStartOrEnd(dateTime, "lastweek");   //计算上周开始/结束时间
            var (weekS, weekE) = TimeHelper.GetStartOrEnd(dateTime, "week");             //计算本周开始/结束时间

            var (lastTermS, lastTermE) = TimeHelper.GetStartOrEnd(dateTime, "lastterm");   //计算上学期开始/结束时间
            var (termS, termE) = TimeHelper.GetStartOrEnd(dateTime, "term");   //计算本学期开始/结束时间

            var (lastMthS, LastmthE) = TimeHelper.GetStartOrEnd(dateTime, "lastMonth");  //上月开始/结束时间
            var (mthS, mthE) = TimeHelper.GetStartOrEnd(dateTime, "month");  //本月开始/结束时间

            var (lastYearS, lastYearE) = TimeHelper.GetStartOrEnd(DateTimeOffset.Parse($"{dateTime.Year - 1}-1-1"), "year"); //计算去年开始/结束时间
            var (yearS, yearE) = TimeHelper.GetStartOrEnd(dateTime, "year");         //计算今年开始/结束时间

            var (weekDayS, weekDayE) = TimeHelper.GetLongToTime(weekS, weekE);
            var (monthDayS, monthDayE) = TimeHelper.GetLongToTime(mthS, mthE);

            string currSql = "select value(count(c.id)) from c";
            string dayWhereSql = $"c.createTime >= {dayS} and c.createTime <= {dayE}";
            string weekWhereSql = $"c.createTime >= {weekS} and c.createTime <= {weekE}";
            string monthWhereSql = $"c.createTime >= {mthS} and c.createTime <= {mthE}";

            ScBase scBase = await cosmosClient.GetContainer("TEAMModelOS", "School").ReadItemAsync<ScBase>(scId, new PartitionKey("Base"));
            statsInfo.schoolId = scBase.id;
            statsInfo.name = scBase.name;
            statsInfo.picture = scBase.picture;
            statsInfo.size = scBase.size;
            statsInfo.scCreateTime = scBase.createTime;
            statsInfo.areaId = scBase.areaId;
            statsInfo.year = year;
            statsInfo.useSize = userSize;

            string tchSql = $"{currSql} where ARRAY_CONTAINS(c.roles, 'teacher', true) AND c.status = 'join'";
            statsInfo.tch = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"Teacher-{scBase.id}", tchSql);

            List<double> tchDoubles = await UserStatsWay.GetYearTecherStudent(cosmosClient, scBase.id, year, 1);
            statsInfo.tchYear = tchDoubles;
            statsInfo.dayTch = ((int)tchDoubles[dateTime.DayOfYear - 1]);
            DenseMatrix tchDouble = DenseMatrix.OfColumns(new List<List<double>>() { tchDoubles });
            int weekDay = 0;
            if ((dateTime.DayOfYear - weekDayS.DayOfYear) <= 7)
                weekDay = dateTime.DayOfYear - weekDayS.DayOfYear + 1;
            else
                weekDay = 7;

            statsInfo.weekTch = ((int)tchDouble.SubMatrix(weekDayS.DayOfYear - 1, weekDay, 0, tchDouble.ColumnCount).ColumnSums().Sum());
            statsInfo.monthTch += ((int)tchDouble.SubMatrix(monthDayS.DayOfYear - 1, (dateTime.DayOfYear - monthDayS.DayOfYear + 1), 0, tchDouble.ColumnCount).ColumnSums().Sum());

            //statsInfo.dayTch = await JointlySingleQuery.GetValueInt(cosmosClient, "School",  $"Teacher-{scBase.id}", $"{tchSql} and {dayWhereSql} ");
            //statsInfo.weekTch = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"Teacher-{scBase.id}", $"{tchSql} and  {weekWhereSql}");
            //statsInfo.monthTch = await JointlySingleQuery.GetValueInt(cosmosClient, "School",  $"Teacher-{scBase.id}", $"{tchSql} and  {monthWhereSql}");
            statsInfo.tchUpTime = dateTime.ToUnixTimeMilliseconds();

            statsInfo.stu = await JointlySingleQuery.GetValueInt(cosmosClient, "Student", $"Base-{scBase.id}", currSql);
            List<double> stuDoubles = await UserStatsWay.GetYearTecherStudent(cosmosClient, scBase.id, year, 2);
            statsInfo.stuYear = stuDoubles;
            statsInfo.dayStu = ((int)stuDoubles[dateTime.DayOfYear]);
            DenseMatrix stuDouble = DenseMatrix.OfColumns(new List<List<double>>() { stuDoubles });
            statsInfo.weekStu = ((int)stuDouble.SubMatrix(weekDayS.DayOfYear - 1, weekDay, 0, stuDouble.ColumnCount).ColumnSums().Sum());
            statsInfo.monthStu += ((int)stuDouble.SubMatrix(monthDayS.DayOfYear - 1, (dateTime.DayOfYear - monthDayS.DayOfYear + 1), 0, stuDouble.ColumnCount).ColumnSums().Sum());

            //statsInfo.dayStu = await JointlySingleQuery.GetValueInt(cosmosClient, "Student", $"Base-{scBase.id}", $"{currSql} where {dayWhereSql}");
            //statsInfo.weekStu = await JointlySingleQuery.GetValueInt(cosmosClient, "Student", $"Base-{scBase.id}", $"{currSql} where {weekWhereSql}");
            //statsInfo.monthStu = await JointlySingleQuery.GetValueInt(cosmosClient, "Student", $"Base-{scBase.id}", $"{currSql} where {monthWhereSql}");
            statsInfo.stuUpTime = dateTime.ToUnixTimeMilliseconds();

            statsInfo.room = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"Room-{scBase.id}", currSql);
            //statsInfo.witRoom = await JointlySingleQuery.GetValueInt(cosmosClient, "School",  $"Room-{scBase.id}", $"{currSql} where (c.serial != null or c.serial != '' or IS_DEFINED(c.serial) = true)");

            statsInfo.resourceCnt = await JointlySingleQuery.GetValueInt(cosmosClient, "School", $"Bloblog-{scBase.id}", currSql);

            statsInfo.upTime = dateTime.ToUnixTimeMilliseconds();
            statsInfo.lesson = await LessonRecordStatsWay.GetSchoolAll(cosmosClient, statsInfo.schoolId, year);
            statsInfo.activity = await ActivityStatsWay.GetSchoolAll(cosmosClient, statsInfo.schoolId, year);
            statsInfo.study = await StudyStatsWay.GetSchoolAll(cosmosClient, statsInfo.schoolId);

            return statsInfo;
        }


        /// <summary>
        /// 多个学校统计
        /// </summary>
        /// <param name="cosmosClient"></param>
        /// <param name="statsInfos"></param>
        /// <param name="_httpTrigger"></param>
        /// <param name="_option"></param>
        /// <param name="area"></param>
        /// <returns></returns>
        public static StatsInfo GetAreaStats(CosmosClient cosmosClient, Option _option, List<StatsInfo> statsInfos, List<string> scIds = null, Area area = null)
        {
            StatsInfo areaInfo = new()
            {
                id = area == null ? "" : $"{DateTimeOffset.UtcNow.Year}-{area.id}",
                pk = "Statistics",
                code = "Statistics",
                name = area == null ? "" : area.name,
                areaId = area == null ? "" : area.id,
                picture = "",
                tch = statsInfos.Select(s => s.tch).Sum(),
                dayTch = statsInfos.Select(s => s.dayTch).Sum(),
                weekTch = statsInfos.Select(s => s.weekTch).Sum(),
                monthTch = statsInfos.Select(s => s.monthTch).Sum(),
                stu = statsInfos.Select(s => s.stu).Sum(),
                dayStu = statsInfos.Select(s => s.dayStu).Sum(),
                weekStu = statsInfos.Select(s => s.weekStu).Sum(),
                monthStu = statsInfos.Select(s => s.monthStu).Sum(),
                room = statsInfos.Select(s => s.room).Sum(),
                witRoom = statsInfos.Select(s => s.witRoom).Sum(),
                resourceCnt = statsInfos.Select(s => s.resourceCnt).Sum(),
                size = statsInfos.Select(s => s.size).Sum(),
                useSize = statsInfos.Select(s => s.useSize).Sum()
            };
            areaInfo.lesson.all = statsInfos.Select(s => s.lesson.all).Sum();
            areaInfo.lesson.open = statsInfos.Select(s => s.lesson.open).Sum();
            areaInfo.lesson.less = statsInfos.Select(s => s.lesson.less).Sum();
            areaInfo.lesson.lastDay = statsInfos.Select(s => s.lesson.lastDay).Sum();
            areaInfo.lesson.day = statsInfos.Select(s => s.lesson.day).Sum();
            areaInfo.lesson.lastWeek = statsInfos.Select(s => s.lesson.lastWeek).Sum();
            areaInfo.lesson.week = statsInfos.Select(s => s.lesson.week).Sum();
            areaInfo.lesson.lastTerm = statsInfos.Select(s => s.lesson.lastTerm).Sum();
            areaInfo.lesson.term = statsInfos.Select(s => s.lesson.term).Sum();
            areaInfo.lesson.lastDayInter = statsInfos.Select(s => s.lesson.lastDayInter).Sum();
            areaInfo.lesson.dayInter = statsInfos.Select(s => s.lesson.dayInter).Sum();
            //areaInfo.lesson.lastMonthInter = statsInfos.Select(s => s.lesson.lastMonthInter).Sum();
            //areaInfo.lesson.monthInter = statsInfos.Select(s => s.lesson.monthInter).Sum();
            areaInfo.lesson.lastYearInter = statsInfos.Select(s => s.lesson.lastYearInter).Sum();
            //areaInfo.lesson.yearInter = statsInfos.Select(s => s.lesson.yearInter).Sum();
            areaInfo.lesson.yearInters = BICommonWay.ManyDoubleMerge(statsInfos.Select(s => s.lesson.yearInters).ToList());
            areaInfo.lesson.year = BICommonWay.ManyDoubleMerge(statsInfos.Select(s => s.lesson.year).Where(w => w.Count > 0).ToList());


            areaInfo.activity.all = statsInfos.Select(s => s.activity.all).Sum();
            areaInfo.activity.exam = statsInfos.Select(s => s.activity.exam).Sum();
            areaInfo.activity.survey = statsInfos.Select(s => s.activity.survey).Sum();
            areaInfo.activity.vote = statsInfos.Select(s => s.activity.vote).Sum();
            areaInfo.activity.homework = statsInfos.Select(s => s.activity.homework).Sum();
            areaInfo.activity.lastDay = statsInfos.Select(s => s.activity.lastDay).Sum();
            areaInfo.activity.dayCnt = statsInfos.Select(s => s.activity.dayCnt).Sum();
            areaInfo.activity.lastWeek = statsInfos.Select(s => s.activity.lastWeek).Sum();
            areaInfo.activity.week = statsInfos.Select(s => s.activity.week).Sum();
            areaInfo.activity.lastTerm = statsInfos.Select(s => s.activity.lastTerm).Sum();
            areaInfo.activity.term = statsInfos.Select(s => s.activity.term).Sum();
            areaInfo.activity.lastMonth = statsInfos.Select(s => s.activity.lastMonth).Sum();
            areaInfo.activity.month = statsInfos.Select(s => s.activity.month).Sum();
            //areaInfo.activity.LastYear = BICommonWay.ManyDoubleMerge(statsInfos.Select(s => s.activity.LastYear).ToList());
            areaInfo.activity.year = BICommonWay.ManyDoubleMerge(statsInfos.Select(s => s.activity.year).Where(w => w.Count > 0).ToList());

            return areaInfo;

        }

        /// <summary>
        /// 计算学校智慧教室数
        /// </summary>
        /// <param name="_azureCosmos"></param>
        /// <param name="_azureRedis"></param>
        /// <param name="_configuration"></param>
        /// <param name="_httpClient"></param>
        /// <param name="code"></param>
        /// <returns></returns>
        public static async Task<double> GetShoolWisdomRoomCount(AzureCosmosFactory _azureCosmos, AzureRedisFactory _azureRedis, IConfiguration _configuration, IHttpClientFactory _httpClient, DingDing _dingDing, string code)
        {
            try
            {
                var db = _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "School");
                var r8 = _azureRedis.GetRedisClient(8);

                List<SchoolProductSerial> serial = new(); //承接DB資料用：序號
                List<deviceForCoreService> uuidList = new(); //要向CoreService詢問deviceID及硬體資訊的UUID列表
                long UTCNow = DateTimeOffset.UtcNow.ToUnixTimeSeconds();

                //軟體
                List<string> serialPermitList = StaticValue.GetSerialPermitProdcodeList();
                string serialPermitJsonStr = JsonConvert.SerializeObject(serialPermitList);
                string serialQueryText = $"SELECT * FROM c WHERE c.dataType = 'serial' AND ARRAY_CONTAINS({serialPermitJsonStr}, c.prodCode)";
                await foreach (var itemsr in db.GetItemQueryStreamIterator(queryText: serialQueryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Product-{code}") }))
                {
                    using var json = await JsonDocument.ParseAsync(itemsr.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        deviceForCoreService uuidForCore;
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            serial.Add(obj.ToObject<SchoolProductSerial>());
                            if (obj.TryGetProperty("deviceBound", out JsonElement deviceBoundJobj) && !string.IsNullOrWhiteSpace(deviceBoundJobj.ToString()))
                            {
                                foreach (var deviceBoundTmpRow in deviceBoundJobj.EnumerateArray())
                                {
                                    uuidForCore = new deviceForCoreService
                                    {
                                        sn = (!string.IsNullOrWhiteSpace(Convert.ToString(obj.GetProperty("serial")))) ? Convert.ToString(obj.GetProperty("serial")) : null,
                                        uuid1 = (!string.IsNullOrWhiteSpace(Convert.ToString(deviceBoundTmpRow.GetProperty("uuid")))) ? Convert.ToString(deviceBoundTmpRow.GetProperty("uuid")) : null,
                                        uuid2 = (!string.IsNullOrWhiteSpace(Convert.ToString(deviceBoundTmpRow.GetProperty("uuid2")))) ? Convert.ToString(deviceBoundTmpRow.GetProperty("uuid2")) : null,
                                        device_id = (!string.IsNullOrWhiteSpace(Convert.ToString(deviceBoundTmpRow.GetProperty("deviceId")))) ? Convert.ToString(deviceBoundTmpRow.GetProperty("deviceId")) : null,
                                        class_id = (!string.IsNullOrWhiteSpace(Convert.ToString(deviceBoundTmpRow.GetProperty("classId")))) ? Convert.ToString(deviceBoundTmpRow.GetProperty("classId")) : null
                                    };
                                    uuidList.Add(uuidForCore);
                                }
                            }
                        }
                    }
                }
                ////取得DeviceInfo From Core 並更新序號資料
                List<deviceFromCoreService> coreUuidList = (List<deviceFromCoreService>)GetDeviceFromCoreAsync(uuidList, _configuration, _httpClient).GetAwaiter().GetResult();
                List<deviceBoundExt> deviceBoundArray;
                List<deviceBoundExt> counts = new();
                List<deviceFromCoreService> coreUuid;
                deviceForCoreService deviceBoundRow;
                deviceBoundExt deviceBoundExt;
                List<SchoolProductSerial> updSchoolProductSerialList = new List<SchoolProductSerial>(); //更新學校產品序號用

                foreach (SchoolProductSerial serialRow in serial)
                {
                    deviceBoundArray = new List<deviceBoundExt>();
                    coreUuid = coreUuidList
                           .Where((deviceFromCoreService x) => x.sn == serialRow.serial)
                           .ToList();
                    foreach (deviceFromCoreService deviceRow in coreUuid)
                    {
                        if (!string.IsNullOrWhiteSpace(deviceRow.uuid1) || !string.IsNullOrWhiteSpace(deviceRow.uuid2) || !string.IsNullOrWhiteSpace(deviceRow.device_id)) //uuid1、uuid2、device_id 任一欄有值
                        {
                            //前端顯示用
                            deviceBoundRow = uuidList.Where(u => u.sn == deviceRow.sn && u.uuid1 == deviceRow.uuid1 && u.uuid2 == deviceRow.uuid2).FirstOrDefault();
                            deviceBoundExt = new deviceBoundExt
                            {
                                uuid = deviceBoundRow.uuid1,
                                uuid2 = deviceBoundRow.uuid2,
                                classId = deviceBoundRow.class_id,
                                deviceId = deviceRow.device_id,
                                os = deviceRow.os,
                                ip = deviceRow.local_ip,
                                cpu = deviceRow.cpu,
                                pcname = deviceRow.pc_name,
                                osver = deviceRow.os_ver
                            };
                            deviceBoundArray.Add(deviceBoundExt);
                        }
                    }
                    if (deviceBoundArray.Count.Equals(0)) //無法取得CS的硬體資訊，則用序號的硬體資訊帶入
                    {
                        if (serialRow.deviceBound != null)
                        {
                            foreach (deviceBound serialRowDeviceBound in serialRow.deviceBound)
                            {
                                if (!string.IsNullOrEmpty(serialRowDeviceBound.uuid) || !string.IsNullOrEmpty(serialRowDeviceBound.uuid2))
                                {
                                    deviceBoundExt = new deviceBoundExt
                                    {
                                        uuid = serialRowDeviceBound.uuid,
                                        uuid2 = serialRowDeviceBound.uuid2,
                                        classId = serialRowDeviceBound.classId,
                                        deviceId = serialRowDeviceBound.deviceId
                                    };
                                    deviceBoundArray.Add(deviceBoundExt);
                                }
                            }
                        }
                    }
                    //序號更新
                    //updSchoolProductSerialList.Add(serialRow);
                    counts.AddRange(deviceBoundArray);
                    //await db.ReplaceItemAsync<SchoolProductSerial>(serialRow, serialRow.id, new PartitionKey($"Product-{school_code}"));
                }
                //取得CC授權使用狀態
                var hashs = await r8.HashGetAllAsync($"CC:License:{code}");
                var ccuser = hashs.Select(x => JsonDocument.Parse(x.Value.ToString())).ToList();

                //更新學校產品序號
                //_ = UpdupdSchoolProductSerialListAsync(updSchoolProductSerialList, school_code.GetString());
                double roomCount = counts.Count + ccuser.Count;
                return roomCount;
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI, {Environment.GetEnvironmentVariable("Option:Location")},GetShoolWisdomRoomCount() {code} \n {ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
                return 0;
            }
        }


        private static async Task<List<deviceFromCoreService>> GetDeviceFromCoreAsync(List<deviceForCoreService> uuidList, IConfiguration _configuration, IHttpClientFactory _httpClient)
        {
            List<deviceFromCoreService> result = new List<deviceFromCoreService>();
            try
            {
                string url = _configuration.GetValue<string>("HaBookAuth:CoreService:deviceinfo");
                string AccessToken = await getCoreAccessToken(_configuration, _httpClient);
                var client = _httpClient.CreateClient();
                client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", AccessToken);
                string uuidListJson = JsonConvert.SerializeObject(uuidList);
                var content = new StringContent(uuidListJson, Encoding.UTF8, "application/json");
                HttpResponseMessage responseMessage = await client.PostAsync(url, content);
                if (responseMessage.StatusCode == HttpStatusCode.OK)
                {
                    string responseBody = responseMessage.Content.ReadAsStringAsync().Result;
                    result = System.Text.Json.JsonSerializer.Deserialize<List<deviceFromCoreService>>(responseBody.ToString());
                }
                return result;
            }
            catch (Exception ex)
            {
                return result;
            }
        }
        private static async Task<string> getCoreAccessToken(IConfiguration _configuration, IHttpClientFactory _httpClient)
        {
            string AccessToken = "";
            try
            {
                string Url = _configuration.GetValue<string>("HaBookAuth:CoreAPI") + "/oauth2/token";
                string GrantType = "device";
                string ClientID = _configuration.GetValue<string>("HaBookAuth:CoreService:clientID");
                string Secret = _configuration.GetValue<string>("HaBookAuth:CoreService:clientSecret");
                var content = new { grant_type = GrantType, client_id = ClientID, client_secret = Secret };
                var response = await _httpClient.CreateClient().PostAsJsonAsync($"{Url}", content);
                if (response.IsSuccessStatusCode)
                {
                    string responseBody = response.Content.ReadAsStringAsync().Result;
                    using (JsonDocument document = JsonDocument.Parse(responseBody.ToString()))
                    {
                        if (document.RootElement.TryGetProperty("access_token", out JsonElement AccessTokenObj))
                        {
                            AccessToken = AccessTokenObj.ToString();
                        }
                    }
                }
                return AccessToken;
            }
            catch (Exception ex)
            {
                return AccessToken;
            }
        }
    }
}