TEAMModelOS/TEAMModelOS.SDK/Models/Service/BI/BIProdAnalysis.cs

using Azure.Cosmos;
using DocumentFormat.OpenXml.Office2010.Excel;
using MathNet.Numerics;
using Microsoft.OData.Edm;
using Newtonsoft.Json;
using OpenXmlPowerTools;
using StackExchange.Redis;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.Json;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Extension;
using TEAMModelOS.SDK.Models.Cosmos;
using TEAMModelOS.SDK.Models.Cosmos.BI.BISchool;

namespace TEAMModelOS.SDK.Models.Service.BI
{
    public static class BIProdAnalysis
    {
        /// <summary>
        /// 取得某日CS IOT 資料並生成IES5各校產品分析統計資料
        /// </summary>
        /// <param name="_azureRedis"></param>
        /// <param name="_dingDing"></param>
        /// <param name="y">年</param>
        /// <param name="m">月(2位數)</param>
        /// <param name="d">日(2位數)</param>
        /// <returns></returns>
        public static async Task BICreatDailyAnalData(AzureRedisFactory _azureRedis, CosmosClient _azureCosmosClient, CosmosClient _azureCosmosClientCsv1, DingDing _dingDing, string y, string m, string d)
        {
            try
            {
                var redisClinet2 = _azureRedis.GetRedisClient(2);
                var redisClinet8 = _azureRedis.GetRedisClient(8);
                var datetime = DateTimeOffset.UtcNow;
                var ynow = datetime.Year;
                List<string> calPropList = new List<string>() { "lessonRecord", "useIES", "useIES5Resource", "useWebIrs", "useDeviceIrs", "useHaboard", "useHita", "lessonLengMin", "stuShow", "stuLessonLengMin", "tGreen", "lTypeCoop", "lTypeIact", "lTypeMis", "lTypeTst", "lTypeDif", "lTypeNone", "lessonCnt928", "lessonCntId", "lessonCntDevice", "lessonCntIdDevice", "mission", "missionFin", "item", "interact" }; //要計算的ProdAnalysis欄位列表
                //取得CS Redis TeachingData (IOT紀錄只有三個月分)
                List<IotTeachingData> IotTeachingDataList = new List<IotTeachingData>();
                if (y.Equals(ynow.ToString()))
                {
                    bool TeachingDataExist = await redisClinet2.KeyExistsAsync($"TeachingData:{m}{d}");
                    if (TeachingDataExist)
                    {
                        RedisValue[] TeachingData = redisClinet2.ListRange($"TeachingData:{m}{d}");
                        foreach (RedisValue tdataRow in TeachingData)
                        {
                            string[] tdata = tdataRow.ToString().Split(',');
                            IotTeachingData IotTeachingData = new IotTeachingData();
                            IotTeachingData.timestamp = Convert.ToInt64(tdata[0]);
                            IotTeachingData.deviceId = tdata[1];
                            IotTeachingData.channel = tdata[2];
                            IotTeachingData.tmid = tdata[3];
                            IotTeachingData.schoolId = tdata[4];
                            IotTeachingData.useIES = tdata[5];
                            IotTeachingData.useIES5Resource = (!string.IsNullOrWhiteSpace(tdata[6])) ? Convert.ToInt32(tdata[6]) : 0;
                            IotTeachingData.useWebIrs = tdata[7];
                            IotTeachingData.useDeviceIrs = tdata[8];
                            IotTeachingData.useHaboard = tdata[9];
                            IotTeachingData.useHita = tdata[10];
                            IotTeachingData.lessonLengMin = (!string.IsNullOrWhiteSpace(tdata[11])) ? Convert.ToInt32(tdata[11]) : 0;
                            IotTeachingData.stuShow = (!string.IsNullOrWhiteSpace(tdata[12])) ? Convert.ToInt32(tdata[12]) : 0;
                            IotTeachingData.tPoint = (!string.IsNullOrWhiteSpace(tdata[13])) ? Convert.ToInt32(tdata[13]) : 0;
                            IotTeachingData.lTypeCoop = tdata[14];
                            IotTeachingData.lTypeIact = tdata[15];
                            IotTeachingData.lTypeMis = tdata[16];
                            IotTeachingData.lTypeTst = tdata[17];
                            IotTeachingData.lTypeDif = tdata[18];
                            IotTeachingData.authType = tdata[19];
                            IotTeachingData.mission = (!string.IsNullOrWhiteSpace(tdata[20])) ? Convert.ToInt32(tdata[20]) : 0;
                            IotTeachingData.missionFin = (!string.IsNullOrWhiteSpace(tdata[21])) ? Convert.ToInt32(tdata[21]) : 0;
                            IotTeachingData.item = (!string.IsNullOrWhiteSpace(tdata[22])) ? Convert.ToInt32(tdata[22]) : 0;
                            IotTeachingData.interact = (!string.IsNullOrWhiteSpace(tdata[23])) ? Convert.ToInt32(tdata[23]) : 0;
                            IotTeachingData.ip = (tdata.Length > 24) ? tdata[24] : "";
                            IotTeachingData.version = (tdata.Length > 25) ? tdata[25] : "";
                            IotTeachingDataList.Add(IotTeachingData);
                        }
                    }
                }
                //生成各校每日產品分析數據
                //1.生成 Redis ProdAnalysis:Month
                //2.生成 CosmosDB Month
                //3.生成 CosmosDB 系統所有學校數值加總
                List<string> crtVirtualSchoolId = new List<string>(); //虛擬學校創建ID列表
                List<ProdAnalysis> ProdAnalysisList = new List<ProdAnalysis>();
                if (IotTeachingDataList.Count > 0)
                {
                    foreach (IotTeachingData IotTeachingDatRow in IotTeachingDataList)
                    {
                        string schoolId = (!string.IsNullOrWhiteSpace(IotTeachingDatRow.schoolId)) ? IotTeachingDatRow.schoolId.ToLower() : "noschoolid"; //noschoolid:無任何學校ID
                        if(!schoolId.Equals("noschoolid") && !schoolId.Equals("allschool") && !crtVirtualSchoolId.Contains(schoolId))
                        {
                            crtVirtualSchoolId.Add(schoolId);
                        }
                        string toolType = string.Empty;
                        if (!string.IsNullOrWhiteSpace(IotTeachingDatRow.deviceId))
                        {
                            if (IotTeachingDatRow.deviceId.Contains("HiTeachCC-")) toolType = "HiTeachCC";
                            else if (IotTeachingDatRow.deviceId.Contains("HiTeach-")) toolType = "HiTeach";
                            else if (IotTeachingDatRow.deviceId.Contains("HiTA-")) toolType = "HiTA";
                        }
                        //學校數據生成
                        if (!string.IsNullOrWhiteSpace(schoolId) && !string.IsNullOrWhiteSpace(toolType))
                        {
                            bool addFlg = false;
                            ProdAnalysis prodAnalysisRow = ProdAnalysisList.Where(s => s.schoolId.Equals(schoolId) && s.toolType.Equals(toolType)).FirstOrDefault();
                            //無此校數據=>創建
                            if (prodAnalysisRow == null)
                            {
                                prodAnalysisRow = new ProdAnalysis();
                                prodAnalysisRow.schoolId = schoolId;
                                prodAnalysisRow.toolType = toolType;
                                addFlg = true;
                            }
                            //欄位加總
                            if (!string.IsNullOrWhiteSpace(IotTeachingDatRow.deviceId) && !prodAnalysisRow.deviceList.Contains(IotTeachingDatRow.deviceId))
                            {
                                prodAnalysisRow.deviceList.Add(IotTeachingDatRow.deviceId);
                            }
                            prodAnalysisRow.deviceCnt = prodAnalysisRow.deviceList.Count;
                            switch (IotTeachingDatRow.authType)
                            {
                                case "0": //928授權
                                    if (!prodAnalysisRow.deviceNoAuthList.Contains(IotTeachingDatRow.deviceId)) prodAnalysisRow.deviceNoAuthList.Add(IotTeachingDatRow.deviceId);
                                    prodAnalysisRow.lessonCnt928++;
                                    break;

                                case "1": //ID授權
                                    if (!prodAnalysisRow.deviceNoAuthList.Contains(IotTeachingDatRow.deviceId)) prodAnalysisRow.deviceNoAuthList.Add(IotTeachingDatRow.deviceId);
                                    prodAnalysisRow.lessonCntId++;
                                    break;

                                case "2": //機器授權
                                    if (!prodAnalysisRow.deviceAuthList.Contains(IotTeachingDatRow.deviceId)) prodAnalysisRow.deviceAuthList.Add(IotTeachingDatRow.deviceId);
                                    prodAnalysisRow.lessonCntDevice++;
                                    break;

                                case "3": //ID+機器授權
                                    if (!prodAnalysisRow.deviceAuthList.Contains(IotTeachingDatRow.deviceId)) prodAnalysisRow.deviceAuthList.Add(IotTeachingDatRow.deviceId);
                                    prodAnalysisRow.lessonCntIdDevice++;
                                    break;
                            }
                            prodAnalysisRow.deviceNoAuth = prodAnalysisRow.deviceNoAuthList.Count;
                            prodAnalysisRow.deviceAuth = prodAnalysisRow.deviceAuthList.Count;
                            if (!string.IsNullOrWhiteSpace(IotTeachingDatRow.tmid) && !prodAnalysisRow.tmidList.Contains(IotTeachingDatRow.tmid))
                            {
                                prodAnalysisRow.tmidList.Add(IotTeachingDatRow.tmid);
                            }
                            prodAnalysisRow.tmidCnt = prodAnalysisRow.tmidList.Count;
                            prodAnalysisRow.lessonRecord++;
                            if (IotTeachingDatRow.useIES.Equals("1")) prodAnalysisRow.useIES++;
                            prodAnalysisRow.useIES5Resource += IotTeachingDatRow.useIES5Resource;
                            if (IotTeachingDatRow.useWebIrs.Equals("1")) prodAnalysisRow.useWebIrs++;
                            if (IotTeachingDatRow.useDeviceIrs.Equals("1")) prodAnalysisRow.useDeviceIrs++;
                            if (IotTeachingDatRow.useHaboard.Equals("1")) prodAnalysisRow.useHaboard++;
                            if (IotTeachingDatRow.useHita.Equals("1")) prodAnalysisRow.useHita++;
                            prodAnalysisRow.lessonLengMin += IotTeachingDatRow.lessonLengMin;
                            prodAnalysisRow.stuShow += IotTeachingDatRow.stuShow;
                            prodAnalysisRow.stuLessonLengMin += IotTeachingDatRow.lessonLengMin * IotTeachingDatRow.stuShow;
                            if (IotTeachingDatRow.tPoint >= 70) prodAnalysisRow.tGreen++;
                            if (IotTeachingDatRow.lTypeCoop.Equals("1")) prodAnalysisRow.lTypeCoop++;
                            if (IotTeachingDatRow.lTypeIact.Equals("1")) prodAnalysisRow.lTypeIact++;
                            if (IotTeachingDatRow.lTypeMis.Equals("1")) prodAnalysisRow.lTypeMis++;
                            if (IotTeachingDatRow.lTypeTst.Equals("1")) prodAnalysisRow.lTypeTst++;
                            if (IotTeachingDatRow.lTypeDif.Equals("1")) prodAnalysisRow.lTypeDif++;
                            if (IotTeachingDatRow.lTypeCoop.Equals("0") && IotTeachingDatRow.lTypeIact.Equals("0") && IotTeachingDatRow.lTypeMis.Equals("0") && IotTeachingDatRow.lTypeTst.Equals("0") && IotTeachingDatRow.lTypeDif.Equals("0")) prodAnalysisRow.lTypeNone++;
                            prodAnalysisRow.mission += IotTeachingDatRow.mission;
                            prodAnalysisRow.missionFin += IotTeachingDatRow.missionFin;
                            prodAnalysisRow.item += IotTeachingDatRow.item;
                            prodAnalysisRow.interact += IotTeachingDatRow.interact;
                            if (addFlg)
                            {
                                ProdAnalysisList.Add(prodAnalysisRow);
                            }
                        }
                    }
                }
                //1.記入IES5 Redis ProdAnalysis:Day
                //2.記入IES5 CosmosDB Day
                if (ProdAnalysisList.Count > 0)
                {
                    //資料整形
                    Dictionary<string, Dictionary<string, string>> redisSchFieldDic = new Dictionary<string, Dictionary<string, string>>(); //架構: key => schId => schJsonContent
                    List<ProdAnalysisCosmos> cosmosSchList = new List<ProdAnalysisCosmos>();
                    Dictionary <string,ProdAnalysisCosmos> cosmosAllSchSumDayDic = new Dictionary<string, ProdAnalysisCosmos>();
                    foreach (ProdAnalysis prodAnalysisRow in ProdAnalysisList)
                    {
                        //Redis整形
                        string toolType = prodAnalysisRow.toolType;
                        string schoolId = prodAnalysisRow.schoolId;
                        string hkey = $"ProdAnalysis:Day:{toolType}:{y}{m}{d}";
                        string fieldContent = prodAnalysisRow.ToJsonString();
                        if (redisSchFieldDic.ContainsKey(hkey)) redisSchFieldDic[hkey].Add(schoolId, fieldContent);
                        else redisSchFieldDic.Add(hkey, new Dictionary<string, string>() { { schoolId, fieldContent } });
                        //CosmosDB整形
                        ProdAnalysisCosmos ProdAnalysisCosmosRow = prodAnalysisRow.ToJsonString().ToObject<ProdAnalysisCosmos>();
                        ProdAnalysisCosmosRow.date = $"{y}{m}{d}";
                        ProdAnalysisCosmosRow.year = Convert.ToInt32(y, 10);
                        ProdAnalysisCosmosRow.month = Convert.ToInt32(m, 10);
                        ProdAnalysisCosmosRow.day = Convert.ToInt32(d, 10);
                        DateTimeOffset dateTime = new DateTimeOffset(ProdAnalysisCosmosRow.year, ProdAnalysisCosmosRow.month, ProdAnalysisCosmosRow.day, 0, 0, 0, TimeSpan.Zero);
                        ProdAnalysisCosmosRow.dateTime = dateTime.ToUnixTimeSeconds();
                        ProdAnalysisCosmosRow.id = $"{ProdAnalysisCosmosRow.toolType}-{ProdAnalysisCosmosRow.date}-{schoolId}";
                        ProdAnalysisCosmosRow.dateUnit = "day";
                        ProdAnalysisCosmosRow.createDate = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
                        cosmosSchList.Add(ProdAnalysisCosmosRow);
                    }
                    //記入Redis
                    if (redisSchFieldDic.Count > 0)
                    {
                        foreach (KeyValuePair<string, Dictionary<string, string>> hkeyItem in redisSchFieldDic)
                        {
                            //記入Redis
                            string hkey = hkeyItem.Key;
                            List<HashEntry> hvalList = new List<HashEntry>();
                            Dictionary<string, string> fieldDic = hkeyItem.Value;
                            foreach (KeyValuePair<string, string> schItem in fieldDic)
                            {
                                string schoolId = schItem.Key;
                                string fieldContent = schItem.Value;
                                hvalList.Add(new HashEntry($"{schoolId}", fieldContent));
                            }
                            await redisClinet8.HashSetAsync(hkey, hvalList.ToArray());
                        }
                    }
                    //記入CosmosDB
                    if (cosmosSchList.Count > 0)
                    {
                        foreach (ProdAnalysisCosmos cosmosSchRow in cosmosSchList)
                        {
                            //各校每日CosmosDB記入
                            await _azureCosmosClient.GetContainer(Constant.TEAMModelOS, "School").UpsertItemAsync<ProdAnalysisCosmos>(cosmosSchRow);
                            //所有學校數值加總 數值生成 cosmosAllSchSumDayDic -> key:toolType val:cosmosAllSchSumDay
                            string toolType = cosmosSchRow.toolType;
                            string schoolId = "allschool";
                            ProdAnalysisCosmos cosmosAllSchSumDay = new ProdAnalysisCosmos();
                            if (cosmosAllSchSumDayDic.ContainsKey(toolType)) cosmosAllSchSumDay = cosmosAllSchSumDayDic[toolType];
                            foreach (PropertyInfo propertyInfo in cosmosAllSchSumDay.GetType().GetProperties()) //累加項目
                            {
                                if (calPropList.Contains(propertyInfo.Name))
                                {
                                    var propType = propertyInfo.PropertyType;
                                    var valNow = propertyInfo.GetValue(cosmosAllSchSumDay);
                                    var valTodo = cosmosSchRow.GetType().GetProperty(propertyInfo.Name).GetValue(cosmosSchRow);
                                    if (propType.Equals(typeof(long))) propertyInfo.SetValue(cosmosAllSchSumDay, Convert.ToInt64(valNow.ToString(), 10) + Convert.ToInt64(valTodo.ToString(), 10));
                                    else propertyInfo.SetValue(cosmosAllSchSumDay, Convert.ToInt32(valNow.ToString(), 10) + Convert.ToInt32(valTodo.ToString(), 10));
                                }
                            }
                            cosmosAllSchSumDay.schoolId = schoolId;
                            cosmosAllSchSumDay.toolType = cosmosSchRow.toolType;
                            cosmosAllSchSumDay.date = cosmosSchRow.date;
                            cosmosAllSchSumDay.id = $"{cosmosAllSchSumDay.toolType}-{cosmosAllSchSumDay.date}-{schoolId}";
                            cosmosAllSchSumDay.dateUnit = "day";
                            cosmosAllSchSumDay.year = cosmosSchRow.year;
                            cosmosAllSchSumDay.month = cosmosSchRow.month;
                            cosmosAllSchSumDay.day = cosmosSchRow.day;
                            DateTimeOffset dateTime = new DateTimeOffset(cosmosAllSchSumDay.year, cosmosAllSchSumDay.month, cosmosAllSchSumDay.day, 0, 0, 0, TimeSpan.Zero);
                            cosmosAllSchSumDay.dateTime = dateTime.ToUnixTimeSeconds();
                            cosmosAllSchSumDay.deviceList = cosmosAllSchSumDay.deviceList.Union(cosmosSchRow.deviceList).ToList();
                            cosmosAllSchSumDay.deviceCnt = cosmosAllSchSumDay.deviceList.Count;
                            cosmosAllSchSumDay.deviceAuthList = cosmosAllSchSumDay.deviceAuthList.Union(cosmosSchRow.deviceAuthList).ToList();
                            cosmosAllSchSumDay.deviceAuth = cosmosAllSchSumDay.deviceAuthList.Count;
                            cosmosAllSchSumDay.deviceNoAuthList = cosmosAllSchSumDay.deviceNoAuthList.Union(cosmosSchRow.deviceNoAuthList).ToList();
                            cosmosAllSchSumDay.deviceNoAuth = cosmosAllSchSumDay.deviceNoAuthList.Count;
                            cosmosAllSchSumDay.tmidList = cosmosAllSchSumDay.tmidList.Union(cosmosSchRow.tmidList).ToList();
                            cosmosAllSchSumDay.tmidCnt = cosmosAllSchSumDay.tmidList.Count;
                            if (cosmosAllSchSumDayDic.ContainsKey(toolType)) cosmosAllSchSumDayDic[toolType] = cosmosAllSchSumDay;
                            else cosmosAllSchSumDayDic.Add(toolType, cosmosAllSchSumDay);
                        }
                        //每日所有學校數據總計CosmosDB記入
                        foreach (KeyValuePair<string, ProdAnalysisCosmos> schItem in cosmosAllSchSumDayDic)
                        {
                            string toolType = schItem.Key;
                            ProdAnalysisCosmos cosmosAllSchSumDay = schItem.Value;
                            cosmosAllSchSumDay.createDate = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
                            await _azureCosmosClient.GetContainer(Constant.TEAMModelOS, "School").UpsertItemAsync<ProdAnalysisCosmos>(cosmosAllSchSumDay);
                        }
                    }
                }

                //取 Redis 該月所有 ProdAnalysis:Day
                //1.生成 Redis ProdAnalysis:Month
                //2.生成 CosmosDB Month
                Dictionary<string, Dictionary<string, ProdAnalysis>> ProdAnalysisListMonth = new Dictionary<string, Dictionary<string, ProdAnalysis>>();
                Dictionary<string, ProdAnalysisCosmos> cosmosAllSchSumMonthDic = new Dictionary<string, ProdAnalysisCosmos>();
                List<ProdAnalysisCosmos> cosmosSchListMonth = new List<ProdAnalysisCosmos>();
                string patternD = $"ProdAnalysis:Day:HiT*:{y}{m}*";
                List<string> keysDayList = ScanRedisKeysByPattern(_azureRedis, patternD);
                if (keysDayList.Count > 0)
                {
                    foreach (string keyDay in keysDayList)
                    {
                        string[] keyItemList = keyDay.Split(':'); //ProdAnalysis:Day:HiTeach:20230326
                        string toolType = keyItemList[2];
                        string dateStrD = keyItemList[3];
                        string dateStrD_y = string.Empty;
                        string dateStrD_m = string.Empty;
                        string dateStrD_d = string.Empty;
                        Regex rgx = new Regex(@"[0-9]{8}");
                        if (rgx.IsMatch(dateStrD))
                        {
                            dateStrD_y = dateStrD.Substring(0, 4);
                            dateStrD_m = dateStrD.Substring(4, 2);
                            dateStrD_d = dateStrD.Substring(6, 2);
                        }
                        string prodAnalMonthKey = $"ProdAnalysis:Month:{toolType}:{dateStrD_y}{dateStrD_m}";
                        bool ProdAnalysisDayExist = await redisClinet8.KeyExistsAsync(keyDay);
                        if (ProdAnalysisDayExist && !string.IsNullOrWhiteSpace(dateStrD_y) && !string.IsNullOrWhiteSpace(dateStrD_m) && !string.IsNullOrWhiteSpace(dateStrD_d))
                        {
                            HashEntry[] hsetDay = redisClinet8.HashGetAll(keyDay); //某日 ProdAnalysis:Day所有學校的統計項目
                            foreach (HashEntry hset in hsetDay) {
                                string keySchId = hset.Name;
                                string valSchDataJson = hset.Value;
                                ProdAnalysis SchDataTodo = valSchDataJson.ToObject<ProdAnalysis>();
                                //Redis Month 資料生成
                                if (ProdAnalysisListMonth.ContainsKey(prodAnalMonthKey)) //月Dic已有此key => 分校累加
                                {
                                    if (ProdAnalysisListMonth[$"{prodAnalMonthKey}"].ContainsKey($"{keySchId}"))
                                    {
                                        ProdAnalysis SchDataNow = ProdAnalysisListMonth[$"{prodAnalMonthKey}"][$"{keySchId}"];
                                        foreach (PropertyInfo propertyInfo in SchDataNow.GetType().GetProperties())
                                        {
                                            if (calPropList.Contains(propertyInfo.Name))
                                            {
                                                var propType = propertyInfo.PropertyType;
                                                var valNow = propertyInfo.GetValue(SchDataNow);
                                                var valTodo = SchDataTodo.GetType().GetProperty(propertyInfo.Name).GetValue(SchDataTodo);
                                                if (propType.Equals(typeof(long))) propertyInfo.SetValue(SchDataNow, Convert.ToInt64(valNow.ToString(), 10) + Convert.ToInt64(valTodo.ToString(), 10));
                                                else propertyInfo.SetValue(SchDataNow, Convert.ToInt32(valNow.ToString(), 10) + Convert.ToInt32(valTodo.ToString(), 10));
                                            }
                                        }
                                        SchDataNow.deviceList = SchDataNow.deviceList.Union(SchDataTodo.deviceList).ToList();
                                        SchDataNow.deviceCnt = SchDataNow.deviceList.Count;
                                        SchDataNow.deviceAuthList = SchDataNow.deviceAuthList.Union(SchDataTodo.deviceAuthList).ToList();
                                        SchDataNow.deviceAuth = SchDataNow.deviceAuthList.Count;
                                        SchDataNow.deviceNoAuthList = SchDataNow.deviceNoAuthList.Union(SchDataTodo.deviceNoAuthList).ToList();
                                        SchDataNow.deviceNoAuth = SchDataNow.deviceNoAuthList.Count;
                                        SchDataNow.tmidList = SchDataNow.tmidList.Union(SchDataTodo.tmidList).ToList();
                                        SchDataNow.tmidCnt = SchDataNow.tmidList.Count;
                                    }
                                    //無此校資料 => 該校資料放入
                                    else
                                    {
                                        ProdAnalysisListMonth[$"{prodAnalMonthKey}"][$"{keySchId}"] = SchDataTodo;
                                    }
                                }
                                else //無此月資料 => 所有學校資料放入
                                {
                                    ProdAnalysisListMonth.Add(prodAnalMonthKey, new Dictionary<string, ProdAnalysis>() { { keySchId, SchDataTodo } });
                                }
                            }
                        }
                        
                    }
                }
                if (ProdAnalysisListMonth.Count > 0)
                {
                    foreach (KeyValuePair<string, Dictionary<string, ProdAnalysis>> item in ProdAnalysisListMonth)
                    {
                        string monthRedisKey = item.Key;
                        List<HashEntry> hvalList = new List<HashEntry>();
                        Dictionary<string, ProdAnalysis> monthRedisSchDIc = item.Value;
                        foreach (KeyValuePair<string, ProdAnalysis> monthRedisSchItem in monthRedisSchDIc)
                        {
                            string monthRedisSchId = monthRedisSchItem.Key;
                            ProdAnalysis monthRedisSchData = monthRedisSchItem.Value;
                            //Redis資料製作
                            hvalList.Add(new HashEntry(monthRedisSchId, monthRedisSchData.ToJsonString()));
                            //CosmosDB資料製作
                            ProdAnalysisCosmos cosmosSchRow = monthRedisSchData.ToJsonString().ToObject<ProdAnalysisCosmos>();
                            cosmosSchRow.date = $"{y}{m}";
                            cosmosSchRow.year = Convert.ToInt32(y, 10);
                            cosmosSchRow.month = Convert.ToInt32(m, 10);
                            DateTimeOffset dateTime = new DateTimeOffset(cosmosSchRow.year, cosmosSchRow.month, 1, 0, 0, 0, TimeSpan.Zero);
                            cosmosSchRow.dateTime = dateTime.ToUnixTimeSeconds();
                            cosmosSchRow.id = $"{monthRedisSchData.toolType}-{cosmosSchRow.date}-{monthRedisSchId}";
                            cosmosSchRow.dateUnit = "month";
                            cosmosSchRow.createDate = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
                            cosmosSchListMonth.Add(cosmosSchRow);
                        }
                        //記入Redis
                        await redisClinet8.HashSetAsync($"{monthRedisKey}", hvalList.ToArray());
                    }
                    //記入CosmosDB
                    if (cosmosSchListMonth.Count > 0)
                    {
                        foreach (ProdAnalysisCosmos cosmosSchRow in cosmosSchListMonth)
                        {
                            await _azureCosmosClient.GetContainer(Constant.TEAMModelOS, "School").UpsertItemAsync<ProdAnalysisCosmos>(cosmosSchRow);
                            //所有學校數值加總 數值生成 cosmosAllSchSumDayDic -> key:toolType val:cosmosAllSchSumDay
                            string toolType = cosmosSchRow.toolType;
                            string schoolId = "allschool";
                            ProdAnalysisCosmos cosmosAllSchSumMonth = new ProdAnalysisCosmos();
                            if (cosmosAllSchSumMonthDic.ContainsKey(toolType)) cosmosAllSchSumMonth = cosmosAllSchSumMonthDic[toolType];
                            foreach (PropertyInfo propertyInfo in cosmosAllSchSumMonth.GetType().GetProperties()) //累加項目
                            {
                                if (calPropList.Contains(propertyInfo.Name))
                                {
                                    var propType = propertyInfo.PropertyType;
                                    var valNow = propertyInfo.GetValue(cosmosAllSchSumMonth);
                                    var valTodo = cosmosSchRow.GetType().GetProperty(propertyInfo.Name).GetValue(cosmosSchRow);
                                    if (propType.Equals(typeof(long))) propertyInfo.SetValue(cosmosAllSchSumMonth, Convert.ToInt64(valNow.ToString(), 10) + Convert.ToInt64(valTodo.ToString(), 10));
                                    else propertyInfo.SetValue(cosmosAllSchSumMonth, Convert.ToInt32(valNow.ToString(), 10) + Convert.ToInt32(valTodo.ToString(), 10));
                                }
                            }
                            cosmosAllSchSumMonth.schoolId = schoolId;
                            cosmosAllSchSumMonth.toolType = cosmosSchRow.toolType;
                            cosmosAllSchSumMonth.date = cosmosSchRow.date;
                            cosmosAllSchSumMonth.id = $"{cosmosAllSchSumMonth.toolType}-{cosmosAllSchSumMonth.date}-{schoolId}";
                            cosmosAllSchSumMonth.dateUnit = "month";
                            cosmosAllSchSumMonth.year = cosmosSchRow.year;
                            cosmosAllSchSumMonth.month = cosmosSchRow.month;
                            DateTimeOffset dateTime = new DateTimeOffset(cosmosAllSchSumMonth.year, cosmosAllSchSumMonth.month, 1, 0, 0, 0, TimeSpan.Zero);
                            cosmosAllSchSumMonth.dateTime = dateTime.ToUnixTimeSeconds();
                            cosmosAllSchSumMonth.deviceList = cosmosAllSchSumMonth.deviceList.Union(cosmosSchRow.deviceList).ToList();
                            cosmosAllSchSumMonth.deviceCnt = cosmosAllSchSumMonth.deviceList.Count;
                            cosmosAllSchSumMonth.deviceAuthList = cosmosAllSchSumMonth.deviceAuthList.Union(cosmosSchRow.deviceAuthList).ToList();
                            cosmosAllSchSumMonth.deviceAuth = cosmosAllSchSumMonth.deviceAuthList.Count;
                            cosmosAllSchSumMonth.deviceNoAuthList = cosmosAllSchSumMonth.deviceNoAuthList.Union(cosmosSchRow.deviceNoAuthList).ToList();
                            cosmosAllSchSumMonth.deviceNoAuth = cosmosAllSchSumMonth.deviceNoAuthList.Count;
                            cosmosAllSchSumMonth.tmidList = cosmosAllSchSumMonth.tmidList.Union(cosmosSchRow.tmidList).ToList();
                            cosmosAllSchSumMonth.tmidCnt = cosmosAllSchSumMonth.tmidList.Count;
                            if (cosmosAllSchSumMonthDic.ContainsKey(toolType)) cosmosAllSchSumMonthDic[toolType] = cosmosAllSchSumMonth;
                            else cosmosAllSchSumMonthDic.Add(toolType, cosmosAllSchSumMonth);
                        }
                        //每月所有學校數據總計CosmosDB記入
                        foreach (KeyValuePair<string, ProdAnalysisCosmos> schItem in cosmosAllSchSumMonthDic)
                        {
                            string toolType = schItem.Key;
                            ProdAnalysisCosmos cosmosAllSchSumMonth = schItem.Value;
                            cosmosAllSchSumMonth.createDate = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
                            await _azureCosmosClient.GetContainer(Constant.TEAMModelOS, "School").UpsertItemAsync<ProdAnalysisCosmos>(cosmosAllSchSumMonth);
                        }
                    }
                }
                //取該年所有 CosmosDB 該年所有月份 生成CosmosDB年資料
                Dictionary<string, Dictionary<string, ProdAnalysisCosmos>> ProdAnalysisListYear = new Dictionary<string, Dictionary<string, ProdAnalysisCosmos>>();
                var query = $"SELECT * FROM c WHERE c.year = {y} AND c.dateUnit = 'month' AND c.schoolId != 'allschool'";
                await foreach (var itemcr in _azureCosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: query, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("ProdAnalysis") }))
                {
                    var json = await JsonDocument.ParseAsync(itemcr.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            ProdAnalysisCosmos SchDataTodo = obj.ToObject<ProdAnalysisCosmos>();
                            string toolType = SchDataTodo.toolType;
                            string schId = SchDataTodo.schoolId;
                            //年Dic已有此key => 分校累加
                            if (ProdAnalysisListYear.ContainsKey(toolType))
                            {
                                if (ProdAnalysisListYear[$"{toolType}"].ContainsKey($"{schId}"))
                                {
                                    ProdAnalysisCosmos SchDataNow = ProdAnalysisListYear[$"{toolType}"][$"{schId}"];
                                    foreach (PropertyInfo propertyInfo in SchDataNow.GetType().GetProperties())
                                    {
                                        if (calPropList.Contains(propertyInfo.Name))
                                        {
                                            var propType = propertyInfo.PropertyType;
                                            var valNow = propertyInfo.GetValue(SchDataNow);
                                            var valTodo = SchDataTodo.GetType().GetProperty(propertyInfo.Name).GetValue(SchDataTodo);
                                            if (propType.Equals(typeof(long))) propertyInfo.SetValue(SchDataNow, Convert.ToInt64(valNow.ToString(), 10) + Convert.ToInt64(valTodo.ToString(), 10));
                                            else propertyInfo.SetValue(SchDataNow, Convert.ToInt32(valNow.ToString(), 10) + Convert.ToInt32(valTodo.ToString(), 10));
                                        }
                                    }
                                    SchDataNow.deviceList = SchDataNow.deviceList.Union(SchDataTodo.deviceList).ToList();
                                    SchDataNow.deviceCnt = SchDataNow.deviceList.Count;
                                    SchDataNow.deviceAuthList = SchDataNow.deviceAuthList.Union(SchDataTodo.deviceAuthList).ToList();
                                    SchDataNow.deviceAuth = SchDataNow.deviceAuthList.Count;
                                    SchDataNow.deviceNoAuthList = SchDataNow.deviceNoAuthList.Union(SchDataTodo.deviceNoAuthList).ToList();
                                    SchDataNow.deviceNoAuth = SchDataNow.deviceNoAuthList.Count;
                                    SchDataNow.tmidList = SchDataNow.tmidList.Union(SchDataTodo.tmidList).ToList();
                                    SchDataNow.tmidCnt = SchDataNow.tmidList.Count;
                                }
                                //無此校資料 => 該校資料放入
                                else
                                {
                                    ProdAnalysisCosmos SchDataNow = SchDataTodo;
                                    SchDataNow.date = $"{y}";
                                    SchDataNow.year = Convert.ToInt32(y, 10);
                                    SchDataNow.month = 0;
                                    DateTimeOffset dateTime = new DateTimeOffset(SchDataNow.year, 1, 1, 0, 0, 0, TimeSpan.Zero);
                                    SchDataNow.dateTime = dateTime.ToUnixTimeSeconds();
                                    SchDataNow.id = $"{toolType}-{y}-{schId}";
                                    SchDataNow.dateUnit = "year";
                                    ProdAnalysisListYear[$"{toolType}"][$"{schId}"] = SchDataNow;
                                }
                            }
                            //無此年資料 => 所有學校資料放入
                            else
                            {
                                ProdAnalysisCosmos SchDataNow = SchDataTodo;
                                SchDataNow.date = $"{y}";
                                SchDataNow.year = Convert.ToInt32(y, 10);
                                SchDataNow.month = 0;
                                DateTimeOffset dateTime = new DateTimeOffset(SchDataNow.year, 1, 1, 0, 0, 0, TimeSpan.Zero);
                                SchDataNow.dateTime = dateTime.ToUnixTimeSeconds();
                                SchDataNow.id = $"{toolType}-{y}-{schId}";
                                SchDataNow.dateUnit = "year";
                                ProdAnalysisListYear.Add(toolType, new Dictionary<string, ProdAnalysisCosmos>() { { schId, SchDataNow } });
                            }
                        }
                    }
                }
                if (ProdAnalysisListYear.Count > 0)
                {
                    foreach (KeyValuePair<string, Dictionary<string, ProdAnalysisCosmos>> item in ProdAnalysisListYear)
                    {
                        string toolType = item.Key;
                        ProdAnalysisCosmos SchDataNow = new ProdAnalysisCosmos(); //當年某產品所有學校總和
                        Dictionary<string, ProdAnalysisCosmos> yearCosmosSchDIc = item.Value;
                        foreach (KeyValuePair<string, ProdAnalysisCosmos> yearCosmosSchItem in yearCosmosSchDIc)
                        {
                            string schId = yearCosmosSchItem.Key;
                            ProdAnalysisCosmos yearCosmosSchData = yearCosmosSchItem.Value;
                            await _azureCosmosClient.GetContainer(Constant.TEAMModelOS, "School").UpsertItemAsync<ProdAnalysisCosmos>(yearCosmosSchData);
                            //每年所有學校數據總計CosmosDB記入
                            foreach (PropertyInfo propertyInfo in SchDataNow.GetType().GetProperties())
                            {
                                if (calPropList.Contains(propertyInfo.Name))
                                {
                                    var propType = propertyInfo.PropertyType;
                                    var valNow = propertyInfo.GetValue(SchDataNow);
                                    var valTodo = yearCosmosSchData.GetType().GetProperty(propertyInfo.Name).GetValue(yearCosmosSchData);
                                    if (propType.Equals(typeof(long))) propertyInfo.SetValue(SchDataNow, Convert.ToInt64(valNow.ToString(), 10) + Convert.ToInt64(valTodo.ToString(), 10));
                                    else propertyInfo.SetValue(SchDataNow, Convert.ToInt32(valNow.ToString(), 10) + Convert.ToInt32(valTodo.ToString(), 10));
                                }

                            }
                            SchDataNow.schoolId = "allschool";
                            SchDataNow.toolType = yearCosmosSchData.toolType;
                            SchDataNow.date = yearCosmosSchData.date;
                            SchDataNow.id = $"{yearCosmosSchData.toolType}-{yearCosmosSchData.date}-allschool";
                            SchDataNow.dateUnit = "year";
                            SchDataNow.year = yearCosmosSchData.year;
                            SchDataNow.month = 0;
                            DateTimeOffset dateTime = new DateTimeOffset(SchDataNow.year, 1, 1, 0, 0, 0, TimeSpan.Zero);
                            SchDataNow.dateTime = dateTime.ToUnixTimeSeconds();
                            SchDataNow.deviceList = SchDataNow.deviceList.Union(yearCosmosSchData.deviceList).ToList();
                            SchDataNow.deviceCnt = SchDataNow.deviceList.Count;
                            SchDataNow.deviceAuthList = SchDataNow.deviceAuthList.Union(yearCosmosSchData.deviceAuthList).ToList();
                            SchDataNow.deviceAuth = SchDataNow.deviceAuthList.Count;
                            SchDataNow.deviceNoAuthList = SchDataNow.deviceNoAuthList.Union(yearCosmosSchData.deviceNoAuthList).ToList();
                            SchDataNow.deviceNoAuth = SchDataNow.deviceNoAuthList.Count;
                            SchDataNow.tmidList = SchDataNow.tmidList.Union(yearCosmosSchData.tmidList).ToList();
                            SchDataNow.tmidCnt = SchDataNow.tmidList.Count;
                        }
                        await _azureCosmosClient.GetContainer(Constant.TEAMModelOS, "School").UpsertItemAsync<ProdAnalysisCosmos>(SchDataNow);
                    }
                }

                //虛擬學校創建
                if (crtVirtualSchoolId.Count > 0)
                {
                    //取得IES5 school Base
                    string schIdListStr = JsonConvert.SerializeObject(crtVirtualSchoolId);
                    string schBaseQueryText = $"SELECT c.id FROM c where ARRAY_CONTAINS({schIdListStr}, c.id, true)";
                    await foreach (var itemsr in _azureCosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: schBaseQueryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base") }))
                    {
                        using var json = await JsonDocument.ParseAsync(itemsr.ContentStream);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        {
                            foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                string existSchid = obj.GetProperty("id").GetString();
                                if (crtVirtualSchoolId.Contains(existSchid))
                                {
                                    crtVirtualSchoolId.Remove(existSchid);
                                }
                            }
                        }
                    }
                    //取得IES5 school VirtualBase
                    schIdListStr = JsonConvert.SerializeObject(crtVirtualSchoolId);
                    schBaseQueryText = $"SELECT c.id FROM c where ARRAY_CONTAINS({schIdListStr}, c.id, true)";
                    await foreach (var itemsr in _azureCosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: schBaseQueryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"VirtualBase") }))
                    {
                        using var json = await JsonDocument.ParseAsync(itemsr.ContentStream);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        {
                            foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                string existSchid = obj.GetProperty("id").GetString();
                                if (crtVirtualSchoolId.Contains(existSchid))
                                {
                                    crtVirtualSchoolId.Remove(existSchid);
                                }
                            }
                        }
                    }
                    //VirtualBase school 建立
                    if(crtVirtualSchoolId.Count > 0)
                    {
                        //取得CSV1 schoolcode，存在者建立IES5 VirtualBase school
                        List<School> crtVSchoolList = new List<School>();
                        string csv1SchIdListStr = JsonConvert.SerializeObject(crtVirtualSchoolId);
                        string schCsv1QueryText = $"SELECT c.shortCode, c.name, c.countryName, c.provinceName, c.cityName, c.distName, c.address FROM c where ARRAY_CONTAINS({csv1SchIdListStr}, c.shortCode, true)";
                        await foreach (var item in _azureCosmosClientCsv1.GetContainer("Core", "SchoolCode").GetItemQueryStreamIterator(queryText: schCsv1QueryText, requestOptions: null ))
                        {
                            using 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())
                                {
                                    VirtualBase crtVSchool = new VirtualBase();
                                    crtVSchool.code = "VirtualBase";
                                    crtVSchool.id = obj.GetProperty("shortCode").GetString();
                                    crtVSchool.pk = "School";
                                    crtVSchool.schoolCode = obj.GetProperty("shortCode").GetString();
                                    crtVSchool.name = obj.GetProperty("name").GetString();
                                    crtVSchool.region = (obj.TryGetProperty("countryName", out JsonElement countryNameJ)) ? Convert.ToString(countryNameJ) : null;
                                    crtVSchool.province = (obj.TryGetProperty("provinceName", out JsonElement provinceNameJ)) ? Convert.ToString(provinceNameJ) : null;
                                    crtVSchool.city = (obj.TryGetProperty("cityName", out JsonElement cityNameJ)) ? Convert.ToString(cityNameJ) : null;
                                    crtVSchool.dist = (obj.TryGetProperty("distName", out JsonElement distNameJ)) ? Convert.ToString(distNameJ) : null;
                                    crtVSchool.address = (obj.TryGetProperty("address", out JsonElement addressJ)) ? Convert.ToString(addressJ) : null;
                                    crtVSchool.createTime = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
                                    await _azureCosmosClient.GetContainer(Constant.TEAMModelOS, "School").CreateItemAsync<VirtualBase>(crtVSchool);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _ = _dingDing.SendBotMsg($"BI,{Environment.GetEnvironmentVariable("Option:Location")},BICreatDailyAnalData() IOT產品分析資料生成錯誤\n{ex.Message}\n{ex.StackTrace}\n", GroupNames.成都开发測試群組);
            }
        }

        /// <summary>
        /// 取得Redis(8)符合搜尋模式的key
        /// </summary>
        /// <param name="pattern"></param>
        /// <returns></returns>
        public static List<string> ScanRedisKeysByPattern(AzureRedisFactory _azureRedis, string pattern)
        {
            var redisClinet8 = _azureRedis.GetRedisClient(8);
            var keys = new HashSet<RedisKey>();
            int nextCursor = 0;
            do
            {
                RedisResult redisResult = redisClinet8.Execute("SCAN", nextCursor.ToString(), "MATCH", pattern, "COUNT", "1000");
                var innerResult = (RedisResult[])redisResult;
                nextCursor = int.Parse((string)innerResult[0]);
                List<RedisKey> resultLines = ((RedisKey[])innerResult[1]).ToList();
                keys.UnionWith(resultLines);
            }
            while (nextCursor != 0);
            List<string> result = new List<string>();
            if (keys.Count > 0)
            {
                foreach (RedisKey key in keys)
                {
                    result.Add(key.ToString());
                }
            }
            return result;
        }
    }
}