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;


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
        [Authorize(Roles = "IES")]
        [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 showSchool = (jsonElement.TryGetProperty("showSchool", out JsonElement _showSchool)) ? _showSchool.GetBoolean() : false; //是否列出學校
                var cosmosClient = _azureCosmos.GetCosmosClient();
                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);
                    }
                }
                //取得學校教師數
                Dictionary<string, int> schTeacherCntDic = new Dictionary<string, int>();
                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) 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 (showSchool)
                        {
                            AreaSchoolInfo sch = new AreaSchoolInfo() { id = schId, name = schNameDic[schId], tchCnt = tchCnt };
                            areaInfo.schools.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
        [Authorize(Roles = "IES")]
        [AuthToken(Roles = "admin")]
#endif
        public async Task<IActionResult> GetGeos(JsonElement jsonElement)
        {
            regiondata regionData = GetRegionData(); //取得國省市區地理資訊架構
            Dictionary<string, string> geoIdNameDic = new Dictionary<string, string>(); //key: geoId val:geoName
            var cosmosClient = _azureCosmos.GetCosmosClient();
            bool showSchool = (jsonElement.TryGetProperty("showSchool", out JsonElement _showSchool)) ? _showSchool.GetBoolean() : false; //是否列出學校
            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 countryName = string.Empty;
            string provinceName = string.Empty;
            string cityName = string.Empty;
            string distName = string.Empty;
            //bool hasVirtual = (jsonElement.TryGetProperty("virtual", out JsonElement virtualJobj)) ? virtualJobj.GetBoolean() : true; //是否取得虛擬學校
            bool isGlobal = (_option.Location.Contains("Global")) ? true : false;
            List<AreaInfo> geoInfos = new();
            //取得搜尋的國省市名稱
            if (string.IsNullOrWhiteSpace(countryId)) return BadRequest();
            countryName = (regionData.country.ContainsKey(countryId)) ? regionData.country[countryId].name : string.Empty;
            if (string.IsNullOrWhiteSpace(countryName))
            {
                return BadRequest();
            }
            else
            {
                if (isGlobal)
                {
                    provinceId = string.Empty;
                    if (countryId.Equals("TW"))
                    {
                        geoIdNameDic = new Dictionary<string, string>();
                        foreach (KeyValuePair<string, regionbase> item in regionData.city[countryId][countryId.ToLower()])
                        {
                            geoIdNameDic.Add(item.Value.code, item.Value.name);
                        }
                    }
                }
                else
                {
                    geoIdNameDic = new Dictionary<string, string>();
                    foreach (KeyValuePair<string, regionbase> item in regionData.province[countryId])
                    {
                        geoIdNameDic.Add(item.Value.code, item.Value.name);
                    }
                }
            }
            if (regionData.province.ContainsKey(countryId) && !string.IsNullOrWhiteSpace(provinceId))
            {
                provinceName = (regionData.province[countryId].ContainsKey(provinceId)) ? regionData.province[countryId][provinceId].name : string.Empty;
                geoIdNameDic = new Dictionary<string, string>();
                foreach (KeyValuePair<string, regionbase> item in regionData.city[countryId][provinceId])
                {
                    geoIdNameDic.Add(item.Value.code, item.Value.name);
                }
            }
            if (isGlobal)
            {
                if (regionData.city.ContainsKey(countryId))
                {
                    if (countryId.ToLower().Equals("tw"))
                    {
                        if (!string.IsNullOrWhiteSpace(cityId) && regionData.city[countryId][countryId.ToLower()].ContainsKey(cityId))
                        {
                            cityName = regionData.city[countryId][countryId.ToLower()][cityId].name;
                        }
                    }
                }
            } else
            {
                if (regionData.city.ContainsKey(countryId) &&
                    !string.IsNullOrWhiteSpace(provinceId) && regionData.city[countryId].ContainsKey(provinceId) &&
                    !string.IsNullOrWhiteSpace(cityId) && regionData.city[countryId][provinceId].ContainsKey(cityId)
                    )
                {
                    cityName = regionData.city[countryId][provinceId][cityId].name;
                }
            }
            //SQL文製作
            string groupKey = string.Empty;
            string sqlWhere = string.Empty;
            if (!string.IsNullOrWhiteSpace(countryName))
            {
                if (!string.IsNullOrWhiteSpace(sqlWhere)) sqlWhere += $" AND";
                comeRemoveStr.ForEach(c => { countryName = countryName.Replace(c, ""); });
                sqlWhere += $" CONTAINS(c.region, '{countryName}') ";
                groupKey = (isGlobal) ? "city" : "province";
            }
            if (!string.IsNullOrWhiteSpace(provinceName))
            {
                if (!string.IsNullOrWhiteSpace(sqlWhere)) sqlWhere += $" AND";
                comeRemoveStr.ForEach(c => { provinceName = provinceName.Replace(c, ""); });
                sqlWhere += $" CONTAINS(c.province, '{provinceName}') ";
                groupKey = "city";
            }
            if (!string.IsNullOrWhiteSpace(cityName))
            {
                if (!string.IsNullOrWhiteSpace(sqlWhere)) sqlWhere += $" AND";
                comeRemoveStr.ForEach(c => { cityName = cityName.Replace(c, ""); });
                sqlWhere += $" CONTAINS(c.city, '{cityName}') ";
                groupKey = "name";
            }
            Dictionary<string, string> schGeoDic = new Dictionary<string, string>(); //key: schoolId val:統計的geoId
            Dictionary<string, string> schNameDic = new Dictionary<string, string>(); //key: schoolId val:學校名稱
            Dictionary<string, int> schTeacherCntDic = new Dictionary<string, int>(); //key: schoolId val:學校教師數
            if (!string.IsNullOrWhiteSpace(sqlWhere))
            {
                //取得學校ID列表
                List<string> teacherCodes = new List<string>();
                sqlWhere = $" WHERE {sqlWhere}";
                string sqlSch = $"SELECT c.id, c.name, c.region, c.province, c.city, c.dist FROM c {sqlWhere} ";
                ///實體校
                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 geoName = item.GetProperty(groupKey).ToString();
                    if (groupKey.Equals("city") || groupKey.Equals("province"))
                    {
                        if (geoName.Equals("荆州市")) geoName = "荆州市"; //例外處理
                        else comeRemoveStr.ForEach(c => { geoName = geoName.Replace(c, ""); }); //常規處理

                    }
                    string geoId = (!string.IsNullOrWhiteSpace(geoName)) ? geoIdNameDic.FirstOrDefault(g => g.Value.Contains(geoName)).Key : string.Empty;
                    if (!schGeoDic.ContainsKey(schId))
                    {
                        schGeoDic.Add(schId, geoId);
                        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 geoName = item.GetProperty(groupKey).ToString();
                    if (groupKey.Equals("city") || groupKey.Equals("province"))
                    {
                        if (geoName.Equals("荆州市")) geoName = "荆州市"; //例外處理
                        else comeRemoveStr.ForEach(c => { geoName = geoName.Replace(c, ""); }); //常規處理
                    }
                    string geoId = (!string.IsNullOrWhiteSpace(geoName)) ? geoIdNameDic.FirstOrDefault(g => g.Value.Contains(geoName)).Key : string.Empty;
                    if (!schGeoDic.ContainsKey(schId))
                    {
                        schGeoDic.Add(schId, geoId);
                        schNameDic.Add(schId, schName);
                    }
                }

                //取得學校教師數
                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) 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 geoId = schGeoDic[schId];
                AreaInfo geoInfo = geoInfos.Where(g => g.id.Equals(geoId)).FirstOrDefault();
                if (geoInfo == null)
                {
                    AreaInfo geoInfoCrt = new AreaInfo();
                    geoInfoCrt.id = (groupKey.Equals("city") || groupKey.Equals("province")) ? geoId : schId;
                    geoInfoCrt.name = (_option.Location.Contains("Global")) ? "無法取得名稱" : "无法取得名称";
                    if(groupKey.Equals("city") || groupKey.Equals("province"))
                    {
                        if(!string.IsNullOrWhiteSpace(geoId) && geoIdNameDic.ContainsKey(geoId))
                        {
                            geoInfoCrt.name = geoIdNameDic[geoId];
                        }
                    }
                    else if(schNameDic.ContainsKey(schId))
                    {
                        geoInfoCrt.name = schNameDic[schId];
                    }
                    geoInfos.Add(geoInfoCrt);
                    geoInfo = geoInfos.Where(g => g.id.Equals(geoInfoCrt.id)).FirstOrDefault();
                }
                geoInfo.scCnt++;
                geoInfo.tchCnt += tchCnt;
                if (showSchool && (groupKey.Equals("city") || groupKey.Equals("province")))
                {
                    AreaSchoolInfo sch = new AreaSchoolInfo() { id = schId, name = schNameDic[schId], tchCnt = tchCnt };
                    geoInfo.schools.Add(sch);
                }
            }
            //資料整理2 移除無學校學區
            var result = geoInfos.Where(a => a.scCnt > 0).ToList();
            if (groupKey.Equals("name")) groupKey = "school";
            return Ok(new { state = 200, dataType = groupKey, data = result });
        }

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

            string type = (jsonElement.TryGetProperty("type", out JsonElement _type)) ? _type.GetString() : string.Empty; //教育機構類型 1:基礎教育機構(K-小學) 2:中等教育機構(國中、高中/職) 3:高等教育機構(大學、研究所) 4:其他
            bool showSchool = (jsonElement.TryGetProperty("showSchool", out JsonElement _showSchool)) ? _showSchool.GetBoolean() : false; //是否列出學校

            //檢索類型轉換
            string iesSql = "SELECT c.id, c.name, 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與學校類型對照表
            Dictionary<string, string> schNameDic = new Dictionary<string, string>(); //IES5學校ID與學校名稱
            List<string> teacherCodes = new List<string>(); //用來取得老師資料的分區鍵
            string sqlSch = $"{iesSql}{iesWhere}";
            ///實體校
            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();
                int schType = item.GetProperty("type").GetInt32();
                iesSchIds.Add(schId);
                if (!schTypeDic.ContainsKey(schId)) schTypeDic.Add(schId, schType.ToString());
                if (!schNameDic.ContainsKey(schId)) schNameDic.Add(schId, schName);
            }
            //Core 篩選取得的學校、學校類型取得
            string coreSql = "SELECT c.shortCode, c.unitType FROM c";
            string coreWhere = $" WHERE 1=1 AND ARRAY_CONTAINS({JsonSerializer.Serialize(iesSchIds)}, c.shortCode) ";
            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);
            }
            //IES學校數及老師數取得
            Dictionary<string, int> schTeacherCntDic = new Dictionary<string, int>(); //key: schoolId val:學校教師數
            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) 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 有教師則記入
            List<AreaInfo> unitInfos = new();
            foreach (KeyValuePair<string, int> item in schTeacherCntDic)
            {
                string schId = item.Key;
                int tchCnt = item.Value;
                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;
                    }
                }
                string lang = (_option.Location.Contains("China")) ? "zh_cn" : "zh_tw";
                Dictionary<string, string> unitNameDic = getSchoolUnitName(lang);
                string id = unitTypeF;
                string name = unitNameDic[id];
                AreaInfo unitInfo = unitInfos.Where(u => u.id.Equals(id)).FirstOrDefault();
                if (unitInfo == null)
                {
                    AreaInfo unitInfoCrt = new AreaInfo();
                    unitInfoCrt.id = id;
                    unitInfoCrt.name = name;
                    unitInfos.Add(unitInfoCrt);
                    unitInfo = unitInfos.Where(u => u.id.Equals(id)).FirstOrDefault();
                }
                unitInfo.scCnt++;
                unitInfo.tchCnt += tchCnt;
                if (showSchool)
                {
                    AreaSchoolInfo sch = new AreaSchoolInfo() { id = schId, name = schNameDic[schId], tchCnt = tchCnt };
                    unitInfo.schools.Add(sch);
                }
            }
            if (!string.IsNullOrWhiteSpace(type))
            {
                unitInfos = unitInfos.Where(u => u.id.Equals(type)).ToList();
            }

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

        }

        /// <summary>
        /// (個別挑選)取得TMID資訊
        /// </summary>
        [HttpPost("get-tmidinfo")]
#if !DEBUG
        [Authorize(Roles = "IES")]
        [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)
                List<string> passTmidGeo = new List<string>();
                List<string> sqlWhereListEx = new List<string>();
                if (target.geo != null)
                {
                    if (!string.IsNullOrWhiteSpace(target.geo.countryId)) sqlWhereListEx.Add($"c.country = '{target.geo.countryId}'");
                    if (!string.IsNullOrWhiteSpace(target.geo.provinceId)) sqlWhereListEx.Add($"c.province = '{target.geo.provinceId}'");
                    if (!string.IsNullOrWhiteSpace(target.geo.cityId)) sqlWhereListEx.Add($"c.city = '{target.geo.cityId}'");
                }
                ///SQL文生成
                bool useGeoFlg = false;
                string sqlWhereEx = string.Empty;
                foreach (string w in sqlWhereListEx)
                {
                    useGeoFlg = true;
                    sqlWhereEx += $" AND {w}";
                }
                if (!string.IsNullOrWhiteSpace(sqlWhereEx))
                {
                    string sqlEx = $"{sqlSel}{sqlWhereEx}";
                    await foreach (var item in cosmosClientCsv2
                    .GetContainer("Core", "ID2")
                    .GetItemQueryIteratorSql<JsonElement>(sqlEx, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("base-ex") }))
                    {
                        string id = item.GetProperty("id").ToString();
                        if (!passTmidGeo.Contains(id)) passTmidGeo.Add(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();
                    //if (useCrtFlg) passTmid = passTmid.Where(p => passTmidCrt.Contains(p)).ToList();
                    //if (useGeoFlg) passTmid = passTmid.Where(p => passTmidGeo.Contains(p)).ToList();
                    //if (usePrdFlg) passTmid = passTmid.Where(p => passTmidPrd.Contains(p)).ToList();
                    //if (usePntFlg) passTmid = passTmid.Where(p => passTmidPnt.Contains(p)).ToList();
                    //if (useSchFlg) passTmid = passTmid.Where(p => passTmidSch.Contains(p)).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
        [Authorize(Roles = "IES")]
        [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 title = (jsonElement.TryGetProperty("title", out JsonElement _title)) ? _title.ToString() : string.Empty; //標題
            string body = (jsonElement.TryGetProperty("body", out JsonElement _body)) ? _body.ToString() : string.Empty; //內文
            var dataParam = (jsonElement.TryGetProperty("data", out JsonElement _data)) ? _data : new JsonElement { };
            List<NotifyAction> action = (jsonElement.TryGetProperty("action", out JsonElement _action)) ? _action.ToObject<List<NotifyAction>>() : new List<NotifyAction>();

            if (target == null || string.IsNullOrWhiteSpace(type) || string.IsNullOrWhiteSpace(method) || string.IsNullOrWhiteSpace(title) || string.IsNullOrWhiteSpace(body)) return BadRequest();
            string eventKey = "bi-gen-notify";
            string eventId = $"{eventKey}_{_snowflakeId.NextId()}";
            string eventName = "BI send notification";
            DataInNotify data = new DataInNotify();
            if(dataParam.ValueKind != JsonValueKind.Undefined) data.value = dataParam;
            if (action.Count > 0) data.action = action;
            await SendMessageCore(target, type, method, title, body, data, eventId, eventName);

            return Ok(new { state = RespondCode.Ok });
        }
        ///寄發訊息核心邏輯
        private async Task<IActionResult> SendMessageCore(SendMessageParam target, string type, string method, string title, string body, DataInNotify data, 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)";
            await foreach (var item in cosmosClientCsv2
                .GetContainer("Core", "ID2")
                .GetItemQueryIteratorSql<JsonElement>(queryText: sqlBaseInfo, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("base") }))
            {
                IdInfo idInfo = new IdInfo();
                idInfo.id = item.GetProperty("id").ToString();
                idInfo.name = item.GetProperty("name").ToString();
                idInfo.mail = item.GetProperty("mail").ToString();
                idInfo.mobile = item.GetProperty("mobile").ToString();
                tmidInfos.Add(idInfo);
            }

            //寄發訊息
            if (type.Equals("notify")) //端內外通知
            {
                List<string> tmIds = tmidInfos.Select(i => i.id).ToList();
                HttpResponseMessage response = CallPushNotifyApi(tmIds, title, body, data, eventId, eventName);
                var result = new { status = response.StatusCode, content = response.Content };
                return Ok(new { state = RespondCode.Ok, result = result });
            }
            else if(type.Equals("mail")) //Email
            {
                List<string> tmIds = tmidInfos.Where(i => !string.IsNullOrWhiteSpace(i.mail)).Select(i => i.id).ToList();
                //呼叫Email API [未]
                var result = new { };
                return Ok(new { state = RespondCode.Ok, result = 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 Ok(new { state = RespondCode.Ok, result = result });
            }

            return Ok(new { state = RespondCode.Ok, result = 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)
        {
            var cosmosClientCsv2 = _azureCosmos.GetCosmosClient(name: "CoreServiceV2");
            string sqlWhere = string.Empty;
            List<string> sqlWhereOrList = new List<string>();
            foreach (Geo geo in geos)
            {
                string sqlWhereRow = String.Empty;
                if (!string.IsNullOrWhiteSpace(geo.countryId))
                {
                    if (!string.IsNullOrWhiteSpace(sqlWhereRow)) sqlWhereRow += " AND ";
                    sqlWhereRow += $"c.country = '{geo.countryId}'";
                }
                if (!string.IsNullOrWhiteSpace(geo.provinceId))
                {
                    if (!string.IsNullOrWhiteSpace(sqlWhereRow)) sqlWhereRow += " AND ";
                    sqlWhereRow += $"c.province = '{geo.provinceId}'";
                }
                if (!string.IsNullOrWhiteSpace(geo.cityId))
                {
                    if (!string.IsNullOrWhiteSpace(sqlWhereRow)) sqlWhereRow += " AND ";
                    sqlWhereRow += $"c.city = '{geo.cityId}'";
                }
                if (!string.IsNullOrWhiteSpace(sqlWhereRow))
                {
                    sqlWhereOrList.Add(sqlWhereRow);
                }
            }
            if (sqlWhereOrList.Count > 0)
            {
                foreach (string sqlWhereOrRow in sqlWhereOrList)
                {
                    if (!string.IsNullOrWhiteSpace(sqlWhere)) sqlWhere += " OR ";
                    sqlWhere += $"({sqlWhereOrRow})";
                }
            }
            List<string> tmids = new List<string>();
            if (!string.IsNullOrWhiteSpace(sqlWhere))
            {
                string sqlEx = $"SELECT DISTINCT c.id FROM c WHERE {sqlWhere}";
                await foreach (var item in cosmosClientCsv2
                .GetContainer("Core", "ID2")
                .GetItemQueryIteratorSql<JsonElement>(sqlEx, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("base-ex") }))
                {
                    string id = item.GetProperty("id").ToString();
                    tmids.Add(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, DataInNotify data, string eventId = "", string eventName = "")
        {
            NotifyData notify = new NotifyData();
            notify.hubName = "hita5";
            notify.sender = "IES"; //先填IES 若之後有需求要標BI 再修正
#if DEBUG //測試模式時限制TMID帳號，正式站佈署時不生效
            List<string> filterTmid = new List<string>() { "1522758684", "1595321354" };
            tmIds = tmIds.Intersect(filterTmid).ToList();
#endif
            notify.tags = tmIds.Select(s => $"{s}_BI").ToList();
            notify.title = title;
            notify.body = body;
            notify.eventId = eventId;
            notify.eventName = eventName;
            notify.data = data.ToJsonString();
            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;
        }

        /// <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 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 //TMID用
        {
            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>
        /// 學區、地理位置、機構類型 回傳信息
        /// </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> schools { 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>
        /// 呼叫端外通知API用 NotifyData.data格式
        /// </summary>
        private record DataInNotify
        {
            public object value { get; set; } = new();
            public List<NotifyAction> action { get; set; } = new();
        }
        private record NotifyAction
        {
            public string type { get; set; } = "click";
            public string label { get; set; }
            public string url { get; set; }
        }
        //地區要去除的特殊字
        public List<string> comeRemoveStr = new List<string>() { "省", "市", "区", "州", "县", "旗", "盟", "回族", "藏族", "羌族", "哈尼族", "彝族", "壮族", "苗族", "自治", "地區", "區", "縣" };
    }
}