TEAMModelOS/TEAMModelBI/Controllers/BITmid/TmidController.cs

using Azure.Cosmos;
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;

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);
                var y = datetime.Year;
                var m = datetime.Month;
                var d = datetime.Day;

                //服務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);

                //存放user資料
                Dictionary<string, TmidStics> tmidDic = new();

                QueryDefinition query =
                       new QueryDefinition(@"SELECT c.id, c.name, c.mobile, c.mail 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 };
                            string tmidName = doc.GetProperty("name").GetString();
                            string tmidMobile = GenDataMask(doc.GetProperty("mobile").GetString(), "mobile");
                            string tmidMail = GenDataMask(doc.GetProperty("mail").GetString(), "mail");

                            //票券
                            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(),
                                    });
                            }

                            //登入各服務的時間
                            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
                                    });
                            }

                            //積分
                            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;
                            
                            //個人服務授權
                            tmidStics.prod = await getTMIDAuthService(cosmosClientCsv2, id, "", true);

                            //IOT
                            tmidStics.iot.hiteach.year = await getTMIDIotData(cosmosClientIes5, id, "HiTeach", "year", y, 0, 0, 0, 0);
                            tmidStics.iot.hiteach.month = await getTMIDIotData(cosmosClientIes5, id, "HiTeach", "month", y, m, 0, 0, 0);
                            tmidStics.iot.hiteach.day = await getTMIDIotData(cosmosClientIes5, id, "HiTeach", "day", y, m, d, 0, 0);
                            tmidStics.iot.hiteachcc.year = await getTMIDIotData(cosmosClientIes5, id, "HiTeachCC", "year", y, 0, 0, 0, 0);
                            tmidStics.iot.hiteachcc.month = await getTMIDIotData(cosmosClientIes5, id, "HiTeachCC", "month", y, m, 0, 0, 0);
                            tmidStics.iot.hiteachcc.day = await getTMIDIotData(cosmosClientIes5, id, "HiTeachCC", "day", y, m, d, 0, 0);

                            tmidDic.Add(id, tmidStics);
                        }
                    }
                }

                //ID進階資料
                query = new QueryDefinition(@"SELECT c.id, c.name, c.mobile, c.mail, c.country, c.province, c.city, c.schoolCode, c.schoolCodeW 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 (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");
                            tmidStics.country = doc.GetProperty("country").GetString();
                            tmidStics.province = doc.GetProperty("province").GetString();
                            tmidStics.city = doc.GetProperty("city").GetString();
                            tmidStics.schoolCode = (doc.TryGetProperty("schoolCode", out JsonElement schCode)) ? schCode.GetString() : string.Empty;
                            tmidStics.schoolCodeW = (doc.TryGetProperty("schoolCodeW", out JsonElement schCodeW)) ? schCodeW.GetString() : string.Empty;
                        }
                    }
                }

                //IES5
                query = new QueryDefinition(@"SELECT c.id, c.defaultSchool, c.schools FROM c WHERE ARRAY_CONTAINS(@key, c.id)")
                      .WithParameter("@key", tmids);
                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.ContainsKey(id)) ? tmidDic[id] : new() { id = id, ies5 = new TmidSticsIes5() { schools = new List<object>() } };
                            TmidStics tmidStics;
                            if (tmidDic.ContainsKey(id))
                            {
                                tmidStics = tmidDic[id];
                            } else
                            {
                                tmidStics = new TmidStics();
                                tmidStics.id = 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);
                        }
                    }
                }

                //個人權益
                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>>();
                            }
                        }
                    }
                }

                //蘇格拉底資料
                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", "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;
                //統計項
                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;
            }
            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 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 schoolCode { get; set; }
            public string schoolCodeW { get; set; }
            public TmidSticsIes5 ies5 { get; set; } = new(); //IES統計資料
            public TmidPoints points { get; set; } = new();
            public TmidSokrates sokrates { get; set; } = new();
            public TmidBenefits benefits { get; set; } = new();
            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; } = new();
        }
        //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 終止日
        }
    }

}