using Amazon.S3.Model; using BMA.EHR.Recurit.Exam.Service.Extensions; using BMA.EHR.Recurit.Exam.Service.Controllers; using BMA.EHR.Recurit.Exam.Service.Core; using BMA.EHR.Recurit.Exam.Service.Data; using BMA.EHR.Recurit.Exam.Service.Extensions; using BMA.EHR.Recurit.Exam.Service.Models; using BMA.EHR.Recurit.Exam.Service.Models.Disables; using BMA.EHR.Recurit.Exam.Service.Request.Disables; using BMA.EHR.Recurit.Exam.Service.Response; using BMA.EHR.Recurit.Exam.Service.Responses; using BMA.EHR.Recurit.Exam.Service.Services; using BMA.EHR.Recurit.Exam.Service.Responses.Document; using Microsoft.AspNetCore.Authorization; using Microsoft.AspNetCore.Mvc; using Microsoft.EntityFrameworkCore; using MySqlConnector; using OfficeOpenXml; using Org.BouncyCastle.Ocsp; using Sentry; using Swashbuckle.AspNetCore.Annotations; using System.Data; using System.Net; using System.Net.WebSockets; using System.Security.Claims; using System.Text; using Newtonsoft.Json.Linq; using Newtonsoft.Json; using System.Net.Http.Headers; using BMA.EHR.Recurit.Exam.Service.Request; namespace BMA.EHR.Recurit.Exam.Service.Controllers { [Route("api/v{version:apiVersion}/candidate/disable-exam")] [ApiVersion("1.0")] [ApiController] [Produces("application/json")] [Authorize] [SwaggerTag("จัดการข้อมูลการสอบคัดเลือกคนพิการ")] public class DisableController : BaseController { #region " Fields " private readonly ApplicationDbContext _context; private readonly MetadataDbContext _contextMetadata; private readonly MinIOService _minioService; private readonly IWebHostEnvironment _webHostEnvironment; private readonly DisableService _disableService; private readonly PeriodExamService _periodExamService; private readonly PermissionRepository _permission; private readonly IHttpContextAccessor _httpContextAccessor; private readonly ILogger _logger; private readonly IConfiguration _configuration; #endregion #region " Constructor and Destructor " public DisableController(ApplicationDbContext context, MetadataDbContext contextMetadata, MinIOService minioService, IWebHostEnvironment webHostEnvironment, DisableService disableService, PeriodExamService periodExamService, IHttpContextAccessor httpContextAccessor, ILogger logger, IConfiguration configuration, PermissionRepository permission) { _context = context; _contextMetadata = contextMetadata; _minioService = minioService; _webHostEnvironment = webHostEnvironment; _disableService = disableService; _periodExamService = periodExamService; _httpContextAccessor = httpContextAccessor; _configuration = configuration; _logger = logger; _permission = permission; } #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 " #region " Private " private int GetColumnIndex(string[] columns, string name, bool partial = false) { try { if (partial) return Array.FindIndex(columns, x => x.Contains(name)) + 1; else return Array.FindIndex(columns, x => x == name) + 1; } catch { return 0; } } private string CalculateDiff(DateTime d1, DateTime d2) { if (d1 > d2) return "ข้อมูลไม่ถูกต้อง"; TimeSpan sp = d2.Subtract(d1); int yy = sp.Days / 365; int mm = (sp.Days - (yy * 365)) / 30; int dd = (sp.Days - (yy * 365) - (mm * 30)); var sb = new StringBuilder(); sb.Clear(); sb.Append(yy == 0 ? "" : $"{yy} ปี "); sb.Append(mm == 0 ? "" : $"{mm} เดือน "); //sb.Append(dd == 0 ? "" : $"{dd} วัน "); return sb.ToString(); } private async Task GetExamCount(Guid exam) { try { return await _context.DisablePayments.AsQueryable() .Include(x => x.Disable) .ThenInclude(x => x.PeriodExam) .Where(x => x.Disable.PeriodExam.Id == exam) .Where(x => x.Disable.PeriodExam.CheckDisability == true) .CountAsync(); } catch { throw; } } private async Task GetPassExamCount(Guid exam) { try { return await _context.DisableScores.AsQueryable() .Include(x => x.ScoreImport) .Where(x => x.ScoreImport.Id == exam) .Where(x => x.ExamStatus == "ผ่าน") .CountAsync(); } catch { throw; } } private async Task GetScoreCount(Guid exam) { try { return await _context.DisableScores.AsQueryable() .Include(x => x.ScoreImport) .Where(x => x.ScoreImport.Id == exam) .CountAsync(); } catch { throw; } } #endregion #region " Ex. Upload, Download and Delete file " /// /// ตัวอย่างในการเขียน api เพื่อทำการ upload file /// /// /// เมื่อทำการ upload สำเร็จ /// ค่าตัวแปรที่ส่งมาไม่ถูกต้อง /// ไม่ได้ Login เข้าระบบ /// เมื่อเกิดข้อผิดพลาดในการทำงาน [HttpPost("upload"), DisableRequestSizeLimit] [ProducesResponseType(StatusCodes.Status200OK)] [ProducesResponseType(StatusCodes.Status400BadRequest)] [ProducesResponseType(StatusCodes.Status401Unauthorized)] [ProducesResponseType(StatusCodes.Status500InternalServerError)] [AllowAnonymous] public async Task> UploadFile() { try { if (Request.Form.Files == null || Request.Form.Files.Count == 0) { return Error(GlobalMessages.NoFileToUpload); } var file = Request.Form.Files[0]; var doc = await _minioService.UploadFileAsync(file); return Success(doc); } catch (Exception ex) { return Error(ex); } } /// /// ตัวอย่างในการเขียน api เพื่อทำการ delete file /// /// รหัสไฟล์ในฐานข้อมูล /// /// เมื่อทำการ delete file สำเร็จ /// ค่าตัวแปรที่ส่งมาไม่ถูกต้อง /// ไม่ได้ Login เข้าระบบ /// เมื่อเกิดข้อผิดพลาดในการทำงาน [HttpGet("delete/{id:length(36)}")] [ProducesResponseType(StatusCodes.Status200OK)] [ProducesResponseType(StatusCodes.Status400BadRequest)] [ProducesResponseType(StatusCodes.Status401Unauthorized)] [ProducesResponseType(StatusCodes.Status500InternalServerError)] [AllowAnonymous] public async Task> DeleteFile(Guid id) { try { await _minioService.DeleteFileAsync(id); return Success(); } catch (Exception ex) { return Error(ex); } } /// /// ตัวอย่างในการเขียน api เพื่อทำการ download file /// /// รหัสไฟล์ในฐานข้อมูล /// /// เมื่อทำการ download file สำเร็จ /// ค่าตัวแปรที่ส่งมาไม่ถูกต้อง /// ไม่ได้ Login เข้าระบบ /// เมื่อเกิดข้อผิดพลาดในการทำงาน [HttpGet("download/{id:length(36)}")] [ProducesResponseType(StatusCodes.Status200OK)] [ProducesResponseType(StatusCodes.Status400BadRequest)] [ProducesResponseType(StatusCodes.Status401Unauthorized)] [ProducesResponseType(StatusCodes.Status500InternalServerError)] [AllowAnonymous] public async Task> DownloadFile(Guid id) { try { var file_data = await _minioService.DownloadFileAsync(id); Response.Headers["Content-Disposition"] = $"inline; filename={file_data.FileName}"; var ret = new FileContentResult(file_data.FileContent, file_data.FileType) { FileDownloadName = file_data.FileName }; return ret; } catch (Exception ex) { return Error(ex); } } #endregion #region " จัดการรอบการสมัครสอบคัดเลือกผู้พิการ " /// /// แสดงข้อมูลรอบการสอบคัดเลือกผู้พิการ /// /// /// เมื่อทำการอ่านข้อมูลจาก Relational Database สำเร็จ /// ค่าตัวแปรที่ส่งมาไม่ถูกต้อง /// ไม่ได้ Login เข้าระบบ /// เมื่อเกิดข้อผิดพลาดในการทำงาน [HttpGet("period")] [ProducesResponseType(StatusCodes.Status200OK)] [ProducesResponseType(StatusCodes.Status400BadRequest)] [ProducesResponseType(StatusCodes.Status401Unauthorized)] [ProducesResponseType(StatusCodes.Status500InternalServerError)] public async Task> GetPeriodsAsync() { try { var data = await _context.PeriodExams.AsQueryable() .Where(x => x.CheckDisability == true) .Include(x => x.ImportFile) .Include(x => x.Disables) .Include(x => x.ScoreImport) .ThenInclude(x => x.ImportFile) .Include(x => x.ScoreImport) .ThenInclude(x => x.Scores) .OrderByDescending(x => x.Year) .ThenByDescending(x => x.Round) .Select(x => new { x.Id, x.Year, x.Name, x.Round, ImportDate = x.CreatedAt.Date.ToThaiShortDate(), ExamCount = x.Disables.Count(), Score = x.ScoreImport == null ? null : new { ID = x.ScoreImport.Id, ImportYear = x.ScoreImport.Year, ImportDate = x.CreatedAt.Date.ToThaiShortDate(), ScoreCount = x.ScoreImport.Scores.Count(), } }) .ToListAsync(); return Success(data); } catch (Exception ex) { return Error(ex); } } /// /// แสดงข้อมูลรอบการสอบคัดเลือกผู้พิการเป็นรายการ /// /// รหัสรอบการสอบคัดเลือกผู้พิการ /// /// เมื่อทำการอ่านข้อมูลจาก Relational Database สำเร็จ /// ค่าตัวแปรที่ส่งมาไม่ถูกต้อง /// ไม่ได้ Login เข้าระบบ /// เมื่อเกิดข้อผิดพลาดในการทำงาน [HttpGet("period/{id:length(36)}")] [ProducesResponseType(StatusCodes.Status200OK)] [ProducesResponseType(StatusCodes.Status400BadRequest)] [ProducesResponseType(StatusCodes.Status401Unauthorized)] [ProducesResponseType(StatusCodes.Status500InternalServerError)] public async Task> GetPeriodByIdAsync(Guid id) { try { // var data = await _context.PeriodExams.AsQueryable() // .Where(x => x.CheckDisability == true) // .Include(x => x.ImportFile) // .Include(x => x.Disables) // .ThenInclude(x => x.Addresses) // .Include(x => x.Disables) // .ThenInclude(x => x.Occupations) // .Include(x => x.Disables) // .ThenInclude(x => x.Certificates) // .Include(x => x.Disables) // .ThenInclude(x => x.Educations) // .Include(x => x.Disables) // .ThenInclude(x => x.Payments) // .Include(x => x.Disables) // .ThenInclude(x => x.Documents) // .ThenInclude(x => x.DocumentFile) // .FirstOrDefaultAsync(x => x.Id == id); var periodExam = await _context.PeriodExams.AsQueryable() .Select(x => new PeriodExamCandidateResponseItem { ExamDate = x.ExamDate, AnnouncementEndDate = x.AnnouncementEndDate, AnnouncementStartDate = x.AnnouncementStartDate, AnnouncementDate = x.AnnouncementDate, CheckDisability = x.CheckDisability, CheckDocument = x.CheckDocument, Detail = x.Detail, Fee = x.Fee, Id = x.Id, IsActive = x.IsActive, Name = x.Name, Note = x.Note, // OrganizationCodeId = x.OrganizationCodeId, // OrganizationCodeName = x.OrganizationCodeName, // OrganizationId = x.OrganizationId, // OrganizationName = x.OrganizationName, PaymentEndDate = x.PaymentEndDate, // PaymentKrungThai = x.PaymentKrungThai, AnnouncementExam = x.AnnouncementExam, Category = x.Category, PaymentStartDate = x.PaymentStartDate, RegisterEndDate = x.RegisterEndDate, RegisterStartDate = x.RegisterStartDate, Round = x.Round, // SetSeat = x.SetSeat, Year = x.Year, // BankExam = x.BankExam.OrderBy(o => o.CreatedAt).Select(b => new BankExam // { // Id = b.Id, // AccountName = b.AccountName, // AccountNumber = b.AccountNumber, // BankName = b.BankName, // }).ToList(), // PositionExam = x.PositionExam.OrderBy(o => o.CreatedAt).Select(b => new PositionExam // { // Id = b.Id, // TypeId = b.TypeId, // TypeName = b.TypeName, // PositionId = b.PositionId, // PositionName = b.PositionName, // }).ToList(), Documents = x.PeriodExamDocuments.OrderBy(o => o.CreatedAt).Select(b => new FileListResponse { Id = b.Document == null ? "" : b.Document.Id.ToString(), FileName = b.Document == null ? "" : b.Document.FileName, FileSize = b.Document == null ? 0 : b.Document.FileSize, FileType = b.Document == null ? "" : b.Document.FileType, Detail = b.Document == null ? "" : b.Document.Id.ToString(), }).ToList(), Images = x.PeriodExamImages.OrderBy(o => o.CreatedAt).Select(b => new FileListResponse { Id = b.Document == null ? "" : b.Document.Id.ToString(), FileName = b.Document == null ? "" : b.Document.FileName, FileSize = b.Document == null ? 0 : b.Document.FileSize, FileType = b.Document == null ? "" : b.Document.FileType, Detail = b.Document == null ? "" : b.Document.Id.ToString(), }).ToList(), }) .FirstOrDefaultAsync(x => x.Id == id); if (periodExam == null) throw new Exception(GlobalMessages.ExamNotFound); return Success(periodExam); } catch (Exception ex) { return Error(ex); } } /// /// เพิ่มข้อมูลรอบการจัดสอบคัดเลือกผู้พิการ /// /// Request parameters /// /// เมื่อทำการเพิ่มข้อมูลสำเร็จ /// ค่าตัวแปรที่ส่งมาไม่ถูกต้อง /// ไม่ได้ Login เข้าระบบ /// เมื่อเกิดข้อผิดพลาดในการทำงาน [HttpPost("period")] [ProducesResponseType(StatusCodes.Status200OK)] [ProducesResponseType(StatusCodes.Status400BadRequest)] [ProducesResponseType(StatusCodes.Status401Unauthorized)] [ProducesResponseType(StatusCodes.Status500InternalServerError)] public async Task> PostPeriodAsync([FromBody] PostDisableImportRequest req) { try { if (req == null) return Error(GlobalMessages.InvalidRequestParam, (int)HttpStatusCode.BadRequest); var periodExam = new PeriodExam { Year = req.Year, Name = req.Name, Round = req.Round, Detail = req.Detail, Fee = req.Fee, AnnouncementStartDate = req.AnnouncementStartDate, AnnouncementEndDate = req.AnnouncementEndDate, RegisterStartDate = req.RegisterStartDate, RegisterEndDate = req.RegisterEndDate, ExamDate = req.ExamDate, PaymentStartDate = req.PaymentStartDate, PaymentEndDate = req.PaymentEndDate, Note = req.Note, AnnouncementDate = req.AnnouncementDate, CheckDisability = true, CreatedAt = DateTime.Now, CreatedUserId = UserId ?? "", CreatedFullName = FullName ?? "System Administrator", LastUpdatedAt = DateTime.Now, LastUpdateUserId = UserId ?? "", LastUpdateFullName = FullName ?? "System Administrator", }; var apiUrl = $"{_configuration["API"]}/org/find/head/officer"; 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(); if (_res.IsSuccessStatusCode) { var org = JsonConvert.DeserializeObject(_result); periodExam.AuthName = org.result.name == null ? "" : org.result.name; periodExam.AuthPosition = org.result.position == null ? "" : org.result.position; } } await _context.PeriodExams.AddAsync(periodExam); await _context.SaveChangesAsync(); return Success(periodExam.Id); } catch (Exception ex) { return Error(ex); } } /// /// แก้ไขข้อมูลรอบการจัดสอบคัดเลือกผู้พิการ /// /// รหัสรอบการสอบคัดเลือกผู้พิการ /// Request parameters /// /// เมื่อทำการเพิ่มข้อมูลสำเร็จ /// ค่าตัวแปรที่ส่งมาไม่ถูกต้อง /// ไม่ได้ Login เข้าระบบ /// เมื่อเกิดข้อผิดพลาดในการทำงาน [HttpPut("period/{id:length(36)}")] [ProducesResponseType(StatusCodes.Status200OK)] [ProducesResponseType(StatusCodes.Status400BadRequest)] [ProducesResponseType(StatusCodes.Status401Unauthorized)] [ProducesResponseType(StatusCodes.Status500InternalServerError)] public async Task> PutPeriodAsync(Guid id, [FromBody] PostDisableImportRequest req) { try { var getPermission = await _permission.GetPermissionAPIAsync("UPDATE", "SYS_EXAM_SELECT_PERIOD_DIS"); var jsonData = JsonConvert.DeserializeObject(getPermission); if (jsonData["status"]?.ToString() != "200") { return Error(jsonData["message"]?.ToString(), StatusCodes.Status403Forbidden); } var data = await _context.PeriodExams.AsQueryable() .Where(x => x.CheckDisability == true) .FirstOrDefaultAsync(x => x.Id == id); if (data == null) return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound); data.Name = req.Name; data.Year = req.Year; data.Round = req.Round; data.Detail = req.Detail; data.Fee = req.Fee; data.AnnouncementEndDate = req.AnnouncementEndDate; data.AnnouncementStartDate = req.AnnouncementStartDate; data.RegisterStartDate = req.RegisterStartDate; data.RegisterEndDate = req.RegisterEndDate; data.PaymentEndDate = req.PaymentEndDate; data.PaymentStartDate = req.PaymentStartDate; data.ExamDate = req.ExamDate; data.Note = req.Note; data.AnnouncementDate = req.AnnouncementDate; data.LastUpdatedAt = DateTime.Now; data.LastUpdateUserId = UserId ?? ""; data.LastUpdateFullName = FullName ?? "System Administrator"; await _context.SaveChangesAsync(); return Success(); } catch (Exception ex) { return Error(ex); } } /// /// ลบข้อมูลรอบการจัดสอบคัดเลือกผู้พิการ /// /// รหัสรอบการสอบคัดเลือกผู้พิการ /// /// เมื่อทำการลบข้อมูลสำเร็จ /// ค่าตัวแปรที่ส่งมาไม่ถูกต้อง /// ไม่ได้ Login เข้าระบบ /// เมื่อเกิดข้อผิดพลาดในการทำงาน [HttpDelete("period/{id:length(36)}")] [ProducesResponseType(StatusCodes.Status200OK)] [ProducesResponseType(StatusCodes.Status400BadRequest)] [ProducesResponseType(StatusCodes.Status401Unauthorized)] [ProducesResponseType(StatusCodes.Status500InternalServerError)] public async Task> DeletePeriodAsync(Guid id) { try { var getPermission = await _permission.GetPermissionAPIAsync("DELETE", "SYS_EXAM_SELECT_PERIOD_DIS"); var jsonData = JsonConvert.DeserializeObject(getPermission); if (jsonData["status"]?.ToString() != "200") { return Error(jsonData["message"]?.ToString(), StatusCodes.Status403Forbidden); } var data = await _context.PeriodExams.AsQueryable() .Where(x => x.CheckDisability == true) .Include(x => x.ImportFile) .Include(x => x.Disables) .ThenInclude(x => x.Addresses) .Include(x => x.Disables) .ThenInclude(x => x.Occupations) .Include(x => x.Disables) .ThenInclude(x => x.Certificates) .Include(x => x.Disables) .ThenInclude(x => x.Educations) .Include(x => x.Disables) .ThenInclude(x => x.Payments) .Include(x => x.Disables) .ThenInclude(x => x.Documents) .ThenInclude(x => x.DocumentFile) .Include(x => x.ScoreImport) .ThenInclude(x => x.ImportFile) .Include(x => x.ScoreImport) .ThenInclude(x => x.Scores) .FirstOrDefaultAsync(x => x.Id == id); _context.PeriodExams.Remove(data); await _context.SaveChangesAsync(); return Success(); } catch (Exception ex) { return Error(ex); } } #endregion #region " Candidate Files " /// /// แสดงข้อมูลสำหรับหน้าจอ รายการนำเข้าข้อมูลผู้สมัครสอบคัดเลือกผู้พิการ /// /// /// เมื่อแสดงรายการข้อมูลสำเร็จ /// ค่าตัวแปรที่ส่งมาไม่ถูกต้อง /// ไม่ได้ Login เข้าระบบ /// เมื่อเกิดข้อผิดพลาดในการทำงาน [HttpGet("candidate")] [ProducesResponseType(StatusCodes.Status200OK)] [ProducesResponseType(StatusCodes.Status400BadRequest)] [ProducesResponseType(StatusCodes.Status401Unauthorized)] [ProducesResponseType(StatusCodes.Status500InternalServerError)] public async Task> GetCandidateFilesAsync() { try { var getPermission = await _permission.GetPermissionAPIAsync("LIST", "SYS_EXAM_SELECT_PERIOD_DIS"); var jsonData = JsonConvert.DeserializeObject(getPermission); if (jsonData["status"]?.ToString() != "200") { return Error(jsonData["message"]?.ToString(), StatusCodes.Status403Forbidden); } var data = await _context.PeriodExams.AsQueryable() .Where(x => x.CheckDisability == true) .Include(x => x.ImportFile) .Include(x => x.Disables) .Include(x => x.ScoreImport) .ThenInclude(x => x.ImportFile) .Include(x => x.ScoreImport) .ThenInclude(x => x.Scores) .OrderByDescending(x => x.Year) .ThenByDescending(x => x.Round) .ThenByDescending(x => x.LastUpdatedAt) .Select(x => new { x.Id, x.Year, x.Name, x.Round, ImportDate = x.CreatedAt.Date.ToThaiShortDate(), ExamCount = x.Disables.Count(), Score = x.ScoreImport == null || x.ScoreImport.Scores.Count == 0 ? null : new { ID = x.ScoreImport.Id, ImportYear = x.ScoreImport.Year, ImportDate = x.CreatedAt.Date.ToThaiShortDate(), ScoreCount = x.ScoreImport.Scores.Count(), ResultCount = x.ScoreImport.Scores.Count(x => !string.IsNullOrEmpty(x.Number)) }, x.CreatedUserId, }) .ToListAsync(); var roles = _httpContextAccessor?.HttpContext?.User?.FindAll(ClaimTypes.Role)?.Select(c => c.Value).ToList(); return Success(data); } catch (Exception ex) { return Error(ex); } } /// /// นำเข้ารายชื่อผู้สมัครสอบคัดเลือกผู้พิการ /// /// /// เมื่อทำนำเข้าข้อมูลสำเร็จ /// ค่าตัวแปรที่ส่งมาไม่ถูกต้อง /// ไม่ได้ Login เข้าระบบ /// เมื่อเกิดข้อผิดพลาดในการทำงาน [HttpPost("candidate"), DisableRequestSizeLimit] [ProducesResponseType(StatusCodes.Status200OK)] [ProducesResponseType(StatusCodes.Status400BadRequest)] [ProducesResponseType(StatusCodes.Status401Unauthorized)] [ProducesResponseType(StatusCodes.Status500InternalServerError)] public async Task> ImportCandidateFileAsync([FromForm] PostDisableImportRequest req) { var tmpDir = Path.Combine(_webHostEnvironment.ContentRootPath, "tmp"); if (!Directory.Exists(tmpDir)) Directory.CreateDirectory(tmpDir); var importFile = Path.Combine(tmpDir, $"c_{DateTime.Now.ToString("ddMMyyyyHHmmss")}.xlsx"); var import_doc_id = ""; try { if (Request.Form.Files == null || Request.Form.Files.Count == 0) { return Error(GlobalMessages.NoFileToUpload); } var file = Request.Form.Files[0]; var doc = await _minioService.UploadFileAsync(file); import_doc_id = doc.Id.ToString("D"); var fileContent = (await _minioService.DownloadFileAsync(doc.Id)).FileContent; // สร้างรอบการบรรจุ โดยเอาเข้ามูลมาใส่จาก Request var imported = new PeriodExam { Year = req.Year, Round = req.Round, Name = req.Name, ImportFile = doc, CheckDisability = true, CreatedAt = DateTime.Now, CreatedUserId = UserId ?? "", CreatedFullName = FullName ?? "System Administrator", LastUpdatedAt = DateTime.Now, LastUpdateUserId = UserId ?? "", LastUpdateFullName = FullName ?? "System Administrator", ImportHostories = new List { new DisableImportHistory { Description = "นำเข้าข้อมูลผู้สมัครสอบคัดเลือกผู้พิการ", CreatedAt = DateTime.Now, CreatedUserId = UserId ?? "", CreatedFullName = FullName ?? "System Administrator", LastUpdatedAt = DateTime.Now, LastUpdateUserId = UserId ?? "", LastUpdateFullName = FullName ?? "System Administrator", } } }; await _context.PeriodExams.AddAsync(imported); // import datafile System.IO.File.WriteAllBytes(importFile, fileContent); using (var c_package = new ExcelPackage(new FileInfo(importFile))) { // loop from sheet2 to end for (int i = 0; i < c_package.Workbook.Worksheets.Count; i++) { var workSheet = c_package.Workbook.Worksheets[i]; var totalRows = workSheet.Dimension.Rows; var cols = workSheet.GetHeaderColumns(); int row = 2; while (row <= totalRows) { var cell1 = workSheet?.Cells[row, 1]?.GetValue(); if (cell1 == "" || cell1 == null) break; var r = new Recurit.Exam.Service.Models.Disables.Disable(); r.ExamId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ExamID)]?.GetValue(); r.CitizenId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PersonalID)]?.GetValue(); r.Prefix = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Prefix)]?.GetValue(); r.FirstName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.FirstName)]?.GetValue(); r.LastName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.LastName)]?.GetValue(); r.Gendor = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Gender)]?.GetValue(); r.National = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.National)]?.GetValue().IsNull(""); r.Race = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Race)]?.GetValue().IsNull(""); r.Religion = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Religion)]?.GetValue().IsNull(""); r.DateOfBirth = Convert.ToDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.DateOfBirth)]?.GetValue().ToDateTime(DateTimeFormat.Ymd, "-")); r.Marry = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Marry)]?.GetValue(); r.Isspecial = "N"; r.CitizenCardIssuer = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PersonalCardIssue)]?.GetValue(); r.CitizenCardExpireDate = Convert.ToDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PersonalCardExpireDate)]?.GetValue().ToDateTime(DateTimeFormat.Ymd, "-")); r.ApplyDate = (DateTime)workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ApplyDate)]?.GetValue(); r.PositionName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PositionName)]?.GetValue().IsNull(""); r.PositionType = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PositionType)]?.GetValue().IsNull(""); r.PositionLevel = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PositionLevel)]?.GetValue().IsNull(""); // address r.Addresses.Add(new DisableAddress() { Address = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Address)]?.GetValue() ?? "", Moo = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Moo)]?.GetValue() ?? "", Soi = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Soi)]?.GetValue() ?? "", Road = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Road)]?.GetValue() ?? "", District = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.District)]?.GetValue() ?? "", Amphur = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Amphur)]?.GetValue() ?? "", Province = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Province)]?.GetValue() ?? "", ZipCode = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ZipCode)]?.GetValue() ?? "", Telephone = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Telephone)]?.GetValue() ?? "", Mobile = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Mobile)]?.GetValue() ?? "", Address1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Address1)]?.GetValue() ?? "", Moo1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Moo1)]?.GetValue() ?? "", Soi1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Soi1)]?.GetValue() ?? "", Road1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Road1)]?.GetValue() ?? "", District1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.District1)]?.GetValue() ?? "", Amphur1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Amphur1)]?.GetValue() ?? "", Province1 = "", ZipCode1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ZipCode1)]?.GetValue() ?? "", CreatedAt = DateTime.Now, CreatedUserId = UserId ?? "", CreatedFullName = FullName ?? "System Administrator", LastUpdatedAt = DateTime.Now, LastUpdateUserId = UserId ?? "", LastUpdateFullName = FullName ?? "System Administrator", }); // payment r.Payments.Add(new DisablePayment() { PaymentId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PaymentID)]?.GetValue() ?? "", CompanyCode = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CompanyCode)]?.GetValue() ?? "", TextFile = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TextFile)]?.GetValue() ?? "", BankCode = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.BankCode)]?.GetValue() ?? "", AccountNumber = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.AccouontNumer)]?.GetValue() ?? "", TransDate = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TransDate)]?.GetValue() ?? "", TransTime = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TransTime)]?.GetValue() ?? "", CustomerName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CustomerName)]?.GetValue() ?? "", RefNo1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.RefNo1)]?.GetValue() ?? "", TermBranch = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TermBranch)]?.GetValue() ?? "", TellerId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TellerID)]?.GetValue() ?? "", CreditDebit = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CreditDebit)]?.GetValue() ?? "", PaymentType = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Type)]?.GetValue(), ChequeNo = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ChequeNo)]?.GetValue() ?? "", Amount = (decimal)workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Amount)]?.GetValue(), ChqueBankCode = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ChqBankCode)]?.GetValue() ?? "", CreatedAt = DateTime.Now, CreatedUserId = UserId ?? "", CreatedFullName = FullName ?? "System Administrator", LastUpdatedAt = DateTime.Now, LastUpdateUserId = UserId ?? "", LastUpdateFullName = FullName ?? "System Administrator", }); // occupation r.Occupations.Add(new DisableOccupation() { Occupation = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Occupation)]?.GetValue() ?? "", Position = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Position)]?.GetValue() ?? "", Workplace = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Workplace)]?.GetValue() ?? "", Telephone = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.WorkplaceTelephone)]?.GetValue() ?? "", WorkAge = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.WorkAge)]?.GetValue() ?? "", CreatedAt = DateTime.Now, CreatedUserId = UserId ?? "", CreatedFullName = FullName ?? "System Administrator", LastUpdatedAt = DateTime.Now, LastUpdateUserId = UserId ?? "", LastUpdateFullName = FullName ?? "System Administrator", }); // certificate r.Certificates.Add(new DisableCertificate() { CertificateNo = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateNo)]?.GetValue() ?? "", Description = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateDesc)]?.GetValue() ?? "", IssueDate = Convert.ToDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateIssueDate)]?.GetValue().ToDateTime(DateTimeFormat.Ymd, "-")), ExpiredDate = Convert.ToDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateExpireDate)]?.GetValue().ToDateTime(DateTimeFormat.Ymd, "-")), CreatedAt = DateTime.Now, CreatedUserId = UserId ?? "", CreatedFullName = FullName ?? "System Administrator", LastUpdatedAt = DateTime.Now, LastUpdateUserId = UserId ?? "", LastUpdateFullName = FullName ?? "System Administrator", }); r.Educations.Add(new DisableEducation() { Degree = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Degree)]?.GetValue() ?? "", Major = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Major)]?.GetValue() ?? "", MajorGroupId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.MajorGroupID)]?.GetValue() ?? "", MajorGroupName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.MajorGroupName)]?.GetValue() ?? "", University = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.University)]?.GetValue() ?? "", GPA = (double)workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.GPA)]?.GetValue(), Specialist = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.SpecialList)]?.GetValue() ?? "", HighDegree = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.HighDegree)]?.GetValue() ?? "", BachelorDate = Convert.ToDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.BachelorDate)]?.GetValue().ToDateTime(DateTimeFormat.Ymd, "-")), CreatedAt = DateTime.Now, CreatedUserId = UserId ?? "", CreatedFullName = FullName ?? "System Administrator", LastUpdatedAt = DateTime.Now, LastUpdateUserId = UserId ?? "", LastUpdateFullName = FullName ?? "System Administrator", }); r.PeriodExam = imported; _context.Disables.Add(r); //imported.Disables.Add(r); row++; } } } // finally save to database _context.SaveChanges(); return Success(); } catch (Exception ex) { await _minioService.DeleteFileAsync(Guid.Parse(import_doc_id)); return Error(ex); } finally { if (System.IO.File.Exists(importFile)) System.IO.File.Delete(importFile); } } /// /// ลบข้อมูลนำข้อมูลผู้สมัครสอบคัดเลือกผู้พิการ /// /// รหัสรอบการสอบคัดเลือกผู้พิการ /// /// เมื่อทำนำเข้าข้อมูลสำเร็จ /// ค่าตัวแปรที่ส่งมาไม่ถูกต้อง /// ไม่ได้ Login เข้าระบบ /// เมื่อเกิดข้อผิดพลาดในการทำงาน [HttpDelete("candidate/{id:length(36)}")] [ProducesResponseType(StatusCodes.Status200OK)] [ProducesResponseType(StatusCodes.Status400BadRequest)] [ProducesResponseType(StatusCodes.Status401Unauthorized)] [ProducesResponseType(StatusCodes.Status500InternalServerError)] public async Task> DeleteCandidateFileAsync(Guid id) { try { var data = await _context.PeriodExams.AsQueryable() .Where(x => x.CheckDisability == true) .Include(x => x.ImportHostories) .Include(x => x.ImportFile) //.Include(x => x.Disables) //.ThenInclude(x => x.Addresses) //.Include(x => x.Disables) //.ThenInclude(x => x.Occupations) //.Include(x => x.Disables) //.ThenInclude(x => x.Certificates) //.Include(x => x.Disables) //.ThenInclude(x => x.Educations) //.Include(x => x.Disables) //.ThenInclude(x => x.Payments) //.Include(x => x.Disables) //.ThenInclude(x => x.Documents) //.ThenInclude(x => x.DocumentFile) .Include(x => x.ScoreImport) .ThenInclude(x => x.ImportFile) .Include(x => x.ScoreImport) .ThenInclude(x => x.Scores) .FirstOrDefaultAsync(x => x.Id == id); if (data == null) return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound); var rec_import_id = data.ImportFile.Id.ToString("D"); var score_import_id = data.ScoreImport != null ? data.ScoreImport.ImportFile.Id.ToString("D") : "-"; _context.PeriodExams.Remove(data); await _context.SaveChangesAsync(); // delete upload candidate file await _minioService.DeleteFileAsync(Guid.Parse(rec_import_id)); // delete score file if (score_import_id != "-") await _minioService.DeleteFileAsync(Guid.Parse(score_import_id)); return Success(); } catch (Exception ex) { return Error(ex); } } /// /// แสดงประวัติการนำเข้าข้อมูลการสอบคัดเลือกผู้พิการ /// /// รหัสรอบการสอบคัดเลือกผู้พิการ /// /// เมื่อทำนำเข้าข้อมูลสำเร็จ /// ค่าตัวแปรที่ส่งมาไม่ถูกต้อง /// ไม่ได้ Login เข้าระบบ /// เมื่อเกิดข้อผิดพลาดในการทำงาน [HttpGet("history/{id:length(36)}")] [ProducesResponseType(StatusCodes.Status200OK)] [ProducesResponseType(StatusCodes.Status400BadRequest)] [ProducesResponseType(StatusCodes.Status401Unauthorized)] [ProducesResponseType(StatusCodes.Status500InternalServerError)] public async Task> GetImportHistoryAsync(Guid id) { try { var getPermission = await _permission.GetPermissionAPIAsync("GET", "SYS_EXAM_SELECT_PERIOD_DIS"); var jsonData = JsonConvert.DeserializeObject(getPermission); if (jsonData["status"]?.ToString() != "200") { return Error(jsonData["message"]?.ToString(), StatusCodes.Status403Forbidden); } var data = await _context.DisableImportHistories.AsQueryable() .Include(x => x.PeriodExam) .Where(x => x.PeriodExam.Id == id) .Where(x => x.PeriodExam.CheckDisability == true) .OrderByDescending(x => x.CreatedAt) .ToListAsync(); return Success(data); } catch (Exception ex) { return Error(ex); } } /// /// นำเข้ารายชื่อผู้สมัครสอบคัดเลือกผู้พิการ (ข้อมูลผู้สมัครสอบ) /// /// รหัสรอบการสอบคัดเลือกผู้พิการ /// /// เมื่อทำนำเข้าข้อมูลสำเร็จ /// ค่าตัวแปรที่ส่งมาไม่ถูกต้อง /// ไม่ได้ Login เข้าระบบ /// เมื่อเกิดข้อผิดพลาดในการทำงาน [HttpPost("candidate/{id:length(36)}"), DisableRequestSizeLimit] [ProducesResponseType(StatusCodes.Status200OK)] [ProducesResponseType(StatusCodes.Status400BadRequest)] [ProducesResponseType(StatusCodes.Status401Unauthorized)] [ProducesResponseType(StatusCodes.Status500InternalServerError)] public async Task> ImportCandidateFileByIdAsync(Guid id) { var getPermission = await _permission.GetPermissionAPIAsync("CREATE", "SYS_EXAM_SELECT_PERIOD_DIS"); var jsonData = JsonConvert.DeserializeObject(getPermission); if (jsonData["status"]?.ToString() != "200") { return Error(jsonData["message"]?.ToString(), StatusCodes.Status403Forbidden); } var tmpDir = Path.Combine(_webHostEnvironment.ContentRootPath, "tmp"); if (!Directory.Exists(tmpDir)) Directory.CreateDirectory(tmpDir); var importFile = Path.Combine(tmpDir, $"c_{DateTime.Now.ToString("ddMMyyyyHHmmss")}.xlsx"); var import_doc_id = ""; try { if (Request.Form.Files == null || Request.Form.Files.Count == 0) { return Error(GlobalMessages.NoFileToUpload); } var imported = await _context.PeriodExams.AsQueryable() .Where(x => x.CheckDisability == true) .Include(x => x.ImportHostories) .Include(x => x.ImportFile) .Include(x => x.Disables) .ThenInclude(x => x.Addresses) .Include(x => x.Disables) .ThenInclude(x => x.Occupations) .Include(x => x.Disables) .ThenInclude(x => x.Certificates) .Include(x => x.Disables) .ThenInclude(x => x.Educations) .Include(x => x.Disables) .ThenInclude(x => x.Payments) .Include(x => x.Disables) .FirstOrDefaultAsync(x => x.Id == id); if (imported == null) return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound); if (imported.Disables != null) { // remove old score data _context.Disables.RemoveRange(imported.Disables); await _context.SaveChangesAsync(); } var file = Request.Form.Files[0]; var doc = await _minioService.UploadFileAsync(file); import_doc_id = doc.Id.ToString("D"); var fileContent = (await _minioService.DownloadFileAsync(doc.Id)).FileContent; // สร้างรอบการบรรจุ โดยเอาเข้ามูลมาใส่จาก Request imported.ImportHostories.Add(new DisableImportHistory { Description = "นำเข้าข้อมูลผู้สมัครสอบคัดเลือกผู้พิการ", CreatedAt = DateTime.Now, CreatedUserId = UserId ?? "", CreatedFullName = FullName ?? "System Administrator", LastUpdatedAt = DateTime.Now, LastUpdateUserId = UserId ?? "", LastUpdateFullName = FullName ?? "System Administrator", }); //await _context.DisableImports.AddAsync(imported); // import datafile System.IO.File.WriteAllBytes(importFile, fileContent); using (var c_package = new ExcelPackage(new FileInfo(importFile))) { // loop from sheet2 to end for (int i = 0; i < c_package.Workbook.Worksheets.Count; i++) { var workSheet = c_package.Workbook.Worksheets[i]; var totalRows = workSheet.Dimension.Rows; var cols = workSheet.GetHeaderColumns(); int row = 2; while (row <= totalRows) { var cell1 = workSheet?.Cells[row, 1]?.GetValue(); if (cell1 == "" || cell1 == null) break; # region old ////var r = new Recurit.Exam.Service.Models.Disables.Disable(); ////r.ExamId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ExamID)]?.GetValue(); ////r.CitizenId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PersonalID)]?.GetValue(); ////r.Prefix = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Prefix)]?.GetValue(); ////r.FirstName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.FirstName)]?.GetValue(); ////r.LastName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.LastName)]?.GetValue(); ////r.Gendor = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Gender)]?.GetValue(); ////r.National = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.National)]?.GetValue().IsNull(""); ////r.Race = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Race)]?.GetValue().IsNull(""); ////r.Religion = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Religion)]?.GetValue().IsNull(""); //////r.DateOfBirth = Convert.ToDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.DateOfBirth)]?.GetValue().ToDateTime(DateTimeFormat.Ymd, "-"));k ////r.DateOfBirth = !string.IsNullOrWhiteSpace(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.DateOfBirth)]?.GetValue()) ? _disableService.CheckDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.DateOfBirth)]?.GetValue() ?? "", "yyyy-MM-dd") : DateTime.MinValue; ////r.Marry = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Marry)]?.GetValue(); ////r.Isspecial = "N"; ////r.CitizenCardIssuer = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PersonalCardIssue)]?.GetValue(); ////r.CitizenCardExpireDate = Convert.ToDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PersonalCardExpireDate)]?.GetValue().ToDateTime(DateTimeFormat.Ymd, "-")); ////r.ApplyDate = (DateTime)workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ApplyDate)]?.GetValue(); ////r.PositionName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PositionName)]?.GetValue().IsNull(""); ////r.PositionType = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PositionType)]?.GetValue().IsNull(""); ////r.PositionLevel = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PositionLevel)]?.GetValue().IsNull(""); ////// address ////r.Addresses.Add(new DisableAddress() ////{ //// Address = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Address)]?.GetValue() ?? "", //// Moo = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Moo)]?.GetValue() ?? "", //// Soi = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Soi)]?.GetValue() ?? "", //// Road = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Road)]?.GetValue() ?? "", //// District = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.District)]?.GetValue() ?? "", //// Amphur = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Amphur)]?.GetValue() ?? "", //// Province = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Province)]?.GetValue() ?? "", //// ZipCode = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ZipCode)]?.GetValue() ?? "", //// Telephone = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Telephone)]?.GetValue() ?? "", //// Mobile = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Mobile)]?.GetValue() ?? "", //// Address1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Address1)]?.GetValue() ?? "", //// Moo1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Moo1)]?.GetValue() ?? "", //// Soi1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Soi1)]?.GetValue() ?? "", //// Road1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Road1)]?.GetValue() ?? "", //// District1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.District1)]?.GetValue() ?? "", //// Amphur1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Amphur1)]?.GetValue() ?? "", //// Province1 = "", //// ZipCode1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ZipCode1)]?.GetValue() ?? "", //// CreatedAt = DateTime.Now, //// CreatedUserId = UserId ?? "", //// CreatedFullName = FullName ?? "System Administrator", //// LastUpdatedAt = DateTime.Now, //// LastUpdateUserId = UserId ?? "", //// LastUpdateFullName = FullName ?? "System Administrator", ////}); ////// payment ////r.Payments.Add(new DisablePayment() ////{ //// PaymentId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.PaymentID)]?.GetValue() ?? "", //// CompanyCode = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CompanyCode)]?.GetValue() ?? "", //// TextFile = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TextFile)]?.GetValue() ?? "", //// BankCode = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.BankCode)]?.GetValue() ?? "", //// AccountNumber = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.AccouontNumer)]?.GetValue() ?? "", //// TransDate = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TransDate)]?.GetValue() ?? "", //// TransTime = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TransTime)]?.GetValue() ?? "", //// CustomerName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CustomerName)]?.GetValue() ?? "", //// RefNo1 = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.RefNo1)]?.GetValue() ?? "", //// TermBranch = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TermBranch)]?.GetValue() ?? "", //// TellerId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.TellerID)]?.GetValue() ?? "", //// CreditDebit = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CreditDebit)]?.GetValue() ?? "", //// PaymentType = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Type)]?.GetValue(), //// ChequeNo = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ChequeNo)]?.GetValue() ?? "", //// Amount = (decimal)workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Amount)]?.GetValue(), //// ChqueBankCode = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.ChqBankCode)]?.GetValue() ?? "", //// CreatedAt = DateTime.Now, //// CreatedUserId = UserId ?? "", //// CreatedFullName = FullName ?? "System Administrator", //// LastUpdatedAt = DateTime.Now, //// LastUpdateUserId = UserId ?? "", //// LastUpdateFullName = FullName ?? "System Administrator", ////}); ////// occupation ////r.Occupations.Add(new DisableOccupation() ////{ //// Occupation = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Occupation)]?.GetValue() ?? "", //// Position = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Position)]?.GetValue() ?? "", //// Workplace = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Workplace)]?.GetValue() ?? "", //// Telephone = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.WorkplaceTelephone)]?.GetValue() ?? "", //// WorkAge = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.WorkAge)]?.GetValue() ?? "", //// CreatedAt = DateTime.Now, //// CreatedUserId = UserId ?? "", //// CreatedFullName = FullName ?? "System Administrator", //// LastUpdatedAt = DateTime.Now, //// LastUpdateUserId = UserId ?? "", //// LastUpdateFullName = FullName ?? "System Administrator", ////}); ////// certificate ////r.Certificates.Add(new DisableCertificate() ////{ //// CertificateNo = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateNo)]?.GetValue() ?? "", //// Description = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateDesc)]?.GetValue() ?? "", //// IssueDate = !string.IsNullOrWhiteSpace(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateIssueDate)]?.GetValue()) ? _disableService.CheckDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateIssueDate)]?.GetValue() ?? "", "yyyy-MM-dd") : DateTime.MinValue, //// ExpiredDate = !string.IsNullOrWhiteSpace(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateExpireDate)]?.GetValue()) ? _disableService.CheckDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.CertificateExpireDate)]?.GetValue() ?? "", "yyyy-MM-dd") : DateTime.MinValue, //// CreatedAt = DateTime.Now, //// CreatedUserId = UserId ?? "", //// CreatedFullName = FullName ?? "System Administrator", //// LastUpdatedAt = DateTime.Now, //// LastUpdateUserId = UserId ?? "", //// LastUpdateFullName = FullName ?? "System Administrator", ////}); ////r.Educations.Add(new DisableEducation() ////{ //// Degree = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Degree)]?.GetValue() ?? "", //// Major = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.Major)]?.GetValue() ?? "", //// MajorGroupId = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.MajorGroupID)]?.GetValue() ?? "", //// MajorGroupName = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.MajorGroupName)]?.GetValue() ?? "", //// University = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.University)]?.GetValue() ?? "", //// GPA = (double)workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.GPA)]?.GetValue(), //// Specialist = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.SpecialList)]?.GetValue() ?? "", //// HighDegree = workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.HighDegree)]?.GetValue() ?? "", //// BachelorDate = !string.IsNullOrWhiteSpace(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.BachelorDate)]?.GetValue()) ? _disableService.CheckDateTime(workSheet?.Cells[row, GetColumnIndex(cols, CandidateFileHeader.BachelorDate)]?.GetValue() ?? "", "yyyy-MM-dd") : DateTime.MinValue, //// CreatedAt = DateTime.Now, //// CreatedUserId = UserId ?? "", //// CreatedFullName = FullName ?? "System Administrator", //// LastUpdatedAt = DateTime.Now, //// LastUpdateUserId = UserId ?? "", //// LastUpdateFullName = FullName ?? "System Administrator", ////}); # endregion var r = new Models.Disables.Disable(); r.ExamId = workSheet?.Cells[row, 1]?.GetValue() ?? ""; r.PositionName = workSheet?.Cells[row, 3]?.GetValue() ?? ""; r.HddPosition = workSheet?.Cells[row, 4]?.GetValue() ?? ""; r.Prefix = workSheet?.Cells[row, 5]?.GetValue() == "อื่น ๆ" ? workSheet?.Cells[row, 6]?.GetValue() ?? "" : workSheet?.Cells[row, 5]?.GetValue() ?? ""; r.FirstName = workSheet?.Cells[row, 7]?.GetValue() ?? ""; r.LastName = workSheet?.Cells[row, 8]?.GetValue() ?? ""; r.Gendor = workSheet?.Cells[row, 98]?.GetValue() ?? ""; r.National = workSheet?.Cells[row, 9]?.GetValue() ?? ""; r.Race = ""; r.Religion = workSheet?.Cells[row, 10]?.GetValue() ?? ""; r.DateOfBirth = !string.IsNullOrWhiteSpace(workSheet?.Cells[row, 11]?.GetValue()) ? _disableService.CheckDateTime(workSheet?.Cells[row, 11]?.GetValue() ?? "", "dd/MM/yyyy") : null; r.CitizenId = workSheet?.Cells[row, 12]?.GetValue() ?? ""; r.typeTest = workSheet?.Cells[row, 13]?.GetValue() ?? ""; r.Marry = ""; r.Isspecial = "N"; r.CitizenCardIssuer = ""; r.CitizenCardExpireDate = null; r.ModifiedDate = null; r.ApplyDate = !string.IsNullOrWhiteSpace(workSheet?.Cells[row, 87]?.GetValue()) ? _disableService.CheckDateTime(workSheet?.Cells[row, 87]?.GetValue() ?? "", "dd/MM/yyyy") : null; r.PositionType = ""; r.PositionLevel = ""; r.CreatedAt = DateTime.Now; r.CreatedUserId = UserId ?? ""; r.CreatedFullName = FullName ?? "System Administrator"; r.LastUpdatedAt = DateTime.Now; r.LastUpdateUserId = UserId ?? ""; r.LastUpdateFullName = FullName ?? "System Administrator"; // education r.Educations.Add(new DisableEducation() { Degree = workSheet?.Cells[row, 18]?.GetValue() ?? "", Major = workSheet?.Cells[row, 19]?.GetValue() == "อื่น ๆ" ? workSheet?.Cells[row, 20]?.GetValue() ?? "" : workSheet?.Cells[row, 19]?.GetValue() ?? "", MajorGroupId = "", MajorGroupName = "", University = workSheet?.Cells[row, 21]?.GetValue() == "อื่น ๆ" ? workSheet?.Cells[row, 22]?.GetValue() ?? "" : workSheet?.Cells[row, 21]?.GetValue() ?? "", GPA = (double)workSheet?.Cells[row, 26]?.GetValue(), Specialist = "", HighDegree = workSheet?.Cells[row, 27]?.GetValue() ?? "", BachelorDate = !string.IsNullOrWhiteSpace(workSheet?.Cells[row, 25]?.GetValue()) ? _disableService.CheckDateTime(workSheet?.Cells[row, 25]?.GetValue() ?? "", "dd/MM/yyyy") : null, CreatedAt = DateTime.Now, CreatedUserId = UserId ?? "", CreatedFullName = FullName ?? "System Administrator", LastUpdatedAt = DateTime.Now, LastUpdateUserId = UserId ?? "", LastUpdateFullName = FullName ?? "System Administrator" }); // occupation r.Occupations.Add(new DisableOccupation() { Occupation = workSheet?.Cells[row, 33]?.GetValue() == "อื่น ๆ" ? workSheet?.Cells[row, 34]?.GetValue() ?? "" : workSheet?.Cells[row, 33]?.GetValue() ?? "", Position = workSheet?.Cells[row, 37]?.GetValue() ?? "", Workplace = $"{(workSheet?.Cells[row, 36]?.GetValue() ?? "")} {(workSheet?.Cells[row, 35]?.GetValue() ?? "")}", Telephone = "", WorkAge = "", CreatedAt = DateTime.Now, CreatedUserId = UserId ?? "", CreatedFullName = FullName ?? "System Administrator", LastUpdatedAt = DateTime.Now, LastUpdateUserId = UserId ?? "", LastUpdateFullName = FullName ?? "System Administrator" }); // address r.Addresses.Add(new DisableAddress() { Address = $"{(workSheet?.Cells[row, 49]?.GetValue() ?? "")} {(workSheet?.Cells[row, 50]?.GetValue() ?? "")}", Moo = workSheet?.Cells[row, 51]?.GetValue() ?? "", Soi = workSheet?.Cells[row, 52]?.GetValue() ?? "", Road = workSheet?.Cells[row, 53]?.GetValue() ?? "", District = workSheet?.Cells[row, 54]?.GetValue() ?? "", Amphur = workSheet?.Cells[row, 55]?.GetValue() ?? "", Province = workSheet?.Cells[row, 56]?.GetValue() ?? "", ZipCode = (workSheet?.Cells[row, 57]?.GetValue() ?? "").Trim(), Telephone = workSheet?.Cells[row, 58]?.GetValue() ?? "", Mobile = "", Address1 = $"{(workSheet?.Cells[row, 61]?.GetValue() ?? "")} {(workSheet?.Cells[row, 62]?.GetValue() ?? "")}", Moo1 = workSheet?.Cells[row, 63]?.GetValue() ?? "", Soi1 = workSheet?.Cells[row, 64]?.GetValue() ?? "", Road1 = workSheet?.Cells[row, 65]?.GetValue() ?? "", District1 = workSheet?.Cells[row, 66]?.GetValue() ?? "", Amphur1 = workSheet?.Cells[row, 67]?.GetValue() ?? "", Province1 = workSheet?.Cells[row, 68]?.GetValue() ?? "", ZipCode1 = (workSheet?.Cells[row, 69]?.GetValue() ?? "").Trim(), CreatedAt = DateTime.Now, CreatedUserId = UserId ?? "", CreatedFullName = FullName ?? "System Administrator", LastUpdatedAt = DateTime.Now, LastUpdateUserId = UserId ?? "", LastUpdateFullName = FullName ?? "System Administrator" }); // payment r.Payments.Add(new DisablePayment() { PaymentId = workSheet?.Cells[row, 104]?.GetValue() ?? "", CompanyCode = workSheet?.Cells[row, 105]?.GetValue() ?? "", TextFile = workSheet?.Cells[row, 106]?.GetValue() ?? "", BankCode = workSheet?.Cells[row, 107]?.GetValue() ?? "", AccountNumber = workSheet?.Cells[row, 108]?.GetValue() ?? "", TransDate = workSheet?.Cells[row, 109]?.GetValue() ?? "", TransTime = workSheet?.Cells[row, 110]?.GetValue() ?? "", CustomerName = workSheet?.Cells[row, 111]?.GetValue() ?? "", RefNo1 = workSheet?.Cells[row, 112]?.GetValue() ?? "", TermBranch = workSheet?.Cells[row, 113]?.GetValue() ?? "", TellerId = workSheet?.Cells[row, 114]?.GetValue() ?? "", CreditDebit = workSheet?.Cells[row, 115]?.GetValue() ?? "", PaymentType = workSheet?.Cells[row, 116]?.GetValue() ?? "", ChequeNo = workSheet?.Cells[row, 117]?.GetValue() ?? "", Amount = (decimal)workSheet?.Cells[row, 118]?.GetValue(), ChqueBankCode = workSheet?.Cells[row, 119]?.GetValue() ?? "", CreatedAt = DateTime.Now, CreatedUserId = UserId ?? "", CreatedFullName = FullName ?? "System Administrator", LastUpdatedAt = DateTime.Now, LastUpdateUserId = UserId ?? "", LastUpdateFullName = FullName ?? "System Administrator" }); /* Comment ข้อมูลใบประกอบวิชาชีพ เพราะใน template ยังไม่มีคอลัมน์ที่ระบุข้อมูลส่วนนี้ */ //// certificate //r.Certificates.Add(new DisableCertificate() //{ // CertificateNo = "",//workSheet?.Cells[row, 9999]?.GetValue() ?? "", // Description = "",//workSheet?.Cells[row, 9999]?.GetValue() ?? "", // IssueDate = DateTime.MinValue,//Convert.ToDateTime(workSheet?.Cells[row, 9999]?.GetValue().ToDateTime(DateTimeFormat.Ymd, "-")), // ExpiredDate = DateTime.MinValue,//Convert.ToDateTime(workSheet?.Cells[row, 9999]?.GetValue().ToDateTime(DateTimeFormat.Ymd, "-")), // CreatedAt = DateTime.Now, // CreatedUserId = UserId ?? "", // CreatedFullName = FullName ?? "System Administrator", // LastUpdatedAt = DateTime.Now, // LastUpdateUserId = UserId ?? "", // LastUpdateFullName = FullName ?? "System Administrator" //}); r.PeriodExam = imported; _context.Disables.Add(r); //imported.Disables.Add(r); row++; } } } // finally save to database _context.SaveChanges(); return Success(); } catch (Exception ex) { await _minioService.DeleteFileAsync(Guid.Parse(import_doc_id)); return Error(ex); } finally { if (System.IO.File.Exists(importFile)) System.IO.File.Delete(importFile); } } #endregion #region " Score File " /// /// นำเข้าผลคะแนนสอบคัดเลือกผู้พิการ (บัญชีรวมคะแนน) /// /// รหัสรอบการสอบคัดเลือกผู้พิการ /// /// เมื่อทำนำเข้าข้อมูลสำเร็จ /// ค่าตัวแปรที่ส่งมาไม่ถูกต้อง /// ไม่ได้ Login เข้าระบบ /// เมื่อเกิดข้อผิดพลาดในการทำงาน [HttpPost("score/{id:length(36)}"), DisableRequestSizeLimit] [ProducesResponseType(StatusCodes.Status200OK)] [ProducesResponseType(StatusCodes.Status400BadRequest)] [ProducesResponseType(StatusCodes.Status401Unauthorized)] [ProducesResponseType(StatusCodes.Status500InternalServerError)] public async Task> ImportScoreFileAsync(Guid id) { var getPermission = await _permission.GetPermissionAPIAsync("CREATE", "SYS_EXAM_SELECT_PERIOD_DIS"); var jsonData = JsonConvert.DeserializeObject(getPermission); if (jsonData["status"]?.ToString() != "200") { return Error(jsonData["message"]?.ToString(), StatusCodes.Status403Forbidden); } var tmpDir = Path.Combine(_webHostEnvironment.ContentRootPath, "tmp"); if (!Directory.Exists(tmpDir)) Directory.CreateDirectory(tmpDir); var importFile = Path.Combine(tmpDir, $"s_{DateTime.Now.ToString("ddMMyyyyHHmmss")}.xlsx"); var import_doc_id = ""; try { if (Request.Form.Files == null || Request.Form.Files.Count == 0) { return Error(GlobalMessages.NoFileToUpload); } var rec_import = await _context.PeriodExams.AsQueryable() .Where(x => x.CheckDisability == true) .Include(x => x.ScoreImport) .ThenInclude(x => x.Scores) .Include(x => x.ImportHostories) .FirstOrDefaultAsync(x => x.Id == id); if (rec_import == null) return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound); if (rec_import.ScoreImport != null) { // remove old score data if (rec_import.ScoreImport.Scores != null) { _context.DisableScores.RemoveRange(rec_import.ScoreImport.Scores); await _context.SaveChangesAsync(); } } var file = Request.Form.Files[0]; var doc = await _minioService.UploadFileAsync(file); import_doc_id = doc.Id.ToString("D"); var fileContent = (await _minioService.DownloadFileAsync(doc.Id)).FileContent; // create import history rec_import.ImportHostories.Add(new DisableImportHistory { Description = "นำเข้าข้อมูลผลคะแนนสอบ", CreatedAt = DateTime.Now, CreatedUserId = UserId ?? "", CreatedFullName = FullName ?? "System Administrator", LastUpdatedAt = DateTime.Now, LastUpdateUserId = UserId ?? "", LastUpdateFullName = FullName ?? "System Administrator", }); // create new file import var imported = new ScoreImport { Year = rec_import.Year, ImportFile = doc, CreatedAt = DateTime.Now, CreatedUserId = UserId ?? "", CreatedFullName = FullName ?? "System Administrator", LastUpdatedAt = DateTime.Now, LastUpdateUserId = UserId ?? "", LastUpdateFullName = FullName ?? "System Administrator", Scores = new List() }; // preload disables ทั้งหมดของครั้งเดียว และ Group ExamId เอาเฉพาะ key ที่ไม่ซ้ำ var recruitsDict = await _context.Disables .Where(x => x.PeriodExam.Id == rec_import.Id) .GroupBy(x => x.ExamId) .Where(g => g.Count() == 1) .Select(g => g.First()) .ToDictionaryAsync(x => x.ExamId, x => x); // import datafile System.IO.File.WriteAllBytes(importFile, fileContent); using (var c_package = new ExcelPackage(new FileInfo(importFile))) { // loop from sheet2 to end for (int i = 0; i < c_package.Workbook.Worksheets.Count; i++) { var workSheet = c_package.Workbook.Worksheets[i]; //var totalRows = workSheet.Dimension.Rows; var cols = workSheet.GetHeaderColumns(); int row = 8; var endRow = workSheet.Dimension.End.Row; // แถวสุดท้ายที่มีข้อมูล while (row <= endRow) { var cell1 = workSheet?.Cells[row, 1]?.GetValue(); if (cell1 == "" || cell1 == null) break; var r = new DisableScore(); #region นำเข้าผลคะแนนสอบคัดเลือกผู้พิการ old ////r.ExamId = workSheet?.Cells[row, 2]?.GetValue(); ////r.FullA = (int)workSheet?.Cells[7, 7]?.GetValue().Replace("(", "").Replace(")", "").Replace("คะแนน", "").Trim().ToInteger(); ////r.SumA = workSheet?.Cells[row, 7]?.GetValue() == "ขส." ? 0 : (int)workSheet?.Cells[row, 7]?.GetValue().Replace(".00", "").ToInteger(); ////r.PercentageA = workSheet?.Cells[row, 8]?.GetValue() == "ขส." ? 0.0 : (double)workSheet?.Cells[row, 8]?.GetValue(); ////r.AStatus = workSheet?.Cells[row, 9]?.GetValue(); ////r.FullB = (int)workSheet?.Cells[7, 12]?.GetValue().Replace("(", "").Replace(")", "").Replace("คะแนน", "").Trim().ToInteger(); ////r.SumB = workSheet?.Cells[row, 12]?.GetValue() == "ขส." ? 0 : (int)workSheet?.Cells[row, 12]?.GetValue().Replace(".00", "").ToInteger(); ////r.PercentageB = workSheet?.Cells[row, 13]?.GetValue() == "ขส." ? 0.0 : (double)workSheet?.Cells[row, 13]?.GetValue(); ////r.BStatus = workSheet?.Cells[row, 14]?.GetValue(); ////r.SumAB = workSheet?.Cells[row, 15]?.GetValue() == "ขส." ? 0 : (int)workSheet?.Cells[row, 15]?.GetValue().Replace(".00", "").ToInteger(); ////r.ABStatus = workSheet?.Cells[row, 17]?.GetValue(); ////r.FullC = (int)workSheet?.Cells[7, 20]?.GetValue().Replace("(", "").Replace(")", "").Replace("คะแนน", "").Trim().ToInteger(); ////r.SumC = workSheet?.Cells[row, 20]?.GetValue() == "ขส." ? 0 : (int)workSheet?.Cells[row, 20]?.GetValue().Replace(".00", "").ToInteger(); ////r.PercentageC = workSheet?.Cells[row, 21]?.GetValue() == "ขส." ? 0.0 : (double)workSheet?.Cells[row, 21]?.GetValue(); ////r.CStatus = workSheet?.Cells[row, 22]?.GetValue(); ////r.ExamStatus = workSheet?.Cells[row, 24]?.GetValue(); ////r.Number = workSheet?.Cells[row, 25]?.GetValue() ?? ""; ////r.Major = workSheet.Name; #endregion r.ExamId = workSheet?.Cells[row, 2]?.GetValue(); // ใช้ dictionary lookup แทน query DB ทีละรอบ if (!string.IsNullOrEmpty(r.ExamId) && recruitsDict.TryGetValue(r.ExamId, out var recruit)) { r.CitizenId = workSheet?.Cells[row, 3]?.GetValue()?.Trim(); // ภาคความรู้ความสามารถที่ใช้เฉพาะตำแหน่ง r.FullA = 200; r.SumA = string.IsNullOrWhiteSpace(workSheet?.Cells[row, 5]?.GetValue()) ? 0.00 : Math.Round(workSheet.Cells[row, 5].GetValue(), 2); r.PercentageA = string.IsNullOrWhiteSpace(workSheet?.Cells[row, 6]?.GetValue()) ? 0.00 : Math.Round(workSheet.Cells[row, 6].GetValue(), 2); r.AStatus = string.IsNullOrWhiteSpace(workSheet?.Cells[row, 7]?.GetValue()) ? "" : workSheet?.Cells[row, 7]?.GetValue(); r.SumAB = string.IsNullOrWhiteSpace(workSheet?.Cells[row, 5]?.GetValue()) ? 0.00 : Math.Round(workSheet.Cells[row, 5].GetValue(), 2); r.ABStatus = string.IsNullOrWhiteSpace(workSheet?.Cells[row, 7]?.GetValue()) ? "" : workSheet?.Cells[row, 7]?.GetValue(); // ภาคความเหมาะสมกับตำแหน่ง r.FullC = 50; r.SumC = string.IsNullOrWhiteSpace(workSheet?.Cells[row, 8]?.GetValue()) ? 0.00 : Math.Round(workSheet.Cells[row, 8].GetValue(), 2); r.FullD = 50; r.SumD = string.IsNullOrWhiteSpace(workSheet?.Cells[row, 9]?.GetValue()) ? 0.00 : Math.Round(workSheet.Cells[row, 9].GetValue(), 2); r.SumCD = string.IsNullOrWhiteSpace(workSheet?.Cells[row, 10]?.GetValue()) ? 0.00 : Math.Round(workSheet.Cells[row, 10].GetValue(), 2); r.PercentageC = string.IsNullOrWhiteSpace(workSheet?.Cells[row, 11]?.GetValue()) ? 0.00 : Math.Round(workSheet.Cells[row, 11].GetValue(), 2); r.CStatus = string.IsNullOrWhiteSpace(workSheet?.Cells[row, 12]?.GetValue()) ? "" : workSheet?.Cells[row, 12]?.GetValue(); r.FullScore = 300; r.TotalScore = string.IsNullOrWhiteSpace(workSheet?.Cells[row, 13]?.GetValue()) ? 0.00 : Math.Round(workSheet.Cells[row, 13].GetValue(), 2); var examStatusCol7 = workSheet?.Cells[row, 7]?.GetValue()?.Trim(); var examStatusCol14 = workSheet?.Cells[row, 14]?.GetValue()?.Trim(); r.ExamStatus = examStatusCol7 == "ขาดสอบ" ? "ขส." : examStatusCol14 == "ได้" ? "ผ่าน" : examStatusCol14 == "ตก" ? "ไม่ผ่าน" : "-"; r.RemarkScore = string.IsNullOrWhiteSpace(workSheet?.Cells[row, 15]?.GetValue()) ? string.Empty : workSheet?.Cells[row, 15]?.GetValue(); var examAttr = workSheet?.Cells[row, 16]?.GetValue()?.Trim(); r.ExamAttribute = examAttr == "ผ่าน" ? "มีคุณสมบัติ" : examAttr == "ไม่ผ่าน" ? "ไม่มีคุณสมบัติ" : ""; r.Major = workSheet.Name; r.CreatedAt = DateTime.Now; r.CreatedUserId = UserId ?? ""; r.CreatedFullName = FullName ?? "System Administrator"; r.LastUpdatedAt = DateTime.Now; r.LastUpdateUserId = UserId ?? ""; r.LastUpdateFullName = FullName ?? "System Administrator"; imported.Scores.Add(r); } row++; } // end of sheet loop } // end of all file loop } // finally save to database rec_import.ScoreImport = imported; await _context.SaveChangesAsync(); return Success(); } catch (Exception ex) { await _minioService.DeleteFileAsync(Guid.Parse(import_doc_id)); return Error(ex); } finally { if (System.IO.File.Exists(importFile)) System.IO.File.Delete(importFile); } } #endregion #region " Result File " /// /// นำเข้าผลการสอบคัดเลือกผู้พิการ (ผลการสอบ) /// /// รหัสรอบการสอบคัดเลือกผู้พิการ /// /// เมื่อทำนำเข้าข้อมูลสำเร็จ /// ค่าตัวแปรที่ส่งมาไม่ถูกต้อง /// ไม่ได้ Login เข้าระบบ /// เมื่อเกิดข้อผิดพลาดในการทำงาน [HttpPost("result/{id:length(36)}"), DisableRequestSizeLimit] [ProducesResponseType(StatusCodes.Status200OK)] [ProducesResponseType(StatusCodes.Status400BadRequest)] [ProducesResponseType(StatusCodes.Status401Unauthorized)] [ProducesResponseType(StatusCodes.Status500InternalServerError)] public async Task> ImportResultFileAsync(Guid id) { var getPermission = await _permission.GetPermissionAPIAsync("CREATE", "SYS_EXAM_SELECT_PERIOD_DIS"); var jsonData = JsonConvert.DeserializeObject(getPermission); if (jsonData["status"]?.ToString() != "200") { return Error(jsonData["message"]?.ToString(), StatusCodes.Status403Forbidden); } try { if (Request.Form.Files == null || Request.Form.Files.Count == 0) { return Error(GlobalMessages.NoFileToUpload); } var rec_import = await _context.PeriodExams.AsQueryable() .Include(x => x.ScoreImport) .ThenInclude(x => x.Scores) .FirstOrDefaultAsync(x => x.Id == id); if (rec_import == null) return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound); // update ฟิลด์ลำดับที่สอบได้และหมายเหตุจากลำดับที่สอบได้อันเก่าก่อน if (rec_import.ScoreImport != null && rec_import.ScoreImport.Scores != null) { var _oldScores = rec_import.ScoreImport.Scores .Where(x => !string.IsNullOrEmpty(x.Number)) .ToList(); if (_oldScores.Count > 0) { foreach (var x in _oldScores) { x.Number = string.Empty; x.RemarkExamOrder = string.Empty; } await _context.SaveChangesAsync(); } } // create import history rec_import.ImportHostories.Add(new DisableImportHistory { Description = "นำเข้าข้อมูลผลการสอบ", CreatedAt = DateTime.Now, CreatedUserId = UserId ?? "", CreatedFullName = FullName ?? "System Administrator", LastUpdatedAt = DateTime.Now, LastUpdateUserId = UserId ?? "", LastUpdateFullName = FullName ?? "System Administrator", }); // preload score var score = rec_import.ScoreImport.Scores .Where(s => !string.IsNullOrEmpty(s.ExamId)) .GroupBy(x => x.ExamId) .Where(g => g.Count() == 1) .Select(g => g.First()) .ToDictionary(s => s.ExamId, s => s); // ถ้าไม่มีผลคะแนนสอบคัดเลือกผู้พิการให้จบการทำงาน if (score.Count == 0) return Success(); var file = Request.Form.Files[0]; using (var stream = file.OpenReadStream()) using (var c_package = new ExcelPackage(stream)) { foreach (var workSheet in c_package.Workbook.Worksheets) { //var totalRows = workSheet.Dimension.Rows; int row = 7; // เริ่มที่ row 7 ตามตัวอย่างไฟล์ var endRow = workSheet.Dimension.End.Row; // แถวสุดท้ายที่มีข้อมูล while (row <= endRow) { var examId = workSheet?.Cells[row, 2]?.GetValue(); if (string.IsNullOrWhiteSpace(examId)) { row++; continue; } if (score.TryGetValue(examId, out var existingScore)) { existingScore.Number = workSheet?.Cells[row, 1]?.GetValue(); existingScore.RemarkExamOrder = workSheet?.Cells[row, 4]?.GetValue() ?? string.Empty; existingScore.LastUpdatedAt = DateTime.Now; existingScore.LastUpdateUserId = UserId ?? ""; existingScore.LastUpdateFullName = FullName ?? "System Administrator"; } row++; } } } await _context.SaveChangesAsync(); return Success(); } catch (Exception ex) { return Error(ex); } } #endregion #region " Exam Information " /// /// แสดงข้อมูลสำหรับหน้าจอ : รายการข้อมูลผู้สมัครสอบ /// [HttpGet("exam")] public async Task> GetExamResultAsync() { try { var data = await _context.PeriodExams.AsQueryable() .Where(x => x.CheckDisability == true) .OrderByDescending(x => x.Year) .ThenByDescending(x => x.Round) .Select(x => new { x.Id, ExamYear = x.Year == null ? 0 : x.Year.Value.ToThaiYear(), ExamOrder = x.Round, Description = x.Name, }) .ToListAsync(); var result = new List(); foreach (var d in data) { result.Add(new { ExamYear = d.ExamYear, Decription = d.Description, ExamCount = await GetExamCount(d.Id), PassCount = await GetPassExamCount(d.Id), NotPassCount = (await GetExamCount(d.Id) - await GetPassExamCount(d.Id)) }); } return Success(result); } catch (Exception ex) { return Error(ex); } } //[HttpPost("exam/{id:length(36)}")] //public ActionResult GetExamResultById([FromBody] DisableExamRequest req, Guid id) //{ // try // { // var periodExam = _context.PeriodExams.AsQueryable() // .Where(x => x.CheckDisability == true) // .FirstOrDefault(x => x.Id == id); // if (periodExam == null) // return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound); // var p_Id = new MySqlParameter("@id", id); // var data = new List(); // using (var cmd = _context.Database.GetDbConnection().CreateCommand()) // { // cmd.CommandTimeout = 0; // var sb = new StringBuilder(); // cmd.Parameters.Clear(); // sb.Clear(); // sb.Append(" SELECT * "); // sb.Append(" FROM exam_info "); // sb.Append(" WHERE disable_import_id = @id "); // cmd.Parameters.Add(p_Id); // if (req.ExamAttribute != null && req.ExamAttribute != "") // { // sb.Append(" AND examAttribute = @a "); // cmd.Parameters.Add(new MySqlParameter("@a", req.ExamAttribute)); // } // if (req.ExamResult != null && req.ExamResult != "") // { // sb.Append(" AND result = @r "); // cmd.Parameters.Add(new MySqlParameter("@r", req.ExamResult)); // } // sb.Append(" ORDER BY examID "); // sb.Append(" LIMIT @PageSize OFFSET @Offset "); // cmd.Parameters.Add(new MySqlParameter("@PageSize", req.PageSize)); // cmd.Parameters.Add(new MySqlParameter("@Offset", ((req.Page - 1) * req.PageSize))); // cmd.CommandText = sb.ToString(); // _context.Database.OpenConnection(); // var da = new MySqlDataAdapter(cmd as MySqlCommand); // var dt = new DataTable(); // da.Fill(dt); // foreach (DataRow dr in dt.Rows) // { // data.Add(new // { // examID = dr["examID"].ToString(), // profileID = dr["profileID"].ToString(), // prefix = dr["prefix"].ToString(), // fullName = dr["fullName"].ToString(), // dateOfBirth = dr["dateofbirth"] == null || Convert.ToDateTime(dr["dateofbirth"]) == DateTime.MinValue ? "" : Convert.ToDateTime(dr["dateofbirth"]).ToThaiShortDate(), // gender = dr["gender"].ToString(), // degree = dr["degree"].ToString(), // major = dr["major"].ToString(), // majorgroup = dr["majorgroup"].ToString(), // certificateNo = dr["certificateno"].ToString(), // certificateIssueDate = dr["certificateIssueDate"] == null || Convert.ToDateTime(dr["certificateIssueDate"]) == DateTime.MinValue ? "" : Convert.ToDateTime(dr["certificateIssueDate"]).ToThaiShortDate(), // ExamScore = dr["score"] == null ? 0 : dr["score"].ToString().ToInteger(), // ExamResult = dr["result"].ToString(), // ExamAttribute = dr["examAttribute"].ToString(), // Remark = dr["remark"].ToString(), // IsSpecial = dr["isspecial"].ToString(), // applyDate = dr["applydate"] == null || Convert.ToDateTime(dr["applydate"]) == DateTime.MinValue ? "" : Convert.ToDateTime(dr["applydate"]).ToThaiShortDate(), // university = dr["university"].ToString(), // position_name = dr["position_name"].ToString(), // hddPosition = dr["hddPosition"].ToString(), // typeTest = dr["typeTest"].ToString(), // position_level = dr["position_level"].ToString(), // position_type = dr["position_type"].ToString(), // exam_name = dr["exam_name"].ToString(), // exam_order = dr["exam_order"].ToString(), // score_year = Convert.ToInt32(dr["score_year"]).ToThaiYear().ToString(), // }); // } // } // //if (data != null && data.Count > 0) // // data = data.Skip((req.Page - 1) * req.PageSize).Take(req.PageSize).ToList(); // dynamic header = null; // using (var cmd = _context.Database.GetDbConnection().CreateCommand()) // { // var sb = new StringBuilder(); // cmd.CommandTimeout = 0; // cmd.Parameters.Clear(); // sb.Clear(); // sb.Append(" SELECT * "); // sb.Append(" FROM sum_exam_info "); // sb.Append(" WHERE disable_import_id = @id "); // cmd.Parameters.Add(p_Id); // cmd.CommandText = sb.ToString(); // _context.Database.OpenConnection(); // MySqlDataAdapter da = new MySqlDataAdapter(cmd as MySqlCommand); // DataTable dt = new DataTable(); // da.Fill(dt); // if (dt.Rows.Count == 0) // { // header = new // { // count = 0, // pass = 0, // notpass = 0, // missed_exam = 0, // other = 0 // }; // } // else // { // var dr = dt.Rows[0]; // header = new // { // count = dr["count"].ToString().ToInteger(), // pass = dr["pass"].ToString().ToInteger(), // notpass = dr["notpass"].ToString().ToInteger(), // missed_exam = dr["missed_exam"].ToString().ToInteger(), // other = dr["other"].ToString().ToInteger() // }; // } // } // return Success(new // { // data = data, // header = header, // round = periodExam.Round, // year = periodExam.Year // }); // } // catch (Exception ex) // { // return Error(ex); // } //} [HttpPost("exam/{id:length(36)}")] public async Task> GetNewExamResultById([FromBody] DisableExamRequest req, Guid id) { try { var periodExam = _context.PeriodExams.AsQueryable() .Where(x => x.CheckDisability == true) .FirstOrDefault(x => x.Id == id); if (periodExam == null) return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound); var query = _context.Disables .Include(x => x.PeriodExam) .Include(x => x.Educations) .Include(x => x.Certificates) .OrderBy(x => x.ExamId) .Where(x => x.PeriodExam != null && x.PeriodExam.Id == id); var keywordParam = req.keyword?.Trim(); if (!string.IsNullOrWhiteSpace(keywordParam)) { query = query.Where(x => x.ExamId.Contains(keywordParam) || x.CitizenId.Contains(keywordParam) || (x.Prefix + x.FirstName + " " + x.LastName).Contains(keywordParam) || x.HddPosition.Contains(keywordParam) || x.PositionName.Contains(keywordParam) ); } // join กับ DisableScores ก่อน เพื่อ filter ตาม ExamStatus var queryWithScores = query .GroupJoin( _context.DisableScores.Include(x => x.ScoreImport), rc => new { rc.PeriodExam!.Id, rc.ExamId }, sc => new { Id = sc.ScoreImport!.PeriodExamId, sc.ExamId }, (disable, scores) => new { disable, scores } ) .SelectMany( x => x.scores.DefaultIfEmpty(), (x, sr) => new { x.disable, score = sr } ); // filter ตาม req.ExamResult if (!string.IsNullOrWhiteSpace(req.ExamResult)) { switch (req.ExamResult.ToLower()) { case "missed_exam": queryWithScores = queryWithScores.Where(x => x.score != null && x.score.ExamStatus == "ขส."); break; case "pass": queryWithScores = queryWithScores.Where(x => x.score != null && x.score.ExamStatus == "ผ่าน"); break; case "notpass": queryWithScores = queryWithScores.Where(x => x.score != null && x.score.ExamStatus == "ไม่ผ่าน"); break; } } // total count หลังกรอง int total = await queryWithScores.CountAsync(); // pagination queryWithScores = queryWithScores .Skip((req.Page - 1) * req.PageSize) .Take(req.PageSize); // mapping var data = await queryWithScores .Select(x => new { examID = x.disable.ExamId, profileID = x.disable.CitizenId, prefix = x.disable.Prefix, fullName = $"{x.disable.FirstName} {x.disable.LastName}", dateOfBirth = x.disable.DateOfBirth.HasValue && x.disable.DateOfBirth.Value != DateTime.MinValue ? x.disable.DateOfBirth.Value.ToThaiShortDate() : "", gender = x.disable.Gendor, degree = x.disable.Educations.Any() ? x.disable.Educations.First().Degree : "", major = x.disable.Educations.Any() ? x.disable.Educations.First().Major : "", certificateNo = x.disable.Certificates.Any() ? x.disable.Certificates.First().CertificateNo ?? "" : "", certificateIssueDate = x.disable.Certificates.Any() && x.disable.Certificates.First().IssueDate != null && x.disable.Certificates.First().IssueDate != DateTime.MinValue ? x.disable.Certificates.First().IssueDate.ToThaiShortDate() : "", examScore = x.score == null ? 0.0 : x.score.TotalScore, examResult = x.score == null ? "" : x.score.ExamStatus, //examAttribute = x.disable.Certificates.Any() && x.disable.Certificates.First().IssueDate != null // ? _disableService.CheckValidCertificate(x.disable.Certificates.First().IssueDate, 5) // ? "มีคุณสมบัติ" : "ไม่มีคุณสมบัติ" // : "ไม่มีคุณสมบัติ", examAttribute = x.score != null && !string.IsNullOrEmpty(x.score.ExamAttribute) && (x.score.ExamAttribute == "มีคุณสมบัติ" || x.score.ExamAttribute == "ไม่มีคุณสมบัติ") ? x.score.ExamAttribute : "", remark = x.disable.Remark, isSpecial = x.disable.Isspecial == "Y" ? x.disable.Isspecial : "", applyDate = x.disable.ApplyDate.HasValue && x.disable.ApplyDate.Value != DateTime.MinValue ? x.disable.ApplyDate.Value.ToThaiShortDate() : "", university = x.disable.Educations.Any() ? x.disable.Educations.First().University : "", position_name = x.disable.PositionName, hddPosition = x.disable.HddPosition ?? "", typeTest = x.disable.typeTest ?? "", position_level = x.disable.PositionLevel ?? "", position_type = x.disable.PositionType ?? "", exam_name = x.disable.PeriodExam!.Name, exam_order = x.disable.PeriodExam != null && x.disable.PeriodExam.Round != null ? x.disable.PeriodExam.Round.ToString() : "", score_year = x.disable.PeriodExam != null && x.disable.PeriodExam.Year != null ? (x.disable.PeriodExam.Year > 2500 ? x.disable.PeriodExam.Year : x.disable.PeriodExam.Year + 543).ToString() : "", number = x.score == null ? "" : x.score.Number, }) .ToListAsync(); // --------------------------- // 3️. ดึงสรุปคะแนน // --------------------------- dynamic header = null; int _count = await _context.Disables.Where(x => x.PeriodExam.Id == id).CountAsync(); if (_count > 0) { header = await _context.DisableScores .Include(x => x.ScoreImport) .Where(x => x.ScoreImport.PeriodExamId == id) .GroupBy(x => 1) .Select(g => new { count = _count, pass = g.Count(x => x.ExamStatus == "ผ่าน"), notpass = g.Count(x => x.ExamStatus == "ไม่ผ่าน"), missed_exam = g.Count(x => x.ExamStatus == "ขส."), other = g.Count(x => string.IsNullOrEmpty(x.ExamStatus) || !new[] { "ผ่าน", "ไม่ผ่าน", "ขส." }.Contains(x.ExamStatus) ) }) .FirstOrDefaultAsync() ?? new // ใช้ null-coalescing กันกรณีไม่มีข้อมูล (แทน else เดิม) { count = _count, pass = 0, notpass = 0, missed_exam = 0, other = 0 }; } else { header = new { count = _count, pass = 0, notpass = 0, missed_exam = 0, other = 0 }; } return Success(new { data = data, total = total, header = header, round = periodExam.Round, year = periodExam.Year.Value.ToThaiYear() }); } catch (Exception ex) { return Error(ex); } } [HttpGet("exam/{id:length(36)}/{examId}")] public async Task> GetExamResultByPersonAsync(Guid id, string examId) { try { var data = await _context.Disables.AsQueryable() .Include(x => x.PeriodExam) .Include(x => x.Documents) .ThenInclude(x => x.DocumentFile) .Where(x => x.PeriodExam.Id == id) .Where(x => x.PeriodExam.CheckDisability == true) .Where(x => x.ExamId == examId) .Join(_context.DisableScores.AsQueryable() .Include(x => x.ScoreImport), rc => new { PeriodExamId = rc.PeriodExam.Id, rc.ExamId }, sc => new { PeriodExamId = sc.ScoreImport.PeriodExamId, sc.ExamId }, (p, sr) => new { ExamID = p.ExamId, ProfileID = p.CitizenId, p.Prefix, FullName = $"{p.FirstName} {p.LastName}", DateOfBirth = p.DateOfBirth.HasValue && p.DateOfBirth.Value != DateTime.MinValue ? p.DateOfBirth.Value.ToThaiShortDate() : "", Gender = p.Gendor, Degree = p.Educations.First().Degree, Major = p.Educations.First().Major, CertificateNo = p.Certificates.Count > 0 ? p.Certificates.First().CertificateNo ?? "" : "", CertificateIssueDate = p.Certificates.Count > 0 ? p.Certificates.First().IssueDate != DateTime.MinValue ? p.Certificates.First().IssueDate.ToThaiShortDate() : "" : "", ExamResult = sr == null ? "" : sr.ExamStatus, // ExamAttribute = p.Certificates.Count > 0 ? // _disableService.CheckValidCertificate(p.Certificates.First().IssueDate, 5) // ? "มีคุณสมบัติ" // : "ไม่มีคุณสมบัติ" // : "ไม่มีคุณสมบัติ", ExamAttribute = sr != null && !string.IsNullOrEmpty(sr.ExamAttribute) && (sr.ExamAttribute == "มีคุณสมบัติ" || sr.ExamAttribute == "ไม่มีคุณสมบัติ") ? sr.ExamAttribute : "", IsSpecial = p.Isspecial, Remark = p.Remark, University = p.Educations.First().University, PositionName = p.PositionName, PositionType = p.PositionType, PositionLevel = p.PositionLevel, ExamName = p.PeriodExam!.Name, ExamOrder = p.PeriodExam.Round, ExamYear = p.PeriodExam.Year == null ? 0 : p.PeriodExam.Year.Value.ToThaiYear(), Score = sr == null ? 0.0 : sr.TotalScore, Number = sr == null ? "" : sr.Number, ExamCount = _disableService.GetExamCount(p.CitizenId), ScoreExpire = p.PeriodExam.AnnouncementDate == null ? "" : p.PeriodExam.AnnouncementDate != DateTime.MinValue ? p.PeriodExam.AnnouncementDate.Value.AddYears(2).ToThaiShortDate() : "", typeTest = p.typeTest, ScoreResult = sr == null ? null : new { ScoreAFull = sr.FullA, ScoreA = sr.SumA, ScoreBFull = sr.FullB, ScoreB = sr.SumB, ScoreAB = sr.SumAB, ScoreCFull = sr.FullC, ScoreC = sr.SumC, ScoreDFull = sr.FullD, ScoreD = sr.SumD, ScoreCD = sr.SumCD, ScoreSumFull = sr.FullScore, ScoreSum = sr.TotalScore, ExamResult = sr.ExamStatus }, Attachments = p.Documents.Select(a => new { FileName = a.DocumentFile.FileName, DocumentId = a.DocumentFile.Id }) }) .FirstOrDefaultAsync(); return Success(data); } catch (Exception ex) { return Error(ex); } } #endregion #region " For CMS " [HttpGet("competitive")] [AllowAnonymous] public IActionResult GetPeriodForCMS() { try { var periods = _context.PeriodExams.AsQueryable() .Where(x => x.CheckDisability == true) .Select(r => new { id = r.Id, title = $"{r.Name} ครั้งที่ {r.Round}/{r.Year.Value.ToThaiYear()}", category = "สำนักงาน ก.ก.", category_id = 1, announcement_startDate = r.AnnouncementStartDate == null ? "" : r.AnnouncementStartDate.ToString("yyyy-mm-dd"), announcement_endDate = r.AnnouncementEndDate == null ? "" : r.AnnouncementEndDate.ToString("yyyy-MM-dd"), announcementExam = true, register_startDate = r.RegisterStartDate == null ? "" : r.RegisterStartDate.Value.ToString("yyyy-MM-dd"), register_endDate = r.RegisterEndDate == null ? "" : r.RegisterEndDate.Value.ToString("yyyy-MM-dd"), payment_startDate = r.PaymentStartDate == null ? "" : r.PaymentStartDate.Value.ToString("yyyy-MM-dd"), payment_endDate = r.PaymentEndDate == null ? "" : r.PaymentEndDate.Value.ToString("yyyy-MM-dd"), exam_date = r.ExamDate == null ? "" : r.ExamDate.Value.ToString("yyyy-MM-dd") }) .ToList(); return Ok(periods); } catch (Exception ex) { return StatusCode(500, ex.Message); } } #endregion #region " Export Excel " [HttpGet("export/exam/{id:length(36)}")] public async Task> ExportExamAsync(Guid id) { var data = await _context.PeriodExams.AsQueryable() .Where(x => x.CheckDisability == true) .Include(x => x.Disables) .FirstOrDefaultAsync(x => x.Id == id); if (data == null) return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound); var header = $"{data.Name} ครั้งที่ {data.Round}/{data.Year.Value.ToThaiYear()}"; var disables = data.Disables .OrderBy(x => x.ExamId) .Select(x => new { ExamId = x.ExamId, CitizenId = x.CitizenId, Fullname = $"{x.Prefix}{x.FirstName} {x.LastName}", PositionName = x.PositionName, PositionType = x.PositionType, PositionLevel = x.PositionLevel }) .ToList(); var _data = new { template = "ExamList", reportName = $"ExamList_{DateTime.Now.ToString("yyyyMMddHHmmss")}", data = new { header = header, data = disables } }; return Success(_data); } [HttpGet("export/pass-exam/{id:length(36)}")] public async Task> ExportPassExamAsync(Guid id) { var data = await _context.PeriodExams.AsQueryable() .Where(x => x.CheckDisability == true) .Include(x => x.Disables) .FirstOrDefaultAsync(x => x.Id == id); var data_pass = await _context.PeriodExams.AsQueryable() .Where(x => x.CheckDisability == true) .Include(x => x.ScoreImport) .ThenInclude(x => x.Scores) .FirstOrDefaultAsync(x => x.Id == id); if (data == null) return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound); if (data_pass == null) return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound); var header = $"{data.Name} ครั้งที่ {data.Round}/{data.Year.Value.ToThaiYear()}"; var result = (from r in data.Disables.ToList() join s in data_pass.ScoreImport.Scores.Where(x => x.AStatus == "ผ่าน").ToList() on r.ExamId equals s.ExamId orderby r.ExamId ascending, s.SumA descending select new { r.ExamId, r.CitizenId, Fullname = $"{r.Prefix}{r.FirstName} {r.LastName}", s.FullA, s.SumA, s.AStatus, }).ToList(); var _data = new { template = "PassAExamList", reportName = $"PassAExamList_{DateTime.Now.ToString("yyyyMMddHHmmss")}", data = new { header = header, data = result } }; return Success(_data); } [HttpGet("export/pass/{id:length(36)}")] public async Task> ExportPassExamResultAsync(Guid id) { var data = await _context.PeriodExams.AsQueryable() .Where(x => x.CheckDisability == true) .Include(x => x.Disables) .FirstOrDefaultAsync(x => x.Id == id); var data_pass = await _context.PeriodExams.AsQueryable() .Where(x => x.CheckDisability == true) .Include(x => x.ScoreImport) .ThenInclude(x => x.Scores) .FirstOrDefaultAsync(x => x.Id == id); if (data == null) return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound); if (data_pass == null) return Error(GlobalMessages.DataNotFound, StatusCodes.Status404NotFound); var header = $"{data.Name} ครั้งที่ {data.Round}/{data.Year.Value.ToThaiYear()}"; var result = (from r in data.Disables.ToList() join s in data_pass.ScoreImport.Scores.Where(x => x.ExamStatus == "ผ่าน").ToList() on r.ExamId equals s.ExamId select new { ExamId = r.ExamId, CitizenId = r.CitizenId, Fullname = $"{r.Prefix}{r.FirstName} {r.LastName}", Full = s.FullScore != null ? s.FullScore : 0, Sum = s.TotalScore != null ? s.TotalScore : 0, Status = s.ExamStatus, s.Number, r.PositionName, r.PositionType, r.PositionLevel, }).ToList() .OrderBy(x => x.ExamId) .ThenBy(x => x.PositionName) .ThenByDescending(x => x.Sum) .ToList(); var _data = new { template = "PassExamList", reportName = $"PassExamList_{DateTime.Now.ToString("yyyyMMddHHmmss")}", data = new { header = header, data = result } }; return Success(_data); } /// /// โอนคนสรรหาไปบรรจุ /// /// รหัสรอบสมัคร /// /// เมื่อโอนคนสรรหาไปบรรจุสำเร็จ /// ไม่ได้ Login เข้าระบบ /// เมื่อเกิดข้อผิดพลาดในการทำงาน [HttpPost("placement/{examId:length(36)}")] [ProducesResponseType(StatusCodes.Status200OK)] [ProducesResponseType(StatusCodes.Status401Unauthorized)] [ProducesResponseType(StatusCodes.Status500InternalServerError)] public async Task> UpdateAsyncDisableToPlacement(Guid examId, [FromBody] RecruitDateRequest req) { try { await _periodExamService.UpdateAsyncDisableToPlacement(examId, req.AccountStartDate); return Success(); } catch (Exception ex) { return Error(ex); } } #endregion #region " Report " /// /// ผลคะแนนผู้สมัครสอบ /// /// รหัสรอบการสอบคัดเลือกผู้พิการ /// เลขประจำตัวสอบ /// /// เมื่อทำการอ่านข้อมูลจำนวนผู้สอบผ่านแข่งขันเพื่อบรรจุเข้ารับราชการเป็นข้าราชการ กทม. สามัญสำเร็จ /// ไม่ได้ Login เข้าระบบ /// เมื่อเกิดข้อผิดพลาดในการทำงาน [HttpGet("report/exam/{id:length(36)}/{examId}"), DisableRequestSizeLimit] [ProducesResponseType(StatusCodes.Status200OK)] [ProducesResponseType(StatusCodes.Status401Unauthorized)] [ProducesResponseType(StatusCodes.Status500InternalServerError)] public async Task> GetReportExamResultByPersonAsync(Guid id, string examId) { try { var data = await _context.Disables.AsQueryable() .Include(x => x.PeriodExam) .Where(x => x.PeriodExam!.Id == id) .Where(x => x.ExamId == examId) .Join(_context.DisableScores.AsQueryable() .Include(x => x.ScoreImport), rc => new { rc.PeriodExam!.Id, rc.ExamId }, sc => new { Id = sc.ScoreImport!.PeriodExamId, sc.ExamId }, (p, sr) => new { ExamID = p.ExamId != null ? p.ExamId.ToThaiNumber() : "", CitizenId = p.CitizenId != null ? p.CitizenId.ToThaiNumber() : "", FullName = $"{p.Prefix}{p.FirstName} {p.LastName}", DateOfBirth = p.DateOfBirth.HasValue && p.DateOfBirth.Value != DateTime.MinValue ? p.DateOfBirth.Value.ToThaiShortDate().ToThaiNumber() : "", Gender = p.Gendor, Degree = p.Educations.First().Degree, Major = p.Educations.First().Major, University = p.Educations.First().University, PositionName = p.PositionName, ExamName = $"{p.PeriodExam!.Name} ครั้งที่ {p.PeriodExam.Round}/{(p.PeriodExam.Year > 2500 ? p.PeriodExam.Year : (p.PeriodExam.Year + 543))}".ToThaiNumber(), ExamCount = _disableService.GetExamCount(p.CitizenId).ToString().ToThaiNumber(), type = p.typeTest == "bangkok" ? "กทม." : p.typeTest == "ocsc" ? "ก.พ." : "", FA = sr.FullA != null ? sr.FullA.ToString().ToThaiNumber() : "", SA = sr.SumA != null ? sr.SumA.ToString().ToThaiNumber() : "", FC = sr.FullC != null ? sr.FullC.ToString().ToThaiNumber() : "", SC = sr.SumC != null ? sr.SumC.ToString().ToThaiNumber() : "", FD = sr.FullD != null ? sr.FullD.ToString().ToThaiNumber() : "", SD = sr.SumD != null ? sr.SumD.ToString().ToThaiNumber() : "", F = sr.FullScore != null ? sr.FullScore.ToString().ToThaiNumber() : "", T = sr.TotalScore != null ? sr.TotalScore.ToString().ToThaiNumber() : "", Result = sr.ExamStatus, Number = sr.Number != null ? sr.Number.ToString().ToThaiNumber() : "", Expire = p.PeriodExam.AnnouncementDate == null ? "" : p.PeriodExam.AnnouncementDate != DateTime.MinValue ? p.PeriodExam.AnnouncementDate.Value.AddYears(2).ToThaiShortDate().ToString().ToThaiNumber() : "", }) .FirstOrDefaultAsync(); var result = new { template = "recruit-exam", reportName = "recruit-exam", data = data }; return Success(result); } catch (Exception ex) { return Error(ex); } } #endregion #endregion } }