TEAMModelOS/TEAMModelBI/Controllers/BITmid/TmidController.cs

using Azure.Cosmos;
using DocumentFormat.OpenXml.Office2010.Excel;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.Cosmos.Table;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Options;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text.Json;
using System.Threading.Tasks;
using TEAMModelOS.Models;
using TEAMModelOS.SDK;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Extension;
using TEAMModelOS.SDK.Models;
using TEAMModelOS.SDK.Services;
using static TEAMModelBI.Models.Extension.GeoRegion;

namespace TEAMModelBI.Controllers.BITmid
{
    [Route("tmid")]
    [ApiController]
    public class TmidController : ControllerBase
    {
        private readonly AzureCosmosFactory _azureCosmos;
        private readonly AzureStorageFactory _azureStorage;
        private readonly AzureRedisFactory _azureRedis;
        private readonly DingDing _dingDing;
        private readonly Option _option;
        private readonly IConfiguration _configuration;
        private readonly HttpTrigger _httpTrigger;

        public TmidController(AzureCosmosFactory azureCosmos, AzureStorageFactory azureStorage, AzureRedisFactory azureRedis, DingDing dingDing, IOptionsSnapshot<Option> option, IConfiguration configuration, HttpTrigger httpTrigger)
        {
            _azureCosmos = azureCosmos;
            _azureStorage = azureStorage;
            _azureRedis = azureRedis;
            _dingDing = dingDing;
            _option = option?.Value;
            _configuration = configuration;
            _httpTrigger = httpTrigger;
        }

        /// <summary>
        /// 取得TMID綜合資料
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        //[AuthToken(Roles = "admin")]
        [HttpPost("get-tmidstics")]
        public async Task<IActionResult> GetTmidStics(JsonElement jsonElement)
        {
            try
            {
                if (!jsonElement.TryGetProperty("tmids", out JsonElement tmidsJobj)) return BadRequest();//TMID(array)
                var tmids = tmidsJobj.Deserialize<List<string>>();
                if (tmids.Count is 0) return BadRequest();
                var datetime = DateTimeOffset.UtcNow.AddDays(-1);
                int y = datetime.Year;
                int m = datetime.Month;
                int d = datetime.Day;
                string dateFromStr = (jsonElement.TryGetProperty("dateFrom", out JsonElement dateFromJobj)) ? dateFromJobj.GetString() : string.Empty; //查詢日期:起始(string)[例]2023-03-05
                string dateToStr = (jsonElement.TryGetProperty("dateTo", out JsonElement dateToJobj)) ? dateToJobj.GetString() : string.Empty; //查詢日期:結束(string)[例]2023-03-27
                string dateUnit = (jsonElement.TryGetProperty("dateUnit", out JsonElement dateUnitJobj)) ? dateUnitJobj.GetString().ToLower() : "month";
                string mode = (jsonElement.TryGetProperty("mode", out JsonElement modeJobj)) ? modeJobj.GetString().ToLower() : "default";
                
                //起始終止日期換算
                long dateTimeFromSec = 0;
                long dateTimeToSec = 0;
                if(!string.IsNullOrWhiteSpace(dateFromStr) && !string.IsNullOrWhiteSpace(dateToStr))
                {
                    DateTimeOffset dateTimeFrom;
                    DateTimeOffset dateTimeTo;
                    List<string> dateFromList = dateFromStr.Split('-').ToList();
                    int dateFromYear = Convert.ToInt32(dateFromList[0], 10);
                    int dateFromMonth = (dateUnit.Equals("day") || dateUnit.Equals("month")) ? Convert.ToInt32(dateFromList[1], 10) : 1;
                    int dateFromDay = (dateUnit.Equals("day")) ? Convert.ToInt32(dateFromList[2], 10) : 1;
                    List<string> dateToList = dateToStr.Split('-').ToList();
                    int dateToYear = Convert.ToInt32(dateToList[0], 10);
                    int dateToMonth = (dateUnit.Equals("day") || dateUnit.Equals("month")) ? Convert.ToInt32(dateToList[1], 10) : 1;
                    int dateToDay = (dateUnit.Equals("day")) ? Convert.ToInt32(dateToList[2], 10) : 1;
                    switch (dateUnit)
                    {
                        case "year":
                            dateTimeFrom = new DateTimeOffset(dateFromYear, 1, 1, 0, 0, 0, TimeSpan.Zero);
                            dateTimeTo = new DateTimeOffset(dateToYear, 12, 31, 23, 59, 59, TimeSpan.Zero);
                            break;
                        case "month":
                            dateTimeFrom = new DateTimeOffset(dateFromYear, dateFromMonth, 1, 0, 0, 0, TimeSpan.Zero);
                            dateTimeTo = new DateTimeOffset(dateToYear, dateToMonth, DateTime.DaysInMonth(dateToYear, dateToMonth), 23, 59, 59, TimeSpan.Zero);
                            break;
                        case "day":
                            dateTimeFrom = new DateTimeOffset(dateFromYear, dateFromMonth, dateFromDay, 0, 0, 0, TimeSpan.Zero);
                            dateTimeTo = new DateTimeOffset(dateToYear, dateToMonth, dateToDay, 23, 59, 59, TimeSpan.Zero);
                            break;
                        default:
                            dateTimeFrom = new DateTimeOffset(y, m, d, 0, 0, 0, TimeSpan.Zero);
                            dateTimeTo = new DateTimeOffset(y, m, d, 23, 59, 59, TimeSpan.Zero);
                            break;
                    }
                    dateTimeFromSec = dateTimeFrom.ToUnixTimeSeconds();
                    dateTimeToSec = dateTimeTo.ToUnixTimeSeconds();
                    y = 0;
                    m = 0;
                    d = 0;
                }
                //取得項目列表
                List<string> eventList = new List<string>();
                if(mode.Equals("simple"))
                {
                    eventList = new List<string>() { "iot" };
                } else
                {
                    eventList = new List<string>() { "coupons", "login", "prod", "points", "iot", "ies5", "benefits", "sokrates" };
                }

                //服務Client端
                var cosmosClientIes5 = _azureCosmos.GetCosmosClient(); //CosmosDB IES5
                var cosmosClientCsv2 = _azureCosmos.GetCosmosClient(name: "CoreServiceV2"); //CosmosDB CSV2
                var storageClientCsv2 = _azureStorage.GetCloudTableClient(name: "CoreServiceV2"); //Storage CSV2
                var tableCouponClient = storageClientCsv2.GetTableReference("Coupon");
                var tablePointsClient = storageClientCsv2.GetTableReference("Points");
                var redisClient = _azureRedis.GetRedisClient(4);

                //取得TMID基本資料
                Dictionary<string, TmidStics> tmidDic = new();
                QueryDefinition query =
                       new QueryDefinition(@"SELECT c.id, c.name, c.picture, c.mobile, c.mail, c.lang, c.wechat, c.facebook, c.google, c.ding, c.apple, c.educloudtw, c.ts FROM c WHERE (ARRAY_CONTAINS(@key, c.id) OR ARRAY_CONTAINS(@key, c.mobile))")
                       .WithParameter("@key", tmids);
                await foreach (var item in cosmosClientCsv2
                            .GetContainer("Core", "ID2")
                            .GetItemQueryStreamIterator(query, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("base") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var doc in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            string id = doc.GetProperty("id").GetString();
                            if(!tmids.Contains(id)) tmids.Add(id);
                            //基本資料
                            TmidStics tmidStics = (tmidDic.ContainsKey(id)) ? tmidDic[id] : new() { id = id };
                            if (eventList.Contains("ies5")) tmidStics.ies5 = new();
                            if (eventList.Contains("points")) tmidStics.points = new();
                            if (eventList.Contains("sokrates")) tmidStics.sokrates = new();
                            if (eventList.Contains("benefits")) tmidStics.benefits = new();
                            if (eventList.Contains("iot")) tmidStics.iot = new();
                            tmidStics.name = doc.GetProperty("name").GetString();
                            tmidStics.picture = doc.GetProperty("picture").GetString();
                            tmidStics.mobile = GenDataMask(doc.GetProperty("mobile").GetString(), "mobile");
                            tmidStics.mail = GenDataMask(doc.GetProperty("mail").GetString(), "mail");
                            tmidStics.lang = (doc.TryGetProperty("lang", out JsonElement lang)) ? lang.GetString() : string.Empty;
                            tmidStics.wechat = (doc.TryGetProperty("wechat", out JsonElement wechat) && !string.IsNullOrWhiteSpace(wechat.GetString())) ? true : false;
                            tmidStics.facebook = (doc.TryGetProperty("facebook", out JsonElement facebook) && !string.IsNullOrWhiteSpace(facebook.GetString())) ? true : false;
                            tmidStics.google = (doc.TryGetProperty("google", out JsonElement google) && !string.IsNullOrWhiteSpace(google.GetString())) ? true : false;
                            tmidStics.ding = (doc.TryGetProperty("ding", out JsonElement ding) && !string.IsNullOrWhiteSpace(ding.GetString())) ? true : false;
                            tmidStics.apple = (doc.TryGetProperty("apple", out JsonElement apple) && !string.IsNullOrWhiteSpace(apple.GetString())) ? true : false;
                            tmidStics.ts = (doc.TryGetProperty("ts", out JsonElement ts)) ? ts.GetInt64() : 0;
                            tmidDic.Add(id, tmidStics);
                        }
                    }
                }
                //取得TMID進階資料
                regiondata regionData = GetRegionData(); //取得國省市區地理資訊架構
                query = new QueryDefinition(@"SELECT c.id, c.name, c.mobile, c.mail, c.country, c.province, c.city, c.schoolCode, c.schoolCodeW, c.unitType, c.unitName, c.jobTitle FROM c WHERE (ARRAY_CONTAINS(@key, c.id) OR ARRAY_CONTAINS(@key, c.mobile))")
                    .WithParameter("@key", tmids);
                await foreach (var item in cosmosClientCsv2
                            .GetContainer("Core", "ID2")
                            .GetItemQueryStreamIterator(query, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("base-ex") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var doc in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            string id = doc.GetProperty("id").GetString();
                            TmidStics tmidStics = (tmidDic.ContainsKey(id)) ? tmidDic[id] : new() { id = id };
                            if (eventList.Contains("ies5")) tmidStics.ies5 = new();
                            if (eventList.Contains("points")) tmidStics.points = new();
                            if (eventList.Contains("sokrates")) tmidStics.sokrates = new();
                            if (eventList.Contains("benefits")) tmidStics.benefits = new();
                            if (eventList.Contains("iot")) tmidStics.iot = new();
                            if (string.IsNullOrWhiteSpace(tmidStics.name)) tmidStics.name = doc.GetProperty("name").GetString();
                            if (string.IsNullOrWhiteSpace(tmidStics.mobile)) tmidStics.mobile = GenDataMask(doc.GetProperty("mobile").GetString(), "mobile");
                            if (string.IsNullOrWhiteSpace(tmidStics.mail)) tmidStics.mail = GenDataMask(doc.GetProperty("mail").GetString(), "mail");
                            string country = doc.GetProperty("country").GetString();
                            tmidStics.country = (regionData.country.ContainsKey(country)) ? regionData.country[country].name : country;
                            tmidStics.country = tmidStics.country.Replace("地區", "").Replace("地区", "");
                            string province = doc.GetProperty("province").GetString();
                            string city = doc.GetProperty("city").GetString();
                            string district = string.Empty;
                            if (country.Equals("TW"))
                            {
                                district = city;
                                city = province;
                                province = string.Empty;
                            }
                            tmidStics.province = (regionData.country.ContainsKey(country) && regionData.province.ContainsKey(country) && regionData.province[country].ContainsKey(province)) ? regionData.province[country][province].name : province;
                            if (country.Equals("TW"))
                                tmidStics.city = (regionData.city.ContainsKey(country) && regionData.city[country]["tw"].ContainsKey(city)) ? regionData.city[country]["tw"][city].name : city;
                            else if(country.Equals("CN"))
                                tmidStics.city = (regionData.city.ContainsKey(country) && regionData.city[country].ContainsKey(province) && regionData.city[country][province].ContainsKey(city)) ? regionData.city[country][province][city].name : city;
                            tmidStics.dist = (!string.IsNullOrWhiteSpace(district) && country.Equals("TW") && regionData.city[country]["tw"].ContainsKey(city) && regionData.dist[country]["tw"][city].ContainsKey(district)) ? regionData.dist[country]["tw"][city][district].name : district;
                            tmidStics.schoolCode = (doc.TryGetProperty("schoolCode", out JsonElement schCode)) ? schCode.GetString() : string.Empty;
                            tmidStics.schoolCodeW = (doc.TryGetProperty("schoolCodeW", out JsonElement schCodeW)) ? schCodeW.GetString() : string.Empty;
                            tmidStics.unitType = (doc.TryGetProperty("unitType", out JsonElement unitType)) ? unitType.GetString() : string.Empty;
                            tmidStics.unitName = (doc.TryGetProperty("unitName", out JsonElement unitName)) ? unitName.GetString() : string.Empty;
                            tmidStics.jobTitle = (doc.TryGetProperty("jobTitle", out JsonElement jobTitle)) ? jobTitle.GetString() : string.Empty;
                        }
                    }
                }
                if(tmidDic.Count.Equals(0)) return Ok(new List<object>());

                //取得 票券、登入各服務的時間、取得積分、個人服務授權、IOT
                foreach (KeyValuePair<string, TmidStics> dicItem in tmidDic)
                {
                    string id = dicItem.Key;
                    TmidStics tmidStics = dicItem.Value;
                    //票券
                    if (eventList.Contains("coupons"))
                    {
                        var usersCoupons = tableCouponClient.Get<DynamicTableEntity>(id);
                        foreach (var coupon in usersCoupons)
                        {
                            tmidStics.coupons.Add(
                                new TmidCoupon()
                                {
                                    code = coupon.RowKey,
                                    exchange = coupon.Properties["exchangeTime"].DateTimeOffsetValue.Value.ToUnixTimeSeconds(),
                                });
                        }
                    }
                    //取得登入各服務的時間
                    if (eventList.Contains("login"))
                    {
                        var loginTime = await redisClient.HashGetAllAsync(id);
                        foreach (var t in loginTime)
                        {
                            if (!t.Name.StartsWith("HiTeach") && !_dicClientIds.ContainsKey(t.Name)) continue;
                            tmidStics.login.Add(
                                new TmidLoginTime()
                                {
                                    product = t.Name.StartsWith("HiTeach") ? t.Name.ToString().Split("-")[0] : _dicClientIds[t.Name],
                                    time = (long)t.Value
                                });
                        }
                    }
                    //積分
                    if (eventList.Contains("points"))
                    {
                        var usersPoints = tablePointsClient.Get("Points", id);
                        tmidStics.points.points = (usersPoints != null) ? usersPoints.Properties["Points"].Int32Value.Value : 0;
                        tmidStics.points.level = (usersPoints != null) ? usersPoints.Properties["Level"].Int32Value.Value : 0;
                        tmidStics.points.balance = (usersPoints != null) ? usersPoints.Properties["Balance"].Int32Value.Value : 0;
                    }
                    //個人服務授權
                    if (eventList.Contains("prod"))
                    {
                        tmidStics.prod = await getTMIDAuthService(cosmosClientCsv2, id, "", true);
                    }
                    //IOT
                    if (eventList.Contains("iot"))
                    {
                        tmidStics.iot.hiteach.year = null;
                        tmidStics.iot.hiteach.month = null;
                        tmidStics.iot.hiteach.day = null;
                        if (!string.IsNullOrWhiteSpace(dateFromStr) && !string.IsNullOrWhiteSpace(dateToStr) && !string.IsNullOrWhiteSpace(dateUnit))
                        {
                            if (dateUnit.ToLower().Equals("year"))
                            {
                                tmidStics.iot.hiteach.year = await getTMIDIotData(cosmosClientIes5, id, "HiTeach", "year", y, 0, 0, dateTimeFromSec, dateTimeToSec);
                                tmidStics.iot.hiteachcc.year = await getTMIDIotData(cosmosClientIes5, id, "HiTeachCC", "year", y, 0, 0, dateTimeFromSec, dateTimeToSec);
                            }
                            else if (dateUnit.ToLower().Equals("month"))
                            {
                                tmidStics.iot.hiteach.month = await getTMIDIotData(cosmosClientIes5, id, "HiTeach", "month", y, m, 0, dateTimeFromSec, dateTimeToSec);
                                tmidStics.iot.hiteachcc.month = await getTMIDIotData(cosmosClientIes5, id, "HiTeachCC", "month", y, m, 0, dateTimeFromSec, dateTimeToSec);
                            }
                            else if (dateUnit.ToLower().Equals("day"))
                            {
                                tmidStics.iot.hiteach.day = await getTMIDIotData(cosmosClientIes5, id, "HiTeach", "day", y, m, d, dateTimeFromSec, dateTimeToSec);
                                tmidStics.iot.hiteachcc.day = await getTMIDIotData(cosmosClientIes5, id, "HiTeachCC", "day", y, m, d, dateTimeFromSec, dateTimeToSec);
                            }
                        }
                        else
                        {
                            tmidStics.iot.hiteach.year = await getTMIDIotData(cosmosClientIes5, id, "HiTeach", "year", y, 0, 0, dateTimeFromSec, dateTimeToSec);
                            tmidStics.iot.hiteach.month = await getTMIDIotData(cosmosClientIes5, id, "HiTeach", "month", y, m, 0, dateTimeFromSec, dateTimeToSec);
                            tmidStics.iot.hiteach.day = await getTMIDIotData(cosmosClientIes5, id, "HiTeach", "day", y, m, d, dateTimeFromSec, dateTimeToSec);
                            tmidStics.iot.hiteachcc.year = await getTMIDIotData(cosmosClientIes5, id, "HiTeachCC", "year", y, 0, 0, dateTimeFromSec, dateTimeToSec);
                            tmidStics.iot.hiteachcc.month = await getTMIDIotData(cosmosClientIes5, id, "HiTeachCC", "month", y, m, 0, dateTimeFromSec, dateTimeToSec);
                            tmidStics.iot.hiteachcc.day = await getTMIDIotData(cosmosClientIes5, id, "HiTeachCC", "day", y, m, d, dateTimeFromSec, dateTimeToSec);
                        }
                    }
                }
                //IES5
                if (eventList.Contains("ies5"))
                {
                    query = new QueryDefinition(@"SELECT c.id, c.defaultSchool, c.schools FROM c WHERE ARRAY_CONTAINS(@key, c.id)")
                          .WithParameter("@key", tmidDic.Keys.ToList());
                    await foreach (var item in cosmosClientIes5
                                .GetContainer(Constant.TEAMModelOS, "Teacher")
                                .GetItemQueryStreamIterator(query, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.ContentStream);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        {
                            foreach (var doc in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                string id = doc.GetProperty("id").GetString();
                                TmidStics tmidStics = tmidDic[id];
                                //IES5學校資訊
                                string defaultschool = (doc.TryGetProperty("defaultSchool", out JsonElement _defaultSchool)) ? _defaultSchool.ToString() : string.Empty; //預設學校
                                List<string> schoolIds = new List<string>();
                                if (doc.TryGetProperty("schools", out JsonElement schoolsJobj))
                                {
                                    foreach (var obj in schoolsJobj.EnumerateArray())
                                    {
                                        string schoolCodeNow = $"{obj.GetProperty("schoolId")}";
                                        string status = $"{obj.GetProperty("status")}";
                                        if (status.Equals("join"))
                                        {
                                            schoolIds.Add(schoolCodeNow); //放入學校ID列，一次取
                                        }
                                    }
                                }
                                if (schoolIds.Count > 0)
                                {
                                    QueryDefinition querysc = new QueryDefinition(@"SELECT c.id, c.name, c.region, c.province, c.city, c.dist, c.size, c.period FROM c WHERE ARRAY_CONTAINS(@key, c.id)")
                                        .WithParameter("@key", schoolIds);
                                    await foreach (var itemsc in cosmosClientIes5
                                        .GetContainer(Constant.TEAMModelOS, "School")
                                        .GetItemQueryStreamIterator(querysc, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
                                    {
                                        using var jsonsc = await JsonDocument.ParseAsync(itemsc.ContentStream);
                                        if (jsonsc.RootElement.TryGetProperty("_count", out JsonElement countsc) && countsc.GetUInt16() > 0)
                                        {
                                            foreach (var school in jsonsc.RootElement.GetProperty("Documents").EnumerateArray())
                                            {
                                                string schoolCodeNow = school.GetProperty("id").GetString();
                                                //當前學年 ※若有多學段，取第一個學段來取得學年資訊吧
                                                Period period = school.GetProperty("period").Deserialize<List<Period>>().First();
                                                var info = SchoolService.GetSemester(period, 0, DateTime.Now.ToString());
                                                int studyYear = info.studyYear;
                                                //學校Blob使用狀況
                                                (long usedSize, long teach, long total, long surplus, Dictionary<string, double?> catalog) schoolUsedBlob = await BlobService.GetSurplusSpace(schoolCodeNow, "school", _option.Location, _azureCosmos, _azureRedis, _azureStorage, _dingDing, _httpTrigger);
                                                //老師在此學校的課程數 ※只取當前學年
                                                int courseCnt = 0;
                                                var queryCrs = $"SELECT VALUE COUNT(1) FROM ( SELECT DISTINCT VALUE(c.id) FROM c JOIN schedules IN c.schedules WHERE schedules.teacherId = '{id}' AND c.year = {studyYear} )";
                                                await foreach (int itemCrs in cosmosClientIes5.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<int>(queryText: queryCrs, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"CourseTask-{schoolCodeNow}") }))
                                                {
                                                    courseCnt = itemCrs;
                                                }
                                                //IES5學校資料製作
                                                TmidSticsIes5School schoolobj = new TmidSticsIes5School();
                                                long ssize = (school.TryGetProperty("size", out JsonElement ssizeJson)) ? ssizeJson.GetInt32() : 0;
                                                long sused = schoolUsedBlob.usedSize + schoolUsedBlob.teach * 1073741824;
                                                long stotal = ssize * 1073741824;
                                                object schzize = new
                                                {
                                                    used = sused,
                                                    total = stotal,
                                                    avaliable = stotal - sused,
                                                };
                                                schoolobj.schoolId = schoolCodeNow;
                                                schoolobj.name = school.GetProperty("name").GetString();
                                                schoolobj.region = school.GetProperty("region").GetString();
                                                schoolobj.province = school.GetProperty("province").GetString();
                                                schoolobj.city = school.GetProperty("city").GetString();
                                                schoolobj.dist = school.GetProperty("dist").GetString();
                                                schoolobj.size = schzize;
                                                schoolobj.courseCnt = courseCnt;
                                                schoolobj.defaultSchool = (!string.IsNullOrWhiteSpace(defaultschool) && defaultschool.Equals(schoolCodeNow)) ? true : false;

                                                tmidStics.ies5.schools.Add(schoolobj);
                                            }
                                        }
                                    }
                                }

                                //IES5個人空間
                                var (usedSize, teach, total, surplus, catalog) = await BlobService.GetSurplusSpace(id, "private", _option.Location, _azureCosmos, _azureRedis, _azureStorage, _dingDing, _httpTrigger);
                                tmidStics.ies5.usedSize = usedSize;
                                tmidStics.ies5.teachSize = teach * 1073741824;
                                tmidStics.ies5.totalSize = total * 1073741824;
                                tmidStics.ies5.surplusSize = surplus;
                                //IES5個人資源數
                                tmidStics.ies5.resCount = 0; //教材數
                                tmidStics.ies5.itemCount = 0; //題目數
                                tmidStics.ies5.paperCount = 0; //試卷數
                                ///IES5個人題目數
                                await foreach (int itemC in cosmosClientIes5.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryIterator<int>(queryText: $"SELECT VALUE COUNT(1) FROM c WHERE c.pid = null", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Item-{id}") }))
                                {
                                    tmidStics.ies5.itemCount = itemC;
                                }
                                ///IES5個人試卷數
                                await foreach (int paperC in cosmosClientIes5.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryIterator<int>(queryText: $"SELECT VALUE COUNT(1) FROM c WHERE c.scope = 'private'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Paper-{id}") }))
                                {
                                    tmidStics.ies5.paperCount = paperC;
                                }
                                ///IES5個人教材數
                                List<BlobService.BlobCount> blogCountList = await BlobService.BloblogCount(cosmosClientIes5, "private", id, "", "");
                                foreach (BlobService.BlobCount blobCountRow in blogCountList)
                                {
                                    switch (blobCountRow.type)
                                    {
                                        case "doc":
                                        case "image":
                                        case "res":
                                        case "video":
                                        case "audio":
                                        case "other":
                                            tmidStics.ies5.resCount += blobCountRow.count;
                                            break;
                                    }
                                }
                                if (!tmidDic.ContainsKey(id)) tmidDic.Add(id, tmidStics);
                            }
                        }
                    }
                }
                //個人權益
                if (eventList.Contains("benefits"))
                {
                    query = new QueryDefinition(@"SELECT * FROM c WHERE (ARRAY_CONTAINS(@key, c.id))")
                                          .WithParameter("@key", tmidDic.Keys.ToList());
                    await foreach (var item in cosmosClientCsv2
                                .GetContainer("Core", "ID2")
                                .GetItemQueryStreamIterator(query, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("benefits") }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.ContentStream);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        {
                            foreach (var doc in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                string id = doc.GetProperty("id").GetString();
                                if (doc.TryGetProperty("hiteach", out var elementHiteachData))
                                {
                                    tmidDic[id].benefits.hiteach = elementHiteachData.ToObject<List<object>>();
                                }
                                if (doc.TryGetProperty("hiteachcc", out var elementHiteachCCData))
                                {
                                    tmidDic[id].benefits.hiteachcc = elementHiteachCCData.ToObject<List<object>>();
                                }
                            }
                        }
                    }
                }
                //蘇格拉底資料
                if (eventList.Contains("sokrates"))
                {
                    query = new QueryDefinition(@"SELECT * FROM c WHERE (ARRAY_CONTAINS(@key, c.id))")
                      .WithParameter("@key", tmidDic.Keys.ToList());
                    await foreach (var item in cosmosClientCsv2
                                .GetContainer("Core", "ID2")
                                .GetItemQueryStreamIterator(query, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("sokrates") }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.ContentStream);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        {
                            foreach (var doc in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                string id = doc.GetProperty("id").GetString();
                                if (doc.TryGetProperty("hiteach_data", out var elementHiteachData))
                                {
                                    tmidDic[id].sokrates.hiteach_data = elementHiteachData.ToObject<object>();
                                }
                                if (doc.TryGetProperty("user_channels", out var elementUserChannels))
                                {
                                    tmidDic[id].sokrates.user_channels = elementUserChannels.ToObject<object>();
                                }
                            }
                        }
                    }
                }

                //輸出
                List<object> data = new();
                foreach (KeyValuePair<string, TmidStics> dicItem in tmidDic)
                {
                    data.Add(dicItem.Value);
                }
                return Ok(data);
            }
            catch (Exception ex)
            {
                //await _dingDing.SendBotMsg($"BI，{_option.Location} /tmid/get-tmidstics \n {ex.Message}\n{ex.StackTrace}", GroupNames.台北開發測試群組);
                return BadRequest();
            }
        }

        //取得TMID服務授權週期
        public async Task<List<object>> getTMIDAuthService(CosmosClient cosmosClientCsv2, string tmid, string prodCode, bool validFlg)
        {
            long now = DateTimeOffset.UtcNow.ToUnixTimeSeconds();
            var qryOption = new QueryRequestOptions() { PartitionKey = new PartitionKey("servicePeriod") };
            string whereSql = $"c.saleClient.tmid = '{tmid}'";
            if (!string.IsNullOrWhiteSpace(prodCode)) whereSql += $" AND c.prodCode = '{prodCode}'";
            if (validFlg) whereSql += $" AND c.startDate <= {now} AND {now} <= c.endDate";
            string sql = $"SELECT c.id, c.prodCode, c.type, c.startDate, c.endDate, c.number, c.unit, c.aprule FROM c WHERE {whereSql}";
            var client = cosmosClientCsv2.GetContainer("Habb", "Auth");
            var result = new List<object>();
            await foreach (var item in client.GetItemQueryStreamIterator(queryText: sql, requestOptions: qryOption))
            {
                var json = await JsonDocument.ParseAsync(item.ContentStream);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                {
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        result.Add(
                        new
                        {
                            id = obj.GetProperty("id").GetString(),
                            prodCode = obj.GetProperty("prodCode").GetString(),
                            type = obj.GetProperty("type").GetInt32(),
                            startDate = obj.GetProperty("startDate").GetInt64(),
                            endDate = obj.GetProperty("endDate").GetInt64(),
                            number = obj.GetProperty("number").GetInt32(),
                            aprule = obj.GetProperty("aprule")
                        });
                    }
                }
            }
            return result;
        }

        //取得TMID購買紀錄
        public async Task<List<Ies5OrderHis>> getTMIDAuthOrder(CosmosClient cosmosClientCsv2, string tmid, string prodCode)
        {
            SortedDictionary<string, Ies5OrderHis> OrderDic = new SortedDictionary<string, Ies5OrderHis>();
            var qryOption = new QueryRequestOptions() { PartitionKey = new PartitionKey("order") };
            //服務 ※序號、硬體 不列入購買紀錄
            string strQueryV = $"SELECT * FROM c WHERE IS_DEFINED(c.saleClient.tmid) AND c.saleClient.tmid = '{tmid}' AND c.prodType = 'service'";
            if (!string.IsNullOrWhiteSpace(prodCode))
            {
                strQueryV += $" AND c.prodCode = '{prodCode}'";
            }
            await foreach (OrderHisService orderService in cosmosClientCsv2.GetContainer("Habb", "Auth").GetItemQueryIterator<OrderHisService>(strQueryV, null, qryOption))
            {
                Ies5OrderHisService ies5OrderVRow = new Ies5OrderHisService();
                ies5OrderVRow.prodCode = orderService.prodCode;
                ies5OrderVRow.type = (orderService.contract.Equals(1)) ? "C" : (orderService.contract.Equals(2)) ? "G" : "N"; //授權方式 [FROM]contract契約型態 0:新約 1:續約 2:變更 [TO]type N:新約 C:續約 G:變更
                ies5OrderVRow.sdate = orderService.startDate;
                ies5OrderVRow.edate = orderService.endDate;
                ies5OrderVRow.number = orderService.number;
                ies5OrderVRow.unit = orderService.unit;
                //加入Order字典
                string OrderID = orderService.orderinfo.orderid.ToString();
                long OrderDate = orderService.orderinfo.createDate;
                if (OrderDic.ContainsKey(OrderID))
                {
                    OrderDic[OrderID].service.Add(ies5OrderVRow);
                }
                else
                {
                    Ies5OrderHis ies5OrderHisNew = new Ies5OrderHis();
                    ies5OrderHisNew.id = OrderID;
                    ies5OrderHisNew.date = OrderDate;
                    ies5OrderHisNew.service.Add(ies5OrderVRow);
                    OrderDic.Add(OrderID, ies5OrderHisNew);
                }
            }

            //輸出項
            List<Ies5OrderHis> Result = new List<Ies5OrderHis>();
            foreach (KeyValuePair<string, Ies5OrderHis> item in OrderDic)
            {
                Result.Add(item.Value);
            }
            return Result;
        }

        //Tool
        //資料遮罩
        ///規則：
        ///1.手機、電話：隱藏後4碼
        ///2.Mail：隱藏@前資料
        ///3.姓名：只顯示第一個字元
        ///4.身分證、護照：隱藏後4碼
        public string GenDataMask(string data, string type)
        {
            int length = 0;
            string subString = string.Empty;
            if (!string.IsNullOrWhiteSpace(data))
            {
                switch (type)
                {
                    case "mobile":
                    case "id":
                        length = 4;
                        subString = data.Substring(data.Length - length, length);
                        data = data.Replace(subString, "****");
                        break;
                    case "mail":
                        string[] dataList = data.Split("@");
                        subString = dataList.First();
                        data = data.Replace(subString, "****");
                        break;
                    case "name":
                        length = 2;
                        subString = data.Substring(data.Length - length, length);
                        data = data.Replace(subString, "〇〇");
                        break;
                }
            }
            return data;
        }

        public async Task<TmidAnalysisCal> getTMIDIotData(CosmosClient cosmosClientIes5, string tmid, string toolType, string dateUnit, int year, int month, int day, long from, long to)
        {
            TmidAnalysisCal result = new TmidAnalysisCal();
            List<string> calPropList = new List<string>() { "lessonRecord", "useIES", "useIES5Resource", "useWebIrs", "useDeviceIrs", "useHaboard", "useHita", "lessonLengMin", "lessonLeng0", "stuShow", "stuLessonLengMin", "tGreen", "tLesson", "lTypeCoop", "lTypeIact", "lTypeMis", "lTypeTst", "lTypeDif", "lTypeNone", "lessonCnt928", "lessonCntId", "lessonCntDevice", "lessonCntIdDevice", "mission", "missionFin", "item", "interact", "sendSok" }; //要計算的ProdAnalysis欄位列表
            string strQuery = $"SELECT * FROM c WHERE c.tmid = '{tmid}' AND c.toolType = '{toolType}'";
            var qryOption = new QueryRequestOptions() { PartitionKey = new PartitionKey("TmidAnalysis") };
            if (!string.IsNullOrWhiteSpace(dateUnit)) strQuery += $" AND c.dateUnit = '{dateUnit}'";
            if (year > 0) strQuery += $" AND c.year = {year}";
            if (month > 0) strQuery += $" AND c.month = {month}";
            if (day > 0) strQuery += $" AND c.day = {day}";
            if (from > 0) strQuery += $" AND c.dateTime >= {from}";
            if (to > 0) strQuery += $" AND c.dateTime <= {to}";

            await foreach (TmidAnalysisCosmos tmidAnalysis in cosmosClientIes5.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryIterator<TmidAnalysisCosmos>(strQuery, null, qryOption))
            {
                //一般項
                result.tmid = tmidAnalysis.tmid;
                result.dateUnit = tmidAnalysis.dateUnit;
                result.toolType = tmidAnalysis.toolType;
                result.date = tmidAnalysis.date;
                if (tmidAnalysis.year > 0) result.year = tmidAnalysis.year;
                if (tmidAnalysis.month > 0) result.month = tmidAnalysis.month;
                if (tmidAnalysis.day > 0) result.day = tmidAnalysis.day;
                if (result.from.Equals(0) || tmidAnalysis.dateTime <= result.from) result.from = tmidAnalysis.dateTime;
                if (result.to.Equals(0) || tmidAnalysis.dateTime >= result.to) result.to = tmidAnalysis.dateTime;
                result.dateList.Add(tmidAnalysis.date);
                //統計項
                foreach (PropertyInfo propertyInfo in tmidAnalysis.GetType().GetProperties()) //累加項目
                {
                    if (calPropList.Contains(propertyInfo.Name))
                    {
                        var propType = propertyInfo.PropertyType;
                        var valNow = propertyInfo.GetValue(result);
                        var valTodo = tmidAnalysis.GetType().GetProperty(propertyInfo.Name).GetValue(tmidAnalysis);
                        if (propType.Equals(typeof(long))) propertyInfo.SetValue(result, Convert.ToInt64(valNow.ToString(), 10) + Convert.ToInt64(valTodo.ToString(), 10));
                        else propertyInfo.SetValue(result, Convert.ToInt32(valNow.ToString(), 10) + Convert.ToInt32(valTodo.ToString(), 10));
                    }
                }
                result.deviceList = result.deviceList.Union(tmidAnalysis.deviceList).ToList();
                result.deviceCnt = result.deviceList.Count;
                result.deviceNoAuthList = result.deviceNoAuthList.Union(tmidAnalysis.deviceNoAuthList).ToList();
                result.deviceNoAuth = result.deviceNoAuthList.Count;
                result.deviceAuthList = result.deviceAuthList.Union(tmidAnalysis.deviceAuthList).ToList();
                result.deviceAuth = result.deviceAuthList.Count;
                result.tmidList = result.tmidList.Union(tmidAnalysis.tmidList).ToList();
                result.tmidCnt = result.tmidList.Count;
                result.verList = result.verList.Union(tmidAnalysis.verList).ToList();
            }
            if (string.IsNullOrWhiteSpace(result.tmid)) result = null;
            return result;
        }

        //Model

        //TMID統計 基本資訊
        private class TmidStics
        {
            public string id { get; set; }
            public string name { get; set; }
            public string picture { get; set; }
            public string mobile { get; set; }
            public string mail { get; set; }
            public string country { get; set; }
            public string province { get; set; }
            public string city { get; set; }
            public string dist { get; set; }
            public string schoolCode { get; set; }
            public string schoolCodeW { get; set; }
            public string lang { get; set; }
            public string unitType { get; set; } //1:基礎教育機構(K-小學) 2:中等教育機構(國中、高中/職) 3:高等教育機構 4:政府單位機構 5:NGO機構 6:企業機構 7:其他 8:學前教育 9:特殊教育
            public string unitName { get; set; }
            public string jobTitle { get; set; }
            public TmidSticsIes5 ies5 { get; set; } //IES統計資料
            public TmidPoints points { get; set; }
            public TmidSokrates sokrates { get; set; }
            public TmidBenefits benefits { get; set; }
            public List<TmidCoupon> coupons { get; set; } = new();
            public List<TmidLoginTime> login { get; set; } = new();
            public List<object> prod { get; set; } = new();
            public TmidIot iot { get; set; }
            public bool wechat { get; set; }
            public bool facebook { get; set; }
            public bool google { get; set; }
            public bool ding { get; set; }
            public bool apple { get; set; }
            public bool educloudtw { get; set; }
            public long ts { get; set; } //資料最終變更時間
        }
        //TMID統計 IES5資訊
        private class TmidSticsIes5
        {
            public long usedSize { get; set; } //已使用的存儲空間(Byte)
            public long teachSize { get; set; } //分配給教師的空間(Byte)
            public long totalSize { get; set; } //總空間(Byte)
            public long surplusSize { get; set; } //剩余的空間(Byte)
            public List<TmidSticsIes5School> schools { get; set; } = new(); //各學校資料
            public int resCount { get; set; } //教材數
            public int itemCount { get; set; } //題目數
            public int paperCount { get; set; } //試卷數

        }
        private class TmidSticsIes5School
        {
            public string schoolId { get; set; }
            public string name { get; set; }
            public string region { get; set; }
            public string province { get; set; }
            public string city { get; set; }
            public string dist { get; set; }
            public object size { get; set; } = new();
            public int courseCnt { get; set; }
            public bool defaultSchool { get; set; }
        }
        private class TmidPoints
        {
            public int points { get; set; } = 0; //累積的點數(只加不減)
            public int balance { get; set; } = 0; //可用的點數
            public int level { get; set; } = 0; //等級
        }
        private class TmidCoupon
        {
            public string code { get; set; }
            public long exchange { get; set; }
        }
        private class TmidLoginTime
        {
            public string product { get; set; }
            public long time { get; set; }
        }
        private class TmidSokrates
        {
            public object hiteach_data { get; set; }
            public object user_channels { get; set; }
        }
        private class TmidBenefits
        {
            public List<object> hiteach { get; set; }
            public List<object> hiteachcc { get; set; }
        }
        private class TmidIot
        {
            public TmidIotYMD hiteach { get; set; } = new();
            public TmidIotYMD hiteachcc { get; set; } = new();
        }
        private class TmidIotYMD
        {
            public TmidAnalysisCal year  { get; set; }
            public TmidAnalysisCal month { get; set; }
            public TmidAnalysisCal day { get; set; }
        }

        //[API輸出] 訂單履歷 
        public class Ies5OrderHis
        {
            public Ies5OrderHis()
            {
                service = new List<Ies5OrderHisService>();
            }
            public string id { get; set; }
            public long date { get; set; }
            public List<Ies5OrderHisService> service { get; set; } = new ();
        }
        //[API輸出] 訂單履歷.服務型產品
        public class Ies5OrderHisService
        {
            public string prodCode { get; set; } //產品代碼
            public string type { get; set; } //授權方式 N:新約 C:續約
            public long sdate { get; set; } //授權起始時間
            public long edate { get; set; } //授權終止時間
            public int number { get; set; } //購買數量
            public string unit { get; set; } //購買單位 無單位者為null
        }

        //[承接DB] DB: Habb.Auth
        //[承接DB] 訂單履歷基本Class
        public class OrderHisBase
        {
            public string id { get; set; } //OPID
            public OrderHisOrderInfo orderinfo { get; set; } //訂單資訊
            public string prodCode { get; set; } //產品代碼
            public SerialSaleClient saleClient { get; set; } = null; //銷售終端
            public string prodType { get; set; } //產品類型 serial:序號 service:服務 hard:硬體
            public string dataType { get; set; } //資料類型
            public long operationTime { get; set; } //最新資料變更時間戳記
            public int ttl { get; set; } = -1; //過期刪除秒數

        }
        //[承接DB] 訂單履歷基本Class.銷售終端
        public class SerialSaleClient
        {
            public string name { get; set; } //[String]銷售終端姓名
            public string schoolCode { get; set; } = null; //[String]銷售終端學校代碼
            public string clientId { get; set; } = null; //[String]銷售終端客戶ID
            public string tmid { get; set; } = null; //[String]TMID
            public string type { get; set; } //[String]銷售終端資料類型  school:學校 client:經銷商客戶
            public string countryId { get; set; } //[String]國家代碼
            public string provinceId { get; set; } //[String]省代碼
            public string cityId { get; set; } //[String]市代碼
            public string schoolShortCode { get; set; } //[String]學校簡碼
            public string districtId { get; set; } //[String]區代碼
            public string dataCenter { get; set; } //[String]數據中心
        }

        //[承接DB] 訂單履歷基本Class.訂單資訊
        public class OrderHisOrderInfo
        {
            public string orderid { get; set; } //[String]訂單編號
            public int orderAudit { get; set; } //[Int]訂單審核狀態 0:待審, 1:通過, 2:否決, 3:問題
            public int orderProperty { get; set; } //[Int]訂單類型 0:銷售,1:展示申請 2:內部申請
            public long createDate { get; set; } //訂單創建時間
        }
        //[承接DB] 訂單履歷 服務類
        public class OrderHisService : OrderHisBase
        {
            public int type { get; set; } //[Int]授權類型 0:銷售 1:試用
            public int contract { get; set; } //[Int]契約型態 0:新約 1:續約
            public long startDate { get; set; } //[long]授權起始日期 Timestamp(UTC)
            public long endDate { get; set; } //[long]授權終止日期 Timestamp(UTC)
            public int number { get; set; } //[Int]數量
            public string unit { get; set; } //[String]單位 (無者為空)
        }

        //雲端服務ClientId列表
        private readonly Dictionary<string, string> _dicClientIds = new()
        {
            { "917d02f2-5b91-404e-a71d-7bdd926ddd81", "HiTeach" },
            { "c5328340-b8eb-4489-8650-8c8862d7acfe", "HiTeach" },
            { "118d2d4d-32a3-44c0-808a-792ca73d3706", "HiTeachCC" },
            { "227082f4-8db0-4517-b281-93dba9428bc7", "HiTeachCC" },
            { "371a99aa-7efd-4c3a-90a8-95b7db4f42c0", "HiTA" },
            { "0ed38cde-e7fe-4fa6-9eaa-33ad404eb532", "HiTA" },
            { "531fecd1-b1a5-469a-93ca-7984e1d392f2", "IES5" },
            { "c7317f88-7cea-4e48-ac57-a16071f7b884", "IES5" },
            { "c8de822b-3fcf-4831-adba-cdd14653bf7f", "Account" },
            { "516148eb-6a38-4657-ba98-a3699061937f", "Account" },
            { "d7193896-9b12-4046-ad44-c991dd48cc39", "Sokrates" },
            { "59009e38-6e30-4116-814b-7605939edc47", "Sokrates" },
            { "626c7285-15aa-4abe-8c0d-2c5ff1381538", "SokAPP" },
            { "5bcc16a5-7d20-4cc4-b5c2-8ed0416f32b6", "SokAPP" },
            { "044482b5-d024-4f23-9b55-906884243405", "IRS" },
            { "5e27b7e3-b36c-4ce9-b838-e94fd0cea080", "IRS" }
        };

        //TMID IOT date
        public class tmidIotDate
        {
            public string dateUnit { get; set; } //[string]日期單位：年月日 year, month, day
            public int year { get; set; } //[Int]年
            public int month { get; set; } //[Int]月
            public int day { get; set; } //[Int]日
            public long from { get; set; } //[long]UTC timestamp 起始日
            public long to { get; set; } //[long]UTC timestamp 終止日
        }
        public class TmidAnalysisCal : TmidAnalysisCosmos
        {
            public long from { get; set; } //[long]UTC timestamp 起始日
            public long to { get; set; } //[long]UTC timestamp 終止日
            public List<string> dateList { get; set; } = new();
        }
    }

}