TEAMModelOS/TEAMModelBI/Controllers/RepairApi/SchoolRepController.cs

using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Options;
using System.Net.Http;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK;
using TEAMModelOS.Models;
using System.Threading.Tasks;
using System.Text.Json;
using System.IO;
using TEAMModelBI.Models;
using System.Collections.Generic;
using System.Text;
using TEAMModelOS.SDK.Extension;
using System;
using TEAMModelOS.SDK.Models;
using Microsoft.Azure.Cosmos;
using System.Linq;
using TEAMModelOS.SDK.Context.Constant;
using Pipelines.Sockets.Unofficial.Arenas;
using TEAMModelBI.Tool;
using TEAMModelOS.SDK.Models.Cosmos.BI.BISchool;
using TEAMModelOS.SDK.Models.Cosmos.BI;
using TEAMModelOS.SDK.Models.Service.BI;
using TEAMModelOS.SDK.Models.Service.BIStatsWay;
using StackExchange.Redis;
using System.Text.RegularExpressions;
using TEAMModelOS.SDK.DI.IPIP;
using static TEAMModelOS.SDK.Extension.GeoRegion;

namespace TEAMModelBI.Controllers.RepairApi
{
    [Route("sccholrep")]
    [ApiController]
    public class SchoolRepController : ControllerBase
    {

        private readonly AzureCosmosFactory _azureCosmos;
        private readonly DingDing _dingDing;
        private readonly Option _option;
        private readonly AzureStorageFactory _azureStorage;
        private readonly AzureRedisFactory _azureRedis;
        private readonly IConfiguration _configuration;
        private readonly CoreAPIHttpService _coreAPIHttpService;
        private readonly IWebHostEnvironment _environment; //读取文件
        private readonly IHttpClientFactory _httpClient;
        private readonly City _city;

        public SchoolRepController(AzureCosmosFactory azureCosmos, DingDing dingDing, AzureStorageFactory azureStorage, IOptionsSnapshot<Option> option, AzureRedisFactory azureRedis, IConfiguration configuration, CoreAPIHttpService coreAPIHttpService, IHttpClientFactory httpClient, IWebHostEnvironment hostingEnvironment, City city)
        {
            _azureCosmos = azureCosmos;
            _dingDing = dingDing;
            _azureStorage = azureStorage;
            _option = option?.Value;
            _azureRedis = azureRedis;
            _configuration = configuration;
            _coreAPIHttpService = coreAPIHttpService;
            _httpClient = httpClient;
            _environment = hostingEnvironment;
            _city = city;
        }

        /// <summary>
        /// 添加新学段的学校
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("set-allscperiod")]
        public async Task<IActionResult> SetAllScPeriod(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("Language", out JsonElement Language);
            jsonElement.TryGetProperty("scId", out JsonElement scId);
            jsonElement.TryGetProperty("periodName", out JsonElement periodName);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            var builder = $"{_environment.ContentRootPath}/JsonFile/Preset/LangSchoolConfig.json";
            StreamReader streamReader = new(new FileStream(builder, FileMode.Open, FileAccess.Read, FileShare.ReadWrite), Encoding.UTF8);
            StringBuilder stringBuilder = new();
            string text;
            while ((text = streamReader.ReadLine()) != null)
            {
                stringBuilder.Append(text.ToString());
            }
            streamReader.Close();
            string input = stringBuilder.ToString();
            List<SchoolConfig> schoolConfigs = input.ToObject<List<SchoolConfig>>();
            SchoolConfig schoolConfig = null;
            string Lang = string.Empty; //語系判斷用字串
            if (!string.IsNullOrEmpty($"{Language}"))
                schoolConfig = schoolConfigs.Find(x => x.Lang.Contains($"{Language}"));
                Lang = $"{Language}";

            if (schoolConfig == null)
            {
                //CN站
                if(_option.Location.Contains("china", StringComparison.OrdinalIgnoreCase)) {
                    if ($"{Language}".Contains("en"))
                    {
                        schoolConfig = schoolConfigs.Find(x => x.Lang.Contains("en-US"));
                        Lang = "en-US";
                    }
                    else
                    {
                        schoolConfig = schoolConfigs.Find(x => x.Lang.Contains("zh-CN"));
                        Lang = "zh-CN";
                    }
                }
                //GL站
                else
                {
                    if ($"{Language}".Contains("tw", StringComparison.OrdinalIgnoreCase))
                    {
                        schoolConfig = schoolConfigs.Find(x => x.Lang.Contains("zh-TW"));
                        Lang = "zh-TW";
                    }
                    else
                    {
                        schoolConfig = schoolConfigs.Find(x => x.Lang.Contains("en-US"));
                        Lang = "en-US";
                    } 
                }
            }
            string campusId = Guid.NewGuid().ToString();

            //默認學段、通識科目、學前、语言 各語系名稱取得
            string scName = string.Empty;
            string subjectGeneral = string.Empty;
            string subjectLanage = string.Empty;
            string gradePreschool = string.Empty;
            switch (Lang)
            {
                case "zh-CN":
                    scName = "默认学段";
                    subjectGeneral = "通识";
                    subjectLanage = "语言";
                    gradePreschool = "学前";
                    break;
                case "zh-TW":
                    scName = "預設學段";
                    subjectGeneral = "通識";
                    subjectLanage = "語言";
                    gradePreschool = "學前";
                    break;
                case "en-US":
                    scName = "Default period";
                    subjectGeneral = "General Studies";
                    subjectLanage = "Language";
                    gradePreschool = "Preschool";
                    break;
            }

            StringBuilder noPeriodScSql = new("SELECT DISTINCT value(c) FROM c ");
            List<School> allSc = new();
            List<string> scidList = new(); //更新的學校ID(回傳值)

            if (!string.IsNullOrEmpty($"{scId}"))
                noPeriodScSql.Append($" where c.id='{scId}'");
            else
                noPeriodScSql.Append($" join sp in c.period where c.code = 'Base' AND ( NOT IS_DEFINED(sp.grades) OR ARRAY_LENGTH(sp.grades) = 0 OR NOT IS_DEFINED(sp.subjects) OR ARRAY_LENGTH(sp.subjects) = 0 )");

            if (!string.IsNullOrEmpty($"{periodName}"))
                scName = $"{periodName}";

            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIteratorSql<School>(queryText: "select value(c) from c where c.code='Base' and c.period=[]", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
            {
                allSc.Add(item);
            }

            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIteratorSql<School>(queryText: noPeriodScSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
            {
                allSc.Add(item);
            }

            foreach (var school in allSc)
            {
                if (school.name.Contains("幼儿园") || school.name.Contains("幼稚園") || school.name.Contains("幼兒園") || school.name.Contains("kindergarten", StringComparison.OrdinalIgnoreCase) || school.name.Contains("preschool", StringComparison.OrdinalIgnoreCase))
                {
                    List<Subject> newSubjects = new List<Subject>() {
                        new Subject { id = Guid.NewGuid().ToString(), name = subjectLanage, type = 1 }
                    };
                    if (school.period.Count > 0)
                    {
                        List<Period> grades = school.period.FindAll(sg => sg.grades.Count == 0);
                        foreach (Period grade in grades)
                        {
                            grade.grades = new List<string>() { gradePreschool };
                            grade.gradeCount = grade.grades.Count;
                        }
                        List<Period> semesters = school.period.FindAll(sg => sg.semesters.Count == 0);
                        foreach (Period semester in semesters)
                        {
                            semester.semesters = new List<Semester>() { 
                                new Semester { name = schoolConfig.semester[0].term, start = schoolConfig.semester[0].start, month = schoolConfig.semester[0].month, day = schoolConfig.semester[0].day, id = Guid.NewGuid().ToString() },
                                new Semester { name = schoolConfig.semester[1].term, start = schoolConfig.semester[1].start, month = schoolConfig.semester[1].month, day = schoolConfig.semester[1].day, id = Guid.NewGuid().ToString() }
                            };
                            semester.semesterCount = semester.semesters.Count;
                        }
                        List<Period> subjects = school.period.FindAll(sg => sg.subjects.Count == 0);
                        foreach (Period subject in subjects)
                        {
                            subject.subjects = newSubjects;
                            subject.subjectCount = subject.subjects.Count;
                        }
                    }
                    else
                    {
                        if (school.campuses.Count > 0)
                            campusId = school.campuses.Select(x => x.id).FirstOrDefault().ToString();

                        school.period.Add(new Period
                        {
                            id = Guid.NewGuid().ToString(),
                            name = scName,
                            grades = new List<string>() { gradePreschool },
                            campusId = campusId,
                            subjects = newSubjects,
                            semesters = new List<Semester>() { 
                                new Semester { name = schoolConfig.semester[0].term, start = schoolConfig.semester[0].start, month = schoolConfig.semester[0].month, day = schoolConfig.semester[0].day, id = Guid.NewGuid().ToString() },
                                new Semester { name = schoolConfig.semester[1].term, start = schoolConfig.semester[1].start, month = schoolConfig.semester[1].month, day = schoolConfig.semester[1].day, id = Guid.NewGuid().ToString() }
                            },
                            gradeCount = 1,
                            semesterCount = 2,
                            subjectCount = newSubjects.Count,
                        });

                        if (!school.campuses.Select(x => x.id).Contains(campusId))
                            school.campuses.Add(new Campus { name = school.name, id = campusId });
                    }
                }
                else
                {
                    if (school.period.Count > 0)
                    {
                        List<Period> grades = school.period.FindAll(sg => sg.grades.Count == 0);
                        foreach (Period grade in grades)
                        {
                            grade.grades = schoolConfig.grades;
                            grade.gradeCount = schoolConfig.grades.Count;
                        }
                        List<Period> semesters = school.period.FindAll(sg => sg.semesters.Count == 0);
                        foreach (Period semester in semesters)
                        {
                            semester.semesters = new List<Semester>() 
                            { 
                                new Semester { name = schoolConfig.semester[0].term, start = schoolConfig.semester[0].start, month = schoolConfig.semester[0].month, day = schoolConfig.semester[0].day, id = Guid.NewGuid().ToString() },
                                new Semester { name = schoolConfig.semester[1].term, start = schoolConfig.semester[1].start, month = schoolConfig.semester[1].month, day = schoolConfig.semester[1].day, id = Guid.NewGuid().ToString() }
                            };
                            semester.semesterCount = semester.semesters.Count;
                        }
                        List<Period> subjects = school.period.FindAll(sg => sg.subjects.Count == 0);
                        foreach (Period subject in subjects)
                        {
                            subject.subjects = (school.type.Equals(2)) ? new List<Subject>() //高教
                            {
                                new Subject { id=Guid.NewGuid().ToString(),name=subjectGeneral,type=1 }
                            } : new List<Subject>() //普教
                            {
                                new Subject { id=Guid.NewGuid().ToString(),name=schoolConfig.PresetSubject[0].name,type=schoolConfig.PresetSubject[0].type },
                                new Subject { id=Guid.NewGuid().ToString(),name=schoolConfig.PresetSubject[1].name,type=schoolConfig.PresetSubject[1].type },
                                new Subject { id=Guid.NewGuid().ToString(),name=schoolConfig.PresetSubject[2].name,type=schoolConfig.PresetSubject[2].type }
                            };
                            subject.subjectCount = subject.subjects.Count;
                        }
                    }
                    else
                    {
                        if (school.campuses.Count > 0)
                            campusId = school.campuses.Select(x => x.id).FirstOrDefault().ToString();

                        school.period.Add(new Period
                        {
                            id = Guid.NewGuid().ToString(),
                            name = scName,
                            campusId = campusId,
                            semesters = new List<Semester>() {
                                new Semester { name = schoolConfig.semester[0].term, start = schoolConfig.semester[0].start, month = schoolConfig.semester[0].month, day = schoolConfig.semester[0].day, id = Guid.NewGuid().ToString() },
                                new Semester { name = schoolConfig.semester[1].term, start = schoolConfig.semester[1].start, month = schoolConfig.semester[1].month, day = schoolConfig.semester[1].day, id = Guid.NewGuid().ToString() }
                            },
                            subjects = (school.type.Equals(2)) ? new List<Subject>() { //高教
                                new Subject { id=Guid.NewGuid().ToString(),name=subjectGeneral,type=1 },
                            } : new List<Subject>() { //普教
                                new Subject { id=Guid.NewGuid().ToString(),name=schoolConfig.PresetSubject[0].name,type=schoolConfig.PresetSubject[0].type },
                                new Subject { id=Guid.NewGuid().ToString(),name=schoolConfig.PresetSubject[1].name,type=schoolConfig.PresetSubject[1].type },
                                new Subject { id=Guid.NewGuid().ToString(),name=schoolConfig.PresetSubject[2].name,type=schoolConfig.PresetSubject[2].type }
                            },
                            grades = schoolConfig.grades,
                            analysis = new Analysis()
                            {
                                type = new List<ExamSimple>() {
                                    new ExamSimple { id = Guid.NewGuid().ToString(), name = schoolConfig.PresetExam[0].type[0].name },
                                    new ExamSimple { id = Guid.NewGuid().ToString(), name =  schoolConfig.PresetExam[0].type[1].name },
                                    new ExamSimple { id = Guid.NewGuid().ToString(), name =  schoolConfig.PresetExam[0].type[2].name },
                                    new ExamSimple { id = Guid.NewGuid().ToString(), name =  schoolConfig.PresetExam[0].type[3].name }
                                },
                                income = schoolConfig.PresetExam[0].income,
                                eugenics = schoolConfig.PresetExam[0].eugenics,
                                touch = schoolConfig.PresetExam[0].touch
                            },
                            gradeCount = schoolConfig.grades.Count,
                            semesterCount = schoolConfig.semester.Count,
                            subjectCount = (school.type.Equals(2)) ? 1 : schoolConfig.PresetSubject.Count
                        });
                        if (!school.campuses.Select(x => x.id).Contains(campusId))
                            school.campuses.Add(new Campus { name = school.name, id = campusId });
                    }
                }
                School rSchool = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReplaceItemAsync<School>(school, school.id, new PartitionKey("Base"));
                scidList.Add(rSchool.id);
            }

            return Ok(new { state = RespondCode.Ok, ids = scidList });
        }

        /// <summary>
        /// 新增学校学段（多个）
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("set-scperiod")]
        public async Task<IActionResult> SetScPeriod(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("scId", out JsonElement scId)) return BadRequest();
            if (!jsonElement.TryGetProperty("periodName", out JsonElement periodName)) return BadRequest();

            List<string> periodNames = $"{periodName}".ToObject<List<string>>();
            jsonElement.TryGetProperty("Language", out JsonElement Language);

            var cosmosClient = _azureCosmos.GetCosmosClient();
            var builder = $"{_environment.ContentRootPath}/JsonFile/Preset/LangSchoolConfig.json";
            StreamReader streamReader = new(new FileStream(builder, FileMode.Open, FileAccess.Read, FileShare.ReadWrite), Encoding.UTF8);
            StringBuilder stringBuilder = new();
            string text;
            while ((text = streamReader.ReadLine()) != null)
            {
                stringBuilder.Append(text.ToString());
            }
            streamReader.Close();
            string input = stringBuilder.ToString();
            List<SchoolConfig> schoolConfigs = input.ToObject<List<SchoolConfig>>();
            SchoolConfig schoolConfig = null;
            if (!string.IsNullOrEmpty($"{Language}"))
                schoolConfig = schoolConfigs.Find(x => x.Lang.Contains($"{Language}"));

            if (schoolConfig == null)
            {
                if ($"{Language}".Contains("en"))
                    schoolConfig = schoolConfigs.Find(x => x.Lang.Contains("en-US"));
                else
                    schoolConfig = schoolConfigs.Find(x => x.Lang.Contains("zh-CN"));
            }
            string campusId = Guid.NewGuid().ToString();
            School school = null;

            school = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemAsync<School>($"{scId}", new PartitionKey("Base"));

            if (school != null)
            {
                foreach (var item in periodNames)
                {
                    if (school.campuses.Count > 0)
                        campusId = school.campuses.Select(x => x.id).FirstOrDefault().ToString();

                    school.period.Add(new Period
                    {
                        id = Guid.NewGuid().ToString(),
                        name = item,
                        campusId = campusId,
                        semesters = new List<Semester>() { new Semester { name = schoolConfig.semester[0].term, start = schoolConfig.semester[0].start, month = schoolConfig.semester[0].month, day = schoolConfig.semester[0].day, id = Guid.NewGuid().ToString() },
                        new Semester { name = schoolConfig.semester[1].term, start = schoolConfig.semester[1].start, month = schoolConfig.semester[1].month, day = schoolConfig.semester[1].day, id = Guid.NewGuid().ToString() } },
                        subjects = new List<Subject>() {
                        new Subject { id=Guid.NewGuid().ToString(),name=schoolConfig.PresetSubject[0].name,type=schoolConfig.PresetSubject[0].type },
                        new Subject { id=Guid.NewGuid().ToString(),name=schoolConfig.PresetSubject[1].name,type=schoolConfig.PresetSubject[1].type },
                        new Subject { id=Guid.NewGuid().ToString(),name=schoolConfig.PresetSubject[2].name,type=schoolConfig.PresetSubject[2].type }
                        },
                        grades = schoolConfig.grades,
                        analysis = new Analysis()
                        {
                            type = new List<ExamSimple>() { new ExamSimple { id = Guid.NewGuid().ToString(), name = schoolConfig.PresetExam[0].type[0].name },
                        new ExamSimple { id = Guid.NewGuid().ToString(), name =  schoolConfig.PresetExam[0].type[1].name },
                        new ExamSimple { id = Guid.NewGuid().ToString(), name =  schoolConfig.PresetExam[0].type[2].name },
                        new ExamSimple { id = Guid.NewGuid().ToString(), name =  schoolConfig.PresetExam[0].type[3].name } },
                            income = schoolConfig.PresetExam[0].income,
                            eugenics = schoolConfig.PresetExam[0].eugenics,
                            touch = schoolConfig.PresetExam[0].touch
                        }
                    });
                }

                if (!school.campuses.Select(x => x.id).Contains(campusId))
                    school.campuses.Add(new Campus { name = school.name, id = campusId });
            }

            school = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReplaceItemAsync<School>(school, school.id, new PartitionKey("Base"));

            return Ok(new { state = RespondCode.Ok, school });
        }

        /// <summary>
        /// 学校信息中间件查询和修复接口
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("set-allscinfos")]
        public async Task<IActionResult> SetAllScInfos(JsonElement jsonElement)
        {
            try
            {
                jsonElement.TryGetProperty("scId", out JsonElement scId);
                var cosmosClient = _azureCosmos.GetCosmosClient();
                List<string> scIds = new();
                if (!string.IsNullOrEmpty($"{scId}"))
                    scIds.Add($"{scId}");
                else
                    scIds = await CommonFind.FindScIds(cosmosClient, "select value(c.id) from c ", "Base");

                List<BIRelation> scInfos = new();
                foreach (var itemId in scIds)
                {
                    BIRelation bIRelation = null;
                    var resRel = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(itemId, new PartitionKey("BIRel"));
                    if (resRel.StatusCode == System.Net.HttpStatusCode.OK)
                    {
                        continue;
                        //using var fileJson = await JsonDocument.ParseAsync(resRel.Content);
                        //bIRelation = fileJson.ToObject<BIRelation>();
                    }
                    else
                    {
                        ScBaseInfo scBaseInfo = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemAsync<ScBaseInfo>(itemId, new PartitionKey("Base"));
                        string areaName = null;
                        if (!string.IsNullOrEmpty(scBaseInfo.areaId))
                        {
                            await foreach (var itemName in cosmosClient.GetContainer(Constant.TEAMModelOS, "Normal").GetItemQueryIteratorSql<string>(queryText: $"select value(c.name) from c where c.pk='Area' and c.id='{scBaseInfo.areaId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base-Area") }))
                            {
                                areaName = itemName;
                            }
                        }

                        bIRelation = new BIRelation()
                        {
                            id = scBaseInfo.id,
                            name = scBaseInfo.name,
                            picture = scBaseInfo.picture,
                            size = scBaseInfo.size,
                            createDate = scBaseInfo.createTime,
                            region = scBaseInfo.region,
                            province = scBaseInfo.province,
                            city = scBaseInfo.city,
                            dist = scBaseInfo.dist,
                            address = scBaseInfo.address,
                            areaId = scBaseInfo.areaId,
                            scale = scBaseInfo.scale,
                            areaName = areaName,
                            upDate = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds()
                        };

                        var response = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(itemId, new PartitionKey("ProductSum"));
                        if (response.StatusCode == System.Net.HttpStatusCode.OK)
                        {
                            using var json = await JsonDocument.ParseAsync(response.Content);
                            if (json.RootElement.TryGetProperty("serial", out JsonElement serial) && !serial.ValueKind.Equals(JsonValueKind.Null))
                            {
                                List<string> serials = serial.ToObject<List<SchoolProductSumData>>().Select(x => x.prodCode).ToList();
                                bIRelation.serial = serials;
                            }
                            if (json.RootElement.TryGetProperty("service", out JsonElement service) && !service.ValueKind.Equals(JsonValueKind.Null))
                            {
                                List<string> services = service.ToObject<List<SchoolProductSumData>>().Select(x => x.prodCode).ToList();
                                bIRelation.service = services;
                            }
                            if (json.RootElement.TryGetProperty("hard", out JsonElement hard) && !hard.ValueKind.Equals(JsonValueKind.Null))
                            {
                                List<string> hards = hard.ToObject<List<SchoolProductSumDataHard>>().Select(x => x.prodCode).ToList();
                                bIRelation.hard = hards;
                            }
                        }
                        bIRelation = await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").CreateItemAsync<BIRelation>(bIRelation, new PartitionKey("BIRel"));
                    }

                    scInfos.Add(bIRelation);
                }

                return Ok(new { state = RespondCode.Ok, cnt = scIds.Count, scInfos });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI，{_option.Location}，/sccholrep/set-allscinfos \n{ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 修复所有学校统计数据中间件接口
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("set-allscstats")]
        public async Task<IActionResult> SetAllScStats(JsonElement jsonElement)
        {
            string id = "";
            try
            {
                jsonElement.TryGetProperty("scId", out JsonElement scId);
                jsonElement.TryGetProperty("year", out JsonElement _year);
                var cosmosClient = _azureCosmos.GetCosmosClient();
                var redisClinet = _azureRedis.GetRedisClient(8);

                List<string> scIds = new();
                if (!string.IsNullOrEmpty($"{scId}"))
                    scIds.Add($"{scId}");
                else
                    scIds = await CommonFind.FindScIds(cosmosClient, "select value(c.id) from c ", "Base");

                DateTimeOffset dateTime = DateTimeOffset.UtcNow;
                int year = dateTime.Year;
                if (!string.IsNullOrEmpty($"{_year}"))
                {
                    year = _year.GetInt32();
                    dateTime = dateTime.AddYears(-1);
                }

                List<StatsInfo> statsInfos = new();
                List<Task<ItemResponse<StatsInfo>>> taskStss = new();
                foreach (var sc in scIds)
                {
                    id = sc;
                    StatsInfo statsInfo = new();
                    var scDataStats = await cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").ReadItemStreamAsync($"{year}-{sc}", new PartitionKey("Statistics"));
                    if (scDataStats.StatusCode == System.Net.HttpStatusCode.OK)
                    {
                        using var fileJson = await JsonDocument.ParseAsync(scDataStats.Content);
                        statsInfo = fileJson.ToObject<StatsInfo>();
                    }
                    else
                    {
                        statsInfo.id = $"{year}-{sc}";
                    }

                    statsInfo = await SchoolStatsWay.GetSingleSc(cosmosClient, redisClinet, sc, year);
                    statsInfo.witRoom = ((int)await SchoolStatsWay.GetShoolWisdomRoomCount(_azureCosmos, _azureRedis, _configuration, _httpClient, _dingDing, statsInfo.schoolId));

                    if (scDataStats.StatusCode == System.Net.HttpStatusCode.OK)
                        taskStss.Add(cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").ReplaceItemAsync<StatsInfo>(statsInfo, $"{year}-{sc}", new PartitionKey("Statistics")));
                    else
                        taskStss.Add(cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").CreateItemAsync<StatsInfo>(statsInfo, new PartitionKey("Statistics")));

                    //statsInfo = await StatsWay.upSingleSc(cosmosClient, sc);
                    statsInfos.Add(statsInfo);
                }

                if (taskStss.Count < 256)
                    await Task.WhenAll(taskStss);
                else
                {
                    int pages = (taskStss.Count + 255) / 256;
                    for (int i = 0; i < pages; i++)
                    {
                        List<Task<ItemResponse<StatsInfo>>> temTaskStss = taskStss.Skip(i * 256).Take(256).ToList();
                        await Task.WhenAll(temTaskStss);
                    }
                }

                return Ok(new { state = RespondCode.Ok, statsInfos });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"BI，{_option.Location}，/sccholrep/set-allscstats  学校id:{id} \n{ex.Message}\n{ex.StackTrace}", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 取得IOT所有資料並生成每日學校產品分析統計data
        /// </summary>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("set-allProdAnalysis")]
        public async Task<IActionResult> SetAllProdAnalysis(JsonElement jsonElement)
        {
            var _azureCosmosClient = _azureCosmos.GetCosmosClient();
            var _azureCosmosClientCsv2 = _azureCosmos.GetCosmosClient(name: "CoreServiceV2");
            var _azureCosmosClientCsv2Read = _azureCosmos.GetCosmosClient(name: "CoreServiceV2CnRead");
            var datetime = DateTimeOffset.UtcNow;
            var y = $"{datetime.Year}";
            string month = (jsonElement.TryGetProperty("month", out JsonElement _month)) ? $"{_month}" : string.Empty;
            string day = (jsonElement.TryGetProperty("day", out JsonElement _day)) ? $"{_day}" : string.Empty;
            var redisClinet2 = _azureRedis.GetRedisClient(2);
            List<regionrow> _region_gl = new List<regionrow>();
            using (StreamReader r = new StreamReader("JsonFile/Region/region_gl.json"))
            {
                string json_g = r.ReadToEnd();
                _region_gl = JsonSerializer.Deserialize<List<regionrow>>(json_g);
            }
            List<regionrow> _region_cn = new List<regionrow>();
            using (StreamReader r = new StreamReader("JsonFile/Region/region_cn.json"))
            {
                string json_c = r.ReadToEnd();
                _region_cn = JsonSerializer.Deserialize<List<regionrow>>(json_c);
            }
            var keys = new HashSet<RedisKey>();
            int nextCursor = 0;
            do
            {
                string sql = "TeachingData:*";
                if (!string.IsNullOrWhiteSpace(month)) {
                    int _m = 0;
                    string mString = (Int32.TryParse(month, out _m) && _m > 0 && _m < 10) ? $"0{_m}" : $"{_m}";
                    sql = $"TeachingData:{mString}*"; //[例]TeachingData:08*
                    if(!string.IsNullOrWhiteSpace(day))
                    {
                        int _d = 0;
                        string dString = (Int32.TryParse(day, out _d) && _d > 0 && _d < 10) ? $"0{_d}" : $"{_d}";
                        sql = $"TeachingData:{mString}{dString}"; //[例]TeachingData:0801
                    }
                }
                RedisResult redisResult = redisClinet2.Execute("SCAN", nextCursor.ToString(), "MATCH", $"{sql}", "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> resultKeys = new List<string>();
            if(keys.Count > 0)
            {
                foreach(RedisKey key in keys)
                {
                    string date = $"{key}".Replace("TeachingData:", "");
                    Regex rgx = new Regex(@"[0-9]{4}");
                    if (rgx.IsMatch(date))
                    {
                        string m = date.Substring(0, 2);
                        string d = date.Substring(2, 2);
                        await BIProdAnalysis.BICreatDailyAnalData(_azureRedis, _azureCosmosClient, _azureCosmosClientCsv2, _azureCosmosClientCsv2Read, _dingDing, _city, _option.Location, y, m, d, _region_gl, _region_cn);
                        resultKeys.Add($"{key}");
                    }
                }
            }

            return Ok(new { state = RespondCode.Ok, keys = resultKeys });
        }

        /// <summary>
        /// 比較IES5學校Base與BIRel中間件的scale、size，若不一致則將學校的Base的數值寫入BIRel
        /// </summary>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("set-schoolBaseToBIRel")]
        public async Task<IActionResult> SetSchoolBaseToBIRel(JsonElement jsonElement)
        {
            string mode = (jsonElement.TryGetProperty("mode", out JsonElement modeJob)) ? modeJob.GetString() : "0"; // 執行模式 "0":測試 "1":執行 預設值:測試 
            List<string> scIds = (jsonElement.TryGetProperty("scId", out JsonElement scId)) ? scId.ToObject<List<string>>() : new List<string>(); // 學校ID(array)
            var cosmosClient = _azureCosmos.GetCosmosClient();
            string schIdListStr = JsonSerializer.Serialize(scIds);
            //取得IES5學校Base
            Dictionary<string, SimpleSchoolInfo> schBaseDic = new Dictionary<string, SimpleSchoolInfo>();
            string qry = "SELECT c.id, c.name, c.scale, c.size FROM c";
            string where = string.Empty;
            if (scIds.Count > 0)
            {
                where += (string.IsNullOrWhiteSpace(where)) ? " WHERE " : " AND ";
                where += $"ARRAY_CONTAINS({schIdListStr}, c.id, true)";
            }
            qry = $"{qry}{where}";
            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIteratorSql(queryText: qry, 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 schRow = obj.ToObject<SimpleSchoolInfo>();
                        schBaseDic.Add(schRow.id, schRow);
                    }
                }
            }
            //取得IES5學校BIRel中間件
            Dictionary<string, SimpleSchoolInfo> updSchBIRel = new Dictionary<string, SimpleSchoolInfo>(); //回傳值 更新的學校BIRel資訊
            if (schBaseDic.Count > 0)
            {
                string qryBIRel = "SELECT * FROM c";
                string whereBIRel = string.Empty;
                if (scIds.Count > 0)
                {
                    whereBIRel += (string.IsNullOrWhiteSpace(whereBIRel)) ? " WHERE " : " AND ";
                    whereBIRel += $"ARRAY_CONTAINS({schIdListStr}, c.id, true)";
                }
                qryBIRel = $"{qryBIRel}{whereBIRel}";
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIteratorSql(queryText: qryBIRel, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"BIRel") }))
                {
                    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())
                        {
                            bool updFlg = false;
                            BIRelation schBIRelRow = obj.ToObject<BIRelation>();
                            if (schBaseDic.ContainsKey(schBIRelRow.id))
                            {
                                //size比對
                                if (!schBIRelRow.size.Equals(schBaseDic[schBIRelRow.id].size))
                                {
                                    schBIRelRow.size = schBaseDic[schBIRelRow.id].size;
                                    updFlg = true;
                                }
                                //scale比對
                                if (!schBIRelRow.scale.Equals(schBaseDic[schBIRelRow.id].scale))
                                {
                                    schBIRelRow.scale = schBaseDic[schBIRelRow.id].scale;
                                    updFlg = true;
                                }
                                //更新
                                if (updFlg)
                                {
                                    if(mode.Equals("1")) await cosmosClient.GetContainer(Constant.TEAMModelOS, "School").ReplaceItemAsync<BIRelation>(schBIRelRow, schBIRelRow.id, new PartitionKey("BIRel"));
                                    updSchBIRel.Add(schBIRelRow.id, schBaseDic[schBIRelRow.id]);
                                }
                            }
                        }
                    }
                }
            }
            return Ok(new { state = RespondCode.Ok, updSchBIRel });
        }

        private class SimpleSchoolInfo
        {
            public string id { get; set; }
            public string name { get; set; }
            public int scale { get; set; }
            public int size { get; set; }

        }
    }
}