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 StackExchange.Redis;
using System;
using System.Collections.Generic;
using System.Linq;
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;
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]);
DenseMatrix tchDouble = DenseMatrix.OfColumns(new List>() { tchDoubles });
int weekDay = 0;
if ((dateTime.DayOfYear - weekDayS.DayOfYear) <= 7)
weekDay = dateTime.DayOfYear - weekDayS.DayOfYear;
else
weekDay = 7;
statsInfo.weekTch = ((int)tchDouble.SubMatrix(weekDayS.DayOfYear, weekDay, 0, tchDouble.ColumnCount).ColumnSums().Sum());
statsInfo.monthTch += ((int)tchDouble.SubMatrix(monthDayS.DayOfYear, (dateTime.DayOfYear - monthDayS.DayOfYear), 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, weekDay, 0, stuDouble.ColumnCount).ColumnSums().Sum());
statsInfo.monthStu += ((int)stuDouble.SubMatrix(monthDayS.DayOfYear, (dateTime.DayOfYear - monthDayS.DayOfYear), 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, HttpTrigger _httpTrigger, 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());
//List>> taskStatsInfos = new();
//foreach (var itemId in scIds)
//{
// bool isExist = true;
// StatsInfo statsInfo = new();
// var scDataStats = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync($"{itemId}", new PartitionKey("Stats"));
// if (scDataStats.Status == 200)
// {
// using var fileJson = await JsonDocument.ParseAsync(scDataStats.ContentStream);
// statsInfo = fileJson.ToObject();
// }
// else
// {
// statsInfo.id = itemId;
// isExist = false;
// }
// bool isnew = false;
// DateTimeOffset dateTime = DateTimeOffset.UtcNow;
// long now = dateTime.ToUnixTimeMilliseconds();
// if (statsInfo.upTime > 0 && !isnew)
// {
// long timedis = 20 * 60 * 1000;//20分钟
// if (now - statsInfo.upTime < timedis)
// isnew = true;
// }
// if (statsInfo.upTime == 0 && isnew)
// statsInfo.upTime = now;
// if (statsInfo.upTime == 0 && isnew)
// statsInfo.upTime = DateTimeOffset.UtcNow.Add(new TimeSpan(-1, 0, 0, 0)).ToUnixTimeMilliseconds();
// if (!isnew)
// {
// ////Function 处理Function 更新或者新增
// //_ = _httpTrigger.RequestHttpTrigger(new { schoolId = $"{itemId}"}, _option.Location, "stats-sc-info");
// //异步方法处理 同步更新
// statsInfo = await SchoolStatsWay.GetSingleSc(cosmosClient, itemId);
// statsInfo.upTime = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
// if (isExist)
// taskStatsInfos.Add(cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReplaceItemAsync(statsInfo, statsInfo.id, new PartitionKey("Stats")));
// else
// taskStatsInfos.Add(cosmosClient.GetContainer(Constant.TEAMModelOS, "School").CreateItemAsync(statsInfo, new PartitionKey("Stats")));
// }
//}
//if (taskStatsInfos.Count < 256)
// await Task.WhenAll(taskStatsInfos);
//else
//{
// int pages = (taskStatsInfos.Count + 256) / 256;
// for (int i = 0; i < pages; i++)
// {
// List>> tempStatsInfos = taskStatsInfos.Skip((i) * 256).Take(256).ToList();
// await Task.WhenAll(tempStatsInfos);
// }
//}
return areaInfo;
}
public static async Task> GetLastYearType(CosmosClient cosmosClient, List scIds)
{
List list = new();
StringBuilder statsSql = new("select value(c) from c");
if (scIds.Count > 0)
{
statsSql.Append($" where {BICommonWay.ManyScSql("c.id", scIds, $"{DateTimeOffset.UtcNow.Year - 1}-")}");
}
await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIterator(queryText: statsSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Statistics") }))
{
list.Add(item);
}
return list;
}
}
}