using Amazon.S3.Model;
using BMA.EHR.Extensions;
using BMA.EHR.Recurit.Exam.Service.Controllers;
using BMA.EHR.Recurit.Exam.Service.Core;
using BMA.EHR.Recurit.Exam.Service.Data;
using BMA.EHR.Recurit.Exam.Service.Extensions;
using BMA.EHR.Recurit.Exam.Service.Models;
using BMA.EHR.Recurit.Exam.Service.Models.Disables;
using BMA.EHR.Recurit.Exam.Service.Request.Disables;
using BMA.EHR.Recurit.Exam.Service.Response;
using BMA.EHR.Recurit.Exam.Service.Responses;
using BMA.EHR.Recurit.Exam.Service.Services;
using BMA.EHR.Recurit.Exam.Service.Responses.Document;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using MySqlConnector;
using OfficeOpenXml;
using Org.BouncyCastle.Ocsp;
using Sentry;
using Swashbuckle.AspNetCore.Annotations;
using System.Data;
using System.Net;
using System.Net.WebSockets;
using System.Security.Claims;
using System.Text;

namespace BMA.EHR.Recurit.Exam.Service.Controllers
{
    [Route("api/v{version:apiVersion}/candidate/disable-exam")]
    [ApiVersion("1.0")]
    [ApiController]
    [Produces("application/json")]
    [Authorize]
    [SwaggerTag("จัดการข้อมูลการสอบคัดเลือกคนพิการ")]
    public class DisableController : BaseController
    {
        #region " Fields "

        private readonly ApplicationDbContext _context;
        private readonly MetadataDbContext _contextMetadata;
        private readonly MinIOService _minioService;
        private readonly IWebHostEnvironment _webHostEnvironment;
        private readonly DisableService _disableService;
        private readonly PeriodExamService _periodExamService;

        private readonly IHttpContextAccessor _httpContextAccessor;
        private readonly ILogger<DisableController> _logger;

        #endregion

        #region " Constructor and Destructor "

        public DisableController(ApplicationDbContext context,
                                     MetadataDbContext contextMetadata,
                                 MinIOService minioService,
                                 IWebHostEnvironment webHostEnvironment,
                                 DisableService disableService,
                                 PeriodExamService periodExamService,
                                 IHttpContextAccessor httpContextAccessor,
                                 ILogger<DisableController> logger)
        {
            _context = context;
            _contextMetadata = contextMetadata;
            _minioService = minioService;
            _webHostEnvironment = webHostEnvironment;
            _disableService = disableService;
            _periodExamService = periodExamService;
            _httpContextAccessor = httpContextAccessor;
            _logger = logger;
        }

        #endregion

        #region " Properties "

        private string? UserId => _httpContextAccessor?.HttpContext?.User?.FindFirst(ClaimTypes.NameIdentifier)?.Value;

        private string? FullName => _httpContextAccessor?.HttpContext?.User?.FindFirst("name")?.Value;

        #endregion

        #region " Methods "

        #region " Private "

        private int GetColumnIndex(string[] columns, string name, bool partial = false)
        {
            try
            {
                if (partial)
                    return Array.FindIndex(columns, x => x.Contains(name)) + 1;
                else
                    return Array.FindIndex(columns, x => x == name) + 1;
            }
            catch
            {
                return 0;
            }

        }

        private string CalculateDiff(DateTime d1, DateTime d2)
        {
            if (d1 > d2) return "ข้อมูลไม่ถูกต้อง";

            TimeSpan sp = d2.Subtract(d1);
            int yy = sp.Days / 365;
            int mm = (sp.Days - (yy * 365)) / 30;
            int dd = (sp.Days - (yy * 365) - (mm * 30));

            var sb = new StringBuilder();
            sb.Clear();
            sb.Append(yy == 0 ? "" : $"{yy} ปี ");
            sb.Append(mm == 0 ? "" : $"{mm} เดือน ");
            //sb.Append(dd == 0 ? "" : $"{dd} วัน ");

            return sb.ToString();
        }

        private int GetExamCountTe(string citizenId)
        {
            try
            {
                var count = _context.Candidates.AsQueryable()
                                    .Where(x => x.CitizenId == citizenId)
                                    .Count();

                return count;
            }
            catch
            {
                throw;
            }
        }

        private async Task<int> GetExamCount(Guid exam)
        {
            try
            {
                return await _context.DisablePayments.AsQueryable()
                                .Include(x => x.Disable)
                                .ThenInclude(x => x.PeriodExam)
                                .Where(x => x.Disable.PeriodExam.Id == exam)
                                .Where(x => x.Disable.PeriodExam.CheckDisability == true)
                                .CountAsync();
            }
            catch
            {
                throw;
            }
        }

        private async Task<int> GetPassExamCount(Guid exam)
        {
            try
            {
                return await _context.DisableScores.AsQueryable()
                                .Include(x => x.ScoreImport)
                                .Where(x => x.ScoreImport.Id == exam)
                                .Where(x => x.ExamStatus == "ผ่าน")
                                .CountAsync();
            }
            catch
            {
                throw;
            }
        }

        private async Task<int> GetScoreCount(Guid exam)
        {
            try
            {
                return await _context.DisableScores.AsQueryable()
                                .Include(x => x.ScoreImport)
                                .Where(x => x.ScoreImport.Id == exam)
                                .CountAsync();
            }
            catch
            {
                throw;
            }
        }
        private List<Guid?> GetAllIdByRoot(Guid id)
        {
            try
            {
                var ret = new List<Guid?>();

                var oc = _contextMetadata.Organizations.FirstOrDefault(x => x.Id == id && x.IsActive);
                if (oc != null)
                    ret.Add(oc.Id);

                var child = _contextMetadata.Organizations.AsQueryable().Where(x => x.ParentId == id && x.IsActive).ToList();
                if (child.Any())
                {
                    foreach (var item in child)
                    {
                        ret.AddRange(GetAllIdByRoot(item.Id));
                    }
                }

                return ret;
            }
            catch
            {
                throw;
            }
        }

        #endregion

        #region " Ex. Upload, Download and Delete file "

        /// <summary>
        /// ตัวอย่างในการเขียน api เพื่อทำการ upload file
        /// </summary>
        /// <returns></returns>
        /// <response code="200">เมื่อทำการ upload สำเร็จ</response>
        /// <response code="400">ค่าตัวแปรที่ส่งมาไม่ถูกต้อง</response>
        /// <response code="401">ไม่ได้ Login เข้าระบบ</response>
        /// <response code="500">เมื่อเกิดข้อผิดพลาดในการทำงาน</response>
        [HttpPost("upload"), DisableRequestSizeLimit]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        [ProducesResponseType(StatusCodes.Status401Unauthorized)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        [AllowAnonymous]
        public async Task<ActionResult<ResponseObject>> UploadFile()
        {
            try
            {
                if (Request.Form.Files == null || Request.Form.Files.Count == 0)
                {
                    return Error(GlobalMessages.NoFileToUpload);
                }

                var file = Request.Form.Files[0];
                var doc = await _minioService.UploadFileAsync(file);

                return Success(doc);
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        /// <summary>
        /// ตัวอย่างในการเขียน api เพื่อทำการ delete file
        /// </summary>
        /// <param name="id">รหัสไฟล์ในฐานข้อมูล</param>
        /// <returns></returns>
        /// <response code="200">เมื่อทำการ delete file สำเร็จ</response>
        /// <response code="400">ค่าตัวแปรที่ส่งมาไม่ถูกต้อง</response>
        /// <response code="401">ไม่ได้ Login เข้าระบบ</response>
        /// <response code="500">เมื่อเกิดข้อผิดพลาดในการทำงาน</response>
        [HttpGet("delete/{id:length(36)}")]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        [ProducesResponseType(StatusCodes.Status401Unauthorized)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        [AllowAnonymous]
        public async Task<ActionResult<ResponseObject>> DeleteFile(Guid id)
        {
            try
            {
                await _minioService.DeleteFileAsync(id);

                return Success();
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        /// <summary>
        /// ตัวอย่างในการเขียน api เพื่อทำการ download file
        /// </summary>
        /// <param name="id">รหัสไฟล์ในฐานข้อมูล</param>
        /// <returns></returns>
        /// <response code="200">เมื่อทำการ download file สำเร็จ</response>
        /// <response code="400">ค่าตัวแปรที่ส่งมาไม่ถูกต้อง</response>
        /// <response code="401">ไม่ได้ Login เข้าระบบ</response>
        /// <response code="500">เมื่อเกิดข้อผิดพลาดในการทำงาน</response>
        [HttpGet("download/{id:length(36)}")]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        [ProducesResponseType(StatusCodes.Status401Unauthorized)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        [AllowAnonymous]
        public async Task<ActionResult<ResponseObject>> DownloadFile(Guid id)
        {
            try
            {
                var file_data = await _minioService.DownloadFileAsync(id);

                Response.Headers["Content-Disposition"] = $"inline; filename={file_data.FileName}";

                var ret = new FileContentResult(file_data.FileContent, file_data.FileType)
                {
                    FileDownloadName = file_data.FileName
                };

                return ret;
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }


        #endregion

        #region " จัดการรอบการสมัครสอบคัดเลือกผู้พิการ "

        /// <summary>
        /// แสดงข้อมูลรอบการสอบคัดเลือกผู้พิการ
        /// </summary>
        /// <returns></returns>
        /// <response code="200">เมื่อทำการอ่านข้อมูลจาก Relational Database สำเร็จ</response>
        /// <response code="400">ค่าตัวแปรที่ส่งมาไม่ถูกต้อง</response>
        /// <response code="401">ไม่ได้ Login เข้าระบบ</response>
        /// <response code="500">เมื่อเกิดข้อผิดพลาดในการทำงาน</response>
        [HttpGet("period")]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        [ProducesResponseType(StatusCodes.Status401Unauthorized)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        public async Task<ActionResult<ResponseObject>> GetPeriodsAsync()
        {
            try
            {
                var data = await _context.PeriodExams.AsQueryable()
                                .Where(x => x.CheckDisability == true)
                                .Include(x => x.ImportFile)
                                .Include(x => x.Disables)
                                .Include(x => x.ScoreImport)
                                .ThenInclude(x => x.ImportFile)
                                .Include(x => x.ScoreImport)
                                .ThenInclude(x => x.Scores)
                                .OrderByDescending(x => x.Year)
                                .ThenByDescending(x => x.Round)
                                .Select(x => new
                                {
                                    x.Id,
                                    x.Year,
                                    x.Name,
                                    x.Round,
                                    ImportDate = x.CreatedAt.Date.ToThaiShortDate(),
                                    ExamCount = x.Disables.Count(),
                                    Score = x.ScoreImport == null ? null :
                                            new
                                            {
                                                ID = x.ScoreImport.Id,
                                                ImportYear = x.ScoreImport.Year,
                                                ImportDate = x.CreatedAt.Date.ToThaiShortDate(),
                                                ScoreCount = x.ScoreImport.Scores.Count(),

                                            }
                                })
                                .ToListAsync();

                return Success(data);
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        /// <summary>
        /// แสดงข้อมูลรอบการสอบคัดเลือกผู้พิการเป็นรายการ
        /// </summary>
        /// <param name="id">รหัสรอบการสอบคัดเลือกผู้พิการ</param>
        /// <returns></returns>
        /// <response code="200">เมื่อทำการอ่านข้อมูลจาก Relational Database สำเร็จ</response>
        /// <response code="400">ค่าตัวแปรที่ส่งมาไม่ถูกต้อง</response>
        /// <response code="401">ไม่ได้ Login เข้าระบบ</response>
        /// <response code="500">เมื่อเกิดข้อผิดพลาดในการทำงาน</response>
        [HttpGet("period/{id:length(36)}")]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        [ProducesResponseType(StatusCodes.Status401Unauthorized)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        public async Task<ActionResult<ResponseObject>> GetPeriodByIdAsync(Guid id)
        {
            try
            {
                // var data = await _context.PeriodExams.AsQueryable()
                //                 .Where(x => x.CheckDisability == true)
                //                 .Include(x => x.ImportFile)
                //                 .Include(x => x.Disables)
                //                 .ThenInclude(x => x.Addresses)
                //                 .Include(x => x.Disables)
                //                 .ThenInclude(x => x.Occupations)
                //                 .Include(x => x.Disables)
                //                 .ThenInclude(x => x.Certificates)
                //                 .Include(x => x.Disables)
                //                 .ThenInclude(x => x.Educations)
                //                 .Include(x => x.Disables)
                //                 .ThenInclude(x => x.Payments)
                //                 .Include(x => x.Disables)
                //                 .ThenInclude(x => x.Documents)
                //                 .ThenInclude(x => x.DocumentFile)
                //                 .FirstOrDefaultAsync(x => x.Id == id);
                var periodExam = await _context.PeriodExams.AsQueryable()
                                .Select(x => new PeriodExamCandidateResponseItem
                                {
                                    ExamDate = x.ExamDate,
                                    AnnouncementEndDate = x.AnnouncementEndDate,
                                    AnnouncementStartDate = x.AnnouncementStartDate,
                                    AnnouncementDate = x.AnnouncementDate,
                                    CheckDisability = x.CheckDisability,
                                    CheckDocument = x.CheckDocument,
                                    Detail = x.Detail,
                                    Fee = x.Fee,
                                    Id = x.Id,
                                    IsActive = x.IsActive,
                                    Name = x.Name,
                                    Note = x.Note,
                                    // OrganizationCodeId = x.OrganizationCodeId,
                                    // OrganizationCodeName = x.OrganizationCodeName,
                                    // OrganizationId = x.OrganizationId,
                                    // OrganizationName = x.OrganizationName,
                                    PaymentEndDate = x.PaymentEndDate,
                                    // PaymentKrungThai = x.PaymentKrungThai,
                                    AnnouncementExam = x.AnnouncementExam,
                                    Category = x.Category,
                                    PaymentStartDate = x.PaymentStartDate,
                                    RegisterEndDate = x.RegisterEndDate,
                                    RegisterStartDate = x.RegisterStartDate,
                                    Round = x.Round,
                                    // SetSeat = x.SetSeat,
                                    Year = x.Year,
                                    // BankExam = x.BankExam.OrderBy(o => o.CreatedAt).Select(b => new BankExam
                                    // {
                                    //     Id = b.Id,
                                    //     AccountName = b.AccountName,
                                    //     AccountNumber = b.AccountNumber,
                                    //     BankName = b.BankName,
                                    // }).ToList(),
                                    // PositionExam = x.PositionExam.OrderBy(o => o.CreatedAt).Select(b => new PositionExam
                                    // {
                                    //     Id = b.Id,
                                    //     TypeId = b.TypeId,
                                    //     TypeName = b.TypeName,
                                    //     PositionId = b.PositionId,
                                    //     PositionName = b.PositionName,
                                    // }).ToList(),
                                    Documents = x.PeriodExamDocuments.OrderBy(o => o.CreatedAt).Select(b => new FileListResponse
                                    {
                                        Id = b.Document == null ? "" : b.Document.Id.ToString(),
                                        FileName = b.Document == null ? "" : b.Document.FileName,
                                        FileSize = b.Document == null ? 0 : b.Document.FileSize,
                                        FileType = b.Document == null ? "" : b.Document.FileType,
                                        Detail = b.Document == null ? "" : b.Document.Id.ToString(),
                                    }).ToList(),
                                    Images = x.PeriodExamImages.OrderBy(o => o.CreatedAt).Select(b => new FileListResponse
                                    {
                                        Id = b.Document == null ? "" : b.Document.Id.ToString(),
                                        FileName = b.Document == null ? "" : b.Document.FileName,
                                        FileSize = b.Document == null ? 0 : b.Document.FileSize,
                                        FileType = b.Document == null ? "" : b.Document.FileType,
                                        Detail = b.Document == null ? "" : b.Document.Id.ToString(),
                                    }).ToList(),
                                })
                                .FirstOrDefaultAsync(x => x.Id == id);

                if (periodExam == null)
                    throw new Exception(GlobalMessages.ExamNotFound);

                return Success(periodExam);
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }


        /// <summary>
        /// เพิ่มข้อมูลรอบการจัดสอบคัดเลือกผู้พิการ
        /// </summary>
        /// <param name="req">Request parameters</param>
        /// <returns></returns>
        /// <response code="200">เมื่อทำการเพิ่มข้อมูลสำเร็จ</response>
        /// <response code="400">ค่าตัวแปรที่ส่งมาไม่ถูกต้อง</response>
        /// <response code="401">ไม่ได้ Login เข้าระบบ</response>
        /// <response code="500">เมื่อเกิดข้อผิดพลาดในการทำงาน</response>
        [HttpPost("period")]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        [ProducesResponseType(StatusCodes.Status401Unauthorized)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        public async Task<ActionResult<ResponseObject>> PostPeriodAsync([FromBody] PostDisableImportRequest req)
        {
            try
            {
                if (req == null)
                    return Error(GlobalMessages.InvalidRequestParam, (int)HttpStatusCode.BadRequest);

                await _context.PeriodExams.AddAsync(new PeriodExam
                {
                    Year = req.Year,
                    Name = req.Name,
                    Round = req.Round,
                    Detail = req.Detail,
                    Fee = req.Fee,
                    AnnouncementStartDate = req.AnnouncementStartDate,
                    AnnouncementEndDate = req.AnnouncementEndDate,
                    RegisterStartDate = req.RegisterStartDate,
                    RegisterEndDate = req.RegisterEndDate,
                    ExamDate = req.ExamDate,
                    PaymentStartDate = req.PaymentStartDate,
                    PaymentEndDate = req.PaymentEndDate,
                    Note = req.Note,
                    AnnouncementDate = req.AnnouncementDate,
                    CheckDisability = true,
                    CreatedAt = DateTime.Now,
                    CreatedUserId = UserId ?? "",
                    CreatedFullName = FullName ?? "System Administrator",
                    LastUpdatedAt = DateTime.Now,
                    LastUpdateUserId = UserId ?? "",
                    LastUpdateFullName = FullName ?? "System Administrator",
                });

                await _context.SaveChangesAsync();

                return Success();
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        /// <summary>
        /// แก้ไขข้อมูลรอบการจัดสอบคัดเลือกผู้พิการ
        /// </summary>
        /// <param name="id">รหัสรอบการสอบคัดเลือกผู้พิการ</param>
        /// <param name="req">Request parameters</param>
        /// <returns></returns>
        /// <response code="200">เมื่อทำการเพิ่มข้อมูลสำเร็จ</response>
        /// <response code="400">ค่าตัวแปรที่ส่งมาไม่ถูกต้อง</response>
        /// <response code="401">ไม่ได้ Login เข้าระบบ</response>
        /// <response code="500">เมื่อเกิดข้อผิดพลาดในการทำงาน</response>
        [HttpPut("period/{id:length(36)}")]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        [ProducesResponseType(StatusCodes.Status401Unauthorized)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        public async Task<ActionResult<ResponseObject>> PutPeriodAsync(Guid id, [FromBody] PostDisableImportRequest req)
        {
            try
            {
                var data = await _context.PeriodExams.AsQueryable()
                                    .Where(x => x.CheckDisability == true)
                                    .FirstOrDefaultAsync(x => x.Id == id);

                if (data == null)
                    return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound);

                data.Name = req.Name;
                data.Year = req.Year;
                data.Round = req.Round;
                data.Detail = req.Detail;
                data.Fee = req.Fee;
                data.AnnouncementEndDate = req.AnnouncementEndDate;
                data.AnnouncementStartDate = req.AnnouncementStartDate;
                data.RegisterStartDate = req.RegisterStartDate;
                data.RegisterEndDate = req.RegisterEndDate;
                data.PaymentEndDate = req.PaymentEndDate;
                data.PaymentStartDate = req.PaymentStartDate;
                data.ExamDate = req.ExamDate;
                data.Note = req.Note;
                data.AnnouncementDate = req.AnnouncementDate;
                data.LastUpdatedAt = DateTime.Now;
                data.LastUpdateUserId = UserId ?? "";
                data.LastUpdateFullName = FullName ?? "System Administrator";

                await _context.SaveChangesAsync();

                return Success();

            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }


        /// <summary>
        /// ลบข้อมูลรอบการจัดสอบคัดเลือกผู้พิการ
        /// </summary>
        /// <param name="id">รหัสรอบการสอบคัดเลือกผู้พิการ</param>
        /// <returns></returns>
        /// <response code="200">เมื่อทำการลบข้อมูลสำเร็จ</response>
        /// <response code="400">ค่าตัวแปรที่ส่งมาไม่ถูกต้อง</response>
        /// <response code="401">ไม่ได้ Login เข้าระบบ</response>
        /// <response code="500">เมื่อเกิดข้อผิดพลาดในการทำงาน</response>
        [HttpDelete("period/{id:length(36)}")]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        [ProducesResponseType(StatusCodes.Status401Unauthorized)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        public async Task<ActionResult<ResponseObject>> DeletePeriodAsync(Guid id)
        {
            try
            {
                var data = await _context.PeriodExams.AsQueryable()
                                .Where(x => x.CheckDisability == true)
                                .Include(x => x.ImportFile)
                                .Include(x => x.Disables)
                                .ThenInclude(x => x.Addresses)
                                .Include(x => x.Disables)
                                .ThenInclude(x => x.Occupations)
                                .Include(x => x.Disables)
                                .ThenInclude(x => x.Certificates)
                                .Include(x => x.Disables)
                                .ThenInclude(x => x.Educations)
                                .Include(x => x.Disables)
                                .ThenInclude(x => x.Payments)
                                .Include(x => x.Disables)
                                .ThenInclude(x => x.Documents)
                                .ThenInclude(x => x.DocumentFile)
                                .Include(x => x.ScoreImport)
                                .ThenInclude(x => x.ImportFile)
                                .Include(x => x.ScoreImport)
                                .ThenInclude(x => x.Scores)
                                .FirstOrDefaultAsync(x => x.Id == id);

                _context.PeriodExams.Remove(data);
                await _context.SaveChangesAsync();

                return Success();
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        #endregion

        #region " Candidate Files "

        /// <summary>
        /// แสดงข้อมูลสำหรับหน้าจอ รายการนำเข้าข้อมูลผู้สมัครสอบคัดเลือกผู้พิการ
        /// </summary>
        /// <returns></returns>
        /// <response code="200">เมื่อแสดงรายการข้อมูลสำเร็จ</response>
        /// <response code="400">ค่าตัวแปรที่ส่งมาไม่ถูกต้อง</response>
        /// <response code="401">ไม่ได้ Login เข้าระบบ</response>
        /// <response code="500">เมื่อเกิดข้อผิดพลาดในการทำงาน</response>
        [HttpGet("candidate")]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        [ProducesResponseType(StatusCodes.Status401Unauthorized)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        public async Task<ActionResult<ResponseObject>> GetCandidateFilesAsync()
        {
            try
            {
                var data = await _context.PeriodExams.AsQueryable()
                                .Where(x => x.CheckDisability == true)
                                .Include(x => x.ImportFile)
                                .Include(x => x.Disables)
                                .Include(x => x.ScoreImport)
                                .ThenInclude(x => x.ImportFile)
                                .Include(x => x.ScoreImport)
                                .ThenInclude(x => x.Scores)
                                .OrderByDescending(x => x.Year)
                                .ThenByDescending(x => x.Round)
                                .Select(x => new
                                {
                                    x.Id,
                                    x.Year,
                                    x.Name,
                                    x.Round,
                                    ImportDate = x.CreatedAt.Date.ToThaiShortDate(),
                                    ExamCount = x.Disables.Count(),
                                    Score = x.ScoreImport == null ? null :
                                            new
                                            {
                                                ID = x.ScoreImport.Id,
                                                ImportYear = x.ScoreImport.Year,
                                                ImportDate = x.CreatedAt.Date.ToThaiShortDate(),
                                                ScoreCount = x.ScoreImport.Scores.Count(),

                                            },
                                    x.CreatedUserId,
                                })
                                .ToListAsync();
                var profileOrganizations = await _contextMetadata.ProfileOrganizations.AsQueryable()
                                                    .ToListAsync();
                var _periodExams = (from x in data
                                    join po in profileOrganizations on Guid.Parse(x.CreatedUserId) equals po?.UserId into poGroup
                                    from po in poGroup.DefaultIfEmpty()
                                    select new
                                    {
                                        x.Id,
                                        x.Year,
                                        x.Name,
                                        x.Round,
                                        x.ImportDate,
                                        x.ExamCount,
                                        x.Score,
                                        x.CreatedUserId,
                                        OcId = po == null ? null : po.OrganizationId,
                                    }).AsQueryable()
                                    .ToList();

                var roles = _httpContextAccessor?.HttpContext?.User?.FindAll(ClaimTypes.Role)?.Select(c => c.Value).ToList();
                if (!roles.Contains("head"))
                {
                    var criteria = new List<Guid?>();
                    var profileOrganization = await _contextMetadata.ProfileOrganizations.AsQueryable()
                        .FirstOrDefaultAsync(x => x.UserId == Guid.Parse(UserId));

                    if (profileOrganization == null)
                        return Success(new List<dynamic>());

                    var ocId = _contextMetadata.Organizations.AsQueryable()
                               .FirstOrDefault(x => x.Id == profileOrganization.OrganizationId);
                    if (ocId == null)
                        return Success(new List<dynamic>());
                    criteria = GetAllIdByRoot(ocId.Id);
                    if (criteria.Any())
                        _periodExams = _periodExams.Where(x => x.CreatedUserId == UserId || criteria.Contains(x.OcId == null ? Guid.Parse("00000000-0000-0000-0000-000000000000") : x.OcId)).ToList();
                }

                return Success(_periodExams);
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }


        /// <summary>
        /// นำเข้ารายชื่อผู้สมัครสอบคัดเลือกผู้พิการ
        /// </summary>
        /// <returns></returns>
        /// <response code="200">เมื่อทำนำเข้าข้อมูลสำเร็จ</response>
        /// <response code="400">ค่าตัวแปรที่ส่งมาไม่ถูกต้อง</response>
        /// <response code="401">ไม่ได้ Login เข้าระบบ</response>
        /// <response code="500">เมื่อเกิดข้อผิดพลาดในการทำงาน</response>
        [HttpPost("candidate"), DisableRequestSizeLimit]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        [ProducesResponseType(StatusCodes.Status401Unauthorized)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        public async Task<ActionResult<ResponseObject>> ImportCandidateFileAsync([FromForm] PostDisableImportRequest req)
        {
            var tmpDir = Path.Combine(_webHostEnvironment.ContentRootPath, "tmp");
            if (!Directory.Exists(tmpDir))
                Directory.CreateDirectory(tmpDir);

            var importFile = Path.Combine(tmpDir, $"c_{DateTime.Now.ToString("ddMMyyyyHHmmss")}.xlsx");
            var import_doc_id = "";

            try
            {
                if (Request.Form.Files == null || Request.Form.Files.Count == 0)
                {
                    return Error(GlobalMessages.NoFileToUpload);
                }

                var file = Request.Form.Files[0];
                var doc = await _minioService.UploadFileAsync(file);
                import_doc_id = doc.Id.ToString("D");

                var fileContent = (await _minioService.DownloadFileAsync(doc.Id)).FileContent;

                // สร้างรอบการบรรจุ โดยเอาเข้ามูลมาใส่จาก Request
                var imported = new PeriodExam
                {
                    Year = req.Year,
                    Round = req.Round,
                    Name = req.Name,
                    ImportFile = doc,
                    CheckDisability = true,
                    CreatedAt = DateTime.Now,
                    CreatedUserId = UserId ?? "",
                    CreatedFullName = FullName ?? "System Administrator",
                    LastUpdatedAt = DateTime.Now,
                    LastUpdateUserId = UserId ?? "",
                    LastUpdateFullName = FullName ?? "System Administrator",
                    ImportHostories = new List<DisableImportHistory>
                    {
                        new DisableImportHistory
                        {
                            Description = "นำเข้าข้อมูลผู้สมัครสอบคัดเลือกผู้พิการ",
                            CreatedAt = DateTime.Now,
                            CreatedUserId = UserId ?? "",
                            CreatedFullName = FullName ?? "System Administrator",
                            LastUpdatedAt = DateTime.Now,
                            LastUpdateUserId = UserId ?? "",
                            LastUpdateFullName = FullName ?? "System Administrator",
                        }
                    }
                };

                await _context.PeriodExams.AddAsync(imported);

                // import datafile
                System.IO.File.WriteAllBytes(importFile, fileContent);

                using (var c_package = new ExcelPackage(new FileInfo(importFile)))
                {
                    // loop from sheet2 to end

                    for (int i = 1; i < c_package.Workbook.Worksheets.Count; i++)
                    {
                        var workSheet = c_package.Workbook.Worksheets[i];
                        var totalRows = workSheet.Dimension.Rows;
                        var cols = workSheet.GetHeaderColumns();

                        int row = 2;

                        while (row <= totalRows)
                        {
                            var cell1 = workSheet?.Cells[row, 1]?.GetValue<string>();
                            if (cell1 == "" || cell1 == null) break;

                            var r = new Recurit.Exam.Service.Models.Disables.Disable();
                            r.ExamId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ExamID)]?.GetValue<string>();
                            r.CitizenId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PersonalID)]?.GetValue<string>();
                            r.Prefix = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Prefix)]?.GetValue<string>();
                            r.FirstName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.FirstName)]?.GetValue<string>();
                            r.LastName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.LastName)]?.GetValue<string>();
                            r.Gendor = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Gender)]?.GetValue<string>();
                            r.National = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.National)]?.GetValue<string>().IsNull("");
                            r.Race = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Race)]?.GetValue<string>().IsNull("");
                            r.Religion = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Religion)]?.GetValue<string>().IsNull("");
                            r.DateOfBirth = Convert.ToDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.DateOfBirth)]?.GetValue<string>().ToDateTime(DateTimeFormat.Ymd, "-"));
                            r.Marry = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Marry)]?.GetValue<string>();
                            r.Isspecial = "N";
                            r.CitizenCardIssuer = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PersonalCardIssue)]?.GetValue<string>();
                            r.CitizenCardExpireDate = Convert.ToDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PersonalCardExpireDate)]?.GetValue<string>().ToDateTime(DateTimeFormat.Ymd, "-"));
                            r.ApplyDate = (DateTime)workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ApplyDate)]?.GetValue<DateTime>();
                            r.PositionName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PositionName)]?.GetValue<string>().IsNull("");


                            // address
                            r.Addresses.Add(new DisableAddress()
                            {
                                Address = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Address)]?.GetValue<string>() ?? "",
                                Moo = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Moo)]?.GetValue<string>() ?? "",
                                Soi = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Soi)]?.GetValue<string>() ?? "",
                                Road = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Road)]?.GetValue<string>() ?? "",
                                District = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.District)]?.GetValue<string>() ?? "",
                                Amphur = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Amphur)]?.GetValue<string>() ?? "",
                                Province = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Province)]?.GetValue<string>() ?? "",
                                ZipCode = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ZipCode)]?.GetValue<string>() ?? "",
                                Telephone = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Telephone)]?.GetValue<string>() ?? "",
                                Mobile = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Mobile)]?.GetValue<string>() ?? "",
                                Address1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Address1)]?.GetValue<string>() ?? "",
                                Moo1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Moo1)]?.GetValue<string>() ?? "",
                                Soi1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Soi1)]?.GetValue<string>() ?? "",
                                Road1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Road1)]?.GetValue<string>() ?? "",
                                District1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.District1)]?.GetValue<string>() ?? "",
                                Amphur1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Amphur1)]?.GetValue<string>() ?? "",
                                Province1 = "",
                                ZipCode1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ZipCode1)]?.GetValue<string>() ?? "",
                                CreatedAt = DateTime.Now,
                                CreatedUserId = UserId ?? "",
                                CreatedFullName = FullName ?? "System Administrator",
                                LastUpdatedAt = DateTime.Now,
                                LastUpdateUserId = UserId ?? "",
                                LastUpdateFullName = FullName ?? "System Administrator",
                            });

                            // payment
                            r.Payments.Add(new DisablePayment()
                            {
                                PaymentId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PaymentID)]?.GetValue<string>() ?? "",
                                CompanyCode = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CompanyCode)]?.GetValue<string>() ?? "",
                                TextFile = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TextFile)]?.GetValue<string>() ?? "",
                                BankCode = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.BankCode)]?.GetValue<string>() ?? "",
                                AccountNumber = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.AccouontNumer)]?.GetValue<string>() ?? "",
                                TransDate = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TransDate)]?.GetValue<string>() ?? "",
                                TransTime = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TransTime)]?.GetValue<string>() ?? "",
                                CustomerName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CustomerName)]?.GetValue<string>() ?? "",
                                RefNo1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.RefNo1)]?.GetValue<string>() ?? "",
                                TermBranch = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TermBranch)]?.GetValue<string>() ?? "",
                                TellerId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TellerID)]?.GetValue<string>() ?? "",
                                CreditDebit = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CreditDebit)]?.GetValue<string>() ?? "",
                                PaymentType = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Type)]?.GetValue<string>(),
                                ChequeNo = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ChequeNo)]?.GetValue<string>() ?? "",
                                Amount = (decimal)workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Amount)]?.GetValue<decimal>(),
                                ChqueBankCode = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ChqBankCode)]?.GetValue<string>() ?? "",
                                CreatedAt = DateTime.Now,
                                CreatedUserId = UserId ?? "",
                                CreatedFullName = FullName ?? "System Administrator",
                                LastUpdatedAt = DateTime.Now,
                                LastUpdateUserId = UserId ?? "",
                                LastUpdateFullName = FullName ?? "System Administrator",
                            });

                            // occupation
                            r.Occupations.Add(new DisableOccupation()
                            {
                                Occupation = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Occupation)]?.GetValue<string>() ?? "",
                                Position = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Position)]?.GetValue<string>() ?? "",
                                Workplace = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Workplace)]?.GetValue<string>() ?? "",
                                Telephone = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.WorkplaceTelephone)]?.GetValue<string>() ?? "",
                                WorkAge = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.WorkAge)]?.GetValue<string>() ?? "",
                                CreatedAt = DateTime.Now,
                                CreatedUserId = UserId ?? "",
                                CreatedFullName = FullName ?? "System Administrator",
                                LastUpdatedAt = DateTime.Now,
                                LastUpdateUserId = UserId ?? "",
                                LastUpdateFullName = FullName ?? "System Administrator",
                            });

                            // certificate
                            r.Certificates.Add(new DisableCertificate()
                            {
                                CertificateNo = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateNo)]?.GetValue<string>() ?? "",
                                Description = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateDesc)]?.GetValue<string>() ?? "",
                                IssueDate = Convert.ToDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateIssueDate)]?.GetValue<string>().ToDateTime(DateTimeFormat.Ymd, "-")),
                                ExpiredDate = Convert.ToDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateExpireDate)]?.GetValue<string>().ToDateTime(DateTimeFormat.Ymd, "-")),
                                CreatedAt = DateTime.Now,
                                CreatedUserId = UserId ?? "",
                                CreatedFullName = FullName ?? "System Administrator",
                                LastUpdatedAt = DateTime.Now,
                                LastUpdateUserId = UserId ?? "",
                                LastUpdateFullName = FullName ?? "System Administrator",
                            });

                            r.Educations.Add(new DisableEducation()
                            {
                                Degree = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Degree)]?.GetValue<string>() ?? "",
                                Major = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Major)]?.GetValue<string>() ?? "",
                                MajorGroupId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.MajorGroupID)]?.GetValue<string>() ?? "",
                                MajorGroupName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.MajorGroupName)]?.GetValue<string>() ?? "",
                                University = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.University)]?.GetValue<string>() ?? "",
                                GPA = (double)workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.GPA)]?.GetValue<double>(),
                                Specialist = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.SpecialList)]?.GetValue<string>() ?? "",
                                HighDegree = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.HighDegree)]?.GetValue<string>() ?? "",
                                BachelorDate = Convert.ToDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.BachelorDate)]?.GetValue<string>().ToDateTime(DateTimeFormat.Ymd, "-")),
                                CreatedAt = DateTime.Now,
                                CreatedUserId = UserId ?? "",
                                CreatedFullName = FullName ?? "System Administrator",
                                LastUpdatedAt = DateTime.Now,
                                LastUpdateUserId = UserId ?? "",
                                LastUpdateFullName = FullName ?? "System Administrator",
                            });

                            r.PeriodExam = imported;
                            _context.Disables.Add(r);

                            //imported.Disables.Add(r);
                            row++;

                        }
                    }
                }

                // finally save to database

                _context.SaveChanges();

                return Success();

            }
            catch (Exception ex)
            {
                await _minioService.DeleteFileAsync(Guid.Parse(import_doc_id));
                return Error(ex);
            }
            finally
            {
                if (System.IO.File.Exists(importFile))
                    System.IO.File.Delete(importFile);
            }
        }

        /// <summary>
        /// ลบข้อมูลนำข้อมูลผู้สมัครสอบคัดเลือกผู้พิการ
        /// </summary>
        /// <param name="id">รหัสรอบการสอบคัดเลือกผู้พิการ</param>
        /// <returns></returns>
        /// <response code="200">เมื่อทำนำเข้าข้อมูลสำเร็จ</response>
        /// <response code="400">ค่าตัวแปรที่ส่งมาไม่ถูกต้อง</response>
        /// <response code="401">ไม่ได้ Login เข้าระบบ</response>
        /// <response code="500">เมื่อเกิดข้อผิดพลาดในการทำงาน</response>
        [HttpDelete("candidate/{id:length(36)}")]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        [ProducesResponseType(StatusCodes.Status401Unauthorized)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        public async Task<ActionResult<ResponseObject>> DeleteCandidateFileAsync(Guid id)
        {
            try
            {
                var data = await _context.PeriodExams.AsQueryable()
                                .Where(x => x.CheckDisability == true)
                                .Include(x => x.ImportHostories)
                                .Include(x => x.ImportFile)
                                .Include(x => x.Disables)
                                .ThenInclude(x => x.Addresses)
                                .Include(x => x.Disables)
                                .ThenInclude(x => x.Occupations)
                                .Include(x => x.Disables)
                                .ThenInclude(x => x.Certificates)
                                .Include(x => x.Disables)
                                .ThenInclude(x => x.Educations)
                                .Include(x => x.Disables)
                                .ThenInclude(x => x.Payments)
                                .Include(x => x.Disables)
                                .ThenInclude(x => x.Documents)
                                .ThenInclude(x => x.DocumentFile)
                                .Include(x => x.ScoreImport)
                                .ThenInclude(x => x.ImportFile)
                                .Include(x => x.ScoreImport)
                                .ThenInclude(x => x.Scores)
                                .FirstOrDefaultAsync(x => x.Id == id);

                if (data == null)
                    return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound);

                var rec_import_id = data.ImportFile.Id.ToString("D");
                var score_import_id = data.ScoreImport != null ? data.ScoreImport.ImportFile.Id.ToString("D") : "-";

                _context.PeriodExams.Remove(data);
                await _context.SaveChangesAsync();

                // delete upload candidate file
                await _minioService.DeleteFileAsync(Guid.Parse(rec_import_id));

                // delete score file
                if (score_import_id != "-")
                    await _minioService.DeleteFileAsync(Guid.Parse(score_import_id));

                return Success();
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        /// <summary>
        /// แสดงประวัติการนำเข้าข้อมูลการสอบคัดเลือกผู้พิการ
        /// </summary>
        /// <param name="id">รหัสรอบการสอบคัดเลือกผู้พิการ</param>
        /// <returns></returns>
        /// <response code="200">เมื่อทำนำเข้าข้อมูลสำเร็จ</response>
        /// <response code="400">ค่าตัวแปรที่ส่งมาไม่ถูกต้อง</response>
        /// <response code="401">ไม่ได้ Login เข้าระบบ</response>
        /// <response code="500">เมื่อเกิดข้อผิดพลาดในการทำงาน</response>
        [HttpGet("history/{id:length(36)}")]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        [ProducesResponseType(StatusCodes.Status401Unauthorized)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        public async Task<ActionResult<ResponseObject>> GetImportHistoryAsync(Guid id)
        {
            try
            {
                var data = await _context.DisableImportHistories.AsQueryable()
                                    .Include(x => x.PeriodExam)
                                    .Where(x => x.PeriodExam.Id == id)
                                    .Where(x => x.PeriodExam.CheckDisability == true)
                                    .OrderByDescending(x => x.CreatedAt)
                                    .ToListAsync();

                return Success(data);
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }


        /// <summary>
        /// นำเข้ารายชื่อผู้สมัครสอบคัดเลือกผู้พิการ
        /// </summary>
        /// <param name="id">รหัสรอบการสอบคัดเลือกผู้พิการ</param>
        /// <returns></returns>
        /// <response code="200">เมื่อทำนำเข้าข้อมูลสำเร็จ</response>
        /// <response code="400">ค่าตัวแปรที่ส่งมาไม่ถูกต้อง</response>
        /// <response code="401">ไม่ได้ Login เข้าระบบ</response>
        /// <response code="500">เมื่อเกิดข้อผิดพลาดในการทำงาน</response>
        [HttpPost("candidate/{id:length(36)}"), DisableRequestSizeLimit]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status400BadRequest)]
        [ProducesResponseType(StatusCodes.Status401Unauthorized)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        public async Task<ActionResult<ResponseObject>> ImportCandidateFileByIdAsync(Guid id)
        {
            var tmpDir = Path.Combine(_webHostEnvironment.ContentRootPath, "tmp");
            if (!Directory.Exists(tmpDir))
                Directory.CreateDirectory(tmpDir);

            var importFile = Path.Combine(tmpDir, $"c_{DateTime.Now.ToString("ddMMyyyyHHmmss")}.xlsx");
            var import_doc_id = "";

            try
            {
                if (Request.Form.Files == null || Request.Form.Files.Count == 0)
                {
                    return Error(GlobalMessages.NoFileToUpload);
                }

                var imported = await _context.PeriodExams.AsQueryable()
                                        .Where(x => x.CheckDisability == true)
                                        .Include(x => x.ImportHostories)
                                        .Include(x => x.ImportFile)
                                        .Include(x => x.Disables)
                                        .ThenInclude(x => x.Addresses)
                                        .Include(x => x.Disables)
                                        .ThenInclude(x => x.Occupations)
                                        .Include(x => x.Disables)
                                        .ThenInclude(x => x.Certificates)
                                        .Include(x => x.Disables)
                                        .ThenInclude(x => x.Educations)
                                        .Include(x => x.Disables)
                                        .ThenInclude(x => x.Payments)
                                        .Include(x => x.Disables)
                                        .FirstOrDefaultAsync(x => x.Id == id);

                if (imported == null)
                    return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound);

                if (imported.Disables != null)
                {
                    // remove old score data
                    _context.Disables.RemoveRange(imported.Disables);
                    await _context.SaveChangesAsync();
                }

                var file = Request.Form.Files[0];
                var doc = await _minioService.UploadFileAsync(file);
                import_doc_id = doc.Id.ToString("D");

                var fileContent = (await _minioService.DownloadFileAsync(doc.Id)).FileContent;

                // สร้างรอบการบรรจุ โดยเอาเข้ามูลมาใส่จาก Request
                imported.ImportHostories.Add(new DisableImportHistory
                {
                    Description = "นำเข้าข้อมูลผู้สมัครสอบคัดเลือกผู้พิการ",
                    CreatedAt = DateTime.Now,
                    CreatedUserId = UserId ?? "",
                    CreatedFullName = FullName ?? "System Administrator",
                    LastUpdatedAt = DateTime.Now,
                    LastUpdateUserId = UserId ?? "",
                    LastUpdateFullName = FullName ?? "System Administrator",
                });

                //await _context.DisableImports.AddAsync(imported);

                // import datafile
                System.IO.File.WriteAllBytes(importFile, fileContent);

                using (var c_package = new ExcelPackage(new FileInfo(importFile)))
                {
                    // loop from sheet2 to end

                    for (int i = 1; i < c_package.Workbook.Worksheets.Count; i++)
                    {
                        var workSheet = c_package.Workbook.Worksheets[i];
                        var totalRows = workSheet.Dimension.Rows;
                        var cols = workSheet.GetHeaderColumns();

                        int row = 2;

                        while (row <= totalRows)
                        {
                            var cell1 = workSheet?.Cells[row, 1]?.GetValue<string>();
                            if (cell1 == "" || cell1 == null) break;

                            var r = new Recurit.Exam.Service.Models.Disables.Disable();
                            r.ExamId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ExamID)]?.GetValue<string>();
                            r.CitizenId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PersonalID)]?.GetValue<string>();
                            r.Prefix = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Prefix)]?.GetValue<string>();
                            r.FirstName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.FirstName)]?.GetValue<string>();
                            r.LastName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.LastName)]?.GetValue<string>();
                            r.Gendor = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Gender)]?.GetValue<string>();
                            r.National = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.National)]?.GetValue<string>().IsNull("");
                            r.Race = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Race)]?.GetValue<string>().IsNull("");
                            r.Religion = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Religion)]?.GetValue<string>().IsNull("");
                            r.DateOfBirth = Convert.ToDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.DateOfBirth)]?.GetValue<string>().ToDateTime(DateTimeFormat.Ymd, "-"));
                            r.Marry = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Marry)]?.GetValue<string>();
                            r.Isspecial = "N";
                            r.CitizenCardIssuer = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PersonalCardIssue)]?.GetValue<string>();
                            r.CitizenCardExpireDate = Convert.ToDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PersonalCardExpireDate)]?.GetValue<string>().ToDateTime(DateTimeFormat.Ymd, "-"));
                            r.ApplyDate = (DateTime)workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ApplyDate)]?.GetValue<DateTime>();
                            r.PositionName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PositionName)]?.GetValue<string>().IsNull("");

                            // address
                            r.Addresses.Add(new DisableAddress()
                            {
                                Address = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Address)]?.GetValue<string>() ?? "",
                                Moo = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Moo)]?.GetValue<string>() ?? "",
                                Soi = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Soi)]?.GetValue<string>() ?? "",
                                Road = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Road)]?.GetValue<string>() ?? "",
                                District = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.District)]?.GetValue<string>() ?? "",
                                Amphur = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Amphur)]?.GetValue<string>() ?? "",
                                Province = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Province)]?.GetValue<string>() ?? "",
                                ZipCode = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ZipCode)]?.GetValue<string>() ?? "",
                                Telephone = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Telephone)]?.GetValue<string>() ?? "",
                                Mobile = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Mobile)]?.GetValue<string>() ?? "",
                                Address1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Address1)]?.GetValue<string>() ?? "",
                                Moo1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Moo1)]?.GetValue<string>() ?? "",
                                Soi1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Soi1)]?.GetValue<string>() ?? "",
                                Road1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Road1)]?.GetValue<string>() ?? "",
                                District1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.District1)]?.GetValue<string>() ?? "",
                                Amphur1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Amphur1)]?.GetValue<string>() ?? "",
                                Province1 = "",
                                ZipCode1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ZipCode1)]?.GetValue<string>() ?? "",
                                CreatedAt = DateTime.Now,
                                CreatedUserId = UserId ?? "",
                                CreatedFullName = FullName ?? "System Administrator",
                                LastUpdatedAt = DateTime.Now,
                                LastUpdateUserId = UserId ?? "",
                                LastUpdateFullName = FullName ?? "System Administrator",
                            });

                            // payment
                            r.Payments.Add(new DisablePayment()
                            {
                                PaymentId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PaymentID)]?.GetValue<string>() ?? "",
                                CompanyCode = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CompanyCode)]?.GetValue<string>() ?? "",
                                TextFile = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TextFile)]?.GetValue<string>() ?? "",
                                BankCode = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.BankCode)]?.GetValue<string>() ?? "",
                                AccountNumber = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.AccouontNumer)]?.GetValue<string>() ?? "",
                                TransDate = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TransDate)]?.GetValue<string>() ?? "",
                                TransTime = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TransTime)]?.GetValue<string>() ?? "",
                                CustomerName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CustomerName)]?.GetValue<string>() ?? "",
                                RefNo1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.RefNo1)]?.GetValue<string>() ?? "",
                                TermBranch = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TermBranch)]?.GetValue<string>() ?? "",
                                TellerId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TellerID)]?.GetValue<string>() ?? "",
                                CreditDebit = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CreditDebit)]?.GetValue<string>() ?? "",
                                PaymentType = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Type)]?.GetValue<string>(),
                                ChequeNo = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ChequeNo)]?.GetValue<string>() ?? "",
                                Amount = (decimal)workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Amount)]?.GetValue<decimal>(),
                                ChqueBankCode = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ChqBankCode)]?.GetValue<string>() ?? "",
                                CreatedAt = DateTime.Now,
                                CreatedUserId = UserId ?? "",
                                CreatedFullName = FullName ?? "System Administrator",
                                LastUpdatedAt = DateTime.Now,
                                LastUpdateUserId = UserId ?? "",
                                LastUpdateFullName = FullName ?? "System Administrator",
                            });

                            // occupation
                            r.Occupations.Add(new DisableOccupation()
                            {
                                Occupation = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Occupation)]?.GetValue<string>() ?? "",
                                Position = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Position)]?.GetValue<string>() ?? "",
                                Workplace = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Workplace)]?.GetValue<string>() ?? "",
                                Telephone = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.WorkplaceTelephone)]?.GetValue<string>() ?? "",
                                WorkAge = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.WorkAge)]?.GetValue<string>() ?? "",
                                CreatedAt = DateTime.Now,
                                CreatedUserId = UserId ?? "",
                                CreatedFullName = FullName ?? "System Administrator",
                                LastUpdatedAt = DateTime.Now,
                                LastUpdateUserId = UserId ?? "",
                                LastUpdateFullName = FullName ?? "System Administrator",
                            });

                            // certificate
                            r.Certificates.Add(new DisableCertificate()
                            {
                                CertificateNo = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateNo)]?.GetValue<string>() ?? "",
                                Description = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateDesc)]?.GetValue<string>() ?? "",
                                IssueDate = Convert.ToDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateIssueDate)]?.GetValue<string>().ToDateTime(DateTimeFormat.Ymd, "-")),
                                ExpiredDate = Convert.ToDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateExpireDate)]?.GetValue<string>().ToDateTime(DateTimeFormat.Ymd, "-")),
                                CreatedAt = DateTime.Now,
                                CreatedUserId = UserId ?? "",
                                CreatedFullName = FullName ?? "System Administrator",
                                LastUpdatedAt = DateTime.Now,
                                LastUpdateUserId = UserId ?? "",
                                LastUpdateFullName = FullName ?? "System Administrator",
                            });

                            r.Educations.Add(new DisableEducation()
                            {
                                Degree = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Degree)]?.GetValue<string>() ?? "",
                                Major = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Major)]?.GetValue<string>() ?? "",
                                MajorGroupId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.MajorGroupID)]?.GetValue<string>() ?? "",
                                MajorGroupName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.MajorGroupName)]?.GetValue<string>() ?? "",
                                University = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.University)]?.GetValue<string>() ?? "",
                                GPA = (double)workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.GPA)]?.GetValue<double>(),
                                Specialist = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.SpecialList)]?.GetValue<string>() ?? "",
                                HighDegree = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.HighDegree)]?.GetValue<string>() ?? "",
                                BachelorDate = Convert.ToDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.BachelorDate)]?.GetValue<string>().ToDateTime(DateTimeFormat.Ymd, "-")),
                                CreatedAt = DateTime.Now,
                                CreatedUserId = UserId ?? "",
                                CreatedFullName = FullName ?? "System Administrator",
                                LastUpdatedAt = DateTime.Now,
                                LastUpdateUserId = UserId ?? "",
                                LastUpdateFullName = FullName ?? "System Administrator",
                            });

                            r.PeriodExam = imported;
                            r.LastUpdatedAt = DateTime.Now;
                            r.LastUpdateUserId = UserId ?? "";
                            r.LastUpdateFullName = FullName ?? "System Administrator";
                            _context.Disables.Add(r);

                            //imported.Disables.Add(r);
                            row++;

                        }
                    }
                }

                // finally save to database

                _context.SaveChanges();

                return Success();

            }
            catch (Exception ex)
            {
                await _minioService.DeleteFileAsync(Guid.Parse(import_doc_id));
                return Error(ex);
            }
            finally
            {
                if (System.IO.File.Exists(importFile))
                    System.IO.File.Delete(importFile);
            }
        }

        #endregion

        #region " Score File "

        [HttpPost("score/{id:length(36)}"), DisableRequestSizeLimit]
        public async Task<ActionResult<ResponseObject>> ImportScoreFileAsync(Guid id)
        {
            var tmpDir = Path.Combine(_webHostEnvironment.ContentRootPath, "tmp");
            if (!Directory.Exists(tmpDir))
                Directory.CreateDirectory(tmpDir);

            var importFile = Path.Combine(tmpDir, $"s_{DateTime.Now.ToString("ddMMyyyyHHmmss")}.xlsx");
            var import_doc_id = "";

            try
            {
                if (Request.Form.Files == null || Request.Form.Files.Count == 0)
                {
                    return Error(GlobalMessages.NoFileToUpload);
                }

                var rec_import = await _context.PeriodExams.AsQueryable()
                                        .Where(x => x.CheckDisability == true)
                                        .Include(x => x.ScoreImport)
                                        .ThenInclude(x => x.Scores)
                                        .Include(x => x.ImportHostories)
                                        .FirstOrDefaultAsync(x => x.Id == id);

                if (rec_import == null)
                    return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound);

                if (rec_import.ScoreImport != null)
                {
                    // remove old score data
                    if (rec_import.ScoreImport.Scores != null)
                    {
                        _context.DisableScores.RemoveRange(rec_import.ScoreImport.Scores);
                        await _context.SaveChangesAsync();
                    }
                }

                var file = Request.Form.Files[0];
                var doc = await _minioService.UploadFileAsync(file);
                import_doc_id = doc.Id.ToString("D");
                var fileContent = (await _minioService.DownloadFileAsync(doc.Id)).FileContent;

                // create import history
                rec_import.ImportHostories.Add(new DisableImportHistory
                {
                    Description = "นำเข้าข้อมูลผลคะแนนสอบ",
                    CreatedAt = DateTime.Now,
                    CreatedUserId = UserId ?? "",
                    CreatedFullName = FullName ?? "System Administrator",
                    LastUpdatedAt = DateTime.Now,
                    LastUpdateUserId = UserId ?? "",
                    LastUpdateFullName = FullName ?? "System Administrator",
                });

                // create new file import
                var imported = new ScoreImport
                {
                    Year = rec_import.Year,
                    ImportFile = doc,
                    Scores = new List<DisableScore>()
                };

                // import datafile
                System.IO.File.WriteAllBytes(importFile, fileContent);

                using (var c_package = new ExcelPackage(new FileInfo(importFile)))
                {
                    // loop from sheet2 to end
                    for (int i = 1; i < c_package.Workbook.Worksheets.Count; i++)
                    {
                        var workSheet = c_package.Workbook.Worksheets[i];
                        var totalRows = workSheet.Dimension.Rows;
                        var cols = workSheet.GetHeaderColumns();

                        int row = 8;

                        while (row <= totalRows)
                        {
                            var cell1 = workSheet?.Cells[row, 1]?.GetValue<string>();
                            if (cell1 == "" || cell1 == null) break;

                            var r = new DisableScore();
                            r.ExamId = workSheet?.Cells[row, 2]?.GetValue<string>();

                            r.FullA = (int)workSheet?.Cells[7, 7]?.GetValue<string>().Replace("(", "").Replace(")", "").Replace("คะแนน", "").Trim().ToInteger();
                            r.SumA = workSheet?.Cells[row, 7]?.GetValue<string>() == "ขส." ? 0 : (int)workSheet?.Cells[row, 7]?.GetValue<string>().Replace(".00", "").ToInteger();
                            r.PercentageA = workSheet?.Cells[row, 8]?.GetValue<string>() == "ขส." ? 0.0 : (double)workSheet?.Cells[row, 8]?.GetValue<double>();
                            r.AStatus = workSheet?.Cells[row, 9]?.GetValue<string>();

                            r.FullB = (int)workSheet?.Cells[7, 12]?.GetValue<string>().Replace("(", "").Replace(")", "").Replace("คะแนน", "").Trim().ToInteger();
                            r.SumB = workSheet?.Cells[row, 12]?.GetValue<string>() == "ขส." ? 0 : (int)workSheet?.Cells[row, 12]?.GetValue<string>().Replace(".00", "").ToInteger();
                            r.PercentageB = workSheet?.Cells[row, 13]?.GetValue<string>() == "ขส." ? 0.0 : (double)workSheet?.Cells[row, 13]?.GetValue<double>();
                            r.BStatus = workSheet?.Cells[row, 14]?.GetValue<string>();

                            r.SumAB = workSheet?.Cells[row, 15]?.GetValue<string>() == "ขส." ? 0 : (int)workSheet?.Cells[row, 15]?.GetValue<string>().Replace(".00", "").ToInteger();
                            r.ABStatus = workSheet?.Cells[row, 17]?.GetValue<string>();

                            r.FullC = (int)workSheet?.Cells[7, 20]?.GetValue<string>().Replace("(", "").Replace(")", "").Replace("คะแนน", "").Trim().ToInteger();
                            r.SumC = workSheet?.Cells[row, 20]?.GetValue<string>() == "ขส." ? 0 : (int)workSheet?.Cells[row, 20]?.GetValue<string>().Replace(".00", "").ToInteger();
                            r.PercentageC = workSheet?.Cells[row, 21]?.GetValue<string>() == "ขส." ? 0.0 : (double)workSheet?.Cells[row, 21]?.GetValue<double>();
                            r.CStatus = workSheet?.Cells[row, 22]?.GetValue<string>();

                            r.ExamStatus = workSheet?.Cells[row, 24]?.GetValue<string>();
                            r.Number = workSheet?.Cells[row, 25]?.GetValue<string>() ?? "";
                            r.Major = workSheet.Name;
                            r.LastUpdatedAt = DateTime.Now;
                            r.LastUpdateUserId = UserId ?? "";
                            r.LastUpdateFullName = FullName ?? "System Administrator";

                            imported.Scores.Add(r);
                            row++;
                        }
                    }
                }

                // finally save to database
                rec_import.ScoreImport = imported;
                await _context.SaveChangesAsync();

                return Success();

            }
            catch (Exception ex)
            {
                await _minioService.DeleteFileAsync(Guid.Parse(import_doc_id));
                return Error(ex);
            }
            finally
            {
                if (System.IO.File.Exists(importFile))
                    System.IO.File.Delete(importFile);
            }
        }

        #endregion

        #region " Exam Information "

        /// <summary>
        /// แสดงข้อมูลสำหรับหน้าจอ : รายการข้อมูลผู้สมัครสอบ
        /// </summary>
        [HttpGet("exam")]
        public async Task<ActionResult<ResponseObject>> GetExamResultAsync()
        {
            try
            {
                var data = await _context.PeriodExams.AsQueryable()
                                    .Where(x => x.CheckDisability == true)
                                    .OrderByDescending(x => x.Year)
                                    .ThenByDescending(x => x.Round)
                                    .Select(x => new
                                    {
                                        x.Id,
                                        ExamYear = x.Year == null ? 0 : x.Year.Value.ToThaiYear(),
                                        ExamOrder = x.Round,
                                        Description = x.Name,
                                    })
                                    .ToListAsync();

                var result = new List<dynamic>();

                foreach (var d in data)
                {
                    result.Add(new
                    {
                        ExamYear = d.ExamYear,
                        Decription = d.Description,
                        ExamCount = await GetExamCount(d.Id),
                        PassCount = await GetPassExamCount(d.Id),
                        NotPassCount = (await GetExamCount(d.Id) - await GetPassExamCount(d.Id))
                    });
                }

                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        [HttpPost("exam/{id:length(36)}")]
        public ActionResult<ResponseObject> GetExamResultById([FromBody] DisableExamRequest req, Guid id)
        {
            try
            {
                var periodExam = _context.PeriodExams.AsQueryable()
                                    .Where(x => x.CheckDisability == true)
                                    .FirstOrDefault(x => x.Id == id);

                if (periodExam == null)
                    return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound);

                var p_Id = new MySqlParameter("@id", id);
                var data = new List<dynamic>();
                using (var cmd = _context.Database.GetDbConnection().CreateCommand())
                {
                    cmd.CommandTimeout = 0;
                    var sb = new StringBuilder();
                    cmd.Parameters.Clear();
                    sb.Clear();
                    sb.Append(" SELECT *  ");
                    sb.Append(" FROM exam_info ");
                    sb.Append(" WHERE disable_import_id = @id ");
                    cmd.Parameters.Add(p_Id);

                    if (req.ExamAttribute != null && req.ExamAttribute != "")
                    {
                        sb.Append(" AND examAttribute = @a ");
                        cmd.Parameters.Add(new MySqlParameter("@a", req.ExamAttribute));
                    }

                    if (req.ExamResult != null && req.ExamResult != "")
                    {
                        sb.Append(" AND result = @r ");
                        cmd.Parameters.Add(new MySqlParameter("@r", req.ExamResult));
                    }

                    cmd.CommandText = sb.ToString();

                    _context.Database.OpenConnection();
                    var da = new MySqlDataAdapter(cmd as MySqlCommand);
                    var dt = new DataTable();
                    da.Fill(dt);

                    foreach (DataRow dr in dt.Rows)
                    {
                        data.Add(new
                        {
                            examID = dr["examID"].ToString(),
                            profileID = dr["profileID"].ToString(),
                            prefix = dr["prefix"].ToString(),
                            fullName = dr["fullName"].ToString(),
                            dateOfBirth = dr["dateofbirth"] == null ? "" : Convert.ToDateTime(dr["dateofbirth"]).ToThaiShortDate(),
                            gender = dr["gender"].ToString(),
                            degree = dr["degree"].ToString(),
                            major = dr["major"].ToString(),
                            majorgroup = dr["majorgroup"].ToString(),
                            certificateNo = dr["certificateno"].ToString(),
                            certificateIssueDate = dr["certificateIssueDate"] == null ? "" : Convert.ToDateTime(dr["certificateIssueDate"]).ToThaiShortDate(),
                            ExamScore = dr["score"] == null ? 0 : dr["score"].ToString().ToInteger(),
                            ExamResult = dr["result"].ToString(),
                            ExamAttribute = dr["examAttribute"].ToString(),
                            Remark = dr["remark"].ToString(),
                            IsSpecial = dr["isspecial"].ToString(),
                            applyDate = dr["applydate"] == null ? "" : Convert.ToDateTime(dr["applydate"]).ToThaiShortDate(),
                            university = dr["university"].ToString(),
                            position_name = dr["position_name"].ToString(),
                            exam_name = dr["exam_name"].ToString(),
                            exam_order = dr["exam_order"].ToString(),
                            score_year = Convert.ToInt32(dr["score_year"]).ToThaiYear().ToString(),
                        });
                    }

                }

                dynamic header = null;
                using (var cmd = _context.Database.GetDbConnection().CreateCommand())
                {
                    var sb = new StringBuilder();
                    cmd.CommandTimeout = 0;
                    cmd.Parameters.Clear();
                    sb.Clear();
                    sb.Append(" SELECT *  ");
                    sb.Append(" FROM sum_exam_info ");
                    sb.Append(" WHERE disable_import_id = @id ");
                    cmd.Parameters.Add(p_Id);

                    cmd.CommandText = sb.ToString();


                    _context.Database.OpenConnection();
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd as MySqlCommand);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    if (dt.Rows.Count == 0)
                    {
                        header = new
                        {
                            count = 0,
                            pass = 0,
                            notpass = 0
                        };
                    }
                    else
                    {
                        var dr = dt.Rows[0];
                        header = new
                        {
                            count = dr["count"].ToString().ToInteger(),
                            pass = dr["pass"].ToString().ToInteger(),
                            notpass = dr["notpass"].ToString().ToInteger()
                        };
                    }
                }

                return Success(new
                {
                    data = data,
                    header = header,
                    round = periodExam.Round,
                    year = periodExam.Year
                });
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        [HttpGet("exam/{id:length(36)}/{examId}")]
        public async Task<ActionResult<ResponseObject>> GetExamResultByPersonAsync(Guid id, string examId)
        {
            try
            {
                var data = await _context.Disables.AsQueryable()
                                    .Include(x => x.PeriodExam)
                                    .Include(x => x.Documents)
                                    .ThenInclude(x => x.DocumentFile)
                                    .Where(x => x.PeriodExam.Id == id)
                                    .Where(x => x.PeriodExam.CheckDisability == true)
                                    .Where(x => x.ExamId == examId)
                                    .Join(_context.DisableScores.AsQueryable()
                                            .Include(x => x.ScoreImport),
                                            rc => new { rc.PeriodExam.Year, rc.ExamId },
                                            sc => new { sc.ScoreImport.Year, sc.ExamId },
                                            (p, sr) => new
                                            {
                                                ExamID = p.ExamId,
                                                ProfileID = p.CitizenId,
                                                p.Prefix,
                                                FullName = $"{p.FirstName} {p.LastName}",
                                                DateOfBirth = p.DateOfBirth.ToThaiShortDate(),
                                                Gender = p.Gendor,
                                                Degree = p.Educations.First().Degree,
                                                Major = p.Educations.First().Major,
                                                CertificateNo = p.Certificates.First().CertificateNo,
                                                CertificateIssueDate = p.Certificates.First().IssueDate.ToThaiShortDate(),
                                                ExamResult = sr == null ? "" : sr.ExamStatus,
                                                ExamAttribute = _disableService.CheckValidCertificate(p.Certificates.First().IssueDate, 5) ? "มีคุณสมบัติ" : "ไม่มีคุณสมบัติ",
                                                IsSpecial = p.Isspecial,
                                                Remark = p.Remark,
                                                University = p.Educations.First().University,
                                                PositionName = p.PositionName,
                                                ExamName = p.PeriodExam.Name,
                                                ExamOrder = p.PeriodExam.Round,
                                                ExamYear = p.PeriodExam.Year == null ? 0 : p.PeriodExam.Year.Value.ToThaiYear(),
                                                Score = sr == null ? 0 : sr.SumA + sr.SumB + sr.SumC,
                                                Number = sr == null ? "" : sr.Number,
                                                CitizenId = p.CitizenId,
                                                ExamCount = GetExamCountTe(p.CitizenId),
                                                ScoreExpire = p.PeriodExam.AnnouncementDate == null ? "" : p.PeriodExam.AnnouncementDate.Value.AddYears(2).ToThaiShortDate(),
                                                ScoreResult = sr == null ? null : new
                                                {
                                                    ScoreAFull = sr.FullA,
                                                    ScoreA = sr.SumA,
                                                    ScoreBFull = sr.FullB,
                                                    ScoreB = sr.SumB,
                                                    ScoreCFull = sr.FullC,
                                                    ScoreC = sr.SumC,
                                                    ScoreSumFull = sr.FullA + sr.FullB + sr.FullC,
                                                    ScoreSum = sr.SumA + sr.SumB + sr.SumC,
                                                    ExamResult = sr.ExamStatus
                                                },
                                                Attachments = p.Documents.Select(a => new
                                                {
                                                    FileName = a.DocumentFile.FileName,
                                                    DocumentId = a.DocumentFile.Id
                                                })
                                            })
                                    .FirstOrDefaultAsync();

                return Success(new { data, ExamCount = data == null || data.CitizenId == "" ? 0 : GetExamCountTe(data.CitizenId) });
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        #endregion

        #region " For CMS "

        [HttpGet("competitive")]
        [AllowAnonymous]
        public IActionResult GetPeriodForCMS()
        {
            try
            {
                var periods = _context.PeriodExams.AsQueryable()
                                .Where(x => x.CheckDisability == true)
                                .Select(r => new
                                {
                                    id = r.Id,
                                    title = $"{r.Name} ครั้งที่ {r.Round}/{r.Year.Value.ToThaiYear()}",
                                    category = "สำนักงาน ก.ก.",
                                    category_id = 1,
                                    announcement_startDate = r.AnnouncementStartDate == null ? "" : r.AnnouncementStartDate.ToString("yyyy-mm-dd"),
                                    announcement_endDate = r.AnnouncementEndDate == null ? "" : r.AnnouncementEndDate.ToString("yyyy-MM-dd"),
                                    announcementExam = true,
                                    register_startDate = r.RegisterStartDate == null ? "" : r.RegisterStartDate.Value.ToString("yyyy-MM-dd"),
                                    register_endDate = r.RegisterEndDate == null ? "" : r.RegisterEndDate.Value.ToString("yyyy-MM-dd"),
                                    payment_startDate = r.PaymentStartDate == null ? "" : r.PaymentStartDate.Value.ToString("yyyy-MM-dd"),
                                    payment_endDate = r.PaymentEndDate == null ? "" : r.PaymentEndDate.Value.ToString("yyyy-MM-dd"),
                                    exam_date = r.ExamDate == null ? "" : r.ExamDate.Value.ToString("yyyy-MM-dd")

                                })
                                .ToList();

                return Ok(periods);
            }
            catch (Exception ex)
            {
                return StatusCode(500, ex.Message);
            }
        }

        #endregion

        #region " Export Excel "

        [HttpGet("export/exam/{id:length(36)}")]
        public async Task<ActionResult<ResponseObject>> ExportExamAsync(Guid id)
        {
            var data = await _context.PeriodExams.AsQueryable()
                                    .Where(x => x.CheckDisability == true)
                                    .Include(x => x.Disables)
                                    .FirstOrDefaultAsync(x => x.Id == id);

            if (data == null)
                return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound);

            var header = $"{data.Name} ครั้งที่ {data.Round}/{data.Year.Value.ToThaiYear()}";

            var template_dir = Path.Combine(_webHostEnvironment.ContentRootPath, "Templates");

            var template_file = Path.Combine(template_dir, "ExamList.xlsx");

            var tmpDir = Path.Combine(_webHostEnvironment.ContentRootPath, "tmp");
            if (!Directory.Exists(tmpDir))
                Directory.CreateDirectory(tmpDir);

            var exportFile = Path.Combine(tmpDir, $"ExamList_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx");
            try
            {
                // copy template
                System.IO.File.Copy(template_file, exportFile);

                using (var excel = new ExcelPackage(new FileInfo(exportFile)))
                {
                    var workSheet = excel.Workbook.Worksheets[0];


                    workSheet.Cells[1, 2].Value = header;
                    var disables = data.Disables.OrderBy(x => x.ExamId).ToList();

                    var row = 4; // start at row 4
                    foreach (var item in disables)
                    {
                        workSheet.Cells[row, 1].Value = item.ExamId;
                        workSheet.Cells[row, 2].Value = item.CitizenId;
                        workSheet.Cells[row, 3].Value = $"{item.Prefix}{item.FirstName} {item.LastName}";
                        workSheet.Cells[row, 4].Value = item.PositionName;

                        row++;
                    }

                    excel.Save();

                    using (FileStream fs = new FileStream(exportFile, FileMode.Open, FileAccess.Read))
                    {
                        byte[] bytes = System.IO.File.ReadAllBytes(exportFile);
                        fs.Read(bytes, 0, System.Convert.ToInt32(fs.Length));
                        fs.Close();

                        var fname = Path.GetFileName(exportFile);

                        Response.Headers["Content-Disposition"] = $"inline; filename={fname}";

                        var ret = new FileContentResult(bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                        {
                            FileDownloadName = fname
                        };

                        return ret;
                    }
                }
            }
            catch (Exception ex)
            {
                return Error(ex, "ไม่สามารถส่งออกรายชื่อผู้มีสิทธิ์สอบได้!!");
            }
            finally
            {
                if (System.IO.File.Exists(exportFile))
                    System.IO.File.Delete(exportFile);
            }
        }

        [HttpGet("export/pass-exam/{id:length(36)}")]
        public async Task<ActionResult<ResponseObject>> ExportPassExamAsync(Guid id)
        {
            var data = await _context.PeriodExams.AsQueryable()
                                    .Where(x => x.CheckDisability == true)
                                    .Include(x => x.Disables)
                                    .FirstOrDefaultAsync(x => x.Id == id);

            var data_pass = await _context.PeriodExams.AsQueryable()
                                    .Where(x => x.CheckDisability == true)
                                    .Include(x => x.ScoreImport)
                                    .ThenInclude(x => x.Scores)
                                    .FirstOrDefaultAsync(x => x.Id == id);

            if (data == null)
                return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound);

            if (data_pass == null)
                return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound);

            var header = $"{data.Name} ครั้งที่ {data.Round}/{data.Year.Value.ToThaiYear()}";

            var template_dir = Path.Combine(_webHostEnvironment.ContentRootPath, "Templates");

            var template_file = Path.Combine(template_dir, "PassAExamList.xlsx");

            var tmpDir = Path.Combine(_webHostEnvironment.ContentRootPath, "tmp");
            if (!Directory.Exists(tmpDir))
                Directory.CreateDirectory(tmpDir);

            var exportFile = Path.Combine(tmpDir, $"PassExamList_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx");

            try
            {
                var result = (from r in data.Disables.ToList()
                              join s in data_pass.ScoreImport.Scores.Where(x => x.AStatus == "ผ่าน").ToList() on r.ExamId equals s.ExamId
                              select new
                              {
                                  r.ExamId,
                                  r.CitizenId,
                                  r.Prefix,
                                  r.FirstName,
                                  r.LastName,
                                  s.FullA,
                                  s.SumA,
                                  s.AStatus,

                              }).ToList();

                // copy template
                System.IO.File.Copy(template_file, exportFile);

                using (var excel = new ExcelPackage(new FileInfo(exportFile)))
                {
                    var workSheet = excel.Workbook.Worksheets[0];


                    workSheet.Cells[1, 2].Value = header;
                    var disables = data.Disables.OrderBy(x => x.ExamId).ToList();

                    var row = 4; // start at row 4
                    foreach (var item in result)
                    {
                        workSheet.Cells[row, 1].Value = item.ExamId;
                        workSheet.Cells[row, 2].Value = item.CitizenId;
                        workSheet.Cells[row, 3].Value = $"{item.Prefix}{item.FirstName} {item.LastName}";
                        workSheet.Cells[row, 4].Value = item.FullA;
                        workSheet.Cells[row, 5].Value = item.SumA;
                        workSheet.Cells[row, 6].Value = item.AStatus;

                        row++;
                    }

                    excel.Save();

                    using (FileStream fs = new FileStream(exportFile, FileMode.Open, FileAccess.Read))
                    {
                        byte[] bytes = System.IO.File.ReadAllBytes(exportFile);
                        fs.Read(bytes, 0, System.Convert.ToInt32(fs.Length));
                        fs.Close();

                        var fname = Path.GetFileName(exportFile);

                        Response.Headers["Content-Disposition"] = $"inline; filename={fname}";

                        var ret = new FileContentResult(bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                        {
                            FileDownloadName = fname
                        };

                        return ret;
                    }
                }
            }
            catch (Exception ex)
            {
                return Error(ex, "ไม่สามารถส่งออกรายชื่อผู้สอบผ่านได้!!");
            }
            finally
            {
                if (System.IO.File.Exists(exportFile))
                    System.IO.File.Delete(exportFile);
            }
        }

        [HttpGet("export/pass/{id:length(36)}")]
        public async Task<ActionResult<ResponseObject>> ExportPassExamResultAsync(Guid id)
        {
            var data = await _context.PeriodExams.AsQueryable()
                                    .Where(x => x.CheckDisability == true)
                                    .Include(x => x.Disables)
                                    .FirstOrDefaultAsync(x => x.Id == id);

            var data_pass = await _context.PeriodExams.AsQueryable()
                                    .Where(x => x.CheckDisability == true)
                                    .Include(x => x.ScoreImport)
                                    .ThenInclude(x => x.Scores)
                                    .FirstOrDefaultAsync(x => x.Id == id);

            if (data == null)
                return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound);

            if (data_pass == null)
                return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound);

            var header = $"{data.Name} ครั้งที่ {data.Round}/{data.Year.Value.ToThaiYear()}";

            var template_dir = Path.Combine(_webHostEnvironment.ContentRootPath, "Templates");

            var template_file = Path.Combine(template_dir, "PassExamList.xlsx");

            var tmpDir = Path.Combine(_webHostEnvironment.ContentRootPath, "tmp");
            if (!Directory.Exists(tmpDir))
                Directory.CreateDirectory(tmpDir);

            var exportFile = Path.Combine(tmpDir, $"PassExamList_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx");

            try
            {
                var result = (from r in data.Disables.ToList()
                              join s in data_pass.ScoreImport.Scores.Where(x => x.ExamStatus == "ผ่าน").ToList() on r.ExamId equals s.ExamId
                              select new
                              {
                                  r.ExamId,
                                  r.CitizenId,
                                  r.Prefix,
                                  r.FirstName,
                                  r.LastName,
                                  Full = s.FullA + s.FullB + s.FullC,
                                  Sum = s.SumA + s.SumB + s.SumC,
                                  Status = s.ExamStatus,
                                  r.PositionName,
                                  s.Number
                              }).ToList();

                // copy template
                System.IO.File.Copy(template_file, exportFile);

                using (var excel = new ExcelPackage(new FileInfo(exportFile)))
                {
                    var workSheet = excel.Workbook.Worksheets[0];


                    workSheet.Cells[1, 2].Value = header;
                    var disables = data.Disables.OrderBy(x => x.ExamId).ToList();

                    var row = 4; // start at row 4
                    foreach (var item in result)
                    {
                        workSheet.Cells[row, 1].Value = item.ExamId;
                        workSheet.Cells[row, 2].Value = item.CitizenId;
                        workSheet.Cells[row, 3].Value = $"{item.Prefix}{item.FirstName} {item.LastName}";
                        workSheet.Cells[row, 4].Value = item.Full;
                        workSheet.Cells[row, 5].Value = item.Sum;
                        workSheet.Cells[row, 6].Value = item.Status;
                        workSheet.Cells[row, 7].Value = item.Number;
                        workSheet.Cells[row, 8].Value = item.PositionName;

                        row++;
                    }

                    excel.Save();

                    using (FileStream fs = new FileStream(exportFile, FileMode.Open, FileAccess.Read))
                    {
                        byte[] bytes = System.IO.File.ReadAllBytes(exportFile);
                        fs.Read(bytes, 0, System.Convert.ToInt32(fs.Length));
                        fs.Close();

                        var fname = Path.GetFileName(exportFile);

                        Response.Headers["Content-Disposition"] = $"inline; filename={fname}";

                        var ret = new FileContentResult(bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                        {
                            FileDownloadName = fname
                        };

                        return ret;
                    }
                }
            }
            catch (Exception ex)
            {
                return Error(ex, "ไม่สามารถส่งออกรายชื่อผู้สอบผ่านได้!!");
            }
            finally
            {
                if (System.IO.File.Exists(exportFile))
                    System.IO.File.Delete(exportFile);
            }
        }

        /// <summary>
        /// โอนคนสรรหาไปบรรจุ
        /// </summary>
        /// <param name="examId">รหัสรอบสมัคร</param>
        /// <returns></returns>
        /// <response code="200">เมื่อโอนคนสรรหาไปบรรจุสำเร็จ</response>
        /// <response code="401">ไม่ได้ Login เข้าระบบ</response>
        /// <response code="500">เมื่อเกิดข้อผิดพลาดในการทำงาน</response>
        [HttpGet("placement/{examId:length(36)}")]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status401Unauthorized)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        public async Task<ActionResult<ResponseObject>> UpdateAsyncDisableToPlacement(Guid examId)
        {
            try
            {
                await _periodExamService.UpdateAsyncDisableToPlacement(examId);
                return Success();
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        #endregion

        #endregion
    }
}