using Microsoft.Azure.Cosmos; using MathNet.Numerics.LinearAlgebra.Double; 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.Net.Http.Json; namespace TEAMModelOS.SDK.Models.Service.BIStatsWay { public static class SchoolStatsWay { /// /// 统计单个学校 /// /// /// /// public static async Task 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(); 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(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 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>() { 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 stuDoubles = await UserStatsWay.GetYearTecherStudent(cosmosClient, scBase.id, year, 2); statsInfo.stuYear = stuDoubles; statsInfo.dayStu = ((int)stuDoubles[dateTime.DayOfYear]); DenseMatrix stuDouble = DenseMatrix.OfColumns(new List>() { 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; } /// /// 多个学校统计 /// /// /// /// /// /// /// public static StatsInfo GetAreaStats(CosmosClient cosmosClient, Option _option, List statsInfos, List 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; } /// /// 计算学校智慧教室数 /// /// /// /// /// /// /// public static async Task 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 serial = new(); //承接DB資料用:序號 List uuidList = new(); //要向CoreService詢問deviceID及硬體資訊的UUID列表 long UTCNow = DateTimeOffset.UtcNow.ToUnixTimeSeconds(); //軟體 List 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.GetItemQueryStreamIteratorSql(queryText: serialQueryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Product-{code}") })) { using var json = await JsonDocument.ParseAsync(itemsr.Content); 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()); 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 coreUuidList = (List)GetDeviceFromCoreAsync(uuidList, _configuration, _httpClient).GetAwaiter().GetResult(); List deviceBoundArray; List counts = new(); List coreUuid; deviceForCoreService deviceBoundRow; deviceBoundExt deviceBoundExt; List updSchoolProductSerialList = new List(); //更新學校產品序號用 foreach (SchoolProductSerial serialRow in serial) { deviceBoundArray = new List(); 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(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> GetDeviceFromCoreAsync(List uuidList, IConfiguration _configuration, IHttpClientFactory _httpClient) { List result = new List(); try { string url = _configuration.GetValue("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>(responseBody.ToString()); } return result; } catch (Exception ex) { return result; } } private static async Task getCoreAccessToken(IConfiguration _configuration, IHttpClientFactory _httpClient) { string AccessToken = ""; try { string Url = _configuration.GetValue("HaBookAuth:CoreAPI") + "/oauth2/token"; string GrantType = "device"; string ClientID = _configuration.GetValue("HaBookAuth:CoreService:clientID"); string Secret = _configuration.GetValue("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; } } } }