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> 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 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 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 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 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 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 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 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 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 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 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 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 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> GetsAsyncCareer(string candidateId) { return await _context.Careers.AsQueryable() .Where(x => x.Candidate.Id == Guid.Parse(candidateId)) .OrderBy(d => d.DurationStart) .ToListAsync(); } public async Task> 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> ReadExcelCandidate(IFormFile formFile) { var list = new List(); 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 + " สถานะการสมัครสอบ: สอบคัดเลือกสำเร็จ
" + "ผลการสอบของท่าน คือ " + 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 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(); 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 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(); 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(); 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 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 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> 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 { 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 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 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(); 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 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 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(); var careers = new List(); 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 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(_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(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(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(); var placementEducations = new List(); /*Comment ข้อมูลใบประกอบวิชาชีพ เพราะในไฟล์นำเข้ายังไม่มีคอลัมน์ที่ระบุข้อมูลส่วนนี้*/ //var placementCertificates = new List(); 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 } }