TEAMModelOS/TEAMModelOS/Controllers/Teacher/JointEventController.cs

using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK;
using Microsoft.Extensions.Options;
using TEAMModelOS.Models;
using System.Net.Http;
using Microsoft.AspNetCore.Authorization;
using TEAMModelOS.Filter;
using System.Text.Json;
using System.Threading.Tasks;
using TEAMModelOS.SDK.Extension;
using System.Text;
using System.Collections.Generic;
using TEAMModelOS.SDK.Models;
using Microsoft.Azure.Cosmos;
using System;
using static TEAMModelOS.SDK.Models.JointEvent;
using System.Linq;
using TEAMModelOS.SDK.Models.Service;
using Azure.Messaging.ServiceBus;
using Azure.Storage.Sas;

namespace TEAMModelOS.Controllers.Common
{
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status400BadRequest)]

    [Route("joint")]
    [ApiController]
    public class JointEventController : ControllerBase
    {
        private readonly AzureCosmosFactory _azureCosmos;
        private readonly SnowflakeId _snowflakeId;
        private readonly AzureServiceBusFactory _serviceBus;
        private readonly DingDing _dingDing;
        private readonly Option _option;
        private readonly AzureStorageFactory _azureStorage;
        private readonly AzureRedisFactory _azureRedis;
        private readonly IHttpClientFactory _httpClient;
        public IConfiguration _configuration { get; set; }
        private readonly CoreAPIHttpService _coreAPIHttpService;
        private readonly IWebHostEnvironment _environment;
        public JointEventController(IWebHostEnvironment environment, CoreAPIHttpService coreAPIHttpService, AzureCosmosFactory azureCosmos, AzureServiceBusFactory serviceBus, SnowflakeId snowflakeId, DingDing dingDing,
           IOptionsSnapshot<Option> option, AzureStorageFactory azureStorage, AzureRedisFactory azureRedis, IConfiguration configuration, IHttpClientFactory httpClient)
        {
            _azureCosmos = azureCosmos;
            _serviceBus = serviceBus;
            _snowflakeId = snowflakeId;
            _dingDing = dingDing;
            _option = option?.Value;
            _azureStorage = azureStorage;
            _azureRedis = azureRedis;
            _configuration = configuration;
            _coreAPIHttpService = coreAPIHttpService;
            _environment = environment;
            _httpClient = httpClient;
        }

        /// <summary>
        /// 取得統測活動
        /// </summary>
        [ProducesDefaultResponseType]
        [Authorize(Roles = "IES,HTCommunity")]
        [HttpPost("event/find")]
        public async Task<IActionResult> EventFind(JsonElement request)
        {
            try
            {
                var client = _azureCosmos.GetCosmosClient();
                StringBuilder stringBuilder = new($"SELECT * FROM c WHERE 1=1 ");
                string jointEventId = (request.TryGetProperty("jointEventId", out JsonElement _jointEventId)) ? _jointEventId.ToString() : string.Empty;
                if (!string.IsNullOrWhiteSpace(jointEventId))
                {
                    stringBuilder.Append($" AND c.id = '{jointEventId}' ");
                }
                string creatorId = (request.TryGetProperty("creatorId", out JsonElement _creatorId)) ? _creatorId.ToString() : string.Empty;
                if (!string.IsNullOrWhiteSpace(creatorId))
                {
                    stringBuilder.Append($" AND c.creatorId = '{creatorId}' ");
                }
                if (request.TryGetProperty("name", out JsonElement name) && !string.IsNullOrWhiteSpace($"{name}"))
                {
                    stringBuilder.Append($" AND Contains( c.name , '{name}') =  true ");
                }
                if (request.TryGetProperty("progress", out JsonElement progress) && !string.IsNullOrWhiteSpace($"{progress}"))
                {
                    stringBuilder.Append($" AND c.progress = '{progress}' ");
                }
                if (request.TryGetProperty("geo", out JsonElement _geo))
                {
                    JointEventGeo geo = _geo.ToObject<JointEventGeo>();
                    //國級活動
                    string whereCountry = string.Empty;
                    if (!string.IsNullOrWhiteSpace(geo.countryId)) {
                        whereCountry = $" (c.geo.countryId = '{geo.countryId}' AND ( IS_NULL(c.geo.provinceId) OR c.geo.provinceId = '' ) AND ( IS_NULL(c.geo.cityId) OR c.geo.cityId = '' ) AND ( IS_NULL(c.geo.distId) OR c.geo.distId = '' ) )";
                    }
                    //省級活動
                    string whereProvince = string.Empty;
                    if (!string.IsNullOrWhiteSpace(geo.provinceId))
                    {
                        whereProvince = $" (c.geo.countryId = '{geo.countryId}' AND c.geo.provinceId = '{geo.provinceId}' AND ( IS_NULL(c.geo.cityId) OR c.geo.cityId = '' ) AND ( IS_NULL(c.geo.distId) OR c.geo.distId = '' ) ) ";
                    }
                    //市級活動
                    string whereCity = string.Empty;
                    if (!string.IsNullOrWhiteSpace(geo.cityId))
                    {
                        if(!string.IsNullOrWhiteSpace(geo.provinceId))
                        {
                            whereCity = $"( c.geo.countryId = '{geo.countryId}' AND c.geo.provinceId = '{geo.provinceId}' AND c.geo.cityId = '{geo.cityId}' AND ( IS_NULL(c.geo.distId) OR c.geo.distId = '' ) )";
                        }
                        else
                        {
                            whereCity = $"( c.geo.countryId = '{geo.countryId}' AND ( IS_NULL(c.geo.provinceId) OR c.geo.provinceId = '' ) AND c.geo.cityId = '{geo.cityId}' AND ( IS_NULL(c.geo.distId) OR c.geo.distId = '' ) )";
                        }
                        
                    }
                    //區級活動
                    string whereDist = string.Empty;
                    if (!string.IsNullOrWhiteSpace(geo.distId))
                    {
                        whereDist = $"( c.geo.countryId = '{geo.countryId}' AND c.geo.provinceId = '{geo.provinceId}' AND c.geo.cityId = '{geo.cityId}' AND c.geo.distId = '{geo.distId}' )";
                    }
                    //SQL文整理
                    StringBuilder whereGeo = new();
                    if(!string.IsNullOrWhiteSpace(whereCountry))
                    {
                        if(whereGeo.Length > 0) whereGeo.Append(" OR ");
                        whereGeo.Append(whereCountry);
                    }
                    if (!string.IsNullOrWhiteSpace(whereProvince))
                    {
                        if (whereGeo.Length > 0) whereGeo.Append(" OR ");
                        whereGeo.Append(whereProvince);
                    }
                    if (!string.IsNullOrWhiteSpace(whereCity))
                    {
                        if (whereGeo.Length > 0) whereGeo.Append(" OR ");
                        whereGeo.Append(whereCity);
                    }
                    if (!string.IsNullOrWhiteSpace(whereDist))
                    {
                        if (whereGeo.Length > 0) whereGeo.Append(" OR ");
                        whereGeo.Append(whereDist);
                    }
                    if (whereGeo.Length > 0)
                    {
                        stringBuilder.Append(" AND " + whereGeo);
                    }
                }

                List<JointEventDto> eventInfo = new List<JointEventDto>();
                await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIteratorSql(queryText: stringBuilder.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("JointEvent") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.Content);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            JointEventDto jointEventDto = obj.ToObject<JointEventDto>();// blobsas
                            var (blob_uri, blob_sas) = _azureStorage.GetBlobContainerSAS(jointEventDto.creatorId, BlobContainerSasPermissions.Read | BlobContainerSasPermissions.List );
                            jointEventDto.blobsas = blob_sas;
                            eventInfo.Add(jointEventDto);
                        }
                    }
                }
                return Ok(new { data = eventInfo });
            }
            catch (Exception e)
            {
                //await _dingDing.SendBotMsg($"OS，{_option.Location}，jointevent/find()\n{e.Message}\n{e.StackTrace}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }
        }
        /// <summary>
        /// 新增修改統測活動
        /// </summary>
        [ProducesDefaultResponseType]
        [Authorize(Roles = "IES")]
#if !DEBUG
        [AuthToken(Roles = "teacher")]
#endif
        [HttpPost("event/upsert")]
        public async Task<IActionResult> EventUpsert(JsonElement request)
        {
            try
            {
                var client = _azureCosmos.GetCosmosClient();
                long now = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
                string creatorId = (request.TryGetProperty("creatorId", out JsonElement _creatorId)) ? _creatorId.ToString() : string.Empty;
                string name = (request.TryGetProperty("name", out JsonElement _name)) ? _name.ToString() : string.Empty;
                string jointEventId = (request.TryGetProperty("jointEventId", out JsonElement _jointEventId)) ? _jointEventId.ToString() : string.Empty;
                JointEventGeo geo = (request.TryGetProperty("geo", out JsonElement _geo)) ? _geo.ToObject<JointEventGeo>() : null;
                List<string> admin = (request.TryGetProperty("admin", out JsonElement _admin)) ? _admin.ToObject<List<string>>() : new List<string>();
                long startTime = (request.TryGetProperty("startTime", out JsonElement _startTime)) ? _startTime.GetInt64() : 0;
                long endTime = (request.TryGetProperty("endTime", out JsonElement _endTime)) ? _endTime.GetInt64() : 0;
                //新建統測活動 輸入項檢測 
                if (string.IsNullOrWhiteSpace(creatorId) || string.IsNullOrWhiteSpace(name) || startTime.Equals(0) || endTime.Equals(0))
                {
                    return Ok(new { errCode = "1", err = "Invalid param." });
                }
                if (startTime > endTime)
                {
                    return Ok(new { errCode = "2", err = "Invalid startTime or endTime." });
                }
                JointEvent jointEvent = new JointEvent();
                if (string.IsNullOrWhiteSpace(jointEventId))
                {
                    jointEvent.id = Guid.NewGuid().ToString();
                    jointEvent.creatorId = creatorId;
                    jointEvent.code = "JointEvent";
                    jointEvent.createTime = now;
                }
                else
                {
                    jointEvent = await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReadItemAsync<JointEvent>(jointEventId, new PartitionKey("JointEvent"));
                    if (jointEvent == null)
                    {
                        return Ok(new { errCode = "3", err = "Invalid jointEventId." });
                    }
                }
                jointEvent.name = name;
                jointEvent.startTime = startTime;
                jointEvent.endTime = endTime;
                jointEvent.progress = (startTime <= now && now <= endTime) ? "going" : (now < startTime) ? "pending" : "finish";
                if (geo != null) jointEvent.geo = geo;
                if (admin.Count > 0) jointEvent.admin = admin;

                await client.GetContainer(Constant.TEAMModelOS, "Teacher").UpsertItemAsync<JointEvent>(jointEvent, new PartitionKey("JointEvent"));
                return Ok(new { errCode = "", err = "", jointEvent });
            }
            catch (Exception e)
            {
                //await _dingDing.SendBotMsg($"OS，{_option.Location}，jointevent/find()\n{e.Message}\n{e.StackTrace}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }
        }
        /// <summary>
        /// 新增修改統測活動分組
        /// </summary>
        [ProducesDefaultResponseType]
        [Authorize(Roles = "IES")]
#if !DEBUG
        [AuthToken(Roles = "teacher")]
#endif
        [HttpPost("event-group/upsert")]
        public async Task<IActionResult> EventGroupUpsert(JsonElement request)
        {
            try
            {
                string errCode = string.Empty;
                string err = string.Empty;
                var client = _azureCosmos.GetCosmosClient();
                string jointEventId = (request.TryGetProperty("jointEventId", out JsonElement _jointEventId)) ? _jointEventId.ToString() : string.Empty;
                if (string.IsNullOrWhiteSpace(jointEventId)) return BadRequest();
                List<JointEventGroup> groups = (request.TryGetProperty("groups", out JsonElement _groups)) ? _groups.ToObject<List<JointEventGroup>>() : new List<JointEventGroup>();
                if (groups.Count.Equals(0)) return BadRequest();
                JointEvent jointEvent = await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReadItemAsync<JointEvent>(jointEventId, new PartitionKey("JointEvent"));
                if (jointEvent == null)
                {
                    return Ok(new { errCode = "3", err = "Invalid jointEventId." });
                }
                foreach (JointEventGroup groupRow in groups)
                {
                    if (!string.IsNullOrWhiteSpace(groupRow.id))
                    {
                        JointEventGroup groupNow = jointEvent.groups.Where(g => g.id.Equals(groupRow.id)).FirstOrDefault();
                        if (groupNow == null)
                        {
                            errCode = "4";
                            err += $"Invalid groupId:{groupRow.id}. \r\n";
                            continue;
                        }
                        groupNow.name = groupRow.name;
                        groupNow.assistants = groupRow.assistants;
                    }
                    else
                    {
                        JointEventGroup groupNow = new JointEventGroup();
                        groupNow.id = Guid.NewGuid().ToString();
                        groupNow.name = groupRow.name;
                        groupNow.assistants = groupRow.assistants;
                        jointEvent.groups.Add(groupNow);
                    }
                }

                await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReplaceItemAsync<JointEvent>(jointEvent, jointEvent.id, new PartitionKey("JointEvent"));
                return Ok(new { errCode, err, jointEvent });
            }
            catch (Exception e)
            {
                //await _dingDing.SendBotMsg($"OS，{_option.Location}，jointevent/find()\n{e.Message}\n{e.StackTrace}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }
        }
        /// <summary>
        /// 刪除統測活動分組
        /// </summary>
        [ProducesDefaultResponseType]
        [Authorize(Roles = "IES")]
#if !DEBUG
        [AuthToken(Roles = "teacher")]
#endif
        [HttpPost("event-group/delete")]
        public async Task<IActionResult> EventGroupDelete(JsonElement request)
        {
            var client = _azureCosmos.GetCosmosClient();
            string jointEventId = (request.TryGetProperty("jointEventId", out JsonElement _jointEventId)) ? _jointEventId.ToString() : string.Empty;
            if (string.IsNullOrWhiteSpace(jointEventId)) return BadRequest();
            string groupId = (request.TryGetProperty("groupId", out JsonElement _groupId)) ? _groupId.GetString() : string.Empty;
            if (string.IsNullOrWhiteSpace(groupId)) return BadRequest();
            JointEvent jointEvent = await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReadItemAsync<JointEvent>(jointEventId, new PartitionKey("JointEvent"));
            if (jointEvent == null)
            {
                return Ok(new { errCode = "3", err = "Invalid jointEventId." });
            }
            JointEventGroup group = jointEvent.groups.Where(g => g.id.Equals(groupId)).FirstOrDefault();
            if (group == null)
            {
                return Ok(new { errCode = "4", err = "Invalid groupId." });
            }
            jointEvent.groups.Remove(group);
            await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReplaceItemAsync<JointEvent>(jointEvent, jointEvent.id, new PartitionKey("JointEvent"));
            return Ok(new { errCode = "", err = "", jointEvent });
        }

        /// <summary>
        /// 新增修改統測行程
        /// </summary>
        [ProducesDefaultResponseType]
        [Authorize(Roles = "IES")]
#if !DEBUG
        [AuthToken(Roles = "teacher")]
#endif
        [HttpPost("schedule/upsert")]
        public async Task<IActionResult> EventScheduleUpsert(JsonElement request)
        {
            try
            {
                string errCode = string.Empty;
                string err = string.Empty;
                var client = _azureCosmos.GetCosmosClient();
                long now = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
                string jointEventId = (request.TryGetProperty("jointEventId", out JsonElement _jointEventId)) ? _jointEventId.ToString() : string.Empty;
                if (string.IsNullOrWhiteSpace(jointEventId)) return BadRequest();
                List<JointEventSchedule> schedules = (request.TryGetProperty("schedules", out JsonElement _schedules)) ? _schedules.ToObject<List<JointEventSchedule>>() : new List<JointEventSchedule>();
                if (schedules.Count.Equals(0)) return BadRequest();
                var sbm = new List<ServiceBusMessage>(); //ServiceBus訊息列表

                //取得活動本體
                JointEvent jointEvent = await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReadItemAsync<JointEvent>(jointEventId, new PartitionKey("JointEvent"));
                if (jointEvent == null)
                {
                    return Ok(new { errCode = "3", err = "Invalid jointEventId." });
                }
                //取得活動Schedule
                foreach (JointEventSchedule schedule in schedules)
                {
                    string mode = "add";
                    JointEventSchedule jointEventSchedule = new JointEventSchedule();
                    if (!string.IsNullOrWhiteSpace(schedule.id))
                    {
                        jointEventSchedule = jointEvent.schedule.Where(s => s.id.Equals(schedule.id)).FirstOrDefault();
                        if (jointEventSchedule == null)
                        {
                            errCode = "5";
                            err += $"Invalid scheduleId:{schedule.id}. \r\n";
                            continue;
                        }
                        if (jointEventSchedule.progress.Equals("going"))
                        {
                            errCode = "10";
                            err += $"JointSchedule (id:{schedule.id}) is going:, can't be changed. \r\n";
                            continue;
                        }
                        mode = "upd";
                    }
                    else
                    {
                        jointEventSchedule.id = Guid.NewGuid().ToString();
                    }
                    jointEventSchedule.name = schedule.name;
                    jointEventSchedule.type = schedule.type;
                    jointEventSchedule.examType = (schedule.type.Equals("join")) ? null : schedule.examType;
                    jointEventSchedule.examOverwrite = schedule.examOverwrite;
                    long startTimeOld = jointEventSchedule.startTime;
                    long endTimeOld = jointEventSchedule.endTime;
                    jointEventSchedule.startTime = schedule.startTime;
                    jointEventSchedule.endTime = schedule.endTime;
                    jointEventSchedule.progress = (schedule.startTime <= now && now <= schedule.endTime) ? "going" : (now < schedule.startTime) ? "pending" : "finish";
                    jointEventSchedule.orderby = schedule.orderby;
                    if (schedule.location != null) jointEventSchedule.location = schedule.location;
                    if (schedule.description != null) jointEventSchedule.description = schedule.description;
                    if (schedule.blobs != null) jointEventSchedule.blobs = schedule.blobs;
                    if (mode.Equals("add"))
                    {
                        jointEvent.schedule.Add(jointEventSchedule);
                    }
                    //製作Schedule progress更新訊息(active-task)
                    bool sendScheduleMsg = (!startTimeOld.Equals(jointEventSchedule.startTime) || !endTimeOld.Equals(jointEventSchedule.endTime) ) ? true : false; //是否發送變更Schedule.progress訊息
                    if(sendScheduleMsg)
                    {
                        var msg = new ServiceBusMessage(new { jointEventId = $"{jointEvent.id}", jointScheduleId = $"{jointEventSchedule.id}", progress = jointEventSchedule.progress }.ToJsonString());
                        msg.ApplicationProperties.Add("name", "JointEventSchedule");
                        sbm.Add(msg);
                    }
                }
                //排序
                jointEvent.schedule = jointEvent.schedule.OrderBy(s => s.orderby).ToList();
                int index = 0;
                foreach (JointEventSchedule schedule in jointEvent.schedule)
                {
                    schedule.orderby = index;
                    index++;
                }

                //DB更新:JointEvent
                await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReplaceItemAsync<JointEvent>(jointEvent, jointEvent.id, new PartitionKey("JointEvent"));

                //批量發送Schedule progress消息
                if(sbm.Count > 0)
                {
                    var s = await _serviceBus.GetServiceBusClient().SendBatchMessageAsync(_configuration.GetValue<string>("Azure:ServiceBus:ActiveTask"), sbm);
                }

                return Ok(new { errCode, err, jointEvent });
            }
            catch (Exception e)
            {
                //await _dingDing.SendBotMsg($"OS，{_option.Location}，jointevent/find()\n{e.Message}\n{e.StackTrace}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }
        }
        /// <summary>
        /// 刪除統測統測行程
        /// </summary>
        [ProducesDefaultResponseType]
        [Authorize(Roles = "IES")]
#if !DEBUG
        [AuthToken(Roles = "teacher")]
#endif
        [HttpPost("schedule/delete")]
        public async Task<IActionResult> EventScheduleDelete(JsonElement request)
        {
            try
            {
                var client = _azureCosmos.GetCosmosClient();
                string jointEventId = (request.TryGetProperty("jointEventId", out JsonElement _jointEventId)) ? _jointEventId.ToString() : string.Empty;
                if (string.IsNullOrWhiteSpace(jointEventId)) return BadRequest();
                string scheduleId = (request.TryGetProperty("scheduleId", out JsonElement _scheduleId)) ? _scheduleId.ToString() : string.Empty;
                if (string.IsNullOrWhiteSpace(scheduleId)) return BadRequest();

                //取得活動本體
                JointEvent jointEvent = await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReadItemAsync<JointEvent>(jointEventId, new PartitionKey("JointEvent"));
                if (jointEvent == null)
                {
                    return Ok(new { errCode = "3", err = "Invalid jointEventId." });
                }
                //取得活動Schedule
                JointEventSchedule jointEventSchedule = jointEvent.schedule.Where(s => s.id.Equals(scheduleId)).FirstOrDefault();
                if (jointEventSchedule == null)
                {
                    return Ok(new { errCode = "5", err = "Invalid scheduleId." });
                }
                else
                {
                    jointEvent.schedule.Remove(jointEventSchedule);
                    //排序
                    jointEvent.schedule = jointEvent.schedule.OrderBy(s => s.orderby).ToList();
                    int index = 0;
                    foreach (JointEventSchedule schedule in jointEvent.schedule)
                    {
                        schedule.orderby = index;
                        index++;
                    }

                    //DB更新:JointEvent
                    await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReplaceItemAsync<JointEvent>(jointEvent, jointEvent.id, new PartitionKey("JointEvent"));
                    return Ok(new { errCode = "", err = "", jointEvent });
                }
            }
            catch (Exception e)
            {
                //await _dingDing.SendBotMsg($"OS，{_option.Location}，jointevent/find()\n{e.Message}\n{e.StackTrace}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 取得統測教師課程及組別
        /// </summary>
        [ProducesDefaultResponseType]
        [Authorize(Roles = "IES,HTCommunity")]
        [HttpPost("course/find")]
        public async Task<IActionResult> JointCourseFind(JsonElement request)
        {
            try {
                var client = _azureCosmos.GetCosmosClient();
                List<JointEventGroupDto> JointCourseDto = new List<JointEventGroupDto>();
                StringBuilder stringBuilder = new($"SELECT * FROM c WHERE 1=1 ");
                string jointEventId = (request.TryGetProperty("jointEventId", out JsonElement _jointEventId)) ? _jointEventId.ToString() : string.Empty;
                string jointGroupId = (request.TryGetProperty("jointGroupId", out JsonElement _jointGroupId)) ? _jointGroupId.ToString() : string.Empty;
                string type = (request.TryGetProperty("type", out JsonElement _type)) ? _type.ToString() : "regular"; //預設取得報名名單
                if (string.IsNullOrWhiteSpace(jointEventId)) return BadRequest();
                //取得活動
                JointEvent jointEvent = await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReadItemAsync<JointEvent>(jointEventId, new PartitionKey("JointEvent"));
                if (jointEvent == null)
                {
                    return Ok(new { errCode = "3", err = "Invalid jointEventId.", data = JointCourseDto });
                }
                //取得報名課程
                stringBuilder.Append($" AND c.jointEventId = '{jointEventId}' ");
                if (!string.IsNullOrWhiteSpace(jointGroupId))
                {
                    stringBuilder.Append($" AND c.jointGroupId = '{jointGroupId}' ");
                }
                string creatorId = (request.TryGetProperty("creatorId", out JsonElement _creatorId)) ? _creatorId.ToString() : string.Empty;
                if (!string.IsNullOrWhiteSpace(creatorId))
                {
                    stringBuilder.Append($" AND c.creatorId = '{creatorId}' ");
                }
                if(type.Equals("regular"))
                {
                    stringBuilder.Append($" AND (c.type = 'regular' OR NOT IS_DEFINED(c.type) OR IS_NULL(c.type)) ");
                }
                else
                {
                    stringBuilder.Append($" AND c.type = '{type}' ");
                }
                await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIteratorSql(queryText: stringBuilder.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("JointCourse") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.Content);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            JointEventGroupDto jointEventGroupDto = obj.ToObject<JointEventGroupDto>();
                            foreach(JointEventGroupDto.JointEventGroupCourseDto course in jointEventGroupDto.courseLists)
                            {
                                foreach(JointEventGroupDto.JointEventGroupCourseGroupDto group in course.groupLists)
                                {
                                    foreach(JointEventSchedule schedule in jointEvent.schedule)
                                    {
                                        string scheduleStatus = await GetGroupFinishJointSchedule(jointEventGroupDto.jointEventId, jointEventGroupDto.jointGroupId, group.id, schedule);
                                        group.schedule.Add(new {id = schedule.id, name = schedule.name, status = scheduleStatus });
                                    }
                                }
                            }
                            JointCourseDto.Add(jointEventGroupDto);
                        }
                    }
                }

                return Ok(new { data = JointCourseDto });
            }
            catch (Exception e)
            {
                //await _dingDing.SendBotMsg($"OS，{_option.Location}，jointevent/jointcourse/find()\n{e.Message}\n{e.StackTrace}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }
        }
        /// <summary>
        /// 新建修改教師課程及組別
        /// </summary>
        [ProducesDefaultResponseType]
        [Authorize(Roles = "IES,HTCommunity")]
        [HttpPost("course/upsert")]
        public async Task<IActionResult> JointCourseUpsert(JsonElement request)
        {
            try
            {
                var client = _azureCosmos.GetCosmosClient();
                string jointEventId = (request.TryGetProperty("jointEventId", out JsonElement _jointEventId)) ? _jointEventId.ToString() : string.Empty;
                string jointGroupId = (request.TryGetProperty("jointGroupId", out JsonElement _jointGroupId)) ? _jointGroupId.ToString() : string.Empty;
                string creatorId = (request.TryGetProperty("creatorId", out JsonElement _creatorId)) ? _creatorId.ToString() : string.Empty;
                string creatorName = (request.TryGetProperty("creatorName", out JsonElement _creatorName)) ? _creatorName.ToString() : string.Empty;
                string creatorEmail = (request.TryGetProperty("creatorEmail", out JsonElement _creatorEmail)) ? _creatorEmail.ToString() : string.Empty;
                string schoolId = (request.TryGetProperty("schoolId", out JsonElement _schoolId)) ? _schoolId.ToString() : string.Empty;
                string schoolName = (request.TryGetProperty("schoolName", out JsonElement _schoolName)) ? _schoolName.ToString() : string.Empty;
                string scope = (request.TryGetProperty("scope", out JsonElement _scope)) ? _scope.ToString() : string.Empty;
                List<JointEventGroupBase.JointEventGroupCourse> courseLists = (request.TryGetProperty("courseLists", out JsonElement _courseLists)) ? _courseLists.ToObject<List<JointEventGroupBase.JointEventGroupCourse>>() : null;
                if (string.IsNullOrWhiteSpace(jointEventId) || string.IsNullOrWhiteSpace(jointGroupId) || string.IsNullOrWhiteSpace(creatorId) || courseLists == null || string.IsNullOrWhiteSpace(scope))
                {
                    return Ok(new { errCode = "1", err = "Invalid param." });
                }
                JointEventGroupDb jointCourse = new JointEventGroupDb();
                StringBuilder stringBuilder = new($"SELECT * FROM c WHERE c.jointEventId = '{jointEventId}' AND c.jointGroupId = '{jointGroupId}' AND c.creatorId = '{creatorId}' ");
                await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIteratorSql(queryText: stringBuilder.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("JointCourse") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.Content);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            jointCourse = obj.ToObject<JointEventGroupDb>();
                        }
                    }
                }
                //刪除
                if (!string.IsNullOrWhiteSpace(jointCourse.id) && courseLists.Count.Equals(0))
                {
                    await client.GetContainer(Constant.TEAMModelOS, "Teacher").DeleteItemAsync<JointEventGroupDb>(jointCourse.id, new PartitionKey("JointCourse"));
                    return Ok(new { errCode = "", err = "", jointCourse = new { } });
                }
                //新建修改
                else
                {
                    //新建
                    if (string.IsNullOrWhiteSpace(jointCourse.id))
                    {
                        jointCourse.scope = scope;
                        jointCourse.id = Guid.NewGuid().ToString();
                        jointCourse.code = "JointCourse";
                        jointCourse.pk = "JointCourse";
                        jointCourse.jointEventId = jointEventId;
                        jointCourse.jointGroupId = jointGroupId;
                        jointCourse.creatorId = creatorId;
                        jointCourse.creatorName = creatorName;
                        jointCourse.creatorEmail = creatorEmail;
                        jointCourse.schoolId = schoolId;
                        jointCourse.schoolName = schoolName;
                    }
                    //修改
                    jointCourse.courseLists = courseLists;
                    jointCourse.type = "regular"; //教師報名名單
                    await client.GetContainer(Constant.TEAMModelOS, "Teacher").UpsertItemAsync<JointEventGroupDb>(jointCourse, new PartitionKey("JointCourse"));
                }

                //取得該老師報名該活動該組別且progress="going"的JointExam並生成Exam
                List<JointExam> jointExams = new List<JointExam>();
                ///個人
                if (jointCourse.scope.Equals("private"))
                {
                    StringBuilder sqlJointExam = new($"SELECT * FROM c WHERE c.jointEventId = '{jointCourse.jointEventId}' AND c.jointGroupId = '{jointCourse.jointGroupId}' AND c.progress = 'going' AND c.examType = 'regular' ");
                    await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryStreamIteratorSql(queryText: sqlJointExam.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("JointExam") }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.Content);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        {
                            foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                jointExams.Add(obj.ToObject<JointExam>());
                            }
                        }
                    }
                }
                ///學校[待做]
                else if (scope.Equals("school"))
                {

                }
                //Exam生成邏輯
                if (jointExams.Count > 0)
                {
                    foreach (var info in jointExams)
                    {
                        //生成該報名教師的統測活動個人評量
                        await JointService.GenerateExamFromJointExamAsync(client, _azureStorage, _serviceBus, _coreAPIHttpService, _azureRedis, _configuration, _dingDing, info, creatorId);
                    }
                }

                return Ok(new { errCode = "", err = "", jointCourse });
            }
            catch (Exception e)
            {
                //await _dingDing.SendBotMsg($"OS，{_option.Location}，jointevent/jointcourse/find()\n{e.Message}\n{e.StackTrace}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }
        }
        /// <summary>
        /// 刪除教師課程及組別
        /// </summary>
        [ProducesDefaultResponseType]
        [Authorize(Roles = "IES")]
#if !DEBUG
        [AuthToken(Roles = "teacher")]
#endif
        [HttpPost("course/delete")]
        public async Task<IActionResult> JointCourseDelete(JsonElement request)
        {
            var client = _azureCosmos.GetCosmosClient();
            string jointCourseId = (request.TryGetProperty("jointCourseId", out JsonElement _jointCourseId)) ? _jointCourseId.ToString() : string.Empty;
            if (string.IsNullOrWhiteSpace(jointCourseId))
            {
                return BadRequest();
            }
            await client.GetContainer(Constant.TEAMModelOS, "Teacher").DeleteItemAsync<JointEventGroupDb>(jointCourseId, new PartitionKey("JointCourse"));
            return Ok(new { errCode = "", err = "" });
        }

        /// <summary>
        /// 取得統測評量
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [Authorize(Roles = "IES,HTCommunity")]
        [HttpPost("exam/find")]
        public async Task<IActionResult> ExamFind(JsonElement request)
        {
            try
            {
                var client = _azureCosmos.GetCosmosClient();

                StringBuilder stringBuilder = new($"SELECT * FROM c WHERE 1=1 ");
                string jointEventId = (request.TryGetProperty("jointEventId", out JsonElement _jointEventId)) ? _jointEventId.ToString() : string.Empty;
                if (string.IsNullOrWhiteSpace(jointEventId)) return BadRequest();
                stringBuilder.Append($" AND c.jointEventId = '{jointEventId}' ");
                string creatorId = (request.TryGetProperty("creatorId", out JsonElement _creatorId)) ? _creatorId.ToString() : string.Empty;
                if (!string.IsNullOrWhiteSpace(creatorId))
                {
                    stringBuilder.Append($" AND c.creatorId = '{creatorId}' ");
                }
                string jointScheduleId = (request.TryGetProperty("jointScheduleId", out JsonElement _jointScheduleId)) ? _jointScheduleId.ToString() : string.Empty;
                if (!string.IsNullOrWhiteSpace(jointScheduleId))
                {
                    stringBuilder.Append($" AND c.jointScheduleId = '{jointScheduleId}' ");
                }
                string jointGroupId = (request.TryGetProperty("jointGroupId", out JsonElement _jointGroupId)) ? _jointGroupId.ToString() : string.Empty;
                if (!string.IsNullOrWhiteSpace(jointGroupId))
                {
                    stringBuilder.Append($" AND c.jointGroupId = '{jointGroupId}' ");
                }
                if (request.TryGetProperty("stime", out JsonElement _stime))
                {
                    if (long.TryParse($"{_stime}", out long stime))
                    {
                        stringBuilder.Append($" AND  c.createTime >= {stime} ");

                    };
                };
                if (request.TryGetProperty("etime", out JsonElement _etime))
                {
                    if (long.TryParse($"{_etime}", out long etime))
                    {
                        stringBuilder.Append($" AND  c.createTime <= {etime} ");
                    };
                };
                if (request.TryGetProperty("name", out JsonElement name) && !string.IsNullOrWhiteSpace($"{name}"))
                {
                    stringBuilder.Append($" AND Contains( c.name , '{name}') =  true ");
                }
                if (request.TryGetProperty("progress", out JsonElement progress) && !string.IsNullOrWhiteSpace($"{progress}"))
                {
                    stringBuilder.Append($" AND c.progress = '{progress}' ");
                }
                if (request.TryGetProperty("source", out JsonElement source) && !string.IsNullOrWhiteSpace($"{source}"))
                {
                    stringBuilder.Append($" AND c.source = '{source}' ");
                }
                stringBuilder.Append("order by c.createTime desc");
                //string token = null;
                string token = default;
                //默认不指定返回大小
                int? topcout = null;
                if (request.TryGetProperty("count", out JsonElement jcount))
                {
                    if (!jcount.ValueKind.Equals(JsonValueKind.Undefined) && !jcount.ValueKind.Equals(JsonValueKind.Null) && jcount.TryGetInt32(out int data))
                    {
                        topcout = data;
                    }
                }
                //是否需要进行分页查询，默认不分页
                bool iscontinuation = false;
                if (topcout != null && topcout.Value > 0)
                {
                    iscontinuation = true;
                }
                //如果指定了返回大小
                if (request.TryGetProperty("token", out JsonElement token_1))
                {
                    if (!token_1.ValueKind.Equals(JsonValueKind.Null) && token_1.ValueKind.Equals(JsonValueKind.String))
                    {
                        token = token_1.GetString();
                    }
                }
                List<JointExamIdCollector> jointExamIdCollectorsPrivate = new List<JointExamIdCollector>();
                List<JointExamIdCollector> jointExamIdCollectorsSchool = new List<JointExamIdCollector>();
                List<JointExam> jointExamInfo = new List<JointExam>();
                await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "Common").GetItemQueryStreamIteratorSql(queryText: stringBuilder.ToString(), continuationToken: token, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("JointExam"), MaxItemCount = topcout }))
                {
                    using var json = await JsonDocument.ParseAsync(item.Content);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            JointExam jointExamRow = obj.ToObject<JointExam>();
                            //取得JointCourse用ID束
                            if(jointExamRow.scope.Equals("private"))
                            {
                                JointExamIdCollector jointExamIdCollectorRowNow = jointExamIdCollectorsPrivate.Where(c => c.jointEventId.Equals(jointExamRow.jointEventId) && c.jointGroupId.Equals(jointExamRow.jointGroupId)).FirstOrDefault();
                                if (jointExamIdCollectorRowNow == null)
                                {
                                    JointExamIdCollector jointExamIdCollectorRow = new JointExamIdCollector();
                                    jointExamIdCollectorRow.jointEventId = jointExamRow.jointEventId;
                                    jointExamIdCollectorRow.jointGroupId = jointExamRow.jointGroupId;
                                    jointExamIdCollectorsPrivate.Add(jointExamIdCollectorRow);
                                }
                            }
                            else
                            {
                                JointExamIdCollector jointExamIdCollectorRowNow = jointExamIdCollectorsSchool.Where(c => c.jointEventId.Equals(jointExamRow.jointEventId) && c.jointGroupId.Equals(jointExamRow.jointGroupId)).FirstOrDefault();
                                if (jointExamIdCollectorRowNow == null)
                                {
                                    JointExamIdCollector jointExamIdCollectorRow = new JointExamIdCollector();
                                    jointExamIdCollectorRow.jointEventId = jointExamRow.jointEventId;
                                    jointExamIdCollectorRow.jointGroupId = jointExamRow.jointGroupId;
                                    jointExamIdCollectorsSchool.Add(jointExamIdCollectorRow);
                                }
                            }
                            //jointExam列表
                            jointExamInfo.Add(jointExamRow);
                        }
                    }
                    if (iscontinuation)
                    {
                        token = item.ContinuationToken;
                        break;
                    }

                }
                //取得Exam
                List<ExamInfo> exams = new List<ExamInfo>();
                List<string> jointExamIds = jointExamInfo.Select(x => x.id).ToList();
                if (jointExamIds.Count > 0)
                {
                    StringBuilder stringBuilderExam = new($"SELECT * FROM c WHERE CONTAINS(c.code, 'Exam-', true) AND ARRAY_CONTAINS({JsonSerializer.Serialize(jointExamIds)}, c.jointExamId)");
                    await foreach (var item in client.GetContainer(Constant.TEAMModelOS, Constant.Common).GetItemQueryStreamIteratorSql(queryText: stringBuilderExam.ToString(), requestOptions: null))
                    {
                        using var json = await JsonDocument.ParseAsync(item.Content);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        {
                            foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                exams.Add(obj.ToObject<ExamInfo>());
                            }
                        }
                    }
                }
                //取得老師報名的JointCourse
                List<JointEventGroupDb> jointEventGroup = new List<JointEventGroupDb>(); //個人課程 ※報名、決賽 一起取，最後整理時再分
                Dictionary<string, Dictionary<string,string>> courseToExamIdRegularDic = new Dictionary<string, Dictionary<string, string>>(); //活動評量ID => 課程ID、個人評量ID對應表 (報名)
                Dictionary<string, Dictionary<string, string>> courseToExamIdCustomDic = new Dictionary<string, Dictionary<string, string>>(); //活動評量ID => 課程ID、個人評量ID對應表 (決賽)
                if (jointExamIdCollectorsPrivate.Count > 0)
                {
                    StringBuilder stringBuilderEventGroup = new($"SELECT * FROM c ");
                    StringBuilder Where = new();
                    foreach (JointExamIdCollector jointExamIdCollector in jointExamIdCollectorsPrivate)
                    {
                        if (Where.Length > 0)
                        {
                            Where.Append(" OR ");
                        }
                        Where.Append($" ( c.jointEventId = '{jointExamIdCollector.jointEventId}' AND c.jointGroupId = '{jointExamIdCollector.jointGroupId}' ) ");
                    }
                    stringBuilderEventGroup.Append(" WHERE ").Append(Where);
                    await foreach (var item in client.GetContainer(Constant.TEAMModelOS, Constant.Teacher).GetItemQueryStreamIteratorSql(queryText: stringBuilderEventGroup.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("JointCourse") }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.Content);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        {
                            foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                JointEventGroupDb jointEventGroupRow = obj.ToObject<JointEventGroupDb>();
                                foreach(var course in jointEventGroupRow.courseLists)
                                {
                                    if(jointEventGroupRow.scope.Equals("private") && string.IsNullOrWhiteSpace(course.subjectId) && !string.IsNullOrWhiteSpace(course.courseId)) course.subjectId = course.courseId;
                                    
                                    //課程ID => 活動ID、個人評量ID對應表製作
                                    List<ExamInfo> examByCreator = exams.Where(e => e.subjects.Select(s => s.id).ToList().Contains(course.courseId) && e.creatorId.Equals(jointEventGroupRow.creatorId)).ToList();
                                    if(!string.IsNullOrWhiteSpace(jointEventGroupRow.type) && jointEventGroupRow.type.Equals("custom")) //決賽
                                    {
                                        foreach (ExamInfo exam in examByCreator)
                                        {
                                            if(!courseToExamIdCustomDic.ContainsKey(exam.jointExamId))
                                            {
                                                courseToExamIdCustomDic.Add(exam.jointExamId, new Dictionary<string, string>());
                                            }
                                            if (!courseToExamIdCustomDic[exam.jointExamId].ContainsKey(course.courseId))
                                            {
                                                courseToExamIdCustomDic[exam.jointExamId].Add(course.courseId, exam.id);
                                            }
                                        }
                                    }
                                    else //報名
                                    {
                                        foreach (ExamInfo exam in examByCreator)
                                        {
                                            if (!courseToExamIdRegularDic.ContainsKey(exam.jointExamId))
                                            {
                                                courseToExamIdRegularDic.Add(exam.jointExamId, new Dictionary<string, string>());
                                            }
                                            if (!courseToExamIdRegularDic[exam.jointExamId].ContainsKey(course.courseId))
                                            {
                                                courseToExamIdRegularDic[exam.jointExamId].Add(course.courseId, exam.id);
                                            }
                                        }
                                    }
                                }
                                jointEventGroup.Add(jointEventGroupRow);
                            }
                        }
                    }
                }
                List<JointEventClassDb> jointEventClass = new List<JointEventClassDb>(); //學校班級 [待學校班級架構定義完成後再做]
                if (jointExamIdCollectorsSchool.Count > 0)
                {

                }
                
                //整理 jointExamInfo
                if (jointExamInfo.Count > 0)
                {
                    foreach(JointExam jointExam in jointExamInfo)
                    {
                        List<JointEventGroupDb> stuLists = new List<JointEventGroupDb>();
                        List<JointEventClassDb> classes = new List<JointEventClassDb>();
                        if (jointExam.examType.Equals("regular")) //一般競賽
                        {
                            stuLists = jointEventGroup.Where(g => g.jointEventId.Equals(jointExam.jointEventId) && g.jointGroupId.Equals(jointExam.jointGroupId) && (g.type == null || g.type.Equals("regular"))).ToList();
                            classes = jointEventClass.Where(g => g.jointEventId.Equals(jointExam.jointEventId) && g.jointGroupId.Equals(jointExam.jointGroupId) && (g.type == null || g.type.Equals("regular"))).ToList();
                        }
                        else if(jointExam.examType.Equals("custom")) //決賽
                        {
                            stuLists = jointEventGroup.Where(g => g.jointEventId.Equals(jointExam.jointEventId) && g.jointGroupId.Equals(jointExam.jointGroupId) && (g.type.Equals("custom"))).ToList();
                            classes = jointEventClass.Where(g => g.jointEventId.Equals(jointExam.jointEventId) && g.jointGroupId.Equals(jointExam.jointGroupId) && (g.type.Equals("custom"))).ToList();
                        }
                        //private (stuLists)
                        if (stuLists.Count > 0)
                        {
                            foreach (JointEventGroupDb stu in stuLists)
                            {
                                JointEventGroupBase stuListRow = new JointEventGroupBase();
                                stuListRow.scope = "private";
                                stuListRow.type = stu.type;
                                stuListRow.creatorId = stu.creatorId;
                                stuListRow.creatorName = stu.creatorName;
                                stuListRow.creatorEmail = stu.creatorEmail;
                                stuListRow.schoolId = stu.schoolId;
                                stuListRow.schoolName = stu.schoolName;
                                stuListRow.courseLists = Newtonsoft.Json.JsonConvert.DeserializeObject<List<JointEventGroupBase.JointEventGroupCourse>>(Newtonsoft.Json.JsonConvert.SerializeObject(stu.courseLists));
                                Dictionary<string, Dictionary<string, string>> courseToExamIdDic = (!string.IsNullOrWhiteSpace(stu.type) && stu.type.Equals("custom")) ? courseToExamIdCustomDic : courseToExamIdRegularDic;
                                foreach (JointEventGroupBase.JointEventGroupCourse course in stuListRow.courseLists)
                                {
                                    course.examId = (courseToExamIdDic.ContainsKey(jointExam.id) && courseToExamIdDic[jointExam.id].ContainsKey(course.courseId)) ? courseToExamIdDic[jointExam.id][course.courseId] : null;
                                }
                                jointExam.stuLists.Add(stuListRow);
                            }
                        }
                        //school (classes)
                        if (classes.Count > 0)
                        {
                            foreach (JointEventClassDb clas in classes)
                            {
                                JointEventClassBase classRow = new JointEventClassBase();
                                //樣式未定
                                jointExam.classes.Add(classRow);
                            }
                        }
                    }
                }

                return Ok(new { data = jointExamInfo, token = token });
            }
            catch (Exception e)
            {
                //await _dingDing.SendBotMsg($"OS，{_option.Location}，jointevent/jointexam/find()\n{e.Message}\n{e.StackTrace}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }

        }

        /// <summary>
        /// 新建變更統測評量
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [Authorize(Roles = "IES")]
#if !DEBUG
        [AuthToken(Roles = "teacher")]
#endif
        [HttpPost("exam/upsert")]
        public async Task<IActionResult> upsertJointExam(JsonElement request)
        {
            try
            {
                var client = _azureCosmos.GetCosmosClient();
                long now = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
                string jointEventId = (request.TryGetProperty("jointEventId", out JsonElement _jointEventId)) ? _jointEventId.ToString() : string.Empty;
                string jointGroupId = (request.TryGetProperty("jointGroupId", out JsonElement _jointGroupId)) ? _jointGroupId.ToString() : string.Empty;
                string jointScheduleId = (request.TryGetProperty("jointScheduleId", out JsonElement _jointScheduleId)) ? _jointScheduleId.ToString() : string.Empty;
                string jointExamId = (request.TryGetProperty("jointExamId", out JsonElement _jointExamId)) ? _jointExamId.ToString() : string.Empty;
                string creatorId = (request.TryGetProperty("creatorId", out JsonElement _creatorId)) ? _creatorId.ToString() : string.Empty;
                string name = (request.TryGetProperty("name", out JsonElement _name)) ? _name.ToString() : string.Empty;
                string source = (request.TryGetProperty("source", out JsonElement _source)) ? _source.ToString() : string.Empty;
                string scope = (request.TryGetProperty("scope", out JsonElement _scope)) ? _scope.ToString() : "private";
                List<PaperSimple> papers = (request.TryGetProperty("papers", out JsonElement _papers)) ? _papers.ToObject<List<PaperSimple>>() : null;
                List<JointEventGroupBase> stuLists = (request.TryGetProperty("stuLists", out JsonElement _stuLists)) ? _stuLists.ToObject<List<JointEventGroupBase>>() : null;
                List<JointEventClassBase> classes = (request.TryGetProperty("classes", out JsonElement _classes)) ? _classes.ToObject<List<JointEventClassBase>>() : null;

                if (string.IsNullOrWhiteSpace(jointEventId) || string.IsNullOrWhiteSpace(jointGroupId) || string.IsNullOrWhiteSpace(jointScheduleId) || string.IsNullOrWhiteSpace(jointExamId))
                {
                    return BadRequest();
                }
                //取得schedule
                JointEventGroup jointEventGroup = new JointEventGroup();
                JointEventSchedule jointEventSchedule = new JointEventSchedule();
                StringBuilder stringBuilderSchedule = new($"SELECT cg.id as groupId, cg.name as groupName, cg.assistants, cs.id as scheduleId, cs.type as scheduleType, cs.examType, cs.examOverwrite, cs.name as scheduleName, cs.startTime as scheduleStartTime, cs.endTime as scheduleEndTime, cs.progress as scheduleProgress FROM c JOIN cs IN c.schedule JOIN cg IN c.groups WHERE c.id = '{jointEventId}' AND cs.id = '{jointScheduleId}' AND cg.id = '{jointGroupId}' ");
                await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIteratorSql(queryText: stringBuilderSchedule.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("JointEvent") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.Content);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            jointEventGroup.id = obj.GetProperty("groupId").GetString();
                            jointEventGroup.name = obj.GetProperty("groupName").GetString();
                            jointEventGroup.assistants = obj.GetProperty("assistants").ToObject<HashSet<string>>();
                            jointEventSchedule.id = obj.GetProperty("scheduleId").GetString();
                            jointEventSchedule.examType = obj.GetProperty("examType").GetString();
                            jointEventSchedule.examOverwrite = obj.GetProperty("examOverwrite").GetBoolean();
                            jointEventSchedule.type = obj.GetProperty("scheduleType").GetString();
                            jointEventSchedule.name = obj.GetProperty("scheduleName").GetString();
                            jointEventSchedule.startTime = obj.GetProperty("scheduleStartTime").GetInt64();
                            jointEventSchedule.endTime = obj.GetProperty("scheduleEndTime").GetInt64();
                            jointEventSchedule.progress = obj.GetProperty("scheduleProgress").GetString();
                        }
                    }
                }
                if (string.IsNullOrWhiteSpace(jointEventGroup.id) || string.IsNullOrWhiteSpace(jointEventSchedule.id))
                {
                    return Ok(new { errCode = "3", err = "Invalid jointEventId." });
                }
                JointExam jointExam = new JointExam();
                string mode = "upd";
                try
                {
                    jointExam = await client.GetContainer(Constant.TEAMModelOS, "Common").ReadItemAsync<JointExam>(jointExamId, new PartitionKey("JointExam"));
                }
                catch (Exception e)
                {
                    mode = "add";
                }
                if (mode.Equals("upd")) //更新
                {
                    if (jointExam.progress.Equals("going"))
                    {
                        return Ok(new { errCode = "9", err = "JointExam is going, can't be changed." });
                    }
                    if (!string.IsNullOrWhiteSpace(name)) jointExam.name = name;
                    if (!string.IsNullOrWhiteSpace(source)) jointExam.source = source;
                    //if (jointEventSchedule.examType.Equals("custom")) //決賽，要填classes或stuLists擇一必須
                    //{
                    //    if (classes == null || stuLists == null)
                    //    {
                    //        return Ok(new { errCode = "8", err = "Invalid classes or stuLists." });
                    //    }
                    //    if (classes != null) jointExam.classes = classes;
                    //    if (stuLists != null) jointExam.stuLists = stuLists;
                    //}
                    if (papers != null) jointExam.papers = papers;
                }
                else //新建
                {
                    if (papers == null) return Ok(new { errCode = "7", err = "Invalid paper." });
                    if (string.IsNullOrWhiteSpace(creatorId)) return Ok(new { errCode = "8", err = "Invalid creatorId." });
                    if (string.IsNullOrWhiteSpace(name)) return Ok(new { errCode = "9", err = "Invalid name." });
                    if (string.IsNullOrWhiteSpace(source)) return Ok(new { errCode = "10", err = "Invalid source." });
                    //if (jointEventSchedule.examType.Equals("custom")) //決賽，要填classes或stuLists擇一必須
                    //{
                    //    if (classes == null || stuLists == null)
                    //    {
                    //        return Ok(new { errCode = "8", err = "Invalid classes or stuLists." });
                    //    }
                    //    if (classes != null) jointExam.classes = classes;
                    //    if (stuLists != null) jointExam.stuLists = stuLists;
                    //}
                    jointExam.pk = "JointExam";
                    jointExam.code = "JointExam";
                    jointExam.id = jointExamId;
                    jointExam.jointEventId = jointEventId;
                    jointExam.jointScheduleId = jointScheduleId;
                    jointExam.jointGroupId = jointGroupId;
                    jointExam.creatorId = creatorId;
                    jointExam.name = name;
                    jointExam.source = source;
                    jointExam.papers = papers;
                    jointExam.createTime = now;
                    jointExam.scope = scope;
                }
                //共通
                jointExam.examType = jointEventSchedule.examType;
                jointExam.examOverwrite = jointEventSchedule.examOverwrite;
                jointExam.startTime = jointEventSchedule.startTime;
                jointExam.endTime = jointEventSchedule.endTime;
                jointExam.progress = (jointExam.startTime > now) ? "pending" : (now > jointExam.endTime) ? "finish" : "going";
                jointExam.updateTime = now;

                await client.GetContainer(Constant.TEAMModelOS, "Common").UpsertItemAsync<JointExam>(jointExam, new PartitionKey("JointExam"));
                return Ok(new { errCode = "", err = "", jointExam });
            }
            catch (Exception e)
            {
                //await _dingDing.SendBotMsg($"OS，{_option.Location}，jointevent/jointexam/find()\n{e.Message}\n{e.StackTrace}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }
        }

        //判斷某groupId在某JointSchedule是否完成
        private async Task<string> GetGroupFinishJointSchedule(string jointEventId, string jointGroupId, string groupId, JointEventSchedule schedule)
        {
            string result = "undo";
            var client = _azureCosmos.GetCosmosClient();
            string scope = "private"; //先只指定個人
            string container = (scope.Equals("school")) ? Constant.School : Constant.Teacher;
            switch (schedule.type)
            {
                //報名
                case "join":
                    StringBuilder stringBuilder = new($"SELECT DISTINCT ccg.id FROM c JOIN cc IN c.courseLists JOIN ccg IN cc.groupLists WHERE c.jointEventId = '{jointEventId}' AND c.jointGroupId = '{jointGroupId}' AND (c.type = 'regular' OR NOT IS_DEFINED(c.type) OR IS_NULL(c.type)) AND ccg.id = '{groupId}' ");
                    await foreach (var item in client.GetContainer(Constant.TEAMModelOS, container).GetItemQueryStreamIteratorSql(queryText: stringBuilder.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("JointCourse") }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.Content);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        {
                            foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                if (obj.GetProperty("id").GetString().Equals(groupId))
                                {
                                    result = "complete";
                                }
                            }
                        }
                    }
                    break;
                //競賽
                case "exam":
                    //取得老師報名課程或決賽老師課程
                    List<JointEventGroupDb> jointEventGroup = new List<JointEventGroupDb>(); //個人課程
                    StringBuilder stringBuilderEventGroup = new($"SELECT * FROM c WHERE c.jointEventId = '{jointEventId}' AND c.jointGroupId = '{jointGroupId}' ");
                    if (schedule.examType.Equals("regular")) //熱身賽
                    {
                        stringBuilderEventGroup.Append($" AND (c.type = 'regular' OR NOT IS_DEFINED(c.type) OR IS_NULL(c.type)) ");
                    }
                    else if(schedule.examType.Equals("custom")) //決賽
                    {
                        stringBuilderEventGroup.Append($" AND c.type = 'custom' AND c.jointScheduleId = '{schedule.id}' ");
                    }
                    await foreach (var item in client.GetContainer(Constant.TEAMModelOS, Constant.Teacher).GetItemQueryStreamIteratorSql(queryText: stringBuilderEventGroup.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("JointCourse") }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.Content);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        {
                            foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                jointEventGroup.Add(obj.ToObject<JointEventGroupDb>());
                            }
                        }
                    }
                    //取得本Schedule的所有JointExam
                    List<string> jointExamIdList = new List<string>();
                    StringBuilder stringBuilderJointExam = new($"SELECT DISTINCT VALUE c.id FROM c WHERE c.jointEventId = '{jointEventId}' AND c.jointGroupId = '{jointGroupId}' AND c.jointScheduleId = '{schedule.id}' ");
                    var resultJointExam = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, Constant.Common).GetList<string>(stringBuilderJointExam.ToString(), $"JointExam");
                    if (resultJointExam.list.IsNotEmpty())
                    {
                        jointExamIdList = new List<string>(resultJointExam.list);
                    }
                    //取得所有個人評量
                    List<string> examIdList = new List<string>();
                    string sqlExam = $"SELECT DISTINCT VALUE c.id FROM c WHERE ARRAY_CONTAINS({JsonSerializer.Serialize(jointExamIdList)}, c.jointExamId) AND ARRAY_CONTAINS(c.stuLists, '{groupId}') AND CONTAINS(c.code, 'Exam-')";
                    await foreach (var item in client.GetContainer(Constant.TEAMModelOS, Constant.Common).GetItemQueryIteratorSql<string>(queryText: sqlExam, requestOptions: new QueryRequestOptions { }))
                    {
                        examIdList.Add(item);
                    }
                    //取得所有考試的作答結果
                    List<string> finishExamIdList = new List<string>();
                    string sqlExamClassResult = $"SELECT c.examId, c.info.id as classId, c.studentAnswers FROM c WHERE ARRAY_CONTAINS({JsonSerializer.Serialize(examIdList)}, c.examId) AND c.info.id = '{groupId}' AND c.progress=true AND CONTAINS(c.code, 'ExamClassResult')";
                    await foreach (var item in client.GetContainer(Constant.TEAMModelOS, Constant.Common).GetItemQueryStreamIteratorSql(queryText: sqlExamClassResult, requestOptions: new QueryRequestOptions() { }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.Content);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        {
                            foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                string examId = obj.GetProperty("examId").ToString();
                                string classId = obj.GetProperty("classId").ToString();
                                List<List<string>> studentAnswers = obj.GetProperty("studentAnswers").ToObject<List<List<string>>>();
                                bool isFinish = false; //評量是否已完成 ※有任一學生有作答則視為已完成
                                foreach (List<string> studentAnswer in studentAnswers)
                                {
                                    if (studentAnswer.Count > 0) { isFinish = true; break; }
                                }
                                if (isFinish)
                                {
                                    finishExamIdList.Add(examId);
                                }
                            }
                        }
                    }
                    //結果判斷
                    if(jointEventGroup.Count.Equals(0)) //資格不符
                    {
                        result = "disqualify";
                    }
                    else if (jointExamIdList.Count > 0 && jointExamIdList.Count.Equals(finishExamIdList.Count))
                    {
                        result = "complete";
                    }
                    else if (jointExamIdList.Count > 0 && finishExamIdList.Count > 0 && jointExamIdList.Count > finishExamIdList.Count)
                    {
                        result = "doing";
                    }
                    break;
            }
            return result;
        }


        [ProducesDefaultResponseType]
#if !DEBUG
        [Authorize(Roles = "IES")]
        [AuthToken(Roles = "teacher")]
#endif
        [HttpPost("exam/create-final-teachercourse")]
        public async Task<IActionResult> createFinalJointTeacherCourse(JsonElement request)
        {
            List<JointEventGroupDb> result = new List<JointEventGroupDb>();
            var client = _azureCosmos.GetCosmosClient();
            string jointEventId = (request.TryGetProperty("jointEventId", out JsonElement _jointEventId)) ? _jointEventId.ToString() : string.Empty;
            string jointScheduleId = (request.TryGetProperty("jointScheduleId", out JsonElement _jointScheduleId)) ? _jointScheduleId.ToString() : string.Empty; //要計算的行程
            string scope = "private";
            JointEvent jointEvent = await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReadItemAsync<JointEvent>(jointEventId, new PartitionKey("JointEvent"));
            if (jointEvent == null)
            {
                return BadRequest();
            }
            List<string> jointGroupIds = jointEvent.groups.Select(g => g.id).ToList();
            if(jointGroupIds.Count > 0)
            {
                foreach(string jointGroupId in jointGroupIds)
                {
                    List<JointEventGroupDb> addResult = await JointService.CreatePassJointCourseBySchedule(client, jointEventId, jointGroupId, jointScheduleId, scope);
                    result = result.Union(addResult).ToList();
                }
            }

            return Ok(result);
        }

        public class JointExamIdCollector
        {
            public string jointEventId { get; set; }
            public string jointGroupId { get; set; }
        }
        /// <summary>
        /// 統測活動 API輸出用 
        /// </summary>
        public class JointEventDto : JointEvent
        {
            public string blobsas { get; set; }
        }

        /// <summary>
        /// 統測活動老師報名課程 API輸出用 
        /// </summary>
        public class JointEventGroupDto
        {
            public string id { get; set; }
            public string code { get; set; }
            public string pk { get; set; }
            public string jointEventId { get; set; }
            public string jointGroupId { get; set; }
            public string scope { get; set; } //school:學校(選課班) private:個人 
            public string creatorId { get; set; }
            public string creatorName { get; set; }
            public string schoolId { get; set; } //老師教育雲綁定的學校ID
            public string schoolName { get; set; } //老師教育雲綁定的學校名稱
            public string periodId { get; set; } //老師個人課程時為null
            public List<JointEventGroupCourseDto> courseLists { get; set; } = new();
            public class JointEventGroupCourseDto
            {
                public string subjectId { get; set; } //老師個人課程時為null
                public string courseId { get; set; }
                public string courseName { get; set; }
                public List<JointEventGroupCourseGroupDto> groupLists { get; set; } = new();
            }
            public class JointEventGroupCourseGroupDto
            {
                public string id { get; set; }
                public string name { get; set; }
                public List<object> schedule { get; set; } = new List<object>(); //已完成的行程
            }
        }
    }
}