TEAMModelOS/TEAMModelBI/Controllers/BIProductAnalysis/ProductAnalysisController.cs

using Microsoft.Azure.Cosmos;
using DingTalk.Api.Request;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Options;
using StackExchange.Redis;
using System;
using System.Collections.Generic;
using System.Dynamic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using TEAMModelBI.Filter;
using TEAMModelBI.Tool;
using TEAMModelBI.Tool.Extension;
using TEAMModelOS.Models;
using TEAMModelOS.SDK.Context.BI;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Extension;
using TEAMModelOS.SDK.Models;
using TEAMModelOS.SDK.Models.Service.BI;
using PartitionKey = PartitionKey;
using QueryRequestOptions = QueryRequestOptions;

namespace TEAMModelBI.Controllers.ProductAnalysis
{
    [Route("prodanalysis")]
    [ApiController]
    public class ProductAnalysisController : 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;

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

        /// <summary>
        /// 取得產品分析IOT統計資料(redis)
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [Authorize(Roles = "IES")]
        [HttpPost("get-iotstics")]
        public async Task<IActionResult> GetIotStics(JsonElement jsonElement)
        {
            try
            {
                var cosmosClient = _azureCosmos.GetCosmosClient();
                var redisClinet8 = _azureRedis.GetRedisClient(8);
                if (!jsonElement.TryGetProperty("dateFrom", out JsonElement dateFromJobj)) return BadRequest();//查詢日期:起始(string)[例]2023-03-05
                if (!jsonElement.TryGetProperty("dateTo", out JsonElement dateToJobj)) return BadRequest();//查詢日期:結束(string)[例]2023-03-27
                if (!jsonElement.TryGetProperty("prod", out JsonElement prodJobj)) return BadRequest();//產品 HiTeach, HiTeachCC, HiTA
                string prod = (prodJobj.ToString().Equals("HiTeach") || prodJobj.ToString().Equals("HiTeachCC") || prodJobj.ToString().Equals("HiTA")) ? prodJobj.ToString() : string.Empty;
                if(string.IsNullOrWhiteSpace(prod)) return BadRequest();
                if (!jsonElement.TryGetProperty("schoolIds", out JsonElement schoolIdsJobj)) return BadRequest();//學校ID(array)
                List<string> schoolIds = schoolIdsJobj.ToObject<List<string>>();
                string dateUnit = (jsonElement.TryGetProperty("dateUnit", out JsonElement dateUnitJobj)) ? (!string.IsNullOrWhiteSpace(Convert.ToString(dateUnitJobj))) ? Convert.ToString(dateUnitJobj).ToLower() : "day" : "day"; //時間統計單位 ※以每年(Year)、每月(Month)、每日(Day) 為統計單位 預設值:每日
                string geoUnit = (jsonElement.TryGetProperty("geoUnit", out JsonElement geoUnitJobj)) ? (!string.IsNullOrWhiteSpace(Convert.ToString(geoUnitJobj))) ? Convert.ToString(geoUnitJobj).ToLower() : "city" : "city"; //地理統計單位 region:國 province:省 city:市 dist:區 預設值:市
                //起始終止日期換算
                List<string> dateFromList = dateFromJobj.ToString().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 = dateToJobj.ToString().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;
                DateTimeOffset dateTimeFrom = new DateTimeOffset(dateFromYear, dateFromMonth, dateFromDay, 0, 0, 0, TimeSpan.Zero);
                DateTimeOffset dateTimeTo = new DateTimeOffset(dateToYear, dateToMonth, dateToDay, 23, 59, 59, TimeSpan.Zero);
                long dateTimeFromSec = dateTimeFrom.ToUnixTimeSeconds();
                long dateTimeToSec = dateTimeTo.ToUnixTimeSeconds();

                //CosmosDB資料取得
                ////取得學校基本資訊 => 記入Dictionary
                Dictionary<string, Dictionary<string, string>> schDic = new();
                string SqlSch = $"SELECT c.id, c.name, c.code, c.region, c.province, c.city, c.areaId, c.dist FROM c WHERE (c.code = 'Base' OR c.code = 'VirtualBase')";
                //ARRAY_CONTAINS({schIdListStr}, c.id, true) AND
                if(schoolIds.Count > 0)
                {
                    string schIdListStr = JsonSerializer.Serialize(schoolIds);
                    SqlSch += $" AND ARRAY_CONTAINS({schIdListStr}, c.id, true)";
                }
                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIteratorSql(queryText: SqlSch, requestOptions: null))
                {
                    var json = await JsonDocument.ParseAsync(item.Content);
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        string schId = obj.GetProperty("id").GetString();
                        string name = Convert.ToString(obj.GetProperty("name"));
                        string region = Convert.ToString(obj.GetProperty("region"));
                        string province = Convert.ToString(obj.GetProperty("province"));
                        string city = Convert.ToString(obj.GetProperty("city"));
                        string dist = (obj.TryGetProperty("dist", out JsonElement distJ)) ? Convert.ToString(distJ) : string.Empty;
                        string areaId = (obj.TryGetProperty("areaId", out JsonElement areaIdJ)) ? Convert.ToString(areaIdJ) : string.Empty;
                        string type = Convert.ToString(obj.GetProperty("code"));
                        if (!schDic.ContainsKey(schId))
                        {
                            Dictionary<string, string> schDicRow = new() { { "name", name }, { "region", region }, { "province", province }, { "city", city }, { "dist", dist }, { "areaId", areaId }, { "type", type } };
                            schDic.Add(schId, schDicRow);
                        }
                    }
                }
                //取得學校產品授權資訊
                Dictionary<string, List<SchoolProductSumDataService>> serviceResultDic = new Dictionary<string, List<SchoolProductSumDataService>>(); //key:學校ID
                Dictionary<string, List<SchProdSerial>> serialResultDic = new Dictionary<string, List<SchProdSerial>>();
                if (schoolIds.Count > 0)
                {
                    try
                    {
                        //服務
                        string schoolIdsStr = JsonSerializer.Serialize(schoolIds);
                        string SqlSchProdSoervice = $"SELECT * FROM c WHERE ARRAY_CONTAINS({schoolIdsStr}, c.id)";
                        await foreach (SchoolProductSum schProductSum in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryIteratorSql<SchoolProductSum>(queryText: SqlSchProdSoervice, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"ProductSum") }))
                        {
                            if (schProductSum != null && schProductSum.service != null && schProductSum.service.Count > 0)
                            {
                                string schId = schProductSum.id;
                                if (!serviceResultDic.ContainsKey(schId)) serviceResultDic.Add(schId, new List<SchoolProductSumDataService>());
                                serviceResultDic[schId] = schProductSum.service;
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                    }
                    //軟體
                    if (schoolIds.Count > 0)
                    {
                        List<string> schoolCodes = new List<string>();
                        foreach (string schId in schoolIds)
                        {
                            schoolCodes.Add($"Product-{schId}");
                        }
                        string schoolCodesStr = JsonSerializer.Serialize(schoolCodes);
                        string SqlSchProdSoft = $"SELECT * FROM c WHERE c.dataType = 'serial' AND c.expireStatus = 'A' AND ARRAY_CONTAINS({schoolCodesStr}, c.code, true)";
                        try
                        {
                            await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIteratorSql(queryText: SqlSchProdSoft, requestOptions: null ))
                            {
                                var json = await JsonDocument.ParseAsync(item.Content);
                                foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                                {
                                    SchoolProductSerial serialRow = obj.ToObject<SchoolProductSerial>();
                                    SchAuthDataSerial schAuthDataSerial = new SchAuthDataSerial();
                                    schAuthDataSerial.prodCode = serialRow.prodCode;
                                    schAuthDataSerial.serial = serialRow.serial;
                                    schAuthDataSerial.deviceBoundCnt = (serialRow.deviceBound != null) ? serialRow.deviceBound.Count : 0;
                                    schAuthDataSerial.clientQty = serialRow.clientQty;
                                    schAuthDataSerial.regDate = serialRow.regDate;
                                    schAuthDataSerial.startDate = serialRow.startDate;
                                    schAuthDataSerial.endDate = serialRow.endDate;
                                    schAuthDataSerial.deviceMax = serialRow.deviceMax;
                                    schAuthDataSerial.aprule = serialRow.aprule;

                                    string schId = serialRow.code.Replace("Product-", "");
                                    if (!serialResultDic.ContainsKey(schId)) serialResultDic.Add(schId, new List<SchProdSerial>());
                                    SchProdSerial schProdSerialNow = serialResultDic[schId].Where(s => s.prodCode.Equals(serialRow.prodCode) && s.endDate.Equals(serialRow.endDate) && s.deviceMax.Equals(serialRow.deviceMax)).FirstOrDefault();
                                    if (schProdSerialNow == null)
                                    {
                                        SchProdSerial schProdSerialNew = new SchProdSerial();
                                        schProdSerialNew.prodCode = serialRow.prodCode;
                                        schProdSerialNew.endDate = serialRow.endDate;
                                        schProdSerialNew.deviceMax = serialRow.deviceMax;
                                        schProdSerialNew.serials.Add(schAuthDataSerial);
                                        serialResultDic[schId].Add(schProdSerialNew);
                                    }
                                    else
                                    {
                                        schProdSerialNow.serials.Add(schAuthDataSerial);
                                    }
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                        }
                    }
                }

                ////取得IOT產品分析資訊、回傳結果製作
                List<string> calProperty = 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","learnPeer","learnCoop","useWordCloud","useClouDAS","useGPT","useIes5Test","usePaperTest","useExcelTest","useScoreBoard","learnParticipationCnt","learnParticipationT","coopMission","coopWork","coopContributionT","peerAct","peerStuParticipationT","useTransMode","useMiniMode"}; //要加算統計的欄位名
                List<ProdAnalysisApiResult> result = new List<ProdAnalysisApiResult>(); //各校統計資料
                Dictionary<string, ProdAnalysisApiResult> geoDic = new Dictionary<string, ProdAnalysisApiResult>(); //各地理資訊統計資料
                string Sql = $"SELECT * FROM c WHERE c.toolType = '{prod}' AND c.dateUnit = '{dateUnit}' AND c.dateTime >= {dateTimeFromSec} AND c.dateTime <= {dateTimeToSec}";
                if(schoolIds.Count > 0)
                {
                    schoolIds.Add("noschoolid");
                    schoolIds.Add("allschool");
                    string schIdListStr = JsonSerializer.Serialize(schoolIds);
                    Sql += $" AND ARRAY_CONTAINS({schIdListStr}, c.schoolId, true)";
                }
                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIteratorSql(queryText: Sql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"ProdAnalysis") }))
                {
                    var json = await JsonDocument.ParseAsync(item.Content);
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        ProdAnalysisApiResult resultRow = obj.ToObject<ProdAnalysisApiResult>();
                        resultRow.lTypeNone = ((resultRow.lTypeNone - resultRow.lessonLeng0) < 0) ? 0 : resultRow.lTypeNone - resultRow.lessonLeng0; //無學習型態數值須扣除未上課
                        //各校IOT統計
                        if (schDic.ContainsKey(resultRow.schoolId))
                        {
                            resultRow.school.name = schDic[resultRow.schoolId]["name"];
                            resultRow.school.region = schDic[resultRow.schoolId]["region"];
                            resultRow.school.province = schDic[resultRow.schoolId]["province"];
                            resultRow.school.city = schDic[resultRow.schoolId]["city"];
                            resultRow.school.dist = schDic[resultRow.schoolId]["dist"];
                            resultRow.school.areaId = schDic[resultRow.schoolId]["areaId"];
                            resultRow.school.type = schDic[resultRow.schoolId]["type"];
                        }
                        //各校產品授權資訊
                        result.Add(resultRow);
                        //地理位置統計
                        string geoKey = string.Empty;
                        switch (geoUnit)
                        {
                            case "region":
                                geoKey = resultRow.school.region;
                                break;
                            case "province":
                                geoKey = resultRow.school.region + resultRow.school.province;
                                break;
                            case "city":
                                geoKey = resultRow.school.region + resultRow.school.province + resultRow.school.city;
                                break;
                            case "dist":
                                geoKey = resultRow.school.region + resultRow.school.province + resultRow.school.city + resultRow.school.dist;
                                break;
                        }
                        if (!string.IsNullOrWhiteSpace(geoKey))
                        {
                            geoKey = $"{geoKey}-{resultRow.date}";
                            if (!geoDic.ContainsKey(geoKey)) geoDic.Add(geoKey, new ProdAnalysisApiResult());
                            foreach (PropertyInfo propertyInfo in resultRow.GetType().GetProperties())
                            {
                                if (calProperty.Contains(propertyInfo.Name))
                                {
                                    var propType = propertyInfo.PropertyType;
                                    var valNow = propertyInfo.GetValue(geoDic[geoKey]);
                                    var valTodo = resultRow.GetType().GetProperty(propertyInfo.Name).GetValue(resultRow);
                                    if (propType.Equals(typeof(long))) propertyInfo.SetValue(geoDic[geoKey], Convert.ToInt64(valNow.ToString(), 10) + Convert.ToInt64(valTodo.ToString(), 10));
                                    else propertyInfo.SetValue(geoDic[geoKey], Convert.ToInt32(valNow.ToString(), 10) + Convert.ToInt32(valTodo.ToString(), 10));
                                }
                            }
                            decimal learnParticipationTmp = (resultRow.learnParticipationCnt > 0) ? (decimal)resultRow.learnParticipationT / (decimal)resultRow.learnParticipationCnt : 0;
                            geoDic[geoKey].date = resultRow.date;
                            geoDic[geoKey].learnParticipation = Math.Round(learnParticipationTmp, 2); //學習參與度指數(平均)
                            geoDic[geoKey].deviceList = geoDic[geoKey].deviceList.Union(resultRow.deviceList).ToList();
                            geoDic[geoKey].deviceCnt = geoDic[geoKey].deviceList.Count;
                            geoDic[geoKey].deviceAuthList = geoDic[geoKey].deviceAuthList.Union(resultRow.deviceAuthList).ToList();
                            geoDic[geoKey].deviceAuth = geoDic[geoKey].deviceAuthList.Count;
                            geoDic[geoKey].deviceNoAuthList = geoDic[geoKey].deviceNoAuthList.Union(resultRow.deviceNoAuthList).ToList();
                            geoDic[geoKey].deviceNoAuth = geoDic[geoKey].deviceNoAuthList.Count;
                            geoDic[geoKey].tmidList = geoDic[geoKey].tmidList.Union(resultRow.tmidList).ToList();
                            geoDic[geoKey].tmidCnt = geoDic[geoKey].tmidList.Count;
                        }
                    }
                }
                //地理資訊統計資料整理
                List<ProdAnalysisApiResultGeoExtend> geoResult = new List<ProdAnalysisApiResultGeoExtend>();
                foreach(KeyValuePair<string, ProdAnalysisApiResult> geoDicRow in geoDic)
                {
                    ProdAnalysisApiResultGeoExtend geoResultRow = new ProdAnalysisApiResultGeoExtend();
                    foreach (PropertyInfo propertyInfo in geoDicRow.Value.GetType().GetProperties())
                    {
                        var valNow = propertyInfo.GetValue(geoDicRow.Value);
                        propertyInfo.SetValue(geoResultRow, valNow);
                    }
                    geoResultRow.geoInfo = geoDicRow.Key.Split("-").First();
                    geoResult.Add(geoResultRow);
                }
                //學校產品授權資料整理
                List<SchAuthData> auth = new List<SchAuthData>();
                ////服務
                foreach(KeyValuePair<string, List<SchoolProductSumDataService>> op in serviceResultDic)
                {
                    string schIdNow = op.Key;
                    SchAuthData schAuthDataNow = auth.Where(a => a.schId.Equals(schIdNow)).FirstOrDefault();
                    if(schAuthDataNow != null)
                    {
                        schAuthDataNow.authService = op.Value;
                    }
                    else
                    {
                        SchAuthData schAuthDataNew = new SchAuthData();
                        schAuthDataNew.schId = schIdNow;
                        schAuthDataNew.authService = op.Value;
                        auth.Add(schAuthDataNew);
                    }
                }
                ////序號
                foreach (KeyValuePair<string, List<SchProdSerial>> op in serialResultDic)
                {
                    string schIdNow = op.Key;
                    SchAuthData schAuthDataNow = auth.Where(a => a.schId.Equals(schIdNow)).FirstOrDefault();
                    if (schAuthDataNow != null)
                    {
                        schAuthDataNow.authSerial = op.Value;
                    }
                    else
                    {
                        SchAuthData schAuthDataNew = new SchAuthData();
                        schAuthDataNew.schId = schIdNow;
                        schAuthDataNew.authSerial = op.Value;
                        auth.Add(schAuthDataNew);
                    }
                }

                return Ok(new { state = 200, data = result, geo = geoResult, auth });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI，{_option.Location} /prodanalysis/get-iotstics \n {ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 取得學校資訊
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [Authorize(Roles = "IES")]
        [HttpPost("get-school")]
        public async Task<IActionResult> GetSchoolInfo(JsonElement jsonElement)
        {
            try
            {
                var cosmosClient = _azureCosmos.GetCosmosClient();
                List<string> schoolIds = (jsonElement.TryGetProperty("schoolIds", out JsonElement schoolIdsJobj)) ? schoolIdsJobj.ToObject<List<string>>() : new List<string>(); //學校ID(array)
                string region = (jsonElement.TryGetProperty("region", out JsonElement regionJobj)) ? regionJobj.GetString() : string.Empty; //地區
                string province = (jsonElement.TryGetProperty("province", out JsonElement provinceJobj)) ? provinceJobj.GetString() : string.Empty; //省
                string city = (jsonElement.TryGetProperty("city", out JsonElement cityJobj)) ? cityJobj.GetString() : string.Empty; //市
                string dist = (jsonElement.TryGetProperty("dist", out JsonElement distJobj)) ? distJobj.GetString() : string.Empty; //區
                List<string> areaIds = (jsonElement.TryGetProperty("areaIds", out JsonElement areaIdsJobj)) ? areaIdsJobj.ToObject<List<string>>() : new List<string>(); //學區ID(array)
                bool hasVirtual = (jsonElement.TryGetProperty("virtual", out JsonElement virtualJobj)) ? virtualJobj.GetBoolean() : true; //是否取得虛擬學校
                //國際站欄位調整
                if(_option.Location.Contains("Global"))
                {
                    if(string.IsNullOrWhiteSpace(region) && !string.IsNullOrWhiteSpace(province))
                    {
                        region = province;
                        province = string.Empty;
                        dist = string.Empty;
                    }
                }
                //地區特殊字去除
                comeRemoveStr.ForEach(c => { region = region.Replace(c, ""); });
                comeRemoveStr.ForEach(c => { province = province.Replace(c, ""); });
                if (!_option.Location.Contains("Global")) comeRemoveStr.ForEach(c => { city = city.Replace(c, ""); }); //國際站不去除「市」的特殊字元，以區分縣市
                comeRemoveStr.ForEach(c => { dist = dist.Replace(c, ""); });

                //CosmosDB資料取得
                List<SimpleSchoolInfo> result = new List<SimpleSchoolInfo>();
                ///檢索條件全無對策
                if(schoolIds.Count.Equals(0) && string.IsNullOrWhiteSpace(region) && string.IsNullOrWhiteSpace(province) && string.IsNullOrWhiteSpace(city) && string.IsNullOrWhiteSpace(dist) && areaIds.Count.Equals(0))
                {
                    return Ok(new { state = 200, data = result });
                }
                string schIdListStr = JsonSerializer.Serialize(schoolIds);
                string areaIdsListStr = JsonSerializer.Serialize(areaIds);
                string Sql = $"SELECT c.id, c.name, c.region, c.province, c.city, c.dist, c.areaId FROM c WHERE (c.pk = 'Base' OR c.pk = 'School')";
                if (schoolIds.Count > 0) Sql += $" AND ARRAY_CONTAINS({schIdListStr}, c.id, true)";
                if (!string.IsNullOrWhiteSpace(region)) Sql += $" AND CONTAINS(c.region, '{region}')"; 
                if (!string.IsNullOrWhiteSpace(province)) Sql += $" AND CONTAINS(c.province, '{province}')";
                if (!string.IsNullOrWhiteSpace(city)) Sql += $" AND CONTAINS(c.city, '{city}')";
                if (!string.IsNullOrWhiteSpace(dist)) Sql += $" AND CONTAINS(c.dist, '{dist}')";
                if (areaIds.Count > 0) Sql += $" AND ARRAY_CONTAINS({areaIdsListStr}, c.areaId, true)";
                //實體學校
                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIteratorSql(queryText: Sql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.Content);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            SimpleSchoolInfo resultRow = obj.ToObject<SimpleSchoolInfo>();
                            result.Add(resultRow);
                        }
                    }
                }
                //虛擬學校
                if (hasVirtual)
                {
                    await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIteratorSql(queryText: Sql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"VirtualBase") }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.Content);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        {
                            foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                SimpleSchoolInfo resultRow = obj.ToObject<SimpleSchoolInfo>();
                                resultRow.isvirtual = true;
                                result.Add(resultRow);
                            }
                        }
                    }
                }

                return Ok(new { state = 200, data = result });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI，{_option.Location} /prodanalysis/get-iotstics \n {ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        public List<string> comeRemoveStr = new List<string>() { "省", "市", "区", "州", "县", "旗", "盟", "自治", "地區", "區", "縣" };
        private class SimpleSchoolInfo
        {
            public string id { 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 string areaId { get; set; }
            public bool isvirtual { get; set; }
        }
        private class ProdAnalysisApiResultGeoExtend : ProdAnalysisApiResult
        {
            public string geoInfo { get; set; }
        }
        private class SchProdSerial
        {
            public string prodCode { get; set; }
            public long endDate { get; set; }
            public int deviceMax { get; set; }
            public List<SchAuthDataSerial> serials { get; set; } = new List<SchAuthDataSerial>();
        }
        private class SchAuthData {
            public string schId { get; set; }
            public List<SchProdSerial> authSerial { get; set; } = new();
            public List<SchoolProductSumDataService> authService { get; set; } = new();
        }
        private class SchAuthDataSerial
        {
            public string prodCode { get; set; }
            public string serial { get; set; }
            public int deviceBoundCnt { get; set; }
            public int clientQty { get; set; }
            public long regDate { get; set; }
            public long startDate { get; set; }
            public long endDate { get; set; }
            public int deviceMax { get; set; }
            public object aprule { get; set; }
        }
    }
}