hrms-api-exam/Services/PeriodExamService.cs

using System.Security.Claims;
using BMA.EHR.Domain.Models.Placement;
using BMA.EHR.Recurit.Exam.Service.Extensions;
using BMA.EHR.Recurit.Exam.Service.Core;
using BMA.EHR.Recurit.Exam.Service.Data;
using BMA.EHR.Recurit.Exam.Service.Models;
using BMA.EHR.Recurit.Exam.Service.Request;
using BMA.EHR.Recurit.Exam.Service.Response;
using BMA.EHR.Recurit.Exam.Service.Responses.Document;
using Microsoft.EntityFrameworkCore;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Text;
using Newtonsoft.Json;
using System.Net.Http.Headers;

namespace BMA.EHR.Recurit.Exam.Service.Services
{
    public class PeriodExamService
    {
        #region " Fields "

        private readonly ApplicationDbContext _context;
        private readonly MetadataDbContext _contextMetadata;
        private readonly OrgDbContext _contextOrg;
        private readonly IHttpContextAccessor _httpContextAccessor;
        private readonly MinIOService _minioService;
        private readonly MailService _mailService;
        private readonly IConfiguration _configuration;
        private readonly IHttpClientFactory _httpClientFactory;

        #endregion

        #region " Constructor and Destructor "

        public PeriodExamService(ApplicationDbContext context,
                                     MetadataDbContext contextMetadata,
                                     OrgDbContext contextOrg,
                                     IHttpContextAccessor httpContextAccessor,
                                     MinIOService minioService,
                                     MailService mailService,
                                   IConfiguration configuration,
                                   IHttpClientFactory httpClientFactory)
        {
            _context = context;
            _contextMetadata = contextMetadata;
            _contextOrg = contextOrg;
            _httpContextAccessor = httpContextAccessor;
            _minioService = minioService;
            _mailService = mailService;
            _configuration = configuration;
            _httpClientFactory = httpClientFactory;
        }

        #endregion

        #region " Properties "

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

        private string? FullName => _httpContextAccessor?.HttpContext?.User?.FindFirst("name")?.Value;
        private string? token => _httpContextAccessor?.HttpContext?.Request.Headers["Authorization"];

        #endregion

        #region " Methods "

        private string GenerateStatusCandidate(string status)
        {
            switch (status.Trim().ToUpper())
            {
                case "REGISTER": return "รอยืนยันสมัครสอบ";
                case "CHECKREGISTER": return "ตรวจสอบข้อมูลสมัครสอบ";
                case "PAYMENT": return "รอชำระค่าสมัครสอบ";
                case "REJECTREGISTER": return "ปฏิเสธตรวจสอบข้อมูลสมัคร";
                case "CHECKPAYMENT": return "ตรวจสอบหลักฐานชำระเงิน";
                case "REJECTPAYMENT": return "หลักฐานชำระเงินไม่ถูกต้อง";
                case "CHECKSEAT": return "จัดที่นั่งสอบ";
                case "CHECKPOINT": return "สรุปคะแนนสอบ";
                case "DONE": return "สอบคัดเลือกสำเร็จ";
                case "WAIVER": return "สละสิทธิ์สอบ";
                default: return status;
            }
        }

        private string GenerateStatusOccupation(string status)
        {
            switch (status.Trim().ToUpper())
            {
                case "PREM": return "ลูกจ้างประจำ";
                case "TEMP": return "ลูกจ้างชั่วคราว";
                case "OTHER": return "ผู้ปฏิบัติงานอื่นในกรุงเทพมหานคร";
                default: return status;
            }
        }

        public async Task<IEnumerable<PeriodExamCandidateResponseItem>> GetsAsync(string type, bool showAll = true)
        {
            var periodExams = await _context.PeriodExams.AsQueryable()
                           .Where(p => p.IsActive)
                           .Where(p => p.CheckDisability == false)
                           .Where(p => type.ToUpper() == "ALL" ? (p.AnnouncementExam == true || p.AnnouncementExam == false) : (type.ToUpper() == "EXAM" ? p.AnnouncementExam == true : p.AnnouncementExam == false))
                           .OrderByDescending(d => d.CreatedAt).ToListAsync();
            var _periodExams = (from x in periodExams
                                select new PeriodExamCandidateResponseItem
                                {
                                    ExamDate = x.ExamDate,
                                    AnnouncementEndDate = x.AnnouncementEndDate,
                                    AnnouncementStartDate = x.AnnouncementStartDate,
                                    AnnouncementDate = x.AnnouncementDate,
                                    CheckDisability = x.CheckDisability,
                                    CheckDocument = x.CheckDocument,
                                    Detail = x.Detail,
                                    EditorCondition = x.EditorCondition,
                                    EditorConfirm = x.EditorConfirm,
                                    Fee = x.Fee,
                                    Id = x.Id,
                                    IsActive = x.IsActive,
                                    Name = x.Name,
                                    Note = x.Note,
                                    Remark = x.Remark,
                                    CompanyCode = x.CompanyCode,
                                    Reason = x.Reason,
                                    RefNo1 = x.RefNo1,
                                    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,
                                    CreatedUserId = x.CreatedUserId,
                                }).AsQueryable()
                                .ToList();

            var roles = _httpContextAccessor?.HttpContext?.User?.FindAll(ClaimTypes.Role)?.Select(c => c.Value).ToList();
            return _periodExams;
        }

        public async Task<PeriodExamCandidateResponseItem?> GetsExamAndCandidateAsync(string examId, bool showAll = true)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .Where(x => x.CheckDisability == false)
                            .Include(x => x.PositionExam)
                            .Include(x => x.BankExam)
                            .Include(x => x.PeriodExamDocuments)
                            .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,
                                EditorCondition = x.EditorCondition,
                                EditorConfirm = x.EditorConfirm,
                                Fee = x.Fee,
                                Id = x.Id,
                                IsActive = x.IsActive,
                                Name = x.Name,
                                Note = x.Note,
                                Remark = x.Remark,
                                CompanyCode = x.CompanyCode,
                                Reason = x.Reason,
                                RefNo1 = x.RefNo1,
                                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,
                                GraduationYearLock = x.GraduationYearLock,
                                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,
                                    PositionLevelId = b.PositionLevelId,
                                    PositionLevelName = b.PositionLevelName,
                                    HighDegree = b.HighDegree,
                                    Code = b.Code,
                                    EducationLevel = b.EducationLevel,
                                }).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(),
                                BarCodes = x.PeriodExamBarCodes.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(),
                                QrCodes = x.PeriodExamQrCodes.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 == Guid.Parse(examId));

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

            var i = 0;
            foreach (var item in periodExam.Images)
            {
                if (periodExam.Images[i].Detail != null && periodExam.Images[i].Detail != "")
                    periodExam.Images[i].Detail = _minioService.ImagesPath(Guid.Parse(periodExam.Images[i].Detail)).Result;
                i++;
            }
            i = 0;
            foreach (var item in periodExam.Documents)
            {
                if (periodExam.Documents[i].Detail != null && periodExam.Documents[i].Detail != "")
                    periodExam.Documents[i].Detail = _minioService.ImagesPath(Guid.Parse(periodExam.Documents[i].Detail)).Result;
                i++;
            }
            i = 0;
            foreach (var item in periodExam.QrCodes)
            {
                if (periodExam.QrCodes[i].Detail != null && periodExam.QrCodes[i].Detail != "")
                    periodExam.QrCodes[i].Detail = _minioService.ImagesPath(Guid.Parse(periodExam.QrCodes[i].Detail)).Result;
                i++;
            }
            i = 0;
            foreach (var item in periodExam.BarCodes)
            {
                if (periodExam.BarCodes[i].Detail != null && periodExam.BarCodes[i].Detail != "")
                    periodExam.BarCodes[i].Detail = _minioService.ImagesPath(Guid.Parse(periodExam.BarCodes[i].Detail)).Result;
                i++;
            }

            return periodExam;
        }

        public async Task<RequestStatusExam?> GetsStatusPaymentAsync(string examId, bool showAll = true)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

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

            return new RequestStatusExam
            {
                Name = periodExam.Name,
                Round = periodExam.Round,
                Year = periodExam.Year,
                Status = periodExam.PaymentEndDate == null ? true : DateTime.Now > periodExam.PaymentEndDate,
                SetSeat = periodExam.SetSeat,
                Fee = periodExam.Fee,
            };
        }

        public async Task<RequestPositionName?> GetsNamePositionAsync(string examId, string positionId)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .Where(x => x.CheckDisability == false)
                            .Include(x => x.PositionExam)
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

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

            var positionExam = await _context.PositionExams.AsQueryable()
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(positionId));

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

            if (positionId != "00000000-0000-0000-0000-000000000000")
            {
                return new RequestPositionName
                {
                    Name = periodExam.Name,
                    Round = periodExam.Round,
                    Year = periodExam.Year,
                    Position = positionExam.PositionName,
                    PositionLevel = positionExam.PositionLevelName,
                    EducationLevel = positionExam.EducationLevel,
                };
            }
            else
            {
                return new RequestPositionName
                {
                    Name = periodExam.Name,
                    Round = periodExam.Round,
                    Year = periodExam.Year,
                    Position = null,
                    PositionLevel = null,
                    EducationLevel = positionExam.EducationLevel,
                };
            }
        }

        public async Task<Guid> CreateAsync(RequestPeriodExam inserted)
        {
            var periodExam = new PeriodExam
            {
                Name = inserted.Name,
                CheckDocument = inserted.CheckDocument,
                Round = inserted.Round,
                Year = inserted.Year,
                Fee = inserted.Fee,
                RegisterStartDate = inserted.RegisterStartDate,
                RegisterEndDate = inserted.RegisterEndDate,
                PaymentStartDate = inserted.PaymentStartDate,
                PaymentEndDate = inserted.PaymentEndDate,
                AnnouncementStartDate = inserted.AnnouncementStartDate,
                AnnouncementDate = inserted.AnnouncementDate,
                ExamDate = inserted.ExamDate,
                AnnouncementEndDate = inserted.AnnouncementEndDate,
                OrganizationCodeId = inserted.OrganizationCodeId,
                OrganizationCodeName = inserted.OrganizationCodeName,
                OrganizationId = inserted.OrganizationId,
                OrganizationName = inserted.OrganizationName,
                PaymentKrungThai = inserted.PaymentKrungThai,
                AnnouncementExam = inserted.AnnouncementExam,
                Category = inserted.Category,
                Detail = inserted.Detail,
                EditorCondition = inserted.EditorCondition,
                EditorConfirm = inserted.EditorConfirm,
                Note = inserted.Note,
                Remark = inserted.Remark,
                CompanyCode = inserted.CompanyCode,
                Reason = inserted.Reason,
                RefNo1 = inserted.RefNo1,
                GraduationYearLock = inserted.GraduationYearLock ?? 3,
                CreatedAt = DateTime.Now,
                CreatedUserId = UserId ?? "",
                LastUpdatedAt = DateTime.Now,
                LastUpdateUserId = UserId ?? "",
                CreatedFullName = FullName ?? "",
                LastUpdateFullName = FullName ?? "",
            };

            if (inserted.PaymentKrungThai == "payment2")
            {
                foreach (var bank in inserted.BankExam)
                {
                    var bankExam = new BankExam
                    {
                        PeriodExam = periodExam,
                        AccountName = bank.AccountName,
                        AccountNumber = bank.AccountNumber,
                        BankName = bank.BankName,
                        CreatedAt = DateTime.Now,
                        CreatedUserId = UserId ?? "",
                        LastUpdatedAt = DateTime.Now,
                        LastUpdateUserId = UserId ?? "",
                        CreatedFullName = FullName ?? "",
                        LastUpdateFullName = FullName ?? "",
                    };
                    await _context.BankExams.AddAsync(bankExam);
                }
            }

            foreach (var position in inserted.PositionExam)
            {
                var positionExam = new PositionExam
                {
                    PeriodExam = periodExam,
                    PositionId = position.PositionId,
                    PositionName = position.PositionName,
                    PositionLevelId = position.PositionLevelId,
                    PositionLevelName = position.PositionLevelName,
                    HighDegree = position.HighDegree,
                    Code = position.Code,
                    TypeId = position.TypeId,
                    TypeName = position.TypeName,
                    EducationLevel = position.EducationLevel,
                    CreatedAt = DateTime.Now,
                    CreatedUserId = UserId ?? "",
                    LastUpdatedAt = DateTime.Now,
                    LastUpdateUserId = UserId ?? "",
                    CreatedFullName = FullName ?? "",
                    LastUpdateFullName = FullName ?? "",
                };
                await _context.PositionExams.AddAsync(positionExam);
            }

            await _context.PeriodExams.AddAsync(periodExam);
            await _context.SaveChangesAsync();

            return periodExam.Id;
        }

        public async Task UpdateAsync(string examId, RequestPeriodExam updated)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .Where(x => x.CheckDisability == false)
                            .Include(x => x.BankExam)
                            .Include(x => x.PositionExam)
                            .Include(x => x.Candidate)
                            .Include(x => x.PeriodExamDocuments)
                            .ThenInclude(x => x.Document)
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

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

            periodExam.Name = updated.Name;
            periodExam.CheckDocument = updated.CheckDocument;
            periodExam.Round = updated.Round;
            periodExam.Year = updated.Year;
            periodExam.Fee = updated.Fee;
            periodExam.RegisterStartDate = updated.RegisterStartDate;
            periodExam.RegisterEndDate = updated.RegisterEndDate;
            periodExam.PaymentStartDate = updated.PaymentStartDate;
            periodExam.PaymentEndDate = updated.PaymentEndDate;
            periodExam.AnnouncementStartDate = updated.AnnouncementStartDate;
            periodExam.AnnouncementDate = updated.AnnouncementDate;
            periodExam.ExamDate = updated.ExamDate;
            periodExam.AnnouncementEndDate = updated.AnnouncementEndDate;
            periodExam.OrganizationCodeId = updated.OrganizationCodeId;
            periodExam.OrganizationCodeName = updated.OrganizationCodeName;
            periodExam.OrganizationId = updated.OrganizationId;
            periodExam.OrganizationName = updated.OrganizationName;
            periodExam.PaymentKrungThai = updated.PaymentKrungThai;
            // periodExam.AnnouncementExam = updated.AnnouncementExam;
            periodExam.Category = updated.Category;
            periodExam.Detail = updated.Detail;
            periodExam.EditorCondition = updated.EditorCondition;
            periodExam.EditorConfirm = updated.EditorConfirm;
            periodExam.Note = updated.Note;
            periodExam.Remark = updated.Remark;
            periodExam.CompanyCode = updated.CompanyCode;
            periodExam.Reason = updated.Reason;
            periodExam.RefNo1 = updated.RefNo1;
            periodExam.GraduationYearLock = updated.GraduationYearLock;
            periodExam.LastUpdatedAt = DateTime.Now;
            periodExam.LastUpdateUserId = UserId ?? "";
            periodExam.LastUpdateFullName = FullName ?? "";

            if (updated.PaymentKrungThai == "payment2")
            {
                foreach (var bank in periodExam.BankExam)
                {
                    var bankData = updated.BankExam
                                        .FirstOrDefault(x => x.Id == bank.Id);
                    if (bankData != null)
                    {
                        bank.AccountName = bankData.AccountName;
                        bank.AccountNumber = bankData.AccountNumber;
                        bank.BankName = bankData.BankName;
                        bank.LastUpdatedAt = DateTime.Now;
                        bank.LastUpdateUserId = UserId ?? "";
                        bank.LastUpdateFullName = FullName ?? "";
                    }
                    else
                    {
                        var bankDelete = await _context.BankExams.AsQueryable()
                                            .FirstOrDefaultAsync(x => x.Id == bank.Id);

                        if (bankDelete != null)
                            _context.BankExams.Remove(bankDelete);
                    }
                }
                var bankAdd = updated.BankExam
                                    .Where(x => x.Id == Guid.Parse("00000000-0000-0000-0000-000000000000"))
                                    .ToList();
                foreach (var bank in bankAdd)
                {
                    var bankExam = new BankExam
                    {
                        PeriodExam = periodExam,
                        AccountName = bank.AccountName,
                        AccountNumber = bank.AccountNumber,
                        BankName = bank.BankName,
                        CreatedAt = DateTime.Now,
                        CreatedUserId = UserId ?? "",
                        LastUpdatedAt = DateTime.Now,
                        LastUpdateUserId = UserId ?? "",
                        CreatedFullName = FullName ?? "",
                        LastUpdateFullName = FullName ?? "",
                    };
                    await _context.BankExams.AddAsync(bankExam);
                }
            }
            else
            {
                foreach (var bank in periodExam.BankExam)
                {
                    var bankDelete = await _context.BankExams.AsQueryable()
                                        .FirstOrDefaultAsync(x => x.Id == bank.Id);

                    if (bankDelete != null)
                        _context.BankExams.Remove(bankDelete);
                }
            }

            foreach (var position in periodExam.PositionExam)
            {
                var positionData = updated.PositionExam
                                        .FirstOrDefault(x => x.Id == position.Id);
                if (positionData != null)
                {
                    position.PositionId = positionData.PositionId;
                    position.PositionName = positionData.PositionName;
                    position.PositionLevelId = positionData.PositionLevelId;
                    position.PositionLevelName = positionData.PositionLevelName;
                    position.HighDegree = positionData.HighDegree;
                    position.Code = positionData.Code;
                    position.TypeId = positionData.TypeId;
                    position.TypeName = positionData.TypeName;
                    position.EducationLevel = positionData.EducationLevel;
                    position.LastUpdatedAt = DateTime.Now;
                    position.LastUpdateUserId = UserId ?? "";
                    position.LastUpdateFullName = FullName ?? "";
                }
                else
                {
                    var positionDelete = await _context.PositionExams.AsQueryable()
                                            .FirstOrDefaultAsync(x => x.Id == position.Id);

                    if (positionDelete != null)
                    {
                        if (periodExam.Candidate.Count() > 0)
                            throw new Exception("รอบนี้มีการสมัครแล้วไม่สามารถลบตำแหน่งได้");
                        _context.PositionExams.Remove(positionDelete);
                    }
                }
            }

            var positionAdd = updated.PositionExam
                                    .Where(x => x.Id == Guid.Parse("00000000-0000-0000-0000-000000000000"))
                                    .ToList();
            foreach (var position in positionAdd)
            {
                var positionExam = new PositionExam
                {
                    PeriodExam = periodExam,
                    PositionId = position.PositionId,
                    PositionName = position.PositionName,
                    PositionLevelId = position.PositionLevelId,
                    PositionLevelName = position.PositionLevelName,
                    HighDegree = position.HighDegree,
                    Code = position.Code,
                    TypeId = position.TypeId,
                    TypeName = position.TypeName,
                    EducationLevel = position.EducationLevel,
                    CreatedAt = DateTime.Now,
                    CreatedUserId = UserId ?? "",
                    LastUpdatedAt = DateTime.Now,
                    LastUpdateUserId = UserId ?? "",
                    CreatedFullName = FullName ?? "",
                    LastUpdateFullName = FullName ?? "",
                };
                await _context.PositionExams.AddAsync(positionExam);
            }

            await _context.SaveChangesAsync();
        }

        public async Task UpdateDocAsync(string examId, IFormFileCollection files)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            // .Where(x => x.CheckDisability == false)
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

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

            foreach (var file in files)
            {
                var doc = await _minioService.UploadFileAsync(file);

                var document = await _context.Documents.AsQueryable()
                                .FirstOrDefaultAsync(x => x.Id == doc.Id);

                if (document == null)
                    throw new Exception(GlobalMessages.NoFileToUpload);

                var periodExamDocument = new PeriodExamDocument
                {
                    PeriodExam = periodExam,
                    Document = document,
                };

                await _context.PeriodExamDocuments.AddAsync(periodExamDocument);
            }

            await _context.SaveChangesAsync();
        }

        public async Task UpdateImgAsync(string examId, IFormFileCollection files)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            // .Where(x => x.CheckDisability == false)
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

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

            foreach (var file in files)
            {
                var doc = await _minioService.UploadFileAsync(file);

                var document = await _context.Documents.AsQueryable()
                                .FirstOrDefaultAsync(x => x.Id == doc.Id);

                if (document == null)
                    throw new Exception(GlobalMessages.NoFileToUpload);

                var periodExamImage = new PeriodExamImage
                {
                    PeriodExam = periodExam,
                    Document = document,
                };

                await _context.PeriodExamImages.AddAsync(periodExamImage);
            }

            await _context.SaveChangesAsync();
        }

        public async Task UpdateBarcodeAsync(string examId, IFormFileCollection files)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .Include(x => x.PeriodExamBarCodes)
                            .ThenInclude(x => x.Document)
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

            if (periodExam == null)
                throw new Exception(GlobalMessages.ExamNotFound);
            if (periodExam.PeriodExamBarCodes != null && periodExam.PeriodExamBarCodes.Count() > 0 && periodExam.PeriodExamBarCodes[0] != null && periodExam.PeriodExamBarCodes[0].Document != null)
                await _minioService.DeleteFileAsync(periodExam.PeriodExamBarCodes[0].Document.Id);
            foreach (var file in files)
            {
                var doc = await _minioService.UploadFileAsync(file);

                var document = await _context.Documents.AsQueryable()
                                .FirstOrDefaultAsync(x => x.Id == doc.Id);

                if (document == null)
                    throw new Exception(GlobalMessages.NoFileToUpload);

                var periodExamBarCode = new PeriodExamBarCode
                {
                    PeriodExam = periodExam,
                    Document = document,
                };

                await _context.PeriodExamBarCodes.AddAsync(periodExamBarCode);
            }

            await _context.SaveChangesAsync();
        }

        public async Task UpdateQrcodeAsync(string examId, IFormFileCollection files)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .Include(x => x.PeriodExamQrCodes)
                            .ThenInclude(x => x.Document)
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

            if (periodExam == null)
                throw new Exception(GlobalMessages.ExamNotFound);
            if (periodExam.PeriodExamQrCodes != null && periodExam.PeriodExamQrCodes.Count() > 0 && periodExam.PeriodExamQrCodes[0] != null && periodExam.PeriodExamQrCodes[0].Document != null)
                await _minioService.DeleteFileAsync(periodExam.PeriodExamQrCodes[0].Document.Id);

            foreach (var file in files)
            {
                var doc = await _minioService.UploadFileAsync(file);

                var document = await _context.Documents.AsQueryable()
                                .FirstOrDefaultAsync(x => x.Id == doc.Id);

                if (document == null)
                    throw new Exception(GlobalMessages.NoFileToUpload);

                var periodExamQrCode = new PeriodExamQrCode
                {
                    PeriodExam = periodExam,
                    Document = document,
                };

                await _context.PeriodExamQrCodes.AddAsync(periodExamQrCode);
            }

            await _context.SaveChangesAsync();
        }

        public async Task DeleteDocument(string documentId)
        {
            await _minioService.DeleteFileAsync(Guid.Parse(documentId));
        }

        public async Task DeleteAsyncDocument(string examId, string documentId)
        {
            var exam = await _context.PeriodExams.AsQueryable()
                            // .Where(x => x.CheckDisability == false)
                            .Include(x => x.PositionExam)
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

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

            await _minioService.DeleteFileAsync(Guid.Parse(documentId));
        }

        public async Task DeleteAsync(string examId)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .Where(x => x.CheckDisability == false)
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

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

            _context.PeriodExams.Remove(periodExam);
            await _context.SaveChangesAsync();
        }

        public async Task<dynamic> GetsCandidateByStatusAsync(string examId, string status, int page = 1, int pageSize = 25, string keyword = "")
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .Where(x => x.CheckDisability == false)
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

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

            if (status == "all")
            {
                var candidate = await _context.Candidates.AsQueryable()
                                .Include(x => x.PositionExam)
                                .Include(x => x.ProfileImg)
                                .OrderByDescending(d => d.CreatedAt)
                                .Where(x => x.PeriodExam == periodExam && x.RegisterDate != null && x.Status != "register" && x.Status != "rejectRegister")
                                .Where(x =>
                                        //(x.PositionExam == null || x.PositionExam.PositionLevelName == null ? false : x.PositionExam.PositionLevelName.Contains(keyword)) ||
                                        //(x.PositionExam == null || x.PositionExam.PositionName == null ? false : x.PositionExam.PositionName.Contains(keyword)) ||
                                        (x.PositionExam.PositionName + x.PositionExam.PositionLevelName).Contains(keyword) ||
                                        (x.CitizenId == null ? false : x.CitizenId.Contains(keyword)) ||
                                        //(x.PrefixName == null ? false : x.PrefixName.Contains(keyword)) ||
                                        //(x.FirstName == null ? false : x.FirstName.Contains(keyword)) ||
                                        //(x.LastName == null ? false : x.LastName.Contains(keyword)) ||
                                        (x.PrefixName + x.FirstName + " " + x.LastName).Contains(keyword) ||
                                        (x.ExamIdenNumber == null ? false : x.ExamIdenNumber.Contains(keyword)) ||
                                        (x.SeatNumber == null ? false : x.SeatNumber.Contains(keyword)) ||
                                        (x.ResultA == null ? false : x.ResultA.Contains(keyword)) ||
                                        (x.ResultB == null ? false : x.ResultB.Contains(keyword)) ||
                                        (x.ResultC == null ? false : x.ResultC.Contains(keyword)) ||
                                        (x.Pass == null ? false : x.Pass.Contains(keyword))
                                // (x.RegisterDate == null ? false : x.RegisterDate.Value.ToThaiShortDateTime().Contains(keyword))
                                )
                                .Select(x => new
                                {
                                    Id = x.Id,
                                    PrefixName = x.PrefixName,
                                    FirstName = x.FirstName,
                                    LastName = x.LastName,
                                    ProfileImg = x.ProfileImg,
                                    CitizenId = x.CitizenId,
                                    Number = x.Number,
                                    RegisterDate = x.RegisterDate,
                                    ExamIdenNumber = x.ExamIdenNumber,
                                    SeatNumber = x.SeatNumber,
                                    ResultA = x.ResultA,
                                    ResultB = x.ResultB,
                                    ResultC = x.ResultC,
                                    Pass = x.Pass,
                                    Email = x.Email,
                                    Status = x.Status,
                                    PositionLevelName = x.PositionExam == null ? null : x.PositionExam.PositionLevelName,
                                    PositionName = x.PositionExam == null ? null : x.PositionExam.PositionName,
                                    IsShowExamInfo = x.IsShowExamInfo,
                                })
                                .Skip((page - 1) * pageSize)
                                .Take(pageSize)
                                .ToListAsync();
                var _candidate = await _context.Candidates.AsQueryable()
                                .Where(x => x.PeriodExam == periodExam && x.RegisterDate != null && x.Status != "register" && x.Status != "rejectRegister")
                                .Where(x =>
                                        //(x.PositionExam == null || x.PositionExam.PositionLevelName == null ? false : x.PositionExam.PositionLevelName.Contains(keyword)) ||
                                        //(x.PositionExam == null || x.PositionExam.PositionName == null ? false : x.PositionExam.PositionName.Contains(keyword)) ||
                                        (x.PositionExam.PositionName + x.PositionExam.PositionLevelName).Contains(keyword) ||
                                        (x.CitizenId == null ? false : x.CitizenId.Contains(keyword)) ||
                                        //(x.PrefixName == null ? false : x.PrefixName.Contains(keyword)) ||
                                        //(x.FirstName == null ? false : x.FirstName.Contains(keyword)) ||
                                        //(x.LastName == null ? false : x.LastName.Contains(keyword)) ||
                                        (x.PrefixName + x.FirstName + " " + x.LastName).Contains(keyword) ||
                                        (x.ExamIdenNumber == null ? false : x.ExamIdenNumber.Contains(keyword)) ||
                                        (x.SeatNumber == null ? false : x.SeatNumber.Contains(keyword)) ||
                                        (x.ResultA == null ? false : x.ResultA.Contains(keyword)) ||
                                        (x.ResultB == null ? false : x.ResultB.Contains(keyword)) ||
                                        (x.ResultC == null ? false : x.ResultC.Contains(keyword)) ||
                                        (x.Pass == null ? false : x.Pass.Contains(keyword))
                                // (x.RegisterDate == null ? false : x.RegisterDate.Value.ToThaiShortDateTime().Contains(keyword))
                                )
                                .CountAsync();
                if (candidate.Where(x => x.Status == "done").FirstOrDefault() != null)
                    candidate = candidate.OrderBy(x => Convert.ToInt32(x.Number)).ToList();
                var i = 0;
                foreach (var item in candidate)
                {
                    if (candidate[i].ProfileImg != null)
                        candidate[i].ProfileImg.Detail = _minioService.ImagesPath(candidate[i].ProfileImg.Id).Result;
                    i++;
                }
                return new { data = candidate, total = _candidate };
            }
            else
            {
                var candidate = await _context.Candidates.AsQueryable()
                                .Include(x => x.PositionExam)
                                .Include(x => x.ProfileImg)
                                .OrderByDescending(d => d.CreatedAt)
                                .Where(x => x.PeriodExam == periodExam && x.Status == status)
                                .Where(x =>
                                        //(x.PositionExam == null || x.PositionExam.PositionLevelName == null ? false : x.PositionExam.PositionLevelName.Contains(keyword)) ||
                                        //(x.PositionExam == null || x.PositionExam.PositionName == null ? false : x.PositionExam.PositionName.Contains(keyword)) ||
                                        (x.PositionExam.PositionName + x.PositionExam.PositionLevelName).Contains(keyword) ||
                                        (x.CitizenId == null ? false : x.CitizenId.Contains(keyword)) ||
                                        //(x.PrefixName == null ? false : x.PrefixName.Contains(keyword)) ||
                                        //(x.FirstName == null ? false : x.FirstName.Contains(keyword)) ||
                                        //(x.LastName == null ? false : x.LastName.Contains(keyword)) ||
                                        (x.PrefixName + x.FirstName + " " + x.LastName).Contains(keyword) ||
                                        (x.ExamIdenNumber == null ? false : x.ExamIdenNumber.Contains(keyword)) ||
                                        (x.SeatNumber == null ? false : x.SeatNumber.Contains(keyword)) ||
                                        (x.ResultA == null ? false : x.ResultA.Contains(keyword)) ||
                                        (x.ResultB == null ? false : x.ResultB.Contains(keyword)) ||
                                        (x.ResultC == null ? false : x.ResultC.Contains(keyword)) ||
                                        (x.Pass == null ? false : x.Pass.Contains(keyword))
                                // (x.RegisterDate == null ? false : x.RegisterDate.Value.ToThaiShortDateTime().Contains(keyword))
                                )
                                .Skip((page - 1) * pageSize)
                                .Take(pageSize)
                                .Select(x => new
                                {
                                    Id = x.Id,
                                    PrefixName = x.PrefixName,
                                    FirstName = x.FirstName,
                                    LastName = x.LastName,
                                    ProfileImg = x.ProfileImg,
                                    CitizenId = x.CitizenId,
                                    Number = x.Number,
                                    RegisterDate = x.RegisterDate,
                                    ExamIdenNumber = x.ExamIdenNumber,
                                    SeatNumber = x.SeatNumber,
                                    ResultA = x.ResultA,
                                    ResultB = x.ResultB,
                                    ResultC = x.ResultC,
                                    Pass = x.Pass,
                                    Email = x.Email,
                                    Status = x.Status,
                                    PositionLevelName = x.PositionExam == null ? null : x.PositionExam.PositionLevelName,
                                    PositionName = x.PositionExam == null ? null : x.PositionExam.PositionName,
                                    IsShowExamInfo = x.IsShowExamInfo,
                                })
                                .ToListAsync();
                var _candidate = await _context.Candidates.AsQueryable()
                                .Where(x => x.PeriodExam == periodExam && x.RegisterDate != null && x.Status != "register" && x.Status != "rejectRegister")
                                .Where(x =>
                                        //(x.PositionExam == null || x.PositionExam.PositionLevelName == null ? false : x.PositionExam.PositionLevelName.Contains(keyword)) ||
                                        //(x.PositionExam == null || x.PositionExam.PositionName == null ? false : x.PositionExam.PositionName.Contains(keyword)) ||
                                        (x.PositionExam.PositionName + x.PositionExam.PositionLevelName).Contains(keyword) ||
                                        (x.CitizenId == null ? false : x.CitizenId.Contains(keyword)) ||
                                        //(x.PrefixName == null ? false : x.PrefixName.Contains(keyword)) ||
                                        //(x.FirstName == null ? false : x.FirstName.Contains(keyword)) ||
                                        //(x.LastName == null ? false : x.LastName.Contains(keyword)) ||
                                        (x.PrefixName + x.FirstName + " " + x.LastName).Contains(keyword) ||
                                        (x.ExamIdenNumber == null ? false : x.ExamIdenNumber.Contains(keyword)) ||
                                        (x.SeatNumber == null ? false : x.SeatNumber.Contains(keyword)) ||
                                        (x.ResultA == null ? false : x.ResultA.Contains(keyword)) ||
                                        (x.ResultB == null ? false : x.ResultB.Contains(keyword)) ||
                                        (x.ResultC == null ? false : x.ResultC.Contains(keyword)) ||
                                        (x.Pass == null ? false : x.Pass.Contains(keyword))
                                // (x.RegisterDate == null ? false : x.RegisterDate.Value.ToThaiShortDateTime().Contains(keyword))
                                )
                                .CountAsync();
                if (candidate.Where(x => x.Status == "done").FirstOrDefault() != null)
                    candidate = candidate.OrderBy(x => Convert.ToInt32(x.Number)).ToList();
                var i = 0;
                foreach (var item in candidate)
                {
                    if (candidate[i].ProfileImg != null)
                        candidate[i].ProfileImg.Detail = _minioService.ImagesPath(candidate[i].ProfileImg.Id).Result;
                    i++;
                }
                return new { data = candidate, total = _candidate };
            }
        }

        public async Task<CandidateInformationResponseItem?> GetsAsyncInformation(string candidateId)
        {
            var candidate = await _context.Candidates.AsQueryable()
                            .Where(x => x.Id == Guid.Parse(candidateId))
                            .Select(x => new CandidateInformationResponseItem
                            {
                                Prefix = x.PrefixName,
                                PrefixId = x.PrefixId != null ? x.PrefixId.ToString() : null,
                                FirstName = x.FirstName,
                                LastName = x.LastName,
                                Nationality = x.Nationality,
                                DateOfBirth = x.DateOfBirth,
                                Religion = x.ReligionName,
                                ReligionId = x.ReligionId != null ? x.ReligionId.ToString() : null,
                                CitizenProvince = x.CitizenProvinceName,
                                CitizenProvinceId = x.CitizenProvinceId != null ? x.CitizenProvinceId.ToString() : null,
                                CitizenDistrict = x.CitizenDistrictName,
                                CitizenDistrictId = x.CitizenDistrictId != null ? x.CitizenDistrictId.ToString() : null,
                                CitizenDate = x.CitizenDate,
                                Email = x.Email,
                                CitizenId = x.CitizenId,
                                Telephone = x.Telephone,
                                MobilePhone = x.MobilePhone,
                                Knowledge = x.Knowledge,
                                ProfileImg = x.ProfileImg == null ? "" : x.ProfileImg.Id.ToString(),
                            })
                            .FirstOrDefaultAsync();

            if (candidate == null)
                return candidate;

            if (candidate.ProfileImg != null && candidate.ProfileImg != "")
                candidate.ProfileImg = _minioService.ImagesPath(Guid.Parse(candidate.ProfileImg)).Result;

            return candidate;
        }

        public async Task<string> GetsAsyncProfileImage(string candidateId)
        {
            var candidate = await _context.Candidates.AsQueryable()
                            .Include(x => x.ProfileImg)
                            .Where(x => x.Id == Guid.Parse(candidateId))
                            .FirstOrDefaultAsync();

            if (candidate == null)
                throw new Exception(GlobalMessages.CandidateNotFound);

            if (candidate.ProfileImg != null)
                candidate.ProfileImg.Detail = _minioService.ImagesPath(candidate.ProfileImg.Id).Result;

            return candidate.ProfileImg == null ? "" : candidate.ProfileImg.Detail;
        }

        public async Task<CandidateOccupationResponseItem?> GetsAsyncOccupation(string candidateId)
        {
            return await _context.Candidates.AsQueryable()
                            .Where(x => x.Id == Guid.Parse(candidateId))
                            .Select(x => new CandidateOccupationResponseItem
                            {
                                OccupationOrg = x.OccupationOrg,
                                OccupationPile = x.OccupationPile,
                                OccupationGroup = x.OccupationGroup,
                                OccupationSalary = x.OccupationSalary,
                                OccupationPosition = x.OccupationPosition,
                                OccupationPositionType = x.OccupationPositionType,
                                OccupationTelephone = x.OccupationTelephone,
                            })
                            .FirstOrDefaultAsync();
        }

        public async Task<CandidateContactResponseItem?> GetsAsyncContact(string candidateId)
        {
            return await _context.Candidates.AsQueryable()
                            .Where(x => x.Id == Guid.Parse(candidateId))
                            .Select(x => new CandidateContactResponseItem
                            {
                                ContactPrefixId = x.ContactPrefixId,
                                ContactPrefix = x.ContactPrefixName,
                                ContactFirstname = x.ContactFirstname,
                                ContactLastname = x.ContactLastname,
                                ContactRelations = x.ContactRelations,
                                ContactTel = x.ContactTel,
                            })
                            .FirstOrDefaultAsync();
        }

        public async Task<CandidateAddressResponseItem?> GetsAsyncAddress(string candidateId)
        {
            return await _context.Candidates.AsQueryable()
                            .Where(x => x.Id == Guid.Parse(candidateId))
                            .Select(x => new CandidateAddressResponseItem
                            {
                                RegistAddress = x.RegistAddress,
                                RegistProvince = x.RegistProvinceName,
                                RegistProvinceId = x.RegistProvinceId != null ? x.RegistProvinceId.ToString() : null,
                                RegistDistrict = x.RegistDistrictName,
                                RegistDistrictId = x.RegistDistrictId != null ? x.RegistDistrictId.ToString() : null,
                                RegistSubDistrict = x.RegistSubDistrictName,
                                RegistSubDistrictId = x.RegistSubDistrictId != null ? x.RegistSubDistrictId.ToString() : null,
                                RegistZipCode = x.RegistZipCode,
                                RegistSame = x.RegistSame,
                                CurrentAddress = x.CurrentAddress,
                                CurrentProvince = x.CurrentProvinceName,
                                CurrentProvinceId = x.CurrentProvinceId != null ? x.CurrentProvinceId.ToString() : null,
                                CurrentDistrict = x.CurrentDistrictName,
                                CurrentDistrictId = x.CurrentDistrictId != null ? x.CurrentDistrictId.ToString() : null,
                                CurrentSubDistrict = x.CurrentSubDistrictName,
                                CurrentSubDistrictId = x.CurrentSubDistrictId != null ? x.CurrentSubDistrictId.ToString() : null,
                                CurrentZipCode = x.CurrentZipCode,
                            })
                            .FirstOrDefaultAsync();
        }

        public async Task<CandidateFamilyResponseItem?> GetsAsyncFamily(string candidateId)
        {
            return await _context.Candidates.AsQueryable()
                            .Where(x => x.Id == Guid.Parse(candidateId))
                            .Select(x => new CandidateFamilyResponseItem
                            {
                                Marry = x.Marry,
                                MarryPrefix = x.MarryPrefixName,
                                MarryPrefixId = x.MarryPrefixId != null ? x.MarryPrefixId.ToString() : null,
                                MarryFirstName = x.MarryFirstName,
                                MarryLastName = x.MarryLastName,
                                MarryOccupation = x.MarryOccupation,
                                MarryNationality = x.MarryNationality,
                                FatherPrefix = x.FatherPrefixName,
                                FatherPrefixId = x.FatherPrefixId != null ? x.FatherPrefixId.ToString() : null,
                                FatherFirstName = x.FatherFirstName,
                                FatherLastName = x.FatherLastName,
                                FatherOccupation = x.FatherOccupation,
                                FatherNationality = x.FatherNationality,
                                MotherPrefix = x.MotherPrefixName,
                                MotherPrefixId = x.MotherPrefixId != null ? x.MotherPrefixId.ToString() : null,
                                MotherFirstName = x.MotherFirstName,
                                MotherLastName = x.MotherLastName,
                                MotherOccupation = x.MotherOccupation,
                                MotherNationality = x.MotherNationality,
                            })
                            .FirstOrDefaultAsync();
        }

        public async Task<Education?> GetsAsyncEducation(string candidateId)
        {
            var candidate = await _context.Candidates.AsQueryable()
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(candidateId));

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

            return await _context.Educations.AsQueryable()
                            .Where(x => x.Candidate == candidate)
                            .FirstOrDefaultAsync();
        }

        public async Task<IEnumerable<Career?>> GetsAsyncCareer(string candidateId)
        {
            return await _context.Careers.AsQueryable()
                            .Where(x => x.Candidate.Id == Guid.Parse(candidateId))
                            .OrderBy(d => d.DurationStart)
                            .ToListAsync();
        }

        public async Task<IEnumerable<FileListResponse?>> GetsAsyncDocument(string candidateId)
        {
            var candidate = await _context.Candidates.AsQueryable()
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(candidateId));

            if (candidate == null)
                throw new Exception(GlobalMessages.CandidateNotFound);

            var document = await _context.CandidateDocuments.AsQueryable()
                                        .Where(x => x.Candidate == candidate)
                                        .OrderBy(d => d.CreatedAt)
                                        .Select(x => new FileListResponse
                                        {
                                            Id = x.Document == null ? "" : x.Document.Id.ToString(),
                                            FileName = x.Document == null ? "" : x.Document.FileName,
                                            FileType = x.Document == null ? "" : x.Document.FileType,
                                            FileSize = x.Document == null ? 0 : x.Document.FileSize,
                                            Detail = x.Document == null ? "" : x.Document.Id.ToString(),
                                        })
                                        .ToListAsync();

            var i = 0;
            foreach (var item in document)
            {
                if (document[i].Detail != null && document[i].Detail != "")
                    document[i].Detail = _minioService.ImagesPath(Guid.Parse(document[i].Detail)).Result;
                i++;
            }

            return document;
        }

        private async Task<List<RequestImportSeat>> ReadExcelCandidate(IFormFile formFile)
        {
            var list = new List<RequestImportSeat>();
            using (var stream = new MemoryStream())
            {
                await formFile.CopyToAsync(stream);
                using (var package = new ExcelPackage(stream))
                {
                    for (int i = 0; i < package.Workbook.Worksheets.Count(); i++)
                    {
                        ExcelWorksheet worksheet = package.Workbook.Worksheets[i];
                        var rowCount = worksheet.Dimension.Rows;
                        for (int row = 4; row <= rowCount; row++)
                        {
                            System.Diagnostics.Debug.WriteLine(worksheet);
                            // if (worksheet.Cells[row, 2].Value != null)
                            // {
                            list.Add(new RequestImportSeat
                            {
                                Number = worksheet.Cells[row, 1].Value != null ? worksheet.Cells[row, 1].Value.ToString() : null,
                                ExamIdenNumber = worksheet.Cells[row, 2].Value != null ? worksheet.Cells[row, 2].Value.ToString() : null,
                                CitizenId = worksheet.Cells[row, 3].Value != null ? worksheet.Cells[row, 3].Value.ToString() : null,
                                SeatNumber = worksheet.Cells[row, 4].Value != null ? worksheet.Cells[row, 4].Value.ToString() : null,

                                PointPath1A = worksheet.Cells[row, 9].Value != null ? worksheet.Cells[row, 9].Value.ToString() : null,
                                PointPath2A = worksheet.Cells[row, 10].Value != null ? worksheet.Cells[row, 10].Value.ToString() : null,
                                PointPath3A = worksheet.Cells[row, 11].Value != null ? worksheet.Cells[row, 11].Value.ToString() : null,
                                PointTotalA = worksheet.Cells[3, 12].Value != null ? worksheet.Cells[3, 12].Value.ToString()?.Split(" ")[0] : null,
                                PointA = worksheet.Cells[row, 12].Value != null ? worksheet.Cells[row, 12].Value.ToString() : null,
                                PointPerA = worksheet.Cells[row, 13].Value != null ? worksheet.Cells[row, 13].Value.ToString() : null,
                                ResultA = worksheet.Cells[row, 14].Value != null ? worksheet.Cells[row, 14].Value.ToString() : null,

                                PointTotalB = worksheet.Cells[3, 15].Value != null ? worksheet.Cells[3, 15].Value.ToString()?.Split(" ")[0] : null,
                                PointB = worksheet.Cells[row, 15].Value != null ? worksheet.Cells[row, 15].Value.ToString() : null,
                                PointPerB = worksheet.Cells[row, 16].Value != null ? worksheet.Cells[row, 16].Value.ToString() : null,
                                ResultB = worksheet.Cells[row, 17].Value != null ? worksheet.Cells[row, 17].Value.ToString() : null,

                                PointPath1C = worksheet.Cells[row, 18].Value != null ? worksheet.Cells[row, 18].Value.ToString() : null,
                                PointPath2C = worksheet.Cells[row, 19].Value != null ? worksheet.Cells[row, 19].Value.ToString() : null,
                                PointTotalC = worksheet.Cells[3, 20].Value != null ? worksheet.Cells[3, 20].Value.ToString()?.Split(" ")[0] : null,
                                PointC = worksheet.Cells[row, 20].Value != null ? worksheet.Cells[row, 20].Value.ToString() : null,
                                PointPerC = worksheet.Cells[row, 21].Value != null ? worksheet.Cells[row, 21].Value.ToString() : null,
                                ResultC = worksheet.Cells[row, 22].Value != null ? worksheet.Cells[row, 22].Value.ToString() : null,

                                Pass = worksheet.Cells[row, 23].Value != null ? worksheet.Cells[row, 23].Value.ToString() : null,
                                ExamReason = worksheet.Cells[row, 27].Value != null ? worksheet.Cells[row, 27].Value.ToString() : null,
                            });
                            if (!int.TryParse(worksheet.Cells[row, 9].Value != null ? worksheet.Cells[row, 9].Value.ToString() : "0", out _))
                                throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
                            if (!int.TryParse(worksheet.Cells[row, 10].Value != null ? worksheet.Cells[row, 10].Value.ToString() : "0", out _))
                                throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
                            if (!int.TryParse(worksheet.Cells[row, 11].Value != null ? worksheet.Cells[row, 11].Value.ToString() : "0", out _))
                                throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
                            if (!int.TryParse(worksheet.Cells[3, 12].Value != null ? worksheet.Cells[3, 12].Value.ToString()?.Split(" ")[0] : "0", out _))
                                throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
                            if (!int.TryParse(worksheet.Cells[row, 12].Value != null ? worksheet.Cells[row, 12].Value.ToString() : "0", out _))
                                throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
                            if (!int.TryParse(worksheet.Cells[row, 13].Value != null ? worksheet.Cells[row, 13].Value.ToString() : "0", out _))
                                throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
                            if (!int.TryParse(worksheet.Cells[3, 15].Value != null ? worksheet.Cells[3, 15].Value.ToString()?.Split(" ")[0] : "0", out _))
                                throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
                            if (!int.TryParse(worksheet.Cells[row, 15].Value != null ? worksheet.Cells[row, 15].Value.ToString() : "0", out _))
                                throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
                            if (!int.TryParse(worksheet.Cells[row, 16].Value != null ? worksheet.Cells[row, 16].Value.ToString() : "0", out _))
                                throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
                            if (!int.TryParse(worksheet.Cells[row, 18].Value != null ? worksheet.Cells[row, 18].Value.ToString() : "0", out _))
                                throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
                            if (!int.TryParse(worksheet.Cells[row, 19].Value != null ? worksheet.Cells[row, 19].Value.ToString() : "0", out _))
                                throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
                            if (!int.TryParse(worksheet.Cells[3, 20].Value != null ? worksheet.Cells[3, 20].Value.ToString()?.Split(" ")[0] : "0", out _))
                                throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
                            if (!int.TryParse(worksheet.Cells[row, 20].Value != null ? worksheet.Cells[row, 20].Value.ToString() : "0", out _))
                                throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
                            if (!int.TryParse(worksheet.Cells[row, 21].Value != null ? worksheet.Cells[row, 21].Value.ToString() : "0", out _))
                                throw new Exception("กรอกจำนวนคะแนนไม่ถูกต้อง");
                            // }
                        }
                    }
                }
            }
            return list;
        }

        public async Task UploadSeatCandidateAsync(string examId, IFormFile excels)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .Where(x => x.CheckDisability == false)
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

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

            var candidates = await _context.Candidates
                                    .AsQueryable()
                                    .Where(x => x.PeriodExam == periodExam)
                                    .ToListAsync();

            var items = await ReadExcelCandidate(excels);

            foreach (var candidate in candidates)
            {
                if (candidate.ExamIdenNumber == null || candidate.CitizenId == null)
                    continue;
                var item = items.FirstOrDefault(x => x.CitizenId == candidate.CitizenId && x.ExamIdenNumber == candidate.ExamIdenNumber);

                if (item != null)
                {
                    if (candidate.Status == "checkSeat")
                    {
                        candidate.SeatNumber = item.SeatNumber;
                        var subject = "แจ้งผลการสมัครสอบคัดเลือก " + periodExam.Name;
                        var body = candidate.FirstName + " " + candidate.LastName + " สถานะการสมัครสอบ: ติดตามประกาศทาง Website";
                        if (candidate.Email != null && candidate.Email != "") _mailService.SendMailToUser(subject, body, candidate.Email);
                        candidate.Status = "checkPoint";
                    }
                    else
                    {
                        if (candidate.Status != "waiver")
                        {
                            var subject = "แจ้งผลการสมัครสอบคัดเลือก " + periodExam.Name;
                            var body = candidate.FirstName + " " + candidate.LastName + " สถานะการสมัครสอบ: สละสิทธิ์สอบ";
                            if (candidate.Email != null && candidate.Email != "") _mailService.SendMailToUser(subject, body, candidate.Email);
                        }
                        candidate.Status = "waiver";
                    }
                }
                else
                {
                    if (candidate.Status != "waiver")
                    {
                        var subject = "แจ้งผลการสมัครสอบคัดเลือก " + periodExam.Name;
                        var body = candidate.FirstName + " " + candidate.LastName + " สถานะการสมัครสอบ: สละสิทธิ์สอบ";
                        if (candidate.Email != null && candidate.Email != "") _mailService.SendMailToUser(subject, body, candidate.Email);
                    }
                    candidate.Status = "waiver";
                }
                periodExam.SetSeat = true;
            }
            await _context.SaveChangesAsync();
        }

        public async Task UploadPointCandidateAsync(string examId, IFormFile excels)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .Where(x => x.CheckDisability == false)
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

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

            var candidates = await _context.Candidates
                                    .AsQueryable()
                                    .Where(x => x.PeriodExam == periodExam)
                                    .ToListAsync();

            var items = await ReadExcelCandidate(excels);

            foreach (var candidate in candidates)
            {
                if (candidate.ExamIdenNumber == null || candidate.CitizenId == null)
                    continue;
                var item = items.FirstOrDefault(x => x.CitizenId == candidate.CitizenId && x.ExamIdenNumber == candidate.ExamIdenNumber);

                if (item != null)
                {
                    if (candidate.Status == "checkPoint" || candidate.Status == "done")
                    {
                        candidate.PointTotalA = item.PointTotalA;
                        candidate.PointPath1A = item.PointPath1A;
                        candidate.PointPath2A = item.PointPath2A;
                        candidate.PointPath3A = item.PointPath3A;
                        candidate.PointA = item.PointA;
                        candidate.PointPerA = item.PointPerA;
                        candidate.ResultA = item.ResultA;
                        candidate.PointTotalB = item.PointTotalB;
                        candidate.PointB = item.PointB;
                        candidate.PointPerB = item.PointPerB;
                        candidate.ResultB = item.ResultB;
                        candidate.PointTotalC = item.PointTotalC;
                        candidate.PointPath1C = item.PointPath1C;
                        candidate.PointPath2C = item.PointPath2C;
                        candidate.PointC = item.PointC;
                        candidate.PointPerC = item.PointPerC;
                        candidate.ResultC = item.ResultC;
                        candidate.Pass = item.Pass;
                        candidate.ExamReason = item.ExamReason;
                        candidate.Number = item.Number;
                        var subject = "แจ้งผลการสมัครสอบคัดเลือก " + periodExam.Name;
                        var body = candidate.FirstName + " " + candidate.LastName + " สถานะการสมัครสอบ: สอบคัดเลือกสำเร็จ <br />" + "ผลการสอบของท่าน คือ " + candidate.Pass;
                        if (candidate.Email != null && candidate.Email != "") _mailService.SendMailToUser(subject, body, candidate.Email);
                        candidate.Status = "done";
                    }
                    else
                    {
                        if (candidate.Status != "waiver")
                        {
                            var subject = "แจ้งผลการสมัครสอบคัดเลือก " + periodExam.Name;
                            var body = candidate.FirstName + " " + candidate.LastName + " สถานะการสมัครสอบ: สละสิทธิ์สอบ";
                            if (candidate.Email != null && candidate.Email != "") _mailService.SendMailToUser(subject, body, candidate.Email);
                        }
                        // candidate.Status = "waiver";
                    }
                }
                else
                {
                    if (candidate.Status != "waiver")
                    {
                        var subject = "แจ้งผลการสมัครสอบคัดเลือก " + periodExam.Name;
                        var body = candidate.FirstName + " " + candidate.LastName + " สถานะการสมัครสอบ: สละสิทธิ์สอบ";
                        if (candidate.Email != null && candidate.Email != "") _mailService.SendMailToUser(subject, body, candidate.Email);
                    }
                    // candidate.Status = "waiver";
                }
                periodExam.SetSeat = true;
            }
            await _context.SaveChangesAsync();
        }

        public async Task<MemoryStream> DownloadCandidateAsync(string examId)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .Where(x => x.CheckDisability == false)
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

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

            var candidates = new List<Candidate>();
            if (periodExam.SetSeat == true)
            {
                candidates = await _context.Candidates
                                        .AsQueryable()
                                        .Include(x => x.PositionExam)
                                        // .OrderBy(x => x.PositionExam == null ? null : x.PositionExam.PositionName)
                                        // .ThenBy(x => x.PositionExam == null ? null : x.PositionExam.PositionLevelName)
                                        // .ThenBy(x => x.ExamIdenNumber)
                                        .OrderBy(x => x.ExamIdenNumber)
                                        .Where(x => x.PeriodExam == periodExam)
                                        .Where(x => x.Status != "waiver")
                                        .Where(x => x.Status != "register")
                                        .ToListAsync();
            }
            else
            {
                candidates = await _context.Candidates
                                        .AsQueryable()
                                        .Include(x => x.PositionExam)
                                        // .OrderBy(x => x.PositionExam == null ? null : x.PositionExam.PositionName)
                                        // .ThenBy(x => x.PositionExam == null ? null : x.PositionExam.PositionLevelName)
                                        // .ThenBy(x => x.ExamIdenNumber)
                                        .OrderBy(x => x.ExamIdenNumber)
                                        .Where(x => x.PeriodExam == periodExam)
                                        .Where(x => x.Status != "register")
                                        .ToListAsync();
            }

            var stream = new MemoryStream();
            using (var package = new ExcelPackage(stream))
            {
                var summarySheet = package.Workbook.Worksheets.Add("Candidate");
                summarySheet.TabColor = System.Drawing.Color.Black;
                // summarySheet.DefaultRowHeight = 17;
                summarySheet.Row(1).Style.Font.Bold = true;
                summarySheet.Cells[1, 1, 1, 8].Value = "ข้อมูลผู้สมัครสอบ";
                summarySheet.Cells[1, 1, 1, 8].Merge = true;
                summarySheet.Cells[2, 1, 3, 1].Value = "ลำดับที่สอบได้";
                summarySheet.Cells[2, 1, 3, 1].Merge = true;
                summarySheet.Cells[2, 2, 3, 2].Value = "เลขประจำตัวสอบ";
                summarySheet.Cells[2, 2, 3, 2].Merge = true;
                summarySheet.Cells[2, 3, 3, 3].Value = "เลขบัตรประชาชน";
                summarySheet.Cells[2, 3, 3, 3].Merge = true;
                summarySheet.Cells[2, 4, 3, 4].Value = "สนามสอบ";
                summarySheet.Cells[2, 4, 3, 4].Merge = true;
                summarySheet.Cells[2, 5, 3, 5].Value = "คำนำหน้า";
                summarySheet.Cells[2, 5, 3, 5].Merge = true;
                summarySheet.Cells[2, 6, 3, 6].Value = "ชื่อ-ชื่อสกุล";
                summarySheet.Cells[2, 6, 3, 6].Merge = true;
                summarySheet.Cells[2, 7, 3, 7].Value = "ตำแหน่ง";
                summarySheet.Cells[2, 7, 3, 7].Merge = true;
                summarySheet.Cells[2, 8, 3, 8].Value = "ระดับ";
                summarySheet.Cells[2, 8, 3, 8].Merge = true;

                summarySheet.Cells[1, 9, 1, 14].Value = "ความรู้ความสามารถทั่วไป (ภาค ก.)";
                summarySheet.Cells[1, 9, 1, 14].Merge = true;
                summarySheet.Cells[2, 9].Value = "ความสามารถในการคิดวิเคราะห์";
                summarySheet.Cells[3, 9].Value = "100 คะแนน";
                summarySheet.Cells[2, 10].Value = "ภาษาอังกฤษ";
                summarySheet.Cells[3, 10].Value = "50 คะแนน";
                summarySheet.Cells[2, 11].Value = "ความรู้และลักษณะการเป็นข้าราชการที่ดี";
                summarySheet.Cells[3, 11].Value = "50 คะแนน";
                // summarySheet.Cells[2, 9].Value = "คะแนนเต็มภาค ก";
                // summarySheet.Cells[3, 9].Value = "100 คะแนน";
                summarySheet.Cells[2, 12].Value = "คะแนนรวม";
                summarySheet.Cells[3, 12].Value = "200 คะแนน";
                summarySheet.Cells[2, 13, 3, 13].Value = "ร้อยละ";
                summarySheet.Cells[2, 13, 3, 13].Merge = true;
                summarySheet.Cells[2, 14, 3, 14].Value = "ผลประเมิน ภาค ก";
                summarySheet.Cells[2, 14, 3, 14].Merge = true;

                summarySheet.Cells[1, 15, 1, 17].Value = "ภาคความรู้ความสามารถที่ใช้เฉพาะตำแหน่ง(ภาค ข)";
                summarySheet.Cells[1, 15, 1, 17].Merge = true;
                // summarySheet.Cells[1, 15].Value = "คะแนนเต็มภาค ข";
                summarySheet.Cells[2, 15].Value = "คะแนนรวม";
                summarySheet.Cells[3, 15].Value = "200 คะแนน";
                summarySheet.Cells[2, 16, 3, 16].Value = "ร้อยละ";
                summarySheet.Cells[2, 16, 3, 16].Merge = true;
                summarySheet.Cells[2, 17, 3, 17].Value = "ผลประเมิน ภาค ข";
                summarySheet.Cells[2, 17, 3, 17].Merge = true;

                summarySheet.Cells[1, 18, 1, 22].Value = "ภาคความเหมาะสมกับตำแหน่ง (ภาค ค)";
                summarySheet.Cells[1, 18, 1, 22].Merge = true;
                summarySheet.Cells[2, 18].Value = "ทดสอบสมรรถนะหลัก";
                summarySheet.Cells[3, 18].Value = "50 คะแนน";
                summarySheet.Cells[2, 19].Value = "สัมภาษณ์";
                summarySheet.Cells[3, 19].Value = "50 คะแนน";
                // summarySheet.Cells[1, 19].Value = "คะแนนเต็มภาค ค";
                summarySheet.Cells[2, 20].Value = "คะแนนรวม";
                summarySheet.Cells[3, 20].Value = "100 คะแนน";
                summarySheet.Cells[2, 21, 3, 21].Value = "ร้อยละ";
                summarySheet.Cells[2, 21, 3, 21].Merge = true;
                summarySheet.Cells[2, 22, 3, 22].Value = "ผลประเมิน ภาค ค";
                summarySheet.Cells[2, 22, 3, 22].Merge = true;

                summarySheet.Cells[1, 23, 3, 23].Value = "ได้ / ตก";
                summarySheet.Cells[1, 23, 3, 23].Merge = true;
                summarySheet.Cells[1, 24, 3, 24].Value = "ชำระค่าธรรมเนียม";
                summarySheet.Cells[1, 24, 3, 24].Merge = true;
                summarySheet.Cells[1, 25, 3, 25].Value = "วันเวลาชำระ";
                summarySheet.Cells[1, 25, 3, 25].Merge = true;
                summarySheet.Cells[1, 26, 3, 26].Value = "วันและเวลาที่สมัคร";
                summarySheet.Cells[1, 26, 3, 26].Merge = true;
                summarySheet.Cells[1, 27, 3, 27].Value = "หมายเหตุ";
                summarySheet.Cells[1, 27, 3, 27].Merge = true;
                int row = 4;

                foreach (var item in candidates)
                {
                    summarySheet.Cells[row, 1].Value = item.Number;
                    summarySheet.Cells[row, 2].Value = item.ExamIdenNumber;
                    summarySheet.Cells[row, 3].Value = item.CitizenId;
                    summarySheet.Cells[row, 4].Value = item.SeatNumber;
                    summarySheet.Cells[row, 5].Value = item.PrefixName;
                    summarySheet.Cells[row, 6].Value = $"{item.FirstName} {item.LastName}";
                    summarySheet.Cells[row, 7].Value = item.PositionExam == null ? "-" : item.PositionExam.PositionName;
                    summarySheet.Cells[row, 8].Value = item.PositionExam == null ? "-" : item.PositionExam.PositionLevelName;

                    summarySheet.Cells[row, 9].Value = item.PointPath1A;
                    summarySheet.Cells[row, 10].Value = item.PointPath2A;
                    summarySheet.Cells[row, 11].Value = item.PointPath3A;
                    // summarySheet.Cells[row, 9].Value = item.PointTotalA;
                    summarySheet.Cells[row, 12].Value = item.PointA;
                    summarySheet.Cells[row, 13].Value = item.PointPerA;
                    summarySheet.Cells[row, 14].Value = item.ResultA;

                    // summarySheet.Cells[row, 15].Value = item.PointTotalB;
                    summarySheet.Cells[row, 15].Value = item.PointB;
                    summarySheet.Cells[row, 16].Value = item.PointPerB;
                    summarySheet.Cells[row, 17].Value = item.ResultB;

                    summarySheet.Cells[row, 17].Value = item.PointPath1C;
                    summarySheet.Cells[row, 19].Value = item.PointPath2C;
                    // summarySheet.Cells[row, 20].Value = item.PointTotalC;
                    summarySheet.Cells[row, 20].Value = item.PointC;
                    summarySheet.Cells[row, 21].Value = item.PointPerC;
                    summarySheet.Cells[row, 22].Value = item.ResultC;

                    summarySheet.Cells[row, 23].Value = item.Pass;
                    summarySheet.Cells[row, 24].Value = item.PeriodExam == null || item.PeriodExam.Fee <= 0 ? "ไม่มีชำระเงิน" : (item.PaymentDate == null ? "ยังไม่ชำระเงิน" : "ชำระแล้ว");
                    summarySheet.Cells[row, 25].Value = item.PaymentDate == null ? "-" : $"{item.PaymentDate.Value.ToThaiShortDate()} {item.PaymentDate.Value.ToString("HH:mm:ss.ffff")}";//xxxxxxxxxxxx
                    summarySheet.Cells[row, 26].Value = item.RegisterDate == null ? "-" : $"{item.RegisterDate.Value.ToThaiShortDate()} {item.RegisterDate.Value.ToString("HH:mm:ss.ffff")}";
                    summarySheet.Cells[row, 27].Value = item.ExamReason;
                    row++;
                }
                summarySheet.Cells[summarySheet.Dimension.Address].AutoFitColumns();
                package.Save();
            }

            stream.Position = 0;
            return stream;
        }

        public async Task<dynamic> NewDownloadCandidateAsync(string examId)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .Where(x => x.CheckDisability == false)
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

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

            var candidates = new List<Candidate>();
            if (periodExam.SetSeat == true)
            {
                candidates = await _context.Candidates
                                        .AsQueryable()
                                        .Include(x => x.PositionExam)
                                        .OrderBy(x => x.ExamIdenNumber)
                                        .Where(x => x.PeriodExam == periodExam)
                                        .Where(x => x.Status != "waiver")
                                        .Where(x => x.Status != "register")
                                        .ToListAsync();
            }
            else
            {
                candidates = await _context.Candidates
                                        .AsQueryable()
                                        .Include(x => x.PositionExam)
                                        .OrderBy(x => x.ExamIdenNumber)
                                        .Where(x => x.PeriodExam == periodExam)
                                        .Where(x => x.Status != "register")
                                        .ToListAsync();
            }
            var _data = new List<object>();

            if (candidates.Count > 0)
            {
                foreach (var item in candidates)
                {
                    _data.Add(new
                    {
                        Number = item.Number != null ? item.Number.ToString() : "-",
                        ExamIdenNumber = item.ExamIdenNumber != null ? item.ExamIdenNumber.ToString() : "-",
                        CitizenId = item.CitizenId != null ? item.CitizenId.ToString() : "-",
                        SeatNumber = item.SeatNumber != null ? item.SeatNumber.ToString() : "-",
                        PrefixName = item.PrefixName != null ? item.PrefixName.ToString() : "-",
                        FullName = item.FirstName != null && item.LastName != null ? $"{item.FirstName} {item.LastName}" : "-",
                        PositionName = item.PositionExam != null && item.PositionExam?.PositionName != null ? item.PositionExam?.PositionName.ToString() : "-",
                        PositionLevelName = item.PositionExam != null && item.PositionExam?.PositionLevelName != null ? item.PositionExam?.PositionLevelName.ToString() : "-",

                        PointPath1A = item.PointPath1A != null ? item.PointPath1A.ToString() : "0",
                        PointPath2A = item.PointPath2A != null ? item.PointPath2A.ToString() : "0",
                        PointPath3A = item.PointPath3A != null ? item.PointPath3A.ToString() : "0",
                        PointA = item.PointA != null ? item.PointA.ToString() : "0",
                        PointPerA = item.PointPerA != null ? item.PointPerA.ToString() : "0",
                        ResultA = item.ResultA != null ? item.ResultA.ToString() : "-",

                        PointB = item.PointB != null ? item.PointB.ToString() : "0",
                        PointPerB = item.PointPerB != null ? item.PointPerB.ToString() : "0",
                        ResultB = item.ResultB != null ? item.ResultB.ToString() : "-",

                        PointPath1C = item.PointPath1C != null ? item.PointPath1C.ToString() : "0",
                        PointPath2C = item.PointPath2C != null ? item.PointPath2C.ToString() : "0",
                        PointC = item.PointC != null ? item.PointC.ToString() : "0",
                        PointPerC = item.PointPerC != null ? item.PointPerC.ToString() : "0",
                        ResultC = item.ResultC != null ? item.ResultC.ToString() : "-",

                        Pass = item.Pass != null ? item.Pass.ToString() : "-",
                        PaymentStatus = item.PeriodExam == null || item.PeriodExam.Fee <= 0 ? "ไม่มีชำระเงิน" : (item.PaymentDate == null ? "ยังไม่ชำระเงิน" : "ชำระแล้ว"),
                        PaymentDate = item.PaymentDate == null ? "-" : $"{item.PaymentDate.Value.ToThaiShortDate()} {item.PaymentDate.Value:HH:mm:ss.ffff}".ToString(),
                        RegisterDate = item.RegisterDate == null ? "-" : $"{item.RegisterDate.Value.ToThaiShortDate()} {item.RegisterDate.Value:HH:mm:ss.ffff}".ToString(),
                        ExamReason = item.ExamReason != null ? item.ExamReason.ToString() : "-"
                    });
                }
            }
            else
            {
                _data.Add(new
                {
                    Number = "-",
                    ExamIdenNumber = "-",
                    CitizenId = "-",
                    SeatNumber = "-",
                    PrefixName = "-",
                    FullName = "-",
                    PositionName = "-",
                    PositionLevelName = "-",

                    PointPath1A = "-",
                    PointPath2A = "-",
                    PointPath3A = "-",
                    PointA = "-",
                    PointPerA = "-",
                    ResultA = "-",

                    PointB = "-",
                    PointPerB = "-",
                    ResultB = "-",

                    PointPath1C = "-",
                    PointPath2C = "-",
                    PointC = "-",
                    PointPerC = "-",
                    ResultC = "-",

                    Pass = "-",
                    PaymentStatus = "-",
                    PaymentDate = "-",
                    RegisterDate = "-",
                    ExamReason = "-"
                });
            }

            return new
            {
                template = "candidate",
                reportName = $"Candidate_{DateTime.Now.ToString("yyyyMMddHHmmssfff")}",
                data = new
                {
                    data = _data
                }
            };
        }

        public async Task<PeriodExam> GetsPaymentExamAsync(string examId)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .Where(x => x.CheckDisability == false)
                            .Include(x => x.BankExam)
                            .Where(x => x.Id == Guid.Parse(examId))
                            .Select(x => new PeriodExam
                            {
                                BankExam = x.BankExam,
                                Fee = x.Fee,
                            })
                            .FirstOrDefaultAsync();

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

            return periodExam;
        }

        public async Task<dynamic> ExportsPaymentExamAsync(string candidateId)
        {
            var periodExam = await _context.Candidates.AsQueryable()
                            .Include(x => x.PeriodExam)
                            .ThenInclude(x => x.PeriodExamBarCodes)
                            .ThenInclude(x => x.Document)
                            .Include(x => x.PeriodExam)
                            .ThenInclude(x => x.PeriodExamQrCodes)
                            .ThenInclude(x => x.Document)
                            .Where(x => x.Id == Guid.Parse(candidateId))
                            .FirstOrDefaultAsync();

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

            return new
            {
                template = "candidate-payment",
                reportName = $"Candidate_{DateTime.Now.ToString("yyyyMMddHHmmssfff")}",
                data = new
                {
                    Remark = periodExam?.PeriodExam?.Remark ?? "-",
                    CompanyCode = periodExam?.PeriodExam?.CompanyCode ?? "-",
                    PeriodExamBarCodes = (periodExam?.PeriodExam?.PeriodExamBarCodes[0]?.Document?.Id ?? null) == null ? "" : _minioService.ImagesPath(periodExam?.PeriodExam?.PeriodExamBarCodes[0]?.Document?.Id ?? Guid.Parse("00000000-0000-0000-0000-000000000000")).Result,
                    Reason = periodExam?.PeriodExam?.Reason ?? "-",
                    RefNo1 = periodExam?.PeriodExam?.RefNo1 ?? "-",
                    PeriodExamQrCodes = (periodExam?.PeriodExam?.PeriodExamQrCodes[0]?.Document?.Id ?? null) == null ? "" : _minioService.ImagesPath(periodExam?.PeriodExam?.PeriodExamQrCodes[0]?.Document?.Id ?? Guid.Parse("00000000-0000-0000-0000-000000000000")).Result,
                    CitizenId = periodExam?.CitizenId ?? "-",
                    FullName = $"{periodExam.PrefixName}{periodExam.FirstName} {periodExam.LastName}",
                    Fee = periodExam?.PeriodExam?.Fee?.ToString() ?? "-",
                    Date = DateTime.Now.Date.ToThaiShortDate(),
                    FeeText = (periodExam?.PeriodExam?.Fee ?? null) == null ? "-" : periodExam?.PeriodExam?.Fee.Value.NumberToThaiText() ?? "-",
                }
            };
        }

        public async Task<List<DashboardResponseItem>> GetsDashboardPaymentExamAsync(string examId)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                                .Where(x => x.CheckDisability == false)
                                .Include(x => x.Candidate)
                                .ThenInclude(x => x.PaymentImg)
                                .Where(x => x.Id == Guid.Parse(examId))
                                .FirstOrDefaultAsync();

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

            var dashboard = new List<DashboardResponseItem>
            {
                new DashboardResponseItem
                {
                    Id = 1,
                    Name = "จำนวนผู้สมัครคัดเลือกทั้งหมด",
                    Count = periodExam.Candidate.Count()
                },
                new DashboardResponseItem
                {
                    Id = 2,
                    Name = "จำนวนผู้ยืนยันการสมัครคัดเลือก",
                    Count = periodExam.Candidate.Where(x=>x.Status!="register").Count()
                },
                new DashboardResponseItem
                {
                    Id = 3,
                    Name = "จำนวนผู้มีสิทธิ์เข้ารับคัดเลือกทั้งหมด",
                    Count = periodExam.Candidate.Where(x=>x.Status!="register").Where(x=>x.ExamIdenNumber != null).Count()
                },
                new DashboardResponseItem
                {
                    Id = 4,
                    Name = "จำนวนผู้เข้ารับการคัดเลือกทั้งหมด",
                    Count = periodExam.Candidate.Where(x=>x.Status!="register").Where(x=>x.SeatNumber != null).Count()
                },
                new DashboardResponseItem
                {
                    Id = 5,
                    Name = "ผ่านการสอบ",
                    Count = periodExam.Candidate.Where(x=>x.Status!="register").Where(x=>x.Pass=="ผ่าน" || x.Pass=="ได้").Count()
                },
                new DashboardResponseItem
                {
                    Id = 6,
                    Name = "ไม่ผ่านการสอบ",
                    Count = periodExam.Candidate.Where(x=>x.Status!="register").Where(x=>x.Pass!=null && x.Pass!="ผ่าน" && x.Pass!="ได้").Count()
                },
                new DashboardResponseItem
                {
                    Id = 7,
                    Name = "เพศชาย",
                    Count = periodExam.Candidate.Where(x=>x.Status!="register").Where(x=>x.PrefixName != null && x.PrefixName=="นาย").Count()
                },
                new DashboardResponseItem
                {
                    Id = 8,
                    Name = "เพศหญิง",
                    Count = periodExam.Candidate.Where(x=>x.Status!="register").Where(x=>x.PrefixName != null && (x.PrefixName=="นาง" || x.PrefixName=="นางสาว")).Count()
                },
                // new DashboardResponseItem
                // {
                //     Id = 8,
                //     Name = "เพศอื่นๆ",
                //     Count = periodExam.Candidate.Count() -periodExam.Candidate.Where(x=>x.PrefixName != null && x.PrefixName.Contains("ชาย")).Count()-periodExam.Candidate.Where(x=>x.PrefixName != null && x.PrefixName.Contains("หญิง")).Count()
                // },
            };

            return dashboard;
        }

        public async Task<MemoryStream> DownloadCandidateAllAsync(string examId)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .Where(x => x.CheckDisability == false)
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

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

            var candidates = await _context.Candidates
                                    .AsQueryable()
                                    .OrderBy(x => x.ExamIdenNumber)
                                    .Where(x => x.PeriodExam == periodExam)
                                    .Where(x => x.Status != "register")
                                    .Select(c => new
                                    {
                                        Id = c.Id,
                                        Status = c.Status,
                                        CitizenId = c.CitizenId,
                                        FullName = $"{c.PrefixName}{c.FirstName} {c.LastName}",
                                        Nationality = c.Nationality,
                                        DateOfBirth = c.DateOfBirth == null ? "" : c.DateOfBirth.Value.Date.ToThaiShortDate(),
                                        Age = c.DateOfBirth == null ? "" : c.DateOfBirth.Value.Date.CalculateAgeStrV2(0, 0),
                                        ReligionName = c.ReligionName,
                                        Telephone = c.Telephone,
                                        MobilePhone = c.MobilePhone,
                                        Email = c.Email,
                                        Knowledge = c.Knowledge,
                                        RegistAddress = c.RegistAddress,
                                        RegistProvinceName = c.RegistProvinceName,
                                        RegistDistrictName = c.RegistDistrictName,
                                        RegistSubDistrictName = c.RegistSubDistrictName,
                                        RegistZipCode = c.RegistZipCode,
                                        CurrentAddress = c.CurrentAddress,
                                        CurrentProvinceName = c.CurrentProvinceName,
                                        CurrentDistrictName = c.CurrentDistrictName,
                                        CurrentSubDistrictName = c.CurrentSubDistrictName,
                                        CurrentZipCode = c.CurrentZipCode,
                                        OccupationOrg = c.OccupationOrg,
                                        OccupationPile = c.OccupationPile,
                                        OccupationGroup = c.OccupationGroup,
                                        OccupationSalary = c.OccupationSalary,
                                        OccupationPosition = c.OccupationPosition,
                                        OccupationPositionType = c.OccupationPositionType,
                                        OccupationTelephone = c.OccupationTelephone,
                                        RegisterDate = c.RegisterDate,
                                        PositionName = c.PositionExam.PositionName,
                                        PositionLevelName = c.PositionExam.PositionLevelName,

                                        ContactFullName = $"{c.ContactPrefixName}{c.ContactFirstname} {c.ContactLastname}",
                                        ContactRelations = c.ContactRelations,
                                        ContactTel = c.ContactTel,

                                        Number = c.Number,
                                        ExamIdenNumber = c.ExamIdenNumber,
                                        SeatNumber = c.SeatNumber,
                                        PointTotalA = c.PointTotalA,
                                        PointPath1A = c.PointPath1A,
                                        PointPath2A = c.PointPath2A,
                                        PointPath3A = c.PointPath3A,
                                        PointA = c.PointA,
                                        PointPerA = c.PointPerA,
                                        ResultA = c.ResultA,
                                        PointTotalB = c.PointTotalB,
                                        PointB = c.PointB,
                                        PointPerB = c.PointPerB,
                                        ResultB = c.ResultB,
                                        PointTotalC = c.PointTotalC,
                                        PointPath1C = c.PointPath1C,
                                        PointPath2C = c.PointPath2C,
                                        PointC = c.PointC,
                                        PointPerC = c.PointPerC,
                                        ResultC = c.ResultC,
                                        Pass = c.Pass,
                                        ExamReason = c.ExamReason,
                                        CreatedAt = c.CreatedAt,
                                    })
                                    .ToListAsync();
            var stream = new MemoryStream();
            using (var package = new ExcelPackage(stream))
            {
                var summarySheet = package.Workbook.Worksheets.Add("Candidate");
                summarySheet.TabColor = System.Drawing.Color.Black;
                // summarySheet.DefaultRowHeight = 17;
                summarySheet.Row(1).Style.Font.Bold = true;
                summarySheet.Cells[1, 1, 2, 1].Value = "ลำดับ";
                summarySheet.Cells[1, 1, 2, 1].Merge = true;
                summarySheet.Cells[1, 2, 2, 2].Value = "ตำแหน่งที่สมัครสอบ";
                summarySheet.Cells[1, 2, 2, 2].Merge = true;
                summarySheet.Cells[1, 3, 2, 3].Value = "ชื่อ-สกุล";
                summarySheet.Cells[1, 3, 2, 3].Merge = true;
                summarySheet.Cells[1, 4, 2, 4].Value = "สัญชาติ";
                summarySheet.Cells[1, 4, 2, 4].Merge = true;
                summarySheet.Cells[1, 5, 2, 5].Value = "ศาสนา";
                summarySheet.Cells[1, 5, 2, 5].Merge = true;
                summarySheet.Cells[1, 6, 2, 6].Value = "วันเดือนปีเกิด";
                summarySheet.Cells[1, 6, 2, 6].Merge = true;
                summarySheet.Cells[1, 7, 2, 7].Value = "อายุ";
                summarySheet.Cells[1, 7, 2, 7].Merge = true;
                summarySheet.Cells[1, 8, 2, 8].Value = "โทรศัพท์";
                summarySheet.Cells[1, 8, 2, 8].Merge = true;
                summarySheet.Cells[1, 9, 2, 9].Value = "อีเมล";
                summarySheet.Cells[1, 9, 2, 9].Merge = true;
                summarySheet.Cells[1, 10, 2, 10].Value = "เลขบัตรประจำตัวประชาชน";
                summarySheet.Cells[1, 10, 2, 10].Merge = true;
                summarySheet.Cells[1, 11, 2, 11].Value = "วุฒิที่ใช้ในการสมัคร";
                summarySheet.Cells[1, 11, 2, 11].Merge = true;
                summarySheet.Cells[1, 12, 2, 12].Value = "ชื่อปริญญา";
                summarySheet.Cells[1, 12, 2, 12].Merge = true;
                summarySheet.Cells[1, 13, 2, 13].Value = "สาขาวิชา/วิชาเอก";
                summarySheet.Cells[1, 13, 2, 13].Merge = true;
                summarySheet.Cells[1, 14, 2, 14].Value = "สถานศึกษา";
                summarySheet.Cells[1, 14, 2, 14].Merge = true;
                summarySheet.Cells[1, 15, 2, 15].Value = "วันที่สำเร็จการศึกษา";
                summarySheet.Cells[1, 15, 2, 15].Merge = true;
                summarySheet.Cells[1, 16, 2, 16].Value = "คะแนนเฉลี่ยสะสม";
                summarySheet.Cells[1, 16, 2, 16].Merge = true;
                summarySheet.Cells[1, 17, 2, 17].Value = "ประเภทสถานศึกษา";
                summarySheet.Cells[1, 17, 2, 17].Merge = true;
                summarySheet.Cells[1, 18, 2, 18].Value = "วุฒิการศึกษาสูงสุด";
                summarySheet.Cells[1, 18, 2, 18].Merge = true;
                summarySheet.Cells[1, 19, 2, 19].Value = "ประเภทตำแหน่งปัจจุบัน";
                summarySheet.Cells[1, 19, 2, 19].Merge = true;
                summarySheet.Cells[1, 20, 2, 20].Value = "ชื่อตำแหน่ง";
                summarySheet.Cells[1, 20, 2, 20].Merge = true;
                summarySheet.Cells[1, 21, 2, 21].Value = "เงินเดือน";
                summarySheet.Cells[1, 21, 2, 21].Merge = true;
                summarySheet.Cells[1, 22, 2, 22].Value = "กลุ่ม/ฝ่าย";
                summarySheet.Cells[1, 22, 2, 22].Merge = true;
                summarySheet.Cells[1, 23, 2, 23].Value = "กอง";
                summarySheet.Cells[1, 23, 2, 23].Merge = true;
                summarySheet.Cells[1, 24, 2, 24].Value = "สังกัด";
                summarySheet.Cells[1, 24, 2, 24].Merge = true;
                summarySheet.Cells[1, 25, 2, 25].Value = "เบอร์โทรที่ทำงาน";
                summarySheet.Cells[1, 25, 2, 25].Merge = true;

                summarySheet.Cells[1, 26, 1, 31].Value = "ประวัติการทำงาน (ตั้งแต่เริ่มปฏิบัติงานกับกรุงเทพฯ - ปัจจุบัน)";
                summarySheet.Cells[1, 26, 1, 31].Merge = true;
                summarySheet.Cells[2, 26].Value = "ตำแหน่ง";
                summarySheet.Cells[2, 27].Value = "ประเภท";
                summarySheet.Cells[2, 28].Value = "เริ่ม";
                summarySheet.Cells[2, 29].Value = "สิ้นสุด";
                summarySheet.Cells[2, 30].Value = "ระยะเวลา";
                summarySheet.Cells[2, 31].Value = "ระยะเวลารวม";

                summarySheet.Cells[1, 32, 1, 36].Value = "ที่อยู่ที่สามารถติดต่อได้";
                summarySheet.Cells[1, 32, 1, 36].Merge = true;
                summarySheet.Cells[2, 32].Value = "เลขที่";
                summarySheet.Cells[2, 33].Value = "ตำบล";
                summarySheet.Cells[2, 34].Value = "อำเภอ";
                summarySheet.Cells[2, 35].Value = "จังหวัด";
                summarySheet.Cells[2, 36].Value = "รหัสไปรษณีย์";

                summarySheet.Cells[1, 37, 1, 41].Value = "ที่อยู่ตามทะเบียนบ้าน";
                summarySheet.Cells[1, 37, 1, 41].Merge = true;
                summarySheet.Cells[2, 37].Value = "เลขที่";
                summarySheet.Cells[2, 38].Value = "ตำบล";
                summarySheet.Cells[2, 39].Value = "อำเภอ";
                summarySheet.Cells[2, 40].Value = "จังหวัด";
                summarySheet.Cells[2, 41].Value = "รหัสไปรษณีย์";

                summarySheet.Cells[1, 42, 2, 42].Value = "บุคคลที่สามารถติดต่อได้";
                summarySheet.Cells[1, 42, 2, 42].Merge = true;
                summarySheet.Cells[1, 43, 2, 43].Value = "เกี่ยวข้องเป็น";
                summarySheet.Cells[1, 43, 2, 43].Merge = true;
                summarySheet.Cells[1, 44, 2, 44].Value = "โทรศัพท์";
                summarySheet.Cells[1, 44, 2, 44].Merge = true;
                summarySheet.Cells[1, 45, 2, 45].Value = "สถานะสอบคัดเลือก";
                summarySheet.Cells[1, 45, 2, 45].Merge = true;
                summarySheet.Cells[1, 46, 2, 46].Value = "ลำดับที่สอบได้";
                summarySheet.Cells[1, 46, 2, 46].Merge = true;
                summarySheet.Cells[1, 47, 2, 47].Value = "เลขประจำตัวสอบ";
                summarySheet.Cells[1, 47, 2, 47].Merge = true;
                summarySheet.Cells[1, 48, 2, 48].Value = "เลขที่นั่งสอบ";
                summarySheet.Cells[1, 48, 2, 48].Merge = true;
                summarySheet.Cells[1, 49, 2, 49].Value = "คะแนนเต็มภาค ก";
                summarySheet.Cells[1, 49, 2, 49].Merge = true;
                summarySheet.Cells[1, 50, 2, 50].Value = "คะแนนภาค ก";
                summarySheet.Cells[1, 50, 2, 50].Merge = true;
                summarySheet.Cells[1, 51, 2, 51].Value = "ผลสอบภาค ก";
                summarySheet.Cells[1, 51, 2, 51].Merge = true;
                summarySheet.Cells[1, 52, 2, 52].Value = "คะแนนเต็มภาค ข";
                summarySheet.Cells[1, 52, 2, 52].Merge = true;
                summarySheet.Cells[1, 53, 2, 53].Value = "คะแนนภาค ข";
                summarySheet.Cells[1, 53, 2, 53].Merge = true;
                summarySheet.Cells[1, 54, 2, 54].Value = "ผลสอบภาค ข";
                summarySheet.Cells[1, 54, 2, 54].Merge = true;
                summarySheet.Cells[1, 55, 2, 55].Value = "คะแนนเต็มภาค ค";
                summarySheet.Cells[1, 55, 2, 55].Merge = true;
                summarySheet.Cells[1, 56, 2, 56].Value = "คะแนนภาค ค";
                summarySheet.Cells[1, 56, 2, 56].Merge = true;
                summarySheet.Cells[1, 57, 2, 57].Value = "ผลสอบภาค ค";
                summarySheet.Cells[1, 57, 2, 57].Merge = true;
                summarySheet.Cells[1, 58, 2, 58].Value = "ผลการสอบ";
                summarySheet.Cells[1, 58, 2, 58].Merge = true;
                summarySheet.Cells[1, 59, 2, 59].Value = "วันที่สมัคร";
                summarySheet.Cells[1, 59, 2, 59].Merge = true;
                summarySheet.Cells[1, 60, 2, 60].Value = "วันและเวลาที่สมัคร";
                summarySheet.Cells[1, 60, 2, 60].Merge = true;
                int row = 3;
                int num = 1;

                foreach (var item in candidates)
                {
                    var education = await _context.Educations
                                            .AsQueryable()
                                            .OrderBy(x => x.EducationEndDate)
                                            .Where(x => x.Candidate.Id == item.Id)
                                            .FirstOrDefaultAsync();

                    summarySheet.Cells[row, 1].Value = num;
                    summarySheet.Cells[row, 2].Value = item.PositionName + item.PositionLevelName;
                    summarySheet.Cells[row, 3].Value = item.FullName;
                    summarySheet.Cells[row, 4].Value = item.Nationality;
                    summarySheet.Cells[row, 5].Value = item.ReligionName;
                    summarySheet.Cells[row, 6].Value = item.DateOfBirth;
                    summarySheet.Cells[row, 7].Value = item.Age;
                    summarySheet.Cells[row, 8].Value = item.Telephone;
                    summarySheet.Cells[row, 9].Value = item.Email;
                    summarySheet.Cells[row, 10].Value = item.CitizenId;

                    summarySheet.Cells[row, 11].Value = education == null ? "-" : education.EducationLevelExamName;
                    summarySheet.Cells[row, 12].Value = education == null ? "-" : education.EducationName;
                    summarySheet.Cells[row, 13].Value = education == null ? "-" : education.EducationMajor;
                    summarySheet.Cells[row, 14].Value = education == null ? "-" : education.EducationLocation;
                    summarySheet.Cells[row, 15].Value = education == null ? "-" : (education.EducationEndDate == null ? "-" : education.EducationEndDate.Value.Date.ToThaiShortDate());
                    summarySheet.Cells[row, 16].Value = education == null ? "-" : education.EducationScores;
                    summarySheet.Cells[row, 17].Value = education == null ? "-" : education.EducationType;
                    summarySheet.Cells[row, 18].Value = education == null ? "-" : education.EducationLevelHighName;

                    summarySheet.Cells[row, 19].Value = item.OccupationPositionType == null ? null : GenerateStatusOccupation(item.OccupationPositionType);
                    summarySheet.Cells[row, 20].Value = item.OccupationPosition;
                    summarySheet.Cells[row, 21].Value = item.OccupationSalary;
                    summarySheet.Cells[row, 22].Value = item.OccupationGroup;
                    summarySheet.Cells[row, 23].Value = item.OccupationPile;
                    summarySheet.Cells[row, 24].Value = item.OccupationOrg;
                    summarySheet.Cells[row, 25].Value = item.OccupationTelephone;

                    summarySheet.Cells[row, 32].Value = item.CurrentAddress;
                    summarySheet.Cells[row, 33].Value = item.CurrentProvinceName;
                    summarySheet.Cells[row, 34].Value = item.CurrentDistrictName;
                    summarySheet.Cells[row, 35].Value = item.CurrentSubDistrictName;
                    summarySheet.Cells[row, 36].Value = item.CurrentZipCode;

                    summarySheet.Cells[row, 37].Value = item.RegistAddress;
                    summarySheet.Cells[row, 38].Value = item.RegistProvinceName;
                    summarySheet.Cells[row, 39].Value = item.RegistDistrictName;
                    summarySheet.Cells[row, 40].Value = item.RegistSubDistrictName;
                    summarySheet.Cells[row, 41].Value = item.RegistZipCode;

                    summarySheet.Cells[row, 42].Value = item.ContactFullName;
                    summarySheet.Cells[row, 43].Value = item.ContactRelations;
                    summarySheet.Cells[row, 44].Value = item.ContactTel;
                    summarySheet.Cells[row, 45].Value = GenerateStatusCandidate(item.Status);

                    summarySheet.Cells[row, 46].Value = item.Number;
                    summarySheet.Cells[row, 47].Value = item.ExamIdenNumber;
                    summarySheet.Cells[row, 48].Value = item.SeatNumber;
                    summarySheet.Cells[row, 49].Value = item.PointTotalA;
                    summarySheet.Cells[row, 50].Value = item.PointA;
                    summarySheet.Cells[row, 51].Value = item.ResultA;
                    summarySheet.Cells[row, 52].Value = item.PointTotalB;
                    summarySheet.Cells[row, 53].Value = item.PointB;
                    summarySheet.Cells[row, 54].Value = item.ResultB;
                    summarySheet.Cells[row, 55].Value = item.PointTotalC;
                    summarySheet.Cells[row, 56].Value = item.PointC;
                    summarySheet.Cells[row, 57].Value = item.ResultC;
                    summarySheet.Cells[row, 58].Value = item.Pass;
                    summarySheet.Cells[row, 59].Value = item.CreatedAt.Date.ToThaiShortDate();
                    summarySheet.Cells[row, 60].Value = item.RegisterDate == null ? "-" : $"{item.RegisterDate.Value.ToThaiShortDate()} {item.RegisterDate.Value.ToString("HH:mm:ss.ffff")}";//xxxxxxxxxx

                    var careers = await _context.Careers
                                            .AsQueryable()
                                            .OrderBy(x => x.DurationStart)
                                            .Where(x => x.Candidate.Id == item.Id)
                                            .ToListAsync();

                    var yearDiff = 0;
                    var monthDiff = 0;
                    var dayDiff = 0;
                    var sb = new StringBuilder();
                    foreach (var career in careers)
                    {
                        if (career.DurationEnd < career.DurationStart)
                        {
                            var rangeObj = CalculateBetweenDateV2Value(career.DurationEnd, career.DurationStart);
                            yearDiff = yearDiff + rangeObj.years;
                            monthDiff = monthDiff + rangeObj.months;
                            dayDiff = dayDiff + rangeObj.days;
                        }
                        else
                        {
                            var rangeObj = CalculateBetweenDateV2Value(career.DurationStart, career.DurationEnd);
                            yearDiff = yearDiff + rangeObj.years;
                            monthDiff = monthDiff + rangeObj.months;
                            dayDiff = dayDiff + rangeObj.days;
                        }
                        if (dayDiff >= 30)
                        {
                            monthDiff = monthDiff + (int)(dayDiff / 30);
                            dayDiff = dayDiff % 30;
                        }
                        if (monthDiff >= 12)
                        {
                            yearDiff = yearDiff + (int)(monthDiff / 12);
                            monthDiff = monthDiff % 12;
                        }
                        sb.Clear();
                        sb.Append(yearDiff == 0 ? "" : $"{yearDiff} ปี ");
                        sb.Append(monthDiff == 0 ? "" : $"{monthDiff} เดือน ");
                        sb.Append(dayDiff == 0 ? "" : $"{dayDiff} วัน ");
                    }
                    var _career = false;
                    foreach (var career in careers)
                    {
                        summarySheet.Cells[row, 26].Value = career.Position;
                        summarySheet.Cells[row, 27].Value = career.Type;
                        summarySheet.Cells[row, 28].Value = career.DurationStart == null ? "-" : career.DurationStart.Date.ToThaiShortDate();
                        summarySheet.Cells[row, 29].Value = career.DurationEnd == null ? "-" : career.DurationEnd.Date.ToThaiShortDate();
                        summarySheet.Cells[row, 30].Value = career.RangeDate;
                        if (_career == false)
                        {
                            summarySheet.Cells[row, 31].Value = sb.ToString();
                            _career = true;
                        }
                        row++;
                    }
                    if (careers.Count == 0)
                        row++;
                    num = num + 1;
                }
                summarySheet.Cells[summarySheet.Dimension.Address].AutoFitColumns();
                package.Save();
            }

            stream.Position = 0;
            return stream;
        }

        public async Task<dynamic> NewDownloadCandidateAllAsync(string examId)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .Where(x => x.CheckDisability == false)
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

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

            var candidates = await _context.Candidates
                                    .AsQueryable()
                                    .OrderBy(x => x.ExamIdenNumber)
                                    .Where(x => x.PeriodExam == periodExam)
                                    .Where(x => x.Status != "register")
                                    .Select(c => new
                                    {
                                        Id = c.Id,
                                        Status = c.Status,
                                        CitizenId = c.CitizenId,
                                        FullName = $"{c.PrefixName}{c.FirstName} {c.LastName}",
                                        Nationality = c.Nationality,
                                        DateOfBirth = c.DateOfBirth == null ? "" : c.DateOfBirth.Value.Date.ToThaiShortDate(),
                                        Age = c.DateOfBirth == null ? "" : c.DateOfBirth.Value.Date.CalculateAgeStrV2(0, 0),
                                        ReligionName = c.ReligionName,
                                        Telephone = c.Telephone,
                                        MobilePhone = c.MobilePhone,
                                        Email = c.Email,
                                        Knowledge = c.Knowledge,
                                        RegistAddress = c.RegistAddress,
                                        RegistProvinceName = c.RegistProvinceName,
                                        RegistDistrictName = c.RegistDistrictName,
                                        RegistSubDistrictName = c.RegistSubDistrictName,
                                        RegistZipCode = c.RegistZipCode,
                                        CurrentAddress = c.CurrentAddress,
                                        CurrentProvinceName = c.CurrentProvinceName,
                                        CurrentDistrictName = c.CurrentDistrictName,
                                        CurrentSubDistrictName = c.CurrentSubDistrictName,
                                        CurrentZipCode = c.CurrentZipCode,
                                        OccupationOrg = c.OccupationOrg,
                                        OccupationPile = c.OccupationPile,
                                        OccupationGroup = c.OccupationGroup,
                                        OccupationSalary = c.OccupationSalary,
                                        OccupationPosition = c.OccupationPosition,
                                        OccupationPositionType = c.OccupationPositionType,
                                        OccupationTelephone = c.OccupationTelephone,
                                        RegisterDate = c.RegisterDate,
                                        PositionName = c.PositionExam.PositionName,
                                        PositionLevelName = c.PositionExam.PositionLevelName,

                                        ContactFullName = $"{c.ContactPrefixName}{c.ContactFirstname} {c.ContactLastname}",
                                        ContactRelations = c.ContactRelations,
                                        ContactTel = c.ContactTel,

                                        Number = c.Number,
                                        ExamIdenNumber = c.ExamIdenNumber,
                                        SeatNumber = c.SeatNumber,
                                        PointTotalA = c.PointTotalA,
                                        PointPath1A = c.PointPath1A,
                                        PointPath2A = c.PointPath2A,
                                        PointPath3A = c.PointPath3A,
                                        PointA = c.PointA,
                                        PointPerA = c.PointPerA,
                                        ResultA = c.ResultA,
                                        PointTotalB = c.PointTotalB,
                                        PointB = c.PointB,
                                        PointPerB = c.PointPerB,
                                        ResultB = c.ResultB,
                                        PointTotalC = c.PointTotalC,
                                        PointPath1C = c.PointPath1C,
                                        PointPath2C = c.PointPath2C,
                                        PointC = c.PointC,
                                        PointPerC = c.PointPerC,
                                        ResultC = c.ResultC,
                                        Pass = c.Pass,
                                        ExamReason = c.ExamReason,
                                        CreatedAt = c.CreatedAt,
                                    })
                                    .ToListAsync();

            var _data = new List<object>();
            if (candidates.Count > 0)
            {
                int num = 1;
                foreach (var item in candidates)
                {
                    var education = await _context.Educations
                                            .AsQueryable()
                                            .OrderBy(x => x.EducationEndDate)
                                            .Where(x => x.Candidate.Id == item.Id)
                                            .FirstOrDefaultAsync();

                    var careers = await _context.Careers
                                            .AsQueryable()
                                            .OrderBy(x => x.DurationStart)
                                            .Where(x => x.Candidate.Id == item.Id)
                                            .ToListAsync();
                    if (careers.Any())
                    {
                        var firstCareer = careers.First();

                        _data.Add(new
                        {
                            Num = num.ToString(),
                            PositionName = item.PositionName != null && item.PositionLevelName != null ? $"{item.PositionName} {item.PositionLevelName}" : "-",
                            FullName = item.FullName != null ? item.FullName: "-",
                            Nationality = item.Nationality != null ? item.Nationality : "-",
                            ReligionName = item.ReligionName != null ? item.ReligionName : "-",
                            DateOfBirth = item.DateOfBirth != null ? item.DateOfBirth : "-",
                            Age = item.Age != null ? item.Age : "-",
                            Telephone = item.Telephone != null ? item.Telephone : "-",
                            Email = item.Email != null ? item.Email : "-",
                            CitizenId = item.CitizenId != null ? item.CitizenId : "-",

                            EducationLevelExamName = education.EducationLevelExamName == null ? "-" : education.EducationLevelExamName,
                            EducationName = education.EducationName == null ? "-" : education.EducationName,
                            EducationMajor = education.EducationMajor == null ? "-" : education.EducationMajor,
                            EducationLocation = education.EducationLocation == null ? "-" : education.EducationLocation,
                            EducationEndDate = education.EducationEndDate == null ? "-" : (education.EducationEndDate == null ? "-" : education.EducationEndDate.Value.Date.ToThaiShortDate()),
                            EducationScores = education.EducationScores == null ? "-" : education.EducationScores,
                            EducationType = education.EducationType == null ? "-" : education.EducationType,
                            EducationLevelHighName = education.EducationLevelHighName == null ? "-" : education.EducationLevelHighName,

                            OccupationPositionType = item.OccupationPositionType == null ? "-" : GenerateStatusOccupation(item.OccupationPositionType),
                            OccupationPosition = item.OccupationPosition == null ? "-" : item.OccupationPosition,
                            OccupationSalary = item.OccupationSalary == null ? "-" : item.OccupationSalary.ToString(),
                            OccupationGroup = item.OccupationGroup == null ? "-" : item.OccupationGroup,
                            OccupationPile = item.OccupationPile == null ? "-" : item.OccupationPile,
                            OccupationOrg = item.OccupationOrg == null ? "-" : item.OccupationOrg,
                            OccupationTelephone = item.OccupationTelephone == null ? "-" : item.OccupationTelephone,

                            Position = firstCareer.Position == null ? "-" : firstCareer.Position,
                            Type = firstCareer.Type == null ? "-" : firstCareer.Type,
                            DurationStart = firstCareer.DurationStart == null ? "-" : firstCareer.DurationStart.Date.ToThaiShortDate(),
                            DurationEnd = firstCareer.DurationEnd == null ? "-" : firstCareer.DurationEnd.Date.ToThaiShortDate(),
                            RangeDate = firstCareer.RangeDate == null ? "-" : firstCareer.RangeDate,

                            CurrentAddress = item.CurrentAddress == null ? "-" : item.CurrentAddress,
                            CurrentProvinceName = item.CurrentProvinceName == null ? "-" : item.CurrentProvinceName,
                            CurrentDistrictName = item.CurrentDistrictName == null ? "-" : item.CurrentDistrictName,
                            CurrentSubDistrictName = item.CurrentSubDistrictName == null ? "-" : item.CurrentSubDistrictName,
                            CurrentZipCode = item.CurrentZipCode == null ? "-" : item.CurrentZipCode,

                            RegistAddress = item.RegistAddress == null ? "-" : item.RegistAddress,
                            RegistProvinceName = item.RegistProvinceName == null ? "-" : item.RegistProvinceName,
                            RegistDistrictName = item.RegistDistrictName == null ? "-" : item.RegistDistrictName,
                            RegistSubDistrictName = item.RegistSubDistrictName == null ? "-" : item.RegistSubDistrictName,
                            RegistZipCode = item.RegistZipCode == null ? "-" : item.RegistZipCode,

                            ContactFullName = item.ContactFullName == null ? "-" : item.ContactFullName,
                            ContactRelations = item.ContactRelations == null ? "-" : item.ContactRelations,
                            ContactTel = item.ContactTel == null ? "-" : item.ContactTel,
                            CandidateStatus = GenerateStatusCandidate(item.Status),

                            Number = item.Number == null ? "-" : item.Number,
                            ExamIdenNumber = item.ExamIdenNumber == null ? "-" : item.ExamIdenNumber,
                            SeatNumber = item.SeatNumber == null ? "-" : item.SeatNumber,
                            PointTotalA = item.PointTotalA == null ? "-" : item.PointTotalA,
                            PointA = item.PointA == null ? "-" : item.PointA,
                            ResultA = item.ResultA == null ? "-" : item.ResultA,
                            PointTotalB = item.PointTotalB == null ? "-" : item.PointTotalB,
                            PointB = item.PointB == null ? "-" : item.PointB,
                            ResultB = item.ResultB == null ? "-" : item.ResultB,
                            PointTotalC = item.PointTotalC == null ? "-" : item.PointTotalC,
                            PointC = item.PointC == null ? "-" : item.PointC,
                            ResultC = item.ResultC == null ? "-" : item.ResultC,
                            Pass = item.Pass == null ? "-" : item.Pass,
                            CreatedAt = item.CreatedAt.Date == null ? "-" : item.CreatedAt.Date.ToThaiShortDate(),
                            RegisterDate = item.RegisterDate.Value.Date == null ? "-" : $"{item.RegisterDate.Value.Date.ToThaiShortDate()} {item.RegisterDate.Value.ToString("HH:mm:ss.ffff")}"
                        });

                        if (careers.Count > 1)
                        {
                            foreach (var career in careers.Skip(1))
                            {
                                _data.Add(new
                                {
                                    Num = "",
                                    PositionName = "",
                                    FullName = "",
                                    Nationality = "",
                                    ReligionName = "",
                                    DateOfBirth = "",
                                    Age = "",
                                    Telephone = "",
                                    Email = "",
                                    CitizenId = "",

                                    EducationLevelExamName = "",
                                    EducationName = "",
                                    EducationMajor = "",
                                    EducationLocation = "",
                                    EducationEndDate = "",
                                    EducationScores = "",
                                    EducationType = "",
                                    EducationLevelHighName = "",

                                    OccupationPositionType = "",
                                    OccupationPosition = "",
                                    OccupationSalary = "",
                                    OccupationGroup = "",
                                    OccupationPile = "",
                                    OccupationOrg = "",
                                    OccupationTelephone = "",

                                    Position = career.Position != null ? career.Position : "-",
                                    Type = career.Type != null ? career.Type : "-",
                                    DurationStart = career.DurationStart != null ? career.DurationStart.Date.ToThaiShortDate() : "-",
                                    DurationEnd = career.DurationEnd != null ? career.DurationEnd.Date.ToThaiShortDate() : "-",
                                    RangeDate = career.RangeDate != null ? career.RangeDate : "-",

                                    CurrentAddress = "",
                                    CurrentProvinceName = "",
                                    CurrentDistrictName = "",
                                    CurrentSubDistrictName = "",
                                    CurrentZipCode = "",

                                    RegistAddress = "",
                                    RegistProvinceName = "",
                                    RegistDistrictName = "",
                                    RegistSubDistrictName = "",
                                    RegistZipCode = "",

                                    ContactFullName = "",
                                    ContactRelations = "",
                                    ContactTel = "",
                                    CandidateStatus = "",

                                    Number = "",
                                    ExamIdenNumber = "",
                                    SeatNumber = "",
                                    PointTotalA = "",
                                    PointA = "",
                                    ResultA = "",
                                    PointTotalB = "",
                                    PointB = "",
                                    ResultB = "",
                                    PointTotalC = "",
                                    PointC = "",
                                    ResultC = "",
                                    Pass = "",
                                    CreatedAt = "",
                                    RegisterDate = ""
                                });
                            }
                        }
                    }
                    else
                    {
                        _data.Add(new
                        {
                            Num = num.ToString(),
                            PositionName = item.PositionName != null && item.PositionLevelName != null ? $"{item.PositionName} {item.PositionLevelName}" : "-",
                            FullName = item.FullName != null ? item.FullName : "-",
                            Nationality = item.Nationality != null ? item.Nationality : "-",
                            ReligionName = item.ReligionName != null ? item.ReligionName : "-",
                            DateOfBirth = item.DateOfBirth != null ? item.DateOfBirth : "-",
                            Age = item.Age != null ? item.Age : "-",
                            Telephone = item.Telephone != null ? item.Telephone : "-",
                            Email = item.Email != null ? item.Email : "-",
                            CitizenId = item.CitizenId != null ? item.CitizenId : "-",

                            EducationLevelExamName = education.EducationLevelExamName == null ? "-" : education.EducationLevelExamName,
                            EducationName = education.EducationName == null ? "-" : education.EducationName,
                            EducationMajor = education.EducationMajor == null ? "-" : education.EducationMajor,
                            EducationLocation = education.EducationLocation == null ? "-" : education.EducationLocation,
                            EducationEndDate = education.EducationEndDate == null ? "-" : (education.EducationEndDate == null ? "-" : education.EducationEndDate.Value.Date.ToThaiShortDate()),
                            EducationScores = education.EducationScores == null ? "-" : education.EducationScores,
                            EducationType = education.EducationType == null ? "-" : education.EducationType,
                            EducationLevelHighName = education.EducationLevelHighName == null ? "-" : education.EducationLevelHighName,

                            OccupationPositionType = item.OccupationPositionType == null ? "-" : GenerateStatusOccupation(item.OccupationPositionType),
                            OccupationPosition = item.OccupationPosition == null ? "-" : item.OccupationPosition,
                            OccupationSalary = item.OccupationSalary == null ? "-" : item.OccupationSalary.ToString(),
                            OccupationGroup = item.OccupationGroup == null ? "-" : item.OccupationGroup,
                            OccupationPile = item.OccupationPile == null ? "-" : item.OccupationPile,
                            OccupationOrg = item.OccupationOrg == null ? "-" : item.OccupationOrg,
                            OccupationTelephone = item.OccupationTelephone == null ? "-" : item.OccupationTelephone,

                            Position = "-",
                            Type = "-",
                            DurationStart = "-",
                            DurationEnd = "-",
                            RangeDate = "-",

                            CurrentAddress = item.CurrentAddress == null ? "-" : item.CurrentAddress,
                            CurrentProvinceName = item.CurrentProvinceName == null ? "-" : item.CurrentProvinceName,
                            CurrentDistrictName = item.CurrentDistrictName == null ? "-" : item.CurrentDistrictName,
                            CurrentSubDistrictName = item.CurrentSubDistrictName == null ? "-" : item.CurrentSubDistrictName,
                            CurrentZipCode = item.CurrentZipCode == null ? "-" : item.CurrentZipCode,

                            RegistAddress = item.RegistAddress == null ? "-" : item.RegistAddress,
                            RegistProvinceName = item.RegistProvinceName == null ? "-" : item.RegistProvinceName,
                            RegistDistrictName = item.RegistDistrictName == null ? "-" : item.RegistDistrictName,
                            RegistSubDistrictName = item.RegistSubDistrictName == null ? "-" : item.RegistSubDistrictName,
                            RegistZipCode = item.RegistZipCode == null ? "-" : item.RegistZipCode,

                            ContactFullName = item.ContactFullName == null ? "-" : item.ContactFullName,
                            ContactRelations = item.ContactRelations == null ? "-" : item.ContactRelations,
                            ContactTel = item.ContactTel == null ? "-" : item.ContactTel,
                            CandidateStatus = GenerateStatusCandidate(item.Status),

                            Number = item.Number == null ? "-" : item.Number,
                            ExamIdenNumber = item.ExamIdenNumber == null ? "-" : item.ExamIdenNumber,
                            SeatNumber = item.SeatNumber == null ? "-" : item.SeatNumber,
                            PointTotalA = item.PointTotalA == null ? "-" : item.PointTotalA,
                            PointA = item.PointA == null ? "-" : item.PointA,
                            ResultA = item.ResultA == null ? "-" : item.ResultA,
                            PointTotalB = item.PointTotalB == null ? "-" : item.PointTotalB,
                            PointB = item.PointB == null ? "-" : item.PointB,
                            ResultB = item.ResultB == null ? "-" : item.ResultB,
                            PointTotalC = item.PointTotalC == null ? "-" : item.PointTotalC,
                            PointC = item.PointC == null ? "-" : item.PointC,
                            ResultC = item.ResultC == null ? "-" : item.ResultC,
                            Pass = item.Pass == null ? "-" : item.Pass,
                            CreatedAt = item.CreatedAt.Date == null ? "-" : item.CreatedAt.Date.ToThaiShortDate(),
                            RegisterDate = item.RegisterDate.Value.Date == null ? "-" : $"{item.RegisterDate.Value.Date.ToThaiShortDate()} {item.RegisterDate.Value.ToString("HH:mm:ss.ffff")}"
                        });
                    }

                    num = num + 1;
                }
            }
            return new
            {
                template = "candidateDetail",
                reportName = $"CandidateDetail_{DateTime.Now.ToString("yyyyMMddHHmmssfff")}",
                data = new
                {
                    data = _data
                }
            };
        }

        public async Task<MemoryStream> GetsDashboardExamAsync(RequestCandidateDashboard item, string examId)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

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

            var candidates = await _context.Candidates
                                    .AsQueryable()
                                    .OrderBy(x => x.ExamIdenNumber)
                                    // .Where(x => x.CreatedAt.Date <= item.DateEnd.Date)
                                    // .Where(x => x.CreatedAt.Date >= item.DateStart.Date)
                                    .Where(x => x.PeriodExam == periodExam)
                                    .Where(x => x.Status != "register")
                                    .Select(c => new
                                    {
                                        Id = c.Id,
                                        Status = c.Status,
                                        CitizenId = c.CitizenId,
                                        FullName = $"{c.PrefixName}{c.FirstName} {c.LastName}",
                                        Nationality = c.Nationality,
                                        DateOfBirth = c.DateOfBirth == null ? "" : c.DateOfBirth.Value.Date.ToThaiShortDate(),
                                        Age = c.DateOfBirth == null ? 0 : DateTime.Now.Date.Year - c.DateOfBirth.Value.Date.Year,
                                        ReligionName = c.ReligionName,
                                        Telephone = c.Telephone,
                                        MobilePhone = c.MobilePhone,
                                        Email = c.Email,
                                        Knowledge = c.Knowledge,
                                        RegistAddress = c.RegistAddress,
                                        RegistProvinceName = c.RegistProvinceName,
                                        RegistDistrictName = c.RegistDistrictName,
                                        RegistSubDistrictName = c.RegistSubDistrictName,
                                        RegistZipCode = c.RegistZipCode,
                                        CurrentAddress = c.CurrentAddress,
                                        CurrentProvinceName = c.CurrentProvinceName,
                                        CurrentDistrictName = c.CurrentDistrictName,
                                        CurrentSubDistrictName = c.CurrentSubDistrictName,
                                        CurrentZipCode = c.CurrentZipCode,
                                        OccupationOrg = c.OccupationOrg,
                                        OccupationPile = c.OccupationPile,
                                        OccupationGroup = c.OccupationGroup,
                                        OccupationSalary = c.OccupationSalary,
                                        OccupationPosition = c.OccupationPosition,
                                        OccupationPositionType = c.OccupationPositionType,
                                        OccupationTelephone = c.OccupationTelephone,
                                        Number = c.Number,
                                        ExamIdenNumber = c.ExamIdenNumber,
                                        SeatNumber = c.SeatNumber,
                                        PointTotalA = c.PointTotalA,
                                        PointPath1A = c.PointPath1A,
                                        PointPath2A = c.PointPath2A,
                                        PointPath3A = c.PointPath3A,
                                        PointA = c.PointA,
                                        PointPerA = c.PointPerA,
                                        ResultA = c.ResultA,
                                        PointTotalB = c.PointTotalB,
                                        PointB = c.PointB,
                                        PointPerB = c.PointPerB,
                                        ResultB = c.ResultB,
                                        PointTotalC = c.PointTotalC,
                                        PointPath1C = c.PointPath1C,
                                        PointPath2C = c.PointPath2C,
                                        PointC = c.PointC,
                                        PointPerC = c.PointPerC,
                                        ResultC = c.ResultC,
                                        Pass = c.Pass,
                                        ExamReason = c.ExamReason,
                                        CreatedAt = c.CreatedAt,
                                    })
                                    .ToListAsync();

            var stream = new MemoryStream();
            using (var package = new ExcelPackage(stream))
            {
                var summarySheet = package.Workbook.Worksheets.Add("Dashboard");
                summarySheet.TabColor = System.Drawing.Color.Black;
                // summarySheet.DefaultRowHeight = 17;
                summarySheet.Column(1).Style.Font.Bold = true;
                summarySheet.Row(4).Style.Font.Bold = true;
                summarySheet.Row(4).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                summarySheet.Row(5).Style.Font.Bold = true;
                summarySheet.Cells[1, 3].Style.Font.Bold = true;
                int rowName = 2;
                int rowCount = 2;
                int fixMerge = 2;
                // summarySheet.Cells[1, 1].Value = "ตั้งแต่";
                // summarySheet.Cells[1, 2].Value = item.DateStart.Date.ToThaiShortDate();
                // summarySheet.Cells[1, 3].Value = "ถึง";
                // summarySheet.Cells[1, 4].Value = item.DateEnd.Date.ToThaiShortDate();
                summarySheet.Cells[1, 1].Value = "ชื่อรอบการสอบ/ชื่อประกาศ";
                summarySheet.Cells[1, 2].Value = periodExam.Name;
                summarySheet.Cells[2, 1].Value = "ครั้งที่";
                summarySheet.Cells[2, 2].Value = $"{periodExam.Round}/{periodExam.Year + 543}";
                summarySheet.Cells[3, 1].Value = "จำนวนผู้สมัคร";
                summarySheet.Cells[3, 2].Value = candidates.Count().ToString();
                if (candidates.Count() > 0)
                {
                    summarySheet.Cells[6, 1].Value = "จำนวน";
                    // summarySheet.Cells[1, 6].Value = "คะแนนภาค ข";
                    // summarySheet.Cells[1, 7].Value = "ผลสอบภาค ข";
                    // summarySheet.Cells[1, 8].Value = "คะแนนเต็มภาค ค";
                    // summarySheet.Cells[1, 9].Value = "คะแนนภาค ค";
                    // summarySheet.Cells[1, 10].Value = "ผลสอบภาค ค";
                    // summarySheet.Cells[1, 11].Value = "ผลการสอบ";
                    var educations = await _context.Educations
                                            .AsQueryable()
                                            .Where(x => x.Candidate.PeriodExam == periodExam)
                                            .Where(x => x.Candidate.Status != "register")
                                            // .Where(x => x.Candidate.CreatedAt.Date <= item.DateEnd.Date)
                                            // .Where(x => x.Candidate.CreatedAt.Date >= item.DateStart.Date)
                                            .GroupBy(x => x.EducationLevelExamName)
                                            .Select(x => new
                                            {
                                                Name = x.Key,
                                                Count = x.Count(),
                                            })
                                            .ToListAsync();

                    foreach (var education in educations)
                    {
                        summarySheet.Cells[5, rowName].Value = education.Name;
                        summarySheet.Cells[6, rowCount].Value = education.Count;
                        rowName++;
                        rowCount++;
                    }
                    if (educations.Count() != 0)
                    {
                        summarySheet.Cells[4, fixMerge, 4, rowName - 1].Value = "วุฒิที่ใช้สมัครสอบ";
                        summarySheet.Cells[4, fixMerge, 4, rowName - 1].Merge = true;
                        fixMerge = rowCount;
                    }
                    var careers = await _context.Careers
                                            .AsQueryable()
                                            .Where(x => x.Candidate.PeriodExam == periodExam)
                                            .Where(x => x.Candidate.Status != "register")
                                            // .Where(x => x.Candidate.CreatedAt.Date <= item.DateEnd.Date)
                                            // .Where(x => x.Candidate.CreatedAt.Date >= item.DateStart.Date)
                                            .GroupBy(x => x.Position)
                                            .Select(x => new
                                            {
                                                Name = x.Key,
                                                Count = x.Count(),
                                            })
                                            .ToListAsync();
                    foreach (var career in careers)
                    {
                        summarySheet.Cells[5, rowName].Value = career.Name;
                        summarySheet.Cells[6, rowCount].Value = career.Count;
                        rowName++;
                        rowCount++;
                    }
                    if (careers.Count() != 0)
                    {
                        summarySheet.Cells[4, fixMerge, 4, rowName - 1].Value = "สถานที่ทำงาน/ฝึกงาน";
                        summarySheet.Cells[4, fixMerge, 4, rowName - 1].Merge = true;
                        fixMerge = rowCount;
                    }
                    summarySheet.Cells[5, rowName].Value = "อายุ 1-20 ปี";
                    summarySheet.Cells[6, rowCount].Value = candidates.Where(x => x.Age >= 1 && x.Age <= 20).Count();
                    rowName++;
                    rowCount++;
                    summarySheet.Cells[5, rowName].Value = "อายุ 21-45 ปี";
                    summarySheet.Cells[6, rowCount].Value = candidates.Where(x => x.Age >= 21 && x.Age <= 45).Count();
                    rowName++;
                    rowCount++;
                    summarySheet.Cells[5, rowName].Value = "อายุ 46-60 ปี";
                    summarySheet.Cells[6, rowCount].Value = candidates.Where(x => x.Age >= 46 && x.Age <= 60).Count();
                    rowName++;
                    rowCount++;
                    summarySheet.Cells[5, rowName].Value = "อายุ 61-70 ปี";
                    summarySheet.Cells[6, rowCount].Value = candidates.Where(x => x.Age >= 61 && x.Age <= 70).Count();
                    rowName++;
                    rowCount++;
                    summarySheet.Cells[5, rowName].Value = "อายุ 71-80 ปี";
                    summarySheet.Cells[6, rowCount].Value = candidates.Where(x => x.Age >= 71 && x.Age <= 80).Count();
                    rowName++;
                    rowCount++;
                    summarySheet.Cells[5, rowName].Value = "อายุ 80 ปีขึ้นไป";
                    summarySheet.Cells[6, rowCount].Value = candidates.Where(x => x.Age >= 81).Count();
                    rowName++;
                    rowCount++;
                    summarySheet.Cells[4, fixMerge, 4, rowName - 1].Value = "ช่วงอายุ";
                    summarySheet.Cells[4, fixMerge, 4, rowName - 1].Merge = true;
                }
                else
                {
                    summarySheet.Cells[6, 1].Value = "ไม่มีผู้สมัครสอบในช่วงเวลานี้";
                }
                summarySheet.Cells[summarySheet.Dimension.Address].AutoFitColumns();
                package.Save();
            }

            stream.Position = 0;
            return stream;
        }

        public async Task<dynamic> NewGetsDashboardExamAsync(RequestCandidateDashboard item, string examId)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

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

            var candidates = await _context.Candidates
                                    .AsQueryable()
                                    .OrderBy(x => x.ExamIdenNumber)
                                    .Where(x => x.PeriodExam == periodExam)
                                    .Where(x => x.Status != "register")
                                    .Select(c => new
                                    {
                                        Id = c.Id,
                                        Status = c.Status,
                                        CitizenId = c.CitizenId,
                                        FullName = $"{c.PrefixName}{c.FirstName} {c.LastName}",
                                        Nationality = c.Nationality,
                                        DateOfBirth = c.DateOfBirth == null ? "" : c.DateOfBirth.Value.Date.ToThaiShortDate(),
                                        Age = c.DateOfBirth == null ? 0 : DateTime.Now.Date.Year - c.DateOfBirth.Value.Date.Year,
                                        ReligionName = c.ReligionName,
                                        Telephone = c.Telephone,
                                        MobilePhone = c.MobilePhone,
                                        Email = c.Email,
                                        Knowledge = c.Knowledge,
                                        RegistAddress = c.RegistAddress,
                                        RegistProvinceName = c.RegistProvinceName,
                                        RegistDistrictName = c.RegistDistrictName,
                                        RegistSubDistrictName = c.RegistSubDistrictName,
                                        RegistZipCode = c.RegistZipCode,
                                        CurrentAddress = c.CurrentAddress,
                                        CurrentProvinceName = c.CurrentProvinceName,
                                        CurrentDistrictName = c.CurrentDistrictName,
                                        CurrentSubDistrictName = c.CurrentSubDistrictName,
                                        CurrentZipCode = c.CurrentZipCode,
                                        OccupationOrg = c.OccupationOrg,
                                        OccupationPile = c.OccupationPile,
                                        OccupationGroup = c.OccupationGroup,
                                        OccupationSalary = c.OccupationSalary,
                                        OccupationPosition = c.OccupationPosition,
                                        OccupationPositionType = c.OccupationPositionType,
                                        OccupationTelephone = c.OccupationTelephone,
                                        Number = c.Number,
                                        ExamIdenNumber = c.ExamIdenNumber,
                                        SeatNumber = c.SeatNumber,
                                        PointTotalA = c.PointTotalA,
                                        PointPath1A = c.PointPath1A,
                                        PointPath2A = c.PointPath2A,
                                        PointPath3A = c.PointPath3A,
                                        PointA = c.PointA,
                                        PointPerA = c.PointPerA,
                                        ResultA = c.ResultA,
                                        PointTotalB = c.PointTotalB,
                                        PointB = c.PointB,
                                        PointPerB = c.PointPerB,
                                        ResultB = c.ResultB,
                                        PointTotalC = c.PointTotalC,
                                        PointPath1C = c.PointPath1C,
                                        PointPath2C = c.PointPath2C,
                                        PointC = c.PointC,
                                        PointPerC = c.PointPerC,
                                        ResultC = c.ResultC,
                                        Pass = c.Pass,
                                        ExamReason = c.ExamReason,
                                        CreatedAt = c.CreatedAt,
                                    })
                                    .ToListAsync();

            var educations = new List<object>();
            var careers = new List<object>();
            var ages = new
            {
                one = "๐",
                two = "๐",
                three = "๐",
                four = "๐",
                five = "๐",
                six = "๐",
            };
            var educationsCount = 0;
            var careersCount = 0;
            if (candidates.Count > 0)
            {
                educations = await _context.Educations
                    .AsQueryable()
                    .Where(x => x.Candidate.PeriodExam == periodExam)
                    .Where(x => x.Candidate.Status != "register")
                    .GroupBy(x => x.EducationLevelExamName)
                    .Select(x => (object)new
                    {
                        Name = x.Key,
                        ToTal = x.Count(),
                        Count = x.Count().ToString().ToThaiNumber(),
                    })
                    .ToListAsync();

                educationsCount = educations.Sum(x => ((dynamic)x).ToTal);

                careers = await _context.Careers
                    .AsQueryable()
                    .Where(x => x.Candidate.PeriodExam == periodExam)
                    .Where(x => x.Candidate.Status != "register")
                    .GroupBy(x => x.Position)
                    .Select(x => (object)new
                    {
                        Name = x.Key,
                        Total = x.Count(),
                        Count = x.Count().ToString().ToThaiNumber(),
                    })
                    .ToListAsync();

                careersCount = careers.Sum(x => ((dynamic)x).Total);

                ages = new
                {
                    one = candidates.Where(x => x.Age >= 1 && x.Age <= 20).Count().ToString().ToThaiNumber(),
                    two = candidates.Where(x => x.Age >= 21 && x.Age <= 45).Count().ToString().ToThaiNumber(),
                    three = candidates.Where(x => x.Age >= 46 && x.Age <= 60).Count().ToString().ToThaiNumber(),
                    four = candidates.Where(x => x.Age >= 61 && x.Age <= 70).Count().ToString().ToThaiNumber(),
                    five = candidates.Where(x => x.Age >= 71 && x.Age <= 80).Count().ToString().ToThaiNumber(),
                    six = candidates.Where(x => x.Age >= 81).Count().ToString().ToThaiNumber(),
                };
            }

            return new
            {
                template = "candidateDashboard",
                reportName = $"สรุปข้อลการสมัครสอบ {periodExam.Name} ครั้งที่ {periodExam.Round}/{periodExam.Year.Value.ToThaiYear()}",
                data = new
                {
                    periodExamName = periodExam.Name.ToThaiNumber(),
                    periodExamRound = $"{periodExam.Round}/{periodExam.Year + 543}".ToThaiNumber(),
                    candidatesCount = candidates.Count().ToString().ToThaiNumber(),
                    educations = educations,
                    educationsCount = educationsCount.ToString().ToThaiNumber(),
                    careers = careers,
                    careersCount = careersCount.ToString().ToThaiNumber(),
                    ages = ages
                }
            };
        }

        public async Task<MemoryStream> DownloadCandidatePaymentAllAsync(string examId)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                            .Where(x => x.CheckDisability == false)
                            .FirstOrDefaultAsync(x => x.Id == Guid.Parse(examId));

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

            var candidates = await _context.Candidates
                                    .AsQueryable()
                                    .Where(x => x.PeriodExam == periodExam)
                                    .Where(x => x.Status != "register")
                                    .Select(c => new
                                    {
                                        CitizenId = c.CitizenId,
                                        FullName = $"{c.PrefixName}{c.FirstName} {c.LastName}",
                                        PaymentDate = c.PaymentDate,
                                    })
                                    .ToListAsync();

            var stream = new MemoryStream();
            using (var package = new ExcelPackage(stream))
            {
                var summarySheet = package.Workbook.Worksheets.Add("Payment");
                summarySheet.TabColor = System.Drawing.Color.Black;
                summarySheet.Row(1).Style.Font.Bold = true;
                summarySheet.Cells[1, 1].Value = "เลขบัตรประชาชน";
                summarySheet.Cells[1, 2].Value = "ชื่อ-สกุล";
                summarySheet.Cells[1, 3].Value = "ชำระค่าธรรมเนียม";
                summarySheet.Cells[1, 4].Value = "วันเวลาชำระ";
                int row = 2;

                foreach (var item in candidates)
                {
                    summarySheet.Cells[row, 1].Value = item.CitizenId;
                    summarySheet.Cells[row, 2].Value = item.FullName;
                    summarySheet.Cells[row, 3].Value = item.PaymentDate == null ? "ยังไม่ชำระเงิน" : "ชำระแล้ว";
                    summarySheet.Cells[row, 4].Value = item.PaymentDate == null ? "" : item.PaymentDate.Value;
                    row++;
                }
                summarySheet.Cells[summarySheet.Dimension.Address].AutoFitColumns();
                package.Save();
            }

            stream.Position = 0;
            return stream;
        }

        public async Task UpdateAsyncCandidateToPlacement(Guid examId)
        {
            var periodExam = await _context.PeriodExams.AsQueryable()
                                    .Include(x => x.Candidate)
                                    .ThenInclude(x => x.PositionExam)
                                    .Include(x => x.Candidate)
                                    .ThenInclude(x => x.ProfileImg)
                                    .Include(x => x.Candidate)
                                    .ThenInclude(x => x.Educations)
                                    .FirstOrDefaultAsync(x => x.Id == examId);

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

            var _placement = await _contextMetadata.Placements.AsQueryable()
                                        .FirstOrDefaultAsync(x => x.PlacementType.Name == "สอบคัดเลือก" && x.RefId == periodExam.Id);
            if (_placement != null)
                throw new Exception("รอบการสอบนี้ได้ทำการบรรจุไปแล้ว");

            var placement = new Placement
            {
                Name = periodExam.Name,
                RefId = periodExam.Id,
                Round = periodExam.Round == null ? "" : periodExam.Round.ToString(),
                Year = (int)(periodExam.Year == null ? 0 : periodExam.Year),
                Number = periodExam.Candidate.Where(x => x.Status.Contains("done")).Count(),
                PlacementType = await _contextMetadata.PlacementTypes.FirstOrDefaultAsync(x => x.Name.Trim().ToUpper().Contains("สอบคัดเลือก")) == null ? await _contextMetadata.PlacementTypes.FirstOrDefaultAsync() : await _contextMetadata.PlacementTypes.FirstOrDefaultAsync(x => x.Name.Trim().ToUpper().Contains("สอบคัดเลือก")),
                StartDate = DateTime.Now,
                EndDate = DateTime.Now.AddYears(2).AddDays(-1),
                CreatedAt = DateTime.Now,
                CreatedUserId = UserId ?? "",
                CreatedFullName = FullName ?? "",
                LastUpdatedAt = DateTime.Now,
                LastUpdateUserId = UserId ?? "",
                LastUpdateFullName = FullName ?? "",
            };
            await _contextMetadata.Placements.AddAsync(placement);
            foreach (var candidate in periodExam.Candidate.Where(x => x.Status.Trim().ToUpper() == "DONE" && x.Pass?.Trim() == "ได้"))
            {
                var IsOfficer = false;
                dynamic org = null;
                var apiUrl = $"{_configuration["API"]}/org/profile/citizenid/position/{candidate.CitizenId}";
                using (var client = new HttpClient())
                {
                    client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token.Replace("Bearer ", ""));
                    client.DefaultRequestHeaders.Add("api_key", _configuration["API_KEY"]);
                    var _req = new HttpRequestMessage(HttpMethod.Get, apiUrl);
                    var _res = await client.SendAsync(_req);
                    var _result = await _res.Content.ReadAsStringAsync();

                    org = JsonConvert.DeserializeObject<dynamic>(_result);

                    if (org == null || org.result == null)
                    {
                        IsOfficer = false;
                    }
                    else
                    {
                        IsOfficer = true;
                    }
                }

                var placementProfile = new PlacementProfile
                {
                    Placement = placement,
                    PositionCandidate = candidate?.PositionExam?.PositionName ?? null,
                    PositionLevel = candidate?.PositionExam?.PositionLevelName ?? null,
                    PositionType = candidate.PositionExam == null ? null : (candidate.PositionExam.HighDegree == null ? null : (candidate.PositionExam.HighDegree == true ? "วิชาการ" : "ทั่วไป")),
                    Prefix = candidate.PrefixName,
                    // ProfileImg = candidate.ProfileImg,
                    Firstname = candidate.FirstName,
                    Lastname = candidate.LastName,
                    Nationality = candidate.Nationality,
                    DateOfBirth = candidate.DateOfBirth,
                    Religion = candidate.ReligionName,
                    Email = candidate.Email,
                    CitizenId = candidate.CitizenId,
                    CitizenDistrictId = candidate.CitizenDistrictId,
                    CitizenProvinceId = candidate.CitizenProvinceId,
                    CitizenDate = candidate.CitizenDate,
                    Telephone = candidate.Telephone,
                    MobilePhone = candidate.MobilePhone,
                    Knowledge = candidate.Knowledge,
                    RegistAddress = candidate.RegistAddress,
                    RegistProvinceId = candidate.RegistProvinceId,
                    RegistDistrictId = candidate.RegistDistrictId,
                    RegistSubDistrictId = candidate.RegistSubDistrictId,
                    RegistZipCode = candidate.RegistZipCode,
                    RegistSame = candidate.RegistSame,
                    CurrentAddress = candidate.CurrentAddress,
                    CurrentProvinceId = candidate.CurrentProvinceId,
                    CurrentDistrictId = candidate.CurrentDistrictId,
                    CurrentSubDistrictId = candidate.CurrentSubDistrictId,
                    CurrentZipCode = candidate.CurrentZipCode,
                    Marry = candidate.Marry,
                    MarryPrefix = candidate.MarryPrefixName,
                    MarryFirstName = candidate.MarryFirstName,
                    MarryLastName = candidate.MarryLastName,
                    MarryOccupation = candidate.MarryOccupation,
                    MarryNationality = candidate.MarryNationality,
                    FatherPrefix = candidate.FatherPrefixName,
                    FatherFirstName = candidate.FatherFirstName,
                    FatherLastName = candidate.FatherLastName,
                    FatherOccupation = candidate.FatherOccupation,
                    FatherNationality = candidate.FatherNationality,
                    MotherPrefix = candidate.MotherPrefixName,
                    MotherFirstName = candidate.MotherFirstName,
                    MotherLastName = candidate.MotherLastName,
                    MotherOccupation = candidate.MotherOccupation,
                    MotherNationality = candidate.MotherNationality,
                    OccupationOrg = candidate.OccupationOrg,
                    OccupationPile = candidate.OccupationPile,
                    OccupationGroup = candidate.OccupationGroup,
                    OccupationSalary = candidate.OccupationSalary,
                    OccupationPosition = candidate.OccupationPosition,
                    OccupationPositionType = candidate.OccupationPositionType,
                    OccupationTelephone = candidate.OccupationTelephone,
                    PointTotalA = candidate.PointTotalA == null ? null : Convert.ToDouble(candidate.PointTotalA),
                    PointA = candidate.PointA == null ? null : Convert.ToDouble(candidate.PointA),
                    PointTotalB = candidate.PointTotalB == null ? null : Convert.ToDouble(candidate.PointTotalB),
                    PointB = candidate.PointB == null ? null : Convert.ToDouble(candidate.PointB),
                    PointTotalC = candidate.PointTotalC == null ? null : Convert.ToDouble(candidate.PointTotalC),
                    PointC = candidate.PointC == null ? null : Convert.ToDouble(candidate.PointC),
                    ExamNumber = candidate.Number == null ? null : Convert.ToInt32(candidate.Number),
                    ExamRound = 1,
                    IsRelief = false,
                    PlacementStatus = "UN-CONTAIN",
                    Pass = candidate.Pass,
                    RemarkHorizontal = "โดยมีเงื่อนไขว่าต้องปฏิบัติงานให้กรุงเทพมหานครเป็นระยะเวลาไม่น้อยกว่า ๕ ปี นับแต่วันที่ได้รับการบรรจุและแต่งตั้ง โดยห้ามโอนไปหน่วยงานหรือส่วนราชการอื่น เว้นเเต่ลาออกจากราชการ",
                    Amount = org?.result?.amount ?? null,
                    PositionSalaryAmount = org?.result?.positionSalaryAmount ?? null,
                    MouthSalaryAmount = org?.result?.mouthSalaryAmount ?? null,
                    CreatedAt = DateTime.Now,
                    CreatedUserId = UserId ?? "",
                    CreatedFullName = FullName ?? "",
                    LastUpdatedAt = DateTime.Now,
                    LastUpdateUserId = UserId ?? "",
                    LastUpdateFullName = FullName ?? "",
                    IsOfficer = IsOfficer,
                    profileId = org?.result?.profileId ?? null,
                    IsOld = org == null || org.result == null ? false : true,
                    AmountOld = org?.result?.AmountOld ?? null,
                    nodeOld = org?.result?.node ?? null,
                    nodeIdOld = org?.result?.nodeId ?? null,
                    posmasterIdOld = org?.result?.posmasterId ?? null,
                    rootOld = org?.result?.root ?? null,
                    rootIdOld = org?.result?.rootId ?? null,
                    rootShortNameOld = org?.result?.rootShortName ?? null,
                    child1Old = org?.result?.child1 ?? null,
                    child1IdOld = org?.result?.child1Id ?? null,
                    child1ShortNameOld = org?.result?.child1ShortName ?? null,
                    child2Old = org?.result?.child2 ?? null,
                    child2IdOld = org?.result?.child2Id ?? null,
                    child2ShortNameOld = org?.result?.child2ShortName ?? null,
                    child3Old = org?.result?.child3 ?? null,
                    child3IdOld = org?.result?.child3Id ?? null,
                    child3ShortNameOld = org?.result?.child3ShortName ?? null,
                    child4Old = org?.result?.child4 ?? null,
                    child4IdOld = org?.result?.child4Id ?? null,
                    child4ShortNameOld = org?.result?.child4ShortName ?? null,
                    orgRevisionIdOld = org?.result?.orgRevisionId ?? null,
                    posMasterNoOld = org?.result?.posMasterNo ?? null,
                    positionNameOld = org?.result?.position ?? null,
                    posTypeIdOld = org?.result?.posTypeId ?? null,
                    posTypeNameOld = org?.result?.posTypeName ?? null,
                    posLevelIdOld = org?.result?.posLevelId ?? null,
                    posLevelNameOld = org?.result?.posLevelName ?? null,
                };

                if (candidate.ProfileImg != null && candidate.ProfileImg.ObjectRefId != null)
                {
                    IFormFile imageFile = await _minioService.GetImageToFormFileAsync(candidate.ProfileImg.ObjectRefId.ToString());
                    var doc = await _minioService.UploadImageFileAsync(imageFile, imageFile.FileName);
                    var _doc = await _contextMetadata.Documents.AsQueryable()
                                        .FirstOrDefaultAsync(x => x.Id == doc.Id);
                    if (_doc != null)
                    {
                        placementProfile.ProfileImg = _doc;
                    }
                }

                await _contextMetadata.PlacementProfiles.AddAsync(placementProfile);

                foreach (var education in candidate.Educations)
                {
                    var placementEducation = new PlacementEducation
                    {
                        PlacementProfile = placementProfile,
                        EducationLevelId = education.EducationLevelExamId,
                        EducationLevelName = education.EducationLevelExamName,
                        Field = education.EducationMajor,
                        Gpa = education.EducationScores,
                        Institute = education.EducationLocation,
                        IsDate = true,
                        FinishDate = education.EducationEndDate,
                        CreatedAt = DateTime.Now,
                        CreatedUserId = UserId ?? "",
                        LastUpdatedAt = DateTime.Now,
                        LastUpdateUserId = UserId ?? "",
                        CreatedFullName = FullName ?? "",
                        LastUpdateFullName = FullName ?? "",
                    };
                    await _contextMetadata.PlacementEducations.AddAsync(placementEducation);
                }
            }
            await _contextMetadata.SaveChangesAsync();
        }

        public async Task UpdateAsyncDisableToPlacement(Guid examId, DateTime accountStartDate)
        {
            try
            {
                // 🚀 Prepare HTTP client once via factory with timeout
                var clientForPos = _httpClientFactory.CreateClient("default");
                clientForPos.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token?.Replace("Bearer ", ""));
                clientForPos.DefaultRequestHeaders.Remove("api_key");
                clientForPos.DefaultRequestHeaders.Add("api_key", _configuration["API_KEY"] ?? "");
                var apiUrl1 = $"{_configuration["API"]}/org/pos/level";
                var response1 = string.Empty;
                try
                {
                    using var ctsPos = new CancellationTokenSource(TimeSpan.FromSeconds(30));
                    response1 = await clientForPos.GetStringAsync(apiUrl1, ctsPos.Token);
                }
                catch (TaskCanceledException)
                {
                    // timeout - fallback to empty posOptions
                    response1 = string.Empty;
                }

                var posOptions = string.IsNullOrWhiteSpace(response1) ? null : JsonConvert.DeserializeObject<RecruitPosRequest>(response1);

                var periodExam = await _context.PeriodExams.AsQueryable()
                                        .Where(x => x.CheckDisability == true)
                                        .FirstOrDefaultAsync(x => x.Id == examId);

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

                var _placement = await _contextMetadata.Placements.AsQueryable()
                                            .FirstOrDefaultAsync(x => x.PlacementType.Name == "คัดเลือกคนพิการ" && x.RefId == periodExam.Id);
                if (_placement != null)
                    throw new Exception("รอบการสอบนี้ได้ทำการบรรจุไปแล้ว");

                // 🚀 Pre-load all lookup data once
                var placementTypesCache = await _contextMetadata.PlacementTypes.ToListAsync();
                var provincesCache = await _contextOrg.province.ToListAsync();
                var districtsCache = await _contextOrg.district.ToListAsync();
                var subDistrictsCache = await _contextOrg.subDistrict.ToListAsync();
                var educationLevelsCache = await _contextOrg.educationLevel.ToListAsync();

                var placement = new Placement
                {
                    Name = periodExam.Name,
                    RefId = periodExam.Id,
                    Round = periodExam.Round?.ToString() ?? "",
                    Year = (int)(periodExam.Year ?? 0),
                    Number = await _context.Disables.AsQueryable().Where(x => x.PeriodExam == periodExam).CountAsync(),
                    PlacementType = placementTypesCache.FirstOrDefault(x => x.Name.Trim().ToUpper().Contains("คัดเลือกคนพิการ")) ?? placementTypesCache.First(),
                    StartDate = accountStartDate,
                    EndDate = accountStartDate.AddYears(2).AddDays(-1),
                    CreatedAt = DateTime.Now,
                    CreatedUserId = UserId ?? "",
                    CreatedFullName = FullName ?? "",
                    LastUpdatedAt = DateTime.Now,
                    LastUpdateUserId = UserId ?? "",
                    LastUpdateFullName = FullName ?? "",
                };
                await _contextMetadata.Placements.AddAsync(placement);

                // 🚀 Load all related data with single queries
                var candidates = await _context.Disables.AsQueryable()
                                        .Include(x => x.Addresses)
                                        .Include(x => x.Certificates)
                                        .Include(x => x.Educations)
                                        .Include(x => x.Occupations)
                                        .Where(x => x.PeriodExam == periodExam)
                                        .ToListAsync();

                var scoreImport = await _context.ScoreImports.AsQueryable()
                                        .FirstOrDefaultAsync(x => x.PeriodExam == periodExam);

                var disableScores = await _context.DisableScores.AsQueryable()
                                        .Where(x => x.ScoreImport == scoreImport && x.ExamStatus == "ผ่าน")
                                        .ToListAsync();

                var disableScoresDict = disableScores
                    .Where(x => !string.IsNullOrWhiteSpace(x.ExamId))
                    .ToDictionary(x => x.ExamId, x => x);

                // 🚀 Batch HTTP requests using IHttpClientFactory with concurrency limit and cancellation
                var clientForOrg = _httpClientFactory.CreateClient("default");
                clientForOrg.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token?.Replace("Bearer ", ""));
                clientForOrg.DefaultRequestHeaders.Remove("api_key");
                clientForOrg.DefaultRequestHeaders.Add("api_key", _configuration["API_KEY"] ?? "");

                var semaphore = new SemaphoreSlim(10); // limit concurrency
                var orgTasks = candidates.Select(async candidate =>
                {
                    if (string.IsNullOrWhiteSpace(candidate.CitizenId))
                        return new { CitizenId = candidate.CitizenId ?? "", org = (dynamic?)null };

                    await semaphore.WaitAsync();
                    try
                    {
                        var apiUrl = $"{_configuration["API"]}/org/profile/citizenid/position/{candidate.CitizenId}";
                        try
                        {
                            using var cts = new CancellationTokenSource(TimeSpan.FromSeconds(30));
                            var response = await clientForOrg.GetStringAsync(apiUrl, cts.Token);
                            return new { CitizenId = candidate.CitizenId, org = JsonConvert.DeserializeObject<dynamic>(response) };
                        }
                        catch (TaskCanceledException)
                        {
                            // timeout
                            return new { CitizenId = candidate.CitizenId ?? "", org = (dynamic?)null };
                        }
                        catch (Exception)
                        {
                            return new { CitizenId = candidate.CitizenId ?? "", org = (dynamic?)null };
                        }
                    }
                    finally
                    {
                        semaphore.Release();
                    }
                }).ToList();

                var orgResults = await Task.WhenAll(orgTasks);
                var orgDict = orgResults.ToDictionary(x => x.CitizenId ?? "", x => x.org);

                // 🚀 Prepare batch inserts
                var placementProfiles = new List<PlacementProfile>();
                var placementEducations = new List<PlacementEducation>();
                /*Comment ข้อมูลใบประกอบวิชาชีพ เพราะในไฟล์นำเข้ายังไม่มีคอลัมน์ที่ระบุข้อมูลส่วนนี้*/
                //var placementCertificates = new List<PlacementCertificate>();

                foreach (var candidate in candidates)
                {
                    if (string.IsNullOrWhiteSpace(candidate.ExamId) ||
                        !disableScoresDict.TryGetValue(candidate.ExamId, out var disableScore))
                        continue;

                    var org = orgDict.TryGetValue(candidate.CitizenId ?? "", out var orgValue) ? orgValue : null;
                    var isOfficer = org?.result != null;

                    // 🚀 Cache repeated calculations
                    var firstAddress = candidate.Addresses?.FirstOrDefault();
                    var firstEducation = candidate.Educations?.FirstOrDefault();
                    var firstCertificate = candidate.Certificates?.FirstOrDefault();
                    var firstOccupation = candidate.Occupations?.FirstOrDefault();

                    var registAddress = string.Join("", new[] {
                        firstAddress?.Address ?? "",
                        string.IsNullOrWhiteSpace(firstAddress?.Moo) ? "" : $" หมู่ {firstAddress.Moo}",
                        string.IsNullOrWhiteSpace(firstAddress?.Soi) ? "" : $" ซอย {firstAddress.Soi}",
                        string.IsNullOrWhiteSpace(firstAddress?.Road) ? "" : $" ถนน {firstAddress.Road}"
                    });

                    var currentAddress = string.Join("", new[] {
                        firstAddress?.Address1 ?? "",
                        string.IsNullOrWhiteSpace(firstAddress?.Moo1) ? "" : $" หมู่ {firstAddress.Moo1}",
                        string.IsNullOrWhiteSpace(firstAddress?.Soi1) ? "" : $" ซอย {firstAddress.Soi1}",
                        string.IsNullOrWhiteSpace(firstAddress?.Road1) ? "" : $" ถนน {firstAddress.Road1}"
                    });

                    // หาค่า posLevelName หลังสุด
                    var posLevelObject = posOptions?.result?.FirstOrDefault(x =>
                        !string.IsNullOrWhiteSpace(x.posLevelName) &&
                        !string.IsNullOrWhiteSpace(candidate.PositionName) &&
                        candidate.PositionName.Contains(x.posLevelName));

                    // เก็บเฉพาะค่า posLevelName
                    var posLevelName = posLevelObject?.posLevelName;

                    // สร้างตัวแปร PositionName ที่ตัดค่า posLevelName ออก
                    var positionNameWithoutLevel = candidate.PositionName ?? "";
                    if (!string.IsNullOrWhiteSpace(posLevelName))
                    {
                        positionNameWithoutLevel = positionNameWithoutLevel.Replace(posLevelName, "").Trim();
                    }

                    var placementProfile = new PlacementProfile
                    {
                        Placement = placement,
                        PositionCandidate = positionNameWithoutLevel ?? "",
                        PositionType = posLevelObject?.posTypes?.posTypeName ?? "",
                        PositionLevel = posLevelName ?? "",
                        Prefix = candidate.Prefix ?? "",
                        Firstname = candidate.FirstName ?? "",
                        Lastname = candidate.LastName ?? "",
                        Gender = candidate.Gendor ?? "",
                        Nationality = candidate.National ?? "",
                        Race = candidate.Race ?? "",
                        Religion = candidate.Religion ?? "",
                        DateOfBirth = candidate.DateOfBirth,
                        Relationship = candidate.Marry ?? "",
                        CitizenId = candidate.CitizenId ?? "",
                        CitizenProvinceId = provincesCache.FirstOrDefault(x => x.name == candidate.CitizenCardIssuer)?.Id,
                        CitizenDate = candidate.CitizenCardExpireDate,
                        Telephone = firstAddress?.Telephone ?? "",
                        MobilePhone = firstAddress?.Mobile ?? "",
                        RegistAddress = registAddress,
                        RegistProvinceId = provincesCache.FirstOrDefault(x => x.name == firstAddress?.Province)?.Id,
                        RegistDistrictId = districtsCache.FirstOrDefault(x => x.name == firstAddress?.District)?.Id,
                        RegistSubDistrictId = subDistrictsCache.FirstOrDefault(x => x.name == firstAddress?.Soi)?.Id,
                        RegistZipCode = firstAddress?.ZipCode ?? "",
                        RegistSame = false,
                        CurrentAddress = currentAddress,
                        CurrentProvinceId = provincesCache.FirstOrDefault(x => x.name == firstAddress?.Province1)?.Id,
                        CurrentDistrictId = districtsCache.FirstOrDefault(x => x.name == firstAddress?.District1)?.Id,
                        CurrentSubDistrictId = subDistrictsCache.FirstOrDefault(x => x.name == firstAddress?.Soi1)?.Id,
                        CurrentZipCode = firstAddress?.ZipCode1,
                        Marry = candidate.Marry?.Contains("สมรส") ?? false,
                        OccupationPositionType = "other",
                        OccupationTelephone = firstOccupation?.Telephone ?? "",
                        OccupationPosition = firstOccupation?.Position ?? "",
                        PointTotalA = Convert.ToDouble(disableScore.FullA),
                        PointA = Convert.ToDouble(disableScore.SumA),
                        PointTotalB = Convert.ToDouble(disableScore.FullB),
                        PointB = Convert.ToDouble(disableScore.SumB),
                        PointTotalC = Convert.ToDouble(disableScore.FullC),
                        PointC = Convert.ToDouble(disableScore.SumC),
                        ExamNumber = !string.IsNullOrWhiteSpace(disableScore.Number) && int.TryParse(disableScore.Number, out int n) ? n : null,
                        ExamRound = null,
                        IsRelief = false,
                        PlacementStatus = "UN-CONTAIN",
                        Pass = disableScore.ExamStatus ?? "",
                        RemarkHorizontal = "โดยมีเงื่อนไขว่าต้องปฏิบัติงานให้กรุงเทพมหานครเป็นระยะเวลาไม่น้อยกว่า ๕ ปี นับแต่วันที่ได้รับการบรรจุและแต่งตั้ง โดยห้ามโอนไปหน่วยงานหรือส่วนราชการอื่น เว้นเเต่ลาออกจากราชการ",
                        Amount = org?.result?.amount,
                        PositionSalaryAmount = org?.result?.positionSalaryAmount,
                        MouthSalaryAmount = org?.result?.mouthSalaryAmount,
                        CreatedAt = DateTime.Now,
                        CreatedUserId = UserId ?? "",
                        CreatedFullName = FullName ?? "",
                        LastUpdatedAt = DateTime.Now,
                        LastUpdateUserId = UserId ?? "",
                        LastUpdateFullName = FullName ?? "",
                        IsOfficer = isOfficer,
                        profileId = org?.result?.profileId ?? "",
                        IsOld = org?.result != null,
                        AmountOld = org?.result?.AmountOld,
                        nodeOld = org?.result?.node ?? "",
                        nodeIdOld = org?.result?.nodeId ?? "",
                        posmasterIdOld = org?.result?.posmasterId ?? "",
                        rootOld = org?.result?.root ?? "",
                        rootIdOld = org?.result?.rootId ?? "",
                        rootShortNameOld = org?.result?.rootShortName ?? "",
                        child1Old = org?.result?.child1 ?? "",
                        child1IdOld = org?.result?.child1Id ?? "",
                        child1ShortNameOld = org?.result?.child1ShortName ?? "",
                        child2Old = org?.result?.child2 ?? "",
                        child2IdOld = org?.result?.child2Id ?? "",
                        child2ShortNameOld = org?.result?.child2ShortName ?? "",
                        child3Old = org?.result?.child3 ?? "",
                        child3IdOld = org?.result?.child3Id ?? "",
                        child3ShortNameOld = org?.result?.child3ShortName ?? "",
                        child4Old = org?.result?.child4 ?? "",
                        child4IdOld = org?.result?.child4Id ?? "",
                        child4ShortNameOld = org?.result?.child4ShortName ?? "",
                        orgRevisionIdOld = org?.result?.orgRevisionId ?? "",
                        posMasterNoOld = org?.result?.posMasterNo,
                        positionNameOld = org?.result?.position ?? "",
                        posTypeIdOld = org?.result?.posTypeId ?? "",
                        posTypeNameOld = org?.result?.posTypeName ?? "",
                        posLevelIdOld = org?.result?.posLevelId ?? "",
                        posLevelNameOld = org?.result?.posLevelName ?? "",
                    };
                    placementProfiles.Add(placementProfile);

                    var placementEducation = new PlacementEducation
                    {
                        PlacementProfile = placementProfile,
                        EducationLevelId = educationLevelsCache.FirstOrDefault(x => x.name == firstEducation?.HighDegree)?.Id,
                        EducationLevelName = educationLevelsCache.FirstOrDefault(x => x.name == firstEducation?.HighDegree)?.name,
                        Field = firstEducation?.Major ?? "",
                        Gpa = firstEducation?.GPA.ToString() ?? "",
                        Institute = firstEducation?.University ?? "",
                        Degree = firstEducation?.Degree ?? "",
                        FinishDate = firstEducation?.BachelorDate,
                        IsDate = true,
                        CreatedAt = DateTime.Now,
                        CreatedUserId = UserId ?? "",
                        LastUpdatedAt = DateTime.Now,
                        LastUpdateUserId = UserId ?? "",
                        CreatedFullName = FullName ?? "",
                        LastUpdateFullName = FullName ?? "",
                    };
                    placementEducations.Add(placementEducation);
                    /*Comment ข้อมูลใบประกอบวิชาชีพ เพราะในไฟล์นำเข้ายังไม่มีคอลัมน์ที่ระบุข้อมูลส่วนนี้*/
                    //var placementCertificate = new PlacementCertificate
                    //{
                    //    PlacementProfile = placementProfile,
                    //    CertificateNo = firstCertificate?.CertificateNo ?? "",
                    //    IssueDate = firstCertificate?.IssueDate,
                    //    ExpireDate = firstCertificate?.ExpiredDate,
                    //    CertificateType = firstCertificate?.Description ?? "",
                    //    CreatedAt = DateTime.Now,
                    //    CreatedUserId = UserId ?? "",
                    //    LastUpdatedAt = DateTime.Now,
                    //    LastUpdateUserId = UserId ?? "",
                    //    CreatedFullName = FullName ?? "",
                    //    LastUpdateFullName = FullName ?? "",
                    //};
                    //placementCertificates.Add(placementCertificate);
                }

                // 🚀 Batch insert all records
                await _contextMetadata.PlacementProfiles.AddRangeAsync(placementProfiles);
                await _contextMetadata.PlacementEducations.AddRangeAsync(placementEducations);
                /*Comment ข้อมูลใบประกอบวิชาชีพ เพราะในไฟล์นำเข้ายังไม่มีคอลัมน์ที่ระบุข้อมูลส่วนนี้*/
                //await _contextMetadata.PlacementCertificates.AddRangeAsync(placementCertificates);

                // 🚀 Single SaveChanges at the end
                await _contextMetadata.SaveChangesAsync();
            }
            catch
            {
                throw;
            }
        }


        public CalculateBetweenDateV2ValueObj CalculateBetweenDateV2Value(DateTime startDate, DateTime endDate)
        {
            // if (startDate == null || endDate == null)
            //     return null;
            DateTime today = endDate.AddDays(1);
            DateTime birthDate = startDate;
            int years = new DateTime(endDate.Subtract(birthDate).Ticks).Year - 1;
            DateTime pastYearDate = birthDate.AddYears(years);
            int months = 0;
            for (int i = 1; i <= 12; i++)
            {
                if (pastYearDate.AddMonths(i) == today)
                {
                    months = i;
                    break;
                }
                else if (pastYearDate.AddMonths(i) >= today)
                {
                    months = i - 1;
                    break;
                }
            }
            int days = today.Subtract(pastYearDate.AddMonths(months)).Days;
            if (today.Day < pastYearDate.Day)
            {
                if (System.DateTime.DaysInMonth(pastYearDate.Year, pastYearDate.Month) > System.DateTime.DaysInMonth(pastYearDate.AddMonths(months).Year, pastYearDate.AddMonths(months).Month))
                {
                    days += 1;
                }
                if (System.DateTime.DaysInMonth(pastYearDate.Year, pastYearDate.Month) < System.DateTime.DaysInMonth(pastYearDate.AddMonths(months).Year, pastYearDate.AddMonths(months).Month))
                {
                    days -= 1;
                }
            }
            if (days >= 30)
            {
                months = months + 1;
                days = 0;
                if (months >= 12)
                {
                    years = years + 1;
                    months = 0;
                }
            }

            var sb = new StringBuilder();
            sb.Clear();
            sb.Append(years == 0 ? "" : $"{years} ปี ");
            sb.Append(months == 0 ? "" : $"{months} เดือน ");
            sb.Append(days == 0 ? "" : $"{days} วัน ");
            return new CalculateBetweenDateV2ValueObj { years = years, months = months, days = days };
        }
        public class CalculateBetweenDateV2ValueObj
        {
            public int years { get; set; }

            public int months { get; set; }

            public int days { get; set; }
        }
        #endregion
    }
}