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 = Microsoft.Azure.Cosmos.PartitionKey;
using QueryRequestOptions = Microsoft.Azure.Cosmos.QueryRequestOptions;
using static TEAMModelOS.SDK.Models.Cosmos.IotTeachingData;
using System.Text.RegularExpressions;

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,Sokrates")]
        [HttpPost("get-iotstics")]
        public async Task<IActionResult> GetIotStics(JsonElement jsonElement)
        {
            try
            {
                var cosmosClient = _azureCosmos.GetCosmosClient();
                var cosmosClientCsv2 = _azureCosmos.GetCosmosClient(name: "CoreServiceV2"); //CosmosDB CSV2
                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();
                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:區 預設值:市
                Geo geo = (jsonElement.TryGetProperty("geo", out JsonElement geoJobj)) ? geoJobj.ToObject<Geo>() : null;
                string target = (jsonElement.TryGetProperty("target", out JsonElement targetJobj)) ? (!string.IsNullOrWhiteSpace(Convert.ToString(targetJobj))) ? Convert.ToString(targetJobj).ToLower() : "school" : "school"; //統計對象 school:學校 tmid:TMID
                string extra = (jsonElement.TryGetProperty("extra", out JsonElement extraJobj)) ? (!string.IsNullOrWhiteSpace(Convert.ToString(extraJobj))) ? Convert.ToString(extraJobj).ToLower() : string.Empty : string.Empty; //額外參數 rmvNoAll:不取 noschoolid、allschool
                List<string> schoolIds = new List<string>();
                if (target.Equals("school"))
                {
                    if (!jsonElement.TryGetProperty("schoolIds", out JsonElement schoolIdsJobj)) return BadRequest();//學校ID(array)
                    schoolIds = schoolIdsJobj.ToObject<List<string>>();
                }
                List<string> tmids = (jsonElement.TryGetProperty("tmids", out JsonElement tmidsJobj)) ? tmidsJobj.ToObject<List<string>>() : new List<string>(); //TMID(array) ※target = tmid 時縮小搜尋範圍
                //起始終止日期換算
                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();

                ///輸出項定義
                //統計資料
                List<ProdAnalysisApiResult> result = new List<ProdAnalysisApiResult>();
                //地理位置別資訊
                List<ProdAnalysisApiResultGeoExtend> geoResult = new List<ProdAnalysisApiResultGeoExtend>();
                //產品授權資料整理
                List<SchAuthData> auth = new List<SchAuthData>();
            
                //CosmosDB資料取得
                ///TMID
                if (target.Equals("tmid"))
                {
                    List<string> tmidList = new List<string>();
                    string Sql = $"SELECT * FROM c WHERE c.toolType = '{prod}' AND c.dateUnit = '{dateUnit}' AND c.dateTime >= {dateTimeFromSec} AND c.dateTime <= {dateTimeToSec}";
                    if(tmids.Count > 0)
                    {
                        string tmidsSer = JsonSerializer.Serialize(tmids);
                        Sql += $" AND ARRAY_CONTAINS({tmidsSer}, c.tmid) ";
                    }
                    await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Teacher").GetItemQueryStreamIteratorSql(queryText: Sql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"TmidAnalysis") }))
                    {
                        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; //無學習型態數值須扣除未上課
                            //各TMID IOT統計
                            result.Add(resultRow);
                            string tmid = obj.GetProperty("tmid").GetString();
                            if (!tmidList.Contains(tmid)) tmidList.Add(tmid);
                        }
                    }
                    ////取得TMID基本資訊 => 記入Dictionary
                    Dictionary<string, string> tmidDic = new();
                    string tmidsStr = JsonSerializer.Serialize(tmidList);
                    string SqlTmid = $"SELECT c.id, c.name FROM c WHERE ARRAY_CONTAINS({tmidsStr}, c.id) ";
                    await foreach (var item in cosmosClientCsv2.GetContainer("Core", "ID2").GetItemQueryStreamIteratorSql(SqlTmid, 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())
                            {
                                string tmid = obj.GetProperty("id").GetString();
                                string name = obj.GetProperty("name").GetString();
                                tmidDic.Add(tmid, name);
                            }
                        }
                    }
                    ////取得TMID歸戶學校ID => 記入Dictionary
                    Dictionary<string, string> tmidSchidDic = new();
                    List<string> sqlSchIds = new List<string>(); //取得學校資訊用ID列表
                    SqlTmid = $"SELECT c.id, c.schoolCode, c.schoolCodeW FROM c WHERE ARRAY_CONTAINS({tmidsStr}, c.id) ";
                    await foreach (var item in cosmosClientCsv2.GetContainer("Core", "ID2").GetItemQueryStreamIteratorSql(SqlTmid, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("base-ex") }))
                    {
                        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())
                            {
                                string tmid = obj.GetProperty("id").ToString();
                                string schoolId = string.Empty;
                                var schoolCode = (obj.TryGetProperty("schoolCode", out JsonElement _schoolCode)) ? Convert.ToString(_schoolCode) : string.Empty;
                                var schoolCodeW = (obj.TryGetProperty("schoolCodeW", out JsonElement _schoolCodeW)) ? Convert.ToString(_schoolCodeW) : string.Empty;
                                if (!string.IsNullOrWhiteSpace(schoolCode)) schoolId = schoolCode;
                                else if (!string.IsNullOrWhiteSpace(schoolCodeW)) schoolId = schoolCodeW;
                                if (!string.IsNullOrWhiteSpace(schoolId))
                                {
                                    tmidSchidDic.Add(tmid, schoolId);
                                    if (!sqlSchIds.Contains(schoolId))
                                    {
                                        sqlSchIds.Add(schoolId);
                                    }
                                }
                            }
                        }
                    }
                    ////取得TMID歸戶學校資料 => 記入Dictionary
                    Dictionary<string, Dictionary<string, string>> schDic = new();
                    string schIdListStr = JsonSerializer.Serialize(sqlSchIds);
                    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') 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);
                            }
                        }
                    }

                    //資料整理
                    foreach (ProdAnalysisApiResult resultRow in result)
                    {
                        //記入TMID資料
                        if(tmidDic.ContainsKey(resultRow.tmid))
                        {
                            resultRow.tmidInfo.name = tmidDic[resultRow.tmid];
                        }
                        //記入學校資料
                        if(tmidSchidDic.ContainsKey(resultRow.tmid))
                        {
                            resultRow.schoolId = tmidSchidDic[resultRow.tmid];
                            if (!string.IsNullOrWhiteSpace(resultRow.schoolId) && 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"];
                            }
                        }
                    }
                }
                ///學校
                else if(target.Equals("school"))
                {
                    ////取得學校基本資訊 => 記入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
                    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>>();
                    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)
                    {
                    }
                    //軟體
                    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" }; //要加算統計的欄位名
                    Dictionary<string, ProdAnalysisApiResult> geoDic = new Dictionary<string, ProdAnalysisApiResult>(); //各地理資訊統計資料
                    Dictionary<string, List<string>> geoSchDic = new Dictionary<string, List<string>>(); //地理ID->學校ID 字典
                    string Sql = $"SELECT * FROM c WHERE c.toolType = '{prod}' AND c.dateUnit = '{dateUnit}' AND c.dateTime >= {dateTimeFromSec} AND c.dateTime <= {dateTimeToSec}";
                    if(!extra.Equals("rmvnoall"))
                    {
                        schoolIds.Add("noschoolid");
                        schoolIds.Add("allschool");
                    }
                    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 geoId = string.Empty;
                            switch (geoUnit)
                            {
                                case "region":
                                    geoId = resultRow.school.region;
                                    break;
                                case "province":
                                    geoId = resultRow.school.region + resultRow.school.province;
                                    break;
                                case "city":
                                    geoId = resultRow.school.region + resultRow.school.province + resultRow.school.city;
                                    break;
                                case "dist":
                                    geoId = resultRow.school.region + resultRow.school.province + resultRow.school.city + resultRow.school.dist;
                                    break;
                            }
                            if (!string.IsNullOrWhiteSpace(geoId))
                            {
                                //統計資料
                                string geoKey = $"{geoId}-{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;
                                //地理ID->學校ID 字典
                                if (!geoSchDic.ContainsKey(geoId)) geoSchDic.Add(geoId, new List<string>());
                                if (!geoSchDic[geoId].Contains(resultRow.schoolId)) geoSchDic[geoId].Add(resultRow.schoolId);
                            }
                        }
                    }
                    //地理資訊統計資料整理
                    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();
                        if (geoSchDic.ContainsKey(geoResultRow.geoInfo)) geoResultRow.schoolIds = geoSchDic[geoResultRow.geoInfo];
                        geoResult.Add(geoResultRow);
                    }
                    
                    ////服務
                    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);
                        }
                    }
                }
                ///地理資訊
                else if (target.Equals("geo"))
                {
                    ////取得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" }; //要加算統計的欄位名
                    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(!string.IsNullOrWhiteSpace(geo.countryId))
                    {
                        Sql += $" AND c.geo.countryId = '{geo.countryId}' ";
                    }
                    if (!string.IsNullOrWhiteSpace(geo.provinceId))
                    {
                        string provinceId = (Regex.IsMatch(geo.provinceId, "^\\d{6}$")) ? geo.provinceId.Substring(0, 2) : geo.provinceId;
                        Sql += $" AND c.geo.provinceId = '{provinceId}' ";
                    }
                    if (!string.IsNullOrWhiteSpace(geo.cityId))
                    {
                        Sql += $" AND c.geo.cityId = '{geo.cityId}' ";
                    }
                    if (!string.IsNullOrWhiteSpace(geo.distId))
                    {
                        Sql += $" AND c.geo.distId = '{geo.distId}' ";
                    }
                    await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", "Common").GetItemQueryStreamIteratorSql(queryText: Sql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"GeoAnalysis") }))
                    {
                        var json = await JsonDocument.ParseAsync(item.Content);
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            GeoAnalysisCosmos resultRow = obj.ToObject<GeoAnalysisCosmos>();
                            resultRow.lTypeNone = ((resultRow.lTypeNone - resultRow.lessonLeng0) < 0) ? 0 : resultRow.lTypeNone - resultRow.lessonLeng0; //無學習型態數值須扣除未上課
                            ProdAnalysisApiResultGeoExtend geoResultRow = new ProdAnalysisApiResultGeoExtend();
                            geoResultRow.geoId = resultRow.geoId;
                            geoResultRow.geo = resultRow.geo;
                            geoResultRow.geoInfo = $"{resultRow.geo.countryName}" + $"{resultRow.geo.provinceName}" + $"{resultRow.geo.cityName}";
                            geoResultRow.dateUnit = resultRow.dateUnit;
                            geoResultRow.date = resultRow.date;
                            geoResultRow.year = resultRow.year;
                            geoResultRow.month = resultRow.month;
                            geoResultRow.day = resultRow.day;
                            geoResultRow.interact = resultRow.interact;
                            geoResultRow.item = resultRow.item;
                            geoResultRow.lTypeCoop = resultRow.lTypeCoop;
                            geoResultRow.lTypeDif = resultRow.lTypeDif;
                            geoResultRow.lTypeIact = resultRow.lTypeIact;
                            geoResultRow.lTypeMis = resultRow.lTypeMis;
                            geoResultRow.lTypeNone = resultRow.lTypeNone;
                            geoResultRow.lTypeTst = resultRow.lTypeTst;
                            geoResultRow.learnCoop = resultRow.learnCoop;
                            geoResultRow.learnParticipation = resultRow.learnParticipation;
                            geoResultRow.learnParticipationCnt = resultRow.learnParticipationCnt;
                            geoResultRow.learnParticipationT = resultRow.learnParticipationT;
                            geoResultRow.learnPeer = resultRow.learnPeer;
                            geoResultRow.lessonCnt928 = resultRow.lessonCnt928;
                            geoResultRow.lessonCntDevice = resultRow.lessonCntDevice;
                            geoResultRow.lessonCntId = resultRow.lessonCntId;
                            geoResultRow.lessonCntIdDevice = resultRow.lessonCntIdDevice;
                            geoResultRow.lessonLeng0 = resultRow.lessonLeng0;
                            geoResultRow.lessonLengMin = resultRow.lessonLengMin;
                            geoResultRow.lessonRecord = resultRow.lessonRecord;
                            geoResultRow.mission = resultRow.mission;
                            geoResultRow.missionFin = resultRow.missionFin;
                            geoResultRow.month = resultRow.month;
                            geoResultRow.peerAct = resultRow.peerAct;
                            geoResultRow.peerStuParticipationT = resultRow.peerStuParticipationT;
                            geoResultRow.sendSok = resultRow.sendSok;
                            geoResultRow.stuLessonLengMin = resultRow.stuLessonLengMin;
                            geoResultRow.stuShow = resultRow.stuShow;
                            geoResultRow.tGreen = resultRow.tGreen;
                            geoResultRow.tLesson = resultRow.tLesson;
                            geoResultRow.tmidCnt = resultRow.tmidCnt;
                            geoResultRow.tmidList = resultRow.tmidList;
                            geoResultRow.toolType = resultRow.toolType;
                            geoResultRow.useClouDAS = resultRow.useClouDAS;
                            geoResultRow.useDeviceIrs = resultRow.useDeviceIrs;
                            geoResultRow.useExcelTest = resultRow.useExcelTest;
                            geoResultRow.useGPT = resultRow.useGPT;
                            geoResultRow.useHaboard = resultRow.useHaboard;
                            geoResultRow.useHita = resultRow.useHita;
                            geoResultRow.useIES = resultRow.useIES;
                            geoResultRow.useIES5Resource = resultRow.useIES5Resource;
                            geoResultRow.useIes5Test = resultRow.useIes5Test;
                            geoResultRow.useMiniMode = resultRow.useMiniMode;
                            geoResultRow.usePaperTest = resultRow.usePaperTest;
                            geoResultRow.useScoreBoard = resultRow.useScoreBoard;
                            geoResultRow.useTransMode = resultRow.useTransMode;
                            geoResultRow.useWebIrs = resultRow.useWebIrs;
                            geoResultRow.useWordCloud = resultRow.useWordCloud;
                            geoResultRow.verList = resultRow.verList;
                            geoResult.Add(geoResultRow);
                        }
                    }
                }

                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();
                var cosmosClientCsv2 = _azureCosmos.GetCosmosClient(name: "CoreServiceV2"); //CosmosDB CSV2
                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-school \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; }
            public string geoId { get; set; }
            public Geo geo { get; set; }
            public List<string> schoolIds { get; set; } = new();
        }
        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; }
        }
    }
}