TEAMModelOS/TEAMModelBI/Controllers/BICommon/BINoticeController.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.Text.Json;
using System.Threading.Tasks;
using TEAMModelOS.SDK.Context.Constant;
using System.Collections.Generic;
using System.Text;
using TEAMModelOS.SDK.Extension;
using TEAMModelBI.Models;
using System.Linq;
using TEAMModelBI.Filter;
using TEAMModelOS.SDK.Models.Cosmos.BI;
using Microsoft.Azure.Cosmos;
using System;
using TEAMModelOS.SDK.Models.Service.BI;
using TEAMModelOS.SDK.Models.Cosmos.BI.BISchool;
using TEAMModelBI.Tool.Extension;
using TEAMModelOS.SDK.Models;
using Pipelines.Sockets.Unofficial.Arenas;
using TEAMModelBI.Tool;
using Azure.Core;
using Azure.Storage.Blobs.Models;
using Microsoft.AspNetCore.Authorization;
using static TEAMModelBI.Models.Extension.GeoRegion;
using System.Diagnostics.Metrics;
using Microsoft.Azure.Cosmos.Table;
using System.Net.Http.Json;
using NotifyData = TEAMModelOS.SDK.CoreAPIHttpService.NotifyData;
using Microsoft.Azure.Cosmos.Linq;
using System.Text.RegularExpressions;
using MathNet.Numerics.Distributions;
using System.Drawing.Drawing2D;
using Microsoft.Azure.Amqp.Framing;


namespace TEAMModelBI.Controllers.BICommon
{
    [Route("notice")]
    [ApiController]
    public class BINoticeController : ControllerBase
    {
        private readonly AzureCosmosFactory _azureCosmos;
        private readonly DingDing _dingDing;
        private readonly Option _option;
        private readonly AzureStorageFactory _azureStorage;
        private readonly IConfiguration _configuration;
        private readonly AzureServiceBusFactory _serviceBus;
        private readonly IHttpClientFactory _http;
        private readonly CoreAPIHttpService _coreAPIHttpService;
        private readonly IWebHostEnvironment _environment; //读取文件
        private readonly HttpClient _httpClient;
        private readonly SnowflakeId _snowflakeId;

        public BINoticeController(AzureCosmosFactory azureCosmos, DingDing dingDing, AzureStorageFactory azureStorage, IOptionsSnapshot<Option> option, IConfiguration configuration, AzureServiceBusFactory serviceBus, IHttpClientFactory http, CoreAPIHttpService coreAPIHttpService, IWebHostEnvironment hostingEnvironment, HttpClient httpClient, SnowflakeId snowflakeId)
        {
            _azureCosmos = azureCosmos;
            _dingDing = dingDing;
            _azureStorage = azureStorage;
            _option = option?.Value;
            _configuration = configuration;
            _serviceBus = serviceBus;
            _http = http;
            _coreAPIHttpService = coreAPIHttpService;
            _environment = hostingEnvironment;
            _httpClient = httpClient;
            _snowflakeId = snowflakeId;
        }

        /// <summary>
        /// 查询账户信息
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [AuthToken(Roles = "admin,rdc,assist,sales")]
        [HttpPost("get-info")]
        public async Task<IActionResult> GetInfo(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("ids", out JsonElement _ids);
            var cosmosClient = _azureCosmos.GetCosmosClient();
            List<string> ids = new();
            ids = _ids.ToObject<List<string>>();
            List<TmdUserinfo> userinfos = new();
            List<string> noFound = new();
            if (ids.Count > 0)
            {
                var content = new StringContent(ids.ToArray().ToJsonString(), Encoding.UTF8, "application/json");
                string idJson = await _coreAPIHttpService.GetUserInfos(content);
                userinfos = idJson.ToObject<List<TmdUserinfo>>();

                foreach (var item in ids)
                {
                    var tempId = userinfos.Where(s => s.id.Equals(item)).ToList();
                    var tempMail = userinfos.Where(s => !string.IsNullOrEmpty($"{s.mail}") && s.mail.Equals(item)).ToList();
                    var tempmobile = userinfos.Where(s => !string.IsNullOrEmpty($"{s.mobile}") && s.mobile.Equals(item)).ToList();
                    if (tempId.Count == 0 && tempMail.Count == 0 && tempmobile.Count == 0)
                        noFound.Add(item);
                }
            }

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

        /// <summary>
        /// 依据学校id查询学校教师信息
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        //[AuthToken(Roles = "admin,rdc,assist,sales")]
        [HttpPost("get-tchinfos")]
        public async Task<IActionResult> GetSchoolTeacher(JsonElement jsonElement)
        {
            if (!jsonElement.TryGetProperty("scId", out JsonElement scId)) return BadRequest();

            List<IdInfo> idInfos = new();
            var cosmosClient = _azureCosmos.GetCosmosClient();

            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIteratorSql<IdInfo>(queryText: "select c.id,c.name,c.picture from c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Teacher-{scId}") }))
            {
                idInfos.Add(item);
            }

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

        /// <summary>
        /// BI发布端外消息
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [AuthToken(Roles = "admin,rdc,assist,sales")]
        [HttpPost("push-info")]
        public async Task<IActionResult> PushNotion(JsonElement jsonElement)
        {
            try
            {
                var (_tmdId, _tmdName, _, _, _, _) = HttpJwtAnalysis.JwtXAuthBI(HttpContext.GetXAuth("AuthToken"), _option);

                jsonElement.TryGetProperty("type", out JsonElement type);
                jsonElement.TryGetProperty("jumpUrl", out JsonElement jumpUrl);
                jsonElement.TryGetProperty("callbackName", out JsonElement callbackName);
                jsonElement.TryGetProperty("refuseName", out JsonElement refuseName);
                if (!jsonElement.TryGetProperty("theme", out JsonElement theme)) return BadRequest();
                if (!jsonElement.TryGetProperty("content", out JsonElement content)) return BadRequest();
                jsonElement.TryGetProperty("tmdIds", out JsonElement _tmdIds);
                jsonElement.TryGetProperty("schoolIds", out JsonElement _schoolIds);
                jsonElement.TryGetProperty("areaIds", out JsonElement _areaIds);
                if (!jsonElement.TryGetProperty("crowdType", out JsonElement _crowdType)) return BadRequest();
                if (!jsonElement.TryGetProperty("sendTime", out JsonElement sendTime)) return BadRequest();
                jsonElement.TryGetProperty("source", out JsonElement source);
                jsonElement.TryGetProperty("isOnlySave", out JsonElement isOnlySave);

                var cosmosClient = _azureCosmos.GetCosmosClient();
                //DateTimeOffset dateTime = DateTimeOffset.UtcNow;

                List<IdNameCode> idNameCodes = new();
                BINotice bINotice = new();
                List<CrowdInfo> tmdIds = new();
                List<CrowdInfo> schoolIds = new();
                List<CrowdInfo> areaIds = new();

                List<string> tempScIds = new();

                //if (!string.IsNullOrEmpty($"{sendTime}"))
                //    dateTime = DateTimeOffset.Parse($"{sendTime}");
                if (string.IsNullOrEmpty($"{_tmdIds}") && string.IsNullOrEmpty($"{_schoolIds}") && string.IsNullOrEmpty($"{_areaIds}"))
                    return Ok(new { state = RespondCode.ParamsError, msg = "发送人群不能为空" });
                if (!string.IsNullOrEmpty($"{_tmdIds}"))
                    tmdIds = _tmdIds.ToObject<List<CrowdInfo>>();
                if (!string.IsNullOrEmpty($"{_schoolIds}"))
                    schoolIds = _schoolIds.ToObject<List<CrowdInfo>>();
                if (!string.IsNullOrEmpty($"{_areaIds}"))
                    areaIds = _areaIds.ToObject<List<CrowdInfo>>();

                Crowd crowd = new()
                {
                    tmdIds = tmdIds.Count > 0 ? tmdIds : new List<CrowdInfo>(),
                    schoolIds = schoolIds.Count > 0 ? schoolIds : new List<CrowdInfo>(),
                    areaIds = areaIds.Count > 0 ? areaIds : new List<CrowdInfo>(),
                    types = $"{_crowdType}"
                };

                if (tmdIds.Count > 0)
                {
                    StringBuilder tchSql = new($"select c.id, c.name,c.code,c.picture,c.nickname from c ");
                    if (tmdIds.Count > 0)
                    {
                        tchSql.Append(BICommonWay.ManyScSql(" where c.id ", tmdIds.Select(s => s.id).ToList()));
                        await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryIteratorSql<IdNameCode>(queryText: tchSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
                        {
                            var tempId = idNameCodes.FindAll(fa => fa.id.Equals(item.id)).ToList();
                            if (tempId.Count == 0)
                                idNameCodes.Add(item);
                        }
                    }
                }

                if (areaIds.Count > 0)
                {
                    StringBuilder scAreaSql = new($"select value(c.id) from c");
                    if (schoolIds.Count > 0)
                    {
                        scAreaSql.Append(BICommonWay.ManyScSql(" where c.areaId", areaIds.Select(s => s.id).ToList()));
                        await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIteratorSql<string>(queryText: scAreaSql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
                        {
                            var tempScId = schoolIds.FindAll(fa => fa.Equals(item)).ToList();
                            if (tempScId.Count == 0)
                                tempScIds.Add(item);
                        }
                    }
                }

                if (schoolIds.Count > 0)
                {
                    StringBuilder scTchSql = new($"select c.id, c.name, c.picture,c.nickname from c where c.pk='Teacher'");
                    if (schoolIds.Count > 0)
                    {
                        scTchSql.Append(BICommonWay.ManyScSql(" and c.code", schoolIds.Select(s => s.id).ToList(), $"Teacher-"));
                        await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIteratorSql<IdNameCode>(queryText: scTchSql.ToString(), requestOptions: new QueryRequestOptions() { }))
                        {
                            var tempId = idNameCodes.FindAll(fa => fa.id.Equals(item.id)).ToList();
                            if (tempId.Count == 0)
                                idNameCodes.Add(item);
                        }
                    }
                }

                string tagServiceName = ($"{_crowdType}".ToLower().Contains("hita")) ? "HiTA" : $"{_crowdType}";
                bINotice = new()
                {
                    id = Guid.NewGuid().ToString(),
                    code = "BINotice",
                    type = !string.IsNullOrEmpty($"{type}") ? type.GetInt32() : 0,
                    jumpUrl = !string.IsNullOrEmpty($"{jumpUrl}") ? $"{jumpUrl}" : "",
                    callbackName = !string.IsNullOrEmpty($"{callbackName}") ? $"{callbackName}" : "",
                    refuseName = !string.IsNullOrEmpty($"{refuseName}") ? $"{refuseName}" : "",
                    theme = $"{theme}",
                    content = $"{content}",
                    crowd = crowd,
                    crowdIds = idNameCodes.Select(s => $"{s.id}_{tagServiceName}").ToList(),
                    createId = _tmdId,
                    sendTime = sendTime.GetInt64(),//发布时间待解决
                    createTime = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds(),
                    source = !string.IsNullOrEmpty($"{source}") ? $"{source}" : "BI"
                };

                bINotice = await cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").CreateItemAsync<BINotice>(bINotice, new PartitionKey("BINotice"));
                if (isOnlySave.GetBoolean())
                {
                    //BI发送端外通知
                    _coreAPIHttpService.BIPushNotify(bINotice, new Dictionary<string, object> { { "tmdid", _tmdIds }, { "sendId", bINotice.crowdIds } }, _option.Location, _configuration, _dingDing);
                }

                return Ok(new { state = RespondCode.Ok, bINotice });
            }
            catch (Exception ex)
            {
                _ = _dingDing.SendBotMsg($"BI，{_option.Location}，notion/PushNotion() \n{ex.Message}\n{ex.StackTrace}\n", GroupNames.成都开发測試群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 查询发布的消息信息
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-infos")]
        [AuthToken(Roles = "admin,rdc,assist,sales")]
        public async Task<IActionResult> GetInfos(JsonElement jsonElement)
        {
            jsonElement.TryGetProperty("id", out JsonElement id);

            var cosmosClient = _azureCosmos.GetCosmosClient();
            List<BINotice> bINotices = new();
            StringBuilder sql = new("select value(c) from c");
            if (!string.IsNullOrEmpty($"{id}"))
                sql.Append($" where c.id='{id}'");

            sql.Append($" order by c.sendTime desc ");
            await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryIteratorSql<BINotice>(queryText: sql.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("BINotice") }))
            {
                bINotices.Add(item);
            }

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


        /// <summary>
        /// 取得學區及所屬學校、教師數
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-areas")]
#if !DEBUG
        [AuthToken(Roles = "admin")]
#endif
        public async Task<IActionResult> GetAreas(JsonElement jsonElement)
        {
            try
            {
                string reqAreaId = (jsonElement.TryGetProperty("areaId", out JsonElement _areaId)) ? _areaId.GetString() : string.Empty;
                bool showList = (jsonElement.TryGetProperty("showList", out JsonElement _showList)) ? _showList.GetBoolean() : false; //是否列出學校
                bool hasMail = (jsonElement.TryGetProperty("hasMail", out JsonElement _hasMail)) ? _hasMail.GetBoolean() : false; //篩選是否有Email true:會排除沒有Mail的醍摩豆帳號
                var cosmosClient = _azureCosmos.GetCosmosClient();
                var cosmosClientCsv2 = _azureCosmos.GetCosmosClient(name: "CoreServiceV2");
                List<AreaInfo> areaInfos = new();

                //取得學區
                string sqlArea = $"SELECT c.id, c.name FROM c ";
                string whereArea = string.Empty;
                if (!string.IsNullOrWhiteSpace(reqAreaId))
                {
                    string wherePre = (!string.IsNullOrWhiteSpace(whereArea)) ? " AND " : string.Empty;
                    whereArea += $"{wherePre} c.id = '{reqAreaId}' ";
                }
                if (!string.IsNullOrWhiteSpace(whereArea))
                {
                    whereArea = $" WHERE {whereArea}";
                    sqlArea = sqlArea + whereArea;
                }
                await foreach (var item in cosmosClient.GetContainer("TEAMModelOS", Constant.Normal).GetItemQueryIteratorSql<AreaInfo>(queryText: sqlArea, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base-Area") }))
                {
                    areaInfos.Add(item);
                }
                //取得學校ID列表
                Dictionary<string, string> schAreaDic = new Dictionary<string, string>();
                Dictionary<string, string> schNameDic = new Dictionary<string, string>();
                List<string> teacherCodes = new List<string>();
                List<string> areaIds = areaInfos.Select(a => a.id).ToList();
                string sqlSch = $"SELECT c.id, c.name, c.areaId FROM c WHERE ARRAY_CONTAINS({JsonSerializer.Serialize(areaIds)}, c.areaId)";
                ///實體校
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, Constant.School).GetItemQueryIteratorSql<JsonElement>(queryText: sqlSch, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey($"Base") }))
                {
                    string schId = item.GetProperty("id").ToString();
                    string schName = item.GetProperty("name").ToString();
                    string areaId = item.GetProperty("areaId").ToString();
                    if (!schAreaDic.ContainsKey(schId))
                    {
                        schAreaDic.Add(schId, areaId);
                        schNameDic.Add(schId, schName);
                    }
                    if (!teacherCodes.Contains($"Teacher-{schId}"))
                    {
                        teacherCodes.Add($"Teacher-{schId}");
                    }
                }
                ///虛擬校
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, Constant.School).GetItemQueryIteratorSql<JsonElement>(queryText: sqlSch, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey($"VirtualBase") }))
                {
                    string schId = item.GetProperty("id").ToString();
                    string schName = item.GetProperty("name").ToString();
                    string areaId = item.GetProperty("areaId").ToString();
                    if (!schAreaDic.ContainsKey(schId))
                    {
                        schAreaDic.Add(schId, areaId);
                        if (!schNameDic.ContainsKey(schId)) schNameDic.Add(schId, schName);
                    }
                }
                //有無Email篩檢
                List<string> tmidFilterList = new List<string>();
                if (hasMail)
                {
                    List<string> tmidListIes = new List<string>();
                    string sqlTchHasMailIes = $"SELECT DISTINCT c.id FROM c WHERE c.pk = 'Teacher' AND c.status = 'join' AND ARRAY_CONTAINS({JsonSerializer.Serialize(teacherCodes)}, c.code)";
                    await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, Constant.School).GetItemQueryIteratorSql<JsonElement>(queryText: sqlTchHasMailIes, requestOptions: null))
                    {
                        string tmid = item.GetProperty("id").ToString();
                        tmidListIes.Add(tmid);
                    }
                    string sqlEx = $"SELECT c.id FROM c WHERE IS_DEFINED(c.mail) AND NOT IS_NULL(c.mail) AND c.mail != '' AND ARRAY_CONTAINS({JsonSerializer.Serialize(tmidListIes)}, c.id) ";
                    await foreach (var item in cosmosClientCsv2.GetContainer("Core", "ID2").GetItemQueryIteratorSql<JsonElement>(queryText: sqlEx, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey($"base-ex") }))
                    {
                        string tmid = item.GetProperty("id").ToString();
                        tmidFilterList.Add(tmid);
                    }
                }
                //取得學校教師數
                Dictionary<string, int> schTeacherCntDic = new Dictionary<string, int>();
                string sqlTmidFilter = (tmidFilterList.Count > 0) ? $" AND ARRAY_CONTAINS({JsonSerializer.Serialize(tmidFilterList)}, c.id) " : string.Empty;
                string sqlTch = $"SELECT REPLACE(c.code, 'Teacher-', '')  as schoolId, COUNT(c.id) AS tchCnt FROM c WHERE c.pk = 'Teacher' AND c.status = 'join' AND ARRAY_CONTAINS({JsonSerializer.Serialize(teacherCodes)}, c.code) {sqlTmidFilter} GROUP BY c.code";
                await foreach (var item in cosmosClient.GetContainer(Constant.TEAMModelOS, Constant.School).GetItemQueryIteratorSql<JsonElement>(queryText: sqlTch, requestOptions: null))
                {
                    string schId = item.GetProperty("schoolId").ToString();
                    int tchCnt = item.GetProperty("tchCnt").GetInt32();
                    schTeacherCntDic.Add(schId, tchCnt);
                }
                //資料整理1 有教師則記入
                foreach (KeyValuePair<string, int> item in schTeacherCntDic)
                {
                    string schId = item.Key;
                    int tchCnt = item.Value;
                    string areaId = schAreaDic[schId];
                    AreaInfo areaInfo = areaInfos.Where(a => a.id.Equals(areaId)).FirstOrDefault();
                    if (areaInfo != null)
                    {
                        areaInfo.scCnt++;
                        areaInfo.tchCnt += tchCnt;
                        if (showList)
                        {
                            AreaSchoolInfo sch = new AreaSchoolInfo() { id = schId, name = schNameDic[schId], tchCnt = tchCnt };
                            areaInfo.lists.Add(sch);
                        }
                    }
                }
                //資料整理2 移除無學校學區
                var result = areaInfos.Where(a => a.scCnt > 0).ToList();

                return Ok(new { state = RespondCode.Ok, result });
            }
            catch (Exception ex)
            {
                return BadRequest(new { ex });
            }
        }
        /// <summary>
        /// 取得地理位置所屬的學校或TMID
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-geos")]
#if !DEBUG
        [AuthToken(Roles = "admin")]
#endif
        public async Task<IActionResult> GetGeos(JsonElement jsonElement)
        {
            bool showList = (jsonElement.TryGetProperty("showList", out JsonElement _showList)) ? _showList.GetBoolean() : false; //是否列出學校或TMID
            string countryId = (jsonElement.TryGetProperty("countryId", out JsonElement _countryId)) ? _countryId.GetString() : string.Empty; //地區ID(國ID)
            string provinceId = (jsonElement.TryGetProperty("provinceId", out JsonElement _provinceId)) ? _provinceId.GetString() : string.Empty; //省ID
            string cityId = (jsonElement.TryGetProperty("cityId", out JsonElement _cityId)) ? _cityId.GetString() : string.Empty; //市ID
            string distId = (jsonElement.TryGetProperty("dist", out JsonElement _distJobj)) ? _distJobj.GetString() : string.Empty; //區ID
            string type = (jsonElement.TryGetProperty("type", out JsonElement _type)) ? _type.GetString() : "school"; //類型 tmid、school
            bool hasMail = (jsonElement.TryGetProperty("hasMail", out JsonElement _hasMail)) ? _hasMail.GetBoolean() : false; //篩選是否有Email true:會排除沒有Mail的醍摩豆帳號
            string countryName = string.Empty;
            string provinceName = string.Empty;
            string cityName = string.Empty;
            string distName = string.Empty;

            var (geoInfos, groupKey) = await GetDataByGeo(type, "geo", showList, countryId, provinceId, cityId, hasMail);
            return Ok(new { state = 200, dataType = groupKey, data = geoInfos });
        }

        /// <summary>
        /// 取得教育機構所屬的學校或TMID
        /// </summary>
        /// <param name="jsonElement"></param>
        /// <returns></returns>
        [HttpPost("get-units")]
#if !DEBUG
        [AuthToken(Roles = "admin")]
#endif
        public async Task<IActionResult> GetUnitTypes(JsonElement jsonElement)
        {
            //var cosmosClient = _azureCosmos.GetCosmosClient();
            var cosmosClientCsv2 = _azureCosmos.GetCosmosClient(name: "CoreServiceV2");
            var coreCosmosClientCn = _azureCosmos.GetCosmosClient(name: "CoreServiceV2CnRead");

            string type = (jsonElement.TryGetProperty("type", out JsonElement _type)) ? _type.GetString() : string.Empty; //教育機構類型 1:基礎教育機構(K-小學) 2:中等教育機構(國中、高中/職) 3:高等教育機構(大學、研究所) 4:其他
            bool showList = (jsonElement.TryGetProperty("showList", out JsonElement _showList)) ? _showList.GetBoolean() : false; //是否列出學校
            bool hasMail = (jsonElement.TryGetProperty("hasMail", out JsonElement _hasMail)) ? _hasMail.GetBoolean() : false; //篩選是否有Email true:會排除沒有Mail的醍摩豆帳號            

            //取得ID帳號中所有的歸戶學校ID
            List<string> coreSchIds = new List<string>();
            Dictionary<string, List<string>> schTmidDic = new Dictionary<string, List<string>>(); //學校ID > TMID 字典
            string sqlEx = "SELECT c.id, c.schoolCode, c.schoolCodeW FROM c WHERE IS_DEFINED(c.schoolCode) AND NOT IS_NULL(c.schoolCode) AND IS_DEFINED(c.schoolCodeW) AND NOT IS_NULL(c.schoolCodeW)";
            if (hasMail) sqlEx += " AND IS_DEFINED(c.mail) AND NOT IS_NULL(c.mail) ";
            await foreach (var item in cosmosClientCsv2.GetContainer("Core", "ID2").GetItemQueryIteratorSql<JsonElement>(queryText: sqlEx, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey($"base-ex") }))
            {
                string tmid = item.GetProperty("id").ToString();
                string shortCode = (item.TryGetProperty("shortCode", out JsonElement _shortCode)) ? _shortCode.ToString() : string.Empty;
                string schoolCodeW = (item.TryGetProperty("schoolCodeW", out JsonElement _schoolCodeW)) ? _schoolCodeW.ToString() : string.Empty;
                string schId = (!string.IsNullOrWhiteSpace(shortCode)) ? shortCode : (!string.IsNullOrWhiteSpace(schoolCodeW)) ? schoolCodeW : string.Empty;
                if (!string.IsNullOrWhiteSpace(schId))
                {
                    if(!coreSchIds.Contains(schId)) coreSchIds.Add(schId);
                    if (schTmidDic.ContainsKey(schId)) {
                        if (!schTmidDic[schId].Contains(tmid)) schTmidDic[schId].Add(tmid);
                    }
                    else
                    {
                        schTmidDic.Add(schId, new List<string>() { tmid });
                    }
                }
            }
            //Core 篩選取得的學校、學校類型取得
            string coreSql = "SELECT c.id, c.name, c.code, c.shortCode, c.unitType, c.unitType = '1' ? '1' : c.unitType = '8' ? '1' : c.unitType = '4' ? '1' : c.unitType = '5' ? '4' : c.unitType = '6' ? '4' : c.unitType = '7' ? '4' : c.unitType = '9' ? '4' : c.unitType as unitTypeFix FROM c";
            string coreWhere = $" WHERE 1=1 AND ARRAY_CONTAINS({JsonSerializer.Serialize(coreSchIds)}, c.shortCode) AND IS_DEFINED(c.unitType) AND NOT IS_NULL(c.unitType) ";
            switch (type)
            {
                case "1": ///基礎教育機構(K-小學)
                    coreWhere += $" AND ARRAY_CONTAINS({JsonSerializer.Serialize(new List<string>() { "1", "8" })}, c.unitType)";
                    break;
                case "2": ///中等教育機構(國中、高中/職)
                    coreWhere += $" AND ARRAY_CONTAINS({JsonSerializer.Serialize(new List<string>() { "2" })}, c.unitType)";
                    break;
                case "3": ///高等教育機構(大學、研究所)
                    coreWhere += $" AND ARRAY_CONTAINS({JsonSerializer.Serialize(new List<string>() { "3" })}, c.unitType)";
                    break;
                case "4": ///其他
                    coreWhere += $" AND ARRAY_CONTAINS({JsonSerializer.Serialize(new List<string>() { "4", "5", "6", "7", "9" })}, c.unitType)";
                    break;
            }
            string lang = (_option.Location.Contains("China")) ? "zh_cn" : "zh_tw";
            Dictionary<string, string> unitNameDic = getSchoolUnitName(lang);
            List<AreaInfo> unitInfos = new(); //輸出結果
            string sqlCore = $"{coreSql}{coreWhere}";
            await foreach (var item in coreCosmosClientCn.GetContainer("Core", "School").GetItemQueryIteratorSql<JsonElement>(queryText: sqlCore, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey($"base") }))
            {
                string schName = item.GetProperty("name").ToString();
                string shortCode = item.GetProperty("shortCode").ToString();
                string unitType = item.GetProperty("unitTypeFix").ToString();
                ///結果製作
                string unitId = unitType;
                string unitName = unitNameDic[unitId];
                AreaInfo unitInfo = unitInfos.Where(u => u.id.Equals(unitId)).FirstOrDefault();
                if (unitInfo == null)
                {
                    AreaInfo unitInfoCrt = new AreaInfo();
                    unitInfoCrt.id = unitId;
                    unitInfoCrt.name = unitName;
                    unitInfos.Add(unitInfoCrt);
                    unitInfo = unitInfos.Where(u => u.id.Equals(unitId)).FirstOrDefault();
                }
                unitInfo.scCnt++;
                int tchCnt = (schTmidDic.ContainsKey(shortCode)) ? schTmidDic[shortCode].Count : 0;
                unitInfo.tchCnt += tchCnt;
                if (showList)
                {
                    AreaSchoolInfo sch = new AreaSchoolInfo() { id = shortCode, name = schName, tchCnt = tchCnt };
                    unitInfo.lists.Add(sch);
                }
            }

            return Ok(new { state = 200, data = unitInfos });
        }

        //由地理資訊取得TMID或學校核心邏輯
        /// <summary>由地理資訊取得TMID或學校核心邏輯</summary>
        /// <param name="type">地理資訊取得對象：tmid、school</param>
        /// <param name="output">輸出：tmid、school</param>
        /// <param name="showList">輸出項lists是否要有值</param>
        /// <param name="countryId"></param>
        /// <param name="provinceId"></param>
        /// <param name="cityId"></param>
        /// <param name="hasMail">醍魔豆帳號是否有Email</param>
        /// <returns></returns>
        private async Task<(List<AreaInfo>,string)> GetDataByGeo (string type, string output, bool showList, string countryId = "", string provinceId = "", string cityId = "", bool hasMail = false)
        {
            var cosmosClientCsv2 = _azureCosmos.GetCosmosClient(name: "CoreServiceV2");
            var cosmosClientCsv2Cn = _azureCosmos.GetCosmosClient(name: "CoreServiceV2CnRead");
            bool isChina = (_option.Location.Contains("China")) ? true : false;
            List<string> comeRemoveStr = GeoRegion.comeRemoveStr;
            if (isChina) countryId = "CN";
            List<TmidInfo> tmidExInfos = new List<TmidInfo>(); //TMID資訊
            List<AreaInfo> geoInfos = new(); //輸出:地理位置為單位
            List<AreaInfo> tmidInfos = new(); //輸出:TMID為單位
            regiondata regionData = GetRegionData(); //取得國省市區地理資訊架構
            regiondata regionDataEn = GetRegionDataEn(); //取得EN版國省市區地理資訊架構
            Dictionary<string, string> geoIdNameDic = new Dictionary<string, string>(); //key: geoId val:geoName
            Dictionary<string, string> geoCountryIdNameDicEn = new Dictionary<string, string>(); //key: geoId val:geoName

            string countryName = string.Empty;
            string provinceName = string.Empty;
            string cityName = string.Empty;
            string distName = string.Empty;

            //[TMID地理位置欄位 ID與名稱混雜對策] 取得搜尋的國省市名稱
            ///國字典製作
            var geoCountryIdNameDic = new Dictionary<string, string>();
            foreach (KeyValuePair<string, regionbase> item in regionData.country)
            {
                geoCountryIdNameDic.Add(item.Value.code, item.Value.name);
            }
            foreach (KeyValuePair<string, regionbase> item in regionDataEn.country)
            {
                geoCountryIdNameDicEn.Add(item.Value.code, item.Value.name);
            }
            if (!string.IsNullOrWhiteSpace(countryId))
            {
                countryName = (regionData.country.ContainsKey(countryId)) ? regionData.country[countryId].name : string.Empty;
            }
            ///省字典製作
            var geoProvinceIdNameDic = new Dictionary<string, string>();
            if (isChina)
            {
                foreach (KeyValuePair<string, regionbase> item in regionData.province["CN"])
                {
                    geoProvinceIdNameDic.Add(item.Value.code, item.Value.name);
                }
            }
            if (!string.IsNullOrWhiteSpace(provinceId))
            {
                provinceName = (geoProvinceIdNameDic.ContainsKey(provinceId)) ? geoProvinceIdNameDic[provinceId] : string.Empty;
            }
            ///市字典製作
            var geoCityIdNameDic = new Dictionary<string, string>();
            if(isChina)
            {
                foreach (var dicProvince in regionData.city["CN"])
                {
                    var pNow = dicProvince.Value;
                    foreach (var item in pNow)
                    {
                        geoCityIdNameDic.Add(item.Value.code, item.Value.name);
                    }
                }
            }
            else
            {
                foreach (var dicProvince in regionData.city["TW"])
                {
                    var pNow = dicProvince.Value;
                    foreach (var item in pNow)
                    {
                        geoCityIdNameDic.Add(item.Value.code, item.Value.name);
                    }
                }
            }
            if (!string.IsNullOrWhiteSpace(cityId))
            {
                cityName = (geoCityIdNameDic.ContainsKey(cityId)) ? geoCityIdNameDic[cityId] : string.Empty;
            }

            string sqlWhere = string.Empty;
            string groupKey = "country";
            //地理資訊取得對象：TMID
            if (type.Equals("tmid"))
            {
                List<string> schIds = new List<string>(); //要取得的學校ID
                //取得ID資訊
                sqlWhere = $" (NOT IS_NULL(c.country) AND c.country != '') ";
                ///國
                if (!string.IsNullOrWhiteSpace(countryId))
                {
                    groupKey = (!isChina) ? "city" : "province";
                    string tmpSql = $" c.country = '{countryId}' ";
                    if (!string.IsNullOrWhiteSpace(countryName))
                    {
                        comeRemoveStr.ForEach(c => { countryName = countryName.Replace(c, ""); });
                        tmpSql += $" OR CONTAINS(c.country, '{countryName}') ";
                    }
                    sqlWhere += $" AND ({tmpSql}) ";
                }
                ///省
                if (!string.IsNullOrWhiteSpace(provinceId))
                {
                    groupKey = "city";
                    string tmpSql = $" c.province = '{provinceId}' ";
                    if (!string.IsNullOrWhiteSpace(provinceName))
                    {
                        comeRemoveStr.ForEach(c => { provinceName = provinceName.Replace(c, ""); });
                        tmpSql += $" OR CONTAINS(c.province, '{provinceName}') ";
                    }
                    sqlWhere += $" AND ({tmpSql}) ";
                }
                ///市
                if (!string.IsNullOrWhiteSpace(cityId))
                {
                    groupKey = "name";
                    string tmpSql = (!isChina) ? $" c.province = '{cityId}' " : $" c.city = '{cityId}' ";
                    if (!string.IsNullOrWhiteSpace(cityName))
                    {
                        comeRemoveStr.ForEach(c => { cityName = cityName.Replace(c, ""); });
                        tmpSql += (!isChina) ? $" OR CONTAINS(c.province, '{cityName}') " : $" OR CONTAINS(c.city, '{cityName}') ";
                    }
                    sqlWhere += $" AND ({tmpSql}) ";
                }
                //有Email篩選
                if (hasMail)
                {
                    string tmpSql = "IS_DEFINED(c.mail) AND NOT IS_NULL(c.mail)";
                    sqlWhere += $" AND ({tmpSql}) ";
                }
                if (!string.IsNullOrWhiteSpace(sqlWhere))
                {
                    Regex regex = new Regex("^[a-zA-Z0-9]{2}$");
                    string sqlTmidEx = $"SELECT * FROM c WHERE {sqlWhere}";
                    await foreach (var item in cosmosClientCsv2.GetContainer("Core", "ID2").GetItemQueryIteratorSql<JsonElement>(queryText: sqlTmidEx, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey($"base-ex") }))
                    {
                        TmidInfo tmidExInfoTmp = item.ToObject<TmidInfo>();
                        //地理位置名稱
                        ///國
                        if(!string.IsNullOrWhiteSpace(tmidExInfoTmp.country))
                        {
                            string countryIdNow = (regex.IsMatch(tmidExInfoTmp.country)) ? tmidExInfoTmp.country : string.Empty;
                            if (string.IsNullOrWhiteSpace(countryIdNow)) //國欄位填入國名對策
                            {
                                var geoCountry = geoCountryIdNameDic.FirstOrDefault(x => x.Value.Contains(tmidExInfoTmp.country));
                                if (!geoCountry.Equals(default(KeyValuePair<string, string>))) {
                                    countryIdNow = geoCountry.Key;
                                }
                            }
                            if (string.IsNullOrWhiteSpace(countryIdNow)) //國欄位填入國名對策(英文)
                            {
                                var geoCountryEn = geoCountryIdNameDicEn.FirstOrDefault(x => x.Value.Equals(tmidExInfoTmp.country));
                                if (!geoCountryEn.Equals(default(KeyValuePair<string, string>))) {
                                    countryIdNow = geoCountryEn.Key;
                                }
                            }
                            if(!string.IsNullOrWhiteSpace(countryIdNow) && !tmidExInfoTmp.country.Equals(countryIdNow)) tmidExInfoTmp.country = countryIdNow;
                            if (geoCountryIdNameDic.TryGetValue(countryIdNow, out var countryInfo))
                            {
                                tmidExInfoTmp.countryName = countryInfo;
                            }
                        }
                        ///省
                        if (!string.IsNullOrWhiteSpace(tmidExInfoTmp.province))
                        {
                            if (tmidExInfoTmp.country.Equals("TW")) //TW的省欄位填的是市資料
                            {
                                tmidExInfoTmp.city = tmidExInfoTmp.province;
                                tmidExInfoTmp.province = string.Empty;
                            }
                            else
                            {
                                string provinceIdNow = (regex.IsMatch(tmidExInfoTmp.province)) ? tmidExInfoTmp.province : string.Empty;
                                if (string.IsNullOrWhiteSpace(provinceIdNow)) //省欄位填入省名對策
                                {
                                    var geoProvince = geoProvinceIdNameDic.FirstOrDefault(x => x.Value.Equals(tmidExInfoTmp.province));
                                    if (!geoProvince.Equals(default(KeyValuePair<string, string>)))
                                    {
                                        provinceIdNow = geoProvince.Key;
                                    }
                                }
                                if (!string.IsNullOrWhiteSpace(provinceIdNow) && !tmidExInfoTmp.province.Equals(provinceIdNow)) tmidExInfoTmp.province = provinceIdNow;
                                //var provinceDic = regionData.province[tmidExInfoTmp.country];
                                if (geoProvinceIdNameDic.TryGetValue(tmidExInfoTmp.province, out var provinceInfo))
                                {
                                    tmidExInfoTmp.provinceName = provinceInfo;
                                }
                            }
                        }
                        ///市
                        if (!string.IsNullOrWhiteSpace(tmidExInfoTmp.city))
                        {
                            string cityIdNow = (regex.IsMatch(tmidExInfoTmp.city)) ? tmidExInfoTmp.city : string.Empty;
                            if (string.IsNullOrWhiteSpace(cityIdNow)) //市欄位填入市名對策
                            {
                                var geoCity = geoCityIdNameDic.FirstOrDefault(x => x.Value.Equals(tmidExInfoTmp.city));
                                if (!geoCity.Equals(default(KeyValuePair<string, string>)))
                                {
                                    cityIdNow = geoCity.Key;
                                }
                            }
                            if (!string.IsNullOrWhiteSpace(cityIdNow) && !tmidExInfoTmp.city.Equals(cityIdNow)) tmidExInfoTmp.city = cityIdNow;
                            if(geoCityIdNameDic.TryGetValue(tmidExInfoTmp.city, out var cityInfo))
                            {
                                tmidExInfoTmp.cityName = cityInfo;
                            }
                        }
                        //要取得的學校ID
                        if (!string.IsNullOrWhiteSpace(tmidExInfoTmp.schoolCode) && !schIds.Contains(tmidExInfoTmp.schoolCode)) schIds.Add(tmidExInfoTmp.schoolCode);
                        if (!string.IsNullOrWhiteSpace(tmidExInfoTmp.schoolCodeW) && !schIds.Contains(tmidExInfoTmp.schoolCodeW)) schIds.Add(tmidExInfoTmp.schoolCodeW);
                        //輸出項生成
                        tmidExInfos.Add(tmidExInfoTmp);
                    }
                }
                //取得學校資訊
                Dictionary<string, string> schIdNameDic = new Dictionary<string, string>(); //key: schId val:schName
                string sqlWhereSch = $" ARRAY_CONTAINS({JsonSerializer.Serialize(schIds)}, c.shortCode)";
                string sqlSch = $"SELECT c.id, c.code, c.shortCode, c.name FROM c WHERE {sqlWhereSch} ";
                await foreach (var item in cosmosClientCsv2Cn.GetContainer("Core", "School").GetItemQueryIteratorSql<JsonElement>(queryText: sqlSch, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey($"base") }))
                {
                    string schId = item.GetProperty("shortCode").ToString();
                    string schName = item.GetProperty("name").ToString();
                    schIdNameDic.Add(schId, schName);
                }
                //輸出項整理
                foreach (TmidInfo tmidInfo in tmidExInfos)
                {
                    ///tmidInfos
                    AreaInfo tmidInfoRow = new AreaInfo() { id = tmidInfo.id, name = tmidInfo.name };
                    tmidInfos.Add(tmidInfoRow);
                    ///geoInfos
                    string geoId = tmidInfo.country;
                    string geoName = tmidInfo.countryName;
                    if(!string.IsNullOrWhiteSpace(geoName)) comeRemoveStr.ForEach(c => { geoName = ReplaceLastMatch(geoName, c, string.Empty); }); //字串替換
                    switch (groupKey)
                    {
                        case "province":
                            geoId = tmidInfo.province;
                            geoName = tmidInfo.provinceName;
                            if (!string.IsNullOrWhiteSpace(geoName)) comeRemoveStr.ForEach(c => { geoName = ReplaceLastMatch(geoName, c, string.Empty); }); //字串替換
                            break;
                        case "city":
                            geoId = tmidInfo.city;
                            geoName = tmidInfo.cityName;
                            if (!string.IsNullOrWhiteSpace(geoName)) comeRemoveStr.ForEach(c => { geoName = ReplaceLastMatch(geoName, c, string.Empty); }); //字串替換
                            break;
                        case "name":
                            geoId = tmidInfo.schoolCode;
                            if (string.IsNullOrWhiteSpace(geoId)) geoId = tmidInfo.schoolCodeW;
                            if (!string.IsNullOrWhiteSpace(geoName)) geoName = (!string.IsNullOrWhiteSpace(geoId) && schIdNameDic.ContainsKey(geoId)) ? schIdNameDic[geoId] : string.Empty;
                            break;
                    }
                    //輸出
                    if (!string.IsNullOrWhiteSpace(geoId))
                    {
                        AreaInfo geoInfo = geoInfos.Where(g => g.id.Equals(geoId)).FirstOrDefault();
                        if (geoInfo == null)
                        {
                            AreaInfo geoInfoCrt = new AreaInfo();
                            geoInfoCrt.id = geoId;
                            geoInfoCrt.name = (_option.Location.Contains("Global")) ? "無法取得名稱" : "无法取得名称";
                            if (!string.IsNullOrWhiteSpace(geoName)) geoInfoCrt.name = geoName;
                            geoInfos.Add(geoInfoCrt);
                            geoInfo = geoInfos.Where(g => g.id.Equals(geoInfoCrt.id)).FirstOrDefault();
                        }
                        string schId = (!string.IsNullOrWhiteSpace(tmidInfo.schoolCode)) ? tmidInfo.schoolCode : (!string.IsNullOrWhiteSpace(tmidInfo.schoolCodeW)) ? tmidInfo.schoolCodeW : string.Empty;
                        string schName = (!string.IsNullOrWhiteSpace(schId) && schIdNameDic.ContainsKey(schId)) ? schIdNameDic[schId] : string.Empty;
                        if (!string.IsNullOrWhiteSpace(schId))
                        {
                            AreaSchoolInfo schInGeo = geoInfo.lists.FirstOrDefault(s => s.id.Equals(schId));
                            if (schInGeo == null)
                            {
                                geoInfo.lists.Add(new AreaSchoolInfo() { id = schId, name = schName });
                                schInGeo = geoInfo.lists.FirstOrDefault(s => s.id.Equals(schId));
                            }
                            schInGeo.tchCnt++;
                            geoInfo.scCnt++;
                            geoInfo.tchCnt++;
                        }
                    }
                }
                ///移除無校列
                geoInfos.RemoveAll(g => g.lists.Count.Equals(0));
                if (!showList)
                {
                    geoInfos.ForEach(g => g.lists.Clear());
                }
            }
            //地理資訊取得對象：學校
            else if (type.Equals("school"))
            {
                List<string> schIds = new List<string>(); //要取得的學校ID
                //取得ID資訊
                sqlWhere = $" ((NOT IS_NULL(c.schoolCode) AND c.schoolCode != '') OR (NOT IS_NULL(c.schoolCodeW) AND c.schoolCodeW != ''))";
                //有Email篩選
                if (hasMail)
                {
                    string tmpSql = "IS_DEFINED(c.mail) AND NOT IS_NULL(c.mail)";
                    sqlWhere += $" AND ({tmpSql}) ";
                }
                if (!string.IsNullOrWhiteSpace(sqlWhere))
                {
                    string sqlTmidEx = $"SELECT * FROM c WHERE {sqlWhere}";
                    await foreach (var item in cosmosClientCsv2.GetContainer("Core", "ID2").GetItemQueryIteratorSql<JsonElement>(queryText: sqlTmidEx, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey($"base-ex") }))
                    {
                        TmidInfo tmidEx = item.ToObject<TmidInfo>();
                        tmidExInfos.Add(tmidEx);
                        if (!string.IsNullOrWhiteSpace(tmidEx.schoolCode) && !schIds.Contains(tmidEx.schoolCode)) schIds.Add(tmidEx.schoolCode);
                        if (!string.IsNullOrWhiteSpace(tmidEx.schoolCodeW) && !schIds.Contains(tmidEx.schoolCodeW)) schIds.Add(tmidEx.schoolCodeW);
                    }
                }
                //取得學校資訊
                if(schIds.Count > 0)
                {
                    sqlWhere = $" (NOT IS_NULL(c.countryId) AND c.countryId != '') ";
                    ///國
                    if (!string.IsNullOrWhiteSpace(countryId))
                    {
                        groupKey = (isChina) ? "province" : (!countryId.Equals("TW")) ? "name" : "city";
                        sqlWhere += $" AND c.countryId = '{countryId}' ";
                    }
                    ///省
                    if (!string.IsNullOrWhiteSpace(provinceId))
                    {
                        groupKey = "city";
                        sqlWhere += $" AND c.provinceId = '{provinceId}' ";
                    }
                    ///市
                    if (!string.IsNullOrWhiteSpace(cityId))
                    {
                        groupKey = "name";
                        sqlWhere += $" AND c.cityId = '{cityId}' ";
                    }
                    sqlWhere += $" AND ARRAY_CONTAINS({JsonSerializer.Serialize(schIds)}, c.shortCode)";
                    string sqlSch = $"SELECT c.id, c.code, c.shortCode, c.name, c.countryId, c.provinceId, c.cityId, c.distId FROM c WHERE {sqlWhere} ";
                    await foreach (var item in cosmosClientCsv2Cn.GetContainer("Core", "School").GetItemQueryIteratorSql<JsonElement>(queryText: sqlSch, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey($"base") }))
                    {
                        string schId = item.GetProperty("shortCode").ToString();
                        string schName = item.GetProperty("name").ToString();
                        string geoId = Convert.ToString(item.GetProperty("countryId"));
                        var countryInfo = regionData.country.FirstOrDefault(c => c.Key.Equals(geoId));
                        string geoName = (!countryInfo.Equals(default(KeyValuePair<string, regionbase>))) ? countryInfo.Value.name : string.Empty;
                        if (!string.IsNullOrWhiteSpace(geoName)) comeRemoveStr.ForEach(c => { geoName = ReplaceLastMatch(geoName, c, string.Empty); }); //字串替換
                        switch (groupKey)
                        {
                            case "province":
                                geoId = Convert.ToString(item.GetProperty("provinceId"));
                                var provinceDic = regionData.province[countryId];
                                if(provinceDic.TryGetValue(geoId, out var provinceInfo))
                                {
                                    geoName = provinceInfo.name;
                                }
                                if (!string.IsNullOrWhiteSpace(geoName)) comeRemoveStr.ForEach(c => { geoName = ReplaceLastMatch(geoName, c, string.Empty); }); //字串替換
                                break;
                            case "city":
                                geoId = Convert.ToString(item.GetProperty("cityId"));
                                if(regionData.city.ContainsKey(countryId))
                                {
                                    if (regionData.city[countryId].ContainsKey("tw"))
                                    {
                                        var cityDic = regionData.city[countryId]["tw"];
                                        if(cityDic.TryGetValue(geoId, out var cityInfo))
                                        {
                                            geoName = cityInfo.name;
                                        }
                                    }
                                    else if (!string.IsNullOrWhiteSpace(provinceId) && regionData.city[countryId].ContainsKey(provinceId))
                                    {
                                        var cityDic = regionData.city[countryId][provinceId];
                                        if (cityDic.TryGetValue(geoId, out var cityInfo))
                                        {
                                            geoName = cityInfo.name;
                                        }
                                    }
                                }
                                comeRemoveStr.ForEach(c => { geoName = ReplaceLastMatch(geoName, c, string.Empty); }); //字串替換
                                break;
                            case "name":
                                geoId = Convert.ToString(item.GetProperty("shortCode"));
                                geoName = Convert.ToString(item.GetProperty("name"));
                                break;
                        }
                        //輸出項製作
                        if (!string.IsNullOrWhiteSpace(geoId))
                        {
                            ///geoInfos
                            AreaInfo geoInfo = geoInfos.Where(g => g.id.Equals(geoId)).FirstOrDefault();
                            if (geoInfo == null)
                            {
                                AreaInfo geoInfoCrt = new AreaInfo();
                                geoInfoCrt.id = geoId;
                                geoInfoCrt.name = (_option.Location.Contains("Global")) ? "無法取得名稱" : "无法取得名称";
                                if(!string.IsNullOrWhiteSpace(geoName)) geoInfoCrt.name = geoName;
                                geoInfos.Add(geoInfoCrt);
                                geoInfo = geoInfos.Where(g => g.id.Equals(geoInfoCrt.id)).FirstOrDefault();
                            }
                            geoInfo.scCnt++;
                            List<TmidInfo> tmidInfoSelected = tmidExInfos.Where(g => (!string.IsNullOrWhiteSpace(g.schoolCode) && g.schoolCode.Equals(schId)) || (!string.IsNullOrWhiteSpace(g.schoolCodeW) && g.schoolCodeW.Equals(schId))).ToList();
                            geoInfo.tchCnt += tmidInfoSelected.Count;
                            if (showList && (groupKey.Equals("country") || groupKey.Equals("province") || groupKey.Equals("city")))
                            {
                                AreaSchoolInfo sch = new AreaSchoolInfo() { id = schId, name = schName, tchCnt = tmidInfoSelected.Count };
                                geoInfo.lists.Add(sch);
                            }
                            ///tmidInfos
                            List<AreaInfo> tmidAddToList = tmidInfoSelected.Select(g => new AreaInfo() { id = g.id, name = g.name }).ToList();
                            tmidInfos.AddRange(tmidAddToList);
                        }
                    }
                }
            }
            //輸出
            if (output.Equals("tmid"))
            {
                return (tmidInfos, groupKey);
            }
            else
            {
                return (geoInfos, groupKey);
            }
        }

        /// <summary>
        /// (個別挑選)取得TMID資訊
        /// </summary>
        [HttpPost("get-tmidinfo")]
#if !DEBUG
        [AuthToken(Roles = "admin")]
#endif
        public async Task<IActionResult> GetTmidInfo(GetTmidInfoParam target)
        {
            try
            {
                var storageClientCsv2 = _azureStorage.GetCloudTableClient(name: "CoreServiceV2"); //Storage CSV2
                var cosmosClientIes = _azureCosmos.GetCosmosClient();
                var cosmosClientCsv2 = _azureCosmos.GetCosmosClient(name: "CoreServiceV2");
                var tablePointsClient = storageClientCsv2.GetTableReference("Points");

                //各項值取聯集或交集
                string modeToAll = !string.IsNullOrWhiteSpace(target.mode) ? target.mode : "and";

                string sqlSel = "SELECT c.id FROM c WHERE 1=1 ";
                //生成時間
                List<string> passTmidCrt = new List<string>();
                List<string> sqlWhereListBase = new List<string>();
                if (target.creatTime != null)
                {
                    if (target.creatTime.start > 0) sqlWhereListBase.Add($" {target.creatTime.start} <= StringToNumber(c.id) ");
                    if (target.creatTime.end > 0) sqlWhereListBase.Add($" StringToNumber(c.id) <= {target.creatTime.end} ");
                }
                ///SQL文生成
                bool useCrtFlg = false;
                string sqlWhereBase = string.Empty;
                foreach (string w in sqlWhereListBase)
                {
                    useCrtFlg = true;
                    sqlWhereBase += $" AND {w}";
                }
                if (!string.IsNullOrWhiteSpace(sqlWhereBase))
                {
                    string sqlBase = $"{sqlSel}{sqlWhereBase}";
                    await foreach (var item in cosmosClientCsv2
                    .GetContainer("Core", "ID2")
                    .GetItemQueryIteratorSql<JsonElement>(queryText: sqlBase, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("base") }))
                    {
                        string id = item.GetProperty("id").ToString();
                        if (!passTmidCrt.Contains(id)) passTmidCrt.Add(id);
                    }
                }

                //地理資訊 (base-ex)
                bool useGeoFlg = (target.geo != null && (!string.IsNullOrWhiteSpace(target.geo.countryId) || !string.IsNullOrWhiteSpace(target.geo.provinceId) || !string.IsNullOrWhiteSpace(target.geo.cityId))) ? true : false;
                List<string> passTmidGeo = new List<string>();
                var (geoInfos, _) = await GetDataByGeo("tmid", "tmid", true, target.geo.countryId, target.geo.provinceId, target.geo.cityId, target.hasMail);
                foreach(var geoInfo in geoInfos)
                {
                    if (!passTmidGeo.Contains(geoInfo.id)) passTmidGeo.Add(geoInfo.id);
                }

                //使用產品  ARRAY_CONTAINS(['hiteach', 'hiteachcc', 'sokapp', 'sokrates', 'webirs'], c.type)
                bool usePrdFlg = false;
                List<string> passTmidPrd = new List<string>();
                var tmidProdDic = new Dictionary<string, List<string>>(); //各ID使用過的的產品List
                List<string> prodWhereList = new List<string>();
                if (target.product != null && target.product.id.Count > 0)
                {
                    usePrdFlg = true;
                    foreach (string prodCode in target.product.id)
                    {
                        prodWhereList.Add(prodCode);
                    }
                }
                if (prodWhereList.Count > 0)
                {
                    string sqlSelCross = "SELECT DISTINCT c.id, c.type FROM c ";
                    string sqlWhereCross = $" WHERE ARRAY_CONTAINS({JsonSerializer.Serialize(prodWhereList)}, c.type)";
                    string sqlCross = $"{sqlSelCross}{sqlWhereCross}";
                    await foreach (var item in cosmosClientCsv2
                    .GetContainer("Core", "ID2")
                    .GetItemQueryIteratorSql<JsonElement>(sqlCross, requestOptions: null))
                    {
                        string id = item.GetProperty("id").ToString();
                        string prod = item.GetProperty("type").ToString();
                        if (!tmidProdDic.ContainsKey(id)) tmidProdDic.Add(id, new List<string>());
                        tmidProdDic[id].Add(prod);
                    }
                    ///生成符合AndOr條件的TMID製作
                    if (target.product.mode.Equals("and"))
                    {
                        foreach (var prodItem in tmidProdDic)
                        {
                            int hasProdCount = 0;
                            string tmid = prodItem.Key;
                            ///產品比對
                            foreach (string prod in target.product.id)
                            {
                                if (prodItem.Value.Contains(prod)) hasProdCount++;
                            }
                            if (hasProdCount.Equals(target.product.id.Count))
                            {
                                if (!passTmidPrd.Contains(tmid)) passTmidPrd.Add(tmid);
                            }
                        }
                    }
                    else if (target.product.mode.Equals("or"))
                    {
                        foreach (var prodItem in tmidProdDic)
                        {
                            int hasProdCount = 0;
                            string tmid = prodItem.Key;
                            ///產品比對
                            foreach (string prod in target.product.id)
                            {
                                if (prodItem.Value.Contains(prod))
                                {
                                    if (!passTmidPrd.Contains(tmid)) passTmidPrd.Add(tmid);
                                    break;
                                }
                            }
                        }
                    }
                }

                //點數
                bool usePntFlg = false;
                List<string> passTmidPnt = new List<string>();
                string filter = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "Points");
                string rkFilterCombine = string.Empty;
                if (target.point != null)
                {
                    if (target.point.start > 0 || target.point.end > 0)
                    {
                        usePntFlg = true;
                        if (target.point.start > 0)
                        {
                            string rkFilter = TableQuery.GenerateFilterConditionForInt("Points", QueryComparisons.GreaterThanOrEqual, Convert.ToInt32(target.point.start));
                            if (string.IsNullOrWhiteSpace(rkFilterCombine)) rkFilterCombine = rkFilter;
                            else rkFilterCombine = TableQuery.CombineFilters(rkFilterCombine, TableOperators.And, rkFilter);
                        }
                        if (target.point.end > 0)
                        {
                            string rkFilter = TableQuery.GenerateFilterConditionForInt("Points", QueryComparisons.LessThanOrEqual, Convert.ToInt32(target.point.end));
                            if (string.IsNullOrWhiteSpace(rkFilterCombine)) rkFilterCombine = rkFilter;
                            else rkFilterCombine = TableQuery.CombineFilters(rkFilterCombine, TableOperators.And, rkFilter);
                        }
                        filter = TableQuery.CombineFilters(filter, TableOperators.And, rkFilterCombine);
                        TableQuery tableQuery = new TableQuery().Where(filter);
                        var usersPoints = tablePointsClient.ExecuteQuery(tableQuery);
                        if (usersPoints.Any())
                        {
                            foreach (DynamicTableEntity item in usersPoints)
                            {
                                string tmid = item.RowKey;
                                if (!passTmidPnt.Contains(tmid)) passTmidPnt.Add(tmid);
                            }
                        }
                    }
                }

                //學校
                bool useSchFlg = false;
                List<string> passTmidSch = new List<string>();
                if (target.school != null && target.school.Count > 0)
                {
                    useSchFlg = true;
                    List<string> schTeacherCodeList = target.school.Select(s => $"Teacher-{s}").ToList();
                    string sqlWhereSch = $" AND ARRAY_CONTAINS({JsonSerializer.Serialize(schTeacherCodeList)}, c.code)";
                    string sqlSch = $"{sqlSel}{sqlWhereSch}";
                    await foreach (var item in cosmosClientIes
                    .GetContainer(Constant.TEAMModelOS, Constant.School)
                    .GetItemQueryIteratorSql<JsonElement>(sqlSch, requestOptions: null))
                    {
                        string id = item.GetProperty("id").ToString();
                        if (!passTmidSch.Contains(id)) passTmidSch.Add(id);
                    }
                }

                //TMID
                bool useTmidFlg = false;
                List<string> passTmidMid = new List<string>();
                if (target.tmid != null && target.tmid.Count > 0)
                {
                    useTmidFlg = true;
                    string sqlWhereTmid = $" AND ARRAY_CONTAINS({JsonSerializer.Serialize(target.tmid)}, c.id)";
                    string sqlTmid = $"{sqlSel}{sqlWhereTmid}";
                    await foreach (var item in cosmosClientCsv2
                   .GetContainer("Core", "ID2")
                   .GetItemQueryIteratorSql<JsonElement>(queryText: sqlTmid, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("base") }))
                    {
                        string id = item.GetProperty("id").ToString();
                        if (!passTmidMid.Contains(id)) passTmidMid.Add(id);
                    }
                }

                //所有TMID做聯集交集整合
                List<string> passTmid = new List<string>(); //最終條件符合的TMID列表
                ///初始化
                if (passTmidCrt.Count > 0) passTmid = passTmidCrt;
                else if (passTmidGeo.Count > 0) passTmid = passTmidGeo;
                else if (passTmidPrd.Count > 0) passTmid = passTmidPrd;
                else if (passTmidPnt.Count > 0) passTmid = passTmidPnt;
                else if (passTmidSch.Count > 0) passTmid = passTmidSch;
                else if (passTmidMid.Count > 0) passTmid = passTmidMid;
                if (modeToAll.Equals("or")) //聯集
                {
                    if (useCrtFlg) passTmid = passTmid.Union(passTmidCrt).ToList();
                    if (useGeoFlg) passTmid = passTmid.Union(passTmidGeo).ToList();
                    if (usePrdFlg) passTmid = passTmid.Union(passTmidPrd).ToList();
                    if (usePntFlg) passTmid = passTmid.Union(passTmidPnt).ToList();
                    if (useSchFlg) passTmid = passTmid.Union(passTmidSch).ToList();
                    if (useTmidFlg) passTmid = passTmid.Union(passTmidMid).ToList();
                }
                else //交集
                {
                    if (useCrtFlg) passTmid = passTmid.Intersect(passTmidCrt).ToList();
                    if (useGeoFlg) passTmid = passTmid.Intersect(passTmidGeo).ToList();
                    if (usePrdFlg) passTmid = passTmid.Intersect(passTmidPrd).ToList();
                    if (usePntFlg) passTmid = passTmid.Intersect(passTmidPnt).ToList();
                    if (useSchFlg) passTmid = passTmid.Intersect(passTmidSch).ToList();
                    if (useTmidFlg) passTmid = passTmid.Intersect(passTmidMid).ToList();
                }

                //最終ID基本資訊取得
                List<IdName> result = new List<IdName>();
                string sqlBaseInfo = $"SELECT c.id, c.name FROM c WHERE ARRAY_CONTAINS({JsonSerializer.Serialize(passTmid)}, c.id)";
                await foreach (var item in cosmosClientCsv2
                    .GetContainer("Core", "ID2")
                    .GetItemQueryIteratorSql<JsonElement>(queryText: sqlBaseInfo, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("base") }))
                {
                    string id = item.GetProperty("id").ToString();
                    string name = item.GetProperty("name").ToString();
                    IdName resultRow = new IdName() { id = id, name = name };
                    result.Add(resultRow);
                }
                return Ok(new { state = RespondCode.Ok, data = result });
            }
            catch (Exception ex)
            {
                return BadRequest(new { state = RespondCode.NotFound, data = new List<IdName>() });
            }
        }

        /// <summary>
        /// 寄發訊息API
        /// </summary>
        /// <param name="target"></param>
        /// <param name="type">發送類型</param>
        /// <param name="method">挑選方式</param>
        /// <param name="title">標題</param>
        /// <param name="body">內文</param>
        /// <param name="data">傳遞資料</param>
        /// <param name="action">傳遞動作</param>
        [HttpPost("send-message")]
#if !DEBUG
        [AuthToken(Roles = "admin")]
#endif
        public async Task<IActionResult> SendMessage(JsonElement jsonElement)
        {
            SendMessageParam target = (jsonElement.TryGetProperty("target", out JsonElement _target)) ? _target.ToObject<SendMessageParam>() : null; //發送對象
            string type = (jsonElement.TryGetProperty("type", out JsonElement _type)) ? _type.ToString() : string.Empty; //發送類型 mail:郵件、notify:端外、sms:簡訊
            string method = (jsonElement.TryGetProperty("method", out JsonElement _method)) ? _method.ToString() : string.Empty; //挑選方式 single:個別 multi:批次
            string subject = (jsonElement.TryGetProperty("subject", out JsonElement _subject)) ? _subject.ToString() : string.Empty; //主題 (Email用)
            string title = (jsonElement.TryGetProperty("title", out JsonElement _title)) ? _title.ToString() : string.Empty; //標題
            string body = (jsonElement.TryGetProperty("body", out JsonElement _body)) ? _body.ToString() : string.Empty; //內文
            string sender = (jsonElement.TryGetProperty("sender", out JsonElement _sender)) ? _sender.ToString() : string.Empty; //發送者
            string hubName = (jsonElement.TryGetProperty("hubName", out JsonElement _hubName)) ? _hubName.ToString() : "hita5"; //訊息中樞
            string data = (jsonElement.TryGetProperty("data", out JsonElement _data)) ? _data.ToString() : string.Empty; //額外資料
            long send = (jsonElement.TryGetProperty("send", out JsonElement _send)) ? _send.GetInt64() : 0; //發送時間 0:立即發送
            string template = (jsonElement.TryGetProperty("template", out JsonElement _template)) ? _template.ToString() : string.Empty; //模板ID

            if (target == null || string.IsNullOrWhiteSpace(type) || string.IsNullOrWhiteSpace(method) || string.IsNullOrWhiteSpace(title) || string.IsNullOrWhiteSpace(body) || string.IsNullOrWhiteSpace(sender)) return BadRequest();
            string eventKey = "bi-gen-notify";
            string eventId = $"{eventKey}_{_snowflakeId.NextId()}";
            string eventName = "BI send notification";
            var result = await SendMessageCore(target, type, method, subject, title, body, data, sender, hubName, template, send, eventId, eventName);

            return Ok(new { state = RespondCode.Ok, result });
        }
        ///寄發訊息核心邏輯
        private async Task<object> SendMessageCore(SendMessageParam target, string type, string method, string subject, string title, string body, string data, string sender, string hubName, string template, long send, string eventId = "", string eventName = "")
        {
            #region target 內容例
            //{
            //     "area":[
            //      "1234"
            //     ],
            //     "geo":[
            //      {
            //       "countryId":"TW",
            //       "provinceId":"",
            //       "cityId":"30",
            //       "type":"tmid"
            //      },
            //      {
            //       "countryId":"TW",
            //       "provinceId":"",
            //       "cityId":"30",
            //       "type":"school"
            //      }
            //     ],
            //     "unit":[
            //      "1"
            //     ],
            //     "school":[
            //      "1234"
            //     ],
            //     "tmid": [
            //      "1234"
            //     ]
            //}
            #endregion
            var cosmosClientCsv2 = _azureCosmos.GetCosmosClient(name: "CoreServiceV2");

            List<string> tmids_area = await GetTmidByAreaId(target.area);
            List<string> tmids_geo = await GetTmidByGeo(target.geo);
            List<string> tmids_unit = await GetTmidByUnitId(target.unit);
            List<string> tmids_school = await GetTmidBySchoolId(target.school);
            List<string> tmids_direct = target.tmid;

            List<string> tmids = new List<string>(); //聯集化
            tmids = tmids.Union(tmids_area).ToList();
            tmids = tmids.Union(tmids_geo).ToList();
            tmids = tmids.Union(tmids_unit).ToList();
            tmids = tmids.Union(tmids_school).ToList();
            tmids = tmids.Union(tmids_direct).ToList();
            tmids = tmids.Distinct().ToList();  //唯一化

            //取得TMID資料
            List<IdInfo> tmidInfos = new List<IdInfo>();
            string sqlBaseInfo = $"SELECT c.id, c.name, c.mail, c.mobile FROM c WHERE ARRAY_CONTAINS({JsonSerializer.Serialize(tmids)}, c.id)";
            if(type.Equals("mail")) //發送類型 mail:郵件
            {
                sqlBaseInfo += $" AND (IS_DEFINED(c.mail) AND NOT IS_NULL(c.mail) AND c.mail != '') ";
            }
            else if(type.Equals("sms")) //發送類型 sms:簡訊
            {
                sqlBaseInfo += $" AND (IS_DEFINED(c.mobile) AND NOT IS_NULL(c.mobile) AND c.mobile != '') ";
            }
            await foreach (var item in cosmosClientCsv2
                .GetContainer("Core", "ID2")
                .GetItemQueryIteratorSql<JsonElement>(queryText: sqlBaseInfo, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("base-ex") }))
            {
                IdInfo idInfo = new IdInfo();
                idInfo.id = item.GetProperty("id").ToString();
                idInfo.name = (item.TryGetProperty("name", out JsonElement _name)) ? _name.ToString() : string.Empty;
                idInfo.mail = (item.TryGetProperty("mail", out JsonElement _mail)) ? _mail.ToString() : string.Empty;
                idInfo.mobile = (item.TryGetProperty("mobile", out JsonElement _mobile)) ? _mobile.ToString() : string.Empty;
                tmidInfos.Add(idInfo);
            }

            //寄發訊息
            if (type.Equals("notify")) //端內外通知
            {
                List<string> tmIds = tmidInfos.Select(i => i.id).ToList();
                if(send.Equals(0)) //立即寄送
                {
                    HttpResponseMessage response = CallPushNotifyApi(tmIds, title, body, sender, hubName, template, data, eventId, eventName);
                    var result = new { status = response.StatusCode, content = await response.Content.ReadAsStringAsync() };
                    return result;
                }
                else //定時寄送 [待做]
                {

                }
                
            }
            else if(type.Equals("mail")) //Email
            {
                List<string> tmIds = tmidInfos.Where(i => !string.IsNullOrWhiteSpace(i.mail)).Select(i => i.id).ToList();
                //呼叫Email API [未]
                ///模板設定
                if(_option.Location.Contains("Global"))
                {
                    if (string.IsNullOrWhiteSpace(template)) template = "d-f1c5abd8247f4be79ceaecdd327e9a68"; //若未指定模板ID，用預設模板
                }
                var result = new { };
                return result;
            }
            else if (type.Equals("sms")) //短訊
            {
                List<string> tmIds = tmidInfos.Where(i => !string.IsNullOrWhiteSpace(i.mobile)).Select(i => i.id).ToList();
                //呼叫短訊API [未]
                var result = new { };
                return result;
            }

            return new { };
        }
        //取得學區所屬學校教師
        private async Task<List<string>> GetTmidByAreaId(List<string> areaIds)
        {
            var cosmosClientIes = _azureCosmos.GetCosmosClient();
            //取得學校ID列表
            List<string> schIds = new List<string>();
            string sqlSch = $"SELECT c.id FROM c WHERE ARRAY_CONTAINS({JsonSerializer.Serialize(areaIds)}, c.areaId)";
            ///實體校
            await foreach (var item in cosmosClientIes.GetContainer(Constant.TEAMModelOS, Constant.School).GetItemQueryIteratorSql<JsonElement>(queryText: sqlSch, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey($"Base") }))
            {
                string schId = item.GetProperty("id").ToString();
                schIds.Add(schId);
            }
            List<string> teacherCodes = schIds.Select(s => $"Teacher-{s}").ToList();
            //取得學校教師ID列表
            List<string> tmids = new List<string>();
            string sqlTch = $"SELECT DISTINCT c.id FROM c WHERE c.pk = 'Teacher' AND c.status = 'join' AND ARRAY_CONTAINS({JsonSerializer.Serialize(teacherCodes)}, c.code)";
            await foreach (var item in cosmosClientIes.GetContainer(Constant.TEAMModelOS, Constant.School).GetItemQueryIteratorSql<JsonElement>(queryText: sqlTch, requestOptions: null))
            {
                string tmid = item.GetProperty("id").ToString();
                tmids.Add(tmid);
            }
            return tmids;
        }
        //取得地理資訊所屬TMID
        private async Task<List<string>> GetTmidByGeo(List<Geo> geos)
        {
            List<string> tmids = new List<string>();
            foreach (Geo geo in geos)
            {
                if(!string.IsNullOrWhiteSpace(geo.type) && geo.type.Equals("tmid"))
                {
                    var (geoInfos, _) = await GetDataByGeo("tmid", "tmid", true, geo.countryId, geo.provinceId, geo.cityId);
                    foreach (var geoInfo in geoInfos)
                    {
                        if (!tmids.Contains(geoInfo.id)) tmids.Add(geoInfo.id);
                    }
                }
                else if(!string.IsNullOrWhiteSpace(geo.type) && geo.type.Equals("school"))
                {
                    var (geoInfos, _) = await GetDataByGeo("school", "tmid", true, geo.countryId, geo.provinceId, geo.cityId);
                    foreach (var geoInfo in geoInfos)
                    {
                        if (!tmids.Contains(geoInfo.id)) tmids.Add(geoInfo.id);
                    }
                }
            }
            return tmids;
        }

        //取得學校機構所屬學校教師
        private async Task<List<string>> GetTmidByUnitId(List<string> units)
        {
            if (units.Count.Equals(0)) return new List<string>();

            var cosmosClientIes = _azureCosmos.GetCosmosClient();
            var coreCosmosClientCn = _azureCosmos.GetCosmosClient(name: "CoreServiceV2CnRead");
            //取得IES5學校ID
            string iesSql = "SELECT c.id, c.type FROM c WHERE 1=1 ";
            string iesWhere = string.Empty;
            //IES學校ID取得、學校類型取得
            List<string> iesSchIds = new List<string>();
            Dictionary<string, string> schTypeDic = new Dictionary<string, string>(); //IES5學校ID與學校類型對照表
            List<string> teacherCodes = new List<string>(); //用來取得老師資料的分區鍵
            string sqlSch = $"{iesSql}{iesWhere}";
            await foreach (var item in cosmosClientIes.GetContainer(Constant.TEAMModelOS, Constant.School).GetItemQueryIteratorSql<JsonElement>(queryText: sqlSch, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey($"Base") }))
            {
                string schId = item.GetProperty("id").ToString();
                int schType = item.GetProperty("type").GetInt32();
                iesSchIds.Add(schId);
                if (!schTypeDic.ContainsKey(schId)) schTypeDic.Add(schId, schType.ToString());
            }
            //取得Core學校ID及類型
            string coreSql = "SELECT c.shortCode, c.unitType FROM c";
            string coreWhere = $" WHERE ARRAY_CONTAINS({JsonSerializer.Serialize(iesSchIds)}, c.shortCode) ";
            string coreWhereUnittype = string.Empty;
            if (units.Contains("1"))
            {
                if (!string.IsNullOrWhiteSpace(coreWhereUnittype)) coreWhereUnittype += " OR ";
                coreWhereUnittype += $" ARRAY_CONTAINS(['1','8'], c.unitType) ";
            }
            if (units.Contains("2"))
            {
                if (!string.IsNullOrWhiteSpace(coreWhereUnittype)) coreWhereUnittype += " OR ";
                coreWhereUnittype += $" c.unitType = '2' ";
            }
            if (units.Contains("3"))
            {
                if (!string.IsNullOrWhiteSpace(coreWhereUnittype)) coreWhereUnittype += " OR ";
                coreWhereUnittype += $" c.unitType = '3' ";
            }
            if (units.Contains("4"))
            {
                if (!string.IsNullOrWhiteSpace(coreWhereUnittype)) coreWhereUnittype += " OR ";
                coreWhereUnittype += $" ARRAY_CONTAINS(['4','5','6','7','9'], c.unitType) ";
            }
            if (!string.IsNullOrWhiteSpace(coreWhereUnittype))
            {
                coreWhere += $" AND ({coreWhereUnittype})";
            }
            Dictionary<string, string> coreSchUnitTypeDic = new Dictionary<string, string>(); //Core學校ID與學校類型對照表
            string sqlCore = $"{coreSql}{coreWhere}";
            await foreach (var item in coreCosmosClientCn.GetContainer("Core", "School").GetItemQueryIteratorSql<JsonElement>(queryText: sqlCore, requestOptions: new QueryRequestOptions { PartitionKey = new PartitionKey($"base") }))
            {
                string shortCode = item.GetProperty("shortCode").ToString();
                string unitType = item.GetProperty("unitType").ToString();
                if (!teacherCodes.Contains($"Teacher-{shortCode}")) teacherCodes.Add($"Teacher-{shortCode}");
                if (!coreSchUnitTypeDic.ContainsKey(shortCode)) coreSchUnitTypeDic.Add(shortCode, unitType);
            }
            //學校類型轉換
            Dictionary<string, string> finalSchUnitTypeDic = new Dictionary<string, string>();
            foreach (KeyValuePair<string, string> item in coreSchUnitTypeDic)
            {
                string schId = item.Key;
                string coreUnitType = (coreSchUnitTypeDic.ContainsKey(schId)) ? coreSchUnitTypeDic[schId] : string.Empty;
                string iesUnitType = (schTypeDic.ContainsKey(schId)) ? schTypeDic[schId] : string.Empty;
                string unitTypeF = string.Empty; //機構類型(最終判斷)
                if (!string.IsNullOrWhiteSpace(coreUnitType))
                {
                    switch (coreUnitType)
                    {
                        case "1": //基礎教育機構
                        case "8": //學前教育
                            unitTypeF = "1"; // => 基礎教育機構(K-小學)
                            break;
                        case "2": //中等教育機構
                            unitTypeF = "2"; // => 中等教育機構(國中、高中/職)
                            break;

                        case "3": //高等教育機構
                            unitTypeF = "3"; // => 高等教育機構(大學、研究所)
                            break;
                        case "4": //政府單位機構
                        case "5": //NGO機構
                        case "6": //企業機構
                        case "7": //其他
                        case "9": //特殊教育
                            unitTypeF = "4"; // => 其他
                            break;
                    }
                }
                else if (!string.IsNullOrWhiteSpace(iesUnitType))
                {
                    switch (iesUnitType)  //1 普教，2 高职教
                    {
                        case "1": //國教(K-12)
                            unitTypeF = "1"; // => 基礎教育機構(K-小學)
                            break;
                        case "2": //大專院校
                            unitTypeF = "3"; // => 高等教育機構(大學、研究所)
                            break;
                        default: //未設定
                            unitTypeF = "4"; // => 其他
                            break;
                    }
                }
                finalSchUnitTypeDic.Add(schId, unitTypeF);
            }
            //取得學校所屬老師TMID
            teacherCodes = finalSchUnitTypeDic.Select(s => $"Teacher-{s.Key}").ToList();
            List<string> tmids = new List<string>();
            string sqlTch = $"SELECT DISTINCT c.id FROM c WHERE c.pk = 'Teacher' AND c.status = 'join' AND ARRAY_CONTAINS({JsonSerializer.Serialize(teacherCodes)}, c.code)";
            await foreach (var item in cosmosClientIes.GetContainer(Constant.TEAMModelOS, Constant.School).GetItemQueryIteratorSql<JsonElement>(queryText: sqlTch, requestOptions: null))
            {
                string tmid = item.GetProperty("id").ToString();
                tmids.Add(tmid);
            }
            return tmids;

        }
        //取得學校所屬學校教師
        private async Task<List<string>> GetTmidBySchoolId(List<string> schIds)
        {
            var cosmosClientIes = _azureCosmos.GetCosmosClient();
            //取得學校ID列表
            List<string> teacherCodes = schIds.Select(s => $"Teacher-{s}").ToList();
            //取得學校教師ID列表
            List<string> tmids = new List<string>();
            string sqlTch = $"SELECT DISTINCT c.id FROM c WHERE c.pk = 'Teacher' AND c.status = 'join' AND ARRAY_CONTAINS({JsonSerializer.Serialize(teacherCodes)}, c.code)";
            await foreach (var item in cosmosClientIes.GetContainer(Constant.TEAMModelOS, Constant.School).GetItemQueryIteratorSql<JsonElement>(queryText: sqlTch, requestOptions: null))
            {
                string tmid = item.GetProperty("id").ToString();
                tmids.Add(tmid);
            }
            return tmids;
        }

        /// <summary>
        /// 寄發端外通知
        /// </summary>
        /// <param name="tmIds"></param>
        /// <param name="title"></param>
        /// <param name="body"></param>
        /// <param name="eventId"></param>
        /// <param name="eventName"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        private HttpResponseMessage CallPushNotifyApi(List<string> tmIds, string title, string body, string sender, string hubName, string template, string data, string eventId = "", string eventName = "")
        {
            NotifyData notify = new NotifyData();
            notify.hubName = hubName;
            notify.sender = sender;
//#if DEBUG //測試模式時限制TMID帳號，正式站佈署時不生效
//            List<string> filterTmid = new List<string>() { "1522758684", "1595321354" };
//            tmIds = tmIds.Intersect(filterTmid).ToList();
//#endif
            notify.tags = tmIds.Select(s => $"{s}_{sender}").ToList();
            notify.title = title;
            notify.body = body;
            notify.eventId = eventId;
            notify.eventName = eventName;
            notify.data = data;
            var clientID = _configuration.GetValue<string>("HaBookAuth:CoreService:clientID");
            var clientSecret = _configuration.GetValue<string>("HaBookAuth:CoreService:clientSecret");
            var url = _configuration.GetValue<string>("HaBookAuth:CoreAPI");
            var client = _httpClient;
            string sendSite = (_option.Location.Contains("China")) ? "China" : "Global";
            var token = CoreTokenExtensions.CreateAccessToken(clientID, clientSecret, sendSite).Result;
            if (client.DefaultRequestHeaders.Contains("Authorization"))
            {
                client.DefaultRequestHeaders.Remove("Authorization");
            }
            client.DefaultRequestHeaders.Add("Authorization", $"Bearer {token.AccessToken}");
            HttpResponseMessage responseMessage = client.PostAsJsonAsync($"{url}/service/PushNotify", notify).Result;
            return responseMessage;
        }

        //BI訊息推送DB記入
        private async Task<BINotice> CrtBiNoticeData(BINotice bINotice)
        {
            var cosmosClientIes = _azureCosmos.GetCosmosClient(); //IES
            bINotice.id = Guid.NewGuid().ToString();
            BINotice result = await cosmosClientIes.GetContainer(Constant.TEAMModelOS, "Common").CreateItemAsync<BINotice>(bINotice, new PartitionKey("BINotice"));
            return result;
        }

        private string GetDictionaryKeyByValue(Dictionary<string, string> dic, string value)
        {
            string result = string.Empty;
            foreach(KeyValuePair<string, string> d in dic)
            {
                if(d.Value.Equals(value))
                {
                    result = d.Key;
                }
            }
            return result;
        }
        /// <summary>
        /// 取得TMID資訊 接收參數class
        /// </summary>
        public record GetTmidInfoParam
        {
            public string mode { get; set; } //and or
            public StartEnd creatTime { get; set; }
            public Geo geo { get; set; }
            public Product product { get; set; }
            public StartEnd point { get; set; }
            public List<string> school { get; set; }
            public List<string> tmid { get; set; }
            public bool hasMail { get; set; } = false;
            public record StartEnd
            {
                public long start { get; set; }
                public long end { get; set; }
            }
            public record Product
            {
                public string mode { get; set; } //and or
                public List<string> id { get; set; } = new(); //產品類型
            }
        }
        public record Geo
        {
            public string countryId { get; set; }
            public string provinceId { get; set; }
            public string cityId { get; set; }
            public string distId { get; set; }
            public string type { get; set; } //tmid、school
        }

        /// <summary>
        /// 發送訊息核心邏輯 接收參數class
        /// </summary>
        public record SendMessageParam
        {
            public List<string> area { get; set; } = new(); //學區ID
            public List<Geo> geo { get; set; } = new();
            public List<string> unit { get; set; } = new(); //學校機構
            public List<string> school { get; set; } = new(); //學校ID
            public List<string> tmid { get; set; } = new(); //TMID
        }

        //取得學校機構ID及名稱
        public Dictionary<string, string> getSchoolUnitName(string lang)
        {
            Dictionary<string, string> tw = new Dictionary<string, string>();
            tw.Add("1", "基礎教育機構(K-小學)");
            tw.Add("2", "中等教育機構(國中、高中/職)");
            tw.Add("3", "高等教育機構(大學、研究所)");
            tw.Add("4", "其他");
            Dictionary<string, string> cn = new Dictionary<string, string>();
            cn.Add("1", "基础教育机构(K-小学)");
            cn.Add("2", "中等教育机构(初中、高中/职)");
            cn.Add("3", "高等教育机构(大学、研究生院)");
            cn.Add("4", "其他");
            if(lang.Equals("zh-cn")) return cn;
            else return tw;
        }

        /// <summary>
        /// TMID基本進階資訊(base-ex)
        /// </summary>
        private class TmidInfo
        {
            public string id { get; set; }
            public string name { get; set; }
            public string mobile { get; set; }
            public string mail { get; set; }
            public string countryCode { get; set; }
            public string country { get; set; }
            public string countryName { get; set; }
            public string province { get; set; }
            public string provinceName { get; set; }
            public string city { get; set; }
            public string cityName { get; set; }
            public string dist { get; set; }
            public string distName { get; set; }
            public string schoolCode { get; set; }
            public string schoolCodeW { get; set; }
        }
        /// <summary>
        /// 學區、地理位置、機構類型 回傳信息
        /// </summary>
        public record AreaInfo
        {
            public string id { get; set; }
            public string name { get; set; }
            public int scCnt { get; set; } = 0;
            public int tchCnt { get; set; } = 0;
            public List<AreaSchoolInfo> lists { get; set; } = new();
        }
        /// <summary>
        /// 學區附屬學校
        /// </summary>
        public record AreaSchoolInfo
        {
            public string id { get; set; }
            public string name { get; set; }
            public int tchCnt { get; set; } = 0;
        }
        /// <summary>
        /// TMID個人積分
        /// </summary>
        private record TmidPoints
        {
            public int points { get; set; } = 0; //累積的點數(只加不減)
            public int balance { get; set; } = 0; //可用的點數
            public int level { get; set; } = 0; //等級
        }
        private record IdName
        {
            public string id { get; set; }
            public string name { get; set; }
        }
        private record IdInfo : IdName
        {
            public string mail { get; set; }
            public string mobile { get; set; }
        }
        
        /// <summary>
        /// 替換字串最後一個字
        /// </summary>
        /// <param name="str">目標字串</param>
        /// <param name="target">被替換的字串</param>
        /// <param name="alternative">要換上的字串</param>
        /// <returns></returns>
        public string ReplaceLastMatch(string str, string target, string alternative)
        {
            var pos = str.LastIndexOf(target);
            if (pos >= 0)
                return str.Substring(0, pos) + alternative + str.Substring(pos + target.Length);
            return str;
        }
        //地區要去除的特殊字
        public List<string> comeRemoveStr = new List<string>() { "省", "市", "区", "自治州", "县", "旗", "盟", "回族", "藏族", "羌族", "哈尼族", "彝族", "壮族", "苗族", "维吾尔", "自治", "地區", "區", "縣" };
    }
}