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